VIEW: APPS.AHL_SEARCH_VISIT_TASK_V

Source

View Text - Preformatted

SELECT AVTS.VISIT_ID VISIT_ID, AVTS.VISIT_NUMBER, AVTS.TEMPLATE_FLAG, AVTS.STATUS_CODE, ATSK.VISIT_TASK_ID, ATSK.VISIT_TASK_NUMBER, ATSK.VISIT_TASK_NAME, ATSK.INVENTORY_ITEM_ID, MTSB.CONCATENATED_SEGMENTS, ATSK.ITEM_ORGANIZATION_ID, ATSK.INSTANCE_ID, CSIS.SERIAL_NUMBER, ATSK.MR_ROUTE_ID, AMRH.TITLE, AMRR.ROUTE_NUMBER, ARV.ROUTE_TYPE_CODE, LKUP4.MEANING "ROUTE_TYPE", AWO.WORKORDER_ID, AWO.WORKORDER_NAME, AWO.STATUS_CODE, LKUP2.MEANING, AWO.ACTUAL_START_DATE, AWO.ACTUAL_END_DATE, ATSK.SERVICE_REQUEST_ID, SR.INCIDENT_NUMBER, ATSK.START_DATE_TIME, ATSK.END_DATE_TIME, ATSK.TASK_TYPE_CODE, LKUP1.MEANING, ATSK.STATUS_CODE "TASK_STATUS_CODE", LKUP3.MEANING "TASK_STATUS_NAME", ATSK.STAGE_ID, ASTG.STAGE_NUM, ASTG.STAGE_NAME, AUEF.DUE_DATE, ATSK.DEPARTMENT_ID, BDPT.DESCRIPTION DEPARTMENT_NAME, ATSK.ORIGINATING_TASK_ID, ORIGTSK.VISIT_TASK_NUMBER ORIGINATING_TASK_NUMBER, ORIGTSK.VISIT_TASK_NAME ORIGINATING_TASK_NAME, ATSK.MR_ID, ATSK.UNIT_EFFECTIVITY_ID, AMRH.DESCRIPTION, ATSK.QUANTITY, CSIS.UNIT_OF_MEASURE, CSIS.INSTANCE_NUMBER, ATSK.SERVICE_TYPE_CODE, PA.MEANING, ASWE.WARRANTY_ENTITLEMENT_ID , AWCB.CONTRACT_NUMBER , ASWE.ENTITLEMENT_STATUS_CODE , LKUP5.MEANING ENTITLEMENT_STATUS , AWCL.CLAIM_NAME , AWCL.CLAIM_STATUS_CODE, LKUP6.MEANING CLAIM_STATUS , AHL_WARRANTY_ENTL_PVT.IS_TASK_WARRANTY_AVAILABLE(ATSK.VISIT_TASK_ID) TASK_WARRANTY_FLAG, AHL_WARRANTY_CONTRACTS_PVT.IS_INSTANCE_WARRANTY_AVAILABLE(ATSK.INSTANCE_ID) INSTANCE_WARRANTY_FLAG, NVL2(TO_NUMBER(ASWE.WARRANTY_ENTITLEMENT_ID), DECODE(ASWE.ENTITLEMENT_STATUS_CODE, 'NOT_APPLICABLE', 'Y', 'N'), 'Y') WARRANTY_NOT_APPL_FLAG FROM AHL_VISITS_VL AVTS, AHL_VISIT_TASKS_VL ATSK, AHL_VISIT_TASKS_VL ORIGTSK, AHL_MR_ROUTES_V AMRR, AHL_MR_HEADERS_VL AMRH, AHL_UNIT_EFFECTIVITIES_B AUEF, CSI_ITEM_INSTANCES CSIS, CS_INCIDENTS_ALL_B SR, BOM_DEPARTMENTS BDPT, FND_LOOKUP_VALUES_VL LKUP1, FND_LOOKUP_VALUES_VL LKUP2, FND_LOOKUP_VALUES_VL LKUP3, FND_LOOKUP_VALUES_VL LKUP4, AHL_VWP_STAGES_VL ASTG, MTL_SYSTEM_ITEMS_B_KFV MTSB, AHL_ROUTES_B ARV, AHL_WORKORDERS AWO, PA_LOOKUPS PA, AHL_WARRANTY_ENTITLEMENTS ASWE, AHL_WARRANTY_CLAIMS_B AWCL , AHL_WARRANTY_CONTRACTS_B AWCB, FND_LOOKUP_VALUES_VL LKUP5 , FND_LOOKUP_VALUES_VL LKUP6 WHERE ATSK.INSTANCE_ID = CSIS.INSTANCE_ID (+) AND ATSK.ORIGINATING_TASK_ID = ORIGTSK.VISIT_TASK_ID(+) AND (ATSK.MR_ROUTE_ID = AMRR.MR_ROUTE_ID (+) AND AMRR.MR_HEADER_ID= AMRH.MR_HEADER_ID (+)) AND AMRR.ROUTE_ID = ARV.ROUTE_ID (+) AND LKUP4.LOOKUP_TYPE (+) = 'AHL_ROUTE_TYPE' AND LKUP4.LOOKUP_CODE (+) = ARV.ROUTE_TYPE_CODE AND ATSK.SERVICE_REQUEST_ID=SR.INCIDENT_ID (+) AND ATSK.UNIT_EFFECTIVITY_ID=AUEF.UNIT_EFFECTIVITY_ID(+) AND ATSK. INVENTORY_ITEM_ID = MTSB.INVENTORY_ITEM_ID(+) AND ATSK. ITEM_ORGANIZATION_ID = MTSB.ORGANIZATION_ID(+) AND LKUP1.LOOKUP_TYPE(+) = 'AHL_VWP_TASK_TYPE' AND LKUP1.LOOKUP_CODE(+) = ATSK.TASK_TYPE_CODE AND LKUP3.LOOKUP_TYPE(+) = 'AHL_VWP_TASK_STATUS' AND LKUP3.LOOKUP_CODE(+) = ATSK.STATUS_CODE AND AVTS.VISIT_ID = ATSK.VISIT_ID AND AVTS.TEMPLATE_FLAG = 'N' AND ATSK.DEPARTMENT_ID = BDPT.DEPARTMENT_ID(+) AND ATSK.VISIT_TASK_ID = AWO.VISIT_TASK_ID(+) AND NVL(ATSK.STATUS_CODE,'X') <> 'DELETED' AND ATSK.TASK_TYPE_CODE <> 'SUMMARY' AND LKUP2.LOOKUP_TYPE(+) = 'AHL_JOB_STATUS' AND LKUP2.LOOKUP_CODE(+) = AWO.STATUS_CODE AND ATSK.STAGE_ID = ASTG.STAGE_ID(+) AND PA.LOOKUP_TYPE(+) = 'SERVICE TYPE' AND PA.LOOKUP_CODE(+) = ATSK.SERVICE_TYPE_CODE AND ATSK.VISIT_TASK_ID = ASWE.VISIT_TASK_ID (+) AND ASWE.WARRANTY_CONTRACT_ID = AWCB.WARRANTY_CONTRACT_ID (+) AND ASWE.WARRANTY_CLAIM_ID = AWCL.WARRANTY_CLAIM_ID (+) AND LKUP5.LOOKUP_TYPE (+) = 'AHL_WARRANTY_ENTLMNT_STATUS' AND LKUP5.LOOKUP_CODE (+) = ASWE.ENTITLEMENT_STATUS_CODE AND LKUP6.LOOKUP_TYPE (+) = 'AHL_WARRANTY_CLAIM_STATUS' AND LKUP6.LOOKUP_CODE (+) = AWCL.CLAIM_STATUS_CODE UNION SELECT AVTS.VISIT_ID VISIT_ID, AVTS.VISIT_NUMBER, AVTS.TEMPLATE_FLAG, AVTS.STATUS_CODE, ATSK.VISIT_TASK_ID, ATSK.VISIT_TASK_NUMBER, ATSK.VISIT_TASK_NAME, ATSK.INVENTORY_ITEM_ID, MTSB.CONCATENATED_SEGMENTS, ATSK.ITEM_ORGANIZATION_ID, ATSK.INSTANCE_ID, CSIS.SERIAL_NUMBER, ATSK.MR_ROUTE_ID, AMRH.TITLE, to_char(NULL), to_char(NULL), to_char(NULL), AWO.WORKORDER_ID, AWO.WORKORDER_NAME, AWO.STATUS_CODE, LKUP2.MEANING, AWO.ACTUAL_START_DATE, AWO.ACTUAL_END_DATE, ATSK.SERVICE_REQUEST_ID, SR.INCIDENT_NUMBER, ATSK.START_DATE_TIME, ATSK.END_DATE_TIME, ATSK.TASK_TYPE_CODE, LKUP1.MEANING, ATSK.STATUS_CODE "TASK_STATUS_CODE", LKUP3.MEANING "TASK_STATUS_NAME", ATSK.STAGE_ID, ASTG.STAGE_NUM, ASTG.STAGE_NAME, AUEF.DUE_DATE, ATSK.DEPARTMENT_ID, BDPT.DESCRIPTION DEPARTMENT_NAME, ATSK.ORIGINATING_TASK_ID, ORIGTSK.VISIT_TASK_NUMBER ORIGINATING_TASK_NUMBER, ORIGTSK.VISIT_TASK_NAME ORIGINATING_TASK_NAME, ATSK.MR_ID, ATSK.UNIT_EFFECTIVITY_ID, AMRH.DESCRIPTION, ATSK.QUANTITY, CSIS.UNIT_OF_MEASURE, CSIS.INSTANCE_NUMBER, ATSK.SERVICE_TYPE_CODE, PA.MEANING, ASWE.WARRANTY_ENTITLEMENT_ID, AWCB.CONTRACT_NUMBER , ASWE.ENTITLEMENT_STATUS_CODE , LKUP4.MEANING ENTITLEMENT_STATUS , AWCL.CLAIM_NAME, AWCL.CLAIM_STATUS_CODE , LKUP5.MEANING CLAIM_STATUS , AHL_WARRANTY_ENTL_PVT.IS_TASK_WARRANTY_AVAILABLE(ATSK.VISIT_TASK_ID) TASK_WARRANTY_FLAG, AHL_WARRANTY_CONTRACTS_PVT.IS_INSTANCE_WARRANTY_AVAILABLE(ATSK.INSTANCE_ID) INSTANCE_WARRANTY_FLAG, NVL2(TO_NUMBER(ASWE.WARRANTY_ENTITLEMENT_ID), DECODE(ASWE.ENTITLEMENT_STATUS_CODE, 'NOT_APPLICABLE', 'Y', 'N'), 'Y') WARRANTY_NOT_APPL_FLAG FROM AHL_VISITS_VL AVTS, AHL_VISIT_TASKS_VL ATSK, AHL_VISIT_TASKS_VL ORIGTSK, AHL_MR_HEADERS_VL AMRH, AHL_UNIT_EFFECTIVITIES_B AUEF, CSI_ITEM_INSTANCES CSIS, CS_INCIDENTS_ALL_B SR, AHL_VWP_STAGES_VL ASTG, BOM_DEPARTMENTS BDPT, FND_LOOKUP_VALUES_VL LKUP1, FND_LOOKUP_VALUES_VL LKUP2, FND_LOOKUP_VALUES_VL LKUP3, MTL_SYSTEM_ITEMS_B_KFV MTSB, AHL_WORKORDERS AWO, PA_LOOKUPS PA, AHL_WARRANTY_ENTITLEMENTS ASWE, AHL_WARRANTY_CLAIMS_B AWCL , AHL_WARRANTY_CONTRACTS_B AWCB, FND_LOOKUP_VALUES_VL LKUP4 , FND_LOOKUP_VALUES_VL LKUP5 WHERE ATSK.INSTANCE_ID = CSIS.INSTANCE_ID (+) AND ATSK.ORIGINATING_TASK_ID = ORIGTSK.VISIT_TASK_ID(+) AND ATSK.SERVICE_REQUEST_ID = SR.INCIDENT_ID(+) AND ATSK.UNIT_EFFECTIVITY_ID = AUEF.UNIT_EFFECTIVITY_ID(+) AND ATSK.INVENTORY_ITEM_ID = MTSB.INVENTORY_ITEM_ID(+) AND ATSK.ITEM_ORGANIZATION_ID = MTSB.ORGANIZATION_ID(+) AND LKUP1.LOOKUP_TYPE(+) = 'AHL_VWP_TASK_TYPE' AND LKUP1.LOOKUP_CODE(+) = ATSK.TASK_TYPE_CODE AND LKUP3.LOOKUP_TYPE(+) = 'AHL_VWP_TASK_STATUS' AND LKUP3.LOOKUP_CODE(+) = ATSK.STATUS_CODE AND AVTS.VISIT_ID = ATSK.VISIT_ID AND AVTS.TEMPLATE_FLAG = 'N' AND ATSK.DEPARTMENT_ID = BDPT.DEPARTMENT_ID(+) AND ATSK.VISIT_TASK_ID = AWO.VISIT_TASK_ID(+) AND ATSK.MR_ID IS NOT NULL AND NVL(ATSK.STATUS_CODE,'X') <> 'DELETED' AND ATSK.TASK_TYPE_CODE = 'SUMMARY' AND AMRH.MR_HEADER_ID = ATSK.MR_ID AND LKUP2.LOOKUP_TYPE(+) = 'AHL_JOB_STATUS' AND LKUP2.LOOKUP_CODE(+) = AWO.STATUS_CODE AND ATSK.STAGE_ID = ASTG.STAGE_ID(+) AND PA.LOOKUP_TYPE(+) = 'SERVICE TYPE' AND PA.LOOKUP_CODE(+) = ATSK.SERVICE_TYPE_CODE AND ATSK.VISIT_TASK_ID = ASWE.VISIT_TASK_ID (+) AND ASWE.WARRANTY_CONTRACT_ID = AWCB.WARRANTY_CONTRACT_ID (+) AND ASWE.WARRANTY_CLAIM_ID = AWCL.WARRANTY_CLAIM_ID (+) AND LKUP4.LOOKUP_TYPE (+) = 'AHL_WARRANTY_ENTLMNT_STATUS' AND LKUP4.LOOKUP_CODE (+) = ASWE.ENTITLEMENT_STATUS_CODE AND LKUP5.LOOKUP_TYPE (+) = 'AHL_WARRANTY_CLAIM_STATUS' AND LKUP5.LOOKUP_CODE (+) = AWCL.CLAIM_STATUS_CODE UNION SELECT AVTS.VISIT_ID VISIT_ID, AVTS.VISIT_NUMBER, AVTS.TEMPLATE_FLAG, AVTS.STATUS_CODE, ATSK.VISIT_TASK_ID, ATSK.VISIT_TASK_NUMBER, ATSK.VISIT_TASK_NAME, ATSK.INVENTORY_ITEM_ID, MTSB.CONCATENATED_SEGMENTS, ATSK.ITEM_ORGANIZATION_ID, ATSK.INSTANCE_ID, CSIS.SERIAL_NUMBER, to_number(null), to_char(NULL), to_char(NULL), to_char(NULL), to_char(NULL), to_number(null), to_char(NULL), to_char(NULL), to_char(NULL), to_date(NULL), to_date(NULL), ATSK.SERVICE_REQUEST_ID, SR.INCIDENT_NUMBER, ATSK.START_DATE_TIME, ATSK.END_DATE_TIME, ATSK.TASK_TYPE_CODE, LKUP1.MEANING, ATSK.STATUS_CODE "TASK_STATUS_CODE", LKUP2.MEANING "TASK_STATUS_NAME", ATSK.STAGE_ID, ASTG.STAGE_NUM, ASTG.STAGE_NAME, to_date(NULL), ATSK.DEPARTMENT_ID, BDPT.DESCRIPTION DEPARTMENT_NAME, ATSK.ORIGINATING_TASK_ID, ORIGTSK.VISIT_TASK_NUMBER ORIGINATING_TASK_NUMBER, ORIGTSK.VISIT_TASK_NAME ORIGINATING_TASK_NAME, to_number(null), to_number(null), to_char(NULL), ATSK.QUANTITY, CSIS.UNIT_OF_MEASURE, CSIS.INSTANCE_NUMBER, ATSK.SERVICE_TYPE_CODE, PA.MEANING, TO_NUMBER(NULL) , TO_NUMBER(NULL) , TO_CHAR(NULL) , TO_CHAR(NULL) , TO_CHAR(NULL) , TO_CHAR(NULL) , TO_CHAR(NULL) , 'N' TASK_WARRANTY_FLAG , 'N' INSTANCE_WARRANTY_FLAG , 'Y' WARRANTY_NOT_APPL_FLAG FROM AHL_VISITS_VL AVTS, AHL_VISIT_TASKS_VL ATSK, AHL_VISIT_TASKS_VL ORIGTSK, CSI_ITEM_INSTANCES CSIS, CS_INCIDENTS_ALL_B SR, AHL_VWP_STAGES_VL ASTG, BOM_DEPARTMENTS BDPT, FND_LOOKUP_VALUES_VL LKUP1, FND_LOOKUP_VALUES_VL LKUP2, MTL_SYSTEM_ITEMS_B_KFV MTSB, PA_LOOKUPS PA WHERE ATSK.INSTANCE_ID = CSIS.INSTANCE_ID (+) AND ATSK.ORIGINATING_TASK_ID = ORIGTSK.VISIT_TASK_ID(+) AND ATSK.SERVICE_REQUEST_ID = SR.INCIDENT_ID(+) AND ATSK.INVENTORY_ITEM_ID = MTSB.INVENTORY_ITEM_ID(+) AND ATSK.ITEM_ORGANIZATION_ID = MTSB.ORGANIZATION_ID(+) AND LKUP1.LOOKUP_TYPE(+) = 'AHL_VWP_TASK_TYPE' AND LKUP1.LOOKUP_CODE(+) = ATSK.TASK_TYPE_CODE AND LKUP2.LOOKUP_TYPE(+) = 'AHL_VWP_TASK_STATUS' AND LKUP2.LOOKUP_CODE(+) = ATSK.STATUS_CODE AND AVTS.VISIT_ID = ATSK.VISIT_ID AND AVTS.TEMPLATE_FLAG = 'N' AND ATSK.DEPARTMENT_ID = BDPT.DEPARTMENT_ID(+) AND ATSK.MR_ID IS NULL AND NVL(ATSK.STATUS_CODE,'X') <> 'DELETED' AND ATSK.TASK_TYPE_CODE = 'SUMMARY' AND ATSK.STAGE_ID = ASTG.STAGE_ID(+) AND PA.LOOKUP_TYPE(+) = 'SERVICE TYPE' AND PA.LOOKUP_CODE(+) = ATSK.SERVICE_TYPE_CODE

