存储过程-程序员宅基地

CREATE OR REPLACE PACKAGE BODY PG_FIN_REPORT IS

--ACCYEARMONTH 核算年
--CURRENTMONTH 核算月
--USERID 用户id
PROCEDURE SP_INNER_REPORT_REV(CURRENTYEAR CHAR,
CURRENTMONTH CHAR,
USERID NUMBER,
SP_V_RESULT_COUNT OUT NUMBER) AS
BATCH_ID NUMBER; --批次ID
ACC_YEARMONTH CHAR(8);
P_BATCH_ID NUMBER;
V_ERRORCODE NUMBER;
V_ERRORTEXT VARCHAR2(4000);
--生成财务核算报表
BEGIN
SP_V_RESULT_COUNT := 0;
BATCH_ID := 0;
--核算年月
ACC_YEARMONTH := CONCAT(CURRENTYEAR, CURRENTMONTH);

--获取收入报表批次ID
SELECT COUNT(1)
INTO BATCH_ID
FROM TFIN_INNER_REVBATCH T
WHERE T.ACC_YEAR_MONTH = ACC_YEARMONTH
AND T.IS_DELETE = 0;

--生成核算报表
IF BATCH_ID > 0 THEN
--如果存在数据，作废
SELECT T.BATCH_ID
INTO P_BATCH_ID
FROM TFIN_INNER_REVBATCH T
WHERE T.ACC_YEAR_MONTH = ACC_YEARMONTH
AND T.IS_DELETE = 0;
--删除明细
UPDATE TFIN_INNER_REVREPORT T
SET T.IS_DELETE = 1,
T.LAST_OPERATE_ID = USERID,
T.LAST_OPERATE_TIME = SYSDATE
WHERE T.BATCH_ID = P_BATCH_ID;
SP_V_RESULT_COUNT := SP_V_RESULT_COUNT + SQL%ROWCOUNT;
--删除批次
UPDATE TFIN_INNER_REVBATCH T
SET T.IS_DELETE = 1,
T.LAST_OPERATE_ID = USERID,
T.LAST_OPERATE_TIME = SYSDATE
WHERE T.ACC_YEAR_MONTH = ACC_YEARMONTH;
SP_V_RESULT_COUNT := SP_V_RESULT_COUNT + SQL%ROWCOUNT;

----添加基础数据start 20190131
--删除基础数据明细
UPDATE TFIN_INNER_REVBASE T
SET T.IS_DELETE = 1,
T.LAST_OPERATE_ID = USERID,
T.LAST_OPERATE_TIME = SYSDATE
WHERE T.POSTING_DATE = TO_NUMBER(ACC_YEARMONTH);
SP_V_RESULT_COUNT := SP_V_RESULT_COUNT + SQL%ROWCOUNT;

END IF;

--新增批次数据
SELECT SEQ_TFIN_INNER_REVBATCH.NEXTVAL INTO BATCH_ID FROM DUAL;

---添加基础数据start
INSERT INTO TFIN_INNER_REVBASE
(REVBASE_ID,
BATCH_ID,
DEP_ID,
GROUP_ID,
DEP_NAME,
GROUP_NAME,
POSTING_DATE,
REPORT_TOTAL_FEE,
MAN_FEE,
OTHER_FEE,
INST_FEE,
CREATE_ID,
CREATE_TIME)
SELECT SEQ_TFIN_INNER_REVBASE.NEXTVAL,
BATCH_ID,
VTT.DEP_ID,
VTT.GROUP_ID,
VTT.DEP_NAME,
VTT.GROUP_NAME,
VTT.POSTING_DATE,
VTT.REPORT_TOTAL_FEE, --本期收入
VTT.MAN_FEE,
VTT.OTHER_MAN_FEE,
VTT.INST_FEE,
USERID,
SYSDATE
FROM OMS_V3.VW_FIN_REV_REPORT VTT
WHERE VTT.POSTING_DATE = TO_NUMBER(ACC_YEARMONTH);
SP_V_RESULT_COUNT := SP_V_RESULT_COUNT + SQL%ROWCOUNT;
---添加基础数据end

--产品新的批次ID
INSERT INTO TFIN_INNER_REVBATCH
(BATCH_ID,
BATCH_NAME,
ACC_YEAR_MONTH,
IS_LOCK,
IS_DELETE,
CREATE_ID,
CREATE_TIME,
LAST_OPERATE_ID,
LAST_OPERATE_TIME)
VALUES
(BATCH_ID,
CONCAT('revreport-', ACC_YEARMONTH),
ACC_YEARMONTH,
0,
0,
USERID,
SYSDATE,
NULL,
NULL);
SP_V_RESULT_COUNT := SP_V_RESULT_COUNT + SQL%ROWCOUNT;

--写入明细表

