Help with Subquery

[szeiss]

Using 10G, I'm trying to use a cursor in the /* Obligated */ area below to check to see if a certain year is in the budget_history table, if it's not then I use the bud_stru_29_lvl_2 table. But I keep getting errors. BTW, this is only a portion of the code

CURSOR c1 IS
SELECT 'Y'
FROM cip.budget_period13_afs2
WHERE ROWNUM < 2
AND fiscalyear = TO_NUMBER(v_cfy-1);

BEGIN
-- Really don't need to set all these, but makes it easier to read ---
v_cur_yr := TO_CHAR (p_fy);
v_cfy := TO_CHAR (p_cfy);
v_yr1 := TO_CHAR (p_fy - 2);
v_yr2 := TO_CHAR (p_fy - 1);
v_yr3 := TO_CHAR (p_fy - 3);

IF p_fy >= p_cfy THEN
SELECT DISTINCT
cip.budget_month_faoao_afs2.MONTH
INTO v_month
FROM cip.budget_month_faoao_afs2;
ELSE
v_month := '12';
END IF;

IF p_permission = 'true' THEN
IF p_mgrempid <> 'All' THEN
INSERT INTO OM_REPORT_COMBSUM_MANAGER(
fund,
agency,
orgn,
manager,
object_cd,
typename,
actuals,
budget,
ytd_expenses,
cye,
basic,
suppreduct_amt,
username)
(select distinct
FDUO.fund,
FDUO.agency,
FDUrg,
FDUO.fullname,
FDUbject_cd,
FDUO.type_name,
sum(nvl(PFY_Obligated.PFYObligated,0)) PFYObligated,
sum(nvl(CFY_Budget.CFYBudget,0)) CFYBudget,
sum(nvl(CFY_AsLastMonthEnd.CFYAsLastMonthEnd,0)) CFYAsLastMonthEnd,
sum(nvl(CYE_TBL.CYE,0)) CYE,
sum(nvl(Proposed_TBL.Proposed,0)) Proposed,
sum(nvl(Suppreduct_TBL.Suppreduct,0)) Suppreduct,
p_username
FROM
(select a.fund, a.agency, a.org, a.object_cd, c.fullname, d.type_name
from cip.budget_period13_afs2 a, om.om_faos b, cip.list_employees c, om.om_objectcd_types d
where a.fund = b.FUND
and a.agency = b.AGENCY
and a.org = b.ORG
and b.EMPID_DIVISIONMANAGER = p_empid
and a.OBJECT_CD = d.OBJECT_CD
and b.FY = p_fy
and (a.FUND, a.agency) in (select distinct e.fund, e.agency
from om.om_list_deptagencies e
where e.DEPT_CD = p_dept_cd)
and a.fiscalyear = v_yr1
UNION
select a.fund_cd, a.dept_cd, a.unit_cd, a.obj_cd, c.fullname, d.type_name
from aims36.BUD_STRU_29_LVL_2 a, om.om_faos b, cip.list_employees c, om.om_objectcd_types d
where a.fund_cd = b.FUND
and a.dept_cd = b.AGENCY
and a.unit_cd = b.ORG
and b.EMPID_DIVISIONMANAGER = p_empid
and a.obj_cd = d.OBJECT_CD
and b.FY = p_fy
and a.bfy = v_cfy
and (a.fund_cd, a.dept_cd) in (select distinct e.fund, e.agency
from om.om_list_deptagencies e
where e.DEPT_CD = p_dept_cd)
UNION
select a.fund, a.agency, a.org, a.object_cd, c.fullname, d.type_name
from cip.budget_history_afs2 a, om.om_faos b, cip.list_employees c, om.om_objectcd_types d
where a.fund = b.FUND
and a.agency = b.AGENCY
and a.org = b.ORG
and b.EMPID_DIVISIONMANAGER = p_empid
and a.object_cd = d.OBJECT_CD
and b.FY = p_fy
and a.fiscalyear = v_cfy
and (a.fund, a.agency) in (select distinct e.fund, e.agency
from om.om_list_deptagencies e
where e.DEPT_CD = p_dept_cd)
UNION
select a.fund, a.agency, a.org, b.object_cd, c.fullname, d.type_name
from om_faos a, om_phaseobject b, cip.list_employees c, om.om_objectcd_types d
where a.om_fao_id = b.om_fao_id
and a.fy between v_cfy and p_fy
and (a.fund, a.agency) in (select distinct e.fund, e.agency
from om.om_list_deptagencies e
where e.DEPT_CD = p_dept_cd)
and a.EMPID_DIVISIONMANAGER = p_empid
and b.object_cd = d.OBJECT_CD
) FDUO,

/* Obligated */
-- IF p_fy > 2006 THEN
-- OPEN c1;
-- FETCH c1 INTO in_result;
-- IF c1%NOTFOUND THEN
-- CLOSE c1;
(select
a.fund_cd,
a.dept_cd,
a.unit_cd,
a.obj_cd,
round((sum(a.ACTU_EXP_AM)+sum(a.ENC_AM)),0) PFYObligated
from aims36.bud_stru_29_lvl_2 a
where (a.fund_cd, a.dept_cd) in (select distinct e.fund, e.agency
from om.om_list_deptagencies e
where e.DEPT_CD = p_dept_cd)
and a.BFY = TO_NUMBER(v_yr1)
having sum(a.ACTU_EXP_AM)+sum(a.ENC_AM) <> 0
group by a.fund_cd, a.dept_cd, a.unit_cd, a.obj_cd) PFY_Obligated,
-- ELSE
-- (select
-- fund,
-- agency,
-- org,
-- object_cd,
-- round((sum(YTDEXPENSE)+sum(ENCUMBERANCE)),0) PFYObligated
-- from cip.budget_period13_afs2
-- where (fund, agency) in (select distinct e.fund, e.agency
-- from om.om_list_deptagencies e
-- where e.DEPT_CD = p_dept_cd)
-- and fiscalyear = v_yr1
-- having sum(YTDEXPENSE)+sum(ENCUMBERANCE) <> 0
-- group by fund, agency, org, object_cd) PFY_Obligated,
-- END IF;
-- END IF;

Thanks,
Sherry

 

[SantaMufasa]

Sherry,

Would you prefer that we guess what errors you are receiving? It would save us tons of time to know the errors, preferrably from a copy-and-paste of the actual error message immediately following the attempted run.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[szeiss]

sorry...
ERROR line 142, col 12, ending_line 142, ending_col 12, Found ';', Expecting: CROSS FULL INNER LEFT NATURAL PARTITION RIGHT -or- , JOIN WHERE -or- CONNECT GROUP HAVING MODEL START -or- INTERSECT MINUS UNION -or- )

 

