API to create Customers

We can create or insert a new customer data usingthis API.

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  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  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 from step 5 and 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

Check Validations:

• legacy values fetched are valid.
• customer address site is already created.
• customer site use is already created.
• customer header is already created.
• whether the ship_to_site has associated bill_to_site
• 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.

Read More »

Customer conversion

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.

Important Columns:

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

Read More »

Item import (Item conversion)

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
• Load child record if no error found.

Important Columns:

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 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.

Read More »

Order Import Interface (Sales Order Conversion)

Order Import Interface (Sales Order Conversion)

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

Prerequisites:

• 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.

Important Columns:

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

Read More »

Difference between Conversion and Interface in Oracle Apps

Interfaces:

• It is schedule concurrent Process which will be executed multiple times
• We will not be knowing flat file Volume
• We need to handle all the expected exceptions
• Error Reports and sending mail alerts, If any error occurs
• Only in Enhancement or customization projects

Conversion:

• One time data transfer
• We will be know exact falt file Volume
• No need to know the exceptions
• Not required to upload all records
• Only in Implementation,Migration or up-gradation projects

Read More »

Interfaces and Conversions in Oracle Applications

Interfaces and Conversions in Oracle Applications

Overview:

Oracle provides flexible and flexible tools in the form of Interface programs to import the master and transactional data like Customers, Invoices, and Sales Orders etc from external systems into Oracle Applications. This article briefs you about some of the major interface programs.

Conversion/Interface Strategy:

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.

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.

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.

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 with details

Read More »