Customer Conversion and Customer API

Customer Conversion and Customer API

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.

Auto Invoice Interface & receipt API

Auto Invoice Interface & receipt API

Auto Invoice interface

This interface is used to import Customer invoices, Credit memos, Debit memos and On Account credits.

Pre-requisites:

Set of Books

Code combinations

Items

Sales representatives

Customers

Sales Tax rate

Payment Terms

Transaction Types

Freight Carriers

FOB

Batch Sources

Accounting Rules

Interface tables:

RA_INTERFACE_LINES_ALL

RA_INTERFACE_SALESCREDITS

RA_INTERFACE_DISTRIBUTIONS

RA_INTERFACE_ERRORS (details about the failed records)

Base tables:

RA_BATCHES

RA_CUSTOMER_TRX_ALL

RA_CUSTOMER_TRX_LINES_ALL

AR_PAYMENT_SCHEDULES_ALL

RA_CUSTOMER_TRX_LINE_SALESREPS

RA_CUST_TRX_GL_DIST_ALL

RA_CUSTOMER_TRX_TYPES_ALL

Concurrent Program:

Auto invoice master program

Validations:

Check for amount, batch source name, conversion rate, conversion type.

Validate orig_system_bill_customer_id, orig_system_bill_address_id,

Validate if the amount includes tax flag.

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

RA_INTERFACE_LINES_ALL:

AGREEMENT_ID

COMMENTS

CONVERSION_DATE

CONVERSION_RATE

CONVERSION_TYPE

CREDIT_METHOD_FOR_ACCT_RULE

CREDIT_METHOD_FOR_INSTALLMENTS

CURRENCY_CODE

CUSTOMER_BANK_ACCOUNT_ID

CUST_TRX_TYPE_ID

DOCUMENT_NUMBER

DOCUMENT_NUMBER_SEQUENCE_ID

GL_DATE

HEADER_ATTRIBUTE1–15

HEADER_ATTRIBUTE_CATEGORY

INITIAL_CUSTOMER_TRX_ID

INTERNAL_NOTES

INVOICING_RULE_ID

ORIG_SYSTEM_BILL_ADDRESS_ID

ORIG_SYSTEM_BILL_CONTACT_ID

ORIG_SYSTEM_BILL_CUSTOMER_ID

ORIG_SYSTEM_SHIP_ADDRESS_ID

ORIG_SYSTEM_SHIP_CONTACT_ID

ORIG_SYSTEM_SHIP_CUSTOMER_ID

ORIG_SYSTEM_SOLD_CUSTOMER_ID

ORIG_SYSTEM_BATCH_NAME

PAYMENT_SERVER_ORDER_ID

PREVIOUS_CUSTOMER_TRX_ID

PRIMARY_SALESREP_ID

PRINTING_OPTION

PURCHASE_ORDER

PURCHASE_ORDER_DATE

PURCHASE_ORDER_REVISION

REASON_CODE

RECEIPT_METHOD_ID

RELATED_CUSTOMER_TRX_ID

SET_OF_BOOKS_ID

TERM_ID

TERRITORY_ID

TRX_DATE

TRX_NUMBER

§ Receipt API

To bring in Unapplied Receipts and Conversion Receipts for Open Debit items to reduce the balance to the original amount due.

Pre-requisites:

Set of Books

Code combinations

Items

Quick Codes

Sales representatives

Customers

Sales Tax rate

API:

AR_RECEIPT_API_PUB.CREATE_CASH

AR_RECEIPT_API_PUB.CREATE_AND_APPLY

Base tables:

AR_CASH_RECEIPTS

Validations:

Check the currency and the exchange rate type to assign the exchange rate.

Validate bill to the customer.

Get bill to site use id.

Get the customer trx id for this particular transaction number.

Get payment schedule date for the customer trx id.

Create Organization API

Create Organization API

Create Organization

DECLARE

p_organization_rec hz_party_v2pub.organization_rec_type;

x_return_status VARCHAR2 (2000);

x_msg_count NUMBER;

x_msg_data VARCHAR2 (2000);