[szeiss]

Line 142 is the beginning of the 'If p_fy....' statement.

/* Obligated */
-- IF p_fy > 2006 THEN
-- OPEN c1;
-- FETCH c1 INTO in_result;
-- IF c1%NOTFOUND THEN
-- CLOSE c1;
(select
a.fund_cd,
a.dept_cd,
a.unit_cd,
a.obj_cd,
round((sum(a.ACTU_EXP_AM)+sum(a.ENC_AM)),0) PFYObligated
from aims36.bud_stru_29_lvl_2 a
where (a.fund_cd, a.dept_cd) in (select distinct e.fund, e.agency
from om.om_list_deptagencies e
where e.DEPT_CD = p_dept_cd)
and a.BFY = TO_NUMBER(v_yr1)
having sum(a.ACTU_EXP_AM)+sum(a.ENC_AM) <> 0
group by a.fund_cd, a.dept_cd, a.unit_cd, a.obj_cd) PFY_Obligated,
-- ELSE
-- (select
-- fund,
-- agency,
-- org,
-- object_cd,
-- round((sum(YTDEXPENSE)+sum(ENCUMBERANCE)),0) PFYObligated
-- from cip.budget_period13_afs2
-- where (fund, agency) in (select distinct e.fund, e.agency
-- from om.om_list_deptagencies e
-- where e.DEPT_CD = p_dept_cd)
-- and fiscalyear = v_yr1
-- having sum(YTDEXPENSE)+sum(ENCUMBERANCE) <> 0
-- group by fund, agency, org, object_cd) PFY_Obligated,
-- END IF;
-- END IF;

 

[SantaMufasa]

Sherry,

I see multiple issues with the code that you posted:[ul][li]Since the "SELECT..." statement you posted is not within either an explicit or implicit CURSOR, PL/SQL syntax requires that you SELECT expressions INTO receiving fields, for example:

select a.fund_cd, 
       a.dept_cd, 
       a.unit_cd, 
       a.obj_cd,
       round((sum(a.ACTU_EXP_AM)+sum(a.ENC_AM)),0) PFYObligated
[b]INTO   v_dept_cd, v_unit_cd, v_obj_cd, v_PFYObligated...[/b]

[/li][li]Your SELECT statement ends with a comma, which tells PL/SQL "there's more to follow".[/li][li]You began the SELECT with a paren "(SELECT...", yet there is no closing paren.[/li][/ul]These are a few issues to start with, but since we don't know what you are trying to accomplish (programmatically), we can respond only to syntax remediation.


If you are using SQL*Plus to run your code, it would be helpful (following your receiving the error message) to give the SQL*Plus command, "LIST", which lists the code along with line numbers. Please copy and paste the error message, followed by the code (at least for a few lines above and a few lines below the offending line number(s)).

Let us know how things progress,


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[SantaMufasa]

...Plus...with the commented-out code you posted, I cannot see the ";" that PL/SQL is complaining about.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[szeiss]

This is it...

Error was:
ERROR line 141, col 17, ending_line 141, ending_col 17, Found '>', Expecting: CROSS FULL INNER LEFT NATURAL PARTITION RIGHT -or- , JOIN WHERE -or- CONNECT GROUP HAVING MODEL START -or- INTERSECT MINUS UNION -or- )



1) FUNCTION Afs3_Rpt_Cs_Manager_TEST (

2) p_fy NUMBER,

3) p_fyminus1 NUMBER,

4) p_fyminus2 NUMBER,

5) p_cfy NUMBER,

6) p_phase NUMBER,

7) p_empid VARCHAR2,

8) p_dept_cd VARCHAR2,

9) p_deptname VARCHAR2,

10) p_username VARCHAR2,

11) p_cye VARCHAR2,

12) p_group VARCHAR2,

13) p_phasedesc VARCHAR2,

14) p_permission VARCHAR2,

15) p_mgrempid VARCHAR2)

16) RETURN NUMBER

17) IS

18) /******************************************************************************************

19) *

20) * Filename:
21) * Author:
22) * Date:
23) *

24) * Data is inserted into a table, om_report_combsum_manager, and is identified by USERNAME.

25) *

26) ******************************************************************************************/

27) v_cur_yr VARCHAR2 (4);

28) v_cfy VARCHAR2 (4);

29) v_yr1 VARCHAR2 (4);

30) v_yr2 VARCHAR2 (4);

31) v_yr3 VARCHAR2 (4);

32) v_month VARCHAR2(15);

33) in_result VARCHAR2(2);

34)

35)

36) CURSOR c1 IS

37) SELECT 'Y'

38) FROM cip.budget_period13_afs2

39) WHERE ROWNUM < 2

40) AND fiscalyear = TO_NUMBER(v_cfy-1);

41)

42) BEGIN

43) -- Really don't need to set all these, but makes it easier to read ---

44) v_cur_yr := TO_CHAR (p_fy);

45) v_cfy := TO_CHAR (p_cfy);

46) v_yr1 := TO_CHAR (p_fy - 2);

47) v_yr2 := TO_CHAR (p_fy - 1);

48) v_yr3 := TO_CHAR (p_fy - 3);

49)

50) IF p_fy >= p_cfy THEN

51) SELECT DISTINCT

52) cip.budget_month_faoao_afs2.MONTH

53) INTO v_month

54) FROM cip.budget_month_faoao_afs2;

55) ELSE

56) v_month := '12';

