NC常用SQL

select * from sm_product_version
where 1=1
for update;

select * from sm_codetocode
where 1=1
for update;

sm_createcorp

select * from dap_dapsystem
where 1=1
for update;
-------------------------------------------功能注册--a
select * from sm_funcregister a 
where 1=1
 and a.fun_code like 'HF00%'
/* and a.fun_name like '%销售出库%'*/
/* and a.class_name like '%ExcelImportUI%'*/
and a.fun_property = 0
for update

select * from sm_butnregister ttt where ttt.fun_code like 'HF%'
-------------------------------------------单据类型--b
select * from bd_billtype b 
where 1=1
 and b.pk_billtypecode like '%HFEJ%'
/* and b.nodecode = 'MD040215'*/
/* and b.billtypename like '%%'*/
/* and b.systemcode = 'GSP'*/
for update

-------------------------------------------VO对照
select * from pub_votable
where 1=1
/*and pub_votable.pk_billtype like 'HFI1%'*/ 
and pub_votable.pk_billtype in ('HFH0') 
/*and pub_votable.billvo like 'nc.vo.gsp.hf05640505.GspReviewHeadVO'*/
for update

-------------------------------------------单据号
select * 
from pub_billcode_rule t 
where 1=1
and t.pk_billtypecode like '%HFD1%'
for update

-------------------------------------------模板分配
select * from pub_systemplate
where 1=1
/*and pub_systemplate.templateid = '0001ZZ1000000001IEIA'*/
and pub_systemplate.funnode like '2006030102%'
and pub_systemplate.tempstyle = 0
for update
-------------------------------------------单据模板--c
--主表
select * from pub_billtemplet c1 
where 1=1
/* and c1.pk_billtemplet = 'BD01AA10000000004IPS'*/
 and c1.pk_billtypecode like 'HFEJ%'
/* and c1.nodecode like '%HF057205%'*/
/* and c1.bill_templetcaption like '%%'*/
order by c1.nodecode
for update
--子表
select c2.* 
from pub_billtemplet_b c2 
where 1=1
 and c2.pk_billtemplet = '0001Y01000000002ID39'
/* and c2.pk_corp = '@@@@'*/
/* and c2.itemkey in ('corigin','vapprovalno','pk_batchcode') */
order by c2.pos,c2.showorder
for update

--页签
select * from pub_billtemplet_t c3 
where 1=1
 and c3.pk_billtemplet = '0001Y01000000002HVVW' 
for update
-------------------------------------------查询模板--d
select * from pub_query_templet d1 
where 1=1
and d1.node_code='HF05721005 '
/*and e1.model_code = ''*/
for update

select * 
from pub_query_condition d2
where 1=1
and d2.pk_templet = '0001Y0100000000321UW'
/*and d2.field_code like '%gsp_receive_b.%'*/
order by d2.disp_sequence,d2.field_code
for update

--更新查询模板参照显示类型和返回类型
update pub_query_condition d2
set d2.disp_type=1,d2.return_type = 2
where d2.pk_templet = '0001ZZ1000000001MBTH'
and d2.data_type = 5;

--更新查询模板子表查询条件
update pub_query_condition d2
set d2.instrumentsql = 'mcm_splx_splclrd.cgeneralhid in (select cgeneralhid from mcm_splx_splclrd_b where nvl(dr,0)=0 and ???)'
where d2.pk_templet = '0001ZZ1000000001MBTH'
and d2.field_code like 'mcm_splx_splclrd_b.%'

-------------------------------------------打印模板--e
select * from pub_print_template e1 
where 1=1
 /*and e1.vtemplatecode like '%MD010150%'*/
 and e1.vnodecode = 'HF00050510'
/* and e1.vtemplatename like '%药品品种%'*/
for update

select * from pub_print_cell e2 
where 1=1
and e2.ctemplateid = '0001Y010000000032DN5'
and e2.vvar is not null
for update

select * from pub_print_dataitem e3 
where 1=1
and e3.vnodecode = 'HF05361003' 
/*and e3.vvarexpress not like 'h_%'*/
and e3.vvarexpress like 'b_%'
for update

