ECC Receivables, Billing Process, SQL2

Description
Categories: Enterprise Command Center
Columns: Ledger Currency Code, Ledger, Account Number, Bill To Customer, Bill To Location, Ship To Customer, Ship To Location, Adjustment Number, Adjustment Amount, Adjustment Date ...
Imported from Enterprise Command Center
Dataset Key: ar-billproc
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 trx_v.temp.trx_v trx_v.temp.trx) full(trx_v.temp.process_t) cardinality(trx_v.temp.process_t 10) */  trx_v.OPERATING_UNIT
, trx_v.LEDGER_CURRENCY
, trx_v.LEDGER_CURRENCY_CODE
, trx_v.LEDGER
, 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.ADJUSTMENT_NUMBER
, trx_v.ADJUSTMENT_AMOUNT
, trx_v.ADJUSTMENT_DATE
, trx_v.RECEIVABLE_ACTIVITY
, trx_v.ADJUSTMENT_STATUS
, trx_v.ADJUSTMENT_STATUS_CODE
, trx_v.ADJUSTMENT_TYPE
, trx_v.ADJUSTMENT_TYPE_CODE
, trx_v.ADJUSTMENT_REASON
, trx_v.ADJUSTMENT_REASON_CODE
, trx_v.TRANSACTION_NUMBER
, trx_v.TRANSACTION_AMOUNT
, trx_v.TRANSACTION_SOURCE
, trx_v.ADJUSTMENT_ACCOUNTED_AMOUNT
, trx_v.TRANSACTION_ID
, trx_v.TRANSACTION_CURRENCY
, trx_v.TRANSACTION_CURRENCY_CODE
, trx_v.ADJUSTMENT_ID
, trx_v.ORG_ID
, trx_v.ECC_SPEC_ID
, trx_v.ECC_LAST_UPDATE_DATE
, trx_v.RECORD_TYPE
, idfv.*, trx_v.LANGUAGE
  FROM ar_ecc_adjustment_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.row_id = idfv."'ARINT_ROW_ID'"(+)
  AND trx_v.language in ('US'))
   PIVOT(max(OPERATING_UNIT) as OPERATING_UNIT, max(LEDGER_CURRENCY) as LEDGER_CURRENCY, max(TRANSACTION_CURRENCY) as TRANSACTION_CURRENCY,
   max(ADJUSTMENT_REASON) as ADJUSTMENT_REASON,max(ADJUSTMENT_TYPE) as ADJUSTMENT_TYPE,max(ADJUSTMENT_STATUS) as ADJUSTMENT_STATUS
   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