[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
FII_AP_Pay_Sched_UI_MS Pay_Sched_B_Type; --Stores records that have been updated or inserted.
FII_AP_Pay_Sched_D_MS Pay_Sched_D_Type; --Stores records that have been deleted.
FII_AP_Invoice_UI_MS Invoice_B_Type; --Stores records that have been updated or inserted.
FII_AP_Invoice_D_MS Invoice_D_Type; --Stores records that have been deleted.
SELECT DISTINCT Functional_Currency From_Currency,
decode(prim_conversion_rate,-3, to_date('01/01/1999','MM/DD/RRRR'),
LEAST(TRX_DATE,sysdate)) Trx_Date
FROM FII_AP_PS_RATES_TEMP RATES
WHERE RATES.Prim_Conversion_Rate < 0 ;
SELECT DISTINCT FUNCTIONAL_CURRENCY From_Currency,
decode(sec_conversion_rate,-3, to_date('01/01/1999','MM/DD/RRRR'),
least(TRX_DATE,sysdate)) Trx_Date
FROM FII_AP_PS_RATES_TEMP RATES
WHERE RATES.Sec_Conversion_Rate < 0 ;
SELECT DISTINCT From_Currency,
To_Currency,
decode(conversion_rate,-3, to_date('01/01/1999','MM/DD/RRRR'),
least(TRX_DATE,sysdate)) Trx_Date,
Conversion_Type
FROM FII_AP_FUNC_RATES_TEMP RATES
WHERE RATES.Conversion_Rate < 0 ;
SELECT COUNT(*)
INTO l_miss_rates_prim
FROM FII_AP_PS_RATES_TEMP RATES
WHERE RATES.Prim_Conversion_Rate < 0;
SELECT COUNT(*)
INTO l_miss_rates_sec
FROM FII_AP_PS_RATES_TEMP RATES
WHERE RATES.Sec_Conversion_Rate < 0;
SELECT COUNT(*)
INTO l_miss_rates_func
FROM FII_AP_FUNC_RATES_TEMP RATES
WHERE RATES.Conversion_Rate < 0;
PROCEDURE Insert_Rates IS
BEGIN
INSERT INTO FII_AP_RATES_GT(
Trx_Currency,
Func_Currency,
Exchange_Date,
Exchange_Rate_Type,
Exchange_Rate,
Functional_MAU,
Invoice_Date)
SELECT AI.Payment_Currency_Code Trx_Currency,
ASP.Base_Currency_Code Func_Currency,
TRUNC(NVL(AI.Exchange_Date, AI.Invoice_Date)) Exchange_Date,
NVL(AI.Exchange_Rate_Type,'No Rate Type') Exchange_Rate_Type,
AI.Exchange_Rate Exchange_Rate,
NVL(FC.Minimum_Accountable_Unit, 0.01) Functional_MAU,
TRUNC(AI.Invoice_Date) Invoice_Date
FROM FII_AP_Invoice_IDS ID,
AP_Invoices_All AI,
AP_System_Parameters_All ASP,
FND_Currencies FC
WHERE ID.Invoice_ID = AI.Invoice_ID
AND ID.Get_Rate_Flag = 'Y'
AND AI.Org_ID = ASP.Org_ID
AND AI.Set_Of_Books_ID = ASP.Set_Of_Books_ID
AND AI.Invoice_Type_Lookup_Code <> 'EXPENSE REPORT'
AND (AI.Invoice_Amount <> 0 OR (AI.Invoice_Amount = 0 AND AI.Cancelled_Date IS NOT NULL))
AND TRUNC(AI.Creation_Date) >= g_start_date
AND ASP.Base_Currency_Code = FC.Currency_Code;
INSERT INTO FII_AP_PS_RATES_TEMP
(Functional_Currency,
Trx_Date,
Prim_Conversion_Rate,
Sec_Conversion_Rate)
SELECT Functional_Currency,
Trx_Date,
DECODE(Functional_Currency, g_prim_currency, 1,
FII_CURRENCY.GET_GLOBAL_RATE_PRIMARY(Functional_Currency,
least(Trx_Date,sysdate))) PRIM_CONVERSION_RATE,
DECODE(Functional_Currency, g_sec_currency, 1,
FII_CURRENCY.GET_GLOBAL_RATE_SECONDARY(Functional_Currency,
least(Trx_Date,sysdate))) SEC_CONVERSION_RATE
FROM (SELECT /*+ no_merge */ DISTINCT
Func_Currency Functional_Currency,
Invoice_Date Trx_Date
FROM FII_AP_RATES_GT);
INSERT INTO FII_AP_FUNC_RATES_TEMP
(From_Currency,
To_Currency,
Trx_Date,
Conversion_Type,
Conversion_Rate,
Functional_MAU)
SELECT From_Currency,
To_Currency,
Trx_Date,
Exchange_Rate_Type,
DECODE(Exchange_Rate_Type, 'User', Exchange_Rate, 'No Rate Type', 1,
DECODE(From_Currency, To_Currency, 1,
FII_CURRENCY.get_rate(From_Currency, To_Currency,
least(Trx_Date,sysdate), Exchange_Rate_Type)))
Conversion_Rate,
Functional_MAU
FROM (SELECT /*+ no_merge */ DISTINCT
Trx_Currency From_Currency,
Func_Currency To_Currency,
Exchange_Date Trx_Date,
Exchange_Rate_Type,
DECODE(Exchange_Rate_Type, 'User', Exchange_Rate, null) Exchange_Rate,
Functional_MAU
FROM FII_AP_RATES_GT);
END Insert_Rates;
PROCEDURE DELETE_SUMMARY IS
BEGIN
g_state := 'Inside the procedure DELETE_SUMMARY';
DELETE FROM FII_AP_AGING_BKTS_B
WHERE Invoice_ID IN (SELECT Key_Value1_Num
FROM FII_AP_DBI_LOG_T
WHERE Table_Name = 'AP_INVOICES'
AND Operation_Flag = 'D');
FII_UTIL.put_line('Deleted '|| SQL%ROWCOUNT ||' records from the FII_AP_AGING_BKTS_B');
DELETE FROM FII_AP_DUE_COUNTS_B
WHERE Invoice_ID IN (SELECT Key_Value1_Num
FROM FII_AP_DBI_LOG_T
WHERE Table_Name = 'AP_INVOICES'
AND Operation_Flag = 'D');
FII_UTIL.put_line('Deleted '|| SQL%ROWCOUNT ||' records from the FII_AP_DUE_COUNTS_B');
DELETE FROM FII_AP_INV_HOLDS_B
WHERE Invoice_ID IN (SELECT Key_Value1_Num
FROM FII_AP_DBI_LOG_T
WHERE Table_Name = 'AP_INVOICES'
AND Operation_Flag = 'D');
FII_UTIL.put_line('Deleted '|| SQL%ROWCOUNT ||' records from the FII_AP_INV_HOLDS_B');
DELETE FROM FII_AP_HOLD_HIST_B
WHERE Invoice_ID IN (SELECT Key_Value1_Num
FROM FII_AP_DBI_LOG_T
WHERE Table_Name = 'AP_INVOICES'
AND Operation_Flag = 'D');
FII_UTIL.put_line('Deleted '|| SQL%ROWCOUNT ||' records from the FII_AP_HOLD_HIST_B');
END DELETE_SUMMARY;
DELETE FROM FII_AP_INV_HOLDS_B
WHERE Invoice_ID IN (SELECT /*+ cardinality(T,100) */ Key_Value1_Num
FROM FII_AP_DBI_LOG_T T
WHERE Table_Name = 'AP_HOLDS');
DELETE /*+ index(B) push_subq */ FROM FII_AP_INV_HOLDS_B B
WHERE Invoice_ID IN (SELECT /*+ cardinality(Log,1) */ Key_Value1_Num
FROM FII_AP_DBI_LOG_T Log, FII_AP_Invoice_B AI
WHERE Log.Key_Value1_Num = AI.Invoice_ID
AND AI.Cancel_Date IS NOT NULL
AND Log.Table_Name = 'AP_INVOICES');
INSERT INTO FII_AP_INV_HOLDS_B
(Time_ID,
Period_Type_ID,
Org_ID,
Supplier_ID,
Invoice_ID,
Hold_Date,
Hold_Code,
Held_By,
Hold_Category,
Release_Date,
Released_By,
Created_By,
Creation_Date,
Last_Updated_By,
Last_Update_Date,
Last_Update_Login)
SELECT /*+ ordered use_nl(AH,AI) */ TO_NUMBER(TO_CHAR(AH.Hold_Date,'J')),
1,
AH.Org_ID,
AI.Supplier_ID,
AH.Invoice_ID,
TRUNC(AH.Hold_Date),
AH.Hold_Lookup_Code,
AH.Held_By,
(CASE
WHEN Hold_Lookup_Code IN ('DIST ACCT INVALID', 'ERV ACCT INVALID')
THEN 'ACCOUNT'
WHEN Hold_Lookup_Code IN ('CANT FUNDS CHECK', 'INSUFFICIENT FUNDS')
THEN 'FUNDS'
WHEN Hold_Lookup_Code IN ('AMOUNT', 'AWT ERROR', 'VENDOR',
'NATURAL ACCOUNT TAX')
THEN 'INVOICE'
WHEN Hold_Lookup_Code IN ('CANT CLOSE PO', 'CANT TRY PO CLOSE',
'FINAL MATCHING', 'PO REQUIRED', 'MAX QTY ORD',
'MAX QTY REC', 'MAX RATE AMOUNT', 'MAX SHIP AMOUNT',
'MAX TOTAL AMOUNT', 'PRICE', 'QTY ORD', 'QTY REC',
'QUANTITY', 'REC EXCEPTION', 'TAX DIFFERENCE')
THEN 'PO MATCHING'
WHEN Hold_Lookup_Code IN ('DIST VARIANCE', 'TAX VARIANCE', 'TAX AMOUNT RANGE', 'LINE VARIANCE')
THEN 'VARIANCE'
WHEN Hold_Lookup_Code IN ('NO RATE', 'VENDOR')
THEN 'MISCELLANEOUS'
ELSE 'USER DEFINED'
END) AS Hold_Category,
DECODE(AH.Release_Lookup_Code, Null, Null,
AH.Last_Update_Date),
DECODE(AH.Release_Lookup_Code, Null, Null,
AH.Last_Updated_By),
g_fii_user_id Created_By,
sysdate Creation_Date,
g_fii_user_id Last_Updated_By,
sysdate Last_Update_Date,
g_fii_login_id Last_Update_Login
FROM (SELECT /*+ no_merge */ distinct Key_Value1_Num
FROM FII_AP_DBI_LOG_T
WHERE Table_Name = 'AP_HOLDS'
AND Operation_Flag IN ('I','U')) T,
AP_Holds_All AH,
FII_AP_Invoice_B AI
WHERE AH.Invoice_ID = AI.Invoice_ID
AND AI.Cancel_Date IS NULL
AND AI.Invoice_Type NOT IN ('PREPAYMENT')
AND AH.Invoice_ID = T.Key_Value1_Num;
FII_UTIL.put_line('Inserted ' || SQL%ROWCOUNT || ' records into FII_AP_INV_HOLDS_B');
UPDATE FII_AP_INV_HOLDS_B HSUM
SET Supplier_ID = (SELECT AI.Supplier_ID
FROM FII_AP_Invoice_B AI
WHERE AI.Invoice_ID = HSUM.Invoice_ID)
WHERE HSUM.Invoice_ID IN (SELECT Key_Value1_Num
FROM FII_AP_DBI_LOG_T
WHERE Table_Name = 'AP_INVOICES'
AND Operation_Flag = 'U');
DELETE FROM FII_AP_Hold_Hist_B
WHERE Invoice_ID IN (SELECT LOG.Key_Value1_Num
FROM FII_AP_DBI_LOG_T LOG
WHERE Table_Name = 'AP_HOLDS');
DELETE /*+ index(B) push_subq */ FROM FII_AP_Hold_Hist_B
WHERE Invoice_ID IN (SELECT /*+ cardinality(LOG,1) */ Key_Value1_Num
FROM FII_AP_DBI_LOG_T LOG, FII_AP_Invoice_B AI
WHERE LOG.Key_Value1_Num = AI.Invoice_ID
AND AI.Cancel_Date IS NOT NULL
AND LOG.Table_Name = 'AP_INVOICES');
will only select the first hold and insert into the hold history table.
The subquery in the select statement checks if any overlapping holds exist
with hold date between the first hold and release dates */
/* Made changes for bug # 3212761 changed query for inserting Rec_type 'R' and 'H'*/
INSERT INTO FII_AP_Hold_Hist_B HH
(Time_ID,
Period_Type_ID,
Org_ID,
Supplier_ID,
Invoice_ID,
Seq_ID,
Action,
Action_Date,
Created_By,
Creation_Date,
Last_Updated_By,
Last_Update_Date,
Last_Update_Login)
SELECT TO_NUMBER(TO_CHAR(H_R_Date,'J')),
1,
Org_ID,
Supplier_ID,
Invoice_ID,
DECODE(Rec_Type, 'H', FII_AP_HOLD_HIST_B_S.NEXTVAL, NULL),
rec_type,
H_R_Date,
g_fii_user_id Created_By,
sysdate Creation_Date,
g_fii_user_id Last_Updated_By,
sysdate Last_Update_Date,
g_fii_login_id Last_Update_Login
FROM
(SELECT /*+ NO_EXPAND ordered use_nl(AH,AI) */ DISTINCT AI.Org_ID,
AI.Supplier_ID,
AI.Invoice_ID,
TRUNC(DECODE(RT.Rec_Type, 'H', AH.Hold_Date, AH.Last_Update_Date)) H_R_Date,
RT.Rec_Type
FROM (SELECT /*+ no_merge index(lt) */ distinct Key_Value1_Num
FROM FII_AP_DBI_LOG_T lt
WHERE Table_Name = 'AP_HOLDS'
AND Operation_Flag IN ('I','U')) LOG,
AP_HOLDS_ALL AH,
FII_AP_INVOICE_B AI,
(SELECT 'H' Rec_Type FROM DUAL WHERE dummy IS NOT NULL
UNION ALL select 'R' Rec_Type FROM DUAL WHERE dummy IS NOT NULL) RT
WHERE AH.Invoice_ID = LOG.Key_Value1_Num
AND AI.Invoice_ID = AH.Invoice_ID
AND AI.Cancel_Date IS NULL
AND AI.Invoice_Type NOT IN ('PREPAYMENT')
AND ((RT.Rec_Type = 'H'
AND ah.hold_date IN (SELECT min(ah1.hold_date)
FROM ap_holds_all ah1
WHERE ah1.invoice_id = ah.invoice_id
AND trunc(ah1.hold_date) <= decode(ah.release_lookup_code, NULL, sysdate, ah.last_update_date)
AND trunc(ah.hold_date) <= decode(ah1.release_lookup_code, NULL, sysdate, ah1.last_update_date)))
OR
(RT.Rec_Type = 'R'
AND AH.Release_Lookup_Code IS NOT NULL
AND AH.Last_Update_Date IN (SELECT max(ah1.last_update_date)
FROM AP_HOLDS_ALL AH1
WHERE AH.invoice_id=AH1.invoice_id
AND trunc(ah1.hold_date)<=trunc(ah.last_update_date)
AND trunc(ah.hold_date)<=decode(AH1.release_lookup_code,NULL,g_sysdate, trunc(AH1.last_update_date)))
AND NOT EXISTS (SELECT 'Unrelease holds'
FROM AP_HOLDS_ALL AH2
WHERE AH2.invoice_id=AH.invoice_id
AND trunc(AH2.hold_date)<=trunc(AH.last_update_date)
AND ah2.release_lookup_code IS NULL))));
FII_UTIL.put_line('Inserted '|| SQL%ROWCOUNT ||' Hold and Release records into FII_AP_HOLD_HIST_B');
UPDATE FII_AP_Hold_Hist_B HH
SET Seq_ID = (SELECT HH1.Seq_ID
FROM FII_AP_Hold_Hist_B HH1
WHERE HH1.Action = 'H'
AND HH1.Invoice_ID = HH.Invoice_ID
AND HH1.Period_Type_ID = 1
AND HH1.Action_Date IN
(SELECT MIN(TRUNC(AH1.Hold_Date))
FROM AP_Holds_ALL AH1, AP_Holds_ALL AH2
WHERE AH1.Invoice_ID = HH1.Invoice_ID
AND AH2.Invoice_ID = HH1.Invoice_ID
AND TRUNC(AH2.Last_Update_Date) = HH.Action_Date
AND AH2.Release_Lookup_Code IS NOT NULL
AND TRUNC(AH1.Last_Update_Date) >= TRUNC(AH2.Hold_Date)
AND AH1.Release_Lookup_Code IS NOT NULL
AND TRUNC(AH1.Last_Update_Date)
<= TRUNC(AH2.Last_Update_Date)))
WHERE HH.Action = 'R'
AND HH.Period_Type_ID = 1
AND HH.Seq_ID IS NULL;
FII_UTIL.put_line('Updated '|| SQL%ROWCOUNT ||' Release records in the FII_AP_HOLD_HIST_B');
UPDATE FII_AP_Hold_Hist_B HH
SET Hold_Count = (SELECT DECODE(HH.Action,'H', COUNT(*), -1 * COUNT(*))
FROM AP_Holds_ALL AH
WHERE AH.Invoice_ID = HH.Invoice_ID
AND (EXISTS (SELECT 'Hold Exists'
FROM FII_AP_Hold_Hist_B HH1
WHERE HH1.Invoice_ID = AH.Invoice_ID
AND HH1.Seq_ID = HH.Seq_ID
AND HH1.Period_Type_ID = 1
AND TRUNC(AH.Hold_Date) >= DECODE(HH.Action,'H',
HH.Action_Date, HH1.Action_Date)
AND AH.Release_Lookup_Code IS NOT NULL
AND TRUNC(AH.Last_Update_Date) <=
DECODE(HH.Action,'H',HH1.Action_Date,
HH.Action_Date)
AND HH1.Rowid <> HH.Rowid)
OR NOT EXISTS (SELECT 'Release Exists'
FROM FII_AP_Hold_Hist_B HH2
WHERE HH2.Invoice_ID = AH.Invoice_ID
AND HH.Seq_ID = HH2.Seq_ID
AND HH.Period_Type_ID = 1
AND HH2.Rowid <> HH.Rowid)))
WHERE HH.Hold_Count IS NULL
AND HH.Period_Type_ID = 1;
UPDATE FII_AP_Hold_Hist_B HH
SET Supplier_ID = (SELECT AI.Vendor_ID
FROM AP_Invoices_ALL AI
WHERE AI.Invoice_ID = HH.Invoice_ID)
WHERE HH.Invoice_ID IN (SELECT Key_Value1_Num
FROM FII_AP_DBI_LOG_T
WHERE Table_Name = 'AP_INVOICES'
AND Operation_Flag = 'U');
FII_UTIL.put_line('Updated '|| SQL%ROWCOUNT ||' Hold Counts in the FII_AP_HOLD_HIST_B');
PROCEDURE INSERT_DELETED_REC(Invoice_ID NUMBER, Payment_Num NUMBER) IS
Deleted_Pay_Sched Pay_Sched_D_Rec;
Deleted_Invoice Invoice_D_Rec;
g_state := 'Inside INSERT_DELETED_REC Procedure.';
Deleted_Pay_Sched.Invoice_ID := FII_AP_Pay_Sched_B_MS(g_pay_sched_b_marker).Invoice_ID;
Deleted_Pay_Sched.Payment_Num := FII_AP_Pay_Sched_B_MS(g_pay_sched_b_marker).Payment_Num;
Deleted_Pay_Sched.Action_Date := FII_AP_Pay_Sched_B_MS(g_pay_sched_b_marker).Action_Date;
Deleted_Pay_Sched.Action := FII_AP_Pay_Sched_B_MS(g_pay_sched_b_marker).Action;
Deleted_Pay_Sched.Inv_Pymt_Flag := FII_AP_Pay_Sched_B_MS(g_pay_sched_b_marker).Inv_Pymt_Flag;
Deleted_Pay_Sched.Unique_ID := FII_AP_Pay_Sched_B_MS(g_pay_sched_b_marker).Unique_ID;
FII_AP_Pay_Sched_D_MS(FII_AP_Pay_Sched_D_MS.Count+1) := Deleted_Pay_Sched;
Deleted_Invoice.Invoice_ID := FII_AP_Invoice_B_MS(g_invoice_b_marker).Invoice_ID;
FII_AP_Invoice_D_MS(FII_AP_Invoice_D_MS.Count+1) := Deleted_Invoice;
Deleted_Pay_Sched.Invoice_ID := FII_AP_Pay_Sched_B_MS(g_pay_sched_b_marker).Invoice_ID;
Deleted_Pay_Sched.Payment_Num := FII_AP_Pay_Sched_B_MS(g_pay_sched_b_marker).Payment_Num;
Deleted_Pay_Sched.Action_Date := FII_AP_Pay_Sched_B_MS(g_pay_sched_b_marker).Action_Date;
Deleted_Pay_Sched.Action := FII_AP_Pay_Sched_B_MS(g_pay_sched_b_marker).Action;
Deleted_Pay_Sched.Inv_Pymt_Flag := FII_AP_Pay_Sched_B_MS(g_pay_sched_b_marker).Inv_Pymt_Flag;
Deleted_Pay_Sched.Unique_ID := FII_AP_Pay_Sched_B_MS(g_pay_sched_b_marker).Unique_ID;
FII_AP_Pay_Sched_D_MS(FII_AP_Pay_Sched_D_MS.Count+1) := Deleted_Pay_Sched;
Deleted_Invoice.Invoice_ID := FII_AP_Invoice_B_MS(g_invoice_b_marker).Invoice_ID;
FII_AP_Invoice_D_MS(FII_AP_Invoice_D_MS.Count+1) := Deleted_Invoice;
FII_UTIL.put_line('Error in procedure Insert_Deleted_Rec.');
END Insert_Deleted_Rec;
PROCEDURE INSERT_PAY_SCHED_B_REC(Pay_Sched_Rec FII_AP_PAY_SCHED_B%ROWTYPE, Update_Only_Flag VARCHAR2) IS
BEGIN
g_state := 'Inside INSERT_PAY_SCHED_B_REC Procedure.';
IF Update_Only_Flag = 'Y' THEN
FII_AP_Pay_Sched_UI_MS(FII_AP_Pay_Sched_UI_MS.Count+1) := Pay_Sched_Rec;
INSERT_DELETED_REC(Pay_Sched_Rec.Invoice_ID, Pay_Sched_Rec.Payment_Num);
ELSE --Pay_Sched_Rec does not exist in previous load, so insert.
FII_AP_Pay_Sched_UI_MS(FII_AP_Pay_Sched_UI_MS.Count+1) := Pay_Sched_Rec;
END IF; --IF Update_Only_Flag = 'Y'
FII_UTIL.put_line('Error in procedure Insert_Pay_Sched_B_Rec.');
END Insert_Pay_Sched_B_Rec;
PROCEDURE INSERT_INVOICE_B_REC(Invoice_Rec FII_AP_Invoice_B%ROWTYPE) IS
BEGIN
g_state := 'Inside INSERT_INVOICE_B_REC Procedure.';
ELSE --Invoice_Rec does not exist in previous load, so insert.
FII_AP_Invoice_UI_MS(FII_AP_Invoice_UI_MS.Count+1) := Invoice_Rec;
FII_UTIL.put_line('Error in procedure Insert_Invoice_B_Rec.');
END Insert_Invoice_B_Rec;
SELECT AI.Org_ID Org_ID,
AI.Vendor_ID Supplier_ID,
AI.Invoice_ID Invoice_ID,
AI.Invoice_Type_Lookup_Code Invoice_Type,
AI.Invoice_Num Invoice_Number,
TRUNC(AI.Invoice_Date) Invoice_Date,
AI.Invoice_Amount Invoice_Amount,
AI.Invoice_Currency_Code Invoice_Currency_Code,
ASP.Base_Currency_Code Base_Currency_Code,
TRUNC(NVL(AI.Exchange_Date, AI.Invoice_Date)) Exchange_Date,
AI.Exchange_Rate Exchange_Rate,
NVL(AI.Exchange_Rate_Type, 'No Rate Type') Exchange_Rate_Type,
TRUNC(AI.Creation_Date) Entered_Date,
AI.Created_By Created_By,
AI.Payment_Currency_Code Payment_Currency_Code,
AI.Payment_Status_Flag Payment_Status_Flag,
AI.Payment_Cross_Rate Payment_Cross_Rate,
AI.Terms_ID Terms_ID,
AI.Source Source,
CASE WHEN g_manual_sources like '%''' || to_char(upper(AI.Source)) || '''%'
THEN 'N' ELSE 'Y' END E_Invoices_Flag,
DECODE(AI.Cancelled_Date, NULL, 'N', 'Y') Cancel_Flag,
AI.Cancelled_Date Cancel_Date,
COUNT(DISTINCT AID.Invoice_Distribution_ID) Dist_Count,
NVL(FC.Minimum_Accountable_Unit, 0.01) Minimum_Accountable_Unit,
FRATES.Functional_MAU Functional_MAU,
FRATES.Conversion_Rate To_Func_Rate,
DECODE(AI.Invoice_Currency_Code, g_prim_currency, 1,
FRATES.Conversion_Rate * RATES.Prim_Conversion_Rate) To_Prim_Rate,
DECODE(AI.Invoice_Currency_Code, g_sec_currency, 1,
FRATES.Conversion_Rate * RATES.Sec_Conversion_Rate) To_Sec_Rate,
ID.Invoice_B_Flag Invoice_B_Flag,
ID.Pay_Sched_B_Flag Pay_Sched_B_Flag
BULK COLLECT INTO FII_AP_Inv_MS
FROM FII_AP_Invoice_IDS ID,
AP_Invoices_All AI,
AP_Invoice_Distributions_All AID,
AP_System_Parameters_All ASP,
FND_Currencies FC,
FII_AP_PS_Rates_Temp RATES,
FII_AP_Func_Rates_Temp FRATES
WHERE ID.Invoice_ID = AI.Invoice_ID
AND AI.Invoice_ID = AID.Invoice_ID (+)
AND AI.Org_ID = ASP.Org_ID
AND AI.Payment_Currency_Code = FC.Currency_Code
AND FRATES.To_Currency = ASP.Base_Currency_Code
AND FRATES.From_Currency = AI.Payment_Currency_Code
AND FRATES.Trx_Date = TRUNC(NVL(AI.Exchange_Date, AI.Invoice_Date))
AND DECODE(NVL(AI.Exchange_Rate_Type, 'No Rate Type'),'User', AI.Exchange_Rate,1) =
DECODE(FRATES.Conversion_Type,'User', FRATES.Conversion_Rate,1)
AND FRATES.Conversion_Type = NVL(AI.Exchange_Rate_Type, 'No Rate Type')
AND RATES.Functional_Currency = ASP.Base_Currency_Code
AND RATES.Trx_Date = TRUNC(AI.Invoice_Date)
AND (ID.Invoice_B_Flag = 'Y' OR ID.Pay_Sched_B_Flag = 'Y')
GROUP BY AI.Org_ID, AI.Vendor_ID, AI.Invoice_ID, AI.Invoice_Type_Lookup_Code, AI.Invoice_Num,
AI.Invoice_Date, AI.Invoice_Amount, AI.Invoice_Currency_Code,
ASP.Base_Currency_Code, AI.Exchange_Date, AI.Exchange_Rate, AI.Exchange_Rate_Type,
AI.Creation_Date, AI.Created_By, AI.Payment_Currency_Code, AI.Payment_Status_Flag,
AI.Payment_Cross_Rate, AI.Terms_ID, AI.Source, AI.Cancelled_Date, FC.Minimum_Accountable_Unit,
FRATES.Functional_MAU, FRATES.Conversion_Rate, RATES.Prim_Conversion_Rate, RATES.Sec_Conversion_Rate,
ID.Invoice_B_Flag, ID.Pay_Sched_B_Flag
ORDER BY AI.Invoice_ID;
SELECT PS.Invoice_ID Invoice_ID,
PS.Payment_Num Payment_Num,
PS.Due_Date Due_Date,
PS.Discount_Date Discount_Date,
PS.Gross_Amount Gross_Amount,
PS.Second_Discount_Date Second_Discount_Date,
PS.Third_Discount_Date Third_Discount_Date,
NVL(PS.Discount_Amount_Available, 0) Discount_Amount_Available,
NVL(PS.Second_Disc_Amt_Available, 0) Second_Disc_Amt_Available,
NVL(PS.Third_Disc_Amt_Available, 0) Third_Disc_Amt_Available,
PS.Created_By Created_By,
NULL Fully_Paid_Date
BULK COLLECT INTO FII_AP_Pay_Sched_MS
FROM FII_AP_Invoice_IDS ID,
AP_Payment_Schedules_All PS
WHERE ID.Invoice_ID = PS.Invoice_ID
AND (ID.Invoice_B_Flag = 'Y' OR ID.Pay_Sched_B_Flag = 'Y')
ORDER BY PS.Invoice_ID, PS.Payment_Num;
SELECT AIP.Amount Amount,
AIP.Check_ID Check_ID,
AIP.Invoice_ID Invoice_ID,
AIP.Invoice_Payment_ID Invoice_Payment_ID,
AIP.Payment_Num Payment_Num,
AIP.Created_By Created_By,
AIP.Creation_Date Creation_Date,
NVL(AIP.Discount_Taken, 0) Discount_Taken,
AC.Check_Date Check_Date,
DECODE(IBY_SYS_PROF_B.Processing_Type,NULL,DECODE(AC.Payment_Method_Lookup_Code, 'EFT', 'E', 'WIRE', 'E', 'M')
,DECODE(IBY_SYS_PROF_B.Processing_Type, 'ELECTRONIC', 'E', 'M')) Processing_Type
BULK COLLECT INTO FII_AP_Inv_Pay_MS
FROM FII_AP_Invoice_IDS ID,
AP_Invoice_Payments_ALL AIP,
AP_Checks_ALL AC,
IBY_SYS_PMT_PROFILES_B IBY_SYS_PROF_B, --IBY CHANGE
IBY_ACCT_PMT_PROFILES_B IBY_ACCT_PROF_B--IBY CHANGE
WHERE ID.Invoice_ID = AIP.Invoice_ID
AND AIP.Check_ID = AC.Check_ID
AND AC.Payment_Profile_ID = IBY_ACCT_PROF_B.Payment_Profile_ID(+)--IBY CHANGE
AND IBY_ACCT_PROF_B.system_profile_code = IBY_SYS_PROF_B.system_profile_code(+)--IBY CHANGE
AND (ID.Invoice_B_Flag = 'Y' OR ID.Pay_Sched_B_Flag = 'Y')
AND AC.Void_Date IS NULL
ORDER BY AIP.Invoice_ID, AIP.Payment_Num, AIP.Creation_Date;
SELECT /*+ USE_NL (ID, AID) */ AID.Invoice_ID Invoice_ID,
AID.Line_Type_Lookup_Code Line_Type_Lookup_Code,
SUM(AID.Amount) Amount,
TRUNC(AID.Creation_Date) Creation_Date,
MAX(AID.Invoice_Distribution_ID) Invoice_Distribution_ID --Any invoice distribution id is ok. Just used to make the record unique.
BULK COLLECT INTO FII_AP_WH_Tax_MS
FROM FII_AP_Invoice_IDS ID,
AP_Invoice_Distributions_ALL AID,
AP_Invoice_Lines_ALL AIL
WHERE ID.Invoice_ID = AID.Invoice_ID
AND AID.Invoice_ID = AIL.Invoice_ID
AND AID.Invoice_Line_Number = AIL.Line_Number
AND (ID.Invoice_B_Flag = 'Y' OR ID.Pay_Sched_B_Flag = 'Y')
AND (AID.Line_Type_Lookup_Code IN ('AWT') OR (AID.Line_Type_Lookup_Code IN ('NONREC_TAX', 'REC_TAX') AND AID.Prepay_Distribution_ID IS NOT NULL))
AND (AIL.Invoice_Includes_Prepay_Flag IS NULL OR AIL.Invoice_Includes_Prepay_Flag = 'N')
--AND AID.Reversal_Flag IS NULL
AND NVL(AID.Reversal_Flag,'N') = 'N'
GROUP BY AID.Invoice_ID, AID.Line_Type_Lookup_Code,
TRUNC(AID.Creation_Date)
ORDER BY AID.Invoice_ID, TRUNC(AID.Creation_Date), AID.Line_Type_Lookup_Code;
SELECT /*+ ORDERED USE_NL(AC) */
TEMP2.Invoice_ID Invoice_ID,
-1 * SUM(TEMP2.Amount) Amount,
TEMP2.Creation_Date Creation_Date,
AC.Check_ID Check_ID,
AC.Check_Date Check_Date,
DECODE(IBY_SYS_PROF_B.Processing_Type,NULL,DECODE(AC.Payment_Method_Lookup_Code, 'EFT', 'E', 'WIRE', 'E', 'M')
,DECODE(IBY_SYS_PROF_B.Processing_Type, 'ELECTRONIC', 'E', 'M')) Processing_Type,
-1 * SUM(TEMP2.Amount) Unallocated_Amount
BULK COLLECT INTO FII_AP_Prepay_Applied_MS
FROM (SELECT /*+ NO_MERGE ORDERED USE_NL(AIP) */
TEMP1.Invoice_ID,
TEMP1.Creation_Date,
TEMP1.Amount,
MIN(AIP.Check_ID) Check_ID
FROM (SELECT /*+ NO_MERGE ORDERED USE_NL(AID, TEMP) */
AID.Invoice_ID,
TRUNC(AID.Creation_Date) Creation_Date,
TEMP.Invoice_ID Prepay_Invoice_ID,
SUM(AID.Amount) Amount
FROM FII_AP_Invoice_IDS ID,
AP_Invoice_Distributions_All AID,
AP_Invoice_Lines_ALL AIL,
AP_Invoice_Distributions_ALL TEMP
WHERE ID.Invoice_ID = AID.Invoice_ID
AND AID.Invoice_ID = AIL.Invoice_ID
AND AID.Invoice_Line_Number = AIL.Line_Number
AND (ID.Invoice_B_Flag = 'Y' OR ID.Pay_Sched_B_Flag = 'Y')
AND AID.Line_Type_Lookup_Code = 'PREPAY'
--AND AID.Reversal_Flag IS NULL
AND NVL(AID.Reversal_Flag,'N') = 'N'
AND (AIL.Invoice_Includes_Prepay_Flag IS NULL OR AIL.Invoice_Includes_Prepay_Flag = 'N')
AND AID.Prepay_Distribution_ID = TEMP.Invoice_Distribution_ID
GROUP BY AID.Invoice_ID, TRUNC(AID.Creation_Date), TEMP.Invoice_ID) TEMP1,
AP_Invoice_Payments_All AIP
WHERE TEMP1.Prepay_Invoice_ID = AIP.Invoice_ID
GROUP BY TEMP1.Invoice_ID, TEMP1.Creation_Date, TEMP1.Prepay_Invoice_ID, TEMP1.Amount) TEMP2,
AP_Checks_All AC,
IBY_SYS_PMT_PROFILES_B IBY_SYS_PROF_B,--IBY CHANGE
IBY_ACCT_PMT_PROFILES_B IBY_ACCT_PROF_B--IBY CHANGE
WHERE TEMP2.Check_ID = AC.Check_ID
AND AC.Payment_Profile_ID = IBY_ACCT_PROF_B.Payment_Profile_ID(+)--IBY CHANGE
AND IBY_ACCT_PROF_B.system_profile_code= IBY_SYS_PROF_B.system_profile_code(+)--IBY CHANGE
GROUP BY TEMP2.Invoice_ID, TEMP2.Creation_Date, AC.Check_ID, AC.Check_Date,
IBY_SYS_PROF_B.Processing_Type,AC.Payment_Method_Lookup_Code
ORDER BY TEMP2.Invoice_ID, TEMP2.Creation_Date, AC.Check_ID;
SELECT /*+ ordered index(PSUM, FII_AP_PAY_SCHED_B_N1) */
PSUM.Time_ID, PSUM.Period_Type_ID, PSUM.Action_Date, PSUM.Action,
PSUM.Update_Sequence, PSUM.Org_ID, PSUM.Supplier_ID, PSUM.Invoice_ID,
PSUM.Base_Currency_Code, PSUM.Trx_Date, PSUM.Payment_Num, PSUM.Due_Date,
PSUM.Amount_Remaining, PSUM.Past_Due_Amount, PSUM.Discount_Available,
PSUM.Discount_Taken, PSUM.Discount_Lost, PSUM.Payment_Amount,
PSUM.On_Time_Payment_Amt, PSUM.Late_Payment_Amt, PSUM.No_Days_Late,
PSUM.Due_Bucket1, PSUM.Due_Bucket2, PSUM.Due_Bucket3, PSUM.Past_Due_Bucket1,
PSUM.Past_Due_Bucket2, PSUM.Past_Due_Bucket3, PSUM.Amount_Remaining_B,
PSUM.Past_Due_Amount_B, PSUM.Discount_Available_B, PSUM.Discount_Taken_B,
PSUM.Discount_Lost_B, PSUM.Payment_Amount_B, PSUM.On_Time_Payment_Amt_B,
PSUM.Late_Payment_Amt_B, PSUM.Due_Bucket1_B, PSUM.Due_Bucket2_B,
PSUM.Due_Bucket3_B, PSUM.Past_Due_Bucket1_B, PSUM.Past_Due_Bucket2_B,
PSUM.Past_Due_Bucket3_B, PSUM.Prim_Amount_Remaining, PSUM.Prim_Past_Due_Amount,
PSUM.Prim_Discount_Available, PSUM.Prim_Discount_Taken, PSUM.Prim_Discount_Lost,
PSUM.Prim_Payment_Amount, PSUM.Prim_On_Time_Payment_Amt,
PSUM.Prim_Late_Payment_Amt, PSUM.Prim_Due_Bucket1, PSUM.Prim_Due_Bucket2,
PSUM.Prim_Due_Bucket3, PSUM.Prim_Past_Due_Bucket1, PSUM.Prim_Past_Due_Bucket2,
PSUM.Prim_Past_Due_Bucket3, PSUM.Sec_Amount_Remaining, PSUM.Sec_Past_Due_Amount,
PSUM.Sec_Discount_Available, PSUM.Sec_Discount_Taken, PSUM.Sec_Discount_Lost,
PSUM.Sec_Payment_Amount, PSUM.Sec_On_Time_Payment_Amt, PSUM.Sec_Late_Payment_Amt,
PSUM.Sec_Due_Bucket1, PSUM.Sec_Due_Bucket2, PSUM.Sec_Due_Bucket3,
PSUM.Sec_Past_Due_Bucket1, PSUM.Sec_Past_Due_Bucket2, PSUM.Sec_Past_Due_Bucket3,
PSUM.Fully_Paid_Date, PSUM.Check_ID, PSUM.Payment_Method, PSUM.Last_Update_Date,
PSUM.Last_Updated_By, PSUM.Creation_Date, PSUM.Created_By, PSUM.Last_Update_Login,
PSUM.Check_Date, PSUM.Inv_Pymt_Flag, PSUM.Unique_ID
BULK COLLECT INTO FII_AP_Pay_Sched_B_MS
FROM FII_AP_Invoice_IDS ID,
FII_AP_Pay_Sched_B PSUM
WHERE ID.Invoice_ID = PSUM.Invoice_ID
AND (ID.Pay_Sched_B_Flag = 'Y' OR ID.Delete_Inv_Flag='Y')
ORDER BY PSUM.Invoice_ID,
PSUM.Payment_Num,
PSUM.Action_Date,
DECODE(PSUM.Action, 'CREATION', 1,
'DISCOUNT', 2,
'DUE BUCKET', 3,
'DUE', 3,
'PAST BUCKET', 3,
'TAX', 4,
'WITHHOLDING', 5,
'PAYMENT', 6,
'PREPAYMENT', 7),
DECODE(PSUM.Inv_Pymt_Flag, NULL, 0, 'N', 1, 'Y', 2, 0),
NVL(PSUM.Unique_ID, 0);
SELECT /*+ ordered index(AI, FII_AP_INVOICE_B_U1) */
AI.Org_ID, AI.Supplier_ID, AI.Invoice_ID, AI.Invoice_Type, AI.Invoice_Number,
AI.Invoice_Date, AI.Invoice_Amount, AI.Base_Amount, AI.Prim_Amount,
AI.Sec_Amount, Ai.Invoice_Currency_Code, AI.Base_Currency_Code, AI.Entered_Date,
AI.Payment_Currency_Code, AI.Fully_Paid_Date, AI.Terms_ID, AI.Source,
AI.E_Invoices_Flag, AI.Cancel_Flag, AI.Cancel_Date, AI.Dist_Count, AI.Due_Date,
AI.Discount_Offered, AI.Discount_Offered_B, AI.Prim_Discount_Offered,
AI.Sec_Discount_Offered, AI.First_Hold_Date, AI.Last_Update_Date,
AI.Last_Updated_By, AI.Creation_Date, AI.Created_By, AI.Last_Update_Login,
AI.Exchange_Date, AI.Exchange_Rate, AI.Exchange_Rate_Type, AI.Payment_Status_Flag,
AI.Payment_Cross_Rate, AI.Fully_Paid_Amount, AI.Fully_Paid_Amount_B,
AI.Prim_Fully_Paid_Amount, AI.Sec_Fully_Paid_Amount
BULK COLLECT INTO FII_AP_Invoice_B_MS
FROM FII_AP_Invoice_IDS ID,
FII_AP_Invoice_B AI
WHERE ID.Invoice_ID = AI.Invoice_ID
AND ID.Invoice_B_Flag = 'Y'
ORDER BY AI.Invoice_ID;
g_state := 'Bulk inserting into FII_AP_Invoice_D_GT from FII_AP_Invoice_D_MS.';
INSERT INTO FII_AP_Invoice_D_GT VALUES FII_AP_Invoice_D_MS(i);
FII_UTIL.put_line('The time taken to bulk insert into FII_AP_Invoice_D_GT is: ' || to_char(l_timestamp1_tmp/100) || ' seconds.');
g_state := 'Bulk inserting into FII_AP_Invoice_UI_GT from FII_AP_Invoice_UI_MS.';
INSERT INTO FII_AP_Invoice_UI_GT VALUES FII_AP_Invoice_UI_MS(i);
FII_UTIL.put_line('The time taken to bulk insert records into FII_AP_Invoice_UI_GT is: ' || to_char(l_timestamp1_tmp/100) || ' seconds.');
g_state := 'Bulk inserting into FII_AP_Pay_Sched_D_GT from FII_AP_Pay_Sched_D_MS.';
INSERT INTO FII_AP_Pay_Sched_D_GT VALUES FII_AP_Pay_Sched_D_MS(i);
FII_UTIL.put_line('The time taken to bulk insert into FII_AP_Pay_Sched_D_GT is: ' || to_char(l_timestamp1_tmp/100) || ' seconds.');
g_state := 'Bulk inserting into FII_AP_Pay_Sched_UI_GT from FII_AP_Pay_Sched_UI_MS.';
INSERT INTO FII_AP_Pay_Sched_UI_GT VALUES FII_AP_Pay_Sched_UI_MS(i);
FII_UTIL.put_line('The time taken to bulk insert records into FII_AP_Pay_Sched_UI_GT is: ' || to_char(l_timestamp1_tmp/100) || ' seconds.');
g_state := 'Bulk inserting into FII_AP_Aging_Bkts_B from FII_AP_Aging_Bkts_B_MS.';
INSERT INTO FII_AP_AGING_BKTS_B VALUES FII_AP_Aging_Bkts_B_MS(i);
FII_UTIL.put_line('The time taken to bulk insert into FII_AP_Aging_Bkts_B is: ' || to_char(l_timestamp1_tmp/100) || ' seconds.');
g_state := 'Bulk inserting into FII_AP_Due_Counts_B from FII_AP_Due_Counts_B_MS.';
INSERT INTO FII_AP_DUE_COUNTS_B VALUES FII_AP_Due_Counts_B_MS(i);
FII_UTIL.put_line('The time taken to bulk insert into FII_AP_Due_Counts_B is: ' || to_char(l_timestamp1_tmp/100) || ' seconds.');
DELETE FROM FII_AP_Pay_Sched_B PSUM
WHERE EXISTS
(SELECT 1
FROM FII_AP_Pay_Sched_D_GT D
WHERE D.Invoice_ID = PSUM.Invoice_ID
AND D.Payment_Num = PSUM.Payment_Num
AND D.Action_Date = PSUM.Action_Date
AND D.Action = PSUM.Action
AND NVL(D.Inv_Pymt_Flag, ' ') = NVL(PSUM.Inv_Pymt_Flag, ' ')
AND NVL(D.Unique_ID, -99) = NVL(PSUM.Unique_ID, -99));
FII_UTIL.put_line('The time taken to delete records from FII_AP_Pay_Sched_B is: ' || to_char(l_timestamp1_tmp/100) || ' seconds.');
g_state := 'Updating and Inserting records in FII_AP_Pay_Sched_B.';
UPDATE SET PSUM.Org_ID = UI.Org_ID,
PSUM.Supplier_ID = UI.Supplier_ID,
PSUM.Base_Currency_Code = UI.Base_Currency_Code,
PSUM.Trx_Date = UI.Trx_Date,
PSUM.Due_Date = UI.Due_Date,
PSUM.Amount_Remaining = UI.Amount_Remaining,
PSUM.Past_Due_Amount = UI.Past_Due_Amount,
PSUM.Discount_Available = UI.Discount_Available,
PSUM.Discount_Taken = UI.Discount_Taken,
PSUM.Discount_Lost = UI.Discount_Lost,
PSUM.Payment_Amount = UI.Payment_Amount,
PSUM.On_Time_Payment_Amt = UI.On_Time_Payment_Amt,
PSUM.Late_Payment_Amt = UI.Late_Payment_Amt,
PSUM.No_Days_Late = UI.No_Days_Late,
PSUM.Due_Bucket1 = UI.Due_Bucket1,
PSUM.Due_Bucket2 = UI.Due_Bucket2,
PSUM.Due_Bucket3 = UI.Due_Bucket3,
PSUM.Past_Due_Bucket1 = UI.Past_Due_Bucket1,
PSUM.Past_Due_Bucket2 = UI.Past_Due_Bucket2,
PSUM.Past_Due_Bucket3 = UI.Past_Due_Bucket3,
PSUM.Amount_Remaining_B = UI.Amount_Remaining_B,
PSUM.Past_Due_Amount_B = UI.Past_Due_Amount_B,
PSUM.Discount_Available_B = UI.Discount_Available_B,
PSUM.Discount_Taken_B = UI.Discount_Taken_B,
PSUM.Discount_Lost_B = UI.Discount_Lost_B,
PSUM.Payment_Amount_B = UI.Payment_Amount_B,
PSUM.On_Time_Payment_Amt_B = UI.On_Time_Payment_Amt_B,
PSUM.Late_Payment_Amt_B = UI.Late_Payment_Amt_B,
PSUM.Due_Bucket1_B = UI.Due_Bucket1_B,
PSUM.Due_Bucket2_B = UI.Due_Bucket2_B,
PSUM.Due_Bucket3_B = UI.Due_Bucket3_B,
PSUM.Past_Due_Bucket1_B = UI.Past_Due_Bucket1_B,
PSUM.Past_Due_Bucket2_B = UI.Past_Due_Bucket2_B,
PSUM.Past_Due_Bucket3_B = UI.Past_Due_Bucket3_B,
PSUM.Prim_Amount_Remaining = UI.Prim_Amount_Remaining,
PSUM.Prim_Past_Due_Amount = UI.Prim_Past_Due_Amount,
PSUM.Prim_Discount_Available = UI.Prim_Discount_Available,
PSUM.Prim_Discount_Taken = UI.Prim_Discount_Taken,
PSUM.Prim_Discount_Lost = UI.Prim_Discount_Lost,
PSUM.Prim_Payment_Amount = UI.Prim_Payment_Amount,
PSUM.Prim_On_Time_Payment_Amt = UI.Prim_On_Time_Payment_Amt,
PSUM.Prim_Late_Payment_Amt = UI.Prim_Late_Payment_Amt,
PSUM.Prim_Due_Bucket1 = UI.Prim_Due_Bucket1,
PSUM.Prim_Due_Bucket2 = UI.Prim_Due_Bucket2,
PSUM.Prim_Due_Bucket3 = UI.Prim_Due_Bucket3,
PSUM.Prim_Past_Due_Bucket1 = UI.Prim_Past_Due_Bucket1,
PSUM.Prim_Past_Due_Bucket2 = UI.Prim_Past_Due_Bucket2,
PSUM.Prim_Past_Due_Bucket3 = UI.Prim_Past_Due_Bucket3,
PSUM.Sec_Amount_Remaining = UI.Sec_Amount_Remaining,
PSUM.Sec_Past_Due_Amount = UI.Sec_Past_Due_Amount,
PSUM.Sec_Discount_Available = UI.Sec_Discount_Available,
PSUM.Sec_Discount_Taken = UI.Sec_Discount_Taken,
PSUM.Sec_Discount_Lost = UI.Sec_Discount_Lost,
PSUM.Sec_Payment_Amount = UI.Sec_Payment_Amount,
PSUM.Sec_On_Time_Payment_Amt = UI.Sec_On_Time_Payment_Amt,
PSUM.Sec_Late_Payment_Amt = UI.Sec_Late_Payment_Amt,
PSUM.Sec_Due_Bucket1 = UI.Sec_Due_Bucket1,
PSUM.Sec_Due_Bucket2 = UI.Sec_Due_Bucket2,
PSUM.Sec_Due_Bucket3 = UI.Sec_Due_Bucket3,
PSUM.Sec_Past_Due_Bucket1 = UI.Sec_Past_Due_Bucket1,
PSUM.Sec_Past_Due_Bucket2 = UI.Sec_Past_Due_Bucket2,
PSUM.Sec_Past_Due_Bucket3 = UI.Sec_Past_Due_Bucket3,
PSUM.Check_ID = UI.Check_ID,
PSUM.Payment_Method = UI.Payment_Method,
PSUM.Created_By = UI.Created_By,
PSUM.Check_Date = UI.Check_Date,
PSUM.Last_Update_Date = UI.Last_Update_Date
WHEN NOT MATCHED THEN
INSERT (PSUM.Time_ID, PSUM.Period_Type_ID, PSUM.Action_Date, PSUM.Action,
PSUM.Update_Sequence, PSUM.Org_ID, PSUM.Supplier_ID, PSUM.Invoice_ID,
PSUM.Base_Currency_Code, PSUM.Trx_Date, PSUM.Payment_Num, PSUM.Due_Date,
PSUM.Amount_Remaining, PSUM.Past_Due_Amount, PSUM.Discount_Available,
PSUM.Discount_Taken, PSUM.Discount_Lost, PSUM.Payment_Amount,
PSUM.On_Time_Payment_Amt, PSUM.Late_Payment_Amt, PSUM.No_Days_Late,
PSUM.Due_Bucket1, PSUM.Due_Bucket2, PSUM.Due_Bucket3, PSUM.Past_Due_Bucket1,
PSUM.Past_Due_Bucket2, PSUM.Past_Due_Bucket3, PSUM.Amount_Remaining_B,
PSUM.Past_Due_Amount_B, PSUM.Discount_Available_B, PSUM.Discount_Taken_B,
PSUM.Discount_Lost_B, PSUM.Payment_Amount_B, PSUM.On_Time_Payment_Amt_B,
PSUM.Late_Payment_Amt_B, PSUM.Due_Bucket1_B, PSUM.Due_Bucket2_B,
PSUM.Due_Bucket3_B, PSUM.Past_Due_Bucket1_B, PSUM.Past_Due_Bucket2_B,
PSUM.Past_Due_Bucket3_B, PSUM.Prim_Amount_Remaining, PSUM.Prim_Past_Due_Amount,
PSUM.Prim_Discount_Available, PSUM.Prim_Discount_Taken, PSUM.Prim_Discount_Lost,
PSUM.Prim_Payment_Amount, PSUM.Prim_On_Time_Payment_Amt,
PSUM.Prim_Late_Payment_Amt, PSUM.Prim_Due_Bucket1, PSUM.Prim_Due_Bucket2,
PSUM.Prim_Due_Bucket3, PSUM.Prim_Past_Due_Bucket1, PSUM.Prim_Past_Due_Bucket2,
PSUM.Prim_Past_Due_Bucket3, PSUM.Sec_Amount_Remaining, PSUM.Sec_Past_Due_Amount,
PSUM.Sec_Discount_Available, PSUM.Sec_Discount_Taken, PSUM.Sec_Discount_Lost,
PSUM.Sec_Payment_Amount, PSUM.Sec_On_Time_Payment_Amt, PSUM.Sec_Late_Payment_Amt,
PSUM.Sec_Due_Bucket1, PSUM.Sec_Due_Bucket2, PSUM.Sec_Due_Bucket3,
PSUM.Sec_Past_Due_Bucket1, PSUM.Sec_Past_Due_Bucket2, PSUM.Sec_Past_Due_Bucket3,
PSUM.Fully_Paid_Date, PSUM.Check_ID, PSUM.Payment_Method, PSUM.Last_Update_Date,
PSUM.Last_Updated_By, PSUM.Creation_Date, PSUM.Created_By, PSUM.Last_Update_Login,
PSUM.Check_Date, PSUM.Inv_Pymt_Flag, PSUM.Unique_ID)
VALUES (UI.Time_ID, UI.Period_Type_ID, UI.Action_Date, UI.Action,
UI.Update_Sequence, UI.Org_ID, UI.Supplier_ID, UI.Invoice_ID,
UI.Base_Currency_Code, UI.Trx_Date, UI.Payment_Num, UI.Due_Date,
UI.Amount_Remaining, UI.Past_Due_Amount, UI.Discount_Available,
UI.Discount_Taken, UI.Discount_Lost, UI.Payment_Amount,
UI.On_Time_Payment_Amt, UI.Late_Payment_Amt, UI.No_Days_Late,
UI.Due_Bucket1, UI.Due_Bucket2, UI.Due_Bucket3, UI.Past_Due_Bucket1,
UI.Past_Due_Bucket2, UI.Past_Due_Bucket3, UI.Amount_Remaining_B,
UI.Past_Due_Amount_B, UI.Discount_Available_B, UI.Discount_Taken_B,
UI.Discount_Lost_B, UI.Payment_Amount_B, UI.On_Time_Payment_Amt_B,
UI.Late_Payment_Amt_B, UI.Due_Bucket1_B, UI.Due_Bucket2_B,
UI.Due_Bucket3_B, UI.Past_Due_Bucket1_B, UI.Past_Due_Bucket2_B,
UI.Past_Due_Bucket3_B, UI.Prim_Amount_Remaining, UI.Prim_Past_Due_Amount,
UI.Prim_Discount_Available, UI.Prim_Discount_Taken, UI.Prim_Discount_Lost,
UI.Prim_Payment_Amount, UI.Prim_On_Time_Payment_Amt,
UI.Prim_Late_Payment_Amt, UI.Prim_Due_Bucket1, UI.Prim_Due_Bucket2,
UI.Prim_Due_Bucket3, UI.Prim_Past_Due_Bucket1, UI.Prim_Past_Due_Bucket2,
UI.Prim_Past_Due_Bucket3, UI.Sec_Amount_Remaining, UI.Sec_Past_Due_Amount,
UI.Sec_Discount_Available, UI.Sec_Discount_Taken, UI.Sec_Discount_Lost,
UI.Sec_Payment_Amount, UI.Sec_On_Time_Payment_Amt, UI.Sec_Late_Payment_Amt,
UI.Sec_Due_Bucket1, UI.Sec_Due_Bucket2, UI.Sec_Due_Bucket3,
UI.Sec_Past_Due_Bucket1, UI.Sec_Past_Due_Bucket2, UI.Sec_Past_Due_Bucket3,
UI.Fully_Paid_Date, UI.Check_ID, UI.Payment_Method, UI.Last_Update_Date,
UI.Last_Updated_By, UI.Creation_Date, UI.Created_By, UI.Last_Update_Login,
UI.Check_Date, UI.Inv_Pymt_Flag, UI.Unique_ID);
FII_UTIL.put_line('The time taken to update and insert records in FII_AP_Pay_Sched_B is: ' || to_char(l_timestamp1_tmp/100) || ' seconds.');
DELETE FROM FII_AP_Invoice_B AI
WHERE EXISTS
(SELECT 1
FROM FII_AP_Invoice_D_GT D
WHERE D.Invoice_ID = AI.Invoice_ID);
FII_UTIL.put_line('The time taken to delete records from FII_AP_Invoice_B is: ' || to_char(l_timestamp1_tmp/100) || ' seconds.');
g_state := 'Updating and Inserting records in FII_Invoice_B.';
UPDATE SET AI.Org_ID = UI.Org_ID,
AI.Supplier_ID = UI.Supplier_ID,
AI.Invoice_Type = UI.Invoice_Type,
AI.Invoice_Number = UI.Invoice_Number,
AI.Invoice_Date = UI.Invoice_Date,
AI.Invoice_Amount = UI.Invoice_Amount,
AI.Base_Amount = UI.Base_Amount,
AI.Prim_Amount = UI.Prim_Amount,
AI.Sec_Amount = UI.Sec_Amount,
AI.Invoice_Currency_Code = UI.Invoice_Currency_Code,
AI.Base_Currency_Code = UI.Base_Currency_Code,
AI.Entered_Date = UI.Entered_Date,
AI.Payment_Currency_Code = UI.Payment_Currency_Code,
AI.Fully_Paid_Date = UI.Fully_Paid_Date,
AI.Terms_ID = UI.Terms_ID,
AI.Source = UI.Source,
AI.E_Invoices_Flag = UI.E_Invoices_Flag,
AI.Cancel_Flag = UI.Cancel_Flag,
AI.Cancel_Date = UI.Cancel_Date,
AI.Dist_Count = UI.Dist_Count,
AI.Due_Date = UI.Due_Date,
AI.Discount_Offered = UI.Discount_Offered,
AI.Discount_Offered_B = UI.Discount_Offered_B,
AI.Prim_Discount_Offered = UI.Prim_Discount_Offered,
AI.Sec_Discount_Offered = UI.Sec_Discount_Offered,
AI.First_Hold_Date = UI.First_Hold_Date,
AI.Exchange_Date = UI.Exchange_Date,
AI.Exchange_Rate = UI.Exchange_Rate,
AI.Exchange_Rate_Type = UI.Exchange_Rate_Type,
AI.Payment_Status_Flag = UI.Payment_Status_Flag,
AI.Payment_Cross_Rate = UI.Payment_Cross_Rate,
AI.Fully_Paid_Amount = UI.Fully_Paid_Amount,
AI.Fully_Paid_Amount_B = UI.Fully_Paid_Amount_B,
AI.Prim_Fully_Paid_Amount = UI.Prim_Fully_Paid_Amount,
AI.Sec_Fully_Paid_Amount = UI.Sec_Fully_Paid_Amount,
AI.Last_Update_Date = UI.Last_Update_Date
WHEN NOT MATCHED THEN
INSERT (AI.Org_ID, AI.Supplier_ID, AI.Invoice_ID, AI.Invoice_Type, AI.Invoice_Number,
AI.Invoice_Date, AI.Invoice_Amount, AI.Base_Amount, AI.Prim_Amount,
AI.Sec_Amount, Ai.Invoice_Currency_Code, AI.Base_Currency_Code, AI.Entered_Date,
AI.Payment_Currency_Code, AI.Fully_Paid_Date, AI.Terms_ID, AI.Source,
AI.E_Invoices_Flag, AI.Cancel_Flag, AI.Cancel_Date, AI.Dist_Count, AI.Due_Date,
AI.Discount_Offered, AI.Discount_Offered_B, AI.Prim_Discount_Offered,
AI.Sec_Discount_Offered, AI.First_Hold_Date, AI.Last_Update_Date,
AI.Last_Updated_By, AI.Creation_Date, AI.Created_By, AI.Last_Update_Login,
AI.Exchange_Date, AI.Exchange_Rate, AI.Exchange_Rate_Type, AI.Payment_Status_Flag,
AI.Payment_Cross_Rate, AI.Fully_Paid_Amount, AI.Fully_Paid_Amount_B,
AI.Prim_Fully_Paid_Amount, AI.Sec_Fully_Paid_Amount)
VALUES (UI.Org_ID, UI.Supplier_ID, UI.Invoice_ID, UI.Invoice_Type, UI.Invoice_Number,
UI.Invoice_Date, UI.Invoice_Amount, UI.Base_Amount, UI.Prim_Amount,
UI.Sec_Amount, Ui.Invoice_Currency_Code, UI.Base_Currency_Code, UI.Entered_Date,
UI.Payment_Currency_Code, UI.Fully_Paid_Date, UI.Terms_ID, UI.Source,
UI.E_Invoices_Flag, UI.Cancel_Flag, UI.Cancel_Date, UI.Dist_Count, UI.Due_Date,
UI.Discount_Offered, UI.Discount_Offered_B, UI.Prim_Discount_Offered,
UI.Sec_Discount_Offered, UI.First_Hold_Date, UI.Last_Update_Date,
UI.Last_Updated_By, UI.Creation_Date, UI.Created_By, UI.Last_Update_Login,
UI.Exchange_Date, UI.Exchange_Rate, UI.Exchange_Rate_Type, UI.Payment_Status_Flag,
UI.Payment_Cross_Rate, UI.Fully_Paid_Amount, UI.Fully_Paid_Amount_B,
UI.Prim_Fully_Paid_Amount, UI.Sec_Fully_Paid_Amount);
FII_UTIL.put_line('The time taken to update and insert records in FII_AP_Invoice_B is: ' || to_char(l_timestamp1_tmp/100) || ' seconds.');
DELETE /*+ index(A, FII_AP_AGING_BKTS_B_N1) */ FROM FII_AP_Aging_Bkts_B A
WHERE Invoice_ID IN (SELECT Invoice_ID
FROM FII_AP_Invoice_IDS
WHERE Pay_Sched_B_Flag = 'Y');
DELETE /*+ index(A, FII_AP_DUE_COUNTS_B_N1) */ FROM FII_AP_Due_Counts_B A
WHERE Invoice_ID IN (SELECT Invoice_ID
FROM FII_AP_Invoice_IDS
WHERE Pay_Sched_B_Flag = 'Y');
SELECT fii_ap_pay_sched_b_s.nextval
INTO g_seq_id
FROM dual;
'FII: Manual Invoice Sources'. Do dummy select in order to verify that the
format of the profile option is valid. Correct format is: 'Source1',..,'SourceN'*/
g_state := 'Verifying that profile option ''FII: Manual Invoice Sources'' is valid.';
execute immediate('SELECT 1 FROM (SELECT '' '' SOURCE FROM DUAL)
WHERE SOURCE IN (' || g_manual_sources || ')');
INSERT_DELETED_REC(l_invoice.Invoice_ID, NULL);
g_state := 'Inserting ''CREATION'' record into FII_AP_Pay_Sched_Temp_MS for Invoice ' || l_invoice.Invoice_ID || ', Payment Number ' || l_pay_sched.Payment_Num || '.';
g_state := 'Inserting first ''DISCOUNT'' record into FII_AP_Pay_Sched_Temp_MS for Invoice ' || l_invoice.Invoice_ID || ', Payment Number ' || l_pay_sched.Payment_Num || '.';
g_state := 'Inserting second ''DISCOUNT'' record into FII_AP_Pay_Sched_Temp_MS for Invoice ' || l_invoice.Invoice_ID || ', Payment Number ' || l_pay_sched.Payment_Num || '.';
g_state := 'Inserting third ''DISCOUNT'' record into FII_AP_Pay_Sched_Temp_MS for Invoice ' || l_invoice.Invoice_ID || ', Payment Number ' || l_pay_sched.Payment_Num || '.';
g_state := 'Inserting first ''DUE BUCKET'' record into FII_AP_Pay_Sched_Temp_MS for Invoice ' || l_invoice.Invoice_ID || ', Payment Number ' || l_pay_sched.Payment_Num || '.';
g_state := 'Inserting second ''DUE BUCKET'' record into FII_AP_Pay_Sched_Temp_MS for Invoice ' || l_invoice.Invoice_ID || ', Payment Number ' || l_pay_sched.Payment_Num || '.';
g_state := 'Inserting ''DUE'' record into FII_AP_Pay_Sched_Temp_MS for Invoice ' || l_invoice.Invoice_ID || ', Payment Number ' || l_pay_sched.Payment_Num || '.';
g_state := 'Inserting first ''PAST BUCKET'' record into FII_AP_Pay_Sched_Temp_MS for Invoice ' || l_invoice.Invoice_ID || ', Payment Number ' || l_pay_sched.Payment_Num || '.';
g_state := 'Inserting second ''PAST BUCKET'' record into FII_AP_Pay_Sched_Temp_MS for Invoice ' || l_invoice.Invoice_ID || ', Payment Number ' || l_pay_sched.Payment_Num || '.';
g_state := 'Inserting invoice payment record into FII_AP_Pay_Sched_Temp_MS for Invoice ' || l_invoice.Invoice_ID || ', Payment Number ' || l_pay_sched.Payment_Num || ', Invoice Payment ' || l_inv_pay.Invoice_Payment_ID || '.';
g_state := 'Inserting withholding/tax record into FII_AP_Pay_Sched_Temp_MS for Invoice ' || l_invoice.Invoice_ID || ', Payment Number ' || l_pay_sched.Payment_Num || ', Invoice Distribution ' || l_wh_tax.Invoice_Distribution_ID || '.';
g_state := 'Deciding what action to insert for Invoice ' || l_invoice.Invoice_ID || ', Payment Number ' || l_pay_sched.Payment_Num || '.';
g_state := 'Inserting prepayment applied record for Invoice ' || l_invoice.Invoice_ID || ', Payment Number ' || l_pay_sched.Payment_Num || '.';
l_pay_sched_b.Update_Sequence := g_seq_id;
l_pay_sched_b.Last_Update_Date := sysdate;
l_pay_sched_b.Last_Updated_By := g_fii_user_id;
l_pay_sched_b.Last_Update_Login := g_fii_login_id;
Insert_Pay_Sched_B_Rec(l_pay_sched_b, 'Y');
ELSE Insert_Pay_Sched_B_Rec(l_pay_sched_b, 'N');
l_aging_bkts_b.Last_Update_Date := sysdate;
l_aging_bkts_b.Last_Updated_By := g_fii_user_id;
l_aging_bkts_b.Last_Update_Login := g_fii_login_id;
l_due_counts_b.Last_Update_Date := sysdate;
l_due_counts_b.Last_Updated_By := g_fii_user_id;
l_due_counts_b.Last_Update_Login := g_fii_login_id;
FII_UTIL.put_line('Error occured while inserting applied prepayment record into FII_AP_Aging_MS.');
g_state := 'Inserting ''CREATION'' record for Invoice ' || l_invoice.Invoice_ID || ', Payment Number ' || l_pay_sched.Payment_Num || '.';
l_pay_sched_b.Update_Sequence := g_seq_id;
l_pay_sched_b.Last_Update_Date := sysdate;
l_pay_sched_b.Last_Updated_By := g_fii_user_id;
l_pay_sched_b.Last_Update_Login := g_fii_login_id;
Insert_Pay_Sched_B_Rec(l_pay_sched_b, 'Y');
ELSE Insert_Pay_Sched_B_Rec(l_pay_sched_b, 'N');
l_aging_bkts_b.Last_Update_Date := sysdate;
l_aging_bkts_b.Last_Updated_By := g_fii_user_id;
l_aging_bkts_b.Last_Update_Login := g_fii_login_id;
l_due_counts_b.Last_Update_Date := sysdate;
l_due_counts_b.Last_Updated_By := g_fii_user_id;
l_due_counts_b.Last_Update_Login := g_fii_login_id;
FII_UTIL.put_line('Error occured while inserting creation record into FII_AP_Aging_MS.');
g_state := 'Inserting discount record for Invoice ' || l_invoice.Invoice_ID || ', Payment Number ' || l_pay_sched.Payment_Num || ', Discount Number ' || l_pay_sched_temp.Number1 || '.';
l_pay_sched_b.Update_Sequence := g_seq_id;
l_pay_sched_b.Last_Update_Date := sysdate;
l_pay_sched_b.Last_Updated_By := g_fii_user_id;
l_pay_sched_b.Last_Update_Login := g_fii_login_id;
Insert_Pay_Sched_B_Rec(l_pay_sched_b, 'Y');
ELSE Insert_Pay_Sched_B_Rec(l_pay_sched_b, 'N');
g_state := 'Inserting Due Bucket/Due/Past Bucket record for Invoice ' || l_invoice.Invoice_ID || ', Payment Number ' || l_pay_sched.Payment_Num || ', Date ' || l_pay_sched_temp.Action_Date || '.';
l_pay_sched_b.Update_Sequence := g_seq_id;
l_pay_sched_b.Last_Update_Date := sysdate;
l_pay_sched_b.Last_Updated_By := g_fii_user_id;
l_pay_sched_b.Last_Update_Login := g_fii_login_id;
Insert_Pay_Sched_B_Rec(l_pay_sched_b, 'Y');
ELSE Insert_Pay_Sched_B_Rec(l_pay_sched_b, 'N');
l_aging_bkts_b.Last_Update_Date := sysdate;
l_aging_bkts_b.Last_Updated_By := g_fii_user_id;
l_aging_bkts_b.Last_Update_Login := g_fii_login_id;
l_due_counts_b.Last_Update_Date := sysdate;
l_due_counts_b.Last_Updated_By := g_fii_user_id;
l_due_counts_b.Last_Update_Login := g_fii_login_id;
FII_UTIL.put_line('Error occured while inserting due bucket/due/past bucket record into FII_AP_Aging_MS.');
g_state := 'Inserting invoice payment record for Invoice ' || l_invoice.Invoice_ID || ', Payment Number ' || l_pay_sched.Payment_Num || ', Marker ' || l_pay_sched_temp_marker || '.';
l_pay_sched_b.Update_Sequence := g_seq_id;
l_pay_sched_b.Last_Update_Date := sysdate;
l_pay_sched_b.Last_Updated_By := g_fii_user_id;
l_pay_sched_b.Last_Update_Login := g_fii_login_id;
Insert_Pay_Sched_B_Rec(l_pay_sched_b, 'Y');
ELSE Insert_Pay_Sched_B_Rec(l_pay_sched_b, 'N');
l_aging_bkts_b.Last_Update_Date := sysdate;
l_aging_bkts_b.Last_Updated_By := g_fii_user_id;
l_aging_bkts_b.Last_Update_Login := g_fii_login_id;
l_due_counts_b.Last_Update_Date := sysdate;
l_due_counts_b.Last_Updated_By := g_fii_user_id;
l_due_counts_b.Last_Update_Login := g_fii_login_id;
FII_UTIL.put_line('Error occured while inserting invoice payment record into FII_AP_Aging_MS.');
g_state := 'Inserting wh/tax record for Invoice ' || l_invoice.Invoice_ID || ', Payment Number ' || l_pay_sched.Payment_Num || ', Marker ' || l_pay_sched_temp_marker || '.';
l_pay_sched_b.Update_Sequence := g_seq_id;
l_pay_sched_b.Last_Update_Date := sysdate;
l_pay_sched_b.Last_Updated_By := g_fii_user_id;
l_pay_sched_b.Last_Update_Login := g_fii_login_id;
Insert_Pay_Sched_B_Rec(l_pay_sched_b, 'Y');
ELSE Insert_Pay_Sched_B_Rec(l_pay_sched_b, 'N');
l_aging_bkts_b.Last_Update_Date := sysdate;
l_aging_bkts_b.Last_Updated_By := g_fii_user_id;
l_aging_bkts_b.Last_Update_Login := g_fii_login_id;
l_due_counts_b.Last_Update_Date := sysdate;
l_due_counts_b.Last_Updated_By := g_fii_user_id;
l_due_counts_b.Last_Update_Login := g_fii_login_id;
FII_UTIL.put_line('Error occured while inserting wh/tax record into FII_AP_Aging_MS.');
g_state := 'Inserting invoice for Invoice ' || l_invoice.Invoice_ID || '.';
l_invoice_b.Last_Update_Date := sysdate;
l_invoice_b.Last_Updated_By := g_fii_user_id;
l_invoice_b.Last_Update_Login := g_fii_login_id;
Insert_Invoice_B_Rec(l_invoice_b);
g_state := 'Inserting aging records into FII_AP_Aging_Bkts_B_MS and FII_AP_Due_Counts_B_MS for Invoice ' || l_invoice.Invoice_ID || ' with multiple payment schedules.';
l_aging_bkts_b.Past_Due_Bucket1_Cnt <> 0 THEN --Insert into FII_AP_AGING_BKTS_B_MS.
g_state := 'Inserting aging record in FII_AP_Aging_Bkts_B_MS for Invoice ' || l_invoice.Invoice_ID || ', Marker ' || l_ps_aging_marker || '.';
l_aging_bkts_b.Last_Update_Date := sysdate;
l_aging_bkts_b.Last_Updated_By := g_fii_user_id;
l_aging_bkts_b.Last_Update_Login := g_fii_login_id;
l_due_counts_b.Past_Due_Cnt <> 0 THEN --Insert into FII_AP_DUE_COUNTS_B_MS.
g_state := 'Inserting aging record in FII_AP_Due_Counts_B_MS for Invoice ' || l_invoice.Invoice_ID || ', Marker ' || l_ps_aging_marker || '.';
l_due_counts_b.Last_Update_Date := sysdate;
l_due_counts_b.Last_Updated_By := g_fii_user_id;
l_due_counts_b.Last_Update_Login := g_fii_login_id;
INSERT_DELETED_REC(NULL, NULL);
UPDATE FII_AP_AGING_BKTS_B AB
SET Supplier_ID = (SELECT AI.Supplier_ID
FROM FII_AP_Invoice_B AI
WHERE AI.Invoice_ID = AB.Invoice_ID)
WHERE AB.Invoice_ID IN (SELECT Key_Value1_Num
FROM FII_AP_DBI_LOG_T
WHERE Table_Name = 'AP_INVOICES'
AND Operation_Flag = 'U');
UPDATE FII_AP_DUE_COUNTS_B DC
SET Supplier_ID = (SELECT AI.Supplier_ID
FROM FII_AP_Invoice_B AI
WHERE AI.Invoice_ID = DC.Invoice_ID)
WHERE DC.Invoice_ID IN (SELECT Key_Value1_Num
FROM FII_AP_DBI_LOG_T
WHERE Table_Name = 'AP_INVOICES'
AND Operation_Flag = 'U');
INSERT into FII_AP_DBI_LOG_T(Key_Value1_Num,
Key_Value2_Num,
Table_Name,
Operation_Flag,
Creation_Date,
Created_By,
Last_Update_Date,
Last_Updated_By,
Last_Update_Login)
SELECT Key_Value1_Num,
Key_Value2_Num,
Table_Name,
Operation_Flag,
sysdate Creation_Date,
g_fii_user_id Created_By,
sysdate Last_Update_Date,
g_fii_user_id Last_Updated_By,
g_fii_login_id Last_Update_Login
FROM (SELECT Key_Value1 Key_Value1_Num,
Key_Value2 Key_Value2_Num,
Table_Name,
Operation_Flag
FROM AP_DBI_LOG
WHERE Creation_Date >= g_timestamp2
AND Creation_Date < g_timestamp1
UNION
SELECT Key_Value1_ID Key_Value1_Num,
Key_Value2_ID Key_Value2_Num,
Table_Name,
Operation_Flag
FROM FII_AP_DBI_Log_PS_T)
GROUP BY Key_Value1_Num, Key_Value2_Num, Table_Name, Operation_Flag;
g_state := 'Inserting records into the FII_AP_INVOICE_IDS table';
INSERT INTO FII_AP_Invoice_IDS
(Invoice_ID,
Invoice_B_Flag,
Pay_Sched_B_Flag,
Get_Rate_Flag,
Delete_Inv_Flag,
Last_Update_Date,
Last_Updated_By,
Creation_Date,
Created_By,
Last_Update_Login)
SELECT INVIDS.Invoice_ID,
CASE WHEN MAX(DECODE(Rank, 1, 1, 0)) = 1 THEN 'Y' ELSE 'N' END Invoice_B_Flag,
CASE WHEN MAX(DECODE(Rank, 2, 1, 0)) = 1 THEN 'Y' ELSE 'N' END Pay_Sched_B_Flag,
CASE WHEN MAX(DECODE(Rank,1, DECODE(Operation_Flag, 'D', 0, 1), 0)) = 1 OR MAX(DECODE(Rank, 2, 1, 0)) = 1
THEN 'Y' ELSE 'N' END Get_Rate_Flag,
CASE WHEN MAX(CASE WHEN Rank = 1
AND Table_Name = 'AP_INVOICES'
AND Operation_Flag = 'D' THEN 1 ELSE 0 END) = 1
THEN 'Y' ELSE 'N' END Delete_Inv_Flag,
sysdate Last_Update_Date,
g_fii_user_id Last_Updated_By,
sysdate Creation_Date,
g_fii_user_id Created_By,
g_fii_login_id Last_Update_Login
FROM (SELECT Key_Value1_Num Invoice_ID,
1 Rank,
Table_Name,
Operation_Flag
FROM FII_AP_DBI_LOG_T
WHERE Table_Name IN ('AP_INVOICES', 'AP_HOLDS', 'AP_PAYMENT_SCHEDULES',
'AP_INVOICE_DISTRIBUTIONS')
UNION
SELECT Invoice_ID,
2 Rank,
Table_Name,
Operation_Flag
FROM (
SELECT Key_Value1_Num Invoice_ID,
Table_Name,
Operation_Flag
FROM FII_AP_DBI_LOG_T
WHERE Table_Name = 'AP_PAYMENT_SCHEDULES'
UNION
SELECT LOG.Key_Value1_Num Invoice_ID,
LOG.Table_Name Table_Name,
LOG.Operation_Flag Operation_Flag
FROM FII_AP_DBI_LOG_T LOG, AP_Invoice_Distributions_All AID
WHERE LOG.Table_Name = 'AP_INVOICE_DISTRIBUTIONS'
AND LOG.Key_Value2_Num = AID.Invoice_Distribution_ID
AND AID.Line_Type_Lookup_Code IN ('PREPAY', 'AWT', 'NONREC_TAX', 'REC_TAX')
UNION
SELECT AIP.Invoice_ID Invoice_ID,
Table_Name,
Operation_Flag
FROM FII_AP_DBI_LOG_T LOG, AP_Invoice_Payments_All AIP
WHERE Table_Name = 'AP_INVOICE_PAYMENTS'
AND LOG.Key_Value1_Num = AIP.Invoice_Payment_ID
UNION
SELECT PS.Invoice_ID Invoice_ID,
'OTHER' Table_Name,
'U' Operation_Flag
FROM AP_Payment_Schedules_All PS
WHERE Payment_Status_Flag IN ('N', 'P')
AND ((g_last_start_date <= TRUNC(PS.Discount_Date)
AND TRUNC(PS.Discount_Date) < g_sysdate)
OR (g_last_start_date <= TRUNC(PS.Second_Discount_Date)
AND TRUNC(PS.Second_Discount_Date) < g_sysdate)
OR (g_last_start_date <= TRUNC(PS.Third_Discount_Date)
AND TRUNC(PS.Third_Discount_Date) < g_sysdate)
OR (g_last_start_date <= (TRUNC(PS.Due_Date) - g_due_bucket2 - 1)
AND (TRUNC(PS.Due_Date) - g_due_bucket2 - 1) < g_sysdate)
OR (g_last_start_date <= (TRUNC(PS.Due_Date) - g_due_bucket3 - 1)
AND (TRUNC(PS.Due_Date) - g_due_bucket3 - 1) < g_sysdate)
OR (g_last_start_date <= TRUNC(PS.Due_Date)
AND TRUNC(PS.Due_Date) < g_sysdate)
OR (g_last_start_date <= (TRUNC(PS.Due_Date) + g_past_bucket3)
AND (TRUNC(PS.Due_Date) + g_past_bucket3) < g_sysdate)
OR (g_last_start_date <= (TRUNC(PS.Due_Date) + g_past_bucket2)
AND (TRUNC(PS.Due_Date) + g_past_bucket2) < g_sysdate)))) INVIDS
GROUP BY INVIDS.Invoice_ID;
FII_UTIL.put_line('Calling the Insert_Rates procedure to insert the missing rate info');
INSERT_RATES;
FII_UTIL.put_line('Calling procedure DELETE_SUMMARY');
DELETE_SUMMARY;
INSERT INTO FII_AP_DBI_LOG_EXP_T(
Table_Name,
Operation_Flag,
Key_Value1_ID,
Key_Value2_ID,
Created_By,
Last_Updated_By,
Last_Update_Login,
Creation_Date,
Last_Update_Date)
SELECT Table_Name,
Operation_Flag,
Key_Value1,
Key_Value2,
Created_By,
Last_Updated_By,
Last_Update_Login,
Creation_Date,
Last_Update_Date
FROM AP_DBI_LOG
WHERE Partition_ID = i
AND Creation_Date >= g_timestamp2
AND Creation_Date < g_timestamp1;