IF CURRENTMONTH = '01' THEN
--每年的一月 除分公司与BPO公司
INSERT INTO TFIN_INNER_REVREPORT
(REVREPORT_ID,
BATCH_ID,
DEP_ID,
GROUP_ID,
ACC_UNIT_ID,
ACC_UNIT_NAME,
PROFITNORM_VALUE,
THIS_REVENUE,
INVESTMENT_REVENUE,
TAX,
THISREVENUE_TOTAL,
YEARSREVENUE_TOTAL,
IS_DELETE,
CREATE_ID,
CREATE_TIME,
LAST_OPERATE_ID,
LAST_OPERATE_TIME)
SELECT SEQ_TFIN_INNER_REVREPORT.NEXTVAL,
BATCH_ID,
T3.DEP_ID,
T3.GROUP_ID,
T1.UNIT_ID,
T1.ACC_UNIT_NAME,
T2.PROFITNORM_VALUE,
NVL(T3.TOTAL_FEE, 0),
0,
0,
NVL(T3.TOTAL_FEE, 0),
NVL(T3.TOTAL_FEE, 0),
0,
USERID,
SYSDATE,
NULL,
NULL
FROM TFIN_INNER_ACCOUNT_UNIT T1
LEFT JOIN TFIN_PROFIT_PLAN T2
ON T1.UNIT_ID = T2.UNIT_ID
AND T2.INDEX_YEAR = CURRENTYEAR
LEFT JOIN (SELECT TT.DEP_ID,
TT.GROUP_ID,
TT.DEP_NAME,
TT.GROUP_NAME,
TT.POSTING_DATE,
TT.REPORT_TOTAL_FEE TOTAL_FEE --本期收入
FROM TFIN_INNER_REVBASE TT
WHERE TT.POSTING_DATE = TO_NUMBER(ACC_YEARMONTH)
AND TT.IS_DELETE = 0) T3
ON T1.ORG_ID = T3.GROUP_ID
WHERE T1.IS_DELETE = 0
AND T1.ORG_ID NOT IN (1, 2058, 2059)
/* AND T1.ORG_TYPE = 2*/
; ---小组统计
SP_V_RESULT_COUNT := SP_V_RESULT_COUNT + SQL%ROWCOUNT;

---增加分公司逻辑start
--每年的一月
INSERT INTO TFIN_INNER_REVREPORT
(REVREPORT_ID,
BATCH_ID,
DEP_ID,
GROUP_ID,
ACC_UNIT_ID,
ACC_UNIT_NAME,
PROFITNORM_VALUE,
THIS_REVENUE,
INVESTMENT_REVENUE,
TAX,
THISREVENUE_TOTAL,
YEARSREVENUE_TOTAL,
IS_DELETE,
CREATE_ID,
CREATE_TIME,
LAST_OPERATE_ID,
LAST_OPERATE_TIME)
SELECT SEQ_TFIN_INNER_REVREPORT.NEXTVAL,
BATCH_ID,
T3.DEP_ID,
T3.GROUP_ID,
T1.UNIT_ID,
T1.ACC_UNIT_NAME,
T2.PROFITNORM_VALUE,
NVL(T3.TOTAL_FEE, 0) + NVL(COM.TOTAL_FEE, 0),
0,
0,
NVL(T3.TOTAL_FEE, 0) + NVL(COM.TOTAL_FEE, 0),
NVL(T3.TOTAL_FEE, 0) + NVL(COM.TOTAL_FEE, 0),
0,
USERID,
SYSDATE,
NULL,
NULL
FROM TFIN_INNER_ACCOUNT_UNIT T1
LEFT JOIN TFIN_PROFIT_PLAN T2
ON T1.UNIT_ID = T2.UNIT_ID
AND T2.INDEX_YEAR = CURRENTYEAR
--分公司以及需要体现在分公司的核算单位：分公司，人事，对外委托组，
LEFT JOIN (SELECT SUM(B.REPORT_TOTAL_FEE) TOTAL_FEE,
1 DEP_ID,
1 GROUP_ID
FROM TFIN_INNER_REVBASE B
WHERE B.GROUP_ID IN (1, 42, 307, 21) --分公司,人事，对外委托组，
AND B.IS_DELETE = 0

AND B.POSTING_DATE = TO_NUMBER(ACC_YEARMONTH)) COM
ON COM.GROUP_ID = T1.ORG_ID

--BPO部分的其他收入
LEFT JOIN (SELECT SUM(TT.OTHER_FEE) TOTAL_FEE,
1 DEP_ID,
1 GROUP_ID
FROM TFIN_INNER_REVBASE TT
WHERE TT.DEP_ID = 2058 --BPO公司
AND TT.IS_DELETE = 0
AND TT.POSTING_DATE = TO_NUMBER(ACC_YEARMONTH)) T3
ON T3.GROUP_ID = T1.ORG_ID
WHERE T1.ORG_ID = 1
AND T1.IS_DELETE = 0;

SP_V_RESULT_COUNT := SP_V_RESULT_COUNT + SQL%ROWCOUNT;
---增加分公司逻辑end

