×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Newbie to Oracle reports need help with Lexical parameters

Newbie to Oracle reports need help with Lexical parameters

Newbie to Oracle reports need help with Lexical parameters

(OP)
Hi All,

I am having a difficult time knowing where to exactly place a Lexical references/parameters in my query below. Please keep in mind I am new. I know what a lexical parameter is, but not sure on where it should go in this query for my report. Also will I need to put in any information in the before, after etc parameter area of reports? Any advice or direction will help.  Thanks

---PROJECT EXPENDITURE REPORT

CODE

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: Newbie to Oracle reports need help with Lexical parameters

You need to give us a clue what you want your lexical parameters to do.

RE: Newbie to Oracle reports need help with Lexical parameters

(OP)
Hi,

Thanks for your reply. That is a little what I am confused on. I realize that a lexical parameter is a placeholder column containing the actual text to be used in a query. This report will only have 8 parameters:

##1        from period
##2        to period
##3        from project org number
##4        to project org number   
##5        from project number
##6        to project number  
##7        from natural account
##8        to natural account

Which are embedded already in the query as bind variables, so maybe I don't need any lexical parameters??? Let me know if I am mis-understanding the use of lexical parameters in any way.

Thanks


RE: Newbie to Oracle reports need help with Lexical parameters

You are exactly correct in your assumptions. Your SQL as written above will work fine.

Use lexical parameters in the way you understand to create dynamic SQL in your reports.  Be aware that if you use lexiacal parameters, you must ensure the default value of a lexical parameter must be so that the SQL statement it belongs to is valid.  eg. If you have a SQL that looks like SELECT column1 &LEXICAL_FROM, the default value of LEXICAL_FROM must contain the FROM keyword and a valid table name that has COLUMN1 in its definition.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close