x_party_id NUMBER;

x_party_number VARCHAR2 (2000);

x_profile_id NUMBER;

BEGIN

p_organization_rec.organization_name := 'erpschools';

p_organization_rec.created_by_module := 'ERPSCHOOLS_DEMO';

hz_party_v2pub.create_organization ('T',

p_organization_rec,

x_return_status,

x_msg_count,

x_msg_data,

x_party_id,

x_party_number,

x_profile_id

);

DBMS_OUTPUT.put_line ('party id ' || x_party_id);

DBMS_OUTPUT.put_line (SUBSTR ('x_return_status = ' || x_return_status,

1,

255

)

);

DBMS_OUTPUT.put_line ('x_msg_count = ' || TO_CHAR (x_msg_count));

DBMS_OUTPUT.put_line (SUBSTR ('x_msg_data = ' || x_msg_data, 1, 255));

IF x_msg_count > 1

THEN

FOR i IN 1 .. x_msg_count

LOOP

DBMS_OUTPUT.put_line

( i

|| '. '

|| SUBSTR

(fnd_msg_pub.get (p_encoded => fnd_api.g_false),

1,

255

)

);

END LOOP;

END IF;

END;

The above API should create records in hz_parties table 

General Ledger Journal Interface

General Ledger Journal Interface

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

Order to Cash Cycle

Order to Cash Cycle

Details of Order to Cash Life Cycle with Table details means joining of all tables in O2C

ORDER MANAGEMENT
Order Import
Menu: Orders, Returns-> Import Orders->Order Import Request
Program: Order Import
Parameters: Order Source
Order Reference
Validate Only 
Instances
Interface tables:  OE_HEADERS_IFACE_ALL
  OE_LINES_IFACE_ALL
OE_PRICE_ADJS_IFACE_ALL
Error Tables:  OE_PROCESSING_MSGS
  OE_PROCESSING_MSGS_TL
Base Tables:  OE_ORDER_HEADERS_ALL
OE_ORDER_LINES_ALL
OE_PRICE_ADJUSTMENTS
Oracle Order Management Interface Tables and Column Descriptions
OE_HEADERS_IFACE_ALL:
This is a multi-org table for sales order headers open interface. This table stores order header information that is imported from a feeder system into Oracle Order Management using Order Import.
Column Description Required/Conditionally Required :-->
ORDER_SOURCE_ID Order source id C
ORIG_SYS_DOCUMENT_REF Original system document reference REQUIRED
ORDER_SOURCE Order source C
OPERATION_CODE Operation code REQUIRED
ORDER_TYPE_ID Order type id C
ORDER_TYPE Order type C
RETURN_REASON_CODE Return reason code Required for returns only
SALESREP_ID Salesrep id 
PRICE_LIST_ID Price list id Conditionally Required for Booking
PRICE_LIST Price list Conditionally Required for Booking
OE_HEADERS_IFACE_ALL Derived Values
#9632; TRANSACTIONAL_CURR_CODE = FND_CURRENCIES.CURRENCY_CODE
#9632; SOLD_FROM_ORG_ID = HR_ALL_ORGANIZATION_UNITS.ORGANIZATION_ID
#9632; ACCOUNTING_RULE_ID = RA_RULES.RULE_ID
#9632; INVOICING_RULE_ID = RA_RULES.RULE_ID
#9632; SALESREP_ID = RA_SALESREPS_ALL.SALESREP_ID
#9632; SALESREP = RA_SALESREPS_ALL.NAME
#9632; PAYMENT_TERM_ID = RA_TERMS_B.TERM_ID
#9632; CUSTOMER_PAYMENT_TERM_ID = RA_TERMS_B.TERM_ID
#9632; PAYMENT_TERM = RA_TERMS_TL.NAME
#9632; CUSTOMER_PAYMENT_TERM = RA_TERMS_TL.NAME
#9632; AGREEMENT_ID = OE_AGREEMENTS_B.AGREEMENT_ID
#9632; ORDER_SOURCE_ID = OE_ORDER_SOURCES.ORDER_SOURCE_ID
#9632; HEADER_ID = OE_ORDER_HEADERS_ALL.HEADER_ID
#9632; PRICE_LIST_ID = QP_LIST_HEADERS_TL.LIST_HEADER_ID
#9632; PRICE_LIST = QP_LIST_HEADERS_TL.NAME
OE_LINES_IFACE_ALL:
This is a multi-org table for sales order lines open interface. This table stores order lines information that is imported from a feeder system into Oracle Order Management using Order Import.
Column Description Required/Conditionally Required:-->
ORDER_SOURCE_ID Order source id REQUIRED
ORIG_SYS_DOCUMENT_REF Original system document reference REQUIRED
ORIG_SYS_LINE_REF Original system line reference REQUIRED
ORIG_SYS_SHIPMENT_REF Original system shipment reference REQUIRED
INVENTORY_ITEM Inventory Item REQUIRED
INVENTORY_ITEM_ID Inventory Item ID C
TOP_MODEL_LINE_REF Top model line reference C
LINK_TO_LINE_REF Link to line reference C
REQUEST_DATE Request Date REQUIRED
DELIVERY_LEAD_TIME Delivery lead time REQUIRED
DELIVERY_ID Delivery id REQUIRED
ORDERED_QUANTITY Ordered quantity REQUIRED
ORDER_QUANTITY_UOM Order quantity uom REQUIRED
SHIPPING_QUANTITY Quantity which has been shipped by Shipping in Shipping UOM. C
SHIPPING_QUANTITY_ UOM The UOM for Shipping Quantity C
SHIPPED_QUANTITY Shipped quantity C
CANCELLED_QUANTITY
FULFILLED_QUANTITY The fulfilled quantity for the line C
PRICING_QUANTITY Pricing quantity C
PRICING_QUANTITY_ UOM Pricing quantity uom C
OE_LINES_IFACE_ALL Derived Values
#9632; AGREEMENT_ID = OE_AGREEMENTS_TL.AGREEMENT_ID
#9632; SHIP_FROM_ORG_ID = HR_ALL_ORGANIZATION_UNITS.ORGANIZATION_ID
#9632; SOLD_FROM_ORG_ID = HR_ALL_ORGANIZATION_UNITS.ORGANIZATION_ID
#9632; ACCOUNTING_RULE_ID = RA_RULES.RULE_ID
#9632; INVOICING_RULE_ID = RA_RULES.RULE_ID
#9632; SALESREP_ID = RA_SALESREPS_ALL.SALESREP_ID
#9632; PRICE_LIST_ID = QP_LIST_HEADERS_TL.LIST_HEADER_ID
#9632; PAYMENT_TERM_ID = RA_TERMS_B.TERM_ID
#9632; CUSTOMER_PAYMENT_TERM_ID = RA_TERMS_B.TERM_ID
#9632; SALESREP = RA_SALESREPS_ALL.NAME
#9632; CUSTOMER_PAYMENT_TERM = RA_TERMS_TL.NAME
#9632; PAYMENT_TERM = RA_TERMS_TL.NAME
#9632; PRICE_LIST = QP_LIST_HEADERS_TL.NAME
#9632; ORDER_SOURCE_ID = OE_ORDER_SOURCES.ORDER_SOURCE_ID
#9632; ORIG_SYS_DOCUMENT_REF = OE_HEADERS_IFACE_ALL.ORIG_SYS_DOCUMENT_REF
#9632; LINK_TO_LINE_REF = OE_LINES_IFACE_ALL.ORIG_SYS_LINE_REF
#9632; TOP_MODEL_LINE_REF = OE_LINES_IFACE_ALL.ORIG_SYS_LINE_REF
API: OE_ORDER_PUB
Public Procedures
Process Order API provides the following public procedures for operations and queries on the entities for the sales order object.
#9632; OE_ORDER_PUB.Process_Order
#9632; This is the main procedure used to create, update and delete the entities on the sales order. This procedure can also be used to perform other actions via the records or explicit action requests. Please refer to the next section on the details of the processing.
#9632; OE_ORDER_PUB.Get_Order
This procedure queries and returns all records belonging to a single sales order object.
#9632; OE_ORDER_PUB.Lock_Order
Locks the entities of a sales order object. Use the get_order procedure to retrieve the entity records and then call lock_order with the records to be locked.

Tips Related to Order Import:
Manual and Automatic Pricing
You can indicate whether you want to manually enter prices for imported orders or allow Order Management to automatically price the order. You can use automatic pricing or manual pricing for your imported orders.
#9632; If you want to use automatic pricing, you should set the column OE_LINES_ INTERFACE.CALCULATE_PRICE_ FLAG to Calculate Price, and define all your pricing setup including discounts, promotions, surcharges, free goods, etc. in Oracle Pricing and Order Management.
#9632; If you want to use the manual pricing, you should set the column OE_ LINES_ INTERFACE.CALCULATE_PRICE_FLAG to Freeze Price. In this case, you should define all your discounts as line level, overridable, and not automatic.
#9632; If you want the system to only calculate Freight Charges, set the column OE_ LINES_INTERFACE.CALCULATE_PRICE_FLAG to Partial Price.
Note: Order Import does not support the importing of free goods, promotions, and other item discounts for manual pricing. Additionally, if the value of OE_LINES_IFACE_ALL is null, Order Management will set the value of this column to Y during import processing.
Lookup Type: CALCULATE_PRICE_FLAG
Code 
Meaning 
Description
N Freeze Price Freeze Price
P Partial Price Price certain Phases
Y Calculate Price Calculate Price
Overview of Order Management in the Order to Cash Process:-->
Order Management receives detailed item information from the Inventory application and price list information from the Pricing application. Orders and returns can be entered manually or imported through an EDI, CRM, or external source. Once in the system the order is ready to be pick released and shipped, if needed by the Shipping application. It can then proceed through the AutoInvoice program into the Oracle Receivables application to be invoiced. All of the accounting information is transferred to the General Ledger by the Inventory and Receivables applications
Sales Order Life Cycle
• Order Creation
• Order Booking
• Pick Release
• Ship Confirm
Order Creation: While creating Order it effects following two tables 
• OE_ORDER_HEADERS_ALL
• OE_ORDER_LINES_ALL
 After entering the order the status of the order and items in line level will be Entered.
Order Booking: After booking the Order You will see two changes in the Line level

Status of that Item will become Awaiting Shipping from Entered. Also you can see date is appearing in the Schedule Ship date column.After Booking the Order Following Two Tables also comes in the picture:

