ECC Receivables, Outstanding Receivables

➜ Back to Library
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: ar-trx
Query Procedure: AR_ECC_UTIL_PVT.GET_ECC_DATA_LOAD_INFO
Security Procedure: AR_ECC_DATASECURITY_PKG_PUB.GetFilterAttributeValues 
select
x.*
from
(
SELECT * from (SELECT /*+ leading ( trx_v.temp.trx_t trx_v.temp.ps trx_v.temp.trx) full (trx_v.temp.trx_t ) cardinality (trx_v.temp.trx_t 10) use_nl(trx_v.temp.trx) */
trx_v.ACCOUNT_NUMBER,trx_v.BILL_TO_CUSTOMER,
trx_v.BILL_TO_LOCATION,trx_v.SHIP_TO_CUSTOMER,trx_v.SHIP_TO_LOCATION,trx_v.COLLECTOR,trx_v.PROFILE_CLASS,trx_v.TRANSACTION_NUMBER,
trx_v.TRANSACTION_CLASS,trx_v.TRANSACTION_TYPE,trx_v.TRANSACTION_SOURCE,trx_v.CURRENCY_CODE,trx_v.CURRENCY,trx_v.PURCHASE_ORDER_NUMBER,
trx_v.SALES_ORDER_NUMBER,trx_v.SALESPERSON,trx_v.OPERATING_UNIT,trx_v.TRANSACTION_CONTEXT,trx_v.TRANSACTION_REFERENCE,
trx_v.TRANSACTION_CURRENCY_CODE,trx_v.TRANSACTION_CURRENCY,trx_v.LEDGER_CURRENCY_CODE,trx_v.LEDGER_CURRENCY,trx_v.LEDGER,
trx_v.DUE_ACCOUNTED_AMOUNT,trx_v.ACCOUNTED_AMOUNT,trx_v.DUE_DATE,trx_v.TRANSACTION_DATE,trx_v.ACCOUNTING_DATE,trx_v.PURCHASE_ORDER_DATE,
trx_v.ECC_SPEC_ID,trx_v.PAYMENT_SCHEDULE_ID,trx_v.INSTALLMENT,trx_v.TRANSACTION_CLASS_CODE,trx_v.TRANSACTION_AMOUNT,trx_v.DUE_AMOUNT,
trx_v.APPLIED_AMOUNT,trx_v.STATUS,trx_v.TRANSACTION_ID,trx_v.CREDIT_MEMO_REASON_CODE,trx_v.CREDIT_MEMO_REASON,trx_v.COMMENTS,
trx_v.INTERNAL_NOTES,trx_v.SPECIAL_INSTRUCTION,trx_v.SHIP_TO_CUSTOMER_NUMBER,trx_v.ORG_ID,trx_v.BILL_TO_CUSTOMER_ID,
trx_v.BILL_TO_SITE_USE_ID,trx_v.BILL_TO_CONTACT,trx_v.ECC_LAST_UPDATE_DATE,trx_v.RECORD_TYPE,trx_v.ATTRIBUTE_CATEGORY,trx_v.ATTRIBUTE1,
trx_v.ATTRIBUTE2,trx_v.ATTRIBUTE3,trx_v.ATTRIBUTE4,trx_v.ATTRIBUTE5,trx_v.ATTRIBUTE6,trx_v.ATTRIBUTE7,trx_v.ATTRIBUTE8,trx_v.ATTRIBUTE9,
trx_v.ATTRIBUTE10,trx_v.ATTRIBUTE11,trx_v.ATTRIBUTE12,trx_v.ATTRIBUTE13,trx_v.ATTRIBUTE14,trx_v.ATTRIBUTE15,idfv.*,
       trx_v.aging_buckets,
       trx_v.ar_aging_buckets,
       trx_v.discount_expiration_date,
       trx_v.discount_flag,
       trx_v.discount_amount, trx_v.LANGUAGE
  FROM ARI_ECC_TRX_V trx_v  , (select ROW_ID "'ARINT_ROW_ID'",CONTEXT_VALUE "'ARINT_CONTEXT_VALUE'",DEBIT_MEMO_NUMBER "'ARINT_DEBIT_MEMO_NUMBER'",to_char(DEBIT_MEMO_LINE_NO) "'ARINT_DEBIT_MEMO_LINE_NO'",FINANCE_CHARGE_TYPE "'ARINT_FINANCE_CHARGE_TYPE'",to_char(INV_PAYMENT_SCHEDULE_ID) "'ARINT_INV_PAYMENT_SCHEDULE_ID'",CLAIM_NUMBER "'ARINT_CLAIM_NUMBER'",CUSTOMER_REFERENCE "'ARINT_CUSTOMER_REFERENCE'",CUSTOMER_REASON "'ARINT_CUSTOMER_REASON'",CLAIM_REASON "'ARINT_CLAIM_REASON'",BILLING_PARTY_ID "'ARINT_BILLING_PARTY_ID'",BILLING_SITE_USE_ID "'ARINT_BILLING_SITE_USE_ID'",BILLING_INTERFACE_REQUEST_ID "'ARINT_BILLING_INTERFACE_REQUEST_ID'",BILLING_PERIOD "'ARINT_BILLING_PERIOD'",BILL_TO_PARTY_ID "'ARINT_BILL_TO_PARTY_ID'",INTEREST_INVOICE_BATCH_ID "'ARINT_INTEREST_INVOICE_BATCH_ID'",INTEREST_INVOICE_ID "'ARINT_INTEREST_INVOICE_ID'",PAYMENT_SCHEDULE_ID "'ARINT_PAYMENT_SCHEDULE_ID'",LINE_NUMBER "'ARINT_LINE_NUMBER'",PRIVATE_LABEL "'ARINT_PRIVATE_LABEL'",INVOICE_GROUP "'ARINT_INVOICE_GROUP'",INVOICE_TYPE "'ARINT_INVOICE_TYPE'",TERMINATION_QUOTE_NUMBER "'ARINT_TERMINATION_QUOTE_NUMBER'",OKL_SOURCE_BILLING_TRANSACTION "'ARINT_OKL_SOURCE_BILLING_TRANSACTION'",PROJECT_NUM___CONSOLIDATED_BG "'ARINT_PROJECT_NUM___CONSOLIDATED_BG'",DRAFT_INVOICE_NUMBER "'ARINT_DRAFT_INVOICE_NUMBER'",AGREEMENT_NUMBER "'ARINT_AGREEMENT_NUMBER'",PROJECT_ORGANIZATION "'ARINT_PROJECT_ORGANIZATION'",PROJECT_MANAGER "'ARINT_PROJECT_MANAGER'",PERIODICS_H "'ARINT_PERIODICS_H'",PERIODICS_S "'ARINT_PERIODICS_S'",DRAFT_CONSOLIDATED_INVOICE_NUM "'ARINT_DRAFT_CONSOLIDATED_INVOICE_NUM'",RISK_CLASS "'ARINT_RISK_CLASS'",CONCATENATED_SEGMENTS "'ARINT_CONCATENATED_SEGMENTS'" from RA_CUSTOMER_TRX_ALL2_DFV) idfv  WHERE trx_v.row_id = idfv."'ARINT_ROW_ID'"(+) and language in ('US'))
   PIVOT (max(SALESPERSON) as SALESPERSON, max(AGING_BUCKETS) as AGING_BUCKETS, max(AR_AGING_BUCKETS) as AR_AGING_BUCKETS,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
          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
Period From 
x.accounting_date>=(select
gps.start_date
from
gl_period_statuses gps
where
gps.period_name=:period_name and
gps.set_of_books_id=(select hou.set_of_books_id from hr_operating_units hou where hou.organization_id=fnd_global.org_id) and
gps.application_id=222)
 LOV
Period To 
x.accounting_date<(select
gps.end_date+1
from
gl_period_statuses gps
where
gps.period_name=:period_name_to and
gps.set_of_books_id=(select hou.set_of_books_id from hr_operating_units hou where hou.organization_id=fnd_global.org_id) and
gps.application_id=222)
 LOV
Download
Related Blitz Reports
Skeleton ecc ECC Receivables, Outstanding Receivables (2) ECC Receivables, Outstanding Receivables (1) ECC Receivables, Billing Process, SQL1 ECC Receivables, Payment History, SQL1 ECC Receivables, Billing Process, SQL2 ECC Lease Contracts, Lease Outstanding Receivables AR iReceivables Customers Statement Data Definition ECC Lease Contracts, Lease Invoice Lines
Blitz Report In Action
Blitz Report™

Blitz Report™ provides multiple benefits: