用友NC常用SQL查询语句

//检查审批流是否定义

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
group_name,*
from
sm_group
where
cgroupid
in( select
groupid
from
sm_usergrouppower
where
funid='0001AA1000000000LL7G')

//审批流相关表

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

//查找主体账簿下的科目

 select
bd_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.subjlev
from
bd_accsubj
where
bd_accsubj.pk_subjtype
=
?
and
bd_accsubj.subjlev
=
?
and
(bd_accsubj.dr
=
'0'
or
bd_accsubj.dr
is
null
)

and
bd_accsubj.pk_glorgbook
=
(select
pk_glorgbook
from
bd_glorgbook
where
pk_glorg
=
(select
PK_GLORG
from
bd_glorg
where
pk_entityorg
=
?)

转载于：http://www.kuaiji66.com/nc/yongyouncjituan/6EJNL7eC5986.html