用友NC常用SQL查询语句标题:用友NC常用SQL查询语句
//检查审批流是否定义
selectpk_wf_def,fatherpk,processDefID,node_name,node_type,billmaker,billmaker_name,billmaker_type,createdate,
version,workflow_typefrompub_wf_defwherevalidation=1andpk_corp='1001'andbusitype='KHHH0000000000000001'
and
billtype='ZG54'and(node_type='2'ornode_type='3')
----------------------------------------------------------------------------
//单据类型管理表,手工设置4位类型代码
select*Frombd_billtypewherepk_billtypecode='TB'
updatebd_billtypesetpk_billtypecode='TB10'wherepk_billtypecode='TB'
----------------------------------------------------------------------------
//手工做vo对照_主表
insertintopub_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-2716:59:14')
----------------------------------------------------------------------------
//手工做vo对照_子表
insertintopub_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-2717:12:01')
----------------------------------------------------------------------------
--自动生成单据编码
insertintopub_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-2718:55:32','07')
----------------------------------------------------------------------------
//操作员人员关联关系
selectpsn.psnnamefrombd_psndocpsninnerjoinsm_userandclerkonsm_userandclerk.pk_psndoc=psn.pk_psndoc
innerjoinsm_useronsm_user.cuserid=sm_userandclerk.userid
----------------------------------------------------------------------------
//查询模板的单据状态下列值初始化
updatepub_query_conditionsetconsult_code='I,,审批不通过,审批通过,审批进行中,提交态,作废态,冲销态,终止(结算)态,冻结
态,自由
态,待经办,已经办,已核查,已作废'
wherefield_codelike'%billstatus'andpk_templetin(
selectidfrompub_query_templetwheremodel_codein('91012010'))
----------------------------------------------------------------------------
//复制数据库里的类似数据,声称INSERT语句
select'insertdap_defitemvalues('+attrname+','+CONVERT(char(1),dr)+','+headflag+','+itemname+','+CONVERT(char(1),
itemtype)
+','+pk_billtype+','+pk_voitem+','','+ts+')'fromdap_defitemwherepk_billtype='TB52'
----------------------------------------------------------------------------
//手动增加打印模板中的项
insertintoPUB_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-2115:23:37',null,'TC0106',null,null,
't_pk_psndoc','报销人(表尾)');
----------------------------------------------------------------------------
//修改单据模板模板的字段类型
updatepub_billtemplet_bsetreftype='5'
wherepk_billtempletin
(selectpk_billtempletfrompub_billtempletwherenodecodelike'TD%')
anddatatype=2and(reftypeisnullorreftype='3'orreftype='2')and
(defaultshownamelike'%费%'ordefaultshownamelike'%额%'
ordefaultshownamelike'%金%'ordefaultshownamelike'%价%')
----------------------------------------------------------------------------
//参照的表和字段
selectpk_refcolumn,fieldname,fieldshowname,ishiddenfield,isblurfield,ismnecode,
ispkfield,iscolumnshow,islocateshow,columnshowindex,locateshowindex,
bd_refcolumn.pk_reftable,datatype,tablenamefrom
bd_refcolumninnerjoinbd_reftableonbd_refcolumn.pk_reftable=bd_reftable.pk_reftable
wherebd_refcolumn.pk_reftable='0001AA1000000000P4LA'orderbycolumnshowindex
//查询有节点权限的人员
-----------------------------------------------------------------------------
select*fromsm_funcregister
selectuser_namefromsm_userwherecuseridin(
selectuseridfromsm_appuserpowerwherefunid='0001AA10000000000UDO'
)
//查询有节点权限的用户组
selectgroup_name,*fromsm_groupwherecgroupidin(
selectgroupidfromsm_usergrouppowerwherefunid='0001AA1000000000LL7G')
----------------------------------------------------------------------------
//审批流相关表
----------------------------------------------------------------------------
select*frompub_wf_instancewherebillid=''andbillno=''
select*frompub_wf_taskwherepk_wf_instance=''
select*frompub_wf_actinstancewherepk_wf_instance=''
select*frompub_workflownotewherebillid=''andbillno=''
select*frompub_wf_actinstancesrcwheretarget_actinstance=pub_wf_actinstance.pk_wf_actinstance
//查找主体账簿下的科目
selectbd_accsubj.cashbankflag,bd_accsubj.currency,bd_accsubj.dispname,bd_accsubj.endflag,bd_accsubj.pk_accsubj,bd_accsubj.pk_corp,bd_accsubj.subjcode,bd_accsubj.subjname,bd_accsubj.subjlevfrombd_accsubjwherebd_accsubj.pk_subjtype=?andbd_accsubj.subjlev=?and(bd_accsubj.dr='0'orbd_accsubj.drisnull)andbd_accsubj.pk_glorgbook=(selectpk_glorgbookfrombd_glorgbookwherepk_glorg=(selectPK_GLORGfrombd_glorgwherepk_entityorg=?)