OM Related Queries

OM Related Queries

OM Related Queries

SALES ORDER FLOW

Navigation: Orders. Returns -> Sales Orders

1. Enter Customer and Order Type. The other fields may default values. If “Ship To Location”, “Bill To Location” and “Price List” are not defaulted, pick those values from “LOV”

2. All the fields should default in Others tab. If Payment Terms or Warehouse is not defaulted, choose values from LOV. Warehouse parameter stores the warehouse from which the order will be shipped

3. Enter “Ordered Item”, “Qty”, “UOM”.

Note: The item should already be setup in Inventory. Please refer to Item Setup for the item setup information. The item price should exist in the price list specified at the order header level. Please refer to Price List Setup section for price list setup.

Base Tables: Header Level : oe_order_headers_all

Colums: flow_status_code= “ENTERED” and booked_flag=”N”

Line Level : oe_order_lines_all

Columns : flow_status_code= “ENTERED” and open_flag=”Y”

Book Sales Order

Book the order by clicking on “Book Order” button

Base Tables: Header Level : oe_order_headers_all

Colums: flow_status_code= “BOOKED” and booked_flag=”Y”

Line Level : oe_order_lines_all

Columns : flow_status_code= “WAITING SHIPING” and booked_flag=”Y”

Shipping: wsh_delivary_details

Columns released_status=”R” ‘Ready To Release’

Wsh_delivary_assignments

Reservations: This step is required for doing reservations schedule order program run in backend and two tables are updated

Tables: mtl_demand and mtl_reservations

Pick Releasing a Sales Order:

Navigation: Shipping -> Release Sales Orders -> Release Sales Orders

1. Enter the Order Number that needs to be pick released

2. Specify the warehouse. This should be the same warehouse as specified in the order line.

3. Click on Concurrent button. This should launch “Pick Release” concurrent program

4. Click on “View” and then “Request” menu option. Click on “Find”. It should bring all the concurrent requests submitted by the user. There should be a request called “Pick Slip Report”. Click on “View Output” button to view the orders that are pick released.

Tables: wsh_delivary_details

Wsh_picking_batch

Wsh_new_delivary

Wsh_delivary_assignments

oe_order_lines_all flow_status_code= “PICKED”

mtl_onhand_quantities

mtl_transaction_accounts

Shipping a Sales Order:

Navigation: Shipping -> Transactions

1. Enter the Order Number in “From Order Number” field and click “Find” button

2. Click on Details button to enter the ship quantity

3. Enter “Shipped Quantity” and click Done

4. Click on the “Delivery” tab and select “Ship Confirm” for actions and click “GO” to confirm the shipment

Tables: wsh_delivary_details

Wsh_picking_batch

Wsh_new_delivary

Wsh_delivary_assignments

oe_order_lines_all flow_status_code= “SHIPPED”

mtl_onhand_quantities

mtl_material_transactions

Invoicing a Sales Order:

Navigation: Inventory -> Workflow Background Engine

1. Run this concurrent program to interface order to accounts receivables. Select “Yes” for “Process Deferred” and select “Yes” for “Process Timeout” parameters. Click on OK

Tables: wsh_delivary_details

Ra_interface_lines_all

Ra_customer_trx_all

Ra_customer_trx_lines_all

Ra_cust_trx_types_all

Viewing and Modifying an Invoice:

Navigation: Orders. Returns -> Sales Orders

1. Query the order that was invoiced (Press F11, Enter the Order Number and Press Ctrl F11). Right Click on Customer Field and choose “Additional Order Information” -> Invoices/Credit Memos

2. Note down the invoice number

Navigation: Receivables -> Transactions -> Transactions

3. Query the invoice using the invoice number. Order and Invoice can be verified for proper invoicing

Creating Receipt for the Invoice

Navigation: Receivables -> Receipts -> Receipts

1. Enter “Payment Method”, “Receipt Number”, “Receipt Amount” and “Trans Number”. Receipt Amount can be equal to the invoice amount and Trans Number is the invoice number for which the receipt was created.

TABLES: ar_cash_receipts_all

Ar_payment_schedules_all

Ar_cash_receipts_history_all

Ar_receivable_applications_all

Receivables Transfer to GL

Navigation: Receivables -> Interfaces -> General Ledger

1. Run General Ledger Transfer Program to Transfer AR data to GL

Creation of Pricelist (Copying)

Order Management Super User

(N) Pricing-> Price Lists -> Copy Price List

Base Tables Affected: qp_list_headers_b,

qp_list_lines

Switch Responsibility to Inventory Super User

Inventory Super User

Items -> Master Items

For Item to be used in transactions check the following tabs and mandatory fields.

Main Tab – Primary UOM, Pricing, Deviation Factor +/-

Inventory Tab – Inventory Item, Stackable, Translatable, Reservable need to be checked.

Purchasing Tab – Purchased, Purchasable, Allow Description Updatable need to be checked.

Order Management Tab – Customer Ordered, Customer Orders Enabled, Shippable, OE Translatable need to be checked

Invoicing Tab – Invoicable Item, Invoice Enabled.

Base Tables Affected: mtl_system_items_b

After that we have to assign the item in the Inventory Organizations.

(M) Tools -> Organization Assignments

Check the Checkbox Checked for all the organizations to which u want to assign the item to

