Oracle Purchasing Tables & PO_CHANGE_API1_S API

Usual Oracle Applications PO Tables
PO_HEADERS_ALL: Purchase Order information with Supplier, Site and status
PO_LINES_ALL: PO Lines with Item Information and quantity
PO_LINE_LOCATIONS_ALL: PO Information on Shipments Destination
PO_DISTRIBUTIONS_ALL: PO Distribution where the accounts are
PO_LINE_LOCATIONS_ARCHIVE_ALL: table updated for history on Shipment/Destination Location Information
PO_LINE_TYPES_B: PO Types used in the PO_LINES_ALL
PO_LINES_TYPES_TL: PO Line Types used in MLS
PO_RELEASES_ALL: Stores revision number for the PO
PO_HEADERS_ARCHIVE_ALL: table updated for the history on the status and PO Header changes, Lines, Location and PO Accounts Distribution
PO_LINES_ARCHIVE_ALL: table updated for the history on the lines
PO_DISTRIBUTIONS_ARCHIVE_ALL: table updated for the history on the account distribution
PO_AGENTS_ALL_V: Buyer
PO_VENDORS: Supplier Table
PO_VENDOR_SITES: Supplier Sites
PO_VENDOR_CONTACTS: Supplier Contacts
PO_HAZARD_CLASSES: contains code and description for hazardous items which gets automatically printed into purchase order, RFQ and Receipt Travelers
PO_REQUISITION_LINES_ALL: Requisition Lines
PO_REQUISITION_HEADERS_ALL: Requisition Headers
PO_REQ_DISTRIBUTIONS_ALL: Distribution Lines of Requisition where accounts are
PO_APPROVED_SUPPLIER_LIST: Supplier List for Auto-Sourcing
PO_ASL_DOCUMENTS: Advanced Shipment Documents
PO_APPROVAL_LIST_HEADERS: PO Approval Path
PO_APPROVAL_LIST_LINES: PO Approval Path


OTHER RELATED: 
MRP_SOURCING_RULES: Used for Auto-Sourcing Rules
MRP_SR_RECEIPT_ORG: Used for Auto-Sourcing Rules
MRP_SR_SOURCE_ORG: Used for Auto-Sourcing Rules
MRP_ASSIGNMENT_SETS: Used for Auto-Sourcing Rules
MRP_SR_ASSIGNMENTS: Used for Auto-Sourcing Rules
RCV_SHIPMENT_HEADERS: Shipment Table Header with grouping information
RCV_SHIPMENT_LINES: Shipment Table lines with item information
RCV_TRANSACTIONS: PO Lines or Requisition received in destination or transit
RCV_ACCOUNTING_EVENTS: Receiving information on accounts
RCV_RECEIVING_SUB_LEDGER: Accounting entries generated for the receiving transactions
RCV_SUB_LEDGER_DETAILS: Detail accounting entries generated for the receiving transactions
RCV_LOT_SUPPLY: Parent for RCV_LOT_TRANSACTIONS
RCV_LOT_TRANSACTIONS: Table housing the information what lot the item is received

INTERFACES:
PO_HEADERS_INTERFACE: Used for creating PO
PO_LINES_INTERFACE: Used for creating PO
PO_DISTRIBUTIONS_INTERFACE: Used for creating PO
PO_REQUISITIONS_INTERFACE: Used for creating Requisition
PO_REQ_DIST_INTERFACE: Used for creating Requisition
PO_INTERFACE_ERRORS: Error created during the processing of the PO or requisition
RCV_INTERFACE: Used for creating Received Items
RCV_HEADERS_INTERFACE: Used for creating Received Group of items
RCV_LOT_INTERFACE: Used for receiving item into a particular lot

API:
Following API can be used to modify
a) Need by Date
b) Promise Date
c) Quantity
d) Price

       po_change_api1_s.update_po
                                 (x_po_number                => c1_rec.po_number
                                 ,x_release_number           => NULL
                                 ,x_revision_number          => v_REVISION_NUM
                                 ,x_line_number              => c1_rec.line_num
                                 ,x_shipment_number          => NULL
                                 ,new_quantity               => NULL
                                 ,new_price                  => c1_rec.new_price
                                 ,new_promised_date          => NULL
                                 ,new_need_by_date           => NULL
                                 ,launch_approvals_flag      => l_chr_lch_appr_flg
                                 ,update_source              => NULL
                                 ,VERSION                    => '1.0'
                                 ,x_override_date            => NULL
                                 ,x_api_errors               => l_rec_out_error
                                 ,p_buyer_name               => c1_rec.buyer
                                 ,p_secondary_quantity       => NULL
                                 ,p_preferred_grade          => NULL
                                 ,p_org_id                   => c1_rec.org_id
                                 );

