ECC Receivables, Payment History, SQL1

➜ Back to Library
Description
Categories: Enterprise Command Center
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
Run ECC Receivables, Payment History, SQL1 and other Oracle EBS reports with Blitz Report™ on our demo environment
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'",FND_ECC_DFF_UTIL.get_vset_values('RA_INTERFACE_HEADER','FINANCE_CHARGE_TYPE',FINANCE_CHARGE_TYPE, NULL ,NULL,222) "'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'",FND_ECC_DFF_UTIL.get_vset_values('RA_INTERFACE_HEADER','PROJECT_NUM___CONSOLIDATED_BG',PROJECT_NUM___CONSOLIDATED_BG, NULL ,NULL,222) "'ARINT_PROJECT_NUM___CONSOLIDATED_BG'",FND_ECC_DFF_UTIL.get_vset_values('RA_INTERFACE_HEADER','DRAFT_INVOICE_NUMBER',DRAFT_INVOICE_NUMBER, NULL ,NULL,222) "'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'",FND_ECC_DFF_UTIL.get_vset_values('RA_INTERFACE_HEADER','DRAFT_CONSOLIDATED_INVOICE_NUM',DRAFT_CONSOLIDATED_INVOICE_NUM, NULL ,NULL,222) "'ARINT_DRAFT_CONSOLIDATED_INVOICE_NUM'",FND_ECC_DFF_UTIL.get_vset_values('RA_INTERFACE_HEADER','RISK_CLASS',RISK_CLASS, NULL ,NULL,222) "'ARINT_RISK_CLASS'",CONCATENATED_SEGMENTS "'ARINT_CONCATENATED_SEGMENTS'",FINANCE_CHARGE_TYPE "'ARINT_FINANCE_CHARGE_TYPE_EDOC'",PROJECT_NUM___CONSOLIDATED_BG "'ARINT_PROJECT_NUM___CONSOLIDATED_BG_EDOC'",DRAFT_INVOICE_NUMBER "'ARINT_DRAFT_INVOICE_NUMBER_EDOC'",DRAFT_CONSOLIDATED_INVOICE_NUM "'ARINT_DRAFT_CONSOLIDATED_INVOICE_NUM_EDOC'",RISK_CLASS "'ARINT_RISK_CLASS_EDOC'" 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, SQL2 Skeleton ecc ECC Receivables, Billing Process, SQL1 ECC Receivables, Outstanding Receivables (2) ECC Receivables, Outstanding Receivables ECC Receivables, Outstanding Receivables (1) AR Bills Receivable Remittance Batch Management AR Miscellaneous Receipts GL Account Analysis (Distributions)
Blitz Report™

Blitz Report™ provides multiple benefits: