CREATE OR REPLACE PROCEDURE "P_HGVS_SD_CUS_183" AS
BEGIN
----------声明变量
DECLARE C_CDEPCODE ZSDG_YSMX.VBAK_ZSDS_VA01%TYPE;
C_CFEEITEMCODE ZSDG_YSMX.CFEEITEMCODE%TYPE;
I_FEE ZSDG_YSMX.IFEE%TYPE;
C_CFEETYPE ZSDG_YSMX.CFEETYPE%TYPE;
I_APPID T_APPLY_MAIN.CBILLCODE%TYPE;
C_CACCOUNTCODE T_APPLY_MAIN.CACCOUNTCODE%TYPE;
v_clxbillcode VARCHAR(30);
v_citemname VARCHAR(200);
v_ctypecode VARCHAR(50);
v_count number;
-----------定义游标
CURSOR CURSOR_NAME IS
SELECT A.*
FROM (
/*
SELECT a.cgmcode,a.cdepcode,b.cfeeitemcode,sum(a.izjfee) izjfee , sum(a.ihtyffee) ihtyffee , sum(a.igcyffee) igcyffee , a.CSELLORDERCODE from T_TEMP_HGVS_INT_SD85 a
inner join t_businesstype b on a.cbudtype=b.cbuscode where isyscode IN (SELECT MAX(isyscode) FROM t_temp_hgvs_int_sd85
WHERE iflag = 1
GROUP BY CSELLORDERCODE,citemcode,ccptype,cgmcode,cdepcode)
GROUP BY a.cgmcode,a.cdepcode,b.cfeeitemcode,a.CSELLORDERCODE */ SELECT VBAK_ZSDS_VA01,
CFEEITEMCODE,
SUM(IFEE) AS IFEE,
CFEETYPE,
VBAK_VKORG
FROM ZSDG_YSMX a
inner join t_department d on a.VBAK_ZSDS_VA01=d.cdepcode
inner join t_feeitem f on a.CFEEITEMCODE=f.cfeeitemcode
inner join t_account acc on a.VBAK_VKORG=acc.caccountcode
inner join t_validbudgettype v on a.CFEETYPE=v.ctypecode
WHERE IFLAG = 1
GROUP BY VBAK_ZSDS_VA01, CFEEITEMCODE, CFEETYPE, VBAK_VKORG ) A;
BEGIN
-----------打开游标
OPEN CURSOR_NAME;
-----------开始循环
LOOP
FETCH CURSOR_NAME -- exit when fetch
INTO C_CDEPCODE, C_CFEEITEMCODE, I_FEE, C_CFEETYPE, C_CACCOUNTCODE;
EXIT WHEN CURSOR_NAME%NOTFOUND; notfound ---
--取立项数据
v_count := 0 ;
v_clxbillcode := '';
v_citemname := '';
v_ctypecode := ''; SELECT COUNT(*)
INTO v_count
FROM t_lxitem_main a
INNER JOIN t_lxitem_sub b ON a.cbillcode=b.cbillcode
WHERE b.iyear=TO_CHAR(SYSDATE-1,'YYYY')
--AND CASE WHEN Length(b.imonth)=1 THEN LPAD(b.imonth,2,'0') ELSE to_char(b.imonth) END =TO_CHAR(SYSDATE-1,'MM')
AND b.imonth =TO_NUMBER(TO_CHAR(SYSDATE-1,'MM')) --TO_NUMBER(TO_CHAR(SYSDATE-1,'MM'))
AND b.cfeeitemcode = c_cfeeitemcode
AND b.cdepcocode = c_cDepCode
AND rownum=1
ORDER BY a.cbillcode; IF v_count > 0 THEN
SELECT a.cbillcode,a.citemname,a.ctypecode
INTO v_clxbillcode,v_citemname,v_ctypecode
FROM t_lxitem_main a
INNER JOIN t_lxitem_sub b ON a.cbillcode=b.cbillcode
WHERE b.iyear=TO_CHAR(SYSDATE-1,'YYYY')
--AND CASE WHEN Length(b.imonth)=1 THEN LPAD(b.imonth,2,'0') ELSE to_char(b.imonth) END =TO_CHAR(SYSDATE-1,'MM')
AND b.imonth =TO_NUMBER(TO_CHAR(SYSDATE-1,'MM')) --TO_NUMBER(TO_CHAR(SYSDATE-1,'MM'))
AND b.cfeeitemcode = c_cfeeitemcode
AND b.cdepcocode = c_cDepCode
AND rownum=1
ORDER BY a.cbillcode;
END IF; --取出当前编号
SELECT 'AO' || FEEAPPLY.NEXTVAL INTO I_APPID FROM DUAL; --插入数据-------------------------------
INSERT INTO T_APPLY_MAIN
(CBILLCODE, --
IYEAR, --
IMONTH, --
DDATE, --
CDEPCODE_COST, --
CDEPCODE, --
CFEEITEMCODE, --
CBUDGETTYPECODE, --
CSALETYPE, --
ITOTALAPPLY, --
CACCOUNTCODE, --
CACCOUNTNO_OUT, --
CSUPTYPE, --
CDESCRIPTION, --
CBUDGETDEPCODE, --
CBUDGETDEPCODE_COST, --
CSYSTEMCODE, --
BALLOTTOPL, --
BALLOTTOINV,
CACCOUNTBANK,
CPROCODE,--
CPRONAME,--
CPROTYPE --
--cbillcode_sap )
SELECT M.I_APPID AS CBILLCODE,
TO_NUMBER(TO_CHAR(M.DDATE, 'YYYY')) AS IYEAR,
TO_NUMBER(TO_CHAR(M.DDATE, 'MM')) AS IMONTH,
M.DDATE,
DEPCLASS.CDEPCLASSCODE AS CDEPCODE_COST, --
M.CDEPCODE,
CBUDTYPE AS CFEEITEMCODE,
C_CFEETYPE AS CBUDGETTYPECODE, --
'内销' AS CSALETYPE, ---
M.I_IJE AS ITOTALAPPLY, --
C_CACCOUNTCODE AS CACCOUNTCODE, --
ACC.CACCOUNTNO AS CACCOUNTNO_OUT, --
'客户' AS CSUPTYPE, --
'实发明细' AS CDESCRIPTION, --
M.CDEPCODE AS CBUDGETDEPCODE, --
DEPCLASS.CDEPCLASSCODE AS CBUDGETDEPCODE_COST, --
'SAP' AS CSYSTEMCODE, --
'N' AS BALLOTTOPL, --
'N' AS BALLOTTOINV,
ACC.CACCOUNTBANK,
v_clxbillcode,--
v_citemname,--
v_ctypecode --
--c_cSoCode as cbillcode_sap
FROM (SELECT C_CDEPCODE AS CDEPCODE,
I_APPID AS I_APPID,
C_CFEEITEMCODE AS CBUDTYPE,
I_FEE AS I_IJE, SYSDATE AS DDATE --dep.CDEPCODE
FROM T_DEPARTMENT DEP
WHERE DEP.CDEPCODE = C_CDEPCODE) M
INNER JOIN T_DEPARTMENT DEPCLASS ON M.CDEPCODE = DEPCLASS.CDEPCODE
LEFT JOIN T_ACCOUNT ACC ON ACC.CACCOUNTCODE = C_CACCOUNTCODE; -----向费用子表插入数据-------------------------------
INSERT INTO T_APPLY_SUB
(CBILLCODE, --
IID, --
CDEPCODE, --
ITOTAL --
) SELECT I_APPID AS I_APPID,
ROWNUM,
SUB.VBAK_ZSDS_VA01,
NVL(SUB.IFEE, 0) AS ITOTAL
FROM ZSDG_YSMX SUB
WHERE SUB.VBAK_ZSDS_VA01 = C_CDEPCODE
AND CFEEITEMCODE = C_CFEEITEMCODE AND CFEETYPE=C_CFEETYPE AND VBAK_VKORG=C_CACCOUNTCODE
AND IFLAG = 1; -----更新中间表-------------------------------
UPDATE ZSDG_YSMX
SET CBILLCODE = I_APPID, IFLAG = 0
WHERE VBAK_ZSDS_VA01 = C_CDEPCODE
AND IFLAG = 1
AND CFEEITEMCODE = C_CFEEITEMCODE
AND CFEETYPE = C_CFEETYPE
AND VBAK_VKORG = C_CACCOUNTCODE;
-----------循环结束
END LOOP;
-----------关闭游标
CLOSE CURSOR_NAME; -----更新中间表-------------------------------
--没匹配的也更新标志,下次就不再取了
UPDATE ZSDG_YSMX
SET IFLAG = 2
WHERE IFLAG = 1
AND CFROM = 'p'
AND CMEMO = 'p';
END;
END;
BEGIN
----------声明变量
DECLARE C_CDEPCODE ZSDG_YSMX.VBAK_ZSDS_VA01%TYPE;
C_CFEEITEMCODE ZSDG_YSMX.CFEEITEMCODE%TYPE;
I_FEE ZSDG_YSMX.IFEE%TYPE;
C_CFEETYPE ZSDG_YSMX.CFEETYPE%TYPE;
I_APPID T_APPLY_MAIN.CBILLCODE%TYPE;
C_CACCOUNTCODE T_APPLY_MAIN.CACCOUNTCODE%TYPE;
v_clxbillcode VARCHAR(30);
v_citemname VARCHAR(200);
v_ctypecode VARCHAR(50);
v_count number;
-----------定义游标
CURSOR CURSOR_NAME IS
SELECT A.*
FROM (
/*
SELECT a.cgmcode,a.cdepcode,b.cfeeitemcode,sum(a.izjfee) izjfee , sum(a.ihtyffee) ihtyffee , sum(a.igcyffee) igcyffee , a.CSELLORDERCODE from T_TEMP_HGVS_INT_SD85 a
inner join t_businesstype b on a.cbudtype=b.cbuscode where isyscode IN (SELECT MAX(isyscode) FROM t_temp_hgvs_int_sd85
WHERE iflag = 1
GROUP BY CSELLORDERCODE,citemcode,ccptype,cgmcode,cdepcode)
GROUP BY a.cgmcode,a.cdepcode,b.cfeeitemcode,a.CSELLORDERCODE */ SELECT VBAK_ZSDS_VA01,
CFEEITEMCODE,
SUM(IFEE) AS IFEE,
CFEETYPE,
VBAK_VKORG
FROM ZSDG_YSMX a
inner join t_department d on a.VBAK_ZSDS_VA01=d.cdepcode
inner join t_feeitem f on a.CFEEITEMCODE=f.cfeeitemcode
inner join t_account acc on a.VBAK_VKORG=acc.caccountcode
inner join t_validbudgettype v on a.CFEETYPE=v.ctypecode
WHERE IFLAG = 1
GROUP BY VBAK_ZSDS_VA01, CFEEITEMCODE, CFEETYPE, VBAK_VKORG ) A;
BEGIN
-----------打开游标
OPEN CURSOR_NAME;
-----------开始循环
LOOP
FETCH CURSOR_NAME -- exit when fetch
INTO C_CDEPCODE, C_CFEEITEMCODE, I_FEE, C_CFEETYPE, C_CACCOUNTCODE;
EXIT WHEN CURSOR_NAME%NOTFOUND; notfound ---
--取立项数据
v_count := 0 ;
v_clxbillcode := '';
v_citemname := '';
v_ctypecode := ''; SELECT COUNT(*)
INTO v_count
FROM t_lxitem_main a
INNER JOIN t_lxitem_sub b ON a.cbillcode=b.cbillcode
WHERE b.iyear=TO_CHAR(SYSDATE-1,'YYYY')
--AND CASE WHEN Length(b.imonth)=1 THEN LPAD(b.imonth,2,'0') ELSE to_char(b.imonth) END =TO_CHAR(SYSDATE-1,'MM')
AND b.imonth =TO_NUMBER(TO_CHAR(SYSDATE-1,'MM')) --TO_NUMBER(TO_CHAR(SYSDATE-1,'MM'))
AND b.cfeeitemcode = c_cfeeitemcode
AND b.cdepcocode = c_cDepCode
AND rownum=1
ORDER BY a.cbillcode; IF v_count > 0 THEN
SELECT a.cbillcode,a.citemname,a.ctypecode
INTO v_clxbillcode,v_citemname,v_ctypecode
FROM t_lxitem_main a
INNER JOIN t_lxitem_sub b ON a.cbillcode=b.cbillcode
WHERE b.iyear=TO_CHAR(SYSDATE-1,'YYYY')
--AND CASE WHEN Length(b.imonth)=1 THEN LPAD(b.imonth,2,'0') ELSE to_char(b.imonth) END =TO_CHAR(SYSDATE-1,'MM')
AND b.imonth =TO_NUMBER(TO_CHAR(SYSDATE-1,'MM')) --TO_NUMBER(TO_CHAR(SYSDATE-1,'MM'))
AND b.cfeeitemcode = c_cfeeitemcode
AND b.cdepcocode = c_cDepCode
AND rownum=1
ORDER BY a.cbillcode;
END IF; --取出当前编号
SELECT 'AO' || FEEAPPLY.NEXTVAL INTO I_APPID FROM DUAL; --插入数据-------------------------------
INSERT INTO T_APPLY_MAIN
(CBILLCODE, --
IYEAR, --
IMONTH, --
DDATE, --
CDEPCODE_COST, --
CDEPCODE, --
CFEEITEMCODE, --
CBUDGETTYPECODE, --
CSALETYPE, --
ITOTALAPPLY, --
CACCOUNTCODE, --
CACCOUNTNO_OUT, --
CSUPTYPE, --
CDESCRIPTION, --
CBUDGETDEPCODE, --
CBUDGETDEPCODE_COST, --
CSYSTEMCODE, --
BALLOTTOPL, --
BALLOTTOINV,
CACCOUNTBANK,
CPROCODE,--
CPRONAME,--
CPROTYPE --
--cbillcode_sap )
SELECT M.I_APPID AS CBILLCODE,
TO_NUMBER(TO_CHAR(M.DDATE, 'YYYY')) AS IYEAR,
TO_NUMBER(TO_CHAR(M.DDATE, 'MM')) AS IMONTH,
M.DDATE,
DEPCLASS.CDEPCLASSCODE AS CDEPCODE_COST, --
M.CDEPCODE,
CBUDTYPE AS CFEEITEMCODE,
C_CFEETYPE AS CBUDGETTYPECODE, --
'内销' AS CSALETYPE, ---
M.I_IJE AS ITOTALAPPLY, --
C_CACCOUNTCODE AS CACCOUNTCODE, --
ACC.CACCOUNTNO AS CACCOUNTNO_OUT, --
'客户' AS CSUPTYPE, --
'实发明细' AS CDESCRIPTION, --
M.CDEPCODE AS CBUDGETDEPCODE, --
DEPCLASS.CDEPCLASSCODE AS CBUDGETDEPCODE_COST, --
'SAP' AS CSYSTEMCODE, --
'N' AS BALLOTTOPL, --
'N' AS BALLOTTOINV,
ACC.CACCOUNTBANK,
v_clxbillcode,--
v_citemname,--
v_ctypecode --
--c_cSoCode as cbillcode_sap
FROM (SELECT C_CDEPCODE AS CDEPCODE,
I_APPID AS I_APPID,
C_CFEEITEMCODE AS CBUDTYPE,
I_FEE AS I_IJE, SYSDATE AS DDATE --dep.CDEPCODE
FROM T_DEPARTMENT DEP
WHERE DEP.CDEPCODE = C_CDEPCODE) M
INNER JOIN T_DEPARTMENT DEPCLASS ON M.CDEPCODE = DEPCLASS.CDEPCODE
LEFT JOIN T_ACCOUNT ACC ON ACC.CACCOUNTCODE = C_CACCOUNTCODE; -----向费用子表插入数据-------------------------------
INSERT INTO T_APPLY_SUB
(CBILLCODE, --
IID, --
CDEPCODE, --
ITOTAL --
) SELECT I_APPID AS I_APPID,
ROWNUM,
SUB.VBAK_ZSDS_VA01,
NVL(SUB.IFEE, 0) AS ITOTAL
FROM ZSDG_YSMX SUB
WHERE SUB.VBAK_ZSDS_VA01 = C_CDEPCODE
AND CFEEITEMCODE = C_CFEEITEMCODE AND CFEETYPE=C_CFEETYPE AND VBAK_VKORG=C_CACCOUNTCODE
AND IFLAG = 1; -----更新中间表-------------------------------
UPDATE ZSDG_YSMX
SET CBILLCODE = I_APPID, IFLAG = 0
WHERE VBAK_ZSDS_VA01 = C_CDEPCODE
AND IFLAG = 1
AND CFEEITEMCODE = C_CFEEITEMCODE
AND CFEETYPE = C_CFEETYPE
AND VBAK_VKORG = C_CACCOUNTCODE;
-----------循环结束
END LOOP;
-----------关闭游标
CLOSE CURSOR_NAME; -----更新中间表-------------------------------
--没匹配的也更新标志,下次就不再取了
UPDATE ZSDG_YSMX
SET IFLAG = 2
WHERE IFLAG = 1
AND CFROM = 'p'
AND CMEMO = 'p';
END;
END;
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货