Oracle Transactional Business Intelligence

Home› Oracle Analytics› Oracle Analytics Forums› More Solutions› Oracle Transactional Business Intelligence

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

Help with my SQL Code

Received Response
103
Views
1
Comments
Ruzwan Akram
Ruzwan Akram Rank 2 - Community Beginner
in Oracle Transactional Business Intelligence

Summary

Help with my SQL Code

Content

I am trying to create a BIP Report to show Document Payslips pdf uploaded for each employee (with HDL) which covers the Tables like  HR_DOCUMENTS_OF_RECORD, FND_DOCUMENTS_TL, ,FND_ATTACHED_DOCUMENTS and per_all_people_f with related to person_id.

Problem is my BIP report with below query keeps erroring. The SQL is valid but the SQL query pulls back too many records. How can I get this to work ?

select   "PER_ALL_PEOPLE_F"."PERSON_ID" as "PERSON_ID",

            "PER_ALL_PEOPLE_F"."PERSON_NUMBER" as "PERSON_NUMBER",

            "PER_PERSON_NAMES_F"."PERSON_ID" as "PERSON_ID1",

            "PER_PERSON_NAMES_F"."LAST_NAME" as "LAST_NAME",

            "PER_PERSON_NAMES_F"."FIRST_NAME" as "FIRST_NAME",

            "HR_DOCUMENTS_OF_RECORD"."DOCUMENTS_OF_RECORD_ID" as "DOCUMENTS_OF_RECORD_ID",

            "HR_DOCUMENTS_OF_RECORD"."DOCUMENT_CODE" as "DOCUMENT_CODE",

            "HR_DOCUMENTS_OF_RECORD"."PERSON_ID" as "PERSON_ID_1",

            "HR_DOCUMENTS_OF_RECORD"."DOCUMENT_NAME" as "DOCUMENT_NAME",

            "FND_DOCUMENTS_TL_3"."DOCUMENT_ID" as "DOCUMENT_ID",

            "FND_DOCUMENTS_TL_3"."DESCRIPTION" as "DESCRIPTION",

            "FND_DOCUMENTS_TL_3"."TITLE" as "TITLE",

            "FND_DOCUMENTS_TL_3"."URL" as "URL",

            "FND_DOCUMENTS_TL_3"."DM_NODE" as "DM_NODE",

            "FND_DOCUMENTS_TL_3"."FILE_NAME" as "FILE_NAME",

            "FND_DOCUMENTS_TL_3"."DM_TYPE" as "DM_TYPE",

            "FND_DOCUMENTS_TL_3"."STATUS" as "STATUS",

            "FND_ATTACHED_DOCUMENTS"."DOCUMENT_ID" as "DOCUMENT_ID_1",

            "FND_ATTACHED_DOCUMENTS"."ENTITY_NAME" as "ENTITY_NAME",

            "FND_ATTACHED_DOCUMENTS"."PK1_VALUE" as "PK1_VALUE",

            "FND_ATTACHED_DOCUMENTS"."CATEGORY_NAME" as "CATEGORY_NAME"

 from   "FUSION"."FND_ATTACHED_DOCUMENTS" "FND_ATTACHED_DOCUMENTS",

            "FUSION"."FND_DOCUMENTS_TL" "FND_DOCUMENTS_TL_3",

            "FUSION"."FND_DOCUMENTS_TL" "FND_DOCUMENTS_TL_2",

            "FUSION"."FND_DOCUMENTS_VL" "FND_DOCUMENTS_VL_1",

            "FUSION"."FND_DOCUMENTS_VL" "FND_DOCUMENTS_VL",

            "FUSION"."HR_DOCUMENTS_OF_RECORD" "HR_DOCUMENTS_OF_RECORD",

            "FUSION"."PER_PERSON_NAMES_F" "PER_PERSON_NAMES_F",

            "FUSION"."PER_ALL_PEOPLE_F" "PER_ALL_PEOPLE_F"

 where              "PER_PERSON_NAMES_F"."PERSON_ID" =per_all_people_f.person_id

   and   "HR_DOCUMENTS_OF_RECORD"."PERSON_ID" =per_all_people_f.person_id

   and   "FND_ATTACHED_DOCUMENTS"."DOCUMENT_ID" =fnd_documents_tl_3.document_id

   and   "FND_ATTACHED_DOCUMENTS"."ENTITY_NAME" ='HR_DOCUMENTS_OF_RECORD'

   and   "FND_ATTACHED_DOCUMENTS"."PK1_VALUE" =hr_documents_of_record.DOCUMENTS_OF_RECORD_ID

Tagged:

Answers

  • Ruzwan Akram
    Ruzwan Akram Rank 2 - Community Beginner

    Issue resolved - I had the table  "FUSION"."FND_DOCUMENTS_VL" "FND_DOCUMENTS_VL" too many times in the From section of the SQL :)