E-Mail using PLSQL

CREATE OR REPLACE PROCEDURE plsql_mail_test IS
c UTL_SMTP.connection;
v_sent_date VARCHAR2 (30);
v_dns_name VARCHAR2 (100);
v_sender VARCHAR2 (100) := 'xxxxx@xxxx.com';
v_recepient VARCHAR2 (100) := 'xxxxx@xxxx.com';
v_subject VARCHAR2 (100) := 'TEST';
v_message VARCHAR2 (100) := NULL;

PROCEDURE send_header (
NAME IN VARCHAR2
,header IN VARCHAR2
) AS
BEGIN
UTL_SMTP.write_data (c, NAME
|| ': '
|| header
|| UTL_TCP.crlf);
END;


BEGIN

SELECT SYSDATE
INTO v_sent_date
FROM DUAL;

c := UTL_SMTP.open_connection ('xxx.xxx.xxx.xxx'); --server ip
UTL_SMTP.helo (c, 'XXXXXX'); --server name
UTL_SMTP.mail (c, v_sender);
UTL_SMTP.rcpt (c, v_recepient);
UTL_SMTP.rcpt (c, 'xxxxx@xxxx.com');
UTL_SMTP.open_data (c);
send_header ('From', v_sender);
send_header ('To', v_recepient);
send_header ('Subject', v_subject);
send_header ('Date', v_sent_date);
UTL_SMTP.write_data (c, UTL_TCP.crlf
|| v_message);
UTL_SMTP.close_data (c);
UTL_SMTP.quit (c);

EXCEPTION
WHEN UTL_SMTP.transient_error OR UTL_SMTP.permanent_error THEN
UTL_SMTP.quit (c);
raise_application_error (-20000
, 'Failed to send mail due to the following error: '
|| SQLERRM);

END plsql_mail_test;
/

Useful apps websites

www.erpschools.com

http://www.oracleappshub.com/

http://www.oracle.com/technology/documentation/applications.html

Creating Custom View(Synonym) in R12

In R12, Org Views of 11i are not used to filter data based on operating unit. Instead few synonyms are enabled with 'Security Policy' to filter data for a single operating unit.

'Fine-Grained Access Control' security policy replace CLIENT_INFO(Org Context) of accessing Multi-Org data.

'Security Profile' and calls to MO_GLOBAL package can be used to initialize and obtain access to Operating Unit specific data. All custom forms, reports and packages need to be initialized and make appropriate call to secure access for operating unit specific data.

Metalink reference:

Note:420787.1
Note:414013.1

Example:

CREATE SYNONYM APPS.DPMI_SO_TEST FOR
DPMI_MFG.DPMI_SO_HEADERS_INTERFACE_ALL

begin
dbms_rls.add_policy ('apps',
'DPMI_SO_TEST', -- synonym name
'ORG_SEC', -- use 'ORG_SEC' here
'apps',
'MO_GLOBAL.ORG_SECURITY', -- Standard MO VPD
policy
'SELECT, INSERT, UPDATE, DELETE',
TRUE,
TRUE,
FALSE,
DBMS_RLS.SHARED_CONTEXT_SENSITIVE);
end;

Ship To address and Bill to Address Qry

SELECT hp.party_name
FROM hz_parties hp
,hz_party_sites site
,hz_locations loc
,hz_party_site_uses uses
,fnd_territories_vl terr
,hz_cust_site_uses hcsua
,hz_cust_acct_sites hcasa
WHERE site.party_id = hp.party_id
AND site.location_id = loc.location_id
AND site.party_site_id = uses.party_site_id(+)
AND loc.country = terr.territory_code
AND hp.status = 'A'
AND site.status = 'A'
AND hcsua.status = 'A'
AND uses.site_use_type = 'SHIP_TO'
AND NVL (uses.status, 'A') = 'A'
AND hcsua.cust_acct_site_id = hcasa.cust_acct_site_id
AND hcasa.party_site_id = site.party_site_id
AND hcsua.site_use_code = uses.site_use_type
AND hcsua.site_use_id = ol.ship_to_org_id

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

