Order To Cash Cycle – O2C

“Order to cash” (O2C or OTC) normally refers to the business process for receiving and processing customer sales.
order to cash process steps can be listed as below:
  • Enter the Sales Order
  • Book the Sales Order
  • Launch Pick Release
  • Ship Confirm
  • Create Invoice
  • Create the Receipts either manually or using Auto Lockbox.
  • Transfer to General Ledger
  • Journal Import
  • Posting



    Step 1] Order Entry:
    Customer sends details of order or sales dept brings order from customers. After that the order is entered in Order Management (OM)
  • Navigation: Order Management Super User> Orders Returns >Sales Orders
  • Here we need to enter the Customer Details (Customer Name , Number, Contact Ship to and Bill to address etc.), Order type. In the Lines tab we need to enter the Item to be ordered and the quantity required. Here we can also check the availability of the order. Here we can save the order. Once saved the Order Status is changed to ‘Entered’.
    Key Tables:
  • OE_ORDER_HEADERS_ALL– All header information is stored here.
  • OE_ORDER_LINES_ALL– All the line information is stored here.
    Step 2] Order Book :
    When we book the order, we are just confirming and freezing our order.
  • The final step in the Sales Order Entry process is to Book the order. This signifies that the Order Entry process is complete and that the order is eligible for the next stage in the line flow for this order, as defined by its Transaction Type. Select the Book Order button. The Entry Status of the Order will change to Booked.
    After Order Booking:
  • Order Header: Booked
  • Order Line : Awaiting Shipping
  • Shipping Transaction form: Ready to release
    Table Level :
    OE_ORDER_HEADERS_ALL: Flow_Status_code –Booked
    OE_ORDER_LINES_ALL: Flow_Status_code – Awaiting Shipping
    WSH_DELIVERY_DETAILS: Released_Status – R ( means – Ready to release)
    Step 3] Launch Pick Release :
    Pick Release is the process in which the items on the sales order are taken out from inventory.
  • Navigation: Order Management Super User> Shipping > Release sales Orders > Release sales order
  • Based On rule: Select the Grouping rule the reaming details will default in Order, Shipping and Inventory tab
  • Order Tab:
  • Order Number: Select the Order Number. Values for the Order Type and Customer fields of this form default to those for the order number you enter here.
  • Ship Set: Select the Ship Set to be released. The Order Number must be selected first.
    Shipping Tab:
  • Auto creates Deliveries : Select Yes in this box to automatically create deliveries for delivery lines once they are released
  • Release Sequence Rule: Select Rule to specify the order in which the picking lines are released.
  • Auto Pick Confirm –Yes/No
    Inventory tab:
  • Warehouse: Select the Warehouse
  • Sub inventory: Select the Sub inventory
  • Pick Slip Grouping Rule: To determine how released picking lines are grouped onto pick slips.
  • Default Stage Sub inventory: Select the Default Stage Sub inventory
    Click on Execute Now Button to complete the pick release of the order. Normally pick release SRS program runs in background . Once the program get completed these are the table get affected:
  • OE_ORDER_LINES_ALL (flow_status_code ‘PICKED’ )
  • WSH_DELIVERY_DETAILS(released_status ‘S’ ‘submitted for release’ )
  • mtl_txn_request_headers
  • mtl_txn_request_lines
  • Mtl_material_transactions_temp(link to above tables through move_order_header_id/line_id
    Step 4] Pick Confirm the Order:
  • If Auto Pick Confirm in the above step is set to NO, then the following should be done.
  • Navigation: Inventory Super User > Move Order> Transact Move Order
  • In the HEADER tab, enter the BATCH NUMBER (from the above step) of the order. Click FIND. Click on VIEW/UPDATE Allocation, then Click TRANSACT button. Then Transact button will be deactivated then just close it and go to next step.
  • Items are transferred from salable to staging Sub inventory.
  • mtl_material_transactions
  • mtl_transaction_accounts
  • wsh_delivery_details (released_status ‘Y’ ‘Released’ )
  • wsh_delivery_assignments
    Step 5] Ship Confirm the Order:
  • The Shipping Transaction window provides a centralized workbench that consolidates three major shipping functions: planning, pick releasing, and ship confirming.
  • Navigation: Order Management Super User>Shipping >Transactions.
  • Here ship confirm interface program runs in background . Data are removed from wsh_new_deliveries.
  • oe_order_lines_all (flow_status_code ‘shipped’)
  • wsh_delivery_details (released_status ‘C’ ‘Shipped’)
  • mtl_transaction_interface
  • mtl_material_transactions(linked through Transaction source header id)
  • mtl_transaction_accounts
    Data are deleted from mtl_demand, mtl_reservations and Item is deducted from mtl_onhand_quantities.
    Step 6] Enter Invoices in Receivables:
  • Run workflow background Process. Workflow Background Process inserts the records in RA_INTERFACE_LINES_ALL with
  • INTERFACE_LINE_CONTEXT =’ORDER ENTRY’
  • INTERFACE_LINE_ATTRIBUTE1 = Order_number
  • INTERFACE_LINE_ATTRIBUTE3 = Delivery_id
    Then it spawns Auto invoice Master Program and Auto invoice import program which creates Invoice for that particular Order.
  • Navigation: Order Management >view >Requests
    Underlying tables:
  • RA_CUSTOMER_TRX_ALL will have the Invoice header information. The column INTERFACE_HEADER_ATTRIBUTE1 will have the Order Number.
  • RA_CUSTOMER_TRX_LINES_ALL will have the Invoice lines information. The column INTERFACE_LINE_ATTRIBUTE1 will have the Order Number.
    Step 7] 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”)
    Step 8] CLOSE ORDER:
  • This is last step of Order Processing . In this stage only oe_order_lines_all table get updated .
  • oe_order_lines_all (flow_status_code ‘closed’, open_flag “N”)



