NC常用SQL

andylee624
浏览: 56430 次
性别:
来自: 广州
最近访客更多访客>>

usiyuu
abcanyalei
博主相关

博客
微博
相册
留言
关于我
文章分类

社区版块

存档分类

博客分类：
用友NC
1.--NC建库   
2.CREATE TABLESPACE NNC_DATA01 DATAFILE 'E:\oracle\product\10.2.0\oradata\orcl\nnc_data01.dbf' SIZE 500M AUTOEXTEND ON NEXT 50M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 256K ;   
3.CREATE TABLESPACE NNC_DATA02 DATAFILE 'E:\oracle\product\10.2.0\oradata\orcl\nnc_data02.dbf' SIZE 300M AUTOEXTEND ON NEXT 50M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 256K ;   
4.CREATE TABLESPACE NNC_DATA03 DATAFILE 'E:\oracle\product\10.2.0\oradata\orcl\nnc_data03.dbf' SIZE 500M AUTOEXTEND ON NEXT 100M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 512K ;   
5.CREATE TABLESPACE NNC_INDEX01 DATAFILE 'E:\oracle\product\10.2.0\oradata\orcl\nnc_index01.dbf' SIZE 500M AUTOEXTEND ON NEXT 50M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K ;   
6.CREATE TABLESPACE NNC_INDEX02 DATAFILE 'E:\oracle\product\10.2.0\oradata\orcl\nnc_index02.dbf' SIZE 300M AUTOEXTEND ON NEXT 50M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K ;    
7.CREATE TABLESPACE NNC_INDEX03 DATAFILE 'E:\oracle\product\10.2.0\oradata\orcl\nnc_index03.dbf' SIZE 500M AUTOEXTEND ON NEXT 100M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 256K ;    
8.CREATE USER NC50 IDENTIFIED BY NC50 DEFAULT TABLESPACE NNC_DATA01 TEMPORARY TABLESPACE temp;   
9.GRANT connect,dba to NC50;   
10.  
11.  
12.--根据公司编码查询公司主键   
13.select c.pk_corp from nc.bd_corp c where c.unitcode='3101'  
14.  
15.--根据凭证号、公司主键查询分录   
16.select *   
17.  from nc.gl_detail d   
18. where d.pk_voucher = '1004A11000000000H7P1'  
19.   and d.pk_corp = '1004'  
20.--分录号   
21.and d.detailindex=219   
22.  
23.--根据凭证主键查询作证号   
24.select v.no from nc.gl_voucher v where v.pk_voucher='1004A11000000000H7P1'  
25./*   
26.select  
27.  acc.subjcode,   
28.  acc.subjname   
29.from bd_accsubj acc,   
30.     bd_glorgbook orgbook,   
31.     bd_corp corp,   
32.     bd_glorg org   
33.where acc.pk_glorgbook = orgbook.pk_glorgbook   
34.  and orgbook.pk_glorg = org.pk_glorg   
35.  and org.pk_entityorg = corp.pk_corp   
36.  and corp.unitcode = '5002'  
37.  and acc.subjcode = '221114'  
38.*/   
39.--根据公司编码查询公司目录主键   
40.select g.pk_glorg,g.* from bd_glorg g where g.glorgcode='5003'  
41.--根据公司目录主键查询公司账薄主键   
42.select bd_glorgbook.pk_glorgbook, bd_glorgbook.*   
43.  from bd_glorgbook   
44. where bd_glorgbook.pk_glorg = '0001A1100000000ARH5O'  
45.  
46.select acc.subjcode, acc.subjname   
47.  from bd_accsubj acc   
48. where acc.pk_glorgbook = '0001A1100000000ARHAZ'  
49.  
50.--根据公司和科目编码查询余额      
51.select b.pk_balance,b.*   
52.  from bd_corp c, bd_accsubj a, gl_balance b   
53. where a.pk_corp = c.pk_corp   
54.   and c.unitcode = '3101'  
55.   and a.subjcode = '260101'  
56.   and a.pk_accsubj = b.pk_accsubj   
57.   and a.dr = 0   
58.   and b.year='2010'  
59.  
60.      
61.--根据科目编码查询辅助核算   
62.select distinct info.bdname, info.*   
63.  from bd_subjass s, bd_accsubj a, bd_bdinfo info   
64. where a.pk_accsubj = s.pk_accsubj   
65.   and s.pk_bdinfo = info.pk_bdinfo   
66.   and a.subjcode = '6601'  
67.      
68. select distinct bd_bdinfo.bdname   
69.   from bd_accsubj, bd_subjass, bd_bdinfo   
70.  where bd_accsubj.pk_accsubj = bd_subjass.pk_accsubj   
71.    and bd_subjass.pk_bdinfo = bd_bdinfo.pk_bdinfo   
72.    and (bd_accsubj.pk_corp = '0001' or bd_accsubj.pk_corp is null)   
73.    and nvl(bd_accsubj.dr, 0) = 0   
74.    and nvl(bd_accsubj.endflag, 'N') = 'Y'  
75.    and bd_accsubj.subjcode = '66013301'  
76.      
77.      
78.      
79.  
80.--根据年度、会计期间、公司编码、凭证号查询分录   
81.select v.no, v.pk_voucher, d.*   
82.  from gl_voucher v, gl_detail d, bd_corp c   
83. where v.pk_voucher = d.pk_voucher   
84.   and v.pk_corp = c.pk_corp   
85.   and v.year = '2009'  
86.   and v.period = '08'  
87.   and c.unitcode = '1101'  
88.   and v.no = '251'  
89.   and v.dr = 0   
90.  
91.--根据年度、公司编码、凭证主键查询科目   
92.select a.pk_accsubj, a.subjcode, a.subjname, d.localcreditamount   
93.  from gl_voucher v, bd_corp c, gl_detail d, bd_accsubj a   
94. where v.year = '2010'  
95.   and c.unitcode = '3101'  
96.   and c.pk_corp = v.pk_corp   
97.   and v.pk_voucher = d.pk_voucher      
98.   and a.pk_accsubj = d.pk_accsubj   
99.   and d.pk_voucher = '1004AQ1000000001OV9K'  
100.  
101.--根据公司和科目编码查询科目主键      
102.select pk_accsubj, g.glorgcode,acc.dispname, acc.*   
103.  from nc.bd_accsubj acc, nc.bd_glorgbook gb, nc.bd_glorg g   
104. where acc.subjcode like '660105%'  
105.   and acc.pk_glorgbook = gb.pk_glorgbook   
106.   and gb.pk_glorg = g.pk_glorg   
107.   and g.glorgcode = '5101'  
108.      
109.----------------------------------------------------------------------------------------------   
110.--2010/1/6            查询总账期初余额   
111.--subj.balanorient科目方向：1：借；2：贷   
112.select subj.subjcode 科目编码,   
113.       subj.subjname 科目名称,   
114.       case subj.balanorient   
115.         when 1 then  
116.          sum(gl.localdebitamount)   
117.         when 2 then  
118.          sum(gl.localcreditamount)   
119.       end 期初余额   
120.  from nc.gl_detail gl, nc.bd_corp corp, nc.bd_accsubj subj   
121. where gl.pk_corp = corp.pk_corp   
122.   and gl.pk_accsubj = subj.pk_accsubj   
123.   and corp.unitcode = '00'  
124.   and gl.yearv = '2009'  
125.   and gl.periodv = '00'  
126.   and gl.dr = 0   
127. group by subj.subjcode, subj.subjname, subj.balanorient   
128. order by subj.subjcode   
129.----------------------------------------------------------------------------------------------   
130.--根据收支项目与公司编码取收支项目主键   
131.select c.pk_costsubj   
132.  from nc.bd_costsubj c, nc.bd_corp corp   
133. where c.costcode = 'C224104'  
134.   and c.pk_corp = corp.pk_corp   
135.   and corp.unitcode = '00'  
136.  
137.----------------------------------------------------------------------------------------------   
138.--查看汇率   
139.select decode(currtypecode,   
140.              'HKD',   
141.              '02',   
142.              'USD',   
143.              '03',   
144.              'EUR',   
145.              '12',   
146.              'JPY',   
147.              '05',   
148.              '01') c_cur_no,   
149.       currtypecode,   
150.       currtypename,   
151.       substr(ratedate, 0, 7) rateperiod,   
152.       ratedate,   
153.       rate   
154.  from nc.bd_currtype, nc.bd_currrate, nc.bd_currinfo   
155. where (bd_currtype.pk_currtype = bd_currinfo.pk_currtype)   
156.   and (bd_currinfo.pk_currinfo = bd_currrate.pk_currinfo)   
157.   and (bd_currrate.pk_corp = '0001')   
158.   --and (currtypecode = 'USD' and substr(ratedate, 0, 7) = '2009-12')   
159. order by currtypecode, ratedate   
160.  
161.  
162.  
163.----------------------------------------------------------------------------------------------   
164.--停止后台任务   
165.update pub_alertregistry p set p.enabled = 'N' where p.accountpk = '0001AA100000000324SZ'  
166.----------------------------------------------------------------------------------------------   
167.--查询一个列属于哪个表   
168.select table_name, column_name from user_tab_columns where column_name = 'DJDL';   
169.--查询一个表的列   
170.select c.COLUMN_NAME from user_tab_columns c where c.TABLE_NAME = 'ARAP_DJZB' AND c.COLUMN_NAME like 'ZYX%'  
171.select concat('a.',c.COLUMN_NAME) from user_tab_columns c where c.TABLE_NAME = 'ARAP_DJZB' AND c.COLUMN_NAME like 'ZYX%'  
172.  
173.select a.djdl,a.djlxbm,b.zyx2 from    
174.arap_djzb a, arap_djfb b   
175.where trim(a.djbh) = trim(b.djbh)   
176.and length(b.zyx2)!=0   
177.----------------------------------------------------------------------------------------------   
178.--查询单据模板上自定义项的配置情况   
179.select t.bill_templetname 单据名称,   
180.       b.defaultshowname 显示名称,   
181.       b.itemkey 项目主键,   
182.       b.editformula 编辑公式,   
183.       decode(b.table_code,'main','表头','table','表体') 位置   
184.  from nc.pub_billtemplet t, nc.pub_billtemplet_b b   
185.  --pub_billtemplet t, pub_billtemplet_b b   
186. where t.pk_corp = '0001'  
187.   and t.pk_billtemplet = b.pk_billtemplet      
188.   and (b.itemkey like 'zyx%' or instr(b.editformula,'zyx')<>0)   
189.      
190.   select distinct f.zyx6 from nc.arap_djfb f     
191.----------------------------------------------------------------------------------------------   
192.--Q:在会计平台科目分类定义节点通过复制的方式会同时复制对照表但目标单位生成凭证时对照表没有启作用   
193.--原因是因为影响因素如表体科目还是源单位的，并没有自动转换成目标单位的   
194.--科目分类定义   
195.select * from dap_insubjclass  s where  s.insubjclasscode = 'EC34' and s.pk_corp = '1014';   
196.  
197.select * from dap_insubjclassfactor f where f.pk_insubject = '1020AQ1000000000JSYQ';   
198.--科目对照表   
199.select * from dap_subjview s where s.pk_insubject  = '1020AQ1000000000JSYQ';   
200.  
201.select * from bd_corp c where c.unitcode = '3301';   
202.  
203.select * from bd_accsubj a where a.pk_accsubj = '1014AA100000000003B7';   
204.  
205.  
206.select corp.unitcode 公司编码,   
207.       corp.unitname 公司名称,   
208.       insubjclass.insubjclasscode 科目分类编码,   
209.       insubjclass.insubjclassname 科目分类名称,   
210.       v.factor1 表体科目PK,   
211.       v.factorname1 表体科目名称,   
212.       v.subjcode 入账科目PK   
213.  from bd_corp corp   
214.  left join dap_insubjclass insubjclass on corp.pk_corp =   
215.                                           insubjclass.pk_corp   
216.  left join dap_subjview v on insubjclass.pk_insubjclass = v.pk_insubject   
217.  where corp.unitcode = '5002'  
218.  and insubjclass.insubjclasscode = 'EC34'--低耗品科目   
219.----------------------------------------------------------------------------------------------   
220.  
221.--成本系数定义明细   
222.select distinct corp.unitcode,   
223.                corp.unitname,   
224.                dept.deptname,   
225.                k.name,   
226.                c.coefficientname,   
227.                c.coefficientcode,   
228.                cb.value,   
229.                cb.ts   
230.  from nc.fc_coefficient_b  cb,   
231.       nc.fc_coefficient    c,   
232.       nc.bd_corp           corp,   
233.       nc.bd_deptdoc        dept,   
234.       nc.irp_insurancekind k   
235. where cb.pk_coefficient = c.pk_coefficient   
236.   and cb.pk_dwbm = corp.pk_corp   
237.   and cb.pk_deptdoc = dept.pk_deptdoc   
238.   and cb.pk_object = k.pk_insurancekind   
239.   and cb.kjnd = '2010'  
240.   and cb.kjqj = '06'  
241.   --and c.coefficientcode = 25   
242.  
243. order by  cb.ts desc;   
244.  
245.  
246.  
247.----------------------------------------------------------------------------------------------   
248.  
249.--结账状态   
250.--固定资产   
251.select * from fa_closebook where pk_corp=1006 and accyear=2010;   
252.  
253.--总账   
254.select * from gl_syssettled;--可以手动在此插入一条记录   
255.select glorgbook.pk_glorgbook 主体账薄主键,glorgbook.glorgbookname 主体账薄名称  from bd_glorgbook glorgbook ,bd_glorg glorg   
256.where glorgbook.pk_glorg = glorg.pk_glorg   
257.and glorg.glorgcode = '8501';   
258.  
259.  
260.----------------------------------------------------------------------------------------------   
261.  
262.----------------------------------------------------------------------------------------------   
263.--查询单据zyx是否有值   
264.select distinct f.zyx1,z.djlxbm   
265.  from nc.arap_djzb z, nc.arap_djfb f   
266. where    
267. z.djbh = trim(f.djbh)   
268. and z.djlxbm = '23A8'  
269.  
270.----------------------------------------------------------------------------------------------   
271.  
272.--更新所有用户密码为1   
273.update sm_user u set u.user_password = 'jlehfdffcfmohiag',u.pwdlevelcode = ''  
274.  
275.select * from sm_user u   
276.where u.user_code in ('zbjjl','cqls','zbfuzhaofui','tbadmin','100000524');   
277.--锁定用户   
278.update sm_user u set u.locked_tag = 'Y'  
279.where u.user_code not in ('zbjjl','cqls','zbfuzhaofui','tbadmin','100000524');   
280.  
281.  
282.----------------------------------------------------------------------------------------------   
283.--结算方式   
284.select * from nc.bd_balatype   
285.----------------------------------------------------------------------------------------------   
286.  
287.---单据查询时后台取出的一段语句   
288.select pk_finindex,   
289.       pk_sys,   
290.       pk_proc,   
291.       pk_vouchentry,   
292.       gl_voucher.no voucherno,   
293.       procmsg,   
294.       gl_voucher.pk_vouchertype vouchertype,   
295.       bd_vouchertype.vouchtypename vouchtypename,   
296.       bd_glorg.pk_glorg,   
297.       bd_glorgbook.pk_glbook,   
298.       bd_glorg.glorgname glorg_name,   
299.       bd_glorgbook.glorgbookname glbook_name,   
300.       checked.user_name checked,   
301.       manager.user_name manager,   
302.       totaldebit,   
303.       totalcredit,   
304.       prepareddate,   
305.       period,   
306.       dap_finindex.pk_rtvouch,   
307.       dap_finindex.errmsg,   
308.       gl_voucher.tallydate   
309.  from dap_finindex,   
310.       gl_voucher,   
311.       bd_vouchertype,   
312.       sm_user prepared,   
313.       sm_user checked,   
314.       sm_user casher,   
315.       sm_user manager,   
316.       bd_glorg,   
317.       bd_glorgbook   
318. where dap_finindex.pk_vouchentry = gl_voucher.pk_voucher(+)   
319.   and gl_voucher.pk_vouchertype = bd_vouchertype.pk_vouchertype(+)   
320.   and gl_voucher.pk_prepared = prepared.cUserId(+)   
321.   and gl_voucher.pk_checked = checked.cUserId(+)   
322.   and gl_voucher.pk_casher = casher.cUserId(+)   
323.   and gl_voucher.pk_manager = manager.cUserId(+)   
324.   and bd_glorg.pk_glorg(+) = dap_finindex.pk_glorg   
325.   and bd_glorgbook.pk_glbook(+) = dap_finindex.pk_glbook   
326.   and bd_glorgbook.pk_glorg(+) = dap_finindex.pk_glorg   
327.   and (procmsg in ('1001A1100000000CC547', '1001AQ100000000CB9H5',   
328.        '1001AQ100000000CB7HQ', '1001AQ100000000CC3RN',   
329.        '1001AQ100000000CB7D3', '1001AQ100000000CB7I8',   
330.        '1001AQ100000000CC3RT', '1001AQ100000000CC1BF',   
331.        '1001AQ100000000CC3KQ', '0001AQ100000004LI2BQ',   
332.        '1001A1100000000CB5M9', '1001AQ100000000CB7HX',   
333.        '1001A1100000000CB6F0', '1001AQ100000000CB7DU',   
334.        '1001A1100000000CC541', '1001AA100000000CBDC8',   
335.        '1001A1100000000CB6E4', '1001AQ100000000CB7XO',   
336.        '1001AQ100000000CC3KJ', '1001A1100000000CB6E1'))   
337.  
338.----------------------------------------------------------------------------------------------   
339.  
340.  
341.----------------------------------------------------------------------------------------------   
342.  
343.--成本要素科目对照   
344.select a.pk_glrapport, a.subjcode, b.subjname, a.itemcode, c.itemname   
345.  from fc_glrapport a   
346.  left join bd_accsubj b on a.subjcode = b.subjcode   
347.  left join fc_costitemdefine c on a.itemcode = c.itemcode   
348. where (b.Pk_Glorgbook = '0001')   
349.    or (b.pk_corp is null And b.Pk_Glorgbook is null)   
350. order by a.subjcode;   
351.  
352.select a.pk_glrapport, a.subjcode, b.subjname, a.itemcode, c.itemname   
353.  from fc_glrapport a, bd_accsubj b, fc_costitemdefine c   
354. where a.subjcode = b.subjcode(+)   
355.   and a.itemcode = c.itemcode(+)   
356.   and ((b.Pk_Glorgbook = '0001') or  
357.       (b.pk_corp is null And b.Pk_Glorgbook is null))   
358. order by a.subjcode;   
359.----------------------------------------------------------------------------------------------   
360.--凡是公司不设置本位币的公司   
361.select * from pub_sysinit where initname like '%本位币%' and pkvalue is null;   
362.--将凭证子表中的垃圾数据清理掉   
363.delete from gl_detail d where not exists( select pk_voucher from gl_voucher v where v.pk_voucher=d.pk_voucher);   
364.--检查会计平台还存在未处理的实时凭证   
365.SELECT dap_finindex.*,settlecentername FROM dap_finindex left join bd_settlecenter on(dap_finindex.pk_corp=bd_settlecenter.pk_corp) WHERE flag = 2 AND pk_sys = 'FTS' AND destsystem=1 and nvl(dap_finindex.dr,0)=0 and nvl(bd_settlecenter.dr,0)=0;   
366.  
367.  
368.----------------------------------------------------------------------------------------------   
369.----------------------------------------------------------------------------------------------   
370.----------------------------------------------------------------------------------------------   
371.----------------------------------------------------------------------------------------------   
372.----------------------------------------------------------------------------------------------   
373.  
374.--检查审批流是否定义   
375.select pk_wf_def, fatherpk, processDefID, node_name, node_type, billmaker, billmaker_name, billmaker_type, createdate,   
376.version, workflow_type from pub_wf_def where validation = 1 and pk_corp = '1001' and busitype = 'KHHH0000000000000001'  
377.and  
378.billtype = 'ZG54' and ( node_type = '2' or node_type = '3' )    
379.----------------------------------------------------------------------------   
380.--单据类型管理表,手工设置4位类型代码   
381.select * From bd_billtype where pk_billtypecode='TB'  
382.update bd_billtype set pk_billtypecode='TB10' where pk_billtypecode='TB'  
383.----------------------------------------------------------------------------   
384.--手工做vo对照_主表   
385.insert into pub_votable(approveid,    billid,    billno,    billvo,  busitype,   def1,def2,   
386.def3,dr,   headbodyflag,headitemvo,                itemcode,            operator,     pk_billtype,pk_corp,pkfield,   
387.   votable,           pk_votable,            ts)    
388.                 values('vapproveid','pk_apply','vbillcode','nc.vo.trade.pub.HYBillVO','pk_busitype',NULL,NULL,NULL,   
389.NULL,'Y',       'nc.vo.shsh.stock.tb1010.StockApplyVO','shsh_stock_apply',   'voperatorid','TB10',     NULL,   
390.   'pk_apply','shsh_stock_apply','0001AA10000000001RK3','2007-09-27 16:59:14')    
391.--手工做vo对照_子表   
392.insert into pub_votable(approveid,billid,billno,billvo,                    busitype,def1,def2,def3,dr,  headbodyflag,   
393.headitemvo,                             itemcode,            operator,pk_billtype,pk_corp,pkfield,     votable,   
394.              pk_votable,            ts)    
395.                 values('',       '',    '',    'nc.vo.trade.pub.HYBillVO','',      NULL,NULL,NULL,NULL,'N','nc.vo.   
396.shsh.   
397.stock.tb1010.StockApplyBVO','shsh_stock_apply_b','',      'TB10',     NULL,   
398.   'pk_apply','shsh_stock_apply_b','0001AA10000000001RK4','2007-09-27 17:12:01')    
399.----------------------------------------------------------------------------   
400.--自动生成单据编码   
401.insert into pub_billcode_rule(billcodeshortname,controlpara,day,dr,isautofill,ischeck,ishaveshortname,ispreserve,   
402.lastsn,   
403.month,   
404.object1,object2,pk_billcoderule,pk_billtypecode,snnum,snresetflag,ts,year)   
405.values('ZA','Y',20,0,'Y','Y','Y','Y','0000',12,NULL,NULL,'smTB1000000000000001','TB10',4,   
406.0,'2007-09-27 18:55:32','07')   
407.----------------------------------------------------------------------------   
408.--操作员人员关联关系   
409.select psn.psnname from bd_psndoc psn inner join sm_userandclerk on sm_userandclerk.pk_psndoc = psn.pk_psndoc   
410.inner join sm_user on sm_user.cuserid = sm_userandclerk.userid    
411.----------------------------------------------------------------------------   
412.--查询模板的单据状态下列值初始化   
413.update pub_query_condition set consult_code='I,,审批不通过,审批通过,审批进行中,提交态,作废态,冲销态,终止(结算)态,冻结   
414.态,自由   
415.态,待经办,已经办,已核查,已作废'    
416.where field_code like '%billstatus' and pk_templet in(   
417.select id from pub_query_templet where model_code in ('91012010'))   
418.----------------------------------------------------------------------------   
419.--复制数据库里的类似数据，声称INSERT语句   
420.select 'insert dap_defitem values('+attrname+','+CONVERT(char(1),dr)+','+headflag+','+itemname+','+CONVERT(char(1),   
421.itemtype)   
422.+','+pk_billtype+','+pk_voitem+','','+ts+')' from dap_defitem where pk_billtype='TB52'  
423.----------------------------------------------------------------------------   
424.--手动增加打印模板中的项   
425.insert into PUB_PRINT_DATAITEM (DR, IDATATYPE, ITYPE, PK_CORP, PK_VARITEM, PREPARE1, RESID, TS, USERDEFFLAG, VNODECODE,   
426.VTABLECODE, VTABLENAME, VVAREXPRESS, VVARNAME)   
427.values (0, null, null, [email=]'@@@@'[/email], '0001AA10000000ZY1220', null, null, '2007-01-21 15:23:37', null, 'TC0106', null, null,   
428.'t_pk_psndoc', '报销人(表尾)');   
429.----------------------------------------------------------------------------   
430.--修改单据模板模板的字段类型   
431.update pub_billtemplet_b set reftype='5'  
432.where pk_billtemplet in  
433.(select pk_billtemplet from pub_billtemplet where nodecode like 'TD%')   
434.and datatype=2 and (reftype is null or reftype='3' or reftype='2') and  
435.(defaultshowname like '%费%' or defaultshowname like '%额%'  
436.or defaultshowname like '%金%' or defaultshowname like '%价%')   
437.----------------------------------------------------------------------------   
438.--参照的表和字段   
439.select pk_refcolumn, fieldname, fieldshowname, ishiddenfield, isblurfield, ismnecode,    
440.ispkfield, iscolumnshow, islocateshow, columnshowindex, locateshowindex,   
441.bd_refcolumn.pk_reftable,datatype,tablename from    
442.bd_refcolumn inner join bd_reftable on bd_refcolumn.pk_reftable = bd_reftable.pk_reftable    
443.where bd_refcolumn.pk_reftable = '0001AA1000000000P4LA'  order by columnshowindex    
444.--查询有节点权限的人员   
445.-----------------------------------------------------------------------------   
446.select * from sm_funcregister   
447.select user_name from sm_user where cuserid in(   
448.select userid from sm_appuserpower where funid='0001AA10000000000UDO'  
449.)   
450.--查询有节点权限的用户组   
451.select g.group_name,g.pk_corp from sm_group g where cgroupid in(   
452.select groupid from sm_usergrouppower where funid='0001AA10000000000WKR'  
453.)   
454.--审批流相关表   
455.----------------------------------------------------------------------------   
456.select * from pub_wf_instance where billid='' and billno=''  
457.select * from pub_wf_task where pk_wf_instance=''  
458.select * from pub_wf_actinstance where pk_wf_instance=''  
459.select * from pub_workflownote where billid='' and billno=''  
460.select * from pub_wf_actinstancesrc where target_actinstance=pub_wf_actinstance.pk_wf_actinstance   
461.--由于各表外键约束,自行调节删除顺序
分享到：