EAM Work Order Transactions

-- ##############################################################################
--       EAM WORK ORDER TRANSACTIONS
-- ############################################################################*/

    select wta.transaction_id
         , wta.last_update_date
         , wta.creation_date
         , wta.transaction_date
         , wta.wip_entity_id
         , wta.accounting_line_type
         , wta.base_transaction_value
         , wta.primary_quantity
         , we.wip_entity_name work_order
         , gcc.segment1
         , gcc.segment2
         , gcc.segment3
         , gcc.segment4
         , gcc.segment5
         , gcc.segment6
         , wta.*
      from wip.WIP_TRANSACTION_ACCOUNTS wta
      join wip.wip_entities we         on we.wip_entity_id =      wta.wip_entity_id
 left join gl.gl_code_combinations gcc ON wta.reference_account = gcc.code_combination_id
    WHERE 1 = 1
    --  AND transaction_id IN (16798328,16798329,16798848,16800417,16803014)
      and we.wip_entity_name = 'ABC123';

    SELECT * 
      FROM WIP.WIP_TRANSACTIONS
     WHERE 1 = 1
--       AND last_update_date > '16-JUN-2016'
       AND creation_date > '01-AUG-2016'
       AND wip_entity_id = 3083864
       AND 1 = 1
  ORDER BY last_update_date DESC;

SELECT we.wip_entity_name wo
     , we.request_id
     , fu.description
     , wt.* 
  FROM wip.wip_transactions wt
     , wip.wip_entities we
     , applsys.fnd_user fu
 WHERE wt.wip_entity_id = we.wip_entity_id
   AND wt.created_by = fu.user_id
--   AND we.wip_entity_name = 'ABC123'
--   AND we.creation_date > '10-MAY-2016'
   AND wt.creation_date > '10-JUN-2016'
   AND 1 = 1;
   
select * from wip.WIP_TRANSACTION_ACCOUNTS WHERE transaction_id = 14153865;
select * from wip.WIP_COST_TXN_INTERFACE where creation_date > '10-MAR-2014';

EAM WIP Cost Transaction Interface

-- ##############################################################################
--       EAM WIP COST TRANSACTION INTERFACE
-- ############################################################################*/

select we.wip_entity_name wo_num
     , we.creation_date wo_cr_dt
     , wcti.creation_date iface_cr_dt
     , wcti.last_update_date iface_up_dt
     , wcti.source_code iface_src
     , (SELECT COUNT(*) FROM WIP.WIP_PERIOD_BALANCES wpb WHERE wpb.wip_entity_id = we.wip_entity_id group by we.wip_entity_id) wpb_count
     , decode(wcti.process_phase,1,'Resource validation',2,'Resource processing','Other') phase
     , decode(wcti.process_status,1,'Pending',3,'Error','Other') status
     , oap.period_name
     , we.description
  from WIP.WIP_COST_TXN_INTERFACE wcti
  join wip.wip_entities we      on wcti.wip_entity_id =  we.wip_entity_id
  join inv.org_acct_periods oap on wcti.acct_period_id = oap.acct_period_id
 where 1 = 1
ORDER BY wcti.creation_date;

EAM Maintenance Routings

-- ##############################################################################
--        MAINTENANCE ROUTINGS
-- ##############################################################################

-- ROUTINGS

SELECT msib.segment1
     , msib.description
     , borv.*
  FROM apps.bom_operational_routings_v borv
  JOIN inv.mtl_system_items_b msib ON borv.assembly_item_id = msib.inventory_item_id
 WHERE msib.organization_id = 10
   AND msib.segment1 = 'ABC123';

-- ROUTINGS > OPERATIONS

SELECT msib.segment1
     , msib.description
     , bosv.operation_seq_num seq
     , bosv.department_code
     , bosv.operation_description
     , bosv.long_description
     , bosv.effectivity_date
  FROM apps.bom_operational_routings_v borv
  JOIN apps.bom_operation_sequences_v bosv ON borv.routing_sequence_id = bosv.routing_sequence_id
  JOIN inv.mtl_system_items_b msib         ON borv.assembly_item_id =    msib.inventory_item_id
 WHERE msib.organization_id = 10
   AND msib.segment1 = 'ABC123';

