query

WITH


--1.6
company AS
 (
        SELECT DISTINCT
                        glh.pk1_value  AS child_value ,
                        fv.description AS description
                FROM
                        fnd_vs_typed_values_vl fv ,
                        gl_seg_val_hier_rf     glh,
                        fnd_tree_version       ftv
                WHERE
                        1                 =1
                AND     fv.value_set_code = 'Company UOB Ledger'
                AND     glh.tree_code     = 'Company UOB Ledger'
                AND     ftv.tree_code     = 'Company UOB Ledger'
                AND     SYSDATE BETWEEN ftv.effective_start_date AND     ftv.effective_end_date
                AND     ftv.tree_structure_code = 'GL_ACCT_FLEX'
                AND     ftv.TREE_VERSION_ID     = glh.TREE_VERSION_ID
                AND     glh.pk1_value           = fv.VALUE
                AND     glh.tree_structure_code = 'GL_ACCT_FLEX' -- 2.8
                AND     ftv.STATUS              = 'ACTIVE' -- 3.0
                AND     (
                                glh.ancestor_pk1_value IN (:P_COMPANY)
                        OR      least(:P_COMPANY) IS NULL)
 )
     ,   analysis AS
        (
                SELECT DISTINCT
                        glh.pk1_value  AS child_value ,
                        fv.description AS description
                FROM
                        fnd_vs_typed_values_vl fv ,
                        gl_seg_val_hier_rf     glh,
                        fnd_tree_version       ftv
                WHERE
                        1                 =1
                AND     fv.value_set_code = 'Analysis UOB Ledger'
                AND     glh.tree_code     = 'Analysis UOB Ledger'
                AND     ftv.tree_code     = 'Analysis UOB Ledger'
                AND     SYSDATE BETWEEN ftv.effective_start_date AND     ftv.effective_end_date
                AND     ftv.tree_structure_code = 'GL_ACCT_FLEX'
                AND     ftv.TREE_VERSION_ID     = glh.TREE_VERSION_ID
                AND     glh.pk1_value           = fv.VALUE
                AND     glh.tree_structure_code = 'GL_ACCT_FLEX' -- 2.8
                AND     ftv.STATUS              = 'ACTIVE' -- 3.0
                AND     (
                                glh.ancestor_pk1_value IN (:P_ANALYSIS)
                        OR      least(:P_ANALYSIS) IS NULL)
        )
        ,
        cost_centre AS
        (
                SELECT DISTINCT
                        glh.pk1_value  AS child_value ,
                        fv.description AS description
                FROM
                        fnd_vs_typed_values_vl fv ,
                        gl_seg_val_hier_rf     glh,
                        fnd_tree_version       ftv
                WHERE
                        1                 =1
                AND     fv.value_set_code = 'Cost Centre UOB Ledger'
                AND     glh.tree_code     = 'Cost Centre UOB Ledger'
                AND     ftv.tree_code     = 'Cost Centre UOB Ledger'
                AND     SYSDATE BETWEEN ftv.effective_start_date AND     ftv.effective_end_date
                AND     ftv.tree_structure_code = 'GL_ACCT_FLEX'
                AND     ftv.TREE_VERSION_ID     = glh.TREE_VERSION_ID
                AND     glh.pk1_value           = fv.VALUE
                AND     glh.tree_structure_code = 'GL_ACCT_FLEX' -- 2.8
                AND     ftv.STATUS              = 'ACTIVE' -- 3.0
                AND     (
                                glh.ancestor_pk1_value IN (:P_COST_CENTRE)
                        OR      least(:P_COST_CENTRE) IS NULL)
        )
        ,
        activity AS
        (
                SELECT DISTINCT
                        glh.pk1_value  AS child_value ,
                        fv.description AS description
                FROM
                        fnd_vs_typed_values_vl fv ,
                        gl_seg_val_hier_rf     glh,
                        fnd_tree_version       ftv
                WHERE
                        1                 =1
                AND     fv.value_set_code = 'Activity UOB Ledger'
                AND     glh.tree_code     = 'Activity UOB Ledger'
                AND     ftv.tree_code     = 'Activity UOB Ledger'
                AND     SYSDATE BETWEEN ftv.effective_start_date AND     ftv.effective_end_date
                AND     ftv.tree_structure_code = 'GL_ACCT_FLEX'
                AND     ftv.TREE_VERSION_ID     = glh.TREE_VERSION_ID
                AND     glh.pk1_value           = fv.VALUE
                AND     glh.tree_structure_code = 'GL_ACCT_FLEX' -- 2.8
                AND     ftv.STATUS              = 'ACTIVE' -- 3.0
                AND     (
                                glh.ancestor_pk1_value IN (:P_ACTIVITY)
                        OR      least(:P_ACTIVITY) IS NULL)
        )
        ,
        sof AS
        (
                SELECT DISTINCT
                        glh.pk1_value  AS child_value ,
                        fv.description AS description
                FROM
                        fnd_vs_typed_values_vl fv ,
                        gl_seg_val_hier_rf     glh,
                        fnd_tree_version       ftv
                WHERE
                        1                 =1
                AND     fv.value_set_code = 'Source of Funds UOB Ledger'
                AND     glh.tree_code     = 'Source of Funds UOB Ledger'
                AND     ftv.tree_code     = 'Source of Funds UOB Ledger'
                AND     SYSDATE BETWEEN ftv.effective_start_date AND     ftv.effective_end_date
                AND     ftv.tree_structure_code = 'GL_ACCT_FLEX'
                AND     ftv.TREE_VERSION_ID     = glh.TREE_VERSION_ID
                AND     glh.pk1_value           = fv.VALUE
                AND     glh.tree_structure_code = 'GL_ACCT_FLEX' -- 2.8
                AND     ftv.STATUS              = 'ACTIVE' -- 3.0
                AND     (
                                glh.ancestor_pk1_value IN (:P_SOF)
                        OR      least(:P_SOF) IS NULL)
        )
        ,
        code_combinations AS
        (
                SELECT
                        cc.code_combination_id,
                        cc.segment1           ,
                        cc.segment2           ,
                        cc.segment3           ,
                        cc.segment4           ,
                        cc.segment5
                FROM
                        gl_code_combinations cc,
                        company,
                        analysis,
                        cost_centre,
                        activity,
                        sof
                WHERE 1          = 1
                AND cc.segment1  = company.child_value
                AND cc.segment2  = analysis.child_value
                AND cc.segment3  = cost_centre.child_value
                AND cc.segment4  = activity.child_value
                AND cc.segment5  = sof.child_value
                      --  ENABLED_FLAG = 'Y'
        ),

        gl_data AS
         (
            SELECT
                glh.je_source                                       AS je_source,
                glh.ledger_id                                       AS ledger_id,
                glh.name                                            AS journal_header_name,
                glh.description                                     AS je_description,
                TO_CHAR(glh.posted_date, 'DD-MON-YYYY')             AS gl_posted_date,
                gll.description                                     AS gl_lines_description,
                gll.je_header_id                                    AS je_header_id,
                gll.je_line_num                                     AS je_line_num,
                NVL(gll.entered_dr,0)   - NVL(gll.entered_cr,0)     AS entered_currency_net,
                NVL(gll.accounted_dr,0) - NVL(gll.accounted_cr,0)   AS net_amount,
                gll.code_combination_id                             AS code_combination_id,
                glb.name                                            AS gl_batch_name,
                (SELECT user_je_source_name
                 FROM gl_je_sources gjs
                WHERE gjs.je_source_name = glh.je_source
                 AND ROWNUM = 1)                                    AS je_user_source_name,
                (SELECT user_je_category_name
                 FROM gl_je_categories gjc
                 WHERE gjc.je_category_name = glh.je_category
                 AND ROWNUM = 1)                                    AS je_user_category_name,
                 gll.currency_code                                  AS currency_code, -- 3.4
                 glh.REVERSED_JE_HEADER_ID -- 3.5
             FROM
                gl_je_headers     glh ,
                gl_je_lines       gll ,
                gl_je_batches     glb,
                code_combinations cc
             WHERE 1                      = 1
              AND glh.je_header_id        = gll.je_header_id
              AND gll.ledger_id           = glh.ledger_id
              AND glh.ledger_id           = :P_LEDGER_ID
              AND glh.je_batch_id         = glb.je_batch_id
              AND glh.period_name         = :P_PERIOD_NAME
              AND gll.period_name         = glh.period_name
              AND gll.code_combination_id = cc.code_combination_id
              AND glb.STATUS              = 'P' -- report only posted batches 3.2
              AND gll.currency_code       <> 'STAT' -- 3.3 exclude STAT journals / 3.4


         )
         ,
         xla AS
          (
            SELECT
                xal.gl_sl_link_id
               ,xal.code_combination_id
               ,xal.gl_sl_link_table
               ,xal.ae_header_id
               ,xal.ae_line_num
               ,NVL(xal.entered_dr,0)   - NVL(xal.entered_cr,0)   AS entered_currency_net
               ,NVL(xal.accounted_dr,0) - NVL(xal.accounted_cr,0) AS net_amount
               ,xah.ledger_id
               ,xal.accounting_class_code
               ,nvl(xte.source_id_int_1,-99) AS source_id_int_1 -- 2.9
               ,xet.name
               ,xte.entity_code
               ,xte.application_id
               ,xah.event_id
               ,xte.legal_entity_id
               ,xal.description AS line_description
             FROM
              xla_ae_headers           xah ,
              xla_ae_lines             xal ,
              xla_transaction_entities xte ,
              xla_events               xev ,
              xla_event_types_vl       xet
          WHERE 1                   = 1
           AND xah.ae_header_id     = xal.ae_header_id
           AND xal.application_id   = xet.application_id -- add to hit index XLA_AE_HEADERS_U1 which is unique
           AND xah.entity_id        = xte.entity_id
           AND xah.event_id         = xev.event_id
           AND xev.entity_id        = xte.entity_id
           AND xev.event_type_code  = xet.event_type_code
           AND xev.application_id   = xet.application_id
           -- 2.9
           AND xah.period_name      = :P_PERIOD_NAME
           AND xah.ledger_id        = :P_LEDGER_ID
           AND xte.ledger_id        = :P_LEDGER_ID
           AND xah.application_id   = xal.application_id
          )

SELECT  /*+ PARALLEL(journals 5) */
        analysis_level_1      ,
        segment2              ,
        segment3              ,
        segment4              ,
        segment5              ,
        accounting_period     ,
        accounting_date       ,
        transaction_number    ,
        journal_source        ,
        journal_category      ,
        journal_batch         ,
        journal_header_name   ,
        journal_line_number   ,
        gl_header_description ,
        transaction_type_name ,
        event_type            ,
        gl_line_descripton    ,
        trx_description       ,
        accounting_class      ,
        party_name            ,
        reference             ,
        transaction_date      ,
        currency_code         ,
        journals_po_number    ,
        entered_currency_net  ,
        net_amount            ,
        CEIL(ROWNUM/65000) sheet_no     --2.6
FROM
        (
                --- payables
                SELECT
                        (
                                SELECT
                                        ia.description
                                FROM
                                        fnd_vs_typed_values_vl ia,
                                        gl_seg_val_hier_rf     gh,
                                        fnd_tree_version       ftv
                                WHERE
                                        ia.VALUE          = gh.ancestor_pk1_value
                                AND     gh.pk1_value      = cc.segment2
                                AND     ia.value_set_code = 'Analysis UOB Ledger'
                                AND     gh.tree_code      = 'Analysis UOB Ledger'
                                AND     ftv.tree_code     = ia.value_set_code
                                AND     SYSDATE BETWEEN ftv.effective_start_date AND     ftv.effective_end_date
                                AND     ftv.tree_structure_code = 'GL_ACCT_FLEX'
                                AND     ftv.tree_structure_code = gh.tree_structure_code
                                AND     ftv.TREE_VERSION_ID     = gh.TREE_VERSION_ID
                                AND     gh.distance             = 1)      AS analysis_level_1      ,
                        cc.segment2                                       AS segment2              ,
                        cc.segment3                                       AS segment3              ,
                        cc.segment4                                       AS segment4              ,
                        cc.segment5                                       AS segment5              ,
                        :P_PERIOD_NAME                                    AS accounting_period     ,
                        gl_data.gl_posted_date                            AS accounting_date       ,
                        aia.invoice_num                                   AS transaction_number    ,
                        gl_data.je_user_source_name                       AS journal_source        ,
                        gl_data.je_user_category_name                     AS journal_category      ,
                        gl_data.gl_batch_name                             AS journal_batch         ,
                        gl_data.journal_header_name                       AS journal_header_name   ,
                        gl_data.je_line_num                               AS journal_line_number   ,
                        gl_data.je_description                            AS gl_header_description ,
                        aia.invoice_type_lookup_code                      AS transaction_type_name ,
                        xla.name                                          AS event_type            ,
                        xla.line_description                              AS gl_line_descripton    ,
                        aia.description                                   AS trx_description       ,
                        xla.accounting_class_code                         AS accounting_class      ,
                        hp.party_name                                     AS party_name            ,
                        ''                                                AS reference             ,
                        TO_CHAR(aia.invoice_date, 'DD-MON-YYYY')          AS transaction_date      ,
                        aia.invoice_currency_code                         AS currency_code         ,
                        ''                                                AS journals_po_number    , -- 2.1
                        xla.entered_currency_net                          AS entered_currency_net  , -- 1.5 / 1.7
                        xla.net_amount                                    AS net_amount              -- 1.5 / 1.7
                FROM
                         code_combinations              cc
                        ,gl_data
                        ,gl_import_references           gir
                        ,xla
                        ,ap_invoices_all                aia
                        --,ap_invoice_distributions_all   aid -- 1.8
                        ,hz_parties                     hp
                WHERE
                        1                                 =1
                AND     gl_data.je_source                 = 'Payables'
                AND     gl_data.je_header_id              = gir.je_header_id
                AND     gir.je_line_num                   = gl_data.je_line_num
                AND     aia.set_of_books_id               = gl_data.ledger_id
                AND     xla.ledger_id                     = gl_data.ledger_id
                AND     xla.gl_sl_link_id                 = gir.gl_sl_link_id
                AND     gir.gl_sl_link_table              = xla.gl_sl_link_table
                AND     cc.code_combination_id            = xla.code_combination_id
             --   1.9
              --  AND   aia.invoice_id                    = aid.invoice_id
                AND     aia.invoice_id                    = xla.source_id_int_1
                --AND   aid.dist_code_combination_id      = cc.code_combination_id
                --AND   aid.accounting_event_id           = xla.event_id
                -- 1.9
                AND     aia.party_id                      = hp.party_id
                -- 3.1
                AND  xla.entity_code                      = 'AP_INVOICES'
                -- 3.1
                AND  gl_data.REVERSED_JE_HEADER_ID IS NULL -- 3.5

                UNION ALL -- 2.9

                --- payables - AP Payments
                SELECT


                (
                                SELECT
                                        ia.description
                                FROM
                                        fnd_vs_typed_values_vl ia,
                                        gl_seg_val_hier_rf     gh,
                                        fnd_tree_version       ftv
                                WHERE
                                        ia.VALUE          = gh.ancestor_pk1_value
                                AND     gh.pk1_value      = cc.segment2
                                AND     ia.value_set_code = 'Analysis UOB Ledger'
                                AND     gh.tree_code      = 'Analysis UOB Ledger'
                                AND     ftv.tree_code     = ia.value_set_code
                                AND     SYSDATE BETWEEN ftv.effective_start_date AND     ftv.effective_end_date
                                AND     ftv.tree_structure_code = 'GL_ACCT_FLEX'
                                AND     ftv.tree_structure_code = gh.tree_structure_code
                                AND     ftv.TREE_VERSION_ID     = gh.TREE_VERSION_ID
                                AND     gh.distance             = 1)      AS analysis_level_1      ,
                        cc.segment2                                       AS segment2              ,
                        cc.segment3                                       AS segment3              ,
                        cc.segment4                                       AS segment4              ,
                        cc.segment5                                       AS segment5              ,
                        :P_PERIOD_NAME                                    AS accounting_period     ,
                        gl_data.gl_posted_date                            AS accounting_date       ,
                        to_char(aca.check_number)                         AS transaction_number    ,
                        gl_data.je_user_source_name                       AS journal_source        ,
                        gl_data.je_user_category_name                     AS journal_category      ,
                        gl_data.gl_batch_name                             AS journal_batch         ,
                        gl_data.journal_header_name                       AS journal_header_name   ,
                        gl_data.je_line_num                               AS journal_line_number   ,
                        gl_data.je_description                            AS gl_header_description ,
                        'Payment'                                         AS transaction_type_name ,
                        xla.name                                          AS event_type            ,
                        xla.line_description                              AS gl_line_descripton    ,
                        aca.description                                   AS trx_description       ,
                        xla.accounting_class_code                         AS accounting_class      ,
                        hp.party_name                                     AS party_name            ,
                        ''                                                AS reference             ,
                        TO_CHAR(aca.check_date, 'DD-MON-YYYY')            AS transaction_date      ,
                        aca.currency_code                                 AS currency_code         ,
                        ''                                                AS journals_po_number    , -- 2.1
                        xla.entered_currency_net                          AS entered_currency_net  , -- 1.5 / 1.7
                        xla.net_amount                                    AS net_amount              -- 1.5 / 1.7
                FROM
                         code_combinations              cc
                        ,gl_data
                        ,gl_import_references           gir
                        ,xla
                        ,ap_checks_all                  aca
                        --,ap_invoice_payments_all       aipa
                    --  ,ap_invoices_all                 aia
                        ,hz_parties                     hp
                WHERE
                        1                                 =1
                AND     gl_data.je_source                 = 'Payables'
                AND     gl_data.je_header_id              = gir.je_header_id
                AND     gir.je_line_num                   = gl_data.je_line_num
                --AND     aipa.set_of_books_id              = gl_data.ledger_id
                AND     xla.ledger_id                     = gl_data.ledger_id
                AND     xla.gl_sl_link_id                 = gir.gl_sl_link_id
                AND     gir.gl_sl_link_table              = xla.gl_sl_link_table
                AND     cc.code_combination_id            = xla.code_combination_id
                AND     aca.check_id                      = xla.source_id_int_1
            --  and     aipa.check_id                     = aca.check_id
            --  and     aipa.invoice_id                   = aia.invoice_id
                AND     aca.party_id                      = hp.party_id
                AND     xla.entity_code                   = 'AP_PAYMENTS'
                AND  gl_data.REVERSED_JE_HEADER_ID IS NULL -- 3.5


                UNION ALL


                -- Receivables
                SELECT
                        (
                                SELECT
                                        ia.description
                                FROM
                                        fnd_vs_typed_values_vl ia,
                                        gl_seg_val_hier_rf     gh,
                                        fnd_tree_version       ftv
                                WHERE
                                        ia.VALUE          = gh.ancestor_pk1_value
                                AND     gh.pk1_value      = cc.segment2
                                AND     ia.value_set_code = 'Analysis UOB Ledger'
                                AND     gh.tree_code      = 'Analysis UOB Ledger'
                                AND     ftv.tree_code     = ia.value_set_code
                                AND     SYSDATE BETWEEN ftv.effective_start_date AND     ftv.effective_end_date
                                AND     ftv.tree_structure_code = 'GL_ACCT_FLEX'
                                AND     ftv.tree_structure_code = gh.tree_structure_code
                                AND     ftv.TREE_VERSION_ID     = gh.TREE_VERSION_ID
                                AND     gh.distance             = 1)      AS analysis_level_1      ,
                        cc.segment2                                       AS segment2              ,
                        cc.segment3                                       AS segment3              ,
                        cc.segment4                                       AS segment4              ,
                        cc.segment5                                       AS segment5              ,
                        :P_PERIOD_NAME                                    AS accounting_period     ,
                        gl_data.gl_posted_date                            AS accounting_date       ,
                        rct.trx_number                                    AS transaction_number    ,
                        gl_data.je_user_source_name                       AS journal_source        ,
                        gl_data.je_user_category_name                     AS journal_category      ,
                        gl_data.gl_batch_name                             AS journal_batch         ,
                        gl_data.journal_header_name                       AS journal_header_name   ,
                        gl_data.je_line_num                               AS journal_line_number   ,
                        gl_data.je_description                            AS gl_header_description ,
                        rctta.name                                        AS transaction_type_name ,
                        xla.name                                          AS event_type            ,
                        xla.line_description                              AS gl_line_descripton    ,
                        ''                                                AS trx_description       ,
                        xla.accounting_class_code                         AS accounting_class      ,
                        hp.party_name                                     AS party_name            ,
                        ''                                                AS REFERENCE             ,
                        TO_CHAR(rct.trx_date , 'DD-MON-YYYY')             AS transaction_date      ,
                        rct.invoice_currency_code                         AS currency_code         ,
                        ''                                                AS journals_po_number    , -- 2.1
                        xla.entered_currency_net                          AS entered_currency_net  , -- 1.5 / 1.7
                        xla.net_amount                                    AS net_amount              -- 1.5 / 1.7
                FROM
                         code_combinations             cc
                        ,gl_data
                        ,gl_import_references          gir
                        ,xla
                        ,ra_customer_trx_all           rct
                        ,ra_cust_trx_types_all         rctta
                        ,hz_parties                    hp
                        ,hz_cust_accounts              ca
                        --2.8
                      /*,ra_customer_trx_lines_all     ctl
                       ,ra_cust_trx_line_gl_dist_all  ctld
                        ,xla_distribution_links        xdl
                      */
                        --2.8
                WHERE
                        1                                = 1
                AND     gl_data.je_source                = 'Receivables'
                AND     gl_data.je_header_id             = gir.je_header_id
                AND     gir.je_line_num                  = gl_data.je_line_num
                AND     xla.ledger_id                    = gl_data.ledger_id
                AND     xla.gl_sl_link_id                = gir.gl_sl_link_id
                AND     gir.gl_sl_link_table             = xla.gl_sl_link_table
                AND     cc.code_combination_id           = xla.code_combination_id
                AND     rct.cust_trx_type_seq_id         = rctta.cust_trx_type_seq_id
                AND     xla.source_id_int_1              = rct.customer_trx_id
                AND     xla.application_id               = 222
                AND     rct.bill_to_customer_id          = ca.cust_account_id
                AND     ca.party_id                      = hp.party_id
                AND     xla.entity_code                  NOT IN ('ADJUSTMENTS','RECEIPTS') -- 3.0
                 -- 2.8
            /*  AND     rct.customer_trx_id              = ctld.customer_trx_id
                AND     rct.customer_trx_id              = ctl.customer_trx_id
                AND     ctld.customer_trx_line_id        = ctl.customer_trx_line_id
                AND     ctl.line_type                    = 'LINE'
                AND     cc.code_combination_id           = ctld.code_combination_id
                AND     xdl.source_distribution_id_num_1 = ctld.cust_trx_line_gl_dist_id
                AND     xla.ae_header_id                 = xdl.ae_header_id
                AND     xla.ae_line_num                  = xdl.ae_line_num
            */
                -- 2.8
                AND  gl_data.REVERSED_JE_HEADER_ID IS NULL -- 3.5


                UNION ALL -- 2.9
                -- Receivables - Adjustments
                SELECT
                        (
                                SELECT
                                        ia.description
                                FROM
                                        fnd_vs_typed_values_vl ia,
                                        gl_seg_val_hier_rf     gh,
                                        fnd_tree_version       ftv
                                WHERE
                                        ia.VALUE          = gh.ancestor_pk1_value
                                AND     gh.pk1_value      = cc.segment2
                                AND     ia.value_set_code = 'Analysis UOB Ledger'
                                AND     gh.tree_code      = 'Analysis UOB Ledger'
                                AND     ftv.tree_code     = ia.value_set_code
                                AND     SYSDATE BETWEEN ftv.effective_start_date AND     ftv.effective_end_date
                                AND     ftv.tree_structure_code = 'GL_ACCT_FLEX'
                                AND     ftv.tree_structure_code = gh.tree_structure_code
                                AND     ftv.TREE_VERSION_ID     = gh.TREE_VERSION_ID
                                AND     gh.distance             = 1)      AS analysis_level_1      ,
                        cc.segment2                                       AS segment2              ,
                        cc.segment3                                       AS segment3              ,
                        cc.segment4                                       AS segment4              ,
                        cc.segment5                                       AS segment5              ,
                        :P_PERIOD_NAME                                    AS accounting_period     ,
                        gl_data.gl_posted_date                            AS accounting_date       ,
                        ada.adjustment_number                             AS transaction_number    ,
                        gl_data.je_user_source_name                       AS journal_source        ,
                        gl_data.je_user_category_name                     AS journal_category      ,
                        gl_data.gl_batch_name                             AS journal_batch         ,
                        gl_data.journal_header_name                       AS journal_header_name   ,
                        gl_data.je_line_num                               AS journal_line_number   ,
                        xla.line_description                              AS gl_header_description ,
                        rctta.name                                        AS transaction_type_name ,
                        xla.name                                          AS event_type            ,
                        gl_data.gl_lines_description                      AS gl_line_descripton    ,
                        ''                                                AS trx_description       ,
                        xla.accounting_class_code                         AS accounting_class      ,
                        hp.party_name                                     AS party_name            ,
                        ''                                                AS REFERENCE             ,
                        TO_CHAR(rct.trx_date , 'DD-MON-YYYY')             AS transaction_date      ,
                        rct.invoice_currency_code                         AS currency_code         ,
                        ''                                                AS journals_po_number    , -- 2.1
                        xla.entered_currency_net                          AS entered_currency_net  , -- 1.5 / 1.7
                        xla.net_amount                                    AS net_amount              -- 1.5 / 1.7
                FROM
                         code_combinations             cc
                        ,gl_data
                        ,gl_import_references          gir
                        ,xla
                        ,ra_customer_trx_all           rct
                        ,ra_cust_trx_types_all         rctta
                        ,hz_parties                    hp
                        ,hz_cust_accounts              ca
                   --     ra_customer_trx_lines_all     ctl,
                        ,ar_adjustments_all            ada

                WHERE
                        1                                = 1
                AND     gl_data.je_source                = 'Receivables'
                AND     gl_data.je_header_id             = gir.je_header_id
                AND     gir.je_line_num                  = gl_data.je_line_num
                AND     xla.ledger_id                    = gl_data.ledger_id
                AND     xla.gl_sl_link_id                = gir.gl_sl_link_id
                AND     gir.gl_sl_link_table             = xla.gl_sl_link_table
                AND     cc.code_combination_id           = xla.code_combination_id
--                AND     rct.customer_trx_id              = ctl.customer_trx_id
                AND     rct.cust_trx_type_seq_id         = rctta.cust_trx_type_seq_id
                --AND     ctl.line_type                    = 'LINE'
                AND     xla.source_id_int_1              = ada.adjustment_id
                AND     xla.entity_code                  = 'ADJUSTMENTS'
                AND     ada.customer_trx_id              = rct.customer_trx_id
                AND     xla.application_id               = 222
                AND     rct.bill_to_customer_id          = ca.cust_account_id
                AND     ca.party_id                      = hp.party_id
                AND  gl_data.REVERSED_JE_HEADER_ID IS NULL -- 3.5

                UNION ALL -- 2.9
                -- Receivables - misc receipts
                -- 2.3

                SELECT
                    (
                            SELECT
                                    ia.description
                            FROM
                                    fnd_vs_typed_values_vl ia,
                                    gl_seg_val_hier_rf     gh,
                                    fnd_tree_version       ftv
                            WHERE
                                    ia.VALUE          = gh.ancestor_pk1_value
                            AND     gh.pk1_value      = cc.segment2
                            AND     ia.value_set_code = 'Analysis UOB Ledger'
                            AND     gh.tree_code      = 'Analysis UOB Ledger'
                            AND     ftv.tree_code     = ia.value_set_code
                            AND     SYSDATE BETWEEN ftv.effective_start_date AND     ftv.effective_end_date
                            AND     ftv.tree_structure_code = 'GL_ACCT_FLEX'
                            AND     ftv.tree_structure_code = gh.tree_structure_code
                            AND     ftv.TREE_VERSION_ID     = gh.TREE_VERSION_ID
                            AND     gh.distance             = 1)      AS analysis_level_1      ,
                    cc.segment2                                       AS segment2              ,
                    cc.segment3                                       AS segment3              ,
                    cc.segment4                                       AS segment4              ,
                    cc.segment5                                       AS segment5              ,
                    :P_PERIOD_NAME                                    AS accounting_period     ,
                    gl_data.gl_posted_date                            AS accounting_date       ,
                    acra.receipt_number                               AS transaction_number    ,
                    gl_data.je_user_source_name                       AS journal_source        ,
                    gl_data.je_user_category_name                     AS journal_category      ,
                    gl_data.gl_batch_name                             AS journal_batch         ,
                    gl_data.journal_header_name                       AS journal_header_name   ,
                    gl_data.je_line_num                               AS journal_line_number   ,
                    xla.line_description                              AS gl_header_description ,
                    acra.TYPE                                         AS transaction_type_name ,
                    xla.name                                          AS event_type            ,
                    gl_data.gl_lines_description                      AS gl_line_descripton    ,
                    ''                                                AS trx_description       ,
                    xla.accounting_class_code                         AS accounting_class      ,
                    ''                                                AS party_name            ,
                    ''                                                AS REFERENCE             ,
                    TO_CHAR(acra.receipt_date , 'DD-MON-YYYY')        AS transaction_date      ,
                    acra.currency_code                                AS currency_code         ,
                    ''                                                AS journals_po_number    , -- 2.1
                    xla.entered_currency_net                          AS entered_currency_net  , -- 1.5 / 1.7
                    xla.net_amount                                    AS net_amount              -- 1.5 / 1.7
            FROM
                     code_combinations             cc
                    ,gl_data
                    ,gl_import_references          gir
                    ,xla
                    ,ar_cash_receipts_all          acra

            WHERE
                    1                                = 1
            AND     gl_data.je_source                = 'Receivables'
            AND     gl_data.je_header_id             = gir.je_header_id
            AND     gir.je_line_num                  = gl_data.je_line_num
            AND     xla.ledger_id                    = gl_data.ledger_id
            AND     xla.gl_sl_link_id                = gir.gl_sl_link_id
            AND     gir.gl_sl_link_table             = xla.gl_sl_link_table
            AND     cc.code_combination_id           = xla.code_combination_id
            AND     xla.source_id_int_1              = acra.cash_receipt_id
            AND     xla.entity_code                  = 'RECEIPTS'
            AND     xla.application_id               = 222
            AND  gl_data.REVERSED_JE_HEADER_ID IS NULL -- 3.5

                UNION ALL -- 2.9

                -- manual and spreadsheet
                SELECT DISTINCT -- 3.6
                        (
                                SELECT
                                        ia.description
                                FROM
                                        fnd_vs_typed_values_vl ia,
                                        gl_seg_val_hier_rf     gh,
                                        fnd_tree_version       ftv
                                WHERE
                                        ia.VALUE          = gh.ancestor_pk1_value
                                AND     gh.pk1_value      = cc.segment2
                                AND     ia.value_set_code = 'Analysis UOB Ledger'
                                AND     gh.tree_code      = 'Analysis UOB Ledger'
                                AND     ftv.tree_code     = ia.value_set_code
                                AND     SYSDATE BETWEEN ftv.effective_start_date AND     ftv.effective_end_date
                                AND     ftv.tree_structure_code = 'GL_ACCT_FLEX'
                                AND     ftv.tree_structure_code = gh.tree_structure_code
                                AND     ftv.TREE_VERSION_ID     = gh.TREE_VERSION_ID
                                AND     gh.distance             = 1)      AS analysis_level_1      ,
                        cc.segment2                                       AS segment2              ,
                        cc.segment3                                       AS segment3              ,
                        cc.segment4                                       AS segment4              ,
                        cc.segment5                                       AS segment5              ,
                        :P_PERIOD_NAME                                    AS accounting_period     ,
                        gl_data.gl_posted_date                            AS accounting_date       ,
                        NULL                                              AS transaction_number    ,
                        gl_data.je_user_source_name                       AS journal_source        ,
                        gl_data.je_user_category_name                     AS journal_category      ,
                        gl_data.gl_batch_name                             AS journal_batch         ,
                        gl_data.journal_header_name                       AS journal_header_name   ,
                        gl_data.je_line_num                               AS journal_line_number   ,
                        gl_data.je_description                            AS gl_header_description ,
                        NULL                                              AS transaction_type_name ,
                        NULL                                              AS event_type            ,
                        gl_data.gl_lines_description                      AS gl_line_descripton    ,
                        NULL                                              AS trx_description       ,
                        NULL                                              AS accounting_class      ,
                        NULL                                              AS party_name            ,
                        NULL                                              AS REFERENCE             ,
                        NULL                                              AS transaction_date      ,
                        gl_data.currency_code                             AS currency_code         ,
                        ''                                                AS journals_po_number            , -- 2.1
                        gl_data.entered_currency_net                      AS entered_currency_net  , -- 1.5
                        gl_data.net_amount                                AS net_amount              -- 1.5
                FROM
                        code_combinations cc
                       ,gl_data
                WHERE
                        1                = 1
                AND    ( (gl_data.je_source NOT IN ('Receivables','Payables', 'Receipt Accounting'))  OR
                         (gl_data.je_source IN ('Receivables','Payables', 'Receipt Accounting')
                         AND NOT EXISTS (
                                            SELECT 1 FROM gl_import_references gir
                                             WHERE  gir.je_line_num        = gl_data.je_line_num
                                                AND gl_data.je_header_id   = gir.je_header_id
                                        )
                         )
                       )
                -- 1.8 / 3.6
                AND     cc.code_combination_id = gl_data.code_combination_id
                AND  gl_data.REVERSED_JE_HEADER_ID IS NULL -- 3.5

                UNION ALL -- 2.9

--1.8 Receipt Accounting
                SELECT

                        (
                                SELECT
                                        ia.description
                                FROM
                                        fnd_vs_typed_values_vl ia,
                                        gl_seg_val_hier_rf     gh,
                                        fnd_tree_version       ftv
                                WHERE
                                        ia.VALUE          = gh.ancestor_pk1_value
                                AND     gh.pk1_value      = cc.segment2
                                AND     ia.value_set_code = 'Analysis UOB Ledger'
                                AND     gh.tree_code      = 'Analysis UOB Ledger'
                                AND     ftv.tree_code     = ia.value_set_code
                                AND     SYSDATE BETWEEN ftv.effective_start_date AND     ftv.effective_end_date
                                AND     ftv.tree_structure_code = 'GL_ACCT_FLEX'
                                AND     ftv.tree_structure_code = gh.tree_structure_code
                                AND     ftv.TREE_VERSION_ID     = gh.TREE_VERSION_ID
                                AND     gh.distance             = 1)      AS analysis_level_1      ,
                        cc.segment2                                       AS segment2              ,
                        cc.segment3                                       AS segment3              ,
                        cc.segment4                                       AS segment4              ,
                        cc.segment5                                       AS segment5              ,
                        :P_PERIOD_NAME                                    AS accounting_period     ,
                        gl_data.gl_posted_date                            AS accounting_date       ,
                        rcpt_data.receipt_number                          AS transaction_number    ,
                        gl_data.je_user_source_name                       AS journal_source        ,
                        gl_data.je_user_category_name                     AS journal_category      ,
                        gl_data.gl_batch_name                             AS journal_batch         ,
                        gl_data.journal_header_name                       AS journal_header_name   ,
                        gl_data.je_line_num                               AS journal_line_number   ,
                        gl_data.je_description                            AS gl_header_description ,
                        rcpt_data.transaction_type                        AS transaction_type_name ,
                        xla.name                                          AS event_type            ,
                        xla.line_description                              AS gl_line_descripton    ,
                        rcpt_data.description                             AS trx_description       ,
                        xla.accounting_class_code                         AS accounting_class      ,
                        rcpt_data.party_unique_name                       AS party_name            ,
                        ''                                                AS REFERENCE             ,
                        TO_CHAR(rcpt_data.transaction_date,'DD-MON-YYYY') AS transaction_date      ,
                        /*rcpt_data.entered_currency_code*/ ''            AS currency_code         ,
                        rcpt_data.po_number                               AS journals_po_number    , -- 2.1
                        xla.entered_currency_net                          AS entered_currency_net  , -- 1.5 / 1.7
                        xla.net_amount                                    AS net_amount              -- 1.5 / 1.7

                FROM
                         code_combinations            cc
                        ,gl_data
                        ,gl_import_references         gir
                        ,xla
                        ,xla_distribution_links       xdl
                        ,(SELECT
                          hp.party_unique_name,
                          cre.accounting_event_id,
                         -- crd.accounting_line_type,
                          cre.event_id                  AS event_id,
                          cre.ledger_id                 AS ledger_id,
                          cre.legal_entity_id           AS legal_entity_id,
                          crt.transaction_type,
                          crt.transaction_date,
                         -- crd.entered_currency_code,
                          crt.receipt_number,
                          pl.item_description           AS description,
                          crt.cmr_rcv_transaction_id    AS transaction_id,
                          h.segment1                    AS po_number        --2.1
                       FROM
                          --cmr_rcv_distributions       crd,
                          cmr_rcv_events                cre,
                          fun_all_business_units_v      bu,
                          cmr_rcv_transactions          crt ,
                          po_headers_all                h,
                          po_distributions              d,
                          po_lines_all                  pl, -- 3.2
                        --  por_requisition_headers_all     prh,
                        --  por_requisition_lines_all   prl,
                        --  por_req_distributions_all   prd,
                          poz_suppliers                 ps,
                          hz_parties                    hp
                       WHERE 1                          = 1
                         AND cre.business_unit_id       = bu.bu_id
                         --AND crd.accounting_event_id  = cre.accounting_event_id
                         AND cre.cmr_rcv_transaction_id = crt.cmr_rcv_transaction_id
                         AND d.po_header_id             = h.po_header_id
                         AND d.PO_DISTRIBUTION_ID       = crt.PO_DISTRIBUTION_ID
                         AND ps.party_id                = hp.party_id
                         AND h.vendor_id                = ps.vendor_id
                         AND h.po_header_id             = pl.po_header_id
                         AND pl.po_line_id              = d.po_line_id
                        -- and d.REQ_DISTRIBUTION_ID        = prd.DISTRIBUTION_ID
                        -- and prl.requisition_line_id  = prd.requisition_line_id
                        -- and prh.requisition_header_id    = prl.requisition_header_id
                        ) rcpt_data
                WHERE
                        1                                = 1
                AND     gl_data.je_source                = 'Receipt Accounting'
                AND     gl_data.je_header_id             = gir.je_header_id
                AND     gir.je_line_num                  = gl_data.je_line_num
                AND     xla.ledger_id                    = gl_data.ledger_id
                AND     xla.gl_sl_link_id                = gir.gl_sl_link_id
                AND     gir.gl_sl_link_table             = xla.gl_sl_link_table
                AND     cc.code_combination_id           = xla.code_combination_id
                AND     xla.ledger_id                    = rcpt_data.ledger_id
                AND     xla.event_id                     = rcpt_data.event_id
                AND    ( xla.source_id_int_1              = rcpt_data.transaction_id  OR xla.source_id_int_1 =  rcpt_data.accounting_event_id)
                --AND   xla.accounting_class_code        = rcpt_data.accounting_line_type
                AND     xla.legal_entity_id              = rcpt_data.legal_entity_id
                AND     xla.ae_header_id                 = xdl.ae_header_id
                AND     xla.ae_line_num                  = xdl.ae_line_num
    --          AND     glh.currency_code                = :P_CURRENCY_CODE -- 1.9
                AND  gl_data.REVERSED_JE_HEADER_ID IS NULL -- 3.5

                UNION ALL -- 2.9

                -- reversal journals -- 3.5
                SELECT
                        (
                                SELECT
                                        ia.description
                                FROM
                                        fnd_vs_typed_values_vl ia,
                                        gl_seg_val_hier_rf     gh,
                                        fnd_tree_version       ftv
                                WHERE
                                        ia.VALUE          = gh.ancestor_pk1_value
                                AND     gh.pk1_value      = cc.segment2
                                AND     ia.value_set_code = 'Analysis UOB Ledger'
                                AND     gh.tree_code      = 'Analysis UOB Ledger'
                                AND     ftv.tree_code     = ia.value_set_code
                                AND     SYSDATE BETWEEN ftv.effective_start_date AND     ftv.effective_end_date
                                AND     ftv.tree_structure_code = 'GL_ACCT_FLEX'
                                AND     ftv.tree_structure_code = gh.tree_structure_code
                                AND     ftv.TREE_VERSION_ID     = gh.TREE_VERSION_ID
                                AND     gh.distance             = 1)      AS analysis_level_1      ,
                        cc.segment2                                       AS segment2              ,
                        cc.segment3                                       AS segment3              ,
                        cc.segment4                                       AS segment4              ,
                        cc.segment5                                       AS segment5              ,
                        :P_PERIOD_NAME                                    AS accounting_period     ,
                        gl_data.gl_posted_date                            AS accounting_date       ,
                        NULL                                              AS transaction_number    ,
                        gl_data.je_user_source_name                       AS journal_source        ,
                        gl_data.je_user_category_name                     AS journal_category      ,
                        gl_data.gl_batch_name                             AS journal_batch         ,
                        gl_data.journal_header_name                       AS journal_header_name   ,
                        gl_data.je_line_num                               AS journal_line_number   ,
                        gl_data.je_description                            AS gl_header_description ,
                        NULL                                              AS transaction_type_name ,
                        NULL                                              AS event_type            ,
                        gl_data.gl_lines_description                      AS gl_line_descripton    ,
                        NULL                                              AS trx_description       ,
                        NULL                                              AS accounting_class      ,
                        NULL                                              AS party_name            ,
                        NULL                                              AS REFERENCE             ,
                        NULL                                              AS transaction_date      ,
                        gl_data.currency_code                             AS currency_code         ,
                        ''                                                AS journals_po_number            , -- 2.1
                        gl_data.entered_currency_net                      AS entered_currency_net  , -- 1.5
                        gl_data.net_amount                                AS net_amount              -- 1.5
                FROM
                        code_combinations cc
                       ,gl_data
                WHERE
                        1                = 1
                AND     cc.code_combination_id = gl_data.code_combination_id
                AND  gl_data.REVERSED_JE_HEADER_ID IS NOT NULL -- 3.5

                ) journals

 WHERE 1      =1
/*
ORDER BY
        segment3,
        segment5,
        segment4,
        analysis_level_1*/