Leave Initial Balance

select PER.PERSON_NUMBER,
            PPN.FULL_NAME,
            TO_CHAR(ACC.PROCD_DATE,'DD-MON-YYYY','NLS_DATE_LANGUAGE = AMERICAN') PROCD_DATE,
            (select  NAME
   from ANC_ABSENCE_PLANS_VL   
   WHERE ABSENCE_PLAN_ID = ACC.PL_ID) PLAN_NAME,
            ACC.VALUE BALANCE
   from ANC_PER_ACRL_ENTRY_DTLS ACC,
            PER_ALL_PEOPLE_F PER,
            PER_PERSON_NAMES_F PPN
WHERE ACC.PERSON_ID = PER.PERSON_ID
  AND PER.PERSON_ID = PPN.PERSON_ID
  AND TRUNC(SYSDATE) BETWEEN PER.EFFECTIVE_START_DATE AND PER.EFFECTIVE_END_DATE
  AND TRUNC(SYSDATE) BETWEEN PPN.EFFECTIVE_START_DATE AND PPN.EFFECTIVE_END_DATE
  AND PPN.NAME_TYPE = 'GLOBAL'
AND ACC.TYPE = 'INIT'
ORDER BY PER.PERSON_NUMBER

Organization Department Query

SELECT haou.name
      ,fnd.set_name
      ,haou.attribute1 classification
      ,haou.attribute2 business_function
      ,haou.attribute3 operating_unit
      ,TO_CHAR (haou.effective_start_date
               ,'dd-mm-yyyy')
          Effective_Start_Date
      ,hl.iNTERNAL_LOCATION_CODE LOCATION_CODE
FROM   hr_org_unit_classifications_f houc
      ,hr_all_organization_units haou
      ,fnd_setid_sets fnd
      ,hr_locations hl
WHERE  1 = 1
AND    hl.location_id(+) = haou.location_id
AND    haou.organization_id = houc.organization_id
AND    houc.classification_code = 'DEPARTMENT'
AND    fnd.set_id = houc.set_id

Location Query

/* Formatted on 9/25/2017 4:12:35 PM (QP5 v5.163.1008.3004) */
SELECT LOCATION_NAME,
       iNTERNAL_LOCATION_CODE,
       TO_CHAR (EFFECTIVE_START_DATE,
                'DD-Mon-YYYY',
                'NLS_DATE_LANGUAGE=AMERICAN')
          "Effective Start Date",
       POSTAL_CODE,
       COUNTRY, ADDRESS_LINE_1,
       ADDRESS_LINE_2
  FROM hr_locations
 --WHERE iNTERNAL_LOCATION_CODE in ('225','203')

Job Query

SELECT pjf.job_family_name
      ,pj.job_family_code CODE
      ,pj.active_status
      ,TO_CHAR (pj.effective_start_date
               ,'DD-Mon-YYYY'
               ,'NLS_DATE_LANGUAGE=AMERICAN')
          effective_start_date
FROM   per_job_family_f_tl pjf
      ,per_job_family_f pj
WHERE  pjf.job_family_id = pj.job_family_id
       AND TRUNC (SYSDATE) BETWEEN pj.effective_start_date
                               AND pj.effective_end_date
       AND TRUNC (SYSDATE) BETWEEN pjf.effective_start_date
                               AND pjf.effective_end_date

Job Query

SELECT   effective_start_date
        ,effective_end_date
        ,job_code
        ,job_name
        ,job_family_name
        ,arabic_name
        ,deployment_indicator
        ,set_name
        ,MIN (valid_grade_code1) valid_grade_code1
        ,MIN (valid_grade_code2) valid_grade_code2
        ,MIN (valid_grade_code3) valid_grade_code3
FROM     (SELECT TO_CHAR (pj.effective_start_date
                         ,'DD-Mon-YYYY'
                         ,'NLS_DATE_LANGUAGE=AMERICAN')
                    effective_start_date
                ,TO_CHAR (pj.effective_end_date
                         ,'DD-Mon-YYYY'
                         ,'NLS_DATE_LANGUAGE=AMERICAN')
                    effective_end_date
                ,pj.job_code job_code
                ,pjj.name job_name
                ,RANK ()
                    OVER (PARTITION BY pj.job_id
                          ORDER BY
                             LPAD (pg.grade_code
                                  ,10
                                  ,'0'))
                    seq_no
                ,pg.grade_code grade_code
                ,CASE
                    WHEN RANK ()
                            OVER (PARTITION BY pj.job_id
                                  ORDER BY
                                     LPAD (pg.grade_code
                                          ,10
                                          ,'0')) = 1
                    THEN
                       pg.grade_code
                    ELSE
                       ''
                 END
                    valid_grade_code1
                ,CASE
                    WHEN RANK ()
                            OVER (PARTITION BY pj.job_id
                                  ORDER BY
                                     LPAD (pg.grade_code
                                          ,10
                                          ,'0')) = 2
                    THEN
                       pg.grade_code
                    ELSE
                       ''
                 END
                    valid_grade_code2
                ,CASE
                    WHEN RANK ()
                            OVER (PARTITION BY pj.job_id
                                  ORDER BY
                                     LPAD (pg.grade_code
                                          ,10
                                          ,'0')) = 3
                    THEN
                       pg.grade_code
                    ELSE
                       ''
                 END
                    valid_grade_code3
                ,pjf.job_family_name job_family_name
                ,pj.attribute2 arabic_name
                ,pj.attribute1 deployment_indicator
                , (SELECT set_name
                   FROM   fnd_setid_sets_vl
                   WHERE  set_id = pj.set_id)
                    set_name
          FROM   per_job_family_f_tl pjf
                ,per_grades pg
                ,per_valid_grades_f pvg
                ,per_jobs_f pj
                ,per_jobs pjj
          WHERE  pjf.job_family_id = pj.job_family_id
                 AND pjf.language = 'US'
                 AND TRUNC (SYSDATE) BETWEEN pjf.effective_start_date
                                         AND pjf.effective_end_date
                 AND pg.grade_id = pvg.grade_id
                 AND TRUNC (SYSDATE) BETWEEN pvg.effective_start_date
                                         AND pvg.effective_end_date
                 AND pvg.job_id = pj.job_id
                 AND TRUNC (SYSDATE) BETWEEN pj.effective_start_date
                                         AND pj.effective_end_date
                 AND pj.job_id = pjj.job_id)
GROUP BY effective_start_date
        ,effective_end_date
        ,job_code
        ,job_name
        ,job_family_name
        ,arabic_name
        ,deployment_indicator
        ,set_name
ORDER BY job_name

Fusion HDL Error Analysis

Fusion Error Analysis Summary:

SELECT '1' KEY, ucm_content_id, data_set_name, imported_status,
       transfer_status,
       TO_CHAR (ds.last_update_date,
                'dd-mon-rrrr hh24:mi:ss'
               ) batch_submission_date,
       import_lines_total_count, import_error_count, loaded_count,
       error_count
  FROM hrc_dl_data_sets ds
 WHERE ucm_content_id =  'UCMFA00009281'
/* (SELECT MAX (ucm_content_id)
                           FROM hrc_dl_data_sets hdd
                          WHERE TRUNC (SYSDATE) = TRUNC (hdd.creation_date)
        AND hdd.LOADED_STATUS = 'ERROR')*/

Fusion Error Analysis Details :
SELECT  DISTINCT ds.ucm_content_id ucm_content_id1
                ,fr.key_source_owner data_source1
                ,hdbo.bus_obj_file_discriminator bo
                ,ds.data_set_name file_name
                ,TO_CHAR (ds.last_update_date
                         ,'dd-mon-rrrr hh24:mi:ss')
                    batch_submission_date1
                ,l.seq_num
                ,fr.key_source_id source_id
                ,INITCAP (ll.imported_status) import_status
                ,INITCAP (REPLACE (ll.validated_loaded_status
                                  ,'_'
                                  ,' '))
                    loaded_status
                , (SELECT  fhl.text
                   FROM    hrc_dl_file_lines fhl
                   WHERE   fhl.line_id = fh.line_id)
                    metadata
                ,ml.msg_text error_msg
                ,l.text error_data
FROM    hrc_dl_logical_lines ll
       ,hrc_dl_file_rows fr
       ,hrc_dl_message_lines ml
       ,hrc_dl_data_set_bus_objs dsbo
       ,hrc_dl_data_sets ds
       ,hrc_dl_file_lines l
       ,hrc_dl_business_objects hdbo
       ,hrc_dl_file_headers fh
WHERE       ucm_content_id = 'UCMFA00009281'
        /* (SELECT MAX (ucm_content_id)
                                   FROM hrc_dl_data_sets hdd
                                  WHERE TRUNC (SYSDATE) = TRUNC (hdd.creation_date)
                                  AND hdd.LOADED_STATUS = 'ERROR')*/
        AND ds.data_set_id = dsbo.data_set_id
        AND ml.data_set_bus_obj_id = dsbo.data_set_bus_obj_id
        AND ll.data_set_bus_obj_id = dsbo.data_set_bus_obj_id
        AND ml.message_source_table_name = 'HRC_DL_LOGICAL_LINES'
        AND ml.message_source_line_id = ll.logical_line_id
        AND ll.logical_line_id = fr.logical_line_id
        AND fr.line_id = l.line_id
        AND fr.header_id = fh.header_id
        AND hdbo.business_object_id = dsbo.business_object_id
-- AND ds.transfer_status = 'ERROR'
UNION
SELECT  DISTINCT ds.ucm_content_id
                ,fr.key_source_owner data_source
                ,hdbo.bus_obj_file_discriminator bo
                ,ds.data_set_name file_name
                ,TO_CHAR (ds.last_update_date
                         ,'dd-mon-rrrr hh24:mi:ss')
                    batch_submission_date
                ,l.seq_num
                ,fr.key_source_id source_id
                ,INITCAP (ll.imported_status) import_status
                ,INITCAP (REPLACE (ll.validated_loaded_status
                                  ,'_'
                                  ,' '))
                    loaded_status
                , (SELECT  fhl.text
                   FROM    hrc_dl_file_lines fhl
                   WHERE   fhl.line_id = fh.line_id)
                    metadata
                ,ml.msg_text error_msg
                ,l.text error_data
FROM    hrc_dl_physical_lines ll
       ,hrc_dl_file_rows fr
       ,hrc_dl_message_lines ml
       ,hrc_dl_data_set_bus_objs dsbo
       ,hrc_dl_data_sets ds
       ,hrc_dl_file_lines l
       ,hrc_dl_file_headers fh
       ,hrc_dl_business_objects hdbo
WHERE       ucm_content_id =  'UCMFA00009281'
        /* (SELECT MAX (ucm_content_id)
                             FROM hrc_dl_data_sets hdd
                            WHERE TRUNC (SYSDATE) = TRUNC (hdd.creation_date)
                            AND hdd.LOADED_STATUS = 'ERROR')*/
        AND ds.data_set_id = dsbo.data_set_id
        AND ml.data_set_bus_obj_id = dsbo.data_set_bus_obj_id
        AND ll.data_set_bus_obj_id = dsbo.data_set_bus_obj_id
        AND ml.message_source_table_name = 'HRC_DL_PHYSICAL_LINES'
        AND ml.message_source_line_id = ll.physical_line_id
        AND ll.logical_line_id = fr.logical_line_id
        AND fr.line_id = l.line_id
        AND fr.header_id = fh.header_id
        AND hdbo.business_object_id = dsbo.business_object_id

 

Documents of Records DOR Query