57) END IF;

58)

59) IF p_permission = 'true' THEN /* permission is true */

60) IF p_mgrempid <> 'All' THEN /* this is a single manager */

61) INSERT INTO OM_REPORT_COMBSUM_MANAGER(

62) fund,

63) agency,

64) orgn,

65) manager,

66) object_cd,

67) typename,

68) actuals,

69) budget,

70) ytd_expenses,

71) cye,

72) basic,

73) suppreduct_amt,

74) username)

75) (select distinct

76) FDUO.fund,

77) FDUO.agency,

78) FDUrg,

79) FDUO.fullname,

80) FDUbject_cd,

81) FDUO.type_name,

82) sum(nvl(PFY_Obligated.PFYObligated,0)) PFYObligated,

83) sum(nvl(CFY_Budget.CFYBudget,0)) CFYBudget,

84) sum(nvl(CFY_AsLastMonthEnd.CFYAsLastMonthEnd,0)) CFYAsLastMonthEnd,

85) sum(nvl(CYE_TBL.CYE,0)) CYE,

86) sum(nvl(Proposed_TBL.Proposed,0)) Proposed,

87) sum(nvl(Suppreduct_TBL.Suppreduct,0)) Suppreduct,

88) p_username

89) FROM

90) (select a.fund, a.agency, a.org, a.object_cd, c.fullname, d.type_name

91) from cip.budget_period13_afs2 a, om.om_faos b, cip.list_employees c, om.om_objectcd_types d

92) where a.fund = b.FUND

93) and a.agency = b.AGENCY

94) and a.org = b.ORG

95) and b.EMPID_DIVISIONMANAGER = p_empid

96) and a.OBJECT_CD = d.OBJECT_CD

97) and b.FY = p_fy

98) and (a.FUND, a.agency) in (select distinct e.fund, e.agency

99) from om.om_list_deptagencies e

100) where e.DEPT_CD = p_dept_cd)

101) and a.fiscalyear = v_yr1

102) UNION

103) select a.fund_cd, a.dept_cd, a.unit_cd, a.obj_cd, c.fullname, d.type_name

104) from aims36.BUD_STRU_29_LVL_2 a, om.om_faos b, cip.list_employees c, om.om_objectcd_types d

105) where a.fund_cd = b.FUND

106) and a.dept_cd = b.AGENCY

107) and a.unit_cd = b.ORG

108) and b.EMPID_DIVISIONMANAGER = p_empid

109) and a.obj_cd = d.OBJECT_CD

110) and b.FY = p_fy

111) and a.bfy = v_cfy

112) and (a.fund_cd, a.dept_cd) in (select distinct e.fund, e.agency

113) from om.om_list_deptagencies e

114) where e.DEPT_CD = p_dept_cd)

115) UNION

116) select a.fund, a.agency, a.org, a.object_cd, c.fullname, d.type_name

117) from cip.budget_history_afs2 a, om.om_faos b, cip.list_employees c, om.om_objectcd_types d

118) where a.fund = b.FUND

119) and a.agency = b.AGENCY

120) and a.org = b.ORG

121) and b.EMPID_DIVISIONMANAGER = p_empid

122) and a.object_cd = d.OBJECT_CD

123) and b.FY = p_fy

124) and a.fiscalyear = v_cfy

125) and (a.fund, a.agency) in (select distinct e.fund, e.agency

126) from om.om_list_deptagencies e

127) where e.DEPT_CD = p_dept_cd)

128) UNION

129) select a.fund, a.agency, a.org, b.object_cd, c.fullname, d.type_name

130) from om_faos a, om_phaseobject b, cip.list_employees c, om.om_objectcd_types d

131) where a.om_fao_id = b.om_fao_id

132) and a.fy between v_cfy and p_fy

133) and (a.fund, a.agency) in (select distinct e.fund, e.agency

134) from om.om_list_deptagencies e

135) where e.DEPT_CD = p_dept_cd)

136) and a.EMPID_DIVISIONMANAGER = p_empid

137) and b.object_cd = d.OBJECT_CD

138) ) FDUO,

139)

140) /* Obligated */

141) IF p_fy > 2006 THEN

142) OPEN c1;

143) FETCH c1 INTO in_result;

144) IF c1%NOTFOUND THEN

145) CLOSE c1;

146) IF p_fy > 2006 THEN

147) (select

148) a.fund_cd,

149) a.dept_cd,

150) a.unit_cd,

151) a.obj_cd,

152) round((sum(a.ACTU_EXP_AM)+sum(a.ENC_AM)),0) PFYObligated

153) from aims36.bud_stru_29_lvl_2 a

154) where (a.fund_cd, a.dept_cd) in (select distinct e.fund, e.agency

155) from om.om_list_deptagencies e

156) where e.DEPT_CD = p_dept_cd)

157) and a.BFY = TO_NUMBER(v_yr1)

158) having sum(a.ACTU_EXP_AM)+sum(a.ENC_AM) <> 0

159) group by a.fund_cd, a.dept_cd, a.unit_cd, a.obj_cd) PFY_Obligated,

160) ELSE

161) (select

162) fund,

163) agency,

164) org,

165) object_cd,

166) round((sum(YTDEXPENSE)+sum(ENCUMBERANCE)),0) PFYObligated

167) from cip.budget_period13_afs2

168) where (fund, agency) in (select distinct e.fund, e.agency

169) from om.om_list_deptagencies e

170) where e.DEPT_CD = p_dept_cd)

171) and fiscalyear = v_yr1

172) having sum(YTDEXPENSE)+sum(ENCUMBERANCE) <> 0

173) group by fund, agency, org, object_cd) PFY_Obligated,

174) END IF;

175) -- END IF;

176)

177) /* Budget */

178) (select a.FUND_CD, a.dept_cd, a.unit_cd, a.obj_cd,

179) sum(a.CURR_BUD_AM) CFYBudget

180) from aims36.BUD_STRU_29_LVL_2 a

181) where a.bfy = v_cfy

182) and (a.FUND_CD, a.dept_cd) in (select distinct e.fund, e.agency

183) from om.om_list_deptagencies e

184) where e.DEPT_CD = p_dept_cd)

185) having sum(a.CURR_BUD_AM)+sum(a.ENC_AM)+sum(a.ACTU_EXP_AM) <> 0

186) group by a.FUND_CD, a.dept_cd, a.unit_cd, a.obj_cd) CFY_Budget,

187)

188) /* As of Month End */

189) (select fund, agency, org, object_cd,

190) round((sum(qtr1)+sum(qtr2)+sum(qtr3)+sum(qtr4)),0) CFYAsLastMonthEnd

191) from cip.budget_history_afs2 a

192) where fiscalyear = v_cfy

193) and (fund, agency) in (select distinct e.fund, e.agency

194) from om.om_list_deptagencies e

195) where e.DEPT_CD = p_dept_cd)

196) group by fund, agency, org, object_cd) CFY_AsLastMonthEnd,

197)

198) /* CYE */

199) (select a.fund, a.agency, a.org, b.object_cd,

200) sum(amount) cye

201) from om_faos a, om_phaseobject b, om_phaseobject_lineitem_cye c

202) where a.om_fao_id = b.om_fao_id

203) and b.phaseobject_id = c.phaseobject_id

204) and b.phase_id = p_phase

205) and a.fy = v_cfy

206) and (a.fund, a.agency) in (select distinct e.fund, e.agency

207) from om.om_list_deptagencies e

208) where e.DEPT_CD = p_dept_cd)

209) group by a.fund, a.agency, a.org, b.object_cd) CYE_TBL,

210)

211) /* Proposed */

212) (select a.fund, a.agency, a.org, b.object_cd,

213) sum(AMOUNT) Proposed

214) from om_faos a, om_phaseobject b, om_phaseobject_lineitem_budget c

215) where a.om_fao_id = b.om_fao_id

216) and b.phaseobject_id = c.phaseobject_id

217) and (a.fund, a.agency) in (select distinct e.fund, e.agency

218) from om.om_list_deptagencies e

219) where e.DEPT_CD = p_dept_cd)

220) and a.fy = p_fy

221) and b.phase_id = p_phase

222) group by a.fund, a.agency, org, object_cd

223) order by a.fund, a.agency, org, object_cd) Proposed_TBL,

224)

225) /* Suppreduct */

226) (select a.fund, a.agency, a.org, b.object_cd,

227) sum(AMOUNT) Suppreduct

228) from om_faos a, om_phaseobject b, om_phaseobject_lineitem_budget c

229) where a.om_fao_id = b.om_fao_id

230) and b.phaseobject_id = c.phaseobject_id

231) and (a.fund, a.agency) in (select distinct e.fund, e.agency

232) from om.om_list_deptagencies e

233) where e.DEPT_CD = p_dept_cd)

234) and a.fy = p_fy

235) and b.phase_id = p_phase

236) and c.suppreduct = 'Y'

237) group by a.fund, a.agency, org, object_cd

238) order by a.fund, a.agency, org, object_cd) Suppreduct_TBL

239)

240)

241) WHERE

242) FDUO.fund = PFY_Obligated.fund_cd(+)

243) AND FDUO.agency = PFY_Obligated.dept_cd(+)

244) AND FDUrg = PFY_Obligated.unit_cd(+)

245) AND FDUbject_cd = PFY_Obligated.obj_cd(+)

246)

247) AND FDUO.fund = CFY_Budget.fund_cd(+)

248) AND FDUO.agency = CFY_Budget.dept_cd(+)

249) AND FDUrg = CFY_Budget.unit_cd(+)

250) AND FDUbject_cd = CFY_Budget.obj_cd(+)

251)

252) AND FDUO.fund = CFY_AsLastMonthEnd.fund(+)

253) AND FDUO.agency = CFY_AsLastMonthEnd.agency(+)

254) AND FDUrg = CFY_AsLastMonthEnd.org(+)

255) AND FDUbject_cd = CFY_AsLastMonthEnd.object_cd(+)

256)

257) AND FDUO.fund = CYE_TBL.fund(+)

258) AND FDUO.agency = CYE_TBL.agency(+)

259) AND FDUrg = CYE_TBL.org(+)

260) AND FDUbject_cd = CYE_TBL.object_cd(+)

261)

262) AND FDUO.fund = Proposed_TBL.fund(+)

263) AND FDUO.agency = Proposed_TBL.agency(+)

264) AND FDUrg = Proposed_TBL.org(+)

265) AND FDUbject_cd = Proposed_TBL.object_cd(+)

266)

267) AND FDUO.fund = Suppreduct_TBL.fund(+)

268) AND FDUO.agency = Suppreduct_TBL.agency(+)

269) AND FDUrg = Suppreduct_TBL.org(+)

270) AND FDUbject_cd = Suppreduct_TBL.object_cd(+)

271)

272) AND (nvl(PFY_Obligated.PFYObligated,0) <> 0

273) OR nvl(CFY_Budget.CFYBudget,0) <> 0

274) OR nvl(CFY_AsLastMonthEnd.CFYAsLastMonthEnd,0) <> 0

275) OR nvl(CYE_TBL.CYE,0) <> 0

276) OR nvl(Proposed_TBL.Proposed,0) <> 0

277) OR nvl(Suppreduct_TBL.Suppreduct,0) <> 0 )

278)

279) GROUP BY

280) FDUO.fullname,

281) FDUO.type_name,

282) FDUbject_cd,

283) FDUO.fund,

284) FDUO.agency,

285) FDUrg,

286) PFY_Obligated.PFYObligated,

287) CFY_Budget.CFYBudget,

288) CFY_AsLastMonthEnd.CFYAsLastMonthEnd,

289) CYE_TBL.CYE,

290) Proposed_TBL.Proposed,

291) Suppreduct_TBL.Suppreduct);

292)

293) ELSE /* this is all managers */

294) INSERT INTO OM_REPORT_COMBSUM_MANAGER(

295) fund,

296) agency,

297) orgn,

298) manager,

299) object_cd,

