View: CSD_REPAIR_JOBS_V

Product:	CSD - Depot Repair
Description:	The view for getting repair orders with service request information.
Implementation/DBA Data:	APPS.CSD_REPAIR_JOBS_V

View Text

SELECT DRA.ROWID ROW_ID
, DRA.REPAIR_LINE_ID REPAIR_LINE_ID
, DRA.CREATED_BY CREATED_BY
, DRA.CREATION_DATE CREATION_DATE
, DRA.LAST_UPDATED_BY LAST_UPDATED_BY
, DRA.LAST_UPDATE_DATE LAST_UPDATE_DATE
, DRA.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, DRA.REPAIR_NUMBER REPAIR_NUMBER
, DRA.INCIDENT_ID INCIDENT_ID
, SR.INCIDENT_NUMBER INCIDENT_NUMBER
, SR.CUSTOMER_ID CUSTOMER_ID
, SR.CALLER_TYPE CALLER_TYPE
, DRA.INVENTORY_ITEM_ID INVENTORY_ITEM_ID
, ITEM.CONCATENATED_SEGMENTS ITEM
, ITEM.DESCRIPTION ITEM_DESC
, ITEM.ORGANIZATION_ID ORGANIZATION_ID
, DRA.UNIT_OF_MEASURE UNIT_OF_MEASURE
, DRA.QUANTITY REPAIR_ORDER_QUANTITY
, NVL(DRA.QUANTITY_RCVD
, 0) RELEASED_QUANTITY
, (NVL(DRA.QUANTITY_RCVD
, 0) - NVL(DRA.QUANTITY_IN_WIP
, 0)) REMAINING_QUANTITY
, NVL(DRA.QUANTITY_IN_WIP
, 0) QUANTITY_IN_WIP
, NVL(DRA.QUANTITY_RCVD
, 0) RECEIVED_QUANTITY
, NVL(DRA.QUANTITY_SHIPPED
, 0) QUANTITY_SHIPPED
, DRA.PROJECT_ID PROJECT_ID
, DRA.TASK_ID TASK_ID
, DRA.CUSTOMER_PRODUCT_ID CUSTOMER_PRODUCT_ID
, CP.INSTANCE_NUMBER REFERENCE_NUMBER
, DRA.SERIAL_NUMBER SERIAL_NUMBER
, DRA.RESOURCE_ID RESOURCE_ID
, NULL RESOURCE_NAME
, NULL JOB_TITLE
, DRA.REPAIR_TYPE_ID REPAIR_TYPE_ID
, RTYPE.NAME REPAIR_TYPE
, DRA.INSTANCE_ID INSTANCE_ID
, DRA.APPROVAL_REQUIRED_FLAG APPROVAL_REQUIRED_FLAG
, DRA.APPROVAL_STATUS APPROVAL_STATUS
, FND1.MEANING APPROVAL_STATUS_MEANING
, DRA.STATUS STATUS
, FND2.MEANING STATUS_MEANING
, DRA.DATE_CLOSED DATE_CLOSED
, DRA.PROMISE_DATE PROMISE_DATE
, DRA.ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY
, DRA.ATTRIBUTE1 ATTRIBUTE1
, DRA.ATTRIBUTE2 ATTRIBUTE2
, DRA.ATTRIBUTE3 ATTRIBUTE3
, DRA.ATTRIBUTE4 ATTRIBUTE4
, DRA.ATTRIBUTE5 ATTRIBUTE5
, DRA.ATTRIBUTE6 ATTRIBUTE6
, DRA.ATTRIBUTE7 ATTRIBUTE7
, DRA.ATTRIBUTE8 ATTRIBUTE8
, DRA.ATTRIBUTE9 ATTRIBUTE9
, DRA.ATTRIBUTE10 ATTRIBUTE10
, DRA.ATTRIBUTE11 ATTRIBUTE11
, DRA.ATTRIBUTE12 ATTRIBUTE12
, DRA.ATTRIBUTE13 ATTRIBUTE13
, DRA.ATTRIBUTE14 ATTRIBUTE14
, DRA.ATTRIBUTE15 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
, SR.INCIDENT_SEVERITY_ID SEVERITY_ID
, SEV.NAME SEVERITY
, SR.INCIDENT_URGENCY_ID URGENCY_ID
, URG.NAME URGENCY
, CITB.NAME INCIDENT_TYPE
, STAT.NAME INCIDENT_STATUS
, SR.INCIDENT_DATE INCIDENT_DATE
, DRA.SUPERCESSION_INV_ITEM_ID SUPERCESSION_PRODUCT_ID
, MSIS.CONCATENATED_SEGMENTS SUPERCESSION_PRODUCT
, MSIS.DESCRIPTION SUPERCESSION_PRODUCT_DESC
, SR.ACCOUNT_ID ACCOUNT_ID
, HCA.ACCOUNT_NUMBER
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
, HZ_CUST_ACCOUNTS HCA
, MTL_SYSTEM_ITEMS_VL MSIS
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(+)
AND SR.ACCOUNT_ID = HCA.CUST_ACCOUNT_ID (+)
AND DRA.SUPERCESSION_INV_ITEM_ID = MSIS.INVENTORY_ITEM_ID(+)
AND ITEM.ORGANIZATION_ID = NVL( MSIS.ORGANIZATION_ID
, ITEM.ORGANIZATION_ID )
AND DRA.REPAIR_MODE = 'WIP'
AND DRA.STATUS = 'O'
AND NVL ( STAT.STATUS_CODE
, 'OPEN' ) != 'CLOSED'

Columns