[Home] [Help]
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
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
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;
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
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
683: SELECT Parent_ID,
684: Child_ID,
685: Level Level_Number,
686: ROW_NUMBER () OVER (ORDER BY Level DESC) SRLID
687: FROM (SELECT * FROM HZ_Hierarchy_Nodes
688: WHERE Level_Number = 1
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
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:
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;
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
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
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
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: