eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.AHL_MM_WRK_ORDER_RESERVTN_V

Source

View Text - Preformatted


SELECT AWO.WORKORDER_ID,
    AWO.WIP_ENTITY_ID,
    AWO.WORKORDER_NAME,
    AWO.STATUS_CODE,
    (SELECT MEANING
    FROM FND_LOOKUPS
    WHERE UPPER(LOOKUP_TYPE) = 'AHL_JOB_STATUS'
    AND UPPER(LOOKUP_CODE)   = UPPER(AWO.STATUS_CODE)
    ) STATUS_MEANING,
    CSI.INVENTORY_ITEM_ID,
    CSI.INSTANCE_ID ITEM_INSTANCE_ID,
    CSI.INSTANCE_NUMBER INSTANCE_NUMBER,
    (SELECT CONCATENATED_SEGMENTS
    FROM MTL_SYSTEM_ITEMS_KFV
    WHERE INVENTORY_ITEM_ID = CSI.INVENTORY_ITEM_ID
    AND ROWNUM              <2
    ) ITEM,
    HZP.PARTY_NAME ITEM_OWNER,
    CSI.SERIAL_NUMBER,
    CSI.LOT_NUMBER,
    CSIN.INCIDENT_ID NON_ROUTINE_ID,
    CSIN.INCIDENT_NUMBER NON_ROUTINE_NUMBER,
    AUE.UNIT_EFFECTIVITY_ID,
    HOU.NAME ORG_NAME,
    VST.ORGANIZATION_ID,
    AWR.WORKORDER_NAME RESERVING_WORK_ORDER_NUM,
    RES.RESERVATION_ID RESERVATION_ID,
    WDJR.SCHEDULED_START_DATE WORK_ORDER_START_DATE,
    WDJ.SCHEDULED_COMPLETION_DATE AVAILABLE_DATE,
    AHL_CMP_UTIL_PKG.Get_Rpr_Batch_For_Inst (CSI.INSTANCE_ID ,VST.ORGANIZATION_ID) REPAIR_BATCH_NAME
  FROM CSI_ITEM_INSTANCES CSI,
    AHL_WORKORDERS AWO,-- for supply WO details
    AHL_WORKORDERS AWR,-- for reservation WO details
    AHL_VISITS_VL VST,
    AHL_VISIT_TASKS_VL VTS,
    CS_INCIDENTS_ALL_B CSIN,
    AHL_UNIT_EFFECTIVITIES_B AUE,
    HR_ORGANIZATION_UNITS HOU,
    HZ_PARTIES HZP,
    WIP_DISCRETE_JOBS WDJ, -- for supply WO details
    WIP_DISCRETE_JOBS WDJR,-- for reservation WO details
    MTL_RESERVATIONS RES
  WHERE AWO.MASTER_WORKORDER_FLAG = 'N'
  AND AWO.STATUS_CODE NOT        IN (7, 22, 17, 12, 18, 21, 4, 5)---- cancelled, deleted, draft, closed,Deffered,Pending Defer/Cancel Approval, Complete, Complete No-charge
    -- for item owner
  AND CSI.OWNER_PARTY_SOURCE_TABLE              = 'HZ_PARTIES'
  AND CSI.OWNER_PARTY_ID                        = HZP.PARTY_ID -- party id will always be present for each instance
  AND AWO.VISIT_TASK_ID                         = VTS.VISIT_TASK_ID
  AND VST.VISIT_ID                              = VTS.VISIT_ID
  AND NVL(VTS.INSTANCE_ID,VST.ITEM_INSTANCE_ID) = CSI.INSTANCE_ID
    -- for organization name
  AND VST.ORGANIZATION_ID = HOU.ORGANIZATION_ID
    -- return to supply must be Y
  AND UPPER(VTS.RETURN_TO_SUPPLY_FLAG) = UPPER('Y')
    -- for NR details
  AND VTS.SERVICE_REQUEST_ID         = CSIN.INCIDENT_ID(+)
  AND CSIN.INCIDENT_ID               = AUE.CS_INCIDENT_ID(+)
  AND AWO.WIP_ENTITY_ID              = WDJ.WIP_ENTITY_ID
  AND AWR.WIP_ENTITY_ID              = WDJR.WIP_ENTITY_ID(+)
  AND RES.SUPPLY_SOURCE_HEADER_ID(+) = AWO.WIP_ENTITY_ID
  AND RES.DEMAND_SOURCE_HEADER_ID    = AWR.WIP_ENTITY_ID(+)
  AND RES.SUPPLY_SOURCE_TYPE_ID (+)  = 5
  AND RES.DEMAND_SOURCE_TYPE_ID (+)  = 5

View Text - HTML Formatted

