CREATE OR REPLACE PROCEDURE P_CL_PUB_CLEAR_JOUR_FEE(AN_O_RET_CODE OUT NUMBER,
AV_O_RET_MSG OUT VARCHAR2,
AV_I_USER_CODE IN VARCHAR2,
AN_I_CLEAR_DATE IN NUMBER,
AV_I_TRADE_MKT_CODE IN VARCHAR2,
AV_I_CLEAR_TASK_CODE IN VARCHAR2,
AV_I_FUNC_CODE IN VARCHAR2) AS
/******************************************************************
项目名称：E_OSP
所属用户：EA_CLEAR
概要说明：
中文名称：计算费用
语法信息：
用途简述：计算费用
输入参数：
输出参数：AN_O_RET_CODE
AV_O_RET_MSG
AV_I_USER_CODE
AN_I_CLEAR_DATE
AN_I_BATCH_NO:批次号
调用举例：
VARIABLE AN_O_RET_CODE NUMBER;
VARIABLE AV_O_RET_MSG VARCHAR2;
EXEC P_CL_PUB_CLEAR_JOUR_FEE(:AN_O_RET_CODE,:AV_O_RET_MSG,'8888',20120320,1);
功能修订：
简要说明：
修订记录：
修订日期 修订人 修改内容简要说明
---------- ---------- ------------------------------
2012-03-16 JiangHua Create
2012-03-27 caimingtuan Modify
2012-07-05 caimingtuan 费用行转列增加系统标志区分
2012-0801 caimingtuan 资费增加日期区间
******************************************************************/
V_SYSTEM_FLAG VARCHAR2(1) := '1';
BEGIN
AV_O_RET_MSG := '操作成功';
AN_O_RET_CODE := 0;
EXECUTE IMMEDIATE 'TRUNCATE TABLE CL_TEMP_BUSI_JOUR_EXCH_FEE';
EXECUTE IMMEDIATE 'TRUNCATE TABLE CL_TEMP_CLEAR_JOUR_TOTAL_FEE';
EXECUTE IMMEDIATE 'TRUNCATE TABLE CL_TEMP_FEE_RATIO';
--准备资费信息
INSERT INTO CL_TEMP_FEE_RATIO
(FEE_ITEM_ID,
BUSI_TYPE,
TRADE_MKT_CODE,
TRADE_SEC_KIND_CODE,
TRUST_TYPE,
TRADE_DIRECT,
CHARGE_TYPE,
SETTLE_TYPE,
SEC_CODE,
TRADE_TYPE,
CLEAR_TYPE,
IS_UNITE,
CAL_TYPE,
FEE_MODE,
FEE_RATIO_ID,
ASSIST_FEE_ITEM_ID,
FEE_PRECISION,
BEGIN_AMT,
END_AMT,
FEE_AMT,
DISCOUNT,
MIN_AMT,
MAX_AMT,
BEGIN_DATE,
END_DATE)
SELECT FEE_ITEM_ID,
BUSI_TYPE,
TRADE_MKT_CODE,
TRADE_SEC_KIND_CODE,
TRUST_TYPE,
TRADE_DIRECT,
CHARGE_TYPE,
SETTLE_TYPE,
SEC_CODE,
TRADE_TYPE,
CLEAR_TYPE,
IS_UNITE,
CAL_TYPE,
FEE_MODE,
FEE_RATIO_ID,
ASSIST_FEE_ITEM_ID,
FEE_PRECISION,
BEGIN_AMT,
END_AMT,
FEE_AMT,
DISCOUNT,
MIN_AMT,
MAX_AMT,
BEGIN_DATE,
END_DATE
FROM V_STD_FEE_RATIO A
WHERE A.TRADE_MKT_CODE = AV_I_TRADE_MKT_CODE
AND AN_I_CLEAR_DATE BETWEEN BEGIN_DATE AND END_DATE
AND EXISTS (SELECT 1
FROM CL_BR_CLEAR_BUSI_CFG B
WHERE B.BUSI_TYPE = A.BUSI_TYPE
AND B.CL_FUNC_CODE = AV_I_FUNC_CODE);
COMMIT;
--计算有代扣分笔费用
INSERT /*+APPEND*/
INTO CL_TEMP_BUSI_JOUR_EXCH_FEE
(TRADE_BUSI_JOUR_ID, CHARGE_TYPE, OCCUR_AMT, DATA_ROWID)
SELECT A.TRADE_BUSI_JOUR_ID,
C.CHARGE_TYPE,
CASE
WHEN C.FEE_MODE = 0 THEN
(CASE
WHEN C.CAL_TYPE = 1 THEN
C.FEE_AMT + ROUND(ABS(A.EXCH_AMT) * D.FEE_AMT, C.FEE_PRECISION) -
ROUND(ROUND(ROUND(ABS(A.EXCH_AMT), 2) * D.FEE_AMT, D.FEE_PRECISION),
C.FEE_PRECISION)
WHEN C.CAL_TYPE = 2 THEN
C.FEE_AMT + ROUND(ABS(A.EXCH_PER) * D.FEE_AMT, C.FEE_PRECISION) -
ROUND(ROUND(ROUND(A.EXCH_PER, 2) * D.FEE_AMT, D.FEE_PRECISION),
C.FEE_PRECISION)
END)
ELSE
F_CL_GET_MID_VALUE((CASE
WHEN C.CAL_TYPE = 1 THEN
ROUND(ABS(A.EXCH_AMT) * (C.FEE_AMT + D.FEE_AMT), C.FEE_PRECISION) -
ROUND(ROUND(ABS(A.EXCH_AMT), 2) * D.FEE_AMT, D.FEE_PRECISION)
WHEN C.CAL_TYPE = 2 THEN
ROUND(A.EXCH_PER * (C.FEE_AMT + D.FEE_AMT), C.FEE_PRECISION) -
ROUND(ROUND(A.EXCH_PER, 2) * D.FEE_AMT, D.FEE_PRECISION)
END),
C.MIN_AMT,
C.MAX_AMT)
END AS OCCUR_AMT,
ROWIDTOCHAR(A.ROWID) AS DATA_ROWID
FROM CL_TEMP_BUSI_JOUR_EXCH A, CL_TEMP_FEE_RATIO C, CL_TEMP_FEE_RATIO D
WHERE A.TRADE_SEC_KIND_CODE = C.TRADE_SEC_KIND_CODE
AND A.TRADE_MKT_CODE = C.TRADE_MKT_CODE
AND A.BUSI_TYPE = C.BUSI_TYPE
AND A.TRADE_TYPE = C.TRADE_TYPE
AND (C.CLEAR_TYPE IS NULL OR A.CLEAR_TYPE = C.CLEAR_TYPE)
AND (C.SETTLE_TYPE IS NULL OR A.SETTLE_TYPE = C.SETTLE_TYPE)
AND (C.TRADE_DIRECT IS NULL OR A.TRAD
E_DIRECT = C.TRADE_DIRECT)
AND (C.SEC_CODE IS NULL OR A.SEC_CODE = C.SEC_CODE)
AND (ABS((CASE
WHEN C.CAL_TYPE = 1 THEN
A.EXCH_AMT
WHEN C.CAL_TYPE = 2 THEN
A.EXCH_PER
ELSE
0
END)) > C.BEGIN_AMT AND
(ABS(CASE
WHEN C.CAL_TYPE = 1 THEN
A.EXCH_AMT
WHEN C.CAL_TYPE = 2 THEN
A.EXCH_PER
ELSE
0
END) <= C.END_AMT))
AND C.IS_UNITE = 0 --分笔费用
AND C.ASSIST_FEE_ITEM_ID > 0
AND C.ASSIST_FEE_ITEM_ID = D.FEE_ITEM_ID
AND A.TRADE_SEC_KIND_CODE = D.TRADE_SEC_KIND_CODE
AND A.TRADE_MKT_CODE = D.TRADE_MKT_CODE
AND A.BUSI_TYPE = D.BUSI_TYPE
AND A.TRADE_TYPE = D.TRADE_TYPE
AND (D.CLEAR_TYPE IS NULL OR A.CLEAR_TYPE = D.CLEAR_TYPE)
AND (D.SETTLE_TYPE IS NULL OR A.SETTLE_TYPE = D.SETTLE_TYPE)
AND (D.TRADE_DIRECT IS NULL OR A.TRADE_DIRECT = D.TRADE_DIRECT)
AND (D.SEC_CODE IS NULL OR A.SEC_CODE = D.SEC_CODE)
AND (ABS((CASE
WHEN D.CAL_TYPE = 1 THEN
A.EXCH_AMT
WHEN D.CAL_TYPE = 2 THEN
A.EXCH_PER
ELSE
0
END)) > D.BEGIN_AMT AND
(ABS(CASE
WHEN D.CAL_TYPE = 1 THEN
A.EXCH_AMT
WHEN D.CAL_TYPE = 2 THEN
A.EXCH_PER
ELSE
0
END) <= D.END_AMT))
AND D.IS_UNITE = 0; --分笔费用
COMMIT;
--计算无代扣分笔费用
INSERT /*+APPEND*/
INTO CL_TEMP_BUSI_JOUR_EXCH_FEE
(TRADE_BUSI_JOUR_ID, CHARGE_TYPE, OCCUR_AMT, DATA_ROWID)
SELECT A.TRADE_BUSI_JOUR_ID,
CHARGE_TYPE,
CASE
WHEN C.FEE_MODE = 0 THEN
C.FEE_AMT
ELSE
F_CL_GET_MID_VALUE(ABS(ROUND(ROUND(ABS((CASE
WHEN CAL_TYPE = 1 THEN
A.EXCH_AMT
WHEN CAL_TYPE = 2 THEN
A.EXCH_PER
END)),
2) * C.FEE_AMT,
C.FEE_PRECISION)),
C.MIN_AMT,
C.MAX_AMT)
END AS OCCUR_AMT,
ROWIDTOCHAR(A.ROWID) AS DATA_ROWID
FROM CL_TEMP_BUSI_JOUR_EXCH A, CL_TEMP_FEE_RATIO C
WHERE A.TRADE_SEC_KIND_CODE = C.TRADE_SEC_KIND_CODE
AND A.TRADE_MKT_CODE = C.TRADE_MKT_CODE
AND A.BUSI_TYPE = C.BUSI_TYPE
AND A.TRADE_TYPE = C.TRADE_TYPE
AND (C.CLEAR_TYPE IS NULL OR A.CLEAR_TYPE = C.CLEAR_TYPE)
AND (C.SETTLE_TYPE IS NULL OR A.SETTLE_TYPE = C.SETTLE_TYPE)
AND (C.TRADE_DIRECT IS NULL OR A.TRADE_DIRECT = C.TRADE_DIRECT)
AND (C.SEC_CODE IS NULL OR A.SEC_CODE = C.SEC_CODE)
AND C.IS_UNITE = 0 --分笔费用
AND C.ASSIST_FEE_ITEM_ID = 0
AND (ABS((CASE
WHEN C.CAL_TYPE = 1 THEN
A.EXCH_AMT
WHEN C.CAL_TYPE = 2 THEN
A.EXCH_PER
END)) > C.BEGIN_AMT AND
(ABS(CASE
WHEN C.CAL_TYPE = 1 THEN
A.EXCH_AMT
WHEN C.CAL_TYPE = 2 THEN
A.EXCH_PER
END) <= C.END_AMT));
COMMIT;
--成交费用行列转换
EXECUTE IMMEDIATE 'TRUNCATE TABLE CL_TEMP_CLEAR_JOUR_EXCH_FEE';
P_CL_PUB_FEE_ROW_TO_COL(AN_O_RET_CODE,
AV_O_RET_MSG,
V_SYSTEM_FLAG,
'CL_TEMP_BUSI_JOUR_EXCH_FEE',
'OCCUR_AMT',
'TRADE_BUSI_JOUR_ID,DATA_ROWID',
'TRADE_BUSI_JOUR_ID,DATA_ROWID',
NULL,
'CL_TEMP_CLEAR_JOUR_EXCH_FEE',
'TRADE_BUSI_JOUR_ID,DATA_ROWID');
--生成清算流水成交明细
EXECUTE IMMEDIATE 'TRUNCATE TABLE CL_TEMP_CLEAR_JOUR_EXCH';
INSERT /*+APPEND*/
INTO CL_TEMP_CLEAR_JOUR_EXCH
(TRADE_BUSI_JOUR_ID,
EXCH_NO,
EXCH_DATE,
EXCH_TIME,
EXCH_QTY,
EXCH_PRICE,
SETTLE_PRICE,
EXCH_AMT,
EXCH_NET_AMT,
TRANSACT_FEE,
TRANSFER_FEE,
STAMP_DUTY,
SUP_FEE,
SETTLE_FEE)
SELECT A.TRADE_BUSI_JOUR_ID,
EXCH_NO,
EXCH_DATE,
EXCH_TIME,
EXCH_QTY,
EXCH_PRICE,
SETTLE_PRICE,
EXCH_AMT,
EXCH_NET_AMT,
NVL(TRANSACT_FEE, 0),
NVL(TRANSFER_FEE, 0),
NVL(STAMP_DUTY, 0),
NVL(SUP_FEE, 0),
NVL(SETTLE_FEE, 0)
FROM CL_TEMP_BUSI_JOUR_EXCH A, CL_TEMP_CLEAR_JOUR_EXCH_FEE B
WHERE ROWIDTOCHAR(A.ROWID) = B.DATA_ROWID(+);
COMMIT;
EXECUTE IMMEDIATE 'TRUNCATE TABLE CL_TEMP_BUSI_JOUR
_FEE';
--计算无代扣合笔费用
INSERT /*+APPEND*/
INTO CL_TEMP_BUSI_JOUR_FEE
(TRADE_BUSI_JOUR_ID,
EXCH_DATE,
EXCH_QTY,
EXCH_AMT,
EXCH_NET_AMT,
CHARGE_TYPE,
OCCUR_AMT)
SELECT TRADE_BUSI_JOUR_ID,
EXCH_DATE,
EXCH_QTY,
EXCH_AMT,
EXCH_NET_AMT,
CHARGE_TYPE,
CASE
WHEN C.FEE_MODE = 0 THEN
C.FEE_AMT
ELSE
F_CL_GET_MID_VALUE(ABS(ROUND(ROUND(ABS((CASE
WHEN CAL_TYPE = 1 THEN
B.EXCH_AMT
WHEN CAL_TYPE = 2 THEN
B.EXCH_PER
END)),
2) * C.FEE_AMT,
C.FEE_PRECISION)),
C.MIN_AMT,
C.MAX_AMT)
END AS OCCUR_AMT
FROM (SELECT TRADE_BUSI_JOUR_ID,
MIN(EXCH_DATE) AS EXCH_DATE,
SUM(EXCH_PER) AS EXCH_PER,
SUM(EXCH_QTY) AS EXCH_QTY,
SUM(EXCH_AMT) AS EXCH_AMT,
SUM(EXCH_NET_AMT) AS EXCH_NET_AMT,
MIN(TRADE_SEC_KIND_CODE) AS TRADE_SEC_KIND_CODE,
MIN(TRADE_MKT_CODE) AS TRADE_MKT_CODE,
MIN(BUSI_TYPE) AS BUSI_TYPE,
MIN(TRADE_TYPE) AS TRADE_TYPE,
MIN(SEC_CODE) AS SEC_CODE,
MIN(TRADE_DIRECT) AS TRADE_DIRECT,
MIN(CLEAR_TYPE) AS CLEAR_TYPE,
MIN(SETTLE_OBJECT) AS SETTLE_OBJECT,
MIN(SETTLE_TYPE) AS SETTLE_TYPE
FROM CL_TEMP_BUSI_JOUR_EXCH T
WHERE EXISTS
(SELECT 1
FROM V_CL_FEE_ITEM D
WHERE T.TRADE_SEC_KIND_CODE = D.TRADE_SEC_KIND_CODE
AND T.TRADE_MKT_CODE = D.TRADE_MKT_CODE
AND T.BUSI_TYPE = D.BUSI_TYPE
AND T.TRADE_TYPE = D.TRADE_TYPE
AND (D.CLEAR_TYPE IS NULL OR T.CLEAR_TYPE = D.CLEAR_TYPE)
AND (D.SETTLE_TYPE IS NULL OR T.SETTLE_TYPE = D.SETTLE_TYPE)
AND (D.TRADE_DIRECT IS NULL OR T.TRADE_DIRECT = D.TRADE_DIRECT)
AND (D.SEC_CODE IS NULL OR T.SEC_CODE = D.SEC_CODE)
AND D.IS_UNITE = 1 --合笔费用
AND D.ASSIST_FEE_ITE
M_ID = 0)
GROUP BY TRADE_BUSI_JOUR_ID) B,
CL_TEMP_FEE_RATIO C
WHERE B.TRADE_SEC_KIND_CODE = C.TRADE_SEC_KIND_CODE
AND B.TRADE_MKT_CODE = C.TRADE_MKT_CODE
AND B.BUSI_TYPE = C.BUSI_TYPE
AND B.TRADE_TYPE = C.TRADE_TYPE
AND (C.CLEAR_TYPE IS NULL OR B.CLEAR_TYPE = C.CLEAR_TYPE)
AND (C.SETTLE_TYPE IS NULL OR B.SETTLE_TYPE = C.SETTLE_TYPE)
AND (C.TRADE_DIRECT IS NULL OR B.TRADE_DIRECT = C.TRADE_DIRECT)
AND (C.SEC_CODE IS NULL OR B.SEC_CODE = C.SEC_CODE)
AND C.IS_UNITE = 1 --合笔费用
AND C.ASSIST_FEE_ITEM_ID = 0
AND (ABS((CASE
WHEN C.CAL_TYPE = 1 THEN
B.EXCH_AMT
WHEN C.CAL_TYPE = 2 THEN
B.EXCH_PER
ELSE
0
END)) > C.BEGIN_AMT AND
(ABS(CASE
WHEN C.CAL_TYPE = 1 THEN
B.EXCH_AMT
WHEN C.CAL_TYPE = 2 THEN
B.EXCH_PER
ELSE
0
END) <= C.END_AMT));
COMMIT;
--流水合笔费用行列转换
EXECUTE IMMEDIATE 'TRUNCATE TABLE CL_TEMP_CLEAR_JOUR_FEE';
P_CL_PUB_FEE_ROW_TO_COL(AN_O_RET_CODE,
AV_O_RET_MSG,
V_SYSTEM_FLAG,
'CL_TEMP_BUSI_JOUR_FEE',
'OCCUR_AMT',
'TRADE_BUSI_JOUR_ID,EXCH_DATE',
'TRADE_BUSI_JOUR_ID, EXCH_DATE, MIN(EXCH_QTY),MIN(EXCH_AMT), MIN(EXCH_NET_AMT)',
NULL,
'CL_TEMP_CLEAR_JOUR_FEE',
'TRADE_BUSI_JOUR_ID, EXCH_DATE, EXCH_QTY, EXCH_AMT, EXCH_NET_AMT');
--费用合计
INSERT /*+APPEND*/
INTO CL_TEMP_CLEAR_JOUR_TOTAL_FEE
(TRADE_BUSI_JOUR_ID,
CLEAR_DATE,
EXCH_DATE,
EXCH_QTY,
EXCH_AMT,
EXCH_NET_AMT,
TRANSACT_FEE,
TRANSFER_FEE,
STAMP_DUTY,
SUP_FEE,
SETTLE_FEE)
SELECT TRADE_BUSI_JOUR_ID,
AN_I_CLEAR_DATE,
MIN(EXCH_DATE) AS EXCH_DATE,
MAX(EXCH_QTY) AS EXCH_QTY,
MAX(EXCH_AMT) AS EXCH_AMT,
MAX(EXCH_NET_AMT) AS EXCH_NET_AMT,
SUM(TRANSACT_FEE) AS TRANSACT_FEE,
SUM(TRANSFER_FEE) AS TRANSFER_FEE,
SUM(STAMP_DUTY) AS STAMP_DUTY,
SUM(SUP_FEE) AS SUP_FEE,
SUM(SETTLE_FEE) AS SETTLE_FEE
FROM (SELECT TRADE_BUSI_JOUR_ID,
MIN(EXCH_DATE) AS EXCH_DATE,
SUM(EXCH_QTY) AS EXCH_QTY,
SUM(EXCH_AMT) AS EXCH_AMT,
SUM(EXCH_NET_AMT) AS EXCH_NET_AMT,
ROUND(SUM(TRANSACT_FEE), 2) AS TRANSACT_FEE,
ROUND(SUM(TRANSFER_FEE), 2) AS TRANSFER_FEE,
ROUND(SUM(STAMP_DUTY), 2) AS STAMP_DUTY,
ROUND(SUM(SUP_FEE), 2) AS SUP_FEE,
ROUND(SUM(SETTLE_FEE), 2) AS SETTLE_FEE
FROM CL_TEMP_CLEAR_JOUR_EXCH A
GROUP BY A.TRADE_BUSI_JOUR_ID
UNION ALL
SELECT B.TRADE_BUSI_JOUR_ID,
EXCH_DATE,
B.EXCH_QTY,
B.EXCH_AMT,
B.EXCH_NET_AMT,
ROUND(B.TRANSACT_FEE, 2),
ROUND(B.TRANSFER_FEE, 2),
ROUND(B.STAMP_DUTY, 2),
ROUND(B.SUP_FEE, 2),
ROUND(B.SETTLE_FEE, 2)
FROM CL_TEMP_CLEAR_JOUR_FEE B)
GROUP BY TRADE_BUSI_JOUR_ID;
COMMIT;
EXECUTE IMMEDIATE 'TRUNCATE TABLE CL_TEMP_BUSI_JOUR_EXCH_FEE';
EXECUTE IMMEDIATE 'TRUNCATE TABLE CL_TEMP_CLEAR_JOUR_EXCH_FEE';
EXECUTE IMMEDIATE 'TRUNCATE TABLE CL_TEMP_BUSI_JOUR_FEE';
EXECUTE IMMEDIATE 'TRUNCATE TABLE CL_TEMP_FEE_RATIO';
EXCEPTION
WHEN OTHERS THEN
AN_O_RET_CODE := -1;
AV_O_RET_MSG := PKG_CL_PUB_UTILS.F_GET_ERR_MSG(SQLCODE, SQLERRM);
END P_CL_PUB_CLEAR_JOUR_FEE;
/