Base Tables Affected: mtl_system_items_b (One Line created for each Organization)

We have to create the On-Hand Quantity for the Item Created for that you have to create a Miscellaneous Transaction.

(N) Transactions -> Miscellaneous Transaction

(N) Type -> Miscellaneous Receipt

(B) Transaction Lines

Then Enter the Item and Quantity and Save

Base Tables Affected: mtl_onhand_quantities

Attach the item created to a Price List.

Switch Responsibility to Order Management Super User.

(N) Pricing -> Price Lists -> Price List Setup

Base Tables Affected: qp_list_headers_b, qp_list_lines

Creating a Profile Class To attach to a customer.

(N) Customers -> Profile Class

Base Tables Affected: hz_cust_profile_classes

To Create Payment Terms

(N) Receivables -> setup -> Transactions -> Payment Terms

Base Tables Affected: ra_terms

To Disable the Automatic Numbering for the Locations while creating the customer.

(N) Receivables -> Setup -> System -> System Options

Uncheck the check box Automatic Site Numbering.

Base Tables Affected: ar_system_parametes_all

Customer Creation

(N) Customers -> Standard

Base Tables Affected: hz_parties, hz_party_sites, hz_locations, hz_cust_accounts, hz_cust_account_sites_all, hz_cust_site_uses_all, ra_customers

To update a customer profile which is already in use?

Do Not Update existing Profile

Update All Profiles

Updated All Uncustomized Profiles

(N) Customers -> Customer Profiles

Create Order Types, Line Types (Return and Standard)

(N) Setup -> Transactions Types -> Define

Base Tables Affected: oe_transaction_types_all,oe_workflow_assignments

Create a Document Sequence

·         Create a Sequence.

·         Create a Category

·         Assign the Sequence to the Category

Setup -> Documents -> Define

Base Tables Affected: fnd_document_sequences

Document Category Creation

(N) Setup -> Documents -> Categories

Base Tables Affected: fnd_doc_sequence_categories

Document Sequence Assignments

(N) Setup -> Documents -> Assign

Base Tables Affected: fnd_doc_sequence_assignments

Defaulting Rules

Create a Defaulting Rule Template

(N) Setup -> Rules -> Defaulting

(B) Defaulting Condition Templates

(B) Defaulting Rules

For Price List

Then Go to

(M) Tools -> Generate Default Handler Package.

Base Tables Affected: oe_def_attr_def_rules, oe_def_attr_condns, ak_object_attributes

Capturing End User Details (Install Base)

First Check the item is install base trackable or not?

(N) Order, Organizer -> Sales Order

(T) Lines

(B) Actions -> Installation Details

Base Table Affected: csi_t_party_details

Sales Credits - Practice.

(N) Orders. Returns -> Sales Orders

(B) Actions -> Sales Credits

Base Table Affected: oe_sales_credits

Creating a Ship Sets, Arrival Sets and Fulfillment Sets

Sales Order Screen

(T) Shipping and go to appropriates set and create new one

Attaching Documents to the Order

Go to Attachments

Base Tables Affected: fnd_attached_documents,fnd_documents_tl,fnd_documents_short_text

Blanket Sales Agreement

(N) Blanket Sales Agreements -> Blanket Sales Agreements

Base Tables Affected: oe_blanket_headers_all, oe_blanket_lines_all

(B) Submit Draft

Internal Orders

Prerequisites the internal Ordered Check Boxes need to be checked

Internal Orders

First we have to create the Purchase Requisition for the internal Order.

(N) Purchasing -> Requisitions -> Requisitions

You have to be setup as Employee to access purchasing

Change Responsibility to HRMS Manager

(N) HRMS Manager -> People -> Enter and Maintain

Base Tables affected: per_all_people_f

Now assign the employee to the user

Change Responsibility System Administrator

(N) Security -> User

Base Table affected: fnd_users

Change Responsibility to OM Super User.

(N) Purchasing -> Requisitions -> Requisitions

Base Tables Affected: po_requisition_headers_all, po_requisition_lines_all

Now the PO Requisition needs to be approved, so the user should have appropriate privileges

To Check the Status of the Purchase Requisition you have to check the Requisition Summaries screen

(N) Purchasing -> Requisitions -> Requisition Summary

We can check the status in a view po_requisition_headers_inq_v

After the Purchase Requisition has been approved then you have to run the Create Internal Orders Program to import the internal Purchase Requisitions to Internal Orders.

(N) Reports, Requests -> Run Requests

Check the Log to ensure that the Internal Requisition has been processed for the Order

The Above Process will take the PO Requisition up to the Order Interface Tables now we have to run the Order Import Program to Import the Internal Orders.

(N) Reports, Requests -> Run Requests

Now check for the Order Created for that PR

(N) Orders. Returns -> Order Organizer

Automatically the order created will be of type Mixed. To Change the Order Type of the Internal Order we have to set up that at the Organization Level.

(N) Inventory -> Setup -> Organization -> Purchasing Parameters

Now Pick Release

(N) Shipping -> Release Sales Orders -> Release Sales Orders

Ship Confirm

(N) Shipping -> Transactions

To Check the Internal Order Details.

Go to the Line and

(B) Actions -> Additional Line Information

(T) Internal Requisition.

Drop Shipment

Create Order, Line Level -> Shipping tab, Source type should be External

Now Create Purchase for the Order.

