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”)