2) Following is to call the PO workflow.

 po_reqapproval_init1.
          start_wf_process (itemtype                 => 'POAPPRV',
                            itemkey                  => l_num_item_key,
                            workflowprocess          => '<<<<>>>>>>>>>',
                            actionoriginatedfrom     => 'PO_FORM',
                            documentid               => i.po_header_id,
                            documentnumber           => i.segment1,
                            preparerid               => i.agent_id,
                            documenttypecode         => 'PO',
                            documentsubtype          => 'STANDARD',
                            submitteraction          => 'APPROVE',
                            forwardtoid              => NULL,
                            forwardfromid            => NULL,
                            defaultapprovalpathid    => NULL,
                            note                     => NULL,
                            printflag                => 'N',
                            faxflag                  => 'N',
                            faxnumber                => NULL,
                            emailflag                => 'N',
                            emailaddress             => NULL,
                            createsourcingrule       => 'N',
                            releasegenmethod         => 'N',
                            updatesourcingrule       => 'N',
                            massupdatereleases       => 'N',
                            retroactivepricechange   => 'N',
                            orgassignchange          => 'N',
                            communicatepricechange   => 'N',
                            p_background_flag        => 'N',
                            p_initiator              => NULL,
                            p_xml_flag               => NULL,
                            fpdsngflag               => 'N',
                            p_source_type_code       => NULL);

Updating PO using PO_CHANGE_API1_S.Update_PO

Using the PO_CHANGE_API1_S.update_po, the following columns can be updated in a Purchase Order.

- Promised_date
- Need_by_date
- Quantity
- Unit_price

Importance of LAUNCH_APPROVALS_FLAG
=================================

- Indicates if you want to launch APPROVAL workflow after the update. Its value could be either 'Y' or 'N'. If not provided, the default value is 'N'.
- If we are passing launch_approvals_flag =>'N', the PO is remaining in the Requires Reapproval status.

Post Update PO Validation: 
====================
1. Set the PO status to REQUIRES REAPPROVAL.
2. Increment revision number if the PO was in APPROVED status before the update.
3. Launch the PO Approval workflow if LAUNCH_APPROVALS_FLAG = 'Y'.
4. If the quantity was adjusted down to be equal to the total quantity received or billed, then set the appropriate closed code and roll up the closed code to line and header levels.

-- R12 - PO - Sample Script to Update PO Using po_change_api1_s
--=================================================


DECLARE

CURSOR po_date_update
IS

SELECT pha.segment1 po_number, pha.revision_num,pha.po_header_id,
pha.authorization_status, pla.po_line_id, pla.line_num, pha.org_id,
pla.unit_price, pola.line_location_id, pola.shipment_num,
pola.quantity, pola.promised_date, pola.need_by_date,
pha.closed_code
FROM po_headers_all pha, po_lines_all pla,
po_line_locations_all pola
WHERE pha.po_header_id = pla.po_header_id
AND pla.po_line_id = pola.po_line_id
AND NVL (pha.approved_flag, 'N') = 'Y'
AND NVL (pola.cancel_flag, 'N') <> 'Y'
AND NVL (pola.closed_code, 'OPEN') = 'OPEN'
AND NVL (pola.quantity_received, 0) = 0
AND NVL (pola.quantity_billed, 0) = 0
AND pha.segment1 = '11170007326'
AND pla.line_num = 1
AND pha.type_lookup_code = 'STANDARD'
AND pha.org_id = 308;

l_user_id NUMBER;
l_resp_id NUMBER;
l_appl_id NUMBER;
l_result NUMBER;
l_api_errors po_api_errors_rec_type;
l_revision_num NUMBER;
l_promised_date DATE;
l_need_by_date DATE;
l_price po_lines_all.unit_price%TYPE;
l_quantity po_line_locations_all.quantity%TYPE;

BEGIN

SELECT user_id
INTO l_user_id
FROM fnd_user
WHERE user_name = 'CONVERSION';

SELECT responsibility_id, application_id
INTO l_resp_id, l_appl_id
FROM fnd_responsibility_vl
WHERE responsibility_name = 'Purchasing Super User';

fnd_global.apps_initialize (l_user_id, l_resp_id, l_appl_id);

