Tuesday, June 6, 2017

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 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
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
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
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
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;

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