SELECT hp.party_name
FROM hz_parties hp
,hz_party_sites site
,hz_locations loc
,hz_party_site_uses uses
,fnd_territories_vl terr
,hz_cust_site_uses hcsua
,hz_cust_acct_sites hcasa
WHERE site.party_id = hp.party_id
AND site.location_id = loc.location_id
AND site.party_site_id = uses.party_site_id(+)
AND loc.country = terr.territory_code
AND hp.status = 'A'
AND site.status = 'A'
AND hcsua.status = 'A'
AND uses.site_use_type = 'BILL_TO'
AND NVL (uses.status, 'A') = 'A'
AND hcsua.cust_acct_site_id = hcasa.cust_acct_site_id
AND hcasa.party_site_id = site.party_site_id
AND hcsua.site_use_code = uses.site_use_type
AND hcsua.site_use_id = ol.invoice_to_org_id

ORDER MANAGEMENT BASICS

1) What are the Base Tables and Interface Tables for Order Management?
Interface Tables : OE_HEADERS_IFACE_ALL, OE_LINES_IFACE_ALL
OE_PRICE_ADJS_IFACE_ALL, OE_ACTIONS_IFACE_ALL
OE_CREDITS_IFACE_ALL (Order holds like credit check holds etc)
Base Tables : OE_ORDER_HEADERS_ALL: Order Header Information
OE_ORDER_LINES_ALL: Items Information
OE_PRICE_ADJUSTMENTS: Discounts Information
OE_SALES_CREDITS: Sales Representative Credits.
Shipping Tables :WSH_NEW_DELIVERIES, WSH_DELIVERY_DETAILS, WSH_DELIVERY_ASSIGNMENTS, WSH_DELIVERIES.

2) What is Order Import and What are the Setup's involved in Order Import?
A) Order Import is an open interface that consists of open interface tables and a set of API’s. It imports New, updated, or changed sales orders from other applications such as Legacy systems. Order Import features include validations, Defaulting, Processing Constraints checks, Applying and releasing of order holds, scheduling of shipments, then ultimately inserting, updating or deleting orders from the OM base tables. Order management checks all the data during the import process to ensure its validity with OM. Valid Transactions are then converted into orders with lines, reservations ,price adjustments, and sales credits in the OM base tables.
B) Setups:
· Setup every aspect of order management that we want to use with imported orders, including customers, pricing, items, and bills.
· Define and enable the order import sources using the order import source window.

3) Explain the Order Cycle?
i) Enter the Sales Order
ii) Book the Sales Order(SO will not be processed until booked(Inventory confirmation))
iii) Release sales order(Pickslip Report is generated and Deliveries are created)
(Deliveries – details about the delivery. Belongs to shipping module (wsh_deliveries, wsh_new_deliveries, wsh_delivery_assignments etc) they explain how many items are being shipped and such details.
iv) Transaction Move Order (creates reservations determines the source and transfers the inventory into the staging areas)
v) Launch Pick Release (
vi) Ship Confirm (Shipping Documents(Pickslip report, Performa Invoice, Shipping Lables))

4) Explain the Order to Cash Flow?
I. Enter the Sales Order
II. Book the Sales Order(SO will not be processed until booked(Inventory confirmation))
III. Release sales order(Pickslip Report is generated and Deliveries are created)
(Deliveries – details about the delivery. Belongs to shipping module (wsh_deliveries, wsh_new_deliveries, wsh_delivery_assignments etc) they explain how many items are being shipped and such details.
IV. Transaction Move Order (Selects the serial number of the product which has to be moved/ shipped)
V. Launch Pick Release
VI. Ship Confirm (Shipping Documents(Pickslip report, Performa Invoice, Shipping Lables))
VII. AutoInvoice (Creation of Invoice in Accounts Receivable Module)
VIII. Autolockbox ( Appling Receipts to Invoices In AR)
IX. Transfer to General Ledger ( Populates GL interface tables)
X. Journal Import ( Populates GL base tables)
XI. Posting ( Account Balances Updated).

5. What are the Process Constraints?
A. Process Constraints prevent users from adding updating, deleting, splitting lines and canceling order or return information beyond certain points in the order cycle. Oracle has provided certain process constraints which prevent data integrity violations.
Process constraints are defined for entities and attributes. Entities include regions on the sales order window such as order, line, order price adjustments, line price adjustments, order sales credits and line sales credits. Attributes include individual fields (of a particular entity) such as warehouse, shit to location, or agreement.

6. What are Validation Templates?
A) Validation Templates are used to define the validation conditions in process constraints. A validation template names a conditions and defines the semantic of how to validate that condition. These are used in processing constraints framework to specify the constraining conditions for a given constraint. These conditions are based on
Where the entity is in its work flow.
The state of attributes on an entity.
Any other validation condition that cannot be modeled using the above condition.

