ECC Receivables, Payment History, SQL1

➜ Back to Library
Description
Categories: Enterprise Command Center
Columns: Ledger Currency Code, Ledger, Customer, Account Number, Transaction Number, Transaction Currency Code, Transaction Type, Transaction Date, Transaction Amount, Discount Taken Amount ...
Imported from Enterprise Command Center
Dataset Key: ar-history
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.process_t) full(trx_v.temp.process_t) */
trx_v.OPERATING_UNIT, trx_v.LEDGER_CURRENCY_CODE,
trx_v.LEDGER_CURRENCY, trx_v.LEDGER, trx_v.CUSTOMER, trx_v.ACCOUNT_NUMBER, trx_v.TRANSACTION_NUMBER,
trx_v.TRANSACTION_CURRENCY_CODE,
trx_v.TRANSACTION_CURRENCY, trx_v.TRANSACTION_CLASS, trx_v.TRANSACTION_TYPE, trx_v.TRANSACTION_DATE, trx_v.TRANSACTION_AMOUNT,
trx_v.DISCOUNT_TAKEN_AMOUNT, trx_v.PAID_DATE, trx_v.TRANSACTION_SOURCE, trx_v.PURCHASE_ORDER_NUMBER,
trx_v.SALES_ORDER_NUMBER, trx_v.SALESPERSON,
trx_v.TRANSACTION_CONTEXT, trx_v.TRANSACTION_REFERENCE, trx_v.BILL_TO_LOCATION, trx_v.SHIP_TO_CUSTOMER,
trx_v.SHIP_TO_LOCATION, trx_v.COLLECTOR,
trx_v.PROFILE_CLASS, trx_v.AVERAGE_DAYS_PAID, trx_v.AVERAGE_DAYS_DELINQUENT, trx_v.ACCOUNTED_AMOUNT,
trx_v.ADJUSTED_AMOUNT, trx_v.INSTALLMENT,
trx_v.TRANSACTION_ID, trx_v.DUE_DATE, trx_v.TRANSACTION_CLASS_CODE, trx_v.ORG_ID,
trx_v.PAYMENT_SCHEDULE_ID, trx_v.ECC_SPEC_ID,
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.LANGUAGE
  FROM ar_ecc_trx_history_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.trx_row_id = idfv."'ARINT_ROW_ID'"(+)
  AND trx_v.language in ('US'))
   PIVOT(max(SALESPERSON) as SALESPERSON,max(OPERATING_UNIT) as OPERATING_UNIT, max(LEDGER_CURRENCY) as LEDGER_CURRENCY, max(TRANSACTION_CURRENCY) as TRANSACTION_CURRENCY,
   max(TRANSACTION_CLASS) as TRANSACTION_CLASS
   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
Download
Related Blitz Reports
ECC Receivables, Billing Process, SQL1 Skeleton ecc ECC Receivables, Outstanding Receivables ECC Receivables, Billing Process, SQL2 AR Bills Receivable Remittance Batch Management CE Cleared Transactions - draft AR Miscellaneous Receipts AR Bills Receivable Reminder Letters AR Transactions and Payments
Blitz Report In Action
Blitz Report™

Blitz Report™ provides multiple benefits: