APPS.CS_CHARGE_DETAILS_V
[Home] [Help]
| Product: | CS - Service |
|---|---|
| Description: | Charge Lines associated with Service request,depot repair and Field Service Report |
| Implementation/DBA Data: |
APPS.CS_CHARGE_DETAILS_V
|
SELECT ESTDTL.ESTIMATE_DETAIL_ID
, ESTDTL.LAST_UPDATE_DATE
, ESTDTL.LAST_UPDATED_BY
, ESTDTL.CREATION_DATE
, ESTDTL.CREATED_BY
, ESTDTL.LAST_UPDATE_LOGIN
, ESTDTL.ORIGINAL_SOURCE_CODE
, ESTDTL.ORIGINAL_SOURCE_ID
, LK1.MEANING
, ESTDTL.SOURCE_CODE
, ESTDTL.SOURCE_ID
, ESTDTL.INCIDENT_ID
, INC.CUSTOMER_ID PARTY_ID
, INC.ACCOUNT_ID
, ESTDTL.LINE_NUMBER
, ESTDTL.INVENTORY_ITEM_ID
, ESTDTL.SERIAL_NUMBER
, ESTDTL.QUANTITY_REQUIRED
, ESTDTL.UNIT_OF_MEASURE_CODE
, ESTDTL.SELLING_PRICE
, ESTDTL.AFTER_WARRANTY_COST /*
, ESTDTL.AFTER_WARRANTY_COST AFT_WARR_COST */
, ESTDTL.FUNC_CURR_AFT_WARR_COST
, ESTDTL.PRICING_CONTEXT
, ESTDTL.PRICING_ATTRIBUTE1
, ESTDTL.PRICING_ATTRIBUTE2
, ESTDTL.PRICING_ATTRIBUTE3
, ESTDTL.PRICING_ATTRIBUTE4
, ESTDTL.PRICING_ATTRIBUTE5
, ESTDTL.PRICING_ATTRIBUTE6
, ESTDTL.PRICING_ATTRIBUTE7
, ESTDTL.PRICING_ATTRIBUTE8
, ESTDTL.PRICING_ATTRIBUTE9
, ESTDTL.PRICING_ATTRIBUTE10
, ESTDTL.PRICING_ATTRIBUTE11
, ESTDTL.PRICING_ATTRIBUTE12
, ESTDTL.PRICING_ATTRIBUTE13
, ESTDTL.PRICING_ATTRIBUTE14
, ESTDTL.PRICING_ATTRIBUTE15
, ESTDTL.PRICING_ATTRIBUTE16
, ESTDTL.PRICING_ATTRIBUTE17
, ESTDTL.PRICING_ATTRIBUTE18
, ESTDTL.PRICING_ATTRIBUTE19
, ESTDTL.PRICING_ATTRIBUTE20
, ESTDTL.PRICING_ATTRIBUTE21
, ESTDTL.PRICING_ATTRIBUTE22
, ESTDTL.PRICING_ATTRIBUTE23
, ESTDTL.PRICING_ATTRIBUTE24
, ESTDTL.PRICING_ATTRIBUTE25
, ESTDTL.PRICING_ATTRIBUTE26
, ESTDTL.PRICING_ATTRIBUTE27
, ESTDTL.PRICING_ATTRIBUTE28
, ESTDTL.PRICING_ATTRIBUTE29
, ESTDTL.PRICING_ATTRIBUTE30
, ESTDTL.PRICING_ATTRIBUTE31
, ESTDTL.PRICING_ATTRIBUTE32
, ESTDTL.PRICING_ATTRIBUTE33
, ESTDTL.PRICING_ATTRIBUTE34
, ESTDTL.PRICING_ATTRIBUTE35
, ESTDTL.PRICING_ATTRIBUTE36
, ESTDTL.PRICING_ATTRIBUTE37
, ESTDTL.PRICING_ATTRIBUTE38
, ESTDTL.PRICING_ATTRIBUTE39
, ESTDTL.PRICING_ATTRIBUTE40
, ESTDTL.PRICING_ATTRIBUTE41
, ESTDTL.PRICING_ATTRIBUTE42
, ESTDTL.PRICING_ATTRIBUTE43
, ESTDTL.PRICING_ATTRIBUTE44
, ESTDTL.PRICING_ATTRIBUTE45
, ESTDTL.PRICING_ATTRIBUTE46
, ESTDTL.PRICING_ATTRIBUTE47
, ESTDTL.PRICING_ATTRIBUTE48
, ESTDTL.PRICING_ATTRIBUTE49
, ESTDTL.PRICING_ATTRIBUTE50
, ESTDTL.PRICING_ATTRIBUTE51
, ESTDTL.PRICING_ATTRIBUTE52
, ESTDTL.PRICING_ATTRIBUTE53
, ESTDTL.PRICING_ATTRIBUTE54
, ESTDTL.PRICING_ATTRIBUTE55
, ESTDTL.PRICING_ATTRIBUTE56
, ESTDTL.PRICING_ATTRIBUTE57
, ESTDTL.PRICING_ATTRIBUTE58
, ESTDTL.PRICING_ATTRIBUTE59
, ESTDTL.PRICING_ATTRIBUTE60
, ESTDTL.PRICING_ATTRIBUTE61
, ESTDTL.PRICING_ATTRIBUTE62
, ESTDTL.PRICING_ATTRIBUTE63
, ESTDTL.PRICING_ATTRIBUTE64
, ESTDTL.PRICING_ATTRIBUTE65
, ESTDTL.PRICING_ATTRIBUTE66
, ESTDTL.PRICING_ATTRIBUTE67
, ESTDTL.PRICING_ATTRIBUTE68
, ESTDTL.PRICING_ATTRIBUTE69
, ESTDTL.PRICING_ATTRIBUTE70
, ESTDTL.PRICING_ATTRIBUTE71
, ESTDTL.PRICING_ATTRIBUTE72
, ESTDTL.PRICING_ATTRIBUTE73
, ESTDTL.PRICING_ATTRIBUTE74
, ESTDTL.PRICING_ATTRIBUTE75
, ESTDTL.PRICING_ATTRIBUTE76
, ESTDTL.PRICING_ATTRIBUTE77
, ESTDTL.PRICING_ATTRIBUTE78
, ESTDTL.PRICING_ATTRIBUTE79
, ESTDTL.PRICING_ATTRIBUTE80
, ESTDTL.PRICING_ATTRIBUTE81
, ESTDTL.PRICING_ATTRIBUTE82
, ESTDTL.PRICING_ATTRIBUTE83
, ESTDTL.PRICING_ATTRIBUTE84
, ESTDTL.PRICING_ATTRIBUTE85
, ESTDTL.PRICING_ATTRIBUTE86
, ESTDTL.PRICING_ATTRIBUTE87
, ESTDTL.PRICING_ATTRIBUTE88
, ESTDTL.PRICING_ATTRIBUTE89
, ESTDTL.PRICING_ATTRIBUTE90
, ESTDTL.PRICING_ATTRIBUTE91
, ESTDTL.PRICING_ATTRIBUTE92
, ESTDTL.PRICING_ATTRIBUTE93
, ESTDTL.PRICING_ATTRIBUTE94
, ESTDTL.PRICING_ATTRIBUTE95
, ESTDTL.PRICING_ATTRIBUTE96
, ESTDTL.PRICING_ATTRIBUTE97
, ESTDTL.PRICING_ATTRIBUTE98
, ESTDTL.PRICING_ATTRIBUTE99
, ESTDTL.PRICING_ATTRIBUTE100
, ESTDTL.CONTEXT
, ESTDTL.ATTRIBUTE1
, ESTDTL.ATTRIBUTE2
, ESTDTL.ATTRIBUTE3
, ESTDTL.ATTRIBUTE4
, ESTDTL.ATTRIBUTE5
, ESTDTL.ATTRIBUTE6
, ESTDTL.ATTRIBUTE7
, ESTDTL.ATTRIBUTE8
, ESTDTL.ATTRIBUTE9
, ESTDTL.ATTRIBUTE10
, ESTDTL.ATTRIBUTE11
, ESTDTL.ATTRIBUTE12
, ESTDTL.ATTRIBUTE13
, ESTDTL.ATTRIBUTE14
, ESTDTL.ATTRIBUTE15
, INC.ORG_ID
, ESTDTL.COVERAGE_BILL_RATE_ID
, ESTDTL.EST_TAX_AMOUNT
, ESTDTL.TAX_CODE
, ESTDTL.BUSINESS_PROCESS_ID
, BP.NAME BUSINESS_PROCESS_NAME
, BP.ORDER_TYPE_ID
, TTT.NAME TTYPE
, TBT.BILLING_TYPE MATERIAL_BILLING_FLAG
, LK2.MEANING MATERIAL_BILLING_DESC
, ESTDTL.TXN_BILLING_TYPE_ID
, TTB.REVISION_FLAG
, ESTDTL.CUSTOMER_PRODUCT_ID
, ESTDTL.ORDER_HEADER_ID
, ESTDTL.ORDER_LINE_ID
, ESTDTL.INSTALLED_CP_RETURN_BY_DATE
, ESTDTL.NEW_CP_RETURN_BY_DATE
, ESTDTL.INTERFACE_TO_OE_FLAG
, ESTDTL.ROLLUP_FLAG
, ESTDTL.ADD_TO_ORDER_FLAG
, ESTDTL.RETURN_REASON_CODE
, ESTDTL.PRICE_LIST_HEADER_ID
, ESTDTL.INVOICE_TO_ORG_ID BILL_TO
, ESTDTL.SHIP_TO_ORG_ID SHIP_TO
, ESTDTL.LINE_TYPE_ID
, ESTDTL.LINE_CATEGORY_CODE
, ALTY.NAME LINE_TYPE_NAME
, ASOP.NAME PRICE_LIST_NAME
, ESTDTL.CURRENCY_CODE
, ESTDTL.CONVERSION_RATE
, ESTDTL.CONVERSION_TYPE_CODE
, ESTDTL.CONVERSION_RATE_DATE
, ASOH.ORDER_NUMBER ORDER_NUM
, ASOH.ORDERED_DATE ORDER_DATE
, ASOL.LINE_NUMBER ORDER_LINE_NUM
, SUBSTR(HL1.ADDRESS1||' '||HL1.ADDRESS2||' '||HL1.ADDRESS3||' '||HL1.ADDRESS4
, 1
, 220) BILL_TO_ADDRESS
, HL1.CITY BCITY
, HL1.STATE BSTATE
, HL1.COUNTRY BCOUNTRY
, HL1.POSTAL_CODE BPOSTAL_CODE
, SUBSTR(HL2.ADDRESS1||' '||HL2.ADDRESS2||' '||HL2.ADDRESS3||' '||HL2.ADDRESS4
, 1
, 220) SHIP_TO_ADDRESS
, HL2.CITY SCITY
, HL2.STATE SSTATE
, HL2.COUNTRY SCOUNTRY
, HL2.POSTAL_CODE SPOSTAL_CODE
, OKH.CONTRACT_NUMBER
, ESTDTL.COVERAGE_TXN_GROUP_ID
, OKC.NAME COVERAGE
, ESTDTL.COVERAGE_ID
, ESTDTL.CONTRACT_ID
, ESTDTL.EXCEPTION_COVERAGE_USED /*
, FD.MEANING RATE_TYPE */
, OKBR.RATE_NAME RATE_TYPE
, ESTDTL.PURCHASE_ORDER_NUM CUST_PO_NUMBER
, ESTDTL.OBJECT_VERSION_NUMBER
, ESTDTL.NO_CHARGE_FLAG
, ESTDTL.ORG_ID
, ESTDTL.ITEM_REVISION
, ESTDTL.TRANSACTION_INVENTORY_ORG
, ESTDTL.TRANSACTION_SUB_INVENTORY
, ESTDTL.CHARGE_LINE_TYPE
, ESTDTL.BILL_TO_CONTACT_ID
, ESTDTL.SHIP_TO_CONTACT_ID
, ESTDTL.INVOICE_TO_ACCOUNT_ID
, ESTDTL.SHIP_TO_ACCOUNT_ID
, ESTDTL.LIST_PRICE
, ESTDTL.CONTRACT_DISCOUNT_AMOUNT
, ESTDTL.BILL_TO_PARTY_ID
, ESTDTL.SHIP_TO_PARTY_ID
, ESTDTL.TRANSACTION_TYPE_ID
, ESTDTL.GENERATED_BY_BCA_ENGINE_FLAG
, ESTDTL.ACTIVITY_START_DATE_TIME
, ESTDTL.ACTIVITY_END_DATE_TIME
, ESTDTL.SUBMIT_RESTRICTION_MESSAGE
, ESTDTL.SUBMIT_ERROR_MESSAGE
, ESTDTL.SUBMIT_FROM_SYSTEM
, ESTDTL.LINE_SUBMITTED
, DECODE(ESTDTL.CHARGE_LINE_TYPE
, 'ESTIMATE'
, 'NEW'
, 'IN_PROGRESS'
, 'NEW'
, DECODE(NVL(ESTDTL.ORDER_LINE_ID
, -999)
, -999
, DECODE(NVL(ESTDTL.SUBMIT_ERROR_MESSAGE
, 'XXX')
, 'XXX'
, DECODE(NVL(ESTDTL.SUBMIT_RESTRICTION_MESSAGE
, 'XXX')
, 'XXX'
, 'NEW'
, 'FOR_REVIEW')
, 'IN_ERROR')
, 'SUBMITTED')) LINE_STATUS
, DECODE(ESTDTL.CHARGE_LINE_TYPE
, 'ESTIMATE'
, ESTDTL.AFTER_WARRANTY_COST
, 0) TOTAL_ESTIMATE
, DECODE(ESTDTL.CHARGE_LINE_TYPE
, 'ESTIMATE'
, 0
, DECODE(NVL(ESTDTL.ORDER_LINE_ID
, -999)
, -999
, ESTDTL.AFTER_WARRANTY_COST
, 0)) TOTAL_UNSUBMITTED
, DECODE(ESTDTL.CHARGE_LINE_TYPE
, 'ESTIMATE'
, 0
, DECODE(NVL(ESTDTL.ORDER_LINE_ID
, -999)
, -999
, 0
, ESTDTL.AFTER_WARRANTY_COST)) TOTAL_SUBMITTED
, LK3.MEANING LINE_STATUS_MEANING
, ESTDTL.CONTRACT_LINE_ID
, ESTDTL.RATE_TYPE_CODE
FROM /* ASO_I_PRICE_LISTS_V ASOP REPLACED */ QP_LIST_HEADERS_TL ASOP /*
, ASO_I_OE_ORDER_HEADERS_V ASOH REPLACED */
, OE_ORDER_HEADERS_ALL ASOH /*
, ASO_I_OE_ORDER_LINES_V ASOL REPLACED */
, OE_ORDER_LINES_ALL ASOL /*
, ASO_I_LINE_TYPES_V ALTY REPLACED */
, OE_TRANSACTION_TYPES_TL ALTY /*
, ASO_I_GL_CONV_TYPES_V ACON NEVER USED */
, HZ_PARTY_SITES HP1
, HZ_PARTY_SITES HP2
, HZ_LOCATIONS HL1
, HZ_LOCATIONS HL2 /*
, OKS_ENT_HEADERS_V OKH REPLACED */
, OKC_K_HEADERS_ALL_B OKH /*
, OKS_ENT_COVERAGES_V OKC */
, OKC_K_LINES_TL OKC
, OKS_ENT_BILL_RATES_V OKBR /*
, OKC_RULE_GROUPS_B RG
, OKC_RULES_B RL
, FND_LOOKUPS FD */ /*
, CS_CUSTOMER_PRODUCTS_ALL CP MOVED TO POST QUERY */ /*
, CS_INCIDENTS_ALL INC REPLACED */
, CS_INCIDENTS_ALL_B INC
, CS_LOOKUPS LK1
, CS_LOOKUPS LK2
, CS_TRANSACTION_TYPES_TL TTT
, CS_TRANSACTION_TYPES_B TTB
, CS_TXN_BILLING_TYPES TBT
, CS_BUSINESS_PROCESSES BP /*
, MTL_SYSTEM_ITEMS MTL NOT USED */ /*
, MTL_SYSTEM_ITEMS_KFV KFV MOVED TO POST QUERY */
, CS_ESTIMATE_DETAILS ESTDTL
, CS_TXN_BILLING_OETXN_ALL CTBOA
, CS_LOOKUPS LK3
WHERE ESTDTL.INCIDENT_ID = INC.INCIDENT_ID /*
AND ESTDTL.LINE_TYPE_ID = ALTY.TRANSACTION_TYPE_ID(+) */ AND ESTDTL.TXN_BILLING_TYPE_ID = CTBOA.TXN_BILLING_TYPE_ID (+) AND ESTDTL.ORG_ID = CTBOA.ORG_ID (+) AND CTBOA.LINE_TYPE_ID = ALTY.TRANSACTION_TYPE_ID (+)
AND ALTY.LANGUAGE(+) = USERENV('LANG')
AND ESTDTL.CONTRACT_ID = OKH.ID(+)
AND OKH.START_DATE(+) IS NOT NULL
AND OKH.END_DATE(+) IS NOT NULL /*
AND ESTDTL.COVERAGE_ID = OKC.ACTUAL_COVERAGE_ID(+) */
AND ESTDTL.COVERAGE_ID = OKC.ID(+)
AND OKC.LANGUAGE(+) = USERENV('LANG')
AND ESTDTL.COVERAGE_BILL_RATE_ID = OKBR.BILL_RATE_ID(+) /*
AND ESTDTL.COVERAGE_BILL_RATE_ID = RG.CLE_ID (+)
AND RG.ID = RL.RGP_ID (+)
AND RL.RULE_INFORMATION4 = FD.LOOKUP_CODE (+) */ /*
AND RL.RULE_INFORMATION_CATEGORY = 'RSL' */ /*
AND FD.LOOKUP_TYPE (+) ='OKS_BILLING_RATE' */
AND ESTDTL.BUSINESS_PROCESS_ID = BP.BUSINESS_PROCESS_ID
AND ESTDTL.TXN_BILLING_TYPE_ID = TBT.TXN_BILLING_TYPE_ID(+)
AND TBT.TRANSACTION_TYPE_ID = TTB.TRANSACTION_TYPE_ID(+)
AND TBT.TRANSACTION_TYPE_ID = TTT.TRANSACTION_TYPE_ID(+)
AND TTT.LANGUAGE (+) = USERENV('LANG') /*
AND ESTDTL.INVENTORY_ITEM_ID = MTL.INVENTORY_ITEM_ID(+) NOT USED */ /*
AND MTL.ORGANIZATION_ID (+) = CS_STD.GET_ITEM_VALDN_ORGZN_ID NOT USED */ /*
AND ESTDTL.CUSTOMER_PRODUCT_ID = CP.CUSTOMER_PRODUCT_ID(+) MOVED TO POST QUERY */ /*
AND CP.INVENTORY_ITEM_ID = KFV.INVENTORY_ITEM_ID(+) MOVED TO POST QUERY */ /*
AND (NVL(KFV.ORGANIZATION_ID
, CS_STD.GET_ITEM_VALDN_ORGZN_ID) = CS_STD.GET_ITEM_VALDN_ORGZN_ID) MOVED TO POST QUERY */
AND ESTDTL.PRICE_LIST_HEADER_ID = ASOP.LIST_HEADER_ID(+)
AND ASOP.LANGUAGE(+) = USERENV('LANG') /*
AND ESTDTL.CONVERSION_TYPE_CODE = ACON.CONVERSION_TYPE(+) NOT USED */
AND ESTDTL.INVOICE_TO_ORG_ID = HP1.PARTY_SITE_ID(+)
AND ESTDTL.SHIP_TO_ORG_ID = HP2.PARTY_SITE_ID(+)
AND HP1.LOCATION_ID = HL1.LOCATION_ID(+)
AND HP2.LOCATION_ID = HL2.LOCATION_ID(+)
AND ESTDTL.ORDER_HEADER_ID = ASOH.HEADER_ID(+)
AND ESTDTL.ORDER_LINE_ID = ASOL.LINE_ID(+)
AND ESTDTL.ORIGINAL_SOURCE_CODE = LK1.LOOKUP_CODE(+)
AND LK1.LOOKUP_TYPE(+) = 'ESTIMATE_SOURCE'
AND LK2.LOOKUP_TYPE(+) = 'MTL_SERVICE_BILLABLE_FLAG'
AND LK2.LOOKUP_CODE(+) = TBT.BILLING_TYPE
AND LK3.LOOKUP_TYPE(+) = 'CS_CHG_LINE_STATUS'
AND LK3.LOOKUP_CODE(+) = DECODE(ESTDTL.CHARGE_LINE_TYPE
, 'ESTIMATE'
, 'NEW'
, 'IN_PROGRESS'
, 'NEW'
, DECODE(NVL(ESTDTL.ORDER_LINE_ID
, -999.0)
, -999.0
, DECODE(NVL(ESTDTL.SUBMIT_ERROR_MESSAGE
, 'XXX')
, 'XXX'
, DECODE(NVL(ESTDTL.SUBMIT_RESTRICTION_MESSAGE
, 'XXX')
, 'XXX'
, 'NEW'
, 'FOR_REVIEW')
, 'IN_ERROR')
, 'SUBMITTED'))