ECC Payables, AP Closing, SQL1

select
x.*
from
(
 
select * from (
select
ECC_SPEC_ID,
RECORD_TYPE,
INVOICE_TYPE_LOOKUP_CODE,
ORG_ID,
TRANSACTION_CURRENCY,
VENDOR_ID,
SUPPLIER_NAME,
SUPPLIER_NUMBER,
SITE_CODE,
INVOICE_ID,
TRANSACTION_NUMBER,
TRANSACTION_DATE,
GL_DATE,
APPLICATION_ID,
EVENT_ID,
EVENT_STATUS_CODE,
EVENT_STATUS_DESC,
PROCESS_STATUS_CODE,
PROCESS_STATUS_DESC,
EVENT_TYPE_CODE,
EVENT_TYPE_DESC,
EVENT_NUMBER,
DISPLAYED_LINE_NUMBER,
AE_HEADER_ID,
GL_TRANSFER_STATUS_CODE,
GL_TRANSFER_FLAG_DESC,
ACCOUNTING_ENTRY_STATUS_CODE,
ACC_ENTRY_STATUS_DESC,
ACCOUNTING_ENTRY_TYPE_CODE,
ACCOUNTING_ENTRY_TYPE_DESC,
JE_CATEGORY_NAME,
JE_CATEGORY_DESC,
DESCRIPTION,
BALANCE_TYPE_CODE,
BALANCE_TYPE_DESC,
GL_PERIOD_NAME,
AE_LINE_NUM,
ACCOUNTING_DATE,
ACCOUNTING_CLASS_CODE,
ACCOUNTING_CLASS_CODE_DESC,
BUSINESS_CLASS_CODE,
BUSINESS_CLASS_CODE_DESC,
XLA_CCID,
XLA_ENTERED_DR,
XLA_ENTERED_CR,
XLA_ACCOUNTED_DR,
XLA_ACCOUNTED_CR,
LINE_DESC,
ACCOUNT_CODE,
ACCOUNT_DESCRIPTION,
LEDGER_ID,
LEDGER_NAME,
CURRENCY_CODE,
PERIOD_NAME,
PERIOD_YEAR,
CLOSING_STATUS,
OPERATING_UNIT,
LANGUAGE
from(
SELECT /*+ LEADING(xah) index(xte) */
    aia.invoice_id
    || '-'
    || xe.event_id
    || '-'
    || xe.event_number
    || '-'
    || xah.ae_header_id
    || '-'
    || xal.ae_line_num ecc_spec_id,
    'INV' AS record_type,
    aia.invoice_type_lookup_code,
    aia.org_id,
    aia.invoice_currency_code AS transaction_currency,
    aia.vendor_id,
    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,
    xe.application_id,
    xe.event_id,
    DECODE(xe.event_status_code,'Z','I',xe.event_status_code) AS 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 = DECODE(xe.event_status_code,'Z','I',xe.event_status_code)
    ) event_status_desc,
    DECODE(xe.process_status_code,'Z','I',xe.process_status_code) AS 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 = DECODE(xe.process_status_code,'Z','I',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,
    xal.displayed_line_number,
    xah.ae_header_id,
    xah.gl_transfer_status_code,
    (
        SELECT
            meaning
        FROM
            fnd_lookup_values
        WHERE
            view_application_id = 602
            AND   lookup_type = 'GL_TRANSFER_FLAG'
            AND   language = outl.language
            AND   lookup_code = xah.gl_transfer_status_code
    ) gl_transfer_flag_desc,
    xah.accounting_entry_status_code,
    (
        SELECT
            meaning
        FROM
            fnd_lookup_values
        WHERE
            view_application_id = 602
            AND   lookup_type = 'XLA_ACCOUNTING_ENTRY_STATUS'
            AND   language = outl.language
            AND   lookup_code = xah.accounting_entry_status_code
    ) acc_entry_status_desc,
    xah.accounting_entry_type_code,
    (
        SELECT
            meaning
        FROM
            fnd_lookup_values
        WHERE
            view_application_id = 602
            AND   lookup_type = 'XLA_ACCOUNTING_ENTRY_TYPE'
            AND   language = outl.language
            AND   lookup_code = xah.accounting_entry_type_code
    ) accounting_entry_type_desc,
    xah.je_category_name,
    (
        SELECT
            description
        FROM
            gl_je_categories_tl jct
        WHERE
            xah.je_category_name = jct.je_category_name
            AND   language = outl.language
    ) je_category_desc,
    xah.description,
    xah.balance_type_code,
    (
        SELECT
            meaning
        FROM
            fnd_lookup_values
        WHERE
            view_application_id = 602
            AND   lookup_type = 'XLA_BALANCE_TYPE'
            AND   language = outl.language
            AND   lookup_code = xah.balance_type_code
    ) balance_type_desc,
    xah.period_name gl_period_name,
    xal.ae_line_num,
    xal.accounting_date,
    xal.accounting_class_code,
    (
        SELECT
            meaning
        FROM
            fnd_lookup_values
        WHERE
            view_application_id = 602
            AND   lookup_type = 'XLA_ACCOUNTING_CLASS'
            AND   language = outl.language
            AND   lookup_code = xal.accounting_class_code
    ) accounting_class_code_desc,
    xal.business_class_code,
    (
        SELECT
            meaning
        FROM
            fnd_lookup_values
        WHERE
            view_application_id = 602
            AND   lookup_type = 'XLA_BUSINESS_FLOW_CLASS'
            AND   language = outl.language
            AND   lookup_code = xal.business_class_code
    ) business_class_code_desc,
    xal.code_combination_id xla_ccid,
    xal.entered_dr xla_entered_dr,
    xal.entered_cr xla_entered_cr,
    xal.accounted_dr xla_accounted_dr,
    xal.accounted_cr xla_accounted_cr,
    xal.description line_desc,
    DECODE(xal.code_combination_id,NULL,NULL,-1,NULL,fnd_flex_xml_publisher_apis.process_kff_combination_1(p_lexical_name => 'ACCOUNT CODE',p_application_short_name
=> 'SQLGL',p_id_flex_code => 'GL#',p_id_flex_num => lgr.chart_of_accounts_id,p_data_set => lgr.chart_of_accounts_id,p_ccid => xal.code_combination_id
,p_segments => 'GL_ACCOUNT',p_show_parent_segments => 'Y',p_output_type => 'VALUE') ) AS account_code,
    DECODE(xal.code_combination_id,NULL,NULL,-1,NULL,fnd_flex_xml_publisher_apis.process_kff_combination_1(p_lexical_name => 'ACCOUNT CODE',p_application_short_name
=> 'SQLGL',p_id_flex_code => 'GL#',p_id_flex_num => lgr.chart_of_accounts_id,p_data_set => lgr.chart_of_accounts_id,p_ccid => xal.code_combination_id
,p_segments => 'GL_ACCOUNT',p_show_parent_segments => 'Y',p_output_type => 'FULL_DESCRIPTION') ) AS account_description,
    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_supplier_sites_all pos,
    xla_events xe,
    xla_transaction_entities_upg xte,
    xla_ae_headers xah,
    xla_ae_lines xal,
    gl_ledgers lgr,
    gl_period_statuses ps,
    gl_code_combinations cc,
    hr_all_organization_units ou,
    hr_all_organization_units_tl outl,
    (
        SELECT
            ccid.code_combination_id,
            sav.id_flex_num chart_of_accounts_id
        FROM
            fnd_id_flex_segments s,
            fnd_segment_attribute_values sav,
            gl_code_combinations ccid
        WHERE
            s.application_id = 101
            AND   s.id_flex_code = 'GL#'
            AND   s.enabled_flag = 'Y'
            AND   sav.application_column_name = s.application_column_name
            AND   sav.application_id = 101
            AND   sav.id_flex_code = s.id_flex_code
            AND   sav.id_flex_num = s.id_flex_num
            AND   sav.attribute_value = 'Y'
            AND   sav.segment_attribute_type = 'GL_ACCOUNT'
            AND   ccid.chart_of_accounts_id = s.id_flex_num
    ) account_segments
WHERE
    ap.vendor_id = aia.vendor_id
    AND   aia.vendor_site_id = pos.vendor_site_id
    AND   xe.application_id = 200
    AND   xe.process_status_code <> 'U'
    AND   xte.application_id = 200
    AND   nvl(xte.source_id_int_1,-99) = aia.invoice_id
    AND   xte.entity_id = xah.entity_id
    AND   xte.ledger_id = aia.set_of_books_id
    AND   xe.entity_id = xte.entity_id
    AND   xah.application_id = xe.application_id
    AND   xah.event_id = xe.event_id
    AND   xal.ae_header_id = xah.ae_header_id
    AND   xal.application_id = xah.application_id
    AND   xah.zero_amount_flag = 'N'
    AND   xah.gl_transfer_status_code <> 'Y'
    AND   xal.displayed_line_number >= 0
    AND   ou.organization_id = aia.org_id
    AND   ou.organization_id = outl.organization_id
    AND   lgr.ledger_id = aia.set_of_books_id
    AND   ps.set_of_books_id = aia.set_of_books_id
    AND   ps.application_id = xe.application_id
    AND   aia.invoice_date BETWEEN ps.start_date AND ps.end_date
    AND   ps.closing_status = 'O'
    AND   cc.code_combination_id (+) = xal.code_combination_id
    AND   account_segments.code_combination_id (+) = cc.code_combination_id
 
    AND outl.language in ('US')
    ) temp
    )
    PIVOT
    ( max(OPERATING_UNIT) as OPERATING_UNIT, max(EVENT_STATUS_DESC) as EVENT_STATUS_DESC, max(PROCESS_STATUS_DESC) as PROCESS_STATUS_DESC,
    max(GL_TRANSFER_FLAG_DESC) as GL_TRANSFER_FLAG_DESC, max(ACC_ENTRY_STATUS_DESC) as ACC_ENTRY_STATUS_DESC,
    max(ACCOUNTING_ENTRY_TYPE_DESC) as ACCOUNTING_ENTRY_TYPE_DESC, max(BALANCE_TYPE_DESC) as BALANCE_TYPE_DESC, MAX(EVENT_TYPE_DESC) as EVENT_TYPE_DESC,
     max(JE_CATEGORY_DESC) as JE_CATEGORY_DESC, max(ACCOUNTING_CLASS_CODE_DESC) as ACCOUNTING_CLASS_CODE_DESC, max(BUSINESS_CLASS_CODE_DESC) as BUSINESS_CLASS_CODE_DESC
 
    For LANGUAGE in ('US' "US"))
) x
where
2=2