Drill Down Query From GL into Project Accounting subledger

The below query will  provide the complete breakup of posted Journals with transaction distribution wise breakup details of Project Accounting sub-ledger modules.

SELECT      GLCC.concatenated_segments segments

                    ,GJL.period_name

                    ,GJH.name journal_name

                    ,GJB.name batch_name

                    ,GJH.je_source journal_source

                    ,GJH.je_category journal_category

                    ,GLCC.segment1 entity_segment

                    ,GLCC.segment2 project_segment

                    ,FFV.attribute1 project_vertical_dff

                    ,GLCC.segment3

                    ,GLCC.segment4

                    ,GLCC.segment5

                    ,GLCC.segment6

                    ,GLCC.segment7

                    ,TO_CHAR (GJH.doc_sequence_value) gl_doc_no

                    ,TO_CHAR (GJH.default_effective_date, 'DD-MON-YYYY') gl_date

                    ,TO_CHAR (GJH.posted_date, 'DD-MON-YYYY') posted_date

                    ,PE.expenditure_group document_number

                    ,PE.expenditure_group document_description

                    ,TO_CHAR (PEI.expenditure_item_date, 'DD-MON-YYYY')  document_date

--                  ,PPA.project_status_code document_status     

                    ,PPA.segment1 project_code 

                    ,PT.task_number task_code        

                    ,PEI.expenditure_type  

                    ,PEI.raw_cost transaction_cur_amount

                    ,PPA.project_currency_code entered_currency_code

                    ,PPA.project_rate_type exchange_rate_type

                    ,PEI.project_exchange_rate exchange_rat

   FROM       gl_je_batches GJB

                    ,gl_je_headers GJH

                    ,gl_je_lines   GJL

                    ,gl_code_combinations_kfv GLCC

                    ,gl_import_references GIR

                    ,xla_ae_headers  XAH

                    ,xla_ae_lines    XAL

                    ,xla_events      XE

                    ,xla_distribution_links XDL

                    ,pa_cost_distribution_lines_all PDL

                    ,pa_expenditure_items_all PEI

                    ,pa_expenditures_all PE

                    ,pa_tasks PT

                    ,pa_projects_all PPA

                    ,fnd_flex_value_sets FVS

                    ,fnd_flex_values FFV

         WHERE  GJB.je_batch_id         = GJH.je_batch_id          

         AND    GJH.je_header_id        = GJL.je_header_id

         AND    GJL.code_combination_id = GLCC.code_combination_id

         AND    GJL.je_header_id        = GIR.je_header_id

         AND    GJH.je_batch_id         = GIR.je_batch_id

         AND    GJL.je_line_num         = GIR.je_line_num

         AND    GIR.gl_sl_link_id       = XAL.gl_sl_link_id

         AND    GIR.gl_sl_link_table    = XAL.gl_sl_link_table

         AND    XAH.ae_header_id        = XAL.ae_header_id

         AND    XAH.application_id      = XAL.application_id

         AND    XAH.event_id            = XE.event_id

         AND    XAL.ae_header_id        = XDL.ae_header_id

         AND    XAL.ae_line_num         = XDL.ae_line_num

         AND    XDL.source_distribution_id_num_1 = PDL.expenditure_item_id 

         AND    PDL.expenditure_item_id  = PEI.expenditure_item_id

         AND    PEI.expenditure_id       = PE.expenditure_id

         AND    PEI.task_id              = PT.task_id

         AND    PT.project_id            = PPA.project_id

         AND    FFV.flex_value_set_id    = FVS.flex_value_set_id(+)

         AND    GLCC.segment2            = FFV.flex_value(+) 

         AND UPPER (FVS.flex_value_set_name) = UPPER ('PLL_Project')

    --  AND    XAH.entity_id        = XTE.entity_id

    --  AND xte.application_id = 275

        AND    GJH.je_source               = 'Project Accounting'

        AND    GJH.je_category             = 'Miscellaneous Transaction'

        AND    GJH.status                  = 'P' 

        AND    GJH.default_effective_date >= lc_gl_date_from

        AND    GJH.default_effective_date <= lc_gl_date_to

        AND    TRUNC (GJH.posted_date) BETWEEN NVL (lc_gl_posted_from,TRUNC (GJH.posted_date))AND NVL (lc_gl_posted_to ,TRUNC (GJH.posted_date))

        AND    GJH.je_source               = NVL (p_gl_source, GJH.je_source)

        AND    GJH.je_category             = NVL (p_gl_category, GJH.je_category)

        --        AND    GLCC.concatenated_segments BETWEEN (p_account_from) AND (p_account_to)

        AND    GLCC.segment1 BETWEEN  lc_segment_from(1) AND  lc_segment_to(1)

        AND    GLCC.segment2 BETWEEN  lc_segment_from(2) AND  lc_segment_to(2)

        AND    GLCC.segment3 BETWEEN  lc_segment_from(3) AND  lc_segment_to(3)

        AND    GLCC.segment4 BETWEEN  lc_segment_from(4) AND  lc_segment_to(4)

        AND    GLCC.segment5 BETWEEN  lc_segment_from(5) AND  lc_segment_to(5)

        AND    GLCC.segment6 BETWEEN  lc_segment_from(6) AND  lc_segment_to(6)

        AND    GLCC.segment7 BETWEEN  lc_segment_from(7) AND  lc_segment_to(7)

        AND    NVL (FFV.attribute1, '-1')      = NVL (p_proj_vertical_dff, NVL (FFV.attribute1, '-1'))

GL Transactions for Payments

SELECT DISTINCT aca.payment_method_lookup_code trans_type,
                TO_CHAR (aca.check_number) invoice_num,
                TO_CHAR (aca.check_date) invoice_date,
                xle.event_type_code ponum_inv_linenum,
                ael.currency_code line_desc,
                apps.imie_remove_special_chars.remove_special_chars
                   (TO_CHAR (CASE
                                WHEN xle.event_type_code =
                                                        'PAYMENT CANCELLATION'
                                   THEN (aca.amount * -1)
                                ELSE aca.amount
                             END
                            )
                   ) requestor_qty_inv,
                NULL appr_date_unitprice, NULL doc_seq_revamt,
                NULL acct_class, NULL PERCENT,
                  DECODE (ael.entered_dr, NULL, 0, ael.entered_dr)
                - DECODE (ael.entered_cr, NULL, 0, ael.entered_cr) amount,
                  DECODE (ael.accounted_dr,
                          NULL, 0,
                          ael.accounted_dr
                         )
                - DECODE (ael.accounted_cr, NULL, 0, ael.accounted_cr)
                                                                     dist_amt
           FROM xla_ae_headers aeh,
                xla_ae_lines ael,
                xla_events xle,
                xla.xla_transaction_entities ent,              
                xla_distribution_links xdl,
                ap_checks_all aca,
                ap_payment_hist_dists aphd,
                ap_payment_history_all aph
          WHERE 1 = 1
            AND ael.application_id = aeh.application_id
            AND ael.ae_header_id = aeh.ae_header_id
            AND xle.application_id = aeh.application_id
            AND xle.event_id = aeh.event_id          
            AND ent.application_id = xle.application_id
            AND ent.entity_id = xle.entity_id
            AND xdl.ae_header_id = aeh.ae_header_id
            AND xdl.ae_line_num = ael.ae_line_num
            AND aphd.payment_hist_dist_id = xdl.source_distribution_id_num_1
            AND xdl.source_distribution_type = 'AP_PMT_DIST'
            AND ent.transaction_number = aca.check_number
            AND xdl.application_id = 200
            AND aphd.payment_history_id = aph.payment_history_id
            AND aph.check_id = aca.check_id
            AND ael.ae_header_id = p_hdrid
            AND ael.ae_line_num = p_linenum;

GL Transactions for AR

SELECT rctt.description trans_type, TO_CHAR (rct.trx_number) invoice_num,
       TO_CHAR (rct.trx_date) invoice_date,
       TO_CHAR (rcl.line_number) ponum_inv_linenum, rcl.description line_desc,
       TO_CHAR (rcl.quantity_invoiced) requestor_qty_inv,
       TO_CHAR (rcl.unit_selling_price) appr_date_unitprice,
       TO_CHAR (rcl.revenue_amount) doc_seq_revamt,
       rctg.account_class acct_class, TO_CHAR (rctg.PERCENT) PERCENT,
       (rctg.amount * -1) amount, (rctg.acctd_amount * -1) dist_amt
  FROM xla_ae_headers aeh,
       xla_ae_lines ael,
       xla_events xle,
       xla.xla_transaction_entities ent,
       xla_distribution_links xdl,
       ra_customer_trx_all rct,
       ra_customer_trx_lines_all rcl,
       ra_cust_trx_types_all rctt,
       ra_cust_trx_line_gl_dist_all rctg
 WHERE 1 = 1
   AND ael.application_id = aeh.application_id
   AND ael.ae_header_id = aeh.ae_header_id
   AND xle.application_id = aeh.application_id
   AND xle.event_id = aeh.event_id
   AND ent.application_id = xle.application_id
   AND ent.entity_id = xle.entity_id
   AND xdl.ae_header_id = aeh.ae_header_id
   AND xdl.ae_line_num = ael.ae_line_num
   AND rcl.customer_trx_line_id = rctg.customer_trx_line_id
   AND rct.customer_trx_id = rcl.customer_trx_id
   AND rctt.cust_trx_type_id = rct.cust_trx_type_id
   AND rct.trx_number = ent.transaction_number
   AND xdl.source_distribution_id_num_1 = rctg.cust_trx_line_gl_dist_id
   AND xdl.source_distribution_type = 'RA_CUST_TRX_LINE_GL_DIST_ALL'
   AND ael.ae_header_id = p_hdrid
   AND ael.ae_line_num = p_linenum;

