Purchase Order conversion

 Purchase Order conversion:

The Purchasing Document Open Interface concurrent program was replaced by two new concurrent programs Import Price Catalogs and Import Standard Purchase Orders. Import Price Catalogs concurrent program is used to import Catalog Quotations, Standard Quotations, and Blanket Purchase Agreements. Import Standard Purchase Orders concurrent program is used to import Unapproved or Approved Standard Purchase Orders.

Import Standard Purchase Orders

Pre-requisites:

Suppliers, sites and contacts

Buyers

Line Types

Items

PO Charge account setup

Interface Tables:

PO_HEADERS_INTERFACE

PO_LINES_INTERFACE

PO_DISTRIBUTIONS_INTERFACE

PO_INTERFACE_ERRORS (Fallouts)

 Interface Program:

Import Standard Purchase Orders.

Base Tables:

PO_HEADERS_ALL

PO_LINES_ALL

PO_DISTRIBUTIONS_ALL

PO_LINE_LOCATIONS_ALL

 Validations:

Header:

Check if OU name is valid

Check if Supplier is valid

Check if Supplier site is valid

Check if buyer is valid

Check if Payment term is valid

Check if Bill to and ship to are valid

Check if FOB, freight terms are valid

 Lines:

Check if Line_type, ship_to_org, item, uom, ship_to_location_id, requestor, charge_account, deliver_to_location are valid

General:

Check for duplicate records in interface tables

Check if the record already exists in base tables.

Some important columns that need to be populated in the interface tables:

PO_HEADERS_INTERFACE:

INTERFACE_HEADER_ID (PO_HEADERS_INTERFACE_S.NEXTVAL), BATCH_ID, ORG_ID, INTERFACE_SOURCE_CODE, ACTION (‘ORIGINAL’,'UPDATE’,'REPLACE’), GROUP_CODE, DOCUMENT_TYPE_CODE, PO_HEADER_ID (NULL), RELEASE_ID, RELEASE_NUM, CURRENCY_CODE, RATE, AGENT_NAME, VENDOR_ID, VENDOR_SITE_ID, SHIP_TO_LOCATION, BILL_TO_LOCATION, PAYMENT_TERMS

 PO_LINES_INTERFACE:

INTERFACE_LINE_ID, INTERFACE_HEADER_ID, LINE_NUM, SHIPMENT_NUM, ITEM, REQUISITION_LINE_ID, UOM, UNIT_PRICE, FREIGHT_TERMS, FOB

 PO_DISTRIBUTIONS_INTERFACE:

INTERFACE_LINE_ID, INTERFACE_HEADER_ID, INTERFACE_DISTRIBUTION_ID, DISTRIBUTION_NUM, QUANTITY_ORDERED, QTY_DELIVERED, QTY_BILLED, QTY_CANCELLED, DELIVER_TO_LOCATION_ID, DELIVER_TO_PERSON_ID, SET_OF_BOOKS, CHARGE_ACCT, AMOUNT_BILLED.

 Import Blanket Purchase Agreements:

Interface Tables:

PO_HEADERS_INTERFACE

PO_LINES_INTERFACE

 Interface program:

Import Price Catalogs

 Base tables:

PO_HEADERS_ALL

PO_LINES_ALL

PO_LINE_LOCATIONS_ALL

 Example:

Suppose you want to create a blanket with one line and two price breaks and the details for the price break are as below:

1) Quantity = 500, price = 10, effective date from ’01-JAN-2006′ to

’31-JUN-2006′

2) Quantity = 500, price = 11, effective date from ’01-JUL-2006′ to

’01-JAN-2007′

To create the above the BPA, you would create ONE record in PO_HEADERS_INTERFACE and THREE records in PO_LINES_INTERFACE

LINE1: It will have only the line information. LINE NUM would be 1.

LINE2: For the first Price Break details, LINE NUM will be the same as above i.e. 1. SHIPMENT_NUM would be 1 and SHIPMENT_TYPE would be ‘PRICE BREAK’

 LINE3: For the second Price Break details, LINE NUM will be the same as above i.e. 1. SHIPMENT_NUM would be 2 and SHIPMENT_TYPE would be ‘PRICE BREAK’

 All the line-level records above must have the same INTERFACE_HEADER_ID.

·                     Requisition import

You can automatically import requisitions into Oracle Applications using the Requisitions Open Interface

Pre-requisites:

    Set of Books

    Code combinations

    Employees

    Items

Define a Requisition Import Group-By method in the Default region of the Purchasing

