Auto Generate Attendance Procedure

CREATE OR REPLACE PROCEDURE APPS.IFL_AUTGEN_EMP_ATTENDANCE (
   ERRBUFF      VARCHAR2,
   RETCODE      VARCHAR2,
   P_GROUP_ID   NUMBER,
   DATE_FROM    VARCHAR2,
   DATE_TO      VARCHAR2
)
IS
   P_DATE_FROM DATE := FND_CONC_DATE.STRING_TO_DATE (DATE_FROM);
   P_DATE_TO DATE := FND_CONC_DATE.STRING_TO_DATE (DATE_TO);

  CURSOR C_DAYS
  IS
 
  SELECT DISTINCT PPX.PERSON_ID EMPLOYEEID,
                  HAAI.EFFECTIVE_START_DATE,
                  HAAI.EFFECTIVE_END_DATE
             FROM PER_PEOPLE_X PPX,
                  PER_ASSIGNMENTS_X PAX,
                  HXT_ADD_ASSIGN_INFO_F HAAI,
                  HXC_TK_GROUPS HTG,
                  HXC_TK_GROUP_QUERIES HTGQ,
                  HXC_TK_GROUP_QUERY_CRITERIA HTGQC
            WHERE 1 = 1
              AND PAX.PERSON_ID = PPX.PERSON_ID
              AND HAAI.ASSIGNMENT_ID = PAX.ASSIGNMENT_ID
              AND HAAI.AUTOGEN_HOURS_YN = 'Y'
              AND HTGQC.CRITERIA_ID = PPX.PERSON_ID
              AND HTGQ.TK_GROUP_QUERY_ID = HTGQC.TK_GROUP_QUERY_ID
              AND HTG.TK_GROUP_ID = HTGQ.TK_GROUP_ID
              and haai.EFFECTIVE_END_DATE <> ('31-DEC-4712')
              AND HAAI.EFFECTIVE_START_DATE >= P_DATE_FROM
              AND HAAI.EFFECTIVE_END_DATE <= P_DATE_TO
              AND HTG.TK_GROUP_ID = NVL (P_GROUP_ID, HTG.TK_GROUP_ID) ;
       


   CURSOR C_AG(P_PERSON_ID NUMBER, P_EFF_DATE_FROM DATE, P_EFF_DATE_TO DATE, P_NO_OF_DAYS NUMBER)
   IS
      select  * from
     (
       SELECT EMPLOYEEID,
       DECODE(WORK_DAY,1,EFFECTIVE_START_DATE,(EFFECTIVE_START_DATE+WORK_DAY-1)) ATTENDANCEDATE,
       LOCATION_ID, ATTENDANCETYPE, CARDNO,      
       DEFAULTCLOCKINFLAG, DEFAULTCLOCKOUTFLAG,WORK_DAY,
       DEFAULTATTENDANCETYPE,8 WORKEDHOURS, 0 STATUS, 'AUTOGEN' PATH, 0 AUTOFLAG,      
       trim(IFL_EMP_ABSENCE_UPLOAD_MONTHLY.GET_OFF_SHIFT(DECODE(WORK_DAY,1,EFFECTIVE_START_DATE,(EFFECTIVE_START_DATE+WORK_DAY-1)),EMPLOYEEID)) CALENDARID
       FROM
       (
                SELECT DISTINCT PPX.PERSON_ID EMPLOYEEID,
                               PAX.LOCATION_ID,
                                'P' ATTENDANCETYPE, PAX.ASS_ATTRIBUTE7 CARDNO,                    
                                0 DEFAULTCLOCKINFLAG, 0 DEFAULTCLOCKOUTFLAG,
                                'P' DEFAULTATTENDANCETYPE, 8 WORKEDHOURS,
                                HAAI.EFFECTIVE_START_DATE,
                                HAAI.EFFECTIVE_END_DATE
                 FROM PER_PEOPLE_X PPX,
                      PER_ASSIGNMENTS_X PAX,
                      HXT_ADD_ASSIGN_INFO_F HAAI,
                      HXC_TK_GROUPS HTG,
                      HXC_TK_GROUP_QUERIES HTGQ,
                      HXC_TK_GROUP_QUERY_CRITERIA HTGQC
                WHERE 1 = 1
                  AND PAX.PERSON_ID = PPX.PERSON_ID
                  AND HAAI.ASSIGNMENT_ID = PAX.ASSIGNMENT_ID
                  AND HAAI.AUTOGEN_HOURS_YN = 'Y'
                  AND HTGQC.CRITERIA_ID = PPX.PERSON_ID
                  AND HTGQ.TK_GROUP_QUERY_ID = HTGQC.TK_GROUP_QUERY_ID
                  AND HTG.TK_GROUP_ID = HTGQ.TK_GROUP_ID
                  AND PPX.PERSON_ID = P_PERSON_ID
                  AND HAAI.EFFECTIVE_START_DATE >= P_EFF_DATE_FROM
                  AND HAAI.EFFECTIVE_END_DATE <= P_EFF_DATE_TO
                  )
              ATG,    
             (SELECT LEVEL WORK_DAY FROM DUAL CONNECT BY LEVEL <= P_NO_OF_DAYS)
             ORDER BY WORK_DAY
) ag        
where (ag.ATTENDANCEDATE, AG.EMPLOYEEID) NOT IN (
                     SELECT TR.ATTENDANCEDATE, TR.EMPLOYEEID
                     FROM TAD_REGISTER TR
                     WHERE TR.ATTENDANCEDATE BETWEEN P_EFF_DATE_FROM AND P_EFF_DATE_TO);
     

   R_DAYS C_DAYS%ROWTYPE;
   C_AT C_AG%ROWTYPE;
   P_NO_OF_DAYS NUMBER;
   V_CLOCK_IN  DATE;
   V_CLOCK_OUT DATE;
 
