Purchasing

Get Involved. Join the Conversation.

Topic

    Annette Massie
    Approved requisitions not generating PO'sAnswered
    Topic posted July 26, 2019 by Annette MassieRed Ribbon: 250+ Points, tagged BI Publisher Reports, Business Intelligence, Public Sector 
    47 Views, 8 Comments
    Title:
    Approved requisitions not generating PO's
    Content:

    We are finding that some of our requisitions are still in process status because it requires a negotiation to turn into a PO. Oracle support supplied a sql query to create a BI report, however I can't seem to make it work. Has anyone created an analysis to help with this that they would be willing to share? The query that was supplied looked like this:

    SELECT prh.requisition_number
    ,prl.line_number
    ,prl.line_status
    ,prl.req_po_instance_id
    ,prl.reqs_in_pool_flag
    ,ph.segment1 AS po_number
    ,prh.emergency_po_number
    ,prl.reqtopo_automation_failed
    ,prl.reqtopo_auto_failed_reason
    ,msg.context AS failed_reason_text
    ,DECODE(NVL2(prh.EMERGENCY_PO_NUMBER, 1, - 1), 1, prh.SOLDTO_LE_ID, - 1,
    NULL) AS SOLDTO_LE_ID
    ,prh.pcard_id
    ,prl.line_type_id
    ,prl.pcard_flag
    ,prl.requisition_header_id
    ,prl.requisition_line_id
    ,prl.prc_bu_id AS req_line_prc_bu
    ,prh.prc_bu_id AS req_header_prc_bu
    ,prl.req_bu_id
    ,prl.assigned_buyer_id
    ,prl.negotiated_by_preparer_flag
    ,prl.negotiation_required_flag
    ,prl.source_document_type
    ,prl.source_doc_header_id
    ,prl.vendor_id
    ,prl.vendor_site_id
    ,prl.line_location_id
    ,prl.item_id
    ,prl.category_id
    ,prl.amount
    ,prl.quantity
    ,prl.unit_price
    ,ph.document_creation_method
    ,ph.prc_bu_id AS po_prc_bu
    ,ph.document_status
    ,ph.po_header_id
    FROM po_headers_all ph
    ,po_lines_draft_all pl
    ,por_requisition_lines_all prl
    ,por_requisition_headers_all prh
    ,fnd_messages_b msg
    WHERE ph.po_header_id(+) = pl.po_header_id
    AND prl.po_line_id = pl.po_line_id(+)
    AND prl.requisition_header_id = prh.requisition_header_id
    AND prh.requisition_number IN ('RQ17135')
    AND prl.reqtopo_auto_failed_reason = msg.message_name(+)
    ORDER BY prl.requisition_header_id, prl.line_number; 

    Version:
    19B

    Best Comment

    Leaman Crews

    Hi Annette,

    No problem, glad to help. The Doc ID that Niamath posted has detailed directions, but here are the main steps:

    1.) Go to the BI Publisher administration interface by going to /xmlpserver in your cloud instance. For example, if your URL is https://something.us3.oraclecloud.com/, go https://something.us3.oraclecloud.com/xmlpserver

    2.) Once logged in, create a new Data Model

    3.) On the Diagram tab, click the + icon and choose SQL Query

    4.) Paste the SQL text into the box. Oracle BI EE is not the proper Data Source; it will be an option with ApplicationDB_ at the beginning (more than likely, ApplicationDB_FSCM)

    5.) Click OK, then go to the Data tab, click View and see if the query returns any results

    One other thing I had to do is remove the semicolon at the end of the query. Although this is the proper way to end a SQL statement, BI Publisher doesn't use it in this case.

    In the Data tab, you can view the data as an XML tree or in Table View.

    Hopefully this helps!

    Comment

     

    • Leaman Crews

      Hi Annette,

      I copied the SQL into a new data model, and the query worked fine.

      I did comment out the line near the end that specifies the requisition number:

      AND prh.requisition_number IN ('RQ17135')

      but the only effect of doing that is to return all matching rows instead of rows for that particular req number. What error are you running into?

      Regards,

      Leaman

    • Annette Massie

      Help me understand the process of pasting this to a new data model. I have only ever worked with new analysis and building them from subject areas. I have never worked in data models. is there a document that shows me where to paste this at? I  click +Diagram, SQL Query and pasted in SQL Query box. Should the Data Source field be Oracle BIEE or our application instance? What option should I use for Type of SQL? Again, my apologies, but I have never done this before and was hoping for a bit more direction. Thanks for helping!

      • Niamath Jeddy

        Annette,

        Please refer below note. The snaps may be old but the process is correct.

        Fusion Applications BI Publisher : How Can I Use BI Publisher to Run SQL Against the Fusion Applications Database ? (Doc ID 1910762.1)

        Regards,

        Niamath

      • Leaman Crews

        Hi Annette,

        No problem, glad to help. The Doc ID that Niamath posted has detailed directions, but here are the main steps:

        1.) Go to the BI Publisher administration interface by going to /xmlpserver in your cloud instance. For example, if your URL is https://something.us3.oraclecloud.com/, go https://something.us3.oraclecloud.com/xmlpserver

        2.) Once logged in, create a new Data Model

        3.) On the Diagram tab, click the + icon and choose SQL Query

        4.) Paste the SQL text into the box. Oracle BI EE is not the proper Data Source; it will be an option with ApplicationDB_ at the beginning (more than likely, ApplicationDB_FSCM)

        5.) Click OK, then go to the Data tab, click View and see if the query returns any results

        One other thing I had to do is remove the semicolon at the end of the query. Although this is the proper way to end a SQL statement, BI Publisher doesn't use it in this case.

        In the Data tab, you can view the data as an XML tree or in Table View.

        Hopefully this helps!

    • Annette Massie

      Perfect! It is working wonderful. That silly semi-colon had me!