• WSH_DELIVERY_DETAILS
• WSH_DELIVERY_ASSIGNMENTS
Here Released_status of WSH_DELIVERY_DETAILS is ‘R’.Also the status of Order will become Booked from Entered
Main Column gets inserted in WSH_DELIVERY_DETAILS
SOURCE_HEADER_NUMBER
SOURCE_HEADER_TYPE_NAME
CUSTOMER_ID
INVENTORY_ITEM_ID
ITEM_DESCRIPTION
REQUESTED_QUANTITY
SUBINVENTORY
DATE_REQUESTED
DATE_SCHEDULED
UNIT_PRICE
CURRENCY_CODE
Main Column gets inserted in WSH_DELIVERY_ASSIGNMENTS
DELIVERY_ASSIGNMENT_ID
DELIVERY_DETAIL_ID 
CREATED_BY
LAST_UPDATED_BY
LAST_UPDATE_LOGIN
Pick Release: In pick release we reserve the item in the Inventory against that particular order.
In This process one new table comes in the pictureWSH_NEW_DELIVERIES.
Here it generates delivery_id against that particular Order
Here Released_status of WSH_DELIVERY_DETAILS is ‘Y’
Here it inserts one new record in WSH_NEW_DELIVERIS table and updates WSH_DELIVERY_ASSIGNMENTS with delivery_id.If there is no item in the Inventory to reserve then the item will go into the backorder and again we have to pick release the order after increasing the inventory of that particular item.
After Pick Release the status of the line item becomes to the Picked.
Main Column gets inserted in WSH_NEW_DELIVERIES
DELIVERY_ID
NAME
WEIGHT_UOM_CODE
DELIVERY_TYPE
BATCH_ID
SOURCE_HEADER_ID
Ship Confirm:After Ship Confirm the RELEASED_STATUS of WSH_DELIVERY_DETAILS will become ‘C’ and the status of the line item becomes to Shipped.In this if we partially ship the quantity then it will split the OE_ORDER_LINES_ALL and the status will be like shipped for the shipped quantity and awaiting shipping for the rest.After completing ship confirm we have to run Workflow Background Process.
Once Workflow Background Process completes successfully then the status of the line item will become Closed.Once Item gets closed then its ready for the Invoicing. We can run the Auto-invoice from AR Manager for creating invoice.
After running the Workflow Background Process data move from Order tables to RA INTERFACE tables
Record gets inserted into following tables
• RA_INTERFACE_LINES_ALL
• RA_INTERFACE_SALESCREDITS_ALL
Then we have to run
• Create Tax and Freight
• EA Custom Update RMA Transaction Type
• Auto invoice Master Program
Records gets Inserted into following table after running AutoInvoice
• RA_CUSTOMER_TRX_ALL
• RA_CUSTOMER_TRX_LINES
• AR_PAYMENT_SCHEDULES
Join of Shipping Tables:--->
WSH_NEW_DELIVERIES
WSH_DELIVERY_DETAILS
WSH_DELIVERY_ASSIGNMENTS
WSH_DELIVERY_DETAILS
SOURCE_HEADER_ID = OE_ORDER_HEADERS_ALL.HEDAER_ID
SOURCE_HEADER_NUMBER = OE_ORDER_HEADERS_ALL.ORDER_NUMBER
SOURCE_LINE_ID = OE_ORDER_LINES_ALL.LINE_ID
WSH_DELIVERY_ASSIGNMENTS
DELIVERY_DETAIL_ID = WSH_DELIVERY_DETAILS. DELIVERY_DETAIL_ID
WSH_NEW_DELIVERIES
WSH_NEW_DELIVERIES.DELIVERY_ID = WSH_DELIVERY_ASSIGNMENTS.DELIVERY_ID 
OM to AR
Oracle Order Management supports invoice processing at 2 levels:
1. Order Header level Invoicing
The Order Level Invoice Interface workflow activity is part of the Order Header workflow process. It will interface data from the entire order or return to Oracle Receivables at the same time.
2. Order Line level Invoicing
The Order Line level Invoice Interface workflow activity is part of the Order Line workflow process. It will interface data from each line or set of lines as to Oracle Receivables as they become eligible for interface.
Fulfillment in Oracle Order Management
Order Management's fulfillment functionality provides a simple way to synchronize line workflows for multiple order lines. It allows you to prevent invoicing of lines within a fulfillment set until all lines are ready for invoicing.
OE_ORDER_LINES_ALL.FULFILLMENT_METHOD_CODE
Problem Description
Suppose I do shipping of only one line out of three in OM, does invoice gets created (in AR through Autoinvoice) for this one line alone? Or is it that it waits for all the lines to be shipped and then only they get interfaced and then creates invoice? 
Basically, does One pass the lines to interface immediately for this one line or it passes the three lines together to interface. Also, if only one line is present in the interface does it generate the invoice if I run autoinvoice? 
Solution Description: 
It does create the invoice as soon as you ship the line and will also create a invoice. if you don’t want this functionality, either use header level invoicing or attach the required lines which should be invoiced together into a fulfillment set
Invoicing rule:Invoicing rules determine the accounting period in which the receivable amount is recorded.
OE_ORDER_HEADERS_ALL.INVOICING_RULE_ID
OE_ORDER_LINES_ALL.INVOICING_RULE_ID
Note: Lines level takes precedence over header level
Accounting rule:
Accounting rules determine the accounting period(s) in which the revenue distributions for an invoice line are recorded.
OE_ORDER_HEADERS_ALL.ACCOUNTING_RULE_ID
OE_ORDER_LINES_ALL.ACCOUNTING_RULE_ID
Note: Lines level takes precedence over header level
SELECT rule_id, name, description FROM RA_RULES
RULE_ID NAME DESCRIPTION
1 IMMEDIATE Recognize Immediately
-2 ADVANCE INVOICE Invoice in Advance
-3 ARREARS INVOICE Invoice in Arrears
Payment terms:
The due date and discount date for payment of a transaction. For example, the payment term ’2% 10, Net 30’ lets a customer take a two percent discount if payment is received within 10 days; after 10 days, the entire balance is due within 30 days of the invoice date with no applicable
discount.
OE_ORDER_HEADERS_ALL.PAYMENT_TERM_ID
OE_ORDER_LINES_ALL.PAYMENT_TERM_ID
Note: Lines level takes precedence over header level.
SELECT b.term_id, t.name, t.description 
FROM RA_TERMS_B b, RA_TERMS_TL t
WHERE b.term_id = t.term_id
AND t.language = 'US'
Problem Description
On a sales order with two order lines using the same Invoice Rule, there are two separate invoices generated.
In the OE_ORDER_LINES_ALL table, the INVOICING_RULE_ID is the same (-2) for both lines on the sales order.
The problem is that only certain lines are being populated in RA_INTERFACE_LINES_ALL table with the INVOICING_RULE_ID field. 
This causes the lines to end up on separate invoices. Invoice rule is a required grouping attribute. These lines have the same invoice rule in OM and should appear on the same invoice.
In this situation, the same Invoicing Rule was used but, a different Accounting Rule was used 
ie: 
Line 1:
Invoice Rule : Advance Invoice
Accounting Rule : Immediate
and
Line 2:
Invoice Rule: Advance Invoice
Accounting Rule: 12 month revenue
Solution Description
Set up a new accounting rule that has the same properties as IMMEDIATE 
(ACCOUNTING_RULE_ID=1) with a new name (ie: IMMEDIATELY) this will give the 
rule a different ACCOUNTING_RULE_ID. Then Invoice Rule will then get passed to RA_INTERFACE_LINES_ALL and both lines will end up on the same invoice. 
Explanation
When the Accounting Rule set to Immediate, the ACCOUNTING_RULE_ID=1 in the 
table OE_ORDER_LINES_ALL.
When the ACCOUNTING_RULE_ID=1, a value of NULL is interfaced into RA_INTERFACE_LINES_ALL.INVOICING_RULE_ID.
If ACCOUNTING_RULE_ID is <>1, then the invoicing rule is interfaced to 
INTERFACE_LINES_ALL.INVOICING_LINES_ID.
For example, ACCOUNTING_RULE_ID<>1 for "12 month revenue". Therefore the value of -2 from OE_ORDER_LINES_ALL.INVOICING_RULE_ID is interfaced into RA_INTERFACE_LINES_ALL.INVOICING_LINES_ID.
Since INVOICING_RULE_ID is part of mandatory grouping rules in AR, these 2 lines will go into different invoices (You are using rules for one line and no rules for the other line).
Autoinvoice:
Navigation: Interface-> Run Autoinvoice
Program : Autoinvoice Master Program

Interface Table: RA_INTERFACE_LINES_ALL

Error Table: RA_INTERFACE_ERRORS_ALL

Base Tables: RA_BATCHES_ALL 
RA_CUSTOMER_TRX_ALL
RA_CUSTOMER_TRX_LINES_ALL 
OE_ORDER_HEADERS_ALL.ORDER_NUMBER = 
RA_CUSTOMER_TRX_ALL.INTERFACE_HEADER_ATTRIBUTE1
Following are the references passed through AutoInvoice from OM and can be reviewed in AR
through the Transaction Workbench Line Window:
Interface line Attributes populating Order Information:-->

1 Order Number INTERFACE_LINE_ATTRIBUTE1
2 Order Type INTERFACE_LINE_ATTRIBUTE2
3 Delivery INTERFACE_LINE_ATTRIBUTE3
4 Waybill INTERFACE_LINE_ATTRIBUTE4
5 Count INTERFACE_LINE_ATTRIBUTE5
6 Line ID INTERFACE_LINE_ATTRIBUTE6
7 Picking Line ID INTERFACE_LINE_ATTRIBUTE7
8 Bill of Lading INTERFACE_LINE_ATTRIBUTE8
9 Customer Item Part INTERFACE_LINE_ATTRIBUTE9
10 Warehouse INTERFACE_LINE_ATTRIBUTE10
11 Price Adjustment ID INTERFACE_LINE_ATTRIBUTE11
12 Shipment Number INTERFACE_LINE_ATTRIBUTE12
13 Option Number INTERFACE_LINE_ATTRIBUTE13
14 Service Number INTERFACE_LINE_ATTRIBUTE14
Invoice Grouping Rules:

