Types of Table Extensions with Definitions in Oracle Apps

Types of Table Extensions with Definitions in Oracle Apps

Would like to share few table extensions with definitions which are used in oracle apps:

S no	Table Extension	Definition	Ex - Table names
1	_ALL	This Table holds all the information about different operating units in Multi-Org environment. client_info can also be set to a specific operating unit to see the data specific to that operating unit only.	OE_ORDER_HEADERS_ALL OE_ORDER_LINES_ALL PO_DISTRIBUTIONS_ALL PO_HEADERS_ALL PO_LINES_ALL PO_LINE_LOCATIONS_ALL
2	_TL	Tables corresponding to another table with the same name minus the _TL. These tables provide multiple language support. For each item in the table without _TL there can be many rows in the _TL table, but all with different values in the LANGUAGE column.	PO_DISTRIBUTIONS_ALL_EXT_TL OE_TRANSACTION_TYPES_TL  MTL_ITEM_TEMPLATES_TL MTL_UNITS_OF_MEASURE_TL MTL_CATEGORIES_TL
3	_B	BASE tables - These Tables are very important and the data is stored with all validations. It is supposed that these table will always contain the perfect format data. If anything happens to the BASE table data, then it is a data corruption issue.	MTL_SYSTEM_ITEMS_B BOM_COMPONENTS_B BOM_STRUCTURES_B QP_LIST_HEADERS_B
4	_F	These are date tracked tables, which occur in HR and Payroll. For these there are two date columns EFFECTIVE_START_DATE and EFFECTIVE_END_DATE which together with the PK identifies a row uniquely. The date intervals cannot overlap.	BEN_BENEFICIARIES_F HR_ALL_POSITIONS_F PAY_ALL_PAYROLLS_F
5	_V	View Tables created on base tables	OE_ORDER_LINES_HIST_V OE_PRICE_ADJUSTMENTS_V OE_ORDER_HEADERS_HIST_V OE_ORDER_TYPES_V PO_VENDOR_SITES_ALL_V PO_ACTION_HISTORY_V
6	_VL	Multi Language View Tables which combines the row of the base table with the corresponding row of the _TL table where the LANGUAGE = USERENV(’LANG’).	FND_DESCRIPTIVE_FLEXS_VL FND_DESCR_FLEX_COL_USAGE_VL HR_FORM_WINDOWS_VL HR_FORM_ITEMS_VL
7	_S	These tables are sequences, used for finding new values for the primary key of a table.	AQ$_AR_REV_REC_QT_S IGS_PS_COO_AD_UNIT_S AQ$_FND_CP_GSM_OPP_AQTBL_S

Oracle Order Management - Quick Codes (Lookups)

Oracle Order Management - Quick Codes (Lookups)

Application Utilities Lookups:

Main purpose of Application Lookups in Oracle Application are:

1. Maintain existing and to define additional Lookups for your shared Lookup types.

2. A maximum of 250 Lookups can be defined for a single type.

3. Each Lookup has a code and a meaning.

Navigation:

Oracle Order Management > Setup > QuickCodes > Order Management

Application Lookups Block:

Application Lookups Block contains the below fields:

  

Type

Query any Lookup type. A maximum of 250 Lookups can be defined for a single type.

Application

Query the application associated with the Lookup type.

Description

If you use windows specialized for a particular Lookup type, the window uses this description in the window title.

Access Level

The access level restricts changes that are possible to a lookup type. The possible access levels are:

  

System -              No changes to the lookup codes are allowed.

Extensible -        New lookup codes can be added. However, you cannot modify seeded lookup codes.

User -                    You can change any lookup code.

Lookups Values Block:

Application Lookups Values Block contains the below fields:

Code

Enter the code value for the Lookup. A maximum of 250 Lookups can be defined for a single type.

When you enter a valid Lookup meaning into a displayed window field, Lookups stores this code into a corresponding hidden field.

Ex: Lookup "Y" displays the meaning "Yes" but stores the code value "Y" in a hidden field.

Values cannot be changed in this field after committing them. To remove an obsolete Lookup, It can be either disabled the code, enter an end date, or change the meaning and description to match a replacement code.

Meaning

When a valid Lookup meaning is entered into a displayed window field, Lookups stores the corresponding code into a hidden field. Lookups automatically displays the meaning in the Lookups field whenever the window is queried. For example, the Lookup "Y" displays the meaning "Yes" but stores the code value "Y" in a hidden field.

Description

Description is to add the meaning to give more information about the Lookup.

Tag

Optionally enter in a tag to describe the lookup. The tag can be used to categorize lookup values.

Effective Dates

Enter the dates between which this Lookup becomes active. If start date is not entered then the Lookup is valid immediately.

Once a Lookup expires, users cannot insert additional records using the Lookup, but can query records that already use the Lookup. If an end date is not entered the Lookup is valid indefinitely.

Enabled

Indicate whether applications can use the Lookup. If disabled, users cannot insert additional records using the Lookup, but can query records that already use this Lookup.

DFF:

Double brackets ([ ]) indicates a descriptive flexfield that is used to add data fields to this window without programming.

Oracle R12 Order Management - Drop Shipment Order Flow

Oracle Order Management – Drop Shipment Order Flow

Drop Shipment order type is used for when a customer is urgent need of an item and which is not an inventory stocked item or when the stock is not available and customer requires large quantity. If it is more economical to ship the goods directly from supplier to customer.

Prerequisite:

Drop Shipment item should be

1. Customer ordered, customer orders enabled, Shippable, OE Transactable & Default SO Source Type should be “External” in Order Management tab and Purchased & Purchase order enabled in Purchasing tab

2. Sourcing Rule & Approved Supplier list should be defined for the Drop Shipment Item

Drop Shipment Flow:

Create a sales order, line source type should be External

Once the sales order is entered, the line status will be “Entered”

Once the sales order is booked, the workflow will be deferred. And the line status will be “Booked”

Run the “Workflow Background Process” program, once the program is completed the line status will be “Awaiting Receipt”

Run the “Requisition Import” program from the Purchasing Responsibility with parameter Source as ORDER ENTRY and Initiate Approval after ReqImport as Yes.

Once the Requisition Import program is completed, query the sales order and in additional information, Drop Ship tab, PO / PO requisition details would be displayed.

If you query the sales order in shipping transaction form, no details will be available.

 

Go to Purchasing --> Autocreate, Query the requisition and create the PO Document and approve the same.

Once the PO release is created, query the sales order and in additional information, Drop Ship tab, PO details would be displayed.

When shipment confirmation is received, enter a receipt in Purchasing, even if the drop-shipped item is not transactable. This creates inbound and outbound material transactions in system for accounting purposes.

Once the item is received, the sales order line status will be “Shipped”

After system's inventory has a record of the transaction, run the workflow background process, Receivables Interface and AutoInvoice programs to generate an invoice for customer.

Please note below the sales order line status for each activity in Drop Shipment order flow in Oracle Order Management:

Oracle Order Management – Shipping Execution: Release Sequence Rules

Release Sequence Rules:

• You can define release sequence rules to specify the order in which eligible picking lines are allocated to Inventory during pick release. You can release the picking lines by:
• Order number
• Outstanding Invoice Value
• Scheduled Date
• Departure Date
• Shipment Priority

• You can assign a priority level to one or more attributes with 1 being the highest priority and 5 being the lowest. You can also define whether you want the picking lines released in ascending or descending order.

• For example, if you select the Ascending button for Order, picking lines are released by ascending order number-- Order 1001 is released first, then Order 1002, Order 1003, and so on. If the Descending button is selected, the picking lines are released by descending Order number from highest to lowest.

• Note: You can define either the Outstanding Invoice Value attribute or the Order attribute for the Release Sequence Rule, but you cannot select both for the same rule. No two attributes can be given the same priority.

