Friday, April 21, 2017

Oracle Order Management Functional Interview Questions and Answers

Oracle Order Management is one of the most important module of Oracle E-Business Suite. Using Oracle Order Management we can create and maintain different types of orders. Oracle Order Management drives the order fulfillment process of any business.


 The orders that are created in Order Management can be for inventory items as well as for services.


What are the various order management processes supported by oracle OM

        Standard orders: This method supports Make to Stock business model where the products are made and kept in stock. The products are then shipped to the customer based on his order.

·       Configure to orders: this method supports Assemble to Order business model. The product configuration is chosen at the time of sales order entry. WIP job is created based on the requirement of the sales order

·      Drop ship orders: In this method, your supplier will ship the goods to   your customer on your behalf

·    Internal sales orders: Internal sales orders are created across two    different organizations of the same business group. Internal requisition is raised first and is converted to an internal sales order where the items are shipped based on the quantity specified in the internal sales order.

 

2.  Describe the order flow in Order to Cash life cycle
•Enter the sales order
•Book the sales order
•Pick release
•Ship confirm
•Auto Invoice generation
•Creating receipt
•Transfer to GL

3.  Describe the processes involved after entering sales order?
•The items specified on the order are validated in Oracle Inventory
•The price of the items are calculated using the pricing engine
•The availability of the items are checked and may be reserved
•The items are then pick released and shipped to the customer.

4.  What are the different types of sales orders available?
•Standard
•Mixed
•Return

5.  When is an item eligible for customer order?

When the attributes ‘Customer Ordered’ and ‘Customer order enabled’ are checked in the order management tab of Master Items.

6.  Describe the Header status during the sales order process?

Entering the sales order                               Entered

Booking the sales order                                Booked

Pick release the order                                   Booked

Ship confirm the order                                 Booked

Running interface trip stop                          Booked

 

7.  Describe the line status during the sales order process?

Entering the sales order                               Entered

Booking the sales order                                Awaiting shipping (when  sufficient quantity is available)

Supply eligible (when sufficient quantity is not available)

Pick release the order                                    Picked

Ship confirm the order                                  Shipped

Running interface trip stop                          Closed

8.  Differentiate available quantity and on-hand quantity?

Available quantity + Reserved quantity = On-hand quantity

9.  Differentiate Supply Eligible Status and Supply Partial Status?

Supply Eligible

When there is no sufficient quantity available.

Example: When user tries to book a sales order for Item ‘TestItem’ for a quantity of 10 but the item contains zero quantity. Then the Sales Order Line Status change to ‘Supply Eligible’.

Supply Partial
 when the quantity is partially available.
 Example: When user tries to book a sales order for Item ‘TestItem’ for a quantity of 10 but the item contains only 4 quantities. Then the Sales Order Line Status changes to ‘Supply Partial’.

10.  Describe scheduling in sales order?

It is a process of communicating the balance between customer demand and a company’s ability to fulfill an order from current inventory and supply sources

11.  What are the various ways of scheduling the sales order?
•Setting Profile option – OM:AutoSchedule to yes
•From Tools Menu->Check Autoschedule Checkbox in Sales Order Screen.
•Right Click on Sales Order Line ->Scheduling->Schedule.
•Schedule Order Concurrent Program.

12.  Differentiate ship set and arrival set?

In the ship set process, the user would like to ship group of order lines from same warehouse to same location. If Order Lines are grouped under ship set, then until all lines in the ship set satisfy the demand, you cannot ship the goods to customer.

In the Arrival Set process, the customer requests specific order lines to arrive together

13.  What are the Process Constraints?

Processing Constraints allow Order Management users the ability to control changes to sales orders, at all stages of its order or line workflows to avoid data inconsistencies and audit problems.

14.  At what stage an order cannot be cancelled?

An order cannot be cancelled when it is Pick confirmed.

15.  What are picking rules?

A user-defined set of criteria to define the priorities Order Management uses when picking items out of finished goods inventory to ship to a customer. Picking rules are defined in Oracle Inventory.

