How to create a AOR BIP Report with HDL Data Conversion format in Oracle.

How to create a AOR BIP Report  with HDL Data Conversion format in Oracle.

Hi folks, In this article how to create a simple AOR(Area Of Responsibilities) BIP report with HDL data conversion format in the Oracle Fusion SaaS.

Prerequisites for develop BI Reports:

Below are pre-requisites to complete this article:

1.     Basic knowledge of Programming skills and SQL. 

3.     Oracle Fusion Cloud SaaS Instance with necessary roles to create reports.

3.     If need to Learn Basic Creation of BI Report Please click on the following link BI Reports Creation

So based on this BI Report we can able see the BI Report with HDL format output. If need to update the AOR data based this out put data will do it.

Below is the SQL Query of AOR.

SELECT DISTINCT

       'MERGE' "METADATA",

        'AreasOfResponsibility'  "AreasOfResponsibility",

        PAPF.PERSON_NUMBER "PersonNumber",

        PAR.PERSON_ID "PersonId",

        PAAF.ASSIGNMENT_NUMBER "AssignmentNumber",

        PAR.RESPONSIBILITY_NAME "ResponsibilityName",

        (SELECT MEANING

          FROM HR_LOOKUPS

         WHERE TRIM (LOOKUP_TYPE) = 'PER_RESPONSIBILITY_TYPES'

               AND PAR.RESPONSIBILITY_TYPE = LOOKUP_CODE) "ResponsibilityType",

        TO_CHAR (par.start_date, 'yyyy/mm/dd') "StartDate",

       TO_CHAR (par.end_date, 'yyyy/mm/dd') "EndDate",

        STATUS "Status",

        PAR.ORGANIZATION_TREE_CODE "OrganizationTreeCode",

        TOP_ORG.NAME         "TopHierarchyOrganizationName",

        TOP_ORG.ORGANIZATION_ID "TopOrganizationId",

        HOCV.CLASSIFICATION_CODE "TopOrganizationClassificationCode",

        ftvt.TREE_VERSION_NAME "OrganizationHierarchyVersionName",

        PAR.INCLUDE_TOP_HIER_NODE "IncludeTopHierNode",

        PAR.WORK_CONTACTS_FLAG "WorkContactsFlag",

        INTG.SOURCE_SYSTEM_ID AS "SourceSystemId",

       INTG.SOURCE_SYSTEM_OWNER AS "SourceSystemOwner"

FROM   PER_ALL_PEOPLE_F PAPF,

       PER_ALL_ASSIGNMENTS_F PAAF,

        PER_ASG_RESPONSIBILITIES PAR,

        HR_ORGANIZATION_UNITS TOP_ORG,

        HR_ORG_UNIT_CLASSIFICATIONS_F HOCV,

        HRC_INTEGRATION_KEY_MAP INTG,

        FND_TREE_VERSION_TL ftvt

WHERE  PAPF.PERSON_ID = PAAF.PERSON_ID

   AND PAR.PERSON_ID = PAAF.PERSON_ID

   AND PAR.TOP_ORGANIZATION_ID(+) = TOP_ORG.ORGANIZATION_ID

   AND INTG.SURROGATE_ID(+)=PAR.ASG_RESPONSIBILITY_ID

   AND PAAF.PRIMARY_FLAG = 'Y'

   AND UPPER (TRIM (PAAF.ASSIGNMENT_TYPE)) = 'E'

   AND ASSIGNMENT_STATUS_TYPE = 'ACTIVE'

   AND HOCV.ORGANIZATION_ID=TOP_ORG.ORGANIZATION_ID

    AND TRUNC (SYSDATE) BETWEEN TRUNC (papf.effective_start_date) AND TRUNC (papf.effective_end_date)

   AND TRUNC (SYSDATE) BETWEEN TRUNC (paaf.effective_start_date) AND TRUNC (paaf.effective_end_date)

   and PAR.ORGANIZATION_HIERARCHY_ID(+) = ftvt.TREE_VERSION_ID

ORDER BY PAPF.PERSON_NUMBER

 

It may helpful for you reach out to sathwikit@gmail.com for any queries. please visit our blog for more information and share your ideas and comments.

Also, please comments and follow to our Blog . Thanks.