Order Management Interview Questions Part 2

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

Source : Internet Blogs

Read More»

SQL query to find out Drop Ship Sales Order, Line, Requisition and Purchase order

select h.order_number,l.line_number SO_Line_number,  ph.segment1
PO_Number,pl.line_num PO_Line_Number,
ph.authorization_status,ph.closed_date,ph.closed_code,
prh.interface_source_code,
prh.segment1 Requisition_number, prl.line_num Requisition_line_number
from
OE_DROP_SHIP_SOURCES ods,
oe_order_headers_all h,
oe_order_lines_all l,
po_line_locations_all pll,
po_lines_all pl,
po_headers_all ph,
po_requisition_headers_all prh,
po_requisition_lines_all prl
where h.header_id = l.header_id
and h.header_id = ods.header_id
and ods.line_location_id = pll.line_location_id
and ods.po_header_id = ph.po_header_id
and ods.po_line_id = pl.po_line_id
and ph.po_header_id = pl.po_header_id
and prh.requisition_header_id = ods.requisition_header_id
and prl.requisition_line_id = ods.requisition_line_id
and prh.requisition_header_id = prl.requisition_header_id
and h.order_number = &sales_order_number;

Read More»

SQL Query to find sales order lines whose workflow is Purged

Select Line_Id, flow_status_code,ordered_quantity,shipped_quantity, fulfilled_flag, invoice_interface_status_code
From Oe_Order_Lines_All l
where open_flag = 'Y'
And not Exists
(Select 1 From Wf_Items W
Where W.Item_Type='OEOL'
and w.item_key = to_char(l.line_id));

Read More»

Sql query for relation between Business Group, Legal Entity, Operating Unit and Balancing segment

select HRL.COUNTRY,
  HROUTL_BG.name BG,
  HROUTL_BG.ORGANIZATION_ID,
  LEP.LEGAL_ENTITY_ID,
  LEP.name LEGAL_ENTITY,
  HROUTL_OU.name OU_NAME,
  HROUTL_OU.ORGANIZATION_ID ORG_ID,
  HRL.LOCATION_ID,
  HRL.LOCATION_CODE,
  GLEV.FLEX_SEGMENT_VALUE
from XLE_ENTITY_PROFILES LEP,
  XLE_REGISTRATIONS REG,
  HR_LOCATIONS_ALL HRL,
  HZ_PARTIES HZP,
  FND_TERRITORIES_VL TER,
  HR_OPERATING_UNITS HRO,
  HR_ALL_ORGANIZATION_UNITS_TL HROUTL_BG,
  HR_ALL_ORGANIZATION_UNITS_TL HROUTL_OU,
  HR_ORGANIZATION_UNITS GLOPERATINGUNITSEO,
  GL_LEGAL_ENTITIES_BSVS GLEV
where LEP.TRANSACTING_ENTITY_FLAG      = 'Y'
and LEP.PARTY_ID                       = HZP.PARTY_ID
and LEP.LEGAL_ENTITY_ID                = REG.SOURCE_ID
and REG.SOURCE_TABLE                   = 'XLE_ENTITY_PROFILES'
and HRL.LOCATION_ID                    = REG.LOCATION_ID
and REG.IDENTIFYING_FLAG               = 'Y'
and TER.TERRITORY_CODE                 = HRL.COUNTRY
and LEP.LEGAL_ENTITY_ID                = HRO.DEFAULT_LEGAL_CONTEXT_ID
and GLOPERATINGUNITSEO.ORGANIZATION_ID = HRO.ORGANIZATION_ID
and HROUTL_BG.ORGANIZATION_ID          = HRO.BUSINESS_GROUP_ID
and HROUTL_OU.ORGANIZATION_ID          = HRO.ORGANIZATION_ID
and GLEV.LEGAL_ENTITY_ID               = LEP.LEGAL_ENTITY_ID;

Read More»

SQL Query To Check Form Personalizations

SELECT   ffcr.SEQUENCE "Seq", ffcr.description "Description",
         DECODE (ffcr.rule_type,
                 'F', 'Form',
                 'A', 'Function',
                 'Other'
                ) "Level",
         ffcr.enabled "Enabled", ffcr.trigger_event "Trigger Event",
         ffcr.trigger_object "Trigger Object", ffcr.condition "Condition",
         DECODE (ffcr.fire_in_enter_query,
                 'Y', 'Both',
                 'N', 'Not in Enter-Query Mode',
                 'O', 'Only in Enter-Query Mode',
                 'Other'
                ) "Processing Mode"
    FROM apps.fnd_form_custom_rules ffcr
   WHERE ffcr.function_name = 'PO_POXPOEPO'
     AND ffcr.form_name = 'POXPOEPO'
ORDER BY ffcr.SEQUENCE;

Read More»

Key Tables in Oracle Inventory

