ECC Receivables, Payment History, SQL1

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,dfv.*,idfv.*, trx_v.LANGUAGE
  FROM ar_ecc_trx_history_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'" 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.trx_row_id = dfv."'AR_ROW_ID'"(+) AND 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