v_work_date L_DATALOAD_MSG.WORK_DATE%TYPE := GET_TODAY(0);
v_proc_name L_DATALOAD_MSG.PROC_NAME%TYPE := 'PROC_F5811024';
v_table_name L_DATALOAD_MSG.TABLE_NAME%TYPE := 'F5811024';
v_start_time L_DATALOAD_MSG.START_TIME%TYPE := SYSDATE;
v_end_time L_DATALOAD_MSG.END_TIME%TYPE;
v_source_rowcnt L_DATALOAD_MSG.SOURCE_ROWCNT%TYPE := 0;
v_target_rowcnt L_DATALOAD_MSG.TARGET_ROWCNT%TYPE := 0;
v_work_status L_DATALOAD_MSG.WORK_STATUS%TYPE := 'SUCCESS';
v_err_code L_DATALOAD_MSG.ERR_CODE%TYPE;
v_err_msg L_DATALOAD_MSG.ERR_MSG%TYPE;
v_etl_type L_DATALOAD_MSG.PROC_NAME%TYPE := 'C';
v_upmt F5811024.M4UPMT%TYPE := TO_NUMBER(TO_CHAR(SYSDATE, 'HH24MISS'));
v_sysDate F5811024.M4UPMJ%TYPE := GET_ERP_DATE(TO_NUMBER(TO_CHAR(sysdate, 'YYYYMMDD')));
v_cnt1 number := 0;
v_cnt2 number := 0;
V_DAY_TEMP F5811024.M4UPMJ%TYPE:=GET_ERP_DATE(TO_CHAR(LAST_DAY(SYSDATE),'YYYYMMDD')) ;
V_DAY_TEMP2 DATE:=TO_date(GET_DATE(V_DAY_TEMP),'YYYYMMDD');
V_CIDSDT F5811024.M4DSDT%TYPE:=TO_CHAR(V_DAY_TEMP2 ,'YYYYMM');
V_CIDSDT_M1 F5811024.M4DSDT%TYPE:=TO_CHAR(ADD_MONTHS(V_DAY_TEMP2,-1),'YYYYMM');
-- 3년전 년월일
V_ADGJ_3 F5811024.M4UPMJ%TYPE:=GET_ERP_DATE(TO_CHAR(ADD_MONTHS(TO_DATE(GET_DATE(V_DAY_TEMP),'YYYYMMDD'),-36)+1,'YYYYMMDD'));
V_CFY F5811024.M4CFY%TYPE:=TO_CHAR(V_DAY_TEMP2,'YYYY');
V_PN F5811024.M4PN%TYPE:=TO_CHAR(V_DAY_TEMP2 ,'MM');
BEGIN
MERGE
INTO F5811024 Q
USING
(SELECT V_CIDSDT M4DSDT, A.P9AN8, V_CFY M4CFY, V_PN M4PN, A.P1MCU, 삼년이전합계_AJ,
최근3년합계_AJ,
현재마일리지, 현재마일리지_고객마스타,
-- -100(적립취소) 300 -100 300 300 (삼년이전AJ 400)
(CASE WHEN 최근3년합계_AJ < 현재마일리지 AND 최근3년합계_AJ <= 0 AND 현재마일리지 > 0 THEN 현재마일리지 ELSE
-- -100(적립취소) 0 -100 0 0 (삼년이전AJ 100)
-- -200(적립취소) -100 -200 -100 0 (삼년이전AJ 100)
CASE WHEN 최근3년합계_AJ < 현재마일리지 AND 최근3년합계_AJ <= 0 AND 현재마일리지 <=0 THEN 0 ELSE
CASE WHEN 최근3년합계_AJ < 현재마일리지 AND 최근3년합계_AJ > 0 THEN 현재마일리지 - 최근3년합계_AJ ELSE
CASE WHEN 최근3년합계_AJ >= 현재마일리지 AND 최근3년합계_AJ > 0 THEN 0 ELSE 0 END END END END) 차감마일리지,
(CASE WHEN M1.M1E1SAM2>=10000 AND 최근3년합계_AJ < 현재마일리지 AND 최근3년합계_AJ <= 0 AND 현재마일리지 > 0 THEN 현재마일리지 ELSE
CASE WHEN M1.M1E1SAM2>=10000 AND 최근3년합계_AJ < 현재마일리지 AND 최근3년합계_AJ <= 0 AND 현재마일리지 <=0 THEN 0 ELSE
CASE WHEN M1.M1E1SAM2>=10000 AND 최근3년합계_AJ < 현재마일리지 AND 최근3년합계_AJ > 0 THEN 현재마일리지 - 최근3년합계_AJ ELSE
CASE WHEN M1.M1E1SAM2>=10000 AND 최근3년합계_AJ >= 현재마일리지 AND 최근3년합계_AJ > 0 THEN 0 ELSE 0 END END END END) 차감마일리지_만원이상,
M1.M1E1SAM2 적립누계,
최근3년합계, 최근3년합계_AJ, 최근3년합계_D,
P9.P1E1FLG2 SMS수신동의,
P9.P1E1DMNM, P9.P1AR12, P9.P1ET1TELM, P9.P1E1CDNO, 'A' 삼년이전이후구분,
SUBSTR(P9.P1E1RRNO,0,6), 'JDE', 'F5811024AJ', 'JDE', v_sysDate, v_upmt
FROM (
---------------------------------------------------------------------------------------------------------------------------------------------
SELECT P9.P9AN8, P1MCU,
--20110601 111152
NVL(SUM(CASE WHEN P9.P9ADGJ < V_ADGJ_3 AND P9.P9ET1FLG0='A' THEN P9.P9AA/100 ELSE 0 END),0)
-NVL(SUM(CASE WHEN P9.P9ADGJ < V_ADGJ_3 AND P9.P9ET1FLG0='J' THEN P9.P9AA/100 ELSE 0 END),0)
-NVL(SUM(CASE WHEN P9.P9ADGJ < V_ADGJ_3 AND P9.P9ET1FLG0='D' THEN P9.P9AA/100 ELSE 0 END),0)
-NVL(SUM(CASE WHEN P9.P9ADGJ < V_ADGJ_3 AND P9.P9ET1FLG0='U' THEN P9.P9AA/100 ELSE 0 END),0)
-NVL(SUM(CASE WHEN P9.P9ADGJ < V_ADGJ_3 AND P9.P9ET1FLG0='C' THEN P9.P9AA/100 ELSE 0 END),0) 삼년이전합계, --C=적립취소 부호 주의
--20110601 111152
NVL(SUM(CASE WHEN P9.P9ADGJ < V_ADGJ_3 AND P9.P9ET1FLG0='A' THEN P9.P9AA/100 ELSE 0 END),0)
-NVL(SUM(CASE WHEN P9.P9ADGJ < V_ADGJ_3 AND P9.P9ET1FLG0='J' THEN P9.P9AA/100 ELSE 0 END),0) 삼년이전합계_AJ,
--20110601 111152 ~ 20140531 114151
NVL(SUM(CASE WHEN P9.P9ADGJ BETWEEN V_ADGJ_3 AND V_DAY_TEMP AND P9.P9ET1FLG0='A' THEN P9.P9AA/100 ELSE 0 END),0)
-NVL(SUM(CASE WHEN P9.P9ADGJ BETWEEN V_ADGJ_3 AND V_DAY_TEMP AND P9.P9ET1FLG0='J' THEN P9.P9AA/100 ELSE 0 END),0)
-NVL(SUM(CASE WHEN P9.P9ADGJ BETWEEN V_ADGJ_3 AND V_DAY_TEMP AND P9.P9ET1FLG0='D' THEN P9.P9AA/100 ELSE 0 END),0)
-NVL(SUM(CASE WHEN P9.P9ADGJ BETWEEN V_ADGJ_3 AND V_DAY_TEMP AND P9.P9ET1FLG0='U' THEN P9.P9AA/100 ELSE 0 END),0)
-NVL(SUM(CASE WHEN P9.P9ADGJ BETWEEN V_ADGJ_3 AND V_DAY_TEMP AND P9.P9ET1FLG0='C' THEN P9.P9AA/100 ELSE 0 END),0) 최근3년합계, --C=적립취소 부호 주의
NVL(SUM(CASE WHEN P9.P9ADGJ BETWEEN V_ADGJ_3 AND V_DAY_TEMP AND P9.P9ET1FLG0='U' THEN P9.P9AA/100 ELSE 0 END),0)
+NVL(SUM(CASE WHEN P9.P9ADGJ BETWEEN V_ADGJ_3 AND V_DAY_TEMP AND P9.P9ET1FLG0='C' THEN P9.P9AA/100 ELSE 0 END),0) 최근3년합계_UC, --C=적립취소 부호 주의
NVL(SUM(CASE WHEN P9.P9ADGJ BETWEEN V_ADGJ_3 AND V_DAY_TEMP AND P9.P9ET1FLG0='A' THEN P9.P9AA/100 ELSE 0 END),0)
-NVL(SUM(CASE WHEN P9.P9ADGJ BETWEEN V_ADGJ_3 AND V_DAY_TEMP AND P9.P9ET1FLG0='J' THEN P9.P9AA/100 ELSE 0 END),0) 최근3년합계_AJ, --C=적립취소 부호 주의
NVL(SUM(CASE WHEN P9.P9ADGJ BETWEEN V_ADGJ_3 AND V_DAY_TEMP AND P9.P9ET1FLG0='D' THEN P9.P9AA/100 ELSE 0 END),0) 최근3년합계_D, --C=적립취소 부호 주의
NVL(SUM(CASE WHEN P9.P9ET1FLG0='A' THEN P9.P9AA/100 ELSE 0 END),0)
-NVL(SUM(CASE WHEN P9.P9ET1FLG0='J' THEN P9.P9AA/100 ELSE 0 END),0)
-NVL(SUM(CASE WHEN P9.P9ET1FLG0='D' THEN P9.P9AA/100 ELSE 0 END),0)
-NVL(SUM(CASE WHEN P9.P9ET1FLG0='U' THEN P9.P9AA/100 ELSE 0 END),0)
-NVL(SUM(CASE WHEN P9.P9ET1FLG0='C' THEN P9.P9AA/100 ELSE 0 END),0) 현재마일리지, --C=적립취소 부호 주의
P1.P1B104/100 현재마일리지_고객마스타
FROM F5811009 P9, F5801008 P1
WHERE 1=1
AND P9.P9AN8=P1.P1AN8
----------------------------------------------------------------------------------------------------------------------------
--AND P1.P1AN8 IN (4981780,1720288,1636700,1638200,1639300,1639700,1641200,1642300,1642400,1642700,1643900,1644500,1645000,1646000,1646400,
-- 1646700,9425088,7921740,7921739,7921726,7921723,7921722,7921721,7921719,7921714,7921710,7921709,1003234)
--AND P1.P1AN8=1720288
----------------------------------------------------------------------------------------------------------------------------
GROUP BY P9.P9AN8, P1.P1MCU, P1.P1B104
--ORDER BY P9.P9AN8
---------------------------------------------------------------------------------------------------------------------------------------------
) A,
--3년내 적립, 3년이전 적립 둘 다 있는 고객
(SELECT B.P9AN8, NVL(P1.P1E1FLG2,' ') P1E1FLG2, --SMS 수신동의
P1.P1E1DMNM, P1.P1AR12, P1.P1ET1TELM, P1.P1E1CDNO, P1.P1E1RRNO
FROM (SELECT DISTINCT P9AN8
FROM F5811009
--20110601 111152 ~ 20140531 114151
WHERE P9ADGJ BETWEEN V_ADGJ_3 AND V_DAY_TEMP
----------------------------------------------------------------------------------------------------------------------------
--AND P9AN8 IN (4981780,1720288,1636700,1638200,1639300,1639700,1641200,1642300,1642400,1642700,1643900,1644500,1645000,1646000,1646400,
-- 1646700,9425088,7921740,7921739,7921726,7921723,7921722,7921721,7921719,7921714,7921710,7921709,1003234)
--AND P9AN8=1720288
----------------------------------------------------------------------------------------------------------------------------
) A,
(SELECT DISTINCT P9AN8
FROM F5811009
--20110601 111152
WHERE P9ADGJ < V_ADGJ_3
----------------------------------------------------------------------------------------------------------------------------
--AND P9AN8 IN (4981780,1720288,1636700,1638200,1639300,1639700,1641200,1642300,1642400,1642700,1643900,1644500,1645000,1646000,1646400,
-- 1646700,9425088,7921740,7921739,7921726,7921723,7921722,7921721,7921719,7921714,7921710,7921709,1003234)
--AND P9AN8=1720288
----------------------------------------------------------------------------------------------------------------------------
) B,
F5801008 P1
WHERE A.P9AN8=B.P9AN8
AND A.P9AN8=P1.P1AN8
) P9,
----------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------
--충당금 만원이상 계산위해 필요함, 5/1 새벽에 3년이전마일리지 소멸후, 4월충당금정산을 5/1 낮에 진행해야함
--월중 만원이상 정확하지않음, SMS 발송 시 의미없음
(SELECT M1AN8, M1E1SAM2 FROM F5811026 WHERE M1DSDT=V_CIDSDT_M1) M1
----------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------
WHERE A.P9AN8=P9.P9AN8
AND A.P9AN8=M1.M1AN8
----------------------------------------------------------------------------------------------------------------------------
--AND A.P9AN8 IN (4981780,1720288,1636700,1638200,1639300,1639700,1641200,1642300,1642400,1642700,1643900,1644500,1645000,1646000,1646400,
-- 1646700,9425088,7921740,7921739,7921726,7921723,7921722,7921721,7921719,7921714,7921710,7921709,1003234)
--AND A.P9AN8=1720288
----------------------------------------------------------------------------------------------------------------------------
AND (CASE WHEN 최근3년합계_AJ < 현재마일리지 AND 최근3년합계_AJ <= 0 AND 현재마일리지 > 0 THEN 현재마일리지 ELSE
CASE WHEN 최근3년합계_AJ < 현재마일리지 AND 최근3년합계_AJ <= 0 AND 현재마일리지 <=0 THEN 0 ELSE
CASE WHEN 최근3년합계_AJ < 현재마일리지 AND 최근3년합계_AJ > 0 THEN 현재마일리지 - 최근3년합계_AJ ELSE
CASE WHEN 최근3년합계_AJ >= 현재마일리지 AND 최근3년합계_AJ > 0 THEN 0 ELSE 0 END END END END) > 0)W
ON(Q.M4AN8 = W.P9AN8)
WHEN MATCHED THEN
UPDATE SET Q.M4AN8 = W.P9AN8
;