常用SQL

--NC建库
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 ;
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 ;
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 ;
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 ;
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 ; 
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 ; 
CREATE USER NC50 IDENTIFIED BY NC50 DEFAULT TABLESPACE NNC_DATA01 TEMPORARY TABLESPACE temp;
GRANT connect,dba to NC50;


--根据公司编码查询公司主键
select c.pk_corp from nc.bd_corp c where c.unitcode='3101'

--根据凭证号、公司主键查询分录
select *
  from nc.gl_detail d
 where d.pk_voucher = '1004A11000000000H7P1'
   and d.pk_corp = '1004'
--分录号
and d.detailindex=219

--根据凭证主键查询作证号
select v.no from nc.gl_voucher v where v.pk_voucher='1004A11000000000H7P1'
/*
select
  acc.subjcode,
  acc.subjname
from bd_accsubj acc,
     bd_glorgbook orgbook,
     bd_corp corp,
     bd_glorg org
where acc.pk_glorgbook = orgbook.pk_glorgbook
  and orgbook.pk_glorg = org.pk_glorg
  and org.pk_entityorg = corp.pk_corp
  and corp.unitcode = '5002'
  and acc.subjcode = '221114'
*/
--根据公司编码查询公司目录主键
select g.pk_glorg,g.* from bd_glorg g where g.glorgcode='5003'
--根据公司目录主键查询公司账薄主键
select bd_glorgbook.pk_glorgbook, bd_glorgbook.*
  from bd_glorgbook
 where bd_glorgbook.pk_glorg = '0001A1100000000ARH5O'

select acc.subjcode, acc.subjname
  from bd_accsubj acc
 where acc.pk_glorgbook = '0001A1100000000ARHAZ'

--根据公司和科目编码查询余额   
select b.pk_balance,b.*
  from bd_corp c, bd_accsubj a, gl_balance b
 where a.pk_corp = c.pk_corp
   and c.unitcode = '3101'
   and a.subjcode = '260101'
   and a.pk_accsubj = b.pk_accsubj
   and a.dr = 0
   and b.year='2010'

   
--根据科目编码查询辅助核算
select distinct info.bdname, info.*
  from bd_subjass s, bd_accsubj a, bd_bdinfo info
 where a.pk_accsubj = s.pk_accsubj
   and s.pk_bdinfo = info.pk_bdinfo
   and a.subjcode = '6601'
   
 select distinct bd_bdinfo.bdname
   from bd_accsubj, bd_subjass, bd_bdinfo
  where bd_accsubj.pk_accsubj = bd_subjass.pk_accsubj
    and bd_subjass.pk_bdinfo = bd_bdinfo.pk_bdinfo
    and (bd_accsubj.pk_corp = '0001' or bd_accsubj.pk_corp is null)
    and nvl(bd_accsubj.dr, 0) = 0
    and nvl(bd_accsubj.endflag, 'N') = 'Y'
    and bd_accsubj.subjcode = '66013301'
   
   
   

--根据年度、会计期间、公司编码、凭证号查询分录
select v.no, v.pk_voucher, d.*
  from gl_voucher v, gl_detail d, bd_corp c
 where v.pk_voucher = d.pk_voucher
   and v.pk_corp = c.pk_corp
   and v.year = '2009'
   and v.period = '08'
   and c.unitcode = '1101'
   and v.no = '251'
   and v.dr = 0

--根据年度、公司编码、凭证主键查询科目
select a.pk_accsubj, a.subjcode, a.subjname, d.localcreditamount
  from gl_voucher v, bd_corp c, gl_detail d, bd_accsubj a
 where v.year = '2010'
   and c.unitcode = '3101'
   and c.pk_corp = v.pk_corp
   and v.pk_voucher = d.pk_voucher   
   and a.pk_accsubj = d.pk_accsubj
   and d.pk_voucher = '1004AQ1000000001OV9K'

--根据公司和科目编码查询科目主键   
select pk_accsubj, g.glorgcode,acc.dispname, acc.*
  from nc.bd_accsubj acc, nc.bd_glorgbook gb, nc.bd_glorg g
 where acc.subjcode like '660105%'
   and acc.pk_glorgbook = gb.pk_glorgbook
   and gb.pk_glorg = g.pk_glorg
   and g.glorgcode = '5101'
   