-- ROUTINGS > OPERATIONS > RESOURCES

SELECT msib.segment1
     , msib.description
     , bosv.operation_seq_num seq
     , bosv.department_code
     , bosv.operation_description
     , bosv.long_description
     , bosv.effectivity_date
     , borv_rsc.resource_seq_num
     , borv_rsc.resource_code
     , borv_rsc.uom
     , DECODE (borv_rsc.autocharge_type, 2, 'Manual', 'PO receipt') charge_type
  FROM apps.bom_operational_routings_v borv
  JOIN apps.bom_operation_sequences_v bosv     ON borv.routing_sequence_id =   bosv.routing_sequence_id
  JOIN apps.bom_operation_resources_v borv_rsc ON bosv.operation_sequence_id = borv_rsc.operation_sequence_id
  JOIN inv.mtl_system_items_b msib             ON borv.assembly_item_id =      msib.inventory_item_id
 WHERE msib.organization_id = 10
   AND msib.segment1 = 'ABC123';

EAM Assets

-- ##############################################################################
--       EAM ASSETS
-- ############################################################################*/

-- version 1

    SELECT inventory_item_id
         , serial_number
         , parent_item_id
         , parent_serial_number
         , descriptive_text
         , concatenated_segments
         , asset_group_description
         , wip_accounting_class_code
         , owning_department_id
         , owning_department
         , maintained_unit
      FROM apps.mtl_eam_asset_numbers_all_v
     WHERE 1 = 1
    --   AND descriptive_text = 'BUILDING 00001'
       AND lower(descriptive_text) like '%dining%'
    --   AND parent_item_id = 174738
    --   AND inventory_item_id = 174738
       AND 2 = 2;

-- version 2

    SELECT nm.inventory_item_id
         , nm.serial_number
         , nm.descriptive_text
         , nm.current_organization_id
         , nm.creation_date
         , nm.concatenated_segments
         , nm.eam_item_type
         , nm.asset_group_description
         , nm.maintainable_flag
         , nm.area_id
         , nm.area
         , nm.owning_department
         , nm.parent_serial_number
         , nm.parent_item_id
--         , nm.
      FROM apps.mtl_eam_asset_numbers_all_v nm
--      JOIN apps.mtl_eam_asset_attr_values_v attr ON attr.inventory_item_id = nm.inventory_item_id AND attr.organization_id = nm.current_subinventory_code
     WHERE 1 = 1
--       AND LOWER (descriptive_text) LIKE '%hall%'
       AND nm.serial_number IN ('ABC123')
  ORDER BY nm.creation_Date DESC;
       
-- ##############################################################################
--       EAM ASSETS - HIERARCHY
-- ############################################################################*/

    SELECT LPAD (' ', (LEVEL - 1) * 5, '_') || serial_number serial_num
         , LEVEL
         , serial_number
         , descriptive_text
         , asset_group_description
         , owning_department
         , parent_serial_number
         , parent_item_id
      FROM apps.mtl_eam_asset_numbers_all_v
START WITH serial_number = '000'
CONNECT BY PRIOR serial_number = parent_serial_number;

    SELECT COUNT(*)
      FROM apps.mtl_eam_asset_numbers_all_v
      WHERE LEVEL > 1
START WITH serial_number = '102AA'
CONNECT BY PRIOR serial_number = parent_serial_number;

SELECT     LEVEL
         , LPAD(' ', (LEVEL - 1) * 5, ' ') || serial_number asset
         , descriptive_text
      FROM inv.mtl_serial_numbers msn
     WHERE 1 = 1
CONNECT BY msn.parent_serial_number = PRIOR msn.serial_number
START WITH serial_number = 'ABC123';

EAM Work Requests and Work Orders

Sections:

  1. Work Requests
  2. Work Orders And Requests, No Operations
  3. Work Orders With Operations
  4. Work Orders With Resources
  5. Work Orders With Materials (Inventory Items)
  6. Work Orders Linked To Requisitions
  7. Work Orders Linked To Requisitions And Purchase Orders
  8. Work Orders Linked To Direct Items

Work Requests

-- ##############################################################################
--       WORK REQUESTS
-- ############################################################################*/

SELECT *
  FROM wip.wip_eam_work_requests wewr;

  SELECT fu.user_name
       , fu.description
       , wewr.*
    FROM wip.wip_eam_work_requests wewr
       , applsys.fnd_user fu
   WHERE wewr.created_by = fu.user_id
     AND wewr.creation_date > '20-NOV-2015'
--     AND wewr.work_request_number = '5284812'
ORDER BY wewr.creation_date DESC;

Work Orders And Requests, No Operations

-- ##############################################################################
--       WORK ORDERS AND REQUESTS, NO OPERATIONS
-- ############################################################################*/

  SELECT DISTINCT
         we.wip_entity_name wo_num
       , we.wip_entity_id
       , wdj.status_type
       , wewr.work_request_id work_req
       , we.creation_date
       , we.last_update_date
       , wo_status.meaning status
       , DECODE (wdj.firm_planned_flag,  1, 'Yes',  2, 'No') firm
       , fu.description cr_by
       , wdj.asset_number asset
       , msn.descriptive_text asset_descr
       , wdj.scheduled_start_date start_
       , wdj.scheduled_completion_date end_
       , replace(replace(we.description,chr(10),' '),chr(13),' ') description
    FROM wip.wip_entities we
       , wip.wip_discrete_jobs wdj
       , wip.wip_eam_work_requests wewr
       , inv.mtl_serial_numbers msn
       , applsys.fnd_user fu
       , (SELECT lookup_code, meaning FROM apps.fnd_lookup_values_vl WHERE lookup_type = 'WIP_JOB_STATUS') wo_status
   WHERE we.created_by = fu.user_id
     AND we.wip_entity_id = wdj.wip_entity_id
     AND wdj.asset_number = msn.serial_number
     AND wdj.status_type = wo_status.lookup_code
     AND we.wip_entity_id = wewr.wip_entity_id(+)
     AND we.creation_date > '04-MAY-2016';

Work Orders With Operations

-- ##############################################################################
--       WORK ORDERS WITH OPERATIONS
-- ############################################################################*/

  SELECT DISTINCT
         we.wip_entity_name wo_num
       , wewr.work_request_id work_req
       , we.creation_date
       , wo_status.meaning status
       , DECODE (wdj.firm_planned_flag,  1, 'Yes',  2, 'No') firm
       , fu.description cr_by
       , wdj.asset_number asset
       , msn.descriptive_text asset_descr
       , replace(replace(we.description,chr(10),' '),chr(13),' ') description
       , wov.creation_date op_ct_dt
       , wov.operation_seq_num op
       , wov.description op_descr
       , wdj.scheduled_start_date start_
       , wdj.scheduled_completion_date end_
    FROM wip.wip_entities we
       , wip.wip_discrete_jobs wdj
       , wip.wip_eam_work_requests wewr
       , inv.mtl_serial_numbers msn
       , applsys.fnd_user fu
       , (SELECT lookup_code, meaning FROM apps.fnd_lookup_values_vl WHERE lookup_type = 'WIP_JOB_STATUS') wo_status
       , apps.wip_operations_v wov
   WHERE we.created_by = fu.user_id(+)
     AND we.wip_entity_id = wdj.wip_entity_id
     AND wdj.asset_number = msn.serial_number(+)
     AND wdj.status_type = wo_status.lookup_code(+)
     AND we.wip_entity_id = wov.wip_entity_id(+)
     AND we.wip_entity_id = wewr.wip_entity_id
     AND we.creation_date > '05-NOV-2015'
ORDER BY we.creation_date DESC
       , wov.operation_seq_num;

-- Operation count per work order

  SELECT we.wip_entity_name wo_num
       , we.creation_date
       , COUNT (DISTINCT worv.operation_seq_num) op_count
    FROM wip.wip_entities we
       , wip.wip_discrete_jobs wdj
       , inv.mtl_serial_numbers msn
       , applsys.fnd_user fu
       , (SELECT lookup_code
               , meaning
            FROM apps.fnd_lookup_values_vl
           WHERE lookup_type = 'WIP_JOB_STATUS') wo_status
       , apps.wip_operations_v wov
       , apps.wip_operation_resources_v worv
   WHERE we.created_by = fu.user_id
     AND we.wip_entity_id = wdj.wip_entity_id
     AND wdj.asset_number = msn.serial_number
     AND wdj.status_type = wo_status.lookup_code
     AND we.wip_entity_id = wov.wip_entity_id(+)
     AND we.wip_entity_id = worv.wip_entity_id(+)
     AND wo_status.meaning = 'Released'
     --      AND worv.resource_code = 'TECH01'
     AND we.creation_date > '06-DEC-2014'
GROUP BY we.wip_entity_name
       , we.creation_date
  HAVING COUNT (DISTINCT worv.operation_seq_num) > 0
ORDER BY we.wip_entity_name DESC;

Work Orders With Resources

-- ##############################################################################
--       WORK ORDERS WITH RESOURCES
-- ############################################################################*/

  SELECT DISTINCT
         we.wip_entity_name wo_num
       , we.creation_date
       , wo_status.meaning status
       , DECODE (wdj.firm_planned_flag,  1, 'Yes',  2, 'No') firm
       , fu.description cr_by
       , wdj.asset_number || ' (' || msn.descriptive_text || ')' asset
       , wov.operation_seq_num op
       , (SELECT DISTINCT 'YES' FROM po.po_requisition_lines_all prla WHERE prla.wip_entity_id = we.wip_entity_id) req_exists
       , wov.description op_descr
       , worv.resource_code rsc
       , worv.scheduled_flag
       , worv.scheduled_units
       , worv.uom_class
       , worv.uom_code uom
       , worv.description rsc_descr
       , worv.usage_rate_or_amount rsc_amt
       , worv.assigned_units rsc_units
       , wdj.scheduled_start_date start_
       , wdj.scheduled_completion_date end_
       , replace(replace(we.description,chr(10),' '),chr(13),' ') description
       , wdj.date_released
       , wdj.date_completed
--       , wdj.attribute1 rep_by
--       , wdj.attribute2 rep_by_tel
--       , wdj.attribute3 rep_by_email
       , wdj.class_code
       , wov.operation_seq_num
    FROM wip.wip_entities we
       , wip.wip_discrete_jobs wdj
       , inv.mtl_serial_numbers msn
       , applsys.fnd_user fu
       , (SELECT lookup_code, meaning FROM apps.fnd_lookup_values_vl WHERE lookup_type = 'WIP_JOB_STATUS') wo_status
       , apps.wip_operations_v wov
       , apps.wip_operation_resources_v worv
   WHERE we.created_by = fu.user_id(+)
     AND we.wip_entity_id = wdj.wip_entity_id
     AND wdj.asset_number = msn.serial_number(+)
     AND wdj.status_type = wo_status.lookup_code(+)
     AND we.wip_entity_id = wov.wip_entity_id
     AND we.wip_entity_id = worv.wip_entity_id
     AND NVL (wov.operation_seq_num, 0) = NVL (worv.operation_seq_num, 0)
--     AND we.wip_entity_name IN ('1235277','1235274','1235273')
--     AND wo_status.meaning = 'Complete'
--     AND wdj.firm_planned_flag = 1
     AND we.creation_date > '05-MAY-2015'
--     AND worv.resource_code IS NOT NULL
--     AND wdj.status_type = 4
--     AND wdj.asset_number = '037AB'
ORDER BY we.creation_date DESC
       , wov.operation_seq_num;

Work Orders With Materials (Inventory Items)

