Thursday, August 4, 2011

Sales Order , Customer and Receipts Related Quries

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

Best Blogger TipsGet Flower Effect