(N) Purchasing -> Reports -> Run

Step 1 – Creation of Purchase Requisition

Step 2 – Create PO from PR.

(N) Purchasing -> Requisitions -> Requisition Summary

(T) Related Documents

Search based on Sales Order Number

(N) Purchasing -> AutoCreate (To Crete a PR from PO)

Base Tables Affected: po_headers_all, po_lines_all

Now if we check the Order Line Status, it will be Awaiting Receipt.

Step 3 – Receiving

Change the Organization to – M1

Now we have to create the receipt for the PO

(N) Purchasing -> Receiving -> Receipts.

Save the work and it will trigger the following programs.

Apart from this it will also trigger the Payables open Import Program also.

Now if we check the status of the Line it will be shipped

To Check the Details of Drop Shipment then you have go to Additional Line Information.

(B) Actions -> Additional Line Information

After this step line will be awaiting fulfillment, so run the workflow background process and autoinvoice program so that invoice will be generated.

Scheduling

Scheduling Before Booking,

Initially Scheduled ship Date and Schedule Arrival date will be empty

(M) Tools -> Scheduling -> Schedule

With Scheduling the above mentioned fields will be populated automatically

We can do auto scheduling also.

(M) Tools -> Auto Scheduling (Checking the Check Box)

Profile Options:-

·         OM: Scheduling Role

Values – CSR Only – User Cannot Access The Scheduling

CSR and Scheduler – User Can Access All the Tabs

Scheduler Only – Only Scheduler Tab will be enabled

·         OM: Apply Automatic Attachments – Determine whether the rule based attachments are enabled without User Intervention.

·         OM: Autoschedule – Determines the default Settings of the autoscheduling.

·         OM: Invoice Source – This Value is transferred if the transaction type is null

·         OM: Invoice Transaction Type – This Value is transferred if the transaction type is null

·         OM: Item Flexfield – Determine the structure of the item Flexfield used in OM.

·         OM: Source Code – Value is defaulted to ORDER ENTRY and identifies the source code which passes to OM to inventory while scheduling.

·         OM: Sales Orders form preference – Confirms the default sales order window whether it is quick sales order window or the standard one.

·         Sequential Numbering – Required Profile option to determine the sequential number triggered in E-Business Suite.

You are not allowed to change the order once if the order is booked. This has been protected by the processing constraints.

If you want to update even if the order is booked, then this processing constraints need to be changed.

(N) Setup -> Rules -> Security -> Processing Constraints

Base Tables Affected: oe_pc_assignments, oe_pc_exclusions

Splitting Lines

Base Tables Affected: oe_order_lines_all

Line will be split into two lines and the split_from_line_id will be populated for the second line.

Managing Holds

(N) Setup -> Orders -> Holds

Base Tables Affected: oe_hold_definitions, oe_hold_authorizations

Applying Holds

(B) Actions -> Apply Holds

To Retrieve/ Check the holds against order

(B) Actions -> Additional Order Information

Base Tables Affected: oe_hold_sources_all, oe_order_holds_all

Now if we try to book the order you can’t book it will through an error.

To Proceed Further we have to release the hold.

Releasing Holds

(B) Actions -> Release Holds

Base Tables Affected: oe_hold_sources_all, oe_hold_releases_all

Now, you can book the order.

(N) Orders. Returns -> Sales Order

Credit Check

(N) Setup -> Rules -> Credit

Base Tables Affected: oe_credit_check_rules

Assign this credit check rule to the transaction type

(N) Setup -> Transaction Types -> Define

Credit Check Flag For the Customer Profile need to be enabled.

(N) Setup -> Customers -> Profile Classes

We have to check the credit Flag in Payment Terms also.

(N) Receivables -> Setup -> Transaction Types -> Payment Terms

Credit Check Flag need to be checked at 3 places

·         Customer Level

·         Payment Terms

·         Transaction Type

If we create a order which is exceeding the Credit Limit of the customer it will automatically apply the hold.

(N) Orders. Returns -> Sales Order

(B) Actions -> Additional Header Information

To Process this order the hold need to be removed manually.

Cancelling Orders

(N) Orders. Returns -> Sales Order

(B) Actions -> Cancel

Base Tables Affected: oe_order_headers_all, oe_order_lines_all

Purging Of Orders

Create the Purge set and then Query for the Set

(N) Orders. Returns -> Purge -> Order Purge Selection

It’s Time to see the orders through Purge Set

(N) Orders. Returns -> Purge -> Order Purge Selection

Working with Order Organizer

(N) Orders. Returns -> Quick Order Organizer

Create Own Folder

(M) Folders -> New

Check the Tools Available for Organizing Orders

(M) Folders -> Folder Tools

Save the Changes your folder will be created here and if you click the folders button you can see your folder and if you open the folder what ever the changes you made and saved will be reflected.

Changing the prompt appearing in the current form.

(M) Folder -> Change Prompt

Query for some records

You can save the Query for future purposes.

Check the Saved Query in the Public Folders

How does one control Scheduling after booking an Order?

As we have already seen either the lines get automatically scheduled or these are scheduled when the order is booked. But if you want to postpone scheduling still further…

This can be achieved by two ways.
a. Changing of work flow definition
b. Applying hold at the time of order entry

a. Changing of work flow definition