300) typename,

301) actuals,

302) budget,

303) ytd_expenses,

304) cye,

305) basic,

306) suppreduct_amt,

307) username)

308) (select distinct

309) FDUO.fund,

310) FDUO.agency,

311) FDUrg,

312) FDUO.fullname,

313) FDUbject_cd,

314) FDUO.type_name,

315) sum(nvl(PFY_Obligated.PFYObligated,0)) PFYObligated,

316) sum(nvl(CFY_Budget.CFYBudget,0)) CFYBudget,

317) sum(nvl(CFY_AsLastMonthEnd.CFYAsLastMonthEnd,0)) CFYAsLastMonthEnd,

318) sum(nvl(CYE_TBL.CYE,0)) CYE,

319) sum(nvl(Proposed_TBL.Proposed,0)) Proposed,

320) sum(nvl(Suppreduct_TBL.Suppreduct,0)) Suppreduct,

321) p_username

322) FROM

323) (select a.fund, a.agency, a.org, a.object_cd, c.fullname, d.type_name

324) from cip.budget_period13_afs2 a, om.om_faos b, cip.list_employees c, om.om_objectcd_types d

325) where a.fund = b.FUND

326) and a.agency = b.AGENCY

327) and a.org = b.ORG

328) and b.EMPID_DIVISIONMANAGER = c.EMPLOYEE_ID

329) and a.OBJECT_CD = d.OBJECT_CD

330) and b.FY = p_fy

331) and (a.FUND, a.agency) in (select distinct e.fund, e.agency

332) from om.om_list_deptagencies e

333) where e.DEPT_CD = p_dept_cd)

334) and a.fiscalyear = v_yr1

335) UNION

336) select a.fund_cd, a.dept_cd, a.unit_cd, a.obj_cd, c.fullname, d.type_name

337) from aims36.BUD_STRU_29_LVL_2 a, om.om_faos b, cip.list_employees c, om.om_objectcd_types d

338) where a.fund_cd = b.FUND

339) and a.dept_cd = b.AGENCY

340) and a.unit_cd = b.ORG

341) and b.EMPID_DIVISIONMANAGER = c.EMPLOYEE_ID

342) and a.obj_cd = d.OBJECT_CD

343) and b.FY = p_fy

344) and a.bfy = v_cfy

345) and (a.fund_cd, a.dept_cd) in (select distinct e.fund, e.agency

346) from om.om_list_deptagencies e

347) where e.DEPT_CD = p_dept_cd)

348) UNION

349) select a.fund, a.agency, a.org, a.object_cd, c.fullname, d.type_name

350) from cip.budget_history_afs2 a, om.om_faos b, cip.list_employees c, om.om_objectcd_types d

351) where a.fund = b.FUND

352) and a.agency = b.AGENCY

353) and a.org = b.ORG

354) and b.EMPID_DIVISIONMANAGER = c.EMPLOYEE_ID

355) and a.object_cd = d.OBJECT_CD

356) and b.FY = p_fy

357) and a.fiscalyear = v_cfy

358) and (a.fund, a.agency) in (select distinct e.fund, e.agency

359) from om.om_list_deptagencies e

360) where e.DEPT_CD = p_dept_cd)

361) UNION

362) select a.fund, a.agency, a.org, b.object_cd, c.fullname, d.type_name

363) from om_faos a, om_phaseobject b, cip.list_employees c, om.om_objectcd_types d

364) where a.om_fao_id = b.om_fao_id

365) and a.fy between v_cfy and p_fy

366) and (a.fund, a.agency) in (select distinct e.fund, e.agency

367) from om.om_list_deptagencies e

368) where e.DEPT_CD = p_dept_cd)

369) and a.EMPID_DIVISIONMANAGER = c.EMPLOYEE_ID

370) and b.object_cd = d.OBJECT_CD

371) ) FDUO,

372)

373) /* Obligated */

374) -- IF p_fy > 2006 THEN

375) -- OPEN c1;

376) -- FETCH c1 INTO in_result;

377) -- IF c1%NOTFOUND THEN

378) -- CLOSE c1;

379) (select

380) a.fund_cd,

381) a.dept_cd,

382) a.unit_cd,

383) a.obj_cd,

384) round((sum(a.ACTU_EXP_AM)+sum(a.ENC_AM)),0) PFYObligated

385) from aims36.bud_stru_29_lvl_2 a

386) where (a.fund_cd, a.dept_cd) in (select distinct e.fund, e.agency

387) from om.om_list_deptagencies e

388) where e.DEPT_CD = p_dept_cd)

389) and a.BFY = TO_NUMBER(v_yr1)

390) having sum(a.ACTU_EXP_AM)+sum(a.ENC_AM) <> 0

391) group by a.fund_cd, a.dept_cd, a.unit_cd, a.obj_cd) PFY_Obligated,

392) -- ELSE

393) -- (select

394) -- fund,

395) -- agency,

396) -- org,

397) -- object_cd,

398) -- round((sum(YTDEXPENSE)+sum(ENCUMBERANCE)),0) PFYObligated

399) -- from cip.budget_period13_afs2

400) -- where (fund, agency) in (select distinct e.fund, e.agency

401) -- from om.om_list_deptagencies e

402) -- where e.DEPT_CD = p_dept_cd)

403) -- and fiscalyear = v_yr1

404) -- having sum(YTDEXPENSE)+sum(ENCUMBERANCE) <> 0

405) -- group by fund, agency, org, object_cd) PFY_Obligated,

406) -- END IF;

407) -- END IF;

408)

409) /* Budget */

410) (select a.FUND_CD, a.dept_cd, a.unit_cd, a.obj_cd,

411) sum(a.CURR_BUD_AM) CFYBudget

412) from aims36.BUD_STRU_29_LVL_2 a

413) where a.bfy = v_cfy

414) and (a.FUND_CD, a.dept_cd) in (select distinct e.fund, e.agency

415) from om.om_list_deptagencies e

416) where e.DEPT_CD = p_dept_cd)