Menu: Navigation > Setup > Transactions > Autoinvoice > Grouping Rule 
Autoinvoice uses grouping rules to group lines to create one transaction. Mandatory grouping attributes cannot be dropped. However, users can add optional grouping attributes. 
Lines which have identical values in the columns for mandatory attributes and the optional attributes, defined in the grouping rule, are grouped into one single transaction. 
Autoinvoice uses the following hierarchy to determine the grouping rule that is to be used: 
• Transaction batch source 
• Customer site level 
• Customer profile level 
• System Options Window 
If a grouping rule is not specified, either, at the Transaction Source or at the Customer Site level or in the Customer Profile Classes window, Autoinvoice uses the default grouping rule specified in the System Options. 
Table : Mandatory and Optional Grouping Attributes 
The table below lists the mandatory attributes and optional attributes that can be used as Grouping rules. 
MANDTORY ATTRIBUTES OPTIONAL ATTRIBUTES 
AGREEMENT_ID ACCOUNTING_RULE_DURATION 
COMMENTS ACCOUNTING_RULE_ID 
CONVERSION_RATE ATTRIBUTE1-15 
CONVERSION_TYPE ATTRIBUTE_CATEGORY 
CONVERSION_DATE INTERFACE_LINE_ATTRIBUTE1-15 
CREDIT_METHOD_FOR_ACCT_RULE INTERFACE_LINE_CONTEXT 
CREDIT_METHOD_FOR_INSTALLMENTS INVENTORY_ITEM_ID 
CURRENCY_CODE REFERENCE_LINE_ID 
CUSTOMER_BANK_ACCOUNT_ID RULE_START_DATE 
CUST_TRX_TYPE_ID SALES_ORDER 
DOCUMENT_NUMBER SALES_ORDER_DATE 
DOCUMENT_NUMBER_SEQUENCE_ID SALES_ORDER_LINE 
GL_DATE SALES_ORDER_REVISION 
HEADER_ATTRIBUTE1-15 SALES_ORDER_SOURCE 
HEADER_ATTRIBUTE_CATEGORY TAX_CODE 
HEADER_GDF_ATTRIBTUE1-15 TAX_RATE
INITIAL_CUSTOMER_TRX_ID 
INTERNAL_NOTES 
INVOICING_RULE_ID 
ORIG_SYSTEM_BILL_ADDRESS_ID 
ORIG_SYSTEM_BILL_CONTACT_ID 
ORIG_SYSTEM_BILL_CUSTOMER_ID 
ORIG_SYSTEM_SHIP_ADDRESS_ID 
ORIG_SYSTEM_SHIP_CONTACT_ID 
ORIG_SYSTEM_SHIP_CUSTOMER_ID 
ORIG_SYSTEM_SOLD_CUSTOMER_ID 
ORIG_SYSTEM_BATCH_NAME 
PREVIOUS_CUSTOMER_TRX_ID 
PRIMARY_SALESREP_ID 
PRINTING_OPTION 
PURCHASE_ORDER 
PURCHASE_ORDER_DATE 
PURCHASE_ORDER_REVISION 
REASON_CODE 
RECEIPT_METHOD_ID 
RELATED_CUSTOMER_TRX_ID 
SET_OF_BOOKS_ID 
TERM_ID 
TERRITORY_ID TRX_DATE 
Internal Sales Order Highlights:
1. In case of Internal sales order, invoice is getting created
2. At line level SOLD_FROM_ORG_ID differs from SHIP_FROM_ORG_ID
Tables are getting populated like below (an example)
OE_ORDER_LINES_ALL:
LINE_ID = 4683555
HEADER_ID = 890179
ORG_ID = 166
SOLD_FROM_ORG_ID = 166
SHIP_FROM_ORG_ID = 890
SOURCE_TYPE_CODE = 'INTERNAL'
RA_CUSTOMER_TRX_ALL:
Interface_header_attribute1 = OOH.Order_number
Interface_header_attribute3 = OOL.SHIP_FROM_ORG_ID
Interface_header_attribute5 = OOL.SOLD_FROM_ORG_ID
Interface_header_attribute6 = OOL.LINE_ID
Interface_header_context = INTERCOMPANY
RA_CUSTOMER_TRX_LINES_ALL
Sales_order = OOH.Order_Number
INTERFACE_LINE_CONTEXT = INTERCOMPANY
INTERFACE_LINE_ATTRIBUTE1 = OOH.Order_Number
WAREHOUSE_ID = OOL.SHIP_FROM_ORG_ID
Questions:

