Home » RDBMS Server » Performance Tuning » Hints USE_HASH (merged)
| Hints USE_HASH (merged) [message #395193] |
Tue, 31 March 2009 10:39  |
Anne Simms
Messages: 163
Registered: May 2002
Location: San Francisco, CA
|
Senior Member |
|
|
Hi,
I'm creating a huge report that is using tons of nested loops.
Question:
1. I'm trying to figure out if I need to use the hint USE_HASH for each table?
2.How will I know which tables to use for the use_hash?
I have the explain plan if needed.
Plan hash value: 2454355945
SELECT/*+ use_hash(a) use_hash(p) use_hash(t)*/ DISTINCT
p.x1||'^'||
p.x2||'^'||
p.x3||'^'||
p.x4||'^'||
m.x5||'^'||
t.x6||'^'||
t.x7||'^'||
t.x8||'^'||
tm.x9||'^'||
a.x10||'^'||
o1.x11||'^'||
o2.x12||'^'||
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.x13||'^'||
a.x14||'^'||
a.x15||'^'||
a.attribute10||'^'||
i.x16||'^'||
e.x17||'^'||
e.x18||'^'||
e.x19||'^'||
e.x20||'^'||
ct.x21||'^'||
ct.x22||'^'||
ct.x23||'^'||
ct.x24||'^'||
e.x25||'^'||
e.x26||'^'||
e.x27||'^'||
f.x28||'^'||
b.x29||'^'||
b.x30||'^'||
b.x31||'^'||
b.x32,
3
FROM apps.gm_award_all a
JOIN apps.pa_project_all p
ON p.project_type = 'Program'
AND p.SEGMENT1 BETWEEN NVL('##5', p.segment1) AND NVL('##6', p.segment1)
JOIN apps.pa_task t
ON t.project_id = p.PROJECT_ID
JOIN apps.hr_all_organization_units o1
ON o1.ORGANIZATION_ID = p.CARRYING_OUT_ORGANIZATION_ID
JOIN apps.pa_segment_value_lookups d1
ON d1.SEGMENT_VALUE_LOOKUP = o1.NAME
AND d1.segment_value BETWEEN NVL('##3',d1.segment_value)
AND NVL ('##4',d1.segment_value)
JOIN apps.hr_all_organization_units o2
ON o2.ORGANIZATION_ID = t.CARRYING_OUT_ORGANIZATION_ID
LEFT OUTER
JOIN apps.ra_customers c
ON c.CUSTOMER_ID = a.FUNDING_SOURCE_ID
LEFT OUTER
Anne
|
|
|
|
| Re: Hints USE_HASH [message #395397 is a reply to message #395193] |
Wed, 01 April 2009 05:20  |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
I'm guessing that all of those ##n values are replaced with either other values or nulls.
How do you generally use those values? Are they normally small ranges returning a small number of rows? Or do you frequently use NULLs so that all rows re selected? Use Nested loops for the former, hash joins for the latter. However, if you are using Nested Loops, you need to make sure that the driving table is the one with the selective where clauses.
Ross Leishman
|
|
|
|
| Re: Hints USE_HASH [message #395426 is a reply to message #395397] |
Wed, 01 April 2009 07:37 |
Anne Simms
Messages: 163
Registered: May 2002
Location: San Francisco, CA
|
Senior Member |
|
|
Yes, they are replaced with values. It's a sql plus report, so therefore a user would be prompted to put in a value.
Normally the report is ran with a high range of values, so it will return tons of rows.But sometimes it's null so that all rows are selected. The explain plan shows nested loops through out this report.
Can you explain this better:
you need to make sure that the driving table is the one with the selective where clauses.
Are you saying the driving tables should be used in the hint?
Should I separate each table in the hint as follows:
/*+ use_hash(a) use_hash(p) use_hash(t)*/
or should it be:
/*+ use_hash(a,p) use_hash(t) */
|
|
|
|
| Re: Hints USE_HASH [message #395467 is a reply to message #395426] |
Wed, 01 April 2009 09:18 |
Anne Simms
Messages: 163
Registered: May 2002
Location: San Francisco, CA
|
Senior Member |
|
|
Here is the entire query. Also attached explain plan.
SELECT 'Project report ##s_date - ##e_date, Project Organizations ##3 - ##4, Projects ##5 - ##6 and Accounts ##7 - ##8',
0 sort_col
FROM DUAL
UNION
SELECT '##err_message', 1
FROM DUAL
UNION
SELECT 'Project Number^Project Start Date^Project End Date^Status^Project Manager^Task Number^'||
'Task Start Date^Task Completion Date^Task Manager^Award Number^Award Short Name^Project Organization^'||
'Task Organization^Expense Code^OMB Code^Revenue Line^Burden Rate^Burden Structure^Site^Sponsor^Type^Customer^'||
'Award Type^Award Purpose^Federal Flow Thru Code^IDC Schedule Name^Total Expenditure^Direct Charges^'||
'Indirect Charges^Cost Share Charges^Total Commitments^Direct Commitments^Indirect Commitments^Cost Share Commitments^'||
'Program Income^Total Invoiced^Total Revenue^Total Funding^'||
'Total Budget^Direct Budget^Indirect Budget^Cost Share Budget',
2
FROM DUAL
UNION
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.program_income||'^'||
e.billed||'^'||
e.rev||'^'||
f.funding||'^'||
b.budget_total||'^'||
b.direct_budget||'^'||
b.indirect_budget||'^'||
b.cs_budget,
3
FROM apps.gms_awards_all a
JOIN apps.pa_projects_all p
ON p.project_type = 'Sponsored'
AND p.SEGMENT1 BETWEEN NVL('##5', p.segment1) AND NVL('##6', p.segment1)
JOIN apps.pa_tasks t
ON t.project_id = p.PROJECT_ID
JOIN apps.hr_all_organization_units o1
ON o1.ORGANIZATION_ID = p.CARRYING_OUT_ORGANIZATION_ID
JOIN apps.pa_segment_value_lookups d1
ON d1.SEGMENT_VALUE_LOOKUP = o1.NAME
AND d1.segment_value BETWEEN NVL('##3',d1.segment_value)
AND NVL ('##4',d1.segment_value)
JOIN apps.hr_all_organization_units o2
ON o2.ORGANIZATION_ID = t.CARRYING_OUT_ORGANIZATION_ID
LEFT OUTER
JOIN apps.ra_customers c
ON c.CUSTOMER_ID = a.FUNDING_SOURCE_ID
LEFT OUTER
--Breakdown of Budget totals
JOIN (SELECT ag.award_id, gra.project_id, gra.task_id,
gra.budget_version_id,
SUM (NVL(bl.burdened_cost,0)) budget_total,
SUM (CASE SUBSTR (r.NAME, 1, 10)
WHEN 'Award Reve' THEN NVL (bl.burdened_cost, 0)
WHEN 'F & A Cost' THEN NVL (bl.burdened_cost, 0)
ELSE 0
END) indirect_budget,
SUM (CASE SUBSTR (r.NAME, 1, 10)
WHEN 'Award Reve' THEN 0
WHEN 'F & A Cost' THEN 0
WHEN 'Cost Share' THEN 0
ELSE NVL (bl.burdened_cost, 0)
END) direct_budget,
SUM (CASE SUBSTR (r.NAME, 1, 10)
WHEN 'Cost Share' THEN NVL (bl.burdened_cost, 0)
ELSE 0
END) cs_budget
FROM apps.gms_resource_assignments gra
JOIN apps.gms_budget_versions bv
ON bv.project_id = gra.project_id
AND bv.budget_version_id = gra.budget_version_id
JOIN apps.gms_budget_lines bl
ON bl.resource_assignment_id = gra.resource_assignment_id
JOIN apps.pa_resource_list_members rlm
ON rlm.resource_list_member_id = gra.resource_list_member_id
JOIN apps.pa_resources r
ON r.resource_id = rlm.resource_id
JOIN apps.gms_awards_all ag
ON ag.award_id = bv.award_id
WHERE 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 ag.award_id,
gra.project_id,
gra.task_id,
gra.budget_version_id) b
ON b.project_id = t.project_id
AND b.task_id = t.task_id
AND b.award_id = a.award_id
LEFT OUTER
--Breakdown of tot commitments
JOIN (SELECT gma.award_id,
pct.project_id,
pct.TASK_ID,
SUM (NVL(pct.tot_cmt_burdened_cost,0)) tot_commit,
SUM (CASE pct.expenditure_category
WHEN 'F & A Costs' THEN 0
WHEN 'Cost Share' THEN 0
ELSE NVL(pct.tot_cmt_burdened_cost, 0)
END) dir_commit,
SUM (CASE pct.expenditure_category
WHEN 'F & A Costs' THEN NVL(pct.tot_cmt_burdened_cost, 0)
ELSE 0
END) ind_commit,
SUM (CASE pct.expenditure_category
WHEN 'Cost Share' THEN NVL(pct.tot_cmt_burdened_cost, 0)
ELSE 0
END) cs_commit
FROM apps.pa_commitment_txns pct
JOIN apps.gms_awards_basic_v gmb
ON gmb.project_id = pct.project_id
AND gmb.task_id = pct.task_id
JOIN apps.gms_awards_all gma
ON gma.award_id = gmb.award_id
GROUP BY gma.award_id,
pct.project_id,
pct.TASK_ID) ct
ON ct.project_id = t.project_id
AND ct.task_id = t.task_id
AND ct.award_id = a.award_id
LEFT OUTER
--Breakdown for tot expenses
JOIN (SELECT gmsa.award_id,
gmsac.project_id,
gmsac.task_id,
SUM (CASE SUBSTR (gmsac.expenditure_type, 1, 3)
WHEN 'IDC' THEN 0
ELSE NVL (GMSAC.BURDENED_COST, 0)
END)
+ SUM (CASE SUBSTR (et.expenditure_category, 1, 11)
WHEN 'F & A Costs' THEN NVL (GMSAC.BURDENED_COST, 0)
ELSE 0
END)
+ SUM (CASE SUBSTR (et.expenditure_category, 1, 20)
WHEN 'Cost Share' THEN NVL (GMSAC.BURDENED_COST, 0)
WHEN 'Cost Share-Direct' THEN NVL (GMSAC.BURDENED_COST, 0)
WHEN 'Cost Share-Indirect' THEN NVL (GMSAC.BURDENED_COST, 0)
WHEN 'Research Enhancement' THEN NVL (GMSAC.BURDENED_COST, 0)
ELSE 0
END)
+ SUM (CASE SUBSTR (et.expenditure_category, 1, 16)
WHEN 'Program Income' THEN NVL (GMSAC.BURDENED_COST, 0)
WHEN 'Program - Income' THEN NVL (GMSAC.BURDENED_COST, 0)
ELSE 0
END) TOTAL_EXP,
SUM (CASE substr (gmsac.expenditure_type, 1, 3)
WHEN 'IDC' THEN 0
ELSE NVL (GMSAC.BURDENED_COST, 0)
END) DIR_EXP,
SUM (CASE SUBSTR (et.expenditure_category, 1, 11)
WHEN 'F & A Costs' THEN NVL (GMSAC.BURDENED_COST, 0)
ELSE 0
END) IND_EXP,
SUM (CASE SUBSTR(et.expenditure_category, 1, 20)
WHEN 'Cost Share' THEN NVL (GMSAC.BURDENED_COST, 0)
WHEN 'Cost Share-Direct' THEN NVL (GMSAC.BURDENED_COST, 0)
WHEN 'Cost Share-Indirect' THEN NVL (GMSAC.BURDENED_COST, 0)
WHEN 'Research Enhancement' THEN NVL (GMSAC.BURDENED_COST, 0)
ELSE 0
END) CS_EXP,
SUM(CASE SUBSTR (et.expenditure_category, 1, 16)
WHEN 'Program Income' THEN NVL(GMSAC.BURDENED_COST,0)
WHEN 'Program - Income' THEN NVL(GMSAC.BURDENED_COST, 0)
ELSE 0
END) PROGRAM_INCOME,
SUM (NVL (gmsac.BILLED_AMOUNT, 0)) billed,
SUM (NVL (gmsac.REVENUE_AMOUNT, 0)) rev
FROM tams.tams_gms_status_actuals gmsac
JOIN apps.gms_awards_all gmsa
ON gmsa.AWARD_ID = gmsac.award_id
LEFT OUTER
JOIN apps.pa_expenditure_types et
ON et.EXPENDITURE_TYPE = gmsac.expenditure_type
LEFT OUTER
JOIN apps.PA_COST_DISTRIBUTION_LINES_ALL cdl
ON cdl.EXPENDITURE_ITEM_ID = gmsac.expenditure_item_id
AND cdl.project_id = gmsac.project_id
AND cdl.task_id = gmsac.task_id
AND cdl.line_num = '1'
LEFT OUTER
JOIN apps.gl_code_combinations gcc
ON gcc.CODE_COMBINATION_ID = cdl.dr_CODE_COMBINATION_ID
WHERE gmsac.pa_date BETWEEN TO_DATE (SUBSTR ('##1', 1, 10), 'YYYY/MM/DD')
AND TO_DATE (SUBSTR ('##2', 1, 10), 'YYYY/MM/DD')
AND gcc.segment3 BETWEEN NVL('##7', gcc.segment3)
AND NVL('##8', gcc.segment3)
GROUP BY gmsa.award_id,
gmsac.project_id,
gmsac.task_id) e
ON e.project_id = t.project_id
AND e.task_id = t.task_id
AND e.award_id = a.award_id
LEFT OUTER
-- Breakdown of funding amounts
JOIN (SELECT gi.award_id,
project_id,
pfs.task_id,
SUM (pfs.FUNDING_AMOUNT) funding
FROM apps.gms_project_fundings pfs
JOIN apps.gms_installments gi
ON gi.installment_id = pfs.installment_id
WHERE pfs.installment_id IN
(SELECT MAX (g.INSTALLMENT_ID)
FROM apps.gms_installments g
JOIN apps.gms_awards_all ga
ON ga.award_id = g.award_id
WHERE g.installment_id = pfs.installment_id)
GROUP BY gi.award_id,
pfs.project_id,
pfs.task_id) f
ON f.project_id = t.project_id
AND f.task_id = t.task_id
AND f.award_id = a.award_id
LEFT OUTER
JOIN apps.PA_PROJECT_PARTIES pp1
ON pp1.PROJECT_ID = p.PROJECT_ID
AND pp1.PROJECT_ROLE_ID = 1
AND pp1.start_date_active <= sysdate
AND NVL (pp1.END_DATE_ACTIVE, sysdate) >= sysdate
LEFT OUTER
JOIN apps.per_people_f m
ON m.PERSON_ID = pp1.RESOURCE_SOURCE_ID
JOIN apps.PA_PROJECT_PARTIES pp2
ON pp2.PROJECT_ID = p.PROJECT_ID
AND pp2.PROJECT_ROLE_ID = 1004
AND pp2.start_date_active <= sysdate
AND NVL (pp2.END_DATE_ACTIVE,sysdate) >= sysdate
LEFT OUTER
JOIN apps.gms_personnel gp
ON gp.award_role ='AM'
AND gp.award_id = a.award_id
JOIN apps.per_people_f fp
ON fp.person_id = gp.person_id
JOIN apps.per_people_f tm
ON tm.PERSON_ID = t.TASK_MANAGER_PERSON_ID
LEFT OUTER
JOIN apps.pa_project_classes c1
ON c1.PROJECT_ID = p.PROJECT_ID
AND c1.CLASS_CATEGORY = 'Expense Code'
LEFT OUTER
JOIN apps.pa_project_classes c2
ON c2.PROJECT_ID = p.PROJECT_ID
AND c2.CLASS_CATEGORY = 'OMB A-21'
LEFT OUTER
JOIN apps.pa_project_classes c3
ON c3.PROJECT_ID = p.PROJECT_ID
AND c3.CLASS_CATEGORY = 'Revenue Line'
LEFT OUTER
JOIN apps.pa_project_classes c4
ON c4.PROJECT_ID = p.PROJECT_ID
AND c4.CLASS_CATEGORY = 'Burden Rate'
LEFT OUTER
JOIN apps.pa_project_classes c5
ON c5.PROJECT_ID = p.PROJECT_ID
AND c5.CLASS_CATEGORY = 'Burden Structure'
LEFT OUTER
JOIN apps.pa_project_classes c6
ON c6.PROJECT_ID = p.PROJECT_ID
AND c6.CLASS_CATEGORY = 'Site'
LEFT OUTER
JOIN apps.pa_project_classes c7
ON c7.PROJECT_ID = p.PROJECT_ID
AND c7.CLASS_CATEGORY = 'Sponsor'
LEFT OUTER
JOIN apps.pa_project_classes c8
ON c8.PROJECT_ID = p.PROJECT_ID
AND c8.CLASS_CATEGORY = 'Type'
LEFT OUTER
JOIN apps.PA_IND_RATE_SCHEDULES_ALL_BG i
ON i.IND_RATE_SCH_ID = a.IDC_SCHEDULE_ID
ORDER BY 2;
Question: How would I utilize the hint use_hash in the report? Any suggestions?
Anne
|
|
|
|
|
|
|
|
| Re: Hints USE_HASH (merged) [message #396456 is a reply to message #395193] |
Mon, 06 April 2009 17:13 |
coleing
Messages: 213
Registered: February 2008
|
Senior Member |
|
|
"Hints are (ab)used by those who think they are smarter than CBO"
Agreed. If you dont know wh you are using them, then you shouldnt be using them.
Although a very simple rule of thumb is that if you are reading more then 25% of a table via nested loops, then a hash join *may* be faster and more efficient in an IO limited subsystem.
The question should be answered why the CBO feels that doing a hash join is sub-optimal compared to an NL join though. This is a matter of tracing the query using SQL*Trace and TK*Prof to find out the choices made.
|
|
|
|
|
|
| Re: Hints USE_HASH [message #396669 is a reply to message #396501] |
Tue, 07 April 2009 09:26 |
Anne Simms
Messages: 163
Registered: May 2002
Location: San Francisco, CA
|
Senior Member |
|
|
I just need to understand how the use the USE_HASH in my report. See my prior postings in this same topic.
Thanks
Anne
|
|
|
|
|
|
|
|
| Re: Hints USE_HASH (merged) [message #396792 is a reply to message #395193] |
Wed, 08 April 2009 01:26 |
coleing
Messages: 213
Registered: February 2008
|
Senior Member |
|
|
Repeat:-
Although a very simple rule of thumb is that if you are reading more then 25% of a table via nested loops, then a hash join *may* be faster and more efficient in an IO limited subsystem.
The question should be answered why the CBO feels that doing a hash join is sub-optimal compared to an NL join though. This is a matter of tracing the query using SQL*Trace and TK*Prof to find out the choices made.
In addition to this, look at using the FULL hint along with the USE_HASH hint.
But as part of your tuning, you should find out how big each table is, and how many rows from that table you need to read to achieve the results. We cannot help with that.
That will dictate any hinting required.
|
|
|
|
| Re: Hints USE_HASH (merged) [message #396945 is a reply to message #396721] |
Wed, 08 April 2009 07:58 |
Anne Simms
Messages: 163
Registered: May 2002
Location: San Francisco, CA
|
Senior Member |
|
|
Ok, well please provide a suggestion. I have already attached the explain plan, what suggestions can you give me from that? Let's forget about hints then, what suggestions do you have??
Anne
|
|
|
|
| Re: Hints USE_HASH [message #399767 is a reply to message #395467] |
Thu, 23 April 2009 22:49 |
scottwmackey
Messages: 515
Registered: March 2005
|
Senior Member |
|
|
I don't know if you need this anymore, but I like to do inline views with this syntax. It's more of an aesthetic thing than anything else, but I find it helps break up the queries and the just seem easier for me to deal with. If you hashes turn out to be necessary, you can treat each part as an autonomous unit.
WITH b AS (SELECT ag.award_id
,gra.project_id
,gra.task_id
,gra.budget_version_id
,SUM(nvl(bl.burdened_cost, 0)) budget_total
,SUM(CASE
WHEN substr(r.NAME, 1, 10) IN ('Award Reve', 'F & A Cost') THEN nvl(bl.burdened_cost, 0)
ELSE 0
END) indirect_budget
,SUM(CASE
WHEN substr(r.NAME, 1, 10) IN ('Award Reve', 'F & A Cost', 'Cost Share') THEN 0
ELSE nvl(bl.burdened_cost, 0)
END) direct_budget
,SUM(CASE substr(r.NAME, 1, 10)
WHEN 'Cost Share' THEN nvl(bl.burdened_cost, 0)
ELSE 0
END) cs_budget
FROM apps.gms_resource_assignments gra
JOIN apps.gms_budget_versions bv ON bv.project_id = gra.project_id
AND bv.budget_version_id = gra.budget_version_id
JOIN apps.gms_budget_lines bl ON bl.resource_assignment_id = gra.resource_assignment_id
JOIN apps.pa_resource_list_members rlm ON rlm.resource_list_member_id = gra.resource_list_member_id
JOIN apps.pa_resources r ON r.resource_id = rlm.resource_id
JOIN apps.gms_awards_all ag ON ag.award_id = bv.award_id
WHERE 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 ag.award_id, gra.project_id, gra.task_id, gra.budget_version_id)
,ct AS (SELECT /*+ USE_HASH(pct gmb gma)*/ -- You could do this if you think it will work, but the CBO tends to be pretty good at stuff like this
gma.award_id
,pct.project_id
,pct.task_id
,SUM(nvl(pct.tot_cmt_burdened_cost, 0)) tot_commit
,SUM(CASE WHEN pct.expenditure_category IN ('F & A Costs', 'Cost Share') THEN 0
ELSE nvl(pct.tot_cmt_burdened_cost, 0)
END) dir_commit
,SUM(CASE pct.expenditure_category
WHEN 'F & A Costs' THEN nvl(pct.tot_cmt_burdened_cost, 0)
ELSE 0
END) ind_commit
,SUM(CASE pct.expenditure_category
WHEN 'Cost Share' THEN nvl(pct.tot_cmt_burdened_cost, 0)
ELSE 0
END) cs_commit
FROM apps.pa_commitment_txns pct
JOIN apps.gms_awards_basic_v gmb ON gmb.project_id = pct.project_id
AND gmb.task_id = pct.task_id
JOIN apps.gms_awards_all gma ON gma.award_id = gmb.award_id
GROUP BY gma.award_id, pct.project_id, pct.task_id)
,e1 AS (SELECT gmsa.award_id
,gmsac.project_id
,gmsac.task_id
,SUM(CASE
WHEN gmsac.expenditure_type LIKE 'IDC%' THEN 0
ELSE nvl(gmsac.burdened_cost, 0)
END) dir_exp
,SUM(CASE
WHEN et.expenditure_category LIKE 'F & A Costs%' THEN nvl(gmsac.burdened_cost, 0)
ELSE 0
END) ind_exp
,SUM(CASE
WHEN regexp_like(et.expenditure_category, '^Cost Share(-(D|Ind)irect)*$')
OR et.expenditure_category = 'Research Enhancement' THEN nvl(gmsac.burdened_cost, 0)
ELSE 0
END) cs_exp
,SUM(CASE substr(et.expenditure_category, 1, 16)
WHEN regexp_like(et.expenditure_category, '^Program (- )*Income$') THEN nvl(gmsac.burdened_cost, 0)
ELSE 0
END) program_income
,SUM(nvl(gmsac.billed_amount, 0)) billed
,SUM(nvl(gmsac.revenue_amount, 0)) rev
FROM tams.tams_gms_status_actuals gmsac
JOIN apps.gms_awards_all gmsa ON gmsa.award_id = gmsac.award_id
LEFT JOIN apps.pa_expenditure_types et ON et.expenditure_type = gmsac.expenditure_type
LEFT JOIN apps.pa_cost_distribution_lines_all cdl ON cdl.expenditure_item_id = gmsac.expenditure_item_id
AND cdl.project_id = gmsac.project_id
AND cdl.task_id = gmsac.task_id
AND cdl.line_num = '1'
LEFT JOIN apps.gl_code_combinations gcc ON gcc.code_combination_id = cdl.dr_code_combination_id
WHERE gmsac.pa_date BETWEEN to_date(substr('##1', 1, 10), 'YYYY/MM/DD') AND to_date(substr('##2', 1, 10), 'YYYY/MM/DD')
AND gcc.segment3 BETWEEN nvl('##7', gcc.segment3) AND nvl('##8', gcc.segment3)
GROUP BY gmsa.award_id, gmsac.project_id, gmsac.task_id)
,e AS (SELECT e1.*
,dir_exp + ind_exp + cs_exp + program_income total_exp
FROM e1)
,f (SELECT gi.award_id
,project_id
,pfs.task_id
,SUM(pfs.funding_amount) funding
FROM apps.gms_project_fundings pfs
JOIN apps.gms_installments gi ON gi.installment_id = pfs.installment_id
WHERE pfs.installment_id IN (SELECT MAX(g.installment_id)
FROM apps.gms_installments g
JOIN apps.gms_awards_all ga ON ga.award_id = g.award_id
WHERE g.installment_id = pfs.installment_id)
GROUP BY gi.award_id, pfs.project_id, pfs.task_id)
SELECT 'Project report ##s_date - ##e_date, Project Organizations ##3 - ##4, Projects ##5 - ##6 and Accounts ##7 - ##8'
,0 sort_col
FROM dual
UNION
SELECT '##err_message'
,1
FROM dual
UNION
SELECT 'Project Number^Project Start Date^Project End Date^Status^Project Manager^Task Number^' ||
'Task Start Date^Task Completion Date^Task Manager^Award Number^Award Short Name^Project Organization^' ||
'Task Organization^Expense Code^OMB Code^Revenue Line^Burden Rate^Burden Structure^Site^Sponsor^Type^Customer^' ||
'Award Type^Award Purpose^Federal Flow Thru Code^IDC Schedule Name^Total Expenditure^Direct Charges^' ||
'Indirect Charges^Cost Share Charges^Total Commitments^Direct Commitments^Indirect Commitments^Cost Share Commitments^' ||
'Program Income^Total Invoiced^Total Revenue^Total Funding^' ||
'Total Budget^Direct Budget^Indirect Budget^Cost Share Budget'
,2
FROM dual
UNION
SELECT /*+ USE_HASH(a p t ol dl o2 c b ct e f ppl)*/ --You can include as many as you will need
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.program_income || '^' || e.billed || '^' || e.rev || '^' || f.funding || '^' || b.budget_total || '^' ||
b.direct_budget || '^' || b.indirect_budget || '^' || b.cs_budget
,3
FROM apps.gms_awards_all a
JOIN apps.pa_projects_all p ON p.project_type = 'Sponsored'
AND p.segment1 BETWEEN nvl('##5', p.segment1) AND nvl('##6', p.segment1)
JOIN apps.pa_tasks t ON t.project_id = p.project_id
JOIN apps.hr_all_organization_units o1 ON o1.organization_id = p.carrying_out_organization_id
JOIN apps.pa_segment_value_lookups d1 ON d1.segment_value_lookup = o1.NAME
AND d1.segment_value BETWEEN nvl('##3', d1.segment_value) AND nvl('##4', d1.segment_value)
JOIN apps.hr_all_organization_units o2 ON o2.organization_id = t.carrying_out_organization_id
LEFT OUTER JOIN apps.ra_customers c ON c.customer_id = a.funding_source_id
LEFT JOIN b ON b.project_id = t.project_id
AND b.task_id = t.task_id
AND b.award_id = a.award_id
LEFT JOIN ct ON ct.project_id = t.project_id
AND ct.task_id = t.task_id
AND ct.award_id = a.award_id
LEFT JOIN e ON e.project_id = t.project_id
AND e.task_id = t.task_id
AND e.award_id = a.award_id
LEFT JOIN f ON f.project_id = t.project_id
AND f.task_id = t.task_id
AND f.award_id = a.award_id
LEFT JOIN apps.pa_project_parties pp1 ON pp1.project_id = p.project_id
AND pp1.project_role_id = 1
AND pp1.start_date_active <= SYSDATE
AND nvl(pp1.end_date_active, SYSDATE) >= SYSDATE
LEFT JOIN apps.per_people_f m ON m.person_id = pp1.resource_source_id
JOIN apps.pa_project_parties pp2 ON pp2.project_id = p.project_id
AND pp2.project_role_id = 1004
AND pp2.start_date_active <= SYSDATE
AND nvl(pp2.end_date_active, SYSDATE) >= SYSDATE
LEFT JOIN apps.gms_personnel gp ON gp.award_role = 'AM'
AND gp.award_id = a.award_id
JOIN apps.per_people_f fp ON fp.person_id = gp.person_id
JOIN apps.per_people_f tm ON tm.person_id = t.task_manager_person_id
LEFT JOIN apps.pa_project_classes c1 ON c1.project_id = p.project_id
AND c1.class_category = 'Expense Code'
LEFT JOIN apps.pa_project_classes c2 ON c2.project_id = p.project_id
AND c2.class_category = 'OMB A-21'
LEFT JOIN apps.pa_project_classes c3 ON c3.project_id = p.project_id
AND c3.class_category = 'Revenue Line'
LEFT JOIN apps.pa_project_classes c4 ON c4.project_id = p.project_id
AND c4.class_category = 'Burden Rate'
LEFT JOIN apps.pa_project_classes c5 ON c5.project_id = p.project_id
AND c5.class_category = 'Burden Structure'
LEFT JOIN apps.pa_project_classes c6 ON c6.project_id = p.project_id
AND c6.class_category = 'Site'
LEFT JOIN apps.pa_project_classes c7 ON c7.project_id = p.project_id
AND c7.class_category = 'Sponsor'
LEFT JOIN apps.pa_project_classes c8 ON c8.project_id = p.project_id
AND c8.class_category = 'Type'
LEFT JOIN apps.pa_ind_rate_schedules_all_bg i ON i.ind_rate_sch_id = a.idc_schedule_id
ORDER BY 2;
|
|
|
|
|
|
Goto Forum:
Current Time: Wed Jun 26 11:42:55 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
