ECC Projects, Projects Costing: Transactions, SQL3

select
x.*
from
(
 
WITH sq_bill_proj AS (
    SELECT
        ppa.project_id,
        ppa.name       project_name,
        ppa.segment1   project_number,
        ppa.org_id,
        ppa.project_type,
        nvl(ppa.project_level_funding_flag, 'N') project_level_funding_flag,
        ppa.multi_currency_billing_flag,
		ppta.project_type_class_code,
		ppa.start_date,
		ppa.completion_date,
		pia.set_of_books_id
    FROM
        pa_projects_all           ppa,
        pa_project_statuses       ps,
        pa_project_types_all      ppta,
        pa_budget_versions        pbv,
        pa_budget_types           pbt,
        pa_budget_entry_methods   pabem,
        pa_implementations_all    pia
    WHERE
        pia.org_id = ppa.org_id
        AND ppta.project_type = ppa.project_type
        AND ppta.org_id = ppa.org_id
        AND ppta.direct_flag = 'Y' /* CONTRACT PROJECT */
        AND ppta.project_type_class_code = 'CONTRACT'
        AND nvl(ppta.cc_prvdr_flag, 'N') <> 'Y' /* NOT TO INCLUDE INTERCOMPANY PROJECTS */
        AND ppa.project_status_code = ps.project_status_code
        AND ps.project_system_status_code NOT IN ('CLOSED','PENDING_CLOSE','PENDING_PURGE','PARTIALLY_PURGED','PURGED')
        AND ppa.template_flag = 'N'
        AND ppa.revenue_accrual_method IN ('WORK','EVENT')
        AND ppa.invoice_method IN ('WORK','EVENT')
        AND pbv.project_id = ppa.project_id
        AND pbv.budget_status_code = 'B'
        AND pbv.current_flag = 'Y'
        AND pbv.budget_type_code = 'AR' /* APPROVED REVENUE BUDGET HARD CHECK? */
        AND pbv.budget_type_code = pbt.budget_type_code
        AND pabem.budget_entry_method_code = pbv.budget_entry_method_code
		AND PABEM.TIME_PHASED_TYPE_CODE = 'G' /* BY GL PERIOD */
        AND pabem.entry_level_code IN ('P','T')
)
SELECT
'PROJ_DS_EICDL_EXCLD' record_type,
'PROJ-EXCLD-'
|| TO_CHAR(proj.project_id) || '-' || to_char(pt.top_task_id)||'-'||gps.period_name ecc_spec_id,
proj.project_id,
proj.project_name,
proj.project_number,
proj.org_id,
case proj.project_level_funding_flag when 'Y' then 0 else pt.top_task_id end funding_task_id,
pt.task_id,
pt.task_number,
pt.task_name,
pt.top_task_id top_task_id,
pt.task_name top_task_name,
pt.task_number top_task_number,
proj.project_type_class_code,
'N' billproj_display_flag,
-9999 ei_ev_reference,
gps.period_name bill_gl_period_name,
proj.project_id||':'||case proj.project_level_funding_flag when 'Y' then 0 else pt.top_task_id end||':'||'-9999' DS_ASSOC_PRJ_TSK_EE_ATTR,
proj.project_id||':'||case proj.project_level_funding_flag when 'Y' then 0 else pt.top_task_id end||':'||'-9999'||':'||gps.period_name DS_ASSOC_PRJ_TSK_EE_PER_ATTR,
proj.project_id||':'||case proj.project_level_funding_flag when 'Y' then 0 else pt.top_task_id end||':'||gps.period_name DS_ASSOC_PRJ_TSK_PER_ATTR
FROM sq_bill_proj proj, pa_tasks pt, gl_period_statuses gps
WHERE proj.project_id = pt.project_id
AND   pt.task_id = pt.top_task_id
AND   pt.wbs_level = 1
AND   gps.set_of_books_id = proj.set_of_books_id
AND   gps.application_id = 101
AND   gps.start_date between proj.start_date and nvl(proj.completion_date, add_months(sysdate, 6))
AND   gps.adjustment_period_flag = 'N'
 
) x
where
2=2