-- ##############################################################################
--       WORK ORDERS WITH MATERIALS (INVENTORY ITEMS)
-- ############################################################################*/

  SELECT we.wip_entity_name wo_num
       , we.creation_date
       , wdj.asset_number
       , wo_status.meaning status
       , replace(replace(we.description,chr(10),' '),chr(13),' ') description
       , fu.description cr_by
       , wdj.class_code
       , wdj.date_released
       , wdj.date_completed
       , msn.descriptive_text
       , worv.operation_seq_num op
       , worv.resource_code rsc
       , msib.segment1 item
       , msib.description item_descr
       , wro.required_quantity
       , wro.quantity_issued
       , wro.suggested_vendor_name suppl
    FROM wip.wip_entities we
       , wip.wip_discrete_jobs wdj
       , inv.mtl_serial_numbers msn
       , applsys.fnd_user fu
       , (SELECT lookup_code, meaning FROM apps.fnd_lookup_values_vl WHERE lookup_type = 'WIP_JOB_STATUS') wo_status
       , apps.wip_operations_v wov
       , apps.wip_operation_resources_v worv
       , wip.wip_requirement_operations wro
       , inv.mtl_system_items_b msib
   WHERE we.created_by = fu.user_id
     AND we.wip_entity_id = wdj.wip_entity_id
     AND wdj.asset_number = msn.serial_number
     AND wdj.status_type = wo_status.lookup_code
     AND we.wip_entity_id = wov.wip_entity_id(+)
     AND we.wip_entity_id = worv.wip_entity_id(+)
     AND wro.wip_entity_id = we.wip_entity_id
     AND wro.inventory_item_id = msib.inventory_item_id
     AND msib.organization_id = we.organization_id
     AND we.creation_date > '10-MAY-2015'
--     AND we.wip_entity_name = '1380516'
ORDER BY we.wip_entity_name DESC;

Work Orders Linked to Requisitions

-- ##############################################################################
--       WORK ORDERS LINKED TO REQUISITIONS
-- ############################################################################*/

  SELECT DISTINCT 
         prha.segment1 req
--       , '################'
--       , prha.*
--       , '################'
       , we.wip_entity_name wo
       , prha.creation_date req_date
       , fu.description cr_by
       , prla.suggested_vendor_name supplier
       , prha.wf_item_key
       , plla.po_header_id
       , pha.segment1 po
    FROM po.po_requisition_headers_all prha
       , po.po_requisition_lines_all prla
       , po.po_line_locations_all plla
       , po.po_headers_all pha
       , applsys.fnd_user fu
       , wip.wip_entities we
   WHERE prha.requisition_header_id = prla.requisition_header_id
     AND prla.line_location_id = plla.line_location_id(+)
     AND plla.po_header_id = pha.po_header_id(+)
     AND prha.created_by = fu.user_id
     AND prla.wip_entity_id = we.wip_entity_id
--     AND prha.creation_date > '08-MAY-2015'
     AND we.wip_entity_name = '1247457'
ORDER BY prha.creation_date DESC;

  select prha.segment1
       , PRHA.INTERFACE_SOURCE_CODE
       , PRHA.CREATION_DATE
       , PRLA.WIP_ENTITY_ID
       , PRLA.REFERENCE_NUM
       , PRLA.ITEM_DESCRIPTION
       , PRLA.SUGGESTED_VENDOR_NAME
       , CASE WHEN PRHA.INTERFACE_SOURCE_CODE = 'EAM' AND PRLA.WIP_ENTITY_ID IS NOT NULL THEN 'Description Direct'
              WHEN PRHA.INTERFACE_SOURCE_CODE IS NULL AND PRLA.WIP_ENTITY_ID IS NOT NULL THEN 'Purchase Materials'
         END requisition_route
       , CASE WHEN PRHA.INTERFACE_SOURCE_CODE = 'EAM' AND PRLA.WIP_ENTITY_ID IS NOT NULL THEN 1
         END flag_dd
       , CASE WHEN PRHA.INTERFACE_SOURCE_CODE IS NULL AND PRLA.WIP_ENTITY_ID IS NOT NULL THEN 1
         END flag_pm
    FROM po.po_requisition_headers_all prha
       , po.po_requisition_lines_all prla
   WHERE prha.requisition_header_id = prla.requisition_header_id
     AND prla.wip_entity_id IS NOT NULL
     AND prha.interface_source_code IS NULL
     AND prla.wip_entity_id IS NOT NULL
     AND prha.creation_date > '01-DEC-2014';
     
SELECT xyz.requisition_route
     , MAX(xyz.creation_date) latest
     , SUM(xyz.flag_dd) count_description_direct
     , SUM(xyz.flag_pm) count_purchase_materials
     , COUNT(DISTINCT xyz.requisition_header_id) req_count
     , COUNT(DISTINCT xyz.WIP_ENTITY_ID) work_order_count
