VIEW: APPS.CSD_IR_REPAIR_JOBS_V

Source

View Text - Preformatted

SELECT dra.ROWID, dra.REPAIR_LINE_ID, dra.CREATED_BY, dra.CREATION_DATE, dra.LAST_UPDATED_BY, dra.LAST_UPDATE_DATE, dra.LAST_UPDATE_LOGIN, dra.REPAIR_NUMBER, dra.INCIDENT_ID, sr.INCIDENT_NUMBER, sr.CUSTOMER_ID, sr.caller_type, dra.INVENTORY_ITEM_ID, item.concatenated_segments, item.description, item.organization_id, dra.UNIT_OF_MEASURE, dra.quantity, nvl(dra.quantity_rcvd, 0) released_quantity, (nvl(dra.quantity, 0) - nvl(dra.quantity_in_wip, 0)) REMAINING_QUANTITY, nvl(dra.QUANTITY_IN_WIP, 0), nvl(dra.QUANTITY_RCVD, 0), nvl(dra.QUANTITY_SHIPPED, 0), dra.PROJECT_ID, dra.TASK_ID, dra.CUSTOMER_PRODUCT_ID, cp.INSTANCE_NUMBER, dra.SERIAL_NUMBER, dra.RESOURCE_ID, null, null, dra.REPAIR_TYPE_ID, rtype.NAME, dra.INSTANCE_ID, dra.APPROVAL_REQUIRED_FLAG, dra.APPROVAL_STATUS, fnd1.MEANING, dra.STATUS, fnd2.MEANING, dra.DATE_CLOSED, dra.PROMISE_DATE, dra.ATTRIBUTE_CATEGORY, dra.ATTRIBUTE1, dra.ATTRIBUTE2, dra.ATTRIBUTE3, dra.ATTRIBUTE4, dra.ATTRIBUTE5, dra.ATTRIBUTE6, dra.ATTRIBUTE7, dra.ATTRIBUTE8, dra.ATTRIBUTE9, dra.ATTRIBUTE10, dra.ATTRIBUTE11, dra.ATTRIBUTE12, dra.ATTRIBUTE13, dra.ATTRIBUTE14, dra.ATTRIBUTE15, decode (sr.CALLER_TYPE, 'ORGANIZATION', hzp.party_name, 'PERSON', hzp.PERSON_FIRST_NAME||' '||hzp.PERSON_LAST_NAME, papf.full_name) "PARTY_NAME" , decode (sr.CALLER_TYPE, 'ORGANIZATION', hzp.PARTY_NUMBER, 'PERSON' , hzp.PARTY_NUMBER, papf.EMPLOYEE_NUMBER ) "PARTY_NUMBER" , sev.name severity, urg.name urgency, citb.name incident_type, stat.name incident_status, sr.incident_date from CSD_REPAIRS dra, CS_INCIDENTS_ALL_B sr, CSI_ITEM_INSTANCES CP, MTL_SYSTEM_ITEMS_VL item, CSD_REPAIR_TYPES_VL rtype, FND_LOOKUPS fnd1, FND_LOOKUPS fnd2, HZ_PARTIES hzp, CS_INCIDENT_SEVERITIES_VL sev, CS_INCIDENT_URGENCIES_VL urg, CS_INCIDENT_TYPES_VL citb, CS_INCIDENT_STATUSES_VL stat, PER_ALL_PEOPLE_F papf WHERE dra.incident_id = sr.incident_id and dra.repair_type_id = rtype.repair_type_id and dra.approval_status = fnd1.lookup_code (+) and fnd1.lookup_type (+) = 'CSD_APPROVAL_STATUS' and dra.status = fnd2.lookup_code and fnd2.lookup_type = 'CSD_REPAIR_STATUS' and dra.inventory_item_id = item.inventory_item_id and (dra.approval_required_flag = 'N' or (dra.approval_required_flag = 'Y' and dra.approval_status = 'A')) and dra.CUSTOMER_PRODUCT_ID = cp.INSTANCE_ID(+) and sr.customer_id = hzp.party_id and sr.incident_severity_id = sev.incident_severity_id and sr.incident_urgency_id = urg.incident_urgency_id and citb.INCIDENT_TYPE_ID = sr.INCIDENT_TYPE_ID and stat.INCIDENT_STATUS_ID = sr.INCIDENT_STATUS_ID and sr.EMPLOYEE_ID = papf.PERSON_ID(+)

View Text - HTML Formatted

