Oracle Purchase order Migration Setups

Purchase Order migration

BASIC INFORMATION

1. In order to migrate standard purchase orders Purchasing documents open interface (PDOI) is used.
2. The basic tables that are required for these are.
• PO_HEADERS_INTERFACE
• PO_LINES_INTERFACE
• PO_DISTRIBUTIONS_INTERFACE
3. After the interface tables is run the following tables get populated
• PO_HEADERS_ALL
• PO_LINES_ALL
• PO_DISTRIBUTIONS_ALL
• PO_LINE_LOCATIONS_ALL.

DESIGN

The architecture for the migration stages and all remains common across modules.

Refer the start of the document.

REQUIREMENTS

The migration was done only for OPEN Purchase orders. To identify a purchase order as open all lines for PO were considered. Lines were said to be open if ordered quantity<>received quantity<>vouched quantity. If all lines are closed for a particular PO then the po was closed else if any line was open then the po stands as open and only the open lines were migrated.

To get the received quantity and the vouched quantity the tables for receipts in the legacy were used.

PREQUISITES BEFORE MIGRATION

·        Employees have already been migrated

·        Vendors have been migrated

·        Jobs have been defined

·        Positions have been defined

·        The Position Hierarchy has been defined.

·        Approval Groups have been defined

·        Approval Assignments have been defined

·        The Purchase Order numbering is ‘Manual ’ at the time of migration but will be set to ‘Automatic’ after the migration.

·        Various Ship-to and Bill-to locations for the various organizations have already been defined.

·        Oracle Purchasing Look Up codes defined.

·        The Various Document Types have been defined.

·        The Attributes for every PO type are set in such a manner that the owner can approve, modify and change the ‘forward to’ for his/her own POs.

PROBLEMS ENCOUNTERED AND QUESTIONS

1.      What are the basic columns populated in the interface tables?

PO_HEADERS_INTERFACE

interface_header_id ,
last_update_date,
last_updated_by,
last_update_login,
creation_date,
created_by,
document_num,
document_type_code,
vendor_site_code,
vendor_name,
currency_code,
agent_name,
payment_terms,
ship_to_location

vendor_doc_num,
bill_to_location,
request_id,
program_application_id,
action,
process_code

Of these the hard coded values are Process_code should be ‘PENDING’ (in capitals matters) Action should be ‘ORIGINAL’ All standard who columns must be entered. Interface header id is populated using a user-defined sequence. Either the vendor id or the vendor name can be populated. Also same stands for all id’s and names.

PO_LINES_INTERFACE

interface_header_id,
interface_line_id,
last_update_date,
last_updated_by,
last_update_login,
creation_date,
created_by,  
line_num,
shipment_num,
line_type,
item_id,
quantity,
unit_price,
uom_code,
receiving_routing,
promised_date,
need_by_date,
request_id,
program_application_id

note: the need by dates  and the promise date should be same as the sysdate else the PDOI gives an error for this . Or the GL dates for that period should be open.

PO_DISTRIBUTIONS_INTERFACE

interface_header_id,
interface_line_id,
interface_distribution_id,
last_update_date,
last_updated_by,
last_update_login,
creation_date,
created_by,
deliver_to_location,
quantity_ordered,
distribution_num,
destination_organization,
request_id,
program_application_id

Note: Here the deliver to location is different from the bill to location that pops up on the header screen. This will be the location in the shipments for each of the lines.

Also the charge account need not be populated. It defaults from the destination_organization that is populated.

2.      What is the derivation error?

A derivation error is not due to data in consistence or so. It is the result of deriving data from some id or name that is given in the interface tables.

For e.g. :If there is some agent name and the agent id is not found by PDOI then derivation error will result.

In our case agent names were picked up from look up tables.

3.       Error: need_by_date and promised_Date

These dates have to be either same or more than the sysdate or the gl periods for these dates have to be open.

Generally these dates from the legacy cannot be changed as it corresponds to the dates when the items are required.

4.       Agent_name is invalid.

If the agent name is selected from a lookup table  and not defined as a buyer then this error occurs.

5.       Bill to location or s hip to location is invalid.

If the location are selected from a look up table then

·         ·        This location must be defined (with same caps or small case as in look up table)

·         ·        Secondly this location must be checked as a bill to location if migrated as bill to location or as a ship to location id migrated as ship to (purchasing-setup-organizations-locations-shipping details)