Table	Description
MTL_PARAMETERS	It maintains a set of default options like general ledger accounts; locator, lot, and serial controls, inter-organization options, costing method, etc. for each organization defined in Oracle Inventory. Each organization’s item master organization (MASTER_ORGANIZATION_ID) and costing organization (COST_ORGANIZATION_ID) are maintained here.
MTL_SYSTEM_ITEMS_B	This is the definition table for items. This table holds the definitions for inventory items, engineering items, and purchasing items. The primary key for an item is the INVENTORY_ITEM_ID and ORGANIZATION_ID. Therefore, the same item can be defined in more than one organization. Items now support multilingual description. MLS is implemented with a pair of tables: MTL_SYSTEM_ITEMS_B and MTL_SYSTEM_ITEMS_TL. Translations table (MTL_SYSTEM_ITEMS_TL) holds item Description and Long Description in multiple languages.
MTL_ITEM_STATUS	This is the definition table for material status codes. Status code is a required item attribute. It indicates the status of an item, i.e., Active, Pending, Obsolete.
MTL_UNITS_OF_MEASURE_TL	This is the definition table for both the 25-character and the 3-character units of measure. The base_uom_flag indicates if the unit of measure is the primary unit of measure for the uom_class. Oracle Inventory uses this table to keep track of the units of measure used to transact an item.
MTL_ITEM_LOCATIONS	This is the definition table for stock locators. The associated attributes describe which subinventory this locator belongs to, what the locator physical capacity is, etc.
MTL_ITEM_CATEGORIES	This table stores inventory item assignments to categories within a category set. For each category assignment, this table stores the item, the category set, and the category. Items always may be assigned to multiple category sets. However, depending on the Multiple Assignments Allowed attribute value in a given category set definition, an item can be assigned to either many or only one category in that category set.
MTL_CATEGORIES_B	This is the code combinations table for item categories. Items are grouped into categories within the context of a category set to provide flexible grouping schemes. Item categories now support multilingual category description. MLS is implemented with a pair of tables: MTL_CATEGORIES_B and MTL_CATEGORIES_TL. MTL_CATEGORIES_TL table holds translated Description for Categories.
MTL_CATEGORY_SETS_B	It contains the entity definition for category sets. A category set is a categorization scheme for a group of items. Items may be assigned to different categories in different category sets to represent the different groupings of items used for different purposes. An item may be assigned to only one category within a category set, however. STRUCTURE_ID identifies the flexfield structure associated with the category set. Category Sets now support multilingual category set name and description. MLS is implemented with a pair of tables: MTL_CATEGORY_SETS_B and MTL_CATEGORY_SETS_TL. MTL_CATEGORY_SETS_TL table holds translated Name and Description for Category Sets.
MTL_DEMAND	This table stores demand and reservation information used in Available To Promise, Planning and other Manufacturing functions. There are three major row types stored in the table: Summary Demand rows, Open Demand Rows, and Reservation Rows.
MTL_SECONDARY_INVENTORIES	This is the definition table for the subinventory. A subinventory is a section of inventory, i.e., raw material, finished goods, etc. Subinventories are assigned to items (in a many to one relationship), indicating a list of valid places where this item will physically exist in inventory.
MTL_ONHAND_QUANTITIES	It stores quantity on hand information by control level and location. It is maintained as a stack of receipt records, which are consumed by issue transactions in FIFO order. The quantity on hand of an item at any particular control level and location can be found by summing TRANSACTION_QUANTITY for all records that match the criteria.
MTL_TRANSACTION_TYPES	It contains seeded transaction types and the user defined ones. USER_DEFINED_FLAG will distinguish the two. The table also stores the TRANSACTION_ACTION_ID and TRANSACTION_SOURCE_TYPE_ID that is associated with each transaction type.
MTL_MATERIAL_TRANSACTIONS	This table stores a record of every material transaction or cost update performed in Inventory. Records are inserted into this table either through the transaction processor or by the standard cost update program. The columns TRANSACTION_TYPE_ID, TRANSACTION_ACTION_ID, TRANSACTION_SOURCE_TYPE_ID, TRANSACTION_SOURCE_ID and TRANSACTION_SOURCE_NAME describe what the transaction is and against what entity it was performed.
MTL_ITEM_ATTRIBUTES	This table stores information on item attributes. Each row in the table corresponds to an attribute. The table stores the attribute name, the corresponding user-friendly name seen by the users, and the kind of validation enforced on the attribute.
MTL_ITEM_CATALOG_GROUPS_B	This is the code combinations table for item catalog groups. An item catalog group consists of items that can be described by the same set of descriptive elements or item properties. When an item is associated with an item catalog group, the item inherits the descriptive elements for that group which then behave like additional item attributes.
MTL_ITEM_REVISIONS_B	It stores revision levels for an inventory item. When an item is defined a starting revision record is written out to this table, so every item will at least have one starting revision.
MTL_ITEM_TEMPLATES_B	This is the definition table for item templates. It contains the user-defined name (TEMPLATE_NAME) and description (DESCRIPTION) ONLY for backward compatibility. You can use a template to set certain item attributes.
MTL_DESCRIPTIVE_ELEMENTS	It stores the descriptive element definitions for an item catalog group. Descriptive elements are defining properties used to describe in the catalog group.
MTL_DESCR_ELEMENT_VALUES	It stores the descriptive element values for a specific item. When an item is associated with a particular item catalog group, one row per descriptive element (for that catalog group) is inserted into this table.
ORG_ACCT_PERIODS	It holds the open and closed financial periods for organizations.
MTL_CUSTOMER_ITEMS	It stores customer item information for a specific customer. Each record can be defined at one of the following levels: Customer, Address Category, and Address. The customer item definition is organization independent.
MTL_SYSTEM_ITEMS_INTERFACE	It temporarily stores the definitions for inventory items, engineering items and purchasing items before loading this information into Oracle Inventory.
MTL_TRANSACTIONS_INTERFACE	It allows calling applications to post material transactions (movements, issues, receipts etc. to Oracle Inventory  transaction module.
MTL_ITEM_REVISIONS_INTERFACE	It temporarily stores revision levels for an inventory item before loading this information into Oracle Inventory.
MTL_ITEM_CATEGORIES_INTERFACE	This table temporarily stores data about inventory item assignments to category sets and categories before loading this information into Oracle Inventory.
MTL_DESC_ELEM_VAL_INTERFACE	This table temporarily stores descriptive element values for an item that is associated with an item catalog group before loading this information into Oracle Inventory.
MTL_DEMAND_INTERFACE	It is the interface point between non-Inventory applications and the Inventory demand module. Records inserted into this table are processed by the Demand Manager concurrent program.
MTL_INTERFACE_ERRORS	It stores errors that occur during the item interface process reporting where the errors occurred along with the error messages.

Read More»

FND Tables

FND_APPLICATION:  Stores applications registered with Oracle Application Object Library.

FND_APPLICATION_TL: Stores translated information about all the applications registered with Oracle Application Object Library.

FND_APP_SERVERS: This table will track the servers used by the E-Business Suite system.

FND_ATTACHED_DOCUMENTS: Stores information relating a document to an application entity.

FND_CONCURRENT_PROCESSES: Stores information about concurrent managers.

FND_CONCURRENT_PROCESSORS: Stores information about immediate (subroutine) concurrent program libraries.

FND_CONCURRENT_PROGRAMS: Stores information about concurrent programs. Each row includes a name and description of the concurrent program.

FND_CONCURRENT_PROGRAMS_TL: Stores translated information about concurrent programs in each of the installed languages.

FND_CONCURRENT_QUEUES: Stores information about concurrent managers.

FND_CONCURRENT_QUEUE_SIZE: Stores information about the number of requests a concurrent manager can process at once, according to its work shift.

FND_CONCURRENT_REQUESTS: Stores information about individual concurrent requests.

FND_CONCURRENT_REQUEST_CLASS: Stores information about concurrent request types.

FND_CONC_REQ_OUTPUTS: This table stores output files created by Concurrent Request.

FND_CURRENCIES: Stores information about currencies.

FND_DATABASES: It tracks the databases employed by the eBusiness suite. This table stores information about the database that is not instance specific.

FND_DATABASE_INSTANCES: Stores instance specific information. Every database has one or more instance.

FND_DESCRIPTIVE_FLEXS: Stores setup information about descriptive flexfields.

FND_DESCRIPTIVE_FLEXS_TL: Stores translated setup information about descriptive flexfields.

FND_DOCUMENTS: Stores language-independent information about a document.

FND_EXECUTABLES: Stores information about concurrent program executable.

FND_FLEX_VALUES: Stores valid values for key and descriptive flexfield segments.

FND_FLEX_VALUE_SETS: Stores information about the value sets used by both key and descriptive flexfields.

FND_LANGUAGES: Stores information regarding languages and dialects.

FND_MENUS: It lists the menus that appear in the Navigate Window, as determined by the System Administrator when defining responsibilities for function security.

FND_MENUS_TL: Stores translated information about the menus in FND_MENUS.

FND_MENU_ENTRIES: Stores information about individual entries in the menus in FND_MENUS.

FND_PROFILE_OPTIONS: Stores information about user profile options.

FND_REQUEST_GROUPS: Stores information about report security groups.

FND_REQUEST_SETS: Stores information about report sets.

FND_RESPONSIBILITY: Stores information about responsibilities. Each row includes the name and description of the responsibility, the application it belongs to, and values that identify the main menu, and the first form that it uses.

FND_RESPONSIBILITY_TL: Stores translated information about responsibilities.

FND_RESP_FUNCTIONS: Stores security exclusion rules for function security menus. Security exclusion rules are lists of functions and menus inaccessible to a particular responsibility.

FND_SECURITY_GROUPS: Stores information about security groups used to partition data in a Service Bureau architecture.

FND_SEQUENCES: Stores information about the registered sequences in your applications.

FND_TABLES: Stores information about the registered tables in your applications.

FND_TERRITORIES: Stores information for countries, alternatively known as territories.

FND_USER: Stores information about application users.

FND_VIEWS: Stores information about the registered views in your applications.

Read More»