FOR i IN po_date_update

LOOP

mo_global.set_policy_context ('S', i.org_id);
mo_global.init ('PO');

l_promised_date := i.promised_date + 30;
l_need_by_date := i.need_by_date + 35;
l_quantity := i.quantity + 100;
l_price := i.unit_price - 1;

DBMS_OUTPUT.put_line ('Calling po_change_api1_s.update_po To Update PO');
DBMS_OUTPUT.put_line ('===================================');
DBMS_OUTPUT.put_line ('Retrieving the Current Revision Number of PO');

select revision_num
into l_revision_num
from po_headers_all
where segment1 = i.po_number;

l_result :=
po_change_api1_s.update_po
(x_po_number => i.po_number, --Enter the PO Number
x_release_number => NULL, --Enter the Release Num
x_revision_number => l_revision_num, --Enter the Revision Number
x_line_number => i.line_num, --Enter the Line Number
x_shipment_number => i.shipment_num, --Enter the Shipment Number
new_quantity => l_quantity, --Enter the new quantity
new_price => l_price, --Enter the new price,
new_promised_date => l_promised_date, --Enter the new promised date,
new_need_by_date => l_need_by_date, --Enter the new need by date,
launch_approvals_flag => 'Y',
update_source => NULL,
VERSION => '1.0',
x_override_date => NULL,
x_api_errors => l_api_errors,
p_buyer_name => NULL,
p_secondary_quantity => NULL,
p_preferred_grade => NULL,
p_org_id => i.org_id
);

DBMS_OUTPUT.put_line (l_result);

IF (l_result = 1)
THEN
DBMS_OUTPUT.put_line('Successfully update the PO :=>');
END IF;

IF (l_result <> 1)
THEN
DBMS_OUTPUT.put_line ('Failed to update the PO Due to Following Reasons');
-- Display the errors
FOR j IN 1 .. l_api_errors.MESSAGE_TEXT.COUNT
LOOP
DBMS_OUTPUT.put_line (l_api_errors.MESSAGE_TEXT (j));
END LOOP;
END IF;

END LOOP;

END;

PO Update APIs po_change_api1_s.update_po

It is quite often to update some of the lines of a PO after it is approved.
In order to Update a PO, we can use the API po_change_api1_s.update_po.

It Update a standard purchase order or release changes of quantity, price, promise date and Need-by-date.This Need-by-date updation with this API has been enhanced in R12 only. In earlier releases updating the PO line’s Need-by-date is not available.

Psudo code of the usage
l_result := po_change_api1_s.update_po
(x_po_number => p_po_num ,
x_release_number => NULL ,
x_revision_number => l_revision_no,
x_line_number => lines_rec.line_num,
x_shipment_number => lines_rec.shipment_num,
new_quantity => NULL,
new_price => NULL,
new_promised_date => NULL ,
new_need_by_date => TO_DATE(nedbydate,'DD-MON-RRRR'),
launch_approvals_flag => 'N' ,
update_source => NULL,
VERSION => '1.0',
x_api_errors => l_api_errors,
p_buyer_name => Lines_rec.agent_name,
p_secondary_quantity => NULL,
p_preferred_grade => NULL,
p_org_id => g_org_id);

It will either return a 1 for successful update or 0 for failure.

Pre-requisites to use this API
1) PO header to be either in APPROVED or REQUIRES REAPPROVAL status.
2) No update if the revision number doesn't match the current revision.
Post Update
1)Upon successful updation,the PO revision will be incremented.
2) if launch_approvals_flag is set to 'Y' then it will launch the PO Approval. But if you set this parameter as 'Y' then the program try to submit the PO Approval for every successful update of the line.Most of the time this Approval will not update the PO to APPROVED, it may be delay waiting for some other input, so it will make the PO status as INPROCESS.
So please avoid to launch the Po Approval with this Update PO API.
3)The authorization status of the PO will be in REQUIRES REAPPROVAL is the above paramter is set to 'N'

Difficulties faced:
1) This will update the PO if it have one line without any issue in the first run.
2) If the PO have more than one line, then we need to run the Update program the no.of lines po have.We need to pass the Po line num for every run.
3)If the first PO line got updated then revision will gets chnaged to next highest number.So we need to grab the revision number for every updated line and need to input to the above program.
4) By doing this we can avoid the weird error 'The revision number doesn't match the current revision'.
5)Need to launch the PO approval manually because it will leave teh PO in REQUIRES REAPPROVAL state.