业务数据订正存储过程二
----------------------------------------End here--------------------------------------
WHEN O_ITEMTYPE = 'mobile' AND O_NSTATUS = 82 AND P_ACCOUNTTYPE = '部分到账' AND O_MONEY < O_PARVALUE
--手机充值:充值状态改成功且只有部分到账
THEN
SELECT id,
itemnumber,
createdate,
lastdate,
parvalue,
price,
money,
status_id,
send,
sendcount,
sendtime,
air,
offer_id,
agent_id,
refundmentnumber,
v_orderid,
stat_agent_date,
stat_sky_date
INTO v_item_id,
v_itemnumber,
v_createdate,
v_lastdate,
v_parvalue,
v_price,
v_money,
v_status_id,
v_send,
v_sendcount,
v_sendtime,
v_air,
v_offer_id,
v_agent_id,
v_refundmentnumber,
v_orderid,
v_stat_agent_date,
v_stat_sky_date
FROM tab_topup_orderitem o
WHERE o.itemnumber = O_ITEMNUMBER;
--手机充值:充值状态改成功且只有部分到账
THEN
SELECT id,
itemnumber,
createdate,
lastdate,
parvalue,
price,
money,
status_id,
send,
sendcount,
sendtime,
air,
offer_id,
agent_id,
refundmentnumber,
v_orderid,
stat_agent_date,
stat_sky_date
INTO v_item_id,
v_itemnumber,
v_createdate,
v_lastdate,
v_parvalue,
v_price,
v_money,
v_status_id,
v_send,
v_sendcount,
v_sendtime,
v_air,
v_offer_id,
v_agent_id,
v_refundmentnumber,
v_orderid,
v_stat_agent_date,
v_stat_sky_date
FROM tab_topup_orderitem o
WHERE o.itemnumber = O_ITEMNUMBER;
INSERT INTO TAB_DATA_CORRECTION_RECORD (ID,
CREATEDATE,
LASTDATE,
ITEMNUMBER,
PARVALUE,
PRICE,
SEND,
SENDCOUNT,
SENDTIME,
MONEY,
STATUS_ID,
AIR,
OFFER_ID,
AGENT_ID,
REFUNDMENTNUMBER,
CORRECTIONDATE)
VALUES (SEQ_DATA_CORRECTION_RECORD.NEXTVAL,
v_createdate,
v_lastdate,
v_itemnumber,
v_parvalue,
v_price,
v_send,
v_sendcount,
v_sendtime,
v_money,
v_status_id,
v_air,
v_offer_id,
v_agent_id,
v_refundmentnumber,
SYSDATE);
COMMIT;
UPDATE tab_topup_orderitem
SET price = O_MONEY, status_id = O_NSTATUS, llprice = O_MONEY
WHERE itemnumber = O_ITEMNUMBER;
SET price = O_MONEY, status_id = O_NSTATUS, llprice = O_MONEY
WHERE itemnumber = O_ITEMNUMBER;
COMMIT;
UPDATE tab_topup_orderitem
SET successdate = SYSDATE
WHERE itemnumber = O_ITEMNUMBER;
SET successdate = SYSDATE
WHERE itemnumber = O_ITEMNUMBER;
COMMIT;
--检查订单有否有对应的代充商,没有则指定代充商
------------------------------------------Start here---------------------------------
SELECT '%' || O_LOGOGRAM || '%' INTO v_offername FROM DUAL;
SELECT id
INTO v_group_id
FROM tab_base_group
WHERE CTYPE = 'Supply' AND logogram LIKE v_offername;
INTO v_group_id
FROM tab_base_group
WHERE CTYPE = 'Supply' AND logogram LIKE v_offername;
IF v_group_id = 2283 AND v_offer_id IS NULL
THEN
UPDATE tab_topup_orderitem
SET LIANLIAN = 1, AIR = 1, OFFER_ID = 2283, FFERRES = 1
WHERE itemnumber = O_ITEMNUMBER;
THEN
UPDATE tab_topup_orderitem
SET LIANLIAN = 1, AIR = 1, OFFER_ID = 2283, FFERRES = 1
WHERE itemnumber = O_ITEMNUMBER;
COMMIT;
ELSIF v_group_id != 2283 AND v_offer_id IS NULL
THEN
UPDATE tab_topup_orderitem
SET AIR = 1, OFFER_ID = v_group_id, FFERRES = 1
WHERE itemnumber = O_ITEMNUMBER;
ELSIF v_group_id != 2283 AND v_offer_id IS NULL
THEN
UPDATE tab_topup_orderitem
SET AIR = 1, OFFER_ID = v_group_id, FFERRES = 1
WHERE itemnumber = O_ITEMNUMBER;
COMMIT;
END IF;
END IF;
------------------------------------------End here------------------------------------
--如果订单在资金流水中没有向代充商支付的记录，则加入支付记录
------------------------------------------Start here-------------------------------
SELECT COUNT ( * )
INTO v_count
FROM tab_base_moneylist a,
tab_topup_moneylist b,
tab_topup_orderitem o
WHERE a.id = b.moneylist_id
AND o.id = b.item_id
AND a.tradetype_id = 338
AND o.itemnumber = O_ITEMNUMBER;
------------------------------------------Start here-------------------------------
SELECT COUNT ( * )
INTO v_count
FROM tab_base_moneylist a,
tab_topup_moneylist b,
tab_topup_orderitem o
WHERE a.id = b.moneylist_id
AND o.id = b.item_id
AND a.tradetype_id = 338
AND o.itemnumber = O_ITEMNUMBER;
IF v_count IS NULL
THEN
v_count := 0;
END IF;
THEN
v_count := 0;
END IF;
IF v_count < 1
THEN
SP_MONEYLIST_ADD_PAYRECORD (O_ITEMNUMBER, O_MONEY);
END IF;
------------------------------------------End here------------------------------------
-- 如果订单之前有成功或失败过,并且在报表中已经统计过,需要改失败或成功的,则需要在订正表中插入一笔记录
IF O_OSTATUS = 81
AND (v_stat_agent_date IS NOT NULL OR v_stat_sky_date IS NOT NULL)
THEN
SELECT (v_money / v_parvalue) * O_MONEY
INTO v_refundprice
FROM DUAL;
INSERT INTO tab_topup_auditorderitem (id,
status_id,
employee_id,
itemnumber,
orderid,
price,
refundmoney,
operator_id,
mobile,
createdate,
lastdate,
auditt,
item_id,
auditor_id,
auditdate,
checkdate,
checked,
statdate,
refundprice,
agent_id)
VALUES (seq_tab_topup_auditorderitem.NEXTVAL,
O_NSTATUS,
NULL,
O_ITEMNUMBER,
v_orderid,
v_parvalue,
v_parvalue,
12887,
'15814462718',
SYSDATE,
SYSDATE,
1,
v_item_id,
12887,
SYSDATE,
NULL,
0,
TRUNC (SYSDATE),
v_refundprice,
v_offer_id);
status_id,
employee_id,
itemnumber,
orderid,
price,
refundmoney,
operator_id,
mobile,
createdate,
lastdate,
auditt,
item_id,
auditor_id,
auditdate,
checkdate,
checked,
statdate,
refundprice,
agent_id)
VALUES (seq_tab_topup_auditorderitem.NEXTVAL,
O_NSTATUS,
NULL,
O_ITEMNUMBER,
v_orderid,
v_parvalue,
v_parvalue,
12887,
'15814462718',
SYSDATE,
SYSDATE,
1,
v_item_id,
12887,
SYSDATE,
NULL,
0,
TRUNC (SYSDATE),
v_refundprice,
v_offer_id);
COMMIT;
END IF;
----------------------------------------End here--------------------------------------
END IF;
----------------------------------------End here--------------------------------------
WHEN O_ITEMTYPE = 'mobile' AND O_NSTATUS = 81 AND P_ACCOUNTTYPE = '没有到账' AND O_MONEY = 0
--手机充值:充值状态改失败,没有到账
THEN
SELECT id,
itemnumber,
createdate,
lastdate,
parvalue,
price,
money,
status_id,
send,
sendcount,
sendtime,
air,
offer_id,
agent_id,
refundmentnumber,
v_orderid,
stat_agent_date,
stat_sky_date
INTO v_item_id,
v_itemnumber,
v_createdate,
v_lastdate,
v_parvalue,
v_price,
v_money,
v_status_id,
v_send,
v_sendcount,
v_sendtime,
v_air,
v_offer_id,
v_agent_id,
v_refundmentnumber,
v_orderid,
v_stat_agent_date,
v_stat_sky_date
FROM tab_topup_orderitem o
WHERE o.itemnumber = O_ITEMNUMBER;
--手机充值:充值状态改失败,没有到账
THEN
SELECT id,
itemnumber,
createdate,
lastdate,
parvalue,
price,
money,
status_id,
send,
sendcount,
sendtime,
air,
offer_id,
agent_id,
refundmentnumber,
v_orderid,
stat_agent_date,
stat_sky_date
INTO v_item_id,
v_itemnumber,
v_createdate,
v_lastdate,
v_parvalue,
v_price,
v_money,
v_status_id,
v_send,
v_sendcount,
v_sendtime,
v_air,
v_offer_id,
v_agent_id,
v_refundmentnumber,
v_orderid,
v_stat_agent_date,
v_stat_sky_date
FROM tab_topup_orderitem o
WHERE o.itemnumber = O_ITEMNUMBER;
INSERT INTO TAB_DATA_CORRECTION_RECORD (ID,
CREATEDATE,
LASTDATE,
ITEMNUMBER,
PARVALUE,
PRICE,
SEND,
SENDCOUNT,
SENDTIME,
MONEY,
STATUS_ID,
AIR,
OFFER_ID,
AGENT_ID,
REFUNDMENTNUMBER,
CORRECTIONDATE)
VALUES (SEQ_DATA_CORRECTION_RECORD.NEXTVAL,
v_createdate,
v_lastdate,
v_itemnumber,
v_parvalue,
v_price,
v_send,
v_sendcount,
v_sendtime,
v_money,
v_status_id,
v_air,
v_offer_id,
v_agent_id,
v_refundmentnumber,
SYSDATE);
COMMIT;
UPDATE tab_topup_orderitem
SET status_id = O_NSTATUS,
successdate = SYSDATE,
send = 0,
sendcount = 0,
sendtime = NULL
WHERE itemnumber = O_ITEMNUMBER;
SET status_id = O_NSTATUS,
successdate = SYSDATE,
send = 0,
sendcount = 0,
sendtime = NULL
WHERE itemnumber = O_ITEMNUMBER;
COMMIT;
--检查订单有否有对应的代充商,没有则指定代充商
------------------------------------------Start here-----------------------------------
SELECT '%' || O_LOGOGRAM || '%' INTO v_offername FROM DUAL;
SELECT id
INTO v_group_id
FROM tab_base_group
WHERE CTYPE = 'Supply' AND logogram LIKE v_offername;
INTO v_group_id
FROM tab_base_group
WHERE CTYPE = 'Supply' AND logogram LIKE v_offername;
IF v_group_id = 2283 AND v_offer_id IS NULL
THEN
UPDATE tab_topup_orderitem
SET LIANLIAN = 1, AIR = 1, OFFER_ID = 2283, FFERRES = 1
WHERE itemnumber = O_ITEMNUMBER;
THEN
UPDATE tab_topup_orderitem
SET LIANLIAN = 1, AIR = 1, OFFER_ID = 2283, FFERRES = 1
WHERE itemnumber = O_ITEMNUMBER;
COMMIT;
ELSIF v_group_id != 2283 AND v_offer_id IS NULL
THEN
UPDATE tab_topup_orderitem
SET AIR = 1, OFFER_ID = v_group_id, FFERRES = 1
WHERE itemnumber = O_ITEMNUMBER;
ELSIF v_group_id != 2283 AND v_offer_id IS NULL
THEN
UPDATE tab_topup_orderitem
SET AIR = 1, OFFER_ID = v_group_id, FFERRES = 1
WHERE itemnumber = O_ITEMNUMBER;
COMMIT;
END IF;
END IF;
------------------------------------------End here--------------------------------------
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/25198367/viewspace-751134/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
业务数据订正存储过程一
下一篇：
业务数据订正存储三
请登录后发表评论
登录
全部评论
