Query:  

SELECTempl_leave_trxn_id,
       employee_id,
       leave_name,
       leave_type_id,
       To_char(start_datetime,‘DD-MON-YYYY’)from_date,
       To_char(end_datetime,‘DD-MON-YYYY’)   to_date
       – ,CASE WHEN absence_type =’Permission Leave’ THEN TO_CHAR(START_DATETIME, ’DD-MON-YYYY HH24:MI:SS’) ELSE from_date END from_date
       – ,CASE WHEN absence_type =’Permission Leave’ THEN TO_CHAR(END_DATETIME, ’DD-MON-YYYY HH24:MI:SS’) ELSE TO_date  END to_date
       ,
       absence_type
       –,CASE WHEN absence_type !=’Permission Leave’ THEN days ELSE NULL END days
       ,
       CASE
         WHENabsence_typeNOTLIKE‘Permission%’THENdays
         ELSENULL
       END                                    days
       –   ,reason
       –  ,CASE WHEN absence_type =’Permission Leave’ THEN (days *60) ELSE NULL END permission_minutes
       –  ,CASE WHEN absence_type LIKE ’Permission%’ THEN (days *60) ELSE NULL END permission_minutes
       ,
       CASE
         WHENabsence_typeLIKE‘Permission%’THENRound((days* 60 ), 0)
         ELSENULL
       END                                    permission_minutes,
       reason,
       transaction_date,
       comments_and_description,
       CASE
         WHENabsence_status_cd=‘ORA_WITHDRAWN’THEN‘WITHDRAWN’
         ELSEleave_status
       END                                    leave_Status,
       applied_by,
       CASE
         WHENleave_statusIN(‘APPROVED’,‘DENIED’)THEN
         (SELECTcomm.wfcomment
          FROM
         fa_fusion_soainfra.wfcomments_viewcomm,
         fa_fusion_soainfra.wftasktask
                                                             WHERE  1 = 1
                                                                    AND
comm.taskid=task.taskid
        AND
task.identificationkey=To_char(empl_leave_trxn_id)
        –‘300000023933205’
        ANDrownum< 2
        AND
comm.version=(SELECTMax(comm.version)
FROM   fa_fusion_soainfra.wfcomments_viewcomm,
fa_fusion_soainfra.wftasktask
WHERE  1 = 1
ANDcomm.taskid=task.taskid
ANDtask.identificationkey=To_char(
empl_leave_trxn_id)
–‘300000023933205’
))
ELSENULL
END                                    Approver_Comments,
CASE
WHENleave_statusIN(‘APPROVED’,‘DENIED’)THEN
Nvl((SELECTUpper(task.approvers)–UPPER(comm.UPDATEDBY)
FROM   fa_fusion_soainfra.wftasktask
– ,fa_fusion_soainfra.wfcomments_view comm,
WHERE  1 = 1
–AND comm.TASKID=task.TASKID
ANDtask.identificationkey=To_char(empl_leave_trxn_id)
–‘300000023933205’
–AND comm.VERSION =
ANDrownum< 2
ANDtask.version=(SELECTMax(task.version)–MAX(comm.VERSION)
FROM   fa_fusion_soainfra.wftasktask
–,fa_fusion_soainfra.wfcomments_view comm,
WHERE  1 = 1
–  AND comm.TASKID=task.TASKID
ANDtask.identificationkey=To_char(
empl_leave_trxn_id)
–‘300000023933205’
)),approved_by)
ELSENULL
END                                    Approved_By,
CASE
WHENleave_statusIN(‘APPROVED’,‘DENIED’)THEN
Nvl((SELECTTo_char(comm.commentdate,‘DD-MON-YYYY HH24:MI:SS’)
FROM   fa_fusion_soainfra.wfcomments_viewcomm,
fa_fusion_soainfra.wftasktask
WHERE  1 = 1
ANDcomm.taskid=task.taskid
ANDtask.identificationkey=To_char(empl_leave_trxn_id)
–‘300000023933205’
ANDrownum< 2
ANDcomm.version=(SELECTMax(comm.version)
FROM   fa_fusion_soainfra.wfcomments_viewcomm
,
fa_fusion_soainfra.wftasktask
WHERE  1 = 1
ANDcomm.taskid=task.taskid
ANDtask.identificationkey=To_char(
empl_leave_trxn_id)
–‘300000023933205’
)),approved_date)
ELSENULL
END                                    Approved_Date
—   ,CASE WHEN  LEAVE_STATUS IN (‘APPROVED’,’DENIED’) THEN NULL ELSE NULL  END Approver_Comments
—   ,CASE WHEN  LEAVE_STATUS IN (‘APPROVED’,’DENIED’) THEN APPROVED_BY ELSE NULL END Approved_By
—   ,CASE WHEN  LEAVE_STATUS IN (‘APPROVED’,’DENIED’) THEN Approved_Date ELSE NULL END Approved_Date
,
absence_status_cd,
long_leave_dt_from
– ,Long_leave_dt_to
,
long_leave_comments,
long_leave_creation_dt,
long_leave_created_by
FROM   (SELECTapae.per_absence_entry_id
                       Empl_Leave_Trxn_ID,
               per.person_number
                       Employee_ID,
               abs.absence_type_id
                       leave_type_id,
               abs.NAME
                       absence_type,
               To_char(apae.start_date,‘DD-MON-YYYY’)
                       from_date–,’nls_date_language = american’)  from_date
               ,
               To_char(apae.end_date,‘DD-MON-YYYY’)
                       to_date–,’nls_date_language = american’)  to_date
               ,
               duration
                       days,
               (SELECTabsence_reason_id
                FROM   anc_absence_type_reasons_fabtr
                WHERE  1 = 1
                       ANDabtr.absence_type_reason_id
                           =apae.absence_type_reason_id)
                       reason,
               NULL
                       permission_minutes
               –,null transactionmode
               ,
               To_char(apae.creation_date,‘DD-MON-YYYY HH24:MI:SS’)
                       transaction_date,
               apae.comments
                       comments_and_description,
               apae.approval_status_cd
                       leave_Status,
               apae.created_by
                       Applied_By,
               NULL
                       Approver_Comments,
               apae.last_updated_by
                       Approved_By,
               To_char(apae.last_update_date,‘DD-MON-YYYY HH24:MI:SS’)
                       Approved_Date,
               start_datetime,
               end_datetime,
               absence_status_cd– 
               ,
               NULL
                       leave_name,
               NULL
                       Long_leave_dt_from
               – ,NULL Long_leave_dt_to
               ,
               NULL
                       Long_leave_comments,
               NULL
                       Long_leave_creation_dt,
               NULL
                       Long_leave_created_by
        FROM   anc_per_abs_entriesapae,
               per_periods_of_servicepps,
               per_all_people_fper,
               anc_absence_types_vlabs,
               (SELECThao.organization_id
                FROM   hr_all_organization_units_fhao,
                       hr_organization_units_f_tlhaot
                WHERE  hao.organization_id=haot.organization_id
                       ANDhao.effective_start_date=haot.effective_start_date
                       ANDhao.effective_end_date=haot.effective_end_date
                       ANDTrunc(sysdate)BETWEENhao.effective_start_dateAND
                                                  hao.effective_end_date
                       ANDhaot.language=‘US’
                       ANDhaot.NAME=XX Health Private Limited’
                       ANDorganization_code=‘XX001’)ind_emp
        WHERE  1 = 1
               ANDapae.period_of_service_id=pps.period_of_service_id
               ANDapae.absence_type_id=abs.absence_type_id
               ANDpps.person_id=per.person_id
               ANDTrunc(sysdate)BETWEENabs.effective_start_dateAND
                                          abs.effective_end_date
               ANDTrunc(sysdate)BETWEENper.effective_start_dateAND
                                          per.effective_end_date
               – and per.person_number  IN (‘101071’)– (‘AR06947’)
               ANDTo_char(apae.creation_date,‘YYYY-MM-DD’)>=‘2022-07-20’
               – Added CUT OFF DATE logic as per Padhu on 25July22
               ANDind_emp.organization_id=apae.legal_entity_id
               ANDabsence_status_cd<>‘SAVED’
               –AND ABSENCE_STATUS_CD NOT IN (‘SAVED’,’ORA_WITHDRAWN’)
               –AND apae.comments NOT IN (‘Migrated Absence Entry’,’As per HR request’)
               ANDNvl(apae.comments,‘X’)NOTIN(
                   ‘Migrated Absence Entry’,‘As per HR request’)
               –AND to_char(apae.start_date,’DD-MM-YYYY’ ) >’20-07-2022′
               ANDTo_char(apae.start_date,‘YYYY-MM-DD’)>‘2022-07-20’
               –AND (apae.last_update_date )>= TO_DATE (:p_last_run_dt, ’YYYY-MM-DD”T”HH24:MI:SS’)
               AND((apae.creation_date)>=
                     To_date(:p_last_run_dt,‘YYYY-MM-DD”T”HH24:MI:SS’)
                      OR(apae.last_update_date)>=
                         To_date(:p_last_run_dt,‘YYYY-MM-DD”T”HH24:MI:SS’))
        –OR        (abs.creation_date)>= TO_DATE (:p_last_run_dt, ’YYYY-MM-DD”T”HH24:MI:SS’) OR (abs.last_update_date )>= TO_DATE (:p_last_run_dt, ’YYYY-MM-DD”T”HH24:MI:SS’))
        UNION
        /*SELECT 
             NULL Empl_Leave_Trxn_ID
          ,(select person_number from per_all_people_f where person_id=doc.person_id) Employee_ID
          ,NULL leave_type_id    
          ,NULL absence_type
               ,NULL from_date
               ,NULL to_date
               ,NULL days
          ,NULL reason
               ,NULL permission_minutes
               ,NULL transaction_date
               ,NULL comments_and_description
               ,NULL leave_Status
               ,NULL Applied_By
          ,NULL Approver_Comments
          ,NULL Approved_By
          ,NULL Approved_Date
          ,NULL START_DATETIME
          ,NULL END_DATETIME
          ,NULL absence_status_cd  
          –,DOCUMENT_NAME leave_name
          ,’Long Leave’ leave_name
               ,to_char(doc.DATE_FROM,’DD-MON-YYYY’) Long_leave_dt_from
             –  ,to_char(doc.DATE_TO,’DD-MON-YYYY’) Long_leave_dt_to
               ,COMMENTS Long_leave_comments
          ,to_char(doc.creation_date,’DD-MON-YYYY HH24:MI:SS’) Long_leave_creation_dt
          ,doc.CREATED_BY Long_leave_created_by
          FROM hr_documents_of_record doc
        WHERE 1=1
        –AND UPPER(doc.document_name) = ’LONG LEAVE’ 
        AND  TO_CHAR (doc.creation_date, ’YYYY-MM-DD’) >= ’2022-07-20’ – Added CUT OFF DATE logic as per Padhu on 25July22
        AND document_type_id in (SELECT document_type_id
                                 FROM hr_document_types_tl
                                 WHERE 1=1
                                  AND DOCUMENT_TYPE = ’Long Leave Application’)
        –AND TRUNC(doc.creation_date)=TRUNC(SYSDATE)  */
        SELECTNULL
               Empl_Leave_Trxn_ID,
               (SELECTperson_number
                FROM   per_all_people_f
                WHERE
person_id=
Extractvalue(txnh.xml_data_cache,‘/TRANSACTION/BUSINESSDATA/AM/TXN/EO/DocumentsOfRecordEORow/PersonId/DATA’))person_id,
NULL
       leave_type_id,
NULL
       absence_type,
NULL
       from_date,
NULL
       to_date,
NULL
       days,
NULL
       reason,
NULL
       permission_minutes,
NULL
       transaction_date,
NULL
       comments_and_description,
NULL
       leave_Status,
NULL
       Applied_By,
NULL
       Approver_Comments,
NULL
       Approved_By,
NULL
       Approved_Date,
NULL
       START_DATETIME,
NULL
       END_DATETIME,
NULL
       absence_status_cd
–,DOCUMENT_NAME leave_name
,
‘Long Leave’
       leave_name
–,to_char(doc.DATE_FROM,’DD-MON-YYYY’) Long_leave_dt_from
–  ,to_char(doc.DATE_TO,’DD-MON-YYYY’) Long_leave_dt_to
,
Extractvalue(txnh.xml_data_cache,‘/TRANSACTION/BUSINESSDATA/AM/TXN/EO/DocumentsOfRecordEORow/DateFrom/DATA’)  Long_leave_dt_from
,
Extractvalue(txnh.xml_data_cache,‘/TRANSACTION/BUSINESSDATA/AM/TXN/EO/DocumentsOfRecordEORow/Comments/DATA’)  Long_leave_comments,
To_char(txnh.creation_date,‘DD-MON-YYYY HH24:MI:SS’)
       Long_leave_creation_dt,
txnh.created_by
       Long_leave_created_by
FROM   fusion.hrc_txn_console_entrytxnc,
       fusion.hrc_txn_headertxnh,
       fusion.hrc_txn_datatxnd
WHERE  1 = 1
       ANDtxnh.transaction_id=txnd.transaction_id
       ANDtxnd.transaction_id=txnc.transaction_id
       ANDtxnc.object_nameLIKE‘IN%LONG%LEAVE%’
       ANDtxnd.statusIN(‘PENDING’,‘APPROVED’)
       AND(txnh.creation_date)>=
           To_date(:p_last_run_dt,‘YYYY-MM-DD”T”HH24:MI:SS’))
ORDER  BYemployee_id 

Recent Posts

Start typing and press Enter to search