VIEW: APPS.CSD_REPAIR_JOBS_V

Source

View Text - Preformatted

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'

View Text - HTML Formatted

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'