[Home] [Help]
PACKAGE BODY: APPS.PSB_PURGE_DATA_EXTRACT_PVT
Source
1 PACKAGE BODY PSB_PURGE_DATA_EXTRACT_PVT AS
2 /* $Header: PSBPHRXB.pls 115.5 2002/11/12 11:04:01 msuram ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):= 'PSB_PURGE_DATA_EXTRACT_PVT';
5 g_dbug VARCHAR2(2000);
6
7 TYPE TokNameArray IS TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER;
8
9 -- TokValArray contains values for all tokens
10
11 TYPE TokValArray IS TABLE OF VARCHAR2(1000) INDEX BY BINARY_INTEGER;
12
13 -- Number of Message Tokens
14
15 no_msg_tokens NUMBER := 0;
16
17 -- Message Token Name
18
19 msg_tok_names TokNameArray;
20
21 -- Message Token Value
22
23 msg_tok_val TokValArray;
24
25 PROCEDURE message_token
26 ( tokname IN VARCHAR2,
27 tokval IN VARCHAR2
28 );
29
30 PROCEDURE add_message
31 ( appname IN VARCHAR2,
32 msgname IN VARCHAR2
33 );
34
35 /* ----------------------------------------------------------------------- */
36
37 PROCEDURE Purge_Data_Extract
38 ( p_api_version IN NUMBER,
39 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
40 p_commit IN VARCHAR2 := FND_API.G_FALSE,
41 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
42 p_return_status OUT NOCOPY VARCHAR2,
43 p_msg_count OUT NOCOPY NUMBER,
44 p_msg_data OUT NOCOPY VARCHAR2,
45 p_data_extract_id IN NUMBER,
46 p_purge OUT NOCOPY VARCHAR2
47 ) AS
48
49 l_pay_element_id number;
50 l_account_position_set_id number;
51 l_worksheet_cnt number := 0;
52 l_return_status varchar2(1);
53 l_msg_count number;
54 l_msg_data varchar2(2000);
55
56
57 Cursor C_Entity_Set is
58 Select entity_set_id
59 from psb_entity_set
60 where data_extract_id = p_data_extract_id;
61
62 Cursor C_Entity is
63 Select entity_id
64 from psb_entity
65 where data_extract_id = p_data_extract_id;
66
67 Cursor C_Positions is
68 Select position_id
69 from psb_positions
70 where data_extract_id = p_data_extract_id;
71
72 Cursor C_Defaults is
73 Select default_rule_id,
74 entity_id
75 from psb_defaults
76 where data_extract_id = p_data_extract_id;
77
78 Cursor C_Elements is
79 Select pay_element_id
80 from psb_pay_elements
81 where data_extract_id = p_data_extract_id;
82
83 Cursor C_Set_Groups is
84 Select position_set_group_id
85 from psb_element_pos_set_groups
86 where pay_element_id = l_pay_element_id;
87
88 Cursor C_Attribute_Values is
89 Select attribute_id,attribute_value_id
90 from psb_attribute_values
91 where data_extract_id = p_data_extract_id;
92
93 Cursor C_Account_Position_Sets is
94 Select account_position_set_id
95 from psb_account_position_sets
96 where data_extract_id = p_data_extract_id;
97
98 Cursor C_Position_Set_Lines is
99 Select line_sequence_id
100 from psb_account_position_set_lines
101 where account_position_set_id = l_account_position_set_id;
102
103 Cursor C_Review_Group_Rules is
104 Select budget_workflow_rule_id
105 from psb_budget_workflow_rules
106 where data_extract_id = p_data_extract_id;
107
108
109 l_api_name CONSTANT VARCHAR2(30) := 'Purge_Data_Extract';
110 l_api_version CONSTANT NUMBER := 1.0;
111
112 BEGIN
113
114 -- Standard Start of API savepoint
115
116 SAVEPOINT Purge_Data_Extract;
117
118 -- Standard call to check for call compatibility.
119
120 if not FND_API.Compatible_API_Call (l_api_version,
121 p_api_version,
122 l_api_name,
123 G_PKG_NAME)
124 then
125 raise FND_API.G_EXC_UNEXPECTED_ERROR;
126 end if;
127
128 -- Initialize message list if p_init_msg_list is set to TRUE.
129
130 if FND_API.to_Boolean (p_init_msg_list) then
131 FND_MSG_PUB.initialize;
132 end if;
133
134 -- Initialize API return status to success
135
136 p_return_status := FND_API.G_RET_STS_SUCCESS;
137 p_purge := 'PURGE';
138
139 -- API body
140 Begin
141 begin
142 delete psb_positions_i
143 where data_extract_id = p_data_extract_id;
144 commit work;
145 exception
146 when NO_DATA_FOUND then
147 null;
148 end;
149 begin
150 delete psb_salary_i
151 where data_extract_id = p_data_extract_id;
152 commit work;
153 exception
154 when NO_DATA_FOUND then
155 null;
156 end;
157 begin
158 delete psb_employees_i
159 where data_extract_id = p_data_extract_id;
160 commit work;
161
162 exception
163 when NO_DATA_FOUND then
164 null;
165 end;
166
167 begin
168 delete psb_cost_distributions_i
169 where data_extract_id = p_data_extract_id;
170 commit work;
171
172 exception
173 when NO_DATA_FOUND then
174 null;
175 end;
176
177
178 begin
179 delete psb_attribute_values_i
180 where data_extract_id = p_data_extract_id;
181
182 commit work;
183 exception
184 when NO_DATA_FOUND then
185 null;
186 end;
187
188 begin
189 delete psb_employee_assignments_i
190 where data_extract_id = p_data_extract_id;
191 commit work;
192
193 exception
194 when NO_DATA_FOUND then
195 null;
196 end;
197
198 begin
199 delete psb_reentrant_process_status
200 where process_uid = p_data_extract_id
201 and process_type = 'HR DATA EXTRACT';
202 commit work;
203 exception
204 when NO_DATA_FOUND then
205 null;
206 end;
207
208 Select count(*)
209 into l_worksheet_cnt
210 from psb_worksheets
211 where data_extract_id = p_data_extract_id;
212
213 if (l_worksheet_cnt > 0) then
214 FND_MESSAGE.SET_NAME('PSB', 'PSB_DE_CANNOT_BE_DELETED');
215 FND_MSG_PUB.Add;
216 p_purge := 'NO_PURGE';
217 raise FND_API.G_EXC_ERROR;
218 End if;
219 End;
220
221 For C_Entity_Set_Rec in C_Entity_Set
222 Loop
223 Begin
224 Delete psb_entity_assignment
225 where entity_set_id = C_Entity_Set_Rec.entity_set_id;
226 exception When NO_DATA_FOUND then
227 null;
228 End;
229 End Loop;
230 commit work;
231 Begin
232 Delete psb_entity_set
233 where data_extract_id = p_data_extract_id;
234 exception When NO_DATA_FOUND then
235 null;
236 End;
237
238 commit work;
239 For C_Entity_Rec in C_Entity
240 Loop
241 Begin
242 Delete psb_entity_assignment
243 where entity_id = C_Entity_Rec.entity_id;
244 exception When NO_DATA_FOUND then
245 null;
246 End;
247 End Loop;
248 Begin
249 Delete psb_entity
250 where data_extract_id = p_data_extract_id;
251 exception When NO_DATA_FOUND then
252 null;
253 End;
254
255 commit work;
256 For C_Positions_Rec in C_Positions
257 Loop
258 Begin
259 Delete psb_position_assignments
260 where position_id = C_Positions_Rec.position_id;
261 exception When NO_DATA_FOUND then
262 null;
263 End;
264 Begin
265 Delete psb_budget_positions
266 where position_id = C_Positions_Rec.position_id;
267 exception When NO_DATA_FOUND then
268 null;
269 End;
270 Begin
271 Delete psb_position_pay_distributions
272 where position_id = C_Positions_Rec.position_id;
273 exception When NO_DATA_FOUND then
274 null;
275 End;
276 End Loop;
277 Begin
278 Delete psb_positions
279 where data_extract_id = p_data_extract_id;
280 exception When NO_DATA_FOUND then
281 null;
282 End;
283
284 commit work;
285 Begin
286 Delete psb_employees
287 where data_extract_id = p_data_extract_id;
288 exception When NO_DATA_FOUND then
289 null;
290 End;
291
292 commit work;
293 For C_Default_Rec in C_Defaults
294 Loop
295
296 Begin
297 Delete Psb_Set_Relations
298 where default_rule_id = C_Default_Rec.default_rule_id;
299 exception When NO_DATA_FOUND then
300 null;
301 End;
302
303 Begin
304 Delete Psb_Default_Account_Distrs
305 where default_rule_id = C_Default_Rec.default_rule_id;
306 exception When NO_DATA_FOUND then
307 null;
308 End;
309
310 Begin
311 Delete Psb_Default_Assignments
312 where default_rule_id = C_Default_Rec.default_rule_id;
313 exception When NO_DATA_FOUND then
314 null;
315 End;
316
317 Begin
318 Delete Psb_Entity
319 where entity_id = C_Default_Rec.entity_id;
320
321 Delete Psb_allocrule_percents
322 where allocation_rule_id = C_Default_Rec.entity_id;
323
324 exception When NO_DATA_FOUND then
325 null;
326 End;
327
328 End Loop;
329
330 Begin
331 Delete Psb_Defaults
332 where data_extract_id = p_data_extract_id;
333 exception When NO_DATA_FOUND then
334 null;
335 End;
336
337 commit work;
338 For C_Element_rec in C_Elements
339 Loop
340 l_pay_element_id := C_Element_rec.pay_element_id;
341 Begin
342 Delete Psb_pay_element_options
343 where pay_element_id = C_Element_Rec.pay_element_id;
344 exception When NO_DATA_FOUND then
345 null;
346 End;
347
348 Begin
349 Delete Psb_pay_element_rates
350 where pay_element_id = C_Element_Rec.pay_element_id;
351 exception When NO_DATA_FOUND then
352 null;
353 End;
354
355 For C_Set_Group_Rec in C_Set_Groups
356 Loop
357 Begin
358 Delete Psb_Pay_Element_Distributions
359 where position_set_group_id = C_Set_Group_Rec.position_set_group_id;
360 exception When NO_DATA_FOUND then
361 null;
362 End;
363
364 Begin
365 Delete Psb_Set_Relations
366 where position_set_group_id = C_Set_Group_Rec.position_set_group_id;
367 exception When NO_DATA_FOUND then
368 null;
369 End;
370
371 End Loop;
372 Begin
373 Delete Psb_Element_Pos_Set_Groups
374 where pay_element_id = l_pay_element_id;
375 exception When NO_DATA_FOUND then
376 null;
377 End;
378 End Loop;
379
380 Begin
381 Delete Psb_Pay_Elements
382 where data_extract_id = p_data_extract_id;
383 exception When NO_DATA_FOUND then
384 null;
385 End;
386
387 commit work;
388 For C_Account_Position_Set_Rec in C_Account_Position_Sets
389 Loop
390 l_account_position_set_id := C_Account_Position_Set_Rec.account_position_set_id;
391 Begin
392 Delete Psb_Set_Relations
393 where account_position_set_id = C_Account_Position_Set_Rec.account_position_set_id;
394 exception When NO_DATA_FOUND then
395 null;
396 End;
397 For C_Lines_Rec in C_Position_Set_Lines
398 Loop
399 Begin
400 Delete Psb_Position_Set_Line_values
401 where line_sequence_id = C_Lines_Rec.line_sequence_id;
402 exception When NO_DATA_FOUND then
403 null;
404 End;
405 End Loop;
406 Begin
407 Delete Psb_Account_Position_Set_Lines
408 where account_position_set_id = C_Account_Position_Set_Rec.account_position_set_id;
409
410 exception When NO_DATA_FOUND then
411 null;
412 End;
413
414 Begin
415 Delete Psb_Budget_Positions
416 where account_position_set_id = C_Account_Position_Set_Rec.account_position_set_id;
417 exception When NO_DATA_FOUND then
418 null;
419 End;
420 End Loop;
421
422 Begin
423 Delete Psb_Account_Position_Sets
424 where data_extract_id = p_data_extract_id;
425
426 exception When NO_DATA_FOUND then
427 null;
428 End;
429
430 commit work;
431 For C_Attribute_Value_Rec in C_Attribute_Values
432 Loop
433 Begin
434 Delete Psb_Position_Set_Line_Values
435 where attribute_value_id = C_Attribute_Value_Rec.attribute_value_id;
436 exception When NO_DATA_FOUND then
437 null;
438 End;
439 End Loop;
440
441 Begin
442 Delete Psb_Attribute_Values
443 where data_extract_id = p_data_extract_id;
444 exception When NO_DATA_FOUND then
445 null;
446 End;
447
448 commit work;
449
450 For C_Review_Group_Rule_Rec in C_Review_Group_Rules
451 Loop
452 Begin
453 Delete Psb_Set_Relations
454 where budget_workflow_rule_id = C_Review_Group_Rule_Rec.budget_workflow_rule_id;
455
456 exception When NO_DATA_FOUND then
457 null;
458 End;
459 End Loop;
460
461 commit work;
462 Begin
463 Delete Psb_Budget_Workflow_Rules
464 where data_extract_id = p_data_extract_id;
465 exception When NO_DATA_FOUND then
466 null;
467 End;
468
469 Delete Psb_Data_Extracts
470 where data_extract_id = p_data_extract_id;
471
472 commit work;
473 -- End of API body.
474
475 -- Standard check of p_commit.
476
477 if FND_API.to_Boolean (p_commit) then
478 commit work;
479 end if;
480
481 -- Standard call to get message count and if count is 1, get message info.
482
483 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
484 p_data => p_msg_data);
485
486 EXCEPTION
487
488 when FND_API.G_EXC_ERROR then
489
490 /*For Bug No : 2577889 Start*/
491 --rollback to Purge_Data_Extract;
492 rollback;
493 /*For Bug No : 2577889 End*/
494
495 p_return_status := FND_API.G_RET_STS_ERROR;
496
497 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
498 p_data => p_msg_data);
499
500
501 when FND_API.G_EXC_UNEXPECTED_ERROR then
502
503 /*For Bug No : 2577889 Start*/
504 --rollback to Purge_Data_Extract;
505 rollback;
506 /*For Bug No : 2577889 End*/
507
508 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
509
513
510 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
511 p_data => p_msg_data);
512
514 when OTHERS then
515
516 /*For Bug No : 2577889 Start*/
517 --rollback to Purge_Data_Extract;
518 rollback;
519 /*For Bug No : 2577889 End*/
520
521 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) then
522
523 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
524 l_api_name);
525 end if;
526
527 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
528 p_data => p_msg_data);
529
530 END Purge_Data_Extract;
531
532 /*===========================================================================+
533 | PROCEDURE Purge_Data_Extract_CP |
534 +===========================================================================*/
535 --
536 -- This is the execution file for the concurrent program 'Purge Data Extract'
537
538 PROCEDURE Purge_Data_Extract_CP
539 (
540 errbuf OUT NOCOPY VARCHAR2 ,
541 retcode OUT NOCOPY VARCHAR2 ,
542 --
543 p_data_extract_id IN NUMBER
544 )
545 IS
546 --
547 l_api_name CONSTANT VARCHAR2(30) := 'Purge_Data_Extract_CP';
548 l_api_version CONSTANT NUMBER := 1.0 ;
549 --
550 l_data_extract_name VARCHAR2(30);
551 l_error_api_name VARCHAR2(2000);
552 l_return_status VARCHAR2(1) ;
553 l_msg_count NUMBER ;
554 l_msg_data VARCHAR2(2000) ;
555 l_msg_index_out NUMBER;
556 l_purge VARCHAR2(20);
557
558 BEGIN
559
560
561 Select data_extract_name
562 into l_data_extract_name
563 from psb_data_extracts
564 where data_extract_id = p_data_extract_id;
565
566 message_token('DATA_EXTRACT_NAME',l_data_extract_name);
567 add_message('PSB', 'PSB_DATA_EXTRACT');
568
569 PSB_PURGE_DATA_EXTRACT_PVT.Purge_Data_Extract
570 (p_api_version => 1.0,
571 p_init_msg_list => FND_API.G_TRUE,
572 p_return_status => l_return_status,
573 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
574 p_msg_count => l_msg_count,
575 p_msg_data => l_msg_data,
576 p_data_extract_id => p_data_extract_id,
577 p_purge => l_purge );
578
579
580 if l_purge <> 'PURGE' THEN
581 message_token('PROCESS', 'Delete Data Extract');
582 add_message('PSB', 'PSB_EXTRACT_FAILURE_MESSAGE');
583 raise FND_API.G_EXC_ERROR;
584 end if;
585
586 if l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
587 raise FND_API.G_EXC_ERROR;
588 END IF;
589 --
590 PSB_MESSAGE_S.Print_Success;
591 retcode := 0 ;
592 --
593 COMMIT WORK;
594
595 EXCEPTION
596 --
597 WHEN OTHERS THEN
598 --
599 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
600 --
601 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,
602 l_api_name ) ;
603 END IF ;
604 --
605 PSB_MESSAGE_S.Print_Error( p_mode => FND_FILE.OUTPUT,
606 p_print_header => FND_API.G_TRUE);
607 --
608 retcode := 2 ;
609 --
610 END Purge_Data_Extract_CP;
611
612 /* ----------------------------------------------------------------------- */
613
614 -- Add Token and Value to the Message Token array
615
616 PROCEDURE message_token(tokname IN VARCHAR2,
617 tokval IN VARCHAR2) AS
618
619 BEGIN
620
621 if no_msg_tokens is null then
622 no_msg_tokens := 1;
623 else
624 no_msg_tokens := no_msg_tokens + 1;
625 end if;
626
627 msg_tok_names(no_msg_tokens) := tokname;
628 msg_tok_val(no_msg_tokens) := tokval;
629
630 END message_token;
631
632 /* ----------------------------------------------------------------------- */
633
634 -- Define a Message Token with a Value and set the Message Name
635
636 -- Calls FND_MESSAGE server package to set the Message Stack. This message is
637 -- retrieved by the calling program.
638
639 PROCEDURE add_message(appname IN VARCHAR2,
640 msgname IN VARCHAR2) AS
641
642 i BINARY_INTEGER;
643
644 BEGIN
645
646 if ((appname is not null) and
647 (msgname is not null)) then
648
649 FND_MESSAGE.SET_NAME(appname, msgname);
650
651 if no_msg_tokens is not null then
652
653 for i in 1..no_msg_tokens loop
654 FND_MESSAGE.SET_TOKEN(msg_tok_names(i), msg_tok_val(i));
655 end loop;
656
657 end if;
658
659 FND_MSG_PUB.Add;
660
661 end if;
662
663 -- Clear Message Token stack
664
665 no_msg_tokens := 0;
666
667 END add_message;
668
669 /* ----------------------------------------------------------------------- */
670
671 -- Get Debug Information
672
673 -- This Module is used to retrieve Debug Information for this routine. It
674 -- prints Debug Information when run as a Batch Process from SQL*Plus. For
675 -- the Debug Information to be printed on the Screen, the SQL*Plus parameter
676 -- 'Serveroutput' should be set to 'ON'
677
678 FUNCTION get_debug RETURN VARCHAR2 AS
679
680 BEGIN
681
682 return(g_dbug);
683
684 END get_debug;
685
686 /* ----------------------------------------------------------------------- */
687
688 END PSB_PURGE_DATA_EXTRACT_PVT;