ECC Receivables, Outstanding Receivables, SQL1 (1)

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 ECC
System: EBS
Dataset Key: ar-trx
Description: null
select * from (
SELECT * from (SELECT /*+ leading(trx_v.temp.trx_t) full(trx_v.temp.trx_t) */
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 'ARINT_ROW_ID','ARINT_CONTEXT_VALUE','ARINT_DEBIT_MEMO_NUMBER','ARINT_DEBIT_MEMO_LINE_NO','ARINT_FINANCE_CHARGE_TYPE','ARINT_INV_PAYMENT_SCHEDULE_ID','ARINT_CLAIM_NUMBER','ARINT_CUSTOMER_REFERENCE','ARINT_CUSTOMER_REASON','ARINT_CLAIM_REASON','ARINT_BILLING_PARTY_ID','ARINT_BILLING_SITE_USE_ID','ARINT_BILLING_INTERFACE_REQUEST_ID','ARINT_BILLING_PERIOD','ARINT_BILL_TO_PARTY_ID','ARINT_INTEREST_INVOICE_BATCH_ID','ARINT_INTEREST_INVOICE_ID','ARINT_PAYMENT_SCHEDULE_ID','ARINT_LINE_NUMBER','ARINT_PRIVATE_LABEL','ARINT_INVOICE_GROUP','ARINT_INVOICE_TYPE','ARINT_TERMINATION_QUOTE_NUMBER','ARINT_OKL_SOURCE_BILLING_TRANSACTION','ARINT_PROJECT_NUM___CONSOLIDATED_BG','ARINT_DRAFT_INVOICE_NUMBER','ARINT_AGREEMENT_NUMBER','ARINT_PROJECT_ORGANIZATION','ARINT_PROJECT_MANAGER','ARINT_PERIODICS_H','ARINT_PERIODICS_S','ARINT_DRAFT_CONSOLIDATED_INVOICE_NUM','ARINT_RISK_CLASS','ARINT_CONCATENATED_SEGMENTS' from dual where 1=2  union select ROWIDTOCHAR(ROW_ID),CONTEXT_VALUE,DEBIT_MEMO_NUMBER,to_char(DEBIT_MEMO_LINE_NO),FINANCE_CHARGE_TYPE,to_char(INV_PAYMENT_SCHEDULE_ID),CLAIM_NUMBER,CUSTOMER_REFERENCE,CUSTOMER_REASON,CLAIM_REASON,BILLING_PARTY_ID,BILLING_SITE_USE_ID,BILLING_INTERFACE_REQUEST_ID,BILLING_PERIOD,BILL_TO_PARTY_ID,INTEREST_INVOICE_BATCH_ID,INTEREST_INVOICE_ID,PAYMENT_SCHEDULE_ID,LINE_NUMBER,PRIVATE_LABEL,INVOICE_GROUP,INVOICE_TYPE,TERMINATION_QUOTE_NUMBER,OKL_SOURCE_BILLING_TRANSACTION,PROJECT_NUM___CONSOLIDATED_BG,DRAFT_INVOICE_NUMBER,AGREEMENT_NUMBER,PROJECT_ORGANIZATION,PROJECT_MANAGER,PERIODICS_H,PERIODICS_S,DRAFT_CONSOLIDATED_INVOICE_NUM,RISK_CLASS,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"))
)
where 1=1
Parameter Name SQL text Validation
Operating Unit
ORG_ID in (select organization_id from hr_all_organization_units where name = :op_unit)
LOV