Wednesday, March 27, 2013

Order-Delivery-Item Complete Details





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')
         --AND oeh.header_id = '1344613'
         --AND oeh.order_type_id <> 1037
         --AND oel.global_attribute1 = 'TRANSMITTED'
         --AND oel.org_id = 26
         -- AND oel.line_id IN (6269374)
         --AND oel.line_number = 104357064
         --AND oel.flow_status_code NOT IN ('CLOSED', 'CANCELLED')  
         --AND oel.inventory_item_id in (1928882, 1928414, 1927654, 1927835)
         --AND msi.organization_id = 26
         --AND msi.segment1 = '22013144'
         --AND msi.shippable_item_flag = 'Y'
         --AND wda.delivery_id = '2242405'
         --AND wdd.delivery_detail_id IN (969822688, 969917104, 969917105, 969917106, 969917107)
         --AND mtrl. pick_slip_number = '2246389'
ORDER BY line_number, line_id, delivery_id

2 comments:

  1. Is this query deliver the order to cash flow .i want to know that is it ok from order place to invoice generation process. Waiting for your reply.

    ReplyDelete
  2. This query gives details only from OM, INV modules, but you can join AR tables to get invoice details as well

    ReplyDelete

Note: Only a member of this blog may post a comment.