Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Newbie to Oracle reports need help with Lexical parameters

Status
Not open for further replies.

californiagirl

Programmer
Sep 20, 2007
2
US
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;


 
You need to give us a clue what you want your lexical parameters to do.
 
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


 
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 [tt]SELECT column1 &LEXICAL_FROM[/tt], the default value of LEXICAL_FROM must contain the FROM keyword and a valid table name that has COLUMN1 in its definition.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top