GL Transactions for AR Adjustments

SELECT NULL trans_type, ent.transaction_number invoice_num,
       (SELECT TO_CHAR (trx_date)
          FROM ra_customer_trx_all
         WHERE customer_trx_id = aaa.customer_trx_id) invoice_date,
       NULL ponum_inv_linenum, art.description line_desc,
       NULL requestor_qty_inv, NULL appr_date_unitprice, NULL doc_seq_revamt,
       NULL acct_class, NULL PERCENT,
       (  DECODE (ada.amount_dr, NULL, 0, ada.amount_dr)
        - DECODE (ada.amount_cr, NULL, 0, ada.amount_cr)
       ) amount,
       (  DECODE (ada.acctd_amount_dr, NULL, 0, ada.acctd_amount_dr)
        - DECODE (ada.acctd_amount_cr, NULL, 0, ada.acctd_amount_cr)
       ) dist_amt
  FROM xla_ae_headers aeh,
       xla_ae_lines ael,
       xla_events xle,
       xla.xla_transaction_entities ent,
       xla_distribution_links xdl,
       ar_distributions_all ada,
       ar_payment_schedules_all aps,
       ar_adjustments_all aaa,
       ar_receivables_trx_all art
 WHERE 1 = 1
   AND ael.application_id = aeh.application_id
   AND ael.ae_header_id = aeh.ae_header_id
   AND xle.application_id = aeh.application_id
   AND xle.event_id = aeh.event_id
   AND ent.application_id = xle.application_id
   AND ent.entity_id = xle.entity_id
   AND xdl.ae_header_id = aeh.ae_header_id
   AND xdl.ae_line_num = ael.ae_line_num
   AND aaa.payment_schedule_id = aps.payment_schedule_id
   AND aaa.receivables_trx_id = art.receivables_trx_id
   AND xdl.source_distribution_id_num_1 = ada.line_id
   AND ent.source_id_int_1 = aaa.adjustment_id
   AND ael.ae_header_id = p_hdrid
   AND ael.ae_line_num = p_linenum;

GL Transactions for AP

SELECT /*+ leading (ael aeh) */
       aia.invoice_type_lookup_code trans_type,
       ent.transaction_number invoice_num,
       TO_CHAR (aia.invoice_date) invoice_date,
       TO_CHAR (pod.po_number) ponum_inv_linenum,
       CASE
          WHEN aia.SOURCE = 'RECURRING INVOICE'
             THEN aia.description
          ELSE aid.description
       END line_desc,
       TO_CHAR (pod.requested_by) requestor_qty_inv,
       TO_CHAR (pod.approved_date) appr_date_unitprice,
       TO_CHAR (aia.doc_sequence_value) doc_seq_revamt, NULL acct_class,
       NULL PERCENT, NULL amount,
       DECODE (aid.base_amount, NULL, aid.amount, aid.base_amount) dist_amt
  FROM xla_ae_headers aeh,
       xla_ae_lines ael,
       xla_events xle,
       xla.xla_transaction_entities ent,
       xla_distribution_links xdl,
       apps.ap_invoices_all aia,
       apps.ap_invoice_distributions_all aid,
       (SELECT poh.segment1 po_number, poh.approved_date approved_date,
               pod.po_distribution_id po_dist_id, pod.code_combination_id,
               pap.full_name requested_by
          FROM apps.po_headers_all poh,
               apps.po_distributions_all pod,
               (SELECT pap1.person_id, pap2.full_name
                  FROM (SELECT   MAX (effective_end_date) end_date,
                                 person_id person_id
                            FROM apps.per_all_people_f pap
                        GROUP BY person_id) pap1,
                       (SELECT full_name, person_id,
                               effective_end_date end_date
                          FROM apps.per_all_people_f) pap2
                 WHERE pap1.person_id = pap2.person_id
                   AND pap1.end_date = pap2.end_date) pap
         WHERE poh.po_header_id = pod.po_header_id
           AND pod.deliver_to_person_id = pap.person_id) pod
 WHERE 1 = 1
   AND ael.application_id = aeh.application_id
   AND ael.ae_header_id = aeh.ae_header_id
   AND xle.application_id = aeh.application_id
   AND xle.event_id = aeh.event_id
   AND ent.application_id = xle.application_id
   AND ent.entity_id = xle.entity_id
   AND xdl.ae_header_id = aeh.ae_header_id
   AND xdl.ae_line_num = ael.ae_line_num
   AND ent.transaction_number = aia.invoice_num
   AND xdl.source_distribution_id_num_1 = aid.invoice_distribution_id
   AND xdl.source_distribution_type = 'AP_INV_DIST'
   AND aid.invoice_id = aia.invoice_id
   AND ent.entity_code = 'AP_INVOICES'
   AND NVL (aid.amount, 0) <> 0
   AND aid.po_distribution_id = pod.po_dist_id(+)
   AND ael.ae_header_id = p_hdrid
   AND ael.ae_line_num = p_linenum;