ECC Payables, AP Closing, SQL4

select
x.*
from
(
select * from (
SELECT
ECC_SPEC_ID,
RECORD_TYPE,
EVENT_ID,
ORG_ID,
APPLICATION_ID,
INVOICE_ID,
INVOICE_TYPE_LOOKUP_CODE,
INVOICE_NUMBER,
TRANSACTION_CURRENCY,
INVOICE_DATE,
GL_DATE,
ACCOUNTED_AMOUNT,
PAYMENT_TYPE_FLAG,
PAYMENT_TYPE_DESC,
INSTALLMENT,
AMOUNT,
INVOICE_PAYMENT_ID,
CHECK_ID,
TRANSACTION_NUMBER,
TRANSACTION_DATE,
BANK_ACCOUNT_NAME,
VENDOR_ID,
SUPPLIER_NAME,
SUPPLIER_NUMBER,
SITE_CODE,
EVENT_STATUS_CODE,
EVENT_STATUS_DESC,
PROCESS_STATUS_CODE,
PROCESS_STATUS_DESC,
EVENT_TYPE_CODE,
EVENT_TYPE_DESC,
EVENT_NUMBER,
LEDGER_ID,
LEDGER_NAME,
CURRENCY_CODE,
PERIOD_NAME,
PERIOD_YEAR,
CLOSING_STATUS,
OPERATING_UNIT,
LANGUAGE
 
FROM
(
SELECT
    ip.invoice_payment_id
    || '-'
    || xe.event_id
    || '-'
    || xe.event_number ecc_spec_id ,
    'PAY' AS record_type,
    xe.event_id,
    ai.org_id,
	xe.APPLICATION_ID,
    ai.invoice_id                 invoice_id,
    ai.invoice_type_lookup_code   invoice_type_lookup_code,
    ai.invoice_num                invoice_number,
	ai.invoice_currency_code TRANSACTION_CURRENCY,
    ai.invoice_date               AS invoice_date,
	ai.GL_DATE,
 
    ( ip.amount * nvl(ip.exchange_rate, 1) ) AS ACCOUNTED_AMOUNT,
    c.payment_type_flag,
	(
        SELECT
            meaning
        FROM
            fnd_lookup_values
        WHERE
            view_application_id = 200
            AND lookup_type = 'PAYMENT TYPE'
            AND language = outl.language
            AND lookup_code = c.payment_type_flag
    ) PAYMENT_TYPE_DESC,
    ip.payment_num                installment,
    ip.amount                     AMOUNT,
    ip.invoice_payment_id,
	c.check_id,
	c.check_number TRANSACTION_NUMBER,
    c.check_date TRANSACTION_DATE,
    c.bank_account_name,
    ai.vendor_id,
    hp.party_name                 supplier_name,
    pav.segment1                  supplier_number,
    pos.vendor_site_code          site_code,
    xe.event_status_code,
    (
        SELECT
            meaning
        FROM
            fnd_lookup_values
        WHERE
            view_application_id = 602
            AND lookup_type = 'XLA_EVENT_STATUS'
                            AND language = outl.language
            AND lookup_code = xe.event_status_code
    ) event_status_desc,
    xe.process_status_code,
    (
        SELECT
            meaning
        FROM
            fnd_lookup_values
        WHERE
            view_application_id = 602
            AND lookup_type = 'XLA_EVENT_PROCESS_STATUS'
            AND language = outl.language
            AND lookup_code = xe.process_status_code
    ) process_status_desc,
	xe.event_type_code,
    (select ett.description from XLA_EVENT_TYPES_TL ett where xe.EVENT_TYPE_CODE = ett. EVENT_TYPE_CODE and xe.application_id = ett.application_id and ett.language = outl.language) as EVENT_TYPE_DESC,
    xe.event_number,
    gl.ledger_id,
    gl.name                       ledger_name,
    gl.currency_code,
    ps.period_name,
    ps.period_year,
    ps.closing_status,
    outl.name                     operating_unit,
    outl.language
FROM
    gl_period_statuses             ps,
    ap_invoices_all                ai,
    gl_ledgers                     gl,
    ap_invoice_payments_all        ip,
    ap_checks_all                  c,
    ap_suppliers                   pav,
    ap_supplier_sites_all          pos,
    hr_all_organization_units      ou,
    hr_all_organization_units_tl   outl,
    hz_parties                     hp,
    xla_events                     xe
WHERE
    xe.application_id = 200
    AND ps.adjustment_period_flag = 'N'
    AND ps.set_of_books_id = ai.set_of_books_id
    AND ai.gl_date BETWEEN ps.start_date AND ps.end_date
    AND ps.closing_status = 'O'
    AND ps.application_id = xe.application_id
    AND ai.invoice_id = ip.invoice_id
    AND ip.check_id = c.check_id
    AND ps.set_of_books_id = ip.set_of_books_id
    AND ip.accounting_date BETWEEN ps.start_date AND ps.end_date
    AND ai.set_of_books_id = gl.ledger_id
    AND ou.organization_id = ai.org_id
    AND ou.organization_id = outl.organization_id
    AND ai.vendor_id = pav.vendor_id
    AND ai.vendor_site_id = pos.vendor_site_id
    AND hp.party_id = ai.party_id
    AND ai.payment_status_flag = 'Y'
    AND ip.posted_flag = 'N'
    AND ip.accounting_event_id = xe.event_id
    AND xe.event_status_code = 'U'
    AND xe.process_status_code = 'U'  )temp
where temp.language in ('US'))
PIVOT (max(OPERATING_UNIT) as OPERATING_UNIT, max(EVENT_STATUS_DESC) as EVENT_STATUS_DESC,
        max(PROCESS_STATUS_DESC) as PROCESS_STATUS_DESC,
		max(EVENT_TYPE_DESC) as EVENT_TYPE_DESC, max(PAYMENT_TYPE_DESC) as PAYMENT_TYPE_DESC
For LANGUAGE in ('US' "US"))
) x
where
2=2