417) having sum(a.CURR_BUD_AM)+sum(a.ENC_AM)+sum(a.ACTU_EXP_AM) <> 0

418) group by a.FUND_CD, a.dept_cd, a.unit_cd, a.obj_cd) CFY_Budget,

419)

420) /* As of Month End */

421) (select fund, agency, org, object_cd,

422) round((sum(qtr1)+sum(qtr2)+sum(qtr3)+sum(qtr4)),0) CFYAsLastMonthEnd

423) from cip.budget_history_afs2 a

424) where fiscalyear = v_cfy

425) and (fund, agency) in (select distinct e.fund, e.agency

426) from om.om_list_deptagencies e

427) where e.DEPT_CD = p_dept_cd)

428) group by fund, agency, org, object_cd) CFY_AsLastMonthEnd,

429)

430) /* CYE */

431) (select a.fund, a.agency, a.org, b.object_cd,

432) sum(amount) cye

433) from om_faos a, om_phaseobject b, om_phaseobject_lineitem_cye c

434) where a.om_fao_id = b.om_fao_id

435) and b.phaseobject_id = c.phaseobject_id

436) and b.phase_id = p_phase

437) and a.fy = v_cfy

438) and (a.fund, a.agency) in (select distinct e.fund, e.agency

439) from om.om_list_deptagencies e

440) where e.DEPT_CD = p_dept_cd)

441) group by a.fund, a.agency, a.org, b.object_cd) CYE_TBL,

442)

443) /* Proposed */

444) (select a.fund, a.agency, a.org, b.object_cd,

445) sum(AMOUNT) Proposed

446) from om_faos a, om_phaseobject b, om_phaseobject_lineitem_budget c

447) where a.om_fao_id = b.om_fao_id

448) and b.phaseobject_id = c.phaseobject_id

449) and (a.fund, a.agency) in (select distinct e.fund, e.agency

450) from om.om_list_deptagencies e

451) where e.DEPT_CD = p_dept_cd)

452) and a.fy = p_fy

453) and b.phase_id = p_phase

454) group by a.fund, a.agency, org, object_cd

455) order by a.fund, a.agency, org, object_cd) Proposed_TBL,

456)

457) /* Suppreduct */

458) (select a.fund, a.agency, a.org, b.object_cd,

459) sum(AMOUNT) Suppreduct

460) from om_faos a, om_phaseobject b, om_phaseobject_lineitem_budget c

461) where a.om_fao_id = b.om_fao_id

462) and b.phaseobject_id = c.phaseobject_id

463) and (a.fund, a.agency) in (select distinct e.fund, e.agency

464) from om.om_list_deptagencies e

465) where e.DEPT_CD = p_dept_cd)

466) and a.fy = p_fy

467) and b.phase_id = p_phase

468) and c.suppreduct = 'Y'

469) group by a.fund, a.agency, org, object_cd

470) order by a.fund, a.agency, org, object_cd) Suppreduct_TBL

471)

472)

473) WHERE

474) FDUO.fund = PFY_Obligated.fund_cd(+)

475) AND FDUO.agency = PFY_Obligated.dept_cd(+)

476) AND FDUrg = PFY_Obligated.unit_cd(+)

477) AND FDUbject_cd = PFY_Obligated.obj_cd(+)

478)

479) AND FDUO.fund = CFY_Budget.fund_cd(+)

480) AND FDUO.agency = CFY_Budget.dept_cd(+)

481) AND FDUrg = CFY_Budget.unit_cd(+)

482) AND FDUbject_cd = CFY_Budget.obj_cd(+)

483)

484) AND FDUO.fund = CFY_AsLastMonthEnd.fund(+)

485) AND FDUO.agency = CFY_AsLastMonthEnd.agency(+)

486) AND FDUrg = CFY_AsLastMonthEnd.org(+)

487) AND FDUbject_cd = CFY_AsLastMonthEnd.object_cd(+)

488)

489) AND FDUO.fund = CYE_TBL.fund(+)

490) AND FDUO.agency = CYE_TBL.agency(+)

491) AND FDUrg = CYE_TBL.org(+)

492) AND FDUbject_cd = CYE_TBL.object_cd(+)

493)

494) AND FDUO.fund = Proposed_TBL.fund(+)

495) AND FDUO.agency = Proposed_TBL.agency(+)

496) AND FDUrg = Proposed_TBL.org(+)

497) AND FDUbject_cd = Proposed_TBL.object_cd(+)

498)

499) AND FDUO.fund = Suppreduct_TBL.fund(+)

500) AND FDUO.agency = Suppreduct_TBL.agency(+)

501) AND FDUrg = Suppreduct_TBL.org(+)

502) AND FDUbject_cd = Suppreduct_TBL.object_cd(+)

503)

504) AND (nvl(PFY_Obligated.PFYObligated,0) <> 0

505) OR nvl(CFY_Budget.CFYBudget,0) <> 0

506) OR nvl(CFY_AsLastMonthEnd.CFYAsLastMonthEnd,0) <> 0

507) OR nvl(CYE_TBL.CYE,0) <> 0

508) OR nvl(Proposed_TBL.Proposed,0) <> 0

509) OR nvl(Suppreduct_TBL.Suppreduct,0) <> 0 )

510)

511) GROUP BY

512) FDUO.fullname,

513) FDUO.type_name,

514) FDUbject_cd,

515) FDUO.fund,

516) FDUO.agency,

517) FDUrg,

518) PFY_Obligated.PFYObligated,

519) CFY_Budget.CFYBudget,

520) CFY_AsLastMonthEnd.CFYAsLastMonthEnd,

521) CYE_TBL.CYE,

522) Proposed_TBL.Proposed,

523) Suppreduct_TBL.Suppreduct);

524)

525) END IF; /* close single or all managers */

526)

527) ELSE /* permission is false */

528)

529) INSERT INTO OM_REPORT_COMBSUM_MANAGER(

530) fund,

531) agency,

532) orgn,

533) manager,

534) object_cd,

535) typename,

536) actuals,

537) budget,

538) ytd_expenses,

539) cye,

540) basic,

541) suppreduct_amt,