7. What are different types of Holds?
· GSA(General Services Administration) Violation Hold(Ensures that specific customers always get better pricing for example Govt. Customers)
· Credit Checking Hold( Used for credit checking feature Ex: Credit Limit)
· Configurator Validation Hold ( Cause: If we invalidate a configuration after booking)

8. What is Document Sequence?
A) Document sequence is defined to automatically generate numbers for your orders or returns as you enter them. Single / multiple document sequences can be defined for different order types.
Document sequences can be defined as three types Automatic (Does not ensure that the numbers are contiguous), Gapless (Ensures that the numbering is contiguous), Manual Numbering. Order Management validates that the number specified is unique for order type.

9. What are Defaulting Rules?
A) A defaulting rule is a value that OM automatically places in an order field of the sales order window. Defaulting rules reduce the amount of information one must enter. A defaulting rule is a collection of defaulting sources for objects and their attributes.
It involves the following steps
· Defaulting Conditions - Conditions for Defaulting
· Sequence – Priority for search
· Source – Entity ,Attribute, Value
· Defaulting source/Value

10. When an order cannot be cancelled?
A) An order cannot be cancelled if,
· It has been closed
· It has already been cancelled
· A work order is open for an ATO line
· Any part of the line has been shipped or invoiced
· Any return line has been returned or credited.

11. When an order cannot be deleted?
A) you cannot delete an order line until there is a need for recording reason.

12. What is order type?
A) An order type is the classification of order. It controls the order work flow activity, order number sequence, credit check point and transaction type. Order Type is associated to a work flow process which drives the processing of the order.

13. What are primary and secondary price lists?
A) Every order is associated to a price list as each item on the order ought to have a price. A price list is contains basic list information and one or more pricing lines, pricing attributes, qualifiers, and secondary price lists. The price list that is primarily associated to an order is termed as Primary price list.
The pricing engine uses a Secondary Price list if it cannot determine the price of the item ordered in the Primary price list.

14. What is pick slip? Types?
A) It is an internal shipping document that pickers use to locate items to ship for an order.
· Standard Pick Slip – Each order will have its own pick slip with in each picking batch.
· Consolidated Pickslip – Pick slip will have all the orders released in the each picking batch.

15. What is packing slip?
A) It is an external shipping document that accompanies the shipment itemizing the contents of the shipment.

16. What are picking rules?
A) Picking rules define the sources and prioritization of sub inventories, lots, revisions and locators when the item is pick released by order management. They are user defined set of rules to define the priorities order management must use when picking items from finished goods inventory to ship to a customer.

17. Where do you find the order status column?
A) In the base tables, Order Status is maintained both at the header and line level. The field that maintains the Order status is FLOW_STATUS_CODE. This field is available in both the OE_ORDER_HEADERS_ALL and OE_ORDER_LINES_ALL.

18. When the order import program is run it validates and the errors occurred can be seen in?
A) Responsibility: Order Management Super User
Navigation: Order, Returns > Import Orders > Corrections

Changing Record group for LOV using CUSTOM.PLL

Definition: vrg_id recordgroup;

1) vrg_id := Find_Group( "tesT_new_record_grp" ); ...Generate a group id for the record group ur creating

--Delete group id

2)

IF NOT Id_Null(vrg_id) THEN

Delete_Group( vrg_id);

END IF;

3) Now create a SQL for new record group which will be attached to LOV. But creation of new record group has its limitations, this process allows to change the tables and WHERE conditions used to generate the LOV, it won't allow to change the number of columns or their alias.Because here we are changing only record group , LOV cannot be modified in CUSTOM.pll

So make sure number of columns in select statement and their aliases match actual record group

Example:

Actual record group:

select col1, col2, col3

from tbl1,tbl2

where tbl1.x = tbl2.y

New record group:

vlv_sql = 'select tbl1111.123 col1,'

|| 'tbl1111.234 col2,'

|| 'tbl1111.xyz col3'

|| 'from tbl1111,tbl22222'

|| 'where tbl1111.xx = tbl22222.yy ';

(only tables and where condition are changed)

4) vrg_id := CREATE_GROUP_FROM_QUERY (vrg_name_new, vlv_sql);

--creating the new record group

5) SET_LOV_PROPERTY ('XXXXX' --LOV name

,group_name

,vrg_name_new

);

--Assign the newly created record group to existing LOV

Internal orders

Internal orders normally start with creation of requisitions in Oracle Purchasing (which is created as Internal Sales Orders) .Once the requisitions created from the MRP and Inventory modules, the Requisition Import program must be run in order to move records from the requisition interface tables to the historical PO_REQUISITION_HEADERS_ALL, PO_REQUISITION_LINES_ALL and PO_REQ_DISTRIBUTIONS_ALL.

Now next is to create internal requisitions from Purchasing module.When each requisition is approved, data will be inserted into the MTL_SUPPLY table. When the items on the requisition are stocked in inventory, the supply data can be viewed from the inventory form called Item Supply/Demand.

Once the Internal Requisition is approved, a user should run Create Internal Sales Orders (from a Purchasing responsibility) to load the internal sales orders interface tables called in Order Entry.

After the Creation of Internal Sales Orders has completed, a Import Orders concurrent program need to run from an Order Management. at this stage, Oracle order Workflow will process the internal sales order using the workflow definition listed for the Transaction Type definition in Order Management. When the workflow completes the Shipping Network is checked to see if the Transfer Type between the source and destination organization is In transit or Direct.

You can use Oracle Order Entry/Shipping to define order cycles, approvals and holds.Internal Requisitions use the Account Generator, which automatically builds account distributions. You can specify only one accounting distribution per inventory sourced line.

This internal requisition process provides the features needed to define your inter- organization shipping network. For transfers between two organizations, you can specify whether to use in transit or direct shipments. You can also require internal requisitions for transfers between specific organization.

DYNAMIC CURSOR

Sample code to create where condition dynamically in a function , which can be used as dynamic cursors.

CREATE OR REPLACE PROCEDURE apps.check_refcur (

where_in IN VARCHAR2 := NULL

) IS

TYPE cv_type IS REF CURSOR;

CV cv_type;

v_inventory_item_id NUMBER;

v_segment1 VARCHAR2 (100);

l_v_sql_string VARCHAR2 (2000);

BEGIN

l_v_sql_string :=

'SELECT msik.inventory_item_id

, msik.segment1

FROM mtl_system_items_kfv msik

WHERE 1 = 1

AND msik.organization_id = 83

