VIEW: APPS.CSD_REPAIRS_V

Source

View Text - Preformatted

SELECT dra.rowid, sr.org_id, dra.repair_line_id,dra.request_id,dra.program_id, dra.program_application_id, dra.program_update_date, 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, dra.inventory_item_id, items.concatenated_segments, items.description item_desc, dra.unit_of_measure, uom.unit_of_measure_tl, dra.quantity, nvl(dra.quantity_in_wip,0), nvl(dra.quantity_rcvd, 0), nvl(dra.quantity_shipped, 0), dra.project_id, ppv.project_number, dra.task_id, ptv.task_number, dra.unit_number, dra.contract_line_id, okh.contract_number, okh.id, dra.customer_product_id, cp.instance_number, cp.lot_number, cps.name, decode(dra.customer_product_id, '',dra.item_revision,cp.inventory_revision), cp.system_id, cp.location_id, decode(dra.customer_product_id, '',dra.serial_number,cp.serial_number), dra.resource_id, null, null, dra.repair_type_id, drtvl.name, drtvl.repair_type_ref, drtvl.internal_order_flag, dra.instance_id, dra.approval_required_flag, dra.approval_status, fndl1.meaning, dra.status, fndl2.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, dra.status_reason_code, fndl3.meaning, fndl3.description, dra.auto_process_rma, dra.repair_mode, dra.object_version_number, dra.currency_code , dra.default_po_num, dra.original_source_header_id, dra.original_source_line_id, dra.split_from_rep_line_id, dra.split_flag, dra.price_list_header_id, qp.name, dra.supercession_inv_item_id, drtvl.business_process_id business_process_id, dra.FLOW_STATUS_ID FLOW_STATUS_ID, FS_B.FLOW_STATUS_CODE FLOW_STATUS_CODE, FS_LKUP.MEANING FLOW_STATUS, dra.WF_ITEM_TYPE WF_ITEM_TYPE, dra.WF_ITEM_KEY WF_ITEM_KEY, dra.owning_organization_id, dra.inventory_org_id, dra.problem_description, dra.ro_priority_code, p_lkup.meaning ro_priority_meaning, dra.resolve_by_date , fndes.lookup_code Escalation_Code , fndes.meaning Escalation_Meaning , fndes.description Escalation_Desc , fndrws.lookup_code Ro_Warranty_Status_Code , fndrws.meaning Ro_Warranty_Status_Meaning , fndiw.lookup_code In_Warranty_Code , fndiw.meaning In_Warranty_Meaning , fndiw.description In_Warranty_Desc , fndwv.lookup_code Warranty_Violation_Code , fndwv.meaning Warranty_Violation_Meaning , fndwv.description Warranty_Violation_Desc , fndrwk.lookup_code Rework_Code , fndrwk.meaning Rework_Meaning , fndrwk.description Rework_Desc , dra.bulletin_check_date , dra.repair_yield_quantity ,dra.attribute16 ,dra.attribute17 , dra.attribute18 , dra.attribute19 , dra.attribute20 , dra.attribute21 , dra.attribute22 ,dra.attribute23 , dra.attribute24 , dra.attribute25 , dra.attribute26 , dra.attribute27 , dra.attribute28 ,dra.attribute29 , dra.attribute30 from cs_incidents_b_sec sr, fnd_lookups fndl1, fnd_lookups fndl2, fnd_lookups fndl3, csd_repair_types_vl drtvl, csi_item_instances cp, csi_instance_statuses cps, mtl_system_items_vl items, mtl_units_of_measure_vl uom, csd_repairs dra, okc_k_lines_b okl, okc_k_headers_b okh, qp_list_headers_tl qp, pjm_projects_v ppv, pjm_tasks_v ptv, CSD_FLOW_STATUSES_B FS_B, CSD_FLOW_STATUSES_TL FS_TL, FND_LOOKUPS FS_LKUP, fnd_lookups p_lkup , fnd_lookups fndes , fnd_lookups fndiw , fnd_lookups fndwv , fnd_lookups fndrwk , fnd_lookups fndrws WHERE dra.repair_type_id = drtvl.repair_type_id and dra.incident_id = sr.incident_id and dra.CUSTOMER_PRODUCT_ID = cp.INSTANCE_ID(+) and cp.instance_status_id = cps.instance_status_id(+) 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 dra.status_reason_code = fndl3.lookup_code (+) and fndl3.lookup_type (+) = 'CSD_REASON' 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 okl.id(+) = dra.contract_line_id and okh.id(+) = okl.chr_id and qp.language(+)=userenv('LANG') and dra.price_list_header_id = qp.list_header_id(+) AND dra.project_id = ppv.project_id(+) and dra.task_id = ptv.task_id(+) and FS_B.FLOW_STATUS_ID = dra.FLOW_STATUS_ID AND FS_TL.FLOW_STATUS_ID = FS_B.FLOW_STATUS_ID AND FS_TL.LANGUAGE = USERENV('LANG') AND FS_LKUP.LOOKUP_TYPE = 'CSD_REPAIR_FLOW_STATUS' AND FS_LKUP.LOOKUP_CODE = FS_B.FLOW_STATUS_CODE AND dra.ro_priority_code = p_lkup.lookup_code(+) AND p_lkup.lookup_type(+) = 'CSD_RO_PRIORITY' AND dra.escalation_code = fndes.lookup_code (+) AND fndes.lookup_type (+) = 'CSD_ESCALATION_CODES' AND dra.ro_warranty_status_code = fndrws.lookup_code (+) AND fndrws.lookup_type (+) = 'CSD_RO_WARRANTY_STATUS' AND dra.in_warranty_code = fndiw.lookup_code (+) AND fndiw.lookup_type (+) = 'CSD_YES_NO_VALUE_SET' AND dra.warranty_violation_code = fndwv.lookup_code (+) AND fndwv.lookup_type (+) = 'CSD_WARRANTY_VIOLATION' AND dra.rework_code = fndrwk.lookup_code (+) AND fndrwk.lookup_type (+) = 'CSD_REWORK'