from 
(  select prha.segment1
        , prha.interface_source_code
        , prha.creation_date
        , prla.wip_entity_id
        , prha.requisition_header_id
        , prla.reference_num
        , CASE WHEN prha.interface_source_code = 'EAM' AND prla.wip_entity_id IS NOT NULL THEN 'Description Direct'
               WHEN prha.interface_source_code IS NULL AND prla.wip_entity_id IS NOT NULL THEN 'Purchase Materials'
          END requisition_route
        , CASE WHEN prha.interface_source_code = 'EAM' AND prla.wip_entity_id IS NOT NULL THEN 1
          END flag_dd
        , CASE WHEN prha.interface_source_code IS NULL AND prla.wip_entity_id IS NOT NULL THEN 1
          END flag_pm
     FROM po.po_requisition_headers_all prha
        , po.po_requisition_lines_all prla
    WHERE prha.requisition_header_id = prla.requisition_header_id
      AND prla.wip_entity_id IS NOT NULL
      AND prha.creation_date > '01-DEC-2014') xyz
 group by xyz.requisition_route;

select wf_item_type, wf_item_key
from po.po_requisition_headers_all
where segment1='901586';

-- REQ IMPORTS FOR TODAY

SELECT   fcp.user_concurrent_program_name job_name
       , fcr.request_id id  
       , DECODE (fcr.phase_code , 'P', 'Pending' , 'R', 'Running' , 'C', 'Complete' , 'I', 'Inactive') phase
       , DECODE (fcr.status_code , 'A', 'Waiting' , 'B', 'Resuming' , 'C', 'Normal' , 'D', 'Cancelled' , 'E', 'Error' , 'F', 'Scheduled' , 'G', 'Warning' , 'H', 'On Hold' , 'I', 'Normal' , 'M', 'No Manager' , 'Q', 'Standby' , 'R', 'Normal' , 'S', 'Suspended' , 'T', 'Terminating' , 'U', 'Disabled' , 'W', 'Paused' , 'X', 'Terminated' , 'Z', 'Waiting') status
       , fcr.requested_start_date
       , fcr.actual_start_date start_ 
       , fcr.actual_completion_date end_
       , fcr.argument_text
    FROM applsys.fnd_concurrent_requests fcr
       , applsys.fnd_concurrent_programs_tl fcp
   WHERE fcr.concurrent_program_id = fcp.concurrent_program_id
     AND fcp.user_concurrent_program_name = 'Requisition Import'
     AND fcr.argument1 = 'EAM'
     AND fcr.argument3 = 'VENDOR'
     AND actual_completion_date > TRUNC(SYSDATE)
ORDER BY fcr.actual_completion_date DESC;

Work Orders Linked To Requisitions And Purchase Orders

-- ##############################################################################
--       WORK ORDERS LINKED TO REQUISITIONS AND PURCHASE ORDERS
-- ############################################################################*/

  SELECT DISTINCT 
         prha.segment1 req
       , prha.authorization_status req_status
       , pha.segment1 po
       , pha.authorization_status po_status
       , we.wip_entity_name wo
       , prha.creation_date req_date
       , fu.description cr_by
       , prha.authorization_status status
       , prla.suggested_vendor_name supplier
       , plla.accrue_on_receipt_flag
    FROM po.po_requisition_headers_all prha
       , po.po_requisition_lines_all prla
       , po.po_line_locations_all plla
       , po.po_lines_all pla
       , po.po_headers_all pha
       , applsys.fnd_user fu
       , wip.wip_entities we
   WHERE prha.requisition_header_id = prla.requisition_header_id
     AND prla.line_location_id = plla.line_location_id
     and plla.po_line_id = pla.po_line_id
     and pla.po_header_id = pha.po_header_id
     AND prha.created_by = fu.user_id
     AND prla.wip_entity_id = we.wip_entity_id
     AND prha.creation_date > '01-MAY-2016'
ORDER BY prha.creation_date DESC;

Work Orders Linked To Direct Items

-- ##############################################################################
--       WORK ORDERS LINKED TO DIRECT ITEMS
-- ############################################################################*/

-- DIRECT ITEMS, NO REQ TABLE JOIN----------------------------------------------

  SELECT wedi.direct_item_sequence_id item_seq_id
       , we.wip_entity_name wo
       , wewr.work_request_id wr
       , wdj.asset_number asset
       , wedi.operation_seq_num op
       , wedi.suggested_vendor_name supplier
       , wedi.unit_price price
       , wedi.required_quantity qty
       , we.creation_date wo_cr_date
       , wedi.creation_date cr_date_direct_item
       , wo_status.meaning status
       , (SELECT COUNT(*) FROM apps.fnd_concurrent_requests fcr, apps.fnd_concurrent_programs_tl fcp WHERE fcr.concurrent_program_id = fcp.concurrent_program_id AND fcp.user_concurrent_program_name = 'Requisition Import' AND fcr.argument_text = 'EAM, , VENDOR, , N, Y' AND TRUNC(actual_completion_date) = TRUNC(wedi.creation_date)) req_import_job_ct_today
       , CASE WHEN (SELECT COUNT (DISTINCT PRLA.REQUISITION_HEADER_ID) FROM po.po_requisition_lines_all prla WHERE prla.wip_entity_id = wedi.wip_entity_id AND prla.wip_resource_seq_num = wedi.direct_item_sequence_id) = 1 then (SELECT to_number(PRHA.segment1) FROM po.po_requisition_lines_all prla, po.po_requisition_headers_all prha WHERE PRHA.REQUISITION_HEADER_ID = PRLA.REQUISITION_HEADER_ID AND prla.wip_entity_id = wedi.wip_entity_id AND prla.wip_resource_seq_num = wedi.direct_item_sequence_id) WHEN (SELECT COUNT (DISTINCT PRLA.REQUISITION_HEADER_ID) FROM po.po_requisition_lines_all prla WHERE prla.wip_entity_id = wedi.wip_entity_id AND prla.wip_resource_seq_num = wedi.direct_item_sequence_id) > 1 then (SELECT COUNT (DISTINCT PRLA.REQUISITION_HEADER_ID) FROM po.po_requisition_lines_all prla WHERE prla.wip_entity_id = wedi.wip_entity_id AND prla.wip_resource_seq_num = wedi.direct_item_sequence_id) END REQ
    FROM wip.wip_eam_direct_items wedi
       , wip.wip_entities we
       , wip.wip_discrete_jobs wdj
       , wip.wip_eam_work_requests wewr
       , (SELECT lookup_code, meaning FROM apps.fnd_lookup_values_vl WHERE lookup_type = 'WIP_JOB_STATUS') wo_status
   WHERE we.wip_entity_id = wedi.wip_entity_id
     AND wdj.status_type = wo_status.lookup_code(+)
     AND we.wip_entity_id = wdj.wip_entity_id
     AND we.wip_entity_id = wewr.wip_entity_id(+)
--     AND wedi.creation_date > '11-DEC-2014'
--     AND we.wip_entity_id = 2868137
     and we.wip_entity_name = 1391304
ORDER BY wedi.creation_date DESC;

-- linked to REQs---------------------------------------------------------------

-- THE WIP_RESOURCE_SEQ_NUM On REQ LINES is not always populated
-- so it's not always possible to link between the REQ line and the Direct Item

  SELECT DISTINCT 
         we.wip_entity_name wo_num
       , we.creation_date wo_date
       , wedi.wip_entity_id
       , wdj.date_released
       , wedi.creation_date item_added_date
       , wedi.uom
       , wedi.required_quantity qty
       , wedi.unit_price prc
       , wedi.auto_request_material auto_req
       , wedi.suggested_vendor_name supplier
       , prha.segment1 req
       , prha.creation_date req_date
       , prha.request_id
       , wedi.description
--       , prla.wip_resource_seq_num
--       , wedi.direct_item_sequence_id
       , fu.description
    FROM wip.wip_eam_direct_items wedi
       , wip.wip_entities we
       , wip.wip_discrete_jobs wdj
       , po.po_requisition_headers_all prha
       , po.po_requisition_lines_all prla
       , applsys.fnd_user fu
   WHERE we.wip_entity_id = wdj.wip_entity_id
     AND we.wip_entity_id = wedi.wip_entity_id
     AND wedi.wip_entity_id = prla.wip_entity_id
     AND prla.wip_resource_seq_num = wedi.direct_item_sequence_id(+)
     AND prla.requisition_header_id = prha.requisition_header_id
     AND prha.created_by = fu.user_id
--     AND prha.creation_date > '05-NOV-2015'
--     AND prha.segment1 = '737707'
     AND we.wip_entity_name = '1391304'
ORDER BY wedi.creation_date DESC;


select * FROM wip.wip_eam_direct_items wedi where wedi.wip_entity_id = 2878940;
select description from applsys.fnd_user where user_id = 12332;

-- inc. link to Operations and Resources

  SELECT DISTINCT
         we.wip_entity_name wo
       , wewr.work_request_id wr
       , wdj.asset_number asset
       , wedi.operation_seq_num op
       , wedi.suggested_vendor_name supplier
       , wedi.unit_price price
       , wedi.required_quantity qty
       , we.creation_date wo_cr_date
       , wedi.creation_date cr_date_direct_item
       , wo_status.meaning status
       , CASE WHEN (SELECT COUNT (DISTINCT PRLA.REQUISITION_HEADER_ID) FROM po.po_requisition_lines_all prla WHERE prla.wip_entity_id = wedi.wip_entity_id AND prla.wip_resource_seq_num = wedi.direct_item_sequence_id) = 1 then (SELECT to_number(PRHA.segment1) FROM po.po_requisition_lines_all prla, po.po_requisition_headers_all prha WHERE PRHA.REQUISITION_HEADER_ID = PRLA.REQUISITION_HEADER_ID AND prla.wip_entity_id = wedi.wip_entity_id AND prla.wip_resource_seq_num = wedi.direct_item_sequence_id) WHEN (SELECT COUNT (DISTINCT PRLA.REQUISITION_HEADER_ID) FROM po.po_requisition_lines_all prla WHERE prla.wip_entity_id = wedi.wip_entity_id AND prla.wip_resource_seq_num = wedi.direct_item_sequence_id) > 1 then (SELECT COUNT (DISTINCT PRLA.REQUISITION_HEADER_ID) FROM po.po_requisition_lines_all prla WHERE prla.wip_entity_id = wedi.wip_entity_id AND prla.wip_resource_seq_num = wedi.direct_item_sequence_id) END REQ
       , wov.operation_seq_num op
       , worv.resource_code rsc       
    FROM wip.wip_eam_direct_items wedi
       , wip.wip_entities we
       , wip.wip_discrete_jobs wdj
       , wip.wip_eam_work_requests wewr
       , apps.wip_operations_v wov
       , apps.wip_operation_resources_v worv       
       , (SELECT lookup_code, meaning FROM apps.fnd_lookup_values_vl WHERE lookup_type = 'WIP_JOB_STATUS') wo_status
   WHERE we.wip_entity_id = wedi.wip_entity_id
     AND wdj.status_type = wo_status.lookup_code(+)
     AND we.wip_entity_id = wdj.wip_entity_id
     AND we.wip_entity_id = wewr.wip_entity_id(+)
     AND we.wip_entity_id = wov.wip_entity_id
     AND we.wip_entity_id = worv.wip_entity_id
     AND wov.operation_seq_num = worv.operation_seq_num
     AND wov.operation_seq_num = WEDI.OPERATION_SEQ_NUM
     AND wedi.creation_date > '06-DEC-2014'
     AND we.wip_entity_name = '1391304'
ORDER BY wedi.creation_date DESC;

-- REQs TODAY-------------------------------------------------------------------

SELECT prha.creation_date
     , prha.segment1 REQ
     , prha.description
     , PRLA.SUGGESTED_VENDOR_NAME
     , PRLA.ITEM_DESCRIPTION
     , PRLA.UNIT_PRICE
     , PRLA.QUANTITY
     , PRLA.WIP_ENTITY_ID
     , PRLA.WIP_RESOURCE_SEQ_NUM
FROM po.po_requisition_headers_all prha, po.po_requisition_lines_all prla
where PRHA.REQUISITION_HEADER_ID = PRLA.REQUISITION_HEADER_ID
--and prha.creation_date > TRUNC(SYSDATE) - 1
--and prha.segment1 = '737707'
ORDER BY prha.creation_date DESC;

-- REQ INTERFACE----------------------------------------------------------------

SELECT * FROM PO.PO_REQUISITIONS_INTERFACE_ALL PRIA
ORDER BY CREATION_DATE DESC;