emails

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;

                                

html letter

<!DOCTYPE HTML>

<html>
<head>
<meta charset="utf-8" />
        <style type="text/css">

            .bodyBody {
           width: 80%;
  margin-left: 70px;
  margin-right: 60px;
           font-size: 1.30em;
  border-style: double
            }
            .divHeader {
                text-align: right;
                border: 1px solid;
            }
            .divReturnAddress {
                text-align: left;
                float: right;
margin-right: 95px;
            }
            .divSubject {
                clear: both;
float: center;
text-align: center;
                font-weight: bold;
                padding-top: 40px;
margin-right: 80px;
            }
.divContents{
margin-left:25px;
}
            .divAdios {
                text-align: left;
float: right;
margin-right:100px;

             
            }
        </style>
    </head>
    <body class="bodyBody divAdios">
        <img src="file:///C|/Users/Keerthy/Desktop/firstdata.png" width="212" height="52" alt=""/>
        <div class="divReturnAddress">
            21 ,Bharathi Nagar<br/>
            Ayanavaram <br/>
            Chennai <br/>  
            <br/>
             <time datetime="2012-12-01">01-Dec-2012</time>
        </div>

        <div class="divSubject">             Welcome Letter
        </div>

        <div class="divContents">
          <p>
                Dear Sir,
          </p>
          <p> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;&nbsp;&nbsp;We are pleased to Welcome you for our prestigious organnization. </br>
           &nbsp;We foresee a lot of growth in our future endeavours.We appreciate your cooperation in </br>
             delevoping strong relation with our company.
          </p>
          </div>
        <div class="divAdios">Yours faithfully <br/>
            <br/>
            <br/>
            <br/>
            Joseph <br/>
            First Data <br/>
            </div>
    </body>
</html>

3 days no punch

--- AE STEP: 5
---
%Truncatetable (PS_TL_IPT2)
(DELETE FROM PS_TL_IPT2)
--------------------------------------------------------------------------------------------
--- AE STEP: 7
---
%Truncatetable (PS_TL_IPT3)
(DELETE FROM PS_TL_IPT3)
--------------------------------------------------------------------------------------------
--- AE STEP: 10
--- SQL Object ID: FDINDNOPUNCH_01
---
INSERT INTO %Table(TL_IPT2)
SELECT %BIND(PROCESS_INSTANCE), A.EMPLID,A.EMPL_RCD,A.DUR,500,' ','','',0,'','',0,0,0,' ',' ',0,0,0,' ',' ',' ',' ',' ',' ',' ',0,0,0
,0,0,' ',' ',' ',' ',' ',0,0,' ',' ',' ',' ',0,' ',' ',' ',' ',' ',' ',0,' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',0,' ',' ',0,0
FROM %Table(WRK_ADHOC_TAO) A
WHERE  A.PROCESS_INSTANCE = %Bind(PROCESS_INSTANCE)
AND A.OFFDAY_IND = 'N'
AND A.HOLIDAY_FLAG = 'N'
GROUP BY A.PROCESS_INSTANCE,A.EMPLID,A.EMPL_RCD,A.DUR
--------------------------------------------------------------------------------------------
--- AE STEP: 15
--- SQL Object ID: FDINDNOPCH_RANK
---
INSERT INTO %Table(TL_IPT3)
SELECT %BIND(PROCESS_INSTANCE), A.EMPLID,A.EMPL_RCD,A.DUR,500,' ','','',0,'','',0,0,0,' ',' ',0,0,0,' ',' ',' ',' ',' ',' ',' ',0,0,0
,RANK() OVER (ORDER BY A.DUR),0,' ',' ',' ',' ',' ',0,0,' ',' ',' ',' ',0,' ',' ',' ',' ',' ',' ',0,' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',0,' ',' ',0,0
FROM %Table(TL_IPT2) A
WHERE  A.PROCESS_INSTANCE = %Bind(PROCESS_INSTANCE)
--------------------------------------------------------------------------------------------
--- AE STEP: 17
--- SQL Object ID: FDINDNP_01
---
UPDATE %Table(TL_IPT3) A SET A.USER_FIELD_1 = (CASE WHEN A.DUR IN (SELECT B.DUR FROM %Table(TL_IPT1) B WHERE B.PROCESS_INSTANCE =  %Bind(PROCESS_INSTANCE) AND B.TRC IN (SELECT VALUEGROUP FROM PS_TL_VAL_LIST_DTL WHERE LIST_ID = ''')) THEN 'Y' ELSE 'N' END) WHERE A.PROCESS_INSTANCE = %Bind(PROCESS_INSTANCE)
--------------------------------------------------------------------------------------------
--- AE STEP: 18
--- SQL Object ID: FDINDNP_02
---
UPDATE %Table(TL_IPT3) A SET A.RULE_FLAG3 = 1
WHERE A.PROCESS_INSTANCE = %Bind(PROCESS_INSTANCE)
AND A.USER_FIELD_1 = 'N'
AND EXISTS(SELECT 'X' FROM %Table(TL_IPT3) B WHERE B.PROCESS_INSTANCE = %Bind(PROCESS_INSTANCE)
AND B.RULE_FLAG4 = (A.RULE_FLAG4-1) AND B.USER_FIELD_1 = 'N')
AND EXISTS(SELECT 'X' FROM %Table(TL_IPT3) C WHERE  C.PROCESS_INSTANCE = %Bind(PROCESS_INSTANCE)
AND C.RULE_FLAG4 = (A.RULE_FLAG4-2) AND C.USER_FIELD_1 = 'N')
--------------------------------------------------------------------------------------------
--- AE STEP: 19
--- SQL Object ID: FDINDNP_03
---
INSERT INTO %Table(TL_EXCEPT_WRK)
(PROCESS_INSTANCE
,VALID_INST_ID
,EXCEPTION_ID          
,EMPLID                
,EMPL_RCD              
,DUR                   
,SEQ_NBR               
,EXCEPTION_SOURCE
,UPDATE_FLAG           
,RUN_CNTL_ID           
,EXCEPTION_STATUS
,RESOLVED_BY           
,ACTION_DTTM           
,SEVERITY              
,START_DT              
,END_DT                
,ALLOW_IND             
,MSG_DATA1             
,MSG_DATA2             
,MSG_DATA3             
,MSG_DATA4             
,MSG_DATA5)
SELECT
%Bind(A.PROCESS_INSTANCE)
,0         
,'FDIN0001'
,A.EMPLID                
,A.EMPL_RCD              
,A.DUR                   
,A.SEQ_NBR               
,'TA'
,'U'           
,' '
,'U'
,' '
,%CurrentDateTimeIn           
,'L'              
,A.DUR
,A.DUR
,'Y'             
,' '
,%Bind(TL_EXC_RULE_AET.DESCR)
,' '
,' '
,' '
FROM %Table(TL_IPT3) A
WHERE A.PROCESS_INSTANCE = %bind(PROCESS_INSTANCE)
AND NOT EXISTS (SELECT 'X' FROM %Table(TL_EXCEPT_WRK) D
WHERE A.PROCESS_INSTANCE = D.PROCESS_INSTANCE
AND A.EMPLID = D.EMPLID
AND A.EMPL_RCD = D.EMPL_RCD
AND A.DUR = D.DUR
AND D.EXCEPTION_ID = 'FDIN0001')
AND A.RULE_FLAG3 = 1
AND A.USER_FIELD_1 = 'N'
AND A.DUR <= SYSDATE