AND msik.inventory_item_id IN ( '

|| where_in

|| ')';

OPEN CV FOR l_v_sql_string;

LOOP

FETCH CV

INTO v_inventory_item_id

,v_segment1;

EXIT WHEN CV%NOTFOUND;

END LOOP;

COMMIT;

EXCEPTION

WHEN OTHERS THEN

INSERT INTO TEST

VALUES ( 'Exception'

|| SYSDATE

);

COMMIT;

END;

/

To import a routing with operations and resource

To import a routing with operations and resources, you need to populate the following tables:

BOM_OP_ROUTINGS_INTERFACE

BOM_OP_SEQUENCES_INTERFACE

BOM_OP_RESOURCES_INTERFACE

With these three tables, you can create routing header information and assign

operation and resource details.

PROCESS_FLAG

The column PROCESS_FLAG indicates the current state of processing for a row in the interface table. All inserted rows must have the PROCESS_FLAG set to 1.

• 1 - Pending
• 2 - Assigned Succeeded
• 3 - Assign/Validation Failed
• 4 - Validation Succeeded
• 7 - Import Succeeded

BOM_OP_ROUTINGS_INTERFACE TABLE.

a. REQUIRED COLUMNS FOR THE BOM_OP_ROUTINGS_INTERFACE TABLE.

You must always enter values for the following required columns when

you insert rows into the BOM_OP_ROUTINGS_INTERFACE table.

PROCESS_FLAG

ASSEMBLY_ITEM_ID

ORGANIZATION_ID

ROUTING_TYPE

If you are creating an alternate routing, you must also enter a value

in the ALTERNATE_ROUTING_DESIGNATOR column.

If the routing you import references a common routing, you must enter a

value in the COMMON_ASSEMBLY_ITEM_ID or the COMMON_ROUTING_SEQUENCE_ID

columns. Routings can only reference common routings that belong to the

same organization. If the routing does not reference a common routing,

the Bill and Routing Interface program defaults the value of the

ROUTING_SEQUENCE_ID for the COMMON_ROUTING_SEQUENCE_ID.

You can specify, in the ROUTING_TYPE column, whether the routing is a

manufacturing routing or an engineering routing. If you do not include

a value for this column, Oracle Bills of Material defaults a value

of 1 (manufacturing), and creates a manufacturing routing. To create

an engineering routing, you must enter a value of 2 (engineering) for

the ROUTING_TYPE column.

For each new row you insert into the BOM_OP_ROUTINGS_INTERFACE table,

you should set the PROCESS_FLAG to 1 (Pending).

b. DERIVED/DEFAULTED VALUES FOR BOM_OP_ROUTINGS_INTERFACE.

The Bill and Routing Interface program derives or defaults most of the

data required to create a manufacturing or engineering routing.

The Bill and Routing Interface program derives or defaults the columns

using the same logic as the Define Routing form or the Define

Engineering Routing form. When you populate a column in the interface

table, the program imports the row with the data you included and does

not default a value.

If you enter a value for the ASSEMBLY_ITEM_NUMBER or COMMON_ITEM_NUMBER

column, you must insert the system item flexfield separator between each

segment of your item number.

INSERT INTO bom_op_routing_interface

(process_flag

,assembly_item_id

,organization_id

,routing_type

,transaction_type

)

VALUES (1

,&inventory_item_id

,&organization_id

,1

,'insert'

);

BOM_OP_SEQUENCES_INTERFACE TABLE.

a. REQUIRED COLUMNS FOR THE BOM_OP_SEQUENCES_INTERFACE TABLE

Each imported record must have a value for the following columns:

PROCESS_FLAG

ROUTING_SEQUENCE_ID

OPERATION_SEQ_NUM

DEPARTMENT_ID

EFFECTIVITY_DATE

You must also specify a value in the ALTERNATE_ROUTING_DESIGNATOR column

if you assign an operation to an alternate routing and have not entered

a value for the ROUTING_SEQUENCE_ID column.

When you insert rows into BOM_OP_SEQUENCES_INTERFACE, you must set the

PROCESS_FLAG to 1 (Pending) for the Bill and Routing Interface program

to process the record.

b. DERIVED/DEFAULTED COLUMN VALUES FOR BOM_OP_SEQUENCES_INTERFACE.

The Bill and Routing Interface program derives or defaults most of the

data required to assign operations to a routing. You can optionally

include a value for derived or defaulted columns, as well as data for

any of the other columns in the interface. The interface program uses

the same logic to derive or default column values in the

BOM_OP_SEQUENCES_INTERFACE table as in the BOM_OP_ROUTINGS_INTERFACE

table. When you populate a column in the interface table, the program

imports the row with the data you included and does not default a

value. However, if you do not enter data in a derived or defaulted

column, the program automatically imports the row with the derived or

defaulted value.

BOM_OP_SEQUENCES_INTERFACE Derived or Defaulted Value

OPERATION_SEQUENCE_ID Sequence BOM_OPERATIONAL_SEQUENCES_S

ROUTING_SEQUENCE_ID From BOM_OP_ROUTINGS_INTERFACE or

BOM_OPERATIONAL_ROUTINGS

LAST_UPDATE_DATE System Date

LAST_UPDATE_BY Userid

CREATION_DATE System Date

CREATED_BY Userid

STANDARD_OPERATION_ID From OPERATION_CODE

DEPARTMENT_ID From DEPARTMENT_CODE

MINIMUM_TRANSFER_QUANTITY 0

COUNT_POINT_TYPE 1

EFFECTIVITY_DATE System Date

BACKFLUSH_FLAG 1

REQUEST_ID From FND_CONCURRENT_REQUESTS

ASSEMBLY_ITEM_ID From ASSEMBLY_ITEM_NUMBER

OPTION_DEPENDENT_FLAG 2

ORGANIZATION_ID From ORGANIZATION_CODE

RESOURCE_ID1 From RESOURCE_CODE1

RESOURCE_ID2 From RESOURCE_CODE2

RESOURCE_ID3 From RESOURCE_CODE3

INSERT INTO bom_op_sequences_interface

(process_flag

,assembly_item_id

,organization_id

,operation_seq_num

,department_id

,effectivity_date

,transaction_type

)

VALUES (1

,&assembly_item_id

,&organization_id

,&operation_seq_num

,&department_id

,&effectivity_date

,'insert'

);

BOM_OP_RESOURCES_INTERFACE TABLE.

a. REQUIRED COLUMNS FOR THE BOM_OP_RESOURCES_INTERFACE TABLE.

You must always enter values for the following required columns when

you insert rows into the BOM_OP_RESOURCES_INTERFACE table:

PROCESS_FLAG

RESOURCE_SEQ_NUM

RESOURCE_ID

OPERATION_SEQUENCE_ID

You must specify a value in the ALTERNATE_ROUTING_DESIGNATOR column if

you assign resources to an alternate routing and have not entered a

value for the ROUTING_SEQUENCE_ID or the OPERATION_SEQUENCE_ID column.

When you insert a row into the BOM_OP_RESOURCES_INTERFACE table, you

must set the PROCESS_FLAG to 1 (Pending) for the Bill and Routing

Interface program to process the record.

b. DERIVED/DEFAULTED COLUMN VALUES FOR BOM_OP_RESOURCES_INTERFACE.

The Bill and Routing Interface program derives or defaults most of the

data required to assign a resource to an operation. You can optionally

include a value for derived or defaulted columns, as well as data for

any of the other columns in the interface. The interface program uses

the same logic to derive or default column values in the

BOM_OP_RESOURCES_INTERFACE table as in the BOM_OP_ROUTINGS_INTERFACE

table and BOM_OP_SEQUENCES_INTERFACE tables. When you populate a

column in the interface table, the program imports the row with the

data you included and does not default a value. However, if you do not

enter data in a derived or defaulted column, the program automatically

imports the row with the derived or defaulted value.

BOM_OP_RESOURCES_INTERFACE Derived or Defaulted Value

OPERATION_SEQUENCE_ID Sequence BOM_OP_SEQUENCES_INTERFACE or

BOM_OPERATION_SEQUENCES_S

RESOURCE_ID From RESOURCE_CODE

ACTIVITY_ID From ACTIVITY

STATDARD_RATE_FLAG From BOM_RESOURCES.STANDARD_RATE_FLAG

ASSIGNED UNITS 1

USAGE_RATE_OR_AMOUNT 1

USAGE_RATE_OR_AMOUNT_INVERSE 1

BASIS_TYPE From BOM_RESOURCES.DEFAULT_BASIS

SCHEDULE_FLAG 2

LAST_UPDATE_DATE System Date

LAST_UPDATED_BY Userid

CREATION_DATE System Date

CREATED_BY Userid

AUTOCHARGE_TYPE From BOM_RESOURCES.AUTOCHARGE_TYPE

REQUEST_ID From FND_CONCURRENT_REQUESTS

ASSEMBLY_ITEM_ID From ASSEMBLY_ITEM_NUMBER

ORGANIZATION_ID From ORGANIZATION_CODE

ROUTING_SEQUENCE_ID From BOM_OP_ROUTINGS_INTERFACE or

BOM_OPERATIONAL_ROUTINGS

INSERT INTO bom_op_resources_interface

(process_flag

,resource_seq_num

,resource_id

,assembly_item_id

,organization_id

,operation_seq_num

,effectivity_date

,transaction_type

)

VALUES (1

,&resource_seq_num

,&resource_id

,&assembly_item_id

,&organization_id

,&operation_seq_num

,&effectivity_date

,'insert'

);

Order To Cash Techinical Overview

Enter order à Book Order à Pick Release à Pick Slip à Confirm Shipment àGenerate Invoice à Customer follow up à Receipt à [Final Reconciliation]

Steps in Cycle:

1. Order Entry

This is first stage, when the order is entered in the system; it creates a record in order headers and Order Lines table.

• Enter header details: Once you enter details on the order header and save it or move it to lines, record goes to one table OE_ORDER_HEADERS_ALL

FLOW_STATUS_CODE = ENTERED,

BOOKED_FLAG = N),

