select distinct
p.segment1,
p.START_DATE,
p.COMPLETION_DATE,
p.PROJECT_STATUS_CODE,
m.FULL_NAME,
ra.FULL_NAME,
t.task_number,
t.START_DATE,
t.COMPLETION_DATE,
tm.full_name,
a.AWARD_NUMBER,
rtrim(translate(a.AWARD_SHORT_NAME,chr(10),' ')),
o1.name,
o2.name,
nvl(c1.class_code,' '),
nvl(c2.class_code,' '),
nvl(c3.class_code,' '),
nvl(c4.class_code,' '),
nvl(c5.class_code,' '),
nvl(c6.class_code,' '),
nvl(c7.class_code,' '),
nvl(c8.class_code,' '),
c.CUSTOMER_NAME,
a.type,
a.Award_Purpose_code,
a.attribute10,
i.IND_RATE_SCH_NAME,
e.total_exp,
e.dir_exp,
e.ind_exp,
e.cs_exp,
ct.tot_commit,
ct.dir_commit,
ct.ind_commit,
ct.cs_commit,
e.billed,
e.rev,
f.funding,
b.budget_total,
b.direct_budget,
b.indirect_budget,
b.cs_budget

from 
(select gra.PROJECT_ID,
gra.TASK_ID,
gra.BUDGET_VERSION_ID,
sum(bl.BURDENED_COST) budget_total,
sum(decode(substr(r.name,1,10) ,'Award Reve',nvl(bl.BURDENED_COST,0),
'F & A Cost',nvl(bl.BURDENED_COST,0),0)) indirect_budget,
sum(decode(substr(r.name,1,10) ,'Award Reve',0,
'F & A Cost',0,
'Cost Share',0, 
nvl(bl.BURDENED_COST,0))) direct_budget,
sum(decode(substr(r.name,1,10),
'Cost Share',nvl(bl.BURDENED_COST,0),0)) cs_budget
from apps.GMS_RESOURCE_ASSIGNMENTS gra,
apps.gms_budget_lines bl,
apps.PA_RESOURCE_LIST_MEMBERS rlm,
apps.PA_RESOURCES r
where rlm.RESOURCE_ID = r.resource_id
and gra.RESOURCE_LIST_MEMBER_ID = rlm.RESOURCE_LIST_MEMBER_ID
and bl.RESOURCE_ASSIGNMENT_ID = gra.RESOURCE_ASSIGNMENT_ID
and gra.budget_version_id in
(select max(bv.budget_version_id)
from gms.gms_budget_versions bv
where bv.PROJECT_ID = gra.PROJECT_ID)
group by
gra.PROJECT_ID,
gra.TASK_ID,
gra.BUDGET_VERSION_ID) b,
(SELECT 
pct.project_id,
pct.TASK_ID, 
SUM(pct.tot_cmt_burdened_cost) tot_commit,
SUM(DECODE(pct.expenditure_category, 
'F & A Costs',0,
'Cost Share',0,
nvl(pct.tot_cmt_burdened_cost,0))) dir_commit,
SUM(DECODE(pct.expenditure_category, 
'F & A Costs',nvl(pct.tot_cmt_burdened_cost,0),0)) ind_commit,
SUM(DECODE(pct.expenditure_category, 
'Cost Share',nvl(pct.tot_cmt_burdened_cost,0),0)) cs_commit
FROM apps.pa_commitment_txns pct
GROUP BY
pct.project_id,
pct.TASK_ID) ct,
(SELECT gmsac.project_id,
gmsac.task_id,
SUM(cdl.PROJECT_BURDENED_COST) total_exp,
SUM(DECODE(SUBSTR(et.expenditure_category,1,10),'F & A Cost',0,
'Cost Share',0,
NVL(gmsac.burdened_cost,0))) dir_exp,
SUM(DECODE(SUBSTR(et.expenditure_category,1,10),'F & A Cost',NVL(cdl.PROJECT_BURDENED_COST,0),0)) ind_exp,
SUM(DECODE(SUBSTR(et.expenditure_category,1,10),'Cost Share',NVL(cdl.PROJECT_BURDENED_COST,0),0)) cs_exp,
SUM(NVL(gmsac.BILLED_AMOUNT,0)) billed,
SUM(NVL(gmsac.REVENUE_AMOUNT,0)) rev
FROM pams.pams_gms_status_actuals gmsac,
apps.gl_code_combinations gcc,
apps.PA_COST_DISTRIBUTION_LINES_ALL cdl,
apps.pa_expenditure_types et
WHERE cdl.EXPENDITURE_ITEM_ID (+) = gmsac.expenditure_item_id 
and cdl.pa_date(+) BETWEEN :from_period AND :to_period
and gcc.CODE_COMBINATION_ID = cdl.dr_CODE_COMBINATION_ID
and gcc.segment3 between nvl(:from_natural_acct,gcc.segment3) and nvl(:to_natural_account,gcc.segment3)
and et.EXPENDITURE_TYPE (+) = gmsac.expenditure_type
group by
gmsac.project_id,
gmsac.task_id) e,
(SELECT pf.project_id,
pf.task_id,
SUM(pf.FUNDING_AMOUNT) funding
FROM apps.gms_project_fundings pf
group by
pf.project_id,
pf.task_id) f,
apps.pa_projects_all p,
apps.pa_tasks t,
apps.gms_awards_all a,
apps.ra_customers c,
apps.PA_PROJECT_PARTIES pp1,
apps.PA_PROJECT_PARTIES pp2,
apps.GMS_PROJECT_FUNDINGS pf,
apps.GMS_INSTALLMENTS gi,
apps.hr_all_organization_units o1, 
apps.hr_all_organization_units o2,
apps.pa_segment_value_lookups d1,
apps.per_people_f m,
apps.per_people_f tm,
apps.per_people_f ra,
apps.pa_project_classes c1, 
apps.pa_project_classes c2, 
apps.pa_project_classes c3, 
apps.pa_project_classes c4, 
apps.pa_project_classes c5, 
apps.pa_project_classes c6, 
apps.pa_project_classes c7, 
apps.pa_project_classes c8, 
apps.PA_IND_RATE_SCHEDULES_ALL_BG i
where p.project_type = 'Sponsored Program' 
and p.SEGMENT1 between nvl(:from_project,p.segment1) and nvl(:to_project,p.segment1) 
and t.project_id = p.PROJECT_ID
and pp1.PROJECT_ID (+) = p.PROJECT_ID
and pp1.PROJECT_ROLE_ID (+) = 1 -- project manager
and pp1.start_date_active (+) <= sysdate
and nvl(pp1.END_DATE_ACTIVE (+),sysdate) >= sysdate
and m.PERSON_ID (+) = pp1.RESOURCE_SOURCE_ID
and pp2.PROJECT_ID (+) = p.PROJECT_ID
and pp2.PROJECT_ROLE_ID (+) = 1004 -- responsible accountant
and pp2.start_date_active (+) <= sysdate
and ra.PERSON_ID (+) = pp2.RESOURCE_SOURCE_ID
and nvl(pp2.END_DATE_ACTIVE (+),sysdate) >= sysdate
and tm.PERSON_ID (+) = t.TASK_MANAGER_PERSON_ID
and o1.ORGANIZATION_ID (+) = p.CARRYING_OUT_ORGANIZATION_ID
and d1.SEGMENT_VALUE_LOOKUP (+) = o1.NAME
and d1.segment_value between nvl(:from_proj_org,d1.segment_value) and nvl(:to_proj_org,d1.segment_value)
and o2.ORGANIZATION_ID (+) = t.CARRYING_OUT_ORGANIZATION_ID
and c1.PROJECT_ID (+) = p.PROJECT_ID
and c1.CLASS_CATEGORY (+) = 'Expense Code' 
and c2.PROJECT_ID (+) = p.PROJECT_ID
and c2.CLASS_CATEGORY (+) = 'OMB A-21'
and c3.PROJECT_ID (+) = p.PROJECT_ID
and c3.CLASS_CATEGORY (+) = 'Revenue Line'
and c4.PROJECT_ID (+) = p.PROJECT_ID
and c4.CLASS_CATEGORY (+) = 'Burden Rate'
and c5.PROJECT_ID (+) = p.PROJECT_ID
and c5.CLASS_CATEGORY (+) = 'Burden Structure'
and c6.PROJECT_ID (+) = p.PROJECT_ID
and c6.CLASS_CATEGORY (+) = 'Site'
and c7.PROJECT_ID (+) = p.PROJECT_ID
and c7.CLASS_CATEGORY (+) = 'Sponsor'
and c8.PROJECT_ID (+) = p.PROJECT_ID
and c8.CLASS_CATEGORY (+) = 'Type'
and b.project_id (+) = t.project_id
and b.task_id (+) = t.task_id
and pf.project_ID (+) = t.project_id
and pf.task_id (+) = t.task_id
and gi.INSTALLMENT_ID (+) = pf.installment_id 
and a.AWARD_ID (+) = gi.AWARD_ID 
and ct.project_id (+) = t.project_id
and ct.task_id (+) = t.task_id
and e.project_id (+) = t.project_id
and e.task_id (+) = t.task_id
and f.project_id (+) = t.project_id
and f.task_id (+) = t.task_id
and i.IND_RATE_SCH_ID (+) = a.IDC_SCHEDULE_ID
and c.CUSTOMER_ID (+) = a.FUNDING_SOURCE_ID
order by 2;

SELECT ename
FROM emp
WHERE hiredate BETWEEN :from_period AND :to_period

... BETWEEN &from_period AND &to_period

IF :par_model = 'U' then
   :par_datum := 'AND o.datum_do IS NULL';
ELSIF :par_model = 'I' then
   :par_datum := 'AND o.datum_do IS NOT NULL';
ELSE
   :par_datum := 'AND 1 = 1';
END IF;

SELECT this, that, ...
FROM tab_1 o, tab_2 m, ...
WHERE o.id = m.id
  AND m.something IN (SELECT ...)
  &par_datum        --> this is a lexical parameter

SELECT this, that, ...
FROM tab_1 o, tab_2 m, ...
WHERE o.id = m.id
  AND m.something IN (SELECT ...)
  AND o.datum_do IS NULL     --> &par_datum

AND o.datum = DECODE (:par_model, 'U', 'IS NULL', 'I', 'IS NOT NULL', '1 = 1')