ECC Payables, AP Closing, SQL2

select
x.*
from
(
select * from (
select
ECC_SPEC_ID,
RECORD_TYPE,
LEDGER_ID,
LEDGER_NAME,
CURRENCY_CODE,
OPERATING_UNIT,
LANGUAGE,
PERIOD_NAME,
PERIOD_YEAR,
CLOSING_STATUS,
VENDOR_ID,
SUPPLIER_NUMBER,
SUPPLIER_NAME,
SITE_CODE,
ORG_ID,
CHECK_ID,
TRANSACTION_NUMBER,
transaction_currency,
TRANSACTION_DATE,
BANK_ACCOUNT_NAME,
PAYMENT_TYPE_FLAG,
PAYMENT_TYPE_DESC,
APPLICATION_ID, EVENT_ID,
EVENT_NUMBER, EVENT_STATUS_CODE, EVENT_STATUS_DESC, PROCESS_STATUS_CODE, PROCESS_STATUS_DESC,
EVENT_TYPE_CODE, EVENT_TYPE_DESC, 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, DISPLAYED_LINE_NUMBER,
LINE_DESC, ACCOUNT_CODE, ACCOUNT_DESCRIPTION
from
(SELECT
    c.CHECK_ID
    || '-'
    || xe.event_id
    || '-'
    || xe.event_number
    || '-'
    || xal.ae_header_id
    || '-'
    || xal.ae_line_num ECC_SPEC_ID,
    'PAY' AS record_type,
    gl.ledger_id,
    gl.name ledger_name,
    gl.currency_code,
    outl.name operating_unit,
    outl.language,
    ps.period_name,
    ps.period_year,
    ps.closing_status,
    DECODE(xal.party_type_code,'C',hz.party_number,po.VENDOR_ID) vendor_id,
    DECODE(xal.party_type_code,'C',hz.party_number,po.segment1) supplier_number,
    DECODE(xal.party_type_code,'C',hz.party_name,po.vendor_name) supplier_name,
    DECODE(xal.party_type_code,'C',hs.party_site_number,ps.vendor_site_code) site_code,
    c.org_id,
    c.check_id,
    c.check_number transaction_number,
    c.currency_code transaction_currency,
    c.check_date transaction_date,
    c.bank_account_name,
    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,
    xe.application_id,
    xe.event_id,
    xe.event_number,
    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,
    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
        jct.description
        FROM
        gl_je_categories_tl jct
        WHERE
        xah.je_category_name = jct.je_category_name
        AND jct.language = outl.language
    ) AS 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.displayed_line_number,
    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 => gl.chart_of_accounts_id,
             p_data_set => gl.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 => gl.chart_of_accounts_id,
             p_data_set => gl.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
FROM
    gl_period_statuses ps,
    gl_ledgers gl,
    ap_checks_all c,
    xla_ae_headers xah,
    xla_ae_lines xal,
    xla_transaction_entities_upg xte,
    xla_events xe,
    ap_suppliers po,
    ap_supplier_sites_all ps,
    hz_parties hz,
    hz_cust_accounts hca,
    hz_party_sites hs,
    hz_cust_acct_sites_all hcas,
    hz_cust_site_uses_all hczu,
    hr_all_organization_units ou,
    hr_all_organization_units_tl outl,
    gl_code_combinations ccid,
    (
        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
    po.vendor_id (+) = xal.party_id
    AND   ps.vendor_site_id (+) = xal.party_site_id
    AND   hz.party_id (+) = hca.party_id
    AND   hca.cust_account_id (+) = xal.party_id
    AND   hs.party_site_id (+) = hcas.party_site_id
    AND   hcas.cust_acct_site_id (+) = hczu.cust_acct_site_id
    AND   hczu.site_use_id (+) = xal.party_site_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 xte.application_id = 200
    AND xte.application_id = xe.application_id
    AND xte.entity_id = xe.entity_id
    AND xe.process_status_code <> 'U'
    AND xe.application_id = 200
    AND xte.source_id_int_1 = c.check_id
    AND xte.application_id = xah.application_id
    AND xte.entity_id = xah.entity_id
    AND NVL(XTE.SOURCE_ID_INT_1,-99)=c.CHECK_ID
    AND XTE.ENTITY_CODE ='AP_PAYMENTS'
    and xte.ledger_id = gl.ledger_id
    AND ps.adjustment_period_flag = 'N'
    AND c.CHECK_DATE BETWEEN ps.start_date AND ps.end_date
    AND ps.closing_status = 'O'
    AND ps.application_id = xe.application_id
    AND ps.set_of_books_id = xte.ledger_id
    AND ou.organization_id = c.org_id
    AND ou.organization_id = outl.organization_id
    AND   ccid.code_combination_id (+) = xal.code_combination_id
    AND   account_segments.code_combination_id (+) = ccid.code_combination_id
    AND   xal.application_id =200 
    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(PAYMENT_TYPE_DESC) as PAYMENT_TYPE_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