Vision Demo - How To Import Standard Purchase Orders Using the Purchase Document Open Interface.

Check the follow link() for more detail:
https://support.oracle.com/CSP/ui/flash.html#tab=KBHome(page=KBHome&id=()),(page=KBNavigator&id=(bmDocType=HOWTO&bmDocID=1054004.1&viewingMode=1143&from=BOOKMARK&bmDocTitle=Vision%20Demo%20-%20How%20To%20Import%20Standard%20Purchase%20Orders%20Using%20the%20Purchase%20Document%20Open%20Interface&bmDocDsrc=KB))

Applies to:

Oracle Purchasing - Version: 11.5.1 to 12.1 - Release: 11.5 to 12
Information in this document applies to any platform.

Goal:

What are the steps required to import Standard Purchase Orders using the Purchasing Document Open Interface and is there any sample sample data that can be used in a vision environment ?

This note is designed to explain the steps to import a Standard Purchase Order and to provide sample data from a Vision environment to facilitate testing.

Please note that the scripts provided here are only sample scripts to introduce users to the functionality. More columns are available for use in the interface tables and more data can be imported depending on what is required. Please see the section at the end of the note for more documentation on the import process.

The scope of this Note is limited to importing Standard Purchase Orders. The use of the Import Price Catalog program will not be demonstrated here.

Solution:

1 - Open SQL Developer and open database connection for the environment to be used for import

WARNING : This step will delete data from the interface tables. Do not do this if others are testing any part of the purchasing process as these tables are used, not only during import, but during other processes such as autocreate. This step is only to be used during testing in test environment. Please do not delete interface data in a production environment.

Run the following statements to clear existing data from the interface tables. Please note that this may interfere with other testing.

delete from po_headers_interface;
delete from po_lines_interface;
delete from po_distributions_interface;
delete from po_interface_errors;
commit;

This step can be ignored if required. This step is only included here to clear out any data from the interface tables so as to prevent any confusion over the results obtained by running the import program.


2 - Please use the following insert statements to populate the interface tables depending on the scenario required :

a - PO with a single line :
-- Insert the Purchase Order Herders
INSERT INTO po.po_headers_interface
(interface_header_id,
batch_id,
process_code,
action,
org_id,
document_type_code,
currency_code,
agent_id,
vendor_name,
vendor_site_code,
ship_to_location,
bill_to_location,
reference_num)
VALUES
(apps.po_headers_interface_s.NEXTVAL,
1,
'PENDING', --process_code
'ORIGINAL',  --  Indicates this is a new document
204, -- Org id for operating unit Vision Operations
'STANDARD',  -- Indicates a standard PO is being imported
'USD', -- The currency to be used in the PO
57, -- The ID of the buyer
'Office Supplies, Inc.',  -- Supplier name
'OFFICESUPPLIES',  --  Supplier Site
'V1- New York City', -- Ship to location name
'V1- New York City', -- Bill to location name
'TestPO'      -- Reference that can be tied to the imported PO
);

-- Insert the Purchase Order Lines
INSERT INTO po.po_lines_interface
(interface_line_id,
interface_header_id,
line_num,
shipment_num,
line_type,
item,
uom_code,
quantity,
unit_price,
ship_to_organization_code,
ship_to_location)
VALUES
(po_lines_interface_s.nextval,
po_headers_interface_s.currval,
1,
1,
'Goods',
'CM96713',  -- Item to imported on the PO line
'Ea',  -- Unit of Measure
1,
50,  --  Line price in specified currency
'V1',  --  Inventory Organization which will receive the shipment
'V1- New York City'  --  Ship to location name
);
commit;


b - PO with one line and one distribution :
-- Insert the Purchase Order Headers.
INSERT INTO po.po_headers_interface
(interface_header_id,
batch_id,
process_code,
action,
org_id,
document_type_code,
currency_code,
agent_id,
vendor_name,
vendor_site_code,
ship_to_location,
bill_to_location,
reference_num)
VALUES
(apps.po_headers_interface_s.NEXTVAL,
2,
'PENDING',
'ORIGINAL',
204,
'STANDARD',
'USD',
57, -- Your buyer id
'Office Supplies, Inc.',
'OFFICESUPPLIES',
'V1- New York City',
'V1- New York City',
'TestPO');

-- Insert the Purchase Order Lines
INSERT INTO po.po_lines_interface
(interface_line_id,
interface_header_id,
line_num,
shipment_num,
line_type,
item,
uom_code,
quantity,
unit_price,
ship_to_organization_code,
ship_to_location)
VALUES
(po_lines_interface_s.nextval,
po_headers_interface_s.currval,
1,
1,
'Goods',
'CM96713',
'Ea',
1,
50,
'V1',
'V1- New York City' );

-- Insert the Purchase Order Distributions
INSERT INTO po.po_distributions_interface
(interface_header_id,
interface_line_id,
interface_distribution_id,
distribution_num,
quantity_ordered,
charge_account_id)
VALUES
(po_headers_interface_s.currval,
po.po_lines_interface_s.CURRVAL,
po.po_distributions_interface_s.NEXTVAL,
1,
1,
12975 -- Code Combination ID for the Charge Account to be used on the Distribution
);
commit;


c - PO with two lines :

-- Insert the Purchase Order Headers.
INSERT INTO po.po_headers_interface
(interface_header_id,
batch_id,
process_code,
action,
org_id,
document_type_code,
currency_code,
agent_id,
vendor_name,
vendor_site_code,
ship_to_location,
bill_to_location,
reference_num)
VALUES
(apps.po_headers_interface_s.NEXTVAL,
3,
'PENDING',
'ORIGINAL',
204,
'STANDARD',
'USD',
57,
'Office Supplies, Inc.',
'OFFICESUPPLIES',
'V1- New York City',
'V1- New York City',
'TestPO');

