VIEW: APPS.CSD_REPAIR_ESTIMATE_LINES_V

Source

View Text - Preformatted

SELECT crel.rowid, cr.repair_line_id, cr.repair_number, cre.repair_estimate_id, crel.repair_estimate_line_id, ced.estimate_detail_id, cr.auto_process_rma, cr.repair_mode, cr.repair_type_id, cr.incident_id, ced.inventory_item_id, msi.concatenated_segments product, msi.description, crel.created_by, crel.creation_date, crel.last_updated_by, crel.last_update_date, crel.last_update_login, crel.object_version_number, ced.unit_of_measure_code, uom.unit_of_measure_tl, ced.quantity_required estimate_quantity, cr.quantity repair_quantity, ced.customer_product_id, ced.serial_number, cr.approval_required_flag, cr.approval_status, cr.status, cr.promise_date, crel.context, crel.attribute1, crel.attribute2, crel.attribute3, crel.attribute4, crel.attribute5, crel.attribute6, crel.attribute7, crel.attribute8, crel.attribute9, crel.attribute10, crel.attribute11, crel.attribute12, crel.attribute13, crel.attribute14, crel.attribute15, ced.price_list_header_id, qp.name price_list, stt.name transaction_type, sbt.billing_type, csl.meaning billing_type_meaning, cbtc.billing_category, ced.txn_billing_type_id, ced.order_header_id, ced.order_line_id, ced.return_reason_code, okh.contract_number, ced.contract_id , ced.invoice_to_org_id, ced.selling_price, ced.no_charge_flag, ced.business_process_id, msi.lot_control_code, msi.serial_number_control_code, ced.after_warranty_cost charge, decode(ced.after_warranty_cost,NULL,NULL,0,NULL, (ced.after_warranty_cost - (nvl(abs(ced.quantity_required),0) * nvl(crel.item_cost,0)))/(nvl( ced.after_warranty_cost,1))*100) profit_margin_percentage, decode(crel.item_cost,NULL,NULL,0,NULL, (ced.after_warranty_cost - (nvl(abs(ced.quantity_required),0) * nvl(crel.item_cost,0)))) profit_margin, crel.item_cost, cre.estimate_date, cre.estimate_status, ced.purchase_order_num po_number, ced.coverage_bill_rate_id coverage_rate_id, null coverage_type_id, crel.justification_notes, ced.currency_code, cia.incident_number, null rma_number, ced.transaction_type_id, ced.original_source_id, ced.original_source_code, ced.source_id, ced.source_code, ced.line_type_id, ced.item_revision, ced.line_category_code, ced.coverage_id, ced.coverage_txn_group_id, ced.organization_id, ced.ship_to_org_id, ced.interface_to_oe_flag, ced.installed_cp_return_by_date, crel.resource_id, decode(crel.resource_id, NULL, NULL, (select resource_code from bom_resources_v where resource_id = crel.resource_id and organization_id = cs_std.get_item_valdn_orgzn_id)) resource_code, nvl(crel.override_charge_flag, 'N'), crel.est_line_source_type_code est_line_source_type_code, fndl.meaning est_line_source_desc, crel.est_line_source_id1 est_line_source_id1, DECODE(est_line_source_type_code, 'SERVICE_CODE', csc.name, 'SOLUTION', kb.name, 'WIP', wip.wip_entity_name, null) est_line_source_name1, crel.est_line_source_id2 est_line_source_id2, null est_line_source_name2, crel.ro_service_code_id ro_service_code_id, ced.pricing_context, ced.pricing_attribute1, ced.pricing_attribute2, ced.pricing_attribute3, ced.pricing_attribute4, ced.pricing_attribute5, ced.pricing_attribute6, ced.pricing_attribute7, ced.pricing_attribute8, ced.pricing_attribute9, ced.pricing_attribute10, ced.pricing_attribute11, ced.pricing_attribute12, ced.pricing_attribute13, ced.pricing_attribute14, ced.pricing_attribute15, ced.pricing_attribute16, ced.pricing_attribute17, ced.pricing_attribute18, ced.pricing_attribute19, ced.pricing_attribute20, ced.pricing_attribute21, ced.pricing_attribute22, ced.pricing_attribute23, ced.pricing_attribute24, ced.pricing_attribute25, ced.pricing_attribute26, ced.pricing_attribute27, ced.pricing_attribute28, ced.pricing_attribute29, ced.pricing_attribute30, ced.pricing_attribute31, ced.pricing_attribute32, ced.pricing_attribute33, ced.pricing_attribute34, ced.pricing_attribute35, ced.pricing_attribute36, ced.pricing_attribute37, ced.pricing_attribute38, ced.pricing_attribute39, ced.pricing_attribute40, ced.pricing_attribute41, ced.pricing_attribute42, ced.pricing_attribute43, ced.pricing_attribute44, ced.pricing_attribute45, ced.pricing_attribute46, ced.pricing_attribute47, ced.pricing_attribute48, ced.pricing_attribute49, ced.pricing_attribute50, ced.pricing_attribute51, ced.pricing_attribute52, ced.pricing_attribute53, ced.pricing_attribute54, ced.pricing_attribute55, ced.pricing_attribute56, ced.pricing_attribute57, ced.pricing_attribute58, ced.pricing_attribute59, ced.pricing_attribute60, ced.pricing_attribute61, ced.pricing_attribute62, ced.pricing_attribute63, ced.pricing_attribute64, ced.pricing_attribute65, ced.pricing_attribute66, ced.pricing_attribute67, ced.pricing_attribute68, ced.pricing_attribute69, ced.pricing_attribute70, ced.pricing_attribute71, ced.pricing_attribute72, ced.pricing_attribute73, ced.pricing_attribute74, ced.pricing_attribute75, ced.pricing_attribute76, ced.pricing_attribute77, ced.pricing_attribute78, ced.pricing_attribute79, ced.pricing_attribute80, ced.pricing_attribute81, ced.pricing_attribute82, ced.pricing_attribute83, ced.pricing_attribute84, ced.pricing_attribute85, ced.pricing_attribute86, ced.pricing_attribute87, ced.pricing_attribute88, ced.pricing_attribute89, ced.pricing_attribute90, ced.pricing_attribute91, ced.pricing_attribute92, ced.pricing_attribute93, ced.pricing_attribute94, ced.pricing_attribute95, ced.pricing_attribute96, ced.pricing_attribute97, ced.pricing_attribute98, ced.pricing_attribute99, ced.pricing_attribute100, ced.contract_discount_amount, ced.contract_line_id contract_line_id FROM csd_repairs cr, cs_incidents_b_sec cia, cs_estimate_details ced, csd_repair_estimate cre, csd_repair_estimate_lines crel, mtl_system_items_vl msi, mtl_units_of_measure_vl uom, cs_transaction_types_vl stt, cs_txn_billing_types sbt , qp_list_headers_tl qp , okc_k_headers_b okh, csd_service_codes_vl csc, fnd_lookups fndl, cs_kb_sets_vl kb, cs_billing_type_categories cbtc, cs_lookups csl, wip_entities wip WHERE cr.incident_id = cia.incident_id and cr.repair_line_id = cre.repair_line_id and cre.repair_estimate_id = crel.repair_estimate_id and crel.estimate_detail_id = ced.estimate_detail_id and ced.original_source_code = 'DR' and ced.inventory_item_id = msi.inventory_item_id and msi.organization_id = cs_std.get_item_valdn_orgzn_id and ced.unit_of_measure_code = uom.uom_code and ced.price_list_header_id = qp.list_header_id(+) and qp.language(+) = userenv('LANG') and ced.contract_id = okh.id(+) and sbt.txn_billing_type_id = ced.txn_billing_type_id and sbt.transaction_type_id = stt.transaction_type_id and crel.est_line_source_type_code = fndl.lookup_code and fndl.lookup_type = 'CSD_SOURCE_TYPE' and crel.est_line_source_id1 = wip.wip_entity_id(+) and crel.est_line_source_id1 = csc.service_code_id(+) and crel.est_line_source_id1 =kb.set_id(+) and ced.charge_line_type = 'ESTIMATE' and csl.lookup_type = 'MTL_SERVICE_BILLABLE_FLAG' and csl.lookup_code = cbtc.billing_type and cbtc.billing_type = sbt.billing_type

View Text - HTML Formatted

SELECT CREL.ROWID
, CR.REPAIR_LINE_ID
, CR.REPAIR_NUMBER
, CRE.REPAIR_ESTIMATE_ID
, CREL.REPAIR_ESTIMATE_LINE_ID
, CED.ESTIMATE_DETAIL_ID
, CR.AUTO_PROCESS_RMA
, CR.REPAIR_MODE
, CR.REPAIR_TYPE_ID
, CR.INCIDENT_ID
, CED.INVENTORY_ITEM_ID
, MSI.CONCATENATED_SEGMENTS PRODUCT
, MSI.DESCRIPTION
, CREL.CREATED_BY
, CREL.CREATION_DATE
, CREL.LAST_UPDATED_BY
, CREL.LAST_UPDATE_DATE
, CREL.LAST_UPDATE_LOGIN
, CREL.OBJECT_VERSION_NUMBER
, CED.UNIT_OF_MEASURE_CODE
, UOM.UNIT_OF_MEASURE_TL
, CED.QUANTITY_REQUIRED ESTIMATE_QUANTITY
, CR.QUANTITY REPAIR_QUANTITY
, CED.CUSTOMER_PRODUCT_ID
, CED.SERIAL_NUMBER
, CR.APPROVAL_REQUIRED_FLAG
, CR.APPROVAL_STATUS
, CR.STATUS
, CR.PROMISE_DATE
, CREL.CONTEXT
, CREL.ATTRIBUTE1
, CREL.ATTRIBUTE2
, CREL.ATTRIBUTE3
, CREL.ATTRIBUTE4
, CREL.ATTRIBUTE5
, CREL.ATTRIBUTE6
, CREL.ATTRIBUTE7
, CREL.ATTRIBUTE8
, CREL.ATTRIBUTE9
, CREL.ATTRIBUTE10
, CREL.ATTRIBUTE11
, CREL.ATTRIBUTE12
, CREL.ATTRIBUTE13
, CREL.ATTRIBUTE14
, CREL.ATTRIBUTE15
, CED.PRICE_LIST_HEADER_ID
, QP.NAME PRICE_LIST
, STT.NAME TRANSACTION_TYPE
, SBT.BILLING_TYPE
, CSL.MEANING BILLING_TYPE_MEANING
, CBTC.BILLING_CATEGORY
, CED.TXN_BILLING_TYPE_ID
, CED.ORDER_HEADER_ID
, CED.ORDER_LINE_ID
, CED.RETURN_REASON_CODE
, OKH.CONTRACT_NUMBER
, CED.CONTRACT_ID
, CED.INVOICE_TO_ORG_ID
, CED.SELLING_PRICE
, CED.NO_CHARGE_FLAG
, CED.BUSINESS_PROCESS_ID
, MSI.LOT_CONTROL_CODE
, MSI.SERIAL_NUMBER_CONTROL_CODE
, CED.AFTER_WARRANTY_COST CHARGE
, DECODE(CED.AFTER_WARRANTY_COST
, NULL
, NULL
, 0
, NULL
, (CED.AFTER_WARRANTY_COST - (NVL(ABS(CED.QUANTITY_REQUIRED)
, 0) * NVL(CREL.ITEM_COST
, 0)))/(NVL( CED.AFTER_WARRANTY_COST
, 1))*100) PROFIT_MARGIN_PERCENTAGE
, DECODE(CREL.ITEM_COST
, NULL
, NULL
, 0
, NULL
, (CED.AFTER_WARRANTY_COST - (NVL(ABS(CED.QUANTITY_REQUIRED)
, 0) * NVL(CREL.ITEM_COST
, 0)))) PROFIT_MARGIN
, CREL.ITEM_COST
, CRE.ESTIMATE_DATE
, CRE.ESTIMATE_STATUS
, CED.PURCHASE_ORDER_NUM PO_NUMBER
, CED.COVERAGE_BILL_RATE_ID COVERAGE_RATE_ID
, NULL COVERAGE_TYPE_ID
, CREL.JUSTIFICATION_NOTES
, CED.CURRENCY_CODE
, CIA.INCIDENT_NUMBER
, NULL RMA_NUMBER
, CED.TRANSACTION_TYPE_ID
, CED.ORIGINAL_SOURCE_ID
, CED.ORIGINAL_SOURCE_CODE
, CED.SOURCE_ID
, CED.SOURCE_CODE
, CED.LINE_TYPE_ID
, CED.ITEM_REVISION
, CED.LINE_CATEGORY_CODE
, CED.COVERAGE_ID
, CED.COVERAGE_TXN_GROUP_ID
, CED.ORGANIZATION_ID
, CED.SHIP_TO_ORG_ID
, CED.INTERFACE_TO_OE_FLAG
, CED.INSTALLED_CP_RETURN_BY_DATE
, CREL.RESOURCE_ID
, DECODE(CREL.RESOURCE_ID
, NULL
, NULL
, (SELECT RESOURCE_CODE
FROM BOM_RESOURCES_V
WHERE RESOURCE_ID = CREL.RESOURCE_ID
AND ORGANIZATION_ID = CS_STD.GET_ITEM_VALDN_ORGZN_ID)) RESOURCE_CODE
, NVL(CREL.OVERRIDE_CHARGE_FLAG
, 'N')
, CREL.EST_LINE_SOURCE_TYPE_CODE EST_LINE_SOURCE_TYPE_CODE
, FNDL.MEANING EST_LINE_SOURCE_DESC
, CREL.EST_LINE_SOURCE_ID1 EST_LINE_SOURCE_ID1
, DECODE(EST_LINE_SOURCE_TYPE_CODE
, 'SERVICE_CODE'
, CSC.NAME
, 'SOLUTION'
, KB.NAME
, 'WIP'
, WIP.WIP_ENTITY_NAME
, NULL) EST_LINE_SOURCE_NAME1
, CREL.EST_LINE_SOURCE_ID2 EST_LINE_SOURCE_ID2
, NULL EST_LINE_SOURCE_NAME2
, CREL.RO_SERVICE_CODE_ID RO_SERVICE_CODE_ID
, CED.PRICING_CONTEXT
, CED.PRICING_ATTRIBUTE1
, CED.PRICING_ATTRIBUTE2
, CED.PRICING_ATTRIBUTE3
, CED.PRICING_ATTRIBUTE4
, CED.PRICING_ATTRIBUTE5
, CED.PRICING_ATTRIBUTE6
, CED.PRICING_ATTRIBUTE7
, CED.PRICING_ATTRIBUTE8
, CED.PRICING_ATTRIBUTE9
, CED.PRICING_ATTRIBUTE10
, CED.PRICING_ATTRIBUTE11
, CED.PRICING_ATTRIBUTE12
, CED.PRICING_ATTRIBUTE13
, CED.PRICING_ATTRIBUTE14
, CED.PRICING_ATTRIBUTE15
, CED.PRICING_ATTRIBUTE16
, CED.PRICING_ATTRIBUTE17
, CED.PRICING_ATTRIBUTE18
, CED.PRICING_ATTRIBUTE19
, CED.PRICING_ATTRIBUTE20
, CED.PRICING_ATTRIBUTE21
, CED.PRICING_ATTRIBUTE22
, CED.PRICING_ATTRIBUTE23
, CED.PRICING_ATTRIBUTE24
, CED.PRICING_ATTRIBUTE25
, CED.PRICING_ATTRIBUTE26
, CED.PRICING_ATTRIBUTE27
, CED.PRICING_ATTRIBUTE28
, CED.PRICING_ATTRIBUTE29
, CED.PRICING_ATTRIBUTE30
, CED.PRICING_ATTRIBUTE31
, CED.PRICING_ATTRIBUTE32
, CED.PRICING_ATTRIBUTE33
, CED.PRICING_ATTRIBUTE34
, CED.PRICING_ATTRIBUTE35
, CED.PRICING_ATTRIBUTE36
, CED.PRICING_ATTRIBUTE37
, CED.PRICING_ATTRIBUTE38
, CED.PRICING_ATTRIBUTE39
, CED.PRICING_ATTRIBUTE40
, CED.PRICING_ATTRIBUTE41
, CED.PRICING_ATTRIBUTE42
, CED.PRICING_ATTRIBUTE43
, CED.PRICING_ATTRIBUTE44
, CED.PRICING_ATTRIBUTE45
, CED.PRICING_ATTRIBUTE46
, CED.PRICING_ATTRIBUTE47
, CED.PRICING_ATTRIBUTE48
, CED.PRICING_ATTRIBUTE49
, CED.PRICING_ATTRIBUTE50
, CED.PRICING_ATTRIBUTE51
, CED.PRICING_ATTRIBUTE52
, CED.PRICING_ATTRIBUTE53
, CED.PRICING_ATTRIBUTE54
, CED.PRICING_ATTRIBUTE55
, CED.PRICING_ATTRIBUTE56
, CED.PRICING_ATTRIBUTE57
, CED.PRICING_ATTRIBUTE58
, CED.PRICING_ATTRIBUTE59
, CED.PRICING_ATTRIBUTE60
, CED.PRICING_ATTRIBUTE61
, CED.PRICING_ATTRIBUTE62
, CED.PRICING_ATTRIBUTE63
, CED.PRICING_ATTRIBUTE64
, CED.PRICING_ATTRIBUTE65
, CED.PRICING_ATTRIBUTE66
, CED.PRICING_ATTRIBUTE67
, CED.PRICING_ATTRIBUTE68
, CED.PRICING_ATTRIBUTE69
, CED.PRICING_ATTRIBUTE70
, CED.PRICING_ATTRIBUTE71
, CED.PRICING_ATTRIBUTE72
, CED.PRICING_ATTRIBUTE73
, CED.PRICING_ATTRIBUTE74
, CED.PRICING_ATTRIBUTE75
, CED.PRICING_ATTRIBUTE76
, CED.PRICING_ATTRIBUTE77
, CED.PRICING_ATTRIBUTE78
, CED.PRICING_ATTRIBUTE79
, CED.PRICING_ATTRIBUTE80
, CED.PRICING_ATTRIBUTE81
, CED.PRICING_ATTRIBUTE82
, CED.PRICING_ATTRIBUTE83
, CED.PRICING_ATTRIBUTE84
, CED.PRICING_ATTRIBUTE85
, CED.PRICING_ATTRIBUTE86
, CED.PRICING_ATTRIBUTE87
, CED.PRICING_ATTRIBUTE88
, CED.PRICING_ATTRIBUTE89
, CED.PRICING_ATTRIBUTE90
, CED.PRICING_ATTRIBUTE91
, CED.PRICING_ATTRIBUTE92
, CED.PRICING_ATTRIBUTE93
, CED.PRICING_ATTRIBUTE94
, CED.PRICING_ATTRIBUTE95
, CED.PRICING_ATTRIBUTE96
, CED.PRICING_ATTRIBUTE97
, CED.PRICING_ATTRIBUTE98
, CED.PRICING_ATTRIBUTE99
, CED.PRICING_ATTRIBUTE100
, CED.CONTRACT_DISCOUNT_AMOUNT
, CED.CONTRACT_LINE_ID CONTRACT_LINE_ID
FROM CSD_REPAIRS CR
, CS_INCIDENTS_B_SEC CIA
, CS_ESTIMATE_DETAILS CED
, CSD_REPAIR_ESTIMATE CRE
, CSD_REPAIR_ESTIMATE_LINES CREL
, MTL_SYSTEM_ITEMS_VL MSI
, MTL_UNITS_OF_MEASURE_VL UOM
, CS_TRANSACTION_TYPES_VL STT
, CS_TXN_BILLING_TYPES SBT
, QP_LIST_HEADERS_TL QP
, OKC_K_HEADERS_B OKH
, CSD_SERVICE_CODES_VL CSC
, FND_LOOKUPS FNDL
, CS_KB_SETS_VL KB
, CS_BILLING_TYPE_CATEGORIES CBTC
, CS_LOOKUPS CSL
, WIP_ENTITIES WIP
WHERE CR.INCIDENT_ID = CIA.INCIDENT_ID
AND CR.REPAIR_LINE_ID = CRE.REPAIR_LINE_ID
AND CRE.REPAIR_ESTIMATE_ID = CREL.REPAIR_ESTIMATE_ID
AND CREL.ESTIMATE_DETAIL_ID = CED.ESTIMATE_DETAIL_ID
AND CED.ORIGINAL_SOURCE_CODE = 'DR'
AND CED.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = CS_STD.GET_ITEM_VALDN_ORGZN_ID
AND CED.UNIT_OF_MEASURE_CODE = UOM.UOM_CODE
AND CED.PRICE_LIST_HEADER_ID = QP.LIST_HEADER_ID(+)
AND QP.LANGUAGE(+) = USERENV('LANG')
AND CED.CONTRACT_ID = OKH.ID(+)
AND SBT.TXN_BILLING_TYPE_ID = CED.TXN_BILLING_TYPE_ID
AND SBT.TRANSACTION_TYPE_ID = STT.TRANSACTION_TYPE_ID
AND CREL.EST_LINE_SOURCE_TYPE_CODE = FNDL.LOOKUP_CODE
AND FNDL.LOOKUP_TYPE = 'CSD_SOURCE_TYPE'
AND CREL.EST_LINE_SOURCE_ID1 = WIP.WIP_ENTITY_ID(+)
AND CREL.EST_LINE_SOURCE_ID1 = CSC.SERVICE_CODE_ID(+)
AND CREL.EST_LINE_SOURCE_ID1 =KB.SET_ID(+)
AND CED.CHARGE_LINE_TYPE = 'ESTIMATE'
AND CSL.LOOKUP_TYPE = 'MTL_SERVICE_BILLABLE_FLAG'
AND CSL.LOOKUP_CODE = CBTC.BILLING_TYPE
AND CBTC.BILLING_TYPE = SBT.BILLING_TYPE