---增加BPO公司逻辑start
--每年的一月
INSERT INTO TFIN_INNER_REVREPORT
(REVREPORT_ID,
BATCH_ID,
DEP_ID,
GROUP_ID,
ACC_UNIT_ID,
ACC_UNIT_NAME,
PROFITNORM_VALUE,
THIS_REVENUE,
INVESTMENT_REVENUE,
TAX,
THISREVENUE_TOTAL,
YEARSREVENUE_TOTAL,
IS_DELETE,
CREATE_ID,
CREATE_TIME,
LAST_OPERATE_ID,
LAST_OPERATE_TIME)
SELECT SEQ_TFIN_INNER_REVREPORT.NEXTVAL,
BATCH_ID,
T3.DEP_ID,
T3.GROUP_ID,
T1.UNIT_ID,
T1.ACC_UNIT_NAME,
T2.PROFITNORM_VALUE,
NVL(T3.TOTAL_FEE, 0),
0,
0,
NVL(T3.TOTAL_FEE, 0),
NVL(T3.TOTAL_FEE, 0),
0,
USERID,
SYSDATE,
NULL,
NULL
FROM TFIN_INNER_ACCOUNT_UNIT T1
LEFT JOIN TFIN_PROFIT_PLAN T2
ON T1.UNIT_ID = T2.UNIT_ID
AND T2.INDEX_YEAR = CURRENTYEAR
--BPO部分的管理费+利息收入
LEFT JOIN (SELECT SUM(TT.MAN_FEE) + SUM(TT.INST_FEE) TOTAL_FEE,
2058 DEP_ID,
2058 GROUP_ID
FROM TFIN_INNER_REVBASE TT
WHERE TT.BATCH_ID = BATCH_ID
AND TT.DEP_ID = 2058 --BPO公司
AND TT.IS_DELETE = 0
AND TT.POSTING_DATE = TO_NUMBER(ACC_YEARMONTH)) T3
ON T3.GROUP_ID = T1.ORG_ID
WHERE T1.ORG_ID = 2058
AND T1.IS_DELETE = 0;

SP_V_RESULT_COUNT := SP_V_RESULT_COUNT + SQL%ROWCOUNT;
---增加BPO公司逻辑end

ELSE
--其他月份除分公司与BPO公司
INSERT INTO TFIN_INNER_REVREPORT
(REVREPORT_ID,
BATCH_ID,
DEP_ID,
GROUP_ID,
ACC_UNIT_ID,
ACC_UNIT_NAME,
PROFITNORM_VALUE,
THIS_REVENUE,
INVESTMENT_REVENUE,
TAX,
THISREVENUE_TOTAL,
YEARSREVENUE_TOTAL,
IS_DELETE,
CREATE_ID,
CREATE_TIME,
LAST_OPERATE_ID,
LAST_OPERATE_TIME)
SELECT SEQ_TFIN_INNER_REVREPORT.NEXTVAL,
BATCH_ID,
T3.DEP_ID,
T3.GROUP_ID,
T1.UNIT_ID,
T1.ACC_UNIT_NAME,
T2.PROFITNORM_VALUE,
NVL(T3.TOTAL_FEE, 0),
0,
0,
NVL(T3.TOTAL_FEE, 0),
NVL(T3.TOTAL_FEE, 0) +
(SELECT NVL(SUM(NVL(T4.YEARSREVENUE_TOTAL, 0)), 0)
FROM TFIN_INNER_REVREPORT T4
JOIN TFIN_INNER_REVBATCH T5
ON T4.BATCH_ID = T5.BATCH_ID
WHERE T4.IS_DELETE = 0
AND T4.STATUS = 0
AND T5.ACC_YEAR_MONTH = TO_NUMBER(ACC_YEARMONTH) - 1
AND T4.ACC_UNIT_ID = T1.UNIT_ID),
0,
USERID,
SYSDATE,
NULL,
NULL
FROM TFIN_INNER_ACCOUNT_UNIT T1
LEFT JOIN TFIN_PROFIT_PLAN T2
ON T1.UNIT_ID = T2.UNIT_ID
AND T2.INDEX_YEAR = CURRENTYEAR
LEFT JOIN (SELECT TT.DEP_ID,
TT.GROUP_ID,
TT.DEP_NAME,
TT.GROUP_NAME,
TT.POSTING_DATE,
TT.REPORT_TOTAL_FEE TOTAL_FEE --本期收入
FROM TFIN_INNER_REVBASE TT
WHERE TT.POSTING_DATE = TO_NUMBER(ACC_YEARMONTH)
AND TT.IS_DELETE = 0) T3
ON T3.GROUP_ID = T1.ORG_ID
WHERE T1.IS_DELETE = 0
AND T1.ORG_ID NOT IN (1, 2058, 2059)
/* AND T1.ORG_TYPE = 2*/
; ---按小组统计
SP_V_RESULT_COUNT := SP_V_RESULT_COUNT + SQL%ROWCOUNT;

