Reporting and Analytics for ERP

Get Involved. Join the Conversation.

Topic

    Umamaheswara Reddy Karri
    What is the best option for incremental extracts in...Answered
    Topic posted June 25, 2019 by Umamaheswara Reddy KarriRed Ribbon: 250+ Points, last edited June 25, 2019, tagged BI Publisher, Fusion Procurement reporting, Reports 
    91 Views, 4 Comments
    Title:
    What is the best option for incremental extracts in Procurement Cloud 19a
    Summary:
    What is the best option for incremental extracts in Procurement Cloud 19a
    Content:

    We have a requirement to extract PO information on the basis of an incremental update. i.e. POs created or updated from the last extract.  We have developed a BIP report to extract the required information.

    What are the options available to generate "from and to date" parameter values for each scheduled request in the cloud?  We are thinking of referring to ESS Job history table to know when was the last request executed successfully to derive the start date for the next request.  But, not sure if there is any other better option.

    Any ideas, please.

    Version:
    19a

    Best Comment

    Madhu Babu Vitta

    Hi ,

    For incremental extract , you can create ESS job of type BIP.

    In the BIP report datamodel query refer table ESS_REQUEST_HISTORY for the last run date. Then use this as reference ( like -poh.creation_date > last_run_date) in your main query to fetch latest/incremental data only.

    Thanks,

    Madhu 

    Comment

     

    • Senthilrajan Vaithianathan

      We are doing a incremental extract of PO Headers/Lines using BI Report, however we are using SOA Cloud Services for invoking the BI Report and use the SOA Interface table to stamp the last successful run date/time which we use for subsequent jobs. We are passing the last run date and time as an input parameter for invoking this report.

      You can also use ESS_REQUEST_HISTORY/ESS_REQUEST_PROPERTY tables to get the last successful run of this report.

      pha.po_header_id IN
             (SELECT po_header_id FROM po_headers_all WHERE last_update_date BETWEEN :p_from_date AND :p_to_date
              UNION
              SELECT po_header_id FROM po_lines_all WHERE last_update_date BETWEEN :p_from_date AND :p_to_date
              UNION
              SELECT po_header_id FROM po_line_locations_all WHERE last_update_date BETWEEN :p_from_date AND :p_to_date
              UNION
              SELECT po_header_id FROM po_distributions_all WHERE last_update_date BETWEEN :p_from_date AND :p_to_date))
    • Madhu Babu Vitta

      Hi ,

      For incremental extract , you can create ESS job of type BIP.

      In the BIP report datamodel query refer table ESS_REQUEST_HISTORY for the last run date. Then use this as reference ( like -poh.creation_date > last_run_date) in your main query to fetch latest/incremental data only.

      Thanks,

      Madhu