Skip to main content

Oracle WMS Tasks Query

SELECT *
  FROM (SELECT wdth.transaction_temp_id,
               wdth.task_id,
               oeh.order_number,
               oel.line_number,
               msi.concatenated_segments item_number,
               'Pick',
               'Completed',
               fu1.user_name             assigned_to,
               wdth.person_id,
               mmtt.transaction_quantity,
               mmtt.transaction_uom
          FROM apps.oe_order_headers_all         oeh,
               apps.wms_dispatched_tasks_history wdth,
               apps.mtl_material_transactions    mmtt,
               apps.oe_order_lines_all           oel,
               apps.fnd_user                     fu1,
               apps.mtl_system_items_kfv         msi --,
         WHERE msi.organization_id = mmtt.organization_id
           AND msi.inventory_item_id = wdth.inventory_item_id
           AND oel.line_id = mmtt.trx_source_line_id
           AND mmtt.transaction_source_type_id = wdth.transaction_source_type_id
           AND mmtt.transaction_type_id = wdth.transaction_type_id
           AND mmtt.subinventory_code = wdth.dest_subinventory_code
           AND mmtt.move_order_line_id = wdth.move_order_line_id
           AND oeh.header_id = oel.header_id
           AND fu1.employee_id(+) = wdth.person_id
           AND wdth.transaction_id = mmtt.transaction_batch_id
        --   AND oeh.order_number = 3531000428
        UNION
        SELECT mmtt.transaction_temp_id,
               wdt.task_id,
               oeh.order_number,
               oel.line_number,
               msi.concatenated_segments item_number,
               decode(mmtt.wms_task_type,
                      1,
                      'Pick',
                      2,
                      'Putaway',
                      3,
                      'Cycle Count',
                      4,
                      'Replenish',
                      5,
                      'Move Order Transfer',
                      6,
                      'Move Order Issue',
                      7,
                      'Staging Move',
                      8,
                      'Inspection') task_type,
               flv.meaning task_status,
               fu1.user_name assigned_to,
               wdt.person_id,
               mmtt.transaction_quantity,
               mmtt.transaction_uom
          FROM apps.oe_order_headers_all           oeh,
               apps.mtl_material_transactions_temp mmtt,
               apps.wms_dispatched_tasks           wdt,
               apps.oe_order_lines_all             oel,
               apps.fnd_lookup_values              flv,
               apps.fnd_user                       fu1,
               apps.mtl_system_items_kfv           msi
         WHERE msi.organization_id = mmtt.organization_id
           AND msi.inventory_item_id = mmtt.inventory_item_id
           AND oel.line_id = mmtt.trx_source_line_id
           AND flv.lookup_type = 'WMS_TASK_STATUS'
           AND flv.lookup_code = nvl(wdt.status,
                                     nvl(mmtt.wms_task_status,
                                         1))
           AND flv.language = 'US'
           AND mmtt.transaction_source_type_id = 2
           AND wdt.transaction_temp_id(+) = mmtt.transaction_temp_id
           AND oeh.header_id = oel.header_id
           AND fu1.employee_id(+) = wdt.person_id)
 WHERE order_number = :p_order_number
 ORDER BY order_number;

 select * from apps.wms_dispatched_tasks_history
 where 1=1
 and organization_id = 1531
 ;

SELECT *
  FROM (SELECT wdth.transaction_temp_id,
               wdth.task_id, wdth.organization_id,
               oeh.order_number,
               oel.line_number,
               msi.concatenated_segments item_number,
               'Pick',
               'Completed',
               fu1.user_name             assigned_to,
               wdth.person_id,
               mmtt.transaction_quantity,
               mmtt.transaction_uom
          FROM apps.oe_order_headers_all         oeh,
               apps.wms_dispatched_tasks_history wdth,
               apps.mtl_material_transactions    mmtt,
               apps.oe_order_lines_all           oel,
               apps.fnd_user                     fu1,
               apps.mtl_system_items_kfv         msi --,
         WHERE msi.organization_id = mmtt.organization_id
           AND msi.inventory_item_id = wdth.inventory_item_id
           AND oel.line_id = mmtt.trx_source_line_id
           AND mmtt.transaction_source_type_id = wdth.transaction_source_type_id
           AND mmtt.transaction_type_id = wdth.transaction_type_id
           AND mmtt.subinventory_code = wdth.dest_subinventory_code
           AND mmtt.move_order_line_id = wdth.move_order_line_id
           AND oeh.header_id = oel.header_id
           AND fu1.employee_id(+) = wdth.person_id
           AND wdth.transaction_id = mmtt.transaction_batch_id
        --   AND oeh.order_number = 3531000428
        UNION
        SELECT mmtt.transaction_temp_id,
               wdt.task_id, wdt.organization_id,
               oeh.order_number,
               oel.line_number,
               msi.concatenated_segments item_number,
               decode(mmtt.wms_task_type,
                      1,
                      'Pick',
                      2,
                      'Putaway',
                      3,
                      'Cycle Count',
                      4,
                      'Replenish',
                      5,
                      'Move Order Transfer',
                      6,
                      'Move Order Issue',
                      7,
                      'Staging Move',
                      8,
                      'Inspection') task_type,
               flv.meaning task_status,
               fu1.user_name assigned_to,
               wdt.person_id,
               mmtt.transaction_quantity,
               mmtt.transaction_uom
          FROM apps.oe_order_headers_all           oeh,
               apps.mtl_material_transactions_temp mmtt,
               apps.wms_dispatched_tasks           wdt,
               apps.oe_order_lines_all             oel,
               apps.fnd_lookup_values              flv,
               apps.fnd_user                       fu1,
               apps.mtl_system_items_kfv           msi
         WHERE msi.organization_id = mmtt.organization_id
           AND msi.inventory_item_id = mmtt.inventory_item_id
           AND oel.line_id = mmtt.trx_source_line_id
           AND flv.lookup_type = 'WMS_TASK_STATUS'
           AND flv.lookup_code = nvl(wdt.status,
                                     nvl(mmtt.wms_task_status,
                                         1))
           AND flv.language = 'US'
           AND mmtt.transaction_source_type_id = 2
           AND wdt.transaction_temp_id(+) = mmtt.transaction_temp_id
           AND oeh.header_id = oel.header_id
           AND fu1.employee_id(+) = wdt.person_id)
 WHERE organization_id = 1533 --:p_order_number
 
 ORDER BY order_number;


Comments

Post a Comment

Popular posts from this blog

Oracle WMS Outbound Query

/*  ************************************************************************************************************          OUTBOUND METRICS           Released to Warehouse --> Staged/Pick Confirmed      ************************************************************************************************************  */  SELECT mp.organization_code "Warehouse", wda.delivery_id "Delivery", wt.name "Trip", (SELECT MEANING FROM apps.FND_LOOKUP_VALUES_VL WHERE lookup_type = 'PICK_STATUS' AND LOOKUP_CODE = wdd.released_status) "Delivery Status", CASE  WHEN wdt_extra.move_order_line_id IS NOT NULL THEN (SELECT MEANING FROM apps.FND_LOOKUP_VALUES_VL WHERE lookup_type = 'WMS_TASK_STATUS' AND LOOKUP_CODE = wdt_extra.status)  WHEN mmtt.wms_task_status IS NOT NULL THEN (SELECT MEANING FROM apps.FND_LOOKUP_VALUES_VL WHERE lookup_type = 'WMS_TASK_STATUS' AND LOOKUP_CODE = mmtt.wms_task_status) ...
Post a Comment
Read more

OracleApps OM Query

-- Order Details select --* ol.line_id, --oh.sold_to_org_id, oh.created_by, (select user_name from apps.fnd_user where user_id = oh.created_by) created_name, c.account_number, d.party_name "Cust Name", --oh.ship_to_org_id, oh.invoice_to_org_id, hl1.country "ShipCountry",hcas1.attribute17, hl2.country "BillCountry",hcas2.attribute17, oh.transactional_curr_code curr, oh.org_id, (select name from apps.hr_operating_units where organization_id = oh.org_id) "OU", oh.order_number, --oh.header_id, oh.creation_date, tt.name "OrderType", --oh.order_source_id,   oh.open_flag, oh.booked_flag,   oh.CUST_PO_NUMBER, --oh.shipping_method_code, (select wcsv.ship_method_meaning from apps.WSH_CARRIER_SERVICES_V wcsv where wcsv.ship_method_code = oh.shipping_method_code) HEADER_SHIP_METHOD, --ol.line_id, ol.line_number||'.'||ol.shipment_number "line", ol.ordered_item, --msieb.C_EXT_ATTR26 APC_WM_ST...
Post a Comment
Read more