ECC Lease Contracts, Lease Outstanding Receivables

Description
Categories: Enterprise Command Center
Imported from Enterprise Command Center
Dataset Key: okl-ar-trx
Query Procedure: OKL_AR_ECC_UTIL_PVT.GET_ECC_DATA_LOAD_INFO
Security Procedure:
Run ECC Lease Contracts, Lease Outstanding Receivables and other Oracle EBS reports with Blitz Report™ on our demo environment
select
x.*
from
(
SELECT * from (SELECT
                /*+ leading(trx_v.trx, ps) full(trx_v.trx)
		cardinality(trx_v.trx 10)
              index(trx_v.org, HR_ALL_ORGANIZATION_UNTS_TL_PK)
              index(trx_v.ps, AR_PAYMENT_SCHEDULES_N2)
              index(trx_v.u_bill, HZ_CUST_SITE_USES_U1)
              index(trx_v.u_ship, HZ_CUST_SITE_USES_U1)
              index(trx_v.types, RA_CUST_TRX_TYPES_U1)
                         */
                trx_v.*,
			   to_number(trx_v.CREDIT_APPLIED_DATE-trx_v.TRANSACTION_DATE) app_Date_diff,
			   trx_t.aging_bucket as aging_buckets,
			   trx_t.ar_aging_bucket as ar_aging_buckets,
			   To_Char(trx_t.discount_amt,
					   fnd_currency.get_format_mask(trx_v.currency_code, 40)) discount_amount_f,
			   trx_t.discount_date discount_expiration_date,
			   trx_t.discount_flag,
			   trx_t.discount_amt discount_amount,
                           (nvl(trx_v.accounted_amount_applied,0) - (nvl(trx_v.accounted_amount_credited,0) + nvl(trx_v.accounted_amount_adjusted,0))) closed_amount,  -- Added by kgopiset 30869903
 			   To_Char(Nvl(ps.amount_in_dispute, To_Number(0)),
			   fnd_currency.get_format_mask(trx_v.transaction_currency_code, 40)) open_disputed_amount_f
 
			  FROM okl_ari_ecc_trx_v        trx_v,
				   okl_ecc_trx_process_t    trx_t,
				   ar_payment_schedules_all ps
 
			 WHERE trx_v.payment_schedule_id = trx_t.payment_schedule_id
			   AND ps.payment_schedule_id = trx_t.payment_schedule_id
        		   AND trx_t.incremental_flag ='N'
 
         		   AND language in ('US') )
			 PIVOT (max(TRANSACTION_CLASS) as TRANSACTION_CLASS,
					max(CREDIT_MEMO_REASON) as CREDIT_MEMO_REASON,
					max(OPERATING_UNIT) as OPERATING_UNIT,
					max(TRANSACTION_CURRENCY) as TRANSACTION_CURRENCY,
					max(LEDGER_CURRENCY) as LEDGER_CURRENCY,
					max(CURRENCY) as CURRENCY,
					max(ATTRIBUTE13) as ATTRIBUTE13
					  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
Blitz Report™