ECC Lease Contracts, Lease Outstanding Receivables

Description
Categories: Enterprise Command Center
Columns: Account Number, Bill To Customer, Bill To Location, Ship To Customer, Ship To Location, Collector, Profile Class, Transaction Number, Transaction Type, Transaction Source ...
Imported from Enterprise Command Center
Dataset Key: okl-ar-trx
Query Procedure: OKL_AR_ECC_UTIL_PVT.GET_ECC_DATA_LOAD_INFO
Security Procedure:
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 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 NameSQL textValidation
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