Options window.

Associate a customer with your deliver-to location using the Customer Addresses

window for internally sourced requisitions.

   Interface tables:    

PO_REQUISITIONS_INTERFACE_ALL

        PO_REQ_DIST_INTERFACE_ALL        
    Base tables:        

PO_REQUISITIONS_HEADERS_ALL                        

PO_REQUISITION_LINES_ALL        

PO_REQ_DISTRIBUTIONS_ALL

 Concurrent program:

REQUISITION IMPORT

     Validations:    

Check for interface transaction source code, requisition destination type.

        Check for quantity ordered, authorization status type.

Some important columns that need to be populated in the interface tables:

PO_REQUISITIONS_INTERFACE_ALL:

INTERFACE_SOURCE_CODE (to identify the source of your imported

Requisitions)

DESTINATION_TYPE_CODE

AUTHORIZATION_STATUS

PREPARER_ID or PREPARER_NAME

QUANTITY

CHARGE_ACCOUNT_ID or charge account segment values

DESTINATION_ORGANIZATION_ID or DESTINATION_ORGANIZATION_

CODE

DELIVER_TO_LOCATION_ID or DELIVER_TO_LOCATION_CODE

DELIVER_TO_REQUESTOR_ID or DELIVER_TO_REQUESTOR_NAME

ORG_ID

ITEM_ID or item segment values (values if the SOURCE_TYPE_CODE or

DESTINATION_TYPE_CODE is ‘INVENTORY’)

PO_REQ_DIST_INTERFACE_ALL:

CHARGE_ACCOUNT_ID or charge account segment values

DISTRIBUTION_NUMBER

DESTINATION_ORGANIZATION_ID

DESTINATION_TYPE_CODE

INTERFACE_SOURCE_CODE

ORG_ID

DIST_SEQUENCE_ID (if MULTI_DISTRIBUTIONS is set to Y)

·                     PO Receipts Interface

The Receiving Open Interface is used for processing and validating receipt data that

comes from sources other than the Receipts window in Purchasing.

 Pre-requisites:

    Set of Books

    Code combinations

    Employees

    Items

    Interface tables:

RCV_HEADERS_INTERFACE

        RCV_TRANSACTIONS_INTERFACE
        PO_INTERFACE_ERRORS    
 Concurrent program:

RECEIVING OPEN INTERFACE

     Base tables:

RCV_SHIPMENT_HEADERS

        RCV_SHIPMENT_LINES
        RCV_TRANSACTIONS
 Validations:

Check that SHIPPED_DATE should not be later than today.

Check if vendor is valid.

If Invoice number is passed, check for its validity

Check if Item is valid

Some important columns that need to be populated in the interface tables:

RCV_HEADERS_INTERFACE:

HEADER_INTERFACE_ID

GROUP_ID

PROCESSING_STATUS_

CODE

RECEIPT_SOURCE_CODE

TRANSACTION_TYPE

SHIPMENT_NUM

RECEIPT_NUM

VENDOR_NAME

SHIP_TO_

ORGANIZATION_CODE

SHIPPED_DATE

INVOICE_NUM

INVOICE_DATE

TOTAL_INVOICE_

AMOUNT

PAYMENT_TERMS_ID

EMPLOYEE_NAME

VALIDATION_FLAG (Indicates whether to validate a row or not, values ‘Y’, ‘N’)

RCV_TRANSACTIONS_INTERFACE:

INTERFACE_TRANSACTION_ID

GROUP_ID

TRANSACTION_TYPE (‘SHIP’ for a standard shipment (an ASN or ASBN)

or ‘RECEIVE’ for a standard receipt)

TRANSACTION_DATE

PROCESSING_STATUS_CODE =’PENDING’

CATEGORY_ID

QUANTITY

UNIT_OF_MEASURE

ITEM_DESCRIPTION

ITEM_REVISION

EMPLOYEE_ID

AUTO_TRANSACT_CODE

SHIP_TO_LOCATION_ID

RECEIPT_SOURCE_CODE

TO_ORGANIZATION_CODE

SOURCE_DOCUMENT_CODE

PO_HEADER_ID

PO_RELEASE_ID

PO_LINE_ID

PO_LINE_LOCATION_ID

PO_DISTRIBUTION_ID

SUBINVENTORY

HEADER_INTERFACE_ID

DELIVER_TO_PERSON_NAME

DELIVER_TO_LOCATION_CODE

VALIDATION_FLAG

ITEM_NUM

VENDOR_ITEM_NUM

VENDOR_ID

VENDOR_SITE_ID

ITEM_ID

ITEM_DESCRIPTION

SHIP_TO_LOCATION_ID

·                     GL Journal interface

This interface lets you import journals from other applications like Receivables, Payables etc to integrate the information with General Ledger.

Pre-requisites:

    Set of Books

Flex field Value sets

Code Combinations

Currencies

Categories

Journal Sources

    Interface tables:    

GL_INTERFACE

Base tables:        

GL_JE_HEADERS    

GL_JE_LINES    

GL_JE_BACTHES

Concurrent Program:    

Journal Import

Journal Posting — populates GL_BALANCES

Validations:    

Validate SOB, journal source name, journal category name, actual flag

    A – Actual amounts

    B – Budget amounts

    E – Encumbrance amount

If you enter E in the interface table, then enter appropriate encumbrance ID, if

B enter budget id.

Check if accounting date or GL date based period name is valid (i.e., not closed).

Check if accounting date falls in open or future open period status.

Check chart of accounts id based on Sob id.

Check if code combination is valid and enabled.

Check if record already exists in GL interface table.

Check if already journal exists in GL application.

Some important columns that need to be populated in the interface tables:
GL_INTERFACE:

STATUS

SET_OF_BOOKS_ID

ACCOUNTING_DATE

CURRENCY_CODE

DATE_CREATED

CREATED_BY

ACTUAL_FLAG

USER_JE_CATEGORY_NAME

USER_JE_SOURCE_NAME

CURRENCY_CONVERSION_DATE

ENCUMBRANCE_TYPE_ID

BUDGET_VERSION_ID

USER_CURRENCY_CONVERSION_TYPE

CURRENCY_CONVERSION_RATE

SEGMENT1 to

ENTERED_DR

ENTERED_CR

ACCOUNTED_DR

ACCOUNTED_CR

TRANSACTION_DATE

PERIOD_NAME

JE_LINE_NUM

CHART_OF_ACCOUNTS_ID

FUNCTIONAL_CURRENCY_CODE

CODE_COMBINATION_ID

DATE_CREATED_IN_GL

GROUP_ID

·                     GL budget interface

Budget interface lets you load budget data from external sources into Oracle Applications.

Pre-requisites:

    Set of Books

Flex field Value sets

Code Combinations

    Interface tables:    

GL_BUDGET_INTERFACE

    
    Base tables:        

GL_BUDGETS    

GL_BUDGET_ASSIGNMENTS    

        GL_BUDGET_TYPES

Concurrent program:

Budget Upload    

Validations:
        Check if CURRENCY_CODE is valid.
        Check if SET_OF_BOOKS_ID is valid.

Check if BUDGET_ENTITY_NAME (budget organization) is valid.

Some important columns that need to be populated in the interface tables:

GL_BUDGET_INTERFACE:

BUDGET_NAME NOT

BUDGET_ENTITY_NAME

CURRENCY_CODE

FISCAL_YEAR

UPDATE_LOGIC_TYPE

BUDGET_ENTITY_ID

SET_OF_BOOKS_ID

CODE_COMBINATION_ID

BUDGET_VERSION_ID

PERIOD_TYPE

DR_FLAG

STATUS

ACCOUNT_TYPE

PERIOD1_AMOUNT through PERIOD60_AMOUNT

SEGMENT1 through SEGMENT30

·                     GL daily conversion rates

This interface lets you load the rates automatically into General Ledger.

Pre-requisites:

    Currencies

    Conversion rate Types

    Interface tables:    

GL_DAILY_RATES_INTERFACE

    Base tables:        

GL_DAILY_RATES        

GL_DAILY_CONVERSION_TYPES

    
    Concurrent Program:

You do not need to run any import programs. The insert, update, or deletion of rates in GL_DAILY_RATES is done automatically by database triggers on the GL_DAILY_RATES_INTERFACE. All that is required is to develop program to populate the interface table with daily rates information.

    Validations:

Check if
FROM_CURRENCY and TO_CURRENCY are valid.

Check if USER_CONVERSION_TYPE is valid.

Some important columns that need to be populated in the interface tables:

GL_DAILY_RATES_INTERFACE:

FROM_CURRENCY

TO_CURRENCY

FROM_CONVERSION_DATE

TO_CONVERSION_DATE

USER_CONVERSION_TYPE

CONVERSION_RATE

MODE_FLAG (D= Delete, I = Insert, U = Update)

INVERSE_CONVERSION_RATE