[Home] [Help]
PACKAGE BODY: APPS.FII_AR_CUSTOMER_DIMENSION_PKG
Source
1 PACKAGE BODY FII_AR_CUSTOMER_DIMENSION_PKG AS
2 /* $Header: FIIARCUSTB.pls 120.14 2007/12/10 22:21:51 mmanasse ship $ */
3
4 g_debug_flag VARCHAR2(1) := NVL(FND_PROFILE.value('FII_DEBUG_MODE'), 'N');
5 g_state VARCHAR2(500);
6 g_schema_name VARCHAR2(120) := 'FII';
7
8 g_fii_user_id NUMBER(15);
9 g_fii_login_id NUMBER(15);
10
11 g_errbuf VARCHAR2(2000) := NULL;
12 g_retcode VARCHAR2(200) := NULL;
13 g_exception_msg VARCHAR2(4000) := NULL;
14
15 G_LOGIN_INFO_NOT_AVABLE EXCEPTION;
16
17 g_hierarchy_type VARCHAR2(30) := FND_PROFILE.VALUE('BIS_CUST_HIER_TYPE');
18 g_sysdate DATE := sysdate;
19 g_last_load_date DATE;
20
21 -- *******************************************************************
22 -- Initialize (get the master value set and the top node)
23 -- **************************************************************************
24
25 PROCEDURE Initialize IS
26 l_dir VARCHAR2(160);
27
28 BEGIN
29
30 g_state := 'Setting up log file location.';
31
32 l_dir := fnd_profile.value('BIS_DEBUG_LOG_DIRECTORY');
33 ------------------------------------------------------
34 -- Set default directory in case if the profile option
35 -- BIS_DEBUG_LOG_DIRECTORY is not set up
36 ------------------------------------------------------
37 if l_dir is NULL then
38 l_dir := FII_UTIL.get_utl_file_dir;
39 end if;
40
41 ----------------------------------------------------------------
42 -- FII_UTIL.initialize will get profile options FII_DEBUG_MODE
43 -- and BIS_DEBUG_LOG_DIRECTORY and set up the directory where
44 -- the log files and output files are written to
45 ----------------------------------------------------------------
46 FII_UTIL.initialize('FII_AR_CUSTOMER_DIMENSION_PKG.log',
47 'FII_AR_CUSTOMER_DIMENSION_PKG.out', l_dir,
48 'FII_AR_CUSTOMER_DIMENSION_PKG');
49
50
51 --Obtain FII schema name.
52 g_schema_name := FII_UTIL.get_schema_name ('FII');
53
54 --Obtain user ID, login ID and initialize package variables.
55 g_fii_user_id := FND_GLOBAL.USER_ID;
56 g_fii_login_id := FND_GLOBAL.LOGIN_ID;
57
58
59 IF (g_fii_user_id IS NULL OR g_fii_login_id IS NULL) THEN
60 RAISE G_LOGIN_INFO_NOT_AVABLE;
61 END IF;
62
63 if g_debug_flag = 'Y' then
64 FII_UTIL.put_line('User ID: ' || g_fii_user_id || ' Login ID: ' || g_fii_login_id);
65 end if;
66
67
68 EXCEPTION
69 WHEN G_LOGIN_INFO_NOT_AVABLE THEN
70 g_errbuf := 'Can not get User ID and Login ID, program exit';
71 RAISE;
72 WHEN OTHERS THEN
73 FII_UTIL.put_line('Unexpected error when calling Initialize.');
74 g_errbuf := 'Error Message: '|| substr(sqlerrm,1,180);
75 RAISE;
76
77 END INITIALIZE;
78
79
80
81 -- **************************************************************************
82 -- This is the main procedure of CUSTOMER dimension program (initial populate).
83 -- **************************************************************************
84
85 PROCEDURE INIT_LOAD (errbuf OUT NOCOPY VARCHAR2,
86 retcode OUT NOCOPY VARCHAR2) IS
87 l_max_cust_account_id NUMBER(15,0);
88 l_max_batch_party_id NUMBER(15,0);
89
90 BEGIN
91
92 g_state := 'Inside the INIT_LOAD procedure.';
93 if g_debug_flag = 'Y' then
94 FII_UTIL.put_line(g_state);
95 end if;
96
97 g_state := 'Calling BIS_COLLECTION_UTILITIES.setup';
98 IF(NOT BIS_COLLECTION_UTILITIES.setup('FII_AR_CUST_DIM_INIT')) THEN
99 raise_application_error(-20000, errbuf);
100 return;
101 END IF;
102
103 g_state := 'Calling the INITIALIZE procedure to initialize global variables.';
104 if g_debug_flag = 'Y' then
105 FII_UTIL.put_line(g_state);
106 end if;
107
108
109 INITIALIZE;
110
111
112 g_state := 'Truncating customer dimension tables.';
113 if g_debug_flag = 'Y' then
114 FII_UTIL.put_line(g_state);
115 end if;
116
117 --Truncate customer dimension tables.
118 FII_UTIL.truncate_table('FII_CUSTOMER_HIERARCHIES', 'FII', g_retcode);
119 FII_UTIL.truncate_table('FII_CUST_ACCOUNTS', 'FII', g_retcode);
120
121
122 g_state := 'Inserting dummy record.';
123 if g_debug_flag = 'Y' then
124 FII_UTIL.put_line(g_state);
125 end if;
126
127 --Insert dummy record required by the MVs.
128 INSERT INTO FII_Customer_Hierarchies (
129 Parent_Party_ID,
130 Next_Level_Party_ID,
131 Child_Party_ID,
132 Next_Level_Is_Leaf_Flag,
133 Is_Hierarchical_Flag,
134 Parent_To_Next_Level,
135 Next_To_Child_Level,
136 Creation_Date,
137 Created_By,
138 Last_Update_Date,
139 Last_Updated_By,
140 Last_Update_Login)
141 VALUES (
142 -999,
143 -2,
144 -2,
145 'Y',
146 DECODE(g_hierarchy_type, NULL, 'N', 'Y'),
147 1,
148 0,
149 sysdate,
150 g_fii_user_id,
151 sysdate,
152 g_fii_user_id,
153 g_fii_login_id);
154
155 INSERT INTO FII_Cust_Accounts (Parent_Party_ID, Cust_Account_ID, Account_Owner_Party_ID, Creation_Date, Created_By, Last_Update_Date, Last_Updated_By, Last_Update_Login)
156 VALUES (-2, -2, -2, sysdate, g_fii_user_id, sysdate, g_fii_user_id, g_fii_login_id);
157
158 --Store the current maximum batch party id to be used in incremental loads.
159 g_state := 'Storing the current maximum batch party id.';
160 if g_debug_flag = 'Y' then
161 FII_UTIL.put_line(g_state);
162 end if;
163
164 SELECT nvl(MAX(Batch_Party_ID), -1) INTO l_max_batch_party_id
165 FROM HZ_Merge_Party_History;
166
167 UPDATE FII_Change_Log
168 SET (Item_Value, Last_Update_Date, Last_Update_Login, Last_Updated_By)
169 = (SELECT l_max_batch_party_id, sysdate, g_fii_login_id, g_fii_user_id FROM DUAL)
170 WHERE log_item = 'MAX_BATCH_PARTY_ID';
171
172 IF SQL%ROWCOUNT = 0 THEN
173 INSERT INTO FII_Change_Log (Log_Item, Item_Value, Creation_Date, Created_By, Last_Update_Date, Last_Update_Login, Last_Updated_By)
174 VALUES ('MAX_BATCH_PARTY_ID', l_max_batch_party_id, sysdate, g_fii_user_id, sysdate, g_fii_login_id, g_fii_user_id);
175 END IF;
176
177 --If a hierarchy_type is chosen, populate hierarchical parties:
178 --1. Populate intermediate tables FII_AR_Cust_LNodes_GT (Leaf Nodes) and
179 -- FII_AR_Cust_Rlns_GT (Direct DBI Relationships).
180 --2. Populate FII_Customer_Hierarchies.
181
182
183 IF g_hierarchy_type IS NOT NULL THEN
184
185 g_state := 'Populating intermediate tables FII_AR_Cust_LNodes_GT and FII_AR_Cust_Rlns_GT.';
186 if g_debug_flag = 'Y' then
187 FII_UTIL.put_line(g_state);
188 FII_UTIL.start_timer;
189 end if;
190
191 /* With one scan of HZ_Hierarchy_Nodes, this sql populates 2 intermediate
192 tables to be used later:
193
194 FII_AR_CUST_LNODES_GT is populated with leaf node parties. The only
195 way to detect leaf nodes is using the Leaf_Child_Flag column of
196 HZ_Hierarchy_Nodes for self-records. This table will later be used
197 to populate Next_Level_Is_Leaf_Flag in FII_Customer_Hierarchies.
198
199 FII_AR_CUST_RLNS_GT is populated with all direct relationships in the
200 DBI hierarchy. The DBI hierarchy differs from the TCA hierarchy
201 because of the pseudo top node, -999. So additional records must be
202 inserted from -999 to any top node party. Party pairs in this table
203 will be used to populate Parent_Party_ID and Next_Level_Party_ID in
204 FII_Customer_Hierarchies, not including self-records. */
205
206
207 INSERT ALL
208 WHEN (Leaf_Child_Flag = 'Y')
209 THEN INTO FII_AR_Cust_LNodes_GT(
210 Leaf_Node_ID)
211 VALUES (Parent_ID)
212 WHEN (Top_Parent_Flag = 'Y' OR Level_Number = 1)
213 THEN INTO FII_AR_Cust_Rlns_GT(
214 Parent_ID,
215 Next_ID)
216 VALUES (CASE WHEN Level_Number = 0 THEN -999
217 ELSE Parent_ID END,
218 CASE WHEN Level_Number = 0 THEN Parent_ID
219 ELSE Child_ID END)
220 SELECT Parent_ID,
221 Child_ID,
222 Level_Number,
223 Top_Parent_Flag,
224 Leaf_Child_Flag
225 FROM HZ_HIERARCHY_NODES
226 WHERE Hierarchy_Type = g_hierarchy_type
227 AND ( Level_Number = 1 OR
228 (Level_Number = 0 AND (Top_Parent_Flag = 'Y' OR Leaf_Child_Flag = 'Y')) )
229 AND g_sysdate BETWEEN Effective_Start_Date AND Effective_End_Date;
230
231 if g_debug_flag = 'Y' then
232 FII_UTIL.put_line('Inserted ' || SQL%ROWCOUNT || ' records into FII_AR_Cust_LNodes_GT and FII_AR_Cust_Rlns_GT');
233 FII_UTIL.stop_timer;
234 FII_UTIL.print_timer('Duration');
235 end if;
236
237 g_state := 'Populating FII_Customer_Hierarchies with hierarchical parties.';
238 if g_debug_flag = 'Y' then
239 FII_UTIL.put_line(g_state);
240 FII_UTIL.start_timer;
241 end if;
242
243 INSERT INTO FII_Customer_Hierarchies(
244 Parent_Party_ID,
245 Next_Level_Party_ID,
246 Child_Party_ID,
247 Next_Level_Is_Leaf_Flag,
248 Is_Hierarchical_Flag,
249 Parent_To_Next_Level,
250 Next_To_Child_Level,
251 Creation_Date,
252 Created_By,
253 Last_Update_Date,
254 Last_Updated_By,
255 Last_Update_Login)
256 SELECT CASE WHEN Temp.ID = 1 THEN PTN.Parent_ID
257 ELSE PTN.Next_ID END Parent_Party_ID,
258 PTN.Next_ID Next_Level_Party_ID,
259 HN.Child_ID Child_Party_ID,
260 DECODE(Leaf.Leaf_Node_ID, NULL, 'N', 'Y') Next_Level_Is_Leaf_Flag,
261 'Y' Is_Hierarchical_Flag,
262 CASE WHEN Temp.ID = 1 THEN 1
263 ELSE 0 END Parent_To_Next_Level,
264 HN.Level_Number Next_To_Child_Level,
265 sysdate,
266 g_fii_user_id,
267 sysdate,
268 g_fii_user_id,
269 g_fii_login_id
270 FROM FII_AR_Cust_Rlns_GT PTN,
271 HZ_Hierarchy_Nodes HN,
272 (SELECT 1 ID FROM Dual UNION
273 SELECT 2 ID FROM Dual) Temp,
274 FII_AR_Cust_LNodes_GT Leaf
275 WHERE PTN.Next_ID = HN.Parent_ID
276 AND (Temp.ID = 1 OR HN.Parent_ID = HN.Child_ID)
277 AND HN.Hierarchy_Type = g_hierarchy_type
278 AND g_sysdate BETWEEN HN.Effective_Start_Date AND HN.Effective_End_Date
279 AND PTN.Next_ID = Leaf.Leaf_Node_ID (+);
280
281 if g_debug_flag = 'Y' then
282 FII_UTIL.put_line('Inserted ' || SQL%ROWCOUNT || ' records into FII_Customer_Hierarchies');
283 FII_UTIL.stop_timer;
284 FII_UTIL.print_timer('Duration');
285 end if;
286
287 END IF; --IF g_hierarchy_type IS NOT NULL
288
289 --Store the current maximum customer account id to be used in incremental loads.
293 end if;
290 g_state := 'Storing the current maximum customer account id.';
291 if g_debug_flag = 'Y' then
292 FII_UTIL.put_line(g_state);
294
295 SELECT nvl(MAX(Cust_Account_ID),-1) INTO l_max_cust_account_id
296 FROM HZ_Cust_Accounts;
297
298 UPDATE FII_Change_Log
299 SET (Item_Value, Last_Update_Date, Last_Update_Login, Last_Updated_By)
300 = (SELECT l_max_cust_account_id, sysdate, g_fii_login_id, g_fii_user_id FROM DUAL)
301 WHERE log_item = 'MAX_CUST_ACCOUNT_ID';
302
303 IF SQL%ROWCOUNT = 0 THEN
304 INSERT INTO FII_Change_Log (Log_Item, Item_Value, Creation_Date, Created_By, Last_Update_Date, Last_Update_Login, Last_Updated_By)
305 VALUES ('MAX_CUST_ACCOUNT_ID', l_max_cust_account_id, sysdate, g_fii_user_id, sysdate, g_fii_login_id, g_fii_user_id);
306 END IF;
307
308 --From HZ_Cust_Accounts, outer join to FII_Customer_Hierarchies to:
309 --1. Populate FII_Customer_Hierarchies with non-hierarchical customers. Use
310 -- the first account from each unique party in HZ_Cust_Accounts.
311 --2. Populate FII_Cust_Accounts with hierarchical and non-hierarchical customers.
312
313 g_state := 'Populating FII_Customer_Hierarchies with non-hierarchical customers and FII_Cust_Accounts with all customers.';
314 if g_debug_flag = 'Y' then
315 FII_UTIL.put_line(g_state);
316 FII_UTIL.start_timer;
317 end if;
318
319 INSERT ALL
320 WHEN (Parent_Party_ID IS NOT NULL AND Parent_Party_ID <> -999)
321 THEN INTO FII_Cust_Accounts(
322 Parent_Party_ID,
323 Cust_Account_ID,
324 Account_Owner_Party_ID,
325 Account_Number,
326 Creation_Date,
327 Created_By,
328 Last_Update_Date,
329 Last_Updated_By,
330 Last_Update_Login)
331 VALUES (Parent_Party_ID,
332 Cust_Account_ID,
333 Party_ID,
334 Account_Number,
335 sysdate,
336 g_fii_user_id,
337 sysdate,
338 g_fii_user_id,
339 g_fii_login_id)
340 WHEN (Parent_Party_ID IS NULL)
341 THEN INTO FII_Cust_Accounts(
342 Parent_Party_ID,
343 Cust_Account_ID,
344 Account_Owner_Party_ID,
345 Account_Number,
346 Creation_Date,
347 Created_By,
348 Last_Update_Date,
349 Last_Updated_By,
350 Last_Update_Login)
351 VALUES (Party_ID,
352 Cust_Account_ID,
353 Party_ID,
354 Account_Number,
355 sysdate,
356 g_fii_user_id,
357 sysdate,
358 g_fii_user_id,
359 g_fii_login_id)
360 WHEN (Parent_Party_ID IS NULL AND SRLID = 1)
361 THEN INTO FII_Customer_Hierarchies(
362 Parent_Party_ID,
363 Next_Level_Party_ID,
364 Child_Party_ID,
365 Next_Level_Is_Leaf_Flag,
366 Is_Hierarchical_Flag,
367 Parent_To_Next_Level,
368 Next_To_Child_Level,
369 Creation_Date,
370 Created_By,
371 Last_Update_Date,
372 Last_Updated_By,
373 Last_Update_Login)
374 VALUES (-999,
375 Party_ID,
376 Party_ID,
377 'Y',
378 DECODE(g_hierarchy_type, NULL, 'N', 'Y'),
379 1,
380 0,
381 sysdate,
382 g_fii_user_id,
383 sysdate,
384 g_fii_user_id,
385 g_fii_login_id)
386 WHEN (Parent_Party_ID IS NULL AND SRLID = 1)
387 THEN INTO FII_Customer_Hierarchies(
388 Parent_Party_ID,
389 Next_Level_Party_ID,
390 Child_Party_ID,
391 Next_Level_Is_Leaf_Flag,
392 Is_Hierarchical_Flag,
393 Parent_To_Next_Level,
394 Next_To_Child_Level,
395 Creation_Date,
396 Created_By,
397 Last_Update_Date,
398 Last_Updated_By,
399 Last_Update_Login)
400 VALUES (Party_ID,
401 Party_ID,
402 Party_ID,
403 'Y',
404 DECODE(g_hierarchy_type, NULL, 'N', 'Y'),
405 0,
406 0,
407 sysdate,
408 g_fii_user_id,
409 sysdate,
410 g_fii_user_id,
411 g_fii_login_id)
412 SELECT /*+ parallel(CA) */ Hier.Parent_Party_ID,
413 CA.Party_ID,
414 CA.Cust_Account_ID,
415 CA.Account_Number,
416 ROW_NUMBER () OVER (
417 PARTITION BY CA.Party_ID
418 ORDER BY CA.Party_ID NULLS LAST) SRLID
419 FROM HZ_Cust_Accounts CA,
420 FII_Customer_Hierarchies Hier
421 WHERE CA.Party_ID = Hier.Child_Party_ID (+)
422 AND CA.Cust_Account_ID <= l_max_cust_account_id;
423
424 if g_debug_flag = 'Y' then
425 FII_UTIL.put_line('Inserted ' || SQL%ROWCOUNT || ' records into FII_Customer_Hierarchies');
426 FII_UTIL.stop_timer;
430
427 FII_UTIL.print_timer('Duration');
428 end if;
429
431 g_state := 'Calling BIS_COLLECTION_UTILITIES.wrapup';
432 BIS_COLLECTION_UTILITIES.wrapup(
433 p_status => TRUE,
434 p_period_from => BIS_COMMON_PARAMETERS.Get_Global_Start_Date,
435 p_period_to => g_sysdate);
436
437
438 EXCEPTION
439 WHEN OTHERS THEN
440 g_retcode := -1;
441 retcode := g_retcode;
442
443 g_exception_msg := g_retcode || ':' || sqlerrm;
444 FII_UTIL.put_line('Error occured while ' || g_state);
445 FII_UTIL.put_line(g_exception_msg);
446
447
448 END INIT_LOAD;
449
450
451 -- *****************************************************************
452 -- This is the main procedure of CUSTOMER dimension program (incremental update).
453 -- *****************************************************************
454
455 PROCEDURE INCRE_UPDATE (errbuf OUT NOCOPY VARCHAR2,
456 retcode OUT NOCOPY VARCHAR2) IS
457 l_start_date DATE;
458 l_end_date DATE;
459 l_period_from DATE;
460 l_period_to DATE;
461 l_start_date_temp DATE;
462
463 CURSOR Party_Delta IS
464 SELECT Party_ID
465 FROM FII_AR_Parties_Level1_GT GROUP BY Party_ID;
466
467 l_max_batch_party_id NUMBER(15,0);
468 l_prev_max_batch_party_id NUMBER(15,0);
469 l_max_cust_account_id NUMBER(15,0);
470 l_prev_max_cust_account_id NUMBER(15,0);
471
472 TYPE Customer_Hierarchies_Type IS TABLE OF FII_CUSTOMER_HIERARCHIES%ROWTYPE
473 INDEX BY BINARY_INTEGER;
474
475 TYPE Cust_Hier_Tmp_Type IS TABLE OF FII_AR_CUST_HIER_TMP_GT%ROWTYPE
476 INDEX BY BINARY_INTEGER;
477
478 TYPE Cust_Account_Denorm_Type IS TABLE OF FII_CUST_ACCOUNTS%ROWTYPE
479 INDEX BY BINARY_INTEGER;
480
481 TYPE CAcct_Denorm_Tmp_Type IS TABLE OF FII_AR_CACCTS_TMP_GT%ROWTYPE
482 INDEX BY BINARY_INTEGER;
483
484 TYPE Cust_Hier_UI_Type IS TABLE OF FII_AR_CUST_HIER_UI_GT%ROWTYPE
485 INDEX BY BINARY_INTEGER;
486
487 TYPE Cust_Hier_D_Type IS TABLE OF FII_AR_CUST_HIER_D_GT%ROWTYPE
488 INDEX BY BINARY_INTEGER;
489
490 TYPE CAcct_Denorm_D_Type IS TABLE OF FII_AR_CACCTS_D_GT%ROWTYPE
491 INDEX BY BINARY_INTEGER;
492
493 FII_Cust_Hier_Old_MS Customer_Hierarchies_Type;
494 FII_Cust_Hier_New_MS Cust_Hier_Tmp_Type;
495 FII_Cust_Hier_UI_MS Cust_Hier_UI_Type;
496 FII_Cust_Hier_D_MS Cust_Hier_D_Type;
497 FII_CAcct_Denorm_Old_MS Cust_Account_Denorm_Type;
498 FII_CAcct_Denorm_New_MS CAcct_Denorm_Tmp_Type;
499 FII_CAcct_Denorm_I_MS Cust_Account_Denorm_Type;
500 FII_CAcct_Denorm_D_MS CAcct_Denorm_D_Type;
501
502 l_cust_hier_old_marker BINARY_INTEGER;
503 l_cust_hier_new_marker BINARY_INTEGER;
504 l_cacct_denorm_old_marker BINARY_INTEGER;
505 l_cacct_denorm_new_marker BINARY_INTEGER;
506
507 l_old_ch FII_CUSTOMER_HIERARCHIES%ROWTYPE;
508 l_new_ch FII_AR_CUST_HIER_TMP_GT%ROWTYPE;
509 l_ui_ch FII_AR_CUST_HIER_UI_GT%ROWTYPE;
510 l_d_ch FII_AR_CUST_HIER_D_GT%ROWTYPE;
511 l_old_cad FII_CUST_ACCOUNTS%ROWTYPE;
512 l_new_cad FII_AR_CACCTS_TMP_GT%ROWTYPE;
513 l_i_cad FII_CUST_ACCOUNTS%ROWTYPE;
514 l_d_cad FII_AR_CACCTS_D_GT%ROWTYPE;
515
516 BEGIN
517
518 g_state := 'Inside the INCRE_UPDATE procedure.';
519 if g_debug_flag = 'Y' then
520 FII_UTIL.put_line(g_state);
521 end if;
522
523 g_state := 'Calling BIS API to get last refresh dates.';
524 if g_debug_flag = 'Y' then
525 FII_UTIL.put_line(g_state);
526 end if;
527
528 BIS_COLLECTION_UTILITIES.get_last_refresh_dates('FII_AR_CUST_DIM_INIT',
529 l_start_date, l_end_date,
530 l_period_from, l_period_to);
531
532
533 BIS_COLLECTION_UTILITIES.get_last_refresh_dates('FII_AR_CUST_DIM_INC',
534 l_start_date_temp, l_end_date,
535 l_period_from, l_period_to);
536
537
538 g_last_load_date := GREATEST(NVL(l_start_date, BIS_COMMON_PARAMETERS.Get_Global_Start_Date),
539 NVL(l_start_date_temp, BIS_COMMON_PARAMETERS.Get_Global_Start_Date));
540
541 if g_debug_flag = 'Y' then
542 FII_UTIL.put_line('Incremental load will collect data from ' || to_char(g_last_load_date, 'YYYY/MM/DD HH24:MI:SS') || ' to ' || to_char(g_sysdate, 'YYYY/MM/DD HH24:MI:SS') || '.');
543 end if;
544
545 g_state := 'Calling BIS_COLLECTION_UTILITIES.setup';
546 IF(NOT BIS_COLLECTION_UTILITIES.setup('FII_AR_CUST_DIM_INC')) THEN
547 raise_application_error(-20000, errbuf);
548 return;
549 END IF;
550
551 g_state := 'Calling the INITIALIZE procedure to initialize global variables.';
552 if g_debug_flag = 'Y' then
553 FII_UTIL.put_line(g_state);
554 end if;
555 INITIALIZE;
556
560 end if;
557 g_state := 'Storing previous maximum batch party id.';
558 if g_debug_flag = 'Y' then
559 FII_UTIL.put_line(g_state);
561
562 SELECT item_value
563 INTO l_prev_max_batch_party_id
564 FROM fii_change_log
565 WHERE log_item = 'MAX_BATCH_PARTY_ID';
566
567 g_state := 'Storing the current maximum batch party id.';
568 if g_debug_flag = 'Y' then
569 FII_UTIL.put_line(g_state);
570 end if;
571
572 SELECT nvl(MAX(Batch_Party_ID), -1) INTO l_max_batch_party_id
573 FROM HZ_Merge_Party_History
574 WHERE Batch_Party_ID >= l_prev_max_batch_party_id;
575
576 IF l_max_batch_party_id > l_prev_max_batch_party_id THEN
577
578 UPDATE FII_Change_Log
579 SET (Item_Value, Last_Update_Date, Last_Update_Login, Last_Updated_By)
580 = (SELECT l_max_batch_party_id, sysdate, g_fii_login_id, g_fii_user_id FROM DUAL)
581 WHERE log_item = 'MAX_BATCH_PARTY_ID';
582
583 g_state := 'Populate FII_AR_Parties_Delta_GT with parties that have been merged.';
584 if g_debug_flag = 'Y' then
585 FII_UTIL.put_line(g_state);
586 FII_UTIL.start_timer;
587 end if;
588
589 INSERT ALL
590 WHEN (1=1)
591 THEN INTO FII_AR_Parties_Delta_GT(Party_ID, Type_ID)
592 VALUES (From_Entity_ID, 5)
593 WHEN (1=1)
594 THEN INTO FII_AR_Parties_Delta_GT(Party_ID, Type_ID)
595 VALUES (To_Entity_ID, 6)
596 SELECT From_Entity_ID, To_Entity_ID
597 FROM HZ_Merge_Party_History M,
598 HZ_Merge_Dictionary D
599 WHERE M.merge_dict_id = D.merge_dict_id
600 AND M.batch_party_id > l_prev_max_batch_party_id
601 AND M.batch_party_id <= l_max_batch_party_id
602 AND D.entity_name = 'HZ_PARTIES';
603
604 if g_debug_flag = 'Y' then
605 FII_UTIL.put_line('Inserted ' || SQL%ROWCOUNT || ' records into FII_AR_Parties_Delta_GT');
606 FII_UTIL.stop_timer;
607 FII_UTIL.print_timer('Duration');
608 end if;
609
610 END IF;
611
612 IF g_hierarchy_type IS NOT NULL THEN
613
614 g_state := 'Populate FII_AR_Parties_Delta_GT with potentially deleted parties and FII_AR_Parties_Level1_GT with potentially new or updated parent parties.';
615 if g_debug_flag = 'Y' then
616 FII_UTIL.put_line(g_state);
617 FII_UTIL.start_timer;
618 end if;
619
620 INSERT ALL
621 WHEN (Level_Number = 0)
622 THEN INTO FII_AR_Parties_Delta_GT (
623 Party_ID,
624 Type_ID)
625 VALUES (Child_ID,
626 DECODE(Top_Parent_Flag, 'Y',
627 CASE WHEN g_sysdate BETWEEN Effective_Start_Date AND Effective_End_Date
628 THEN 4 ELSE 3 END,
629 3))
630 WHEN (Effective_End_Date BETWEEN g_last_load_date AND g_sysdate
631 AND Level_Number = 1)
632 THEN INTO FII_AR_Parties_Delta_GT (
633 Party_ID,
634 Type_ID,
635 Level_Number)
636 VALUES (Child_ID, 2, 0)
637 WHEN (Effective_End_Date NOT BETWEEN g_last_load_date AND g_sysdate
638 AND Level_Number = 1)
639 THEN INTO FII_AR_Parties_Level1_GT (
640 Party_ID)
641 VALUES (Child_ID)
642 SELECT Child_ID,
643 Level_Number,
644 Top_Parent_Flag,
645 Effective_End_Date,
646 Effective_Start_Date,
647 Last_Update_Date
648 FROM HZ_Hierarchy_Nodes
649 WHERE Hierarchy_Type = g_hierarchy_type
650 AND (Level_Number = 0 OR Level_Number = 1)
651 AND (Effective_End_Date BETWEEN g_last_load_date AND g_sysdate
652 OR Effective_Start_Date BETWEEN g_last_load_date AND g_sysdate
653 OR Last_Update_Date BETWEEN g_last_load_date AND g_sysdate);
654
655 if g_debug_flag = 'Y' then
656 FII_UTIL.put_line('Inserted ' || SQL%ROWCOUNT || ' records into FII_AR_Parties_Delta_GT and FII_AR_Parties_Level1_GT');
657 FII_UTIL.stop_timer;
658 FII_UTIL.print_timer('Duration');
659 end if;
660
661 g_state := 'Populate FII_AR_Parties_Level2_GT while looping through FII_AR_Parties_Level1_GT.';
662 if g_debug_flag = 'Y' then
663 FII_UTIL.put_line(g_state);
664 FII_UTIL.start_timer;
665 end if;
666
667 FOR Party_Record IN Party_Delta
668 LOOP
669
670 INSERT ALL
671 WHEN (SRLID=1)
672 THEN INTO FII_AR_Parties_Level2_GT(
673 Party_ID)
674 VALUES(Child_ID)
675 WHEN (SRLID=1)
676 THEN INTO FII_AR_Top_To_Source_GT(
677 Top_Node_ID,
678 Source_Node_ID,
679 Level_Number)
680 VALUES(Parent_ID,
681 Party_Record.Party_ID,
682 Level_Number)
683 SELECT Parent_ID,
684 Child_ID,
688 WHERE Level_Number = 1
685 Level Level_Number,
686 ROW_NUMBER () OVER (ORDER BY Level DESC) SRLID
687 FROM (SELECT * FROM HZ_Hierarchy_Nodes
689 AND Hierarchy_Type = g_hierarchy_type
690 AND g_sysdate BETWEEN Effective_Start_Date AND Effective_End_Date)
691 START WITH Child_ID = Party_Record.Party_ID
692 CONNECT BY PRIOR Parent_ID = Child_ID;
693
694 IF SQL%ROWCOUNT = 0 THEN
695 INSERT INTO FII_AR_Parties_Delta_GT(
696 Party_ID,
697 Type_ID,
698 Level_Number)
699 VALUES(Party_Record.Party_ID,
700 2,
701 0);
702 END IF;
703
704 END LOOP;
705
706 if g_debug_flag = 'Y' then
707 FII_UTIL.put_line('Completed population of FII_AR_Parties_Level2_GT.');
708 FII_UTIL.stop_timer;
709 FII_UTIL.print_timer('Duration');
710 end if;
711
712 g_state := 'Populate FII_AR_Parties_Delta_GT with descendants of parties in FII_AR_Parties_Level2_GT.';
713 if g_debug_flag = 'Y' then
714 FII_UTIL.put_line(g_state);
715 FII_UTIL.start_timer;
716 end if;
717
718 INSERT INTO FII_AR_Parties_Delta_GT(Party_ID, Type_ID)
719 SELECT HN.Child_ID, 1
720 FROM FII_AR_Parties_Level2_GT Log,
721 HZ_Hierarchy_Nodes HN
722 WHERE Log.Party_ID = HN.Parent_ID
723 AND HN.Hierarchy_Type = g_hierarchy_type
724 AND g_sysdate BETWEEN HN.Effective_Start_Date AND HN.Effective_End_Date;
725
726 if g_debug_flag = 'Y' then
727 FII_UTIL.put_line('Inserted ' || SQL%ROWCOUNT || ' records into FII_AR_Parties_Delta_GT.');
728 FII_UTIL.stop_timer;
729 FII_UTIL.print_timer('Duration');
730 end if;
731
732 g_state := 'Populate FII_AR_Top_To_Source_GT with descendants of parties already in FII_AR_Top_To_Source_GT.';
733 if g_debug_flag = 'Y' then
734 FII_UTIL.put_line(g_state);
735 FII_UTIL.start_timer;
736 end if;
737
738 INSERT INTO FII_AR_Top_To_Source_GT(
739 Top_Node_ID,
740 Source_Node_ID,
741 Level_Number)
742 SELECT GT.Top_Node_ID,
743 HN.Child_ID,
744 GT.Level_Number + HN.Level_Number Level_Number
745 FROM FII_AR_Top_To_Source_GT GT,
746 HZ_Hierarchy_Nodes HN
747 WHERE GT.Source_Node_ID = HN.Parent_ID
748 AND HN.Hierarchy_Type = g_hierarchy_type
749 AND g_sysdate BETWEEN HN.Effective_Start_Date AND HN.Effective_End_Date
750 AND HN.Level_Number > 0;
751
752 if g_debug_flag = 'Y' then
753 FII_UTIL.put_line('Inserted ' || SQL%ROWCOUNT || ' records into FII_AR_Top_To_Source_GT.');
754 FII_UTIL.stop_timer;
755 FII_UTIL.print_timer('Duration');
756 end if;
757
758 g_state := 'Populate FII_AR_Parties_Delta_GT with descendants of deleted relationships.';
759 if g_debug_flag = 'Y' then
760 FII_UTIL.put_line(g_state);
761 FII_UTIL.start_timer;
762 end if;
763
764 INSERT INTO FII_AR_Parties_Delta_GT(
765 Party_ID,
766 Type_ID,
767 Level_Number)
768 SELECT HN.Child_ID,
769 2,
770 HN.Level_Number
771 FROM FII_AR_Parties_Delta_GT GT,
772 HZ_Hierarchy_Nodes HN
773 WHERE GT.Party_ID = HN.Parent_ID
774 AND GT.Type_ID = 2
775 AND HN.Hierarchy_Type = g_hierarchy_type
776 AND g_sysdate BETWEEN HN.Effective_Start_Date AND HN.Effective_End_Date
777 AND HN.Level_Number > 0;
778
779 if g_debug_flag = 'Y' then
780 FII_UTIL.put_line('Inserted ' || SQL%ROWCOUNT || ' records into FII_AR_Parties_Delta_GT.');
781 FII_UTIL.stop_timer;
782 FII_UTIL.print_timer('Duration');
783 end if;
784
785
786 g_state := 'Populate FII_AR_Cust_LNodes_GT and FII_AR_Cust_Rlns_GT.';
787 if g_debug_flag = 'Y' then
788 FII_UTIL.put_line(g_state);
789 FII_UTIL.start_timer;
790 end if;
791
792 INSERT ALL
793 WHEN (Leaf_Child_Flag = 'Y')
794 THEN INTO FII_AR_Cust_LNodes_GT(
795 Leaf_Node_ID)
796 VALUES (Parent_ID)
797 WHEN (Top_Parent_Flag = 'Y' OR Level_Number = 1)
798 THEN INTO FII_AR_Cust_Rlns_GT(
799 Parent_ID,
800 Next_ID)
801 VALUES (CASE WHEN Level_Number = 0 THEN -999
802 ELSE Parent_ID END,
803 CASE WHEN Level_Number = 0 THEN Parent_ID
804 ELSE Child_ID END)
805 SELECT Parent_ID,
806 Child_ID,
807 Level_Number,
808 Top_Parent_Flag,
809 Leaf_Child_Flag
810 FROM HZ_HIERARCHY_NODES HN
811 WHERE EXISTS (SELECT 1 FROM FII_AR_Parties_Delta_GT Log
812 WHERE Log.Party_ID = HN.Child_ID)
813 AND Hierarchy_Type = g_hierarchy_type
814 AND ( Level_Number = 1 OR
815 (Level_Number = 0 AND (Top_Parent_Flag = 'Y' OR Leaf_Child_Flag = 'Y')) )
816 AND g_sysdate BETWEEN Effective_Start_Date AND Effective_End_Date;
817
818 if g_debug_flag = 'Y' then
822 end if;
819 FII_UTIL.put_line('Inserted ' || SQL%ROWCOUNT || ' records into FII_AR_Cust_LNodes_GT and FII_AR_Cust_Rlns_GT.');
820 FII_UTIL.stop_timer;
821 FII_UTIL.print_timer('Duration');
823
824 g_state := 'Populate FII_AR_Cust_Hier_Tmp_GT.';
825 if g_debug_flag = 'Y' then
826 FII_UTIL.put_line(g_state);
827 FII_UTIL.start_timer;
828 end if;
829
830 INSERT INTO FII_AR_Cust_Hier_Tmp_GT(
831 Parent_Party_ID,
832 Next_Level_Party_ID,
833 Child_Party_ID,
834 Next_Level_Is_Leaf_Flag,
835 Is_Hierarchical_Flag,
836 Parent_To_Next_Level,
837 Next_To_Child_Level)
838 SELECT CASE WHEN Temp.ID = 1 THEN PTN.Parent_ID
839 ELSE PTN.Next_ID END Parent_Party_ID,
840 PTN.Next_ID Next_Level_Party_ID,
841 HN.Child_ID Child_Party_ID,
842 DECODE(Leaf.Leaf_Node_ID, NULL, 'N', 'Y') Next_Level_Is_Leaf_Flag,
843 DECODE(g_hierarchy_type, NULL, 'N', 'Y') Is_Hierarchical_Flag,
844 CASE WHEN Temp.ID = 1 THEN 1
845 ELSE 0 END Parent_To_Next_Level,
846 HN.Level_Number Next_To_Child_Level
847 FROM FII_AR_Cust_Rlns_GT PTN,
848 HZ_Hierarchy_Nodes HN,
849 (SELECT 1 ID FROM Dual UNION
850 SELECT 2 ID FROM Dual) Temp,
851 FII_AR_Cust_LNodes_GT Leaf
852 WHERE PTN.Next_ID = HN.Parent_ID
853 AND (Temp.ID = 1 OR HN.Parent_ID = HN.Child_ID)
854 AND HN.Hierarchy_Type = g_hierarchy_type
855 AND g_sysdate BETWEEN HN.Effective_Start_Date AND HN.Effective_End_Date
856 AND PTN.Next_ID = Leaf.Leaf_Node_ID (+);
857
858 if g_debug_flag = 'Y' then
859 FII_UTIL.put_line('Inserted ' || SQL%ROWCOUNT || ' records into FII_AR_Cust_Hier_Tmp_GT.');
860 FII_UTIL.stop_timer;
861 FII_UTIL.print_timer('Duration');
862 end if;
863
864 END IF; --IF g_hierarchy_type IS NOT NULL
865
866 g_state := 'Storing previous maximum customer account id.';
867 if g_debug_flag = 'Y' then
868 FII_UTIL.put_line(g_state);
869 end if;
870
871 SELECT item_value
872 INTO l_prev_max_cust_account_id
873 FROM fii_change_log
874 WHERE log_item = 'MAX_CUST_ACCOUNT_ID';
875
876 g_state := 'Storing the current maximum customer account id.';
877 if g_debug_flag = 'Y' then
878 FII_UTIL.put_line(g_state);
879 end if;
880
881 SELECT nvl(MAX(Cust_Account_ID),-1) INTO l_max_cust_account_id
882 FROM HZ_Cust_Accounts
883 WHERE Cust_Account_ID >= l_prev_max_cust_account_id;
884
885 UPDATE FII_Change_Log
886 SET (Item_Value, Last_Update_Date, Last_Update_Login, Last_Updated_By)
887 = (SELECT NVL(l_max_cust_account_id, l_prev_max_cust_account_id), sysdate, g_fii_login_id, g_fii_user_id FROM DUAL)
888 WHERE log_item = 'MAX_CUST_ACCOUNT_ID';
889
890 g_state := 'Populate FII_AR_CAccts_Delta_GT with customer accounts that are new or in an updated hierarchy.';
891 if g_debug_flag = 'Y' then
892 FII_UTIL.put_line(g_state);
893 FII_UTIL.start_timer;
894 end if;
895
896 INSERT INTO FII_AR_Caccts_Delta_GT(Cust_Account_ID, Party_ID, Account_Number)
897 SELECT Cust_Account_ID, Party_ID, Account_Number
898 FROM HZ_Cust_Accounts CA
899 WHERE EXISTS (SELECT 1 FROM FII_AR_Parties_Delta_GT Log
900 WHERE Log.Party_ID = CA.Party_ID)
901 OR (Cust_Account_ID > l_prev_max_cust_account_id
902 AND Cust_Account_ID <= l_max_cust_account_id);
903
904 if g_debug_flag = 'Y' then
905 FII_UTIL.put_line('Inserted ' || SQL%ROWCOUNT || ' records into FII_AR_CAccts_Delta_GT.');
906 FII_UTIL.stop_timer;
907 FII_UTIL.print_timer('Duration');
908 end if;
909
910 g_state := 'Populating FII_AR_Cust_Hier_Tmp_GT with new non-hierarchical customers and FII_AR_CAccts_Tmp_GT with all new customers.';
911 if g_debug_flag = 'Y' then
912 FII_UTIL.put_line(g_state);
913 FII_UTIL.start_timer;
914 end if;
915
916 INSERT ALL
917 WHEN (Parent_Party_ID IS NOT NULL AND Parent_Party_ID <> -999)
918 THEN INTO FII_AR_Caccts_Tmp_GT(
919 Parent_Party_ID,
920 Cust_Account_ID,
921 Account_Party_ID,
922 Account_Number)
923 VALUES (Parent_Party_ID,
924 Cust_Account_ID,
925 Party_ID,
926 Account_Number)
927 WHEN (Parent_Party_ID IS NULL)
928 THEN INTO FII_AR_Caccts_Tmp_GT(
929 Parent_Party_ID,
930 Cust_Account_ID,
931 Account_Party_ID,
932 Account_Number)
933 VALUES (Party_ID,
934 Cust_Account_ID,
935 Party_ID,
936 Account_Number)
937 WHEN (Parent_Party_ID IS NULL AND SRLID = 1)
938 THEN INTO FII_AR_Cust_Hier_Tmp_GT(
939 Parent_Party_ID,
940 Next_Level_Party_ID,
941 Child_Party_ID,
942 Next_Level_Is_Leaf_Flag,
943 Is_Hierarchical_Flag,
944 Parent_To_Next_Level,
945 Next_To_Child_Level)
946 VALUES (-999,
947 Party_ID,
951 1,
948 Party_ID,
949 'Y',
950 DECODE(g_hierarchy_type, NULL, 'N', 'Y'),
952 0)
953 WHEN (Parent_Party_ID IS NULL AND SRLID = 1)
954 THEN INTO FII_AR_Cust_Hier_Tmp_GT(
955 Parent_Party_ID,
956 Next_Level_Party_ID,
957 Child_Party_ID,
958 Next_Level_Is_Leaf_Flag,
959 Is_Hierarchical_Flag,
960 Parent_To_Next_Level,
961 Next_To_Child_Level)
962 VALUES (Party_ID,
963 Party_ID,
964 Party_ID,
965 'Y',
966 DECODE(g_hierarchy_type, NULL, 'N', 'Y'),
967 0,
968 0)
969 SELECT Hier.Parent_ID Parent_Party_ID,
970 CA.Party_ID Party_ID,
971 CA.Cust_Account_ID,
972 CA.Account_Number,
973 ROW_NUMBER () OVER (
974 PARTITION BY CA.Party_ID
975 ORDER BY CA.Party_ID NULLS LAST) SRLID
976 FROM FII_AR_Caccts_Delta_GT CA,
977 (SELECT Parent_ID, Child_ID
978 FROM HZ_Hierarchy_Nodes
979 WHERE Hierarchy_Type = g_hierarchy_type
980 AND g_sysdate BETWEEN Effective_Start_Date AND Effective_End_Date) Hier
981 WHERE CA.Party_ID = Hier.Child_ID (+);
982
983 if g_debug_flag = 'Y' then
984 FII_UTIL.put_line('Inserted ' || SQL%ROWCOUNT || ' records into FII_AR_Cust_Hier_Tmp_GT and FII_AR_CAccts_Tmp_GT.');
985 FII_UTIL.stop_timer;
986 FII_UTIL.print_timer('Duration');
987 end if;
988
989 g_state := 'Merging records in FII_Customer_Hierarchies using FII_AR_Top_To_Source_GT.';
990 if g_debug_flag = 'Y' then
991 FII_UTIL.put_line(g_state);
992 FII_UTIL.start_timer;
993 end if;
994
995 MERGE INTO FII_Customer_Hierarchies CH
996 USING (SELECT DISTINCT Top_Node_ID, Source_Node_ID, Level_Number
997 FROM FII_AR_Top_To_Source_GT) GT
998 ON (CH.Parent_Party_ID = -999 AND
999 CH.Child_Party_ID = GT.Source_Node_ID)
1000 WHEN MATCHED THEN
1001 UPDATE SET CH.Next_Level_Party_ID = GT.Top_Node_ID,
1002 CH.Next_Level_Is_Leaf_Flag = 'N',
1003 CH.Next_To_Child_Level = GT.Level_Number,
1004 CH.Last_Update_Date = sysdate,
1005 CH.Last_Updated_By = g_fii_user_id,
1006 CH.Last_Update_Login = g_fii_login_id
1007 WHEN NOT MATCHED THEN
1008 INSERT (CH.Parent_Party_ID,
1009 CH.Next_Level_Party_ID,
1010 CH.Child_Party_ID,
1011 CH.Next_Level_Is_Leaf_Flag,
1012 CH.Is_Hierarchical_Flag,
1013 CH.Parent_To_Next_Level,
1014 CH.Next_To_Child_Level,
1015 CH.Creation_Date,
1016 CH.Created_By,
1017 CH.Last_Update_Date,
1018 CH.Last_Updated_By,
1019 CH.Last_Update_Login)
1020 VALUES (-999,
1021 GT.Top_Node_ID,
1022 GT.Source_Node_ID,
1023 'N',
1024 DECODE(g_hierarchy_type, NULL, 'N', 'Y'),
1025 1,
1026 GT.Level_Number,
1027 sysdate,
1028 g_fii_user_id,
1029 sysdate,
1030 g_fii_user_id,
1031 g_fii_login_id);
1032
1033 if g_debug_flag = 'Y' then
1034 FII_UTIL.put_line('Merged ' || SQL%ROWCOUNT || ' records in FII_Customer_Hierarchies.');
1035 FII_UTIL.stop_timer;
1036 FII_UTIL.print_timer('Duration');
1037 end if;
1038
1039 --Compare old and new data in memory before update/insert/delete.
1040 --1. Bulk collect new data into memory structures.
1041 --2. Bulk collect old data into memory structures.
1042 --3. Loop through new and old memory structures to populate an update/insert
1043 -- memory structure and a delete memory structure for each customer dimension table.
1044 --4. Bulk insert from update/insert and delete memory structures into tables.
1045 --5. Use update/insert and delete tables to merge and delete customer dimension tables.
1046
1047 g_state := 'Populate memory structure FII_Cust_Hier_New_MS.';
1048 if g_debug_flag = 'Y' then
1049 FII_UTIL.put_line(g_state);
1050 FII_UTIL.start_timer;
1051 end if;
1052
1053 SELECT *
1054 BULK COLLECT INTO FII_Cust_Hier_New_MS
1055 FROM FII_AR_Cust_Hier_Tmp_GT CH
1056 ORDER BY Parent_Party_ID, Next_Level_Party_ID, Child_Party_ID;
1057
1058 if g_debug_flag = 'Y' then
1059 FII_UTIL.put_line('Inserted ' || FII_Cust_Hier_New_MS.COUNT || ' records into FII_Cust_Hier_New_MS.');
1060 FII_UTIL.stop_timer;
1061 FII_UTIL.print_timer('Duration');
1062 end if;
1063
1064 g_state := 'Populate memory structure FII_CAcct_Denorm_New_MS.';
1065 if g_debug_flag = 'Y' then
1066 FII_UTIL.put_line(g_state);
1067 FII_UTIL.start_timer;
1068 end if;
1069
1070 SELECT *
1071 BULK COLLECT INTO FII_CAcct_Denorm_New_MS
1072 FROM FII_AR_CAccts_Tmp_GT CH
1073 ORDER BY Parent_Party_ID, Cust_Account_ID;
1074
1075 if g_debug_flag = 'Y' then
1079 end if;
1076 FII_UTIL.put_line('Inserted ' || FII_CAcct_Denorm_New_MS.COUNT || ' records into FII_CAcct_Denorm_New_MS.');
1077 FII_UTIL.stop_timer;
1078 FII_UTIL.print_timer('Duration');
1080
1081 g_state := 'Populate memory structure FII_Cust_Hier_Old_MS.';
1082 if g_debug_flag = 'Y' then
1083 FII_UTIL.put_line(g_state);
1084 FII_UTIL.start_timer;
1085 end if;
1086
1087 SELECT *
1088 BULK COLLECT INTO FII_Cust_Hier_Old_MS
1089 FROM FII_Customer_Hierarchies CH
1090 WHERE EXISTS (SELECT 1 FROM FII_AR_Parties_Delta_GT Log
1091 WHERE Log.Party_ID = CH.Child_Party_ID
1092 AND ((Log.Type_ID = 1 AND CH.Parent_Party_ID <> -999)
1093 OR (Log.Type_ID = 2
1094 AND CH.Parent_To_Next_Level + CH.Next_To_Child_Level > Log.Level_Number)
1095 OR (Log.Type_ID = 3
1096 AND (CH.Parent_To_Next_Level + CH.Next_To_Child_Level = 0))
1097 OR (Log.Type_ID = 4
1098 AND (CH.Parent_To_Next_Level + CH.Next_To_Child_Level = 0
1099 OR CH.Parent_Party_ID = -999))
1100 OR Log.Type_ID = 5))
1101 ORDER BY Parent_Party_ID, Next_Level_Party_ID, Child_Party_ID;
1102
1103 if g_debug_flag = 'Y' then
1104 FII_UTIL.put_line('Inserted ' || FII_Cust_Hier_Old_MS.COUNT || ' records into FII_Cust_Hier_Old_MS.');
1105 FII_UTIL.stop_timer;
1106 FII_UTIL.print_timer('Duration');
1107 end if;
1108
1109 g_state := 'Populate memory structure FII_CAcct_Denorm_Old_MS.';
1110 if g_debug_flag = 'Y' then
1111 FII_UTIL.put_line(g_state);
1112 FII_UTIL.start_timer;
1113 end if;
1114
1115 SELECT *
1116 BULK COLLECT INTO FII_CAcct_Denorm_Old_MS
1117 FROM FII_Cust_Accounts CAD
1118 WHERE EXISTS (SELECT 1 FROM FII_AR_Caccts_Delta_GT Log
1119 WHERE Log.Cust_Account_ID = CAD.Cust_Account_ID)
1120 ORDER BY Parent_Party_ID, Cust_Account_ID;
1121
1122 if g_debug_flag = 'Y' then
1123 FII_UTIL.put_line('Inserted ' || FII_CAcct_Denorm_Old_MS.COUNT || ' records into FII_CAcct_Denorm_Old_MS.');
1124 FII_UTIL.stop_timer;
1125 FII_UTIL.print_timer('Duration');
1126 end if;
1127
1128 g_state := 'Looping through FII_Cust_Hier_New_MS and FII_Cust_Hier_Old_MS.';
1129 if g_debug_flag = 'Y' then
1130 FII_UTIL.put_line(g_state);
1131 FII_UTIL.start_timer;
1132 end if;
1133
1134 l_cust_hier_old_marker := FII_Cust_Hier_Old_MS.FIRST;
1135 l_cust_hier_new_marker := FII_Cust_Hier_New_MS.FIRST;
1136
1137 WHILE l_cust_hier_old_marker IS NOT NULL
1138 AND l_cust_hier_new_marker IS NOT NULL LOOP
1139
1140 l_old_ch := FII_Cust_Hier_Old_MS(l_cust_hier_old_marker);
1141 l_new_ch := FII_Cust_Hier_New_MS(l_cust_hier_new_marker);
1142
1143 IF l_old_ch.Parent_Party_ID = l_new_ch.Parent_Party_ID
1144 AND l_old_ch.Next_Level_Party_ID = l_new_ch.Next_Level_Party_ID
1145 AND l_old_ch.Child_Party_ID = l_new_ch.Child_Party_ID THEN
1146
1147 --Check if the record has been updated. To avoid unnecessary updates, only
1148 --insert into FII_AR_Cust_Hier_UI_GT if a column has changed.
1149
1150 IF (l_old_ch.Next_Level_Is_Leaf_Flag <> l_new_ch.Next_Level_Is_Leaf_Flag
1151 OR l_old_ch.Parent_To_Next_Level <> l_new_ch.Parent_To_Next_Level
1152 OR l_old_ch.Next_To_Child_Level <> l_new_ch.Next_To_Child_Level) THEN
1153 l_ui_ch.Parent_Party_ID := l_new_ch.Parent_Party_ID;
1154 l_ui_ch.Next_Level_Party_ID := l_new_ch.Next_Level_Party_ID;
1155 l_ui_ch.Child_Party_ID := l_new_ch.Child_Party_ID;
1156 l_ui_ch.Next_Level_Is_Leaf_Flag := l_new_ch.Next_Level_Is_Leaf_Flag;
1157 l_ui_ch.Is_Hierarchical_Flag := l_new_ch.Is_Hierarchical_Flag;
1158 l_ui_ch.Parent_To_Next_Level := l_new_ch.Parent_To_Next_Level;
1159 l_ui_ch.Next_To_Child_Level := l_new_ch.Next_To_Child_Level;
1160
1161 FII_Cust_Hier_UI_MS(FII_Cust_Hier_UI_MS.Count+1) := l_ui_ch;
1162
1163 END IF;
1164
1165 l_cust_hier_old_marker := FII_Cust_Hier_Old_MS.Next(l_cust_hier_old_marker);
1166 l_cust_hier_new_marker := FII_Cust_Hier_New_MS.Next(l_cust_hier_new_marker);
1167
1168 ELSIF (l_old_ch.Parent_Party_ID < l_new_ch.Parent_Party_ID
1169 OR (l_old_ch.Parent_Party_ID = l_new_ch.Parent_Party_ID
1170 AND l_old_ch.Next_Level_Party_ID < l_new_ch.Next_Level_Party_ID)
1171 OR (l_old_ch.Parent_Party_ID = l_new_ch.Parent_Party_ID
1172 AND l_old_ch.Next_Level_Party_ID = l_new_ch.Next_Level_Party_ID
1173 AND l_old_ch.Child_Party_ID < l_new_ch.Child_Party_ID)) THEN
1174
1175 --This is a deleted record so insert into FII_Cust_Hier_D_MS.
1176 l_d_ch.Parent_Party_ID := l_old_ch.Parent_Party_ID;
1177 l_d_ch.Next_Level_Party_ID := l_old_ch.Next_Level_Party_ID;
1178 l_d_ch.Child_Party_ID := l_old_ch.Child_Party_ID;
1179
1180 FII_Cust_Hier_D_MS (FII_Cust_Hier_D_MS.Count+1) := l_d_ch;
1181
1182 l_cust_hier_old_marker := FII_Cust_Hier_Old_MS.Next(l_cust_hier_old_marker);
1183
1184 ELSE
1185 --This is a new record so insert into FII_Cust_Hier_UI_MS,
1189 l_ui_ch.Next_Level_Is_Leaf_Flag := l_new_ch.Next_Level_Is_Leaf_Flag;
1186 l_ui_ch.Parent_Party_ID := l_new_ch.Parent_Party_ID;
1187 l_ui_ch.Next_Level_Party_ID := l_new_ch.Next_Level_Party_ID;
1188 l_ui_ch.Child_Party_ID := l_new_ch.Child_Party_ID;
1190 l_ui_ch.Is_Hierarchical_Flag := l_new_ch.Is_Hierarchical_Flag;
1191 l_ui_ch.Parent_To_Next_Level := l_new_ch.Parent_To_Next_Level;
1192 l_ui_ch.Next_To_Child_Level := l_new_ch.Next_To_Child_Level;
1193
1194 FII_Cust_Hier_UI_MS (FII_Cust_Hier_UI_MS.Count+1) := l_ui_ch;
1195
1196 l_cust_hier_new_marker := FII_Cust_Hier_New_MS.Next(l_cust_hier_new_marker);
1197
1198 END IF;
1199
1200 END LOOP;
1201
1202
1203 WHILE l_cust_hier_old_marker IS NOT NULL LOOP
1204
1205 l_old_ch := FII_Cust_Hier_Old_MS(l_cust_hier_old_marker);
1206
1207 l_d_ch.Parent_Party_ID := l_old_ch.Parent_Party_ID;
1208 l_d_ch.Next_Level_Party_ID := l_old_ch.Next_Level_Party_ID;
1209 l_d_ch.Child_Party_ID := l_old_ch.Child_Party_ID;
1210
1211 FII_Cust_Hier_D_MS (FII_Cust_Hier_D_MS.Count+1) := l_d_ch;
1212
1213 l_cust_hier_old_marker := FII_Cust_Hier_Old_MS.Next(l_cust_hier_old_marker);
1214 END LOOP;
1215
1216
1217 WHILE l_cust_hier_new_marker IS NOT NULL LOOP
1218
1219 l_new_ch := FII_Cust_Hier_New_MS(l_cust_hier_new_marker);
1220
1221 l_ui_ch.Parent_Party_ID := l_new_ch.Parent_Party_ID;
1222 l_ui_ch.Next_Level_Party_ID := l_new_ch.Next_Level_Party_ID;
1223 l_ui_ch.Child_Party_ID := l_new_ch.Child_Party_ID;
1224 l_ui_ch.Next_Level_Is_Leaf_Flag := l_new_ch.Next_Level_Is_Leaf_Flag;
1225 l_ui_ch.Is_Hierarchical_Flag := l_new_ch.Is_Hierarchical_Flag;
1226 l_ui_ch.Parent_To_Next_Level := l_new_ch.Parent_To_Next_Level;
1227 l_ui_ch.Next_To_Child_Level := l_new_ch.Next_To_Child_Level;
1228
1229 FII_Cust_Hier_UI_MS (FII_Cust_Hier_UI_MS.Count+1) := l_ui_ch;
1230
1231 l_cust_hier_new_marker := FII_Cust_Hier_New_MS.Next(l_cust_hier_new_marker);
1232 END LOOP;
1233
1234 if g_debug_flag = 'Y' then
1235 FII_UTIL.put_line('Completed looping through FII_Cust_Hier_New_MS and FII_Cust_Hier_Old_MS.');
1236 FII_UTIL.stop_timer;
1237 FII_UTIL.print_timer('Duration');
1238 end if;
1239
1240 g_state := 'Looping through FII_CAcct_Denorm_New_MS and FII_CAcct_Denorm_Old_MS.';
1241 if g_debug_flag = 'Y' then
1242 FII_UTIL.put_line(g_state);
1243 FII_UTIL.start_timer;
1244 end if;
1245
1246 l_cacct_denorm_old_marker := FII_CAcct_Denorm_Old_MS.FIRST;
1247 l_cacct_denorm_new_marker := FII_CAcct_Denorm_New_MS.FIRST;
1248
1249 WHILE l_cacct_denorm_old_marker IS NOT NULL
1250 AND l_cacct_denorm_new_marker IS NOT NULL LOOP
1251 l_old_cad := FII_CAcct_Denorm_Old_MS(l_cacct_denorm_old_marker);
1252 l_new_cad := FII_CAcct_Denorm_New_MS(l_cacct_denorm_new_marker);
1253
1254 IF l_old_cad.Parent_Party_ID = l_new_cad.Parent_Party_ID
1255 AND l_old_cad.Cust_Account_ID = l_new_cad.Cust_Account_ID THEN
1256
1257 --To avoid unnecessary updates, ignore unchanged record.
1258 --Only update occurs if party merge changes account party id. In this case, delete then insert.
1259
1260 IF l_old_cad.Account_Owner_Party_ID <> l_new_cad.Account_Party_ID THEN
1261 --Delete old record.
1262 l_d_cad.Parent_Party_ID := l_old_cad.Parent_Party_ID;
1263 l_d_cad.Cust_Account_ID := l_old_cad.Cust_Account_ID;
1264
1265 FII_CAcct_Denorm_D_MS (FII_CAcct_Denorm_D_MS.Count+1) := l_d_cad;
1266
1267 --Insert new record.
1268 l_i_cad.Parent_Party_ID := l_new_cad.Parent_Party_ID;
1269 l_i_cad.Cust_Account_ID := l_new_cad.Cust_Account_ID;
1270 l_i_cad.Account_Owner_Party_ID := l_new_cad.Account_Party_ID;
1271 l_i_cad.Account_Number := l_new_cad.Account_Number;
1272 l_i_cad.Creation_Date := sysdate;
1273 l_i_cad.Created_By := g_fii_user_id;
1274 l_i_cad.Last_Update_Date := sysdate;
1275 l_i_cad.Last_Updated_By := g_fii_user_id;
1276 l_i_cad.Last_Update_Login := g_fii_login_id;
1277
1278 FII_CAcct_Denorm_I_MS (FII_CAcct_Denorm_I_MS.Count+1) := l_i_cad;
1279
1280 END IF;
1281
1282 l_cacct_denorm_old_marker := FII_CAcct_Denorm_Old_MS.Next(l_cacct_denorm_old_marker);
1283 l_cacct_denorm_new_marker := FII_CAcct_Denorm_New_MS.Next(l_cacct_denorm_new_marker);
1284
1285 ELSIF (l_old_cad.Parent_Party_ID < l_new_cad.Parent_Party_ID
1286 OR (l_old_cad.Parent_Party_ID = l_new_cad.Parent_Party_ID
1287 AND l_old_cad.Cust_Account_ID < l_new_cad.Cust_Account_ID)) THEN
1288 --This is a deleted record so insert into FII_CAcct_Denorm_D_MS.
1289 l_d_cad.Parent_Party_ID := l_old_cad.Parent_Party_ID;
1290 l_d_cad.Cust_Account_ID := l_old_cad.Cust_Account_ID;
1291
1292 FII_CAcct_Denorm_D_MS (FII_CAcct_Denorm_D_MS.Count+1) := l_d_cad;
1293
1294
1295 l_cacct_denorm_old_marker := FII_CAcct_Denorm_Old_MS.Next(l_cacct_denorm_old_marker);
1296
1297 ELSE
1298 --This is a new record so insert into FII_CAcct_Denorm_I_MS,
1299 l_i_cad.Parent_Party_ID := l_new_cad.Parent_Party_ID;
1300 l_i_cad.Cust_Account_ID := l_new_cad.Cust_Account_ID;
1301 l_i_cad.Account_Owner_Party_ID := l_new_cad.Account_Party_ID;
1305 l_i_cad.Last_Update_Date := sysdate;
1302 l_i_cad.Account_Number := l_new_cad.Account_Number;
1303 l_i_cad.Creation_Date := sysdate;
1304 l_i_cad.Created_By := g_fii_user_id;
1306 l_i_cad.Last_Updated_By := g_fii_user_id;
1307 l_i_cad.Last_Update_Login := g_fii_login_id;
1308
1309 FII_CAcct_Denorm_I_MS (FII_CAcct_Denorm_I_MS.Count+1) := l_i_cad;
1310
1311 l_cacct_denorm_new_marker := FII_CAcct_Denorm_New_MS.Next(l_cacct_denorm_new_marker);
1312
1313 END IF;
1314
1315 END LOOP;
1316
1317
1318 WHILE l_cacct_denorm_old_marker IS NOT NULL LOOP
1319 l_old_cad := FII_CAcct_Denorm_Old_MS(l_cacct_denorm_old_marker);
1320
1321 l_d_cad.Parent_Party_ID := l_old_cad.Parent_Party_ID;
1322 l_d_cad.Cust_Account_ID := l_old_cad.Cust_Account_ID;
1323
1324 FII_CAcct_Denorm_D_MS (FII_CAcct_Denorm_D_MS.Count+1) := l_d_cad;
1325
1326 l_cacct_denorm_old_marker := FII_CAcct_Denorm_Old_MS.Next(l_cacct_denorm_old_marker);
1327 END LOOP;
1328
1329
1330 WHILE l_cacct_denorm_new_marker IS NOT NULL LOOP
1331 l_new_cad := FII_CAcct_Denorm_New_MS(l_cacct_denorm_new_marker);
1332
1333 l_i_cad.Parent_Party_ID := l_new_cad.Parent_Party_ID;
1334 l_i_cad.Cust_Account_ID := l_new_cad.Cust_Account_ID;
1335 l_i_cad.Account_Owner_Party_ID := l_new_cad.Account_Party_ID;
1336 l_i_cad.Account_Number := l_new_cad.Account_Number;
1337 l_i_cad.Creation_Date := sysdate;
1338 l_i_cad.Created_By := g_fii_user_id;
1339 l_i_cad.Last_Update_Date := sysdate;
1340 l_i_cad.Last_Updated_By := g_fii_user_id;
1341 l_i_cad.Last_Update_Login := g_fii_login_id;
1342
1343 FII_CAcct_Denorm_I_MS (FII_CAcct_Denorm_I_MS.Count+1) := l_i_cad;
1344
1345 l_cacct_denorm_new_marker := FII_CAcct_Denorm_New_MS.Next(l_cacct_denorm_new_marker);
1346 END LOOP;
1347
1348 if g_debug_flag = 'Y' then
1349 FII_UTIL.put_line('Completed looping through FII_CAcct_Denorm_New_MS and FII_CAcct_Denorm_Old_MS.');
1350 FII_UTIL.stop_timer;
1351 FII_UTIL.print_timer('Duration');
1352 end if;
1353
1354 g_state := 'Bulk inserting into FII_AR_Cust_Hier_UI_GT from FII_Cust_Hier_UI_MS.';
1355 if g_debug_flag = 'Y' then
1356 FII_UTIL.put_line(g_state);
1357 FII_UTIL.start_timer;
1358 end if;
1359
1360 IF FII_Cust_Hier_UI_MS.Count > 0 THEN
1361 FORALL i IN FII_Cust_Hier_UI_MS.First..FII_Cust_Hier_UI_MS.Last
1362 INSERT INTO FII_AR_Cust_Hier_UI_GT VALUES FII_Cust_Hier_UI_MS(i);
1363 END IF;
1364
1365 if g_debug_flag = 'Y' then
1366 FII_UTIL.put_line('Completed populating FII_AR_Cust_Hier_UI_GT.');
1367 FII_UTIL.stop_timer;
1368 FII_UTIL.print_timer('Duration');
1369 end if;
1370
1371 g_state := 'Bulk inserting into FII_AR_Cust_Hier_D_GT from FII_Cust_Hier_D_MS.';
1372 if g_debug_flag = 'Y' then
1373 FII_UTIL.put_line(g_state);
1374 FII_UTIL.start_timer;
1375 end if;
1376
1377 IF FII_Cust_Hier_D_MS.Count > 0 THEN
1378 FORALL i IN FII_Cust_Hier_D_MS.First..FII_Cust_Hier_D_MS.Last
1379 INSERT INTO FII_AR_Cust_Hier_D_GT VALUES FII_Cust_Hier_D_MS(i);
1380 END IF;
1381
1382 if g_debug_flag = 'Y' then
1383 FII_UTIL.put_line('Completed populating FII_AR_Cust_Hier_D_GT.');
1384 FII_UTIL.stop_timer;
1385 FII_UTIL.print_timer('Duration');
1386 end if;
1387
1388 g_state := 'Bulk inserting into FII_AR_CAccts_D_GT from FII_CAcct_Denorm_D_MS.';
1389 if g_debug_flag = 'Y' then
1390 FII_UTIL.put_line(g_state);
1391 FII_UTIL.start_timer;
1392 end if;
1393
1394 IF FII_CAcct_Denorm_D_MS.Count > 0 THEN
1395 FORALL i IN FII_CAcct_Denorm_D_MS.First..FII_CAcct_Denorm_D_MS.Last
1396 INSERT INTO FII_AR_CAccts_D_GT VALUES FII_CAcct_Denorm_D_MS(i);
1397 END IF;
1398
1399 if g_debug_flag = 'Y' then
1400 FII_UTIL.put_line('Completed populating FII_AR_CAccts_D_GT.');
1401 FII_UTIL.stop_timer;
1402 FII_UTIL.print_timer('Duration');
1403 end if;
1404
1405 g_state := 'Deleting records from FII_Customer_Hierarchies using FII_AR_Cust_Hier_D_GT';
1406 if g_debug_flag = 'Y' then
1407 FII_UTIL.put_line(g_state);
1408 FII_UTIL.start_timer;
1409 end if;
1410
1411 DELETE FROM FII_Customer_Hierarchies CH
1412 WHERE EXISTS (SELECT 1
1413 FROM FII_AR_Cust_Hier_D_GT D
1414 WHERE D.Parent_Party_ID = CH.Parent_Party_ID
1415 AND D.Next_Level_Party_ID = CH.Next_Level_Party_ID
1416 AND D.Child_Party_ID = CH.Child_Party_ID);
1417
1418 if g_debug_flag = 'Y' then
1419 FII_UTIL.put_line('Deleted ' || SQL%ROWCOUNT || ' records from FII_Customer_Hierarchies.');
1420 FII_UTIL.stop_timer;
1421 FII_UTIL.print_timer('Duration');
1422 end if;
1423
1424
1425 g_state := 'Merging records into FII_Customer_Hierarchies using FII_AR_Cust_Hier_UI_GT.';
1426 if g_debug_flag = 'Y' then
1427 FII_UTIL.put_line(g_state);
1428 FII_UTIL.start_timer;
1429 end if;
1430
1431 MERGE INTO FII_Customer_Hierarchies CH
1432 USING FII_AR_Cust_Hier_UI_GT UI
1433 ON (CH.Parent_Party_ID = UI.Parent_Party_ID AND
1434 CH.Child_Party_ID = UI.Child_Party_ID)
1435 WHEN MATCHED THEN
1436 UPDATE SET CH.Next_Level_Party_ID = UI.Next_Level_Party_ID,
1437 CH.Next_Level_Is_Leaf_Flag = UI.Next_Level_Is_Leaf_Flag,
1438 CH.Parent_To_Next_Level = UI.Parent_To_Next_Level,
1439 CH.Next_To_Child_Level = UI.Next_To_Child_Level,
1440 CH.Last_Update_Date = sysdate,
1441 CH.Last_Updated_By = g_fii_user_id,
1442 CH.Last_Update_Login = g_fii_login_id
1443 WHEN NOT MATCHED THEN
1444 INSERT (CH.Parent_Party_ID,
1445 CH.Next_Level_Party_ID,
1446 CH.Child_Party_ID,
1447 CH.Next_Level_Is_Leaf_Flag,
1448 CH.Is_Hierarchical_Flag,
1449 CH.Parent_To_Next_Level,
1450 CH.Next_To_Child_Level,
1451 CH.Creation_Date,
1452 CH.Created_By,
1453 CH.Last_Update_Date,
1454 CH.Last_Updated_By,
1455 CH.Last_Update_Login)
1456 VALUES (UI.Parent_Party_ID,
1457 UI.Next_Level_Party_ID,
1458 UI.Child_Party_ID,
1459 UI.Next_Level_Is_Leaf_Flag,
1460 UI.Is_Hierarchical_Flag,
1461 UI.Parent_To_Next_Level,
1462 UI.Next_To_Child_Level,
1463 sysdate,
1464 g_fii_user_id,
1465 sysdate,
1466 g_fii_user_id,
1467 g_fii_login_id);
1468
1469 if g_debug_flag = 'Y' then
1470 FII_UTIL.put_line('Merged ' || SQL%ROWCOUNT || ' records into FII_Customer_Hierarchies.');
1471 FII_UTIL.stop_timer;
1472 FII_UTIL.print_timer('Duration');
1473 end if;
1474
1475 g_state := 'Deleting records from FII_Cust_Accounts using FII_AR_CAccts_D_GT';
1476 if g_debug_flag = 'Y' then
1477 FII_UTIL.put_line(g_state);
1478 FII_UTIL.start_timer;
1479 end if;
1480
1481 DELETE FROM FII_Cust_Accounts CAD
1482 WHERE EXISTS (SELECT 1
1483 FROM FII_AR_CAccts_D_GT D
1484 WHERE D.Parent_Party_ID = CAD.Parent_Party_ID
1485 AND D.Cust_Account_ID = CAD.Cust_Account_ID);
1486
1487 if g_debug_flag = 'Y' then
1488 FII_UTIL.put_line('Deleted ' || SQL%ROWCOUNT || ' records from FII_Cust_Accounts.');
1489 FII_UTIL.stop_timer;
1490 FII_UTIL.print_timer('Duration');
1491 end if;
1492
1493 g_state := 'Bulk inserting into FII_Cust_Accounts from FII_CAcct_Denorm_I_MS.';
1494 if g_debug_flag = 'Y' then
1495 FII_UTIL.put_line(g_state);
1496 FII_UTIL.start_timer;
1497 end if;
1498
1499 IF FII_CAcct_Denorm_I_MS.Count > 0 THEN
1500 FORALL i IN FII_CAcct_Denorm_I_MS.First..FII_CAcct_Denorm_I_MS.Last
1501 INSERT INTO FII_Cust_Accounts VALUES FII_CAcct_Denorm_I_MS(i);
1502 END IF;
1503
1504 if g_debug_flag = 'Y' then
1505 FII_UTIL.put_line('Completed populating FII_Cust_Accounts.');
1506 FII_UTIL.stop_timer;
1507 FII_UTIL.print_timer('Duration');
1508 end if;
1509
1510 g_state := 'Calling BIS_COLLECTION_UTILITIES.wrapup';
1511 BIS_COLLECTION_UTILITIES.wrapup(
1512 p_status => TRUE,
1513 p_period_from => g_last_load_date,
1514 p_period_to => g_sysdate);
1515
1516
1517 EXCEPTION
1518 WHEN OTHERS THEN
1519 g_retcode := -1;
1520 retcode := g_retcode;
1521
1522 g_exception_msg := g_retcode || ':' || sqlerrm;
1523 FII_UTIL.put_line('Error occured while ' || g_state);
1524 FII_UTIL.put_line(g_exception_msg);
1525
1526
1527 END INCRE_UPDATE;
1528
1529
1530 END FII_AR_CUSTOMER_DIMENSION_PKG;