Revenue Journals created from Projects

Query to get revenue journal details from Project draft revenues, General Ledger and Sub ledger tables:

Usage of WITH Clause:

WITH t1 AS
     (
        SELECT xah.ae_header_id, xal.ae_line_num, xah.event_id,xal.gl_sl_link_id,xal.ledger_id,xal.gl_sl_link_table,
               xal.code_combination_id,xal.entered_dr entered_debit
            ,    xal.entered_cr entered_credit
            ,    xal.accounted_dr accounted_debit
            ,    xal.accounted_cr accounted_credit
            ,   xal.entered_dr, xal.entered_cr,
               xal.accounted_dr, xal.accounted_cr,xal.accounting_class_code
        FROM   xla_ae_headers xah, xla_ae_lines xal
         WHERE xah.ae_header_id = xal.ae_header_id
     ),
     t2 AS
     (
        SELECT erdl.draft_revenue_item_line_num, dr.event_id,
               pe.event_id ev_id, pe.attribute1, pe.attribute2, papa.segment1,TO_CHAR(dr.gl_date,'RRRR-MM-DD') default_effective_date,
               papa.NAME,dr.event_id attribute9,pe.attribute1 man_months,pe.attribute2 onsite_offshore
          FROM pa_cust_event_rdl_all erdl,
               pa_events pe,
               pa_draft_revenues_all dr,
               pa_implementations_all imp,
               pa_projects_all papa
         WHERE erdl.project_id = dr.project_id
           AND dr.draft_revenue_num = erdl.draft_revenue_num
           AND imp.org_id = dr.org_id
           AND erdl.project_id = pe.project_id
           AND NVL (erdl.task_id, -1) = NVL (pe.task_id, -1)
           AND erdl.event_num = pe.event_num
           AND dr.event_id IS NOT NULL
           AND NVL (erdl.amount, 0) <> 0
           AND dr.draft_revenue_num_credited IS NULL
           AND papa.project_id = pe.project_id       ),
      t3 AS
      (
            SELECT gjh.je_header_id,xal.ae_header_id,xal.ae_line_num,xal.gl_sl_link_id
             , gjb.je_batch_id
             , gjb.name batch_name
             , gjh.name journal_name
             , gjh.description description
             , gl.name ledger_name
             , gjh.period_name
             , DECODE (gjh.actual_flag, 'A', 'Actual') balance_type
             , DECODE (gjh.accrual_rev_flag,'N', 'Not Reversed','R', 'Reversed') reverse_status
             , gjh.originating_bal_seg_value
             , gjh.je_category CATEGORY
             ,(SELECT trim(ENCUMBRANCE_TYPE) FROM GL_ENCUMBRANCE_TYPES WHERE ENCUMBRANCE_TYPE_ID=gjh.ENCUMBRANCE_TYPE_ID) ENCUMBRANCE_TYPE
             , gjh.tax_status_code tax
             , gjh.control_total
             , gjh.currency_code
             , gjh.accrual_rev_effective_date reverse_date
             , gjh.accrual_rev_change_sign_flag reverse_method
             , gjh.accrual_rev_period_name reverse_period
            , gjl.je_line_num
            , gjh.posted_date
             , gl.short_name
              FROM gl_je_batches gjb,
                   gl_je_headers gjh,
                   gl_ledgers gl,
                   gl_je_lines gjl,
                   gl_code_combinations gcc,
                   gl_import_references gir,
                   xla_ae_headers xah,
                   xla_ae_lines xal
             WHERE gjb.je_batch_id = gjh.je_batch_id
               AND gl.ledger_id = gjh.ledger_id
               AND gjh.je_source = 'Project Accounting'
               AND gjh.je_category = 'Revenue'
               AND gjh.status = 'U'
               AND gjh.je_header_id = gjl.je_header_id
               AND gcc.code_combination_id = gjl.code_combination_id
               AND gjh.je_header_id = gir.je_header_id
               AND gjh.je_batch_id = gir.je_batch_id
               AND xah.ae_header_id = xal.ae_header_id
               AND xal.gl_sl_link_id = gir.gl_sl_link_id
               AND xal.gl_sl_link_table = gir.gl_sl_link_table
               AND gir.je_line_num = gjl.je_line_num
               AND gcc.code_combination_id = xal.code_combination_id
      )
      SELECT   t3.je_header_id
             , t3.je_line_num
             , t3.je_batch_id
             , t3.batch_name
             , t3.journal_name
             , t3.description
             , t3.ledger_name
             , t3.period_name
             , t3.balance_type
             , t3.reverse_status
             , t3.originating_bal_seg_value
             , t3.CATEGORY
             --,t2.default_effective_date
             , NVL(t2.default_effective_date,(SELECT t4.default_effective_date
                                                FROM t2 t4
                                             WHERE t4.event_id=t1.event_id
                                             AND t4.default_effective_date IS NOT NULL
                                             AND ROWNUM<2)) default_effective_date
             , t3.ENCUMBRANCE_TYPE
             , t3.tax
             , t3.control_total
             , t3.currency_code
             , t3.reverse_date
             , t3.reverse_method
             , t3.reverse_period
            ,    t1.entered_dr entered_debit
            ,    t1.entered_cr entered_credit
            ,    t1.accounted_dr accounted_debit
            ,    t1.accounted_cr accounted_credit
              ,REPLACE(glcc.segment1, '&','&'||'amp;') AS company
              ,REPLACE(glcc.segment2, '&','&'||'amp;') AS unit
              ,REPLACE(glcc.segment3, '&','&'||'amp;') AS department
              ,REPLACE(glcc.segment4, '&','&'||'amp;') AS account
              ,REPLACE(glcc.segment5, '&','&'||'amp;') AS project_product
              ,REPLACE(glcc.segment6, '&','&'||'amp;') AS Intercompany
              ,REPLACE(glcc.segment7, '&','&'||'amp;') AS future
             , t3.posted_date
             , t3.short_name
             ,t2.attribute9
             ,t1.accounting_class_code
             ,t2.man_months
             ,t2.onsite_offshore
             ,t1.ae_line_num
             ,t1.gl_sl_link_id
    FROM t1, t2, t3,gl_code_combinations glcc
    WHERE t1.event_id = t2.event_id(+)
     AND t1.ae_line_num = t2.draft_revenue_item_line_num(+)
     AND glcc.code_combination_id = t1.code_combination_id
     AND t1.ae_header_id = t3. ae_header_id
     AND t1.gl_sl_link_id = t3.gl_sl_link_id
     AND t1.ae_line_num = t3.ae_line_num
     --AND t3.je_header_id =  307299
     ORDER BY 1, 2 

Thanks,

Rajesh