Primary key=HEADER_ID

No record exists in any other table for this order till now.

• Enter Line details for this order: Enter different item numbers, quantity and other details in line tab. When the record gets saved, it goes to one table. Order header details will be linked with line details by order HEADER_ID.

OE_ORDER_LINES_ALL

FLOW_STATUS_CODE = ENTERED,

BOOKED_FLAG = N,

OPEN_FLAG = Y

Primary key= LINE_ID

2. Order Booking

This is next stage, when Order is booked then the Flow status changed from Entered to Booked.At this stage, these below table get affected.

• OE_ORDER_HEADERS_ALL (FLOW_STATUS_CODE as BOOKED, BOOKED_FLAG updated to Y)
• OE_ORDER_LINES_ALL (FLOW_STATUS_CODE as AWAITING_SHIPPING, BOOKED_FLAGupdated Y)
• WSH_DELIVERY_DETAILS (DELIVERY_DETAIL_ID is assigned here, RELEASED_STATUS ‘R’ ready to release, LINE_ID comes as SOURCE_LINE_ID)
• WSH_DELIVERY_ASSIGNMENTS (DELIVERY_ASSIGNMENT_ID is assigned for DELIVERY_DETAIL_ID present in WSH_DELIVERY_DETAILS, DELIVERY_ID remains blank till this stage)

