Home » Developer & Programmer » Reports & Discoverer » Lexical reference/parameter
Lexical reference/parameter [message #268802] Wed, 19 September 2007 14:44 Go to next message
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 messageGo to next message
Littlefoot
Messages: 20901
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 messageGo to next message
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 #269096 is a reply to message #269021] Thu, 20 September 2007 12:39 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
What do you expect this lexical parameter to do?

Quote:
I know lexical references should go in this query ...

How come you do know that ...
Quote:
... but a little confused on where

... and how come you don't know that?
Re: Lexical reference/parameter [message #269129 is a reply to message #269096] Thu, 20 September 2007 15:12 Go to previous messageGo to next message
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 #269133 is a reply to message #269129] Thu, 20 September 2007 15:36 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I see.

Why do you think those should be lexical parameters and not "ordinary" user defined parameters?

Here's an example of my "ordinary" parameter:
SELECT ename
FROM emp
WHERE hiredate BETWEEN :from_period AND :to_period


How would you rewrite it using lexical parameters? What difference would it be if you put it as
... BETWEEN &from_period AND &to_period
Re: Lexical reference/parameter [message #269135 is a reply to message #269133] Thu, 20 September 2007 15:56 Go to previous messageGo to next message
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 messageGo to next message
Littlefoot
Messages: 20901
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 as
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

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 like
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

This could not be done on any other way; for example, you can't put it as
AND 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.
Re: Lexical reference/parameter [message #269292 is a reply to message #269201] Fri, 21 September 2007 09:00 Go to previous message
californiagirl
Messages: 79
Registered: May 2007
Member
I do understand better now. Thank you. Just needed someone to be patient with me.

Razz
Previous Topic: How to use Formula column in Oracle Reports
Next Topic: add fields at different frequencies
Goto Forum:
  


Current Time: Sun Dec 11 08:12:58 CST 2016

Total time taken to generate the page: 0.04351 seconds