2 Replies Latest reply on Sep 27, 2016 4:03 PM by J Reinhart

    Approach to update PO_REQUISITION_LINES_ALL & PO_DISTRIBUTIONS_ALL

    2797876

      HI Team,

       

      I have a requirement to develop inbound interface for to update Need_by_date in PO_REQUISITION_LINES_ALL table and attribute1 column in PO_DISTRIBUTIONS_ALL table.

      Do we have any API's for the same or suggest me any approaches to build the code.

       

      Regards,

      Kumar

        • 1. Re: Approach to update PO_REQUISITION_LINES_ALL & PO_DISTRIBUTIONS_ALL
          2797876

          Hi Team,

           

          Can you please provide me any details.

          • 2. Re: Approach to update PO_REQUISITION_LINES_ALL & PO_DISTRIBUTIONS_ALL
            J Reinhart

            Hi Kumar,

             

            You can take a look at api po_requisition_update_pub, but if I recall correctly, it limits you to requisitions that aren't linked to a PO.   Check to make sure, though.  If it works, then use it.

             

            Unfortunately, direct table updates of standard tables aren't supported by Oracle, and many companies won't allow them.  If you still want to do it, there are a couple of ways.

             

            What is the volume of changes you need to bring in?  If there are only a few, then you could write a concurrent program that takes the values as inputs and updates the tables directly.   The concurrent program would read the inputs, do any validation that is necessary, such as ensuring the need-by is later than the requisition creation and/or later than the current date.  Then do two update statements, one to each table, being certain to use the primary key of each so you don't accidentally update more records than you intend.   Put a message in the log confirming the number of records and the id's and even the before/after values, so that you can troubleshoot any issues

             

            If you  have high volume, then it may be best to create a custom table for staging, with the Req identifier, the new date, "who" columns (created by , creation date, updated by, update date, request id), a status, and a space for the old date.   Create a flat file with your data.   Use SQL*Loader to read the flat file and load the staging table, including the status (pending?) and the "who" data.  Then run a program to read the staging table and update the requisition need by date and the PO DFF, then update the staging table with a new status (complete? error message if it failed) and update the "updated" part of the "who" data.  You could write one program for it all, but many people like to see the steps, so you could have a request set with one concurrent program that just calls SQL*Loader to load the staging table and another that does the update.   Be sure to include messages in the output and/or log, such as "100 records found, 100 inserted into staging table, 96 reqs updated, 4 errors"  that kind of thing, so the user has a way to confirm how many records it found and whether all the updates were successful.   You will also want some good error handling, to put the error code in the staging table and to make sure that the updates to Req and PO either both happen or neither happen.

             

            Regards,

             

            J