In 11.5.10 release 'Schedule - Line, Deferred' is available to perform scheduling activity with minimal manual intervention.
From Release 12 along with 'Schedule - Line, Deferred' a new sub process 'Schedule - Line, Manual' is introduced.

For example let us consider “Schedule - Line, Deferred” here. Deferred scheduling work flow process to be used in place of the default 'Schedule - Line' activity.

One of the above sub-process can be copied and customized to the generic line process.

The process “Schedule - Line, Deferred” should be used in place of “Schedule – Line” in the Line work flow.

1. Copy “Line Flow – Generic” to some other name for example XX Line Flow -Generic.
2. Copy “Schedule - Line Deferred” sub process into the new work flow process XX Line Flow –Generic as below

3. Replace this with new one.

The New workflow would look as below

Attach this new process to the Transaction Type in question.

Understanding Schedule – Line, Deferred:

Once booking is done then the work flow stops at “Schedule – Deferred” status and this would remain at “Schedule – Deferred” activity. Since this is a deferred activity, we need manual intervention to push this further and this is done using Work flow Background process request.

Sample work flow status diagram for one such case:

Tool --> Work flow Status

The line would wait for an external activity to progress the workflow to Schedule activity.

b. Applying hold at the time of order entry

Apply hold once the line is saved and when booking “Could not Schedule. Line on Hold” processing message pops up. Please apply the hold which is not an workflow activity-specific hold.

Note: If the hold is specific to workflow activity, then the Workflow Activity attached should be 'Line Scheduling' for Workflow Item 'OM Order Line' or Workflow Activity attached should be 'Book Order' for Workflow Item 'OM Order Header'

Sample Test case showing the above

1. Create an Order and apply the hold at the time of order entry.

2. Book the Order following error message pops up

Error message: “Could not Schedule. Line on Hold”

See the Order is Booked but Schedule Ship Date is not populated (Scheduling not done) and Order Line is on Hold.

Work flow status diagram shows

Once the hold is released, do Actions -> Progress Order on the line, or run the Schedule Order concurrent request to schedule the Order.

Note: “OM: Schedule Line on Hold” should be set to No. If the profile is Yes, then the workflow would complete the scheduling activity.

APPLY/RELEASE HOLDS

-----------------------------

HOLDS and RESPONSIBILITIES

Holds can be applied and released by responsibilities.

When you define a hold, you also define which responsibility can apply the hold

and which responsibility can release the hold.

EXAMPLE I:

Create hold called Management Review

Assign Order Management Super User the ability to Apply the hold.

Result: Order Management Super User can Apply the hold, but no one can Release

the hold.

EXAMPLE II:

Create hold called Management Review

Assign Order Management Super User the ability to Release the hold.

Result - No one can Apply the hold and only Order Management Super User can

Release the hold.

Thus, if you are going to assign a responsibility to Apply a hold,

you will need to create a corresponding entry with a responsibility to Release

the hold if you want to Release the hold; if you are going to assign a

responsibilty to Release a hold, you will need to create a corresponding entry

with a responsibility to Apply the hold if you want to Apply the hold.

If you do not assign any responsibility to Apply or Release the hold, then

any responsibility can Apply the hold and any responsibility can Release the

hold.

APPLY HOLDS

In order to Apply a hold, you must define a Hold Source.

A hold source has criteria i.e. specific order number, customer, item, etc.

Additionally, the Hold Source can be applied immediately and to existing/future

orders.

To define a hold source:

1. Navigate to the Apply Holds window by selecting Create Hold Sources from

the Tools menu. The Apply Holds window displays.

2. Select the Name of the hold source in the Criteria tabbed region.

3. Enter the Hold Criteria (up to two criteria can be used)

.....Customer--applies holds source to orders specific customers.

.....Customer Site--applies holds to orders or returns specific customer sites.

.....Warehouse--applies holds to orders or returns specific warehouses.

.....Item--applies holds to orders or returns specific to an item.

.....Order--applies holds to orders or returns specific to an order.

4. Select the Criteria Value.

5. Navigate to the Hold Name tabbed region.

6. Enter the Hold Name of the hold source.

7. Optionally, define the Hold Until Date, which is the date when the hold is

released automatically.

8. Optionally, enter a Hold Comment.

9. Enable the Hold Future Orders/Lines check box to activate the hold later to

new orders and returns that satisfy the hold criteria. Enable the Hold Existing

Orders/Lines check box to activate the hold for existing orders or returns only

that satisfy the hold criteria. Enable both options to place holds on future and

existing orders and returns.

10. Choose the Apply Holds button to create the hold source.

WAYS TO APPLY HOLDS:

A. To apply a hold to a single existing order or return:

1. Navigate to the Sales Orders window and query the order or return you want to

apply the hold.

2. Choose the Actions button and select Apply Hold.

3. In the Apply Holds window, select the Hold Name in the Hold Name tabbed

region.

4. Optionally, define the Hold Until Date; that is, the date when the hold is

released automatically.

5. Optionally, enter a Hold Comment.

6. Choose the Apply Holds button.

B. To apply a hold to multiple orders or returns:

1. Navigate to the Order Organizer window and query the order or return you

want to apply the hold.

2. Multi-select all orders and returns you want to apply the hold.

3. Choose the Actions button and select Apply Hold.

4. In the Apply Holds window, select the Hold Name in the Hold Name tabbed

region.

5. Optionally, define the Hold Until Date; that is, the date when the hold is

released automatically.

6. Optionally, enter a Hold Comment.

7. Choose the Apply Holds button.

C. To apply a hold to a specific order line or return line:

1. Navigate to the Sales Orders window and query the order or return line you

want to apply the hold.

2. Navigate to the Line Items tabbed region and select the order or return line you

want to apply the hold.

3. Choose the Actions button and select Apply Hold.

4. In the Apply Holds window, select the Hold Name in the Hold Name tabbed

region.

5. Optionally, define the Hold Until Date; that is, the date when the hold is

released automatically.

6. Optionally, enter a Hold Comment.

7. Choose the Apply Holds button.

C. To apply a hold to multiple order lines or return lines:

1. Navigate to the Order Organizer window and query the order or return you

want to apply the hold.

2. Navigate to the Line tabbed region.

3. Multi-select the lines you want to apply the hold.

4. Choose the Actions button and select Apply Hold.

5. In the Apply Holds window, select the Hold Name in the Hold Name tabbed

region.

6. Optionally, define the Hold Until Date; that is, the date when the hold is

released automatically.

7. Optionally, enter a Hold Comment.

8. Choose the Apply Holds button.

RELEASE HOLDS

Holds can be released via the Sales Order Window, Find Orders window in the

Order Organizer or via the Release Expired Hold concurrent program.

A. To view or release a hold source:

1. Navigate to the Find Orders window in the Order Organizer.

2. Enter search criteria, including the hold criteria and value or the name of the

hold.

3. Choose the Hold Sources button to query the hold sources that meet your

search criteria.

4. Multi-select the orders or lines that you want to release.

5. Select the Reason for the release.

B. To release a single existing order or return:

1. Navigate to the Sales Orders window and query the order or return you want to

release the hold.

2. Choose the Actions button and select Release Holds.

3. Multi-select the holds that you want to release.

4. Select the release Reason for the hold.

5. Optionally, enter a Comment.

6. Choose the Release button.

7. Save your work.

C. To release a specific order line or return line:

1. Navigate to the Sales Orders window and query the order or return line you

want to release.

2. Navigate to the Line Items tabbed region and select the order or return line you

want to release.

3. Choose the Actions button and select Release Holds.

4. Multi-select the holds that you want to release.

5. Enter the Release name.

6. Select the Reason for the release.

7. Optionally, enter a Comment.

8. Choose the Release button.

9. Save your work.

D. To release multiple orders or returns:

1. Navigate to the Orders Organizer window and query the order or return you

want to release.

2. Multi-select all orders and returns you want to release.

3. Choose the Actions button and select Release Holds.

4. Multi-select the holds that you want to release.

5. Enter the Release name.

6. Select the Reason for the release.

7. Optionally, enter a Comment.

8. Choose the Release button.

9. Save your work.

E.  To release multiple order lines or return lines:

1. Navigate to the Orders Organizer window and query the order or return you

want to apply the hold.

2. Navigate to the Line Items tabbed region.

3. Multi-select the lines you want to release.

4. Choose the Actions button and select Release Holds.

5. Enter the Release name.

6. Select the Reason for the release.

7. Optionally, enter a Comment.

8. Choose the Release button.

9. Save your work.

Additionally, Order Management provides the ability to release all expired holds

by effectivity date. The concurrent program is called Release Expired Hold; there

are no enterable input parameters for this program. When the concurrent program,

Release Expired Hold, is called, all expired holds with an ending Effectivity

Date less than or equal to the system date will automatically be released.

HOLD HISTORY

To view hold history:

1. Navigate to the Sales Orders window and query the order or return you want to

view.

2. Choose the Actions button and select Additional Order Information.

3. Select the Holds tabbed region to view hold history information.

The Additional Order Information window displays the hold history

information. The window displays all order level and line level holds for the

order.

.....For an order level hold, the At field will display the text Order.

Applying Holds

.....For a line level hold, the At field will display order line number.

RELATED DOCUMENTS

-----------------

Oracle Order Management User’s Guide for Release 11i User’s Guide,

Part No. A7702

FREQUENTLY ASKED QUESTIONS

-----------------------------

1. What are the Item Attributes that affect Drop Ship orders?

Inventory: Transactable

Purchasing: Purchased, Purchasable, List Price (must be entered)

Receiving: Receipt Routing set to direct

Order Entry: Customer Ordered, Customers Enabled, OE Transactable, Shippable

NOTE: With family Pack G and lower only standard items can be drop shipped.

With family pack H and higher, kits and models can be drop shipped.

Please review metalink note 113636.1 for item setup.

2. How do I drop ship across operating units?

Release 11i does not currently support this functionality.

3. How are over/under shipments handled in drop shipment?

If part of a drop-ship line ships and you do not wish to fulfill the

remaining quantity, cancel the line. If the quantity shipped is less than

the quantity ordered, the sales order line will be split into two lines. The

first line will display the quantity shipped and the second line will display

the backordered quantity. Over-shipments must also be handled manually. If the

Supplier ships more than the ordered quantity, you can bill your customer for

the additional quantity or request that they return the item.

Use the Sales Order and Purchase Order Discrepancy Report to view differences

between your drop-ship sales orders and their associated purchase

requisitions and orders.

