ECC Receivables, Outstanding Receivables (9)

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,dfv.*,idfv.*,
       trx_v.aging_buckets,
       trx_v.ar_aging_buckets,
       trx_v.AGING_BUCKET_LINE_ID,
       trx_v.AR_AGING_BUCKET_LINE_ID,
       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" "'AR_ROW_ID'","CONTEXT_VALUE" "'AR_CONTEXT_VALUE'","ACTIVIDAD" "'AR_ACTIVIDAD'","EDICION" "'AR_EDICION'","SUBEVENTO" "'AR_SUBEVENTO'","CONCATENATED_SEGMENTS" "'AR_CONCATENATED_SEGMENTS'" from ( select ROWID "ROW_ID",ATTRIBUTE_CATEGORY "CONTEXT_VALUE",ATTRIBUTE1 "ACTIVIDAD",ATTRIBUTE2 "EDICION",ATTRIBUTE3 "SUBEVENTO",ATTRIBUTE1||'.'||ATTRIBUTE2||'.'||ATTRIBUTE3 "CONCATENATED_SEGMENTS" from RA_CUSTOMER_TRX_ALL )) dfv , (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_dff('RA_INTERFACE_HEADER','PROJECT_NUM___CONSOLIDATED_BG',"PROJECT_NUM___CONSOLIDATED_BG",222,"CONTEXT_VALUE") "'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'",fnd_ecc_dff_util.get_vset_segment_values_dff('RA_INTERFACE_HEADER','PROJECT_NUM___CONSOLIDATED_BG',"PROJECT_NUM___CONSOLIDATED_BG",222,"CONTEXT_VALUE") "'ARINT_PROJECT_NUM___CONSOLIDATED_BG_EVAL'",fnd_ecc_dff_util.get_vset_segment_value('RA_INTERFACE_HEADER','DRAFT_INVOICE_NUMBER',"DRAFT_INVOICE_NUMBER", NULL ,NULL,222) "'ARINT_DRAFT_INVOICE_NUMBER_EVAL'",fnd_ecc_dff_util.get_vset_segment_value('RA_INTERFACE_HEADER','DRAFT_CONSOLIDATED_INVOICE_NUM',"DRAFT_CONSOLIDATED_INVOICE_NUM", NULL ,NULL,222) "'ARINT_DRAFT_CONSOLIDATED_INVOICE_NUM_EVAL'" from ( select ROWID "ROW_ID",INTERFACE_HEADER_CONTEXT "CONTEXT_VALUE",(DECODE(INTERFACE_HEADER_CONTEXT,'ACCRUE FINANCE CHARGES',INTERFACE_HEADER_ATTRIBUTE1,NULL)) "DEBIT_MEMO_NUMBER",(DECODE(INTERFACE_HEADER_CONTEXT,'ACCRUE FINANCE CHARGES',INTERFACE_HEADER_ATTRIBUTE2,NULL)) "DEBIT_MEMO_LINE_NO",(DECODE(INTERFACE_HEADER_CONTEXT,'ACCRUE FINANCE CHARGES',INTERFACE_HEADER_ATTRIBUTE3,NULL)) "FINANCE_CHARGE_TYPE",(DECODE(INTERFACE_HEADER_CONTEXT,'ACCRUE FINANCE CHARGES',INTERFACE_HEADER_ATTRIBUTE4,NULL)) "INV_PAYMENT_SCHEDULE_ID",(DECODE(INTERFACE_HEADER_CONTEXT,'CLAIM',INTERFACE_HEADER_ATTRIBUTE1,NULL)) "CLAIM_NUMBER",(DECODE(INTERFACE_HEADER_CONTEXT,'CLAIM',INTERFACE_HEADER_ATTRIBUTE5,NULL)) "CUSTOMER_REFERENCE",(DECODE(INTERFACE_HEADER_CONTEXT,'CLAIM',INTERFACE_HEADER_ATTRIBUTE6,NULL)) "CUSTOMER_REASON",(DECODE(INTERFACE_HEADER_CONTEXT,'CLAIM',INTERFACE_HEADER_ATTRIBUTE7,NULL)) "CLAIM_REASON",(DECODE(INTERFACE_HEADER_CONTEXT,'EXCHANGE INVOICES',INTERFACE_HEADER_ATTRIBUTE1,NULL)) "BILLING_PARTY_ID",(DECODE(INTERFACE_HEADER_CONTEXT,'EXCHANGE INVOICES',INTERFACE_HEADER_ATTRIBUTE2,NULL)) "BILLING_SITE_USE_ID",(DECODE(INTERFACE_HEADER_CONTEXT,'EXCHANGE INVOICES',INTERFACE_HEADER_ATTRIBUTE3,NULL)) "BILLING_INTERFACE_REQUEST_ID",(DECODE(INTERFACE_HEADER_CONTEXT,'EXCHANGE INVOICES',INTERFACE_HEADER_ATTRIBUTE4,NULL)) "BILLING_PERIOD",(DECODE(INTERFACE_HEADER_CONTEXT,'EXCHANGE_XML_INVOICE',INTERFACE_HEADER_ATTRIBUTE1,NULL)) "BILL_TO_PARTY_ID",(DECODE(INTERFACE_HEADER_CONTEXT,'INTEREST INVOICE',INTERFACE_HEADER_ATTRIBUTE1,NULL)) "INTEREST_INVOICE_BATCH_ID",(DECODE(INTERFACE_HEADER_CONTEXT,'INTEREST INVOICE',INTERFACE_HEADER_ATTRIBUTE2,NULL)) "INTEREST_INVOICE_ID",(DECODE(INTERFACE_HEADER_CONTEXT,'INTEREST INVOICE',INTERFACE_HEADER_ATTRIBUTE3,NULL)) "PAYMENT_SCHEDULE_ID",(DECODE(INTERFACE_HEADER_CONTEXT,'INTEREST INVOICE',INTERFACE_HEADER_ATTRIBUTE4,NULL)) "LINE_NUMBER",(DECODE(INTERFACE_HEADER_CONTEXT,'OKL_CONTRACTS',INTERFACE_HEADER_ATTRIBUTE4,NULL)) "PRIVATE_LABEL",(DECODE(INTERFACE_HEADER_CONTEXT,'OKL_CONTRACTS',INTERFACE_HEADER_ATTRIBUTE2,NULL)) "INVOICE_GROUP",(DECODE(INTERFACE_HEADER_CONTEXT,'OKL_CONTRACTS',INTERFACE_HEADER_ATTRIBUTE15,NULL)) "INVOICE_TYPE",(DECODE(INTERFACE_HEADER_CONTEXT,'OKL_CONTRACTS',INTERFACE_HEADER_ATTRIBUTE11,NULL)) "TERMINATION_QUOTE_NUMBER",(DECODE(INTERFACE_HEADER_CONTEXT,'OKL_CONTRACTS',INTERFACE_HEADER_ATTRIBUTE13,NULL)) "OKL_SOURCE_BILLING_TRANSACTION",(DECODE(INTERFACE_HEADER_CONTEXT,'PROJECTS INVOICES',INTERFACE_HEADER_ATTRIBUTE1,'PA INVOICES',INTERFACE_HEADER_ATTRIBUTE1,NULL)) "PROJECT_NUM___CONSOLIDATED_BG",(DECODE(INTERFACE_HEADER_CONTEXT,'PA INVOICES',INTERFACE_HEADER_ATTRIBUTE2,NULL)) "DRAFT_INVOICE_NUMBER",(DECODE(INTERFACE_HEADER_CONTEXT,'PROJECTS INVOICES',INTERFACE_HEADER_ATTRIBUTE3,'PA INVOICES',INTERFACE_HEADER_ATTRIBUTE3,NULL)) "AGREEMENT_NUMBER",(DECODE(INTERFACE_HEADER_CONTEXT,'PROJECTS INVOICES',INTERFACE_HEADER_ATTRIBUTE4,'PA INVOICES',INTERFACE_HEADER_ATTRIBUTE4,NULL)) "PROJECT_ORGANIZATION",(DECODE(INTERFACE_HEADER_CONTEXT,'PROJECTS INVOICES',INTERFACE_HEADER_ATTRIBUTE5,'PA INVOICES',INTERFACE_HEADER_ATTRIBUTE5,NULL)) "PROJECT_MANAGER",(DECODE(INTERFACE_HEADER_CONTEXT,'PERIODICS-C',INTERFACE_HEADER_ATTRIBUTE1,NULL)) "PERIODICS_H",(DECODE(INTERFACE_HEADER_CONTEXT,'PERIODICS-C',INTERFACE_HEADER_ATTRIBUTE2,NULL)) "PERIODICS_S",(DECODE(INTERFACE_HEADER_CONTEXT,'PROJECTS INVOICES',INTERFACE_HEADER_ATTRIBUTE2,NULL)) "DRAFT_CONSOLIDATED_INVOICE_NUM",(DECODE(INTERFACE_HEADER_CONTEXT,'RISK CLASS',INTERFACE_HEADER_ATTRIBUTE1,NULL)) "RISK_CLASS",(DECODE(INTERFACE_HEADER_CONTEXT,'ACCRUE FINANCE CHARGES',INTERFACE_HEADER_CONTEXT||'.'||INTERFACE_HEADER_ATTRIBUTE1||'.'||INTERFACE_HEADER_ATTRIBUTE2||'.'||INTERFACE_HEADER_ATTRIBUTE3||'.'||INTERFACE_HEADER_ATTRIBUTE4,'CLAIM',INTERFACE_HEADER_CONTEXT||'.'||INTERFACE_HEADER_ATTRIBUTE1||'.'||INTERFACE_HEADER_ATTRIBUTE5||'.'||INTERFACE_HEADER_ATTRIBUTE6||'.'||INTERFACE_HEADER_ATTRIBUTE7,'EXCHANGE INVOICES',INTERFACE_HEADER_CONTEXT||'.'||INTERFACE_HEADER_ATTRIBUTE1||'.'||INTERFACE_HEADER_ATTRIBUTE2||'.'||INTERFACE_HEADER_ATTRIBUTE3||'.'||INTERFACE_HEADER_ATTRIBUTE4,'EXCHANGE_XML_INVOICE',INTERFACE_HEADER_CONTEXT||'.'||INTERFACE_HEADER_ATTRIBUTE1,'INTEREST INVOICE',INTERFACE_HEADER_CONTEXT||'.'||INTERFACE_HEADER_ATTRIBUTE1||'.'||INTERFACE_HEADER_ATTRIBUTE2||'.'||INTERFACE_HEADER_ATTRIBUTE3||'.'||INTERFACE_HEADER_ATTRIBUTE4,'OKL_CONTRACTS',INTERFACE_HEADER_CONTEXT||'.'||INTERFACE_HEADER_ATTRIBUTE4||'.'||INTERFACE_HEADER_ATTRIBUTE2||'.'||INTERFACE_HEADER_ATTRIBUTE15||'.'||INTERFACE_HEADER_ATTRIBUTE11||'.'||INTERFACE_HEADER_ATTRIBUTE13,'PA INVOICES',INTERFACE_HEADER_CONTEXT||'.'||INTERFACE_HEADER_ATTRIBUTE1||'.'||INTERFACE_HEADER_ATTRIBUTE2||'.'||INTERFACE_HEADER_ATTRIBUTE3||'.'||INTERFACE_HEADER_ATTRIBUTE4||'.'||INTERFACE_HEADER_ATTRIBUTE5,'PERIODICS-C',INTERFACE_HEADER_CONTEXT||'.'||INTERFACE_HEADER_ATTRIBUTE1||'.'||INTERFACE_HEADER_ATTRIBUTE2,'PROJECTS INVOICES',INTERFACE_HEADER_CONTEXT||'.'||INTERFACE_HEADER_ATTRIBUTE1||'.'||INTERFACE_HEADER_ATTRIBUTE2||'.'||INTERFACE_HEADER_ATTRIBUTE3||'.'||INTERFACE_HEADER_ATTRIBUTE4||'.'||INTERFACE_HEADER_ATTRIBUTE5,'RISK CLASS',INTERFACE_HEADER_CONTEXT||'.'||INTERFACE_HEADER_ATTRIBUTE1,NULL)) "CONCATENATED_SEGMENTS" from RA_CUSTOMER_TRX_ALL )) idfv  WHERE trx_v.row_id = dfv."'AR_ROW_ID'"(+) AND 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