PROCEDURE sp_finance_supply_audit (currentdate IN DATE, numdays IN NUMBER)
AS
v_parvalue NUMBER (10); --交易面额
v_price NUMBER (19); --结算金额
v_price_66 NUMBER (19); --结算金额成功或小充大部分
v_price_49 NUMBER (19); --结算金额大充小部分
v_operator_id NUMBER (19); --充值类型(移动，联通，电信)
v_supply_id NUMBER (19); -- 商户id
v_total_parvalue NUMBER (19); --充值面额
v_inmoney NUMBER (19); --进货成本
v_outmoney NUMBER (19); --出货价格
v_statdate DATE; --统计时间
v_date DATE; --创建更新时间
auditbegindate DATE; --审核开始日期
auditenddate DATE; --审核结束日期
BEGIN
v_parvalue := 0; --交易面额
v_price := 0; --结算金额
v_price_66 := 0; --结算金额成功或小充大部分
v_price_49 := 0; --结算金额大充小部分
v_operator_id := 1000; --充值类型(移动，联通，电信)
v_supply_id := 0; --商户id
v_total_parvalue := 0; --充值面额
v_inmoney := 0; --进货成本
v_outmoney := 0; --出货价格
v_statdate := TO_DATE ('2012-05-30', 'YYYY-MM-DD'); --统计时间
v_date := TO_DATE ('2012-05-30', 'YYYY-MM-DD'); --创建更新时间
auditbegindate := TO_DATE ('2012-05-30', 'YYYY-MM-DD'); --审核开始日期
auditenddate := TO_DATE ('2012-05-30', 'YYYY-MM-DD'); --审核结束日期
AS
v_parvalue NUMBER (10); --交易面额
v_price NUMBER (19); --结算金额
v_price_66 NUMBER (19); --结算金额成功或小充大部分
v_price_49 NUMBER (19); --结算金额大充小部分
v_operator_id NUMBER (19); --充值类型(移动，联通，电信)
v_supply_id NUMBER (19); -- 商户id
v_total_parvalue NUMBER (19); --充值面额
v_inmoney NUMBER (19); --进货成本
v_outmoney NUMBER (19); --出货价格
v_statdate DATE; --统计时间
v_date DATE; --创建更新时间
auditbegindate DATE; --审核开始日期
auditenddate DATE; --审核结束日期
BEGIN
v_parvalue := 0; --交易面额
v_price := 0; --结算金额
v_price_66 := 0; --结算金额成功或小充大部分
v_price_49 := 0; --结算金额大充小部分
v_operator_id := 1000; --充值类型(移动，联通，电信)
v_supply_id := 0; --商户id
v_total_parvalue := 0; --充值面额
v_inmoney := 0; --进货成本
v_outmoney := 0; --出货价格
v_statdate := TO_DATE ('2012-05-30', 'YYYY-MM-DD'); --统计时间
v_date := TO_DATE ('2012-05-30', 'YYYY-MM-DD'); --创建更新时间
auditbegindate := TO_DATE ('2012-05-30', 'YYYY-MM-DD'); --审核开始日期
auditenddate := TO_DATE ('2012-05-30', 'YYYY-MM-DD'); --审核结束日期
--审核开始日期
SELECT TRUNC (currentdate - numdays) INTO auditbegindate FROM DUAL;
--审核结束日期
SELECT TRUNC (currentdate - numdays + 1) INTO auditenddate FROM DUAL;
SELECT TRUNC (currentdate - numdays + 1) INTO auditenddate FROM DUAL;
--创建更新时间
SELECT currentdate - numdays + 1 INTO v_date FROM DUAL;
SELECT currentdate - numdays + 1 INTO v_date FROM DUAL;
--统计时间
SELECT TRUNC (currentdate - numdays) INTO v_statdate FROM DUAL;
SELECT TRUNC (currentdate - numdays) INTO v_statdate FROM DUAL;
--定义游标(充值卡面额,进货金额)
DECLARE
CURSOR supply_audit
IS
SELECT SUM (ci.parvalue),
cp.supply_id,
ci.operator_id,
ci.parvalue
FROM tab_topup_mobile_cardpay cp,
tab_topup_mobile_cardpay_item cpi,
tab_topup_mobile_carditem ci
WHERE cp.id = cpi.pay_id AND cpi.carditem_id = ci.id
AND ( ci.status_id = 66
OR ci.status_id = 48
OR ci.status_id = 49
OR ci.status_id = 47)
AND cp.balancepay = 0
AND cp.stat_date IS NULL
AND cp.finisheddate >= auditbegindate
AND cp.finisheddate < auditenddate
GROUP BY cp.supply_id, ci.operator_id, ci.parvalue;
--开始使用游标取数据
CURSOR supply_audit
IS
SELECT SUM (ci.parvalue),
cp.supply_id,
ci.operator_id,
ci.parvalue
FROM tab_topup_mobile_cardpay cp,
tab_topup_mobile_cardpay_item cpi,
tab_topup_mobile_carditem ci
WHERE cp.id = cpi.pay_id AND cpi.carditem_id = ci.id
AND ( ci.status_id = 66
OR ci.status_id = 48
OR ci.status_id = 49
OR ci.status_id = 47)
AND cp.balancepay = 0
AND cp.stat_date IS NULL
AND cp.finisheddate >= auditbegindate
AND cp.finisheddate < auditenddate
GROUP BY cp.supply_id, ci.operator_id, ci.parvalue;
--开始使用游标取数据
BEGIN
OPEN supply_audit;
OPEN supply_audit;
LOOP
FETCH supply_audit
INTO v_total_parvalue, v_supply_id, v_operator_id, v_parvalue;
FETCH supply_audit
INTO v_total_parvalue, v_supply_id, v_operator_id, v_parvalue;
--游标取不到数据则退出
EXIT WHEN supply_audit%NOTFOUND;
EXIT WHEN supply_audit%NOTFOUND;
--结算面额 充值卡状态正常+成功金额小于申报金额
SELECT SUM (ci.price)
INTO v_price_66
FROM tab_topup_mobile_cardpay cp,
tab_topup_mobile_cardpay_item cpi,
tab_topup_mobile_carditem ci
WHERE cp.id = cpi.pay_id AND cpi.carditem_id = ci.id
AND ( ci.status_id = 66
OR ci.status_id = 47
OR ci.status_id = 48)
AND cp.status_id = 66
AND cp.balancepay = 0
AND cp.stat_date IS NULL
AND cp.finisheddate >= auditbegindate
AND cp.finisheddate < auditenddate
AND cp.supply_id = v_supply_id
AND ci.operator_id = v_operator_id
AND ci.parvalue = v_parvalue;
SELECT SUM (ci.price)
INTO v_price_66
FROM tab_topup_mobile_cardpay cp,
tab_topup_mobile_cardpay_item cpi,
tab_topup_mobile_carditem ci
WHERE cp.id = cpi.pay_id AND cpi.carditem_id = ci.id
AND ( ci.status_id = 66
OR ci.status_id = 47
OR ci.status_id = 48)
AND cp.status_id = 66
AND cp.balancepay = 0
AND cp.stat_date IS NULL
AND cp.finisheddate >= auditbegindate
AND cp.finisheddate < auditenddate
AND cp.supply_id = v_supply_id
AND ci.operator_id = v_operator_id
AND ci.parvalue = v_parvalue;
IF v_price_66 IS NULL
THEN
v_price_66 := 0;
END IF;
THEN
v_price_66 := 0;
END IF;
--结算面额 充值卡状态:成功金额大于申报金额
SELECT SUM (ci.parvalue)
INTO v_price_49
FROM tab_topup_mobile_cardpay cp,
tab_topup_mobile_cardpay_item cpi,
tab_topup_mobile_carditem ci
WHERE cp.id = cpi.pay_id
AND cpi.carditem_id = ci.id
AND ci.status_id = 49
AND cp.status_id = 66
AND cp.balancepay = 0
AND cp.stat_date IS NULL
AND cp.finisheddate >= auditbegindate
AND cp.finisheddate < auditenddate
AND cp.supply_id = v_supply_id
AND ci.operator_id = v_operator_id
AND ci.parvalue = v_parvalue;
IF v_price_49 IS NULL
THEN
v_price_49 := 0;
END IF;
THEN
v_price_49 := 0;
END IF;
v_price := v_price_66 + v_price_49;
--进货成本（用户已经使用的）
SELECT SUM (cp.money)
INTO v_inmoney
FROM tab_topup_mobile_cardpay cp,
tab_topup_mobile_cardpay_item cpi,
tab_topup_mobile_carditem ci
WHERE cp.id = cpi.pay_id AND cpi.carditem_id = ci.id
AND ( ci.status_id = 66
OR ci.status_id = 48
OR ci.status_id = 49)
AND cp.status_id = 66
AND cp.stat_date IS NULL
AND ci.operator_id = v_operator_id
AND ci.parvalue = v_parvalue
AND cp.supply_id = v_supply_id
AND cp.finisheddate >= auditbegindate
AND cp.finisheddate < auditenddate;
INTO v_inmoney
FROM tab_topup_mobile_cardpay cp,
tab_topup_mobile_cardpay_item cpi,
tab_topup_mobile_carditem ci
WHERE cp.id = cpi.pay_id AND cpi.carditem_id = ci.id
AND ( ci.status_id = 66
OR ci.status_id = 48
OR ci.status_id = 49)
AND cp.status_id = 66
AND cp.stat_date IS NULL
AND ci.operator_id = v_operator_id
AND ci.parvalue = v_parvalue
AND cp.supply_id = v_supply_id
AND cp.finisheddate >= auditbegindate
AND cp.finisheddate < auditenddate;
IF v_inmoney IS NULL
THEN
v_inmoney := 0;
END IF;
THEN
v_inmoney := 0;
END IF;
--出货金额
SELECT ABS (SUM (bml.money * ci.price / oi.parvalue) * 100)
INTO v_outmoney
FROM tab_base_moneylist bml,
tab_topup_moneylist tml,
tab_topup_orderitem oi,
tab_topup_mobile_carditem ci,
tab_topup_mobile_cardpay_item cpi,
tab_topup_mobile_cardpay cp
WHERE cp.id = cpi.pay_id
AND cpi.carditem_id = ci.id
AND bml.id = tml.moneylist_id
AND tml.item_id = oi.id
AND oi.id = ci.item_id
AND ( ci.status_id = 66
OR ci.status_id = 48
OR ci.status_id = 49)
AND ci.status_id = 66
AND cp.stat_date IS NULL
AND bml.tradetype_id = 225 /* 购物类型*/
AND cp.balancepay = 0
AND cp.supply_id = v_supply_id
AND ci.operator_id = v_operator_id
AND ci.parvalue = v_parvalue
AND cp.finisheddate >= auditbegindate
AND cp.finisheddate < auditenddate;
INTO v_outmoney
FROM tab_base_moneylist bml,
tab_topup_moneylist tml,
tab_topup_orderitem oi,
tab_topup_mobile_carditem ci,
tab_topup_mobile_cardpay_item cpi,
tab_topup_mobile_cardpay cp
WHERE cp.id = cpi.pay_id
AND cpi.carditem_id = ci.id
AND bml.id = tml.moneylist_id
AND tml.item_id = oi.id
AND oi.id = ci.item_id
AND ( ci.status_id = 66
OR ci.status_id = 48
OR ci.status_id = 49)
AND ci.status_id = 66
AND cp.stat_date IS NULL
AND bml.tradetype_id = 225 /* 购物类型*/
AND cp.balancepay = 0
AND cp.supply_id = v_supply_id
AND ci.operator_id = v_operator_id
AND ci.parvalue = v_parvalue
AND cp.finisheddate >= auditbegindate
AND cp.finisheddate < auditenddate;
IF v_outmoney IS NULL
THEN
v_outmoney := 0;
END IF;
THEN
v_outmoney := 0;
END IF;
INSERT INTO tab_topup_stat_supply (id,
GROUP_ID,
parmoney,
actualmoney,
inmoney,
outmoney,
correctpar,
correctprice,
status_id,
batch,
createdate,
lastdate,
operator_id,
parvalue,
statdate)
VALUES (seq_tab_topup_stat_supply.NEXTVAL,
v_supply_id,
v_total_parvalue,
v_price,
v_inmoney,
v_outmoney,
NULL,
NULL,
'',
NULL,
v_date,
v_date,
v_operator_id,
v_parvalue,
v_statdate);
/*
-- 更新统计日期
UPDATE tab_topup_orderitem oi
SET oi.stat_sky_date = v_statdate
WHERE oi.parvalue = v_parvalue
-- and oi.area_id = v_area_id
AND oi.operator_id = v_operator_id
AND oi.status_id = 82
AND oi.stat_sky_date IS NULL
AND oi.createdate >= auditbegindate
AND oi.createdate < auditenddate;
*/
-- 更新统计日期
UPDATE tab_topup_mobile_cardpay cp
SET cp.stat_date = v_statdate
WHERE EXISTS
(SELECT 1
FROM tab_topup_mobile_cardpay_item cpi,
tab_topup_mobile_carditem ci
WHERE cp.id = cpi.pay_id
AND cpi.carditem_id = ci.id
AND ( ci.status_id = 66
OR ci.status_id = 48
OR ci.status_id = 49
OR ci.status_id = 47)
AND cp.balancepay = 0
AND cp.supply_id = v_supply_id
AND ci.operator_id = v_operator_id
AND ci.parvalue = v_parvalue
AND cp.stat_date IS NULL
AND cp.finisheddate >= auditbegindate
AND cp.finisheddate < auditenddate);
-- 更新统计日期
UPDATE tab_topup_orderitem oi
SET oi.stat_sky_date = v_statdate
WHERE oi.parvalue = v_parvalue
-- and oi.area_id = v_area_id
AND oi.operator_id = v_operator_id
AND oi.status_id = 82
AND oi.stat_sky_date IS NULL
AND oi.createdate >= auditbegindate
AND oi.createdate < auditenddate;
*/
-- 更新统计日期
UPDATE tab_topup_mobile_cardpay cp
SET cp.stat_date = v_statdate
WHERE EXISTS
(SELECT 1
FROM tab_topup_mobile_cardpay_item cpi,
tab_topup_mobile_carditem ci
WHERE cp.id = cpi.pay_id
AND cpi.carditem_id = ci.id
AND ( ci.status_id = 66
OR ci.status_id = 48
OR ci.status_id = 49
OR ci.status_id = 47)
AND cp.balancepay = 0
AND cp.supply_id = v_supply_id
AND ci.operator_id = v_operator_id
AND ci.parvalue = v_parvalue
AND cp.stat_date IS NULL
AND cp.finisheddate >= auditbegindate
AND cp.finisheddate < auditenddate);
/*
-- 更新统计日期
UPDATE tab_topup_mobile_cardpay cp,
tab_topup_mobile_cardpay_item cpi,
tab_topup_mobile_carditem ci
SET cp.stat_date = v_statdate
WHERE cp.id = cpi.pay_id AND cpi.carditem_id = ci.id
AND ( ci.status_id = 66
OR ci.status_id = 48
OR ci.status_id = 49)
AND cp.status_id = 66
AND cp.balancepay = 0
AND cp.finisheddate >= auditbegindate
AND cp.finisheddate < auditenddate
AND cp.supply_id = v_supply_id
AND ci.operator_id = v_operator_id
AND ci.parvalue = v_parvalue;
*/
-- 更新统计日期
UPDATE tab_topup_mobile_cardpay cp,
tab_topup_mobile_cardpay_item cpi,
tab_topup_mobile_carditem ci
SET cp.stat_date = v_statdate
WHERE cp.id = cpi.pay_id AND cpi.carditem_id = ci.id
AND ( ci.status_id = 66
OR ci.status_id = 48
OR ci.status_id = 49)
AND cp.status_id = 66
AND cp.balancepay = 0
AND cp.finisheddate >= auditbegindate
AND cp.finisheddate < auditenddate
AND cp.supply_id = v_supply_id
AND ci.operator_id = v_operator_id
AND ci.parvalue = v_parvalue;
*/
COMMIT;
END LOOP;
END LOOP;
CLOSE supply_audit;
END;
--发生异常时返回错误码
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (
'SUPPLY_AUDIT ERROR,PLEASE CHECK OPERATION AGAIN,THANKS!'
);
ROLLBACK;
END sp_finance_supply_audit;
END;
--发生异常时返回错误码
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (
'SUPPLY_AUDIT ERROR,PLEASE CHECK OPERATION AGAIN,THANKS!'
);
ROLLBACK;
END sp_finance_supply_audit;
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25198367/viewspace-734324/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/25198367/viewspace-734324/
扫一扫
2678
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
