SET ECHO ON
SPOOL C:\TEMP\Email_Conversion_sqls.LOG
--Step 1 Insert into PS_EMAIL_ADDRESSES if the email does not exists.
--Select Statement
SELECT COUNT(*) FROM (SELECT A. EMPLID, 'BUSN',A.ZINEMAIL_ADDR,'N'
FROM PS_ZINEMAIL_TBL A
WHERE A.EMPLID IN (SELECT EMPLID FROM PS_JOB_CURR_VW)
AND A.ZINEMAIL_ADDR <>' '
AND NOT EXISTS( SELECT' X ' FROM PS_EMAIL_ADDRESSES C
WHERE A.EMPLID=C.EMPLID
AND C.E_ADDR_TYPE='BUSN')) ;
--Insert Statement
INSERT INTO PS_EMAIL_ADDRESSES
(EMPLID,E_ADDR_TYPE,EMAIL_ADDR,PREF_EMAIL_FLAG)
SELECT A. EMPLID, 'BUSN',A.ZINEMAIL_ADDR,'N'
FROM PS_ZINEMAIL_TBL A
WHERE A.EMPLID IN (SELECT EMPLID FROM PS_JOB_CURR_VW)
AND A.ZINEMAIL_ADDR <>' '
AND NOT EXISTS( SELECT' X ' FROM PS_EMAIL_ADDRESSES C
WHERE A.EMPLID=C.EMPLID
AND C.E_ADDR_TYPE='BUSN') ;
--Step 2 Update Email address if it differs in both records
--Select Statement
SELECT COUNT(*) FROM (SELECT A.EMPLID,A.ZINEMAIL_ADDR,E.EMAIL_ADDR
FROM PS_ZINEMAIL_TBL A,PS_EMAIL_ADDRESSES E
WHERE A.EMPLID IN (SELECT EMPLID FROM PS_JOB_CURR_VW)
AND A.EMPLID=E.EMPLID
AND TRIM(A.ZINEMAIL_ADDR) <>' '
AND E.E_ADDR_TYPE='BUSN'
AND UPPER (A.ZINEMAIL_ADDR)<> UPPER(E.EMAIL_ADDR)) ;
--Update Statement
UPDATE PS_EMAIL_ADDRESSES E SET E.EMAIL_ADDR =
(SELECT A.ZINEMAIL_ADDR
FROM PS_ZINEMAIL_TBL A
WHERE A.EMPLID IN (SELECT EMPLID FROM PS_JOB_CURR_VW)
AND A.EMPLID=E.EMPLID
AND TRIM(A.ZINEMAIL_ADDR) <>' '
AND E.E_ADDR_TYPE='BUSN'
AND UPPER (A.ZINEMAIL_ADDR) <> UPPER(E.EMAIL_ADDR) )
WHERE EXISTS ( SELECT 'X'
FROM PS_ZINEMAIL_TBL A
WHERE A.EMPLID IN (SELECT EMPLID FROM PS_JOB_CURR_VW)
AND TRIM(A.ZINEMAIL_ADDR) <>' '
AND A.EMPLID=E.EMPLID
AND E.E_ADDR_TYPE='BUSN'
AND UPPER (A.ZINEMAIL_ADDR) <> UPPER(E.EMAIL_ADDR) );
--Step 3 Check the mails are updated in EMAIL_ADDRESSES
--Select Statement
SELECT COUNT(*) FROM (SELECT A.EMPLID,A.E_ADDR_TYPE,A.EMAIL_ADDR,B.ZINEMAIL_ADDR FROM PS_EMAIL_ADDRESSES A , PS_ZINEMAIL_TBL B
WHERE A.EMPLID=B.EMPLID AND A.E_ADDR_TYPE='BUSN' AND B.ZINEMAIL_ADDR <> ' '
AND UPPER(TRIM(A.EMAIL_ADDR)) <> UPPER(TRIM(B.ZINEMAIL_ADDR))
and A.EMPLID IN (SELECT EMPLID FROM PS_JOB_CURR_VW)) ;
--step 4 If there is only one Email Address type then make it Preferred flag
--Select Statement
SELECT COUNT (*) FROM (SELECT DISTINCT EMPLID, COUNT( EMAIL_ADDR) FROM PS_EMAIL_ADDRESSES WHERE EMPLID IN (SELECT EMPLID FROM PS_JOB_CURR_VW)
GROUP BY EMPLID
HAVING COUNT( EMAIL_ADDR) = 1);
--Update Statement
UPDATE PS_EMAIL_ADDRESSES E
SET E.PREF_EMAIL_FLAG = 'Y'
WHERE 1 = (SELECT COUNT ('X')
FROM PS_EMAIL_ADDRESSES E1
WHERE E1.EMPLID = E.EMPLID)
AND E.EMPLID IN (SELECT EMPLID FROM PS_JOB_CURR_VW);
--Step 5 If there is a ‘BUSN’ Email type for the employee, set its PREF_EMAIL_FLAG to ‘Y’provided he doesnt have any other preferred mail type
--Select Statement
SELECT COUNT(*) FROM (SELECT EMPLID,E_ADDR_TYPE,PREF_EMAIL_FLAG FROM PS_EMAIL_ADDRESSES WHERE E_ADDR_TYPE='BUSN' AND PREF_EMAIL_FLAG='N'
AND EMPLID NOT IN (SELECT EMPLID FROM PS_EMAIL_ADDRESSES WHERE PREF_EMAIL_FLAG='Y'));
--Update Statement
UPDATE PS_EMAIL_ADDRESSES SET PREF_EMAIL_FLAG='Y' WHERE (EMPLID,E_ADDR_TYPE) IN (
SELECT EMPLID,E_ADDR_TYPE FROM PS_EMAIL_ADDRESSES WHERE E_ADDR_TYPE='BUSN' AND PREF_EMAIL_FLAG='N'
AND EMPLID NOT IN (SELECT EMPLID FROM PS_EMAIL_ADDRESSES WHERE PREF_EMAIL_FLAG='Y') );
--Step 6 If ‘BUSN’ does not exist and is not set as preferred then select the first phone type (alphabetically) and set its PREF_PHONE_FLAG = ‘Y’
--Select Statement
SELECT COUNT(*) FROM (SELECT E.EMPLID FROM PS_EMAIL_ADDRESSES E
WHERE NOT EXISTS
(SELECT 'X'
FROM PS_EMAIL_ADDRESSES E1
WHERE E.EMPLID = E1.EMPLID
AND E1.E_ADDR_TYPE = 'BUSN')
AND NOT EXISTS
(SELECT 'X'
FROM PS_EMAIL_ADDRESSES E1
WHERE E.EMPLID = E1.EMPLID
AND E.PREF_EMAIL_FLAG = 'Y')
GROUP BY EMPLID
HAVING COUNT ('X') > 1);
--Update Statement
UPDATE PS_EMAIL_ADDRESSES A
SET PREF_EMAIL_FLAG = 'Y'
WHERE A.EMPLID IN
( SELECT E.EMPLID
FROM PS_EMAIL_ADDRESSES E
WHERE NOT EXISTS
(SELECT 'X'
FROM PS_EMAIL_ADDRESSES E1
WHERE E.EMPLID = E1.EMPLID
AND E1.E_ADDR_TYPE = 'BUSN')
AND NOT EXISTS
(SELECT 'X'
FROM PS_EMAIL_ADDRESSES E1
WHERE E.EMPLID = E1.EMPLID
AND E.PREF_EMAIL_FLAG = 'Y')
GROUP BY EMPLID
HAVING COUNT ('X') > 1)
AND A.E_ADDR_TYPE = (SELECT MIN(B.E_ADDR_TYPE) FROM PS_EMAIL_ADDRESSES B WHERE B.EMPLID =A.EMPLID);
--Step 7 if there is more than one Email type set as preferred change the other email types as 'N' except 'BUSN'
--Select Statement
SELECT COUNT (*) FROM (SELECT EMPLID, COUNT( PREF_EMAIL_FLAG)
FROM PS_EMAIL_ADDRESSES WHERE PREF_EMAIL_FLAG='Y'
GROUP BY EMPLID
HAVING COUNT( PREF_EMAIL_FLAG) > 1);
--Update Statement i
UPDATE PS_EMAIL_ADDRESSES E SET E.PREF_EMAIL_FLAG = 'N'
WHERE EXISTS (SELECT 'X' FROM PS_EMAIL_ADDRESSES WHERE E_ADDR_TYPE = 'BUSN' AND PREF_EMAIL_FLAG='Y'
AND EMPLID=E.EMPLID)
AND E.EMPLID IN
(SELECT EMPLID
FROM PS_EMAIL_ADDRESSES
WHERE EMPLID IN ( SELECT EMPLID
FROM PS_EMAIL_ADDRESSES
WHERE PREF_EMAIL_FLAG = 'Y'
GROUP BY EMPLID
HAVING COUNT (PREF_EMAIL_FLAG) > 1)) AND E.E_ADDR_TYPE <> 'BUSN' ;
--Step 8 if there is more than one Email type set as preferred change the other email types as 'N' except for the lowest ascending ordered email type
UPDATE PS_EMAIL_ADDRESSES A
SET PREF_EMAIL_FLAG = 'N'
WHERE A.EMPLID IN
(SELECT EMPLID
FROM PS_EMAIL_ADDRESSES
WHERE EMPLID IN ( SELECT EMPLID
FROM PS_EMAIL_ADDRESSES
WHERE PREF_EMAIL_FLAG = 'Y'
GROUP BY EMPLID
HAVING COUNT (PREF_EMAIL_FLAG) > 1))
AND A.E_ADDR_TYPE > (SELECT MIN(B.E_ADDR_TYPE) FROM PS_EMAIL_ADDRESSES B WHERE B.EMPLID =A.EMPLID);
SET SPOOL OFF;
SET ECHO OFF;
No comments:
Post a Comment