/**PageBeginHtml Block Begin **/ /***自定义返回顶部小火箭***/ /*生成博客目录的JS 开始*/ /*生成博客目录的JS 结束*/

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[i]==(delIds_jjfl[j])) {
 32       	var vo = new com.todaytech.yth.gdsd.gkzf.k3.model.ZfpzVO();
 33           vo.setGk_zfpz_id(parseInt(zfpzid_jjfl[i]));
 34           vo.setVoucher_no(voucher_no_jjfl[i]);   //支付凭证号
 35           vo.setVoucher_type(voucher_type_jjfl[i]);  //凭证类型
 36           vo.setAmt(new java.math.BigDecimal(amt_jjfl[i]));  //拨款金额
 37           vo.setPurpose(oper_idea_jjfl.replace(/^\s\s*/, '').replace(/\s\s*$/, '').length!=0?/*"("+oper_idea+")("+*/arrpurpose_jjfl[i]/*+")"*/:arrpurpose_jjfl[i]);//用途说明----用于摘要
 38           vo.setCheck_status(check_status_jjfl[i]);        // 对碰状态
 39           vo.setMappingTable(mappingtable_jjfl[i]);//来源表
 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[i]==(delIds[j])) {
 82         	var vo = new com.todaytech.yth.gdsd.gkzf.k3.model.ZfpzVO();
 83             vo.setGk_zfpz_id(parseInt(zfpzid[i]));
 84             vo.setVoucher_no(voucher_no[i]);   //支付凭证号
 85             vo.setVoucher_type(voucher_type[i]);  //凭证类型
 86             vo.setAmt(new java.math.BigDecimal(amt[i]));  //拨款金额
 87             vo.setPurpose(oper_idea.replace(/^\s\s*/, '').replace(/\s\s*$/, '').length!=0?/*"("+oper_idea+")("+*/arrpurpose[i]/*+")"*/:arrpurpose[i]);//用途说明----用于摘要
 88             vo.setCheck_status(check_status[i]);        // 对碰状态
 89             vo.setMappingTable(mappingtable[i]);//来源表
 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') nextid  FROM 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[i].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[transaction],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[index]+"');";
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[index]+");";
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.value  as 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[i].get("databasename");
183        		    }
184          } else if (pzData.src_type=="1" ) {//预算外
185              //获取支付中心预算外对应数据库
186              var sqlZfzx = "select t.value  as databasename  from 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[i].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.amt  as AMT,
  7                   (case  when v.PURPOSE is null then   '-'   else  v.PURPOSE  end) 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      '-'   else  v.bm_itemnos
 16                   end) as bm_itemnos
 17     FROM (select  gz_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' AND  substr( 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' AND  gz.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                          ( select  zfi.zf_itemno  from view_get_zf_itemname zfi  where  zqdi.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 != '-' and  zqdi.itemno = '301' 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'))
 53                        when zqdi.itemno is not null and zqdi.itemno != '-' and    zqdi.itemno = '301' 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 = '50501'   and bzj.bdgyear = to_char(gz.make_date, 'yyyy'))
 54                        when zqdi.itemno is not null and zqdi.itemno != '-' and   zqdi.itemno = '302' AND  substr( 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 bzj  where 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' AND  substr( gz.unitno, 0, 2) NOT IN ('00', '20') THEN  (select bzj.econ_bdg   from bs_zf_jjflyskm bzj  where bzj.econ_bdgid = '50501'  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'))
 58                        when zqdi.itemno is not null and zqdi.itemno != '-' and   zqdi.itemno = '30311' AND  gz.unitno <> '0017' 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 = '50501'   and bzj.bdgyear = to_char(gz.make_date, 'yyyy'))
 59                        when zqdi.itemno is not null and zqdi.itemno != '-' and  zqdi.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 bzj  where bzj.econ_bdgid = '50799'  and bzj.bdgyear = to_char(gz.make_date, 'yyyy'))
 60                        when zqdi.itemno is not null and zqdi.itemno != '-' and  zqdi.itemno = '30403' 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 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 != '-' and  zqdi.itemno = '399' THEN (select bzj.econ_bdg  from 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 != '-' and  zqdi.itemno = '312' 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 bzj  where bzj.econ_bdgid = '50799' and bzj.bdgyear = to_char(gz.make_date, 'yyyy'))
 63                        when zqdi.itemno is not null and zqdi.itemno != '-' and  zqdi.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 bzj  where bzj.econ_bdgid = '50799'  and bzj.bdgyear = to_char(gz.make_date, 'yyyy'))
 64                        when zqdi.itemno is not null and zqdi.itemno != '-' and  zqdi.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 bzj  where bzj.econ_bdgid = '50799'  and bzj.bdgyear = to_char(gz.make_date, 'yyyy'))
 65 
 66                     else    ( select  zfi.zf_itemname  from view_get_zf_itemname zfi  where  zqdi.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 null  and 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.itemno  as 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' AND  substr( 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' AND  gz.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                          ( select  zfi.zf_itemno  from view_get_zf_itemname zfi  where  gzi.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 != '-' and  gzi.itemno = '301' 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'))
117                        when gzi.itemno is not null and gzi.itemno != '-' and    gzi.itemno = '301' 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 = '50501'   and bzj.bdgyear = to_char(gz.make_date, 'yyyy'))
118                        when gzi.itemno is not null and gzi.itemno != '-' and   gzi.itemno = '302' AND  substr( 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 bzj  where 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' AND  substr( gz.unitno, 0, 2) NOT IN ('00', '20') THEN  (select bzj.econ_bdg   from bs_zf_jjflyskm bzj  where bzj.econ_bdgid = '50501'  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'))
122                        when gzi.itemno is not null and gzi.itemno != '-' and   gzi.itemno = '30311' AND  gz.unitno <> '0017' 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 = '50501'   and bzj.bdgyear = to_char(gz.make_date, 'yyyy'))
123                        when gzi.itemno is not null and gzi.itemno != '-' and  gzi.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 bzj  where bzj.econ_bdgid = '50799'  and bzj.bdgyear = to_char(gz.make_date, 'yyyy'))
124                        when gzi.itemno is not null and gzi.itemno != '-' and  gzi.itemno = '30403' 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 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 != '-' and  gzi.itemno = '399' THEN (select bzj.econ_bdg  from 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 != '-' and  gzi.itemno = '312' 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 bzj  where bzj.econ_bdgid = '50799' and bzj.bdgyear = to_char(gz.make_date, 'yyyy'))
127                        when gzi.itemno is not null and gzi.itemno != '-' and  gzi.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 bzj  where bzj.econ_bdgid = '50799'  and bzj.bdgyear = to_char(gz.make_date, 'yyyy'))
128                        when gzi.itemno is not null and gzi.itemno != '-' and  gzi.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 bzj  where bzj.econ_bdgid = '50799'  and bzj.bdgyear = to_char(gz.make_date, 'yyyy'))
129 
130                     else    ( select  zfi.zf_itemname  from view_get_zf_itemname zfi  where  gzi.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_unit  bu,  bs_zjly  bz,   bs_gnflyskm  bg,   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.amt  as AMT,
  7                   (case  when v.PURPOSE is null then   '-'   else  v.PURPOSE  end) 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      '-'   else  v.bm_itemnos
 16                   end) as bm_itemnos
 17     FROM (/*select  gz_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' AND  substr( 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' AND  gz.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                          ( select  zfi.zf_itemno  from view_get_zf_itemname zfi  where  zqdi.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 != '-' and  zqdi.itemno = '301' 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'))
 53                        when zqdi.itemno is not null and zqdi.itemno != '-' and    zqdi.itemno = '301' 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 = '50501'   and bzj.bdgyear = to_char(gz.make_date, 'yyyy'))
 54                        when zqdi.itemno is not null and zqdi.itemno != '-' and   zqdi.itemno = '302' AND  substr( 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 bzj  where 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' AND  substr( gz.unitno, 0, 2) NOT IN ('00', '20') THEN  (select bzj.econ_bdg   from bs_zf_jjflyskm bzj  where bzj.econ_bdgid = '50501'  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'))
 58                        when zqdi.itemno is not null and zqdi.itemno != '-' and   zqdi.itemno = '30311' AND  gz.unitno <> '0017' 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 = '50501'   and bzj.bdgyear = to_char(gz.make_date, 'yyyy'))
 59                        when zqdi.itemno is not null and zqdi.itemno != '-' and  zqdi.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 bzj  where bzj.econ_bdgid = '50799'  and bzj.bdgyear = to_char(gz.make_date, 'yyyy'))
 60                        when zqdi.itemno is not null and zqdi.itemno != '-' and  zqdi.itemno = '30403' 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 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 != '-' and  zqdi.itemno = '399' THEN (select bzj.econ_bdg  from 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 != '-' and  zqdi.itemno = '312' 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 bzj  where bzj.econ_bdgid = '50799' and bzj.bdgyear = to_char(gz.make_date, 'yyyy'))
 63                        when zqdi.itemno is not null and zqdi.itemno != '-' and  zqdi.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 bzj  where bzj.econ_bdgid = '50799'  and bzj.bdgyear = to_char(gz.make_date, 'yyyy'))
 64                        when zqdi.itemno is not null and zqdi.itemno != '-' and  zqdi.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 bzj  where bzj.econ_bdgid = '50799'  and bzj.bdgyear = to_char(gz.make_date, 'yyyy'))
 65 
 66                     else    ( select  zfi.zf_itemname  from view_get_zf_itemname zfi  where  zqdi.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 null  and 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.itemno  as 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' AND  substr( 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' AND  gz.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                          ( select  zfi.zf_itemno  from view_get_zf_itemname zfi  where  gzi.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 != '-' and  gzi.itemno = '301' 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'))
117                        when gzi.itemno is not null and gzi.itemno != '-' and    gzi.itemno = '301' 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 = '50501'   and bzj.bdgyear = to_char(gz.make_date, 'yyyy'))
118                        when gzi.itemno is not null and gzi.itemno != '-' and   gzi.itemno = '302' AND  substr( 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 bzj  where 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' AND  substr( gz.unitno, 0, 2) NOT IN ('00', '20') THEN  (select bzj.econ_bdg   from bs_zf_jjflyskm bzj  where bzj.econ_bdgid = '50501'  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'))
122                        when gzi.itemno is not null and gzi.itemno != '-' and   gzi.itemno = '30311' AND  gz.unitno <> '0017' 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 = '50501'   and bzj.bdgyear = to_char(gz.make_date, 'yyyy'))
123                        when gzi.itemno is not null and gzi.itemno != '-' and  gzi.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 bzj  where bzj.econ_bdgid = '50799'  and bzj.bdgyear = to_char(gz.make_date, 'yyyy'))
124                        when gzi.itemno is not null and gzi.itemno != '-' and  gzi.itemno = '30403' 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 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 != '-' and  gzi.itemno = '399' THEN (select bzj.econ_bdg  from 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 != '-' and  gzi.itemno = '312' 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 bzj  where bzj.econ_bdgid = '50799' and bzj.bdgyear = to_char(gz.make_date, 'yyyy'))
127                        when gzi.itemno is not null and gzi.itemno != '-' and  gzi.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 bzj  where bzj.econ_bdgid = '50799'  and bzj.bdgyear = to_char(gz.make_date, 'yyyy'))
128                        when gzi.itemno is not null and gzi.itemno != '-' and  gzi.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 bzj  where bzj.econ_bdgid = '50799'  and bzj.bdgyear = to_char(gz.make_date, 'yyyy'))
129 
130                     else    ( select  zfi.zf_itemname  from view_get_zf_itemname zfi  where  gzi.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_unit  bu,  bs_zjly  bz,   bs_gnflyskm  bg,   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 from  GK_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 null  and 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
posted @ 2018-04-26 22:03  一品堂.技术学习笔记  阅读(766)  评论(0)  编辑  收藏  举报