[Home] [Help]
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.';
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,
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:
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
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')
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;
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,
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:
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:
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.';
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;
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,
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,
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
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;
427: FII_UTIL.print_timer('Duration');
428: end if;
429:
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;
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;
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;
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)
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:
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
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;
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
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:
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;
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
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: