APPS.CSD_REPAIR_ACTUAL_LINES_V
[Home] [Help]
| Product: | CSD - Depot Repair |
|---|---|
| Description: | |
| Implementation/DBA Data: |
APPS.CSD_REPAIR_ACTUAL_LINES_V
|
SELECT CRAL.ROWID
, CRAL.REPAIR_ACTUAL_LINE_ID
, CRAL.OBJECT_VERSION_NUMBER
, CR.REPAIR_LINE_ID
, CRA.REPAIR_ACTUAL_ID
, CED.ESTIMATE_DETAIL_ID
, CRAL.CREATED_BY
, CRAL.CREATION_DATE
, CRAL.LAST_UPDATED_BY
, CRAL.LAST_UPDATE_DATE
, CRAL.LAST_UPDATE_LOGIN
, CR.REPAIR_NUMBER
, CRAL.OVERRIDE_CHARGE_FLAG
, CRAL.ACTUAL_SOURCE_CODE
, CRAL.ACTUAL_SOURCE_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
, 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
, CED.PRICE_LIST_HEADER_ID
, QP.NAME PRICE_LIST
, STT.NAME TRANSACTION_TYPE
, SBT.BILLING_TYPE
, CSL1.MEANING BILLING_TYPE_MEANING
, 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.SERIAL_NUMBER_CONTROL_CODE
, CED.AFTER_WARRANTY_COST CHARGE
, DECODE(CRAL.ITEM_COST
, NULL
, NULL
, 0
, NULL
, (CED.AFTER_WARRANTY_COST - (NVL(ABS(CED.QUANTITY_REQUIRED)
, 0) * NVL(CRAL.ITEM_COST
, 0)))) PROFIT_MARGIN
, DECODE(CRAL.ITEM_COST
, NULL
, NULL
, 0
, NULL
, (CED.AFTER_WARRANTY_COST - (NVL(ABS(CED.QUANTITY_REQUIRED)
, 0) * NVL(CRAL.ITEM_COST
, 0)))/(NVL(ABS(CED.QUANTITY_REQUIRED)
, 0)*NVL(CRAL.ITEM_COST
, 0)*100)) PROFIT_MARGIN_PERCENTAGE
, CRAL.ITEM_COST
, CED.PURCHASE_ORDER_NUM PO_NUMBER
, CED.COVERAGE_BILL_RATE_ID COVERAGE_RATE_ID
, NULL COVERAGE_TYPE_ID
, CRAL.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
, CRAL.RESOURCE_ID
, DECODE(CRAL.RESOURCE_ID
, NULL
, NULL
, (SELECT RESOURCE_CODE
FROM BOM_RESOURCES_V
WHERE RESOURCE_ID = CRAL.RESOURCE_ID
AND ORGANIZATION_ID = CS_STD.GET_ITEM_VALDN_ORGZN_ID)) RESOURCE_CODE
, MSI.LOT_CONTROL_CODE
, CED.CONTRACT_LINE_ID CONTRACT_LINE_ID
, OEH.ORDER_NUMBER
, OEL.LINE_NUMBER ORDER_LINE_NUMBER
, CBTC.BILLING_CATEGORY
, CSL.MEANING BILLING_CATEGORY_MEANING
, CRAL.LOT_NUMBER LOT_NUMBER
, FLV.MEANING ACTUAL_SOURCE_MEANING
, CRAL.ATTRIBUTE_CATEGORY
, CRAL.ATTRIBUTE1
, CRAL.ATTRIBUTE2
, CRAL.ATTRIBUTE3
, CRAL.ATTRIBUTE4
, CRAL.ATTRIBUTE5
, CRAL.ATTRIBUTE6
, CRAL.ATTRIBUTE7
, CRAL.ATTRIBUTE8
, CRAL.ATTRIBUTE9
, CRAL.ATTRIBUTE10
, CRAL.ATTRIBUTE11
, CRAL.ATTRIBUTE12
, CRAL.ATTRIBUTE13
, CRAL.ATTRIBUTE14
, CRAL.ATTRIBUTE15
, CRAL.LOCATOR_ID
, CRAL.LOC_SEGMENT1
, CRAL.LOC_SEGMENT2
, CRAL.LOC_SEGMENT3
, CRAL.LOC_SEGMENT4
, CRAL.LOC_SEGMENT5
, CRAL.LOC_SEGMENT6
, CRAL.LOC_SEGMENT7
, CRAL.LOC_SEGMENT8
, CRAL.LOC_SEGMENT9
, CRAL.LOC_SEGMENT10
, CRAL.LOC_SEGMENT11
, CRAL.LOC_SEGMENT12
, CRAL.LOC_SEGMENT13
, CRAL.LOC_SEGMENT14
, CRAL.LOC_SEGMENT15
, CRAL.LOC_SEGMENT16
, CRAL.LOC_SEGMENT17
, CRAL.LOC_SEGMENT18
, CRAL.LOC_SEGMENT19
, CRAL.LOC_SEGMENT20
, 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.INVOICE_TO_ACCOUNT_ID
, HCA.ACCOUNT_NUMBER
, HCA.ACCOUNT_NAME
, CED.BILL_TO_PARTY_ID
, HZ.PARTY_NUMBER
, HZ.PARTY_NAME
, SUBSTR(HL1.ADDRESS1||' '||HL1.ADDRESS2||' '||HL1.ADDRESS3||' '||HL1.ADDRESS4 ||' '|| HL1.CITY||' '||HL1.STATE||' '||HL1.COUNTRY||' '||HL1.POSTAL_CODE
, 1
, 440) BILL_TO_PARTY_SITE
, CRAL.WARRANTY_CLAIM_FLAG
, CRAL.WARRANTY_NUMBER
, CRAL.WARRANTY_STATUS_CODE
, FNDL2.MEANING WARRANTY_STATUS_MEANING
, CRAL.REPLACED_ITEM_ID
, MSI1.CONCATENATED_SEGMENTS
FROM CSD_REPAIRS CR
, CS_INCIDENTS_ALL_B CIA
, CS_ESTIMATE_DETAILS CED
, CSD_REPAIR_ACTUALS CRA
, CSD_REPAIR_ACTUAL_LINES CRAL
, OE_ORDER_HEADERS_ALL OEH
, OE_ORDER_LINES_ALL OEL
, 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
, CS_LOOKUPS CSL
, FND_LOOKUPS FLV
, CS_BILLING_TYPE_CATEGORIES CBTC
, CS_LOOKUPS CSL1
, HZ_CUST_ACCOUNTS HCA
, HZ_PARTIES HZ
, HZ_PARTY_SITES HP1
, HZ_LOCATIONS HL1
, FND_LOOKUPS FNDL2
, MTL_SYSTEM_ITEMS_VL MSI1
WHERE CR.INCIDENT_ID = CIA.INCIDENT_ID
AND CR.REPAIR_LINE_ID = CRA.REPAIR_LINE_ID
AND CRA.REPAIR_ACTUAL_ID = CRAL.REPAIR_ACTUAL_ID
AND CRAL.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 CED.ORDER_HEADER_ID = OEH.HEADER_ID(+)
AND CED.ORDER_LINE_ID = OEL.LINE_ID(+)
AND CSL.LOOKUP_TYPE = 'CS_BILLING_CATEGORY'
AND CSL.LOOKUP_CODE = CBTC.BILLING_CATEGORY
AND CSL1.LOOKUP_TYPE = 'MTL_SERVICE_BILLABLE_FLAG'
AND CSL1.LOOKUP_CODE = CBTC.BILLING_TYPE
AND SBT.BILLING_TYPE = CBTC.BILLING_TYPE
AND CSL.ENABLED_FLAG = 'Y'
AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(CSL.START_DATE_ACTIVE
, SYSDATE))
AND TRUNC(NVL(CSL.END_DATE_ACTIVE
, SYSDATE))
AND FLV.LOOKUP_CODE = CRAL.ACTUAL_SOURCE_CODE
AND FLV.LOOKUP_TYPE = 'CSD_SOURCE_TYPE'
AND FLV.ENABLED_FLAG = 'Y'
AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(FLV.START_DATE_ACTIVE
, SYSDATE))
AND TRUNC(NVL(FLV.END_DATE_ACTIVE
, SYSDATE))
AND HCA.CUST_ACCOUNT_ID (+) = CED.INVOICE_TO_ACCOUNT_ID
AND HZ.PARTY_ID (+) = CED.BILL_TO_PARTY_ID
AND HP1.PARTY_SITE_ID(+) = CED.INVOICE_TO_ORG_ID
AND HP1.LOCATION_ID = HL1.LOCATION_ID(+)
AND CRAL.WARRANTY_STATUS_CODE = FNDL2.LOOKUP_CODE(+)
AND FNDL2.LOOKUP_TYPE(+) = 'CSD_WARRANTY_STATUS'
AND CRAL.REPLACED_ITEM_ID = MSI1.INVENTORY_ITEM_ID(+)
AND MSI1.ORGANIZATION_ID(+) = CS_STD.GET_ITEM_VALDN_ORGZN_ID