Thursday, 7 July 2011

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

· 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

Conversions with example

Conversions with example

Conversion/Interface Strategy:

1. Data Mapping

During the data mapping process, list of all the data sets and data elements that will need to be moved into the Oracle tables as part of conversion are identified. Data mapping tables are prepared as part of this activity that show what are the data elements that are needed by the target system to meet the business requirements and from where they will be extracted in the old system.

2. Download Programs

After the conversion data mapping is complete, download programs are developed that are used to extract the identified conversion data elements from the current systems in the form of an ASCII flat file. The structure of the flat file must match the structure of the Oracle standard interface tables. These flat files generated may be in text form or a comma or space delimited, variable or fixed format data file.

3. Upload Program

Once the data has been extracted to a flat file, it is then moved to the target file system and the data from the file is loaded into user defined staging tables in the target database using SQL Loader or UTL_FILE utilities. Then programs are written and run which validate the data in the staging tables and insert the same into the Oracle provided standard Interface tables.

4. Interface Program

Once the interface tables are populated, the respective interface program (each data element interface has a specific interface program to run) is submitted. The interface programs validate the data, derive and assign the default values and ultimately populate the production base tables.

Interface/Conversion examples and details:
The below list of interfaces/conversions are covered in this section. Details like pre-requisites required, interface tables, interface program, base tables, validations that need to be performed after inserting the details into the interface tables and required columns that need to be populated in the interface table are discussed for each interface.

· Order Import Interface (Sales Order Conversion)

· Item import (Item conversion)

· Inventory On-hand quantity Interface

· Customer conversion

· Auto Invoice Interface

· AR Receipts

· Lockbox Interface

· AP Invoices

· Vendor

· Purchase Orders

· Requisition

· Receiving

· Journal import

· Budget import

· Daily Conversion Rates

Order Import Interface (Sales Order Conversion)

Order Import enables you to import Sales Orders into Oracle Applications instead of manually entering them.

Pre-requisites:

Order Type

Line Type

Items

Customers

Ship Method/ Freight Carrier

Sales Person

Sales Territories

Customer Order Holds

Sub Inventory/ Locations

On hand Quantity

Interface tables:

OE_HEADERS_IFACE_ALL

OE_LINES_IFACE_ALL

OE_ACTIONS_IFACE_ALL

OE_ORDER_CUST_IFACE_ALL

OE_PRICE_ADJS_IFACE_ALL

OE_PRICE_ATTS_IFACE_ALL

Base tables:

OE_ORDER_HEADERS_ALL

OE_ORDER_LINES_ALL

Pricing tables: QP_PRICING_ATTRIBUTES
Concurrent Program:

Order Import

Validations:

Check for sold_to_org_id. If does not exist, create new customer by calling create_new_cust_info API.

Check for sales_rep_id. Should exist for a booked order.

Ordered_date should exist (header level)

Delivery_lead_time should exist (line level)

Earliest_acceptable_date should exist.

Freight_terms should exist

Notes:

During import of orders, shipping tables are not populated.

If importing customers together with the order, OE_ORDER_CUST_IFACE_ALL has to be populated and the base tables are HZ_PARTIES, HZ_LOCATIONS.

Orders can be categorized based on their status:

1. Entered orders

2. Booked orders

3. Closed orders

Order Import API OE_ORDER_PUB.GET_ORDER and PROCESS_ORDER can also be used to import orders.

    Some important columns that need to populated in the interface tables:
        OE_HEADERS_IFACE_ALL:
            ORIG_SYS_DOCUMENT_REF
            ORDER_SOURCE

CONVERSION_RATE

ORG_ID

ORDER_TYPE_ID

PRICE_LIST

SOLD_FROM_ORG_ID

SOLD_TO_ORG_ID

SHIP_TO_ORG_ID

SHIP_FROM_ORG_ID

CUSTOMER_NAME

INVOICE_TO_ORG_ID

OPERATION_CODE

OE_LINES_IFACE_ALL

ORDER_SOURCE_ID

ORIG_SYS_DOCUMENT_REF

ORIG_SYS_LINE_REF

ORIG_SYS_SHIPMENT_REF

INVENTORY_ITEM_ID

LINK_TO_LINE_REF

REQUEST_DATE

DELIVERY_LEAD_TIME

DELIVERY_ID

ORDERED_QUANTITY

ORDER_QUANTITY_UOM

SHIPPING_QUANTITY

PRICING_QUANTITY

PRICING_QUANTITY_UOM

SOLD_FROM_ORG_ID

SOLD_TO_ORG_ID

INVOICE_TO_ ORG_ID

SHIP_TO_ORG_ID

PRICE_LIST_ID

PAYMENT_TERM_ID

Item import (Item conversion)
The Item Interface lets you import items into Oracle Inventory.
Pre-requisites:

Creating an Organization

Code Combinations

Templates

Defining Item Status Codes

Defining Item Types

Interface tables:

MTL_SYSTEM_ITEMS_INTERFACE

MTL_ITEM_REVISIONS_INTERFACE (If importing revisions)

MTL_ITEM_CATEGORIES_INTERFACE (If importing categories)

MTL_INTERFACE_ERRORS (View errors after import)

Concurrent Program:

Item import

In the item import parameters form, for the parameter ‘set process id’, specify

the ‘set process id’ value given in the mtl_item_categories_interface table. The

parameter ‘Create or Update’ can have any value. Through the import process, we

can only create item category assignment(s). Updating or Deletion of item category assignment is not supported.

Base tables:

MTL_SYSTEM_ITEMS_B

MTL_ITEM_REVISIONS_B

MTL_CATEGORIES_B

MTL_CATEGORY_SETS_B

MTL_ITEM_STATUS

MTL_ITEM_TEMPLATES

Validations:

Check for valid item type.

Check for valid part_id/segment of the source table.

Validate part_id/segment1 for master org.

Validate and translate template id of the source table.

Check for valid template id. (Attributes are already set for items, default attributes for

that template, i.e., purchasable, stockable, etc)

Check for valid item status.

Validate primary uom of the source table.

Validate attribute values.

Validate other UOMs of the source table.

Check for unique item type. Discard the item, if part has non-unique item type.

Check for description, inv_um uniqueness

Validate organization id.

Load master records and category records only if all validations are passed.

Load child record if no error found.

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

MTL_SYSTEM_ITEMS_INTERFACE:

PROCESS_FLAG = 1 (1= Pending, 2= Assign Complete,

3= Assign/Validation Failed, 4= Validation succeeded; Import failed, 5 = Import in Process,

7 = Import succeeded)

TRANSACTION_TYPE = ‘CREATE’, ‘UPDATE’

SET_PROCESS_ID = 1

ORGANIZATION_ID

DESCRIPTION

ITEM_NUMBER and/or SEGMENT (n)

MATERIAL_COST

REVISION

TEMPLATE_ID

SUMMARY_FLAG

ENABLED_FLAG

PURCHASING_ITEM_FLAG

SALES_ACCOUNT (defaulted from

MTL_PARAMETERS.SALES_ACCOUNT)

COST_OF_SALES_ACCOUNT (defaulted from MTL_PARAMETERS.

COST_OF_SALES_ACCOUNT)

MTL_ITEM_CATEGORIES_INTERFACE:

INVENTORY_ITEM_ID or ITEM_NUMBER.

ORGANIZATION_ID or ORGANIZATION_CODE or both.

TRANSACTION_TYPE = ‘CREATE’ (‘UPDATE’ or ‘DELETE’ is not

possible through Item Import).

CATEGORY_SET_ID or CATEGORY_SET_NAME or both.

CATEGORY_ID or CATEGORY_NAME or both.

PROCESS_FLAG = 1

SET_PROCESS_ID (The item and category interface records should have the

same set_process_id, if you are importing item and category assignment together)

MTL_ITEM_REVISIONS_INTERFACE:

INVENTORY_ITEM_ID or ITEM_NUMBER (Must match the item_number in mtl_system_items_interface table)

ORGANIZATION_ID or ORGANIZATION_CODE or both

REVISION

CHANGE_NOTICE

ECN_INITIATION_DATE

IMPLEMENTATION_DATE

IMPLEMENTED_SERIAL_NUMBER

EFFECTIVITY_DATE

ATTRIBUTE_CATEGORY

ATTRIBUTEn

REVISED_ITEM_SEQUENCE_ID

DESCRIPTION

PROCESS_FLAG = 1

TRANSACTION_TYPE = ‘CREATE’

SET_PROCESS_ID = 1

Each row in the mtl_item_revisions_interface table must have the REVISION

and EFFECTIVITY_DATE in alphabetical (ASCII sort) and chronological

order.

· Inventory On-hand quantity Interface

This interface lets you import the on hand inventory into Oracle.

Interface tables:

MTL_TRANSACTIONS_INTERFACE

MTL_MTL_TRANSACTION_LOTS_INTERFACE (If the item is Lot controlled)

MTL_SERIAL_NUMBERS_INTERFACE (If the item is Serial controlled)

Concurrent Program:

Launch the Transaction Manager through Interface Manager or explicitly call the API – INV_TXN_MANAGER_PUB.PROCESS_TRANSACTIONS () to launch a dedicated transaction worker to process them.

The Transaction Manager picks up the rows to process based on the LOCK_FLAG, TRANSACTION_MODE, and PROCESS_FLAG. Only records with TRANSACTION_MODE of 3, LOCK_FLAG of ’2′, and PROCESS_FLAG of ’1′ will be picked up by the Transaction Manager and assigned to a Transaction Worker. If a record fails to process completely, then PROCESS_FLAG will be set to ’3′ and ERROR_CODE and ERROR_EXPLANATION will be populated with the cause for the error.

Base Tables:

MTL_ON_HAND_QUANTITIES

MTL_LOT_NUMBERS

MTL_SERIAL_NUMBERS

Validations:

Validate organization_id

Check if item is assigned to organization

Validate disposition_id

Check if the item for the org is lot controlled before inserting into the Lots interface table.

Check if the item for the org is serial controlled before inserting into Serial interface table.

Check if inventory already exists for that item in that org and for a lot.

Validate organization_id, organization_code.

Validate inventory item id.

Transaction period must be open.

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

MTL_TRANSACTIONS_INTERFACE:

TRANSACTION_SOURCE_NAME (ANY USER DEFINED VALUE),

TRANSACTION_HEADER_ID (MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL)

TRANSACTION_INTERFACE_ID (MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL – If item is lot or serial controlled, use this field to link to mtl_transactions_interface otherwise leave it as NULL),

TRANSACTION_DATE,

TRANSACTION_TYPE_ID,

PROCESS_FLAG (1 = Yet to be processed, 2 = Processed, 3= Error)

TRANSACTION_MODE (2 = Concurrent – to launch a dedicated transaction worker

to explicitly process a set of transactions.

3 = Background – will be picked up by transaction manager

polling process and assigned to transaction

worker. These will not be picked up until the

transaction manager is running)

SOURCE_CODE,

SOURCE_HEADER_ID,

SOURCE_LINE_ID (Details about the source like Order Entry etc for tracking purposes)

TRANSACTION_SOURCE_ID

Source Type	Foreign Key Reference
Account	GL_CODE_COMBINATIONS.CODE_COMBINATION_ID
Account Alias	MTL_GENERIC_DISPOSITIONS.DISPOSITION_ID
Job or schedule	WIP_ENTITIES.WIP_ENTITY_ID
Sales Order	MTL_SALES_ORDERS.SALES_ORDER_ID

ITEM_SEGMENT1 TO 20,

TRANSACTION_QTY,

TRANSACTION_UOM,

SUBINVENTORY_CODE,

ORGANIZATION_ID,

LOC_SEGMENT1 TO 20.

MTL_TRANSACTION_LOTS_INTERFACE:

TRANSACTION_INTERFACE_ID,

LOT_NUMBER,

LOT_EXPIRATION_DATE,

TRANSACTION_QUANTITY,

SERIAL_TRANSACTION_TEMP_ID (This is required for items under both lot and serial control to identify child records in mtl_serial_numbers_interface)

MTL_SERIAL_NUMBERS_INTERFACE:

TRANSACTION_INTERFACE_ID,

FM_SERIAL_NUMBER,

TO_SERIAL_NUMBER,

VENDOR_SERIAL_NUMBER

· Customer conversion

Customer Interface helps you create customers in Oracle Applications.

Interface tables:

RA_CUSTOMERS_INTERFACE_ALL

RA_CUSTOMER_PROFILES_INT_ALL

RA_CONTACT_PHONES_INT_ALL

RA_CUSTOMER_BANKS_INT_ALL

RA_CUST_PAY_METHOD_INT_ALL

Base tables:

RA_CUSTOMERS

RA_ADDRESSES_ALL

RA_CUSTOMER_RELATIONSHIPS_ALL

RA_SITE_USES_ALL

Concurrent program:

Customer Interface

Validations:

Check if legacy values fetched are valid.

        Check if customer address site is already created.
        Check if customer site use is already created.
        Check is customer header is already created.
        Check whether the ship_to_site has associated bill_to_site
        Check whether associated bill_to_site is created or not.
        Profile amounts validation:

Validate cust_account_id, validate customer status.

Check if the location already exists in HZ_LOCATIONS. If does not exist, create new location.

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

ORIG_SYSTEM_CUSTOMER_REF

SITE_USE_CODE

ORIG_SYSTEM_ADDRESS_REF

INSERT_UPDATE_FLAG (I = Insert, U = Update)

CUSTOMER_NAME

CUSTOMER_NUMBER

CUSTOMER_STATUS

PRIMARY_SITE_USE_FLAG

LOCATION

ADDRESS1

ADDRESS2

ADDRESS3

ADDRESS4

CITY

STATE

PROVINCE

COUNTY

POSTAL_CODE

COUNTRY

CUSTOMER_ATTRIBUTE1

CUSTOMER_ATTRIBUTE2

CUSTOMER_ATTRIBUTE3

CUSTOMER_ATTRIBUTE4

CUSTOMER_ATTRIBUTE5

LAST_UPDATED_BY

LAST_UPDATE_DATE

CREATED_BY

CREATION_DATE

ORG_ID

CUSTOMER_NAME_PHONETIC

RA_CUSTOMER_PROFILES_INT_ALL:

INSERT_UPDATE_FLAG

ORIG_SYSTEM_CUSTOMER_REF

ORIG_SYSTEM_ADDRESS_REF

CUSTOMER_PROFILE_CLASS_NAME

CREDIT_HOLD

LAST_UPDATED_BY

LAST_UPDATE_DATE

CREATION_DATE

CREATED_BY

ORG_ID

RA_CONTACT_PHONES_INT_ALL:

ORIG_SYSTEM_CONTACT_REF

ORIG_SYSTEM_TELEPHONE_REF

ORIG_SYSTEM_CUSTOMER_REF

ORIG_SYSTEM_ADDRESS_REF

INSERT_UPDATE_FLAG

CONTACT_FIRST_NAME

CONTACT_LAST_NAME

CONTACT_TITLE

CONTACT_JOB_TITLE

TELEPHONE

TELEPHONE_EXTENSION

TELEPHONE_TYPE

TELEPHONE_AREA_CODE

LAST_UPDATE_DATE

LAST_UPDATED_BY

LAST_UPDATE_LOGIN

CREATION_DATE

CREATED_BY

EMAIL_ADDRESS

ORG_ID

· Customer API

Trading Community Architecture (TCA) is an architecture concept designed to support complex

trading communities. These APIs utilize the new TCA model, inserting directly to the HZ tables.

API Details:

1. Set the organization id

Exec dbms_application_info.set_client_info(’204′);

2. Create a party and an account

HZ_CUST_ACCOUNT_V2PUB.CREATE_CUST_ACCOUNT()

HZ_CUST_ACCOUNT_V2PUB.CUST_ACCOUNT_REC_TYPE

HZ_PARTY_V2PUB.ORGANIZATION_REC_TYPE

HZ_CUSTOMER_PROFILE_V2PUB.CUSTOMER_PROFILE_REC_TYPE

3. Create a physical location

HZ_LOCATION_V2PUB.CREATE_LOCATION()

HZ_LOCATION_V2PUB.LOCATION_REC_TYPE

4. Create a party site using party_id you get from step 2 and location_id from step 3.

HZ_PARTY_SITE_V2PUB.CREATE_PARTY_SITE()

HZ_PARTY_SITE_V2PUB.PARTY_SITE_REC_TYPE

5. Create an account site using account_id you get from step 2 and party_site_id from step 4.

HZ_CUST_ACCOUNT_SITE_V2PUB.CREATE_CUST_ACCT_SITE()

HZ_CUST_ACCOUNT_SITE_V2PUB.CUST_ACCT_SITE_REC_TYPE

6. Create an account site use using cust_acct_site_id you get from step 5 ans site_use_code = ‘BILL_TO’.

HZ_CUST_ACCOUNT_SITE_V2PUB.CREATE_CUST_SITE_USE()

HZ_CUST_ACCOUNT_SITE_V2PUB.CUST_SITE_USE_REC_TYPE

HZ_CUSTOMER_PROFILE_V2PUB.CUSTOMER_PROFILE_REC_TYPE

Base table:

HZ_PARTIES

HZ_PARTY_SITES

HZ_LOCATIONS

HZ_CUST_ACCOUNTS

HZ_CUST_SITE_USES_ALL

HZ_CUST_ACCT_SITES_ALL

HZ_PARTY_SITE_USES

Validations:

Check if legacy values fetched are valid.

Check if customer address site is already created.

Check if customer site use is already created.

Check is customer header is already created.

Check whether the ship_to_site has associated bill_to_site

Check whether associated bill_to_site is created or not.

Profile amounts validation:

Validate cust_account_id, validate customer status.

Check if the location already exists in HZ_LOCATIONS. If does not exist, create new location.