VIEW: APPS.CSF_DEBRIEF_TASKS_V

Source

View Text - Preformatted

SELECT /*+ push_pred(oeldv ) */ 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.task_priority_id, 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.task_priority_id, 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

View Text - HTML Formatted

SELECT /*+ PUSH_PRED(OELDV ) */ 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.TASK_PRIORITY_ID
, 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.TASK_PRIORITY_ID
, 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