californiagirl
Programmer
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
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;