----------------------------------------------------------------------------------------------
--2010/1/6            查询总账期初余额
--subj.balanorient科目方向：1：借；2：贷
select subj.subjcode 科目编码,
       subj.subjname 科目名称,
       case subj.balanorient
         when 1 then
          sum(gl.localdebitamount)
         when 2 then
          sum(gl.localcreditamount)
       end 期初余额
  from nc.gl_detail gl, nc.bd_corp corp, nc.bd_accsubj subj
 where gl.pk_corp = corp.pk_corp
   and gl.pk_accsubj = subj.pk_accsubj
   and corp.unitcode = '00'
   and gl.yearv = '2009'
   and gl.periodv = '00'
   and gl.dr = 0
 group by subj.subjcode, subj.subjname, subj.balanorient
 order by subj.subjcode
----------------------------------------------------------------------------------------------
--根据收支项目与公司编码取收支项目主键
select c.pk_costsubj
  from nc.bd_costsubj c, nc.bd_corp corp
 where c.costcode = 'C224104'
   and c.pk_corp = corp.pk_corp
   and corp.unitcode = '00'

----------------------------------------------------------------------------------------------
--查看汇率
select decode(currtypecode,
              'HKD',
              '02',
              'USD',
              '03',
              'EUR',
              '12',
              'JPY',
              '05',
              '01') c_cur_no,
       currtypecode,
       currtypename,
       substr(ratedate, 0, 7) rateperiod,
       ratedate,
       rate
  from nc.bd_currtype, nc.bd_currrate, nc.bd_currinfo
 where (bd_currtype.pk_currtype = bd_currinfo.pk_currtype)
   and (bd_currinfo.pk_currinfo = bd_currrate.pk_currinfo)
   and (bd_currrate.pk_corp = '0001')
   --and (currtypecode = 'USD' and substr(ratedate, 0, 7) = '2009-12')
 order by currtypecode, ratedate



----------------------------------------------------------------------------------------------
--停止后台任务
update pub_alertregistry p set p.enabled = 'N' where p.accountpk = '0001AA100000000324SZ'
----------------------------------------------------------------------------------------------
--查询一个列属于哪个表
select table_name, column_name from user_tab_columns where column_name = 'DJDL';
--查询一个表的列
select c.COLUMN_NAME from user_tab_columns c where c.TABLE_NAME = 'ARAP_DJZB' AND c.COLUMN_NAME like 'ZYX%'
select concat('a.',c.COLUMN_NAME) from user_tab_columns c where c.TABLE_NAME = 'ARAP_DJZB' AND c.COLUMN_NAME like 'ZYX%'

select a.djdl,a.djlxbm,b.zyx2 from 
arap_djzb a, arap_djfb b
where trim(a.djbh) = trim(b.djbh)
and length(b.zyx2)!=0
----------------------------------------------------------------------------------------------
--查询单据模板上自定义项的配置情况
select t.bill_templetname 单据名称,
       b.defaultshowname 显示名称,
       b.itemkey 项目主键,
       b.editformula 编辑公式,
       decode(b.table_code,'main','表头','table','表体') 位置
  from nc.pub_billtemplet t, nc.pub_billtemplet_b b
  --pub_billtemplet t, pub_billtemplet_b b
 where t.pk_corp = '0001'
   and t.pk_billtemplet = b.pk_billtemplet   
   and (b.itemkey like 'zyx%' or instr(b.editformula,'zyx')<>0)
   
   select distinct f.zyx6 from nc.arap_djfb f  
----------------------------------------------------------------------------------------------
--Q:在会计平台科目分类定义节点通过复制的方式会同时复制对照表但目标单位生成凭证时对照表没有启作用
--原因是因为影响因素如表体科目还是源单位的，并没有自动转换成目标单位的
--科目分类定义
select * from dap_insubjclass  s where  s.insubjclasscode = 'EC34' and s.pk_corp = '1014';

select * from dap_insubjclassfactor f where f.pk_insubject = '1020AQ1000000000JSYQ';
--科目对照表
select * from dap_subjview s where s.pk_insubject  = '1020AQ1000000000JSYQ';

select * from bd_corp c where c.unitcode = '3301';

