some sql

最新推荐文章于 2024-05-10 17:17:42 发布

weinanbu

最新推荐文章于 2024-05-10 17:17:42 发布

阅读量619

点赞数

本文链接：https://blog.csdn.net/kidd888/article/details/53079448

版权

--用户表

select * from sys_user ;

--公司、部门、处室（组织结构）

select * from SYS_GROUP where SYS_GROUP.GROUPID=102205;

--用户角色表

select * from SYS_USER_ROLE where SYS_USER_ROLE.USER_ID=1133;

--角色表

select * from SYS_ROLE where SYS_ROLE.ROLEID=321;

--菜单表

select * from SYS_FUNCTION where SYS_FUNCTION.FUNCTIONID =1;

--角色菜单表

select * from SYS_ROLEFUNC;

--流程里的权限

select * from sys_user_position where positionID=9 and sys_user_position.GROUPID = 1018;

--字段映射表

select * from T_RMBS_ITEM_FLD where T_RMBS_ITEM_FLD.ITEM_ID='T075';

--报账单头信息--

SELECT * FROM T_RMBS_CLAIM WHERE T_RMBS_CLAIM.CLAIM_NO='101614063000500200';

--报账单行信息

SELECT * FROM T_RMBS_CLAIM_LINE where CLAIM_ID=143823;

--付款清单信息

SELECT * FROM T_RMBS_PAYLIST WHERE CLAIM_NO='';

--付款清单

SELECT

T_RMBS_CLAIM.CLAIM_ID,

T_RMBS_CLAIM.CLAIM_NO,

T_RMBS_CLAIM.VENDOR_NAME,

T_RMBS_CLAIM.BANK_ACCOUNT_NAME,

T_RMBS_CLAIM.VENDOR_BANK,

T_RMBS_CLAIM.VENDOR_ACCOUNT_NO,

T_RMBS_CLAIM.APPLY_AMOUNT,

T_RMBS_CLAIM.INVOICE_TYPE ,

T_RMBS_CLAIM.ITEM2_NAME,

T_RMBS_CLAIM.PAY_OBJECT

FROM

T_RMBS_CLAIM

WHERE

T_RMBS_CLAIM.CLAIM_NO IN ('101114110209003438');

--报账单类型

select * from T_RMBS_TEMPLATE;

--业务大类

select * from T_CO_ITEMLEVEL2;

--业务小类产品必填：REQUIRED_CONTROL=1

select * from T_CO_ITEMLEVEL3;

--合同--

SELECT * FROM T_SRV_CONTRACTINFO WHERE CONTRACT_NUM='移学院采购字普通合同 [2014] 060 号';

--合同行

SELECT * FROM T_SRV_CONTRACTINFO_LINES WHERE CONTRACT_NUM='移有限采购合同 [2014] 315 号';

--合同表（旧）

select * from DB2INST1.T_RMBS_CONTRACTINFO;

--预算多维映射--

SELECT * FROM T_BUDGET_INQMAPINFO WHERE PROJECT_NUM='0311ZD14031811000015';

--预算项目--

SELECT * FROM T_BUDGET_INQPRJINFO WHERE PROJECT_NUM='0311ZD14031811000015';

--预算责任部门--

SELECT * FROM T_BUDGET_INQPRJMAPDPTINFO WHERE PROJECT_NUM='0311ZD14031811000015';

--报账系统预算模块--

SELECT * FROM T_LMT_BUDGET WHERE BUDGET_ID='';

--报账系统预算模块部门关系--

SELECT * FROM T_LMT_BUDGET_GROUP WHERE BUDGET_ID='';

--报账系统预算历史表

SELECT

T_LMT_BUDGET_HIS.MODIFY_DATE 修改日期,

T_LMT_BUDGET_HIS.MODIFY_USER_ID 修改人,

T_LMT_BUDGET_HIS.BUDGET_ID 预算编号,

T_LMT_BUDGET_HIS.BUDGET_NAME 预算名称,

T_LMT_BUDGET_HIS.GROUP_ID 部门id,

T_LMT_BUDGET_HIS.GROUP_NAME 部门名称,

T_LMT_BUDGET_HIS.USER_GROUP_ID ,

T_LMT_BUDGET_HIS.USER_GROUP_NAME,

