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
Find freight charges ON ORDER lines :
a:
Qp_list_headers_vl IS VIEW based ON qp_list_headers_b AND qp_list_headers_tl
TABLES.
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 :
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_droapoff_location_id,
'NEW
UI CODE'
)
ship_to,
wnd.initial_pickup_date
pickup_date,
wnd.ultimate_droapoff_date
droapoff_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.droap_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;
No comments:
Post a Comment