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