ECC Payables, AP Closing, SQL3

select
x.*
from
(
select * from (
SELECT
ECC_SPEC_ID,
RECORD_TYPE,
INVOICE_TYPE_LOOKUP_CODE,
ORG_ID,
TRANSACTION_CURRENCY,
SUPPLIER_NAME,
SUPPLIER_NUMBER,
SITE_CODE,
INVOICE_ID,
TRANSACTION_NUMBER,
TRANSACTION_DATE,
GL_DATE,
INVOICE_DISTRIBUTION_ID,
DISTRIBUTION_LINE_NUMBER,
INV_PERIOD,
AMOUNT,
ACCOUNTED_AMOUNT,
APPLICATION_ID,
EVENT_ID,
EVENT_STATUS_CODE,
EVENT_STATUS_DESC,
PROCESS_STATUS_CODE,
PROCESS_STATUS_DESC,
EVENT_TYPE_CODE,
EVENT_TYPE_DESC,
LINE_TYPE_LOOKUP_CODE,
LINE_TYPE_DESC,
EVENT_NUMBER,
LEDGER_ID,
LEDGER_NAME,
CURRENCY_CODE,
PERIOD_NAME,
PERIOD_YEAR,
CLOSING_STATUS,
OPERATING_UNIT,
LANGUAGE
FROM
(SELECT
    aia.invoice_id
    || '-'
    ||aida.invoice_distribution_id
    || '-'
    || xe.event_id
    || '-'
    || xe.event_number ecc_spec_id,
    'INV' AS record_type,
    aia.invoice_type_lookup_code,
	aia.org_id,
	aia.invoice_currency_code as TRANSACTION_CURRENCY,
    ap.vendor_name         supplier_name,
    ap.segment1            supplier_number,
    pos.vendor_site_code   site_code,
    aia.invoice_id,
    aia.invoice_num        TRANSACTION_NUMBER,
    aia.invoice_date TRANSACTION_DATE,
    aia.gl_date,
    aida.invoice_distribution_id,
    aida.distribution_line_number,
    aida.period_name       inv_period,
    aida.amount,
	(CASE WHEN aida.base_amount IS NULL
    THEN (aida.amount * nvl(aida.exchange_rate,1))
    ELSE aida.base_amount
	END) as ACCOUNTED_AMOUNT,
 
 
    xe.application_id,
    xe.event_id,
    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,
    LINE_TYPE_LOOKUP_CODE,
    (select DISPLAYED_FIELD from AP_LOOKUP_CODES where LOOKUP_TYPE = 'INVOICE DISTRIBUTION TYPE'
    and LOOKUP_CODE = aida.LINE_TYPE_LOOKUP_CODE) as LINE_TYPE_DESC,
    xe.event_number,
    lgr.ledger_id,
    lgr.name               ledger_name,
    lgr.currency_code,
    ps.period_name,
    ps.period_year,
    ps.closing_status,
    outl.name              operating_unit,
    outl.language
FROM
    ap_invoices_all                aia,
    ap_suppliers                   ap,
    ap_invoice_distributions_all   aida,
    ap_supplier_sites_all          pos,
    xla_events                     xe,
    gl_ledgers                     lgr,
    gl_period_statuses             ps,
    hr_all_organization_units      ou,
    hr_all_organization_units_tl   outl
WHERE
    aia.invoice_id = aida.invoice_id
    AND ap.vendor_id = aia.vendor_id
    AND aida.posted_flag = 'N'
    AND aida.distribution_line_number IS NOT NULL
    AND aia.vendor_site_id = pos.vendor_site_id
    AND xe.application_id = 200
    AND aida.accounting_event_id = xe.event_id
    AND aia.set_of_books_id = lgr.ledger_id
    AND ou.organization_id = aia.org_id
    AND ou.organization_id = outl.organization_id
    AND ps.adjustment_period_flag = 'N'
    AND ps.set_of_books_id = aia.set_of_books_id
    AND aia.gl_date BETWEEN ps.start_date AND ps.end_date
    AND ps.closing_status = 'O'
    AND ps.application_id = xe.application_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(LINE_TYPE_DESC) as LINE_TYPE_DESC
For LANGUAGE in ('US' "US"))
) x
where
2=2