AHL Generate Visit Instances Warranty Contracts

              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,
                     INSTANCE.ACTIVE_START_DATE    SERVICE_ENTRY_DATE,
                     CONTRACT.CONTRACT_NUMBER,
                     FND_STATUS.MEANING              CONTRACT_STATUS,
                     CONTRACT.DESCRIPTION            CONTRACT_DESC,
                     VENDOR.VENDOR_NAME              WARRANTY_VENDOR_NAME,
                     CONTRACT.WARRANTY_PERIOD        WARRANTY_PERIOD,
                     UOM.DESCRIPTION                 CONTRACT_UOM,
                     CONTRACT.ACTIVE_START_DATE      CONTRACT_ACTIVE_START_DATE,
                     CONTRACT.EXPIRATION_DATE        CONTRACT_EXPIRATION_DATE,
                     COUNTER.NAME                                CONTRACT_COUNTER_NAME,
                     UOM_CTR.DESCRIPTION                         CONTRACT_CNTR_UOM_DESC,
                     CONT_CTR.THRESHOLD                          CONTRACT_CNTR_THRESHOLD,
                     CONT_CTR.START_VALUE                        CONTRACT_CNTR_START_VALUE,
                     (CONT_CTR.THRESHOLD + CONT_CTR.START_VALUE) STOP_VALUE,
                     CONT_CTR.EXPIRATION_DATE                    CONTRACT_CNTR_EXPIRATION_DATE
              FROM   MTL_SYSTEM_ITEMS_B_KFV          ITEM,
                     AHL_VISITS_VL                   VISIT,
                     AHL_UNIT_CONFIG_HEADERS         UNIT,
                     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,
                     FND_LOOKUP_VALUES_VL            FND_STATUS,
                     PO_VENDORS                      VENDOR,
                     MTL_UNITS_OF_MEASURE_VL         UOM,
                     CSI_COUNTERS_VL                 COUNTER,
                     MTL_UNITS_OF_MEASURE_VL         UOM_CTR
              WHERE  VISIT.VISIT_NUMBER             = :P_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    EXISTS (SELECT 'X'
                             FROM   AHL_VISIT_TASKS_B TSK
                             WHERE  TSK.INSTANCE_ID = INSTANCE.INSTANCE_ID
                             AND    TSK.VISIT_ID    = VISIT.VISIT_ID)
              AND    ITEM.ORGANIZATION_ID           = INSTANCE.INV_MASTER_ORGANIZATION_ID
              AND    ITEM.INVENTORY_ITEM_ID         = INSTANCE.INVENTORY_ITEM_ID 
              AND    CONTRACT.ITEM_INSTANCE_ID(+)   = INSTANCE.INSTANCE_ID
              AND    CONTRACT.WARRANTY_CONTRACT_ID  = CONT_CTR.WARRANTY_CONTRACT_ID(+)
              AND    FND_STATUS.LOOKUP_TYPE(+)      = 'AHL_WARRANTY_CONT_STATUS'
              AND    FND_STATUS.LOOKUP_CODE(+)      = CONTRACT.CONTRACT_STATUS_CODE
              AND    VENDOR.VENDOR_ID (+)           = CONTRACT.WARRANTY_VENDOR_ID  
              AND    CONTRACT.PERIOD_UOM_CODE       = UOM.UOM_CODE(+)
              AND    CONT_CTR.COUNTER_ID            = COUNTER.COUNTER_ID (+)
              AND    COUNTER.UOM_CODE               = UOM_CTR.UOM_CODE (+)
              ORDER BY VISIT_NUMBER, INSTANCE_NUMBER