ECC Lease Contracts, Contracts

Description
Categories: Enterprise Command Center
Imported from Enterprise Command Center
Description: Lease Asset Contract Details
Dataset Key: okl-ast-ctr-dtls
Query Procedure: OKL_ECC_AM_UTIL_PVT.GET_ECC_DATA_LOAD_INFO
Security Procedure: okl_ecc_datasecurity_pkg_pub.GetFilterAttributeValues
Run ECC Lease Contracts, Contracts and other Oracle EBS reports with Blitz Report™ on our demo environment
select
x.*
from
(
SELECT * FROM (SELECT  a.ecc_spec_id
                             ,a.id		,a.contract_number
                             ,a.contract_description	,a.customer_name
                             ,a.cust_account_number	,a.chr_sts_code
                             ,a.chr_sts_meaning		,a.chr_start_date
                             ,a.chr_end_date		,a.khr_term_duration
                             ,a.khr_pdt_id		,a.product_name
                             ,a.book_class		,a.tax_owner
                             ,a.interest_calculation_basis
                             ,a.interest_calculation_meaning inst_basis
                             ,a.revenue_recognition_method
                             ,a.revenue_recognition_meaning revenue_meaning
                             ,a.accrual_status		,a.expected_delivery_date
                             ,a.legacy_number  		,a.investor_assignable
                             ,a.investor_assigned_yn
                             ,a.evergreen_eligible
                             ,a.early_purchase_option
                             ,a.eot_purchase_option
                             ,a.automatic_purchase_option
                             ,a.bill_to_site_name
                             ,a.pre_tax_irr   		,a.after_tax_irr
                             ,a.implicit_interest_rate
                             ,a.booking_yield
                             ,a.total_financed_amount
                             ,a.total_residual_amount
                             ,a.expiration_error_yn
                             ,a.evg_conversion_err_yn
                             ,a.currency_code		,a.org_id
                             ,a.operating_unit
                             ,a.mla_number     	,a.purchase_order_number
                             ,a.asset_line_id
                             ,a.dnz_chr_id
                             ,a.asset_number
                             ,a.asset_description
                             ,a.line_currency_code
                             ,a.item
                             ,a.units
                             ,a.unit_cost
                             ,a.total_cost
                             ,a.financed_cost
                             ,a.subsidized_cost
                             ,a.manufacturer_name
                             ,a.model_number
                             ,a.year_of_manufacture
                             ,a.install_site
                             ,a.fa_location
                             ,a.asset_key_name
                             ,a.residual_value
                             ,a.residual_percentage
                             ,a.guranteed_residual
                             ,a.line_iir
                             ,a.book_name
                             ,a.depreciable_cost
                             ,a.depreciation_category_name
                             ,a.salvage_value
                             ,a.asset_niv
                             ,a.life_in_months
                             ,a.in_service_date
                             ,a.depreciation_method
                             ,a.depreciation_rate
                             ,a.next_rent_amount
                             ,a.next_rent_date
                             ,a.down_payment_amount
                             ,a.tradein_amount
                             ,a.capitalized_fee_amount
                             ,a.subsidy_amount
                             ,a.asset_bill_to_site_name
                             ,a.asset_vendor_name
                             ,a.asset_status
                             ,a.asset_type
                             ,a.start_date
                             ,a.end_date
                             ,a.end_date_period
                             ,a.quote_effective_date
                             ,a.quote_exists_yn
                             ,a.accepted_quote_exists_yn
                             ,a.quote_type
                             ,a.quote_type_meaning
                             ,a.asset_return_status
                             ,a.asset_ret_status_code
                             ,a.return_due_date
                             ,a.date_returned
                             ,a.inventory_item_id
                             ,a.inventory_item_number
                             ,a.inv_item_description
                             ,a.remarketer
                             ,a.asset_condition
                             ,a.asset_condition_code
                             ,a.inspector
                             ,a.damage_type
                             ,a.condition_type
                             ,a.condition_type_code
                             ,a.order_number
                             ,a.order_date
                             ,a.order_status
                             ,a.order_status_code
                             ,a.order_line_number
                             ,a.line_number_date
                             ,a.order_line_status
                             ,a.order_line_status_code
                             ,a.remaketing_customer
                             ,a.remarketing_amount
                             ,a.returns_past_due_flag
                             ,a.term_not_return_flag
                             ,a.expire_type
                             ,a.overdue_returns_flag
                             ,a.overdue_returns_x_axis
                             ,a.overdue_returns_y_axis
                             ,a.language
                             ,a.expire_type_meaning
                             ,a.book_class_meaning
                             ,a.tax_owner_meaning
                             ,a.accrual_status_meaning
                             ,a.evergreen_eligible_meaning
                             ,a.early_pur_opt_meaning
                             ,a.eot_pur_opt_meaning
                             ,a.investor_assigned_meaning
                             ,a.asset_status_code
                             ,a.quoted_asset_quantity
                             ,a.fin_asset_type
                             ,a.fin_serv_type
                             ,a.contract_type
                             ,a.asset_type_code
                             ,a.off_lease_days
                             ,a.language lang_code  ,gld.name ledger_name
                                       ,to_char(a.asset_line_id) asset_id
                                       ,to_char(a.end_date,'YYYY-MM') period
                                       ,decode(a.off_lease_days,-99,'N','Y') off_lease_flag
				       , okch_dfv.*
 				       , okcl_dfv.*
                                    FROM  okl_ecc_am_cntr_details_t a,
                                          gl_ledgers gld,hr_operating_units ou,
                                          okl_k_lines b ,(select ROW_ID "'KHR_ROW_ID'",CONTEXT_VALUE "'KHR_CONTEXT_VALUE'",LEASE_CREDIT_RATING "'KHR_LEASE_CREDIT_RATING'",LEASE_SERVICE_MANAGER "'KHR_LEASE_SERVICE_MANAGER'",FND_ECC_DFF_UTIL.get_vset_values('OKL_K_HEADERS_DF','LESSEE_SALES_REGION',LESSEE_SALES_REGION, NULL ,NULL,540) "'KHR_LESSEE_SALES_REGION'",CONCATENATED_SEGMENTS "'KHR_CONCATENATED_SEGMENTS'",LESSEE_SALES_REGION "'KHR_LESSEE_SALES_REGION_EDOC'" from OKL_K_HEADERS_DFV) okch_dfv
                                          ,(select ROW_ID "'KLE_ROW_ID'",CONTEXT_VALUE "'KLE_CONTEXT_VALUE'",GROSS_POWER "'KLE_GROSS_POWER'",HEAPED_CAPACITY "'KLE_HEAPED_CAPACITY'",MAXIMUM_PAYLOAD "'KLE_MAXIMUM_PAYLOAD'",MAXIMUM_GROSS_WEIGHT "'KLE_MAXIMUM_GROSS_WEIGHT'",CPU "'KLE_CPU'",MEMORY "'KLE_MEMORY'",HARD_DRIVE "'KLE_HARD_DRIVE'",OPERATING__SYSTEM "'KLE_OPERATING__SYSTEM'",NET_POWER "'KLE_NET_POWER'",DIG_DEPTH "'KLE_DIG_DEPTH'",OPERATING_WEIGHT "'KLE_OPERATING_WEIGHT'",LIFT_CAPACITY "'KLE_LIFT_CAPACITY'",DUMP_HEIGHT "'KLE_DUMP_HEIGHT'",CONCATENATED_SEGMENTS "'KLE_CONCATENATED_SEGMENTS'" from OKL_K_LINES_DFV) okcl_dfv
                                    WHERE a.asset_line_id = b.id and
                                          gld.ledger_id = ou.set_of_books_id and
                                          ou.organization_id = a.org_id and
					  a.header_rowid = okch_dfv."'KHR_ROW_ID'"(+) and  b.rowid = okcl_dfv."'KLE_ROW_ID'"(+) and
                                          language in ('US'))
                                    PIVOT (    MAX(CONTRACT_DESCRIPTION) AS CONTRACT_DESCRIPTION ,
                                               MAX(CHR_STS_MEANING) AS CHR_STS_MEANING,
                                               MAX(ASSET_DESCRIPTION) AS ASSET_DESCRIPTION ,
                                               MAX(ITEM) AS ITEM,
                                               MAX(ASSET_STATUS) AS ASSET_STATUS,
                                               MAX(ASSET_TYPE) AS ASSET_TYPE,
                                               MAX(QUOTE_TYPE_MEANING) AS QUOTE_TYPE_MEANING,
                                               MAX(DAMAGE_TYPE) AS DAMAGE_TYPE,
                                               MAX(ASSET_CONDITION) AS ASSET_CONDITION,
                                               MAX(EXPIRE_TYPE_MEANING) AS EXPIRE_TYPE_MEANING,
                                               MAX(ASSET_RETURN_STATUS) AS ASSET_RETURN_STATUS,
 				   	       MAX(INST_BASIS) AS INST_BASIS,
                                               MAX(BOOK_CLASS_MEANING) AS  BOOK_CLASS_MEANING,
                                               MAX(TAX_OWNER_MEANING) AS  TAX_OWNER_MEANING,
                                               MAX(ACCRUAL_STATUS_MEANING) AS ACCRUAL_STATUS_MEANING,
                                               MAX(EVERGREEN_ELIGIBLE_MEANING) AS EVERGREEN_ELIGIBLE_MEANING,
                                               MAX(EARLY_PUR_OPT_MEANING) AS EARLY_PUR_OPT_MEANING,
                                               MAX(EOT_PUR_OPT_MEANING) AS EOT_PUR_OPT_MEANING,
                                               MAX(INVESTOR_ASSIGNED_MEANING) AS INVESTOR_ASSIGNED_MEANING,
                                               MAX(REVENUE_MEANING) AS REVENUE_MEANING,
                                               MAX(LANG_CODE) AS LANG_CODE
                        FOR LANGUAGE IN ('US' "US"))
) x
where
2=2
Parameter Name SQL text Validation
Operating Unit
x.org_id in (select haouv.organization_id from hr_all_organization_units_vl haouv where haouv.name=:operating_unit)
LOV