Q. Can a line be added if order is booked?
Ans: Yes
Q: RMA Creation:
Ans: Create an RMA by clicking on the 'New RMA' button and reference the above sales order number
Q: Closed 2nd lines(invoice) when 1st(RMA) is recieved in Mixed Type order?
Example, 1 order, 2 lines. One line is a RMA (WF Line Flow- return form credit with Receipt) and a order line with a expense item (WF Line Flow-generic Bill only). If the Order is booked, the first line will have the status 'Awaiting return' the second line (usage fee) is processed closed directly. This line should be closed (and invoiced only) after the item of the first has been received.
Ans: For above functionality you need to change the workflow ( Customize the Workflow ). 
Q: How To Prevent A Return Material Authorization (RMA ) Against An Unshipped Order.
The Order Entry application allows a user to enter a RMA that references a sales order which has not been shipped to the customer.
Navigation
----------
Under Order Entry -> Orders , Returns -> Orders , Returns
1) Click on the 'New Order' button to enter a sales order for a customer
2) Book the order
3) Create an RMA by clicking on the 'New RMA' button and reference the above sales order number
4) Book the RMA
5) The system will allow you to continue processing the RMA through the entire RMA cycle right through to the RMA interface and receivables interface to credit the customer.
There is no rule which prevents you from raising an RMA for an unshipped sales order and processing it. As soon as a sales order is created and booked, an RMA can be raised against it. The workaround is to have the manual approval step in the RMA cycle. Although it is not part of the seeded data in Release 11, you can create an RMA cycle with the approval step. The approval, by definition is a manual process and will not check the ship status of the order. With an approval step in the RMA order cycle, you can manually verify that the original sales order has been shipped and then approve the RMA to be processed.
Q: Can we create RMA for Internal Sales Order?
Ans: RMA is not possible for Internal Orders. So we create another Internal Order from the Receiving Unit send it back. 
INVENTORY
Overview of Inventory Organizations
Organizations can be established for a number of purposes. For example, you could set up manufacturing facilities, warehouses, distribution centers, and branch offices.The organization must be classified as an inventory organization in order to do transactions, planning, and receiving. You can set as many organizations as needed and limit the organization access by responsibility. A user can change organizations at any time from the Change Organization window.
Overview of Inventory Items
An item can be anything you make, sell, or purchase. It can include various stages of products, such as: final products, supplies for products, or components for products. Inventory items are used in Order Management when placing orders. The item has attributes which control how it is used. For example, one of the Order Management attributes that can be set is if the item is Orderable. If the item is not set correctly it cannot be ordered.Items can be set in one or more than one organization. Normally, you set the item in a master organization and assign it to other organizations that will use it.