SELECT DRA.ROWID
, DRA.REPAIR_LINE_ID
, DRA.CREATED_BY
, DRA.CREATION_DATE
, DRA.LAST_UPDATED_BY
, DRA.LAST_UPDATE_DATE
, DRA.LAST_UPDATE_LOGIN
, DRA.REPAIR_NUMBER
, DRA.INCIDENT_ID
, SR.INCIDENT_NUMBER
, SR.CUSTOMER_ID
, SR.CALLER_TYPE
, DRA.INVENTORY_ITEM_ID
, ITEM.CONCATENATED_SEGMENTS
, ITEM.DESCRIPTION
, ITEM.ORGANIZATION_ID
, DRA.UNIT_OF_MEASURE
, DRA.QUANTITY
, NVL(DRA.QUANTITY_RCVD
, 0) RELEASED_QUANTITY
, (NVL(DRA.QUANTITY
, 0) - NVL(DRA.QUANTITY_IN_WIP
, 0)) REMAINING_QUANTITY
, NVL(DRA.QUANTITY_IN_WIP
, 0)
, NVL(DRA.QUANTITY_RCVD
, 0)
, NVL(DRA.QUANTITY_SHIPPED
, 0)
, DRA.PROJECT_ID
, DRA.TASK_ID
, DRA.CUSTOMER_PRODUCT_ID
, CP.INSTANCE_NUMBER
, DRA.SERIAL_NUMBER
, DRA.RESOURCE_ID
, NULL
, NULL
, DRA.REPAIR_TYPE_ID
, RTYPE.NAME
, DRA.INSTANCE_ID
, DRA.APPROVAL_REQUIRED_FLAG
, DRA.APPROVAL_STATUS
, FND1.MEANING
, DRA.STATUS
, FND2.MEANING
, DRA.DATE_CLOSED
, DRA.PROMISE_DATE
, DRA.ATTRIBUTE_CATEGORY
, DRA.ATTRIBUTE1
, DRA.ATTRIBUTE2
, DRA.ATTRIBUTE3
, DRA.ATTRIBUTE4
, DRA.ATTRIBUTE5
, DRA.ATTRIBUTE6
, DRA.ATTRIBUTE7
, DRA.ATTRIBUTE8
, DRA.ATTRIBUTE9
, DRA.ATTRIBUTE10
, DRA.ATTRIBUTE11
, DRA.ATTRIBUTE12
, DRA.ATTRIBUTE13
, DRA.ATTRIBUTE14
, DRA.ATTRIBUTE15
, DECODE (SR.CALLER_TYPE
, 'ORGANIZATION'
, HZP.PARTY_NAME
, 'PERSON'
, HZP.PERSON_FIRST_NAME||' '||HZP.PERSON_LAST_NAME
, PAPF.FULL_NAME) "PARTY_NAME"
, DECODE (SR.CALLER_TYPE
, 'ORGANIZATION'
, HZP.PARTY_NUMBER
, 'PERSON'
, HZP.PARTY_NUMBER
, PAPF.EMPLOYEE_NUMBER ) "PARTY_NUMBER"
, SEV.NAME SEVERITY
, URG.NAME URGENCY
, CITB.NAME INCIDENT_TYPE
, STAT.NAME INCIDENT_STATUS
, SR.INCIDENT_DATE
FROM CSD_REPAIRS DRA
, CS_INCIDENTS_ALL_B SR
, CSI_ITEM_INSTANCES CP
, MTL_SYSTEM_ITEMS_VL ITEM
, CSD_REPAIR_TYPES_VL RTYPE
, FND_LOOKUPS FND1
, FND_LOOKUPS FND2
, HZ_PARTIES HZP
, CS_INCIDENT_SEVERITIES_VL SEV
, CS_INCIDENT_URGENCIES_VL URG
, CS_INCIDENT_TYPES_VL CITB
, CS_INCIDENT_STATUSES_VL STAT
, PER_ALL_PEOPLE_F PAPF
WHERE DRA.INCIDENT_ID = SR.INCIDENT_ID
AND DRA.REPAIR_TYPE_ID = RTYPE.REPAIR_TYPE_ID
AND DRA.APPROVAL_STATUS = FND1.LOOKUP_CODE (+)
AND FND1.LOOKUP_TYPE (+) = 'CSD_APPROVAL_STATUS'
AND DRA.STATUS = FND2.LOOKUP_CODE
AND FND2.LOOKUP_TYPE = 'CSD_REPAIR_STATUS'
AND DRA.INVENTORY_ITEM_ID = ITEM.INVENTORY_ITEM_ID
AND (DRA.APPROVAL_REQUIRED_FLAG = 'N' OR (DRA.APPROVAL_REQUIRED_FLAG = 'Y'
AND DRA.APPROVAL_STATUS = 'A'))
AND DRA.CUSTOMER_PRODUCT_ID = CP.INSTANCE_ID(+)
AND SR.CUSTOMER_ID = HZP.PARTY_ID
AND SR.INCIDENT_SEVERITY_ID = SEV.INCIDENT_SEVERITY_ID
AND SR.INCIDENT_URGENCY_ID = URG.INCIDENT_URGENCY_ID
AND CITB.INCIDENT_TYPE_ID = SR.INCIDENT_TYPE_ID
AND STAT.INCIDENT_STATUS_ID = SR.INCIDENT_STATUS_ID
AND SR.EMPLOYEE_ID = PAPF.PERSON_ID(+)