542) username)

543) (select distinct

544) FDUO.fund,

545) FDUO.agency,

546) FDUrg,

547) FDUO.fullname,

548) FDUbject_cd,

549) FDUO.type_name,

550) sum(nvl(PFY_Obligated.PFYObligated,0)) PFYObligated,

551) sum(nvl(CFY_Budget.CFYBudget,0)) CFYBudget,

552) sum(nvl(CFY_AsLastMonthEnd.CFYAsLastMonthEnd,0)) CFYAsLastMonthEnd,

553) sum(nvl(CYE_TBL.CYE,0)) CYE,

554) sum(nvl(Proposed_TBL.Proposed,0)) Proposed,

555) sum(nvl(Suppreduct_TBL.Suppreduct,0)) Suppreduct,

556) p_username

557) FROM

558) (select a.fund, a.agency, a.org, a.object_cd, c.fullname, d.type_name

559) from cip.budget_period13_afs2 a, om.om_faos b, cip.list_employees c, om.om_objectcd_types d, om.om_users_orgs e

560) where a.fund = b.FUND

561) and a.agency = b.AGENCY

562) and a.org = b.ORG

563) and b.fund = e.FUND

564) and b.agency = e.AGENCY

565) and b.org = e.org

566) and e.employee_id = p_empid

567) and a.OBJECT_CD = d.OBJECT_CD

568) and c.employee_id = e.employee_id

569) and b.FY = p_fy

570) and a.fiscalyear = v_yr1

571) UNION

572) select a.fund_cd, a.dept_cd, a.unit_cd, a.obj_cd, c.fullname, d.type_name

573) from aims36.BUD_STRU_29_LVL_2 a, om.om_faos b, cip.list_employees c, om.om_objectcd_types d, om.om_users_orgs e

574) where a.fund_cd = b.FUND

575) and a.dept_cd = b.AGENCY

576) and a.unit_cd = b.ORG

577) and b.fund = e.FUND

578) and b.agency = e.AGENCY

579) and b.org = e.org

580) and e.employee_id = p_empid

581) and c.employee_id = e.employee_id

582) and a.obj_cd = d.OBJECT_CD

583) and b.FY = p_fy

584) and a.bfy = v_cfy

585) UNION

586) select a.fund, a.agency, a.org, a.object_cd, c.fullname, d.type_name

587) from cip.budget_history_afs2 a, om.om_faos b, cip.list_employees c, om.om_objectcd_types d, om.om_users_orgs e

588) where a.fund = b.FUND

589) and a.agency = b.AGENCY

590) and a.org = b.ORG

591) and b.fund = e.FUND

592) and b.agency = e.AGENCY

593) and b.org = e.org

594) and e.employee_id = p_empid

595) and c.employee_id = e.employee_id

596) and a.object_cd = d.OBJECT_CD

597) and b.FY = p_fy

598) and a.fiscalyear = v_cfy

599) UNION

600) select a.fund, a.agency, a.org, b.object_cd, c.fullname, d.type_name

601) from om_faos a, om_phaseobject b, cip.list_employees c, om.om_objectcd_types d, om.om_users_orgs e

602) where a.om_fao_id = b.om_fao_id

603) and a.fy between v_cfy and p_fy

604) and a.fund = e.FUND

605) and a.agency = e.AGENCY

606) and a.org = e.org

607) and e.employee_id = p_empid

608) and c.employee_id = e.employee_id

609) and b.object_cd = d.OBJECT_CD

610) ) FDUO,

611)

612) /* Obligated */

613) -- IF p_fy > 2006 THEN

614) -- OPEN c1;

615) -- FETCH c1 INTO in_result;

616) -- IF c1%NOTFOUND THEN

617) -- CLOSE c1;

618) (select

619) a.fund_cd,

620) a.dept_cd,

621) a.unit_cd,

622) a.obj_cd,

623) round((sum(a.ACTU_EXP_AM)+sum(a.ENC_AM)),0) PFYObligated

624) from aims36.bud_stru_29_lvl_2 a

625) where a.BFY = TO_NUMBER(v_yr1)

626) having sum(a.ACTU_EXP_AM)+sum(a.ENC_AM) <> 0

627) group by a.fund_cd, a.dept_cd, a.unit_cd, a.obj_cd) PFY_Obligated,

628) -- ELSE

629) -- (select

630) -- fund,

631) -- agency,

632) -- org,

633) -- object_cd,

634) -- round((sum(YTDEXPENSE)+sum(ENCUMBERANCE)),0) PFYObligated

635) -- from cip.budget_period13_afs2

636) -- where fund = om_report_combsum_manager.fund

637) -- and agency = om_report_combsum_manager.agency

638) -- and org = om_report_combsum_manager.org

639) -- and object_cd = om_report_combsum_manager.object_cd

640) -- and fiscalyear = v_yr1

641) -- having sum(YTDEXPENSE)+sum(ENCUMBERANCE) <> 0

642) -- group by fund, agency, org, object_cd) PFY_Obligated,

643) -- END IF;

644) -- END IF;

645)

646) /* Budget */

647) (select a.FUND_CD, a.dept_cd, a.unit_cd, a.obj_cd,

648) sum(a.CURR_BUD_AM) CFYBudget

649) from aims36.BUD_STRU_29_LVL_2 a

650) where a.bfy = v_cfy

651) having sum(a.CURR_BUD_AM)+sum(a.ENC_AM)+sum(a.ACTU_EXP_AM) <> 0

652) group by a.FUND_CD, a.dept_cd, a.unit_cd, a.obj_cd) CFY_Budget,

653)

654) /* As of Month End */

655) (select fund, agency, org, object_cd,

656) round((sum(qtr1)+sum(qtr2)+sum(qtr3)+sum(qtr4)),0) CFYAsLastMonthEnd

657) from cip.budget_history_afs2 a

658) where fiscalyear = v_cfy

659)

660) group by fund, agency, org, object_cd) CFY_AsLastMonthEnd,

661)

662) /* CYE */

663) (select a.fund, a.agency, a.org, b.object_cd,

664) sum(amount) cye

665) from om_faos a, om_phaseobject b, om_phaseobject_lineitem_cye c

666) where a.om_fao_id = b.om_fao_id

667) and b.phaseobject_id = c.phaseobject_id

668) and b.phase_id = p_phase

669) and a.fy = v_cfy

670) group by a.fund, a.agency, a.org, b.object_cd) CYE_TBL,

671)

672) /* Proposed */

673) (select a.fund, a.agency, a.org, b.object_cd,

674) sum(AMOUNT) Proposed

675) from om_faos a, om_phaseobject b, om_phaseobject_lineitem_budget c

676) where a.om_fao_id = b.om_fao_id

677) and b.phaseobject_id = c.phaseobject_id

678) and a.fy = p_fy

679) and b.phase_id = p_phase

680) group by a.fund, a.agency, org, object_cd

681) order by a.fund, a.agency, org, object_cd) Proposed_TBL,

682)

683) /* Suppreduct */

684) (select a.fund, a.agency, a.org, b.object_cd,

685) sum(AMOUNT) Suppreduct

686) from om_faos a, om_phaseobject b, om_phaseobject_lineitem_budget c

687) where a.om_fao_id = b.om_fao_id

688) and b.phaseobject_id = c.phaseobject_id

689) and a.fy = p_fy

690) and b.phase_id = p_phase

691) and c.suppreduct = 'Y'

692) group by a.fund, a.agency, org, object_cd

693) order by a.fund, a.agency, org, object_cd) Suppreduct_TBL

694)

695)

696) WHERE

697) FDUO.fund = PFY_Obligated.fund_cd(+)

698) AND FDUO.agency = PFY_Obligated.dept_cd(+)

699) AND FDUrg = PFY_Obligated.unit_cd(+)

700) AND FDUbject_cd = PFY_Obligated.obj_cd(+)

701)

702) AND FDUO.fund = CFY_Budget.fund_cd(+)

703) AND FDUO.agency = CFY_Budget.dept_cd(+)

704) AND FDUrg = CFY_Budget.unit_cd(+)

705) AND FDUbject_cd = CFY_Budget.obj_cd(+)

706)

707) AND FDUO.fund = CFY_AsLastMonthEnd.fund(+)

708) AND FDUO.agency = CFY_AsLastMonthEnd.agency(+)

709) AND FDUrg = CFY_AsLastMonthEnd.org(+)

710) AND FDUbject_cd = CFY_AsLastMonthEnd.object_cd(+)

711)

712) AND FDUO.fund = CYE_TBL.fund(+)

713) AND FDUO.agency = CYE_TBL.agency(+)

714) AND FDUrg = CYE_TBL.org(+)

715) AND FDUbject_cd = CYE_TBL.object_cd(+)

716)

717) AND FDUO.fund = Proposed_TBL.fund(+)

718) AND FDUO.agency = Proposed_TBL.agency(+)

719) AND FDUrg = Proposed_TBL.org(+)

720) AND FDUbject_cd = Proposed_TBL.object_cd(+)

721)

722) AND FDUO.fund = Suppreduct_TBL.fund(+)

723) AND FDUO.agency = Suppreduct_TBL.agency(+)

724) AND FDUrg = Suppreduct_TBL.org(+)

725) AND FDUbject_cd = Suppreduct_TBL.object_cd(+)

726)

727) AND (nvl(PFY_Obligated.PFYObligated,0) <> 0

728) OR nvl(CFY_Budget.CFYBudget,0) <> 0

729) OR nvl(CFY_AsLastMonthEnd.CFYAsLastMonthEnd,0) <> 0

730) OR nvl(CYE_TBL.CYE,0) <> 0

731) OR nvl(Proposed_TBL.Proposed,0) <> 0

732) OR nvl(Suppreduct_TBL.Suppreduct,0) <> 0 )

733)

734) GROUP BY

735) FDUO.fullname,

736) FDUO.type_name,

737) FDUbject_cd,

738) FDUO.fund,

739) FDUO.agency,

740) FDUrg,

741) PFY_Obligated.PFYObligated,

742) CFY_Budget.CFYBudget,

743) CFY_AsLastMonthEnd.CFYAsLastMonthEnd,

744) CYE_TBL.CYE,

745) Proposed_TBL.Proposed,

746) Suppreduct_TBL.Suppreduct);

747)

748)

749) END IF; /* close permissions */

750)

751)

752) COMMIT;

753) RETURN 0;

754) END;

 

[SantaMufasa]

Sherry,

First, has your code been successfully running previously? Because I see multiple issues.

The problem that PL/SQL has with your line 142 results from the previous non-commented line of code, line 138, ") FDUO,". As I mentioned earlier, SQL infers from the comma that "there is more to come for the previous command line." Instead of the comma, I believe that you want a ";" to indicate that the INSERT is complete.

Furthermore, the SELECT statement that feeds the INSERT statement is, I believe, problematic. The subquery contains aggregate functions on lines 82 thru 88, which cause the non-aggregated expressions on lines 76 thru 81 to require appearance in a GROUP BY clause. I don't see a GROUP BY clause that mentions those non-aggregate expressions.

Let us know how things go once you have addressed these issues.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[szeiss]

Actually this will compile without errors if I take out the cursor code. The insert actually goes thru line 291. My problems began when I put the cursor in.

Sherry

 

[SantaMufasa]

The insert actually goes thru line 291.

I have never seen Oracle PL/SQL syntax that allows in INSERT statement to be interrupted with PL/SQL syntax as you have done here:

61)         INSERT INTO OM_REPORT_COMBSUM_MANAGER(
...
75)         (select distinct
...
138)                 ) FDUO,
139) 
140)         /* Obligated */
141)         IF p_fy > 2006 THEN
142)             OPEN c1;
143)             FETCH c1 INTO in_result;
144)             IF c1%NOTFOUND THEN
145)                 CLOSE c1;
146)             IF p_fy > 2006 THEN
147)                 (select
...
291)             ...);

Is there anyone that can enlightend my on how this could work?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]