AHL Supplier Warranty Visit

    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,
      NVL2(VISIT.UNIT_SCHEDULE_ID,'Yes','No') TRANSIT_VISIT,
      ITEM.CONCATENATED_SEGMENTS ITEM_NAME,
      ITEM.DESCRIPTION ITEM_DESCRIPTION,
      INSTANCE.INSTANCE_NUMBER,
      INSTANCE.SERIAL_NUMBER,
      decode(TASK.INSTANCE_WARRANTY_FLAG,'Y','Yes','No') INSTANCE_WARRANTY,
      TASK.TASK_NUMBER,
      TASK.TASK_NAME,
      TASK.MR_NAME,
      TASK.DEPARTMENT_NAME TASK_DEPT_NAME,
      TASK.STAGE_NAME,
      TASK.TASK_TYPE_NAME,
      TASK.TASK_STATUS_NAME,
      decode(TASK.TASK_WARRANTY_FLAG,'Y','Yes','No') TASK_WARRANTY_FLAG,
      TASK.CONTRACT_NUMBER TASK_CONT_NUMBER,
      NVL(TASK.ENTITLEMENT_STATUS,(SELECT MEANING
                                FROM FND_LOOKUP_VALUES_VL
                                WHERE LOOKUP_TYPE = 'AHL_WARRANTY_ENTLMNT_STATUS'
                                AND LOOKUP_CODE   = 'NOT_APPLICABLE'))
      ENTL_STATUS,
      NVL(TASK.ENTITLEMENT_STATUS_CODE,'NOT_APPLICABLE') ENTL_STATUS_CODE,
      TASK.CONTRACT_NUMBER,
      WTYPE.MEANING WARRANTY_TYPE,
      CONTRACT.ACTIVE_START_DATE,
      CONTRACT.ACTIVE_END_DATE,
      CONTRACT.TERMS_AND_CONDITIONS CONTRACT_TERMS,
      CONTRACT.REACTION_AND_RESOL_TIME CONTRACT_RESOL,
      CONTRACT.SERVICE_LEVEL_AGREEMENT CONTRACT_SERVICE,
      COUNTERS.NAME COUNTER_NAME,
      COUNTERS.UNIT_OF_MEASURE COUNTER_UOM,
      CONT_CTR.START_VALUE      CONT_CTR_START_VALUE,
      (CONT_CTR.THRESHOLD + CONT_CTR.START_VALUE) CONT_CTR_STOP_VALUE,
      CONT_CTR.EXPIRATION_DATE   CONT_CTR_EXP_DATE,
      CONT_CTR.TERMS_AND_CONDITIONS CONT_CTR_TERMS,
      CONT_CTR.REACTION_AND_RESOL_TIME  CONT_CTR_RESOL,
      CONT_CTR.SERVICE_LEVEL_AGREEMENT  CONT_CTR_SERVICE
   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 ,
      AHL_WARRANTY_CONTRACTS_VL CONTRACT,
      AHL_WARRANTY_CONT_CTR_VL CONT_CTR,
      CS_COUNTERS_V COUNTERS,
      FND_LOOKUP_VALUES_VL WTYPE
   WHERE
      VISIT.VISIT_ID = TASK.VISIT_ID
      AND VISIT.VISIT_NUMBER = :P_AHL_VISIT_NUMBER
      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
      AND TASK.CONTRACT_NUMBER = CONTRACT.CONTRACT_NUMBER(+)
      AND CONTRACT.WARRANTY_CONTRACT_ID = CONT_CTR.WARRANTY_CONTRACT_ID(+)
      AND CONT_CTR.COUNTER_ID = COUNTERS.COUNTER_ID(+)
      AND WTYPE.LOOKUP_TYPE(+) = 'AHL_WARRANTY_TYPE'
      AND CONTRACT.WARRANTY_TYPE = WTYPE.LOOKUP_CODE(+)
      ORDER BY INSTANCE_NUMBER, TASK_NUMBER, COUNTER_NAME