1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
| create or replace procedure Purge_Histo as
BEGIN
DECLARE
CURSOR id_cust Is
select cust.customer_number,nvl(cust.fk_household_id,0) fk_household_id, nvl(cust.fk_legalentity_id,0) fk_legalentity_id from customer cust
where cust.customer_number between 0 and 500000;
c_limit integer := 1000;
TYPE id_cust_type IS TABLE OF id_cust%ROWTYPE INDEX BY BINARY_INTEGER;
id_cust_array id_cust_type; --157540 ms
--id_cust_array customer%rowtype;
BEGIN
FOR IDS IN id_cust LOOP
--FETCH id_cust BULK COLLECT INTO id_cust_array LIMIT c_limit;
INSERT INTO PURGE_HISTO_DC ("CUSTOMER_NUMBER","CREATION_DATE","EVENT_CONSENT","EVENT_CONSENT_NB","APPLICATION")
select cust.customer_number, cust.update_date, 'CUSTOMER',cust.customer_number, 'SCR'
from customer cust
where cust.customer_number = IDS.customer_number;
INSERT INTO PURGE_HISTO_DC ("CUSTOMER_NUMBER","CREATION_DATE","EVENT_CONSENT","EVENT_CONSENT_NB","APPLICATION")
select ah.fk_customer_id,nvl(ah.business_date,ah.creation_date), 'HISTORIC', ah.action_history_id, 'SCR'
from action_history ah
where ah.fk_customer_id = IDS.customer_number;
INSERT INTO PURGE_HISTO_DC ("CUSTOMER_NUMBER","CREATION_DATE","EVENT_CONSENT","EVENT_CONSENT_NB","APPLICATION")
select IDS.customer_number, ad.update_date, 'ADDRESS', ad.address_id, 'SCR'
from address ad
where ad.fk_household_id = IDS.fk_HOUSEHOLD_ID;
INSERT INTO PURGE_HISTO_DC ("CUSTOMER_NUMBER","CREATION_DATE","EVENT_CONSENT","EVENT_CONSENT_NB","APPLICATION")
select IDS.customer_number, ad.update_date, 'ADDRESS', ad.address_id, 'SCR'
from address ad
where ad.FK_LEGALENTITY_ID = IDS.FK_LEGALENTITY_ID;
INSERT INTO PURGE_HISTO_DC ("CUSTOMER_NUMBER","CREATION_DATE","EVENT_CONSENT","EVENT_CONSENT_NB","APPLICATION")
select IDS.customer_number, hh.update_date, 'HOUSEHOLD', hh.household_id, 'SCR'
from household hh
where hh.household_id = IDS.fk_HOUSEHOLD_ID;
INSERT INTO PURGE_HISTO_DC ("CUSTOMER_NUMBER","CREATION_DATE","EVENT_CONSENT","EVENT_CONSENT_NB","APPLICATION")
select IDS.CUSTOMER_NUMBER, comm.update_date, 'COMMUNICATION', comm.communication_id, 'SCR'
from communication comm
where comm.fk_naturalperson_id = IDS.CUSTOMER_NUMBER;
INSERT INTO PURGE_HISTO_DC ("CUSTOMER_NUMBER","CREATION_DATE","EVENT_CONSENT","EVENT_CONSENT_NB","APPLICATION")
select IDS.customer_number, ei.update_date, 'EXTERNAL_ID', ei.external_identifier_id, 'SCR'
from external_identifier ei
where ei.fk_customer_id = IDS.customer_number;
INSERT INTO PURGE_HISTO_DC ("CUSTOMER_NUMBER","CREATION_DATE","EVENT_CONSENT","EVENT_CONSENT_NB","APPLICATION")
select IDS.customer_number, ll.update_date, 'LEISURE', ll.LEISURE_ID, 'SCR'
from leisure ll
where ll.FK_NATURALPERSONCUSTOMER_ID = IDS.customer_number;
INSERT INTO PURGE_HISTO_DC ("CUSTOMER_NUMBER","CREATION_DATE","EVENT_CONSENT","EVENT_CONSENT_NB","APPLICATION")
select IDS.customer_number, np.update_date, 'NOTEPAD', np.NOTEPAD_ID, 'SCR'
from NOTEPAD np
where np.FK_CUSTOMER_ID = IDS.customer_number;
INSERT INTO PURGE_HISTO_DC ("CUSTOMER_NUMBER","CREATION_DATE","EVENT_CONSENT","EVENT_CONSENT_NB","APPLICATION")
select IDS.customer_number, opt.update_date, 'OPTIN', opt.OPTIN_ID, 'SCR'
from OPTIN opt
where opt.fk_customer_id = IDS.customer_number;
INSERT INTO PURGE_HISTO_DC ("CUSTOMER_NUMBER","CREATION_DATE","EVENT_CONSENT","EVENT_CONSENT_NB","APPLICATION")
select IDS.customer_number, opt.update_date, 'OPTIN', opt.OPTIN_ID, 'SCR'
from OPTIN opt
join communication comm on comm.communication_id = opt.fk_communication_id
where comm.fk_naturalperson_id = IDS.customer_number;
INSERT INTO PURGE_HISTO_DC ("CUSTOMER_NUMBER","CREATION_DATE","EVENT_CONSENT","EVENT_CONSENT_NB","APPLICATION")
select IDS.customer_number, opt.update_date, 'OPTIN', opt.OPTIN_ID, 'SCR'
from OPTIN opt
join address ad on ad.address_id = opt.fk_address_id
where ad.fk_household_id = IDS.fk_HOUSEHOLD_ID;
INSERT INTO PURGE_HISTO_DC ("CUSTOMER_NUMBER","CREATION_DATE","EVENT_CONSENT","EVENT_CONSENT_NB","APPLICATION")
select IDS.customer_number, opt.update_date, 'OPTIN', opt.OPTIN_ID, 'SCR'
from OPTIN opt
join address ad on ad.address_id = opt.fk_address_id
where ad.FK_LEGALENTITY_ID = IDS.FK_LEGALENTITY_ID;
END LOOP IDS;
end;
end; |
