N996YiCS

1-- based on flags in kcierpisz.emea_phones (specs source)
2-- and kcierpisz.emea_dnc_phones (DNC source)
3
4/* 1. prepare input file from requester
5   1a) delete all other columns except the column containing telephone number and country
6   2a) add additional column called excel_row that will contain the sequence number referring to the excel row (so we append the right permission to the right row)
7   3a) upload such file with 3 columns into lmXXXX_input
8*/
9
10desc lmXXXX_input
11COUNTRY  
12PHONE    
13EXCEL_ROW
14
15-- 2. check if all the countries are based on gcd_dw.gcd_countries
16-- following query shall not return any rows if all are matched
17-- if some countries are returned from the below query update them in the input table.
18select a.country from lmXXXX_input a
19where not exists (select 1 from gcd_dw.gcd_countries b
20                  where upper(a.country) = upper(b.name)
21                );
22/* let's say we had USA (in fact we should not bother in non-EMEA countries but this is just an example) in th input table which does not exist in gcd_countries 
23in that case we run update statement:
24
25UPDATE lmXXXX_input a set country = 'United States'
26where a.country = 'USA';
27commit;
28
29rerun above check until you get:
30no rows selected
31*/
32
33-- 3. process the phones - matching against SPECS and DNC (UK)
34
35alter table lmXXXX_input add (country_id number);
36update lmXXXX_input a set country_id =
37      (select country_id from gcd_dw.gcd_countries b
38                         where upper(b.name) = upper(a.country));
39commit;
40
41create table lmXXXX_input_prep as
42select a.*, kcierpisz.prepare_phone(a.country_id,a.phone) prepared_phone
43from lmXXXX_input a;
44
45alter table lmXXXX_input_prep add (phone_permission varchar2(1));
46update lmXXXX_input_prep a
47set phone_permission = (select contact_phone from kcierpisz.emea_phones b
48                        where b.country_id = a.country_id
49                        and a.prepared_phone = b.prepared_phone)
50where exists (select 1 from kcierpisz.emea_phones b
51             where b.country_id = a.country_id
52             and a.prepared_phone = b.prepared_phone);
53commit;
54
55/* phone_permission will hold now Y N or NULL
56   Y - contact_phone Y in SPECS
57   N - contact_phone N in SPECS
58   NULL -> phone not matched */
59
60/* in case of UK we have additionally check against DNC table for that -> we need to enhance
61*/
62
63update lmXXXX_input_prep a
64set phone_permission = (select 'N' from kcierpisz.emea_dnc_phones b
65                        where b.country_id = a.country_id
66                        and a.prepared_phone = b.prepared_phone)
67where exists (select 1 from kcierpisz.emea_dnc_phones b
68             where b.country_id = a.country_id
69             and a.prepared_phone = b.prepared_phone);
70commit;
71
72/* we end up with this table
73desc lmXXXX_input_prep;
74COUNTRY         
75PHONE           
76EXCEL_ROW       
77COUNTRY_ID      
78PREPARED_PHONE  
79PHONE_PERMISSION --> phone permission
80*/
81
82/* 4. download the file into csv order by excel_row, and append the phone_permission to the original file from the requester */