select * from bd_accsubj a where a.pk_accsubj = '1014AA100000000003B7';


select corp.unitcode 公司编码,
       corp.unitname 公司名称,
       insubjclass.insubjclasscode 科目分类编码,
       insubjclass.insubjclassname 科目分类名称,
       v.factor1 表体科目PK,
       v.factorname1 表体科目名称,
       v.subjcode 入账科目PK
  from bd_corp corp
  left join dap_insubjclass insubjclass on corp.pk_corp =
                                           insubjclass.pk_corp
  left join dap_subjview v on insubjclass.pk_insubjclass = v.pk_insubject
  where corp.unitcode = '5002'
  and insubjclass.insubjclasscode = 'EC34'--低耗品科目
----------------------------------------------------------------------------------------------

--成本系数定义明细
select distinct corp.unitcode,
                corp.unitname,
                dept.deptname,
                k.name,
                c.coefficientname,
                c.coefficientcode,
                cb.value,
                cb.ts
  from nc.fc_coefficient_b  cb,
       nc.fc_coefficient    c,
       nc.bd_corp           corp,
       nc.bd_deptdoc        dept,
       nc.irp_insurancekind k
 where cb.pk_coefficient = c.pk_coefficient
   and cb.pk_dwbm = corp.pk_corp
   and cb.pk_deptdoc = dept.pk_deptdoc
   and cb.pk_object = k.pk_insurancekind
   and cb.kjnd = '2010'
   and cb.kjqj = '06'
   --and c.coefficientcode = 25

 order by  cb.ts desc;



----------------------------------------------------------------------------------------------

--结账状态
--固定资产
select * from fa_closebook where pk_corp=1006 and accyear=2010;

--总账
select * from gl_syssettled;--可以手动在此插入一条记录
select glorgbook.pk_glorgbook 主体账薄主键,glorgbook.glorgbookname 主体账薄名称  from bd_glorgbook glorgbook ,bd_glorg glorg
where glorgbook.pk_glorg = glorg.pk_glorg
and glorg.glorgcode = '8501';


----------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------
--查询单据zyx是否有值
select distinct f.zyx1,z.djlxbm
  from nc.arap_djzb z, nc.arap_djfb f
 where 
 z.djbh = trim(f.djbh)
 and z.djlxbm = '23A8'

----------------------------------------------------------------------------------------------

--更新所有用户密码为1
update sm_user u set u.user_password = 'jlehfdffcfmohiag',u.pwdlevelcode = ''

select * from sm_user u
where u.user_code in ('zbjjl','cqls','zbfuzhaofui','tbadmin','100000524');
--锁定用户
update sm_user u set u.locked_tag = 'Y'
where u.user_code not in ('zbjjl','cqls','zbfuzhaofui','tbadmin','100000524');


----------------------------------------------------------------------------------------------
--结算方式
select * from nc.bd_balatype
----------------------------------------------------------------------------------------------

---单据查询时后台取出的一段语句
select pk_finindex,
       pk_sys,
       pk_proc,
       pk_vouchentry,
       gl_voucher.no voucherno,
       procmsg,
       gl_voucher.pk_vouchertype vouchertype,
       bd_vouchertype.vouchtypename vouchtypename,
       bd_glorg.pk_glorg,
       bd_glorgbook.pk_glbook,
       bd_glorg.glorgname glorg_name,
       bd_glorgbook.glorgbookname glbook_name,
       checked.user_name checked,
       manager.user_name manager,
       totaldebit,
       totalcredit,
       prepareddate,
       period,
       dap_finindex.pk_rtvouch,
       dap_finindex.errmsg,
       gl_voucher.tallydate
  from dap_finindex,
       gl_voucher,
       bd_vouchertype,
       sm_user prepared,
       sm_user checked,
       sm_user casher,
       sm_user manager,
       bd_glorg,
       bd_glorgbook
 where dap_finindex.pk_vouchentry = gl_voucher.pk_voucher(+)
   and gl_voucher.pk_vouchertype = bd_vouchertype.pk_vouchertype(+)
   and gl_voucher.pk_prepared = prepared.cUserId(+)
   and gl_voucher.pk_checked = checked.cUserId(+)
   and gl_voucher.pk_casher = casher.cUserId(+)
   and gl_voucher.pk_manager = manager.cUserId(+)
   and bd_glorg.pk_glorg(+) = dap_finindex.pk_glorg
   and bd_glorgbook.pk_glbook(+) = dap_finindex.pk_glbook
   and bd_glorgbook.pk_glorg(+) = dap_finindex.pk_glorg
   and (procmsg in ('1001A1100000000CC547', '1001AQ100000000CB9H5',
        '1001AQ100000000CB7HQ', '1001AQ100000000CC3RN',
        '1001AQ100000000CB7D3', '1001AQ100000000CB7I8',
        '1001AQ100000000CC3RT', '1001AQ100000000CC1BF',
        '1001AQ100000000CC3KQ', '0001AQ100000004LI2BQ',
        '1001A1100000000CB5M9', '1001AQ100000000CB7HX',
        '1001A1100000000CB6F0', '1001AQ100000000CB7DU',
        '1001A1100000000CC541', '1001AA100000000CBDC8',
        '1001A1100000000CB6E4', '1001AQ100000000CB7XO',
        '1001AQ100000000CC3KJ', '1001A1100000000CB6E1'))

