ECC Lease Contracts, Contract Exception Details, SQL1

Description
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 ECC
SELECT * from
(SELECT 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,
contract_number ecc_spec_id,
contract_status,
org_id,
ledger_name,
ledger_currency,
unaccrued_revenue*conversion_rate acctd_unaccrued_revenue,
unbilled_rents*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,  --Bug# 30723697
unprocess_pay_flag
from okl_ecc_arcon_cont_revenue acr,
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 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"))