Sales Order , Customer and Receipts Related Qurie

/* Formatted on 7/8/2014 9:15:36 AM (QP5 v5.115.810.9015) */
--- 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
/

/* Formatted on 7/8/2014 9:15:53 AM (QP5 v5.115.810.9015) */
--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'
/

/* Formatted on 7/8/2014 9:16:02 AM (QP5 v5.115.810.9015) */
-- 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
/

/* Formatted on 7/8/2014 9:16:12 AM (QP5 v5.115.810.9015) */
---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
/

/* Formatted on 7/8/2014 9:16:44 AM (QP5 v5.115.810.9015) */
--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.

/* Formatted on 7/8/2014 9:17:14 AM (QP5 v5.115.810.9015) */
-- 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.

/* Formatted on 7/8/2014 9:17:29 AM (QP5 v5.115.810.9015) */
---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
/
/* Formatted on 7/8/2014 9:23:22 AM (QP5 v5.115.810.9015) */
---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'
/

/* Formatted on 7/8/2014 9:23:46 AM (QP5 v5.115.810.9015) */
-- 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
/

/* Formatted on 7/8/2014 9:23:56 AM (QP5 v5.115.810.9015) */
-- 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
/

/* Formatted on 7/8/2014 9:24:10 AM (QP5 v5.115.810.9015) */
---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
/

/* Formatted on 7/8/2014 9:25:19 AM (QP5 v5.115.810.9015) */
--- 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'
/

/* Formatted on 7/8/2014 9:25:34 AM (QP5 v5.115.810.9015) */
--- 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;