add

About Me

My photo
Oracle Apps - Techno Functional consultant

Sunday, August 5

ORDER TO CASH CYCLE

The first and foremost thing before raising a sales order, a customer and the Item must be created.

In order to created a customer, follow below navigation

Order Management Super User Operations (USA)>Customer>Standard
     It then opens a self service page. Search whether the customer already exists as shown in the below screen.


Click on the create button to create if the customer doesn’t exists. Fill the mandatory fields, profile class and account site address as shown in the below screens.




Click ‘Apply’ button to save the details.


Then edit the payment tab of the saved customer to enter the bank details as shown in the below screen

The underlined tables which stores the customer information are
HZ_PARTIES, HZ_LOCATIONS, HZ_PARTY_SITES, HZ_PARTY_SITE_USES.

Another prerequisite is to create an item
Navigation: Inventory > Items > Master Items
Create an item and select item attributes as shown in different tabs in the below screen.

Select the item template type, which specifies the item characteristic and accordingly the attributes are defaulted. So select Tools > copy from

Select the Purchased item as example we are taking the item as Purchased item and press apply button.


Then assign an organization where physically item is stored. For that select tool>Organization Assignment and check the organization you want to select as shown in the below screens.





Important tables which store the item definition and SQL tip as below
select inventory_item_id, segment1, organization_id from apps.mtl_system_items_b
where segment1 = 'Fossil Watch';
NVENTORY_ITEM_ID SEGMENT1 ORGANIZATION_ID
----------------- ---------------------------------------- ---------------
43817                                     Fossil Watch           204
43817                                     Fossil Watch           207
You can get the Organization details form the following
select organization_id, organization_code from apps.mtl_parameters
where organization_id in (204,207);
ORGANIZATION_ID ORG
---------------                 ---
204                                  V1
207                                  M1

You can assign the item to a category as shown in the below screen



Create stock for item using Miscellaneous Receipt

Navigation: Inventory, Vision Operations (USA)>Transactions>Miscellaneous Transaction


And enter the following information:
Type: Miscellaneous receipt
Click on the Transaction Lines button


In the Miscellaneous receipt (M1) form, enter the following information:
Item: Fossil Watch
Sub inventory: FGI
Quantity: 100
Account: Invoke the LOV and enter the Account Alias 'Miscellaneous'
Save changes.

Check the material transaction that was just created.
Navigate to Inventory > Transactions > Material Transactions
Enter the item in the Find Material Transactions screen and click on Find
Check the On-hand quantity for your item. Navigate to Inventory > On-hand, Availability > On-hand Quantity. Enter the item in the Query Material screen and click on Find. Verify that the On-hand quantity exists.

Assign item to Price List:
Navigate to Oracle Pricing Manager > Price Lists > Price List Setup. You could create a new price list, but in this test flow, we will assign your item to an existing price list 'Corporate'.
Query the price list 'Corporate'.

Move the cursor to the List Lines section, and add a new line using Ctrl + Down Arrow on your keyboard, or by going to File > New. Enter the following information:
Product Context: Item
Product Attribute: Item Number
Product Value: Fossil Watch
Value: 100
the rest of the required fields should default in.
Underlying tables are affected:
QP_LIST_HEADERS_ALL_B, QP_LIST_HEADERS_TL
QP_LIST_LINES, QP_PRICING_ATTRIBUTES




Create Order:
We are now ready to create a sales order.

1. Navigate to Order Management Super User, Vision Operations (USA) >Orders, Returns > Sales Orders and make sure the following information is entered.
Order Information tab (Order Header)
Customer Name: NTT DATA Incorporation
Make sure the Ship To/Bill to Locations are defaulted in.
Order Type: Mixed
Price List: Corporate
Salesperson: No Sales Credit

Go to the Line Items tab, and enter the following information.
Line Items tab (Order Lines)
Ordered Item : Fossil Watch
Qty : 20
UOM : Defaults to Ea
Save.
In the Pricing tab, notice that the Price List Corporate has defaulted from the Header, and that the List Price 100 is the price you set up in the price list.
Click Book Order