16.  What are Defaulting Rules?

While creating the order, you can define defaulting rules so that the default values of the fields pop up automatically instead of typing all information.

17.  What are validation templates?

A validation template names a condition and defines the semantics of how to validate that condition. Validation templates can be used in the processing constraints framework to specify the constraining conditions for a given constraint.

18.  What is pick slip?

Pick slip is a shipping document that the pickers use to locate items in the warehouse/ inventory to ship for an order.

19.  What is packing slip?

It is a shipping document that is sent along with the shipment which details the contents that are sent in that shipment.

20.  What is shipping exceptions report?

Shipping exceptions report prints the exceptions messages during ship confirmation.

21  Explain Bill of Lading?

It is a legal document between the shipper of a particular item and the carrier detailing the type, quantity and destination of the good being carried

The bill of lading also serves as a receipt of shipment when the good is delivered to the predetermined destination. This document must accompany the shipped goods, no matter the form of transportation, and must be signed by an authorized representative from the carrier, shipper and receiver.

22.  What is Interface trip stop?

ITS is triggered at the time of shipping if the option ‘defer interface’ is not checked. When this report is run, it performs two main things:

Update the order management (OE_ORDER_LINES_ALL)

Trigger the inventory interface (TO UPDATE Inventory tables)

23.  When is drop ship order created?
•Customer requires an item that is not stocked normally
•Customer requires large quantities of the item that is not available with you
•It is more economical when the supplier sends the item directly to the customer

24.  What is ASN?

It is an advanced Shipment Notice which is transmitted via Electronic Data Interchange (EDI) from a supplier to let the receiving organization know that the shipment is arriving.

The ASN contains the shipment date, time, identification number, packing slip data, freight information etc

25.  What is Autoinvoice?

It is a concurrent program which is used to perform the invoice processing. Once the order or lines are eligible for invoicing, the invoice interface workflow activity interfaces the data to reeivables

26.  Explain the difference between a credit memo and an invoice?

Credit memo is raised to partially or fully reverse an original invoice amount

An invoice is generated in receivables which show the amount owed by the customers for the purchase of goods or services. It may also contain tax and freight charges

27.  What is FOB?

The term FOB means Freight (or free) on Board is commonly used when shipping goods to indicate who pays loading and transportation costs, and/or the point at which the responsibility of the goods transfers from shipper to buyer.

“FOB shipping point” or “FOB origin” indicates the buyer pays shipping cost and takes responsibility for the goods when the goods leave the seller’s premises. “FOB destination” designates the seller will pay shipping costs and remain responsible for the goods until the buyer takes possession.

28.  What are the different RMA Order Types?
RMA with Credit is used when the customer returns the physical product and also receives credit as a result of the return.
RMA no Credit is used when the customer will return the product but will not be receiving a credit as a result of the return.
RMA Credit only is used when the customer will receive a credit, but the physical return of the product is not required.

29.  What are the necessary setups to perform Internal Sales Order?
•Internal Order and Internal Ordered Enabled set to ‘Yes’.
•Shipping Network(Intransit or Direct) must be defined.
•Internal Customer must be defined

30.  What are Modifiers and Qualifiers?

Modifiers are discount, surcharge or special charge that may be applied to the base price and may alter the value of the item. It can be applied either at order level or at the line level.

A qualifier helps you define who is eligible for a price list or modifier. A qualifier can be a customer name, a customer class, an order type, or an order amount that can span orders.

31.  What are the possible reasons for Interface trip stop to complete with Error?

Few reasons may be

1) Order is on Hold

2) Tax is not applied properly

3) On-Hand qty is not available in the Inventory

4) Inventory Period is not open

5) Make sure Schedule Ship Date or Requested Date is within the range of order Date

Wednesday, April 19, 2017

Procedures parameters


As we all know there are two mandatory parameters that need to be pased for all the procedures called 
1.ERRBUFF 
2.RETCODE..
They are very useful parameters if used properly..like setting the status of the conucrrent program and putting the log messages...
i think it is better we make some rule like we make the program end in Error when ever there is a unidentified exception occurs based on your business rule...

