CREATE OR REPLACE PACKAGE FM_UTILITY IS
-- Public type declarations
TYPE RCURSOR IS REF CURSOR;
FUNCTION CHECK_ELEMENTCODE(ICNT OUT NUMBER) RETURN VARCHAR2;
PROCEDURE trans_elecode(iAcctsystypeid NUMBER);
PROCEDURE update_fmvoucher_program;
PROCEDURE save_fmvoucher(iAcctsystypeid NUMBER,iUserid NUMBER);
PROCEDURE update_fmvoucher_acctseqAndSub(iAcctsystypeid NUMBER);
PROCEDURE delete_fmvoucher(iAcctsystypeid NUMBER);
FUNCTION check_fmvoucher_vchno RETURN VARCHAR2;
FUNCTION check_fmvoucher_dc RETURN VARCHAR2;
FUNCTION check_fmvoucher_dcamt(sVchid OUT varchar2) RETURN VARCHAR2;
PROCEDURE update_fmvoucher_bsfeedback(iAcctsystype NUMBER);
PROCEDURE update_fmvoucher_user(iAcctsystype NUMBER);
PROCEDURE update_fmvoucher_vchno(iAcctsystypeid NUMBER);
PROCEDURE save_fmvoucher4Poste(iAcctsystype NUMBER);
FUNCTION check_bsdata_And_vch(iAcctsystype NUMBER)RETURN varchar2;
PROCEDURE update_fmvch_acctseqAndSub2(iAcctsystypeid NUMBER,iVchid NUMBER);
PROCEDURE undoFmVch(iAcctsystype NUMBER,iOpertype NUMBER);
END FM_UTILITY;
/
CREATE OR REPLACE PACKAGE BODY FM_UTILITY IS
FUNCTION CHECK_ELEMENTCODE(ICNT OUT NUMBER) RETURN VARCHAR2 IS
iClnCnt number(5);
SDATATABLECODE VARCHAR2(50);
SCOLUMNCODE VARCHAR2(20);
SELEMENTCODE VARCHAR2(20);
RELEMENTCODE RCURSOR;
SELEMENTTABLECODE VARCHAR2(50);
SSQL VARCHAR2(500);
BEGIN
SELECT COUNT(*) INTO iClnCnt FROM TMP_IMPORT_CLN;
IF iClnCnt <= 0 THEN
RETURN '';
END IF;
SELECT A1.TABLECODE
INTO SDATATABLECODE
FROM TMP_IMPORT_CLN A1
WHERE ROWNUM < 2;
OPEN RELEMENTCODE FOR
SELECT A1.COLUMNCODE, A1.ELEMENTCODE, A1.ELEMENTTABLECODE
FROM TMP_IMPORT_CLN A1
WHERE A1.ISELEMENT = 1;
LOOP
FETCH RELEMENTCODE
INTO SCOLUMNCODE, SELEMENTCODE, SELEMENTTABLECODE;
EXIT WHEN RELEMENTCODE%NOTFOUND;
SSQL := 'SELECT COUNT(*) FROM ' || SDATATABLECODE || ' a1 WHERE ' ||
' a1.' || SCOLUMNCODE || ' IS NOT NULL AND NOT EXISTS' ||
' (SELECT * FROM ' || SELEMENTTABLECODE || ' a2 WHERE a1.' ||
SCOLUMNCODE || ' =a2.code)';
EXECUTE IMMEDIATE SSQL
INTO ICNT;
IF ICNT > 0 THEN
RETURN SELEMENTCODE;
END IF;
END LOOP;
RETURN '';
END;
PROCEDURE trans_elecode(iAcctsystypeid NUMBER)
IS
BEGIN
--DELETE FROM tmp_hisdata_import2;
INSERT INTO tmp_hisdata_import2(tmp_id,acctsystype,term,acctdate,vchattr,
vchcls, vchno, digest, account, dc, amt,
bdgagency,func,expeconormic,fundtype,paytype--,program
,creater,auditor,poster,status)
SELECT tmp_id,iAcctsystypeid, a2.termno,to_char(to_date(a1.acctdate,'yyyy-MM-dd'),'yyyyMMdd'),
CASE WHEN a1.vchattr > 0 THEN 6 ELSE 1 END vchattr,a4.vchclsid,a1.vchno,a1.digest,
a3.acctid,CASE WHEN a1.dc ='C' THEN -1 ELSE 1 END dc,a1.amt,
bdgagency,func,expeconormic,fundtype,paytype--,program
--a1.bdgagency,a1.func,a1.EXPECONORMIC,a1.fundtype,a1.paytype,a1.program
,creater,auditor,poster,a1.status
FROM (SELECT a1.tmp_id, a1.acctsystype,term,acctdate,vchattr,
vchcls, vchno, digest, account, dc, amt,
t1.itemid bdgagency,t2.itemid func,t3.itemid expeconormic,
t4.itemid fundtype,t5.itemid paytype--,t6.itemid program
,t7.userid creater,t8.userid auditor,t9.userid poster
,a1.status
FROM tmp_hisdata_import a1
LEFT JOIN t_pubagency t1 ON a1.bdgagency =t1.code
LEFT JOIN t_pubfunc t2 ON a1.func =t2.code
LEFT JOIN t_pubexpeconormic t3 ON a1.expeconormic =t3.code
LEFT JOIN t_pubfundtype t4 ON a1.fundtype =t4.code
LEFT JOIN t_pubpaytype t5 ON a1.paytype =t5.code
--LEFT JOIN t_pubprogram t6 ON a1.program =t6.code
LEFT JOIN t_causer t7 ON a1.creater =t7.code
LEFT JOIN t_causer t8 ON a1.auditor =t8.code
LEFT JOIN t_causer t9 ON a1.poster =t9.code
) a1,t_fmterm a2,t_fmaccount a3,t_fmvouchercls a4
WHERE to_date(a1.term,'yyyy-MM-dd') BETWEEN a2.startdate AND a2.enddate
AND a3.acctsystypeid =iAcctsystypeid AND a1.account =a3.acctcode
AND a4.acctsystypeid =iAcctsystypeid AND a1.vchcls =a4.vchclsname;
--a1.bdgagency,a1.func,a1.EXPECONORMIC,a1.fundtype,a1.paytype,a1.program
UPDATE tmp_hisdata_import2 a1 SET a1.bdgagency =0 WHERE a1.bdgagency IS NULL;
UPDATE tmp_hisdata_import2 a1 SET a1.func =0 WHERE a1.func IS NULL;
UPDATE tmp_hisdata_import2 a1 SET a1.expeconormic =0 WHERE a1.expeconormic IS NULL;
UPDATE tmp_hisdata_import2 a1 SET a1.fundtype =0 WHERE a1.fundtype IS NULL;
UPDATE tmp_hisdata_import2 a1 SET a1.paytype =0 WHERE a1.paytype IS NULL;
UPDATE tmp_hisdata_import2 a1 SET a1.program =0 WHERE a1.program IS NULL;
COMMIT;
END;
PROCEDURE save_fmvoucher(iAcctsystypeid NUMBER,iUserid NUMBER)
IS
iCyid NUMBER(9);
iByVchcls NUMBER(1);
sSQL varchar2(1000);
rGLvch rCursor;
iTerm NUMBER(9);
iVchno NUMBER(9);
BEGIN
SELECT cyid INTO iCyid FROM t_fmcurrency a1 WHERE a1.isstandard =1;
INSERT INTO t_glvoucher(vchid,acctsystype,vchno,vchattr,vchclsid,acctyear,term,acctdate,
creater,createtime,status,appenum,TIMESTAMP
,newvchid,billid,groupno,logid,autovchno,auditor,poster,chargeoff
)
SELECT s_glvoucher.nextval,t.* FROM(
SELECT DISTINCT iAcctsystypeid,vchno,vchattr,vchcls,
(SELECT a1.paramdata FROM t_pubsystemset a1 WHERE a1.paramcode ='acctyear' OR a1.paramcode ='ACCTYEAR'),
term,acctdate,iUserid,to_char(Sysdate,'yyyymmdd'),0 status,0 appenum,1 TIMESTAMP
,0 newvchid,0 billid,0 groupno,0 logid,0 autovchno,0 auditor,0 poster,0 chargeoff
FROM tmp_hisdata_import2) t;
OPEN rGLvch FOR SELECT term,vchno FROM t_glvoucher WHERE acctsystype=iAcctsystypeid ORDER BY term,vchno;
LOOP
FETCH rGLvch INTO iTerm,iVchno;
EXIT WHEN rGLvch%NOTFOUND;
INSERT INTO t_glrecord(recid,vchid,acctyear,term,acctdate,remark,digest,acctsystype,
dc,amt,cyamt,rate,userid,
ACCOUNT,fundtype,bdgagency,expfunc,expeconormic,program,paytype,
text9,text10,
coaid,ctrlid,ctrltypeid,linkctrlid,vchtypeid,billid,vchidsrc,Newctrl,currency
,objectid,detailaccount,payno
,element01,element02,element03,element04,element05,element06,element07,element08,element09,element10 ,element11,element12,element13,element14,element15,element16,element17,element18,element19,element20,
ACCTSEQ,ACCTSEQSUB,INCOME,PROGRAMTYPE,BDGVERSION,DEPARTMENTDIVISION,
BDGMANAGEDIVISION,EXECUTEMETHOD,AGENTBANK,BDGALLOW,MAINBODY,BUSINESSTYPE,
FUNDSOURCE,SUPPLIER,CHARGINGINSTITUTION,ADMCHARGE,PROPORLEVEL,GATHERINGBANKACCOUNT,
PAYMENTBANKACCOUNT,SETTLEMODE,BDGLEVEL,INCOMEEXPMANAGE,BDGYEAR,MONTH,BDGDOCNO,MANAGEMENTTYPE,INDEXLEVEL)
SELECT s_glrecord.nextval,a1.vchid,a1.acctyear,a1.term,a1.acctdate,a2.digest,a2.digest,a1.acctsystype,
a2.dc,a2.amt,a2.amt,1.0,a1.creater,
a2.account,a2.fundtype,a2.bdgagency,a2.func,a2.expeconormic,a2.program,a2.paytype,
a1.term,a1.vchno,
--other
0,0,0,0,0,0,0,0,iCyid,a2.tmp_id,0,0,
--element
0,0,0,0,0,0,0,0,0,0,
0,0,0,0,0,0,0,0,0,0,
0,0,0,0,0,0,
0,0,0,0,0,0,
0,0,0,0,0,0,
0,0,0,0,0,0,0,0,0
FROM t_glvoucher a1,tmp_hisdata_import2 a2
WHERE a1.acctsystype=iAcctsystypeid AND a1.term =a2.term AND a1.vchno =a2.vchno
AND a1.term =iTerm AND a1.vchno =iVchno;
COMMIT;
END LOOP;
UPDATE t_glrecord a1 SET element20 =(
SELECT acctid FROM t_fmaccount WHERE superid =0
START WITH acctid=a1.account
CONNECT BY PRIOR superid=acctid)
WHERE a1.acctsystype =iAcctsystypeid;
SELECT vchnobycls INTO iByVchcls FROM t_fmaccountsystemtype WHERE acctsystypeid=iAcctsystypeid;
sSQL :='INSERT INTO t_glvchno(ID,ACCTSYSTYPEID,VCHCLSID,ACCTYEAR,TERM,CURVCHNO)'||
' SELECT s_glvchno.nextval,t.* FROM('||
' SELECT acctsystype,';
IF iByVchcls = 1 THEN
sSQL :=sSQL ||'vchclsid,';
ELSE
sSQL :=sSQL ||'0,';
END IF;
sSQL :=sSQL ||' acctyear,term,max(vchno)'||
' FROM t_glvoucher WHERE acctsystype='||iAcctsystypeid ||
' GROUP BY acctsystype,acctyear,term,vchclsid) t';
EXECUTE IMMEDIATE sSQL;
COMMIT;
END;
PROCEDURE update_fmvoucher_vchno(iAcctsystypeid NUMBER)
IS
iByVchcls NUMBER(9);
sSQL varchar2(1000);
BEGIN
SELECT vchnobycls INTO iByVchcls FROM t_fmaccountsystemtype WHERE acctsystypeid=iAcctsystypeid;
sSQL :='INSERT INTO t_glvchno(ID,ACCTSYSTYPEID,VCHCLSID,ACCTYEAR,TERM,CURVCHNO)'||
' SELECT s_glvchno.nextval,t.* FROM('||
' SELECT acctsystype,';
IF iByVchcls = 1 THEN
sSQL :=sSQL ||'vchclsid,';
ELSE
sSQL :=sSQL ||'0,';
END IF;
sSQL :=sSQL ||' acctyear,term,max(vchno)'||
' FROM t_glvoucher WHERE acctsystype='||iAcctsystypeid ||
' GROUP BY acctsystype,acctyear,term,vchclsid) t';
EXECUTE IMMEDIATE sSQ
L;
COMMIT;
END;
PROCEDURE update_fmvoucher_acctseq(iAcctsystypeid NUMBER)
IS
rTmp rCursor;
iTerm NUMBER(2);
iVchno NUMBER(9);
iDc NUMBER(2);
iAcct NUMBER(9);
iTmpTerm NUMBER(2);
iTmpVchno NUMBER(9);
iTmpAcctseq NUMBER(9);
BEGIN
iTmpTerm :=0;
iTmpVchno :=0;
OPEN rTmp FOR SELECT a1.term,to_number(a1.text10) vchno,
a1.dc,a1.element20
FROM t_glrecord a1
WHERE a1.acctsystype =iAcctsystypeid
GROUP BY a1.term,to_number(a1.text10),a1.element20,a1.dc
ORDER BY term,vchno,dc DESC;
LOOP
FETCH rTmp INTO iTerm,iVchno,iDc,iAcct;
EXIT WHEN rTmp%NOTFOUND;
IF iTmpTerm <> iTerm OR iTmpVchno <> iVchno THEN
iTmpAcctseq :=1;
ELSE
iTmpAcctseq := iTmpAcctseq + 1;
END IF;
iTmpTerm :=iTerm;
iTmpVchno :=iVchno;
UPDATE t_glrecord a1 SET a1.acctseq =iTmpAcctseq
WHERE a1.term =iTmpTerm AND a1.text10 =to_char(iTmpVchno)
AND a1.dc =iDc AND a1.element20 =iAcct;
--dbms_output.put_line('term='||iTmpTerm||' vchno='||iTmpVchno||' account='||iAcct);
COMMIT;
END LOOP;
END;
PROCEDURE update_fmvoucher_acctseqsub(iAcctsystypeid NUMBER)
IS
rTmp rCursor;
iTerm NUMBER(2);
iVchno NUMBER(9);
iAcctseq NUMBER(2);
iTmpTerm NUMBER(2);
iTmpVchno NUMBER(9);
iTmpAcctseq NUMBER(9);
iTmpAcctseqsub NUMBER(9);
rTmpAcctseqsub rCursor;
iRecid NUMBER(9);
BEGIN
iTmpTerm :=0;
iTmpVchno :=0;
iTmpAcctseq :=0;
OPEN rTmp FOR SELECT a1.term,to_number(a1.text10) vchno,acctseq
FROM t_glrecord a1
WHERE a1.acctsystype =iAcctsystypeid
GROUP BY a1.term,to_number(a1.text10),acctseq
ORDER BY term,vchno;
LOOP
FETCH rTmp INTO iTerm,iVchno,iAcctseq;
EXIT WHEN rTmp%NOTFOUND;
IF iTmpTerm <> iTerm OR iTmpVchno <> iVchno OR iTmpAcctseq <> iAcctseq THEN
iTmpAcctseqsub :=0;
iTmpTerm :=iTerm;
iTmpVchno :=iVchno;
iTmpAcctseq :=iAcctseq;
END IF;
OPEN rTmpAcctseqsub FOR SELECT recid FROM t_glrecord a1
WHERE a1.term =iTmpTerm AND a1.text10 =to_char(iTmpVchno)
AND a1.acctseq =iTmpAcctseq;
LOOP
FETCH rTmpAcctseqsub INTO iRecid;
EXIT WHEN rTmpAcctseqsub%NOTFOUND;
iTmpAcctseqsub := iTmpAcctseqsub + 1;
UPDATE t_glrecord a1 SET a1.acctseqsub =iTmpAcctseqsub WHERE a1.recid =iRecid;
END LOOP;
COMMIT;
END LOOP;
END;
PROCEDURE update_fmvoucher_acctseqAndSub(iAcctsystypeid NUMBER)
IS
BEGIN
update_fmvoucher_acctseq(iAcctsystypeid);
update_fmvoucher_acctseqsub(iAcctsystypeid);
END;
PROCEDURE delete_fmvoucher(iAcctsystypeid NUMBER)
IS
BEGIN
DELETE FROM t_glrecord a1 WHERE EXISTS
(SELECT * FROM t_glvoucher a2 WHERE a2.acctsystype=iAcctsystypeid AND a1.vchid =a2.vchid);
DELETE FROM t_glvoucher a2 WHERE a2.acctsystype=iAcctsystypeid;
DELETE FROM t_glvchno;
COMMIT;
END;
FUNCTION check_fmvoucher_vchno RETURN varchar2
IS
rAcctsystype rCursor;
iAcctsystypeid NUMBER(9);
sAcctsystypecode VARCHAR2(20);
iCurrTerm NUMBER(9);
sHisVch varchar2(20);
iCnt NUMBER(9);
sSQL varchar2(200);
sList varchar2(4000);
BEGIN
iCnt :=0;
sSQL :='';
sList :='';
OPEN rAcctsystype FOR SELECT a1.acctsystypeid,a1.accsystypecode,a1.currenttermno,a1.historyvchtable
FROM t_fmaccountsystemtype a1 ORDER BY a1.accsystypecode;
LOOP
FETCH rAcctsystype INTO iAcctsystypeid,sAcctsystypecode,iCurrTerm,sHisVch;
EXIT WHEN rAcctsystype%NOTFOUND;
for i in 1..iCurrTerm
loop
SELECT COUNT(*) INTO iCnt FROM t_glvoucher WHERE status < 3 AND vchno <=0
AND term=iCurrTerm AND acctsystype =iAcctsystypeid;
IF iCnt > 0 THEN
sList :=sList ||sAcctsystypecode||','||i||';';
ELSE
sSQL := 'SELECT COUNT(*) FROM '||sHisVch||' WHERE status < 3 AND vchno <=0'||
' AND term=:iCurrTerm AND acctsystype=:iAcctsystypeid';
EXECUTE IMMEDIATE sSQL INTO icnt USING iCurrTerm,iAcctsystypeid;
IF iCnt > 0 THEN
sList :=sList ||sAcctsystypecode||','||i||';';
END IF;
END IF;
end loop;
END LOOP;
RETURN sList;
END;
FUNCTION check_fmvoucher_dc RETURN VARCHAR2
IS
rAcctsystype rCursor;
iAcctsystypeid NUMBER(9);
sAcctsystypecode VARCHAR2(20);
iCurrTerm NUMBER(9);
sHisVch varchar2(20);
sHisRec varchar2(20);
iCnt NUMBER(9);
sSQL varchar2(500);
sList varchar2(4000);
BEGIN
iCnt :=0;
sSQL :='';
sList :='';
OPEN rAcctsystype FOR SELECT a1.acctsystypeid,a1.accsystypecode,a1.currenttermno,a1.historyvchtable,a1.historyrectable
FROM t_fmaccountsystemtype a1 ORDER BY a1.accsystypecode;
LOOP
FETCH rAcctsystype INTO iAcctsystypeid,sAcctsystypecode,iCurrTerm,sHisVch,sHisRec;
EXIT WHEN rAcctsystype%NOTFOUND;
-- 如果当前表不存在，则查看历史表是否存在
for i in 1..iCurrTerm
LOOP
SELECT COUNT(*) INTO iCnt FROM t_glrecord a1 WHERE EXISTS(
SELECT * FROM t_glvoucher a2 WHERE status < 3 AND a1.vchid =a2.vchid
AND term=iCurrTerm AND acctsystype =iAcctsystypeid)
AND a1.dc =0;
IF iCnt > 0 THEN
sList :=sList ||sAcctsystypecode||','||i||';';
ELSE
sSQL := ' SELECT COUNT(*) FROM '||sHisRec||' a1 WHERE EXISTS('||
' SELECT * FROM '||sHisVch||' a2 WHERE status < 3 AND a1.vchid =a2.vchid'||
' AND term=:iCurrTerm AND acctsystype =:iAcctsystypeid)'||
' AND a1.dc =0';
EXECUTE IMMEDIATE sSQL INTO icnt USING iCurrTerm,iAcctsystypeid;
IF iCnt > 0 THEN
sList :=sList ||sAcctsystypecode||','||i||';';
END IF;
END IF;
end loop;
END LOOP;
RETURN sList;
END;
FUNCTION check_fmvoucher_dcamt(sVchid OUT varchar2) RETURN VARCHAR2
IS
rAcctsystype rCursor;
rDCAmt rcursor;
iAcctsystypeid NUMBER(9);
sHisVch varchar2(20);
sHisRec varchar2(20);
iCnt NUMBER(10);
sSQL varchar2(500);
iVchid NUMBER(9);
iAmt NUMBER(16,2);
sAmt varchar2(50);
sList varchar2(8000);
sHisList varchar2(4000);
sHisVchid varchar2(2000);
BEGIN
sList :='';
sSQL :='';
sVchid :='';
OPEN rAcctsystype FOR SELECT acctsystypeid,a1.historyrectable,a1.historyvchtable FROM t_fmaccountsystemtype a1 ORDER BY a1.accsystypecode;
LOOP
FETCH rAcctsystype INTO iAcctsystypeid,sHisRec,sHisVch;
EXIT WHEN rAcctsystype%NOTFOUND;
SELECT COUNT(*) INTO iCnt
FROM (SELECT vchid,dc,sum(amt) debit FROM t_glrecord a1 WHERE dc =1
AND a1.acctsystype =iAcctsystypeid
GROUP BY vchid,dc ORDER BY vchid) t1,
(SELECT vchid,dc,sum(amt)credit FROM t_glrecord a1 WHERE dc =-1
AND a1.acctsystype =iAcctsystypeid
GROUP BY vchid,dc ORDER BY vchid) t2
WHERE t1.vchid =t2.vchid AND t1.debit <> t2.credit;
IF iCnt > 0 THEN
--当前表
sSQL :='SELECT t1.vchid,(t1.debit- t2.credit) amt'||
' FROM (SELECT vchid,dc,sum(amt) debit FROM t_glrecord a1 WHERE dc =1'||
' AND EXISTS ('||
' SELECT * FROM t_glvoucher a2 WHERE a1.vchid =a2.vchid'||
' AND A2.ACCTSYSTYPE ='||iAcctsystypeid||')'||
' GROUP BY vchid,dc ORDER BY vchid) t1,'||
' (SELECT vchid,dc,sum(amt)credit FROM t_glrecord a1 WHERE dc =-1'||
' AND EXISTS ('||
' SELECT * FROM t_glvoucher a2 WHERE a1.vchid =a2.vchid'||
' AND A2.ACCTSYSTYPE ='||iAcctsystypeid||')'||
' GROUP BY vchid,dc ORDER BY vchid) t2'||
' WHERE t1.vchid =t2.vchid AND t1.debit <> t2.credit';
OPEN rDCAmt FOR sSQL;
LOOP
FETCH rDCAmt INTO iVchid,iAmt;
EXIT WHEN rDCAmt%NOTFOUND;
sList :=sList||'curr:'||iVchid||','||iAmt||';';
sVchid :=sVchid||iVchid ||',';
END LOOP;
ELSE
--历史表
sSQL :='SELECT t1.vchid,(t1.debit- t2.credit) amt'||
' FROM (SELECT vchid,dc,sum(amt) debit FROM '||sHisRec||' a1 WHERE dc =1'||
' AND EXISTS ('||
' SELECT * FROM '||sHisVch||' a2 WHERE a1.vchid =a2.vchid'||
' AND A2.ACCTSYSTYPE ='||iAcctsystypeid||')'||
' GROUP BY vchid,dc ORDER BY vchid) t1,'||
' (SELECT vchid,dc,sum(amt)credit FROM '||sHisRec||' a1 WHERE dc =-1'||
' AND EXISTS ('||
' SELECT * FROM '||sHisVch||' a2 WHERE a1.vchid =a2.vchid'||
' AND A2.ACCTSYSTYPE ='||iAcctsystypeid||')'||
' GROUP BY vchid,dc ORDER BY vchid) t2'||
' WHERE t1.vchid =t2.vchid AND t1.debit <> t2.credit';
OPEN rDCAmt FOR sSQL;
LOOP
FETCH rDCAmt INTO iVchid,iAmt;
EXIT WHEN rDCAmt%NOTFOUND;
sHisList :='his:'||iVchid ||','||iAmt||';';
sHisVchid :=sHisVchid||iVchid ||',';
END LOOP;
END IF;
END LOOP;
sVchid :=sVchid ||sHisVchid;
RETURN sList||sHisList;
END;
--保存对应关系
PROCEDURE save_bsfeedback(iAcctsystype NUMBER,iVchid NUMBER,iTerm NUMBER,iVchno NUMBER)
IS
rBsData rCursor;
iVchtypeid NUMBER(9);
iFundtype NUMBER(9);
iBillid NUMBER(9);
BEGIN
OPEN rBsData FOR SELECT vchtypeid,fundtype,billid FROM t_payvch WHERE MONTH=iTerm
AND to_number(text7)=iVchno;
LOOP
FETCH rBsData INTO iVchtypeid,iFundtype,iBillid;
EXIT WHEN rBsData%NOTFOUND;
IF iFundtype =1006 THEN
IF iVchtypeid =41 THEN
--bstypeid 421
INSERT INTO t_fmbsbillfeedback2(FEEDBACKID, BSTYPEID, BSBILLID, VCHID, RECID, ACCTSYSTYPEID)
SELECT s_fmbsbillfeedback.nextval,421,a.billid,iVchid,0,iAcctsystype FROM t_payapp a
WHERE a.payvchid =iBillid;
END IF;
IF iVchtypeid =63 THEN
--bstypeid 423
INSERT INTO t_fmbsbillfeedback2(FEEDBACKID, BSTYPEID, BSBILLID, VCHID, RECID, ACCTSYSTYPEID)
SELECT s_fmbsbillfeedback.nextval,423,a.billid,iVchid,0,iAcctsystype FROM t_payapp a
WHERE a.payvchid =iBillid;
END IF;
IF iVchtypeid =2723 THEN
--bstypeid 424
INSERT INTO t_fmbsbillfeedback2(FEEDBACKID, BSTYPEID, BSBILLID, VCHID, RECID, ACCTSYSTYPEID)
SELECT s_fmbsbillfeedback.nextval,424,a.billid,iVchid,0,iAcctsystype FROM t_payapp a
WHERE a.payvchid =iBillid;
END IF;
IF iVchtypeid =481 THEN
--bstypeid 422
INSERT INTO t_fmbsbillfeedback2(FEEDBACKID, BSTYPEID, BSBILLID, VCHID, RECID, ACCTSYSTYPEID)
SELECT s_fmbsbillfeedback.nextval,422,a.billid,iVchid,0,iAcctsystype FROM t_payapp a
WHERE a.payvchid =iBillid;
END IF;
END IF;
-- 预算内
IF iFundtype =1005 OR iFundtype =1020 OR iFundtype =1021 THEN
IF iVchtypeid =41 THEN
--bstypeid 301
INSERT INTO t_fmbsbillfeedback2(FEEDBACKID, BSTYPEID, BSBILLID, VCHID, RECID, ACCTSYSTYPEID)
SELECT s_fmbsbillfeedback.nextval,301,a.billid,iVchid,0,iAcctsystype FROM t_payapp a
WHERE a.payvchid =iBillid;
END IF;
IF iVchtypeid =63 THEN
--bstypeid 321
INSERT INTO t_fmbsbillfeedback2(FEEDBACKID, BSTYPEID, BSBILLID, VCHID, RECID, ACCTSYSTYPEID)
SELECT s_fmbsbillfeedback.nextval,321,a.billid,iVchid,0,iAcctsystype FROM t_payapp a
WHERE a.payvchid =iBillid;
END IF;
IF iVchtypeid =2723 THEN
--bstypeid 420
INSERT INTO t_fmbsbillfeedback2(FEEDBACKID, BSTYPEID, BSBILLID, VCHID, RECID, ACCTSYSTYPEID)
SELECT s_fmbsbillfeedback.nextval,420,a.billid,iVchid,0,iAcctsystype FROM t_payapp a
WHERE a.payvchid =iBillid;
END IF;
IF iVchtypeid =481 THEN
--bstypeid 341
INSERT INTO t_fmbsbillfeedback2(FEEDBACKID, BSTYPEID, BSBILLID, VCHID, RECID, ACCTSYSTYPEID)
SELECT s_fmbsbillfeedback.nextval,341,a.billid,iVchid,0,iAcctsystype FROM t_payapp a
WHERE a.payvchid =iBillid;
END IF;
END IF;
END LOOP;
END ;
-- 更新业务数据对应关系
PROCEDURE update_fmvoucher_bsfeedback(iAcctsystype NUMBER)
IS
iCurrTerm NUMBER(9);
sHisVchTab varchar2(200);
sHisRecTab varchar2(200);
iMaxVchno NUMBER(9);
sSQL varchar2(1000);
rVchno rCursor;
iVchid NUMBER(9);
iVchno NUMBER(9);
rBsData rCursor;
iVchtypeid NUMBER(9);
iFundtype NUMBER(9);
iBillid NUMBER(9);
BEGIN
SELECT a.currenttermno,a.historyvchtable,a.historyrectable INTO iCurrTerm,sHisVchTab,sHisRecTab
FROM t_fmaccountsystemtype a WHERE a.acctsystypeid =iAcctsystype;
FOR i IN 1..iCurrTerm
LOOP
IF i < iCurrTerm THEN
sSQL :='SELECT vchid,vchno FROM '||sHisVchTab||' WHERE acctsystype='||iAcctsystype||' AND term='||i||
' AND vchattr =6 AND acctdate <=''20080414'''||
' ORDER BY vchno';
OPEN rVchno FOR sSQL;
LOOP
FETCH rVchno INTO iVchid,iVchno;
EXIT WHEN rVchno%NOTFOUND;
save_bsfeedback(iAcctsystype,ivchid,i,iVchno);
COMMIT;
END LOOP;
ELSE
sSQL :='SELECT vchid,vchno FROM t_glvoucher WHERE acctsystype='||iAcctsystype||' AND term='||i||
' AND vchattr =6 AND acctdate <=''20080414'' AND status < 3'||
' ORDER BY vchno';
OPEN rVchno FOR sSQL;
LOOP
FETCH rVchno INTO iVchid,iVchno;
EXIT WHEN rVchno%NOTFOUND;
save_bsfeedback(iAcctsystype,ivchid,i,iVchno);
COMMIT;
END LOOP;
sSQL :='SELECT vchid,vchno FROM '||sHisVchTab||' WHERE acctsystype='||iAcctsystype||' AND term='||i||
' AND vchattr =6 AND acctdate <=''20080414'''||
' ORDER BY vchno';
OPEN rVchno FOR sSQL;
LOOP
FETCH rVchno INTO iVchid,iVchno;
EXIT WHEN rVchno%NOTFOUND;
save_bsfeedback(iAcctsystype,ivchid,i,iVchno);
COMMIT;
END LOOP;
END IF;
END LOOP;
END;
PROCEDURE update_fmvoucher_program
IS
rVchPro rCursor;
sProgram varchar2(10);
sAgencycode varchar2(10);
iprogram NUMBER(9);
BEGIN
OPEN rVchPro FOR SELECT a1.programcode,a1.agencycode,a1.program FROM tmp_dis_program a1
ORDER BY a1.programcode;
LOOP
FETCH rVchPro INTO sProgram,sAgencycode,iprogram;
EXIT WHEN rVchPro%NOTFOUND;
UPDATE tmp_hisdata_import t1 SET t1.tmp_program =iprogram
WHERE t1.program =sProgram AND t1.bdgagency =sAgencycode;
COMMIT;
END LOOP;
UPDATE tmp_hisdata_import2 a1 SET a1.program =(
SELECT a2.tmp_program FROM tmp_hisdata_import a2 WHERE a1.tmp_id =a2.tmp_id);
UPDATE tmp_hisdata_import2 SET program=0 WHERE program IS NULL;
COMMIT;
END;
PROCEDURE update_fmvoucher_user(iAcctsystype NUMBER)
IS
rData rCursor;
iTerm NUMBER(9);
iVchno NUMBER(9);
iCreater NUMBER(9);
iAuditor NUMBER(9);
iPoster NUMBER(9);
rVchUser rCursor;
iVchid NUMBER(9);
BEGIN
OPEN rData FOR SELECT term,vchno,creater,auditor,poster
FROM tmp_hisdata_import2 GROUP BY term,vchno,creater,auditor,poster
ORDER BY term,vchno;
LOOP
FETCH rData INTO iTerm,iVchno,iCreater,iAuditor,iPoster;
EXIT WHEN rData%NOTFOUND;
UPDATE t_glvoucher a1 SET a1.creater =iCreater,a1.auditor=iAuditor,a1.poster =iPoster
,a1.audittime =a1.createtime,a1.posttime =a1.createtime
WHERE a1.term =iTerm AND a1.vchno =iVchno AND acctsystype=iAcctsystype;
COMMIT;
END LOOP;
OPEN rVchUser FOR SELECT vchid,creater FROM t_glvoucher WHERE acctsystype=iAcctsystype;
LOOP
FETCH rVchUser INTO iVchid,iCreater;
EXIT WHEN rVchUser%NOTFOUND;
UPDATE t_glrecord a1 SET a1.userid =iCreater WHERE vchid=iVchid;
COMMIT;
END LOOP;
END;
PROCEDURE save_fmvoucher4Poste(iAcctsystype NUMBER)
IS
rVchid rCursor;
sHisVchTab varchar2(30);
sHisRecTab varchar2(30);
iVchid NUMBER(9);
sSQL varchar2(4000);
BEGIN
SELECT a1.historyvchtable,historyrectable INTO sHisVchTab,sHisRecTab
FROM t_fmaccountsystemtype a1 WHERE a1.acctsystypeid =iAcctsystype;
OPEN rVchid FOR SELECT vchid FROM t_glvoucher WHERE acctsystype=iAcctsystype;
LOOP
FETCH rVchid INTO iVchid;
EXIT WHEN rVchid%NOTFOUND;
--glvoucher
--sSQL :='
INSERT INTO t_glvoucher_01( VCHID, VCHTYPEID, BILLID, ACCOUNTNODE, GROUPNO, LOGID, ACCTSYSTYPE,
VCHCLSID , ACCTYEAR, TERM, ACCTDATE , VCHNO, APPENUM, CREATER, CREATETIME , AUDITOR,
AUDITTIME, POSTER , POSTTIME, STATUS , VCHATTR, NEWVCHID , CHARGEOFF, TIMESTAMP, REMARK ,
AUTOVCHNO, BILLIDS)
SELECT VCHID, VCHTYPEID, BILLID, ACCOUNTNODE, GROUPNO, LOGID, ACCTSYSTYPE,
VCHCLSID , ACCTYEAR, TERM, ACCTDATE , VCHNO, APPENUM, CREATER, CREATETIME , AUDITOR,
AUDITTIME, POSTER , POSTTIME, STATUS , VCHATTR, NEWVCHID , CHARGEOFF, TIMESTAMP, REMARK ,
AUTOVCHNO, BILLIDS FROM t_glvoucher WHERE acctsystype=iAcctsystype AND vchid=iVchid
AND status =3;
--EXECUTE IMMEDIATE sSQL;
-- glrecord
--sSQL :='
INSERT INTO t_glrecord_01(RECID , VCHID , ACCTSEQ , ACCTSEQSUB , ACCTYEAR , TERM , ACCTDATE , REMARK, DIGEST , ACCTSYSTYPE ,
ACCOUNT, FUNDTYPE , BDGAGENCY , EXPFUNC , EXPECONORMIC , INCOME, PROGRAMTYPE, PROGRAM ,
BDGVERSION, DEPARTMENTDIVISION, BDGMANAGEDIVISION ,
EXECUTEMETHOD , PAYTYPE , AGENTBANK , BDGALLOW , MAINBODY , BUSINESSTYPE, FUNDSOURCE, SUPPLIER ,
CHARGINGINSTITUTION , ADMCHARGE , PROPORLEVEL, GATHERINGBANKACCOUNT, PAYMENTBANKACCOUNT , SETTLEMODE ,
BDGLEVEL , INCOMEEXPMANAGE , CURRENCY, ELEMENT01, ELEMENT02, ELEMENT03, ELEMENT04, ELEMENT05,
ELEMENT06, ELEMENT07, ELEMENT08, ELEMENT09, ELEMENT10, ELEMENT11, ELEMENT12, ELEMENT13, ELEMENT14, ELEMENT15,
ELEMENT16, ELEMENT17, ELEMENT18, ELEMENT19, ELEMENT20, BDGYEAR, MONTH , BDGDOCNO, TEXT1 , TEXT2 , TEXT3 ,
TEXT4 , TEXT5 , TEXT6 , TEXT7 , TEXT8 , TEXT9 , TEXT10, CYAMT , RATE , AMT , DC , COAID , CTRLID , CTRLTYPEID,
NEWCTRL, VCHTYPEID, BILLID , VCHIDSRC , BILLCODE, MANAGEMENTTYPE , INDEXLEVEL , USERID , OBJECTID ,
LINKCTRLID , BILLIDS , OBJECTIDS , LINKACCOUNT , REFBDGAGENCY , REFEXPFUNC , REFPROGRAM , REFACCOUNT , REFELEMENT ,
PREPAYTAG , SOURCETAG , EXPBYINCFLAG , DETAILACCOUNT , PAYNO , CLEARBANKACCOUNT, CLEARBANK)
SELECT RECID , VCHID , ACCTSEQ , ACCTSEQSUB , ACCTYEAR , TERM , ACCTDATE , REMARK, DIGEST , ACCTSYSTYPE ,
ACCOUNT, FUNDTYPE , BDGAGENCY , EXPFUNC , EXPECONORMIC , INCOME, PROGRAMTYPE, PROGRAM ,
BDGVERSION, DEPARTMENTDIVISION, BDGMANAGEDIVISION ,
EXECUTEMETHOD , PAYTYPE , AGENTBANK , BDGALLOW , MAINBODY , BUSINESSTYPE, FUNDSOURCE, SUPPLIER ,
CHARGINGINSTITUTION , ADMCHARGE , PROPORLEVEL, GATHERINGBANKACCOUNT, PAYMENTBANKACCOUNT , SETTLEMODE ,
BDGLEVEL , INCOMEEXPMANAGE , CURRENCY, ELEMENT01, ELEMENT02, ELEMENT03, ELEMENT04, ELEMENT05,
ELEMENT06, ELEMENT07, ELEMENT08, ELEMENT09, ELEMENT10, ELEMENT11, ELEMENT12, ELEMENT13, ELEMENT14, ELEMENT15,
ELEMENT16, ELEMENT17, ELEMENT18, ELEMENT19, ELEMENT20, BDGYEAR, MONTH , BDGDOCNO, TEXT1 , TEXT2 , TEXT3 ,
TEXT4 , TEXT5 , TEXT6 , TEXT7 , TEXT8 , TEXT9 , TEXT10, CYAMT , RATE , AMT , DC , COAID , CTRLID , CTRLTYPEID,
NEWCTRL, VCHTYPEID, BILLID , VCHIDSRC , BILLCODE, MANAGEMENTTYPE , INDEXLEVEL , USERID , OBJECTID ,
LINKCTRLID , BILLIDS , OBJECTIDS , LINKACCOUNT , REFBDGAGENCY , REFEXPFUNC , REFPROGRAM , REFACCOUNT , REFELEMENT ,
PREPAYTAG , SOURCETAG , EXPBYINCFLAG , DETAILACCOUNT , PAYNO , CLEARBANKACCOUNT, CLEARBANK FROM t_glrecord
WHERE vchid=iVchid;
--EXECUTE IMMEDIATE sSQL;
COMMIT;
END LOOP;
END;
FUNCTION check_bsdata_And_vch(iAcctsystype NUMBER)RETURN varchar2
IS
iCurrterm NUMBER(9);
sHisVchTab varchar2(100);
sHisRecTab varchar2(100);
sSQL varchar2(1000);
iMaxVchno NUMBER(9);
rVch rCursor;
sRetu varchar2(4000);
iCnt NUMBER(9);
BEGIN
SELECT CURRENTTERMNO,a1.historyvchtable,a1.historyrectable INTO icurrterm,sHisVchTab,sHisRecTab
FROM t_fmaccountsystemtype a1 WHERE acctsystypeid=iacctsystype;
FOR i IN 1..icurrterm
LOOP
IF i < iCurrterm THEN
sSQL :='SELECT MAX(vchno) FROM '||sHisVchTab ||' WHERE acctsystype='||iAcctsystype||
' AND term='||i;
EXECUTE IMMEDIATE sSQL INTO iMaxVchno;
FOR j IN 1..iMaxVchno
LOOP
sSQL :='SELECT count(*) FROM (SELECT sum(amt)totalamt
FROM '||sHisRecTab ||' a1 WHERE EXISTS (SELECT * FROM '||sHisVchTab||' a2 '||
' WHERE a2.vchattr=6 AND a2.acctsystype='||iAcctsystype||
' AND a1.vchid =a2.vchid) AND acctsystype='||iAcctsystype||
' AND dc= 1 '||
' AND term='||i||' AND text10='''||j||''') v1,'||
' (SELECT abs(SUM(A1.AMT)) amt FROM t_payvch a1 WHERE a1.MONTH ='||i||
' AND a1.text7='''||j||''' AND createtime < ''20080414'') v2 WHERE v1.totalamt <> v2.amt ';
EXECUTE IMMEDIATE sSQl INTO iCnt;
IF iCnt > 0 THEN
sRetu :=sRetu||' term='||i||' and vchno='||j||' ';
--dbms_output.put_line('term='||i||' and vchno='||j);
END IF;
END LOOP;
ELSE
SELECT MAX(vchno) INTO iMaxVchno FROM t_glvoucher WHERE acctsystype=iAcctsystype AND
term=i AND acctdate <='20080414';
FOR j IN 1..iMaxVchno
LOOP
SELECT count(*) INTO iCnt FROM (SELECT sum(amt) totalamt
FROM t_glrecord a1 WHERE acctsystype=iAcctsystype AND EXISTS(
SELECT * FROM t_glvoucher a2 WHERE a1.vchid =a2.vchid AND a2.vchattr=6
AND a2.acctsystype =iAcctsystype) AND dc=1
AND term=i AND text10=j) v1,
(SELECT abs(SUM(A1.AMT)) amt FROM t_payvch a1 WHERE a1.MONTH =i
AND a1.text7=j AND createtime < '20080414') v2 WHERE v1.totalamt <> v2.amt ;
IF iCnt > 0 THEN
sRetu :=sRetu||' term='||i||' and vchno='||j||' ';
--dbms_output.put_line('term='||i||' and vchno='||j);
END IF;
END LOOP;
END IF;
--dbms_output.put_line('term='||i||' and maxvchno='||iMaxVchno);
END LOOP;
RETURN sRetu;
END;
--20080506 更新，导入系统
PROCEDURE tmp_save_voucher(iAcctsystypeid NUMBER,iTerm NUMBER)
IS
rVch rCursor;
iMaxVchno NUMBER(9);
iCyid NUMBER(9);
BEGIN
SELECT MAX(vchno) INTO iMaxVchno FROM (
SELECT MAX(vchno) vchno FROM t_glvoucher WHERE acctsystype=iAcctsystypeid AND term=iTerm
UNION
SELECT MAX(vchno) vchno FROM t_glvoucher_01 WHERE acctsystype=iAcctsystypeid AND term=iTerm);
--原币金额
SELECT cyid INTO iCyid FROM t_fmcurrency a1 WHERE a1.isstandard =1;
END;
--更新acctseq
PROCEDURE update_fmvch_acctseq2(iAcctsystypeid NUMBER,iVchid NUMBER)
IS
rTmp rCursor;
iTerm NUMBER(2);
iVchno NUMBER(9);
iDc NUMBER(2);
iAcct NUMBER(9);
iTmpTerm NUMBER(2);
iTmpVchno NUMBER(9);
iTmpAcctseq NUMBER(9);
BEGIN
iTmpTerm :=0;
iTmpVchno :=0;
OPEN rTmp FOR SELECT a1.term,to_number(a1.text10) vchno,
a1.dc,a1.element20
FROM t_glrecord a1
WHERE a1.acctsystype =iAcctsystypeid
AND a1.vchid =iVchid
GROUP BY a1.term,to_number(a1.text10),a1.element20,a1.dc
ORDER BY term,vchno,dc DESC;
LOOP
FETCH rTmp INTO iTerm,iVchno,iDc,iAcct;
EXIT WHEN rTmp%NOTFOUND;
IF iTmpTerm <> iTerm OR iTmpVchno <> iVchno THEN
iTmpAcctseq :=1;
ELSE
iTmpAcctseq := iTmpAcctseq + 1;
END IF;
iTmpTerm :=iTerm;
iTmpVchno :=iVchno;
UPDATE t_glrecord a1 SET a1.acctseq =iTmpAcctseq
WHERE a1.term =iTmpTerm AND a1.text10 =to_char(iTmpVchno)
AND a1.dc =iDc AND a1.element20 =iAcct;
--dbms_output.put_line('term='||iTmpTerm||' vchno='||iTmpVchno||' account='||iAcct);
COMMIT;
END LOOP;
END;
--更新分录明细号
PROCEDURE update_fmvch_acctseqsub2(iAcctsystypeid NUMBER,iVchid NUMBER)
IS
rTmp rCursor;
iTerm NUMBER(2);
iVchno NUMBER(9);
iAcctseq NUMBER(2);
iTmpTerm NUMBER(2);
iTmpVchno NUMBER(9);
iTmpAcctseq NUMBER(9);
iTmpAcctseqsub NUMBER(9);
rTmpAcctseqsub rCursor;
iRecid NUMBER(9);
BEGIN
iTmpTerm :=0;
iTmpVchno :=0;
iTmpAcctseq :=0;
OPEN rTmp FOR SELECT a1.term,to_number(a1.text10) vchno,acctseq
FROM t_glrecord a1
WHERE a1.acctsystype =iAcctsystypeid
AND a1.vchid =iVchid
GROUP BY a1.term,to_number(a1.text10),acctseq
ORDER BY term,vchno;
LOOP
FETCH rTmp INTO iTerm,iVchno,iAcctseq;
EXIT WHEN rTmp%NOTFOUND;
IF iTmpTerm <> iTerm OR iTmpVchno <> iVchno OR iTmpAcctseq <> iAcctseq THEN
iTmpAcctseqsub :=0;
iTmpTerm :=iTerm;
iTmpVchno :=iVchno;
iTmpAcctseq :=iAcctseq;
END IF;
OPEN rTmpAcctseqsub FOR SELECT recid FROM t_glrecord a1
WHERE a1.term =iTmpTerm AND a1.text10 =to_char(iTmpVchno)
AND a1.acctseq =iTmpAcctseq;
LOOP
FETCH rTmpAcctseqsub INTO iRecid;
EXIT WHEN rTmpAcctseqsub%NOTFOUND;
iTmpAcctseqsub := iTmpAcctseqsub + 1;
UPDATE t_glrecord a1 SET a1.acctseqsub =iTmpAcctseqsub WHERE a1.recid =iRecid;
END LOOP;
COMMIT;
END LOOP;
END;
PROCEDURE update_fmvch_acctseqAndSub2(iAcctsystypeid NUMBER,iVchid NUMBER)
IS
BEGIN
update_fmvch_acctseq2(iAcctsystypeid,iVchid);
update_fmvch_acctseqsub2(iAcctsystypeid,iVchid);
END;
PROCEDURE undoFmVch(iAcctsystype NUMBER,iOpertype NUMBER)
IS
rVchid rCursor;
iVchid NUMBER(9);
BEGIN
OPEN rVchid FOR SELECT vchid FROM t_glvoucher_01 WHERE acctsystype=81;
LOOP
FETCH rVchid INTO iVchid;
pkg_coa.keepaccountbyvch(iAcctsystype,2008,iVchid,iOpertype);
END LOOP;
END;
END FM_UTILITY;
/
扫一扫
4230
05-26
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
