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.
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 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',
'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 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 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 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 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 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 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 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 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 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);
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 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 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 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);
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 as show below :
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 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',
'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 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 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 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 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 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 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 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 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 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);
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 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 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 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);
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 as show below :
7 - Once the program has completed successfully, the next step is to find the imported document. Navigate to the Purchase Order Summary form and query using the Order fields under the Date Ranges tab. The easiest way to do this is to search for POs created from today's date to tomorrow's date. This will capture the newly imported PO as shown below :
8 - Clicking the Open button will open the PO in the Purchase Orders form. The screen shot below shows a PO imported using Scenario (a) as shown above (PO with single Line)
This note only shows a few possible examples and there any many more combinations of data that can be used in the interface tables.. For more information on the open interface, please review the following documentation :
Oracle Manufacturing APIs and Open Interfaces Manual
This manual contains a detailed explanation of the import process as well as the usable fields in the interface tables. The user guide is only available with the 11i version of the online documentation. For information on changes made to the import process in R12, please see the following notes:
Note 437587.1 Release 12 Oracle Purchasing API and Open Interface Changes
Note 823468.1 Release 12.1.1 New and Changed Features in Oracle Purchasing
Note 755979.1 - Where to find Open Interface Documentation for Release 12 Oracle Purchasing?
The following note is useful for diagnosing problems while importing Standard Purchase Orders
Note.781351.1 How To Diagnose Problems With Importing Standard Purchase Orders:
For more Vision Demo Notes, please see Note 1075748.1 : Vision Demo Document Index Procurement)
REFERENCES
NOTE:437587.1 - Release 12 Oracle Purchasing API and Open Interface ChangesNOTE:755979.1 - Where to find Open Interface Documentation for Release 12 Oracle Purchasing?
NOTE:781351.1 - How To Diagnose Problems With Importing Standard Purchase Orders
NOTE:823468.1 - Release 12.1.1 New and Changed Features in Oracle Purchasing
Images are not coming please check once
ReplyDelete