BEGIN
   
DBMS_OUTPUT.PUT_LINE ('P_DATE_FROM-' || P_DATE_FROM);  
DBMS_OUTPUT.PUT_LINE ('P_DATE_TO-' || P_DATE_TO);  

    OPEN C_DAYS;  
    LOOP
        FETCH C_DAYS INTO R_DAYS;
        EXIT WHEN C_DAYS%NOTFOUND;        

        DBMS_OUTPUT.PUT_LINE ('R_DAYS.EFFECTIVE_END_DATE-' || R_DAYS.EFFECTIVE_END_DATE);  
        DBMS_OUTPUT.PUT_LINE ('R_DAYS.EFFECTIVE_START_DATE-' || R_DAYS.EFFECTIVE_START_DATE);  
       
        P_NO_OF_DAYS := (R_DAYS.EFFECTIVE_END_DATE - R_DAYS.EFFECTIVE_START_DATE) + 1;
        DBMS_OUTPUT.PUT_LINE ('P_NO_OF_DAYS-' || P_NO_OF_DAYS);  

        OPEN C_AG(R_DAYS.EMPLOYEEID, R_DAYS.EFFECTIVE_START_DATE, R_DAYS.EFFECTIVE_END_DATE, P_NO_OF_DAYS);
        LOOP
            FETCH C_AG INTO C_AT;
            EXIT WHEN C_AG%NOTFOUND;

            BEGIN
           
                     ---------  FIND SHIFT IN & OUT TIMING ------------------
                   
                     select         tad_rfid_download.GET_STD_CLOCK_IN_OUT(cd.CLOCK_IN,cd.CLOCK_OUT,C_AT.ATTENDANCEDATE,0) ,
                                    tad_rfid_download.GET_STD_CLOCK_IN_OUT(cd.CLOCK_IN,cd.CLOCK_OUT,C_AT.ATTENDANCEDATE,1)
                                    INTO  V_CLOCK_IN,
                                          V_CLOCK_OUT          
                     from            tad_calendardetail cd                
                     where           cd.CALENDARID = C_AT.CALENDARID ;
                       
           
           
             INSERT INTO TAD_REGISTER
                         (EMPLOYEEID,
                          ATTENDANCEDATE,
                          LOCATIONID,
                          ATTENDANCETYPE,
                          CARDNO,
                          CALENDARID,
                          DEFAULTCLOCKINFLAG,
                          DEFAULTCLOCKOUTFLAG,
                          DEFAULTATTENDANCETYPE,
                          CLOCKIN,
                          CLOCKOUT,
                          DEFAULTCLOCKIN,
                          DEFAULTCLOCKOUT,
                          WORKEDHOURS,
                          STATUS,
                          PATH,
                          AUTOFLAG
                         )
                  VALUES (C_AT.EMPLOYEEID,
                          C_AT.ATTENDANCEDATE,
                          C_AT.LOCATION_ID,
                          C_AT.ATTENDANCETYPE,
                          C_AT.CARDNO,
                          C_AT.CALENDARID,
                          C_AT.DEFAULTCLOCKINFLAG,
                          C_AT.DEFAULTCLOCKOUTFLAG,
                          C_AT.DEFAULTATTENDANCETYPE,
                          V_CLOCK_IN,
                          V_CLOCK_OUT,
                           V_CLOCK_IN,
                          V_CLOCK_OUT,
                          C_AT.WORKEDHOURS,
                          C_AT.STATUS,
                          C_AT.PATH,
                          C_AT.AUTOFLAG
                         );
                COMMIT;                        

             DBMS_OUTPUT.PUT_LINE ('Employee Auto Gen Attendance     : ' || C_AT.EMPLOYEEID || ': ' || C_AT.ATTENDANCEDATE);
             FND_FILE.PUT_LINE (FND_FILE.LOG, 'Employee Auto Gen Attendance     : ' || C_AT.EMPLOYEEID || ': ' || C_AT.ATTENDANCEDATE);
            EXCEPTION
             WHEN OTHERS
             THEN
           
                DBMS_OUTPUT.PUT_LINE ('<<<<<<<<<<''Exception Employee Auto Gen Attendance ' || SQLERRM || '>>>>>>>>>');
                FND_FILE.PUT_LINE (FND_FILE.LOG, 'Exception Employee Auto Gen Attendance ' || SQLERRM || '>>>>>>>>>');
               
            END;

        END LOOP;
       CLOSE C_AG;
   
    END LOOP;
    CLOSE C_DAYS;

EXCEPTION
WHEN OTHERS THEN

    DBMS_OUTPUT.PUT_LINE ('<<<<<<<<<<''Exception Employee Auto Gen Attendance ' || SQLERRM || '>>>>>>>>>');
    FND_FILE.PUT_LINE (FND_FILE.LOG, 'Exception Employee Auto Gen Attendance ' || SQLERRM || '>>>>>>>>>');
   
END;
/