View Text - HTML Formatted

SELECT AVTS.VISIT_ID VISIT_ID
, AVTS.VISIT_NUMBER
, AVTS.TEMPLATE_FLAG
, AVTS.STATUS_CODE
, ATSK.VISIT_TASK_ID
, ATSK.VISIT_TASK_NUMBER
, ATSK.VISIT_TASK_NAME
, ATSK.INVENTORY_ITEM_ID
, MTSB.CONCATENATED_SEGMENTS
, ATSK.ITEM_ORGANIZATION_ID
, ATSK.INSTANCE_ID
, CSIS.SERIAL_NUMBER
, ATSK.MR_ROUTE_ID
, AMRH.TITLE
, AMRR.ROUTE_NUMBER
, ARV.ROUTE_TYPE_CODE
, LKUP4.MEANING "ROUTE_TYPE"
, AWO.WORKORDER_ID
, AWO.WORKORDER_NAME
, AWO.STATUS_CODE
, LKUP2.MEANING
, AWO.ACTUAL_START_DATE
, AWO.ACTUAL_END_DATE
, ATSK.SERVICE_REQUEST_ID
, SR.INCIDENT_NUMBER
, ATSK.START_DATE_TIME
, ATSK.END_DATE_TIME
, ATSK.TASK_TYPE_CODE
, LKUP1.MEANING
, ATSK.STATUS_CODE "TASK_STATUS_CODE"
, LKUP3.MEANING "TASK_STATUS_NAME"
, ATSK.STAGE_ID
, ASTG.STAGE_NUM
, ASTG.STAGE_NAME
, AUEF.DUE_DATE
, ATSK.DEPARTMENT_ID
, BDPT.DESCRIPTION DEPARTMENT_NAME
, ATSK.ORIGINATING_TASK_ID
, ORIGTSK.VISIT_TASK_NUMBER ORIGINATING_TASK_NUMBER
, ORIGTSK.VISIT_TASK_NAME ORIGINATING_TASK_NAME
, ATSK.MR_ID
, ATSK.UNIT_EFFECTIVITY_ID
, AMRH.DESCRIPTION
, ATSK.QUANTITY
, CSIS.UNIT_OF_MEASURE
, CSIS.INSTANCE_NUMBER
, ATSK.SERVICE_TYPE_CODE
, PA.MEANING
, ASWE.WARRANTY_ENTITLEMENT_ID
, AWCB.CONTRACT_NUMBER
, ASWE.ENTITLEMENT_STATUS_CODE
, LKUP5.MEANING ENTITLEMENT_STATUS
, AWCL.CLAIM_NAME
, AWCL.CLAIM_STATUS_CODE
, LKUP6.MEANING CLAIM_STATUS
, AHL_WARRANTY_ENTL_PVT.IS_TASK_WARRANTY_AVAILABLE(ATSK.VISIT_TASK_ID) TASK_WARRANTY_FLAG
, AHL_WARRANTY_CONTRACTS_PVT.IS_INSTANCE_WARRANTY_AVAILABLE(ATSK.INSTANCE_ID) INSTANCE_WARRANTY_FLAG
, NVL2(TO_NUMBER(ASWE.WARRANTY_ENTITLEMENT_ID)
, DECODE(ASWE.ENTITLEMENT_STATUS_CODE
, 'NOT_APPLICABLE'
, 'Y'
, 'N')
, 'Y') WARRANTY_NOT_APPL_FLAG
FROM AHL_VISITS_VL AVTS
, AHL_VISIT_TASKS_VL ATSK
, AHL_VISIT_TASKS_VL ORIGTSK
, AHL_MR_ROUTES_V AMRR
, AHL_MR_HEADERS_VL AMRH
, AHL_UNIT_EFFECTIVITIES_B AUEF
, CSI_ITEM_INSTANCES CSIS
, CS_INCIDENTS_ALL_B SR
, BOM_DEPARTMENTS BDPT
, FND_LOOKUP_VALUES_VL LKUP1
, FND_LOOKUP_VALUES_VL LKUP2
, FND_LOOKUP_VALUES_VL LKUP3
, FND_LOOKUP_VALUES_VL LKUP4
, AHL_VWP_STAGES_VL ASTG
, MTL_SYSTEM_ITEMS_B_KFV MTSB
, AHL_ROUTES_B ARV
, AHL_WORKORDERS AWO
, PA_LOOKUPS PA
, AHL_WARRANTY_ENTITLEMENTS ASWE
, AHL_WARRANTY_CLAIMS_B AWCL
, AHL_WARRANTY_CONTRACTS_B AWCB
, FND_LOOKUP_VALUES_VL LKUP5
, FND_LOOKUP_VALUES_VL LKUP6
WHERE ATSK.INSTANCE_ID = CSIS.INSTANCE_ID (+)
AND ATSK.ORIGINATING_TASK_ID = ORIGTSK.VISIT_TASK_ID(+)
AND (ATSK.MR_ROUTE_ID = AMRR.MR_ROUTE_ID (+)
AND AMRR.MR_HEADER_ID= AMRH.MR_HEADER_ID (+))
AND AMRR.ROUTE_ID = ARV.ROUTE_ID (+)
AND LKUP4.LOOKUP_TYPE (+) = 'AHL_ROUTE_TYPE'
AND LKUP4.LOOKUP_CODE (+) = ARV.ROUTE_TYPE_CODE
AND ATSK.SERVICE_REQUEST_ID=SR.INCIDENT_ID (+)
AND ATSK.UNIT_EFFECTIVITY_ID=AUEF.UNIT_EFFECTIVITY_ID(+)
AND ATSK. INVENTORY_ITEM_ID = MTSB.INVENTORY_ITEM_ID(+)
AND ATSK. ITEM_ORGANIZATION_ID = MTSB.ORGANIZATION_ID(+)
AND LKUP1.LOOKUP_TYPE(+) = 'AHL_VWP_TASK_TYPE'
AND LKUP1.LOOKUP_CODE(+) = ATSK.TASK_TYPE_CODE
AND LKUP3.LOOKUP_TYPE(+) = 'AHL_VWP_TASK_STATUS'
AND LKUP3.LOOKUP_CODE(+) = ATSK.STATUS_CODE
AND AVTS.VISIT_ID = ATSK.VISIT_ID
AND AVTS.TEMPLATE_FLAG = 'N'
AND ATSK.DEPARTMENT_ID = BDPT.DEPARTMENT_ID(+)
AND ATSK.VISIT_TASK_ID = AWO.VISIT_TASK_ID(+)
AND NVL(ATSK.STATUS_CODE
, 'X') <> 'DELETED'
AND ATSK.TASK_TYPE_CODE <> 'SUMMARY'
AND LKUP2.LOOKUP_TYPE(+) = 'AHL_JOB_STATUS'
AND LKUP2.LOOKUP_CODE(+) = AWO.STATUS_CODE
AND ATSK.STAGE_ID = ASTG.STAGE_ID(+)
AND PA.LOOKUP_TYPE(+) = 'SERVICE TYPE'
AND PA.LOOKUP_CODE(+) = ATSK.SERVICE_TYPE_CODE
AND ATSK.VISIT_TASK_ID = ASWE.VISIT_TASK_ID (+)
AND ASWE.WARRANTY_CONTRACT_ID = AWCB.WARRANTY_CONTRACT_ID (+)
AND ASWE.WARRANTY_CLAIM_ID = AWCL.WARRANTY_CLAIM_ID (+)
AND LKUP5.LOOKUP_TYPE (+) = 'AHL_WARRANTY_ENTLMNT_STATUS'
AND LKUP5.LOOKUP_CODE (+) = ASWE.ENTITLEMENT_STATUS_CODE
AND LKUP6.LOOKUP_TYPE (+) = 'AHL_WARRANTY_CLAIM_STATUS'
AND LKUP6.LOOKUP_CODE (+) = AWCL.CLAIM_STATUS_CODE UNION SELECT AVTS.VISIT_ID VISIT_ID
, AVTS.VISIT_NUMBER
, AVTS.TEMPLATE_FLAG
, AVTS.STATUS_CODE
, ATSK.VISIT_TASK_ID
, ATSK.VISIT_TASK_NUMBER
, ATSK.VISIT_TASK_NAME
, ATSK.INVENTORY_ITEM_ID
, MTSB.CONCATENATED_SEGMENTS
, ATSK.ITEM_ORGANIZATION_ID
, ATSK.INSTANCE_ID
, CSIS.SERIAL_NUMBER
, ATSK.MR_ROUTE_ID
, AMRH.TITLE
, TO_CHAR(NULL)
, TO_CHAR(NULL)
, TO_CHAR(NULL)
, AWO.WORKORDER_ID
, AWO.WORKORDER_NAME
, AWO.STATUS_CODE
, LKUP2.MEANING
, AWO.ACTUAL_START_DATE
, AWO.ACTUAL_END_DATE
, ATSK.SERVICE_REQUEST_ID
, SR.INCIDENT_NUMBER
, ATSK.START_DATE_TIME
, ATSK.END_DATE_TIME
, ATSK.TASK_TYPE_CODE
, LKUP1.MEANING
, ATSK.STATUS_CODE "TASK_STATUS_CODE"
, LKUP3.MEANING "TASK_STATUS_NAME"
, ATSK.STAGE_ID
, ASTG.STAGE_NUM
, ASTG.STAGE_NAME
, AUEF.DUE_DATE
, ATSK.DEPARTMENT_ID
, BDPT.DESCRIPTION DEPARTMENT_NAME
, ATSK.ORIGINATING_TASK_ID
, ORIGTSK.VISIT_TASK_NUMBER ORIGINATING_TASK_NUMBER
, ORIGTSK.VISIT_TASK_NAME ORIGINATING_TASK_NAME
, ATSK.MR_ID
, ATSK.UNIT_EFFECTIVITY_ID
, AMRH.DESCRIPTION
, ATSK.QUANTITY
, CSIS.UNIT_OF_MEASURE
, CSIS.INSTANCE_NUMBER
, ATSK.SERVICE_TYPE_CODE
, PA.MEANING
, ASWE.WARRANTY_ENTITLEMENT_ID
, AWCB.CONTRACT_NUMBER
, ASWE.ENTITLEMENT_STATUS_CODE
, LKUP4.MEANING ENTITLEMENT_STATUS
, AWCL.CLAIM_NAME
, AWCL.CLAIM_STATUS_CODE
, LKUP5.MEANING CLAIM_STATUS
, AHL_WARRANTY_ENTL_PVT.IS_TASK_WARRANTY_AVAILABLE(ATSK.VISIT_TASK_ID) TASK_WARRANTY_FLAG
, AHL_WARRANTY_CONTRACTS_PVT.IS_INSTANCE_WARRANTY_AVAILABLE(ATSK.INSTANCE_ID) INSTANCE_WARRANTY_FLAG
, NVL2(TO_NUMBER(ASWE.WARRANTY_ENTITLEMENT_ID)
, DECODE(ASWE.ENTITLEMENT_STATUS_CODE
, 'NOT_APPLICABLE'
, 'Y'
, 'N')
, 'Y') WARRANTY_NOT_APPL_FLAG
FROM AHL_VISITS_VL AVTS
, AHL_VISIT_TASKS_VL ATSK
, AHL_VISIT_TASKS_VL ORIGTSK
, AHL_MR_HEADERS_VL AMRH
, AHL_UNIT_EFFECTIVITIES_B AUEF
, CSI_ITEM_INSTANCES CSIS
, CS_INCIDENTS_ALL_B SR
, AHL_VWP_STAGES_VL ASTG
, BOM_DEPARTMENTS BDPT
, FND_LOOKUP_VALUES_VL LKUP1
, FND_LOOKUP_VALUES_VL LKUP2
, FND_LOOKUP_VALUES_VL LKUP3
, MTL_SYSTEM_ITEMS_B_KFV MTSB
, AHL_WORKORDERS AWO
, PA_LOOKUPS PA
, AHL_WARRANTY_ENTITLEMENTS ASWE
, AHL_WARRANTY_CLAIMS_B AWCL
, AHL_WARRANTY_CONTRACTS_B AWCB
, FND_LOOKUP_VALUES_VL LKUP4
, FND_LOOKUP_VALUES_VL LKUP5
WHERE ATSK.INSTANCE_ID = CSIS.INSTANCE_ID (+)
AND ATSK.ORIGINATING_TASK_ID = ORIGTSK.VISIT_TASK_ID(+)
AND ATSK.SERVICE_REQUEST_ID = SR.INCIDENT_ID(+)
AND ATSK.UNIT_EFFECTIVITY_ID = AUEF.UNIT_EFFECTIVITY_ID(+)
AND ATSK.INVENTORY_ITEM_ID = MTSB.INVENTORY_ITEM_ID(+)
AND ATSK.ITEM_ORGANIZATION_ID = MTSB.ORGANIZATION_ID(+)
AND LKUP1.LOOKUP_TYPE(+) = 'AHL_VWP_TASK_TYPE'
AND LKUP1.LOOKUP_CODE(+) = ATSK.TASK_TYPE_CODE
AND LKUP3.LOOKUP_TYPE(+) = 'AHL_VWP_TASK_STATUS'
AND LKUP3.LOOKUP_CODE(+) = ATSK.STATUS_CODE
AND AVTS.VISIT_ID = ATSK.VISIT_ID
AND AVTS.TEMPLATE_FLAG = 'N'
AND ATSK.DEPARTMENT_ID = BDPT.DEPARTMENT_ID(+)
AND ATSK.VISIT_TASK_ID = AWO.VISIT_TASK_ID(+)
AND ATSK.MR_ID IS NOT NULL
AND NVL(ATSK.STATUS_CODE
, 'X') <> 'DELETED'
AND ATSK.TASK_TYPE_CODE = 'SUMMARY'
AND AMRH.MR_HEADER_ID = ATSK.MR_ID
AND LKUP2.LOOKUP_TYPE(+) = 'AHL_JOB_STATUS'
AND LKUP2.LOOKUP_CODE(+) = AWO.STATUS_CODE
AND ATSK.STAGE_ID = ASTG.STAGE_ID(+)
AND PA.LOOKUP_TYPE(+) = 'SERVICE TYPE'
AND PA.LOOKUP_CODE(+) = ATSK.SERVICE_TYPE_CODE
AND ATSK.VISIT_TASK_ID = ASWE.VISIT_TASK_ID (+)
AND ASWE.WARRANTY_CONTRACT_ID = AWCB.WARRANTY_CONTRACT_ID (+)
AND ASWE.WARRANTY_CLAIM_ID = AWCL.WARRANTY_CLAIM_ID (+)
AND LKUP4.LOOKUP_TYPE (+) = 'AHL_WARRANTY_ENTLMNT_STATUS'
AND LKUP4.LOOKUP_CODE (+) = ASWE.ENTITLEMENT_STATUS_CODE
AND LKUP5.LOOKUP_TYPE (+) = 'AHL_WARRANTY_CLAIM_STATUS'
AND LKUP5.LOOKUP_CODE (+) = AWCL.CLAIM_STATUS_CODE UNION SELECT AVTS.VISIT_ID VISIT_ID
, AVTS.VISIT_NUMBER
, AVTS.TEMPLATE_FLAG
, AVTS.STATUS_CODE
, ATSK.VISIT_TASK_ID
, ATSK.VISIT_TASK_NUMBER
, ATSK.VISIT_TASK_NAME
, ATSK.INVENTORY_ITEM_ID
, MTSB.CONCATENATED_SEGMENTS
, ATSK.ITEM_ORGANIZATION_ID
, ATSK.INSTANCE_ID
, CSIS.SERIAL_NUMBER
, TO_NUMBER(NULL)
, TO_CHAR(NULL)
, TO_CHAR(NULL)
, TO_CHAR(NULL)
, TO_CHAR(NULL)
, TO_NUMBER(NULL)
, TO_CHAR(NULL)
, TO_CHAR(NULL)
, TO_CHAR(NULL)
, TO_DATE(NULL)
, TO_DATE(NULL)
, ATSK.SERVICE_REQUEST_ID
, SR.INCIDENT_NUMBER
, ATSK.START_DATE_TIME
, ATSK.END_DATE_TIME
, ATSK.TASK_TYPE_CODE
, LKUP1.MEANING
, ATSK.STATUS_CODE "TASK_STATUS_CODE"
, LKUP2.MEANING "TASK_STATUS_NAME"
, ATSK.STAGE_ID
, ASTG.STAGE_NUM
, ASTG.STAGE_NAME
, TO_DATE(NULL)
, ATSK.DEPARTMENT_ID
, BDPT.DESCRIPTION DEPARTMENT_NAME
, ATSK.ORIGINATING_TASK_ID
, ORIGTSK.VISIT_TASK_NUMBER ORIGINATING_TASK_NUMBER
, ORIGTSK.VISIT_TASK_NAME ORIGINATING_TASK_NAME
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_CHAR(NULL)
, ATSK.QUANTITY
, CSIS.UNIT_OF_MEASURE
, CSIS.INSTANCE_NUMBER
, ATSK.SERVICE_TYPE_CODE
, PA.MEANING
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_CHAR(NULL)
, TO_CHAR(NULL)
, TO_CHAR(NULL)
, TO_CHAR(NULL)
, TO_CHAR(NULL)
, 'N' TASK_WARRANTY_FLAG
, 'N' INSTANCE_WARRANTY_FLAG
, 'Y' WARRANTY_NOT_APPL_FLAG
FROM AHL_VISITS_VL AVTS
, AHL_VISIT_TASKS_VL ATSK
, AHL_VISIT_TASKS_VL ORIGTSK
, CSI_ITEM_INSTANCES CSIS
, CS_INCIDENTS_ALL_B SR
, AHL_VWP_STAGES_VL ASTG
, BOM_DEPARTMENTS BDPT
, FND_LOOKUP_VALUES_VL LKUP1
, FND_LOOKUP_VALUES_VL LKUP2
, MTL_SYSTEM_ITEMS_B_KFV MTSB
, PA_LOOKUPS PA
WHERE ATSK.INSTANCE_ID = CSIS.INSTANCE_ID (+)
AND ATSK.ORIGINATING_TASK_ID = ORIGTSK.VISIT_TASK_ID(+)
AND ATSK.SERVICE_REQUEST_ID = SR.INCIDENT_ID(+)
AND ATSK.INVENTORY_ITEM_ID = MTSB.INVENTORY_ITEM_ID(+)
AND ATSK.ITEM_ORGANIZATION_ID = MTSB.ORGANIZATION_ID(+)
AND LKUP1.LOOKUP_TYPE(+) = 'AHL_VWP_TASK_TYPE'
AND LKUP1.LOOKUP_CODE(+) = ATSK.TASK_TYPE_CODE
AND LKUP2.LOOKUP_TYPE(+) = 'AHL_VWP_TASK_STATUS'
AND LKUP2.LOOKUP_CODE(+) = ATSK.STATUS_CODE
AND AVTS.VISIT_ID = ATSK.VISIT_ID
AND AVTS.TEMPLATE_FLAG = 'N'
AND ATSK.DEPARTMENT_ID = BDPT.DEPARTMENT_ID(+)
AND ATSK.MR_ID IS NULL
AND NVL(ATSK.STATUS_CODE
, 'X') <> 'DELETED'
AND ATSK.TASK_TYPE_CODE = 'SUMMARY'
AND ATSK.STAGE_ID = ASTG.STAGE_ID(+)
AND PA.LOOKUP_TYPE(+) = 'SERVICE TYPE'
AND PA.LOOKUP_CODE(+) = ATSK.SERVICE_TYPE_CODE