[Home] [Help]
PACKAGE BODY: APPS.PV_ENRL_PREREQ_BINS_PUB
Source
1 PACKAGE BODY PV_ENRL_PREREQ_BINS_PUB AS
2 /* $Header: pvxpebib.pls 120.3 2008/01/08 08:19:17 hekkiral ship $ */
3
4 /*************************************************************************************/
5 /* */
6 /* */
7 /* */
8 /* Global Variable Declaration */
9 /* */
10 /* */
11 /* */
12 /*************************************************************************************/
13 G_PKG_NAME CONSTANT VARCHAR2(30) := 'PV_ENRL_PREREQ_BINS_PUB';
14 g_log_to_file VARCHAR2(5) := 'N';
15 g_module_name VARCHAR2(60);
16
17 /*************************************************************************************/
18 /* */
19 /* */
20 /* */
21 /* Exceptions to Catch */
22 /* */
23 /* */
24 /* */
25 /*************************************************************************************/
26 g_index_columns_existed EXCEPTION;
27 PRAGMA EXCEPTION_INIT(g_index_columns_existed, -1408);
28
29 -- -----------------------------------------------------
30 -- ORA-00955: name is already used by an existing object
31 -- -----------------------------------------------------
32 g_name_already_used EXCEPTION;
33 PRAGMA EXCEPTION_INIT(g_index_columns_existed, -955);
34
35 PV_DEBUG_HIGH_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
36 PV_DEBUG_LOW_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
37 PV_DEBUG_MEDIUM_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
38
39 --=============================================================================+
40 --| Private Procedure |
41 --| |
42 --| Debug |
43 --| |
44 --| Parameters |
45 --| IN |
46 --| OUT |
47 --| |
48 --| |
49 --| NOTES: |
50 --| |
51 --| HISTORY |
52 --| |
53 --==============================================================================
54 PROCEDURE Debug(
55 p_msg_string IN VARCHAR2,
56 p_msg_type IN VARCHAR2 := 'PV_DEBUG_MESSAGE'
57 )
58 IS
59 BEGIN
60 FND_MESSAGE.Set_Name('PV', p_msg_type);
61 FND_MESSAGE.Set_Token('TEXT', p_msg_string);
62
63 IF (g_log_to_file = 'N') THEN
64 FND_MSG_PUB.Add;
65
66 ELSIF (g_log_to_file = 'Y') THEN
67 FND_FILE.PUT_LINE( FND_FILE.LOG, fnd_message.get );
68 END IF;
69
70 END Debug;
71
72 --=============================================================================+
73 --| Procedure |
74 --| get_matched_partners |
75 --| |
76 --| Parameters |
77 --| IN |
78 --| OUT |
79 --| |
80 --| |
81 --| NOTES |
82 --| |
83 --| HISTORY |
84 --| |
85 --==============================================================================
86 PROCEDURE get_matched_partners(
87 x_return_status OUT NOCOPY VARCHAR2
88 ,x_msg_count OUT NOCOPY NUMBER
89 ,x_msg_data OUT NOCOPY VARCHAR2
90 ,p_program_id IN NUMBER
91 ,x_matched_id_tbl OUT NOCOPY JTF_NUMBER_TABLE
92 )
93 IS
94 CURSOR lc_partner_selection(c_program_id NUMBER) IS
95 SELECT a.attribute_id, a.operator,
96 b.attribute_value, b.attribute_to_value,
97 a.selection_criteria_id,
98 c.return_type,
99 d.currency_code
100 FROM pv_enty_select_criteria a,
101 pv_selected_attr_values b,
102 pv_attributes_vl c,
103 pv_process_rules_b d,
104 pv_partner_program_b e
105 WHERE a.attribute_id = c.attribute_id AND
106 a.selection_criteria_id = b.selection_criteria_id (+) AND
107 a.process_rule_id = d.process_rule_id AND
108 d.process_rule_id = e.prereq_process_rule_id AND
109 e.program_id = c_program_id
110 ORDER BY a.attribute_id, b.selection_criteria_id;
111
112 CURSOR c_get_partner_id IS
113 SELECT partner_id
114 FROM pv_partner_profiles
115 WHERE status = 'A';
116
117 CURSOR c_get_prereq (c_program_id NUMBER) IS
118 SELECT change_from_program_id
119 FROM pv_pg_enrl_change_rules rule
120 WHERE rule.change_to_program_id = c_program_id
121 AND rule.change_direction_code = 'PREREQUISITE'
122 AND rule.EFFECTIVE_FROM_DATE <= SYSDATE
123 AND NVL(rule.EFFECTIVE_TO_DATE, SYSDATE+1) >= SYSDATE
124 AND rule.ACTIVE_FLAG = 'Y';
125
126 CURSOR c_is_no_prereq_membership(c_program_id NUMBER, c_partner_id NUMBER) IS
127 SELECT 1
128 FROM dual
129 WHERE not exists(
130 SELECT 1
131 FROM pv_pg_memberships memb
132 WHERE memb.program_id = c_program_id
133 AND memb.partner_id = c_partner_id
134 AND memb.MEMBERSHIP_STATUS_CODE = 'ACTIVE'
135 AND memb.START_DATE <= SYSDATE
136 AND NVL(memb.ACTUAL_END_DATE,NVL(memb.ORIGINAL_END_DATE,SYSDATE+1)) >= SYSDATE
137 );
138
139 i NUMBER := 1;
140 l_cnt NUMBER := 1;
141 l_attr_matched_id_tbl JTF_NUMBER_TABLE;
142 l_matched_id_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
143 l_attr_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
144 l_attr_opr_tbl JTF_VARCHAR2_TABLE_100 := JTF_VARCHAR2_TABLE_100();
145 l_val_attr_tbl JTF_VARCHAR2_TABLE_4000 := JTF_VARCHAR2_TABLE_4000();
146 l_partner_details JTF_VARCHAR2_TABLE_4000 := JTF_VARCHAR2_TABLE_4000();
147 l_flagcount JTF_VARCHAR2_TABLE_100 := JTF_VARCHAR2_TABLE_100();
148 l_attr_data_type_tbl JTF_VARCHAR2_TABLE_100 := JTF_VARCHAR2_TABLE_100();
149 l_distance_tbl JTF_NUMBER_TABLE;
150 l_distance_uom varchar2(100);
151 l_message varchar2(32000);
152 l_previous_attr_id NUMBER;
153 l_previous_sc_id NUMBER;
154 l_delimiter VARCHAR2(10) := '+++';
155 l_prereq_exist BOOLEAN;
156 l_no_membership BOOLEAN;
157
158 BEGIN
159 --FND_MSG_PUB.g_msg_level_threshold := FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW;
160
161 x_return_status := FND_API.G_RET_STS_SUCCESS;
162
163 -- ------------------------------------------------------------------------
164 -- Get partner selection attribute value and append them to the record
165 -- of tables, l_match_attr_rec.
166 -- The following code also performs AND/OR logic. Attribute values
167 -- involved in an OR logic will be concatenated in a string separated
168 -- by a delimiter.
169 -- ------------------------------------------------------------------------
170 FOR x IN lc_partner_selection(p_program_id) LOOP
171 IF (l_previous_attr_id = x.attribute_id AND
172 l_previous_sc_id = x.selection_criteria_id)
173 THEN
174 l_val_attr_tbl(i - 1) := l_val_attr_tbl(i - 1) ||
175 l_delimiter || x.attribute_value;
176
177 IF (x.return_type = 'CURRENCY') THEN
178 l_val_attr_tbl(i - 1) := l_val_attr_tbl(i - 1) || ':::' ||
179 x.currency_code || ':::' || TO_CHAR(SYSDATE, 'yyyymmddhh24miss');
180 END IF;
181 ELSE
182 l_val_attr_tbl.EXTEND;
183 l_attr_tbl.EXTEND;
184 l_attr_data_type_tbl.EXTEND;
185 l_attr_opr_tbl.EXTEND;
186
187 l_val_attr_tbl(i) := x.attribute_value;
188 l_attr_tbl(i) := x.attribute_id;
189 l_attr_data_type_tbl(i) := x.return_type;
190 l_attr_opr_tbl(i) := x.operator;
191
192 IF (x.return_type = 'CURRENCY') THEN
193 l_val_attr_tbl(i) := l_val_attr_tbl(i) || ':::' ||
194 x.currency_code || ':::' || TO_CHAR(SYSDATE, 'yyyymmddhh24miss');
195 END IF;
196
197 IF (x.operator = 'BETWEEN') THEN
198 l_attr_opr_tbl(i) := '>=';
199
200 i := i + 1;
201 l_val_attr_tbl.EXTEND;
202 l_attr_tbl.EXTEND;
203 l_attr_data_type_tbl.EXTEND;
204 l_attr_opr_tbl.EXTEND;
205 l_attr_opr_tbl(i) := '<=';
206 l_attr_tbl(i) := x.attribute_id;
207 l_attr_data_type_tbl(i) := x.return_type;
208 l_attr_data_type_tbl(i) := x.attribute_to_value;
209
210 IF (x.return_type = 'CURRENCY') THEN
211 l_val_attr_tbl(i) := l_val_attr_tbl(i) || ':::' ||
212 x.currency_code || ':::' || TO_CHAR(SYSDATE, 'yyyymmddhh24miss');
213 END IF;
214 END IF;
215
216 i := i + 1;
217 END IF;
218
219 l_previous_attr_id := x.attribute_id;
220 l_previous_sc_id := x.selection_criteria_id;
221 END LOOP;
222
223 Debug('l_attr_tbl.count = ' || l_attr_tbl.count);
224 IF l_attr_tbl.count > 0 THEN
225 --Debug('l_attr_tbl.count > 0');
226
227 --FND_MSG_PUB.g_msg_level_threshold := FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH;
228
229 pv_match_v2_pub.manual_match(
230 p_api_version_number => 1.0
231 , p_attr_id_tbl => l_attr_tbl
232 , p_attr_value_tbl => l_val_attr_tbl
233 , p_attr_operator_tbl => l_attr_opr_tbl
234 , p_attr_data_type_tbl => l_attr_data_type_tbl
235 , p_attr_selection_mode => 'OR'
236 , p_att_delmter => '+++'
237 , p_selection_criteria => 'ALL'
238 , p_resource_id => NULL
239 , p_lead_id => NULL
240 , p_auto_match_flag => 'N'
241 , p_get_distance_flag => 'F'
242 , p_top_n_rows_by_profile => 'F'
243 , x_matched_id => l_attr_matched_id_tbl
244 , x_partner_details => l_partner_details
245 , x_distance_tbl => l_distance_tbl
246 , x_distance_uom_returned => l_distance_uom
247 , x_flagcount => l_flagcount
248 , x_return_status => x_return_status
249 , x_msg_count => x_msg_count
250 , x_msg_data => x_msg_data
251 );
252 --FND_MSG_PUB.g_msg_level_threshold := FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW;
253
254 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
255 Debug('x_return_status <> FND_API.G_RET_STS_SUCCESS');
256 RAISE Fnd_Api.G_EXC_ERROR;
257 END IF;
258 ELSE -- IF l_attr_tbl.count = 0
259 x_return_status := FND_API.G_RET_STS_SUCCESS;
260 -- If there is no attribute, l_attr_matched_id_tbl will not be set (will be null)
261 -- go to ELSIF (l_attr_matched_id_tbl is null)
262 END IF;
263
264 Debug('x_return_status: ' || x_return_status);
265
266 IF (l_attr_matched_id_tbl is not null) THEN
267 Debug('l_attr_matched_id_tbl.count = ' || l_attr_matched_id_tbl.count);
268 FOR i IN 1..l_attr_matched_id_tbl.count LOOP
269 Debug('before: l_attr_matched_id_tbl(' || i || '):' || l_attr_matched_id_tbl(i));
270 --Debug('before: p_partner_details(' || i || '):' || l_partner_details(i));
271 l_prereq_exist := false;
272 l_no_membership := false;
273 FOR x IN c_get_prereq (p_program_id) LOOP
274 Debug('after: x.change_from_program_id = ' || x.change_from_program_id);
275 l_prereq_exist := true;
276 FOR y IN c_is_no_prereq_membership(x.change_from_program_id, l_attr_matched_id_tbl(i)) LOOP
277 Debug('prereq exists but no active membership');
278 l_no_membership := true;
279 END LOOP;
280 EXIT WHEN l_no_membership;
281 END LOOP;
282 IF (l_prereq_exist) THEN
283 IF (not l_no_membership) THEN
284 l_matched_id_tbl.extend;
285 l_matched_id_tbl(l_cnt) := l_attr_matched_id_tbl(i);
286 l_cnt := l_cnt + 1;
287 END IF;
288 ELSIF (NOT l_prereq_exist) THEN
289 --Debug('no prereq exist: l_attr_matched_id_tbl(' || i || '):' || l_attr_matched_id_tbl(i));
290 l_matched_id_tbl.extend;
291 l_matched_id_tbl(l_cnt) := l_attr_matched_id_tbl(i);
292 l_cnt := l_cnt + 1;
293 END IF;
294 END LOOP;
295 ELSIF (l_attr_matched_id_tbl is null) THEN
296 Debug('l_attr_matched_id_tbl is null');
297 FOR x IN c_get_partner_id LOOP
298 Debug('before: x.partner_id = ' || x.partner_id);
299 l_prereq_exist := false;
300 l_no_membership := false;
301 FOR y IN c_get_prereq (p_program_id) LOOP
302 Debug('after: y.change_from_program_id = ' || y.change_from_program_id);
303 l_prereq_exist := true;
304 FOR z IN c_is_no_prereq_membership(y.change_from_program_id, x.partner_id) LOOP
305 Debug('prereq exists but no active membership');
306 l_no_membership := true;
307 END LOOP;
308 EXIT WHEN l_no_membership;
309 END LOOP;
310 IF (l_prereq_exist) THEN
311 IF (not l_no_membership) THEN
312 l_matched_id_tbl.extend;
313 l_matched_id_tbl(l_cnt) := x.partner_id;
314 l_cnt := l_cnt + 1;
315 END IF;
316 ELSIF (NOT l_prereq_exist) THEN
317 --Debug('no prereq exist: x.partner_id = ' || x.partner_id);
318 l_matched_id_tbl.extend;
319 l_matched_id_tbl(l_cnt) := x.partner_id;
320 l_cnt := l_cnt + 1;
321 END IF;
322 END LOOP;
323 END IF;
324
325 x_matched_id_tbl := l_matched_id_tbl;
326
327 Debug('END get_matched_partners');
328
329 EXCEPTION
330 WHEN Fnd_Api.G_EXC_ERROR THEN
331 x_return_status := Fnd_Api.G_RET_STS_ERROR;
332 -- Standard call to get message count and if count=1, get the message
333 Fnd_Msg_Pub.Count_And_Get (
334 p_encoded => Fnd_Api.G_FALSE,
335 p_count => x_msg_count,
336 p_data => x_msg_data
337 );
338 WHEN OTHERS THEN
339 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
340 -- Standard call to get message count and if count=1, get the message
341 Fnd_Msg_Pub.Count_And_Get (
342 p_encoded => Fnd_Api.G_FALSE,
343 p_count => x_msg_count,
344 p_data => x_msg_data
345 );
346 END get_matched_partners;
347
348 --=============================================================================+
349 --| Procedure |
350 --| Exec_Create_Elig_Prgm |
351 --| |
352 --| Parameters |
353 --| IN |
354 --| OUT |
355 --| |
356 --| |
357 --| NOTES |
358 --| |
359 --| HISTORY |
360 --| |
361 --==============================================================================
362 PROCEDURE Exec_Create_Elig_Prgm ( ERRBUF OUT NOCOPY VARCHAR2,
363 RETCODE OUT NOCOPY VARCHAR2,
364 p_log_to_file IN VARCHAR2 := 'Y')
365 IS
366 -- -----------------------------------------------------------------------
367 -- Cursors
368 -- -----------------------------------------------------------------------
369 CURSOR c_get_program_ids IS
370 SELECT prg.program_id
371 FROM pv_partner_program_b prg
372 WHERE prg.program_status_code = 'ACTIVE'
373 AND prg.program_level_code = 'MEMBERSHIP'
374 AND NVL(prg.allow_enrl_until_date, SYSDATE +1) >= SYSDATE
375 AND prg.enabled_flag = 'Y';
376
377 -- -----------------------------------------------------------------------
378 -- Local Variables
379 -- -----------------------------------------------------------------------
380 l_api_package_name VARCHAR2(30) := 'PV_ENRL_PREREQ_BINS_PUB';
381 l_matched_id_tbl JTF_NUMBER_TABLE;
382 l_mirror_table VARCHAR2(30);
383 l_cache_table VARCHAR2(30);
384 l_pv_schema_name VARCHAR2(30);
385 l_user_id NUMBER := FND_GLOBAL.USER_ID();
386 l_total_start NUMBER;
387 l_start NUMBER;
388 l_elapsed_time NUMBER;
389 l_return_status VARCHAR2(100);
390 l_msg_count NUMBER;
391 l_msg_data VARCHAR2(500);
392 l_end_refresh_flag BOOLEAN;
393 l_elapsed_time2 NUMBER;
394 l_refresh_type VARCHAR2(30);
395 l_incr_timestamp VARCHAR2(50);
396 BEGIN
397 -- -----------------------------------------------------------------------
398 -- Set variables.
399 -- -----------------------------------------------------------------------
400 l_total_start := dbms_utility.get_time;
401
402 IF (p_log_to_file <> 'Y') THEN
403 g_log_to_file := 'N';
404 ELSE
405 g_log_to_file := 'Y';
406 END IF;
407
408 g_module_name := 'Partner Program Eligibilities';
409
410 -- -----------------------------------------------------------------------
411 -- Exit the program if there is already a session running.
412 -- -----------------------------------------------------------------------
413 FOR x IN (SELECT COUNT(*) count
414 FROM v$session
415 WHERE module = g_module_name)
416 LOOP
417 IF (x.count > 0) THEN
418 Debug('There is already a Refresh Eligibilities CC session running.');
419 Debug('The program will now exit.');
420 RETURN;
421 END IF;
422 END LOOP;
423
424 -- -----------------------------------------------------------------------
425 -- Code Instrumentation
426 -- -----------------------------------------------------------------------
427 dbms_application_info.set_client_info(
428 client_info => 'p_log_to_file = ' || p_log_to_file
429 );
430
431 dbms_application_info.set_module(
432 module_name => g_module_name,
433 action_name => 'STARTUP'
434 );
435
436 -- -----------------------------------------------------------------------
437 -- Start time message...
438 -- -----------------------------------------------------------------------
439 FND_MESSAGE.SET_NAME(application => 'PV',
440 name => 'PV_GET_ELIG_PRGM_START_TIME');
441 FND_MESSAGE.SET_TOKEN(token => 'P_DATE_TIME',
442 value => TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS'));
443 IF(g_log_to_file = 'Y') THEN
444 FND_FILE.PUT_LINE(FND_FILE.LOG, fnd_message.get);
445 FND_FILE.NEW_LINE(FND_FILE.LOG, 1);
446 ELSE
447 FND_MSG_PUB.ADD;
448 END IF;
449
450 -- -----------------------------------------------------------------------
451 -- Code Instrumentation
452 -- -----------------------------------------------------------------------
453 l_start := dbms_utility.get_time;
454 Debug('~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~');
455 Debug('Pre-Processing....................................................');
456 dbms_application_info.set_module(
457 module_name => g_module_name,
458 action_name => 'Pre-Processing'
459 );
460
461 -- -----------------------------------------------------------------------
462 -- Pre-processing steps including synonym recovery, retrieving PV schema,
463 -- retrieving underlying tables for the search and the mirror table,
464 -- alter/drop indexes, etc.
465 -- -----------------------------------------------------------------------
466 PV_CONTEXT_VALUES.Pre_Processing (
467 p_synonym_name => 'PV_PG_ELIG_PROGRAMS',
468 p_mirror_synonym_name => 'PV_PG_ELIG_PROG_MIRR',
469 p_temp_synonym_name => 'PV_PG_ELIG_PROGRAMS_TMP',
470 p_log_to_file => g_log_to_file,
471 p_pv_schema_name => l_pv_schema_name,
472 p_search_table => l_cache_table,
473 p_mirror_table => l_mirror_table,
474 p_end_refresh_flag => l_end_refresh_flag,
475 p_out_refresh_type => l_refresh_type,
476 p_module_name => g_module_name
477 );
478
479 Debug('Elapsed Time (Pre-Processing): ' || (DBMS_UTILITY.get_time - l_start) || ' hsec');
480 l_start := dbms_utility.get_time;
481
482 -- ------------------------------------------------------------------
483 -- Compute the partners eligibilities for each program
484 -- ------------------------------------------------------------------
485 Debug('~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~');
486 Debug('Compute the partners eligibilities for each program');
487 dbms_application_info.set_module(
488 module_name => g_module_name,
489 action_name => 'Compute the partners eligibilities for each program'
490 );
491
492 FOR x IN c_get_program_ids LOOP
493 Debug('x.program_id = ' || x.program_id);
494 get_matched_partners (
495 x_return_status => l_return_status
496 ,x_msg_count => l_msg_count
497 ,x_msg_data => l_msg_data
498 ,p_program_id => x.program_id
499 ,x_matched_id_tbl => l_matched_id_tbl
500 );
501
502 Debug('get_matched_partners(): x_return_status = ' || l_return_status);
503
504 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
505 Debug('get_matched_partners failed when program_id = ' || x.program_id);
506 FND_MESSAGE.SET_NAME(application => 'PV',
507 name => 'PV_MATCH_PARTNERS_FAILED');
508 FND_MESSAGE.SET_TOKEN(token => 'P_PROGRAM_ID',
509 value => x.program_id);
510 IF (g_log_to_file = 'Y') THEN
511 FND_FILE.PUT_LINE( FND_FILE.LOG, fnd_message.get );
512 FND_FILE.NEW_LINE( FND_FILE.LOG, 1 );
513 ELSE
514 FND_MSG_PUB.Add;
515 END IF;
516 RAISE Fnd_Api.G_EXC_ERROR;
517 END IF;
518
519 IF (l_matched_id_tbl is not null) THEN
520 Debug('l_matched_id_tbl is not null');
521 FORALL l_cnt IN 1..l_matched_id_tbl.count
522 INSERT
523 INTO PV_PG_ELIG_PROG_MIRR
524 (
525 ELIG_PROGRAM_ID,
526 PROGRAM_ID,
527 PARTNER_ID,
528 ELIGIBILITY_CRIT_CODE,
529 CREATION_DATE,
530 CREATED_BY,
531 LAST_UPDATE_DATE,
532 LAST_UPDATED_BY,
533 LAST_UPDATE_LOGIN ,
534 OBJECT_Version_number
535 )
536 VALUES (
537 PV_PG_ELIG_PROGRAMS_S.nextval,
538 x.program_id,
539 l_matched_id_tbl(l_cnt),
540 'PREREQ',
541 SYSDATE,
542 l_user_id,
543 SYSDATE,
544 l_user_id,
545 l_user_id,
546 1.0
547 );
548 END IF;
549 END LOOP;
550 Debug('Load test: Elapsed Time: ' || (DBMS_UTILITY.get_time - l_start) || ' hsec');
551 l_start := dbms_utility.get_time;
552
553
554 -- *****************************************************************
555 -- *****************************************************************
556 -- Post Loading Processing
557 -- *****************************************************************
558 -- *****************************************************************
559 Debug('~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~');
560 Debug('Post loading processing...............................................');
561 dbms_application_info.set_module(
562 module_name => g_module_name,
563 action_name => 'Post Processing'
564 );
565 PV_CONTEXT_VALUES.Post_Processing (
566 p_synonym_name => 'PV_PG_ELIG_PROGRAMS',
567 p_mirror_synonym_name => 'PV_PG_ELIG_PROG_MIRR',
568 p_temp_synonym_name => 'PV_PG_ELIG_PROGRAMS_TMP',
569 p_log_to_file => g_log_to_file,
570 p_pv_schema_name => l_pv_schema_name,
571 p_search_table => l_cache_table,
572 p_mirror_table => l_mirror_table,
573 p_incr_timestamp => l_incr_timestamp,
574 p_api_package_name => l_api_package_name,
575 p_module_name => g_module_name
576 );
577
578
579 COMMIT;
580
581 Debug('Elapsed Time (Total Post-Processing): ' || (DBMS_UTILITY.get_time - l_start) || ' hsec');
582
583 -- -------------------------------------------------------------------------
584 -- Display End Time Message.
585 -- -------------------------------------------------------------------------
586 Debug('=====================================================================');
587 FND_MESSAGE.SET_NAME(application => 'PV',
588 name => 'PV_CREATE_CONTEXT_END_TIME');
589 FND_MESSAGE.SET_TOKEN(token => 'P_DATE_TIME',
590 value => TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') );
591
592 IF (g_log_to_file = 'Y') THEN
593 FND_FILE.PUT_LINE( FND_FILE.LOG, fnd_message.get );
594 FND_FILE.NEW_LINE( FND_FILE.LOG, 1 );
595
596 ELSE
597 FND_MSG_PUB.Add;
598 END IF;
599
600 l_elapsed_time := DBMS_UTILITY.get_time - l_total_start;
601 Debug('=====================================================================');
602 Debug('Total Elapsed Time: ' || l_elapsed_time || ' hsec' || ' = ' ||
603 ROUND((l_elapsed_time/6000), 2) || ' minutes');
604 Debug('=====================================================================');
605
606 EXCEPTION
607 WHEN Fnd_Api.G_EXC_ERROR THEN
608 IF l_msg_count > 1 THEN
609 fnd_msg_pub.reset;
610 FOR i IN 1..l_msg_count LOOP
611 Debug(fnd_msg_pub.get(p_encoded => fnd_api.g_false));
612 END LOOP;
613 ELSE
614 Debug(l_msg_data);
615 END IF;
616 RETCODE := '2';
617 ROLLBACK;
618
619 WHEN OTHERS THEN
620 Debug('OTHERS');
621 Debug(SQLCODE || ': ' || SQLERRM);
622 RETCODE := '2';
623 ROLLBACK;
624
625 END Exec_Create_Elig_Prgm;
626
627 END PV_ENRL_PREREQ_BINS_PUB;
628