Lexical References and Parameters

[anne simms]

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;

[anne simms]

Any suggestions of any sort???

Anne

[vsespn]

Lexical parameters are used in situations where you can not create your code which will change at run time according to different situations. EXAMPLE
In your code you are using one condition:

and cdl.pa_date(+) BETWEEN :from_period AND :to_period

What will happen if :from_period and :to_period are blank, means user wants felxibility; if from_period and to_period are specified by user then they should be used otherwise the report should run for all periods.

In that case; you would like to create another user_parameter e.g.
lexical_example (character Type) , And in your report query replace the lines
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

with following lines:
cdl.EXPENDITURE_ITEM_ID (+) = gmsac.expenditure_item_id
and gcc.CODE_COMBINATION_ID = cdl.dr_CODE_COMBINATION_ID
&lexical_example

So this parameter (lexical_example) is called Lexical Parameter.

In your after_parameter_form trigger, assign actual conditions to it e.g.
you may want the code in your After_Parameter_Form Trigger:

if(:from_period is not null and :to_period is null) then
:lexical_example := ' and cdl.pa_date(+) > '||:from_period ;
elsif(:from_period is null and :to_period is not null) then
:lexical_example := ' and cdl.pa_date(+) < '||:to_period ;
else(:from_period is not null and :to_period is not null) then
:lexical_example := ' and cdl.pa_date(+) BETWEEN '||:from_period||' AND '||:to_period ;
end if ;
Return (TRUE) ;

So when user will run your report, the lexical parameter will set the report query according to the parameters actually passed by user.

[anne simms]

Thanks for explaining that it makes sense now. I have some other questions related to what you said.

1. Are report parameters always put in your after_parameter_form trigger area? or does this just depend on the report and the situation?

2. What exactly would this &lexical_example lexical parameter be named in a real life situation? I'm assuming it would have to make sense, but what are the restrictions on the naming conventions?Overall, what would I call the naming convention for &lexical_example in your example? I was having difficulty understanding how to name lexical parameters, but it seems it just needs to make sense and be in character format. Correct me if I am wrong

3. How do you know where to put the lexical parameter in the case you presented:

cdl.EXPENDITURE_ITEM_ID (+) = gmsac.expenditure_item_id
and gcc.CODE_COMBINATION_ID = cdl.dr_CODE_COMBINATION_ID
&lexical_example

I understand the following about lexical parameters:

A lexical parameter is a placeholder column containing the actual text to be used in a query. It is possible to replace the clauses appearing after SELECT, FROM, WHERE, GROUP BY, ORDER BY, HAVING, CONNECT BY and START WITH.

I guess my main question is why did you put it after this line
and gcc.CODE_COMBINATION_ID = cdl.dr_CODE_COMBINATION_ID?

Anne