ECC Lease Contracts, Quotes

Description
Categories: Enterprise Command Center
Columns: Ecc Spec Id, Quote Number, Quote Type Code, Quote Status Code, Quote Reason Code, Quote Description, Comments, Currency Code, Quote Recipient, Quote Addl Recipient ...
Imported from Enterprise Command Center
Description: Lease Asset Quote Details
Dataset Key: okl-ast-qte-dts
Query Procedure: OKL_ECC_AM_UTIL_PVT.GET_ECC_DATA_LOAD_INFO
Security Procedure:
select
x.*
from
(
SELECT * FROM (SELECT
                                                 a.ecc_spec_id
                                                ,a.quote_number
                                                ,a.quote_type
                                                ,a.quote_type_code
                                                ,a.quote_status
                                                ,a.quote_status_code
                                                ,a.quote_reason
                                                ,a.quote_reason_code
                                                ,a.quote_description
                                                ,a.comments
                                                ,a.currency
                                                ,a.currency_code
                                                ,a.quote_recipient
                                                ,a.quote_addl_recipient
                                                ,a.quote_customer
                                                ,a.early_termination_yn
                                                ,a.partial_termination_yn
                                                ,a.quote_approved_yn
                                                ,a.quote_accepted_yn
                                                ,a.manual_acceptance_yn
                                                ,a.payment_received_yn
                                                ,a.approved_by_user
                                                ,a.accepted_by_user
                                                ,a.consolidated_quote_number
                                                ,a.quote_amount
                                                ,a.quote_bbnp_amount
                                                ,a.quote_bill_adj_amount
                                                ,a.quote_contract_oblig_amount
                                                ,a.quote_purchase_amount
                                                ,a.quote_discount_amount
                                                ,a.quote_antcp_bill_amount
                                                ,a.quote_other_amounts
                                                ,a.quote_gain_loss_amount
                                                ,a.quote_niv_amount
                                                ,a.quote_residual_amount
                                                ,a.quote_unbilled_rec_amount
                                                ,a.quote_financed_fee_amount
                                                ,a.quote_rollover_fee_amount
                                                ,a.quote_service_fee_amount
                                                ,a.quote_creation_date
                                                ,a.quote_effec_from_date
                                                ,a.quote_effec_to_date
                                                ,a.quote_approved_date
                                                ,a.quote_accepted_date
                                                ,a.period
                                                ,a.term_suspense_days
                                                ,a.creation_to_approval_days
                                                ,a.approval_to_acceptance_days
                                                ,a.acceptance_to_completion_days
                                                ,a.payment_received_date
                                                ,a.quote_last_update_date
                                                ,a.khr_last_update_date
                                                ,a.cons_qte_last_update_date
                                                ,a.asset_number
                                                ,a.asset_status_code
                                                ,a.asset_status
                                                ,a.asset_start_date
                                                ,a.asset_end_date
                                                ,a.quote_line_status
                                                ,a.quote_line_status_code
                                                ,a.asset_return_status
                                                ,a.asset_ret_status_code
                                                ,a.asset_units
                                                ,a.quoted_units
                                                ,a.split_asset_number
                                                ,a.return_due_date
                                                ,a.date_returned
                                                ,a.asset_bbnp_amount
                                                ,a.asset_bill_adj_amount
                                                ,a.asset_contract_oblig_amount
                                                ,a.asset_purchase_amount
                                                ,a.asset_discount_amount
                                                ,a.asset_antcp_bill_amount
                                                ,a.asset_other_amounts
                                                ,a.asset_niv_amount
                                                ,a.asset_residual_amount
                                                ,a.asset_unbilled_rec_amount
                                                ,a.asset_financed_fee_amount
                                                ,a.asset_rollover_fee_amount
                                                ,a.asset_service_fee_amount
                                                ,a.contract_number
                                                ,a.contract_status_code
                                                ,a.contract_status
                                                ,a.contract_start_date
                                                ,a.contract_end_date
                                                ,a.term_trx_err_flag
                                                ,a.new_contract_number
                                                ,a.new_contract_start_date
                                                ,a.term	,a.product
                                                ,a.quote_booking_yield
                                                ,a.quote_iir
                                                ,a.quote_pre_tax_irr
                                                ,a.quote_after_tax_irr
                                                ,a.renewal_cost
                                                ,a.termination_amount
                                                ,a.quote_id
                                                ,a.consolidated_quote_id
                                                ,a.quote_cust_acct_id
                                                ,a.contract_id
                                                ,a.quote_line_id
                                                ,a.asset_id
                                                ,a.qte_rma_id
                                                ,a.language	,a.org_id
                                                ,a.early_termination
                                                ,a.partial_termination
                                                ,a.fin_asset_type
                                                ,a.fin_serv_type
                                                ,a.contract_type
                                                    ,(a.asset_bbnp_amount+a.asset_contract_oblig_amount+
                                                      a.asset_bill_adj_amount+a.asset_purchase_amount+a.asset_discount_amount
                                                      +a.asset_other_amounts+
                                                      a.asset_financed_fee_amount+a.asset_rollover_fee_amount+
                                                      a.asset_service_fee_amount) asset_quote_amount,
                                                      to_char(a.quote_creation_date,'YYYY-MM') quote_creation_period,
                                                      to_char(quote_id) quote_id_string
 
 
                                     FROM   okl_ecc_am_quote_details_t a
 
 
                                     WHERE  language in ('US')
 
 
                                             )
                                     PIVOT  (
                                              MAX(quote_type) AS quote_type ,
                                              MAX(quote_status) AS quote_status,
                                              MAX(quote_reason) AS quote_reason ,
                                              MAX(currency) AS currency,
                                              MAX(quote_line_status) AS quote_line_status,
                                              MAX(contract_status) AS contract_status,
                                              MAX(asset_status) AS asset_status,
                                              MAX(early_termination) AS early_termination,
                                              MAX(partial_termination) AS partial_termination,
                                              MAX(asset_return_status) AS asset_return_status FOR language IN  ('US' "US"))
) x
where
2=2
Parameter Name SQL text Validation
Operating Unit
x.operating_unit_id in (select haouv.organization_id from hr_all_organization_units_vl haouv where haouv.name=:operating_unit)
LOV
Sold From Org.
x.sold_from_org_id in (select haouv.organization_id from hr_all_organization_units_vl haouv where haouv.name=:operating_unit)
LOV