[Home] [Help]
PACKAGE BODY: APPS.PSB_COPY_DATA_EXTRACT_PVT
Source
1 PACKAGE BODY PSB_COPY_DATA_EXTRACT_PVT AS
2 /* $Header: PSBVCDEB.pls 120.13 2006/08/21 05:15:42 maniskum ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):= 'PSB_COPY_DATA_EXTRACT_PVT';
5
6 g_dbug VARCHAR2(2000);
7 g_debug_flag VARCHAR2(1) := 'N';
8
9 /* ----------------------------------------------------------------------- */
10
11 PROCEDURE debug
12 ( p_message IN VARCHAR2) IS
13
14 BEGIN
15
16 if g_debug_flag = 'Y' then
17 null;
18 -- dbms_output.put_line(p_message);
19 end if;
20
21 END debug;
22
23 /* ----------------------------------------------------------------------- */
24
25 PROCEDURE Copy_Attributes
26 ( p_api_version IN NUMBER,
27 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
28 p_commit IN VARCHAR2 := FND_API.G_FALSE,
29 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
30 p_return_status OUT NOCOPY VARCHAR2,
31 p_msg_count OUT NOCOPY NUMBER,
32 p_msg_data OUT NOCOPY VARCHAR2,
33 p_extract_method IN VARCHAR2,
34 p_src_data_extract_id IN NUMBER,
35 p_data_extract_id IN NUMBER
36 ) AS
37
38 l_api_name CONSTANT VARCHAR2(30) := 'Copy_Attributes';
39 l_api_version CONSTANT NUMBER := 1.0;
40 l_last_update_date date;
41 l_last_updated_by number;
42 l_last_update_login number;
43 l_creation_date date;
44 l_created_by number;
45 lr_attribute_id number;
46 --UTF8 changes for Bug No : 2615261
47 lr_attribute_value psb_attribute_values.attribute_value%TYPE;
48 l_attribute_value_id number;
49 l_attr_dummy number := 0;
50 l_rowid varchar2(100);
51 l_status varchar2(1);
52 l_return_status varchar2(1);
53 l_msg_count number;
54 l_msg_data varchar2(1000);
55 l_restart_id number;
56
57 Cursor C_Attr_Val is
58 Select attribute_value_id,attribute_id,
59 attribute_value,hr_value_id,description
60 from psb_attribute_values
61 where data_extract_id = p_src_data_extract_id;
62
63 Cursor C_ref_attr is
64 Select attribute_value_id
65 from psb_attribute_values
66 where attribute_id = lr_attribute_id
67 and attribute_value = lr_attribute_value
68 and data_extract_id = p_data_extract_id;
69
70 BEGIN
71
72 -- Standard Start of API savepoint
73
74 SAVEPOINT Copy_Attributes_Pvt;
75
76 -- Standard call to check for call compatibility.
77
78 if not FND_API.Compatible_API_Call (l_api_version,
79 p_api_version,
80 l_api_name,
81 G_PKG_NAME)
82 then
83 raise FND_API.G_EXC_UNEXPECTED_ERROR;
84 end if;
85
86 -- Initialize message list if p_init_msg_list is set to TRUE.
87
88 if FND_API.to_Boolean (p_init_msg_list) then
89 FND_MSG_PUB.initialize;
90 end if;
91
92 -- Initialize API return status to success
93
94 p_return_status := FND_API.G_RET_STS_SUCCESS;
95
96 -- API body
97 l_last_update_date := sysdate;
98 l_last_updated_by := FND_GLOBAL.USER_ID;
99 l_last_update_login := FND_GLOBAL.LOGIN_ID;
100 l_creation_date := sysdate;
101 l_created_by := FND_GLOBAL.USER_ID;
102
103 PSB_HR_EXTRACT_DATA_PVT.Check_Reentry
104 (p_api_version => 1.0 ,
105 p_return_status => l_return_status,
106 p_msg_count => l_msg_count,
107 p_msg_data => l_msg_data,
108 p_data_extract_id => p_data_extract_id,
109 p_process => 'Copy Attributes',
110 p_status => l_status,
111 p_restart_id => l_restart_id
112 );
113
114 if l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
115 raise FND_API.G_EXC_ERROR;
116 end if;
117
118 if (l_status <> 'C') then
119 For C_Attr_Val_Rec in C_Attr_Val
120 Loop
121 lr_attribute_id := C_Attr_Val_Rec.attribute_id;
122 lr_attribute_value := C_Attr_Val_Rec.attribute_value;
123
124 For C_ref_attr_rec in C_ref_attr
125 Loop
126 l_attr_dummy := 1;
127 PSB_ATTRIBUTE_VALUES_PVT.UPDATE_ROW
128 ( p_api_version => 1.0,
129 p_init_msg_lISt => NULL,
130 p_commit => NULL,
131 p_validation_level => NULL,
132 p_return_status => l_return_status,
133 p_msg_count => l_msg_count,
134 p_msg_data => l_msg_data,
135 p_attribute_value_id => C_ref_attr_rec.attribute_value_id,
136 p_attribute_id => C_Attr_Val_Rec.attribute_id,
137 p_attribute_value => C_Attr_Val_Rec.attribute_value,
138 p_hr_value_id => C_Attr_Val_Rec.hr_value_id,
139 p_description => C_Attr_Val_Rec.description,
140 p_data_extract_id => p_data_extract_id,
141 p_context => NULL,
142 p_attribute1 => NULL,
143 p_attribute2 => NULL,
144 p_attribute3 => NULL,
145 p_attribute4 => NULL,
146 p_attribute5 => NULL,
147 p_attribute6 => NULL,
148 p_attribute7 => NULL,
149 p_attribute8 => NULL,
150 p_attribute9 => NULL,
151 p_attribute10 => NULL,
152 p_attribute11 => NULL,
153 p_attribute12 => NULL,
154 p_attribute13 => NULL,
155 p_attribute14 => NULL,
156 p_attribute15 => NULL,
157 p_attribute16 => NULL,
158 p_attribute17 => NULL,
159 p_attribute18 => NULL,
160 p_attribute19 => NULL,
161 p_attribute20 => NULL,
162 p_attribute21 => NULL,
163 p_attribute22 => NULL,
164 p_attribute23 => NULL,
165 p_attribute24 => NULL,
166 p_attribute25 => NULL,
167 p_attribute26 => NULL,
168 p_attribute27 => NULL,
169 p_attribute28 => NULL,
170 p_attribute29 => NULL,
171 p_attribute30 => NULL,
172 p_last_update_date => l_last_update_date,
173 p_last_updated_by => l_last_updated_by,
174 p_last_update_login => l_last_update_login
175 );
176
177 if l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
178 raise FND_API.G_EXC_ERROR;
179 end if;
180 End Loop;
181
182 if (l_attr_dummy = 0) then
183
184 select psb_attribute_values_s.nextval into
185 l_attribute_value_id from dual;
186
187 PSB_ATTRIBUTE_VALUES_PVT.INSERT_ROW
188 ( p_api_version => 1.0,
189 p_init_msg_list => null,
190 p_commit => null,
191 p_validation_level => null,
192 p_return_status => l_return_status,
193 p_msg_count => l_msg_count,
194 p_msg_data => l_msg_data,
195 p_rowid => l_rowid,
196 p_attribute_value_id => l_attribute_value_id,
197 p_attribute_id => C_Attr_Val_Rec.attribute_id,
198 p_attribute_value => C_Attr_Val_Rec.attribute_value,
199 p_hr_value_id => C_Attr_Val_Rec.hr_value_id,
200 p_description => C_Attr_Val_Rec.description,
201 p_data_extract_id => p_data_extract_id,
202 p_context => null,
203 p_attribute1 => null,
204 p_attribute2 => null,
205 p_attribute3 => null,
206 p_attribute4 => null,
207 p_attribute5 => null,
208 p_attribute6 => null,
209 p_attribute7 => null,
210 p_attribute8 => null,
211 p_attribute9 => null,
212 p_attribute10 => null,
213 p_attribute11 => null,
214 p_attribute12 => null,
215 p_attribute13 => null,
216 p_attribute14 => null,
217 p_attribute15 => null,
218 p_attribute16 => null,
219 p_attribute17 => null,
220 p_attribute18 => null,
221 p_attribute19 => null,
222 p_attribute20 => null,
223 p_attribute21 => null,
224 p_attribute22 => null,
225 p_attribute23 => null,
226 p_attribute24 => null,
227 p_attribute25 => null,
228 p_attribute26 => null,
229 p_attribute27 => null,
230 p_attribute28 => null,
231 p_attribute29 => null,
232 p_attribute30 => null,
233 p_last_update_date => l_last_update_date,
234 p_last_updated_by => l_last_updated_by,
235 p_last_update_login => l_last_update_login,
236 p_created_by => l_created_by,
237 p_creation_date => l_creation_date
238 ) ;
239
240 if l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
241 debug('Insert Attribute Values Failed');
242 raise FND_API.G_EXC_ERROR;
243 end if;
244
245 end if;
246
247 End Loop;
248
249 PSB_HR_EXTRACT_DATA_PVT.Reentrant_Process
250 ( p_api_version => 1.0 ,
251 p_return_status => l_return_status,
252 p_msg_count => l_msg_count,
253 p_msg_data => l_msg_data,
254 p_data_extract_id => p_data_extract_id,
255 p_extract_method => p_extract_method,
256 p_process => 'Copy Attributes'
257 );
258
259 if l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
260 debug('Reentry Failed Copy Attributes');
261 raise FND_API.G_EXC_ERROR;
262 end if;
263 end if;
264
265 -- End of API body.
266
267 -- Standard check of p_commit.
268
269 if FND_API.to_Boolean (p_commit) then
270 commit work;
271 end if;
272
273 -- Standard call to get message count and if count is 1, get message info.
274
275 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
276 p_data => p_msg_data);
277
278 EXCEPTION
279
280 when FND_API.G_EXC_ERROR then
281
282 rollback to Copy_Attributes_Pvt;
283
284 p_return_status := FND_API.G_RET_STS_ERROR;
285
286 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
287 p_data => p_msg_data);
288
289
290 when FND_API.G_EXC_UNEXPECTED_ERROR then
291
292 rollback to Copy_Attributes_Pvt;
293
294 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
295
296 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
297 p_data => p_msg_data);
298
299
300 when OTHERS then
301
302 rollback to Copy_Attributes_Pvt;
303
304 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
305
306 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) then
307
308 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
309 l_api_name);
310 end if;
311
312 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
313 p_data => p_msg_data);
314
315 END Copy_Attributes;
316
317 PROCEDURE Copy_Position_Sets
318 ( p_api_version IN NUMBER,
319 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
320 p_commit IN VARCHAR2 := FND_API.G_FALSE,
321 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
322 p_return_status OUT NOCOPY VARCHAR2,
323 p_msg_count OUT NOCOPY NUMBER,
324 p_msg_data OUT NOCOPY VARCHAR2,
325 p_extract_method IN VARCHAR2,
326 p_src_data_extract_id IN NUMBER,
327 p_data_extract_id IN NUMBER
328 ) AS
329
330 l_api_name CONSTANT VARCHAR2(30) := 'Copy_Position_Sets';
331 l_api_version CONSTANT NUMBER := 1.0;
332
333 l_entity_tbl PSB_Account_Position_Set_Pvt.Entity_Tbl_Type;
334 l_return_status varchar2(1);
335 l_status varchar2(1);
336 l_msg_count number;
337 l_msg_data varchar2(1000);
338 l_restart_id number;
339
340 BEGIN
341
342 -- Standard Start of API savepoint
343 SAVEPOINT Copy_Position_Sets_Pvt;
344
345 -- Standard call to check for call compatibility.
346
347 if not FND_API.Compatible_API_Call (l_api_version,
348 p_api_version,
349 l_api_name,
350 G_PKG_NAME)
351 then
352 raise FND_API.G_EXC_UNEXPECTED_ERROR;
353 end if;
354
355 -- Initialize message list if p_init_msg_list is set to TRUE.
356
357 if FND_API.to_Boolean (p_init_msg_list) then
358 FND_MSG_PUB.initialize;
359 end if;
360
361 -- Initialize API return status to success
362
363 p_return_status := FND_API.G_RET_STS_SUCCESS;
364
365 -- API body
366 PSB_HR_EXTRACT_DATA_PVT.Check_Reentry
367 (p_api_version => 1.0 ,
368 p_return_status => l_return_status,
369 p_msg_count => l_msg_count,
370 p_msg_data => l_msg_data,
371 p_data_extract_id => p_data_extract_id,
372 p_process => 'Copy Position Sets',
373 p_status => l_status,
374 p_restart_id => l_restart_id
375 );
376
377 if l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
378 raise FND_API.G_EXC_ERROR;
379 end if;
380
381 if (l_status <> 'C') then
382
383 l_entity_tbl(1) := 'DR';
384 l_entity_tbl(2) := 'E';
385 l_entity_tbl(3) := 'PSG';
386
387 PSB_Account_Position_Set_Pvt.Copy_Position_Sets
388 (p_api_version => 1.0 ,
389 p_return_status => l_return_status,
390 p_msg_count => l_msg_count,
391 p_msg_data => l_msg_data,
392 p_source_data_extract_id => p_src_data_extract_id,
393 p_target_data_extract_id => p_data_extract_id,
394 p_entity_table => l_entity_tbl
395 );
396
397 if l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
398 debug('Copy Position Sets Failed');
399 raise FND_API.G_EXC_ERROR;
400 end if;
401
402 PSB_HR_EXTRACT_DATA_PVT.Reentrant_Process
403 ( p_api_version => 1.0 ,
404 p_return_status => l_return_status,
405 p_msg_count => l_msg_count,
406 p_msg_data => l_msg_data,
407 p_data_extract_id => p_data_extract_id,
408 p_extract_method => p_extract_method,
409 p_process => 'Copy Position Sets'
410 );
411
412 if l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
413 debug('Reentry Failed Copy Position Sets');
414 raise FND_API.G_EXC_ERROR;
415 end if;
416
417 end if;
418 -- End of API body.
419
420 -- Standard check of p_commit.
421
422 if FND_API.to_Boolean (p_commit) then
423 commit work;
424 end if;
425
426 -- Standard call to get message count and if count is 1, get message info.
427
428 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
429 p_data => p_msg_data);
430
431 EXCEPTION
432
433 when FND_API.G_EXC_ERROR then
434
435 rollback to Copy_Position_Sets_Pvt;
436
437 p_return_status := FND_API.G_RET_STS_ERROR;
438
439 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
440 p_data => p_msg_data);
441
442
443 when FND_API.G_EXC_UNEXPECTED_ERROR then
444
445 rollback to Copy_Position_Sets_Pvt;
446
447 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
448
449 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
450 p_data => p_msg_data);
451
452
453 when OTHERS then
454
455 rollback to Copy_Position_Sets_Pvt;
456
457 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
458
459 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) then
460
461 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
462 l_api_name);
463 end if;
464
465 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
466 p_data => p_msg_data);
467 END;
468
469 PROCEDURE Copy_Elements
470 ( p_api_version IN NUMBER,
471 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
472 p_commit IN VARCHAR2 := FND_API.G_FALSE,
473 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
474 p_return_status OUT NOCOPY VARCHAR2,
475 p_msg_count OUT NOCOPY NUMBER,
476 p_msg_data OUT NOCOPY VARCHAR2,
477 p_extract_method IN VARCHAR2,
478 p_src_data_extract_id IN NUMBER,
479 p_copy_salary_flag IN VARCHAR2,
480 p_data_extract_id IN NUMBER
481 ) AS
482
483 l_api_name CONSTANT VARCHAR2(30) := 'Copy_Elements';
484 l_api_version CONSTANT NUMBER := 1.0;
485 l_last_update_date date;
486 l_last_updated_by number;
487 l_last_update_login number;
488 l_element_dummy number;
489 l_element_name varchar2(30);
490 l_creation_date date;
491 l_created_by number;
492 l_status varchar2(1);
493 l_return_status varchar2(1);
494 l_msg_count number;
495 l_msg_data varchar2(1000);
496 l_restart_id number;
497
498 /* Bug No 2579818 Start */
499 l_business_group_id NUMBER;
500
501 cursor c_extract is
502 select business_group_id
503 from PSB_DATA_EXTRACTS
504 where data_extract_id = p_src_data_extract_id;
505 /* Bug No 2579818 End */
506
507 Cursor l_find_element_csr is
508 Select pay_element_id,
509 name,
510 salary_flag
511 from psb_pay_elements
512 where data_extract_id = p_src_data_extract_id;
513
514 Cursor l_find_target_element_csr is
515 Select pay_element_id
516 from psb_pay_elements
517 where name = l_element_name
518 and data_extract_id = p_data_extract_id;
519
520 BEGIN
521
522 -- Standard Start of API savepoint
523
524 SAVEPOINT Copy_Elements_Pvt;
525
526 -- Standard call to check for call compatibility.
527
528 if not FND_API.Compatible_API_Call (l_api_version,
529 p_api_version,
530 l_api_name,
531 G_PKG_NAME)
532 then
533 raise FND_API.G_EXC_UNEXPECTED_ERROR;
534 end if;
535
536 -- Initialize message list if p_init_msg_list is set to TRUE.
537
538 if FND_API.to_Boolean (p_init_msg_list) then
539 FND_MSG_PUB.initialize;
540 end if;
541
542 -- Initialize API return status to success
543
544 p_return_status := FND_API.G_RET_STS_SUCCESS;
545
546 -- API body
547 l_last_update_date := sysdate;
548 l_last_updated_by := FND_GLOBAL.USER_ID;
549 l_last_update_login := FND_GLOBAL.LOGIN_ID;
550 l_creation_date := sysdate;
551 l_created_by := FND_GLOBAL.USER_ID;
552
553 PSB_HR_EXTRACT_DATA_PVT.Check_Reentry
554 (p_api_version => 1.0 ,
555 p_return_status => l_return_status,
556 p_msg_count => l_msg_count,
557 p_msg_data => l_msg_data,
558 p_data_extract_id => p_data_extract_id,
559 p_process => 'Copy Elements',
560 p_status => l_status,
561 p_restart_id => l_restart_id
562 );
563
564 if l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
565 raise FND_API.G_EXC_ERROR;
566 end if;
567
568 if (l_status <> 'C') then
569
570 /* Bug No 2579818 Start */
571 for c_extract_rec in c_extract loop
572 l_business_group_id := c_extract_rec.business_group_id;
573 end loop;
574
575 PSB_POSITION_CONTROL_PVT.Upload_Attribute_Values
576 (p_return_status => l_return_status,
577 p_source_data_extract_id => p_src_data_extract_id,
578 p_source_business_group_id => l_business_group_id,
579 p_target_data_extract_id => p_data_extract_id);
580
581 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
582 raise FND_API.G_EXC_ERROR;
583 end if;
584 /* Bug No 2579818 End */
585
586
587 For l_find_element_rec in l_find_element_csr
588 Loop
589
590 l_element_dummy := 0;
591
592 if (l_find_element_rec.salary_flag = 'Y') then
593 if (p_copy_salary_flag = 'Y') then
594 if (p_extract_method = 'REFRESH') then
595
596 l_element_name := l_find_element_rec.name;
597
598 For l_find_target_element_rec in l_find_target_element_csr
599 Loop
600
601 l_element_dummy := 1;
602
603 End Loop;
604 end if;
605 else
606
607 l_element_dummy := 1;
608
609 end if;
610 else
611
612 l_element_name := l_find_element_rec.name;
613
614 For l_find_target_element_rec in l_find_target_element_csr
615 Loop
616
617 l_element_dummy := 1;
618
619 End Loop;
620 end if;
621
622 if (l_element_dummy = 0) then
623
624 PSB_POSITION_CONTROL_PVT.Upload_Element
625 (p_return_status => l_return_status,
626 p_source_data_extract_id => p_src_data_extract_id,
627 p_target_data_extract_id => p_data_extract_id,
628 p_pay_element_id => l_find_element_rec.pay_element_id);
629
630 if l_return_status = FND_API.G_RET_STS_ERROR then
631 debug('Copy Entity Elements Failed');
632 raise FND_API.G_EXC_ERROR;
633 elsif l_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
634 debug('Copy Entity Elements Failed');
635 raise FND_API.G_EXC_UNEXPECTED_ERROR ;
636 end if;
637
638 end if; -- element_dummy value check
639 End Loop;
640
641 PSB_HR_EXTRACT_DATA_PVT.Reentrant_Process
642 ( p_api_version => 1.0 ,
643 p_return_status => l_return_status,
644 p_msg_count => l_msg_count,
645 p_msg_data => l_msg_data,
646 p_data_extract_id => p_data_extract_id,
647 p_extract_method => p_extract_method,
648 p_process => 'Copy Elements'
649 );
650
651 if l_return_status = FND_API.G_RET_STS_ERROR then
652 debug('Reentry Failed Copy Elements');
653 raise FND_API.G_EXC_ERROR;
654 elsif l_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
655 debug('Reentry Failed Copy Elements');
656 raise FND_API.G_EXC_UNEXPECTED_ERROR ;
657 end if;
658
659 end if; -- check for status
660
661 -- End of API body.
662
663 -- Standard check of p_commit.
664
665 if FND_API.to_Boolean (p_commit) then
666 commit work;
667 end if;
668
669 -- Standard call to get message count and if count is 1, get message info.
670
671 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
672 p_data => p_msg_data);
673
674 EXCEPTION
675
676 when FND_API.G_EXC_ERROR then
677
678 rollback to Copy_Elements_Pvt;
679
680 p_return_status := FND_API.G_RET_STS_ERROR;
681
682 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
683 p_data => p_msg_data);
684
685
686 when FND_API.G_EXC_UNEXPECTED_ERROR then
687
688 rollback to Copy_Elements_Pvt;
689
690 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
691
692 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
693 p_data => p_msg_data);
694
695
696 when OTHERS then
697
698 rollback to Copy_Elements_Pvt;
699
700 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
701
702 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) then
703
704 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
705 l_api_name);
706 end if;
707
708 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
709 p_data => p_msg_data);
710
711 END Copy_Elements;
712
713 PROCEDURE Copy_Default_Rules
714 ( p_api_version IN NUMBER,
715 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
716 p_commit IN VARCHAR2 := FND_API.G_FALSE,
717 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
718 p_return_status OUT NOCOPY VARCHAR2,
719 p_msg_count OUT NOCOPY NUMBER,
720 p_msg_data OUT NOCOPY VARCHAR2,
721 p_extract_method IN VARCHAR2,
722 p_src_data_extract_id IN NUMBER,
723 p_data_extract_id IN NUMBER
724 ) AS
725
726 l_api_name CONSTANT VARCHAR2(30) := 'Copy_Default_Rules';
727 l_api_version CONSTANT NUMBER := 1.0;
728 l_last_update_date date;
729 l_last_updated_by number;
730 l_last_update_login number;
731 l_creation_date date;
732 l_created_by number;
733 l_default_rule_id number;
734 l_account_distribution_id number;
735 l_alloc_rule_percent_id number;
736 l_default_assignment_id number;
737 l_rowid1 varchar2(100);
738 l_rowid2 varchar2(100);
739 l_rowid3 varchar2(100);
740
741 /* Start bug no 1308558 */
742 -- These rowid's are needed for
743 -- PSB_ENTITY_SET AND PSB_ENTITY_ASSIGNMENT
744 l_rowid4 VARCHAR2(1000);
745 l_rowid5 VARCHAR2(1000);
746 /* End bug no 1308558 */
747
748 l_status varchar2(1);
749 l_insert_flag varchar2(1);
750 l_return_status varchar2(1);
751 l_msg_count number;
752 l_default_count number;
753 l_src_default_rule_id number;
754 l_src_pay_element_id number;
755 l_src_pay_option_id number;
756 l_src_attribute_value_id number;
757 l_dest_pay_element_id number;
758 l_dest_pay_option_id number;
759 l_dest_attribute_value_id number;
760 l_src_pay_element_name varchar2(30);
761 l_src_pay_option_name psb_pay_element_options.name%type; -- bug 5149134
762 l_src_grade_step number;
763 --UTF8 changes for Bug No : 2615261
764 l_src_attribute_value psb_attribute_values.attribute_value%TYPE;
765 l_src_entity_id number;
766 l_entity_id number;
767 l_set_relation_id number;
768 l_set_name varchar2(100);
769 l_msg_data varchar2(1000);
770 l_restart_id number;
771
772 /* Start bug no 1308558 */
773 -- local variables needed for
774 -- PSB_ENTITY_SET AND PSB_ENTITY_ASSIGNMENT
775 l_ext_default_rule_id NUMBER;
776 l_entity_set_id NUMBER;
777 l_new_entity_set_id NUMBER;
778 l_business_group_id NUMBER;
779 l_set_of_books_id NUMBER;
780 l_entity_set_name VARCHAR2(30);
781 l_message_name VARCHAR2(2000); -- bug 5149134
782 l_exec_from_position VARCHAR2(1); --bug 4273111
783
784 TYPE default_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
785 l_default_tbl default_tbl_type;
786 /* End bug no 1308558 */
787
788
789
790
791
792 Cursor C_Defaults is
793 Select default_rule_id,
794 name,
795 global_default_flag,
796 business_group_id,
797 entity_id,
798 priority,
799 overwrite -- bug 4179734
800 from psb_defaults
801 where data_extract_id = p_src_data_extract_id;
802
803 Cursor C_Position_Sets is
804 Select aps.name,
805 effective_start_date,
806 effective_end_date
807 from psb_set_relations rels, psb_account_position_sets aps
808 where rels.account_position_set_id = aps.account_position_set_id
809 and aps.data_extract_id = p_src_data_extract_id
810 and rels.default_rule_id = l_src_default_rule_id;
811
812 Cursor C_Def_Distr is
813 Select account_distribution_id,
814 chart_of_accounts_id,
815 code_combination_id,
816 distribution_percent
817 from psb_default_account_distrs
818 where default_rule_id = l_src_default_rule_id;
819
820 Cursor C_Def_Assign is
821 Select assignment_type,attribute_id,
822 attribute_value_id,attribute_value,
823 pay_element_id,pay_element_option_id,
824 element_value,element_value_type,
825 currency_code,pay_basis
826 from psb_default_assignments
827 where default_rule_id = l_src_default_rule_id;
828
829 Cursor C_Pay_Element is
830 Select name
831 from psb_pay_elements
832 where data_extract_id = p_src_data_extract_id
833 and pay_element_id = l_src_pay_element_id;
834
835 Cursor C_Pay_Element_Dest is
836 Select pay_element_id
837 from psb_pay_elements
838 where data_extract_id = p_data_extract_id
839 and name = l_src_pay_element_name;
840 -- Following two cursor modified as part of bug #5407267
841 -- Bug 4179764 .In the following cursor added
842 -- the grade_step in the select clause
843 CURSOR C_Pay_Option IS
844 SELECT name, nvl(grade_step, -1) grade_step
845 FROM psb_pay_element_options
846 WHERE pay_element_id = l_src_pay_element_id
847 AND pay_element_option_id = l_src_pay_option_id;
848
849 CURSOR C_Pay_Option_Dest IS
850 SELECT pay_element_option_id
851 FROM psb_pay_element_options peo, psb_pay_elements pe
852 WHERE peo.pay_element_id = pe.pay_element_id
853 AND peo.pay_element_id = l_dest_pay_element_id
854 AND peo.name = l_src_pay_option_name
855 AND DECODE(pe.hr_element_type_id, null, -1, DECODE(pe.salary_type,
856 'STEP', peo.grade_step, -1)) = l_src_grade_step;
857
858 Cursor C_Attr_Val is
859 Select attribute_value
860 from psb_attribute_values
861 where data_extract_id = p_src_data_extract_id
862 and attribute_value_id = l_src_attribute_value_id;
863
864 Cursor C_Attr_Val_Dest is
865 Select attribute_value_id
866 from psb_attribute_values
867 where data_extract_id = p_data_extract_id
868 and attribute_value = l_src_attribute_value;
869
870 Cursor C_Alloc is
871 Select allocation_rule_id,
872 period_num,
873 monthly,
874 quarterly,
875 semi_annual
876 from psb_allocrule_percents_v
877 where allocation_rule_id = l_src_entity_id;
878
879 Cursor C_Account_Sets is
880 Select account_position_set_id
881 from psb_account_position_sets
882 where name = l_set_name
883 and data_extract_id = p_data_extract_id;
884
885 /* Start bug no 1308558 */
886 -- This cursor selects all the existing
887 -- entity sets associated with source data extract
888 CURSOR l_entity_set_csr
889 IS
890 SELECT *
891 FROM psb_entity_set
892 WHERE data_extract_id = p_src_data_extract_id
893 AND entity_type = 'DEFAULT_RULE';
894
895
896 -- This cursor selects all the entity
897 -- assignments associated with the data
898 -- extract
899 CURSOR l_entity_assignment_csr
900 IS
901 SELECT *
902 FROM psb_entity_assignment
903 WHERE entity_set_id = l_entity_set_id;
904 /* End bug no 1308558 */
905
906 BEGIN
907
908 -- Standard Start of API savepoint
909
910 SAVEPOINT Copy_Default_Rules_Pvt;
911
912 -- Standard call to check for call compatibility.
913
914 if not FND_API.Compatible_API_Call (l_api_version,
915 p_api_version,
916 l_api_name,
917 G_PKG_NAME)
918 then
919 raise FND_API.G_EXC_UNEXPECTED_ERROR;
920 end if;
921
922 -- Initialize message list if p_init_msg_list is set to TRUE.
923
924 if FND_API.to_Boolean (p_init_msg_list) then
925 FND_MSG_PUB.initialize;
926 end if;
927
928 -- Initialize API return status to success
929
930 p_return_status := FND_API.G_RET_STS_SUCCESS;
931 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Inside Copy rule program');
932
933 -- API body
934 l_last_update_date := sysdate;
935 l_last_updated_by := FND_GLOBAL.USER_ID;
936 l_last_update_login := FND_GLOBAL.LOGIN_ID;
937 l_creation_date := sysdate;
938 l_created_by := FND_GLOBAL.USER_ID;
939
940 PSB_HR_EXTRACT_DATA_PVT.Check_Reentry
941 (p_api_version => 1.0 ,
942 p_return_status => l_return_status,
943 p_msg_count => l_msg_count,
944 p_msg_data => l_msg_data,
945 p_data_extract_id => p_data_extract_id,
946 p_process => 'Copy Default Rules',
947 p_status => l_status,
948 p_restart_id => l_restart_id
949 );
950
951 if l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
952 raise FND_API.G_EXC_ERROR;
953 end if;
954
955 if (l_status <> 'C') then
956
957 /* Start bug no 1308558 */
958 l_default_tbl.DELETE;
959 /* End bug no 1308558 */
960
961 For C_Defaults_Rec in C_Defaults
962 Loop
963 l_src_default_rule_id := C_Defaults_Rec.default_rule_id;
964 l_src_entity_id := C_Defaults_Rec.entity_id;
965
966 Select count(*) into l_default_count
967 from psb_defaults
968 where name = C_Defaults_Rec.name
969 and data_extract_id = p_data_extract_id;
970
971 If (l_default_count = 0) then
972 SELECT psb_entity_s.nextval
973 INTO l_entity_id
974 FROM dual;
975
976 INSERT INTO psb_entity
977 (entity_id,
978 entity_type,
979 entity_subtype,
980 name,
981 last_update_date,
982 last_updated_by,
983 last_update_login,
984 created_by,
985 creation_date)
986 VALUES( l_entity_id,
987 'ALLOCRULE',
988 'POSITION',
989 l_entity_id,
990 l_last_update_date,
991 l_last_updated_by,
992 l_last_update_login,
993 l_created_by,
994 l_creation_date);
995
996 Select psb_defaults_s.nextval
997 into l_default_rule_id
998 from dual;
999
1000 PSB_DEFAULTS_PVT.Insert_Row(
1001 p_api_version => 1.0,
1002 p_init_msg_list => FND_API.G_FALSE,
1003 p_commit => FND_API.G_FALSE,
1004 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1005 p_return_status => l_return_status,
1006 p_msg_count => l_msg_count,
1007 p_msg_data => l_msg_data,
1008 --
1009 p_row_id => l_rowid1,
1010 p_default_rule_id => l_default_rule_id,
1011 p_name => C_Defaults_Rec.name,
1012 p_global_default_flag => C_Defaults_Rec.global_default_flag,
1013 p_data_extract_id => p_data_extract_id,
1014 p_business_group_id => C_Defaults_Rec.business_group_id,
1015 p_entity_id => l_entity_id,
1016 p_priority => C_Defaults_Rec.priority,
1017 p_creation_date => l_creation_date,
1018 p_created_by => l_created_by,
1019 p_last_update_date => l_last_update_date,
1020 p_last_updated_by => l_last_updated_by,
1021 p_last_update_login => l_last_update_login,
1022
1023 /* Bug 4179734 Start */
1024 p_overwrite => c_defaults_rec.overwrite
1025 /* Bug 4179734 End*/
1026 );
1027
1028 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1029 raise FND_API.G_EXC_ERROR;
1030 end if;
1031
1032 /* Start bug no 1308558 */
1033 -- This tables will store all existing default rule ID's
1034 l_default_tbl(c_defaults_rec.default_rule_id):= l_default_rule_id;
1035 /* End bug no 1308558 */
1036
1037
1038 For C_Position_Sets_Rec in C_Position_Sets
1039 Loop
1040 l_set_name := C_Position_Sets_Rec.name;
1041 For C_Account_Sets_Rec in C_Account_Sets
1042 Loop
1043 l_set_relation_id := null;
1044 PSB_Set_Relation_PVT.Insert_Row
1045 ( p_api_version => 1.0,
1046 p_init_msg_list => FND_API.G_FALSE,
1047 p_commit => FND_API.G_FALSE,
1048 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1049 p_return_status => l_return_status,
1050 p_msg_count => l_msg_count,
1051 p_msg_data => l_msg_data,
1052 p_Row_Id => l_rowid2,
1053 p_Set_Relation_Id => l_set_relation_id,
1054 p_Account_Position_Set_Id => C_Account_Sets_Rec.account_position_set_id,
1055 p_Allocation_Rule_Id => null,
1056 p_Budget_Group_Id => null,
1057 p_Budget_Workflow_Rule_Id => null,
1058 p_Constraint_Id => null,
1059 p_Default_Rule_Id => l_default_rule_id,
1060 p_Parameter_Id => null,
1061 p_Position_Set_Group_Id => null,
1062 /* Budget Revision Rules Enhancement Start */
1063 p_rule_id => null,
1064 p_apply_balance_flag => null,
1065 /* Budget Revision Rules Enhancement End */
1066 p_Effective_Start_Date => C_Position_Sets_Rec.effective_start_date,
1067 p_Effective_End_Date => C_Position_Sets_Rec.effective_end_date,
1068 p_last_update_date => l_last_update_date,
1069 p_last_updated_by => l_last_updated_by,
1070 p_last_update_login => l_last_update_login,
1071 p_created_by => l_created_by,
1072 p_creation_date => l_creation_date
1073 );
1074
1075 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1076 raise FND_API.G_EXC_ERROR;
1077 end if;
1078
1079 End Loop;
1080
1081 End Loop;
1082
1083 For C_Def_Assign_Rec in C_Def_Assign
1084 Loop
1085 l_insert_flag := 'Y';
1086 select psb_default_assignments_s.nextval
1087 into l_default_assignment_id
1088 from dual;
1089 l_src_pay_element_id := C_Def_Assign_Rec.pay_element_id;
1090 l_src_pay_option_id := C_Def_Assign_Rec.pay_element_option_id;
1091 l_src_attribute_value_id := C_Def_Assign_Rec.attribute_value_id;
1092 l_src_pay_element_name := null;
1093 l_src_pay_option_name := null;
1094 l_src_grade_step := NULL;
1095 /* Start Bug #4179714 */
1096 l_src_attribute_value := null;
1097 /* End Bug #4179714 */
1098 l_dest_pay_element_id := null;
1099 l_dest_pay_option_id := null;
1100 l_dest_attribute_value_id := null;
1101
1102 For C_Pay_Element_Rec in C_Pay_Element
1103 Loop
1104 l_src_pay_element_name := C_Pay_Element_Rec.name;
1105 End Loop;
1106
1107 For C_Pay_Option_Rec in C_Pay_Option
1108 Loop
1109 l_src_pay_option_name := C_Pay_Option_Rec.name;
1110 /* Bug 4179764 Start */
1111 l_src_grade_step := C_Pay_Option_Rec.grade_step;
1112 /* Bug 4179764 End */
1113 End Loop;
1114
1115 For C_Pay_Element_Dest_Rec in C_Pay_Element_Dest
1116 Loop
1117 l_dest_pay_element_id := C_Pay_Element_Dest_Rec.pay_element_id;
1118 For C_Pay_Option_Dest_Rec in C_Pay_Option_Dest
1119 Loop
1120 l_dest_pay_option_id := C_Pay_Option_Dest_Rec.pay_element_option_id;
1121 End Loop;
1122 End Loop;
1123
1124 For C_Attr_Val_Rec in C_Attr_Val
1125 Loop
1126 l_src_attribute_value := C_Attr_Val_Rec.attribute_value;
1127 End Loop;
1128
1129 For C_Attr_Val_Dest_Rec in C_Attr_Val_Dest
1130 Loop
1131 l_dest_attribute_value_id := C_Attr_Val_Dest_Rec.attribute_value_id;
1132 End Loop;
1133
1134 if ((C_Def_Assign_Rec.assignment_type = 'ELEMENT') and
1135 (l_dest_pay_element_id is null)) then
1136 l_insert_flag := 'N';
1137 end if;
1138
1139 if (l_insert_flag = 'Y') then
1140 PSB_DEFAULT_ASSIGNMENTS_PVT.Insert_Row
1141 ( p_api_version => 1.0,
1142 p_init_msg_list => FND_API.G_FALSE,
1143 p_commit => FND_API.G_FALSE,
1144 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1145 p_return_status => l_return_status,
1146 p_msg_count => l_msg_count,
1147 p_msg_data => l_msg_data,
1148 p_Row_Id => l_rowid3,
1149 p_default_assignment_id => l_default_assignment_id,
1150 p_default_rule_id => l_default_rule_id,
1151 p_assignment_type => C_Def_Assign_Rec.assignment_type,
1152 p_attribute_id => C_Def_Assign_Rec.attribute_id,
1153 p_attribute_value_id => l_dest_attribute_value_id,
1154 p_attribute_value => C_Def_Assign_Rec.attribute_value,
1155 p_pay_element_id => l_dest_pay_element_id,
1156 p_pay_basis => C_Def_Assign_Rec.pay_basis,
1157 p_element_value_type => C_Def_Assign_Rec.element_value_type,
1158 p_pay_element_option_id => l_dest_pay_option_id,
1159 p_element_value => C_Def_Assign_Rec.element_value,
1160 p_currency_code => C_Def_Assign_Rec.currency_code,
1161 p_creation_date => l_creation_date,
1162 p_created_by => l_created_by,
1163 p_last_update_date => l_last_update_date,
1164 p_last_updated_by => l_last_updated_by,
1165 p_last_update_login => l_last_update_login
1166 );
1167
1168 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1169 raise FND_API.G_EXC_ERROR;
1170 end if;
1171 end if;
1172
1173 End Loop;
1174
1175 For C_Def_Distr_Rec in C_Def_Distr
1176 Loop
1177 select psb_default_account_distrs_s.nextval
1178 into l_account_distribution_id
1179 from dual;
1180
1181 Insert into Psb_default_account_distrs
1182 (account_distribution_id,
1183 default_rule_id,
1184 chart_of_accounts_id,
1185 code_combination_id,
1186 distribution_percent,
1187 last_update_date ,
1188 last_updated_by ,
1189 last_update_login ,
1190 created_by ,
1191 creation_date )
1192 VALUES
1193 (
1194 l_account_distribution_id,
1195 l_default_rule_id,
1196 C_Def_Distr_Rec.chart_of_accounts_id,
1197 C_Def_Distr_Rec.code_combination_id,
1198 C_Def_Distr_Rec.distribution_percent,
1199 l_last_update_date,
1200 l_last_updated_by ,
1201 l_last_update_login ,
1202 l_created_by,
1203 l_creation_date);
1204 end Loop;
1205
1206 For C_Alloc_Rec in C_Alloc
1207 Loop
1208 PSB_ALLOCRULE_PERCENTS_PVT.Insert_Row
1209 ( p_api_version => 1.0,
1210 p_init_msg_list => FND_API.G_FALSE,
1211 p_commit => FND_API.G_FALSE,
1212 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1213 p_return_status => l_return_status,
1214 p_msg_count => l_msg_count,
1215 p_msg_data => l_msg_data,
1216 p_allocation_rule_percent_id => l_alloc_rule_percent_id,
1217 p_allocation_rule_id => l_entity_id,
1218 p_period_num => C_Alloc_Rec.period_num,
1219 p_monthly => C_Alloc_Rec.monthly,
1220 p_quarterly => C_Alloc_Rec.quarterly ,
1221 p_semi_annual => C_Alloc_Rec.semi_annual,
1222 p_attribute1 => null,
1223 p_attribute2 => null,
1224 p_attribute3 => null,
1225 p_attribute4 => null,
1226 p_attribute5 => null,
1227 p_context => null,
1228 p_last_update_date => l_last_update_date,
1229 p_last_updated_by => l_last_updated_by,
1230 p_last_update_login => l_last_update_login,
1231 p_created_by => l_created_by,
1232 p_creation_date => l_creation_date);
1233 end Loop;
1234
1235 end if;
1236 End Loop;
1237
1238 /* Start bug no 1308558 */
1239 -- The following loop gets all the old entity set
1240 -- data and copies the data for the new data
1241 -- extract set id.
1242
1243
1244 FOR l_entity_set_rec IN l_entity_set_csr
1245 LOOP
1246 -- get the old eneity set id for the inner loop to execute
1247 l_entity_set_id := l_entity_set_rec.entity_set_id;
1248
1249 -- bug 4273111.
1250 l_exec_from_position := l_entity_set_rec.executable_from_position;
1251
1252 -- generate the sequence for new entity sets
1253 FOR l_new_entity_rec IN (SELECT PSB_ENTITY_SET_S.NEXTVAL entity_set_id
1254 FROM dual)
1255 LOOP
1256 l_new_entity_set_id := l_new_entity_rec.entity_set_id;
1257 END LOOP;
1258
1259
1260 -- get the set of books, business group for the target data extract
1261 FOR l_data_extract_rec IN (SELECT business_group_id,
1262 set_of_books_id
1263 FROM psb_data_extracts
1264 WHERE data_extract_id = p_data_extract_id)
1265 LOOP
1266 l_business_group_id := l_data_extract_rec.business_group_id;
1267 l_set_of_books_id := l_data_extract_rec.set_of_books_id;
1268 END LOOP;
1269
1270 /*IF LENGTH(l_entity_set_rec.name) > 23 THEN
1271 l_entity_set_name := SUBSTR(l_entity_set_rec.name, 1, 23);
1272 ELSE
1273 l_entity_set_name := l_entity_set_rec.name;
1274 END IF;*/
1275
1276 FND_MESSAGE.set_name('PSB', 'PSB_COPY_RULE_SET_NAME');
1277 FND_MESSAGE.set_token('RULE_SET_NAME', l_entity_set_rec.name);
1278 l_message_name := FND_MESSAGE.get||'-'||p_data_extract_id;
1279
1280 l_return_status := null;
1281 -- call the insert procedure PSB_ENTITY_SET.INSERT_ROW
1282 PSB_ENTITY_SET_PVT.Insert_Row
1283 (p_api_version => 1.0,
1284 p_init_msg_list => FND_API.G_FALSE,
1285 p_commit => FND_API.G_FALSE,
1286 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1287 p_return_status => l_return_status,
1288 p_msg_count => l_msg_count,
1289 p_msg_data => l_msg_data,
1290 P_ROWID => l_rowid4,
1291 P_ENTITY_SET_ID => l_new_entity_set_id,
1292 P_ENTITY_TYPE => 'DEFAULT_RULE',
1293 P_NAME => l_message_name,
1294 P_DESCRIPTION => l_entity_set_rec.description,
1295 P_BUDGET_GROUP_ID => l_business_group_id,
1296 P_SET_OF_BOOKS_ID => l_set_of_books_id,
1297 P_DATA_EXTRACT_ID => p_data_extract_id,
1298 P_CONSTRAINT_THRESHOLD => null,
1299 P_ENABLE_FLAG => null,
1300 -- bug 4273111. set the following in parameter
1301 P_EXECUTABLE_FROM_POSITION => NVL(l_exec_from_position,'N'),
1302 P_ATTRIBUTE1 => null,
1303 P_ATTRIBUTE2 => null,
1304 P_ATTRIBUTE3 => null,
1305 P_ATTRIBUTE4 => null,
1306 P_ATTRIBUTE5 => null,
1307 P_ATTRIBUTE6 => null,
1308 P_ATTRIBUTE7 => null,
1309 P_ATTRIBUTE8 => null,
1310 P_ATTRIBUTE9 => null,
1311 P_ATTRIBUTE10 => null,
1312 P_CONTEXT => null,
1313 p_Last_Update_Date => l_last_update_date,
1314 p_Last_Updated_By => l_last_updated_by,
1315 p_Last_Update_Login => l_last_update_login,
1316 p_Created_By => l_created_by,
1317 p_Creation_Date => l_creation_date);
1318
1319 -- check the success of the API
1320
1321 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1322 RAISE FND_API.G_EXC_ERROR;
1323 END IF;
1324
1325 FOR l_entity_assignment_rec IN l_entity_assignment_csr
1326 LOOP
1327
1328 -- insert the data into entity assignment table
1329 PSB_ENTITY_ASSIGNMENT_PVT.Insert_Row (
1330 p_api_version => 1.0,
1331 p_init_msg_list => FND_API.G_FALSE,
1332 p_commit => FND_API.G_FALSE,
1333 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1334 p_return_status => l_return_status,
1335 p_msg_count => l_msg_count,
1336 p_msg_data => l_msg_data,
1337 P_ROWID => l_rowid5,
1338 P_ENTITY_SET_ID => l_new_entity_set_id,
1339 P_ENTITY_ID
1340 => l_default_tbl(l_entity_assignment_rec.entity_id),
1341 P_PRIORITY => l_entity_assignment_rec.priority,
1342 P_SEVERITY_LEVEL => l_entity_assignment_rec.severity_level,
1343 P_EFFECTIVE_START_DATE
1344 => l_entity_assignment_rec.effective_start_date,
1345 P_EFFECTIVE_END_DATE => l_entity_assignment_rec.effective_end_date,
1346 p_Last_Update_Date => l_last_update_date,
1347 p_Last_Updated_By => l_last_updated_by,
1348 p_Last_Update_Login => l_last_update_login,
1349 p_Created_By => l_created_by,
1350 p_Creation_Date => l_creation_date);
1351
1352 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1353 RAISE FND_API.G_EXC_ERROR;
1354 END IF;
1355
1356 END LOOP;
1357
1358 END LOOP;
1359 /* End bug no 1308558 */
1360
1361 PSB_HR_EXTRACT_DATA_PVT.Reentrant_Process
1362 ( p_api_version => 1.0 ,
1363 p_return_status => l_return_status,
1364 p_msg_count => l_msg_count,
1365 p_msg_data => l_msg_data,
1366 p_data_extract_id => p_data_extract_id,
1367 p_extract_method => p_extract_method,
1368 p_process => 'Copy Default Rules'
1369 );
1370
1371 if l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1372 debug('Reentry Failed Copy Default Rules');
1373 raise FND_API.G_EXC_ERROR;
1374 end if;
1375 end if;
1376
1377 -- End of API body.
1378
1379 -- Standard check of p_commit.
1380
1381 if FND_API.to_Boolean (p_commit) then
1382 commit work;
1383 end if;
1384
1385 -- Standard call to get message count and if count is 1, get message info.
1386
1387 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
1388 p_data => p_msg_data);
1389
1390 EXCEPTION
1391
1392 when FND_API.G_EXC_ERROR then
1393 fnd_file.put_line(fnd_file.log, sqlerrm||' - exception ');
1394 debug('SQLCODE '||SQLCODE);
1395 rollback to Copy_Default_Rules_Pvt;
1396
1397 p_return_status := FND_API.G_RET_STS_ERROR;
1398
1399 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
1400 p_data => p_msg_data);
1401
1402
1403 when FND_API.G_EXC_UNEXPECTED_ERROR then
1404
1405 fnd_file.put_line(fnd_file.log, sqlerrm||' - exception ');
1406 debug('SQLCODE '||SQLCODE);
1407 rollback to Copy_Default_Rules_Pvt;
1408
1409 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1410
1411 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
1412 p_data => p_msg_data);
1413
1414
1415 when OTHERS then
1416
1417 fnd_file.put_line(fnd_file.log, sqlerrm||' - exception ');
1418 debug('SQLCODE '||SQLCODE);
1419 rollback to Copy_Default_Rules_Pvt;
1420
1421 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1422
1423 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) then
1424
1425 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
1426 l_api_name);
1427 end if;
1428
1429 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
1430 p_data => p_msg_data);
1431
1432 END Copy_Default_Rules;
1433
1434 /* ----------------------------------------------------------------------- */
1435
1436 -- Get Debug Information
1437
1438 -- This Module is used to retrieve Debug Information for this routine. It
1439 -- prints Debug Information when run as a Batch Process from SQL*Plus. For
1440 -- the Debug Information to be printed on the Screen, the SQL*Plus parameter
1441 -- 'Serveroutput' should be set to 'ON'
1442
1443 FUNCTION get_debug RETURN VARCHAR2 AS
1444
1445 BEGIN
1446
1447 return(g_dbug);
1448
1449 END get_debug;
1450
1451 /* ----------------------------------------------------------------------- */
1452
1453 END PSB_COPY_DATA_EXTRACT_PVT;