---增加分公司逻辑start
--其他月份
INSERT INTO TFIN_INNER_REVREPORT
(REVREPORT_ID,
BATCH_ID,
DEP_ID,
GROUP_ID,
ACC_UNIT_ID,
ACC_UNIT_NAME,
PROFITNORM_VALUE,
THIS_REVENUE,
INVESTMENT_REVENUE,
TAX,
THISREVENUE_TOTAL,
YEARSREVENUE_TOTAL,
IS_DELETE,
CREATE_ID,
CREATE_TIME,
LAST_OPERATE_ID,
LAST_OPERATE_TIME)
SELECT SEQ_TFIN_INNER_REVREPORT.NEXTVAL,
BATCH_ID,
T3.DEP_ID,
T3.GROUP_ID,
T1.UNIT_ID,
T1.ACC_UNIT_NAME,
T2.PROFITNORM_VALUE,
NVL(T3.TOTAL_FEE, 0) + NVL(COM.TOTAL_FEE, 0),
0,
0,
NVL(T3.TOTAL_FEE, 0) + NVL(COM.TOTAL_FEE, 0),
NVL(T3.TOTAL_FEE, 0) + NVL(COM.TOTAL_FEE, 0) +
(SELECT NVL(SUM(NVL(T4.YEARSREVENUE_TOTAL, 0)), 0)
FROM TFIN_INNER_REVREPORT T4
JOIN TFIN_INNER_REVBATCH T5
ON T4.BATCH_ID = T5.BATCH_ID
WHERE T4.IS_DELETE = 0
AND T5.ACC_YEAR_MONTH = TO_NUMBER(ACC_YEARMONTH) - 1
AND T4.ACC_UNIT_ID = T1.UNIT_ID),
0,
USERID,
SYSDATE,
NULL,
NULL
FROM TFIN_INNER_ACCOUNT_UNIT T1
JOIN TFIN_PROFIT_PLAN T2
ON T1.UNIT_ID = T2.UNIT_ID
AND T2.IS_DELETE = 0
AND T2.INDEX_YEAR = CURRENTYEAR
--分公司以及需要体现在分公司的核算单位：分公司，人事，对外委托组，互联网
LEFT JOIN (SELECT SUM(B.REPORT_TOTAL_FEE) TOTAL_FEE,
1 DEP_ID,
1 GROUP_ID
FROM TFIN_INNER_REVBASE B
WHERE B.GROUP_ID IN (1, 42, 307, 21) --分公司,人事，对外委托组，互联网
AND B.IS_DELETE = 0

AND B.POSTING_DATE = TO_NUMBER(ACC_YEARMONTH)) COM
ON COM.GROUP_ID = T1.ORG_ID

--BPO部分的其他收入
LEFT JOIN (SELECT SUM(TT.OTHER_FEE) TOTAL_FEE,
1 DEP_ID,
1 GROUP_ID
FROM TFIN_INNER_REVBASE TT
WHERE TT.DEP_ID = 2058 --BPO公司
AND TT.IS_DELETE = 0
AND TT.POSTING_DATE = TO_NUMBER(ACC_YEARMONTH)) T3
ON T3.GROUP_ID = T1.ORG_ID
WHERE T1.ORG_ID = 1
AND T1.IS_DELETE = 0;
SP_V_RESULT_COUNT := SP_V_RESULT_COUNT + SQL%ROWCOUNT;
---增加分公司逻辑end

---增加BPO公司逻辑start
--其他月份
INSERT INTO TFIN_INNER_REVREPORT
(REVREPORT_ID,
BATCH_ID,
DEP_ID,
GROUP_ID,
ACC_UNIT_ID,
ACC_UNIT_NAME,
PROFITNORM_VALUE,
THIS_REVENUE,
INVESTMENT_REVENUE,
TAX,
THISREVENUE_TOTAL,
YEARSREVENUE_TOTAL,
IS_DELETE,
CREATE_ID,
CREATE_TIME,
LAST_OPERATE_ID,
LAST_OPERATE_TIME)
SELECT SEQ_TFIN_INNER_REVREPORT.NEXTVAL,
BATCH_ID,
T3.DEP_ID,
T3.GROUP_ID,
T1.UNIT_ID,
T1.ACC_UNIT_NAME,
T2.PROFITNORM_VALUE,
NVL(T3.TOTAL_FEE, 0),
0,
0,
NVL(T3.TOTAL_FEE, 0),
NVL(T3.TOTAL_FEE, 0) +
(SELECT NVL(SUM(NVL(T4.YEARSREVENUE_TOTAL, 0)), 0)
FROM TFIN_INNER_REVREPORT T4
JOIN TFIN_INNER_REVBATCH T5
ON T4.BATCH_ID = T5.BATCH_ID
WHERE T4.IS_DELETE = 0
AND T5.ACC_YEAR_MONTH = TO_NUMBER(ACC_YEARMONTH) - 1
AND T4.ACC_UNIT_ID = T1.UNIT_ID),
0,
USERID,
SYSDATE,
NULL,
NULL
FROM TFIN_INNER_ACCOUNT_UNIT T1
LEFT JOIN TFIN_PROFIT_PLAN T2
ON T1.UNIT_ID = T2.UNIT_ID
AND T2.INDEX_YEAR = CURRENTYEAR
--BPO部分的管理费+利息收入
LEFT JOIN (SELECT SUM(TT.MAN_FEE) + SUM(TT.INST_FEE) TOTAL_FEE,
2058 DEP_ID,
2058 GROUP_ID
FROM TFIN_INNER_REVBASE TT
WHERE TT.BATCH_ID = BATCH_ID
AND TT.DEP_ID = 2058 --BPO公司
AND TT.IS_DELETE = 0
AND TT.POSTING_DATE = TO_NUMBER(ACC_YEARMONTH)) T3
ON T3.GROUP_ID = T1.ORG_ID
WHERE T1.ORG_ID = 2058
AND T1.IS_DELETE = 0;

SP_V_RESULT_COUNT := SP_V_RESULT_COUNT + SQL%ROWCOUNT;
---增加BPO公司逻辑end

END IF;
--结束
COMMIT;
DBMS_OUTPUT.PUT_LINE(SP_V_RESULT_COUNT);
EXCEPTION
WHEN OTHERS THEN
BEGIN
ROLLBACK;
V_ERRORCODE := SQLCODE;
V_ERRORTEXT := SUBSTR(SQLERRM, 1, 200);
OMS_V3.SP_FIN_WRITEOFFS_LOG(V_ERRORCODE, 110, V_ERRORTEXT);
DBMS_OUTPUT.PUT_LINE(0);
END;
END SP_INNER_REPORT_REV;

-----成本核算--------------------------20180214------
PROCEDURE SP_INNER_REPORT_COST(CURRENTYEAR CHAR,
CURRENTMONTH CHAR,
USERID NUMBER,
SP_V_RESULT_COUNT OUT NUMBER) AS
BATCH_ID NUMBER; --批次ID
ACC_YEARMONTH CHAR(8);
P_BATCH_ID NUMBER;
V_ERRORCODE NUMBER;
V_ERRORTEXT VARCHAR2(4000);
--生成财务核算报表
BEGIN
SP_V_RESULT_COUNT := 0;
BATCH_ID := 0;
--核算年月
ACC_YEARMONTH := CONCAT(CURRENTYEAR, CURRENTMONTH);

--获取收入报表批次ID
SELECT COUNT(1)
INTO BATCH_ID
FROM TFIN_INNER_COSTBATCH T
WHERE T.ACC_YEAR_MONTH = ACC_YEARMONTH
AND T.IS_DELETE = 0;

--生成核算报表
IF BATCH_ID > 0 THEN
--如果存在数据，作废
SELECT T.BATCH_ID
INTO P_BATCH_ID
FROM TFIN_INNER_COSTBATCH T
WHERE T.ACC_YEAR_MONTH = ACC_YEARMONTH
AND T.IS_DELETE = 0;
--删除明细
UPDATE TFIN_INNER_COSTREPORT T
SET T.IS_DELETE = 1,
T.LAST_OPERATE_ID = USERID,
T.LAST_OPERATE_TIME = SYSDATE
WHERE T.BATCH_ID = P_BATCH_ID;
SP_V_RESULT_COUNT := SP_V_RESULT_COUNT + SQL%ROWCOUNT;
--删除批次
UPDATE TFIN_INNER_COSTBATCH T
SET T.IS_DELETE = 1,
T.LAST_OPERATE_ID = USERID,
T.LAST_OPERATE_TIME = SYSDATE
WHERE T.ACC_YEAR_MONTH = ACC_YEARMONTH;
SP_V_RESULT_COUNT := SP_V_RESULT_COUNT + SQL%ROWCOUNT;

----添加基础数据start 20190214
--删除基础数据明细
UPDATE TFIN_INNER_COSTBASE T
SET T.IS_DELETE = 1,
T.LAST_OPERATE_ID = USERID,
T.LAST_OPERATE_TIME = SYSDATE
WHERE T.POSTING_DATE = TO_NUMBER(ACC_YEARMONTH);
SP_V_RESULT_COUNT := SP_V_RESULT_COUNT + SQL%ROWCOUNT;

END IF;

--新增批次数据
SELECT SEQ_INNER_COSTBATCH.NEXTVAL INTO BATCH_ID FROM DUAL;