-- Insert two Purchase Order Lines.
-- First Line.
INSERT INTO po.po_lines_interface
(interface_line_id,
interface_header_id,
line_num,
shipment_num,
line_type,
item,
uom_code,
quantity,
unit_price,
ship_to_organization_code,
ship_to_location)
VALUES
(po_lines_interface_s.nextval,
po_headers_interface_s.currval,
1,
1,
'Goods',
'CM96713',
'Ea',
1,
50,
'V1',
'V1- New York City'
);

-- Second Line
INSERT INTO po.po_lines_interface
(interface_line_id,
interface_header_id,
line_num,
shipment_num,
line_type,
item,
uom_code,
quantity,
unit_price,
ship_to_organization_code,
ship_to_location)
VALUES
(po_lines_interface_s.nextval,
po_headers_interface_s.currval,
2,
1,
'Goods',
'CM96713',
'Ea',
1,
80,
'V1',
'V1- New York City'
);
commit;


d - PO with one line and two shipments :

-- Insert the Purchase Order Header
INSERT INTO po.po_headers_interface
(interface_header_id,
batch_id,
process_code,
action,
org_id,
document_type_code,
currency_code,
agent_id,
vendor_name,
vendor_site_code,
ship_to_location,
bill_to_location,
reference_num)
VALUES
(apps.po_headers_interface_s.NEXTVAL,
4,
'PENDING',
'ORIGINAL',
204,
'STANDARD',
'USD',
57,
'Office Supplies, Inc.',
'OFFICESUPPLIES',
'V1- New York City',
'V1- New York City',
'TestPO'
);

-- Insert two Purchase Order Lines
-- The First Shipment
INSERT INTO po.po_lines_interface
(interface_line_id,
interface_header_id,
line_num,
shipment_num,
line_type,
item,
uom_code,
quantity,
unit_price,
ship_to_organization_code,
ship_to_location,
promised_date)
VALUES
(po_lines_interface_s.nextval,
po_headers_interface_s.currval,
1,
1,
'Goods',
'CM96713',
'Ea',
1,
50,
'V1',
'V1- New York City',
sysdate+1
);

-- The Second Shipment
INSERT INTO po.po_lines_interface
(interface_line_id,
interface_header_id,
line_num,
shipment_num,
line_type,
item,
uom_code,
quantity,
unit_price,
ship_to_organization_code,
ship_to_location,
promised_date)
VALUES
(apps.po_lines_interface_s.NEXTVAL,
apps.po_headers_interface_s.CURRVAL,
1,
2,
'Goods',
'CM96713',
'Ea',
2,
50,
'V1',
'V1- New York City',
sysdate+5
);
commit;


e - PO with one line, one shipment and two distributions :

-- Insert the Purchase Order Header
INSERT INTO po.po_headers_interface
(interface_header_id,
batch_id,
process_code,
action,
org_id,
document_type_code,
currency_code,
agent_id,
vendor_name,
vendor_site_code,
ship_to_location,
bill_to_location,
reference_num)
VALUES
(apps.po_headers_interface_s.NEXTVAL,
5,
'PENDING',
'ORIGINAL',
204,
'STANDARD',
'USD',
57,
'Office Supplies, Inc.',
'OFFICESUPPLIES',
'V1- New York City',
'V1- New York City',
'TestPO'
);

-- Insert the Purchase Order Line
INSERT INTO po.po_lines_interface
(interface_line_id,
interface_header_id,
line_num,
shipment_num,
line_type,
item,
uom_code,
quantity,
unit_price,
ship_to_organization_code,
ship_to_location,
promised_date)
VALUES
(po_lines_interface_s.nextval,
po_headers_interface_s.currval,
1,
1,
'Goods',
'CM96713',
'Ea',
5,
50,
'V1',
'V1- New York City',
sysdate+1
);

-- Insert two Purchase Order Distribution
-- The First Distribution
INSERT INTO po.po_distributions_interface
(interface_header_id,
interface_line_id,
interface_distribution_id,
distribution_num,
quantity_ordered,
charge_account_id)
VALUES
(po_headers_interface_s.currval,
po.po_lines_interface_s.CURRVAL,
po.po_distributions_interface_s.NEXTVAL,
1,
1,
12975
);

-- The Second Distribution
INSERT INTO po.po_distributions_interface
(interface_header_id,
interface_line_id,
interface_distribution_id,
distribution_num,
quantity_ordered,
charge_account_id)
VALUES
(po_headers_interface_s.currval,
po.po_lines_interface_s.CURRVAL,
po.po_distributions_interface_s.NEXTVAL,
2,
4,
12976
);
commit;

3 - The following queries can be used to check the data before the import program is run :

select * from po_headers_interface;
select * from po_lines_interface;
select * from po_distributions_interface;

4 - Login to Oracle Applications as CBAKER/WELCOME

5 - Navigate to responsibility Purchasing, Vision Operations (USA)

6 - Navigate to the Request Submission form. and select the "Import Standard Purchase Orders" request. Set the parameter Create or Update Items to "No" and the parameter Approval Status to "Incomplete" .

7- If there is a problem with the program and it errors out, the "Purchasing Interface Errors Report" request can be run to provide information as to the cause of the error.

If the PO was not imported, the following SQL can be run to query the Interface Errors table and show the same information as the Purchasing Interface Errors Report :

Select * from PO_INTERFACE_ERRORS where interface_type = 'PO_DOCS_OPEN_INTERFACE'
order by creation_date desc;