[Home] [Help]
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
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.';
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);
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;
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,
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,
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;
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:
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:
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:
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;
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);
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:
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;
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.');
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: