VIEW: APPS.CSD_RO_SUMMARY_V

Source

View Text - Preformatted

SELECT dra.REPAIR_NUMBER, sr.INCIDENT_NUMBER , sr.Incident_id, drtvl.name REPAIR_TYPE_NAME, SR.STATUS SR_STATUS, cp.instance_number REFERENCE_NUMBER, dra.INVENTORY_ITEM_ID , items.concatenated_segments ITEM, items.description item_desc, dra.item_revision ITEM_REVISION, sr.Customer_id , decode(dra.customer_product_id, '',dra.serial_number,cp.serial_number) SERIAL_NUMBER, dra.CREATION_DATE , dra.DATE_CLOSED , dra.PROMISE_DATE , fndl2.meaning STATUS_MEANING, dra.REPAIR_LINE_ID , dra.QUANTITY , uom.unit_of_measure_tl, sr.account_number , dra.repair_type_id , dra.status , cp.location_id install_site_use_id, sr.account_number || '-' || sr.party_name account_detail , sr.party_name customer_name , sr.account_number customer_account , fndl1.meaning estimate_approved, dra.flow_status_id, cfb.flow_status_code, nvl(cftl.external_display_status, fnd.meaning) flow_status_meaning FROM csd_repair_types_vl drtvl, csi_item_instances cp, mtl_system_items_vl items, mtl_units_of_measure_vl uom, csd_repairs dra, fnd_lookups fndl1, fnd_lookups fndl2, csd_incidents_non_sec_v sr, csd_flow_statuses_tl cftl, csd_flow_statuses_b cfb, fnd_lookups fnd WHERE dra.incident_id = sr.incident_id and dra.repair_type_id = drtvl.repair_type_id and dra.CUSTOMER_PRODUCT_ID = cp.INSTANCE_ID(+) and dra.inventory_item_id = items.inventory_item_id and items.organization_id = cs_std.get_item_valdn_orgzn_id and dra.unit_of_measure = uom.uom_code and dra.approval_status = fndl1.lookup_code (+) and fndl1.lookup_type (+) = 'CSD_APPROVAL_STATUS' and dra.status = fndl2.lookup_code and fndl2.lookup_type = 'CSD_REPAIR_STATUS' and cfb.status_code = fnd.lookup_code and fnd.lookup_type = 'CSD_REPAIR_FLOW_STATUS' and dra.flow_status_id = cfb.flow_status_id and cfb.flow_status_id = cftl.flow_status_id and cftl.language = userenv('LANG')

View Text - HTML Formatted

SELECT DRA.REPAIR_NUMBER
, SR.INCIDENT_NUMBER
, SR.INCIDENT_ID
, DRTVL.NAME REPAIR_TYPE_NAME
, SR.STATUS SR_STATUS
, CP.INSTANCE_NUMBER REFERENCE_NUMBER
, DRA.INVENTORY_ITEM_ID
, ITEMS.CONCATENATED_SEGMENTS ITEM
, ITEMS.DESCRIPTION ITEM_DESC
, DRA.ITEM_REVISION ITEM_REVISION
, SR.CUSTOMER_ID
, DECODE(DRA.CUSTOMER_PRODUCT_ID
, ''
, DRA.SERIAL_NUMBER
, CP.SERIAL_NUMBER) SERIAL_NUMBER
, DRA.CREATION_DATE
, DRA.DATE_CLOSED
, DRA.PROMISE_DATE
, FNDL2.MEANING STATUS_MEANING
, DRA.REPAIR_LINE_ID
, DRA.QUANTITY
, UOM.UNIT_OF_MEASURE_TL
, SR.ACCOUNT_NUMBER
, DRA.REPAIR_TYPE_ID
, DRA.STATUS
, CP.LOCATION_ID INSTALL_SITE_USE_ID
, SR.ACCOUNT_NUMBER || '-' || SR.PARTY_NAME ACCOUNT_DETAIL
, SR.PARTY_NAME CUSTOMER_NAME
, SR.ACCOUNT_NUMBER CUSTOMER_ACCOUNT
, FNDL1.MEANING ESTIMATE_APPROVED
, DRA.FLOW_STATUS_ID
, CFB.FLOW_STATUS_CODE
, NVL(CFTL.EXTERNAL_DISPLAY_STATUS
, FND.MEANING) FLOW_STATUS_MEANING
FROM CSD_REPAIR_TYPES_VL DRTVL
, CSI_ITEM_INSTANCES CP
, MTL_SYSTEM_ITEMS_VL ITEMS
, MTL_UNITS_OF_MEASURE_VL UOM
, CSD_REPAIRS DRA
, FND_LOOKUPS FNDL1
, FND_LOOKUPS FNDL2
, CSD_INCIDENTS_NON_SEC_V SR
, CSD_FLOW_STATUSES_TL CFTL
, CSD_FLOW_STATUSES_B CFB
, FND_LOOKUPS FND
WHERE DRA.INCIDENT_ID = SR.INCIDENT_ID
AND DRA.REPAIR_TYPE_ID = DRTVL.REPAIR_TYPE_ID
AND DRA.CUSTOMER_PRODUCT_ID = CP.INSTANCE_ID(+)
AND DRA.INVENTORY_ITEM_ID = ITEMS.INVENTORY_ITEM_ID
AND ITEMS.ORGANIZATION_ID = CS_STD.GET_ITEM_VALDN_ORGZN_ID
AND DRA.UNIT_OF_MEASURE = UOM.UOM_CODE
AND DRA.APPROVAL_STATUS = FNDL1.LOOKUP_CODE (+)
AND FNDL1.LOOKUP_TYPE (+) = 'CSD_APPROVAL_STATUS'
AND DRA.STATUS = FNDL2.LOOKUP_CODE
AND FNDL2.LOOKUP_TYPE = 'CSD_REPAIR_STATUS'
AND CFB.STATUS_CODE = FND.LOOKUP_CODE
AND FND.LOOKUP_TYPE = 'CSD_REPAIR_FLOW_STATUS'
AND DRA.FLOW_STATUS_ID = CFB.FLOW_STATUS_ID
AND CFB.FLOW_STATUS_ID = CFTL.FLOW_STATUS_ID
AND CFTL.LANGUAGE = USERENV('LANG')