---添加基础数据start
INSERT INTO TFIN_INNER_COSTBASE
(COSTBASE_ID,
DEP_ID,
GROUP_ID,
DEP_NAME,
GROUP_NAME,
POSTING_DATE,
SALARY_FEE,
TC_FEE,
SHEBAO_FEE,
JBYL_FEE,
GJJ_FEE,
QUNUAN_FEE,
SAVE_FEE,
TANQIN_FEE,
ADDEDRATE_FEE,
BCYL_FEE,
EMS_FEE,
CONSIGN_FEE,
HUODONG_FEE,
ZJZY_FEE,
BAN_ZHENG_FEE,
TJ_FEE,
SALARY_COST_FEE,
MOVIE_FEE,
BT_FEE,
JITI_FEE,
IS_DELETE,
CREATE_ID,
CREATE_TIME,
REMARK,
BATCH_ID)
SELECT SEQ_INNER_COSTBASE.NEXTVAL,
DEP_ID,
GROUP_ID,
DEP_NAME,
GROUP_NAME,
ACC_YEARMONTH,
SALARY_FEE,
TC_FEE,
SHEBAO_FEE,
YILIAO_FEE,
GJJ_FEE,
QUNUAN_FEE,
SAVE_FEE,
TANQIN_FEE,
ADDEDRATE_FEE,
BCYL_FEE,
EMS_FEE,
CONSIGN_FEE,
HUODONG_FEE,
ZJZY_FEE,
BAN_ZHENG_FEE,
TJ_FEE,
SALARY_COST_FEE,
MOVIE_FEE,
BT_FEE,
JITI_FEE,
0,
USERID,
SYSDATE,
'基础数据',
1
FROM (SELECT DEP_ID,
GROUP_ID,
DEP_NAME,
GROUP_NAME,
SUM(SALARY_FEE) SALARY_FEE,
SUM(TC_FEE) TC_FEE,
SUM(SHEBAO_FEE) SHEBAO_FEE,
SUM(YILIAO_FEE) YILIAO_FEE,
SUM(GJJ_FEE) GJJ_FEE,
SUM(QUNUAN_FEE) QUNUAN_FEE,
SUM(SAVE_FEE) SAVE_FEE,
SUM(TANQIN_FEE) TANQIN_FEE,
SUM(ADDEDRATE_FEE) ADDEDRATE_FEE,
SUM(BCYL_FEE) BCYL_FEE,
SUM(EMS_FEE) EMS_FEE,
SUM(CONSIGN_FEE) CONSIGN_FEE,
SUM(HUODONG_FEE) HUODONG_FEE,
0 ZJZY_FEE, --资金占用费
SUM(BAN_ZHENG_FEE) BAN_ZHENG_FEE,
SUM(TJ_FEE) TJ_FEE,
SUM(SALARY_COST_FEE) SALARY_COST_FEE,
SUM(MOVIE_FEE) MOVIE_FEE,
SUM(BT_FEE) BT_FEE,
SUM(JITI_FEE) JITI_FEE
FROM OMS_V3.VW_FIN_COST_ACCOUNT
WHERE TO_CHAR(TO_DATE(POSTING_DATE, 'yyyy-mm-dd'), 'yyyymm') =
TO_NUMBER(ACC_YEARMONTH)
GROUP BY DEP_ID, DEP_NAME, GROUP_ID, GROUP_NAME);
SP_V_RESULT_COUNT := SP_V_RESULT_COUNT + SQL%ROWCOUNT;
---添加基础数据end

--产品新的批次ID
INSERT INTO TFIN_INNER_COSTBATCH
(BATCH_ID,
BATCH_NAME,
ACC_YEAR_MONTH,
IS_LOCK,
IS_DELETE,
CREATE_ID,
CREATE_TIME,
LAST_OPERATE_ID,
LAST_OPERATE_TIME)
VALUES
(BATCH_ID,
CONCAT('revreport-', ACC_YEARMONTH),
ACC_YEARMONTH,
0,
0,
USERID,
SYSDATE,
NULL,
NULL);
SP_V_RESULT_COUNT := SP_V_RESULT_COUNT + SQL%ROWCOUNT;
----批次结束
IF CURRENTMONTH = '01' THEN

