[Home] [Help]
PACKAGE BODY: APPS.PSB_WORKSHEET
Source
1 PACKAGE BODY PSB_WORKSHEET AS
2 /* $Header: PSBVWCMB.pls 120.15 2005/11/16 11:20:28 viraghun ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):= 'PSB_WORKSHEET';
5
6 -- TokNameArray contains names of all tokens
7
8 TYPE TokNameArray IS TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER;
9
10 -- TokValArray contains values for all tokens
11
12 TYPE TokValArray IS TABLE OF VARCHAR2(1000) INDEX BY BINARY_INTEGER;
13
14 -- number of Message Tokens
15
16 no_msg_tokens NUMBER := 0;
17
18 -- Message Token Name
19
20 msg_tok_names TokNameArray;
21
22 -- Message Token Value
23
24 msg_tok_val TokValArray;
25
26 g_set_of_books_id NUMBER;
27 g_set_of_books_name VARCHAR2(30);
28 g_business_group_id NUMBER;
29 g_flex_code NUMBER;
30 g_currency_code VARCHAR2(15);
31 g_budgetary_control VARCHAR2(1);
32
33 g_budget_group_name VARCHAR2(80);
34
35 g_budget_group_id NUMBER;
36 /* Bug 3543845 start */
37 -- Commented out then made the following variable public
38 --g_parameter_set_id NUMBER;
39 --g_num_years_to_allocate NUMBER;
40 --g_budget_by_position VARCHAR2(1);
41 --g_root_budget_group_id NUMBER;
42 /* Bug 3543845 End */
43
44 g_constraint_set_id NUMBER;
45 g_data_extract_id NUMBER;
46 g_data_extract_name VARCHAR2(30);
47 g_global_worksheet VARCHAR2(1);
48 g_global_worksheet_option VARCHAR2(1);
49 g_budget_version_id NUMBER;
50 g_gl_budget_set_id NUMBER;
51 -- g_gl_cutoff_period DATE;
52 /* made the above global variable public.declared it in the spec
53 Bug 3469514 */
54
55 /* Bug 3458191 start */
56 -- Moving the following variables to public
57 -- g_budget_calendar_id NUMBER;
58 -- g_allocrule_set_id NUMBER;
59 -- g_global_worksheet_id NUMBER;
60 -- g_local_copy_flag VARCHAR2(1);
61 -- g_rounding_factor NUMBER;
62 -- g_stage_set_id NUMBER;
63 -- g_current_stage_seq NUMBER;
64 -- g_flex_mapping_set_id NUMBER;
65 /* Bug 3458191 end */
66
67 g_use_revised_element_rates VARCHAR2(1);
68 g_num_proposed_years NUMBER;
69 g_start_stage_seq NUMBER;
70 g_incl_stat_bal VARCHAR2(1);
71 g_incl_trans_bal VARCHAR2(1);
72 g_incl_adj_period VARCHAR2(1);
73 g_create_non_pos_line_items VARCHAR2(1);
74 g_apply_element_parameters VARCHAR2(1);
75 g_apply_position_parameters VARCHAR2(1);
76 g_create_positions VARCHAR2(1);
77 g_create_summary_totals VARCHAR2(1);
78 g_apply_constraints VARCHAR2(1);
79 g_include_gl_commit_balance VARCHAR2(1);
80 g_include_gl_oblig_balance VARCHAR2(1);
81 g_include_gl_other_balance VARCHAR2(1);
82 g_include_cbc_commit_balance VARCHAR2(1);
83 g_include_cbc_oblig_balance VARCHAR2(1);
84 g_include_cbc_budget_balance VARCHAR2(1);
85
86 /* bug no 4725091 */
87 g_include_gl_forward_balance VARCHAR2(1);
88
89
90 g_service_package_id NUMBER;
91
92 g_cs_name VARCHAR2(30);
93 g_cs_threshold NUMBER;
94
95 g_sp1_status VARCHAR2(1);
96 g_sp2_status VARCHAR2(1);
97 g_sp3_status VARCHAR2(1);
98 g_sp4_status VARCHAR2(1);
99
100 g_chr10 CONSTANT VARCHAR2(1) := FND_GLOBAL.Newline;
101
102 g_dbug VARCHAR2(1000);
103
104 /* ----------------------------------------------------------------------- */
105 /* */
106 /* Private Function Definition */
107 /* */
108 /* ----------------------------------------------------------------------- */
109
110 PROCEDURE Initialize
111 ( p_worksheet_id IN NUMBER,
112 p_return_status OUT NOCOPY VARCHAR2);
113
114 PROCEDURE Cache_Worksheet_Variables
115 ( p_worksheet_id IN NUMBER,
116 p_return_status OUT NOCOPY VARCHAR2);
117
118 PROCEDURE Check_DataExt_Completion
119 ( p_data_extract_id IN NUMBER,
120 p_return_status OUT NOCOPY VARCHAR2);
121
122 PROCEDURE message_token
123 ( tokname IN VARCHAR2,
124 tokval IN VARCHAR2);
125
126 PROCEDURE add_message
127 (appname IN VARCHAR2,
128 msgname IN VARCHAR2);
129
130 /* ----------------------------------------------------------------------- */
131
132 PROCEDURE Check_Reentrant_Status
133 ( p_return_status OUT NOCOPY VARCHAR2,
134 p_worksheet_id IN NUMBER,
135 p_parameter_set_id IN NUMBER,
136 p_constraint_set_id IN NUMBER,
137 p_allocrule_set_id IN NUMBER,
138 p_budget_calendar_id IN NUMBER,
139 p_budget_group_id IN NUMBER,
140 p_data_extract_id IN NUMBER,
141 p_gl_budget_set_id IN NUMBER
142 ) IS
143
144 l_return_status VARCHAR2(1);
145
146 l_count_glset1 NUMBER;
147 l_lud_glset1 DATE;
148 l_count_glset2 NUMBER;
149 l_lud_glset2 DATE;
150
151 l_count_ps1 NUMBER;
152 l_lud_ps1 DATE;
153 l_count_ps2 NUMBER;
154 l_lud_ps2 DATE;
155
156 l_count_cs1 NUMBER;
157 l_lud_cs1 DATE;
158 l_count_cs2 NUMBER;
159 l_lud_cs2 DATE;
160
161 l_count_ar1 NUMBER;
162 l_lud_ar1 DATE;
163 l_count_ar2 NUMBER;
164 l_lud_ar2 DATE;
165
166 l_count_bc NUMBER;
167 l_lud_bc DATE;
168
169 l_count_bg1 NUMBER;
170 l_lud_bg1 DATE;
171 l_count_bg2 NUMBER;
172 l_lud_bg2 DATE;
173
174 l_lud_de DATE;
175
176 l_count_assign NUMBER;
177 l_lud_assign DATE;
178
179 l_count_rates NUMBER;
180 l_lud_rates DATE;
181
182 l_count_dist NUMBER;
183 l_lud_dist DATE;
184
185 l_rec_found VARCHAR2(1) := FND_API.G_FALSE;
186 l_restart VARCHAR2(1);
187
188 cursor c_Reent_Status is
189 select to_number(attribute1) count_ps1,
190 to_date(attribute2, 'YYYY/MM/DD HH24:MI:SS') lud_ps1,
191 to_number(attribute3) count_ps2,
192 to_date(attribute4, 'YYYY/MM/DD HH24:MI:SS') lud_ps2,
193 to_number(attribute5) count_cs1,
194 to_date(attribute6, 'YYYY/MM/DD HH24:MI:SS') lud_cs1,
195 to_number(attribute7) count_cs2,
196 to_date(attribute8, 'YYYY/MM/DD HH24:MI:SS') lud_cs2,
197 to_number(attribute9) count_ar1,
198 to_date(attribute10, 'YYYY/MM/DD HH24:MI:SS') lud_ar1,
199 to_number(attribute11) count_ar2,
200 to_date(attribute12, 'YYYY/MM/DD HH24:MI:SS') lud_ar2,
201 to_number(attribute13) count_bc,
202 to_date(attribute14, 'YYYY/MM/DD HH24:MI:SS') lud_bc,
203 to_number(attribute15) count_bg1,
204 to_date(attribute16, 'YYYY/MM/DD HH24:MI:SS') lud_bg1,
205 to_number(attribute17) count_bg2,
206 to_date(attribute18, 'YYYY/MM/DD HH24:MI:SS') lud_bg2,
207 to_date(attribute19, 'YYYY/MM/DD HH24:MI:SS') lud_de,
208 to_number(attribute20) count_assign,
209 to_date(attribute21, 'YYYY/MM/DD HH24:MI:SS') lud_assign,
210 to_number(attribute22) count_rates,
211 to_date(attribute23, 'YYYY/MM/DD HH24:MI:SS') lud_rates,
212 to_number(attribute24) count_dist,
213 to_date(attribute25, 'YYYY/MM/DD HH24:MI:SS') lud_dist,
214 to_number(attribute26) count_glset1,
215 to_date(attribute27, 'YYYY/MM/DD HH24:MI:SS') lud_glset1,
216 to_number(attribute28) count_glset2,
217 to_date(attribute29, 'YYYY/MM/DD HH24:MI:SS') lud_glset2,
218 TO_DATE(attribute30, 'YYYY/MM/DD HH24:MI:SS') gcd_ws,
219 sp1_status,
220 sp2_status,
221 sp3_status,
222 sp4_status
223 from PSB_REENTRANT_PROCESS_STATUS
224 where process_type = 'WORKSHEET_CREATION'
225 and process_uid = p_worksheet_id;
226
227 cursor c_glset1 is
228 select Count(*) count_glset1,
229 Max(last_update_date) lud_glset1
230 from PSB_GL_BUDGETS
231 where gl_budget_set_id = p_gl_budget_set_id;
232
233 cursor c_glset2 is
234 select Count(*) count_glset2,
235 Max(last_update_date) lud_glset2
236 from PSB_SET_RELATIONS
237 where gl_budget_id in
238 (select gl_budget_id
239 from PSB_GL_BUDGETS
240 where gl_budget_set_id = p_gl_budget_set_id);
241
242 cursor c_ps1 is
243 select Count(*) count_ps1,
244 Max(last_update_date) lud_ps1
245 from PSB_PARAMETER_ASSIGNMENTS_V
246 where parameter_set_id = p_parameter_set_id;
247
248 cursor c_ps2 is
249 select Count(*) count_ps2,
250 Max(last_update_date) lud_ps2
251 from PSB_SET_RELATIONS
252 where parameter_id in
253 (select parameter_id
254 from PSB_PARAMETER_ASSIGNMENTS_V
255 where parameter_set_id = p_parameter_set_id);
256
257 cursor c_cs1 is
258 select Count(*) count_cs1,
259 Max(last_update_date) lud_cs1
260 from PSB_CONSTRAINT_ASSIGNMENTS_V
261 where constraint_set_id = p_constraint_set_id;
262
263 cursor c_cs2 is
264 select Count(*) count_cs2,
265 Max(last_update_date) lud_cs2
266 from PSB_SET_RELATIONS
267 where constraint_id in
268 (select constraint_id
269 from PSB_CONSTRAINT_ASSIGNMENTS_V
270 where constraint_set_id = p_constraint_set_id);
271
272 cursor c_ar1 is
273 select Count(*) count_ar1,
274 Max(last_update_date) lud_ar1
275 from PSB_ALLOCRULE_ASSIGNMENTS_V
276 where allocrule_set_id = p_allocrule_set_id;
277
278 cursor c_ar2 is
279 select Count(*) count_ar2,
280 Max(last_update_date) lud_ar2
281 from PSB_SET_RELATIONS
282 where allocation_rule_id in
283 (select allocrule_id
284 from PSB_ALLOCRULE_ASSIGNMENTS_V
285 where allocrule_set_id = p_allocrule_set_id);
286
287 cursor c_bc is
288 select Count(*) count_bc,
289 Max(last_update_date) lud_bc
290 from PSB_BUDGET_PERIODS
291 where budget_period_type = 'Y'
292 and budget_calendar_id = p_budget_calendar_id;
293
294 cursor c_bg1 is
295 select Count(*) count_bg1,
296 Max(last_update_date) lud_bg1
297 from PSB_BUDGET_GROUPS
298 where budget_group_type = 'R'
299 and effective_start_date <= PSB_WS_ACCT1.g_startdate_pp
300 and (effective_end_date is null or effective_end_date >= PSB_WS_ACCT1.g_enddate_cy)
301 start with budget_group_id = p_budget_group_id
302 connect by prior budget_group_id = parent_budget_group_id;
303
304 cursor c_bg2 is
305 select Count(*) count_bg2,
306 Max(last_update_date) lud_bg2
307 from PSB_SET_RELATIONS
308 where budget_group_id in
309 (select budget_group_id
310 from PSB_BUDGET_GROUPS
311 where budget_group_type = 'R'
312 and effective_start_date <= PSB_WS_ACCT1.g_startdate_pp
313 and (effective_end_date is null or effective_end_date >= PSB_WS_ACCT1.g_enddate_cy)
314 start with budget_group_id = p_budget_group_id
315 connect by prior budget_group_id = parent_budget_group_id);
316
317 cursor c_de is
318 select last_extract_date lud_de
319 from PSB_DATA_EXTRACTS
320 where data_extract_id = p_data_extract_id;
321
322 cursor c_assign is
323 select count(*) count_assign,
324 Max(last_update_date) lud_assign
325 from PSB_POSITION_ASSIGNMENTS
326 where data_extract_id = p_data_extract_id;
327
328 cursor c_rates is
329 select count(*) count_rates,
330 Max(last_update_date) lud_rates
331 from PSB_PAY_ELEMENT_RATES
332 where pay_element_id in
333 (select pay_element_id
334 from PSB_PAY_ELEMENTS
335 where data_extract_id = p_data_extract_id);
336
337 cursor c_dist is
338 select count(*) count_dist,
339 Max(last_update_date) lud_dist
340 from PSB_POSITION_PAY_DISTRIBUTIONS
341 where data_extract_id = p_data_extract_id;
342
343 BEGIN
344
345 if p_gl_budget_set_id is not null then
346 begin
347
348 for c_glset1_rec in c_glset1 loop
349 l_count_glset1 := c_glset1_rec.count_glset1;
350 l_lud_glset1 := c_glset1_rec.lud_glset1;
351 end loop;
352
353 for c_glset2_rec in c_glset2 loop
354 l_count_glset2 := c_glset2_rec.count_glset2;
355 l_lud_glset2 := c_glset2_rec.lud_glset2;
356 end loop;
357
358 end;
359 end if;
360
361 if p_parameter_set_id is not null then
362 begin
363
364 for c_ps1_rec in c_ps1 loop
365 l_count_ps1 := c_ps1_rec.count_ps1;
366 l_lud_ps1 := c_ps1_rec.lud_ps1;
367 end loop;
368
369 for c_ps2_rec in c_ps2 loop
370 l_count_ps2 := c_ps2_rec.count_ps2;
371 l_lud_ps2 := c_ps2_rec.lud_ps2;
372 end loop;
373
374 end;
375 end if;
376
377 if p_constraint_set_id is not null then
378 begin
379
380 for c_cs1_rec in c_cs1 loop
381 l_count_cs1 := c_cs1_rec.count_cs1;
382 l_lud_cs1 := c_cs1_rec.lud_cs1;
383 end loop;
384
385 for c_cs2_rec in c_cs2 loop
386 l_count_cs2 := c_cs2_rec.count_cs2;
387 l_lud_cs2 := c_cs2_rec.lud_cs2;
388 end loop;
389
390 end;
391 end if;
392
393 if p_allocrule_set_id is not null then
394 begin
395
396 for c_ar1_rec in c_ar1 loop
397 l_count_ar1 := c_ar1_rec.count_ar1;
398 l_lud_ar1 := c_ar1_rec.lud_ar1;
399 end loop;
400
401 for c_ar2_rec in c_ar2 loop
402 l_count_ar2 := c_ar2_rec.count_ar2;
403 l_lud_ar2 := c_ar2_rec.lud_ar2;
404 end loop;
405
406 end;
407 end if;
408
409 if p_data_extract_id is not null then
410 begin
411
412 for c_de_rec in c_de loop
413 l_lud_de := c_de_rec.lud_de;
414 end loop;
415
416 for c_assign_rec in c_assign loop
417 l_count_assign := c_assign_rec.count_assign;
418 l_lud_assign := c_assign_rec.lud_assign;
419 end loop;
420
421 for c_rates_rec in c_rates loop
422 l_count_rates := c_rates_rec.count_rates;
423 l_lud_rates := c_rates_rec.lud_rates;
424 end loop;
425
426 for c_dist_rec in c_dist loop
427 l_count_dist := c_dist_rec.count_dist;
428 l_lud_dist := c_dist_rec.lud_dist;
429 end loop;
430
431 end;
432 end if;
433
434 for c_bc_rec in c_bc loop
435 l_count_bc := c_bc_rec.count_bc;
436 l_lud_bc := c_bc_rec.lud_bc;
437 end loop;
438
439 for c_bg1_rec in c_bg1 loop
440 l_count_bg1 := c_bg1_rec.count_bg1;
441 l_lud_bg1 := c_bg1_rec.lud_bg1;
442 end loop;
443
444 for c_bg2_rec in c_bg2 loop
445 l_count_bg2 := c_bg2_rec.count_bg2;
446 l_lud_bg2 := c_bg2_rec.lud_bg2;
447 end loop;
448
449 for c_Reent_Status_Rec in c_Reent_Status loop
450
451 l_rec_found := FND_API.G_TRUE;
452
453 if ((nvl(c_Reent_Status_Rec.count_ps1, FND_API.G_MISS_NUM) <> nvl(l_count_ps1, FND_API.G_MISS_NUM)) or
454 (nvl(c_Reent_Status_Rec.lud_ps1, FND_API.G_MISS_DATE) <> nvl(l_lud_ps1, FND_API.G_MISS_DATE)) or
455 (nvl(c_Reent_Status_Rec.count_ps2, FND_API.G_MISS_NUM) <> nvl(l_count_ps2, FND_API.G_MISS_NUM)) or
456 (nvl(c_Reent_Status_Rec.lud_ps2, FND_API.G_MISS_DATE) <> nvl(l_lud_ps2, FND_API.G_MISS_DATE)) or
457 (nvl(c_Reent_Status_Rec.count_glset1, FND_API.G_MISS_NUM) <> nvl(l_count_glset1, FND_API.G_MISS_NUM)) or
458 (nvl(c_Reent_Status_Rec.lud_glset1, FND_API.G_MISS_DATE) <> nvl(l_lud_glset1, FND_API.G_MISS_DATE)) or
459 (nvl(c_Reent_Status_Rec.count_glset2, FND_API.G_MISS_NUM) <> nvl(l_count_glset2, FND_API.G_MISS_NUM)) or
460 (nvl(c_Reent_Status_Rec.lud_glset2, FND_API.G_MISS_DATE) <> nvl(l_lud_glset2, FND_API.G_MISS_DATE)) or
461 (nvl(c_Reent_Status_Rec.count_ar1, FND_API.G_MISS_NUM) <> nvl(l_count_ar1, FND_API.G_MISS_NUM)) or
462 (nvl(c_Reent_Status_Rec.lud_ar1, FND_API.G_MISS_DATE) <> nvl(l_lud_ar1, FND_API.G_MISS_DATE)) or
463 (nvl(c_Reent_Status_Rec.count_ar2, FND_API.G_MISS_NUM) <> nvl(l_count_ar2, FND_API.G_MISS_NUM)) or
464 (nvl(c_Reent_Status_Rec.lud_ar2, FND_API.G_MISS_DATE) <> nvl(l_lud_ar2, FND_API.G_MISS_DATE)) or
465 (nvl(c_Reent_Status_Rec.count_bc, FND_API.G_MISS_NUM) <> nvl(l_count_bc, FND_API.G_MISS_NUM)) or
466 (nvl(c_Reent_Status_Rec.lud_bc, FND_API.G_MISS_DATE) <> nvl(l_lud_bc, FND_API.G_MISS_DATE)) or
467 (nvl(c_Reent_Status_Rec.count_bg1, FND_API.G_MISS_NUM) <> nvl(l_count_bg1, FND_API.G_MISS_NUM)) or
468 (nvl(c_Reent_Status_Rec.lud_bg1, FND_API.G_MISS_DATE) <> nvl(l_lud_bg1, FND_API.G_MISS_DATE)) or
469 (nvl(c_Reent_Status_Rec.count_bg2, FND_API.G_MISS_NUM) <> nvl(l_count_bg2, FND_API.G_MISS_NUM)) or
470 (nvl(c_Reent_Status_Rec.lud_bg2, FND_API.G_MISS_DATE) <> nvl(l_lud_bg2, FND_API.G_MISS_DATE)) OR
471
472 /* Bug 3525832 Start */
473 (nvl(c_Reent_Status_Rec.gcd_ws, FND_API.G_MISS_DATE) <> nvl(g_gl_cutoff_period, FND_API.G_MISS_DATE))) THEN
474 /* Bug 3525832 End */
475
476 begin
477 g_sp1_status := 'I';
478 g_sp2_status := 'I';
479 g_sp3_status := 'I';
480 g_sp4_status := 'I';
481 l_restart := FND_API.G_FALSE;
482 end;
483 else
484 begin
485
486 if ((nvl(c_Reent_Status_Rec.lud_de, FND_API.G_MISS_DATE) <> nvl(l_lud_de, FND_API.G_MISS_DATE)) or
487 (nvl(c_Reent_Status_Rec.count_assign, FND_API.G_MISS_NUM) <> nvl(l_count_assign, FND_API.G_MISS_NUM)) or
488 (nvl(c_Reent_Status_Rec.lud_assign, FND_API.G_MISS_DATE) <> nvl(l_lud_assign, FND_API.G_MISS_DATE)) or
489 (nvl(c_Reent_Status_Rec.count_rates, FND_API.G_MISS_NUM) <> nvl(l_count_rates, FND_API.G_MISS_NUM)) or
490 (nvl(c_Reent_Status_Rec.lud_rates, FND_API.G_MISS_DATE) <> nvl(l_lud_rates, FND_API.G_MISS_DATE)) or
491 (nvl(c_Reent_Status_Rec.count_dist, FND_API.G_MISS_NUM) <> nvl(l_count_dist, FND_API.G_MISS_NUM)) or
492 (nvl(c_Reent_Status_Rec.lud_dist, FND_API.G_MISS_DATE) <> nvl(l_lud_dist, FND_API.G_MISS_DATE))) then
493 begin
494 g_sp1_status := c_Reent_Status_Rec.sp1_status;
495 g_sp2_status := 'I';
496 g_sp3_status := 'I';
497 g_sp4_status := 'I';
498 end;
499 elsif ((nvl(c_Reent_Status_Rec.count_cs1, FND_API.G_MISS_NUM) <> nvl(l_count_cs1, FND_API.G_MISS_NUM)) or
500 (nvl(c_Reent_Status_Rec.lud_cs1, FND_API.G_MISS_DATE) <> nvl(l_lud_cs1, FND_API.G_MISS_DATE)) or
501 (nvl(c_Reent_Status_Rec.count_cs2, FND_API.G_MISS_NUM) <> nvl(l_count_cs2, FND_API.G_MISS_NUM)) or
502 (nvl(c_Reent_Status_Rec.lud_cs2, FND_API.G_MISS_DATE) <> nvl(l_lud_cs2, FND_API.G_MISS_DATE))) then
503 begin
504 g_sp1_status := c_Reent_status_rec.sp1_status;
505 g_sp2_status := c_Reent_status_rec.sp2_status;
506 g_sp3_status := c_Reent_status_rec.sp3_status;
507 g_sp4_status := 'I';
508 end;
509 else
510 begin
511 g_sp1_status := c_Reent_status_rec.sp1_status;
512 g_sp2_status := c_Reent_status_rec.sp2_status;
513 g_sp3_status := c_Reent_status_rec.sp3_status;
514 g_sp4_status := c_Reent_status_rec.sp4_status;
515 end;
516 end if;
517
518 l_restart := FND_API.G_TRUE;
519
520 end;
521 end if;
522
523 end loop;
524
525 if not FND_API.to_Boolean(l_rec_found) then
526 begin
527
528 insert into PSB_REENTRANT_PROCESS_STATUS
529 (process_type, process_uid,
530 attribute1, attribute2,
531 attribute3, attribute4,
532 attribute5, attribute6,
533 attribute7, attribute8,
534 attribute9, attribute10,
535 attribute11, attribute12,
536 attribute13, attribute14,
537 attribute15, attribute16,
538 attribute17, attribute18,
539 attribute19, attribute20,
540 attribute21, attribute22,
541 attribute23, attribute24,
542 attribute25, attribute26,
543 attribute27, attribute28,
544 attribute29, attribute30,
545 sp1_status, sp2_status,
546 sp3_status, sp4_status,
547 sp5_status, sp6_status,
548 sp7_status, sp8_status,
549 sp9_status, sp10_status,
550 sp11_status, sp12_status,
551 sp13_status, sp14_status,
552 sp15_status, sp16_status,
553 sp17_status, sp18_status,
554 sp19_status, sp20_status,
555 sp21_status, sp22_status,
556 sp23_status, sp24_status,
557 sp25_status, sp26_status,
558 sp27_status, sp28_status,
559 sp29_status, sp30_status)
560 values ('WORKSHEET_CREATION', p_worksheet_id,
561 to_char(l_count_ps1), to_char(l_lud_ps1, 'YYYY/MM/DD HH24:MI:SS'),
562 to_char(l_count_ps2), to_char(l_lud_ps2, 'YYYY/MM/DD HH24:MI:SS'),
563 to_char(l_count_cs1), to_char(l_lud_cs1, 'YYYY/MM/DD HH24:MI:SS'),
564 to_char(l_count_cs2), to_char(l_lud_cs2, 'YYYY/MM/DD HH24:MI:SS'),
565 to_char(l_count_ar1), to_char(l_lud_ar1, 'YYYY/MM/DD HH24:MI:SS'),
566 to_char(l_count_ar2), to_char(l_lud_ar2, 'YYYY/MM/DD HH24:MI:SS'),
567 to_char(l_count_bc), to_char(l_lud_bc, 'YYYY/MM/DD HH24:MI:SS'),
568 to_char(l_count_bg1), to_char(l_lud_bg1, 'YYYY/MM/DD HH24:MI:SS'),
569 to_char(l_count_bg2), to_char(l_lud_bg2, 'YYYY/MM/DD HH24:MI:SS'),
570 to_char(l_lud_de, 'YYYY/MM/DD HH24:MI:SS'),
571 to_char(l_count_assign), to_char(l_lud_assign, 'YYYY/MM/DD HH24:MI:SS'),
572 to_char(l_count_rates), to_char(l_lud_rates, 'YYYY/MM/DD HH24:MI:SS'),
573 to_char(l_count_dist), to_char(l_lud_dist, 'YYYY/MM/DD HH24:MI:SS'),
574 to_char(l_count_glset1), to_char(l_lud_glset1, 'YYYY/MM/DD HH24:MI:SS'),
575 to_char(l_count_glset2), to_char(l_lud_glset2, 'YYYY/MM/DD HH24:MI:SS'),
576
577 /* Bug 3525832 Start */
578 TO_CHAR(g_gl_cutoff_period, 'YYYY/MM/DD HH24:MI:SS'),
579 /* Bug 3525832 End */
580
581 'I', 'I',
582 'I', 'I',
583 null, null,
584 null, null,
585 null, null,
586 null, null,
587 null, null,
588 null, null,
589 null, null,
590 null, null,
591 null, null,
592 null, null,
593 null, null,
594 null, null,
595 null, null);
596
597 g_sp1_status := 'I';
598 g_sp2_status := 'I';
599 g_sp3_status := 'I';
600 g_sp4_status := 'I';
601
602 l_restart := FND_API.G_FALSE;
603
604 end;
605 end if;
606
607 if FND_API.to_Boolean(l_restart) then
608 begin
609
610 update PSB_REENTRANT_PROCESS_STATUS
611 set sp1_status = g_sp1_status,
612 sp2_status = g_sp2_status,
613 sp3_status = g_sp3_status,
614 sp4_status = g_sp4_status
615 where process_type = 'WORKSHEET_CREATION'
616 and process_uid = p_worksheet_id;
617
618 end;
619 else
620 begin
621
622 update PSB_REENTRANT_PROCESS_STATUS
623 set attribute1 = to_char(l_count_ps1),
624 attribute2 = to_char(l_lud_ps1, 'YYYY/MM/DD HH24:MI:SS'),
625 attribute3 = to_char(l_count_ps2),
626 attribute4 = to_char(l_lud_ps2, 'YYYY/MM/DD HH24:MI:SS'),
627 attribute5 = to_char(l_count_cs1),
628 attribute6 = to_char(l_lud_cs1, 'YYYY/MM/DD HH24:MI:SS'),
629 attribute7 = to_char(l_count_cs2),
630 attribute8 = to_char(l_lud_cs2, 'YYYY/MM/DD HH24:MI:SS'),
631 attribute9 = to_char(l_count_ar1),
632 attribute10 = to_char(l_lud_ar1, 'YYYY/MM/DD HH24:MI:SS'),
633 attribute11 = to_char(l_count_ar2),
634 attribute12 = to_char(l_lud_ar2, 'YYYY/MM/DD HH24:MI:SS'),
635 attribute13 = to_char(l_count_bc),
636 attribute14 = to_char(l_lud_bc, 'YYYY/MM/DD HH24:MI:SS'),
637 attribute15 = to_char(l_count_bg1),
638 attribute16 = to_char(l_lud_bg1, 'YYYY/MM/DD HH24:MI:SS'),
639 attribute17 = to_char(l_count_bg2),
640 attribute18 = to_char(l_lud_bg2, 'YYYY/MM/DD HH24:MI:SS'),
641 attribute19 = to_char(l_lud_de, 'YYYY/MM/DD HH24:MI:SS'),
642 attribute20 = to_char(l_count_assign),
643 attribute21 = to_char(l_lud_assign, 'YYYY/MM/DD HH24:MI:SS'),
644 attribute22 = to_char(l_count_rates),
645 attribute23 = to_char(l_lud_rates, 'YYYY/MM/DD HH24:MI:SS'),
646 attribute24 = to_char(l_count_dist),
647 attribute25 = to_char(l_lud_dist, 'YYYY/MM/DD HH24:MI:SS'),
648 attribute26 = to_char(l_count_glset1),
649 attribute27 = to_char(l_lud_glset1, 'YYYY/MM/DD HH24:MI:SS'),
650 attribute28 = to_char(l_count_glset2),
651 attribute29 = to_char(l_lud_glset2, 'YYYY/MM/DD HH24:MI:SS'),
652
653 /* Bug 3525832 Start */
654 attribute30 = TO_CHAR(g_gl_cutoff_period, 'YYYY/MM/DD HH24:MI:SS'),
655 /* Bug 3525832 End */
656
657 sp1_status = g_sp1_status,
658 sp2_status = g_sp2_status,
659 sp3_status = g_sp3_status,
660 sp4_status = g_sp4_status
661 where process_type = 'WORKSHEET_CREATION'
662 and process_uid = p_worksheet_id;
663
664 end;
665 end if;
666
667
668 -- Initialize API return status to success
669
670 p_return_status := FND_API.G_RET_STS_SUCCESS;
671
672 EXCEPTION
673
674 when FND_API.G_EXC_ERROR then
675 p_return_status := FND_API.G_RET_STS_ERROR;
676
677 when FND_API.G_EXC_UNEXPECTED_ERROR then
678 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
679
680 when OTHERS then
681 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
682
683 END Check_Reentrant_Status;
684
685 /* ----------------------------------------------------------------------- */
686
687 PROCEDURE Create_WS_Line_Items
688 ( p_api_version IN NUMBER,
689 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_NONE,
690 p_return_status OUT NOCOPY VARCHAR2,
691 p_worksheet_id IN NUMBER
692 ) IS
693
694 l_api_name CONSTANT VARCHAR2(30) := 'Create_WS_Line_Items';
695 l_api_version CONSTANT NUMBER := 1.0;
696
697 l_validation_status VARCHAR2(1);
698 l_return_status VARCHAR2(1);
699
700 BEGIN
701
702 -- Standard call to check for call compatibility.
703
704 if not FND_API.Compatible_API_Call (l_api_version,
705 p_api_version,
706 l_api_name,
707 G_PKG_NAME)
708 then
709 raise FND_API.G_EXC_UNEXPECTED_ERROR;
710 end if;
711
712 PSB_WORKSHEET.Update_Worksheet
713 (p_api_version => 1.0,
714 p_return_status => l_return_status,
715 p_worksheet_id => p_worksheet_id,
716 p_ws_creation_complete => 'N');
717
718 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
719 raise FND_API.G_EXC_ERROR;
720 end if;
721
722 Initialize (p_worksheet_id => p_worksheet_id,
723 p_return_status => l_return_status);
724
725 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
726 raise FND_API.G_EXC_ERROR;
727 end if;
728
729 /*For Bug No : 2260391 Start*/
730 /*if g_current_stage_seq <> g_start_stage_seq then
731 add_message('PSB', 'PSB_CANNOT_RECREATE_WORKSHEET');
732 raise FND_API.G_EXC_ERROR;
733 end if;*/
734 /*For Bug No : 2260391 End*/
735
736 if g_budget_calendar_id <> nvl(PSB_WS_ACCT1.g_budget_calendar_id, FND_API.G_MISS_NUM) then
737 begin
738
739 PSB_WS_ACCT1.Cache_Budget_Calendar
740 (p_return_status => l_return_status,
741 p_budget_calendar_id => g_budget_calendar_id);
742
743 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
744 raise FND_API.G_EXC_ERROR;
745 end if;
746
747 end;
748 end if;
749
750 PSB_WS_POS1.g_budget_calendar_id := g_budget_calendar_id;
751 PSB_WS_POS1.g_budget_group_id := g_budget_group_id;
752 PSB_WS_POS1.g_global_worksheet_id := nvl(g_global_worksheet_id, p_worksheet_id);
753 PSB_WS_POS1.g_local_copy_flag := g_local_copy_flag;
754
755 Check_Reentrant_Status
756 (p_return_status => l_return_status,
757 p_worksheet_id => p_worksheet_id,
758 p_parameter_set_id => g_parameter_set_id,
759 p_constraint_set_id => g_constraint_set_id,
760 p_allocrule_set_id => g_allocrule_set_id,
761 p_budget_calendar_id => g_budget_calendar_id,
762 p_budget_group_id => g_budget_group_id,
763 p_data_extract_id => g_data_extract_id,
764 p_gl_budget_set_id => g_gl_budget_set_id);
765
766 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
767 raise FND_API.G_EXC_ERROR;
768 end if;
769
770 PSB_CONCURRENCY_CONTROL_PVT.Enforce_Concurrency_Control
771 (p_api_version => 1.0 ,
772 p_return_status => l_return_status,
773 p_concurrency_class => 'WORKSHEET_CREATION',
774 p_concurrency_entity_name => 'WORKSHEET',
775 p_concurrency_entity_id => p_worksheet_id);
776
777 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
778 raise FND_API.G_EXC_ERROR;
779 end if;
780
781 if g_sp1_status = 'I' then
782 begin
783
784 PSB_WS_ACCT2.Create_Worksheet_Accounts
785 (p_return_status => l_return_status,
786 p_worksheet_id => p_worksheet_id,
787 p_rounding_factor => g_rounding_factor,
788 p_stage_set_id => g_stage_set_id,
789 p_service_package_id => g_service_package_id,
790 p_start_stage_seq => g_start_stage_seq,
791 p_allocrule_set_id => g_allocrule_set_id,
792 p_budget_group_id => g_budget_group_id,
793 p_flex_code => g_flex_code,
794 p_parameter_set_id => g_parameter_set_id,
795 p_budget_calendar_id => g_budget_calendar_id,
796 p_gl_cutoff_period => g_gl_cutoff_period,
797 p_include_gl_commit_balance => g_include_gl_commit_balance,
798 p_include_gl_oblig_balance => g_include_gl_oblig_balance,
799 p_include_gl_other_balance => g_include_gl_other_balance,
800 p_budget_version_id => g_budget_version_id,
801 p_flex_mapping_set_id => g_flex_mapping_set_id,
802 p_gl_budget_set_id => g_gl_budget_set_id,
803 p_set_of_books_id => g_set_of_books_id,
804 p_set_of_books_name => g_set_of_books_name,
805 p_func_currency => g_currency_code,
806 p_budgetary_control => g_budgetary_control,
807 p_incl_stat_bal => g_incl_stat_bal,
808 p_incl_trans_bal => g_incl_trans_bal,
809 p_incl_adj_period => g_incl_adj_period,
810 p_num_proposed_years => g_num_proposed_years,
811 p_num_years_to_allocate => g_num_years_to_allocate,
812 p_budget_by_position => g_budget_by_position,
813 /* Bug No 4725091 */
814 P_incl_gl_fwd_balance => g_include_gl_forward_balance);
815
816 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
817 raise FND_API.G_EXC_ERROR;
818 end if;
819
820 update PSB_REENTRANT_PROCESS_STATUS
821 set sp1_status = 'C'
822 where process_type = 'WORKSHEET_CREATION'
823 and process_uid = p_worksheet_id;
824
825 commit work;
826
827 end;
828 end if;
829
830 g_dbug := g_dbug || g_chr10 ||
831 'Create_WS_Line_Items: After Phase 1';
832
833 if FND_API.to_Boolean(g_budget_by_position) then
834 begin
835
836 if g_sp2_status = 'I' then
837 begin
838
839 PSB_WS_POS2.Create_Worksheet_Positions
840 (p_return_status => l_return_status,
841 p_root_budget_group_id => g_root_budget_group_id,
842 p_global_worksheet_id => g_global_worksheet_id,
843 p_worksheet_id => p_worksheet_id,
844 p_global_worksheet => g_global_worksheet,
845 p_budget_group_id => g_budget_group_id,
846 p_worksheet_numyrs => g_num_proposed_years,
847 p_rounding_factor => g_rounding_factor,
848 p_service_package_id => g_service_package_id,
849 p_stage_set_id => g_stage_set_id,
850 p_start_stage_seq => g_start_stage_seq,
851 p_current_stage_seq => g_current_stage_seq,
852 p_data_extract_id => g_data_extract_id,
853 p_business_group_id => g_business_group_id,
854 p_budget_calendar_id => g_budget_calendar_id,
855 p_parameter_set_id => g_parameter_set_id,
856 p_func_currency => g_currency_code,
857 p_flex_mapping_set_id => g_flex_mapping_set_id,
858 p_flex_code => g_flex_code,
859 p_apply_element_parameters => g_apply_element_parameters,
860 p_apply_position_parameters => g_apply_position_parameters);
861
862 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
863 raise FND_API.G_EXC_ERROR;
864 end if;
865
866 update PSB_REENTRANT_PROCESS_STATUS
867 set sp2_status = 'C'
868 where process_type = 'WORKSHEET_CREATION'
869 and process_uid = p_worksheet_id;
870
871 commit work;
872
873 end;
874 end if;
875
876 end;
877 end if;
878
879 g_dbug := g_dbug || g_chr10 ||
880 'Create_WS_Line_Items: After Phase 2';
881
882 if g_sp3_status = 'I' then
883 begin
884
885 PSB_WS_ACCT2.Create_Rollup_Totals
886 (p_api_version => 1.0,
887 p_worksheet_id => p_worksheet_id,
888 p_rounding_factor => g_rounding_factor,
889 p_stage_set_id => g_stage_set_id,
890 p_current_stage_seq => g_current_stage_seq,
891 p_set_of_books_id => g_set_of_books_id,
892 p_flex_code => g_flex_code,
893 p_budget_group_id => g_budget_group_id,
894 p_budget_calendar_id => g_budget_calendar_id,
895 p_return_status => l_return_status);
896
897 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
898 raise FND_API.G_EXC_ERROR;
899 end if;
900
901 update PSB_REENTRANT_PROCESS_STATUS
902 set sp3_status = 'C'
903 where process_type = 'WORKSHEET_CREATION'
904 and process_uid = p_worksheet_id;
905
906 commit work;
907
908 end;
909 end if;
910
911 g_dbug := g_dbug || g_chr10 ||
912 'Create_WS_Line_Items: After Phase 3';
913
914 if nvl(g_constraint_set_id, FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM then
915 begin
916
917 if g_sp4_status = 'I' then
918 begin
919
920 Apply_Constraints
921 (p_api_version => 1.0,
922 p_return_status => l_return_status,
923 p_validation_status => l_validation_status,
924 p_worksheet_id => p_worksheet_id,
925 p_budget_group_id => g_budget_group_id,
926 p_flex_code => g_flex_code,
927 p_func_currency => g_currency_code,
928 p_global_worksheet_id => nvl(g_global_worksheet_id, p_worksheet_id),
929 p_constraint_set_id => g_constraint_set_id,
930 p_constraint_set_name => g_cs_name,
931 p_constraint_set_threshold => g_cs_threshold,
932 p_budget_calendar_id => g_budget_calendar_id,
933 p_data_extract_id => g_data_extract_id,
934 p_business_group_id => g_business_group_id,
935 p_budget_by_position => g_budget_by_position);
936
937 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
938 raise FND_API.G_EXC_ERROR;
939 end if;
940
941 update PSB_REENTRANT_PROCESS_STATUS
942 set sp4_status = 'C'
943 where process_type = 'WORKSHEET_CREATION'
944 and process_uid = p_worksheet_id;
945
946 end;
947 end if;
948
949 end;
950 end if;
951
952 PSB_WORKSHEET.Update_Worksheet
953 (p_api_version => 1.0,
954 p_return_status => l_return_status,
955 p_worksheet_id => p_worksheet_id,
956 p_ws_creation_complete => 'Y');
957
958 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
959 raise FND_API.G_EXC_ERROR;
960 end if;
961
962 commit work;
963
964 PSB_CONCURRENCY_CONTROL_PVT.Release_Concurrency_Control
965 (p_api_version => 1.0 ,
966 p_return_status => l_return_status,
967 p_concurrency_class => 'WORKSHEET_CREATION',
968 p_concurrency_entity_name => 'WORKSHEET',
969 p_concurrency_entity_id => p_worksheet_id);
970
971 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
972 raise FND_API.G_EXC_ERROR;
973 end if;
974
975 g_dbug := g_dbug || g_chr10 ||
976 'Create_WS_Line_Items: After Phase 4';
977
978 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
979 raise FND_API.G_EXC_ERROR;
980 end if;
981
982
983 -- Initialize API return status to success
984
985 p_return_status := FND_API.G_RET_STS_SUCCESS;
986
987
988 EXCEPTION
989
990 when FND_API.G_EXC_ERROR then
991 p_return_status := FND_API.G_RET_STS_ERROR;
992
993 PSB_CONCURRENCY_CONTROL_PVT.Release_Concurrency_Control
994 (p_api_version => 1.0 ,
995 p_return_status => l_return_status,
996 p_concurrency_class => 'WORKSHEET_CREATION',
997 p_concurrency_entity_name => 'WORKSHEET',
998 p_concurrency_entity_id => p_worksheet_id);
999
1000 when FND_API.G_EXC_UNEXPECTED_ERROR then
1001 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1002
1003 PSB_CONCURRENCY_CONTROL_PVT.Release_Concurrency_Control
1004 (p_api_version => 1.0 ,
1005 p_return_status => l_return_status,
1006 p_concurrency_class => 'WORKSHEET_CREATION',
1007 p_concurrency_entity_name => 'WORKSHEET',
1008 p_concurrency_entity_id => p_worksheet_id);
1009
1010 when OTHERS then
1011 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1012
1013 PSB_CONCURRENCY_CONTROL_PVT.Release_Concurrency_Control
1014 (p_api_version => 1.0 ,
1015 p_return_status => l_return_status,
1016 p_concurrency_class => 'WORKSHEET_CREATION',
1017 p_concurrency_entity_name => 'WORKSHEET',
1018 p_concurrency_entity_id => p_worksheet_id);
1019
1020 if FND_MSG_PUB.Check_Msg_Level
1021 (p_message_level => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) then
1022
1023 FND_MSG_PUB.Add_Exc_Msg
1024 (p_pkg_name => G_PKG_NAME,
1025 p_procedure_name => l_api_name);
1026
1027 end if;
1028
1029 END Create_WS_Line_Items;
1030
1031 /* ----------------------------------------------------------------------- */
1032
1033 PROCEDURE Apply_Constraints
1034 ( p_api_version IN NUMBER,
1035 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_NONE,
1036 p_return_status OUT NOCOPY VARCHAR2,
1037 p_validation_status OUT NOCOPY VARCHAR2,
1038 p_worksheet_id IN NUMBER,
1039 p_budget_group_id IN NUMBER := FND_API.G_MISS_NUM,
1040 p_flex_code IN NUMBER := FND_API.G_MISS_NUM,
1041 p_func_currency IN VARCHAR2 := FND_API.G_MISS_CHAR,
1042 p_global_worksheet_id IN NUMBER := FND_API.G_MISS_NUM,
1043 p_constraint_set_id IN NUMBER := FND_API.G_MISS_NUM,
1044 p_constraint_set_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
1045 p_constraint_set_threshold IN NUMBER := FND_API.G_MISS_NUM,
1046 p_budget_calendar_id IN NUMBER := FND_API.G_MISS_NUM,
1047 p_data_extract_id IN NUMBER := FND_API.G_MISS_NUM,
1048 p_business_group_id IN NUMBER := FND_API.G_MISS_NUM,
1049 p_budget_by_position IN VARCHAR2 := FND_API.G_MISS_CHAR
1050 ) IS
1051
1052 l_api_name CONSTANT VARCHAR2(30) := 'Apply_Constraints';
1053 l_api_version CONSTANT NUMBER := 1.0;
1054
1055 l_budget_group_id NUMBER;
1056 l_global_worksheet_id NUMBER;
1057 l_constraint_set_id NUMBER;
1058 l_budget_calendar_id NUMBER;
1059 l_data_extract_id NUMBER;
1060 l_business_group_id NUMBER;
1061 l_budget_by_position VARCHAR2(1);
1062
1063 l_flex_code NUMBER;
1064 l_func_currency VARCHAR2(10);
1065
1066 l_cs_name VARCHAR2(30);
1067 l_cs_threshold NUMBER;
1068
1069 l_constraint_set_status VARCHAR2(1) := 'S';
1070 l_validation_status VARCHAR2(1);
1071
1072 l_return_status VARCHAR2(1);
1073 l_msg_data VARCHAR2(2000);
1074 l_msg_count NUMBER;
1075
1076 cursor c_WS is
1077 select budget_group_id,
1078 nvl(global_worksheet_id, worksheet_id) global_worksheet_id,
1079 nvl(constraint_set_id, global_constraint_set_id) constraint_set_id,
1080 budget_calendar_id,
1081 nvl(data_extract_id, global_data_extract_id) data_extract_id,
1082 budget_by_position
1083 from PSB_WORKSHEETS_V
1084 where worksheet_id = p_worksheet_id;
1085
1086 cursor c_BG is
1087 select nvl(chart_of_accounts_id, root_chart_of_accounts_id) chart_of_accounts_id,
1088 nvl(currency_code, root_currency_code) currency_code,
1089 nvl(business_group_id, root_business_group_id) business_group_id
1090 from PSB_BUDGET_GROUPS_V
1091 where budget_group_id = l_budget_group_id;
1092
1093 cursor c_ConstSet is
1094 select name,
1095 constraint_threshold
1096 from PSB_CONSTRAINT_SETS_V
1097 where constraint_set_id = l_constraint_set_id;
1098
1099 BEGIN
1100
1101 -- Standard call to check for call compatibility.
1102
1103 if not FND_API.Compatible_API_Call (l_api_version,
1104 p_api_version,
1105 l_api_name,
1106 G_PKG_NAME)
1107 then
1108 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1109 end if;
1110
1111 if ((p_budget_group_id = FND_API.G_MISS_NUM) or
1112 (p_global_worksheet_id = FND_API.G_MISS_NUM) or
1113 (p_constraint_set_id = FND_API.G_MISS_NUM) or
1114 (p_budget_calendar_id = FND_API.G_MISS_NUM) or
1115 (p_data_extract_id = FND_API.G_MISS_NUM) or
1116 (p_budget_by_position = FND_API.G_MISS_CHAR)) then
1117 begin
1118
1119 for c_WS_Rec in c_WS loop
1120 l_budget_group_id := c_WS_Rec.budget_group_id;
1121 l_global_worksheet_id := c_WS_Rec.global_worksheet_id;
1122 l_constraint_set_id := c_WS_Rec.constraint_set_id;
1123 l_budget_calendar_id := c_WS_Rec.budget_calendar_id;
1124 l_data_extract_id := c_WS_Rec.data_extract_id;
1125 l_budget_by_position := c_WS_Rec.budget_by_position;
1126 end loop;
1127
1128 end;
1129 end if;
1130
1131 if p_budget_group_id <> FND_API.G_MISS_NUM then
1132 l_budget_group_id := p_budget_group_id;
1133 end if;
1134
1135 if p_global_worksheet_id <> FND_API.G_MISS_NUM then
1136 l_global_worksheet_id := p_global_worksheet_id;
1137 end if;
1138
1139 if p_constraint_set_id <> FND_API.G_MISS_NUM then
1140 l_constraint_set_id := p_constraint_set_id;
1141 end if;
1142
1143 if p_budget_calendar_id <> FND_API.G_MISS_NUM then
1144 l_budget_calendar_id := p_budget_calendar_id;
1145 end if;
1146
1147 if p_data_extract_id <> FND_API.G_MISS_NUM then
1148 l_data_extract_id := p_data_extract_id;
1149 end if;
1150
1151 if ((p_flex_code = FND_API.G_MISS_NUM) or
1152 (p_func_currency = FND_API.G_MISS_CHAR) or
1153 (p_business_group_id = FND_API.G_MISS_NUM)) then
1154 begin
1155
1156 for c_BG_Rec in c_BG loop
1157 l_flex_code := c_BG_Rec.chart_of_accounts_id;
1158 l_func_currency := c_BG_Rec.currency_code;
1159 l_business_group_id := c_BG_Rec.business_group_id;
1160 end loop;
1161
1162 end;
1163 end if;
1164
1165 if p_flex_code <> FND_API.G_MISS_NUM then
1166 l_flex_code := p_flex_code;
1167 end if;
1168
1169 if p_func_currency <> FND_API.G_MISS_CHAR then
1170 l_func_currency := p_func_currency;
1171 end if;
1172
1173 if p_business_group_id <> FND_API.G_MISS_NUM then
1174 l_business_group_id := p_business_group_id;
1175 end if;
1176
1177 if ((l_budget_by_position is null) or (l_budget_by_position = 'N')) then
1178 l_budget_by_position := FND_API.G_FALSE;
1179 else
1180 l_budget_by_position := FND_API.G_TRUE;
1181 end if;
1182
1183 if p_budget_by_position <> FND_API.G_MISS_CHAR then
1184 l_budget_by_position := p_budget_by_position;
1185 end if;
1186
1187 if ((p_constraint_set_name = FND_API.G_MISS_CHAR) or
1188 (p_constraint_set_threshold = FND_API.G_MISS_NUM)) then
1189 begin
1190
1191 for c_ConstSet_Rec in c_ConstSet loop
1192 l_cs_name := c_ConstSet_Rec.name;
1193 l_cs_threshold := c_ConstSet_Rec.constraint_threshold;
1194 end loop;
1195
1196 end;
1197 end if;
1198
1199 if p_constraint_set_name <> FND_API.G_MISS_CHAR then
1200 l_cs_name := p_constraint_set_name;
1201 end if;
1202
1203 if p_constraint_set_threshold <> FND_API.G_MISS_NUM then
1204 l_cs_threshold := p_constraint_set_threshold;
1205 end if;
1206
1207 if nvl(l_constraint_set_id, FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM then
1208 begin
1209
1210 delete from PSB_ERROR_MESSAGES
1211 where source_process = 'WORKSHEET_CREATION'
1212 and process_id = p_worksheet_id;
1213
1214 if l_budget_calendar_id <> nvl(PSB_WS_ACCT1.g_budget_calendar_id, FND_API.G_MISS_NUM) then
1215 begin
1216
1217 PSB_WS_ACCT1.Cache_Budget_Calendar
1218 (p_return_status => l_return_status,
1219 p_budget_calendar_id => l_budget_calendar_id);
1220
1221 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1222 raise FND_API.G_EXC_ERROR;
1223 end if;
1224
1225 end;
1226 end if;
1227
1228 PSB_WS_ACCT1.Apply_Account_Constraints
1229 (p_return_status => l_return_status,
1230 p_validation_status => l_validation_status,
1231 p_worksheet_id => p_worksheet_id,
1232 p_flex_mapping_set_id => g_flex_mapping_set_id,
1233 p_budget_group_id => l_budget_group_id,
1234 p_flex_code => l_flex_code,
1235 p_func_currency => l_func_currency,
1236 p_constraint_set_id => l_constraint_set_id,
1237 p_constraint_set_name => l_cs_name,
1238 p_constraint_threshold => l_cs_threshold,
1239 p_budget_calendar_id => l_budget_calendar_id);
1240
1241 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1242 raise FND_API.G_EXC_ERROR;
1243 end if;
1244
1245 l_constraint_set_status := l_validation_status;
1246
1247 if FND_API.to_Boolean(l_budget_by_position) then
1248 begin
1249
1250 PSB_WS_POS3.Apply_Position_Constraints
1251 (p_return_status => l_return_status,
1252 p_validation_status => l_validation_status,
1253 p_worksheet_id => p_worksheet_id,
1254 p_budget_calendar_id => l_budget_calendar_id,
1255 p_data_extract_id => l_data_extract_id,
1256 p_business_group_id => l_business_group_id,
1257 p_func_currency => l_func_currency,
1258 p_constraint_set_id => l_constraint_set_id,
1259 p_constraint_set_name => l_cs_name,
1260 p_constraint_threshold => l_cs_threshold);
1261
1262 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1263 raise FND_API.G_EXC_ERROR;
1264 end if;
1265
1266 if ((l_constraint_set_status = 'S') and
1267 (l_validation_status <> 'S')) then
1268 l_constraint_set_status := l_validation_status;
1269 elsif ((l_constraint_set_status = 'E') and
1270 (l_validation_status = 'F')) then
1271 l_constraint_set_status := l_validation_status;
1272 elsif ((l_constraint_set_status = 'W') and
1273 (l_validation_status in ('F', 'E'))) then
1274 l_constraint_set_status := l_validation_status;
1275 end if;
1276
1277 PSB_WS_POS3.Apply_Element_Constraints
1278 (p_return_status => l_return_status,
1279 p_worksheet_id => l_global_worksheet_id,
1280 p_budget_calendar_id => l_budget_calendar_id,
1281 p_data_extract_id => l_data_extract_id,
1282 p_constraint_set_id => l_constraint_set_id,
1283 p_constraint_set_name => l_cs_name,
1284 p_constraint_threshold => l_cs_threshold);
1285
1286 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1287 raise FND_API.G_EXC_ERROR;
1288 end if;
1289
1290 end;
1291 end if;
1292
1293 end;
1294 end if;
1295
1296
1297 -- Initialize API return status to success
1298
1299 p_validation_status := l_constraint_set_status;
1300 p_return_status := FND_API.G_RET_STS_SUCCESS;
1301
1302
1303 EXCEPTION
1304
1305 when FND_API.G_EXC_ERROR then
1306 p_return_status := FND_API.G_RET_STS_ERROR;
1307 FND_MSG_PUB.Count_And_Get (p_count => l_msg_count,
1308 p_data => l_msg_data);
1309
1310
1311 when FND_API.G_EXC_UNEXPECTED_ERROR then
1312 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1313 FND_MSG_PUB.Count_And_Get (p_count => l_msg_count,
1314 p_data => l_msg_data);
1315
1316
1317 when OTHERS then
1318 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1319
1320 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) then
1321 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
1322 l_api_name);
1323 end if;
1324
1325 FND_MSG_PUB.Count_And_Get (p_count => l_msg_count,
1326 p_data => l_msg_data);
1327
1328 END Apply_Constraints;
1329
1330 /* ----------------------------------------------------------------------- */
1331
1332 PROCEDURE Validate_Entity_Set
1333 ( p_api_version IN NUMBER,
1334 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_NONE,
1335 p_return_status OUT NOCOPY VARCHAR2,
1336 p_data_extract_id IN NUMBER,
1337 p_parameter_set_id IN NUMBER,
1338 p_constraint_set_id IN NUMBER
1339 ) IS
1340
1341 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Entity_Set';
1342 l_api_version CONSTANT NUMBER := 1.0;
1343
1344 l_exists VARCHAR2(1);
1345
1346 cursor c_ParamSet is
1347 select 'Exists'
1348 from PSB_ENTITY_SET
1349 where (p_data_extract_id is null or data_extract_id = p_data_extract_id)
1350 and entity_set_id = p_parameter_set_id;
1351
1352 cursor c_ConsSet is
1353 select 'Exists'
1354 from PSB_ENTITY_SET
1355 where (p_data_extract_id is null or data_extract_id = p_data_extract_id)
1356 and entity_set_id = p_constraint_set_id;
1357
1358 BEGIN
1359
1360 -- Standard call to check for call compatibility.
1361
1362 if not FND_API.Compatible_API_Call (l_api_version,
1363 p_api_version,
1364 l_api_name,
1365 G_PKG_NAME)
1366 then
1367 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1368 end if;
1369
1370 l_exists := FND_API.G_FALSE;
1371
1372 if p_parameter_set_id is not null then
1373 begin
1374
1375 for c_ParamSet_Rec in c_ParamSet loop
1376 l_exists := FND_API.G_TRUE;
1377 end loop;
1378
1379 if not FND_API.to_Boolean(l_exists) then
1380 add_message('PSB', 'PSB_INVALID_PARAMETER_SET');
1381 raise FND_API.G_EXC_ERROR;
1382 end if;
1383
1384 end;
1385 end if;
1386
1387 l_exists := FND_API.G_FALSE;
1388
1389 if p_constraint_set_id is not null then
1390 begin
1391
1392 for c_ConsSet_Rec in c_ConsSet loop
1393 l_exists := FND_API.G_TRUE;
1394 end loop;
1395
1396 if not FND_API.to_Boolean(l_exists) then
1397 add_message('PSB', 'PSB_INVALID_CONSTRAINT_SET');
1398 raise FND_API.G_EXC_ERROR;
1399 end if;
1400
1401 end;
1402 end if;
1403
1404
1405 -- Initialize API return status to success
1406
1407 p_return_status := FND_API.G_RET_STS_SUCCESS;
1408
1409
1410 EXCEPTION
1411
1412 when FND_API.G_EXC_ERROR then
1413 p_return_status := FND_API.G_RET_STS_ERROR;
1414
1415 when FND_API.G_EXC_UNEXPECTED_ERROR then
1416 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1417
1418 when OTHERS then
1419 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1420
1421 if FND_MSG_PUB.Check_Msg_Level
1422 (p_message_level => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) then
1423
1424 FND_MSG_PUB.Add_Exc_Msg
1425 (p_pkg_name => G_PKG_NAME,
1426 p_procedure_name => l_api_name);
1427
1428 end if;
1429
1430 END Validate_Entity_Set;
1431
1432 /* ----------------------------------------------------------------------- */
1433
1434 PROCEDURE Pre_Create_Line_Items
1435 ( p_api_version IN NUMBER,
1436 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_NONE,
1437 p_return_status OUT NOCOPY VARCHAR2,
1438 p_worksheet_id IN NUMBER
1439 ) IS
1440
1441 l_api_name CONSTANT VARCHAR2(30) := 'Pre_Create_Line_Items';
1442 l_api_version CONSTANT NUMBER := 1.0;
1443
1444 l_return_status VARCHAR2(1);
1445
1446 BEGIN
1447
1448 -- Standard call to check for call compatibility.
1449
1450 if not FND_API.Compatible_API_Call (l_api_version,
1451 p_api_version,
1452 l_api_name,
1453 G_PKG_NAME)
1454 then
1455 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1456 end if;
1457
1458 Initialize (p_worksheet_id => p_worksheet_id,
1459 p_return_status => l_return_status);
1460
1461 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1462 raise FND_API.G_EXC_ERROR;
1463 end if;
1464
1465 /*For Bug No : 2260391 Start*/
1466 /*if g_current_stage_seq <> g_start_stage_seq then
1467 add_message('PSB', 'PSB_CANNOT_RECREATE_WORKSHEET');
1468 raise FND_API.G_EXC_ERROR;
1469 end if;*/
1470 /*For Bug No : 2260391 Start*/
1471
1472 delete from PSB_ERROR_MESSAGES
1473 where source_process = 'WORKSHEET_CREATION'
1474 and process_id = p_worksheet_id;
1475
1476 PSB_WORKSHEET.Update_Worksheet
1477 (p_api_version => 1.0,
1478 p_return_status => l_return_status,
1479 p_worksheet_id => p_worksheet_id,
1480 p_ws_creation_complete => 'N');
1481
1482 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1483 raise FND_API.G_EXC_ERROR;
1484 end if;
1485
1486 -- Initialize API return status to success
1487
1488 p_return_status := FND_API.G_RET_STS_SUCCESS;
1489
1490
1491 EXCEPTION
1492
1493 when FND_API.G_EXC_ERROR then
1494 p_return_status := FND_API.G_RET_STS_ERROR;
1495
1496 when FND_API.G_EXC_UNEXPECTED_ERROR then
1497 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1498
1499 when OTHERS then
1500 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1501
1502 if FND_MSG_PUB.Check_Msg_Level
1503 (p_message_level => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) then
1504
1505 FND_MSG_PUB.Add_Exc_Msg
1506 (p_pkg_name => G_PKG_NAME,
1507 p_procedure_name => l_api_name);
1508
1509 end if;
1510
1511 END Pre_Create_Line_Items;
1512
1513 /* ----------------------------------------------------------------------- */
1514
1515 PROCEDURE Create_Acct_Line_Items
1516 ( p_api_version IN NUMBER,
1517 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_NONE,
1518 p_return_status OUT NOCOPY VARCHAR2,
1519 p_worksheet_id IN NUMBER
1520 ) IS
1521
1522 l_api_name CONSTANT VARCHAR2(30) := 'Create_Acct_Line_Items';
1523 l_api_version CONSTANT NUMBER := 1.0;
1524
1525 l_return_status VARCHAR2(1);
1526 l_msg_count NUMBER ;
1527 l_msg_data VARCHAR2(2000) ;
1528
1529 BEGIN
1530
1531 -- Standard call to check for call compatibility.
1532
1533 if not FND_API.Compatible_API_Call (l_api_version,
1534 p_api_version,
1535 l_api_name,
1536 G_PKG_NAME)
1537 then
1538 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1539 end if;
1540
1541 Initialize (p_worksheet_id => p_worksheet_id,
1542 p_return_status => l_return_status);
1543
1544 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1545 raise FND_API.G_EXC_ERROR;
1546 end if;
1547
1548 if g_budget_calendar_id <> nvl(PSB_WS_ACCT1.g_budget_calendar_id, FND_API.G_MISS_NUM) then
1549 begin
1550
1551 PSB_WS_ACCT1.Cache_Budget_Calendar
1552 (p_return_status => l_return_status,
1553 p_budget_calendar_id => g_budget_calendar_id);
1554
1555 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1556 raise FND_API.G_EXC_ERROR;
1557 end if;
1558
1559 end;
1560 end if;
1561
1562 /* Bug 3543845 Start: Check whether the worksheet creation process is
1563 executed for the first time.
1564 */
1565 g_ws_first_time_creation_flag := TRUE;
1566 FOR l_ws_first_time_creation_rec IN
1567 (
1568 SELECT 1
1569 FROM PSB_REENTRANT_PROCESS_STATUS
1570 WHERE PROCESS_UID = p_worksheet_id AND
1571 PROCESS_TYPE = 'WORKSHEET_CREATION'
1572 )
1573 LOOP
1574 g_ws_first_time_creation_flag := FALSE;
1575 END LOOP;
1576 /* Bug 3543845 End */
1577
1578 PSB_WS_POS1.g_budget_calendar_id := g_budget_calendar_id;
1579 PSB_WS_POS1.g_budget_group_id := g_budget_group_id;
1580 PSB_WS_POS1.g_global_worksheet_id := nvl(g_global_worksheet_id, p_worksheet_id);
1581 PSB_WS_POS1.g_local_copy_flag := g_local_copy_flag;
1582
1583 Check_Reentrant_Status
1584 (p_return_status => l_return_status,
1585 p_worksheet_id => p_worksheet_id,
1586 p_parameter_set_id => g_parameter_set_id,
1587 p_constraint_set_id => g_constraint_set_id,
1588 p_allocrule_set_id => g_allocrule_set_id,
1589 p_budget_calendar_id => g_budget_calendar_id,
1590 p_budget_group_id => g_budget_group_id,
1591 p_data_extract_id => g_data_extract_id,
1592 p_gl_budget_set_id => g_gl_budget_set_id);
1593
1594 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1595 raise FND_API.G_EXC_ERROR;
1596 end if;
1597
1598 PSB_CONCURRENCY_CONTROL_PVT.Enforce_Concurrency_Control
1599 (p_api_version => 1.0 ,
1600 p_return_status => l_return_status,
1601 p_concurrency_class => 'WORKSHEET_CREATION',
1602 p_concurrency_entity_name => 'WORKSHEET',
1603 p_concurrency_entity_id => p_worksheet_id);
1604
1605 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1606 raise FND_API.G_EXC_ERROR;
1607 end if;
1608
1609 if g_sp1_status = 'I' then
1610 begin
1611
1612 if ((FND_API.to_Boolean(g_include_cbc_commit_balance)) or
1613 (FND_API.to_Boolean(g_include_cbc_oblig_balance)) or
1614 (FND_API.to_Boolean(g_include_cbc_budget_balance))) then
1615 begin
1616
1617 PSB_COMMITMENTS_PVT.Create_Commitment_Line_Items
1618 (p_api_version => 1.0,
1619 p_return_status => l_return_status,
1620 p_msg_count => l_msg_count,
1621 p_msg_data => l_msg_data,
1622 p_worksheet_id => p_worksheet_id);
1623
1624 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1625 message_token('WORKSHEET', p_worksheet_id);
1626 add_message('PSB', 'PSB_CANNOT_CREATE_COMMITMENT_W');
1627 raise FND_API.G_EXC_ERROR;
1628 end if;
1629
1630 end;
1631 else
1632 begin
1633
1634 PSB_WS_ACCT2.Create_Worksheet_Accounts
1635 (p_return_status => l_return_status,
1636 p_worksheet_id => p_worksheet_id,
1637 p_rounding_factor => g_rounding_factor,
1638 p_stage_set_id => g_stage_set_id,
1639 p_service_package_id => g_service_package_id,
1640 p_start_stage_seq => g_start_stage_seq,
1641 p_allocrule_set_id => g_allocrule_set_id,
1642 p_budget_group_id => g_budget_group_id,
1643 p_flex_code => g_flex_code,
1644 p_parameter_set_id => g_parameter_set_id,
1645 p_budget_calendar_id => g_budget_calendar_id,
1646 p_gl_cutoff_period => g_gl_cutoff_period,
1647 p_include_gl_commit_balance => g_include_gl_commit_balance,
1648 p_include_gl_oblig_balance => g_include_gl_oblig_balance,
1649 p_include_gl_other_balance => g_include_gl_other_balance,
1650 p_budget_version_id => g_budget_version_id,
1651 p_flex_mapping_set_id => g_flex_mapping_set_id,
1652 p_gl_budget_set_id => g_gl_budget_set_id,
1653 p_set_of_books_id => g_set_of_books_id,
1654 p_set_of_books_name => g_set_of_books_name,
1655 p_func_currency => g_currency_code,
1656 p_budgetary_control => g_budgetary_control,
1657 p_incl_stat_bal => g_incl_stat_bal,
1658 p_incl_trans_bal => g_incl_trans_bal,
1659 p_incl_adj_period => g_incl_adj_period,
1660 p_num_proposed_years => g_num_proposed_years,
1661 p_num_years_to_allocate => g_num_years_to_allocate,
1662 p_budget_by_position => g_budget_by_position,
1663 /* bug no 4725091 */
1664 P_incl_gl_fwd_balance => g_include_gl_forward_balance);
1665
1666
1667 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1668 raise FND_API.G_EXC_ERROR;
1669 end if;
1670
1671 end;
1672 end if;
1673
1674 update PSB_REENTRANT_PROCESS_STATUS
1675 set sp1_status = 'C'
1676 where process_type = 'WORKSHEET_CREATION'
1677 and process_uid = p_worksheet_id;
1678
1679 commit work;
1680
1681 end;
1682 end if;
1683
1684 PSB_CONCURRENCY_CONTROL_PVT.Release_Concurrency_Control
1685 (p_api_version => 1.0 ,
1686 p_return_status => l_return_status,
1687 p_concurrency_class => 'WORKSHEET_CREATION',
1688 p_concurrency_entity_name => 'WORKSHEET',
1689 p_concurrency_entity_id => p_worksheet_id);
1690
1691 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1692 raise FND_API.G_EXC_ERROR;
1693 end if;
1694
1695
1696 -- Initialize API return status to success
1697
1698 p_return_status := FND_API.G_RET_STS_SUCCESS;
1699
1700
1701 EXCEPTION
1702
1703 when FND_API.G_EXC_ERROR then
1704 p_return_status := FND_API.G_RET_STS_ERROR;
1705
1706 PSB_CONCURRENCY_CONTROL_PVT.Release_Concurrency_Control
1707 (p_api_version => 1.0 ,
1708 p_return_status => l_return_status,
1709 p_concurrency_class => 'WORKSHEET_CREATION',
1710 p_concurrency_entity_name => 'WORKSHEET',
1711 p_concurrency_entity_id => p_worksheet_id);
1712
1713 when FND_API.G_EXC_UNEXPECTED_ERROR then
1714 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1715
1716 PSB_CONCURRENCY_CONTROL_PVT.Release_Concurrency_Control
1717 (p_api_version => 1.0 ,
1718 p_return_status => l_return_status,
1719 p_concurrency_class => 'WORKSHEET_CREATION',
1720 p_concurrency_entity_name => 'WORKSHEET',
1721 p_concurrency_entity_id => p_worksheet_id);
1722
1723 when OTHERS then
1724 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1725
1726 PSB_CONCURRENCY_CONTROL_PVT.Release_Concurrency_Control
1727 (p_api_version => 1.0 ,
1728 p_return_status => l_return_status,
1729 p_concurrency_class => 'WORKSHEET_CREATION',
1730 p_concurrency_entity_name => 'WORKSHEET',
1731 p_concurrency_entity_id => p_worksheet_id);
1732
1733 if FND_MSG_PUB.Check_Msg_Level
1734 (p_message_level => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) then
1735
1736 FND_MSG_PUB.Add_Exc_Msg
1737 (p_pkg_name => G_PKG_NAME,
1738 p_procedure_name => l_api_name);
1739
1740 end if;
1741
1742 END Create_Acct_Line_Items;
1743
1744 /* ----------------------------------------------------------------------- */
1745
1746 PROCEDURE Create_Pos_Line_Items
1747 ( p_api_version IN NUMBER,
1748 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_NONE,
1749 p_return_status OUT NOCOPY VARCHAR2,
1750 p_worksheet_id IN NUMBER
1751 ) IS
1752
1753 l_api_name CONSTANT VARCHAR2(30) := 'Create_Pos_Line_Items';
1754 l_api_version CONSTANT NUMBER := 1.0;
1755
1756 l_return_status VARCHAR2(1);
1757 l_msg_count NUMBER;
1758 l_msg_data VARCHAR2(2000);
1759
1760 BEGIN
1761
1762 -- Standard call to check for call compatibility.
1763
1764 if not FND_API.Compatible_API_Call (l_api_version,
1765 p_api_version,
1766 l_api_name,
1767 G_PKG_NAME)
1768 then
1769 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1770 end if;
1771
1772 Initialize (p_worksheet_id => p_worksheet_id,
1773 p_return_status => l_return_status);
1774
1775 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1776 raise FND_API.G_EXC_ERROR;
1777 end if;
1778
1779 if FND_API.to_Boolean(g_budget_by_position) then
1780 begin
1781
1782 if g_budget_calendar_id <> nvl(PSB_WS_ACCT1.g_budget_calendar_id, FND_API.G_MISS_NUM) then
1783 begin
1784
1785 PSB_WS_ACCT1.Cache_Budget_Calendar
1786 (p_return_status => l_return_status,
1787 p_budget_calendar_id => g_budget_calendar_id);
1788
1789 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1790 raise FND_API.G_EXC_ERROR;
1791 end if;
1792
1793 end;
1794 end if;
1795
1796 PSB_WS_POS1.g_budget_calendar_id := g_budget_calendar_id;
1797 PSB_WS_POS1.g_budget_group_id := g_budget_group_id;
1798 PSB_WS_POS1.g_global_worksheet_id := nvl(g_global_worksheet_id, p_worksheet_id);
1799
1800 Check_Reentrant_Status
1801 (p_return_status => l_return_status,
1802 p_worksheet_id => p_worksheet_id,
1803 p_parameter_set_id => g_parameter_set_id,
1804 p_constraint_set_id => g_constraint_set_id,
1805 p_allocrule_set_id => g_allocrule_set_id,
1806 p_budget_calendar_id => g_budget_calendar_id,
1807 p_budget_group_id => g_budget_group_id,
1808 p_data_extract_id => g_data_extract_id,
1809 p_gl_budget_set_id => g_gl_budget_set_id);
1810
1811 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1812 raise FND_API.G_EXC_ERROR;
1813 end if;
1814
1815 if g_sp2_status = 'I' then
1816 begin
1817
1818 PSB_CONCURRENCY_CONTROL_PVT.Enforce_Concurrency_Control
1819 (p_api_version => 1.0 ,
1820 p_return_status => l_return_status,
1821 p_concurrency_class => 'WORKSHEET_CREATION',
1822 p_concurrency_entity_name => 'WORKSHEET',
1823 p_concurrency_entity_id => p_worksheet_id);
1824
1825 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1826 raise FND_API.G_EXC_ERROR;
1827 end if;
1828
1829 PSB_WS_POS2.Create_Worksheet_Positions
1830 (p_return_status => l_return_status,
1831 p_root_budget_group_id => g_root_budget_group_id,
1832 p_global_worksheet_id => g_global_worksheet_id,
1833 p_worksheet_id => p_worksheet_id,
1834 p_global_worksheet => g_global_worksheet,
1835 p_budget_group_id => g_budget_group_id,
1836 p_worksheet_numyrs => g_num_proposed_years,
1837 p_rounding_factor => g_rounding_factor,
1838 p_service_package_id => g_service_package_id,
1839 p_stage_set_id => g_stage_set_id,
1840 p_start_stage_seq => g_start_stage_seq,
1841 p_current_stage_seq => g_current_stage_seq,
1842 p_data_extract_id => g_data_extract_id,
1843 p_business_group_id => g_business_group_id,
1844 p_budget_calendar_id => g_budget_calendar_id,
1845 p_parameter_set_id => g_parameter_set_id,
1846 p_func_currency => g_currency_code,
1847 p_flex_mapping_set_id => g_flex_mapping_set_id,
1848 p_flex_code => g_flex_code,
1849 p_apply_element_parameters => g_apply_element_parameters,
1850 p_apply_position_parameters => g_apply_position_parameters);
1851
1852 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1853 raise FND_API.G_EXC_ERROR;
1854 end if;
1855
1856 PSB_CONCURRENCY_CONTROL_PVT.Release_Concurrency_Control
1857 (p_api_version => 1.0 ,
1858 p_return_status => l_return_status,
1859 p_concurrency_class => 'WORKSHEET_CREATION',
1860 p_concurrency_entity_name => 'WORKSHEET',
1861 p_concurrency_entity_id => p_worksheet_id);
1862
1863 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1864 raise FND_API.G_EXC_ERROR;
1865 end if;
1866
1867 update PSB_REENTRANT_PROCESS_STATUS
1868 set sp2_status = 'C'
1869 where process_type = 'WORKSHEET_CREATION'
1870 and process_uid = p_worksheet_id;
1871
1872 commit work;
1873
1874 end;
1875 end if;
1876
1877 end;
1878 end if;
1879
1880
1881 -- Initialize API return status to success
1882
1883 p_return_status := FND_API.G_RET_STS_SUCCESS;
1884
1885
1886 EXCEPTION
1887
1888 when FND_API.G_EXC_ERROR then
1889 p_return_status := FND_API.G_RET_STS_ERROR;
1890
1891 PSB_CONCURRENCY_CONTROL_PVT.Release_Concurrency_Control
1892 (p_api_version => 1.0 ,
1893 p_return_status => l_return_status,
1894 p_concurrency_class => 'WORKSHEET_CREATION',
1895 p_concurrency_entity_name => 'WORKSHEET',
1896 p_concurrency_entity_id => p_worksheet_id);
1897
1898 when FND_API.G_EXC_UNEXPECTED_ERROR then
1899 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1900
1901 PSB_CONCURRENCY_CONTROL_PVT.Release_Concurrency_Control
1902 (p_api_version => 1.0 ,
1903 p_return_status => l_return_status,
1904 p_concurrency_class => 'WORKSHEET_CREATION',
1905 p_concurrency_entity_name => 'WORKSHEET',
1906 p_concurrency_entity_id => p_worksheet_id);
1907
1908 when OTHERS then
1909 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1910
1911 PSB_CONCURRENCY_CONTROL_PVT.Release_Concurrency_Control
1912 (p_api_version => 1.0 ,
1913 p_return_status => l_return_status,
1914 p_concurrency_class => 'WORKSHEET_CREATION',
1915 p_concurrency_entity_name => 'WORKSHEET',
1916 p_concurrency_entity_id => p_worksheet_id);
1917
1918 if FND_MSG_PUB.Check_Msg_Level
1919 (p_message_level => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) then
1920
1921 FND_MSG_PUB.Add_Exc_Msg
1922 (p_pkg_name => G_PKG_NAME,
1923 p_procedure_name => l_api_name);
1924
1925 end if;
1926
1927 END Create_Pos_Line_Items;
1928
1929 /* ----------------------------------------------------------------------- */
1930
1931 PROCEDURE Apply_Acct_Constraints
1932 ( p_api_version IN NUMBER,
1933 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_NONE,
1934 p_return_status OUT NOCOPY VARCHAR2,
1935 p_worksheet_id IN NUMBER
1936 ) IS
1937
1938 l_api_name CONSTANT VARCHAR2(30) := 'Apply_Acct_Constraints';
1939 l_api_version CONSTANT NUMBER := 1.0;
1940
1941 l_budget_group_id NUMBER;
1942 l_global_worksheet_id NUMBER;
1943 l_constraint_set_id NUMBER;
1944 l_budget_calendar_id NUMBER;
1945 l_data_extract_id NUMBER;
1946 l_business_group_id NUMBER;
1947 l_budget_by_position VARCHAR2(1);
1948
1949 l_flex_code NUMBER;
1950 l_func_currency VARCHAR2(10);
1951
1952 l_cs_name VARCHAR2(30);
1953 l_cs_threshold NUMBER;
1954
1955 l_return_status VARCHAR2(1);
1956 l_validation_status VARCHAR2(1);
1957
1958 l_msg_count NUMBER;
1959 l_msg_data VARCHAR2(2000);
1960
1961 cursor c_WS is
1962 select budget_group_id,
1963 nvl(global_worksheet_id, worksheet_id) global_worksheet_id,
1964 nvl(constraint_set_id, global_constraint_set_id) constraint_set_id,
1965 budget_calendar_id,
1966 nvl(data_extract_id, global_data_extract_id) data_extract_id,
1967 budget_by_position
1968 from PSB_WORKSHEETS_V
1969 where worksheet_id = p_worksheet_id;
1970
1971 cursor c_BG is
1972 select nvl(chart_of_accounts_id, root_chart_of_accounts_id) chart_of_accounts_id,
1973 nvl(currency_code, root_currency_code) currency_code,
1974 nvl(business_group_id, root_business_group_id) business_group_id
1975 from PSB_BUDGET_GROUPS_V
1976 where budget_group_id = l_budget_group_id;
1977
1978 cursor c_ConstSet is
1979 select name,
1980 constraint_threshold
1981 from PSB_CONSTRAINT_SETS_V
1982 where constraint_set_id = l_constraint_set_id;
1983
1984 BEGIN
1985
1986 -- Standard call to check for call compatibility.
1987
1988 if not FND_API.Compatible_API_Call (l_api_version,
1989 p_api_version,
1990 l_api_name,
1991 G_PKG_NAME)
1992 then
1993 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1994 end if;
1995
1996 for c_WS_Rec in c_WS loop
1997 l_budget_group_id := c_WS_Rec.budget_group_id;
1998 l_global_worksheet_id := c_WS_Rec.global_worksheet_id;
1999 l_constraint_set_id := c_WS_Rec.constraint_set_id;
2000 l_budget_calendar_id := c_WS_Rec.budget_calendar_id;
2001 l_data_extract_id := c_WS_Rec.data_extract_id;
2002 l_budget_by_position := c_WS_Rec.budget_by_position;
2003 end loop;
2004
2005 for c_BG_Rec in c_BG loop
2006 l_flex_code := c_BG_Rec.chart_of_accounts_id;
2007 l_func_currency := c_BG_Rec.currency_code;
2008 l_business_group_id := c_BG_Rec.business_group_id;
2009 end loop;
2010
2011 for c_ConstSet_Rec in c_ConstSet loop
2012 l_cs_name := c_ConstSet_Rec.name;
2013 l_cs_threshold := c_ConstSet_Rec.constraint_threshold;
2014 end loop;
2015
2016 Initialize (p_worksheet_id => p_worksheet_id,
2017 p_return_status => l_return_status);
2018
2019 if nvl(l_constraint_set_id, FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM then
2020 begin
2021
2022 if l_budget_calendar_id <> nvl(PSB_WS_ACCT1.g_budget_calendar_id, FND_API.G_MISS_NUM) then
2023 begin
2024
2025 PSB_WS_ACCT1.Cache_Budget_Calendar
2026 (p_return_status => l_return_status,
2027 p_budget_calendar_id => l_budget_calendar_id);
2028
2029 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2030 raise FND_API.G_EXC_ERROR;
2031 end if;
2032
2033 end;
2034 end if;
2035
2036 Check_Reentrant_Status
2037 (p_return_status => l_return_status,
2038 p_worksheet_id => p_worksheet_id,
2039 p_parameter_set_id => g_parameter_set_id,
2040 p_constraint_set_id => g_constraint_set_id,
2041 p_allocrule_set_id => g_allocrule_set_id,
2042 p_budget_calendar_id => g_budget_calendar_id,
2043 p_budget_group_id => g_budget_group_id,
2044 p_data_extract_id => g_data_extract_id,
2045 p_gl_budget_set_id => g_gl_budget_set_id);
2046
2047 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2048 raise FND_API.G_EXC_ERROR;
2049 end if;
2050
2051 PSB_CONCURRENCY_CONTROL_PVT.Enforce_Concurrency_Control
2052 (p_api_version => 1.0 ,
2053 p_return_status => l_return_status,
2054 p_concurrency_class => 'WORKSHEET_CREATION',
2055 p_concurrency_entity_name => 'WORKSHEET',
2056 p_concurrency_entity_id => p_worksheet_id);
2057
2058 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2059 raise FND_API.G_EXC_ERROR;
2060 end if;
2061
2062 PSB_WS_ACCT1.Apply_Account_Constraints
2063 (p_return_status => l_return_status,
2064 p_validation_status => l_validation_status,
2065 p_worksheet_id => p_worksheet_id,
2066 p_flex_mapping_set_id => g_flex_mapping_set_id,
2067 p_budget_group_id => l_budget_group_id,
2068 p_flex_code => l_flex_code,
2069 p_func_currency => l_func_currency,
2070 p_constraint_set_id => l_constraint_set_id,
2071 p_constraint_set_name => l_cs_name,
2072 p_constraint_threshold => l_cs_threshold,
2073 p_budget_calendar_id => l_budget_calendar_id);
2074
2075 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2076 raise FND_API.G_EXC_ERROR;
2077 end if;
2078
2079 PSB_CONCURRENCY_CONTROL_PVT.Release_Concurrency_Control
2080 (p_api_version => 1.0 ,
2081 p_return_status => l_return_status,
2082 p_concurrency_class => 'WORKSHEET_CREATION',
2083 p_concurrency_entity_name => 'WORKSHEET',
2084 p_concurrency_entity_id => p_worksheet_id);
2085
2086 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2087 raise FND_API.G_EXC_ERROR;
2088 end if;
2089
2090 end;
2091 end if;
2092
2093
2094 -- Initialize API return status to success
2095
2096 p_return_status := FND_API.G_RET_STS_SUCCESS;
2097
2098
2099 EXCEPTION
2100
2101 when FND_API.G_EXC_ERROR then
2102 p_return_status := FND_API.G_RET_STS_ERROR;
2103
2104 PSB_CONCURRENCY_CONTROL_PVT.Release_Concurrency_Control
2105 (p_api_version => 1.0 ,
2106 p_return_status => l_return_status,
2107 p_concurrency_class => 'WORKSHEET_CREATION',
2108 p_concurrency_entity_name => 'WORKSHEET',
2109 p_concurrency_entity_id => p_worksheet_id);
2110
2111 when FND_API.G_EXC_UNEXPECTED_ERROR then
2112 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2113
2114 PSB_CONCURRENCY_CONTROL_PVT.Release_Concurrency_Control
2115 (p_api_version => 1.0 ,
2116 p_return_status => l_return_status,
2117 p_concurrency_class => 'WORKSHEET_CREATION',
2118 p_concurrency_entity_name => 'WORKSHEET',
2119 p_concurrency_entity_id => p_worksheet_id);
2120
2121 when OTHERS then
2122 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2123
2124 PSB_CONCURRENCY_CONTROL_PVT.Release_Concurrency_Control
2125 (p_api_version => 1.0 ,
2126 p_return_status => l_return_status,
2127 p_concurrency_class => 'WORKSHEET_CREATION',
2128 p_concurrency_entity_name => 'WORKSHEET',
2129 p_concurrency_entity_id => p_worksheet_id);
2130
2131 if FND_MSG_PUB.Check_Msg_Level
2132 (p_message_level => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) then
2133
2134 FND_MSG_PUB.Add_Exc_Msg
2135 (p_pkg_name => G_PKG_NAME,
2136 p_procedure_name => l_api_name);
2137
2138 end if;
2139
2140 END Apply_Acct_Constraints;
2141
2142 /* ----------------------------------------------------------------------- */
2143
2144 PROCEDURE Apply_Pos_Constraints
2145 ( p_api_version IN NUMBER,
2146 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_NONE,
2147 p_return_status OUT NOCOPY VARCHAR2,
2148 p_worksheet_id IN NUMBER
2149 ) IS
2150
2151 l_api_name CONSTANT VARCHAR2(30) := 'Apply_Pos_Constraints';
2152 l_api_version CONSTANT NUMBER := 1.0;
2153
2154 l_budget_group_id NUMBER;
2155 l_global_worksheet_id NUMBER;
2156 l_constraint_set_id NUMBER;
2157 l_budget_calendar_id NUMBER;
2158 l_data_extract_id NUMBER;
2159 l_business_group_id NUMBER;
2160 l_budget_by_position VARCHAR2(1);
2161
2162 l_flex_code NUMBER;
2163 l_func_currency VARCHAR2(10);
2164
2165 l_cs_name VARCHAR2(30);
2166 l_cs_threshold NUMBER;
2167
2168 l_return_status VARCHAR2(1);
2169 l_validation_status VARCHAR2(1);
2170
2171 l_msg_count NUMBER;
2172 l_msg_data VARCHAR2(2000);
2173
2174 cursor c_WS is
2175 select budget_group_id,
2176 nvl(global_worksheet_id, worksheet_id) global_worksheet_id,
2177 nvl(constraint_set_id, global_constraint_set_id) constraint_set_id,
2178 budget_calendar_id,
2179 nvl(data_extract_id, global_data_extract_id) data_extract_id,
2180 budget_by_position
2181 from PSB_WORKSHEETS_V
2182 where worksheet_id = p_worksheet_id;
2183
2184 cursor c_BG is
2185 select nvl(chart_of_accounts_id, root_chart_of_accounts_id) chart_of_accounts_id,
2186 nvl(currency_code, root_currency_code) currency_code,
2187 nvl(business_group_id, root_business_group_id) business_group_id
2188 from PSB_BUDGET_GROUPS_V
2189 where budget_group_id = l_budget_group_id;
2190
2191 cursor c_ConstSet is
2192 select name,
2193 constraint_threshold
2194 from PSB_CONSTRAINT_SETS_V
2195 where constraint_set_id = l_constraint_set_id;
2196
2197 BEGIN
2198
2199 -- Standard call to check for call compatibility.
2200
2201 if not FND_API.Compatible_API_Call (l_api_version,
2202 p_api_version,
2203 l_api_name,
2204 G_PKG_NAME)
2205 then
2206 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2207 end if;
2208
2209 for c_WS_Rec in c_WS loop
2210 l_budget_group_id := c_WS_Rec.budget_group_id;
2211 l_global_worksheet_id := c_WS_Rec.global_worksheet_id;
2212 l_constraint_set_id := c_WS_Rec.constraint_set_id;
2213 l_budget_calendar_id := c_WS_Rec.budget_calendar_id;
2214 l_data_extract_id := c_WS_Rec.data_extract_id;
2215 l_budget_by_position := c_WS_Rec.budget_by_position;
2216 end loop;
2217
2218 for c_BG_Rec in c_BG loop
2219 l_flex_code := c_BG_Rec.chart_of_accounts_id;
2220 l_func_currency := c_BG_Rec.currency_code;
2221 l_business_group_id := c_BG_Rec.business_group_id;
2222 end loop;
2223
2224 for c_ConstSet_Rec in c_ConstSet loop
2225 l_cs_name := c_ConstSet_Rec.name;
2226 l_cs_threshold := c_ConstSet_Rec.constraint_threshold;
2227 end loop;
2228
2229 if nvl(l_constraint_set_id, FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM then
2230 begin
2231
2232 if l_budget_calendar_id <> nvl(PSB_WS_ACCT1.g_budget_calendar_id, FND_API.G_MISS_NUM) then
2233 begin
2234
2235 PSB_WS_ACCT1.Cache_Budget_Calendar
2236 (p_return_status => l_return_status,
2237 p_budget_calendar_id => l_budget_calendar_id);
2238
2239 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2240 raise FND_API.G_EXC_ERROR;
2241 end if;
2242
2243 end;
2244 end if;
2245
2246 if l_budget_by_position = 'Y' then
2247 begin
2248
2249 Check_Reentrant_Status
2250 (p_return_status => l_return_status,
2251 p_worksheet_id => p_worksheet_id,
2252 p_parameter_set_id => g_parameter_set_id,
2253 p_constraint_set_id => g_constraint_set_id,
2254 p_allocrule_set_id => g_allocrule_set_id,
2255 p_budget_calendar_id => g_budget_calendar_id,
2256 p_budget_group_id => g_budget_group_id,
2257 p_data_extract_id => g_data_extract_id,
2258 p_gl_budget_set_id => g_gl_budget_set_id);
2259
2260 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2261 raise FND_API.G_EXC_ERROR;
2262 end if;
2263
2264 PSB_CONCURRENCY_CONTROL_PVT.Enforce_Concurrency_Control
2265 (p_api_version => 1.0 ,
2266 p_return_status => l_return_status,
2267 p_concurrency_class => 'WORKSHEET_CREATION',
2268 p_concurrency_entity_name => 'WORKSHEET',
2269 p_concurrency_entity_id => p_worksheet_id);
2270
2271 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2272 raise FND_API.G_EXC_ERROR;
2273 end if;
2274
2275 PSB_WS_POS3.Apply_Position_Constraints
2276 (p_return_status => l_return_status,
2277 p_validation_status => l_validation_status,
2278 p_worksheet_id => p_worksheet_id,
2279 p_budget_calendar_id => l_budget_calendar_id,
2280 p_data_extract_id => l_data_extract_id,
2281 p_business_group_id => l_business_group_id,
2282 p_func_currency => l_func_currency,
2283 p_constraint_set_id => l_constraint_set_id,
2284 p_constraint_set_name => l_cs_name,
2285 p_constraint_threshold => l_cs_threshold);
2286
2287 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2288 raise FND_API.G_EXC_ERROR;
2289 end if;
2290
2291 PSB_CONCURRENCY_CONTROL_PVT.Release_Concurrency_Control
2292 (p_api_version => 1.0 ,
2293 p_return_status => l_return_status,
2294 p_concurrency_class => 'WORKSHEET_CREATION',
2295 p_concurrency_entity_name => 'WORKSHEET',
2296 p_concurrency_entity_id => p_worksheet_id);
2297
2298 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2299 raise FND_API.G_EXC_ERROR;
2300 end if;
2301
2302 end;
2303 end if;
2304
2305 end;
2306 end if;
2307
2308
2309 -- Initialize API return status to success
2310
2311 p_return_status := FND_API.G_RET_STS_SUCCESS;
2312
2313
2314 EXCEPTION
2315
2316 when FND_API.G_EXC_ERROR then
2317 p_return_status := FND_API.G_RET_STS_ERROR;
2318
2319 PSB_CONCURRENCY_CONTROL_PVT.Release_Concurrency_Control
2320 (p_api_version => 1.0 ,
2321 p_return_status => l_return_status,
2322 p_concurrency_class => 'WORKSHEET_CREATION',
2323 p_concurrency_entity_name => 'WORKSHEET',
2324 p_concurrency_entity_id => p_worksheet_id);
2325
2326 when FND_API.G_EXC_UNEXPECTED_ERROR then
2327 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2328
2329 PSB_CONCURRENCY_CONTROL_PVT.Release_Concurrency_Control
2330 (p_api_version => 1.0 ,
2331 p_return_status => l_return_status,
2332 p_concurrency_class => 'WORKSHEET_CREATION',
2333 p_concurrency_entity_name => 'WORKSHEET',
2334 p_concurrency_entity_id => p_worksheet_id);
2335
2336 when OTHERS then
2337 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2338
2339 PSB_CONCURRENCY_CONTROL_PVT.Release_Concurrency_Control
2340 (p_api_version => 1.0 ,
2341 p_return_status => l_return_status,
2342 p_concurrency_class => 'WORKSHEET_CREATION',
2343 p_concurrency_entity_name => 'WORKSHEET',
2344 p_concurrency_entity_id => p_worksheet_id);
2345
2346 if FND_MSG_PUB.Check_Msg_Level
2347 (p_message_level => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) then
2348
2349 FND_MSG_PUB.Add_Exc_Msg
2350 (p_pkg_name => G_PKG_NAME,
2351 p_procedure_name => l_api_name);
2352
2353 end if;
2354
2355 END Apply_Pos_Constraints;
2356
2357 /* ----------------------------------------------------------------------- */
2358
2359 PROCEDURE Apply_Elem_Constraints
2360 ( p_api_version IN NUMBER,
2361 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_NONE,
2362 p_return_status OUT NOCOPY VARCHAR2,
2363 p_worksheet_id IN NUMBER
2364 ) IS
2365
2366 l_api_name CONSTANT VARCHAR2(30) := 'Apply_Elem_Constraints';
2367 l_api_version CONSTANT NUMBER := 1.0;
2368
2369 l_budget_group_id NUMBER;
2370 l_global_worksheet_id NUMBER;
2371 l_constraint_set_id NUMBER;
2372 l_budget_calendar_id NUMBER;
2373 l_data_extract_id NUMBER;
2374 l_business_group_id NUMBER;
2375 l_budget_by_position VARCHAR2(1);
2376
2377 l_flex_code NUMBER;
2378 l_func_currency VARCHAR2(10);
2379
2380 l_cs_name VARCHAR2(30);
2381 l_cs_threshold NUMBER;
2382
2383 l_return_status VARCHAR2(1);
2384
2385 l_msg_count NUMBER;
2386 l_msg_data VARCHAR2(2000);
2387
2388 cursor c_WS is
2389 select budget_group_id,
2390 nvl(global_worksheet_id, worksheet_id) global_worksheet_id,
2391 nvl(constraint_set_id, global_constraint_set_id) constraint_set_id,
2392 budget_calendar_id,
2393 nvl(data_extract_id, global_data_extract_id) data_extract_id,
2394 budget_by_position
2395 from PSB_WORKSHEETS_V
2396 where worksheet_id = p_worksheet_id;
2397
2398 cursor c_BG is
2399 select nvl(chart_of_accounts_id, root_chart_of_accounts_id) chart_of_accounts_id,
2400 nvl(currency_code, root_currency_code) currency_code,
2401 nvl(business_group_id, root_business_group_id) business_group_id
2402 from PSB_BUDGET_GROUPS_V
2403 where budget_group_id = l_budget_group_id;
2404
2405 cursor c_ConstSet is
2406 select name,
2407 constraint_threshold
2408 from PSB_CONSTRAINT_SETS_V
2409 where constraint_set_id = l_constraint_set_id;
2410
2411 BEGIN
2412
2413 -- Standard call to check for call compatibility.
2414
2415 if not FND_API.Compatible_API_Call (l_api_version,
2416 p_api_version,
2417 l_api_name,
2418 G_PKG_NAME)
2419 then
2420 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2421 end if;
2422
2423 for c_WS_Rec in c_WS loop
2424 l_budget_group_id := c_WS_Rec.budget_group_id;
2425 l_global_worksheet_id := c_WS_Rec.global_worksheet_id;
2426 l_constraint_set_id := c_WS_Rec.constraint_set_id;
2427 l_budget_calendar_id := c_WS_Rec.budget_calendar_id;
2428 l_data_extract_id := c_WS_Rec.data_extract_id;
2429 l_budget_by_position := c_WS_Rec.budget_by_position;
2430 end loop;
2431
2432 for c_BG_Rec in c_BG loop
2433 l_flex_code := c_BG_Rec.chart_of_accounts_id;
2434 l_func_currency := c_BG_Rec.currency_code;
2435 l_business_group_id := c_BG_Rec.business_group_id;
2436 end loop;
2437
2438 for c_ConstSet_Rec in c_ConstSet loop
2439 l_cs_name := c_ConstSet_Rec.name;
2440 l_cs_threshold := c_ConstSet_Rec.constraint_threshold;
2441 end loop;
2442
2443 Initialize (p_worksheet_id => p_worksheet_id,
2444 p_return_status => l_return_status);
2445
2446 if nvl(l_constraint_set_id, FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM then
2447 begin
2448
2449 if l_budget_calendar_id <> nvl(PSB_WS_ACCT1.g_budget_calendar_id, FND_API.G_MISS_NUM) then
2450 begin
2451
2452 PSB_WS_ACCT1.Cache_Budget_Calendar
2453 (p_return_status => l_return_status,
2454 p_budget_calendar_id => l_budget_calendar_id);
2455
2456 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2457 raise FND_API.G_EXC_ERROR;
2458 end if;
2459
2460 end;
2461 end if;
2462
2463 Check_Reentrant_Status
2464 (p_return_status => l_return_status,
2465 p_worksheet_id => p_worksheet_id,
2466 p_parameter_set_id => g_parameter_set_id,
2467 p_constraint_set_id => g_constraint_set_id,
2468 p_allocrule_set_id => g_allocrule_set_id,
2469 p_budget_calendar_id => g_budget_calendar_id,
2470 p_budget_group_id => g_budget_group_id,
2471 p_data_extract_id => g_data_extract_id,
2472 p_gl_budget_set_id => g_gl_budget_set_id);
2473
2474 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2475 raise FND_API.G_EXC_ERROR;
2476 end if;
2477
2478 PSB_CONCURRENCY_CONTROL_PVT.Enforce_Concurrency_Control
2479 (p_api_version => 1.0 ,
2480 p_return_status => l_return_status,
2481 p_concurrency_class => 'WORKSHEET_CREATION',
2482 p_concurrency_entity_name => 'WORKSHEET',
2483 p_concurrency_entity_id => p_worksheet_id);
2484
2485 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2486 raise FND_API.G_EXC_ERROR;
2487 end if;
2488
2489 PSB_WS_POS3.Apply_Element_Constraints
2490 (p_return_status => l_return_status,
2491 p_worksheet_id => l_global_worksheet_id,
2492 p_budget_calendar_id => l_budget_calendar_id,
2493 p_data_extract_id => l_data_extract_id,
2494 p_constraint_set_id => l_constraint_set_id,
2495 p_constraint_set_name => l_cs_name,
2496 p_constraint_threshold => l_cs_threshold);
2497
2498 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2499 raise FND_API.G_EXC_ERROR;
2500 end if;
2501
2502 PSB_CONCURRENCY_CONTROL_PVT.Release_Concurrency_Control
2503 (p_api_version => 1.0 ,
2504 p_return_status => l_return_status,
2505 p_concurrency_class => 'WORKSHEET_CREATION',
2506 p_concurrency_entity_name => 'WORKSHEET',
2507 p_concurrency_entity_id => p_worksheet_id);
2508
2509 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2510 raise FND_API.G_EXC_ERROR;
2511 end if;
2512
2513 end;
2514 end if;
2515
2516
2517 -- Initialize API return status to success
2518
2519 p_return_status := FND_API.G_RET_STS_SUCCESS;
2520
2521
2522 EXCEPTION
2523
2524 when FND_API.G_EXC_ERROR then
2525 p_return_status := FND_API.G_RET_STS_ERROR;
2526
2527 PSB_CONCURRENCY_CONTROL_PVT.Release_Concurrency_Control
2528 (p_api_version => 1.0 ,
2529 p_return_status => l_return_status,
2530 p_concurrency_class => 'WORKSHEET_CREATION',
2531 p_concurrency_entity_name => 'WORKSHEET',
2532 p_concurrency_entity_id => p_worksheet_id);
2533
2534 when FND_API.G_EXC_UNEXPECTED_ERROR then
2535 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2536
2537 PSB_CONCURRENCY_CONTROL_PVT.Release_Concurrency_Control
2538 (p_api_version => 1.0 ,
2539 p_return_status => l_return_status,
2540 p_concurrency_class => 'WORKSHEET_CREATION',
2541 p_concurrency_entity_name => 'WORKSHEET',
2542 p_concurrency_entity_id => p_worksheet_id);
2543
2544 when OTHERS then
2545 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2546
2547 PSB_CONCURRENCY_CONTROL_PVT.Release_Concurrency_Control
2548 (p_api_version => 1.0 ,
2549 p_return_status => l_return_status,
2550 p_concurrency_class => 'WORKSHEET_CREATION',
2551 p_concurrency_entity_name => 'WORKSHEET',
2552 p_concurrency_entity_id => p_worksheet_id);
2553
2554 if FND_MSG_PUB.Check_Msg_Level
2555 (p_message_level => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) then
2556
2557 FND_MSG_PUB.Add_Exc_Msg
2558 (p_pkg_name => G_PKG_NAME,
2559 p_procedure_name => l_api_name);
2560
2561 end if;
2562
2563 END Apply_Elem_Constraints;
2564
2565 /*------------------------------------------------------------------------------*/
2566
2567 PROCEDURE Post_Create_Line_Items
2568 ( p_api_version IN NUMBER,
2569 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_NONE,
2570 p_return_status OUT NOCOPY VARCHAR2,
2571 p_worksheet_id IN NUMBER
2572 ) IS
2573
2574 l_api_name CONSTANT VARCHAR2(30) := 'Post_Create_Line_Items';
2575 l_api_version CONSTANT NUMBER := 1.0;
2576
2577 l_return_status VARCHAR2(1);
2578
2579 BEGIN
2580
2581 -- Standard call to check for call compatibility.
2582
2583 if not FND_API.Compatible_API_Call (l_api_version,
2584 p_api_version,
2585 l_api_name,
2586 G_PKG_NAME)
2587 then
2588 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2589 end if;
2590
2591 PSB_WORKSHEET.Update_Worksheet
2592 (p_api_version => 1.0,
2593 p_return_status => l_return_status,
2594 p_worksheet_id => p_worksheet_id,
2595 p_ws_creation_complete => 'Y');
2596
2597 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2598 raise FND_API.G_EXC_ERROR;
2599 end if;
2600
2601
2602 -- Initialize API return status to success
2603
2604 p_return_status := FND_API.G_RET_STS_SUCCESS;
2605
2606
2607 EXCEPTION
2608
2609 when FND_API.G_EXC_ERROR then
2610 p_return_status := FND_API.G_RET_STS_ERROR;
2611
2612 when FND_API.G_EXC_UNEXPECTED_ERROR then
2613 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2614
2615 when OTHERS then
2616 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2617
2618 if FND_MSG_PUB.Check_Msg_Level
2619 (p_message_level => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) then
2620
2621 FND_MSG_PUB.Add_Exc_Msg
2622 (p_pkg_name => G_PKG_NAME,
2623 p_procedure_name => l_api_name);
2624
2625 end if;
2626
2627 END Post_Create_Line_Items;
2628
2629 /*------------------------------------------------------------------------------*/
2630
2631 PROCEDURE Delete_WS_Line_Items
2632 ( p_api_version IN NUMBER,
2633 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_NONE,
2634 p_return_status OUT NOCOPY VARCHAR2,
2635 p_worksheet_id IN NUMBER,
2636 p_global_worksheet IN VARCHAR2 := FND_API.G_TRUE
2637 ) IS
2638
2639 l_api_name CONSTANT VARCHAR2(30) := 'Delete_WS_Line_Items';
2640 l_api_version CONSTANT NUMBER := 1.0;
2641
2642 BEGIN
2643
2644 -- Standard call to check for call compatibility.
2645
2646 if not FND_API.Compatible_API_Call (l_api_version,
2647 p_api_version,
2648 l_api_name,
2649 G_PKG_NAME)
2650 then
2651 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2652 end if;
2653
2654 if FND_API.to_Boolean(p_global_worksheet) then
2655 begin
2656
2657 delete from PSB_POSITION_ASSIGNMENTS
2658 where worksheet_id = p_worksheet_id;
2659
2660 delete from PSB_PAY_ELEMENT_RATES
2661 where worksheet_id = p_worksheet_id;
2662
2663 delete from PSB_WS_POSITION_LINES
2664 where position_line_id in
2665 (select position_line_id
2666 from PSB_WS_LINES_POSITIONS
2667 where worksheet_id = p_worksheet_id);
2668
2669 delete from PSB_WS_FTE_LINES
2670 where position_line_id in
2671 (select position_line_id
2672 from PSB_WS_LINES_POSITIONS
2673 where worksheet_id = p_worksheet_id);
2674
2675 delete from PSB_WS_ELEMENT_LINES
2676 where position_line_id in
2677 (select position_line_id
2678 from PSB_WS_LINES_POSITIONS
2679 where worksheet_id = p_worksheet_id);
2680
2681 end;
2682 end if;
2683
2684 delete from PSB_WS_LINES_POSITIONS
2685 where worksheet_id = p_worksheet_id;
2686
2687 if FND_API.to_Boolean(p_global_worksheet) then
2688 begin
2689
2690 delete from PSB_WS_ACCOUNT_LINES
2691 where account_line_id in
2692 (select account_line_id
2693 from PSB_WS_LINES
2694 where worksheet_id = p_worksheet_id);
2695
2696 end;
2697 end if;
2698
2699 delete from PSB_WS_LINES
2700 where worksheet_id = p_worksheet_id;
2701
2702 delete from PSB_REENTRANT_PROCESS_STATUS
2703 where process_type = 'WORKSHEET_CREATION'
2704 and process_uid = p_worksheet_id;
2705
2706
2707 -- Initialize API return status to success
2708
2709 p_return_status := FND_API.G_RET_STS_SUCCESS;
2710
2711
2712 EXCEPTION
2713
2714 when FND_API.G_EXC_ERROR then
2715 p_return_status := FND_API.G_RET_STS_ERROR;
2716
2717 when FND_API.G_EXC_UNEXPECTED_ERROR then
2718 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2719
2720 when OTHERS then
2721 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2722
2723 if FND_MSG_PUB.Check_Msg_Level
2724 (p_message_level => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) then
2725
2726 FND_MSG_PUB.Add_Exc_Msg
2727 (p_pkg_name => G_PKG_NAME,
2728 p_procedure_name => l_api_name);
2729
2730 end if;
2731
2732 END Delete_WS_Line_Items;
2733
2734 /* ----------------------------------------------------------------------- */
2735
2736 PROCEDURE Create_Worksheet
2737 ( p_api_version IN NUMBER,
2738 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_NONE,
2739 p_return_status OUT NOCOPY VARCHAR2,
2740 p_budget_group_id IN NUMBER,
2741 p_budget_calendar_id IN NUMBER,
2742 p_worksheet_type IN VARCHAR2,
2743 p_name IN VARCHAR2,
2744 p_description IN VARCHAR2,
2745 p_ws_creation_complete IN VARCHAR2,
2746 p_stage_set_id IN NUMBER,
2747 p_current_stage_seq IN NUMBER,
2748 p_global_worksheet_id IN NUMBER,
2749 p_global_worksheet_flag IN VARCHAR2,
2750 p_global_worksheet_option IN VARCHAR2,
2751 p_local_copy_flag IN VARCHAR2,
2752 p_copy_of_worksheet_id IN NUMBER,
2753 p_freeze_flag IN VARCHAR2,
2754 p_budget_by_position IN VARCHAR2,
2755 p_use_revised_element_rates IN VARCHAR2,
2756 p_num_proposed_years IN NUMBER,
2757 p_num_years_to_allocate IN NUMBER,
2758 p_rounding_factor IN NUMBER,
2759 p_gl_cutoff_period IN DATE,
2760 p_budget_version_id IN NUMBER,
2761 p_gl_budget_set_id IN NUMBER,
2762 p_include_stat_balance IN VARCHAR2,
2763 p_include_trans_balance IN VARCHAR2,
2764 p_include_adj_period IN VARCHAR2,
2765 p_data_extract_id IN NUMBER,
2766 p_parameter_set_id IN NUMBER,
2767 p_constraint_set_id IN NUMBER,
2768 p_allocrule_set_id IN NUMBER,
2769 p_date_submitted IN DATE,
2770 p_submitted_by IN NUMBER,
2771 p_attribute1 IN VARCHAR2,
2772 p_attribute2 IN VARCHAR2,
2773 p_attribute3 IN VARCHAR2,
2774 p_attribute4 IN VARCHAR2,
2775 p_attribute5 IN VARCHAR2,
2776 p_attribute6 IN VARCHAR2,
2777 p_attribute7 IN VARCHAR2,
2778 p_attribute8 IN VARCHAR2,
2779 p_attribute9 IN VARCHAR2,
2780 p_attribute10 IN VARCHAR2,
2781 p_context IN VARCHAR2,
2782 p_create_non_pos_line_items IN VARCHAR2,
2783 p_apply_element_parameters IN VARCHAR2,
2784 p_apply_position_parameters IN VARCHAR2,
2785 p_create_positions IN VARCHAR2,
2786 p_create_summary_totals IN VARCHAR2,
2787 p_apply_constraints IN VARCHAR2,
2788 p_flex_mapping_set_id IN NUMBER,
2789 p_include_gl_commit_balance IN VARCHAR2,
2790 p_include_gl_oblig_balance IN VARCHAR2,
2791 p_include_gl_other_balance IN VARCHAR2,
2792 p_include_cbc_commit_balance IN VARCHAR2,
2793 p_include_cbc_oblig_balance IN VARCHAR2,
2794 p_include_cbc_budget_balance IN VARCHAR2,
2795 p_federal_ws_flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
2796 /* bug no 4725091 */
2797 p_include_gl_forwd_balance IN VARCHAR2,
2798 p_worksheet_id OUT NOCOPY NUMBER
2799 ) IS
2800
2801 l_api_name CONSTANT VARCHAR2(30) := 'Create_Worksheet';
2802 l_api_version CONSTANT NUMBER := 1.0;
2803
2804 l_worksheet_id NUMBER;
2805 l_worksheet_name VARCHAR2(80);
2806 l_userid NUMBER;
2807 l_loginid NUMBER;
2808 l_start_stage_seq NUMBER;
2809
2810 cursor c_Stage is
2811 select min(sequence_number) sequence_number
2812 from psb_budget_stages
2813 where budget_stage_set_id = p_stage_set_id;
2814
2815 cursor c_Seq is
2816 select psb_worksheets_s.nextval worksheet_id
2817 from dual;
2818
2819 cursor c_Budget_Group is
2820 select short_name
2821 from psb_budget_groups
2822 where budget_group_id = p_budget_group_id;
2823
2824 BEGIN
2825
2826 -- Standard call to check for call compatibility.
2827
2828 if not FND_API.Compatible_API_Call (l_api_version,
2829 p_api_version,
2830 l_api_name,
2831 G_PKG_NAME)
2832 then
2833 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2834 end if;
2835
2836 -- Get Who Values
2837
2838 l_userid := FND_GLOBAL.USER_ID;
2839 l_loginid := FND_GLOBAL.LOGIN_ID;
2840
2841 for c_Stage_Rec in c_Stage loop
2842 l_start_stage_seq := c_Stage_Rec.sequence_number;
2843 end loop;
2844
2845 for c_Seq_Rec in c_Seq loop
2846 l_worksheet_id := c_Seq_Rec.Worksheet_ID;
2847 end loop;
2848
2849 for c_Budget_Group_Rec in C_Budget_Group loop
2850 l_worksheet_name := c_Budget_Group_Rec.short_name || ' - '|| to_char(l_worksheet_id);
2851 end loop;
2852
2853 insert into PSB_WORKSHEETS
2854 (worksheet_id,
2855 budget_group_id,
2856 budget_calendar_id,
2857 worksheet_type,
2858 name,
2859 description,
2860 ws_creation_complete,
2861 stage_set_id,
2862 current_stage_seq,
2863 global_worksheet_id,
2864 global_worksheet_flag,
2865 global_worksheet_option,
2866 local_copy_flag,
2867 copy_of_worksheet_id,
2868 freeze_flag,
2869 budget_by_position,
2870 use_revised_element_rates,
2871 num_proposed_years,
2872 num_years_to_allocate,
2873 rounding_factor,
2874 gl_cutoff_period,
2875 budget_version_id,
2876 gl_budget_set_id,
2877 include_stat_balance,
2878 include_translated_balance,
2879 include_adjustment_periods,
2880 data_extract_id,
2881 parameter_set_id,
2882 constraint_set_id,
2883 allocrule_set_id,
2884 date_submitted,
2885 submitted_by,
2886 last_update_date,
2887 last_updated_by,
2888 last_update_login,
2889 created_by,
2890 creation_date,
2891 attribute1,
2892 attribute2,
2893 attribute3,
2894 attribute4,
2895 attribute5,
2896 attribute6,
2897 attribute7,
2898 attribute8,
2899 attribute9,
2900 attribute10,
2901 context,
2902 create_non_pos_line_items,
2903 apply_element_parameters,
2904 apply_position_parameters,
2905 create_positions,
2906 create_summary_totals,
2907 apply_constraints,
2908 flex_mapping_set_id,
2909 include_gl_commit_balance,
2910 include_gl_oblig_balance,
2911 include_gl_other_balance,
2912 include_cbc_commit_balance,
2913 include_cbc_oblig_balance,
2914 include_cbc_budget_balance,
2915 /* For Bug 3157960, added the federal ws flag */
2916 federal_ws_flag,
2917 /* bug no 4725091 */
2918 include_gl_forward_balance)
2919 values (l_worksheet_id,
2920 p_budget_group_id,
2921 p_budget_calendar_id,
2922 p_worksheet_type,
2923 decode(p_name, FND_API.G_MISS_CHAR, l_worksheet_name, null, l_worksheet_name, p_name),
2924 p_description,
2925 decode(p_ws_creation_complete, FND_API.G_MISS_CHAR, null, p_ws_creation_complete),
2926 p_stage_set_id,
2927 decode(p_current_stage_seq, FND_API.G_MISS_NUM, l_start_stage_seq, null, l_start_stage_seq, p_current_stage_seq),
2928 decode(p_global_worksheet_id, FND_API.G_MISS_NUM, null, p_global_worksheet_id),
2929 decode(p_global_worksheet_flag, FND_API.G_MISS_CHAR, null, p_global_worksheet_flag),
2930 decode(p_global_worksheet_option, FND_API.G_MISS_CHAR, null, p_global_worksheet_option),
2931 decode(p_local_copy_flag, FND_API.G_MISS_CHAR, null, p_local_copy_flag),
2932 decode(p_copy_of_worksheet_id, FND_API.G_MISS_NUM, null, p_copy_of_worksheet_id),
2933 decode(p_freeze_flag, FND_API.G_MISS_CHAR, null, p_freeze_flag),
2934 decode(p_budget_by_position, FND_API.G_MISS_CHAR, null, p_budget_by_position),
2935 decode(p_use_revised_element_rates, FND_API.G_MISS_CHAR, null, p_use_revised_element_rates),
2936 decode(p_num_proposed_years, FND_API.G_MISS_NUM, null, p_num_proposed_years),
2937 decode(p_num_years_to_allocate, FND_API.G_MISS_NUM, null, p_num_years_to_allocate),
2938 decode(p_rounding_factor, FND_API.G_MISS_NUM, null, p_rounding_factor),
2939 decode(p_gl_cutoff_period, FND_API.G_MISS_DATE, null, p_gl_cutoff_period),
2940 decode(p_budget_version_id, FND_API.G_MISS_NUM, null, p_budget_version_id),
2941 decode(p_gl_budget_set_id, FND_API.G_MISS_NUM, null, p_gl_budget_set_id),
2942 decode(p_include_stat_balance, FND_API.G_MISS_CHAR, null, p_include_stat_balance),
2943 decode(p_include_trans_balance, FND_API.G_MISS_CHAR, null, p_include_trans_balance),
2944 decode(p_include_adj_period, FND_API.G_MISS_CHAR, null, p_include_adj_period),
2945 decode(p_data_extract_id, FND_API.G_MISS_NUM, null, p_data_extract_id),
2946 decode(p_parameter_set_id, FND_API.G_MISS_NUM, null, p_parameter_set_id),
2947 decode(p_constraint_set_id, FND_API.G_MISS_NUM, null, p_constraint_set_id),
2948 decode(p_allocrule_set_id, FND_API.G_MISS_NUM, null, p_allocrule_set_id),
2949 decode(p_date_submitted, FND_API.G_MISS_DATE, null, p_date_submitted),
2950 decode(p_submitted_by, FND_API.G_MISS_NUM, null, p_submitted_by),
2951 sysdate,
2952 l_userid,
2953 l_loginid,
2954 l_userid,
2955 sysdate,
2956 decode(p_attribute1, FND_API.G_MISS_CHAR, null, p_attribute1),
2957 decode(p_attribute2, FND_API.G_MISS_CHAR, null, p_attribute2),
2958 decode(p_attribute3, FND_API.G_MISS_CHAR, null, p_attribute3),
2959 decode(p_attribute4, FND_API.G_MISS_CHAR, null, p_attribute4),
2960 decode(p_attribute5, FND_API.G_MISS_CHAR, null, p_attribute5),
2961 decode(p_attribute6, FND_API.G_MISS_CHAR, null, p_attribute6),
2962 decode(p_attribute7, FND_API.G_MISS_CHAR, null, p_attribute7),
2963 decode(p_attribute8, FND_API.G_MISS_CHAR, null, p_attribute8),
2964 decode(p_attribute9, FND_API.G_MISS_CHAR, null, p_attribute9),
2965 decode(p_attribute10, FND_API.G_MISS_CHAR, null, p_attribute10),
2966 decode(p_context, FND_API.G_MISS_CHAR, null, p_context),
2967 decode(p_create_non_pos_line_items, FND_API.G_MISS_CHAR, null, p_create_non_pos_line_items),
2968 decode(p_apply_element_parameters, FND_API.G_MISS_CHAR, null, p_apply_element_parameters),
2969 decode(p_apply_position_parameters, FND_API.G_MISS_CHAR, null, p_apply_position_parameters),
2970 decode(p_create_positions, FND_API.G_MISS_CHAR, null, p_create_positions),
2971 decode(p_create_summary_totals, FND_API.G_MISS_CHAR, null, p_create_summary_totals),
2972 decode(p_apply_constraints, FND_API.G_MISS_CHAR, null, p_apply_constraints),
2973 decode(p_flex_mapping_set_id, FND_API.G_MISS_NUM, null, p_flex_mapping_set_id),
2974 decode(p_include_gl_commit_balance, FND_API.G_MISS_CHAR, null, p_include_gl_commit_balance),
2975 decode(p_include_gl_oblig_balance, FND_API.G_MISS_CHAR, null, p_include_gl_oblig_balance),
2976 decode(p_include_gl_other_balance, FND_API.G_MISS_CHAR, null, p_include_gl_other_balance),
2977 decode(p_include_cbc_commit_balance, FND_API.G_MISS_CHAR, null, p_include_cbc_commit_balance),
2978 decode(p_include_cbc_oblig_balance, FND_API.G_MISS_CHAR, null, p_include_cbc_oblig_balance),
2979 decode(p_include_cbc_budget_balance, FND_API.G_MISS_CHAR, null, p_include_cbc_oblig_balance),
2980 decode(p_federal_ws_flag,FND_API.G_MISS_CHAR,null,p_federal_ws_flag),
2981 /* bug no 4725091 */
2982 decode(p_include_gl_forwd_balance,FND_API.G_MISS_CHAR, null, p_include_gl_forwd_balance)
2983 );
2984
2985 p_worksheet_id := l_worksheet_id;
2986
2987
2988 -- Initialize API return status to success
2989
2990 p_return_status := FND_API.G_RET_STS_SUCCESS;
2991
2992
2993 EXCEPTION
2994
2995 when FND_API.G_EXC_ERROR then
2996 p_return_status := FND_API.G_RET_STS_ERROR;
2997
2998 when FND_API.G_EXC_UNEXPECTED_ERROR then
2999 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3000
3001 when OTHERS then
3002 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3003
3004 if FND_MSG_PUB.Check_Msg_Level
3005 (p_message_level => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3006 then
3007 FND_MSG_PUB.Add_Exc_Msg
3008 (p_pkg_name => G_PKG_NAME,
3009 p_procedure_name => l_api_name);
3010 end if;
3011
3012 END Create_Worksheet;
3013
3014 /* ----------------------------------------------------------------------- */
3015
3016 PROCEDURE Update_Worksheet
3017 ( p_api_version IN NUMBER,
3018 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_NONE,
3019 p_return_status OUT NOCOPY VARCHAR2,
3020 p_worksheet_id IN NUMBER := FND_API.G_MISS_NUM,
3021 p_worksheet_type IN VARCHAR2 := FND_API.G_MISS_CHAR,
3022 p_description IN VARCHAR2 := FND_API.G_MISS_CHAR,
3023 p_ws_creation_complete IN VARCHAR2 := FND_API.G_MISS_CHAR,
3024 p_global_worksheet_id IN NUMBER := FND_API.G_MISS_NUM,
3025 p_current_stage_seq IN NUMBER := FND_API.G_MISS_NUM,
3026 p_local_copy_flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
3027 p_copy_of_worksheet_id IN NUMBER := FND_API.G_MISS_NUM,
3028 p_freeze_flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
3029 p_use_revised_element_rates IN VARCHAR2 := FND_API.G_MISS_CHAR,
3030 /* Bug # 3083970 */
3031 p_num_proposed_years IN NUMBER := FND_API.G_MISS_NUM,
3032 p_rounding_factor IN NUMBER := FND_API.G_MISS_NUM,
3033 /* End bug */
3034 p_date_submitted IN DATE := FND_API.G_MISS_DATE,
3035 p_submitted_by IN NUMBER := FND_API.G_MISS_NUM,
3036 p_attribute1 IN VARCHAR2 := FND_API.G_MISS_CHAR,
3037 p_attribute2 IN VARCHAR2 := FND_API.G_MISS_CHAR,
3038 p_attribute3 IN VARCHAR2 := FND_API.G_MISS_CHAR,
3039 p_attribute4 IN VARCHAR2 := FND_API.G_MISS_CHAR,
3040 p_attribute5 IN VARCHAR2 := FND_API.G_MISS_CHAR,
3041 p_attribute6 IN VARCHAR2 := FND_API.G_MISS_CHAR,
3042 p_attribute7 IN VARCHAR2 := FND_API.G_MISS_CHAR,
3043 p_attribute8 IN VARCHAR2 := FND_API.G_MISS_CHAR,
3044 p_attribute9 IN VARCHAR2 := FND_API.G_MISS_CHAR,
3045 p_attribute10 IN VARCHAR2 := FND_API.G_MISS_CHAR,
3046 p_context IN VARCHAR2 := FND_API.G_MISS_CHAR,
3047 p_create_non_pos_line_items IN VARCHAR2 := FND_API.G_MISS_CHAR,
3048 p_apply_element_parameters IN VARCHAR2 := FND_API.G_MISS_CHAR,
3049 p_apply_position_parameters IN VARCHAR2 := FND_API.G_MISS_CHAR,
3050 p_create_positions IN VARCHAR2 := FND_API.G_MISS_CHAR,
3051 p_create_summary_totals IN VARCHAR2 := FND_API.G_MISS_CHAR,
3052 p_apply_constraints IN VARCHAR2 := FND_API.G_MISS_CHAR,
3053 p_include_gl_commit_balance IN VARCHAR2 := FND_API.G_MISS_CHAR,
3054 p_include_gl_oblig_balance IN VARCHAR2 := FND_API.G_MISS_CHAR,
3055 p_include_gl_other_balance IN VARCHAR2 := FND_API.G_MISS_CHAR,
3056 p_include_cbc_commit_balance IN VARCHAR2 := FND_API.G_MISS_CHAR,
3057 p_include_cbc_oblig_balance IN VARCHAR2 := FND_API.G_MISS_CHAR,
3058 p_include_cbc_budget_balance IN VARCHAR2 := FND_API.G_MISS_CHAR,
3059 /* For Bug No. 2312657 : Start */
3060 p_gl_cutoff_period IN DATE := NULL,
3061 p_gl_budget_set_id IN NUMBER := NULL,
3062 /* For Bug No. 2312657 : End */
3063 p_federal_ws_flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
3064 /* bug no 4725091 */
3065 p_include_gl_forwd_balance IN VARCHAR2 := FND_API.G_MISS_CHAR
3066 ) IS
3067
3068 l_api_name CONSTANT VARCHAR2(30) := 'Update_Worksheet';
3069 l_api_version CONSTANT NUMBER := 1.0;
3070
3071 l_userid NUMBER;
3072 l_loginid NUMBER;
3073
3074 /* BUG 3239307 Start */
3075 l_gl_cutoff_period DATE;
3076 /* BUG 3239307 End */
3077
3078 BEGIN
3079
3080 -- Standard call to check for call compatibility.
3081
3082 if not FND_API.Compatible_API_Call (l_api_version,
3083 p_api_version,
3084 l_api_name,
3085 G_PKG_NAME)
3086 then
3087 raise FND_API.G_EXC_UNEXPECTED_ERROR;
3088 end if;
3089
3090 -- Get Who Values
3091
3092 l_userid := FND_GLOBAL.USER_ID;
3093 l_loginid := FND_GLOBAL.LOGIN_ID;
3094
3095 /* BUG 3239307 Start */
3096 select gl_cutoff_period
3097 into l_gl_cutoff_period
3098 from psb_worksheets
3099 where worksheet_id = p_worksheet_id;
3100 /* BUG 3239307 End */
3101
3102 update PSB_WORKSHEETS
3103 set worksheet_type = decode(p_worksheet_type, FND_API.G_MISS_CHAR, worksheet_type, p_worksheet_type),
3104 description = decode(p_description, FND_API.G_MISS_CHAR, description, p_description),
3105 ws_creation_complete = decode(p_ws_creation_complete, FND_API.G_MISS_CHAR, ws_creation_complete, p_ws_creation_complete),
3106 global_worksheet_id = decode(p_global_worksheet_id, FND_API.G_MISS_NUM, global_worksheet_id, p_global_worksheet_id),
3107 current_stage_seq = decode(p_current_stage_seq, FND_API.G_MISS_NUM, current_stage_seq, p_current_stage_seq),
3108 local_copy_flag = decode(p_local_copy_flag, FND_API.G_MISS_CHAR, local_copy_flag, p_local_copy_flag),
3109 copy_of_worksheet_id = decode(p_copy_of_worksheet_id, FND_API.G_MISS_NUM, copy_of_worksheet_id, p_copy_of_worksheet_id),
3110 freeze_flag = decode(p_freeze_flag, FND_API.G_MISS_CHAR, freeze_flag, p_freeze_flag),
3111 use_revised_element_rates = decode(p_use_revised_element_rates, FND_API.G_MISS_CHAR, use_revised_element_rates, p_use_revised_element_rates),
3112 /* Bug # 3083970 */
3113 num_proposed_years = decode(p_num_proposed_years, FND_API.G_MISS_NUM, num_proposed_years, p_num_proposed_years),
3114 rounding_factor = decode(p_rounding_factor, FND_API.G_MISS_NUM, rounding_factor, p_rounding_factor),
3115 /* End Bug # 3083970 */
3116 date_submitted = decode(p_date_submitted, FND_API.G_MISS_DATE, date_submitted, p_date_submitted),
3117 submitted_by = decode(p_submitted_by, FND_API.G_MISS_NUM, submitted_by, p_submitted_by),
3118 last_update_date = sysdate,
3119 last_updated_by = l_userid,
3120 last_update_login = l_loginid,
3121 attribute1 = decode(p_attribute1, FND_API.G_MISS_CHAR, attribute1, p_attribute1),
3122 attribute2 = decode(p_attribute2, FND_API.G_MISS_CHAR, attribute2, p_attribute2),
3123 attribute3 = decode(p_attribute3, FND_API.G_MISS_CHAR, attribute3, p_attribute3),
3124 attribute4 = decode(p_attribute4, FND_API.G_MISS_CHAR, attribute4, p_attribute4),
3125 attribute5 = decode(p_attribute5, FND_API.G_MISS_CHAR, attribute5, p_attribute5),
3126 attribute6 = decode(p_attribute6, FND_API.G_MISS_CHAR, attribute6, p_attribute6),
3127 attribute7 = decode(p_attribute7, FND_API.G_MISS_CHAR, attribute7, p_attribute7),
3128 attribute8 = decode(p_attribute8, FND_API.G_MISS_CHAR, attribute8, p_attribute8),
3129 attribute9 = decode(p_attribute9, FND_API.G_MISS_CHAR, attribute9, p_attribute9),
3130 attribute10 = decode(p_attribute10, FND_API.G_MISS_CHAR, attribute10, p_attribute10),
3131 context = decode(p_context, FND_API.G_MISS_CHAR, context, p_context),
3132 create_non_pos_line_items = decode(p_create_non_pos_line_items, FND_API.G_MISS_CHAR, create_non_pos_line_items, p_create_non_pos_line_items),
3133 apply_element_parameters = decode(p_apply_element_parameters, FND_API.G_MISS_CHAR, apply_element_parameters, p_apply_element_parameters),
3134 apply_position_parameters = decode(p_apply_position_parameters, FND_API.G_MISS_CHAR, apply_position_parameters, p_apply_position_parameters),
3135 create_positions = decode(p_create_positions, FND_API.G_MISS_CHAR, create_positions, p_create_positions),
3136 create_summary_totals = decode(p_create_summary_totals, FND_API.G_MISS_CHAR, create_summary_totals, p_create_summary_totals),
3137 apply_constraints = decode(p_apply_constraints, FND_API.G_MISS_CHAR, apply_constraints, p_apply_constraints),
3138 include_gl_commit_balance = decode(p_include_gl_commit_balance, FND_API.G_MISS_CHAR, include_gl_commit_balance, p_include_gl_commit_balance),
3139 include_gl_oblig_balance = decode(p_include_gl_oblig_balance, FND_API.G_MISS_CHAR, include_gl_oblig_balance, p_include_gl_oblig_balance),
3140 include_gl_other_balance = decode(p_include_gl_other_balance, FND_API.G_MISS_CHAR, include_gl_other_balance, p_include_gl_other_balance),
3141 include_cbc_commit_balance = decode(p_include_cbc_commit_balance, FND_API.G_MISS_CHAR, include_cbc_commit_balance, p_include_cbc_commit_balance),
3142 include_cbc_oblig_balance = decode(p_include_cbc_oblig_balance, FND_API.G_MISS_CHAR, include_cbc_oblig_balance, p_include_cbc_oblig_balance),
3143 include_cbc_budget_balance = decode(p_include_cbc_budget_balance, FND_API.G_MISS_CHAR, include_cbc_budget_balance, p_include_cbc_budget_balance),
3144 /* For Bug No. 2312657 : Start */
3145 gl_cutoff_period = decode(p_gl_cutoff_period, NULL, gl_cutoff_period, p_gl_cutoff_period),
3146 gl_budget_set_id = decode(p_gl_budget_set_id, NULL, gl_budget_set_id, p_gl_budget_set_id),
3147 /* For Bug No. 2312657 : End */
3148 /* For Bug 3157960, added the federal ws flag */
3149 federal_Ws_flag = decode(p_federal_ws_flag,FND_API.G_MISS_CHAR,federal_Ws_flag,p_federal_ws_flag),
3150 /* bug no 4725091 */
3151 include_gl_forward_balance = decode(p_include_gl_forwd_balance, FND_API.G_MISS_CHAR, include_gl_forward_balance, p_include_gl_forwd_balance)
3152 where worksheet_id = p_worksheet_id;
3153
3154 /* BUG 3239307 Start */
3155 if (NVL(p_gl_cutoff_period, to_date('31-12-4712', 'dd-mm-yyyy')) <>
3156 NVL(l_gl_cutoff_period, to_date('31-12-4712', 'dd-mm-yyyy')))
3157 then
3158 update psb_worksheets
3159 set gl_cutoff_period = p_gl_cutoff_period
3160 where global_worksheet_id = p_worksheet_id;
3161 end if;
3162 /* BUG 3239307 End */
3163
3164 -- Initialize API return status to success
3165
3166 p_return_status := FND_API.G_RET_STS_SUCCESS;
3167
3168
3169 EXCEPTION
3170
3171 when FND_API.G_EXC_ERROR then
3172 p_return_status := FND_API.G_RET_STS_ERROR;
3173
3174 when FND_API.G_EXC_UNEXPECTED_ERROR then
3175 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3176
3177 when OTHERS then
3178 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3179
3180 if FND_MSG_PUB.Check_Msg_Level
3181 (p_message_level => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3182 then
3183 FND_MSG_PUB.Add_Exc_Msg
3184 (p_pkg_name => G_PKG_NAME,
3185 p_procedure_name => l_api_name);
3186 end if;
3187
3188 END Update_Worksheet;
3189
3190 /* ----------------------------------------------------------------------- */
3191
3192 PROCEDURE Delete_Worksheet
3193 ( p_api_version IN NUMBER,
3194 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_NONE,
3195 p_return_status OUT NOCOPY VARCHAR2,
3196 p_worksheet_id IN NUMBER
3197 ) IS
3198
3199 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Worksheet';
3200 l_api_version CONSTANT NUMBER := 1.0;
3201
3202 BEGIN
3203
3204 -- Standard call to check for call compatibility.
3205
3206 if not FND_API.Compatible_API_Call (l_api_version,
3207 p_api_version,
3208 l_api_name,
3209 G_PKG_NAME)
3210 then
3211 raise FND_API.G_EXC_UNEXPECTED_ERROR;
3212 end if;
3213
3214 delete from PSB_WORKSHEETS
3215 where worksheet_id = p_worksheet_id;
3216
3217
3218 -- Initialize API return status to success
3219
3220 p_return_status := FND_API.G_RET_STS_SUCCESS;
3221
3222
3223 EXCEPTION
3224
3225 when FND_API.G_EXC_ERROR then
3226 p_return_status := FND_API.G_RET_STS_ERROR;
3227
3228 when FND_API.G_EXC_UNEXPECTED_ERROR then
3229 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3230
3231 when OTHERS then
3232 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3233
3234 if FND_MSG_PUB.Check_Msg_Level
3235 (p_message_level => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3236 then
3237 FND_MSG_PUB.Add_Exc_Msg
3238 (p_pkg_name => G_PKG_NAME,
3239 p_procedure_name => l_api_name);
3240 end if;
3241
3242 END Delete_Worksheet;
3243
3244 /* ----------------------------------------------------------------------- */
3245
3246 PROCEDURE Delete_WAL
3247 ( p_api_version IN NUMBER,
3248 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_NONE,
3249 p_return_status OUT NOCOPY VARCHAR2,
3250 p_account_line_id IN NUMBER
3251 ) IS
3252
3253 l_api_name CONSTANT VARCHAR2(30) := 'Delete_WAL';
3254 l_api_version CONSTANT NUMBER := 1.0;
3255
3256 BEGIN
3257
3258 -- Standard call to check for call compatibility.
3259
3260 if not FND_API.Compatible_API_Call (l_api_version,
3261 p_api_version,
3262 l_api_name,
3263 G_PKG_NAME)
3264 then
3265 raise FND_API.G_EXC_UNEXPECTED_ERROR;
3266 end if;
3267
3268 delete from PSB_WS_ACCOUNT_LINES
3269 where account_line_id = p_account_line_id;
3270
3271 --delete from PSB_WS_LINES a
3272 -- where exists
3273 -- (select 1
3274 -- from PSB_WS_ACCOUNT_LINES b
3275 -- where b.copy_of_account_line_id = p_account_line_id
3276 -- and b.account_line_id = a.account_line_id);
3277
3278 --delete from PSB_WS_ACCOUNT_LINES
3279 -- where copy_of_account_line_id = p_account_line_id;
3280
3281 delete from PSB_WS_LINES
3282 where account_line_id = p_account_line_id;
3283
3284
3285 -- Initialize API return status to success
3286
3287 p_return_status := FND_API.G_RET_STS_SUCCESS;
3288
3289
3290 EXCEPTION
3291
3292 when FND_API.G_EXC_ERROR then
3293 p_return_status := FND_API.G_RET_STS_ERROR;
3294
3295 when FND_API.G_EXC_UNEXPECTED_ERROR then
3296 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3297
3298 when OTHERS then
3299 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3300
3301 if FND_MSG_PUB.Check_Msg_Level
3302 (p_message_level => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3303 then
3304 FND_MSG_PUB.Add_Exc_Msg
3305 (p_pkg_name => G_PKG_NAME,
3306 p_procedure_name => l_api_name);
3307 end if;
3308
3309 END Delete_WAL;
3310
3311 /* ----------------------------------------------------------------------- */
3312
3313 PROCEDURE Delete_WPL
3314 ( p_api_version IN NUMBER,
3315 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_NONE,
3316 p_return_status OUT NOCOPY VARCHAR2,
3317 p_worksheet_id IN NUMBER,
3318 p_position_line_id IN NUMBER
3319 ) IS
3320
3321 l_api_name CONSTANT VARCHAR2(30) := 'Delete_WPL';
3322 l_api_version CONSTANT NUMBER := 1.0;
3323
3324 BEGIN
3325
3326 -- Standard call to check for call compatibility.
3327
3328 if not FND_API.Compatible_API_Call (l_api_version,
3329 p_api_version,
3330 l_api_name,
3331 G_PKG_NAME)
3332 then
3333 raise FND_API.G_EXC_UNEXPECTED_ERROR;
3334 end if;
3335
3336 delete from PSB_WS_FTE_LINES
3337 where position_line_id = p_position_line_id;
3338
3339 delete from PSB_WS_ELEMENT_LINES
3340 where position_line_id = p_position_line_id;
3341
3342 delete from PSB_WS_LINES
3343 where account_line_id in
3344 (select account_line_id
3345 from PSB_WS_ACCOUNT_LINES
3346 where element_set_id is not null
3347 and position_line_id = p_position_line_id);
3348
3349 delete from PSB_WS_ACCOUNT_LINES
3350 where position_line_id = p_position_line_id;
3351
3352 delete from PSB_WS_LINES_POSITIONS
3353 where position_line_id = p_position_line_id;
3354
3355 delete from PSB_POSITION_ASSIGNMENTS
3356 where position_id =
3357 (select position_id
3358 from PSB_WS_POSITION_LINES
3359 where position_line_id = p_position_line_id)
3360 and worksheet_id =
3361 (select nvl(global_worksheet_id, worksheet_id)
3362 from PSB_WORKSHEETS
3363 where worksheet_id = p_worksheet_id);
3364
3365 delete from PSB_WS_POSITION_LINES
3366 where position_line_id = p_position_line_id;
3367
3368 -- Initialize API return status to success
3369
3370 p_return_status := FND_API.G_RET_STS_SUCCESS;
3371
3372
3373 EXCEPTION
3374
3375 when FND_API.G_EXC_ERROR then
3376 p_return_status := FND_API.G_RET_STS_ERROR;
3377
3378 when FND_API.G_EXC_UNEXPECTED_ERROR then
3379 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3380
3381 when OTHERS then
3382 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3383
3384 if FND_MSG_PUB.Check_Msg_Level
3385 (p_message_level => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3386 then
3387 FND_MSG_PUB.Add_Exc_Msg
3388 (p_pkg_name => G_PKG_NAME,
3389 p_procedure_name => l_api_name);
3390 end if;
3391
3392 END Delete_WPL;
3393
3394 /* ----------------------------------------------------------------------- */
3395
3396 PROCEDURE Delete_WFL
3397 ( p_api_version IN NUMBER,
3398 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_NONE,
3399 p_return_status OUT NOCOPY VARCHAR2,
3400 p_fte_line_id IN NUMBER
3401 ) IS
3402
3403 l_api_name CONSTANT VARCHAR2(30) := 'Delete_WFL';
3404 l_api_version CONSTANT NUMBER := 1.0;
3405
3406 BEGIN
3407
3408 -- Standard call to check for call compatibility.
3409
3410 if not FND_API.Compatible_API_Call (l_api_version,
3411 p_api_version,
3412 l_api_name,
3413 G_PKG_NAME)
3414 then
3415 raise FND_API.G_EXC_UNEXPECTED_ERROR;
3416 end if;
3417
3418 delete from PSB_WS_FTE_LINES
3419 where fte_line_id = p_fte_line_id;
3420
3421
3422 -- Initialize API return status to success
3423
3424 p_return_status := FND_API.G_RET_STS_SUCCESS;
3425
3426
3427 EXCEPTION
3428
3429 when FND_API.G_EXC_ERROR then
3430 p_return_status := FND_API.G_RET_STS_ERROR;
3431
3432 when FND_API.G_EXC_UNEXPECTED_ERROR then
3433 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3434
3435 when OTHERS then
3436 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3437
3438 if FND_MSG_PUB.Check_Msg_Level
3439 (p_message_level => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3440 then
3441 FND_MSG_PUB.Add_Exc_Msg
3442 (p_pkg_name => G_PKG_NAME,
3443 p_procedure_name => l_api_name);
3444 end if;
3445
3446 END Delete_WFL;
3447
3448 /* ----------------------------------------------------------------------- */
3449
3450 PROCEDURE Delete_WEL
3451 ( p_api_version IN NUMBER,
3452 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_NONE,
3453 p_return_status OUT NOCOPY VARCHAR2,
3454 p_element_line_id IN NUMBER
3455 ) IS
3456
3457 l_api_name CONSTANT VARCHAR2(30) := 'Delete_WEL';
3458 l_api_version CONSTANT NUMBER := 1.0;
3459
3460 BEGIN
3461
3462 -- Standard call to check for call compatibility.
3463
3464 if not FND_API.Compatible_API_Call (l_api_version,
3465 p_api_version,
3466 l_api_name,
3467 G_PKG_NAME)
3468 then
3469 raise FND_API.G_EXC_UNEXPECTED_ERROR;
3470 end if;
3471
3472 delete from PSB_WS_ELEMENT_LINES
3473 where element_line_id = p_element_line_id;
3474
3475
3476 -- Initialize API return status to success
3477
3478 p_return_status := FND_API.G_RET_STS_SUCCESS;
3479
3480
3481 EXCEPTION
3482
3483 when FND_API.G_EXC_ERROR then
3484 p_return_status := FND_API.G_RET_STS_ERROR;
3485
3486 when FND_API.G_EXC_UNEXPECTED_ERROR then
3487 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3488
3489 when OTHERS then
3490 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3491
3492 if FND_MSG_PUB.Check_Msg_Level
3493 (p_message_level => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3494 then
3495 FND_MSG_PUB.Add_Exc_Msg
3496 (p_pkg_name => G_PKG_NAME,
3497 p_procedure_name => l_api_name);
3498 end if;
3499
3500 END Delete_WEL;
3501
3502 /* ----------------------------------------------------------------------- */
3503
3504 PROCEDURE Delete_Summary_Lines
3505 ( p_api_version IN NUMBER,
3506 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_NONE,
3507 p_return_status OUT NOCOPY VARCHAR2,
3508 p_worksheet_id IN NUMBER
3509 ) IS
3510
3511 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Summary_Lines';
3512 l_api_version CONSTANT NUMBER := 1.0;
3513
3514 BEGIN
3515
3516 -- Standard call to check for call compatibility.
3517
3518 if not FND_API.Compatible_API_Call (l_api_version,
3519 p_api_version,
3520 l_api_name,
3521 G_PKG_NAME)
3522 then
3523 raise FND_API.G_EXC_UNEXPECTED_ERROR;
3524 end if;
3525
3526 delete from PSB_WS_ACCOUNT_LINES
3527 where template_id is not null
3528 and account_line_id in
3529 (select account_line_id
3530 from PSB_WS_LINES
3531 where worksheet_id = p_worksheet_id);
3532
3533 delete from PSB_WS_LINES a
3534 where a.worksheet_id = p_worksheet_id
3535 and not exists
3536 (select 1
3537 from PSB_WS_ACCOUNT_LINES b
3538 where b.account_line_id = a.account_line_id);
3539
3540
3541 -- Initialize API return status to success
3542
3543 p_return_status := FND_API.G_RET_STS_SUCCESS;
3544
3545
3546 EXCEPTION
3547
3548 when FND_API.G_EXC_ERROR then
3549 p_return_status := FND_API.G_RET_STS_ERROR;
3550
3551 when FND_API.G_EXC_UNEXPECTED_ERROR then
3552 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3553
3554 when OTHERS then
3555 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3556
3557 if FND_MSG_PUB.Check_Msg_Level
3558 (p_message_level => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3559 then
3560 FND_MSG_PUB.Add_Exc_Msg
3561 (p_pkg_name => G_PKG_NAME,
3562 p_procedure_name => l_api_name);
3563 end if;
3564
3565 END Delete_Summary_Lines;
3566
3567 /* ----------------------------------------------------------------------- */
3568
3569 PROCEDURE Initialize
3570 ( p_worksheet_id IN NUMBER,
3571 p_return_status OUT NOCOPY VARCHAR2
3572 ) IS
3573
3574 l_return_status VARCHAR2(1);
3575 l_msg_count NUMBER;
3576 l_msg_data VARCHAR2(2000);
3577
3578 BEGIN
3579
3580 Cache_Worksheet_Variables
3581 (p_worksheet_id => p_worksheet_id,
3582 p_return_status => l_return_status);
3583
3584 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
3585 raise FND_API.G_EXC_ERROR;
3586 end if;
3587
3588 PSB_BUDGET_GROUPS_PVT.Check_Budget_Group_Freeze
3589 (p_api_version => 1.0,
3590 p_budget_group_id => g_budget_group_id,
3591 p_return_status => l_return_status,
3592 p_msg_count => l_msg_count,
3593 p_msg_data => l_msg_data);
3594
3595 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
3596 message_token('ROOT_BUDGET_GROUP', g_budget_group_name);
3597 add_message('PSB', 'PSB_FREEZE_BG_HIERARCHY');
3598 raise FND_API.G_EXC_ERROR;
3599 end if;
3600
3601 if FND_API.to_Boolean(g_budget_by_position) then
3602 begin
3603
3604 Check_DataExt_Completion
3605 (p_return_status => l_return_status,
3606 p_data_extract_id => g_data_extract_id);
3607
3608 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
3609 message_token('DATA_EXTRACT', g_data_extract_name);
3610 add_message('PSB', 'PSB_DATA_EXTRACT_INCOMPLETE');
3611 raise FND_API.G_EXC_ERROR;
3612 end if;
3613
3614 end;
3615 end if;
3616
3617
3618 EXCEPTION
3619
3620 when FND_API.G_EXC_ERROR then
3621 p_return_status := FND_API.G_RET_STS_ERROR;
3622
3623 END Initialize;
3624
3625 /* ----------------------------------------------------------------------- */
3626
3627 PROCEDURE Cache_Worksheet_Variables
3628 ( p_worksheet_id IN NUMBER,
3629 p_return_status OUT NOCOPY VARCHAR2
3630 ) IS
3631
3632 l_new_base_sp VARCHAR2(1) := FND_API.G_TRUE;
3633 l_name VARCHAR2(2000);
3634
3635 l_userid NUMBER;
3636 l_loginid NUMBER;
3637
3638 cursor c_WS is
3639 select budget_group_id,
3640 budget_calendar_id,
3641 nvl(parameter_set_id, global_parameter_set_id) parameter_set_id,
3642 nvl(constraint_set_id, global_constraint_set_id) constraint_set_id,
3643 nvl(allocrule_set_id, global_allocrule_set_id) allocrule_set_id,
3644 nvl(data_extract_id, global_data_extract_id) data_extract_id,
3645 data_extract_name,
3646 nvl(global_worksheet_id, worksheet_id) global_worksheet_id,
3647 local_copy_flag,
3648 global_worksheet_flag,
3649 global_worksheet_option,
3650 rounding_factor,
3651 budget_version_id,
3652 gl_budget_set_id,
3653 gl_cutoff_period,
3654 budget_by_position,
3655 use_revised_element_rates,
3656 num_proposed_years,
3657 num_years_to_allocate,
3658 stage_set_id,
3659 current_stage_seq,
3660 include_stat_balance,
3661 include_translated_balance,
3662 include_adjustment_periods,
3663 create_non_pos_line_items,
3664 apply_element_parameters,
3665 apply_position_parameters,
3666 create_positions,
3667 create_summary_totals,
3668 apply_constraints,
3669 flex_mapping_set_id,
3670 include_gl_commit_balance,
3671 include_gl_oblig_balance,
3672 include_gl_other_balance,
3673 include_cbc_commit_balance,
3674 include_cbc_oblig_balance,
3675 include_cbc_budget_balance,
3676 /* Bug No 4725091 */
3677 include_gl_forward_balance
3678 from PSB_WORKSHEETS_V
3679 where worksheet_id = p_worksheet_id;
3680
3681 -- Bug 3543845: Add root_budget_greoup, ps_account_position_set_id, and
3682 -- nps_account_position_set_id for caching purposes.
3683 cursor c_BG is
3684 select nvl(root_budget_group_id, budget_group_id) root_budget_group_id,
3685 nvl(set_of_books_id, root_set_of_books_id) set_of_books_id,
3686 nvl(business_group_id, root_business_group_id) business_group_id,
3687 nvl(name, root_name) name,
3688 root_budget_group,
3689 ps_account_position_set_id psapsid,
3690 nps_account_position_set_id npsapsid
3691 from PSB_BUDGET_GROUPS_V
3692 where budget_group_id = g_budget_group_id;
3693
3694 cursor c_Sob is
3695 select currency_code,
3696 chart_of_accounts_id,
3697 name,
3698 enable_budgetary_control_flag
3699 from GL_SETS_OF_BOOKS
3700 where set_of_books_id = g_set_of_books_id;
3701
3702 cursor c_SP is
3703 select service_package_id
3704 from PSB_SERVICE_PACKAGES
3705 where base_service_package = 'Y'
3706 and global_worksheet_id = g_global_worksheet_id;
3707
3708 cursor c_sp_seq is
3709 select psb_service_packages_s.nextval ServicePackageID
3710 from dual;
3711
3712 cursor c_Stage is
3713 select Min(sequence_number) sequence_number
3714 from PSB_BUDGET_STAGES
3715 where budget_stage_set_id = g_stage_set_id;
3716
3717 cursor c_ConstSet is
3718 select name,
3719 constraint_threshold
3720 from PSB_CONSTRAINT_SETS_V
3721 where constraint_set_id = g_constraint_set_id;
3722
3723 BEGIN
3724
3725 l_userid := FND_GLOBAL.USER_ID;
3726 l_loginid := FND_GLOBAL.LOGIN_ID;
3727
3728 for c_WS_Rec in c_WS loop
3729 g_budget_group_id := c_WS_Rec.budget_group_id;
3730 g_budget_calendar_id := c_WS_Rec.budget_calendar_id;
3731 g_parameter_set_id := c_WS_Rec.parameter_set_id;
3732 g_constraint_set_id := c_WS_Rec.constraint_set_id;
3733 g_allocrule_set_id := c_WS_Rec.allocrule_set_id;
3734 g_data_extract_id := c_WS_Rec.data_extract_id;
3735 g_data_extract_name := c_WS_Rec.data_extract_name;
3736 g_global_worksheet_id := c_WS_Rec.global_worksheet_id;
3737 g_local_copy_flag := c_WS_Rec.local_copy_flag;
3738 g_global_worksheet := c_WS_Rec.global_worksheet_flag;
3739 g_global_worksheet_option := c_WS_Rec.global_worksheet_option;
3740 g_rounding_factor := c_WS_Rec.rounding_factor;
3741 g_budget_version_id := c_WS_Rec.budget_version_id;
3742 g_gl_budget_set_id := c_WS_Rec.gl_budget_set_id;
3743 g_gl_cutoff_period := c_WS_Rec.gl_cutoff_period;
3744 g_budget_by_position := c_WS_Rec.budget_by_position;
3745 g_use_revised_element_rates := c_WS_Rec.use_revised_element_rates;
3746 g_num_proposed_years := c_WS_Rec.num_proposed_years;
3747 g_num_years_to_allocate := c_WS_Rec.num_years_to_allocate;
3748 g_stage_set_id := c_WS_Rec.stage_set_id;
3749 g_current_stage_seq := c_WS_Rec.current_stage_seq;
3750 g_incl_stat_bal := c_WS_Rec.include_stat_balance;
3751 g_incl_trans_bal := c_WS_Rec.include_translated_balance;
3752 g_incl_adj_period := c_WS_Rec.include_adjustment_periods;
3753 g_flex_mapping_set_id := c_WS_Rec.flex_mapping_set_id;
3754 g_create_non_pos_line_items := c_WS_Rec.create_non_pos_line_items;
3755 g_apply_element_parameters := c_WS_Rec.apply_element_parameters;
3756 g_apply_position_parameters := c_WS_Rec.apply_position_parameters;
3757 g_create_positions := c_WS_Rec.create_positions;
3758 g_create_summary_totals := c_WS_Rec.create_summary_totals;
3759 g_apply_constraints := c_WS_Rec.apply_constraints;
3760 g_include_gl_commit_balance := c_WS_Rec.include_gl_commit_balance;
3761 g_include_gl_oblig_balance := c_WS_Rec.include_gl_oblig_balance;
3762 g_include_gl_other_balance := c_WS_Rec.include_gl_other_balance;
3763 g_include_cbc_commit_balance := c_WS_Rec.include_cbc_commit_balance;
3764 g_include_cbc_oblig_balance := c_WS_Rec.include_cbc_oblig_balance;
3765 g_include_cbc_budget_balance := c_WS_Rec.include_cbc_budget_balance;
3766 /* Bug No 4725091 */
3767 g_include_gl_forward_balance := c_WS_Rec.include_gl_forward_balance;
3768 end loop;
3769
3770 if ((g_global_worksheet is null) or (g_global_worksheet = 'N')) then
3771 g_global_worksheet := FND_API.G_FALSE;
3772 else
3773 g_global_worksheet := FND_API.G_TRUE;
3774 end if;
3775
3776 if ((g_budget_by_position is null) or (g_budget_by_position = 'N')) then
3777 g_budget_by_position := FND_API.G_FALSE;
3778 else
3779 g_budget_by_position := FND_API.G_TRUE;
3780 end if;
3781
3782 if ((g_use_revised_element_rates is null) or (g_use_revised_element_rates = 'N')) then
3783 g_use_revised_element_rates := FND_API.G_FALSE;
3784 else
3785 g_use_revised_element_rates := FND_API.G_TRUE;
3786 end if;
3787
3788 if ((g_incl_stat_bal is null) or (g_incl_stat_bal = 'N')) then
3789 g_incl_stat_bal := FND_API.G_FALSE;
3790 else
3791 g_incl_stat_bal := FND_API.G_TRUE;
3792 end if;
3793
3794 if ((g_incl_trans_bal is null) or (g_incl_trans_bal = 'N')) then
3795 g_incl_trans_bal := FND_API.G_FALSE;
3796 else
3797 g_incl_trans_bal := FND_API.G_TRUE;
3798 end if;
3799
3800 if ((g_incl_adj_period is null) or (g_incl_adj_period = 'N')) then
3801 g_incl_adj_period := FND_API.G_FALSE;
3802 else
3803 g_incl_adj_period := FND_API.G_TRUE;
3804 end if;
3805
3806 if ((g_create_non_pos_line_items is null) or (g_create_non_pos_line_items = 'N')) then
3807 g_create_non_pos_line_items := FND_API.G_FALSE;
3808 else
3809 g_create_non_pos_line_items := FND_API.G_TRUE;
3810 end if;
3811
3812 if ((g_apply_element_parameters is null) or (g_apply_element_parameters = 'N')) then
3813 g_apply_element_parameters := FND_API.G_FALSE;
3814 else
3815 g_apply_element_parameters := FND_API.G_TRUE;
3816 end if;
3817
3818 if ((g_apply_position_parameters is null) or (g_apply_position_parameters = 'N')) then
3819 g_apply_position_parameters := FND_API.G_FALSE;
3820 else
3821 g_apply_position_parameters := FND_API.G_TRUE;
3822 end if;
3823
3824 if ((g_create_positions is null) or (g_create_positions = 'N')) then
3825 g_create_positions := FND_API.G_FALSE;
3826 else
3827 g_create_positions := FND_API.G_TRUE;
3828 end if;
3829
3830 if ((g_create_summary_totals is null) or (g_create_summary_totals = 'N')) then
3831 g_create_summary_totals := FND_API.G_FALSE;
3832 else
3833 g_create_summary_totals := FND_API.G_TRUE;
3834 end if;
3835
3836 if ((g_apply_constraints is null) or (g_apply_constraints = 'N')) then
3837 g_apply_constraints := FND_API.G_FALSE;
3838 else
3839 g_apply_constraints := FND_API.G_TRUE;
3840 end if;
3841
3842 if ((g_include_gl_commit_balance is null) or (g_include_gl_commit_balance = 'N')) then
3843 g_include_gl_commit_balance := FND_API.G_FALSE;
3844 else
3845 g_include_gl_commit_balance := FND_API.G_TRUE;
3846 end if;
3847
3848 if ((g_include_gl_oblig_balance is null) or (g_include_gl_oblig_balance = 'N')) then
3849 g_include_gl_oblig_balance := FND_API.G_FALSE;
3850 else
3851 g_include_gl_oblig_balance := FND_API.G_TRUE;
3852 end if;
3853
3854 if ((g_include_gl_other_balance is null) or (g_include_gl_other_balance = 'N')) then
3855 g_include_gl_other_balance := FND_API.G_FALSE;
3856 else
3857 g_include_gl_other_balance := FND_API.G_TRUE;
3858 end if;
3859
3860 if ((g_include_cbc_commit_balance is null) or (g_include_cbc_commit_balance = 'N')) then
3861 g_include_cbc_commit_balance := FND_API.G_FALSE;
3862 else
3863 g_include_cbc_commit_balance := FND_API.G_TRUE;
3864 end if;
3865
3866 if ((g_include_cbc_oblig_balance is null) or (g_include_cbc_oblig_balance = 'N')) then
3867 g_include_cbc_oblig_balance := FND_API.G_FALSE;
3868 else
3869 g_include_cbc_oblig_balance := FND_API.G_TRUE;
3870 end if;
3871
3872 if ((g_include_cbc_budget_balance is null) or (g_include_cbc_budget_balance = 'N')) then
3873 g_include_cbc_budget_balance := FND_API.G_FALSE;
3874 else
3875 g_include_cbc_budget_balance := FND_API.G_TRUE;
3876 end if;
3877
3878 /* bug no 4725091 */
3879 if (g_include_gl_forward_balance is null) or (g_include_gl_forward_balance = 'N') then
3880 g_include_gl_forward_balance := fnd_api.g_false;
3881 else
3882 g_include_gl_forward_balance := fnd_api.g_true;
3883 end if;
3884 /* bug no 4725091 */
3885
3886
3887 for c_BG_Rec in c_BG loop
3888 g_root_budget_group_id := c_BG_Rec.root_budget_group_id;
3889 g_set_of_books_id := c_BG_Rec.set_of_books_id;
3890 g_business_group_id := c_BG_Rec.business_group_id;
3891 g_budget_group_name := c_BG_Rec.name;
3892
3893 /* Bug 3543845 start : Cache ps_acct_pos_set_id and nps_acct_pos_set_id */
3894 IF (c_BG_Rec.root_budget_group = 'Y')
3895 THEN
3896
3897 g_ps_acct_pos_set_id := c_BG_Rec.psapsid;
3898 g_nps_acct_pos_set_id := c_BG_Rec.npsapsid;
3899
3900 ELSE
3901
3902 SELECT ps_account_position_set_id,
3903 nps_account_position_set_id
3904 INTO
3905 g_ps_acct_pos_set_id,
3906 g_nps_acct_pos_set_id
3907 FROM PSB_BUDGET_GROUPS
3908 WHERE budget_group_id = g_root_budget_group_id;
3909
3910 END IF;
3911 /* Bug 3543845 End */
3912 end loop;
3913
3914 for c_Sob_Rec in c_Sob loop
3915 g_currency_code := c_Sob_Rec.currency_code;
3916 g_flex_code := c_Sob_Rec.chart_of_accounts_id;
3917 g_chart_of_accounts_id := g_flex_code; -- Bug#4571412
3918 g_set_of_books_name := c_Sob_Rec.name;
3919 g_budgetary_control := c_Sob_Rec.enable_budgetary_control_flag;
3920 end loop;
3921
3922 if ((g_budgetary_control is null) or (g_budgetary_control = 'N')) then
3923 g_budgetary_control := FND_API.G_FALSE;
3924 else
3925 g_budgetary_control := FND_API.G_TRUE;
3926 end if;
3927
3928 for c_SP_Rec in c_SP loop
3929 g_service_package_id := c_SP_Rec.service_package_id;
3930 l_new_base_sp := FND_API.G_FALSE;
3931 end loop;
3932
3933 if FND_API.to_Boolean(l_new_base_sp) then
3934 begin
3935
3936 for c_sp_seq_rec in c_sp_seq loop
3937 g_service_package_id := c_sp_seq_rec.ServicePackageID;
3938 end loop;
3939
3940 add_message('PSB', 'PSB_BASE_SERVICE_PACKAGE');
3941 l_name := FND_MSG_PUB.Get
3942 (p_encoded => FND_API.G_FALSE);
3943 FND_MSG_PUB.Delete_Msg;
3944
3945 insert into PSB_SERVICE_PACKAGES
3946 (service_package_id, global_worksheet_id,
3947 base_service_package, name,
3948 short_name, description, priority,
3949 last_update_date, last_updated_by,
3950 last_update_login, created_by, creation_date)
3951 values (g_service_package_id, p_worksheet_id,
3952 'Y', substr(l_name, 1, 30),
3953 substr(l_name, 1, 15), l_name, null,
3954 sysdate, l_userid,
3955 l_loginid, l_userid, sysdate);
3956
3957 end;
3958 end if;
3959
3960 for c_Stage_Rec in c_Stage loop
3961 g_start_stage_seq := c_Stage_Rec.sequence_number;
3962 end loop;
3963
3964 for c_ConstSet_Rec in c_ConstSet loop
3965 g_cs_name := c_ConstSet_Rec.name;
3966 g_cs_threshold := c_ConstSet_Rec.constraint_threshold;
3967 end loop;
3968
3969 -- Bug 3458191: Caching g_worksheet_id for account creation cp
3970 g_worksheet_id := p_worksheet_id;
3971
3972 p_return_status := FND_API.G_RET_STS_SUCCESS;
3973
3974
3975 EXCEPTION
3976
3977 when OTHERS then
3978 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3979
3980 END Cache_Worksheet_Variables;
3981
3982 /* ----------------------------------------------------------------------- */
3983
3984 PROCEDURE Check_DataExt_Completion
3985 ( p_data_extract_id IN NUMBER,
3986 p_return_status OUT NOCOPY VARCHAR2
3987 ) IS
3988
3989 l_status VARCHAR2(1);
3990
3991 cursor c_DataExtract is
3992 select data_extract_status
3993 from PSB_DATA_EXTRACTS
3994 where data_extract_id = p_data_extract_id;
3995
3996 BEGIN
3997
3998 for c_DataExtract_Rec in c_DataExtract loop
3999 l_status := nvl(c_DataExtract_Rec.data_extract_status, 'I');
4000 end loop;
4001
4002 if l_status <> 'C' then
4003 raise FND_API.G_EXC_ERROR;
4004 end if;
4005
4006 p_return_status := FND_API.G_RET_STS_SUCCESS;
4007
4008
4009 EXCEPTION
4010
4011 when FND_API.G_EXC_ERROR then
4012 p_return_status := FND_API.G_RET_STS_ERROR;
4013
4014 when FND_API.G_EXC_UNEXPECTED_ERROR then
4015 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4016
4017 when OTHERS then
4018 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4019
4020 END Check_DataExt_Completion;
4021
4022 /* ----------------------------------------------------------------------- */
4023
4024 -- Add Token and Value to the Message Token array
4025
4026 PROCEDURE message_token(tokname IN VARCHAR2,
4027 tokval IN VARCHAR2) IS
4028
4029 BEGIN
4030
4031 if no_msg_tokens is null then
4032 no_msg_tokens := 1;
4033 else
4034 no_msg_tokens := no_msg_tokens + 1;
4035 end if;
4036
4037 msg_tok_names(no_msg_tokens) := tokname;
4038 msg_tok_val(no_msg_tokens) := tokval;
4039
4040 END message_token;
4041
4042 /* ----------------------------------------------------------------------- */
4043
4044 -- Define a Message Token with a Value and set the Message Name
4045
4046 -- Calls FND_MESSAGE server package to set the Message Stack. This message is
4047 -- retrieved by the calling program.
4048
4049 PROCEDURE add_message(appname IN VARCHAR2,
4050 msgname IN VARCHAR2) IS
4051
4052 i BINARY_INTEGER;
4053
4054 BEGIN
4055
4056 if ((appname is not null) and
4057 (msgname is not null)) then
4058
4059 FND_MESSAGE.SET_NAME(appname, msgname);
4060
4061 if no_msg_tokens is not null then
4062
4063 for i in 1..no_msg_tokens loop
4064 FND_MESSAGE.SET_TOKEN(msg_tok_names(i), msg_tok_val(i));
4065 end loop;
4066
4067 end if;
4068
4069 FND_MSG_PUB.Add;
4070
4071 end if;
4072
4073 -- Clear Message Token stack
4074
4075 no_msg_tokens := 0;
4076
4077 END add_message;
4078
4079 /* ----------------------------------------------------------------------- */
4080
4081 -- Get Debug Information
4082
4083 -- This Module is used to retrieve Debug Information for this routine. It
4084 -- prints Debug Information when run as a Batch Process from SQL*Plus. For
4085 -- the Debug Information to be printed on the Screen, the SQL*Plus parameter
4086 -- 'Serveroutput' should be set to 'ON'
4087
4088 FUNCTION Get_Debug RETURN VARCHAR2 IS
4089
4090 BEGIN
4091
4092 return(g_dbug);
4093
4094 END Get_Debug;
4095
4096 /* ----------------------------------------------------------------------- */
4097
4098 END PSB_WORKSHEET;