Now you can see the status of the Sales as ‘Booked’.
Underlying Tables affected: 
In Oracle, Order information is maintained at the header and line level.
The header information is stored in OE_ORDER_HEADERS_ALL and the line information in OE_ORDER_LINES_ALL when the order is entered. The column called FLOW_STATUS_CODE is available in both the headers and lines tables which tell us the status of the order at each stage.
select order_number, header_id, flow_status_code from oe_order_headers_all
where order_number = 66518;
ORDER_NUMBER HEADER_ID FLOW_STATUS_CODE
---------------------       -------------   -      -----------------
66518                     212312                     BOOKED
Finding the line_id and status of the order line based on header_id
select header_id, line_id, line_number, flow_status_code from oe_order_lines_all where header_id = 212312;
HEADER_ID LINE_ID LINE_NUMBER FLOW_STATUS_CODE
---------- ---------- ----------- ------------------------------
212312              408282           1                   AWAITING_SHIPPING

Delivery Details: Delivery details of the items are created in the shipping execution module. It inserts the records in the following tables.
WSH_DELIVERY_DETAILS and WSH_DELIVERY_ASSIGNMENTS
Finding delivery detail information based on the order_line_id
select source_header_number, source_header_id, source_line_id, delivery_detail_id, released_status
from wsh_delivery_details
where source_code = 'OE' and source_line_id =408282;
SOURCE_HEADER_NUMBER SOURCE_HEADER_ID SOURCE_LINE_ID DELIVERY_DETAIL_ID
66518                                          212312                         408282            3979467    
RELEASED_STATUS
R
Where ‘R’ is for Ready to Release
Pick Release:
Navigation:
Shipping > Release Sales Order > Release Sales Orders.
 In order tab, enter the Order number.


Go to the Shipping tab and select Auto create Delivery, Auto Pick Confirm and Auto Pack Delivery fields to ‘YES’
In Inventory tab select Ware house as ‘M1’ and Auto Allocate to YES. Then click on Execute Now button

This runs the Pick Release process, in turn will kick off several other requests like Pick Slip Report,
Shipping Exceptions Report and Auto Pack Report as shown in the following screen.

Underlying Tables affected:
As Auto create Delivery was set to ‘Yes’ a new record is created in the table WSH_NEW_DELIVERIES.
DELIVERY_ID is populated in the table WSH_DELIVERY_ASSIGNMENTS.
The RELEASED_STATUS in WSH_DELIVERY_DETAILS would be now set to ‘Y’ (Pick Confirmed) as Auto Pick Confirm is set to Yes ,otherwise RELEASED_STATUS is ‘S’ (Release to Warehouse).
Finding the delivery associated to the delivery detail and its status
select wnd.delivery_id, wnd.status_code
from wsh_new_deliveries wnd, wsh_delivery_assignments wda, wsh_delivery_details wdd
where wdd.delivery_detail_id = wda.delivery_detail_id
and wda.delivery_id = wnd.delivery_id
and wdd.delivery_detail_id = 3979467;
DELIVERY_ID STATUS
-----------          ---------
3785372              OP
Where ‘OP’ stands for Open and describes Delivery is Open, has not been shipped.

Ship Confirm the Order:
Navigation: 
Order Management Super User>Shipping >Transactions. 
Query with the Order Number.


Notice the following in the Lines/LPNs tab.
Detail: The delivery detail of the shipment
Delivery: The delivery that got auto created during pick release
Line Status: Staged/Pick Confirmed
Next Step: Ship Confirm/Close Trip Stop
Click On Delivery Tab

Click on Ship Confirm. A message pop ups confirming the delivery was successfully confirmed

This will kick off concurrent programs like INTERFACE TRIP Stop, Vehicle Load Sheet Details, Commercial Invoice, Packing Slip Report and Bill of Lading as shown in the below screen.

Underlying tables affected:

RELEASED_STATUS in WSH_DELIVERY_DETAILS would be ‘C’ (Ship Confirmed)
FLOW_STATUS_CODE in OE_ORDER_HEADERS_ALL would beBOOKED
FLOW_STATUS_CODE in OE_ORDER_LINES_ALL would be SHIPPED

Finding delivery details from OM to Inv as below
select delivery_detail_id, released_status, oe_interfaced_flag, inv_interfaced_flag
from wsh_delivery_details
where source_code = 'OE' and source_line_id = 408282;
Output as below
3979467          C         Y         Y

