[Home] [Help]
699: FII_UTIL.put_line('');
700: end if;
701:
702:
703: g_state := 'Deleting records from the FII_AP_HOLD_HIST_B table';
704: if g_debug_flag = 'Y' then
705: FII_UTIL.put_line('');
706: FII_UTIL.put_line(g_state);
707: end if;
705: FII_UTIL.put_line('');
706: FII_UTIL.put_line(g_state);
707: end if;
708:
709: DELETE FROM FII_AP_HOLD_HIST_B
710: WHERE Invoice_ID IN (SELECT Key_Value1_Num
711: FROM FII_AP_DBI_LOG_T
712: WHERE Table_Name = 'AP_INVOICES'
713: AND Operation_Flag = 'D');
712: WHERE Table_Name = 'AP_INVOICES'
713: AND Operation_Flag = 'D');
714:
715: if g_debug_flag = 'Y' then
716: FII_UTIL.put_line('Deleted '|| SQL%ROWCOUNT ||' records from the FII_AP_HOLD_HIST_B');
717: FII_UTIL.put_line('');
718: end if;
719:
720:
881: FII_UTIL.put_line('');
882: FII_UTIL.put_line(g_state);
883: end if;
884:
885: g_state := 'Deleting records from FII_AP_HOLD_HIST_B that are already existing';
886: if g_debug_flag = 'Y' then
887: FII_UTIL.put_line('');
888: FII_UTIL.put_line(g_state);
889: end if;
891:
892: /* Deleting the records from the Hold History table. This will ensure
893: that the records will not be duplicated in the History table */
894:
895: DELETE FROM FII_AP_Hold_Hist_B
896: WHERE Invoice_ID IN (SELECT LOG.Key_Value1_Num
897: FROM FII_AP_DBI_LOG_T LOG
898: WHERE Table_Name = 'AP_HOLDS');
899:
896: WHERE Invoice_ID IN (SELECT LOG.Key_Value1_Num
897: FROM FII_AP_DBI_LOG_T LOG
898: WHERE Table_Name = 'AP_HOLDS');
899:
900: DELETE /*+ index(B) push_subq */ FROM FII_AP_Hold_Hist_B
901: WHERE Invoice_ID IN (SELECT /*+ cardinality(LOG,1) */ Key_Value1_Num
902: FROM FII_AP_DBI_LOG_T LOG, FII_AP_Invoice_B AI
903: WHERE LOG.Key_Value1_Num = AI.Invoice_ID
904: AND AI.Cancel_Date IS NOT NULL
905: AND LOG.Table_Name = 'AP_INVOICES');
906:
907:
908:
909: g_state := 'Populating FII_AP_HOLD_HIST_B FROM AP_HOLDS_ALL table';
910: if g_debug_flag = 'Y' then
911: FII_UTIL.put_line(g_state);
912: end if;
913:
924: with hold date between the first hold and release dates */
925:
926: /* Made changes for bug # 3212761 changed query for inserting Rec_type 'R' and 'H'*/
927:
928: INSERT INTO FII_AP_Hold_Hist_B HH
929: (Time_ID,
930: Period_Type_ID,
931: Org_ID,
932: Supplier_ID,
943: 1,
944: Org_ID,
945: Supplier_ID,
946: Invoice_ID,
947: DECODE(Rec_Type, 'H', FII_AP_HOLD_HIST_B_S.NEXTVAL, NULL),
948: rec_type,
949: H_R_Date,
950: g_fii_user_id Created_By,
951: sysdate Creation_Date,
991: AND ah2.release_lookup_code IS NULL))));
992:
993:
994: if g_debug_flag = 'Y' then
995: FII_UTIL.put_line('Inserted '|| SQL%ROWCOUNT ||' Hold and Release records into FII_AP_HOLD_HIST_B');
996: FII_UTIL.put_line('');
997: FII_UTIL.stop_timer;
998: FII_UTIL.print_timer('Duration');
999: end if;
1004: FII_UTIL.start_timer;
1005: FII_UTIL.put_line('');
1006: end if;
1007:
1008: UPDATE FII_AP_Hold_Hist_B HH
1009: SET Seq_ID = (SELECT HH1.Seq_ID
1010: FROM FII_AP_Hold_Hist_B HH1
1011: WHERE HH1.Action = 'H'
1012: AND HH1.Invoice_ID = HH.Invoice_ID
1006: end if;
1007:
1008: UPDATE FII_AP_Hold_Hist_B HH
1009: SET Seq_ID = (SELECT HH1.Seq_ID
1010: FROM FII_AP_Hold_Hist_B HH1
1011: WHERE HH1.Action = 'H'
1012: AND HH1.Invoice_ID = HH.Invoice_ID
1013: AND HH1.Period_Type_ID = 1
1014: AND HH1.Action_Date IN
1026: AND HH.Period_Type_ID = 1
1027: AND HH.Seq_ID IS NULL;
1028:
1029: if g_debug_flag = 'Y' then
1030: FII_UTIL.put_line('Updated '|| SQL%ROWCOUNT ||' Release records in the FII_AP_HOLD_HIST_B');
1031: FII_UTIL.put_line('');
1032: FII_UTIL.stop_timer;
1033: FII_UTIL.print_timer('Duration');
1034: end if;
1040: FII_UTIL.start_timer;
1041: FII_UTIL.put_line('');
1042: end if;
1043: /*
1044: UPDATE FII_AP_Hold_Hist_B HH
1045: SET Hold_Count = (SELECT DECODE(HH.Action,'H', COUNT(*), -1 * COUNT(*))
1046: FROM AP_Holds_ALL AH
1047: WHERE AH.Invoice_ID = HH.Invoice_ID
1048: AND (EXISTS (SELECT 'Hold Exists'
1045: SET Hold_Count = (SELECT DECODE(HH.Action,'H', COUNT(*), -1 * COUNT(*))
1046: FROM AP_Holds_ALL AH
1047: WHERE AH.Invoice_ID = HH.Invoice_ID
1048: AND (EXISTS (SELECT 'Hold Exists'
1049: FROM FII_AP_Hold_Hist_B HH1
1050: WHERE HH1.Invoice_ID = AH.Invoice_ID
1051: AND HH1.Seq_ID = HH.Seq_ID
1052: AND HH1.Period_Type_ID = 1
1053: AND TRUNC(AH.Hold_Date) >= DECODE(HH.Action,'H',
1057: DECODE(HH.Action,'H',HH1.Action_Date,
1058: HH.Action_Date)
1059: AND HH1.Rowid <> HH.Rowid)
1060: OR NOT EXISTS (SELECT 'Release Exists'
1061: FROM FII_AP_Hold_Hist_B HH2
1062: WHERE HH2.Invoice_ID = AH.Invoice_ID
1063: AND HH.Seq_ID = HH2.Seq_ID
1064: AND HH.Period_Type_ID = 1
1065: AND HH2.Rowid <> HH.Rowid)))
1066: WHERE HH.Hold_Count IS NULL
1067: AND HH.Period_Type_ID = 1;
1068:
1069: */
1070: UPDATE FII_AP_Hold_Hist_B HH
1071: SET Supplier_ID = (SELECT AI.Vendor_ID
1072: FROM AP_Invoices_ALL AI
1073: WHERE AI.Invoice_ID = HH.Invoice_ID)
1074: WHERE HH.Invoice_ID IN (SELECT Key_Value1_Num
1077: AND Operation_Flag = 'U');
1078:
1079:
1080: if g_debug_flag = 'Y' then
1081: FII_UTIL.put_line('Updated '|| SQL%ROWCOUNT ||' Hold Counts in the FII_AP_HOLD_HIST_B');
1082: FII_UTIL.put_line('');
1083: FII_UTIL.stop_timer;
1084: FII_UTIL.print_timer('Duration');
1085: end if;