View Text - HTML Formatted

SELECT DRA.ROWID
, SR.ORG_ID
, DRA.REPAIR_LINE_ID
, DRA.REQUEST_ID
, DRA.PROGRAM_ID
, DRA.PROGRAM_APPLICATION_ID
, DRA.PROGRAM_UPDATE_DATE
, 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
, DRA.INVENTORY_ITEM_ID
, ITEMS.CONCATENATED_SEGMENTS
, ITEMS.DESCRIPTION ITEM_DESC
, DRA.UNIT_OF_MEASURE
, UOM.UNIT_OF_MEASURE_TL
, DRA.QUANTITY
, NVL(DRA.QUANTITY_IN_WIP
, 0)
, NVL(DRA.QUANTITY_RCVD
, 0)
, NVL(DRA.QUANTITY_SHIPPED
, 0)
, DRA.PROJECT_ID
, PPV.PROJECT_NUMBER
, DRA.TASK_ID
, PTV.TASK_NUMBER
, DRA.UNIT_NUMBER
, DRA.CONTRACT_LINE_ID
, OKH.CONTRACT_NUMBER
, OKH.ID
, DRA.CUSTOMER_PRODUCT_ID
, CP.INSTANCE_NUMBER
, CP.LOT_NUMBER
, CPS.NAME
, DECODE(DRA.CUSTOMER_PRODUCT_ID
, ''
, DRA.ITEM_REVISION
, CP.INVENTORY_REVISION)
, CP.SYSTEM_ID
, CP.LOCATION_ID
, DECODE(DRA.CUSTOMER_PRODUCT_ID
, ''
, DRA.SERIAL_NUMBER
, CP.SERIAL_NUMBER)
, DRA.RESOURCE_ID
, NULL
, NULL
, DRA.REPAIR_TYPE_ID
, DRTVL.NAME
, DRTVL.REPAIR_TYPE_REF
, DRTVL.INTERNAL_ORDER_FLAG
, DRA.INSTANCE_ID
, DRA.APPROVAL_REQUIRED_FLAG
, DRA.APPROVAL_STATUS
, FNDL1.MEANING
, DRA.STATUS
, FNDL2.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
, DRA.STATUS_REASON_CODE
, FNDL3.MEANING
, FNDL3.DESCRIPTION
, DRA.AUTO_PROCESS_RMA
, DRA.REPAIR_MODE
, DRA.OBJECT_VERSION_NUMBER
, DRA.CURRENCY_CODE
, DRA.DEFAULT_PO_NUM
, DRA.ORIGINAL_SOURCE_HEADER_ID
, DRA.ORIGINAL_SOURCE_LINE_ID
, DRA.SPLIT_FROM_REP_LINE_ID
, DRA.SPLIT_FLAG
, DRA.PRICE_LIST_HEADER_ID
, QP.NAME
, DRA.SUPERCESSION_INV_ITEM_ID
, DRTVL.BUSINESS_PROCESS_ID BUSINESS_PROCESS_ID
, DRA.FLOW_STATUS_ID FLOW_STATUS_ID
, FS_B.FLOW_STATUS_CODE FLOW_STATUS_CODE
, FS_LKUP.MEANING FLOW_STATUS
, DRA.WF_ITEM_TYPE WF_ITEM_TYPE
, DRA.WF_ITEM_KEY WF_ITEM_KEY
, DRA.OWNING_ORGANIZATION_ID
, DRA.INVENTORY_ORG_ID
, DRA.PROBLEM_DESCRIPTION
, DRA.RO_PRIORITY_CODE
, P_LKUP.MEANING RO_PRIORITY_MEANING
, DRA.RESOLVE_BY_DATE
, FNDES.LOOKUP_CODE ESCALATION_CODE
, FNDES.MEANING ESCALATION_MEANING
, FNDES.DESCRIPTION ESCALATION_DESC
, FNDRWS.LOOKUP_CODE RO_WARRANTY_STATUS_CODE
, FNDRWS.MEANING RO_WARRANTY_STATUS_MEANING
, FNDIW.LOOKUP_CODE IN_WARRANTY_CODE
, FNDIW.MEANING IN_WARRANTY_MEANING
, FNDIW.DESCRIPTION IN_WARRANTY_DESC
, FNDWV.LOOKUP_CODE WARRANTY_VIOLATION_CODE
, FNDWV.MEANING WARRANTY_VIOLATION_MEANING
, FNDWV.DESCRIPTION WARRANTY_VIOLATION_DESC
, FNDRWK.LOOKUP_CODE REWORK_CODE
, FNDRWK.MEANING REWORK_MEANING
, FNDRWK.DESCRIPTION REWORK_DESC
, DRA.BULLETIN_CHECK_DATE
, DRA.REPAIR_YIELD_QUANTITY
, DRA.ATTRIBUTE16
, DRA.ATTRIBUTE17
, DRA.ATTRIBUTE18
, DRA.ATTRIBUTE19
, DRA.ATTRIBUTE20
, DRA.ATTRIBUTE21
, DRA.ATTRIBUTE22
, DRA.ATTRIBUTE23
, DRA.ATTRIBUTE24
, DRA.ATTRIBUTE25
, DRA.ATTRIBUTE26
, DRA.ATTRIBUTE27
, DRA.ATTRIBUTE28
, DRA.ATTRIBUTE29
, DRA.ATTRIBUTE30
FROM CS_INCIDENTS_B_SEC SR
, FND_LOOKUPS FNDL1
, FND_LOOKUPS FNDL2
, FND_LOOKUPS FNDL3
, CSD_REPAIR_TYPES_VL DRTVL
, CSI_ITEM_INSTANCES CP
, CSI_INSTANCE_STATUSES CPS
, MTL_SYSTEM_ITEMS_VL ITEMS
, MTL_UNITS_OF_MEASURE_VL UOM
, CSD_REPAIRS DRA
, OKC_K_LINES_B OKL
, OKC_K_HEADERS_B OKH
, QP_LIST_HEADERS_TL QP
, PJM_PROJECTS_V PPV
, PJM_TASKS_V PTV
, CSD_FLOW_STATUSES_B FS_B
, CSD_FLOW_STATUSES_TL FS_TL
, FND_LOOKUPS FS_LKUP
, FND_LOOKUPS P_LKUP
, FND_LOOKUPS FNDES
, FND_LOOKUPS FNDIW
, FND_LOOKUPS FNDWV
, FND_LOOKUPS FNDRWK
, FND_LOOKUPS FNDRWS
WHERE DRA.REPAIR_TYPE_ID = DRTVL.REPAIR_TYPE_ID
AND DRA.INCIDENT_ID = SR.INCIDENT_ID
AND DRA.CUSTOMER_PRODUCT_ID = CP.INSTANCE_ID(+)
AND CP.INSTANCE_STATUS_ID = CPS.INSTANCE_STATUS_ID(+)
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 DRA.STATUS_REASON_CODE = FNDL3.LOOKUP_CODE (+)
AND FNDL3.LOOKUP_TYPE (+) = 'CSD_REASON'
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 OKL.ID(+) = DRA.CONTRACT_LINE_ID
AND OKH.ID(+) = OKL.CHR_ID
AND QP.LANGUAGE(+)=USERENV('LANG')
AND DRA.PRICE_LIST_HEADER_ID = QP.LIST_HEADER_ID(+)
AND DRA.PROJECT_ID = PPV.PROJECT_ID(+)
AND DRA.TASK_ID = PTV.TASK_ID(+)
AND FS_B.FLOW_STATUS_ID = DRA.FLOW_STATUS_ID
AND FS_TL.FLOW_STATUS_ID = FS_B.FLOW_STATUS_ID
AND FS_TL.LANGUAGE = USERENV('LANG')
AND FS_LKUP.LOOKUP_TYPE = 'CSD_REPAIR_FLOW_STATUS'
AND FS_LKUP.LOOKUP_CODE = FS_B.FLOW_STATUS_CODE
AND DRA.RO_PRIORITY_CODE = P_LKUP.LOOKUP_CODE(+)
AND P_LKUP.LOOKUP_TYPE(+) = 'CSD_RO_PRIORITY'
AND DRA.ESCALATION_CODE = FNDES.LOOKUP_CODE (+)
AND FNDES.LOOKUP_TYPE (+) = 'CSD_ESCALATION_CODES'
AND DRA.RO_WARRANTY_STATUS_CODE = FNDRWS.LOOKUP_CODE (+)
AND FNDRWS.LOOKUP_TYPE (+) = 'CSD_RO_WARRANTY_STATUS'
AND DRA.IN_WARRANTY_CODE = FNDIW.LOOKUP_CODE (+)
AND FNDIW.LOOKUP_TYPE (+) = 'CSD_YES_NO_VALUE_SET'
AND DRA.WARRANTY_VIOLATION_CODE = FNDWV.LOOKUP_CODE (+)
AND FNDWV.LOOKUP_TYPE (+) = 'CSD_WARRANTY_VIOLATION'
AND DRA.REWORK_CODE = FNDRWK.LOOKUP_CODE (+)
AND FNDRWK.LOOKUP_TYPE (+) = 'CSD_REWORK'