define ERRBUFF as the first parameter and Retcode as the second one...Dont forget to specify the out variable type in it...

CREATE PROCEDURE SAMPLE_PROC (ERRBUF OUT VARCHAR2,
RETCODE OUT VARCHAR2,
p_1 varchar2)

The retcode has three values returned by the concurrent manager
0--Success
1--Success & warning
2--Error

we can set our concurrent program to any of the three status by using these parameters...
for example
Begin
.....
Exception
when others then
fnd_File.put_line(fnd_file.log,'Unhandled exception occurred in package.procedure'||SQLERRM);
retcode='2';
end;
Even you can use fnd_concurrent.set_completion_Status to send the concurrent program to more status than success,error and warning.....

Multiple oraganizations Access control

Multiple oraganizations Access control(MOAC)-R12--Part1

Hi ALL,
Here i am trying to put the MOAC techical architecture in simple terms..

we are might have heard the term multi organization till 11.5.10 prior it is only one organization

what does multiorganization mean??
Managing multiple organizations data in a single system...putting it in lay man terms
For example lets take GEfinancial
It is headquarted in US and operations in india also..assume it declares the Profit and loss results in both the countries..
Indian accounting rules, financial Calendar is different between the US and India..
since it belongs to same company with in one system I define two operating units one for us and one for India
different ledgers and calendars...if I want I can have different chart of accounts also..

to manage data for both the organizations with in single instance oracle introduced the column org_id in all table which holds organization specific data..

For data security purpose people of a organization should able to see their own data to achieve this
they created view on the base table like _all _B which restricts the data of the organization to which the user/user responsibility is attached.
for this purpose they defined a profile MO:operating unit which is set at the responsibility/user level
based on this value the system context org_id is set to the operating unitid set at the profile level
and a additional conditiion is added to the where clause of the views like

'org_id = substrb(userenv(''CLIENT_INFO''),1,10)'

By this way they are able to restrict data to the one organization in all the forms,reports,concprograms

fnd_global.apps_intialize--will set the applciation context org_id

thats the resason when ever we try to access the data from toad/sql plus for single org specific views we will not get any results..

select * from po_headers--no data found

if we set the application context we will able to see data for that org_id

execute dbms_Application_info.set_client_info('101');
or
fnd_global.apps_intialize(userid,responsibilityid,applicationid) --this inturn will get the org_id and set the context


From R12:Multiple organization access control

Till 11.5.10.2 one responsibility is able to see data of only one operating unit..
when ever we want to see another operating unit data we need to change resposnsibility..
From R12 oracle introduced the concept of Multiple organization access control(MOAC) so that being in the same responsibility the user should
be able to see the data for which he is give access..
let see how they achieved it..

1.First we will define a policy which will have access for different operating units
2.they attach that policy to the responsibility/user at profile option level

technically..till 11.5.10 the query is getting chagned as org_id=101
if we have to access multiple organization it should changed to org_id in (101,102) or exits...

for achieving this oracle used the concept of Virtual Private Database (VPD)..
VPD:
The Virtual Private Database (VPD) feature allows developers to enforce security by attaching a security policy to database objects such as tables, views and synonyms. It attaches a predicate function to every SQL statement to the objects by applying security policies. When a user directly or indirectly accesses the secure objects, the database rewrites the user's SQL statement to include conditions set by security policy that are visible to the user.

First lets decide whethere the access mode to a responsibility is single or multiple or all.Based on the security policy oracle decides the access mode
let assume our security policy which is attached to our respobility has access to the two operating units.
now oracle will populate the global temporary table(session controlled) mo_glob_org_access_tmp with all the operating units attached to the security policy
of yours..

based on the vpd a concept from oracle 9i onwards they defined a policy 'ORG_SEC' which call the function 'MO_GLOBAL.ORG_SECURITY' for all the objects to which the policy is attached..



MO_GLOBAL.Org_Security function:

