[Home] [Help]
PACKAGE BODY: APPS.PSB_WORKSHEET_CONSOLIDATE
Source
1 PACKAGE BODY PSB_WORKSHEET_CONSOLIDATE AS
2 /* $Header: PSBVWCDB.pls 120.4 2005/08/09 09:29:56 masethur ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):= 'PSB_WORKSHEET_CONSOLIDATE';
5
6 g_userid NUMBER;
7 g_loginid NUMBER;
8
9 g_global_worksheet_id NUMBER;
10 g_global_data_extract_id NUMBER;
11 g_global_business_group_id NUMBER;
12
13 -- TokNameArray contains names of all tokens
14
15 TYPE TokNameArray IS TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER;
16
17 -- TokValArray contains values for all tokens
18
19 TYPE TokValArray IS TABLE OF VARCHAR2(1000) INDEX BY BINARY_INTEGER;
20
21 -- number of Message Tokens
22
23 no_msg_tokens NUMBER := 0;
24
25 -- Message Token Name
26
27 msg_tok_names TokNameArray;
28
29 -- Message Token Value
30
31 msg_tok_val TokValArray;
32
33 /* ----------------------------------------------------------------------- */
34 /* */
35 /* Private Function Definition */
36 /* */
37 /* ----------------------------------------------------------------------- */
38
39 PROCEDURE Consolidate_Attributes
40 ( p_return_status OUT NOCOPY VARCHAR2,
41 p_local_data_extract_id IN NUMBER,
42 p_local_business_group_id IN NUMBER
43 );
44
45 PROCEDURE Consolidate_Elements
46 ( p_return_status OUT NOCOPY VARCHAR2,
47 p_local_worksheet_id IN NUMBER,
48 p_local_data_extract_id IN NUMBER,
49 p_local_business_group_id IN NUMBER
50 );
51
52 PROCEDURE Consolidate_Employees
53 ( p_return_status OUT NOCOPY VARCHAR2,
54 p_local_data_extract_id IN NUMBER,
55 p_local_business_group_id IN NUMBER
56 );
57
58 PROCEDURE Consolidate_Positions
59 ( p_return_status OUT NOCOPY VARCHAR2,
60 p_local_worksheet_id IN NUMBER,
61 p_local_data_extract_id IN NUMBER,
62 p_local_business_group_id IN NUMBER
63 );
64
65 PROCEDURE Consolidate_Service_Packages
66 ( p_return_status OUT NOCOPY VARCHAR2,
67 p_local_worksheet_id IN NUMBER
68 );
69
70 PROCEDURE Consolidate_Local_Worksheets
71 ( p_return_status OUT NOCOPY VARCHAR2,
72 p_local_worksheet_id IN NUMBER,
73 p_global_worksheet_id IN NUMBER
74 );
75
76 PROCEDURE message_token
77 ( tokname IN VARCHAR2,
78 tokval IN VARCHAR2);
79
80 PROCEDURE add_message
81 (appname IN VARCHAR2,
82 msgname IN VARCHAR2);
83
84 /* ----------------------------------------------------------------------- */
85
86 PROCEDURE Consolidate_Attributes
87 ( p_return_status OUT NOCOPY VARCHAR2,
88 p_local_data_extract_id IN NUMBER,
89 p_local_business_group_id IN NUMBER
90 ) IS
91
92 l_attr_already_exists VARCHAR2(1);
93 l_attrval_already_exists VARCHAR2(1);
94
95 l_attribute_id NUMBER;
96 l_attribute_value_id NUMBER;
97
98 l_msg_count NUMBER;
99 l_msg_data VARCHAR2(2000);
100 l_rowid VARCHAR2(100);
101
102 l_return_status VARCHAR2(1);
103
104 cursor c_attr_seq is
105 select PSB_ATTRIBUTES_S.NEXTVAL seq
106 from dual;
107
108 cursor c_attrval_seq is
109 select PSB_ATTRIBUTE_VALUES_S.NEXTVAL seq
110 from dual;
111
112 cursor c_attr is
113 select *
114 from PSB_ATTRIBUTES
115 where business_group_id = p_local_business_group_id;
116
117 cursor c_attrname_exists (attrname VARCHAR2) is
118 select attribute_id
119 from PSB_ATTRIBUTES
120 where name = attrname
121 and business_group_id = g_global_business_group_id;
122
123 cursor c_attrval (attrid NUMBER) is
124 select *
125 from PSB_ATTRIBUTE_VALUES
126 where attribute_id = attrid
127 and data_extract_id = p_local_data_extract_id;
128
129 cursor c_attrval_exists (attrid NUMBER, attrval VARCHAR2) is
130 select attribute_value_id
131 from PSB_ATTRIBUTE_VALUES
132 where attribute_value = attrval
133 and attribute_id = attrid
134 and data_extract_id = g_global_data_extract_id;
135
136 BEGIN
137
138 -- Loop for each attribute in business group of local worksheet
139
140 for c_attr_rec in c_attr loop
141
142 l_attr_already_exists := FND_API.G_FALSE;
143
144 -- Check if same attribute is already defined in business group of global worksheet
145
146 for c_attrname_exists_rec in c_attrname_exists (c_attr_rec.name) loop
147 l_attribute_id := c_attrname_exists_rec.attribute_id;
148 l_attr_already_exists := FND_API.G_TRUE;
149 end loop;
150
151 -- if attribute doesn't already exist in business group of global worksheet create the
152 -- attribute and all its values
153
154 if not FND_API.to_Boolean(l_attr_already_exists) then
155 begin
156
157 for c_attr_seq_rec in c_attr_seq loop
158 l_attribute_id := c_attr_seq_rec.seq;
159 end loop;
160
161 PSB_POSITION_ATTRIBUTES_PVT.Insert_Row
162 (p_api_version => 1.0,
163 p_return_status => l_return_status,
164 p_msg_count => l_msg_count,
165 p_msg_data => l_msg_data,
166 p_row_id => l_rowid,
167 p_attribute_id => l_attribute_id,
168 p_business_group_id => g_global_business_group_id,
169 p_name => c_attr_rec.name,
170 p_display_in_worksheet => c_attr_rec.display_in_worksheet,
171 p_display_sequence => c_attr_rec.display_sequence,
172 p_display_prompt => c_attr_rec.display_prompt,
173 p_required_for_import_flag => c_attr_rec.required_for_import_flag,
174 p_required_for_positions_flag => c_attr_rec.required_for_positions_flag,
175 p_allow_in_position_set_flag => c_attr_rec.allow_in_position_set_flag,
176 p_value_table_flag => c_attr_rec.value_table_flag,
177 p_protected_flag => c_attr_rec.protected_flag,
178 p_definition_type => c_attr_rec.definition_type,
179 p_definition_structure => c_attr_rec.definition_structure,
180 p_definition_table => c_attr_rec.definition_table,
181 p_definition_column => c_attr_rec.definition_column,
182 p_attribute_type_id => c_attr_rec.attribute_type_id,
183 p_data_type => c_attr_rec.data_type,
184 p_application_id => c_attr_rec.application_id,
185 p_system_attribute_type => c_attr_rec.system_attribute_type,
186 p_last_update_date => sysdate,
187 p_last_updated_by => g_userid,
188 p_last_update_login => g_loginid,
189 p_created_by => g_userid,
190 p_creation_date => sysdate);
191
192 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
193 raise FND_API.G_EXC_ERROR;
194 end if;
195
196 for c_attrval_rec in c_attrval (c_attr_rec.attribute_id) loop
197
198 for c_attrval_seq_rec in c_attrval_seq loop
199 l_attribute_value_id := c_attrval_seq_rec.seq;
200 end loop;
201
202 PSB_ATTRIBUTE_VALUES_PVT.Insert_Row
203 (p_api_version => 1.0,
204 p_return_status => l_return_status,
205 p_msg_count => l_msg_count,
206 p_msg_data => l_msg_data,
207 p_rowid => l_rowid,
208 p_attribute_value_id => l_attribute_value_id,
209 p_attribute_id => l_attribute_id,
210 p_attribute_value => c_attrval_rec.attribute_value,
211 p_description => c_attrval_rec.description,
212 p_hr_value_id => c_attrval_rec.hr_value_id,
213 p_data_extract_id => g_global_data_extract_id,
214 p_attribute1 => c_attrval_rec.attribute1,
215 p_attribute2 => c_attrval_rec.attribute2,
216 p_attribute3 => c_attrval_rec.attribute3,
217 p_attribute4 => c_attrval_rec.attribute4,
218 p_attribute5 => c_attrval_rec.attribute5,
219 p_attribute6 => c_attrval_rec.attribute6,
220 p_attribute7 => c_attrval_rec.attribute7,
221 p_attribute8 => c_attrval_rec.attribute8,
222 p_attribute9 => c_attrval_rec.attribute9,
223 p_attribute10 => c_attrval_rec.attribute10,
224 p_attribute11 => c_attrval_rec.attribute11,
225 p_attribute12 => c_attrval_rec.attribute12,
226 p_attribute13 => c_attrval_rec.attribute13,
227 p_attribute14 => c_attrval_rec.attribute14,
228 p_attribute15 => c_attrval_rec.attribute15,
229 p_attribute16 => c_attrval_rec.attribute16,
230 p_attribute17 => c_attrval_rec.attribute17,
231 p_attribute18 => c_attrval_rec.attribute18,
232 p_attribute19 => c_attrval_rec.attribute19,
233 p_attribute20 => c_attrval_rec.attribute20,
234 p_attribute21 => c_attrval_rec.attribute21,
235 p_attribute22 => c_attrval_rec.attribute22,
236 p_attribute23 => c_attrval_rec.attribute23,
237 p_attribute24 => c_attrval_rec.attribute24,
238 p_attribute25 => c_attrval_rec.attribute25,
239 p_attribute26 => c_attrval_rec.attribute26,
240 p_attribute27 => c_attrval_rec.attribute27,
241 p_attribute28 => c_attrval_rec.attribute28,
242 p_attribute29 => c_attrval_rec.attribute29,
243 p_attribute30 => c_attrval_rec.attribute30,
244 p_context => c_attrval_rec.context,
245 p_last_update_date => sysdate,
246 p_last_updated_by => g_userid,
247 p_last_update_login => g_loginid,
248 p_created_by => g_userid,
249 p_creation_date => sysdate);
250
251 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
252 raise FND_API.G_EXC_ERROR;
253 end if;
254
255 end loop;
256
257 end;
258 else
259 begin
260
261 -- Attribute already exists for the global business group. Only
262 -- consolidate attribute values if required
263
264 for c_attrval_rec in c_attrval (l_attribute_id) loop
265
266 l_attrval_already_exists := FND_API.G_FALSE;
267
268 -- Check if attribute value is already defined in data extract of global worksheet
269
270 for c_attrval_exists_rec in c_attrval_exists (l_attribute_id, c_attrval_rec.attribute_value) loop
271 l_attribute_value_id := c_attrval_exists_rec.attribute_value_id;
272 l_attrval_already_exists := FND_API.G_TRUE;
273 end loop;
274
275 if not FND_API.to_Boolean(l_attrval_already_exists) then
276 begin
277
278 for c_attrval_seq_rec in c_attrval_seq loop
279 l_attribute_value_id := c_attrval_seq_rec.seq;
280 end loop;
281
282 PSB_ATTRIBUTE_VALUES_PVT.Insert_Row
283 (p_api_version => 1.0,
284 p_return_status => l_return_status,
285 p_msg_count => l_msg_count,
286 p_msg_data => l_msg_data,
287 p_rowid => l_rowid,
288 p_attribute_value_id => l_attribute_value_id,
289 p_attribute_id => l_attribute_id,
290 p_attribute_value => c_attrval_rec.attribute_value,
291 p_description => c_attrval_rec.description,
292 p_hr_value_id => c_attrval_rec.hr_value_id,
293 p_data_extract_id => g_global_data_extract_id,
294 p_attribute1 => c_attrval_rec.attribute1,
295 p_attribute2 => c_attrval_rec.attribute2,
296 p_attribute3 => c_attrval_rec.attribute3,
297 p_attribute4 => c_attrval_rec.attribute4,
298 p_attribute5 => c_attrval_rec.attribute5,
299 p_attribute6 => c_attrval_rec.attribute6,
300 p_attribute7 => c_attrval_rec.attribute7,
301 p_attribute8 => c_attrval_rec.attribute8,
302 p_attribute9 => c_attrval_rec.attribute9,
303 p_attribute10 => c_attrval_rec.attribute10,
304 p_attribute11 => c_attrval_rec.attribute11,
305 p_attribute12 => c_attrval_rec.attribute12,
306 p_attribute13 => c_attrval_rec.attribute13,
307 p_attribute14 => c_attrval_rec.attribute14,
308 p_attribute15 => c_attrval_rec.attribute15,
309 p_attribute16 => c_attrval_rec.attribute16,
310 p_attribute17 => c_attrval_rec.attribute17,
311 p_attribute18 => c_attrval_rec.attribute18,
312 p_attribute19 => c_attrval_rec.attribute19,
313 p_attribute20 => c_attrval_rec.attribute20,
314 p_attribute21 => c_attrval_rec.attribute21,
315 p_attribute22 => c_attrval_rec.attribute22,
316 p_attribute23 => c_attrval_rec.attribute23,
317 p_attribute24 => c_attrval_rec.attribute24,
318 p_attribute25 => c_attrval_rec.attribute25,
319 p_attribute26 => c_attrval_rec.attribute26,
320 p_attribute27 => c_attrval_rec.attribute27,
321 p_attribute28 => c_attrval_rec.attribute28,
322 p_attribute29 => c_attrval_rec.attribute29,
323 p_attribute30 => c_attrval_rec.attribute30,
324 p_context => c_attrval_rec.context,
325 p_last_update_date => sysdate,
326 p_last_updated_by => g_userid,
327 p_last_update_login => g_loginid,
328 p_created_by => g_userid,
329 p_creation_date => sysdate);
330
331 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
332 raise FND_API.G_EXC_ERROR;
333 end if;
334
335 end;
336 end if;
337
338 end loop;
339
340 end;
341 end if;
342
343 end loop;
344
345
346 -- Initialize API return status to success
347
348 p_return_status := FND_API.G_RET_STS_SUCCESS;
349
350
351 EXCEPTION
352
353 when FND_API.G_EXC_ERROR then
354 p_return_status := FND_API.G_RET_STS_ERROR;
355
356 when FND_API.G_EXC_UNEXPECTED_ERROR then
357 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
358
359 when OTHERS then
360 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
361
362 END Consolidate_Attributes;
363
364 /* ----------------------------------------------------------------------- */
365
366 PROCEDURE Consolidate_Elements
367 ( p_return_status OUT NOCOPY VARCHAR2,
368 p_local_worksheet_id IN NUMBER,
369 p_local_data_extract_id IN NUMBER,
370 p_local_business_group_id IN NUMBER
371 ) IS
372
373 l_element_exists VARCHAR2(1);
374 l_position_set_exists VARCHAR2(1);
375 l_line_sequence_exists VARCHAR2(1);
376 l_value_sequence_exists VARCHAR2(1);
377 l_element_option_exists VARCHAR2(1);
378
379 l_worksheet_id NUMBER;
380 l_attribute_id NUMBER;
381 l_attribute_value_id NUMBER;
382
383 l_pay_element_id NUMBER;
384 l_pay_element_option_id NUMBER;
385 l_pay_element_rate_id NUMBER;
386 l_position_set_group_id NUMBER;
387 l_position_set_id NUMBER;
388 l_line_sequence_id NUMBER;
389 l_value_sequence_id NUMBER;
390 l_set_relation_id NUMBER;
391 l_distribution_id NUMBER;
392
393 l_msg_count NUMBER;
394 l_msg_data VARCHAR2(2000);
395 l_rowid VARCHAR2(100);
396
397 l_return_status VARCHAR2(1);
398
399 cursor c_elem is
400 select *
401 from PSB_PAY_ELEMENTS
402 where data_extract_id = p_local_data_extract_id;
403
404 cursor c_elemname_exists (elemname VARCHAR2) is
405 select pay_element_id
406 from PSB_PAY_ELEMENTS
407 where name = elemname
408 and data_extract_id = g_global_data_extract_id;
409
410 cursor c_elemoptions (elemid NUMBER) is
411 select *
412 from PSB_PAY_ELEMENT_OPTIONS
413 where pay_element_id = elemid;
414
415 cursor c_elemoptions_exists (elemid NUMBER, optname VARCHAR2) is
416 select pay_element_option_id
417 from PSB_PAY_ELEMENT_OPTIONS
418 where name = optname
419 and pay_element_id = elemid;
420
421 cursor c_elemrates (elemid NUMBER, elemoptionid NUMBER) is
422 select *
423 from PSB_PAY_ELEMENT_RATES
424 where (worksheet_id is null or worksheet_id = p_local_worksheet_id)
425 and pay_element_option_id = elemoptionid
426 and pay_element_id = elemid;
427
428 cursor c_elemrates_nooptions (elemid NUMBER) is
429 select *
430 from PSB_PAY_ELEMENT_RATES
431 where (worksheet_id is null or worksheet_id = p_local_worksheet_id)
432 and pay_element_id = elemid;
433
434 cursor c_possetgrp (elemid NUMBER) is
435 select *
436 from PSB_ELEMENT_POS_SET_GROUPS
437 where pay_element_id = elemid;
438
439 cursor c_setrel (possetgrpid NUMBER) is
440 select *
441 from PSB_SET_RELATIONS
442 where position_set_group_id = possetgrpid;
443
444 cursor c_posset (possetid NUMBER) is
445 select *
446 from PSB_ACCOUNT_POSITION_SETS
447 where account_position_set_id = possetid;
448
449 cursor c_possetline (possetid NUMBER) is
450 select *
451 from PSB_ACCOUNT_POSITION_SET_LINES
452 where account_position_set_id = possetid;
453
454 cursor c_possetlineval (lineseqid NUMBER) is
455 select *
456 from PSB_POSITION_SET_LINE_VALUES
457 where line_sequence_id = lineseqid;
458
459 cursor c_posset_exists (possetname VARCHAR2) is
460 select account_position_set_id
461 from PSB_ACCOUNT_POSITION_SETS
462 where data_extract_id = g_global_data_extract_id
463 and account_or_position_type = 'P'
464 and name = possetname;
465
466 cursor c_possetlineval_exists (lineseqid NUMBER, attrval VARCHAR2) is
467 select value_sequence_id
468 from PSB_POSITION_SET_LINE_VALUES
469 where attribute_value = attrval
470 and line_sequence_id = lineseqid;
471
472 cursor c_re_attr (attrid NUMBER) is
473 select a.attribute_id
474 from PSB_ATTRIBUTES a,
475 PSB_ATTRIBUTES b
476 where a.business_group_id = g_global_business_group_id
477 and a.name = b.name
478 and b.attribute_id = attrid;
479
480 cursor c_re_attrval (attrvalid NUMBER) is
481 select a.attribute_value_id
482 from PSB_ATTRIBUTE_VALUES a,
483 PSB_ATTRIBUTE_VALUES b
484 where a.data_extract_id = g_global_data_extract_id
485 and a.attribute_value = b.attribute_value
486 and a.attribute_id = b.attribute_id -- added this for Bug #4262388
487 and b.attribute_value_id = attrvalid;
488
489 cursor c_possetline_exists (possetid NUMBER, attrid NUMBER) is
490 select line_sequence_id
491 from PSB_ACCOUNT_POSITION_SET_LINES
492 where attribute_id = attrid
493 and account_position_set_id = possetid;
494
495 cursor c_elemdist (possetgrpid NUMBER) is
496 select *
497 from PSB_PAY_ELEMENT_DISTRIBUTIONS
498 where position_set_group_id = possetgrpid;
499
500 cursor c_elem_seq is
501 select psb_pay_elements_s.nextval seq
502 from dual;
503
504 cursor c_elemoptions_seq is
505 select psb_pay_element_options_s.nextval seq
506 from dual;
507
508 cursor c_elemrates_seq is
509 select psb_pay_element_rates_s.nextval seq
510 from dual;
511
512 cursor c_posset_seq is
513 select PSB_ACCOUNT_POSITION_SETS_S.NEXTVAL seq
514 from dual;
515
516 cursor c_possetline_seq is
517 select PSB_ACCT_POSITION_SET_LINES_S.NEXTVAL seq
518 from dual;
519
520 cursor c_possetlineval_seq is
521 select PSB_POSITION_SET_LINE_VALUES_S.NEXTVAL seq
522 from dual;
523
524 cursor c_setrel_seq is
525 select PSB_SET_RELATIONS_S.NEXTVAL seq
526 from dual;
527
528 cursor c_elempossetgrp_seq is
529 select PSB_ELEMENT_POS_SET_GROUPS_S.NEXTVAL seq
530 from dual;
531
532 cursor c_elemdist_seq is
533 select PSB_PAY_ELEMENT_DISTRIBUTION_S.NEXTVAL seq
534 from dual;
535
536 BEGIN
537
538 for c_elem_rec in c_elem loop
539
540 l_element_exists := FND_API.G_FALSE;
541
542 -- Check if element name exists in the global data extract
543
544 for c_elemname_exists_rec in c_elemname_exists (c_elem_rec.name) loop
545 l_pay_element_id := c_elemname_exists_rec.pay_element_id;
546 l_element_exists := FND_API.G_TRUE;
547 end loop;
548
549 if not FND_API.to_Boolean(l_element_exists) then
550 begin
551
552 for c_elem_seq_rec in c_elem_seq loop
553 l_pay_element_id := c_elem_seq_rec.seq;
554 end loop;
555
556 PSB_PAY_ELEMENTS_PVT.INSERT_ROW
557 (p_api_version => 1.0,
558 p_return_status => l_return_status,
559 p_msg_count => l_msg_count,
560 p_msg_data => l_msg_data,
561 p_row_id => l_rowid,
562 p_pay_element_id => l_pay_element_id,
563 p_budget_set_id => c_elem_rec.budget_set_id,
564 p_business_group_id => g_global_business_group_id,
565 p_data_extract_id => g_global_data_extract_id,
566 p_name => c_elem_rec.name,
567 p_description => c_elem_rec.description,
568 p_element_value_type => c_elem_rec.element_value_type,
569 p_formula_id => c_elem_rec.formula_id,
570 p_overwrite_flag => c_elem_rec.overwrite_flag,
571 p_required_flag => c_elem_rec.required_flag,
572 p_follow_salary => c_elem_rec.follow_salary,
573 p_pay_basis => c_elem_rec.pay_basis,
574 p_start_date => c_elem_rec.start_date,
575 p_end_date => c_elem_rec.end_date,
576 p_processing_type => c_elem_rec.processing_type,
577 p_period_type => c_elem_rec.period_type,
578 p_process_period_type => c_elem_rec.process_period_type,
579 p_max_element_value_type => c_elem_rec.max_element_value_type,
580 p_max_element_value => c_elem_rec.max_element_value,
581 p_salary_flag => c_elem_rec.salary_flag,
582 p_salary_type => c_elem_rec.salary_type,
583 p_option_flag => c_elem_rec.option_flag,
584 p_hr_element_type_id => c_elem_rec.hr_element_type_id,
585 p_attribute_category => c_elem_rec.attribute_category,
586 p_attribute1 => c_elem_rec.attribute1,
587 p_attribute2 => c_elem_rec.attribute2,
588 p_attribute3 => c_elem_rec.attribute3,
589 p_attribute4 => c_elem_rec.attribute4,
590 p_attribute5 => c_elem_rec.attribute5,
591 p_attribute6 => c_elem_rec.attribute6,
592 p_attribute7 => c_elem_rec.attribute7,
593 p_attribute8 => c_elem_rec.attribute8,
594 p_attribute9 => c_elem_rec.attribute9,
595 p_attribute10 => c_elem_rec.attribute10,
596 p_last_update_date => sysdate,
597 p_last_updated_by => g_userid,
598 p_last_update_login => g_loginid,
599 p_created_by => g_userid,
600 p_creation_date => sysdate);
601
602 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
603 raise FND_API.G_EXC_ERROR;
604 end if;
605
606 if c_elem_rec.option_flag = 'Y' then
607 begin
608
609 for c_elemoptions_rec in c_elemoptions (c_elem_rec.pay_element_id) loop
610
611 for c_elemoptions_seq_rec in c_elemoptions_seq loop
612 l_pay_element_option_id := c_elemoptions_seq_rec.seq;
613 end loop;
614
615 PSB_PAY_ELEMENT_OPTIONS_PVT.INSERT_ROW
616 (p_api_version => 1.0,
617 p_return_status => l_return_status,
618 p_msg_count => l_msg_count,
619 p_msg_data => l_msg_data,
620 p_pay_element_option_id => l_pay_element_option_id,
621 p_pay_element_id => l_pay_element_id,
622 p_name => c_elemoptions_rec.name,
623 p_grade_step => c_elemoptions_rec.grade_step,
624 p_sequence_number => c_elemoptions_rec.sequence_number,
625 p_last_update_date => sysdate,
626 p_last_updated_by => g_userid,
627 p_last_update_login => g_loginid,
628 p_created_by => g_userid,
629 p_creation_date => sysdate);
630
631 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
632 raise FND_API.G_EXC_ERROR;
633 end if;
634
635 for c_elemrates_rec in c_elemrates (c_elem_rec.pay_element_id, c_elemoptions_rec.pay_element_option_id) loop
636
637 for c_elemrates_seq_rec in c_elemrates_seq loop
638 l_pay_element_rate_id := c_elemrates_seq_rec.seq;
639 end loop;
640
641 if c_elemrates_rec.worksheet_id is null then
642 l_worksheet_id := null;
643 else
644 l_worksheet_id := g_global_worksheet_id;
645 end if;
646
647 PSB_PAY_ELEMENT_RATES_PVT.Insert_Row
648 (p_api_version => 1.0,
649 p_return_status => l_return_status,
650 p_msg_count => l_msg_count,
651 p_msg_data => l_msg_data,
652 p_pay_element_rate_id => l_pay_element_rate_id,
653 p_pay_element_option_id => l_pay_element_option_id,
654 p_pay_element_id => l_pay_element_id,
655 p_effective_start_date => c_elemrates_rec.effective_start_date,
656 p_effective_end_date => c_elemrates_rec.effective_end_date,
657 p_worksheet_id => l_worksheet_id,
658 p_element_value_type => c_elemrates_rec.element_value_type,
659 p_element_value => c_elemrates_rec.element_value,
660 p_pay_basis => c_elemrates_rec.pay_basis,
661 p_formula_id => c_elemrates_rec.formula_id,
662 p_maximum_value => c_elemrates_rec.maximum_value,
663 p_mid_value => c_elemrates_rec.mid_value,
664 p_minimum_value => c_elemrates_rec.minimum_value,
665 p_currency_code => c_elemrates_rec.currency_code,
666 p_last_update_date => sysdate,
667 p_last_updated_by => g_userid,
668 p_last_update_login => g_loginid,
669 p_created_by => g_userid,
670 p_creation_date => sysdate);
671
672 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
673 raise FND_API.G_EXC_ERROR;
674 end if;
675
676 end loop;
677
678 end loop;
679
680 end;
681 else
682 begin
683
684 for c_elemrates_rec in c_elemrates_nooptions (c_elem_rec.pay_element_id) loop
685
686 for c_elemrates_seq_rec in c_elemrates_seq loop
687 l_pay_element_rate_id := c_elemrates_seq_rec.seq;
688 end loop;
689
690 if c_elemrates_rec.worksheet_id is null then
691 l_worksheet_id := null;
692 else
693 l_worksheet_id := g_global_worksheet_id;
694 end if;
695
696 PSB_PAY_ELEMENT_RATES_PVT.Insert_Row
697 (p_api_version => 1.0,
698 p_return_status => l_return_status,
699 p_msg_count => l_msg_count,
700 p_msg_data => l_msg_data,
701 p_pay_element_rate_id => l_pay_element_rate_id,
702 p_pay_element_option_id => NULL,
703 p_pay_element_id => l_pay_element_id,
704 p_effective_start_date => c_elemrates_rec.effective_start_date,
705 p_effective_end_date => c_elemrates_rec.effective_end_date,
706 p_worksheet_id => l_worksheet_id,
707 p_element_value_type => c_elemrates_rec.element_value_type,
708 p_element_value => c_elemrates_rec.element_value,
709 p_pay_basis => c_elemrates_rec.pay_basis,
710 p_formula_id => c_elemrates_rec.formula_id,
711 p_maximum_value => c_elemrates_rec.maximum_value,
712 p_mid_value => c_elemrates_rec.mid_value,
713 p_minimum_value => c_elemrates_rec.minimum_value,
714 p_currency_code => c_elemrates_rec.currency_code,
715 p_last_update_date => sysdate,
716 p_last_updated_by => g_userid,
717 p_last_update_login => g_loginid,
718 p_created_by => g_userid,
719 p_creation_date => sysdate);
720
721 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
722 raise FND_API.G_EXC_ERROR;
723 end if;
724
725 end loop;
726
727 end;
728 end if;
729
730 -- Consolidate Element Position Set Groups
731
732 for c_possetgrp_rec in c_possetgrp (c_elem_rec.pay_element_id) loop
733
734 for c_elempossetgrp_seq_rec in c_elempossetgrp_seq loop
735 l_position_set_group_id := c_elempossetgrp_seq_rec.seq;
736 end loop;
737
738 PSB_ELEMENT_POS_SET_GROUPS_PVT.Insert_Row
739 (p_api_version => 1.0,
740 p_return_status => l_return_status,
741 p_msg_count => l_msg_count,
742 p_msg_data => l_msg_data,
743 p_position_set_group_id => l_position_set_group_id,
744 p_pay_element_id => l_pay_element_id,
745 p_name => c_possetgrp_rec.name,
746 p_last_update_date => sysdate,
747 p_last_updated_by => g_userid,
748 p_last_update_login => g_loginid,
749 p_created_by => g_userid,
750 p_creation_date => sysdate);
751
752 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
753 raise FND_API.G_EXC_ERROR;
754 end if;
755
756 -- Consolidate all Set Relations for the Position Set Group
757
758 for c_setrel_rec in c_setrel (c_possetgrp_rec.position_set_group_id) loop
759
760 for c_posset_rec in c_posset (c_setrel_rec.account_position_set_id) loop
761
762 l_position_set_exists := FND_API.G_FALSE;
763
764 for c_posset_exists_rec in c_posset_exists (c_posset_rec.name) loop
765 l_position_set_id := c_posset_exists_rec.account_position_set_id;
766 l_position_set_exists := FND_API.G_TRUE;
767 end loop;
768
769 if not FND_API.to_Boolean(l_position_set_exists) then
770 begin
771
772 for c_posset_seq_rec in c_posset_seq loop
773 l_position_set_id := c_posset_seq_rec.seq;
774 end loop;
775
776 PSB_ACCOUNT_POSITION_SET_PVT.Insert_Row
777 (p_api_version => 1.0,
778 p_return_status => l_return_status,
779 p_msg_count => l_msg_count,
780 p_msg_data => l_msg_data,
781 p_row_id => l_rowid,
782 p_account_position_set_id => l_position_set_id,
783 p_name => c_posset_rec.name,
784 p_use_in_budget_group_flag => c_posset_rec.use_in_budget_group_flag,
785 p_set_of_books_id => c_posset_rec.set_of_books_id,
786 p_data_extract_id => g_global_data_extract_id,
787 p_global_or_local_type => c_posset_rec.global_or_local_type,
788 p_account_or_position_type => c_posset_rec.account_or_position_type,
789 p_attribute_selection_type => c_posset_rec.attribute_selection_type,
790 p_business_group_id => c_posset_rec.business_group_id,
791 p_last_update_date => sysdate,
792 p_last_updated_by => g_userid,
793 p_last_update_login => g_loginid,
794 p_created_by => g_userid,
795 p_creation_date => sysdate);
796
797 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
798 raise FND_API.G_EXC_ERROR;
799 end if;
800
801 for c_possetline_rec in c_possetline (c_posset_rec.account_position_set_id) loop
802
803 for c_re_attr_rec in c_re_attr (c_possetline_rec.attribute_id) loop
804 l_attribute_id := c_re_attr_rec.attribute_id;
805 end loop;
806
807 for c_possetline_seq_rec in c_possetline_seq loop
808 l_line_sequence_id := c_possetline_seq_rec.seq;
809 end loop;
810
811 PSB_ACCT_POSITION_SET_LINE_PVT.Insert_Row
812 (p_api_version => 1.0,
813 p_return_status => l_return_status,
814 p_msg_count => l_msg_count,
815 p_msg_data => l_msg_data,
816 p_row_id => l_rowid,
817 p_line_sequence_id => l_line_sequence_id,
818 p_account_position_set_id => l_position_set_id,
819 p_description => c_possetline_rec.description,
820 p_business_group_id => g_global_business_group_id,
821 p_attribute_id => l_attribute_id,
822 p_include_or_exclude_type => c_possetline_rec.include_or_exclude_type,
823 p_segment1_low => c_possetline_rec.segment1_low, p_segment2_low => c_possetline_rec.segment2_low,
824 p_segment3_low => c_possetline_rec.segment3_low, p_segment4_low => c_possetline_rec.segment4_low,
825 p_segment5_low => c_possetline_rec.segment5_low, p_segment6_low => c_possetline_rec.segment6_low,
826 p_segment7_low => c_possetline_rec.segment7_low, p_segment8_low => c_possetline_rec.segment8_low,
827 p_segment9_low => c_possetline_rec.segment9_low, p_segment10_low => c_possetline_rec.segment10_low,
828 p_segment11_low => c_possetline_rec.segment11_low, p_segment12_low => c_possetline_rec.segment12_low,
829 p_segment13_low => c_possetline_rec.segment13_low, p_segment14_low => c_possetline_rec.segment14_low,
830 p_segment15_low => c_possetline_rec.segment15_low, p_segment16_low => c_possetline_rec.segment16_low,
831 p_segment17_low => c_possetline_rec.segment17_low, p_segment18_low => c_possetline_rec.segment18_low,
832 p_segment19_low => c_possetline_rec.segment19_low, p_segment20_low => c_possetline_rec.segment20_low,
833 p_segment21_low => c_possetline_rec.segment21_low, p_segment22_low => c_possetline_rec.segment22_low,
834 p_segment23_low => c_possetline_rec.segment23_low, p_segment24_low => c_possetline_rec.segment24_low,
835 p_segment25_low => c_possetline_rec.segment25_low, p_segment26_low => c_possetline_rec.segment26_low,
836 p_segment27_low => c_possetline_rec.segment27_low, p_segment28_low => c_possetline_rec.segment28_low,
837 p_segment29_low => c_possetline_rec.segment29_low, p_segment30_low => c_possetline_rec.segment30_low,
838 p_segment1_high => c_possetline_rec.segment1_high, p_segment2_high => c_possetline_rec.segment2_high,
839 p_segment3_high => c_possetline_rec.segment3_high, p_segment4_high => c_possetline_rec.segment4_high,
840 p_segment5_high => c_possetline_rec.segment5_high, p_segment6_high => c_possetline_rec.segment6_high,
841 p_segment7_high => c_possetline_rec.segment7_high, p_segment8_high => c_possetline_rec.segment8_high,
842 p_segment9_high => c_possetline_rec.segment9_high, p_segment10_high => c_possetline_rec.segment10_high,
843 p_segment11_high => c_possetline_rec.segment11_high, p_segment12_high => c_possetline_rec.segment12_high,
844 p_segment13_high => c_possetline_rec.segment13_high, p_segment14_high => c_possetline_rec.segment14_high,
845 p_segment15_high => c_possetline_rec.segment15_high, p_segment16_high => c_possetline_rec.segment16_high,
846 p_segment17_high => c_possetline_rec.segment17_high, p_segment18_high => c_possetline_rec.segment18_high,
847 p_segment19_high => c_possetline_rec.segment19_high, p_segment20_high => c_possetline_rec.segment20_high,
848 p_segment21_high => c_possetline_rec.segment21_high, p_segment22_high => c_possetline_rec.segment22_high,
849 p_segment23_high => c_possetline_rec.segment23_high, p_segment24_high => c_possetline_rec.segment24_high,
850 p_segment25_high => c_possetline_rec.segment25_high, p_segment26_high => c_possetline_rec.segment26_high,
851 p_segment27_high => c_possetline_rec.segment27_high, p_segment28_high => c_possetline_rec.segment28_high,
852 p_segment29_high => c_possetline_rec.segment29_high, p_segment30_high => c_possetline_rec.segment30_high,
853 p_context => c_possetline_rec.context,
854 p_attribute1 => c_possetline_rec.attribute1, p_attribute2 => c_possetline_rec.attribute2,
855 p_attribute3 => c_possetline_rec.attribute3, p_attribute4 => c_possetline_rec.attribute4,
856 p_attribute5 => c_possetline_rec.attribute5, p_attribute6 => c_possetline_rec.attribute6,
857 p_attribute7 => c_possetline_rec.attribute7, p_attribute8 => c_possetline_rec.attribute8,
858 p_attribute9 => c_possetline_rec.attribute9, p_attribute10 => c_possetline_rec.attribute10,
859 p_last_update_date => sysdate,
860 p_last_updated_by => g_userid,
861 p_last_update_login => g_loginid,
862 p_created_by => g_userid,
863 p_creation_date => sysdate);
864
865 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
866 raise FND_API.G_EXC_ERROR;
867 end if;
868
869 for c_possetlineval_rec in c_possetlineval (c_possetline_rec.line_sequence_id) loop
870
871 for c_re_attrval_rec in c_re_attrval (c_possetlineval_rec.attribute_value_id) loop
872 l_attribute_value_id := c_re_attrval_rec.attribute_value_id;
873 end loop;
874
875 for c_possetlineval_seq_rec in c_possetlineval_seq loop
876 l_value_sequence_id := c_possetlineval_seq_rec.seq;
877 end loop;
878
879 PSB_POS_SET_LINE_VALUES_PVT.Insert_Row
880 (p_api_version => 1.0,
881 p_return_status => l_return_status,
882 p_msg_count => l_msg_count,
883 p_msg_data => l_msg_data,
884 p_row_id => l_rowid,
885 p_value_sequence_id => l_value_sequence_id,
886 p_line_sequence_id => l_line_sequence_id,
887 p_attribute_value_id => l_attribute_value_id,
888 p_attribute_value => c_possetlineval_rec.attribute_value,
889 p_last_update_date => sysdate,
890 p_last_updated_by => g_userid,
891 p_last_update_login => g_loginid,
892 p_created_by => g_userid,
893 p_creation_date => sysdate);
894
895 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
896 raise FND_API.G_EXC_ERROR;
897 end if;
898
899 end loop;
900
901 end loop;
902
903 end; /* Position Set does not exist */
904 else
905 begin /* Position Set exists; only consolidate position set lines and attribute values */
906
907 for c_possetline_rec in c_possetline (c_posset_rec.account_position_set_id) loop
908
909 l_line_sequence_exists := FND_API.G_FALSE;
910
911 -- Find attribute id for the global business group
912
913 for c_re_attr_rec in c_re_attr (c_possetline_rec.attribute_id) loop
914 l_attribute_id := c_re_attr_rec.attribute_id;
915 end loop;
916
917 for c_possetline_exists_rec in c_possetline_exists (l_position_set_id, l_attribute_id) loop
918 l_line_sequence_id := c_possetline_exists_rec.line_sequence_id;
919 l_line_sequence_exists := FND_API.G_TRUE;
920 end loop;
921
922 if not FND_API.to_Boolean(l_line_sequence_exists) then
923 begin
924
925 for c_possetline_seq_rec in c_possetline_seq loop
926 l_line_sequence_id := c_possetline_seq_rec.seq;
927 end loop;
928
929 PSB_ACCT_POSITION_SET_LINE_PVT.Insert_Row
930 (p_api_version => 1.0,
931 p_return_status => l_return_status,
932 p_msg_count => l_msg_count,
933 p_msg_data => l_msg_data,
934 p_row_id => l_rowid,
935 p_line_sequence_id => l_line_sequence_id,
936 p_account_position_set_id => l_position_set_id,
937 p_description => c_possetline_rec.description,
938 p_business_group_id => g_global_business_group_id,
939 p_attribute_id => l_attribute_id,
940 p_include_or_exclude_type => c_possetline_rec.include_or_exclude_type,
941 p_segment1_low => c_possetline_rec.segment1_low, p_segment2_low => c_possetline_rec.segment2_low,
942 p_segment3_low => c_possetline_rec.segment3_low, p_segment4_low => c_possetline_rec.segment4_low,
943 p_segment5_low => c_possetline_rec.segment5_low, p_segment6_low => c_possetline_rec.segment6_low,
944 p_segment7_low => c_possetline_rec.segment7_low, p_segment8_low => c_possetline_rec.segment8_low,
945 p_segment9_low => c_possetline_rec.segment9_low, p_segment10_low => c_possetline_rec.segment10_low,
946 p_segment11_low => c_possetline_rec.segment11_low, p_segment12_low => c_possetline_rec.segment12_low,
947 p_segment13_low => c_possetline_rec.segment13_low, p_segment14_low => c_possetline_rec.segment14_low,
948 p_segment15_low => c_possetline_rec.segment15_low, p_segment16_low => c_possetline_rec.segment16_low,
949 p_segment17_low => c_possetline_rec.segment17_low, p_segment18_low => c_possetline_rec.segment18_low,
950 p_segment19_low => c_possetline_rec.segment19_low, p_segment20_low => c_possetline_rec.segment20_low,
951 p_segment21_low => c_possetline_rec.segment21_low, p_segment22_low => c_possetline_rec.segment22_low,
952 p_segment23_low => c_possetline_rec.segment23_low, p_segment24_low => c_possetline_rec.segment24_low,
953 p_segment25_low => c_possetline_rec.segment25_low, p_segment26_low => c_possetline_rec.segment26_low,
954 p_segment27_low => c_possetline_rec.segment27_low, p_segment28_low => c_possetline_rec.segment28_low,
955 p_segment29_low => c_possetline_rec.segment29_low, p_segment30_low => c_possetline_rec.segment30_low,
956 p_segment1_high => c_possetline_rec.segment1_high, p_segment2_high => c_possetline_rec.segment2_high,
957 p_segment3_high => c_possetline_rec.segment3_high, p_segment4_high => c_possetline_rec.segment4_high,
958 p_segment5_high => c_possetline_rec.segment5_high, p_segment6_high => c_possetline_rec.segment6_high,
959 p_segment7_high => c_possetline_rec.segment7_high, p_segment8_high => c_possetline_rec.segment8_high,
960 p_segment9_high => c_possetline_rec.segment9_high, p_segment10_high => c_possetline_rec.segment10_high,
961 p_segment11_high => c_possetline_rec.segment11_high, p_segment12_high => c_possetline_rec.segment12_high,
962 p_segment13_high => c_possetline_rec.segment13_high, p_segment14_high => c_possetline_rec.segment14_high,
963 p_segment15_high => c_possetline_rec.segment15_high, p_segment16_high => c_possetline_rec.segment16_high,
964 p_segment17_high => c_possetline_rec.segment17_high, p_segment18_high => c_possetline_rec.segment18_high,
965 p_segment19_high => c_possetline_rec.segment19_high, p_segment20_high => c_possetline_rec.segment20_high,
966 p_segment21_high => c_possetline_rec.segment21_high, p_segment22_high => c_possetline_rec.segment22_high,
967 p_segment23_high => c_possetline_rec.segment23_high, p_segment24_high => c_possetline_rec.segment24_high,
968 p_segment25_high => c_possetline_rec.segment25_high, p_segment26_high => c_possetline_rec.segment26_high,
969 p_segment27_high => c_possetline_rec.segment27_high, p_segment28_high => c_possetline_rec.segment28_high,
970 p_segment29_high => c_possetline_rec.segment29_high, p_segment30_high => c_possetline_rec.segment30_high,
971 p_context => c_possetline_rec.context,
972 p_attribute1 => c_possetline_rec.attribute1, p_attribute2 => c_possetline_rec.attribute2,
973 p_attribute3 => c_possetline_rec.attribute3, p_attribute4 => c_possetline_rec.attribute4,
974 p_attribute5 => c_possetline_rec.attribute5, p_attribute6 => c_possetline_rec.attribute6,
975 p_attribute7 => c_possetline_rec.attribute7, p_attribute8 => c_possetline_rec.attribute8,
976 p_attribute9 => c_possetline_rec.attribute9, p_attribute10 => c_possetline_rec.attribute10,
977 p_last_update_date => sysdate,
978 p_last_updated_by => g_userid,
979 p_last_update_login => g_loginid,
980 p_created_by => g_userid,
981 p_creation_date => sysdate);
982
983 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
984 raise FND_API.G_EXC_ERROR;
985 end if;
986
987 for c_possetlineval_rec in c_possetlineval (c_possetline_rec.line_sequence_id) loop
988
989 for c_re_attrval_rec in c_re_attrval (c_possetlineval_rec.attribute_value_id) loop
990 l_attribute_value_id := c_re_attrval_rec.attribute_value_id;
991 end loop;
992
993 for c_possetlineval_seq_rec in c_possetlineval_seq loop
994 l_value_sequence_id := c_possetlineval_seq_rec.seq;
995 end loop;
996
997 PSB_POS_SET_LINE_VALUES_PVT.Insert_Row
998 (p_api_version => 1.0,
999 p_return_status => l_return_status,
1000 p_msg_count => l_msg_count,
1001 p_msg_data => l_msg_data,
1002 p_row_id => l_rowid,
1003 p_value_sequence_id => l_value_sequence_id,
1004 p_line_sequence_id => l_line_sequence_id,
1005 p_attribute_value_id => l_attribute_value_id,
1006 p_attribute_value => c_possetlineval_rec.attribute_value,
1007 p_last_update_date => sysdate,
1008 p_last_updated_by => g_userid,
1009 p_last_update_login => g_loginid,
1010 p_created_by => g_userid,
1011 p_creation_date => sysdate);
1012
1013 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1014 raise FND_API.G_EXC_ERROR;
1015 end if;
1016
1017 end loop;
1018
1019 end; /* Position Set exists, Line Sequence does not exist */
1020 else
1021 begin /* Position Set exists, Line Sequence also exists */
1022
1023 for c_possetlineval_rec in c_possetlineval (c_possetline_rec.line_sequence_id) loop
1024
1025 for c_re_attrval_rec in c_re_attrval (c_possetlineval_rec.attribute_value_id) loop
1026 l_attribute_value_id := c_re_attrval_rec.attribute_value_id;
1027 end loop;
1028
1029 for c_possetlineval_exists_rec in c_possetlineval_exists (l_line_sequence_id, c_possetlineval_rec.attribute_value) loop
1030 l_value_sequence_id := c_possetlineval_exists_rec.value_sequence_id;
1031 l_value_sequence_exists := FND_API.G_TRUE;
1032 end loop;
1033
1034 if not FND_API.to_Boolean(l_value_sequence_exists) then
1035 begin
1036
1037 for c_possetlineval_seq_rec in c_possetlineval_seq loop
1038 l_value_sequence_id := c_possetlineval_seq_rec.seq;
1039 end loop;
1040
1041 PSB_POS_SET_LINE_VALUES_PVT.Insert_Row
1042 (p_api_version => 1.0,
1043 p_return_status => l_return_status,
1044 p_msg_count => l_msg_count,
1045 p_msg_data => l_msg_data,
1046 p_row_id => l_rowid,
1047 p_value_sequence_id => l_value_sequence_id,
1048 p_line_sequence_id => l_line_sequence_id,
1049 p_attribute_value_id => l_attribute_value_id,
1050 p_attribute_value => c_possetlineval_rec.attribute_value,
1051 p_last_update_date => sysdate,
1052 p_last_updated_by => g_userid,
1053 p_last_update_login => g_loginid,
1054 p_created_by => g_userid,
1055 p_creation_date => sysdate);
1056
1057 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1058 raise FND_API.G_EXC_ERROR;
1059 end if;
1060
1061 end;
1062 end if;
1063
1064 end loop;
1065
1066 end;
1067 end if;
1068
1069 -- Since we're adding attribute values set the attribute selection type to 'O'
1070
1071 if c_posset_rec.attribute_selection_type <> 'O' then
1072 begin
1073
1074 PSB_ACCOUNT_POSITION_SET_PVT.Update_Row
1075 (p_api_version => 1.0,
1076 p_return_status => l_return_status,
1077 p_msg_count => l_msg_count,
1078 p_msg_data => l_msg_data,
1079 p_row_id => l_rowid,
1080 p_account_position_set_id => l_position_set_id,
1081 p_name => c_posset_rec.name,
1082 p_set_of_books_id => c_posset_rec.set_of_books_id,
1083 p_data_extract_id => g_global_data_extract_id,
1084 p_global_or_local_type => c_posset_rec.global_or_local_type,
1085 p_account_or_position_type => c_posset_rec.account_or_position_type,
1086 p_attribute_selection_type => 'O',
1087 p_business_group_id => g_global_business_group_id,
1088 p_last_update_date => sysdate,
1089 p_last_updated_by => g_userid,
1090 p_last_update_login => g_loginid);
1091
1092 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1093 raise FND_API.G_EXC_ERROR;
1094 end if;
1095
1096 end;
1097 end if;
1098
1099 end loop;
1100
1101 end;
1102 end if;
1103
1104 -- Consolidate Set Relations
1105
1106 for c_setrel_seq_rec in c_setrel_seq loop
1107 l_set_relation_id := c_setrel_seq_rec.seq;
1108 end loop;
1109
1110 PSB_SET_RELATION_PVT.Insert_Row
1111 (p_api_version => 1.0,
1112 p_return_status => l_return_status,
1113 p_msg_count => l_msg_count,
1114 p_msg_data => l_msg_data,
1115 p_row_id => l_rowid,
1116 p_set_relation_id => l_set_relation_id,
1117 p_account_position_set_id => l_position_set_id,
1118 p_allocation_rule_id => NULL,
1119 p_budget_group_id => NULL,
1120 p_budget_workflow_rule_id => NULL,
1121 p_constraint_id => NULL,
1122 p_default_rule_id => NULL,
1123 p_parameter_id => NULL,
1124 p_position_set_group_id => l_position_set_group_id,
1125 /* Budget Revision Rules Enhancement Start */
1126 p_rule_id => NULL,
1127 p_apply_balance_flag => NULL,
1128 /* Budget Revision Rules Enhancement End */
1129 p_effective_start_date => c_setrel_rec.effective_start_date,
1130 p_effective_end_date => c_setrel_rec.effective_end_date,
1131 p_last_update_date => sysdate,
1132 p_last_updated_by => g_userid,
1133 p_last_update_login => g_loginid,
1134 p_created_by => g_userid,
1135 p_creation_date => sysdate);
1136
1137 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1138 raise FND_API.G_EXC_ERROR;
1139 end if;
1140
1141 end loop; /* Position Sets */
1142
1143 end loop; /* Set Relations */
1144
1145 for c_elemdist_rec in c_elemdist (c_possetgrp_rec.position_set_group_id) loop
1146
1147 for c_elemdist_seq_rec in c_elemdist_seq loop
1148 l_distribution_id := c_elemdist_seq_rec.seq;
1149 end loop;
1150
1151 PSB_ELEMENT_DISTRIBUTIONS_PVT.Insert_Row
1152 (p_api_version => 1.0,
1153 p_return_status => l_return_status,
1154 p_msg_count => l_msg_count,
1155 p_msg_data => l_msg_data,
1156 p_distribution_id => l_distribution_id,
1157 p_position_set_group_id => l_position_set_group_id,
1158 p_chart_of_accounts_id => c_elemdist_rec.chart_of_accounts_id,
1159 p_effective_start_date => c_elemdist_rec.effective_start_date,
1160 p_effective_end_date => c_elemdist_rec.effective_end_date,
1161 p_distribution_percent => c_elemdist_rec.distribution_percent,
1162 p_concatenated_segments => c_elemdist_rec.concatenated_segments,
1163 p_code_combination_id => c_elemdist_rec.code_combination_id,
1164 p_distribution_set_id => c_elemdist_rec.distribution_set_id,
1165 p_segment1 => c_elemdist_rec.segment1, p_segment2 => c_elemdist_rec.segment2,
1166 p_segment3 => c_elemdist_rec.segment3, p_segment4 => c_elemdist_rec.segment4,
1167 p_segment5 => c_elemdist_rec.segment5, p_segment6 => c_elemdist_rec.segment6,
1168 p_segment7 => c_elemdist_rec.segment7, p_segment8 => c_elemdist_rec.segment8,
1169 p_segment9 => c_elemdist_rec.segment9, p_segment10 => c_elemdist_rec.segment10,
1170 p_segment11 => c_elemdist_rec.segment11, p_segment12 => c_elemdist_rec.segment12,
1171 p_segment13 => c_elemdist_rec.segment13, p_segment14 => c_elemdist_rec.segment14,
1172 p_segment15 => c_elemdist_rec.segment15, p_segment16 => c_elemdist_rec.segment16,
1173 p_segment17 => c_elemdist_rec.segment17, p_segment18 => c_elemdist_rec.segment18,
1174 p_segment19 => c_elemdist_rec.segment19, p_segment20 => c_elemdist_rec.segment20,
1175 p_segment21 => c_elemdist_rec.segment21, p_segment22 => c_elemdist_rec.segment22,
1176 p_segment23 => c_elemdist_rec.segment23, p_segment24 => c_elemdist_rec.segment24,
1177 p_segment25 => c_elemdist_rec.segment25, p_segment26 => c_elemdist_rec.segment26,
1178 p_segment27 => c_elemdist_rec.segment27, p_segment28 => c_elemdist_rec.segment28,
1179 p_segment29 => c_elemdist_rec.segment29, p_segment30 => c_elemdist_rec.segment30,
1180 p_last_update_date => sysdate,
1181 p_last_updated_by => g_userid,
1182 p_last_update_login => g_loginid,
1183 p_created_by => g_userid,
1184 p_creation_date => sysdate);
1185
1186 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1187 raise FND_API.G_EXC_ERROR;
1188 end if;
1189
1190 end loop; /* Element Distribution */
1191
1192 end loop; /* Element Position Set Group */
1193
1194 end; /* Element doesn't exist */
1195 else
1196 begin /* Element already exists in the global data extract */
1197
1198 if c_elem_rec.option_flag = 'Y' then
1199 begin
1200
1201 for c_elemoptions_rec in c_elemoptions (c_elem_rec.pay_element_id) loop
1202
1203 l_element_option_exists := FND_API.G_FALSE;
1204
1205 -- Check if Element Option already exists
1206
1207 for c_elemoptions_exists_rec in c_elemoptions_exists (c_elem_rec.pay_element_id, c_elemoptions_rec.name) loop
1208 l_element_option_exists := FND_API.G_TRUE;
1209 end loop;
1210
1211 if not FND_API.to_Boolean(l_element_option_exists) then
1212 begin
1213
1214 for c_elemoptions_seq_rec in c_elemoptions_seq loop
1215 l_pay_element_option_id := c_elemoptions_seq_rec.seq;
1216 end loop;
1217
1218 PSB_PAY_ELEMENT_OPTIONS_PVT.INSERT_ROW
1219 (p_api_version => 1.0,
1220 p_return_status => l_return_status,
1221 p_msg_count => l_msg_count,
1222 p_msg_data => l_msg_data,
1223 p_pay_element_option_id => l_pay_element_option_id,
1224 p_pay_element_id => l_pay_element_id,
1225 p_name => c_elemoptions_rec.name,
1226 p_grade_step => c_elemoptions_rec.grade_step,
1227 p_sequence_number => c_elemoptions_rec.sequence_number,
1228 p_last_update_date => sysdate,
1229 p_last_updated_by => g_userid,
1230 p_last_update_login => g_loginid,
1231 p_created_by => g_userid,
1232 p_creation_date => sysdate);
1233
1234 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1235 raise FND_API.G_EXC_ERROR;
1236 end if;
1237
1238 for c_elemrates_rec in c_elemrates (c_elem_rec.pay_element_id, c_elemoptions_rec.pay_element_option_id) loop
1239
1240 if c_elemrates_rec.worksheet_id is null then
1241 l_worksheet_id := null;
1242 else
1243 l_worksheet_id := g_global_worksheet_id;
1244 end if;
1245
1246 for c_elemrates_seq_rec in c_elemrates_seq loop
1247 l_pay_element_rate_id := c_elemrates_seq_rec.seq;
1248 end loop;
1249
1250 PSB_PAY_ELEMENT_RATES_PVT.Insert_Row
1251 (p_api_version => 1.0,
1252 p_return_status => l_return_status,
1253 p_msg_count => l_msg_count,
1254 p_msg_data => l_msg_data,
1255 p_pay_element_rate_id => l_pay_element_rate_id,
1256 p_pay_element_option_id => l_pay_element_option_id,
1257 p_pay_element_id => l_pay_element_id,
1258 p_effective_start_date => c_elemrates_rec.effective_start_date,
1259 p_effective_end_date => c_elemrates_rec.effective_end_date,
1260 p_worksheet_id => l_worksheet_id,
1261 p_element_value_type => c_elemrates_rec.element_value_type,
1262 p_element_value => c_elemrates_rec.element_value,
1263 p_pay_basis => c_elemrates_rec.pay_basis,
1264 p_formula_id => c_elemrates_rec.formula_id,
1265 p_maximum_value => c_elemrates_rec.maximum_value,
1266 p_mid_value => c_elemrates_rec.mid_value,
1267 p_minimum_value => c_elemrates_rec.minimum_value,
1268 p_currency_code => c_elemrates_rec.currency_code,
1269 p_last_update_date => sysdate,
1270 p_last_updated_by => g_userid,
1271 p_last_update_login => g_loginid,
1272 p_created_by => g_userid,
1273 p_creation_date => sysdate);
1274
1275 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1276 raise FND_API.G_EXC_ERROR;
1277 end if;
1278
1279 end loop;
1280
1281 end;
1282 end if;
1283
1284 end loop;
1285
1286 end;
1287 end if;
1288
1289 end;
1290 end if;
1291
1292 end loop;
1293
1294
1295 -- Initialize API return status to success
1296
1297 p_return_status := FND_API.G_RET_STS_SUCCESS;
1298
1299
1300 EXCEPTION
1301
1302 when FND_API.G_EXC_ERROR then
1303 p_return_status := FND_API.G_RET_STS_ERROR;
1304
1305 when FND_API.G_EXC_UNEXPECTED_ERROR then
1306 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1307
1308 when OTHERS then
1309 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1310
1311 END Consolidate_Elements;
1312
1313 /* ----------------------------------------------------------------------- */
1314
1315 PROCEDURE Consolidate_Employees
1316 ( p_return_status OUT NOCOPY VARCHAR2,
1317 p_local_data_extract_id IN NUMBER,
1318 p_local_business_group_id IN NUMBER
1319 ) IS
1320
1321 l_employee_id NUMBER;
1322
1323 l_employee_exists VARCHAR2(1);
1324
1325 l_return_status VARCHAR2(1);
1326
1327 cursor c_emp is
1328 select *
1329 from PSB_EMPLOYEES
1330 where business_group_id = p_local_business_group_id
1331 and data_extract_id = p_local_data_extract_id;
1332
1333 cursor c_emp_exists (empno NUMBER) is
1334 select employee_id
1335 from psb_employees
1336 where employee_number = empno
1337 and data_extract_id = g_global_data_extract_id;
1338
1339 cursor c_emp_seq is
1340 select PSB_EMPLOYEES_S.NEXTVAL seq
1341 from dual;
1342
1343 BEGIN
1344
1345 -- Loop for all employees in the local data extract
1346
1347 for c_emp_rec in c_emp loop
1348
1349 l_employee_exists := FND_API.G_FALSE;
1350
1351 for c_emp_exists_rec in c_emp_exists (c_emp_rec.employee_number) loop
1352 l_employee_exists := FND_API.G_TRUE;
1353 end loop;
1354
1355 -- If employee does not already exist in the global data extract create the employee
1356
1357 if not FND_API.to_Boolean(l_employee_exists) then
1358 begin
1359
1360 for c_emp_seq_rec in c_emp_seq loop
1361 l_employee_id := c_emp_seq_rec.seq;
1362 end loop;
1363
1364 /*For Bug No : 2594575 Start*/
1365 --Stop extracting secured data of employee
1366 --Removed the columns in psb_employees table
1367 /*For Bug No : 2594575 End*/
1368
1369 insert into PSB_EMPLOYEES
1370 (employee_id, data_extract_id, business_group_id,
1371 employee_number, hr_employee_id, first_name,
1372 full_name, known_as, last_name,
1373 middle_names, title,
1374 creation_date, created_by, last_update_date,
1375 last_updated_by, last_update_login)
1376 values (l_employee_id, g_global_data_extract_id, g_global_business_group_id,
1377 c_emp_rec.employee_number, c_emp_rec.hr_employee_id, c_emp_rec.first_name,
1378 c_emp_rec.full_name, c_emp_rec.known_as, c_emp_rec.last_name,
1379 c_emp_rec.middle_names, c_emp_rec.title,
1380 sysdate, g_userid, sysdate,
1381 g_userid, g_loginid);
1382
1383 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1384 raise FND_API.G_EXC_ERROR;
1385 end if;
1386
1387 end;
1388 end if;
1389
1390 end loop;
1391
1392
1393 -- Initialize API return status to success
1394
1395 p_return_status := FND_API.G_RET_STS_SUCCESS;
1396
1397
1398 EXCEPTION
1399
1400 when FND_API.G_EXC_ERROR then
1401 p_return_status := FND_API.G_RET_STS_ERROR;
1402
1403 when FND_API.G_EXC_UNEXPECTED_ERROR then
1404 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1405
1406 when OTHERS then
1407 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1408
1409 END Consolidate_Employees;
1410
1411 /* ----------------------------------------------------------------------- */
1412
1413 PROCEDURE Consolidate_Positions
1414 ( p_return_status OUT NOCOPY VARCHAR2,
1415 p_local_worksheet_id IN NUMBER,
1416 p_local_data_extract_id IN NUMBER,
1417 p_local_business_group_id IN NUMBER
1418 ) IS
1419
1420 l_position_id NUMBER;
1421 l_position_assignment_id NUMBER;
1422 l_distribution_id NUMBER;
1423
1424 l_attribute_id NUMBER;
1425 l_attribute_value_id NUMBER;
1426 l_worksheet_id NUMBER;
1427 l_pay_element_id NUMBER;
1428 l_pay_element_option_id NUMBER;
1429 l_employee_id NUMBER;
1430
1431 l_position_exists VARCHAR2(1);
1432
1433 l_msg_count NUMBER;
1434 l_msg_data VARCHAR2(2000);
1435 l_rowid VARCHAR2(100);
1436
1437 l_return_status VARCHAR2(1);
1438
1439 cursor c_positions is
1440 select *
1441 from PSB_POSITIONS
1442 where data_extract_id = p_local_data_extract_id;
1443
1444 cursor c_position_exists (posname VARCHAR2, hrposid NUMBER, hrempid NUMBER) is
1445 select position_id
1446 from PSB_POSITIONS
1447 where ((hrposid is null and (hrempid is null or hr_employee_id = hrempid) and name = posname)
1448 or ((hr_position_id = hrposid) and (hrempid is null or hr_employee_id = hrempid)))
1449 and data_extract_id = g_global_data_extract_id;
1450
1451 cursor c_posassign_attr (positionid NUMBER) is
1452 select *
1453 from PSB_POSITION_ASSIGNMENTS
1454 where (worksheet_id is null or worksheet_id = p_local_worksheet_id)
1455 and assignment_type = 'ATTRIBUTE'
1456 and position_id = positionid;
1457
1458 cursor c_re_attr (attrid NUMBER) is
1459 select a.attribute_id
1460 from PSB_ATTRIBUTES a,
1461 PSB_ATTRIBUTES b
1462 where a.business_group_id = g_global_business_group_id
1463 and a.name = b.name
1464 and b.attribute_id = attrid;
1465
1466 cursor c_re_attrval (attrvalid NUMBER) is
1467 select a.attribute_value_id
1468 from PSB_ATTRIBUTE_VALUES a,
1469 PSB_ATTRIBUTE_VALUES b
1470 where a.data_extract_id = g_global_data_extract_id
1471 and a.attribute_value = b.attribute_value
1472 and a.attribute_id = b.attribute_id -- added this for Bug #4262388
1473 and b.attribute_value_id = attrvalid;
1474
1475 cursor c_posassign_elem (positionid NUMBER) is
1476 select *
1477 from PSB_POSITION_ASSIGNMENTS
1478 where (worksheet_id is null or worksheet_id = p_local_worksheet_id)
1479 and assignment_type = 'ELEMENT'
1480 and position_id = positionid;
1481
1482 cursor c_re_elem (elemid NUMBER) is
1483 select a.pay_element_id
1484 from PSB_PAY_ELEMENTS a,
1485 PSB_PAY_ELEMENTS b
1486 where a.name = b.name
1487 and a.data_extract_id = g_global_data_extract_id
1488 and b.pay_element_id = elemid;
1489
1490 cursor c_re_elemopt (elemid NUMBER, elemoptid NUMBER) is
1491 select a.pay_element_option_id
1492 from PSB_PAY_ELEMENT_OPTIONS a,
1493 PSB_PAY_ELEMENT_OPTIONS b
1494 where a.name = b.name
1495 and a.pay_element_id = elemid
1496 and b.pay_element_option_id = elemoptid
1497 and nvl(a.sequence_number, -1) = nvl(b.sequence_number, -1);
1498
1499 cursor c_posassign_emp (positionid NUMBER) is
1500 select *
1501 from PSB_POSITION_ASSIGNMENTS
1502 where (worksheet_id is null or worksheet_id = p_local_worksheet_id)
1503 and assignment_type = 'EMPLOYEE'
1504 and position_id = positionid;
1505
1506 cursor c_re_emp (empid NUMBER) is
1507 select a.employee_id
1508 from PSB_EMPLOYEES a,
1509 PSB_EMPLOYEES b
1510 where a.employee_number = b.employee_number
1511 and a.data_extract_id = g_global_data_extract_id
1512 and b.employee_id = empid;
1513
1514 cursor c_position_distr (positionid NUMBER) is
1515 select *
1516 from PSB_POSITION_PAY_DISTRIBUTIONS
1517 where position_id = positionid
1518 and (worksheet_id is null or worksheet_id = p_local_worksheet_id);
1519
1520 cursor c_pos_seq is
1521 select PSB_POSITIONS_S.NEXTVAL seq
1522 from dual;
1523
1524 cursor c_posassign_seq is
1525 select PSB_POSITION_ASSIGNMENTS_S.NEXTVAL seq
1526 from dual;
1527
1528 cursor c_posdistr_seq is
1529 select PSB_POSITION_PAY_DISTR_S.NEXTVAL seq
1530 from dual;
1531
1532 BEGIN
1533
1534 -- Loop for all positions in the local data extract
1535
1536 for c_positions_rec in c_positions loop
1537
1538 l_position_exists := FND_API.G_FALSE;
1539
1540 for c_position_exists_rec in c_position_exists (c_positions_rec.name, c_positions_rec.hr_position_id, c_positions_rec.hr_employee_id) loop
1541 l_position_exists := FND_API.G_TRUE;
1542 end loop;
1543
1544 -- If position does not exist in the global data extract create the position
1545
1546 if not FND_API.to_Boolean(l_position_exists) then
1547 begin
1548
1549 for c_pos_seq_rec in c_pos_seq loop
1550 l_position_id := c_pos_seq_rec.seq;
1551 end loop;
1552
1553 PSB_POSITIONS_PVT.Insert_Row
1554 (p_api_version => 1.0,
1555 p_return_status => l_return_status,
1556 p_msg_count => l_msg_count,
1557 p_msg_data => l_msg_data,
1558 p_rowid => l_rowid,
1559 p_position_id => l_position_id,
1560 p_data_extract_id => g_global_data_extract_id,
1561 p_position_definition_id => c_positions_rec.position_definition_id,
1562 p_hr_position_id => c_positions_rec.hr_position_id,
1563 p_hr_employee_id => c_positions_rec.hr_employee_id,
1564 p_business_group_id => g_global_business_group_id,
1565 -- de by org
1566 p_organization_id => c_positions_rec.organization_id,
1567 p_effective_start_date => c_positions_rec.effective_start_date,
1568 p_effective_end_date => c_positions_rec.effective_end_date,
1569 p_set_of_books_id => c_positions_rec.set_of_books_id,
1570 p_vacant_position_flag => c_positions_rec.vacant_position_flag,
1571 p_availability_status => c_positions_rec.availability_status,
1572 p_transaction_id => c_positions_rec.transaction_id,
1573 p_transaction_status => c_positions_rec.transaction_status,
1574 p_new_position_flag => c_positions_rec.new_position_flag,
1575 p_attribute1 => c_positions_rec.attribute1,
1576 p_attribute2 => c_positions_rec.attribute2,
1577 p_attribute3 => c_positions_rec.attribute3,
1578 p_attribute4 => c_positions_rec.attribute4,
1579 p_attribute5 => c_positions_rec.attribute5,
1580 p_attribute6 => c_positions_rec.attribute6,
1581 p_attribute7 => c_positions_rec.attribute7,
1582 p_attribute8 => c_positions_rec.attribute8,
1583 p_attribute9 => c_positions_rec.attribute9,
1584 p_attribute10 => c_positions_rec.attribute10,
1585 p_attribute11 => c_positions_rec.attribute11,
1586 p_attribute12 => c_positions_rec.attribute12,
1587 p_attribute13 => c_positions_rec.attribute13,
1588 p_attribute14 => c_positions_rec.attribute14,
1589 p_attribute15 => c_positions_rec.attribute15,
1590 p_attribute16 => c_positions_rec.attribute16,
1591 p_attribute17 => c_positions_rec.attribute17,
1592 p_attribute18 => c_positions_rec.attribute18,
1593 p_attribute19 => c_positions_rec.attribute19,
1594 p_attribute20 => c_positions_rec.attribute20,
1595 p_attribute_category => c_positions_rec.attribute_category,
1596 p_name => c_positions_rec.name);
1597
1598 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1599 raise FND_API.G_EXC_ERROR;
1600 end if;
1601
1602 -- Consolidate Position Attribute Assignments
1603
1604 for c_posassign_attr_rec in c_posassign_attr (c_positions_rec.position_id) loop
1605
1606 if c_posassign_attr_rec.worksheet_id is null then
1607 l_worksheet_id := null;
1608 else
1609 l_worksheet_id := g_global_worksheet_id;
1610 end if;
1611
1612 for c_re_attr_rec in c_re_attr (c_posassign_attr_rec.attribute_id) loop
1613 l_attribute_id := c_re_attr_rec.attribute_id;
1614 end loop;
1615
1616 if c_posassign_attr_rec.attribute_value_id is not null then
1617 begin
1618
1619 for c_re_attrval_rec in c_re_attrval (c_posassign_attr_rec.attribute_value_id) loop
1620 l_attribute_value_id := c_re_attrval_rec.attribute_value_id;
1621 end loop;
1622
1623 end;
1624 else
1625 l_attribute_value_id := null;
1626 end if;
1627
1628 for c_posassign_seq_rec in c_posassign_seq loop
1629 l_position_assignment_id := c_posassign_seq_rec.seq;
1630 end loop;
1631
1632 PSB_POSITION_ASSIGNMENTS_PVT.Insert_Row
1633 (p_api_version => 1.0,
1634 p_return_status => l_return_status,
1635 p_msg_count => l_msg_count,
1636 p_msg_data => l_msg_data,
1637 p_rowid => l_rowid,
1638 p_position_assignment_id => l_position_assignment_id,
1639 p_data_extract_id => g_global_data_extract_id,
1640 p_worksheet_id => l_worksheet_id,
1641 p_position_id => l_position_id,
1642 p_assignment_type => 'ATTRIBUTE',
1643 p_attribute_id => l_attribute_id,
1644 p_attribute_value_id => l_attribute_value_id,
1645 p_attribute_value => c_posassign_attr_rec.attribute_value,
1646 p_pay_element_id => null,
1647 p_pay_element_option_id => null,
1648 p_effective_start_date => c_posassign_attr_rec.effective_start_date,
1649 p_effective_end_date => c_posassign_attr_rec.effective_end_date,
1650 p_element_value_type => null,
1651 p_element_value => null,
1652 p_currency_code => null,
1653 p_pay_basis => null,
1654 p_employee_id => null,
1655 p_primary_employee_flag => null,
1656 p_global_default_flag => null,
1657 p_assignment_default_rule_id => null,
1658 p_modify_flag => null);
1659
1660 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1661 raise FND_API.G_EXC_ERROR;
1662 end if;
1663
1664 end loop;
1665
1666 -- Consolidate Position Element Assignments
1667
1668 for c_posassign_elem_rec in c_posassign_elem (c_positions_rec.position_id) loop
1669
1670 if c_posassign_elem_rec.worksheet_id is null then
1671 l_worksheet_id := null;
1672 else
1673 l_worksheet_id := g_global_worksheet_id;
1674 end if;
1675
1676 for c_re_elem_rec in c_re_elem (c_posassign_elem_rec.pay_element_id) loop
1677 l_pay_element_id := c_re_elem_rec.pay_element_id;
1678 end loop;
1679
1680 if c_posassign_elem_rec.pay_element_option_id is not null then
1681 begin
1682
1683 for c_re_elemopt_rec in c_re_elemopt (l_pay_element_id, c_posassign_elem_rec.pay_element_option_id) loop
1684 l_pay_element_option_id := c_re_elemopt_rec.pay_element_option_id;
1685 end loop;
1686
1687 end;
1688 else
1689 l_pay_element_option_id := null;
1690 end if;
1691
1692 for c_posassign_seq_rec in c_posassign_seq loop
1693 l_position_assignment_id := c_posassign_seq_rec.seq;
1694 end loop;
1695
1696 PSB_POSITION_ASSIGNMENTS_PVT.Insert_Row
1697 (p_api_version => 1.0,
1698 p_return_status => l_return_status,
1699 p_msg_count => l_msg_count,
1700 p_msg_data => l_msg_data,
1701 p_rowid => l_rowid,
1702 p_position_assignment_id => l_position_assignment_id,
1703 p_data_extract_id => g_global_data_extract_id,
1704 p_worksheet_id => l_worksheet_id,
1705 p_position_id => l_position_id,
1706 p_assignment_type => 'ELEMENT',
1707 p_attribute_id => null,
1708 p_attribute_value_id => null,
1709 p_attribute_value => null,
1710 p_pay_element_id => l_pay_element_id,
1711 p_pay_element_option_id => l_pay_element_option_id,
1712 p_effective_start_date => c_posassign_elem_rec.effective_start_date,
1713 p_effective_end_date => c_posassign_elem_rec.effective_end_date,
1714 p_element_value_type => c_posassign_elem_rec.element_value_type,
1715 p_element_value => c_posassign_elem_rec.element_value,
1716 p_currency_code => c_posassign_elem_rec.currency_code,
1717 p_pay_basis => c_posassign_elem_rec.pay_basis,
1718 p_employee_id => null,
1719 p_primary_employee_flag => null,
1720 p_global_default_flag => null,
1721 p_assignment_default_rule_id => null,
1722 p_modify_flag => null);
1723
1724 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1725 raise FND_API.G_EXC_ERROR;
1726 end if;
1727
1728 end loop;
1729
1730 -- Consolidate Position Employee Assignments
1731
1732 for c_posassign_emp_rec in c_posassign_emp (c_positions_rec.position_id) loop
1733
1734 if c_posassign_emp_rec.worksheet_id is null then
1735 l_worksheet_id := null;
1736 else
1737 l_worksheet_id := g_global_worksheet_id;
1738 end if;
1739
1740 for c_re_emp_rec in c_re_emp (c_posassign_emp_rec.employee_id) loop
1741 l_employee_id := c_re_emp_rec.employee_id;
1742 end loop;
1743
1744 for c_posassign_seq_rec in c_posassign_seq loop
1745 l_position_assignment_id := c_posassign_seq_rec.seq;
1746 end loop;
1747
1748 PSB_POSITION_ASSIGNMENTS_PVT.Insert_Row
1749 (p_api_version => 1.0,
1750 p_return_status => l_return_status,
1751 p_msg_count => l_msg_count,
1752 p_msg_data => l_msg_data,
1753 p_rowid => l_rowid,
1754 p_position_assignment_id => l_position_assignment_id,
1755 p_data_extract_id => g_global_data_extract_id,
1756 p_worksheet_id => l_worksheet_id,
1757 p_position_id => l_position_id,
1758 p_assignment_type => 'EMPLOYEE',
1759 p_attribute_id => null,
1760 p_attribute_value_id => null,
1761 p_attribute_value => null,
1762 p_pay_element_id => null,
1763 p_pay_element_option_id => null,
1764 p_effective_start_date => c_posassign_emp_rec.effective_start_date,
1765 p_effective_end_date => c_posassign_emp_rec.effective_end_date,
1766 p_element_value_type => null,
1767 p_element_value => null,
1768 p_currency_code => null,
1769 p_pay_basis => null,
1770 p_employee_id => l_employee_id,
1771 p_primary_employee_flag => c_posassign_emp_rec.primary_employee_flag,
1772 p_global_default_flag => null,
1773 p_assignment_default_rule_id => null,
1774 p_modify_flag => null);
1775
1776 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1777 raise FND_API.G_EXC_ERROR;
1778 end if;
1779
1780 end loop;
1781
1782 -- Consolidate Position Salary Distributions
1783
1784 for c_position_distr_rec in c_position_distr (c_positions_rec.position_id) loop
1785
1786 if c_position_distr_rec.worksheet_id is null then
1787 l_worksheet_id := null;
1788 else
1789 l_worksheet_id := g_global_worksheet_id;
1790 end if;
1791
1792 for c_posdistr_seq_rec in c_posdistr_seq loop
1793 l_distribution_id := c_posdistr_seq_rec.seq;
1794 end loop;
1795
1796 PSB_POSITION_PAY_DISTR_PVT.Insert_Row
1797 (p_api_version => 1.0,
1798 p_return_status => l_return_status,
1799 p_msg_count => l_msg_count,
1800 p_msg_data => l_msg_data,
1801 p_rowid => l_rowid,
1802 p_distribution_id => l_distribution_id,
1803 p_worksheet_id => l_worksheet_id,
1804 p_position_id => l_position_id,
1805 p_data_extract_id => g_global_data_extract_id,
1806 p_effective_start_date => c_position_distr_rec.effective_start_date,
1807 p_effective_end_date => c_position_distr_rec.effective_end_date,
1808 p_chart_of_accounts_id => c_position_distr_rec.chart_of_accounts_id,
1809 p_code_combination_id => c_position_distr_rec.code_combination_id,
1810 p_distribution_percent => c_position_distr_rec.distribution_percent,
1811 p_global_default_flag => null,
1812 p_distribution_default_rule_id => null,
1813 p_project_id => c_position_distr_rec.project_id,
1814 p_task_id => c_position_distr_rec.task_id,
1815 p_award_id => c_position_distr_rec.award_id,
1816 p_expenditure_type => c_position_distr_rec.expenditure_type,
1817 p_expenditure_organization_id => c_position_distr_rec.expenditure_organization_id,
1818 p_description => c_position_distr_rec.description);
1819
1820 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1821 raise FND_API.G_EXC_ERROR;
1822 end if;
1823
1824 end loop;
1825
1826 end;
1827 end if;
1828
1829 end loop;
1830
1831
1832 -- Initialize API return status to success
1833
1834 p_return_status := FND_API.G_RET_STS_SUCCESS;
1835
1836
1837 EXCEPTION
1838
1839 when FND_API.G_EXC_ERROR then
1840 p_return_status := FND_API.G_RET_STS_ERROR;
1841
1842 when FND_API.G_EXC_UNEXPECTED_ERROR then
1843 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1844
1845 when OTHERS then
1846 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1847
1848 END Consolidate_Positions;
1849
1850 /* ----------------------------------------------------------------------- */
1851
1852 PROCEDURE Consolidate_Service_Packages
1853 ( p_return_status OUT NOCOPY VARCHAR2,
1854 p_local_worksheet_id IN NUMBER
1855 ) IS
1856
1857 l_service_package_id NUMBER;
1858
1859 l_sp_exists VARCHAR2(1);
1860
1861 l_return_status VARCHAR2(1);
1862
1863 cursor c_sp is
1864 select *
1865 from PSB_SERVICE_PACKAGES
1866 where global_worksheet_id = p_local_worksheet_id;
1867
1868 cursor c_sp_exists (name VARCHAR2) is
1869 select service_package_id
1870 from PSB_SERVICE_PACKAGES
1871 where short_name = name
1872 and global_worksheet_id = g_global_worksheet_id;
1873
1874 cursor c_sp_seq is
1875 select PSB_SERVICE_PACKAGES_S.NEXTVAL seq
1876 from dual;
1877
1878 BEGIN
1879
1880 -- Loop for all service packages in the local worksheet
1881
1882 for c_sp_rec in c_sp loop
1883
1884 l_sp_exists := FND_API.G_FALSE;
1885
1886 for c_sp_exists_rec in c_sp_exists (c_sp_rec.short_name) loop
1887 l_sp_exists := FND_API.G_TRUE;
1888 end loop;
1889
1890 if not FND_API.to_Boolean(l_sp_exists) then
1891 begin
1892
1893 for c_sp_seq_rec in c_sp_seq loop
1894 l_service_package_id := c_sp_seq_rec.seq;
1895 end loop;
1896
1897 insert into PSB_SERVICE_PACKAGES
1898 (service_package_id, global_worksheet_id, base_service_package, name,
1899 short_name, description, priority, last_update_date, last_updated_by,
1900 last_update_login, created_by, creation_date)
1901 values (l_service_package_id, g_global_worksheet_id, c_sp_rec.base_service_package, c_sp_rec.name,
1902 c_sp_rec.short_name, c_sp_rec.description, c_sp_rec.priority, sysdate, g_userid,
1903 g_loginid, g_userid, sysdate);
1904
1905 end;
1906 end if;
1907
1908 end loop;
1909
1910
1911 -- Initialize API return status to success
1912
1913 p_return_status := FND_API.G_RET_STS_SUCCESS;
1914
1915 EXCEPTION
1916
1917 when FND_API.G_EXC_ERROR then
1918 p_return_status := FND_API.G_RET_STS_ERROR;
1919
1920 when FND_API.G_EXC_UNEXPECTED_ERROR then
1921 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1922
1923 when OTHERS then
1924 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1925
1926 END Consolidate_Service_Packages;
1927
1928 /* ----------------------------------------------------------------------- */
1929
1930 PROCEDURE Consolidate_Local_Worksheets
1931 ( p_return_status OUT NOCOPY VARCHAR2,
1932 p_local_worksheet_id IN NUMBER,
1933 p_global_worksheet_id IN NUMBER
1934 ) IS
1935
1936 l_period_amount PSB_WS_ACCT1.g_prdamt_tbl_type;
1937 l_period_fte PSB_WS_ACCT1.g_prdamt_tbl_type;
1938
1939 l_account_line_id NUMBER;
1940 l_position_line_id NUMBER;
1941 l_fte_line_id NUMBER;
1942 l_element_line_id NUMBER;
1943
1944 l_service_package_id NUMBER;
1945 l_pay_element_id NUMBER;
1946 l_position_id NUMBER;
1947
1948 l_position_exists BOOLEAN;
1949
1950 l_return_status VARCHAR2(1);
1951
1952 cursor c_wal_nps is
1953 select *
1954 from PSB_WS_ACCOUNT_LINES a
1955 where position_line_id is null
1956 and exists
1957 (select 1
1958 from PSB_WS_LINES b
1959 where b.account_line_id = a.account_line_id
1960 and b.worksheet_id = p_local_worksheet_id)
1961 order by a.code_combination_id, a.current_stage_seq;
1962
1963 cursor c_wal_ps (poslineid NUMBER) is
1964 select *
1965 from PSB_WS_ACCOUNT_LINES
1966 where position_line_id = poslineid
1967 order by position_line_id, current_stage_seq;
1968
1969 cursor c_wpl is
1970 select *
1971 from PSB_WS_POSITION_LINES a
1972 where exists
1973 (select 1
1974 from PSB_WS_LINES_POSITIONS b
1975 where b.position_line_id = a.position_line_id
1976 and b.worksheet_id = p_local_worksheet_id);
1977
1978 cursor c_wfl (poslineid NUMBER) is
1979 select *
1980 from PSB_WS_FTE_LINES
1981 where position_line_id = poslineid
1982 order by position_line_id, current_stage_seq;
1983
1984 cursor c_wel (poslineid NUMBER) is
1985 select *
1986 from PSB_WS_ELEMENT_LINES
1987 where position_line_id = poslineid
1988 order by position_line_id, current_stage_seq;
1989
1990 cursor c_re_elem (elemid NUMBER) is
1991 select a.pay_element_id
1992 from PSB_PAY_ELEMENTS a,
1993 PSB_PAY_ELEMENTS b
1994 where a.name = b.name
1995 and a.data_extract_id = g_global_data_extract_id
1996 and b.pay_element_id = elemid;
1997
1998 cursor c_re_pos (posid NUMBER) is
1999 select a.position_id
2000 from PSB_POSITIONS a,
2001 PSB_POSITIONS b
2002 where (b.hr_employee_id is null or a.hr_employee_id = b.hr_employee_id)
2003 and a.name = b.name
2004 and a.data_extract_id = g_global_data_extract_id
2005 and b.position_id = posid;
2006
2007 cursor c_position_exists (posid NUMBER) is
2008 select 'Exists'
2009 from dual
2010 where exists
2011 (select 1
2012 from PSB_WS_POSITION_LINES a,
2013 PSB_WS_LINES_POSITIONS b
2014 where a.position_line_id = b.position_line_id
2015 and a.position_id = posid
2016 and b.worksheet_id = p_global_worksheet_id);
2017
2018 cursor c_re_sp (spid NUMBER) is
2019 select a.service_package_id
2020 from PSB_SERVICE_PACKAGES a,
2021 PSB_SERVICE_PACKAGES b
2022 where a.short_name = b.short_name
2023 and a.global_worksheet_id = g_global_worksheet_id
2024 and b.service_package_id = spid;
2025
2026 BEGIN
2027
2028 -- First Consolidate all non-Position Account Lines
2029
2030 for c_wal_rec in c_wal_nps loop
2031
2032 l_period_amount(1) := c_wal_rec.period1_amount; l_period_amount(2) := c_wal_rec.period2_amount;
2033 l_period_amount(3) := c_wal_rec.period3_amount; l_period_amount(4) := c_wal_rec.period4_amount;
2034 l_period_amount(5) := c_wal_rec.period5_amount; l_period_amount(6) := c_wal_rec.period6_amount;
2035 l_period_amount(7) := c_wal_rec.period7_amount; l_period_amount(8) := c_wal_rec.period8_amount;
2036 l_period_amount(9) := c_wal_rec.period9_amount; l_period_amount(10) := c_wal_rec.period10_amount;
2037 l_period_amount(11) := c_wal_rec.period11_amount; l_period_amount(12) := c_wal_rec.period12_amount;
2038 l_period_amount(13) := c_wal_rec.period13_amount; l_period_amount(14) := c_wal_rec.period14_amount;
2039 l_period_amount(15) := c_wal_rec.period15_amount; l_period_amount(16) := c_wal_rec.period16_amount;
2040 l_period_amount(17) := c_wal_rec.period17_amount; l_period_amount(18) := c_wal_rec.period18_amount;
2041 l_period_amount(19) := c_wal_rec.period19_amount; l_period_amount(20) := c_wal_rec.period20_amount;
2042 l_period_amount(21) := c_wal_rec.period21_amount; l_period_amount(22) := c_wal_rec.period22_amount;
2043 l_period_amount(23) := c_wal_rec.period23_amount; l_period_amount(24) := c_wal_rec.period24_amount;
2044 l_period_amount(25) := c_wal_rec.period25_amount; l_period_amount(26) := c_wal_rec.period26_amount;
2045 l_period_amount(27) := c_wal_rec.period27_amount; l_period_amount(28) := c_wal_rec.period28_amount;
2046 l_period_amount(29) := c_wal_rec.period29_amount; l_period_amount(30) := c_wal_rec.period30_amount;
2047 l_period_amount(31) := c_wal_rec.period31_amount; l_period_amount(32) := c_wal_rec.period32_amount;
2048 l_period_amount(33) := c_wal_rec.period33_amount; l_period_amount(34) := c_wal_rec.period34_amount;
2049 l_period_amount(35) := c_wal_rec.period35_amount; l_period_amount(36) := c_wal_rec.period36_amount;
2050 l_period_amount(37) := c_wal_rec.period37_amount; l_period_amount(38) := c_wal_rec.period38_amount;
2051 l_period_amount(39) := c_wal_rec.period39_amount; l_period_amount(40) := c_wal_rec.period40_amount;
2052 l_period_amount(41) := c_wal_rec.period41_amount; l_period_amount(42) := c_wal_rec.period42_amount;
2053 l_period_amount(43) := c_wal_rec.period43_amount; l_period_amount(44) := c_wal_rec.period44_amount;
2054 l_period_amount(45) := c_wal_rec.period45_amount; l_period_amount(46) := c_wal_rec.period46_amount;
2055 l_period_amount(47) := c_wal_rec.period47_amount; l_period_amount(48) := c_wal_rec.period48_amount;
2056 l_period_amount(49) := c_wal_rec.period49_amount; l_period_amount(50) := c_wal_rec.period50_amount;
2057 l_period_amount(51) := c_wal_rec.period51_amount; l_period_amount(52) := c_wal_rec.period52_amount;
2058 l_period_amount(53) := c_wal_rec.period53_amount; l_period_amount(54) := c_wal_rec.period54_amount;
2059 l_period_amount(55) := c_wal_rec.period55_amount; l_period_amount(56) := c_wal_rec.period56_amount;
2060 l_period_amount(57) := c_wal_rec.period57_amount; l_period_amount(58) := c_wal_rec.period58_amount;
2061 l_period_amount(59) := c_wal_rec.period59_amount; l_period_amount(60) := c_wal_rec.period60_amount;
2062
2063 for c_re_sp_rec in c_re_sp (c_wal_rec.service_package_id) loop
2064 l_service_package_id := c_re_sp_rec.service_package_id;
2065 end loop;
2066
2067 PSB_WS_ACCT1.Create_Account_Dist
2068 (p_api_version => 1.0,
2069 p_return_status => l_return_status,
2070 p_account_line_id => l_account_line_id,
2071 p_worksheet_id => g_global_worksheet_id,
2072 p_check_spal_exists => FND_API.G_FALSE,
2073 p_gl_cutoff_period => null,
2074 p_allocrule_set_id => null,
2075 p_budget_calendar_id => null,
2076 p_rounding_factor => null,
2077 p_stage_set_id => c_wal_rec.stage_set_id,
2078 p_budget_year_id => c_wal_rec.budget_year_id,
2079 p_budget_group_id => c_wal_rec.budget_group_id,
2080 p_ccid => c_wal_rec.code_combination_id,
2081 p_flex_code => null,
2082 p_template_id => c_wal_rec.template_id,
2083 p_currency_code => c_wal_rec.currency_code,
2084 p_balance_type => c_wal_rec.balance_type,
2085 p_ytd_amount => c_wal_rec.ytd_amount,
2086 p_period_amount => l_period_amount,
2087 p_service_package_id => l_service_package_id,
2088 p_start_stage_seq => c_wal_rec.start_stage_seq,
2089 p_current_stage_seq => c_wal_rec.current_stage_seq,
2090 p_end_stage_seq => c_wal_rec.end_stage_seq,
2091 p_copy_of_account_line_id => null);
2092
2093 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2094 raise FND_API.G_EXC_ERROR;
2095 end if;
2096
2097 end loop;
2098
2099 -- Now Consolidate all Position Lines
2100
2101 for c_wpl_rec in c_wpl loop
2102
2103 for c_re_pos_rec in c_re_pos (c_wpl_rec.position_id) loop
2104 l_position_id := c_re_pos_rec.position_id;
2105 end loop;
2106
2107 l_position_exists := FALSE;
2108
2109 for c_position_exists_rec in c_position_exists (l_position_id) loop
2110 l_position_exists := TRUE;
2111 end loop;
2112
2113 if not (l_position_exists) then
2114 begin
2115
2116 PSB_WS_POS1.Create_Position_Lines
2117 (p_api_version => 1.0,
2118 p_return_status => l_return_status,
2119 p_position_line_id => l_position_line_id,
2120 p_worksheet_id => g_global_worksheet_id,
2121 p_position_id => l_position_id,
2122 p_budget_group_id => c_wpl_rec.budget_group_id,
2123 p_copy_of_position_line_id => null);
2124
2125 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2126 raise FND_API.G_EXC_ERROR;
2127 end if;
2128
2129 for c_wfl_rec in c_wfl (c_wpl_rec.position_line_id) loop
2130
2131 l_period_fte(1) := c_wfl_rec.period1_fte; l_period_fte(2) := c_wfl_rec.period2_fte;
2132 l_period_fte(3) := c_wfl_rec.period3_fte; l_period_fte(4) := c_wfl_rec.period4_fte;
2133 l_period_fte(5) := c_wfl_rec.period5_fte; l_period_fte(6) := c_wfl_rec.period6_fte;
2134 l_period_fte(7) := c_wfl_rec.period7_fte; l_period_fte(8) := c_wfl_rec.period8_fte;
2135 l_period_fte(9) := c_wfl_rec.period9_fte; l_period_fte(10) := c_wfl_rec.period10_fte;
2136 l_period_fte(11) := c_wfl_rec.period11_fte; l_period_fte(12) := c_wfl_rec.period12_fte;
2137 l_period_fte(13) := c_wfl_rec.period13_fte; l_period_fte(14) := c_wfl_rec.period14_fte;
2138 l_period_fte(15) := c_wfl_rec.period15_fte; l_period_fte(16) := c_wfl_rec.period16_fte;
2139 l_period_fte(17) := c_wfl_rec.period17_fte; l_period_fte(18) := c_wfl_rec.period18_fte;
2140 l_period_fte(19) := c_wfl_rec.period19_fte; l_period_fte(20) := c_wfl_rec.period20_fte;
2141 l_period_fte(21) := c_wfl_rec.period21_fte; l_period_fte(22) := c_wfl_rec.period22_fte;
2142 l_period_fte(23) := c_wfl_rec.period23_fte; l_period_fte(24) := c_wfl_rec.period24_fte;
2143 l_period_fte(25) := c_wfl_rec.period25_fte; l_period_fte(26) := c_wfl_rec.period26_fte;
2144 l_period_fte(27) := c_wfl_rec.period27_fte; l_period_fte(28) := c_wfl_rec.period28_fte;
2145 l_period_fte(29) := c_wfl_rec.period29_fte; l_period_fte(30) := c_wfl_rec.period30_fte;
2146 l_period_fte(31) := c_wfl_rec.period31_fte; l_period_fte(32) := c_wfl_rec.period32_fte;
2147 l_period_fte(33) := c_wfl_rec.period33_fte; l_period_fte(34) := c_wfl_rec.period34_fte;
2148 l_period_fte(35) := c_wfl_rec.period35_fte; l_period_fte(36) := c_wfl_rec.period36_fte;
2149 l_period_fte(37) := c_wfl_rec.period37_fte; l_period_fte(38) := c_wfl_rec.period38_fte;
2150 l_period_fte(39) := c_wfl_rec.period39_fte; l_period_fte(40) := c_wfl_rec.period40_fte;
2151 l_period_fte(41) := c_wfl_rec.period41_fte; l_period_fte(42) := c_wfl_rec.period42_fte;
2152 l_period_fte(43) := c_wfl_rec.period43_fte; l_period_fte(44) := c_wfl_rec.period44_fte;
2153 l_period_fte(45) := c_wfl_rec.period45_fte; l_period_fte(46) := c_wfl_rec.period46_fte;
2154 l_period_fte(47) := c_wfl_rec.period47_fte; l_period_fte(48) := c_wfl_rec.period48_fte;
2155 l_period_fte(49) := c_wfl_rec.period49_fte; l_period_fte(50) := c_wfl_rec.period50_fte;
2156 l_period_fte(51) := c_wfl_rec.period51_fte; l_period_fte(52) := c_wfl_rec.period52_fte;
2157 l_period_fte(53) := c_wfl_rec.period53_fte; l_period_fte(54) := c_wfl_rec.period54_fte;
2158 l_period_fte(55) := c_wfl_rec.period55_fte; l_period_fte(56) := c_wfl_rec.period56_fte;
2159 l_period_fte(57) := c_wfl_rec.period57_fte; l_period_fte(58) := c_wfl_rec.period58_fte;
2160 l_period_fte(59) := c_wfl_rec.period59_fte; l_period_fte(60) := c_wfl_rec.period60_fte;
2161
2162 for c_re_sp_rec in c_re_sp (c_wfl_rec.service_package_id) loop
2163 l_service_package_id := c_re_sp_rec.service_package_id;
2164 end loop;
2165
2166 PSB_WS_POS1.Create_FTE_Lines
2167 (p_api_version => 1.0,
2168 p_return_status => l_return_status,
2169 p_fte_line_id => l_fte_line_id,
2170 p_check_spfl_exists => FND_API.G_FALSE,
2171 p_worksheet_id => g_global_worksheet_id,
2172 p_position_line_id => l_position_line_id,
2173 p_budget_year_id => c_wfl_rec.budget_year_id,
2174 p_annual_fte => c_wfl_rec.annual_fte,
2175 p_service_package_id => l_service_package_id,
2176 p_stage_set_id => c_wfl_rec.stage_set_id,
2177 p_start_stage_seq => c_wfl_rec.start_stage_seq,
2178 p_current_stage_seq => c_wfl_rec.current_stage_seq,
2179 p_end_stage_seq => c_wfl_rec.end_stage_seq,
2180 p_period_fte => l_period_fte);
2181
2182 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2183 raise FND_API.G_EXC_ERROR;
2184 end if;
2185
2186 end loop;
2187
2188 for c_wel_rec in c_wel (c_wpl_rec.position_line_id) loop
2189
2190 for c_re_sp_rec in c_re_sp (c_wel_rec.service_package_id) loop
2191 l_service_package_id := c_re_sp_rec.service_package_id;
2192 end loop;
2193
2194 for c_re_elem_rec in c_re_elem (c_wel_rec.pay_element_id) loop
2195 l_pay_element_id := c_re_elem_rec.pay_element_id;
2196 end loop;
2197
2198 PSB_WS_POS1.Create_Element_Lines
2199 (p_api_version => 1.0,
2200 p_return_status => l_return_status,
2201 p_element_line_id => l_element_line_id,
2202 p_check_spel_exists => FND_API.G_FALSE,
2203 p_position_line_id => l_position_line_id,
2204 p_budget_year_id => c_wel_rec.budget_year_id,
2205 p_pay_element_id => l_pay_element_id,
2206 p_currency_code => c_wel_rec.currency_code,
2207 p_element_cost => c_wel_rec.element_cost,
2208 p_element_set_id => c_wel_rec.element_set_id,
2209 p_service_package_id => l_service_package_id,
2210 p_stage_set_id => c_wel_rec.stage_set_id,
2211 p_start_stage_seq => c_wel_rec.start_stage_seq,
2212 p_current_stage_seq => c_wel_rec.current_stage_seq,
2213 p_end_stage_seq => c_wel_rec.end_stage_seq);
2214
2215 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2216 raise FND_API.G_EXC_ERROR;
2217 end if;
2218
2219 end loop;
2220
2221 for c_wal_rec in c_wal_ps (c_wpl_rec.position_line_id) loop
2222
2223 l_period_amount(1) := c_wal_rec.period1_amount; l_period_amount(2) := c_wal_rec.period2_amount;
2224 l_period_amount(3) := c_wal_rec.period3_amount; l_period_amount(4) := c_wal_rec.period4_amount;
2225 l_period_amount(5) := c_wal_rec.period5_amount; l_period_amount(6) := c_wal_rec.period6_amount;
2226 l_period_amount(7) := c_wal_rec.period7_amount; l_period_amount(8) := c_wal_rec.period8_amount;
2227 l_period_amount(9) := c_wal_rec.period9_amount; l_period_amount(10) := c_wal_rec.period10_amount;
2228 l_period_amount(11) := c_wal_rec.period11_amount; l_period_amount(12) := c_wal_rec.period12_amount;
2229 l_period_amount(13) := c_wal_rec.period13_amount; l_period_amount(14) := c_wal_rec.period14_amount;
2230 l_period_amount(15) := c_wal_rec.period15_amount; l_period_amount(16) := c_wal_rec.period16_amount;
2231 l_period_amount(17) := c_wal_rec.period17_amount; l_period_amount(18) := c_wal_rec.period18_amount;
2232 l_period_amount(19) := c_wal_rec.period19_amount; l_period_amount(20) := c_wal_rec.period20_amount;
2233 l_period_amount(21) := c_wal_rec.period21_amount; l_period_amount(22) := c_wal_rec.period22_amount;
2234 l_period_amount(23) := c_wal_rec.period23_amount; l_period_amount(24) := c_wal_rec.period24_amount;
2235 l_period_amount(25) := c_wal_rec.period25_amount; l_period_amount(26) := c_wal_rec.period26_amount;
2236 l_period_amount(27) := c_wal_rec.period27_amount; l_period_amount(28) := c_wal_rec.period28_amount;
2237 l_period_amount(29) := c_wal_rec.period29_amount; l_period_amount(30) := c_wal_rec.period30_amount;
2238 l_period_amount(31) := c_wal_rec.period31_amount; l_period_amount(32) := c_wal_rec.period32_amount;
2239 l_period_amount(33) := c_wal_rec.period33_amount; l_period_amount(34) := c_wal_rec.period34_amount;
2240 l_period_amount(35) := c_wal_rec.period35_amount; l_period_amount(36) := c_wal_rec.period36_amount;
2241 l_period_amount(37) := c_wal_rec.period37_amount; l_period_amount(38) := c_wal_rec.period38_amount;
2242 l_period_amount(39) := c_wal_rec.period39_amount; l_period_amount(40) := c_wal_rec.period40_amount;
2243 l_period_amount(41) := c_wal_rec.period41_amount; l_period_amount(42) := c_wal_rec.period42_amount;
2244 l_period_amount(43) := c_wal_rec.period43_amount; l_period_amount(44) := c_wal_rec.period44_amount;
2245 l_period_amount(45) := c_wal_rec.period45_amount; l_period_amount(46) := c_wal_rec.period46_amount;
2246 l_period_amount(47) := c_wal_rec.period47_amount; l_period_amount(48) := c_wal_rec.period48_amount;
2247 l_period_amount(49) := c_wal_rec.period49_amount; l_period_amount(50) := c_wal_rec.period50_amount;
2248 l_period_amount(51) := c_wal_rec.period51_amount; l_period_amount(52) := c_wal_rec.period52_amount;
2249 l_period_amount(53) := c_wal_rec.period53_amount; l_period_amount(54) := c_wal_rec.period54_amount;
2250 l_period_amount(55) := c_wal_rec.period55_amount; l_period_amount(56) := c_wal_rec.period56_amount;
2251 l_period_amount(57) := c_wal_rec.period57_amount; l_period_amount(58) := c_wal_rec.period58_amount;
2252 l_period_amount(59) := c_wal_rec.period59_amount; l_period_amount(60) := c_wal_rec.period60_amount;
2253
2254 for c_re_sp_rec in c_re_sp (c_wal_rec.service_package_id) loop
2255 l_service_package_id := c_re_sp_rec.service_package_id;
2256 end loop;
2257
2258 PSB_WS_ACCT1.Create_Account_Dist
2259 (p_api_version => 1.0,
2260 p_return_status => l_return_status,
2261 p_account_line_id => l_account_line_id,
2262 p_worksheet_id => g_global_worksheet_id,
2263 p_check_spal_exists => FND_API.G_FALSE,
2264 p_gl_cutoff_period => null,
2265 p_allocrule_set_id => null,
2266 p_budget_calendar_id => null,
2267 p_rounding_factor => null,
2268 p_stage_set_id => c_wal_rec.stage_set_id,
2269 p_budget_year_id => c_wal_rec.budget_year_id,
2270 p_budget_group_id => c_wal_rec.budget_group_id,
2271 p_ccid => c_wal_rec.code_combination_id,
2272 p_currency_code => c_wal_rec.currency_code,
2273 p_balance_type => c_wal_rec.balance_type,
2274 p_ytd_amount => c_wal_rec.ytd_amount,
2275 p_period_amount => l_period_amount,
2276 p_position_line_id => l_position_line_id,
2277 p_element_set_id => c_wal_rec.element_set_id,
2278 p_salary_account_line => c_wal_rec.salary_account_line,
2279 p_service_package_id => l_service_package_id,
2280 p_start_stage_seq => c_wal_rec.start_stage_seq,
2281 p_current_stage_seq => c_wal_rec.current_stage_seq,
2282 p_end_stage_seq => c_wal_rec.end_stage_seq);
2283
2284 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2285 raise FND_API.G_EXC_ERROR;
2286 end if;
2287
2288 end loop;
2289
2290 end;
2291 end if;
2292
2293 end loop;
2294
2295
2296 -- Initialize API return status to success
2297
2298 p_return_status := FND_API.G_RET_STS_SUCCESS;
2299
2300
2301 EXCEPTION
2302
2303 when FND_API.G_EXC_ERROR then
2304 p_return_status := FND_API.G_RET_STS_ERROR;
2305
2306 when FND_API.G_EXC_UNEXPECTED_ERROR then
2307 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2308
2309 when OTHERS then
2310 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2311
2312 END Consolidate_Local_Worksheets;
2313
2314 /* ----------------------------------------------------------------------- */
2315
2316 PROCEDURE Consolidate_Worksheets
2317 ( p_api_version IN NUMBER,
2318 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_NONE,
2319 p_return_status OUT NOCOPY VARCHAR2,
2320 p_global_worksheet_id IN NUMBER
2321 ) IS
2322
2323 l_api_name CONSTANT VARCHAR2(30) := 'Consolidate_Worksheets';
2324 l_api_version CONSTANT NUMBER := 1.0;
2325
2326 l_msg_count NUMBER;
2327 l_msg_data VARCHAR2(2000);
2328
2329 l_return_status VARCHAR2(1);
2330
2331 cursor c_Global_WS is
2332 select b.data_extract_id,
2333 a.business_group_id
2334 from PSB_DATA_EXTRACTS a,
2335 PSB_WORKSHEETS b
2336 where a.data_extract_id = b.data_extract_id
2337 and b.worksheet_id = p_global_worksheet_id;
2338
2339 cursor c_Local_WS_Pos is
2340 select c.local_worksheet_id,
2341 b.data_extract_id,
2342 a.business_group_id
2343 from PSB_DATA_EXTRACTS a,
2344 PSB_WORKSHEETS b,
2345 PSB_WS_CONSOLIDATION_DETAILS c
2346 where a.data_extract_id = b.data_extract_id
2347 and b.worksheet_id = c.local_worksheet_id
2348 and c.global_worksheet_id = p_global_worksheet_id;
2349
2350 cursor c_Local_WS is
2351 select local_worksheet_id
2352 from PSB_WS_CONSOLIDATION_DETAILS
2353 where global_worksheet_id = p_global_worksheet_id;
2354
2355 BEGIN
2356
2357 -- Standard call to check for call compatibility.
2358
2359 if not FND_API.Compatible_API_Call (l_api_version,
2360 p_api_version,
2361 l_api_name,
2362 G_PKG_NAME)
2363 then
2364 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2365 end if;
2366
2367 PSB_WORKSHEET.Update_Worksheet
2368 (p_api_version => 1.0,
2369 p_return_status => l_return_status,
2370 p_worksheet_id => p_global_worksheet_id,
2371 p_ws_creation_complete => 'N');
2372
2373 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2374 raise FND_API.G_EXC_ERROR;
2375 end if;
2376
2377 commit work;
2378
2379 -- Lock Global Worksheet in Exclusive Mode
2380
2381 PSB_CONCURRENCY_CONTROL_PVT.Enforce_Concurrency_Control
2382 (p_api_version => 1.0,
2383 p_return_status => l_return_status,
2384 p_concurrency_class => 'WORKSHEET_CONSOLIDATION',
2385 p_concurrency_entity_name => 'WORKSHEET',
2386 p_concurrency_entity_id => p_global_worksheet_id);
2387
2388 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2389 raise FND_API.G_EXC_ERROR;
2390 end if;
2391
2392 for c_Global_WS_Rec in c_Global_WS loop
2393 g_global_data_extract_id := c_Global_WS_Rec.data_extract_id;
2394 g_global_business_group_id := c_Global_WS_Rec.business_group_id;
2395 end loop;
2396
2397 g_global_worksheet_id := p_global_worksheet_id;
2398 g_userid := FND_GLOBAL.USER_ID;
2399 g_loginid := FND_GLOBAL.LOGIN_ID;
2400
2401 -- Worksheet Consolidation is a 3-phase process. In the first phase the building
2402 -- blocks for all the worksheets (attributes, elements, employees, positions)
2403 -- are consolidated to eliminate duplication. In the second phase the service
2404 -- packages for all the local worksheets are consolidated. In the third phase all
2405 -- the local worksheets are consolidated
2406
2407 for c_Local_WS_Rec in c_Local_WS_Pos loop
2408
2409 PSB_CONCURRENCY_CONTROL_PVT.Enforce_Concurrency_Control
2410 (p_api_version => 1.0,
2411 p_return_status => l_return_status,
2412 p_concurrency_class => 'WORKSHEET_CONSOLIDATION',
2413 p_concurrency_entity_name => 'DATA_EXTRACT',
2414 p_concurrency_entity_id => c_Local_WS_Rec.data_extract_id);
2415
2416 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2417 raise FND_API.G_EXC_ERROR;
2418 end if;
2419
2420 Consolidate_Attributes
2421 (p_return_status => l_return_status,
2422 p_local_data_extract_id => c_Local_WS_Rec.data_extract_id,
2423 p_local_business_group_id => c_Local_WS_Rec.business_group_id);
2424
2425 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2426 raise FND_API.G_EXC_ERROR;
2427 end if;
2428
2429 Consolidate_Elements
2430 (p_return_status => l_return_status,
2431 p_local_worksheet_id => c_Local_WS_Rec.local_worksheet_id,
2432 p_local_data_extract_id => c_Local_WS_Rec.data_extract_id,
2433 p_local_business_group_id => c_Local_WS_Rec.business_group_id);
2434
2435 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2436 raise FND_API.G_EXC_ERROR;
2437 end if;
2438
2439 Consolidate_Employees
2440 (p_return_status => l_return_status,
2441 p_local_data_extract_id => c_Local_WS_Rec.data_extract_id,
2442 p_local_business_group_id => c_Local_WS_Rec.business_group_id);
2443
2444 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2445 raise FND_API.G_EXC_ERROR;
2446 end if;
2447
2448 Consolidate_Positions
2449 (p_return_status => l_return_status,
2450 p_local_worksheet_id => c_Local_WS_Rec.local_worksheet_id,
2451 p_local_data_extract_id => c_Local_WS_Rec.data_extract_id,
2452 p_local_business_group_id => c_Local_WS_Rec.business_group_id);
2453
2454 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2455 raise FND_API.G_EXC_ERROR;
2456 end if;
2457
2458 commit work;
2459
2460 end loop;
2461
2462 -- Create Positions from the consolidated Position Sets
2463
2464 PSB_Budget_Position_Pvt.Populate_Budget_Positions
2465 (p_api_version => 1.0,
2466 p_return_status => l_return_status,
2467 p_msg_count => l_msg_count,
2468 p_msg_data => l_msg_data,
2469 p_data_extract_id => g_global_data_extract_id);
2470
2471 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2472 raise FND_API.G_EXC_ERROR;
2473 end if;
2474
2475 -- In the second phase consolidate the service packages for all local worksheets
2476
2477 for c_Local_WS_Rec in c_Local_WS loop
2478
2479 -- Lock Local Worksheet in Exclusive Mode
2480
2481 PSB_CONCURRENCY_CONTROL_PVT.Enforce_Concurrency_Control
2482 (p_api_version => 1.0,
2483 p_return_status => l_return_status,
2484 p_concurrency_class => 'WORKSHEET_CONSOLIDATION',
2485 p_concurrency_entity_name => 'WORKSHEET',
2486 p_concurrency_entity_id => c_Local_WS_Rec.local_worksheet_id);
2487
2488 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2489 raise FND_API.G_EXC_ERROR;
2490 end if;
2491
2492 Consolidate_Service_Packages
2493 (p_return_status => l_return_status,
2494 p_local_worksheet_id => c_Local_WS_Rec.local_worksheet_id);
2495
2496 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2497 raise FND_API.G_EXC_ERROR;
2498 end if;
2499
2500 commit work;
2501
2502 end loop;
2503
2504 -- In the third phase consolidate all local worksheets
2505
2506 for c_Local_WS_Rec in c_Local_WS loop
2507
2508 -- Lock Local Worksheet in Exclusive Mode
2509
2510 PSB_CONCURRENCY_CONTROL_PVT.Enforce_Concurrency_Control
2511 (p_api_version => 1.0,
2512 p_return_status => l_return_status,
2513 p_concurrency_class => 'WORKSHEET_CONSOLIDATION',
2514 p_concurrency_entity_name => 'WORKSHEET',
2515 p_concurrency_entity_id => c_Local_WS_Rec.local_worksheet_id);
2516
2517 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2518 raise FND_API.G_EXC_ERROR;
2519 end if;
2520
2521 Consolidate_Local_Worksheets
2522 (p_return_status => l_return_status,
2523 p_local_worksheet_id => c_Local_WS_Rec.local_worksheet_id,
2524 p_global_worksheet_id => p_global_worksheet_id);
2525
2526 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2527 raise FND_API.G_EXC_ERROR;
2528 end if;
2529
2530 commit work;
2531
2532 end loop;
2533
2534 PSB_WORKSHEET.Update_Worksheet
2535 (p_api_version => 1.0,
2536 p_return_status => l_return_status,
2537 p_worksheet_id => p_global_worksheet_id,
2538 p_ws_creation_complete => 'Y');
2539
2540 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2541 raise FND_API.G_EXC_ERROR;
2542 end if;
2543
2544 -- Initialize API return status to success
2545
2546 p_return_status := FND_API.G_RET_STS_SUCCESS;
2547
2548
2549 EXCEPTION
2550
2551 when FND_API.G_EXC_ERROR then
2552 p_return_status := FND_API.G_RET_STS_ERROR;
2553
2554 when FND_API.G_EXC_UNEXPECTED_ERROR then
2555 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2556
2557 when OTHERS then
2558 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2559
2560 if FND_MSG_PUB.Check_Msg_Level
2561 (p_message_level => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) then
2562 FND_MSG_PUB.Add_Exc_Msg
2563 (p_pkg_name => G_PKG_NAME,
2564 p_procedure_name => l_api_name);
2565 end if;
2566
2567 END Consolidate_Worksheets;
2568
2569 /* ----------------------------------------------------------------------- */
2570
2571 PROCEDURE Validate_Consolidation
2572 ( p_api_version IN NUMBER,
2573 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_NONE,
2574 p_return_status OUT NOCOPY VARCHAR2,
2575 p_global_worksheet_id IN NUMBER
2576 ) IS
2577
2578 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Consolidation';
2579 l_api_version CONSTANT NUMBER := 1.0;
2580
2581 l_freeze_flag VARCHAR2(1);
2582 l_stage_set_id NUMBER;
2583 l_current_stage_seq NUMBER;
2584 l_budget_calendar_id NUMBER;
2585 l_budget_by_position VARCHAR2(1);
2586 l_budget_group_id NUMBER;
2587 l_set_of_books_id NUMBER;
2588
2589 l_budget_group_exists VARCHAR2(1);
2590
2591 l_return_status VARCHAR2(1);
2592
2593 cursor c_Global_WS is
2594 select nvl(b.freeze_flag, 'N') freeze_flag,
2595 b.stage_set_id,
2596 b.current_stage_seq,
2597 b.budget_calendar_id,
2598 b.budget_by_position,
2599 b.budget_group_id,
2600 nvl(a.set_of_books_id, a.root_set_of_books_id) set_of_books_id
2601 from PSB_BUDGET_GROUPS_V a,
2602 PSB_WORKSHEETS b
2603 where a.budget_group_id = b.budget_group_id
2604 and b.worksheet_id = p_global_worksheet_id;
2605
2606 cursor c_Local_WS is
2607 select b.name,
2608 b.global_worksheet_flag,
2609 b.stage_set_id,
2610 b.current_stage_seq,
2611 b.budget_calendar_id,
2612 b.budget_by_position,
2613 b.budget_group_id,
2614 nvl(a.set_of_books_id, a.root_set_of_books_id) set_of_books_id
2615 from PSB_BUDGET_GROUPS_V a,
2616 PSB_WORKSHEETS b,
2617 PSB_WS_CONSOLIDATION_DETAILS c
2618 where a.budget_group_id = b.budget_group_id
2619 and b.worksheet_id = c.local_worksheet_id
2620 and c.global_worksheet_id = p_global_worksheet_id;
2621
2622 cursor c_BudGrp (budgetgroup_id NUMBER) is
2623 select budget_group_id
2624 from PSB_BUDGET_GROUPS
2625 where budget_group_type = 'R'
2626 and budget_group_id = budgetgroup_id
2627 and effective_start_date <= PSB_WS_ACCT1.g_startdate_pp
2628 and (effective_end_date is null or effective_end_date >= PSB_WS_ACCT1.g_enddate_cy)
2629 start with budget_group_id = l_budget_group_id
2630 connect by prior budget_group_id = parent_budget_group_id;
2631
2632
2633
2634 BEGIN
2635
2636 -- Standard call to check for call compatibility.
2637
2638 if not FND_API.Compatible_API_Call (l_api_version,
2639 p_api_version,
2640 l_api_name,
2641 G_PKG_NAME)
2642 then
2643 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2644 end if;
2645
2646 for c_Global_WS_Rec in c_Global_WS loop
2647 l_freeze_flag := c_Global_WS_Rec.freeze_flag;
2648 l_stage_set_id := c_Global_WS_Rec.stage_set_id;
2649 l_current_stage_seq := c_Global_WS_Rec.current_stage_seq;
2650 l_budget_calendar_id := c_Global_WS_Rec.budget_calendar_id;
2651 l_budget_by_position := c_Global_WS_Rec.budget_by_position;
2652 l_budget_group_id := c_Global_WS_Rec.budget_group_id;
2653 l_set_of_books_id := c_Global_WS_Rec.set_of_books_id;
2654 end loop;
2655
2656 if l_freeze_flag = 'Y' then
2657 add_message('PSB', 'PSB_TARGET_WORKSHEET_IS_FROZEN');
2658 raise FND_API.G_EXC_ERROR;
2659 end if;
2660
2661 if l_budget_calendar_id <> nvl(PSB_WS_ACCT1.g_budget_calendar_id, FND_API.G_MISS_NUM) then
2662 begin
2663
2664 PSB_WS_ACCT1.Cache_Budget_Calendar
2665 (p_return_status => l_return_status,
2666 p_budget_calendar_id => l_budget_calendar_id);
2667
2668 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2669 raise FND_API.G_EXC_ERROR;
2670 end if;
2671
2672 end;
2673 end if;
2674
2675 for c_Local_WS_Rec in c_Local_WS loop
2676
2677 -- Check that every local worksheet is a global worksheet
2678
2679 if c_Local_WS_Rec.global_worksheet_flag <> 'Y' then
2680 message_token('WORKSHEET', c_Local_WS_Rec.name);
2681 add_message('PSB', 'PSB_CONS_NOT_GLOBAL');
2682 raise FND_API.G_EXC_ERROR;
2683 end if;
2684
2685 -- Check that the stage set of every local worksheet matches the global worksheet
2686
2687 if c_Local_WS_Rec.stage_set_id <> l_stage_set_id then
2688 message_token('WORKSHEET', c_Local_WS_Rec.name);
2689 add_message('PSB', 'PSB_CONS_MISMATCH_STAGESET');
2690 raise FND_API.G_EXC_ERROR;
2691 end if;
2692
2693 -- Check that the current stage sequence of every local worksheet matches the global worksheet
2694
2695 if c_Local_WS_Rec.current_stage_seq <> l_current_stage_seq then
2696 message_token('WORKSHEET', c_Local_WS_Rec.name);
2697 add_message('PSB', 'PSB_CONS_MISMATCH_STAGESEQ');
2698 raise FND_API.G_EXC_ERROR;
2699 end if;
2700
2701 -- Check that the set of books for every local worksheet matches the global worksheet
2702
2703 if c_Local_WS_Rec.set_of_books_id <> l_set_of_books_id then
2704 message_token('WORKSHEET', c_Local_WS_Rec.name);
2705 add_message('PSB', 'PSB_CONS_MISMATCH_SOB');
2706 raise FND_API.G_EXC_ERROR;
2707 end if;
2708
2709 -- Check that the budget calendar of every local worksheet matches the global worksheet
2710
2711 if c_Local_WS_Rec.budget_calendar_id <> l_budget_calendar_id then
2712 message_token('WORKSHEET', c_Local_WS_Rec.name);
2713 add_message('PSB', 'PSB_CONS_MISMATCH_CAL');
2714 raise FND_API.G_EXC_ERROR;
2715 end if;
2716
2717 -- Check that position worksheets are not consolidated into a line-item global worksheet
2718
2719 if ((l_budget_by_position = 'N') and (c_Local_WS_Rec.budget_by_position <> l_budget_by_position)) then
2720 message_token('WORKSHEET', c_Local_WS_Rec.name);
2721 add_message('PSB', 'PSB_CONS_MISMATCH_POS');
2722 raise FND_API.G_EXC_ERROR;
2723 end if;
2724
2725 -- Check that the budget group of every local worksheet belongs to the global worksheet budget group hierarchy
2726
2727 l_budget_group_exists := FND_API.G_FALSE;
2728
2729 for c_BudGrp_Rec in c_BudGrp (c_Local_WS_Rec.budget_group_id) loop
2730 l_budget_group_exists := FND_API.G_TRUE;
2731 end loop;
2732
2733 if not FND_API.to_Boolean(l_budget_group_exists) then
2734 begin
2735
2736 if c_Local_WS_Rec.budget_group_id <> l_budget_group_id then
2737 message_token('WORKSHEET', c_Local_WS_Rec.name);
2738 add_message('PSB', 'PSB_CONS_MISMATCH_BGH');
2739 raise FND_API.G_EXC_ERROR;
2740 end if;
2741
2742 end;
2743 end if;
2744
2745 end loop;
2746
2747
2748 -- Initialize API return status to success
2749
2750 p_return_status := FND_API.G_RET_STS_SUCCESS;
2751
2752
2753 EXCEPTION
2754
2755 when FND_API.G_EXC_ERROR then
2756 p_return_status := FND_API.G_RET_STS_ERROR;
2757
2758 when FND_API.G_EXC_UNEXPECTED_ERROR then
2759 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2760
2761 when OTHERS then
2762 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2763
2764 if FND_MSG_PUB.Check_Msg_Level
2765 (p_message_level => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) then
2766 FND_MSG_PUB.Add_Exc_Msg
2767 (p_pkg_name => G_PKG_NAME,
2768 p_procedure_name => l_api_name);
2769 end if;
2770
2771 END Validate_Consolidation;
2772
2773 /* ----------------------------------------------------------------------- */
2774
2775 -- Add Token and Value to the Message Token array
2776
2777 PROCEDURE message_token(tokname IN VARCHAR2,
2778 tokval IN VARCHAR2) IS
2779
2780 BEGIN
2781
2782 if no_msg_tokens is null then
2783 no_msg_tokens := 1;
2784 else
2785 no_msg_tokens := no_msg_tokens + 1;
2786 end if;
2787
2788 msg_tok_names(no_msg_tokens) := tokname;
2789 msg_tok_val(no_msg_tokens) := tokval;
2790
2791 END message_token;
2792
2793 /* ----------------------------------------------------------------------- */
2794
2795 -- Define a Message Token with a Value and set the Message Name
2796
2797 -- Calls FND_MESSAGE server package to set the Message Stack. This message is
2798 -- retrieved by the calling program.
2799
2800 PROCEDURE add_message(appname IN VARCHAR2,
2801 msgname IN VARCHAR2) IS
2802
2803 i BINARY_INTEGER;
2804
2805 BEGIN
2806
2807 if ((appname is not null) and
2808 (msgname is not null)) then
2809
2810 FND_MESSAGE.SET_NAME(appname, msgname);
2811
2812 if no_msg_tokens is not null then
2813
2814 for i in 1..no_msg_tokens loop
2815 FND_MESSAGE.SET_TOKEN(msg_tok_names(i), msg_tok_val(i));
2816 end loop;
2817
2818 end if;
2819
2820 FND_MSG_PUB.Add;
2821
2822 end if;
2823
2824 -- Clear Message Token stack
2825
2826 no_msg_tokens := 0;
2827
2828 END add_message;
2829
2830 /* ----------------------------------------------------------------------- */
2831
2832 END PSB_WORKSHEET_CONSOLIDATE;