Order Delivery details query

SELECT DISTINCT oeh.flow_status_code header_status,
  oel.flow_status_code line_status,
  DECODE (wdd.released_status, 'S', 'SUBMITTED FOR RELEASE', 'R', 'READY TO RELEASE', 'B', 'BACKORDERED', 'Y', 'STAGED', 'C', 'SHIPPED', 'D', 'CANCELLED') released_status,
  NVL(oel.invoice_interface_status_code, 'NO') ar_interfaced,
  oeh.creation_date,
  (SELECT user_name FROM apps.fnd_user WHERE user_id = oeh.created_by
  ) created_by,
  (SELECT user_name FROM apps.fnd_user WHERE user_id = oeh.last_updated_by
  ) last_upd_by,
  (SELECT name
  FROM apps.oe_order_sources
  WHERE order_source_id = oeh.order_source_id
  ) hdr_source,
  oeh.header_id,
  oeh.org_id header_org_id,
  (SELECT hou.name
  FROM hr_operating_units hou
  WHERE hou.organization_id = oeh.org_id
  ) header_ou,
  oeh.order_number,
  (SELECT ott.name
  FROM apps.oe_transaction_types_tl ott
  WHERE ott.transaction_type_id = oeh.order_type_id
  ) order_type,
  oeh.cust_po_number,
  oeh.fob_point_code,
  oeh.shipping_method_code,
  (SELECT name
  FROM apps.ra_salesreps_all rsa
  WHERE rsa.salesrep_id = oeh.salesrep_id
  ) salesrep_name,
  oeh.transactional_curr_code trx_curr,
  oeh.ship_to_contact_id, -- contact_id in ra_contacts, ship to contact
  oeh.sold_to_contact_id, -- contact_id in ra_contacts, customer level contact
  oeh.ship_to_org_id,     -- site_use_id in hz_cust_site_uses_all
  oeh.sold_to_org_id,     -- cust_account_id in hz_cust_accounts
  oel.creation_date line_creation_date,
  oel.last_update_date line_upd_date,
  (SELECT user_name FROM apps.fnd_user WHERE user_id = oel.last_updated_by
  ) line_upd_by,
  oel.line_id,
  RTRIM ( oel.line_number
  || '.'
  || oel.shipment_number
  || '.'
  || oel.option_number
  || '.'
  || oel.component_number, '.') line_number,
  oel.org_id line_org_id,
  (SELECT hou.name
  FROM hr_operating_units hou
  WHERE hou.organization_id = oel.org_id
  ) line_ou,
  (SELECT ROUND (SUM (cic.item_cost), 2)
  FROM apps.cst_item_costs cic
  WHERE cic.inventory_item_id = MSI.inventory_item_id
  AND cic.organization_id     = MSI.organization_id
  AND cic.cost_type_id        = 1
  ) cogs,
  oel.unit_selling_price usp,
  oel.tax_code,
  oel.tax_value,
  oel.ordered_quantity ord_qty,
  (SELECT ott.name
  FROM apps.oe_transaction_types_tl ott
  WHERE ott.transaction_type_id = oel.line_type_id
  ) line_type,
  oel.schedule_ship_date,
  oel.promise_date,
  oel.latest_acceptable_date latest_acc_date,
  oel.request_date,
  oel.request_id,
  (SELECT a.order_number
  FROM apps.oe_order_headers_all a,
    apps.oe_order_lines_all b
  WHERE a.header_id = b.header_id
  AND b.line_id     = oel.attribute17
  ) icso_number,
  oel.attribute17 icso_line_id,
  (SELECT RTRIM ( oola.line_number
    || '.'
    || oola.shipment_number
    || '.'
    || oola.option_number
    || '.'
    || oola.component_number, '.')
  FROM apps.oe_order_lines_all oola
  WHERE oola.line_id = oel.attribute17
  ) icso_line_num,
  NVL (oel.attribute18, 'EXTERNAL SO') attribute18,
  oel.attribute19 pto_model,
  oel.global_attribute1 flag_3A4,
  oeh.Global_Attribute3 hdr_attr3,
  oel.Global_Attribute3 line_attr3,
  oeh.Global_Attribute4 hdr_attr4,
  oel.Global_Attribute4 line_attr4,
  oel.booked_flag,
  oel.open_flag,
  oeh.booked_date,
  (SELECT set_name FROM apps.oe_sets WHERE set_id = oel.ship_set_id
  ) ship_set_name,
  oel.subinventory,
  oel.top_model_line_id,
  msi.item_type,
  msi.inventory_item_id item_id,
  msi.segment1 item_num,
  msi.shippable_item_flag,
  (SELECT ood.organization_name
  FROM apps.org_organization_definitions ood
  WHERE ood.organization_id = msi.default_shipping_org
  ) default_shipping_org,
  oel.ship_from_org_id,
  (SELECT ood.organization_name
  FROM apps.org_organization_definitions ood
  WHERE ood.organization_id = oel.ship_from_org_id
  ) ship_from_org,
  wdd.last_update_date del_upd_date,
  (SELECT user_name FROM apps.fnd_user WHERE user_id = wdd.last_updated_by
  ) del_upd_by,
  wda.delivery_id,
  wdd.delivery_detail_id,
  wdd.move_order_line_id,
  wdd.request_id del_request_id,
  mtrl.from_subinventory_code from_subinv,
  mtrl.to_subinventory_code to_subinv,
  mtrl.quantity_delivered qty_delivered,
  mtrl.pick_slip_number,
  mtrl.pick_slip_date,
  oel.actual_shipment_date
FROM apps.oe_order_headers_all oeh,
  apps.oe_order_lines_all oel,
  wsh.wsh_delivery_details wdd,
  apps.wsh_delivery_assignments wda,
  apps.mtl_system_items msi,
  apps.mtl_txn_request_lines mtrl
WHERE oeh.header_id        = oel.header_id
AND oel.line_id            = wdd.source_line_id(+)
AND wdd.delivery_detail_id = wda.delivery_detail_id(+)
AND msi.inventory_item_id  = oel.inventory_item_id
AND oel.ship_from_org_id   = msi.organization_id
AND mtrl.line_id(+)        = wdd.move_order_line_id
AND oeh.order_number      IN ('1885000297')
ORDER BY line_number,
  line_id,
  delivery_id

Post a Comment