INSERT INTO TFIN_INNER_COSTREPORT
(COSTREPORT_ID,
BATCH_ID,
DEP_ID,
GROUP_ID,
ACC_UNIT_ID,
ACC_UNIT_NAME,
SALARY_FEE,
SHEBAO_FEE,
JBYL_FEE,
GJJ_FEE,
QUNUAN_FEE,
SAVE_FEE,
TANQIN_FEE,
BCYL_FEE,
EMS_FEE,
CONSIGN_FEE,
HUODONG_FEE,
FUND_OCCUPY_FEE, ---资金占用费
THISCOSTING_TOTAL,
YEARSCOSTING_TOTAL,
IS_DELETE,
CREATE_ID,
CREATE_TIME,
STATUS)
SELECT SEQ_INNER_COSTREPORT.NEXTVAL,
BATCH_ID, ---批次ID
BASE.DEP_ID,
BASE.GROUP_ID,
UNIT.UNIT_ID,
UNIT.ACC_UNIT_NAME,
SALARY_FEE, --雇员工资
BASE.SHEBAO_FEE, ---社保
BASE.JBYL_FEE, ---基本医疗
BASE.GJJ_FEE, ---公积金
BASE.QUNUAN_FEE, ---取暖
BASE.SAVE_FEE, ---存档费
BASE.TANQIN_FEE, ---探亲费
BASE.BCYL_FEE, --- 补充医疗
BASE.EMS_FEE, ---快递费
BASE.CONSIGN_FEE, ---委托服务费
BASE.HUODONG_FEE, ---雇员活动费
BASE.ZJZY_FEE, ---资金占用费
BASE.TOTAL_FEE,
BASE.TOTAL_FEE,
0, --是否删除
USERID, --创建人
SYSDATE,
0 ---修改状态
FROM TFIN_INNER_ACCOUNT_UNIT UNIT
LEFT JOIN VM_TFIN_INNER_COSTBASE BASE
ON BASE.GROUP_ID = UNIT.ORG_ID
AND BASE.TOTAL_FEE <> 0
AND UNIT.IS_DELETE = 0
WHERE BASE.POSTING_DATE = TO_NUMBER(ACC_YEARMONTH);
SP_V_RESULT_COUNT := SP_V_RESULT_COUNT + SQL%ROWCOUNT;
ELSE
INSERT INTO TFIN_INNER_COSTREPORT
(COSTREPORT_ID,
BATCH_ID,
DEP_ID,
GROUP_ID,
ACC_UNIT_ID,
ACC_UNIT_NAME,
SALARY_FEE,
SHEBAO_FEE,
JBYL_FEE,
GJJ_FEE,
QUNUAN_FEE,
SAVE_FEE,
TANQIN_FEE,
BCYL_FEE,
EMS_FEE,
CONSIGN_FEE,
HUODONG_FEE,
FUND_OCCUPY_FEE, ---资金占用费
THISCOSTING_TOTAL,
YEARSCOSTING_TOTAL,
IS_DELETE,
CREATE_ID,
CREATE_TIME,
STATUS)
SELECT SEQ_INNER_COSTREPORT.NEXTVAL,
BATCH_ID, ---批次ID
BASE.DEP_ID,
BASE.GROUP_ID,
UNIT.UNIT_ID,
UNIT.ACC_UNIT_NAME,
SALARY_FEE, --雇员工资
BASE.SHEBAO_FEE, ---社保
BASE.JBYL_FEE, ---基本医疗
BASE.GJJ_FEE, ---公积金
BASE.QUNUAN_FEE, ---取暖
BASE.SAVE_FEE, ---存档费
BASE.TANQIN_FEE, ---探亲费
BASE.BCYL_FEE, --- 补充医疗
BASE.EMS_FEE, ---快递费
BASE.CONSIGN_FEE, ---委托服务费
BASE.HUODONG_FEE, ---雇员活动费
BASE.ZJZY_FEE, ---资金占用费
BASE.TOTAL_FEE,
BASE.TOTAL_FEE +
(SELECT NVL(SUM(NVL(T4.THISCOSTING_TOTAL, 0)), 0)
FROM TFIN_INNER_COSTREPORT T4
JOIN TFIN_INNER_COSTBATCH T5
ON T4.BATCH_ID = T5.BATCH_ID
WHERE T4.IS_DELETE = 0
AND T5.ACC_YEAR_MONTH = TO_NUMBER(ACC_YEARMONTH) - 1
AND T4.ACC_UNIT_ID = UNIT.UNIT_ID),
0, --是否删除
USERID, --创建人
SYSDATE,
0 ---修改状态
FROM TFIN_INNER_ACCOUNT_UNIT UNIT
LEFT JOIN VM_TFIN_INNER_COSTBASE BASE
ON BASE.GROUP_ID = UNIT.ORG_ID
AND BASE.TOTAL_FEE <> 0
AND UNIT.IS_DELETE = 0
WHERE BASE.POSTING_DATE = TO_NUMBER(ACC_YEARMONTH);
SP_V_RESULT_COUNT := SP_V_RESULT_COUNT + SQL%ROWCOUNT;

END IF;
--结束
COMMIT;
DBMS_OUTPUT.PUT_LINE(SP_V_RESULT_COUNT);

EXCEPTION
WHEN OTHERS THEN
BEGIN
ROLLBACK;
V_ERRORCODE := SQLCODE;
V_ERRORTEXT := SUBSTR(SQLERRM, 1, 200);
OMS_V3.SP_FIN_WRITEOFFS_LOG(V_ERRORCODE, 110, V_ERRORTEXT);
DBMS_OUTPUT.PUT_LINE(0);
END;
END SP_INNER_REPORT_COST;

END PG_FIN_REPORT;

转载于:https://www.cnblogs.com/zchunhua/p/10394346.html
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_30662011/article/details/97515348

智能推荐

oracle 12c 集群安装后的检查_12c查看crs状态-程序员宅基地

文章浏览阅读1.6k次。安装配置gi、安装数据库软件、dbca建库见下：http://blog.csdn.net/kadwf123/article/details/784299611、检查集群节点及状态：[root@rac2 ~]# olsnodes -srac1 Activerac2 Activerac3 Activerac4 Active[root@rac2 ~]_12c查看crs状态

解决jupyter notebook无法找到虚拟环境的问题_jupyter没有pytorch环境-程序员宅基地

文章浏览阅读1.3w次，点赞45次，收藏99次。我个人用的是anaconda3的一个python集成环境，自带jupyter notebook，但在我打开jupyter notebook界面后，却找不到对应的虚拟环境，原来是jupyter notebook只是通用于下载anaconda时自带的环境，其他环境要想使用必须手动下载一些库：1.首先进入到自己创建的虚拟环境(pytorch是虚拟环境的名字)activate pytorch2.在该环境下下载这个库conda install ipykernelconda install nb__jupyter没有pytorch环境

国内安装scoop的保姆教程_scoop-cn-程序员宅基地