SELECT papf.person_number
      , (SELECT documents_of_record_id
         FROM   hr_documents_of_record hrd
         WHERE  hrd.person_id = papf.person_id
                AND ROWNUM = 1)
          documents_of_record_id
           , (SELECT DEI_ATTRIBUTE4
         FROM   hr_documents_of_record hrd
         WHERE  hrd.person_id = papf.person_id
                AND ROWNUM = 1)
          self_dependant
      , (SELECT hdr.dei_attribute1 uid_number
         FROM   hr_documents_of_record hdr
               ,hr_document_types_tl hdt
         WHERE  ROWNUM = 1
                AND hdr.last_update_date =
                       (SELECT MAX (hdr1.last_update_date)
                        FROM   hr_documents_of_record hdr1
                        WHERE  hdr1.person_id = hdr.person_id
                               AND hdr1.document_type_id =
                                      hdr.document_type_id)
                AND hdr.document_type_id = hdt.document_type_id
                AND hdr.person_id = papf.person_id
                AND hdt.language = 'US'
                AND hdt.document_type = 'Update National Identifier'
                AND hdr.dei_attribute_category = 'Update National Identifier')
          uid_number
      , (SELECT hdr.dei_attribute2
         FROM   hr_documents_of_record hdr
               ,hr_document_types_tl hdt
         WHERE      ROWNUM = 1
                AND hdt.document_type_id = hdr.document_type_id
                AND hdt.language = 'US'
                AND hdr.dei_attribute_category = 'Update National Identifier'
                AND hdt.document_type = 'Update National Identifier'
                AND hdr.person_id = papf.person_id
                AND hdr.last_update_date =
                       (SELECT MAX (hdr1.last_update_date)
                        FROM   hr_documents_of_record hdr1
                        WHERE  hdr1.person_id = hdr.person_id
                               AND hdr1.document_type_id =
                                      hdr.document_type_id))
          eid_number
      , (SELECT TO_CHAR (hdr.dei_attribute_date1
                        ,'DD-Mon-YYYY'
                        ,'NLS_DATE_LANGUAGE=AMERICAN')
                   eid_expiry
         FROM   hr_documents_of_record hdr
               ,hr_document_types_tl hdt
         WHERE      ROWNUM = 1
                AND hdt.document_type_id = hdr.document_type_id
                AND hdt.language = 'US'
                AND hdr.dei_attribute_category = 'Update National Identifier'
                AND hdt.document_type = 'Update National Identifier'
                AND hdr.person_id = papf.person_id
                AND hdr.last_update_date =
                       (SELECT MAX (hdr1.last_update_date)
                        FROM   hr_documents_of_record hdr1
                        WHERE  hdr1.person_id = hdr.person_id
                               AND hdr1.document_type_id =
                                      hdr.document_type_id))
          eid_expiry
      , (SELECT hdr.dei_attribute3 mol_num
         FROM   hr_documents_of_record hdr
               ,hr_document_types_tl hdt
         WHERE      ROWNUM = 1
                AND hdt.document_type_id = hdr.document_type_id
                AND hdt.language = 'US'
                AND hdr.dei_attribute_category = 'Update National Identifier'
                AND hdt.document_type = 'Update National Identifier'
                AND hdr.person_id = papf.person_id
                AND hdr.last_update_date =
                       (SELECT MAX (hdr1.last_update_date)
                        FROM   hr_documents_of_record hdr1
                        WHERE  hdr1.person_id = hdr.person_id
                               AND hdr1.document_type_id =
                                      hdr.document_type_id))
          mol_num
      , (SELECT TO_CHAR (hdr.dei_attribute_date2
                        ,'DD-Mon-YYYY'
                        ,'NLS_DATE_LANGUAGE=AMERICAN')
         FROM   hr_documents_of_record hdr
               ,hr_document_types_tl hdt
         WHERE      ROWNUM = 1
                AND hdt.document_type_id = hdr.document_type_id
                AND hdt.language = 'US'
                AND hdr.dei_attribute_category = 'Update National Identifier'
                AND hdt.document_type = 'Update National Identifier'
                AND hdr.person_id = papf.person_id
                AND hdr.last_update_date =
                       (SELECT MAX (hdr1.last_update_date)
                        FROM   hr_documents_of_record hdr1
                        WHERE  hdr1.person_id = hdr.person_id
                               AND hdr1.document_type_id =
                                      hdr.document_type_id))
          mol_expiry
      , (SELECT hdr.dei_attribute3 visa_num
         FROM   hr_documents_of_record hdr
               ,hr_document_types_tl hdt
         WHERE      ROWNUM = 1
                AND hdt.document_type_id = hdr.document_type_id
                AND hdt.language = 'US'
                AND hdr.dei_attribute_category = 'Update Visa Details'
                AND hdt.document_type = 'Update Visa Details'
                AND hdr.person_id = papf.person_id
                AND hdr.last_update_date =
                       (SELECT MAX (hdr1.last_update_date)
                        FROM   hr_documents_of_record hdr1
                        WHERE  hdr1.person_id = hdr.person_id
                               AND hdr1.document_type_id =
                                      hdr.document_type_id))
          visa_num
      , (SELECT TO_CHAR (hdr.dei_attribute_date1
                        ,'DD-Mon-YYYY'
                        ,'NLS_DATE_LANGUAGE=AMERICAN')
                   visa_num
         FROM   hr_documents_of_record hdr
               ,hr_document_types_tl hdt
         WHERE      ROWNUM = 1
                AND hdt.document_type_id = hdr.document_type_id
                AND hdt.language = 'US'
                AND hdr.dei_attribute_category = 'Update Visa Details'
                AND hdt.document_type = 'Update Visa Details'
                AND hdr.person_id = papf.person_id
                AND hdr.last_update_date =
                       (SELECT MAX (hdr1.last_update_date)
                        FROM   hr_documents_of_record hdr1
                        WHERE  hdr1.person_id = hdr.person_id
                               AND hdr1.document_type_id =
                                      hdr.document_type_id))
          visa_issue_date
      , (SELECT TO_CHAR (hdr.dei_attribute_date2
                        ,'DD-Mon-YYYY'
                        ,'NLS_DATE_LANGUAGE=AMERICAN')
         FROM   hr_documents_of_record hdr
               ,hr_document_types_tl hdt
         WHERE      ROWNUM = 1
                AND hdt.document_type_id = hdr.document_type_id
                AND hdt.language = 'US'
                AND hdr.dei_attribute_category = 'Update Visa Details'
                AND hdt.document_type = 'Update Visa Details'
                AND hdr.person_id = papf.person_id
                AND hdr.last_update_date =
                       (SELECT MAX (hdr1.last_update_date)
                        FROM   hr_documents_of_record hdr1
                        WHERE  hdr1.person_id = hdr.person_id
                               AND hdr1.document_type_id =
                                      hdr.document_type_id))
          visa_expiry_date
      , (SELECT hdr.dei_attribute6 sponsor_name
         FROM   hr_documents_of_record hdr
               ,hr_document_types_tl hdt
         WHERE      ROWNUM = 1
                AND hdt.document_type_id = hdr.document_type_id
                AND hdt.language = 'US'
                AND hdr.dei_attribute_category = 'Update Visa Details'
                AND hdt.document_type = 'Update Visa Details'
                AND hdr.person_id = papf.person_id
                AND hdr.last_update_date =
                       (SELECT MAX (hdr1.last_update_date)
                        FROM   hr_documents_of_record hdr1
                        WHERE  hdr1.person_id = hdr.person_id
                               AND hdr1.document_type_id =
                                      hdr.document_type_id))
          sponsor_name
      , (SELECT hdr.dei_attribute7
         FROM   hr_documents_of_record hdr
               ,hr_document_types_tl hdt
         WHERE      ROWNUM = 1
                AND hdt.document_type_id = hdr.document_type_id
                AND hdt.language = 'US'
                AND hdr.dei_attribute_category =
                       'Passport Information Update'
                AND hdt.document_type = 'Passport Information Update'
                AND hdr.person_id = papf.person_id
                AND hdr.last_update_date =
                       (SELECT MAX (hdr1.last_update_date)
                        FROM   hr_documents_of_record hdr1
                        WHERE  hdr1.person_id = hdr.person_id
                               AND hdr1.document_type_id =
                                      hdr.document_type_id))
          passprot_number
      , (SELECT TO_CHAR (hdr.dei_attribute_date2
                        ,'DD-Mon-YYYY'
                        ,'NLS_DATE_LANGUAGE=AMERICAN')
         FROM   hr_documents_of_record hdr
               ,hr_document_types_tl hdt
         WHERE      ROWNUM = 1
                AND hdt.document_type_id = hdr.document_type_id
                AND hdt.language = 'US'
                AND hdr.dei_attribute_category =
                       'Passport Information Update'
                AND hdt.document_type = 'Passport Information Update'
                AND hdr.person_id = papf.person_id
                AND hdr.last_update_date =
                       (SELECT MAX (hdr1.last_update_date)
                        FROM   hr_documents_of_record hdr1
                        WHERE  hdr1.person_id = hdr.person_id
                               AND hdr1.document_type_id =
                                      hdr.document_type_id))
          passport_issue_date
      , (SELECT TO_CHAR (hdr.dei_attribute_date3
                        ,'DD-Mon-YYYY'
                        ,'NLS_DATE_LANGUAGE=AMERICAN')
         FROM   hr_documents_of_record hdr
               ,hr_document_types_tl hdt
         WHERE      ROWNUM = 1
                AND hdt.document_type_id = hdr.document_type_id
                AND hdt.language = 'US'
                AND hdr.dei_attribute_category =
                       'Passport Information Update'
                AND hdt.document_type = 'Passport Information Update'
                AND hdr.person_id = papf.person_id
                AND hdr.last_update_date =
                       (SELECT MAX (hdr1.last_update_date)
                        FROM   hr_documents_of_record hdr1
                        WHERE  hdr1.person_id = hdr.person_id
                               AND hdr1.document_type_id =
                                      hdr.document_type_id))
          passport_expiry_date
      , (SELECT hdr.dei_attribute6
         FROM   hr_documents_of_record hdr
               ,hr_document_types_tl hdt
         WHERE      ROWNUM = 1
                AND hdt.document_type_id = hdr.document_type_id
                AND hdt.language = 'US'
                AND hdr.dei_attribute_category =
                       'Passport Information Update'
                AND hdt.document_type = 'Passport Information Update'
                AND hdr.person_id = papf.person_id
                AND hdr.last_update_date =
                       (SELECT MAX (hdr1.last_update_date)
                        FROM   hr_documents_of_record hdr1
                        WHERE  hdr1.person_id = hdr.person_id
                               AND hdr1.document_type_id =
                                      hdr.document_type_id))
          passprot_country
FROM   per_all_people_f papf
WHERE  TRUNC (SYSDATE) BETWEEN papf.effective_start_date
                           AND papf.effective_end_date