select * from pub_print_datasource e4
where 1=1
and e4.ctemplateid = '0001ZZ1000000001LSBT'
for update

select * from pub_print_line e5
where e5.ctemplateid = '0001ZZ1000000001LNP8'

select * from pub_print_variable e6
where e6.ctemplateid = '0001ZZ1000000001LNP8'

-------------------------------------------报表模板--e
select * from pub_report_templet prt 
where prt.parent_code in ('MDH40405')
for update

select * from pub_report_model prm
where prm.pk_templet in ('0001ZZ1000000002B7DH')
and prm.node_code = 'MD040405'
/*and prm.column_code = 'qrycondition'*/
for update

--------------------------------------------单据动作及脚本--f
select * from pub_billaction f1 
where 1=1
 and f1.pk_billtype = 'HFIG'
 and f1.actiontype = 'SAVE' 
for update

select * from pub_busiclass f2
where 1=1
and f2.pk_billtype like 'HFIG%' 
for update

-------------------------------------------流程配置
select * from bd_busitype
where bd_busitype.busicode in ('HFD1','HFH0');
                           
select * from pub_billbusiness
where pub_billbusiness.pk_businesstype in (
    select pk_busitype from bd_busitype
    where bd_busitype.busicode in ('HFD1','HFH0')
);

select * from pub_billsource
where pub_billsource.billbusinessid in (
    select pk_billbusiness from pub_billbusiness
    where pub_billbusiness.pk_businesstype in (
        select pk_busitype from bd_busitype
        where bd_busitype.busicode in ('HFD1','HFH0')
    )
);
-------------------------------------------数据交换
select * from pub_billtobillrefer
where 1=1
and (
pub_billtobillrefer.billtype in ('HFD1','HFH0')
or pub_billtobillrefer.sourcebilltype in ('HFD1','HFH0')
)

select * from pub_function
where 1=1
and pub_function.pk_billtype in ('GS16')

select * from dap_defitem 
where dap_defitem.pk_billtype in ('GS16')

-------------------------------------------数据字典

select * from pub_datadict
where 1=1
/* and pub_datadict.display like '%出库%'*/
 and pub_datadict.guid like 'ysgl%' /*arap_qryobjval*/
for update

select * from pub_datadictdetail 
where pub_datadictdetail.reserved2 = 'pk_qryobjval'



-------------------------------------------自定义项
select * from bd_defdef
where bd_defdef.defcode like 'MEDPUB023%'

select * from bd_defused
where 1=1
and bd_defused.objcode = 'HFKI_head'
 and bd_defused.objname like '%销售发票%'
/* and bd_defused.pk_defused='MD010210BILLBODY0001'*/
for update

select bd_defquote.* from bd_defquote
where bd_defquote.pk_defused='0001Y01000000000JR1Y'
order by to_number(replace(fieldname,'vdef',''))
for update

select * from bd_defcsttblnme
where bd_defcsttblnme.pk_defused='aaa'
for update
-------------------------------------------自定义档案

select * from bd_defdoclist
where bd_defdoclist.doclistname like '%特殊%'

select * from bd_defdoc
where bd_defdoc.pk_defdoclist = '0001ZZ1000000001EV22'

-------------------------------------------系统参照
select * from bd_refinfo 
where 1=1
and bd_refinfo.name like '%业务%'
/*and bd_refinfo.module = 'med' */
for update

bd_refcolumn
-------------------------------------------数据交换
select * from pub_billtobillrefer
where 1=1
and 
(pub_billtobillrefer.billtype = 'HFKL'
or pub_billtobillrefer.sourcebilltype = 'HFKL'
) 

select * from pub_function
where 1=1
and pub_function.pk_billtype = 'GS19'
for update

select * from dap_defitem 
where dap_defitem.pk_billtype = 'HFE0' 
/*and dap_defitem.headflag='N' */
for update

-------------------------------------------基本档案后台校验类
select * from bd_interfaceexec
where 1=1
and bd_interfaceexec.funcode like '%HF%'
and bd_interfaceexec.opertype = 3
-------------------------------------------

select *
  from bd_ref_relation refra
 where (refra.referencedtablename like '%batchcode%' or
       refra.referencedtablekey like '%batchcode%')
for update;