• Release sequence rules can be edited after creating it, but its name cannot be changed.

• Release Sequence rules determines the order in which inventory is allocated to sales orders. You choose to allocate by order, outstanding Invoice value, Scheduled Date, Departure Date and Shipment Priority.

• If a company is dealing with very demanding products and has a problem of running out of material before all of their orders have been filled it is very important that they have filled their most important orders first.

• This Release Sequence rule then assigned in Shipping Parameter form to specify the order in which eligible picking lines are released during Pick Release. All the release transactions will be done based on this rule for the particular inventory organization.

 

Oracle Order Management – Shipping Execution: Pick Slip Grouping Rules

Pick Slip Grouping Rules:

Navigation: Oracle Order Management > Setup > Shipping > Picking > Define Pick Slip Grouping Rules

• Pick Slip Grouping Rules organize how released order lines are grouped on Pick Slips for ease of picking.

• Selection can be defined using criteria:

Order Number,

Carrier,

Sub inventory,

Shipment priority,

Customer,

Departure,

Ship to &

Delivery

For Example: By using the Pick Slip Grouping Rule as Subinventory, the user can reduce the number of trips to a particular subinventory by grouping all lines for that subinventory on to one Pick Slip. If Carrier & Customer are selected, all picking lines with the same freight carrier & customer are grouped on to one pick slip.

• This Pick Slip Grouping rule then assigned in Shipping Parameter form. Grouping of order line on Pick Slip will be done based on this rule for the particular inventory organization.

Oracle Order Management – Shipping Rules

In Oracle Order Management – Shipping Execution. 

During shipping process, rules are used in the process of picking the items from stores to staging area.

There are four rules used during Order Management – Shipping & Execution Process:

S. no	Rule Name	Function
1	Release Rule	Used to determine as which line be released first.
2	Release Sequence Rule	Used to determine as to which details items needs to be allocated first.
3	Picking Rule	Used to determine as from which locations the parts are to be picked first by the picker.
4	Pick Slip Grouping Rule	Used to determine as to by what criteria the details will be clubbed together

Release Rules:

Navigation: Oracle Order Management > Setup > Shipping > Picking > Define Release Rule Form

• Release rules define the criteria to be used during Pick Release. Only orders that meet the criteria and are eligible will be released. An order line is eligible if it has completed the prerequisite workflow activities, such as Schedule - Line or Create Supply.
• When pick release is run, the pick release is performed based on the parameters set up in the selected pick release rule. For example, you can create a specific rule that pick releases only backordered lines.
• Note: Although you can also enter the pick release criteria at pick release time without creating a rule, creating a rule is more efficient if you frequently run the same pick release. Also, note that it is required when releasing using SRS or when using the Auto Pick Pack and Ship features
• This is just for Defaulting purpose, you can change the attribute values at the time of Releasing order

 

   

Release Sequence Rules

Navigation: Oracle Order Management > Setup > Shipping > Picking > Define Release Sequence Rules

• Release sequence rules is to specify the order in which eligible picking lines are allocated to Inventory during pick release. You can release the picking lines by:
• Order number
• Outstanding Invoice Value
• Scheduled Date
• Departure Date
• Shipment Priority

Picking Rules

Navigation: Oracle Inventory > Setup > Rules > Define Picking Rules

• Picking rules, which are created and maintained in Oracle Inventory, suggest which material to use, based on inventory controls such as revision control, Lot control, FIFO (first in first out) or subinventory / locator picking numbers.

Pick Slip Grouping Rules

Navigation: Oracle Inventory > Setup > Rules > Pick Slip Grouping

• Pick Slip Grouping Rules organize how released order lines are grouped on Pick Slips for ease of picking. For Example: By using the Pick Slip Grouping Rule as Subinventory, the user can reduce the number of trips to a particular subinventory by grouping all lines for that subinventory on to one Pick Slip.