文章浏览阅读5.2k次，点赞19次，收藏28次。选择scoop纯属意外，也是无奈，因为电脑用户被锁了管理员权限，所有exe安装程序都无法安装，只可以用绿色软件，最后被我发现scoop，省去了到处下载XXX绿色版的烦恼，当然scoop里需要管理员权限的软件也跟我无缘了（譬如everything）。推荐添加dorado这个bucket镜像，里面很多中文软件，但是部分国外的软件下载地址在github，可能无法下载。以上两个是官方bucket的国内镜像，所有软件建议优先从这里下载。上面可以看到很多bucket以及软件数。如果官网登陆不了可以试一下以下方式。_scoop-cn

Element ui colorpicker在Vue中的使用_vue el-color-picker-程序员宅基地

文章浏览阅读4.5k次，点赞2次，收藏3次。首先要有一个color-picker组件 <el-color-picker v-model="headcolor"></el-color-picker>在data里面data() { return {headcolor: ’ #278add ’ //这里可以选择一个默认的颜色} }然后在你想要改变颜色的地方用v-bind绑定就好了，例如：这里的:sty..._vue el-color-picker

迅为iTOP-4412精英版之烧写内核移植后的镜像_exynos 4412 刷机-程序员宅基地

文章浏览阅读640次。基于芯片日益增长的问题，所以内核开发者们引入了新的方法，就是在内核中只保留函数，而数据则不包含，由用户（应用程序员）自己把数据按照规定的格式编写，并放在约定的地方，为了不占用过多的内存，还要求数据以根精简的方式编写。boot启动时，传参给内核，告诉内核设备树文件和kernel的位置，内核启动时根据地址去找到设备树文件，再利用专用的编译器去反编译dtb文件，将dtb还原成数据结构，以供驱动的函数去调用。firmware是三星的一个固件的设备信息，因为找不到固件，所以内核启动不成功。_exynos 4412 刷机

Linux系统配置jdk_linux配置jdk-程序员宅基地

文章浏览阅读2w次，点赞24次，收藏42次。Linux系统配置jdkLinux学习教程，Linux入门教程（超详细）_linux配置jdk

随便推点

matlab(4)：特殊符号的输入_matlab微米怎么输入-程序员宅基地

文章浏览阅读3.3k次，点赞5次，收藏19次。xlabel('\delta');ylabel('AUC');具体符号的对照表参照下图：_matlab微米怎么输入

C语言程序设计-文件(打开与关闭、顺序、二进制读写)-程序员宅基地

文章浏览阅读119次。顺序读写指的是按照文件中数据的顺序进行读取或写入。对于文本文件，可以使用fgets、fputs、fscanf、fprintf等函数进行顺序读写。在C语言中，对文件的操作通常涉及文件的打开、读写以及关闭。文件的打开使用fopen函数，而关闭则使用fclose函数。在C语言中，可以使用fread和fwrite函数进行二进制读写。‍ Biaoge 于2024-03-09 23:51发布 阅读量：7 ️文章类型：【 C语言程序设计 】在C语言中，用于打开文件的函数是____，用于关闭文件的函数是____。

Touchdesigner自学笔记之三_touchdesigner怎么让一个模型跟着鼠标移动-程序员宅基地

文章浏览阅读3.4k次，点赞2次，收藏13次。跟随鼠标移动的粒子以grid（SOP）为partical（SOP）的资源模板，调整后连接【Geo组合+point spirit（MAT)】，在连接【feedback组合】适当调整。影响粒子动态的节点【metaball(SOP)+force(SOP)】添加mouse in（CHOP)鼠标位置到metaball的坐标，实现鼠标影响。..._touchdesigner怎么让一个模型跟着鼠标移动

【附源码】基于java的校园停车场管理系统的设计与实现61m0e9计算机毕设SSM_基于java技术的停车场管理系统实现与设计-程序员宅基地

文章浏览阅读178次。项目运行环境配置：Jdk1.8 + Tomcat7.0 + Mysql + HBuilderX（Webstorm也行）+ Eclispe（IntelliJ IDEA,Eclispe,MyEclispe,Sts都支持）。项目技术：Springboot + mybatis + Maven +mysql5.7或8.0+html+css+js等等组成，B/S模式 + Maven管理等等。环境需要1.运行环境：最好是java jdk 1.8，我们在这个平台上运行的。其他版本理论上也可以。_基于java技术的停车场管理系统实现与设计

Android系统播放器MediaPlayer源码分析_android多媒体播放源码分析 时序图-程序员宅基地

文章浏览阅读3.5k次。前言对于MediaPlayer播放器的源码分析内容相对来说比较多，会从Java-&amp;amp;gt;Jni-&amp;amp;gt;C/C++慢慢分析，后面会慢慢更新。另外，博客只作为自己学习记录的一种方式，对于其他的不过多的评论。MediaPlayerDemopublic class MainActivity extends AppCompatActivity implements SurfaceHolder.Cal..._android多媒体播放源码分析 时序图

java 数据结构与算法 ——快速排序法-程序员宅基地

文章浏览阅读2.4k次，点赞41次，收藏13次。java 数据结构与算法 ——快速排序法_快速排序法

推荐文章

热门文章

相关标签