SELECT AWO.WORKORDER_ID
, AWO.WIP_ENTITY_ID
, AWO.WORKORDER_NAME
, AWO.STATUS_CODE
, (SELECT MEANING
FROM FND_LOOKUPS
WHERE UPPER(LOOKUP_TYPE) = 'AHL_JOB_STATUS'
AND UPPER(LOOKUP_CODE) = UPPER(AWO.STATUS_CODE) ) STATUS_MEANING
, CSI.INVENTORY_ITEM_ID
, CSI.INSTANCE_ID ITEM_INSTANCE_ID
, CSI.INSTANCE_NUMBER INSTANCE_NUMBER
, (SELECT CONCATENATED_SEGMENTS
FROM MTL_SYSTEM_ITEMS_KFV
WHERE INVENTORY_ITEM_ID = CSI.INVENTORY_ITEM_ID
AND ROWNUM <2 ) ITEM
, HZP.PARTY_NAME ITEM_OWNER
, CSI.SERIAL_NUMBER
, CSI.LOT_NUMBER
, CSIN.INCIDENT_ID NON_ROUTINE_ID
, CSIN.INCIDENT_NUMBER NON_ROUTINE_NUMBER
, AUE.UNIT_EFFECTIVITY_ID
, HOU.NAME ORG_NAME
, VST.ORGANIZATION_ID
, AWR.WORKORDER_NAME RESERVING_WORK_ORDER_NUM
, RES.RESERVATION_ID RESERVATION_ID
, WDJR.SCHEDULED_START_DATE WORK_ORDER_START_DATE
, WDJ.SCHEDULED_COMPLETION_DATE AVAILABLE_DATE
, AHL_CMP_UTIL_PKG.GET_RPR_BATCH_FOR_INST (CSI.INSTANCE_ID
, VST.ORGANIZATION_ID) REPAIR_BATCH_NAME
FROM CSI_ITEM_INSTANCES CSI
, AHL_WORKORDERS AWO
, -- FOR SUPPLY WO DETAILS AHL_WORKORDERS AWR
, -- FOR RESERVATION WO DETAILS AHL_VISITS_VL VST
, AHL_VISIT_TASKS_VL VTS
, CS_INCIDENTS_ALL_B CSIN
, AHL_UNIT_EFFECTIVITIES_B AUE
, HR_ORGANIZATION_UNITS HOU
, HZ_PARTIES HZP
, WIP_DISCRETE_JOBS WDJ
, -- FOR SUPPLY WO DETAILS WIP_DISCRETE_JOBS WDJR
, -- FOR RESERVATION WO DETAILS MTL_RESERVATIONS RES
WHERE AWO.MASTER_WORKORDER_FLAG = 'N'
AND AWO.STATUS_CODE NOT IN (7
, 22
, 17
, 12
, 18
, 21
, 4
, 5)---- CANCELLED
, DELETED
, DRAFT
, CLOSED
, DEFFERED
, PENDING DEFER/CANCEL APPROVAL
, COMPLETE
, COMPLETE NO-CHARGE -- FOR ITEM OWNER
AND CSI.OWNER_PARTY_SOURCE_TABLE = 'HZ_PARTIES'
AND CSI.OWNER_PARTY_ID = HZP.PARTY_ID -- PARTY ID WILL ALWAYS BE PRESENT FOR EACH INSTANCE
AND AWO.VISIT_TASK_ID = VTS.VISIT_TASK_ID
AND VST.VISIT_ID = VTS.VISIT_ID
AND NVL(VTS.INSTANCE_ID
, VST.ITEM_INSTANCE_ID) = CSI.INSTANCE_ID -- FOR ORGANIZATION NAME
AND VST.ORGANIZATION_ID = HOU.ORGANIZATION_ID -- RETURN TO SUPPLY MUST BE Y
AND UPPER(VTS.RETURN_TO_SUPPLY_FLAG) = UPPER('Y') -- FOR NR DETAILS
AND VTS.SERVICE_REQUEST_ID = CSIN.INCIDENT_ID(+)
AND CSIN.INCIDENT_ID = AUE.CS_INCIDENT_ID(+)
AND AWO.WIP_ENTITY_ID = WDJ.WIP_ENTITY_ID
AND AWR.WIP_ENTITY_ID = WDJR.WIP_ENTITY_ID(+)
AND RES.SUPPLY_SOURCE_HEADER_ID(+) = AWO.WIP_ENTITY_ID
AND RES.DEMAND_SOURCE_HEADER_ID = AWR.WIP_ENTITY_ID(+)
AND RES.SUPPLY_SOURCE_TYPE_ID (+) = 5
AND RES.DEMAND_SOURCE_TYPE_ID (+) = 5