[Home] [Help]
PACKAGE BODY: APPS.PSB_WRHR_EXTRACT_PROCESS
Source
1 PACKAGE BODY PSB_WRHR_EXTRACT_PROCESS AS
2 /* $Header: PSBWHRCB.pls 120.30 2005/12/21 12:16:39 maniskum ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):= 'PSB_WRHR_EXTRACT_PROCESS';
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 PROCEDURE Pre_Create_Data_Extract
36 ( p_api_version IN NUMBER,
37 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
38 p_commit IN VARCHAR2 := FND_API.G_FALSE,
39 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
40 p_return_status OUT NOCOPY VARCHAR2,
41 p_msg_count OUT NOCOPY NUMBER,
42 p_msg_data OUT NOCOPY VARCHAR2,
43 p_data_extract_id IN NUMBER);
44
45
46 g_business_group_id number := 0;
47 g_data_extract_id number := 0;
48 g_data_extract_name varchar2(30) := 'X';
49 g_data_extract_method varchar2(30) := 'X';
50 g_set_of_books_id number := 0;
51 g_req_data_as_of_date date;
52 g_copy_defaults_flag varchar2(1) := 'N';
53 g_copy_defaults_status varchar2(1) := 'I';
54 g_populate_interface_flag varchar2(1) := 'N';
55 g_populate_interface_status varchar2(1) := 'I';
56 g_validate_data_flag varchar2(1) := 'N';
57 g_validate_data_status varchar2(1) := 'I';
58 g_populate_data_flag varchar2(1) := 'N';
59 g_populate_data_status varchar2(1) := 'I';
60 g_default_data_flag varchar2(1) := 'N';
61 g_default_data_status varchar2(1) := 'I';
62 g_copy_data_extract_id number := 0;
63 g_copy_salary_flag varchar2(1);
64 g_position_id_flex_num number;
65 gc_return_status varchar2(1);
66 gi_return_status varchar2(1);
67 gv_return_status varchar2(1);
68 gp_return_status varchar2(1);
69
70
71 PROCEDURE Perform_Data_Extract
72 ( p_api_version IN NUMBER,
73 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
74 p_commit IN VARCHAR2 := FND_API.G_FALSE,
75 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
76 p_return_status OUT NOCOPY VARCHAR2,
77 p_msg_count OUT NOCOPY NUMBER,
78 p_msg_data OUT NOCOPY VARCHAR2,
79 p_data_extract_id IN NUMBER
80 ) AS
81
82 l_api_name CONSTANT VARCHAR2(30) := 'Perform_Data_Extract';
83 l_api_version CONSTANT NUMBER := 1.0;
84
85 l_return_status varchar2(1);
86 l_msg_count number;
87 l_msg_data varchar2(2000);
88 l_validate_flag varchar2(1);
89 l_req_id number;
90 l_rep_req_id number;
91 l_reqid NUMBER;
92 l_userid NUMBER ;
93
94 Cursor C_DataExtract is
95 SELECT BUSINESS_GROUP_ID,
96 SET_OF_BOOKS_ID,
97 DATA_EXTRACT_METHOD,
98 REQ_DATA_AS_OF_DATE,
99 NVL(POPULATE_INTERFACE_FLAG,'I') POPULATE_INTERFACE_FLAG,
100 NVL(POPULATE_INTERFACE_STATUS,'I') POPULATE_INTERFACE_STATUS,
101 NVL(VALIDATE_DATA_FLAG,'I') VALIDATE_DATA_FLAG,
102 NVL(VALIDATE_DATA_STATUS,'I') VALIDATE_DATA_STATUS,
103 NVL(POPULATE_DATA_FLAG,'I') POPULATE_DATA_FLAG,
104 NVL(POPULATE_DATA_STATUS,'I') POPULATE_DATA_STATUS,
105 NVL(DEFAULT_DATA_FLAG,'I') DEFAULT_DATA_FLAG,
106 NVL(DEFAULT_DATA_STATUS,'I') DEFAULT_DATA_STATUS,
107 NVL(COPY_DEFAULTS_FLAG,'I') COPY_DEFAULTS_FLAG,
108 NVL(COPY_DEFAULTS_STATUS,'I') COPY_DEFAULTS_STATUS,
109 COPY_DEFAULTS_EXTRACT_ID,
110 COPY_SALARY_FLAG,
111 POSITION_ID_FLEX_NUM
112 FROM PSB_DATA_EXTRACTS
113 WHERE data_extract_id = p_data_extract_id;
114
115 BEGIN
116
117 -- Standard call to check for call compatibility.
118
119 if not FND_API.Compatible_API_Call (l_api_version,
120 p_api_version,
121 l_api_name,
122 G_PKG_NAME)
123 then
124 raise FND_API.G_EXC_UNEXPECTED_ERROR;
125 end if;
126
127 -- Initialize message list if p_init_msg_list is set to TRUE.
128
129 if FND_API.to_Boolean (p_init_msg_list) then
130 FND_MSG_PUB.initialize;
131 end if;
132
133 -- Initialize API return status to success
134
135 p_return_status := FND_API.G_RET_STS_SUCCESS;
136
137 -- API body
138
139
140 For C_DataRec in C_DataExtract
141 Loop
142 l_validate_flag := 'N';
143 if (C_DataRec.validate_data_flag = 'Y') then
144 if (C_DataRec.validate_data_status <> 'C') then
145 l_validate_flag := 'Y';
146 end if;
147 end if;
148
149 if (C_DataRec.populate_data_flag = 'Y') then
150 if (C_DataRec.populate_data_status <> 'C') then
151
152 Update Psb_Data_Extracts
153 set data_extract_status = 'I'
154 where data_extract_id = p_data_extract_id;
155
156 end if;
157
158 end if;
159
160 if (C_DataRec.default_data_flag = 'Y') then
161 if (C_DataRec.default_data_status <> 'C') then
162 PSB_HR_POPULATE_DATA_PVT.Apply_Defaults
163 ( p_api_version => 1.0,
164 p_init_msg_list => FND_API.G_FALSE,
165 p_commit => FND_API.G_FALSE,
166 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
167 p_return_status => l_return_status,
168 p_msg_count => l_msg_count,
169 p_msg_data => l_msg_data,
170 p_data_extract_id => p_data_extract_id,
171 p_extract_method => C_DataRec.data_extract_method
172 );
173
174 if l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
175 message_token('PROCESS', 'Apply Defaults');
176 add_message('PSB', 'PSB_EXTRACT_FAILURE_MESSAGE');
177 p_return_status := FND_API.G_RET_STS_ERROR;
178 --raise FND_API.G_EXC_ERROR;
179 else
180 message_token('PROCESS', 'Apply Defaults');
181 add_message('PSB', 'PSB_EXTRACT_SUCCESS_MESSAGE');
182 end if;
183 end if;
184 end if;
185
186 End Loop;
187
188 -- End of API body.
189 --Add message stack to PSB_ERROR_MESSAGES
190
191 DELETE FROM PSB_ERROR_MESSAGES
192 WHERE process_id = p_data_extract_id;
193
194 l_reqid := FND_GLOBAL.CONC_REQUEST_ID;
195 l_userid := FND_GLOBAL.USER_ID;
196
197 FND_MSG_PUB.Count_And_Get ( p_count => l_msg_count,
198 p_data => l_msg_data );
199 IF l_msg_count > 0 THEN
200
201 l_msg_data := FND_MSG_PUB.Get
202 (p_msg_index => FND_MSG_PUB.G_NEXT,
203 p_encoded => FND_API.G_FALSE);
204
205 PSB_MESSAGE_S.INSERT_ERROR
206 (p_source_process => 'DATA_EXTRACT_VALIDATION',
207 p_process_id => p_data_extract_id,
208 p_msg_count => l_msg_count,
209 p_msg_data => l_msg_data);
210
211 END IF;
212
213 -- Standard check of p_commit.
214 if FND_API.to_Boolean (p_commit) then
215 commit work;
216 end if;
217
218 if (l_msg_count > 0) then
219
220 l_req_id := FND_GLOBAL.CONC_REQUEST_ID;
221
222 l_rep_req_id := Fnd_Request.Submit_Request
223 (application => 'PSB' ,
224 program => 'PSBRPERR' ,
225 description => 'Error Messages Listing' ,
226 start_time => NULL ,
227 sub_request => FALSE ,
228 argument1 => NULL ,
229 argument2 => p_data_extract_id ,
230 argument3 => 'DATA_EXTRACT_VALIDATION'
231 );
232 --
233 if l_rep_req_id = 0 then
234 --
235 fnd_message.set_name('PSB', 'PSB_FAIL_TO_SUBMIT_REQUEST');
236 raise FND_API.G_EXC_ERROR ;
237 --
238 end if;
239
240 end if;
241
242 -- Standard call to get message count and if count is 1, get message info.
243
244 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
245 p_data => p_msg_data);
246
247 EXCEPTION
248
249 when FND_API.G_EXC_ERROR then
250
251 p_return_status := FND_API.G_RET_STS_ERROR;
252
253 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
254 p_data => p_msg_data);
255
256
257 when FND_API.G_EXC_UNEXPECTED_ERROR then
258
259 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
260
261 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
262 p_data => p_msg_data);
263
264
265 when OTHERS then
266
267 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
268
269 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) then
270
271 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
272 l_api_name);
273 end if;
274
275 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
276 p_data => p_msg_data);
277
278 END Perform_Data_Extract;
279
280
281 PROCEDURE Interface_Purge
282 ( p_api_version IN NUMBER,
283 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
284 p_commit IN VARCHAR2 := FND_API.G_FALSE,
285 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
286 /* Start bug #4386374 */
287 p_data_extract_id IN NUMBER := null, -- Fixed for Bug#4683895
288 p_populate_interface_flag IN VARCHAR2 := null, -- Added this for Bug#4683895
289 /* End bug #4386374 */
290 p_return_status OUT NOCOPY VARCHAR2,
291 p_msg_count OUT NOCOPY NUMBER,
292 p_msg_data OUT NOCOPY VARCHAR2)
293 as
294 /* Start bug #4386374 */
295 -- Commented the following cursor and redefined.
296 --
297 -- Cursor C_Interface is
298 -- Select data_extract_id
299 -- from psb_data_extracts
300 -- where decode(copy_defaults_flag,'Y',copy_defaults_status,'C') = 'C'
301 -- and decode(populate_interface_flag,'Y',populate_interface_status,'C') = 'C'
302 -- and decode(validate_data_flag,'Y',validate_data_status,'C') = 'C'
303 -- and decode(populate_data_flag,'Y',populate_data_status,'C') = 'C'
304 -- and decode(default_data_flag,'Y',default_data_status,'C') = 'C'
305 -- and nvl(data_extract_status,'I') = 'C'
306 -- and nvl(rerun_flag,'X') <> 'Y'
307 -- order by data_extract_id;
308
309
310 CURSOR C_Interface is
311 SELECT data_extract_id
312 FROM psb_data_extracts
313 WHERE (data_extract_id = (DECODE(p_populate_interface_flag, 'Y', p_data_extract_id, 0))
314 OR (data_extract_id <> DECODE(p_populate_interface_flag, 'Y', 0, p_data_extract_id)
315 AND populate_interface_status = 'C'
316 AND validate_data_status = 'C'
317 AND populate_data_status = 'C'
318 AND DECODE(default_data_flag,'Y',default_data_status,'C') = 'C' -- Added this for Bug#4683895
319 AND DECODE(copy_defaults_flag,'Y',copy_defaults_status,'C') = 'C' -- Added this for Bug#4683895
320 AND data_extract_status = 'C'))
321 AND NVL(rerun_flag,'X') <> 'Y' -- Added this for Bug#4683895
322 ORDER BY data_extract_id;
323
324 /* End bug #4386374 */
325
326 l_api_name CONSTANT VARCHAR2(30) := 'Interface_Purge';
327 l_api_version CONSTANT NUMBER := 1.0;
328
329 l_return_status varchar2(1);
330 l_msg_count number;
331 l_msg_data varchar2(2000);
332
333 Begin
334
335 -- Standard call to check for call compatibility.
336
337 if not FND_API.Compatible_API_Call (l_api_version,
338 p_api_version,
339 l_api_name,
340 G_PKG_NAME)
341 then
342 raise FND_API.G_EXC_UNEXPECTED_ERROR;
343 end if;
344
345 -- Initialize message list if p_init_msg_list is set to TRUE.
346
347 if FND_API.to_Boolean (p_init_msg_list) then
348 FND_MSG_PUB.initialize;
349 end if;
350
351 -- Initialize API return status to success
352
353 p_return_status := FND_API.G_RET_STS_SUCCESS;
354
355 -- API body
356
357 For C_Interface_Rec in C_Interface
358 Loop
359 begin
360 delete psb_positions_i
361 where data_extract_id = C_Interface_Rec.data_extract_id;
362
363 commit work;
364
365 exception
366 when NO_DATA_FOUND then
367 null;
368 end;
369 begin
370 delete psb_salary_i
371 where data_extract_id = C_Interface_Rec.data_extract_id;
372
373 commit work;
374 exception
375 when NO_DATA_FOUND then
376 null;
377 end;
378 begin
379 delete psb_employees_i
380 where data_extract_id = C_Interface_Rec.data_extract_id;
381
382 commit work;
383 exception
384 when NO_DATA_FOUND then
385 null;
386 end;
387
388 begin
389 delete psb_cost_distributions_i
390 where data_extract_id = C_Interface_Rec.data_extract_id;
391
392 commit work;
393 exception
394 when NO_DATA_FOUND then
395 null;
396 end;
397
398 begin
399 delete psb_attribute_values_i
400 where data_extract_id = C_Interface_Rec.data_extract_id;
401
402 commit work;
403 exception
404 when NO_DATA_FOUND then
405 null;
406 end;
407
408 begin
409 delete psb_employee_assignments_i
410 where data_extract_id = C_Interface_Rec.data_extract_id;
411
412 commit work;
413 exception
414 when NO_DATA_FOUND then
415 null;
416 end;
417
418 /* Commented for bug 3325056 .. Start
419 begin
420 delete psb_reentrant_process_status
421 where process_uid = C_Interface_Rec.data_extract_id
422 and process_type = 'HR DATA EXTRACT';
423
424 commit work;
425 exception
426 when NO_DATA_FOUND then
427 null;
428 end;
429 bug 3325056 end .. */
430
431 End Loop;
432
433 -- Standard call to get message count and if count is 1, get message info.
434
435 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
436 p_data => p_msg_data);
437
438 EXCEPTION
439
440 when FND_API.G_EXC_ERROR then
441
442 p_return_status := FND_API.G_RET_STS_ERROR;
443
444 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
445 p_data => p_msg_data);
446
447
448 when FND_API.G_EXC_UNEXPECTED_ERROR then
449
450 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
451
452 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
453 p_data => p_msg_data);
454
455
456 when OTHERS then
457
458 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
459
460 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) then
461
462 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
463 l_api_name);
464 end if;
465
466 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
467 p_data => p_msg_data);
468
469
470 End Interface_Purge;
471
472 -- de by org
473 -- This procedure inserts into psb_data_extract_orgs all organizations
474 -- belonging to a Business Group.
475
476 PROCEDURE Insert_Organizations
477 ( p_api_version IN NUMBER,
478 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
479 p_commit IN VARCHAR2 := FND_API.G_FALSE,
480 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
481 p_data_extract_id IN NUMBER,
482 p_as_of_date IN DATE,
483 p_business_group_id IN NUMBER,
484 p_return_status OUT NOCOPY VARCHAR2,
485 p_msg_count OUT NOCOPY NUMBER,
486 p_msg_data OUT NOCOPY VARCHAR2)
487 as
488 l_api_name CONSTANT VARCHAR2(30) := 'Insert_Organizations';
489 l_api_version CONSTANT NUMBER := 1.0;
490 l_return_status varchar2(1);
491 l_msg_count number;
492 l_msg_data varchar2(2000);
493 l_organization_id number;
494 l_organization_name varchar2(240);
495 l_completion_status varchar2(1);
496 l_completion_time DATE;
497 l_last_update_date DATE;
498 l_last_updated_BY number;
499 l_last_update_login number;
500 l_creation_date DATE;
501 l_created_by number;
502
503 Begin
504
505 -- Standard call to check for call compatibility.
506
507 if not FND_API.Compatible_API_Call (l_api_version,
508 p_api_version,
509 l_api_name,
510 G_PKG_NAME)
511 then
512 raise FND_API.G_EXC_UNEXPECTED_ERROR;
513 end if;
514
515 -- Initialize message list if p_init_msg_list is set to TRUE.
516
517 if FND_API.to_Boolean (p_init_msg_list) then
518 FND_MSG_PUB.initialize;
519 end if;
520
521 -- Initialize API return status to success
522
523 p_return_status := FND_API.G_RET_STS_SUCCESS;
524
525 -- API body
526
527
528 -- The following statement will insert the organization details into
529 -- PSB_DATA_EXTRACT_ORGS table.
530 -- This is used to ensure that all the organizations available
531 -- in HR are extracted into PSB_DATA_EXTRACT_ORGS table.
532
533
534 IF (p_data_extract_id IS NOT NULL) THEN
535
536 INSERT INTO PSB_DATA_EXTRACT_ORGS
537 (
538 data_extract_id,
539 organization_id,
540 organization_name,
541 select_flag, -- For Bug: 4248378. Select_flag has to be inserted initially.
542 completion_status,
543 completion_time,
544 last_update_date,
545 last_updated_BY,
546 last_update_login,
547 creation_date,
548 created_by
549 )
550 SELECT
551 de.data_extract_id,
552 org.organization_id,
553 org.name,
554 'N', -- For Bug: 4248378. Select_flag has to be inserted initially.
555 NULL, --For Bug No:3071201. For every new organization first insert Null status into Psb_Data_Extract_Orgs table.
556 NULL,
557 SYSDATE,
558 FND_GLOBAL.USER_ID,
559 FND_GLOBAL.LOGIN_ID,
560 SYSDATE,
561 FND_GLOBAL.USER_ID
562 FROM PSB_DATA_EXTRACTS de,
563 PER_ORGANIZATION_UNITS org
564 WHERE de.data_extract_id = p_data_extract_id
565 AND de.business_group_id = org.business_group_id
566 AND p_as_of_date between date_from and nvl(date_to, p_as_of_date)
567 AND NOT EXISTS (
568 SELECT 1
569 FROM PSB_DATA_EXTRACT_ORGS C
570 WHERE c.data_extract_id = p_data_extract_id
571 AND c.organization_id = org.organization_id);
572 END IF;
573
574 -- Standard call to get message count and if count is 1, get message info.
575
576 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
577 p_data => p_msg_data);
578 COMMIT WORK;
579
580 EXCEPTION
581 when FND_API.G_EXC_ERROR then
582
583 p_return_status := FND_API.G_RET_STS_ERROR;
584
585 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
586 p_data => p_msg_data);
587
588
589 when FND_API.G_EXC_UNEXPECTED_ERROR then
590
591 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
592
593 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
594 p_data => p_msg_data);
595
596
597 when OTHERS then
598
599 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
600
601 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) then
602
603 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
604 l_api_name);
605 end if;
606
607 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
608 p_data => p_msg_data);
609
610
611 End insert_organizations;
612
613
614 /*===========================================================================+
615 | PROCEDURE Perform_Data_Extract_CP |
616 +===========================================================================*/
617 --
618 -- This is the execution file for the concurrent program 'Extract Data
619 -- From Human Resources'
620 --
621 PROCEDURE Perform_Data_Extract_CP
622 (
623 errbuf OUT NOCOPY VARCHAR2 ,
624 retcode OUT NOCOPY VARCHAR2 ,
625 --
626 p_data_extract_id IN NUMBER
627 )
628 IS
629 --
630 l_api_name CONSTANT VARCHAR2(30) := 'Perform_Data_Extract_CP';
631 l_api_version CONSTANT NUMBER := 1.0 ;
632 --
633 l_data_extract_name VARCHAR2(30);
634
635 -- de by org
636 l_extract_by_org VARCHAR2(1);
637 l_data_extract_id VARCHAR2(1);
638 l_data_extract_status VARCHAR2(1);
639
640 l_error_api_name VARCHAR2(2000);
641 l_return_status VARCHAR2(1) ;
642 l_msg_count NUMBER ;
643 l_msg_data VARCHAR2(2000) ;
644 l_msg_index_out NUMBER;
645
646 BEGIN
647
648 -- de by org
649 Select data_extract_name,
650 nvl(extract_by_organization_flag,'N'),
651 nvl(data_extract_status,'I')
652 into l_data_extract_name, l_extract_by_org, l_data_extract_status
653 from psb_data_extracts
654 where data_extract_id = p_data_extract_id;
655
656 message_token('DATA_EXTRACT_NAME',l_data_extract_name);
657 add_message('PSB', 'PSB_DATA_EXTRACT');
658
659 FND_FILE.Put_Line( FND_FILE.OUTPUT,
660 'Processing the given Data Extract : ' ||l_data_extract_name);
661
662 PSB_CONCURRENCY_CONTROL_PVT.Enforce_Concurrency_Control
663 (p_api_version => 1.0 ,
664 p_return_status => l_return_status,
665 p_concurrency_class => 'DATAEXTRACT_CREATION',
666 p_concurrency_entity_name => 'DATA_EXTRACT',
667 p_concurrency_entity_id => p_data_extract_id);
668
669 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
670 raise FND_API.G_EXC_ERROR;
671 end if;
672
673
674 PSB_WRHR_EXTRACT_PROCESS.Perform_Data_Extract
675 ( p_api_version => 1.0,
676 p_init_msg_list => FND_API.G_TRUE,
677 p_commit => FND_API.G_TRUE,
678 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
679 p_return_status => l_return_status,
680 p_msg_count => l_msg_count,
681 p_msg_data => l_msg_data,
682 p_data_extract_id => p_data_extract_id
683 ) ;
684
685 if l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
686 raise FND_API.G_EXC_ERROR;
687 END IF;
688 --
689
690 PSB_CONCURRENCY_CONTROL_PVT.Release_Concurrency_Control
691 (p_api_version => 1.0,
692 p_return_status => l_return_status,
693 p_concurrency_class => 'DATAEXTRACT_CREATION',
694 p_concurrency_entity_name => 'DATA_EXTRACT',
695 p_concurrency_entity_id => p_data_extract_id);
696
697 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
698 raise FND_API.G_EXC_ERROR;
699 end if;
700
701 PSB_MESSAGE_S.Print_Success;
702 PSB_MESSAGE_S.Print_Error( p_mode => FND_FILE.OUTPUT,
703 p_print_header => FND_API.G_FALSE);
704 retcode := 0 ;
705 --
706 COMMIT WORK;
707
708 EXCEPTION
709
710 WHEN FND_API.G_EXC_ERROR THEN
711 --
712
713 PSB_CONCURRENCY_CONTROL_PVT.Release_Concurrency_Control
714 (p_api_version => 1.0 ,
715 p_return_status => l_return_status,
716 p_concurrency_class => 'DATAEXTRACT_CREATION',
717 p_concurrency_entity_name => 'DATA_EXTRACT',
718 p_concurrency_entity_id => p_data_extract_id);
719
720
721 PSB_MESSAGE_S.Print_Error ( p_mode => FND_FILE.LOG ,
722 p_print_header => FND_API.G_TRUE );
723 retcode := 2 ;
724 COMMIT WORK ;
725 --
726 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
727 --
728
729 PSB_CONCURRENCY_CONTROL_PVT.Release_Concurrency_Control
730 (p_api_version => 1.0 ,
731 p_return_status => l_return_status,
732 p_concurrency_class => 'DATAEXTRACT_CREATION',
733 p_concurrency_entity_name => 'DATA_EXTRACT',
734 p_concurrency_entity_id => p_data_extract_id);
735
736 PSB_MESSAGE_S.Print_Error ( p_mode => FND_FILE.LOG ,
737 p_print_header => FND_API.G_TRUE );
738 retcode := 2 ;
739 COMMIT WORK ;
740 --
741 WHEN OTHERS THEN
742
743 PSB_CONCURRENCY_CONTROL_PVT.Release_Concurrency_Control
744 (p_api_version => 1.0 ,
745 p_return_status => l_return_status,
746 p_concurrency_class => 'DATAEXTRACT_CREATION',
747 p_concurrency_entity_name => 'DATA_EXTRACT',
748 p_concurrency_entity_id => p_data_extract_id);
749
750 --
751 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
752 --
753 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,
754 l_api_name ) ;
755 END IF ;
756 --
757 PSB_MESSAGE_S.Print_Error ( p_mode => FND_FILE.LOG ,
758 p_print_header => FND_API.G_TRUE );
759 --
760 retcode := 2 ;
761 COMMIT WORK ;
762
763 END Perform_Data_Extract_CP;
764
765 /*===========================================================================+
766 | PROCEDURE Assign_Position_Defaults_CP |
767 +===========================================================================*/
768 --
769 -- This is the execution file for the concurrent program 'Assign Position
770 -- Defaults'
771 -- 1308558 Mass Position Assignment Rules Enhancement
772 -- added the extra parameter p_ruleset_id for passing the
773 -- id for the default ruleset
774
775 -- Bug # 4683895
776 -- Fixed this concurrent program to work for both Pre and Post MPA Code.
777 -- There are many subtle changes. So please see the difference with
778 -- the previous version.
779
780 PROCEDURE Assign_Position_Defaults_CP
781 (
782 errbuf OUT NOCOPY VARCHAR2 ,
783 retcode OUT NOCOPY VARCHAR2 ,
784 --
785 p_data_extract_id IN NUMBER ,
786 p_request_set_flag IN VARCHAR2 := 'N',
787 p_ruleset_id IN NUMBER := NULL
788 )
789 IS
790 --
791 l_api_name CONSTANT VARCHAR2(30) := 'Assign_Position_Defaults_CP';
792 l_api_version CONSTANT NUMBER := 1.0 ;
793 --
794 l_data_extract_name VARCHAR2(30);
795 l_error_api_name VARCHAR2(2000);
796 l_return_status VARCHAR2(1) ;
797 l_msg_count NUMBER ;
798 l_msg_data VARCHAR2(2000) ;
799 l_msg_index_out NUMBER;
800 l_default_data_flag VARCHAR2(1);
801 l_default_data_status VARCHAR2(1);
802 l_data_extract_method VARCHAR2(30);
803
804 -- fix for bug 1787566
805 cursor c_extract is
806 select nvl(default_data_flag,'I') default_data_flag,
807 nvl(default_data_status,'I') default_data_status,
808 data_extract_method,
809 data_extract_name
810 from PSB_DATA_EXTRACTS
811 where data_extract_id = p_data_extract_id;
812
813 BEGIN
814
815 for c_extract_rec in c_extract loop
816 l_default_data_flag := c_extract_rec.default_data_flag;
817 l_default_data_status := c_extract_rec.default_data_status;
818 l_data_extract_method := c_extract_rec.data_extract_method;
819 l_data_extract_name := c_extract_rec.data_extract_name;
820 end loop;
821
822 message_token('DATA_EXTRACT_NAME',l_data_extract_name);
823 add_message('PSB', 'PSB_DATA_EXTRACT');
824
825 if ( ((l_default_data_flag = 'Y') and (p_request_set_flag = 'Y')) -- for Pre MPA DE
826 OR (p_request_set_flag = 'N') -- for Pre MPA SRS and for Post MPA Rule Set form button.
827 ) then
828
829 PSB_BUDGET_POSITION_PVT.Populate_Budget_Positions
830 (p_api_version => 1.0,
831 p_commit => FND_API.G_TRUE,
832 p_return_status => l_return_status,
833 p_msg_count => l_msg_count,
834 p_msg_data => l_msg_data,
835 p_data_extract_id => p_data_extract_id);
836
837 if l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
838 raise FND_API.G_EXC_ERROR;
839 end if;
840 end if;
841
842 if ( (p_request_set_flag = 'Y') and (l_default_data_flag = 'Y') ) then -- for Pre MPA DE
843
844 if (l_default_data_status <> 'C') then
845
846 PSB_CONCURRENCY_CONTROL_PVT.Enforce_Concurrency_Control
847 (p_api_version => 1.0 ,
848 p_return_status => l_return_status,
849 p_concurrency_class => 'DATAEXTRACT_CREATION',
850 p_concurrency_entity_name => 'DATA_EXTRACT',
851 p_concurrency_entity_id => p_data_extract_id);
852
853 if l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
854 raise FND_API.G_EXC_ERROR;
855 end if;
856
857 -- Bug 4649730 removed the extra in parameter p_ruleset_id
858 PSB_HR_POPULATE_DATA_PVT.Apply_Defaults
859 ( p_api_version => 1.0,
860 p_init_msg_list => FND_API.G_FALSE,
861 p_commit => FND_API.G_FALSE,
862 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
863 p_return_status => l_return_status,
864 p_msg_count => l_msg_count,
865 p_msg_data => l_msg_data,
866 p_data_extract_id => p_data_extract_id,
867 p_extract_method => l_data_extract_method
868 );
869
870 if l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
871 raise FND_API.G_EXC_ERROR;
872 END IF;
873
874 PSB_CONCURRENCY_CONTROL_PVT.Release_Concurrency_Control
875 (p_api_version => 1.0 ,
876 p_return_status => l_return_status,
877 p_concurrency_class => 'DATAEXTRACT_CREATION',
878 p_concurrency_entity_name => 'DATA_EXTRACT',
879 p_concurrency_entity_id => p_data_extract_id);
880
881 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
882 raise FND_API.G_EXC_ERROR;
883 end if;
884
885 end if;
886
887 end if;
888
889 -- for Pre MPA SRS and
890 -- for Post MPA Rule Set form button.
891 if (p_request_set_flag = 'N') then
892
893 PSB_POSITIONS_PVT.Create_Default_Assignments(
894 p_api_version => 1.0,
895 p_init_msg_list => FND_API.G_TRUE,
896 p_commit => FND_API.G_TRUE,
897 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
898 p_return_status => l_return_status,
899 p_msg_count => l_msg_count,
900 p_msg_data => l_msg_data,
901 p_data_extract_id => p_data_extract_id,
902 p_ruleset_id => p_ruleset_id) ; --1308558
903
904 if l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
905 raise FND_API.G_EXC_ERROR;
906 END IF;
907
908 end if;
909 --
910
911 PSB_MESSAGE_S.Print_Success;
912 retcode := 0 ;
913 --
914 COMMIT WORK;
915
916 EXCEPTION
917
918 WHEN OTHERS THEN
919
920 PSB_CONCURRENCY_CONTROL_PVT.Release_Concurrency_Control
921 (p_api_version => 1.0 ,
922 p_return_status => l_return_status,
923 p_concurrency_class => 'DATAEXTRACT_CREATION',
924 p_concurrency_entity_name => 'DATA_EXTRACT',
925 p_concurrency_entity_id => p_data_extract_id);
926
927 --
928 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
929 --
930 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,
931 l_api_name ) ;
932 END IF ;
933 --
934 PSB_MESSAGE_S.Print_Error( p_mode => FND_FILE.LOG,
935 p_print_header => FND_API.G_TRUE);
936 --
937 retcode := 2 ;
938 --
939 END Assign_Position_Defaults_CP;
940
941 /* ----------------------------------------------------------------------- */
942
943 -- Add Token and Value to the Message Token array
944
945 PROCEDURE message_token(tokname IN VARCHAR2,
946 tokval IN VARCHAR2) AS
947
948 BEGIN
949
950 if no_msg_tokens is null then
951 no_msg_tokens := 1;
952 else
953 no_msg_tokens := no_msg_tokens + 1;
954 end if;
955
956 msg_tok_names(no_msg_tokens) := tokname;
957 msg_tok_val(no_msg_tokens) := tokval;
958
959 END message_token;
960
961 /* ----------------------------------------------------------------------- */
962
963 -- Define a Message Token with a Value and set the Message Name
964
965 -- Calls FND_MESSAGE server package to set the Message Stack. This message is
966 -- retrieved by the calling program.
967
968 PROCEDURE add_message(appname IN VARCHAR2,
969 msgname IN VARCHAR2) AS
970
971 i BINARY_INTEGER;
972
973 BEGIN
974
975 if ((appname is not null) and
976 (msgname is not null)) then
977
978 FND_MESSAGE.SET_NAME(appname, msgname);
979
980 if no_msg_tokens is not null then
981
982 for i in 1..no_msg_tokens loop
983 FND_MESSAGE.SET_TOKEN(msg_tok_names(i), msg_tok_val(i));
984 end loop;
985
986 end if;
987
988 FND_MSG_PUB.Add;
989
990 end if;
991
992 -- Clear Message Token stack
993
994 no_msg_tokens := 0;
995
996 END add_message;
997
998 PROCEDURE Pre_Create_Data_Extract( p_api_version IN NUMBER,
999 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1000 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1001 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1002 p_return_status OUT NOCOPY VARCHAR2,
1003 p_msg_count OUT NOCOPY NUMBER,
1004 p_msg_data OUT NOCOPY VARCHAR2,
1005 p_data_extract_id IN NUMBER)
1006 IS
1007 Cursor C_Data_Extract is
1008 SELECT BUSINESS_GROUP_ID,
1009 SET_OF_BOOKS_ID,
1010 DATA_EXTRACT_NAME,
1011 DATA_EXTRACT_METHOD,
1012 REQ_DATA_AS_OF_DATE,
1013 NVL(POPULATE_INTERFACE_FLAG,'I') POPULATE_INTERFACE_FLAG,
1014 NVL(POPULATE_INTERFACE_STATUS,'I') POPULATE_INTERFACE_STATUS,
1015 NVL(VALIDATE_DATA_FLAG,'I') VALIDATE_DATA_FLAG,
1016 NVL(VALIDATE_DATA_STATUS,'I') VALIDATE_DATA_STATUS,
1017 NVL(POPULATE_DATA_FLAG,'I') POPULATE_DATA_FLAG,
1018 NVL(POPULATE_DATA_STATUS,'I') POPULATE_DATA_STATUS,
1019 NVL(DEFAULT_DATA_FLAG,'I') DEFAULT_DATA_FLAG,
1020 NVL(DEFAULT_DATA_STATUS,'I') DEFAULT_DATA_STATUS,
1021 NVL(COPY_DEFAULTS_FLAG,'I') COPY_DEFAULTS_FLAG,
1022 NVL(COPY_DEFAULTS_STATUS,'I') COPY_DEFAULTS_STATUS,
1023 COPY_DEFAULTS_EXTRACT_ID,
1024 COPY_SALARY_FLAG,
1025 POSITION_ID_FLEX_NUM,
1026 -- de by org
1027 NVL(EXTRACT_BY_ORGANIZATION_FLAG,'N') EXTRACT_BY_ORG
1028 FROM PSB_DATA_EXTRACTS
1029 WHERE data_extract_id = p_data_extract_id;
1030
1031 l_return_status VARCHAR2(1) ;
1032 l_msg_count NUMBER ;
1033 l_msg_data VARCHAR2(2000) ;
1034 l_api_name CONSTANT VARCHAR2(30) := 'Pre_Create_Data_Extract';
1035 l_extract_by_org VARCHAR2(1);
1036
1037 BEGIN
1038
1039 p_return_status := FND_API.G_RET_STS_SUCCESS;
1040
1041 DELETE FROM PSB_ERROR_MESSAGES
1042 WHERE process_id = p_data_extract_id;
1043
1044 For C_Data_Extract_Rec in C_Data_extract
1045 Loop
1046 g_business_group_id := C_Data_Extract_Rec.business_group_id;
1047 g_data_extract_id := p_data_extract_id;
1048 g_data_extract_name := C_Data_Extract_Rec.data_extract_name;
1049 g_data_extract_method := C_Data_Extract_Rec.data_extract_method;
1050 g_set_of_books_id := C_Data_Extract_Rec.set_of_books_id;
1051 g_req_data_as_of_date := C_Data_Extract_Rec.req_data_as_of_date;
1052 g_copy_defaults_flag := C_Data_Extract_Rec.copy_defaults_flag;
1053 g_copy_defaults_status := C_Data_Extract_Rec.copy_defaults_status;
1054 g_populate_interface_flag := C_Data_Extract_Rec.populate_interface_flag;
1055 g_populate_interface_status := C_Data_Extract_Rec.populate_interface_status;
1056 g_validate_data_flag := C_Data_Extract_Rec.validate_data_flag;
1057 g_validate_data_status := C_Data_Extract_Rec.validate_data_status;
1058 g_populate_data_flag := C_Data_Extract_Rec.populate_data_flag;
1059 g_populate_data_status := C_Data_Extract_Rec.populate_data_status;
1060 g_default_data_flag := C_Data_Extract_Rec.default_data_flag;
1061 g_default_data_status := C_Data_Extract_Rec.default_data_status;
1062 g_copy_data_extract_id := C_Data_Extract_Rec.copy_Defaults_Extract_id;
1063 g_copy_salary_flag := C_Data_Extract_Rec.copy_salary_flag;
1064 g_position_id_flex_num := C_Data_Extract_Rec.position_id_flex_num;
1065 -- de by org
1066 l_extract_by_org := C_Data_Extract_Rec.extract_by_org;
1067 End Loop;
1068
1069 PSB_HR_EXTRACT_DATA_PVT.Init(g_req_data_as_of_date);
1070
1071 -- de by org
1072 if l_extract_by_org = 'Y' then
1073 PSB_WRHR_EXTRACT_PROCESS.INSERT_ORGANIZATIONS
1074 ( p_api_version => 1.0,
1075 p_data_extract_id => p_data_extract_id,
1076 p_as_of_date => g_req_data_as_of_date,
1077 p_business_group_id => g_business_group_id,
1078 p_return_status => l_return_status,
1079 p_msg_count => l_msg_count,
1080 p_msg_data => l_msg_data);
1081
1082 if l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1083 raise FND_API.G_EXC_ERROR;
1084 end if;
1085 end if;
1086
1087 message_token('DATA_EXTRACT_NAME',g_data_extract_name);
1088 add_message('PSB', 'PSB_DATA_EXTRACT');
1089
1090 FND_FILE.Put_Line( FND_FILE.OUTPUT,
1091 'Processing the given Data Extract : ' ||g_data_extract_name);
1092
1093 if l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1094 raise FND_API.G_EXC_ERROR;
1095 end if;
1096
1097
1098 EXCEPTION
1099
1100 when FND_API.G_EXC_ERROR then
1101
1102 p_return_status := FND_API.G_RET_STS_ERROR;
1103
1104 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
1105 p_data => p_msg_data);
1106
1107
1108 when FND_API.G_EXC_UNEXPECTED_ERROR then
1109
1110 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1111
1112 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
1113 p_data => p_msg_data);
1114
1115
1116 when OTHERS then
1117
1118 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1119
1120 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) then
1121
1122 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
1123 l_api_name);
1124 end if;
1125
1126 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
1127 p_data => p_msg_data);
1128
1129 --
1130 End Pre_Create_Data_Extract;
1131
1132 Procedure Pre_Create_Extract_CP
1133 (
1134 errbuf OUT NOCOPY VARCHAR2 ,
1135 retcode OUT NOCOPY VARCHAR2 ,
1136 --
1137 p_data_extract_id IN NUMBER
1138 )
1139
1140 IS
1141 --
1142 l_api_name CONSTANT VARCHAR2(30) := 'Pre_Create_Extract_CP';
1143 l_api_version CONSTANT NUMBER := 1.0 ;
1144 --
1145 l_return_status VARCHAR2(1);
1146 l_msg_data VARCHAR2(2000) ;
1147 l_msg_count number;
1148
1149 BEGIN
1150 Pre_Create_Data_Extract
1151 (p_api_version => 1.0,
1152 p_init_msg_list => FND_API.G_TRUE,
1153 p_commit => FND_API.G_TRUE,
1154 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1155 p_return_status => l_return_status,
1156 p_msg_count => l_msg_count,
1157 p_msg_data => l_msg_data,
1158 p_data_extract_id => p_data_extract_id);
1159
1160 if l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1161 raise FND_API.G_EXC_ERROR;
1162 end if;
1163
1164 PSB_HR_EXTRACT_DATA_PVT.Final_Process;
1165
1166 FND_MSG_PUB.Count_And_Get ( p_count => l_msg_count,
1167 p_data => l_msg_data );
1168 IF l_msg_count > 0 THEN
1169
1170 l_msg_data := FND_MSG_PUB.Get
1171 (p_msg_index => FND_MSG_PUB.G_NEXT,
1172 p_encoded => FND_API.G_FALSE);
1173
1174 PSB_MESSAGE_S.INSERT_ERROR
1175 (p_source_process => 'DATA_EXTRACT_VALIDATION',
1176 p_process_id => p_data_extract_id,
1177 p_msg_count => l_msg_count,
1178 p_msg_data => l_msg_data);
1179
1180 END IF;
1181
1182 PSB_MESSAGE_S.Print_Success;
1183 retcode := 0 ;
1184
1185 COMMIT WORK;
1186
1187 EXCEPTION
1188
1189 WHEN FND_API.G_EXC_ERROR THEN
1190 --
1191 PSB_MESSAGE_S.Print_Error ( p_mode => FND_FILE.LOG ,
1192 p_print_header => FND_API.G_TRUE );
1193 retcode := 2 ;
1194 COMMIT WORK ;
1195 --
1196 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1197 --
1198 PSB_MESSAGE_S.Print_Error ( p_mode => FND_FILE.LOG ,
1199 p_print_header => FND_API.G_TRUE );
1200 retcode := 2 ;
1201 COMMIT WORK ;
1202 --
1203 WHEN OTHERS THEN
1204
1205 --
1206 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
1207 --
1208 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,
1209 l_api_name ) ;
1210 END IF ;
1211 --
1212 l_return_status := FND_API.G_RET_STS_ERROR;
1213
1214 PSB_MESSAGE_S.Print_Error ( p_mode => FND_FILE.LOG ,
1215 p_print_header => FND_API.G_TRUE );
1216 --
1217 retcode := 2 ;
1218 COMMIT WORK ;
1219 --
1220 End Pre_Create_Extract_CP;
1221
1222 Procedure Copy_Attributes_CP
1223 (
1224 errbuf OUT NOCOPY VARCHAR2 ,
1225 retcode OUT NOCOPY VARCHAR2 ,
1226 --
1227 p_copy_defaults_flag IN VARCHAR2 ,
1228 p_copy_defaults_status IN VARCHAR2 ,
1229 p_copy_data_extract_id IN NUMBER,
1230 p_data_extract_method IN VARCHAR2,
1231 p_data_extract_id IN NUMBER
1232 )
1233
1234 IS
1235 --
1236 l_api_name CONSTANT VARCHAR2(30) := 'Copy_Attributes_CP';
1237 l_api_version CONSTANT NUMBER := 1.0 ;
1238 --
1239 l_return_status VARCHAR2(1);
1240 l_msg_count number;
1241 l_msg_data varchar2(2000);
1242
1243 BEGIN
1244
1245 /* Bug No 2579818 Start */
1246 -- for the time being NULLING OUT this procedure since
1247 -- it is called from copy_elements also
1248 RETURN;
1249 /* Bug No 2579818 End */
1250
1251
1252 if (p_copy_defaults_flag = 'Y') then
1253
1254 if ((p_copy_defaults_status is null) or (p_copy_defaults_status <> 'C')) then
1255
1256 PSB_CONCURRENCY_CONTROL_PVT.Enforce_Concurrency_Control
1257 (p_api_version => 1.0 ,
1258 p_return_status => l_return_status,
1259 p_concurrency_class => 'DATAEXTRACT_CREATION',
1260 p_concurrency_entity_name => 'DATA_EXTRACT',
1261 p_concurrency_entity_id => p_data_extract_id);
1262
1263 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1264 raise FND_API.G_EXC_ERROR;
1265 end if;
1266
1267 PSB_COPY_DATA_EXTRACT_PVT.Copy_Attributes
1268 (p_api_version => 1.0,
1269 p_init_msg_list => FND_API.G_TRUE,
1270 p_commit => FND_API.G_TRUE,
1271 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1272 p_return_status => l_return_status,
1273 p_msg_count => l_msg_count,
1274 p_msg_data => l_msg_data,
1275 p_extract_method => p_data_extract_method,
1276 p_src_data_extract_id => p_copy_data_extract_id,
1277 p_data_extract_id => p_data_extract_id
1278 );
1279
1280 if l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1281 FND_FILE.put_line(FND_FILE.LOG,'Copy Attribute Values Failed');
1282 end if;
1283
1284
1285 PSB_CONCURRENCY_CONTROL_PVT.Release_Concurrency_Control
1286 (p_api_version => 1.0 ,
1287 p_return_status => l_return_status,
1288 p_concurrency_class => 'DATAEXTRACT_CREATION',
1289 p_concurrency_entity_name => 'DATA_EXTRACT',
1290 p_concurrency_entity_id => p_data_extract_id);
1291
1292 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1293 raise FND_API.G_EXC_ERROR;
1294 end if;
1295
1296 end if;
1297 end if;
1298
1299 FND_MSG_PUB.Count_And_Get ( p_count => l_msg_count,
1300 p_data => l_msg_data );
1301 IF l_msg_count > 0 THEN
1302
1303 l_msg_data := FND_MSG_PUB.Get
1304 (p_msg_index => FND_MSG_PUB.G_NEXT,
1305 p_encoded => FND_API.G_FALSE);
1306
1307 PSB_MESSAGE_S.INSERT_ERROR
1308 (p_source_process => 'DATA_EXTRACT_VALIDATION',
1309 p_process_id => p_data_extract_id,
1310 p_msg_count => l_msg_count,
1311 p_msg_data => l_msg_data);
1312
1313 END IF;
1314
1315 FND_FILE.put_line(FND_FILE.LOG,'After Call');
1316 PSB_MESSAGE_S.Print_Success;
1317 retcode := 0 ;
1318
1319 COMMIT WORK;
1320
1321 EXCEPTION
1322
1323 WHEN FND_API.G_EXC_ERROR THEN
1324 --
1325 PSB_MESSAGE_S.Print_Error ( p_mode => FND_FILE.LOG ,
1326 p_print_header => FND_API.G_TRUE );
1327 retcode := 2 ;
1328 COMMIT WORK ;
1329 --
1330 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1331 --
1332 PSB_MESSAGE_S.Print_Error ( p_mode => FND_FILE.LOG ,
1333 p_print_header => FND_API.G_TRUE );
1334 retcode := 2 ;
1335 COMMIT WORK ;
1336 --
1337 WHEN OTHERS THEN
1338
1339 --
1340 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
1341 --
1342 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,
1343 l_api_name ) ;
1344 END IF ;
1345 --
1346 l_return_status := FND_API.G_RET_STS_ERROR;
1347
1348 PSB_MESSAGE_S.Print_Error ( p_mode => FND_FILE.LOG ,
1349 p_print_header => FND_API.G_TRUE );
1350 --
1351 retcode := 2 ;
1352 COMMIT WORK ;
1353 --
1354 End Copy_Attributes_CP;
1355
1356 Procedure Copy_Elements_CP
1357 (
1358 errbuf OUT NOCOPY VARCHAR2 ,
1359 retcode OUT NOCOPY VARCHAR2 ,
1360 --
1361 p_copy_defaults_flag IN VARCHAR2 ,
1362 p_copy_defaults_status IN VARCHAR2 ,
1363 p_copy_data_extract_id IN NUMBER,
1364 p_copy_salary_flag IN VARCHAR2,
1365 p_data_extract_method IN VARCHAR2,
1366 p_data_extract_id IN NUMBER
1367 )
1368
1369 IS
1370 --
1371 l_api_name CONSTANT VARCHAR2(30) := 'Copy_Elements_CP';
1372 l_api_version CONSTANT NUMBER := 1.0 ;
1373 --
1374 l_return_status VARCHAR2(1);
1375 l_msg_count number;
1376 l_msg_data varchar2(2000);
1377
1378 BEGIN
1379
1380 if (p_copy_defaults_flag = 'Y') then
1381 if ((p_copy_defaults_status is null) or (p_copy_defaults_status <> 'C')) then
1382 PSB_CONCURRENCY_CONTROL_PVT.Enforce_Concurrency_Control
1383 (p_api_version => 1.0 ,
1384 p_return_status => l_return_status,
1385 p_concurrency_class => 'DATAEXTRACT_CREATION',
1386 p_concurrency_entity_name => 'DATA_EXTRACT',
1387 p_concurrency_entity_id => p_data_extract_id);
1388
1389 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1390 raise FND_API.G_EXC_ERROR;
1391 end if;
1392
1393
1394 PSB_COPY_DATA_EXTRACT_PVT.Copy_Elements
1395 ( p_api_version => 1.0,
1396 p_init_msg_list => FND_API.G_TRUE,
1397 p_commit => FND_API.G_TRUE,
1398 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1399 p_return_status => l_return_status,
1400 p_msg_count => l_msg_count,
1401 p_msg_data => l_msg_data,
1402 p_extract_method => p_data_extract_method,
1403 p_src_data_extract_id => p_copy_data_extract_id,
1404 p_copy_salary_flag => p_copy_salary_flag,
1405 p_data_extract_id => p_data_extract_id
1406 );
1407
1408 if l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1409 FND_FILE.put_line(FND_FILE.LOG,'Copy Elements Failed');
1410 end if;
1411
1412 PSB_CONCURRENCY_CONTROL_PVT.Release_Concurrency_Control
1413 (p_api_version => 1.0 ,
1414 p_return_status => l_return_status,
1415 p_concurrency_class => 'DATAEXTRACT_CREATION',
1416 p_concurrency_entity_name => 'DATA_EXTRACT',
1417 p_concurrency_entity_id => p_data_extract_id);
1418
1419 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1420 raise FND_API.G_EXC_ERROR;
1421 end if;
1422
1423 end if;
1424 end if;
1425
1426 FND_MSG_PUB.Count_And_Get ( p_count => l_msg_count,
1427 p_data => l_msg_data );
1428 IF l_msg_count > 0 THEN
1429
1430 l_msg_data := FND_MSG_PUB.Get
1431 (p_msg_index => FND_MSG_PUB.G_NEXT,
1432 p_encoded => FND_API.G_FALSE);
1433
1434 PSB_MESSAGE_S.INSERT_ERROR
1435 (p_source_process => 'DATA_EXTRACT_VALIDATION',
1436 p_process_id => p_data_extract_id,
1437 p_msg_count => l_msg_count,
1438 p_msg_data => l_msg_data);
1439
1440 END IF;
1441
1442 PSB_MESSAGE_S.Print_Success;
1443 retcode := 0 ;
1444
1445 EXCEPTION
1446
1447 WHEN FND_API.G_EXC_ERROR THEN
1448 --
1449 PSB_MESSAGE_S.Print_Error ( p_mode => FND_FILE.LOG ,
1450 p_print_header => FND_API.G_TRUE );
1451 retcode := 2 ;
1452 COMMIT WORK ;
1453 --
1454 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1455 --
1456 PSB_MESSAGE_S.Print_Error ( p_mode => FND_FILE.LOG ,
1457 p_print_header => FND_API.G_TRUE );
1458 retcode := 2 ;
1459 COMMIT WORK ;
1460 --
1461 WHEN OTHERS THEN
1462
1463 --
1464 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
1465 --
1466 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,
1467 l_api_name ) ;
1468 END IF ;
1469 --
1470 l_return_status := FND_API.G_RET_STS_ERROR;
1471
1472 PSB_MESSAGE_S.Print_Error ( p_mode => FND_FILE.LOG ,
1473 p_print_header => FND_API.G_TRUE );
1474 --
1475 retcode := 2 ;
1476 COMMIT WORK ;
1477 --
1478 End Copy_Elements_CP;
1479
1480 Procedure Copy_Position_Sets_CP
1481 (
1482 errbuf OUT NOCOPY VARCHAR2 ,
1483 retcode OUT NOCOPY VARCHAR2 ,
1484 --
1485 p_copy_defaults_flag IN VARCHAR2 ,
1486 p_copy_defaults_status IN VARCHAR2 ,
1487 p_copy_data_extract_id IN NUMBER,
1488 p_data_extract_method IN VARCHAR2,
1489 p_data_extract_id IN NUMBER
1490 )
1491
1492 IS
1493 --
1494 l_api_name CONSTANT VARCHAR2(30) := 'Copy_Position_Sets_CP';
1495 l_api_version CONSTANT NUMBER := 1.0 ;
1496 --
1497 l_return_status VARCHAR2(1);
1498 l_msg_count number;
1499 l_msg_data varchar2(2000);
1500
1501 BEGIN
1502
1503 if (p_copy_defaults_flag = 'Y') then
1504 if ((p_copy_defaults_status is null) or (p_copy_defaults_status <> 'C')) then
1505
1506 PSB_CONCURRENCY_CONTROL_PVT.Enforce_Concurrency_Control
1507 (p_api_version => 1.0 ,
1508 p_return_status => l_return_status,
1509 p_concurrency_class => 'DATAEXTRACT_CREATION',
1510 p_concurrency_entity_name => 'DATA_EXTRACT',
1511 p_concurrency_entity_id => p_data_extract_id);
1512
1513 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1514 raise FND_API.G_EXC_ERROR;
1515 end if;
1516
1517 PSB_COPY_DATA_EXTRACT_PVT.Copy_Position_Sets
1518 ( p_api_version => 1.0,
1519 p_init_msg_list => FND_API.G_TRUE,
1520 p_commit => FND_API.G_TRUE,
1521 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1522 p_return_status => l_return_status,
1523 p_msg_count => l_msg_count,
1524 p_msg_data => l_msg_data,
1525 p_extract_method => p_data_extract_method,
1526 p_src_data_extract_id => p_copy_data_extract_id,
1527 p_data_extract_id => p_data_extract_id
1528 );
1529
1530 if l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1531 FND_FILE.put_line(FND_FILE.LOG,'Copy Position Sets Failed');
1532 end if;
1533
1534 PSB_CONCURRENCY_CONTROL_PVT.Release_Concurrency_Control
1535 (p_api_version => 1.0 ,
1536 p_return_status => l_return_status,
1537 p_concurrency_class => 'DATAEXTRACT_CREATION',
1538 p_concurrency_entity_name => 'DATA_EXTRACT',
1539 p_concurrency_entity_id => p_data_extract_id);
1540
1541 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1542 raise FND_API.G_EXC_ERROR;
1543 end if;
1544
1545 end if;
1546 end if;
1547
1548 FND_MSG_PUB.Count_And_Get ( p_count => l_msg_count,
1549 p_data => l_msg_data );
1550 IF l_msg_count > 0 THEN
1551
1552 l_msg_data := FND_MSG_PUB.Get
1553 (p_msg_index => FND_MSG_PUB.G_NEXT,
1554 p_encoded => FND_API.G_FALSE);
1555
1556 PSB_MESSAGE_S.INSERT_ERROR
1557 (p_source_process => 'DATA_EXTRACT_VALIDATION',
1558 p_process_id => p_data_extract_id,
1559 p_msg_count => l_msg_count,
1560 p_msg_data => l_msg_data);
1561
1562 END IF;
1563
1564 PSB_MESSAGE_S.Print_Success;
1565 retcode := 0 ;
1566
1567 EXCEPTION
1568
1569 WHEN FND_API.G_EXC_ERROR THEN
1570 --
1571 PSB_MESSAGE_S.Print_Error ( p_mode => FND_FILE.LOG ,
1572 p_print_header => FND_API.G_TRUE );
1573 retcode := 2 ;
1574 COMMIT WORK ;
1575 --
1576 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1577 --
1578 PSB_MESSAGE_S.Print_Error ( p_mode => FND_FILE.LOG ,
1579 p_print_header => FND_API.G_TRUE );
1580 retcode := 2 ;
1581 COMMIT WORK ;
1582 --
1583 WHEN OTHERS THEN
1584
1585 --
1586 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
1587 --
1588 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,
1589 l_api_name ) ;
1590 END IF ;
1591 --
1592 l_return_status := FND_API.G_RET_STS_ERROR;
1593
1594 PSB_MESSAGE_S.Print_Error ( p_mode => FND_FILE.LOG ,
1595 p_print_header => FND_API.G_TRUE );
1596 --
1597 retcode := 2 ;
1598 COMMIT WORK ;
1599 --
1600 End Copy_Position_Sets_CP;
1601
1602
1603 Procedure Copy_Default_Rules_CP
1604 (
1605 errbuf OUT NOCOPY VARCHAR2 ,
1606 retcode OUT NOCOPY VARCHAR2 ,
1607 --
1608 p_copy_defaults_flag IN VARCHAR2 ,
1609 p_copy_defaults_status IN VARCHAR2 ,
1610 p_copy_data_extract_id IN NUMBER,
1611 p_data_extract_method IN VARCHAR2,
1612 p_data_extract_id IN NUMBER
1613 )
1614
1615 IS
1616 --
1617 l_api_name CONSTANT VARCHAR2(30) := 'Copy_Default_Rules_CP';
1618 l_api_version CONSTANT NUMBER := 1.0 ;
1619 --
1620 l_return_status VARCHAR2(1);
1621 l_msg_count number;
1622 l_msg_data varchar2(2000);
1623
1624 BEGIN
1625
1626 if (p_copy_defaults_flag = 'Y') then
1627 if ((p_copy_defaults_status is null) or (p_copy_defaults_status <> 'C')) then
1628
1629 PSB_CONCURRENCY_CONTROL_PVT.Enforce_Concurrency_Control
1630 (p_api_version => 1.0 ,
1631 p_return_status => l_return_status,
1632 p_concurrency_class => 'DATAEXTRACT_CREATION',
1633 p_concurrency_entity_name => 'DATA_EXTRACT',
1634 p_concurrency_entity_id => p_data_extract_id);
1635
1636 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1637 raise FND_API.G_EXC_ERROR;
1638 end if;
1639
1640 PSB_COPY_DATA_EXTRACT_PVT.Copy_Default_Rules
1641 ( p_api_version => 1.0,
1642 p_init_msg_list => FND_API.G_TRUE,
1643 p_commit => FND_API.G_TRUE,
1644 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1645 p_return_status => l_return_status,
1646 p_msg_count => l_msg_count,
1647 p_msg_data => l_msg_data,
1648 p_extract_method => p_data_extract_method,
1649 p_src_data_extract_id => p_copy_data_extract_id,
1650 p_data_extract_id => p_data_extract_id
1651 );
1652
1653 if l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1654 FND_FILE.put_line(FND_FILE.LOG,'Copy Default Rules Failed');
1655 end if;
1656
1657 PSB_CONCURRENCY_CONTROL_PVT.Release_Concurrency_Control
1658 (p_api_version => 1.0 ,
1659 p_return_status => l_return_status,
1660 p_concurrency_class => 'DATAEXTRACT_CREATION',
1661 p_concurrency_entity_name => 'DATA_EXTRACT',
1662 p_concurrency_entity_id => p_data_extract_id);
1663
1664 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1665 raise FND_API.G_EXC_ERROR;
1666 end if;
1667 end if;
1668 end if;
1669
1670 FND_MSG_PUB.Count_And_Get ( p_count => l_msg_count,
1671 p_data => l_msg_data );
1672 IF l_msg_count > 0 THEN
1673
1674 l_msg_data := FND_MSG_PUB.Get
1675 (p_msg_index => FND_MSG_PUB.G_NEXT,
1676 p_encoded => FND_API.G_FALSE);
1677
1678 PSB_MESSAGE_S.INSERT_ERROR
1679 (p_source_process => 'DATA_EXTRACT_VALIDATION',
1680 p_process_id => p_data_extract_id,
1681 p_msg_count => l_msg_count,
1682 p_msg_data => l_msg_data);
1683
1684 END IF;
1685 PSB_MESSAGE_S.Print_Success;
1686 retcode := 0 ;
1687
1688 EXCEPTION
1689
1690 WHEN FND_API.G_EXC_ERROR THEN
1691 --
1692 PSB_MESSAGE_S.Print_Error ( p_mode => FND_FILE.LOG ,
1693 p_print_header => FND_API.G_TRUE );
1694 retcode := 2 ;
1695 COMMIT WORK ;
1696 --
1697 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1698 --
1699 PSB_MESSAGE_S.Print_Error ( p_mode => FND_FILE.LOG ,
1700 p_print_header => FND_API.G_TRUE );
1701 retcode := 2 ;
1702 COMMIT WORK ;
1703 --
1704 WHEN OTHERS THEN
1705
1706 --
1707 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
1708 --
1709 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,
1710 l_api_name ) ;
1711 END IF ;
1712 --
1713 l_return_status := FND_API.G_RET_STS_ERROR;
1714
1715 PSB_MESSAGE_S.Print_Error ( p_mode => FND_FILE.LOG ,
1716 p_print_header => FND_API.G_TRUE );
1717 --
1718 retcode := 2 ;
1719 COMMIT WORK ;
1720 --
1721 End Copy_Default_Rules_CP;
1722
1723 Procedure Populate_Positions_CP
1724 (
1725 errbuf OUT NOCOPY VARCHAR2 ,
1726 retcode OUT NOCOPY VARCHAR2 ,
1727 --
1728 p_populate_interface_flag IN VARCHAR2 ,
1729 p_populate_interface_status IN VARCHAR2 ,
1730 p_populate_data_flag IN VARCHAR2 ,
1731 p_populate_data_status IN VARCHAR2 ,
1732 p_data_extract_method IN VARCHAR2 ,
1733 p_req_data_as_of_date IN DATE ,
1734 p_position_id_flex_num IN NUMBER ,
1735 p_business_group_id IN NUMBER ,
1736 p_set_of_books_id IN NUMBER ,
1737 p_data_extract_id IN NUMBER ,
1738 -- de by org
1739 p_extract_by_org IN VARCHAR2 := 'N'
1740 )
1741
1742 IS
1743 --
1744 l_api_name CONSTANT VARCHAR2(30) := 'Populate_Positions_CP';
1745 l_api_version CONSTANT NUMBER := 1.0 ;
1746 --
1747 l_effective_date DATE;
1748 l_return_status VARCHAR2(1);
1749 l_msg_count number;
1750 l_msg_data varchar2(2000);
1751
1752 BEGIN
1753
1754 if (p_populate_interface_flag = 'Y') then
1755 if ((p_populate_interface_status is null) or (p_populate_interface_status <> 'C')) then
1756
1757 l_effective_date := p_req_data_as_of_date;
1758 PSB_HR_EXTRACT_DATA_PVT.Init(l_effective_date);
1759
1760 PSB_HR_EXTRACT_DATA_PVT.Get_Position_Information
1761 ( p_api_version => 1.0,
1762 p_init_msg_list => FND_API.G_TRUE,
1763 p_commit => FND_API.G_TRUE,
1764 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1765 p_return_status => l_return_status,
1766 p_msg_count => l_msg_count,
1767 p_msg_data => l_msg_data,
1768 p_data_extract_id => p_data_extract_id,
1769 -- de by org
1770 p_extract_by_org => p_extract_by_org,
1771 p_extract_method => p_data_extract_method,
1772 p_date => p_req_data_as_of_date,
1773 p_id_flex_num => p_position_id_flex_num,
1774 p_business_group_id => p_business_group_id,
1775 p_set_of_books_id => p_set_of_books_id
1776 );
1777
1778 if l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1779 FND_FILE.put_line(FND_FILE.LOG,'Get Position Information Failed');
1780 end if;
1781
1782 PSB_DE_Client_Extensions_Pub.Run_Client_Extension_Pub
1783 ( p_api_version => 1.0,
1784 p_init_msg_list => FND_API.G_FALSE,
1785 p_commit => FND_API.G_TRUE,
1786 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1787 x_return_status => l_return_status,
1788 x_msg_count => l_msg_count,
1789 x_msg_data => l_msg_data,
1790 p_data_extract_id => p_data_extract_id,
1791 p_mode => 'P'
1792 );
1793
1794 if l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1795 FND_FILE.put_line(FND_FILE.LOG,'The Client Extension for Position Interface Failed');
1796 raise FND_API.G_EXC_ERROR;
1797 end if;
1798
1799 end if;
1800 end if;
1801
1802 if (p_populate_data_flag = 'Y') then
1803 if ((p_populate_data_status is null) or (p_populate_data_status <> 'C')) then
1804 PSB_CONCURRENCY_CONTROL_PVT.Enforce_Concurrency_Control
1805 (p_api_version => 1.0 ,
1806 p_return_status => l_return_status,
1807 p_concurrency_class => 'DATAEXTRACT_CREATION',
1808 p_concurrency_entity_name => 'DATA_EXTRACT',
1809 p_concurrency_entity_id => p_data_extract_id);
1810
1811 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1812 raise FND_API.G_EXC_ERROR;
1813 end if;
1814
1815 PSB_HR_POPULATE_DATA_PVT.Populate_Position_Information
1816 ( p_api_version => 1.0,
1817 p_init_msg_list => FND_API.G_FALSE,
1818 p_commit => FND_API.G_TRUE,
1819 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1820 p_return_status => l_return_status,
1821 p_msg_count => l_msg_count,
1822 p_msg_data => l_msg_data,
1823 p_data_extract_id => p_data_extract_id,
1824 -- de by org
1825 p_extract_by_org => p_extract_by_org,
1826 p_extract_method => p_data_extract_method,
1827 p_business_group_id => p_business_group_id,
1828 p_set_of_books_id => p_set_of_books_id
1829 );
1830
1831 if l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1832 FND_FILE.put_line(FND_FILE.LOG,'Populate Position Failed');
1833
1834 end if;
1835
1836 PSB_CONCURRENCY_CONTROL_PVT.Release_Concurrency_Control
1837 (p_api_version => 1.0 ,
1838 p_return_status => l_return_status,
1839 p_concurrency_class => 'DATAEXTRACT_CREATION',
1840 p_concurrency_entity_name => 'DATA_EXTRACT',
1841 p_concurrency_entity_id => p_data_extract_id);
1842
1843 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1844 raise FND_API.G_EXC_ERROR;
1845 end if;
1846 end if;
1847 end if;
1848
1849 PSB_HR_EXTRACT_DATA_PVT.Final_Process;
1850
1851 FND_MSG_PUB.Count_And_Get ( p_count => l_msg_count,
1852 p_data => l_msg_data );
1853 IF l_msg_count > 0 THEN
1854
1855 l_msg_data := FND_MSG_PUB.Get
1856 (p_msg_index => FND_MSG_PUB.G_NEXT,
1857 p_encoded => FND_API.G_FALSE);
1858
1859 PSB_MESSAGE_S.INSERT_ERROR
1860 (p_source_process => 'DATA_EXTRACT_VALIDATION',
1861 p_process_id => p_data_extract_id,
1862 p_msg_count => l_msg_count,
1863 p_msg_data => l_msg_data);
1864
1865 END IF;
1866 PSB_MESSAGE_S.Print_Success;
1867 retcode := 0 ;
1868
1869 COMMIT WORK ;
1870
1871 EXCEPTION
1872
1873 WHEN FND_API.G_EXC_ERROR THEN
1874 --
1875 PSB_MESSAGE_S.Print_Error ( p_mode => FND_FILE.LOG ,
1876 p_print_header => FND_API.G_TRUE );
1877 retcode := 2 ;
1878 COMMIT WORK ;
1879 --
1880 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1881 --
1882 PSB_MESSAGE_S.Print_Error ( p_mode => FND_FILE.LOG ,
1883 p_print_header => FND_API.G_TRUE );
1884 retcode := 2 ;
1885 COMMIT WORK ;
1886 --
1887 WHEN OTHERS THEN
1888
1889 --
1890 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
1891 --
1892 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,
1893 l_api_name ) ;
1894 END IF ;
1895 --
1896
1897 PSB_MESSAGE_S.Print_Error ( p_mode => FND_FILE.LOG ,
1898 p_print_header => FND_API.G_TRUE );
1899 --
1900 retcode := 2 ;
1901 COMMIT WORK ;
1902 --
1903 End Populate_Positions_CP;
1904
1905 Procedure Populate_Elements_CP
1906 (
1907 errbuf OUT NOCOPY VARCHAR2 ,
1908 retcode OUT NOCOPY VARCHAR2 ,
1909 --
1910 p_populate_interface_flag IN VARCHAR2 ,
1911 p_populate_interface_status IN VARCHAR2 ,
1912 p_populate_data_flag IN VARCHAR2 ,
1913 p_populate_data_status IN VARCHAR2 ,
1914 p_data_extract_method IN VARCHAR2 ,
1915 p_req_data_as_of_date IN DATE ,
1916 p_business_group_id IN NUMBER ,
1917 p_set_of_books_id IN NUMBER ,
1918 p_data_extract_id IN NUMBER
1919 )
1920
1921 IS
1922 --
1923 l_api_name CONSTANT VARCHAR2(30) := 'Populate_Elements_CP';
1924 l_api_version CONSTANT NUMBER := 1.0 ;
1925 --
1926 l_effective_date DATE;
1927 l_return_status VARCHAR2(1);
1928 l_msg_count number;
1929 l_msg_data varchar2(2000);
1930 -- de by org
1931 l_extract_by_org VARCHAR2(1);
1932
1933 -- Following cursor checks if extract by org is enabled or not.
1934 Cursor c_data_extract_org is
1935 Select nvl(extract_by_organization_flag,'N') extract_by_org
1936 from psb_data_extracts
1937 where data_extract_id = p_data_extract_id;
1938
1939 BEGIN
1940 -- de by org
1941
1942
1943 if (p_populate_interface_flag = 'Y') then
1944 if ((p_populate_interface_status is null) or (p_populate_interface_status <> 'C')) then
1945 l_effective_date := p_req_data_as_of_date;
1946 PSB_HR_EXTRACT_DATA_PVT.Init(l_effective_date);
1947
1948 PSB_HR_EXTRACT_DATA_PVT.Get_Salary_Information
1949 ( p_api_version => 1.0,
1950 p_init_msg_list => FND_API.G_TRUE,
1951 p_commit => FND_API.G_TRUE,
1952 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1953 p_return_status => l_return_status,
1954 p_msg_count => l_msg_count,
1955 p_msg_data => l_msg_data,
1956 p_data_extract_id => p_data_extract_id,
1957 p_extract_method => p_data_extract_method,
1958 p_business_group_id => p_business_group_id
1959 );
1960
1961 if l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1962 FND_FILE.put_line(FND_FILE.LOG,'Get Salary Information Failed');
1963 raise FND_API.G_EXC_ERROR;
1964 end if;
1965
1966 PSB_DE_Client_Extensions_Pub.Run_Client_Extension_Pub
1967 ( p_api_version => 1.0,
1968 p_init_msg_list => FND_API.G_FALSE,
1969 p_commit => FND_API.G_TRUE,
1970 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1971 x_return_status => l_return_status,
1972 x_msg_count => l_msg_count,
1973 x_msg_data => l_msg_data,
1974 p_data_extract_id => p_data_extract_id,
1975 p_mode => 'S'
1976 );
1977
1978 if l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1979 FND_FILE.put_line(FND_FILE.LOG,'The Client Extension for Salary Interface Failed');
1980 raise FND_API.G_EXC_ERROR;
1981 end if;
1982
1983 end if;
1984 end if;
1985
1986 if (p_populate_data_flag = 'Y') then
1987 if ((p_populate_data_status is null) or (p_populate_data_status <> 'C')) then
1988
1989 PSB_CONCURRENCY_CONTROL_PVT.Enforce_Concurrency_Control
1990 (p_api_version => 1.0 ,
1991 p_return_status => l_return_status,
1992 p_concurrency_class => 'DATAEXTRACT_CREATION',
1993 p_concurrency_entity_name => 'DATA_EXTRACT',
1994 p_concurrency_entity_id => p_data_extract_id);
1995
1996 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1997 raise FND_API.G_EXC_ERROR;
1998 end if;
1999
2000 PSB_HR_POPULATE_DATA_PVT.Populate_Element_Information
2001 ( p_api_version => 1.0,
2002 p_init_msg_list => FND_API.G_FALSE,
2003 p_commit => FND_API.G_TRUE,
2004 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2005 p_return_status => l_return_status,
2006 p_msg_count => l_msg_count,
2007 p_msg_data => l_msg_data,
2008 p_data_extract_id => p_data_extract_id,
2009 p_extract_method => p_data_extract_method,
2010 p_business_group_id => p_business_group_id,
2011 p_set_of_books_id => p_set_of_books_id
2012 );
2013
2014 if l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2015 FND_FILE.put_line(FND_FILE.LOG,'Populate Elements Failed');
2016 raise FND_API.G_EXC_ERROR;
2017 end if;
2018
2019 PSB_CONCURRENCY_CONTROL_PVT.Release_Concurrency_Control
2020 (p_api_version => 1.0 ,
2021 p_return_status => l_return_status,
2022 p_concurrency_class => 'DATAEXTRACT_CREATION',
2023 p_concurrency_entity_name => 'DATA_EXTRACT',
2024 p_concurrency_entity_id => p_data_extract_id);
2025
2026 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2027 raise FND_API.G_EXC_ERROR;
2028 end if;
2029 end if;
2030 end if;
2031
2032
2033 PSB_HR_EXTRACT_DATA_PVT.Final_Process;
2034 FND_MSG_PUB.Count_And_Get ( p_count => l_msg_count,
2035 p_data => l_msg_data );
2036 IF l_msg_count > 0 THEN
2037
2038 l_msg_data := FND_MSG_PUB.Get
2039 (p_msg_index => FND_MSG_PUB.G_NEXT,
2040 p_encoded => FND_API.G_FALSE);
2041
2042 PSB_MESSAGE_S.INSERT_ERROR
2043 (p_source_process => 'DATA_EXTRACT_VALIDATION',
2044 p_process_id => p_data_extract_id,
2045 p_msg_count => l_msg_count,
2046 p_msg_data => l_msg_data);
2047
2048 END IF;
2049 PSB_MESSAGE_S.Print_Success;
2050 retcode := 0 ;
2051
2052 COMMIT WORK;
2053
2054 EXCEPTION
2055
2056 WHEN FND_API.G_EXC_ERROR THEN
2057 --
2058 FND_MSG_PUB.Count_And_Get ( p_count => l_msg_count,
2059 p_data => l_msg_data );
2060 IF l_msg_count > 0 THEN
2061
2062 l_msg_data := FND_MSG_PUB.Get
2063 (p_msg_index => FND_MSG_PUB.G_NEXT,
2064 p_encoded => FND_API.G_FALSE);
2065
2066 PSB_MESSAGE_S.INSERT_ERROR
2067 (p_source_process => 'DATA_EXTRACT_VALIDATION',
2068 p_process_id => p_data_extract_id,
2069 p_msg_count => l_msg_count,
2070 p_msg_data => l_msg_data);
2071
2072 END IF;
2073
2074 PSB_MESSAGE_S.Print_Error ( p_mode => FND_FILE.LOG ,
2075 p_print_header => FND_API.G_TRUE );
2076 retcode := 2 ;
2077 COMMIT WORK ;
2078 --
2079 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2080 --
2081 PSB_MESSAGE_S.Print_Error ( p_mode => FND_FILE.LOG ,
2082 p_print_header => FND_API.G_TRUE );
2083 retcode := 2 ;
2084 COMMIT WORK ;
2085 --
2086 WHEN OTHERS THEN
2087
2088 --
2089 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
2090 --
2091 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,
2092 l_api_name ) ;
2093 END IF ;
2094 --
2095
2096 PSB_MESSAGE_S.Print_Error ( p_mode => FND_FILE.LOG ,
2097 p_print_header => FND_API.G_TRUE );
2098 --
2099 retcode := 2 ;
2100 COMMIT WORK ;
2101 --
2102 End Populate_Elements_CP;
2103
2104 Procedure Populate_Attributes_CP
2105 (
2106 errbuf OUT NOCOPY VARCHAR2 ,
2107 retcode OUT NOCOPY VARCHAR2 ,
2108 --
2109 p_populate_interface_flag IN VARCHAR2 ,
2110 p_populate_interface_status IN VARCHAR2 ,
2111 p_populate_data_flag IN VARCHAR2 ,
2112 p_populate_data_status IN VARCHAR2 ,
2113 p_data_extract_method IN VARCHAR2 ,
2114 p_req_data_as_of_date IN DATE ,
2115 p_business_group_id IN NUMBER ,
2116 p_set_of_books_id IN NUMBER ,
2117 p_data_extract_id IN NUMBER
2118 )
2119
2120 IS
2121 --
2122 l_api_name CONSTANT VARCHAR2(30) := 'Populate_Attributes_CP';
2123 l_api_version CONSTANT NUMBER := 1.0 ;
2124 --
2125 l_effective_date DATE;
2126 l_return_status VARCHAR2(1);
2127 l_msg_count number;
2128 l_msg_data varchar2(2000);
2129 -- de by org
2130 l_extract_by_org VARCHAR2(1);
2131
2132
2133 -- Following cursor checks if extract by org is enabled or not.
2134 Cursor c_data_extract_org is
2135 Select nvl(extract_by_organization_flag,'N') extract_by_org
2136 from psb_data_extracts
2137 where data_extract_id = p_data_extract_id;
2138
2139 BEGIN
2140 -- de by org
2141 FOR c_data_extract_org_rec in c_data_extract_org LOOP
2142 l_extract_by_org := c_data_extract_org_rec.extract_by_org;
2143 END LOOP;
2144
2145 if (p_populate_interface_flag = 'Y') then
2146 if ((p_populate_interface_status is null) or (p_populate_interface_status <> 'C')) then
2147 l_effective_date := p_req_data_as_of_date;
2148 PSB_HR_EXTRACT_DATA_PVT.Init(l_effective_date);
2149
2150 PSB_HR_EXTRACT_DATA_PVT.Get_Attributes
2151 ( p_api_version => 1.0,
2152 p_init_msg_list => FND_API.G_TRUE,
2153 p_commit => FND_API.G_TRUE,
2154 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2155 p_return_status => l_return_status,
2156 p_msg_count => l_msg_count,
2157 p_msg_data => l_msg_data,
2158 p_data_extract_id => p_data_extract_id,
2159 p_extract_method => p_data_extract_method,
2160 p_business_group_id => p_business_group_id
2161 );
2162
2163 if l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2164 FND_FILE.put_line(FND_FILE.LOG,'Get Attributes Failed');
2165 end if;
2166
2167 PSB_DE_Client_Extensions_Pub.Run_Client_Extension_Pub
2168 ( p_api_version => 1.0,
2169 p_init_msg_list => FND_API.G_FALSE,
2170 p_commit => FND_API.G_TRUE,
2171 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2172 x_return_status => l_return_status,
2173 x_msg_count => l_msg_count,
2174 x_msg_data => l_msg_data,
2175 p_data_extract_id => p_data_extract_id,
2176 p_mode => 'V'
2177 );
2178
2179 if l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2180 FND_FILE.put_line(FND_FILE.LOG,'The Client Extension for Attribute Values Interface Failed');
2181 raise FND_API.G_EXC_ERROR;
2182 end if;
2183
2184 end if;
2185 end if;
2186
2187 if (p_populate_data_flag = 'Y') then
2188 if ((p_populate_data_status is null) or (p_populate_data_status <> 'C')) then
2189
2190 PSB_CONCURRENCY_CONTROL_PVT.Enforce_Concurrency_Control
2191 (p_api_version => 1.0 ,
2192 p_return_status => l_return_status,
2193 p_concurrency_class => 'DATAEXTRACT_CREATION',
2194 p_concurrency_entity_name => 'DATA_EXTRACT',
2195 p_concurrency_entity_id => p_data_extract_id);
2196
2197 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2198 raise FND_API.G_EXC_ERROR;
2199 end if;
2200
2201 PSB_HR_POPULATE_DATA_PVT.Populate_Attribute_Values
2202 ( p_api_version => 1.0,
2203 p_init_msg_list => FND_API.G_FALSE,
2204 p_commit => FND_API.G_TRUE,
2205 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2206 p_return_status => l_return_status,
2207 p_msg_count => l_msg_count,
2208 p_msg_data => l_msg_data,
2209 p_data_extract_id => p_data_extract_id,
2210 p_extract_method => p_data_extract_method,
2211 p_business_group_id => p_business_group_id
2212 );
2213
2214 if l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2215 FND_FILE.put_line(FND_FILE.LOG,'Populate Attribute Values Failed');
2216 end if;
2217
2218 PSB_CONCURRENCY_CONTROL_PVT.Release_Concurrency_Control
2219 (p_api_version => 1.0 ,
2220 p_return_status => l_return_status,
2221 p_concurrency_class => 'DATAEXTRACT_CREATION',
2222 p_concurrency_entity_name => 'DATA_EXTRACT',
2223 p_concurrency_entity_id => p_data_extract_id);
2224
2225 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2226 raise FND_API.G_EXC_ERROR;
2227 end if;
2228 end if;
2229 end if;
2230
2231
2232 PSB_HR_EXTRACT_DATA_PVT.Final_Process;
2233 FND_MSG_PUB.Count_And_Get ( p_count => l_msg_count,
2234 p_data => l_msg_data );
2235 IF l_msg_count > 0 THEN
2236
2237 l_msg_data := FND_MSG_PUB.Get
2238 (p_msg_index => FND_MSG_PUB.G_NEXT,
2239 p_encoded => FND_API.G_FALSE);
2240
2241 PSB_MESSAGE_S.INSERT_ERROR
2242 (p_source_process => 'DATA_EXTRACT_VALIDATION',
2243 p_process_id => p_data_extract_id,
2244 p_msg_count => l_msg_count,
2245 p_msg_data => l_msg_data);
2246
2247 END IF;
2248 PSB_MESSAGE_S.Print_Success;
2249 retcode := 0 ;
2250 COMMIT WORK ;
2251
2252 EXCEPTION
2253
2254 WHEN FND_API.G_EXC_ERROR THEN
2255 --
2256 PSB_MESSAGE_S.Print_Error ( p_mode => FND_FILE.LOG ,
2257 p_print_header => FND_API.G_TRUE );
2258 retcode := 2 ;
2259 COMMIT WORK ;
2260 --
2261 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2262 --
2263 PSB_MESSAGE_S.Print_Error ( p_mode => FND_FILE.LOG ,
2264 p_print_header => FND_API.G_TRUE );
2265 retcode := 2 ;
2266 COMMIT WORK ;
2267 --
2268 WHEN OTHERS THEN
2269
2270 --
2271 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
2272 --
2273 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,
2274 l_api_name ) ;
2275 END IF ;
2276 --
2277
2278 PSB_MESSAGE_S.Print_Error ( p_mode => FND_FILE.LOG ,
2279 p_print_header => FND_API.G_TRUE );
2280 --
2281 retcode := 2 ;
2282 COMMIT WORK ;
2283 --
2284 End Populate_Attributes_CP;
2285
2286 Procedure Populate_Employees_CP
2287 (
2288 errbuf OUT NOCOPY VARCHAR2 ,
2289 retcode OUT NOCOPY VARCHAR2 ,
2290 --
2291 p_populate_interface_flag IN VARCHAR2 ,
2292 p_populate_interface_status IN VARCHAR2 ,
2293 p_populate_data_flag IN VARCHAR2 ,
2294 p_populate_data_status IN VARCHAR2 ,
2295 p_data_extract_method IN VARCHAR2 ,
2296 p_business_group_id IN NUMBER ,
2297 p_set_of_books_id IN NUMBER ,
2298 p_req_data_as_of_date IN DATE ,
2299 p_copy_defaults_flag IN VARCHAR2 ,
2300 p_copy_salary_flag IN VARCHAR2 ,
2301 p_data_extract_id IN NUMBER ,
2302 -- de by org
2303 p_extract_by_org IN VARCHAR2 := 'N'
2304 )
2305
2306 IS
2307 --
2308 l_api_name CONSTANT VARCHAR2(30) := 'Populate_Employees_CP';
2309 l_api_version CONSTANT NUMBER := 1.0 ;
2310 --
2311 l_effective_date DATE;
2312 l_return_status VARCHAR2(1);
2313 l_msg_count number;
2314 l_msg_data varchar2(2000);
2315
2316 BEGIN
2317
2318 if (p_populate_interface_flag = 'Y') then
2319 if ((p_populate_interface_status is null) or (p_populate_interface_status <> 'C')) then
2320 l_effective_date := p_req_data_as_of_date;
2321 PSB_HR_EXTRACT_DATA_PVT.Init(l_effective_date);
2322
2323 PSB_HR_EXTRACT_DATA_PVT.Get_Employee_Information
2324 ( p_api_version => 1.0,
2325 p_init_msg_list => FND_API.G_TRUE,
2326 p_commit => FND_API.G_TRUE,
2327 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2328 p_return_status => l_return_status,
2329 p_msg_count => l_msg_count,
2330 p_msg_data => l_msg_data,
2331 p_data_extract_id => p_data_extract_id,
2332 -- de by org
2333 p_extract_by_org => p_extract_by_org,
2334 p_extract_method => p_data_extract_method,
2335 p_date => p_req_data_as_of_date,
2336 p_business_group_id => p_business_group_id,
2337 p_set_of_books_id => p_set_of_books_id,
2338 p_copy_defaults_flag => p_copy_defaults_flag,
2339 p_copy_salary_flag => p_copy_salary_flag
2340 );
2341
2342 if l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2343 FND_FILE.put_line(FND_FILE.LOG,'Get Employee Information Failed');
2344 raise FND_API.G_EXC_ERROR;
2345 end if;
2346
2347 PSB_DE_Client_Extensions_Pub.Run_Client_Extension_Pub
2348 ( p_api_version => 1.0,
2349 p_init_msg_list => FND_API.G_FALSE,
2350 p_commit => FND_API.G_TRUE,
2351 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2352 x_return_status => l_return_status,
2353 x_msg_count => l_msg_count,
2354 x_msg_data => l_msg_data,
2355 p_data_extract_id => p_data_extract_id,
2356 p_mode => 'E'
2357 );
2358
2359 if l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2360 FND_FILE.put_line(FND_FILE.LOG,'The Client Extension for Employee Interface Failed');
2361 raise FND_API.G_EXC_ERROR;
2362 end if;
2363
2364 end if;
2365 end if;
2366
2367 if (p_populate_data_flag = 'Y') then
2368 if ((p_populate_data_status is null) or (p_populate_data_status <> 'C')) then
2369
2370 PSB_CONCURRENCY_CONTROL_PVT.Enforce_Concurrency_Control
2371 (p_api_version => 1.0 ,
2372 p_return_status => l_return_status,
2373 p_concurrency_class => 'DATAEXTRACT_CREATION',
2374 p_concurrency_entity_name => 'DATA_EXTRACT',
2375 p_concurrency_entity_id => p_data_extract_id);
2376
2377 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2378 raise FND_API.G_EXC_ERROR;
2379 end if;
2380
2381 PSB_HR_POPULATE_DATA_PVT.Populate_Employee_Information
2382 ( p_api_version => 1.0,
2383 p_init_msg_list => FND_API.G_FALSE,
2384 p_commit => FND_API.G_TRUE,
2385 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2386 p_return_status => l_return_status,
2387 p_msg_count => l_msg_count,
2388 p_msg_data => l_msg_data,
2389 p_data_extract_id => p_data_extract_id,
2390 -- de by org
2391 p_extract_by_org => p_extract_by_org,
2392 p_extract_method => p_data_extract_method,
2393 p_business_group_id => p_business_group_id,
2394 p_set_of_books_id => p_set_of_books_id
2395 );
2396
2397 if l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2398 FND_FILE.put_line(FND_FILE.LOG,'Populate Employees Failed');
2399 raise FND_API.G_EXC_ERROR;
2400 end if;
2401
2402 PSB_CONCURRENCY_CONTROL_PVT.Release_Concurrency_Control
2403 (p_api_version => 1.0 ,
2404 p_return_status => l_return_status,
2405 p_concurrency_class => 'DATAEXTRACT_CREATION',
2406 p_concurrency_entity_name => 'DATA_EXTRACT',
2407 p_concurrency_entity_id => p_data_extract_id);
2408
2409 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2410 raise FND_API.G_EXC_ERROR;
2411 end if;
2412 end if;
2413 end if;
2414
2415 FND_MSG_PUB.Count_And_Get ( p_count => l_msg_count,
2416 p_data => l_msg_data );
2417 IF l_msg_count > 0 THEN
2418
2419 l_msg_data := FND_MSG_PUB.Get
2420 (p_msg_index => FND_MSG_PUB.G_NEXT,
2421 p_encoded => FND_API.G_FALSE);
2422
2423 PSB_MESSAGE_S.INSERT_ERROR
2424 (p_source_process => 'DATA_EXTRACT_VALIDATION',
2425 p_process_id => p_data_extract_id,
2426 p_msg_count => l_msg_count,
2427 p_msg_data => l_msg_data);
2428
2429 END IF;
2430
2431 PSB_HR_EXTRACT_DATA_PVT.Final_Process;
2432 PSB_MESSAGE_S.Print_Success;
2433 retcode := 0 ;
2434 COMMIT WORK ;
2435
2436 EXCEPTION
2437
2438 WHEN FND_API.G_EXC_ERROR THEN
2439 --
2440 FND_MSG_PUB.Count_And_Get ( p_count => l_msg_count,
2441 p_data => l_msg_data );
2442 IF l_msg_count > 0 THEN
2443
2444 l_msg_data := FND_MSG_PUB.Get
2445 (p_msg_index => FND_MSG_PUB.G_NEXT,
2446 p_encoded => FND_API.G_FALSE);
2447
2448 PSB_MESSAGE_S.INSERT_ERROR
2449 (p_source_process => 'DATA_EXTRACT_VALIDATION',
2450 p_process_id => p_data_extract_id,
2451 p_msg_count => l_msg_count,
2452 p_msg_data => l_msg_data);
2453 END IF;
2454
2455 PSB_MESSAGE_S.Print_Error ( p_mode => FND_FILE.LOG ,
2456 p_print_header => FND_API.G_TRUE );
2457 retcode := 2 ;
2458 COMMIT WORK ;
2459 --
2460 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2461 --
2462 PSB_MESSAGE_S.Print_Error ( p_mode => FND_FILE.LOG ,
2463 p_print_header => FND_API.G_TRUE );
2464 retcode := 2 ;
2465 COMMIT WORK ;
2466 --
2467 WHEN OTHERS THEN
2468
2469 --
2470 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
2471 --
2472 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,
2473 l_api_name ) ;
2474 END IF ;
2475 --
2476
2477 PSB_MESSAGE_S.Print_Error ( p_mode => FND_FILE.LOG ,
2478 p_print_header => FND_API.G_TRUE );
2479 --
2480 retcode := 2 ;
2481 COMMIT WORK ;
2482 --
2483 End Populate_Employees_CP;
2484
2485
2486 Procedure Populate_Cost_Distributions_CP
2487 (
2488 errbuf OUT NOCOPY VARCHAR2 ,
2489 retcode OUT NOCOPY VARCHAR2 ,
2490 --
2491 p_populate_interface_flag IN VARCHAR2 ,
2492 p_populate_interface_status IN VARCHAR2 ,
2493 p_populate_data_flag IN VARCHAR2 ,
2494 p_populate_data_status IN VARCHAR2 ,
2495 p_data_extract_method IN VARCHAR2 ,
2496 p_business_group_id IN NUMBER ,
2497 p_set_of_books_id IN NUMBER ,
2498 p_req_data_as_of_date IN DATE ,
2499 p_data_extract_id IN NUMBER ,
2500 -- de by org
2501 p_extract_by_org IN VARCHAR2 := 'N'
2502 )
2503
2504 IS
2505 --
2506 l_api_name CONSTANT VARCHAR2(30) := 'Populate_Cost_Distributions_CP';
2507 l_api_version CONSTANT NUMBER := 1.0 ;
2508 --
2509 l_effective_date DATE;
2510 l_return_status VARCHAR2(1);
2511 l_msg_count number;
2512 l_msg_data varchar2(2000);
2513
2514 BEGIN
2515
2516 if (p_populate_interface_flag = 'Y') then
2517 if ((p_populate_interface_status is null) or (p_populate_interface_status <> 'C')) then
2518
2519 l_effective_date := p_req_data_as_of_date;
2520 PSB_HR_EXTRACT_DATA_PVT.Init(l_effective_date);
2521
2522 PSB_HR_EXTRACT_DATA_PVT.Get_Costing_Information
2523 ( p_api_version => 1.0,
2524 p_init_msg_list => FND_API.G_TRUE,
2525 p_commit => FND_API.G_TRUE,
2526 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2527 p_return_status => l_return_status,
2528 p_msg_count => l_msg_count,
2529 p_msg_data => l_msg_data,
2530 p_data_extract_id => p_data_extract_id,
2531 -- de by org
2532 p_extract_by_org => p_extract_by_org,
2533 p_date => p_req_data_as_of_date,
2534 p_extract_method => p_data_extract_method,
2535 p_business_group_id => p_business_group_id,
2536 p_set_of_books_id => p_set_of_books_id
2537 );
2538
2539 if l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2540 /* Bug 3677529 Start */
2541 PSB_MESSAGE_S.Print_Error ( p_mode => FND_FILE.LOG ,
2542 p_print_header => FND_API.G_TRUE );
2543 /* Bug 3677529 End */
2544 FND_FILE.put_line(FND_FILE.LOG,'Get Cost Distributions Failed');
2545 FND_MSG_PUB.Initialize;
2546 end if;
2547
2548 PSB_DE_Client_Extensions_Pub.Run_Client_Extension_Pub
2549 ( p_api_version => 1.0,
2550 p_init_msg_list => FND_API.G_FALSE,
2551 p_commit => FND_API.G_TRUE,
2552 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2553 x_return_status => l_return_status,
2554 x_msg_count => l_msg_count,
2555 x_msg_data => l_msg_data,
2556 p_data_extract_id => p_data_extract_id,
2557 p_mode => 'C'
2558 );
2559
2560 if l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2561 FND_FILE.put_line(FND_FILE.LOG,'The Client Extension for Cost Distributions Interface Failed');
2562 raise FND_API.G_EXC_ERROR;
2563 end if;
2564
2565 end if;
2566 end if;
2567
2568 if (p_populate_data_flag = 'Y') then
2569 if ((p_populate_data_status is null) or (p_populate_data_status <> 'C')) then
2570 PSB_CONCURRENCY_CONTROL_PVT.Enforce_Concurrency_Control
2571 (p_api_version => 1.0 ,
2572 p_return_status => l_return_status,
2573 p_concurrency_class => 'DATAEXTRACT_CREATION',
2574 p_concurrency_entity_name => 'DATA_EXTRACT',
2575 p_concurrency_entity_id => p_data_extract_id);
2576
2577 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2578 raise FND_API.G_EXC_ERROR;
2579 end if;
2580
2581 PSB_HR_POPULATE_DATA_PVT.Populate_Costing_Information
2582 ( p_api_version => 1.0,
2583 p_init_msg_list => FND_API.G_FALSE,
2584 p_commit => FND_API.G_TRUE,
2585 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2586 p_return_status => l_return_status,
2587 p_msg_count => l_msg_count,
2588 p_msg_data => l_msg_data,
2589 p_data_extract_id => p_data_extract_id,
2590 -- de by org
2591 p_extract_by_org => p_extract_by_org,
2592 p_extract_method => p_data_extract_method,
2593 p_business_group_id => p_business_group_id
2594 );
2595
2596 if l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2597 FND_FILE.put_line(FND_FILE.LOG,'Populate Costing Failed');
2598 end if;
2599
2600 PSB_CONCURRENCY_CONTROL_PVT.Release_Concurrency_Control
2601 (p_api_version => 1.0 ,
2602 p_return_status => l_return_status,
2603 p_concurrency_class => 'DATAEXTRACT_CREATION',
2604 p_concurrency_entity_name => 'DATA_EXTRACT',
2605 p_concurrency_entity_id => p_data_extract_id);
2606
2607 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2608 raise FND_API.G_EXC_ERROR;
2609 end if;
2610 end if;
2611 end if;
2612
2613 FND_MSG_PUB.Count_And_Get ( p_count => l_msg_count,
2614 p_data => l_msg_data );
2615 IF l_msg_count > 0 THEN
2616
2617 l_msg_data := FND_MSG_PUB.Get
2618 (p_msg_index => FND_MSG_PUB.G_NEXT,
2619 p_encoded => FND_API.G_FALSE);
2620
2621 PSB_MESSAGE_S.INSERT_ERROR
2622 (p_source_process => 'DATA_EXTRACT_VALIDATION',
2623 p_process_id => p_data_extract_id,
2624 p_msg_count => l_msg_count,
2625 p_msg_data => l_msg_data);
2626
2627 END IF;
2628
2629 PSB_HR_EXTRACT_DATA_PVT.Final_Process;
2630 PSB_MESSAGE_S.Print_Success;
2631 retcode := 0 ;
2632
2633 COMMIT WORK;
2634
2635 EXCEPTION
2636
2637 WHEN FND_API.G_EXC_ERROR THEN
2638 --
2639 PSB_MESSAGE_S.Print_Error ( p_mode => FND_FILE.LOG ,
2640 p_print_header => FND_API.G_TRUE );
2641 retcode := 2 ;
2642 COMMIT WORK ;
2643 --
2644 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2645 --
2646 PSB_MESSAGE_S.Print_Error ( p_mode => FND_FILE.LOG ,
2647 p_print_header => FND_API.G_TRUE );
2648 retcode := 2 ;
2649 COMMIT WORK ;
2650 --
2651 WHEN OTHERS THEN
2652
2653 --
2654 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
2655 --
2656 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,
2657 l_api_name ) ;
2658 END IF ;
2659 --
2660
2661 PSB_MESSAGE_S.Print_Error ( p_mode => FND_FILE.LOG ,
2662 p_print_header => FND_API.G_TRUE );
2663 --
2664 retcode := 2 ;
2665 COMMIT WORK ;
2666 --
2667 End Populate_Cost_Distributions_CP;
2668
2669 Procedure Populate_Pos_Assignments_CP
2670 (
2671 errbuf OUT NOCOPY VARCHAR2 ,
2672 retcode OUT NOCOPY VARCHAR2 ,
2673 --
2674 p_populate_interface_flag IN VARCHAR2 ,
2675 p_populate_interface_status IN VARCHAR2 ,
2676 p_populate_data_flag IN VARCHAR2 ,
2677 p_populate_data_status IN VARCHAR2 ,
2678 p_data_extract_method IN VARCHAR2 ,
2679 p_business_group_id IN NUMBER ,
2680 p_set_of_books_id IN NUMBER ,
2681 p_req_data_as_of_date IN DATE ,
2682 p_data_extract_id IN NUMBER ,
2683 -- de by org
2684 p_extract_by_org IN VARCHAR2 := 'N'
2685 )
2686
2687 IS
2688 --
2689 l_api_name CONSTANT VARCHAR2(30) := 'Populate_Pos_Assginments_CP';
2690 l_api_version CONSTANT NUMBER := 1.0 ;
2691 --
2692 l_effective_date DATE;
2693 l_return_status VARCHAR2(1);
2694 l_msg_count number;
2695 l_msg_data varchar2(2000);
2696
2697 BEGIN
2698
2699 if (p_populate_interface_flag = 'Y') then
2700 if ((p_populate_interface_status is null) or (p_populate_interface_status <> 'C')) then
2701 l_effective_date := p_req_data_as_of_date;
2702 PSB_HR_EXTRACT_DATA_PVT.Init(l_effective_date);
2703
2704 PSB_HR_EXTRACT_DATA_PVT.Get_Employee_Attributes
2705 ( p_api_version => 1.0,
2706 p_init_msg_list => FND_API.G_TRUE,
2707 p_commit => FND_API.G_TRUE,
2708 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2709 p_return_status => l_return_status,
2710 p_msg_count => l_msg_count,
2711 p_msg_data => l_msg_data,
2712 p_data_extract_id => p_data_extract_id,
2713 p_extract_by_org => p_extract_by_org,
2714 p_extract_method => p_data_extract_method,
2715 p_date => p_req_data_as_of_date,
2716 p_business_group_id => p_business_group_id,
2717 p_set_of_books_id => p_set_of_books_id
2718 );
2719
2720 if l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2721 FND_FILE.put_line(FND_FILE.LOG,'Get Employee Attributes Failed');
2722 end if;
2723
2724 PSB_DE_Client_Extensions_Pub.Run_Client_Extension_Pub
2725 ( p_api_version => 1.0,
2726 p_init_msg_list => FND_API.G_FALSE,
2727 p_commit => FND_API.G_TRUE,
2728 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2729 x_return_status => l_return_status,
2730 x_msg_count => l_msg_count,
2731 x_msg_data => l_msg_data,
2732 p_data_extract_id => p_data_extract_id,
2733 p_mode => 'A'
2734 );
2735
2736 if l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2737 FND_FILE.put_line(FND_FILE.LOG,'The Client Extension for Employee Attributes Interface Failed');
2738 raise FND_API.G_EXC_ERROR;
2739 end if;
2740
2741 end if;
2742 end if;
2743
2744 if (p_populate_data_flag = 'Y') then
2745 if ((p_populate_data_status is null) or (p_populate_data_status <> 'C')) then
2746 PSB_CONCURRENCY_CONTROL_PVT.Enforce_Concurrency_Control
2747 (p_api_version => 1.0 ,
2748 p_return_status => l_return_status,
2749 p_concurrency_class => 'DATAEXTRACT_CREATION',
2750 p_concurrency_entity_name => 'DATA_EXTRACT',
2751 p_concurrency_entity_id => p_data_extract_id);
2752
2753 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2754 raise FND_API.G_EXC_ERROR;
2755 end if;
2756
2757 PSB_HR_POPULATE_DATA_PVT.Populate_Pos_Assignments
2758 ( p_api_version => 1.0,
2759 p_init_msg_list => FND_API.G_FALSE,
2760 p_commit => FND_API.G_TRUE,
2761 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2762 p_return_status => l_return_status,
2763 p_msg_count => l_msg_count,
2764 p_msg_data => l_msg_data,
2765 p_data_extract_id => p_data_extract_id,
2766 -- de by org
2767 p_extract_by_org => p_extract_by_org,
2768 p_extract_method => p_data_extract_method,
2769 p_business_group_id => p_business_group_id,
2770 p_set_of_books_id => p_set_of_books_id
2771 );
2772
2773 if l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2774 FND_FILE.put_line(FND_FILE.LOG,'Populate Position Assign Failed');
2775 end if;
2776
2777 PSB_CONCURRENCY_CONTROL_PVT.Release_Concurrency_Control
2778 (p_api_version => 1.0 ,
2779 p_return_status => l_return_status,
2780 p_concurrency_class => 'DATAEXTRACT_CREATION',
2781 p_concurrency_entity_name => 'DATA_EXTRACT',
2782 p_concurrency_entity_id => p_data_extract_id);
2783
2784 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2785 raise FND_API.G_EXC_ERROR;
2786 end if;
2787 end if;
2788 end if;
2789
2790
2791 FND_MSG_PUB.Count_And_Get ( p_count => l_msg_count,
2792 p_data => l_msg_data );
2793 IF l_msg_count > 0 THEN
2794
2795 l_msg_data := FND_MSG_PUB.Get
2796 (p_msg_index => FND_MSG_PUB.G_NEXT,
2797 p_encoded => FND_API.G_FALSE);
2798
2799 PSB_MESSAGE_S.INSERT_ERROR
2800 (p_source_process => 'DATA_EXTRACT_VALIDATION',
2801 p_process_id => p_data_extract_id,
2802 p_msg_count => l_msg_count,
2803 p_msg_data => l_msg_data);
2804
2805 END IF;
2806 PSB_HR_EXTRACT_DATA_PVT.Final_Process;
2807 PSB_MESSAGE_S.Print_Success;
2808 retcode := 0 ;
2809
2810 COMMIT WORK;
2811
2812 EXCEPTION
2813
2814 WHEN FND_API.G_EXC_ERROR THEN
2815 --
2816 PSB_MESSAGE_S.Print_Error ( p_mode => FND_FILE.LOG ,
2817 p_print_header => FND_API.G_TRUE );
2818 retcode := 2 ;
2819 COMMIT WORK ;
2820 --
2821 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2822 --
2823 PSB_MESSAGE_S.Print_Error ( p_mode => FND_FILE.LOG ,
2824 p_print_header => FND_API.G_TRUE );
2825 retcode := 2 ;
2826 COMMIT WORK ;
2827 --
2828 WHEN OTHERS THEN
2829
2830 --
2831 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
2832 --
2833 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,
2834 l_api_name ) ;
2835 END IF ;
2836 --
2837
2838 PSB_MESSAGE_S.Print_Error ( p_mode => FND_FILE.LOG ,
2839 p_print_header => FND_API.G_TRUE );
2840 --
2841 retcode := 2 ;
2842 COMMIT WORK ;
2843 --
2844 End Populate_Pos_Assignments_CP;
2845
2846 Procedure Validate_Extract_CP
2847 (
2848 errbuf OUT NOCOPY VARCHAR2 ,
2849 retcode OUT NOCOPY VARCHAR2 ,
2850 --
2851 p_validate_data_flag IN VARCHAR2 ,
2852 p_validate_data_status IN VARCHAR2 ,
2853 p_data_extract_method IN VARCHAR2 ,
2854 p_req_data_as_of_date IN DATE ,
2855 p_business_group_id IN NUMBER ,
2856 p_data_extract_id IN NUMBER
2857 )
2858
2859 IS
2860 --
2861 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Extract_CP';
2862 l_api_version CONSTANT NUMBER := 1.0 ;
2863 --
2864 l_effective_date DATE;
2865 l_rep_req_id NUMBER;
2866 l_req_id NUMBER;
2867 l_return_status VARCHAR2(1);
2868 l_msg_count NUMBER;
2869 l_msg_data VARCHAR2(2000);
2870
2871 BEGIN
2872
2873 if p_validate_data_flag = 'Y' then
2874 l_effective_date := p_req_data_as_of_date;
2875 PSB_HR_EXTRACT_DATA_PVT.Init(l_effective_date);
2876
2877 PSB_VALIDATE_DATA_EXTRACT_PVT.Data_Extract_Summary
2878 ( p_api_version => 1.0,
2879 p_init_msg_list => FND_API.G_FALSE,
2880 p_commit => FND_API.G_TRUE,
2881 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2882 p_return_status => l_return_status,
2883 p_msg_count => l_msg_count,
2884 p_msg_data => l_msg_data,
2885 p_extract_method => p_data_extract_method,
2886 p_data_extract_id => p_data_extract_id
2887 );
2888
2889
2890 if l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2891 FND_FILE.put_line(FND_FILE.LOG,'Data Extract Summary failed');
2892 end if;
2893
2894 PSB_VALIDATE_DATA_EXTRACT_PVT.Validate_Data_Extract
2895 ( p_api_version => 1.0,
2896 p_init_msg_list => FND_API.G_FALSE,
2897 p_commit => FND_API.G_TRUE,
2898 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2899 p_return_status => l_return_status,
2900 p_msg_count => l_msg_count,
2901 p_msg_data => l_msg_data,
2902 p_extract_method => p_data_extract_method,
2903 p_data_extract_id => p_data_extract_id,
2904 p_business_group_id => p_business_group_id
2905 );
2906
2907 if l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2908 FND_FILE.put_line(FND_FILE.LOG,'Validate Data Extract failed');
2909 end if;
2910 end if;
2911
2912 FND_MSG_PUB.Count_And_Get ( p_count => l_msg_count,
2913 p_data => l_msg_data );
2914 IF l_msg_count > 0 THEN
2915
2916 l_msg_data := FND_MSG_PUB.Get
2917 (p_msg_index => FND_MSG_PUB.G_NEXT,
2918 p_encoded => FND_API.G_FALSE);
2919
2920 PSB_MESSAGE_S.INSERT_ERROR
2921 (p_source_process => 'DATA_EXTRACT_VALIDATION',
2922 p_process_id => p_data_extract_id,
2923 p_msg_count => l_msg_count,
2924 p_msg_data => l_msg_data);
2925
2926 END IF;
2927
2928 COMMIT WORK;
2929
2930 if (l_msg_count > 0) then
2931
2932 l_req_id := FND_GLOBAL.CONC_REQUEST_ID;
2933
2934 l_rep_req_id := Fnd_Request.Submit_Request
2935 (application => 'PSB' ,
2936 program => 'PSBRPERR' ,
2937 description => 'Error Messages Listing' ,
2938 start_time => NULL ,
2939 sub_request => FALSE ,
2940 argument1 => 'DATA_EXTRACT_VALIDATION' ,
2941 argument2 => p_data_extract_id ,
2942 argument3 => l_req_id
2943 );
2944 --
2945 if l_rep_req_id = 0 then
2946 --
2947 fnd_message.set_name('PSB', 'PSB_FAIL_TO_SUBMIT_REQUEST');
2948 raise FND_API.G_EXC_ERROR ;
2949 --
2950 end if;
2951
2952 end if;
2953 PSB_HR_EXTRACT_DATA_PVT.Final_Process;
2954 PSB_MESSAGE_S.Print_Success;
2955 retcode := 0 ;
2956
2957
2958 EXCEPTION
2959
2960 WHEN FND_API.G_EXC_ERROR THEN
2961 --
2962 PSB_MESSAGE_S.Print_Error ( p_mode => FND_FILE.LOG ,
2963 p_print_header => FND_API.G_TRUE );
2964 retcode := 2 ;
2965 COMMIT WORK ;
2966 --
2967 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2968 --
2969 PSB_MESSAGE_S.Print_Error ( p_mode => FND_FILE.LOG ,
2970 p_print_header => FND_API.G_TRUE );
2971 retcode := 2 ;
2972 COMMIT WORK ;
2973 --
2974 WHEN OTHERS THEN
2975
2976 --
2977 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
2978 --
2979 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,
2980 l_api_name ) ;
2981 END IF ;
2982 --
2983
2984 PSB_MESSAGE_S.Print_Error ( p_mode => FND_FILE.LOG ,
2985 p_print_header => FND_API.G_TRUE );
2986 --
2987 retcode := 2 ;
2988 COMMIT WORK ;
2989 --
2990 End Validate_Extract_CP;
2991
2992 PROCEDURE Post_Extract_CP
2993 (
2994 errbuf OUT NOCOPY VARCHAR2 ,
2995 retcode OUT NOCOPY VARCHAR2 ,
2996 --
2997 p_copy_defaults_flag IN VARCHAR2 ,
2998 p_populate_interface_flag IN VARCHAR2 ,
2999 p_populate_data_flag IN VARCHAR2 ,
3000 p_validate_data_flag IN VARCHAR2 ,
3001 p_data_extract_id IN NUMBER
3002 )
3003 IS
3004 --
3005 l_api_name CONSTANT VARCHAR2(30) := 'Post_Extract_CP';
3006 l_api_version CONSTANT NUMBER := 1.0 ;
3007 --
3008 l_sp9_status VARCHAR2(1);
3009 l_sp10_status VARCHAR2(1);
3010 l_sp11_status VARCHAR2(1);
3011 l_sp12_status VARCHAR2(1);
3012 l_sp13_status VARCHAR2(1);
3013 l_sp14_status VARCHAR2(1);
3014 l_sp15_status VARCHAR2(1);
3015 l_sp16_status VARCHAR2(1);
3016 l_sp17_status VARCHAR2(1);
3017 l_sp18_status VARCHAR2(1);
3018 lc_return_status VARCHAR2(1);
3019 lc_status VARCHAR2(1) := 'I';
3020 li_status VARCHAR2(1) := 'I';
3021 lv_status VARCHAR2(1) := 'I';
3022 lp_status VARCHAR2(1) := 'I';
3023 -- de by org
3024 l_extract_by_org VARCHAR2(1);
3025 l_data_extract_status VARCHAR2(1) := 'I';
3026 -- de by org
3027
3028 l_return_status VARCHAR2(1);
3029
3030 Cursor C_Dataextract is
3031 Select nvl(sp1_status,'I') sp1_status,
3032 nvl(sp2_status,'I') sp2_status,
3033 nvl(sp3_status,'I') sp3_status,
3034 nvl(sp4_status,'I') sp4_status,
3035 nvl(sp5_status,'I') sp5_status,
3036 nvl(sp6_status,'I') sp6_status,
3037 nvl(sp7_status,'I') sp7_status,
3038 nvl(sp8_status,'I') sp8_status,
3039 nvl(sp9_status,'I') sp9_status,
3040 nvl(sp10_status,'I') sp10_status,
3041 nvl(sp11_status,'I') sp11_status,
3042 nvl(sp12_status,'I') sp12_status,
3043 nvl(sp13_status,'I') sp13_status,
3044 nvl(sp14_status,'I') sp14_status,
3045 nvl(sp15_status,'I') sp15_status,
3046 nvl(sp16_status,'I') sp16_status,
3047 nvl(sp17_status,'I') sp17_status,
3048 nvl(sp18_status,'I') sp18_status
3049 from psb_reentrant_process_status
3050 where process_type = 'HR DATA EXTRACT'
3051 and process_uid = p_data_extract_id;
3052
3053 Cursor C_Extract_Status is
3054 Select nvl(populate_interface_status,'I') populate_interface_status,
3055 nvl(populate_data_status,'I') populate_data_status,
3056 nvl(copy_defaults_status,'I') copy_defaults_status,
3057 nvl(validate_data_status,'I') validate_data_status
3058 from psb_data_extracts
3059 where data_extract_id = p_data_extract_id;
3060
3061 -- de by org
3062
3063 -- Following cursor checks if extract by org is enabled or not.
3064 Cursor c_data_extract_org is
3065 Select nvl(extract_by_organization_flag,'N') extract_by_org
3066 from psb_data_extracts
3067 where data_extract_id = p_data_extract_id;
3068
3069 BEGIN
3070 -- de by org
3071 for c_data_extract_org_rec in c_data_extract_org LOOP
3072 l_extract_by_org := c_data_extract_org_rec.extract_by_org;
3073 END LOOP;
3074
3075 for c_extract_status_rec in C_Extract_Status
3076 loop
3077 for c_data_extract_rec in C_Dataextract
3078 loop
3079 if (c_extract_status_rec.copy_defaults_status <> 'C') then
3080 if (p_copy_defaults_flag = 'Y') then
3081 if (c_data_extract_rec.sp9_status = 'C') and
3082 (c_data_extract_rec.sp10_status = 'C') and
3083 (c_data_extract_rec.sp11_status = 'C') and
3084 (c_data_extract_rec.sp12_status = 'C') then
3085 lc_status := 'C';
3086 else
3087 lc_return_status := 'E';
3088 end if;
3089 end if;
3090 else
3091 lc_status := 'C';
3092 end if;
3093
3094 if (c_extract_status_rec.populate_interface_status <> 'C') then
3095 if (p_populate_interface_flag = 'Y') then
3096 if (c_data_extract_rec.sp1_status = 'C') and
3097 (c_data_extract_rec.sp2_status = 'C') and
3098 (c_data_extract_rec.sp3_status = 'C') and
3099 (c_data_extract_rec.sp4_status = 'C') and
3100 (c_data_extract_rec.sp5_status = 'C') and
3101 (c_data_extract_rec.sp6_status = 'C') then
3102 li_status := 'C';
3103 else
3104 lc_return_status := 'E';
3105 end if;
3106 end if;
3107 else
3108 li_status := 'C';
3109 end if;
3110
3111 if (c_extract_status_rec.validate_data_status <> 'C') then
3112 if (p_validate_data_flag = 'Y') then
3113 if (c_data_extract_rec.sp7_status = 'C') and
3114 (c_data_extract_rec.sp8_status = 'C') then
3115 lv_status := 'C';
3116 else
3117 lc_return_status := 'E';
3118 end if;
3119 end if;
3120 else
3121 lv_status := 'C';
3122 end if;
3123
3124 if (c_extract_status_rec.populate_data_status <> 'C') then
3125 if (p_populate_data_flag = 'Y') then
3126 if (c_data_extract_rec.sp13_status = 'C') and
3127 (c_data_extract_rec.sp14_status = 'C') and
3128 (c_data_extract_rec.sp15_status = 'C') and
3129 (c_data_extract_rec.sp16_status = 'C') and
3130 (c_data_extract_rec.sp17_status = 'C') and
3131 (c_data_extract_rec.sp18_status = 'C') then
3132 lp_status := 'C';
3133 else
3134 lc_return_status := 'E';
3135 end if;
3136 end if;
3137 else
3138 lp_status := 'C';
3139 end if;
3140
3141 Update psb_data_extracts
3142 set copy_defaults_status = lc_status,
3143 populate_interface_status = li_status,
3144 validate_data_status = lv_status,
3145 populate_data_status = lp_status
3146 where data_extract_id = p_data_extract_id;
3147
3148 End loop;
3149 End loop;
3150
3151 --PSB_HR_EXTRACT_DATA_PVT.Final_Process;
3152
3153 /* Bug 3451357 Start */
3154 IF (lc_return_status = 'E') then
3155 l_return_status := FND_API.G_RET_STS_ERROR;
3156
3157 UPDATE psb_data_extracts
3158 SET data_extract_status = 'I'
3159 WHERE data_extract_id = p_data_extract_id;
3160 ELSE
3161 UPDATE psb_data_extracts
3162 SET data_extract_status = 'C'
3163 WHERE data_extract_id = p_data_extract_id;
3164 /* Start bug #4248348 */
3165 l_data_extract_status := 'C';
3166 /* End bug #4248348 */
3167 END IF;
3168 /* Bug 3451357 End */
3169
3170 -- Bug 3451357 Start (commented the following code as part of bug fix)
3171 /*
3172 IF (lc_return_status = 'E') then
3173 l_return_status := FND_API.G_RET_STS_ERROR;
3174 ELSE
3175 Begin
3176 Select nvl(sp9_status,'I'),
3177 nvl(sp10_status,'I'),
3178 nvl(sp11_status,'I'),
3179 nvl(sp12_status,'I'),
3180 nvl(sp13_status,'I'),
3181 nvl(sp14_status,'I'),
3182 nvl(sp15_status,'I'),
3183 nvl(sp16_status,'I'),
3184 nvl(sp17_status,'I'),
3185 nvl(sp18_status,'I')
3186 into l_sp9_status,
3187 l_sp10_status,
3188 l_sp11_status,
3189 l_sp12_status,
3190 l_sp13_status,
3191 l_sp14_status,
3192 l_sp15_status,
3193 l_sp16_status,
3194 l_sp17_status,
3195 l_sp18_status
3196 from psb_reentrant_process_status
3197 where process_type = 'HR DATA EXTRACT'
3198 and process_uid = p_data_extract_id;
3199
3200 exception
3201 when NO_DATA_FOUND then
3202 null;
3203 end;
3204
3205 IF ((l_sp13_status = 'C') and
3206 (l_sp14_status = 'C') and
3207 (l_sp15_status = 'C') and
3208 (l_sp16_status = 'C') and
3209 (l_sp17_status = 'C') and
3210 (l_sp18_status = 'C') and p_copy_defaults_flag <> 'Y') THEN
3211
3212 Update PSB_DATA_EXTRACTS
3213 set populate_data_status = 'C',
3214 data_extract_status = 'C'
3215 where data_extract_id = p_data_extract_id;
3216 -- de by org
3217 l_data_extract_status := 'C';
3218
3219 -- Bug 3249834 Start
3220
3221 ELSIF ((l_sp9_status = 'C') and
3222 (l_sp10_status = 'C') and
3223 (l_sp11_status = 'C') and
3224 (l_sp12_status = 'C') and p_copy_defaults_flag = 'Y') THEN
3225
3226 UPDATE PSB_DATA_EXTRACTS
3227 SET copy_defaults_status = 'C',
3228 data_extract_status = 'C'
3229 WHERE data_extract_id = p_data_extract_id;
3230 END IF;
3231 -- Bug 3249834 End
3232
3233 END IF;
3234 */
3235 -- Bug 3451357 End
3236
3237 -- de by org
3238
3239 if l_data_extract_status = 'C' then
3240 if l_extract_by_org = 'Y' then
3241 UPDATE PSB_DATA_EXTRACT_ORGS
3242 set completion_status = 'C',
3243 completion_time = sysdate,
3244 select_flag = 'N'
3245 where data_extract_id = p_data_extract_id
3246 and select_flag = 'Y' ;
3247 else
3248 UPDATE PSB_DATA_EXTRACT_ORGS
3249 set completion_status = 'C',
3250 completion_time = sysdate,
3251 select_flag = 'N'
3252 where data_extract_id = p_data_extract_id;
3253 end if;
3254 end if;
3255
3256 PSB_MESSAGE_S.Print_Success;
3257 retcode := 0 ;
3258
3259 EXCEPTION
3260
3261 WHEN FND_API.G_EXC_ERROR THEN
3262 --
3263 PSB_MESSAGE_S.Print_Error ( p_mode => FND_FILE.LOG ,
3264 p_print_header => FND_API.G_TRUE );
3265 retcode := 2 ;
3266 COMMIT WORK ;
3267 --
3268 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3269 --
3270 PSB_MESSAGE_S.Print_Error ( p_mode => FND_FILE.LOG ,
3271 p_print_header => FND_API.G_TRUE );
3272 retcode := 2 ;
3273 COMMIT WORK ;
3274 --
3275 WHEN OTHERS THEN
3276
3277 --
3278 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
3279 --
3280 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,
3281 l_api_name ) ;
3282 END IF ;
3283 --
3284
3285 PSB_MESSAGE_S.Print_Error ( p_mode => FND_FILE.LOG ,
3286 p_print_header => FND_API.G_TRUE );
3287 --
3288 retcode := 2 ;
3289 COMMIT WORK ;
3290 --
3291 End Post_Extract_CP;
3292
3293
3294
3295 /* ----------------------------------------------------------------------- */
3296
3297 -- Get Debug Information
3298
3299 -- This Module is used to retrieve Debug Information for this routine. It
3300 -- prints Debug Information when run as a Batch Process from SQL*Plus. For
3301 -- the Debug Information to be printed on the Screen, the SQL*Plus parameter
3302 -- 'Serveroutput' should be set to 'ON'
3303
3304 FUNCTION get_debug RETURN VARCHAR2 AS
3305
3306 BEGIN
3307
3308 return(g_dbug);
3309
3310 END get_debug;
3311
3312 /* ----------------------------------------------------------------------- */
3313
3314 --This module is used to submit a set of concurrent programs for data extract.
3315
3316 PROCEDURE Submit_Data_Extract
3317 (
3318 p_api_version IN NUMBER,
3319 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3320 p_commit IN VARCHAR2 := FND_API.G_FALSE,
3321 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
3322 p_return_status OUT NOCOPY VARCHAR2,
3323 p_msg_count OUT NOCOPY NUMBER,
3324 p_msg_data OUT NOCOPY VARCHAR2,
3325 p_data_extract_id IN NUMBER,
3326 p_data_extract_method IN VARCHAR2,
3327 p_req_data_as_of_date IN DATE,
3328 p_business_group_id IN NUMBER,
3329 p_set_of_books_id IN NUMBER,
3330 p_copy_defaults_flag IN VARCHAR2,
3331 p_copy_defaults_extract_id IN NUMBER,
3332 p_copy_defaults_status IN VARCHAR2,
3333 p_populate_interface_flag IN VARCHAR2,
3334 p_populate_interface_status IN VARCHAR2,
3335 p_populate_data_flag IN VARCHAR2,
3336 p_populate_data_status IN VARCHAR2,
3337 p_validate_data_flag IN VARCHAR2,
3338 p_validate_data_status IN VARCHAR2,
3339 p_position_id_flex_num IN NUMBER,
3340 p_request_id OUT NOCOPY NUMBER
3341 )
3342 IS
3343 l_api_name CONSTANT VARCHAR2(30) := 'Submit_Data_Extract';
3344 l_api_version CONSTANT NUMBER := 1.0;
3345 l_success BOOLEAN;
3346 l_request_id NUMBER;
3347 l_refresh_method VARCHAR2(30) := 'REFRESH';
3348 l_extract_by_org VARCHAR2(1) := 'N';
3349
3350 CURSOR c_data_extract_org IS
3351 SELECT nvl(extract_by_organization_flag,'N') extract_by_org
3352 FROM psb_data_extracts
3353 WHERE data_extract_id = p_data_extract_id;
3354
3355 BEGIN
3356 p_request_id := 0; -- Bug #4451621
3357
3358 IF FND_API.to_Boolean ( p_init_msg_list )
3359 THEN
3360 FND_MSG_PUB.initialize ;
3361 END IF;
3362
3363 FOR c_data_extract_org_rec IN c_data_extract_org LOOP
3364 l_extract_by_org := c_data_extract_org_rec.extract_by_org;
3365 END LOOP;
3366
3367 l_success := fnd_submit.set_request_set('PSB', 'PSBRSDER');
3368
3369 IF NOT(l_success)
3370 THEN
3371 FND_MESSAGE.SET_NAME('PSB','PSB_DEBUG_MESSAGE');
3372 FND_MESSAGE.SET_TOKEN('MESSAGE', 'Set_Request_Set Failed');
3373 FND_MSG_PUB.Add;
3374
3375 RAISE FND_API.G_EXC_ERROR; -- Bug #4451621
3376 END IF;
3377
3378 IF (l_success)
3379 THEN -- SET_REQUEST_SET Successful.
3380
3381 /* submit program PSBCPPDE which is in stage ST1 */
3382 l_success := fnd_submit.submit_program
3383 ( application => 'PSB',
3384 program => 'PSBCPPDE',
3385 stage => 'ST1',
3386 argument1 => p_data_extract_id,
3387 argument2 => chr(0)
3388 );
3389
3390 IF ( NOT l_success )
3391 THEN
3392 FND_MESSAGE.SET_NAME('PSB','PSB_DEBUG_MESSAGE');
3393 FND_MESSAGE.SET_TOKEN('MESSAGE', '1st program in Stage 1 failed');
3394 FND_MSG_PUB.Add;
3395 END IF;
3396
3397 /* submit program PSBCPCDA which is in stage ST2 */
3398 l_success := fnd_submit.submit_program
3399 ( application => 'PSB',
3400 program => 'PSBCPCDA',
3401 stage => 'ST2',
3402 argument1 => p_copy_defaults_flag,
3403 argument2 => p_copy_defaults_status,
3404 argument3 => p_copy_defaults_extract_id,
3405 argument4 => p_data_extract_method,
3406 argument5 => p_data_extract_id,
3407 argument6 => chr(0)
3408 );
3409
3410 IF ( NOT l_success )
3411 THEN
3412 FND_MESSAGE.SET_NAME('PSB','PSB_DEBUG_MESSAGE');
3413 FND_MESSAGE.SET_TOKEN('MESSAGE', '1st program in Stage 2 failed');
3414 FND_MSG_PUB.Add;
3415 END IF;
3416
3417 /* submit program PSBCPCDE which is in stage ST2 */
3418 l_success := fnd_submit.submit_program
3419 ( application => 'PSB',
3420 program => 'PSBCPCDE',
3421 stage => 'ST2',
3422 argument1 => p_copy_defaults_flag,
3423 argument2 => p_copy_defaults_status,
3424 argument3 => p_copy_defaults_extract_id,
3425 argument4 => '',
3426 argument5 => p_data_extract_method,
3427 argument6 => p_data_extract_id,
3428 argument7 => chr(0)
3429 );
3430
3431 IF ( NOT l_success ) THEN
3432 FND_MESSAGE.SET_NAME('PSB','PSB_DEBUG_MESSAGE');
3433 FND_MESSAGE.SET_TOKEN('MESSAGE', '2nd program in Stage 2 failed');
3434 FND_MSG_PUB.Add;
3435 END IF;
3436
3437 /* submit program PSBCPCPS which is in stage ST3 */
3438 l_success := fnd_submit.submit_program
3439 ( application => 'PSB',
3440 program => 'PSBCPCPS',
3441 stage => 'ST3',
3442 argument1 => p_copy_defaults_flag,
3443 argument2 => p_copy_defaults_status,
3444 argument3 => p_copy_defaults_extract_id,
3445 argument4 => p_data_extract_method,
3446 argument5 => p_data_extract_id,
3447 argument6 => chr(0)
3448 );
3449
3450 IF ( NOT l_success )
3451 THEN
3452 FND_MESSAGE.SET_NAME('PSB','PSB_DEBUG_MESSAGE');
3453 FND_MESSAGE.SET_TOKEN('MESSAGE', '1st program in Stage 3 failed');
3454 FND_MSG_PUB.Add;
3455 END IF;
3456
3457 /* submit program PSBCPSAL which is in stage ST5 */
3458 l_success := fnd_submit.submit_program
3459 ( application => 'PSB',
3460 program => 'PSBCPSAL',
3461 stage => 'ST5',
3462 argument1 => p_populate_interface_flag,
3463 argument2 => p_populate_interface_status,
3464 argument3 => p_populate_data_flag,
3465 argument4 => p_populate_data_status,
3466 argument5 => l_refresh_method,
3467 argument6 => p_req_data_as_of_date,
3468 argument7 => p_business_group_id,
3469 argument8 => p_set_of_books_id,
3470 argument9 => p_data_extract_id,
3471 argument10 => chr(0)
3472 );
3473
3474 IF ( NOT l_success )
3475 THEN
3476 FND_MESSAGE.SET_NAME('PSB','PSB_DEBUG_MESSAGE');
3477 FND_MESSAGE.SET_TOKEN('MESSAGE', '1st program in Stage 5 failed');
3478 FND_MSG_PUB.Add;
3479 END IF;
3480
3481 /* submit program PSBCPATT which is in stage ST5 */
3482 l_success := fnd_submit.submit_program
3483 ( application => 'PSB',
3484 program => 'PSBCPATT',
3485 stage => 'ST5',
3486 argument1 => p_populate_interface_flag,
3487 argument2 => p_populate_interface_status,
3488 argument3 => p_populate_data_flag,
3489 argument4 => p_populate_data_status,
3490 argument5 => l_refresh_method,
3491 argument6 => p_req_data_as_of_date,
3492 argument7 => p_business_group_id,
3493 argument8 => p_set_of_books_id,
3494 argument9 => p_data_extract_id,
3495 argument10 => chr(0)
3496 );
3497
3498 IF ( NOT l_success )
3499 THEN
3500 FND_MESSAGE.SET_NAME('PSB','PSB_DEBUG_MESSAGE');
3501 FND_MESSAGE.SET_TOKEN('MESSAGE', '2ndprogram in Stage 5 failed');
3502 FND_MSG_PUB.Add;
3503 END IF;
3504
3505 /* submit program PSBCPPOS which is in stage ST5 */
3506 l_success := fnd_submit.submit_program
3507 ( application => 'PSB',
3508 program => 'PSBCPPOS',
3509 stage => 'ST5',
3510 argument1 => p_populate_interface_flag,
3511 argument2 => p_populate_interface_status,
3512 argument3 => p_populate_data_flag,
3513 argument4 => p_populate_data_status,
3514 argument5 => p_data_extract_method,
3515 argument6 => p_req_data_as_of_date,
3516 argument7 => p_position_id_flex_num,
3517 argument8 => p_business_group_id,
3518 argument9 => p_set_of_books_id,
3519 argument10 => p_data_extract_id,
3520 argument11 => l_extract_by_org,
3521 argument12 => chr(0)
3522 );
3523
3524 IF ( NOT l_success )
3525 THEN
3526 FND_MESSAGE.SET_NAME('PSB','PSB_DEBUG_MESSAGE');
3527 FND_MESSAGE.SET_TOKEN('MESSAGE', '3rd program in Stage 5 failed');
3528 FND_MSG_PUB.Add;
3529 END IF;
3530
3531 /* submit program PSBCPEMP which is in stage ST6 */
3532 l_success := fnd_submit.submit_program
3533 ( application => 'PSB',
3534 program => 'PSBCPEMP',
3535 stage => 'ST6',
3536 argument1 => p_populate_interface_flag,
3537 argument2 => p_populate_interface_status,
3538 argument3 => p_populate_data_flag,
3539 argument4 => p_populate_data_status,
3540 argument5 => p_data_extract_method,
3541 argument6 => p_business_group_id,
3542 argument7 => p_set_of_books_id,
3543 argument8 => p_req_data_as_of_date,
3544 argument9 => P_copy_defaults_flag,
3545 argument10 => '',
3546 argument11 => p_data_extract_id,
3547 argument12 => l_extract_by_org,
3548 argument13 => chr(0)
3549 );
3550
3551
3552 IF ( NOT l_success )
3553 THEN
3554 FND_MESSAGE.SET_NAME('PSB','PSB_DEBUG_MESSAGE');
3555 FND_MESSAGE.SET_TOKEN('MESSAGE', '1st program in Stage 6 failed');
3556 FND_MSG_PUB.Add;
3557 END IF;
3558
3559 /* submit program PSBCPDIS which is in stage ST7 */
3560 l_success := fnd_submit.submit_program
3561 ( application => 'PSB',
3562 program => 'PSBCPDIS',
3563 stage => 'ST7',
3564 argument1 => p_populate_interface_flag,
3565 argument2 => p_populate_interface_status,
3566 argument3 => p_populate_data_flag,
3567 argument4 => p_populate_data_status,
3568 argument5 => p_data_extract_method,
3569 argument6 => p_business_group_id,
3570 argument7 => p_set_of_books_id,
3571 argument8 => p_req_data_as_of_date,
3572 argument9 => p_data_extract_id,
3573 argument10 => l_extract_by_org,
3574 argument11 => chr(0)
3575 );
3576
3577 IF ( NOT l_success )
3578 THEN
3579 FND_MESSAGE.SET_NAME('PSB','PSB_DEBUG_MESSAGE');
3580 FND_MESSAGE.SET_TOKEN('MESSAGE', '1st program in Stage 7 failed');
3581 FND_MSG_PUB.Add;
3582 END IF;
3583
3584
3585 /* submit program PSBCPEAA which is in stage ST7 */
3586 l_success := fnd_submit.submit_program
3587 ( application => 'PSB',
3588 program => 'PSBCPEAA',
3589 stage => 'ST7',
3590 argument1 => p_populate_interface_flag,
3591 argument2 => p_populate_interface_status,
3592 argument3 => p_populate_data_flag,
3593 argument4 => p_populate_data_status,
3594 argument5 => p_data_extract_method,
3595 argument6 => p_business_group_id,
3596 argument7 => p_set_of_books_id,
3597 argument8 => p_req_data_as_of_date,
3598 argument9 => p_data_extract_id,
3599 argument10 => l_extract_by_org,
3600 argument11 => chr(0)
3601 );
3602
3603 IF ( NOT l_success )
3604 THEN
3605 FND_MESSAGE.SET_NAME('PSB','PSB_DEBUG_MESSAGE');
3606 FND_MESSAGE.SET_TOKEN('MESSAGE', '2nd program in Stage 7 failed');
3607 FND_MSG_PUB.Add;
3608 END IF;
3609
3610 /* Bug 4179764 Start */
3611 /* submit program PSBCPCDR which is in stage ST4 */
3612 l_success := fnd_submit.submit_program
3613 ( application => 'PSB',
3614 program => 'PSBCPCDR',
3615 stage => 'ST4',
3616 argument1 => p_copy_defaults_flag,
3617 argument2 => p_copy_defaults_status,
3618 argument3 => p_copy_defaults_extract_id,
3619 argument4 => p_data_extract_method,
3620 argument5 => p_data_extract_id,
3621 argument6 => chr(0)
3622 );
3623
3624 IF ( NOT l_success )
3625 THEN
3626 FND_MESSAGE.SET_NAME('PSB','PSB_DEBUG_MESSAGE');
3627 FND_MESSAGE.SET_TOKEN('MESSAGE', '1st program in Stage 4 failed');
3628 FND_MSG_PUB.Add;
3629 END IF;
3630
3631 /* Bug 4179764 End */
3632 /* submit program PSBCPVDE which is in stage ST8 */
3633 l_success := fnd_submit.submit_program
3634 ( application => 'PSB',
3635 program => 'PSBCPVDE',
3636 stage => 'ST8',
3637 argument1 => p_validate_data_flag,
3638 argument2 => p_validate_data_status,
3639 argument3 => p_data_extract_method,
3640 argument4 => p_req_data_as_of_date,
3641 argument5 => p_business_group_id,
3642 argument6 => p_data_extract_id,
3643 argument7 => chr(0)
3644 );
3645
3646 IF ( NOT l_success )
3647 THEN
3648 FND_MESSAGE.SET_NAME('PSB','PSB_DEBUG_MESSAGE');
3649 FND_MESSAGE.SET_TOKEN('MESSAGE', '1st program in Stage 8 failed');
3650 FND_MSG_PUB.Add;
3651 END IF;
3652
3653
3654 -- Bug 4683895: Always call Assign_Position_Defaults_CP.
3655 -- This call was earlier commented for MPA (Bug 1308558),
3656 -- but now it has been un-commented as part of bug 4683895.
3657
3658 -- submit program PSBCPDFL which is in stage ST9
3659 l_success := fnd_submit.submit_program
3660 ( application => 'PSB',
3661 program => 'PSBCPDFL',
3662 stage => 'ST9',
3663 argument1 => p_data_extract_id,
3664 argument2 => 'Y',
3665 argument3 => CHR(0)
3666 );
3667
3668 IF ( NOT l_success )
3669 THEN
3670 FND_MESSAGE.SET_NAME('PSB','PSB_DEBUG_MESSAGE');
3671 FND_MESSAGE.SET_TOKEN('MESSAGE', '1st program in Stage 9 failed');
3672 FND_MSG_PUB.Add;
3673 END IF;
3674
3675 /* submit program PSBCPFPD which is in stage ST10 */
3676 l_success := fnd_submit.submit_program
3677 ( application => 'PSB',
3678 program => 'PSBCPFPD',
3679 stage => 'ST10',
3680 argument1 => p_copy_defaults_flag,
3681 argument2 => p_populate_interface_flag,
3682 argument3 => p_populate_data_flag,
3683 argument4 => p_validate_data_flag,
3684 argument5 => p_data_extract_id,
3685 argument6 => chr(0)
3686 );
3687
3688 IF ( NOT l_success )
3689 THEN
3690 FND_MESSAGE.SET_NAME('PSB','PSB_DEBUG_MESSAGE');
3691 FND_MESSAGE.SET_TOKEN('MESSAGE', '1st program in Stage 10 failed');
3692 FND_MSG_PUB.Add;
3693 END IF;
3694
3695 /* Submit the Request set */
3696 l_request_id := fnd_submit.submit_set(NULL, FALSE);
3697 p_request_id := l_request_id ;
3698
3699 IF (l_request_id = 0)
3700 THEN
3701 FND_MESSAGE.SET_NAME('PSB','PSB_DEBUG_MESSAGE');
3702 FND_MESSAGE.SET_TOKEN('MESSAGE', 'Set Submission Failed');
3703 FND_MSG_PUB.Add;
3704 END IF;
3705
3706 END IF; -- SET_REQUEST_SET Successful.
3707
3708 EXCEPTION
3709
3710 WHEN FND_API.G_EXC_ERROR THEN
3711 P_return_status := FND_API.G_RET_STS_ERROR;
3712 FND_MSG_PUB.Count_And_Get (p_count => P_msg_count,
3713 p_data => P_msg_data);
3714
3715 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3716 P_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3717 FND_MSG_PUB.Count_And_Get (p_count => P_msg_count,
3718 p_data => P_msg_data);
3719
3720 WHEN OTHERS THEN
3721
3722 P_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3723
3724 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3725 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
3726 l_api_name);
3727 END IF;
3728
3729 FND_MSG_PUB.Count_And_Get (p_count => P_msg_count,
3730 p_data => P_msg_data);
3731
3732 END Submit_Data_Extract;
3733
3734 /* ----------------------------------------------------------------------- */
3735
3736 /* Bug No. 1308558 Start */
3737 PROCEDURE Create_Default_Rule_Set
3738 ( x_return_status OUT NOCOPY VARCHAR2,
3739 x_msg_count OUT NOCOPY NUMBER,
3740 x_msg_data OUT NOCOPY VARCHAR2,
3741 x_msg_init_list IN VARCHAR2 := FND_API.G_TRUE,
3742 p_commit IN VARCHAR2 := FND_API.G_FALSE,
3743 p_api_version IN NUMBER,
3744 p_data_extract_id IN NUMBER,
3745 p_rule_set_name IN VARCHAR2
3746 )
3747 IS
3748 l_api_version CONSTANT VARCHAR2(10) := '1.0';
3749 l_api_name CONSTANT VARCHAR2(30) := 'create_default_rule_set';
3750 l_last_update_date DATE;
3751 l_last_updated_by NUMBER(15);
3752 l_last_update_login NUMBER(15);
3753 l_creation_date DATE;
3754 l_created_by NUMBER(15);
3755 l_return_status VARCHAR2(1);
3756 l_msg_count NUMBER;
3757 l_msg_data VARCHAR2(1000);
3758 l_new_entity_set_id PSB_ENTITY_SET.ENTITY_SET_ID%TYPE;
3759 l_entity_type CONSTANT
3760 PSB_ENTITY_SET.ENTITY_TYPE%TYPE := 'DEFAULT_RULE';
3761 l_set_of_books_id PSB_ENTITY_SET.SET_OF_BOOKS_ID%TYPE;
3762 l_budget_group_id PSB_ENTITY_SET.BUDGET_GROUP_ID%TYPE;
3763 l_dummy_rowid VARCHAR2(100);
3764 l_name_already_exist NUMBER;
3765
3766 CURSOR l_defaults_csr
3767 IS
3768 SELECT default_rule_id, priority
3769 FROM psb_non_fte_rules_v
3770 WHERE data_extract_id = p_data_extract_id;
3771
3772 BEGIN
3773 -- check for compatibility
3774 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version,
3775 l_api_name, G_PKG_NAME)
3776 THEN
3777 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3778 END IF;
3779
3780 -- check whether we need to initialize message list
3781 IF FND_API.to_boolean(x_msg_init_list) THEN
3782 FND_MSG_PUB.initialize;
3783 END IF;
3784
3785 SAVEPOINT create_default_rule_set;
3786
3787 l_last_update_date := sysdate;
3788 l_last_updated_by := FND_GLOBAL.USER_ID;
3789 l_last_update_login := FND_GLOBAL.LOGIN_ID;
3790 l_creation_date := sysdate;
3791 l_created_by := FND_GLOBAL.USER_ID;
3792
3793 --verify if the rule set name provided by the user already exists
3794 SELECT COUNT(1) INTO l_name_already_exist
3795 FROM psb_entity_set
3796 WHERE name = p_rule_set_name;
3797
3798 --in case the name is already in use, abort the process and report the
3799 --user through the logs generated
3800 IF l_name_already_exist > 0 THEN
3801 FND_MESSAGE.SET_NAME('PSB', 'PSB_DUPLICATE_NAME');
3802 FND_MSG_PUB.ADD;
3803 RAISE FND_API.G_EXC_ERROR;
3804 END IF;
3805
3806 --get a new PSB_ENTITY_SET_ID for the new rule set
3807 FOR l_entity_set_rec IN (SELECT psb_entity_set_s.NEXTVAL entity_set_id
3808 FROM dual)
3809 LOOP
3810 l_new_entity_set_id := l_entity_set_rec.entity_set_id;
3811 END LOOP;
3812
3813 --Fetch set_of_books_id and budget_group_id
3814 FOR l_data_extracts_rec IN (SELECT set_of_books_id, business_group_id
3815 FROM psb_data_extracts
3816 WHERE data_extract_id = p_data_extract_id)
3817 LOOP
3818 l_set_of_books_id := l_data_extracts_rec.set_of_books_id;
3819 l_budget_group_id := l_data_extracts_rec.business_group_id;
3820 END LOOP;
3821
3822 PSB_ENTITY_SET_PVT.Insert_Row
3823 ( p_api_version => 1.0,
3824 p_init_msg_list => FND_API.G_FALSE,
3825 p_commit => FND_API.G_FALSE,
3826 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
3827 p_return_status => l_return_status,
3828 p_msg_count => l_msg_count,
3829 p_msg_data => l_msg_data,
3830 P_ROWID => l_dummy_rowid,
3831 P_ENTITY_SET_ID => l_new_entity_set_id,
3832 P_ENTITY_TYPE => l_entity_type,
3833 P_NAME => p_rule_set_name,
3834 P_DESCRIPTION => NULL,
3835 P_BUDGET_GROUP_ID => l_budget_group_id,
3836 P_SET_OF_BOOKS_ID => l_set_of_books_id,
3837 P_DATA_EXTRACT_ID => p_data_extract_id,
3838 P_CONSTRAINT_THRESHOLD => NULL,
3839 P_ENABLE_FLAG => NULL,
3840 P_ATTRIBUTE1 => NULL,
3841 P_ATTRIBUTE2 => NULL,
3842 P_ATTRIBUTE3 => NULL,
3843 P_ATTRIBUTE4 => NULL,
3844 P_ATTRIBUTE5 => NULL,
3845 P_ATTRIBUTE6 => NULL,
3846 P_ATTRIBUTE7 => NULL,
3847 P_ATTRIBUTE8 => NULL,
3848 P_ATTRIBUTE9 => NULL,
3849 P_ATTRIBUTE10 => NULL,
3850 P_CONTEXT => NULL,
3851 p_Last_Update_Date => l_last_update_date,
3852 p_Last_Updated_By => l_last_updated_by,
3853 p_Last_Update_Login => l_last_update_login,
3854 p_Created_By => l_created_by,
3855 p_Creation_Date => l_creation_date
3856 );
3857
3858 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3859 RAISE FND_API.G_EXC_ERROR;
3860 END IF;
3861
3862 FOR l_defaults_rec in l_defaults_csr
3863 LOOP
3864 PSB_ENTITY_ASSIGNMENT_PVT.Insert_Row
3865 ( p_api_version => 1.0,
3866 p_init_msg_list => FND_API.G_FALSE,
3867 p_commit => FND_API.G_FALSE,
3868 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
3869 p_return_status => l_return_status,
3870 p_msg_count => l_msg_count,
3871 p_msg_data => l_msg_data,
3872 P_ROWID => l_dummy_rowid,
3873 P_ENTITY_SET_ID => l_new_entity_set_id,
3874 P_ENTITY_ID => l_defaults_rec.default_rule_id,
3875 P_PRIORITY => l_defaults_rec.priority,
3876 P_SEVERITY_LEVEL => NULL,
3877 P_EFFECTIVE_START_DATE => SYSDATE,
3878 P_EFFECTIVE_END_DATE => NULL,
3879 p_Last_Update_Date => l_last_update_date,
3880 p_Last_Updated_By => l_last_updated_by,
3881 p_Last_Update_Login => l_last_update_login,
3882 p_Created_By => l_created_by,
3883 p_Creation_Date => l_creation_date
3884 );
3885
3886 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3887 RAISE FND_API.G_EXC_ERROR;
3888 END IF;
3889
3890 END LOOP;
3891
3892 IF FND_API.TO_BOOLEAN(p_commit) THEN
3893 COMMIT WORK;
3894 END IF;
3895
3896 EXCEPTION
3897 WHEN FND_API.G_EXC_ERROR THEN
3898 ROLLBACK TO create_default_rule_set;
3899 x_return_status := FND_API.G_RET_STS_ERROR;
3900 FND_MSG_PUB.Count_And_Get
3901 ( p_count => x_msg_count,
3902 p_data => x_msg_data
3903 );
3904
3905 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3906 ROLLBACK TO create_default_rule_set;
3907 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3908 FND_MSG_PUB.Count_And_Get
3909 ( p_count => x_msg_count,
3910 p_data => x_msg_data
3911 );
3912
3913 WHEN OTHERS THEN
3914 ROLLBACK TO create_default_rule_set;
3915 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3916 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3917 FND_MSG_PUB.Add_Exc_Msg
3918 ( G_PKG_NAME,
3919 l_api_name
3920 );
3921 END IF;
3922
3923 FND_MSG_PUB.Count_And_Get
3924 ( p_count => x_msg_count,
3925 p_data => x_msg_data
3926 );
3927 END Create_Default_Rule_Set;
3928 /* Bug No. 1308558 End */
3929
3930 /* ----------------------------------------------------------------------- */
3931
3932 /* Bug No. 1308558 Start */
3933 PROCEDURE Create_Default_Rule_Set_CP
3934 ( errbuf OUT NOCOPY VARCHAR2,
3935 retcode OUT NOCOPY VARCHAR2,
3936 p_data_extract_id IN NUMBER,
3937 p_rule_set_name IN VARCHAR2
3938 )
3939 IS
3940 l_api_version CONSTANT VARCHAR2(10) := '1.0';
3941 l_api_name CONSTANT VARCHAR2(30) := 'create_default_rule_set_CP';
3942 l_return_status VARCHAR2(1);
3943 l_msg_count NUMBER;
3944 l_msg_data VARCHAR2(2000);
3945
3946 BEGIN
3947 Create_Default_Rule_Set
3948 ( x_return_status => l_return_status,
3949 x_msg_count => l_msg_count,
3950 x_msg_data => l_msg_data,
3951 x_msg_init_list => FND_API.G_TRUE,
3952 p_commit => FND_API.G_TRUE,
3953 p_api_version => 1.0,
3954 p_data_extract_id => p_data_extract_id,
3955 p_rule_set_name => p_rule_set_name
3956 );
3957
3958 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3959 RAISE FND_API.G_EXC_ERROR;
3960 END IF;
3961
3962 EXCEPTION
3963 WHEN FND_API.G_EXC_ERROR THEN
3964 PSB_MESSAGE_S.Print_Error
3965 ( p_mode => FND_FILE.LOG,
3966 p_print_header => FND_API.G_TRUE
3967 );
3968 retcode := 2;
3969
3970 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3971 PSB_MESSAGE_S.Print_Error
3972 ( p_mode => FND_FILE.LOG,
3973 p_print_header => FND_API.G_TRUE
3974 );
3975 retcode := 2;
3976
3977 WHEN OTHERS THEN
3978 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) then
3979 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, l_api_name);
3980 end if;
3981
3982 PSB_MESSAGE_S.Print_Error
3983 ( p_mode => FND_FILE.LOG,
3984 p_print_header => FND_API.G_TRUE
3985 );
3986 retcode := 2;
3987 END;
3988 /* Bug No. 1308558 End */
3989
3990 /* ----------------------------------------------------------------------- */
3991
3992
3993 END PSB_WRHR_EXTRACT_PROCESS;