Useful Order
Management Queries
1) To know the details of the Order
Header.
--------------------------------------------------------------
SELECT * from oe_order_headers_all WHERE ORDER_NUMBER = ;
2) To know the line details of the Order lines:-
----------------------------------------------------------------
SELECT * FROM OE_ORDER_LINES_ALL WHERE HEADER_ID =
3) To know the reason of cancellation.
-------------------------------------------------------
SELECT reason_code
FROM
oe_order_lines_history
WHERE line_id =
AND
hist_type_code = 'CANCELLATION';
SELECT attribute1 pos_reason_code
FROM FND_LOOKUP_VALUES
WHERE lookup_type = p_lookup_type
AND LANGUAGE = 'US'
AND LOOKUP_CODE = ;
4) if some discount or adjustment is done for the item in the order line then
we can know the original price and selling price.
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT SUM(DECODE(line_id, , unit_selling_price,
DECODE(return_attribute5, , - unit_selling_price, 0))) unit_selling_price,
SUM(DECODE(line_id, , unit_list_price, 0)) unit_list_price
FROM
oe_order_lines_all
WHERE header_id =
AND
Nvl(cancelled_flag, 'N') = 'N'
AND booked_flag
= 'Y'
AND
attribute7 IS Not Null;
5) To know the number of the days from the cash receipt day for any particular
line.
-----------------------------------------------------------------------------------------------------------------------
SELECT floor(sysdate-to_date(To_Char(ar.receipt_date, 'dd-mon-yyyy')))
date_difference
FROM oe_order_lines_all ol,
ar_cash_receipts_all ar
WHERE ol.header_id =
AND ol.line_id=
AND
ol.attribute7=ar.cash_receipt_id
AND
ar.attribute1=;
6) To know the receipt ID from the based on the header_id (HBC Specfic).
--------------------------------------------------------------------------------------------------------
select distinct attribute1 from oe_price_adjustments where HEADER_ID =
7) To know the Location exist or not.
------------------------------------------------------
SELECT 1 FROM hr_organization_units_v
WHERE location_code = Trim();
8) Deposit_payment_made
-------------------------------------
The following query is useful to know whether Deposit is made or not.
-----------------------------------------------------------------------------------------------------
SELECT 1 FROM ar_cash_receipts_all r, ar_receipt_methods m
WHERE r.attribute1=to_char('')
AND r.receipt_method_id=m.receipt_method_id
AND m.name='POS_DEPOSIT';
8.1) Deposit Payment and Order status and Header ID. (HBC Specfic).
-----------------------------------------------------------------------------------------------------
SELECT header_id "Header ID",
flow_status_code "Status",
attribute3 "deposit_amount"
FROM oe_order_headers_all
WHERE order_number = 1029243
AND flow_status_code in
('BOOKED', 'CANCELLED');
8.2) Getting the Line information of the order.
---------------------------------------------------------------
SELECT line_id,
LINE_NUMBER,
Nvl(cancelled_flag, 'N') cancelled_flag,
open_flag,
attribute7
receipt_id,
line_category_code,
global_attribute10,
Nvl(attribute10, 'Y') pos_elig_flag,
return_attribute5 closed_line_adjustment
FROM oe_order_lines_all
WHERE header_id = ;
8.3) If the Deposit Money is refunded, that can be found from the following
query:-
-------------------------------------------------------------------------------------------------------------------
SELECT 'Y'
FROM oe_order_headers_all oeoh
WHERE oeoh.header_id =
AND oeoh.flow_status_code =
'CANCELLED'
AND oeoh.attribute3 IS Not
Null
AND oeoh.attribute5 IS NOT
Null
AND oeoh.global_attribute20 IS
Null
AND NOT EXISTS
(SELECT 1
FROM oe_order_lines_all oeol
WHERE oeol.header_id = oeoh.header_id
AND oeol.attribute7 IS NOT Null
AND oeol.booked_flag = 'Y');
9) pos_payment_made
----------------------------------
The following query is useful to know whether payment is made or not.
-----------------------------------------------------------------------------------------------------
SELECT 1 FROM ar_cash_receipts_all r, ar_receipt_methods m
WHERE r.attribute1=to_char(p_order_number)
AND r.receipt_method_id=m.receipt_method_id
AND m.name='POS_PAYMENT';
10) Adjustment Amount for this order:-
---------------------------------------------------
SELECT SUM(oepj.adjusted_amount)
FROM oe_order_lines_all oeol, oe_price_adjustments
oepj
WHERE oeol.line_id = oepj.line_id
AND oeol.attribute7 IS NOT
Null
AND Nvl(oeol.cancelled_flag,
'N') = 'N'
AND oeol.return_attribute5 IS
Null
AND oeol.open_flag = 'Y'
AND Nvl(Upper(oeol.attribute10),
'Y') = 'Y'
AND oeol.booked_flag = 'Y'
AND oepj.attribute1 IS Null
AND oeol.header_id =
11) find out unprocessed lines in ORDER LINES table:-
---------------------------------------------------------------------------
SELECT NVL(SUM(DECODE(line_category_code,'RETURN',1,0)),0),
NVL(SUM(DECODE(line_category_code,'ORDER',1,0)),0)
FROM
OE_ORDER_LINES_ALL oola
WHERE header_id=
AND
oola.BOOKED_FLAG = 'Y'
AND
oola.OPEN_FLAG = 'Y'
AND
oola.ATTRIBUTE7 IS NULL
AND
oola.CANCELLED_FLAG = 'N'
AND Nvl(Upper(oola.attribute10), 'Y') = 'Y';
12) Findout unhandled_cancels_exists or not:-
-------------------------------------------------------------
SELECT 1
FROM
oe_order_lines_all l, oe_order_headers_all h
WHERE h.header_id
= l.header_id
AND l.line_id =
AND l.attribute7
IS NOT NULL --previously processed
AND l.cancelled_flag='Y'
--now cancelled.
AND
h.order_number=
AND NOT EXISTS (
SELECT
1 FROM xxcofipos_posting_ol_history p
WHERE
p.order_line_id=l.line_id
AND
p.order_line_table_source = 'OL_CANCEL');
13) Findout the region short name with the location code:-
------------------------------------------------------------------------------
SELECT region_1
FROM
hr_organization_units_v hv
WHERE hv.location_code = ''; --Store Number
14) For initial determination of whether it is a "NEW" or a
"NON-NEW" transaction type
-----------------------------------------------------------------------------------------------------------------------
SELECT COUNT(*),
SUM(DECODE(NVL(attribute7,'xx'),'xx',0,1)) --if null then do not count : old :
--SUM(DECODE(attribute7,'Y',1,0))
-- INTO l_total_lines_count,
l_processed_lines_count
FROM OE_ORDER_LINES_ALL
WHERE header_id=;
15) find out line_category_codes for unprocessed lines in ORDER LINES table:-
------------------------------------------------------------------------------------------------------------
SELECT
NVL(SUM(DECODE(line_category_code,'RETURN',1,0)),0),
NVL(SUM(DECODE(line_category_code,'ORDER',1,0)),0)
-- INTO l_return_count, l_sale_count
FROM OE_ORDER_LINES_ALL oola
WHERE header_id=
AND oola.BOOKED_FLAG = 'Y'
AND oola.OPEN_FLAG = 'Y'
AND oola.ATTRIBUTE7 IS NULL
AND oola.CANCELLED_FLAG = 'N';
16) To know all sales rep name and other information:-
----------------------------------------------------------------------
SELECT jr.name,SALESREP_NUMBER,/* pf.attribute1 */, pf.EMAIL_ADDRESS ,
pf.PERSON_ID, pf.sex--nvl(pf.attribute1,'') --salesrep_number
FROM
jtf_rs_salesreps jr,
per_all_people_f pf
WHERE -- salesrep_id =
c_salesrep_id AND
pf.person_id = jr.person_id
AND
Nvl(status, 'A') = 'A'
AND Sysdate
BETWEEN start_date_active AND Nvl(end_date_active, Sysdate + 1);
To Find
Duplicate Item Category Code
SELECT
category_set_name, category_concat_segments, COUNT (*)
FROM mtl_category_set_valid_cats_v
WHERE (category_set_id = 1)
GROUP BY category_set_name, category_concat_segments
HAVING COUNT (*) > 1
ORDER BY category_concat_segments
Get
Number Of canceled requisition
SELECT
a.AUTHORIZATION_STATUS,(a.ORG_ID),(SELECT distinct hr.per_all_people_f.first_name||
‘ ‘|| hr.per_all_people_f.middle_names|| ‘ ‘|| hr.per_all_people_f.last_name
“Employee Name”
FROM hr.per_all_people_f
where hr.per_all_people_f.PERSON_ID in
(select employee_id from fnd_user fu where fu.user_id = a.CREATED_BY))
CREATED_BY,count(SEGMENT1 )
FROM
po_requisition_headers_all a
WHERE
a.creation_date BETWEEN TO_DATE(’01/01/2007′, ‘DD/MM/YYYY’)
and TO_DATE(’30/05/2007′, ‘DD/MM/YYYY’)
and a.AUTHORIZATION_STATUS = ‘CANCELLED’
group by a.AUTHORIZATION_STATUS,a.ORG_ID,a.CREATED_BY
Number of
line processed in Order Management
SELECT
COUNT (line_id) “Order Line Processed”
FROM oe_order_lines_all
WHERE creation_date BETWEEN TO_DATE (:Fdate, ‘DD/MM/YYYY’)
AND TO_DATE (:tdate, ‘DD/MM/YYYY’)
AND flow_status_code = ‘CLOSED’;
To Check
Item Catogry For Inventory master (No Of Segments May Varry)
SELECT
ood.organization_name,
segment1|| ‘-’|| segment2|| ‘-’|| segment3 catgory
FROM org_organization_definitions ood,
mtl_categories_vl mcv,
mtl_category_sets mcs
WHERE mcs.structure_id = mcv.structure_id
ORDER BY ood.organization_name
Check
Locators for inventory Inventory Org Wise(Number of segment may varry)
SELECT
mil.segment1 loc_seg1, mil.segment11 loc_seg11, mil.segment2 loc_seg2,
mil.segment3 loc_seg3, mil.segment4 loc_seg4, mil.segment5 loc_seg5,
mil.segment6 loc_seg6,ood.ORGANIZATION_NAME,mil.SUBINVENTORY_CODE
FROM mtl_item_locations mil,org_organization_definitions ood
where mil.ORGANIZATION_ID = ood.ORGANIZATION_ID
Display
All Subinventories Setup
select msi.secondary_inventory_name, MSI.SECONDARY_INVENTORY_NAME
“Subinventory”, MSI.DESCRIPTION “Description”,
MSI.DISABLE_DATE “Disable Date”, msi.PICKING_ORDER “Picking Order”,
gcc1.concatenated_segments “Material Account”,
gcc2.concatenated_segments “Material Overhead Account”,
gcc3.concatenated_segments “Resource Account”,
gcc4.concatenated_segments “Overhead Account”,
gcc5.concatenated_segments “Outside Processing Account”,
gcc6.concatenated_segments “Expense Account”,
gcc7.concatenated_segments “Encumbrance Account”,
msi.material_overhead_account,
msi.resource_account,
msi.overhead_account,
msi.outside_processing_account,
msi.expense_account,
msi.encumbrance_account
from mtl_secondary_inventories msi,
gl_code_combinations_kfv gcc1,
gl_code_combinations_kfv gcc2,
gl_code_combinations_kfv gcc3,
gl_code_combinations_kfv gcc4,
gl_code_combinations_kfv gcc5,
gl_code_combinations_kfv gcc6,
gl_code_combinations_kfv gcc7
where msi.material_account = gcc1.CODE_COMBINATION_ID(+)
and msi.material_overhead_account = gcc2.CODE_COMBINATION_ID(+)
and msi.resource_account = gcc3.CODE_COMBINATION_ID(+)
and msi.overhead_account = gcc4.CODE_COMBINATION_ID(+)
and msi.outside_processing_account = gcc5.CODE_COMBINATION_ID(+)
and msi.expense_account = gcc6.CODE_COMBINATION_ID(+)
and msi.encumbrance_account = gcc7.CODE_COMBINATION_ID(+)
order by msi.secondary_inventory_name
To Select
Unit Of measure exist in ebusiness suite
select
uom_code,unit_of_measure
from mtl_units_of_measure
Query to
find out Customer Master Information. Customer Name, Account Number, Adress
etc.
select p.PARTY_NAME,ca.ACCOUNT_NUMBER,loc.address1,loc.address2,loc.address3,loc.city,loc.postal_code,
loc.country,ca.CUST_ACCOUNT_ID
from apps.ra_customer_trx_all I,
apps.hz_cust_accounts CA,
apps.hz_parties P,
apps.hz_locations Loc,
apps.hz_cust_site_uses_all CSU,
apps.hz_cust_acct_sites_all CAS,
apps.hz_party_sites PS
where I.COMPLETE_FLAG =’Y’
and I.bill_TO_CUSTOMER_ID= CA.CUST_ACCOUNT_ID
and ca.PARTY_ID=p.PARTY_ID
and I.bill_to_site_use_id=csu.site_use_id
and csu.CUST_ACCT_SITE_ID=cas.CUST_ACCT_SITE_ID
and cas.PARTY_SITE_ID=ps.party_site_id
and ps.location_id=loc.LOCATION_ID
Query to
find on Hand Quantity
select
sum(transaction_quantity) from MTL_ONHAND_QUANTITIES
where inventory_item_id=9
and organization_id=188
Qunatity
on order, Expected Deliver
select
sum(ordered_quantity),a.SCHEDULE_SHIP_DATE
from oe_order_lines_all a
where inventory_item_id=10
and ship_from_org_id=188
group by a.SCHEDULE_SHIP_DATE
Query to
find Item Code, Item Description Oracle Item Master Query
select
item, description from mtl_system_items_b
where inventory_item_id=&your_item
and organization_id=&organization_id) item
Query to
Find out On Hand Quantity of specific Item Oracle inventory
select sum(transaction_quantity) from mtl_onhand_quantity_details
where inventory_item_id=&your_item
and organization_id=&organization_id
Qty On
Order,
Expected
deivery date(select sum(ordered_quantity),
scheduled_ship_date
from oe_order_lines_all
where inventory_item_id=&your_item
and ship_from_org_id=&organization_id
group by scheduled_ship_date) order_info
–Total
Received Qty
select sum(transaction_quantity) from mtl_material_transactions
inventory_item_id=&your_item
and organization_id=&organization_id
and transaction_quantity>0)
Total
received Qty in 9 months
select
sum(transaction_quantity) from mtl_material_transactions
inventory_item_id=&your_item
and organization_id=&organization_id
and transaction_quantity>0
and transaction_date between trunc(sysdate) and trunc(sysdate-270))
Total
issued quantity in 9 months
select
sum(transaction_quantity) from mtl_material_transactions
inventory_item_id=&your_item
and organization_id=&organization_id
and transaction_quantity<0 and transaction_date between trunc(sysdate) and
trunc(sysdate-270)) tot_iss_qty_9mths, –Average monthly consumption
(select sum(transaction_quantity)/30 from mtl_material_transactions
inventory_item_id=&your_item
and organization_id=&organization_id
and transaction_quantity<0) ;
Display
all categories that the Item Belongs
SELECTunique micv.CATEGORY_SET_NAME “Category Set”,
micv.CATEGORY_SET_ID “Category Set ID”,
decode( micv.CONTROL_LEVEL,
1, ‘Master’,
2, ‘Org’,
‘Other’) “Control Level”,
micv.CATEGORY_ID “Category ID”,
micv.CATEGORY_CONCAT_SEGS “Category”
FROM
MTL_ITEM_CATEGORIES_V micv
Another
Query to Get Onhand Qty With Oranization ID, Item Code, Quantity
SELECT organization_id,
(SELECT ( msib.segment1|| ‘-’|| msib.segment2|| ‘-’|| msib.segment3|| ‘-’||
msib.segment4)
FROM mtl_system_items_b msib
WHERE msib.inventory_item_id = moq.inventory_item_id
AND msib.organization_id = moq.organization_id) “Item Code”,
(SELECT description
FROM mtl_system_items_b msib
WHERE msib.inventory_item_id =
moq.inventory_item_id
AND msib.organization_id = moq.organization_id)
“item Description”,
SUM (moq.transaction_quantity) onhandqty
FROM mtl_onhand_quantities moq
GROUP BY moq.organization_id, (moq.inventory_item_id)
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;