In shipping transaction form order status remains "Ready to Release".

At the same time, Demand interface program runs in background And insert into inventory tables MTL_DEMAND, here LINE_ID come as a reference in DEMAND_SOURCE_LINE

3. Reservation

This step is required for doing reservations SCHEDULE ORDER PROGRAM runs in the background and quantities are reserved. Once these programs get successfully get completed.

• MTL_DEMAND andMTL_RESERVATIONS table get updated. LINE_ID gets updated in DEMAND_SOURCE_LINE_ID in both the tables.

4. Pick Release

Pick Release is the process of putting reservation on on-hand quantity available in the inventory and pick them for particular sales order.

Pick release can be done from 'Release Sales Order' form or 'Pick release SRS' program can be scheduled in background. In both of these cases all lines of the order gets pick released depending on the Picking rule used. If specific line/s needs to be pick release it can be done from 'Shipping Transaction form. For this case Pick Release is done from 'Release Sales Order' form with Pick Confirm=NO.
Once pick release is done these are the tables get affected:

· If step 3 is not done then MTL_RESERVATIONS gets updated now.

· WSH_NEW_DELIVERIES (one record gets inserted with SOURCE_HEADER_ID= order header ID, STATUS_CODE=OP =>open)

· WSH_DELIVERY_ASSIGNMENTS (DELIVERY_ID gets assigned which comes from WSH_NEW_DELIVERIES)

· WSH_DELIVERY_DETAILS (RELEASED_STATUS ‘S’ ‘submitted for release’)

· MTL_TXN_REQUEST_HEADERS

· MTL_TXN_REQUEST_LINES (LINE_ID goes as TXN_SOURCE_LINE_ID)

· (move order tables. Here request is generated to move item from Source (RM or FG) sub-inventory to staging sub-inventory)

· MTL_MATERIAL_TRANSACTIONS_TEMP (link to above tables through MOVE_ORDER_HEADER_ID/LINE_ID, this table holds the record temporally)

· MTL_SERIAL_NUMBERS_TEMP (if item is serial controlled at receipt then record goes in this table)

