Home » Developer & Programmer » Reports & Discoverer » Lexical reference/parameter
Lexical reference/parameter [message #268802] |
Wed, 19 September 2007 14:44 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
californiagirl
Messages: 79 Registered: May 2007
|
Member |
|
|
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. I even read some of the comments about Lexical in the forum. 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
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;
|
|
|
Re: Lexical reference/parameter [message #268807 is a reply to message #268802] |
Wed, 19 September 2007 15:44 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/72104.gif) |
Littlefoot
Messages: 21818 Registered: June 2005 Location: Croatia, Europe
|
Senior Member Account Moderator |
|
|
Quote: | Should I use it (lexical parameter) in this report. If so, where and why?
|
You must be kidding, right?
If YOU don't know whether you should use lexical parameter(s) or not, I really can't help you. Could, though, help about "where" and "how", but "should I" and "why" are out of my scope.
By the way, putting a query into the [code] tags does NOT make it formatted. It is still a complete mess.
A suggestion: leave such a complicated query for later. Create an example based on Scott's schema and learn use of lexical parameters on it. When you finally find out what they are, how to use them and why, I believe you'll be able to answer your question yourself.
|
|
|
Re: Lexical reference/parameter [message #269021 is a reply to message #268807] |
Thu, 20 September 2007 08:28 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
californiagirl
Messages: 79 Registered: May 2007
|
Member |
|
|
Correction, let me re-state. I know lexical references should go in this query, but a little confused on where.
Also I don't appreciate being berated. This is suppose to be a professional forum and if someone knows more than another person, you should not condescend someone else for knowing less.
|
|
|
|
Re: Lexical reference/parameter [message #269129 is a reply to message #269096] |
Thu, 20 September 2007 15:12 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
californiagirl
Messages: 79 Registered: May 2007
|
Member |
|
|
These are the lexical parameters I was thinking should be used in the query:
&from_period
&to_period
&from_project_org_number
&to_project_org_number
&from_project_number
&to_project_number
&from_natural_account
&to_natural_account
|
|
|
|
Re: Lexical reference/parameter [message #269135 is a reply to message #269133] |
Thu, 20 September 2007 15:56 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
californiagirl
Messages: 79 Registered: May 2007
|
Member |
|
|
I see what you are saying. Sounds like it's best to just keep them as ordinary parameters.
So what am I missing about the lexical parameters then, because 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. So in my case I guess I really don't need a lexical parameter in my query/report???? because the parameters are driving the report data.
|
|
|
Re: Lexical reference/parameter [message #269201 is a reply to message #269135] |
Fri, 21 September 2007 02:36 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/72104.gif) |
Littlefoot
Messages: 21818 Registered: June 2005 Location: Croatia, Europe
|
Senior Member Account Moderator |
|
|
Although you *might* use lexical parameter as well, their purpose is different. Here's an example I used so - perhaps you'll get the idea.
I have got a report which has to select set of records depending on status and one of the DATE columns. I've created a USER PARAMETER (which will, actually, act as a lexical parameter) called 'par_datum' and another one ('par_model') which is populated by user (model might be 'U', 'I' or "other" (doesn't matter what, but not U or I)).
User enters a value into the 'par_model' parameter. AFTER PARAMETER FORM trigger sets value of the 'par_datum' lexical parameter as:
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;
This lexical parameter is then used in the main report query asSELECT 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
Depending on user's choice, lexical parameter evaluates into a condition which is used in a query. For example, if user chose 'par_model = U', query would look likeSELECT 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
This could not be done on any other way; for example, you can't put it asAND o.datum = DECODE (:par_model, 'U', 'IS NULL', 'I', 'IS NOT NULL', '1 = 1') as it is an invalid syntax and, moreover, datatypes wouldn't match (date vs. string).
I hope you got the picture and understood what I meant to say.
|
|
|
|
Goto Forum:
Current Time: Wed Aug 28 15:18:52 CDT 2024
|