FUNCTION org_security(obj_schema VARCHAR2
obj_name VARCHAR2)RETURN VARCHAR2
IS
BEGIN
--
-- Returns different predicates based on the access_mode
-- The codes for access_mode are
-- M - Multiple OU Access
-- A - All OU Access
-- S - Single OU Access
-- Null - Backward Compatibility - CLIENT_INFO case
--
IF g_access_mode IS NOT NULL THEN
IF g_access_mode = 'M' THEN
RETURN 'EXISTS (SELECT 1
FROM mo_glob_org_access_tmp oa
WHERE oa.organization_id = org_id)';
ELSIF g_access_mode = 'A' THEN -- for future use
RETURN NULL;
ELSIF g_access_mode = 'S' THEN
RETURN 'org_id = sys_context(''multi_org2'',''current_org_id'')';
END IF;
ELSE
RETURN 'org_id = substrb(userenv(''CLIENT_INFO''),1,10)';
END IF;
END org_security;


let see what happend po_headers view(11.5.10.2) in R12

1.Dropped the view po_headers
2.Created a synonym for the base table
create synnonym for po_headers for po_headers_all

3.attach the security policy org_sec to this synonym


so when ever we access this synonym from any where the policy will call the mo_global.org_Security funcion to all a condition to the select statement we isssued

select * from po_headers will be changed as

select * from po_headerS_all where exists 'EXISTS (SELECT 1
FROM mo_glob_org_access_tmp oa
WHERE oa.organization_id = org_id)'

Tuesday, April 18, 2017

Query to find Parameters and Value Sets associated with a Concurrent Program

Query to find Parameters and Value Sets associated with a Concurrent Program

4 comments
 Following query finds the parameters and the value sets that are associated with a Concurrent Program. Change concurrent program name (fcpl.user_concurrent_program_name, see below) according to your search criteria. In this example, my concurrent program name is "XX AR Conversion Program".

-------------------------------------------------------------------------------
-- Query to find Parameters and Value Sets associated with a Concurrent Program
-------------------------------------------------------------------------------


SELECT fcpl.user_concurrent_program_name  "Concurrent Program Name",
       fcp.concurrent_program_name        "Program Short Name",
       fdfcuv.column_seq_num              "Column Seq #",
       fdfcuv.end_user_column_name        "Parameter Name",
       fdfcuv.form_left_prompt            "Prompt Name",
       fdfcuv.enabled_flag                "Enabled Flag",
       fdfcuv.required_flag               "Required Flag",
       fdfcuv.display_flag                "Display Flag",
       fdfcuv.flex_value_set_id           "Value Set ID",
       ffvs.flex_value_set_name           "Value Set Name",
       flv.meaning                        "Default Type",
       fdfcuv.default_value               "Default Value"
  FROM fnd_concurrent_programs      fcp,
       fnd_concurrent_programs_tl   fcpl,
       fnd_descr_flex_col_usage_vl  fdfcuv,
       fnd_flex_value_sets          ffvs,
       fnd_lookup_values            flv
 WHERE fcp.concurrent_program_id          =  fcpl.concurrent_program_id
   AND fdfcuv.descriptive_flexfield_name  =  '$SRS$.' || fcp.concurrent_program_name
   AND ffvs.flex_value_set_id             =  fdfcuv.flex_value_set_id
   AND flv.lookup_type(+)                 =  'FLEX_DEFAULT_TYPE'
   AND flv.lookup_code(+)                 =  fdfcuv.default_type
   AND fcpl.LANGUAGE                      =  USERENV('LANG')
   AND flv.LANGUAGE(+)                    =  USERENV('LANG')
   AND fdfcuv.enabled_flag                =  'Y'
   AND fcpl.user_concurrent_program_name  =  'IFFCO HRMS : Costing Summary Report - Consolidated'  -- 
 ORDER BY fdfcuv.column_seq_num;



Oracle Fusion - Cost Lines and Expenditure Item link in Projects

SELECT   ccd.transaction_id,ex.expenditure_item_id,cacat.serial_number FROM fusion.CST_INV_TRANSACTIONS cit,   fusion.cst_cost_distribution_...