NC常用表以及SQL

x114944880

//检查审批流是否定义
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
//由于各表外键约束,自行调节删除顺序

daniel_chengdu

感谢分享  

daniel_chengdu

感谢分享  

game2000

这个好，谢谢了

百里登风

感谢分享

mjf21cn

不错，谢谢！

lliuhuii

没看到常用的gl-freevalue

wf7342151

不错，不错

victory_lp

不错 ，这个不用下载，顶一下

米修

不错不错不错  就是看不懂额

duckular_1982

学习 学习 谢谢

150005413

账户信息不唯一

george1229

不错，好东西，谢谢^_^

guoyonglei

不错，好东西，谢谢^_^

l58450190

谢谢分享受。下来好好看一下

cyclone111202

学习了，收藏了

sl2182395

不错，好东西，谢谢^_^

ttom

二开的东西，有些意思

sandhiller

牛，坚决支持。谢谢。