注：本文为博主 原创。
jdbctemplate 调用oracle存储过程 事务 临时表 有返回结果
1：java 代码
本逻辑代码本是想把 java 代码里的list<Strign>类型的数据传递到Oracle的为数组入参的存储过程中。如《 java怎样将一组对象传入Oracle存储过程 、数组做为参数传入Oracle存储过程操作数据库 》的逻辑一般操作。在结合jdbctemplate 。使用了《Spring如何使用JdbcTemplate调用存储过程的三种情况》中的“ 三、有返回值的存储过程（结果集） ”的调用方法，把list集合的数据给塞进到oracle存储过程中的 “arr_t in tables_array”入参中。但是也不知道是否因为框架的限制因素还是其他原因，导致在使用了《Spring如何使用JdbcTemplate调用存储过程的三种情况》中的“ 三、有返回值的存储过程（结果集） ”的调用方法里的“cs.setString(1, "p1");// 设置输入参数的值 ”切换为”cs.setArray(1, listAarry) 设置输入参数的值”的时候， listArray的值切换时就是失败了的；即：把下一段代码中的list集合切换为 一个存储过程数组入参的类型没有成功的。因为这个conn链接一直就没有转化成功。
1 con = DriverManager.getConnection(connectionURL, userID, userPassword);
2 StructDescriptor tDescriptor = StructDescriptor.createDescriptor("TEST_OBJECT", con);
3 List<STRUCT> structs = new ArrayList<STRUCT>();
4 Object[] tObject = null ;
5 //能够将系统中VO，DTO转化成Object对象,先创建struts
6 for(int i = 0; i<10; i++){
7 tObject = new Object[2];
8 tObject[0] = i;
9 tObject[1] = "name"+i;
10 STRUCT tStruct = new STRUCT(tDescriptor, con, tObject);
11 structs.add(tStruct);
12 }
13 ArrayDescriptor arrayDescriptor = ArrayDescriptor.createDescriptor("TABLES_ARRAY", con);
14 ARRAY tArray = new ARRAY(arrayDescriptor, con, structs.toArray());最后没有办法，想到了一招，先把这个list集合写到用这个业务表中去。如果在去去存储过程中对该业务表中取出该业务数据，在进行相对应的业务数据的拼装操作。
本框架使用的 srping-jdbc 连接。使用了的jdbc是jdbctemplate 。使用了《Spring如何使用JdbcTemplate调用存储过程的三种情况》中的“ 三、有返回值的存储过程（结果集） ”的调用方法。具体如下：
1 public List getGkzfpz_jjflList (DaoFactory daoFactory, List lstZfpzId,ZwvchVO zwvchvo){
2 /*String strSQL ="select * from view_k3_zfzxz_zfpz v WHERE 1 = 1 ";
3 List<Map<String, Object>>lstZfpz = new ArrayList();
4
5 try {
6
7 if(lstZfpzId.size()>0) {
8 strSQL += " AND ( ";
9 for (int index=0 ;index <lstZfpzId.size();index ++) {
10 if(index == 0){
11 strSQL += " v.id in ("+lstZfpzId.get(index)+")";
12 }else {
13 strSQL += " OR v.id in ("+lstZfpzId.get(index)+")";
14 }
15
16 }
17 strSQL += ")";
18 }
19 lstZfpz = daoFactory.getDao().getJdbcTemplate().queryForList(strSQL);
20 for(int i=0;i<lstZfpz.size();i++){
21 lstZfpz.get(i).put("org_type",zwvchvo.getOrg_type());
22 }
23
24 } catch (Exception e) {
25 e.printStackTrace();
26 }*/
27 final long zfpzIds_Pch = getZfpzIds_pch(daoFactory,lstZfpzId,zwvchvo);
28
29 List<Map<String, Object>> lstZfpz_Pro= getGkzfpz_JjflList_By_Pro(daoFactory,lstZfpzId,zwvchvo, zfpzIds_Pch);
30
31 return lstZfpz_Pro;
32 }
33
34
35 /**
36 *
37 * @Title: getZfpzIds_pch
38 * @Description: 获取数据批次号，和数据的生成
39 * @param: @param daoFactory
40 * @param: @param lstZfpzId
41 * @param: @param zwvchvo
42 * @param: @return
43 * @return: long
44 * @throws
45 */
46 @Transactional(propagation=Propagation.REQUIRES_NEW )
47 public long getZfpzIds_pch(DaoFactory daoFactory, List lstZfpzId,ZwvchVO zwvchvo){
48 /**
49 * 获取批次号
50 */
51 long zfpzIds_Pch =0;
52 try {
53 String sql_pch =" select max(z.pcno)+1 as pch from gk_zwvch_zfzxz_zfpz_ids z ";
54 List<Map<String, Object>> list_PchList=daoFactory.getDao().getJdbcTemplate().queryForList(sql_pch);
55 //批次号
56 zfpzIds_Pch = Long.parseLong(((list_PchList.get(0).get("PCH"))+"").trim()) ;
57 //把界面的ids 插入表gk_zwvch_zfzxz_zfpz_ids中
58 StringBuffer ZFPZ_IDS_sql=new StringBuffer();;
59 ZFPZ_IDS_sql.append("begin\n");
60 for (int i = 0; i < lstZfpzId.size(); i++) {
61 String strSQL_mx ="";
62 strSQL_mx = "";
63 strSQL_mx += "insert into gk_zwvch_zfzxz_zfpz_ids (id, pcno, zfpz_ids) values ( NEXTID('GK_ZWVCH_ZFZXZ_ZFPZ_IDS_SEQ'), "+zfpzIds_Pch+", '"+lstZfpzId.get(i)+"') ;";
64 ZFPZ_IDS_sql.append(strSQL_mx);
65 }
66 //增加SQL语句块支持
67 ZFPZ_IDS_sql.append( " end;\n ");
68
69 daoFactory.getDao().getJdbcTemplate().execute(ZFPZ_IDS_sql.toString());
70 //提交数据 jdbcTemplate.getDataSource().getConnection().commit();
71 daoFactory.getDao().getJdbcTemplate().getDataSource().getConnection().commit();
72
73 } catch (Exception e) {
74 try {
75 daoFactory.getDao().getJdbcTemplate().getDataSource().getConnection().rollback();
76 } catch (SQLException e1) {
77 e1.printStackTrace();
78 }
79 e.printStackTrace();
80 }finally{
81 try {
82 daoFactory.getDao().getJdbcTemplate().getDataSource().getConnection().close();
83 } catch (SQLException e) {
84 e.printStackTrace();
85 }
86 }
87
88
89 return zfpzIds_Pch ;
90 }
91 /**
92 *
93 * @param daoFactory
94 * @param lstZfpzId 界面选择的需要生成会计凭证的凭证id
95 * @param zwvchvo
96 * @param zfpzIds_Pch
97 * @return
98 */
99 @Transactional(propagation=Propagation.REQUIRES_NEW )
100 public List getGkzfpz_JjflList_By_Pro(DaoFactory daoFactory, List lstZfpzId,ZwvchVO zwvchvo ,final long zfpzIds_Pch){
101 List<Map<String, Object>> lstZfpz = new ArrayList();
102 try {
103
104 /**
105 * 调用储存过程 PRO_K3_ZFZXZ 返回合并查询结果
106 */
107 List resultList = (List) daoFactory.getDao().getJdbcTemplate().execute(
108 new CallableStatementCreator() {
109 public CallableStatement createCallableStatement(Connection con) throws SQLException {
110
111 String storedProc = "{call PRO_K3_ZFZXZ(?,?)}";// 调用的sql
112 CallableStatement cs = con.prepareCall(storedProc);
113 cs.setLong (1, zfpzIds_Pch);// 设置输入参数的值
114 cs.registerOutParameter(2, OracleTypes.CURSOR);// 注册输出参数的类型
115 return cs;
116 }
117 }, new CallableStatementCallback() {
118 public Object doInCallableStatement(CallableStatement cs) throws SQLException,DataAccessException {
119 List resultsMap = new ArrayList();
120 cs.execute();
121 ResultSet rs = (ResultSet) cs.getObject(2);// 获取游标一行的值
122 while (rs.next()) {// 转换每行的返回值到Map中
123 Map<String, Object> rowMap = new HashMap();
124 rowMap.put("gk_zfpz_id", rs.getString("gk_zfpz_id"));
125 rowMap.put("id", rs.getString("id"));
126 rowMap.put("origin_id", rs.getString("origin_id"));
127 rowMap.put("voucher_no", rs.getString("voucher_no"));
128 rowMap.put("make_date", rs.getString("make_date"));
129 rowMap.put("fk_account", rs.getString("fk_account"));
130 rowMap.put("fk_bank", rs.getString("fk_bank"));
131 rowMap.put("src_id", rs.getString("src_id"));
132 rowMap.put("sk_name", rs.getString("sk_name"));
133 rowMap.put("sk_account", rs.getString("sk_account"));
134 rowMap.put("sk_bank", rs.getString("sk_bank"));
135 rowMap.put("unitno", rs.getString("unitno"));
136 rowMap.put("unit_name", rs.getString("unit_name"));
137 rowMap.put("budget_type", rs.getString("budget_type"));
138 rowMap.put("bdgt_accid", rs.getString("bdgt_accid"));
139 rowMap.put("budgetno", rs.getString("budgetno"));
140 rowMap.put("budget_name", rs.getString("budget_name"));
141 rowMap.put("prjno", rs.getString("prjno"));
142 rowMap.put("prj_name", rs.getString("prj_name"));
143 rowMap.put("zfpz_amt", rs.getString("zfpz_amt"));
144 rowMap.put("amt", rs.getString("amt"));
145 rowMap.put("purpose", rs.getString("purpose"));
146 rowMap.put("remark", rs.getString("remark"));
147 rowMap.put("checker", rs.getString("checker"));
148 rowMap.put("checker_date", rs.getString("checker_date"));
149 rowMap.put("affirmer", rs.getString("affirmer"));
150 rowMap.put("affirm_date", rs.getString("affirm_date"));
151 rowMap.put("voucher_type", rs.getString("voucher_type"));
152 rowMap.put("check_status", rs.getString("check_status"));
153 rowMap.put("wf_status", rs.getString("wf_status"));
154 rowMap.put("gk_hzqsd_id", rs.getString("gk_hzqsd_id"));
155 rowMap.put("fund", rs.getString("fund"));
156 rowMap.put("org_type", rs.getString("org_type"));
157 rowMap.put("back_oper_id", rs.getString("back_oper_id"));
158 rowMap.put("back_oper_idea", rs.getString("back_oper_idea"));
159 rowMap.put("back_oper_date", rs.getString("back_oper_date"));
160 rowMap.put("back_checker_id", rs.getString("back_checker_id"));
161 rowMap.put("back_checker_idea", rs.getString("back_checker_idea"));
162 rowMap.put("back_checker_date", rs.getString("back_checker_date"));
163 rowMap.put("zf_item_amt", rs.getString("zf_item_amt"));
164 rowMap.put("zf_itemno", rs.getString("zf_itemno"));
165 rowMap.put("zf_itemname", rs.getString("zf_itemname"));
166 rowMap.put("bm_itemnos", rs.getString("bm_itemnos"));
167 resultsMap.add(rowMap);
168 }
169 rs.close();
170 return resultsMap;
171 }
172 });
173
174 for (int i = 0; i < resultList.size(); i++) {
175 Map<String, Object> rowMap = (Map) resultList.get(i);
176 lstZfpz.add(rowMap);
177 }
178 //---清除同一个会话中 临时表gk_zwentry_k3_zfzxz_zfpz_temp 里的数据；因为此临时表是一个会话型的临时表
179 daoFactory.getDao().getJdbcTemplate().execute("truncate table gk_zwentry_k3_zfzxz_zfpz_temp ");
180
181 } catch (Exception e) {
182 e.printStackTrace();
183 }
184 return lstZfpz;
185 }
186因为我的环境问题。临时表 使用事务型一直就没有返回结果。而 把临时表改为 会话型。但是 只要系统一直没有重启，会话就一直存在。那么历史数据就一直在临时表中堆积。对最终数据结果有很大的影响。在多次的尝试之下，无奈之举，
在每次储存过程数据回调结束之后，再加上了 对该临时表数据进行全表清除的操作。
2：SQL逻辑脚本
1 CREATE OR REPLACE VIEW VIEW_K3_ZFZXZ_ZFPZ 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 order by v.unitno, v.acc_bdgid, v.prjno
143 ;
1441
2
3
4
5 ----创建 保存界面选择的zfpz表的 id
6 drop table GK_ZWVCH_ZFZXZ_ZFPZ_IDS ;
7 create table GK_ZWVCH_ZFZXZ_ZFPZ_IDS(
8 id number(20) not null primary key ,
9 pcno number(20) ,
10 zfpz_ids varchar(3000)
11 );
12
13 comment on table GK_ZWVCH_ZFZXZ_ZFPZ_IDS is '保存支付中心帐界面选择的所有支付凭证id ';
14 comment on column GK_ZWVCH_ZFZXZ_ZFPZ_IDS.ID is '主键';
15 comment on column GK_ZWVCH_ZFZXZ_ZFPZ_IDS.pcno is '批次号';
16 comment on column GK_ZWVCH_ZFZXZ_ZFPZ_IDS.zfpz_ids is 'gk_zfpz表的主键id:数据格式为：123，124，125';
17
18 ;
19
20
21 --创建索引
22 create index idx_pcno on GK_ZWVCH_ZFZXZ_ZFPZ_IDS (pcno);
23
24 insert into GK_ZWVCH_ZFZXZ_ZFPZ_IDS (ID, PCNO, ZFPZ_IDS) values (0, 0, '0,0');
25 commit;
26
27 ---创建序列号
28 insert into PWP_NO (NOID, PREFIX, INITIALVALUE, BUFFERSIZE, POSTFIX, NOINCREMENT, NONAME, NOLENGTH, NOTYPE)
29 values ('GK_ZWVCH_ZFZXZ_ZFPZ_IDS_SEQ', null, 1, 10, null, 1, 'GK_ZWVCH_ZFZXZ_ZFPZ_IDS_SEQ', 20, 1);
30 insert into PWP_NODTL (NOID, PREFIX, NODAY, NEXTID, POSTFIX, NOINCREMENT, NOYEAR, NOMONTH)
31 values ('GK_ZWVCH_ZFZXZ_ZFPZ_IDS_SEQ', null, 0, 2, null, 1, 2018, 4);
32
33 commit;
34
35
36 create or replace view view_k3_zfzxz_zfpz_group as
37 select z.unitno, z.prjno, z.src_id, z.budget_type, z.budgetno, z.zf_itemno
38 ----创建视图：进行分组。主要用于存储过程pro_k3_zfzxz 的 rowtype里的
39 from view_k3_zfzxz_zfpz z group by z.unitno, z.prjno,z.src_id, z.budget_type,z.budgetno,z.bdgt_accid, z.prjno, z.zf_itemno
40 order by z.unitno, z.prjno, z.src_id, z.budget_type, z.budgetno, z.bdgt_accid, z.prjno, z.zf_itemno ;
41 comment on table view_k3_zfzxz_zfpz_group is '该视图主要是用于 存储过程 pro_k3_zfzxz 的 rowtype里的 ；作为一个rowtype 类型使用而已 ';
42
43
44
45 create or replace view view_k3_zfzxz_zfpz_count as select count(a) as count_no from ( select '1' as a from dual );
46 comment on table view_k3_zfzxz_zfpz_count is '该视图主要是用于 存储过程 pro_k3_zfzxz 的 rowtype里的 ；作为一个rowtype 类型使用而已 ';
47
48
49 create or replace view view_k3_zfzxz_zfpz_sum as
50 select sum_amt,sum_zf_item_amt,sum_zfpz_amt,list_gk_zfpz_id,list_id,list_origin_id,list_voucher_no,list_purpose,list_remark,list_bm_itemnos from (
51 ----该视图主要是用于 存储过程 pro_k3_zfzxz 的 rowtype里的 ；作为一个rowtype 类型使用而已
52 select sum(z.amt) as sum_amt ,sum(z.zf_item_amt) as sum_zf_item_amt ,
53 sum(z.zfpz_amt) as sum_zfpz_amt ,
54 listagg(z.gk_zfpz_id, ',') within group(order by z.unitno,z.prjno,z.src_id,z.budget_type,z.budgetno,z.zf_itemno ) as list_gk_zfpz_id ,
55 listagg(z.id, ',') within group(order by z.unitno,z.prjno,z.src_id,z.budget_type,z.budgetno,z.zf_itemno ) as list_id ,
56 listagg(z.origin_id, ',') within group(order by z.unitno,z.prjno,z.src_id,z.budget_type,z.budgetno,z.zf_itemno ) as list_origin_id ,
57 listagg(z.voucher_no, ',') within group(order by z.unitno,z.prjno,z.src_id,z.budget_type,z.budgetno,z.zf_itemno ) as list_voucher_no ,
58 listagg(z.purpose, ',') within group(order by z.unitno,z.prjno,z.src_id,z.budget_type,z.budgetno,z.zf_itemno ) as list_purpose ,
59 listagg(z.remark, ',') within group(order by z.unitno,z.prjno,z.src_id,z.budget_type,z.budgetno,z.zf_itemno ) as list_remark ,
60 listagg(z.bm_itemnos, ',') within group(order by z.unitno,z.prjno,z.src_id,z.budget_type,z.budgetno,z.zf_itemno ) as list_bm_itemnos
61 from view_k3_zfzxz_zfpz z where 1=1
62 and (z.id in (1475844, 1476130, 1476144) or z.id in (1485844, 1478130, 1476644))
63 and z.unitno= 0021
64 and z.prjno= 0000030201
65 and z.src_id= 000
66 and z.budget_type= 2
67 and z.budgetno= 2040201
68 and z.zf_itemno= 50299
69 ) ;
70 comment on table view_k3_zfzxz_zfpz_sum is '该视图主要是用于 存储过程 pro_k3_zfzxz 的 rowtype里的 ；作为一个rowtype 类型使用而已 ';
71
72
73
74 drop table gk_zwentry_k3_zfzxz_zfpz_temp ;
75 ---创建 事务级别的临时表 。用来存放进行同单位同资金来源同项目同功能分类科目同经济分类的合并数据
76 create global temporary table GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP
77 (
78 gk_zfpz_id VARCHAR2(4000),
79 id VARCHAR2(4000),
80 origin_id VARCHAR2(4000),
81 voucher_no VARCHAR2(4000),
82 make_date DATE,
83 fk_account VARCHAR2(60),
84 fk_bank VARCHAR2(60),
85 src_id VARCHAR2(10),
86 sk_name VARCHAR2(100),
87 sk_account VARCHAR2(60),
88 sk_bank VARCHAR2(60),
89 unitno VARCHAR2(30),
90 unit_name VARCHAR2(300),
91 budget_type VARCHAR2(2),
92 bdgt_accid VARCHAR2(20),
93 budgetno VARCHAR2(30),
94 budget_name VARCHAR2(300),
95 prjno VARCHAR2(30),
96 prj_name VARCHAR2(300),
97 zfpz_amt NUMBER(20,2),
98 amt NUMBER(20,2),
99 purpose VARCHAR2(4000),
100 remark VARCHAR2(4000),
101 checker NUMBER(20),
102 checker_date DATE,
103 affirmer NUMBER(20),
104 affirm_date DATE,
105 voucher_type VARCHAR2(2),
106 check_status VARCHAR2(2),
107 wf_status VARCHAR2(2),
108 gk_hzqsd_id NUMBER(20),
109 fund VARCHAR2(500),
110 org_type VARCHAR2(2),
111 back_oper_id NUMBER(20),
112 back_oper_idea VARCHAR2(100),
113 back_oper_date DATE,
114 back_checker_id NUMBER(20),
115 back_checker_idea VARCHAR2(100),
116 back_checker_date DATE,
117 zf_item_amt NUMBER,
118 zf_itemno VARCHAR2(30),
119 zf_itemname VARCHAR2(4000),
120 bm_itemnos VARCHAR2(4000)
121 )
122 on commit preserve rows;
123 -- Add comments to the table
124 comment on table GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP
125 is '事务级别的临时表 。用来存放对视图view_k3_zfzxz_zfpz进行同单位同资金来源同项目同功能分类科目同经济分类的合并数据 ';
126 -- Add comments to the columns
127 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.gk_zfpz_id
128 is 'gk_zfpz 表id ';
129 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.id
130 is 'gk_zfpz 表id ';
131 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.origin_id
132 is '凭证来源id ';
133 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.voucher_no
134 is '支付凭证号 ';
135 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.make_date
136 is '支付凭证日期 ';
137 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.fk_account
138 is ' 付款人账号 ';
139 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.fk_bank
140 is ' 付款人开户银行 ';
141 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.src_id
142 is '资金来源代码 ';
143 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.sk_name
144 is '收款人全称 ';
145 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.sk_account
146 is '收款人账号 ';
147 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.sk_bank
148 is ' 收款人开户银行 ';
149 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.unitno
150 is '单位编码 ';
151 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.unit_name
152 is '单位名称 ';
153 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.budget_type
154 is '科目类型代码 ';
155 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.bdgt_accid
156 is ' ';
157 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.budgetno
158 is '功能分类科目编码 ';
159 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.budget_name
160 is '功能分类科目名称 ';
161 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.prjno
162 is '项目编码 ';
163 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.prj_name
164 is '项目名称 ';
165 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.zfpz_amt
166 is '金额(支付凭证表) ';
167 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.amt
168 is ' 金额(经济分类表) ';
169 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.purpose
170 is '用途 ';
171 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.remark
172 is ' 备注 ';
173 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.checker
174 is '审核人 ';
175 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.checker_date
176 is ' 审核日期 ';
177 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.affirmer
178 is '对碰人 ';
179 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.affirm_date
180 is '对碰日期 ';
181 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.voucher_type
182 is '支付凭证类型(参考gk_zfpz表支付凭证类型 ) ';
183 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.check_status
184 is '对碰状态（参考gk_zfpz表对碰状态） ';
185 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.wf_status
186 is ' 审结状态(0草稿,1未审结,9已审结) ';
187 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.gk_hzqsd_id
188 is ' 汇总清算单号 ';
189 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.fund
190 is ' ';
191 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.org_type
192 is ' ';
193 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.back_oper_id
194 is ' ';
195 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.back_oper_idea
196 is ' ';
197 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.back_oper_date
198 is ' ';
199 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.back_checker_id
200 is ' ';
201 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.back_checker_idea
202 is ' ';
203 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.back_checker_date
204 is ' ';
205 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.zf_item_amt
206 is '*经济分类支付金额 ';
207 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.zf_itemno
208 is '*经济分类编码 ';
209 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.zf_itemname
210 is '*经济分类名称 ';
211 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.bm_itemnos
212 is '部门经济分类编码 ';
213
214
215
216
217
218
219 ---创建游标结果集合
220 CREATE OR REPLACE PACKAGE PKG_QUERY IS
221
222 -- Author : ADMINISTRATOR
223 -- Created : 2016/12/8 星期四 10:28:37
224 -- Purpose : 用做查询游标
225
226 -- Public type declarations
227 TYPE CUR_QUERY IS REF CURSOR;
228
229 END PKG_QUERY;
230
231
232
233
234 ---创建 获取数据的存储过程
235 create or replace procedure PRO_K3_ZFZXZ(
236 ZFPZ_IDS_PCH in number ,
237 v_cur out pkg_query.cur_query )
238 is
239 type ref_cursor_type is ref cursor;
240 cursor_ids ref_cursor_type; /*游标*/ /*声明：查询 GK_ZWVCH_ZFZXZ_ZFPZ_IDS 表 数据 的游标对象 */
241 v_row_zfzxz_zfpz_ids gk_zwvch_zfzxz_zfpz_ids%rowtype ; /*声明：GK_ZWVCH_ZFZXZ_ZFPZ_IDS 单条数据对象 */
242 v_sql VARCHAR2(4000);
243 v_ids_sql varchar2(4000);
244 v_ids varchar2(4000) :='';
245 v_index number :=0 ;/*循环数据的循环次数*/
246
247 /* 获取分组数据信息 */
248 cursor_zfzxz_zfpz ref_cursor_type; /*游标*/
249 v_row_zfzxz_zfpz view_k3_zfzxz_zfpz_group%rowtype ;
250 v_zfzxz_zfpz_sql varchar2(4000) :='';
251
252 /** 分组求和 **/
253 cursor_gb_zfzxz ref_cursor_type; /*游标*/
254 v_gb_zfzxz_sql varchar2(4000) :='';
255 v_row_gb_zfzxz view_k3_zfzxz_zfpz_sum%rowtype ;
256
257 v_sum_amt number(16,2) :=0;
258 v_sum_zf_item_amt number(16,2) :=0;
259 v_sum_zfpz_amt number(16,2) :=0;
260 v_list_gk_zfpz_id varchar2(4000) :='';
261 v_list_id varchar2(4000) :='';
262 v_list_origin_id varchar2(4000) :='';
263 v_list_voucher_no varchar2(4000) :='';
264 v_list_purpose varchar2(4000) :='';
265 v_list_remark varchar2(4000) :='';
266 v_list_bm_itemnos varchar2(4000) :='';
267
268 /*查询 view_k3_zfzxz_zfpz 的具体信息 */
269 cursor_q_vk3_zfzxzzfpz ref_cursor_type; /*游标*/
270 v_q_vk3_zfzxzzfpz_sql varchar2(4000) :='' ;
271 v_row_q_vk3_zfzxzzfpz view_k3_zfzxz_zfpz%rowtype ;
272
273
274 /*查询 view_k3_zfzxz_zfpz 的具体有多少条数据信息*/
275 cursor_q_vk3_zfzxzzfpz_count ref_cursor_type; /*游标*/
276 v_row_q_vk3_zfzxzzfpz_count view_k3_zfzxz_zfpz_count%rowtype ;
277 v_q_vk3_zfzxzzfpz_count_sql varchar2(4000) :='' ;
278 v_rowcount number :=0;/*获取游标取数的条数*/
279
280
281
282 begin
283 v_ids_sql:= 'select ids.id as id ,ids.pcno as pcno ,ids.zfpz_ids as zfpz_ids
284 from gk_zwvch_zfzxz_zfpz_ids ids where ids.pcno='||ZFPZ_IDS_PCH ;
285
286 open cursor_ids for v_ids_sql ;
287 /** 判断 是否有id 值查询出来 */
288 fetch cursor_ids into v_row_zfzxz_zfpz_ids;
289 if cursor_ids%found then
290 v_ids :=v_ids|| ' AND ( ' ;
291 while cursor_ids%found loop
292 /*循环查询结果拼接sql 判断条件*/
293 if v_index=0 then
294 v_ids := v_ids||' z.id in ('||v_row_zfzxz_zfpz_ids.zfpz_ids||')';
295 v_index := 1;
296 else
297 v_ids := v_ids|| ' OR z.id in ('||v_row_zfzxz_zfpz_ids.zfpz_ids ||')';
298 end if;
299 fetch cursor_ids into v_row_zfzxz_zfpz_ids;/*先让指针指向结果集中的第一行，并将值保存到emp_row 中*/
300 exit when cursor_ids%notfound or cursor_ids%notfound is null;
301
302 end loop;
303 v_ids :=v_ids|| ' )' ;
304 v_index := 0;
305 end if;
306
307
308 /*获取视图 view_k3_zfzxz_zfpz 的 同单位 同资金来源 同项目 同功能分类科目类型 同功能分类科目编码 同*经济分类编码 的数据 */
309 v_zfzxz_zfpz_sql :=' select z.unitno,z.prjno,z.src_id,z.budget_type,z.budgetno,z.zf_itemno from view_k3_zfzxz_zfpz z where 1=1 ';
310
311 if length(v_ids) > 1 then
312 v_zfzxz_zfpz_sql :=v_zfzxz_zfpz_sql ||v_ids;
313
314
315 v_zfzxz_zfpz_sql :=v_zfzxz_zfpz_sql||' group by z.unitno,z.prjno,z.src_id,z.budget_type,z.budgetno,z.bdgt_accid,z.prjno,z.zf_itemno order by z.unitno,z.prjno,z.src_id,z.budget_type,z.budgetno,z.bdgt_accid,z.prjno,z.zf_itemno ';
316
317 open cursor_zfzxz_zfpz for v_zfzxz_zfpz_sql ;
318 fetch cursor_zfzxz_zfpz into v_row_zfzxz_zfpz;
319 while cursor_zfzxz_zfpz%found loop
320
321 /*赋值*/
322 v_sum_amt :=0;
323 v_sum_zf_item_amt :=0;
324 v_sum_zfpz_amt :=0;
325 v_list_gk_zfpz_id :='';
326 v_list_id :='';
327 v_list_origin_id :='';
328 v_list_voucher_no :='';
329 v_list_purpose :='';
330 v_list_remark :='';
331 v_list_bm_itemnos :='';
332
333 /*取出合并的金额、凭证id列表、 */
334 v_gb_zfzxz_sql :=' select sum(z.amt) as sum_amt ,sum(z.zf_item_amt) as sum_zf_item_amt , sum(z.zfpz_amt) as sum_zfpz_amt ,
335 listagg(z.gk_zfpz_id,'','') within group(order by z.unitno,z.prjno,z.src_id,z.budget_type,z.budgetno,z.zf_itemno ) as list_gk_zfpz_id ,
336 listagg(z.id, '','') within group(order by z.unitno,z.prjno,z.src_id,z.budget_type,z.budgetno,z.zf_itemno ) as list_id ,
337 listagg(z.origin_id, '','') within group(order by z.unitno,z.prjno,z.src_id,z.budget_type,z.budgetno,z.zf_itemno ) as list_origin_id ,
338 listagg(z.voucher_no, '','') within group(order by z.unitno,z.prjno,z.src_id,z.budget_type,z.budgetno,z.zf_itemno ) as list_voucher_no ,
339 listagg(z.purpose, '','') within group(order by z.unitno,z.prjno,z.src_id,z.budget_type,z.budgetno,z.zf_itemno ) as list_purpose ,
340 listagg(z.remark, '','') within group(order by z.unitno,z.prjno,z.src_id,z.budget_type,z.budgetno,z.zf_itemno ) as list_remark ,
341 listagg(z.bm_itemnos, '','') within group(order by z.unitno,z.prjno,z.src_id,z.budget_type,z.budgetno,z.zf_itemno ) as list_bm_itemnos
342 from view_k3_zfzxz_zfpz z where 1=1 ' ;
343
344
345 v_gb_zfzxz_sql :=v_gb_zfzxz_sql ||v_ids;
346
347 if v_row_zfzxz_zfpz.unitno is not null then
348 v_gb_zfzxz_sql :=v_gb_zfzxz_sql || ' and z.unitno='||v_row_zfzxz_zfpz.unitno ;
349 end if ;
350 if v_row_zfzxz_zfpz.prjno is not null then
351 v_gb_zfzxz_sql :=v_gb_zfzxz_sql || ' and z.prjno='||v_row_zfzxz_zfpz.prjno ;
352 end if ;
353 if v_row_zfzxz_zfpz.src_id is not null then
354 v_gb_zfzxz_sql :=v_gb_zfzxz_sql || ' and z.src_id='||v_row_zfzxz_zfpz.src_id ;
355 end if;
356 if v_row_zfzxz_zfpz.budget_type is not null then
357 v_gb_zfzxz_sql :=v_gb_zfzxz_sql || ' and z.budget_type='||v_row_zfzxz_zfpz.budget_type ;
358 end if;
359 if v_row_zfzxz_zfpz.budgetno is not null then
360 v_gb_zfzxz_sql :=v_gb_zfzxz_sql || ' and z.budgetno='||v_row_zfzxz_zfpz.budgetno ;
361 end if;
362 if v_row_zfzxz_zfpz.zf_itemno is not null then
363 v_gb_zfzxz_sql :=v_gb_zfzxz_sql || ' and z.zf_itemno='||v_row_zfzxz_zfpz.zf_itemno ;
364 end if;
365 /*取出合并的同单位 同资金来源 同项目编码 同功能分类科目类型 同功能分类科目编码 同*经济分类编码 的金额求和 值 */
366 open cursor_gb_zfzxz for v_gb_zfzxz_sql ;
367 fetch cursor_gb_zfzxz into v_row_gb_zfzxz;
368 while cursor_gb_zfzxz%found loop
369 /*赋值*/
370 if v_row_gb_zfzxz.sum_amt is not null then
371 v_sum_amt :=v_row_gb_zfzxz.sum_amt;
372 end if;
373 if v_row_gb_zfzxz.sum_zf_item_amt is not null then
374 v_sum_zf_item_amt :=v_row_gb_zfzxz.sum_zf_item_amt;
375 end if;
376 if v_row_gb_zfzxz.sum_zfpz_amt is not null then
377 v_sum_zfpz_amt :=v_row_gb_zfzxz.sum_zfpz_amt;
378 end if ;
379 if v_row_gb_zfzxz.list_gk_zfpz_id is not null then
380 v_list_gk_zfpz_id :=v_row_gb_zfzxz.list_gk_zfpz_id;
381 end if;
382 if v_row_gb_zfzxz.list_id is not null then
383 v_list_id :=v_row_gb_zfzxz.list_id;
384 end if ;
385 if v_row_gb_zfzxz.list_origin_id is not null then
386 v_list_origin_id :=v_row_gb_zfzxz.list_origin_id;
387 end if ;
388 if v_row_gb_zfzxz.list_purpose is not null then
389 v_list_voucher_no :=v_row_gb_zfzxz.list_purpose;
390 end if;
391 if v_row_gb_zfzxz.list_purpose is not null then
392 v_list_purpose :=v_row_gb_zfzxz.list_purpose;
393 end if;
394 if v_row_gb_zfzxz.list_remark is not null then
395 v_list_remark :=v_row_gb_zfzxz.list_remark;
396 end if;
397 if v_row_gb_zfzxz.list_bm_itemnos is not null then
398 v_list_bm_itemnos :=v_row_gb_zfzxz.list_bm_itemnos;
399 end if;
400
401 fetch cursor_gb_zfzxz into v_row_gb_zfzxz;/*先让指针指向结果集中的第一行，并将值保存到v_row_zfzxz_zfpz 中*/
402 exit when cursor_gb_zfzxz%notfound or cursor_gb_zfzxz%notfound is null;
403 end loop;
404
405 v_q_vk3_zfzxzzfpz_sql :='select gk_zfpz_id, id, origin_id, voucher_no, make_date, fk_account, fk_bank, src_id, sk_name, sk_account, sk_bank, unitno, unit_name,
406 budget_type, bdgt_accid, budgetno, budget_name, prjno, prj_name, zfpz_amt, amt, purpose, remark, checker, checker_date, affirmer,
407 affirm_date, voucher_type, check_status, wf_status, gk_hzqsd_id, fund, org_type, back_oper_id, back_oper_idea, back_oper_date,
408 back_checker_id, back_checker_idea, back_checker_date, zf_item_amt, zf_itemno, zf_itemname, bm_itemnos
409 from view_k3_zfzxz_zfpz z where 1=1 ';
410
411 v_q_vk3_zfzxzzfpz_sql :=v_q_vk3_zfzxzzfpz_sql ||v_ids;
412 if v_row_zfzxz_zfpz.unitno is not null then
413 v_q_vk3_zfzxzzfpz_sql :=v_q_vk3_zfzxzzfpz_sql || ' and z.unitno='||v_row_zfzxz_zfpz.unitno ;
414 end if ;
415 if v_row_zfzxz_zfpz.prjno is not null then
416 v_q_vk3_zfzxzzfpz_sql :=v_q_vk3_zfzxzzfpz_sql || ' and z.prjno='||v_row_zfzxz_zfpz.prjno ;
417 end if ;
418 if v_row_zfzxz_zfpz.src_id is not null then
419 v_q_vk3_zfzxzzfpz_sql :=v_q_vk3_zfzxzzfpz_sql || ' and z.src_id='||v_row_zfzxz_zfpz.src_id ;
420 end if;
421 if v_row_zfzxz_zfpz.budget_type is not null then
422 v_q_vk3_zfzxzzfpz_sql :=v_q_vk3_zfzxzzfpz_sql || ' and z.budget_type='||v_row_zfzxz_zfpz.budget_type ;
423 end if;
424 if v_row_zfzxz_zfpz.budgetno is not null then
425 v_q_vk3_zfzxzzfpz_sql :=v_q_vk3_zfzxzzfpz_sql || ' and z.budgetno='||v_row_zfzxz_zfpz.budgetno ;
426 end if;
427 if v_row_zfzxz_zfpz.zf_itemno is not null then
428 v_q_vk3_zfzxzzfpz_sql :=v_q_vk3_zfzxzzfpz_sql || ' and z.zf_itemno='||v_row_zfzxz_zfpz.zf_itemno ;
429 end if;
430
431
432 v_q_vk3_zfzxzzfpz_count_sql:= 'select count(*) count_no from view_k3_zfzxz_zfpz z where 1=1 ' || v_ids
433 || ' and z.unitno='||v_row_zfzxz_zfpz.unitno || ' and z.prjno='||v_row_zfzxz_zfpz.prjno
434 || ' and z.src_id='||v_row_zfzxz_zfpz.src_id || ' and z.budget_type='||v_row_zfzxz_zfpz.budget_type
435 || ' and z.budgetno='||v_row_zfzxz_zfpz.budgetno || ' and z.zf_itemno='||v_row_zfzxz_zfpz.zf_itemno ;
436 v_rowcount:=0;
437 open cursor_q_vk3_zfzxzzfpz_count for v_q_vk3_zfzxzzfpz_count_sql ;
438 fetch cursor_q_vk3_zfzxzzfpz_count into v_row_q_vk3_zfzxzzfpz_count;
439
440 while cursor_q_vk3_zfzxzzfpz_count%found loop
441 v_rowcount:=v_row_q_vk3_zfzxzzfpz_count.count_no;
442
443 fetch cursor_q_vk3_zfzxzzfpz_count into v_row_q_vk3_zfzxzzfpz_count;/*先让指针指向结果集中的第一行，并将值保存到v_row_zfzxz_zfpz 中*/
444 exit when cursor_q_vk3_zfzxzzfpz_count%notfound or cursor_q_vk3_zfzxzzfpz_count%notfound is null;
445 end loop;
446
447
448
449
450
451 /*取出 view_k3_zfzxz_zfpz 视图中的数据 */
452 open cursor_q_vk3_zfzxzzfpz for v_q_vk3_zfzxzzfpz_sql ;
453 fetch cursor_q_vk3_zfzxzzfpz into v_row_q_vk3_zfzxzzfpz;
454 while cursor_q_vk3_zfzxzzfpz%found loop
455 /*判断游标查询结果为几行数据，如果是1行数据，就直接插入临时表中，如果是大于1行的数据集，则取出第一条数据插入 临时表中 */
456 if v_rowcount = 1 then
457
458 insert into gk_zwentry_k3_zfzxz_zfpz_temp
459 (gk_zfpz_id, id, origin_id, voucher_no, make_date, fk_account, fk_bank, src_id, sk_name, sk_account, sk_bank, unitno, unit_name, budget_type, bdgt_accid, budgetno, budget_name, prjno, prj_name, zfpz_amt, amt, purpose, remark, checker, checker_date, affirmer, affirm_date, voucher_type, check_status, wf_status, gk_hzqsd_id, fund, org_type, back_oper_id, back_oper_idea, back_oper_date, back_checker_id, back_checker_idea, back_checker_date, zf_item_amt, zf_itemno, zf_itemname, bm_itemnos)
460 values
461 (v_row_q_vk3_zfzxzzfpz.gk_zfpz_id, v_row_q_vk3_zfzxzzfpz.id, v_row_q_vk3_zfzxzzfpz.origin_id, v_row_q_vk3_zfzxzzfpz.voucher_no, v_row_q_vk3_zfzxzzfpz.make_date, v_row_q_vk3_zfzxzzfpz.fk_account, v_row_q_vk3_zfzxzzfpz.fk_bank, v_row_q_vk3_zfzxzzfpz.src_id, v_row_q_vk3_zfzxzzfpz.sk_name, v_row_q_vk3_zfzxzzfpz.sk_account, v_row_q_vk3_zfzxzzfpz.sk_bank, v_row_q_vk3_zfzxzzfpz.unitno, v_row_q_vk3_zfzxzzfpz.unit_name, v_row_q_vk3_zfzxzzfpz.budget_type, v_row_q_vk3_zfzxzzfpz.bdgt_accid, v_row_q_vk3_zfzxzzfpz.budgetno, v_row_q_vk3_zfzxzzfpz.budget_name, v_row_q_vk3_zfzxzzfpz.prjno, v_row_q_vk3_zfzxzzfpz.prj_name, v_row_q_vk3_zfzxzzfpz.zfpz_amt, v_row_q_vk3_zfzxzzfpz.amt, v_row_q_vk3_zfzxzzfpz.purpose, v_row_q_vk3_zfzxzzfpz.remark, v_row_q_vk3_zfzxzzfpz.checker, v_row_q_vk3_zfzxzzfpz.checker_date, v_row_q_vk3_zfzxzzfpz.affirmer, v_row_q_vk3_zfzxzzfpz.affirm_date, v_row_q_vk3_zfzxzzfpz.voucher_type, v_row_q_vk3_zfzxzzfpz.check_status, v_row_q_vk3_zfzxzzfpz.wf_status, v_row_q_vk3_zfzxzzfpz.gk_hzqsd_id, v_row_q_vk3_zfzxzzfpz.fund, v_row_q_vk3_zfzxzzfpz.org_type, v_row_q_vk3_zfzxzzfpz.back_oper_id, v_row_q_vk3_zfzxzzfpz.back_oper_idea, v_row_q_vk3_zfzxzzfpz.back_oper_date, v_row_q_vk3_zfzxzzfpz.back_checker_id, v_row_q_vk3_zfzxzzfpz.back_checker_idea, v_row_q_vk3_zfzxzzfpz.back_checker_date, v_row_q_vk3_zfzxzzfpz.zf_item_amt, v_row_q_vk3_zfzxzzfpz.zf_itemno, v_row_q_vk3_zfzxzzfpz.zf_itemname, v_row_q_vk3_zfzxzzfpz.bm_itemnos);
462
463
464 elsif v_rowcount > 1 then
465
466
467 insert into gk_zwentry_k3_zfzxz_zfpz_temp
468 (gk_zfpz_id, id, origin_id, voucher_no, make_date, fk_account, fk_bank, src_id, sk_name, sk_account, sk_bank, unitno, unit_name, budget_type, bdgt_accid, budgetno, budget_name, prjno, prj_name, zfpz_amt, amt, purpose, remark, checker, checker_date, affirmer, affirm_date, voucher_type, check_status, wf_status, gk_hzqsd_id, fund, org_type, back_oper_id, back_oper_idea, back_oper_date, back_checker_id, back_checker_idea, back_checker_date, zf_item_amt, zf_itemno, zf_itemname, bm_itemnos)
469 values
470 (v_list_gk_zfpz_id,v_list_id, v_list_origin_id, v_list_voucher_no, v_row_q_vk3_zfzxzzfpz.make_date, v_row_q_vk3_zfzxzzfpz.fk_account, v_row_q_vk3_zfzxzzfpz.fk_bank, v_row_q_vk3_zfzxzzfpz.src_id, v_row_q_vk3_zfzxzzfpz.sk_name, v_row_q_vk3_zfzxzzfpz.sk_account, v_row_q_vk3_zfzxzzfpz.sk_bank, v_row_q_vk3_zfzxzzfpz.unitno, v_row_q_vk3_zfzxzzfpz.unit_name, v_row_q_vk3_zfzxzzfpz.budget_type, v_row_q_vk3_zfzxzzfpz.bdgt_accid, v_row_q_vk3_zfzxzzfpz.budgetno, v_row_q_vk3_zfzxzzfpz.budget_name, v_row_q_vk3_zfzxzzfpz.prjno, v_row_q_vk3_zfzxzzfpz.prj_name,v_sum_zfpz_amt, v_sum_amt,v_list_purpose, v_list_remark , v_row_q_vk3_zfzxzzfpz.checker, v_row_q_vk3_zfzxzzfpz.checker_date, v_row_q_vk3_zfzxzzfpz.affirmer, v_row_q_vk3_zfzxzzfpz.affirm_date, v_row_q_vk3_zfzxzzfpz.voucher_type, v_row_q_vk3_zfzxzzfpz.check_status, v_row_q_vk3_zfzxzzfpz.wf_status, v_row_q_vk3_zfzxzzfpz.gk_hzqsd_id, v_row_q_vk3_zfzxzzfpz.fund, v_row_q_vk3_zfzxzzfpz.org_type, v_row_q_vk3_zfzxzzfpz.back_oper_id, v_row_q_vk3_zfzxzzfpz.back_oper_idea, v_row_q_vk3_zfzxzzfpz.back_oper_date, v_row_q_vk3_zfzxzzfpz.back_checker_id, v_row_q_vk3_zfzxzzfpz.back_checker_idea, v_row_q_vk3_zfzxzzfpz.back_checker_date, v_sum_zf_item_amt, v_row_q_vk3_zfzxzzfpz.zf_itemno, v_row_q_vk3_zfzxzzfpz.zf_itemname, v_list_bm_itemnos );
471
472 EXIT;/*跳出本循环体 ，继续执行上一级循环体 */
473 end if;
474
475 fetch cursor_q_vk3_zfzxzzfpz into v_row_q_vk3_zfzxzzfpz;/*先让指针指向结果集中的第一行，并将值保存到v_row_zfzxz_zfpz 中*/
476 exit when cursor_q_vk3_zfzxzzfpz%notfound or cursor_q_vk3_zfzxzzfpz%notfound is null;
477 end loop;
478
479
480 dbms_output.put_line('====进行PRO_K3_ZFZXZ 调试 GK_ZWVCH_ZFZXZ_ZFPZ_IDS 批次号 v_sql='|| v_sql||'==== ' );
481
482 fetch cursor_zfzxz_zfpz into v_row_zfzxz_zfpz;/*先让指针指向结果集中的第一行，并将值保存到v_row_zfzxz_zfpz 中*/
483 exit when cursor_zfzxz_zfpz%notfound or cursor_zfzxz_zfpz%notfound is null;
484 end loop;
485
486
487 end if; /*结束 gk_zfpz表id 的拼接 情况的判断 */
488
489
490 v_sql :=' select gk_zfpz_id, id, origin_id, voucher_no, make_date, fk_account, fk_bank, src_id, sk_name,
491 sk_account, sk_bank, unitno, unit_name, budget_type, bdgt_accid, budgetno, budget_name, prjno, prj_name,
492 zfpz_amt, amt, purpose, remark, checker, checker_date, affirmer, affirm_date, voucher_type, check_status,
493 wf_status, gk_hzqsd_id, fund, org_type, back_oper_id, back_oper_idea, back_oper_date, back_checker_id,
494 back_checker_idea, back_checker_date, zf_item_amt, zf_itemno, zf_itemname, bm_itemnos
495 from gk_zwentry_k3_zfzxz_zfpz_temp z ';
496
497 OPEN v_cur FOR v_sql;
498
499
500
501 close cursor_q_vk3_zfzxzzfpz;/*关闭游标*/
502 close cursor_gb_zfzxz;/*关闭游标*/
503 close cursor_zfzxz_zfpz;/*关闭游标*/
504 close cursor_ids;/*关闭游标*/
505 commit;
506
507 end PRO_K3_ZFZXZ;
508
509 /
510在对于拼接ID的方式 在存储过程中受限于 oracle字符串的 varcahr2(4000)的长度。于是也改版了一番。
1
2
3
4
5
6 ----创建 保存界面选择的zfpz表的 id
7 drop table GK_ZWVCH_ZFZXZ_ZFPZ_IDS ;
8 create table GK_ZWVCH_ZFZXZ_ZFPZ_IDS(
9 id number(20) not null primary key ,
10 pcno number(20) ,
11 zfpz_ids varchar(3000)
12 );
13
14 comment on table GK_ZWVCH_ZFZXZ_ZFPZ_IDS is '保存支付中心帐界面选择的所有支付凭证id ';
15 comment on column GK_ZWVCH_ZFZXZ_ZFPZ_IDS.ID is '主键';
16 comment on column GK_ZWVCH_ZFZXZ_ZFPZ_IDS.pcno is '批次号';
17 comment on column GK_ZWVCH_ZFZXZ_ZFPZ_IDS.zfpz_ids is 'gk_zfpz表的主键id:数据格式为：123，124，125';
18
19 ;
20
21
22 --创建索引
23 create index idx_pcno on GK_ZWVCH_ZFZXZ_ZFPZ_IDS (pcno);
24
25 insert into GK_ZWVCH_ZFZXZ_ZFPZ_IDS (ID, PCNO, ZFPZ_IDS) values (0, 0, '0,0');
26 commit;
27
28 ---创建序列号
29 insert into PWP_NO (NOID, PREFIX, INITIALVALUE, BUFFERSIZE, POSTFIX, NOINCREMENT, NONAME, NOLENGTH, NOTYPE)
30 values ('GK_ZWVCH_ZFZXZ_ZFPZ_IDS_SEQ', null, 1, 10, null, 1, 'GK_ZWVCH_ZFZXZ_ZFPZ_IDS_SEQ', 20, 1);
31 insert into PWP_NODTL (NOID, PREFIX, NODAY, NEXTID, POSTFIX, NOINCREMENT, NOYEAR, NOMONTH)
32 values ('GK_ZWVCH_ZFZXZ_ZFPZ_IDS_SEQ', null, 0, 2, null, 1, 2018, 4);
33
34 commit;
35
36
37 create or replace view view_k3_zfzxz_zfpz_group as
38 select z.unitno, z.prjno, z.src_id, z.budget_type, z.budgetno, z.zf_itemno
39 ----创建视图：进行分组。主要用于存储过程pro_k3_zfzxz 的 rowtype里的
40 from view_k3_zfzxz_zfpz z group by z.unitno, z.prjno,z.src_id, z.budget_type,z.budgetno,z.bdgt_accid, z.prjno, z.zf_itemno
41 order by z.unitno, z.prjno, z.src_id, z.budget_type, z.budgetno, z.bdgt_accid, z.prjno, z.zf_itemno ;
42 comment on table view_k3_zfzxz_zfpz_group is '该视图主要是用于 存储过程 pro_k3_zfzxz 的 rowtype里的 ；作为一个rowtype 类型使用而已 ';
43
44
45
46 create or replace view view_k3_zfzxz_zfpz_count as select count(a) as count_no from ( select '1' as a from dual );
47 comment on table view_k3_zfzxz_zfpz_count is '该视图主要是用于 存储过程 pro_k3_zfzxz 的 rowtype里的 ；作为一个rowtype 类型使用而已 ';
48
49 drop table gk_zwentry_k3_zfzxz_zfpz_sum;
50 -- Create table
51 create table gk_zwentry_k3_zfzxz_zfpz_sum
52 (
53 sum_amt NUMBER,
54 sum_zf_item_amt NUMBER,
55 sum_zfpz_amt NUMBER,
56 list_amt VARCHAR2(4000),
57 list_zf_item_amt VARCHAR2(4000),
58 list_zfpz_amt VARCHAR2(4000),
59 list_gk_zfpz_id LONG,
60 list_bm_itemnos VARCHAR2(4000)
61 )
62 ;
63 comment on table gk_zwentry_k3_zfzxz_zfpz_sum is '该视图主要是用于 存储过程 pro_k3_zfzxz 的 rowtype里的 ；作为一个rowtype 类型使用而已 ';
64
65
66
67 drop table gk_zwentry_k3_zfzxz_zfpz_temp ;
68 ---创建 事务级别的临时表 。用来存放进行同单位同资金来源同项目同功能分类科目同经济分类的合并数据
69
70 create global temporary table GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP
71 (
72 gk_zfpz_id LONG ,
73 make_date DATE,
74 fk_account VARCHAR2(60),
75 fk_bank VARCHAR2(60),
76 src_id VARCHAR2(10),
77 sk_name VARCHAR2(100),
78 sk_account VARCHAR2(60),
79 sk_bank VARCHAR2(60),
80 unitno VARCHAR2(30),
81 unit_name VARCHAR2(300),
82 budget_type VARCHAR2(2),
83 bdgt_accid VARCHAR2(20),
84 budgetno VARCHAR2(30),
85 budget_name VARCHAR2(300),
86 prjno VARCHAR2(30),
87 prj_name VARCHAR2(300),
88 zfpz_amt NUMBER(20,2),
89 amt NUMBER(20,2),
90 purpose VARCHAR2(4000) ,
91 remark VARCHAR2(4000) ,
92 checker NUMBER(20),
93 checker_date DATE,
94 affirmer NUMBER(20),
95 affirm_date DATE,
96 voucher_type VARCHAR2(2),
97 check_status VARCHAR2(2),
98 wf_status VARCHAR2(2),
99 gk_hzqsd_id NUMBER(20),
100 fund VARCHAR2(500),
101 org_type VARCHAR2(2),
102 back_oper_id NUMBER(20),
103 back_oper_idea VARCHAR2(100),
104 back_oper_date DATE,
105 back_checker_id NUMBER(20),
106 back_checker_idea VARCHAR2(100),
107 back_checker_date DATE,
108 zf_item_amt NUMBER,
109 zf_itemno VARCHAR2(30),
110 zf_itemname VARCHAR2(4000) ,
111 bm_itemnos VARCHAR2(4000) ,
112 list_amt VARCHAR2(4000) ,
113 list_zf_item_amt VARCHAR2(4000) ,
114 list_zfpz_amt VARCHAR2(4000)
115 )
116 on commit preserve rows;
117
118 -- Add comments to the table
119 comment on table GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP
120 is '事务级别的临时表 。用来存放对视图view_k3_zfzxz_zfpz进行同单位同资金来源同项目同功能分类科目同经济分类的合并数据 ';
121 -- Add comments to the columns
122 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.gk_zfpz_id
123 is 'gk_zfpz 表id ';
124
125
126 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.make_date
127 is '支付凭证日期 ';
128 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.fk_account
129 is ' 付款人账号 ';
130 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.fk_bank
131 is ' 付款人开户银行 ';
132 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.src_id
133 is '资金来源代码 ';
134 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.sk_name
135 is '收款人全称 ';
136 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.sk_account
137 is '收款人账号 ';
138 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.sk_bank
139 is ' 收款人开户银行 ';
140 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.unitno
141 is '单位编码 ';
142 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.unit_name
143 is '单位名称 ';
144 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.budget_type
145 is '科目类型代码 ';
146 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.bdgt_accid
147 is ' ';
148 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.budgetno
149 is '功能分类科目编码 ';
150 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.budget_name
151 is '功能分类科目名称 ';
152 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.prjno
153 is '项目编码 ';
154 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.prj_name
155 is '项目名称 ';
156 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.zfpz_amt
157 is '金额(支付凭证表) ';
158 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.amt
159 is ' 金额(经济分类表) ';
160 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.purpose
161 is '用途 ';
162 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.remark
163 is ' 备注 ';
164 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.checker
165 is '审核人 ';
166 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.checker_date
167 is ' 审核日期 ';
168 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.affirmer
169 is '对碰人 ';
170 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.affirm_date
171 is '对碰日期 ';
172 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.voucher_type
173 is '支付凭证类型(参考gk_zfpz表支付凭证类型 ) ';
174 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.check_status
175 is '对碰状态（参考gk_zfpz表对碰状态） ';
176 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.wf_status
177 is ' 审结状态(0草稿,1未审结,9已审结) ';
178 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.gk_hzqsd_id
179 is ' 汇总清算单号 ';
180 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.fund
181 is ' ';
182 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.org_type
183 is ' ';
184 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.back_oper_id
185 is ' ';
186 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.back_oper_idea
187 is ' ';
188 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.back_oper_date
189 is ' ';
190 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.back_checker_id
191 is ' ';
192 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.back_checker_idea
193 is ' ';
194 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.back_checker_date
195 is ' ';
196 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.zf_item_amt
197 is '*经济分类支付金额 ';
198 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.zf_itemno
199 is '*经济分类编码 ';
200 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.zf_itemname
201 is '*经济分类名称 ';
202 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.bm_itemnos
203 is '部门经济分类编码 ';
204
205
206
207
208
209
210 ---创建游标结果集合
211 CREATE OR REPLACE PACKAGE PKG_QUERY IS
212
213 -- Author : ADMINISTRATOR
214 -- Created : 2016/12/8 星期四 10:28:37
215 -- Purpose : 用做查询游标
216
217 -- Public type declarations
218 TYPE CUR_QUERY IS REF CURSOR;
219
220 END PKG_QUERY;
221
222
223
224
225 ---创建 获取数据的存储过程
226 create or replace procedure PRO_K3_ZFZXZ(
227 ZFPZ_IDS_PCH in number ,
228 v_cur out pkg_query.cur_query )
229 is
230 type ref_cursor_type is ref cursor;
231 cursor_ids ref_cursor_type; /*游标*/ /*声明：查询 GK_ZWVCH_ZFZXZ_ZFPZ_IDS 表 数据 的游标对象 */
232 v_row_zfzxz_zfpz_ids gk_zwvch_zfzxz_zfpz_ids%rowtype ; /*声明：GK_ZWVCH_ZFZXZ_ZFPZ_IDS 单条数据对象 */
233 v_sql VARCHAR2(4000);
234 v_ids_sql varchar2(4000);
235 v_ids varchar2(4000) :='';
236 v_index number :=0 ;/*循环数据的循环次数*/
237
238 /* 获取分组数据信息 */
239 cursor_zfzxz_zfpz ref_cursor_type; /*游标*/
240 v_row_zfzxz_zfpz view_k3_zfzxz_zfpz_group%rowtype ;
241 v_zfzxz_zfpz_sql varchar2(4000) :='';
242
243 /** 分组求和 **/
244 cursor_gb_zfzxz ref_cursor_type; /*游标*/
245 v_gb_zfzxz_sql varchar2(4000) :='';
246 v_row_gb_zfzxz view_k3_zfzxz_zfpz_sum%rowtype ;
247
248 v_sum_amt number(16,2) :=0;
249 v_sum_zf_item_amt number(16,2) :=0;
250 v_sum_zfpz_amt number(16,2) :=0;
251 v_list_gk_zfpz_id varchar2(4000) :='';
252 v_list_id varchar2(4000) :='';
253 v_list_origin_id varchar2(4000) :='';
254 v_list_voucher_no varchar2(4000) :='';
255 v_list_purpose varchar2(4000) :='';
256 v_list_remark varchar2(4000) :='';
257 v_list_bm_itemnos varchar2(4000) :='';
258 v_list_amt varchar2(4000) :='';
259 v_list_zf_item_amt varchar2(4000) :='';
260 v_list_zfpz_amt varchar2(4000) :='';
261
262 /*查询 view_k3_zfzxz_zfpz 的具体信息 */
263 cursor_q_vk3_zfzxzzfpz ref_cursor_type; /*游标*/
264 v_q_vk3_zfzxzzfpz_sql varchar2(4000) :='' ;
265 v_row_q_vk3_zfzxzzfpz view_k3_zfzxz_zfpz%rowtype ;
266
267
268 /*查询 view_k3_zfzxz_zfpz 的具体有多少条数据信息*/
269 cursor_q_vk3_zfzxzzfpz_count ref_cursor_type; /*游标*/
270 v_row_q_vk3_zfzxzzfpz_count view_k3_zfzxz_zfpz_count%rowtype ;
271 v_q_vk3_zfzxzzfpz_count_sql varchar2(4000) :='' ;
272 v_rowcount number :=0;/*获取游标取数的条数*/
273
274
275
276 begin
277 v_ids_sql:= 'select ids.id as id ,ids.pcno as pcno ,ids.zfpz_ids as zfpz_ids
278 from gk_zwvch_zfzxz_zfpz_ids ids where ids.pcno='||ZFPZ_IDS_PCH ;
279
280 open cursor_ids for v_ids_sql ;
281 /** 判断 是否有id 值查询出来 */
282 fetch cursor_ids into v_row_zfzxz_zfpz_ids;
283 if cursor_ids%found then
284 v_ids :=v_ids|| ' AND ( ' ;
285 while cursor_ids%found loop
286 /*循环查询结果拼接sql 判断条件*/
287 if v_index=0 then
288 v_ids := v_ids||' z.id in ('||v_row_zfzxz_zfpz_ids.zfpz_ids||')';
289 v_index := 1;
290 else
291 v_ids := v_ids|| ' OR z.id in ('||v_row_zfzxz_zfpz_ids.zfpz_ids ||')';
292 end if;
293 fetch cursor_ids into v_row_zfzxz_zfpz_ids;/*先让指针指向结果集中的第一行，并将值保存到emp_row 中*/
294 exit when cursor_ids%notfound or cursor_ids%notfound is null;
295
296 end loop;
297 v_ids :=v_ids|| ' )' ;
298 v_index := 0;
299 end if;
300
301
302 /*获取视图 view_k3_zfzxz_zfpz 的 同单位 同资金来源 同项目 同功能分类科目类型 同功能分类科目编码 同*经济分类编码 的数据 */
303 v_zfzxz_zfpz_sql :=' select z.unitno,z.prjno,z.src_id,z.budget_type,z.budgetno,z.zf_itemno from view_k3_zfzxz_zfpz z where 1=1 ';
304
305 if length(v_ids) > 1 then
306 v_zfzxz_zfpz_sql :=v_zfzxz_zfpz_sql ||v_ids;
307
308
309 v_zfzxz_zfpz_sql :=v_zfzxz_zfpz_sql||' group by z.unitno,z.prjno,z.src_id,z.budget_type,z.budgetno,z.bdgt_accid,z.prjno,z.zf_itemno order by z.unitno,z.prjno,z.src_id,z.budget_type,z.budgetno,z.bdgt_accid,z.prjno,z.zf_itemno ';
310
311 open cursor_zfzxz_zfpz for v_zfzxz_zfpz_sql ;
312 fetch cursor_zfzxz_zfpz into v_row_zfzxz_zfpz;
313 while cursor_zfzxz_zfpz%found loop
314
315 /*赋值*/
316 v_sum_amt :=0;
317 v_sum_zf_item_amt :=0;
318 v_sum_zfpz_amt :=0;
319 v_list_gk_zfpz_id :='';
320 v_list_id :='';
321 v_list_origin_id :='';
322 v_list_voucher_no :='';
323 v_list_purpose :='';
324 v_list_remark :='';
325 v_list_bm_itemnos :='';
326 v_list_amt :='';
327 v_list_zf_item_amt :='';
328 v_list_zfpz_amt :='';
329
330
331 /*取出合并的金额、凭证id列表、 */
332 v_gb_zfzxz_sql :=' select sum(z.amt) as sum_amt ,sum(z.zf_item_amt) as sum_zf_item_amt , sum(z.zfpz_amt) as sum_zfpz_amt ,
333 listagg(z.amt,'','') within group(order by z.unitno,z.prjno,z.src_id,z.budget_type,z.budgetno,z.zf_itemno ) as list_amt ,
334 listagg(z.zf_item_amt,'','') within group(order by z.unitno,z.prjno,z.src_id,z.budget_type,z.budgetno,z.zf_itemno ) as list_zf_item_amt ,
335 listagg(z.zfpz_amt,'','') within group(order by z.unitno,z.prjno,z.src_id,z.budget_type,z.budgetno,z.zf_itemno ) as list_zfpz_amt ,
336 listagg(z.gk_zfpz_id,'','') within group(order by z.unitno,z.prjno,z.src_id,z.budget_type,z.budgetno,z.zf_itemno ) as list_gk_zfpz_id ,
337 listagg(z.id, '','') within group(order by z.unitno,z.prjno,z.src_id,z.budget_type,z.budgetno,z.zf_itemno ) as list_id ,
338 listagg(z.origin_id, '','') within group(order by z.unitno,z.prjno,z.src_id,z.budget_type,z.budgetno,z.zf_itemno ) as list_origin_id ,
339 listagg(z.voucher_no, '','') within group(order by z.unitno,z.prjno,z.src_id,z.budget_type,z.budgetno,z.zf_itemno ) as list_voucher_no ,
340 listagg(z.purpose, '','') within group(order by z.unitno,z.prjno,z.src_id,z.budget_type,z.budgetno,z.zf_itemno ) as list_purpose ,
341 listagg(z.remark, '','') within group(order by z.unitno,z.prjno,z.src_id,z.budget_type,z.budgetno,z.zf_itemno ) as list_remark ,
342 listagg(z.bm_itemnos, '','') within group(order by z.unitno,z.prjno,z.src_id,z.budget_type,z.budgetno,z.zf_itemno ) as list_bm_itemnos
343 from view_k3_zfzxz_zfpz z where 1=1 ' ;
344
345
346 v_gb_zfzxz_sql :=v_gb_zfzxz_sql ||v_ids;
347
348 if v_row_zfzxz_zfpz.unitno is not null then
349 v_gb_zfzxz_sql :=v_gb_zfzxz_sql || ' and z.unitno='||v_row_zfzxz_zfpz.unitno ;
350 end if ;
351 if v_row_zfzxz_zfpz.prjno is not null then
352 v_gb_zfzxz_sql :=v_gb_zfzxz_sql || ' and z.prjno='||v_row_zfzxz_zfpz.prjno ;
353 end if ;
354 if v_row_zfzxz_zfpz.src_id is not null then
355 v_gb_zfzxz_sql :=v_gb_zfzxz_sql || ' and z.src_id='||v_row_zfzxz_zfpz.src_id ;
356 end if;
357 if v_row_zfzxz_zfpz.budget_type is not null then
358 v_gb_zfzxz_sql :=v_gb_zfzxz_sql || ' and z.budget_type='||v_row_zfzxz_zfpz.budget_type ;
359 end if;
360 if v_row_zfzxz_zfpz.budgetno is not null then
361 v_gb_zfzxz_sql :=v_gb_zfzxz_sql || ' and z.budgetno='||v_row_zfzxz_zfpz.budgetno ;
362 end if;
363 if v_row_zfzxz_zfpz.zf_itemno is not null then
364 v_gb_zfzxz_sql :=v_gb_zfzxz_sql || ' and z.zf_itemno='||v_row_zfzxz_zfpz.zf_itemno ;
365 end if;
366 /*取出合并的同单位 同资金来源 同项目编码 同功能分类科目类型 同功能分类科目编码 同*经济分类编码 的金额求和 值 */
367 open cursor_gb_zfzxz for v_gb_zfzxz_sql ;
368 fetch cursor_gb_zfzxz into v_row_gb_zfzxz;
369 while cursor_gb_zfzxz%found loop
370 /*赋值*/
371 if v_row_gb_zfzxz.sum_amt is not null then
372 v_sum_amt :=v_row_gb_zfzxz.sum_amt;
373 end if;
374 if v_row_gb_zfzxz.sum_zf_item_amt is not null then
375 v_sum_zf_item_amt :=v_row_gb_zfzxz.sum_zf_item_amt;
376 end if;
377 if v_row_gb_zfzxz.sum_zfpz_amt is not null then
378 v_sum_zfpz_amt :=v_row_gb_zfzxz.sum_zfpz_amt;
379 end if ;
380 if v_row_gb_zfzxz.list_gk_zfpz_id is not null then
381 v_list_gk_zfpz_id :=v_row_gb_zfzxz.list_gk_zfpz_id;
382 end if;
383 if v_row_gb_zfzxz.list_id is not null then
384 v_list_id :=v_row_gb_zfzxz.list_id;
385 end if ;
386 if v_row_gb_zfzxz.list_origin_id is not null then
387 v_list_origin_id :=v_row_gb_zfzxz.list_origin_id;
388 end if ;
389 if v_row_gb_zfzxz.list_purpose is not null then
390 v_list_voucher_no :=v_row_gb_zfzxz.list_purpose;
391 end if;
392 if v_row_gb_zfzxz.list_purpose is not null then
393 v_list_purpose :=v_row_gb_zfzxz.list_purpose;
394 end if;
395 if v_row_gb_zfzxz.list_remark is not null then
396 v_list_remark :=v_row_gb_zfzxz.list_remark;
397 end if;
398 if v_row_gb_zfzxz.list_bm_itemnos is not null then
399 v_list_bm_itemnos :=v_row_gb_zfzxz.list_bm_itemnos;
400 end if;
401
402 if v_row_gb_zfzxz.list_amt is not null then
403 v_list_amt :=v_row_gb_zfzxz.list_amt;
404 end if;
405 if v_row_gb_zfzxz.list_zf_item_amt is not null then
406 v_list_zf_item_amt :=v_row_gb_zfzxz.list_zf_item_amt;
407 end if;
408 if v_row_gb_zfzxz.list_zfpz_amt is not null then
409 v_list_zfpz_amt :=v_row_gb_zfzxz.list_zfpz_amt;
410 end if;
411
412 fetch cursor_gb_zfzxz into v_row_gb_zfzxz;/*先让指针指向结果集中的第一行，并将值保存到v_row_zfzxz_zfpz 中*/
413 exit when cursor_gb_zfzxz%notfound or cursor_gb_zfzxz%notfound is null;
414 end loop;
415
416 v_q_vk3_zfzxzzfpz_sql :='select gk_zfpz_id, id, origin_id, voucher_no, make_date, fk_account, fk_bank, src_id, sk_name, sk_account, sk_bank, unitno, unit_name,
417 budget_type, bdgt_accid, budgetno, budget_name, prjno, prj_name, zfpz_amt, amt, purpose, remark, checker, checker_date, affirmer,
418 affirm_date, voucher_type, check_status, wf_status, gk_hzqsd_id, fund, org_type, back_oper_id, back_oper_idea, back_oper_date,
419 back_checker_id, back_checker_idea, back_checker_date, zf_item_amt, zf_itemno, zf_itemname, bm_itemnos
420 from view_k3_zfzxz_zfpz z where 1=1 ';
421
422 v_q_vk3_zfzxzzfpz_sql :=v_q_vk3_zfzxzzfpz_sql ||v_ids;
423 if v_row_zfzxz_zfpz.unitno is not null then
424 v_q_vk3_zfzxzzfpz_sql :=v_q_vk3_zfzxzzfpz_sql || ' and z.unitno='||v_row_zfzxz_zfpz.unitno ;
425 end if ;
426 if v_row_zfzxz_zfpz.prjno is not null then
427 v_q_vk3_zfzxzzfpz_sql :=v_q_vk3_zfzxzzfpz_sql || ' and z.prjno='||v_row_zfzxz_zfpz.prjno ;
428 end if ;
429 if v_row_zfzxz_zfpz.src_id is not null then
430 v_q_vk3_zfzxzzfpz_sql :=v_q_vk3_zfzxzzfpz_sql || ' and z.src_id='||v_row_zfzxz_zfpz.src_id ;
431 end if;
432 if v_row_zfzxz_zfpz.budget_type is not null then
433 v_q_vk3_zfzxzzfpz_sql :=v_q_vk3_zfzxzzfpz_sql || ' and z.budget_type='||v_row_zfzxz_zfpz.budget_type ;
434 end if;
435 if v_row_zfzxz_zfpz.budgetno is not null then
436 v_q_vk3_zfzxzzfpz_sql :=v_q_vk3_zfzxzzfpz_sql || ' and z.budgetno='||v_row_zfzxz_zfpz.budgetno ;
437 end if;
438 if v_row_zfzxz_zfpz.zf_itemno is not null then
439 v_q_vk3_zfzxzzfpz_sql :=v_q_vk3_zfzxzzfpz_sql || ' and z.zf_itemno='||v_row_zfzxz_zfpz.zf_itemno ;
440 end if;
441
442
443 v_q_vk3_zfzxzzfpz_count_sql:= 'select count(*) count_no from view_k3_zfzxz_zfpz z where 1=1 ' || v_ids
444 || ' and z.unitno='||v_row_zfzxz_zfpz.unitno || ' and z.prjno='||v_row_zfzxz_zfpz.prjno
445 || ' and z.src_id='||v_row_zfzxz_zfpz.src_id || ' and z.budget_type='||v_row_zfzxz_zfpz.budget_type
446 || ' and z.budgetno='||v_row_zfzxz_zfpz.budgetno || ' and z.zf_itemno='||v_row_zfzxz_zfpz.zf_itemno ;
447 v_rowcount:=0;
448 open cursor_q_vk3_zfzxzzfpz_count for v_q_vk3_zfzxzzfpz_count_sql ;
449 fetch cursor_q_vk3_zfzxzzfpz_count into v_row_q_vk3_zfzxzzfpz_count;
450
451 while cursor_q_vk3_zfzxzzfpz_count%found loop
452 v_rowcount:=v_row_q_vk3_zfzxzzfpz_count.count_no;
453
454 fetch cursor_q_vk3_zfzxzzfpz_count into v_row_q_vk3_zfzxzzfpz_count;/*先让指针指向结果集中的第一行，并将值保存到v_row_zfzxz_zfpz 中*/
455 exit when cursor_q_vk3_zfzxzzfpz_count%notfound or cursor_q_vk3_zfzxzzfpz_count%notfound is null;
456 end loop;
457
458
459
460
461
462 /*取出 view_k3_zfzxz_zfpz 视图中的数据 */
463 open cursor_q_vk3_zfzxzzfpz for v_q_vk3_zfzxzzfpz_sql ;
464 fetch cursor_q_vk3_zfzxzzfpz into v_row_q_vk3_zfzxzzfpz;
465 while cursor_q_vk3_zfzxzzfpz%found loop
466 /*判断游标查询结果为几行数据，如果是1行数据，就直接插入临时表中，如果是大于1行的数据集，则取出第一条数据插入 临时表中 */
467 if v_rowcount = 1 then
468
469
470 insert into gk_zwentry_k3_zfzxz_zfpz_temp
471 ( list_amt,list_zf_item_amt,list_zfpz_amt , gk_zfpz_id, id, origin_id, voucher_no, make_date, fk_account, fk_bank, src_id, sk_name, sk_account, sk_bank, unitno, unit_name, budget_type, bdgt_accid, budgetno, budget_name, prjno, prj_name, zfpz_amt, amt, purpose, remark, checker, checker_date, affirmer, affirm_date, voucher_type, check_status, wf_status, gk_hzqsd_id, fund, org_type, back_oper_id, back_oper_idea, back_oper_date, back_checker_id, back_checker_idea, back_checker_date, zf_item_amt, zf_itemno, zf_itemname, bm_itemnos)
472 values
473 ( v_list_amt,v_list_zf_item_amt, v_list_zfpz_amt , v_row_q_vk3_zfzxzzfpz.gk_zfpz_id, v_row_q_vk3_zfzxzzfpz.id, v_row_q_vk3_zfzxzzfpz.origin_id, v_row_q_vk3_zfzxzzfpz.voucher_no, v_row_q_vk3_zfzxzzfpz.make_date, v_row_q_vk3_zfzxzzfpz.fk_account, v_row_q_vk3_zfzxzzfpz.fk_bank, v_row_q_vk3_zfzxzzfpz.src_id, v_row_q_vk3_zfzxzzfpz.sk_name, v_row_q_vk3_zfzxzzfpz.sk_account, v_row_q_vk3_zfzxzzfpz.sk_bank, v_row_q_vk3_zfzxzzfpz.unitno, v_row_q_vk3_zfzxzzfpz.unit_name, v_row_q_vk3_zfzxzzfpz.budget_type, v_row_q_vk3_zfzxzzfpz.bdgt_accid, v_row_q_vk3_zfzxzzfpz.budgetno, v_row_q_vk3_zfzxzzfpz.budget_name, v_row_q_vk3_zfzxzzfpz.prjno, v_row_q_vk3_zfzxzzfpz.prj_name, v_row_q_vk3_zfzxzzfpz.zfpz_amt, v_row_q_vk3_zfzxzzfpz.amt, v_row_q_vk3_zfzxzzfpz.purpose, v_row_q_vk3_zfzxzzfpz.remark, v_row_q_vk3_zfzxzzfpz.checker, v_row_q_vk3_zfzxzzfpz.checker_date, v_row_q_vk3_zfzxzzfpz.affirmer, v_row_q_vk3_zfzxzzfpz.affirm_date, v_row_q_vk3_zfzxzzfpz.voucher_type, v_row_q_vk3_zfzxzzfpz.check_status, v_row_q_vk3_zfzxzzfpz.wf_status, v_row_q_vk3_zfzxzzfpz.gk_hzqsd_id, v_row_q_vk3_zfzxzzfpz.fund, v_row_q_vk3_zfzxzzfpz.org_type, v_row_q_vk3_zfzxzzfpz.back_oper_id, v_row_q_vk3_zfzxzzfpz.back_oper_idea, v_row_q_vk3_zfzxzzfpz.back_oper_date, v_row_q_vk3_zfzxzzfpz.back_checker_id, v_row_q_vk3_zfzxzzfpz.back_checker_idea, v_row_q_vk3_zfzxzzfpz.back_checker_date, v_row_q_vk3_zfzxzzfpz.zf_item_amt, v_row_q_vk3_zfzxzzfpz.zf_itemno, v_row_q_vk3_zfzxzzfpz.zf_itemname, v_row_q_vk3_zfzxzzfpz.bm_itemnos);
474
475
476 elsif v_rowcount > 1 then
477
478
479 insert into gk_zwentry_k3_zfzxz_zfpz_temp
480 (list_amt,list_zf_item_amt,list_zfpz_amt ,gk_zfpz_id, id, origin_id, voucher_no, make_date, fk_account, fk_bank, src_id, sk_name, sk_account, sk_bank, unitno, unit_name, budget_type, bdgt_accid, budgetno, budget_name, prjno, prj_name, zfpz_amt, amt, purpose, remark, checker, checker_date, affirmer, affirm_date, voucher_type, check_status, wf_status, gk_hzqsd_id, fund, org_type, back_oper_id, back_oper_idea, back_oper_date, back_checker_id, back_checker_idea, back_checker_date, zf_item_amt, zf_itemno, zf_itemname, bm_itemnos)
481 values
482 (v_list_amt,v_list_zf_item_amt, v_list_zfpz_amt ,v_list_gk_zfpz_id,v_list_id, v_list_origin_id, v_list_voucher_no, v_row_q_vk3_zfzxzzfpz.make_date, v_row_q_vk3_zfzxzzfpz.fk_account, v_row_q_vk3_zfzxzzfpz.fk_bank, v_row_q_vk3_zfzxzzfpz.src_id, v_row_q_vk3_zfzxzzfpz.sk_name, v_row_q_vk3_zfzxzzfpz.sk_account, v_row_q_vk3_zfzxzzfpz.sk_bank, v_row_q_vk3_zfzxzzfpz.unitno, v_row_q_vk3_zfzxzzfpz.unit_name, v_row_q_vk3_zfzxzzfpz.budget_type, v_row_q_vk3_zfzxzzfpz.bdgt_accid, v_row_q_vk3_zfzxzzfpz.budgetno, v_row_q_vk3_zfzxzzfpz.budget_name, v_row_q_vk3_zfzxzzfpz.prjno, v_row_q_vk3_zfzxzzfpz.prj_name,v_sum_zfpz_amt, v_sum_amt,v_list_purpose, v_list_remark , v_row_q_vk3_zfzxzzfpz.checker, v_row_q_vk3_zfzxzzfpz.checker_date, v_row_q_vk3_zfzxzzfpz.affirmer, v_row_q_vk3_zfzxzzfpz.affirm_date, v_row_q_vk3_zfzxzzfpz.voucher_type, v_row_q_vk3_zfzxzzfpz.check_status, v_row_q_vk3_zfzxzzfpz.wf_status, v_row_q_vk3_zfzxzzfpz.gk_hzqsd_id, v_row_q_vk3_zfzxzzfpz.fund, v_row_q_vk3_zfzxzzfpz.org_type, v_row_q_vk3_zfzxzzfpz.back_oper_id, v_row_q_vk3_zfzxzzfpz.back_oper_idea, v_row_q_vk3_zfzxzzfpz.back_oper_date, v_row_q_vk3_zfzxzzfpz.back_checker_id, v_row_q_vk3_zfzxzzfpz.back_checker_idea, v_row_q_vk3_zfzxzzfpz.back_checker_date, v_sum_zf_item_amt, v_row_q_vk3_zfzxzzfpz.zf_itemno, v_row_q_vk3_zfzxzzfpz.zf_itemname, v_list_bm_itemnos );
483
484 EXIT;/*跳出本循环体 ，继续执行上一级循环体 */
485 end if;
486
487 fetch cursor_q_vk3_zfzxzzfpz into v_row_q_vk3_zfzxzzfpz;/*先让指针指向结果集中的第一行，并将值保存到v_row_zfzxz_zfpz 中*/
488 exit when cursor_q_vk3_zfzxzzfpz%notfound or cursor_q_vk3_zfzxzzfpz%notfound is null;
489 end loop;
490
491
492 dbms_output.put_line('====进行PRO_K3_ZFZXZ 调试 GK_ZWVCH_ZFZXZ_ZFPZ_IDS 批次号 v_sql='|| v_sql||'==== ' );
493
494 fetch cursor_zfzxz_zfpz into v_row_zfzxz_zfpz;/*先让指针指向结果集中的第一行，并将值保存到v_row_zfzxz_zfpz 中*/
495 exit when cursor_zfzxz_zfpz%notfound or cursor_zfzxz_zfpz%notfound is null;
496 end loop;
497
498
499 end if; /*结束 gk_zfpz表id 的拼接 情况的判断 */
500
501
502 v_sql :=' select list_amt,list_zf_item_amt,list_zfpz_amt , gk_zfpz_id, id, origin_id, voucher_no, make_date, fk_account, fk_bank, src_id, sk_name,
503 sk_account, sk_bank, unitno, unit_name, budget_type, bdgt_accid, budgetno, budget_name, prjno, prj_name,
504 zfpz_amt, amt, purpose, remark, checker, checker_date, affirmer, affirm_date, voucher_type, check_status,
505 wf_status, gk_hzqsd_id, fund, org_type, back_oper_id, back_oper_idea, back_oper_date, back_checker_id,
506 back_checker_idea, back_checker_date, zf_item_amt, zf_itemno, zf_itemname, bm_itemnos
507 from gk_zwentry_k3_zfzxz_zfpz_temp z ';
508
509 OPEN v_cur FOR v_sql;
510
511
512
513 close cursor_q_vk3_zfzxzzfpz;/*关闭游标*/
514 close cursor_gb_zfzxz;/*关闭游标*/
515 close cursor_zfzxz_zfpz;/*关闭游标*/
516 close cursor_ids;/*关闭游标*/
517 commit;
518
519 end PRO_K3_ZFZXZ;
520
521 /
5221 create or replace procedure PRO_K3_ZFZXZ_2(
2 /*ZFPZ_IDS_PCH in number ,*/
3 org_type in varchar2,
4 opertype in varchar2,
5 fdate in varchar2,
6 v_cur out pkg_query.cur_query )
7 is
8 type ref_cursor_type is ref cursor;
9 v_sql VARCHAR2(4000);
10 /* cursor_ids ref_cursor_type; \*游标*\ \*声明：查询 GK_ZWVCH_ZFZXZ_ZFPZ_IDS 表 数据 的游标对象 *\
11 v_row_zfzxz_zfpz_ids gk_zwvch_zfzxz_zfpz_ids%rowtype ; \*声明：GK_ZWVCH_ZFZXZ_ZFPZ_IDS 单条数据对象 *\
12
13 v_ids_sql varchar2(4000);
14 v_ids varchar2(4000) :='';*/
15 v_index number :=0 ;/*循环数据的循环次数*/
16
17
18 /* 获取分组数据信息 */
19 cursor_zfzxz_zfpz ref_cursor_type; /*游标*/
20 v_row_zfzxz_zfpz view_k3_zfzxz_zfpz_group%rowtype ;
21 v_zfzxz_zfpz_sql varchar2(4000) :='';
22
23 /** 分组求和 **/
24 cursor_gb_zfzxz ref_cursor_type; /*游标*/
25 v_gb_zfzxz_sql varchar2(4000) :='';
26 v_row_gb_zfzxz gk_zwentry_k3_zfzxz_zfpz_sum%rowtype ;
27
28 v_sum_amt number(16,2) :=0;
29 v_sum_zf_item_amt number(16,2) :=0;
30 v_sum_zfpz_amt number(16,2) :=0;
31 v_list_gk_zfpz_id varchar2(4000) :='';
32 v_list_id varchar2(4000) :='';
33 v_list_origin_id varchar2(4000) :='';
34 v_list_voucher_no varchar2(4000) :='';
35 v_list_purpose varchar2(4000) :='';
36 v_list_remark varchar2(4000) :='';
37 v_list_bm_itemnos varchar2(4000) :='';
38 v_list_amt varchar2(4000) :='';
39 v_list_zf_item_amt varchar2(4000) :='';
40 v_list_zfpz_amt varchar2(4000) :='';
41
42 /*查询 view_k3_zfzxz_zfpz 的具体信息 */
43 cursor_q_vk3_zfzxzzfpz ref_cursor_type; /*游标*/
44 v_q_vk3_zfzxzzfpz_sql LONG :='' ;
45 v_row_q_vk3_zfzxzzfpz view_k3_zfzxz_zfpz%rowtype ;
46
47
48 /*查询 view_k3_zfzxz_zfpz 的具体有多少条数据信息*/
49 cursor_q_vk3_zfzxzzfpz_count ref_cursor_type; /*游标*/
50 v_row_q_vk3_zfzxzzfpz_count view_k3_zfzxz_zfpz_count%rowtype ;
51 v_q_vk3_zfzxzzfpz_count_sql LONG :='' ;
52 v_rowcount number :=0;/*获取游标取数的条数*/
53
54 v_zfpzId_sql varchar2(1000) :='';
55
56 begin
57 DBMS_OUTPUT.ENABLE (buffer_size=>null) ;
58 /*
59 v_ids_sql:= 'select ids.id as id ,ids.pcno as pcno ,ids.zfpz_ids as zfpz_ids from gk_zwvch_zfzxz_zfpz_ids ids where ids.pcno='||ZFPZ_IDS_PCH ;
60
61 open cursor_ids for v_ids_sql ;
62 \** 判断 是否有id 值查询出来 *\
63 fetch cursor_ids into v_row_zfzxz_zfpz_ids;
64 if cursor_ids%found then
65 v_ids :=v_ids|| ' AND ( ' ;
66 while cursor_ids%found loop
67 \*循环查询结果拼接sql 判断条件*\
68 if v_index=0 then
69 v_ids := v_ids||' z.id in ('||v_row_zfzxz_zfpz_ids.zfpz_ids||')';
70 v_index := 1;
71 else
72 v_ids := v_ids|| ' OR z.id in ('||v_row_zfzxz_zfpz_ids.zfpz_ids ||')';
73 end if;
74 fetch cursor_ids into v_row_zfzxz_zfpz_ids;先让指针指向结果集中的第一行，并将值保存到emp_row 中
75 exit when cursor_ids%notfound or cursor_ids%notfound is null;
76
77 end loop;
78 v_ids :=v_ids|| ' )' ;
79 v_index := 0;
80 end if;
81 close cursor_ids;\*关闭游标*\
82
83 */
84 --先判断入参是否有值，如果没有值 整个存储过程返回空值
85 if org_type is not null and opertype is not null and fdate is not null then
86 /* org_type 资金来源类型 0 预算内 1预算外
87 opertype 操作类型 0' :'正常对碰的直接支付' 1 '正常对碰的授权支付' 2 :'退款的直接支付' 3 :'退款的授权支付' 4 :'已审结的调账申请'
88 fdate 凭证回单日期 */
89
90 if org_type= '1' /*预算外*/ then
91
92 if opertype = '0' then
93 --正常对碰的直接支付
94 v_zfpzId_sql:= 'and exists ( select gz.id from gk_zfpz gz,bs_zjly zjly where gz.voucher_type =''''0'''' and gz.check_status in ( ''1'',''2'',''02'' ) and gz.src_id=zjly.fundno
95
96 and zjly.fundno in(''043'', ''058'', ''158'', ''258'', ''013'', ''113'', ''213'', ''023'',''123'',''223'', ''046'',''246'',''049'',''149'',''249'',''048'', ''248'',''045'',''245'',''047'',''247'', ''055'',''355'',''455'',''155'',''255'', ''059'', ''159'', ''259'', ''061'', ''161'',''261'', ''026'', ''044'',''144'', ''244'' )
97 and not exists (select r.* from gk_zwvch_refer r where r.origin_teable =''GK_ZFPZ'' and r.orign_id = gz.id and r.fmodule = ''2'' and r.voucher_type=''0'' and r.orign_type=''1'' )
98 and to_char(gz.affirm_date,''yyyy-MM-dd'') = '''||fdate||''' ) ' ;
99
100 elsif opertype = '1' then
101 --正常对碰的授权支付
102 v_zfpzId_sql:= 'and exists ( select gz.id from gk_zfpz gz,bs_zjly zjly where gz.voucher_type =''1'' and gz.check_status in ( ''1'',''2'',''02'' ) and gz.src_id=zjly.fundno
103 and zjly.fundno in(''043'', ''058'', ''158'', ''258'', ''013'', ''113'', ''213'', ''023'',''123'',''223'', ''046'',''246'',''049'',''149'',''249'',''048'', ''248'',''045'',''245'',''047'',''247'', ''055'',''355'',''455'',''155'',''255'', ''059'', ''159'', ''259'', ''061'', ''161'',''261'', ''026'', ''044'',''144'', ''244'' )
104 and not exists (select r.* from gk_zwvch_refer r where r.origin_teable =''GK_ZFPZ'' and r.orign_id = gz.id and r.fmodule = ''2'' and r.voucher_type=''1'' and r.orign_type=''1'' )
105 and to_char(gz.affirm_date,''yyyy-MM-dd'') = '''||fdate||''' ) ' ;
106
107 elsif opertype = '2' then
108 --全额退款的直接支付
109 v_zfpzId_sql:= 'and exists ( select gz.id from gk_zfpz gz,bs_zjly zjly where gz.voucher_type =''''0'''' and gz.check_status in (''2'') and gz.src_id=zjly.fundno
110 and zjly.fundno in( ''043'',''058'', ''158'', ''258'', ''013'', ''113'', ''213'', ''023'',''123'',''223'', ''046'',''246'',''049'',''149'',''249'',''048'', ''248'',''045'',''245'',''047'',''247'', ''055'',''355'',''455'',''155'',''255'', ''059'', ''159'', ''259'', ''061'', ''161'',''261'', ''026'', ''044'',''144'', ''244'' )
111 and not exists (select r.* from gk_zwvch_refer r where r.origin_teable =''GK_ZFPZ'' and r.orign_id = gz.id and r.fmodule = ''2'' and r.voucher_type=''0'' and r.orign_type=''3'' )
112 and to_char(gz.affirm_date,''yyyy-MM-dd'') = '''||fdate||''' ) ' ;
113
114 elsif opertype = '3' then
115 -- 全额退款的授权支付
116 v_zfpzId_sql:= 'and exists ( select gz.id from gk_zfpz gz,bs_zjly zjly where gz.voucher_type =''1'' and gz.check_status =''2'' and gz.src_id=zjly.fundno
117 and zjly.fundno in( ''043'',''058'', ''158'', ''258'', ''013'', ''113'', ''213'', ''023'',''123'',''223'', ''046'',''246'',''049'',''149'',''249'',''048'', ''248'',''045'',''245'',''047'',''247'', ''055'',''355'',''455'',''155'',''255'', ''059'', ''159'', ''259'', ''061'', ''161'',''261'', ''026'', ''044'',''144'', ''244'' )
118 and not exists (select r.* from gk_zwvch_refer r where r.origin_teable =''GK_ZFPZ'' and r.orign_id = gz.id and r.fmodule = ''2'' and r.voucher_type=''1'' and r.orign_type=''3'' )
119 and to_char(gz.affirm_date,''yyyy-MM-dd'') = '''||fdate||''' ) ' ;
120
121 end if;
122
123 elsif org_type= '0' /*预算内 */ then
124 /* org_type 资金来源类型 0 预算内 1预算外
125 opertype 操作类型 0'' :''正常对碰的直接支付'' 1 ''正常对碰的授权支付'' 2 :''退款的直接支付'' 3 :''退款的授权支付'' 4 :''已审结的调账申请''
126 fdate 凭证回单日期 */
127 if opertype = 0 then
128 --正常对碰的直接支付
129 v_zfpzId_sql:= 'and exists (
130 select gz.id from gk_zfpz gz,bs_zjly zjly
131 where gz.voucher_type =''0'' and gz.check_status in ( ''1'',''2'',''02'' ) and gz.src_id=zjly.fundno and zjly.org_type=1 and zjly.fundno not in (''007'',''107'',''207'')
132 and not exists (select r.* from gk_zwvch_refer r where r.origin_teable =''GK_ZFPZ'' and r.orign_id = gz.id and r.fmodule = ''1'' and r.voucher_type=''0'' and r.orign_type=''1'' )
133 and to_char(gz.affirm_date,''yyyy-MM-dd'') = '''||fdate||''' ) ' ;
134
135 elsif opertype = '1' then
136 -- 正常对碰的授权支付
137 v_zfpzId_sql:= 'and exists (
138 select gz.id from gk_zfpz gz,bs_zjly zjly
139 where gz.voucher_type =''0'' and gz.check_status in ( ''1'',''2'',''02'' ) and gz.src_id=zjly.fundno and zjly.org_type=1 and zjly.fundno not in (''007'',''107'',''207'')
140 and not exists (select r.* from gk_zwvch_refer r where r.origin_teable =''GK_ZFPZ'' and r.orign_id = gz.id and r.fmodule = ''1'' and r.voucher_type=''0'' and r.orign_type=''1'' )
141 and to_char(gz.affirm_date,''yyyy-MM-dd'') = '''||fdate||''' ) ' ;
142
143 elsif opertype = '2' then
144 -- 全额退款的直接支付
145 v_zfpzId_sql:= 'and exists (
146 select gz.id from gk_zfpz gz,bs_zjly zjly where gz.voucher_type =''0'' and gz.check_status =''2'' and gz.src_id=zjly.fundno and zjly.org_type=1 and zjly.fundno not in (''007'',''107'',''207'')
147 and not exists (select r.* from gk_zwvch_refer r where r.origin_teable =''GK_ZFPZ'' and r.orign_id = gz.id and r.fmodule = ''1'' and r.voucher_type=''0'' and r.orign_type=''3'' )
148 and to_char(gz.affirm_date,''yyyy-MM-dd'') = '''||fdate||''' ) ' ;
149
150 elsif opertype = '3' then
151 ---全额退款的授权支付
152 v_zfpzId_sql:= 'and exists (
153 select gz.id from gk_zfpz gz,bs_zjly zjly where gz.voucher_type =''1'' and gz.check_status =''2'' and gz.src_id=zjly.fundno and zjly.org_type=1 and zjly.fundno not in (''007'',''107'',''207'')
154 and not exists (select r.* from gk_zwvch_refer r where r.origin_teable =''GK_ZFPZ'' and r.orign_id = gz.id and r.fmodule = ''1'' and r.voucher_type=''1'' and r.orign_type=''3'' )
155 and to_char(gz.affirm_date,''yyyy-MM-dd'') = '''||fdate||''' ) ' ;
156 end if;
157
158 end if;
159
160
161
162
163 /*获取视图 view_k3_zfzxz_zfpz 的 同单位 同资金来源 同项目 同功能分类科目类型 同功能分类科目编码 同*经济分类编码 的数据 */
164 v_zfzxz_zfpz_sql :=' select z.unitno,z.prjno,z.src_id,z.budget_type,z.budgetno,z.zf_itemno from view_k3_zfzxz_zfpz z where 1=1 ';
165
166 /* if length(v_ids) > 1 then
167 v_zfzxz_zfpz_sql :=v_zfzxz_zfpz_sql ||v_ids;
168 */
169
170 v_zfzxz_zfpz_sql :=v_zfzxz_zfpz_sql||v_zfpzId_sql ||' group by z.unitno,z.prjno,z.src_id,z.budget_type,z.budgetno,z.bdgt_accid,z.prjno,z.zf_itemno order by z.unitno,z.prjno,z.src_id,z.budget_type,z.budgetno,z.bdgt_accid,z.prjno,z.zf_itemno ';
171
172 open cursor_zfzxz_zfpz for v_zfzxz_zfpz_sql ;
173 fetch cursor_zfzxz_zfpz into v_row_zfzxz_zfpz;
174 while cursor_zfzxz_zfpz%found loop
175
176 /*赋值*/
177 v_sum_amt :=0;
178 v_sum_zf_item_amt :=0;
179 v_sum_zfpz_amt :=0;
180 v_list_gk_zfpz_id :='';
181 v_list_id :='';
182 v_list_origin_id :='';
183 v_list_voucher_no :='';
184 v_list_purpose :='';
185 v_list_remark :='';
186 v_list_bm_itemnos :='';
187 v_list_amt :='';
188 v_list_zf_item_amt :='';
189 v_list_zfpz_amt :='';
190
191
192 /*取出合并的金额、凭证id列表、 */
193 v_gb_zfzxz_sql :=' select sum(z.amt) as sum_amt ,sum(z.zf_item_amt) as sum_zf_item_amt , sum(z.zfpz_amt) as sum_zfpz_amt ,
194 listagg(z.amt,'','') within group(order by z.unitno,z.prjno,z.src_id,z.budget_type,z.budgetno,z.zf_itemno ) as list_amt ,
195 listagg(z.zf_item_amt,'','') within group(order by z.unitno,z.prjno,z.src_id,z.budget_type,z.budgetno,z.zf_itemno ) as list_zf_item_amt ,
196 listagg(z.zfpz_amt,'','') within group(order by z.unitno,z.prjno,z.src_id,z.budget_type,z.budgetno,z.zf_itemno ) as list_zfpz_amt ,
197 listagg(z.gk_zfpz_id,'','') within group(order by z.unitno,z.prjno,z.src_id,z.budget_type,z.budgetno,z.zf_itemno ) as list_gk_zfpz_id ,
198 listagg(z.bm_itemnos, '','') within group(order by z.unitno,z.prjno,z.src_id,z.budget_type,z.budgetno,z.zf_itemno ) as list_bm_itemnos
199 from view_k3_zfzxz_zfpz z where 1=1 ' ;
200
201
202 v_gb_zfzxz_sql :=v_gb_zfzxz_sql ||v_zfpzId_sql /*v_ids*/;
203
204 if v_row_zfzxz_zfpz.unitno is not null then
205 v_gb_zfzxz_sql :=v_gb_zfzxz_sql || ' and z.unitno='||v_row_zfzxz_zfpz.unitno ;
206 end if ;
207 if v_row_zfzxz_zfpz.prjno is not null then
208 v_gb_zfzxz_sql :=v_gb_zfzxz_sql || ' and z.prjno='||v_row_zfzxz_zfpz.prjno ;
209 end if ;
210 if v_row_zfzxz_zfpz.src_id is not null then
211 v_gb_zfzxz_sql :=v_gb_zfzxz_sql || ' and z.src_id='||v_row_zfzxz_zfpz.src_id ;
212 end if;
213 if v_row_zfzxz_zfpz.budget_type is not null then
214 v_gb_zfzxz_sql :=v_gb_zfzxz_sql || ' and z.budget_type='||v_row_zfzxz_zfpz.budget_type ;
215 end if;
216 if v_row_zfzxz_zfpz.budgetno is not null then
217 v_gb_zfzxz_sql :=v_gb_zfzxz_sql || ' and z.budgetno='||v_row_zfzxz_zfpz.budgetno ;
218 end if;
219 if v_row_zfzxz_zfpz.zf_itemno is not null then
220 v_gb_zfzxz_sql :=v_gb_zfzxz_sql || ' and z.zf_itemno='||v_row_zfzxz_zfpz.zf_itemno ;
221 end if;
222 /*取出合并的同单位 同资金来源 同项目编码 同功能分类科目类型 同功能分类科目编码 同*经济分类编码 的金额求和 值 */
223 dbms_output.put_line('====进行PRO_K3_ZFZXZ 调试 v_gb_zfzxz_sql v_gb_zfzxz_sql='|| v_gb_zfzxz_sql||'==== ' );
224 open cursor_gb_zfzxz for v_gb_zfzxz_sql ;
225 fetch cursor_gb_zfzxz into v_row_gb_zfzxz;
226 while cursor_gb_zfzxz%found loop
227 /*赋值*/
228 if v_row_gb_zfzxz.sum_amt is not null then
229 v_sum_amt :=v_row_gb_zfzxz.sum_amt;
230 end if;
231 if v_row_gb_zfzxz.sum_zf_item_amt is not null then
232 v_sum_zf_item_amt :=v_row_gb_zfzxz.sum_zf_item_amt;
233 end if;
234 if v_row_gb_zfzxz.sum_zfpz_amt is not null then
235 v_sum_zfpz_amt :=v_row_gb_zfzxz.sum_zfpz_amt;
236 end if ;
237 if v_row_gb_zfzxz.list_gk_zfpz_id is not null then
238 v_list_gk_zfpz_id :=v_row_gb_zfzxz.list_gk_zfpz_id;
239 end if;
240
241 if v_row_gb_zfzxz.list_bm_itemnos is not null then
242 v_list_bm_itemnos :=v_row_gb_zfzxz.list_bm_itemnos;
243 end if;
244
245 if v_row_gb_zfzxz.list_amt is not null then
246 v_list_amt :=v_row_gb_zfzxz.list_amt;
247 end if;
248 if v_row_gb_zfzxz.list_zf_item_amt is not null then
249 v_list_zf_item_amt :=v_row_gb_zfzxz.list_zf_item_amt;
250 end if;
251 if v_row_gb_zfzxz.list_zfpz_amt is not null then
252 v_list_zfpz_amt :=v_row_gb_zfzxz.list_zfpz_amt;
253 end if;
254
255 fetch cursor_gb_zfzxz into v_row_gb_zfzxz;/*先让指针指向结果集中的第一行，并将值保存到v_row_zfzxz_zfpz 中*/
256 exit when cursor_gb_zfzxz%notfound or cursor_gb_zfzxz%notfound is null;
257 end loop;
258 close cursor_gb_zfzxz;/*关闭游标*/
259
260 v_q_vk3_zfzxzzfpz_sql :='select gk_zfpz_id, id, origin_id, voucher_no, make_date, fk_account, fk_bank, src_id, sk_name, sk_account, sk_bank, unitno, unit_name,
261 budget_type, bdgt_accid, budgetno, budget_name, prjno, prj_name, zfpz_amt, amt, purpose, remark, checker, checker_date, affirmer,
262 affirm_date, voucher_type, check_status, wf_status, gk_hzqsd_id, fund, org_type, back_oper_id, back_oper_idea, back_oper_date,
263 back_checker_id, back_checker_idea, back_checker_date, zf_item_amt, zf_itemno, zf_itemname, bm_itemnos
264 from view_k3_zfzxz_zfpz z where 1=1 ';
265
266 v_q_vk3_zfzxzzfpz_sql :=v_q_vk3_zfzxzzfpz_sql ||v_zfpzId_sql /*v_ids*/;
267 if v_row_zfzxz_zfpz.unitno is not null then
268 v_q_vk3_zfzxzzfpz_sql :=v_q_vk3_zfzxzzfpz_sql || ' and z.unitno='||v_row_zfzxz_zfpz.unitno ;
269 end if ;
270 if v_row_zfzxz_zfpz.prjno is not null then
271 v_q_vk3_zfzxzzfpz_sql :=v_q_vk3_zfzxzzfpz_sql || ' and z.prjno='||v_row_zfzxz_zfpz.prjno ;
272 end if ;
273 if v_row_zfzxz_zfpz.src_id is not null then
274 v_q_vk3_zfzxzzfpz_sql :=v_q_vk3_zfzxzzfpz_sql || ' and z.src_id='||v_row_zfzxz_zfpz.src_id ;
275 end if;
276 if v_row_zfzxz_zfpz.budget_type is not null then
277 v_q_vk3_zfzxzzfpz_sql :=v_q_vk3_zfzxzzfpz_sql || ' and z.budget_type='||v_row_zfzxz_zfpz.budget_type ;
278 end if;
279 if v_row_zfzxz_zfpz.budgetno is not null then
280 v_q_vk3_zfzxzzfpz_sql :=v_q_vk3_zfzxzzfpz_sql || ' and z.budgetno='||v_row_zfzxz_zfpz.budgetno ;
281 end if;
282 if v_row_zfzxz_zfpz.zf_itemno is not null then
283 v_q_vk3_zfzxzzfpz_sql :=v_q_vk3_zfzxzzfpz_sql || ' and z.zf_itemno='||v_row_zfzxz_zfpz.zf_itemno ;
284 end if;
285
286
287 v_q_vk3_zfzxzzfpz_count_sql:= 'select count(*) count_no from view_k3_zfzxz_zfpz z where 1=1 ' || v_zfpzId_sql /*v_ids */;
288
289 if v_row_zfzxz_zfpz.unitno is not null then
290 v_q_vk3_zfzxzzfpz_count_sql :=v_q_vk3_zfzxzzfpz_count_sql || ' and z.unitno='||v_row_zfzxz_zfpz.unitno ;
291 end if ;
292 if v_row_zfzxz_zfpz.prjno is not null then
293 v_q_vk3_zfzxzzfpz_count_sql :=v_q_vk3_zfzxzzfpz_count_sql || ' and z.prjno='||v_row_zfzxz_zfpz.prjno ;
294 end if ;
295 if v_row_zfzxz_zfpz.src_id is not null then
296 v_q_vk3_zfzxzzfpz_count_sql :=v_q_vk3_zfzxzzfpz_count_sql || ' and z.src_id='||v_row_zfzxz_zfpz.src_id ;
297 end if;
298 if v_row_zfzxz_zfpz.budget_type is not null then
299 v_q_vk3_zfzxzzfpz_count_sql :=v_q_vk3_zfzxzzfpz_count_sql || ' and z.budget_type='||v_row_zfzxz_zfpz.budget_type ;
300 end if;
301 if v_row_zfzxz_zfpz.budgetno is not null then
302 v_q_vk3_zfzxzzfpz_count_sql :=v_q_vk3_zfzxzzfpz_count_sql || ' and z.budgetno='||v_row_zfzxz_zfpz.budgetno ;
303 end if;
304 if v_row_zfzxz_zfpz.zf_itemno is not null then
305 v_q_vk3_zfzxzzfpz_count_sql :=v_q_vk3_zfzxzzfpz_count_sql || ' and z.zf_itemno='||v_row_zfzxz_zfpz.zf_itemno ;
306 end if;
307
308 v_rowcount:=0;
309 dbms_output.put_line('====进行 v_q_vk3_zfzxzzfpz_count_sql='|| v_q_vk3_zfzxzzfpz_count_sql||'==== ' );
310 open cursor_q_vk3_zfzxzzfpz_count for v_q_vk3_zfzxzzfpz_count_sql ;
311
312 fetch cursor_q_vk3_zfzxzzfpz_count into v_row_q_vk3_zfzxzzfpz_count;
313
314 while cursor_q_vk3_zfzxzzfpz_count%found loop
315 v_rowcount:=v_row_q_vk3_zfzxzzfpz_count.count_no;
316
317 fetch cursor_q_vk3_zfzxzzfpz_count into v_row_q_vk3_zfzxzzfpz_count;/*先让指针指向结果集中的第一行，并将值保存到v_row_zfzxz_zfpz 中*/
318 exit when cursor_q_vk3_zfzxzzfpz_count%notfound or cursor_q_vk3_zfzxzzfpz_count%notfound is null;
319 end loop;
320
321
322
323
324
325 /*取出 view_k3_zfzxz_zfpz 视图中的数据 */
326 open cursor_q_vk3_zfzxzzfpz for v_q_vk3_zfzxzzfpz_sql ;
327 fetch cursor_q_vk3_zfzxzzfpz into v_row_q_vk3_zfzxzzfpz;
328 while cursor_q_vk3_zfzxzzfpz%found loop
329 /*判断游标查询结果为几行数据，如果是1行数据，就直接插入临时表中，如果是大于1行的数据集，则取出第一条数据插入 临时表中 */
330 if v_rowcount = 1 then
331
332
333 insert into gk_zwentry_k3_zfzxz_zfpz_temp
334 ( list_amt,list_zf_item_amt,list_zfpz_amt , gk_zfpz_id, make_date, fk_account, fk_bank, src_id, sk_name, sk_account, sk_bank, unitno, unit_name, budget_type, bdgt_accid, budgetno, budget_name, prjno, prj_name, zfpz_amt, amt, purpose, remark, checker, checker_date, affirmer, affirm_date, voucher_type, check_status, wf_status, gk_hzqsd_id, fund, org_type, back_oper_id, back_oper_idea, back_oper_date, back_checker_id, back_checker_idea, back_checker_date, zf_item_amt, zf_itemno, zf_itemname, bm_itemnos)
335 values
336 ( v_list_amt,v_list_zf_item_amt, v_list_zfpz_amt , v_row_q_vk3_zfzxzzfpz.gk_zfpz_id, v_row_q_vk3_zfzxzzfpz.make_date, v_row_q_vk3_zfzxzzfpz.fk_account, v_row_q_vk3_zfzxzzfpz.fk_bank, v_row_q_vk3_zfzxzzfpz.src_id, v_row_q_vk3_zfzxzzfpz.sk_name, v_row_q_vk3_zfzxzzfpz.sk_account, v_row_q_vk3_zfzxzzfpz.sk_bank, v_row_q_vk3_zfzxzzfpz.unitno, v_row_q_vk3_zfzxzzfpz.unit_name, v_row_q_vk3_zfzxzzfpz.budget_type, v_row_q_vk3_zfzxzzfpz.bdgt_accid, v_row_q_vk3_zfzxzzfpz.budgetno, v_row_q_vk3_zfzxzzfpz.budget_name, v_row_q_vk3_zfzxzzfpz.prjno, v_row_q_vk3_zfzxzzfpz.prj_name, v_row_q_vk3_zfzxzzfpz.zfpz_amt, v_row_q_vk3_zfzxzzfpz.amt, v_row_q_vk3_zfzxzzfpz.purpose, v_row_q_vk3_zfzxzzfpz.remark, v_row_q_vk3_zfzxzzfpz.checker, v_row_q_vk3_zfzxzzfpz.checker_date, v_row_q_vk3_zfzxzzfpz.affirmer, v_row_q_vk3_zfzxzzfpz.affirm_date, v_row_q_vk3_zfzxzzfpz.voucher_type, v_row_q_vk3_zfzxzzfpz.check_status, v_row_q_vk3_zfzxzzfpz.wf_status, v_row_q_vk3_zfzxzzfpz.gk_hzqsd_id, v_row_q_vk3_zfzxzzfpz.fund, v_row_q_vk3_zfzxzzfpz.org_type, v_row_q_vk3_zfzxzzfpz.back_oper_id, v_row_q_vk3_zfzxzzfpz.back_oper_idea, v_row_q_vk3_zfzxzzfpz.back_oper_date, v_row_q_vk3_zfzxzzfpz.back_checker_id, v_row_q_vk3_zfzxzzfpz.back_checker_idea, v_row_q_vk3_zfzxzzfpz.back_checker_date, v_row_q_vk3_zfzxzzfpz.zf_item_amt, v_row_q_vk3_zfzxzzfpz.zf_itemno, v_row_q_vk3_zfzxzzfpz.zf_itemname, v_row_q_vk3_zfzxzzfpz.bm_itemnos);
337
338
339 elsif v_rowcount > 1 then
340
341
342 insert into gk_zwentry_k3_zfzxz_zfpz_temp
343 (list_amt,list_zf_item_amt,list_zfpz_amt ,gk_zfpz_id, make_date, fk_account, fk_bank, src_id, sk_name, sk_account, sk_bank, unitno, unit_name, budget_type, bdgt_accid, budgetno, budget_name, prjno, prj_name, zfpz_amt, amt, purpose, remark, checker, checker_date, affirmer, affirm_date, voucher_type, check_status, wf_status, gk_hzqsd_id, fund, org_type, back_oper_id, back_oper_idea, back_oper_date, back_checker_id, back_checker_idea, back_checker_date, zf_item_amt, zf_itemno, zf_itemname, bm_itemnos)
344 values
345 (v_list_amt,v_list_zf_item_amt, v_list_zfpz_amt ,v_list_gk_zfpz_id,v_row_q_vk3_zfzxzzfpz.make_date, v_row_q_vk3_zfzxzzfpz.fk_account, v_row_q_vk3_zfzxzzfpz.fk_bank, v_row_q_vk3_zfzxzzfpz.src_id, v_row_q_vk3_zfzxzzfpz.sk_name, v_row_q_vk3_zfzxzzfpz.sk_account, v_row_q_vk3_zfzxzzfpz.sk_bank, v_row_q_vk3_zfzxzzfpz.unitno, v_row_q_vk3_zfzxzzfpz.unit_name, v_row_q_vk3_zfzxzzfpz.budget_type, v_row_q_vk3_zfzxzzfpz.bdgt_accid, v_row_q_vk3_zfzxzzfpz.budgetno, v_row_q_vk3_zfzxzzfpz.budget_name, v_row_q_vk3_zfzxzzfpz.prjno, v_row_q_vk3_zfzxzzfpz.prj_name,v_sum_zfpz_amt, v_sum_amt,v_row_q_vk3_zfzxzzfpz.purpose, v_row_q_vk3_zfzxzzfpz.remark, v_row_q_vk3_zfzxzzfpz.checker, v_row_q_vk3_zfzxzzfpz.checker_date, v_row_q_vk3_zfzxzzfpz.affirmer, v_row_q_vk3_zfzxzzfpz.affirm_date, v_row_q_vk3_zfzxzzfpz.voucher_type, v_row_q_vk3_zfzxzzfpz.check_status, v_row_q_vk3_zfzxzzfpz.wf_status, v_row_q_vk3_zfzxzzfpz.gk_hzqsd_id, v_row_q_vk3_zfzxzzfpz.fund, v_row_q_vk3_zfzxzzfpz.org_type, v_row_q_vk3_zfzxzzfpz.back_oper_id, v_row_q_vk3_zfzxzzfpz.back_oper_idea, v_row_q_vk3_zfzxzzfpz.back_oper_date, v_row_q_vk3_zfzxzzfpz.back_checker_id, v_row_q_vk3_zfzxzzfpz.back_checker_idea, v_row_q_vk3_zfzxzzfpz.back_checker_date, v_sum_zf_item_amt, v_row_q_vk3_zfzxzzfpz.zf_itemno, v_row_q_vk3_zfzxzzfpz.zf_itemname, v_list_bm_itemnos );
346
347 EXIT;/*跳出本循环体 ，继续执行上一级循环体 */
348 end if;
349
350 fetch cursor_q_vk3_zfzxzzfpz into v_row_q_vk3_zfzxzzfpz;/*先让指针指向结果集中的第一行，并将值保存到v_row_zfzxz_zfpz 中*/
351 exit when cursor_q_vk3_zfzxzzfpz%notfound or cursor_q_vk3_zfzxzzfpz%notfound is null;
352 end loop;
353
354 close cursor_q_vk3_zfzxzzfpz;/*关闭游标*/
355
356
357 fetch cursor_zfzxz_zfpz into v_row_zfzxz_zfpz;/*先让指针指向结果集中的第一行，并将值保存到v_row_zfzxz_zfpz 中*/
358 exit when cursor_zfzxz_zfpz%notfound or cursor_zfzxz_zfpz%notfound is null;
359 end loop;
360 close cursor_zfzxz_zfpz;/*关闭游标*/
361
362 /* end if; \*结束 gk_zfpz表id 的拼接 情况的判断 *\
363
364 */
365
366 end if;
367 v_sql :=' select list_amt,list_zf_item_amt,list_zfpz_amt , gk_zfpz_id, gk_zfpz_id as id, gk_zfpz_id as origin_id, gk_zfpz_id as voucher_no, make_date, fk_account, fk_bank, src_id, sk_name,
368 sk_account, sk_bank, unitno, unit_name, budget_type, bdgt_accid, budgetno, budget_name, prjno, prj_name,
369 zfpz_amt, amt, purpose, remark, checker, checker_date, affirmer, affirm_date, voucher_type, check_status,
370 wf_status, gk_hzqsd_id, fund, org_type, back_oper_id, back_oper_idea, back_oper_date, back_checker_id,
371 back_checker_idea, back_checker_date, zf_item_amt, zf_itemno, zf_itemname, bm_itemnos
372 from gk_zwentry_k3_zfzxz_zfpz_temp z ';
373
374 OPEN v_cur FOR v_sql;
375
376 commit;
377
378 end PRO_K3_ZFZXZ_2;
3791
2
3
4
5
6
7
8 ---创建序列号
9 insert into PWP_NO (NOID, PREFIX, INITIALVALUE, BUFFERSIZE, POSTFIX, NOINCREMENT, NONAME, NOLENGTH, NOTYPE)
10 values ('GK_ZWVCH_REFER_TEMP_SEQ', null, 1, 10, null, 1, 'GK_ZWVCH_REFER_TEMP_SEQ', 20, 1);
11 insert into PWP_NODTL (NOID, PREFIX, NODAY, NEXTID, POSTFIX, NOINCREMENT, NOYEAR, NOMONTH)
12 values ('GK_ZWVCH_REFER_TEMP_SEQ', null, 0, 2, null, 1, 2018, 4);
13
14 commit;
15
16
17 create or replace view view_k3_zfzxz_zfpz_group as
18 select z.unitno, z.prjno, z.src_id, z.budget_type, z.budgetno, z.zf_itemno
19 ----创建视图：进行分组。主要用于存储过程pro_k3_zfzxz 的 rowtype里的
20 from view_k3_zfzxz_zfpz z group by z.unitno, z.prjno,z.src_id, z.budget_type,z.budgetno,z.bdgt_accid, z.prjno, z.zf_itemno
21 order by z.unitno, z.prjno, z.src_id, z.budget_type, z.budgetno, z.bdgt_accid, z.prjno, z.zf_itemno ;
22 comment on table view_k3_zfzxz_zfpz_group is '该视图主要是用于 存储过程 pro_k3_zfzxz 的 rowtype里的 ；作为一个rowtype 类型使用而已 ';
23
24
25
26 create or replace view view_k3_zfzxz_zfpz_count as select count(a) as count_no from ( select '1' as a from dual );
27 comment on table view_k3_zfzxz_zfpz_count is '该视图主要是用于 存储过程 pro_k3_zfzxz 的 rowtype里的 ；作为一个rowtype 类型使用而已 ';
28
29
30 drop table gk_zwentry_k3_zfzxz_zfpz_sum;
31 -- Create table
32 create table gk_zwentry_k3_zfzxz_zfpz_sum
33 (
34 sum_amt NUMBER,
35 sum_zf_item_amt NUMBER,
36 sum_zfpz_amt NUMBER
37 );
38 comment on table gk_zwentry_k3_zfzxz_zfpz_sum is '该视图主要是用于 存储过程 pro_k3_zfzxz 的 rowtype里的 ；作为一个rowtype 类型使用而已 ';
39
40
41 drop table GK_ZWVCH_REFER_TEMP ;
42 -- Create table
43 create table GK_ZWVCH_REFER_TEMP
44 (
45
46 pch NUMBER(20) not null,
47 zfpz_id NUMBER(20) not null,
48 zfpz_amt NUMBER(16,2),
49 bm_itemno VARCHAR2(4000),
50 zf_itemno VARCHAR2(30),
51 zf_itemname VARCHAR2(3000),
52 itemno_amt NUMBER(16,2),
53 CONSTRAINT PK_GK_ZWVCH_REFER_TEMP PRIMARY KEY (pch,zfpz_id) -- 联合主键
54 ) ;
55 comment on table GK_ZWVCH_REFER_TEMP is '该表主要是用于 存储过程 pro_k3_zfzxz 的 rowtype里的用来存放中福在线账中支付凭证的数据信息 ';
56 comment on column GK_ZWVCH_REFER_TEMP.pch is '批次号 ';
57 comment on column GK_ZWVCH_REFER_TEMP.zfpz_id is '支付凭证表主键 ';
58 comment on column GK_ZWVCH_REFER_TEMP.zfpz_amt is '支付凭证支付金额 ';
59 comment on column GK_ZWVCH_REFER_TEMP.bm_itemno is '部门经济分类编码 ';
60 comment on column GK_ZWVCH_REFER_TEMP.zf_itemno is '支付经济分类编码 ';
61 comment on column GK_ZWVCH_REFER_TEMP.zf_itemname is '*经济分类名称 ';
62 comment on column GK_ZWVCH_REFER_TEMP.itemno_amt is '部分经济分类支付金额 ';
63
64
65
66
67 drop table gk_zwentry_k3_zfzxz_zfpz_temp ;
68 ---创建 事务级别的临时表 。用来存放进行同单位同资金来源同项目同功能分类科目同经济分类的合并数据
69
70 create global temporary table GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP
71 (
72
73 gk_zfpz_id NUMBER(20) ,
74 GK_ZWVCH_REFER_TEMP_PCH NUMBER(20) ,
75 make_date DATE,
76 fk_account VARCHAR2(60),
77 fk_bank VARCHAR2(60),
78 src_id VARCHAR2(10),
79 sk_name VARCHAR2(100),
80 sk_account VARCHAR2(60),
81 sk_bank VARCHAR2(60),
82 unitno VARCHAR2(30),
83 unit_name VARCHAR2(300),
84 budget_type VARCHAR2(2),
85 bdgt_accid VARCHAR2(20),
86 budgetno VARCHAR2(30),
87 budget_name VARCHAR2(300),
88 prjno VARCHAR2(30),
89 prj_name VARCHAR2(300),
90 zfpz_amt NUMBER(20,2),
91 amt NUMBER(20,2),
92 purpose VARCHAR2(4000) ,
93 remark VARCHAR2(4000) ,
94 checker NUMBER(20),
95 checker_date DATE,
96 affirmer NUMBER(20),
97 affirm_date DATE,
98 voucher_type VARCHAR2(2),
99 check_status VARCHAR2(2),
100 wf_status VARCHAR2(2),
101 gk_hzqsd_id NUMBER(20),
102 fund VARCHAR2(500),
103 org_type VARCHAR2(2),
104 back_oper_id NUMBER(20),
105 back_oper_idea VARCHAR2(100),
106 back_oper_date DATE,
107 back_checker_id NUMBER(20),
108 back_checker_idea VARCHAR2(100),
109 back_checker_date DATE,
110 zf_item_amt NUMBER,
111 zf_itemno VARCHAR2(30),
112 zf_itemname VARCHAR2(4000) ,
113 bm_itemnos VARCHAR2(4000)
114
115 )
116 on commit preserve rows;
117
118 -- Add comments to the table
119 comment on table GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP
120 is '事务级别的临时表 。用来存放对视图view_k3_zfzxz_zfpz进行同单位同资金来源同项目同功能分类科目同经济分类的合并数据 ';
121 -- Add comments to the columns
122 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.gk_zfpz_id
123 is 'gk_zfpz 表id ';
124
125
126 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.make_date
127 is '支付凭证日期 ';
128 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.fk_account
129 is ' 付款人账号 ';
130 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.fk_bank
131 is ' 付款人开户银行 ';
132 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.src_id
133 is '资金来源代码 ';
134 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.sk_name
135 is '收款人全称 ';
136 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.sk_account
137 is '收款人账号 ';
138 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.sk_bank
139 is ' 收款人开户银行 ';
140 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.unitno
141 is '单位编码 ';
142 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.unit_name
143 is '单位名称 ';
144 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.budget_type
145 is '科目类型代码 ';
146 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.bdgt_accid
147 is ' ';
148 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.budgetno
149 is '功能分类科目编码 ';
150 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.budget_name
151 is '功能分类科目名称 ';
152 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.prjno
153 is '项目编码 ';
154 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.prj_name
155 is '项目名称 ';
156 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.zfpz_amt
157 is '金额(支付凭证表) ';
158 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.amt
159 is ' 金额(经济分类表) ';
160 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.purpose
161 is '用途 ';
162 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.remark
163 is ' 备注 ';
164 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.checker
165 is '审核人 ';
166 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.checker_date
167 is ' 审核日期 ';
168 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.affirmer
169 is '对碰人 ';
170 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.affirm_date
171 is '对碰日期 ';
172 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.voucher_type
173 is '支付凭证类型(参考gk_zfpz表支付凭证类型 ) ';
174 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.check_status
175 is '对碰状态（参考gk_zfpz表对碰状态） ';
176 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.wf_status
177 is ' 审结状态(0草稿,1未审结,9已审结) ';
178 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.gk_hzqsd_id
179 is ' 汇总清算单号 ';
180 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.fund
181 is ' ';
182 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.org_type
183 is ' ';
184 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.back_oper_id
185 is ' ';
186 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.back_oper_idea
187 is ' ';
188 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.back_oper_date
189 is ' ';
190 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.back_checker_id
191 is ' ';
192 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.back_checker_idea
193 is ' ';
194 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.back_checker_date
195 is ' ';
196 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.zf_item_amt
197 is '*经济分类支付金额 ';
198 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.zf_itemno
199 is '*经济分类编码 ';
200 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.zf_itemname
201 is '*经济分类名称 ';
202 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.bm_itemnos
203 is '部门经济分类编码 ';
204
205
206
207
208
209
210 ---创建游标结果集合
211 CREATE OR REPLACE PACKAGE PKG_QUERY IS
212
213 -- Author : ADMINISTRATOR
214 -- Created : 2016/12/8 星期四 10:28:37
215 -- Purpose : 用做查询游标
216
217 -- Public type declarations
218 TYPE CUR_QUERY IS REF CURSOR;
219
220 END PKG_QUERY;
221
222
223
224
225 ---创建 获取数据的存储过程
226 create or replace procedure PRO_K3_ZFZXZ(
227 /*ZFPZ_IDS_PCH in number ,*/
228 org_type in varchar2,
229 opertype in varchar2,
230 fdate in varchar2,
231 v_cur out pkg_query.cur_query )
232 is
233 type ref_cursor_type is ref cursor;
234 v_sql VARCHAR2(4000);
235 v_index number :=0 ;/*循环数据的循环次数*/
236
237
238 /* 获取分组数据信息 */
239 cursor_zfzxz_zfpz ref_cursor_type; /*游标*/
240 v_row_zfzxz_zfpz view_k3_zfzxz_zfpz_group%rowtype ;
241 v_zfzxz_zfpz_sql varchar2(4000) :='';
242
243 /** 分组求和 **/
244 cursor_gb_zfzxz ref_cursor_type; /*游标*/
245 v_gb_zfzxz_sql varchar2(4000) :='';
246 v_row_gb_zfzxz gk_zwentry_k3_zfzxz_zfpz_sum%rowtype ;
247
248 v_sum_amt number(16,2) :=0;
249 v_sum_zf_item_amt number(16,2) :=0;
250 v_sum_zfpz_amt number(16,2) :=0;
251 v_list_gk_zfpz_id varchar2(4000) :='';
252 v_list_id varchar2(4000) :='';
253 v_list_origin_id varchar2(4000) :='';
254 v_list_voucher_no varchar2(4000) :='';
255 v_list_purpose varchar2(4000) :='';
256 v_list_remark varchar2(4000) :='';
257 v_list_bm_itemnos varchar2(4000) :='';
258 v_list_amt varchar2(4000) :='';
259 v_list_zf_item_amt varchar2(4000) :='';
260 v_list_zfpz_amt varchar2(4000) :='';
261
262 /*查询 view_k3_zfzxz_zfpz 的具体信息 */
263 cursor_q_vk3_zfzxzzfpz ref_cursor_type; /*游标*/
264 v_q_vk3_zfzxzzfpz_sql LONG :='' ;
265 v_row_q_vk3_zfzxzzfpz view_k3_zfzxz_zfpz%rowtype ;
266
267
268 /*查询 view_k3_zfzxz_zfpz 的具体有多少条数据信息*/
269 cursor_q_vk3_zfzxzzfpz_count ref_cursor_type; /*游标*/
270 v_row_q_vk3_zfzxzzfpz_count view_k3_zfzxz_zfpz_count%rowtype ;
271 v_q_vk3_zfzxzzfpz_count_sql LONG :='' ;
272 v_rowcount number :=0;/*获取游标取数的条数*/
273
274 v_zfpzId_sql varchar2(1000) :='';
275
276 v_gk_zwvch_refer_temp_pch number :=0;
277 v_pch number :=0;/*批次号*/
278
279 begin
280 dbms_output.enable (buffer_size=>null) ;
281
282 select nextid('GK_ZWVCH_REFER_TEMP_SEQ') as v_gk_zwvch_refer_temp_pch into v_gk_zwvch_refer_temp_pch from dual;
283 if sql%found then
284 v_pch :=v_gk_zwvch_refer_temp_pch;
285 end if;
286
287
288
289 --先判断入参是否有值，如果没有值 整个存储过程返回空值
290 if org_type is not null and opertype is not null and fdate is not null then
291 /* org_type 资金来源类型 0 预算内 1预算外
292 opertype 操作类型 0' :'正常对碰的直接支付' 1 '正常对碰的授权支付' 2 :'退款的直接支付' 3 :'退款的授权支付' 4 :'已审结的调账申请'
293 fdate 凭证回单日期 */
294
295 if org_type= '1' /*预算外*/ then
296
297 if opertype = '0' then
298 --正常对碰的直接支付
299 v_zfpzId_sql:= 'and exists ( select gz.id from gk_zfpz gz,bs_zjly zjly where gz.voucher_type =''''0'''' and gz.check_status in ( ''1'',''2'',''02'' ) and gz.src_id=zjly.fundno
300
301 and zjly.fundno in(''043'', ''058'', ''158'', ''258'', ''013'', ''113'', ''213'', ''023'',''123'',''223'', ''046'',''246'',''049'',''149'',''249'',''048'', ''248'',''045'',''245'',''047'',''247'', ''055'',''355'',''455'',''155'',''255'', ''059'', ''159'', ''259'', ''061'', ''161'',''261'', ''026'', ''044'',''144'', ''244'' )
302 and not exists (select r.* from gk_zwvch_refer r where r.origin_teable =''GK_ZFPZ'' and r.orign_id = gz.id and r.fmodule = ''2'' and r.voucher_type=''0'' and r.orign_type=''1'' )
303 and to_char(gz.affirm_date,''yyyy-MM-dd'') = '''||fdate||''' ) ' ;
304
305 elsif opertype = '1' then
306 --正常对碰的授权支付
307 v_zfpzId_sql:= 'and exists ( select gz.id from gk_zfpz gz,bs_zjly zjly where gz.voucher_type =''1'' and gz.check_status in ( ''1'',''2'',''02'' ) and gz.src_id=zjly.fundno
308 and zjly.fundno in(''043'', ''058'', ''158'', ''258'', ''013'', ''113'', ''213'', ''023'',''123'',''223'', ''046'',''246'',''049'',''149'',''249'',''048'', ''248'',''045'',''245'',''047'',''247'', ''055'',''355'',''455'',''155'',''255'', ''059'', ''159'', ''259'', ''061'', ''161'',''261'', ''026'', ''044'',''144'', ''244'' )
309 and not exists (select r.* from gk_zwvch_refer r where r.origin_teable =''GK_ZFPZ'' and r.orign_id = gz.id and r.fmodule = ''2'' and r.voucher_type=''1'' and r.orign_type=''1'' )
310 and to_char(gz.affirm_date,''yyyy-MM-dd'') = '''||fdate||''' ) ' ;
311
312 elsif opertype = '2' then
313 --全额退款的直接支付
314 v_zfpzId_sql:= 'and exists ( select gz.id from gk_zfpz gz,bs_zjly zjly where gz.voucher_type =''''0'''' and gz.check_status in (''2'') and gz.src_id=zjly.fundno
315 and zjly.fundno in( ''043'',''058'', ''158'', ''258'', ''013'', ''113'', ''213'', ''023'',''123'',''223'', ''046'',''246'',''049'',''149'',''249'',''048'', ''248'',''045'',''245'',''047'',''247'', ''055'',''355'',''455'',''155'',''255'', ''059'', ''159'', ''259'', ''061'', ''161'',''261'', ''026'', ''044'',''144'', ''244'' )
316 and not exists (select r.* from gk_zwvch_refer r where r.origin_teable =''GK_ZFPZ'' and r.orign_id = gz.id and r.fmodule = ''2'' and r.voucher_type=''0'' and r.orign_type=''3'' )
317 and to_char(gz.affirm_date,''yyyy-MM-dd'') = '''||fdate||''' ) ' ;
318
319 elsif opertype = '3' then
320 -- 全额退款的授权支付
321 v_zfpzId_sql:= 'and exists ( select gz.id from gk_zfpz gz,bs_zjly zjly where gz.voucher_type =''1'' and gz.check_status =''2'' and gz.src_id=zjly.fundno
322 and zjly.fundno in( ''043'',''058'', ''158'', ''258'', ''013'', ''113'', ''213'', ''023'',''123'',''223'', ''046'',''246'',''049'',''149'',''249'',''048'', ''248'',''045'',''245'',''047'',''247'', ''055'',''355'',''455'',''155'',''255'', ''059'', ''159'', ''259'', ''061'', ''161'',''261'', ''026'', ''044'',''144'', ''244'' )
323 and not exists (select r.* from gk_zwvch_refer r where r.origin_teable =''GK_ZFPZ'' and r.orign_id = gz.id and r.fmodule = ''2'' and r.voucher_type=''1'' and r.orign_type=''3'' )
324 and to_char(gz.affirm_date,''yyyy-MM-dd'') = '''||fdate||''' ) ' ;
325
326 end if;
327
328 elsif org_type= '0' /*预算内 */ then
329 /* org_type 资金来源类型 0 预算内 1预算外
330 opertype 操作类型 0'' :''正常对碰的直接支付'' 1 ''正常对碰的授权支付'' 2 :''退款的直接支付'' 3 :''退款的授权支付'' 4 :''已审结的调账申请''
331 fdate 凭证回单日期 */
332 if opertype = 0 then
333 --正常对碰的直接支付
334 v_zfpzId_sql:= 'and exists (
335 select gz.id from gk_zfpz gz,bs_zjly zjly
336 where gz.voucher_type =''0'' and gz.check_status in ( ''1'',''2'',''02'' ) and gz.src_id=zjly.fundno and zjly.org_type=1 and zjly.fundno not in (''007'',''107'',''207'')
337 and not exists (select r.* from gk_zwvch_refer r where r.origin_teable =''GK_ZFPZ'' and r.orign_id = gz.id and r.fmodule = ''1'' and r.voucher_type=''0'' and r.orign_type=''1'' )
338 and to_char(gz.affirm_date,''yyyy-MM-dd'') = '''||fdate||''' ) ' ;
339
340 elsif opertype = '1' then
341 -- 正常对碰的授权支付
342 v_zfpzId_sql:= 'and exists (
343 select gz.id from gk_zfpz gz,bs_zjly zjly
344 where gz.voucher_type =''0'' and gz.check_status in ( ''1'',''2'',''02'' ) and gz.src_id=zjly.fundno and zjly.org_type=1 and zjly.fundno not in (''007'',''107'',''207'')
345 and not exists (select r.* from gk_zwvch_refer r where r.origin_teable =''GK_ZFPZ'' and r.orign_id = gz.id and r.fmodule = ''1'' and r.voucher_type=''0'' and r.orign_type=''1'' )
346 and to_char(gz.affirm_date,''yyyy-MM-dd'') = '''||fdate||''' ) ' ;
347
348 elsif opertype = '2' then
349 -- 全额退款的直接支付
350 v_zfpzId_sql:= 'and exists (
351 select gz.id from gk_zfpz gz,bs_zjly zjly where gz.voucher_type =''0'' and gz.check_status =''2'' and gz.src_id=zjly.fundno and zjly.org_type=1 and zjly.fundno not in (''007'',''107'',''207'')
352 and not exists (select r.* from gk_zwvch_refer r where r.origin_teable =''GK_ZFPZ'' and r.orign_id = gz.id and r.fmodule = ''1'' and r.voucher_type=''0'' and r.orign_type=''3'' )
353 and to_char(gz.affirm_date,''yyyy-MM-dd'') = '''||fdate||''' ) ' ;
354
355 elsif opertype = '3' then
356 ---全额退款的授权支付
357 v_zfpzId_sql:= 'and exists (
358 select gz.id from gk_zfpz gz,bs_zjly zjly where gz.voucher_type =''1'' and gz.check_status =''2'' and gz.src_id=zjly.fundno and zjly.org_type=1 and zjly.fundno not in (''007'',''107'',''207'')
359 and not exists (select r.* from gk_zwvch_refer r where r.origin_teable =''GK_ZFPZ'' and r.orign_id = gz.id and r.fmodule = ''1'' and r.voucher_type=''1'' and r.orign_type=''3'' )
360 and to_char(gz.affirm_date,''yyyy-MM-dd'') = '''||fdate||''' ) ' ;
361 end if;
362
363 end if;
364
365
366
367
368 /*获取视图 view_k3_zfzxz_zfpz 的 同单位 同资金来源 同项目 同功能分类科目类型 同功能分类科目编码 同*经济分类编码 的数据 */
369 v_zfzxz_zfpz_sql :=' select z.unitno,z.prjno,z.src_id,z.budget_type,z.budgetno,z.zf_itemno from view_k3_zfzxz_zfpz z where 1=1 ';
370
371
372
373 v_zfzxz_zfpz_sql :=v_zfzxz_zfpz_sql||v_zfpzId_sql ||' group by z.unitno,z.prjno,z.src_id,z.budget_type,z.budgetno,z.bdgt_accid,z.prjno,z.zf_itemno order by z.unitno,z.prjno,z.src_id,z.budget_type,z.budgetno,z.bdgt_accid,z.prjno,z.zf_itemno ';
374
375 open cursor_zfzxz_zfpz for v_zfzxz_zfpz_sql ;
376 fetch cursor_zfzxz_zfpz into v_row_zfzxz_zfpz;
377 while cursor_zfzxz_zfpz%found loop
378
379 /*赋值*/
380 v_sum_amt :=0;
381 v_sum_zf_item_amt :=0;
382 v_sum_zfpz_amt :=0;
383 v_list_gk_zfpz_id :='';
384 v_list_id :='';
385 v_list_origin_id :='';
386 v_list_voucher_no :='';
387 v_list_purpose :='';
388 v_list_remark :='';
389 v_list_bm_itemnos :='';
390 v_list_amt :='';
391 v_list_zf_item_amt :='';
392 v_list_zfpz_amt :='';
393
394
395 /*取出合并的金额、凭证id列表、 */
396 v_gb_zfzxz_sql :=' select sum(z.amt) as sum_amt ,sum(z.zf_item_amt) as sum_zf_item_amt , sum(z.zfpz_amt) as sum_zfpz_amt from view_k3_zfzxz_zfpz z where 1=1 ' ;
397
398
399 v_gb_zfzxz_sql :=v_gb_zfzxz_sql ||v_zfpzId_sql ;
400
401 if v_row_zfzxz_zfpz.unitno is not null then
402 v_gb_zfzxz_sql :=v_gb_zfzxz_sql || ' and z.unitno='||v_row_zfzxz_zfpz.unitno ;
403 end if ;
404 if v_row_zfzxz_zfpz.prjno is not null then
405 v_gb_zfzxz_sql :=v_gb_zfzxz_sql || ' and z.prjno='||v_row_zfzxz_zfpz.prjno ;
406 end if ;
407 if v_row_zfzxz_zfpz.src_id is not null then
408 v_gb_zfzxz_sql :=v_gb_zfzxz_sql || ' and z.src_id='||v_row_zfzxz_zfpz.src_id ;
409 end if;
410 if v_row_zfzxz_zfpz.budget_type is not null then
411 v_gb_zfzxz_sql :=v_gb_zfzxz_sql || ' and z.budget_type='||v_row_zfzxz_zfpz.budget_type ;
412 end if;
413 if v_row_zfzxz_zfpz.budgetno is not null then
414 v_gb_zfzxz_sql :=v_gb_zfzxz_sql || ' and z.budgetno='||v_row_zfzxz_zfpz.budgetno ;
415 end if;
416 if v_row_zfzxz_zfpz.zf_itemno is not null then
417 v_gb_zfzxz_sql :=v_gb_zfzxz_sql || ' and z.zf_itemno='||v_row_zfzxz_zfpz.zf_itemno ;
418 end if;
419 /*取出合并的同单位 同资金来源 同项目编码 同功能分类科目类型 同功能分类科目编码 同*经济分类编码 的金额求和 值 */
420 dbms_output.put_line('====进行PRO_K3_ZFZXZ 调试 v_gb_zfzxz_sql v_gb_zfzxz_sql='|| v_gb_zfzxz_sql||'==== ' );
421 open cursor_gb_zfzxz for v_gb_zfzxz_sql ;
422 fetch cursor_gb_zfzxz into v_row_gb_zfzxz;
423 while cursor_gb_zfzxz%found loop
424 /*赋值*/
425 if v_row_gb_zfzxz.sum_amt is not null then
426 v_sum_amt :=v_row_gb_zfzxz.sum_amt;
427 end if;
428 if v_row_gb_zfzxz.sum_zf_item_amt is not null then
429 v_sum_zf_item_amt :=v_row_gb_zfzxz.sum_zf_item_amt;
430 end if;
431 if v_row_gb_zfzxz.sum_zfpz_amt is not null then
432 v_sum_zfpz_amt :=v_row_gb_zfzxz.sum_zfpz_amt;
433 end if ;
434
435
436 fetch cursor_gb_zfzxz into v_row_gb_zfzxz;/*先让指针指向结果集中的第一行，并将值保存到v_row_zfzxz_zfpz 中*/
437 exit when cursor_gb_zfzxz%notfound or cursor_gb_zfzxz%notfound is null;
438 end loop;
439 close cursor_gb_zfzxz;/*关闭游标*/
440
441 v_q_vk3_zfzxzzfpz_sql :='select gk_zfpz_id, id, origin_id, voucher_no, make_date, fk_account, fk_bank, src_id, sk_name, sk_account, sk_bank, unitno, unit_name,
442 budget_type, bdgt_accid, budgetno, budget_name, prjno, prj_name, zfpz_amt, amt, purpose, remark, checker, checker_date, affirmer,
443 affirm_date, voucher_type, check_status, wf_status, gk_hzqsd_id, fund, org_type, back_oper_id, back_oper_idea, back_oper_date,
444 back_checker_id, back_checker_idea, back_checker_date, zf_item_amt, zf_itemno, zf_itemname, bm_itemnos
445 from view_k3_zfzxz_zfpz z where 1=1 ';
446
447 v_q_vk3_zfzxzzfpz_sql :=v_q_vk3_zfzxzzfpz_sql ||v_zfpzId_sql ;
448 if v_row_zfzxz_zfpz.unitno is not null then
449 v_q_vk3_zfzxzzfpz_sql :=v_q_vk3_zfzxzzfpz_sql || ' and z.unitno='||v_row_zfzxz_zfpz.unitno ;
450 end if ;
451 if v_row_zfzxz_zfpz.prjno is not null then
452 v_q_vk3_zfzxzzfpz_sql :=v_q_vk3_zfzxzzfpz_sql || ' and z.prjno='||v_row_zfzxz_zfpz.prjno ;
453 end if ;
454 if v_row_zfzxz_zfpz.src_id is not null then
455 v_q_vk3_zfzxzzfpz_sql :=v_q_vk3_zfzxzzfpz_sql || ' and z.src_id='||v_row_zfzxz_zfpz.src_id ;
456 end if;
457 if v_row_zfzxz_zfpz.budget_type is not null then
458 v_q_vk3_zfzxzzfpz_sql :=v_q_vk3_zfzxzzfpz_sql || ' and z.budget_type='||v_row_zfzxz_zfpz.budget_type ;
459 end if;
460 if v_row_zfzxz_zfpz.budgetno is not null then
461 v_q_vk3_zfzxzzfpz_sql :=v_q_vk3_zfzxzzfpz_sql || ' and z.budgetno='||v_row_zfzxz_zfpz.budgetno ;
462 end if;
463 if v_row_zfzxz_zfpz.zf_itemno is not null then
464 v_q_vk3_zfzxzzfpz_sql :=v_q_vk3_zfzxzzfpz_sql || ' and z.zf_itemno='||v_row_zfzxz_zfpz.zf_itemno ;
465 end if;
466
467
468 v_q_vk3_zfzxzzfpz_count_sql:= 'select count(*) count_no from view_k3_zfzxz_zfpz z where 1=1 ' || v_zfpzId_sql ;
469
470 if v_row_zfzxz_zfpz.unitno is not null then
471 v_q_vk3_zfzxzzfpz_count_sql :=v_q_vk3_zfzxzzfpz_count_sql || ' and z.unitno='||v_row_zfzxz_zfpz.unitno ;
472 end if ;
473 if v_row_zfzxz_zfpz.prjno is not null then
474 v_q_vk3_zfzxzzfpz_count_sql :=v_q_vk3_zfzxzzfpz_count_sql || ' and z.prjno='||v_row_zfzxz_zfpz.prjno ;
475 end if ;
476 if v_row_zfzxz_zfpz.src_id is not null then
477 v_q_vk3_zfzxzzfpz_count_sql :=v_q_vk3_zfzxzzfpz_count_sql || ' and z.src_id='||v_row_zfzxz_zfpz.src_id ;
478 end if;
479 if v_row_zfzxz_zfpz.budget_type is not null then
480 v_q_vk3_zfzxzzfpz_count_sql :=v_q_vk3_zfzxzzfpz_count_sql || ' and z.budget_type='||v_row_zfzxz_zfpz.budget_type ;
481 end if;
482 if v_row_zfzxz_zfpz.budgetno is not null then
483 v_q_vk3_zfzxzzfpz_count_sql :=v_q_vk3_zfzxzzfpz_count_sql || ' and z.budgetno='||v_row_zfzxz_zfpz.budgetno ;
484 end if;
485 if v_row_zfzxz_zfpz.zf_itemno is not null then
486 v_q_vk3_zfzxzzfpz_count_sql :=v_q_vk3_zfzxzzfpz_count_sql || ' and z.zf_itemno='||v_row_zfzxz_zfpz.zf_itemno ;
487 end if;
488
489 v_rowcount:=0;
490 dbms_output.put_line('====进行 v_q_vk3_zfzxzzfpz_count_sql='|| v_q_vk3_zfzxzzfpz_count_sql||'==== ' );
491 open cursor_q_vk3_zfzxzzfpz_count for v_q_vk3_zfzxzzfpz_count_sql ;
492
493 fetch cursor_q_vk3_zfzxzzfpz_count into v_row_q_vk3_zfzxzzfpz_count;
494
495 while cursor_q_vk3_zfzxzzfpz_count%found loop
496 v_rowcount:=v_row_q_vk3_zfzxzzfpz_count.count_no;
497
498 fetch cursor_q_vk3_zfzxzzfpz_count into v_row_q_vk3_zfzxzzfpz_count;/*先让指针指向结果集中的第一行，并将值保存到v_row_zfzxz_zfpz 中*/
499 exit when cursor_q_vk3_zfzxzzfpz_count%notfound or cursor_q_vk3_zfzxzzfpz_count%notfound is null;
500 end loop;
501
502
503
504
505
506 /*取出 view_k3_zfzxz_zfpz 视图中的数据 */
507 open cursor_q_vk3_zfzxzzfpz for v_q_vk3_zfzxzzfpz_sql ;
508 fetch cursor_q_vk3_zfzxzzfpz into v_row_q_vk3_zfzxzzfpz;
509 while cursor_q_vk3_zfzxzzfpz%found loop
510 /*判断游标查询结果为几行数据，如果是1行数据，就直接插入临时表中，如果是大于1行的数据集，则取出第一条数据插入 临时表中 */
511 if v_rowcount = 1 then
512
513
514 insert into gk_zwentry_k3_zfzxz_zfpz_temp
515 ( gk_zwvch_refer_temp_pch, gk_zfpz_id, make_date, fk_account, fk_bank, src_id, sk_name, sk_account, sk_bank, unitno, unit_name, budget_type, bdgt_accid, budgetno, budget_name, prjno, prj_name, zfpz_amt, amt, purpose, remark, checker, checker_date, affirmer, affirm_date, voucher_type, check_status, wf_status, gk_hzqsd_id, fund, org_type, back_oper_id, back_oper_idea, back_oper_date, back_checker_id, back_checker_idea, back_checker_date, zf_item_amt, zf_itemno, zf_itemname, bm_itemnos)
516 values
517 ( v_pch, v_row_q_vk3_zfzxzzfpz.gk_zfpz_id, v_row_q_vk3_zfzxzzfpz.make_date, v_row_q_vk3_zfzxzzfpz.fk_account, v_row_q_vk3_zfzxzzfpz.fk_bank, v_row_q_vk3_zfzxzzfpz.src_id, v_row_q_vk3_zfzxzzfpz.sk_name, v_row_q_vk3_zfzxzzfpz.sk_account, v_row_q_vk3_zfzxzzfpz.sk_bank, v_row_q_vk3_zfzxzzfpz.unitno, v_row_q_vk3_zfzxzzfpz.unit_name, v_row_q_vk3_zfzxzzfpz.budget_type, v_row_q_vk3_zfzxzzfpz.bdgt_accid, v_row_q_vk3_zfzxzzfpz.budgetno, v_row_q_vk3_zfzxzzfpz.budget_name, v_row_q_vk3_zfzxzzfpz.prjno, v_row_q_vk3_zfzxzzfpz.prj_name, v_row_q_vk3_zfzxzzfpz.zfpz_amt, v_row_q_vk3_zfzxzzfpz.amt, v_row_q_vk3_zfzxzzfpz.purpose, v_row_q_vk3_zfzxzzfpz.remark, v_row_q_vk3_zfzxzzfpz.checker, v_row_q_vk3_zfzxzzfpz.checker_date, v_row_q_vk3_zfzxzzfpz.affirmer, v_row_q_vk3_zfzxzzfpz.affirm_date, v_row_q_vk3_zfzxzzfpz.voucher_type, v_row_q_vk3_zfzxzzfpz.check_status, v_row_q_vk3_zfzxzzfpz.wf_status, v_row_q_vk3_zfzxzzfpz.gk_hzqsd_id, v_row_q_vk3_zfzxzzfpz.fund, v_row_q_vk3_zfzxzzfpz.org_type, v_row_q_vk3_zfzxzzfpz.back_oper_id, v_row_q_vk3_zfzxzzfpz.back_oper_idea, v_row_q_vk3_zfzxzzfpz.back_oper_date, v_row_q_vk3_zfzxzzfpz.back_checker_id, v_row_q_vk3_zfzxzzfpz.back_checker_idea, v_row_q_vk3_zfzxzzfpz.back_checker_date, v_row_q_vk3_zfzxzzfpz.zf_item_amt, v_row_q_vk3_zfzxzzfpz.zf_itemno, v_row_q_vk3_zfzxzzfpz.zf_itemname, v_row_q_vk3_zfzxzzfpz.bm_itemnos);
518
519
520 elsif v_rowcount > 1 then
521
522
523 insert into gk_zwentry_k3_zfzxz_zfpz_temp
524 (gk_zwvch_refer_temp_pch, gk_zfpz_id, make_date, fk_account, fk_bank, src_id, sk_name, sk_account, sk_bank, unitno, unit_name, budget_type, bdgt_accid, budgetno, budget_name, prjno, prj_name, zfpz_amt, amt, purpose, remark, checker, checker_date, affirmer, affirm_date, voucher_type, check_status, wf_status, gk_hzqsd_id, fund, org_type, back_oper_id, back_oper_idea, back_oper_date, back_checker_id, back_checker_idea, back_checker_date, zf_item_amt, zf_itemno, zf_itemname, bm_itemnos)
525 values
526 ( v_pch, v_row_q_vk3_zfzxzzfpz.gk_zfpz_id,v_row_q_vk3_zfzxzzfpz.make_date, v_row_q_vk3_zfzxzzfpz.fk_account, v_row_q_vk3_zfzxzzfpz.fk_bank, v_row_q_vk3_zfzxzzfpz.src_id, v_row_q_vk3_zfzxzzfpz.sk_name, v_row_q_vk3_zfzxzzfpz.sk_account, v_row_q_vk3_zfzxzzfpz.sk_bank, v_row_q_vk3_zfzxzzfpz.unitno, v_row_q_vk3_zfzxzzfpz.unit_name, v_row_q_vk3_zfzxzzfpz.budget_type, v_row_q_vk3_zfzxzzfpz.bdgt_accid, v_row_q_vk3_zfzxzzfpz.budgetno, v_row_q_vk3_zfzxzzfpz.budget_name, v_row_q_vk3_zfzxzzfpz.prjno, v_row_q_vk3_zfzxzzfpz.prj_name,v_sum_zfpz_amt, v_sum_amt,v_row_q_vk3_zfzxzzfpz.purpose, v_row_q_vk3_zfzxzzfpz.remark, v_row_q_vk3_zfzxzzfpz.checker, v_row_q_vk3_zfzxzzfpz.checker_date, v_row_q_vk3_zfzxzzfpz.affirmer, v_row_q_vk3_zfzxzzfpz.affirm_date, v_row_q_vk3_zfzxzzfpz.voucher_type, v_row_q_vk3_zfzxzzfpz.check_status, v_row_q_vk3_zfzxzzfpz.wf_status, v_row_q_vk3_zfzxzzfpz.gk_hzqsd_id, v_row_q_vk3_zfzxzzfpz.fund, v_row_q_vk3_zfzxzzfpz.org_type, v_row_q_vk3_zfzxzzfpz.back_oper_id, v_row_q_vk3_zfzxzzfpz.back_oper_idea, v_row_q_vk3_zfzxzzfpz.back_oper_date, v_row_q_vk3_zfzxzzfpz.back_checker_id, v_row_q_vk3_zfzxzzfpz.back_checker_idea, v_row_q_vk3_zfzxzzfpz.back_checker_date, v_sum_zf_item_amt, v_row_q_vk3_zfzxzzfpz.zf_itemno, v_row_q_vk3_zfzxzzfpz.zf_itemname, v_list_bm_itemnos );
527
528 EXIT;/*跳出本循环体 ，继续执行上一级循环体 */
529 end if;
530
531 fetch cursor_q_vk3_zfzxzzfpz into v_row_q_vk3_zfzxzzfpz;/*先让指针指向结果集中的第一行，并将值保存到v_row_zfzxz_zfpz 中*/
532 exit when cursor_q_vk3_zfzxzzfpz%notfound or cursor_q_vk3_zfzxzzfpz%notfound is null;
533 end loop;
534
535 close cursor_q_vk3_zfzxzzfpz;/*关闭游标*/
536
537
538
539 fetch cursor_zfzxz_zfpz into v_row_zfzxz_zfpz;/*先让指针指向结果集中的第一行，并将值保存到v_row_zfzxz_zfpz 中*/
540 exit when cursor_zfzxz_zfpz%notfound or cursor_zfzxz_zfpz%notfound is null;
541 end loop;
542 close cursor_zfzxz_zfpz;/*关闭游标*/
543
544
545 /*GK_ZWVCH_REFER_TEMP 表中插入数据 */
546
547 v_q_vk3_zfzxzzfpz_sql :='select gk_zfpz_id, id, origin_id, voucher_no, make_date, fk_account, fk_bank, src_id, sk_name, sk_account, sk_bank, unitno, unit_name,
548 budget_type, bdgt_accid, budgetno, budget_name, prjno, prj_name, zfpz_amt, amt, purpose, remark, checker, checker_date, affirmer,
549 affirm_date, voucher_type, check_status, wf_status, gk_hzqsd_id, fund, org_type, back_oper_id, back_oper_idea, back_oper_date,
550 back_checker_id, back_checker_idea, back_checker_date, zf_item_amt, zf_itemno, zf_itemname, bm_itemnos
551 from view_k3_zfzxz_zfpz z where 1=1 ';
552
553 v_q_vk3_zfzxzzfpz_sql :=v_q_vk3_zfzxzzfpz_sql ||v_zfpzId_sql ;
554 /*取出 view_k3_zfzxz_zfpz 视图中的数据 */
555 open cursor_q_vk3_zfzxzzfpz for v_q_vk3_zfzxzzfpz_sql ;
556 fetch cursor_q_vk3_zfzxzzfpz into v_row_q_vk3_zfzxzzfpz;
557 while cursor_q_vk3_zfzxzzfpz%found loop
558
559 insert into gk_zwvch_refer_temp
560 (pch, zfpz_id, zfpz_amt, bm_itemno, zf_itemno, zf_itemname, itemno_amt)
561 values
562 (v_pch, v_row_q_vk3_zfzxzzfpz.gk_zfpz_id,v_row_q_vk3_zfzxzzfpz.zfpz_amt ,v_row_q_vk3_zfzxzzfpz.bm_itemnos ,v_row_q_vk3_zfzxzzfpz.zf_itemno ,v_row_q_vk3_zfzxzzfpz.zf_itemname ,v_row_q_vk3_zfzxzzfpz.zf_item_amt );
563
564 fetch cursor_q_vk3_zfzxzzfpz into v_row_q_vk3_zfzxzzfpz;/*先让指针指向结果集中的第一行，并将值保存到v_row_zfzxz_zfpz 中*/
565 exit when cursor_q_vk3_zfzxzzfpz%notfound or cursor_q_vk3_zfzxzzfpz%notfound is null;
566 end loop;
567 close cursor_q_vk3_zfzxzzfpz;/*关闭游标*/
568
569
570
571
572 end if;
573 v_sql :=' select gk_zwvch_refer_temp_pch as pch, gk_zfpz_id, make_date, fk_account, fk_bank, src_id, sk_name,
574 sk_account, sk_bank, unitno, unit_name, budget_type, bdgt_accid, budgetno, budget_name, prjno, prj_name,
575 zfpz_amt, amt, purpose, remark, checker, checker_date, affirmer, affirm_date, voucher_type, check_status,
576 wf_status, gk_hzqsd_id, fund, org_type, back_oper_id, back_oper_idea, back_oper_date, back_checker_id,
577 back_checker_idea, back_checker_date, zf_item_amt, zf_itemno, zf_itemname, bm_itemnos
578 from gk_zwentry_k3_zfzxz_zfpz_temp z ';
579
580 OPEN v_cur FOR v_sql;
581
582 commit;
583
584 end PRO_K3_ZFZXZ;
585
586
587 /
588
1
2
3
4
5
6
7
8 ---创建序列号
9 insert into PWP_NO (NOID, PREFIX, INITIALVALUE, BUFFERSIZE, POSTFIX, NOINCREMENT, NONAME, NOLENGTH, NOTYPE)
10 values ('GK_ZWVCH_REFER_TEMP_SEQ', null, 1, 10, null, 1, 'GK_ZWVCH_REFER_TEMP_SEQ', 20, 1);
11 insert into PWP_NODTL (NOID, PREFIX, NODAY, NEXTID, POSTFIX, NOINCREMENT, NOYEAR, NOMONTH)
12 values ('GK_ZWVCH_REFER_TEMP_SEQ', null, 0, 2, null, 1, 2018, 4);
13
14 commit;
15
16
17 create or replace view view_k3_zfzxz_zfpz_group as
18 select z.unitno, z.prjno, z.src_id, z.budget_type, z.budgetno, z.zf_itemno
19 ----创建视图：进行分组。主要用于存储过程pro_k3_zfzxz 的 rowtype里的
20 from view_k3_zfzxz_zfpz z group by z.unitno, z.prjno,z.src_id, z.budget_type,z.budgetno,z.bdgt_accid, z.prjno, z.zf_itemno
21 order by z.unitno, z.prjno, z.src_id, z.budget_type, z.budgetno, z.bdgt_accid, z.prjno, z.zf_itemno ;
22 comment on table view_k3_zfzxz_zfpz_group is '该视图主要是用于 存储过程 pro_k3_zfzxz 的 rowtype里的 ；作为一个rowtype 类型使用而已 ';
23
24
25
26 create or replace view view_k3_zfzxz_zfpz_count as select count(a) as count_no from ( select '1' as a from dual );
27 comment on table view_k3_zfzxz_zfpz_count is '该视图主要是用于 存储过程 pro_k3_zfzxz 的 rowtype里的 ；作为一个rowtype 类型使用而已 ';
28
29
30 drop table gk_zwentry_k3_zfzxz_zfpz_sum;
31 -- Create table
32 create table gk_zwentry_k3_zfzxz_zfpz_sum
33 (
34 sum_amt NUMBER,
35 sum_zf_item_amt NUMBER,
36 sum_zfpz_amt NUMBER
37 );
38 comment on table gk_zwentry_k3_zfzxz_zfpz_sum is '该视图主要是用于 存储过程 pro_k3_zfzxz 的 rowtype里的 ；作为一个rowtype 类型使用而已 ';
39
40
41 drop table GK_ZWVCH_REFER_TEMP ;
42 -- Create table
43 create table GK_ZWVCH_REFER_TEMP
44 (
45
46 pch NUMBER(20) not null,
47 zfpz_id NUMBER(20) not null,
48 zfpz_amt NUMBER(16,2),
49 bm_itemno VARCHAR2(4000),
50 zf_itemno VARCHAR2(30),
51 zf_itemname VARCHAR2(3000),
52 itemno_amt NUMBER(16,2),
53 CONSTRAINT PK_GK_ZWVCH_REFER_TEMP PRIMARY KEY (pch,zfpz_id) -- 联合主键
54 ) ;
55 comment on table GK_ZWVCH_REFER_TEMP is '该表主要是用于 存储过程 pro_k3_zfzxz 的 rowtype里的用来存放中福在线账中支付凭证的数据信息 ';
56 comment on column GK_ZWVCH_REFER_TEMP.pch is '批次号 ';
57 comment on column GK_ZWVCH_REFER_TEMP.zfpz_id is '支付凭证表主键 ';
58 comment on column GK_ZWVCH_REFER_TEMP.zfpz_amt is '支付凭证支付金额 ';
59 comment on column GK_ZWVCH_REFER_TEMP.bm_itemno is '部门经济分类编码 ';
60 comment on column GK_ZWVCH_REFER_TEMP.zf_itemno is '支付经济分类编码 ';
61 comment on column GK_ZWVCH_REFER_TEMP.zf_itemname is '*经济分类名称 ';
62 comment on column GK_ZWVCH_REFER_TEMP.itemno_amt is '部分经济分类支付金额 ';
63
64
65
66
67 drop table gk_zwentry_k3_zfzxz_zfpz_temp ;
68 ---创建 事务级别的临时表 。用来存放进行同单位同资金来源同项目同功能分类科目同经济分类的合并数据
69
70 create global temporary table GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP
71 (
72
73 gk_zfpz_id NUMBER(20) ,
74 GK_ZWVCH_REFER_TEMP_PCH NUMBER(20) ,
75 make_date DATE,
76 fk_account VARCHAR2(60),
77 fk_bank VARCHAR2(60),
78 src_id VARCHAR2(10),
79 sk_name VARCHAR2(100),
80 sk_account VARCHAR2(60),
81 sk_bank VARCHAR2(60),
82 unitno VARCHAR2(30),
83 unit_name VARCHAR2(300),
84 budget_type VARCHAR2(2),
85 bdgt_accid VARCHAR2(20),
86 budgetno VARCHAR2(30),
87 budget_name VARCHAR2(300),
88 prjno VARCHAR2(30),
89 prj_name VARCHAR2(300),
90 zfpz_amt NUMBER(20,2),
91 amt NUMBER(20,2),
92 purpose VARCHAR2(4000) ,
93 remark VARCHAR2(4000) ,
94 checker NUMBER(20),
95 checker_date DATE,
96 affirmer NUMBER(20),
97 affirm_date DATE,
98 voucher_type VARCHAR2(2),
99 check_status VARCHAR2(2),
100 wf_status VARCHAR2(2),
101 gk_hzqsd_id NUMBER(20),
102 fund VARCHAR2(500),
103 org_type VARCHAR2(2),
104 back_oper_id NUMBER(20),
105 back_oper_idea VARCHAR2(100),
106 back_oper_date DATE,
107 back_checker_id NUMBER(20),
108 back_checker_idea VARCHAR2(100),
109 back_checker_date DATE,
110 zf_item_amt NUMBER,
111 zf_itemno VARCHAR2(30),
112 zf_itemname VARCHAR2(4000) ,
113 bm_itemnos VARCHAR2(4000)
114
115 )
116 on commit preserve rows;
117
118 -- Add comments to the table
119 comment on table GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP
120 is '事务级别的临时表 。用来存放对视图view_k3_zfzxz_zfpz进行同单位同资金来源同项目同功能分类科目同经济分类的合并数据 ';
121 -- Add comments to the columns
122 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.gk_zfpz_id
123 is 'gk_zfpz 表id ';
124
125
126 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.make_date
127 is '支付凭证日期 ';
128 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.fk_account
129 is ' 付款人账号 ';
130 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.fk_bank
131 is ' 付款人开户银行 ';
132 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.src_id
133 is '资金来源代码 ';
134 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.sk_name
135 is '收款人全称 ';
136 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.sk_account
137 is '收款人账号 ';
138 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.sk_bank
139 is ' 收款人开户银行 ';
140 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.unitno
141 is '单位编码 ';
142 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.unit_name
143 is '单位名称 ';
144 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.budget_type
145 is '科目类型代码 ';
146 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.bdgt_accid
147 is ' ';
148 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.budgetno
149 is '功能分类科目编码 ';
150 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.budget_name
151 is '功能分类科目名称 ';
152 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.prjno
153 is '项目编码 ';
154 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.prj_name
155 is '项目名称 ';
156 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.zfpz_amt
157 is '金额(支付凭证表) ';
158 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.amt
159 is ' 金额(经济分类表) ';
160 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.purpose
161 is '用途 ';
162 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.remark
163 is ' 备注 ';
164 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.checker
165 is '审核人 ';
166 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.checker_date
167 is ' 审核日期 ';
168 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.affirmer
169 is '对碰人 ';
170 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.affirm_date
171 is '对碰日期 ';
172 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.voucher_type
173 is '支付凭证类型(参考gk_zfpz表支付凭证类型 ) ';
174 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.check_status
175 is '对碰状态（参考gk_zfpz表对碰状态） ';
176 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.wf_status
177 is ' 审结状态(0草稿,1未审结,9已审结) ';
178 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.gk_hzqsd_id
179 is ' 汇总清算单号 ';
180 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.fund
181 is ' ';
182 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.org_type
183 is ' ';
184 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.back_oper_id
185 is ' ';
186 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.back_oper_idea
187 is ' ';
188 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.back_oper_date
189 is ' ';
190 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.back_checker_id
191 is ' ';
192 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.back_checker_idea
193 is ' ';
194 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.back_checker_date
195 is ' ';
196 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.zf_item_amt
197 is '*经济分类支付金额 ';
198 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.zf_itemno
199 is '*经济分类编码 ';
200 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.zf_itemname
201 is '*经济分类名称 ';
202 comment on column GK_ZWENTRY_K3_ZFZXZ_ZFPZ_TEMP.bm_itemnos
203 is '部门经济分类编码 ';
204
205
206
207
208
209
210 ---创建游标结果集合
211 CREATE OR REPLACE PACKAGE PKG_QUERY IS
212
213 -- Author : ADMINISTRATOR
214 -- Created : 2016/12/8 星期四 10:28:37
215 -- Purpose : 用做查询游标
216
217 -- Public type declarations
218 TYPE CUR_QUERY IS REF CURSOR;
219
220 END PKG_QUERY;
221
222
223
224
225 ---创建 获取数据的存储过程
226
227 create or replace procedure PRO_K3_ZFZXZ(
228 /*ZFPZ_IDS_PCH in number ,*/
229 org_type in varchar2,
230 opertype in varchar2,
231 fdate in varchar2,
232 v_cur out pkg_query.cur_query )
233 is
234 type ref_cursor_type is ref cursor;
235 v_sql VARCHAR2(4000);
236 v_index number :=0 ;/*循环数据的循环次数*/
237
238
239 /* 获取分组数据信息 */
240 cursor_zfzxz_zfpz ref_cursor_type; /*游标*/
241 v_row_zfzxz_zfpz view_k3_zfzxz_zfpz_group%rowtype ;
242 v_zfzxz_zfpz_sql varchar2(4000) :='';
243
244 /** 分组求和 **/
245 cursor_gb_zfzxz ref_cursor_type; /*游标*/
246 v_gb_zfzxz_sql varchar2(4000) :='';
247 v_row_gb_zfzxz gk_zwentry_k3_zfzxz_zfpz_sum%rowtype ;
248
249 v_sum_amt number(16,2) :=0;
250 v_sum_zf_item_amt number(16,2) :=0;
251 v_sum_zfpz_amt number(16,2) :=0;
252 v_list_gk_zfpz_id varchar2(4000) :='';
253 v_list_id varchar2(4000) :='';
254 v_list_origin_id varchar2(4000) :='';
255 v_list_voucher_no varchar2(4000) :='';
256 v_list_purpose varchar2(4000) :='';
257 v_list_remark varchar2(4000) :='';
258 v_list_bm_itemnos varchar2(4000) :='';
259 v_list_amt varchar2(4000) :='';
260 v_list_zf_item_amt varchar2(4000) :='';
261 v_list_zfpz_amt varchar2(4000) :='';
262
263 /*查询 view_k3_zfzxz_zfpz 的具体信息 */
264 cursor_q_vk3_zfzxzzfpz ref_cursor_type; /*游标*/
265 v_q_vk3_zfzxzzfpz_sql LONG :='' ;
266 v_row_q_vk3_zfzxzzfpz view_k3_zfzxz_zfpz%rowtype ;
267
268
269 /*查询 view_k3_zfzxz_zfpz 的具体有多少条数据信息*/
270 cursor_q_vk3_zfzxzzfpz_count ref_cursor_type; /*游标*/
271 v_row_q_vk3_zfzxzzfpz_count view_k3_zfzxz_zfpz_count%rowtype ;
272 v_q_vk3_zfzxzzfpz_count_sql LONG :='' ;
273 v_rowcount number :=0;/*获取游标取数的条数*/
274
275 v_zfpzId_sql varchar2(1000) :='';
276
277 v_gk_zwvch_refer_temp_pch number :=0;
278 v_pch number :=0;/*批次号*/
279
280 begin
281 dbms_output.enable (buffer_size=>null) ;
282
283 select nextid('GK_ZWVCH_REFER_TEMP_SEQ') as v_gk_zwvch_refer_temp_pch into v_gk_zwvch_refer_temp_pch from dual;
284 if sql%found then
285 v_pch :=v_gk_zwvch_refer_temp_pch;
286 end if;
287
288
289
290 --先判断入参是否有值，如果没有值 整个存储过程返回空值
291 if org_type is not null and opertype is not null and fdate is not null then
292 /* org_type 资金来源类型 0 预算内 1预算外
293 opertype 操作类型 0' :'正常对碰的直接支付' 1 '正常对碰的授权支付' 2 :'退款的直接支付' 3 :'退款的授权支付' 4 :'已审结的调账申请'
294 fdate 凭证回单日期 */
295
296 if org_type= '1' /*预算外*/ then
297
298 if opertype = '0' then
299 --正常对碰的直接支付
300 v_zfpzId_sql:= 'and z.id in ( select gz.id from gk_zfpz gz,bs_zjly zjly where gz.voucher_type =''''0'''' and gz.check_status in ( ''1'',''2'',''02'' ) and gz.src_id=zjly.fundno
301
302 and zjly.fundno in(''043'', ''058'', ''158'', ''258'', ''013'', ''113'', ''213'', ''023'',''123'',''223'', ''046'',''246'',''049'',''149'',''249'',''048'', ''248'',''045'',''245'',''047'',''247'', ''055'',''355'',''455'',''155'',''255'', ''059'', ''159'', ''259'', ''061'', ''161'',''261'', ''026'', ''044'',''144'', ''244'' )
303 and not exists (select r.* from gk_zwvch_refer r where r.origin_teable =''GK_ZFPZ'' and r.orign_id = gz.id and r.fmodule = ''2'' and r.voucher_type=''0'' and r.orign_type=''1'' )
304 and to_char(gz.affirm_date,''yyyy-MM-dd'') = '''||fdate||''' ) ' ;
305
306 elsif opertype = '1' then
307 --正常对碰的授权支付
308 v_zfpzId_sql:= 'and z.id in ( select gz.id from gk_zfpz gz,bs_zjly zjly where gz.voucher_type =''1'' and gz.check_status in ( ''1'',''2'',''02'' ) and gz.src_id=zjly.fundno
309 and zjly.fundno in(''043'', ''058'', ''158'', ''258'', ''013'', ''113'', ''213'', ''023'',''123'',''223'', ''046'',''246'',''049'',''149'',''249'',''048'', ''248'',''045'',''245'',''047'',''247'', ''055'',''355'',''455'',''155'',''255'', ''059'', ''159'', ''259'', ''061'', ''161'',''261'', ''026'', ''044'',''144'', ''244'' )
310 and not exists (select r.* from gk_zwvch_refer r where r.origin_teable =''GK_ZFPZ'' and r.orign_id = gz.id and r.fmodule = ''2'' and r.voucher_type=''1'' and r.orign_type=''1'' )
311 and to_char(gz.affirm_date,''yyyy-MM-dd'') = '''||fdate||''' ) ' ;
312
313 elsif opertype = '2' then
314 --全额退款的直接支付
315 v_zfpzId_sql:= 'and z.id in ( select gz.id from gk_zfpz gz,bs_zjly zjly where gz.voucher_type =''''0'''' and gz.check_status in (''2'') and gz.src_id=zjly.fundno
316 and zjly.fundno in( ''043'',''058'', ''158'', ''258'', ''013'', ''113'', ''213'', ''023'',''123'',''223'', ''046'',''246'',''049'',''149'',''249'',''048'', ''248'',''045'',''245'',''047'',''247'', ''055'',''355'',''455'',''155'',''255'', ''059'', ''159'', ''259'', ''061'', ''161'',''261'', ''026'', ''044'',''144'', ''244'' )
317 and not exists (select r.* from gk_zwvch_refer r where r.origin_teable =''GK_ZFPZ'' and r.orign_id = gz.id and r.fmodule = ''2'' and r.voucher_type=''0'' and r.orign_type=''3'' )
318 and to_char(gz.affirm_date,''yyyy-MM-dd'') = '''||fdate||''' ) ' ;
319
320 elsif opertype = '3' then
321 -- 全额退款的授权支付
322 v_zfpzId_sql:= 'and z.id in ( select gz.id from gk_zfpz gz,bs_zjly zjly where gz.voucher_type =''1'' and gz.check_status =''2'' and gz.src_id=zjly.fundno
323 and zjly.fundno in( ''043'',''058'', ''158'', ''258'', ''013'', ''113'', ''213'', ''023'',''123'',''223'', ''046'',''246'',''049'',''149'',''249'',''048'', ''248'',''045'',''245'',''047'',''247'', ''055'',''355'',''455'',''155'',''255'', ''059'', ''159'', ''259'', ''061'', ''161'',''261'', ''026'', ''044'',''144'', ''244'' )
324 and not exists (select r.* from gk_zwvch_refer r where r.origin_teable =''GK_ZFPZ'' and r.orign_id = gz.id and r.fmodule = ''2'' and r.voucher_type=''1'' and r.orign_type=''3'' )
325 and to_char(gz.affirm_date,''yyyy-MM-dd'') = '''||fdate||''' ) ' ;
326
327 end if;
328
329 elsif org_type= '0' /*预算内 */ then
330 /* org_type 资金来源类型 0 预算内 1预算外
331 opertype 操作类型 0'' :''正常对碰的直接支付'' 1 ''正常对碰的授权支付'' 2 :''退款的直接支付'' 3 :''退款的授权支付'' 4 :''已审结的调账申请''
332 fdate 凭证回单日期 */
333 if opertype = 0 then
334 --正常对碰的直接支付
335 v_zfpzId_sql:= 'and z.id in (
336 select gz.id from gk_zfpz gz,bs_zjly zjly
337 where gz.voucher_type =''0'' and gz.check_status in ( ''1'',''2'',''02'' ) and gz.src_id=zjly.fundno and zjly.org_type=1 and zjly.fundno not in (''007'',''107'',''207'')
338 and not exists (select r.* from gk_zwvch_refer r where r.origin_teable =''GK_ZFPZ'' and r.orign_id = gz.id and r.fmodule = ''1'' and r.voucher_type=''0'' and r.orign_type=''1'' )
339 and to_char(gz.affirm_date,''yyyy-MM-dd'') = '''||fdate||''' ) ' ;
340
341 elsif opertype = '1' then
342 -- 正常对碰的授权支付
343 v_zfpzId_sql:= 'and z.id in (
344 select gz.id from gk_zfpz gz,bs_zjly zjly
345 where gz.voucher_type =''0'' and gz.check_status in ( ''1'',''2'',''02'' ) and gz.src_id=zjly.fundno and zjly.org_type=1 and zjly.fundno not in (''007'',''107'',''207'')
346 and not exists (select r.* from gk_zwvch_refer r where r.origin_teable =''GK_ZFPZ'' and r.orign_id = gz.id and r.fmodule = ''1'' and r.voucher_type=''0'' and r.orign_type=''1'' )
347 and to_char(gz.affirm_date,''yyyy-MM-dd'') = '''||fdate||''' ) ' ;
348
349 elsif opertype = '2' then
350 -- 全额退款的直接支付
351 v_zfpzId_sql:= 'and z.id in (
352 select gz.id from gk_zfpz gz,bs_zjly zjly where gz.voucher_type =''0'' and gz.check_status =''2'' and gz.src_id=zjly.fundno and zjly.org_type=1 and zjly.fundno not in (''007'',''107'',''207'')
353 and not exists (select r.* from gk_zwvch_refer r where r.origin_teable =''GK_ZFPZ'' and r.orign_id = gz.id and r.fmodule = ''1'' and r.voucher_type=''0'' and r.orign_type=''3'' )
354 and to_char(gz.affirm_date,''yyyy-MM-dd'') = '''||fdate||''' ) ' ;
355
356 elsif opertype = '3' then
357 ---全额退款的授权支付
358 v_zfpzId_sql:= 'and z.id in (
359 select gz.id from gk_zfpz gz,bs_zjly zjly where gz.voucher_type =''1'' and gz.check_status =''2'' and gz.src_id=zjly.fundno and zjly.org_type=1 and zjly.fundno not in (''007'',''107'',''207'')
360 and not exists (select r.* from gk_zwvch_refer r where r.origin_teable =''GK_ZFPZ'' and r.orign_id = gz.id and r.fmodule = ''1'' and r.voucher_type=''1'' and r.orign_type=''3'' )
361 and to_char(gz.affirm_date,''yyyy-MM-dd'') = '''||fdate||''' ) ' ;
362 end if;
363
364 end if;
365
366
367
368
369 /*获取视图 view_k3_zfzxz_zfpz 的 同单位 同资金来源 同项目 同功能分类科目类型 同功能分类科目编码 同*经济分类编码 的数据 */
370 v_zfzxz_zfpz_sql :=' select z.unitno,z.prjno,z.src_id,z.budget_type,z.budgetno,z.zf_itemno from view_k3_zfzxz_zfpz z where 1=1 ';
371
372
373
374 v_zfzxz_zfpz_sql :=v_zfzxz_zfpz_sql||v_zfpzId_sql ||' group by z.unitno,z.prjno,z.src_id,z.budget_type,z.budgetno,z.bdgt_accid,z.prjno,z.zf_itemno order by z.unitno,z.prjno,z.src_id,z.budget_type,z.budgetno,z.bdgt_accid,z.prjno,z.zf_itemno ';
375
376 open cursor_zfzxz_zfpz for v_zfzxz_zfpz_sql ;
377 fetch cursor_zfzxz_zfpz into v_row_zfzxz_zfpz;
378 while cursor_zfzxz_zfpz%found loop
379
380 /*赋值*/
381 v_sum_amt :=0;
382 v_sum_zf_item_amt :=0;
383 v_sum_zfpz_amt :=0;
384 v_list_gk_zfpz_id :='';
385 v_list_id :='';
386 v_list_origin_id :='';
387 v_list_voucher_no :='';
388 v_list_purpose :='';
389 v_list_remark :='';
390 v_list_bm_itemnos :='';
391 v_list_amt :='';
392 v_list_zf_item_amt :='';
393 v_list_zfpz_amt :='';
394
395
396 /*取出合并的金额、凭证id列表、 */
397 v_gb_zfzxz_sql :=' select sum(z.amt) as sum_amt ,sum(z.zf_item_amt) as sum_zf_item_amt , sum(z.zfpz_amt) as sum_zfpz_amt from view_k3_zfzxz_zfpz z where 1=1 ' ;
398
399
400 v_gb_zfzxz_sql :=v_gb_zfzxz_sql ||v_zfpzId_sql ;
401
402 if v_row_zfzxz_zfpz.unitno is not null then
403 v_gb_zfzxz_sql :=v_gb_zfzxz_sql || ' and z.unitno='||v_row_zfzxz_zfpz.unitno ;
404 end if ;
405 if v_row_zfzxz_zfpz.prjno is not null then
406 v_gb_zfzxz_sql :=v_gb_zfzxz_sql || ' and z.prjno='||v_row_zfzxz_zfpz.prjno ;
407 end if ;
408 if v_row_zfzxz_zfpz.src_id is not null then
409 v_gb_zfzxz_sql :=v_gb_zfzxz_sql || ' and z.src_id='||v_row_zfzxz_zfpz.src_id ;
410 end if;
411 if v_row_zfzxz_zfpz.budget_type is not null then
412 v_gb_zfzxz_sql :=v_gb_zfzxz_sql || ' and z.budget_type='||v_row_zfzxz_zfpz.budget_type ;
413 end if;
414 if v_row_zfzxz_zfpz.budgetno is not null then
415 v_gb_zfzxz_sql :=v_gb_zfzxz_sql || ' and z.budgetno='||v_row_zfzxz_zfpz.budgetno ;
416 end if;
417 if v_row_zfzxz_zfpz.zf_itemno is not null then
418 v_gb_zfzxz_sql :=v_gb_zfzxz_sql || ' and z.zf_itemno='||v_row_zfzxz_zfpz.zf_itemno ;
419 end if;
420 /*取出合并的同单位 同资金来源 同项目编码 同功能分类科目类型 同功能分类科目编码 同*经济分类编码 的金额求和 值 */
421 dbms_output.put_line('====进行PRO_K3_ZFZXZ 调试 v_gb_zfzxz_sql v_gb_zfzxz_sql='|| v_gb_zfzxz_sql||'==== ' );
422 open cursor_gb_zfzxz for v_gb_zfzxz_sql ;
423 fetch cursor_gb_zfzxz into v_row_gb_zfzxz;
424 while cursor_gb_zfzxz%found loop
425 /*赋值*/
426 if v_row_gb_zfzxz.sum_amt is not null then
427 v_sum_amt :=v_row_gb_zfzxz.sum_amt;
428 end if;
429 if v_row_gb_zfzxz.sum_zf_item_amt is not null then
430 v_sum_zf_item_amt :=v_row_gb_zfzxz.sum_zf_item_amt;
431 end if;
432 if v_row_gb_zfzxz.sum_zfpz_amt is not null then
433 v_sum_zfpz_amt :=v_row_gb_zfzxz.sum_zfpz_amt;
434 end if ;
435
436
437 fetch cursor_gb_zfzxz into v_row_gb_zfzxz;/*先让指针指向结果集中的第一行，并将值保存到v_row_zfzxz_zfpz 中*/
438 exit when cursor_gb_zfzxz%notfound or cursor_gb_zfzxz%notfound is null;
439 end loop;
440 close cursor_gb_zfzxz;/*关闭游标*/
441
442 v_q_vk3_zfzxzzfpz_sql :='select gk_zfpz_id, id, origin_id, voucher_no, make_date, fk_account, fk_bank, src_id, sk_name, sk_account, sk_bank, unitno, unit_name,
443 budget_type, bdgt_accid, budgetno, budget_name, prjno, prj_name, zfpz_amt, amt, purpose, remark, checker, checker_date, affirmer,
444 affirm_date, voucher_type, check_status, wf_status, gk_hzqsd_id, fund, org_type, back_oper_id, back_oper_idea, back_oper_date,
445 back_checker_id, back_checker_idea, back_checker_date, zf_item_amt, zf_itemno, zf_itemname, bm_itemnos
446 from view_k3_zfzxz_zfpz z where 1=1 ';
447
448 v_q_vk3_zfzxzzfpz_sql :=v_q_vk3_zfzxzzfpz_sql ||v_zfpzId_sql ;
449 if v_row_zfzxz_zfpz.unitno is not null then
450 v_q_vk3_zfzxzzfpz_sql :=v_q_vk3_zfzxzzfpz_sql || ' and z.unitno='||v_row_zfzxz_zfpz.unitno ;
451 end if ;
452 if v_row_zfzxz_zfpz.prjno is not null then
453 v_q_vk3_zfzxzzfpz_sql :=v_q_vk3_zfzxzzfpz_sql || ' and z.prjno='||v_row_zfzxz_zfpz.prjno ;
454 end if ;
455 if v_row_zfzxz_zfpz.src_id is not null then
456 v_q_vk3_zfzxzzfpz_sql :=v_q_vk3_zfzxzzfpz_sql || ' and z.src_id='||v_row_zfzxz_zfpz.src_id ;
457 end if;
458 if v_row_zfzxz_zfpz.budget_type is not null then
459 v_q_vk3_zfzxzzfpz_sql :=v_q_vk3_zfzxzzfpz_sql || ' and z.budget_type='||v_row_zfzxz_zfpz.budget_type ;
460 end if;
461 if v_row_zfzxz_zfpz.budgetno is not null then
462 v_q_vk3_zfzxzzfpz_sql :=v_q_vk3_zfzxzzfpz_sql || ' and z.budgetno='||v_row_zfzxz_zfpz.budgetno ;
463 end if;
464 if v_row_zfzxz_zfpz.zf_itemno is not null then
465 v_q_vk3_zfzxzzfpz_sql :=v_q_vk3_zfzxzzfpz_sql || ' and z.zf_itemno='||v_row_zfzxz_zfpz.zf_itemno ;
466 end if;
467
468
469 v_q_vk3_zfzxzzfpz_count_sql:= 'select count(*) count_no from view_k3_zfzxz_zfpz z where 1=1 ' || v_zfpzId_sql ;
470
471 if v_row_zfzxz_zfpz.unitno is not null then
472 v_q_vk3_zfzxzzfpz_count_sql :=v_q_vk3_zfzxzzfpz_count_sql || ' and z.unitno='||v_row_zfzxz_zfpz.unitno ;
473 end if ;
474 if v_row_zfzxz_zfpz.prjno is not null then
475 v_q_vk3_zfzxzzfpz_count_sql :=v_q_vk3_zfzxzzfpz_count_sql || ' and z.prjno='||v_row_zfzxz_zfpz.prjno ;
476 end if ;
477 if v_row_zfzxz_zfpz.src_id is not null then
478 v_q_vk3_zfzxzzfpz_count_sql :=v_q_vk3_zfzxzzfpz_count_sql || ' and z.src_id='||v_row_zfzxz_zfpz.src_id ;
479 end if;
480 if v_row_zfzxz_zfpz.budget_type is not null then
481 v_q_vk3_zfzxzzfpz_count_sql :=v_q_vk3_zfzxzzfpz_count_sql || ' and z.budget_type='||v_row_zfzxz_zfpz.budget_type ;
482 end if;
483 if v_row_zfzxz_zfpz.budgetno is not null then
484 v_q_vk3_zfzxzzfpz_count_sql :=v_q_vk3_zfzxzzfpz_count_sql || ' and z.budgetno='||v_row_zfzxz_zfpz.budgetno ;
485 end if;
486 if v_row_zfzxz_zfpz.zf_itemno is not null then
487 v_q_vk3_zfzxzzfpz_count_sql :=v_q_vk3_zfzxzzfpz_count_sql || ' and z.zf_itemno='||v_row_zfzxz_zfpz.zf_itemno ;
488 end if;
489
490 v_rowcount:=0;
491 dbms_output.put_line('====进行 v_q_vk3_zfzxzzfpz_count_sql='|| v_q_vk3_zfzxzzfpz_count_sql||'==== ' );
492 open cursor_q_vk3_zfzxzzfpz_count for v_q_vk3_zfzxzzfpz_count_sql ;
493
494 fetch cursor_q_vk3_zfzxzzfpz_count into v_row_q_vk3_zfzxzzfpz_count;
495
496 while cursor_q_vk3_zfzxzzfpz_count%found loop
497 v_rowcount:=v_row_q_vk3_zfzxzzfpz_count.count_no;
498
499 fetch cursor_q_vk3_zfzxzzfpz_count into v_row_q_vk3_zfzxzzfpz_count;/*先让指针指向结果集中的第一行，并将值保存到v_row_zfzxz_zfpz 中*/
500 exit when cursor_q_vk3_zfzxzzfpz_count%notfound or cursor_q_vk3_zfzxzzfpz_count%notfound is null;
501 end loop;
502
503
504
505
506
507 /*取出 view_k3_zfzxz_zfpz 视图中的数据 */
508 open cursor_q_vk3_zfzxzzfpz for v_q_vk3_zfzxzzfpz_sql ;
509 fetch cursor_q_vk3_zfzxzzfpz into v_row_q_vk3_zfzxzzfpz;
510 while cursor_q_vk3_zfzxzzfpz%found loop
511 /*判断游标查询结果为几行数据，如果是1行数据，就直接插入临时表中，如果是大于1行的数据集，则取出第一条数据插入 临时表中 */
512 if v_rowcount = 1 then
513
514
515 insert into gk_zwentry_k3_zfzxz_zfpz_temp
516 ( gk_zwvch_refer_temp_pch, gk_zfpz_id, make_date, fk_account, fk_bank, src_id, sk_name, sk_account, sk_bank, unitno, unit_name, budget_type, bdgt_accid, budgetno, budget_name, prjno, prj_name, zfpz_amt, amt, purpose, remark, checker, checker_date, affirmer, affirm_date, voucher_type, check_status, wf_status, gk_hzqsd_id, fund, org_type, back_oper_id, back_oper_idea, back_oper_date, back_checker_id, back_checker_idea, back_checker_date, zf_item_amt, zf_itemno, zf_itemname, bm_itemnos)
517 values
518 ( v_pch, v_row_q_vk3_zfzxzzfpz.gk_zfpz_id, v_row_q_vk3_zfzxzzfpz.make_date, v_row_q_vk3_zfzxzzfpz.fk_account, v_row_q_vk3_zfzxzzfpz.fk_bank, v_row_q_vk3_zfzxzzfpz.src_id, v_row_q_vk3_zfzxzzfpz.sk_name, v_row_q_vk3_zfzxzzfpz.sk_account, v_row_q_vk3_zfzxzzfpz.sk_bank, v_row_q_vk3_zfzxzzfpz.unitno, v_row_q_vk3_zfzxzzfpz.unit_name, v_row_q_vk3_zfzxzzfpz.budget_type, v_row_q_vk3_zfzxzzfpz.bdgt_accid, v_row_q_vk3_zfzxzzfpz.budgetno, v_row_q_vk3_zfzxzzfpz.budget_name, v_row_q_vk3_zfzxzzfpz.prjno, v_row_q_vk3_zfzxzzfpz.prj_name, v_row_q_vk3_zfzxzzfpz.zfpz_amt, v_row_q_vk3_zfzxzzfpz.amt, v_row_q_vk3_zfzxzzfpz.purpose, v_row_q_vk3_zfzxzzfpz.remark, v_row_q_vk3_zfzxzzfpz.checker, v_row_q_vk3_zfzxzzfpz.checker_date, v_row_q_vk3_zfzxzzfpz.affirmer, v_row_q_vk3_zfzxzzfpz.affirm_date, v_row_q_vk3_zfzxzzfpz.voucher_type, v_row_q_vk3_zfzxzzfpz.check_status, v_row_q_vk3_zfzxzzfpz.wf_status, v_row_q_vk3_zfzxzzfpz.gk_hzqsd_id, v_row_q_vk3_zfzxzzfpz.fund, v_row_q_vk3_zfzxzzfpz.org_type, v_row_q_vk3_zfzxzzfpz.back_oper_id, v_row_q_vk3_zfzxzzfpz.back_oper_idea, v_row_q_vk3_zfzxzzfpz.back_oper_date, v_row_q_vk3_zfzxzzfpz.back_checker_id, v_row_q_vk3_zfzxzzfpz.back_checker_idea, v_row_q_vk3_zfzxzzfpz.back_checker_date, v_row_q_vk3_zfzxzzfpz.zf_item_amt, v_row_q_vk3_zfzxzzfpz.zf_itemno, v_row_q_vk3_zfzxzzfpz.zf_itemname, v_row_q_vk3_zfzxzzfpz.bm_itemnos);
519
520
521 elsif v_rowcount > 1 then
522
523
524 insert into gk_zwentry_k3_zfzxz_zfpz_temp
525 (gk_zwvch_refer_temp_pch, gk_zfpz_id, make_date, fk_account, fk_bank, src_id, sk_name, sk_account, sk_bank, unitno, unit_name, budget_type, bdgt_accid, budgetno, budget_name, prjno, prj_name, zfpz_amt, amt, purpose, remark, checker, checker_date, affirmer, affirm_date, voucher_type, check_status, wf_status, gk_hzqsd_id, fund, org_type, back_oper_id, back_oper_idea, back_oper_date, back_checker_id, back_checker_idea, back_checker_date, zf_item_amt, zf_itemno, zf_itemname, bm_itemnos)
526 values
527 ( v_pch, v_row_q_vk3_zfzxzzfpz.gk_zfpz_id,v_row_q_vk3_zfzxzzfpz.make_date, v_row_q_vk3_zfzxzzfpz.fk_account, v_row_q_vk3_zfzxzzfpz.fk_bank, v_row_q_vk3_zfzxzzfpz.src_id, v_row_q_vk3_zfzxzzfpz.sk_name, v_row_q_vk3_zfzxzzfpz.sk_account, v_row_q_vk3_zfzxzzfpz.sk_bank, v_row_q_vk3_zfzxzzfpz.unitno, v_row_q_vk3_zfzxzzfpz.unit_name, v_row_q_vk3_zfzxzzfpz.budget_type, v_row_q_vk3_zfzxzzfpz.bdgt_accid, v_row_q_vk3_zfzxzzfpz.budgetno, v_row_q_vk3_zfzxzzfpz.budget_name, v_row_q_vk3_zfzxzzfpz.prjno, v_row_q_vk3_zfzxzzfpz.prj_name,v_sum_zfpz_amt, v_sum_amt,v_row_q_vk3_zfzxzzfpz.purpose, v_row_q_vk3_zfzxzzfpz.remark, v_row_q_vk3_zfzxzzfpz.checker, v_row_q_vk3_zfzxzzfpz.checker_date, v_row_q_vk3_zfzxzzfpz.affirmer, v_row_q_vk3_zfzxzzfpz.affirm_date, v_row_q_vk3_zfzxzzfpz.voucher_type, v_row_q_vk3_zfzxzzfpz.check_status, v_row_q_vk3_zfzxzzfpz.wf_status, v_row_q_vk3_zfzxzzfpz.gk_hzqsd_id, v_row_q_vk3_zfzxzzfpz.fund, v_row_q_vk3_zfzxzzfpz.org_type, v_row_q_vk3_zfzxzzfpz.back_oper_id, v_row_q_vk3_zfzxzzfpz.back_oper_idea, v_row_q_vk3_zfzxzzfpz.back_oper_date, v_row_q_vk3_zfzxzzfpz.back_checker_id, v_row_q_vk3_zfzxzzfpz.back_checker_idea, v_row_q_vk3_zfzxzzfpz.back_checker_date, v_sum_zf_item_amt, v_row_q_vk3_zfzxzzfpz.zf_itemno, v_row_q_vk3_zfzxzzfpz.zf_itemname, v_list_bm_itemnos );
528
529 EXIT;/*跳出本循环体 ，继续执行上一级循环体 */
530 end if;
531
532 fetch cursor_q_vk3_zfzxzzfpz into v_row_q_vk3_zfzxzzfpz;/*先让指针指向结果集中的第一行，并将值保存到v_row_zfzxz_zfpz 中*/
533 exit when cursor_q_vk3_zfzxzzfpz%notfound or cursor_q_vk3_zfzxzzfpz%notfound is null;
534 end loop;
535
536 close cursor_q_vk3_zfzxzzfpz;/*关闭游标*/
537
538
539
540 fetch cursor_zfzxz_zfpz into v_row_zfzxz_zfpz;/*先让指针指向结果集中的第一行，并将值保存到v_row_zfzxz_zfpz 中*/
541 exit when cursor_zfzxz_zfpz%notfound or cursor_zfzxz_zfpz%notfound is null;
542 end loop;
543 close cursor_zfzxz_zfpz;/*关闭游标*/
544
545
546 /*GK_ZWVCH_REFER_TEMP 表中插入数据 */
547
548 v_q_vk3_zfzxzzfpz_sql :='select gk_zfpz_id, id, origin_id, voucher_no, make_date, fk_account, fk_bank, src_id, sk_name, sk_account, sk_bank, unitno, unit_name,
549 budget_type, bdgt_accid, budgetno, budget_name, prjno, prj_name, zfpz_amt, amt, purpose, remark, checker, checker_date, affirmer,
550 affirm_date, voucher_type, check_status, wf_status, gk_hzqsd_id, fund, org_type, back_oper_id, back_oper_idea, back_oper_date,
551 back_checker_id, back_checker_idea, back_checker_date, zf_item_amt, zf_itemno, zf_itemname, bm_itemnos
552 from view_k3_zfzxz_zfpz z where 1=1 ';
553
554 v_q_vk3_zfzxzzfpz_sql :=v_q_vk3_zfzxzzfpz_sql ||v_zfpzId_sql ;
555 /*取出 view_k3_zfzxz_zfpz 视图中的数据 */
556 open cursor_q_vk3_zfzxzzfpz for v_q_vk3_zfzxzzfpz_sql ;
557 fetch cursor_q_vk3_zfzxzzfpz into v_row_q_vk3_zfzxzzfpz;
558 while cursor_q_vk3_zfzxzzfpz%found loop
559
560 insert into gk_zwvch_refer_temp
561 (pch, zfpz_id, zfpz_amt, bm_itemno, zf_itemno, zf_itemname, itemno_amt)
562 values
563 (v_pch, v_row_q_vk3_zfzxzzfpz.gk_zfpz_id,v_row_q_vk3_zfzxzzfpz.zfpz_amt ,v_row_q_vk3_zfzxzzfpz.bm_itemnos ,v_row_q_vk3_zfzxzzfpz.zf_itemno ,v_row_q_vk3_zfzxzzfpz.zf_itemname ,v_row_q_vk3_zfzxzzfpz.zf_item_amt );
564
565 fetch cursor_q_vk3_zfzxzzfpz into v_row_q_vk3_zfzxzzfpz;/*先让指针指向结果集中的第一行，并将值保存到v_row_zfzxz_zfpz 中*/
566 exit when cursor_q_vk3_zfzxzzfpz%notfound or cursor_q_vk3_zfzxzzfpz%notfound is null;
567 end loop;
568 close cursor_q_vk3_zfzxzzfpz;/*关闭游标*/
569
570
571
572
573 end if;
574 v_sql :=' select gk_zwvch_refer_temp_pch as pch, gk_zfpz_id, make_date, fk_account, fk_bank, src_id, sk_name,
575 sk_account, sk_bank, unitno, unit_name, budget_type, bdgt_accid, budgetno, budget_name, prjno, prj_name,
576 zfpz_amt, amt, purpose, remark, checker, checker_date, affirmer, affirm_date, voucher_type, check_status,
577 wf_status, gk_hzqsd_id, fund, org_type, back_oper_id, back_oper_idea, back_oper_date, back_checker_id,
578 back_checker_idea, back_checker_date, zf_item_amt, zf_itemno, zf_itemname, bm_itemnos
579 from gk_zwentry_k3_zfzxz_zfpz_temp z ';
580
581 OPEN v_cur FOR v_sql;
582
583 commit;
584
585 end PRO_K3_ZFZXZ;
586
587
588 /
589改进版的存储过程
1 @Transactional(propagation=Propagation.REQUIRES_NEW )
2 public List getGkzfpz_JjflList_By_Pro(DaoFactory daoFactory, List lstZfpzId,ZwvchVO zwvchvo ){
3 List<Map<String, Object>> lstZfpz = new ArrayList();
4 try {
5
6 /**
7 * 调用储存过程 PRO_K3_ZFZXZ 返回合并查询结果
8 */
9 final String org_type=zwvchvo.getOrg_type();
10 final String opertype=zwvchvo.getOpertype();
11 SimpleDateFormat formatter=new SimpleDateFormat("yyyy-MM-dd");
12 final String fdate= formatter.format(zwvchvo.getFdate());
13
14 List resultList = (List) daoFactory.getDao().getJdbcTemplate().execute(
15 new CallableStatementCreator() {
16 public CallableStatement createCallableStatement(Connection con) throws SQLException {
17
18 String storedProc = "{call PRO_K3_ZFZXZ(?,?,?,?)}";// 调用的sql
19 CallableStatement cs = con.prepareCall(storedProc);
20 cs.setString (1, org_type);// 设置输入参数的值 org_type 资金来源类型 0 预算内 1预算外
21 cs.setString (2, opertype);// 设置输入参数的值 opertype 操作类型 0' :'正常对碰的直接支付' 1 '正常对碰的授权支付' 2 :'退款的直接支付' 3 :'退款的授权支付' 4 :'已审结的调账申请'
22 cs.setString (3, fdate);// 设置输入参数的值 fdate 凭证回单日期
23 cs.registerOutParameter(4, OracleTypes.CURSOR);// 注册输出参数的类型
24 return cs;
25 }
26 }, new CallableStatementCallback() {
27 public Object doInCallableStatement(CallableStatement cs) throws SQLException,DataAccessException {
28 List resultsMap = new ArrayList();
29 cs.execute();
30 ResultSet rs = (ResultSet) cs.getObject(4);// 获取游标一行的值
31 /*因为没有办法获取存储过程的回调结果。所以只好全部注释掉不要了。重新执行查询sql查询出结果*/
32 while (rs.next()) {// 转换每行的返回值到Map中
33 Map<String, Object> rowMap = new HashMap();
34 rowMap.put("pch", rs.getString("pch"));
35 rowMap.put("unitno", rs.getString("unitno"));
36 rowMap.put("unit_name", rs.getString("unit_name"));
37 rowMap.put("budget_type", rs.getString("budget_type"));
38 rowMap.put("bdgt_accid", rs.getString("bdgt_accid"));
39 rowMap.put("budgetno", rs.getString("budgetno"));
40 rowMap.put("prjno", rs.getString("prjno"));
41 rowMap.put("zfpz_amt", rs.getString("zfpz_amt"));
42 rowMap.put("amt", rs.getString("amt"));
43 rowMap.put("purpose", rs.getString("purpose"));
44 rowMap.put("voucher_type", rs.getString("voucher_type"));
45 rowMap.put("src_id", rs.getString("src_id"));
46 rowMap.put("org_type", rs.getString("org_type"));
47 rowMap.put("zf_item_amt", rs.getString("zf_item_amt"));
48 rowMap.put("zf_itemno", rs.getString("zf_itemno"));
49 rowMap.put("zf_itemname", rs.getString("zf_itemname"));
50 rowMap.put("bm_itemnos", rs.getString("bm_itemnos"));
51
52
53 resultsMap.add(rowMap);
54 }
55 rs.close();
56 return resultsMap;
57 }
58 });
59 for (int i = 0; i < resultList.size(); i++) {
60 Map<String, Object> rowMap = (Map) resultList.get(i);
61 lstZfpz.add(rowMap);
62 }
63
64 } catch (Exception e) {
65 e.printStackTrace();
66 }
67 return lstZfpz;
68 }1 create or replace procedure PRO_K3_ZFZXZ(org_type in varchar2,
2 opertype in varchar2,
3 fdate in varchar2,
4 v_cur out pkg_query.cur_query) is
5
6
7 v_zfzxz_zfpz_sql varchar2(4000) := '';
8
9 /*查询 view_k3_zfzxz_zfpz 的具体信息 */
10
11 v_q_vk3_zfzxzzfpz_sql varchar2(4000) := '';
12
13
14 v_zfpzId_sql varchar2(1000) := '';
15 v_pch number :=0;/*批次号*/
16
17 begin
18 select nextid('GK_ZWVCH_REFER_TEMP_SEQ') into v_pch from dual;
19
20
21 --先判断入参是否有值，如果没有值 整个存储过程返回空值
22 if org_type is not null and opertype is not null and fdate is not null then
23 /* org_type 资金来源类型 0 预算内 1预算外
24 opertype 操作类型 0' :'正常对碰的直接支付' 1 '正常对碰的授权支付' 2 :'退款的直接支付' 3 :'退款的授权支付' 4 :'已审结的调账申请'
25 fdate 凭证回单日期 */
26
27 if org_type = '1' /*预算外*/
28 then
29
30 if opertype = '0' then
31 --正常对碰的直接支付
32 v_zfpzId_sql := 'and z.id in ( select gz.id from gk_zfpz gz,bs_zjly zjly where gz.voucher_type =''''0'''' and gz.check_status in ( ''1'',''2'',''02'' ) and gz.src_id=zjly.fundno
33
34 and zjly.fundno in(''043'', ''058'', ''158'', ''258'', ''013'', ''113'', ''213'', ''023'',''123'',''223'', ''046'',''246'',''049'',''149'',''249'',''048'', ''248'',''045'',''245'',''047'',''247'', ''055'',''355'',''455'',''155'',''255'', ''059'', ''159'', ''259'', ''061'', ''161'',''261'', ''026'', ''044'',''144'', ''244'' )
35 and not exists (select r.* from gk_zwvch_refer r where r.origin_teable =''GK_ZFPZ'' and r.orign_id = gz.id and r.fmodule = ''2'' and r.voucher_type=''0'' and r.orign_type=''1'' )
36 and to_char(gz.affirm_date,''yyyy-MM-dd'') = ''' ||
37 fdate || ''' ) ';
38
39 elsif opertype = '1' then
40 --正常对碰的授权支付
41 v_zfpzId_sql := 'and z.id in ( select gz.id from gk_zfpz gz,bs_zjly zjly where gz.voucher_type =''1'' and gz.check_status in ( ''1'',''2'',''02'' ) and gz.src_id=zjly.fundno
42 and zjly.fundno in(''043'', ''058'', ''158'', ''258'', ''013'', ''113'', ''213'', ''023'',''123'',''223'', ''046'',''246'',''049'',''149'',''249'',''048'', ''248'',''045'',''245'',''047'',''247'', ''055'',''355'',''455'',''155'',''255'', ''059'', ''159'', ''259'', ''061'', ''161'',''261'', ''026'', ''044'',''144'', ''244'' )
43 and not exists (select r.* from gk_zwvch_refer r where r.origin_teable =''GK_ZFPZ'' and r.orign_id = gz.id and r.fmodule = ''2'' and r.voucher_type=''1'' and r.orign_type=''1'' )
44 and to_char(gz.affirm_date,''yyyy-MM-dd'') = ''' ||
45 fdate || ''' ) ';
46
47 elsif opertype = '2' then
48 --全额退款的直接支付
49 v_zfpzId_sql := 'and z.id in ( select gz.id from gk_zfpz gz,bs_zjly zjly where gz.voucher_type =''''0'''' and gz.check_status in (''2'') and gz.src_id=zjly.fundno
50 and zjly.fundno in( ''043'',''058'', ''158'', ''258'', ''013'', ''113'', ''213'', ''023'',''123'',''223'', ''046'',''246'',''049'',''149'',''249'',''048'', ''248'',''045'',''245'',''047'',''247'', ''055'',''355'',''455'',''155'',''255'', ''059'', ''159'', ''259'', ''061'', ''161'',''261'', ''026'', ''044'',''144'', ''244'' )
51 and not exists (select r.* from gk_zwvch_refer r where r.origin_teable =''GK_ZFPZ'' and r.orign_id = gz.id and r.fmodule = ''2'' and r.voucher_type=''0'' and r.orign_type=''3'' )
52 and to_char(gz.affirm_date,''yyyy-MM-dd'') = ''' ||
53 fdate || ''' ) ';
54
55 elsif opertype = '3' then
56 -- 全额退款的授权支付
57 v_zfpzId_sql := 'and z.id in ( select gz.id from gk_zfpz gz,bs_zjly zjly where gz.voucher_type =''1'' and gz.check_status =''2'' and gz.src_id=zjly.fundno
58 and zjly.fundno in( ''043'',''058'', ''158'', ''258'', ''013'', ''113'', ''213'', ''023'',''123'',''223'', ''046'',''246'',''049'',''149'',''249'',''048'', ''248'',''045'',''245'',''047'',''247'', ''055'',''355'',''455'',''155'',''255'', ''059'', ''159'', ''259'', ''061'', ''161'',''261'', ''026'', ''044'',''144'', ''244'' )
59 and not exists (select r.* from gk_zwvch_refer r where r.origin_teable =''GK_ZFPZ'' and r.orign_id = gz.id and r.fmodule = ''2'' and r.voucher_type=''1'' and r.orign_type=''3'' )
60 and to_char(gz.affirm_date,''yyyy-MM-dd'') = ''' ||
61 fdate || ''' ) ';
62
63 end if;
64
65 elsif org_type = '0' /*预算内 */
66 then
67 /* org_type 资金来源类型 0 预算内 1预算外
68 opertype 操作类型 0'' :''正常对碰的直接支付'' 1 ''正常对碰的授权支付'' 2 :''退款的直接支付'' 3 :''退款的授权支付'' 4 :''已审结的调账申请''
69 fdate 凭证回单日期 */
70 if opertype = 0 then
71 --正常对碰的直接支付
72 v_zfpzId_sql := 'and z.id in (
73 select gz.id from gk_zfpz gz,bs_zjly zjly
74 where gz.voucher_type =''0'' and gz.check_status in ( ''1'',''2'',''02'' ) and gz.src_id=zjly.fundno and zjly.org_type=1 and zjly.fundno not in (''007'',''107'',''207'')
75 and not exists (select r.* from gk_zwvch_refer r where r.origin_teable =''GK_ZFPZ'' and r.orign_id = gz.id and r.fmodule = ''1'' and r.voucher_type=''0'' and r.orign_type=''1'' )
76 and to_char(gz.affirm_date,''yyyy-MM-dd'') = ''' ||
77 fdate || ''' ) ';
78
79 elsif opertype = '1' then
80 -- 正常对碰的授权支付
81 v_zfpzId_sql := 'and z.id in (
82 select gz.id from gk_zfpz gz,bs_zjly zjly
83 where gz.voucher_type =''1'' and gz.check_status in ( ''1'',''2'',''02'' ) and gz.src_id=zjly.fundno and zjly.org_type=1 and zjly.fundno not in (''007'',''107'',''207'')
84 and not exists (select r.* from gk_zwvch_refer r where r.origin_teable =''GK_ZFPZ'' and r.orign_id = gz.id and r.fmodule = ''1'' and r.voucher_type=''0'' and r.orign_type=''1'' )
85 and to_char(gz.affirm_date,''yyyy-MM-dd'') = ''' ||
86 fdate || ''' ) ';
87
88 elsif opertype = '2' then
89 -- 全额退款的直接支付
90 v_zfpzId_sql := 'and z.id in (
91 select gz.id from gk_zfpz gz,bs_zjly zjly where gz.voucher_type =''0'' and gz.check_status =''2'' and gz.src_id=zjly.fundno and zjly.org_type=1 and zjly.fundno not in (''007'',''107'',''207'')
92 and not exists (select r.* from gk_zwvch_refer r where r.origin_teable =''GK_ZFPZ'' and r.orign_id = gz.id and r.fmodule = ''1'' and r.voucher_type=''0'' and r.orign_type=''3'' )
93 and to_char(gz.affirm_date,''yyyy-MM-dd'') = ''' ||
94 fdate || ''' ) ';
95
96 elsif opertype = '3' then
97 ---全额退款的授权支付
98 v_zfpzId_sql := 'and z.id in (
99 select gz.id from gk_zfpz gz,bs_zjly zjly where gz.voucher_type =''1'' and gz.check_status =''2'' and gz.src_id=zjly.fundno and zjly.org_type=1 and zjly.fundno not in (''007'',''107'',''207'')
100 and not exists (select r.* from gk_zwvch_refer r where r.origin_teable =''GK_ZFPZ'' and r.orign_id = gz.id and r.fmodule = ''1'' and r.voucher_type=''1'' and r.orign_type=''3'' )
101 and to_char(gz.affirm_date,''yyyy-MM-dd'') = ''' ||
102 fdate || ''' ) ';
103 end if;
104
105 end if;
106
107 v_zfzxz_zfpz_sql :=
108
109
110
111 'select '|| v_pch || ' as pch,
112 z.unitno
113
114 ,z.unit_name,
115 z.budget_type,
116 z.bdgt_accid,
117 z.budgetno,
118 z.prjno
119 ,sum(z.zfpz_amt) zfpz_amt,
120 sum(z.amt) amt,
121 z.purpose ,
122 z.voucher_type,
123 z.src_id src_id ,
124 z.org_type,
125 sum(z.zf_item_amt) zf_item_amt,
126 z.zf_itemno
127 ,max(z.zf_itemname) zf_itemname,
128 max(z.bm_itemnos) bm_itemnos
129
130 from
131 view_k3_zfzxz_zfpz z where 1=1 ' || v_zfpzId_sql ||
132 ' group by z.unitno,
133 z.unit_name,
134 z.prjno,
135 z.src_id,
136 z.budget_type,
137 z.budgetno,
138 z.bdgt_accid,
139 z.purpose,
140 z.prjno,
141 z.zf_itemno
142 ,z.org_type,
143 z.voucher_type ' ;
144
145 dbms_output.put_line(v_zfzxz_zfpz_sql);
146
147 OPEN v_cur FOR v_zfzxz_zfpz_sql;
148
149 v_q_vk3_zfzxzzfpz_sql :=
150
151 'insert into gk_zwvch_refer(FSERIALNUM, orign_type,orign_id,voucher_type,origin_teable,fmodule,ZF_ITEMNAME)
152 select ''9999999999'',
153 ''1'' ORIGN_TYPE,
154 z.id ORIGN_ID,
155 z.voucher_type VOUCHER_TYPE,
156 ''GK_ZFPZ'' ORIGIN_TEABLE,
157 ''1'' FMODULE,
158 '||v_pch||'
159 from gk_zfpz z
160 where 1 = 1 ' || v_zfpzId_sql;
161
162 execute immediate v_q_vk3_zfzxzzfpz_sql;
163
164
165
166
167 end if;
168
169 --close cursor_q_vk3_zfzxzzfpz_count;/*关闭游标*/
170 --close cursor_q_vk3_zfzxzzfpz;/*关闭游标*/
171 --close cursor_zfzxz_zfpz;/*关闭游标*/
172
173 /*
174 v_sql :=' select gk_zwvch_refer_temp_pch as pch, gk_zfpz_id, make_date, fk_account, fk_bank, src_id, sk_name,
175 sk_account, sk_bank, unitno, unit_name, budget_type, bdgt_accid, budgetno, budget_name, prjno, prj_name,
176 zfpz_amt, amt, purpose, remark, checker, checker_date, affirmer, affirm_date, voucher_type, check_status,
177 wf_status, gk_hzqsd_id, fund, org_type, back_oper_id, back_oper_idea, back_oper_date, back_checker_id,
178 back_checker_idea, back_checker_date, zf_item_amt, zf_itemno, zf_itemname, bm_itemnos
179 from gk_zwentry_k3_zfzxz_zfpz_temp z ';*/
180
181
182
183
184 end PRO_K3_ZFZXZ;
185
——————————————————————————————————————————————————————————————————————————————————————————————————
jdbctemplate 调用oracle 有返回(会话型临时表数据的)结果的存储过程的更多相关文章
-
C# ODP.NET 调用Oracle函数返回值时报错的一个解决方案
有人在社区问到:C#调用Oracle中自定义函数的返回值时,无法正常调用.但在PL/SQL中正常调用返回. 于是动手一试: 1.准备函数(Oralce 11g.2.0.0.4) CREATE OR R ...
-
spring jdbctemplate调用procedure（返回游标）
package cn.com.git.htsc.uac.core.repository.report; import cn.com.git.htsc.uac.core.api.dto.report.R ...
-
oracle pipelined返回值函数 针对数据汇总统计 返回结果集方法
近期需要一个汇总统计,由于数据太多,数据量太大所以在java程序中实现比较困难.若用后台程序统计,数据不能保证实时,同时实现周期比较长.顾使用函数返回结果集的方式,在不增加临时表的情况下实时获取数据. ...
-
Spring如何使用JdbcTemplate调用存储过程的三种情况
注:原文 <Spring如何使用JdbcTemplate调用存储过程的三种情况 > Spring的SimpleJdbcTemplate将存储过程的调用进行了良好的封装,下面列出使用Jdbc ...
-
Java调用oracle存储过程通过游标返回临时表数据
注:本文来源于 < Java调用oracle存储过程通过游标返回临时表数据 > Java调用oracle存储过程通过游标返回临时表数据 项目开发过程中,不可避免的会用到存储过程返回结 ...
-
spring jdbctemplate调用存储过程，返回list对象
注:本文来源于< spring jdbctemplate调用存储过程,返回list对象 > spring jdbctemplate调用存储过程,返回list对象 方法: /** * 调用 ...
-
jdbcTemplate 调用存储过程。 入参 array 返回 cursor
注:本文来源< jdbcTemplate 调用存储过程. 入参 array 返回 cursor > 需求: java传入一个list object.从数据库找到相关的数据并返回. ...
-
123 c#调用oracle存储过程返回数据集 --通过oracle存储过程返回数据集
c#调用oracle存储过程返回数据集 2008-12-20 10:59:57| 分类: net|字号 订阅 CREATE OR REPLACE PACKAGE pkg_tableTypeIS ...
-
Oracle创建表语句(Create table)语法详解及示例、、 C# 调用Oracle 存储过程返回数据集 实例
Oracle创建表语句(Create table)语法详解及示例 2010-06-28 13:59:13| 分类: Oracle PL/SQL|字号 订阅 创建表(Create table)语法详解 ...
随机推荐
-
剑指Offer面试题：19.包含Min函数的栈
一.题目:包含Min函数的栈 题目:定义栈的数据结构,请在该类型中实现一个能够得到栈的最小元素的min函数.在该栈中,调用min.push及pop的时间复杂度都是O(1). 这里我们要实现的就是min ...
-
Browsersync — 省时的浏览器同步测试工具
Browsersync能让浏览器实时.快速响应您的文件更改(html.js.css.sass.less等)并自动刷新页面.更重要的是 Browsersync可以同时在PC.平板.手机等设备下进项调试. ...
-
iOS - 如何自动播放H5中的音频
场景:iOS端设备,App页面跳转到H5产品介绍,背景音乐无法播放.(为什么不能自动播放,因该是iPhone人性化设定吧~) 加载H5用UIWebView空间: 代码: CGRect rect = s ...
-
Redis入门（优势，环境，字符串，哈希，列表）
Redis从它的许多竞争继承来的三个主要特点: Redis数据库完全在内存中,使用磁盘仅用于持久性. 相比许多键值数据存储,Redis拥有一套较为丰富的数据类型. Redis可以将数据复制到任意数量的 ...
-
文件和目录之mkdir和rmdir函数
用mkdir函数创建目录,用rmdir函数删除目录. #include <sys/stat.h> int mkdir( const char *pathname, mode_t mode ...
-
安装supervisord
一:简介 supervisord是一个进程管理工具,提供web页面管理,能对进程进行自动重启等操作. 优点: - 可以将非后台运行程序后台运行 - 自动监控,重启进程 缺点: - 不能管理后台运行程序 ...
-
关于transform的3D变形函数
继续transform的3D用法: translate3d(x,y,z)定义3D转换 transformX(x)只用x轴的值进行转换: transformY(y)只用y轴的值进行转换: transfo ...
-
Java学习笔记：多线程（二）
与线程生命周期相关的方法: sleep 调用sleep方法会进入计时等待状态,等待时间到了,进入就绪状态. yield 调用yield方法会让别的线程执行,但是不确保真正让出.较少使用,官方注释都说 ...
-
HNOI2017 游记
如果你要问我为什么现在才发出来,那是因为我太懒了 Day0: 日常看板子……不想写题,嘴巴了几道题之后也不想写…… 到了晚上颓起来了……回想了一下似乎也没有立什么flag,那就愉快地颓吧……深感技术下 ...
-
2015.8.2js-19(完美运动框架)
/*完美运动框架*/ //1.先清除定时期,2,获取样式,如果是opacity则单独解决,3,定义速度,4,定义当前值是否到达目的地,5,判断当前值是否到达目的地,6运动基本,如果是opacity f ...