· MTL_SERIAL_NUMBERS (enter value in GROUP_MARK_ID )

*In shipping transaction form order status remains "Released to Warehouse" and all the material still remains in source sub-inventory. We need to do Move Order Transaction for this order. Till this no material transaction has been posted to MTL_MATERIAL_TRANSACTIONS

5. Pick Confirm/ Move Order Transaction

Items are transferred from source sub-inventory to staging Sub-inventory. Here material transaction occurs.

Order line status becomes 'Picked' on Sales Order and 'Staged/Pick Confirmed' on Shipping Transaction Form.

• MTL_MATERIAL_TRANSACTIONS_TEMP (Record gets deleted from here and gets posted to MTL_MATERIAL_TRANSACTIONS)
• OE_ORDER_LINES_ALL (FLOW_STATUS_CODE ‘PICKED’ )
• MTL_MATERIAL_TRANSACTIONS (LINE_ID goes as TXN_SOURCE_LINE_ID)
• MTL_TRANSACTION_ACCOUNTS
• WSH_DELIVERY_DETAILS (RELEASED_STATUS becomes ‘Y’ => ‘Released’ )
• WSH_DELIVERY_ASSIGNMENTS
• MTL_ONHAND_QUANTITIES
• MTL_SERIAL_NUMBERS_TEMP (record gets inserted after putting details for the item which are serial controlled at 'Sales order issue')
• MTL_SERIAL_NUMBERS (record gets inserted after putting details for the item which are serial controlled at 'Sales order issue')

* This step can be eliminated if we set Pick Confirm=YES at the time of Pick Release

6. Ship Confirm

Here is the ship confirm interface program runs in background. Data removed from

• WSH_NEW_DELIVERIES. The items on the delivery shipped to customer at this stage
• OE_ORDER_LINES_ALL (FLOW_STATUS_CODE ‘shipped’)
• WSH_DELIVERY_DETAILS (RELEASED_STATUS ‘C’ ‘Shipped’, SERIAL_NUMBER if quantity is ONE)
• WSH_SERIAL_NUMBERS (records gets inserted with the DELIVERY_DETAIL_ID reference, only in case of shipped quantity is two or more)
• MTL_TRANSACTION_INTERFACE
• MTL_MATERIAL_TRANSACTIONS (linked through Transaction source header id)
• MTL_TRANSACTION_ACCOUNTS
• Data deleted from MTL_DEMAND, MTL_RESERVATIONS
• Item deducted from MTL_ONHAND_QUANTITIES
• MTL_SERIAL_NUMBERS_TEMP (records gets deleted from this table)
• MTL_SERIAL_NUMBERS (Serial number stauts gets updated CURRENT_STATUS=4 , 'Issued out of store')

7. Enter Invoice

After shipping the order, order lines are eligible to transfer to RA_INTERFACE_LINES_ALL. Workflow background engine picks those records and post it to RA_INTERFACE_LINES_ALL. This is also called Receivable interface, this mean information moved to accounting area for invoicing details. Invoicing workflow activity transfers shipped item information to Oracle Receivables. At the same time records also goes in the table RA_INTERFACE_SALESCREDITS_ALL which hold details of sales credit for the particular order

• RA_INTERFACE_LINES_ALL (interface table into which the data is transferred from order management) Then Autoinvoice program imports data from this table which get affected into this stage are receivables base table. At the same time records goes in
• RA_CUSTOMER_TRX_ALL (CUST_TRX_ID is primary key to link it to TRX_LINES table and TRX_NUMBER is the invoice number)
• RA_CUSTOMER_TRX_LINES_ALL (LINE_ATTRIBUTE_1 and LINE_ATTRIBUTE_6 are linked to order number and LINE_ID of the orders)

8. Complete Line

In this stage order line level table get updated with Flow status and open flag.

• OE_ORDER_LINES_ALL (FLOW_STATUS_CODE ‘shipped’, OPEN_FLAG “N”)

9. Close Order

This is last step of Order Processing. In this stage only OE_ORDER_LINES_ALL table get updated. These are the table get affected in this step.

OE_ORDER_LINES_ALL (FLOW_STATUS_CODE ‘closed’, OPEN_FLAG “N”)