[Home] [Help]
PACKAGE BODY: APPS.FII_PARTY_MKT_CLASS_C
Source
1 PACKAGE BODY fii_party_mkt_class_c AS
2 /* $Header: FIIPCLSB.pls 120.11 2006/10/26 19:17:52 mmanasse noship $ */
3
4 g_bis_setup_exception EXCEPTION;
5 g_user_id NUMBER := fnd_global.user_id;
6 g_login_id NUMBER := fnd_global.login_id;
7 g_run_date DATE := sysdate;
8 g_collection_from_date DATE;
9 g_collection_to_date DATE;
10 g_process_type VARCHAR2(30) := NULL;
11 g_class_type VARCHAR2(100);
12
13 /* Last Collection Details */
14 g_last_collection_from_date DATE := NULL;
15 g_last_collection_to_date DATE := NULL;
16 g_last_process_type VARCHAR2(30) := NULL;
17 g_last_success_flag VARCHAR2(30) := NULL;
18
19 /* Global return Variable */
20 g_errbuf VARCHAR2(4000);
21 g_retcode VARCHAR2(10) := 0;
22
23 /* Debugging variable*/
24 g_phase varchar2(500);
25
26 PROCEDURE last_collection_detail;
27 FUNCTION non_hierarchical_class RETURN BOOLEAN;
28 PROCEDURE initial_load;
29 PROCEDURE incremental_load;
30
31 PROCEDURE load
32 ( errbuf IN OUT NOCOPY VARCHAR2
33 , retcode IN OUT NOCOPY VARCHAR2
34 , p_load_mode IN VARCHAR2 DEFAULT 'INCRE'
35 ) as
36
37 l_exception exception;
38 l_error_message varchar2(4000);
39 l_setup_ok BOOLEAN;
40
41 BEGIN
42 l_setup_ok := FALSE;
43 -- Retrieve last collection details
44 last_collection_detail();
45
46 IF (g_retcode <> 0)
47 THEN
48 RAISE l_exception;
49 END IF;
50
51 -- Check classification category profile value.
52 -- If the classification is hierarchical or allows multiple assignments
53 -- Then exit with error.
54
55 IF NOT non_hierarchical_class( )
56 THEN
57 bis_collection_utilities.log('Error in Party Market Classification Type global setup ');
58 bis_collection_utilities.log('Make sure that Party Market Classification Type profile is non hierarchical and does not allow multiple parent code or multiple class code assignment. ');
59 raise g_bis_setup_exception;
60 END IF;
61
62 IF (g_retcode <> 0)
63 THEN
64 RAISE l_exception;
65 END IF;
66
67 l_setup_ok := BIS_COLLECTION_UTILITIES.setup('FII_PARTY_MKT_CLASS');
68 IF (NOT l_setup_ok) THEN
69 errbuf := fnd_message.get;
70 bis_collection_utilities.log( 'BIS Setup Failure ',0);
71 RAISE_APPLICATION_ERROR (-20000, 'Error in SETUP: ' || errbuf);
72 END IF;
73
74 -- check to see whether initial load or incremental load.
75 -- p_load_mode = 'INIT' initial load
76 -- p_load_mode = 'INCRE' incremental load
77 IF (upper(p_load_mode) = 'INIT')
78 THEN
79 -- Current request is for initial
80 -- OR
81 -- Last Initial is unseccessfull
82 g_phase := 'Setting dates for initial load.';
83 bis_collection_utilities.log('Setting dates for initial load. ');
84 g_collection_from_date := bis_common_parameters.get_GLOBAL_START_DATE; --global start date
85 if (g_collection_from_date is null) then
86 raise g_bis_setup_exception;
87 end if;
88 g_collection_to_date := g_run_date;
89 g_process_type := 'INIT';
90 initial_load();
91 ELSIF (upper(p_load_mode) = 'INCRE')
92 THEN
93 IF( g_last_success_flag = 'Y')
94 THEN
95 -- Last Load is successfull
96 bis_collection_utilities.log('Setting dates for incremental load. ');
97 g_collection_from_date := g_last_collection_to_date;
98 g_collection_to_date := g_run_date;
99 ELSE
100 -- Last Load is unsuccessfull
101 bis_collection_utilities.log('Unsuccessfull initial load. Contact Administrator to complete initial load of party market classification');
102 RAISE l_exception;
103 END IF;
104 g_process_type := 'INCRE';
105 incremental_load();
106 ELSE
107 bis_collection_utilities.log('Please enter a valid parameter for load mode. Use INIT for initial load and INCRE for incremental load.');
108 RAISE l_exception;
109 END IF;
110
111 IF (g_retcode <> 0)
112 THEN
113 RAISE l_exception;
114 END IF;
115
116 bis_collection_utilities.log('Current Collection Details: ');
117 bis_collection_utilities.log(' Process Type : ' || g_process_type );
118 bis_collection_utilities.log(' Collection From Date : ' || to_char(g_collection_from_date,'DD-MON-YYYY HH24:MI:SS'));
119 bis_collection_utilities.log(' Collection To Date : ' || to_char(g_collection_to_date,'DD-MON-YYYY HH24:MI:SS'));
120 bis_collection_utilities.log(' Success Flag : ' || 'Y');
121
122 COMMIT;
123
124 bis_collection_utilities.log('SUCCESS: Load Program Successfully completed ' ||
125 fnd_date.date_to_displayDT(sysdate),0);
126
127 BIS_COLLECTION_UTILITIES.wrapup(TRUE,
128 -1,
129 'FII_PARTY_MKT_CLASS COLLECTION SUCCEEFULL',
130 g_collection_from_date,
131 g_collection_to_date
132 );
133 EXCEPTION
134 WHEN l_exception THEN
135 errbuf := sqlerrm;
136 retcode := 2;
137 WHEN g_bis_setup_exception THEN
138 bis_collection_utilities.log('Error partner classification load program ');
139 bis_collection_utilities.log('Phase : ' || g_phase);
140 retcode := -1;
141 rollback;
142 WHEN OTHERS THEN
143 l_error_message := sqlerrm;
144 bis_collection_utilities.log('Error partner classification load program ');
145 bis_collection_utilities.log('Error Message : ' || l_error_message);
146 bis_collection_utilities.log('Phase : ' || g_phase);
147 errbuf := l_error_message;
148 retcode := 2;
149 END load;
150
151 PROCEDURE last_collection_detail IS
152 l_error_message VARCHAR2(1000);
153 l_period_from DATE;
154 l_period_to DATE;
155
156 BEGIN
157 g_phase := 'Getting last refresh dates';
158
159 BIS_COLLECTION_UTILITIES.get_last_refresh_dates(
160 p_object_name => 'FII_PARTY_MKT_CLASS',
161 p_start_date => l_period_from,
162 p_end_date => l_period_to,
163 p_period_from => g_last_collection_from_date,
164 p_period_to => g_last_collection_to_date);
165
166 IF (g_last_collection_from_date IS NULL)
167 THEN
168 g_last_success_flag := NULL;
169 ELSE
170 g_last_success_flag := 'Y';
171 END IF;
172
173 bis_collection_utilities.log('Last Collection Details: ');
174 bis_collection_utilities.log(' Collection From Date : ' || to_char(g_last_collection_from_date,'DD-MON-YYYY HH24:MI:SS'));
175 bis_collection_utilities.log(' Collection To Date : ' || to_char(g_last_collection_to_date,'DD-MON-YYYY HH24:MI:SS'));
176
177 EXCEPTION
178 WHEN OTHERS THEN
179 l_error_message := sqlerrm;
180 bis_collection_utilities.log('Error While collecting last log information ');
181 bis_collection_utilities.log('Error Message : ' || l_error_message);
182 bis_collection_utilities.log('Phase : ' || g_phase);
183 g_errbuf := l_error_message;
184 g_retcode := 2;
185 END last_collection_detail;
186
187 FUNCTION non_hierarchical_class RETURN BOOLEAN IS
188 l_ret_val NUMBER;
189 l_error_message VARCHAR2(1000);
190 BEGIN
191 l_ret_val := 0;
192 -- Select Classification type
193 SELECT nvl(bis_common_parameters.GET_BIS_CUST_CLASS_TYPE, -1)
194 INTO g_class_type
195 FROM DUAL;
196
197 -- Check if the classification type is non_hierarchical.
198 g_phase := 'Check if the classification type is non_hierarchical';
199
200 SELECT count(b.CLASS_CATEGORY) INTO l_ret_val
201 FROM hz_class_categories c,hz_class_code_relations b -- changes for bug 4130053
202 Where c.CLASS_CATEGORY = g_class_type
203 AND b.class_category = g_class_type
204 AND b.START_DATE_ACTIVE <= g_run_date
205 AND NVL(b.END_DATE_ACTIVE, g_run_date+1) > g_run_date;
206
207 /*
208 -- Temporary for testing
209 g_class_type := 'CUSTOMER_CATEGORY';
210 l_ret_val := 1;
211 -- END temporary
212 */
213 IF (l_ret_val <> 0)
214 THEN
215 RETURN FALSE;
216 bis_collection_utilities.log('Classification Category is hierarchical. ');
217 ELSE
218 bis_collection_utilities.log('Classification Category is non-hierarchical. ');
219
220 END IF;
221 -- Checks if multiple parent flag is set to Y or multiple class code assignment flag is set to 'Y'.
222 g_phase := 'Checks if multiple parent flag is set to Y or multiple class code assignment flag is set to Yes';
223
224 SELECT count(c.CLASS_CATEGORY) INTO l_ret_val
225 FROM hz_class_categories c -- changes for bug 4207952
226 Where c.CLASS_CATEGORY = g_class_type
227 AND (c.allow_multi_parent_flag ='Y'
228 OR c.allow_multi_assign_flag = 'Y');
229
230 IF (l_ret_val <> 0)
231 THEN
232 RETURN FALSE;
233 bis_collection_utilities.log('Classification Category allows multiple parent code or multiple class code assignment. ');
234 ELSE
235 RETURN TRUE;
236 bis_collection_utilities.log('Classification Category is non-hierarchial and does not allow multiple parent code or multiple class code assignment . ');
237
238 END IF;
239
240 EXCEPTION
241 WHEN OTHERS THEN
242 l_error_message := sqlerrm;
243 bis_collection_utilities.log('Error while verifying partner classification global setup ');
244 bis_collection_utilities.log('Error Message : ' || l_error_message);
245 bis_collection_utilities.log('Phase : ' || g_phase);
246 g_errbuf := l_error_message;
247 g_retcode := 2;
248 END non_hierarchical_class;
249
250 -- Load FII_PARTY_MKT_CLASS in initial mode
251 -- Find the latest class code that was assigned to a party
252 -- If no class code assignment was found for a customer a record with class code '-1' will be
253 -- created for the customer
254 PROCEDURE initial_load IS
255 l_sql_string VARCHAR2(1000);
256 l_fii_schema VARCHAR2(100);
257 l_error_message VARCHAR2(4000);
258 l_max_batch_party_id NUMBER(15);
259
260 BEGIN
261 l_fii_schema := 'FII';
262
263 g_phase := 'Populating IND_MAX_BATCH_PARTY_ID in fii_change_log';
264 FII_UTIL.Write_Log(g_phase);
265
266 select nvl(max(batch_party_id), -1)
267 into l_max_batch_party_id
268 from hz_merge_party_history m,
269 hz_merge_dictionary d
270 where m.merge_dict_id = d.merge_dict_id
271 and d.entity_name = 'HZ_PARTIES';
272
273 INSERT INTO fii_change_log
274 (log_item, item_value, CREATION_DATE, CREATED_BY,
275 LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN)
276 (SELECT 'IND_MAX_BATCH_PARTY_ID',
277 l_max_batch_party_id,
278 sysdate, --CREATION_DATE,
279 g_user_id, --CREATED_BY,
280 sysdate, --LAST_UPDATE_DATE,
281 g_user_id, --LAST_UPDATED_BY,
282 g_login_id --LAST_UPDATE_LOGIN
283 FROM DUAL
284 WHERE NOT EXISTS
285 (select 1 from fii_change_log
286 where log_item = 'IND_MAX_BATCH_PARTY_ID'));
287
288 IF (SQL%ROWCOUNT = 0) THEN
289 UPDATE fii_change_log
290 SET item_value = l_max_batch_party_id,
291 last_update_date = sysdate,
292 last_update_login = g_login_id,
293 last_updated_by = g_user_id
294 WHERE log_item = 'IND_MAX_BATCH_PARTY_ID';
295 END IF;
296
297 -- Identify Last valid class code for each party and create a record in FII_PARTY_MKT_CLASS table
298 g_phase := 'Identify Last valid class code for each party and create a record in FII_PARTY_MKT_CLASS table';
299
300 bis_collection_utilities.log('Truncating FII_PARTY_MKT_CLASS Table ');
301
302 l_sql_string := 'TRUNCATE TABLE ' || l_fii_schema ||'.FII_PARTY_MKT_CLASS';
303 EXECUTE IMMEDIATE l_sql_string;
304
305 bis_collection_utilities.log('Bis log file was reset for FII_PARTY_MKT_CLASS. ');
306 BIS_COLLECTION_UTILITIES.DeleteLogForObject('FII_PARTY_MKT_CLASS');
307
308 bis_collection_utilities.log('Populating FII_PARTY_MKT_CLASS table');
309 g_phase := 'Populating FII_PARTY_MKT_CLASS table';
310
311 INSERT /*+ APPEND */ INTO fii_party_mkt_class
312 (
313 party_id,
314 class_category,
315 class_code,
316 creation_date,
317 created_by,
318 last_update_date,
319 last_updated_by,
320 last_update_login
321 )
322 SELECT
323 party_id,
324 class_category,
325 MAX(class_code) KEEP (DENSE_RANK LAST ORDER BY party_id, active_priority, last_update_date) class_code,
326 sysdate,
327 g_user_id,
328 sysdate,
329 g_user_id,
330 g_login_id
331 FROM
332 (
333 SELECT /*+ PARALLEL(HZ_CODE_ASSIGNMENTS) */
334 owner_table_id party_id,
335 class_category,
336 class_code,
337 creation_date,
338 last_update_date,
339 CASE WHEN primary_flag = 'Y'
340 THEN 2 ELSE 1 END active_priority
341 FROM hz_code_assignments
342 WHERE class_category = g_class_type
343 AND owner_table_name = 'HZ_PARTIES'
344 AND g_collection_to_date BETWEEN start_date_active AND nvl(end_date_active, g_collection_to_date+1)
345 ORDER BY owner_table_id
346 )
347 GROUP BY party_id, class_category;
348
349 FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into fii_party_mkt_class');
350
351 commit; --Added for ORA-12838: cannot read/modify an object after modifying it in parallel
352
353 bis_collection_utilities.log('Populating FII_PARTY_MKT_CLASS table for unassigned customers');
354
355 g_phase := 'Populating FII_PARTY_MKT_CLASS table for unassigned customers';
356
357 INSERT /*+ APPEND */ INTO fii_party_mkt_class
358 (
359 party_id,
360 class_category,
361 class_code,
362 creation_date,
363 created_by,
364 last_update_date,
365 last_updated_by,
366 last_update_login
367 )
368
369 SELECT
370 party_id,
371 g_class_type class_category,
372 '-1' class_code,
373 sysdate creation_date,
374 g_user_id created_by,
375 sysdate last_update_date,
376 g_user_id last_updated_by,
377 g_login_id last_update_login
378 FROM
379 ( SELECT /*+ PARALLEL(HZ_CUST_ACCOUNTS) */
380 DISTINCT party_id
381 FROM hz_cust_accounts
382 WHERE party_id NOT IN (SELECT /*+ PARALLEL(FII_PARTY_MKT_CLASS) */
383 party_id
384 FROM fii_party_mkt_class
385 WHERE class_category = g_class_type
386 )
387 );
388
389 FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into fii_party_mkt_class');
390
391 EXCEPTION
392 WHEN OTHERS THEN
393 l_error_message := sqlerrm;
394 bis_collection_utilities.log('Error while doing initial population of partner classification table ');
395 bis_collection_utilities.log('Error Message : ' || l_error_message);
396 bis_collection_utilities.log('Phase : ' || g_phase);
397 g_errbuf := l_error_message;
398 g_retcode := 2;
399 END initial_load;
400
401 -- Load FII_PARTY_MKT_CLASS in incremental mode
402 PROCEDURE incremental_load IS
403 l_sql_string VARCHAR2(1000);
404 l_fii_schema VARCHAR2(100);
405 l_error_message VARCHAR2(4000);
406 lDateFormat VARCHAR2(50);
407 l_max_batch_party_id NUMBER(15);
408
409 BEGIN
410 l_fii_schema := 'FII';
411
412 g_phase := 'Getting maximum batch_party_id from fii_change_log table';
413 FII_UTIL.Write_Log (g_phase);
414
415 select item_value
416 into l_max_batch_party_id
417 from fii_change_log
418 where log_item = 'IND_MAX_BATCH_PARTY_ID';
419
420 FII_UTIL.Write_Log ('IND_MAX_BATCH_PARTY_ID = '||l_max_batch_party_id);
421
422 g_phase := 'Deleting merged parties';
423 FII_UTIL.Write_Log (g_phase);
424
425 Delete from fii_party_mkt_class
426 where party_id in
427 (select from_entity_id
428 from hz_merge_party_history m,
429 hz_merge_dictionary d
430 where m.merge_dict_id = d.merge_dict_id
431 and d.entity_name = 'HZ_PARTIES'
432 and batch_party_id > l_max_batch_party_id);
433
434 g_phase := 'Logging maximum batch_party_id into fii_change_log table';
435 FII_UTIL.Write_Log (g_phase);
436
437 select nvl(max(batch_party_id), -1)
438 into l_max_batch_party_id
439 from hz_merge_party_history m,
440 hz_merge_dictionary d
441 where m.merge_dict_id = d.merge_dict_id
442 and d.entity_name = 'HZ_PARTIES';
443
444 INSERT INTO fii_change_log
445 (log_item, item_value, CREATION_DATE, CREATED_BY,
446 LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN)
447 (SELECT 'IND_MAX_BATCH_PARTY_ID',
448 l_max_batch_party_id,
449 sysdate, --CREATION_DATE,
450 g_user_id, --CREATED_BY,
451 sysdate, --LAST_UPDATE_DATE,
452 g_user_id, --LAST_UPDATED_BY,
453 g_login_id --LAST_UPDATE_LOGIN
454 FROM DUAL
455 WHERE NOT EXISTS
456 (select 1 from fii_change_log
457 where log_item = 'IND_MAX_BATCH_PARTY_ID'));
458
459 IF (SQL%ROWCOUNT = 0) THEN
460 UPDATE fii_change_log
461 SET item_value = l_max_batch_party_id,
462 last_update_date = sysdate,
463 last_update_login = g_login_id,
464 last_updated_by = g_user_id
465 WHERE log_item = 'IND_MAX_BATCH_PARTY_ID';
466 END IF;
467
468 -- Identify Last valid class code for each party and create a record in FII_PARTY_MKT_CLASS table
469
470
471 bis_collection_utilities.log('Truncating Staging table');
472
473 l_sql_string := 'TRUNCATE TABLE ' || l_fii_schema ||'.FII_PARTY_MKT_CLASS_STG';
474 EXECUTE IMMEDIATE l_sql_string;
475
476 bis_collection_utilities.log('Populating Staging table with incremental records from hz_code_assignments table');
477 g_phase := 'Populating Staging table with incremental records from hz_code_assignments table';
478
479 -- Bug 5093260. Performance enhancement
480 INSERT INTO fii_party_mkt_class_stg
481 (
482 owner_table_id,
483 class_category,
484 class_code,
485 start_date,
486 end_date,
487 primary_flag,
488 creation_date,
489 created_by,
490 last_update_date,
491 last_updated_by,
492 last_update_login
493 )
494 SELECT /*+ leading(v) use_nl(a) */
495 a.OWNER_TABLE_ID,
496 a.CLASS_CATEGORY,
497 a.CLASS_CODE,
498 a.START_DATE_ACTIVE,
499 a.END_DATE_ACTIVE,
500 a.PRIMARY_FLAG,
501 a.CREATION_DATE,
502 g_user_id,
503 a.LAST_UPDATE_DATE,
504 g_user_id,
505 g_login_id
506 FROM HZ_CODE_ASSIGNMENTS a,
507 (
508 SELECT /*+ no_merge parallel(h) */ DISTINCT OWNER_TABLE_ID
509 FROM HZ_CODE_ASSIGNMENTS h
510 WHERE ( (LAST_UPDATE_DATE BETWEEN SYSDATE-1 AND SYSDATE)
511 OR
512 (START_DATE_ACTIVE BETWEEN SYSDATE-1 AND SYSDATE)
513 OR
514 (END_DATE_ACTIVE BETWEEN SYSDATE-1 AND SYSDATE) )
515 AND CLASS_CATEGORY = g_class_type
516 AND OWNER_TABLE_NAME = 'HZ_PARTIES'
517 ) v
518 WHERE a.OWNER_TABLE_ID = v.OWNER_TABLE_ID
519 AND a.CLASS_CATEGORY = g_class_type;
520
521 bis_collection_utilities.log('Populating staging table with customer_ids that are created after last collection');
522 bis_collection_utilities.log(' and that are not assigned to any class code' );
523 g_phase := 'Populating staging table with customer_ids that are created after last collection
524 and that are not assigned to any class code';
525
526 -- Bug 5093260. Performance enhancement
527 -- This is required to get the date format for the next query
528 SELECT value INTO lDateFormat
529 FROM v$parameter
530 WHERE name = 'nls_date_format';
531
532
533 INSERT INTO fii_party_mkt_class_stg
534 (
535 owner_table_id,
536 class_category,
537 class_code,
538 start_date,
539 end_date,
540 primary_flag,
541 creation_date,
542 created_by,
543 last_update_date,
544 last_updated_by,
545 last_update_login
546 )
547 SELECT
548 party_id,
549 g_class_type class_category,
550 '-1' class_code,
551 to_date(g_collection_to_date, lDateFormat) - 1 start_date_active,
552 to_date(g_collection_to_date, lDateFormat) + 1 end_date_active,
553 'N',
554 g_run_date creation_date,
555 g_user_id created_by,
556 g_run_date last_update_date,
557 g_user_id last_updated_by,
558 g_login_id last_update_login
559 FROM (
560 SELECT /*+ parallel(a) */ DISTINCT PARTY_ID
561 FROM HZ_CUST_ACCOUNTS a
562 WHERE CREATION_DATE BETWEEN g_collection_from_date AND g_collection_to_date
563 AND PARTY_ID NOT IN (
564 SELECT /*+ parallel(s) */ OWNER_TABLE_ID
565 FROM FII_PARTY_MKT_CLASS_STG s
566 WHERE CLASS_CATEGORY = g_class_type
567 )
568 );
569
570 g_phase := 'gather_table_stats for FII_PARTY_MKT_CLASS_STG';
571 FND_STATS.gather_table_stats
572 (ownname => l_fii_schema,
573 tabname => 'FII_PARTY_MKT_CLASS_STG');
574
575 bis_collection_utilities.log('Merging records into FII_PARTY_MKT_CLASS table ');
576 g_phase := 'Merging records into FII_PARTY_MKT_CLASS table';
577
578 MERGE INTO fii_party_mkt_class cl
579 USING
580 (
581 SELECT
582 party_id,
583 class_category,
584 MAX(class_code) KEEP (DENSE_RANK LAST ORDER BY party_id, active_priority, last_update_date) class_code,
585 sysdate creation_date,
586 g_user_id created_by,
587 sysdate last_update_date,
588 g_user_id last_updated_by,
589 g_login_id last_update_login
590 FROM
591 (
592 SELECT
593 owner_table_id party_id,
594 class_category,
595 class_code,
596 creation_date,
597 last_update_date,
598 CASE WHEN primary_flag = 'Y'
599 THEN 2 ELSE 1 END active_priority
600 FROM FII_PARTY_MKT_CLASS_STG
601 WHERE g_collection_to_date BETWEEN start_date AND nvl(end_date, g_collection_to_date+1)
602 ORDER BY owner_table_id
603 )
604 GROUP BY party_id, class_category
605 ) cu
606 ON ( cl.party_id = cu.party_id AND
607 cl.class_category = cu.class_category )
608 WHEN MATCHED THEN UPDATE
609 SET
610 cl.class_code = cu.class_code
611 WHEN NOT MATCHED THEN
612 INSERT
613 (
614 party_id,
615 class_category,
616 class_code,
617 creation_date,
618 created_by,
619 last_update_date,
620 last_updated_by,
621 last_update_login
622 )
623 VALUES
624 (
625 cu.party_id,
626 cu.class_category,
627 cu.class_code,
628 cu.creation_date,
629 cu.created_by,
630 cu.last_update_date,
631 cu.last_updated_by,
632 cu.last_update_login
633 );
634
635 EXCEPTION
636 WHEN OTHERS THEN
637 l_error_message := sqlerrm;
638 bis_collection_utilities.log('Error while doing incremental population of partner classification table ');
639 bis_collection_utilities.log('Error Message : ' || l_error_message);
640 bis_collection_utilities.log('Phase : ' || g_phase);
641 g_errbuf := l_error_message;
642 g_retcode := 2;
643 END incremental_load;
644
645
646 ---------------------------------------------------
647 -- PUBLIC FUNCTION DEFAULT_LOAD_MODE
648 -- this function is used to return the default load
649 -- mode parameter of the concurrent program
650 ---------------------------------------------------
651 FUNCTION DEFAULT_LOAD_MODE
652 return varchar2
653 is
654 l_count number;
655 l_class_category varchar2(30);
656 l_period_from DATE;
657 l_period_to DATE;
658 l_count_party_marge number;
659
660 begin
661 ----------------------------------------------------------------------------
662 -- Run incremental/initial load based on the following:
663 -- 1. Last run was successful or not: Yes INCRE and No INIT
664 -- 2. Global parameter has been changed or not: Yes INIT and No INCRE
665 -- 3. table fii_party_mkt_class is empty: INIT
666 ----------------------------------------------------------------------------
667
668 -- If the table is empty then run Initial load
669 select count(*) into l_count
670 from fii_party_mkt_class;
671
672 IF (l_count = 0) THEN
673 return 'INIT';
674 ELSE
675 -- If the global parameter has changed then run initial load
676 select class_category into l_class_category
677 from fii_party_mkt_class
678 where rownum <2;
679
680 IF l_class_category <> nvl(bis_common_parameters.GET_BIS_CUST_CLASS_TYPE, -1) THEN
681 return 'INIT';
682 ELSE
683 -- If the last run was unsuccessfull run initial else incremental
684 BIS_COLLECTION_UTILITIES.get_last_refresh_dates(
685 p_object_name => 'FII_PARTY_MKT_CLASS',
686 p_start_date => l_period_from,
687 p_end_date => l_period_to,
688 p_period_from => g_last_collection_from_date,
689 p_period_to => g_last_collection_to_date);
690
691 IF (g_last_collection_from_date IS NULL) THEN
692 return 'INIT';
693 ELSE
694 --Check if log_item = 'IND_MAX_BATCH_PARTY_ID' exists for party merge functionality.
695 select count(*) into l_count_party_marge
696 from fii_change_log
697 where log_item = 'IND_MAX_BATCH_PARTY_ID';
698
699 IF (l_count_party_marge = 0) THEN
700 return 'INIT';
701 ELSE
702 return 'INCRE';
703 END IF;
704 END IF;
705
706 END IF;
707 END IF;
708
709 end DEFAULT_LOAD_MODE;
710
711 END fii_party_mkt_class_c;