[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_state := 'Inserting dummy record.';
INSERT INTO FII_Customer_Hierarchies (
Parent_Party_ID,
Next_Level_Party_ID,
Child_Party_ID,
Next_Level_Is_Leaf_Flag,
Is_Hierarchical_Flag,
Parent_To_Next_Level,
Next_To_Child_Level,
Creation_Date,
Created_By,
Last_Update_Date,
Last_Updated_By,
Last_Update_Login)
VALUES (
-999,
-2,
-2,
'Y',
DECODE(g_hierarchy_type, NULL, 'N', 'Y'),
1,
0,
sysdate,
g_fii_user_id,
sysdate,
g_fii_user_id,
g_fii_login_id);
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)
VALUES (-2, -2, -2, sysdate, g_fii_user_id, sysdate, g_fii_user_id, g_fii_login_id);
SELECT nvl(MAX(Batch_Party_ID), -1) INTO l_max_batch_party_id
FROM HZ_Merge_Party_History;
UPDATE FII_Change_Log
SET (Item_Value, Last_Update_Date, Last_Update_Login, Last_Updated_By)
= (SELECT l_max_batch_party_id, sysdate, g_fii_login_id, g_fii_user_id FROM DUAL)
WHERE log_item = 'MAX_BATCH_PARTY_ID';
INSERT INTO FII_Change_Log (Log_Item, Item_Value, Creation_Date, Created_By, Last_Update_Date, Last_Update_Login, Last_Updated_By)
VALUES ('MAX_BATCH_PARTY_ID', l_max_batch_party_id, sysdate, g_fii_user_id, sysdate, g_fii_login_id, g_fii_user_id);
inserted from -999 to any top node party. Party pairs in this table
will be used to populate Parent_Party_ID and Next_Level_Party_ID in
FII_Customer_Hierarchies, not including self-records. */
INSERT ALL
WHEN (Leaf_Child_Flag = 'Y')
THEN INTO FII_AR_Cust_LNodes_GT(
Leaf_Node_ID)
VALUES (Parent_ID)
WHEN (Top_Parent_Flag = 'Y' OR Level_Number = 1)
THEN INTO FII_AR_Cust_Rlns_GT(
Parent_ID,
Next_ID)
VALUES (CASE WHEN Level_Number = 0 THEN -999
ELSE Parent_ID END,
CASE WHEN Level_Number = 0 THEN Parent_ID
ELSE Child_ID END)
SELECT Parent_ID,
Child_ID,
Level_Number,
Top_Parent_Flag,
Leaf_Child_Flag
FROM HZ_HIERARCHY_NODES
WHERE Hierarchy_Type = g_hierarchy_type
AND ( Level_Number = 1 OR
(Level_Number = 0 AND (Top_Parent_Flag = 'Y' OR Leaf_Child_Flag = 'Y')) )
AND g_sysdate BETWEEN Effective_Start_Date AND Effective_End_Date;
FII_UTIL.put_line('Inserted ' || SQL%ROWCOUNT || ' records into FII_AR_Cust_LNodes_GT and FII_AR_Cust_Rlns_GT');
INSERT INTO FII_Customer_Hierarchies(
Parent_Party_ID,
Next_Level_Party_ID,
Child_Party_ID,
Next_Level_Is_Leaf_Flag,
Is_Hierarchical_Flag,
Parent_To_Next_Level,
Next_To_Child_Level,
Creation_Date,
Created_By,
Last_Update_Date,
Last_Updated_By,
Last_Update_Login)
SELECT CASE WHEN Temp.ID = 1 THEN PTN.Parent_ID
ELSE PTN.Next_ID END Parent_Party_ID,
PTN.Next_ID Next_Level_Party_ID,
HN.Child_ID Child_Party_ID,
DECODE(Leaf.Leaf_Node_ID, NULL, 'N', 'Y') Next_Level_Is_Leaf_Flag,
'Y' Is_Hierarchical_Flag,
CASE WHEN Temp.ID = 1 THEN 1
ELSE 0 END Parent_To_Next_Level,
HN.Level_Number Next_To_Child_Level,
sysdate,
g_fii_user_id,
sysdate,
g_fii_user_id,
g_fii_login_id
FROM FII_AR_Cust_Rlns_GT PTN,
HZ_Hierarchy_Nodes HN,
(SELECT 1 ID FROM Dual UNION
SELECT 2 ID FROM Dual) Temp,
FII_AR_Cust_LNodes_GT Leaf
WHERE PTN.Next_ID = HN.Parent_ID
AND (Temp.ID = 1 OR HN.Parent_ID = HN.Child_ID)
AND HN.Hierarchy_Type = g_hierarchy_type
AND g_sysdate BETWEEN HN.Effective_Start_Date AND HN.Effective_End_Date
AND PTN.Next_ID = Leaf.Leaf_Node_ID (+);
FII_UTIL.put_line('Inserted ' || SQL%ROWCOUNT || ' records into FII_Customer_Hierarchies');
SELECT nvl(MAX(Cust_Account_ID),-1) INTO l_max_cust_account_id
FROM HZ_Cust_Accounts;
UPDATE FII_Change_Log
SET (Item_Value, Last_Update_Date, Last_Update_Login, Last_Updated_By)
= (SELECT l_max_cust_account_id, sysdate, g_fii_login_id, g_fii_user_id FROM DUAL)
WHERE log_item = 'MAX_CUST_ACCOUNT_ID';
INSERT INTO FII_Change_Log (Log_Item, Item_Value, Creation_Date, Created_By, Last_Update_Date, Last_Update_Login, Last_Updated_By)
VALUES ('MAX_CUST_ACCOUNT_ID', l_max_cust_account_id, sysdate, g_fii_user_id, sysdate, g_fii_login_id, g_fii_user_id);
INSERT ALL
WHEN (Parent_Party_ID IS NOT NULL AND Parent_Party_ID <> -999)
THEN INTO FII_Cust_Accounts(
Parent_Party_ID,
Cust_Account_ID,
Account_Owner_Party_ID,
Account_Number,
Creation_Date,
Created_By,
Last_Update_Date,
Last_Updated_By,
Last_Update_Login)
VALUES (Parent_Party_ID,
Cust_Account_ID,
Party_ID,
Account_Number,
sysdate,
g_fii_user_id,
sysdate,
g_fii_user_id,
g_fii_login_id)
WHEN (Parent_Party_ID IS NULL)
THEN INTO FII_Cust_Accounts(
Parent_Party_ID,
Cust_Account_ID,
Account_Owner_Party_ID,
Account_Number,
Creation_Date,
Created_By,
Last_Update_Date,
Last_Updated_By,
Last_Update_Login)
VALUES (Party_ID,
Cust_Account_ID,
Party_ID,
Account_Number,
sysdate,
g_fii_user_id,
sysdate,
g_fii_user_id,
g_fii_login_id)
WHEN (Parent_Party_ID IS NULL AND SRLID = 1)
THEN INTO FII_Customer_Hierarchies(
Parent_Party_ID,
Next_Level_Party_ID,
Child_Party_ID,
Next_Level_Is_Leaf_Flag,
Is_Hierarchical_Flag,
Parent_To_Next_Level,
Next_To_Child_Level,
Creation_Date,
Created_By,
Last_Update_Date,
Last_Updated_By,
Last_Update_Login)
VALUES (-999,
Party_ID,
Party_ID,
'Y',
DECODE(g_hierarchy_type, NULL, 'N', 'Y'),
1,
0,
sysdate,
g_fii_user_id,
sysdate,
g_fii_user_id,
g_fii_login_id)
WHEN (Parent_Party_ID IS NULL AND SRLID = 1)
THEN INTO FII_Customer_Hierarchies(
Parent_Party_ID,
Next_Level_Party_ID,
Child_Party_ID,
Next_Level_Is_Leaf_Flag,
Is_Hierarchical_Flag,
Parent_To_Next_Level,
Next_To_Child_Level,
Creation_Date,
Created_By,
Last_Update_Date,
Last_Updated_By,
Last_Update_Login)
VALUES (Party_ID,
Party_ID,
Party_ID,
'Y',
DECODE(g_hierarchy_type, NULL, 'N', 'Y'),
0,
0,
sysdate,
g_fii_user_id,
sysdate,
g_fii_user_id,
g_fii_login_id)
SELECT /*+ parallel(CA) */ Hier.Parent_Party_ID,
CA.Party_ID,
CA.Cust_Account_ID,
CA.Account_Number,
ROW_NUMBER () OVER (
PARTITION BY CA.Party_ID
ORDER BY CA.Party_ID NULLS LAST) SRLID
FROM HZ_Cust_Accounts CA,
FII_Customer_Hierarchies Hier
WHERE CA.Party_ID = Hier.Child_Party_ID (+)
AND CA.Cust_Account_ID <= l_max_cust_account_id;
FII_UTIL.put_line('Inserted ' || SQL%ROWCOUNT || ' records into FII_Customer_Hierarchies');
PROCEDURE INCRE_UPDATE (errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY VARCHAR2) IS
l_start_date DATE;
SELECT Party_ID
FROM FII_AR_Parties_Level1_GT GROUP BY Party_ID;
g_state := 'Inside the INCRE_UPDATE procedure.';
SELECT item_value
INTO l_prev_max_batch_party_id
FROM fii_change_log
WHERE log_item = 'MAX_BATCH_PARTY_ID';
SELECT nvl(MAX(Batch_Party_ID), -1) INTO l_max_batch_party_id
FROM HZ_Merge_Party_History
WHERE Batch_Party_ID >= l_prev_max_batch_party_id;
UPDATE FII_Change_Log
SET (Item_Value, Last_Update_Date, Last_Update_Login, Last_Updated_By)
= (SELECT l_max_batch_party_id, sysdate, g_fii_login_id, g_fii_user_id FROM DUAL)
WHERE log_item = 'MAX_BATCH_PARTY_ID';
INSERT ALL
WHEN (1=1)
THEN INTO FII_AR_Parties_Delta_GT(Party_ID, Type_ID)
VALUES (From_Entity_ID, 5)
WHEN (1=1)
THEN INTO FII_AR_Parties_Delta_GT(Party_ID, Type_ID)
VALUES (To_Entity_ID, 6)
SELECT From_Entity_ID, To_Entity_ID
FROM HZ_Merge_Party_History M,
HZ_Merge_Dictionary D
WHERE M.merge_dict_id = D.merge_dict_id
AND M.batch_party_id > l_prev_max_batch_party_id
AND M.batch_party_id <= l_max_batch_party_id
AND D.entity_name = 'HZ_PARTIES';
FII_UTIL.put_line('Inserted ' || SQL%ROWCOUNT || ' records into FII_AR_Parties_Delta_GT');
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.';
INSERT ALL
WHEN (Level_Number = 0)
THEN INTO FII_AR_Parties_Delta_GT (
Party_ID,
Type_ID)
VALUES (Child_ID,
DECODE(Top_Parent_Flag, 'Y',
CASE WHEN g_sysdate BETWEEN Effective_Start_Date AND Effective_End_Date
THEN 4 ELSE 3 END,
3))
WHEN (Effective_End_Date BETWEEN g_last_load_date AND g_sysdate
AND Level_Number = 1)
THEN INTO FII_AR_Parties_Delta_GT (
Party_ID,
Type_ID,
Level_Number)
VALUES (Child_ID, 2, 0)
WHEN (Effective_End_Date NOT BETWEEN g_last_load_date AND g_sysdate
AND Level_Number = 1)
THEN INTO FII_AR_Parties_Level1_GT (
Party_ID)
VALUES (Child_ID)
SELECT Child_ID,
Level_Number,
Top_Parent_Flag,
Effective_End_Date,
Effective_Start_Date,
Last_Update_Date
FROM HZ_Hierarchy_Nodes
WHERE Hierarchy_Type = g_hierarchy_type
AND (Level_Number = 0 OR Level_Number = 1)
AND (Effective_End_Date BETWEEN g_last_load_date AND g_sysdate
OR Effective_Start_Date BETWEEN g_last_load_date AND g_sysdate
OR Last_Update_Date BETWEEN g_last_load_date AND g_sysdate);
FII_UTIL.put_line('Inserted ' || SQL%ROWCOUNT || ' records into FII_AR_Parties_Delta_GT and FII_AR_Parties_Level1_GT');
INSERT ALL
WHEN (SRLID=1)
THEN INTO FII_AR_Parties_Level2_GT(
Party_ID)
VALUES(Child_ID)
WHEN (SRLID=1)
THEN INTO FII_AR_Top_To_Source_GT(
Top_Node_ID,
Source_Node_ID,
Level_Number)
VALUES(Parent_ID,
Party_Record.Party_ID,
Level_Number)
SELECT Parent_ID,
Child_ID,
Level Level_Number,
ROW_NUMBER () OVER (ORDER BY Level DESC) SRLID
FROM (SELECT * FROM HZ_Hierarchy_Nodes
WHERE Level_Number = 1
AND Hierarchy_Type = g_hierarchy_type
AND g_sysdate BETWEEN Effective_Start_Date AND Effective_End_Date)
START WITH Child_ID = Party_Record.Party_ID
CONNECT BY PRIOR Parent_ID = Child_ID;
INSERT INTO FII_AR_Parties_Delta_GT(
Party_ID,
Type_ID,
Level_Number)
VALUES(Party_Record.Party_ID,
2,
0);
INSERT INTO FII_AR_Parties_Delta_GT(Party_ID, Type_ID)
SELECT HN.Child_ID, 1
FROM FII_AR_Parties_Level2_GT Log,
HZ_Hierarchy_Nodes HN
WHERE Log.Party_ID = HN.Parent_ID
AND HN.Hierarchy_Type = g_hierarchy_type
AND g_sysdate BETWEEN HN.Effective_Start_Date AND HN.Effective_End_Date;
FII_UTIL.put_line('Inserted ' || SQL%ROWCOUNT || ' records into FII_AR_Parties_Delta_GT.');
INSERT INTO FII_AR_Top_To_Source_GT(
Top_Node_ID,
Source_Node_ID,
Level_Number)
SELECT GT.Top_Node_ID,
HN.Child_ID,
GT.Level_Number + HN.Level_Number Level_Number
FROM FII_AR_Top_To_Source_GT GT,
HZ_Hierarchy_Nodes HN
WHERE GT.Source_Node_ID = HN.Parent_ID
AND HN.Hierarchy_Type = g_hierarchy_type
AND g_sysdate BETWEEN HN.Effective_Start_Date AND HN.Effective_End_Date
AND HN.Level_Number > 0;
FII_UTIL.put_line('Inserted ' || SQL%ROWCOUNT || ' records into FII_AR_Top_To_Source_GT.');
g_state := 'Populate FII_AR_Parties_Delta_GT with descendants of deleted relationships.';
INSERT INTO FII_AR_Parties_Delta_GT(
Party_ID,
Type_ID,
Level_Number)
SELECT HN.Child_ID,
2,
HN.Level_Number
FROM FII_AR_Parties_Delta_GT GT,
HZ_Hierarchy_Nodes HN
WHERE GT.Party_ID = HN.Parent_ID
AND GT.Type_ID = 2
AND HN.Hierarchy_Type = g_hierarchy_type
AND g_sysdate BETWEEN HN.Effective_Start_Date AND HN.Effective_End_Date
AND HN.Level_Number > 0;
FII_UTIL.put_line('Inserted ' || SQL%ROWCOUNT || ' records into FII_AR_Parties_Delta_GT.');
INSERT ALL
WHEN (Leaf_Child_Flag = 'Y')
THEN INTO FII_AR_Cust_LNodes_GT(
Leaf_Node_ID)
VALUES (Parent_ID)
WHEN (Top_Parent_Flag = 'Y' OR Level_Number = 1)
THEN INTO FII_AR_Cust_Rlns_GT(
Parent_ID,
Next_ID)
VALUES (CASE WHEN Level_Number = 0 THEN -999
ELSE Parent_ID END,
CASE WHEN Level_Number = 0 THEN Parent_ID
ELSE Child_ID END)
SELECT Parent_ID,
Child_ID,
Level_Number,
Top_Parent_Flag,
Leaf_Child_Flag
FROM HZ_HIERARCHY_NODES HN
WHERE EXISTS (SELECT 1 FROM FII_AR_Parties_Delta_GT Log
WHERE Log.Party_ID = HN.Child_ID)
AND Hierarchy_Type = g_hierarchy_type
AND ( Level_Number = 1 OR
(Level_Number = 0 AND (Top_Parent_Flag = 'Y' OR Leaf_Child_Flag = 'Y')) )
AND g_sysdate BETWEEN Effective_Start_Date AND Effective_End_Date;
FII_UTIL.put_line('Inserted ' || SQL%ROWCOUNT || ' records into FII_AR_Cust_LNodes_GT and FII_AR_Cust_Rlns_GT.');
INSERT INTO FII_AR_Cust_Hier_Tmp_GT(
Parent_Party_ID,
Next_Level_Party_ID,
Child_Party_ID,
Next_Level_Is_Leaf_Flag,
Is_Hierarchical_Flag,
Parent_To_Next_Level,
Next_To_Child_Level)
SELECT CASE WHEN Temp.ID = 1 THEN PTN.Parent_ID
ELSE PTN.Next_ID END Parent_Party_ID,
PTN.Next_ID Next_Level_Party_ID,
HN.Child_ID Child_Party_ID,
DECODE(Leaf.Leaf_Node_ID, NULL, 'N', 'Y') Next_Level_Is_Leaf_Flag,
DECODE(g_hierarchy_type, NULL, 'N', 'Y') Is_Hierarchical_Flag,
CASE WHEN Temp.ID = 1 THEN 1
ELSE 0 END Parent_To_Next_Level,
HN.Level_Number Next_To_Child_Level
FROM FII_AR_Cust_Rlns_GT PTN,
HZ_Hierarchy_Nodes HN,
(SELECT 1 ID FROM Dual UNION
SELECT 2 ID FROM Dual) Temp,
FII_AR_Cust_LNodes_GT Leaf
WHERE PTN.Next_ID = HN.Parent_ID
AND (Temp.ID = 1 OR HN.Parent_ID = HN.Child_ID)
AND HN.Hierarchy_Type = g_hierarchy_type
AND g_sysdate BETWEEN HN.Effective_Start_Date AND HN.Effective_End_Date
AND PTN.Next_ID = Leaf.Leaf_Node_ID (+);
FII_UTIL.put_line('Inserted ' || SQL%ROWCOUNT || ' records into FII_AR_Cust_Hier_Tmp_GT.');
SELECT item_value
INTO l_prev_max_cust_account_id
FROM fii_change_log
WHERE log_item = 'MAX_CUST_ACCOUNT_ID';
SELECT nvl(MAX(Cust_Account_ID),-1) INTO l_max_cust_account_id
FROM HZ_Cust_Accounts
WHERE Cust_Account_ID >= l_prev_max_cust_account_id;
UPDATE FII_Change_Log
SET (Item_Value, Last_Update_Date, Last_Update_Login, Last_Updated_By)
= (SELECT NVL(l_max_cust_account_id, l_prev_max_cust_account_id), sysdate, g_fii_login_id, g_fii_user_id FROM DUAL)
WHERE log_item = 'MAX_CUST_ACCOUNT_ID';
g_state := 'Populate FII_AR_CAccts_Delta_GT with customer accounts that are new or in an updated hierarchy.';
INSERT INTO FII_AR_Caccts_Delta_GT(Cust_Account_ID, Party_ID, Account_Number)
SELECT Cust_Account_ID, Party_ID, Account_Number
FROM HZ_Cust_Accounts CA
WHERE EXISTS (SELECT 1 FROM FII_AR_Parties_Delta_GT Log
WHERE Log.Party_ID = CA.Party_ID)
OR (Cust_Account_ID > l_prev_max_cust_account_id
AND Cust_Account_ID <= l_max_cust_account_id);
FII_UTIL.put_line('Inserted ' || SQL%ROWCOUNT || ' records into FII_AR_CAccts_Delta_GT.');
INSERT ALL
WHEN (Parent_Party_ID IS NOT NULL AND Parent_Party_ID <> -999)
THEN INTO FII_AR_Caccts_Tmp_GT(
Parent_Party_ID,
Cust_Account_ID,
Account_Party_ID,
Account_Number)
VALUES (Parent_Party_ID,
Cust_Account_ID,
Party_ID,
Account_Number)
WHEN (Parent_Party_ID IS NULL)
THEN INTO FII_AR_Caccts_Tmp_GT(
Parent_Party_ID,
Cust_Account_ID,
Account_Party_ID,
Account_Number)
VALUES (Party_ID,
Cust_Account_ID,
Party_ID,
Account_Number)
WHEN (Parent_Party_ID IS NULL AND SRLID = 1)
THEN INTO FII_AR_Cust_Hier_Tmp_GT(
Parent_Party_ID,
Next_Level_Party_ID,
Child_Party_ID,
Next_Level_Is_Leaf_Flag,
Is_Hierarchical_Flag,
Parent_To_Next_Level,
Next_To_Child_Level)
VALUES (-999,
Party_ID,
Party_ID,
'Y',
DECODE(g_hierarchy_type, NULL, 'N', 'Y'),
1,
0)
WHEN (Parent_Party_ID IS NULL AND SRLID = 1)
THEN INTO FII_AR_Cust_Hier_Tmp_GT(
Parent_Party_ID,
Next_Level_Party_ID,
Child_Party_ID,
Next_Level_Is_Leaf_Flag,
Is_Hierarchical_Flag,
Parent_To_Next_Level,
Next_To_Child_Level)
VALUES (Party_ID,
Party_ID,
Party_ID,
'Y',
DECODE(g_hierarchy_type, NULL, 'N', 'Y'),
0,
0)
SELECT Hier.Parent_ID Parent_Party_ID,
CA.Party_ID Party_ID,
CA.Cust_Account_ID,
CA.Account_Number,
ROW_NUMBER () OVER (
PARTITION BY CA.Party_ID
ORDER BY CA.Party_ID NULLS LAST) SRLID
FROM FII_AR_Caccts_Delta_GT CA,
(SELECT Parent_ID, Child_ID
FROM HZ_Hierarchy_Nodes
WHERE Hierarchy_Type = g_hierarchy_type
AND g_sysdate BETWEEN Effective_Start_Date AND Effective_End_Date) Hier
WHERE CA.Party_ID = Hier.Child_ID (+);
FII_UTIL.put_line('Inserted ' || SQL%ROWCOUNT || ' records into FII_AR_Cust_Hier_Tmp_GT and FII_AR_CAccts_Tmp_GT.');
USING (SELECT DISTINCT Top_Node_ID, Source_Node_ID, Level_Number
FROM FII_AR_Top_To_Source_GT) GT
ON (CH.Parent_Party_ID = -999 AND
CH.Child_Party_ID = GT.Source_Node_ID)
WHEN MATCHED THEN
UPDATE SET CH.Next_Level_Party_ID = GT.Top_Node_ID,
CH.Next_Level_Is_Leaf_Flag = 'N',
CH.Next_To_Child_Level = GT.Level_Number,
CH.Last_Update_Date = sysdate,
CH.Last_Updated_By = g_fii_user_id,
CH.Last_Update_Login = g_fii_login_id
WHEN NOT MATCHED THEN
INSERT (CH.Parent_Party_ID,
CH.Next_Level_Party_ID,
CH.Child_Party_ID,
CH.Next_Level_Is_Leaf_Flag,
CH.Is_Hierarchical_Flag,
CH.Parent_To_Next_Level,
CH.Next_To_Child_Level,
CH.Creation_Date,
CH.Created_By,
CH.Last_Update_Date,
CH.Last_Updated_By,
CH.Last_Update_Login)
VALUES (-999,
GT.Top_Node_ID,
GT.Source_Node_ID,
'N',
DECODE(g_hierarchy_type, NULL, 'N', 'Y'),
1,
GT.Level_Number,
sysdate,
g_fii_user_id,
sysdate,
g_fii_user_id,
g_fii_login_id);
SELECT *
BULK COLLECT INTO FII_Cust_Hier_New_MS
FROM FII_AR_Cust_Hier_Tmp_GT CH
ORDER BY Parent_Party_ID, Next_Level_Party_ID, Child_Party_ID;
FII_UTIL.put_line('Inserted ' || FII_Cust_Hier_New_MS.COUNT || ' records into FII_Cust_Hier_New_MS.');
SELECT *
BULK COLLECT INTO FII_CAcct_Denorm_New_MS
FROM FII_AR_CAccts_Tmp_GT CH
ORDER BY Parent_Party_ID, Cust_Account_ID;
FII_UTIL.put_line('Inserted ' || FII_CAcct_Denorm_New_MS.COUNT || ' records into FII_CAcct_Denorm_New_MS.');
SELECT *
BULK COLLECT INTO FII_Cust_Hier_Old_MS
FROM FII_Customer_Hierarchies CH
WHERE EXISTS (SELECT 1 FROM FII_AR_Parties_Delta_GT Log
WHERE Log.Party_ID = CH.Child_Party_ID
AND ((Log.Type_ID = 1 AND CH.Parent_Party_ID <> -999)
OR (Log.Type_ID = 2
AND CH.Parent_To_Next_Level + CH.Next_To_Child_Level > Log.Level_Number)
OR (Log.Type_ID = 3
AND (CH.Parent_To_Next_Level + CH.Next_To_Child_Level = 0))
OR (Log.Type_ID = 4
AND (CH.Parent_To_Next_Level + CH.Next_To_Child_Level = 0
OR CH.Parent_Party_ID = -999))
OR Log.Type_ID = 5))
ORDER BY Parent_Party_ID, Next_Level_Party_ID, Child_Party_ID;
FII_UTIL.put_line('Inserted ' || FII_Cust_Hier_Old_MS.COUNT || ' records into FII_Cust_Hier_Old_MS.');
SELECT *
BULK COLLECT INTO FII_CAcct_Denorm_Old_MS
FROM FII_Cust_Accounts CAD
WHERE EXISTS (SELECT 1 FROM FII_AR_Caccts_Delta_GT Log
WHERE Log.Cust_Account_ID = CAD.Cust_Account_ID)
ORDER BY Parent_Party_ID, Cust_Account_ID;
FII_UTIL.put_line('Inserted ' || FII_CAcct_Denorm_Old_MS.COUNT || ' records into FII_CAcct_Denorm_Old_MS.');
l_i_cad.Last_Update_Date := sysdate;
l_i_cad.Last_Updated_By := g_fii_user_id;
l_i_cad.Last_Update_Login := g_fii_login_id;
l_i_cad.Last_Update_Date := sysdate;
l_i_cad.Last_Updated_By := g_fii_user_id;
l_i_cad.Last_Update_Login := g_fii_login_id;
l_i_cad.Last_Update_Date := sysdate;
l_i_cad.Last_Updated_By := g_fii_user_id;
l_i_cad.Last_Update_Login := g_fii_login_id;
g_state := 'Bulk inserting into FII_AR_Cust_Hier_UI_GT from FII_Cust_Hier_UI_MS.';
INSERT INTO FII_AR_Cust_Hier_UI_GT VALUES FII_Cust_Hier_UI_MS(i);
g_state := 'Bulk inserting into FII_AR_Cust_Hier_D_GT from FII_Cust_Hier_D_MS.';
INSERT INTO FII_AR_Cust_Hier_D_GT VALUES FII_Cust_Hier_D_MS(i);
g_state := 'Bulk inserting into FII_AR_CAccts_D_GT from FII_CAcct_Denorm_D_MS.';
INSERT INTO FII_AR_CAccts_D_GT VALUES FII_CAcct_Denorm_D_MS(i);
DELETE FROM FII_Customer_Hierarchies CH
WHERE EXISTS (SELECT 1
FROM FII_AR_Cust_Hier_D_GT D
WHERE D.Parent_Party_ID = CH.Parent_Party_ID
AND D.Next_Level_Party_ID = CH.Next_Level_Party_ID
AND D.Child_Party_ID = CH.Child_Party_ID);
FII_UTIL.put_line('Deleted ' || SQL%ROWCOUNT || ' records from FII_Customer_Hierarchies.');
UPDATE SET CH.Next_Level_Party_ID = UI.Next_Level_Party_ID,
CH.Next_Level_Is_Leaf_Flag = UI.Next_Level_Is_Leaf_Flag,
CH.Parent_To_Next_Level = UI.Parent_To_Next_Level,
CH.Next_To_Child_Level = UI.Next_To_Child_Level,
CH.Last_Update_Date = sysdate,
CH.Last_Updated_By = g_fii_user_id,
CH.Last_Update_Login = g_fii_login_id
WHEN NOT MATCHED THEN
INSERT (CH.Parent_Party_ID,
CH.Next_Level_Party_ID,
CH.Child_Party_ID,
CH.Next_Level_Is_Leaf_Flag,
CH.Is_Hierarchical_Flag,
CH.Parent_To_Next_Level,
CH.Next_To_Child_Level,
CH.Creation_Date,
CH.Created_By,
CH.Last_Update_Date,
CH.Last_Updated_By,
CH.Last_Update_Login)
VALUES (UI.Parent_Party_ID,
UI.Next_Level_Party_ID,
UI.Child_Party_ID,
UI.Next_Level_Is_Leaf_Flag,
UI.Is_Hierarchical_Flag,
UI.Parent_To_Next_Level,
UI.Next_To_Child_Level,
sysdate,
g_fii_user_id,
sysdate,
g_fii_user_id,
g_fii_login_id);
DELETE FROM FII_Cust_Accounts CAD
WHERE EXISTS (SELECT 1
FROM FII_AR_CAccts_D_GT D
WHERE D.Parent_Party_ID = CAD.Parent_Party_ID
AND D.Cust_Account_ID = CAD.Cust_Account_ID);
FII_UTIL.put_line('Deleted ' || SQL%ROWCOUNT || ' records from FII_Cust_Accounts.');
g_state := 'Bulk inserting into FII_Cust_Accounts from FII_CAcct_Denorm_I_MS.';
INSERT INTO FII_Cust_Accounts VALUES FII_CAcct_Denorm_I_MS(i);
END INCRE_UPDATE;