decode(T_LMT_BUDGET_HIS.OP_TYPE,'02','冻结','03','更改冻结','04','解冻','05','占用','06','更改预算') 变更操作,

T_LMT_BUDGET_HIS.CLAIM_NO 报账单编号,

T_LMT_BUDGET_HIS.CLAIM_ID 报账单ID,

T_LMT_BUDGET_HIS.CLAIM_LINE_ID 报账单行ID,

T_LMT_BUDGET_HIS.CHANGE_AMOUNT 变动金额,

T_LMT_BUDGET_HIS.BUDGET_AMOUNT 预算金额,

T_LMT_BUDGET_HIS.OCCUPIED_AMOUNT 占用金额,

T_LMT_BUDGET_HIS.BLOCKED_AMOUNT 冻结金额,

T_LMT_BUDGET_HIS.BUDGET_BALANCE 预算余额,

T_LMT_BUDGET_HIS.DESC,

T_LMT_BUDGET_HIS.CHANGE_TAX_AMOUNT

FROM

T_LMT_BUDGET_HIS

WHERE

T_LMT_BUDGET_HIS.BUDGET_ID='PL20140020010014';

--预算控制

IS_BUDGET_CONTROL=0 不加控制

--供应商--

SELECT * FROM T_MIS_INQVENDOR WHERE VENDOR_NUMBER='103344';

SELECT * FROM T_MIS_INQVENDORSITE WHERE VENDOR_ID=5606;

--流程模板（xml文件）

select * from PROCESS_TEMPLATE;

--流程是否已经完成

select * from PROCESS_INSTANCE where PROCESS_INSTANCE.PROCESS_INSTANCE_ID=xxx;

--审批时间和环节待办--

SELECT * FROM T_ENGINE_PROCESS_WORKITEM WHERE DOMINO_DOCID='140253';

--记录ID--

SELECT * FROM T_ENGINE_ACTIVITY_INSTANCE WHERE PROCESS_INSTANCEID=124878;

--审批意见

SELECT * FROM T_ENGINE_PROCESS_COMMENT where PROCESS_INSTANCEID=124878;

--已办--

SELECT * FROM T_UNIFIED_WORKITEM WHERE PROCESS_INSTANCEID=124878;

--CONTEXT_INSTANCE.getHandlerLevel() SYS_GROUP表的GROUP_LAYER字段

--会计表--

SELECT * FROM T_PROC_COMP_ACCOUNT WHERE T_PROC_COMP_ACCOUNT.COMP_ID='1016';

--审批金额配置表--

SELECT * FROM T_PROC_AMOUNT WHERE T_PROC_AMOUNT.COMP_ID='1011';

--归口部门归口经办人配置--

SELECT * FROM T_PROC_PROVINCE_BIZDEPT;

--分管领导配置--

SELECT * FROM T_PROC_DIRECTOR WHERE T_PROC_DIRECTOR.COMP_ID='1011'

--财务部负责人--

SELECT * FROM T_PROC_PROVINCE_FMGR;

--额度标准--

select * from T_LMT_DUTY_QUOTA where comp_id = '1016';

--用户额度--

select * from T_QT_USER_QUOTA where T_QT_USER_QUOTA.USER_ID = 1826;

--下拉列表码表

select * from T_RMBS_DICT;

--根据业务小类的业务活动编码（BUSINESS_ACTION_CODE）判断业务活动是否可抵扣

SELECT T_RMBS_CLAIM.ITEM3_ID,T_RMBS_CLAIM.APPLY_COM_ID FROM T_RMBS_CLAIM WHERE T_RMBS_CLAIM.CLAIM_NO='101014071100900012';

select BUSINESS_ACTION_CODE from T_CO_ITEMLEVEL3 where T_CO_ITEMLEVEL3.ITEM_ID='009001001' and T_CO_ITEMLEVEL3.COMP_ID='1010';

SELECT T_BUDGET_INQACTINFO.OFFSET_FLAG,decode(T_BUDGET_INQACTINFO.OFFSET_FLAG,'Y','可抵扣','不可抵扣') FROM DB2INST1.T_BUDGET_INQACTINFO where T_BUDGET_INQACTINFO.ACTIVITY_CODE='Z01';

--上了预算系统的根据OFFSET_FLAG判断是否可抵扣没上预算系统的根据IS_HAS_VOUCHER判断

--如果(政企)BUSINESS_ACTION_CODE为空则根据IS_HAS_VOUCHER判断 IS_HAS_VOUCHER=1 为可抵扣 IS_HAS_VOUCHER=0 不可抵扣

--采购订单

select * from T_MIS_ORDER_DISTRIBUTIONINFO where T_MIS_ORDER_DISTRIBUTIONINFO.SEGMENT1='8180000183' ;

select * from T_MIS_ORDER_HEADERINFO where T_MIS_ORDER_HEADERINFO.SEGMENT1='8180000183' and T_MIS_ORDER_HEADERINFO.CLOSED_CODE='OPEN';

select * from T_MIS_ORDER_LINEINFO where T_MIS_ORDER_LINEINFO.SEGMENT1='8180000183' AND T_MIS_ORDER_LINEINFO.CLOSED_CODE='OPEN';

select * from T_MIS_ORDER_LINELOCATIONINFO where T_MIS_ORDER_LINELOCATIONINFO.SEGMENT1='8180000183' AND T_MIS_ORDER_LINELOCATIONINFO.QUANTITY_RECEIVED>T_MIS_ORDER_LINELOCATIONINFO.QUANTITY_BILLED;

--增值税扣税凭证表发票表

select * from T_VAT_INVOICEAUTH where T_VAT_INVOICEAUTH.CLAIM_ID=151176;

--订单信息

select * from T_RMBS_POLINESREL where T_RMBS_POLINESREL.CLAIM_ID=149219;

select * from T_RMBS_POREL where T_RMBS_POREL.CLAIM_ID=149219;

--所有成本中心

select FLEX_VALUE,Description from T_MIS_INQVSETVALUEINFO where FLEX_VALUE_SET_NAME LIKE '%COA_CC' order by FLEX_VALUE;

--流程序列

values nextval for SEQ_WORKITEM;

--差旅明细信息

select * from T_RMBS_TRAVELMES where T_RMBS_TRAVELMES.CLAIM_NO='101014092308700469';

--集团业务大小类

SELECT

a.item_id 报账单类型编号,

a.item_name 报账单类型名称,

b.item_id 业务大类id,

b.item_name 业务大类名称,

c.item_id 业务小类id,

c.item_name 业务小类名称,

b.description 业务大类描述,

c.description2 小类描述,

c.ap_dr_seg 应付借方,

c.ap_dr_seg_code 应付借方代码,

c.gl_dr_seg 总装借方,

c.gl_dr_seg_code 总账借方代码,

c.gl_cr_seg 总账贷方,

c.gl_cr_seg_code 总账贷方代码,

c.ar_cr_seg 应收贷方,

c.ar_cr_seg_code 应收贷方代码,

c.comp_id 公司id,

b.IS_BUDGET_CONTROL 预算控制标识,

c.business_action_code 业务活动代码,

c.business_action_name 业务活动名称,

b.province_piid 对应的流程模板

FROM

T_CO_ITEMLEVEL3 c,

T_CO_ITEMLEVEL2 b,

T_RMBS_TEMPLATE a

WHERE

c.father_item = b.item_id

AND c.comp_id = '1010'

AND b.father_item = a.item_id

AND b.comp_id = '1010'

ORDER BY

b.item_id,

c.item_id

--将resolution 设置为空即可

select * from jiraissue where jiraissue.ASSIGNEE='buweinan' order by jiraissue.issuenum desc;

--导入ERP

1.INVOICE_TYPE 导入应付：1、9、17 导入总账：2 不导入：8

2.IMPORT_RINV_STATUS 值从1 改为null 是否退换票标示,0-未退换票,1-有退换票,2-退换票失败

--增值税认证通过

UPDATE

DB2INST1.T_RMBS_CLAIM

SET

T_RMBS_CLAIM.INVOICE_AUTH_STATE='1'

WHERE

T_RMBS_CLAIM.CLAIM_NO IN ('102115081800501026');

UPDATE

DB2INST1.T_VAT_INVOICEAUTH

SET

T_VAT_INVOICEAUTH.INVOICE_RESULT='1' ,

T_VAT_INVOICEAUTH.INVOICE_RESULT_DESC='认证通过'

WHERE

T_VAT_INVOICEAUTH.CLAIM_NO IN ('102115081800501026');

--加审批意见

SELECT

nextval FOR SEQ_ENGINE_PROCESS_COMMENT,

a.PROCESS_INSTANCEID,

a.STATE_ID,

a.STATE_NAME,

'0',

'同意',

a.HANDLER_NAME,

a.ORIG_HANDLER_USERID,

a.END_TIME,

'0',

'',

a.HANDLER_ORGID,

a.ACTIVITY_INSTANCEID

FROM

T_ENGINE_PROCESS_WORKITEM a

WHERE

a.STATE_ID='step70'

AND PROCESS_INSTANCEID IN

(

SELECT

t_rmbs_claim.PROCESS_INSTANCE_ID

FROM

t_rmbs_claim

WHERE

t_rmbs_claim.CLAIM_NO IN ('101115110507400426'));

--出国费预算申请表

select * from T_RMBS_INTERNATIONALTRAVEL_COST where T_RMBS_INTERNATIONALTRAVEL_COST.CLAIM_NO='101115101310800032';

select * from T_RMBS_INTERNATIONALTRAVEL_INFO where T_RMBS_INTERNATIONALTRAVEL_INFO.CLAIM_NO='101115101310800032';

select * from T_RMBS_OTHEREXPENSES where T_RMBS_OTHEREXPENSES.CLAIM_NO='101115101310800032';

select * from T_RMBS_EXPENDITURE_ITEMS where T_RMBS_EXPENDITURE_ITEMS.CLAIM_NO='101115101310800032';

--查看表的索引

select * from SYSCAT.INDEXES where TABNAME='T_SRV_CONTRACTINFO_LINES';

--流程结束的报账单退回到核算会计

UPDATE

T_RMBS_CLAIM

SET

T_RMBS_CLAIM.ERP_IMPORT_STATUS='0' ,

T_RMBS_CLAIM.ERP_IMPORT_STATUS_NAME='未导入',

T_RMBS_CLAIM.PROCESS_STATE_ENG='step202',

T_RMBS_CLAIM.PROCESS_STATE='核算会计审核',

T_RMBS_CLAIM.CUR_ACTOR_ID=','||T_RMBS_CLAIM.AC_ID||',',

T_RMBS_CLAIM.CUR_ACTOR_NAME=''||T_RMBS_CLAIM.AC_NAME||','

WHERE

T_RMBS_CLAIM.CLAIM_NO IN ('101816062707100004');

UPDATE

PROCESS_WORKITEM

SET

PROCESS_WORKITEM.STATUS=2002 ,

PROCESS_WORKITEM.END_TIME=NULL ,

PROCESS_WORKITEM.NEXT_ACTIVITIES_INFO=NULL

WHERE

PROCESS_WORKITEM.PROCESS_INSTANCEID IN

(

SELECT

PROCESS_INSTANCE_ID

FROM

T_RMBS_CLAIM

WHERE

T_RMBS_CLAIM.CLAIM_NO IN ('101014072401800356'))

AND PROCESS_WORKITEM.STATE_ID='step202';

UPDATE

PROCESS_INSTANCE

SET

PROCESS_INSTANCE.STATUS='2002'

WHERE

PROCESS_INSTANCE.PROCESS_INSTANCE_ID IN

(

SELECT

T_RMBS_CLAIM.PROCESS_INSTANCE_ID

FROM

T_RMBS_CLAIM

WHERE

T_RMBS_CLAIM.CLAIM_NO IN ('101816062707100004'));

UPDATE

T_ENGINE_ACTIVITY_INSTANCE

SET

T_ENGINE_ACTIVITY_INSTANCE.STATUS=2002

WHERE

T_ENGINE_ACTIVITY_INSTANCE.STATE_ID='step202'

AND T_ENGINE_ACTIVITY_INSTANCE.PROCESS_INSTANCEID IN

(

SELECT

T_RMBS_CLAIM.PROCESS_INSTANCE_ID

FROM

T_RMBS_CLAIM

WHERE

T_RMBS_CLAIM.CLAIM_NO IN ('101816062707100004'))

weinanbu

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