Node.js 调存储过程
1 var spring = require("spring");2 //当前登录人ID
3 var account_id = require('nodejava').toJs.parse(com.tt.pwp.framework.security.SecurityUtils.getLoginAccount()).attributes.ACCOUNT_ID;
4 var result ={
5 flag:false,
6 cont:"操作失败"
7 }
8
9
10 /**
11* 需要 进行经济分类 会计辅助核算
12*/
13 var pzData_jjfl=param.pzData_jjfl;
14 var fdate_jjfl = pzData_jjfl.fdate;//凭证日期
15 var fattachment_jjfl = pzData_jjfl.fattachment;//附件
16 var strOptionType_jjfl = pzData_jjfl.strOptionType;//操作类型
17 var oper_idea_jjfl = pzData_jjfl.oper_idea;//审核意见
18 var delIds_jjfl = pzData_jjfl.delIds;
19 var zfpzid_jjfl = pzData_jjfl.zfpzid;
20 var check_status_jjfl = pzData_jjfl.check_status;
21 var voucher_no_jjfl = pzData_jjfl.voucher_no;
22 var voucher_type_jjfl = pzData_jjfl.voucher_type;
23 var amt_jjfl = pzData_jjfl.amt;
24 var arrpurpose_jjfl = pzData_jjfl.arrpurpose;
25 var mappingtable_jjfl = pzData_jjfl.mappingtable;
26
27 var lstVo_jjfl = new java.util.ArrayList();
28 //只有书面银行回单的,手工对碰.传递支付凭证编号和加章信息
29 for (var i = 0; i < zfpzid_jjfl.length; i++) {
30 for (var j = 0; j < delIds_jjfl.length; j++) {
31 if (zfpzid_jjfl==(delIds_jjfl)) {
32 var vo = new com.todaytech.yth.gdsd.gkzf.k3.model.ZfpzVO();
33 vo.setGk_zfpz_id(parseInt(zfpzid_jjfl));
34 vo.setVoucher_no(voucher_no_jjfl); //支付凭证号
35 vo.setVoucher_type(voucher_type_jjfl);//凭证类型
36 vo.setAmt(new java.math.BigDecimal(amt_jjfl));//拨款金额
37 vo.setPurpose(oper_idea_jjfl.replace(/^\s\s*/, '').replace(/\s\s*$/, '').length!=0?/*"("+oper_idea+")("+*/arrpurpose_jjfl/*+")"*/:arrpurpose_jjfl);//用途说明----用于摘要
38 vo.setCheck_status(check_status_jjfl); // 对碰状态
39 vo.setMappingTable(mappingtable_jjfl);//来源表
40
41
42 //用于正常回单生成会计凭证和退款以及调帐管理生成会计凭证 和汇总清算通知单
43 if (strOptionType_jjfl == (com.todaytech.yth.gdsd.gkzf.k3.ReceiveBillEnum.GK_SQZFPZ_CHECK_STATUS_SUCCEED_AUTPAY/*="0"; //正常对碰的直接支付凭证*/) || strOptionType == (com.todaytech.yth.gdsd.gkzf.k3.ReceiveBillEnum.GK_SQZFPZ_CHECK_STATUS_SUCCEED_PAYEVI_AND_ROXYBILL/*="1"; //正常对碰的授权支付凭证和代扣代缴*/)) {
44 vo.setAffirmer(parseInt(account_id)); //回单确认人编号
45 //用于正常回单生成会计凭证和退款以及调帐管理生成会计凭证 和汇总清算通知单
46 } else if (strOptionType_jjfl == (com.todaytech.yth.gdsd.gkzf.k3.ReceiveBillEnum.GK_SQZFPZ_CHECK_STATUS_BACK_BILL_AUTPAY/*="2"; //退款的直接支付凭证*/) || strOptionType == (com.todaytech.yth.gdsd.gkzf.k3.ReceiveBillEnum.GK_SQZFPZ_CHECK_STATUS_BACK_BILL_PAYEVI_AND_ROXYBILL/*="3"; //退款的授权支付凭证和代扣代缴*/)) {
47 vo.setBack_oper_id(parseInt(account_id));//制单人userid
48 vo.setBack_oper_idea(oper_idea);//意见
49 }
50 lstVo_jjfl.add(vo);
51 }
52 }
53 }
54
55
56
57
58
59
60 /**
61* 不需要 进行经济分类 会计辅助核算
62*/
63 var pzData=param.pzData;
64 var fdate = pzData.fdate;//凭证日期
65 var fattachment = pzData.fattachment;//附件
66 var strOptionType = pzData.strOptionType;//操作类型
67 var oper_idea = pzData.oper_idea;//审核意见
68 var delIds = pzData.delIds;
69 var zfpzid = pzData.zfpzid;
70 var check_status = pzData.check_status;
71 var voucher_no = pzData.voucher_no;
72 var voucher_type = pzData.voucher_type;
73 var amt = pzData.amt;
74 var arrpurpose = pzData.arrpurpose;
75 var mappingtable = pzData.mappingtable;
76
77 var lstVo = new java.util.ArrayList();
78 //只有书面银行回单的,手工对碰.传递支付凭证编号和加章信息
79 for (var i = 0; i < zfpzid.length; i++) {
80 for (var j = 0; j < delIds.length; j++) {
81 if (zfpzid==(delIds)) {
82 var vo = new com.todaytech.yth.gdsd.gkzf.k3.model.ZfpzVO();
83 vo.setGk_zfpz_id(parseInt(zfpzid));
84 vo.setVoucher_no(voucher_no); //支付凭证号
85 vo.setVoucher_type(voucher_type);//凭证类型
86 vo.setAmt(new java.math.BigDecimal(amt));//拨款金额
87 vo.setPurpose(oper_idea.replace(/^\s\s*/, '').replace(/\s\s*$/, '').length!=0?/*"("+oper_idea+")("+*/arrpurpose/*+")"*/:arrpurpose);//用途说明----用于摘要
88 vo.setCheck_status(check_status); // 对碰状态
89 vo.setMappingTable(mappingtable);//来源表
90 //用于正常回单生成会计凭证和退款以及调帐管理生成会计凭证 和汇总清算通知单
91 if (strOptionType == (com.todaytech.yth.gdsd.gkzf.k3.ReceiveBillEnum.GK_SQZFPZ_CHECK_STATUS_SUCCEED_AUTPAY/*="0"; //正常对碰的直接支付凭证*/) || strOptionType == (com.todaytech.yth.gdsd.gkzf.k3.ReceiveBillEnum.GK_SQZFPZ_CHECK_STATUS_SUCCEED_PAYEVI_AND_ROXYBILL/*="1"; //正常对碰的授权支付凭证和代扣代缴*/)) {
92 vo.setAffirmer(parseInt(account_id)); //回单确认人编号
93 //用于正常回单生成会计凭证和退款以及调帐管理生成会计凭证 和汇总清算通知单
94 } else if (strOptionType == (com.todaytech.yth.gdsd.gkzf.k3.ReceiveBillEnum.GK_SQZFPZ_CHECK_STATUS_BACK_BILL_AUTPAY/*="2"; //退款的直接支付凭证*/) || strOptionType == (com.todaytech.yth.gdsd.gkzf.k3.ReceiveBillEnum.GK_SQZFPZ_CHECK_STATUS_BACK_BILL_PAYEVI_AND_ROXYBILL/*="3"; //退款的授权支付凭证和代扣代缴*/)) {
95 vo.setBack_oper_id(parseInt(account_id));//制单人userid
96 vo.setBack_oper_idea(oper_idea);//意见
97 }
98 lstVo.add(vo);
99 }
100 }
101 }
102
103
104 //用于生成会计凭证ZwvchVO
105 var zwvchvo= spring.getBean("ZwvchVO"); //
106
107 //会计凭证(zwvch)
108 zwvchvo.setFgroup("");// 凭证类型:"总"预算
109 zwvchvo.setFdate(new Date(fdate));//填制日期
110 //凭证号(FNUM):按月水,在ZwvchCreateHelper类中根据"表名(ZWVCH)和月份"自动生成
111 zwvchvo.setFpreparer(parseInt(account_id));//填制人(FPREPARER):为空
112 zwvchvo.setFchecker("");//审核人员(FCHECKER):为空
113 zwvchvo.setFposter("");//记帐人员(FPOSTER):为空
114 zwvchvo.setFattachment(fattachment);//附件张数(FATTACHMENT):
115 zwvchvo.setFmodule("");//接转凭证标志(FMODULE):为空
116 zwvchvo.setFdeleted("");//作废标记(FDELETED):为空
117 zwvchvo.setOrg_type(pzData.src_type);//资金来源类型
118
119 zwvchvo.setOpertype(pzData.org_type);//20180119 新增 (界面选择用到的)操作类型
120 if(pzData.org_type==0||pzData.org_type==1){
121 zwvchvo.setOrign_type("1");
122 }else if(pzData.org_type==2||pzData.org_type==3){
123 zwvchvo.setOrign_type("3");
124 }
125
126 //数据来源类型
127 try {
128 var facade = spring.getBean("zysZfzxPayCtl");// zwvchNewFacade
129
130 //用于正常回单生成会计凭证和退款以及调帐管理生成会计凭证 和汇总清算通知单
131 if (strOptionType == (com.todaytech.yth.gdsd.gkzf.k3.ReceiveBillEnum.GK_SQZFPZ_CHECK_STATUS_SUCCEED_AUTPAY/*="0"; //正常对碰的直接支付凭证*/)||
132 strOptionType == (com.todaytech.yth.gdsd.gkzf.k3.ReceiveBillEnum.GK_SQZFPZ_CHECK_STATUS_BACK_BILL_AUTPAY/*="2"; //退款的直接支付凭证*/)||
133 strOptionType == (com.todaytech.yth.gdsd.gkzf.k3.ReceiveBillEnum.GK_SQZFPZ_CHECK_STATUS_BACK_BILL_PAYEVI_AND_ROXYBILL/*="3"; //退款的授权支付凭证和代扣代缴*/) ||
134 strOptionType == (com.todaytech.yth.gdsd.gkzf.k3.ReceiveBillEnum.GK_SQZFPZ_CHECK_STATUS_SUCCEED_PAYEVI_AND_ROXYBILL/*="1"; //正常对碰的授权支付凭证和代扣代缴*/)) {
135 //正常对碰的直接支付凭证
136 //正常对碰的授权支付凭证和代扣代缴
137 /*var orign_type = com.todaytech.yth.gdsd.gkzf.k3.ReceiveBillEnum.GK_ZWVCH_REFER_ORIGN_TYPE_PAYEVI;
138 zwvchvo.setOrign_type(orign_type);
139 facade.createCZGKZwvch(zwvchvo, lstVo,lstVo_jjfl);*/
140
141
142 //获取GK_ZWVCH_IDS pk_id值
143 var pk_id=0;
144 var pk_idSql="select nextid('GK_ZWVCH_IDS_SEQ') nextidFROM DUAL";
145 var pk_idData = db.queryService.query(pk_idSql,{});
146 if(pk_idData.data.length>0){
147 for(var i=0;i<pk_idData.data.length;i++){
148 // 获取id
149 pk_id=pk_idData.data.get("nextid");
150 }
151 }
152
153 /** 开启一个新事务，把数据插入到业务表中
154 * 事务传播级别,共有PROPAGATION_REQUIRED, PROPAGATION_SUPPORTS,
155 * PROPAGATION_MANDATORY, PROPAGATION_REQUIRES_NEW,
156 * PROPAGATION_NOT_SUPPORTED, PROPAGATION_NEVER,
157 * PROPAGATION_NESTED七种
158 */
159 var transaction = "PROPAGATION_REQUIRES_NEW";
160 var timeout = -1;
161 var readonly = false;
162 db.doInTransaction(TransactionDefinition,timeout,readonly,
163 function(){
164 var insertSql="";
165 insertSql += " begin\n ";
166 for(var index=0;index<zfpzid_jjfl.length;index++){
167 insertSql +=" insert into GK_ZWVCH_IDS (pk_id, GK_ZFPZ_ID) values ('"+pk_id+"', '"+zfpzid_jjfl+"');";
168 }
169 for(var index=0;index<zfpzid.length;index++){
170 insertSql +=" insert into GK_ZWVCH_IDS (pk_id, GK_ZFPZ_ID) values ("+pk_id+", "+zfpzid+");";
171 }
172 insertSql+="end;";
173 var ret = db.update(insertSql,{});
174 });
175 var dataBaseName="";
176 if ( pzData.src_type=="0" ) {//预算内
177 //获取支付中心预算内对应数据库
178 var sqlZfzx = "select t.valueas databasename from pwp_sysparam t where t.name ='payZ'";
179 var dataBaseNameData=db.queryService.query(sqlZfzx,{});
180 for(var i=0;i<dataBaseNameData.data.length;i++){
181 // 获取id
182 dataBaseName=dataBaseNameData.data.get("databasename");
183 }
184 } else if (pzData.src_type=="1" ) {//预算外
185 //获取支付中心预算外对应数据库
186 var sqlZfzx = "select t.valueas databasenamefrom pwp_sysparam t where t.name ='payZout'";
187 var dataBaseNameData=db.queryService.query(sqlZfzx,{});
188 for(var i=0;i<dataBaseNameData.data.length;i++){
189 // 获取id
190 dataBaseName=dataBaseNameData.data.get("databasename");
191 }
192 }
193 /**
194 * 调用存储过程PRO_K3_ZFZXZ
195 *1 org_type 入参 varchar2, --预算内外
196 *2 opertype 入参 varchar2, --操作类型
197 *3 fdate 入参 varchar2, --日期
198 *4 i_pk_id 入参 varchar2, --主键id
199 *5 i_database 入参 varchar2, --数据库名
200 *6 i_fgroup 入参 varchar2, -- 字号,
201 *7 i_fpreparer 入参 number, -- 制单人,
202 *8 i_fattachment 入参 number, -- 附件数
203 *9 o_ret 出参 number
204 */
205
206 var results = db.callProcedure(function(conn){
207 var storedProc = "{call PRO_K3_ZFZXZ(?,?,?,?,?,?,?,?,?)}";// 调用的sql
208 var cs = conn.prepareCall(storedProc);
209 cs.setString(1, ""+pzData.src_type+"");
210 cs.setString(2, ""+pzData.org_type+"");
211 cs.setString(3, ""+pzData.fdate+"");
212 cs.setString(4, ""+pk_id+"");
213 cs.setString(5, ""+dataBaseName+"");
214 cs.setString(6, "记");
215 cs.setInt(7, parseInt(account_id));
216 cs.setInt(8, fattachment);
217 cs.registerOutParameter(9, 2);
218 return cs;
219 },function(cs){
220 cs.execute();
221 var rs = cs.getObject(9);
222 return 0 ;
223 });
224
225 if(results==0){
226 result.flag = true;
227 result.cont= "操作成功";
228 }
229 //用于正常回单生成会计凭证和退款以及调帐管理生成会计凭证 和汇总清算通知单
230 } else if (strOptionType =='5'
231 ||strOptionType =='6' ) {
232 //退款的直接支付凭证
233 //退款的授权支付凭证和代扣代缴
234
235 var orign_type = com.todaytech.yth.gdsd.gkzf.k3.ReceiveBillEnum.GK_ZWVCH_REFER_ORIGN_TYPE_BACKBILL;
236 zwvchvo.setOrign_type(orign_type);
237 facade.createRubricCZGKZwvch(zwvchvo, lstVo,lstVo_jjfl);
238
239
240 result.flag = true;
241 result.cont= "操作成功";
242 }
243
244 }catch (e) {
245 console.log(e);
246 result.flag = false;
247 result.cont ="操作失败"
248 }
249 return result;
250
251
252 1 CREATE OR REPLACE VIEW VIEW_K3_ZFZXZ_DIR AS
2 SELECT v.id GK_ZFPZ_ID ,v.id ,v.ORIGIN_ID,v.VOUCHER_NO,v.MAKE_DATE,v.FK_ACCOUNT, v.FK_BANK,v.SRC_ID,v.SK_NAME,v.SK_ACCOUNT, v.SK_BANK,
3 ------支付中心账务： 预算内 直接支付凭证含义政府经济分类辅助核算 部分 视图
4 v.unitno,v.UNIT_NAME,v.budget_type,v.acc_bdgid bdgt_accid,v.budgetno,v.budget_name, v.PRJNO,v.prj_name,v.amt as zfpz_amt,
5
6 v.amtas AMT,
7 (casewhen v.PURPOSE is null then '-' elsev.PURPOSEend) as PURPOSE,
8 v.REMARK, v.CHECKER, v.CHECKER_DATE,v.AFFIRMER,v.AFFIRM_DATE, v.VOUCHER_TYPE,v.CHECK_STATUS, v.WF_STATUS,v.GK_HZQSD_ID,
9 v.FUND,v.ORG_TYPE,v.BACK_OPER_ID,v.BACK_OPER_IDEA,v.BACK_OPER_DATE,v.BACK_CHECKER_ID,v.BACK_CHECKER_IDEA,v.BACK_CHECKER_DATE,
10 nvl(v.zf_item_amt, 0) as zf_item_amt ,
11 zf_itemno,
12 zf_itemname,
13 (case
14 when v.bm_itemnos is null then '-'
15 when v.bm_itemnos is not null and length(TRIM(v.bm_itemnos)) < 1 then '-' elsev.bm_itemnos
16 end) as bm_itemnos
17 FROM (selectgz_i.*
18 from (select gz.id,gz.origin_id,gz.zbz_bal_id,gz.quota_id,gz.voucher_no, gz.make_date,gz.voucher_type,
19 gz.funds_type,gz.balance_type,gz.src_id,gz.fk_name,gz.fk_account,gz.fk_bank,gz.fk_bank_code,gz.sk_name,
20 gz.sk_account,gz.sk_bank,gz.sk_bank_code,gz.unitno, gz.unit_name,gz.budget_type,gz.budgetno,
21 gz.budget_name,gz.prjno,gz.prj_name, gz.itemno,gz.item_name,gz.purpose,gz.remark,gz.wf_status,gz.print_status,
22 gz.send_status, gz.check_status, gz.affirm_date,gz.affirmer,gz.back_oper_date,gz.back_oper_id,
23 gz.back_oper_idea,gz.back_checker_id, gz.back_checker_idea,gz.back_checker_date,gz.source_voucher_id,
24 gz.gk_hzqsd_id, gz.cx_gk_hzqsd_id,gz.qs_date,gz.jy_no,gz.jy_date,gz.oper_account,
25 gz.oper_dt, gz.oper_org,gz.process_instance_id,gz.status,gz.zjzfsq_id,gz.is_paper_print,
26 gz.pay_type,gz.cheque_no,gz.checker,gz.checker_date,gz.ryjf_type, bg.acc_bdgid,
27 zqdi.amt as amt,
28 zqdi.amt as zf_item_amt,
29 zqdi.itemno as bm_itemnos,
30 bz.org_type,bz.fund, bg.func_bdgname, bu.unit_name as unit_names,
31
32 (case
33 when zqdi.itemno is not null and zqdi.itemno = '-' then zqdi.itemno
34 when zqdi.itemno is not null and zqdi.itemno != '-' and zqdi.itemno = '301' ANDsubstr( gz.unitno, 0, 2) IN ('00', '20') THEN'50101'
35 when zqdi.itemno is not null and zqdi.itemno != '-' and zqdi.itemno = '301' AND substr( gz.unitno, 0, 2) NOT IN ('00', '20') THEN'50501'
36 when zqdi.itemno is not null and zqdi.itemno != '-' and zqdi.itemno = '302' AND substr( gz.unitno, 0, 2) IN ('00', '20') THEN'50201'
37 when zqdi.itemno is not null and zqdi.itemno != '-' and zqdi.itemno = '302' AND substr( gz.unitno, 0, 2) NOT IN ('00', '20') THEN '50502'
38 when zqdi.itemno is not null and zqdi.itemno != '-' and zqdi.itemno = '30107' AND substr( gz.unitno, 0, 2) IN ('00', '20') THEN'50101'
39 when zqdi.itemno is not null and zqdi.itemno != '-' and zqdi.itemno = '30107' AND substr( gz.unitno, 0, 2) NOT IN ('00', '20') THEN'50501'
40 when zqdi.itemno is not null and zqdi.itemno != '-' and zqdi.itemno = '30311' ANDgz.unitno <> '0017' THEN '50501'
41 when zqdi.itemno is not null and zqdi.itemno != '-' and zqdi.itemno = '31199' THEN'50799'
42 when zqdi.itemno is not null and zqdi.itemno != '-' and zqdi.itemno = '30403' THEN'50299'
43 when zqdi.itemno is not null and zqdi.itemno != '-' and zqdi.itemno = '399' THEN'59999'
44 when zqdi.itemno is not null and zqdi.itemno != '-' and zqdi.itemno = '312' THEN '50799'
45 when zqdi.itemno is not null and zqdi.itemno != '-' and zqdi.itemno ='30499' then'50799'
46 when zqdi.itemno is not null and zqdi.itemno != '-' and zqdi.itemno = '30401' then'50799'
47
48 else
49 ( selectzfi.zf_itemnofrom view_get_zf_itemname zfiwherezqdi.itemno = zfi.bm_itemno(+)and zfi.year(+) = to_char(gz.make_date, 'yyyy'))
50 end) as zf_itemno,
51 (case
52 when zqdi.itemno is not null and zqdi.itemno != '-' andzqdi.itemno = '301' ANDsubstr( gz.unitno, 0, 2) IN ('00', '20') THEN (select bzj.econ_bdg from bs_zf_jjflyskm bzjwhere bzj.econ_bdgid = '501' and bzj.bdgyear = to_char(gz.make_date, 'yyyy')) || '--' ||(select bzj.econ_bdg from bs_zf_jjflyskm bzjwhere bzj.econ_bdgid = '50101'and bzj.bdgyear = to_char(gz.make_date, 'yyyy'))
53 when zqdi.itemno is not null and zqdi.itemno != '-' and zqdi.itemno = '301' ANDsubstr( gz.unitno, 0, 2) NOT IN ('00', '20') THEN (select bzj.econ_bdgfrom bs_zf_jjflyskm bzj where bzj.econ_bdgid = '505'and bzj.bdgyear = to_char(gz.make_date, 'yyyy')) || '--' ||(select bzj.econ_bdg from bs_zf_jjflyskm bzj where bzj.econ_bdgid = '50501' and bzj.bdgyear = to_char(gz.make_date, 'yyyy'))
54 when zqdi.itemno is not null and zqdi.itemno != '-' and zqdi.itemno = '302' ANDsubstr( gz.unitno, 0, 2) IN ('00', '20') THEN(select bzj.econ_bdg from bs_zf_jjflyskm bzj where bzj.econ_bdgid = '502' and bzj.bdgyear = to_char(gz.make_date, 'yyyy')) || '--' || (select bzj.econ_bdg from bs_zf_jjflyskm bzjwhere bzj.econ_bdgid = '50201' and bzj.bdgyear = to_char(gz.make_date, 'yyyy'))
55 when zqdi.itemno is not null and zqdi.itemno != '-' and zqdi.itemno = '302' AND substr( gz.unitno, 0, 2) NOT IN ('00', '20') THEN (select bzj.econ_bdg from bs_zf_jjflyskm bzj where bzj.econ_bdgid = '505'and bzj.bdgyear = to_char(gz.make_date, 'yyyy')) || '--' ||(select bzj.econ_bdg from bs_zf_jjflyskm bzj where bzj.econ_bdgid = '50502'and bzj.bdgyear = to_char(gz.make_date, 'yyyy'))
56 when zqdi.itemno is not null and zqdi.itemno != '-' and zqdi.itemno = '30107' AND substr( gz.unitno, 0, 2) IN ('00', '20') THEN(select bzj.econ_bdg from bs_zf_jjflyskm bzj where bzj.econ_bdgid = '501' and bzj.bdgyear = to_char(gz.make_date, 'yyyy')) || '--' ||(select bzj.econ_bdg from bs_zf_jjflyskm bzj where bzj.econ_bdgid = '50101'and bzj.bdgyear = to_char(gz.make_date, 'yyyy'))
57 when zqdi.itemno is not null and zqdi.itemno != '-' and zqdi.itemno = '30107' ANDsubstr( gz.unitno, 0, 2) NOT IN ('00', '20') THEN(select bzj.econ_bdg from bs_zf_jjflyskm bzjwhere bzj.econ_bdgid = '50501'and bzj.bdgyear = to_char(gz.make_date, 'yyyy')) || '--' || (select bzj.econ_bdgfrom bs_zf_jjflyskm bzjwhere bzj.econ_bdgid = '50501' and bzj.bdgyear = to_char(gz.make_date, 'yyyy'))
58 when zqdi.itemno is not null and zqdi.itemno != '-' and zqdi.itemno = '30311' ANDgz.unitno <> '0017' THEN (select bzj.econ_bdgfrom bs_zf_jjflyskm bzj where bzj.econ_bdgid = '505'and bzj.bdgyear = to_char(gz.make_date, 'yyyy')) || '--' ||(select bzj.econ_bdg from bs_zf_jjflyskm bzjwhere bzj.econ_bdgid = '50501' and bzj.bdgyear = to_char(gz.make_date, 'yyyy'))
59 when zqdi.itemno is not null and zqdi.itemno != '-' andzqdi.itemno = '31199' THEN (select bzj.econ_bdg from bs_zf_jjflyskm bzj where bzj.econ_bdgid = '507'and bzj.bdgyear = to_char(gz.make_date, 'yyyy')) || '--' ||(select bzj.econ_bdg from bs_zf_jjflyskm bzjwhere bzj.econ_bdgid = '50799'and bzj.bdgyear = to_char(gz.make_date, 'yyyy'))
60 when zqdi.itemno is not null and zqdi.itemno != '-' andzqdi.itemno = '30403' THEN (select bzj.econ_bdgfrom bs_zf_jjflyskm bzj where bzj.econ_bdgid = '502' and bzj.bdgyear = to_char(gz.make_date, 'yyyy')) || '--' ||(select bzj.econ_bdgfrom bs_zf_jjflyskm bzj where bzj.econ_bdgid = '50299'and bzj.bdgyear = to_char(gz.make_date, 'yyyy'))
61 when zqdi.itemno is not null and zqdi.itemno != '-' andzqdi.itemno = '399' THEN (select bzj.econ_bdgfrom bs_zf_jjflyskm bzj where bzj.econ_bdgid = '599'and bzj.bdgyear = to_char(gz.make_date, 'yyyy')) || '--' ||(select bzj.econ_bdg from bs_zf_jjflyskm bzj where bzj.econ_bdgid = '59999'and bzj.bdgyear = to_char(gz.make_date, 'yyyy'))
62 when zqdi.itemno is not null and zqdi.itemno != '-' andzqdi.itemno = '312' THEN(select bzj.econ_bdgfrom bs_zf_jjflyskm bzjwhere bzj.econ_bdgid = '507'and bzj.bdgyear = to_char(gz.make_date, 'yyyy')) || '--' || (select bzj.econ_bdgfrom bs_zf_jjflyskm bzjwhere bzj.econ_bdgid = '50799' and bzj.bdgyear = to_char(gz.make_date, 'yyyy'))
63 when zqdi.itemno is not null and zqdi.itemno != '-' andzqdi.itemno = '30499' THEN (select bzj.econ_bdg from bs_zf_jjflyskm bzj where bzj.econ_bdgid = '507'and bzj.bdgyear = to_char(gz.make_date, 'yyyy')) || '--' ||(select bzj.econ_bdg from bs_zf_jjflyskm bzjwhere bzj.econ_bdgid = '50799'and bzj.bdgyear = to_char(gz.make_date, 'yyyy'))
64 when zqdi.itemno is not null and zqdi.itemno != '-' andzqdi.itemno = '30401' THEN (select bzj.econ_bdg from bs_zf_jjflyskm bzj where bzj.econ_bdgid = '507'and bzj.bdgyear = to_char(gz.make_date, 'yyyy')) || '--' ||(select bzj.econ_bdg from bs_zf_jjflyskm bzjwhere bzj.econ_bdgid = '50799'and bzj.bdgyear = to_char(gz.make_date, 'yyyy'))
65
66 else ( selectzfi.zf_itemnamefrom view_get_zf_itemname zfiwherezqdi.itemno = zfi.bm_itemno(+)and zfi.year(+) = to_char(gz.make_date, 'yyyy')) end) as zf_itemname
67
68 from gk_zfpz gz,
69 bs_bdg_unit bu,
70 bs_gnflyskm bg,
71 bs_zjly bz,
72 (select * from gk_zjzfsq_detail_item gzdi where gzdi.amt is not nulland gzdi.amt != 0) zqdi
73 where gz.budgetno = bg.func_bdgid(+)
74 and gz.unitno = bu.unitno(+)
75 and gz.src_id = bz.fundno(+)
76 and gz.budget_type = bg.func_bdg_type(+)
77 and to_char(gz.make_date, 'yyyy') = bg.bdgyear
78 and gz.voucher_type = '0'
79 and gz.origin_id = zqdi.gk_zjzfsq_detail_id(+)) gz_i
80 /*union all
81 select gz_i.*
82 from (
83
84 select gz.id,gz.origin_id,gz.zbz_bal_id,gz.quota_id,gz.voucher_no, gz.make_date, gz.voucher_type,gz.funds_type, gz.balance_type,gz.src_id,
85 gz.fk_name, gz.fk_account,gz.fk_bank,gz.fk_bank_code, gz.sk_name,gz.sk_account,
86 gz.sk_bank,gz.sk_bank_code,gz.unitno,gz.unit_name,gz.budget_type,gz.budgetno,gz.budget_name,gz.prjno,gz.prj_name,
87 gz.itemno,gz.item_name, gz.purpose,gz.remark,gz.wf_status,gz.print_status,gz.send_status,gz.check_status,gz.affirm_date,gz.affirmer,
88 gz.back_oper_date,gz.back_oper_id, gz.back_oper_idea,gz.back_checker_id,gz.back_checker_idea, gz.back_checker_date,gz.source_voucher_id,
89 gz.gk_hzqsd_id,gz.cx_gk_hzqsd_id,gz.qs_date,gz.jy_no,gz.jy_date,gz.oper_account,gz.oper_dt,gz.oper_org, gz.process_instance_id,
90 gz.status,gz.zjzfsq_id,gz.is_paper_print,gz.pay_type,gz.cheque_no,gz.checker,gz.checker_date,gz.ryjf_type, bg.acc_bdgid,
91 gzi.amt as amt,
92 gzi.amt as zf_item_amt,
93 gzi.itemnoas bm_itemnos,
94 bz.org_type,bz.fund,bg.func_bdgname, bu.unit_name as unit_names,
95
96 (case
97 when gzi.itemno is not null and gzi.itemno = '-' then gzi.itemno
98 when gzi.itemno is not null and gzi.itemno != '-' and gzi.itemno = '301' ANDsubstr( gz.unitno, 0, 2) IN ('00', '20') THEN'50101'
99 when gzi.itemno is not null and gzi.itemno != '-' and gzi.itemno = '301' AND substr( gz.unitno, 0, 2) NOT IN ('00', '20') THEN'50501'
100 when gzi.itemno is not null and gzi.itemno != '-' and gzi.itemno = '302' AND substr( gz.unitno, 0, 2) IN ('00', '20') THEN'50201'
101 when gzi.itemno is not null and gzi.itemno != '-' and gzi.itemno = '302' AND substr( gz.unitno, 0, 2) NOT IN ('00', '20') THEN '50502'
102 when gzi.itemno is not null and gzi.itemno != '-' and gzi.itemno = '30107' AND substr( gz.unitno, 0, 2) IN ('00', '20') THEN'50101'
103 when gzi.itemno is not null and gzi.itemno != '-' and gzi.itemno = '30107' AND substr( gz.unitno, 0, 2) NOT IN ('00', '20') THEN'50501'
104 when gzi.itemno is not null and gzi.itemno != '-' and gzi.itemno = '30311' ANDgz.unitno <> '0017' THEN '50501'
105 when gzi.itemno is not null and gzi.itemno != '-' and gzi.itemno = '31199' THEN'50799'
106 when gzi.itemno is not null and gzi.itemno != '-' and gzi.itemno = '30403' THEN'50299'
107 when gzi.itemno is not null and gzi.itemno != '-' and gzi.itemno = '399' THEN'59999'
108 when gzi.itemno is not null and gzi.itemno != '-' and gzi.itemno = '312' THEN '50799'
109 when gzi.itemno is not null and gzi.itemno != '-' and gzi.itemno ='30499' then'50799'
110 when gzi.itemno is not null and gzi.itemno != '-' and gzi.itemno = '30401' then'50799'
111
112 else
113 ( selectzfi.zf_itemnofrom view_get_zf_itemname zfiwheregzi.itemno = zfi.bm_itemno(+)and zfi.year(+) = to_char(gz.make_date, 'yyyy'))
114 end) as zf_itemno,
115 (case
116 when gzi.itemno is not null and gzi.itemno != '-' andgzi.itemno = '301' ANDsubstr( gz.unitno, 0, 2) IN ('00', '20') THEN (select bzj.econ_bdg from bs_zf_jjflyskm bzjwhere bzj.econ_bdgid = '501' and bzj.bdgyear = to_char(gz.make_date, 'yyyy')) || '--' ||(select bzj.econ_bdg from bs_zf_jjflyskm bzjwhere bzj.econ_bdgid = '50101'and bzj.bdgyear = to_char(gz.make_date, 'yyyy'))
117 when gzi.itemno is not null and gzi.itemno != '-' and gzi.itemno = '301' ANDsubstr( gz.unitno, 0, 2) NOT IN ('00', '20') THEN (select bzj.econ_bdgfrom bs_zf_jjflyskm bzj where bzj.econ_bdgid = '505'and bzj.bdgyear = to_char(gz.make_date, 'yyyy')) || '--' ||(select bzj.econ_bdg from bs_zf_jjflyskm bzj where bzj.econ_bdgid = '50501' and bzj.bdgyear = to_char(gz.make_date, 'yyyy'))
118 when gzi.itemno is not null and gzi.itemno != '-' and gzi.itemno = '302' ANDsubstr( gz.unitno, 0, 2) IN ('00', '20') THEN(select bzj.econ_bdg from bs_zf_jjflyskm bzj where bzj.econ_bdgid = '502' and bzj.bdgyear = to_char(gz.make_date, 'yyyy')) || '--' || (select bzj.econ_bdg from bs_zf_jjflyskm bzjwhere bzj.econ_bdgid = '50201' and bzj.bdgyear = to_char(gz.make_date, 'yyyy'))
119 when gzi.itemno is not null and gzi.itemno != '-' and gzi.itemno = '302' AND substr( gz.unitno, 0, 2) NOT IN ('00', '20') THEN (select bzj.econ_bdg from bs_zf_jjflyskm bzj where bzj.econ_bdgid = '505'and bzj.bdgyear = to_char(gz.make_date, 'yyyy')) || '--' ||(select bzj.econ_bdg from bs_zf_jjflyskm bzj where bzj.econ_bdgid = '50502'and bzj.bdgyear = to_char(gz.make_date, 'yyyy'))
120 when gzi.itemno is not null and gzi.itemno != '-' and gzi.itemno = '30107' AND substr( gz.unitno, 0, 2) IN ('00', '20') THEN(select bzj.econ_bdg from bs_zf_jjflyskm bzj where bzj.econ_bdgid = '501' and bzj.bdgyear = to_char(gz.make_date, 'yyyy')) || '--' ||(select bzj.econ_bdg from bs_zf_jjflyskm bzj where bzj.econ_bdgid = '50101'and bzj.bdgyear = to_char(gz.make_date, 'yyyy'))
121 when gzi.itemno is not null and gzi.itemno != '-' and gzi.itemno = '30107' ANDsubstr( gz.unitno, 0, 2) NOT IN ('00', '20') THEN(select bzj.econ_bdg from bs_zf_jjflyskm bzjwhere bzj.econ_bdgid = '50501'and bzj.bdgyear = to_char(gz.make_date, 'yyyy')) || '--' || (select bzj.econ_bdgfrom bs_zf_jjflyskm bzjwhere bzj.econ_bdgid = '50501' and bzj.bdgyear = to_char(gz.make_date, 'yyyy'))
122 when gzi.itemno is not null and gzi.itemno != '-' and gzi.itemno = '30311' ANDgz.unitno <> '0017' THEN (select bzj.econ_bdgfrom bs_zf_jjflyskm bzj where bzj.econ_bdgid = '505'and bzj.bdgyear = to_char(gz.make_date, 'yyyy')) || '--' ||(select bzj.econ_bdg from bs_zf_jjflyskm bzjwhere bzj.econ_bdgid = '50501' and bzj.bdgyear = to_char(gz.make_date, 'yyyy'))
123 when gzi.itemno is not null and gzi.itemno != '-' andgzi.itemno = '31199' THEN (select bzj.econ_bdg from bs_zf_jjflyskm bzj where bzj.econ_bdgid = '507'and bzj.bdgyear = to_char(gz.make_date, 'yyyy')) || '--' ||(select bzj.econ_bdg from bs_zf_jjflyskm bzjwhere bzj.econ_bdgid = '50799'and bzj.bdgyear = to_char(gz.make_date, 'yyyy'))
124 when gzi.itemno is not null and gzi.itemno != '-' andgzi.itemno = '30403' THEN (select bzj.econ_bdgfrom bs_zf_jjflyskm bzj where bzj.econ_bdgid = '502' and bzj.bdgyear = to_char(gz.make_date, 'yyyy')) || '--' ||(select bzj.econ_bdgfrom bs_zf_jjflyskm bzj where bzj.econ_bdgid = '50299'and bzj.bdgyear = to_char(gz.make_date, 'yyyy'))
125 when gzi.itemno is not null and gzi.itemno != '-' andgzi.itemno = '399' THEN (select bzj.econ_bdgfrom bs_zf_jjflyskm bzj where bzj.econ_bdgid = '599'and bzj.bdgyear = to_char(gz.make_date, 'yyyy')) || '--' ||(select bzj.econ_bdg from bs_zf_jjflyskm bzj where bzj.econ_bdgid = '59999'and bzj.bdgyear = to_char(gz.make_date, 'yyyy'))
126 when gzi.itemno is not null and gzi.itemno != '-' andgzi.itemno = '312' THEN(select bzj.econ_bdgfrom bs_zf_jjflyskm bzjwhere bzj.econ_bdgid = '507'and bzj.bdgyear = to_char(gz.make_date, 'yyyy')) || '--' || (select bzj.econ_bdgfrom bs_zf_jjflyskm bzjwhere bzj.econ_bdgid = '50799' and bzj.bdgyear = to_char(gz.make_date, 'yyyy'))
127 when gzi.itemno is not null and gzi.itemno != '-' andgzi.itemno = '30499' THEN (select bzj.econ_bdg from bs_zf_jjflyskm bzj where bzj.econ_bdgid = '507'and bzj.bdgyear = to_char(gz.make_date, 'yyyy')) || '--' ||(select bzj.econ_bdg from bs_zf_jjflyskm bzjwhere bzj.econ_bdgid = '50799'and bzj.bdgyear = to_char(gz.make_date, 'yyyy'))
128 when gzi.itemno is not null and gzi.itemno != '-' andgzi.itemno = '30401' THEN (select bzj.econ_bdg from bs_zf_jjflyskm bzj where bzj.econ_bdgid = '507'and bzj.bdgyear = to_char(gz.make_date, 'yyyy')) || '--' ||(select bzj.econ_bdg from bs_zf_jjflyskm bzjwhere bzj.econ_bdgid = '50799'and bzj.bdgyear = to_char(gz.make_date, 'yyyy'))
129
130 else ( selectzfi.zf_itemnamefrom view_get_zf_itemname zfiwheregzi.itemno = zfi.bm_itemno(+)and zfi.year(+) = to_char(gz.make_date, 'yyyy')) end) as zf_itemname
131
132 from gk_zfpz gz,bs_bdg_unitbu,bs_zjlybz, bs_gnflyskmbg, gk_zfpz_item gzi
133 where gz.budgetno = bg.func_bdgid(+)
134 and gz.unitno = bu.unitno(+)
135 and gz.src_id = bz.fundno(+)
136 and gz.budget_type = bg.func_bdg_type(+)
137 and to_char(gz.make_date, 'yyyy') = bg.bdgyear
138 and gz.voucher_type = '1'
139 and gz.id = gzi.gk_zfpz_id(+)
140
141 ) gz_i*/
142 ) v;
143 1 CREATE OR REPLACE VIEW VIEW_K3_ZFZXZ_ACC AS
2 SELECT v.id GK_ZFPZ_ID ,v.id ,v.ORIGIN_ID,v.VOUCHER_NO,v.MAKE_DATE,v.FK_ACCOUNT, v.FK_BANK,v.SRC_ID,v.SK_NAME,v.SK_ACCOUNT, v.SK_BANK,
3 ------支付中心账务： 预算内 直接支付凭证含义政府经济分类辅助核算 部分 视图
4 v.unitno,v.UNIT_NAME,v.budget_type,v.acc_bdgid bdgt_accid,v.budgetno,v.budget_name, v.PRJNO,v.prj_name,v.amt as zfpz_amt,
5
6 v.amtas AMT,
7 (casewhen v.PURPOSE is null then '-' elsev.PURPOSEend) as PURPOSE,
8 v.REMARK, v.CHECKER, v.CHECKER_DATE,v.AFFIRMER,v.AFFIRM_DATE, v.VOUCHER_TYPE,v.CHECK_STATUS, v.WF_STATUS,v.GK_HZQSD_ID,
9 v.FUND,v.ORG_TYPE,v.BACK_OPER_ID,v.BACK_OPER_IDEA,v.BACK_OPER_DATE,v.BACK_CHECKER_ID,v.BACK_CHECKER_IDEA,v.BACK_CHECKER_DATE,
10 nvl(v.zf_item_amt, 0) as zf_item_amt ,
11 zf_itemno,
12 zf_itemname,
13 (case
14 when v.bm_itemnos is null then '-'
15 when v.bm_itemnos is not null and length(TRIM(v.bm_itemnos)) < 1 then '-' elsev.bm_itemnos
16 end) as bm_itemnos
17 FROM (/*selectgz_i.*
18 from (select gz.id,gz.origin_id,gz.zbz_bal_id,gz.quota_id,gz.voucher_no, gz.make_date,gz.voucher_type,
19 gz.funds_type,gz.balance_type,gz.src_id,gz.fk_name,gz.fk_account,gz.fk_bank,gz.fk_bank_code,gz.sk_name,
20 gz.sk_account,gz.sk_bank,gz.sk_bank_code,gz.unitno, gz.unit_name,gz.budget_type,gz.budgetno,
21 gz.budget_name,gz.prjno,gz.prj_name, gz.itemno,gz.item_name,gz.purpose,gz.remark,gz.wf_status,gz.print_status,
22 gz.send_status, gz.check_status, gz.affirm_date,gz.affirmer,gz.back_oper_date,gz.back_oper_id,
23 gz.back_oper_idea,gz.back_checker_id, gz.back_checker_idea,gz.back_checker_date,gz.source_voucher_id,
24 gz.gk_hzqsd_id, gz.cx_gk_hzqsd_id,gz.qs_date,gz.jy_no,gz.jy_date,gz.oper_account,
25 gz.oper_dt, gz.oper_org,gz.process_instance_id,gz.status,gz.zjzfsq_id,gz.is_paper_print,
26 gz.pay_type,gz.cheque_no,gz.checker,gz.checker_date,gz.ryjf_type, bg.acc_bdgid,
27 zqdi.amt as amt,
28 zqdi.amt as zf_item_amt,
29 zqdi.itemno as bm_itemnos,
30 bz.org_type,bz.fund, bg.func_bdgname, bu.unit_name as unit_names,
31
32 (case
33 when zqdi.itemno is not null and zqdi.itemno = '-' then zqdi.itemno
34 when zqdi.itemno is not null and zqdi.itemno != '-' and zqdi.itemno = '301' ANDsubstr( gz.unitno, 0, 2) IN ('00', '20') THEN'50101'
35 when zqdi.itemno is not null and zqdi.itemno != '-' and zqdi.itemno = '301' AND substr( gz.unitno, 0, 2) NOT IN ('00', '20') THEN'50501'
36 when zqdi.itemno is not null and zqdi.itemno != '-' and zqdi.itemno = '302' AND substr( gz.unitno, 0, 2) IN ('00', '20') THEN'50201'
37 when zqdi.itemno is not null and zqdi.itemno != '-' and zqdi.itemno = '302' AND substr( gz.unitno, 0, 2) NOT IN ('00', '20') THEN '50502'
38 when zqdi.itemno is not null and zqdi.itemno != '-' and zqdi.itemno = '30107' AND substr( gz.unitno, 0, 2) IN ('00', '20') THEN'50101'
39 when zqdi.itemno is not null and zqdi.itemno != '-' and zqdi.itemno = '30107' AND substr( gz.unitno, 0, 2) NOT IN ('00', '20') THEN'50501'
40 when zqdi.itemno is not null and zqdi.itemno != '-' and zqdi.itemno = '30311' ANDgz.unitno <> '0017' THEN '50501'
41 when zqdi.itemno is not null and zqdi.itemno != '-' and zqdi.itemno = '31199' THEN'50799'
42 when zqdi.itemno is not null and zqdi.itemno != '-' and zqdi.itemno = '30403' THEN'50299'
43 when zqdi.itemno is not null and zqdi.itemno != '-' and zqdi.itemno = '399' THEN'59999'
44 when zqdi.itemno is not null and zqdi.itemno != '-' and zqdi.itemno = '312' THEN '50799'
45 when zqdi.itemno is not null and zqdi.itemno != '-' and zqdi.itemno ='30499' then'50799'
46 when zqdi.itemno is not null and zqdi.itemno != '-' and zqdi.itemno = '30401' then'50799'
47
48 else
49 ( selectzfi.zf_itemnofrom view_get_zf_itemname zfiwherezqdi.itemno = zfi.bm_itemno(+)and zfi.year(+) = to_char(gz.make_date, 'yyyy'))
50 end) as zf_itemno,
51 (case
52 when zqdi.itemno is not null and zqdi.itemno != '-' andzqdi.itemno = '301' ANDsubstr( gz.unitno, 0, 2) IN ('00', '20') THEN (select bzj.econ_bdg from bs_zf_jjflyskm bzjwhere bzj.econ_bdgid = '501' and bzj.bdgyear = to_char(gz.make_date, 'yyyy')) || '--' ||(select bzj.econ_bdg from bs_zf_jjflyskm bzjwhere bzj.econ_bdgid = '50101'and bzj.bdgyear = to_char(gz.make_date, 'yyyy'))
53 when zqdi.itemno is not null and zqdi.itemno != '-' and zqdi.itemno = '301' ANDsubstr( gz.unitno, 0, 2) NOT IN ('00', '20') THEN (select bzj.econ_bdgfrom bs_zf_jjflyskm bzj where bzj.econ_bdgid = '505'and bzj.bdgyear = to_char(gz.make_date, 'yyyy')) || '--' ||(select bzj.econ_bdg from bs_zf_jjflyskm bzj where bzj.econ_bdgid = '50501' and bzj.bdgyear = to_char(gz.make_date, 'yyyy'))
54 when zqdi.itemno is not null and zqdi.itemno != '-' and zqdi.itemno = '302' ANDsubstr( gz.unitno, 0, 2) IN ('00', '20') THEN(select bzj.econ_bdg from bs_zf_jjflyskm bzj where bzj.econ_bdgid = '502' and bzj.bdgyear = to_char(gz.make_date, 'yyyy')) || '--' || (select bzj.econ_bdg from bs_zf_jjflyskm bzjwhere bzj.econ_bdgid = '50201' and bzj.bdgyear = to_char(gz.make_date, 'yyyy'))
55 when zqdi.itemno is not null and zqdi.itemno != '-' and zqdi.itemno = '302' AND substr( gz.unitno, 0, 2) NOT IN ('00', '20') THEN (select bzj.econ_bdg from bs_zf_jjflyskm bzj where bzj.econ_bdgid = '505'and bzj.bdgyear = to_char(gz.make_date, 'yyyy')) || '--' ||(select bzj.econ_bdg from bs_zf_jjflyskm bzj where bzj.econ_bdgid = '50502'and bzj.bdgyear = to_char(gz.make_date, 'yyyy'))
56 when zqdi.itemno is not null and zqdi.itemno != '-' and zqdi.itemno = '30107' AND substr( gz.unitno, 0, 2) IN ('00', '20') THEN(select bzj.econ_bdg from bs_zf_jjflyskm bzj where bzj.econ_bdgid = '501' and bzj.bdgyear = to_char(gz.make_date, 'yyyy')) || '--' ||(select bzj.econ_bdg from bs_zf_jjflyskm bzj where bzj.econ_bdgid = '50101'and bzj.bdgyear = to_char(gz.make_date, 'yyyy'))
57 when zqdi.itemno is not null and zqdi.itemno != '-' and zqdi.itemno = '30107' ANDsubstr( gz.unitno, 0, 2) NOT IN ('00', '20') THEN(select bzj.econ_bdg from bs_zf_jjflyskm bzjwhere bzj.econ_bdgid = '50501'and bzj.bdgyear = to_char(gz.make_date, 'yyyy')) || '--' || (select bzj.econ_bdgfrom bs_zf_jjflyskm bzjwhere bzj.econ_bdgid = '50501' and bzj.bdgyear = to_char(gz.make_date, 'yyyy'))
58 when zqdi.itemno is not null and zqdi.itemno != '-' and zqdi.itemno = '30311' ANDgz.unitno <> '0017' THEN (select bzj.econ_bdgfrom bs_zf_jjflyskm bzj where bzj.econ_bdgid = '505'and bzj.bdgyear = to_char(gz.make_date, 'yyyy')) || '--' ||(select bzj.econ_bdg from bs_zf_jjflyskm bzjwhere bzj.econ_bdgid = '50501' and bzj.bdgyear = to_char(gz.make_date, 'yyyy'))
59 when zqdi.itemno is not null and zqdi.itemno != '-' andzqdi.itemno = '31199' THEN (select bzj.econ_bdg from bs_zf_jjflyskm bzj where bzj.econ_bdgid = '507'and bzj.bdgyear = to_char(gz.make_date, 'yyyy')) || '--' ||(select bzj.econ_bdg from bs_zf_jjflyskm bzjwhere bzj.econ_bdgid = '50799'and bzj.bdgyear = to_char(gz.make_date, 'yyyy'))
60 when zqdi.itemno is not null and zqdi.itemno != '-' andzqdi.itemno = '30403' THEN (select bzj.econ_bdgfrom bs_zf_jjflyskm bzj where bzj.econ_bdgid = '502' and bzj.bdgyear = to_char(gz.make_date, 'yyyy')) || '--' ||(select bzj.econ_bdgfrom bs_zf_jjflyskm bzj where bzj.econ_bdgid = '50299'and bzj.bdgyear = to_char(gz.make_date, 'yyyy'))
61 when zqdi.itemno is not null and zqdi.itemno != '-' andzqdi.itemno = '399' THEN (select bzj.econ_bdgfrom bs_zf_jjflyskm bzj where bzj.econ_bdgid = '599'and bzj.bdgyear = to_char(gz.make_date, 'yyyy')) || '--' ||(select bzj.econ_bdg from bs_zf_jjflyskm bzj where bzj.econ_bdgid = '59999'and bzj.bdgyear = to_char(gz.make_date, 'yyyy'))
62 when zqdi.itemno is not null and zqdi.itemno != '-' andzqdi.itemno = '312' THEN(select bzj.econ_bdgfrom bs_zf_jjflyskm bzjwhere bzj.econ_bdgid = '507'and bzj.bdgyear = to_char(gz.make_date, 'yyyy')) || '--' || (select bzj.econ_bdgfrom bs_zf_jjflyskm bzjwhere bzj.econ_bdgid = '50799' and bzj.bdgyear = to_char(gz.make_date, 'yyyy'))
63 when zqdi.itemno is not null and zqdi.itemno != '-' andzqdi.itemno = '30499' THEN (select bzj.econ_bdg from bs_zf_jjflyskm bzj where bzj.econ_bdgid = '507'and bzj.bdgyear = to_char(gz.make_date, 'yyyy')) || '--' ||(select bzj.econ_bdg from bs_zf_jjflyskm bzjwhere bzj.econ_bdgid = '50799'and bzj.bdgyear = to_char(gz.make_date, 'yyyy'))
64 when zqdi.itemno is not null and zqdi.itemno != '-' andzqdi.itemno = '30401' THEN (select bzj.econ_bdg from bs_zf_jjflyskm bzj where bzj.econ_bdgid = '507'and bzj.bdgyear = to_char(gz.make_date, 'yyyy')) || '--' ||(select bzj.econ_bdg from bs_zf_jjflyskm bzjwhere bzj.econ_bdgid = '50799'and bzj.bdgyear = to_char(gz.make_date, 'yyyy'))
65
66 else ( selectzfi.zf_itemnamefrom view_get_zf_itemname zfiwherezqdi.itemno = zfi.bm_itemno(+)and zfi.year(+) = to_char(gz.make_date, 'yyyy')) end) as zf_itemname
67
68 from gk_zfpz gz,
69 bs_bdg_unit bu,
70 bs_gnflyskm bg,
71 bs_zjly bz,
72 (select * from gk_zjzfsq_detail_item gzdi where gzdi.amt is not nulland gzdi.amt != 0) zqdi
73 where gz.budgetno = bg.func_bdgid(+)
74 and gz.unitno = bu.unitno(+)
75 and gz.src_id = bz.fundno(+)
76 and gz.budget_type = bg.func_bdg_type(+)
77 and to_char(gz.make_date, 'yyyy') = bg.bdgyear
78 and gz.voucher_type = '0'
79 and gz.origin_id = zqdi.gk_zjzfsq_detail_id(+)) gz_i
80 union all*/
81 select gz_i.*
82 from (
83
84 select gz.id,gz.origin_id,gz.zbz_bal_id,gz.quota_id,gz.voucher_no, gz.make_date, gz.voucher_type,gz.funds_type, gz.balance_type,gz.src_id,
85 gz.fk_name, gz.fk_account,gz.fk_bank,gz.fk_bank_code, gz.sk_name,gz.sk_account,
86 gz.sk_bank,gz.sk_bank_code,gz.unitno,gz.unit_name,gz.budget_type,gz.budgetno,gz.budget_name,gz.prjno,gz.prj_name,
87 gz.itemno,gz.item_name, gz.purpose,gz.remark,gz.wf_status,gz.print_status,gz.send_status,gz.check_status,gz.affirm_date,gz.affirmer,
88 gz.back_oper_date,gz.back_oper_id, gz.back_oper_idea,gz.back_checker_id,gz.back_checker_idea, gz.back_checker_date,gz.source_voucher_id,
89 gz.gk_hzqsd_id,gz.cx_gk_hzqsd_id,gz.qs_date,gz.jy_no,gz.jy_date,gz.oper_account,gz.oper_dt,gz.oper_org, gz.process_instance_id,
90 gz.status,gz.zjzfsq_id,gz.is_paper_print,gz.pay_type,gz.cheque_no,gz.checker,gz.checker_date,gz.ryjf_type, bg.acc_bdgid,
91 gzi.amt as amt,
92 gzi.amt as zf_item_amt,
93 gzi.itemnoas bm_itemnos,
94 bz.org_type,bz.fund,bg.func_bdgname, bu.unit_name as unit_names,
95
96 (case
97 when gzi.itemno is not null and gzi.itemno = '-' then gzi.itemno
98 when gzi.itemno is not null and gzi.itemno != '-' and gzi.itemno = '301' ANDsubstr( gz.unitno, 0, 2) IN ('00', '20') THEN'50101'
99 when gzi.itemno is not null and gzi.itemno != '-' and gzi.itemno = '301' AND substr( gz.unitno, 0, 2) NOT IN ('00', '20') THEN'50501'
100 when gzi.itemno is not null and gzi.itemno != '-' and gzi.itemno = '302' AND substr( gz.unitno, 0, 2) IN ('00', '20') THEN'50201'
101 when gzi.itemno is not null and gzi.itemno != '-' and gzi.itemno = '302' AND substr( gz.unitno, 0, 2) NOT IN ('00', '20') THEN '50502'
102 when gzi.itemno is not null and gzi.itemno != '-' and gzi.itemno = '30107' AND substr( gz.unitno, 0, 2) IN ('00', '20') THEN'50101'
103 when gzi.itemno is not null and gzi.itemno != '-' and gzi.itemno = '30107' AND substr( gz.unitno, 0, 2) NOT IN ('00', '20') THEN'50501'
104 when gzi.itemno is not null and gzi.itemno != '-' and gzi.itemno = '30311' ANDgz.unitno <> '0017' THEN '50501'
105 when gzi.itemno is not null and gzi.itemno != '-' and gzi.itemno = '31199' THEN'50799'
106 when gzi.itemno is not null and gzi.itemno != '-' and gzi.itemno = '30403' THEN'50299'
107 when gzi.itemno is not null and gzi.itemno != '-' and gzi.itemno = '399' THEN'59999'
108 when gzi.itemno is not null and gzi.itemno != '-' and gzi.itemno = '312' THEN '50799'
109 when gzi.itemno is not null and gzi.itemno != '-' and gzi.itemno ='30499' then'50799'
110 when gzi.itemno is not null and gzi.itemno != '-' and gzi.itemno = '30401' then'50799'
111
112 else
113 ( selectzfi.zf_itemnofrom view_get_zf_itemname zfiwheregzi.itemno = zfi.bm_itemno(+)and zfi.year(+) = to_char(gz.make_date, 'yyyy'))
114 end) as zf_itemno,
115 (case
116 when gzi.itemno is not null and gzi.itemno != '-' andgzi.itemno = '301' ANDsubstr( gz.unitno, 0, 2) IN ('00', '20') THEN (select bzj.econ_bdg from bs_zf_jjflyskm bzjwhere bzj.econ_bdgid = '501' and bzj.bdgyear = to_char(gz.make_date, 'yyyy')) || '--' ||(select bzj.econ_bdg from bs_zf_jjflyskm bzjwhere bzj.econ_bdgid = '50101'and bzj.bdgyear = to_char(gz.make_date, 'yyyy'))
117 when gzi.itemno is not null and gzi.itemno != '-' and gzi.itemno = '301' ANDsubstr( gz.unitno, 0, 2) NOT IN ('00', '20') THEN (select bzj.econ_bdgfrom bs_zf_jjflyskm bzj where bzj.econ_bdgid = '505'and bzj.bdgyear = to_char(gz.make_date, 'yyyy')) || '--' ||(select bzj.econ_bdg from bs_zf_jjflyskm bzj where bzj.econ_bdgid = '50501' and bzj.bdgyear = to_char(gz.make_date, 'yyyy'))
118 when gzi.itemno is not null and gzi.itemno != '-' and gzi.itemno = '302' ANDsubstr( gz.unitno, 0, 2) IN ('00', '20') THEN(select bzj.econ_bdg from bs_zf_jjflyskm bzj where bzj.econ_bdgid = '502' and bzj.bdgyear = to_char(gz.make_date, 'yyyy')) || '--' || (select bzj.econ_bdg from bs_zf_jjflyskm bzjwhere bzj.econ_bdgid = '50201' and bzj.bdgyear = to_char(gz.make_date, 'yyyy'))
119 when gzi.itemno is not null and gzi.itemno != '-' and gzi.itemno = '302' AND substr( gz.unitno, 0, 2) NOT IN ('00', '20') THEN (select bzj.econ_bdg from bs_zf_jjflyskm bzj where bzj.econ_bdgid = '505'and bzj.bdgyear = to_char(gz.make_date, 'yyyy')) || '--' ||(select bzj.econ_bdg from bs_zf_jjflyskm bzj where bzj.econ_bdgid = '50502'and bzj.bdgyear = to_char(gz.make_date, 'yyyy'))
120 when gzi.itemno is not null and gzi.itemno != '-' and gzi.itemno = '30107' AND substr( gz.unitno, 0, 2) IN ('00', '20') THEN(select bzj.econ_bdg from bs_zf_jjflyskm bzj where bzj.econ_bdgid = '501' and bzj.bdgyear = to_char(gz.make_date, 'yyyy')) || '--' ||(select bzj.econ_bdg from bs_zf_jjflyskm bzj where bzj.econ_bdgid = '50101'and bzj.bdgyear = to_char(gz.make_date, 'yyyy'))
121 when gzi.itemno is not null and gzi.itemno != '-' and gzi.itemno = '30107' ANDsubstr( gz.unitno, 0, 2) NOT IN ('00', '20') THEN(select bzj.econ_bdg from bs_zf_jjflyskm bzjwhere bzj.econ_bdgid = '50501'and bzj.bdgyear = to_char(gz.make_date, 'yyyy')) || '--' || (select bzj.econ_bdgfrom bs_zf_jjflyskm bzjwhere bzj.econ_bdgid = '50501' and bzj.bdgyear = to_char(gz.make_date, 'yyyy'))
122 when gzi.itemno is not null and gzi.itemno != '-' and gzi.itemno = '30311' ANDgz.unitno <> '0017' THEN (select bzj.econ_bdgfrom bs_zf_jjflyskm bzj where bzj.econ_bdgid = '505'and bzj.bdgyear = to_char(gz.make_date, 'yyyy')) || '--' ||(select bzj.econ_bdg from bs_zf_jjflyskm bzjwhere bzj.econ_bdgid = '50501' and bzj.bdgyear = to_char(gz.make_date, 'yyyy'))
123 when gzi.itemno is not null and gzi.itemno != '-' andgzi.itemno = '31199' THEN (select bzj.econ_bdg from bs_zf_jjflyskm bzj where bzj.econ_bdgid = '507'and bzj.bdgyear = to_char(gz.make_date, 'yyyy')) || '--' ||(select bzj.econ_bdg from bs_zf_jjflyskm bzjwhere bzj.econ_bdgid = '50799'and bzj.bdgyear = to_char(gz.make_date, 'yyyy'))
124 when gzi.itemno is not null and gzi.itemno != '-' andgzi.itemno = '30403' THEN (select bzj.econ_bdgfrom bs_zf_jjflyskm bzj where bzj.econ_bdgid = '502' and bzj.bdgyear = to_char(gz.make_date, 'yyyy')) || '--' ||(select bzj.econ_bdgfrom bs_zf_jjflyskm bzj where bzj.econ_bdgid = '50299'and bzj.bdgyear = to_char(gz.make_date, 'yyyy'))
125 when gzi.itemno is not null and gzi.itemno != '-' andgzi.itemno = '399' THEN (select bzj.econ_bdgfrom bs_zf_jjflyskm bzj where bzj.econ_bdgid = '599'and bzj.bdgyear = to_char(gz.make_date, 'yyyy')) || '--' ||(select bzj.econ_bdg from bs_zf_jjflyskm bzj where bzj.econ_bdgid = '59999'and bzj.bdgyear = to_char(gz.make_date, 'yyyy'))
126 when gzi.itemno is not null and gzi.itemno != '-' andgzi.itemno = '312' THEN(select bzj.econ_bdgfrom bs_zf_jjflyskm bzjwhere bzj.econ_bdgid = '507'and bzj.bdgyear = to_char(gz.make_date, 'yyyy')) || '--' || (select bzj.econ_bdgfrom bs_zf_jjflyskm bzjwhere bzj.econ_bdgid = '50799' and bzj.bdgyear = to_char(gz.make_date, 'yyyy'))
127 when gzi.itemno is not null and gzi.itemno != '-' andgzi.itemno = '30499' THEN (select bzj.econ_bdg from bs_zf_jjflyskm bzj where bzj.econ_bdgid = '507'and bzj.bdgyear = to_char(gz.make_date, 'yyyy')) || '--' ||(select bzj.econ_bdg from bs_zf_jjflyskm bzjwhere bzj.econ_bdgid = '50799'and bzj.bdgyear = to_char(gz.make_date, 'yyyy'))
128 when gzi.itemno is not null and gzi.itemno != '-' andgzi.itemno = '30401' THEN (select bzj.econ_bdg from bs_zf_jjflyskm bzj where bzj.econ_bdgid = '507'and bzj.bdgyear = to_char(gz.make_date, 'yyyy')) || '--' ||(select bzj.econ_bdg from bs_zf_jjflyskm bzjwhere bzj.econ_bdgid = '50799'and bzj.bdgyear = to_char(gz.make_date, 'yyyy'))
129
130 else ( selectzfi.zf_itemnamefrom view_get_zf_itemname zfiwheregzi.itemno = zfi.bm_itemno(+)and zfi.year(+) = to_char(gz.make_date, 'yyyy')) end) as zf_itemname
131
132 from gk_zfpz gz,bs_bdg_unitbu,bs_zjlybz, bs_gnflyskmbg, gk_zfpz_item gzi
133 where gz.budgetno = bg.func_bdgid(+)
134 and gz.unitno = bu.unitno(+)
135 and gz.src_id = bz.fundno(+)
136 and gz.budget_type = bg.func_bdg_type(+)
137 and to_char(gz.make_date, 'yyyy') = bg.bdgyear
138 and gz.voucher_type = '1'
139 and gz.id = gzi.gk_zfpz_id(+)
140
141 ) gz_i
142 ) v;
143 1 create or replace procedure PRO_K3_ZFZXZ(org_type in varchar2, --预算内外
2 opertype in varchar2, --操作类型
3 fdate in varchar2, --日期
4 i_pk_id in varchar2, --主键id
5 i_database in varchar2, --数据
6 i_fgroup in varchar2, -- 字号,
7 i_fpreparer in number, -- 制单人,
8 i_fattachment in number, -- 附件数
9 o_ret out number
10 ) is
11
12
13 v_total number(16, 2);
14 v_fserialnum_1 number(16, 2); -- 流水号
15 v_fserialnum_2 number(16, 2); -- 流水号
16 v_max_index number;
17 v_count number;
18
19
20 begin
21 o_ret := 0;
22 select nextid('GK_ZWVCH_SEQ') into v_fserialnum_1 from dual;
23 select nextid('GK_ZWVCH_SEQ') into v_fserialnum_2 from dual;
24
25
26 select count(1) into v_count fromGK_ZWVCH_IDS a where a.pk_id= i_pk_id;
27
28 if v_count = 0 then
29 return ;
30 end if;
31
32 --先判断入参是否有值，如果没有值 整个存储过程返回空值
33 if org_type is not null and opertype is not null and fdate is not nulland i_pk_id is not null then
34
35 --插入主表
36 insert into gk_zwvch
37 (fserialnum,
38 FPERIOD,
39 fgroup,
40 fdate,
41 fnum,
42 fpreparer,
43 fattachment,
44 fmodule,
45 jz_database,
46 org_type,
47 jz_fserialnum,
48 orign_type,
49 make_date)
50
51 select v_fserialnum_1,
52 substr(fdate, 6, 2),
53 i_fgroup,
54 to_date(fdate, 'yyyy-MM-dd'),
55 0,
56 i_fpreparer,
57 i_fattachment,
58 1,
59 i_database,
60 org_type,
61 0,
62 '1',
63 sysdate
64
65 from dual;
66
67 /*直接支付 正常对碰*/
68 if opertype = 0 then
69
70 insert into gk_zwentry(fserialnum,fentryid,facctid,fclsid,fexp,fdebit,fcredit,fbanksettel,ftransid,fcyid,fobjid,zf_itemno,zf_itemname)
71 select fserialnum,ROWNUM,facctid,fclsid,fexp,fdebit,fcredit,fbanksettel,ftransid,fcyid,fobjid,zf_itemno,zf_itemname from (
72 SELECT v_fserialnum_1 fserialnum ,z.bdgt_accid || '01' || (select a.src_id_identify from bs_zjly a where a.fundno = Z.SRC_ID) facctid,'1' fclsid ,min(z.purpose) fexp,SUM(z.zf_item_amt) fdebit,0 fcredit,'1' fbanksettel ,z.prjno ftransid,'RMB' fcyid,z.unitno fobjid,z.zf_itemno zf_itemno,z.zf_itemname zf_itemname
73 FROM (
74 SELECT
75 z.bdgt_accid,
76 Z.voucher_type,
77 z.purpose purpose,
78 z.zf_item_amt,
79 z.prjno,
80 z.unitno,
81 Z.SRC_ID,
82 Z.budget_type,
83 Z.budgetno,
84 z.zf_itemno zf_itemno,
85 z.zf_itemname zf_itemname,
86 Z.ORG_TYPE
87 FROM VIEW_K3_ZFZXZ_DIR Z
88 WHERE Z.id IN
89 (select GK_ZFPZ_ID from GK_ZWVCH_IDS a where pk_id = i_pk_id)
90 ) Z GROUP BY
91 z.unitno,
92 z.prjno,
93 z.src_id,
94 z.budget_type,
95 z.budgetno,
96 z.bdgt_accid,
97 --z.purpose,
98 z.prjno,
99 z.zf_itemno
100 ,z.org_type,
101 z.voucher_type
102 ,z.zf_itemname);
103
104 select sum(a.fdebit) into v_total from gk_zwentry a where a.fserialnum = v_fserialnum_1 ;
105 select max(a.fentryid) + 1 into v_max_index from gk_zwentry a where a.fserialnum = v_fserialnum_1;
106 insert into gk_zwentry
107 (fserialnum,
108 fentryid,
109 facctid,
110 fclsid,
111 fexp,
112 fdebit,
113 fcredit,
114 fbanksettel,
115 ftransid,
116 fcyid,
117 fobjid,
118 zf_itemno,
119 zf_itemname)
120 SELECT v_fserialnum_1,
121 v_max_index,
122 '1005',
123 '0',
124 '',
125 0,
126 v_total,
127 '1',
128 '000',
129 'RMB',
130 '*',
131 '',
132 ''
133 from dual;
134
135 insert into gk_zwvch
136 (fserialnum,
137 FPERIOD,
138 fgroup,
139 fdate,
140 fnum,
141 fpreparer,
142 fattachment,
143 fmodule,
144 jz_database,
145 org_type,
146 jz_fserialnum,
147 orign_type,
148 make_date)
149
150 select v_fserialnum_2,
151 substr(fdate, 6, 2),
152 i_fgroup,
153 to_date(fdate, 'yyyy-MM-dd'),
154 0,
155 i_fpreparer,
156 i_fattachment,
157 1,
158 i_database,
159 org_type,
160 0,
161 '1',
162 sysdate
163
164 from dual;
165
166
167 insert into gk_zwentry
168 (fserialnum,
169 fentryid,
170 facctid,
171 fclsid,
172 fexp,
173 fdebit,
174 fcredit,
175 fbanksettel,
176 ftransid,
177 fcyid,
178 fobjid,
179 zf_itemno,
180 zf_itemname)
181 SELECT v_fserialnum_2,
182 1,
183 '1005',
184 '0',
185 '',
186 v_total,
187 0,
188 '1',
189 '000',
190 'RMB',
191 '*',
192 '',
193 ''
194 from dual;
195
196 insert into gk_zwentry
197 (fserialnum,
198 fentryid,
199 facctid,
200 fclsid,
201 fexp,
202 fdebit,
203 fcredit,
204 fbanksettel,
205 ftransid,
206 fcyid,
207 fobjid,
208 zf_itemno,
209 zf_itemname)
210 SELECT v_fserialnum_2,
211 2,
212 '2091001',
213 '0',
214 '',
215 0,
216 v_total,
217 '1',
218 '000',
219 'RMB',
220 '*',
221 '',
222 ''
223 from dual;
224
225 insert into gk_zwvch_refer(FSERIALNUM, orign_type,orign_id,voucher_type,origin_teable,fmodule)
226 select v_fserialnum_1 ,
227 '1' ORIGN_TYPE,
228 z.id ORIGN_ID,
229 z.voucher_type VOUCHER_TYPE,
230 'GK_ZFPZ' ORIGIN_TEABLE,
231 '1' FMODULE
232 from gk_zfpz z
233 WHERE Z.id IN
234 (select GK_ZFPZ_ID from GK_ZWVCH_IDS a where pk_id = i_pk_id);
235
236
237 end if;
238 /*正常对碰 授权支付*/
239 if opertype = '1' then
240
241 insert into gk_zwentry(fserialnum,fentryid,facctid,fclsid,fexp,fdebit,fcredit,fbanksettel,ftransid,fcyid,fobjid,zf_itemno,zf_itemname)
242 select fserialnum,ROWNUM,facctid,fclsid,fexp,fdebit,fcredit,fbanksettel,ftransid,fcyid,fobjid,zf_itemno,zf_itemname from (
243 SELECT v_fserialnum_1 fserialnum ,z.bdgt_accid || '02' || (select a.src_id_identify from bs_zjly a where a.fundno = Z.SRC_ID) facctid,'1' fclsid ,min(z.purpose) fexp,SUM(z.zf_item_amt) fdebit,0 fcredit,'1' fbanksettel ,z.prjno ftransid,'RMB' fcyid,z.unitno fobjid,z.zf_itemno zf_itemno,z.zf_itemname zf_itemname
244 FROM (
245 SELECT
246 z.bdgt_accid,
247 Z.voucher_type,
248 z.purpose purpose,
249 z.zf_item_amt,
250 z.prjno,
251 z.unitno,
252 Z.SRC_ID,
253 Z.budget_type,
254 Z.budgetno,
255 z.zf_itemno zf_itemno,
256 z.zf_itemname zf_itemname,
257 Z.ORG_TYPE
258 FROM VIEW_K3_ZFZXZ_ACC Z
259 WHERE Z.id IN
260 (select GK_ZFPZ_ID from GK_ZWVCH_IDS a where pk_id = i_pk_id)
261 ) Z GROUP BY
262 z.unitno,
263 z.prjno,
264 z.src_id,
265 z.budget_type,
266 z.budgetno,
267 z.bdgt_accid,
268 --z.purpose,
269 z.prjno,
270 z.zf_itemno
271 ,z.org_type,
272 z.voucher_type
273 ,z.zf_itemname);
274
275
276 select max(a.fentryid) + 1 into v_max_index from gk_zwentry a where a.fserialnum = v_fserialnum_1;
277 select sum(a.fdebit) into v_total from gk_zwentry a where a.fserialnum = v_fserialnum_1 ;
278
279
280 insert into gk_zwentry
281 (fserialnum,
282 fentryid,
283 facctid,
284 fclsid,
285 fexp,
286 fdebit,
287 fcredit,
288 fbanksettel,
289 ftransid,
290 fcyid,
291 fobjid,
292 zf_itemno,
293 zf_itemname)
294 SELECT v_fserialnum_1,
295 v_max_index,
296 '2091002',
297 '0',
298 '',
299 0,
300 v_total,
301 '1',
302 '000',
303 'RMB',
304 '*',
305 '',
306 ''
307 from dual;
308
309 insert into gk_zwvch_refer(FSERIALNUM, orign_type,orign_id,voucher_type,origin_teable,fmodule)
310 select v_fserialnum_1 ,
311 '1' ORIGN_TYPE,
312 z.id ORIGN_ID,
313 z.voucher_type VOUCHER_TYPE,
314 'GK_ZFPZ' ORIGIN_TEABLE,
315 '1' FMODULE
316 from gk_zfpz z
317 WHERE Z.id IN
318 (select GK_ZFPZ_ID from GK_ZWVCH_IDS a where pk_id = i_pk_id);
319
320
321 end if;
322 /*全额退款的直接支付 */
323 if opertype = '2' then
324
325 insert into gk_zwentry(fserialnum,fentryid,facctid,fclsid,fexp,fdebit,fcredit,fbanksettel,ftransid,fcyid,fobjid,zf_itemno,zf_itemname)
326 select fserialnum,ROWNUM+1,facctid,fclsid,fexp,fdebit,fcredit,fbanksettel,ftransid,fcyid,fobjid,zf_itemno,zf_itemname from (
327 SELECT v_fserialnum_1 fserialnum ,z.bdgt_accid || '01' || (select a.src_id_identify from bs_zjly a where a.fundno = Z.SRC_ID) facctid,'1' fclsid ,min(z.purpose) fexp,SUM(z.zf_item_amt) fdebit,0 fcredit,'1' fbanksettel ,z.prjno ftransid,'RMB' fcyid,z.unitno fobjid,z.zf_itemno zf_itemno,z.zf_itemname zf_itemname
328 FROM (
329 SELECT
330 z.bdgt_accid,
331 Z.voucher_type,
332 z.purpose purpose,
333 z.zf_item_amt,
334 z.prjno,
335 z.unitno,
336 Z.SRC_ID,
337 Z.budget_type,
338 Z.budgetno,
339 z.zf_itemno zf_itemno,
340 z.zf_itemname zf_itemname,
341 Z.ORG_TYPE
342 FROM VIEW_K3_ZFZXZ_DIR Z
343 WHERE Z.id IN
344 (select GK_ZFPZ_ID from GK_ZWVCH_IDS a where pk_id = i_pk_id)
345 ) Z GROUP BY
346 z.unitno,
347 z.prjno,
348 z.src_id,
349 z.budget_type,
350 z.budgetno,
351 z.bdgt_accid,
352 --z.purpose,
353 z.prjno,
354 z.zf_itemno
355 ,z.org_type,
356 z.voucher_type
357 ,z.zf_itemname);
358
359 select sum(a.fdebit) into v_total from gk_zwentry a where a.fserialnum = v_fserialnum_1 ;
360
361
362
363 insert into gk_zwentry
364 (fserialnum,
365 fentryid,
366 facctid,
367 fclsid,
368 fexp,
369 fdebit,
370 fcredit,
371 fbanksettel,
372 ftransid,
373 fcyid,
374 fobjid,
375 zf_itemno,
376 zf_itemname)
377 SELECT v_fserialnum_1,
378 1,
379 '1005',
380 '0',
381 '',
382 v_total,
383 0,
384 '1',
385 '000',
386 'RMB',
387 '*',
388 '',
389 ''
390 from dual;
391
392
393
394
395 insert into gk_zwvch
396 (fserialnum,
397 FPERIOD,
398 fgroup,
399 fdate,
400 fnum,
401 fpreparer,
402 fattachment,
403 fmodule,
404 jz_database,
405 org_type,
406 jz_fserialnum,
407 orign_type,
408 make_date)
409
410 select v_fserialnum_2,
411 substr(fdate, 6, 2),
412 i_fgroup,
413 to_date(fdate, 'yyyy-MM-dd'),
414 0,
415 i_fpreparer,
416 i_fattachment,
417 1,
418 i_database,
419 org_type,
420 0,
421 '1',
422 sysdate
423
424 from dual;
425
426 insert into gk_zwentry
427 (fserialnum,
428 fentryid,
429 facctid,
430 fclsid,
431 fexp,
432 fdebit,
433 fcredit,
434 fbanksettel,
435 ftransid,
436 fcyid,
437 fobjid,
438 zf_itemno,
439 zf_itemname)
440 SELECT v_fserialnum_2,
441 1,
442 '2091001',
443 '0',
444 '',
445 v_total,
446 0,
447 '1',
448 '000',
449 'RMB',
450 '*',
451 '',
452 ''
453 from dual;
454
455
456 insert into gk_zwentry
457 (fserialnum,
458 fentryid,
459 facctid,
460 fclsid,
461 fexp,
462 fdebit,
463 fcredit,
464 fbanksettel,
465 ftransid,
466 fcyid,
467 fobjid,
468 zf_itemno,
469 zf_itemname)
470 SELECT v_fserialnum_2,
471 2,
472 '1005',
473 '0',
474 '',
475 0,
476 v_total,
477 '1',
478 '000',
479 'RMB',
480 '*',
481 '',
482 ''
483 from dual;
484
485 insert into gk_zwvch_refer(FSERIALNUM, orign_type,orign_id,voucher_type,origin_teable,fmodule)
486 select v_fserialnum_1 ,
487 '3' ORIGN_TYPE,
488 z.id ORIGN_ID,
489 z.voucher_type VOUCHER_TYPE,
490 'GK_ZFPZ' ORIGIN_TEABLE,
491 '1' FMODULE
492 from gk_zfpz z
493 WHERE Z.id IN
494 (select GK_ZFPZ_ID from GK_ZWVCH_IDS a where pk_id = i_pk_id);
495
496
497 end if;
498 /*全额退款的授权支付*/
499 if opertype = '3' then
500
501 insert into gk_zwentry(fserialnum,fentryid,facctid,fclsid,fexp,fdebit,fcredit,fbanksettel,ftransid,fcyid,fobjid,zf_itemno,zf_itemname)
502 select fserialnum,ROWNUM+1,facctid,fclsid,fexp,fdebit,fcredit,fbanksettel,ftransid,fcyid,fobjid,zf_itemno,zf_itemname from (
503 SELECT v_fserialnum_1 fserialnum ,z.bdgt_accid || '02' || (select a.src_id_identify from bs_zjly a where a.fundno = Z.SRC_ID) facctid,'1' fclsid ,min(z.purpose) fexp,SUM(z.zf_item_amt) fdebit,0 fcredit,'1' fbanksettel ,z.prjno ftransid,'RMB' fcyid,z.unitno fobjid,z.zf_itemno zf_itemno,z.zf_itemname zf_itemname
504 FROM (
505 SELECT
506 z.bdgt_accid,
507 Z.voucher_type,
508 z.purpose purpose,
509 z.zf_item_amt,
510 z.prjno,
511 z.unitno,
512 Z.SRC_ID,
513 Z.budget_type,
514 Z.budgetno,
515 z.zf_itemno zf_itemno,
516 z.zf_itemname zf_itemname,
517 Z.ORG_TYPE
518 FROM VIEW_K3_ZFZXZ_ACC Z
519 WHERE Z.id IN
520 (select GK_ZFPZ_ID from GK_ZWVCH_IDS a where pk_id = i_pk_id)
521 ) Z GROUP BY
522 z.unitno,
523 z.prjno,
524 z.src_id,
525 z.budget_type,
526 z.budgetno,
527 z.bdgt_accid,
528 --z.purpose,
529 z.prjno,
530 z.zf_itemno
531 ,z.org_type,
532 z.voucher_type
533 ,z.zf_itemname);
534
535 select sum(a.fdebit) into v_total from gk_zwentry a where a.fserialnum = v_fserialnum_1 ;
536
537
538 insert into gk_zwentry
539 (fserialnum,
540 fentryid,
541 facctid,
542 fclsid,
543 fexp,
544 fdebit,
545 fcredit,
546 fbanksettel,
547 ftransid,
548 fcyid,
549 fobjid,
550 zf_itemno,
551 zf_itemname)
552 SELECT v_fserialnum_1,
553 1,
554 '2091002',
555 '0',
556 '',
557 v_total,
558 0,
559 '1',
560 '000',
561 'RMB',
562 '*',
563 '',
564 ''
565 from dual;
566
567
568 insert into gk_zwvch_refer(FSERIALNUM, orign_type,orign_id,voucher_type,origin_teable,fmodule)
569 select v_fserialnum_1 ,
570 '3' ORIGN_TYPE,
571 z.id ORIGN_ID,
572 z.voucher_type VOUCHER_TYPE,
573 'GK_ZFPZ' ORIGIN_TEABLE,
574 '1' FMODULE
575 from gk_zfpz z
576 WHERE Z.id IN
577 (select GK_ZFPZ_ID from GK_ZWVCH_IDS a where pk_id = i_pk_id);
578
579 end if;
580
581
582
583
584
585 end if;
586 exception
587 when others then
588 o_ret := 1;
589 end PRO_K3_ZFZXZ;
590 /
591
页:
[1]