[Home] [Help]
PACKAGE BODY: APPS.PSB_BUDGET_GROUPS_PVT
Source
1 package body PSB_BUDGET_GROUPS_PVT as
2 /* $Header: PSBVBGPB.pls 120.8 2006/01/30 04:20:11 viraghun ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):= 'PSB_BUDGET_GROUPS_PVT';
5
6 /*For Bug No : 2230514 Start*/
7 --g_debug_flag VARCHAR2(1) := 'N';
8 /*For Bug No : 2230514 End*/
9
10 TYPE TokNameArray IS TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER;
11
12 -- TokValArray contains values for all tokens
13
14 TYPE TokValArray IS TABLE OF VARCHAR2(1000) INDEX BY BINARY_INTEGER;
15
16 TYPE g_budgetgroup_rec_type IS RECORD
17 (budget_group_id NUMBER,
18 name VARCHAR2(80),
19 parent_budget_group_id NUMBER,
20 effective_start_date DATE,
21 effective_end_date DATE);
22
23 TYPE g_budgetgroup_tbl_type is TABLE OF g_budgetgroup_rec_type
24 INDEX BY BINARY_INTEGER;
25
26 cursor c_BudgetGroup (RootBudgetGroup_ID NUMBER) is
27 select budget_group_id,
28 name,
29 parent_budget_group_id,
30 set_of_books_id,
31 business_group_id,
32 budget_group_category_set_id,
33 ps_account_position_set_id,
34 nps_account_position_set_id,
35 effective_start_date,
36 effective_end_date
37 from PSB_BUDGET_GROUPS
38 where budget_group_type = 'R'
39 start with budget_group_id = RootBudgetGroup_ID
40 connect by prior budget_group_id = parent_budget_group_id;
41
42 cursor c_AccSet (BudgetGroup_ID NUMBER) is
43 select account_position_set_id,
44 name,
45 effective_start_date,
46 effective_end_date
47 from psb_set_relations_v
48 where account_or_position_type = 'A'
49 and budget_group_id = BudgetGroup_ID;
50
51 -- .. cursor of overlap ccid in psb_budget_accounts
52 -- .. used in val_hierarchy and account_range_overlap procs
53 -- .. to test duplicate account overlap
54 --
55
56 cursor c_Overlap_CCID (RootBudgetGroup_ID NUMBER,
57 BudgetGroup_ID NUMBER,
58 AccSet_ID NUMBER,
59 Start_Date DATE,
60 End_Date DATE) is
61 select a.code_combination_id
62 from psb_budget_accounts a,
63 psb_set_relations c
64 where a.account_position_set_id = c.account_position_set_id
65 and ((((End_Date is not null)
66 and ((c.effective_start_date <= End_Date)
67 and (c.effective_end_date is null))
68 or ((c.effective_start_date between Start_Date and End_Date)
69 or (c.effective_end_date between Start_Date and End_Date)
70 or ((c.effective_start_date < Start_Date)
71 and (c.effective_end_date > End_Date)))))
72 or ((End_Date is null)
73 and (nvl(c.effective_end_date, Start_Date) >= Start_Date)))
74 /* for bug no 3824989 */
75 -- and c.account_position_set_id <> AccSet_ID
76 and c.budget_group_id <> BudgetGroup_ID
77 /*For Bug No : 2255402 Start*/
78 and exists
79 (select 1
80 from psb_budget_groups
81 where budget_group_id = c.budget_group_id
82 and (budget_group_id = RootBudgetGroup_ID
83 or root_budget_group_id = RootBudgetGroup_ID))
84 /*
85 and c.budget_group_id in
86 (select budget_group_id
87 from psb_budget_groups
88 where (budget_group_id = RootBudgetGroup_ID
89 or root_budget_group_id = RootBudgetGroup_ID))
90 */
91 /*For Bug No : 2255402 End*/
92 and exists
93 (select 1
94 from psb_budget_accounts b
95 where a.code_combination_id = b.code_combination_id
96 and b.account_position_set_id = AccSet_ID);
97
98
99 /* For bug 4991981 --> Removed the view psb_set_relations_v
100 and used the table psb_set_relations. Also substituted
101 bind variables for literals */
102
103 cursor c_Overlap_Account_Range (RootBudgetGroup_ID NUMBER,
104 BudgetGroup_ID NUMBER,
105 AccSet_ID NUMBER,
106 Start_Date DATE,
107 End_Date DATE,
108 Inc_Type IN VARCHAR2 DEFAULT 'I',
109 Def_Seg IN VARCHAR2 DEFAULT 'X') is
110 select 'ACCOUNT RANGE OVERLAP'
111 from psb_account_position_set_lines cmp,
112 psb_account_position_set_lines lst,
113 psb_set_relations a
114 where (
115 NVL(cmp.SEGMENT30_LOW,Def_Seg) <= NVL(lst.SEGMENT30_HIGH,Def_Seg)
116 AND NVL(cmp.SEGMENT30_HIGH,Def_Seg) >= NVL(lst.SEGMENT30_LOW,Def_Seg)
117 AND NVL(cmp.SEGMENT29_LOW,Def_Seg) <= NVL(lst.SEGMENT29_HIGH,Def_Seg)
118 AND NVL(cmp.SEGMENT29_HIGH,Def_Seg) >= NVL(lst.SEGMENT29_LOW,Def_Seg)
119 AND NVL(cmp.SEGMENT28_LOW,Def_Seg) <= NVL(lst.SEGMENT28_HIGH,Def_Seg)
120 AND NVL(cmp.SEGMENT28_HIGH,Def_Seg) >= NVL(lst.SEGMENT28_LOW,Def_Seg)
121 AND NVL(cmp.SEGMENT27_LOW,Def_Seg) <= NVL(lst.SEGMENT27_HIGH,Def_Seg)
122 AND NVL(cmp.SEGMENT27_HIGH,Def_Seg) >= NVL(lst.SEGMENT27_LOW,Def_Seg)
123 AND NVL(cmp.SEGMENT26_LOW,Def_Seg) <= NVL(lst.SEGMENT26_HIGH,Def_Seg)
124 AND NVL(cmp.SEGMENT26_HIGH,Def_Seg) >= NVL(lst.SEGMENT26_LOW,Def_Seg)
125 AND NVL(cmp.SEGMENT25_LOW,Def_Seg) <= NVL(lst.SEGMENT25_HIGH,Def_Seg)
126 AND NVL(cmp.SEGMENT25_HIGH,Def_Seg) >= NVL(lst.SEGMENT25_LOW,Def_Seg)
127 AND NVL(cmp.SEGMENT24_LOW,Def_Seg) <= NVL(lst.SEGMENT24_HIGH,Def_Seg)
128 AND NVL(cmp.SEGMENT24_HIGH,Def_Seg) >= NVL(lst.SEGMENT24_LOW,Def_Seg)
129 AND NVL(cmp.SEGMENT23_LOW,Def_Seg) <= NVL(lst.SEGMENT23_HIGH,Def_Seg)
130 AND NVL(cmp.SEGMENT23_HIGH,Def_Seg) >= NVL(lst.SEGMENT23_LOW,Def_Seg)
131 AND NVL(cmp.SEGMENT22_LOW,Def_Seg) <= NVL(lst.SEGMENT22_HIGH,Def_Seg)
132 AND NVL(cmp.SEGMENT22_HIGH,Def_Seg) >= NVL(lst.SEGMENT22_LOW,Def_Seg)
133 AND NVL(cmp.SEGMENT21_LOW,Def_Seg) <= NVL(lst.SEGMENT21_HIGH,Def_Seg)
134 AND NVL(cmp.SEGMENT21_HIGH,Def_Seg) >= NVL(lst.SEGMENT21_LOW,Def_Seg)
135 AND NVL(cmp.SEGMENT20_LOW,Def_Seg) <= NVL(lst.SEGMENT20_HIGH,Def_Seg)
136 AND NVL(cmp.SEGMENT20_HIGH,Def_Seg) >= NVL(lst.SEGMENT20_LOW,Def_Seg)
137 AND NVL(cmp.SEGMENT19_LOW,Def_Seg) <= NVL(lst.SEGMENT19_HIGH,Def_Seg)
138 AND NVL(cmp.SEGMENT19_HIGH,Def_Seg) >= NVL(lst.SEGMENT19_LOW,Def_Seg)
139 AND NVL(cmp.SEGMENT18_LOW,Def_Seg) <= NVL(lst.SEGMENT18_HIGH,Def_Seg)
140 AND NVL(cmp.SEGMENT18_HIGH,Def_Seg) >= NVL(lst.SEGMENT18_LOW,Def_Seg)
141 AND NVL(cmp.SEGMENT17_LOW,Def_Seg) <= NVL(lst.SEGMENT17_HIGH,Def_Seg)
142 AND NVL(cmp.SEGMENT17_HIGH,Def_Seg) >= NVL(lst.SEGMENT17_LOW,Def_Seg)
143 AND NVL(cmp.SEGMENT16_LOW,Def_Seg) <= NVL(lst.SEGMENT16_HIGH,Def_Seg)
144 AND NVL(cmp.SEGMENT16_HIGH,Def_Seg) >= NVL(lst.SEGMENT16_LOW,Def_Seg)
145 AND NVL(cmp.SEGMENT15_LOW,Def_Seg) <= NVL(lst.SEGMENT15_HIGH,Def_Seg)
146 AND NVL(cmp.SEGMENT15_HIGH,Def_Seg) >= NVL(lst.SEGMENT15_LOW,Def_Seg)
147 AND NVL(cmp.SEGMENT14_LOW,Def_Seg) <= NVL(lst.SEGMENT14_HIGH,Def_Seg)
148 AND NVL(cmp.SEGMENT14_HIGH,Def_Seg) >= NVL(lst.SEGMENT14_LOW,Def_Seg)
149 AND NVL(cmp.SEGMENT13_LOW,Def_Seg) <= NVL(lst.SEGMENT13_HIGH,Def_Seg)
150 AND NVL(cmp.SEGMENT13_HIGH,Def_Seg) >= NVL(lst.SEGMENT13_LOW,Def_Seg)
151 AND NVL(cmp.SEGMENT12_LOW,Def_Seg) <= NVL(lst.SEGMENT12_HIGH,Def_Seg)
152 AND NVL(cmp.SEGMENT12_HIGH,Def_Seg) >= NVL(lst.SEGMENT12_LOW,Def_Seg)
153 AND NVL(cmp.SEGMENT11_LOW,Def_Seg) <= NVL(lst.SEGMENT11_HIGH,Def_Seg)
154 AND NVL(cmp.SEGMENT11_HIGH,Def_Seg) >= NVL(lst.SEGMENT11_LOW,Def_Seg)
155 AND NVL(cmp.SEGMENT10_LOW,Def_Seg) <= NVL(lst.SEGMENT10_HIGH,Def_Seg)
156 AND NVL(cmp.SEGMENT10_HIGH,Def_Seg) >= NVL(lst.SEGMENT10_LOW,Def_Seg)
157 AND NVL(cmp.SEGMENT9_LOW,Def_Seg) <= NVL(lst.SEGMENT9_HIGH,Def_Seg)
158 AND NVL(cmp.SEGMENT9_HIGH,Def_Seg) >= NVL(lst.SEGMENT9_LOW,Def_Seg)
159 AND NVL(cmp.SEGMENT8_LOW,Def_Seg) <= NVL(lst.SEGMENT8_HIGH,Def_Seg)
160 AND NVL(cmp.SEGMENT8_HIGH,Def_Seg) >= NVL(lst.SEGMENT8_LOW,Def_Seg)
161 AND NVL(cmp.SEGMENT7_LOW,Def_Seg) <= NVL(lst.SEGMENT7_HIGH,Def_Seg)
162 AND NVL(cmp.SEGMENT7_HIGH,Def_Seg) >= NVL(lst.SEGMENT7_LOW,Def_Seg)
163 AND NVL(cmp.SEGMENT6_LOW,Def_Seg) <= NVL(lst.SEGMENT6_HIGH,Def_Seg)
164 AND NVL(cmp.SEGMENT6_HIGH,Def_Seg) >= NVL(lst.SEGMENT6_LOW,Def_Seg)
165 AND NVL(cmp.SEGMENT5_LOW,Def_Seg) <= NVL(lst.SEGMENT5_HIGH,Def_Seg)
166 AND NVL(cmp.SEGMENT5_HIGH,Def_Seg) >= NVL(lst.SEGMENT5_LOW,Def_Seg)
167 AND NVL(cmp.SEGMENT4_LOW,Def_Seg) <= NVL(lst.SEGMENT4_HIGH,Def_Seg)
168 AND NVL(cmp.SEGMENT4_HIGH,Def_Seg) >= NVL(lst.SEGMENT4_LOW,Def_Seg)
169 AND NVL(cmp.SEGMENT3_LOW,Def_Seg) <= NVL(lst.SEGMENT3_HIGH,Def_Seg)
170 AND NVL(cmp.SEGMENT3_HIGH,Def_Seg) >= NVL(lst.SEGMENT3_LOW,Def_Seg)
171 AND NVL(cmp.SEGMENT2_LOW,Def_Seg) <= NVL(lst.SEGMENT2_HIGH,Def_Seg)
172 AND NVL(cmp.SEGMENT2_HIGH,Def_Seg) >= NVL(lst.SEGMENT2_LOW,Def_Seg)
173 AND NVL(cmp.SEGMENT1_LOW,Def_Seg) <= NVL(lst.SEGMENT1_HIGH,Def_Seg)
174 AND NVL(cmp.SEGMENT1_HIGH,Def_Seg) >= NVL(lst.SEGMENT1_LOW,Def_Seg)
175 )
176 and lst.account_position_set_id = AccSet_ID
177 and lst.include_or_exclude_type = Inc_Type
178 and cmp.account_position_set_id = a.account_position_set_id
179 and cmp.include_or_exclude_type = Inc_Type
180 and ((((End_Date is not null)
181 and ((a.effective_start_date <= End_Date)
182 and (a.effective_end_date is null))
183 or ((a.effective_start_date between Start_Date and End_Date)
184 or (a.effective_end_date between Start_Date and End_Date)
185 or ((a.effective_start_date < Start_Date)
186 and (a.effective_end_date > End_Date)))))
187 or ((End_Date is null)
188 and (nvl(a.effective_end_date, Start_Date) >= Start_Date)))
189 and a.account_position_set_id <> AccSet_ID
190 and exists
191 (select 1
192 from psb_budget_groups
193 where budget_group_id = a.budget_group_id
194 and (budget_group_id = RootBudgetGroup_ID
195 or root_budget_group_id = RootBudgetGroup_ID));
196
197 /* For Bug No : 2255402 Start --> added the above exist clause
198 and commented the one below
199 and a.budget_group_id in
200 (select budget_group_id
201 d from psb_budget_groups
202 where (budget_group_id = RootBudgetGroup_ID
203 or root_budget_group_id = RootBudgetGroup_ID));
204
205 For Bug No : 2255402 End*/
206
207
208 cursor c_Overlap_ps (ps_AccSet NUMBER,
209 nps_AccSet NUMBER) is
210 select 'Personnel Services and Non-Personnel Services Account Sets Overlap'
211 from dual
212 where exists
213 (select 1
214 from psb_budget_accounts a,
215 psb_budget_accounts b
216 where a.code_combination_id = b.code_combination_id
217 and a.account_position_set_id = nps_AccSet
218 and b.account_position_set_id = ps_AccSet);
219
220 cursor c_Overlap_ps_range (l_ps_AccSet NUMBER,
221 l_nps_AccSet NUMBER) is
222 select 'Personnel Services and Non-Personnel Services Account Sets Overlap'
223 from psb_account_position_set_lines ps,
224 psb_account_position_set_lines nps
225 where (
226 NVL(ps.SEGMENT30_LOW,'X') <= NVL(nps.SEGMENT30_HIGH,'X')
227 AND NVL(ps.SEGMENT30_HIGH,'X') >= NVL(nps.SEGMENT30_LOW,'X')
228 AND NVL(ps.SEGMENT29_LOW,'X') <= NVL(nps.SEGMENT29_HIGH,'X')
229 AND NVL(ps.SEGMENT29_HIGH,'X') >= NVL(nps.SEGMENT29_LOW,'X')
230 AND NVL(ps.SEGMENT28_LOW,'X') <= NVL(nps.SEGMENT28_HIGH,'X')
231 AND NVL(ps.SEGMENT28_HIGH,'X') >= NVL(nps.SEGMENT28_LOW,'X')
232 AND NVL(ps.SEGMENT27_LOW,'X') <= NVL(nps.SEGMENT27_HIGH,'X')
233 AND NVL(ps.SEGMENT27_HIGH,'X') >= NVL(nps.SEGMENT27_LOW,'X')
234 AND NVL(ps.SEGMENT26_LOW,'X') <= NVL(nps.SEGMENT26_HIGH,'X')
235 AND NVL(ps.SEGMENT26_HIGH,'X') >= NVL(nps.SEGMENT26_LOW,'X')
236 AND NVL(ps.SEGMENT25_LOW,'X') <= NVL(nps.SEGMENT25_HIGH,'X')
237 AND NVL(ps.SEGMENT25_HIGH,'X') >= NVL(nps.SEGMENT25_LOW,'X')
238 AND NVL(ps.SEGMENT24_LOW,'X') <= NVL(nps.SEGMENT24_HIGH,'X')
239 AND NVL(ps.SEGMENT24_HIGH,'X') >= NVL(nps.SEGMENT24_LOW,'X')
240 AND NVL(ps.SEGMENT23_LOW,'X') <= NVL(nps.SEGMENT23_HIGH,'X')
241 AND NVL(ps.SEGMENT23_HIGH,'X') >= NVL(nps.SEGMENT23_LOW,'X')
242 AND NVL(ps.SEGMENT22_LOW,'X') <= NVL(nps.SEGMENT22_HIGH,'X')
243 AND NVL(ps.SEGMENT22_HIGH,'X') >= NVL(nps.SEGMENT22_LOW,'X')
244 AND NVL(ps.SEGMENT21_LOW,'X') <= NVL(nps.SEGMENT21_HIGH,'X')
245 AND NVL(ps.SEGMENT21_HIGH,'X') >= NVL(nps.SEGMENT21_LOW,'X')
246 AND NVL(ps.SEGMENT20_LOW,'X') <= NVL(nps.SEGMENT20_HIGH,'X')
247 AND NVL(ps.SEGMENT20_HIGH,'X') >= NVL(nps.SEGMENT20_LOW,'X')
248 AND NVL(ps.SEGMENT19_LOW,'X') <= NVL(nps.SEGMENT19_HIGH,'X')
249 AND NVL(ps.SEGMENT19_HIGH,'X') >= NVL(nps.SEGMENT19_LOW,'X')
250 AND NVL(ps.SEGMENT18_LOW,'X') <= NVL(nps.SEGMENT18_HIGH,'X')
251 AND NVL(ps.SEGMENT18_HIGH,'X') >= NVL(nps.SEGMENT18_LOW,'X')
252 AND NVL(ps.SEGMENT17_LOW,'X') <= NVL(nps.SEGMENT17_HIGH,'X')
253 AND NVL(ps.SEGMENT17_HIGH,'X') >= NVL(nps.SEGMENT17_LOW,'X')
254 AND NVL(ps.SEGMENT16_LOW,'X') <= NVL(nps.SEGMENT16_HIGH,'X')
255 AND NVL(ps.SEGMENT16_HIGH,'X') >= NVL(nps.SEGMENT16_LOW,'X')
256 AND NVL(ps.SEGMENT15_LOW,'X') <= NVL(nps.SEGMENT15_HIGH,'X')
257 AND NVL(ps.SEGMENT15_HIGH,'X') >= NVL(nps.SEGMENT15_LOW,'X')
258 AND NVL(ps.SEGMENT14_LOW,'X') <= NVL(nps.SEGMENT14_HIGH,'X')
259 AND NVL(ps.SEGMENT14_HIGH,'X') >= NVL(nps.SEGMENT14_LOW,'X')
260 AND NVL(ps.SEGMENT13_LOW,'X') <= NVL(nps.SEGMENT13_HIGH,'X')
261 AND NVL(ps.SEGMENT13_HIGH,'X') >= NVL(nps.SEGMENT13_LOW,'X')
262 AND NVL(ps.SEGMENT12_LOW,'X') <= NVL(nps.SEGMENT12_HIGH,'X')
263 AND NVL(ps.SEGMENT12_HIGH,'X') >= NVL(nps.SEGMENT12_LOW,'X')
264 AND NVL(ps.SEGMENT11_LOW,'X') <= NVL(nps.SEGMENT11_HIGH,'X')
265 AND NVL(ps.SEGMENT11_HIGH,'X') >= NVL(nps.SEGMENT11_LOW,'X')
266 AND NVL(ps.SEGMENT10_LOW,'X') <= NVL(nps.SEGMENT10_HIGH,'X')
267 AND NVL(ps.SEGMENT10_HIGH,'X') >= NVL(nps.SEGMENT10_LOW,'X')
268 AND NVL(ps.SEGMENT9_LOW,'X') <= NVL(nps.SEGMENT9_HIGH,'X')
269 AND NVL(ps.SEGMENT9_HIGH,'X') >= NVL(nps.SEGMENT9_LOW,'X')
270 AND NVL(ps.SEGMENT8_LOW,'X') <= NVL(nps.SEGMENT8_HIGH,'X')
271 AND NVL(ps.SEGMENT8_HIGH,'X') >= NVL(nps.SEGMENT18_LOW,'X')
272 AND NVL(ps.SEGMENT7_LOW,'X') <= NVL(nps.SEGMENT7_HIGH,'X')
273 AND NVL(ps.SEGMENT7_HIGH,'X') >= NVL(nps.SEGMENT7_LOW,'X')
274 AND NVL(ps.SEGMENT6_LOW,'X') <= NVL(nps.SEGMENT6_HIGH,'X')
275 AND NVL(ps.SEGMENT6_HIGH,'X') >= NVL(nps.SEGMENT6_LOW,'X')
276 AND NVL(ps.SEGMENT5_LOW,'X') <= NVL(nps.SEGMENT5_HIGH,'X')
277 AND NVL(ps.SEGMENT5_HIGH,'X') >= NVL(nps.SEGMENT5_LOW,'X')
278 AND NVL(ps.SEGMENT4_LOW,'X') <= NVL(nps.SEGMENT4_HIGH,'X')
279 AND NVL(ps.SEGMENT4_HIGH,'X') >= NVL(nps.SEGMENT4_LOW,'X')
280 AND NVL(ps.SEGMENT3_LOW,'X') <= NVL(nps.SEGMENT3_HIGH,'X')
281 AND NVL(ps.SEGMENT3_HIGH,'X') >= NVL(nps.SEGMENT3_LOW,'X')
282 AND NVL(ps.SEGMENT2_LOW,'X') <= NVL(nps.SEGMENT2_HIGH,'X')
283 AND NVL(ps.SEGMENT2_HIGH,'X') >= NVL(nps.SEGMENT2_LOW,'X')
284 AND NVL(ps.SEGMENT1_LOW,'X') <= NVL(nps.SEGMENT1_HIGH,'X')
285 AND NVL(ps.SEGMENT1_HIGH,'X') >= NVL(nps.SEGMENT1_LOW,'X')
286 )
287 and ps.account_position_set_id = l_ps_AccSet
288 and ps.include_or_exclude_type = 'I'
289 and nps.account_position_set_id = l_nps_AccSet
290 and nps.include_or_exclude_type = 'I';
291
292 -- Characterset independent representation of chr(10)
293
294 /*For Bug No : 2230514 Start*/
295 --g_chr10 CONSTANT VARCHAR2(1) := FND_GLOBAL.Newline;
296 /*For Bug No : 2230514 End*/
297
298 -- Number of Message Tokens
299
300 no_msg_tokens NUMBER := 0;
301
302 -- Message Token Name
303
304 msg_tok_names TokNameArray;
305
306 -- Message Token Value
307
308 msg_tok_val TokValArray;
309
310 /*For Bug No : 2230514 Start*/
311 --g_dbug VARCHAR2(2000) := 'starting';
312 /*For Bug No : 2230514 End*/
313
314 /* ----------------------------------------------------------------------- */
315 /* */
316 /* Private Function Definition */
317 /* */
318 /* ----------------------------------------------------------------------- */
319
320 PROCEDURE Val_Budget_Group
321 ( p_budget_group_id IN NUMBER,
322 p_budget_group_name IN VARCHAR2,
323 p_effective_start_date IN DATE,
324 p_effective_end_date IN DATE,
325 p_return_status OUT NOCOPY VARCHAR2
326 );
327
328 PROCEDURE message_token
329 ( tokname IN VARCHAR2,
330 tokval IN VARCHAR2
331 );
332
333 PROCEDURE Output_Message_To_Table
334 ( p_budget_group_id IN NUMBER
335 );
336
337 PROCEDURE add_message
338 ( appname IN VARCHAR2,
339 msgname IN VARCHAR2
340 );
341
342 PROCEDURE Validate_PSB_Accounts_And_GL
343 ( p_top_budget_group_id IN NUMBER,
344 p_flex_code IN NUMBER,
345 p_return_status OUT NOCOPY VARCHAR2
346 );
347
348 PROCEDURE Validate_BGCCID_vs_PS_NPS
349 ( p_top_budget_group_id IN NUMBER,
350 p_flex_code IN NUMBER,
351 p_ps_account_set_id IN NUMBER,
352 p_nps_account_set_id IN NUMBER,
353 p_return_status OUT NOCOPY VARCHAR2
354 );
355
356 PROCEDURE Validate_BG_ORGANIZATION
357 ( p_top_budget_group_id IN NUMBER,
358 p_return_status OUT NOCOPY VARCHAR2
359 );
360
361 /* ----------------------------------------------------------------------- */
362
363 /*For Bug No : 2230514 Start*/
364 /*
365 PROCEDURE debug
366 ( p_message IN VARCHAR2) IS
367
368 BEGIN
369
370 if g_debug_flag = 'Y' then
371 null;
372 -- dbms_output.put_line(p_message);
373 end if;
374
375 END debug;
376 */
377 /*For Bug No : 2230514 End*/
378
379 /* ----------------------------------------------------------------------- */
380
381 PROCEDURE Check_Budget_Group_Freeze
382 ( p_api_version IN NUMBER,
383 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
384 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_NONE,
385 p_return_status OUT NOCOPY VARCHAR2,
386 p_msg_count OUT NOCOPY NUMBER,
387 p_msg_data OUT NOCOPY VARCHAR2,
388 p_budget_group_id IN NUMBER
389 ) AS
390
391 l_api_name CONSTANT VARCHAR2(30) := 'Check_Budget_Group_Freeze';
392 l_api_version CONSTANT NUMBER := 1.0;
393
394 l_freeze_flag VARCHAR2(1);
395
396 cursor c_FreezeFlag is
397 select nvl(freeze_hierarchy_flag, root_freeze_hierarchy_flag) freeze_hierarchy_flag
398 from PSB_BUDGET_GROUPS_V
399 where budget_group_id = p_budget_group_id;
400
401 BEGIN
402
403 -- Standard call to check for call compatibility
404
405 if not FND_API.Compatible_API_Call (l_api_version,
406 p_api_version,
407 l_api_name,
408 G_PKG_NAME)
409 then
410 raise FND_API.G_EXC_UNEXPECTED_ERROR;
411 end if;
412
413 for c_FreezeFlag_Rec in c_FreezeFlag loop
414 l_freeze_flag := c_FreezeFlag_Rec.freeze_hierarchy_flag;
415 end loop;
416
417 if ((l_freeze_flag is null) or (l_freeze_flag = 'N')) then
418 raise FND_API.G_EXC_ERROR;
419 end if;
420
421
422 -- Standard call to get message count and if count is 1, get message info
423
424 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
425 p_data => p_msg_data);
426
427
428 -- Initialize API Return Status to Success
429
430 p_return_status := FND_API.G_RET_STS_SUCCESS;
431
432
433 EXCEPTION
434
435 when FND_API.G_EXC_ERROR then
436 p_return_status := FND_API.G_RET_STS_ERROR;
437 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
438 p_data => p_msg_data);
439
440 when FND_API.G_EXC_UNEXPECTED_ERROR then
441 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
442 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
443 p_data => p_msg_data);
444
445 when OTHERS then
446 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
447
448 if FND_MSG_PUB.Check_Msg_Level
449 (p_message_level => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
450 then
451 FND_MSG_PUB.Add_Exc_Msg
452 (p_pkg_name => G_PKG_NAME,
453 p_procedure_name => l_api_name);
454 end if;
455
456 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
457 p_data => p_msg_data);
458
459 END Check_Budget_Group_Freeze;
460
461 /* ----------------------------------------------------------------------- */
462
463 PROCEDURE Val_Budget_Group
464 ( p_budget_group_id IN NUMBER,
465 p_budget_group_name IN VARCHAR2,
466 p_effective_start_date IN DATE,
467 p_effective_end_date IN DATE,
468 p_return_status OUT NOCOPY VARCHAR2
469 ) AS
470
471 -- Find CCIDs that overlap for the same effective dates
472
473 cursor c_Overlap (AccSet_ID NUMBER,
474 Start_Date DATE,
475 End_Date DATE) is
476 select 'Account Sets Overlap'
477 from psb_budget_accounts a,
478 psb_budget_accounts b,
479 psb_set_relations_v c
480 where a.code_combination_id = b.code_combination_id
481 and b.account_position_set_id = AccSet_ID
482 and a.account_position_set_id = c.account_position_set_id
483 and ((((End_Date is not null)
484 and ((c.effective_start_date <= End_Date)
485 and (c.effective_end_date is null))
486 or ((c.effective_start_date between Start_Date and End_Date)
487 or (c.effective_end_date between Start_Date and End_Date)
488 or ((c.effective_start_date < Start_Date)
489 and (c.effective_end_date > End_Date)))))
490 or ((End_Date is null)
491 and (nvl(c.effective_end_date, Start_Date) >= Start_Date)))
492 and c.account_position_set_id <> AccSet_ID
493 and c.account_or_position_type = 'A'
494 and c.budget_group_id = p_budget_group_id;
495
496 BEGIN
497
498 -- Check for a specific Budget Group :
499 --
500 -- (1) Account Set Effective Dates bounded by Budget Group Effective Dates
501 -- (2) No Overlap in Account Assignments for the same Effective Dates
502
503 for c_AccSet_Rec in c_AccSet (p_budget_group_id) loop
504
505 if p_effective_end_date is null then
506
507 if (c_AccSet_Rec.effective_start_date < p_effective_start_date) then
508 message_token('ACCOUNT_SET', c_AccSet_Rec.name);
509 message_token('BUDGET_GROUP', p_budget_group_name);
510 add_message('PSB', 'ACCSET_EFF_DATE_OUT_OF_RANGE');
511 raise FND_API.G_EXC_ERROR;
512 end if;
513
514 else
515
516 if (c_AccSet_Rec.effective_start_date not between p_effective_start_date
517 and p_effective_end_date) then
518 message_token('ACCOUNT_SET', c_AccSet_Rec.name);
519 message_token('BUDGET_GROUP', p_budget_group_name);
520 add_message('PSB', 'ACCSET_EFF_DATE_OUT_OF_RANGE');
521 raise FND_API.G_EXC_ERROR;
522 end if;
523
524 if (nvl(c_AccSet_Rec.effective_end_date, p_effective_end_date) not between
525 p_effective_start_date and p_effective_end_date) then
526 message_token('ACCOUNT_SET', c_AccSet_Rec.name);
527 message_token('BUDGET_GROUP', p_budget_group_name);
528 add_message('PSB', 'ACCSET_EFF_DATE_OUT_OF_RANGE');
529 raise FND_API.G_EXC_ERROR;
530 end if;
531
532 end if;
533
534 for c_Overlap_Rec in c_Overlap (c_AccSet_Rec.account_position_set_id,
535 c_AccSet_Rec.effective_start_date,
536 c_AccSet_Rec.effective_end_date) loop
537 message_token('ACCOUNT_SET', c_AccSet_Rec.name);
538 message_token('BUDGET_GROUP', p_budget_group_name);
539 add_message('PSB', 'ACCSET_BG_OVERLAP');
540 raise FND_API.G_EXC_ERROR;
541 end loop;
542
543 end loop;
544
545
546 -- Initialize API Return Status to Success
547
548 p_return_status := FND_API.G_RET_STS_SUCCESS;
549
550
551 EXCEPTION
552
553 when FND_API.G_EXC_ERROR then
554 p_return_status := FND_API.G_RET_STS_ERROR;
555
556 when FND_API.G_EXC_UNEXPECTED_ERROR then
557 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
558
559 when OTHERS then
560 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
561
562 END Val_Budget_Group;
563
564 /* ----------------------------------------------------------------------- */
565
566 PROCEDURE Val_Budget_Group_Hierarchy
567 ( p_api_version IN NUMBER,
568 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
569 p_commit IN VARCHAR2 := FND_API.G_FALSE,
570 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_NONE,
571 p_return_status OUT NOCOPY VARCHAR2,
572 p_msg_count OUT NOCOPY NUMBER,
573 p_msg_data OUT NOCOPY VARCHAR2,
574 p_budget_group_id IN NUMBER,
575 p_budget_by_position IN VARCHAR2 := 'N',
576 p_validate_ranges IN VARCHAR2 := FND_API.G_TRUE,
577 p_force_freeze IN VARCHAR2 := 'N',
578 p_check_missing_acct IN VARCHAR2 := FND_API.G_TRUE
579 ) AS
580
581 l_api_name CONSTANT VARCHAR2(30) := 'Val_Budget_Group_Hierarchy';
582 l_api_version CONSTANT NUMBER := 1.0;
583
584 l_budget_group g_budgetgroup_tbl_type;
585
586 l_account_range_overlap_flag VARCHAR2(1) := 'N' ;
587 l_budget_group_error VARCHAR2(1) := 'N' ;
588 l_missing_ccid_flag VARCHAR2(1) := 'N' ;
589 l_missing_nps_ps_ccid VARCHAR2(1) := 'N' ;
590
591 l_bg_index BINARY_INTEGER := 1;
592 l_init_index BINARY_INTEGER;
593 l_search_index BINARY_INTEGER;
594
595 l_parent_name VARCHAR2(100);
596 l_parent_start_date DATE;
597 l_parent_end_date DATE;
598
599 l_flex_code NUMBER;
600
601 l_concat_segments VARCHAR2(2000);
602
603 l_ps_account_set_id NUMBER;
604 l_nps_account_set_id NUMBER;
605
606 l_return_status VARCHAR2(1);
607
608 cursor c_Flex is
609 select nvl(chart_of_accounts_id, root_chart_of_accounts_id) flex_code
610 from PSB_BUDGET_GROUPS_V
611 where budget_group_id = p_budget_group_id;
612
613 BEGIN
614
615 -- Standard Start of API savepoint
616
617 SAVEPOINT Val_Budget_Group_Hierarchy_Pvt;
618
619
620 -- Standard call to check for call compatibility
621
622 if not FND_API.Compatible_API_Call (l_api_version,
623 p_api_version,
624 l_api_name,
625 G_PKG_NAME)
626 then
627 raise FND_API.G_EXC_UNEXPECTED_ERROR;
628 end if;
629
630 -- Initialize message list if p_init_msg_list is set to TRUE.
631
632 if FND_API.to_Boolean (p_init_msg_list) then
633 FND_MSG_PUB.initialize;
634 end if;
635
636
637 -- Check for Budget Group Hierarchy :
638 --
639 -- (1) Child Budget Group Effective Dates within Effective Dates of Parent Budget Group
640 -- (2) No Duplicate Account Set Assignments within the Hierarchy for the same Effective Dates
641 -- (3) Complete Data for Root Budget Group (SOB, Business Group, Budget Group Category Set,
642 -- Personnel Services Account Set, Non_Personnel Services Account Set)
643 -- (4) Check that Ranges for the Personnel Services and Non-Personnel Services Account Sets do not overlap
644
645 for l_init_index in 1..l_budget_group.Count loop
646 l_budget_group(l_init_index).budget_group_id := null;
647 l_budget_group(l_init_index).name := null;
648 l_budget_group(l_init_index).parent_budget_group_id := null;
649 l_budget_group(l_init_index).effective_start_date := null;
650 l_budget_group(l_init_index).effective_end_date := null;
651 end loop;
652
653 for c_Flex_Rec in c_Flex loop
654 l_flex_code := c_Flex_Rec.flex_code;
655 end loop;
656
657 for c_BudgetGroup_Rec in c_BudgetGroup (p_budget_group_id) loop
658
659 Val_Budget_Group
660 (p_budget_group_id => c_BudgetGroup_Rec.budget_group_id,
661 p_budget_group_name => c_BudgetGroup_Rec.name,
662 p_effective_start_date => c_BudgetGroup_Rec.effective_start_date,
663 p_effective_end_date => c_BudgetGroup_Rec.effective_end_date,
664 p_return_status => l_return_status);
665
666 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
667 message_token('BUDGET_GROUP', c_BudgetGroup_Rec.name);
668 add_message('PSB', 'PSB_INVALID_BUDGET_GROUP');
669 l_budget_group_error := 'Y';
670 end if;
671
672 -- Note : this is a DFS so we need to cache these Budget Groups
673
674 if (c_BudgetGroup_Rec.budget_group_id = p_budget_group_id) then
675 begin
676
677 l_ps_account_set_id := c_BudgetGroup_Rec.ps_account_position_set_id;
678 l_nps_account_set_id := c_BudgetGroup_Rec.nps_account_position_set_id;
679
680 -- Root Budget Group Definition must be complete
681
682 if ((c_BudgetGroup_Rec.set_of_books_id is null) or
683 ((p_budget_by_position = 'Y') and (c_BudgetGroup_Rec.business_group_id is null)) or
684 /* Bug No 2610221 Start */
685 -- Removed the category_set check since it is not mandatory
686 -- (c_BudgetGroup_Rec.budget_group_category_set_id is null) or
687 /* Bug No 2610221 End */
688 (c_BudgetGroup_Rec.ps_account_position_set_id is null) or
689 (c_BudgetGroup_Rec.nps_account_position_set_id is null)) then
690
691 add_message('PSB', 'INVALID_ROOT_BG_DEFN');
692 l_budget_group_error := 'Y';
693 end if;
694
695 -- Ranges for Personnel Services and Non Personnel Services Account Sets should not Overlap
696
697 for c_Overlap_ps_Rec in c_Overlap_ps (c_BudgetGroup_Rec.ps_account_position_set_id,
698 c_BudgetGroup_Rec.nps_account_position_set_id) loop
699 message_token('BUDGET_GROUP', c_BudgetGroup_Rec.name);
700 add_message('PSB', 'CCID_OVERLAP');
701 l_budget_group_error := 'Y';
702 end loop;
703
704 if FND_API.to_Boolean(p_validate_ranges) then
705 begin
706
707 l_account_range_overlap_flag := 'N';
708
709 for c_Overlap_ps_Rec in c_Overlap_ps_range (c_BudgetGroup_Rec.ps_account_position_set_id,
710 c_BudgetGroup_Rec.nps_account_position_set_id) loop
711 message_token('BUDGET_GROUP', c_BudgetGroup_Rec.name);
712 add_message('PSB', 'CCID_OVERLAP');
713 l_account_range_overlap_flag := 'Y'; -- set flag
714 end loop;
715
716 if ((l_account_range_overlap_flag = 'Y') and
717 (p_force_freeze = 'N')) then
718 l_budget_group_error := 'Y';
719 end if ;
720
721 end;
722 end if;
723
724 end;
725 else
726 begin
727
728 for l_search_index in 1..l_bg_index - 1 loop
729
730 if (l_budget_group(l_search_index).budget_group_id = c_BudgetGroup_Rec.parent_budget_group_id) then
731 l_parent_name := l_budget_group(l_search_index).name;
732 l_parent_start_date := l_budget_group(l_search_index).effective_start_date;
733 l_parent_end_date := l_budget_group(l_search_index).effective_end_date;
734 exit;
735 end if;
736
737 end loop;
738
739 -- Child Budget Group Effective Dates must be bounded by the corresponding
740 -- values for the parent Budget Group
741
742 if l_parent_end_date is null then
743
744 if (c_BudgetGroup_Rec.effective_start_date < l_parent_start_date) then
745 message_token('BUDGET_GROUP', c_BudgetGroup_Rec.name);
746 message_token('PARENT_BUDGET_GROUP', l_parent_name);
747 add_message('PSB', 'BG_EFF_DATE_OUT_OF_RANGE');
748 l_budget_group_error := 'Y';
749 end if;
750
751 else
752 begin
753
754 if (c_BudgetGroup_Rec.effective_start_date not between l_parent_start_date
755 and l_parent_end_date) then
756 message_token('BUDGET_GROUP', c_BudgetGroup_Rec.name);
757 message_token('PARENT_BUDGET_GROUP', l_parent_name);
758 add_message('PSB', 'BG_EFF_DATE_OUT_OF_RANGE');
759 l_budget_group_error := 'Y';
760 end if;
761
762 if (nvl(c_BudgetGroup_Rec.effective_end_date, l_parent_end_date) not between
763 l_parent_start_date and l_parent_end_date) then
764 message_token('BUDGET_GROUP', c_BudgetGroup_Rec.name);
765 message_token('PARENT_BUDGET_GROUP', l_parent_name);
766 add_message('PSB', 'BG_EFF_DATE_OUT_OF_RANGE');
767 l_budget_group_error := 'Y';
768 end if;
769
770 end;
771 end if;
772
773 end;
774 end if;
775
776 for c_AccSet_Rec in c_Accset (c_BudgetGroup_Rec.budget_group_id) loop
777
778 for c_Overlap_CCID_Rec in c_Overlap_CCID (p_budget_group_id,
779 c_BudgetGroup_Rec.budget_group_id,
780 c_AccSet_Rec.account_position_set_id,
781 c_AccSet_Rec.effective_start_date,
782 c_AccSet_Rec.effective_end_date) loop
783
784 l_concat_segments := FND_FLEX_EXT.Get_Segs
785 (application_short_name => 'SQLGL',
786 key_flex_code => 'GL#',
787 structure_number => l_flex_code,
788 combination_id => c_Overlap_CCID_Rec.code_combination_id);
789
790 message_token('CCID', l_concat_segments);
791 message_token('ACCOUNT_SET', c_AccSet_Rec.name);
792 message_token('BUDGET_GROUP',c_budgetgroup_rec.name);
793 add_message('PSB', 'ACCSET_BGH_OVERLAP');
794 l_budget_group_error := 'Y';
795 end loop;
796
797 if FND_API.to_Boolean(p_validate_ranges) then
798 begin
799
800 l_account_range_overlap_flag := 'N';
801
802 for c_Overlap_Account_Range_Rec in c_Overlap_Account_Range (p_budget_group_id,
803 c_BudgetGroup_Rec.budget_group_id,
804 c_AccSet_Rec.account_position_set_id,
805 c_AccSet_Rec.effective_start_date,
806 c_AccSet_Rec.effective_end_date) loop
807 message_token('BUDGET_GROUP',c_budgetgroup_rec.name);
808 message_token('ACCOUNT_SET', c_AccSet_Rec.name);
809 add_message('PSB', 'PSB_OVERLAP_ACCT_RANGE');
810 l_account_range_overlap_flag := 'Y'; -- set flag
811 end loop;
812
813 -- .. freeze ....., use value of l_account_range_overlap_flag
814 -- to either force freeze or not, depending on user selection
815
816 if ((l_account_range_overlap_flag = 'Y') and
817 (p_force_freeze = 'N')) then
818 l_budget_group_error := 'Y';
819 end if ;
820
821 end;
822 end if;
823
824 end loop;
825
826 l_budget_group(l_bg_index).budget_group_id := c_BudgetGroup_Rec.budget_group_id;
827 l_budget_group(l_bg_index).name := c_BudgetGroup_Rec.name;
828 l_budget_group(l_bg_index).parent_budget_group_id := c_BudgetGroup_Rec.parent_budget_group_id;
829 l_budget_group(l_bg_index).effective_start_date := c_BudgetGroup_Rec.effective_start_date;
830 l_budget_group(l_bg_index).effective_end_date := c_BudgetGroup_Rec.effective_end_date;
831
832 l_bg_index := l_bg_index + 1;
833
834 end loop;
835
836 --
837 -- validate psb accounts vs gl accounts
838 --
839 if FND_API.to_Boolean(p_check_missing_acct) then
840
841 Validate_PSB_Accounts_And_GL
842 (p_top_budget_group_id => p_budget_group_id,
843 p_flex_code => l_flex_code,
844 p_return_status => l_return_status);
845
846 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
847
848 l_missing_ccid_flag := 'Y';
849
850 -- flag indicates there was error
851 IF (p_force_freeze = 'N') then
852 l_budget_group_error := 'Y';
853 END IF;
854
855 end if;
856 --
857 end if;
858
859 --
860 -- validate budget group's ccid is in nps/ps ccid
861 -- do not bypass error even if force freeze on
862 --
863
864 Validate_BGCCID_vs_PS_NPS
865 (p_top_budget_group_id => p_budget_group_id,
866 p_ps_account_set_id => l_ps_account_set_id,
867 p_nps_account_set_id => l_nps_account_set_id,
868 p_flex_code => l_flex_code,
869 p_return_status => l_return_status);
870
871 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
872 -- this will fail... ws creation will fail it too
873
874 l_budget_group_error := 'Y';
875
876 END IF;
877
878
879 Validate_BG_Organization
880 (p_top_budget_group_id => p_budget_group_id,
881 p_return_status => l_return_status);
882
883 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
884
885 l_missing_ccid_flag := 'Y';
886 -- soft validation only
887 end if;
888 --
889 --
890 -- Initialize API Return Status to Success
891 -- and update freeze flag
892 --
893 IF (l_budget_group_error <> 'Y') THEN
894
895 update PSB_BUDGET_GROUPS
896 set freeze_hierarchy_flag = 'Y'
897 where budget_group_id = p_budget_group_id;
898
899 p_return_status := FND_API.G_RET_STS_SUCCESS;
900 ELSE
901 p_return_status := FND_API.G_RET_STS_ERROR ;
902 END IF;
903
904 -- add final messages to error file
905 -- reverse order b/c insert error will insert it in desc order
906
907 add_message('PSB', 'PSB_BG_ERROR_DUMMY1');
908 add_message('PSB', 'PSB_BG_ERROR_DUMMY');
909 add_message('PSB', 'PSB_BG_ERROR_DUMMY1');
910 -- dummy message to separate final msg from validation errors
911
912 IF (l_budget_group_error <> 'Y') THEN
913 IF ( l_missing_ccid_flag = 'Y' OR
914 l_account_range_overlap_flag = 'Y') THEN
915 add_message('PSB', 'PSB_WARNING_VALIDATE_BGP');
916 END IF;
917 add_message('PSB', 'PSB_SUCCESS_VALIDATE_BGP');
918 ELSE
919 IF ( l_missing_ccid_flag = 'Y' OR
920 l_account_range_overlap_flag = 'Y') THEN
921 add_message('PSB', 'PSB_WARNING_VALIDATE_BGP_FAIL2');
922 add_message('PSB', 'PSB_WARNING_VALIDATE_BGP_FAIL');
923 add_message('PSB', 'PSB_WARNING_VALIDATE_BGP');
924 END IF;
925 add_message('PSB', 'PSB_FAILURE_VALIDATE_BGP');
926 END IF;
927
928
929 -- write to file
930 IF (l_budget_group_error = 'Y' OR
931 l_missing_ccid_flag = 'Y' OR
932 l_account_range_overlap_flag = 'Y'
933 ) THEN
934
935 Output_Message_To_Table(p_budget_group_id => p_budget_group_id);
936 ELSE
937 delete from PSB_ERROR_MESSAGES
938 where source_process = 'VALIDATE_BUDGET_HIERARCHY'
939 and process_id = p_budget_group_id;
940 -- to delete previous error for the budget group
941 END IF;
942
943 -- add messages to empty stack for out and log file
944 IF (l_budget_group_error <> 'Y') THEN
945 add_message('PSB', 'PSB_SUCCESS_VALIDATE_BGP');
946 IF ( l_missing_ccid_flag = 'Y' OR
947 l_account_range_overlap_flag = 'Y') THEN
948 add_message('PSB', 'PSB_WARNING_VALIDATE_BGP');
949 END IF;
950 ELSE
951 add_message('PSB', 'PSB_FAILURE_VALIDATE_BGP');
952 IF ( l_missing_ccid_flag = 'Y' OR
953 l_account_range_overlap_flag = 'Y') THEN
954 add_message('PSB', 'PSB_WARNING_VALIDATE_BGP');
955 add_message('PSB', 'PSB_WARNING_VALIDATE_BGP_FAIL');
956 add_message('PSB', 'PSB_WARNING_VALIDATE_BGP_FAIL2');
957 END IF;
958 END IF;
959
960 -- Standard check of p_commit
961
962 if FND_API.to_Boolean(p_commit) then
963 commit work;
964 end if;
965
966 -- Standard call to get message count and if count is 1, get message info
967
968 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
969 p_data => p_msg_data);
970
971
972 -- Initialize API Return Status to Success
973 --
974 -- p_return_status := FND_API.G_RET_STS_SUCCESS;
975
976
977 EXCEPTION
978
979 when FND_API.G_EXC_ERROR then
980 rollback to Val_Budget_Group_Hierarchy_Pvt;
981 p_return_status := FND_API.G_RET_STS_ERROR ;
982 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
983 p_data => p_msg_data);
984
985 when FND_API.G_EXC_UNEXPECTED_ERROR then
986 rollback to Val_Budget_Group_Hierarchy_Pvt;
987 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
988 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
989 p_data => p_msg_data);
990
991 when OTHERS then
992 rollback to Val_Budget_Group_Hierarchy_Pvt;
993 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
994
995 if FND_MSG_PUB.Check_Msg_Level
996 (p_message_level => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) then
997
998 FND_MSG_PUB.Add_Exc_Msg
999 (p_pkg_name => G_PKG_NAME,
1000 p_procedure_name => l_api_name);
1001
1002 end if;
1003
1004 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
1005 p_data => p_msg_data);
1006
1007 END Val_Budget_Group_Hierarchy;
1008
1009 /* ----------------------------------------------------------------------- */
1010
1011 PROCEDURE INSERT_ROW (
1012 p_api_version in number,
1013 p_init_msg_list in varchar2 := fnd_api.g_false,
1014 p_commit in varchar2 := fnd_api.g_false,
1015 p_validation_level in number := fnd_api.g_valid_level_full,
1016 p_return_status OUT NOCOPY varchar2,
1017 p_msg_count OUT NOCOPY number,
1018 p_msg_data OUT NOCOPY varchar2,
1019 p_rowid in OUT NOCOPY varchar2,
1020 p_budget_group_id in number,
1021 p_name in varchar2,
1022 p_short_name in varchar2,
1023 p_root_budget_group in varchar2,
1024 p_parent_budget_group_id in number,
1025 p_root_budget_group_id in number,
1026 p_ps_account_position_set_id in number,
1027 p_nps_account_position_set_id in number,
1028 p_budget_group_category_set_id in number,
1029 p_effective_start_date in date,
1030 p_effective_end_date in date,
1031 p_freeze_hierarchy_flag in varchar2,
1032 p_description in varchar2,
1033 p_set_of_books_id in number,
1034 p_business_group_id in number,
1035 p_num_proposed_years in number,
1036 p_narrative_description in varchar2,
1037 p_budget_group_type in varchar2,
1038 p_organization_id in number ,
1039 p_request_id in number,
1040 p_segment1_type in number,
1041 p_segment2_type in number,
1042 p_segment3_type in number,
1043 p_segment4_type in number,
1044 p_segment5_type in number,
1045 p_segment6_type in number,
1046 p_segment7_type in number,
1047 p_segment8_type in number,
1048 p_segment9_type in number,
1049 p_segment10_type in number,
1050 p_segment11_type in number,
1051 p_segment12_type in number,
1052 p_segment13_type in number,
1053 p_segment14_type in number,
1054 p_segment15_type in number,
1055 p_segment16_type in number,
1056 p_segment17_type in number,
1057 p_segment18_type in number,
1058 p_segment19_type in number,
1059 p_segment20_type in number,
1060 p_segment21_type in number,
1061 p_segment22_type in number,
1062 p_segment23_type in number,
1063 p_segment24_type in number,
1064 p_segment25_type in number,
1065 p_segment26_type in number,
1066 p_segment27_type in number,
1067 p_segment28_type in number,
1068 p_segment29_type in number,
1069 p_segment30_type in number,
1070 p_attribute1 in varchar2,
1071 p_attribute2 in varchar2,
1072 p_attribute3 in varchar2,
1073 p_attribute4 in varchar2,
1074 p_attribute5 in varchar2,
1075 p_attribute6 in varchar2,
1076 p_attribute7 in varchar2,
1077 p_attribute8 in varchar2,
1078 p_attribute9 in varchar2,
1079 p_attribute10 in varchar2,
1080 p_context in varchar2,
1081 p_mode in varchar2 := 'R'
1082 ) AS
1083
1084 cursor C is select ROWID from PSB_BUDGET_GROUPS
1085 where BUDGET_GROUP_ID = P_BUDGET_GROUP_ID;
1086 P_LAST_UPDATE_DATE DATE;
1087 P_LAST_UPDATED_BY NUMBER;
1088 P_LAST_UPDATE_LOGIN NUMBER;
1089 --
1090 l_api_name CONSTANT VARCHAR2(30) := 'Insert_Row' ;
1091 l_api_version CONSTANT NUMBER := 1.0 ;
1092 l_return_status VARCHAR2(1);
1093 --
1094
1095 BEGIN
1096 --
1097 SAVEPOINT Insert_Row ;
1098 --
1099 if FND_API.to_Boolean (p_init_msg_list) then
1100 FND_MSG_PUB.initialize;
1101 end if;
1102 --
1103 p_return_status := FND_API.G_RET_STS_SUCCESS ;
1104 --
1105 P_LAST_UPDATE_DATE := SYSDATE;
1106 if(P_MODE = 'I') then
1107 P_LAST_UPDATED_BY := 1;
1108 P_LAST_UPDATE_LOGIN := 0;
1109 elsif (P_MODE = 'R') then
1110 P_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1111 if P_LAST_UPDATED_BY is NULL then
1112 P_LAST_UPDATED_BY := -1;
1113 end if;
1114 P_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
1115 if P_LAST_UPDATE_LOGIN is NULL then
1116 P_LAST_UPDATE_LOGIN := -1;
1117 end if;
1118 else
1119 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
1120 FND_MSG_PUB.Add ;
1121 raise FND_API.G_EXC_ERROR ;
1122 end if;
1123
1124 insert into PSB_BUDGET_GROUPS (
1125 budget_group_id,
1126 name,
1127 short_name,
1128 root_budget_group,
1129 parent_budget_group_id,
1130 root_budget_group_id,
1131 ps_account_position_set_id,
1132 nps_account_position_set_id,
1133 budget_group_category_set_id,
1134 effective_start_date,
1135 effective_end_date,
1136 freeze_hierarchy_flag,
1137 description,
1138 set_of_books_id,
1139 business_group_id,
1140 num_proposed_years,
1141 narrative_description,
1142 budget_group_type,
1143 organization_id,
1144 request_id,
1145 segment1_type,
1146 segment2_type,
1147 segment3_type,
1148 segment4_type,
1149 segment5_type,
1150 segment6_type,
1151 segment7_type,
1152 segment8_type,
1153 segment9_type,
1154 segment10_type,
1155 segment11_type,
1156 segment12_type,
1157 segment13_type,
1158 segment14_type,
1159 segment15_type,
1160 segment16_type,
1161 segment17_type,
1162 segment18_type,
1163 segment19_type,
1164 segment20_type,
1165 segment21_type,
1166 segment22_type,
1167 segment23_type,
1168 segment24_type,
1169 segment25_type,
1170 segment26_type,
1171 segment27_type,
1172 segment28_type,
1173 segment29_type,
1174 segment30_type,
1175 attribute1,
1176 attribute2,
1177 attribute3,
1178 attribute4,
1179 attribute5,
1180 attribute6,
1181 attribute7,
1182 attribute8,
1183 attribute9,
1184 attribute10,
1185 context,
1186 creation_date,
1187 created_by,
1188 last_update_date,
1189 last_updated_by,
1190 last_update_login
1191 ) values (
1192 p_budget_group_id,
1193 p_name,
1194 p_short_name,
1195 p_root_budget_group,
1196 p_parent_budget_group_id,
1197 p_root_budget_group_id,
1198 p_ps_account_position_set_id,
1199 p_nps_account_position_set_id,
1200 p_budget_group_category_set_id,
1201 p_effective_start_date,
1202 p_effective_end_date,
1203 p_freeze_hierarchy_flag,
1204 p_description,
1205 p_set_of_books_id,
1206 p_business_group_id,
1207 p_num_proposed_years,
1208 p_narrative_description,
1209 p_budget_group_type,
1210 p_organization_id,
1211 p_request_id,
1212 p_segment1_type,
1213 p_segment2_type,
1214 p_segment3_type,
1215 p_segment4_type,
1216 p_segment5_type,
1217 p_segment6_type,
1218 p_segment7_type,
1219 p_segment8_type,
1220 p_segment9_type,
1221 p_segment10_type,
1222 p_segment11_type,
1223 p_segment12_type,
1224 p_segment13_type,
1225 p_segment14_type,
1226 p_segment15_type,
1227 p_segment16_type,
1228 p_segment17_type,
1229 p_segment18_type,
1230 p_segment19_type,
1231 p_segment20_type,
1232 p_segment21_type,
1233 p_segment22_type,
1234 p_segment23_type,
1235 p_segment24_type,
1236 p_segment25_type,
1237 p_segment26_type,
1238 p_segment27_type,
1239 p_segment28_type,
1240 p_segment29_type,
1241 p_segment30_type,
1242 p_attribute1,
1243 p_attribute2,
1244 p_attribute3,
1245 p_attribute4,
1246 p_attribute5,
1247 p_attribute6,
1248 p_attribute7,
1249 p_attribute8,
1250 p_attribute9,
1251 p_attribute10,
1252 p_context,
1253 p_last_update_date,
1254 p_last_updated_by,
1255 p_last_update_date,
1256 p_last_updated_by,
1257 p_last_update_login
1258 );
1259
1260 open c;
1261 fetch c into P_ROWID;
1262 if (c%notfound) then
1263 close c;
1264 raise FND_API.G_EXC_ERROR ;
1265 end if;
1266 close c;
1267
1268 --
1269 -- Standard check of p_commit.
1270 if FND_API.to_Boolean (p_commit) then
1271 commit work;
1272 end if;
1273 -- Standard call to get message count and if count is 1, get message info.
1274 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
1275 p_data => p_msg_data);
1276 --
1277
1278 EXCEPTION
1279 --
1280 when FND_API.G_EXC_ERROR then
1281 --
1282 rollback to INSERT_ROW ;
1283 p_return_status := FND_API.G_RET_STS_ERROR;
1284 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
1285 p_data => p_msg_data);
1286 --
1287 when FND_API.G_EXC_UNEXPECTED_ERROR then
1288 --
1289 rollback to INSERT_ROW ;
1290 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1291 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
1292 p_data => p_msg_data);
1293 --
1294 when OTHERS then
1295 --
1296 rollback to INSERT_ROW ;
1297 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1298 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) then
1299 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
1300 l_api_name);
1301 END if;
1302 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
1303 p_data => p_msg_data);
1304 --
1305 END INSERT_ROW;
1306
1307 /*---------------------------------------------------------------*/
1308
1309 PROCEDURE LOCK_ROW (
1310 p_api_version in number,
1311 p_init_msg_list in varchar2 := FND_API.G_FALSE,
1312 p_commit in varchar2 := FND_API.G_FALSE,
1313 p_validation_level in number := FND_API.G_VALID_LEVEL_FULL,
1314 p_return_status OUT NOCOPY varchar2,
1315 p_msg_count OUT NOCOPY number,
1316 p_msg_data OUT NOCOPY varchar2,
1317 p_lock_row OUT NOCOPY varchar2,
1318 p_budget_group_id in number,
1319 p_name in varchar2,
1320 p_short_name in varchar2,
1321 p_root_budget_group in varchar2,
1322 p_parent_budget_group_id in number,
1323 p_root_budget_group_id in number,
1324 p_ps_account_position_set_id in number,
1325 p_nps_account_position_set_id in number,
1326 p_budget_group_category_set_id in number,
1327 p_effective_start_date in date,
1328 p_effective_end_date in date,
1329 p_freeze_hierarchy_flag in varchar2,
1330 p_description in varchar2,
1331 p_set_of_books_id in number,
1332 p_business_group_id in number,
1333 p_num_proposed_years in number,
1334 p_narrative_description in varchar2,
1335 p_budget_group_type in varchar2,
1336 p_organization_id in number ,
1337 p_request_id in number,
1338 p_segment1_type in number,
1339 p_segment2_type in number,
1340 p_segment3_type in number,
1341 p_segment4_type in number,
1342 p_segment5_type in number,
1343 p_segment6_type in number,
1344 p_segment7_type in number,
1345 p_segment8_type in number,
1346 p_segment9_type in number,
1347 p_segment10_type in number,
1348 p_segment11_type in number,
1349 p_segment12_type in number,
1350 p_segment13_type in number,
1351 p_segment14_type in number,
1352 p_segment15_type in number,
1353 p_segment16_type in number,
1354 p_segment17_type in number,
1355 p_segment18_type in number,
1356 p_segment19_type in number,
1357 p_segment20_type in number,
1358 p_segment21_type in number,
1359 p_segment22_type in number,
1360 p_segment23_type in number,
1361 p_segment24_type in number,
1362 p_segment25_type in number,
1363 p_segment26_type in number,
1364 p_segment27_type in number,
1365 p_segment28_type in number,
1366 p_segment29_type in number,
1367 p_segment30_type in number,
1368 p_attribute1 in varchar2,
1369 p_attribute2 in varchar2,
1370 p_attribute3 in varchar2,
1371 p_attribute4 in varchar2,
1372 p_attribute5 in varchar2,
1373 p_attribute6 in varchar2,
1374 p_attribute7 in varchar2,
1375 p_attribute8 in varchar2,
1376 p_attribute9 in varchar2,
1377 p_attribute10 in varchar2,
1378 p_context in varchar2
1379 ) as
1380
1381 cursor c1 is select
1382 name,
1383 short_name,
1384 root_budget_group,
1385 parent_budget_group_id,
1386 root_budget_group_id,
1387 ps_account_position_set_id,
1388 nps_account_position_set_id,
1389 budget_group_category_set_id,
1390 effective_start_date,
1391 effective_end_date,
1392 freeze_hierarchy_flag,
1393 description,
1394 set_of_books_id,
1395 business_group_id,
1396 num_proposed_years,
1397 narrative_description,
1398 budget_group_type,
1399 organization_id,
1400 request_id,
1401 segment1_type,
1402 segment2_type,
1403 segment3_type,
1404 segment4_type,
1405 segment5_type,
1406 segment6_type,
1407 segment7_type,
1408 segment8_type,
1409 segment9_type,
1410 segment10_type,
1411 segment11_type,
1412 segment12_type,
1413 segment13_type,
1414 segment14_type,
1415 segment15_type,
1416 segment16_type,
1417 segment17_type,
1418 segment18_type,
1419 segment19_type,
1420 segment20_type,
1421 segment21_type,
1422 segment22_type,
1423 segment23_type,
1424 segment24_type,
1425 segment25_type,
1426 segment26_type,
1427 segment27_type,
1428 segment28_type,
1429 segment29_type,
1430 segment30_type,
1431 attribute1,
1432 attribute2,
1433 attribute3,
1434 attribute4,
1435 attribute5,
1436 attribute6,
1437 attribute7,
1438 attribute8,
1439 attribute9,
1440 attribute10,
1441 context
1442 from PSB_BUDGET_GROUPS
1443 where BUDGET_GROUP_ID = P_BUDGET_GROUP_ID
1444 for update of BUDGET_GROUP_ID nowait;
1445 tlinfo c1%rowtype;
1446 --
1447 l_api_name CONSTANT VARCHAR2(30) := 'Lock_Row';
1448 l_api_version CONSTANT NUMBER := 1.0 ;
1449
1450 --
1451 BEGIN
1452 --
1453 open c1;
1454 fetch c1 into tlinfo;
1455 if (c1%notfound) then
1456 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
1457 app_exception.raise_exception;
1458 close c1;
1459 return;
1460 end if;
1461 close c1;
1462
1463 if ( (tlinfo.NAME = P_NAME)
1464 AND (tlinfo.SHORT_NAME = P_SHORT_NAME)
1465 AND ((tlinfo.ROOT_BUDGET_GROUP = P_ROOT_BUDGET_GROUP)
1466 OR ((tlinfo.ROOT_BUDGET_GROUP is null)
1467 AND (P_ROOT_BUDGET_GROUP is null)))
1468 AND ((tlinfo.PARENT_BUDGET_GROUP_ID = P_PARENT_BUDGET_GROUP_ID)
1469 OR ((tlinfo.PARENT_BUDGET_GROUP_ID is null)
1470 AND (P_PARENT_BUDGET_GROUP_ID is null)))
1471 AND ((tlinfo.ROOT_BUDGET_GROUP_ID = P_ROOT_BUDGET_GROUP_ID)
1472 OR ((tlinfo.ROOT_BUDGET_GROUP_ID is null)
1473 AND (P_ROOT_BUDGET_GROUP_ID is null)))
1474 AND ((tlinfo.PS_ACCOUNT_POSITION_SET_ID = P_PS_ACCOUNT_POSITION_SET_ID)
1475 OR ((tlinfo.PS_ACCOUNT_POSITION_SET_ID is null)
1476 AND (P_PS_ACCOUNT_POSITION_SET_ID is null)))
1477 AND ((tlinfo.NPS_ACCOUNT_POSITION_SET_ID = P_NPS_ACCOUNT_POSITION_SET_ID)
1478 OR ((tlinfo.NPS_ACCOUNT_POSITION_SET_ID is null)
1479 AND (P_NPS_ACCOUNT_POSITION_SET_ID is null)))
1480 AND ((tlinfo.BUDGET_GROUP_CATEGORY_SET_ID = P_BUDGET_GROUP_CATEGORY_SET_ID)
1481 OR ((tlinfo.BUDGET_GROUP_CATEGORY_SET_ID is null)
1482 AND (P_BUDGET_GROUP_CATEGORY_SET_ID is null)))
1483 AND (tlinfo.EFFECTIVE_START_DATE = P_EFFECTIVE_START_DATE)
1484 AND ((tlinfo.EFFECTIVE_END_DATE = P_EFFECTIVE_END_DATE)
1485 OR ((tlinfo.EFFECTIVE_END_DATE is null)
1486 AND (P_EFFECTIVE_END_DATE is null)))
1487 AND ((tlinfo.FREEZE_HIERARCHY_FLAG = P_FREEZE_HIERARCHY_FLAG)
1488 OR ((tlinfo.FREEZE_HIERARCHY_FLAG is null)
1489 AND (P_FREEZE_HIERARCHY_FLAG is null)))
1490 AND ((tlinfo.DESCRIPTION = P_DESCRIPTION)
1491 OR ((tlinfo.DESCRIPTION is null)
1492 AND (P_DESCRIPTION is null)))
1493 AND ((tlinfo.SET_OF_BOOKS_ID = P_SET_OF_BOOKS_ID)
1494 OR ((tlinfo.SET_OF_BOOKS_ID is null)
1495 AND (P_SET_OF_BOOKS_ID is null)))
1496 AND ((tlinfo.BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID)
1497 OR ((tlinfo.BUSINESS_GROUP_ID is null)
1498 AND (P_BUSINESS_GROUP_ID is null)))
1499 AND ((tlinfo.ORGANIZATION_ID = P_ORGANIZATION_ID )
1500 OR ((tlinfo.ORGANIZATION_ID is null)
1501 AND (P_ORGANIZATION_ID is null)))
1502 AND ((tlinfo.NUM_PROPOSED_YEARS = P_NUM_PROPOSED_YEARS)
1503 OR ((tlinfo.NUM_PROPOSED_YEARS is null)
1504 AND (P_NUM_PROPOSED_YEARS is null)))
1505 AND ((tlinfo.NARRATIVE_DESCRIPTION = P_NARRATIVE_DESCRIPTION)
1506 OR ((tlinfo.NARRATIVE_DESCRIPTION is null)
1507 AND (P_NARRATIVE_DESCRIPTION is null)))
1508 AND ((tlinfo.BUDGET_GROUP_TYPE = P_BUDGET_GROUP_TYPE)
1509 OR ((tlinfo.BUDGET_GROUP_TYPE is null)
1510 AND (P_BUDGET_GROUP_TYPE is null)))
1511 AND ((tlinfo.REQUEST_ID = P_REQUEST_ID)
1512 OR ((tlinfo.REQUEST_ID is null)
1513 AND (P_REQUEST_ID is null)))
1514 AND ((tlinfo.SEGMENT1_TYPE = P_SEGMENT1_TYPE)
1515 OR ((tlinfo.SEGMENT1_TYPE is null)
1516 AND (P_SEGMENT1_TYPE is null)))
1517 AND ((tlinfo.SEGMENT2_TYPE = P_SEGMENT2_TYPE)
1518 OR ((tlinfo.SEGMENT2_TYPE is null)
1519 AND (P_SEGMENT2_TYPE is null)))
1520 AND ((tlinfo.SEGMENT3_TYPE = P_SEGMENT3_TYPE)
1521 OR ((tlinfo.SEGMENT3_TYPE is null)
1522 AND (P_SEGMENT3_TYPE is null)))
1523 AND ((tlinfo.SEGMENT4_TYPE = P_SEGMENT4_TYPE)
1524 OR ((tlinfo.SEGMENT4_TYPE is null)
1525 AND (P_SEGMENT4_TYPE is null)))
1526 AND ((tlinfo.SEGMENT5_TYPE = P_SEGMENT5_TYPE)
1527 OR ((tlinfo.SEGMENT5_TYPE is null)
1528 AND (P_SEGMENT5_TYPE is null)))
1529 AND ((tlinfo.SEGMENT6_TYPE = P_SEGMENT6_TYPE)
1530 OR ((tlinfo.SEGMENT6_TYPE is null)
1531 AND (P_SEGMENT6_TYPE is null)))
1532 AND ((tlinfo.SEGMENT7_TYPE = P_SEGMENT7_TYPE)
1533 OR ((tlinfo.SEGMENT7_TYPE is null)
1534 AND (P_SEGMENT7_TYPE is null)))
1535 AND ((tlinfo.SEGMENT8_TYPE = P_SEGMENT8_TYPE)
1536 OR ((tlinfo.SEGMENT8_TYPE is null)
1537 AND (P_SEGMENT8_TYPE is null)))
1538 AND ((tlinfo.SEGMENT9_TYPE = P_SEGMENT9_TYPE)
1539 OR ((tlinfo.SEGMENT9_TYPE is null)
1540 AND (P_SEGMENT9_TYPE is null)))
1541 AND ((tlinfo.SEGMENT10_TYPE = P_SEGMENT10_TYPE)
1542 OR ((tlinfo.SEGMENT10_TYPE is null)
1543 AND (P_SEGMENT10_TYPE is null)))
1544 AND ((tlinfo.SEGMENT11_TYPE = P_SEGMENT11_TYPE)
1545 OR ((tlinfo.SEGMENT11_TYPE is null)
1546 AND (P_SEGMENT11_TYPE is null)))
1547 AND ((tlinfo.SEGMENT12_TYPE = P_SEGMENT12_TYPE)
1548 OR ((tlinfo.SEGMENT12_TYPE is null)
1549 AND (P_SEGMENT12_TYPE is null)))
1550 AND ((tlinfo.SEGMENT13_TYPE = P_SEGMENT13_TYPE)
1551 OR ((tlinfo.SEGMENT13_TYPE is null)
1552 AND (P_SEGMENT13_TYPE is null)))
1553 AND ((tlinfo.SEGMENT14_TYPE = P_SEGMENT14_TYPE)
1554 OR ((tlinfo.SEGMENT14_TYPE is null)
1555 AND (P_SEGMENT14_TYPE is null)))
1556 AND ((tlinfo.SEGMENT15_TYPE = P_SEGMENT15_TYPE)
1557 OR ((tlinfo.SEGMENT15_TYPE is null)
1558 AND (P_SEGMENT15_TYPE is null)))
1559 AND ((tlinfo.SEGMENT16_TYPE = P_SEGMENT16_TYPE)
1560 OR ((tlinfo.SEGMENT16_TYPE is null)
1561 AND (P_SEGMENT16_TYPE is null)))
1562 AND ((tlinfo.SEGMENT17_TYPE = P_SEGMENT17_TYPE)
1563 OR ((tlinfo.SEGMENT17_TYPE is null)
1564 AND (P_SEGMENT17_TYPE is null)))
1565 AND ((tlinfo.SEGMENT18_TYPE = P_SEGMENT18_TYPE)
1566 OR ((tlinfo.SEGMENT18_TYPE is null)
1567 AND (P_SEGMENT18_TYPE is null)))
1568 AND ((tlinfo.SEGMENT19_TYPE = P_SEGMENT19_TYPE)
1569 OR ((tlinfo.SEGMENT19_TYPE is null)
1570 AND (P_SEGMENT19_TYPE is null)))
1571 AND ((tlinfo.SEGMENT20_TYPE = P_SEGMENT20_TYPE)
1572 OR ((tlinfo.SEGMENT20_TYPE is null)
1573 AND (P_SEGMENT20_TYPE is null)))
1574 AND ((tlinfo.SEGMENT21_TYPE = P_SEGMENT21_TYPE)
1575 OR ((tlinfo.SEGMENT21_TYPE is null)
1576 AND (P_SEGMENT21_TYPE is null)))
1577 AND ((tlinfo.SEGMENT22_TYPE = P_SEGMENT22_TYPE)
1578 OR ((tlinfo.SEGMENT22_TYPE is null)
1579 AND (P_SEGMENT22_TYPE is null)))
1580 AND ((tlinfo.SEGMENT23_TYPE = P_SEGMENT23_TYPE)
1581 OR ((tlinfo.SEGMENT23_TYPE is null)
1582 AND (P_SEGMENT23_TYPE is null)))
1583 AND ((tlinfo.SEGMENT24_TYPE = P_SEGMENT24_TYPE)
1584 OR ((tlinfo.SEGMENT24_TYPE is null)
1585 AND (P_SEGMENT24_TYPE is null)))
1586 AND ((tlinfo.SEGMENT25_TYPE = P_SEGMENT25_TYPE)
1587 OR ((tlinfo.SEGMENT25_TYPE is null)
1588 AND (P_SEGMENT25_TYPE is null)))
1589 AND ((tlinfo.SEGMENT26_TYPE = P_SEGMENT26_TYPE)
1590 OR ((tlinfo.SEGMENT26_TYPE is null)
1591 AND (P_SEGMENT26_TYPE is null)))
1592 AND ((tlinfo.SEGMENT27_TYPE = P_SEGMENT27_TYPE)
1593 OR ((tlinfo.SEGMENT27_TYPE is null)
1594 AND (P_SEGMENT27_TYPE is null)))
1595 AND ((tlinfo.SEGMENT28_TYPE = P_SEGMENT28_TYPE)
1596 OR ((tlinfo.SEGMENT28_TYPE is null)
1597 AND (P_SEGMENT28_TYPE is null)))
1598 AND ((tlinfo.SEGMENT29_TYPE = P_SEGMENT29_TYPE)
1599 OR ((tlinfo.SEGMENT29_TYPE is null)
1600 AND (P_SEGMENT29_TYPE is null)))
1601 AND ((tlinfo.SEGMENT30_TYPE = P_SEGMENT30_TYPE)
1602 OR ((tlinfo.SEGMENT30_TYPE is null)
1603 AND (P_SEGMENT30_TYPE is null)))
1604 AND ((tlinfo.ATTRIBUTE1 = P_ATTRIBUTE1)
1605 OR ((tlinfo.ATTRIBUTE1 is null)
1606 AND (P_ATTRIBUTE1 is null)))
1607 AND ((tlinfo.ATTRIBUTE2 = P_ATTRIBUTE2)
1608 OR ((tlinfo.ATTRIBUTE2 is null)
1609 AND (P_ATTRIBUTE2 is null)))
1610 AND ((tlinfo.ATTRIBUTE3 = P_ATTRIBUTE3)
1611 OR ((tlinfo.ATTRIBUTE3 is null)
1612 AND (P_ATTRIBUTE3 is null)))
1613 AND ((tlinfo.ATTRIBUTE4 = P_ATTRIBUTE4)
1614 OR ((tlinfo.ATTRIBUTE4 is null)
1615 AND (P_ATTRIBUTE4 is null)))
1616 AND ((tlinfo.ATTRIBUTE5 = P_ATTRIBUTE5)
1617 OR ((tlinfo.ATTRIBUTE5 is null)
1618 AND (P_ATTRIBUTE5 is null)))
1619 AND ((tlinfo.ATTRIBUTE6 = P_ATTRIBUTE6)
1620 OR ((tlinfo.ATTRIBUTE6 is null)
1621 AND (P_ATTRIBUTE6 is null)))
1622 AND ((tlinfo.ATTRIBUTE7 = P_ATTRIBUTE7)
1623 OR ((tlinfo.ATTRIBUTE7 is null)
1624 AND (P_ATTRIBUTE7 is null)))
1625 AND ((tlinfo.ATTRIBUTE8 = P_ATTRIBUTE8)
1626 OR ((tlinfo.ATTRIBUTE8 is null)
1627 AND (P_ATTRIBUTE8 is null)))
1628 AND ((tlinfo.ATTRIBUTE9 = P_ATTRIBUTE9)
1629 OR ((tlinfo.ATTRIBUTE9 is null)
1630 AND (P_ATTRIBUTE9 is null)))
1631 AND ((tlinfo.ATTRIBUTE10 = P_ATTRIBUTE10)
1632 OR ((tlinfo.ATTRIBUTE10 is null)
1633 AND (P_ATTRIBUTE10 is null)))
1634 AND ((tlinfo.CONTEXT = P_CONTEXT)
1635 OR ((tlinfo.CONTEXT is null)
1636 AND (P_CONTEXT is null)))
1637 ) then
1638 p_lock_row := FND_API.G_TRUE;
1639 else
1640 FND_MESSAGE.Set_name('FND', 'FORM_RECORD_CHANGED');
1641 app_exception.raise_exception ;
1642 end if;
1643
1644 p_return_status := FND_API.G_RET_STS_SUCCESS;
1645
1646 EXCEPTION
1647 WHEN APP_EXCEPTION.RECORD_LOCK_EXCEPTION THEN
1648 ROLLBACK TO Lock_Row ;
1649 p_lock_row := FND_API.G_FALSE;
1650 p_return_status := FND_API.G_RET_STS_ERROR;
1651 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
1652 p_data => p_msg_data );
1653
1654 END LOCK_ROW;
1655
1656 /*---------------------------------------------------------------*/
1657
1658 PROCEDURE UPDATE_ROW (
1659 p_api_version in number,
1660 p_init_msg_list in varchar2 := fnd_api.g_false,
1661 p_commit in varchar2 := fnd_api.g_false,
1662 p_validation_level in number := fnd_api.g_valid_level_full,
1663 p_return_status OUT NOCOPY varchar2,
1664 p_msg_count OUT NOCOPY number,
1665 p_msg_data OUT NOCOPY varchar2,
1666
1667 p_budget_group_id in number,
1668 p_name in varchar2,
1669 p_short_name in varchar2,
1670 p_root_budget_group in varchar2,
1671 p_parent_budget_group_id in number,
1672 p_root_budget_group_id in number,
1673 p_ps_account_position_set_id in number,
1674 p_nps_account_position_set_id in number,
1675 p_budget_group_category_set_id in number,
1676 p_effective_start_date in date,
1677 p_effective_end_date in date,
1678 p_freeze_hierarchy_flag in varchar2,
1679 p_description in varchar2,
1680 p_set_of_books_id in number,
1681 p_business_group_id in number,
1682 p_num_proposed_years in number,
1683 p_narrative_description in varchar2,
1684 p_budget_group_type in varchar2,
1685 p_organization_id in number ,
1686 p_request_id in number,
1687 p_segment1_type in number,
1688 p_segment2_type in number,
1689 p_segment3_type in number,
1690 p_segment4_type in number,
1691 p_segment5_type in number,
1692 p_segment6_type in number,
1693 p_segment7_type in number,
1694 p_segment8_type in number,
1695 p_segment9_type in number,
1696 p_segment10_type in number,
1697 p_segment11_type in number,
1698 p_segment12_type in number,
1699 p_segment13_type in number,
1700 p_segment14_type in number,
1701 p_segment15_type in number,
1702 p_segment16_type in number,
1703 p_segment17_type in number,
1704 p_segment18_type in number,
1705 p_segment19_type in number,
1706 p_segment20_type in number,
1707 p_segment21_type in number,
1708 p_segment22_type in number,
1709 p_segment23_type in number,
1710 p_segment24_type in number,
1711 p_segment25_type in number,
1712 p_segment26_type in number,
1713 p_segment27_type in number,
1714 p_segment28_type in number,
1715 p_segment29_type in number,
1716 p_segment30_type in number,
1717 p_attribute1 in varchar2,
1718 p_attribute2 in varchar2,
1719 p_attribute3 in varchar2,
1720 p_attribute4 in varchar2,
1721 p_attribute5 in varchar2,
1722 p_attribute6 in varchar2,
1723 p_attribute7 in varchar2,
1724 p_attribute8 in varchar2,
1725 p_attribute9 in varchar2,
1726 p_attribute10 in varchar2,
1727 p_context in varchar2,
1728 p_mode in varchar2 := 'R'
1729 ) AS
1730
1731 P_LAST_UPDATE_DATE DATE;
1732 P_LAST_UPDATED_BY NUMBER;
1733 P_LAST_UPDATE_LOGIN NUMBER;
1734 --
1735 l_api_name CONSTANT VARCHAR2(30) := 'Update Row';
1736 l_api_version CONSTANT NUMBER := 1.0 ;
1737 --
1738 BEGIN
1739 --
1740 SAVEPOINT Update_Row ;
1741 --
1742 if FND_API.to_Boolean (p_init_msg_list) then
1743 FND_MSG_PUB.initialize;
1744 end if;
1745
1746 -- Initialize API return status to success
1747
1748 p_return_status := FND_API.G_RET_STS_SUCCESS ;
1749
1750 --
1751
1752 P_LAST_UPDATE_DATE := SYSDATE;
1753 if(P_MODE = 'I') then
1754 P_LAST_UPDATED_BY := 1;
1755 P_LAST_UPDATE_LOGIN := 0;
1756 elsif (P_MODE = 'R') then
1757 P_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1758 if P_LAST_UPDATED_BY is NULL then
1759 P_LAST_UPDATED_BY := -1;
1760 end if;
1761 P_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
1762 if P_LAST_UPDATE_LOGIN is NULL then
1763 P_LAST_UPDATE_LOGIN := -1;
1764 end if;
1765 else
1766 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
1767 FND_MSG_PUB.Add ;
1768 raise FND_API.G_EXC_ERROR ;
1769 end if;
1770
1771
1772 update PSB_BUDGET_GROUPS set
1773 name = p_name,
1774 short_name = p_short_name,
1775 root_budget_group = p_root_budget_group,
1776 parent_budget_group_id = p_parent_budget_group_id,
1777 root_budget_group_id = p_root_budget_group_id,
1778 ps_account_position_set_id = p_ps_account_position_set_id,
1779 nps_account_position_set_id = p_nps_account_position_set_id,
1780 budget_group_category_set_id = p_budget_group_category_set_id,
1781 effective_start_date = p_effective_start_date,
1782 effective_end_date = p_effective_end_date,
1783 freeze_hierarchy_flag = p_freeze_hierarchy_flag,
1784 description = p_description,
1785 set_of_books_id = p_set_of_books_id,
1786 business_group_id = p_business_group_id,
1787 num_proposed_years = p_num_proposed_years,
1788 narrative_description = p_narrative_description,
1789 budget_group_type = p_budget_group_type,
1790 organization_id = p_organization_id ,
1791 request_id = p_request_id,
1792 segment1_type = p_segment1_type,
1793 segment2_type = p_segment2_type,
1794 segment3_type = p_segment3_type,
1795 segment4_type = p_segment4_type,
1796 segment5_type = p_segment5_type,
1797 segment6_type = p_segment6_type,
1798 segment7_type = p_segment7_type,
1799 segment8_type = p_segment8_type,
1800 segment9_type = p_segment9_type,
1801 segment10_type = p_segment10_type,
1802 segment11_type = p_segment11_type,
1803 segment12_type = p_segment12_type,
1804 segment13_type = p_segment13_type,
1805 segment14_type = p_segment14_type,
1806 segment15_type = p_segment15_type,
1807 segment16_type = p_segment16_type,
1808 segment17_type = p_segment17_type,
1809 segment18_type = p_segment18_type,
1810 segment19_type = p_segment19_type,
1811 segment20_type = p_segment20_type,
1812 segment21_type = p_segment21_type,
1813 segment22_type = p_segment22_type,
1814 segment23_type = p_segment23_type,
1815 segment24_type = p_segment24_type,
1816 segment25_type = p_segment25_type,
1817 segment26_type = p_segment26_type,
1818 segment27_type = p_segment27_type,
1819 segment28_type = p_segment28_type,
1820 segment29_type = p_segment29_type,
1821 segment30_type = p_segment30_type,
1822 attribute1 = p_attribute1,
1823 attribute2 = p_attribute2,
1824 attribute3 = p_attribute3,
1825 attribute4 = p_attribute4,
1826 attribute5 = p_attribute5,
1827 attribute6 = p_attribute6,
1828 attribute7 = p_attribute7,
1829 attribute8 = p_attribute8,
1830 attribute9 = p_attribute9,
1831 attribute10 = p_attribute10,
1832 context = p_context,
1833 last_update_date = p_last_update_date,
1834 last_updated_by = p_last_updated_by,
1835 last_update_login = p_last_update_login
1836 where BUDGET_GROUP_ID = P_BUDGET_GROUP_ID
1837 ;
1838
1839 if (sql%notfound) then
1840 raise FND_API.G_EXC_ERROR;
1841 end if;
1842
1843 --
1844 -- Standard check of p_commit.
1845 if FND_API.to_Boolean (p_commit) then
1846 commit work;
1847 end if;
1848 -- Standard call to get message count and if count is 1, get message info.
1849 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
1850 p_data => p_msg_data);
1851 --
1852
1853
1854 EXCEPTION
1855 --
1856 when FND_API.G_EXC_ERROR then
1857 --
1858 rollback to UPDATE_ROW ;
1859 p_return_status := FND_API.G_RET_STS_ERROR;
1860 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
1861 p_data => p_msg_data);
1862 --
1863 when FND_API.G_EXC_UNEXPECTED_ERROR then
1864 --
1865 rollback to UPDATE_ROW ;
1866 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1867 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
1868 p_data => p_msg_data);
1869 --
1870 when OTHERS then
1871 --
1872 rollback to UPDATE_ROW ;
1873 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1874 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) then
1875 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
1876 l_api_name);
1877 END if;
1878 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
1879 p_data => p_msg_data);
1880 --
1881
1882
1883 END UPDATE_ROW;
1884
1885 /*---------------------------------------------------------------*/
1886
1887 PROCEDURE ADD_ROW (
1888 p_api_version in number,
1889 p_init_msg_list in varchar2 := fnd_api.g_false,
1890 p_commit in varchar2 := fnd_api.g_false,
1891 p_validation_level in number := fnd_api.g_valid_level_full,
1892 p_return_status OUT NOCOPY varchar2,
1893 p_msg_count OUT NOCOPY number,
1894 p_msg_data OUT NOCOPY varchar2,
1895 p_rowid in OUT NOCOPY varchar2,
1896 p_budget_group_id in number,
1897 p_name in varchar2,
1898 p_short_name in varchar2,
1899 p_root_budget_group in varchar2,
1900 p_parent_budget_group_id in number,
1901 p_root_budget_group_id in number,
1902 p_ps_account_position_set_id in number,
1903 p_nps_account_position_set_id in number,
1904 p_budget_group_category_set_id in number,
1905 p_effective_start_date in date,
1906 p_effective_end_date in date,
1907 p_freeze_hierarchy_flag in varchar2,
1908 p_description in varchar2,
1909 p_set_of_books_id in number,
1910 p_business_group_id in number,
1911 p_num_proposed_years in number,
1912 p_narrative_description in varchar2,
1913 p_budget_group_type in varchar2,
1914 p_organization_id in number ,
1915 p_request_id in number,
1916 p_segment1_type in number,
1917 p_segment2_type in number,
1918 p_segment3_type in number,
1919 p_segment4_type in number,
1920 p_segment5_type in number,
1921 p_segment6_type in number,
1922 p_segment7_type in number,
1923 p_segment8_type in number,
1924 p_segment9_type in number,
1925 p_segment10_type in number,
1926 p_segment11_type in number,
1927 p_segment12_type in number,
1928 p_segment13_type in number,
1929 p_segment14_type in number,
1930 p_segment15_type in number,
1931 p_segment16_type in number,
1932 p_segment17_type in number,
1933 p_segment18_type in number,
1934 p_segment19_type in number,
1935 p_segment20_type in number,
1936 p_segment21_type in number,
1937 p_segment22_type in number,
1938 p_segment23_type in number,
1939 p_segment24_type in number,
1940 p_segment25_type in number,
1941 p_segment26_type in number,
1942 p_segment27_type in number,
1943 p_segment28_type in number,
1944 p_segment29_type in number,
1945 p_segment30_type in number,
1946 p_attribute1 in varchar2,
1947 p_attribute2 in varchar2,
1948 p_attribute3 in varchar2,
1949 p_attribute4 in varchar2,
1950 p_attribute5 in varchar2,
1951 p_attribute6 in varchar2,
1952 p_attribute7 in varchar2,
1953 p_attribute8 in varchar2,
1954 p_attribute9 in varchar2,
1955 p_attribute10 in varchar2,
1956 p_context in varchar2,
1957 p_mode in varchar2 := 'R'
1958 ) AS
1959
1960 cursor c1 is select rowid from PSB_BUDGET_GROUPS
1961 where BUDGET_GROUP_ID = P_BUDGET_GROUP_ID
1962 ;
1963 dummy c1%rowtype;
1964 --
1965 l_api_name CONSTANT VARCHAR2(30) := 'Add Row' ;
1966 l_api_version CONSTANT NUMBER := 1.0 ;
1967 --
1968 BEGIN
1969 --
1970 SAVEPOINT Add_Row ;
1971 --
1972 -- Initialize message list if p_init_msg_list is set to TRUE.
1973 --
1974 if FND_API.to_Boolean (p_init_msg_list) then
1975 FND_MSG_PUB.initialize;
1976 end if;
1977 --
1978 p_return_status := FND_API.G_RET_STS_SUCCESS ;
1979 --
1980 open c1;
1981 fetch c1 into dummy;
1982 if (c1%notfound) then
1983 close c1;
1984 INSERT_ROW (
1985 p_api_version,
1986 p_init_msg_list,
1987 p_commit,
1988 p_validation_level,
1989 p_return_status,
1990 p_msg_count,
1991 p_msg_data,
1992 p_rowid,
1993 p_budget_group_id,
1994 p_name,
1995 p_short_name,
1996 p_root_budget_group,
1997 p_parent_budget_group_id,
1998 p_root_budget_group_id,
1999 p_ps_account_position_set_id,
2000 p_nps_account_position_set_id,
2001 p_budget_group_category_set_id,
2002 p_effective_start_date,
2003 p_effective_end_date,
2004 p_freeze_hierarchy_flag,
2005 p_description,
2006 p_set_of_books_id,
2007 p_business_group_id,
2008 p_num_proposed_years,
2009 p_narrative_description,
2010 p_budget_group_type,
2011 p_organization_id,
2012 p_request_id,
2013 p_segment1_type,
2014 p_segment2_type,
2015 p_segment3_type,
2016 p_segment4_type,
2017 p_segment5_type,
2018 p_segment6_type,
2019 p_segment7_type,
2020 p_segment8_type,
2021 p_segment9_type,
2022 p_segment10_type,
2023 p_segment11_type,
2024 p_segment12_type,
2025 p_segment13_type,
2026 p_segment14_type,
2027 p_segment15_type,
2028 p_segment16_type,
2029 p_segment17_type,
2030 p_segment18_type,
2031 p_segment19_type,
2032 p_segment20_type,
2033 p_segment21_type,
2034 p_segment22_type,
2035 p_segment23_type,
2036 p_segment24_type,
2037 p_segment25_type,
2038 p_segment26_type,
2039 p_segment27_type,
2040 p_segment28_type,
2041 p_segment29_type,
2042 p_segment30_type,
2043 p_attribute1,
2044 p_attribute2,
2045 p_attribute3,
2046 p_attribute4,
2047 p_attribute5,
2048 p_attribute6,
2049 p_attribute7,
2050 p_attribute8,
2051 p_attribute9,
2052 p_attribute10,
2053 p_context,
2054 p_mode);
2055 return;
2056 end if;
2057 close c1;
2058
2059 UPDATE_ROW (
2060 p_api_version,
2061 p_init_msg_list,
2062 p_commit,
2063 p_validation_level,
2064 p_return_status,
2065 p_msg_count,
2066 p_msg_data,
2067 p_budget_group_id,
2068 p_name,
2069 p_short_name,
2070 p_root_budget_group,
2071 p_parent_budget_group_id,
2072 p_root_budget_group_id,
2073 p_ps_account_position_set_id,
2074 p_nps_account_position_set_id,
2075 p_budget_group_category_set_id,
2076 p_effective_start_date,
2077 p_effective_end_date,
2078 p_freeze_hierarchy_flag,
2079 p_description,
2080 p_set_of_books_id,
2081 p_business_group_id,
2082 p_num_proposed_years,
2083 p_narrative_description,
2084 p_budget_group_type,
2085 p_organization_id,
2086 p_request_id,
2087 p_segment1_type,
2088 p_segment2_type,
2089 p_segment3_type,
2090 p_segment4_type,
2091 p_segment5_type,
2092 p_segment6_type,
2093 p_segment7_type,
2094 p_segment8_type,
2095 p_segment9_type,
2096 p_segment10_type,
2097 p_segment11_type,
2098 p_segment12_type,
2099 p_segment13_type,
2100 p_segment14_type,
2101 p_segment15_type,
2102 p_segment16_type,
2103 p_segment17_type,
2104 p_segment18_type,
2105 p_segment19_type,
2106 p_segment20_type,
2107 p_segment21_type,
2108 p_segment22_type,
2109 p_segment23_type,
2110 p_segment24_type,
2111 p_segment25_type,
2112 p_segment26_type,
2113 p_segment27_type,
2114 p_segment28_type,
2115 p_segment29_type,
2116 p_segment30_type,
2117 p_attribute1,
2118 p_attribute2,
2119 p_attribute3,
2120 p_attribute4,
2121 p_attribute5,
2122 p_attribute6,
2123 p_attribute7,
2124 p_attribute8,
2125 p_attribute9,
2126 p_attribute10,
2127 p_context,
2128 p_mode);
2129
2130 --
2131 -- Standard check of p_commit.
2132 if FND_API.to_Boolean (p_commit) then
2133 commit work;
2134 end if;
2135 -- Standard call to get message count and if count is 1, get message info.
2136 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
2137 p_data => p_msg_data);
2138 --
2139
2140 EXCEPTION
2141 --
2142 when FND_API.G_EXC_ERROR then
2143 --
2144 rollback to ADD_ROW ;
2145 p_return_status := FND_API.G_RET_STS_ERROR;
2146 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
2147 p_data => p_msg_data);
2148 --
2149 when FND_API.G_EXC_UNEXPECTED_ERROR then
2150 --
2151 rollback to ADD_ROW ;
2152 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2153 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
2154 p_data => p_msg_data);
2155 --
2156 when OTHERS then
2157 --
2158 rollback to ADD_ROW ;
2159 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2160 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) then
2161 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
2162 l_api_name);
2163 END if;
2164 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
2165 p_data => p_msg_data);
2166 --
2167 END ADD_ROW;
2168
2169 /*---------------------------------------------------------------*/
2170
2171 PROCEDURE DELETE_ROW (
2172 p_api_version in number,
2173 p_init_msg_list in varchar2 := fnd_api.g_false,
2174 p_commit in varchar2 := fnd_api.g_false,
2175 p_validation_level in number := fnd_api.g_valid_level_full,
2176 p_return_status OUT NOCOPY varchar2,
2177 p_msg_count OUT NOCOPY number,
2178 p_msg_data OUT NOCOPY varchar2,
2179 p_budget_group_id in number,
2180 p_delete OUT NOCOPY varchar2
2181 ) AS
2182 cursor C1 is
2183 select budget_group_id,
2184 short_name
2185 from psb_budget_groups
2186 where budget_group_type = 'R'
2187 start with budget_group_id = p_budget_group_id
2188 connect by prior budget_group_id = parent_budget_group_id;
2189
2190 l_budget_group_id NUMBER;
2191 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Row';
2192 l_api_version CONSTANT NUMBER := 1.0;
2193
2194 CURSOR c_p1 IS
2195 SELECT 'PSB_POSITION_ACCOUNTS'
2196 FROM dual
2197 WHERE exists
2198 (SELECT 1
2199 FROM PSB_POSITION_ACCOUNTS
2200 WHERE budget_group_id = l_budget_group_id
2201 );
2202 l_p1_exists VARCHAR2(1) := FND_API.G_FALSE;
2203
2204 CURSOR c_p2 IS
2205 SELECT 'PSB_ACCOUNT_POSITION_SETS'
2206 FROM dual
2207 WHERE exists
2208 (SELECT 1
2209 FROM PSB_ACCOUNT_POSITION_SETS
2210 WHERE budget_group_id = l_budget_group_id
2211 );
2212 l_p2_exists VARCHAR2(1) := FND_API.G_FALSE;
2213 CURSOR c_p3 IS
2214 SELECT 'PSB_BUDGET_REVISIONS'
2215 FROM dual
2216 WHERE exists
2217 (SELECT 1
2218 FROM PSB_BUDGET_REVISIONS
2219 WHERE budget_group_id = l_budget_group_id
2220 );
2221 l_p3_exists VARCHAR2(1) := FND_API.G_FALSE;
2222 CURSOR c_p4 IS
2223 SELECT 'PSB_BUDGET_REVISION_POSITIONS'
2224 FROM dual
2225 WHERE exists
2226 (SELECT 1
2227 FROM PSB_BUDGET_REVISION_POSITIONS
2228 WHERE budget_group_id = l_budget_group_id
2229 );
2230 l_p4_exists VARCHAR2(1) := FND_API.G_FALSE;
2231 CURSOR c_p5 IS
2232 SELECT 'PSB_BUDGET_REVISION_ACCOUNTS'
2233 FROM dual
2234 WHERE exists
2235 (SELECT 1
2236 FROM PSB_BUDGET_REVISION_ACCOUNTS
2237 WHERE budget_group_id = l_budget_group_id
2238 );
2239 l_p5_exists VARCHAR2(1) := FND_API.G_FALSE;
2240 CURSOR c_p6 IS
2241 SELECT 'PSB_BUDGET_WORKFLOW_RULES'
2242 FROM dual
2243 WHERE exists
2244 (SELECT 1
2245 FROM PSB_BUDGET_WORKFLOW_RULES
2246 WHERE budget_group_id = l_budget_group_id
2247 );
2248 l_p6_exists VARCHAR2(1) := FND_API.G_FALSE;
2249 CURSOR c_p7 IS
2250 SELECT 'PSB_DATA_EXTRACTS'
2251 FROM dual
2252 WHERE exists
2253 (SELECT 1
2254 FROM PSB_DATA_EXTRACTS
2255 WHERE budget_group_id = l_budget_group_id
2256 );
2257 l_p7_exists VARCHAR2(1) := FND_API.G_FALSE;
2258
2259 CURSOR c_p8 IS
2260 SELECT 'PSB_WS_POSITION_LINES'
2261 FROM dual
2262 WHERE exists
2263 (SELECT 1
2264 FROM PSB_WS_POSITION_LINES
2265 WHERE budget_group_id = l_budget_group_id
2266 );
2267 l_p8_exists VARCHAR2(1) := FND_API.G_FALSE;
2268 CURSOR c_p9 IS
2269 SELECT 'PSB_POSITIONS'
2270 FROM dual
2271 WHERE exists
2272 (SELECT 1
2273 FROM PSB_POSITIONS
2274 WHERE budget_group_id = l_budget_group_id
2275 );
2276 l_p9_exists VARCHAR2(1) := FND_API.G_FALSE;
2277
2278 CURSOR c_p10 IS
2279 SELECT 'PSB_ENTITY_SET'
2280 FROM dual
2281 WHERE exists
2282 (SELECT 1
2283 FROM PSB_ENTITY_SET
2284 WHERE budget_group_id = l_budget_group_id
2285 );
2286 l_p10_exists VARCHAR2(1) := FND_API.G_FALSE;
2287
2288 CURSOR c_p11 IS
2289 SELECT 'PSB_ENTITY'
2290 FROM dual
2291 WHERE exists
2292 (SELECT 1
2293 FROM PSB_ENTITY
2294 WHERE budget_group_id = l_budget_group_id
2295 );
2296 l_p11_exists VARCHAR2(1) := FND_API.G_FALSE;
2297
2298 CURSOR c_p12 IS
2299 SELECT 'PSB_WS_DISTRIBUTION_RULE_LINES'
2300 FROM dual
2301 WHERE exists
2302 (SELECT 1
2303 FROM PSB_WS_DISTRIBUTION_RULE_LINES
2304 WHERE budget_group_id = l_budget_group_id
2305 );
2306 l_p12_exists VARCHAR2(1) := FND_API.G_FALSE;
2307
2308 CURSOR c_p13 IS
2309 SELECT 'PSB_WS_DISTRIBUTION_RULES'
2310 FROM dual
2311 WHERE exists
2312 (SELECT 1
2313 FROM PSB_WS_DISTRIBUTION_RULES
2314 WHERE budget_group_id = l_budget_group_id
2315 );
2316 l_p13_exists VARCHAR2(1) := FND_API.G_FALSE;
2317
2318 CURSOR c_p14 IS
2319 SELECT 'PSB_WS_LINE_BALANCES_I'
2320 FROM dual
2321 WHERE exists
2322 (SELECT 1
2323 FROM PSB_WS_LINE_BALANCES_I
2324 WHERE budget_group_id = l_budget_group_id
2325 );
2326 l_p14_exists VARCHAR2(1) := FND_API.G_FALSE;
2327
2328 CURSOR c_p15 IS
2329 SELECT 'PSB_WORKSHEETS'
2330 FROM dual
2331 WHERE exists
2332 (SELECT 1
2333 FROM PSB_WORKSHEETS
2334 WHERE budget_group_id = l_budget_group_id
2335 );
2336 l_p15_exists VARCHAR2(1) := FND_API.G_FALSE;
2337
2338 CURSOR c_p16 IS
2339 SELECT 'PSB_WS_ACCOUNT_LINES'
2340 FROM dual
2341 WHERE exists
2342 (SELECT 1
2343 FROM PSB_WS_ACCOUNT_LINES
2344 WHERE budget_group_id = l_budget_group_id
2345 );
2346 l_p16_exists VARCHAR2(1) := FND_API.G_FALSE;
2347
2348
2349 BEGIN
2350
2351 -- Standard Start of API savepoint
2352
2353 SAVEPOINT Delete_Row;
2354
2355 -- Standard call to check for call compatibility.
2356
2357 if not FND_API.Compatible_API_Call (l_api_version,
2358 p_api_version,
2359 l_api_name,
2360 G_PKG_NAME)
2361 then
2362 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2363 end if;
2364
2365 -- Initialize message list if p_init_msg_list is set to TRUE.
2366
2367 if FND_API.to_Boolean (p_init_msg_list) then
2368 FND_MSG_PUB.initialize;
2369 end if;
2370
2371 -- Initialize API return status to success
2372
2373 p_return_status := FND_API.G_RET_STS_SUCCESS;
2374
2375 -- API body
2376
2377
2378
2379 for c1rec in C1 LOOP
2380
2381 l_budget_group_id := c1rec.budget_group_id;
2382
2383 -- integrity check
2384 for c_p1_rec in c_p1 loop
2385 l_p1_exists := FND_API.G_TRUE;
2386 end loop;
2387
2388 IF FND_API.to_Boolean(l_p1_exists) THEN
2389 rollback to Delete_Row;
2390 message_token('TABLE','PSB_POSITION_ACCOUNTS' );
2391 message_token('BUDGET_GROUP', c1rec.short_name);
2392 add_message('PSB', 'PSB_BG_CANNOT_BE_DELETED');
2393 p_delete := 'NO_DELETE';
2394 raise FND_API.G_EXC_ERROR;
2395 end if;
2396
2397 for c_p2_rec in c_p2 loop
2398 l_p2_exists := FND_API.G_TRUE;
2399 end loop;
2400
2401 IF FND_API.to_Boolean(l_p2_exists) THEN
2402 rollback to Delete_Row;
2403 message_token('TABLE','PSB_ACCOUNT_POSITION_SETS' );
2404 message_token('BUDGET_GROUP', c1rec.short_name);
2405 add_message('PSB', 'PSB_BG_CANNOT_BE_DELETED');
2406 p_delete := 'NO_DELETE';
2407 raise FND_API.G_EXC_ERROR;
2408 end if;
2409
2410 for c_p3_rec in c_p3 loop
2411 l_p3_exists := FND_API.G_TRUE;
2412 end loop;
2413
2414 IF FND_API.to_Boolean(l_p3_exists) THEN
2415 rollback to Delete_Row;
2416 message_token('TABLE','PSB_BUDGET_REVISIONS' );
2417 message_token('BUDGET_GROUP', c1rec.short_name);
2418 add_message('PSB', 'PSB_BG_CANNOT_BE_DELETED');
2419 p_delete := 'NO_DELETE';
2420 raise FND_API.G_EXC_ERROR;
2421 end if;
2422
2423 for c_p4_rec in c_p4 loop
2424 l_p4_exists := FND_API.G_TRUE;
2425 end loop;
2426
2427 IF FND_API.to_Boolean(l_p4_exists) THEN
2428 rollback to Delete_Row;
2429 message_token('TABLE','PSB_BUDGET_REVISION_POSITIONS' );
2430 message_token('BUDGET_GROUP', c1rec.short_name);
2431 add_message('PSB', 'PSB_BG_CANNOT_BE_DELETED');
2432 p_delete := 'NO_DELETE';
2433 raise FND_API.G_EXC_ERROR;
2434 end if;
2435
2436 for c_p5_rec in c_p5 loop
2437 l_p5_exists := FND_API.G_TRUE;
2438 end loop;
2439
2440 IF FND_API.to_Boolean(l_p5_exists) THEN
2441 rollback to Delete_Row;
2442 message_token('TABLE','PSB_BUDGET_REVISION_ACCOUNTS' );
2443 message_token('BUDGET_GROUP', c1rec.short_name);
2444 add_message('PSB', 'PSB_BG_CANNOT_BE_DELETED');
2445 p_delete := 'NO_DELETE';
2446 raise FND_API.G_EXC_ERROR;
2447 end if;
2448
2449 for c_p6_rec in c_p6 loop
2450 l_p6_exists := FND_API.G_TRUE;
2451 end loop;
2452
2453 IF FND_API.to_Boolean(l_p6_exists) THEN
2454 rollback to Delete_Row;
2455 message_token('TABLE','PSB_BUDGET_WORKFLOW_RULES' );
2456 message_token('BUDGET_GROUP', c1rec.short_name);
2457 add_message('PSB', 'PSB_BG_CANNOT_BE_DELETED');
2458 p_delete := 'NO_DELETE';
2459 raise FND_API.G_EXC_ERROR;
2460 end if;
2461
2462 for c_p7_rec in c_p7 loop
2463 l_p7_exists := FND_API.G_TRUE;
2464 end loop;
2465
2466 IF FND_API.to_Boolean(l_p7_exists) THEN
2467 rollback to Delete_Row;
2468 message_token('TABLE','PSB_DATA_EXTRACTS' );
2469 message_token('BUDGET_GROUP', c1rec.short_name);
2470 add_message('PSB', 'PSB_BG_CANNOT_BE_DELETED');
2471 p_delete := 'NO_DELETE';
2472 raise FND_API.G_EXC_ERROR;
2473 end if;
2474
2475 for c_p8_rec in c_p8 loop
2476 l_p8_exists := FND_API.G_TRUE;
2477 end loop;
2478
2479 IF FND_API.to_Boolean(l_p8_exists) THEN
2480 rollback to Delete_Row;
2481 message_token('TABLE','PSB_WS_POSITION_LINES' );
2482 message_token('BUDGET_GROUP', c1rec.short_name);
2483 add_message('PSB', 'PSB_BG_CANNOT_BE_DELETED');
2484 p_delete := 'NO_DELETE';
2485 raise FND_API.G_EXC_ERROR;
2486 end if;
2487
2488 for c_p9_rec in c_p9 loop
2489 l_p9_exists := FND_API.G_TRUE;
2490 end loop;
2491
2492 IF FND_API.to_Boolean(l_p9_exists) THEN
2493 rollback to Delete_Row;
2494 message_token('TABLE','PSB_POSITIONS' );
2495 message_token('BUDGET_GROUP', c1rec.short_name);
2496 add_message('PSB', 'PSB_BG_CANNOT_BE_DELETED');
2497 p_delete := 'NO_DELETE';
2498 raise FND_API.G_EXC_ERROR;
2499 end if;
2500
2501 for c_p10_rec in c_p10 loop
2502 l_p10_exists := FND_API.G_TRUE;
2503 end loop;
2504
2505 IF FND_API.to_Boolean(l_p10_exists) THEN
2506 rollback to Delete_Row;
2507 message_token('TABLE','PSB_ENTITY_SET' );
2508 message_token('BUDGET_GROUP', c1rec.short_name);
2509 add_message('PSB', 'PSB_BG_CANNOT_BE_DELETED');
2510 p_delete := 'NO_DELETE';
2511 raise FND_API.G_EXC_ERROR;
2512 end if;
2513
2514 for c_p11_rec in c_p11 loop
2515 l_p11_exists := FND_API.G_TRUE;
2516 end loop;
2517
2518 IF FND_API.to_Boolean(l_p11_exists) THEN
2519 rollback to Delete_Row;
2520 message_token('TABLE','PSB_ENTITY' );
2521 message_token('BUDGET_GROUP', c1rec.short_name);
2522 add_message('PSB', 'PSB_BG_CANNOT_BE_DELETED');
2523 p_delete := 'NO_DELETE';
2524 raise FND_API.G_EXC_ERROR;
2525 end if;
2526
2527 for c_p12_rec in c_p12 loop
2528 l_p12_exists := FND_API.G_TRUE;
2529 end loop;
2530
2531 IF FND_API.to_Boolean(l_p12_exists) THEN
2532 rollback to Delete_Row;
2533 message_token('TABLE','PSB_WS_DISTRIBUTION_RULE_LINES' );
2534 message_token('BUDGET_GROUP', c1rec.short_name);
2535 add_message('PSB', 'PSB_BG_CANNOT_BE_DELETED');
2536 p_delete := 'NO_DELETE';
2537 raise FND_API.G_EXC_ERROR;
2538 end if;
2539
2540 for c_p13_rec in c_p13 loop
2541 l_p13_exists := FND_API.G_TRUE;
2542 end loop;
2543
2544 IF FND_API.to_Boolean(l_p13_exists) THEN
2545 rollback to Delete_Row;
2546 message_token('TABLE','PSB_WS_DISTRIBUTION_RULES' );
2547 message_token('BUDGET_GROUP', c1rec.short_name);
2548 add_message('PSB', 'PSB_BG_CANNOT_BE_DELETED');
2549 p_delete := 'NO_DELETE';
2550 raise FND_API.G_EXC_ERROR;
2551 end if;
2552
2553 for c_p14_rec in c_p14 loop
2554 l_p14_exists := FND_API.G_TRUE;
2555 end loop;
2556
2557 IF FND_API.to_Boolean(l_p14_exists) THEN
2558 rollback to Delete_Row;
2559 message_token('TABLE','PSB_WS_LINE_BALANCES_I' );
2560 message_token('BUDGET_GROUP', c1rec.short_name);
2561 add_message('PSB', 'PSB_BG_CANNOT_BE_DELETED');
2562 p_delete := 'NO_DELETE';
2563 raise FND_API.G_EXC_ERROR;
2564 end if;
2565
2566 for c_p15_rec in c_p15 loop
2567 l_p15_exists := FND_API.G_TRUE;
2568 end loop;
2569
2570 IF FND_API.to_Boolean(l_p15_exists) THEN
2571 rollback to Delete_Row;
2572 message_token('TABLE','PSB_WORKSHEETS' );
2573 message_token('BUDGET_GROUP', c1rec.short_name);
2574 add_message('PSB', 'PSB_BG_CANNOT_BE_DELETED');
2575 p_delete := 'NO_DELETE';
2576 raise FND_API.G_EXC_ERROR;
2577 end if;
2578
2579 for c_p16_rec in c_p16 loop
2580 l_p16_exists := FND_API.G_TRUE;
2581 end loop;
2582
2583 IF FND_API.to_Boolean(l_p16_exists) THEN
2584 rollback to Delete_Row;
2585 message_token('TABLE','PSB_WS_ACCOUNT_LINES' );
2586 message_token('BUDGET_GROUP', c1rec.short_name);
2587 add_message('PSB', 'PSB_BG_CANNOT_BE_DELETED');
2588 p_delete := 'NO_DELETE';
2589 raise FND_API.G_EXC_ERROR;
2590 end if;
2591
2592
2593
2594
2595 -- proceed with deletion
2596
2597 delete psb_budget_groups where budget_group_id = c1rec.budget_group_id;
2598 delete psb_budget_group_resp where budget_group_id = c1rec.budget_group_id;
2599 if (sql%notfound) then
2600 null;
2601 end if;
2602 delete psb_set_relations where budget_group_id = c1rec.budget_group_id;
2603 if (sql%notfound) then
2604 null;
2605 end if;
2606 delete psb_budget_group_categories where budget_group_id = c1rec.budget_group_id;
2607 if (sql%notfound) then
2608 null;
2609 end if;
2610 end loop;
2611 -- deleting top level bg
2612 delete psb_budget_groups where budget_group_id = p_budget_group_id;
2613 delete psb_budget_group_resp where budget_group_id = p_budget_group_id;
2614 if (sql%notfound) then
2615 null;
2616 end if;
2617 delete psb_set_relations where budget_group_id = p_budget_group_id;
2618 if (sql%notfound) then
2619 null;
2620 end if;
2621 delete psb_budget_group_categories where budget_group_id = p_budget_group_id;
2622 if (sql%notfound) then
2623 null;
2624 end if;
2625
2626 p_delete := 'DELETE';
2627 p_return_status := FND_API.G_RET_STS_SUCCESS;
2628
2629 -- End of API body.
2630
2631 -- Standard check of p_commit.
2632
2633 if FND_API.to_Boolean (p_commit) then
2634 commit work;
2635 end if;
2636
2637 -- Standard call to get message count and if count is 1, get message info.
2638
2639 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
2640 p_data => p_msg_data);
2641
2642 EXCEPTION
2643
2644 when FND_API.G_EXC_ERROR then
2645
2646 rollback to Delete_Row;
2647
2648 p_return_status := FND_API.G_RET_STS_ERROR;
2649
2650 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
2651 p_data => p_msg_data);
2652
2653
2654 when FND_API.G_EXC_UNEXPECTED_ERROR then
2655
2656 rollback to Delete_Row;
2657
2658 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2659
2660 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
2661 p_data => p_msg_data);
2662
2663
2664 when OTHERS then
2665
2666 rollback to Delete_Row;
2667
2668 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2669
2670 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) then
2671
2672 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
2673 l_api_name);
2674 end if;
2675
2676 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
2677 p_data => p_msg_data);
2678
2679 END Delete_Row;
2680
2681 PROCEDURE Delete_Review_Group (
2682 p_api_version in number,
2683 p_init_msg_list in varchar2 := fnd_api.g_false,
2684 p_commit in varchar2 := fnd_api.g_false,
2685 p_validation_level in number := fnd_api.g_valid_level_full,
2686 p_return_status OUT NOCOPY varchar2,
2687 p_msg_count OUT NOCOPY number,
2688 p_msg_data OUT NOCOPY varchar2,
2689
2690 p_budget_group_id in number
2691 ) AS
2692 --
2693 l_api_name CONSTANT VARCHAR2(30) := 'Delete Review Group' ;
2694 l_api_version CONSTANT NUMBER := 1.0 ;
2695 --
2696 BEGIN
2697 --
2698 SAVEPOINT Delete_Review_Group ;
2699 --
2700 -- Initialize message list if p_init_msg_list is set to TRUE.
2701 --
2702 if FND_API.to_Boolean (p_init_msg_list) then
2703 FND_MSG_PUB.initialize;
2704 end if;
2705 --
2706 p_return_status := FND_API.G_RET_STS_SUCCESS ;
2707 --
2708 delete from PSB_BUDGET_GROUPS
2709 where BUDGET_GROUP_ID = P_BUDGET_GROUP_ID;
2710 if (sql%notfound) then
2711 raise FND_API.G_EXC_ERROR ;
2712 end if;
2713
2714 delete psb_budget_group_resp where budget_group_id = P_BUDGET_GROUP_ID;
2715 if (sql%notfound) then
2716 null;
2717 end if;
2718 --
2719 -- Standard check of p_commit.
2720 if FND_API.to_Boolean (p_commit) then
2721 commit work;
2722 end if;
2723 -- Standard call to get message count and if count is 1, get message info.
2724 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
2725 p_data => p_msg_data);
2726 --
2727
2728 EXCEPTION
2729 --
2730 when FND_API.G_EXC_ERROR then
2731 --
2732 rollback to Delete_Review_Group ;
2733 p_return_status := FND_API.G_RET_STS_ERROR;
2734 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
2735 p_data => p_msg_data);
2736 --
2737 when FND_API.G_EXC_UNEXPECTED_ERROR then
2738 --
2739 rollback to Delete_Review_Group ;
2740 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2741 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
2742 p_data => p_msg_data);
2743 --
2744 when OTHERS then
2745 --
2746 rollback to DELETE_ROW ;
2747 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2748 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) then
2749 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
2750 l_api_name);
2751 END if;
2752 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
2753 p_data => p_msg_data);
2754 --
2755
2756 END Delete_Review_Group;
2757
2758 /*---------------------------------------------------------------*/
2759
2760 PROCEDURE Copy_Budget_Group
2761 ( p_api_version IN NUMBER,
2762 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2763 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2764 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2765 p_src_budget_group_id IN NUMBER,
2766 p_curr_budget_group_id IN NUMBER,
2767 p_return_status OUT NOCOPY VARCHAR2,
2768 p_msg_count OUT NOCOPY NUMBER,
2769 p_msg_data OUT NOCOPY VARCHAR2
2770 ) AS
2771
2772 Cursor BG_grp_Cur Is
2773 select short_name
2774 from psb_budget_groups
2775 where budget_group_id = p_curr_budget_group_id ;
2776 Bgg_Rec BG_grp_Cur%ROWTYPE;
2777
2778 Cursor BG_resp_Cur Is
2779 select responsibility_id,responsibility_type
2780 from psb_budget_group_resp
2781 where budget_group_id = p_src_budget_group_id
2782 and responsibility_type = 'R';
2783
2784 Bgr_Rec BG_resp_Cur%ROWTYPE;
2785
2786 Cursor BG_Role_Cur Is
2787 select wf_role_name,wf_role_orig_system,wf_role_orig_system_id,
2788 responsibility_type from
2789 psb_budget_group_resp
2790 where budget_group_id = p_src_budget_group_id
2791 and responsibility_type = 'N';
2792
2793 Bgw_Rec BG_Role_Cur%ROWTYPE;
2794
2795
2796 Cursor BG_WF_Cur Is
2797 select stage_id
2798 from psb_budget_group_categories
2799 where budget_group_id = p_src_budget_group_id;
2800
2801 Bgwf_Rec BG_WF_Cur%ROWTYPE;
2802
2803 Cursor BG_SR_Cur IS
2804 select name,
2805 set_relation_id,
2806 set_of_books_id,
2807 data_extract_id,
2808 global_or_local_type,
2809 account_or_position_type ,
2810 business_group_id ,
2811 effective_start_date,
2812 effective_end_date,
2813 account_position_set_id,
2814 attribute_selection_type,
2815 use_in_budget_group_flag
2816 from psb_set_relations_v
2817 where budget_group_id = p_src_budget_group_id;
2818
2819 Bgsr_Rec BG_SR_Cur%ROWTYPE;
2820
2821 Cursor BG_AL_Cur IS
2822 select
2823 LINE_SEQUENCE_ID,
2824 ACCOUNT_POSITION_SET_ID,
2825 DESCRIPTION,
2826 BUSINESS_GROUP_ID,
2827 ATTRIBUTE_ID,
2828 INCLUDE_OR_EXCLUDE_TYPE,
2829 SEGMENT1_LOW ,
2830 SEGMENT2_LOW ,
2831 SEGMENT3_LOW ,
2832 SEGMENT4_LOW ,
2833 SEGMENT5_LOW ,
2834 SEGMENT6_LOW ,
2835 SEGMENT7_LOW ,
2836 SEGMENT8_LOW ,
2837 SEGMENT9_LOW ,
2838 SEGMENT10_LOW ,
2839 SEGMENT11_LOW ,
2840 SEGMENT12_LOW ,
2841 SEGMENT13_LOW ,
2842 SEGMENT14_LOW ,
2843 SEGMENT15_LOW ,
2844 SEGMENT16_LOW ,
2845 SEGMENT17_LOW ,
2846 SEGMENT18_LOW ,
2847 SEGMENT19_LOW ,
2848 SEGMENT20_LOW ,
2849 SEGMENT21_LOW ,
2850 SEGMENT22_LOW ,
2851 SEGMENT23_LOW ,
2852 SEGMENT24_LOW ,
2853 SEGMENT25_LOW ,
2854 SEGMENT26_LOW ,
2855 SEGMENT27_LOW ,
2856 SEGMENT28_LOW ,
2857 SEGMENT29_LOW ,
2858 SEGMENT30_LOW ,
2859 SEGMENT1_HIGH ,
2860 SEGMENT2_HIGH ,
2861 SEGMENT3_HIGH ,
2862 SEGMENT4_HIGH ,
2863 SEGMENT5_HIGH ,
2864 SEGMENT6_HIGH ,
2865 SEGMENT7_HIGH ,
2866 SEGMENT8_HIGH ,
2867 SEGMENT9_HIGH ,
2868 SEGMENT10_HIGH ,
2869 SEGMENT11_HIGH ,
2870 SEGMENT12_HIGH ,
2871 SEGMENT13_HIGH ,
2872 SEGMENT14_HIGH ,
2873 SEGMENT15_HIGH ,
2874 SEGMENT16_HIGH ,
2875 SEGMENT17_HIGH ,
2876 SEGMENT18_HIGH ,
2877 SEGMENT19_HIGH ,
2878 SEGMENT20_HIGH ,
2879 SEGMENT21_HIGH ,
2880 SEGMENT22_HIGH ,
2881 SEGMENT23_HIGH ,
2882 SEGMENT24_HIGH ,
2883 SEGMENT25_HIGH ,
2884 SEGMENT26_HIGH ,
2885 SEGMENT27_HIGH ,
2886 SEGMENT28_HIGH ,
2887 SEGMENT29_HIGH ,
2888 SEGMENT30_HIGH ,
2889 CONTEXT ,
2890 ATTRIBUTE1 ,
2891 ATTRIBUTE2 ,
2892 ATTRIBUTE3 ,
2893 ATTRIBUTE4 ,
2894 ATTRIBUTE5 ,
2895 ATTRIBUTE6 ,
2896 ATTRIBUTE7 ,
2897 ATTRIBUTE8 ,
2898 ATTRIBUTE9 ,
2899 ATTRIBUTE10
2900 from PSB_ACCT_POSITION_SET_LINES_V
2901 where account_position_set_id = Bgsr_Rec.account_position_set_id;
2902
2903 Bgal_Rec BG_AL_Cur%ROWTYPE;
2904
2905 l_api_name CONSTANT VARCHAR2(30) := 'Copy_Budget_Group';
2906 l_api_version CONSTANT NUMBER := 1.0;
2907 l_last_update_date date;
2908 l_last_updated_by number;
2909 l_last_update_login number;
2910 l_creation_date date;
2911 l_created_by number;
2912 l_budget_group_resp_id number;
2913 l_budget_group_category_id number;
2914 l_curr_bg_short_name Varchar2(30);
2915 l_set_name_seq number := NULL;
2916
2917 BEGIN
2918
2919 -- Standard Start of API savepoint
2920
2921 SAVEPOINT Copy_Budget_Group;
2922
2923 -- Standard call to check for call compatibility.
2924
2925 if not FND_API.Compatible_API_Call (l_api_version,
2926 p_api_version,
2927 l_api_name,
2928 G_PKG_NAME)
2929 then
2930 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2931 end if;
2932
2933 -- Initialize message list if p_init_msg_list is set to TRUE.
2934
2935 if FND_API.to_Boolean (p_init_msg_list) then
2936 FND_MSG_PUB.initialize;
2937 end if;
2938
2939 -- Initialize API return status to success
2940
2941 p_return_status := FND_API.G_RET_STS_SUCCESS;
2942
2943 l_last_update_date := sysdate;
2944 l_last_updated_by := FND_GLOBAL.USER_ID;
2945 l_last_update_login :=FND_GLOBAL.LOGIN_ID;
2946 l_creation_date := sysdate;
2947 l_created_by := FND_GLOBAL.USER_ID;
2948
2949 Open BG_grp_Cur;
2950 Fetch BG_grp_Cur INTO BGG_Rec;
2951 l_curr_bg_short_name := BGG_Rec.Short_Name;
2952 Close BG_grp_Cur;
2953
2954 -- API body
2955 Begin
2956 Open BG_resp_Cur;
2957
2958 Loop
2959 Fetch BG_resp_Cur INTO Bgr_Rec;
2960
2961 if BG_resp_Cur%NOTFOUND then
2962 EXIT;
2963 else
2964 select PSB_BUDGET_GROUP_RESP_S.NEXTVAL
2965 Into l_budget_group_resp_id from DUAL ;
2966
2967 Insert into psb_budget_group_resp
2968 (budget_group_resp_id,
2969 budget_group_id,
2970 responsibility_id,
2971 responsibility_type,
2972 last_update_date,
2973 last_updated_by,
2974 last_update_login,
2975 created_by,
2976 creation_date)
2977 values
2978 (
2979 l_budget_group_resp_id,
2980 p_curr_budget_group_id,
2981 Bgr_Rec.responsibility_id,
2982 Bgr_Rec.responsibility_type,
2983 l_last_update_date,
2984 l_last_updated_by,
2985 l_last_update_login,
2986 l_created_by,
2987 l_creation_date);
2988
2989 end if;
2990 end loop;
2991 CLOSE BG_resp_Cur ;
2992
2993 end;
2994 begin
2995 Open BG_Role_Cur;
2996
2997 Loop
2998 Fetch BG_Role_Cur INTO Bgw_Rec;
2999
3000 if BG_Role_Cur%NOTFOUND then
3001 EXIT;
3002 else
3003 select PSB_BUDGET_GROUP_RESP_S.NEXTVAL
3004 Into l_budget_group_resp_id from DUAL ;
3005
3006 Insert into psb_budget_group_resp
3007 (budget_group_resp_id,
3008 budget_group_id,
3009 wf_role_name,
3010 wf_role_orig_system,
3011 wf_role_orig_system_id,
3012 responsibility_type,
3013 last_update_date,
3014 last_updated_by,
3015 last_update_login,
3016 created_by,
3017 creation_date)
3018 values
3019 (
3020 l_budget_group_resp_id,
3021 p_curr_budget_group_id,
3022 Bgw_Rec.wf_role_name,
3023 Bgw_Rec.wf_role_orig_system,
3024 Bgw_Rec.wf_role_orig_system_id,
3025 Bgw_Rec.responsibility_type,
3026 l_last_update_date,
3027 l_last_updated_by,
3028 l_last_update_login,
3029 l_created_by,
3030 l_creation_date);
3031
3032 end if;
3033 end loop;
3034 CLOSE BG_Role_Cur ;
3035
3036 end;
3037
3038 begin
3039 Open BG_WF_Cur;
3040
3041 Loop
3042 Fetch BG_WF_Cur INTO Bgwf_Rec;
3043
3044 if BG_WF_Cur%NOTFOUND then
3045 EXIT;
3046 else
3047 select psb_budget_group_categories_s.NEXTVAL
3048 Into l_budget_group_category_id from DUAL ;
3049
3050 Insert into psb_budget_group_categories
3051 ( budget_group_category_id ,
3052 budget_group_id ,
3053 stage_id ,
3054 last_updated_date ,
3055 last_updated_by ,
3056 last_update_login ,
3057 created_by ,
3058 created_date )
3059 values
3060 (l_budget_group_category_id,
3061 p_curr_budget_group_id,
3062 Bgwf_Rec.stage_id,
3063 l_last_update_date,
3064 l_last_updated_by,
3065 l_last_update_login,
3066 l_created_by,
3067 l_creation_date);
3068
3069 end if;
3070 end loop;
3071 Close BG_WF_Cur ;
3072
3073
3074 end;
3075 declare
3076 l_row_id varchar2(100);
3077 l_row_id1 varchar2(100);
3078 l_return_status varchar2(1);
3079 l_return_status1 varchar2(1);
3080 l_msg_count number;
3081 l_msg_data varchar2(2000);
3082 l_msg_count1 number;
3083 l_msg_data1 varchar2(2000);
3084 l_name varchar2(100);
3085 l_account_position_set_id number;
3086 l_set_relation_id number;
3087 l_valid boolean;
3088 l_repeat number;
3089 l_count number;
3090
3091
3092 begin
3093 Open BG_SR_Cur;
3094
3095 Loop
3096 Fetch BG_SR_Cur INTO Bgsr_Rec;
3097
3098 if BG_SR_Cur%NOTFOUND then
3099 EXIT;
3100 else
3101 -- initialize
3102 l_account_position_set_id := NULL;
3103
3104 -- get set name
3105
3106 l_name := l_curr_bg_short_name;
3107 l_valid := FALSE;
3108 l_repeat := 0;
3109
3110 loop
3111 l_repeat := nvl(l_repeat,0) + 1;
3112 exit when l_repeat > 10 ;
3113 select count(*) INTO l_count
3114 from psb_account_position_sets
3115 where name = l_name
3116 and account_or_position_type = 'A'
3117 and global_or_local_type = 'G' ;
3118
3119 if l_count = 0 then
3120 l_valid := TRUE;
3121 EXIT;
3122 else
3123 l_set_name_seq := nvl(l_set_name_seq,0) + 1;
3124 l_name := l_curr_bg_short_name || l_set_name_seq ;
3125 end if;
3126
3127 end loop ; -- end test of set name
3128
3129 if not l_valid then
3130 -- debug('not to be updated');
3131 EXIT;
3132 end if;
3133
3134
3135 PSB_Account_Position_Set_PVT.Insert_Row
3136 (
3137 p_api_version => 1.0,
3138 p_init_msg_list => FND_API.G_TRUE,
3139 p_commit => FND_API.G_FALSE,
3140 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
3141 p_return_status => l_return_status,
3142 p_msg_count => l_msg_count,
3143 p_msg_data => l_msg_data,
3144 p_row_id => l_row_id,
3145 p_account_position_set_id => l_account_position_set_id,
3146 p_name => l_name,
3147 p_set_of_books_id => Bgsr_Rec.set_of_books_id,
3148 p_data_extract_id => Bgsr_Rec.data_extract_id,
3149 p_global_or_local_type => Bgsr_Rec.Global_or_Local_Type,
3150 p_account_or_position_type => Bgsr_Rec.account_or_position_type,
3151 p_attribute_selection_type => Bgsr_Rec.attribute_selection_type,
3152 p_business_group_id => Bgsr_Rec.business_group_id,
3153 p_last_update_date => l_last_update_date,
3154 p_last_updated_by => l_last_updated_by,
3155 p_last_update_login => l_last_update_login,
3156 p_created_by => l_created_by,
3157 p_creation_date => l_creation_date,
3158 p_use_in_budget_group_flag => Bgsr_Rec.use_in_budget_group_flag
3159 );
3160
3161 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
3162 --
3163 FND_MSG_PUB.Get( p_msg_index => 1 ,
3164 p_encoded => FND_API.G_FALSE ,
3165 p_data => l_msg_data ,
3166 p_msg_index_out => l_msg_count
3167 );
3168 --debug(l_msg_data);
3169 end if;
3170 l_set_relation_id := NULL;
3171
3172 PSB_Set_Relation_PVT.Insert_Row
3173 (
3174 p_api_version => 1.0,
3175 p_init_msg_list => FND_API.G_TRUE,
3176 p_commit => FND_API.G_FALSE,
3177 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
3178 p_return_status => l_return_status1,
3179 p_msg_count => l_msg_count1,
3180 p_msg_data => l_msg_data1,
3181 p_Row_Id => l_row_id1,
3182 p_Set_Relation_Id => l_set_relation_id,
3183 p_Account_Position_Set_Id => l_account_position_set_id,
3184 p_Allocation_Rule_Id => null,
3185 p_Budget_Group_Id => p_curr_budget_group_id,
3186 p_Budget_Workflow_Rule_Id => null,
3187 p_Constraint_Id => null,
3188 p_Default_Rule_Id => null,
3189 p_Parameter_Id => null,
3190 p_Position_Set_Group_Id => null,
3191 /* Budget Revision Rules Enhancement Start */
3192 p_rule_id => null,
3193 p_apply_balance_flag => null,
3194 /* Budget Revision Rules Enhancement End */
3195 p_Effective_Start_Date => Bgsr_Rec.effective_start_date,
3196 p_Effective_End_Date => BGsr_Rec.effective_end_date,
3197 p_last_update_date => l_last_update_date,
3198 p_last_updated_by => l_last_updated_by,
3199 p_last_update_login => l_last_update_login,
3200 p_created_by => l_created_by,
3201 p_creation_date => l_creation_date
3202 );
3203
3204 if l_return_status1 <> FND_API.G_RET_STS_SUCCESS then
3205 --
3206 FND_MSG_PUB.Get( p_msg_index => 1 ,
3207 p_encoded => FND_API.G_FALSE ,
3208 p_data => l_msg_data1 ,
3209 p_msg_index_out => l_msg_count1
3210 );
3211 --debug(l_msg_data1);
3212 end if;
3213
3214 declare
3215 l_row_id2 varchar2(100);
3216 l_return_status2 varchar2(1);
3217 l_msg_count2 number;
3218 l_msg_data2 varchar2(2000);
3219 l_line_sequence_id number;
3220
3221 begin
3222 if NOT (BG_AL_Cur%ISOPEN) then
3223 Open BG_AL_Cur;
3224 end if;
3225
3226 Loop
3227 Fetch BG_AL_Cur INTO Bgal_Rec;
3228
3229 if BG_AL_Cur%NOTFOUND then
3230 EXIT;
3231 else
3232 l_line_sequence_id := NULL;
3233
3234 PSB_Acct_Pos_Set_Line_I_PVT.Insert_Row
3235 (
3236 p_api_version => 1.0,
3237 p_init_msg_list => FND_API.G_TRUE,
3238 p_commit => FND_API.G_FALSE,
3239 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
3240 p_return_status => l_return_status2,
3241 p_msg_count => l_msg_count2,
3242 p_msg_data => l_msg_data2,
3243 p_Row_Id => l_row_id2,
3244 p_line_sequence_id => l_line_sequence_id,
3245 p_account_position_set_id => l_account_position_set_id,
3246 p_description => Bgal_Rec.description,
3247 p_business_group_id => Bgal_Rec.business_group_id,
3248 p_attribute_id => Bgal_Rec.attribute_id,
3249 p_include_or_exclude_type => Bgal_Rec.include_or_exclude_type,
3250 p_segment1_low => Bgal_Rec.segment1_low,
3251 p_segment2_low => Bgal_Rec.segment2_low,
3252 p_segment3_low => Bgal_Rec.segment3_low,
3253 p_segment4_low => Bgal_Rec.segment4_low,
3254 p_segment5_low => Bgal_Rec.segment5_low ,
3255 p_segment6_low => Bgal_Rec.segment6_low,
3256 p_segment7_low => Bgal_Rec.segment7_low,
3257 p_segment8_low => Bgal_Rec.segment8_low,
3258 p_segment9_low => Bgal_Rec.segment9_low,
3259 p_segment10_low => Bgal_Rec.segment10_low,
3260 p_segment11_low => Bgal_Rec.segment11_low,
3261 p_segment12_low => Bgal_Rec.segment12_low,
3262 p_segment13_low => Bgal_Rec.segment13_low,
3263 p_segment14_low => Bgal_Rec.segment14_low,
3264 p_segment15_low => Bgal_Rec.segment15_low,
3265 p_segment16_low => Bgal_Rec.segment16_low,
3266 p_segment17_low => Bgal_Rec.segment17_low,
3267 p_segment18_low => Bgal_Rec.segment18_low,
3268 p_segment19_low => Bgal_Rec.segment19_low,
3269 p_segment20_low => Bgal_Rec.segment20_low,
3270 p_segment21_low => Bgal_Rec.segment21_low,
3271 p_segment22_low => Bgal_Rec.segment22_low,
3272 p_segment23_low => Bgal_Rec.segment23_low,
3273 p_segment24_low => Bgal_Rec.segment24_low,
3274 p_segment25_low => Bgal_Rec.segment25_low,
3275 p_segment26_low => Bgal_Rec.segment26_low,
3276 p_segment27_low => Bgal_Rec.segment27_low,
3277 p_segment28_low => Bgal_Rec.segment28_low,
3278 p_segment29_low => Bgal_Rec.segment29_low,
3279 p_segment30_low => Bgal_Rec.segment30_low,
3280 p_segment1_high => Bgal_Rec.segment1_high,
3281 p_segment2_high => Bgal_Rec.segment2_high,
3282 p_segment3_high => Bgal_Rec.segment3_high,
3283 p_segment4_high => Bgal_Rec.segment4_high,
3284 p_segment5_high => Bgal_Rec.segment5_high,
3285 p_segment6_high => Bgal_Rec.segment6_high,
3286 p_segment7_high => Bgal_Rec.segment7_high,
3287 p_segment8_high => Bgal_Rec.segment8_high,
3288 p_segment9_high => Bgal_Rec.segment9_high,
3289 p_segment10_high => Bgal_Rec.segment10_high,
3290 p_segment11_high => Bgal_Rec.segment11_high,
3291 p_segment12_high => Bgal_Rec.segment12_high,
3292 p_segment13_high => Bgal_Rec.segment13_high,
3293 p_segment14_high => Bgal_Rec.segment14_high,
3294 p_segment15_high => Bgal_Rec.segment15_high,
3295 p_segment16_high => Bgal_Rec.segment16_high,
3296 p_segment17_high => Bgal_Rec.segment17_high,
3297 p_segment18_high => Bgal_Rec.segment18_high,
3298 p_segment19_high => Bgal_Rec.segment19_high,
3299 p_segment20_high => Bgal_Rec.segment20_high,
3300 p_segment21_high => Bgal_Rec.segment21_high,
3301 p_segment22_high => Bgal_Rec.segment22_high,
3302 p_segment23_high => Bgal_Rec.segment23_high,
3303 p_segment24_high => Bgal_Rec.segment24_high,
3304 p_segment25_high => Bgal_Rec.segment25_high,
3305 p_segment26_high => Bgal_Rec.segment26_high,
3306 p_segment27_high => Bgal_Rec.segment27_high,
3307 p_segment28_high => Bgal_Rec.segment28_high,
3308 p_segment29_high => Bgal_Rec.segment29_high,
3309 p_segment30_high => Bgal_Rec.segment30_high,
3310 p_context => Bgal_Rec.context,
3311 p_attribute1 => Bgal_Rec.attribute1,
3312 p_attribute2 => Bgal_Rec.attribute2,
3313 p_attribute3 => Bgal_Rec.attribute3,
3314 p_attribute4 => Bgal_Rec.attribute4,
3315 p_attribute5 => Bgal_Rec.attribute5,
3316 p_attribute6 => Bgal_Rec.attribute6,
3317 p_attribute7 => Bgal_Rec.attribute7,
3318 p_attribute8 => Bgal_Rec.attribute8,
3319 p_attribute9 => Bgal_Rec.attribute9,
3320 p_attribute10 => Bgal_Rec.attribute10,
3321 p_last_update_date => l_last_update_date,
3322 p_last_updated_by => l_last_updated_by,
3323 p_last_update_login => l_last_update_login,
3324 p_created_by => l_created_by,
3325 p_creation_date => l_creation_date
3326 );
3327 if l_return_status2 <> FND_API.G_RET_STS_SUCCESS then
3328 --
3329 FND_MSG_PUB.Get( p_msg_index => 1 ,
3330 p_encoded => FND_API.G_FALSE ,
3331 p_data => l_msg_data2 ,
3332 p_msg_index_out => l_msg_count2
3333 );
3334 --debug(l_msg_data2);
3335 end if;
3336
3337 end if;
3338 end loop;
3339
3340 if BG_AL_Cur%ISOPEN then
3341 Close BG_AL_Cur ;
3342 end if ;
3343 end;
3344
3345 end if;
3346 end loop;
3347
3348 if BG_SR_Cur%ISOPEN then
3349 Close BG_SR_Cur ;
3350 end if ;
3351
3352 end;
3353 -- End of API body.
3354
3355 -- Standard check of p_commit.
3356
3357 if FND_API.to_Boolean (p_commit) then
3358 commit work;
3359 end if;
3360
3361 -- Standard call to get message count and if count is 1, get message info.
3362
3363 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
3364 p_data => p_msg_data);
3365
3366 EXCEPTION
3367
3368 when FND_API.G_EXC_ERROR then
3369
3370 rollback to Copy_Budget_Group;
3371
3372 p_return_status := FND_API.G_RET_STS_ERROR;
3373
3374 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
3375 p_data => p_msg_data);
3376
3377
3378 when FND_API.G_EXC_UNEXPECTED_ERROR then
3379
3380 rollback to Copy_Budget_Group;
3381
3382 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3383
3384 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
3385 p_data => p_msg_data);
3386
3387
3388 when OTHERS then
3389
3390 rollback to Copy_Budget_Group;
3391
3392 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3393
3394 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) then
3395
3396 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
3397 l_api_name);
3398 end if;
3399
3400 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
3401 p_data => p_msg_data);
3402
3403 END Copy_Budget_Group;
3404
3405 /* ----------------------------------------------------------------------- */
3406
3407 PROCEDURE Account_Overlap_Validation
3408 ( p_api_version IN NUMBER,
3409 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3410 p_commit IN VARCHAR2 := FND_API.G_FALSE,
3411 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
3412 p_return_status OUT NOCOPY VARCHAR2,
3413 p_msg_count OUT NOCOPY NUMBER,
3414 p_msg_data OUT NOCOPY VARCHAR2,
3415 p_budget_group_id IN NUMBER
3416 ) AS
3417
3418 l_api_name CONSTANT VARCHAR2(30) := 'Account_Overlap_Validation';
3419 l_api_version CONSTANT NUMBER := 1.0;
3420
3421 -- .. budget groups for global worksheet flag
3422 -- .. ?? include only official WS??
3423
3424 CURSOR c_top_level_bg IS
3425 SELECT budget_group_id,PS_ACCOUNT_POSITION_SET_ID,
3426 NPS_ACCOUNT_POSITION_SET_ID,name,chart_of_accounts_id
3427 FROM psb_budget_groups_v
3428 WHERE root_budget_group = 'Y'
3429 AND (((p_budget_group_id is not null) and
3430 (budget_group_id = p_budget_group_id))
3431 or (p_budget_group_id is null));
3432
3433 l_flex_code NUMBER;
3434 l_error_flag VARCHAR2(1) := 'N';
3435 l_concat_segments VARCHAR2(2000);
3436 l_return_status VARCHAR2(1);
3437
3438 BEGIN
3439
3440 -- Standard Start of API savepoint
3441
3442 SAVEPOINT Account_Overlap_Validation;
3443
3444 -- Standard call to check for call compatibility.
3445
3446 if not FND_API.Compatible_API_Call (l_api_version,
3447 p_api_version,
3448 l_api_name,
3449 G_PKG_NAME)
3450 then
3451 raise FND_API.G_EXC_UNEXPECTED_ERROR;
3452 end if;
3453
3454
3455 -- Initialize message list if p_init_msg_list is set to TRUE.
3456
3457 if FND_API.to_Boolean (p_init_msg_list) then
3458 FND_MSG_PUB.initialize;
3459 end if;
3460
3461 -- Initialize API return status to success
3462
3463 p_return_status := FND_API.G_RET_STS_SUCCESS;
3464
3465 /* --- S T A R T OF A P I */
3466
3467 FOR c_top_level_bg_rec in c_top_level_bg LOOP
3468
3469 l_flex_code := c_top_level_bg_rec.chart_of_accounts_id;
3470
3471 for c_Overlap_ps_Rec in c_Overlap_ps (
3472 c_top_level_bg_rec.ps_account_position_set_id,
3473 c_top_level_bg_rec.nps_account_position_set_id) loop
3474
3475 l_error_flag := 'Y' ;
3476 message_token('BUDGET_GROUP', c_top_level_bg_rec.name);
3477 add_message('PSB', 'CCID_OVERLAP');
3478 end loop;
3479
3480 FOR c_hier_budget_group_rec in c_BudgetGroup(c_top_level_bg_rec.budget_group_id) LOOP
3481 --
3482 FOR c_AccSet_Rec in c_Accset (c_hier_budget_group_rec.budget_group_id) loop
3483 /*For Bug No : 2230514 Start*/
3484 --g_dbug := g_dbug || g_chr10 || to_char(c_hier_budget_group_rec.budget_group_id) ;
3485 /*For Bug No : 2230514 End*/
3486 FOR c_Overlap_CCID_Rec in c_Overlap_CCID
3487 (c_top_level_bg_rec.budget_group_id ,
3488 c_hier_budget_group_rec.budget_group_id ,
3489 c_AccSet_Rec.account_position_set_id ,
3490 c_AccSet_Rec.effective_start_date ,
3491 c_AccSet_Rec.effective_end_date) LOOP
3492
3493 l_error_flag := 'Y' ;
3494 --g_dbug := g_dbug || g_chr10 ||
3495 -- 'overlap on budget group : ' ||
3496 -- to_char(c_hier_budget_group_rec.budget_group_id);
3497 l_concat_segments := FND_FLEX_EXT.Get_Segs
3498 (application_short_name => 'SQLGL',
3499 key_flex_code => 'GL#',
3500 structure_number => l_flex_code,
3501 combination_id => c_Overlap_CCID_Rec.code_combination_id);
3502
3503 message_token('CCID',l_concat_segments) ;
3504 message_token('ACCOUNT_SET', c_AccSet_Rec.name);
3505 message_token('BUDGET_GROUP',c_hier_budget_group_rec.name);
3506 message_token('TOP_BUDGET_GROUP',c_top_level_bg_rec.name);
3507 add_message('PSB', 'PSB_BGH_OVERLAP_ACCOUNTS');
3508 --
3509 END LOOP;
3510
3511 END LOOP;
3512
3513
3514 -- .. validate ccid for each account sets
3515
3516
3517 END LOOP ;
3518
3519
3520 Validate_PSB_Accounts_And_GL
3521 ( p_top_budget_group_id => p_budget_group_id,
3522 p_flex_code => l_flex_code,
3523 p_return_status => l_return_status);
3524
3525 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
3526 l_error_flag := 'Y';
3527 end if;
3528
3529 END LOOP;
3530
3531 IF l_error_flag = 'Y' then
3532
3533 Output_Message_To_Table(p_budget_group_id => p_budget_group_id);
3534
3535 END IF ;
3536 --
3537 /* --- */
3538
3539
3540 -- End of API body.
3541
3542 -- Standard check of p_commit.
3543
3544 if FND_API.to_Boolean (p_commit) then
3545 commit work;
3546 end if;
3547
3548 -- Standard call to get message count and if count is 1, get message info.
3549
3550 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
3551 p_data => p_msg_data);
3552
3553 EXCEPTION
3554
3555 when FND_API.G_EXC_ERROR then
3556
3557 rollback to Account_Overlap_Validation;
3558 p_return_status := FND_API.G_RET_STS_ERROR;
3559 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
3560 p_data => p_msg_data);
3561
3562
3563 when FND_API.G_EXC_UNEXPECTED_ERROR then
3564
3565 rollback to Account_Overlap_Validation;
3566 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3567 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
3568 p_data => p_msg_data);
3569
3570
3571 when OTHERS then
3572
3573 rollback to Account_Overlap_Validation;
3574 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3575 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) then
3576 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
3577 l_api_name);
3578 end if;
3579 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
3580 p_data => p_msg_data);
3581
3582 END Account_Overlap_Validation ;
3583
3584 /* ----------------------------------------------------------------------- */
3585
3586 PROCEDURE Validate_PSB_Accounts_And_GL
3587 ( p_top_budget_group_id IN NUMBER,
3588 p_flex_code IN NUMBER,
3589 p_return_status OUT NOCOPY VARCHAR2
3590 ) AS
3591
3592 l_concat VARCHAR2(2000);
3593 l_error_flag VARCHAR2(1) := FND_API.G_FALSE;
3594 l_error_count NUMBER := 0;
3595
3596 /* start bug 4030864 */
3597 l_count NUMBER := 0;
3598 /* End bug 4030864 */
3599
3600 /*For Bug No : 2519314 Start*/
3601 TYPE l_missing_accts_tbl IS TABLE OF NUMBER(15) INDEX BY BINARY_INTEGER;
3602 l_missing_accts l_missing_accts_tbl;
3603 /*For Bug No : 2519314 End*/
3604
3605 /*For Bug No : 2255402 Start*/
3606 cursor c_Missing_Accounts IS
3607 select gcc.code_combination_id
3608 from gl_code_combinations gcc
3609 where gcc.chart_of_accounts_id = p_flex_code
3610 and gcc.detail_budgeting_allowed_flag = 'Y'
3611 and gcc.enabled_flag = 'Y'
3612 /*For Bug No : 2359795 Start*/
3613 and gcc.summary_flag = 'N'
3614 and gcc.template_id is null
3615 /*For Bug No : 2359795 End*/
3616 and not exists(select 1
3617 from PSB_BUDGET_ACCOUNTS b,
3618 PSB_SET_RELATIONS_V c,
3619 PSB_BUDGET_GROUPS d
3620 where b.code_combination_id = gcc.code_combination_id
3621 and b.account_position_set_id = c.account_position_set_id
3622 and c.budget_group_id = d.budget_group_id
3623 and (d.budget_group_id = p_top_budget_group_id or
3624 d.root_budget_group_id = p_top_budget_group_id));
3625 /*For Bug No : 2255402 End*/
3626
3627 BEGIN
3628
3629 /*For Bug No : 2519314 Start*/
3630 --following validation has been converted to BULK FETCH to reduce the fetch count
3631 open c_Missing_Accounts;
3632 loop
3633
3634 fetch c_Missing_Accounts BULK COLLECT INTO l_missing_accts
3635 LIMIT PSB_WS_ACCT1.g_limit_bulk_numrows;
3636
3637 /* Start bug 4030864 */
3638 IF l_missing_accts.count > 0 THEN
3639 l_count := l_count + 1;
3640 IF l_count = 1 THEN
3641 DELETE PSB_ERROR_MESSAGES
3642 WHERE SOURCE_PROCESS = 'VALIDATE_BUDGET_HIERARCHY'
3643 AND process_id = p_top_budget_group_id;
3644 END IF;
3645 END IF;
3646 /* End bug 4030864 */
3647
3648 for l_acct_index in 1..l_missing_accts.count loop
3649
3650 l_concat := FND_FLEX_EXT.Get_Segs
3651 (application_short_name => 'SQLGL',
3652 key_flex_code => 'GL#',
3653 structure_number => p_flex_code,
3654 combination_id => l_missing_accts(l_acct_index));
3655
3656 message_token('CCID', l_concat);
3657 add_message('PSB', 'PSB_MISSING_BUDGET_ACCOUNTS');
3658
3659 l_error_flag := FND_API.G_TRUE;
3660
3661 end loop;
3662
3663 /* Start bug no 4030864 */
3664 IF l_missing_accts.count > 0 THEN
3665 l_missing_accts.delete;
3666 PSB_MESSAGE_S.l_batch_error_flag := true;
3667 PSB_MESSAGE_S.BATCH_INSERT_ERROR('VALIDATE_BUDGET_HIERARCHY',
3668 p_top_budget_group_id);
3669
3670 PSB_MESSAGE_S.Print_Error ( p_mode => FND_FILE.OUTPUT ,
3671 p_print_header => FND_API.G_TRUE) ;
3672
3673 fnd_msg_pub.initialize;
3674 END IF;
3675 /* End bug no 4030864 */
3676
3677 exit when c_Missing_Accounts%NOTFOUND;
3678
3679 end loop;
3680 close c_Missing_Accounts;
3681 /*For Bug No : 2519314 End*/
3682
3683 if FND_API.to_Boolean(l_error_flag) then
3684 raise FND_API.G_EXC_ERROR;
3685 end if;
3686
3687
3688 EXCEPTION
3689
3690 WHEN FND_API.G_EXC_ERROR THEN
3691 if c_Missing_Accounts%ISOPEN then
3692 close c_Missing_Accounts;
3693 end if;
3694 p_return_status := FND_API.G_RET_STS_ERROR;
3695
3696 when FND_API.G_EXC_UNEXPECTED_ERROR then
3697 if c_Missing_Accounts%ISOPEN then
3698 close c_Missing_Accounts;
3699 end if;
3700 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3701
3702 WHEN OTHERS THEN
3703 if c_Missing_Accounts%ISOPEN then
3704 close c_Missing_Accounts;
3705 end if;
3706 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3707 --
3708 END Validate_PSB_Accounts_And_GL;
3709
3710
3711 /* ----------------------------------------------------------------------- */
3712
3713 PROCEDURE Validate_BGCCID_vs_PS_NPS
3714 ( p_top_budget_group_id IN NUMBER,
3715 p_flex_code IN NUMBER,
3716 p_ps_account_set_id IN NUMBER,
3717 p_nps_account_set_id IN NUMBER,
3718 p_return_status OUT NOCOPY VARCHAR2
3719 ) AS
3720
3721 l_concat VARCHAR2(2000);
3722 l_error_flag VARCHAR2(1) := FND_API.G_FALSE;
3723 l_error_count NUMBER := 0;
3724
3725 cursor c_Missing_Accounts IS
3726 SELECT a.code_combination_id ccid,b.name bg_name ,s.name set_name
3727 FROM psb_budget_accounts a,
3728 psb_set_relations_v s,
3729 psb_budget_groups_v b
3730 WHERE b.budget_group_id = s.budget_group_id
3731 AND b.budget_group_type = 'R'
3732 AND nvl(b.root_budget_group_id, b.budget_group_id) = p_top_budget_group_id
3733 AND s.account_position_set_id = a.account_position_set_id
3734 AND NOT EXISTS
3735 (SELECT z.code_combination_id from psb_budget_accounts z
3736 WHERE z.account_position_set_id in (p_ps_account_set_id, p_nps_account_set_id)
3737 AND a.code_combination_id = z.code_combination_id);
3738
3739 BEGIN
3740
3741 for c_Missing_Accounts_Rec in c_Missing_Accounts loop
3742
3743 l_concat := FND_FLEX_EXT.Get_Segs
3744 (application_short_name => 'SQLGL',
3745 key_flex_code => 'GL#',
3746 structure_number => p_flex_code,
3747 combination_id => c_Missing_Accounts_Rec.ccid);
3748
3749 message_token('CCID', l_concat);
3750 message_token('ACCSET', c_Missing_Accounts_Rec.set_name);
3751 message_token('BGID', c_Missing_Accounts_Rec.set_name);
3752 add_message('PSB', 'PSB_CCID_NOT_IN_PS_NPS');
3753
3754 l_error_flag := FND_API.G_TRUE;
3755
3756 end loop;
3757
3758 if FND_API.to_Boolean(l_error_flag) then
3759 raise FND_API.G_EXC_ERROR;
3760 end if;
3761
3762
3763 EXCEPTION
3764
3765 WHEN FND_API.G_EXC_ERROR THEN
3766 p_return_status := FND_API.G_RET_STS_ERROR;
3767
3768 when FND_API.G_EXC_UNEXPECTED_ERROR then
3769 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3770
3771 WHEN OTHERS THEN
3772 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3773 --
3774 END Validate_BGCCID_vs_PS_NPS;
3775
3776 --++ procedure is called by api needing a validation of organization for budget group hier
3777
3778 PROCEDURE Validate_Budget_Group_Org
3779 ( p_api_version IN NUMBER,
3780 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3781 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_NONE,
3782 p_return_status OUT NOCOPY VARCHAR2,
3783 p_msg_count OUT NOCOPY NUMBER,
3784 p_msg_data OUT NOCOPY VARCHAR2,
3785 p_top_budget_group_id IN NUMBER
3786 ) AS
3787
3788 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Budget_Group_Org';
3789 l_api_version CONSTANT NUMBER := 1.0;
3790 l_return_status VARCHAR2(1);
3791
3792
3793
3794 BEGIN
3795
3796 -- Standard call to check for call compatibility
3797
3798 if not FND_API.Compatible_API_Call (l_api_version,
3799 p_api_version,
3800 l_api_name,
3801 G_PKG_NAME)
3802 then
3803 raise FND_API.G_EXC_UNEXPECTED_ERROR;
3804 end if;
3805
3806
3807 Validate_BG_ORGANIZATION(p_top_budget_group_id => p_top_budget_group_id,
3808 p_return_status => l_return_status);
3809
3810 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
3811 raise FND_API.G_EXC_ERROR;
3812 END IF;
3813
3814 -- Standard call to get message count and if count is 1, get message info
3815
3816 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
3817 p_data => p_msg_data);
3818
3819
3820 -- Initialize API Return Status to Success
3821
3822 p_return_status := FND_API.G_RET_STS_SUCCESS;
3823
3824
3825 EXCEPTION
3826
3827 when FND_API.G_EXC_ERROR then
3828 p_return_status := FND_API.G_RET_STS_ERROR;
3829 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
3830 p_data => p_msg_data);
3831
3832 when FND_API.G_EXC_UNEXPECTED_ERROR then
3833 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3834 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
3835 p_data => p_msg_data);
3836
3837 when OTHERS then
3838 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3839
3840 if FND_MSG_PUB.Check_Msg_Level
3841 (p_message_level => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3842 then
3843 FND_MSG_PUB.Add_Exc_Msg
3844 (p_pkg_name => G_PKG_NAME,
3845 p_procedure_name => l_api_name);
3846 end if;
3847
3848 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
3849 p_data => p_msg_data);
3850
3851 END Validate_Budget_Group_Org;
3852
3853 PROCEDURE Validate_BG_ORGANIZATION
3854 ( p_top_budget_group_id IN NUMBER,
3855 p_return_status OUT NOCOPY VARCHAR2
3856 ) AS
3857
3858 l_error_flag VARCHAR2(1) := FND_API.G_FALSE;
3859 l_error_count NUMBER := 0;
3860
3861
3862 cursor c_Missing_org IS
3863 SELECT budget_group_id ,short_name
3864 FROM psb_budget_groups_v
3865 WHERE budget_group_type = 'R'
3866 AND nvl(root_budget_group_id, budget_group_id) =
3867 p_top_budget_group_id
3868 AND organization_id is null;
3869 cursor c_Missing_Top_org IS
3870 SELECT budget_group_id, short_name
3871 FROM psb_budget_groups_v
3872 WHERE budget_group_type = 'R'
3873 AND budget_group_id = p_top_budget_group_id
3874 AND business_group_id is null;
3875
3876
3877 cursor c_Invalid_ORG IS
3878 SELECT budget_group_id, bg.short_name
3879 FROM psb_budget_groups_v bg
3880 WHERE budget_group_type = 'R'
3881 AND root_budget_group_id = p_top_budget_group_id
3882 AND nvl(root_budget_group,'N') = 'N'
3883 and not exists
3884 (select 'exists' from per_organization_units
3885 where organization_id = bg.organization_id and
3886 business_group_id = bg.root_business_group_id);
3887
3888
3889 BEGIN
3890
3891 for c_Missing_Org_Rec in c_Missing_Org loop
3892
3893 message_token('BG_NAME', c_Missing_Org_Rec.short_name);
3894 add_message('PSB', 'PSB_BG_MISSING_ORG');
3895 l_error_flag := FND_API.G_TRUE;
3896
3897 end loop;
3898
3899 for c_Missing_Top_org_rec in c_Missing_Top_org loop
3900
3901 message_token('BGNAME', c_Missing_Top_Org_Rec.short_name);
3902 add_message('PSB', 'PSB_BG_TOPBG_MISSING_ORG');
3903
3904 l_error_flag := FND_API.G_TRUE;
3905
3906 end loop;
3907
3908 for c_Invalid_Org_Rec in c_Invalid_Org loop
3909
3910 message_token('BGNAME', c_Invalid_Org_Rec.short_name);
3911 add_message('PSB', 'PSB_BG_INVALID_ORG');
3912
3913 l_error_flag := FND_API.G_TRUE;
3914
3915 end loop;
3916
3917 if FND_API.to_Boolean(l_error_flag) then
3918 raise FND_API.G_EXC_ERROR;
3919 end if;
3920
3921
3922 EXCEPTION
3923
3924 WHEN FND_API.G_EXC_ERROR THEN
3925 p_return_status := FND_API.G_RET_STS_ERROR;
3926
3927 when FND_API.G_EXC_UNEXPECTED_ERROR then
3928 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3929
3930 WHEN OTHERS THEN
3931 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3932 --
3933 END Validate_BG_ORGANIZATION ;
3934
3935 /* ----------------------------------------------------------------------- */
3936
3937 -- Add Token and Value to the Message Token array
3938
3939 PROCEDURE message_token(tokname IN VARCHAR2,
3940 tokval IN VARCHAR2) AS
3941
3942 BEGIN
3943
3944 if no_msg_tokens is null then
3945 no_msg_tokens := 1;
3946 else
3947 no_msg_tokens := no_msg_tokens + 1;
3948 end if;
3949
3950 msg_tok_names(no_msg_tokens) := tokname;
3951 msg_tok_val(no_msg_tokens) := tokval;
3952
3953 END message_token;
3954
3955 /* ----------------------------------------------------------------------- */
3956
3957 -- Define a Message Token with a Value and set the Message Name
3958
3959 -- Calls FND_MESSAGE server package to set the Message Stack. This message is
3960 -- retrieved by the calling program.
3961
3962 PROCEDURE add_message(appname IN VARCHAR2,
3963 msgname IN VARCHAR2) AS
3964
3965 i BINARY_INTEGER;
3966
3967 BEGIN
3968
3969 if ((appname is not null) and
3970 (msgname is not null)) then
3971
3972 FND_MESSAGE.SET_NAME(appname, msgname);
3973
3974 if no_msg_tokens is not null then
3975
3976 for i in 1..no_msg_tokens loop
3977 FND_MESSAGE.SET_TOKEN(msg_tok_names(i), msg_tok_val(i));
3978 end loop;
3979
3980 end if;
3981
3982 FND_MSG_PUB.Add;
3983
3984 end if;
3985
3986 -- Clear Message Token stack
3987
3988 no_msg_tokens := 0;
3989
3990 END add_message;
3991
3992 /* ----------------------------------------------------------------------- */
3993
3994 PROCEDURE Output_Message_To_Table(p_budget_group_id IN NUMBER) AS
3995
3996 l_reqid NUMBER;
3997 l_rep_req_id NUMBER;
3998 l_userid NUMBER;
3999 l_msg_count NUMBER;
4000 l_msg_buf varchar2(1000);
4001
4002 /* Start bug no 4030864 */
4003 l_max_sequence_number NUMBER := 0;
4004 /* End bug no 4030864 */
4005
4006
4007 BEGIN
4008 /* Start bug no 4030864 */
4009 IF PSB_MESSAGE_S.l_batch_error_flag = FALSE THEN
4010 delete from PSB_ERROR_MESSAGES
4011 where source_process = 'VALIDATE_BUDGET_HIERARCHY'
4012 and process_id = p_budget_group_id;
4013 END IF;
4014 /* End bug no 4030864 */
4015
4016
4017 l_reqid := FND_GLOBAL.CONC_REQUEST_ID;
4018 l_userid := FND_GLOBAL.USER_ID;
4019
4020 FND_MSG_PUB.Count_And_Get ( p_count => l_msg_count,
4021 p_data => l_msg_buf );
4022
4023 /* Start bug no 4030864 */
4024 -- performance bug
4025 IF PSB_MESSAGE_S.l_batch_error_flag = FALSE THEN
4026 PSB_MESSAGE_S.Insert_Error ( p_source_process => 'VALIDATE_BUDGET_HIERARCHY',
4027 p_process_id => p_budget_group_id,
4028 p_msg_count => l_msg_count,
4029 p_msg_data => l_msg_buf,
4030 p_desc_sequence => FND_API.G_TRUE) ;
4031 ELSE
4032 PSB_MESSAGE_S.BATCH_INSERT_ERROR ( p_source_process => 'VALIDATE_BUDGET_HIERARCHY',
4033 p_process_id => p_budget_group_id);
4034
4035 -- end of session
4036 FOR l_max_seq_rec IN (
4037 SELECT max(sequence_number) + 1 max_seq
4038 FROM psb_error_messages
4039 WHERE process_id = p_budget_group_id
4040 AND source_process = 'VALIDATE_BUDGET_HIERARCHY')
4041 LOOP
4042 l_max_sequence_number := l_max_seq_rec.max_seq;
4043 END LOOP;
4044
4045 IF l_max_sequence_number > 0 THEN
4046 UPDATE psb_error_messages
4047 SET sequence_number = (l_max_sequence_number - sequence_number)
4048 WHERE process_id = p_budget_group_id
4049 AND source_process = 'VALIDATE_BUDGET_HIERARCHY';
4050 END IF;
4051 END IF;
4052 /* End bug no 4030864 */
4053
4054 -- submit concurrent request for error report
4055 l_rep_req_id := Fnd_Request.Submit_Request
4056 (application => 'PSB' ,
4057 program => 'PSBRPERR' ,
4058 description => 'Validate Budget Group Error Report',
4059 start_time => NULL ,
4060 sub_request => FALSE ,
4061 argument1 => 'Validate_Budget_Hierarchy',
4062 argument2 => p_budget_group_id,
4063 argument3 => l_reqid
4064 );
4065 --
4066 if l_rep_req_id = 0 then
4067 --
4068 fnd_message.set_name('PSB', 'PSB_FAIL_TO_SUBMIT_REQUEST');
4069 --
4070 end if;
4071 --
4072 -- initialize error message stack --
4073 FND_MSG_PUB.initialize;
4074
4075 END Output_Message_To_Table;
4076
4077
4078 /* ----------------------------------------------------------------------- */
4079 -- C O N C U R R E N T R E Q U E S T S
4080 /* ----------------------------------------------------------------------- */
4081
4082
4083 /*===========================================================================+
4084 | PROCEDURE Val Budget Group Hierarchy CP |
4085 +===========================================================================*/
4086 --
4087 -- This is the execution file for the concurrent program
4088 -- Val Budget Group Hierarchy CP through Standard Report Submissions.
4089 --
4090 PROCEDURE Val_Budget_Group_Hierarchy_CP
4091 (
4092 errbuf OUT NOCOPY VARCHAR2,
4093 retcode OUT NOCOPY VARCHAR2,
4094 --
4095 p_budget_group_id IN NUMBER ,
4096 p_force_freeze IN VARCHAR2
4097 )
4098 IS
4099 --
4100 l_api_name CONSTANT VARCHAR2(30) := 'Val_Budget_Group_Hierarchy_CP';
4101 l_api_version CONSTANT NUMBER := 1.0 ;
4102 --
4103 l_error_api_name VARCHAR2(2000);
4104 l_return_status VARCHAR2(1) ;
4105 l_msg_count NUMBER ;
4106 l_msg_data VARCHAR2(2000) ;
4107 l_msg_index_out NUMBER;
4108 --
4109 BEGIN
4110 --
4111
4112 PSB_BUDGET_GROUPS_PVT.Val_Budget_Group_Hierarchy
4113 (p_api_version => 1.0,
4114 p_init_msg_list => FND_API.G_TRUE,
4115 p_commit => FND_API.G_TRUE,
4116 p_return_status => l_return_status,
4117 p_msg_count => l_msg_count,
4118 p_msg_data => l_msg_data,
4119 p_budget_group_id => p_budget_group_id,
4120 p_force_freeze => p_force_freeze);
4121
4122 if l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4123 PSB_MESSAGE_S.Print_Error ( p_mode => FND_FILE.OUTPUT ,
4124 p_print_header => FND_API.G_TRUE) ;
4125 raise FND_API.G_EXC_ERROR;
4126 elsif (l_msg_count > 0) THEN
4127 -- informational message
4128 PSB_MESSAGE_S.Print_Error ( p_mode => FND_FILE.OUTPUT ,
4129 p_print_header => FND_API.G_FALSE) ;
4130
4131 /* Start Bug No. 2322856 */
4132 -- PSB_MESSAGE_S.Print_Success;
4133 /* End Bug No. 2322856 */
4134 retcode := 0 ;
4135
4136 else
4137 -- a success
4138 /* Start Bug No. 2322856 */
4139 -- PSB_MESSAGE_S.Print_Success;
4140 /* End Bug No. 2322856 */
4141 retcode := 0 ;
4142 --debug('The program completed successfully');
4143 end if;
4144
4145 --
4146 COMMIT WORK;
4147 --
4148 EXCEPTION
4149 --
4150
4151 WHEN FND_API.G_EXC_ERROR THEN
4152 --
4153 PSB_MESSAGE_S.Print_Error ( p_mode => FND_FILE.LOG ,
4154 p_print_header => FND_API.G_TRUE ) ;
4155 retcode := 2 ;
4156 --
4157 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4158 --
4159 PSB_MESSAGE_S.Print_Error ( p_mode => FND_FILE.LOG ,
4160 p_print_header => FND_API.G_TRUE ) ;
4161 retcode := 2 ;
4162 --
4163
4164 WHEN OTHERS THEN
4165 --
4166 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
4167 --
4168 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,
4169 l_api_name ) ;
4170 END IF ;
4171 --
4172 PSB_MESSAGE_S.Print_Error ( p_mode => FND_FILE.LOG ,
4173 p_print_header => FND_API.G_TRUE ) ;
4174 retcode := 2 ;
4175 --
4176 END Val_Budget_Group_Hierarchy_CP ;
4177
4178
4179 /* ----------------------------------------------------------------------- */
4180
4181
4182 /*===========================================================================+
4183 | PROCEDURE Delete Row CP |
4184 +===========================================================================*/
4185 --
4186 -- This is the execution file for the concurrent program Create Budget Journal
4187 -- through Standard Report Submissions.
4188 --
4189 PROCEDURE DELETE_ROW_CP
4190 (
4191 errbuf OUT NOCOPY VARCHAR2,
4192 retcode OUT NOCOPY VARCHAR2,
4193 --
4194 p_budget_group_id IN NUMBER
4195 )
4196 IS
4197 --
4198 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_ROW_CP';
4199 l_api_version CONSTANT NUMBER := 1.0 ;
4200 --
4201 l_error_api_name VARCHAR2(2000);
4202 l_return_status VARCHAR2(1) ;
4203 l_msg_count NUMBER ;
4204 l_msg_data VARCHAR2(2000) ;
4205 l_msg_index_out NUMBER;
4206 l_delete VARCHAR(20) ;
4207 --
4208 BEGIN
4209 --
4210
4211 PSB_BUDGET_GROUPS_PVT.DELETE_ROW
4212 (p_api_version => 1.0,
4213 p_init_msg_list => FND_API.G_TRUE,
4214 p_return_status => l_return_status,
4215 p_msg_count => l_msg_count,
4216 p_msg_data => l_msg_data,
4217 p_budget_group_id => p_budget_group_id,
4218 p_delete => l_delete);
4219
4220
4221 if l_delete <> 'DELETE' THEN
4222 FND_FILE.put_line(FND_FILE.LOG,'The Budget Group Cannot Be Deleted');
4223 raise FND_API.G_EXC_ERROR;
4224 end if;
4225
4226
4227 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4228 RAISE FND_API.G_EXC_ERROR;
4229 END IF;
4230 --
4231 /* Start Bug No. 2322856 */
4232 -- PSB_MESSAGE_S.Print_Success;
4233 /* End Bug No. 2322856 */
4234 retcode := 0 ;
4235 --
4236 COMMIT WORK;
4237 --
4238 EXCEPTION
4239 --
4240
4241 WHEN FND_API.G_EXC_ERROR THEN
4242 --
4243 PSB_MESSAGE_S.Print_Error ( p_mode => FND_FILE.LOG ,
4244 p_print_header => FND_API.G_TRUE ) ;
4245 retcode := 2 ;
4246 --
4247 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4248 --
4249 PSB_MESSAGE_S.Print_Error ( p_mode => FND_FILE.LOG ,
4250 p_print_header => FND_API.G_TRUE ) ;
4251 retcode := 2 ;
4252 --
4253 WHEN OTHERS THEN
4254 --
4255 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
4256 --
4257 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,
4258 l_api_name ) ;
4259 END IF ;
4260 --
4261 PSB_MESSAGE_S.Print_Error ( p_mode => FND_FILE.LOG ,
4262 p_print_header => FND_API.G_TRUE ) ;
4263 retcode := 2 ;
4264 --
4265 END Delete_Row_CP ;
4266
4267 /*===========================================================================+
4268 | PROCEDURE Account Overlap Validation CP |
4269 +===========================================================================*/
4270 --
4271 -- This is the execution file for the concurrent program
4272 -- Account Overlap Validation CP through Standard Report Submissions.
4273 --
4274 PROCEDURE Account_Overlap_Validation_CP
4275 (
4276 errbuf OUT NOCOPY VARCHAR2,
4277 retcode OUT NOCOPY VARCHAR2,
4278 --
4279 p_budget_group_id IN NUMBER
4280 )
4281 IS
4282 --
4283 l_api_name CONSTANT VARCHAR2(30) := 'Account_Overlap_Validation_CP';
4284 l_api_version CONSTANT NUMBER := 1.0 ;
4285 --
4286 l_error_api_name VARCHAR2(2000);
4287 l_return_status VARCHAR2(1) ;
4288 l_msg_count NUMBER ;
4289 l_msg_data VARCHAR2(2000) ;
4290 l_msg_index_out NUMBER;
4291 --
4292 BEGIN
4293 --
4294 PSB_BUDGET_GROUPS_PVT.Account_Overlap_Validation
4295 (p_api_version => 1.0,
4296 p_init_msg_list => FND_API.G_TRUE,
4297 p_return_status => l_return_status,
4298 p_msg_count => l_msg_count,
4299 p_msg_data => l_msg_data,
4300 p_budget_group_id => p_budget_group_id);
4301
4302 if l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4303 raise FND_API.G_EXC_ERROR;
4304 elsif (l_msg_count > 0) THEN
4305 -- informational message --
4306 PSB_MESSAGE_S.Print_Error ( p_mode => FND_FILE.OUTPUT ,
4307 p_print_header => FND_API.G_FALSE) ;
4308
4309 /* Start Bug No. 2322856 */
4310 -- PSB_MESSAGE_S.Print_Success;
4311 /* End Bug No. 2322856 */
4312 retcode := 0 ;
4313 else
4314 -- successful msg --
4315 /* Start Bug No. 2322856 */
4316 -- PSB_MESSAGE_S.Print_Success;
4317 /* End Bug No. 2322856 */
4318 retcode := 0 ;
4319 end if;
4320
4321 --
4322 COMMIT WORK;
4323 --
4324 EXCEPTION
4325 --
4326
4327 WHEN FND_API.G_EXC_ERROR THEN
4328 --
4329 PSB_MESSAGE_S.Print_Error ( p_mode => FND_FILE.LOG ,
4330 p_print_header => FND_API.G_TRUE ) ;
4331 retcode := 2 ;
4332 --
4333 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4334 --
4335 PSB_MESSAGE_S.Print_Error ( p_mode => FND_FILE.LOG ,
4336 p_print_header => FND_API.G_TRUE ) ;
4337 retcode := 2 ;
4338 --
4339 WHEN OTHERS THEN
4340 --
4341 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
4342 --
4343 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,
4344 l_api_name ) ;
4345 END IF ;
4346 --
4347 PSB_MESSAGE_S.Print_Error ( p_mode => FND_FILE.LOG ,
4348 p_print_header => FND_API.G_TRUE ) ;
4349 retcode := 2 ;
4350 --
4351 END Account_Overlap_Validation_CP ;
4352
4353
4354 /* ----------------------------------------------------------------------- */
4355
4356 -- Get Debug Information
4357
4358 -- This Module is used to retrieve Debug Information for this Package. It
4359 -- prints Debug Information when run as a Batch Process from SQL*Plus. For
4360 -- the Debug Information to be printed on the Screen, the SQL*Plus parameter
4361 -- 'Serveroutput' should be set to 'ON'
4362
4363 /*For Bug No : 2230514 Start*/
4364 /*
4365 FUNCTION get_debug RETURN VARCHAR2 AS
4366
4367 BEGIN
4368
4369 return(g_dbug);
4370
4371 END get_debug;
4372 */
4373 /*For Bug No : 2230514 End*/
4374 /* ----------------------------------------------------------------------- */
4375
4376
4377 END PSB_BUDGET_GROUPS_PVT;