ECC Payables, Installments, SQL1

Description
Categories: Enterprise Command Center
Imported from Enterprise Command Center
Dataset Key: ap-trx
Query Procedure: AP_ECC_UTIL_PVT.GET_ECC_DATA_LOAD_INFO
Security Procedure: AP_ECC_DATASECURITY_PKG_PUB.GetFilterAttributeValues
Run ECC Payables, Installments, SQL1 and other Oracle EBS reports with Blitz Report™ on our demo environment
select
x.*
from
(
select * from (select /*+  leading ( trx_v.temp.trx_t trx_v.temp.ai trx_v.temp.ps trx_v.temp.term)
use_nl(trx_v.temp.ai trx_v.temp.ps trx_v.temp.pav trx_v.temp.term trx_v.temp.gl) use_nl(trx_v.temp.term trx_v.temp.inv_curr_tl trx_v.temp.gl_curr_tl) full ( trx_v.temp.trx_t ) index ( trx_v.temp.ai )  */ ECC_SPEC_ID,
dfv.*, 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,trx_v.RECORD_TYPE,trx_v.OPERATING_UNIT,trx_v.ORG_ID,trx_v.
 INVOICE_ID,trx_v.INVOICE_TYPE_LOOKUP_CODE,trx_v.INVOICE_TYPE,trx_v.INVOICE_NUMBER,trx_v.LEGAL_ENTITY,trx_v.
 VALIDATION_STATUS,trx_v.INVOICE_AMOUNT,trx_v.UNPAID_AMOUNT,trx_v.INV_CURRENCY,trx_v.INV_CURRENCY_CODE,trx_v.INVOICE_DATE,trx_v.
 PO_NUMBERS,trx_v.VOUCHER_NUM,trx_v.SOURCE,trx_v.BASE_AMOUNT,trx_v.PAYMENT_TERM,trx_v.
 GL_DATE,trx_v.EXCHANGE_RATE,trx_v.DUE_DATE,trx_v.PAYMENT_STATUS_FLAG,trx_v.PAYMENT_STATUS,trx_v.
 AMOUNT_REMAINING,trx_v.AMOUNT_REMAINING_BASE,trx_v.INSTALLMENT,trx_v.FIRST_DISCOUNT_DATE,trx_v.SECOND_DISCOUNT_DATE,trx_v.
 THIRD_DISCOUNT_DATE,trx_v.DISCOUNT_AMOUNT_AVAILABLE,trx_v.SECOND_DISC_AMT_AVAILABLE,trx_v.THIRD_DISC_AMT_AVAILABLE,trx_v.DISCOUNT_AMOUNT_AVAILABLE_BASE,trx_v.
 SECOND_DISC_AMT_AVAILABLE_BASE,trx_v.THIRD_DISC_AMT_AVAILABLE_BASE,trx_v.HOLD_TYPE,trx_v.HOLD_NAME,trx_v.HOLD_COUNT,trx_v.
 SUPPLIER_TYPE,trx_v.VENDOR_ID,trx_v.SUPPLIER_NAME,trx_v.SUPPLIER_NUMBER,trx_v.SITE_CODE,trx_v.
 LEDGER_ID,trx_v.LEDGER,trx_v.GL_CURRENCY,trx_v.GL_CURRENCY_CODE,trx_v.SUPPLIER_TYPE_CODE,  trx_v.LANGUAGE,
 trx_v.aging_bucket as aging_bucket, trx_v.NEXT_DISCOUNT_DATE, trx_v.EARLIEST_OPEN_HOLD_DATE,
    trx_v.INVOICE_ON_HOLD_FLAG from AP_ECC_INSTALLMENT_V trx_v  , (select ROW_ID "'AP_ROW_ID'",CONTEXT "'AP_CONTEXT'",MISC_VENDOR_NAME "'AP_MISC_VENDOR_NAME'",MISC_VENDOR_ADDRESS "'AP_MISC_VENDOR_ADDRESS'",MISC_VENDOR_CITY "'AP_MISC_VENDOR_CITY'",MISC_VENDOR_STATE "'AP_MISC_VENDOR_STATE'",to_char(MISC_VENDOR_ZIP) "'AP_MISC_VENDOR_ZIP'",CONCATENATED_SEGMENTS "'AP_CONCATENATED_SEGMENTS'" from AP_INVOICES_ALL_DFV) dfv
    where language in ('US')  AND TRX_V.ROW_ID = DFV."'AP_ROW_ID'"(+)
 ) PIVOT (max(OPERATING_UNIT) as OPERATING_UNIT , max(INVOICE_TYPE) as INVOICE_TYPE,max(PAYMENT_STATUS) as PAYMENT_STATUS,max(HOLD_TYPE) as HOLD_TYPE,
 max(HOLD_NAME) as HOLD_NAME, max(SUPPLIER_TYPE) as SUPPLIER_TYPE, max(PAYMENT_TERM) as PAYMENT_TERM,max (INV_CURRENCY) as INV_CURRENCY, max(GL_CURRENCY) as GL_CURRENCY
 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