[Home] [Help]
PACKAGE BODY: APPS.PSB_WS_POS1
Source
1 PACKAGE BODY PSB_WS_POS1 AS
2 /* $Header: PSBVWP1B.pls 120.20 2006/02/23 13:29:39 shtripat ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):= 'PSB_WS_POS1';
5
6 cursor c_WS (Worksheet NUMBER) is
7 select budget_calendar_id,
8 budget_group_id,
9 rounding_factor,
10 nvl(global_worksheet_id, worksheet_id) global_worksheet_id,
11 nvl(data_extract_id, global_data_extract_id) data_extract_id,
12 local_copy_flag
13 from PSB_WORKSHEETS_V
14 where worksheet_id = Worksheet;
15
16 cursor c_Wfl_SP (PosLine NUMBER,
17 BudYr NUMBER,
18 SvcPkg NUMBER,
19 StSet NUMBER,
20 CurSeq NUMBER) is
21 select fte_line_id,
22 period1_fte, period2_fte, period3_fte, period4_fte,
23 period5_fte, period6_fte, period7_fte, period8_fte,
24 period9_fte, period10_fte, period11_fte, period12_fte,
25 period13_fte, period14_fte, period15_fte, period16_fte,
26 period17_fte, period18_fte, period19_fte, period20_fte,
27 period21_fte, period22_fte, period23_fte, period24_fte,
28 period25_fte, period26_fte, period27_fte, period28_fte,
29 period29_fte, period30_fte, period31_fte, period32_fte,
30 period33_fte, period34_fte, period35_fte, period36_fte,
31 period37_fte, period38_fte, period39_fte, period40_fte,
32 period41_fte, period42_fte, period43_fte, period44_fte,
33 period45_fte, period46_fte, period47_fte, period48_fte,
34 period49_fte, period50_fte, period51_fte, period52_fte,
35 period53_fte, period54_fte, period55_fte, period56_fte,
36 period57_fte, period58_fte, period59_fte, period60_fte,
37 annual_fte
38 from PSB_WS_FTE_LINES a
39 where CurSeq between start_stage_seq and current_stage_seq
40 and stage_set_id = StSet
41 and service_package_id = SvcPkg
42 and budget_year_id = BudYr
43 and position_line_id = PosLine;
44
45 cursor c_BG (BudGrp NUMBER) is
46 select nvl(business_group_id, root_business_group_id) business_group_id
47 from PSB_BUDGET_GROUPS_V
48 where budget_group_id = BudGrp;
49
50 cursor c_FTESeq is
51 select psb_ws_fte_lines_s.nextval FteLineID
52 from dual;
53
54 cursor c_BaseSP (GlobalWS NUMBER) is
55 select service_package_id
56 from PSB_SERVICE_PACKAGES
57 where base_service_package = 'Y'
58 and global_worksheet_id = GlobalWS;
59
60 cursor c_Rec_Elements (DataExt NUMBER,
61 BusGrp NUMBER) is
62 select pay_element_id
63 from PSB_PAY_ELEMENTS
64 where processing_type = 'R'
65 and business_group_id = BusGrp
66 and data_extract_id = DataExt
67 /* For Bug No. 2250319 */
68 order by pay_element_id;
69
70 TYPE TokNameArray IS TABLE OF VARCHAR2(100)
71 INDEX BY BINARY_INTEGER;
72
73 TYPE TokValArray IS TABLE OF VARCHAR2(1000)
74 INDEX BY BINARY_INTEGER;
75
76 -- Message Tokens
77 no_msg_tokens NUMBER := 0;
78 msg_tok_names TokNameArray;
79 msg_tok_val TokValArray;
80
81 -- To store last annual FTE ratio for elements within an element set.
82 g_last_annual_fte_ratio NUMBER;
83
84 g_dbug VARCHAR2(1000);
85
86 /* ----------------------------------------------------------------------- */
87 /* */
88 /* Private Function Definition */
89 /* */
90 /* ----------------------------------------------------------------------- */
91
92 -- This procedure prorates the Element Costs and Account Distributions
93 -- using the Period FTE Ratios and the Annual FTE Ratio
94
95 PROCEDURE Distribute_Position_Cost
96 ( p_return_status OUT NOCOPY VARCHAR2,
97 p_insert_from_base IN VARCHAR2 := FND_API.G_FALSE,
98 p_update_from_base IN VARCHAR2 := FND_API.G_FALSE,
99 p_worksheet_id IN NUMBER,
100 p_flex_mapping_set_id IN NUMBER := FND_API.G_MISS_NUM,
101 p_rounding_factor IN NUMBER,
102 p_position_line_id IN NUMBER,
103 p_pay_element_id IN NUMBER,
104 p_budget_year_id IN NUMBER,
105 p_base_service_package_id IN NUMBER := FND_API.G_MISS_NUM,
106 p_service_package_id IN NUMBER,
107 p_stage_set_id IN NUMBER,
108 p_start_stage_seq IN NUMBER,
109 p_current_stage_seq IN NUMBER,
110 p_budget_group_id IN NUMBER,
111 /*For Bug No : 2811698 Start*/
112 p_period_fte IN PSB_WS_ACCT1.g_prdamt_tbl_type,
113 p_total_fte IN NUMBER,
114 p_num_budget_periods IN NUMBER
115 /*For Bug No : 2811698 End*/
116
117 );
118
119 PROCEDURE message_token
120 ( tokname IN VARCHAR2,
121 tokval IN VARCHAR2
122 );
123
124 PROCEDURE add_message
125 ( appname IN VARCHAR2,
126 msgname IN VARCHAR2
127 );
128
129 /* ----------------------------------------------------------------------- */
130 /* Bug No 2278216 Start */
131
132 -- Initialize the PL/SQL Structures that temporarily store Position Account
133 -- Period Distribution for a year
134
135 PROCEDURE Initialize_Period_Dist IS
136
137 l_init_index BINARY_INTEGER;
138
139 BEGIN
140
141 for l_init_index in 1..g_periods.Count loop
142 g_periods(l_init_index).ccid := null;
143 g_periods(l_init_index).element_type := null;
144 g_periods(l_init_index).element_set_id := null;
145 g_periods(l_init_index).budget_year_id := null;
146 g_periods(l_init_index).percent := null;
147 g_periods(l_init_index).period_start_date := null;
148 g_periods(l_init_index).period_end_date := null;
149 end loop;
150
151 g_num_periods := 0;
152
153 END Initialize_Period_Dist;
154
155 /* Bug No 2278216 End */
156 /* ----------------------------------------------------------------------- */
157
158 -- Initialize the PL/SQL Structures that temporarily store Position Cost info
159
160 PROCEDURE Initialize_Calc
161 ( p_init_index NUMBER := FND_API.G_MISS_NUM
162 ) IS
163
164 l_init_index BINARY_INTEGER;
165
166 BEGIN
167
168 if p_init_index = FND_API.G_MISS_NUM then
169 begin
170
171 -- Initialize the entire structure
172
173 for l_init_index in 1..g_pc_costs.Count loop
174 g_pc_costs(l_init_index).pay_element_id := null;
175 g_pc_costs(l_init_index).element_type := null;
176 g_pc_costs(l_init_index).element_set_id := null;
177 g_pc_costs(l_init_index).element_cost := null;
178 g_pc_costs(l_init_index).budget_year_id := null;
179 g_pc_costs(l_init_index).period1_amount := null;
180 g_pc_costs(l_init_index).period2_amount := null;
181 g_pc_costs(l_init_index).period3_amount := null;
182 g_pc_costs(l_init_index).period4_amount := null;
183 g_pc_costs(l_init_index).period5_amount := null;
184 g_pc_costs(l_init_index).period6_amount := null;
185 g_pc_costs(l_init_index).period7_amount := null;
186 g_pc_costs(l_init_index).period8_amount := null;
187 g_pc_costs(l_init_index).period9_amount := null;
188 g_pc_costs(l_init_index).period10_amount := null;
189 g_pc_costs(l_init_index).period11_amount := null;
190 g_pc_costs(l_init_index).period12_amount := null;
191 g_pc_costs(l_init_index).period13_amount := null;
192 g_pc_costs(l_init_index).period14_amount := null;
193 g_pc_costs(l_init_index).period15_amount := null;
194 g_pc_costs(l_init_index).period16_amount := null;
195 g_pc_costs(l_init_index).period17_amount := null;
196 g_pc_costs(l_init_index).period18_amount := null;
197 g_pc_costs(l_init_index).period19_amount := null;
198 g_pc_costs(l_init_index).period20_amount := null;
199 g_pc_costs(l_init_index).period21_amount := null;
200 g_pc_costs(l_init_index).period22_amount := null;
201 g_pc_costs(l_init_index).period23_amount := null;
202 g_pc_costs(l_init_index).period24_amount := null;
203 g_pc_costs(l_init_index).period25_amount := null;
204 g_pc_costs(l_init_index).period26_amount := null;
205 g_pc_costs(l_init_index).period27_amount := null;
206 g_pc_costs(l_init_index).period28_amount := null;
207 g_pc_costs(l_init_index).period29_amount := null;
208 g_pc_costs(l_init_index).period30_amount := null;
209 g_pc_costs(l_init_index).period31_amount := null;
210 g_pc_costs(l_init_index).period32_amount := null;
211 g_pc_costs(l_init_index).period33_amount := null;
212 g_pc_costs(l_init_index).period34_amount := null;
213 g_pc_costs(l_init_index).period35_amount := null;
214 g_pc_costs(l_init_index).period36_amount := null;
215 g_pc_costs(l_init_index).period37_amount := null;
216 g_pc_costs(l_init_index).period38_amount := null;
217 g_pc_costs(l_init_index).period39_amount := null;
218 g_pc_costs(l_init_index).period40_amount := null;
219 g_pc_costs(l_init_index).period41_amount := null;
220 g_pc_costs(l_init_index).period42_amount := null;
221 g_pc_costs(l_init_index).period43_amount := null;
222 g_pc_costs(l_init_index).period44_amount := null;
223 g_pc_costs(l_init_index).period45_amount := null;
224 g_pc_costs(l_init_index).period46_amount := null;
225 g_pc_costs(l_init_index).period47_amount := null;
226 g_pc_costs(l_init_index).period48_amount := null;
227 g_pc_costs(l_init_index).period49_amount := null;
228 g_pc_costs(l_init_index).period50_amount := null;
229 g_pc_costs(l_init_index).period51_amount := null;
230 g_pc_costs(l_init_index).period52_amount := null;
231 g_pc_costs(l_init_index).period53_amount := null;
232 g_pc_costs(l_init_index).period54_amount := null;
233 g_pc_costs(l_init_index).period55_amount := null;
234 g_pc_costs(l_init_index).period56_amount := null;
235 g_pc_costs(l_init_index).period57_amount := null;
236 g_pc_costs(l_init_index).period58_amount := null;
237 g_pc_costs(l_init_index).period59_amount := null;
238 g_pc_costs(l_init_index).period60_amount := null;
239 end loop;
240
241 g_num_pc_costs := 0;
242
243 end;
244 else
245 begin
246
247 -- Initialize a specific record in the structure. This is to avoid
248 -- referencing uninitialized columns in the record which was
249 -- crashing the database
250
251 g_pc_costs(p_init_index).pay_element_id := null;
252 g_pc_costs(p_init_index).element_type := null;
253 g_pc_costs(p_init_index).element_set_id := null;
254 g_pc_costs(p_init_index).element_cost := null;
255 g_pc_costs(p_init_index).budget_year_id := null;
256 g_pc_costs(p_init_index).period1_amount := null;
257 g_pc_costs(p_init_index).period2_amount := null;
258 g_pc_costs(p_init_index).period3_amount := null;
259 g_pc_costs(p_init_index).period4_amount := null;
260 g_pc_costs(p_init_index).period5_amount := null;
261 g_pc_costs(p_init_index).period6_amount := null;
262 g_pc_costs(p_init_index).period7_amount := null;
263 g_pc_costs(p_init_index).period8_amount := null;
264 g_pc_costs(p_init_index).period9_amount := null;
265 g_pc_costs(p_init_index).period10_amount := null;
266 g_pc_costs(p_init_index).period11_amount := null;
267 g_pc_costs(p_init_index).period12_amount := null;
268 g_pc_costs(p_init_index).period13_amount := null;
269 g_pc_costs(p_init_index).period14_amount := null;
270 g_pc_costs(p_init_index).period15_amount := null;
271 g_pc_costs(p_init_index).period16_amount := null;
272 g_pc_costs(p_init_index).period17_amount := null;
273 g_pc_costs(p_init_index).period18_amount := null;
274 g_pc_costs(p_init_index).period19_amount := null;
275 g_pc_costs(p_init_index).period20_amount := null;
276 g_pc_costs(p_init_index).period21_amount := null;
277 g_pc_costs(p_init_index).period22_amount := null;
278 g_pc_costs(p_init_index).period23_amount := null;
279 g_pc_costs(p_init_index).period24_amount := null;
280 g_pc_costs(p_init_index).period25_amount := null;
281 g_pc_costs(p_init_index).period26_amount := null;
282 g_pc_costs(p_init_index).period27_amount := null;
283 g_pc_costs(p_init_index).period28_amount := null;
284 g_pc_costs(p_init_index).period29_amount := null;
285 g_pc_costs(p_init_index).period30_amount := null;
286 g_pc_costs(p_init_index).period31_amount := null;
287 g_pc_costs(p_init_index).period32_amount := null;
288 g_pc_costs(p_init_index).period33_amount := null;
289 g_pc_costs(p_init_index).period34_amount := null;
290 g_pc_costs(p_init_index).period35_amount := null;
291 g_pc_costs(p_init_index).period36_amount := null;
292 g_pc_costs(p_init_index).period37_amount := null;
293 g_pc_costs(p_init_index).period38_amount := null;
294 g_pc_costs(p_init_index).period39_amount := null;
295 g_pc_costs(p_init_index).period40_amount := null;
296 g_pc_costs(p_init_index).period41_amount := null;
297 g_pc_costs(p_init_index).period42_amount := null;
298 g_pc_costs(p_init_index).period43_amount := null;
299 g_pc_costs(p_init_index).period44_amount := null;
300 g_pc_costs(p_init_index).period45_amount := null;
301 g_pc_costs(p_init_index).period46_amount := null;
302 g_pc_costs(p_init_index).period47_amount := null;
303 g_pc_costs(p_init_index).period48_amount := null;
304 g_pc_costs(p_init_index).period49_amount := null;
305 g_pc_costs(p_init_index).period50_amount := null;
306 g_pc_costs(p_init_index).period51_amount := null;
307 g_pc_costs(p_init_index).period52_amount := null;
308 g_pc_costs(p_init_index).period53_amount := null;
309 g_pc_costs(p_init_index).period54_amount := null;
310 g_pc_costs(p_init_index).period55_amount := null;
311 g_pc_costs(p_init_index).period56_amount := null;
312 g_pc_costs(p_init_index).period57_amount := null;
313 g_pc_costs(p_init_index).period58_amount := null;
314 g_pc_costs(p_init_index).period59_amount := null;
315 g_pc_costs(p_init_index).period60_amount := null;
316
317 end;
318 end if;
319
320 END Initialize_Calc;
321
322 /* ----------------------------------------------------------------------- */
323
324 -- Initialize the PL/SQL Structures that temporarily store Position Account
325 -- Distribution info
326
327 PROCEDURE Initialize_Dist IS
328
329 l_init_index BINARY_INTEGER;
330
331 BEGIN
332
333 for l_init_index in 1..g_pd_costs.Count loop
334 g_pd_costs(l_init_index).ccid := null;
335 g_pd_costs(l_init_index).element_type := null;
336 g_pd_costs(l_init_index).element_set_id := null;
337 g_pd_costs(l_init_index).budget_year_id := null;
338 g_pd_costs(l_init_index).ytd_amount := null;
339 g_pd_costs(l_init_index).period1_amount := null;
340 g_pd_costs(l_init_index).period2_amount := null;
341 g_pd_costs(l_init_index).period3_amount := null;
342 g_pd_costs(l_init_index).period4_amount := null;
343 g_pd_costs(l_init_index).period5_amount := null;
344 g_pd_costs(l_init_index).period6_amount := null;
345 g_pd_costs(l_init_index).period7_amount := null;
346 g_pd_costs(l_init_index).period8_amount := null;
347 g_pd_costs(l_init_index).period9_amount := null;
348 g_pd_costs(l_init_index).period10_amount := null;
349 g_pd_costs(l_init_index).period11_amount := null;
350 g_pd_costs(l_init_index).period12_amount := null;
351 g_pd_costs(l_init_index).period13_amount := null;
352 g_pd_costs(l_init_index).period14_amount := null;
353 g_pd_costs(l_init_index).period15_amount := null;
354 g_pd_costs(l_init_index).period16_amount := null;
355 g_pd_costs(l_init_index).period17_amount := null;
356 g_pd_costs(l_init_index).period18_amount := null;
357 g_pd_costs(l_init_index).period19_amount := null;
358 g_pd_costs(l_init_index).period20_amount := null;
359 g_pd_costs(l_init_index).period21_amount := null;
360 g_pd_costs(l_init_index).period22_amount := null;
361 g_pd_costs(l_init_index).period23_amount := null;
362 g_pd_costs(l_init_index).period24_amount := null;
363 g_pd_costs(l_init_index).period25_amount := null;
364 g_pd_costs(l_init_index).period26_amount := null;
365 g_pd_costs(l_init_index).period27_amount := null;
366 g_pd_costs(l_init_index).period28_amount := null;
367 g_pd_costs(l_init_index).period29_amount := null;
368 g_pd_costs(l_init_index).period30_amount := null;
369 g_pd_costs(l_init_index).period31_amount := null;
370 g_pd_costs(l_init_index).period32_amount := null;
371 g_pd_costs(l_init_index).period33_amount := null;
372 g_pd_costs(l_init_index).period34_amount := null;
373 g_pd_costs(l_init_index).period35_amount := null;
374 g_pd_costs(l_init_index).period36_amount := null;
375 g_pd_costs(l_init_index).period37_amount := null;
376 g_pd_costs(l_init_index).period38_amount := null;
377 g_pd_costs(l_init_index).period39_amount := null;
378 g_pd_costs(l_init_index).period40_amount := null;
379 g_pd_costs(l_init_index).period41_amount := null;
380 g_pd_costs(l_init_index).period42_amount := null;
381 g_pd_costs(l_init_index).period43_amount := null;
382 g_pd_costs(l_init_index).period44_amount := null;
383 g_pd_costs(l_init_index).period45_amount := null;
384 g_pd_costs(l_init_index).period46_amount := null;
385 g_pd_costs(l_init_index).period47_amount := null;
386 g_pd_costs(l_init_index).period48_amount := null;
387 g_pd_costs(l_init_index).period49_amount := null;
388 g_pd_costs(l_init_index).period50_amount := null;
389 g_pd_costs(l_init_index).period51_amount := null;
390 g_pd_costs(l_init_index).period52_amount := null;
391 g_pd_costs(l_init_index).period53_amount := null;
392 g_pd_costs(l_init_index).period54_amount := null;
393 g_pd_costs(l_init_index).period55_amount := null;
394 g_pd_costs(l_init_index).period56_amount := null;
395 g_pd_costs(l_init_index).period57_amount := null;
396 g_pd_costs(l_init_index).period58_amount := null;
397 g_pd_costs(l_init_index).period59_amount := null;
398 g_pd_costs(l_init_index).period60_amount := null;
399 end loop;
400
401 g_num_pd_costs := 0;
402
403 END Initialize_Dist;
404
405 /* ----------------------------------------------------------------------- */
406
407 -- Initialize the PL/SQL Structures that temporarily store Position Salary
408 -- Distribution info
409
410 PROCEDURE Initialize_Salary_Dist IS
411
412 l_init_index BINARY_INTEGER;
413
414 BEGIN
415
416 for l_init_index in 1..g_salary_dist.Count loop
417 g_salary_dist(l_init_index).ccid := null;
418 g_salary_dist(l_init_index).amount := null;
419 g_salary_dist(l_init_index).percent := null;
420 g_salary_dist(l_init_index).start_date := null;
421 g_salary_dist(l_init_index).end_date := null;
422 end loop;
423
424 g_num_salary_dist := 0;
425
426 END Initialize_Salary_Dist;
427
428 /* ----------------------------------------------------------------------- */
429
430 -- Initialize the PL/SQL Structures that temporarily store Position Element
431 -- Distribution info. This is invoked by Redistribute_Follow_Salary
432
433 PROCEDURE Initialize_Element_Dist IS
434
435 l_init_index BINARY_INTEGER;
436
437 BEGIN
438
439 for l_init_index in 1..g_element_dist.Count loop
440 g_element_dist(l_init_index).account_line_id := null;
441 g_element_dist(l_init_index).ccid := null;
442 g_element_dist(l_init_index).ytd_amount := null;
443 g_element_dist(l_init_index).period1_amount := null;
444 g_element_dist(l_init_index).period2_amount := null;
445 g_element_dist(l_init_index).period3_amount := null;
446 g_element_dist(l_init_index).period4_amount := null;
447 g_element_dist(l_init_index).period5_amount := null;
448 g_element_dist(l_init_index).period6_amount := null;
449 g_element_dist(l_init_index).period7_amount := null;
450 g_element_dist(l_init_index).period8_amount := null;
451 g_element_dist(l_init_index).period9_amount := null;
452 g_element_dist(l_init_index).period10_amount := null;
453 g_element_dist(l_init_index).period11_amount := null;
454 g_element_dist(l_init_index).period12_amount := null;
455 g_element_dist(l_init_index).period13_amount := null;
456 g_element_dist(l_init_index).period14_amount := null;
457 g_element_dist(l_init_index).period15_amount := null;
458 g_element_dist(l_init_index).period16_amount := null;
459 g_element_dist(l_init_index).period17_amount := null;
460 g_element_dist(l_init_index).period18_amount := null;
461 g_element_dist(l_init_index).period19_amount := null;
462 g_element_dist(l_init_index).period20_amount := null;
463 g_element_dist(l_init_index).period21_amount := null;
464 g_element_dist(l_init_index).period22_amount := null;
465 g_element_dist(l_init_index).period23_amount := null;
466 g_element_dist(l_init_index).period24_amount := null;
467 g_element_dist(l_init_index).period25_amount := null;
468 g_element_dist(l_init_index).period26_amount := null;
469 g_element_dist(l_init_index).period27_amount := null;
470 g_element_dist(l_init_index).period28_amount := null;
471 g_element_dist(l_init_index).period29_amount := null;
472 g_element_dist(l_init_index).period30_amount := null;
473 g_element_dist(l_init_index).period31_amount := null;
474 g_element_dist(l_init_index).period32_amount := null;
475 g_element_dist(l_init_index).period33_amount := null;
476 g_element_dist(l_init_index).period34_amount := null;
477 g_element_dist(l_init_index).period35_amount := null;
478 g_element_dist(l_init_index).period36_amount := null;
479 g_element_dist(l_init_index).period37_amount := null;
480 g_element_dist(l_init_index).period38_amount := null;
481 g_element_dist(l_init_index).period39_amount := null;
482 g_element_dist(l_init_index).period40_amount := null;
483 g_element_dist(l_init_index).period41_amount := null;
484 g_element_dist(l_init_index).period42_amount := null;
485 g_element_dist(l_init_index).period43_amount := null;
486 g_element_dist(l_init_index).period44_amount := null;
487 g_element_dist(l_init_index).period45_amount := null;
488 g_element_dist(l_init_index).period46_amount := null;
489 g_element_dist(l_init_index).period47_amount := null;
490 g_element_dist(l_init_index).period48_amount := null;
491 g_element_dist(l_init_index).period49_amount := null;
492 g_element_dist(l_init_index).period50_amount := null;
493 g_element_dist(l_init_index).period51_amount := null;
494 g_element_dist(l_init_index).period52_amount := null;
495 g_element_dist(l_init_index).period53_amount := null;
496 g_element_dist(l_init_index).period54_amount := null;
497 g_element_dist(l_init_index).period55_amount := null;
498 g_element_dist(l_init_index).period56_amount := null;
499 g_element_dist(l_init_index).period57_amount := null;
500 g_element_dist(l_init_index).period58_amount := null;
501 g_element_dist(l_init_index).period59_amount := null;
502 g_element_dist(l_init_index).period60_amount := null;
503 g_element_dist(l_init_index).redist_flag := null;
504 end loop;
505
506 g_num_element_dist := 0;
507
508 END Initialize_Element_Dist;
509
510 /* ----------------------------------------------------------------------- */
511
512 -- Check whether the Budget Group for a Position is allowed within a
513 -- Worksheet. This is invoked by the Worksheet Modification module when
514 -- creating new Positions
515
516 FUNCTION Check_Allowed
517 ( p_api_version IN NUMBER,
518 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_NONE,
519 p_worksheet_id IN NUMBER,
520 p_position_budget_group_id IN NUMBER
521 ) RETURN VARCHAR2 IS
522
523 l_api_name CONSTANT VARCHAR2(30) := 'Check_Allowed';
524 l_api_version CONSTANT NUMBER := 1.0;
525
526 l_budget_calendar_id NUMBER;
527 l_budget_group_id NUMBER;
528
529 l_return_status VARCHAR2(1) := FND_API.G_FALSE;
530
531 cursor c_Allowed is
532 select 'Valid'
533 from PSB_BUDGET_GROUPS
534 where budget_group_type = 'R'
535 and effective_start_date <= PSB_WS_ACCT1.g_startdate_pp
536 and (effective_end_date is null
537 or effective_end_date >= PSB_WS_ACCT1.g_enddate_cy)
538 and budget_group_id = p_position_budget_group_id
539 start with budget_group_id = l_budget_group_id
540 connect by prior budget_group_id = parent_budget_group_id;
541
542 BEGIN
543
544 -- Standard call to check for call compatibility
545
546 if not FND_API.Compatible_API_Call (l_api_version,
547 p_api_version,
548 l_api_name,
549 G_PKG_NAME)
550 then
551 raise FND_API.G_EXC_UNEXPECTED_ERROR;
552 end if;
553
554 for c_WS_Rec in c_WS (p_worksheet_id) loop
555 l_budget_calendar_id := c_WS_Rec.budget_calendar_id;
556 l_budget_group_id := c_WS_Rec.budget_group_id;
557 end loop;
558
559 if l_budget_calendar_id <> nvl(PSB_WS_ACCT1.g_budget_calendar_id, FND_API.G_MISS_NUM) then
560 begin
561
562 PSB_WS_ACCT1.Cache_Budget_Calendar
563 (p_return_status => l_return_status,
564 p_budget_calendar_id => l_budget_calendar_id);
565
566 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
567 raise FND_API.G_EXC_ERROR;
568 end if;
569
570 end;
571 end if;
572
573 for c_Allowed_Rec in c_Allowed loop
574 l_return_status := FND_API.G_TRUE;
575 end loop;
576
577 return l_return_status;
578
579
580 EXCEPTION
581
582 when FND_API.G_EXC_ERROR then
583 return FND_API.G_FALSE;
584
585 when FND_API.G_EXC_UNEXPECTED_ERROR then
586 return FND_API.G_FALSE;
587
588 when OTHERS then
589 return FND_API.G_FALSE;
590
591 if FND_MSG_PUB.Check_Msg_Level
592 (p_message_level => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
593 then
594 FND_MSG_PUB.Add_Exc_Msg
595 (p_pkg_name => G_PKG_NAME,
596 p_procedure_name => l_api_name);
597 end if;
598
599 END Check_Allowed;
600
601 /* ----------------------------------------------------------------------- */
602
603 -- Cache all Elements for a Data Extract and Business Group that are valid
604 -- within the projection period for a Worksheet (specified by
605 -- PSB_WS_ACCT1.g_startdate_cy and PSB_WS_ACCT1.g_end_est_date)
606
607 PROCEDURE Cache_Elements
608 ( p_return_status OUT NOCOPY VARCHAR2,
609 p_data_extract_id IN NUMBER,
610 p_business_group_id IN NUMBER,
611 p_worksheet_id IN NUMBER
612 ) IS
613
614 l_budget_calendar_id NUMBER;
615
616 l_init_index BINARY_INTEGER;
617
618 l_return_status VARCHAR2(1);
619
620 cursor c_Elements is
621 select pay_element_id,
622 name,
623 processing_type,
624 max_element_value_type,
625 max_element_value,
626 option_flag,
627 overwrite_flag,
628 salary_flag,
629 salary_type,
630 follow_salary,
631 period_type,
632 process_period_type
633 from PSB_PAY_ELEMENTS
634 where (((start_date <= PSB_WS_ACCT1.g_end_est_date)
635 and (end_date is null))
636 or ((start_date between PSB_WS_ACCT1.g_startdate_cy and PSB_WS_ACCT1.g_end_est_date)
637 or (end_date between PSB_WS_ACCT1.g_startdate_cy and PSB_WS_ACCT1.g_end_est_date)
638 or ((start_date < PSB_WS_ACCT1.g_startdate_cy)
639 and (end_date > PSB_WS_ACCT1.g_end_est_date))))
640 and business_group_id = p_business_group_id
641 and data_extract_id = p_data_extract_id
642 order by salary_flag desc,
643 pay_element_id;
644
645 BEGIN
646
647 for l_init_index in 1..g_elements.Count loop
648 g_elements(l_init_index).pay_element_id := null;
649 g_elements(l_init_index).element_name := null;
650 g_elements(l_init_index).processing_type := null;
651 g_elements(l_init_index).max_element_value_type := null;
652 g_elements(l_init_index).max_element_value := null;
653 g_elements(l_init_index).salary_flag := null;
654 g_elements(l_init_index).option_flag := null;
655 g_elements(l_init_index).overwrite_flag := null;
656 g_elements(l_init_index).salary_type := null;
657 g_elements(l_init_index).follow_salary := null;
658 g_elements(l_init_index).period_type := null;
659 g_elements(l_init_index).process_period_type := null;
660 end loop;
661
662 g_num_elements := 0;
663
664 if g_budget_calendar_id is null then
665 begin
666
667 for c_WS_Rec in c_WS (p_worksheet_id) loop
668 l_budget_calendar_id := c_WS_Rec.budget_calendar_id;
669 end loop;
670
671 end;
672 end if;
673
674 if g_budget_calendar_id is not null then
675 l_budget_calendar_id := g_budget_calendar_id;
676 end if;
677
678 if l_budget_calendar_id <> nvl(PSB_WS_ACCT1.g_budget_calendar_id, FND_API.G_MISS_NUM) then
679 begin
680
681 PSB_WS_ACCT1.Cache_Budget_Calendar
682 (p_return_status => l_return_status,
683 p_budget_calendar_id => l_budget_calendar_id);
684
685 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
686 raise FND_API.G_EXC_ERROR;
687 end if;
688
689 end;
690 end if;
691
692 for c_Elements_Rec in c_Elements loop
693
694 g_num_elements := g_num_elements + 1;
695
696 g_elements(g_num_elements).pay_element_id := c_Elements_Rec.pay_element_id;
697 g_elements(g_num_elements).element_name := c_Elements_Rec.name;
698 g_elements(g_num_elements).processing_type := c_Elements_Rec.processing_type;
699 g_elements(g_num_elements).max_element_value_type := c_Elements_Rec.max_element_value_type;
700 g_elements(g_num_elements).max_element_value := c_Elements_Rec.max_element_value;
701 g_elements(g_num_elements).option_flag := c_Elements_Rec.option_flag;
702 g_elements(g_num_elements).overwrite_flag := c_Elements_Rec.overwrite_flag;
703 g_elements(g_num_elements).salary_flag := c_Elements_Rec.salary_flag;
704 g_elements(g_num_elements).salary_type := c_Elements_Rec.salary_type;
705 g_elements(g_num_elements).follow_salary := c_Elements_Rec.follow_salary;
706 g_elements(g_num_elements).period_type := c_Elements_Rec.period_type;
707 g_elements(g_num_elements).process_period_type := c_Elements_Rec.process_period_type;
708 end loop;
709
710 g_data_extract_id := p_data_extract_id;
711 g_business_group_id := p_business_group_id;
712
713
714 -- Initialize API return status to success
715
716 p_return_status := FND_API.G_RET_STS_SUCCESS;
717
718
719 EXCEPTION
720
721 when FND_API.G_EXC_ERROR then
722 p_return_status := FND_API.G_RET_STS_ERROR;
723
724 when FND_API.G_EXC_UNEXPECTED_ERROR then
725 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
726
727 when OTHERS then
728 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
729
730 END Cache_Elements;
731
732 /* ----------------------------------------------------------------------- */
733
734 -- Cache specific named attribute identifiers for a Business Group. These
735 -- attributes are used in the Worksheet Creation process
736
737 PROCEDURE Cache_Named_Attributes
738 ( p_return_status OUT NOCOPY VARCHAR2,
739 p_business_group_id IN NUMBER
740 ) IS
741
742 cursor c_Attributes is
743 select attribute_id ,
744 system_attribute_type ,
745 NVL(value_table_flag, 'N') value_table_flag
746 from PSB_ATTRIBUTES
747 where system_attribute_type IN ('FTE', 'DEFAULT_WEEKLY_HOURS',
748 'HIREDATE', 'ADJUSTMENT_DATE')
749 and business_group_id = p_business_group_id;
750
751 BEGIN
752
753 for c_Attributes_Rec in c_Attributes loop
754
755 if c_Attributes_Rec.system_attribute_type = 'FTE' then
756 g_fte_id := c_Attributes_Rec.attribute_id;
757 elsif c_Attributes_Rec.system_attribute_type = 'DEFAULT_WEEKLY_HOURS' then
758 g_default_wklyhrs_id := c_Attributes_Rec.attribute_id;
759 g_default_wklyhrs_vt_flag := c_Attributes_Rec.value_table_flag;
760 elsif c_Attributes_Rec.system_attribute_type = 'HIREDATE' then
761 g_hiredate_id := c_Attributes_Rec.attribute_id;
762 elsif c_Attributes_Rec.system_attribute_type = 'ADJUSTMENT_DATE' then
763 g_adjdate_id := c_Attributes_Rec.attribute_id;
764 end if;
765
766 end loop;
767
768 g_attr_busgrp_id := p_business_group_id;
769
770
771 -- Initialize API return status to success
772
773 p_return_status := FND_API.G_RET_STS_SUCCESS;
774
775
776 EXCEPTION
777
778 when FND_API.G_EXC_ERROR then
779 p_return_status := FND_API.G_RET_STS_ERROR;
780
781 when FND_API.G_EXC_UNEXPECTED_ERROR then
782 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
783
784 when OTHERS then
785 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
786
787 END Cache_Named_Attributes;
788
789 /* ----------------------------------------------------------------------- */
790
791 -- Cache Attribute Values for Hiredate and Adjustment Date for a Position
792
793 PROCEDURE Cache_Named_Attribute_Values
794 ( p_return_status OUT NOCOPY VARCHAR2,
795 p_worksheet_id IN NUMBER,
796 p_data_extract_id IN NUMBER,
797 p_position_id IN NUMBER,
798 /* start bug 4104890 */
799 p_local_parameter_flag IN VARCHAR2 := 'N'
800 /* End bug 4104890 */
801 ) IS
802
803 l_adjdate_value_id NUMBER;
804 l_hiredate_value_id NUMBER;
805
806 /* Bug 4075170 Start */
807 l_param_info VARCHAR2(4000);
808 l_debug_info VARCHAR2(4000);
809 /* Bug 4075170 End */
810
811 /*cursor c_Attributes is
812 select attribute_id,
813 attribute_value,
814 attribute_value_id
815 from PSB_POSITION_ASSIGNMENTS
816 where attribute_id in (g_hiredate_id, g_adjdate_id)
817 and worksheet_id is null
818 and assignment_type = 'ATTRIBUTE'
819 and position_id = p_position_id;*/
820
821 /* start bug 4104890 */
822 CURSOR c_Attributes
823 IS
824 SELECT attribute_id,
825 attribute_value,
826 attribute_value_id
827 FROM PSB_POSITION_ASSIGNMENTS po1
828 WHERE attribute_id IN (g_hiredate_id, g_adjdate_id)
829 AND ( (worksheet_id = p_worksheet_id
830 AND (p_local_parameter_flag = 'Y'))
831 OR ( worksheet_id IS NULL
832 AND ( (NOT EXISTS ( SELECT 1
833 FROM psb_position_assignments po2
834 WHERE po1.position_id = po2.position_id
835 AND po1.attribute_id = po2.attribute_id
836 AND po2.worksheet_id = p_worksheet_id))
837 OR ( p_local_parameter_flag = 'N'))))
838 AND assignment_type = 'ATTRIBUTE'
839 AND position_id = p_position_id;
840 /* End bug 4104890 */
841
842
843 cursor c_AttrVal is
844 select attribute_value_id,
845 attribute_value
846 from PSB_ATTRIBUTE_VALUES
847 where attribute_value_id in (l_hiredate_value_id, l_adjdate_value_id);
848
849 BEGIN
850
851 /* Bug 4075170 Start */
852 l_param_info := 'WS_id::'||p_worksheet_id
853 ||', DE_id::'||p_data_extract_id
854 ||', Position_id::'||p_position_id;
855 l_debug_info := 'Starting Cache_Named_Attribute_Values API';
856 /* Bug 4075170 End */
857
858 g_adjustment_date := null;
859 g_hiredate := null;
860
861 for c_Attributes_Rec in c_Attributes loop
862
863 if c_Attributes_Rec.attribute_id = g_adjdate_id then
864 begin
865 g_adjustment_date := fnd_date.canonical_to_date(c_Attributes_Rec.attribute_value);
866 l_adjdate_value_id := nvl(c_Attributes_Rec.attribute_value_id, 0);
867 end;
868 elsif c_Attributes_Rec.attribute_id = g_hiredate_id then
869 begin
870 g_hiredate := fnd_date.canonical_to_date(c_Attributes_Rec.attribute_value);
871 l_hiredate_value_id := nvl(c_Attributes_Rec.attribute_value_id, 0);
872 end;
873 end if;
874
875 end loop;
876
877 if (((g_adjustment_date is null) and (l_adjdate_value_id <> 0)) or
878 ((g_hiredate is null) and (l_hiredate_value_id <> 0))) then
879 begin
880
881 for c_AttrVal_Rec in c_AttrVal loop
882
883 if c_AttrVal_Rec.attribute_value_id = l_adjdate_value_id then
884 g_adjustment_date := fnd_date.canonical_to_date(c_AttrVal_Rec.attribute_value);
885 elsif c_AttrVal_Rec.attribute_value_id = l_hiredate_value_id then
886 g_hiredate := fnd_date.canonical_to_date(c_AttrVal_Rec.attribute_value);
887 end if;
888
889 end loop;
890
891 end;
892 end if;
893
894
895 -- Initialize API return status to success
896
897 p_return_status := FND_API.G_RET_STS_SUCCESS;
898
899 EXCEPTION
900
901 when FND_API.G_EXC_ERROR then
902 /* Bug 4075170 Start */
903 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Last Status::>'||l_debug_info);
904 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Parameter Info::'||l_param_info);
905 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error Type:: FND_API.G_EXC_ERROR');
906 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Cache_Named_Attribute_Values API '
907 ||'failed due to the following error');
908 FND_FILE.PUT_LINE(FND_FILE.LOG, sqlerrm);
909 /* Bug 4075170 End */
910
911 p_return_status := FND_API.G_RET_STS_ERROR;
912
913 when FND_API.G_EXC_UNEXPECTED_ERROR then
914 /* Bug 4075170 Start */
915 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Last Status::>'||l_debug_info);
916 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Parameter Info::'||l_param_info);
917 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error Type:: FND_API.G_EXC_UNEXPECTED_ERROR');
918 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Cache_Named_Attribute_Values API '
919 ||'failed due to the following error');
920 FND_FILE.PUT_LINE(FND_FILE.LOG, sqlerrm);
921 /* Bug 4075170 End */
922
923 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
924
925 when OTHERS then
926 /* Bug 4075170 Start */
927 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Last Status::>'||l_debug_info);
928 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Parameter Info::'||l_param_info);
929 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error Type:: WHEN OTHERS');
930 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Cache_Named_Attribute_Values API '
931 ||'failed due to the following error');
932 FND_FILE.PUT_LINE(FND_FILE.LOG, sqlerrm);
933 /* Bug 4075170 End */
934
935 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
936
937 END Cache_Named_Attribute_Values;
938
939 /* ----------------------------------------------------------------------- */
940
941 -- Return the conversion factor from the HRMS Period Type (p_hrms_period_type)
942 -- to the PSB Budget Period Type (p_budget_period_type)
943
944 PROCEDURE HRMS_Factor
945 ( p_return_status OUT NOCOPY VARCHAR2,
946 p_hrms_period_type IN VARCHAR2,
947 p_budget_period_type IN VARCHAR2,
948 p_position_name IN VARCHAR2,
949 p_element_name IN VARCHAR2,
950 p_start_date IN DATE,
951 p_end_date IN DATE,
952 p_factor OUT NOCOPY NUMBER
953 ) IS
954
955 l_factor NUMBER;
956
957 cursor c_Factor is
958 select factor
959 from PSB_HRMS_FACTORS
960 where hrms_period_type = p_hrms_period_type
961 and budget_period_type = p_budget_period_type;
962
963 BEGIN
964
965 for c_Factor_Rec in c_Factor loop
966 l_factor := c_Factor_Rec.factor;
967 end loop;
968
969 if l_factor is null then
970 message_token('HRMS_PERIOD', p_hrms_period_type);
971 message_token('PSB_PERIOD', p_budget_period_type);
972 message_token('POSITION', p_position_name);
973 message_token('ELEMENT', p_element_name);
974 message_token('START_DATE', p_start_date);
975 message_token('END_DATE', p_end_date);
976 add_message('PSB', 'PSB_FACTOR_NOT_DEFINED');
977 raise FND_API.G_EXC_ERROR;
978 end if;
979
980 p_factor := l_factor;
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 when FND_API.G_EXC_UNEXPECTED_ERROR then
994 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
995
996 when OTHERS then
997 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
998
999 END HRMS_Factor;
1000
1001 /* ----------------------------------------------------------------------- */
1002
1003 -- Cache Salary Distribution for a Position for specific date range
1004
1005 PROCEDURE Cache_Salary_Dist
1006 ( p_return_status OUT NOCOPY VARCHAR2,
1007 p_worksheet_id IN NUMBER,
1008 p_root_budget_group_id IN NUMBER,
1009 p_data_extract_id IN NUMBER,
1010 p_flex_code IN NUMBER,
1011 p_position_id IN NUMBER,
1012 p_position_name IN VARCHAR2,
1013 p_start_date IN DATE,
1014 p_end_date IN DATE
1015 ) IS
1016
1017 l_saldist_found BOOLEAN := FALSE;
1018 l_budget_group_found BOOLEAN := FALSE;
1019
1020 l_concat_segments VARCHAR2(2000);
1021
1022 l_init_index BINARY_INTEGER;
1023
1024 l_return_status VARCHAR2(1);
1025
1026 /* Bug No 2782604 Start */
1027 -- changed the query from table 'psb_position_pay_distributions' to
1028 -- view 'psb_positions_pay_distrs_v'
1029 -- commented the worksheet_id filter the query since it is already present in the view
1030 cursor c_WSDist is
1031 select code_combination_id,
1032 distribution_percent,
1033 effective_start_date,
1034 effective_end_date
1035 from PSB_POSITION_PAY_DISTRIBUTIONS a
1036 where position_id = p_position_id
1037 and chart_of_accounts_id = p_flex_code
1038 and code_combination_id is not null
1039 and ((worksheet_id = p_worksheet_id) or (worksheet_id is null
1040 and not exists
1041 (select 1
1042 from psb_position_pay_distributions c
1043 where (
1044 ( nvl(c.effective_start_date, p_end_date + 1)
1045 between nvl(a.effective_start_date, p_end_date)
1046 and nvl(a.effective_end_date, nvl(p_end_date, c.effective_start_date)))
1047 OR ( nvl(a.effective_start_date, p_end_date + 1)
1048 between nvl(c.effective_start_date, p_end_date)
1049 and nvl(c.effective_end_date, nvl(p_end_date, a.effective_start_date)))
1050 )
1051 and c.position_id = a.position_id
1052 and c.chart_of_accounts_id = p_flex_code
1053 and c.code_combination_id is not null
1054 and c.worksheet_id = p_worksheet_id
1055 )))
1056 -- commented for bug 3216145
1057 -- if there exists worksheet specific records
1058 -- pick up the data
1059 /*
1060 and (((p_end_date is not null)
1061 and (((effective_start_date <= p_end_date)
1062 and (effective_end_date is null))
1063 or ((effective_start_date between p_start_date and p_end_date)
1064 or (effective_end_date between p_start_date and p_end_date)
1065 or ((effective_start_date < p_start_date)
1066 and (effective_end_date > p_end_date)))))
1067 or ((p_end_date is null)
1068 and (nvl(effective_end_date, p_start_date) >= p_start_date))) */
1069
1070 order by distribution_percent desc;
1071 /* Bug No 2782604 End */
1072
1073 cursor c_Dist is
1074 select code_combination_id,
1075 distribution_percent,
1076 effective_start_date,
1077 effective_end_date
1078 from PSB_POSITION_PAY_DISTRIBUTIONS a
1079 where code_combination_id is not null
1080 /* Bug No 2747205 Start */
1081 and chart_of_accounts_id = p_flex_code
1082 and (worksheet_id is null
1083 and not exists
1084 (select 1
1085 from psb_position_pay_distributions c
1086 where (
1087 ( nvl(c.effective_start_date, p_end_date + 1)
1088 between nvl(a.effective_start_date, p_end_date)
1089 and nvl(a.effective_end_date, nvl(p_end_date, c.effective_start_date)))
1090 OR ( nvl(a.effective_start_date, p_end_date + 1)
1091 between nvl(c.effective_start_date, p_end_date)
1092 and nvl(c.effective_end_date, nvl(p_end_date, a.effective_start_date)))
1093 )
1094 and c.position_id = a.position_id
1095 and c.chart_of_accounts_id = p_flex_code
1096 and c.code_combination_id is null
1097 and c.worksheet_id = p_worksheet_id
1098 ))
1099 -- and worksheet_id is null
1100 -- and chart_of_accounts_id = p_flex_code
1101 -- and (((p_end_date is not null)
1102 -- and (((effective_start_date <= p_end_date)
1103 -- and (effective_end_date is null))
1104 -- or ((effective_start_date between p_start_date and p_end_date)
1105 -- or (effective_end_date between p_start_date and p_end_date)
1106 -- or ((effective_start_date < p_start_date)
1107 -- and (effective_end_date > p_end_date)))))
1108 -- or ((p_end_date is null)
1109 -- and (nvl(effective_end_date, p_start_date) >= p_start_date)))
1110 /* Bug No 2747205 End */
1111 and position_id = p_position_id
1112 order by distribution_percent desc;
1113
1114 cursor c_Budget_Group (CCID NUMBER) is
1115 select a.budget_group_id,
1116 b.num_proposed_years
1117 from PSB_SET_RELATIONS a,
1118 PSB_BUDGET_GROUPS b,
1119 PSB_BUDGET_ACCOUNTS c
1120 where a.budget_group_id = b.budget_group_id
1121 and b.effective_start_date <= PSB_WS_ACCT1.g_startdate_pp
1122 and (b.effective_end_date is null
1123 or b.effective_end_date >= PSB_WS_ACCT1.g_enddate_cy)
1124 and b.budget_group_type = 'R'
1125 and ((b.budget_group_id = p_root_budget_group_id) or
1126 (b.root_budget_group_id = p_root_budget_group_id))
1127 and a.account_position_set_id = c.account_position_set_id
1128 and c.code_combination_id = CCID;
1129
1130 BEGIN
1131
1132 Initialize_Salary_Dist;
1133
1134 for c_Dist_Rec in c_WSDist loop
1135
1136 l_saldist_found := TRUE;
1137
1138 if nvl(g_salary_budget_group_id, FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM then
1139 begin
1140
1141 for c_Budget_Group_Rec in c_Budget_Group (c_Dist_Rec.code_combination_id) loop
1142 g_salary_budget_group_id := c_Budget_Group_Rec.budget_group_id;
1143 g_budget_group_numyrs := c_Budget_Group_Rec.num_proposed_years;
1144 l_budget_group_found := TRUE;
1145 end loop;
1146
1147 -- Budget Group for a Position is the Budget Group assigned to the CCID with
1148 -- the maximum distribution percentage
1149
1150 if not l_budget_group_found then
1151 begin
1152
1153 l_concat_segments := FND_FLEX_EXT.Get_Segs
1154 (application_short_name => 'SQLGL',
1155 key_flex_code => 'GL#',
1156 structure_number => p_flex_code,
1157 combination_id => c_Dist_Rec.code_combination_id);
1158
1159 message_token('CCID', l_concat_segments);
1160 message_token('POSITION', p_position_name);
1161 add_message('PSB', 'PSB_CANNOT_ASSIGN_BUDGET_GROUP');
1162 raise FND_API.G_EXC_ERROR;
1163
1164 end;
1165 end if;
1166
1167 end;
1168 end if;
1169
1170 g_num_salary_dist := g_num_salary_dist + 1;
1171
1172 g_salary_dist(g_num_salary_dist).ccid := c_Dist_Rec.code_combination_id;
1173 g_salary_dist(g_num_salary_dist).percent := c_Dist_Rec.distribution_percent;
1174 g_salary_dist(g_num_salary_dist).start_date := c_Dist_Rec.effective_start_date;
1175 g_salary_dist(g_num_salary_dist).end_date := c_Dist_Rec.effective_end_date;
1176
1177 end loop;
1178
1179 if not l_saldist_found then
1180 begin
1181
1182 for c_Dist_Rec in c_Dist loop
1183
1184 l_saldist_found := TRUE;
1185
1186 if nvl(g_salary_budget_group_id, FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM then
1187 begin
1188
1189 for c_Budget_Group_Rec in c_Budget_Group (c_Dist_Rec.code_combination_id) loop
1190 g_salary_budget_group_id := c_Budget_Group_Rec.budget_group_id;
1191 g_budget_group_numyrs := c_Budget_Group_Rec.num_proposed_years;
1192 l_budget_group_found := TRUE;
1193 end loop;
1194
1195 -- Budget Group for a Position is the Budget Group assigned to the CCID with
1196 -- the maximum distribution percentage
1197
1198 if not l_budget_group_found then
1199 begin
1200
1201 l_concat_segments := FND_FLEX_EXT.Get_Segs
1202 (application_short_name => 'SQLGL',
1203 key_flex_code => 'GL#',
1204 structure_number => p_flex_code,
1205 combination_id => c_Dist_Rec.code_combination_id);
1206
1207 message_token('CCID', l_concat_segments);
1208 message_token('POSITION', p_position_name);
1209 add_message('PSB', 'PSB_CANNOT_ASSIGN_BUDGET_GROUP');
1210 raise FND_API.G_EXC_ERROR;
1211
1212 end;
1213 end if;
1214
1215 end;
1216 end if;
1217
1218 g_num_salary_dist := g_num_salary_dist + 1;
1219
1220 g_salary_dist(g_num_salary_dist).ccid := c_Dist_Rec.code_combination_id;
1221 g_salary_dist(g_num_salary_dist).percent := c_Dist_Rec.distribution_percent;
1222 g_salary_dist(g_num_salary_dist).start_date := c_Dist_Rec.effective_start_date;
1223 g_salary_dist(g_num_salary_dist).end_date := c_Dist_Rec.effective_end_date;
1224
1225 end loop;
1226
1227 end;
1228 end if;
1229
1230 -- If Salary Distribution is not found return an error. Salary Distribution is
1231 -- needed to create a Worksheet specific instance of a Position (identified by
1232 -- position_line_id)
1233
1234 if not l_saldist_found then
1235 message_token('POSITION', p_position_name);
1236 message_token('START_DATE', p_start_date);
1237 message_token('END_DATE', p_end_date);
1238 add_message('PSB', 'PSB_NO_SALARY_DISTRIBUTION');
1239 raise FND_API.G_EXC_ERROR;
1240 end if;
1241
1242 if p_flex_code <> nvl(PSB_WS_ACCT1.g_flex_code, FND_API.G_MISS_NUM) then
1243 begin
1244
1245 PSB_WS_ACCT1.Flex_Info
1246 (p_flex_code => p_flex_code,
1247 p_return_status => l_return_status);
1248
1249 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1250 raise FND_API.G_EXC_ERROR;
1251 end if;
1252
1253 end;
1254 end if;
1255
1256
1257 -- Initialize API return status to success
1258
1259 p_return_status := FND_API.G_RET_STS_SUCCESS;
1260
1261
1262 EXCEPTION
1263
1264 when FND_API.G_EXC_ERROR then
1265 p_return_status := FND_API.G_RET_STS_ERROR;
1266
1267 when FND_API.G_EXC_UNEXPECTED_ERROR then
1268 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1269
1270 when OTHERS then
1271 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1272
1273 END Cache_Salary_Dist;
1274
1275 /* ----------------------------------------------------------------------- */
1276
1277 -- Create Worksheet specific instance of a Position. A unique Position Line
1278 -- ID is created for a Position for every Global Worksheet and Local Copy
1279 -- of a Worksheet. This also creates an entry in the Position Matrix table
1280 -- (PSB_WS_LINES_POSITIONS) for the current Worksheet. Entries for the Parent
1281 -- Worksheets, for Distributed Worksheets, must be created using the
1282 -- Worksheet Operations APIs
1283
1284 PROCEDURE Create_Position_Lines
1285 ( p_api_version IN NUMBER,
1286 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_NONE,
1287 p_return_status OUT NOCOPY VARCHAR2,
1288 p_position_line_id OUT NOCOPY NUMBER,
1289 p_worksheet_id IN NUMBER,
1290 p_position_id IN NUMBER,
1291 p_budget_group_id IN NUMBER,
1292 p_copy_of_position_line_id IN NUMBER := FND_API.G_MISS_NUM
1293 ) IS
1294
1295 l_api_name CONSTANT VARCHAR2(30) := 'Create_Position_Lines';
1296 l_api_version CONSTANT NUMBER := 1.0;
1297
1298 l_userid NUMBER;
1299 l_loginid NUMBER;
1300
1301 l_instance_exists VARCHAR2(1) := FND_API.G_FALSE;
1302
1303 l_poslineid NUMBER;
1304 l_budget_group_id NUMBER;
1305
1306 l_return_status VARCHAR2(1);
1307
1308 l_msg_count NUMBER;
1309 l_msg_data VARCHAR2(2000);
1310
1311 cursor c_PosLine is
1312 select b.position_line_id,
1313 b.budget_group_id
1314 from PSB_WS_LINES_POSITIONS a,
1315 PSB_WS_POSITION_LINES b
1316 where a.position_line_id = b.position_line_id
1317 and a.worksheet_id = g_global_worksheet_id
1318 and b.position_id = p_position_id;
1319
1320 cursor c_Seq is
1321 select psb_ws_position_lines_s.nextval PosLineID
1322 from dual;
1323
1324 BEGIN
1325
1326 -- Standard call to check for call compatibility
1327
1328 if not FND_API.Compatible_API_Call (l_api_version,
1329 p_api_version,
1330 l_api_name,
1331 G_PKG_NAME)
1332 then
1333 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1334 end if;
1335
1336 l_userid := FND_GLOBAL.USER_ID;
1337 l_loginid := FND_GLOBAL.LOGIN_ID;
1338
1339 if g_global_worksheet_id is null then
1340 begin
1341
1342 for c_WS_Rec in c_WS (p_worksheet_id) loop
1343
1344 g_local_copy_flag := c_WS_Rec.local_copy_flag;
1345
1346 if c_WS_Rec.local_copy_flag = 'Y' then
1347 g_global_worksheet_id := p_worksheet_id;
1348 else
1349 g_global_worksheet_id := c_WS_Rec.global_worksheet_id;
1350 end if;
1351
1352 g_budget_calendar_id := c_WS_Rec.budget_calendar_id;
1353
1354 end loop;
1355
1356 end;
1357 end if;
1358
1359 if g_budget_calendar_id <> nvl(PSB_WS_ACCT1.g_budget_calendar_id, FND_API.G_MISS_NUM) then
1360 begin
1361
1362 PSB_WS_ACCT1.Cache_Budget_Calendar
1363 (p_return_status => l_return_status,
1364 p_budget_calendar_id => g_budget_calendar_id);
1365
1366 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1367 raise FND_API.G_EXC_ERROR;
1368 end if;
1369
1370 end;
1371 end if;
1372
1373 for c_PosLine_Rec in c_PosLine loop
1374 l_poslineid := c_PosLine_Rec.position_line_id;
1375 l_budget_group_id := c_PosLine_Rec.budget_group_id;
1376 l_instance_exists := FND_API.G_TRUE;
1377 end loop;
1378
1379 if FND_API.to_Boolean(l_instance_exists) then
1380 begin
1381
1382 if p_budget_group_id <> l_budget_group_id then
1383 begin
1384
1385 update PSB_WS_POSITION_LINES
1386 set budget_group_id = p_budget_group_id,
1387 copy_of_position_line_id = decode(p_copy_of_position_line_id, FND_API.G_MISS_NUM, null, p_copy_of_position_line_id),
1388 last_update_date = sysdate,
1389 last_updated_by = l_userid,
1390 last_update_login = l_loginid
1391 where position_line_id = l_poslineid;
1392
1393 delete from PSB_WS_LINES_POSITIONS
1394 where position_line_id = l_poslineid;
1395
1396 if g_local_copy_flag = 'Y' then
1397 begin
1398
1399 Create_Position_Matrix
1400 (p_api_version => 1.0,
1401 p_return_status => l_return_status,
1402 p_worksheet_id => p_worksheet_id,
1403 p_position_line_id => l_poslineid);
1404
1405 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1406 raise FND_API.G_EXC_ERROR;
1407 end if;
1408
1409 end;
1410 else
1411 begin
1412
1413 for c_Distribute_WS_Rec in PSB_WS_ACCT1.c_Distribute_WS (g_global_worksheet_id,
1414 p_budget_group_id,
1415 PSB_WS_ACCT1.g_startdate_pp,
1416 PSB_WS_ACCT1.g_enddate_cy) loop
1417
1418 Create_Position_Matrix
1419 (p_api_version => 1.0,
1420 p_return_status => l_return_status,
1421 p_worksheet_id => c_Distribute_WS_Rec.worksheet_id,
1422 p_position_line_id => l_poslineid);
1423
1424 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1425 raise FND_API.G_EXC_ERROR;
1426 end if;
1427
1428 end loop;
1429
1430 end;
1431 end if;
1432
1433 end;
1434 end if;
1435
1436 end;
1437 else
1438 begin
1439
1440 for c_Seq_Rec in c_Seq loop
1441 l_poslineid := c_Seq_Rec.PosLineID;
1442 end loop;
1443
1444 insert into PSB_WS_POSITION_LINES
1445 (position_line_id,
1446 position_id,
1447 budget_group_id,
1448 copy_of_position_line_id,
1449 last_update_date,
1450 last_updated_by,
1451 last_update_login,
1452 created_by,
1453 creation_date)
1454 values (l_poslineid,
1455 p_position_id,
1456 p_budget_group_id,
1457 decode(p_copy_of_position_line_id, FND_API.G_MISS_NUM, null, p_copy_of_position_line_id),
1458 sysdate,
1459 l_userid,
1460 l_loginid,
1461 l_userid,
1462 sysdate);
1463
1464 if g_local_copy_flag = 'Y' then
1465 begin
1466
1467 Create_Position_Matrix
1468 (p_api_version => 1.0,
1469 p_return_status => l_return_status,
1470 p_worksheet_id => p_worksheet_id,
1471 p_position_line_id => l_poslineid);
1472
1473 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1474 raise FND_API.G_EXC_ERROR;
1475 end if;
1476
1477 end;
1478 else
1479 begin
1480
1481 for c_Distribute_WS_Rec in PSB_WS_ACCT1.c_Distribute_WS (g_global_worksheet_id,
1482 p_budget_group_id,
1483 PSB_WS_ACCT1.g_startdate_pp,
1484 PSB_WS_ACCT1.g_enddate_cy) loop
1485
1486 Create_Position_Matrix
1487 (p_api_version => 1.0,
1488 p_return_status => l_return_status,
1489 p_worksheet_id => c_Distribute_WS_Rec.worksheet_id,
1490 p_position_line_id => l_poslineid);
1491
1492 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1493 raise FND_API.G_EXC_ERROR;
1494 end if;
1495
1496 end loop;
1497
1498 end;
1499 end if;
1500
1501 end;
1502 end if;
1503
1504 p_position_line_id := l_poslineid;
1505
1506
1507 -- Initialize API return status to success
1508
1509 p_return_status := FND_API.G_RET_STS_SUCCESS;
1510
1511
1512 EXCEPTION
1513
1514 when FND_API.G_EXC_ERROR then
1515 p_return_status := FND_API.G_RET_STS_ERROR;
1516 FND_MSG_PUB.Count_And_Get (p_count => l_msg_count,
1517 p_data => l_msg_data);
1518
1519
1520 when FND_API.G_EXC_UNEXPECTED_ERROR then
1521 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1522 FND_MSG_PUB.Count_And_Get (p_count => l_msg_count,
1523 p_data => l_msg_data);
1524
1525 when OTHERS then
1526 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1527
1528 if FND_MSG_PUB.Check_Msg_Level
1529 (p_message_level => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1530 then
1531 FND_MSG_PUB.Add_Exc_Msg
1532 (p_pkg_name => G_PKG_NAME,
1533 p_procedure_name => l_api_name);
1534 end if;
1535
1536 FND_MSG_PUB.Count_And_Get (p_count => l_msg_count,
1537 p_data => l_msg_data);
1538
1539 END Create_Position_Lines;
1540
1541 /* ----------------------------------------------------------------------- */
1542
1543 -- Create or Update entries in the Position Matrix table
1544 -- (PSB_WS_LINES_POSITIONS)
1545
1546 PROCEDURE Create_Position_Matrix
1547 ( p_api_version IN NUMBER,
1548 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_NONE,
1549 p_return_status OUT NOCOPY VARCHAR2,
1550 p_worksheet_id IN NUMBER,
1551 p_position_line_id IN NUMBER,
1552 p_freeze_flag IN VARCHAR2 := FND_API.G_FALSE,
1553 p_view_line_flag IN VARCHAR2 := FND_API.G_TRUE
1554 ) IS
1555
1556 l_api_name CONSTANT VARCHAR2(30) := 'Create_Position_Matrix';
1557 l_api_version CONSTANT NUMBER := 1.0;
1558
1559 l_userid NUMBER;
1560 l_loginid NUMBER;
1561
1562 BEGIN
1563
1564 -- Standard call to check for call compatibility
1565
1566 if not FND_API.Compatible_API_Call (l_api_version,
1567 p_api_version,
1568 l_api_name,
1569 G_PKG_NAME)
1570 then
1571 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1572 end if;
1573
1574 l_userid := FND_GLOBAL.USER_ID;
1575 l_loginid := FND_GLOBAL.LOGIN_ID;
1576
1577 update PSB_WS_LINES_POSITIONS
1578 set freeze_flag = decode(p_freeze_flag, FND_API.G_FALSE, null, FND_API.G_TRUE, 'Y', p_freeze_flag),
1579 view_line_flag = decode(p_view_line_flag, FND_API.G_TRUE, 'Y', FND_API.G_FALSE, null, p_view_line_flag),
1580 last_update_date = sysdate,
1581 last_updated_by = l_userid,
1582 last_update_login = l_loginid
1583 where position_line_id = p_position_line_id
1584 and worksheet_id = p_worksheet_id;
1585
1586 if SQL%NOTFOUND then
1587 begin
1588
1589 insert into PSB_WS_LINES_POSITIONS
1590 (worksheet_id,
1591 position_line_id,
1592 freeze_flag,
1593 view_line_flag,
1594 last_update_date,
1595 last_updated_by,
1596 last_update_login,
1597 created_by,
1598 creation_date)
1599 values
1600 (p_worksheet_id,
1601 p_position_line_id,
1602 decode(p_freeze_flag, FND_API.G_FALSE, null, FND_API.G_TRUE, 'Y', p_freeze_flag),
1603 decode(p_view_line_flag, FND_API.G_TRUE, 'Y', FND_API.G_FALSE, null, p_view_line_flag),
1604 sysdate,
1605 l_userid,
1606 l_loginid,
1607 l_userid,
1608 sysdate);
1609
1610 end;
1611 end if;
1612
1613
1614 -- Initialize API return status to success
1615
1616 p_return_status := FND_API.G_RET_STS_SUCCESS;
1617
1618
1619 EXCEPTION
1620
1621 when FND_API.G_EXC_ERROR then
1622 p_return_status := FND_API.G_RET_STS_ERROR;
1623
1624 when FND_API.G_EXC_UNEXPECTED_ERROR then
1625 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1626
1627 when OTHERS then
1628 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1629
1630 if FND_MSG_PUB.Check_Msg_Level
1631 (p_message_level => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1632 then
1633 FND_MSG_PUB.Add_Exc_Msg
1634 (p_pkg_name => G_PKG_NAME,
1635 p_procedure_name => l_api_name);
1636 end if;
1637
1638 END Create_Position_Matrix;
1639
1640 /* ----------------------------------------------------------------------- */
1641
1642 -- Set p_recalculate_flag to true when manually creating new FTE/SP entries
1643 -- (from the Modify Worksheet module - Form, Spreadsheet or OFA).
1644 -- If new FTE/SP entry overlaps with existing entry for same SP, the Element
1645 -- Costs, Annual FTE, Account Distributions for the existing entry are prorated
1646 -- to reflect the new entry. If FTE/SP entry does not overlap with any existing
1647 -- entry, new Element Costs, Annual FTE, Account Distributions are created by
1648 -- prorating the entries for the base SP
1649 -- Proration of Element Costs and Account Distributions are done only for
1650 -- recurring Elements since non-recurring Elements are by definition independent
1651 -- of FTE
1652
1653 PROCEDURE Create_FTE_Lines
1654 ( p_api_version IN NUMBER,
1655 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_NONE,
1656 p_return_status OUT NOCOPY VARCHAR2,
1657 p_fte_line_id OUT NOCOPY NUMBER,
1658 p_check_spfl_exists IN VARCHAR2 := FND_API.G_TRUE,
1659 p_recalculate_flag IN VARCHAR2 := FND_API.G_FALSE,
1660 p_worksheet_id IN NUMBER,
1661 p_flex_mapping_set_id IN NUMBER := FND_API.G_MISS_NUM,
1662 p_position_line_id IN NUMBER,
1663 p_budget_year_id IN NUMBER,
1664 p_budget_group_id IN NUMBER := FND_API.G_MISS_NUM,
1665 p_annual_fte IN NUMBER := FND_API.G_MISS_NUM,
1666 p_service_package_id IN NUMBER,
1667 p_stage_set_id IN NUMBER,
1668 p_start_stage_seq IN NUMBER := FND_API.G_MISS_NUM,
1669 p_current_stage_seq IN NUMBER,
1670 p_end_stage_seq IN NUMBER := FND_API.G_MISS_NUM,
1671 p_period_fte IN PSB_WS_ACCT1.g_prdamt_tbl_type
1672 ) IS
1673
1674 l_api_name CONSTANT VARCHAR2(30) := 'Create_FTE_Lines';
1675 l_api_version CONSTANT NUMBER := 1.0;
1676
1677 l_userid NUMBER;
1678 l_loginid NUMBER;
1679
1680 l_budget_calendar_id NUMBER;
1681 l_budget_group_id NUMBER;
1682 l_rounding_factor NUMBER;
1683 l_data_extract_id NUMBER;
1684
1685 l_business_group_id NUMBER;
1686
1687 l_num_budget_periods NUMBER;
1688
1689 l_ftelineid NUMBER;
1690
1691 l_fte NUMBER;
1692 l_period_fte PSB_WS_ACCT1.g_prdamt_tbl_type;
1693 l_annual_fte NUMBER;
1694 /*For Bug No : 2811698 Start*/
1695 l_total_fte NUMBER := 0;
1696 /*For Bug No : 2811698 End*/
1697
1698 l_spflid NUMBER;
1699 l_spfte PSB_WS_ACCT1.g_prdamt_tbl_type;
1700 l_spannual_fte NUMBER;
1701 l_spfl_exists VARCHAR2(1) := FND_API.G_FALSE;
1702
1703 l_global_wsid NUMBER;
1704 l_base_spid NUMBER;
1705
1706 l_start_stage_seq NUMBER;
1707
1708 l_year_index BINARY_INTEGER;
1709 l_index BINARY_INTEGER;
1710
1711 l_return_status VARCHAR2(1);
1712 l_msg_data VARCHAR2(2000);
1713 l_msg_count NUMBER;
1714
1715 BEGIN
1716
1717 -- Standard call to check for call compatibility
1718
1719 if not FND_API.Compatible_API_Call (l_api_version,
1720 p_api_version,
1721 l_api_name,
1722 G_PKG_NAME)
1723 then
1724 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1725 end if;
1726
1727 l_userid := FND_GLOBAL.USER_ID;
1728 l_loginid := FND_GLOBAL.LOGIN_ID;
1729
1730 --Total cost would be the sum of all the periods
1731 /*For Bug No : 2811698 Start*/
1732 for l_index in 1..PSB_WS_ACCT1.g_max_num_amounts loop
1733 l_total_fte := l_total_fte + nvl(p_period_fte(l_index),0);
1734 end loop;
1735 /*For Bug No : 2811698 End*/
1736
1737 if p_start_stage_seq = FND_API.G_MISS_NUM then
1738 l_start_stage_seq := p_current_stage_seq;
1739 else
1740 l_start_stage_seq := p_start_stage_seq;
1741 end if;
1742
1743 if FND_API.to_Boolean(p_check_spfl_exists) then
1744 begin
1745
1746 -- For a Service Package overlap try to identify the target FTE Line
1747
1748 for c_Wfl_Rec in c_Wfl_SP (p_position_line_id, p_budget_year_id, p_service_package_id,
1749 p_stage_set_id, p_current_stage_seq) loop
1750
1751 l_spflid := c_Wfl_Rec.fte_line_id;
1752 l_spfte(1) := c_Wfl_Rec.period1_fte; l_spfte(2) := c_Wfl_Rec.period2_fte;
1753 l_spfte(3) := c_Wfl_Rec.period3_fte; l_spfte(4) := c_Wfl_Rec.period4_fte;
1754 l_spfte(5) := c_Wfl_Rec.period5_fte; l_spfte(6) := c_Wfl_Rec.period6_fte;
1755 l_spfte(7) := c_Wfl_Rec.period7_fte; l_spfte(8) := c_Wfl_Rec.period8_fte;
1756 l_spfte(9) := c_Wfl_Rec.period9_fte; l_spfte(10) := c_Wfl_Rec.period10_fte;
1757 l_spfte(11) := c_Wfl_Rec.period11_fte; l_spfte(12) := c_Wfl_Rec.period12_fte;
1758 l_spfte(13) := c_Wfl_Rec.period13_fte; l_spfte(14) := c_Wfl_Rec.period14_fte;
1759 l_spfte(15) := c_Wfl_Rec.period15_fte; l_spfte(16) := c_Wfl_Rec.period16_fte;
1760 l_spfte(17) := c_Wfl_Rec.period17_fte; l_spfte(18) := c_Wfl_Rec.period18_fte;
1761 l_spfte(19) := c_Wfl_Rec.period19_fte; l_spfte(20) := c_Wfl_Rec.period20_fte;
1762 l_spfte(21) := c_Wfl_Rec.period21_fte; l_spfte(22) := c_Wfl_Rec.period22_fte;
1763 l_spfte(23) := c_Wfl_Rec.period23_fte; l_spfte(24) := c_Wfl_Rec.period24_fte;
1764 l_spfte(25) := c_Wfl_Rec.period25_fte; l_spfte(26) := c_Wfl_Rec.period26_fte;
1765 l_spfte(27) := c_Wfl_Rec.period27_fte; l_spfte(28) := c_Wfl_Rec.period28_fte;
1766 l_spfte(29) := c_Wfl_Rec.period29_fte; l_spfte(30) := c_Wfl_Rec.period30_fte;
1767 l_spfte(31) := c_Wfl_Rec.period31_fte; l_spfte(32) := c_Wfl_Rec.period32_fte;
1768 l_spfte(33) := c_Wfl_Rec.period33_fte; l_spfte(34) := c_Wfl_Rec.period34_fte;
1769 l_spfte(35) := c_Wfl_Rec.period35_fte; l_spfte(36) := c_Wfl_Rec.period36_fte;
1770 l_spfte(37) := c_Wfl_Rec.period37_fte; l_spfte(38) := c_Wfl_Rec.period38_fte;
1771 l_spfte(39) := c_Wfl_Rec.period39_fte; l_spfte(40) := c_Wfl_Rec.period40_fte;
1772 l_spfte(41) := c_Wfl_Rec.period41_fte; l_spfte(42) := c_Wfl_Rec.period42_fte;
1773 l_spfte(43) := c_Wfl_Rec.period43_fte; l_spfte(44) := c_Wfl_Rec.period44_fte;
1774 l_spfte(45) := c_Wfl_Rec.period45_fte; l_spfte(46) := c_Wfl_Rec.period46_fte;
1775 l_spfte(47) := c_Wfl_Rec.period47_fte; l_spfte(48) := c_Wfl_Rec.period48_fte;
1776 l_spfte(49) := c_Wfl_Rec.period49_fte; l_spfte(50) := c_Wfl_Rec.period50_fte;
1777 l_spfte(51) := c_Wfl_Rec.period51_fte; l_spfte(52) := c_Wfl_Rec.period52_fte;
1778 l_spfte(53) := c_Wfl_Rec.period53_fte; l_spfte(54) := c_Wfl_Rec.period54_fte;
1779 l_spfte(55) := c_Wfl_Rec.period55_fte; l_spfte(56) := c_Wfl_Rec.period56_fte;
1780 l_spfte(57) := c_Wfl_Rec.period57_fte; l_spfte(58) := c_Wfl_Rec.period58_fte;
1781 l_spfte(59) := c_Wfl_Rec.period59_fte; l_spfte(60) := c_Wfl_Rec.period60_fte;
1782 l_spannual_fte := c_Wfl_Rec.annual_fte;
1783
1784 l_spfl_exists := FND_API.G_TRUE;
1785
1786 end loop;
1787
1788 end;
1789 end if;
1790
1791 if ((p_annual_fte = FND_API.G_MISS_NUM) or
1792 (FND_API.to_Boolean(l_spfl_exists)) or
1793 (FND_API.to_Boolean(p_recalculate_flag))) then
1794 begin
1795
1796 for c_WS_Rec in c_WS (p_worksheet_id) loop
1797 l_global_wsid := c_WS_Rec.global_worksheet_id;
1798 l_budget_calendar_id := c_WS_Rec.budget_calendar_id;
1799 l_budget_group_id := c_WS_Rec.budget_group_id;
1800 l_rounding_factor := c_WS_Rec.rounding_factor;
1801 l_data_extract_id := c_WS_Rec.data_extract_id;
1802 end loop;
1803
1804 for c_BG_Rec in c_BG (l_budget_group_id) loop
1805 l_business_group_id := c_BG_Rec.business_group_id;
1806 end loop;
1807
1808 if l_budget_calendar_id <> nvl(PSB_WS_ACCT1.g_budget_calendar_id, FND_API.G_MISS_NUM) then
1809 begin
1810
1811 PSB_WS_ACCT1.Cache_Budget_Calendar
1812 (p_return_status => l_return_status,
1813 p_budget_calendar_id => l_budget_calendar_id);
1814
1815 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1816 raise FND_API.G_EXC_ERROR;
1817 end if;
1818
1819 end;
1820 end if;
1821
1822 for l_year_index in 1..PSB_WS_ACCT1.g_num_budget_years loop
1823
1824 -- Find number of Budget Periods in the Budget Year : this is used to compute the Annual FTE
1825
1826 if PSB_WS_ACCT1.g_budget_years(l_year_index).budget_year_id = p_budget_year_id then
1827 l_num_budget_periods := PSB_WS_ACCT1.g_budget_years(l_year_index).num_budget_periods;
1828 end if;
1829
1830 end loop;
1831
1832 end;
1833 end if;
1834
1835 -- Annual FTE is computed by summing up the Period FTEs and dividing by the number of
1836 -- budget periods in the Budget Year
1837
1838 l_annual_fte := 0;
1839
1840 for l_index in 1..PSB_WS_ACCT1.g_max_num_amounts loop
1841
1842 if FND_API.to_Boolean(l_spfl_exists) then
1843 begin
1844
1845 l_fte := nvl(p_period_fte(l_index), 0) + nvl(l_spfte(l_index), 0);
1846
1847 end;
1848 else
1849 l_fte := nvl(p_period_fte(l_index), 0);
1850 end if;
1851
1852 if l_fte = 0 then
1853 l_period_fte(l_index) := null;
1854 else
1855 l_period_fte(l_index) := l_fte;
1856 end if;
1857
1858 l_annual_fte := l_annual_fte + l_fte;
1859
1860 end loop;
1861
1862 if p_annual_fte <> FND_API.G_MISS_NUM then
1863 l_annual_fte := p_annual_fte;
1864 else
1865 begin
1866
1867 if l_num_budget_periods <> 0 then
1868 l_annual_fte := l_annual_fte / l_num_budget_periods;
1869 end if;
1870
1871 end;
1872 end if;
1873
1874 if FND_API.to_Boolean(l_spfl_exists) then
1875 begin
1876
1877 update PSB_WS_FTE_LINES
1878 set period1_fte = l_period_fte(1), period2_fte = l_period_fte(2),
1879 period3_fte = l_period_fte(3), period4_fte = l_period_fte(4),
1880 period5_fte = l_period_fte(5), period6_fte = l_period_fte(6),
1881 period7_fte = l_period_fte(7), period8_fte = l_period_fte(8),
1882 period9_fte = l_period_fte(9), period10_fte = l_period_fte(10),
1883 period11_fte = l_period_fte(11), period12_fte = l_period_fte(12),
1884 period13_fte = l_period_fte(13), period14_fte = l_period_fte(14),
1885 period15_fte = l_period_fte(15), period16_fte = l_period_fte(16),
1886 period17_fte = l_period_fte(17), period18_fte = l_period_fte(18),
1887 period19_fte = l_period_fte(19), period20_fte = l_period_fte(20),
1888 period21_fte = l_period_fte(21), period22_fte = l_period_fte(22),
1889 period23_fte = l_period_fte(23), period24_fte = l_period_fte(24),
1890 period25_fte = l_period_fte(25), period26_fte = l_period_fte(26),
1891 period27_fte = l_period_fte(27), period28_fte = l_period_fte(28),
1892 period29_fte = l_period_fte(29), period30_fte = l_period_fte(30),
1893 period31_fte = l_period_fte(31), period32_fte = l_period_fte(32),
1894 period33_fte = l_period_fte(33), period34_fte = l_period_fte(34),
1895 period35_fte = l_period_fte(35), period36_fte = l_period_fte(36),
1896 period37_fte = l_period_fte(37), period38_fte = l_period_fte(38),
1897 period39_fte = l_period_fte(39), period40_fte = l_period_fte(40),
1898 period41_fte = l_period_fte(41), period42_fte = l_period_fte(42),
1899 period43_fte = l_period_fte(43), period44_fte = l_period_fte(44),
1900 period45_fte = l_period_fte(45), period46_fte = l_period_fte(46),
1901 period47_fte = l_period_fte(47), period48_fte = l_period_fte(48),
1902 period49_fte = l_period_fte(49), period50_fte = l_period_fte(50),
1903 period51_fte = l_period_fte(51), period52_fte = l_period_fte(52),
1904 period53_fte = l_period_fte(53), period54_fte = l_period_fte(54),
1905 period55_fte = l_period_fte(55), period56_fte = l_period_fte(56),
1906 period57_fte = l_period_fte(57), period58_fte = l_period_fte(58),
1907 period59_fte = l_period_fte(59), period60_fte = l_period_fte(60),
1908 end_stage_seq = decode(p_end_stage_seq, FND_API.G_MISS_NUM, end_stage_seq, p_end_stage_seq),
1909 annual_fte = l_annual_fte,
1910 last_update_date = sysdate,
1911 last_updated_by = l_userid,
1912 last_update_login = l_loginid
1913 where fte_line_id = l_spflid;
1914
1915 end;
1916 else
1917 begin
1918
1919 update PSB_WS_FTE_LINES
1920 set period1_fte = l_period_fte(1), period2_fte = l_period_fte(2),
1921 period3_fte = l_period_fte(3), period4_fte = l_period_fte(4),
1922 period5_fte = l_period_fte(5), period6_fte = l_period_fte(6),
1923 period7_fte = l_period_fte(7), period8_fte = l_period_fte(8),
1924 period9_fte = l_period_fte(9), period10_fte = l_period_fte(10),
1925 period11_fte = l_period_fte(11), period12_fte = l_period_fte(12),
1926 period13_fte = l_period_fte(13), period14_fte = l_period_fte(14),
1927 period15_fte = l_period_fte(15), period16_fte = l_period_fte(16),
1928 period17_fte = l_period_fte(17), period18_fte = l_period_fte(18),
1929 period19_fte = l_period_fte(19), period20_fte = l_period_fte(20),
1930 period21_fte = l_period_fte(21), period22_fte = l_period_fte(22),
1931 period23_fte = l_period_fte(23), period24_fte = l_period_fte(24),
1932 period25_fte = l_period_fte(25), period26_fte = l_period_fte(26),
1933 period27_fte = l_period_fte(27), period28_fte = l_period_fte(28),
1934 period29_fte = l_period_fte(29), period30_fte = l_period_fte(30),
1935 period31_fte = l_period_fte(31), period32_fte = l_period_fte(32),
1936 period33_fte = l_period_fte(33), period34_fte = l_period_fte(34),
1937 period35_fte = l_period_fte(35), period36_fte = l_period_fte(36),
1938 period37_fte = l_period_fte(37), period38_fte = l_period_fte(38),
1939 period39_fte = l_period_fte(39), period40_fte = l_period_fte(40),
1940 period41_fte = l_period_fte(41), period42_fte = l_period_fte(42),
1941 period43_fte = l_period_fte(43), period44_fte = l_period_fte(44),
1942 period45_fte = l_period_fte(45), period46_fte = l_period_fte(46),
1943 period47_fte = l_period_fte(47), period48_fte = l_period_fte(48),
1944 period49_fte = l_period_fte(49), period50_fte = l_period_fte(50),
1945 period51_fte = l_period_fte(51), period52_fte = l_period_fte(52),
1946 period53_fte = l_period_fte(53), period54_fte = l_period_fte(54),
1947 period55_fte = l_period_fte(55), period56_fte = l_period_fte(56),
1948 period57_fte = l_period_fte(57), period58_fte = l_period_fte(58),
1949 period59_fte = l_period_fte(59), period60_fte = l_period_fte(60),
1950 end_stage_seq = decode(p_end_stage_seq, FND_API.G_MISS_NUM, end_stage_seq, p_end_stage_seq),
1951 annual_fte = l_annual_fte,
1952 last_update_date = sysdate,
1953 last_updated_by = l_userid,
1954 last_update_login = l_loginid
1955 where p_current_stage_seq between start_stage_seq and current_stage_seq
1956 and stage_set_id = p_stage_set_id
1957 and service_package_id = p_service_package_id
1958 and budget_year_id = p_budget_year_id
1959 and position_line_id = p_position_line_id;
1960
1961 end;
1962 end if;
1963
1964 if SQL%NOTFOUND then
1965 begin
1966
1967 for c_FTESeq_Rec in c_FTESeq loop
1968 l_ftelineid := c_FTESeq_Rec.FTELineID;
1969 end loop;
1970
1971 for l_index in 1..PSB_WS_ACCT1.g_max_num_amounts loop
1972
1973 l_fte := nvl(p_period_fte(l_index), 0);
1974
1975 if l_fte = 0 then
1976 l_period_fte(l_index) := null;
1977 else
1978 l_period_fte(l_index) := l_fte;
1979 end if;
1980
1981 end loop;
1982
1983 insert into PSB_WS_FTE_LINES
1984 (fte_line_id,
1985 position_line_id,
1986 budget_year_id,
1987 service_package_id,
1988 stage_set_id,
1989 start_stage_seq,
1990 current_stage_seq,
1991 end_stage_seq,
1992 period1_fte, period2_fte, period3_fte, period4_fte,
1993 period5_fte, period6_fte, period7_fte, period8_fte,
1994 period9_fte, period10_fte, period11_fte, period12_fte,
1995 period13_fte, period14_fte, period15_fte, period16_fte,
1996 period17_fte, period18_fte, period19_fte, period20_fte,
1997 period21_fte, period22_fte, period23_fte, period24_fte,
1998 period25_fte, period26_fte, period27_fte, period28_fte,
1999 period29_fte, period30_fte, period31_fte, period32_fte,
2000 period33_fte, period34_fte, period35_fte, period36_fte,
2001 period37_fte, period38_fte, period39_fte, period40_fte,
2002 period41_fte, period42_fte, period43_fte, period44_fte,
2003 period45_fte, period46_fte, period47_fte, period48_fte,
2004 period49_fte, period50_fte, period51_fte, period52_fte,
2005 period53_fte, period54_fte, period55_fte, period56_fte,
2006 period57_fte, period58_fte, period59_fte, period60_fte,
2007 annual_fte,
2008 last_update_date,
2009 last_updated_by,
2010 last_update_login,
2011 created_by,
2012 creation_date)
2013 values (l_ftelineid,
2014 p_position_line_id,
2015 p_budget_year_id,
2016 p_service_package_id,
2017 p_stage_set_id,
2018 l_start_stage_seq,
2019 p_current_stage_seq,
2020 decode(p_end_stage_seq, FND_API.G_MISS_NUM, null, p_end_stage_seq),
2021 l_period_fte(1), l_period_fte(2), l_period_fte(3), l_period_fte(4),
2022 l_period_fte(5), l_period_fte(6), l_period_fte(7), l_period_fte(8),
2023 l_period_fte(9), l_period_fte(10), l_period_fte(11), l_period_fte(12),
2024 l_period_fte(13), l_period_fte(14), l_period_fte(15), l_period_fte(16),
2025 l_period_fte(17), l_period_fte(18), l_period_fte(19), l_period_fte(20),
2026 l_period_fte(21), l_period_fte(22), l_period_fte(23), l_period_fte(24),
2027 l_period_fte(25), l_period_fte(26), l_period_fte(27), l_period_fte(28),
2028 l_period_fte(29), l_period_fte(30), l_period_fte(31), l_period_fte(32),
2029 l_period_fte(33), l_period_fte(34), l_period_fte(35), l_period_fte(36),
2030 l_period_fte(37), l_period_fte(38), l_period_fte(39), l_period_fte(40),
2031 l_period_fte(41), l_period_fte(42), l_period_fte(43), l_period_fte(44),
2032 l_period_fte(45), l_period_fte(46), l_period_fte(47), l_period_fte(48),
2033 l_period_fte(49), l_period_fte(50), l_period_fte(51), l_period_fte(52),
2034 l_period_fte(53), l_period_fte(54), l_period_fte(55), l_period_fte(56),
2035 l_period_fte(57), l_period_fte(58), l_period_fte(59), l_period_fte(60),
2036 l_annual_fte,
2037 sysdate,
2038 l_userid,
2039 l_loginid,
2040 l_userid,
2041 sysdate);
2042
2043 p_fte_line_id := l_ftelineid;
2044
2045 if FND_API.to_Boolean(p_recalculate_flag) then
2046 begin
2047
2048 for c_BaseSP_Rec in c_BaseSP (l_global_wsid) loop
2049 l_base_spid := c_BaseSP_Rec.service_package_id;
2050 end loop;
2051
2052 for c_Elements_Rec in c_Rec_Elements (l_data_extract_id,
2053 l_business_group_id) loop
2054
2055 --pass the total_fte , and period_fte and number of budget periods
2056 --so the FTE proration will be calculated in the calle procedure itself
2057 --This is to ensure that FTE proration will happen based on the yearly FTE
2058 --and not on period level FTE
2059 Distribute_Position_Cost
2060 (p_return_status => l_return_status,
2061 p_insert_from_base => FND_API.G_TRUE,
2062 p_worksheet_id => p_worksheet_id,
2063 p_flex_mapping_set_id => p_flex_mapping_set_id,
2064 p_rounding_factor => l_rounding_factor,
2065 p_position_line_id => p_position_line_id,
2066 p_pay_element_id => c_Elements_Rec.pay_element_id,
2067 p_budget_year_id => p_budget_year_id,
2068 p_base_service_package_id => l_base_spid,
2069 p_service_package_id => p_service_package_id,
2070 p_stage_set_id => p_stage_set_id,
2071 p_start_stage_seq => l_start_stage_seq,
2072 p_current_stage_seq => p_current_stage_seq,
2073 p_budget_group_id => p_budget_group_id,
2074 /*For Bug No : 2811698 Start*/
2075 p_period_fte => p_period_fte,
2076 p_total_fte => l_total_fte,
2077 p_num_budget_periods => l_num_budget_periods
2078 /*For Bug No : 2811698 End*/
2079 );
2080
2081 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2082 raise FND_API.G_EXC_ERROR;
2083 end if;
2084
2085 end loop;
2086
2087 end;
2088 end if;
2089
2090 end;
2091 else
2092 begin
2093
2094 if ((FND_API.to_Boolean(p_recalculate_flag)) or
2095 (FND_API.to_Boolean(l_spfl_exists))) then
2096 begin
2097
2098 for c_Elements_Rec in c_Rec_Elements (l_data_extract_id,
2099 l_business_group_id) loop
2100 Distribute_Position_Cost
2101 (p_return_status => l_return_status,
2102 p_worksheet_id => p_worksheet_id,
2103 p_flex_mapping_set_id => p_flex_mapping_set_id,
2104 p_rounding_factor => l_rounding_factor,
2105 p_position_line_id => p_position_line_id,
2106 p_pay_element_id => c_Elements_Rec.pay_element_id,
2107 p_budget_year_id => p_budget_year_id,
2108 p_service_package_id => p_service_package_id,
2109 p_stage_set_id => p_stage_set_id,
2110 p_start_stage_seq => l_start_stage_seq,
2111 p_current_stage_seq => p_current_stage_seq,
2112 p_budget_group_id => p_budget_group_id,
2113 /*For Bug No : 2811698 Start*/
2114 p_period_fte => p_period_fte,
2115 p_total_fte => l_total_fte,
2116 p_num_budget_periods => l_num_budget_periods
2117 /*For Bug No : 2811698 End*/
2118 );
2119
2120 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2121 raise FND_API.G_EXC_ERROR;
2122 end if;
2123
2124 end loop;
2125
2126 end;
2127 end if;
2128
2129 end;
2130 end if;
2131
2132 if ((FND_API.to_Boolean(p_recalculate_flag)) or
2133 (FND_API.to_Boolean(l_spfl_exists))) then
2134 begin
2135
2136 -- Redistribute the Annual FTE across the Recurring Salary Distributions
2137
2138 Update_Annual_FTE
2139 (p_api_version => 1.0,
2140 p_return_status => l_return_status,
2141 p_worksheet_id => p_worksheet_id,
2142 p_position_line_id => p_position_line_id,
2143 p_budget_year_id => p_budget_year_id,
2144 p_service_package_id => p_service_package_id,
2145 p_stage_set_id => p_stage_set_id,
2146 p_current_stage_seq => p_current_stage_seq,
2147 p_budget_group_id => p_budget_group_id);
2148
2149 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2150 raise FND_API.G_EXC_ERROR;
2151 end if;
2152
2153 end;
2154 end if;
2155
2156
2157 -- Initialize API return status to success
2158
2159 p_return_status := FND_API.G_RET_STS_SUCCESS;
2160
2161
2162 EXCEPTION
2163
2164 when FND_API.G_EXC_ERROR then
2165 p_return_status := FND_API.G_RET_STS_ERROR;
2166 FND_MSG_PUB.Count_And_Get (p_count => l_msg_count,
2167 p_data => l_msg_data);
2168
2169
2170 when FND_API.G_EXC_UNEXPECTED_ERROR then
2171 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2172 FND_MSG_PUB.Count_And_Get (p_count => l_msg_count,
2173 p_data => l_msg_data);
2174
2175 when OTHERS then
2176 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2177
2178 if FND_MSG_PUB.Check_Msg_Level
2179 (p_message_level => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2180 then
2181 FND_MSG_PUB.Add_Exc_Msg
2182 (p_pkg_name => G_PKG_NAME,
2183 p_procedure_name => l_api_name);
2184 end if;
2185
2186 FND_MSG_PUB.Count_And_Get (p_count => l_msg_count,
2187 p_data => l_msg_data);
2188
2189 END Create_FTE_Lines;
2190
2191 /* ----------------------------------------------------------------------- */
2192
2193 -- Changes allowed for FTE/SP entries :
2194 --
2195 -- (i) Change SP only : if (start_stage_seq <> current_stage_seq) for current
2196 -- record
2197 -- (a) create new stage for all entries for the same SP including :
2198 -- FTE/SP record (in PSB_WS_FTE_LINES)
2199 -- Element Cost records for all recurring elements (in PSB_WS_ELEMENT_LINES)
2200 -- Account Dist records for all recurring elements (in PSB_WS_ACCOUNT_LINES)
2201 -- (b) recalculate for all recurring elements
2202
2203 -- (ii) Change FTE only : if (start_stage_seq <> current_stage_seq) for current
2204 -- record
2205 -- (a) create new stage for all entries for the same SP including :
2206 -- FTE/SP record (in PSB_WS_FTE_LINES)
2207 -- Element Cost records for all recurring elements (in PSB_WS_ELEMENT_LINES)
2208 -- Account Dist records for all recurring elements (in PSB_WS_ACCOUNT_LINES)
2209 -- (b) recalculate for all recurring elements
2210
2211 -- (iii) Change FTE and SP : if (start_stage_seq <> current_stage_seq) for current
2212 -- record
2213 -- (a) create new stage for all entries for the same SP including :
2214 -- FTE/SP record (in PSB_WS_FTE_LINES)
2215 -- Element Cost records for all recurring elements (in PSB_WS_ELEMENT_LINES)
2216 -- Account Dist records for all recurring elements (in PSB_WS_ACCOUNT_LINES)
2217 -- (b) recalculate for all recurring elements
2218
2219 -- Note : Recalculation and Redistribution of Element Costs and Accounting Distributions
2220 -- are done only for recurring Elements since non-recurring Elements are by
2221 -- definition independent of FTE
2222
2223 PROCEDURE Create_FTE_Lines
2224 ( p_api_version IN NUMBER,
2225 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_NONE,
2226 p_return_status OUT NOCOPY VARCHAR2,
2227 p_check_stages IN VARCHAR2 := FND_API.G_TRUE,
2228 p_worksheet_id IN NUMBER,
2229 p_fte_line_id IN NUMBER,
2230 p_service_package_id IN NUMBER := FND_API.G_MISS_NUM,
2231 p_current_stage_seq IN NUMBER := FND_API.G_MISS_NUM,
2232 p_period_fte IN PSB_WS_ACCT1.g_prdamt_tbl_type,
2233 p_budget_group_id IN NUMBER := FND_API.G_MISS_NUM
2234 ) IS
2235
2236 l_api_name CONSTANT VARCHAR2(30) := 'Create_FTE_Lines';
2237 l_api_version CONSTANT NUMBER := 1.0;
2238
2239 l_new_stage VARCHAR2(1) := FND_API.G_FALSE;
2240
2241 l_previous_stage_seq NUMBER;
2242
2243 l_userid NUMBER;
2244 l_loginid NUMBER;
2245
2246 l_position_line_id NUMBER;
2247 l_budget_year_id NUMBER;
2248 l_service_package_id NUMBER;
2249 l_stage_set_id NUMBER;
2250 l_start_stage_seq NUMBER;
2251 l_current_stage_seq NUMBER;
2252 l_end_stage_seq NUMBER;
2253 l_period_fte PSB_WS_ACCT1.g_prdamt_tbl_type;
2254 l_annual_fte NUMBER;
2255 l_new_annual_fte NUMBER;
2256
2257 l_budget_calendar_id NUMBER;
2258 l_budget_group_id NUMBER;
2259 l_rounding_factor NUMBER;
2260 l_data_extract_id NUMBER;
2261
2262 l_business_group_id NUMBER;
2263
2264 l_fte NUMBER;
2265 /*For Bug No : 2811698 Start*/
2266 l_total_fte NUMBER := 0;
2267 /*For Bug No : 2811698 End*/
2268 l_num_budget_periods NUMBER;
2269
2270 l_fte_line_id NUMBER;
2271
2272 l_spflid NUMBER;
2273 l_spfte PSB_WS_ACCT1.g_prdamt_tbl_type;
2274 -- Added l_bind_fte as part of fix for bug 3132485
2275 l_bind_fte PSB_WS_ACCT1.g_prdamt_tbl_type;
2276 l_spfl_exists VARCHAR2(1) := FND_API.G_FALSE;
2277
2278 l_recalculate_flag VARCHAR2(1) := FND_API.G_FALSE;
2279
2280 sql_wfl VARCHAR2(6000);
2281 num_wfl INTEGER;
2282
2283 l_year_index BINARY_INTEGER;
2284 l_index BINARY_INTEGER;
2285
2286 l_global_wsid NUMBER;
2287 l_base_spid NUMBER;
2288
2289 l_update_from_base VARCHAR2(1) := FND_API.G_FALSE;
2290
2291 l_return_status VARCHAR2(1);
2292 l_msg_count NUMBER;
2293 l_msg_data VARCHAR2(2000);
2294 l_temp_buf VARCHAR2(2000);
2295
2296 cursor c_Wfl is
2297 select position_line_id,
2298 budget_year_id,
2299 service_package_id,
2300 stage_set_id,
2301 start_stage_seq,
2302 current_stage_seq,
2303 end_stage_seq,
2304 period1_fte, period2_fte, period3_fte, period4_fte,
2305 period5_fte, period6_fte, period7_fte, period8_fte,
2306 period9_fte, period10_fte, period11_fte, period12_fte,
2307 period13_fte, period14_fte, period15_fte, period16_fte,
2308 period17_fte, period18_fte, period19_fte, period20_fte,
2309 period21_fte, period22_fte, period23_fte, period24_fte,
2310 period25_fte, period26_fte, period27_fte, period28_fte,
2311 period29_fte, period30_fte, period31_fte, period32_fte,
2312 period33_fte, period34_fte, period35_fte, period36_fte,
2313 period37_fte, period38_fte, period39_fte, period40_fte,
2314 period41_fte, period42_fte, period43_fte, period44_fte,
2315 period45_fte, period46_fte, period47_fte, period48_fte,
2316 period49_fte, period50_fte, period51_fte, period52_fte,
2317 period53_fte, period54_fte, period55_fte, period56_fte,
2318 period57_fte, period58_fte, period59_fte, period60_fte,
2319 annual_fte
2320 from PSB_WS_FTE_LINES
2321 where fte_line_id = p_fte_line_id;
2322
2323 cursor c_PrevStage is
2324 select Max(sequence_number) sequence_number
2325 from PSB_BUDGET_STAGES
2326 where sequence_number < l_current_stage_seq
2327 and budget_stage_set_id = l_stage_set_id;
2328
2329 BEGIN
2330
2331 -- Standard call to check for call compatibility
2332
2333
2334 if not FND_API.Compatible_API_Call (l_api_version,
2335 p_api_version,
2336 l_api_name,
2337 G_PKG_NAME)
2338 then
2339 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2340 end if;
2341
2342 --Total cost would be the sum of all the periods
2343 /*For Bug No : 2811698 Start*/
2344 for l_index in 1..PSB_WS_ACCT1.g_max_num_amounts loop
2345 l_total_fte := l_total_fte + nvl(p_period_fte(l_index),0);
2346 end loop;
2347 /*For Bug No : 2811698 End*/
2348
2349 for c_Wfl_Rec in c_Wfl loop
2350 l_position_line_id := c_Wfl_Rec.position_line_id;
2351 l_budget_year_id := c_Wfl_Rec.budget_year_id;
2352 l_service_package_id := c_Wfl_Rec.service_package_id;
2353 l_stage_set_id := c_Wfl_Rec.stage_set_id;
2354 l_start_stage_seq := c_Wfl_Rec.start_stage_seq;
2355 l_current_stage_seq := c_Wfl_Rec.current_stage_seq;
2356 l_end_stage_seq := c_Wfl_Rec.end_stage_seq;
2357 l_period_fte(1) := c_Wfl_Rec.period1_fte; l_period_fte(2) := c_Wfl_Rec.period2_fte;
2358 l_period_fte(3) := c_Wfl_Rec.period3_fte; l_period_fte(4) := c_Wfl_Rec.period4_fte;
2359 l_period_fte(5) := c_Wfl_Rec.period5_fte; l_period_fte(6) := c_Wfl_Rec.period6_fte;
2360 l_period_fte(7) := c_Wfl_Rec.period7_fte; l_period_fte(8) := c_Wfl_Rec.period8_fte;
2361 l_period_fte(9) := c_Wfl_Rec.period9_fte; l_period_fte(10) := c_Wfl_Rec.period10_fte;
2362 l_period_fte(11) := c_Wfl_Rec.period11_fte; l_period_fte(12) := c_Wfl_Rec.period12_fte;
2363 l_period_fte(13) := c_Wfl_Rec.period13_fte; l_period_fte(14) := c_Wfl_Rec.period14_fte;
2364 l_period_fte(15) := c_Wfl_Rec.period15_fte; l_period_fte(16) := c_Wfl_Rec.period16_fte;
2365 l_period_fte(17) := c_Wfl_Rec.period17_fte; l_period_fte(18) := c_Wfl_Rec.period18_fte;
2366 l_period_fte(19) := c_Wfl_Rec.period19_fte; l_period_fte(20) := c_Wfl_Rec.period20_fte;
2367 l_period_fte(21) := c_Wfl_Rec.period21_fte; l_period_fte(22) := c_Wfl_Rec.period22_fte;
2368 l_period_fte(23) := c_Wfl_Rec.period23_fte; l_period_fte(24) := c_Wfl_Rec.period24_fte;
2369 l_period_fte(25) := c_Wfl_Rec.period25_fte; l_period_fte(26) := c_Wfl_Rec.period26_fte;
2370 l_period_fte(27) := c_Wfl_Rec.period27_fte; l_period_fte(28) := c_Wfl_Rec.period28_fte;
2371 l_period_fte(29) := c_Wfl_Rec.period29_fte; l_period_fte(30) := c_Wfl_Rec.period30_fte;
2372 l_period_fte(31) := c_Wfl_Rec.period31_fte; l_period_fte(32) := c_Wfl_Rec.period32_fte;
2373 l_period_fte(33) := c_Wfl_Rec.period33_fte; l_period_fte(34) := c_Wfl_Rec.period34_fte;
2374 l_period_fte(35) := c_Wfl_Rec.period35_fte; l_period_fte(36) := c_Wfl_Rec.period36_fte;
2375 l_period_fte(37) := c_Wfl_Rec.period37_fte; l_period_fte(38) := c_Wfl_Rec.period38_fte;
2376 l_period_fte(39) := c_Wfl_Rec.period39_fte; l_period_fte(40) := c_Wfl_Rec.period40_fte;
2377 l_period_fte(41) := c_Wfl_Rec.period41_fte; l_period_fte(42) := c_Wfl_Rec.period42_fte;
2378 l_period_fte(43) := c_Wfl_Rec.period43_fte; l_period_fte(44) := c_Wfl_Rec.period44_fte;
2379 l_period_fte(45) := c_Wfl_Rec.period45_fte; l_period_fte(46) := c_Wfl_Rec.period46_fte;
2380 l_period_fte(47) := c_Wfl_Rec.period47_fte; l_period_fte(48) := c_Wfl_Rec.period48_fte;
2381 l_period_fte(49) := c_Wfl_Rec.period49_fte; l_period_fte(50) := c_Wfl_Rec.period50_fte;
2382 l_period_fte(51) := c_Wfl_Rec.period51_fte; l_period_fte(52) := c_Wfl_Rec.period52_fte;
2383 l_period_fte(53) := c_Wfl_Rec.period53_fte; l_period_fte(54) := c_Wfl_Rec.period54_fte;
2384 l_period_fte(55) := c_Wfl_Rec.period55_fte; l_period_fte(56) := c_Wfl_Rec.period56_fte;
2385 l_period_fte(57) := c_Wfl_Rec.period57_fte; l_period_fte(58) := c_Wfl_Rec.period58_fte;
2386 l_period_fte(59) := c_Wfl_Rec.period59_fte; l_period_fte(60) := c_Wfl_Rec.period60_fte;
2387 l_annual_fte := c_Wfl_Rec.annual_fte;
2388 end loop;
2389
2390
2391 -- If Service Package is being modified, check whether the target FTE line exists
2392
2393 if ((p_service_package_id <> FND_API.G_MISS_NUM) and
2394 (p_service_package_id <> l_service_package_id)) then
2395 begin
2396
2397 for c_Wfl_Rec in c_Wfl_SP (l_position_line_id, l_budget_year_id, p_service_package_id,
2398 l_stage_set_id, l_current_stage_seq) loop
2399 l_spflid := c_Wfl_Rec.fte_line_id;
2400 l_spfte(1) := c_Wfl_Rec.period1_fte; l_spfte(2) := c_Wfl_Rec.period2_fte;
2401 l_spfte(3) := c_Wfl_Rec.period3_fte; l_spfte(4) := c_Wfl_Rec.period4_fte;
2402 l_spfte(5) := c_Wfl_Rec.period5_fte; l_spfte(6) := c_Wfl_Rec.period6_fte;
2403 l_spfte(7) := c_Wfl_Rec.period7_fte; l_spfte(8) := c_Wfl_Rec.period8_fte;
2404 l_spfte(9) := c_Wfl_Rec.period9_fte; l_spfte(10) := c_Wfl_Rec.period10_fte;
2405 l_spfte(11) := c_Wfl_Rec.period11_fte; l_spfte(12) := c_Wfl_Rec.period12_fte;
2406 l_spfte(13) := c_Wfl_Rec.period13_fte; l_spfte(14) := c_Wfl_Rec.period14_fte;
2407 l_spfte(15) := c_Wfl_Rec.period15_fte; l_spfte(16) := c_Wfl_Rec.period16_fte;
2408 l_spfte(17) := c_Wfl_Rec.period17_fte; l_spfte(18) := c_Wfl_Rec.period18_fte;
2409 l_spfte(19) := c_Wfl_Rec.period19_fte; l_spfte(20) := c_Wfl_Rec.period20_fte;
2410 l_spfte(21) := c_Wfl_Rec.period21_fte; l_spfte(22) := c_Wfl_Rec.period22_fte;
2411 l_spfte(23) := c_Wfl_Rec.period23_fte; l_spfte(24) := c_Wfl_Rec.period24_fte;
2412 l_spfte(25) := c_Wfl_Rec.period25_fte; l_spfte(26) := c_Wfl_Rec.period26_fte;
2413 l_spfte(27) := c_Wfl_Rec.period27_fte; l_spfte(28) := c_Wfl_Rec.period28_fte;
2414 l_spfte(29) := c_Wfl_Rec.period29_fte; l_spfte(30) := c_Wfl_Rec.period30_fte;
2415 l_spfte(31) := c_Wfl_Rec.period31_fte; l_spfte(32) := c_Wfl_Rec.period32_fte;
2416 l_spfte(33) := c_Wfl_Rec.period33_fte; l_spfte(34) := c_Wfl_Rec.period34_fte;
2417 l_spfte(35) := c_Wfl_Rec.period35_fte; l_spfte(36) := c_Wfl_Rec.period36_fte;
2418 l_spfte(37) := c_Wfl_Rec.period37_fte; l_spfte(38) := c_Wfl_Rec.period38_fte;
2419 l_spfte(39) := c_Wfl_Rec.period39_fte; l_spfte(40) := c_Wfl_Rec.period40_fte;
2420 l_spfte(41) := c_Wfl_Rec.period41_fte; l_spfte(42) := c_Wfl_Rec.period42_fte;
2421 l_spfte(43) := c_Wfl_Rec.period43_fte; l_spfte(44) := c_Wfl_Rec.period44_fte;
2422 l_spfte(45) := c_Wfl_Rec.period45_fte; l_spfte(46) := c_Wfl_Rec.period46_fte;
2423 l_spfte(47) := c_Wfl_Rec.period47_fte; l_spfte(48) := c_Wfl_Rec.period48_fte;
2424 l_spfte(49) := c_Wfl_Rec.period49_fte; l_spfte(50) := c_Wfl_Rec.period50_fte;
2425 l_spfte(51) := c_Wfl_Rec.period51_fte; l_spfte(52) := c_Wfl_Rec.period52_fte;
2426 l_spfte(53) := c_Wfl_Rec.period53_fte; l_spfte(54) := c_Wfl_Rec.period54_fte;
2427 l_spfte(55) := c_Wfl_Rec.period55_fte; l_spfte(56) := c_Wfl_Rec.period56_fte;
2428 l_spfte(57) := c_Wfl_Rec.period57_fte; l_spfte(58) := c_Wfl_Rec.period58_fte;
2429 l_spfte(59) := c_Wfl_Rec.period59_fte; l_spfte(60) := c_Wfl_Rec.period60_fte;
2430
2431 l_spfl_exists := FND_API.G_TRUE;
2432 end loop;
2433
2434 l_new_stage := FND_API.G_TRUE;
2435
2436 -- Delete target FTE line if it exists since entries for the target FTE line will
2437 -- be added to the current FTE line
2438
2439 if FND_API.to_Boolean(l_spfl_exists) then
2440 begin
2441
2442 l_recalculate_flag := FND_API.G_TRUE;
2443
2444 PSB_WORKSHEET.Delete_WFL
2445 (p_api_version => 1.0,
2446 p_return_status => l_return_status,
2447 p_fte_line_id => l_spflid);
2448
2449 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2450 raise FND_API.G_EXC_ERROR;
2451 end if;
2452
2453 end;
2454 end if;
2455
2456 end;
2457 end if;
2458
2459 -- Create new Stage if any of the existing Period FTEs are being updated and the Start Stage Seq for
2460 -- the current FTE line is different from the Current Stage Sequence
2461
2462 for l_index in 1..PSB_WS_ACCT1.g_max_num_amounts loop
2463 if nvl(p_period_fte(l_index), FND_API.G_MISS_NUM) <> nvl(l_period_fte(l_index), FND_API.G_MISS_NUM) then
2464 l_new_stage := FND_API.G_TRUE;
2465 l_recalculate_flag := FND_API.G_TRUE;
2466 exit;
2467 end if;
2468
2469 end loop;
2470
2471 if ((FND_API.to_Boolean(l_new_stage)) and
2472 (l_start_stage_seq = l_current_stage_seq)) then
2473 l_new_stage := FND_API.G_FALSE;
2474 end if;
2475
2476 l_userid := FND_GLOBAL.USER_ID;
2477 l_loginid := FND_GLOBAL.LOGIN_ID;
2478
2479 if ((FND_API.to_Boolean(p_check_stages)) and
2480 (FND_API.to_Boolean(l_new_stage)) and
2481 (l_start_stage_seq < l_current_stage_seq)) then
2482 begin
2483
2484 for c_PrevStage_Rec in c_PrevStage loop
2485 l_previous_stage_seq := c_PrevStage_Rec.sequence_number;
2486 end loop;
2487
2488 for c_FTESeq_Rec in c_FTESeq loop
2489 l_fte_line_id := c_FTESeq_Rec.FTELineID;
2490 end loop;
2491
2492 sql_wfl := 'insert into PSB_WS_FTE_LINES ' ||
2493 '(fte_line_id, ' ||
2494 'position_line_id, ' ||
2495 'budget_year_id, ' ||
2496 'service_package_id, ' ||
2497 'stage_set_id, ' ||
2498 'start_stage_seq, ' ||
2499 'current_stage_seq, ' ||
2500 'end_stage_seq, ';
2501
2502 for l_index in 1..PSB_WS_ACCT1.g_max_num_amounts loop
2503 sql_wfl := sql_wfl ||
2504 'period' || l_index || '_fte, ';
2505 end loop;
2506
2507 -- Bug#5030383
2508 -- Replaced l_fte_line_id by :b_fte_line_id
2509 -- Replaced l_previous_stage_seq by :b_previous_stage_seq
2510 sql_wfl := sql_wfl ||
2511 'annual_fte, ' ||
2512 'last_update_date, ' ||
2513 'last_updated_by, ' ||
2514 'last_update_login, ' ||
2515 'created_by, ' ||
2516 'creation_date) ' ||
2517 'select :b_fte_line_id, ' ||
2518 'position_line_id, ' ||
2519 'budget_year_id, ' ||
2520 'service_package_id, ' ||
2521 'stage_set_id, ' ||
2522 'start_stage_seq, ' ||
2523 ':b_previous_stage_seq, ' ||
2524 ':b_previous_stage_seq, ';
2525
2526 for l_index in 1..PSB_WS_ACCT1.g_max_num_amounts loop
2527 sql_wfl := sql_wfl ||
2528 'period' || l_index || '_fte, ';
2529 end loop;
2530
2531 -- Bug#5030383
2532 -- Replaced sysdate by :b_last_update_date
2533 -- Replaced l_userid by :b_last_updated_by
2534 -- Replaced l_loginid by :b_last_update_login
2535 -- Replaced l_userid by :b_created_by
2536 -- Replaced sysdate by :b_creation_date
2537 -- Replaced p_fte_line_id by :b_fte_line_id
2538 sql_wfl := sql_wfl ||
2539 'annual_fte, ' ||
2540 ':b_last_update_date, ' ||
2541 ':b_last_updated_by, ' ||
2542 ':b_last_update_login , ' ||
2543 ':b_created_by, ' ||
2544 ':b_creation_date ' ||
2545 'from PSB_WS_FTE_LINES ' ||
2546 'where fte_line_id = :b_fte_line_id';
2547
2548 -- Replaced PSB_WS_ACCT1.dsql_execute with execute immediate for bug 3132485
2549 -- Bug#5030383
2550
2551 EXECUTE IMMEDIATE
2552 sql_wfl
2553 USING
2554 l_fte_line_id
2555 , l_previous_stage_seq
2556 , l_previous_stage_seq
2557 , SYSDATE
2558 , l_userid
2559 , l_loginid
2560 , l_userid
2561 , SYSDATE
2562 , p_fte_line_id ;
2563
2564 /*
2565 num_wfl := PSB_WS_ACCT1.dsql_execute(sql_wfl);
2566
2567 if num_wfl < 0 then
2568 raise FND_API.G_EXC_ERROR;
2569 end if;
2570 */
2571
2572 end;
2573 end if;
2574
2575 l_new_annual_fte := 0;
2576
2577 sql_wfl := 'update PSB_WS_FTE_LINES ' ||
2578 'set service_package_id = decode( :p_service_package_id1 , :gmn1,'||
2579 'service_package_id, :p_service_package_id2 ), ';
2580
2581 for l_index in 1..PSB_WS_ACCT1.g_max_num_amounts loop
2582
2583 if FND_API.to_Boolean(l_spfl_exists) then
2584 l_fte := nvl(p_period_fte(l_index), 0) + nvl(l_spfte(l_index), 0);
2585 else
2586 l_fte := nvl(p_period_fte(l_index), 0);
2587 end if;
2588
2589 -- Replaced l_fte with bind variable for Bug 3132485
2590 if l_fte = 0 then
2591 l_bind_fte(l_index) := null;
2592 sql_wfl := sql_wfl ||
2593 'period' || l_index || '_fte = :l_fte, ';
2594 else
2595 l_bind_fte(l_index) := l_fte;
2596 sql_wfl := sql_wfl ||
2597 'period' || l_index || '_fte = ' || ':l_fte' || ', ';
2598 end if;
2599
2600 l_new_annual_fte := l_new_annual_fte + l_fte;
2601
2602 end loop;
2603
2604 -- If new Stage is created, update the Start and Current Stage Sequences to reflect
2605 -- the new Stage; otherwise, update the Current Stage Sequence if it is being changed
2606 -- from Worksheet Operations
2607
2608 if ((FND_API.to_Boolean(p_check_stages)) and
2609 (FND_API.to_Boolean(l_new_stage)) and
2610 (l_start_stage_seq < l_current_stage_seq)) then
2611 begin
2612 -- Bug#5030383
2613 -- Replaced literals by b_start_stage_seq and :b_current_stage_seq
2614 sql_wfl := sql_wfl ||
2615 'start_stage_seq = :b_start_stage_seq, ' ||
2616 'current_stage_seq = :b_current_stage_seq, ';
2617
2618 end;
2619 else
2620 begin
2621 -- Replaced p_current_stage_seq and FND_API.G_MISS_NUM with bind variables for
2622 -- bug 3132485
2623 sql_wfl := sql_wfl ||
2624 'current_stage_seq = decode( :p_current_stage_seq1 , :gmn2 ,'||
2625 'current_stage_seq, :p_current_stage_seq2 ), ';
2626
2627 end;
2628 end if;
2629
2630 if FND_API.to_Boolean(l_recalculate_flag) then
2631 begin
2632
2633 for c_WS_Rec in c_WS (p_worksheet_id) loop
2634 l_global_wsid := c_WS_Rec.global_worksheet_id;
2635 l_budget_calendar_id := c_WS_Rec.budget_calendar_id;
2636 l_budget_group_id := c_WS_Rec.budget_group_id;
2637 l_rounding_factor := c_WS_Rec.rounding_factor;
2638 l_data_extract_id := c_WS_Rec.data_extract_id;
2639 end loop;
2640
2641 for c_BG_Rec in c_BG (l_budget_group_id) loop
2642 l_business_group_id := c_BG_Rec.business_group_id;
2643 end loop;
2644
2645 if l_budget_calendar_id <> nvl(PSB_WS_ACCT1.g_budget_calendar_id, FND_API.G_MISS_NUM) then
2646 begin
2647
2648 PSB_WS_ACCT1.Cache_Budget_Calendar
2649 (p_return_status => l_return_status,
2650 p_budget_calendar_id => l_budget_calendar_id);
2651
2652 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2653 raise FND_API.G_EXC_ERROR;
2654 end if;
2655
2656 end;
2657 end if;
2658
2659 for l_year_index in 1..PSB_WS_ACCT1.g_num_budget_years loop
2660
2661 if PSB_WS_ACCT1.g_budget_years(l_year_index).budget_year_id = l_budget_year_id then
2662 l_num_budget_periods := PSB_WS_ACCT1.g_budget_years(l_year_index).num_budget_periods;
2663 end if;
2664
2665 end loop;
2666
2667 if l_num_budget_periods <> 0 then
2668 l_new_annual_fte := l_new_annual_fte / l_num_budget_periods;
2669 end if;
2670 -- Replaced l_new_annual_fte with bind variable for fix for bug 3132485
2671 sql_wfl := sql_wfl ||
2672 'annual_fte = ' || ':l_new_annual_fte' || ', ';
2673
2674 end;
2675 end if;
2676
2677 -- Bug#5030383
2678 -- Replaced sysdate by :b_last_update_date
2679 -- Replaced l_userid by :b_last_updated_by
2680 -- Replaced l_loginid by :b_last_update_login
2681 -- Replaced p_fte_line_id by :b_fte_line_id
2682 sql_wfl := sql_wfl ||
2683 'last_update_date = :b_last_update_date, ' ||
2684 'last_updated_by = :b_last_updated_by, ' ||
2685 'last_update_login = :b_last_update_login ' ||
2686 'where fte_line_id = :b_fte_line_id' ;
2687
2688 -- Commented this as part of fix for Bug 3132485
2689 /*
2690 num_wfl := PSB_WS_ACCT1.dsql_execute(sql_wfl);
2691
2692 if num_wfl < 0 then
2693 raise FND_API.G_EXC_ERROR;
2694 end if;
2695 */
2696
2697 -- start of bug 3353382
2698 if ((FND_API.to_Boolean(p_check_stages)) and
2699 (FND_API.to_Boolean(l_new_stage)) and
2700 (l_start_stage_seq < l_current_stage_seq)) then
2701
2702 if FND_API.to_Boolean(l_recalculate_flag) then
2703
2704 -- Bug#5030383
2705
2706 EXECUTE IMMEDIATE sql_wfl USING
2707 p_service_package_id,FND_API.G_MISS_NUM,p_service_package_id,
2708 l_bind_fte(1),l_bind_fte(2),l_bind_fte(3),l_bind_fte(4),
2709 l_bind_fte(5),l_bind_fte(6),l_bind_fte(7),l_bind_fte(8),
2710 l_bind_fte(9),l_bind_fte(10),l_bind_fte(11),l_bind_fte(12),
2711 l_bind_fte(13),l_bind_fte(14),l_bind_fte(15),l_bind_fte(16),
2712 l_bind_fte(17),l_bind_fte(18),l_bind_fte(19),l_bind_fte(20),
2713 l_bind_fte(21),l_bind_fte(22),l_bind_fte(23),l_bind_fte(24),
2714 l_bind_fte(25),l_bind_fte(26),l_bind_fte(27),l_bind_fte(28),
2715 l_bind_fte(29),l_bind_fte(30),l_bind_fte(31),l_bind_fte(32),
2716 l_bind_fte(33),l_bind_fte(34),l_bind_fte(35),l_bind_fte(36),
2717 l_bind_fte(37),l_bind_fte(38),l_bind_fte(39),l_bind_fte(40),
2718 l_bind_fte(41),l_bind_fte(42),l_bind_fte(43),l_bind_fte(44),
2719 l_bind_fte(45),l_bind_fte(46),l_bind_fte(47),l_bind_fte(48),
2720 l_bind_fte(49),l_bind_fte(50),l_bind_fte(51),l_bind_fte(52),
2721 l_bind_fte(53),l_bind_fte(54),l_bind_fte(55),l_bind_fte(56),
2722 l_bind_fte(57),l_bind_fte(58),l_bind_fte(59),l_bind_fte(60),
2723 l_new_annual_fte, l_current_stage_seq, l_current_stage_seq,
2724 SYSDATE, l_userid, l_loginid, p_fte_line_id ;
2725
2726 else
2727 -- Bug#5030383
2728
2729 EXECUTE IMMEDIATE sql_wfl USING
2730 p_service_package_id,FND_API.G_MISS_NUM,p_service_package_id,
2731 l_bind_fte(1),l_bind_fte(2),l_bind_fte(3),l_bind_fte(4),
2732 l_bind_fte(5),l_bind_fte(6),l_bind_fte(7),l_bind_fte(8),
2733 l_bind_fte(9),l_bind_fte(10),l_bind_fte(11),l_bind_fte(12),
2734 l_bind_fte(13),l_bind_fte(14),l_bind_fte(15),l_bind_fte(16),
2735 l_bind_fte(17),l_bind_fte(18),l_bind_fte(19),l_bind_fte(20),
2736 l_bind_fte(21),l_bind_fte(22),l_bind_fte(23),l_bind_fte(24),
2737 l_bind_fte(25),l_bind_fte(26),l_bind_fte(27),l_bind_fte(28),
2738 l_bind_fte(29),l_bind_fte(30),l_bind_fte(31),l_bind_fte(32),
2739 l_bind_fte(33),l_bind_fte(34),l_bind_fte(35),l_bind_fte(36),
2740 l_bind_fte(37),l_bind_fte(38),l_bind_fte(39),l_bind_fte(40),
2741 l_bind_fte(41),l_bind_fte(42),l_bind_fte(43),l_bind_fte(44),
2742 l_bind_fte(45),l_bind_fte(46),l_bind_fte(47),l_bind_fte(48),
2743 l_bind_fte(49),l_bind_fte(50),l_bind_fte(51),l_bind_fte(52),
2744 l_bind_fte(53),l_bind_fte(54),l_bind_fte(55),l_bind_fte(56),
2745 l_bind_fte(57),l_bind_fte(58),l_bind_fte(59),l_bind_fte(60),
2746 l_current_stage_seq, l_current_stage_seq, SYSDATE, l_userid,
2747 l_loginid, p_fte_line_id ;
2748
2749 end if;
2750 else
2751
2752 if FND_API.to_Boolean(l_recalculate_flag) then
2753 -- Bug#5030383
2754
2755 EXECUTE IMMEDIATE sql_wfl USING
2756 p_service_package_id,FND_API.G_MISS_NUM,p_service_package_id,
2757 l_bind_fte(1),l_bind_fte(2),l_bind_fte(3),l_bind_fte(4),
2758 l_bind_fte(5),l_bind_fte(6),l_bind_fte(7),l_bind_fte(8),
2759 l_bind_fte(9),l_bind_fte(10),l_bind_fte(11),l_bind_fte(12),
2760 l_bind_fte(13),l_bind_fte(14),l_bind_fte(15),l_bind_fte(16),
2761 l_bind_fte(17),l_bind_fte(18),l_bind_fte(19),l_bind_fte(20),
2762 l_bind_fte(21),l_bind_fte(22),l_bind_fte(23),l_bind_fte(24),
2763 l_bind_fte(25),l_bind_fte(26),l_bind_fte(27),l_bind_fte(28),
2764 l_bind_fte(29),l_bind_fte(30),l_bind_fte(31),l_bind_fte(32),
2765 l_bind_fte(33),l_bind_fte(34),l_bind_fte(35),l_bind_fte(36),
2766 l_bind_fte(37),l_bind_fte(38),l_bind_fte(39),l_bind_fte(40),
2767 l_bind_fte(41),l_bind_fte(42),l_bind_fte(43),l_bind_fte(44),
2768 l_bind_fte(45),l_bind_fte(46),l_bind_fte(47),l_bind_fte(48),
2769 l_bind_fte(49),l_bind_fte(50),l_bind_fte(51),l_bind_fte(52),
2770 l_bind_fte(53),l_bind_fte(54),l_bind_fte(55),l_bind_fte(56),
2771 l_bind_fte(57),l_bind_fte(58),l_bind_fte(59),l_bind_fte(60),
2772 p_current_stage_seq,FND_API.G_MISS_NUM,p_current_stage_seq,
2773 l_new_annual_fte, SYSDATE, l_userid, l_loginid, p_fte_line_id ;
2774
2775 else
2776 -- Bug#5030383
2777
2778 EXECUTE IMMEDIATE sql_wfl USING
2779 p_service_package_id,FND_API.G_MISS_NUM,p_service_package_id,
2780 l_bind_fte(1),l_bind_fte(2),l_bind_fte(3),l_bind_fte(4),
2781 l_bind_fte(5),l_bind_fte(6),l_bind_fte(7),l_bind_fte(8),
2782 l_bind_fte(9),l_bind_fte(10),l_bind_fte(11),l_bind_fte(12),
2783 l_bind_fte(13),l_bind_fte(14),l_bind_fte(15),l_bind_fte(16),
2784 l_bind_fte(17),l_bind_fte(18),l_bind_fte(19),l_bind_fte(20),
2785 l_bind_fte(21),l_bind_fte(22),l_bind_fte(23),l_bind_fte(24),
2786 l_bind_fte(25),l_bind_fte(26),l_bind_fte(27),l_bind_fte(28),
2787 l_bind_fte(29),l_bind_fte(30),l_bind_fte(31),l_bind_fte(32),
2788 l_bind_fte(33),l_bind_fte(34),l_bind_fte(35),l_bind_fte(36),
2789 l_bind_fte(37),l_bind_fte(38),l_bind_fte(39),l_bind_fte(40),
2790 l_bind_fte(41),l_bind_fte(42),l_bind_fte(43),l_bind_fte(44),
2791 l_bind_fte(45),l_bind_fte(46),l_bind_fte(47),l_bind_fte(48),
2792 l_bind_fte(49),l_bind_fte(50),l_bind_fte(51),l_bind_fte(52),
2793 l_bind_fte(53),l_bind_fte(54),l_bind_fte(55),l_bind_fte(56),
2794 l_bind_fte(57),l_bind_fte(58),l_bind_fte(59),l_bind_fte(60),
2795 p_current_stage_seq,FND_API.G_MISS_NUM,p_current_stage_seq,
2796 SYSDATE, l_userid, l_loginid, p_fte_line_id ;
2797
2798 end if;
2799
2800 end if;
2801
2802 --EXECUTE IMMEDIATE sql_wfl USING
2803 --p_service_package_id,FND_API.G_MISS_NUM,p_service_package_id,
2804 --l_bind_fte(1),l_bind_fte(2),l_bind_fte(3),l_bind_fte(4),
2805 --l_bind_fte(5),l_bind_fte(6),l_bind_fte(7),l_bind_fte(8),
2806 --l_bind_fte(9),l_bind_fte(10),l_bind_fte(11),l_bind_fte(12),
2807 --l_bind_fte(13),l_bind_fte(14),l_bind_fte(15),l_bind_fte(16),
2808 --l_bind_fte(17),l_bind_fte(18),l_bind_fte(19),l_bind_fte(20),
2809 --l_bind_fte(21),l_bind_fte(22),l_bind_fte(23),l_bind_fte(24),
2810 --l_bind_fte(25),l_bind_fte(26),l_bind_fte(27),l_bind_fte(28),
2811 --l_bind_fte(29),l_bind_fte(30),l_bind_fte(31),l_bind_fte(32),
2812 --l_bind_fte(33),l_bind_fte(34),l_bind_fte(35),l_bind_fte(36),
2813 --l_bind_fte(37),l_bind_fte(38),l_bind_fte(39),l_bind_fte(40),
2814 --l_bind_fte(41),l_bind_fte(42),l_bind_fte(43),l_bind_fte(44),
2815 --l_bind_fte(45),l_bind_fte(46),l_bind_fte(47),l_bind_fte(48),
2816 --l_bind_fte(49),l_bind_fte(50),l_bind_fte(51),l_bind_fte(52),
2817 --l_bind_fte(53),l_bind_fte(54),l_bind_fte(55),l_bind_fte(56),
2818 --l_bind_fte(57),l_bind_fte(58),l_bind_fte(59),l_bind_fte(60),
2819 --p_current_stage_seq,FND_API.G_MISS_NUM,p_current_stage_seq,
2820 --l_new_annual_fte;
2821
2822 -- end of bug 3353382
2823
2824 if FND_API.to_Boolean(l_recalculate_flag) then
2825 begin
2826
2827 if ((p_service_package_id <> FND_API.G_MISS_NUM) and
2828 (p_service_package_id <> l_service_package_id)) then
2829 l_service_package_id := p_service_package_id;
2830 end if;
2831
2832 for c_Elements_Rec in c_Rec_Elements (l_data_extract_id,
2833 l_business_group_id) loop
2834
2835 --pass the total_fte , and period_fte and number of budget periods
2836 --so the FTE proration will be calculated in the calle procedure itself
2837 --This is to ensure that FTE proration will happen based on the yearly FTE
2838 --and not on period level FTE
2839 Distribute_Position_Cost
2840 (p_return_status => l_return_status,
2841 p_update_from_base => l_update_from_base,
2842 p_worksheet_id => p_worksheet_id,
2843 p_rounding_factor => l_rounding_factor,
2844 p_position_line_id => l_position_line_id,
2845 p_pay_element_id => c_Elements_Rec.pay_element_id,
2846 p_budget_year_id => l_budget_year_id,
2847 p_base_service_package_id => l_base_spid,
2848 p_service_package_id => l_service_package_id,
2849 p_stage_set_id => l_stage_set_id,
2850 p_start_stage_seq => l_start_stage_seq,
2851 p_current_stage_seq => l_current_stage_seq,
2852 p_budget_group_id => p_budget_group_id,
2853 /*For Bug No : 2811698 Start*/
2854 p_period_fte => p_period_fte,
2855 p_total_fte => l_total_fte,
2856 p_num_budget_periods => l_num_budget_periods
2857 /*For Bug No : 2811698 End*/
2858 );
2859
2860 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2861 raise FND_API.G_EXC_ERROR;
2862 end if;
2863
2864 end loop;
2865
2866 -- Reallocate Annual FTE across the non-recurring Salary Distributions
2867
2868 Update_Annual_FTE
2869 (p_api_version => 1.0,
2870 p_return_status => l_return_status,
2871 p_worksheet_id => p_worksheet_id,
2872 p_position_line_id => l_position_line_id,
2873 p_budget_year_id => l_budget_year_id,
2874 p_service_package_id => l_service_package_id,
2875 p_stage_set_id => l_stage_set_id,
2876 p_current_stage_seq => l_current_stage_seq,
2877 p_budget_group_id => p_budget_group_id);
2878
2879 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2880 raise FND_API.G_EXC_ERROR;
2881 end if;
2882
2883 end;
2884 end if;
2885
2886
2887 -- Initialize API return status to success
2888
2889 p_return_status := FND_API.G_RET_STS_SUCCESS;
2890
2891
2892 EXCEPTION
2893 when FND_API.G_EXC_ERROR then
2894 p_return_status := FND_API.G_RET_STS_ERROR;
2895 FND_MSG_PUB.Count_And_Get (p_count => l_msg_count,
2896 p_data => l_msg_data);
2897
2898
2899 when FND_API.G_EXC_UNEXPECTED_ERROR then
2900 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2901 FND_MSG_PUB.Count_And_Get (p_count => l_msg_count,
2902 p_data => l_msg_data);
2903
2904 when OTHERS then
2905 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2906
2907 if FND_MSG_PUB.Check_Msg_Level
2908 (p_message_level => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2909 then
2910 FND_MSG_PUB.Add_Exc_Msg
2911 (p_pkg_name => G_PKG_NAME,
2912 p_procedure_name => l_api_name);
2913 end if;
2914
2915 FND_MSG_PUB.Count_And_Get (p_count => l_msg_count,
2916 p_data => l_msg_data);
2917 END Create_FTE_Lines;
2918
2919 /* ----------------------------------------------------------------------- */
2920
2921
2922 -- Recalculate Position Element Costs and Position Account Distributions
2923 -- using the pre-computed Period FTE Ratios and the Annual FTE Ratio
2924
2925 PROCEDURE Distribute_Position_Cost
2926 ( p_return_status OUT NOCOPY VARCHAR2,
2927 p_insert_from_base IN VARCHAR2 := FND_API.G_FALSE,
2928 p_update_from_base IN VARCHAR2 := FND_API.G_FALSE,
2929 p_worksheet_id IN NUMBER,
2930 p_flex_mapping_set_id IN NUMBER := FND_API.G_MISS_NUM,
2931 p_rounding_factor IN NUMBER,
2932 p_position_line_id IN NUMBER,
2933 p_pay_element_id IN NUMBER,
2934 p_budget_year_id IN NUMBER,
2935 p_base_service_package_id IN NUMBER := FND_API.G_MISS_NUM,
2936 p_service_package_id IN NUMBER,
2937 p_stage_set_id IN NUMBER,
2938 p_start_stage_seq IN NUMBER,
2939 p_current_stage_seq IN NUMBER,
2940 p_budget_group_id IN NUMBER,
2941 /*For Bug No : 2811698 Start*/
2942 p_period_fte IN PSB_WS_ACCT1.g_prdamt_tbl_type,
2943 p_total_fte IN NUMBER,
2944 p_num_budget_periods IN NUMBER
2945 /*For Bug No : 2811698 End*/
2946 ) IS
2947
2948 l_account_line_id NUMBER;
2949 l_period_amount PSB_WS_ACCT1.g_prdamt_tbl_type;
2950
2951 l_salary_account_line VARCHAR2(1);
2952 l_ytd_amount NUMBER;
2953 l_annual_fte NUMBER;
2954
2955 l_rounding_diff NUMBER;
2956
2957 l_element_line_id NUMBER;
2958 l_element_cost NUMBER;
2959 /*For Bug No : 2811698 Start*/
2960 l_single_fte_amount NUMBER;
2961 l_annual_fte_ratio NUMBER;
2962 /*For Bug No : 2811698 End*/
2963
2964 l_return_status VARCHAR2(1);
2965 l_msg_count NUMBER;
2966 l_msg_data VARCHAR2(2000);
2967
2968 l_api_name CONSTANT VARCHAR2(30) := 'Distribute_Position_Cost';
2969
2970 cursor c_Base_Element_Cost is
2971 select element_line_id,
2972 element_set_id,
2973 currency_code,
2974 element_cost
2975 from PSB_WS_ELEMENT_LINES a
2976 where p_current_stage_seq between start_stage_seq and current_stage_seq
2977 and pay_element_id = p_pay_element_id
2978 and stage_set_id = p_stage_set_id
2979 and service_package_id = p_base_service_package_id
2980 and budget_year_id = p_budget_year_id
2981 and position_line_id = p_position_line_id;
2982
2983 cursor c_Base_Element_Dist is
2984 select a.account_line_id,
2985 a.budget_group_id,
2986 a.code_combination_id,
2987 a.currency_code,
2988 a.ytd_amount,
2989 a.annual_fte,
2990 a.element_set_id,
2991 a.salary_account_line,
2992 a.period1_amount, a.period2_amount, a.period3_amount, a.period4_amount,
2993 a.period5_amount, a.period6_amount, a.period7_amount, a.period8_amount,
2994 a.period9_amount, a.period10_amount, a.period11_amount, a.period12_amount,
2995 a.period13_amount, a.period14_amount, a.period15_amount, a.period16_amount,
2996 a.period17_amount, a.period18_amount, a.period19_amount, a.period20_amount,
2997 a.period21_amount, a.period22_amount, a.period23_amount, a.period24_amount,
2998 a.period25_amount, a.period26_amount, a.period27_amount, a.period28_amount,
2999 a.period29_amount, a.period30_amount, a.period31_amount, a.period32_amount,
3000 a.period33_amount, a.period34_amount, a.period35_amount, a.period36_amount,
3001 a.period37_amount, a.period38_amount, a.period39_amount, a.period40_amount,
3002 a.period41_amount, a.period42_amount, a.period43_amount, a.period44_amount,
3003 a.period45_amount, a.period46_amount, a.period47_amount, a.period48_amount,
3004 a.period49_amount, a.period50_amount, a.period51_amount, a.period52_amount,
3005 a.period53_amount, a.period54_amount, a.period55_amount, a.period56_amount,
3006 a.period57_amount, a.period58_amount, a.period59_amount, a.period60_amount
3007 from PSB_WS_ACCOUNT_LINES a,
3008 PSB_WS_ELEMENT_LINES b
3009 where a.element_set_id = b.element_set_id
3010 and p_current_stage_seq between a.start_stage_seq and a.current_stage_seq
3011 and a.stage_set_id = p_stage_set_id
3012 and a.service_package_id = p_base_service_package_id
3013 and a.budget_year_id = p_budget_year_id
3014 and a.position_line_id = p_position_line_id
3015 and p_current_stage_seq between b.start_stage_seq and b.current_stage_seq
3016 and b.service_package_id = p_base_service_package_id
3017 and b.pay_element_id = p_pay_element_id
3018 and b.budget_year_id = p_budget_year_id
3019 and b.position_line_id = p_position_line_id;
3020
3021 cursor c_Element_Cost is
3022 select element_line_id,
3023 element_set_id,
3024 currency_code,
3025 element_cost
3026 from PSB_WS_ELEMENT_LINES a
3027 where p_current_stage_seq between start_stage_seq and current_stage_seq
3028 and pay_element_id = p_pay_element_id
3029 and stage_set_id = p_stage_set_id
3030 and service_package_id = p_service_package_id
3031 and budget_year_id = p_budget_year_id
3032 and position_line_id = p_position_line_id;
3033
3034 cursor c_Element_Dist is
3035 select a.account_line_id,
3036 a.budget_group_id,
3037 a.code_combination_id,
3038 a.currency_code,
3039 a.ytd_amount,
3040 a.annual_fte,
3041 a.element_set_id,
3042 a.salary_account_line,
3043 a.period1_amount, a.period2_amount, a.period3_amount, a.period4_amount,
3044 a.period5_amount, a.period6_amount, a.period7_amount, a.period8_amount,
3045 a.period9_amount, a.period10_amount, a.period11_amount, a.period12_amount,
3046 a.period13_amount, a.period14_amount, a.period15_amount, a.period16_amount,
3047 a.period17_amount, a.period18_amount, a.period19_amount, a.period20_amount,
3048 a.period21_amount, a.period22_amount, a.period23_amount, a.period24_amount,
3049 a.period25_amount, a.period26_amount, a.period27_amount, a.period28_amount,
3050 a.period29_amount, a.period30_amount, a.period31_amount, a.period32_amount,
3051 a.period33_amount, a.period34_amount, a.period35_amount, a.period36_amount,
3052 a.period37_amount, a.period38_amount, a.period39_amount, a.period40_amount,
3053 a.period41_amount, a.period42_amount, a.period43_amount, a.period44_amount,
3054 a.period45_amount, a.period46_amount, a.period47_amount, a.period48_amount,
3055 a.period49_amount, a.period50_amount, a.period51_amount, a.period52_amount,
3056 a.period53_amount, a.period54_amount, a.period55_amount, a.period56_amount,
3057 a.period57_amount, a.period58_amount, a.period59_amount, a.period60_amount
3058 from PSB_WS_ACCOUNT_LINES a,
3059 PSB_WS_ELEMENT_LINES b
3060 where a.element_set_id = b.element_set_id
3061 and p_current_stage_seq between a.start_stage_seq and a.current_stage_seq
3062 and a.stage_set_id = p_stage_set_id
3063 and a.service_package_id = p_service_package_id
3064 and a.budget_year_id = p_budget_year_id
3065 and a.position_line_id = p_position_line_id
3066 and p_current_stage_seq between b.start_stage_seq and b.current_stage_seq
3067 and b.service_package_id = p_service_package_id
3068 and b.pay_element_id = p_pay_element_id
3069 and b.budget_year_id = p_budget_year_id
3070 and b.position_line_id = p_position_line_id;
3071
3072
3073 /* For Bug No. 2250319 : Start */
3074
3075 cursor c_Element_Set(element_set NUMBER) IS
3076 SELECT 1
3077 FROM PSB_WS_ELEMENT_LINES
3078 WHERE position_line_id = p_position_line_id
3079 AND budget_year_id = p_budget_year_id
3080 AND element_set_id = element_set
3081 AND pay_element_id < p_pay_element_id;
3082
3083 l_acct_distributed BOOLEAN := FALSE;
3084
3085 /* For Bug No. 2250319 : End */
3086
3087 /* Bug 4379636 Start */
3088 l_root_budget_group_id NUMBER;
3089 l_position_id NUMBER;
3090 l_budget_group_id NUMBER;
3091 l_set_of_books_id NUMBER;
3092 l_data_extract_id NUMBER;
3093 l_chart_of_accounts_id NUMBER;
3094 l_budget_calendar_id NUMBER;
3095 l_distr_percent NUMBER;
3096 l_else_flag VARCHAR2(1) := 'N';
3097 l_ccid NUMBER;
3098
3099 CURSOR c_ws IS
3100 SELECT data_extract_id,
3101 budget_calendar_id,
3102 budget_group_id
3103 FROM psb_worksheets
3104 WHERE worksheet_id = p_worksheet_id;
3105
3106 CURSOR c_bg IS
3107 SELECT nvl(root_budget_group_id, budget_group_id) root_budget_group_id,
3108 nvl(set_of_books_id, root_set_of_books_id) set_of_books_id
3109 FROM PSB_BUDGET_GROUPS_V
3110 WHERE budget_group_id = l_budget_group_id;
3111
3112 CURSOR c_sob IS
3113 SELECT chart_of_accounts_id
3114 FROM GL_SETS_OF_BOOKS
3115 WHERE set_of_books_id = l_set_of_books_id;
3116
3117 CURSOR c_positions IS
3118 SELECT position_id
3119 FROM psb_ws_position_lines
3120 WHERE position_line_id = p_position_line_id;
3121
3122 CURSOR c_fte IS
3123 SELECT annual_fte
3124 FROM psb_ws_fte_lines
3125 WHERE position_line_id = p_position_line_id
3126 AND budget_year_id= p_budget_year_id;
3127
3128 CURSOR c_pos_group IS
3129 SELECT pay_element_id,
3130 distribution_percent,
3131 code_combination_id
3132 FROM psb_element_pos_set_groups pepsg ,
3133 psb_pay_element_distributions pped
3134 WHERE pepsg.position_set_group_id = pped.position_set_group_id
3135 AND pepsg.pay_element_id = p_pay_element_id
3136 AND code_combination_id = l_ccid;
3137
3138 /* Bug 4379636 End */
3139
3140 BEGIN
3141 /* Bug 4379636 Start */
3142
3143 FOR c_ws_rec IN c_ws
3144 LOOP
3145 l_data_extract_id := c_ws_rec.data_extract_id;
3146 l_budget_calendar_id := c_ws_rec.budget_calendar_id;
3147 l_budget_group_id := c_ws_rec.budget_group_id;
3148 END LOOP;
3149
3150 psb_ws_acct1.cache_budget_calendar
3151 (
3152 p_return_status => l_return_status ,
3153 p_budget_calendar_id => l_budget_calendar_id
3154 );
3155
3156 FOR c_bg_rec IN c_bg
3157 LOOP
3158 l_root_budget_group_id := c_bg_rec.root_budget_group_id;
3159 l_set_of_books_id := c_bg_rec.set_of_books_id;
3160 END LOOP;
3161
3162 FOR c_sob_rec IN c_sob
3163 LOOP
3164 l_chart_of_accounts_id := c_sob_rec.chart_of_accounts_id;
3165 END LOOP;
3166
3167 FOR c_positions_rec IN c_positions
3168 LOOP
3169 l_position_id := c_positions_rec.position_id;
3170 END LOOP;
3171
3172 PSB_WS_POS1.Cache_Salary_Dist
3173 (p_return_status => l_return_status,
3174 p_worksheet_id => p_worksheet_id,
3175 p_root_budget_group_id => l_root_budget_group_id,
3176 p_flex_code => l_chart_of_accounts_id,
3177 p_data_extract_id => l_data_extract_id,
3178 p_position_id => l_position_id,
3179 p_position_name => null,
3180 p_start_date => PSB_WS_ACCT1.g_startdate_cy,
3181 p_end_date => PSB_WS_ACCT1.g_end_est_date);
3182
3183 /* Bug 4379636 End */
3184
3185 l_rounding_diff := 0;
3186
3187 if FND_API.to_Boolean(p_insert_from_base) then
3188 begin
3189
3190 for c_Element_Dist_Rec in c_Base_Element_Dist loop
3191
3192 for l_index in 1..PSB_WS_ACCT1.g_max_num_amounts loop
3193 l_period_amount(l_index) := null;
3194 end loop;
3195
3196 /* Bug 4379636 Start */
3197 IF c_Element_Dist_Rec.salary_account_line = 'Y' THEN
3198
3199 FOR i in 1..g_salary_dist.count
3200 LOOP
3201
3202 IF c_Element_Dist_Rec.code_combination_id = g_salary_dist(i).ccid THEN
3203 l_distr_percent := g_salary_dist(i).percent;
3204 END IF;
3205 END LOOP;
3206 ELSE
3207 l_ccid := c_element_dist_rec.code_combination_id;
3208 l_distr_percent := 100;
3209 FOR c_pos_group_rec IN c_pos_group
3210 LOOP
3211 IF c_pos_group_rec.distribution_percent IS NOT NULL THEN
3212 l_distr_percent := c_pos_group_rec.distribution_percent;
3213 ELSE
3214 l_distr_percent := 100;
3215 END IF;
3216 END LOOP;
3217 END IF;
3218 /* Bug 4379636 End */
3219
3220 /*For Bug No : 2811698 Start*/
3221 --changed the logic to calculate the FTE proration here itself
3222 --instead of from Prorate_FTE_Base. This will ensure that period costs
3223 --will come properly.
3224 if (nvl(c_Element_Dist_Rec.annual_fte,0) <> 0) then
3225
3226 /* Bug 4379636 Start */
3227 -- l_annual_fte_ratio := p_total_fte / (c_Element_Dist_Rec.annual_fte * p_num_budget_periods);
3228 l_annual_fte_ratio := ( (p_total_fte / p_num_budget_periods) * l_distr_percent/100)
3229 / c_Element_Dist_Rec.annual_fte;
3230 /* Bug 4379636 End */
3231
3232 l_ytd_amount := c_Element_Dist_Rec.ytd_amount * l_annual_fte_ratio;
3233 else
3234 l_annual_fte_ratio := 0;
3235 l_ytd_amount := 0;
3236 end if;
3237 if (p_total_fte <> 0) then
3238 l_single_fte_amount := l_ytd_amount / p_total_fte;
3239 else
3240 l_single_fte_amount := 0;
3241 end if;
3242
3243 for l_index in 1..PSB_WS_ACCT1.g_max_num_amounts loop
3244 if nvl(p_period_fte(l_index),0) = 0 then
3245 l_period_amount(l_index) := NULL;
3246 else
3247 l_period_amount(l_index) := l_single_fte_amount * p_period_fte(l_index);
3248 end if;
3249 end loop;
3250 /*For Bug No : 2811698 End*/
3251
3252 if p_rounding_factor is not null then
3253 l_rounding_diff := l_rounding_diff +
3254 round(l_ytd_amount / p_rounding_factor) * p_rounding_factor - l_ytd_amount;
3255 end if;
3256
3257 /*For Bug No : 2811698 Start*/
3258 l_annual_fte := p_total_fte / p_num_budget_periods;
3259 /*For Bug No : 2811698 End*/
3260
3261 if c_Element_Dist_Rec.salary_account_line is null then
3262 l_salary_account_line := FND_API.G_FALSE;
3263 else
3264 l_salary_account_line := FND_API.G_TRUE;
3265 end if;
3266
3267 PSB_WS_ACCT1.Create_Account_Dist
3268 (p_api_version => 1.0,
3269 p_return_status => l_return_status,
3270 p_account_line_id => l_account_line_id,
3271 p_worksheet_id => p_worksheet_id,
3272 p_flex_mapping_set_id => p_flex_mapping_set_id,
3273 p_map_accounts => TRUE,
3274 p_check_spal_exists => FND_API.G_FALSE,
3275 p_gl_cutoff_period => null,
3276 p_allocrule_set_id => null,
3277 p_budget_calendar_id => null,
3278 p_rounding_factor => p_rounding_factor,
3279 p_stage_set_id => p_stage_set_id,
3280 p_budget_year_id => p_budget_year_id,
3281 p_budget_group_id => c_Element_Dist_Rec.budget_group_id,
3282 p_ccid => c_Element_Dist_Rec.code_combination_id,
3283 p_currency_code => c_Element_Dist_Rec.currency_code,
3284 p_balance_type => 'E',
3285 p_ytd_amount => l_ytd_amount,
3286 /*For Bug No : 2811698 Start*/
3287 --p_annual_fte => c_Element_Dist_Rec.annual_fte * g_annual_fte_ratio,
3288 p_annual_fte => l_annual_fte,
3289 /*For Bug No : 2811698 End*/
3290 p_period_amount => l_period_amount,
3291 p_position_line_id => p_position_line_id,
3292 p_element_set_id => c_Element_Dist_Rec.element_set_id,
3293 p_salary_account_line => l_salary_account_line,
3294 p_service_package_id => p_service_package_id,
3295 p_start_stage_seq => p_start_stage_seq,
3296 p_current_stage_seq => p_current_stage_seq);
3297
3298 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
3299 raise FND_API.G_EXC_ERROR;
3300 end if;
3301
3302 end loop;
3303
3304 for c_Element_Cost_Rec in c_Base_Element_Cost loop
3305
3306 Create_Element_Lines
3307 (p_api_version => 1.0,
3308 p_return_status => l_return_status,
3309 p_element_line_id => l_element_line_id,
3310 p_check_spel_exists => FND_API.G_FALSE,
3311 p_position_line_id => p_position_line_id,
3312 p_budget_year_id => p_budget_year_id,
3313 p_pay_element_id => p_pay_element_id,
3314 p_currency_code => c_Element_Cost_Rec.currency_code,
3315 /*For Bug No : 2811698 Start*/
3316 --p_element_cost => (c_Element_Cost_Rec.element_cost * nvl(g_annual_fte_ratio, 0) + l_rounding_diff),
3317 p_element_cost => (c_Element_Cost_Rec.element_cost * l_annual_fte_ratio + l_rounding_diff),
3318 /*For Bug No : 2811698 End*/
3319 p_element_set_id => c_Element_Cost_Rec.element_set_id,
3320 p_service_package_id => p_service_package_id,
3321 p_stage_set_id => p_stage_set_id,
3322 p_start_stage_seq => p_start_stage_seq,
3323 p_current_stage_seq => p_current_stage_seq);
3324
3325 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
3326 raise FND_API.G_EXC_ERROR;
3327 end if;
3328
3329 end loop;
3330
3331 end;
3332 else
3333 begin
3334
3335 for c_Element_Dist_Rec in c_Element_Dist loop
3336
3337 for l_index in 1..PSB_WS_ACCT1.g_max_num_amounts loop
3338 l_period_amount(l_index) := null;
3339 end loop;
3340
3341 /* Bug 4379636 Start */
3342 IF c_Element_Dist_Rec.salary_account_line = 'Y' THEN
3343
3344 FOR i in 1..g_salary_dist.count
3345 LOOP
3346
3347 IF c_Element_Dist_Rec.code_combination_id = g_salary_dist(i).ccid THEN
3348 l_distr_percent := g_salary_dist(i).percent;
3349 END IF;
3350 END LOOP;
3351 ELSE
3352 l_ccid := c_element_dist_rec.code_combination_id;
3353 l_distr_percent := 100;
3354 FOR c_pos_group_rec IN c_pos_group
3355 LOOP
3356 IF c_pos_group_rec.distribution_percent IS NOT NULL THEN
3357 l_distr_percent := c_pos_group_rec.distribution_percent;
3358 ELSE
3359 l_distr_percent := 100;
3360 END IF;
3361 END LOOP;
3362 END IF;
3363 /* Bug 4379636 End */
3364
3365 if FND_API.to_Boolean(p_update_from_base) then
3366 begin
3367
3368 for c_Base_Element_Dist_Rec in c_Base_Element_Dist loop
3369
3370 if c_Base_Element_Dist_Rec.code_combination_id = c_Element_Dist_Rec.code_combination_id then
3371 begin
3372
3373 /*For Bug No : 2811698 Start*/
3374 --changed the logic to calculate the FTE proration here itself
3375 --instead of from Prorate_FTE_Base. This will ensure that period costs
3376 --will come properly.
3377 if (nvl(c_Base_Element_Dist_Rec.annual_fte,0) <> 0) then
3378
3379 /* Bug 4379636 Start */
3380 -- l_annual_fte_ratio := p_total_fte / (c_Base_Element_Dist_Rec.annual_fte * p_num_budget_periods);
3381 l_annual_fte_ratio := ( (p_total_fte / p_num_budget_periods) * l_distr_percent/100) /
3382 c_Base_Element_Dist_Rec.annual_fte;
3383 /* Bug 4379636 End */
3384
3385 l_ytd_amount := c_Base_Element_Dist_Rec.ytd_amount * l_annual_fte_ratio;
3386 else
3387 l_annual_fte_ratio := 0;
3388 l_ytd_amount := 0;
3389 end if;
3390 if (p_total_fte <> 0) then
3391 l_single_fte_amount := l_ytd_amount / p_total_fte;
3392 else
3393 l_single_fte_amount := 0;
3394 end if;
3395
3396 for l_index in 1..PSB_WS_ACCT1.g_max_num_amounts loop
3397 if nvl(p_period_fte(l_index),0) = 0 then
3398 l_period_amount(l_index) := NULL;
3399 else
3400 l_period_amount(l_index) := l_single_fte_amount * p_period_fte(l_index);
3401 end if;
3402 end loop;
3403 /*For Bug No : 2811698 End*/
3404
3405 if p_rounding_factor is not null then
3406 l_rounding_diff := l_rounding_diff +
3407 round(l_ytd_amount / p_rounding_factor) * p_rounding_factor - l_ytd_amount;
3408 end if;
3409
3410 /*For Bug No : 2811698 Start*/
3411 --l_annual_fte := c_Base_Element_Dist_Rec.annual_fte * g_annual_fte_ratio;
3412 l_annual_fte := p_total_fte / p_num_budget_periods;
3413 /*For Bug No : 2811698 End*/
3414
3415 end;
3416 end if;
3417
3418 end loop;
3419
3420 end;
3421 else
3422 begin
3423
3424 /*For Bug No : 2811698 Start*/
3425 --changed the logic to calculate the FTE proration here itself
3426 --instead of from Prorate_FTE_Base. This will ensure that period costs
3427 --will come properly.
3428 if (nvl(c_Element_Dist_Rec.annual_fte,0) <> 0) then
3429 /* Bug 4379636 Start */
3430 -- l_annual_fte_ratio := p_total_fte / (c_Element_Dist_Rec.annual_fte * p_num_budget_periods);
3431 l_annual_fte_ratio := ( (p_total_fte / p_num_budget_periods) * l_distr_percent/100) / c_Element_Dist_Rec.annual_fte;
3432
3433 /* Bug 4379636 End */
3434 l_ytd_amount := c_Element_Dist_Rec.ytd_amount * l_annual_fte_ratio;
3435 else
3436 l_annual_fte_ratio := 0;
3437 l_ytd_amount := 0;
3438 end if;
3439 if (p_total_fte <> 0) then
3440 l_single_fte_amount := l_ytd_amount / p_total_fte;
3441 else
3442 l_single_fte_amount := 0;
3443 end if;
3444
3445 for l_index in 1..PSB_WS_ACCT1.g_max_num_amounts loop
3446 if nvl(p_period_fte(l_index),0) = 0 then
3447 l_period_amount(l_index) := NULL;
3448 else
3449 l_period_amount(l_index) := l_single_fte_amount * p_period_fte(l_index);
3450 end if;
3451 end loop;
3452 /*For Bug No : 2811698 End*/
3453
3454 if p_rounding_factor is not null then
3455 l_rounding_diff := l_rounding_diff +
3456 round(l_ytd_amount / p_rounding_factor) * p_rounding_factor - l_ytd_amount;
3457 end if;
3458
3459 /*For Bug No : 2811698 Start*/
3460 --l_annual_fte := c_Element_Dist_Rec.annual_fte * g_annual_fte_ratio;
3461 l_annual_fte := p_total_fte / p_num_budget_periods;
3462 /*For Bug No : 2811698 End*/
3463
3464 end;
3465 end if;
3466
3467 /* For Bug No. 2250319 : Start */
3468 -- If Account Distribution corresponding to a particular Element_Set_Id is modified for FTE, it should not be modified again.
3469 l_acct_distributed := FALSE;
3470
3471 for c_Element_Set_Rec in c_Element_Set(c_Element_Dist_Rec.element_set_id) loop
3472 l_acct_distributed := TRUE;
3473 end loop;
3474
3475 IF NOT l_acct_distributed THEN
3476 /* For Bug No. 2250319 : End */
3477
3478 PSB_WS_ACCT1.Create_Account_Dist
3479 (p_api_version => 1.0,
3480 p_return_status => l_return_status,
3481 p_worksheet_id => p_worksheet_id,
3482 p_account_line_id => c_Element_Dist_Rec.account_line_id,
3483 p_ytd_amount => l_ytd_amount,
3484 p_annual_fte => l_annual_fte,
3485 p_period_amount => l_period_amount,
3486 p_service_package_id => p_service_package_id,
3487 p_current_stage_seq => p_current_stage_seq,
3488 p_budget_group_id => p_budget_group_id);
3489
3490 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
3491 raise FND_API.G_EXC_ERROR;
3492 end if;
3493
3494 -- For Bug No. 2250319
3495 END IF;
3496
3497 --
3498 -- (SRawat: Found while fixing 3140849).
3499 -- If mulitple elements correspond to same element set, during processing
3500 -- of very first element, psb_ws_account_lines will get updated for the
3501 -- element set for annual_fte. This means we need to cache annual fte
3502 -- to process rest of the elements within the same element set rather
3503 -- than reading it from psb_ws_account_lines. Note l_acct_distributed is
3504 -- always FALSE for the very first element on a per element set basis.
3505 --
3506 IF NOT l_acct_distributed THEN
3507 -- Processing very first element within the set. Cache the value to be
3508 -- used by the subsequent elements within the set.
3509 g_last_annual_fte_ratio := l_annual_fte_ratio ;
3510 ELSE
3511 -- Processing subsequent elements within the set. Get the cache value.
3512 l_annual_fte_ratio := g_last_annual_fte_ratio ;
3513 END IF;
3514
3515 end loop;
3516
3517 for c_Element_Cost_Rec in c_Element_Cost loop
3518
3519 if FND_API.to_Boolean(p_update_from_base) then
3520 begin
3521 for c_Base_Element_Cost_Rec in c_Base_Element_Cost loop
3522 l_element_cost := c_Base_Element_Cost_Rec.element_cost * nvl(l_annual_fte_ratio, 0) + l_rounding_diff;
3523 end loop;
3524 end;
3525 else
3526 l_element_cost := c_Element_Cost_Rec.element_cost * nvl(l_annual_fte_ratio, 0) + l_rounding_diff;
3527 end if;
3528
3529 Create_Element_Lines
3530 (p_api_version => 1.0,
3531 p_return_status => l_return_status,
3532 p_element_line_id => c_Element_Cost_Rec.element_line_id,
3533 p_element_cost => l_element_cost,
3534 p_service_package_id => p_service_package_id,
3535 p_current_stage_seq => p_current_stage_seq);
3536
3537 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
3538 raise FND_API.G_EXC_ERROR;
3539 end if;
3540
3541 end loop;
3542
3543 end;
3544 end if;
3545
3546
3547 -- Initialize API return status to success
3548
3549 p_return_status := FND_API.G_RET_STS_SUCCESS;
3550
3551
3552 EXCEPTION
3553 when FND_API.G_EXC_ERROR then
3554 p_return_status := FND_API.G_RET_STS_ERROR;
3555 FND_MSG_PUB.Count_And_Get (p_count => l_msg_count,
3556 p_data => l_msg_data);
3557
3558
3559 when FND_API.G_EXC_UNEXPECTED_ERROR then
3560 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3561 FND_MSG_PUB.Count_And_Get (p_count => l_msg_count,
3562 p_data => l_msg_data);
3563
3564 when OTHERS then
3565 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3566
3567 if FND_MSG_PUB.Check_Msg_Level
3568 (p_message_level => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3569 then
3570 FND_MSG_PUB.Add_Exc_Msg
3571 (p_pkg_name => G_PKG_NAME,
3572 p_procedure_name => l_api_name);
3573 end if;
3574
3575 FND_MSG_PUB.Count_And_Get (p_count => l_msg_count,
3576 p_data => l_msg_data);
3577
3578 END Distribute_Position_Cost;
3579
3580 /* ----------------------------------------------------------------------- */
3581
3582 -- Create or Update existing Position Element Cost line. Use this API when
3583 -- the element_line_id identifier is not known
3584
3585 PROCEDURE Create_Element_Lines
3586 ( p_api_version IN NUMBER,
3587 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_NONE,
3588 p_return_status OUT NOCOPY VARCHAR2,
3589 p_element_line_id OUT NOCOPY NUMBER,
3590 p_check_spel_exists IN VARCHAR2 := FND_API.G_TRUE,
3591 p_position_line_id IN NUMBER,
3592 p_budget_year_id IN NUMBER,
3593 p_pay_element_id IN NUMBER,
3594 p_currency_code IN VARCHAR2,
3595 p_element_cost IN NUMBER,
3596 p_element_set_id IN NUMBER,
3597 p_service_package_id IN NUMBER,
3598 p_stage_set_id IN NUMBER,
3599 p_start_stage_seq IN NUMBER := FND_API.G_MISS_NUM,
3600 p_current_stage_seq IN NUMBER,
3601 p_end_stage_seq IN NUMBER := FND_API.G_MISS_NUM,
3602 p_functional_transaction IN VARCHAR2 := NULL
3603 ) IS
3604
3605 l_api_name CONSTANT VARCHAR2(30) := 'Create_Element_Lines';
3606 l_api_version CONSTANT NUMBER := 1.0;
3607
3608 l_userid NUMBER;
3609 l_loginid NUMBER;
3610
3611 l_element_line_id NUMBER;
3612 l_start_stage_seq NUMBER;
3613 l_set_of_books_id NUMBER;
3614 l_spelid NUMBER;
3615 l_spelcost NUMBER;
3616 l_spel_exists VARCHAR2(1) := FND_API.G_FALSE;
3617
3618 l_return_status VARCHAR2(1);
3619 l_msg_data VARCHAR2(2000);
3620 l_msg_count NUMBER;
3621
3622 cursor c_Seq is
3623 select psb_ws_element_lines_s.nextval ElmLineID
3624 from dual;
3625
3626 cursor c_Wel_SP is
3627 select element_line_id,
3628 element_cost
3629 from PSB_WS_ELEMENT_LINES a
3630 where p_current_stage_seq between start_stage_seq and current_stage_seq
3631 and pay_element_id = p_pay_element_id
3632 and stage_set_id = p_stage_set_id
3633 and service_package_id = p_service_package_id
3634 and budget_year_id = p_budget_year_id
3635 and position_line_id = p_position_line_id;
3636
3637 BEGIN
3638
3639 -- Standard call to check for call compatibility
3640
3641 if not FND_API.Compatible_API_Call (l_api_version,
3642 p_api_version,
3643 l_api_name,
3644 G_PKG_NAME)
3645 then
3646 raise FND_API.G_EXC_UNEXPECTED_ERROR;
3647 end if;
3648
3649 l_userid := FND_GLOBAL.USER_ID;
3650 l_loginid := FND_GLOBAL.LOGIN_ID;
3651
3652 if p_start_stage_seq = FND_API.G_MISS_NUM then
3653 l_start_stage_seq := p_current_stage_seq;
3654 else
3655 l_start_stage_seq := p_start_stage_seq;
3656 end if;
3657
3658 if FND_API.to_Boolean(p_check_spel_exists) then
3659 begin
3660
3661 for c_Wel_Rec in c_Wel_SP loop
3662 l_spelid := c_Wel_Rec.element_line_id;
3663 l_spelcost := c_Wel_Rec.element_cost;
3664
3665 l_spel_exists := FND_API.G_TRUE;
3666 end loop;
3667
3668 end;
3669 end if;
3670
3671 if FND_API.to_Boolean(l_spel_exists) then
3672 begin
3673
3674 update PSB_WS_ELEMENT_LINES
3675 set element_cost = nvl(p_element_cost, 0),
3676 element_set_id = p_element_set_id,
3677 current_stage_seq = p_current_stage_seq,
3678 end_stage_seq = decode(p_end_stage_seq, FND_API.G_MISS_NUM, end_stage_seq, p_end_stage_seq),
3679 last_update_date = sysdate,
3680 last_updated_by = l_userid,
3681 last_update_login = l_loginid
3682 where element_line_id = l_spelid;
3683
3684 end;
3685 else
3686 begin
3687
3688 update PSB_WS_ELEMENT_LINES a
3689 set element_cost = nvl(p_element_cost, 0),
3690 element_set_id = p_element_set_id,
3691 current_stage_seq = p_current_stage_seq,
3692 end_stage_seq = decode(p_end_stage_seq, FND_API.G_MISS_NUM, end_stage_seq, p_end_stage_seq),
3693 last_update_date = sysdate,
3694 last_updated_by = l_userid,
3695 last_update_login = l_loginid
3696 where start_stage_seq = p_start_stage_seq
3697 and currency_code = p_currency_code
3698 and pay_element_id = p_pay_element_id
3699 and stage_set_id = p_stage_set_id
3700 and service_package_id = p_service_package_id
3701 and budget_year_id = p_budget_year_id
3702 and position_line_id = p_position_line_id;
3703
3704 end;
3705 end if;
3706
3707 if SQL%NOTFOUND then
3708 begin
3709
3710 for c_Seq_Rec in c_Seq loop
3711 l_element_line_id := c_Seq_Rec.ElmLineID;
3712 end loop;
3713
3714 insert into PSB_WS_ELEMENT_LINES
3715 (element_line_id,
3716 position_line_id,
3717 budget_year_id,
3718 pay_element_id,
3719 currency_code,
3720 element_cost,
3721 element_set_id,
3722 service_package_id,
3723 stage_set_id,
3724 start_stage_seq,
3725 current_stage_seq,
3726 end_stage_seq,
3727 last_update_date,
3728 last_updated_by,
3729 last_update_login,
3730 created_by,
3731 creation_date,
3732 functional_transaction)
3733 values (l_element_line_id,
3734 p_position_line_id,
3735 p_budget_year_id,
3736 p_pay_element_id,
3737 p_currency_code,
3738 nvl(p_element_cost, 0),
3739 p_element_set_id,
3740 p_service_package_id,
3741 p_stage_set_id,
3742 l_start_stage_seq,
3743 p_current_stage_seq,
3744 decode(p_end_stage_seq, FND_API.G_MISS_NUM, null, p_end_stage_seq),
3745 sysdate,
3746 l_userid,
3747 l_loginid,
3748 l_userid,
3749 sysdate,
3750 p_functional_transaction);
3751
3752 p_element_line_id := l_element_line_id;
3753
3754 end;
3755 end if;
3756
3757 -- Initialize API return status to success
3758
3759 p_return_status := FND_API.G_RET_STS_SUCCESS;
3760
3761
3762 EXCEPTION
3763
3764 when FND_API.G_EXC_ERROR then
3765 p_return_status := FND_API.G_RET_STS_ERROR;
3766 FND_MSG_PUB.Count_And_Get (p_count => l_msg_count,
3767 p_data => l_msg_data);
3768
3769
3770 when FND_API.G_EXC_UNEXPECTED_ERROR then
3771 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3772 FND_MSG_PUB.Count_And_Get (p_count => l_msg_count,
3773 p_data => l_msg_data);
3774
3775 when OTHERS then
3776 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3777
3778 if FND_MSG_PUB.Check_Msg_Level
3779 (p_message_level => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3780 then
3781 FND_MSG_PUB.Add_Exc_Msg
3782 (p_pkg_name => G_PKG_NAME,
3783 p_procedure_name => l_api_name);
3784 end if;
3785
3786 FND_MSG_PUB.Count_And_Get (p_count => l_msg_count,
3787 p_data => l_msg_data);
3788
3789 END Create_Element_Lines;
3790
3791 /* ----------------------------------------------------------------------- */
3792
3793 -- Update existing Position Element Cost line and create new Stages if
3794 -- required and the flag p_check_stages is set to FND_API.G_TRUE
3795
3796 PROCEDURE Create_Element_Lines
3797 ( p_api_version IN NUMBER,
3798 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_NONE,
3799 p_return_status OUT NOCOPY VARCHAR2,
3800 p_check_stages IN VARCHAR2 := FND_API.G_TRUE,
3801 p_element_line_id IN NUMBER,
3802 p_service_package_id IN NUMBER := FND_API.G_MISS_NUM,
3803 p_current_stage_seq IN NUMBER := FND_API.G_MISS_NUM,
3804 p_element_cost IN NUMBER := FND_API.G_MISS_NUM
3805 ) IS
3806
3807 l_api_name CONSTANT VARCHAR2(30) := 'Create_Element_Lines';
3808 l_api_version CONSTANT NUMBER := 1.0;
3809
3810 l_new_stage VARCHAR2(1) := FND_API.G_FALSE;
3811
3812 l_previous_stage_seq NUMBER;
3813
3814 l_userid NUMBER;
3815 l_loginid NUMBER;
3816
3817 l_position_line_id NUMBER;
3818 l_budget_year_id NUMBER;
3819 l_pay_element_id NUMBER;
3820 l_currency_code VARCHAR2(10);
3821 l_element_cost NUMBER;
3822 l_element_set_id NUMBER;
3823 l_service_package_id NUMBER;
3824 l_stage_set_id NUMBER;
3825 l_start_stage_seq NUMBER;
3826 l_current_stage_seq NUMBER;
3827 l_end_stage_seq NUMBER;
3828
3829 l_spelid NUMBER;
3830 l_spelcost NUMBER;
3831 l_spel_exists VARCHAR2(1) := FND_API.G_FALSE;
3832
3833 sql_wel VARCHAR2(6000);
3834 num_wel INTEGER;
3835
3836 l_set_of_books_id NUMBER;
3837 l_element_line_id NUMBER;
3838
3839 l_return_status VARCHAR2(1);
3840 l_msg_data VARCHAR2(2000);
3841 l_msg_count NUMBER;
3842
3843 cursor c_ElmSeq is
3844 select psb_ws_element_lines_s.nextval ElmLineID
3845 from dual;
3846
3847 cursor c_PrevStage is
3848 select Max(sequence_number) sequence_number
3849 from PSB_BUDGET_STAGES
3850 where sequence_number < l_current_stage_seq
3851 and budget_stage_set_id = l_stage_set_id;
3852
3853 BEGIN
3854
3855 -- Standard call to check for call compatibility
3856
3857 if not FND_API.Compatible_API_Call (l_api_version,
3858 p_api_version,
3859 l_api_name,
3860 G_PKG_NAME)
3861 then
3862 raise FND_API.G_EXC_UNEXPECTED_ERROR;
3863 end if;
3864
3865 sql_wel := 'select position_line_id, budget_year_id, pay_element_id, currency_code, element_cost, ' ||
3866 'element_set_id, service_package_id, stage_set_id, start_stage_seq, current_stage_seq, ' ||
3867 'end_stage_seq ' ||
3868 'from PSB_WS_ELEMENT_LINES ' ||
3869 'where element_line_id = :ElemLineID';
3870
3871 execute immediate sql_wel into
3872 l_position_line_id, l_budget_year_id, l_pay_element_id, l_currency_code, l_element_cost, l_element_set_id,
3873 l_service_package_id, l_stage_set_id, l_start_stage_seq, l_current_stage_seq, l_end_stage_seq
3874 using p_element_line_id;
3875
3876 -- If Service Package is being modified, check whether the target element line exists
3877
3878 if ((p_service_package_id <> FND_API.G_MISS_NUM) and
3879 (p_service_package_id <> l_service_package_id)) then
3880 begin
3881
3882 sql_wel := 'select ' ||
3883 'element_line_id, element_cost ' ||
3884 'from PSB_WS_ELEMENT_LINES a ' ||
3885 'where :current_stage_seq between start_stage_seq and current_stage_seq ' ||
3886 'and pay_element_id = :pay_element_id ' ||
3887 'and stage_set_id = :stage_set_id ' ||
3888 'and service_package_id = :service_package_id ' ||
3889 'and budget_year_id = :budget_year_id ' ||
3890 'and position_line_id = :position_line_id';
3891
3892 begin
3893
3894 execute immediate sql_wel into
3895 l_spelid, l_spelcost
3896 using l_current_stage_seq, l_pay_element_id, l_stage_set_id, p_service_package_id, l_budget_year_id,
3897 l_position_line_id;
3898
3899 l_spel_exists := FND_API.G_TRUE;
3900
3901 exception
3902 when others then
3903 l_spel_exists := FND_API.G_FALSE;
3904 end;
3905
3906 l_new_stage := FND_API.G_TRUE;
3907
3908 if FND_API.to_Boolean(l_spel_exists) then
3909 begin
3910
3911 PSB_WORKSHEET.Delete_WEL
3912 (p_api_version => 1.0,
3913 p_return_status => l_return_status,
3914 p_element_line_id => l_spelid);
3915
3916 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
3917 raise FND_API.G_EXC_ERROR;
3918 end if;
3919
3920 end;
3921 end if;
3922
3923 end;
3924 end if;
3925
3926 if ((p_element_cost <> FND_API.G_MISS_NUM) and
3927 (p_element_cost <> l_element_cost)) then
3928 l_new_stage := FND_API.G_TRUE;
3929 end if;
3930
3931 if ((FND_API.to_Boolean(l_new_stage)) and
3932 (l_start_stage_seq = l_current_stage_seq)) then
3933 l_new_stage := FND_API.G_FALSE;
3934 end if;
3935
3936 l_userid := FND_GLOBAL.USER_ID;
3937 l_loginid := FND_GLOBAL.LOGIN_ID;
3938
3939 if ((FND_API.to_Boolean(p_check_stages)) and
3940 (FND_API.to_Boolean(l_new_stage)) and
3941 (l_start_stage_seq < l_current_stage_seq)) then
3942 begin
3943
3944 for c_PrevStage_Rec in c_PrevStage loop
3945 l_previous_stage_seq := c_PrevStage_Rec.sequence_number;
3946 end loop;
3947
3948 for c_ElmSeq_Rec in c_ElmSeq loop
3949 l_element_line_id := c_ElmSeq_Rec.ElmLineID;
3950 end loop;
3951
3952 -- Bug#5030383
3953 -- Replaced l_element_line_id by :b_element_line_id
3954 -- Replaced stage_seq by :b_current_stage_seq and :b_end_stage_seq
3955 -- Replaced sysdate by :b_last_update_date and :b_creation_date
3956 -- Replaced l_userid by :b_last_updated_by
3957 -- Replaced l_loginid by :b_last_update_login
3958 -- Replaced p_element_line_id by :b_element_line_id
3959 sql_wel := 'insert into PSB_WS_ELEMENT_LINES ' ||
3960 '(element_line_id, ' ||
3961 'position_line_id, ' ||
3962 'budget_year_id, ' ||
3963 'pay_element_id, ' ||
3964 'currency_code, ' ||
3965 'element_cost, ' ||
3966 'element_set_id, ' ||
3967 'service_package_id, ' ||
3968 'stage_set_id, ' ||
3969 'start_stage_seq, ' ||
3970 'current_stage_seq, ' ||
3971 'end_stage_seq, ' ||
3972 'functional_transaction, ' ||
3973 'last_update_date, ' ||
3974 'last_updated_by, ' ||
3975 'last_update_login, ' ||
3976 'created_by, ' ||
3977 'creation_date) ' ||
3978 'select :b_element_line_id, ' ||
3979 'position_line_id, ' ||
3980 'budget_year_id, ' ||
3981 'pay_element_id, ' ||
3982 'currency_code, ' ||
3983 'element_cost, ' ||
3984 'element_set_id, ' ||
3985 'service_package_id, ' ||
3986 'stage_set_id, ' ||
3987 'start_stage_seq, ' ||
3988 ':b_current_stage_seq, ' ||
3989 ':b_end_stage_seq, ' ||
3990 'functional_transaction, ' ||
3991 ':b_last_update_date, ' ||
3992 ':b_last_updated_by, ' ||
3993 ':b_last_update_login, ' ||
3994 ':b_last_updated_by, ' ||
3995 ':b_creation_date ' ||
3996 'from PSB_WS_ELEMENT_LINES ' ||
3997 'where element_line_id = :b_element_line_id' ;
3998
3999 -- Bug#5030383
4000
4001 EXECUTE IMMEDIATE
4002 sql_wel
4003 USING
4004 l_element_line_id
4005 , l_previous_stage_seq
4006 , l_previous_stage_seq
4007 , SYSDATE
4008 , l_userid
4009 , l_loginid
4010 , l_userid
4011 , SYSDATE
4012 , p_element_line_id ;
4013
4014 /* if num_wel < 0 then
4015 raise FND_API.G_EXC_ERROR;
4016 end if; */
4017
4018 update PSB_WS_ELEMENT_LINES
4019 set element_cost = decode(p_element_cost, FND_API.G_MISS_NUM, element_cost, null, 0, p_element_cost),
4020 service_package_id = decode(p_service_package_id, FND_API.G_MISS_NUM, service_package_id, p_service_package_id),
4021 start_stage_seq = l_current_stage_seq,
4022 current_stage_seq = l_current_stage_seq,
4023 last_update_date = sysdate,
4024 last_updated_by = l_userid,
4025 last_update_login = l_loginid
4026 where element_line_id = p_element_line_id;
4027
4028 end;
4029 else
4030 begin
4031
4032 update PSB_WS_ELEMENT_LINES
4033 set element_cost = decode(p_element_cost, FND_API.G_MISS_NUM, element_cost, null, 0, p_element_cost),
4034 service_package_id = decode(p_service_package_id, FND_API.G_MISS_NUM, service_package_id, p_service_package_id),
4035 current_stage_seq = decode(p_current_stage_seq, FND_API.G_MISS_NUM, current_stage_seq, p_current_stage_seq),
4036 last_update_date = sysdate,
4037 last_updated_by = l_userid,
4038 last_update_login = l_loginid
4039 where element_line_id = p_element_line_id;
4040
4041 end;
4042 end if;
4043
4044 -- Initialize API return status to success
4045
4046 p_return_status := FND_API.G_RET_STS_SUCCESS;
4047
4048
4049 EXCEPTION
4050
4051 when FND_API.G_EXC_ERROR then
4052 p_return_status := FND_API.G_RET_STS_ERROR;
4053 FND_MSG_PUB.Count_And_Get (p_count => l_msg_count,
4054 p_data => l_msg_data);
4055
4056
4057 when FND_API.G_EXC_UNEXPECTED_ERROR then
4058 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4059 FND_MSG_PUB.Count_And_Get (p_count => l_msg_count,
4060 p_data => l_msg_data);
4061
4062 when OTHERS then
4063 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4064
4065 if FND_MSG_PUB.Check_Msg_Level
4066 (p_message_level => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4067 then
4068 FND_MSG_PUB.Add_Exc_Msg
4069 (p_pkg_name => G_PKG_NAME,
4070 p_procedure_name => l_api_name);
4071 end if;
4072
4073 FND_MSG_PUB.Count_And_Get (p_count => l_msg_count,
4074 p_data => l_msg_data);
4075
4076 END Create_Element_Lines;
4077
4078 /* ----------------------------------------------------------------------- */
4079
4080 -- Distribute the computed Annual FTE across the recurring Salary Account
4081 -- Distributions
4082
4083 PROCEDURE Update_Annual_FTE
4084 ( p_api_version IN NUMBER,
4085 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_NONE,
4086 p_return_status OUT NOCOPY VARCHAR2,
4087 p_worksheet_id IN NUMBER,
4088 p_position_line_id IN NUMBER,
4089 p_budget_year_id IN NUMBER,
4090 p_service_package_id IN NUMBER,
4091 p_stage_set_id IN NUMBER,
4092 p_current_stage_seq IN NUMBER,
4093 p_budget_group_id IN NUMBER
4094 ) IS
4095
4096 l_api_name CONSTANT VARCHAR2(30) := 'Update_Annual_FTE';
4097 l_api_version CONSTANT NUMBER := 1.0;
4098
4099 l_ytd_amount NUMBER;
4100 l_period_amount PSB_WS_ACCT1.g_prdamt_tbl_type;
4101 l_annual_fte NUMBER;
4102
4103 l_init_index BINARY_INTEGER;
4104 l_salary_index BINARY_INTEGER;
4105
4106 l_return_status VARCHAR2(1);
4107
4108 cursor c_FTE is
4109 select annual_fte
4110 from PSB_WS_FTE_LINES a
4111 where p_current_stage_seq between start_stage_seq and current_stage_seq
4112 and stage_set_id = p_stage_set_id
4113 and service_package_id = p_service_package_id
4114 and budget_year_id = p_budget_year_id
4115 and position_line_id = p_position_line_id;
4116
4117 cursor c_Salary_Dist is
4118 select a.account_line_id,
4119 a.ytd_amount,
4120 a.period1_amount, a.period2_amount, a.period3_amount, a.period4_amount,
4121 a.period5_amount, a.period6_amount, a.period7_amount, a.period8_amount,
4122 a.period9_amount, a.period10_amount, a.period11_amount, a.period12_amount,
4123 a.period13_amount, a.period14_amount, a.period15_amount, a.period16_amount,
4124 a.period17_amount, a.period18_amount, a.period19_amount, a.period20_amount,
4125 a.period21_amount, a.period22_amount, a.period23_amount, a.period24_amount,
4126 a.period25_amount, a.period26_amount, a.period27_amount, a.period28_amount,
4127 a.period29_amount, a.period30_amount, a.period31_amount, a.period32_amount,
4128 a.period33_amount, a.period34_amount, a.period35_amount, a.period36_amount,
4129 a.period37_amount, a.period38_amount, a.period39_amount, a.period40_amount,
4130 a.period41_amount, a.period42_amount, a.period43_amount, a.period44_amount,
4131 a.period45_amount, a.period46_amount, a.period47_amount, a.period48_amount,
4132 a.period49_amount, a.period50_amount, a.period51_amount, a.period52_amount,
4133 a.period53_amount, a.period54_amount, a.period55_amount, a.period56_amount,
4134 a.period57_amount, a.period58_amount, a.period59_amount, a.period60_amount
4135 from PSB_WS_ACCOUNT_LINES a
4136 where exists
4137 (select 1
4138 from PSB_PAY_ELEMENTS b,
4139 PSB_WS_ELEMENT_LINES c
4140 where b.processing_type = 'R'
4141 and b.pay_element_id = c.pay_element_id
4142 and c.element_set_id = a.element_set_id
4143 and c.budget_year_id = p_budget_year_id
4144 and c.position_line_id = p_position_line_id)
4145 and a.salary_account_line = 'Y'
4146 and p_current_stage_seq between a.start_stage_seq and a.current_stage_seq
4147 and a.stage_set_id = p_stage_set_id
4148 and a.service_package_id = p_service_package_id
4149 and a.budget_year_id = p_budget_year_id
4150 and a.position_line_id = p_position_line_id;
4151
4152 BEGIN
4153
4154 -- Standard call to check for call compatibility
4155
4156 if not FND_API.Compatible_API_Call (l_api_version,
4157 p_api_version,
4158 l_api_name,
4159 G_PKG_NAME)
4160 then
4161 raise FND_API.G_EXC_UNEXPECTED_ERROR;
4162 end if;
4163
4164 l_ytd_amount := 0;
4165
4166 Initialize_Element_Dist;
4167
4168 for c_Salary_Dist_Rec in c_Salary_Dist loop
4169
4170 g_num_element_dist := g_num_element_dist + 1;
4171
4172 l_ytd_amount := l_ytd_amount + c_Salary_Dist_Rec.ytd_amount;
4173
4174 g_element_dist(g_num_element_dist).account_line_id := c_Salary_Dist_Rec.account_line_id;
4175 g_element_dist(g_num_element_dist).ytd_amount := c_Salary_Dist_Rec.ytd_amount;
4176 g_element_dist(g_num_element_dist).period1_amount := c_Salary_Dist_Rec.period1_amount;
4177 g_element_dist(g_num_element_dist).period2_amount := c_Salary_Dist_Rec.period2_amount;
4178 g_element_dist(g_num_element_dist).period3_amount := c_Salary_Dist_Rec.period3_amount;
4179 g_element_dist(g_num_element_dist).period4_amount := c_Salary_Dist_Rec.period4_amount;
4180 g_element_dist(g_num_element_dist).period5_amount := c_Salary_Dist_Rec.period5_amount;
4181 g_element_dist(g_num_element_dist).period6_amount := c_Salary_Dist_Rec.period6_amount;
4182 g_element_dist(g_num_element_dist).period7_amount := c_Salary_Dist_Rec.period7_amount;
4183 g_element_dist(g_num_element_dist).period8_amount := c_Salary_Dist_Rec.period8_amount;
4184 g_element_dist(g_num_element_dist).period9_amount := c_Salary_Dist_Rec.period9_amount;
4185 g_element_dist(g_num_element_dist).period10_amount := c_Salary_Dist_Rec.period10_amount;
4186 g_element_dist(g_num_element_dist).period11_amount := c_Salary_Dist_Rec.period11_amount;
4187 g_element_dist(g_num_element_dist).period12_amount := c_Salary_Dist_Rec.period12_amount;
4188 g_element_dist(g_num_element_dist).period13_amount := c_Salary_Dist_Rec.period13_amount;
4189 g_element_dist(g_num_element_dist).period14_amount := c_Salary_Dist_Rec.period14_amount;
4190 g_element_dist(g_num_element_dist).period15_amount := c_Salary_Dist_Rec.period15_amount;
4191 g_element_dist(g_num_element_dist).period16_amount := c_Salary_Dist_Rec.period16_amount;
4192 g_element_dist(g_num_element_dist).period17_amount := c_Salary_Dist_Rec.period17_amount;
4193 g_element_dist(g_num_element_dist).period18_amount := c_Salary_Dist_Rec.period18_amount;
4194 g_element_dist(g_num_element_dist).period19_amount := c_Salary_Dist_Rec.period19_amount;
4195 g_element_dist(g_num_element_dist).period20_amount := c_Salary_Dist_Rec.period20_amount;
4196 g_element_dist(g_num_element_dist).period21_amount := c_Salary_Dist_Rec.period21_amount;
4197 g_element_dist(g_num_element_dist).period22_amount := c_Salary_Dist_Rec.period22_amount;
4198 g_element_dist(g_num_element_dist).period23_amount := c_Salary_Dist_Rec.period23_amount;
4199 g_element_dist(g_num_element_dist).period24_amount := c_Salary_Dist_Rec.period24_amount;
4200 g_element_dist(g_num_element_dist).period25_amount := c_Salary_Dist_Rec.period25_amount;
4201 g_element_dist(g_num_element_dist).period26_amount := c_Salary_Dist_Rec.period26_amount;
4202 g_element_dist(g_num_element_dist).period27_amount := c_Salary_Dist_Rec.period27_amount;
4203 g_element_dist(g_num_element_dist).period28_amount := c_Salary_Dist_Rec.period28_amount;
4204 g_element_dist(g_num_element_dist).period29_amount := c_Salary_Dist_Rec.period29_amount;
4205 g_element_dist(g_num_element_dist).period30_amount := c_Salary_Dist_Rec.period30_amount;
4206 g_element_dist(g_num_element_dist).period31_amount := c_Salary_Dist_Rec.period31_amount;
4207 g_element_dist(g_num_element_dist).period32_amount := c_Salary_Dist_Rec.period32_amount;
4208 g_element_dist(g_num_element_dist).period33_amount := c_Salary_Dist_Rec.period33_amount;
4209 g_element_dist(g_num_element_dist).period34_amount := c_Salary_Dist_Rec.period34_amount;
4210 g_element_dist(g_num_element_dist).period35_amount := c_Salary_Dist_Rec.period35_amount;
4211 g_element_dist(g_num_element_dist).period36_amount := c_Salary_Dist_Rec.period36_amount;
4212 g_element_dist(g_num_element_dist).period37_amount := c_Salary_Dist_Rec.period37_amount;
4213 g_element_dist(g_num_element_dist).period38_amount := c_Salary_Dist_Rec.period38_amount;
4214 g_element_dist(g_num_element_dist).period39_amount := c_Salary_Dist_Rec.period39_amount;
4215 g_element_dist(g_num_element_dist).period40_amount := c_Salary_Dist_Rec.period40_amount;
4216 g_element_dist(g_num_element_dist).period41_amount := c_Salary_Dist_Rec.period41_amount;
4217 g_element_dist(g_num_element_dist).period42_amount := c_Salary_Dist_Rec.period42_amount;
4218 g_element_dist(g_num_element_dist).period43_amount := c_Salary_Dist_Rec.period43_amount;
4219 g_element_dist(g_num_element_dist).period44_amount := c_Salary_Dist_Rec.period44_amount;
4220 g_element_dist(g_num_element_dist).period45_amount := c_Salary_Dist_Rec.period45_amount;
4221 g_element_dist(g_num_element_dist).period46_amount := c_Salary_Dist_Rec.period46_amount;
4222 g_element_dist(g_num_element_dist).period47_amount := c_Salary_Dist_Rec.period47_amount;
4223 g_element_dist(g_num_element_dist).period48_amount := c_Salary_Dist_Rec.period48_amount;
4224 g_element_dist(g_num_element_dist).period49_amount := c_Salary_Dist_Rec.period49_amount;
4225 g_element_dist(g_num_element_dist).period50_amount := c_Salary_Dist_Rec.period50_amount;
4226 g_element_dist(g_num_element_dist).period51_amount := c_Salary_Dist_Rec.period51_amount;
4227 g_element_dist(g_num_element_dist).period52_amount := c_Salary_Dist_Rec.period52_amount;
4228 g_element_dist(g_num_element_dist).period53_amount := c_Salary_Dist_Rec.period53_amount;
4229 g_element_dist(g_num_element_dist).period54_amount := c_Salary_Dist_Rec.period54_amount;
4230 g_element_dist(g_num_element_dist).period55_amount := c_Salary_Dist_Rec.period55_amount;
4231 g_element_dist(g_num_element_dist).period56_amount := c_Salary_Dist_Rec.period56_amount;
4232 g_element_dist(g_num_element_dist).period57_amount := c_Salary_Dist_Rec.period57_amount;
4233 g_element_dist(g_num_element_dist).period58_amount := c_Salary_Dist_Rec.period58_amount;
4234 g_element_dist(g_num_element_dist).period59_amount := c_Salary_Dist_Rec.period59_amount;
4235 g_element_dist(g_num_element_dist).period60_amount := c_Salary_Dist_Rec.period60_amount;
4236
4237 end loop;
4238
4239 if l_ytd_amount <> 0 then
4240 begin
4241
4242 for c_FTE_Rec in c_FTE loop
4243 l_annual_fte := c_FTE_Rec.annual_fte;
4244 end loop;
4245
4246 for l_salary_index in 1..g_num_element_dist loop
4247
4248 for l_init_index in 1..PSB_WS_ACCT1.g_max_num_amounts loop
4249 l_period_amount(l_init_index) := null;
4250 end loop;
4251
4252 l_period_amount(1) := g_element_dist(l_salary_index).period1_amount;
4253 l_period_amount(2) := g_element_dist(l_salary_index).period2_amount;
4254 l_period_amount(3) := g_element_dist(l_salary_index).period3_amount;
4255 l_period_amount(4) := g_element_dist(l_salary_index).period4_amount;
4256 l_period_amount(5) := g_element_dist(l_salary_index).period5_amount;
4257 l_period_amount(6) := g_element_dist(l_salary_index).period6_amount;
4258 l_period_amount(7) := g_element_dist(l_salary_index).period7_amount;
4259 l_period_amount(8) := g_element_dist(l_salary_index).period8_amount;
4260 l_period_amount(9) := g_element_dist(l_salary_index).period9_amount;
4261 l_period_amount(10) := g_element_dist(l_salary_index).period10_amount;
4262 l_period_amount(11) := g_element_dist(l_salary_index).period11_amount;
4263 l_period_amount(12) := g_element_dist(l_salary_index).period12_amount;
4264 l_period_amount(13) := g_element_dist(l_salary_index).period13_amount;
4265 l_period_amount(14) := g_element_dist(l_salary_index).period14_amount;
4266 l_period_amount(15) := g_element_dist(l_salary_index).period15_amount;
4267 l_period_amount(16) := g_element_dist(l_salary_index).period16_amount;
4268 l_period_amount(17) := g_element_dist(l_salary_index).period17_amount;
4269 l_period_amount(18) := g_element_dist(l_salary_index).period18_amount;
4270 l_period_amount(19) := g_element_dist(l_salary_index).period19_amount;
4271 l_period_amount(20) := g_element_dist(l_salary_index).period20_amount;
4272 l_period_amount(21) := g_element_dist(l_salary_index).period21_amount;
4273 l_period_amount(22) := g_element_dist(l_salary_index).period22_amount;
4274 l_period_amount(23) := g_element_dist(l_salary_index).period23_amount;
4275 l_period_amount(24) := g_element_dist(l_salary_index).period24_amount;
4276 l_period_amount(25) := g_element_dist(l_salary_index).period25_amount;
4277 l_period_amount(26) := g_element_dist(l_salary_index).period26_amount;
4278 l_period_amount(27) := g_element_dist(l_salary_index).period27_amount;
4279 l_period_amount(28) := g_element_dist(l_salary_index).period28_amount;
4280 l_period_amount(29) := g_element_dist(l_salary_index).period29_amount;
4281 l_period_amount(30) := g_element_dist(l_salary_index).period30_amount;
4282 l_period_amount(31) := g_element_dist(l_salary_index).period31_amount;
4283 l_period_amount(32) := g_element_dist(l_salary_index).period32_amount;
4284 l_period_amount(33) := g_element_dist(l_salary_index).period33_amount;
4285 l_period_amount(34) := g_element_dist(l_salary_index).period34_amount;
4286 l_period_amount(35) := g_element_dist(l_salary_index).period35_amount;
4287 l_period_amount(36) := g_element_dist(l_salary_index).period36_amount;
4288 l_period_amount(37) := g_element_dist(l_salary_index).period37_amount;
4289 l_period_amount(38) := g_element_dist(l_salary_index).period38_amount;
4290 l_period_amount(39) := g_element_dist(l_salary_index).period39_amount;
4291 l_period_amount(40) := g_element_dist(l_salary_index).period40_amount;
4292 l_period_amount(41) := g_element_dist(l_salary_index).period41_amount;
4293 l_period_amount(42) := g_element_dist(l_salary_index).period42_amount;
4294 l_period_amount(43) := g_element_dist(l_salary_index).period43_amount;
4295 l_period_amount(44) := g_element_dist(l_salary_index).period44_amount;
4296 l_period_amount(45) := g_element_dist(l_salary_index).period45_amount;
4297 l_period_amount(46) := g_element_dist(l_salary_index).period46_amount;
4298 l_period_amount(47) := g_element_dist(l_salary_index).period47_amount;
4299 l_period_amount(48) := g_element_dist(l_salary_index).period48_amount;
4300 l_period_amount(49) := g_element_dist(l_salary_index).period49_amount;
4301 l_period_amount(50) := g_element_dist(l_salary_index).period50_amount;
4302 l_period_amount(51) := g_element_dist(l_salary_index).period51_amount;
4303 l_period_amount(52) := g_element_dist(l_salary_index).period52_amount;
4304 l_period_amount(53) := g_element_dist(l_salary_index).period53_amount;
4305 l_period_amount(54) := g_element_dist(l_salary_index).period54_amount;
4306 l_period_amount(55) := g_element_dist(l_salary_index).period55_amount;
4307 l_period_amount(56) := g_element_dist(l_salary_index).period56_amount;
4308 l_period_amount(57) := g_element_dist(l_salary_index).period57_amount;
4309 l_period_amount(58) := g_element_dist(l_salary_index).period58_amount;
4310 l_period_amount(59) := g_element_dist(l_salary_index).period59_amount;
4311 l_period_amount(60) := g_element_dist(l_salary_index).period60_amount;
4312
4313 PSB_WS_ACCT1.Create_Account_Dist
4314 (p_api_version => 1.0,
4315 p_return_status => l_return_status,
4316 p_worksheet_id => p_worksheet_id,
4317 p_account_line_id => g_element_dist(l_salary_index).account_line_id,
4318 p_check_stages => FND_API.G_FALSE,
4319 p_ytd_amount => g_element_dist(l_salary_index).ytd_amount,
4320 p_annual_fte => l_annual_fte * g_element_dist(l_salary_index).ytd_amount / l_ytd_amount,
4321 p_period_amount => l_period_amount,
4322 p_budget_group_id => p_budget_group_id,
4323 p_service_package_id => p_service_package_id,
4324 p_current_stage_seq => p_current_stage_seq);
4325
4326 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
4327 raise FND_API.G_EXC_ERROR;
4328 end if;
4329
4330 end loop;
4331
4332 end;
4333 end if;
4334
4335
4336 -- Initialize API return status to success
4337
4338 p_return_status := FND_API.G_RET_STS_SUCCESS;
4339
4340
4341 EXCEPTION
4342
4343 when FND_API.G_EXC_ERROR then
4344 p_return_status := FND_API.G_RET_STS_ERROR;
4345
4346 when FND_API.G_EXC_UNEXPECTED_ERROR then
4347 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4348
4349 when OTHERS then
4350 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4351
4352 if FND_MSG_PUB.Check_Msg_Level
4353 (p_message_level => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4354 then
4355 FND_MSG_PUB.Add_Exc_Msg
4356 (p_pkg_name => G_PKG_NAME,
4357 p_procedure_name => l_api_name);
4358 end if;
4359
4360 END Update_Annual_FTE;
4361
4362 /* ----------------------------------------------------------------------- */
4363
4364 PROCEDURE Distribute_Following_Elements
4365 ( p_return_status OUT NOCOPY VARCHAR2,
4366 p_redistribute IN VARCHAR2 := FND_API.G_FALSE,
4367 p_pay_element_id IN NUMBER,
4368 p_data_extract_id IN NUMBER,
4369 p_flex_code IN NUMBER,
4370 p_business_group_id IN NUMBER,
4371 p_rounding_factor IN NUMBER,
4372 p_position_line_id IN NUMBER,
4373 p_position_id IN NUMBER,
4374 p_budget_year_id IN NUMBER,
4375 p_start_date IN DATE,
4376 p_end_date IN DATE
4377 ) IS
4378
4379 l_ccid_val FND_FLEX_EXT.SegmentArray;
4380 l_seg_val FND_FLEX_EXT.SegmentArray;
4381 l_ccid NUMBER;
4382
4383 l_start_date DATE;
4384 l_end_date DATE;
4385 l_dist_start_date DATE;
4386 l_dist_end_date DATE;
4387
4388 l_rounding_difference NUMBER;
4389
4390 l_init_index BINARY_INTEGER;
4391 l_calc_index BINARY_INTEGER;
4392 l_saldist_index BINARY_INTEGER;
4393 l_dist_index BINARY_INTEGER;
4394 l_element_index BINARY_INTEGER;
4395 l_index BINARY_INTEGER;
4396
4397 l_elem_found VARCHAR2(1) := FND_API.G_FALSE;
4398 l_dist_found VARCHAR2(1);
4399
4400 l_percent NUMBER;
4401 l_element_set_id NUMBER;
4402
4403 l_flex_delimiter VARCHAR2(1);
4404 l_concat_segments VARCHAR2(2000);
4405
4406 /* Bug No 2278216 Start */
4407 l_return_status VARCHAR2(1);
4408 /* Bug No 2278216 End */
4409
4410 cursor c_Dist is
4411 select a.segment1, a.segment2, a.segment3, a.segment4,
4412 a.segment5, a.segment6, a.segment7, a.segment8,
4413 a.segment9, a.segment10, a.segment11, a.segment12,
4414 a.segment13, a.segment14, a.segment15, a.segment16,
4415 a.segment17, a.segment18, a.segment19, a.segment20,
4416 a.segment21, a.segment22, a.segment23, a.segment24,
4417 a.segment25, a.segment26, a.segment27, a.segment28,
4418 a.segment29, a.segment30,
4419 a.effective_start_date, a.effective_end_date
4420 from PSB_PAY_ELEMENT_DISTRIBUTIONS a,
4421 PSB_ELEMENT_POS_SET_GROUPS b,
4422 PSB_SET_RELATIONS c,
4423 PSB_BUDGET_POSITIONS d
4424 where a.chart_of_accounts_id = p_flex_code
4425 and (((a.effective_start_date <= p_end_date)
4426 and (a.effective_end_date is null))
4427 or ((a.effective_start_date between p_start_date and p_end_date)
4428 or (a.effective_end_date between p_start_date and p_end_date)
4429 or ((a.effective_start_date < p_start_date)
4430 and (a.effective_end_date > p_end_date))))
4431 and a.position_set_group_id = b.position_set_group_id
4432 and b.position_set_group_id = c.position_set_group_id
4433 and b.pay_element_id = p_pay_element_id
4434 and c.account_position_set_id = d.account_position_set_id
4435 and d.data_extract_id = p_data_extract_id
4436 and d.position_id = p_position_id;
4437
4438 BEGIN
4439
4440 for l_calc_index in 1..g_num_pc_costs loop
4441
4442 if ((g_pc_costs(l_calc_index).budget_year_id = p_budget_year_id) and
4443 (g_pc_costs(l_calc_index).pay_element_id = p_pay_element_id)) then
4444 l_element_index := l_calc_index;
4445 l_elem_found := FND_API.G_TRUE;
4446 exit;
4447 end if;
4448
4449 end loop;
4450
4451 if FND_API.to_Boolean(l_elem_found) then
4452 begin
4453
4454 /* Bug No 2278216 Start */
4455 PSB_WS_POS1.Initialize_Period_Dist;
4456 /* Bug No 2278216 End */
4457
4458 for l_calc_index in REVERSE 1..(l_element_index - 1) loop
4459
4460 if g_pc_costs(l_calc_index).pay_element_id = p_pay_element_id then
4461 l_element_set_id := g_pc_costs(l_calc_index).element_set_id;
4462 exit;
4463 end if;
4464
4465 end loop;
4466
4467 l_rounding_difference := 0;
4468
4469 for c_Dist_Rec in c_Dist loop
4470
4471 if (((c_Dist_Rec.effective_start_date <= p_start_date) and
4472 (c_Dist_Rec.effective_end_date is null)) or
4473 ((c_Dist_Rec.effective_start_date between p_start_date and p_end_date) or
4474 (c_Dist_Rec.effective_end_date between p_start_date and p_end_date) or
4475 ((c_Dist_Rec.effective_start_date < p_start_date) and
4476 (c_Dist_Rec.effective_end_date > p_end_date)))) then
4477 Begin
4478 l_start_date := greatest(p_start_date, c_Dist_Rec.effective_start_date);
4479 l_end_date := least(p_end_date, nvl(c_Dist_Rec.effective_end_date, p_end_date));
4480
4481 for l_saldist_index in 1..g_num_salary_dist loop
4482
4483 if (((PSB_WS_POS1.g_salary_dist(l_saldist_index).start_date <= l_end_date) and
4484 (PSB_WS_POS1.g_salary_dist(l_saldist_index).end_date is null)) or
4485 ((PSB_WS_POS1.g_salary_dist(l_saldist_index).start_date between l_start_date and l_end_date) or
4486 (PSB_WS_POS1.g_salary_dist(l_saldist_index).end_date between l_start_date and l_end_date) or
4487 ((PSB_WS_POS1.g_salary_dist(l_saldist_index).start_date < l_start_date) and
4488 (PSB_WS_POS1.g_salary_dist(l_saldist_index).end_date > l_end_date)))) then
4489 begin
4490
4491 l_dist_start_date := greatest(l_start_date, PSB_WS_POS1.g_salary_dist(l_saldist_index).start_date);
4492 l_dist_end_date := least(l_end_date, nvl(PSB_WS_POS1.g_salary_dist(l_saldist_index).end_date, l_end_date));
4493
4494 l_dist_found := FND_API.G_FALSE;
4495
4496 -- commented for bug # 4502946
4497 /*if g_salary_dist(l_saldist_index).percent < 1 then
4498 l_percent := g_salary_dist(l_saldist_index).percent;
4499 else
4500 l_percent := g_salary_dist(l_saldist_index).percent / 100;
4501 end if;*/
4502
4503 -- added for bug # 4502946
4504 l_percent := g_salary_dist(l_saldist_index).percent / 100;
4505
4506 for l_init_index in 1..PSB_WS_ACCT1.g_num_segs loop
4507 l_ccid_val(l_init_index) := null;
4508 l_seg_val(l_init_index) := null;
4509 end loop;
4510
4511 if not FND_FLEX_EXT.Get_Segments
4512 (application_short_name => 'SQLGL',
4513 key_flex_code => 'GL#',
4514 structure_number => p_flex_code,
4515 combination_id => g_salary_dist(l_saldist_index).ccid,
4516 n_segments => PSB_WS_ACCT1.g_num_segs,
4517 segments => l_ccid_val) then
4518
4519 FND_MSG_PUB.Add;
4520 raise FND_API.G_EXC_ERROR;
4521 end if;
4522
4523 for l_index in 1..PSB_WS_ACCT1.g_num_segs loop
4524
4525 if ((PSB_WS_ACCT1.g_seg_name(l_index) = 'SEGMENT1') and
4526 (c_Dist_Rec.segment1 is not null)) then
4527 l_seg_val(l_index) := c_Dist_Rec.segment1;
4528
4529 elsif ((PSB_WS_ACCT1.g_seg_name(l_index) = 'SEGMENT2') and
4530 (c_Dist_Rec.segment2 is not null)) then
4531 l_seg_val(l_index) := c_Dist_Rec.segment2;
4532
4533 elsif ((PSB_WS_ACCT1.g_seg_name(l_index) = 'SEGMENT3') and
4534 (c_Dist_Rec.segment3 is not null)) then
4535 l_seg_val(l_index) := c_Dist_Rec.segment3;
4536
4537 elsif ((PSB_WS_ACCT1.g_seg_name(l_index) = 'SEGMENT4') and
4538 (c_Dist_Rec.segment4 is not null)) then
4539 l_seg_val(l_index) := c_Dist_Rec.segment4;
4540
4541 elsif ((PSB_WS_ACCT1.g_seg_name(l_index) = 'SEGMENT5') and
4542 (c_Dist_Rec.segment5 is not null)) then
4543 l_seg_val(l_index) := c_Dist_Rec.segment5;
4544
4545 elsif ((PSB_WS_ACCT1.g_seg_name(l_index) = 'SEGMENT6') and
4546 (c_Dist_Rec.segment6 is not null)) then
4547 l_seg_val(l_index) := c_Dist_Rec.segment6;
4548
4549 elsif ((PSB_WS_ACCT1.g_seg_name(l_index) = 'SEGMENT7') and
4550 (c_Dist_Rec.segment7 is not null)) then
4551 l_seg_val(l_index) := c_Dist_Rec.segment7;
4552
4553 elsif ((PSB_WS_ACCT1.g_seg_name(l_index) = 'SEGMENT8') and
4554 (c_Dist_Rec.segment8 is not null)) then
4555 l_seg_val(l_index) := c_Dist_Rec.segment8;
4556
4557 elsif ((PSB_WS_ACCT1.g_seg_name(l_index) = 'SEGMENT9') and
4558 (c_Dist_Rec.segment9 is not null)) then
4559 l_seg_val(l_index) := c_Dist_Rec.segment9;
4560
4561 elsif ((PSB_WS_ACCT1.g_seg_name(l_index) = 'SEGMENT10') and
4562 (c_Dist_Rec.segment10 is not null)) then
4563 l_seg_val(l_index) := c_Dist_Rec.segment10;
4564
4565 elsif ((PSB_WS_ACCT1.g_seg_name(l_index) = 'SEGMENT11') and
4566 (c_Dist_Rec.segment11 is not null)) then
4567 l_seg_val(l_index) := c_Dist_Rec.segment11;
4568
4569 elsif ((PSB_WS_ACCT1.g_seg_name(l_index) = 'SEGMENT12') and
4570 (c_Dist_Rec.segment12 is not null)) then
4571 l_seg_val(l_index) := c_Dist_Rec.segment12;
4572
4573 elsif ((PSB_WS_ACCT1.g_seg_name(l_index) = 'SEGMENT13') and
4574 (c_Dist_Rec.segment13 is not null)) then
4575 l_seg_val(l_index) := c_Dist_Rec.segment13;
4576
4577 elsif ((PSB_WS_ACCT1.g_seg_name(l_index) = 'SEGMENT14') and
4578 (c_Dist_Rec.segment14 is not null)) then
4579 l_seg_val(l_index) := c_Dist_Rec.segment14;
4580
4581 elsif ((PSB_WS_ACCT1.g_seg_name(l_index) = 'SEGMENT15') and
4582 (c_Dist_Rec.segment15 is not null)) then
4583 l_seg_val(l_index) := c_Dist_Rec.segment15;
4584
4585 elsif ((PSB_WS_ACCT1.g_seg_name(l_index) = 'SEGMENT16') and
4586 (c_Dist_Rec.segment16 is not null)) then
4587 l_seg_val(l_index) := c_Dist_Rec.segment16;
4588
4589 elsif ((PSB_WS_ACCT1.g_seg_name(l_index) = 'SEGMENT17') and
4590 (c_Dist_Rec.segment17 is not null)) then
4591 l_seg_val(l_index) := c_Dist_Rec.segment17;
4592
4593 elsif ((PSB_WS_ACCT1.g_seg_name(l_index) = 'SEGMENT18') and
4594 (c_Dist_Rec.segment18 is not null)) then
4595 l_seg_val(l_index) := c_Dist_Rec.segment18;
4596
4597 elsif ((PSB_WS_ACCT1.g_seg_name(l_index) = 'SEGMENT19') and
4598 (c_Dist_Rec.segment19 is not null)) then
4599 l_seg_val(l_index) := c_Dist_Rec.segment19;
4600
4601 elsif ((PSB_WS_ACCT1.g_seg_name(l_index) = 'SEGMENT20') and
4602 (c_Dist_Rec.segment20 is not null)) then
4603 l_seg_val(l_index) := c_Dist_Rec.segment20;
4604
4605 elsif ((PSB_WS_ACCT1.g_seg_name(l_index) = 'SEGMENT21') and
4606 (c_Dist_Rec.segment21 is not null)) then
4607 l_seg_val(l_index) := c_Dist_Rec.segment21;
4608
4609 elsif ((PSB_WS_ACCT1.g_seg_name(l_index) = 'SEGMENT22') and
4610 (c_Dist_Rec.segment22 is not null)) then
4611 l_seg_val(l_index) := c_Dist_Rec.segment22;
4612
4613 elsif ((PSB_WS_ACCT1.g_seg_name(l_index) = 'SEGMENT23') and
4614 (c_Dist_Rec.segment23 is not null)) then
4615 l_seg_val(l_index) := c_Dist_Rec.segment23;
4616
4617 elsif ((PSB_WS_ACCT1.g_seg_name(l_index) = 'SEGMENT24') and
4618 (c_Dist_Rec.segment24 is not null)) then
4619 l_seg_val(l_index) := c_Dist_Rec.segment24;
4620
4621 elsif ((PSB_WS_ACCT1.g_seg_name(l_index) = 'SEGMENT25') and
4622 (c_Dist_Rec.segment25 is not null)) then
4623 l_seg_val(l_index) := c_Dist_Rec.segment25;
4624
4625 elsif ((PSB_WS_ACCT1.g_seg_name(l_index) = 'SEGMENT26') and
4626 (c_Dist_Rec.segment26 is not null)) then
4627 l_seg_val(l_index) := c_Dist_Rec.segment26;
4628
4629 elsif ((PSB_WS_ACCT1.g_seg_name(l_index) = 'SEGMENT27') and
4630 (c_Dist_Rec.segment27 is not null)) then
4631 l_seg_val(l_index) := c_Dist_Rec.segment27;
4632
4633 elsif ((PSB_WS_ACCT1.g_seg_name(l_index) = 'SEGMENT28') and
4634 (c_Dist_Rec.segment28 is not null)) then
4635 l_seg_val(l_index) := c_Dist_Rec.segment28;
4636
4637 elsif ((PSB_WS_ACCT1.g_seg_name(l_index) = 'SEGMENT29') and
4638 (c_Dist_Rec.segment29 is not null)) then
4639 l_seg_val(l_index) := c_Dist_Rec.segment29;
4640
4641 elsif ((PSB_WS_ACCT1.g_seg_name(l_index) = 'SEGMENT30') and
4642 (c_Dist_Rec.segment30 is not null)) then
4643 l_seg_val(l_index) := c_Dist_Rec.segment30;
4644
4645 else
4646 l_seg_val(l_index) := l_ccid_val(l_index);
4647 end if;
4648
4649 end loop;
4650
4651 if not FND_FLEX_EXT.Get_Combination_ID
4652 (application_short_name => 'SQLGL',
4653 key_flex_code => 'GL#',
4654 structure_number => p_flex_code,
4655 validation_date => sysdate,
4656 n_segments => PSB_WS_ACCT1.g_num_segs,
4657 segments => l_seg_val,
4658 combination_id => l_ccid) then
4659 begin
4660
4661 l_flex_delimiter := FND_FLEX_EXT.Get_Delimiter
4662 (application_short_name => 'SQLGL',
4663 key_flex_code => 'GL#',
4664 structure_number => p_flex_code);
4665
4666 l_concat_segments := FND_FLEX_EXT.Concatenate_Segments
4667 (n_segments => PSB_WS_ACCT1.g_num_segs,
4668 segments => l_seg_val,
4669 delimiter => l_flex_delimiter);
4670
4671 FND_MSG_PUB.Add;
4672 message_token('ACCOUNT', l_concat_segments);
4673 add_message('PSB', 'PSB_GL_CCID_FAILURE');
4674 raise FND_API.G_EXC_ERROR;
4675
4676 end;
4677 end if;
4678
4679 for l_index in REVERSE 1..g_num_pd_costs loop
4680
4681 if ((g_pd_costs(l_index).ccid = l_ccid) and
4682 (g_pd_costs(l_index).budget_year_id = p_budget_year_id) and
4683 (g_pd_costs(l_index).element_type = 'F')) then
4684 l_element_set_id := g_pd_costs(l_index).element_set_id;
4685 l_dist_index := l_index;
4686 l_dist_found := FND_API.G_TRUE;
4687 exit;
4688 end if;
4689
4690 end loop;
4691
4692 /* Bug No 2278216 Start */
4693 -- Created a separate procedure for calculating period amounts
4694
4695 if not FND_API.to_Boolean(l_dist_found) then
4696 begin
4697 g_num_pd_costs := g_num_pd_costs + 1;
4698
4699 g_pd_costs(g_num_pd_costs).budget_year_id := p_budget_year_id;
4700 g_pd_costs(g_num_pd_costs).element_type := 'F';
4701 g_pd_costs(g_num_pd_costs).ccid := l_ccid;
4702
4703 if not FND_API.to_Boolean(p_redistribute) then
4704 begin
4705 if l_element_set_id is null then
4706 l_element_set_id := p_pay_element_id;
4707 end if;
4708
4709 if g_pc_costs(l_element_index).element_set_id is null then
4710 g_pc_costs(l_element_index).element_set_id := l_element_set_id;
4711 end if;
4712
4713 g_pd_costs(g_num_pd_costs).element_set_id := l_element_set_id;
4714 end;
4715 else
4716 l_element_set_id := g_pc_costs(l_element_index).element_set_id;
4717
4718 g_pd_costs(g_num_pd_costs).element_set_id := l_element_set_id;
4719 end if;
4720 end;
4721 else
4722 begin
4723 if not FND_API.to_Boolean(p_redistribute) then
4724 begin
4725 if g_pc_costs(l_element_index).element_set_id is null then
4726 g_pc_costs(l_element_index).element_set_id := l_element_set_id;
4727 end if;
4728 end;
4729 end if;
4730 end;
4731 end if;
4732
4733 g_num_periods := g_num_periods + 1;
4734
4735 g_periods(g_num_periods).ccid := l_ccid;
4736 g_periods(g_num_periods).budget_year_id := g_pd_costs(g_num_pd_costs).budget_year_id;
4737 g_periods(g_num_periods).element_type := 'F';
4738 g_periods(g_num_periods).element_set_id := l_element_set_id;
4739 g_periods(g_num_periods).percent := l_percent;
4740 g_periods(g_num_periods).period_start_date := l_dist_start_date;
4741 g_periods(g_num_periods).period_end_date := l_dist_end_date;
4742
4743 end;
4744 end if;
4745
4746 end loop;
4747
4748 end;
4749 end if;
4750
4751 end loop;
4752
4753 l_rounding_difference := 0;
4754
4755 for l_dist_index in 1..g_num_pd_costs loop
4756
4757 if ((g_pd_costs(l_dist_index).budget_year_id = p_budget_year_id)
4758 and (g_pd_costs(l_dist_index).element_type = 'F')
4759 and (g_pd_costs(l_dist_index).element_set_id = l_element_set_id)) then
4760
4761 if not FND_API.to_Boolean(p_redistribute) then
4762 begin
4763
4764 for l_period_index in 1..g_num_periods loop
4765 if ((g_periods(l_period_index).ccid = g_pd_costs(l_dist_index).ccid)
4766 and (g_periods(l_period_index).element_set_id = g_pd_costs(l_dist_index).element_set_id)
4767 and (g_periods(l_period_index).element_type = 'F')
4768 and (g_periods(l_period_index).budget_year_id = g_pd_costs(l_dist_index).budget_year_id)) then
4769
4770 Distribute_Periods
4771 (p_return_status => l_return_status,
4772 p_ccid => g_pd_costs(l_dist_index).ccid,
4773 p_element_type => 'F',
4774 p_element_set_id => g_pd_costs(l_dist_index).element_set_id,
4775 p_budget_year_id => p_budget_year_id,
4776 p_dist_start_date => g_periods(l_period_index).period_start_date,
4777 p_dist_end_date => g_periods(l_period_index).period_end_date,
4778 p_start_date => p_start_date,
4779 p_end_date => p_end_date,
4780 p_element_index => l_element_index,
4781 p_dist_index => l_dist_index,
4782 p_percent => g_periods(l_period_index).percent);
4783
4784 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
4785 raise FND_API.G_EXC_ERROR;
4786 end if;
4787
4788 end if;
4789 end loop;
4790
4791 end;
4792 end if;
4793
4794 end if;
4795 end loop;
4796
4797 if p_rounding_factor is not null then
4798 l_rounding_difference := l_rounding_difference +
4799 (round(g_pc_costs(l_element_index).element_cost / p_rounding_factor)
4800 * p_rounding_factor - g_pc_costs(l_element_index).element_cost);
4801 end if;
4802
4803 g_pc_costs(l_element_index).element_cost := g_pc_costs(l_element_index).element_cost +
4804 l_rounding_difference;
4805 end;
4806 end if;
4807
4808 -- Initialize API return status to success
4809
4810 p_return_status := FND_API.G_RET_STS_SUCCESS;
4811
4812
4813 EXCEPTION
4814
4815 when FND_API.G_EXC_ERROR then
4816 p_return_status := FND_API.G_RET_STS_ERROR;
4817
4818 when FND_API.G_EXC_UNEXPECTED_ERROR then
4819 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4820
4821 when OTHERS then
4822 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4823
4824 END Distribute_Following_Elements;
4825
4826 /* ------------------------------------------------------------------------- */
4827 -- Compute the Account distribution for the Following Elements
4828
4829 PROCEDURE Distribute_Periods
4830 ( p_return_status OUT NOCOPY VARCHAR2,
4831 p_ccid IN NUMBER,
4832 p_element_type IN VARCHAR2,
4833 p_element_set_id IN NUMBER,
4834 p_budget_year_id IN NUMBER,
4835 p_dist_start_date IN DATE,
4836 p_dist_end_date IN DATE,
4837 p_start_date IN DATE,
4838 p_end_date IN DATE,
4839 p_element_index IN NUMBER,
4840 p_dist_index IN NUMBER,
4841 p_percent IN NUMBER
4842 ) IS
4843
4844 l_num_budget_periods NUMBER := 0;
4845 l_dist_periods NUMBER;
4846 l_no_init_dist_periods NUMBER;
4847 l_dist_flag VARCHAR2(1);
4848 l_start_num NUMBER;
4849 l_end_num NUMBER;
4850
4851 l_year_index BINARY_INTEGER;
4852
4853 /* Bug 3610713 Start */
4854 l_year_start_date DATE;
4855 l_ctr NUMBER;
4856 /* Bug 3610713 End */
4857
4858 BEGIN
4859 /* Bug 3610713 Start */
4860 FOR l_year_index in 1..PSB_WS_ACCT1.g_num_budget_years
4861 LOOP
4862
4863 IF PSB_WS_ACCT1.g_budget_years(l_year_index).budget_year_id = p_budget_year_id THEN
4864 l_year_start_date := PSB_WS_ACCT1.g_budget_years(l_year_index).start_date;
4865 END IF;
4866
4867 END LOOP;
4868 /* Bug 3610713 End */
4869
4870 l_num_budget_periods := 0;
4871 for l_year_index in 1..PSB_WS_ACCT1.g_num_budget_periods loop
4872 if (PSB_WS_ACCT1.g_budget_periods(l_year_index).budget_year_id = p_budget_year_id) then
4873 l_num_budget_periods := l_num_budget_periods + 1;
4874 end if;
4875 end loop;
4876
4877 l_dist_periods := (l_num_budget_periods * months_between(p_dist_end_date, p_dist_start_date - 1)) / 12;
4878 l_no_init_dist_periods := (l_num_budget_periods * months_between(p_dist_start_date, p_start_date)) / 12;
4879
4880 l_dist_flag := NULL;
4881
4882 IF (l_no_init_dist_periods = 0) THEN
4883
4884 /* Bug 3610713 Start */
4885 IF l_year_start_date < p_dist_start_date THEN
4886
4887 l_ctr := 0;
4888 FOR l_period_index IN 1..PSB_WS_ACCT1.g_num_budget_periods
4889 LOOP
4890
4891 IF PSB_WS_ACCT1.g_budget_periods(l_period_index).budget_year_id =
4892 p_budget_year_id THEN
4893 l_ctr := l_ctr +1 ;
4894 END IF;
4895
4896 IF PSB_WS_ACCT1.g_budget_periods(l_period_index).budget_year_id =
4897 p_budget_year_id AND
4898 p_dist_start_date BETWEEN
4899 PSB_WS_ACCT1.g_budget_periods(l_period_index).start_date AND
4900 PSB_WS_ACCT1.g_budget_periods(l_period_index).end_date THEN
4901
4902 l_start_num := l_ctr;
4903 l_end_num := (l_ctr-1) + ceil(l_dist_periods);
4904 END IF;
4905 END LOOP;
4906 ELSE
4907 l_start_num := 1;
4908 l_end_num := ceil(l_dist_periods);
4909 END IF;
4910 /* Bug 3610713 End */
4911 l_dist_flag := 'Y';
4912
4913 ELSE
4914
4915 /* Bug 3610713 Start */
4916 IF l_year_start_date < p_dist_start_date THEN
4917
4918 l_ctr := 0;
4919 FOR l_period_index IN 1..PSB_WS_ACCT1.g_num_budget_periods
4920 LOOP
4921
4922 IF PSB_WS_ACCT1.g_budget_periods(l_period_index).budget_year_id =
4923 p_budget_year_id THEN
4924 l_ctr := l_ctr +1 ;
4925 END IF;
4926
4927 IF PSB_WS_ACCT1.g_budget_periods(l_period_index).budget_year_id =
4928 p_budget_year_id AND
4929 p_dist_start_date BETWEEN
4930 PSB_WS_ACCT1.g_budget_periods(l_period_index).start_date AND
4931 PSB_WS_ACCT1.g_budget_periods(l_period_index).end_date THEN
4932
4933 l_start_num := l_ctr;
4934 l_end_num := (l_ctr-1) + ceil(l_dist_periods);
4935 END IF;
4936 END LOOP;
4937 ELSE
4938 l_start_num := floor(l_no_init_dist_periods+1);
4939 l_end_num := ceil(least(l_no_init_dist_periods + l_dist_periods, l_num_budget_periods));
4940 END IF;
4941 /* Bug 3610713 End */
4942
4943 END IF;
4944
4945 if ((g_pd_costs(p_dist_index).ccid = p_ccid)
4946 and (g_pd_costs(p_dist_index).element_type = p_element_type)
4947 and (g_pd_costs(p_dist_index).element_set_id = p_element_set_id)
4948 and (g_pd_costs(p_dist_index).budget_year_id = p_budget_year_id)) then
4949
4950 if 1 between l_start_num and l_end_num then
4951 g_pd_costs(p_dist_index).period1_amount := nvl(g_pd_costs(p_dist_index).period1_amount,0)
4952 + nvl(g_pc_costs(p_element_index).period1_amount,0) * p_percent;
4953 g_pd_costs(p_dist_index).ytd_amount := nvl(g_pd_costs(p_dist_index).ytd_amount, 0)
4954 + nvl(g_pc_costs(p_element_index).period1_amount,0) * p_percent;
4955 end if;
4956
4957 if 2 between l_start_num and l_end_num then
4958 g_pd_costs(p_dist_index).period2_amount := nvl(g_pd_costs(p_dist_index).period2_amount,0)
4959 + nvl(g_pc_costs(p_element_index).period2_amount,0) * p_percent;
4960 g_pd_costs(p_dist_index).ytd_amount := nvl(g_pd_costs(p_dist_index).ytd_amount, 0)
4961 + nvl(g_pc_costs(p_element_index).period2_amount,0) * p_percent;
4962 end if;
4963
4964 if 3 between l_start_num and l_end_num then
4965 g_pd_costs(p_dist_index).period3_amount := nvl(g_pd_costs(p_dist_index).period3_amount,0)
4966 + nvl(g_pc_costs(p_element_index).period3_amount,0) * p_percent;
4967 g_pd_costs(p_dist_index).ytd_amount := nvl(g_pd_costs(p_dist_index).ytd_amount, 0)
4968 + nvl(g_pc_costs(p_element_index).period3_amount,0) * p_percent;
4969 end if;
4970
4971 if 4 between l_start_num and l_end_num then
4972 g_pd_costs(p_dist_index).period4_amount := nvl(g_pd_costs(p_dist_index).period4_amount,0)
4973 + nvl(g_pc_costs(p_element_index).period4_amount,0) * p_percent;
4974 g_pd_costs(p_dist_index).ytd_amount := nvl(g_pd_costs(p_dist_index).ytd_amount, 0)
4975 + nvl(g_pc_costs(p_element_index).period4_amount,0) * p_percent;
4976 end if;
4977
4978 if 5 between l_start_num and l_end_num then
4979 g_pd_costs(p_dist_index).period5_amount := nvl(g_pd_costs(p_dist_index).period5_amount,0)
4980 + nvl(g_pc_costs(p_element_index).period5_amount,0) * p_percent;
4981 g_pd_costs(p_dist_index).ytd_amount := nvl(g_pd_costs(p_dist_index).ytd_amount, 0)
4982 + nvl(g_pc_costs(p_element_index).period5_amount,0) * p_percent;
4983 end if;
4984
4985 if 6 between l_start_num and l_end_num then
4986 g_pd_costs(p_dist_index).period6_amount := nvl(g_pd_costs(p_dist_index).period6_amount,0)
4987 + nvl(g_pc_costs(p_element_index).period6_amount,0) * p_percent;
4988 g_pd_costs(p_dist_index).ytd_amount := nvl(g_pd_costs(p_dist_index).ytd_amount, 0)
4989 + nvl(g_pc_costs(p_element_index).period6_amount,0) * p_percent;
4990 end if;
4991
4992 if 7 between l_start_num and l_end_num then
4993 g_pd_costs(p_dist_index).period7_amount := nvl(g_pd_costs(p_dist_index).period7_amount,0)
4994 + nvl(g_pc_costs(p_element_index).period7_amount,0) * p_percent;
4995 g_pd_costs(p_dist_index).ytd_amount := nvl(g_pd_costs(p_dist_index).ytd_amount, 0)
4996 + nvl(g_pc_costs(p_element_index).period7_amount,0) * p_percent;
4997 end if;
4998
4999 if 8 between l_start_num and l_end_num then
5000 g_pd_costs(p_dist_index).period8_amount := nvl(g_pd_costs(p_dist_index).period8_amount,0)
5001 + nvl(g_pc_costs(p_element_index).period8_amount,0) * p_percent;
5002 g_pd_costs(p_dist_index).ytd_amount := nvl(g_pd_costs(p_dist_index).ytd_amount, 0)
5003 + nvl(g_pc_costs(p_element_index).period8_amount,0) * p_percent;
5004 end if;
5005
5006 if 9 between l_start_num and l_end_num then
5007 g_pd_costs(p_dist_index).period9_amount := nvl(g_pd_costs(p_dist_index).period9_amount,0)
5008 + nvl(g_pc_costs(p_element_index).period9_amount,0) * p_percent;
5009 g_pd_costs(p_dist_index).ytd_amount := nvl(g_pd_costs(p_dist_index).ytd_amount, 0)
5010 + nvl(g_pc_costs(p_element_index).period9_amount,0) * p_percent;
5011 end if;
5012
5013 if 10 between l_start_num and l_end_num then
5014 g_pd_costs(p_dist_index).period10_amount := nvl(g_pd_costs(p_dist_index).period10_amount,0)
5015 + nvl(g_pc_costs(p_element_index).period10_amount,0) * p_percent;
5016 g_pd_costs(p_dist_index).ytd_amount := nvl(g_pd_costs(p_dist_index).ytd_amount, 0)
5017 + nvl(g_pc_costs(p_element_index).period10_amount,0) * p_percent;
5018 end if;
5019
5020 if 11 between l_start_num and l_end_num then
5021 g_pd_costs(p_dist_index).period11_amount := nvl(g_pd_costs(p_dist_index).period11_amount,0)
5022 + nvl(g_pc_costs(p_element_index).period11_amount,0) * p_percent;
5023 g_pd_costs(p_dist_index).ytd_amount := nvl(g_pd_costs(p_dist_index).ytd_amount, 0)
5024 + nvl(g_pc_costs(p_element_index).period11_amount,0) * p_percent;
5025 end if;
5026
5027 if 12 between l_start_num and l_end_num then
5028 g_pd_costs(p_dist_index).period12_amount := nvl(g_pd_costs(p_dist_index).period12_amount,0)
5029 + nvl(g_pc_costs(p_element_index).period12_amount,0) * p_percent;
5030 g_pd_costs(p_dist_index).ytd_amount := nvl(g_pd_costs(p_dist_index).ytd_amount, 0)
5031 + nvl(g_pc_costs(p_element_index).period12_amount,0) * p_percent;
5032 end if;
5033
5034 if 13 between l_start_num and l_end_num then
5035 g_pd_costs(p_dist_index).period13_amount := nvl(g_pd_costs(p_dist_index).period13_amount,0)
5036 + nvl(g_pc_costs(p_element_index).period13_amount,0) * p_percent;
5037 g_pd_costs(p_dist_index).ytd_amount := nvl(g_pd_costs(p_dist_index).ytd_amount, 0)
5038 + nvl(g_pc_costs(p_element_index).period1_amount,0) * p_percent;
5039 end if;
5040
5041 if 14 between l_start_num and l_end_num then
5042 g_pd_costs(p_dist_index).period14_amount := nvl(g_pd_costs(p_dist_index).period14_amount,0)
5043 + nvl(g_pc_costs(p_element_index).period14_amount,0) * p_percent;
5044 g_pd_costs(p_dist_index).ytd_amount := nvl(g_pd_costs(p_dist_index).ytd_amount, 0)
5045 + nvl(g_pc_costs(p_element_index).period14_amount,0) * p_percent;
5046 end if;
5047
5048 if 15 between l_start_num and l_end_num then
5049 g_pd_costs(p_dist_index).period15_amount := nvl(g_pd_costs(p_dist_index).period15_amount,0)
5050 + nvl(g_pc_costs(p_element_index).period15_amount,0) * p_percent;
5051 g_pd_costs(p_dist_index).ytd_amount := nvl(g_pd_costs(p_dist_index).ytd_amount, 0)
5052 + nvl(g_pc_costs(p_element_index).period15_amount,0) * p_percent;
5053 end if;
5054
5055 if 16 between l_start_num and l_end_num then
5056 g_pd_costs(p_dist_index).period16_amount := nvl(g_pd_costs(p_dist_index).period16_amount,0)
5057 + nvl(g_pc_costs(p_element_index).period16_amount,0) * p_percent;
5058 g_pd_costs(p_dist_index).ytd_amount := nvl(g_pd_costs(p_dist_index).ytd_amount, 0)
5059 + nvl(g_pc_costs(p_element_index).period16_amount,0) * p_percent;
5060 end if;
5061
5062 if 17 between l_start_num and l_end_num then
5063 g_pd_costs(p_dist_index).period17_amount := nvl(g_pd_costs(p_dist_index).period17_amount,0)
5064 + nvl(g_pc_costs(p_element_index).period17_amount,0) * p_percent;
5065 g_pd_costs(p_dist_index).ytd_amount := nvl(g_pd_costs(p_dist_index).ytd_amount, 0)
5066 + nvl(g_pc_costs(p_element_index).period17_amount,0) * p_percent;
5067 end if;
5068
5069 if 18 between l_start_num and l_end_num then
5070 g_pd_costs(p_dist_index).period18_amount := nvl(g_pd_costs(p_dist_index).period18_amount,0)
5071 + nvl(g_pc_costs(p_element_index).period18_amount,0) * p_percent;
5072 g_pd_costs(p_dist_index).ytd_amount := nvl(g_pd_costs(p_dist_index).ytd_amount, 0)
5073 + nvl(g_pc_costs(p_element_index).period18_amount,0) * p_percent;
5074 end if;
5075
5076 if 19 between l_start_num and l_end_num then
5077 g_pd_costs(p_dist_index).period19_amount := nvl(g_pd_costs(p_dist_index).period19_amount,0)
5078 + nvl(g_pc_costs(p_element_index).period19_amount,0) * p_percent;
5079 g_pd_costs(p_dist_index).ytd_amount := nvl(g_pd_costs(p_dist_index).ytd_amount, 0)
5080 + nvl(g_pc_costs(p_element_index).period19_amount,0) * p_percent;
5081 end if;
5082
5083 if 20 between l_start_num and l_end_num then
5084 g_pd_costs(p_dist_index).period20_amount := nvl(g_pd_costs(p_dist_index).period20_amount,0)
5085 + nvl(g_pc_costs(p_element_index).period20_amount,0) * p_percent;
5086 g_pd_costs(p_dist_index).ytd_amount := nvl(g_pd_costs(p_dist_index).ytd_amount, 0)
5087 + nvl(g_pc_costs(p_element_index).period20_amount,0) * p_percent;
5088 end if;
5089
5090 if 21 between l_start_num and l_end_num then
5091 g_pd_costs(p_dist_index).period21_amount := nvl(g_pd_costs(p_dist_index).period21_amount,0)
5092 + nvl(g_pc_costs(p_element_index).period21_amount,0) * p_percent;
5093 g_pd_costs(p_dist_index).ytd_amount := nvl(g_pd_costs(p_dist_index).ytd_amount, 0)
5094 + nvl(g_pc_costs(p_element_index).period21_amount,0) * p_percent;
5095 end if;
5096
5097 if 22 between l_start_num and l_end_num then
5098 g_pd_costs(p_dist_index).period22_amount := nvl(g_pd_costs(p_dist_index).period22_amount,0)
5099 + nvl(g_pc_costs(p_element_index).period22_amount,0) * p_percent;
5100 g_pd_costs(p_dist_index).ytd_amount := nvl(g_pd_costs(p_dist_index).ytd_amount, 0)
5101 + nvl(g_pc_costs(p_element_index).period22_amount,0) * p_percent;
5102 end if;
5103
5104 if 23 between l_start_num and l_end_num then
5105 g_pd_costs(p_dist_index).period23_amount := nvl(g_pd_costs(p_dist_index).period23_amount,0)
5106 + nvl(g_pc_costs(p_element_index).period23_amount,0) * p_percent;
5107 g_pd_costs(p_dist_index).ytd_amount := nvl(g_pd_costs(p_dist_index).ytd_amount, 0)
5108 + nvl(g_pc_costs(p_element_index).period23_amount,0) * p_percent;
5109 end if;
5110
5111 if 24 between l_start_num and l_end_num then
5112 g_pd_costs(p_dist_index).period24_amount := nvl(g_pd_costs(p_dist_index).period24_amount,0)
5113 + nvl(g_pc_costs(p_element_index).period24_amount,0) * p_percent;
5114 g_pd_costs(p_dist_index).ytd_amount := nvl(g_pd_costs(p_dist_index).ytd_amount, 0)
5115 + nvl(g_pc_costs(p_element_index).period24_amount,0) * p_percent;
5116 end if;
5117
5118 if 25 between l_start_num and l_end_num then
5119 g_pd_costs(p_dist_index).period25_amount := nvl(g_pd_costs(p_dist_index).period25_amount,0)
5120 + nvl(g_pc_costs(p_element_index).period25_amount,0) * p_percent;
5121 g_pd_costs(p_dist_index).ytd_amount := nvl(g_pd_costs(p_dist_index).ytd_amount, 0)
5122 + nvl(g_pc_costs(p_element_index).period25_amount,0) * p_percent;
5123 end if;
5124
5125 if 26 between l_start_num and l_end_num then
5126 g_pd_costs(p_dist_index).period26_amount := nvl(g_pd_costs(p_dist_index).period26_amount,0)
5127 + nvl(g_pc_costs(p_element_index).period26_amount,0) * p_percent;
5128 g_pd_costs(p_dist_index).ytd_amount := nvl(g_pd_costs(p_dist_index).ytd_amount, 0)
5129 + nvl(g_pc_costs(p_element_index).period26_amount,0) * p_percent;
5130 end if;
5131
5132 if 27 between l_start_num and l_end_num then
5133 g_pd_costs(p_dist_index).period27_amount := nvl(g_pd_costs(p_dist_index).period27_amount,0)
5134 + nvl(g_pc_costs(p_element_index).period27_amount,0) * p_percent;
5135 g_pd_costs(p_dist_index).ytd_amount := nvl(g_pd_costs(p_dist_index).ytd_amount, 0)
5136 + nvl(g_pc_costs(p_element_index).period27_amount,0) * p_percent;
5137 end if;
5138
5139 if 28 between l_start_num and l_end_num then
5140 g_pd_costs(p_dist_index).period28_amount := nvl(g_pd_costs(p_dist_index).period28_amount,0)
5141 + nvl(g_pc_costs(p_element_index).period28_amount,0) * p_percent;
5142 g_pd_costs(p_dist_index).ytd_amount := nvl(g_pd_costs(p_dist_index).ytd_amount, 0)
5143 + nvl(g_pc_costs(p_element_index).period28_amount,0) * p_percent;
5144 end if;
5145
5146 if 29 between l_start_num and l_end_num then
5147 g_pd_costs(p_dist_index).period29_amount := nvl(g_pd_costs(p_dist_index).period29_amount,0)
5148 + nvl(g_pc_costs(p_element_index).period29_amount,0) * p_percent;
5149 g_pd_costs(p_dist_index).ytd_amount := nvl(g_pd_costs(p_dist_index).ytd_amount, 0)
5150 + nvl(g_pc_costs(p_element_index).period29_amount,0) * p_percent;
5151 end if;
5152
5153 if 30 between l_start_num and l_end_num then
5154 g_pd_costs(p_dist_index).period30_amount := nvl(g_pd_costs(p_dist_index).period30_amount,0)
5155 + nvl(g_pc_costs(p_element_index).period30_amount,0) * p_percent;
5156 g_pd_costs(p_dist_index).ytd_amount := nvl(g_pd_costs(p_dist_index).ytd_amount, 0)
5157 + nvl(g_pc_costs(p_element_index).period30_amount,0) * p_percent;
5158 end if;
5159
5160 if 31 between l_start_num and l_end_num then
5161 g_pd_costs(p_dist_index).period31_amount := nvl(g_pd_costs(p_dist_index).period31_amount,0)
5162 + nvl(g_pc_costs(p_element_index).period31_amount,0) * p_percent;
5163 g_pd_costs(p_dist_index).ytd_amount := nvl(g_pd_costs(p_dist_index).ytd_amount, 0)
5164 + nvl(g_pc_costs(p_element_index).period31_amount,0) * p_percent;
5165 end if;
5166
5167 if 32 between l_start_num and l_end_num then
5168 g_pd_costs(p_dist_index).period32_amount := nvl(g_pd_costs(p_dist_index).period32_amount,0)
5169 + nvl(g_pc_costs(p_element_index).period32_amount,0) * p_percent;
5170 g_pd_costs(p_dist_index).ytd_amount := nvl(g_pd_costs(p_dist_index).ytd_amount, 0)
5171 + nvl(g_pc_costs(p_element_index).period32_amount,0) * p_percent;
5172 end if;
5173
5174 if 33 between l_start_num and l_end_num then
5175 g_pd_costs(p_dist_index).period33_amount := nvl(g_pd_costs(p_dist_index).period33_amount,0)
5176 + nvl(g_pc_costs(p_element_index).period33_amount,0) * p_percent;
5177 g_pd_costs(p_dist_index).ytd_amount := nvl(g_pd_costs(p_dist_index).ytd_amount, 0)
5178 + nvl(g_pc_costs(p_element_index).period33_amount,0) * p_percent;
5179 end if;
5180
5181 if 34 between l_start_num and l_end_num then
5182 g_pd_costs(p_dist_index).period34_amount := nvl(g_pd_costs(p_dist_index).period34_amount,0)
5183 + nvl(g_pc_costs(p_element_index).period34_amount,0) * p_percent;
5184 g_pd_costs(p_dist_index).ytd_amount := nvl(g_pd_costs(p_dist_index).ytd_amount, 0)
5185 + nvl(g_pc_costs(p_element_index).period34_amount,0) * p_percent;
5186 end if;
5187
5188 if 35 between l_start_num and l_end_num then
5189 g_pd_costs(p_dist_index).period35_amount := nvl(g_pd_costs(p_dist_index).period35_amount,0)
5190 + nvl(g_pc_costs(p_element_index).period35_amount,0) * p_percent;
5191 g_pd_costs(p_dist_index).ytd_amount := nvl(g_pd_costs(p_dist_index).ytd_amount, 0)
5192 + nvl(g_pc_costs(p_element_index).period35_amount,0) * p_percent;
5193 end if;
5194
5195 if 36 between l_start_num and l_end_num then
5196 g_pd_costs(p_dist_index).period36_amount := nvl(g_pd_costs(p_dist_index).period36_amount,0)
5197 + nvl(g_pc_costs(p_element_index).period36_amount,0) * p_percent;
5198 g_pd_costs(p_dist_index).ytd_amount := nvl(g_pd_costs(p_dist_index).ytd_amount, 0)
5199 + nvl(g_pc_costs(p_element_index).period36_amount,0) * p_percent;
5200 end if;
5201
5202 if 37 between l_start_num and l_end_num then
5203 g_pd_costs(p_dist_index).period37_amount := nvl(g_pd_costs(p_dist_index).period37_amount,0)
5204 + nvl(g_pc_costs(p_element_index).period37_amount,0) * p_percent;
5205 g_pd_costs(p_dist_index).ytd_amount := nvl(g_pd_costs(p_dist_index).ytd_amount, 0)
5206 + nvl(g_pc_costs(p_element_index).period37_amount,0) * p_percent;
5207 end if;
5208
5209 if 38 between l_start_num and l_end_num then
5210 g_pd_costs(p_dist_index).period38_amount := nvl(g_pd_costs(p_dist_index).period38_amount,0)
5211 + nvl(g_pc_costs(p_element_index).period38_amount,0) * p_percent;
5212 g_pd_costs(p_dist_index).ytd_amount := nvl(g_pd_costs(p_dist_index).ytd_amount, 0)
5213 + nvl(g_pc_costs(p_element_index).period38_amount,0) * p_percent;
5214 end if;
5215
5216 if 39 between l_start_num and l_end_num then
5217 g_pd_costs(p_dist_index).period39_amount := nvl(g_pd_costs(p_dist_index).period39_amount,0)
5218 + nvl(g_pc_costs(p_element_index).period39_amount,0) * p_percent;
5219 g_pd_costs(p_dist_index).ytd_amount := nvl(g_pd_costs(p_dist_index).ytd_amount, 0)
5220 + nvl(g_pc_costs(p_element_index).period39_amount,0) * p_percent;
5221 end if;
5222
5223 if 40 between l_start_num and l_end_num then
5224 g_pd_costs(p_dist_index).period40_amount := nvl(g_pd_costs(p_dist_index).period40_amount,0)
5225 + nvl(g_pc_costs(p_element_index).period40_amount,0) * p_percent;
5226 g_pd_costs(p_dist_index).ytd_amount := nvl(g_pd_costs(p_dist_index).ytd_amount, 0)
5227 + nvl(g_pc_costs(p_element_index).period40_amount,0) * p_percent;
5228 end if;
5229
5230 if 41 between l_start_num and l_end_num then
5231 g_pd_costs(p_dist_index).period4_amount := nvl(g_pd_costs(p_dist_index).period41_amount,0)
5232 + nvl(g_pc_costs(p_element_index).period41_amount,0) * p_percent;
5233 g_pd_costs(p_dist_index).ytd_amount := nvl(g_pd_costs(p_dist_index).ytd_amount, 0)
5234 + nvl(g_pc_costs(p_element_index).period41_amount,0) * p_percent;
5235 end if;
5236
5237 if 42 between l_start_num and l_end_num then
5238 g_pd_costs(p_dist_index).period42_amount := nvl(g_pd_costs(p_dist_index).period42_amount,0)
5239 + nvl(g_pc_costs(p_element_index).period42_amount,0) * p_percent;
5240 g_pd_costs(p_dist_index).ytd_amount := nvl(g_pd_costs(p_dist_index).ytd_amount, 0)
5241 + nvl(g_pc_costs(p_element_index).period42_amount,0) * p_percent;
5242 end if;
5243
5244 if 43 between l_start_num and l_end_num then
5245 g_pd_costs(p_dist_index).period43_amount := nvl(g_pd_costs(p_dist_index).period43_amount,0)
5246 + nvl(g_pc_costs(p_element_index).period43_amount,0) * p_percent;
5247 g_pd_costs(p_dist_index).ytd_amount := nvl(g_pd_costs(p_dist_index).ytd_amount, 0)
5248 + nvl(g_pc_costs(p_element_index).period43_amount,0) * p_percent;
5249 end if;
5250
5251 if 44 between l_start_num and l_end_num then
5252 g_pd_costs(p_dist_index).period44_amount := nvl(g_pd_costs(p_dist_index).period44_amount,0)
5253 + nvl(g_pc_costs(p_element_index).period44_amount,0) * p_percent;
5254 g_pd_costs(p_dist_index).ytd_amount := nvl(g_pd_costs(p_dist_index).ytd_amount, 0)
5255 + nvl(g_pc_costs(p_element_index).period44_amount,0) * p_percent;
5256 end if;
5257
5258 if 45 between l_start_num and l_end_num then
5259 g_pd_costs(p_dist_index).period45_amount := nvl(g_pd_costs(p_dist_index).period45_amount,0)
5260 + nvl(g_pc_costs(p_element_index).period45_amount,0) * p_percent;
5261 g_pd_costs(p_dist_index).ytd_amount := nvl(g_pd_costs(p_dist_index).ytd_amount, 0)
5262 + nvl(g_pc_costs(p_element_index).period45_amount,0) * p_percent;
5263 end if;
5264
5265 if 46 between l_start_num and l_end_num then
5266 g_pd_costs(p_dist_index).period46_amount := nvl(g_pd_costs(p_dist_index).period46_amount,0)
5267 + nvl(g_pc_costs(p_element_index).period46_amount,0) * p_percent;
5268 g_pd_costs(p_dist_index).ytd_amount := nvl(g_pd_costs(p_dist_index).ytd_amount, 0)
5269 + nvl(g_pc_costs(p_element_index).period46_amount,0) * p_percent;
5270 end if;
5271
5272 if 47 between l_start_num and l_end_num then
5273 g_pd_costs(p_dist_index).period47_amount := nvl(g_pd_costs(p_dist_index).period47_amount,0)
5274 + nvl(g_pc_costs(p_element_index).period47_amount,0) * p_percent;
5275 g_pd_costs(p_dist_index).ytd_amount := nvl(g_pd_costs(p_dist_index).ytd_amount, 0)
5276 + nvl(g_pc_costs(p_element_index).period47_amount,0) * p_percent;
5277 end if;
5278
5279 if 48 between l_start_num and l_end_num then
5280 g_pd_costs(p_dist_index).period48_amount := nvl(g_pd_costs(p_dist_index).period48_amount,0)
5281 + nvl(g_pc_costs(p_element_index).period48_amount,0) * p_percent;
5282 g_pd_costs(p_dist_index).ytd_amount := nvl(g_pd_costs(p_dist_index).ytd_amount, 0)
5283 + nvl(g_pc_costs(p_element_index).period48_amount,0) * p_percent;
5284 end if;
5285
5286 if 49 between l_start_num and l_end_num then
5287 g_pd_costs(p_dist_index).period49_amount := nvl(g_pd_costs(p_dist_index).period49_amount,0)
5288 + nvl(g_pc_costs(p_element_index).period49_amount,0) * p_percent;
5289 g_pd_costs(p_dist_index).ytd_amount := nvl(g_pd_costs(p_dist_index).ytd_amount, 0)
5290 + nvl(g_pc_costs(p_element_index).period49_amount,0) * p_percent;
5291 end if;
5292
5293 if 50 between l_start_num and l_end_num then
5294 g_pd_costs(p_dist_index).period50_amount := nvl(g_pd_costs(p_dist_index).period50_amount,0)
5295 + nvl(g_pc_costs(p_element_index).period50_amount,0) * p_percent;
5296 g_pd_costs(p_dist_index).ytd_amount := nvl(g_pd_costs(p_dist_index).ytd_amount, 0)
5297 + nvl(g_pc_costs(p_element_index).period50_amount,0) * p_percent;
5298 end if;
5299
5300 if 51 between l_start_num and l_end_num then
5301 g_pd_costs(p_dist_index).period51_amount := nvl(g_pd_costs(p_dist_index).period51_amount,0)
5302 + nvl(g_pc_costs(p_element_index).period51_amount,0) * p_percent;
5303 g_pd_costs(p_dist_index).ytd_amount := nvl(g_pd_costs(p_dist_index).ytd_amount, 0)
5304 + nvl(g_pc_costs(p_element_index).period51_amount,0) * p_percent;
5305 end if;
5306
5307 if 52 between l_start_num and l_end_num then
5308 g_pd_costs(p_dist_index).period52_amount := nvl(g_pd_costs(p_dist_index).period52_amount,0)
5309 + nvl(g_pc_costs(p_element_index).period52_amount,0) * p_percent;
5310 g_pd_costs(p_dist_index).ytd_amount := nvl(g_pd_costs(p_dist_index).ytd_amount, 0)
5311 + nvl(g_pc_costs(p_element_index).period52_amount,0) * p_percent;
5312 end if;
5313
5314 if 53 between l_start_num and l_end_num then
5315 g_pd_costs(p_dist_index).period53_amount := nvl(g_pd_costs(p_dist_index).period53_amount,0)
5316 + nvl(g_pc_costs(p_element_index).period53_amount,0) * p_percent;
5317 g_pd_costs(p_dist_index).ytd_amount := nvl(g_pd_costs(p_dist_index).ytd_amount, 0)
5318 + nvl(g_pc_costs(p_element_index).period53_amount,0) * p_percent;
5319 end if;
5320
5321 if 54 between l_start_num and l_end_num then
5322 g_pd_costs(p_dist_index).period54_amount := nvl(g_pd_costs(p_dist_index).period54_amount,0)
5323 + nvl(g_pc_costs(p_element_index).period54_amount,0) * p_percent;
5324 g_pd_costs(p_dist_index).ytd_amount := nvl(g_pd_costs(p_dist_index).ytd_amount, 0)
5325 + nvl(g_pc_costs(p_element_index).period54_amount,0) * p_percent;
5326 end if;
5327
5328 if 55 between l_start_num and l_end_num then
5329 g_pd_costs(p_dist_index).period55_amount := nvl(g_pd_costs(p_dist_index).period55_amount,0)
5330 + nvl(g_pc_costs(p_element_index).period55_amount,0) * p_percent;
5331 g_pd_costs(p_dist_index).ytd_amount := nvl(g_pd_costs(p_dist_index).ytd_amount, 0)
5332 + nvl(g_pc_costs(p_element_index).period55_amount,0) * p_percent;
5333 end if;
5334
5335 if 56 between l_start_num and l_end_num then
5336 g_pd_costs(p_dist_index).period56_amount := nvl(g_pd_costs(p_dist_index).period56_amount,0)
5337 + nvl(g_pc_costs(p_element_index).period56_amount,0) * p_percent;
5338 g_pd_costs(p_dist_index).ytd_amount := nvl(g_pd_costs(p_dist_index).ytd_amount, 0)
5339 + nvl(g_pc_costs(p_element_index).period56_amount,0) * p_percent;
5340 end if;
5341
5342 if 57 between l_start_num and l_end_num then
5343 g_pd_costs(p_dist_index).period57_amount := nvl(g_pd_costs(p_dist_index).period57_amount,0)
5344 + nvl(g_pc_costs(p_element_index).period57_amount,0) * p_percent;
5345 g_pd_costs(p_dist_index).ytd_amount := nvl(g_pd_costs(p_dist_index).ytd_amount, 0)
5346 + nvl(g_pc_costs(p_element_index).period57_amount,0) * p_percent;
5347 end if;
5348
5349 if 58 between l_start_num and l_end_num then
5350 g_pd_costs(p_dist_index).period58_amount := nvl(g_pd_costs(p_dist_index).period58_amount,0)
5351 + nvl(g_pc_costs(p_element_index).period58_amount,0) * p_percent;
5352 g_pd_costs(p_dist_index).ytd_amount := nvl(g_pd_costs(p_dist_index).ytd_amount, 0)
5353 + nvl(g_pc_costs(p_element_index).period58_amount,0) * p_percent;
5354 end if;
5355
5356 if 59 between l_start_num and l_end_num then
5357 g_pd_costs(p_dist_index).period59_amount := nvl(g_pd_costs(p_dist_index).period59_amount,0)
5358 + nvl(g_pc_costs(p_element_index).period59_amount,0) * p_percent;
5359 g_pd_costs(p_dist_index).ytd_amount := nvl(g_pd_costs(p_dist_index).ytd_amount, 0)
5360 + nvl(g_pc_costs(p_element_index).period59_amount,0) * p_percent;
5361 end if;
5362
5363 if 60 between l_start_num and l_end_num then
5364 g_pd_costs(p_dist_index).period60_amount := nvl(g_pd_costs(p_dist_index).period60_amount,0)
5365 + nvl(g_pc_costs(p_element_index).period60_amount,0) * p_percent;
5366 g_pd_costs(p_dist_index).ytd_amount := nvl(g_pd_costs(p_dist_index).ytd_amount, 0)
5367 + nvl(g_pc_costs(p_element_index).period60_amount,0) * p_percent;
5368 end if;
5369
5370 end if;
5371
5372 -- Initialize API return status to success
5373
5374 p_return_status := FND_API.G_RET_STS_SUCCESS;
5375
5376
5377 EXCEPTION
5378
5379 when FND_API.G_EXC_ERROR then
5380 p_return_status := FND_API.G_RET_STS_ERROR;
5381
5382 when FND_API.G_EXC_UNEXPECTED_ERROR then
5383 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5384
5385 when OTHERS then
5386 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5387
5388 END Distribute_Periods;
5389
5390 /* Bug No 2278216 End */
5391 /* ------------------------------------------------------------------------- */
5392
5393 -- Add Token and Value to the Message Token array
5394
5395 PROCEDURE message_token(tokname IN VARCHAR2,
5396 tokval IN VARCHAR2) IS
5397
5398 BEGIN
5399
5400 if no_msg_tokens is null then
5401 no_msg_tokens := 1;
5402 else
5403 no_msg_tokens := no_msg_tokens + 1;
5404 end if;
5405
5406 msg_tok_names(no_msg_tokens) := tokname;
5407 msg_tok_val(no_msg_tokens) := tokval;
5408
5409 END message_token;
5410
5411 /* ----------------------------------------------------------------------- */
5412
5413 -- Define a Message Token with a Value and set the Message Name
5414
5415 -- Calls FND_MESSAGE server package to set the Message Stack. This message is
5416 -- retrieved by the calling program.
5417
5418 PROCEDURE add_message(appname IN VARCHAR2,
5419 msgname IN VARCHAR2) IS
5420
5421 i BINARY_INTEGER;
5422
5423 BEGIN
5424
5425 if ((appname is not null) and
5426 (msgname is not null)) then
5427
5428 FND_MESSAGE.SET_NAME(appname, msgname);
5429
5430 if no_msg_tokens is not null then
5431 for i in 1..no_msg_tokens loop
5432 FND_MESSAGE.SET_TOKEN(msg_tok_names(i), msg_tok_val(i));
5433 end loop;
5434 end if;
5435
5436 FND_MSG_PUB.Add;
5437
5438 end if;
5439
5440 -- Clear Message Token stack
5441
5442 no_msg_tokens := 0;
5443
5444 END add_message;
5445
5446 /* ----------------------------------------------------------------------- */
5447
5448 -- Get Debug Information
5449
5450 -- This Module is used to retrieve Debug Information for this Package. It
5451 -- prints Debug Information when run as a Batch Process from SQL*Plus. For
5452 -- the Debug Information to be printed on the Screen, the SQL*Plus parameter
5453 -- 'Serveroutput' should be set to 'ON'
5454
5455 FUNCTION Get_Debug RETURN VARCHAR2 IS
5456 BEGIN
5457 return(g_dbug);
5458 END Get_Debug;
5459
5460 /* ----------------------------------------------------------------------- */
5461
5462 END PSB_WS_POS1;