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