create or replace PACKAGE BODY PKG_ANP_BANCA_FV AS --DONE
PROCEDURE MAIN(cur_month_yyyymm in NUMBER) AS
BEGIN
BANCA_CUSTOMERS(cur_month_yyyymm);
CNT_BANCA_CONTRACT(cur_month_yyyymm);
NEW_CONTRACTS(cur_month_yyyymm);
TOT_APE_AND_SUM_ASSURED(cur_month_yyyymm);
LAST_BANCA_CONTRACT_SINCE(cur_month_yyyymm);
COVERAGE(cur_month_yyyymm);
FREELOOK_CONTRACTS(cur_month_yyyymm);
END MAIN;
PROCEDURE BANCA_CUSTOMERS(cur_month_yyyymm in NUMBER) AS
BEGIN
PKG_LOGS.log('PKG_ANP_BANCA_FV', 'BANCA_CUSTOMERS', 'Begin processing: '||cur_month_yyyymm);
DELETE FROM BANCA_FV WHERE MONTH_ID = cur_month_yyyymm;
COMMIT;
INSERT INTO BANCA_FV (CST_NBR, MONTH_ID)
SELECT DISTINCT CST_NBR, cur_month_yyyymm FROM TBL_BANCA WHERE CST_NBR IS NOT NULL;
COMMIT;
PKG_LOGS.log('PKG_ANP_BANCA_FV', 'BANCA_CUSTOMERS', 'End processing: '||cur_month_yyyymm);
END BANCA_CUSTOMERS;
--#####################################################################################################################
-- this procedure create 4 feature vector:
--CNT_BANCA_CONTRACT_3M, CNT_BANCA_CONTRACT_6M, CNT_BANCA_CONTRACT_1Y, CNT_BANCA_CONTRACT_EOM , CNT_BANCA_CONTRACT_2Y,
-- BANCA_HOLD_2Y, BANCA_HOLD_1Y, BANCA_HOLD_9M, BANCA_HOLD_6M, BANCA_HOLD_3M, BANCA_HOLD_EOM,
PROCEDURE CNT_BANCA_CONTRACT(cur_month_yyyymm in NUMBER) AS
v_to_month number;
v_from_month_3 number;
v_from_month_6 number;
v_from_month_12 number;
v_from_month_24 number;
BEGIN
PKG_LOGS.log('PKG_ANP_BANCA_FV', 'CNT_BANCA_CONTRACT', 'Begin processing: '||cur_month_yyyymm);
v_to_month := cur_month_yyyymm;
v_from_month_3 := to_number(to_char(add_months(to_date(cur_month_yyyymm,'yyyymm'),-2 ),'yyyymm'));
v_from_month_6 := to_number(to_char(add_months(to_date(cur_month_yyyymm,'yyyymm'),-5 ),'yyyymm'));
v_from_month_12 := to_number(to_char(add_months(to_date(cur_month_yyyymm,'yyyymm'),-11 ),'yyyymm'));
v_from_month_24 := to_number(to_char(add_months(to_date(cur_month_yyyymm,'yyyymm'),-23 ),'yyyymm'));
----------------------------------------------------------
MERGE INTO BANCA_FV A
USING (
with dat1 as (
select cst_nbr, ar_id
from TBL_BANCA
where cst_nbr is not null
AND status IN ('VALID')
and month_id = v_to_month
),
dat2 as (
select cst_nbr, count(distinct ar_id) as CNT_BANCA_CONTRACT_EOM
from dat1
group by cst_nbr
)
select cst_nbr, v_to_month as month_id, CNT_BANCA_CONTRACT_EOM ,
CASE
WHEN CNT_BANCA_CONTRACT_EOM >0 THEN 1
ELSE 0
END AS BANCA_HOLD_EOM
FROM DAT2
) B
ON (A.CST_NBR = B.CST_NBR AND A.MONTH_ID = B.MONTH_ID)
WHEN MATCHED THEN UPDATE SET
A.CNT_BANCA_CONTRACT_EOM = B.CNT_BANCA_CONTRACT_EOM , A.BANCA_HOLD_EOM = B.BANCA_HOLD_EOM
WHEN NOT MATCHED then
INSERT (A.CST_NBR, A.MONTH_ID, A.CNT_BANCA_CONTRACT_EOM, A.BANCA_HOLD_EOM)
VALUES (B.CST_NBR, B.MONTH_ID, B.CNT_BANCA_CONTRACT_EOM, B.BANCA_HOLD_EOM) ;
COMMIT;
----------------------------------------------------------
MERGE INTO BANCA_FV A
USING (
with dat1 as (
select cst_nbr, ar_id
from TBL_BANCA
where cst_nbr is not null
AND status IN ('VALID')
and month_id between v_from_month_3 AND v_to_month
),
dat2 as (
select cst_nbr, count(distinct ar_id) as CNT_BANCA_CONTRACT_3M
from dat1
group by cst_nbr
)
select cst_nbr, v_to_month as month_id, CNT_BANCA_CONTRACT_3M ,
CASE
WHEN CNT_BANCA_CONTRACT_3M >0 THEN 1
ELSE 0
END AS BANCA_HOLD_3M
FROM DAT2
) B
ON (A.CST_NBR = B.CST_NBR AND A.MONTH_ID = B.MONTH_ID)
WHEN MATCHED THEN UPDATE SET
A.CNT_BANCA_CONTRACT_3M = B.CNT_BANCA_CONTRACT_3M , A.BANCA_HOLD_3M = B.BANCA_HOLD_3M
WHEN NOT MATCHED then
INSERT (A.CST_NBR, A.MONTH_ID, A.CNT_BANCA_CONTRACT_3M, A.BANCA_HOLD_3M)
VALUES (B.CST_NBR, B.MONTH_ID, B.CNT_BANCA_CONTRACT_3M, B.BANCA_HOLD_3M) ;
COMMIT;
-------------------------------------------------------------
MERGE INTO BANCA_FV A
USING (
with dat1 as (
select cst_nbr, ar_id
from TBL_BANCA
where cst_nbr is not null
AND status IN ('VALID')
and month_id between v_from_month_6 AND v_to_month
),
dat2 as (
select cst_nbr, count(distinct ar_id) as CNT_BANCA_CONTRACT_6M
from dat1
group by cst_nbr
)
select cst_nbr, v_to_month as month_id, CNT_BANCA_CONTRACT_6M,
CASE
WHEN CNT_BANCA_CONTRACT_6M >0 THEN 1
ELSE 0
END AS BANCA_HOLD_6M
FROM DAT2
) B
ON (A.CST_NBR = B.CST_NBR AND A.MONTH_ID = B.MONTH_ID)
WHEN MATCHED THEN UPDATE SET
A.CNT_BANCA_CONTRACT_6M = B.CNT_BANCA_CONTRACT_6M , A.BANCA_HOLD_6M = B.BANCA_HOLD_6M
WHEN NOT MATCHED then
INSERT (A.CST_NBR, A.MONTH_ID, A.CNT_BANCA_CONTRACT_6M, A.BANCA_HOLD_6M)
VALUES (B.CST_NBR, B.MONTH_ID, B.CNT_BANCA_CONTRACT_6M, B.BANCA_HOLD_6M) ;
COMMIT;
-------------------------------------------------------------
-------------------------------------------------------------
MERGE INTO BANCA_FV A
USING (
with dat1 as (
select cst_nbr, ar_id
from TBL_BANCA
where CST_NBR IS NOT NULL
AND status IN ('VALID')
and month_id between v_from_month_12 AND v_to_month
),
dat2 as (
select cst_nbr, count(distinct ar_id) as CNT_BANCA_CONTRACT_1Y
from dat1
group by cst_nbr
)
select cst_nbr, v_to_month as month_id, CNT_BANCA_CONTRACT_1Y,
CASE
WHEN CNT_BANCA_CONTRACT_1Y >0 THEN 1
ELSE 0
END AS BANCA_HOLD_1Y
FROM DAT2
) B
ON (A.CST_NBR = B.CST_NBR AND A.MONTH_ID = B.MONTH_ID)
WHEN MATCHED THEN UPDATE SET
A.CNT_BANCA_CONTRACT_1Y = B.CNT_BANCA_CONTRACT_1Y , A.BANCA_HOLD_1Y = B.BANCA_HOLD_1Y
WHEN NOT MATCHED then
INSERT (A.CST_NBR, A.MONTH_ID, A.CNT_BANCA_CONTRACT_1Y, A.BANCA_HOLD_1Y)
VALUES (B.CST_NBR, B.MONTH_ID, B.CNT_BANCA_CONTRACT_1Y, B.BANCA_HOLD_1Y) ;
COMMIT;
-------------------------------------------------------------
MERGE INTO BANCA_FV A
USING (
with dat1 as (
select cst_nbr, ar_id
from TBL_BANCA
where CST_NBR IS NOT NULL
AND status IN ('VALID')
and month_id between v_from_month_24 AND v_to_month
),
dat2 as (
select cst_nbr, count(distinct ar_id) as CNT_BANCA_CONTRACT_2Y
from dat1
group by cst_nbr
)
select cst_nbr, v_to_month as month_id, CNT_BANCA_CONTRACT_2Y,
CASE
WHEN CNT_BANCA_CONTRACT_2Y >0 THEN 1
ELSE 0
END AS BANCA_HOLD_2Y
FROM DAT2
) B
ON (A.CST_NBR = B.CST_NBR AND A.MONTH_ID = B.MONTH_ID)
WHEN MATCHED THEN UPDATE SET
A.CNT_BANCA_CONTRACT_2Y = B.CNT_BANCA_CONTRACT_2Y , A.BANCA_HOLD_2Y =B.BANCA_HOLD_2Y
WHEN NOT MATCHED then
INSERT (A.CST_NBR, A.MONTH_ID, A.CNT_BANCA_CONTRACT_2Y, A.BANCA_HOLD_2Y)
VALUES (B.CST_NBR, B.MONTH_ID, B.CNT_BANCA_CONTRACT_2Y, B.BANCA_HOLD_2Y) ;
COMMIT;
PKG_LOGS.log('PKG_ANP_BANCA_FV', 'CNT_BANCA_CONTRACT', 'End processing: '||cur_month_yyyymm);
END CNT_BANCA_CONTRACT;
------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------
PROCEDURE NEW_CONTRACTS(cur_month_yyyymm in NUMBER) AS
v_to_month number;
v_from_month_3 number;
v_from_month_6 number;
v_from_month_12 number;
v_from_month_24 number;
BEGIN
PKG_LOGS.log('PKG_ANP_BANCA_FV', 'NEW_CONTRACTS', 'Begin processing: '||cur_month_yyyymm);
v_to_month := cur_month_yyyymm;
v_from_month_3 := to_number(to_char(add_months(to_date(cur_month_yyyymm,'yyyymm'),-2 ),'yyyymm'));
v_from_month_6 := to_number(to_char(add_months(to_date(cur_month_yyyymm,'yyyymm'),-5 ),'yyyymm'));
v_from_month_12 := to_number(to_char(add_months(to_date(cur_month_yyyymm,'yyyymm'),-11 ),'yyyymm'));
v_from_month_24 := to_number(to_char(add_months(to_date(cur_month_yyyymm,'yyyymm'),-23 ),'yyyymm'));
MERGE INTO BANCA_FV A
USING (
with dat1 as (
SELECT CST_NBR, AR_ID, APE, sum_assured
FROM TBL_BANCA
WHERE CST_NBR IS NOT NULL
AND to_number(to_char(NO_FREELOOK_DATE,'yyyymm')) = v_to_month
and month_id =v_to_month
AND STATUS IN ('VALID')
)
select cst_nbr, v_to_month AS MONTH_ID,
SUM(APE) as TOT_APE_EOM ,
COUNT(DISTINCT AR_ID) AS CNT_NEW_BANCA_CONTRACT_EOM,
SUM(sum_assured) AS TOT_SUM_ASSURED_EOM
from dat1
group by cst_nbr
) B
ON (A.CST_NBR = B.CST_NBR AND A.MONTH_ID = B.MONTH_ID)
WHEN MATCHED THEN UPDATE SET
A.TOT_APE_EOM = B.TOT_APE_EOM ,
A.CNT_NEW_BANCA_CONTRACT_EOM = B.CNT_NEW_BANCA_CONTRACT_EOM,
A.TOT_SUM_ASSURED_EOM = B.TOT_SUM_ASSURED_EOM
WHEN NOT MATCHED then
INSERT (A.CST_NBR, A.MONTH_ID, A.TOT_APE_EOM, A.CNT_NEW_BANCA_CONTRACT_EOM, A.TOT_SUM_ASSURED_EOM)
VALUES (B.CST_NBR, B.MONTH_ID, B.TOT_APE_EOM, B.CNT_NEW_BANCA_CONTRACT_EOM, B.TOT_SUM_ASSURED_EOM) ;
COMMIT;
--------------------------
MERGE INTO BANCA_FV A
USING (
with dat1 as (
SELECT CST_NBR, AR_ID, APE, sum_assured
FROM TBL_BANCA
WHERE CST_NBR IS NOT NULL
AND to_number(to_char(NO_FREELOOK_DATE,'yyyymm')) BETWEEN v_from_month_3 AND v_to_month
AND STATUS IN ('VALID')
AND MONTH_ID = to_number(to_char(NO_FREELOOK_DATE,'yyyymm'))
)
select cst_nbr, v_to_month AS MONTH_ID,
SUM(APE) as TOT_APE_3M ,
COUNT(DISTINCT AR_ID) AS CNT_NEW_BANCA_CONTRACT_3M,
SUM(sum_assured) AS TOT_SUM_ASSURED_3M
from dat1
group by cst_nbr
) B
ON (A.CST_NBR = B.CST_NBR AND A.MONTH_ID = B.MONTH_ID)
WHEN MATCHED THEN UPDATE SET
A.TOT_APE_3M = B.TOT_APE_3M , A.CNT_NEW_BANCA_CONTRACT_3M = B.CNT_NEW_BANCA_CONTRACT_3M, A.TOT_SUM_ASSURED_3M = B.TOT_SUM_ASSURED_3M
WHEN NOT MATCHED then
INSERT (A.CST_NBR, A.MONTH_ID, A.TOT_APE_3M, A.CNT_NEW_BANCA_CONTRACT_3M, A.TOT_SUM_ASSURED_3M)
VALUES (B.CST_NBR, B.MONTH_ID, B.TOT_APE_3M, B.CNT_NEW_BANCA_CONTRACT_3M, B.TOT_SUM_ASSURED_3M) ;
COMMIT;
--------------------------
MERGE INTO BANCA_FV A
USING (
with dat1 as (
SELECT CST_NBR, AR_ID, APE, SUM_ASSURED
FROM TBL_BANCA
WHERE CST_NBR IS NOT NULL
AND to_number(to_char(NO_FREELOOK_DATE,'yyyymm')) BETWEEN v_from_month_6 AND v_to_month
AND STATUS IN ('VALID')
AND MONTH_ID = to_number(to_char(NO_FREELOOK_DATE,'yyyymm'))
)
select cst_nbr, v_to_month AS MONTH_ID,
SUM(APE) as TOT_APE_6M ,
COUNT(DISTINCT AR_ID) AS CNT_NEW_BANCA_CONTRACT_6M,
SUM(sum_assured) AS TOT_SUM_ASSURED_6M
from dat1
group by cst_nbr
) B
ON (A.CST_NBR = B.CST_NBR AND A.MONTH_ID = B.MONTH_ID)
WHEN MATCHED THEN UPDATE SET
A.TOT_APE_6M = B.TOT_APE_6M , A.CNT_NEW_BANCA_CONTRACT_6M = B.CNT_NEW_BANCA_CONTRACT_6M, A.TOT_SUM_ASSURED_6M = B.TOT_SUM_ASSURED_6M
WHEN NOT MATCHED then
INSERT (A.CST_NBR, A.MONTH_ID, A.TOT_APE_6M, A.CNT_NEW_BANCA_CONTRACT_6M, A.TOT_SUM_ASSURED_6M)
VALUES (B.CST_NBR, B.MONTH_ID, B.TOT_APE_6M, B.CNT_NEW_BANCA_CONTRACT_6M, B.TOT_SUM_ASSURED_6M) ;
COMMIT;
--------------------------
MERGE INTO BANCA_FV A
USING (
with dat1 as (
SELECT CST_NBR, AR_ID, APE, sum_assured
FROM TBL_BANCA
WHERE CST_NBR IS NOT NULL
AND to_number(to_char(NO_FREELOOK_DATE,'yyyymm')) BETWEEN v_from_month_12 AND v_to_month
AND STATUS IN ('VALID')
AND MONTH_ID = to_number(to_char(NO_FREELOOK_DATE,'yyyymm'))
)
select cst_nbr, v_to_month AS MONTH_ID,
SUM(APE) as TOT_APE_1Y ,
COUNT(DISTINCT AR_ID) AS CNT_NEW_BANCA_CONTRACT_1Y,
SUM(sum_assured) AS TOT_SUM_ASSURED_1Y
from dat1
group by cst_nbr
) B
ON (A.CST_NBR = B.CST_NBR AND A.MONTH_ID = B.MONTH_ID)
WHEN MATCHED THEN UPDATE SET
A.TOT_APE_1Y = B.TOT_APE_1Y , A.CNT_NEW_BANCA_CONTRACT_1Y = B.CNT_NEW_BANCA_CONTRACT_1Y, A.TOT_SUM_ASSURED_1Y = B.TOT_SUM_ASSURED_1Y
WHEN NOT MATCHED then
INSERT (A.CST_NBR, A.MONTH_ID, A.TOT_APE_1Y, A.CNT_NEW_BANCA_CONTRACT_1Y, A.TOT_SUM_ASSURED_1Y)
VALUES (B.CST_NBR, B.MONTH_ID, B.TOT_APE_1Y, B.CNT_NEW_BANCA_CONTRACT_1Y, B.TOT_SUM_ASSURED_1Y) ;
COMMIT;
--------------------------
END NEW_CONTRACTS;
-----------------------------------------------------------------------
PROCEDURE TOT_APE_AND_SUM_ASSURED(cur_month_yyyymm in NUMBER) AS
BEGIN
MERGE INTO BANCA_FV A
USING (
with dat1 as (
SELECT CST_NBR, AR_ID, APE, SUM_ASSURED
FROM TBL_BANCA
WHERE CST_NBR IS NOT NULL
AND STATUS IN ('VALID')
AND MONTH_ID = cur_month_yyyymm
)
select cst_nbr, cur_month_yyyymm AS MONTH_ID,
SUM(APE) as TOT_APE ,
SUM(sum_assured) AS TOT_SUM_ASSURED
from dat1
group by cst_nbr
) B
ON (A.CST_NBR = B.CST_NBR AND A.MONTH_ID = B.MONTH_ID)
WHEN MATCHED THEN UPDATE SET
A.TOT_APE = B.TOT_APE, A.TOT_SUM_ASSURED = B.TOT_SUM_ASSURED
WHEN NOT MATCHED then
INSERT (A.CST_NBR, A.MONTH_ID, A.TOT_APE, A.TOT_SUM_ASSURED)
VALUES (B.CST_NBR, B.MONTH_ID, B.TOT_APE, B.TOT_SUM_ASSURED) ;
COMMIT;
PKG_LOGS.log('PKG_ANP_BANCA_FV', 'NEW_CONTRACTS', 'End processing: '||cur_month_yyyymm);
END TOT_APE_AND_SUM_ASSURED;
------------------------------------------------------------------------------------------------------------------------------
PROCEDURE LAST_BANCA_CONTRACT_SINCE(cur_month_yyyymm in NUMBER) AS
v_to_month number;
BEGIN
PKG_LOGS.log('PKG_ANP_BANCA_FV', 'LAST_BANCA_CONTRACT_SINCE', 'Begin processing: '||cur_month_yyyymm);
v_to_month := cur_month_yyyymm;
MERGE INTO BANCA_FV A
USING (
WITH DAT1 AS (
SELECT DISTINCT CST_NBR, AR_ID, no_freelook_date
FROM TBL_BANCA
WHERE STATUS IN ('VALID')
and to_number(to_char(NO_FREELOOK_DATE,'yyyymm')) <= v_to_month
),
DAT2 AS (
SELECT DAT1.*,
ROW_NUMBER() OVER (PARTITION BY CST_NBR ORDER BY no_freelook_date desc) AS date_ORDER
FROM DAT1
)
select cst_nbr, v_to_month AS MONTH_ID, last_day (to_date(v_to_month,'YYYYMM')) - no_freelook_date as LAST_BANCA_CONTRACT_SINCE
from dat2 where date_order =1
) B
ON (A.CST_NBR = B.CST_NBR AND A.MONTH_ID = B.MONTH_ID)
WHEN MATCHED THEN UPDATE SET
A.LAST_BANCA_CONTRACT_SINCE = B.LAST_BANCA_CONTRACT_SINCE
WHEN NOT MATCHED then
INSERT (A.CST_NBR, A.MONTH_ID, A.LAST_BANCA_CONTRACT_SINCE)
VALUES (B.CST_NBR, B.MONTH_ID, B.LAST_BANCA_CONTRACT_SINCE) ;
COMMIT;
PKG_LOGS.log('PKG_ANP_BANCA_FV', 'LAST_BANCA_CONTRACT_SINCE', 'End processing: '||cur_month_yyyymm);
END LAST_BANCA_CONTRACT_SINCE;
PROCEDURE COVERAGE (cur_month_yyyymm in NUMBER) AS
BEGIN
PKG_LOGS.log('PKG_ANP_BANCA_FV', 'COVERAGE', 'Begin processing: '||cur_month_yyyymm);
MERGE INTO BANCA_FV A
USING (
WITH DAT1 AS (
SELECT * FROM (
SELECT DISTINCT CST_NBR, AR_ID, RELATIONSHIP
FROM TBL_BANCA
WHERE CST_NBR IS NOT NULL
AND STATUS IN ('VALID')
AND MONTH_ID = cur_month_yyyymm
)
PIVOT (
COUNT(DISTINCT AR_ID)
FOR RELATIONSHIP
IN (1 AS A1,2 AS A2)
)
),
DAT2 AS (
SELECT CST_NBR, A1, A2,
CASE
WHEN A1 >0 AND A2 = 0 THEN 1
WHEN A1 =0 AND A2 > 0 THEN 2
WHEN A1 >= 0 AND A2 >= 0 THEN 3
ELSE NULL
END AS FL_COVERAGE
FROM DAT1
)
SELECT CST_NBR, cur_month_yyyymm AS MONTH_ID, FL_COVERAGE
FROM DAT2
) B
ON (A.CST_NBR = B.CST_NBR AND A.MONTH_ID = B.MONTH_ID)
WHEN MATCHED THEN UPDATE SET
A.FL_COVERAGE = B.FL_COVERAGE
WHEN NOT MATCHED then
INSERT (A.CST_NBR, A.MONTH_ID, A.FL_COVERAGE)
VALUES (B.CST_NBR, B.MONTH_ID, B.FL_COVERAGE) ;
COMMIT;
PKG_LOGS.log('PKG_ANP_BANCA_FV', 'COVERAGE', 'End processing: '||cur_month_yyyymm);
END COVERAGE;
--------------------------------------------------------------
PROCEDURE FREELOOK_CONTRACTS(cur_month_yyyymm in NUMBER) AS
BEGIN
PKG_LOGS.log('PKG_ANP_BANCA_FV', 'FREELOOK_CONTRACTS', 'Begin processing: '||cur_month_yyyymm);
MERGE INTO BANCA_FV A
USING (
with dat1 as (
SELECT CST_NBR, AR_ID
FROM TBL_BANCA
WHERE CST_NBR IS NOT NULL
AND STATUS IN ('FREELOOK')
AND MONTH_ID = cur_month_yyyymm
)
select cst_nbr, cur_month_yyyymm AS MONTH_ID,
COUNT(DISTINCT AR_ID) AS CNT_FREELOOK_CONTRACTS
from dat1
group by cst_nbr
) B
ON (A.CST_NBR = B.CST_NBR AND A.MONTH_ID = B.MONTH_ID)
WHEN MATCHED THEN UPDATE SET
A.CNT_FREELOOK_CONTRACTS = B.CNT_FREELOOK_CONTRACTS
WHEN NOT MATCHED then
INSERT (A.CST_NBR, A.MONTH_ID, A.CNT_FREELOOK_CONTRACTS)
VALUES (B.CST_NBR, B.MONTH_ID, B.CNT_FREELOOK_CONTRACTS) ;
COMMIT;
PKG_LOGS.log('PKG_ANP_BANCA_FV', 'FREELOOK_CONTRACTS', 'End processing: '||cur_month_yyyymm);
END FREELOOK_CONTRACTS ;
--------END OF PACKAGE--------
END PKG_ANP_BANCA_FV;
No comments:
Post a Comment