// -----------
SELECT * FROM STRNCDTB@WEBPOS WHERE SALE_DATE BETWEEN '20130125' AND '20130206' AND SUBSTR(MS_NO,1,4)='E009' AND CARD_CO='999';
-- STRNCDTB UPDATE
DECLARE
CURSOR CARD_INFO IS
SELECT * FROM TMPSTRNCDTB WHERE MS_NO='E00901' AND SALE_DATE='20130129' AND POS_NO='04' ORDER BY SALE_DATE;
BEGIN
FOR C IN CARD_INFO LOOP
UPDATE STRNCDTB@WEBPOS
SET CARD_CO = C.ORG_CARD_CO
WHERE SALE_DATE = C.SALE_DATE
AND MS_NO = C.MS_NO
AND POS_NO = C.POS_NO
AND BILL_NO = C.BILL_NO
AND VAN_CD = C.VAN_CD
AND CARD_CO = C.CARD_CO;
END LOOP;
END;
--SDCARDTB UPDATE
DECLARE
CURSOR CARD_SUM IS
SELECT MS_NO,SALE_DATE,CARD_CO,APPR_FG,CHAIN_NO,CHAIN_AREA
,NVL(DECODE(SALE_FG,'0',COUNT(*)),0) AS CARD_CNT
,NVL(DECODE(SALE_FG,'0',SUM(APPR_AMT)),0) AS CARD_AMT
,NVL(DECODE(SALE_FG,'1',COUNT(*)),0) AS C_CARD_CNT
,NVL(DECODE(SALE_FG,'1',SUM(APPR_AMT)),0) AS C_CARD_AMT
FROM STRNCDTB WHERE MS_NO='E00901' AND SALE_DATE='20130129' AND POS_NO='04'
GROUP BY MS_NO,SALE_DATE,CARD_CO,APPR_FG,CHAIN_NO,CHAIN_AREA,SALE_FG
;
BEGIN
FOR C IN CARD_SUM LOOP
MERGE INTO SDCARDTB A
USING (SELECT * FROM DUAL) B
ON ( A.MS_NO = C.MS_NO AND A.SALE_DATE = C.SALE_DATE AND A.CARD_CO = C.CARD_CO AND A.APPR_FG = C.APPR_FG )
WHEN MATCHED THEN
UPDATE
SET CARD_CNT = C.CARD_CNT
, CARD_AMT = C.CARD_AMT
, C_CARD_CNT = C.C_CARD_CNT
, C_CARD_AMT = C.C_CARD_AMT
WHEN NOT MATCHED THEN
INSERT (MS_NO,SALE_DATE,CARD_CO,APPR_FG,CHAIN_NO,CHAIN_AREA,CARD_CNT,CARD_AMT,C_CARD_CNT,C_CARD_AMT)
VALUES (C.MS_NO,C.SALE_DATE,C.CARD_CO,C.APPR_FG,C.CHAIN_NO,C.CHAIN_AREA,C.CARD_CNT,C.CARD_AMT,C.C_CARD_CNT,C.C_CARD_AMT);
END LOOP;
END;
-- SDCARD DELETE
DELETE FROM SDCARDTB WHERE MS_NO='E00901' AND SALE_DATE='20130129' AND CARD_CO='999';
-- STRNCDTB UPDATE
DECLARE
CURSOR CARD_INFO IS
SELECT * FROM TMPSTRNCDTB WHERE MS_NO='E00903' AND SALE_DATE BETWEEN '20130129' AND '20130205' AND POS_NO='01' ORDER BY SALE_DATE;
BEGIN
FOR C IN CARD_INFO LOOP
UPDATE STRNCDTB@WEBPOS
SET CARD_CO = C.ORG_CARD_CO
WHERE SALE_DATE = C.SALE_DATE
AND MS_NO = C.MS_NO
AND POS_NO = C.POS_NO
AND BILL_NO = C.BILL_NO
AND VAN_CD = C.VAN_CD
AND CARD_CO = C.CARD_CO;
END LOOP;
END;
--SDCARDTB UPDATE
DECLARE
CURSOR CARD_SUM IS
SELECT MS_NO,SALE_DATE,CARD_CO,APPR_FG,CHAIN_NO,CHAIN_AREA
,NVL(DECODE(SALE_FG,'0',COUNT(*)),0) AS CARD_CNT
,NVL(DECODE(SALE_FG,'0',SUM(APPR_AMT)),0) AS CARD_AMT
,NVL(DECODE(SALE_FG,'1',COUNT(*)),0) AS C_CARD_CNT
,NVL(DECODE(SALE_FG,'1',SUM(APPR_AMT)),0) AS C_CARD_AMT
FROM STRNCDTB WHERE MS_NO='E00903' AND SALE_DATE BETWEEN '20130129' AND '20130205' AND POS_NO='01'
GROUP BY MS_NO,SALE_DATE,CARD_CO,APPR_FG,CHAIN_NO,CHAIN_AREA,SALE_FG
;
BEGIN
FOR C IN CARD_SUM LOOP
MERGE INTO SDCARDTB A
USING (SELECT * FROM DUAL) B
ON ( A.MS_NO = C.MS_NO AND A.SALE_DATE = C.SALE_DATE AND A.CARD_CO = C.CARD_CO AND A.APPR_FG = C.APPR_FG )
WHEN MATCHED THEN
UPDATE
SET CARD_CNT = C.CARD_CNT
, CARD_AMT = C.CARD_AMT
, C_CARD_CNT = C.C_CARD_CNT
, C_CARD_AMT = C.C_CARD_AMT
WHEN NOT MATCHED THEN
INSERT (MS_NO,SALE_DATE,CARD_CO,APPR_FG,CHAIN_NO,CHAIN_AREA,CARD_CNT,CARD_AMT,C_CARD_CNT,C_CARD_AMT)
VALUES (C.MS_NO,C.SALE_DATE,C.CARD_CO,C.APPR_FG,C.CHAIN_NO,C.CHAIN_AREA,C.CARD_CNT,C.CARD_AMT,C.C_CARD_CNT,C.C_CARD_AMT);
END LOOP;
END;
DELETE FROM SDCARDTB WHERE MS_NO='E00903' AND SALE_DATE BETWEEN '20130129' AND '20130205' AND CARD_CO='999';
|
|
|
