AHL Generate Visit Instance - Tasks Warranty Contracts

Description

Application: Complex Maintenance Repair and Overhaul
Source: Generate Visit Instance - Tasks Warranty Contracts Report
Short Name: AHLVITWCR
DB package:

Download Blitz Report™ – World’s fastest reporting and data upload for Oracle EBS

Copy

            SELECT VISIT.VISIT_NUMBER, 
                   VISIT.VISIT_NAME, 
                   UNIT.NAME UNIT_NAME, 
                   HROU.NAME ORGANIZATION_NAME, 
                   BDPT.DESCRIPTION DEPARTMENT_NAME, 
                   VISIT.START_DATE_TIME START_DATE, 
                   AHL_VWP_TIMES_PVT.get_visit_end_time(VISIT.visit_id) END_DATE,
                   INSTANCE.INSTANCE_NUMBER, 
                   INSTANCE.SERIAL_NUMBER, 
                   INSTANCE.LOT_NUMBER, 
                   ITEM.CONCATENATED_SEGMENTS ITEM_NAME, 
                   ITEM.DESCRIPTION ITEM_DESCRIPTION, 
                   TASK.TASK_NUMBER, 
                   TASK.TASK_NAME, 
                   TASK.QUANTITY, 
                   TASK.UOM, 
                   TASK.MR_NAME MAINTENENCE_REQUIREMENT, 
                   TASK.DEPARTMENT_NAME TASK_DEPT_NAME,
                   TASK.STAGE_NAME,  
                   TASK.TASK_TYPE_NAME, 
                   TASK.TASK_STATUS_NAME, 
                   TASK.TASK_WARRANTY_FLAG, 
                   TASK.INSTANCE_WARRANTY_FLAG, 
                   TASK.CONTRACT_NUMBER WARRANTY_CONTRACT, 
                   TASK.ENTITLEMENT_STATUS WARRANTY_STATUS
            FROM   MTL_SYSTEM_ITEMS_B_KFV ITEM, 
                   AHL_VISITS_VL VISIT, 
                   AHL_UNIT_CONFIG_HEADERS UNIT, 
                   AHL_SEARCH_VISIT_TASK_V TASK, 
                   HR_ALL_ORGANIZATION_UNITS_TL HROU, 
                   BOM_DEPARTMENTS BDPT, 
                   CSI_ITEM_INSTANCES INSTANCE
            WHERE  VISIT.VISIT_NUMBER            = :P_VISIT_NUMBER
            AND    VISIT.VISIT_ID                = TASK.VISIT_ID
            AND    UNIT.CSI_ITEM_INSTANCE_ID (+) = VISIT.ITEM_INSTANCE_ID
            AND    VISIT.ORGANIZATION_ID         = HROU.ORGANIZATION_ID(+)
            AND    HROU.LANGUAGE(+)              = USERENV('LANG')
            AND    VISIT.DEPARTMENT_ID           = BDPT.DEPARTMENT_ID(+)
            AND    TASK.INSTANCE_NUMBER          = INSTANCE.INSTANCE_NUMBER
            AND    ITEM.INVENTORY_ITEM_ID        = TASK.ITEM_ID
            AND    ITEM.ORGANIZATION_ID          = TASK.ITEM_ORGANIZATION_ID
            ORDER BY VISIT_NUMBER, INSTANCE_NUMBER, TASK_NUMBER