Lexical References and Parameters
Posted: Wed Sep 19, 2007 3:13 pm
Hi All,
I am having a difficult time understanding Lexical references and parameters; like when to actually use them in Oracle reports. I read the help in Oracle reports about Lexical references and it makes sense somewhat, but I'm in the process of writing an Oracle report and I need to know if I need to add a lexical parameter or reference. See data model data below:
Please someone explain in laymen terms the lexical reference/parameters and when they should be used. Also please indicate if I should use it in this report. If so, where and why? I would truly appreciate your advice.
Thanks
---PAMSEXP.rdf PAMS PROJECT EXPENDITURE REPORT
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;
I am having a difficult time understanding Lexical references and parameters; like when to actually use them in Oracle reports. I read the help in Oracle reports about Lexical references and it makes sense somewhat, but I'm in the process of writing an Oracle report and I need to know if I need to add a lexical parameter or reference. See data model data below:
Please someone explain in laymen terms the lexical reference/parameters and when they should be used. Also please indicate if I should use it in this report. If so, where and why? I would truly appreciate your advice.
Thanks
---PAMSEXP.rdf PAMS PROJECT EXPENDITURE REPORT
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;