ECC Lease Contracts, Contract Exception Details

Description
Categories: Enterprise Command Center
Columns: Contract Number, Unaccrued Revenue, Unbilled Rents, Accruing Yn, Uninterfaced Bills, Receivable Interface Error, Payables Interface Error, Lease Unaccounted, Receivables Unaccounted, Payable Unaccounted ...
Imported from Enterprise Command Center
Description: Lease Contract Exception Details
Dataset Key: okl_arcon_contract_revenue
Query Procedure: okl_ecc_arcon_pkg.ecc_acct_recon_data_load
Security Procedure:
select
x.*
from
(
SELECT * from
(SELECT acr.CONTRACT_NUMBER,
UNACCRUED_REVENUE ,
UNBILLED_RENTS,
ACCRUING_YN,
UNINTERFACED_BILLS,
RECEIVABLE_INTERFACE_ERROR,
PAYABLES_INTERFACE_ERROR,
LEASE_UNACCOUNTED,
RECEIVABLES_UNACCOUNTED,
PAYABLE_UNACCOUNTED,
UNAPPROVED_PAYABLE_TRX,
UNPREPARED_PAYABLE_TRX,
UNINTERFACED_PAYABLE_TRX,
ELIGIBLE_PAYABLES_IMPORT,
acr.contract_number ecc_spec_id,
acr.contract_status,
acr.org_id,
ledger_name,
ledger_currency,
unaccrued_revenue*acr.conversion_rate acctd_unaccrued_revenue,
unbilled_rents*acr.conversion_rate acctd_unbilled_rents,
decode(accruing_YN,NULL,NULL,acc.meaning) accruing_flag,
decode(RECEIVABLE_INTERFACE_ERROR,NULL,NULL,rie.meaning) recv_interface_trx_flag,
decode(UNAPPROVED_PAYABLE_TRX,NULL,NULL,uat.meaning) unapp_payable_trx_flag,
decode(UNPREPARED_PAYABLE_TRX,NULL,NULL,upt.meaning) unprep_payable_trx_flag,
decode(UNINTERFACED_PAYABLE_TRX,NULL,NULL,uit.meaning) unint_payable_trx_flag,
acc.language language_code,
unacctd_trans_flag,
unprocess_recv_flag,
unprocess_pay_flag,
uncleared_paydown
from okl_ecc_arcon_cont_revenue acr,
okl_k_headers           khr,
okc_k_headers_all_b hdr,
okl_product_parameters_all_v ppv,
fnd_lookup_values acc,
fnd_lookup_values  rie,
fnd_lookup_values uat,
fnd_lookup_values upt,
fnd_lookup_values uit
where processing_status_flag='N'
and acc.lookup_type='YES_NO'
and acc.lookup_code(+) = nvl(acr.ACCRUING_YN,'N')
and acc.view_application_id = 0
and nvl(acr.receivable_interface_error,'N') = rie.lookup_code(+)
and rie.lookup_type = 'YES_NO'
and acc.view_application_id = rie.view_application_id
and uat.lookup_type = 'YES_NO'
and nvl(acr.unapproved_payable_trx,'N')=uat.lookup_code(+)
and acc.view_application_id = uat.view_application_id
and upt.lookup_type = 'YES_NO'
and nvl(acr.unprepared_payable_trx,'N')=upt.lookup_code(+)
and acc.view_application_id = upt.view_application_id
and nvl(acr.uninterfaced_payable_trx,'N')=uit.lookup_code(+)
and uit.lookup_type='YES_NO'
and acc.view_application_id = uit.view_application_id
and acr.contract_number= hdr.contract_number
and hdr.id = khr.id
and khr.pdt_id = ppv.id
and ppv.deal_type in('LOAN','LOAN-REVOLVING','LEASEDF')
and acc.language in ('US')
and acc.language = rie.language
and rie.language = uat.language
and uat.language = upt.language
and upt.language = uit.language)
PIVOT( MAX(accruing_flag) AS accruing_flag,
       MAX(recv_interface_trx_flag) AS recv_interface_trx_flag,
       MAX(unapp_payable_trx_flag) AS unapp_payable_trx_flag,
       MAX(unprep_payable_trx_flag) AS unprep_payable_trx_flag,
       MAX(unint_payable_trx_flag) AS unint_payable_trx_flag
       for language_code 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