-- 取得本行交易单位编号 商户名称 OPEN CUR_CARD_S FOR SELECT DISTINCT TYDWNO, TYDWNAME FROM TEMP_BFHRPPOS WHERE WORKDATE = P_WORKDATE; LOOP FETCH CUR_CARD_S INTO E_MERCHANT_NO, E_CONTACT; EXIT WHEN CUR_CARD_S%NOTFOUND;
-- 取得跨行交易单位名称 商户名称 OPEN CUR_CARD_C FOR SELECT DISTINCT TYDWNO, TYDWNAME FROM TEMP_BFHPOSLC WHERE WORKDATE = P_WORKDATE; LOOP FETCH CUR_CARD_C INTO E_MERCHANT_NO, E_CONTACT; EXIT WHEN CUR_CARD_C%NOTFOUND; BEGIN
-- 日交易金额2860 日交易笔数 SELECT SUM(TO_NUMBER(AMOUNT)), TO_NUMBER(COUNT(TYDWNO)) INTO E_OT_AMOUNT, E_OT_BS FROM TEMP_BFHPOSLC WHERE TYDWNAME = E_CONTACT AND WORKDATE = P_WORKDATE AND TYDWNO = E_MERCHANT_NO; IF (E_OT_BS = 0) THEN E_OT_AMOUNT := 0; END IF;
-- 大额交易笔数 大额交易金额 SELECT TO_NUMBER(COUNT(TYDWNO)), SUM(TO_NUMBER(AMOUNT)) INTO E_BIG_OT_BS, E_BIG_OT_AMOUNT FROM TEMP_BFHPOSLC WHERE TYDWNAME = E_CONTACT AND WORKDATE = P_WORKDATE AND TO_NUMBER(AMOUNT) > TO_NUMBER(E_LAGER_POINT) AND TYDWNO = E_MERCHANT_NO; IF (E_BIG_OT_BS = 0) THEN E_BIG_OT_AMOUNT := 0; END IF;
-- 整数交易笔数 SELECT TO_NUMBER(COUNT(TYDWNO)), SUM(TO_NUMBER(AMOUNT)) INTO E_INT_OT_BS, E_INT_OT_AMOUNT FROM TEMP_BFHPOSLC WHERE TYDWNAME = E_CONTACT AND WORKDATE = P_WORKDATE AND to_number(AMOUNT) > TO_NUMBER(E_INTEGER_POINT) AND MOD(to_number(AMOUNT), 10000) = 0 AND TYDWNO = E_MERCHANT_NO; IF (E_INT_OT_BS = 0) THEN E_INT_OT_AMOUNT := 0; END IF;
-- 撤销笔数 撤销金额2865 SELECT TO_NUMBER(COUNT(TYDWNO)), SUM(TO_NUMBER(AMOUNT)) INTO E_WD_BS, E_WD_AMOUNT FROM TEMP_BFHPOSLC WHERE TYDWNAME = E_CONTACT AND WORKDATE = P_WORKDATE AND TYDWNO = E_MERCHANT_NO AND TRXCODE = '2865'; IF (E_WD_BS = 0) THEN E_WD_AMOUNT := 0; END IF;
-- 退货笔数 退货金额2867 SELECT TO_NUMBER(COUNT(TYDWNO)), SUM(TO_NUMBER(AMOUNT)) INTO E_RETURN_BS, E_RETURN_AMOUNT FROM TEMP_BFHPOSLC WHERE TYDWNAME = E_CONTACT AND WORKDATE = P_WORKDATE AND TYDWNO = E_MERCHANT_NO AND TRXCODE = '2867'; IF (E_RETURN_BS = 0) THEN E_RETURN_AMOUNT := 0; END IF;
-- 信用卡笔数 信用卡金额 SELECT TO_NUMBER(COUNT(TYDWNO)), SUM(TO_NUMBER(AMOUNT)) INTO E_CREDIT_BS, E_CREDIT_AMOUNT FROM TEMP_BFHPOSLC WHERE TYDWNAME = E_CONTACT AND WORKDATE = P_WORKDATE AND TYDWNO = E_MERCHANT_NO AND CARDKIND <> '000'; IF (E_CREDIT_BS = 0) THEN E_CREDIT_AMOUNT := 0; END IF;
-- 借记卡笔数 借记卡金额 SELECT TO_NUMBER(COUNT(TYDWNO)), SUM(TO_NUMBER(AMOUNT)) INTO E_DEBIT_CARD_BS, E_DEBIT_CARD_AMOUNT FROM TEMP_BFHPOSLC WHERE TYDWNAME = E_CONTACT AND WORKDATE = P_WORKDATE AND TYDWNO = E_MERCHANT_NO AND CARDKIND = '000'; IF (E_DEBIT_CARD_BS = 0) THEN E_DEBIT_CARD_AMOUNT := 0; END IF; EXCEPTION WHEN NO_DATA_FOUND THEN NULL; WHEN TOO_MANY_ROWS THEN NULL; WHEN OTHERS THEN NULL; END;
TYPE CARD_CURSOR IS REF CURSOR;
CUR_CARD_C CARD_CURSOR;
CUR_CARD_S CARD_CURSOR; E_OT_AMOUNT NUMBER(18, 2); -- 日交易金额2860
E_CONTACT VARCHAR2(60); -- 商户名称
E_MERCHANT_NO VARCHAR2(20); -- 商户编号
E_OT_BS NUMBER(10); -- 日交易笔数
E_BIG_OT_BS NUMBER(10); -- 大额交易笔数
E_BIG_OT_AMOUNT NUMBER(18, 2); -- 大额交易金额
E_INT_OT_BS NUMBER(10); -- 整数交易笔数
E_INT_OT_AMOUNT NUMBER(18, 2); -- 整数交易金额
E_WD_BS NUMBER(10); -- 撤销笔数
E_WD_AMOUNT NUMBER(18, 2); -- 撤销金额2865
E_RETURN_BS NUMBER(10); -- 退货笔数
E_RETURN_AMOUNT NUMBER(18, 2); -- 退货金额2867
E_CREDIT_BS NUMBER(10); -- 信用卡笔数
E_CREDIT_AMOUNT NUMBER(18, 2); -- 信用卡金额
E_DEBIT_CARD_BS NUMBER(10); -- 借记卡笔数
E_DEBIT_CARD_AMOUNT NUMBER(18, 2); -- 借记卡金额
E_LAGER_POINT VARCHAR2(18); -- 大额起点
E_INTEGER_POINT VARCHAR2(18); -- 整数起点BEGIN
--判断表中是否有数据 若有则删除 DELETE FROM TRANSACTION_SUMMARY
WHERE exists (select 1 from TRANSACTION_SUMMARY WHERE ROWNUM = 1); -- 取得大额交易起点参数
SELECT LARGE_POINT * 100 INTO E_LAGER_POINT FROM FXPC_PARAMETER;
-- 取得整数交易起点参数
SELECT INTEGER_POINT * 100 INTO E_INTEGER_POINT FROM FXPC_PARAMETER;
-- 取得本行交易单位编号 商户名称
OPEN CUR_CARD_S FOR
SELECT DISTINCT TYDWNO, TYDWNAME
FROM TEMP_BFHRPPOS
WHERE WORKDATE = P_WORKDATE;
LOOP
FETCH CUR_CARD_S
INTO E_MERCHANT_NO, E_CONTACT;
EXIT WHEN CUR_CARD_S%NOTFOUND;
-- 取得跨行交易单位名称 商户名称 OPEN CUR_CARD_C FOR
SELECT DISTINCT TYDWNO, TYDWNAME
FROM TEMP_BFHPOSLC
WHERE WORKDATE = P_WORKDATE;
LOOP
FETCH CUR_CARD_C
INTO E_MERCHANT_NO, E_CONTACT;
EXIT WHEN CUR_CARD_C%NOTFOUND;
BEGIN
-- 日交易金额2860 日交易笔数
SELECT SUM(TO_NUMBER(AMOUNT)), TO_NUMBER(COUNT(TYDWNO))
INTO E_OT_AMOUNT, E_OT_BS
FROM TEMP_BFHPOSLC
WHERE TYDWNAME = E_CONTACT
AND WORKDATE = P_WORKDATE
AND TYDWNO = E_MERCHANT_NO;
IF (E_OT_BS = 0) THEN
E_OT_AMOUNT := 0;
END IF;
-- 大额交易笔数 大额交易金额
SELECT TO_NUMBER(COUNT(TYDWNO)), SUM(TO_NUMBER(AMOUNT))
INTO E_BIG_OT_BS, E_BIG_OT_AMOUNT
FROM TEMP_BFHPOSLC
WHERE TYDWNAME = E_CONTACT
AND WORKDATE = P_WORKDATE
AND TO_NUMBER(AMOUNT) > TO_NUMBER(E_LAGER_POINT)
AND TYDWNO = E_MERCHANT_NO;
IF (E_BIG_OT_BS = 0) THEN
E_BIG_OT_AMOUNT := 0;
END IF;
-- 整数交易笔数
SELECT TO_NUMBER(COUNT(TYDWNO)), SUM(TO_NUMBER(AMOUNT))
INTO E_INT_OT_BS, E_INT_OT_AMOUNT
FROM TEMP_BFHPOSLC
WHERE TYDWNAME = E_CONTACT
AND WORKDATE = P_WORKDATE
AND to_number(AMOUNT) > TO_NUMBER(E_INTEGER_POINT)
AND MOD(to_number(AMOUNT), 10000) = 0
AND TYDWNO = E_MERCHANT_NO;
IF (E_INT_OT_BS = 0) THEN
E_INT_OT_AMOUNT := 0;
END IF;
-- 撤销笔数 撤销金额2865
SELECT TO_NUMBER(COUNT(TYDWNO)), SUM(TO_NUMBER(AMOUNT))
INTO E_WD_BS, E_WD_AMOUNT
FROM TEMP_BFHPOSLC
WHERE TYDWNAME = E_CONTACT
AND WORKDATE = P_WORKDATE
AND TYDWNO = E_MERCHANT_NO
AND TRXCODE = '2865';
IF (E_WD_BS = 0) THEN
E_WD_AMOUNT := 0;
END IF;
-- 退货笔数 退货金额2867
SELECT TO_NUMBER(COUNT(TYDWNO)), SUM(TO_NUMBER(AMOUNT))
INTO E_RETURN_BS, E_RETURN_AMOUNT
FROM TEMP_BFHPOSLC
WHERE TYDWNAME = E_CONTACT
AND WORKDATE = P_WORKDATE
AND TYDWNO = E_MERCHANT_NO
AND TRXCODE = '2867';
IF (E_RETURN_BS = 0) THEN
E_RETURN_AMOUNT := 0;
END IF;
-- 信用卡笔数 信用卡金额
SELECT TO_NUMBER(COUNT(TYDWNO)), SUM(TO_NUMBER(AMOUNT))
INTO E_CREDIT_BS, E_CREDIT_AMOUNT
FROM TEMP_BFHPOSLC
WHERE TYDWNAME = E_CONTACT
AND WORKDATE = P_WORKDATE
AND TYDWNO = E_MERCHANT_NO
AND CARDKIND <> '000';
IF (E_CREDIT_BS = 0) THEN
E_CREDIT_AMOUNT := 0;
END IF;
-- 借记卡笔数 借记卡金额
SELECT TO_NUMBER(COUNT(TYDWNO)), SUM(TO_NUMBER(AMOUNT))
INTO E_DEBIT_CARD_BS, E_DEBIT_CARD_AMOUNT
FROM TEMP_BFHPOSLC
WHERE TYDWNAME = E_CONTACT
AND WORKDATE = P_WORKDATE
AND TYDWNO = E_MERCHANT_NO
AND CARDKIND = '000';
IF (E_DEBIT_CARD_BS = 0) THEN
E_DEBIT_CARD_AMOUNT := 0;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN TOO_MANY_ROWS THEN
NULL;
WHEN OTHERS THEN
NULL;
END;
-- 插入数据
INSERT INTO TRANSACTION_SUMMARY
(OT_AMOUNT, -- 日交易金额
MERCHANT, -- 商户名称
MERCHANT_NO, -- 商户编号
OT_BS, -- 日交易笔数
BIG_OT_BS, -- 大额交易笔数
BIG_OT_AMOUNT, -- 大额交易金额
INT_OT_BS, -- 整数交易笔数
INT_OT_AMOUNT, -- 整数交易金额
WD_BS, -- 撤销笔数
WD_AMOUNT, -- 撤销金额
RETURN_BS, -- 退货笔数
RETURN_AMOUNT, -- 退货金额
CREDIT_BS, -- 信用卡笔数
CREDIT_AMOUNT, -- 信用卡金额
DEBIT_CARD_BS, -- 借记卡笔数
DEBIT_CARD_AMOUNT, -- 借记卡金额
TRANSACTION_DATE) -- 交易日期
VALUES
(E_OT_AMOUNT / 100,
E_CONTACT,
E_MERCHANT_NO,
E_OT_BS,
E_BIG_OT_BS,
E_BIG_OT_AMOUNT / 100,
E_INT_OT_BS,
E_INT_OT_AMOUNT / 100,
E_WD_BS,
E_WD_AMOUNT / 100,
E_RETURN_BS,
E_RETURN_AMOUNT / 100,
E_CREDIT_BS,
E_CREDIT_AMOUNT / 100,
E_DEBIT_CARD_BS,
E_DEBIT_CARD_AMOUNT / 100,
P_WORKDATE);
END LOOP;
CLOSE CUR_CARD_C;