4. When the Requisition Import parameter 'Initiate Approval after Import' is

set to 'No', the requisition still gets imported in an approved status.

Why is this?

The Purchase Release program in the OM module ignores the setting of this

parameter. When it is populating the data in the po_requisitions_interface

table the field authorization_status is hardcoded with the value 'APPROVED'.

Thus in the case of drop shipments, the requisition always gets imported in

an approved status. The reason for this is because the sales order has

already been 'purchase released'.

5. Did any drop-ship functionality change from R11 to R11i?

In R11i drop shipping is Workflow enabled. The OM Order Line Workflow calls

the 'Create Supply - Line' process. This process has a function called

'Branch on Source Type' which will detect an item with a Source Type of

'External' and set the line to a 'Purchase Release - Deferred' status.

Then when the Workflow Background processor picks up the line, it will

initiate the Purchase Release process to write the records to the interface

table. After the Requisition Import is successfully run, the sales order

line status will change to 'Awaiting Receipt'.

Another big change in R11i is in the way locations are maintained. You no

longer need to define a separate Purchasing Location for each customer

Ship-To location, as was necessary in R11. Purchasing now uses a view a the

HZ and HR tables to derive the locations.

6. How can I view the Purchasing information for a drop ship order?

After the requisition import successfully pulls in the data from the

PO_REQUISITIONS_INTERFACE_ALL table, and creates the internal requisition,

this information is available from the Sales Orders form as follows:

Select the sales order line with the externally sourced item and hit

Actions -> Additional Line Information

Select the Drop Ship tab to view the data

7. Does the Purchase Release concurrent program still exist?

Yes, this program can be run from the Orders, Returns -> Purchase Release

menu. If there are holds which prevent the order from progressing through

the Workflow normally after the order is booked, these holds must be

manually released. Then the externally sourced lines can be sent to

Purchasing by running the concurrent program. Alternatively, you can use

the Actions -> Progress Order button to push the lines through to the

PO_REQUISTIONS_INTERFACE_ALL table for Requisition Import to pick them up.

8. Can you drop ship kits?

You can drop ship kits in family pack H and higher.

9. You are trying to split a line on a drop ship order line after purchase

release; you receive the message:

You are not allowed to split order line because Line is released to purchasing

SO AND CUSTOMER AND RECEIPTS RELATED QUERIES

--- find OUT the customer, line item, ordered qty AND price info OF the ORDER :

SELECT h.order_number, org.NAME customer_name, h.ordered_date order_date,

ot.NAME order_type, s.NAME sales_rep, l.line_id, l.line_number,

l.inventory_item_id, si.segment1, l.ordered_quantity,

l.unit_selling_price,

NVL (l.ordered_quantity, 0) * NVL (l.unit_selling_price, 0) amount,

h.transactional_curr_code currency_code

FROM ra_salesreps s,

oe_transaction_types_tl ot,

oe_sold_to_orgs_v org,

mtl_system_items_vl si,

oe_order_lines_all l,

oe_order_headers_all h

WHERE h.order_number = 14463

AND h.org_id = 204

AND l.header_id = h.header_id

AND h.sold_to_org_id = org.organization_id

AND (h.cancelled_flag IS NULL OR h.cancelled_flag = 'N')

AND h.open_flag = 'Y'

AND l.open_flag = 'Y'

AND l.service_reference_line_id IS NULL

AND l.inventory_item_id = si.inventory_item_id

AND NVL (si.organization_id, 0) = 204 --Item master orgn

AND h.order_type_id = ot.transaction_type_id

AND h.salesrep_id = s.salesrep_id

AND h.org_id = s.org_id

ORDER BY l.line_id

/

--find customer, ship TO AND bill TO information OF an ORDER :

SELECT h.order_number, c.NAME customer_name, lk1.meaning freight_terms,

lk2.meaning fob, s.location_code ship_location_code,

s.address_line_1 ship_address1, s.address_line_2 ship_address2,

s.state ship_state, s.postal_code ship_zip, s.country ship_country,

b.location_code bill_location_code, b.address_line_1 bill_address1,

b.address_line_2 bill_address2, b.country bill_country

FROM ar_lookups lk2,

oe_lookups lk1,

oe_sold_to_orgs_v c,

oe_invoice_to_orgs_v b,

oe_ship_to_orgs_v s,

oe_order_headers_all h

WHERE h.order_number = '123'

AND h.org_id = '204'

AND h.ship_to_org_id = s.organization_id

AND h.invoice_to_org_id = b.organization_id

AND h.sold_to_org_id = c.organization_id

AND h.freight_terms_code = lk1.lookup_code(+)

AND lk1.lookup_type(+) = 'FREIGHT_TERMS'

AND lk2.lookup_code(+) = h.fob_point_code

AND lk2.lookup_type(+) = 'FOB'

/

-- find OUT ORDER AND line hold information :

SELECT ho.NAME hold_name, hs.hold_until_date, hs.hold_comment,

h.order_number, oh.header_id, oh.line_id, oh.order_hold_id,

l.item_identifier_type, l.inventory_item_id, l.ordered_item

FROM oe_order_holds_all oh,

oe_order_lines_all l,

oe_order_headers_all h,

oe_hold_definitions ho,

oe_hold_sources_all hs

WHERE h.order_number = '1234'

AND oh.header_id = h.header_id

AND (h.cancelled_flag IS NULL OR h.cancelled_flag = 'N')

AND h.open_flag = 'Y'

AND oh.hold_source_id = hs.hold_source_id

AND hs.hold_id = ho.hold_id

AND h.header_id = l.header_id(+)

AND l.open_flag = 'Y'

AND l.line_id = NVL (oh.line_id, l.line_id)

AND l.service_reference_line_id IS NULL

AND oh.hold_release_id IS NULL

AND NVL (h.org_id, 0) = '204'

AND NVL (l.org_id, 0) = NVL (h.org_id, 0)

ORDER BY ho.NAME, h.order_number

/

---find freight related info OF ORDER viz: freight carrier, ship method AND service LEVEL :

SELECT h.order_number, h.shipping_method_code, wc.carrier_name,

wcsm.service_level, wcsm.freight_code

FROM wsh_carrier_ship_methods_v wcsm,

wsh_carriers_v wc,

oe_order_headers_all h

WHERE h.order_number = 14463

AND h.org_id = 204

AND h.shipping_method_code = wcsm.ship_method_code(+)

AND NVL (wcsm.organization_id(+), 0) = 204 --Master Organization

AND wcsm.freight_code = wc.freight_code(+)

ORDER BY h.order_number

/

--find price discounts AND surcharges ON ORDER lines :

SELECT h.order_number, l.line_number, pa.list_line_type_code,

pa.arithmetic_operator, pa.operand,

DECODE (pa.modifier_level_code,

'ORDER', l.unit_list_price

* l.ordered_quantity

* pa.operand

* SIGN (pa.adjusted_amount)

/ 100,

(pa.adjusted_amount * NVL (l.ordered_quantity, 0))

) discount_amt

FROM qp_list_headers_vl lh,

oe_price_adjustments pa,

oe_order_lines_all l,

oe_order_headers_all h

WHERE h.order_number = '12345'

AND h.header_id = l.header_id

AND h.org_id = l.org_id

AND h.header_id = pa.header_id

AND l.line_id = pa.line_id(+)

AND pa.list_header_id = lh.list_header_id

AND ( pa.list_line_type_code = 'DIS'

OR pa.list_line_type_code = 'SUR'

OR pa.list_line_type_code = 'PBH'

)

AND pa.applied_flag = 'Y'

AND NOT EXISTS (

SELECT 'X'

FROM oe_price_adj_assocs pas, oe_price_adjustments pa1

WHERE pas.rltd_price_adj_id = pa.price_adjustment_id

AND pa1.price_adjustment_id = pas.price_adjustment_id

AND pa1.list_line_type_code = 'PBH')

ORDER BY l.line_id

/

a: Qp_list_headers_vl IS VIEW based ON qp_list_headers_b AND qp_list_headers_tl TABLES.

-- find freight charges ON ORDER lines :

SELECT header_id, line_id, charge_id, charge_name, charge_amount,

currency_code, invoiced_flag, interco_invoiced_flag, org_id,

source_system_code, estimated_flag, invoiced_amount

FROM oe_charge_lines_v

WHERE header_id = (SELECT header_id

FROM oe_order_headers_all

WHERE order_number = '12345')

ORDER BY line_id

/

a: The OE_CHARGE_LINES_V VIEW IS based ON oe_price_adjustments, oe_order_headers_all ANDoe_order_lines_all FOR FREIGHT CHARGES.

---SALES tax rate FOR State 'xx' :

SELECT DISTINCT lv.parent_segment_id, lc.location_id_segment_1,

location_segment_user_value, lr.from_postal_code,

lr.to_postal_code, location_segment_value, lr.tax_rate

FROM ar_location_rates lr,

ar_location_combinations lc,

ar_location_values lv

WHERE lv.location_segment_user_value = 'xx' --State name

AND lv.location_segment_id = lc.location_id_segment_1

AND lv.location_structure_id = lc.location_structure_id

AND lc.location_structure_id = '101'

AND lv.location_segment_id = lr.location_segment_id

ORDER BY 1

/

---TABLE ar_sales_tax contains location wise total tax- rates WITH tax break up ..

SELECT DISTINCT location_id, rate_context, tax_rate, location1_rate,

location2_rate, location3_rate, from_postal_code,

to_postal_code

FROM ar_sales_tax

WHERE location_id = 1000 AND enabled_flag = 'Y'

/

-- find OUT the shipper info :

/* Formatted on 2010/08/24 11:32 (Formatter Plus v4.8.0) */

SELECT wnd.delivery_id delivery_id, SUBSTRB (party.party_name, 1,

50) customer,

wpb.NAME batch_name,

wsh_util_core.get_location_description

(wnd.initial_pickup_location_id,

'NEW UI CODE'

) ship_from,

wsh_util_core.get_location_description

(wnd.ultimate_dropoff_location_id,

'NEW UI CODE'

) ship_to,

wnd.initial_pickup_date pickup_date,

wnd.ultimate_dropoff_date dropoff_date, lv.meaning ship_method,

wnd.waybill waybill, wnd.gross_weight gross_weight,

wnd.weight_uom_code uom, wnd.status_code, we.MESSAGE

FROM wsh_new_deliveries wnd,

wsh_picking_batches wpb,

wsh_exceptions we,

fnd_lookup_values_vl lv,

