1. Order Entry
Step 1 Enter header detail, Once after entering the header details on the order header tab , click on the save button or move to lines tab.
The order header gets saved and a record will insert into
OE_ORDER_HEADERS_ALL with FLOW_STATUS_CODE as ENTERED,
OPEN_FLAG as Y and BOOKED_FLAG as N . Header is the primary key in OE_ORDER_HEADERS_ALL
In the Lines tab of the order Enter item name / number, quantity and other details in line tab.
Click on the Save button. After saving the line, the line data get inserts into OE_ORDER_LINES_ALL with FLOW_STATUS_CODE = ENTERED,
BOOKED_FLAG = N, OPEN_FLAG = Y and also records will be inserted into OE_SETS with ship set details of the order.
Primary key is the LINE_ID in OE_ORDER_LINES_ALL and the HEADER_ID is the join between OE_ORDER_HEADERS_ALL and OE_ORDER_LINES_ALL.
SHIP_SET_ID is the join between OE_ORDER_LINES_ALL(SHIP_SET_ID) and OE_SETS(SET_ID).
SET_ID is the primary column in OE_SETS.
2. Order Booking
Step 2 , when Order is booked then the Flow status changed from Entered to Booked. At this stage, these below table are updated.
· OE_ORDER_HEADERS_ALL (FLOW_STATUS_CODE = BOOKED, BOOKED_FLAG updated to Y)
· OE_ORDER_LINES_ALL (FLOW_STATUS_CODE as AWAITING_SHIPPING, BOOKED_FLAG updated 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 this program get successfully get completed, the MTL_DEMAND and MTL_RESERVATIONS table get updated.
LINE_ID gets updated in DEMAND_SOURCE_LINE_ID in both the tables.
4. Pick Release
Pick release Order Tab,
Inventory Tab,
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)
· Order to Cash - Technical Flow in R12‘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
Pick Release process in turn will kick off several other requests like Pick Slip Report,Shipping Exception Report and Auto Pack Report
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 TRX_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 ship confirm interface program runs in background. Data removed from WSH_NEW_DELIVERIES.
The items on the delivery gets 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 lines is eligible for Invoice. The Workflow background engine picks the Invoice eligible records and post it to the below interface tables.
RA_INTERFACE_LINES_ALL
RA_INTERFACE_SALESCREDITS_ALL
RA_INTERFACE_DISTRIBUTIONS_ALL
RA_INTERFACE_LINES_ALL -- Sale order and order line information.
RA_INTERFACE_SALESCREDITS_ALL --This table contains all sales credit information for the sales order
RA_INTERFACE_DISTRIBUTIONS_ALL --This table contains accounting distributions to be used by the transactions defined in RA_INTERFACE_LINES_ALL.
Then Autoinvoice concurrent program imports data from the above interface tables to the RA_CUSTOMER_TRX_ALL
TRX_NUMBER is the invoice number
CUST_TRX_ID is the key to link to TRX_LINES table
IN RA_CUSTOMER_TRX_LINES_ALL
LINE_ATTRIBUTE_1 stores the order number
LINE_ATTRIBUTE_6 stores teh LINE_ID of the order
Ship Confirm will kick off concurrent programs like INTERFACE TRIP Stop, Commercial Invoice, Packing Slip Report, Bill of Lading
8.Complete Line
After pushing the data to AR interface tables the workflow back ground engine updates the lines flow status and the open flag
OE_ORDER_LINES_ALL (FLOW_STATUS_CODE ‘CLOSED’, OPEN_FLAG “N”)
9. Close Order
Once all the lines of the sales order is closed then at the last day of the month the workflow back ground engine
update the flow status code of the order to Closed and open flag to N.
10. Create receipt:
Navigation:
Receivables> Receipts> Receipts
Underlying tables:
AR_CASH_RECEIPTS_ALL
11. Transfer to General Ledger:
To transfer the Receivables accounting information to general ledger, run General Ledger Transfer Program.
Navigation:
Receivables> View Requests
Parameters:
· Give in the Start date and Post through date to specify the date range of the transactions to be transferred.
· Specify the GL Posted Date, defaults to SYSDATE.
· Post in summary: This controls how Receivables creates journal entries for your transactions in the interface table. If you select ‘No’, then the General Ledger Interface program creates at least one journal entry in the interface table for each transaction in your posting submission. If you select ‘Yes’, then the program creates one journal entry for each general ledger account.
· If the Parameter Run Journal Import is set to ‘Yes’, the journal import program is kicked off automatically which transfers journal entries from the interface table to General Ledger, otherwise follow the topic Journal Import to import the journals to General Ledger manually.
Underlying tables:
This transfers data about your adjustments, chargeback, credit memos, commitments, debit memos, invoices, and receipts to the GL_INTERFACE table.
12. Journal Import:
To transfer the data from General Ledger Interface table to General Ledger, run the Journal Import program from Oracle General Ledger.
Navigation:
General Ledger > Journal> Import> Run
Parameters:
· Select the appropriate Source.
· Enter one of the following Selection Criteria:
No Group ID: To import all data for that source that has no group ID. Use this option if you specified a NULL group ID for this source.
All Group IDs: To import all data for that source that has a group ID.
Use this option to import multiple journal batches for the same source with varying group IDs.
Specific Group ID: To import data for a specific source/group ID combination. Choose a specific group ID from the List of Values for the Specific Value field.
If you do not specify a Group ID, General Ledger imports all data from the specified journal entry source, where the Group_ID is null.
· Define the Journal Import Run Options (optional)
Choose Post Errors to Suspense if you have suspense posting enabled for your set of books to post the difference resulting
from any unbalanced journals to your suspense account.
Choose Create Summary Journals to have journal import create the following:
• one journal line for all transactions that share the same account, period, and currency and that has a debit balance
• one journal line for all transactions that share the same account, period, and currency and that has a credit balance.
· Enter a Date Range to have General Ledger import only journals with accounting dates in that range. If you do not specify a date range, General Ledger imports all journals data.
· Choose whether to Import Descriptive Flexfields, and whether to import them with validation.
Underlying tables:
GL_JE_BATCHES, GL_JE_HEADERS, GL_JE_LINES
13. Posting:
We have to Post journal batches that we have imported previously to update the account balances in General Ledger.
Navigation:
General Ledger> Journals > Enter
Underlying tables:
GL_BALANCES.
Following is an important Query which gives the joining between OM , WSH , AR
SELECT ooh.order_number
,ool.line_id
,ool.ordered_quantity
,ool.shipped_quantity
,ool.invoiced_quantity
,wdd.delivery_detail_id
,wnd.delivery_id
,rctl.interface_line_attribute1
,rctl.interface_line_attribute3
,rctl.interface_line_attribute6
,rct.org_id
,rct.creation_date
,trx_number
,rctl.quantity_ordered
,rct.interface_header_context
FROM oe_order_headers_all ooh
,oe_order_lines_all ool
,wsh_delivery_details wdd
,wsh_new_deliveries wnd
,wsh_delivery_assignments wda
,ra_customer_trx_all rct
,ra_customer_trx_lines_all rctl
WHERE ooh.header_Id = ool.header_id
AND wdd.source_header_id = ooh.header_id
AND wdd.delivery_detail_Id = wda.delivery_detail_id
AND wda.delivery_id = wnd.delivery_id
AND rctl.interface_line_attribute1 = to_char(ooh.order_number)
AND rctl.interface_line_attribute6 = to_char(ool.line_id)
AND rctl.interface_line_attribute3 = to_char(wnd.delivery_id)
AND rctl.customer_trx_id = rct.customer_trx_id
AND rct.interface_header_context = 'ORDER ENTRY'
No comments:
Post a Comment