released_status = C (Shipped) -- becomes Shipped after ship confirm, even before ITS
oe_interfaced_flag = Y -- signifies ITS has interfaced shipping data to OM
inv_interfaced_flag = Y -- signifies ITS has interfaced shipping data to INV

As an optional step, you can check if the item has actually been shipped out of inventory.
Navigate to Inventory > Transactions > Material Transactions
Enter the following information in the Find Material Transactions screen and click on Find
Transaction Date : Ensure the transaction date is within the range specified
Source Type : Sales Order
Source :
-Evoke the LOV and click on the button Combinations
-Enter your Sales Order Number and Order Type Mixed, then click OK
-As 66518.Mixed.ORDER ENTRY
Go to the Transaction Type tab, and notice the line with Transaction Type as Sales Order Issue. This is the Inventory Transaction that happened when the item was shipped out of the warehouse M1, hence the negative quantity -20.
The other two lines with Transaction Type = Sales Order Pick, are for the move order transaction that happened automatically during Pick Release , when the item was moved from the pick-from subinventory to the staging subinventory. Hence there is one line each for a negative and positive transaction from/to each subinventory. The final shipment to the customer (sales order issue) is made from the staging subinventory.


Invoice
First check the status of the order line.
Navigate to the sales order form, and query your sales order. Go to the Line Items tab and check that the status of the line is 'Shipped'.
Go to Tools > Workflow Status to verify the workflow. This shows that the order line workflow is now at the Fulfill - Deferred activity with a status of Deferred as shown below. This means that the next steps is to run the Workflow Background Process.

Navigation:
Order Management >view >Requests and run the Workflow Background Process

In addition, notice that the AR concurrent requests Autoinvoice and Autoinvoice Import Program get kicked off automatically to import the invoices into AR. OM inserts invoice data into the AR interface tables through 'Invoice Interface', which runs in the background.AR picks up the data from the interface tables through the 'Autoinvoice' concurrent program.
Tables Effected:
Inserts the records RA_INTERFACE_LINES_ALL with
INTERFACE_LINE_CONTEXT     =     ’ORDER ENTRY’
INTERFACE_LINE_ATTRIBUTE1=     Order_number and INTERFACE_LINE_ATTRIBUTE3=     Delivery_id

And
select * from apps.RA_CUSTOMER_TRX_ALL  where INTERFACE_HEADER_ATTRIBUTE1='66518'
select * from apps.ra_customer_trx_lines_all where INTERFACE_LINE_ATTRIBUTE1='66518'


Select the output to check whether data has successfully imported into receivables tables to create Invoice as shown below

The Invoice created can be seen using the Receivables responsibility
Navigation:
Receivables Super User> Transactions> Transactions 
Query with the Order Number as Reference and Source as ORDER ENTRY

Click the Line Items to view the line information


Create Receipt:
Navigation:
Receivables> Receipts> Receipts

Enter the Receipt method as manual and receipt type as Standard, it doesn’t need to do remittance and clearance method is direct. Also enter customer and amount. Click apply button, which insert the records into below table.
AR_CASH_RECEIPTS_ALL


Transfer to General Ledger:
  Select Tool>Create Accounting from the receipt form as shown below

Check  ‘Create Final Accounting Post to GL’ and click OK.
This kicks Create Accounting program.

It creates records in the GL_INTERFACE table. You can view the created account information as shown in the below screen
Created accounting information displayed
Journal Import:

Navigation:
General Ledger > Journal> Import> Run


To transfer the data from General Ledger Interface table to General Ledger, run the Journal Import program from Oracle General Ledger. Enter parameters as below, ‘selection criteria’ is to run for a specific group id, no group id or all group id. Click import.


It kicks off Journal Import program
Click on the output to check whether import was successful. If it successful, underlying tables are effected
GL_JE_BATCHES, GL_JE_HEADERS, GL_JE_LINES


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

Query for the unposted journals for a specific period as shown below
From the list of unposted journals displayed, select the journal that to be poated and click on Post button to post the journal

It runs Posting: Single Ledger Program

To view Journal, click Review Journal button. Table effected GL_BALANCES
 

No comments: