eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.CS_CHARGE_DETAILS_V

Source

View Text - Preformatted


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 estdtl.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'))

View Text - HTML Formatted

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 ESTDTL.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'))