[Home] [Help]
764: WHERE Log.Key_Value1_Num = AI.Invoice_ID
765: AND AI.Cancel_Date IS NOT NULL
766: AND Log.Table_Name = 'AP_INVOICES');
767:
768: g_state := 'Populating FII_AP_INV_HOLDS_B FROM AP_HOLDS_ALL table';
769: if g_debug_flag = 'Y' then
770: FII_UTIL.put_line(g_state);
771: end if;
772:
827: FROM (SELECT /*+ no_merge */ distinct Key_Value1_Num
828: FROM FII_AP_DBI_LOG_T
829: WHERE Table_Name = 'AP_HOLDS'
830: AND Operation_Flag IN ('I','U')) T,
831: AP_Holds_All AH,
832: FII_AP_Invoice_B AI
833: WHERE AH.Invoice_ID = AI.Invoice_ID
834: AND AI.Cancel_Date IS NULL
835: AND AI.Invoice_Type NOT IN ('PREPAYMENT')
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:
917: FII_UTIL.start_timer;
918: FII_UTIL.put_line('');
919: end if;
920:
921: /* If in the AP_HOLDS_ALL table there are overlapping holds then we
922: will only select the first hold and insert into the hold history table.
923: The subquery in the select statement checks if any overlapping holds exist
924: with hold date between the first hold and release dates */
925:
961: FROM (SELECT /*+ no_merge index(lt) */ distinct Key_Value1_Num
962: FROM FII_AP_DBI_LOG_T lt
963: WHERE Table_Name = 'AP_HOLDS'
964: AND Operation_Flag IN ('I','U')) LOG,
965: AP_HOLDS_ALL AH,
966: FII_AP_INVOICE_B AI,
967: (SELECT 'H' Rec_Type FROM DUAL WHERE dummy IS NOT NULL
968: UNION ALL select 'R' Rec_Type FROM DUAL WHERE dummy IS NOT NULL) RT
969: WHERE AH.Invoice_ID = LOG.Key_Value1_Num
971: AND AI.Cancel_Date IS NULL
972: AND AI.Invoice_Type NOT IN ('PREPAYMENT')
973: AND ((RT.Rec_Type = 'H'
974: AND ah.hold_date IN (SELECT min(ah1.hold_date)
975: FROM ap_holds_all ah1
976: WHERE ah1.invoice_id = ah.invoice_id
977: AND trunc(ah1.hold_date) <= decode(ah.release_lookup_code, NULL, sysdate, ah.last_update_date)
978: AND trunc(ah.hold_date) <= decode(ah1.release_lookup_code, NULL, sysdate, ah1.last_update_date)))
979: OR
979: OR
980: (RT.Rec_Type = 'R'
981: AND AH.Release_Lookup_Code IS NOT NULL
982: AND AH.Last_Update_Date IN (SELECT max(ah1.last_update_date)
983: FROM AP_HOLDS_ALL AH1
984: WHERE AH.invoice_id=AH1.invoice_id
985: AND trunc(ah1.hold_date)<=trunc(ah.last_update_date)
986: AND trunc(ah.hold_date)<=decode(AH1.release_lookup_code,NULL,g_sysdate, trunc(AH1.last_update_date)))
987: AND NOT EXISTS (SELECT 'Unrelease holds'
984: WHERE AH.invoice_id=AH1.invoice_id
985: AND trunc(ah1.hold_date)<=trunc(ah.last_update_date)
986: AND trunc(ah.hold_date)<=decode(AH1.release_lookup_code,NULL,g_sysdate, trunc(AH1.last_update_date)))
987: AND NOT EXISTS (SELECT 'Unrelease holds'
988: FROM AP_HOLDS_ALL AH2
989: WHERE AH2.invoice_id=AH.invoice_id
990: AND trunc(AH2.hold_date)<=trunc(AH.last_update_date)
991: AND ah2.release_lookup_code IS NULL))));
992:
1012: AND HH1.Invoice_ID = HH.Invoice_ID
1013: AND HH1.Period_Type_ID = 1
1014: AND HH1.Action_Date IN
1015: (SELECT MIN(TRUNC(AH1.Hold_Date))
1016: FROM AP_Holds_ALL AH1, AP_Holds_ALL AH2
1017: WHERE AH1.Invoice_ID = HH1.Invoice_ID
1018: AND AH2.Invoice_ID = HH1.Invoice_ID
1019: AND TRUNC(AH2.Last_Update_Date) = HH.Action_Date
1020: AND AH2.Release_Lookup_Code IS NOT NULL
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'
1049: FROM FII_AP_Hold_Hist_B HH1
1050: WHERE HH1.Invoice_ID = AH.Invoice_ID