hz_cust_accounts cust_acct,

hz_parties party

WHERE wnd.delivery_id = '123'

AND wpb.batch_id = wnd.batch_id

AND we.delivery_id(+) = wnd.delivery_id

AND we.exception_name(+) = 'WSH_BATCH_MESSAGE'

AND lv.lookup_code(+) = wpb.ship_method_code

AND lv.lookup_type(+) = 'SHIP_METHOD'

AND lv.view_application_id(+) = '1'

AND cust_acct.cust_account_id(+) = wnd.customer_id

AND party.party_id(+) = cust_acct.party_id

/

-- find OUT shipper detail info :

SELECT wnd.delivery_id, wnd.NAME delivery_name,

wdd.source_header_number so_order_number,

oola.line_number so_line_number, wdd.source_header_id so_header_id,

wdd.source_line_id so_line_id, wdd.shipping_instructions,

wdd.inventory_item_id, wdd.requested_quantity_uom,

msi.description item_description, msi.revision_qty_control_code,

wdd.ship_method_code carrier, wdd.shipment_priority_code priority,

wdd.organization_id, wnd.initial_pickup_location_id,

wdd.released_status, wdd.source_code

FROM mtl_system_items_vl msi,

oe_order_lines_all oola,

wsh_delivery_details wdd,

wsh_delivery_assignments wda,

wsh_new_deliveries wnd

WHERE wnd.delivery_id = '123'

AND wda.delivery_id = wnd.delivery_id(+)

AND wdd.delivery_detail_id = wda.delivery_detail_id

AND wdd.inventory_item_id = msi.inventory_item_id(+)

AND wdd.organization_id = msi.organization_id(+)

AND wdd.source_line_id = oola.line_id

AND wdd.source_header_id = oola.header_id

/

---find OUT Move ORDER line details :

SELECT wnd.delivery_id, wnd.NAME delivery_name,

wnd.initial_pickup_location_id, mtrh.request_number mo_number,

mtrl.line_number mo_line_number, mtrl.line_id mo_line_id,

mtrl.from_subinventory_code, mtrl.to_subinventory_code,

mtrl.lot_number, mtrl.serial_number_start, mtrl.serial_number_end,

mtrl.uom_code, mtrl.quantity, mtrl.quantity_delivered,

mtrl.quantity_detailed, wdd.source_header_number so_order_number,

oola.line_number so_line_number, wdd.source_header_id so_header_id,

wdd.source_line_id so_line_id, wdd.shipping_instructions,

wdd.inventory_item_id, wdd.requested_quantity_uom,

msi.description item_description, msi.revision_qty_control_code,

wdd.ship_method_code carrier, wdd.shipment_priority_code priority,

wdd.organization_id, wdd.released_status, wdd.source_code

FROM mtl_system_items_vl msi,

oe_order_lines_all oola,

mtl_txn_request_lines mtrl,

mtl_txn_request_headers mtrh,

wsh_delivery_details wdd,

wsh_delivery_assignments wda,

wsh_new_deliveries wnd

WHERE wnd.delivery_id = '123'

AND wda.delivery_id = wnd.delivery_id(+)

AND wdd.delivery_detail_id = wda.delivery_detail_id

AND wdd.move_order_line_id = mtrl.line_id

AND mtrl.header_id = mtrh.header_id

AND wdd.inventory_item_id = msi.inventory_item_id(+)

AND wdd.organization_id = msi.organization_id(+)

AND wdd.source_line_id = oola.line_id

AND wdd.source_header_id = oola.header_id

/

--- find Bill OF Lading info OF the Delivery :

SELECT wnd.delivery_id delivery_id, wdi.sequence_number bol_number,

wdi.bol_notify_party, wdi.port_of_loading, wdi.port_of_discharge,

wnd.waybill waybill, wnd.gross_weight gross_weight,

wnd.weight_uom_code uom, wnd.status_code

FROM wsh_new_deliveries wnd,

wsh_delivery_legs wdl,

wsh_document_instances wdi

WHERE wnd.delivery_id = '123'

AND wnd.delivery_id = wdl.delivery_id(+)

AND wdi.entity_id(+) = wdl.delivery_leg_id

AND wdi.entity_name(+) = 'WSH_DELIVERY_LEGS'

AND wdi.document_type(+) = 'BOL'

AND wdi.status(+) <> 'CANCELLED'

/

--- find delivery leg AND pick up STOP info :

SELECT wt.trip_id, wt.NAME, wt.status_code, wt.vehicle_item_id,

wt.vehicle_number, wt.carrier_id, wt.ship_method_code, wts.stop_id,

wts.stop_location_id, wts.status_code, wts.stop_sequence_number,

wts.planned_arrival_date, wts.planned_departure_date,

wts.actual_arrival_date, wts.actual_departure_date,

wts.departure_net_weight, wts.weight_uom_code, wdl.delivery_leg_id,

wdl.delivery_id, wdl.pick_up_stop_id, wdl.drop_off_stop_id,

wdl.sequence_number, wdl.loading_order_flag, wdl.shipper_title,

wdl.shipper_phone

FROM wsh_trips wt, wsh_trip_stops wts, wsh_delivery_legs wdl

WHERE wdl.delivery_id = '123'

AND wts.stop_id = wdl.pick_up_stop_id

AND wts.trip_id = wt.trip_id;