PA Revnue extract for exp and events

(select -- Query for exp revenues
hou.name "OU_NAME",
ppa.segment1 "Proj_num",
ppa.name Project_name,
ppa.creation_date "Proj_creation_date",
pef.full_name Project_Manager,
pdra.draft_revenue_num,
perd.draft_revenue_item_line_num,
pdra.creation_date revenue_creation_date,
(
SELECT MEANING
FROM PA_LOOKUPS LK
WHERE
LK.LOOKUP_TYPE = 'INVOICE/REVENUE STATUS' AND
LK.LOOKUP_CODE = DECODE(PDRA.GENERATION_ERROR_FLAG, 'Y', 'GENERATION ERROR', DECODE(PDRA.RELEASED_DATE, NULL, 'UNRELEASED', DECODE(PDRA.TRANSFER_STATUS_CODE, 'P', 'RELEASED', 'X', 'REJECTED IN TRANSFER', 'T', 'TRANSFERRED', 'A', 'ACCEPTED', 'R', 'REJECTED', 'RECEIVED')))
) REVENUE_STATUS,
PDRA.TRANSFER_REJECTION_REASON,
perd.expenditure_item_id "Transaction Id",
pat.task_number,
pat.task_name,
haou.name expenditure_org,
pei.expenditure_type,
NULL "Event Num",
amount ,
abs(pdra.unearned_revenue_cr) "Unearned Revenue amount",
(select gcccr.segment1'-'gcccr.segment2'-'gcccr.segment3'-'gcccr.segment4'-'gcccr.segment5'-'gcccr.segment6'-'gcccr.segment7'-'gcccr.segment8
from apps.gl_code_combinations gcccr
where code_combination_id = unearned_code_combination_id ) "Unearned Revenue Account",
pdra.unbilled_receivable_dr "Unbilled Receivables amount",
(select gcccr.segment1'-'gcccr.segment2'-'gcccr.segment3'-'gcccr.segment4'-'gcccr.segment5'-'gcccr.segment6'-'gcccr.segment7'-'gcccr.segment8
from apps.gl_code_combinations gcccr
where code_combination_id = unbilled_code_combination_id ) "Unbilled Receivables Account",
(select gcccr.segment1'-'gcccr.segment2'-'gcccr.segment3'-'gcccr.segment4'-'gcccr.segment5'-'gcccr.segment6'-'gcccr.segment7'-'gcccr.segment8
from apps.gl_code_combinations gcccr
where code_combination_id = perd.code_combination_id ) "Expnd/Event Revenue",
perd.project_currency_code
from
pa_draft_revenues_all pdra,
pa_cust_rev_dist_lines_all perd,
pa_expenditure_items_all pei,
apps.hr_all_organization_units haou,
apps.pa_tasks pat,
apps.pa_projects_all ppa,
apps.hr_operating_units hou ,
apps.pa_project_players pp,
apps.per_all_people_f pef
where 1=1--pdra.project_id = 147587
and pdra.project_id = perd.project_id
and pdra.DRAFT_REVENUE_NUM = perd.DRAFT_REVENUE_NUM
and ppa.project_id = pdra.project_id
and ppa.project_id = perd.project_id
and ppa.org_id = hou.organization_id
and
--trunc(ppa.creation_date) >= to_date(:p_date) AND -- MM/DD/YYYY
ppa.project_id=pp.project_id AND
pp.project_role_type='PROJECT MANAGER' AND
pp.person_id=pef.person_id AND
SYSDATE BETWEEN pef.effective_start_date AND NVL(pef.effective_end_date,SYSDATE) AND
SYSDATE BETWEEN pp.start_date_active AND NVL(pp.end_date_active,SYSDATE) AND
perd.expenditure_item_id = pei.expenditure_item_id AND
ppa.project_id = pei.project_id AND
pat.task_id = pei.task_id AND
pat.project_id = pei.project_id AND
haou.organization_id = pei.cc_recvr_organization_id
UNION
select -- -- Query for exp revenures
hou.name "OU_NAME",
ppa.segment1 "Proj_num",
ppa.name Project_name,
ppa.creation_date "Proj_creation_date",
pef.full_name Project_Manager,
pdra.draft_revenue_num,
perd.draft_revenue_item_line_num,
pdra.creation_date revenue_creation_date,
(
SELECT MEANING
FROM PA_LOOKUPS LK
WHERE
LK.LOOKUP_TYPE = 'INVOICE/REVENUE STATUS' AND
LK.LOOKUP_CODE = DECODE(PDRA.GENERATION_ERROR_FLAG, 'Y', 'GENERATION ERROR', DECODE(PDRA.RELEASED_DATE, NULL, 'UNRELEASED', DECODE(PDRA.TRANSFER_STATUS_CODE, 'P', 'RELEASED', 'X', 'REJECTED IN TRANSFER', 'T', 'TRANSFERRED', 'A', 'ACCEPTED', 'R', 'REJECTED', 'RECEIVED')))
) REVENUE_STATUS,
PDRA.TRANSFER_REJECTION_REASON,
NULL "Transaction Id",
NULL task_number,
NULL task_name,
NULL expenditure_org,
NULL expenditure_type,
event_num "Event Num",
amount ,
abs(pdra.unearned_revenue_cr) "Unearned Revenue amount",
(select gcccr.segment1'-'gcccr.segment2'-'gcccr.segment3'-'gcccr.segment4'-'gcccr.segment5'-'gcccr.segment6'-'gcccr.segment7'-'gcccr.segment8
from apps.gl_code_combinations gcccr
where CODE_COMBINATION_ID = UNEARNED_CODE_COMBINATION_ID ) "Unearned Revenue Account",
pdra.unbilled_receivable_dr "Unbilled Receivables amount",
(select gcccr.segment1'-'gcccr.segment2'-'gcccr.segment3'-'gcccr.segment4'-'gcccr.segment5'-'gcccr.segment6'-'gcccr.segment7'-'gcccr.segment8
from apps.gl_code_combinations gcccr
where CODE_COMBINATION_ID = UNBILLED_CODE_COMBINATION_ID ) "Unbilled Receivables Account",
(select gcccr.segment1'-'gcccr.segment2'-'gcccr.segment3'-'gcccr.segment4'-'gcccr.segment5'-'gcccr.segment6'-'gcccr.segment7'-'gcccr.segment8
from apps.gl_code_combinations gcccr
where CODE_COMBINATION_ID = perd.CODE_COMBINATION_ID ) "Expnd/Event Revenue",
perd.PROJECT_CURRENCY_CODE
from
pa_draft_revenues_all pdra,
pa_cust_event_rdl_all perd,
apps.pa_projects_all ppa,
apps.hr_operating_units hou,
apps.pa_project_players pp,
apps.per_all_people_f pef
where 1=1--pdra.project_id = 147587
and pdra.project_id = perd.project_id
and pdra.DRAFT_REVENUE_NUM = perd.DRAFT_REVENUE_NUM
and ppa.project_id = pdra.project_id
and ppa.project_id = perd.project_id
and ppa.org_id = hou.organization_id
and
--trunc(ppa.creation_date) >= to_date(:p_date) AND -- MM/DD/YYYY
ppa.project_id=pp.project_id AND
pp.project_role_type='PROJECT MANAGER' AND
pp.person_id=pef.person_id AND
SYSDATE BETWEEN pef.effective_start_date AND NVL(pef.effective_end_date,SYSDATE) AND
SYSDATE BETWEEN pp.start_date_active AND NVL(pp.end_date_active,SYSDATE)
) order by 1,2