Home » SQL & PL/SQL » SQL & PL/SQL » Combination required from code
Combination required from code [message #268455] Tue, 18 September 2007 13:19 Go to next message
Anne Simms
Messages: 163
Registered: May 2002
Location: San Francisco, CA
Senior Member
Hi All,

I am trying to get a specific combination (Project, Task and Award level detail) meaning detailed data level of information broken down in the following order: Project Info, Task info and Award Information for a sql plus report. The format has to be in comma delimmited. Can someone please look at the report detail and let me know if I am missing something in order to get the detail I am looking for (Project, Task and Award combination). I have also attached a copy of the report.


/*  PAMSPAXP.sql  PAMS Project Expenditures Report 
Program takes six 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 
*/
set termout off
set head off
set pages 0
set feedback off
set verify off
set lines 2000
set define #

col start_date noprint new_value s_date
col	end_date   noprint new_value e_date
col	err_msg	   noprint new_value err_message
col sort_col noprint

SELECT  s.start_date,
		e.end_date,
		decode(sign(e.end_date - s.start_date),-1,'*** Note:  From Period is later than To Period, no expenditures reported ***') err_msg
FROM	apps.pa_periods s,
		apps.pa_periods e
WHERE  	s.period_name(+) = '##1'
and		e.period_name(+) = '##2';

set termout on

 
SELECT 'PAMS Project Expenditures for Dates ##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^Responsible Accountant^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^'||
	   '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.billed||'^'||
	   e.rev||'^'||
	   f.funding||'^'||
	   b.budget_total||'^'||
	   b.direct_budget||'^'||
	   b.indirect_budget||'^'||
	   b.cs_budget,
	   3
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	tams.tams_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 '##s_date'  AND '##e_date'
and		gcc.CODE_COMBINATION_ID  = cdl.dr_CODE_COMBINATION_ID
and		gcc.segment3 between nvl('##7',gcc.segment3) and nvl('##8',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('##5',p.segment1) and nvl('##6',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('##3',d1.segment_value) and nvl('##4',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: Combination required from code [message #273008 is a reply to message #268455] Mon, 08 October 2007 11:13 Go to previous messageGo to next message
tomstone_98
Messages: 44
Registered: May 2007
Location: Atlanta
Member
do you still need an answer for this?

did you write this code or was it created by a report writer?

Re: Combination required from code [message #273444 is a reply to message #273008] Wed, 10 October 2007 09:05 Go to previous message
Anne Simms
Messages: 163
Registered: May 2002
Location: San Francisco, CA
Senior Member
Yes, still looking for some advice on this.


Previous Topic: Function
Next Topic: How many column we can create in an sql table
Goto Forum:
  


Current Time: Thu Dec 08 00:22:27 CST 2016

Total time taken to generate the page: 0.08455 seconds