----------------------------------------------------------------------------------------------


----------------------------------------------------------------------------------------------

--成本要素科目对照
select a.pk_glrapport, a.subjcode, b.subjname, a.itemcode, c.itemname
  from fc_glrapport a
  left join bd_accsubj b on a.subjcode = b.subjcode
  left join fc_costitemdefine c on a.itemcode = c.itemcode
 where (b.Pk_Glorgbook = '0001')
    or (b.pk_corp is null And b.Pk_Glorgbook is null)
 order by a.subjcode;

select a.pk_glrapport, a.subjcode, b.subjname, a.itemcode, c.itemname
  from fc_glrapport a, bd_accsubj b, fc_costitemdefine c
 where a.subjcode = b.subjcode(+)
   and a.itemcode = c.itemcode(+)
   and ((b.Pk_Glorgbook = '0001') or
       (b.pk_corp is null And b.Pk_Glorgbook is null))
 order by a.subjcode;
----------------------------------------------------------------------------------------------
--凡是公司不设置本位币的公司
select * from pub_sysinit where initname like '%本位币%' and pkvalue is null;
--将凭证子表中的垃圾数据清理掉
delete from gl_detail d where not exists( select pk_voucher from gl_voucher v where v.pk_voucher=d.pk_voucher);
--检查会计平台还存在未处理的实时凭证
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;


----------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------

--检查审批流是否定义
select pk_wf_def, fatherpk, processDefID, node_name, node_type, billmaker, billmaker_name, billmaker_type, createdate,
version, workflow_type from pub_wf_def where validation = 1 and pk_corp = '1001' and busitype = 'KHHH0000000000000001'
and
billtype = 'ZG54' and ( node_type = '2' or node_type = '3' ) 
----------------------------------------------------------------------------
--单据类型管理表,手工设置4位类型代码
select * From bd_billtype where pk_billtypecode='TB'
update bd_billtype set pk_billtypecode='TB10' where pk_billtypecode='TB'
----------------------------------------------------------------------------
--手工做vo对照_主表
insert into pub_votable(approveid,    billid,    billno,    billvo,  busitype,   def1,def2,
def3,dr,   headbodyflag,headitemvo,                itemcode,            operator,     pk_billtype,pk_corp,pkfield,
   votable,           pk_votable,            ts) 
                 values('vapproveid','pk_apply','vbillcode','nc.vo.trade.pub.HYBillVO','pk_busitype',NULL,NULL,NULL,
NULL,'Y',       'nc.vo.shsh.stock.tb1010.StockApplyVO','shsh_stock_apply',   'voperatorid','TB10',     NULL,
   'pk_apply','shsh_stock_apply','0001AA10000000001RK3','2007-09-27 16:59:14') 
--手工做vo对照_子表
insert into pub_votable(approveid,billid,billno,billvo,                    busitype,def1,def2,def3,dr,  headbodyflag,
headitemvo,                             itemcode,            operator,pk_billtype,pk_corp,pkfield,     votable,
              pk_votable,            ts) 
                 values('',       '',    '',    'nc.vo.trade.pub.HYBillVO','',      NULL,NULL,NULL,NULL,'N','nc.vo.
shsh.
stock.tb1010.StockApplyBVO','shsh_stock_apply_b','',      'TB10',     NULL,
   'pk_apply','shsh_stock_apply_b','0001AA10000000001RK4','2007-09-27 17:12:01') 
----------------------------------------------------------------------------
--自动生成单据编码
insert into pub_billcode_rule(billcodeshortname,controlpara,day,dr,isautofill,ischeck,ishaveshortname,ispreserve,
lastsn,
month,
object1,object2,pk_billcoderule,pk_billtypecode,snnum,snresetflag,ts,year)
values('ZA','Y',20,0,'Y','Y','Y','Y','0000',12,NULL,NULL,'smTB1000000000000001','TB10',4,
0,'2007-09-27 18:55:32','07')
----------------------------------------------------------------------------
--操作员人员关联关系
select psn.psnname from bd_psndoc psn inner join sm_userandclerk on sm_userandclerk.pk_psndoc = psn.pk_psndoc
inner join sm_user on sm_user.cuserid = sm_userandclerk.userid 
----------------------------------------------------------------------------
--查询模板的单据状态下列值初始化
update pub_query_condition set consult_code='I,,审批不通过,审批通过,审批进行中,提交态,作废态,冲销态,终止(结算)态,冻结
态,自由
态,待经办,已经办,已核查,已作废' 
where field_code like '%billstatus' and pk_templet in(
select id from pub_query_templet where model_code in ('91012010'))
----------------------------------------------------------------------------
--复制数据库里的类似数据，声称INSERT语句
select 'insert dap_defitem values('+attrname+','+CONVERT(char(1),dr)+','+headflag+','+itemname+','+CONVERT(char(1),
itemtype)
+','+pk_billtype+','+pk_voitem+','','+ts+')' from dap_defitem where pk_billtype='TB52'
----------------------------------------------------------------------------
--手动增加打印模板中的项
insert into PUB_PRINT_DATAITEM (DR, IDATATYPE, ITYPE, PK_CORP, PK_VARITEM, PREPARE1, RESID, TS, USERDEFFLAG, VNODECODE,
VTABLECODE, VTABLENAME, VVAREXPRESS, VVARNAME)
values (0, null, null, [email=]'@@@@'[/email], '0001AA10000000ZY1220', null, null, '2007-01-21 15:23:37', null, 'TC0106', null, null,
't_pk_psndoc', '报销人(表尾)');
----------------------------------------------------------------------------
--修改单据模板模板的字段类型
update pub_billtemplet_b set reftype='5'
where pk_billtemplet in
(select pk_billtemplet from pub_billtemplet where nodecode like 'TD%')
and datatype=2 and (reftype is null or reftype='3' or reftype='2') and
(defaultshowname like '%费%' or defaultshowname like '%额%'
or defaultshowname like '%金%' or defaultshowname like '%价%')
----------------------------------------------------------------------------
--参照的表和字段
select pk_refcolumn, fieldname, fieldshowname, ishiddenfield, isblurfield, ismnecode, 
ispkfield, iscolumnshow, islocateshow, columnshowindex, locateshowindex,
bd_refcolumn.pk_reftable,datatype,tablename from 
bd_refcolumn inner join bd_reftable on bd_refcolumn.pk_reftable = bd_reftable.pk_reftable 
where bd_refcolumn.pk_reftable = '0001AA1000000000P4LA'  order by columnshowindex 
--查询有节点权限的人员
-----------------------------------------------------------------------------
select * from sm_funcregister
select user_name from sm_user where cuserid in(
select userid from sm_appuserpower where funid='0001AA10000000000UDO'
)
--查询有节点权限的用户组
select g.group_name,g.pk_corp from sm_group g where cgroupid in(
select groupid from sm_usergrouppower where funid='0001AA10000000000WKR'
)
--审批流相关表
----------------------------------------------------------------------------
select * from pub_wf_instance where billid='' and billno=''
select * from pub_wf_task where pk_wf_instance=''
select * from pub_wf_actinstance where pk_wf_instance=''
select * from pub_workflownote where billid='' and billno=''
select * from pub_wf_actinstancesrc where target_actinstance=pub_wf_actinstance.pk_wf_actinstance
--由于各表外键约束,自行调节删除顺序