Posts

Posts

  • Ruzwan Akram
    Help with my SQL CodeAnswered15.0
    Topic posted March 13, 2017 by Ruzwan AkramGold Medal: 3,500+ Points, tagged BI Publisher, BI Publisher Data Models, Core HR, SQL in General OBIEE public
    Title:
    Help with my SQL Code
    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

  • Kiran Shenvi
    Error : Exceeded Configured Maximum number of allowed input...Answered35.0
    Topic posted November 14, 2015 by Kiran ShenviGold Trophy: 10,000+ Points, tagged Analyses, OBI Answers, OTBI, Report Output, Reports, Setup/Administration, SQL, Taleo Enterprise in General OBIEE public
    Title:
    Error : Exceeded Configured Maximum number of allowed input records
    Summary:
    Pivot Table View and Narrative View giving error at runtime
    Content:

    Hi All,

    We are facing error as "Exceeded Configured Maximum number of allowed input records" during runtime for particular prompts.

    The rowcount of the records is around 50K and around 15-20 columns.

    When we show the output in Table View it renders correctly but when the same is shown using Pivot Table View and Narrative View are showing up this error.

    We had raised SR for this issue but the support was not able to provide us with configured limits of each view or any resolution for the same. 

    Can someone help us to resolve this, if you have faced this error before?

    Regards,

    Kiran Shenvi

  • Gal Shani
    Show report results in dashboard with promptAnswered15.0
    Topic posted October 18, 2015 by Gal ShaniSilver Medal: 2,000+ Points, tagged Analyses, Customizing Reports, OTBI, Report Output, Reports, Sample Reports, Setup, Setup/Administration, SQL in General OBIEE public
    Title:
    Show report results in dashboard with prompt
    Content:

    Hello,

    I know there is an option to show the report results immediately within dashboard without clicking on the apply button of the prompt (after defining default value).
    if someone can share his knowledge regarding this issue on how to do it it will be great!

    Thank you,

    Gal.

     

  • Mohammad Alhindawi
    Customization on the AP invoice14.0
    Topic posted March 11, 2019 by Mohammad AlhindawiRed Ribbon: 250+ Points, tagged Asset Tracking, BI Publisher, BI Publisher Reports, BICS, Business Intelligence, Configure to Order-CTO, Costing, Dashboards, DVCS, Geneology, Innovation Management, Installed Base, Inventory, Maintenance, Manufacturing, OBIEE Answers, Order Management, OTBI, Procurement, Product Development, Product Information Management-PIM, Public Sector, Quality – Inspections, Quality – Issues and Actions, Report Scheduling, Reporting and Analytics, Sample Reports, SCM, Setup/Administration, SQL in Reporting and Analytics for SCM public
    Title:
    Customization on the AP invoice
    Summary:
    Customization on the AP invoice
    Content:

    Is it possible to do a customization on the AP invoice to validate certain fields and making some fields required based on filling of other fields?

    Version:
    Oracle Fusion
  • Giuseppe Botta
    Calculate Yesterday date based on CURRENT_DATE variable24.0
    Topic posted July 5, 2017 by Giuseppe BottaSilver Medal: 2,000+ Points, tagged Analyses, BI Publisher, BI Publisher Data Models, Compensation, OTBI, Report Output, Reports, SQL in General OBIEE public
    Title:
    Calculate Yesterday date based on CURRENT_DATE variable
    Summary:
    Calculate Yesterday date based on CURRENT_DATE variable
    Content:

    Hello,

    how do I could calcolate obtain yesterday date based on current_date field in an OTBI analysis, to use it in a where clause for example?

    ex "...WHERE "Compensation Person Worksheet Details"."Worksheet Last Update Date" < CURRENT_DATE-1" (not working)

     

    Thank you in advance

  • Dario Di Franceschi
    Prompt Dashboard for cross subject areas33.0
    Topic posted March 6, 2018 by Dario Di FranceschiRed Ribbon: 250+ Points, tagged Analyses, BI Publisher, Fusion, OTBI, SQL in General OBIEE public
    Title:
    Prompt Dashboard for cross subject areas
    Summary:
    Prompt Dashboard for cross subject areas
    Content:

    Hello,

    I have an analysis made by two subject areas (linked by an INNER JOIN) and the system puts "saw" alias in the resulted query.

    If I wanted to insert the analysis in a dashboard, how do I create a dashboard prompt in order to filter appropriately the analysis fields?

     

    Thanks

    Dario

  • Nirmal Kumar L
    Table name to capture reverse termination date & its details...3.0
    Topic posted November 10, 2017 by Nirmal Kumar LBronze Medal: 1,250+ Points, tagged BI Publisher, BI Publisher Data Models, BI Publisher Report Templates, Core HR, Customizing Reports, Data Models, OBI Answers, OBIEE Answers, OTBI, Report Output, Reports, Sample Reports, SQL in General OBIEE public
    Title:
    Table name to capture reverse termination date & its details for reverse terminated employees
    Summary:
    Table name to capture reverse termination date & its details for reverse terminated employees
    Content:

    Hi All,

    In which table I can find the reverse terminated date & its details for the reverse terminated employee? Please help me with the table name or sample query.

    Thanks
    Nirmal kumar

  • Karen Bredehoeft
    Filter for terminations in prior calendar week33.0
    Topic posted September 21, 2017 by Karen BredehoeftBronze Trophy: 5,000+ Points, tagged Analyses, Core HR, OTBI, SQL in General OBIEE public
    Title:
    Filter for terminations in prior calendar week
    Summary:
    I'm creating a termination report and trying to show which terminations were in the calendar week (Sunday-Saturday) prior from the week when the report was run
    Content:

    Hello,

    I'm trying to create a report that shows which terminations were from the prior calendar week. So far I've only been able to create the below formula to show terms for 1 week prior to current date, but it's calculating the past 7 days, not calendar week. I've tried changing the formula to use CALENDAR WEEK but to no avail. 

    CASE  WHEN (("Worker"."Termination Date" IN (timestampadd(SQL_TSI_WEEK,-1,CURRENT_DATE))) OR ("Worker"."Termination Date" IN (CURRENT_DATE))) THEN 'Last Week' ELSE '' END

    I would like this to be a value in a column. So for example, if we ran the report today, Thursday September 21st, any employee whose termination date was between Sunday, Sept 10 and Saturday Sept 16 would have the value LAST WEEK, in the column. We would then be able to use that column as a table prompt to show terminations in the last week. We do not want it as a filter on the entire report, because we are using the same analysis criteria to also show terminations by mth and quarter. 

    I've attached a screenshot of what we're trying to get to. 

    Image:
  • krishiveth
    OTBI Data Security is not working for Documents of Record...13.0
    Topic posted July 3, 2017 by krishivethGold Medal: 3,500+ Points, tagged Analyses, BI Publisher, Core HR, Fusion, OBI Answers, OBIEE Answers, OTBI, Reports, Sample Reports, SQL in General OBIEE public
    Title:
    OTBI Data Security is not working for Documents of Record Real Time Subject Area
    Summary:
    OTBI Data Security is not working for Documents of Record Real Time Subject Area
  • Peter Kirwan
    report on user account details - Role Names43.0
    Topic posted May 12, 2017 by Peter KirwanBlue Ribbon: 750+ Points, tagged BI Publisher, BI Publisher Data Models, BI Publisher Report Templates, Core HR, Data Models, Fusion, OBI Answers, OBIEE Answers, OTBI, Reports, SQL in General OBIEE public
    Title:
    report on user account details - Role Names
    Summary:
    SQL for reporting on employees roles
    Content:

    Can someone see how to stop the multiple rows from returning in below SQL?

    select  "PER_USERS"."USERNAME" as "USERNAME",
      "PER_ROLES_DN_VL"."ROLE_NAME" as "ROLE_NAME",
      "PER_ALL_PEOPLE_F"."ATTRIBUTE3" as "ATTRIBUTE3",
      "PER_PERSON_NAMES_F"."FIRST_NAME" as "FIRST_NAME",
      "PER_PERSON_NAMES_F"."LAST_NAME" as "LAST_NAME",
      "PER_ALL_PEOPLE_F"."EFFECTIVE_START_DATE" as "EFFECTIVE_START_DATE",
      "PER_ALL_PEOPLE_F"."EFFECTIVE_END_DATE" as "EFFECTIVE_END_DATE"
     from "FUSION"."PER_PERSON_NAMES_F" "PER_PERSON_NAMES_F",
     "FUSION"."PER_ALL_PEOPLE_F" "PER_ALL_PEOPLE_F",
     "FUSION"."PER_ROLES_DN_VL" "PER_ROLES_DN_VL",
     "FUSION"."PER_USER_ROLES" "PER_USER_ROLES",
     "FUSION"."PER_USERS" "PER_USERS"
     where   "PER_USERS"."USER_ID"="PER_USER_ROLES"."USER_ID"
     and  "PER_USER_ROLES"."ROLE_ID"="PER_ROLES_DN_VL"."ROLE_ID"
     and  "PER_USERS"."PERSON_ID"="PER_ALL_PEOPLE_F"."PERSON_ID"
     and  "PER_ALL_PEOPLE_F"."PERSON_ID"="PER_PERSON_NAMES_F"."PERSON_ID"
      and   "PER_ALL_PEOPLE_F"."EFFECTIVE_END_DATE" >CURRENT_DATE
    order by "PER_USERS"."USERNAME" ASC