Oracle Order To Cash Process for Shippable Item

Order to cash (OTC or O2C) is a set of business processes that involve receiving and fulfilling customer requests for goods or services. It is the complete process of entering an order into the system (Sales Order), delivery the good(s) (Shipping), and then producing the Invoice for the good(s) which are Ordered by the Customer.

O2C cycle  can be summarized in below steps:

• Enter the Sales Order
• Book the Sales Order
• Pick Release
• Ship Confirm
• Create Invoice

Now let's see the underlying affected tables for each of the above steps and the Status for each step.

Step	Order Header Status	Order Line Status	Shipping Transaction Status	Tables Affected
Enter Sales Order	Entered	Entered		New Records populated in below tables: OE_ORDER_HEADERS_ALL (FLOW_STATUS_CODE = ENTERED, BOOKED_FLAG = N, Primary key=HEADER_ID) OE_ORDER_LINES_ALL (FLOW_STATUS_CODE = ENTERED,  BOOKED_FLAG = N, OPEN_FLAG = Y, Primary key= LINE_ID)
Book Sales Order	Booked	Awaiting Shipping	Ready To release	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_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) MTL_DEMAND (LINE_ID come as a reference in DEMAND_SOURCE_LINE)
Pick Release	Booked	Picked	Released to Warehouse -> Staged/Pick Confirmed	WSH_NEW_DELIVERIES (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) 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 ) MTL_RESERVATIONS
Ship Confirm	Booked	Shipped	Shipped	OE_ORDER_LINES_ALL (flow_status_code =‘SHIPPED’) WSH_DELIVERY_DETAILS. released_status=‘C’ (‘Shipped’) Data from MTL_TRANSACTIONS_INTERFACE is moved to MTL_MATERIAL_TRANACTIONS and MTL_MATERIAL_TRANSACTIONS is updated with Sales Order Issue transaction. Data is deleted from MTL_DEMAND, MTL_RESERVATIONS Item reduced from MTL_ONHAND_QUANTITIES MTL_TRANSACTION_ACCOUNTS is updated with accounting information.
Create Invoice				RA_INTERFACE_LINES_ALL (interface table into which the data is transferred from order management) 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)

1. Enter Sales Order

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

2. Book Sales Order

This is next step, when Order is booked. The status of the order header has changed from Entered to 'Booked', and the status of the line (check the Status field in the Main tab of the lines) has changed from Entered to 'Awaiting Shipping'.

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

Check if the order lines are scheduled or not. If any of the order line is in BOOKED status, it means that the order line is not yet scheduled.

Schedule Order

·         Run the Schedule Order concurrent program to schedule the order. After the concurrent program is scheduled the order line will move to Awaiting Shipping status.

·         Still if the order lines do not go to Awaiting Shipping, check for any holds on the order line/ header. Release the holds (if any) and re-run the Schedule Order program.

·         You can even schedule the order line in the following way: Right click on the order line and progress the line to schedule it.

At the same time when order is booked ‘Demand interface program’ is triggered in the background and demand of the item with specified quantity is created and these demand information is stored in MTL_DEMAND.

3. 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.

*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 Confirm/ Move Order Transaction

If Auto Pick confirm is not selected while pick release, then we have to manually do the transact move order process. In fact, pick confirm also triggers the transact move order process in which the inventory is moved from item sub inventory to staging area.Here material transaction occurs.

Order line status becomes 'Picked' on Sales Order and 'Staged/Pick Confirmed' on Shipping Transaction Form. Move order tables are populated. Here request is generated to move item from Source (RM or FG) sub-inventory to staging sub-inventory.

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

4. Ship Confirm

The items on the delivery gets shipped to customer at this stage.

The Interface Trip Stop (ITS) program gets submitted in the background and it should  be completed successfully.

The Status in Shipping Transaction screen will now be ‘Shipped’.

Optional: Check if the item has actually been shipped out of inventory.

Make sure you are in the correct organization. If not, navigate to Inventory > Change Organization and select the correct one.

*Navigate to Inventory > Transactions > Material Transactions

*Enter the following information in the Find Material Transactions screen and click on Find

1. Transaction Date : Ensure the transaction date is within the range specified
2. Source Type : Sales Order
3. Source :

• Evoke the LOV and click on the button Combinations
• Enter your Sales Order Number and Order Type, then click OK(Example : 66666.Mixed.ORDER ENTRY)

*Go to the Transaction Type tab, and notice the line with Transaction Type = Sales Order Issue. This is the Inventory Transaction that happened when the item was shipped out of the warehouse M1, hence the negative quantity.

*The other two lines with Transaction Type = Sales Order Pick, are for the move order transaction that happened automatically during Pick Release (automatic as per the pick release parameters we had set in this testflow), 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.

5. Create Invoice

Run the Workflow Background Process with the following parameters.

Item Type : OM Order Line

Process Deferred : Yes

Process Timeout : No

Workflow Background Process inserts the records RA_INTERFACE_LINES_ALL with

• INTERFACE_LINE_CONTEXT     =     ‘ORDER ENTRY’
• INTERFACE_LINE_ATTRIBUTE1= Order_number
• INTERFACE_LINE_ATTRIBUTE3= Delivery_id

AR concurrent requests Auto invoice Master Program and Auto invoice import program gets 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.

Invoicing workflow activity transfers shipped item information to Oracle Receivables.

The Invoice created can be seen using the Receivables responsibility.

Read more »

Query - Get all Custom BI Publisher Reports with Excel Output

SELECT 
prog.user_concurrent_program_name,
exe.executable_name,
exe.execution_method_code,
exe.execution_file_name
FROM 
fnd_executables_form_v exe,
fnd_concurrent_programs_vl prog,
xdo_templates_b rtf
WHERE
 exe.executable_id = prog.executable_id
AND rtf.application_short_name = 'XX' 
AND rtf.default_output_type = 'EXCEL'
AND concurrent_program_name = rtf.data_source_code

Read more »

Query - Get Form Personalization Details from Database

SELECT 
DISTINCT
fcr.form_name , 
fft.user_form_name, 
fcr.enabled, 
app.application_name ,
fcr.description,
ca.action_type,
ca.enabled,
ca.object_type
FROM 
     FND_FORM_CUSTOM_RULES fcr,
     FND_FORM ff,
     FND_FORM_TL fft,
     FND_APPLICATION_TL app,
     FND_FORM_CUSTOM_ACTIONS caWHERE 
fcr.form_name = ff.form_name
AND fcr.enabled ='Y'
AND ff.form_Id = fft.form_Id
AND ff.application_Id = app.application_Id
AND app.application_Id = 660 --For Order Management
AND fcr.id = ca.rule_id
AND fcr.form_name LIKE  '%form_name%' -- LIKE  'OEXOEORD' for Sales Order Form

Read more »

Query - Link OM, WSH and 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.source_line_id = ool.line_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'

      AND ooh.order_number = :p_order_number

Read more »

Query - Get Sales Order and corresponding AR Invoice Details

SELECT 
       oha.order_number, 
       ola.line_number line_number,
       ola.ordered_item item_name,
       ola.ordered_quantity,       ola.ordered_quantity * ola.unit_selling_price line_amount,
       rcta.trx_number invoice_number, 
       rcta.trx_date invoice_date,
       rctla.line_number trx_line_number
FROM   oe_order_headers_all oha,
       oe_order_lines_all ola              
       ra_customer_trx_all rcta,
       ra_customer_trx_lines_all rctla
 WHERE oha.header_id = ola.header_id
   AND rcta.customer_trx_id = rctla.customer_trx_id
   AND rctla.interface_line_context = ‘ORDER ENTRY’
   AND rctla.interface_line_attribute6 = TO_CHAR (ola.line_id)
   AND rctla.interface_line_attribute1 = TO_CHAR (oha.order_number)
   AND order_number = :p_order_number

Read more »

Query - Get Customer,its Bill To Address and Ship To Address for a Sales Order

SELECT 
  ooh.order_number 
, hps_ship.party_site_number site_number,
, hp_bill.party_name customer_name
, hl_ship.address1 ||Decode(hl_ship.address2,NULL,'',chr(10)) 
||hl_ship.address2||Decode(hl_ship.address3,NULL,'', chr(10)) 
||hl_ship.address3||Decode(hl_ship.address4,NULL,'', chr(10)) 
||hl_ship.address4||Decode(hl_ship.city,NULL,'',chr( 10)) 
||hl_ship.city ||Decode(hl_ship.state,NULL,'',',') 
||hl_ship.state ||Decode(hl_ship.postal_code,'',',') 
||hl_ship.postal_code ship_to_address 
, hl_bill.address1 ||Decode(hl_bill.address2,NULL,'',chr(10)) 
||hl_bill.address2||Decode(hl_bill.address3,NULL,'', chr(10)) 
||hl_bill.address3||Decode(hl_bill.address4,NULL,'', chr(10)) 
||hl_bill.address4||Decode(hl_bill.city,NULL,'',chr( 10)) 
||hl_bill.city ||Decode(hl_bill.state,NULL,'',',') 
||hl_bill.state ||Decode(hl_bill.postal_code,'',',') 
||hl_bill.postal_code bill_to_address 
FROM oe_order_headers_all ooh 
, hz_cust_site_uses_all hcs_ship 
, hz_cust_acct_sites_all hca_ship 
, hz_party_sites hps_ship 
, hz_parties hp_ship 
, hz_locations hl_ship 
, hz_cust_site_uses_all hcs_bill 
, hz_cust_acct_sites_all hca_bill 
, hz_party_sites hps_bill 
, hz_parties hp_bill 
, hz_locations hl_bill 
, mtl_parameters mp 
WHERE 1 = 1 
AND ooh.order_number = :p_order_number 
AND ooh.ship_to_org_id = hcs_ship.site_use_id 
AND hcs_ship.cust_acct_site_id = hca_ship.cust_acct_site_id 
AND hca_ship.party_site_id = hps_ship.party_site_id 
AND hps_ship.party_id = hp_ship.party_id 
AND hps_ship.location_id = hl_ship.location_id 
AND ooh.invoice_to_org_id = hcs_bill.site_use_id 
AND hcs_bill.cust_acct_site_id = hca_bill.cust_acct_site_id 
AND hca_bill.party_site_id = hps_bill.party_site_id 
AND hps_bill.party_id = hp_bill.party_id 
AND hps_bill.location_id = hl_bill.location_id 
AND mp.organization_id(+) = ooh.ship_from_org_id 

Read more »

Query - Ge Request Group(s) for a Concurrent Program

SELECT cpt.user_concurrent_program_name "Concurrent Program Name",
       DECODE(rgu.request_unit_type,
              'P',
              'Program',
              'S',
              'Set',
              rgu.request_unit_type) "Unit Type",
       cp.concurrent_program_name "Concurrent Program Short Name",
       rg.application_id "Application ID",
       rg.request_group_name "Request Group Name",
       fat.application_name "Application Name",
       fa.application_short_name "Application Short Name",
       fa.basepath "Basepath"
  FROM fnd_request_groups         rg,
       fnd_request_group_units    rgu,
       fnd_concurrent_programs    cp,
       fnd_concurrent_programs_tl cpt,
       fnd_application            fa,
       fnd_application_tl         fat
 WHERE rg.request_group_id = rgu.request_group_id
   AND rgu.request_unit_id = cp.concurrent_program_id
   AND cp.concurrent_program_id = cpt.concurrent_program_id
   AND rg.application_id = fat.application_id
   AND fa.application_id = fat.application_id
   AND cpt.language = USERENV('LANG')
   AND fat.language = USERENV('LANG')
   AND cpt.user_concurrent_program_name Like ‘%ProgramName%’;

Read more »

Behind-The-Wheel Driving Test at Santa Clara California on March 2016

I was reading anything and everything available on Google, regarding driving test experience at Santa Clara, before my Behind-The -Wheel test. I promised myself that I will share my experience once I get my License. Hopefully, it is going to help someone who is going through the same phase.

Well, my first-time experience was not so good. I had booked my first Behind-the-wheel test, right after clearing my written exam, at San Jose DMV. I took a morning slot, around 8:30 AM, at Santa Clara DMV, within a month time gap. I thought that a month of driving is enough to get a license. But sometimes I can be so wrong!

I took a driving class of 6 hours to learn the traffic rules and to get the initial confidence of hitting the road with our own new car.  I started driving on weekends and a few times on weekdays with my husband. As the D- Day was approaching, I was getting nervous and was not sure if I were ready for the test. Finally, the day arrived.

My First Driving Test - A BLUNDER!

We reached 15 minutes before my scheduled time and was waiting in the queue. At the counter, they checked my fingerprints and the insurance document, registration document and my husband's license. Once everything verified, I was told to wait in my car, in the lane, meant for driving test. 2 cars were already there, waiting before me. My turn came and an Inspector came for my test. I had to sign my "Driving Performance Evaluation Score Sheet". She checked all the documents and started my "Pre-Drive" Test where I was checked if I knew all the controls of my car. Once satisfied, she sat inside the car and told me to drive ahead. There was a very clear instruction that she was going to tell me where to take a right or a left. If nothing was told, I had to follow the road and traffic rules.

We hit the road and she told me to take a left turn along the "Granade Ave". At "Flora Vista Ave." she asked me to take a left turn. Now, did it ever happen to you when you get confused between your left and right? If not, believe me, you are lucky! Unfortunately, I was not among those lucky ones. I said "Okay" aloud. But inside my mind, I was ready to take a right turn. Near the intersection, my Inspector again repeated that she wanted me to take a Left turn. Then it struck me that I did a big mistake and I panicked!I had already missed my left turning lane and some car was waiting there to take a left turn. I was not sure what I needed to do and then my inspector intervened and asked me to wait till all the traffic clears and then to take a left at the "Flora Vista Ave.". I knew that I had screwed up my test. I was asked to enter the DMV and park on one of those allocated slots. She explained to me that I should not be that nervous and drive more and then again take a test and it was over.

My Second Driving Test - A SUCCESS!

After my first awful experience, I was not willing to go for the test unless I was absolutely prepared. I continued driving, but only on weekends. 3 months passed and  I didn't book any appointment. Then one day, suddenly I realized that I would never feel confident enough for the test.So, I told myself " bring it on"! I booked an appointment, again at Santa Clara DMV after 3 weeks time. Although I got advice from many people to take the test at Las Gatos DMV as apparently, it is more strict at Santa Clara. But I took a chance. I started preparing, both ON road and OFF Road. I saw a few videos and the common mistakes that people do in driving test. I started searching for the routes that they usually take the driving test at Santa Clara. I took a print out of the routes and drove on those roads, 2 days before my test. But I was still worried. Reluctantly, I searched in Google if anyone else has faced the problem of getting confused between left and right and if there is any workaround. I was really surprised to find out that it is a common syndrome. Well, that is a different story that I will tell some other day. Somewhere I read to repeat aloud LEFT or RIGHT before taking the turn so that my brain can hear me clearly and can act accordingly.

This time I had booked a slot at 2:30 in the afternoon, on 8th March,2016. We reached there on time and repeated all the steps as before. Only, this time, I had to pay $7 for the re-test. After the initial checking, the inspector got into the car. He gave me the same instruction as before and we hit the road. I drove for almost 20 - 30 minutes and returned to the DMV and my inspector told that I can take my Driving License at counter D. I was ecstatic!

 A few pieces of advice from my bag of experience!

• Know the roads and the routes well before taking the test. It builds a confidence and reduces confusion when you are actually taking the test. Here is the list of routes for Santa Clara Driving Test, that I had taken print of and practiced. Also, it includes the route that I had to take, during the exam.
• If possible, book an afternoon slot when the roads are relatively empty and the people on the road are driving in a more relaxed way.
• Need to be accompanied by someone with valid California Driving License on the Behind-The-Wheel test day, even if you are driving well and do not need assistance.
• Carry the below documents without a mistake:

1. Appointment Print
2. Learner License
3. Car Insurance
4. Car Registration

• Know your car! Below are the things that are checked as per the PRE-DRIVE CHECKLIST.

1. Driver Window
2. Windshield
3. Rear-View Mirrors
4. Turn Signals Front/Back
5. Brake Lights
6. Tires
7. Foot Brake
8. Horn
9. Emergency/Parking Brake
10. Arm Signals
11. Windshield Wipers
12. Defroster
13. Emergency Flasher
14. Headlights
15. Passenger Door
16. Glove Box
17. Seat Belts

• Be Conscious and check the mirrors every 5 seconds
• Emergency Vehicle approaching: I knew that I need to slow down and stop at the right curb if any Emergency Vehicle is approaching with sirens ON, in my lane. But I was not sure if I need to do anything if it comes on the other Lane. I did a few research and YES we need to stop at the right curb if we are in the opposite lane and there is no divider. But, no need to stop if there is a divider in between and the emergency vehicle in on the other side.
• Any single critical error implies unsatisfactory result. Critical Errors include the below:

1. Intervention by Examiner
2. Strikes Object/Curb
3. Disobeys Traffic Sign/Signal
4. Disobey Safety personnel or safety vehicles
5. Dangerous maneuver
6. Speed
7. Lane violation
8. Auxiliary equipment use

• No test on Highways and no parallel parking test. Only need to reverse along the curb, without hitting.
• Total 15 errors are allowed which are not critical

So that is all I had. All the Best for your test! Driving can be terrifying for some people like me! But at the end of that fear, there is a rewarding experience awaiting you. The story how I learned my driving is long and boring. But you can have your own tale to tell.

Good Luck and Drive Safe!

Read more »

Santa Clara Driving Test(Behind-The-Wheel) Routes

Through Homestead
Route 1:
=========================
L on Granada
R on Pomeroy
L on El Sobrante Av (Reverse & Curbside Test)
R on Kiely Blvd
L on Benton
R on White Dr
R on Las Palmas Dr @ STOP sign
R on HomeStead
R on Pomeroy
R on Granada
R onto DMV Office and Park

Route 2:
=========================
L on Granada Ave
R on Pomeroy Ave
L on El Sobrante Av (Reverse & Curbside Test)
L on Alpine Ave
R on Fresno St
R on Kiely Blvd
R on Homestead Rd
R on Pomeroy Ave
L on Benton St
R on Flora Vista Ave
R on Granada
R onto DMV Office and Park

Through El Camino

Route 1:
=========================
L on Granada
R on Pomeroy
L on El Sobrante Av
R on Via Dondera
R on Cala Blazas
L on El Camino Real
R on Lawrence Rd (Not onto Lawrence Freeway)
R on Warburton Avenue (Reverse and Curbside Parking)
R on Pomeroy
R on El Camino
L on Flora Vista
R on Granda
R onto DMV office and Park

Through Both El Camino and Homestead

Route 1:
=========================
L on Granada
L on Pomeroy
R on El Camino Real
R on Kiely Blvd
R on Home Stead Rd
R on Qunice Avenue
L on Brook Dale
R on Pomeroy
L on Benton
R on Flora Vista Ave
L on Granda
R onto DMV office and Park

Route 2 (My Driving Test Route)
=========================
L on Granada
L on Flora Vista Avenue
R on El Camino Real
R on Pomeroy Ave
L on Benton 
R on Keily Blvd
R on Homestead RD
R on Pomeroy
L on Benton
R on Flora Vista Ave
L on Granda
R onto DMV office and Park

Read more »

Books for Babies - My Daughter's Collection

My daughter who is a year and 4 months old now has her own favorite collection.  Seeing her inclination towards books, we started taking her to the local library. But it did not help much. She just wants to read the same books again and again, what we had bought for her. May be it's going to take some time when she will enjoy reading new books every time. But as of now, she is quite happy with the little collection she has. I think babies like the predictability, the routine and are not open for anything new in their life. They are happy in their small world.
So here is the collection of her books according to her preference and I got all her books from Amazon: Books For Babies:

Mr. Brown Can MOO! - Dr. Seuss's

Llama Llama's little library - Anna Dewdney 

Giraffes Can't Dance - Giles Andreae/ Guy Parker-Rees

Happy Hippo, Angry Duck  - Sandra Boynton

Head, Shoulders, Knees and Toes... - Annie Kubler

My big animal book

Baby Faces Peekabook

Baby Touch and Feel Playtime

Please And Thank You - Richard Scarry's

This Little Kitten - Ladybird Touch and Feel

Read more »