Oracle Apps Technical interview questions

1—how we can create multi reports in xml publishers Ans:subtemplate

 2—how to print multi currency,multi language in xml: using xliff file(transalation)

 3—what are the validations in sql*loader : infile,datafile,begindata(when we write infile*),logfile,discarded file,badfile,when clause,TRAILING NULLCOLS 

 4—where u can get data file : flat file(.csv,.dat,.txt) placed on any path of server

 5—where u r putting control file and where the bad file discard file generated : put control file in bin folder of custom top(generally),The directory parameter                 specifies a directory path to which the bad and discard file will be written.

6—how to print external date in xmlp - using xdosdlt command=<?xdoxslt:current_date($_XDOLOCALE, $_XDOTIMEZONE)?> or <?xdoxslt:sysdate(‘DD-MON-YYYY HH24:MI’)?> 

 7—in data file fields terminated by different sybals how you can write code in ctrl file - field termin=taed by '|' or ','

8—how to use date function in sql*loader -      PUBLISH_AMPM EXPRESSION "TO_CHAR(SYSDATE,'AM')" or DATE "TO_DATE(:HIRE_DATE,'DD-MON-YY')") 


9—which path u have used in sql*loader - bin or vartemp

 10—what are utl file triggers - FOPEN,FCLOSE,FCOPY,FCLOSE_ALL,FREMOVE,FRENAME(same MV in unix)

 11—where u can find errorbuf ret code - dirst two mandatetory parameter of any procedure as errbuf,retcode

12 how we can know weather procedure is successfully executed from backend - state as VALID

13—data file contains how many levels - N/A => db serever(db+ora home),insatnce specific files,appln server(appl+common)

 14—what kind of errors u got in discard file - errorr dur to data,datatypes, oracle errors

15—how you can skip the records in thr middle of data file and end of data file - using skip commond (eg.OPTIONS(skip=1,bindsize=1048576,rows=1024))
if multiple files then use skip but run sqlldr twice OR use when clause to check end of first file or start of second.

 16—invoice created and send to the gl but we don’t know the invoice number how we can know - CCID and GL_POSTED_DATE column in ra_cust_trx_line_gl_dist_all table
17—where u devlop -interface program local syst or server system-server
 18—how we can know how many receipts are created for a po - po_header_id,po_line_id,po_distribution_id

19—after completion of po interface into base table how we can see etair line header info in single table. - in po_lines_all

20—how to save a workflow in database in short cut? Can we delete workflow how -yes

 21—what are the common errors in workflow - notification mailer down,attribute not provided
22—strong cursor and weak cursor - return type known and not known until o/p of cursor come

23—what is external table where we will use - ORGANIZATION EXTERNAL keyword after create table, 3rd party uses non-oracle ERP.

24—we have 50 record while printing in rdf report we have print 10 records per page how u can do - declare varible nad initialize it with 0, get variable count and increament it,when count is 10 use "split-by-page-break" tag

25—what is the difference between workflow and oracle alert 

A.Oracle Alert is a database event detection tool designed to detect database events.
B.Oracle Alert does contain some workflow type features such as response processing, that allow a sequence of actions to be taken depending on a users response to a message. (Example : Approval sequence)

A.Oracle workflow is designed to manage the execution complex of business processes that result from database events.
B.Oracle Workflows response processing capabilities are more advanced than Oracle Alert.


26—hot to implement muli org in interface approach - "MO: Security Profile" can be set at Site and Responsibility level.
 27—while loading records if the records no>=10 then all the records should be discarder,then error message populated how you can achieve

 28—while picking the sales aorder qty is there in shipping there is no qty then what u will you will do 

29—what is the next step after back order 

30—if we do any modification in po_headers_v is it effects the base tables 

31—what is tca architecture 

32—where we can see the invoice payment 

33—when selection existing customer in om what you will do 

34—how to pass the values for the variables in api. 

35—can we use look up values instead of profile options. 

35—how to create ldt lct files? usimf fndload or XDO command(unix command)

36—how to tranfor on file from one instance to another instance?(throgh unix using sftp)-
sftp -oIdentityFile=$lv_key_file $lv_rem_user@$lv_rem_server <<EOF
mput file1 dest_path
bye
EOF

Query to find out inventory adjustment made by specific user in specific time 


Below is the query to find out inventory adjustment made by specific user in specific time :

SELECT   (SELECT segment1
            FROM mtl_system_items_b
           WHERE inventory_item_id = mpa.inventory_item_id
             AND organization_id = mpa.organization_id) item_number,
         (SELECT fu.user_name
            FROM apps.fnd_user fu
           WHERE fu.user_id = mpa.created_by) created_by, creation_date,
         mpa.organization_id,
         (SELECT NAME
            FROM apps.hr_organization_units hou
           WHERE organization_id = mpa.organization_id) org_name
    FROM mtl_physical_adjustments mpa
   WHERE 1 = 1 AND mpa.created_by = :user_id
   AND TRUNC (creation_date) >:p_date
ORDER BY creation_date;


How to find Approver of Requisition in oracle Apps R12



SELECT distinct papf.full_name
                   FROM po.po_action_history pah
                      , applsys.fnd_user fu
                      , hr.per_all_people_f papf
                  WHERE object_id = prh.requisition_header_id
                    AND pah.employee_id = fu.employee_id
                    AND fu.employee_id = papf.person_id
                    AND SYSDATE BETWEEN papf.effective_start_date
                                    AND papf.effective_end_date
                    AND pah.object_type_code = 'REQUISITION'
                    AND pah.action_code = 'APPROVE'
                    AND pah.sequence_num =
                           (SELECT MAX(sequence_num)
                              FROM po.po_action_history pah1
                             WHERE pah1.object_id = pah.object_id
                               AND pah1.object_type_code = 'REQUISITION'
                              AND pah1.action_code = 'APPROVE'
                               )

Cloned Date, Version Details, URL of Oracle Applications,Cloned date of oracle application and Version



Query to get Version details..
++++++++++++++++++++++++++++++++++++

SELECT product, VERSION, status
FROM product_component_version;


Query to  get cloned date of an oracle instance
+++++++++++++++++++++++++++++++++++++++++

SELECT resetlogs_time FROM v$database;

========================

Query to get the front end URL from backend
++++++++++++++++++++++++++++++++++
SELECT home_url FROM icx_parameters;

How to remove End date of specific responsibility for specific user in oracle apps R12

If you want to remove end date for particular user say user ABC and want to have access to that responsibility then run below script in back-end(plsql window) to get access to required responsibility :

{**Note-you need to have APPS access to run below script}

DECLARE

p_user_name VARCHAR2 (50) := 'ABC';
p_resp_name VARCHAR2 (50) := 'Responsibility_Name';
v_user_id NUMBER (10) := 0;
v_responsibility_id NUMBER (10) := 0;
v_application_id NUMBER (10) := 0;

BEGIN

BEGIN
SELECT user_id
INTO v_user_id
FROM fnd_user
WHERE UPPER (user_name) = UPPER (p_user_name);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
DBMS_OUTPUT.put_line ('User not found');
RAISE;
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('Error finding User.');
RAISE;
END;

BEGIN
SELECT application_id, responsibility_id
INTO v_application_id, v_responsibility_id
FROM fnd_responsibility_vl
WHERE UPPER (responsibility_name) = UPPER (p_resp_name);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
DBMS_OUTPUT.put_line ('Responsibility not found.');
RAISE;
WHEN TOO_MANY_ROWS
THEN
DBMS_OUTPUT.put_line
('More than one responsibility found with this name.');
RAISE;
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('Error finding responsibility.');
RAISE;
END;

BEGIN

--DBMS_OUTPUT.put_line (‘Initializing The Application’);

fnd_global.apps_initialize (user_id => v_user_id,
resp_id => v_responsibility_id,
resp_appl_id => v_application_id
);

DBMS_OUTPUT.put_line
('Calling FND_USER_RESP_GROUPS_API API To Insert/Update Resp');

fnd_user_resp_groups_api.update_assignment
(user_id => v_user_id,
responsibility_id => v_responsibility_id,
responsibility_application_id => v_application_id,
security_group_id => 0,
start_date => SYSDATE,
end_date => NULL,
description => NULL
);

DBMS_OUTPUT.put_line
('‘The End Date has been removed from responsibility');
COMMIT;

EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('‘Error calling the API’');
RAISE;
END;
END;

Workflow Email Override oracle apps R12


If you are testing with for alert.Sending email to required user then you need to enter your email in workflow mailer then and then only you will get email.Also just updating and changing email address wont work until and unless you run below script from backed and commit the changes.

--workflow email override
 BEGIN
FND_SVC_COMP_PARAM_VALS_PKG.LOAD_ROW
( x_component_name => 'Workflow Notification Mailer',
x_parameter_name => 'TEST_ADDRESS',
x_parameter_value => 'User_Email_id',
x_customization_level => 'L',
x_object_version_number => -1,
x_owner => 'user_name'
);
END;

COMMIT;

Importing journals Oracle Apps R12

To prepare for importing journals:

1. Define all account segment values used in your feeder systems.
2. Define your ledger using Accounting Setup Manager. The accounting setup must have a Complete status to use its ledgers.
3. Define or enable all currencies used in your feeder systems.
4. Define the journal entry sources used in your feeder systems. You can also specify whether you want General Ledger to store journal reference information from your feeder systems for a particular source. 5. Define journal entry categories used in your feeder systems.
6. If you want Journal Import to assign sequential numbers to your journal entries, enable sequential numbering, specifying Automatic as both your numbering and document generation method.
7. Open periods used in your feeder system. You can only import journals into Open or Future-Enterable periods in General Ledger.
8. Run the Optimizer program to create indexes on your account segments.
9. Define the concurrent program controls to improve the performance of Journal Import by setting the amount of disk space and memory it uses. The Journal Import program requires approximately 1.4 megabytes of memory to run. You can also specify whether to save your Journal Import data each time you run Journal Import. Journal Import runs faster if you do not archive your data.

Script for Creating user in oracle Apps R12

You can create user in oracle from front end directly, but user can be created only if you are having Sysadmin responsibility.What if you don't have  Sysadmin responsibility. but you have full back-end access (APPS access), then by running following script you can create your user from PL/SQL developer.

Reasons for a sales order to get Backordered

Back Orders:

The Oracle "term" backorder is a "status" on the order line or delivery line indicating that you have tried to release an order for picking in your warehouse, but that the pick release was UNSUCCESSFUL because there was no available inventory.(Backorder can be partial or complete). The Oracle term backorder does NOT mean that you have open purchase orders for the out-of-stock item from your vendors.

The term backorder is also used in business a little differently than in Oracle. The term "An item is on backorder" usually means that the item is not in stock, but the shipping company has already placed purchase orders from their suppliers to restock the item.