View: CSF_DEBRIEF_TASKS_V

Product:	CSF - Field Service
Description:	Task information collection
Implementation/DBA Data:	APPS.CSF_DEBRIEF_TASKS_V

View Text

SELECT CDH.ROWID
, CSF_UTIL_PVT.GET_OBJECT_NAME(JTA.RESOURCE_TYPE_CODE
, JTA.RESOURCE_ID)
, JTA.RESOURCE_ID
, CSF_UTIL_PVT.GET_OBJECT_NAME(JTV.OWNER_TYPE_CODE
, JTV.OWNER_ID)
, JTV.OWNER_ID
, JTV.OWNER_TYPE_CODE
, JTV.TASK_NUMBER
, JTV.TASK_NAME
, JTV.TASK_ID
, JTS1.NAME TASK_STATUS
, JTS1.TASK_STATUS_ID
, JTP.IMPORTANCE_LEVEL
, JTP.NAME TASK_PRIORITY_NAME
, JTS2.NAME TASK_ASSIGNMENT_STATUS
, JTV.SOURCE_OBJECT_TYPE_CODE
, JOV.NAME
, JTV.SOURCE_OBJECT_NAME
, CIA.INVENTORY_ITEM_ID
, MSB.CONCATENATED_SEGMENTS
, NULL
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, CIA.INCIDENT_ID
, CDH.DEBRIEF_NUMBER
, CDH.DEBRIEF_HEADER_ID
, CDH.DEBRIEF_DATE
, JPA.PARTY_ID
, JPA.PARTY_NAME
, JPA.PARTY_NUMBER
, JTA.RESOURCE_TYPE_CODE
, JTA.TASK_ASSIGNMENT_ID
, JO.NAME RESOURCE_TYPE_NAME
, CIA.ACCOUNT_ID
, CIA.CUSTOMER_PRODUCT_ID
, TO_NUMBER(NULL)
, JTV.SCHEDULED_START_DATE
, JTV.SCHEDULED_END_DATE
, JTV.PLANNED_EFFORT
, JTV.PLANNED_EFFORT_UOM
, CDH.PROCESSED_FLAG
, JTS2.CLOSED_FLAG
, JTS2.COMPLETED_FLAG
, JTS2.CANCELLED_FLAG
, JTS2.ON_HOLD_FLAG
, JTS2.REJECTED_FLAG
, CDH.ATTRIBUTE1
, CDH.ATTRIBUTE2
, CDH.ATTRIBUTE3
, CDH.ATTRIBUTE4
, CDH.ATTRIBUTE5
, CDH.ATTRIBUTE6
, CDH.ATTRIBUTE7
, CDH.ATTRIBUTE8
, CDH.ATTRIBUTE9
, CDH.ATTRIBUTE10
, CDH.ATTRIBUTE11
, CDH.ATTRIBUTE12
, CDH.ATTRIBUTE13
, CDH.ATTRIBUTE14
, CDH.ATTRIBUTE15
, CDH.ATTRIBUTE_CATEGORY
, CDH.CREATED_BY
, CDH.CREATION_DATE
, CDH.LAST_UPDATED_BY
, CDH.LAST_UPDATE_DATE
, CDH.LAST_UPDATE_LOGIN
, JTV.ADDRESS_ID
, JTTV.TASK_TYPE_ID
, JTTV.NAME
, JTV.DESCRIPTION
, HZ_FORMAT_PUB.FORMAT_ADDRESS(HPS.LOCATION_ID)
, NVL(CIA.CURRENT_SERIAL_NUMBER
, CII.SERIAL_NUMBER)
, NVL(CIA.EXTERNAL_REFERENCE
, CII.EXTERNAL_REFERENCE)
, OELDV.CONTRACT_NUMBER
, OELDV.SERVICE_NAME
, OELDV.SERVICE_DESCRIPTION
, OELDV.WARRANTY_FLAG
, OELDV.STATUS_CODE
, OELDV.SERVICE_START_DATE
, OELDV.SERVICE_END_DATE
FROM JTF_TASK_PRIORITIES_VL JTP
, JTF_TASK_TYPES_VL JTTV
, OKS_ENT_LINE_DETAILS_V OELDV
, HZ_PARTY_SITES HPS
, JTF_TASK_STATUSES_VL JTS1
, JTF_TASK_STATUSES_VL JTS2
, JTF_PARTIES_ALL_V JPA
, CSF_DEBRIEF_HEADERS CDH
, JTF_TASKS_VL JTV
, JTF_TASK_ASSIGNMENTS JTA
, JTF_OBJECTS_VL JO
, CS_INCIDENTS_ALL CIA
, JTF_OBJECTS_VL JOV
, MTL_SYSTEM_ITEMS_B_KFV MSB
, CSI_ITEM_INSTANCES CII
WHERE CDH.TASK_ASSIGNMENT_ID (+) = JTA.TASK_ASSIGNMENT_ID
AND JTA.TASK_ID = JTV.TASK_ID
AND JTP.TASK_PRIORITY_ID (+) = JTV.TASK_PRIORITY_ID
AND JO.OBJECT_CODE = JTA.RESOURCE_TYPE_CODE
AND NVL(JTV.DELETED_FLAG
, 'N') != 'Y'
AND CIA.CUSTOMER_ID = JPA.PARTY_ID
AND JTV.TASK_STATUS_ID = JTS1.TASK_STATUS_ID
AND JTA.ASSIGNMENT_STATUS_ID = JTS2.TASK_STATUS_ID
AND JTA.ASSIGNEE_ROLE = 'ASSIGNEE'
AND JTV.SOURCE_OBJECT_TYPE_CODE = 'SR'
AND CIA.INCIDENT_ID = JTV.SOURCE_OBJECT_ID
AND JOV.OBJECT_CODE = JTV.SOURCE_OBJECT_TYPE_CODE
AND MSB.INVENTORY_ITEM_ID(+) =CIA.INVENTORY_ITEM_ID
AND MSB.ORGANIZATION_ID(+) = CS_STD.GET_ITEM_VALDN_ORGZN_ID
AND JTV.TASK_TYPE_ID = JTTV.TASK_TYPE_ID
AND HPS.PARTY_SITE_ID(+) = JTV.ADDRESS_ID
AND OELDV.SERVICE_LINE_ID(+) = CIA.CONTRACT_SERVICE_ID
AND CIA.CUSTOMER_PRODUCT_ID = CII.INSTANCE_ID (+) UNION SELECT CDH.ROWID
, CSF_UTIL_PVT.GET_OBJECT_NAME(JTA.RESOURCE_TYPE_CODE
, JTA.RESOURCE_ID)
, JTA.RESOURCE_ID
, CSF_UTIL_PVT.GET_OBJECT_NAME(JTV.OWNER_TYPE_CODE
, JTV.OWNER_ID)
, JTV.OWNER_ID
, JTV.OWNER_TYPE_CODE
, JTV.TASK_NUMBER
, JTV.TASK_NAME
, JTV.TASK_ID
, JTS1.NAME TASK_STATUS
, JTS1.TASK_STATUS_ID
, JTP.IMPORTANCE_LEVEL
, JTP.NAME TASK_PRIORITY_NAME
, JTS2.NAME TASK_ASSIGNMENT_STATUS
, JTV.SOURCE_OBJECT_TYPE_CODE
, JOV.NAME
, CR.REPAIR_NUMBER
, CR.INVENTORY_ITEM_ID
, MSB.CONCATENATED_SEGMENTS
, CR.UNIT_OF_MEASURE
, CR.QUANTITY
, CR. QUANTITY_RCVD
, CR.QUANTITY_SHIPPED
, CR.INCIDENT_ID
, CDH.DEBRIEF_NUMBER
, CDH.DEBRIEF_HEADER_ID
, CDH.DEBRIEF_DATE
, JPA.PARTY_ID
, JPA.PARTY_NAME
, JPA.PARTY_NUMBER
, JTA.RESOURCE_TYPE_CODE
, JTA.TASK_ASSIGNMENT_ID
, JO.NAME RESOURCE_TYPE_NAME
, -1
, CR.CUSTOMER_PRODUCT_ID
, CR.REPAIR_LINE_ID
, JTV.SCHEDULED_START_DATE
, JTV.SCHEDULED_END_DATE
, JTV.PLANNED_EFFORT
, JTV.PLANNED_EFFORT_UOM
, CDH.PROCESSED_FLAG
, JTS2.CLOSED_FLAG
, JTS2.COMPLETED_FLAG
, JTS2.CANCELLED_FLAG
, JTS2.ON_HOLD_FLAG
, JTS2.REJECTED_FLAG
, CDH.ATTRIBUTE1
, CDH.ATTRIBUTE2
, CDH.ATTRIBUTE3
, CDH.ATTRIBUTE4
, CDH.ATTRIBUTE5
, CDH.ATTRIBUTE6
, CDH.ATTRIBUTE7
, CDH.ATTRIBUTE8
, CDH.ATTRIBUTE9
, CDH.ATTRIBUTE10
, CDH.ATTRIBUTE11
, CDH.ATTRIBUTE12
, CDH.ATTRIBUTE13
, CDH.ATTRIBUTE14
, CDH.ATTRIBUTE15
, CDH.ATTRIBUTE_CATEGORY
, CDH.CREATED_BY
, CDH.CREATION_DATE
, CDH.LAST_UPDATED_BY
, CDH.LAST_UPDATE_DATE
, CDH.LAST_UPDATE_LOGIN
, JTV.ADDRESS_ID
, JTTV.TASK_TYPE_ID
, JTTV.NAME
, JTV.DESCRIPTION
, TO_CHAR(NULL)
, TO_CHAR(NULL)
, TO_CHAR(NULL)
, TO_CHAR(NULL)
, TO_CHAR(NULL)
, TO_CHAR(NULL)
, TO_CHAR(NULL)
, TO_CHAR(NULL)
, TO_DATE(NULL)
, TO_DATE(NULL)
FROM JTF_TASK_PRIORITIES_VL JTP
, JTF_TASK_TYPES_VL JTTV
, JTF_TASK_STATUSES_VL JTS1
, JTF_TASK_STATUSES_VL JTS2
, JTF_PARTIES_ALL_V JPA
, CSF_DEBRIEF_HEADERS CDH
, JTF_TASKS_VL JTV
, JTF_TASK_ASSIGNMENTS JTA
, JTF_OBJECTS_VL JO
, CSD_REPAIRS CR
, JTF_OBJECTS_VL JOV
, MTL_SYSTEM_ITEMS_B_KFV MSB
WHERE CDH.TASK_ASSIGNMENT_ID (+) = JTA.TASK_ASSIGNMENT_ID
AND JTA.TASK_ID = JTV.TASK_ID
AND JTP.TASK_PRIORITY_ID (+) = JTV.TASK_PRIORITY_ID
AND JO.OBJECT_CODE = JTA.RESOURCE_TYPE_CODE
AND NVL(JTV.DELETED_FLAG
, 'N') != 'Y'
AND JTV.CUSTOMER_ID = JPA.PARTY_ID
AND JTV.TASK_STATUS_ID = JTS1.TASK_STATUS_ID
AND JTA.ASSIGNMENT_STATUS_ID = JTS2.TASK_STATUS_ID
AND JTA.ASSIGNEE_ROLE = 'ASSIGNEE'
AND JTV.SOURCE_OBJECT_TYPE_CODE = 'DR'
AND JTV.SOURCE_OBJECT_ID=CR.REPAIR_LINE_ID
AND JOV.OBJECT_CODE = JTV.SOURCE_OBJECT_TYPE_CODE
AND MSB.INVENTORY_ITEM_ID =CR.INVENTORY_ITEM_ID
AND MSB.ORGANIZATION_ID = CS_STD.GET_ITEM_VALDN_ORGZN_ID
AND JTV.TASK_TYPE_ID = JTTV.TASK_TYPE_ID

Columns