[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT max(invoice_ID), min(invoice_ID), COUNT(*)
INTO l_max_number, l_start_number, l_inv_count
FROM FII_AP_INVOICE_B;
SELECT MAX(INVOICE_ID) INTO l_end_number
FROM (SELECT invoice_id
FROM fii_ap_invoice_b
WHERE invoice_id >= l_start_number
ORDER BY invoice_id)
WHERE rownum < l_job_size;
INSERT INTO FII_AP_PS_WORK_JOBS
(Start_Range,
End_Range,
Worker_Number,
Status)
VALUES
(l_start_number,
least(l_end_number, l_max_number),
0,
'UNASSIGNED');
FII_UTIL.put_line('Inserted ' || l_count || ' jobs into FII_AP_PS_WORK_JOBS table');
SELECT nvl(sum(decode(status,'UNASSIGNED',1,0)),0),
nvl(sum(decode(status,'COMPLETED',1,0)),0),
nvl(sum(decode(status,'IN PROCESS',1,0)),0),
nvl(sum(decode(status,'FAILED',1,0)),0),
count(*)
INTO l_unassigned_cnt,
l_completed_cnt,
l_wip_cnt,
l_failed_cnt,
l_tot_cnt
FROM FII_AP_PS_WORK_JOBS;
Procedure INSERT_WH_PREPAY_AMOUNT IS
BEGIN
g_state := 'Inserting records into the FII_AP_WH_TAX_T table';
/* Selecting the prorated prepayment and withholding amount for a
payment schedule and inserting into the temp table */
INSERT /*+ append parallel(T) */ INTO fii_ap_wh_tax_t T
(Invoice_ID,
Payment_Num,
Creation_Date,
Due_Date,
Discount_Date,
Second_Discount_Date,
Third_Discount_Date,
Invoice_Type,
Entered_Date,
WH_Tax_Amount)
SELECT /*+ ordered use_hash(AID,AI,FC,PS) parallel(AID) parallel(AI) parallel(FC)
parallel(PS) */
AI.Invoice_ID,
PS.Payment_Num,
TRUNC(AID.Creation_Date) Creation_Date,
TRUNC(PS.Due_Date),
TRUNC(PS.Discount_Date),
TRUNC(PS.Second_Discount_Date),
TRUNC(PS.Third_Discount_Date),
AI.Invoice_Type,
TRUNC(AI.Entered_Date),
-1 * DECODE(AI.Invoice_Amount, 0, 0,
DECODE(FC.Minimum_Accountable_Unit, NULL,
ROUND(PS.Gross_Amount *
SUM(AID.Amount)/AI.Invoice_Amount
/ 0.01) * 0.01,
ROUND(PS.Gross_Amount *
SUM(AID.Amount)/AI.Invoice_Amount
/ FC.Minimum_Accountable_Unit) * FC.Minimum_Accountable_Unit))
WH_Tax_Amount
FROM FII_AP_Invoice_B AI,
AP_Invoice_Distributions_All AID,
AP_Invoice_Lines_All AIL,
AP_Payment_Schedules_All PS,
FND_Currencies FC
WHERE AI.Invoice_ID = AID.Invoice_ID
AND AID.Invoice_ID = AIL.Invoice_ID
AND AID.Invoice_Line_Number = AIL.Line_Number
AND AI.Cancel_Date IS NULL
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 PS.Invoice_ID = AI.Invoice_ID
AND FC.Currency_Code = AI.Payment_Currency_Code
GROUP BY AI.Invoice_ID,
AI.Invoice_Amount,
PS.Payment_Num,
PS.Gross_Amount,
TRUNC(AID.Creation_Date),
TRUNC(PS.Due_Date),
TRUNC(PS.Discount_Date),
TRUNC(PS.Second_Discount_Date),
TRUNC(PS.Third_Discount_Date),
AI.Invoice_Type,
TRUNC(AI.Entered_Date),
FC.Precision,
FC.Minimum_Accountable_Unit;
fii_util.put_line('Inserted '||SQL%ROWCOUNT||' records into FII_AP_WH_TAX_T');
g_state := 'Inserting records into the FII_AP_Prepay_T table';
INSERT /*+ append parallel(T) */ INTO fii_ap_prepay_t T
(Invoice_ID,
Payment_Num,
Creation_Date,
Due_Date,
Discount_Date,
Second_Discount_Date,
Third_Discount_Date,
Entered_Date,
Prepay_Amount,
Check_ID)
SELECT /*+ parallel(AID_prepay) parallel(PS_Prepay) */
PS_Prepay.Invoice_ID Invoice_ID,
PS_Prepay.Payment_Num Payment_Num,
AID_Prepay.Creation_Date Creation_Date,
PS_Prepay.Due_Date Due_Date,
PS_Prepay.Discount_Date Discount_Date,
PS_Prepay.Second_Discount_Date Second_Discount_Date,
PS_Prepay.Third_Discount_Date Third_Discount_Date,
PS_Prepay.Entered_Date Entered_Date,
CASE
WHEN PS_Prepay.First_PP + 1 <= AID_Prepay.First + 1 AND AID_Prepay.First + 1 <= PS_Prepay.Last_PP
THEN LEAST(PS_Prepay.Last_PP, AID_Prepay.Last) - AID_Prepay.First
WHEN AID_Prepay.First + 1 <= PS_Prepay.First_PP + 1 AND PS_Prepay.First_PP + 1 <= AID_Prepay.Last
THEN LEAST(PS_Prepay.Last_PP, AID_Prepay.Last) - PS_Prepay.First_PP
END Prepay_Amount,
AID_Prepay.Check_ID Check_ID
FROM (SELECT /*+ use_hash(AID,TEMP,AIP) parallel(AID) parallel(TEMP) parallel(AIP) */
AID.Invoice_ID Invoice_ID,
TRUNC(AID.Creation_Date) Creation_Date,
SUM(-1*ROUND((AID.Amount * AI.Payment_Cross_Rate)
/ NVL(FC.Minimum_Accountable_Unit, 0.01))
* NVL(FC.Minimum_Accountable_Unit, 0.01)) Prepymt,
(SUM(SUM(-1*ROUND((AID.Amount * AI.Payment_Cross_Rate)
/ NVL(FC.Minimum_Accountable_Unit, 0.01))
* NVL(FC.Minimum_Accountable_Unit, 0.01)))
OVER (PARTITION BY AID.Invoice_ID
ORDER BY AID.Invoice_ID, TRUNC(AID.Creation_Date)
ROWS UNBOUNDED PRECEDING))
- SUM(-1*ROUND((AID.Amount * AI.Payment_Cross_Rate)
/ NVL(FC.Minimum_Accountable_Unit, 0.01))
* NVL(FC.Minimum_Accountable_Unit, 0.01)) AS First,
SUM(SUM(-1*AID.Amount * AI.Payment_Cross_Rate)) OVER (PARTITION BY AID.Invoice_ID
ORDER BY AID.Invoice_ID, TRUNC(AID.Creation_Date)
ROWS UNBOUNDED PRECEDING) AS Last,
AIP.Check_ID Check_ID
FROM AP_Invoice_Distributions_All AID,
AP_Invoice_Lines_All AIL,
AP_Invoice_Distributions_All TEMP,
(SELECT /*+ parallel(AIP1) */ Invoice_ID Invoice_ID,
MIN(Check_ID) Check_ID
FROM AP_Invoice_Payments_All AIP1
GROUP BY Invoice_ID) AIP,
AP_Invoices_All AI,
FND_Currencies FC
WHERE AID.Invoice_ID = AI.Invoice_ID
AND AID.Invoice_ID = AIL.Invoice_ID
AND AID.Invoice_Line_Number = AIL.Line_Number
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
AND TEMP.Invoice_ID = AIP.Invoice_ID
AND AI.Payment_Currency_Code = FC.Currency_Code
GROUP BY AID.Invoice_ID, TRUNC(AID.Creation_Date), AIP.Check_ID) AID_Prepay,
(SELECT /*+ parallel(PP) */
PP.Invoice_ID Invoice_ID,
PP.Payment_Num Payment_Num,
PP.Due_Date Due_Date,
PP.Discount_Date Discount_Date,
PP.Second_Discount_Date Second_Discount_Date,
PP.Third_Discount_Date Third_Discount_Date,
PP.Entered_Date Entered_Date,
PP.PP_Amount,
(SUM(PP.PP_Amount) OVER (PARTITION BY PP.Invoice_ID
ORDER BY PP.Invoice_ID, PP.Payment_Num
ROWS UNBOUNDED PRECEDING)) - PP.PP_Amount AS First_PP,
SUM(PP.PP_Amount) OVER (PARTITION BY PP.Invoice_ID
ORDER BY PP.Invoice_ID, PP.Payment_Num
ROWS UNBOUNDED PRECEDING) AS Last_PP
FROM (SELECT /*+ use_hash(AI) parallel(AI) parallel(PS) parallel(PAY) parallel(TEMP) */
PS.Invoice_ID Invoice_ID,
PS.Payment_Num Payment_Num,
TRUNC(PS.Due_Date) Due_Date,
TRUNC(PS.Discount_Date) Discount_Date,
TRUNC(PS.Second_Discount_Date) Second_Discount_Date,
TRUNC(PS.Third_Discount_Date) Third_Discount_Date,
TRUNC(AI.Entered_Date) Entered_Date,
PS.Gross_Amount - PS.Amount_Remaining - NVL(PAY.Payment_Amount,0) - NVL(TEMP.WH_Tax_Amount, 0) PP_Amount
FROM FII_AP_INVOICE_B AI,
AP_PAYMENT_SCHEDULES_ALL PS,
(SELECT /*+ parallel(AI) parallel(AIP) parallel(PS) */
AIP.Invoice_ID Invoice_ID,
AIP.Payment_Num Payment_Num,
SUM(AIP.Amount + NVL(AIP.Discount_Taken, 0)) Payment_Amount
FROM AP_Invoice_Payments_All AIP,
FII_AP_Invoice_B AI,
AP_Payment_Schedules_All PS
WHERE PS.Invoice_ID = AI.Invoice_ID
AND AIP.Invoice_ID = AI.Invoice_ID
AND AI.Invoice_Type NOT IN ('PREPAYMENT')
AND AI.Cancel_Date IS NULL
AND AIP.Payment_Num = PS.Payment_Num
GROUP BY AIP.Invoice_ID, AIP.Payment_Num) PAY,
(SELECT /*+ parallel(STG) */ Invoice_ID,
Payment_Num,
SUM(WH_Tax_Amount) WH_Tax_Amount
FROM FII_AP_WH_TAX_T STG
GROUP BY Invoice_ID, Payment_Num) TEMP
WHERE PS.Invoice_ID = PAY.Invoice_ID (+)
AND PS.Payment_Num = PAY.Payment_Num (+)
AND PS.Invoice_ID = TEMP.Invoice_ID (+)
AND PS.Payment_Num = TEMP.Payment_Num (+)
AND AI.Invoice_ID = PS.Invoice_ID
ORDER BY PS.Invoice_ID, PS.Payment_Num) PP
WHERE PP.PP_Amount > 0) PS_Prepay
WHERE AID_Prepay.Invoice_ID = PS_Prepay.Invoice_ID
AND AID_Prepay.Prepymt > 0
AND ((PS_Prepay.First_PP + 1 <= AID_Prepay.First + 1 AND AID_Prepay.First + 1 <= PS_Prepay.Last_PP) OR
(AID_Prepay.First + 1 <= PS_Prepay.First_PP + 1 AND PS_Prepay.First_PP + 1 <= AID_Prepay.Last));
fii_util.put_line('Inserted '||SQL%ROWCOUNT||' records into FII_AP_Prepay_T');
END Insert_WH_Prepay_Amount;
Procedure INSERT_PAYMENT_CHECK_INFO IS
BEGIN
g_state := 'Inserting records into the FII_AP_PAY_CHK_STG table';
INSERT /*+ append parallel(S) */ INTO FII_AP_PAY_CHK_STG S
(Invoice_ID,
Payment_Num,
Check_Date,
Payment_Amount,
Discount_Taken,
Invoice_Type,
Due_Date,
Discount_Date,
Second_Discount_Date,
Third_Discount_Date,
Entered_Date,
Invp_Creation_Date)
SELECT /*+ use_hash(PS, AIP, AI, AC) parallel(PS) parallel(AIP) parallel(AI) parallel(AC) */
PS.Invoice_ID Invoice_Id,
PS.Payment_Num Payment_Num,
TRUNC(AC.Check_Date) Check_Date,
AIP.Amount + NVL(AIP.Discount_Taken,0) Payment_Amount,
NVL(AIP.Discount_Taken,0) Discount_Taken,
AI.Invoice_Type Invoice_Type,
TRUNC(PS.Due_Date) Due_Date,
TRUNC(PS.Discount_Date) Discount_Date,
TRUNC(PS.Second_Discount_Date) Second_Discount_Date,
TRUNC(PS.Third_Discount_Date) Third_Discount_Date,
TRUNC(AI.Entered_Date) Entered_Date,
TRUNC(AIP.Creation_Date) Invp_Creation_Date
FROM FII_AP_INVOICE_B AI,
AP_Checks_All AC,
AP_Invoice_Payments_All AIP,
AP_Payment_Schedules_All PS
WHERE AI.Invoice_ID = PS.Invoice_ID
AND PS.Invoice_ID = AIP.Invoice_ID
AND PS.Payment_Num = AIP.Payment_Num
AND AC.Check_ID = AIP.Check_ID
AND AC.Void_Date IS NULL;
fii_util.put_line('Inserted '||SQL%ROWCOUNT||' records into FII_AP_PAY_CHK_STG');
END Insert_Payment_Check_Info;
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 1
INTO l_miss_rates_ps
FROM FII_AP_PS_RATES_TEMP RATES
WHERE (RATES.Prim_Conversion_Rate < 0
OR RATES.Sec_Conversion_Rate < 0)
AND ROWNUM = 1;
SELECT 1
INTO l_miss_rates_func
FROM FII_AP_FUNC_RATES_TEMP RATES
WHERE RATES.Conversion_Rate < 0
AND ROWNUM = 1;
PROCEDURE Insert_Rates IS
l_host_var VARCHAR2(100);
INSERT /*+ append parallel(T) */
INTO FII_AP_PS_RATES_TEMP T
(Functional_Currency,
Trx_Date,
Prim_Conversion_Rate,
Sec_Conversion_Rate)
SELECT Curr_Code,
Trx_Date,
DECODE(Curr_Code, g_prim_currency, 1,
FII_CURRENCY.GET_GLOBAL_RATE_PRIMARY (Curr_Code, LEAST(Trx_Date,sysdate)))
PRIM_CONVERSION_RATE,
DECODE(Curr_Code, g_sec_currency, 1,
FII_CURRENCY.GET_GLOBAL_RATE_SECONDARY(Curr_Code, LEAST(Trx_Date,sysdate)))
SEC_CONVERSION_RATE
FROM (
SELECT /*+ parallel(ASP) parallel(AI) use_hash(AI) */
Distinct ASP.Base_Currency_code Curr_Code,
TRUNC(AI.Invoice_Date) Trx_Date
FROM AP_Invoices_All AI,
AP_System_Parameters_All ASP
WHERE AI.Org_ID = ASP.Org_ID
AND AI.Invoice_Type_Lookup_Code <> 'EXPENSE REPORT'
AND AI.Invoice_Amount <> 0
AND TRUNC(AI.Creation_Date) >= g_start_date
AND TRUNC(AI.Creation_Date) + 0 <= g_end_date + 0.99999);
INSERT /*+ append parallel(T)*/
INTO FII_AP_FUNC_RATES_TEMP T
(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 /*+ parallel(AI) parallel(ASP) parallel(FC) use_hash(AI,ASP,FC) */
Distinct AI.Payment_Currency_Code From_Currency,
ASP.Base_Currency_code To_Currency,
TRUNC(NVL(AI.Exchange_Date, AI.Invoice_Date)) Trx_Date,
NVL(AI.Exchange_Rate_Type,'No Rate Type') Exchange_Rate_Type,
DECODE(AI.Exchange_Rate_Type, 'User', AI.Exchange_Rate, 1)
Exchange_Rate,
NVL(FC.Minimum_Accountable_Unit, 0.01) Functional_MAU
FROM AP_Invoices_All AI,
AP_System_Parameters_All ASP,
FND_Currencies FC
WHERE AI.Org_ID = ASP.Org_ID
AND AI.Invoice_Type_Lookup_Code <> 'EXPENSE REPORT'
AND AI.Invoice_Amount <> 0
AND TRUNC(AI.Creation_Date) >= g_start_date
AND TRUNC(AI.Creation_Date) + 0 <= g_end_date + 0.99999
AND ASP.Base_Currency_Code = FC.Currency_Code);
END Insert_Rates;
INSERT /*+ append parallel(S) */ INTO FII_AP_INV_HOLDS_B S
(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 parallel(AH) parallel(AI) use_hash(AI,AH) */
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', 'PREPAID AMOUNT')
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 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 TRUNC(AH.Hold_Date) >= g_start_date
AND TRUNC(AH.Hold_Date) + 0 <= g_end_date + 0.99999;
FII_UTIL.put_line('Inserted '|| SQL%ROWCOUNT ||' records into FII_AP_INV_HOLDS_B');
'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_manual_sources := upper(g_manual_sources);
execute immediate('SELECT 1 FROM (SELECT '' '' SOURCE FROM DUAL)
WHERE SOURCE IN (' || g_manual_sources || ')');
/* Insert statement to insert the invoice records into the base summary
table. We will insert the maximum due_date, maximum discount offered
and the first hold date in this table. */
INSERT /*+ append parallel(S) */ INTO FII_AP_INVOICE_B S
(Org_ID,
Supplier_ID,
Invoice_ID,
Invoice_Type,
Invoice_Number,
Invoice_Date,
Invoice_Amount,
Invoice_Currency_Code,
Base_Currency_Code,
Exchange_Date,
Exchange_Rate,
Exchange_Rate_Type,
Entered_Date,
Created_By,
Payment_Currency_Code,
Payment_Status_Flag,
Payment_Cross_Rate,
Fully_Paid_Date,
Terms_ID,
Source,
E_Invoices_Flag,
Cancel_Flag,
Cancel_Date,
Dist_Count,
Base_Amount,
Prim_Amount,
Sec_Amount,
Discount_Offered,
Discount_Offered_B,
Prim_Discount_Offered,
Sec_Discount_Offered,
Fully_Paid_Amount,
Fully_Paid_Amount_B,
Prim_Fully_Paid_Amount,
Sec_Fully_Paid_Amount,
Due_Date,
Creation_Date,
Last_Updated_By,
Last_Update_Date,
Last_Update_Login)
SELECT /*+ leading(IB) parallel(IB) use_hash(PS,RATES,FRATES,AIP)
parallel(RATES) parallel(FRATES) */
IB.Org_ID,
IB.Supplier_ID,
IB.Invoice_ID,
IB.Invoice_Type,
IB.Invoice_Number,
IB.Invoice_Date,
IB.Invoice_Amount,
IB.Invoice_Currency_Code,
IB.Base_Currency_Code,
IB.Exchange_Date,
IB.Exchange_Rate,
IB.Exchange_Rate_Type,
IB.Entered_Date,
IB.Created_By,
IB.Payment_Currency_Code,
IB.Payment_Status_Flag,
IB.Payment_Cross_Rate,
DECODE(IB.Payment_Status_Flag, 'Y',
DECODE(AIP.Fully_Paid_Date, NULL, IB.Fully_Paid_Date,
DECODE(IB.Fully_Paid_Date, NULL, AIP.Fully_Paid_Date,
GREATEST(AIP.Fully_Paid_Date, IB.Fully_Paid_Date))), NULL) Fully_Paid_Date,
IB.Terms_ID,
IB.Source,
IB.E_Invoices_Flag,
IB.Cancel_Flag,
IB.Cancel_Date,
IB.Dist_Count,
ROUND((IB.Invoice_Amount * FRATES.Conversion_Rate)
/ Functional_MAU ) * Functional_MAU Base_Amount,
ROUND(DECODE(IB.Invoice_Currency_Code, g_prim_currency, IB.Invoice_Amount,
((IB.Invoice_Amount * FRATES.Conversion_Rate) * RATES.Prim_Conversion_Rate))
/ g_primary_mau) * g_primary_mau Prim_Amount,
ROUND(DECODE(IB.Invoice_Currency_Code, g_sec_currency, IB.Invoice_Amount,
((IB.Invoice_Amount * FRATES.Conversion_Rate) * RATES.Sec_Conversion_Rate))
/ g_secondary_mau) * g_secondary_mau Sec_Amount,
PS.Discount_Amount_Available Discount_Offered,
ROUND((PS.Discount_Amount_Available * FRATES.Conversion_Rate)
/ Functional_MAU ) * Functional_MAU Discount_Offered_B,
ROUND(DECODE(IB.Invoice_Currency_Code, g_prim_currency, PS.Discount_Amount_Available,
((PS.Discount_Amount_Available * FRATES.Conversion_Rate)
* RATES.Prim_Conversion_Rate)) / g_primary_mau) * g_primary_mau Prim_Discount_Offered,
ROUND(DECODE(IB.Invoice_Currency_Code, g_sec_currency, PS.Discount_Amount_Available,
((PS.Discount_Amount_Available * FRATES.Conversion_Rate)
* RATES.Sec_Conversion_Rate)) / g_secondary_mau) * g_secondary_mau Sec_Discount_Offered,
DECODE(IB.Payment_Status_Flag, 'Y',
NVL(AIP.Fully_Paid_Amount_IP,0) + NVL(IB.Fully_Paid_Amount_PP,0), NULL) Fully_Paid_Amount,
ROUND((DECODE(IB.Payment_Status_Flag, 'Y',
NVL(AIP.Fully_Paid_Amount_IP,0) + NVL(IB.Fully_Paid_Amount_PP,0), NULL) * FRATES.Conversion_Rate)
/ Functional_MAU) * Functional_MAU Fully_Paid_Amount_B,
ROUND(DECODE(IB.Invoice_Currency_Code, g_prim_currency,
DECODE(IB.Payment_Status_Flag, 'Y',
NVL(AIP.Fully_Paid_Amount_IP,0) + NVL(IB.Fully_Paid_Amount_PP,0), NULL),
((DECODE(IB.Payment_Status_Flag, 'Y',
NVL(AIP.Fully_Paid_Amount_IP,0) + NVL(IB.Fully_Paid_Amount_PP,0), NULL) * FRATES.Conversion_Rate)
* RATES.Prim_Conversion_Rate)) / g_primary_mau) * g_primary_mau Prim_Fully_Paid_Amount,
ROUND(DECODE(IB.Invoice_Currency_Code, g_sec_currency,
DECODE(IB.Payment_Status_Flag, 'Y',
NVL(AIP.Fully_Paid_Amount_IP,0) + NVL(IB.Fully_Paid_Amount_PP,0), NULL),
((DECODE(IB.Payment_Status_Flag, 'Y',
NVL(AIP.Fully_Paid_Amount_IP,0) + NVL(IB.Fully_Paid_Amount_PP,0), NULL) * FRATES.Conversion_Rate)
* RATES.Sec_Conversion_Rate)) / g_secondary_mau) * g_secondary_mau Sec_Fully_Paid_Amount,
PS.Due_Date,
IB.Creation_Date,
IB.Last_Updated_By,
IB.Last_Update_Date,
IB.Last_Update_Login
FROM
(SELECT /*+ no_merge ordered use_hash(AI,AID) parallel(IB) parallel(AI) parallel(AID) */
AI.Org_ID,
AI.Vendor_ID Supplier_id,
AI.Invoice_ID,
AI.Invoice_Type_Lookup_Code Invoice_type,
AI.Invoice_Num Invoice_Number,
TRUNC(AI.Invoice_Date) Invoice_Date,
AI.Invoice_Amount,
AI.Invoice_Currency_Code,
ASP.Base_Currency_Code,
TRUNC(NVL(AI.Exchange_Date, AI.Invoice_Date)) Exchange_Date,
AI.Exchange_Rate,
NVL(AI.Exchange_Rate_Type, 'No Rate Type') Exchange_Rate_Type,
TRUNC(AI.Creation_Date) Entered_Date ,
AI.Created_By ,
AI.Payment_Currency_Code,
AI.Payment_Status_Flag,
AI.Payment_Cross_Rate,
Decode(AI.Payment_Status_Flag, 'Y',
TRUNC(MAX(CASE WHEN 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')
THEN AID.Creation_Date
ELSE NULL END)), NULL) Fully_Paid_Date,
Decode(AI.Payment_Status_Flag, 'Y',
SUM(CASE WHEN 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')
THEN -1 * AID.Amount
ELSE 0 END), 0) Fully_Paid_Amount_PP,
AI.Terms_ID,
AI.Source,
CASE WHEN g_manual_sources like '%''' || to_char(upper(AI.Source)) || '''%'
--upper(AI.Source) IN (g_manual_sources)
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,
sysdate Creation_Date,
g_fii_user_id Last_Updated_By,
sysdate Last_Update_Date,
g_fii_login_id Last_Update_Login
FROM AP_System_Parameters_All ASP,
AP_Invoices_All AI,
AP_Invoice_Distributions_All AID,
AP_Invoice_Lines_All AIL
WHERE AI.Invoice_ID = AIL.Invoice_ID (+)
AND AIL.Invoice_ID = AID.Invoice_ID (+)
AND AIL.Line_Number = AID.Invoice_Line_Number (+)
AND AI.Org_ID = ASP.Org_ID
AND AI.Invoice_Type_Lookup_Code NOT IN ('EXPENSE REPORT')
AND AI.Invoice_Amount <> 0
AND TRUNC(AI.Creation_Date) >= g_start_date
AND TRUNC(AI.Creation_Date) + 0 <= g_end_date + 0.99999
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,
ASP.Base_Currency_Code,
AI.Base_Amount,
AI.Invoice_Currency_Code,
AI.Payment_Currency_Code,
AI.Exchange_Date,
AI.Exchange_Rate,
AI.Exchange_Rate_Type,
AI.Creation_Date,
AI.Created_By,
AI.Payment_Status_Flag,
AI.Payment_Cross_Rate,
AI.Terms_ID,
AI.Source,
AI.Cancelled_Date) IB,
(SELECT /*+ no_merge parallel(PS) */
PS.Invoice_ID,
SUM(NVL(PS.Discount_Amount_Available,0)) Discount_Amount_Available,
TRUNC(MIN(PS.Due_Date)) Due_Date
FROM AP_Payment_Schedules_all PS
GROUP BY PS.Invoice_ID ) PS,
(SELECT /*+ no_merge parallel(AIP) */
AIP.Invoice_id Invoice_ID,
TRUNC(MAX(AIP.Creation_Date)) Fully_Paid_Date,
SUM(AIP.Amount + NVL(AIP.Discount_Taken,0)) Fully_Paid_Amount_IP
FROM AP_Invoice_Payments_All AIP
GROUP BY AIP.Invoice_ID ) AIP,
FII_AP_PS_Rates_Temp RATES,
FII_AP_Func_Rates_Temp FRATES
WHERE IB.Invoice_ID = PS.Invoice_ID
AND IB.Invoice_ID = AIP.Invoice_ID (+)
AND IB.Invoice_Date = RATES.Trx_Date
AND IB.Base_Currency_Code = RATES.Functional_Currency
AND IB.Payment_Currency_Code = FRATES.From_Currency
AND IB.Exchange_Date = FRATES.Trx_Date
AND IB.Exchange_Rate_Type = FRATES.Conversion_Type
AND DECODE(IB.Exchange_Rate_Type,'User', IB.Exchange_Rate,1) =
DECODE(FRATES.Conversion_Type,'User', FRATES.Conversion_Rate,1)
AND IB.Base_Currency_Code = FRATES.To_Currency;
FII_UTIL.put_line('Inserted ' ||SQL%ROWCOUNT|| ' records into FII_AP_INVOICE_B');
/* Insert statement to insert all the payment information into the summary
table including the payments made for a prepayment invoice.
We will record the creation date as the action date and not the check date
for a payment. If we record the check date as the action date then the
action date for a voided payment would be the same as the payment creation
and the report would show wrong results for past periods. */
INSERT INTO FII_AP_PAY_SCHED_B b
(Time_ID,
Period_Type_ID,
Action_Date,
Action,
Update_Sequence,
Org_ID,
Supplier_ID,
Invoice_ID,
Base_Currency_Code,
Trx_Date,
Payment_Num,
Due_Date,
Created_By,
Amount_Remaining,
Past_Due_Amount,
Discount_Available,
Discount_Taken,
Discount_Lost,
Payment_Amount,
On_Time_Payment_Amt,
Late_Payment_Amt,
No_Days_Late,
Due_Bucket1,
Due_Bucket2,
Due_Bucket3,
Past_Due_Bucket1,
Past_Due_Bucket2,
Past_Due_Bucket3,
Amount_Remaining_B,
Past_Due_Amount_B,
Discount_Available_B,
Discount_Taken_B,
Discount_Lost_B,
Payment_Amount_B,
On_Time_Payment_Amt_B,
Late_Payment_Amt_B,
Due_Bucket1_B,
Due_Bucket2_B,
Due_Bucket3_B,
Past_Due_Bucket1_B,
Past_Due_Bucket2_B,
Past_Due_Bucket3_B,
Prim_Amount_Remaining,
Prim_Past_Due_Amount,
Prim_Discount_Available,
Prim_Discount_Taken,
Prim_Discount_Lost,
Prim_Payment_Amount,
Prim_On_time_Payment_Amt,
Prim_Late_Payment_Amt,
Prim_Due_Bucket1,
Prim_Due_Bucket2,
Prim_Due_Bucket3,
Prim_Past_Due_Bucket1,
Prim_Past_Due_Bucket2,
Prim_Past_Due_Bucket3,
Sec_Amount_Remaining,
Sec_Past_Due_Amount,
Sec_Discount_Available,
Sec_Discount_Taken,
Sec_Discount_Lost,
Sec_Payment_Amount,
Sec_On_time_Payment_Amt,
Sec_Late_Payment_Amt,
Sec_Due_Bucket1,
Sec_Due_Bucket2,
Sec_Due_Bucket3,
Sec_Past_Due_Bucket1,
Sec_Past_Due_Bucket2,
Sec_Past_Due_Bucket3,
Check_ID,
Check_Date,
Payment_Method,
Creation_Date,
Last_Updated_By,
Last_Update_Date,
Last_Update_Login,
Inv_Pymt_Flag,
Unique_ID)
SELECT /*+ MERGE(PSUM) use_nl(frates,rates) */
TO_NUMBER(TO_CHAR(Action_Date,'J')) Time_ID,
1 Period_Type_ID,
Action_Date,
Action,
g_seq_id Update_Sequence,
Org_ID,
Supplier_ID,
Invoice_ID,
Base_Currency_Code,
Invoice_Date,
Payment_Num,
Due_Date,
Created_By,
Amount_Remaining,
Past_Due_Amount,
Discount_Available,
Discount_Taken,
Discount_Lost,
Payment_Amount,
On_Time_Payment_Amt,
Late_Payment_Amt,
No_Days_Late,
Due_Bucket1,
Due_Bucket2,
Due_Bucket3,
Past_Due_Bucket1,
Past_Due_Bucket2,
Past_Due_Bucket3,
ROUND((Amount_Remaining * Conversion_Rate) / Functional_MAU) * Functional_MAU Amount_Remaining_B,
ROUND((Past_Due_Amount * Conversion_Rate) / Functional_MAU) * Functional_MAU Past_Due_Amount_B,
ROUND((Discount_Available * Conversion_Rate) / Functional_MAU) * Functional_MAU Discount_Available_B,
ROUND((Discount_Taken * Conversion_Rate) / Functional_MAU) * Functional_MAU Discount_Taken_B,
ROUND((Discount_Lost * Conversion_Rate) / Functional_MAU) * Functional_MAU Discount_Lost_B,
ROUND((Payment_Amount * Conversion_Rate) / Functional_MAU) * Functional_MAU Payment_Amount_B,
ROUND((On_Time_Payment_Amt * Conversion_Rate) / Functional_MAU) * Functional_MAU On_Time_Payment_Amt_B,
ROUND((Late_Payment_Amt * Conversion_Rate) / Functional_MAU) * Functional_MAU Last_Payment_Amt_B,
ROUND((Due_Bucket1 * Conversion_Rate) / Functional_MAU) * Functional_MAU Due_Bucket1_B,
ROUND((Due_Bucket2 * Conversion_Rate) / Functional_MAU) * Functional_MAU Due_Bucket2_B,
ROUND((Due_Bucket3 * Conversion_Rate) / Functional_MAU) * Functional_MAU Due_Bucket3_B,
ROUND((Past_Due_Bucket1 * Conversion_Rate) / Functional_MAU) * Functional_MAU Past_Due_Bucket1_B,
ROUND((Past_Due_Bucket2 * Conversion_Rate) / Functional_MAU) * Functional_MAU Past_Due_Bucket2_B,
ROUND((Past_Due_Bucket3 * Conversion_Rate) / Functional_MAU) * Functional_MAU Past_Due_Bucket3_B,
ROUND(DECODE(Invoice_Currency_Code, g_prim_currency, Amount_Remaining,
((Amount_Remaining * Conversion_Rate) * RATES.Prim_Conversion_Rate))
/ g_primary_mau) * g_primary_mau Prim_Amount_Remaining,
ROUND(DECODE(Invoice_Currency_Code, g_prim_currency, Past_Due_Amount,
((Past_Due_Amount * Conversion_Rate) * RATES.Prim_Conversion_Rate))
/ g_primary_mau) * g_primary_mau Prim_Past_Due_Amount,
ROUND(DECODE(Invoice_Currency_Code, g_prim_currency, Discount_Available,
((Discount_Available * Conversion_Rate) * RATES.Prim_Conversion_Rate))
/ g_primary_mau) * g_primary_mau Prim_Discount_Available,
ROUND(DECODE(Invoice_Currency_Code, g_prim_currency, Discount_Taken,
((Discount_Taken * Conversion_Rate) * RATES.Prim_Conversion_Rate))
/ g_primary_mau) * g_primary_mau Prim_Discount_Taken,
ROUND(DECODE(Invoice_Currency_Code, g_prim_currency, Discount_Lost,
((Discount_Lost * Conversion_Rate) * RATES.Prim_Conversion_Rate))
/ g_primary_mau) * g_primary_mau Prim_Discount_Lost,
ROUND(DECODE(Invoice_Currency_Code, g_prim_currency, Payment_Amount,
((Payment_Amount * Conversion_Rate) * RATES.Prim_Conversion_Rate))
/ g_primary_mau) * g_primary_mau Prim_Payment_Amount,
ROUND(DECODE(Invoice_Currency_Code, g_prim_currency, On_Time_Payment_Amt,
((On_Time_Payment_Amt * Conversion_Rate) * RATES.Prim_Conversion_Rate))
/ g_primary_mau) * g_primary_mau Prim_On_Time_Payment_Amt,
ROUND(DECODE(Invoice_Currency_Code, g_prim_currency, Late_Payment_Amt,
((Late_Payment_Amt * Conversion_Rate) * RATES.Prim_Conversion_Rate))
/ g_primary_mau) * g_primary_mau Prim_Late_Payment_Amt,
ROUND(DECODE(Invoice_Currency_Code, g_prim_currency, Due_Bucket1,
((Due_Bucket1 * Conversion_Rate) * RATES.Prim_Conversion_Rate))
/ g_primary_mau) * g_primary_mau Prim_Due_Bucket1,
ROUND(DECODE(Invoice_Currency_Code, g_prim_currency, Due_Bucket2,
((Due_Bucket2 * Conversion_Rate) * RATES.Prim_Conversion_Rate))
/ g_primary_mau) * g_primary_mau Prim_Due_Bucket2,
ROUND(DECODE(Invoice_Currency_Code, g_prim_currency, Due_Bucket3,
((Due_Bucket3 * Conversion_Rate) * RATES.Prim_Conversion_Rate))
/ g_primary_mau) * g_primary_mau Prim_Due_Bucket3,
ROUND(DECODE(Invoice_Currency_Code, g_prim_currency, Past_Due_Bucket1,
((Past_Due_Bucket1 * Conversion_Rate) * RATES.Prim_Conversion_Rate))
/ g_primary_mau) * g_primary_mau Prim_Past_Due_Bucket1,
ROUND(DECODE(Invoice_Currency_Code, g_prim_currency, Past_Due_Bucket2,
((Past_Due_Bucket2 * Conversion_Rate) * RATES.Prim_Conversion_Rate))
/ g_primary_mau) * g_primary_mau Prim_Past_Due_Bucket2,
ROUND(DECODE(Invoice_Currency_Code, g_prim_currency, Past_Due_Bucket3,
((Past_Due_Bucket3 * Conversion_Rate) * RATES.Prim_Conversion_Rate))
/ g_primary_mau) * g_primary_mau Prim_Past_Due_Bucket3,
ROUND(DECODE(Invoice_Currency_Code, g_sec_currency, Amount_Remaining,
((Amount_Remaining * Conversion_Rate) * RATES.Sec_Conversion_Rate))
/ g_secondary_mau) * g_secondary_mau Sec_Amount_Remaining,
ROUND(DECODE(Invoice_Currency_Code, g_sec_currency, Past_Due_Amount,
((Past_Due_Amount * Conversion_Rate) * RATES.Sec_Conversion_Rate))
/ g_secondary_mau) * g_secondary_mau Sec_Past_Due_Amount,
ROUND(DECODE(Invoice_Currency_Code, g_sec_currency, Discount_Available,
((Discount_Available * Conversion_Rate) * RATES.Sec_Conversion_Rate))
/ g_secondary_mau) * g_secondary_mau Sec_Discount_Available,
ROUND(DECODE(Invoice_Currency_Code, g_sec_currency, Discount_Taken,
((Discount_Taken * Conversion_Rate) * RATES.Sec_Conversion_Rate))
/ g_secondary_mau) * g_secondary_mau Sec_Discount_Taken,
ROUND(DECODE(Invoice_Currency_Code, g_sec_currency, Discount_Lost,
((Discount_Lost * Conversion_Rate) * RATES.Sec_Conversion_Rate))
/ g_secondary_mau) * g_secondary_mau Sec_Discount_Lost,
ROUND(DECODE(Invoice_Currency_Code, g_sec_currency, Payment_Amount,
((Payment_Amount * Conversion_Rate) * RATES.Sec_Conversion_Rate))
/ g_secondary_mau) * g_secondary_mau Sec_Payment_Amount,
ROUND(DECODE(Invoice_Currency_Code, g_sec_currency, On_Time_Payment_Amt,
((On_Time_Payment_Amt * Conversion_Rate) * RATES.Sec_Conversion_Rate))
/ g_secondary_mau) * g_secondary_mau Sec_On_Time_Payment_Amt,
ROUND(DECODE(Invoice_Currency_Code, g_sec_currency, Late_Payment_Amt,
((Late_Payment_Amt * Conversion_Rate) * RATES.Sec_Conversion_Rate))
/ g_secondary_mau) * g_secondary_mau Sec_Late_Payment_Amt,
ROUND(DECODE(Invoice_Currency_Code, g_sec_currency, Due_Bucket1,
((Due_Bucket1 * Conversion_Rate) * RATES.Sec_Conversion_Rate))
/ g_secondary_mau) * g_secondary_mau Sec_Due_Bucket1,
ROUND(DECODE(Invoice_Currency_Code, g_sec_currency, Due_Bucket2,
((Due_Bucket2 * Conversion_Rate) * RATES.Sec_Conversion_Rate))
/ g_secondary_mau) * g_secondary_mau Sec_Due_Bucket2,
ROUND(DECODE(Invoice_Currency_Code, g_sec_currency, Due_Bucket3,
((Due_Bucket3 * Conversion_Rate) * RATES.Sec_Conversion_Rate))
/ g_secondary_mau) * g_secondary_mau Sec_Due_Bucket3,
ROUND(DECODE(Invoice_Currency_Code, g_sec_currency, Past_Due_Bucket1,
((Past_Due_Bucket1 * Conversion_Rate) * RATES.Sec_Conversion_Rate))
/ g_secondary_mau) * g_secondary_mau Sec_Past_Due_Bucket1,
ROUND(DECODE(Invoice_Currency_Code, g_sec_currency, Past_Due_Bucket2,
((Past_Due_Bucket2 * Conversion_Rate) * RATES.Sec_Conversion_Rate))
/ g_secondary_mau) * g_secondary_mau Sec_Past_Due_Bucket2,
ROUND(DECODE(Invoice_Currency_Code, g_sec_currency, Past_Due_Bucket3,
((Past_Due_Bucket3 * Conversion_Rate) * RATES.Sec_Conversion_Rate))
/ g_secondary_mau) * g_secondary_mau Sec_Past_Due_Bucket3,
Check_ID,
Check_Date,
Payment_Method,
sysdate Creation_Date,
g_fii_user_id Last_Updated_By,
sysdate Last_Update_Date,
g_fii_login_id Last_Update_Login,
'Y' Inv_Pymt_Flag,
Invoice_Payment_ID Unique_ID
FROM
(SELECT /*+ leading(aip) merge(aip) use_nl(ai) use_nl(PS) use_nl(AIP) use_nl(apc) */
TRUNC(AIP.Creation_Date) Action_Date,
DECODE(AI.Invoice_Type, 'PREPAYMENT', 'PREPAYMENT', 'PAYMENT') Action,
AI.Org_ID Org_ID,
AI.Supplier_ID Supplier_ID,
AI.Invoice_ID Invoice_ID,
AI.Invoice_Currency_Code Invoice_Currency_Code,
AI.Base_Currency_Code Base_Currency_Code,
AI.Invoice_Date Invoice_Date,
AI.Payment_Currency_Code Payment_Currency_Code,
AI.Exchange_Rate Exchange_Rate,
AI.Exchange_Date Exchange_Date,
AI.Exchange_Rate_Type Exchange_Rate_Type,
PS.Payment_Num Payment_Num,
TRUNC(PS.Due_Date) Due_Date,
AIP.Created_By Created_By,
-1 * (AIP.Amount + NVL(AIP.Discount_Taken,0)) Amount_Remaining,
DECODE(AI.Invoice_Type, 'PREPAYMENT', 0,
DECODE(SIGN(TRUNC(PS.Due_Date) - TRUNC(AIP.Creation_Date)), -1,
-1 * (AIP.Amount + NVL(AIP.Discount_Taken,0)), 0)) Past_Due_Amount,
-1 * NVL(AIP.Discount_Taken,0) Discount_Available,
NVL(AIP.Discount_Taken,0) Discount_Taken,
0 Discount_Lost,
AIP.Amount Payment_Amount,
DECODE(SIGN(TRUNC(PS.Due_Date) - TRUNC(AIP.Creation_Date)), -1, 0,
AIP.Amount) On_Time_Payment_Amt,
DECODE(SIGN(TRUNC(PS.Due_Date) - TRUNC(AIP.Creation_Date)), -1,
AIP.Amount, 0) Late_Payment_Amt,
DECODE(SIGN(TRUNC(PS.Due_Date) - TRUNC(AIP.Creation_Date)), -1,
(TRUNC(AIP.Creation_Date) - TRUNC(PS.Due_Date)), 0) No_Days_Late,
CASE
WHEN (AI.Invoice_Type <> 'PREPAYMENT')
AND (TRUNC(PS.Due_Date) - TRUNC(AIP.Creation_Date)) >= g_due_bucket1
THEN -1 * (AIP.Amount + NVL(AIP.Discount_Taken,0))
ELSE 0
END Due_Bucket1,
CASE
WHEN (AI.Invoice_Type <> 'PREPAYMENT')
AND (TRUNC(PS.Due_Date) - TRUNC(AIP.Creation_Date)) <= g_due_bucket2
AND (TRUNC(PS.Due_Date) - TRUNC(AIP.Creation_Date)) > g_due_bucket3
THEN -1 * (AIP.Amount + NVL(AIP.Discount_Taken,0))
ELSE 0
END Due_Bucket2,
CASE
WHEN (AI.Invoice_Type <> 'PREPAYMENT')
AND (TRUNC(PS.Due_Date) - TRUNC(AIP.Creation_Date)) <= g_due_bucket3
AND (TRUNC(PS.Due_Date) - TRUNC(AIP.Creation_Date)) >= 0
THEN -1 * (AIP.Amount + NVL(AIP.Discount_Taken,0))
ELSE 0
END Due_Bucket3,
CASE
WHEN (AI.Invoice_Type <> 'PREPAYMENT')
AND (TRUNC(AIP.Creation_Date) - TRUNC(PS.Due_Date)) >= g_past_bucket1
THEN -1 * (AIP.Amount + NVL(AIP.Discount_Taken,0))
ELSE 0
END Past_Due_Bucket1,
CASE
WHEN (AI.Invoice_Type <> 'PREPAYMENT')
AND (TRUNC(AIP.Creation_Date) - TRUNC(PS.Due_Date)) <= g_past_bucket2
AND (TRUNC(AIP.Creation_Date) - TRUNC(PS.Due_Date)) > g_past_bucket3
THEN -1 * (AIP.Amount + NVL(AIP.Discount_Taken,0))
ELSE 0
END Past_Due_Bucket2,
CASE
WHEN (AI.Invoice_Type <> 'PREPAYMENT')
AND (TRUNC(AIP.Creation_Date) - TRUNC(PS.Due_Date)) <= g_past_bucket3
AND (TRUNC(AIP.Creation_Date) - TRUNC(PS.Due_Date)) > 0
THEN -1 * (AIP.Amount + NVL(AIP.Discount_Taken,0))
ELSE 0
END Past_Due_Bucket3,
AIP.Check_ID Check_ID,
AC.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')) PAYMENT_METHOD,
AIP.Invoice_Payment_ID
FROM FII_AP_Invoice_B AI,
AP_Payment_Schedules_All PS,
(SELECT /*+ leading(aip_pp) use_nl(aip_pp,pp) */ AIP_PP.Invoice_ID,
AIP_PP.Payment_Num,
AIP_PP.Creation_Date,
AIP_PP.Created_By,
AIP_PP.Amount,
AIP_PP.Discount_Taken,
AIP_PP.Check_ID,
AIP_PP.WH_Tax_Amount + NVL(SUM(CASE WHEN PP.Creation_Date IS NOT NULL
AND PP.Creation_Date <= AIP_PP.Creation_Date
THEN PP.Prepay_Amount ELSE 0 END),0) Prepay_WH_Tax_Amount,
AIP_PP.Invoice_Payment_ID
FROM
(SELECT /*+ use_nl(aip_wh,temp) */ AIP_WH.Invoice_ID,
AIP_WH.Payment_Num,
AIP_WH.Creation_Date,
AIP_WH.Created_By,
AIP_WH.Amount,
AIP_WH.Discount_Taken,
AIP_WH.Check_ID,
NVL(SUM(CASE WHEN TEMP.Creation_Date IS NOT NULL
AND TEMP.Creation_Date <= TRUNC(AIP_WH.Creation_Date)
THEN TEMP.WH_Tax_Amount ELSE 0 END),0) WH_Tax_Amount,
AIP_WH.Invoice_Payment_ID
FROM AP_Invoice_Payments_All AIP_WH,
FII_AP_WH_Tax_T TEMP
WHERE AIP_WH.Invoice_ID BETWEEN g_start_range and g_end_range
AND AIP_WH.Invoice_ID = TEMP.Invoice_ID (+)
AND AIP_WH.Payment_Num = TEMP.Payment_Num (+)
GROUP BY AIP_WH.Invoice_ID,
AIP_WH.Payment_Num,
AIP_WH.Creation_Date,
AIP_WH.Created_By,
AIP_WH.Amount,
AIP_WH.Discount_Taken,
AIP_WH.Check_ID,
AIP_WH.Invoice_Payment_ID) AIP_PP,
FII_AP_Prepay_T PP
WHERE AIP_PP.Invoice_ID = PP.Invoice_ID (+)
AND AIP_PP.Payment_Num = PP.Payment_Num (+)
GROUP BY AIP_PP.Invoice_ID,
AIP_PP.Payment_Num,
AIP_PP.Creation_Date,
AIP_PP.Created_By,
AIP_PP.Amount,
AIP_PP.Discount_Taken,
AIP_PP.Check_ID,
AIP_PP.WH_Tax_Amount,
AIP_PP.Invoice_Payment_ID) AIP,
FII_AP_PAY_CHK_STG APC,
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 AI.Invoice_ID = PS.Invoice_ID
AND AI.Cancel_Date IS NULL
AND AIP.Invoice_ID = PS.Invoice_ID
AND AIP.Payment_Num = PS.Payment_Num
AND AIP.Check_ID = AC.Check_ID
AND APC.Invoice_ID = PS.Invoice_ID
AND APC.Payment_Num = PS.Payment_Num
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 APC.Invp_Creation_Date <= TRUNC(AIP.Creation_Date)
AND AC.Void_Date IS NULL
HAVING SUM(APC.Payment_Amount) + AIP.Prepay_WH_Tax_Amount <> PS.Gross_Amount
GROUP BY AI.Org_ID, AI.Supplier_ID,
AI.Invoice_ID,
AI.Invoice_Currency_Code,
AI.Base_Currency_Code,
AI.Invoice_Date,
AI.Invoice_Type,
AI.Payment_Currency_Code,
AI.Exchange_Rate,
AI.Exchange_Date,
AI.Exchange_Rate_Type,
PS.Payment_Num,
PS.Due_Date,
AIP.Amount,
AIP.Created_By,
AIP.Check_ID,
AC.Check_Date,
IBY_SYS_PROF_B.Processing_Type,
AC.Payment_Method_Lookup_Code,
-- AC.PAYMENT_PROFILE_ID,
AIP.Creation_Date,
NVL(AIP.Discount_Taken,0),
PS.Gross_Amount,
AIP.Prepay_WH_Tax_Amount,
AIP.Invoice_Payment_ID) PSUM,
FII_AP_PS_Rates_Temp RATES,
FII_AP_Func_Rates_Temp FRATES
WHERE FRATES.To_Currency = PSUM.Base_Currency_Code
AND FRATES.From_Currency = PSUM.Payment_Currency_Code
AND FRATES.Trx_Date = PSUM.Exchange_Date
AND DECODE(PSUM.Exchange_Rate_Type,'User', PSUM.Exchange_Rate,1) =
DECODE(FRATES.Conversion_Type,'User', FRATES.Conversion_Rate,1)
AND FRATES.Conversion_Type = PSUM.Exchange_Rate_Type
AND RATES.Functional_Currency = PSUM.Base_Currency_Code
AND RATES.Trx_Date = PSUM.Invoice_Date;
FII_UTIL.put_line('Inserted ' ||SQL%ROWCOUNT|| ' Payment records into FII_AP_PAY_SCHED_B');
/* We will first insert the last payment of a payment schedule into a temp table
because we want to adjust the discount available and discount lost for the last
payment as the discounts can be taken after the discount dates and also more
discounts can be taken. This adjustment will ensure that the sum of the
discount available will be zero and the discount lost amount will be
discount available - taken.
Since we cannot insert and select from the same table at the same time we will
select from the pay sched sum table and insert into the temp table */
INSERT INTO FII_AP_PAY_SCHED_TEMP
(Time_ID,
Period_Type_ID,
Action_Date,
Action,
Update_Sequence,
Org_ID,
Supplier_ID,
Invoice_ID,
Base_Currency_Code,
Trx_Date,
Payment_Num,
Due_Date,
Created_By,
Amount_Remaining,
Past_Due_Amount,
Discount_Available,
Discount_Taken,
Discount_Lost,
Payment_Amount,
On_Time_Payment_Amt,
Late_Payment_Amt,
No_Days_Late,
Due_Bucket1,
Due_Bucket2,
Due_Bucket3,
Past_Due_Bucket1,
Past_Due_Bucket2,
Past_Due_Bucket3,
Fully_Paid_Date,
Check_ID,
Check_Date,
Payment_Method,
Inv_Pymt_Flag,
Unique_ID)
SELECT /*+ ordered use_nl(PS) index(ai, FII_AP_INVOICE_B_U1 )
use_nl(ai) use_nl(apc)*/
TO_NUMBER(TO_CHAR(AIP.Creation_Date,'J')) Time_ID,
1 Period_Type_ID,
TRUNC(AIP.Creation_Date) Action_Date,
DECODE(AI.Invoice_Type, 'PREPAYMENT', 'PREPAYMENT', 'PAYMENT') Action,
g_seq_id Update_Sequence,
AI.Org_ID Org_ID,
AI.Supplier_ID Supplier_ID,
AI.Invoice_ID Invoice_ID,
AI.Base_Currency_Code Base_Currency_Code,
AI.Invoice_Date Trx_Date,
PS.Payment_Num Payment_Num,
TRUNC(PS.Due_Date) Due_Date,
AIP.Created_By Created_By,
-1 * (AIP.Amount + NVL(AIP.Discount_Taken,0)) Amount_Remaining,
DECODE(AI.Invoice_Type, 'PREPAYMENT', 0,
DECODE(SIGN(TRUNC(PS.Due_Date) - TRUNC(AIP.Creation_Date)), -1,
-1 * ((AIP.Amount + NVL(AIP.Discount_Taken,0))),
0)) Past_Due_Amount,
-1 * NVL(DISC.Discount_Available,0) Discount_Available,
NVL(AIP.Discount_Taken,0) Discount_Taken,
GREATEST(NVL(PS.Discount_Amount_Available,0),
NVL(PS.Second_Disc_Amt_Available,0),
NVL(PS.Third_Disc_Amt_Available,0))
- NVL(DISC.Discount_Taken,0) - NVL(DISC.Discount_Lost,0)
- NVL(AIP.Discount_Taken,0) Discount_Lost,
AIP.Amount Payment_Amount,
DECODE(SIGN(TRUNC(PS.Due_Date) - TRUNC(AIP.Creation_Date)), -1, 0, AIP.Amount) On_Time_Payment_Amt,
DECODE(SIGN(TRUNC(PS.Due_Date) - TRUNC(AIP.Creation_Date)), -1, AIP.Amount, 0) Late_Payment_Amt,
DECODE(SIGN(TRUNC(PS.Due_Date) - TRUNC(AIP.Creation_Date)), -1,
(TRUNC(AIP.Creation_Date) - TRUNC(PS.Due_Date)), 0) No_Days_Late,
CASE
WHEN (AI.Invoice_Type <> 'PREPAYMENT')
AND (TRUNC(PS.Due_Date) - TRUNC(AIP.Creation_Date)) >= g_due_bucket1
THEN -1 * ((AIP.Amount + NVL(AIP.Discount_Taken,0)))
ELSE 0
END Due_Bucket1,
CASE
WHEN (AI.Invoice_Type <> 'PREPAYMENT')
AND (TRUNC(PS.Due_Date) - TRUNC(AIP.Creation_Date)) <= g_due_bucket2
AND (TRUNC(PS.Due_Date) - TRUNC(AIP.Creation_Date)) > g_due_bucket3
THEN -1 * ((AIP.Amount + NVL(AIP.Discount_Taken,0)))
ELSE 0
END Due_Bucket2,
CASE
WHEN (AI.Invoice_Type <> 'PREPAYMENT')
AND (TRUNC(PS.Due_Date) - TRUNC(AIP.Creation_Date)) <= g_due_bucket3
AND (TRUNC(PS.Due_Date) - TRUNC(AIP.Creation_Date)) >= 0
THEN -1 * ((AIP.Amount + NVL(AIP.Discount_Taken,0)))
ELSE 0
END Due_Bucket3,
CASE
WHEN (AI.Invoice_Type <> 'PREPAYMENT')
AND (TRUNC(AIP.Creation_Date) - TRUNC(PS.Due_Date)) > = g_past_bucket1
THEN -1 * ((AIP.Amount + NVL(AIP.Discount_Taken,0)))
ELSE 0
END Past_Due_Bucket1,
CASE
WHEN (AI.Invoice_Type <> 'PREPAYMENT')
AND (TRUNC(AIP.Creation_Date) - TRUNC(PS.Due_Date)) <= g_past_bucket2
AND (TRUNC(AIP.Creation_Date) - TRUNC(PS.Due_Date)) > g_past_bucket3
THEN -1 * ((AIP.Amount + NVL(AIP.Discount_Taken,0)))
ELSE 0
END Past_Due_Bucket2,
CASE
WHEN (AI.Invoice_Type <> 'PREPAYMENT')
AND (TRUNC(AIP.Creation_Date) - TRUNC(PS.Due_Date)) <= g_past_bucket3
AND (TRUNC(AIP.Creation_Date) - TRUNC(PS.Due_Date)) > 0
THEN -1 * ((AIP.Amount + NVL(AIP.Discount_Taken,0)))
ELSE 0
END Past_Due_Bucket3,
TRUNC(AIP.Creation_Date) Fully_Paid_Date,
AIP.Check_ID Check_ID,
AC.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')) PAYMENT_METHOD,
'Y' Inv_Pymt_Flag,
AIP.Invoice_Payment_ID Unique_ID
FROM
(SELECT /*+ use_nl(PSUM) */
PS.Invoice_ID Invoice_ID,
PS.Payment_Num Payment_Num,
SUM(Discount_Available) Discount_Available,
SUM(Discount_Lost) Discount_Lost,
SUM(Discount_Taken) Discount_Taken
FROM FII_AP_Pay_Sched_B PSUM,
AP_Payment_Schedules_All PS
WHERE PS.Invoice_ID BETWEEN g_start_range and g_end_range
AND PS.Invoice_ID = PSUM.Invoice_ID (+)
AND PS.Payment_Num = PSUM.Payment_Num (+)
AND PSUM.Period_Type_ID (+) = 1
GROUP BY PS.Invoice_ID,
PS.Payment_Num) DISC,
AP_Payment_Schedules_All PS,
FII_AP_Invoice_B AI,
(SELECT /*+ merge(aip_pp) use_nl(aip_pp, pp) */
AIP_PP.Invoice_ID,
AIP_PP.Payment_Num,
AIP_PP.Creation_Date,
AIP_PP.Created_By,
AIP_PP.Amount,
AIP_PP.Discount_Taken,
AIP_PP.Check_ID,
AIP_PP.WH_Tax_Amount + NVL(SUM(CASE WHEN PP.Creation_Date IS NOT NULL
AND PP.Creation_Date <= AIP_PP.Creation_Date
THEN PP.Prepay_Amount ELSE 0 END),0) Prepay_WH_Tax_Amount,
AIP_PP.Invoice_Payment_ID
FROM
(SELECT /*+ use_nl(aip_wh,temp) */ AIP_WH.Invoice_ID,
AIP_WH.Payment_Num,
AIP_WH.Creation_Date,
AIP_WH.Created_By,
AIP_WH.Amount,
AIP_WH.Discount_Taken,
AIP_WH.Check_ID,
NVL(SUM(CASE WHEN TEMP.Creation_Date IS NOT NULL
AND TEMP.Creation_Date <= TRUNC(AIP_WH.Creation_Date)
THEN TEMP.WH_Tax_Amount ELSE 0 END),0) WH_Tax_Amount,
AIP_WH.Invoice_Payment_ID
FROM AP_Invoice_Payments_All AIP_WH,
FII_AP_WH_Tax_T TEMP
WHERE AIP_WH.Invoice_ID BETWEEN g_start_range and g_end_range
AND AIP_WH.Invoice_ID = TEMP.Invoice_ID (+)
AND AIP_WH.Payment_Num = TEMP.Payment_Num (+)
GROUP BY AIP_WH.Invoice_ID,
AIP_WH.Payment_Num,
AIP_WH.Creation_Date,
AIP_WH.Created_By,
AIP_WH.Amount,
AIP_WH.Discount_Taken,
AIP_WH.Check_ID,
AIP_WH.Invoice_Payment_ID) AIP_PP,
FII_AP_Prepay_T PP
WHERE AIP_PP.Invoice_ID = PP.Invoice_ID (+)
AND AIP_PP.Payment_Num = PP.Payment_Num (+)
GROUP BY AIP_PP.Invoice_ID,
AIP_PP.Payment_Num,
AIP_PP.Creation_Date,
AIP_PP.Created_By,
AIP_PP.Amount,
AIP_PP.Discount_Taken,
AIP_PP.Check_ID,
AIP_PP.WH_Tax_Amount,
AIP_PP.Invoice_Payment_ID) AIP,
FII_AP_PAY_CHK_STG APC,
AP_Checks_All AC,
-- IBY_Payment_Profiles IBYPM
IBY_SYS_PMT_PROFILES_B IBY_SYS_PROF_B,--IBY CHANGE
IBY_ACCT_PMT_PROFILES_B IBY_ACCT_PROF_B--IBY CHANGE
WHERE AI.Invoice_ID = PS.Invoice_ID
AND AI.Cancel_Date IS NULL
AND AIP.Invoice_ID = PS.Invoice_ID
AND AIP.Payment_Num = PS.Payment_Num
AND AIP.Check_ID = AC.Check_ID
AND PS.Invoice_ID = DISC.Invoice_ID
AND PS.Payment_Num = DISC.Payment_Num
AND APC.Invoice_ID = PS.Invoice_ID
AND APC.Payment_Num = PS.Payment_Num
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 APC.Invp_Creation_Date <= TRUNC(AIP.Creation_Date)
AND AC.Void_Date IS NULL
HAVING SUM(APC.Payment_Amount) + AIP.Prepay_WH_Tax_Amount = PS.Gross_Amount
GROUP BY AI.Org_ID, AI.Supplier_ID,
AI.Invoice_ID,
AI.Base_Currency_Code,
AI.Invoice_Date,
AI.Invoice_Type,
PS.Payment_Num,
PS.Due_Date,
AIP.Amount,
AIP.Created_By,
AIP.Check_ID,
AC.Check_Date,
IBY_SYS_PROF_B.Processing_Type,
AC.Payment_Method_Lookup_Code,
-- AC.PAYMENT_PROFILE_ID,
AIP.Creation_Date,
NVL(AIP.Discount_Taken,0),
NVL(DISC.Discount_Available,0),
NVL(DISC.Discount_Taken,0),
NVL(DISC.Discount_Lost,0),
NVL(PS.Discount_Amount_Available,0),
NVL(PS.Second_Disc_Amt_Available,0),
NVL(PS.Third_Disc_Amt_Available,0),
PS.Gross_Amount,
AIP.Prepay_WH_Tax_Amount,
AIP.Invoice_Payment_ID;
FII_UTIL.put_line('Inserted ' ||SQL%ROWCOUNT|| ' Last Payment records into FII_AP_PAY_SCHED_TEMP ');
INSERT INTO FII_AP_PAY_SCHED_B b
(Time_ID,
Period_Type_ID,
Action_Date,
Action,
Update_Sequence,
Org_ID,
Supplier_ID,
Invoice_ID,
Base_Currency_Code,
Trx_Date,
Payment_Num,
Due_Date,
Created_By,
Amount_Remaining,
Past_Due_Amount,
Discount_Available,
Discount_Taken,
Discount_Lost,
Payment_Amount,
On_Time_Payment_Amt,
Late_Payment_Amt,
No_Days_Late,
Due_Bucket1,
Due_Bucket2,
Due_Bucket3,
Past_Due_Bucket1,
Past_Due_Bucket2,
Past_Due_Bucket3,
Amount_Remaining_B,
Past_Due_Amount_B,
Discount_Available_B,
Discount_Taken_B,
Discount_Lost_B,
Payment_Amount_B,
On_Time_Payment_Amt_B,
Late_Payment_Amt_B,
Due_Bucket1_B,
Due_Bucket2_B,
Due_Bucket3_B,
Past_Due_Bucket1_B,
Past_Due_Bucket2_B,
Past_Due_Bucket3_B,
Prim_Amount_Remaining,
Prim_Past_Due_Amount,
Prim_Discount_Available,
Prim_Discount_Taken,
Prim_Discount_Lost,
Prim_Payment_Amount,
Prim_On_time_Payment_Amt,
Prim_Late_Payment_Amt,
Prim_Due_Bucket1,
Prim_Due_Bucket2,
Prim_Due_Bucket3,
Prim_Past_Due_Bucket1,
Prim_Past_Due_Bucket2,
Prim_Past_Due_Bucket3,
Sec_Amount_Remaining,
Sec_Past_Due_Amount,
Sec_Discount_Available,
Sec_Discount_Taken,
Sec_Discount_Lost,
Sec_Payment_Amount,
Sec_On_time_Payment_Amt,
Sec_Late_Payment_Amt,
Sec_Due_Bucket1,
Sec_Due_Bucket2,
Sec_Due_Bucket3,
Sec_Past_Due_Bucket1,
Sec_Past_Due_Bucket2,
Sec_Past_Due_Bucket3,
Fully_Paid_Date,
Check_ID,
Check_Date,
Payment_Method,
Creation_Date,
Last_Updated_By,
Last_Update_Date,
Last_Update_Login,
Inv_Pymt_Flag,
Unique_ID)
SELECT /*+ ordered use_nl(RATES,FRATES) */
TEMP.Time_ID,
TEMP.Period_Type_ID,
TEMP.Action_Date,
TEMP.Action,
TEMP.Update_Sequence,
TEMP.Org_ID,
TEMP.Supplier_ID,
TEMP.Invoice_ID,
TEMP.Base_Currency_Code,
TEMP.Trx_Date,
TEMP.Payment_Num,
TEMP.Due_Date,
TEMP.Created_By,
TEMP.Amount_Remaining,
TEMP.Past_Due_Amount,
TEMP.Discount_Available,
TEMP.Discount_Taken,
TEMP.Discount_Lost,
TEMP.Payment_Amount,
TEMP.On_Time_Payment_Amt,
TEMP.Late_Payment_Amt,
TEMP.No_Days_Late,
TEMP.Due_Bucket1,
TEMP.Due_Bucket2,
TEMP.Due_Bucket3,
TEMP.Past_Due_Bucket1,
TEMP.Past_Due_Bucket2,
TEMP.Past_Due_Bucket3,
ROUND((Amount_Remaining * Conversion_Rate) / Functional_MAU) * Functional_MAU Amount_Remaining_B,
ROUND((Past_Due_Amount * Conversion_Rate) / Functional_MAU) * Functional_MAU Past_Due_Amount_B,
ROUND((Discount_Available * Conversion_Rate) / Functional_MAU) * Functional_MAU Discount_Available_B,
ROUND((Discount_Taken * Conversion_Rate) / Functional_MAU) * Functional_MAU,
ROUND((Discount_Lost * Conversion_Rate) / Functional_MAU) * Functional_MAU,
ROUND((Payment_Amount * Conversion_Rate) / Functional_MAU) * Functional_MAU,
ROUND((On_Time_Payment_Amt * Conversion_Rate) / Functional_MAU) * Functional_MAU,
ROUND((Late_Payment_Amt * Conversion_Rate) / Functional_MAU) * Functional_MAU,
ROUND((Due_Bucket1 * Conversion_Rate) / Functional_MAU) * Functional_MAU Due_Bucket1_B,
ROUND((Due_Bucket2 * Conversion_Rate) / Functional_MAU) * Functional_MAU Due_Bucket2_B,
ROUND((Due_Bucket3 * Conversion_Rate) / Functional_MAU) * Functional_MAU Due_Bucket3_B,
ROUND((Past_Due_Bucket1 * Conversion_Rate) / Functional_MAU) * Functional_MAU Past_Due_Bucket1_B,
ROUND((Past_Due_Bucket2 * Conversion_Rate) / Functional_MAU) * Functional_MAU Past_Due_Bucket2_B,
ROUND((Past_Due_Bucket3 * Conversion_Rate) / Functional_MAU) * Functional_MAU Past_Due_Bucket3_B,
ROUND(DECODE(AI.Invoice_Currency_Code, g_prim_currency, Amount_Remaining,
((Amount_Remaining * Conversion_Rate) * RATES.Prim_Conversion_Rate))
/ g_primary_mau) * g_primary_mau Prim_Amount_Remaining,
ROUND(DECODE(AI.Invoice_Currency_Code, g_prim_currency, Past_Due_Amount,
((Past_Due_Amount * Conversion_Rate) * RATES.Prim_Conversion_Rate))
/ g_primary_mau) * g_primary_mau Prim_Past_Due_Amount,
ROUND(DECODE(AI.Invoice_Currency_Code, g_prim_currency, Discount_Available,
((Discount_Available * Conversion_Rate) * RATES.Prim_Conversion_Rate))
/ g_primary_mau) * g_primary_mau Prim_Discount_Available,
ROUND(DECODE(AI.Invoice_Currency_Code, g_prim_currency, Discount_Taken,
((Discount_Taken * Conversion_Rate) * RATES.Prim_Conversion_Rate))
/ g_primary_mau) * g_primary_mau Prim_Discount_Taken,
ROUND(DECODE(AI.Invoice_Currency_Code, g_prim_currency, Discount_Lost,
((Discount_Lost * Conversion_Rate) * RATES.Prim_Conversion_Rate))
/ g_primary_mau) * g_primary_mau Prim_Discount_Lost,
ROUND(DECODE(AI.Invoice_Currency_Code, g_prim_currency, Payment_Amount,
((Payment_Amount * Conversion_Rate) * RATES.Prim_Conversion_Rate))
/ g_primary_mau) * g_primary_mau Prim_Payment_Amount,
ROUND(DECODE(AI.Invoice_Currency_Code, g_prim_currency, On_Time_Payment_Amt,
((On_Time_Payment_Amt * Conversion_Rate) * RATES.Prim_Conversion_Rate))
/ g_primary_mau) * g_primary_mau Prim_On_Time_Payment_Amt,
ROUND(DECODE(AI.Invoice_Currency_Code, g_prim_currency, Late_Payment_Amt,
((Late_Payment_Amt * Conversion_Rate) * RATES.Prim_Conversion_Rate))
/ g_primary_mau) * g_primary_mau Prim_Late_Payment_Amt,
ROUND(DECODE(AI.Invoice_Currency_Code, g_prim_currency, Due_Bucket1,
((Due_Bucket1 * Conversion_Rate) * RATES.Prim_Conversion_Rate))
/ g_primary_mau) * g_primary_mau Prim_Due_Bucket1,
ROUND(DECODE(AI.Invoice_Currency_Code, g_prim_currency, Due_Bucket2,
((Due_Bucket2 * Conversion_Rate) * RATES.Prim_Conversion_Rate))
/ g_primary_mau) * g_primary_mau Prim_Due_Bucket2,
ROUND(DECODE(AI.Invoice_Currency_Code, g_prim_currency, Due_Bucket3,
((Due_Bucket3 * Conversion_Rate) * RATES.Prim_Conversion_Rate))
/ g_primary_mau) * g_primary_mau Prim_Due_Bucket3,
ROUND(DECODE(AI.Invoice_Currency_Code, g_prim_currency, Past_Due_Bucket1,
((Past_Due_Bucket1 * Conversion_Rate) * RATES.Prim_Conversion_Rate))
/ g_primary_mau) * g_primary_mau Prim_Past_Due_Bucket1,
ROUND(DECODE(AI.Invoice_Currency_Code, g_prim_currency, Past_Due_Bucket2,
((Past_Due_Bucket2 * Conversion_Rate) * RATES.Prim_Conversion_Rate))
/ g_primary_mau) * g_primary_mau Prim_Past_Due_Bucket2,
ROUND(DECODE(AI.Invoice_Currency_Code, g_prim_currency, Past_Due_Bucket3,
((Past_Due_Bucket3 * Conversion_Rate) * RATES.Prim_Conversion_Rate))
/ g_primary_mau) * g_primary_mau Prim_Past_Due_Bucket3,
ROUND(DECODE(AI.Invoice_Currency_Code, g_sec_currency, Amount_Remaining,
((Amount_Remaining * Conversion_Rate) * RATES.Sec_Conversion_Rate))
/ g_secondary_mau) * g_secondary_mau Sec_Amount_Remaining,
ROUND(DECODE(AI.Invoice_Currency_Code, g_sec_currency, Past_Due_Amount,
((Past_Due_Amount * Conversion_Rate) * RATES.Sec_Conversion_Rate))
/ g_secondary_mau) * g_secondary_mau Sec_Past_Due_Amount,
ROUND(DECODE(AI.Invoice_Currency_Code, g_sec_currency, Discount_Available,
((Discount_Available * Conversion_Rate) * RATES.Sec_Conversion_Rate))
/ g_secondary_mau) * g_secondary_mau Sec_Discount_Available,
ROUND(DECODE(AI.Invoice_Currency_Code, g_sec_currency, Discount_Taken,
((Discount_Taken * Conversion_Rate) * RATES.Sec_Conversion_Rate))
/ g_secondary_mau) * g_secondary_mau Sec_Discount_Taken,
ROUND(DECODE(AI.Invoice_Currency_Code, g_sec_currency, Discount_Lost,
((Discount_Lost * Conversion_Rate) * RATES.Sec_Conversion_Rate))
/ g_secondary_mau) * g_secondary_mau Sec_Discount_Lost,
ROUND(DECODE(AI.Invoice_Currency_Code, g_sec_currency, Payment_Amount,
((Payment_Amount * Conversion_Rate) * RATES.Sec_Conversion_Rate))
/ g_secondary_mau) * g_secondary_mau Sec_Payment_Amount,
ROUND(DECODE(AI.Invoice_Currency_Code, g_sec_currency, On_Time_Payment_Amt,
((On_Time_Payment_Amt * Conversion_Rate) * RATES.Sec_Conversion_Rate))
/ g_secondary_mau) * g_secondary_mau Sec_On_Time_Payment_Amt,
ROUND(DECODE(AI.Invoice_Currency_Code, g_sec_currency, Late_Payment_Amt,
((Late_Payment_Amt * Conversion_Rate) * RATES.Sec_Conversion_Rate))
/ g_secondary_mau) * g_secondary_mau Sec_Late_Payment_Amt,
ROUND(DECODE(AI.Invoice_Currency_Code, g_sec_currency, Due_Bucket1,
((Due_Bucket1 * Conversion_Rate) * RATES.Sec_Conversion_Rate))
/ g_secondary_mau) * g_secondary_mau Sec_Due_Bucket1,
ROUND(DECODE(AI.Invoice_Currency_Code, g_sec_currency, Due_Bucket2,
((Due_Bucket2 * Conversion_Rate) * RATES.Sec_Conversion_Rate))
/ g_secondary_mau) * g_secondary_mau Sec_Due_Bucket2,
ROUND(DECODE(AI.Invoice_Currency_Code, g_sec_currency, Due_Bucket3,
((Due_Bucket3 * Conversion_Rate) * RATES.Sec_Conversion_Rate))
/ g_secondary_mau) * g_secondary_mau Sec_Due_Bucket3,
ROUND(DECODE(AI.Invoice_Currency_Code, g_sec_currency, Past_Due_Bucket1,
((Past_Due_Bucket1 * Conversion_Rate) * RATES.Sec_Conversion_Rate))
/ g_secondary_mau) * g_secondary_mau Sec_Past_Due_Bucket1,
ROUND(DECODE(AI.Invoice_Currency_Code, g_sec_currency, Past_Due_Bucket2,
((Past_Due_Bucket2 * Conversion_Rate) * RATES.Sec_Conversion_Rate))
/ g_secondary_mau) * g_secondary_mau Sec_Past_Due_Bucket2,
ROUND(DECODE(AI.Invoice_Currency_Code, g_sec_currency, Past_Due_Bucket3,
((Past_Due_Bucket3 * Conversion_Rate) * RATES.Sec_Conversion_Rate))
/ g_secondary_mau) * g_secondary_mau Sec_Past_Due_Bucket3,
TEMP.Fully_Paid_Date,
TEMP.Check_ID,
TEMP.Check_Date,
TEMP.Payment_Method,
sysdate Creation_Date,
g_fii_user_id Last_Updated_By,
sysdate Last_Update_Date,
g_fii_login_id Last_Update_Login,
TEMP.Inv_Pymt_Flag,
TEMP.Unique_ID
FROM FII_AP_Invoice_B AI,
FII_AP_Pay_Sched_Temp TEMP,
FII_AP_PS_Rates_Temp RATES,
FII_AP_Func_Rates_Temp FRATES
WHERE TEMP.Invoice_ID = AI.Invoice_ID
AND AI.Invoice_ID BETWEEN g_start_range and g_end_range
AND FRATES.To_Currency = TEMP.Base_Currency_Code
AND FRATES.From_Currency = AI.Payment_Currency_Code
AND FRATES.Trx_Date = AI.Exchange_Date
AND DECODE(AI.Exchange_Rate_Type,'User', AI.Exchange_Rate,1) =
DECODE(FRATES.Conversion_Type,'User', FRATES.Conversion_Rate,1)
AND FRATES.Conversion_Type = AI.Exchange_Rate_Type
AND RATES.Functional_Currency = TEMP.Base_Currency_Code
AND RATES.Trx_Date = TEMP.Trx_Date;
FII_UTIL.put_line('Inserted ' ||SQL%ROWCOUNT|| ' Payment records into FII_AP_PAY_SCHED_B from Temp table');
/* Insert statement to insert prepayment information into the summary tables.
Discount information is not recorded as the discounts taken and lost are
available in the payments to the prepayment invoice.
For prepayments we will always assume that the payment is made on time and
will populate the On_Time_Payment_Amt column with the payment amount */
INSERT INTO FII_AP_PAY_SCHED_B b
(Time_ID,
Period_Type_ID,
Action_Date,
Action,
Update_Sequence,
Org_ID,
Supplier_ID,
Invoice_ID,
Base_Currency_Code,
Trx_Date,
Payment_Num,
Due_Date,
Created_By,
Amount_Remaining,
Past_Due_Amount,
Discount_Available,
Discount_Taken,
Discount_Lost,
Payment_Amount,
On_Time_Payment_Amt,
Late_Payment_Amt,
No_Days_Late,
Due_Bucket1,
Due_Bucket2,
Due_Bucket3,
Past_Due_Bucket1,
Past_Due_Bucket2,
Past_Due_Bucket3,
Amount_Remaining_B,
Past_Due_Amount_B,
Discount_Available_B,
Discount_Taken_B,
Discount_Lost_B,
Payment_Amount_B,
On_Time_Payment_Amt_B,
Late_Payment_Amt_B,
Due_Bucket1_B,
Due_Bucket2_B,
Due_Bucket3_B,
Past_Due_Bucket1_B,
Past_Due_Bucket2_B,
Past_Due_Bucket3_B,
Prim_Amount_Remaining,
Prim_Past_Due_Amount,
Prim_Discount_Available,
Prim_Discount_Taken,
Prim_Discount_Lost,
Prim_Payment_Amount,
Prim_On_time_Payment_Amt,
Prim_Late_Payment_Amt,
Prim_Due_Bucket1,
Prim_Due_Bucket2,
Prim_Due_Bucket3,
Prim_Past_Due_Bucket1,
Prim_Past_Due_Bucket2,
Prim_Past_Due_Bucket3,
Sec_Amount_Remaining,
Sec_Past_Due_Amount,
Sec_Discount_Available,
Sec_Discount_Taken,
Sec_Discount_Lost,
Sec_Payment_Amount,
Sec_On_time_Payment_Amt,
Sec_Late_Payment_Amt,
Sec_Due_Bucket1,
Sec_Due_Bucket2,
Sec_Due_Bucket3,
Sec_Past_Due_Bucket1,
Sec_Past_Due_Bucket2,
Sec_Past_Due_Bucket3,
Creation_Date,
Last_Updated_By,
Last_Update_Date,
Last_Update_Login)
SELECT /*+ ordered use_nl(ps) use_nl(rates,frates) */
TO_NUMBER(TO_CHAR(AID.Creation_Date,'J')) Time_ID,
1 Period_Type_ID,
TRUNC(AID.Creation_Date) Action_Date,
DECODE(AID.Line_Type_Lookup_Code, 'AWT', 'WITHHOLDING', 'TAX') Action,
g_seq_id Update_Sequence,
AI.Org_ID Org_ID,
AI.Supplier_ID Supplier_ID,
AI.Invoice_ID Invoice_ID,
AI.Base_Currency_Code Base_Currency_Code,
AI.Invoice_Date Invoice_Date,
PS.Payment_Num Payment_Num,
TRUNC(PS.Due_Date) Due_Date,
PS.Created_By Created_By,
ROUND((SUM(DECODE(AI.Invoice_Amount, 0, 0, PS.Gross_Amount * AID.Amount / AI.Invoice_Amount)))
/ NVL(FC.Minimum_Accountable_Unit, 0.01))
* NVL(FC.Minimum_Accountable_Unit, 0.01) Amount_Remaining,
DECODE(SIGN(TRUNC(PS.Due_Date) - TRUNC(AID.Creation_Date)), -1,
ROUND(SUM(DECODE(AI.Invoice_Amount, 0, 0, PS.Gross_Amount * AID.Amount / AI.Invoice_Amount))
/NVL(FC.Minimum_Accountable_Unit, 0.01))
* NVL(FC.Minimum_Accountable_Unit, 0.01), 0) Past_Due_Amount,
0 Discount_Available,
0 Discount_Taken,
0 Discount_Lost,
0 Payment_Amount,
0 On_Time_Payment_Amount,
0 Late_Payment_Amt,
DECODE(SIGN(TRUNC(PS.Due_Date) - TRUNC(AID.Creation_Date)), -1,
(TRUNC(AID.Creation_Date) - TRUNC(PS.Due_Date)), 0) No_Days_Late,
CASE
WHEN (TRUNC(PS.Due_Date) - TRUNC(AID.Creation_Date)) >= g_due_bucket1
THEN ROUND((SUM(DECODE(AI.Invoice_Amount, 0, 0,PS.Gross_Amount * AID.Amount / AI.Invoice_Amount)))
/ NVL(FC.Minimum_Accountable_Unit, 0.01))
* NVL(FC.Minimum_Accountable_Unit, 0.01)
ELSE 0
END Due_Bucket1,
CASE
WHEN (TRUNC(PS.Due_Date) - TRUNC(AID.Creation_Date)) <= g_due_bucket2
AND (TRUNC(PS.Due_Date) - TRUNC(AID.Creation_Date)) > g_due_bucket3
THEN ROUND((SUM(DECODE(AI.Invoice_Amount, 0, 0,PS.Gross_Amount * AID.Amount / AI.Invoice_Amount)))
/ NVL(FC.Minimum_Accountable_Unit, 0.01))
* NVL(FC.Minimum_Accountable_Unit, 0.01)
ELSE 0
END Due_Bucket2,
CASE
WHEN (TRUNC(PS.Due_Date) - TRUNC(AID.Creation_Date)) <= g_due_bucket3
AND (TRUNC(PS.Due_Date) - TRUNC(AID.Creation_Date)) >= 0
THEN ROUND((SUM(DECODE(AI.Invoice_Amount, 0, 0,PS.Gross_Amount * AID.Amount / AI.Invoice_Amount)))
/ NVL(FC.Minimum_Accountable_Unit, 0.01))
* NVL(FC.Minimum_Accountable_Unit, 0.01)
ELSE 0
END Due_Bucket3,
CASE
WHEN (TRUNC(AID.Creation_Date) - TRUNC(PS.Due_Date)) >= g_past_bucket1
THEN ROUND((SUM(DECODE(AI.Invoice_Amount, 0, 0,PS.Gross_Amount * AID.Amount / AI.Invoice_Amount)))
/ NVL(FC.Minimum_Accountable_Unit, 0.01))
* NVL(FC.Minimum_Accountable_Unit, 0.01)
ELSE 0
END Past_Due_Bucket1,
CASE
WHEN (TRUNC(AID.Creation_Date) - TRUNC(PS.Due_Date)) <= g_past_bucket2
AND (TRUNC(AID.Creation_Date) - TRUNC(PS.Due_Date)) > g_past_bucket3
THEN ROUND((SUM(DECODE(AI.Invoice_Amount, 0, 0,PS.Gross_Amount * AID.Amount / AI.Invoice_Amount)))
/ NVL(FC.Minimum_Accountable_Unit, 0.01))
* NVL(FC.Minimum_Accountable_Unit, 0.01)
ELSE 0
END Past_Due_Bucket2,
CASE
WHEN (TRUNC(AID.Creation_Date) - TRUNC(PS.Due_Date)) <= g_past_bucket3
AND (TRUNC(AID.Creation_Date) - TRUNC(PS.Due_Date)) > 0
THEN ROUND((SUM(DECODE(AI.Invoice_Amount, 0, 0,PS.Gross_Amount * AID.Amount / AI.Invoice_Amount)))
/ NVL(FC.Minimum_Accountable_Unit, 0.01))
* NVL(FC.Minimum_Accountable_Unit, 0.01)
ELSE 0
END Past_Due_Bucket3,
ROUND(((SUM(DECODE(AI.Invoice_Amount, 0, 0, PS.Gross_Amount * AID.Amount / AI.Invoice_Amount))) * Conversion_Rate)
/ NVL(FC.Minimum_Accountable_Unit, 0.01))
* NVL(FC.Minimum_Accountable_Unit, 0.01) Amount_Remaining_B,
DECODE(SIGN(TRUNC(PS.Due_Date) - TRUNC(AID.Creation_Date)), -1,
ROUND(((SUM(DECODE(AI.Invoice_Amount, 0, 0, PS.Gross_Amount * AID.Amount / AI.Invoice_Amount))) * Conversion_Rate)
/ NVL(FC.Minimum_Accountable_Unit, 0.01))
* NVL(FC.Minimum_Accountable_Unit, 0.01), 0) Past_Due_Amount_B,
0 Discount_Available_B,
0 Discount_Taken_B,
0 Discount_Lost_B,
0 Payment_Amount_B,
0 On_Time_Payment_Amt_B,
0 Late_Payment_Amt_B,
CASE
WHEN (TRUNC(PS.Due_Date) - TRUNC(AID.Creation_Date)) >= g_due_bucket1
THEN ROUND(((SUM(DECODE(AI.Invoice_Amount, 0, 0, PS.Gross_Amount * AID.Amount / AI.Invoice_Amount))) * Conversion_Rate)
/ NVL(FC.Minimum_Accountable_Unit, 0.01))
* NVL(FC.Minimum_Accountable_Unit, 0.01)
ELSE 0
END Due_Bucket1_B,
CASE
WHEN (TRUNC(PS.Due_Date) - TRUNC(AID.Creation_Date)) <= g_due_bucket2
AND (TRUNC(PS.Due_Date) - TRUNC(AID.Creation_Date)) > g_due_bucket3
THEN ROUND(((SUM(DECODE(AI.Invoice_Amount, 0, 0, PS.Gross_Amount * AID.Amount / AI.Invoice_Amount))) * Conversion_Rate)
/ NVL(FC.Minimum_Accountable_Unit, 0.01))
* NVL(FC.Minimum_Accountable_Unit, 0.01)
ELSE 0
END Due_Bucket2_B,
CASE
WHEN (TRUNC(PS.Due_Date) - TRUNC(AID.Creation_Date)) <= g_due_bucket3
AND (TRUNC(PS.Due_Date) - TRUNC(AID.Creation_Date)) >= 0
THEN ROUND(((SUM(DECODE(AI.Invoice_Amount, 0, 0, PS.Gross_Amount * AID.Amount / AI.Invoice_Amount))) * Conversion_Rate)
/ NVL(FC.Minimum_Accountable_Unit, 0.01))
* NVL(FC.Minimum_Accountable_Unit, 0.01)
ELSE 0
END Due_Bucket3_B,
CASE
WHEN (TRUNC(AID.Creation_Date) - TRUNC(PS.Due_Date)) >= g_past_bucket1
THEN ROUND(((SUM(DECODE(AI.Invoice_Amount, 0, 0, PS.Gross_Amount * AID.Amount / AI.Invoice_Amount))) * Conversion_Rate)
/ NVL(FC.Minimum_Accountable_Unit, 0.01))
* NVL(FC.Minimum_Accountable_Unit, 0.01)
ELSE 0
END Past_Due_Bucket1_B,
CASE
WHEN (TRUNC(AID.Creation_Date) - TRUNC(PS.Due_Date)) <= g_past_bucket2
AND (TRUNC(AID.Creation_Date) - TRUNC(PS.Due_Date)) > g_past_bucket3
THEN ROUND(((SUM(DECODE(AI.Invoice_Amount, 0, 0, PS.Gross_Amount * AID.Amount / AI.Invoice_Amount))) * Conversion_Rate)
/ NVL(FC.Minimum_Accountable_Unit, 0.01))
* NVL(FC.Minimum_Accountable_Unit, 0.01)
ELSE 0
END Past_Due_Bucket2_B,
CASE
WHEN (TRUNC(AID.Creation_Date) - TRUNC(PS.Due_Date)) <= g_past_bucket3
AND (TRUNC(AID.Creation_Date) - TRUNC(PS.Due_Date)) > 0
THEN ROUND(((SUM(DECODE(AI.Invoice_Amount, 0, 0, PS.Gross_Amount * AID.Amount / AI.Invoice_Amount))) * Conversion_Rate)
/ NVL(FC.Minimum_Accountable_Unit, 0.01))
* NVL(FC.Minimum_Accountable_Unit, 0.01)
ELSE 0
END Past_Due_Bucket3_B,
ROUND(DECODE(AI.Invoice_Currency_Code, g_prim_currency,
SUM(DECODE(AI.Invoice_Amount, 0, 0, PS.Gross_Amount * AID.Amount / AI.Invoice_Amount)),
(((SUM(DECODE(AI.Invoice_Amount, 0, 0, PS.Gross_Amount * AID.Amount / AI.Invoice_Amount))) * Conversion_Rate)
* RATES.Prim_Conversion_Rate)) / g_primary_mau) * g_primary_mau Prim_Amount_Remaining,
DECODE(SIGN(TRUNC(PS.Due_Date) - TRUNC(AID.Creation_Date)), -1,
ROUND(DECODE(AI.Invoice_Currency_Code, g_prim_currency,
SUM(DECODE(AI.Invoice_Amount, 0, 0, PS.Gross_Amount * AID.Amount / AI.Invoice_Amount)),
((SUM(DECODE(AI.Invoice_Amount, 0, 0, PS.Gross_Amount * AID.Amount / AI.Invoice_Amount)) * Conversion_Rate)
* RATES.Prim_Conversion_Rate)) / g_primary_mau) * g_primary_mau, 0) Prim_Past_Due_Amount,
0 Prim_Discount_Available,
0 Prim_Discount_Taken,
0 Prim_Discount_Lost,
0 Prim_Payment_Amount,
0 Prim_On_Time_Payment_Amt,
0 Prim_Late_Payment_Amt,
CASE
WHEN (TRUNC(PS.Due_Date) - TRUNC(AID.Creation_Date)) >= g_due_bucket1
THEN ROUND(DECODE(AI.Invoice_Currency_Code, g_prim_currency,
SUM(DECODE(AI.Invoice_Amount, 0, 0, PS.Gross_Amount * AID.Amount / AI.Invoice_Amount)),
(((SUM(DECODE(AI.Invoice_Amount, 0, 0, PS.Gross_Amount * AID.Amount / AI.Invoice_Amount))) * Conversion_Rate)
* RATES.Prim_Conversion_Rate)) / g_primary_mau) * g_primary_mau
ELSE 0
END Prim_Due_Bucket1,
CASE
WHEN (TRUNC(PS.Due_Date) - TRUNC(AID.Creation_Date)) <= g_due_bucket2
AND (TRUNC(PS.Due_Date) - TRUNC(AID.Creation_Date)) > g_due_bucket3
THEN ROUND(DECODE(AI.Invoice_Currency_Code, g_prim_currency,
SUM(DECODE(AI.Invoice_Amount, 0, 0, PS.Gross_Amount * AID.Amount / AI.Invoice_Amount)),
(((SUM(DECODE(AI.Invoice_Amount, 0, 0, PS.Gross_Amount * AID.Amount / AI.Invoice_Amount))) * Conversion_Rate)
* RATES.Prim_Conversion_Rate)) / g_primary_mau) * g_primary_mau
ELSE 0
END Prim_Due_Bucket2,
CASE
WHEN (TRUNC(PS.Due_Date) - TRUNC(AID.Creation_Date)) <= g_due_bucket3
AND (TRUNC(PS.Due_Date) - TRUNC(AID.Creation_Date)) >= 0
THEN ROUND(DECODE(AI.Invoice_Currency_Code, g_prim_currency,
SUM(DECODE(AI.Invoice_Amount, 0, 0, PS.Gross_Amount * AID.Amount / AI.Invoice_Amount)),
(((SUM(DECODE(AI.Invoice_Amount, 0, 0, PS.Gross_Amount * AID.Amount / AI.Invoice_Amount))) * Conversion_Rate)
* RATES.Prim_Conversion_Rate)) / g_primary_mau) * g_primary_mau
ELSE 0
END Prim_Due_Bucket3,
CASE
WHEN (TRUNC(AID.Creation_Date) - TRUNC(PS.Due_Date)) >= g_past_bucket1
THEN ROUND(DECODE(AI.Invoice_Currency_Code, g_prim_currency,
SUM(DECODE(AI.Invoice_Amount, 0, 0, PS.Gross_Amount * AID.Amount / AI.Invoice_Amount)),
(((SUM(DECODE(AI.Invoice_Amount, 0, 0, PS.Gross_Amount * AID.Amount / AI.Invoice_Amount))) * Conversion_Rate)
* RATES.Prim_Conversion_Rate)) / g_primary_mau) * g_primary_mau
ELSE 0
END Prim_Past_Due_Bucket1,
CASE
WHEN (TRUNC(AID.Creation_Date) - TRUNC(PS.Due_Date)) <= g_past_bucket2
AND (TRUNC(AID.Creation_Date) - TRUNC(PS.Due_Date)) > g_past_bucket3
THEN ROUND(DECODE(AI.Invoice_Currency_Code, g_prim_currency,
SUM(DECODE(AI.Invoice_Amount, 0, 0, PS.Gross_Amount * AID.Amount / AI.Invoice_Amount)),
(((SUM(DECODE(AI.Invoice_Amount, 0, 0, PS.Gross_Amount * AID.Amount / AI.Invoice_Amount))) * Conversion_Rate)
* RATES.Prim_Conversion_Rate)) / g_primary_mau) * g_primary_mau
ELSE 0
END Prim_Past_Due_Bucket2,
CASE
WHEN (TRUNC(AID.Creation_Date) - TRUNC(PS.Due_Date)) <= g_past_bucket3
AND (TRUNC(AID.Creation_Date) - TRUNC(PS.Due_Date)) > 0
THEN ROUND(DECODE(AI.Invoice_Currency_Code, g_prim_currency,
SUM(DECODE(AI.Invoice_Amount, 0, 0, PS.Gross_Amount * AID.Amount / AI.Invoice_Amount)),
(((SUM(DECODE(AI.Invoice_Amount, 0, 0, PS.Gross_Amount * AID.Amount / AI.Invoice_Amount))) * Conversion_Rate)
* RATES.Prim_Conversion_Rate)) / g_primary_mau) * g_primary_mau
ELSE 0
END Prim_Past_Due_Bucket3,
ROUND(DECODE(AI.Invoice_Currency_Code, g_sec_currency,
SUM(DECODE(AI.Invoice_Amount, 0, 0, PS.Gross_Amount * AID.Amount / AI.Invoice_Amount)),
(((SUM(DECODE(AI.Invoice_Amount, 0, 0, PS.Gross_Amount * AID.Amount / AI.Invoice_Amount))) * Conversion_Rate)
* RATES.Sec_Conversion_Rate)) / g_secondary_mau) * g_secondary_mau Sec_Amount_Remaining,
DECODE(SIGN(TRUNC(PS.Due_Date) - TRUNC(AID.Creation_Date)), -1,
ROUND(DECODE(AI.Invoice_Currency_Code, g_sec_currency,
SUM(DECODE(AI.Invoice_Amount, 0, 0, PS.Gross_Amount * AID.Amount / AI.Invoice_Amount)),
((SUM(DECODE(AI.Invoice_Amount, 0, 0, PS.Gross_Amount * AID.Amount / AI.Invoice_Amount)) * Conversion_Rate)
* RATES.Sec_Conversion_Rate)) / g_secondary_mau) * g_secondary_mau, 0) Sec_Past_Due_Amount,
0 Sec_Discount_Available,
0 Sec_Discount_Taken,
0 Sec_Discount_Lost,
0 Sec_Payment_Amount,
0 Sec_On_Time_Payment_Amt,
0 Sec_Late_Payment_Amt,
CASE
WHEN (TRUNC(PS.Due_Date) - TRUNC(AID.Creation_Date)) >= g_due_bucket1
THEN ROUND(DECODE(AI.Invoice_Currency_Code, g_sec_currency,
SUM(DECODE(AI.Invoice_Amount, 0, 0, PS.Gross_Amount * AID.Amount / AI.Invoice_Amount)),
(((SUM(DECODE(AI.Invoice_Amount, 0, 0, PS.Gross_Amount * AID.Amount / AI.Invoice_Amount))) * Conversion_Rate)
* RATES.Sec_Conversion_Rate)) / g_secondary_mau) * g_secondary_mau
ELSE 0
END Sec_Due_Bucket1,
CASE
WHEN (TRUNC(PS.Due_Date) - TRUNC(AID.Creation_Date)) <= g_due_bucket2
AND (TRUNC(PS.Due_Date) - TRUNC(AID.Creation_Date)) > g_due_bucket3
THEN ROUND(DECODE(AI.Invoice_Currency_Code, g_sec_currency,
SUM(DECODE(AI.Invoice_Amount, 0, 0, PS.Gross_Amount * AID.Amount / AI.Invoice_Amount)),
(((SUM(DECODE(AI.Invoice_Amount, 0, 0, PS.Gross_Amount * AID.Amount / AI.Invoice_Amount))) * Conversion_Rate)
* RATES.Sec_Conversion_Rate)) / g_secondary_mau) * g_secondary_mau
ELSE 0
END Sec_Due_Bucket2,
CASE
WHEN (TRUNC(PS.Due_Date) - TRUNC(AID.Creation_Date)) <= g_due_bucket3
AND (TRUNC(PS.Due_Date) - TRUNC(AID.Creation_Date)) >= 0
THEN ROUND(DECODE(AI.Invoice_Currency_Code, g_sec_currency,
SUM(DECODE(AI.Invoice_Amount, 0, 0, PS.Gross_Amount * AID.Amount / AI.Invoice_Amount)),
(((SUM(DECODE(AI.Invoice_Amount, 0, 0, PS.Gross_Amount * AID.Amount / AI.Invoice_Amount))) * Conversion_Rate)
* RATES.Sec_Conversion_Rate)) / g_secondary_mau) * g_secondary_mau
ELSE 0
END Sec_Due_Bucket3,
CASE
WHEN (TRUNC(AID.Creation_Date) - TRUNC(PS.Due_Date)) >= g_past_bucket1
THEN ROUND(DECODE(AI.Invoice_Currency_Code, g_sec_currency,
SUM(DECODE(AI.Invoice_Amount, 0, 0, PS.Gross_Amount * AID.Amount / AI.Invoice_Amount)),
(((SUM(DECODE(AI.Invoice_Amount, 0, 0, PS.Gross_Amount * AID.Amount / AI.Invoice_Amount))) * Conversion_Rate)
* RATES.Sec_Conversion_Rate)) / g_secondary_mau) * g_secondary_mau
ELSE 0
END Sec_Past_Due_Bucket1,
CASE
WHEN (TRUNC(AID.Creation_Date) - TRUNC(PS.Due_Date)) <= g_past_bucket2
AND (TRUNC(AID.Creation_Date) - TRUNC(PS.Due_Date)) > g_past_bucket3
THEN ROUND(DECODE(AI.Invoice_Currency_Code, g_sec_currency,
SUM(DECODE(AI.Invoice_Amount, 0, 0, PS.Gross_Amount * AID.Amount / AI.Invoice_Amount)),
(((SUM(DECODE(AI.Invoice_Amount, 0, 0, PS.Gross_Amount * AID.Amount / AI.Invoice_Amount))) * Conversion_Rate)
* RATES.Sec_Conversion_Rate)) / g_secondary_mau) * g_secondary_mau
ELSE 0
END Sec_Past_Due_Bucket2,
CASE
WHEN (TRUNC(AID.Creation_Date) - TRUNC(PS.Due_Date)) <= g_past_bucket3
AND (TRUNC(AID.Creation_Date) - TRUNC(PS.Due_Date)) > 0
THEN ROUND(DECODE(AI.Invoice_Currency_Code, g_sec_currency,
SUM(DECODE(AI.Invoice_Amount, 0, 0, PS.Gross_Amount * AID.Amount / AI.Invoice_Amount)),
(((SUM(DECODE(AI.Invoice_Amount, 0, 0, PS.Gross_Amount * AID.Amount / AI.Invoice_Amount))) * Conversion_Rate)
* RATES.Sec_Conversion_Rate)) / g_secondary_mau) * g_secondary_mau
ELSE 0
END Sec_Past_Due_Bucket3,
sysdate Creation_Date,
g_fii_user_id Last_Updated_By,
sysdate Last_Update_Date,
g_fii_login_id Last_Update_Login
FROM FII_AP_Invoice_B AI,
AP_Invoice_Distributions_All AID,
AP_Invoice_Lines_All AIL,
AP_Payment_Schedules_All PS,
FII_AP_PS_Rates_Temp RATES,
FII_AP_Func_Rates_Temp FRATES,
FND_Currencies FC
WHERE PS.Invoice_ID = AI.Invoice_ID
AND AID.Invoice_ID = AI.Invoice_ID
AND AID.Invoice_ID = AIL.Invoice_ID
AND AID.Invoice_Line_Number = AIL.Line_Number
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'
AND AI.Invoice_Type NOT IN ('PREPAYMENT')
AND AI.Cancel_Date IS NULL
AND FRATES.To_Currency = AI.Base_Currency_Code
AND FRATES.From_Currency = AI.Payment_Currency_Code
AND FRATES.Trx_Date = AI.Exchange_Date
AND DECODE(AI.Exchange_Rate_Type,'User', AI.Exchange_Rate,1) =
DECODE(FRATES.Conversion_Type,'User', FRATES.Conversion_Rate,1)
AND FRATES.Conversion_Type = AI.Exchange_Rate_Type
AND RATES.Functional_Currency = AI.Base_Currency_Code
AND RATES.Trx_Date = AI.Invoice_Date
AND AI.Invoice_ID BETWEEN g_start_range and g_end_range
AND AI.Payment_Currency_Code = FC.Currency_Code
GROUP BY TO_NUMBER(TO_CHAR(AID.Creation_Date,'J')),
TRUNC(AID.Creation_Date),
AID.Line_Type_Lookup_Code,
AI.Invoice_Currency_Code,
AI.Base_Currency_Code,
AI.Invoice_Date,
AI.Org_ID,
AI.Supplier_ID,
AI.Invoice_ID,
PS.Payment_Num,
TRUNC(PS.Due_Date),
PS.Created_By,
AI.Invoice_Currency_Code,
AI.Payment_Currency_Code,
AI.Payment_Cross_Rate,
NVL(FC.Minimum_Accountable_Unit, 0.01),
AI.Invoice_Type,
RATES.Prim_Conversion_Rate,
RATES.Sec_Conversion_Rate,
Conversion_Rate;
FII_UTIL.put_line('Inserted ' ||SQL%ROWCOUNT|| ' Withholding records into FII_AP_PAY_SCHED_B');
INSERT INTO FII_AP_PAY_SCHED_B b
(Time_ID,
Period_Type_ID,
Action_Date,
Action,
Update_Sequence,
Org_ID,
Supplier_ID,
Invoice_ID,
Base_Currency_Code,
Trx_Date,
Payment_Num,
Due_Date,
Created_By,
Amount_Remaining,
Past_Due_Amount,
Discount_Available,
Discount_Taken,
Discount_Lost,
Payment_Amount,
On_Time_Payment_Amt,
Late_Payment_Amt,
No_Days_Late,
Due_Bucket1,
Due_Bucket2,
Due_Bucket3,
Past_Due_Bucket1,
Past_Due_Bucket2,
Past_Due_Bucket3,
Amount_Remaining_B,
Past_Due_Amount_B,
Discount_Available_B,
Discount_Taken_B,
Discount_Lost_B,
Payment_Amount_B,
On_Time_Payment_Amt_B,
Late_Payment_Amt_B,
Due_Bucket1_B,
Due_Bucket2_B,
Due_Bucket3_B,
Past_Due_Bucket1_B,
Past_Due_Bucket2_B,
Past_Due_Bucket3_B,
Prim_Amount_Remaining,
Prim_Past_Due_Amount,
Prim_Discount_Available,
Prim_Discount_Taken,
Prim_Discount_Lost,
Prim_Payment_Amount,
Prim_On_time_Payment_Amt,
Prim_Late_Payment_Amt,
Prim_Due_Bucket1,
Prim_Due_Bucket2,
Prim_Due_Bucket3,
Prim_Past_Due_Bucket1,
Prim_Past_Due_Bucket2,
Prim_Past_Due_Bucket3,
Sec_Amount_Remaining,
Sec_Past_Due_Amount,
Sec_Discount_Available,
Sec_Discount_Taken,
Sec_Discount_Lost,
Sec_Payment_Amount,
Sec_On_time_Payment_Amt,
Sec_Late_Payment_Amt,
Sec_Due_Bucket1,
Sec_Due_Bucket2,
Sec_Due_Bucket3,
Sec_Past_Due_Bucket1,
Sec_Past_Due_Bucket2,
Sec_Past_Due_Bucket3,
Fully_Paid_Date,
Check_ID,
Check_Date,
Payment_Method,
Creation_Date,
Last_Updated_By,
Last_Update_Date,
Last_Update_Login,
Inv_Pymt_Flag,
Unique_ID)
SELECT /*+ use_nl(PS) use_nl(TEMP) */
TO_NUMBER(TO_CHAR(TEMP.Creation_Date,'J')) Time_ID,
1 Period_Type_ID,
TEMP.Creation_Date Action_Date,
'PAYMENT' Action,
g_seq_id Update_Sequence,
AI.Org_ID Org_ID,
AI.Supplier_ID Supplier_ID,
AI.Invoice_ID Invoice_ID,
AI.Base_Currency_Code Base_Currency_Code,
AI.Invoice_Date Invoice_Date,
PS.Payment_Num Payment_Num,
TRUNC(PS.Due_Date) Due_Date,
PS.Created_By Created_By,
-1 * ROUND(TEMP.Prepay_Amount
/ NVL(FC.Minimum_Accountable_Unit, 0.01)) * NVL(FC.Minimum_Accountable_Unit, 0.01) Amount_Remaining,
-1 * DECODE(SIGN(TRUNC(PS.Due_Date) - TRUNC(TEMP.Creation_Date)), -1,
ROUND(TEMP.Prepay_Amount
/NVL(FC.Minimum_Accountable_Unit, 0.01)) * NVL(FC.Minimum_Accountable_Unit, 0.01), 0) Past_Due_Amount,
0 Discount_Available,
0 Discount_Taken,
0 Discount_Lost,
ROUND(TEMP.Prepay_Amount
/ NVL(FC.Minimum_Accountable_Unit, 0.01)) * NVL(FC.Minimum_Accountable_Unit, 0.01) Payment_Amount,
DECODE(SIGN(TRUNC(PS.Due_Date) - TRUNC(TEMP.Creation_Date)), -1, 0,
ROUND(TEMP.Prepay_Amount
/ NVL(FC.Minimum_Accountable_Unit, 0.01)) * NVL(FC.Minimum_Accountable_Unit, 0.01)) On_Time_Payment_Amt,
DECODE(SIGN(TRUNC(PS.Due_Date) - TRUNC(TEMP.Creation_Date)), -1,
ROUND(TEMP.Prepay_Amount
/ NVL(FC.Minimum_Accountable_Unit, 0.01)) * NVL(FC.Minimum_Accountable_Unit, 0.01), 0) Late_Payment_Amt,
DECODE(SIGN(TRUNC(PS.Due_Date) - TRUNC(TEMP.Creation_Date)), -1,
(TRUNC(TEMP.Creation_Date) - TRUNC(PS.Due_Date)), 0) No_Days_Late,
CASE
WHEN (TRUNC(PS.Due_Date) - TRUNC(TEMP.Creation_Date)) >= g_due_bucket1
THEN -1 * ROUND(TEMP.Prepay_Amount
/ NVL(FC.Minimum_Accountable_Unit, 0.01)) * NVL(FC.Minimum_Accountable_Unit, 0.01)
ELSE 0
END Due_Bucket1,
CASE
WHEN (TRUNC(PS.Due_Date) - TRUNC(TEMP.Creation_Date)) <= g_due_bucket2
AND (TRUNC(PS.Due_Date) - TRUNC(TEMP.Creation_Date)) > g_due_bucket3
THEN -1 * ROUND(TEMP.Prepay_Amount
/ NVL(FC.Minimum_Accountable_Unit, 0.01)) * NVL(FC.Minimum_Accountable_Unit, 0.01)
ELSE 0
END Due_Bucket2,
CASE
WHEN (TRUNC(PS.Due_Date) - TRUNC(TEMP.Creation_Date)) <= g_due_bucket3
AND (TRUNC(PS.Due_Date) - TRUNC(TEMP.Creation_Date)) >= 0
THEN -1 * ROUND(TEMP.Prepay_Amount
/ NVL(FC.Minimum_Accountable_Unit, 0.01)) * NVL(FC.Minimum_Accountable_Unit, 0.01)
ELSE 0
END Due_Bucket3,
CASE
WHEN (TRUNC(TEMP.Creation_Date) - TRUNC(PS.Due_Date)) >= g_past_bucket1
THEN -1 * ROUND(TEMP.Prepay_Amount
/ NVL(FC.Minimum_Accountable_Unit, 0.01)) * NVL(FC.Minimum_Accountable_Unit, 0.01)
ELSE 0
END Past_Due_Bucket1,
CASE
WHEN (TRUNC(TEMP.Creation_Date) - TRUNC(PS.Due_Date)) <= g_past_bucket2
AND (TRUNC(TEMP.Creation_Date) - TRUNC(PS.Due_Date)) > g_past_bucket3
THEN -1 * ROUND(TEMP.Prepay_Amount
/ NVL(FC.Minimum_Accountable_Unit, 0.01)) * NVL(FC.Minimum_Accountable_Unit, 0.01)
ELSE 0
END Past_Due_Bucket2,
CASE
WHEN (TRUNC(TEMP.Creation_Date) - TRUNC(PS.Due_Date)) <= g_past_bucket3
AND (TRUNC(TEMP.Creation_Date) - TRUNC(PS.Due_Date)) > 0
THEN -1 * ROUND(TEMP.Prepay_Amount
/ NVL(FC.Minimum_Accountable_Unit, 0.01)) * NVL(FC.Minimum_Accountable_Unit, 0.01)
ELSE 0
END Past_Due_Bucket3,
-1 * ROUND((TEMP.Prepay_Amount * Conversion_Rate)
/ NVL(FC.Minimum_Accountable_Unit, 0.01)) * NVL(FC.Minimum_Accountable_Unit, 0.01) Amount_Remaining_B,
-1 * DECODE(SIGN(TRUNC(PS.Due_Date) - TRUNC(TEMP.Creation_Date)), -1,
ROUND((TEMP.Prepay_Amount * Conversion_Rate)
/ NVL(FC.Minimum_Accountable_Unit, 0.01)) * NVL(FC.Minimum_Accountable_Unit, 0.01), 0) Past_Due_Amount_B,
0 Discount_Available_B,
0 Discount_Taken_B,
0 Discount_Lost_B,
ROUND((TEMP.Prepay_Amount * Conversion_Rate)
/ NVL(FC.Minimum_Accountable_Unit, 0.01)) * NVL(FC.Minimum_Accountable_Unit, 0.01) Payment_Amount_B,
DECODE(SIGN(TRUNC(PS.Due_Date) - TRUNC(TEMP.Creation_Date)), -1, 0,
ROUND((TEMP.Prepay_Amount * Conversion_Rate)
/ NVL(FC.Minimum_Accountable_Unit, 0.01)) * NVL(FC.Minimum_Accountable_Unit, 0.01)) On_Time_Payment_Amt_B,
DECODE(SIGN(TRUNC(PS.Due_Date) - TRUNC(TEMP.Creation_Date)), -1,
ROUND((TEMP.Prepay_Amount * Conversion_Rate)
/ NVL(FC.Minimum_Accountable_Unit, 0.01)) * NVL(FC.Minimum_Accountable_Unit, 0.01), 0) Late_Payment_Amt_B,
CASE
WHEN (TRUNC(PS.Due_Date) - TRUNC(TEMP.Creation_Date)) >= g_due_bucket1
THEN -1 * ROUND((TEMP.Prepay_Amount * Conversion_Rate)
/ NVL(FC.Minimum_Accountable_Unit, 0.01)) * NVL(FC.Minimum_Accountable_Unit, 0.01)
ELSE 0
END Due_Bucket1_B,
CASE
WHEN (TRUNC(PS.Due_Date) - TRUNC(TEMP.Creation_Date)) <= g_due_bucket2
AND (TRUNC(PS.Due_Date) - TRUNC(TEMP.Creation_Date)) > g_due_bucket3
THEN -1 * ROUND((TEMP.Prepay_Amount * Conversion_Rate)
/ NVL(FC.Minimum_Accountable_Unit, 0.01)) * NVL(FC.Minimum_Accountable_Unit, 0.01)
ELSE 0
END Due_Bucket2_B,
CASE
WHEN (TRUNC(PS.Due_Date) - TRUNC(TEMP.Creation_Date)) <= g_due_bucket3
AND (TRUNC(PS.Due_Date) - TRUNC(TEMP.Creation_Date)) >= 0
THEN -1 * ROUND((TEMP.Prepay_Amount * Conversion_Rate)
/ NVL(FC.Minimum_Accountable_Unit, 0.01)) * NVL(FC.Minimum_Accountable_Unit, 0.01)
ELSE 0
END Due_Bucket3_B,
CASE
WHEN (TRUNC(TEMP.Creation_Date) - TRUNC(PS.Due_Date)) >= g_past_bucket1
THEN -1 * ROUND((TEMP.Prepay_Amount * Conversion_Rate)
/ NVL(FC.Minimum_Accountable_Unit, 0.01)) * NVL(FC.Minimum_Accountable_Unit, 0.01)
ELSE 0
END Past_Due_Bucket1_B,
CASE
WHEN (TRUNC(TEMP.Creation_Date) - TRUNC(PS.Due_Date)) <= g_past_bucket2
AND (TRUNC(TEMP.Creation_Date) - TRUNC(PS.Due_Date)) > g_past_bucket3
THEN -1 * ROUND((TEMP.Prepay_Amount * Conversion_Rate)
/ NVL(FC.Minimum_Accountable_Unit, 0.01)) * NVL(FC.Minimum_Accountable_Unit, 0.01)
ELSE 0
END Past_Due_Bucket2_B,
CASE
WHEN (TRUNC(TEMP.Creation_Date) - TRUNC(PS.Due_Date)) <= g_past_bucket3
AND (TRUNC(TEMP.Creation_Date) - TRUNC(PS.Due_Date)) > 0
THEN -1 * ROUND((TEMP.Prepay_Amount * Conversion_Rate)
/ NVL(FC.Minimum_Accountable_Unit, 0.01)) * NVL(FC.Minimum_Accountable_Unit, 0.01)
ELSE 0
END Past_Due_Bucket3_B,
-1 * ROUND(DECODE(AI.Invoice_Currency_Code, g_prim_currency, TEMP.Prepay_Amount,
((TEMP.Prepay_Amount * Conversion_Rate) * RATES.Prim_Conversion_Rate))
/ g_primary_mau) * g_primary_mau Prim_Amount_Remaining,
-1 * DECODE(SIGN(TRUNC(PS.Due_Date) - TRUNC(TEMP.Creation_Date)), -1,
ROUND(DECODE(AI.Invoice_Currency_Code, g_prim_currency, TEMP.Prepay_Amount,
((TEMP.Prepay_Amount * Conversion_Rate) * RATES.Prim_Conversion_Rate))
/ g_primary_mau) * g_primary_mau, 0) Prim_Past_Due_Amount,
0 Prim_Discount_Available,
0 Prim_Discount_Taken,
0 Prim_Discount_Lost,
ROUND(DECODE(AI.Invoice_Currency_Code, g_prim_currency, TEMP.Prepay_Amount,
((TEMP.Prepay_Amount * Conversion_Rate) * RATES.Prim_Conversion_Rate))
/ g_primary_mau) * g_primary_mau Prim_Payment_Amount,
DECODE(SIGN(TRUNC(PS.Due_Date) - TRUNC(TEMP.Creation_Date)), -1, 0,
(ROUND(DECODE(AI.Invoice_Currency_Code, g_prim_currency, TEMP.Prepay_Amount,
((TEMP.Prepay_Amount * Conversion_Rate) * RATES.Prim_Conversion_Rate))
/ g_primary_mau) * g_primary_mau)) Prim_On_Time_Payment_Amt,
DECODE(SIGN(TRUNC(PS.Due_Date) - TRUNC(TEMP.Creation_Date)), -1,
ROUND(DECODE(AI.Invoice_Currency_Code, g_prim_currency, TEMP.Prepay_Amount,
((TEMP.Prepay_Amount * Conversion_Rate) * RATES.Prim_Conversion_Rate))
/ g_primary_mau) * g_primary_mau, 0) Prim_Late_Payment_Amt,
CASE
WHEN (TRUNC(PS.Due_Date) - TRUNC(TEMP.Creation_Date)) >= g_due_bucket1
THEN -1 * ROUND(DECODE(AI.Invoice_Currency_Code, g_prim_currency, TEMP.Prepay_Amount,
((TEMP.Prepay_Amount * Conversion_Rate) * RATES.Prim_Conversion_Rate))
/ g_primary_mau) * g_primary_mau
ELSE 0
END Prim_Due_Bucket1,
CASE
WHEN (TRUNC(PS.Due_Date) - TRUNC(TEMP.Creation_Date)) <= g_due_bucket2
AND (TRUNC(PS.Due_Date) - TRUNC(TEMP.Creation_Date)) > g_due_bucket3
THEN -1 * ROUND(DECODE(AI.Invoice_Currency_Code, g_prim_currency, TEMP.Prepay_Amount,
((TEMP.Prepay_Amount * Conversion_Rate) * RATES.Prim_Conversion_Rate))
/ g_primary_mau) * g_primary_mau
ELSE 0
END Prim_Due_Bucket2,
CASE
WHEN (TRUNC(PS.Due_Date) - TRUNC(TEMP.Creation_Date)) <= g_due_bucket3
AND (TRUNC(PS.Due_Date) - TRUNC(TEMP.Creation_Date)) >= 0
THEN -1 * ROUND(DECODE(AI.Invoice_Currency_Code, g_prim_currency, TEMP.Prepay_Amount,
((TEMP.Prepay_Amount * Conversion_Rate) * RATES.Prim_Conversion_Rate))
/ g_primary_mau) * g_primary_mau
ELSE 0
END Prim_Due_Bucket3,
CASE
WHEN (TRUNC(TEMP.Creation_Date) - TRUNC(PS.Due_Date)) >= g_past_bucket1
THEN -1 * ROUND(DECODE(AI.Invoice_Currency_Code, g_prim_currency, TEMP.Prepay_Amount,
((TEMP.Prepay_Amount * Conversion_Rate) * RATES.Prim_Conversion_Rate))
/ g_primary_mau) * g_primary_mau
ELSE 0
END Prim_Past_Due_Bucket1,
CASE
WHEN (TRUNC(TEMP.Creation_Date) - TRUNC(PS.Due_Date)) <= g_past_bucket2
AND (TRUNC(TEMP.Creation_Date) - TRUNC(PS.Due_Date)) > g_past_bucket3
THEN -1 * ROUND(DECODE(AI.Invoice_Currency_Code, g_prim_currency, TEMP.Prepay_Amount,
((TEMP.Prepay_Amount * Conversion_Rate) * RATES.Prim_Conversion_Rate))
/ g_primary_mau) * g_primary_mau
ELSE 0
END Prim_Past_Due_Bucket2,
CASE
WHEN (TRUNC(TEMP.Creation_Date) - TRUNC(PS.Due_Date)) <= g_past_bucket3
AND (TRUNC(TEMP.Creation_Date) - TRUNC(PS.Due_Date)) > 0
THEN -1 * ROUND(DECODE(AI.Invoice_Currency_Code, g_prim_currency, TEMP.Prepay_Amount,
((TEMP.Prepay_Amount * Conversion_Rate) * RATES.Prim_Conversion_Rate))
/ g_primary_mau) * g_primary_mau
ELSE 0
END Prim_Past_Due_Bucket3,
-1 * ROUND(DECODE(AI.Invoice_Currency_Code, g_sec_currency, TEMP.Prepay_Amount,
((TEMP.Prepay_Amount * Conversion_Rate) * RATES.Sec_Conversion_Rate))
/ g_secondary_mau) * g_secondary_mau Sec_Amount_Remaining,
-1 * DECODE(SIGN(TRUNC(PS.Due_Date) - TRUNC(TEMP.Creation_Date)), -1,
ROUND(DECODE(AI.Invoice_Currency_Code, g_sec_currency, TEMP.Prepay_Amount,
((TEMP.Prepay_Amount * Conversion_Rate) * RATES.Sec_Conversion_Rate))
/ g_secondary_mau) * g_secondary_mau, 0) Sec_Past_Due_Amount,
0 Sec_Discount_Available,
0 Sec_Discount_Taken,
0 Sec_Discount_Lost,
ROUND(DECODE(AI.Invoice_Currency_Code, g_sec_currency, TEMP.Prepay_Amount,
((TEMP.Prepay_Amount * Conversion_Rate) * RATES.Sec_Conversion_Rate))
/ g_secondary_mau) * g_secondary_mau Sec_Payment_Amount,
DECODE(SIGN(TRUNC(PS.Due_Date) - TRUNC(TEMP.Creation_Date)), -1, 0,
(ROUND(DECODE(AI.Invoice_Currency_Code, g_sec_currency, TEMP.Prepay_Amount,
((TEMP.Prepay_Amount * Conversion_Rate) * RATES.Sec_Conversion_Rate))
/ g_secondary_mau)) * g_secondary_mau) Sec_On_Time_Payment_Amt,
DECODE(SIGN(TRUNC(PS.Due_Date) - TRUNC(TEMP.Creation_Date)), -1,
ROUND(DECODE(AI.Invoice_Currency_Code, g_sec_currency, TEMP.Prepay_Amount,
((TEMP.Prepay_Amount * Conversion_Rate) * RATES.Sec_Conversion_Rate))
/ g_secondary_mau) * g_secondary_mau, 0) Sec_Late_Payment_Amt,
CASE
WHEN (TRUNC(PS.Due_Date) - TRUNC(TEMP.Creation_Date)) >= g_due_bucket1
THEN -1 * ROUND(DECODE(AI.Invoice_Currency_Code, g_sec_currency, TEMP.Prepay_Amount,
((TEMP.Prepay_Amount * Conversion_Rate) * RATES.Sec_Conversion_Rate))
/ g_secondary_mau) * g_secondary_mau
ELSE 0
END Sec_Due_Bucket1,
CASE
WHEN (TRUNC(PS.Due_Date) - TRUNC(TEMP.Creation_Date)) <= g_due_bucket2
AND (TRUNC(PS.Due_Date) - TRUNC(TEMP.Creation_Date)) > g_due_bucket3
THEN -1 * ROUND(DECODE(AI.Invoice_Currency_Code, g_sec_currency, TEMP.Prepay_Amount,
((TEMP.Prepay_Amount * Conversion_Rate) * RATES.Sec_Conversion_Rate))
/ g_secondary_mau) * g_secondary_mau
ELSE 0
END Sec_Due_Bucket2,
CASE
WHEN (TRUNC(PS.Due_Date) - TRUNC(TEMP.Creation_Date)) <= g_due_bucket3
AND (TRUNC(PS.Due_Date) - TRUNC(TEMP.Creation_Date)) >= 0
THEN -1 * ROUND(DECODE(AI.Invoice_Currency_Code, g_sec_currency, TEMP.Prepay_Amount,
((TEMP.Prepay_Amount * Conversion_Rate) * RATES.Sec_Conversion_Rate))
/ g_secondary_mau) * g_secondary_mau
ELSE 0
END Sec_Due_Bucket3,
CASE
WHEN (TRUNC(TEMP.Creation_Date) - TRUNC(PS.Due_Date)) >= g_past_bucket1
THEN -1 * ROUND(DECODE(AI.Invoice_Currency_Code, g_sec_currency, TEMP.Prepay_Amount,
((TEMP.Prepay_Amount * Conversion_Rate) * RATES.Sec_Conversion_Rate))
/ g_secondary_mau) * g_secondary_mau
ELSE 0
END Sec_Past_Due_Bucket1,
CASE
WHEN (TRUNC(TEMP.Creation_Date) - TRUNC(PS.Due_Date)) <= g_past_bucket2
AND (TRUNC(TEMP.Creation_Date) - TRUNC(PS.Due_Date)) > g_past_bucket3
THEN -1 * ROUND(DECODE(AI.Invoice_Currency_Code, g_sec_currency, TEMP.Prepay_Amount,
((TEMP.Prepay_Amount * Conversion_Rate) * RATES.Sec_Conversion_Rate))
/ g_secondary_mau) * g_secondary_mau
ELSE 0
END Sec_Past_Due_Bucket2,
CASE
WHEN (TRUNC(TEMP.Creation_Date) - TRUNC(PS.Due_Date)) <= g_past_bucket3
AND (TRUNC(TEMP.Creation_Date) - TRUNC(PS.Due_Date)) > 0
THEN -1 * ROUND(DECODE(AI.Invoice_Currency_Code, g_sec_currency, TEMP.Prepay_Amount,
((TEMP.Prepay_Amount * Conversion_Rate) * RATES.Sec_Conversion_Rate))
/ g_secondary_mau) * g_secondary_mau
ELSE 0
END Sec_Past_Due_Bucket3,
CASE
WHEN TEMP.PP_WH_Tax_Pay_Amount = PS.Gross_Amount
THEN TEMP.Creation_Date ELSE NULL
END Fully_Paid_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')) PAYMENT_METHOD,
sysdate Creation_Date,
g_fii_user_id Last_Updated_By,
sysdate Last_Update_Date,
g_fii_login_id Last_Update_Login,
'N' Inv_Pymt_Flag,
AC.Check_ID Unique_ID
FROM FII_AP_INVOICE_B AI,
AP_Payment_Schedules_All PS,
(SELECT /*+ use_nl(aps3,pp) */APS3.Invoice_ID,
APS3.Payment_Num,
APS3.Creation_Date,
APS3.Prepay_Amount,
APS3.Check_ID,
APS3.WH_Tax_Pay_Amount + NVL(SUM(CASE WHEN PP.Creation_Date IS NOT NULL
AND PP.Creation_Date <= APS3.Creation_Date
THEN PP.Prepay_Amount ELSE 0 END),0) PP_WH_Tax_Pay_Amount
FROM
(SELECT /*+ use_nl(aps2,awts) */APS2.Invoice_ID,
APS2.Payment_Num,
APS2.Creation_Date,
APS2.Prepay_Amount,
APS2.Check_ID,
APS2.Pay_Amount + NVL(SUM(CASE WHEN AWTS.Creation_Date IS NOT NULL
AND AWTS.Creation_Date <= APS2.Creation_Date
THEN AWTS.WH_Tax_Amount ELSE 0 END),0) WH_Tax_Pay_Amount
FROM
(SELECT /*+ use_nl(aps1, apc) */APS1.Invoice_ID,
APS1.Payment_Num,
APS1.Creation_Date,
APS1.Prepay_Amount,
APS1.Check_ID,
NVL(SUM(CASE WHEN APC.Invp_Creation_Date IS NOT NULL
AND APC.Invp_Creation_Date <= APS1.Creation_Date
THEN APC.Payment_Amount ELSE 0 END),0) Pay_Amount
FROM FII_AP_Prepay_T APS1,
FII_AP_Pay_Chk_Stg APC
WHERE APS1.Invoice_ID BETWEEN g_start_range and g_end_range
AND APS1.Invoice_ID = APC.Invoice_ID (+)
AND APS1.Payment_Num = APC.Payment_Num (+)
Group By APS1.Invoice_ID,
APS1.Payment_Num,
APS1.Creation_Date,
APS1.Prepay_Amount,
APS1.Check_ID) APS2,
FII_AP_WH_Tax_T AWTS
WHERE APS2.Invoice_ID = AWTS.Invoice_ID (+)
AND APS2.Payment_Num = AWTS.Payment_Num (+)
Group By APS2.Invoice_ID,
APS2.Payment_Num,
APS2.Creation_Date,
APS2.Prepay_Amount,
APS2.Check_ID,
APS2.Pay_Amount) APS3,
FII_AP_Prepay_T PP
WHERE APS3.Invoice_ID = PP.Invoice_ID (+)
AND APS3.Payment_Num = PP.Payment_Num (+)
Group By APS3.Invoice_ID,
APS3.Payment_Num,
APS3.Creation_Date,
APS3.Prepay_Amount,
APS3.Check_ID,
APS3.WH_Tax_Pay_Amount) TEMP,
FII_AP_PS_Rates_Temp RATES,
FII_AP_Func_Rates_Temp FRATES,
AP_Checks_All AC,
-- IBY_Payment_Profiles IBYPM--IBY CHANGE
IBY_SYS_PMT_PROFILES_B IBY_SYS_PROF_B,--IBY CHANGE
IBY_ACCT_PMT_PROFILES_B IBY_ACCT_PROF_B,--IBY CHANGE
FND_Currencies FC
WHERE AI.Invoice_ID BETWEEN g_start_range and g_end_range
AND AI.Invoice_ID = TEMP.Invoice_ID
AND TEMP.Invoice_ID = PS.Invoice_ID
AND TEMP.Payment_Num = PS.Payment_Num
AND TEMP.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 AI.Invoice_Type NOT IN ('PREPAYMENT')
AND AI.Cancel_Date IS NULL
AND FRATES.To_Currency = AI.Base_Currency_Code
AND FRATES.From_Currency = AI.Payment_Currency_Code
AND FRATES.Trx_Date = AI.Exchange_Date
AND DECODE(AI.Exchange_Rate_Type,'User', AI.Exchange_Rate,1) =
DECODE(FRATES.Conversion_Type,'User', FRATES.Conversion_Rate,1)
AND FRATES.Conversion_Type = AI.Exchange_Rate_Type
AND RATES.Functional_Currency = AI.Base_Currency_Code
AND RATES.Trx_Date = AI.Invoice_Date
AND AI.Payment_Currency_Code = FC.Currency_Code;
FII_UTIL.put_line('Inserted ' ||SQL%ROWCOUNT|| ' Prepayment records into FII_AP_PAY_SCHED_B ');
g_state := 'Inserting the Payment Schedules Discount Action';
/* For Discount and Due actions we will select the payment schedules whose
discount or due date falls between the given from date and the least of
to date or yesterday's date. This way we will not create wrong discount
lost and past due amounts for due action */
/* Inserting the Discount Date passed records into the summary table.
We will insert only those payment schedules which have not been paid
fully before the first discount date. This check is done by comparing
the gross amount with the payment, prepayment and withheld amount */
INSERT INTO FII_AP_PAY_SCHED_B b
(Time_ID,
Period_Type_ID,
Action_Date,
Action,
Update_Sequence,
Org_ID,
Supplier_ID,
Invoice_ID,
Base_Currency_Code,
Trx_Date,
Payment_Num,
Due_Date,
Created_By,
Amount_Remaining,
Past_Due_Amount,
Discount_Available,
Discount_Taken,
Discount_Lost,
Payment_Amount,
On_Time_Payment_Amt,
Late_Payment_Amt,
No_Days_Late,
Due_Bucket1,
Due_Bucket2,
Due_Bucket3,
Past_Due_Bucket1,
Past_Due_Bucket2,
Past_Due_Bucket3,
Amount_Remaining_B,
Past_Due_Amount_B,
Discount_Available_B,
Discount_Taken_B,
Discount_Lost_B,
Payment_Amount_B,
On_Time_Payment_Amt_B,
Late_Payment_Amt_B,
Due_Bucket1_B,
Due_Bucket2_B,
Due_Bucket3_B,
Past_Due_Bucket1_B,
Past_Due_Bucket2_B,
Past_Due_Bucket3_B,
Prim_Amount_Remaining,
Prim_Past_Due_Amount,
Prim_Discount_Available,
Prim_Discount_Taken,
Prim_Discount_Lost,
Prim_Payment_Amount,
Prim_On_time_Payment_Amt,
Prim_Late_Payment_Amt,
Prim_Due_Bucket1,
Prim_Due_Bucket2,
Prim_Due_Bucket3,
Prim_Past_Due_Bucket1,
Prim_Past_Due_Bucket2,
Prim_Past_Due_Bucket3,
Sec_Amount_Remaining,
Sec_Past_Due_Amount,
Sec_Discount_Available,
Sec_Discount_Taken,
Sec_Discount_Lost,
Sec_Payment_Amount,
Sec_On_time_Payment_Amt,
Sec_Late_Payment_Amt,
Sec_Due_Bucket1,
Sec_Due_Bucket2,
Sec_Due_Bucket3,
Sec_Past_Due_Bucket1,
Sec_Past_Due_Bucket2,
Sec_Past_Due_Bucket3,
Creation_Date,
Last_Updated_By,
Last_Update_Date,
Last_Update_Login,
Unique_ID)
SELECT /*+ leading(ai) index(AI,FII_AP_INVOICE_B_U1) index(FRATES) use_nl(ps) */
TO_NUMBER(TO_CHAR(PS.Discount_Date + 1,'J')) Time_ID,
1 Period_Type_ID,
TRUNC(PS.Discount_Date) + 1 Action_Date,
'DISCOUNT' Action,
g_seq_id Update_Sequence,
AI.Org_ID Org_ID,
AI.Supplier_ID Supplier_ID,
AI.Invoice_ID Invoice_ID,
AI.Base_Currency_Code Base_Currency_Code,
AI.Invoice_Date Trx_Date,
PS.Payment_Num Payment_Num,
TRUNC(PS.Due_Date) Due_Date,
PS.Created_By Created_By,
0 Amount_Remaining,
0 Past_Due_Amount,
-1 * (NVL(PS.Discount_Amount_Available,0) - NVL(APC.Discount_Taken ,0)
- NVL(PS.Second_Disc_Amt_Available,0)) Discount_Available,
0 Discount_Taken,
NVL(PS.Discount_Amount_Available,0) - NVL(APC.Discount_Taken ,0)
- NVL(PS.Second_Disc_Amt_Available,0) Discount_Lost,
0 Payment_Amount,
0 On_Time_Payment_Amt,
0 Late_Payment_Amt,
0 No_Days_Late,
0 Due_Bucket1,
0 Due_Bucket2,
0 Due_Bucket3,
0 Past_Due_Bucket1,
0 Past_Due_Bucket2,
0 Past_Due_Bucket3,
0 Amount_Remaining_B,
0 Past_Due_Amount_B,
ROUND((-1 * (NVL(PS.Discount_Amount_Available,0) - NVL(APC.Discount_Taken ,0)
- NVL(PS.Second_Disc_Amt_Available,0)) * Conversion_Rate)
/ Functional_MAU) * Functional_MAU Discount_Available_B,
0 Discount_Taken_B,
ROUND(((NVL(PS.Discount_Amount_Available,0) - NVL(APC.Discount_Taken ,0)
- NVL(PS.Second_Disc_Amt_Available,0)) * Conversion_Rate)
/ Functional_MAU) * Functional_MAU Discount_Lost_B,
0 Payment_Amount_B,
0 On_Time_Payment_Amt_B,
0 Late_Payment_Amt_B,
0 Due_Bucket1_B,
0 Due_Bucket2_B,
0 Due_Bucket3_B,
0 Past_Due_Bucket1_B,
0 Past_Due_Bucket2_B,
0 Past_Due_Bucket3_B,
0 Prim_Amount_Remaining,
0 Prim_Past_Due_Amount,
ROUND(DECODE(AI.Invoice_Currency_Code, g_prim_currency,
-1 * (NVL(PS.Discount_Amount_Available,0) - NVL(APC.Discount_Taken ,0)
- NVL(PS.Second_Disc_Amt_Available,0)),
((-1 * (NVL(PS.Discount_Amount_Available,0) - NVL(APC.Discount_Taken ,0)
- NVL(PS.Second_Disc_Amt_Available,0)) * Conversion_Rate)
* RATES.Prim_Conversion_Rate)) / g_primary_mau) * g_primary_mau Prim_Discount_Available,
0 Prim_Discount_Taken,
ROUND(DECODE(AI.Invoice_Currency_Code, g_prim_currency,
NVL(PS.Discount_Amount_Available,0) - NVL(APC.Discount_Taken ,0)
- NVL(PS.Second_Disc_Amt_Available,0),
(((NVL(PS.Discount_Amount_Available,0) - NVL(APC.Discount_Taken ,0)
- NVL(PS.Second_Disc_Amt_Available,0)) * Conversion_Rate)
* RATES.Prim_Conversion_Rate)) / g_primary_mau) * g_primary_mau Prim_Discount_Lost,
0 Prim_Payment_Amount,
0 Prim_On_Time_Payment_Amt,
0 Prim_Late_Payment_Amt,
0 Prim_Due_Bucket1,
0 Prim_Due_Bucket2,
0 Prim_Due_Bucket3,
0 Prim_Past_Due_Bucket1,
0 Prim_Past_Due_Bucket2,
0 Prim_Past_Due_Bucket3,
0 Sec_Amount_Remaining,
0 Sec_Past_Due_Amount,
ROUND(DECODE(AI.Invoice_Currency_Code, g_sec_currency,
-1 * (NVL(PS.Discount_Amount_Available,0) - NVL(APC.Discount_Taken ,0)
- NVL(PS.Second_Disc_Amt_Available,0)),
((-1 * (NVL(PS.Discount_Amount_Available,0) - NVL(APC.Discount_Taken ,0)
- NVL(PS.Second_Disc_Amt_Available,0)) * Conversion_Rate)
* RATES.Sec_Conversion_Rate)) / g_secondary_mau) * g_secondary_mau Sec_Discount_Available,
0 Sec_Discount_Taken,
ROUND(DECODE(AI.Invoice_Currency_Code, g_sec_currency,
NVL(PS.Discount_Amount_Available,0) - NVL(APC.Discount_Taken ,0)
- NVL(PS.Second_Disc_Amt_Available,0),
(((NVL(PS.Discount_Amount_Available,0) - NVL(APC.Discount_Taken ,0)
- NVL(PS.Second_Disc_Amt_Available,0)) * Conversion_Rate)
* RATES.Sec_Conversion_Rate)) / g_secondary_mau) * g_secondary_mau Sec_Discount_Lost,
0 Sec_Payment_Amount,
0 Sec_On_Time_Payment_Amt,
0 Sec_Late_Payment_Amt,
0 Sec_Due_Bucket1,
0 Sec_Due_Bucket2,
0 Sec_Due_Bucket3,
0 Sec_Past_Due_Bucket1,
0 Sec_Past_Due_Bucket2,
0 Sec_Past_Due_Bucket3,
sysdate Creation_Date,
g_fii_user_id Last_Updated_By,
sysdate Last_Update_Date,
g_fii_login_id Last_Update_Login,
1 Unique_ID
FROM FII_AP_Invoice_B AI,
FII_AP_PS_Rates_Temp RATES,
FII_AP_Func_Rates_Temp FRATES,
AP_Payment_Schedules_All PS,
(SELECT /*+ index(TEM,FII_AP_WH_TAX_T_N1) */ TEM.Invoice_ID,
TEM.Payment_Num,
SUM(NVL(TEM.WH_Tax_Amount, 0)) WH_Tax_Amount
FROM FII_AP_WH_Tax_T TEM
WHERE TEM.Invoice_id BETWEEN g_start_range and g_end_range
AND TEM.Invoice_Type <> 'PREPAYMENT'
AND TRUNC(TEM.Discount_Date) + 1 BETWEEN g_start_date AND LEAST(g_end_date,g_sysdate)
AND TEM.Creation_Date < TRUNC(TEM.Discount_Date) + 1
GROUP BY TEM.Invoice_ID,
TEM.Payment_Num,
TEM.Discount_Date) TEMP,
(SELECT /*+ index(prep,FII_AP_PREPAY_T_N1) */ PREP.Invoice_ID,
PREP.Payment_Num,
SUM(NVL(PREP.Prepay_Amount, 0)) Prepay_Amount
FROM FII_AP_Prepay_T PREP
WHERE PREP.Invoice_id BETWEEN g_start_range and g_end_range
AND TRUNC(PREP.Discount_Date) + 1 BETWEEN g_start_date AND LEAST(g_end_date,g_sysdate)
AND PREP.Creation_Date < TRUNC(PREP.Discount_Date) + 1
GROUP BY PREP.Invoice_ID,
PREP.Payment_Num,
PREP.Discount_Date) PP,
(SELECT /*+ index(PC,FII_AP_PAY_CHK_STG_N1) */ PC.Invoice_ID,
PC.Payment_Num,
SUM(NVL(PC.Discount_Taken, 0)) Discount_Taken,
SUM(NVL(PC.Payment_Amount, 0)) Payment_Amount
FROM FII_AP_PAY_CHK_STG PC
WHERE PC.Invoice_id BETWEEN g_start_range and g_end_range
AND PC.Invoice_Type <> 'PREPAYMENT'
AND TRUNC(PC.Discount_Date) + 1 BETWEEN g_start_date AND LEAST(g_end_date,g_sysdate)
AND PC.Invp_Creation_Date < TRUNC(PC.Discount_Date) + 1
GROUP BY PC.Invoice_ID,
PC.Payment_Num,
PC.Discount_Date) APC
WHERE PS.Invoice_ID = AI.Invoice_ID
AND AI.Invoice_ID BETWEEN g_start_range and g_end_range
AND AI.Invoice_Type NOT IN ('PREPAYMENT')
AND AI.Cancel_Date IS NULL
AND FRATES.To_Currency = AI.Base_Currency_Code
AND FRATES.From_Currency = AI.Payment_Currency_Code
AND FRATES.Trx_Date = AI.Exchange_Date
AND DECODE(AI.Exchange_Rate_Type,'User', AI.Exchange_Rate,1) =
DECODE(FRATES.Conversion_Type,'User',FRATES.Conversion_Rate,1)
AND FRATES.Conversion_Type = AI.Exchange_Rate_Type
AND RATES.Functional_Currency = AI.Base_Currency_Code
AND RATES.Trx_Date = AI.Invoice_Date
AND TRUNC(PS.Discount_Date) + 1 BETWEEN g_start_date AND LEAST(g_end_date,g_sysdate)
AND PS.Invoice_ID = TEMP.Invoice_ID(+)
AND PS.Payment_Num = TEMP.Payment_Num(+)
AND PS.Invoice_ID = PP.Invoice_ID(+)
AND PS.Payment_Num = PP.Payment_Num(+)
AND PS.Invoice_ID = APC.Invoice_ID(+)
AND PS.Payment_Num = APC.Payment_Num(+)
AND ABS(NVL(APC.Payment_Amount,0) + NVL(TEMP.WH_Tax_Amount,0)
+ NVL(PP.Prepay_Amount,0)) < ABS(PS.Gross_Amount);
FII_UTIL.put_line('Inserted ' ||SQL%ROWCOUNT|| ' Discount records into FII_AP_PAY_SCHED_B');
g_state := 'Inserting the Payment Schedules Second Discount Records';
/* Inserting the Discount Date passed records into the summary table.
We will insert only those payment schedules which have not be paid
fully before the second discount date. This check is done by comparing
the gross amount with the payment, prepayment and withheld amount */
INSERT INTO FII_AP_PAY_SCHED_B b
(Time_ID,
Period_Type_ID,
Action_Date,
Action,
Update_Sequence,
Org_ID,
Supplier_ID,
Invoice_ID,
Base_Currency_Code,
Trx_Date,
Payment_Num,
Due_Date,
Created_By,
Amount_Remaining,
Past_Due_Amount,
Discount_Available,
Discount_Taken,
Discount_Lost,
Payment_Amount,
On_Time_Payment_Amt,
Late_Payment_Amt,
No_Days_Late,
Due_Bucket1,
Due_Bucket2,
Due_Bucket3,
Past_Due_Bucket1,
Past_Due_Bucket2,
Past_Due_Bucket3,
Amount_Remaining_B,
Past_Due_Amount_B,
Discount_Available_B,
Discount_Taken_B,
Discount_Lost_B,
Payment_Amount_B,
On_Time_Payment_Amt_B,
Late_Payment_Amt_B,
Due_Bucket1_B,
Due_Bucket2_B,
Due_Bucket3_B,
Past_Due_Bucket1_B,
Past_Due_Bucket2_B,
Past_Due_Bucket3_B,
Prim_Amount_Remaining,
Prim_Past_Due_Amount,
Prim_Discount_Available,
Prim_Discount_Taken,
Prim_Discount_Lost,
Prim_Payment_Amount,
Prim_On_time_Payment_Amt,
Prim_Late_Payment_Amt,
Prim_Due_Bucket1,
Prim_Due_Bucket2,
Prim_Due_Bucket3,
Prim_Past_Due_Bucket1,
Prim_Past_Due_Bucket2,
Prim_Past_Due_Bucket3,
Sec_Amount_Remaining,
Sec_Past_Due_Amount,
Sec_Discount_Available,
Sec_Discount_Taken,
Sec_Discount_Lost,
Sec_Payment_Amount,
Sec_On_time_Payment_Amt,
Sec_Late_Payment_Amt,
Sec_Due_Bucket1,
Sec_Due_Bucket2,
Sec_Due_Bucket3,
Sec_Past_Due_Bucket1,
Sec_Past_Due_Bucket2,
Sec_Past_Due_Bucket3,
Creation_Date,
Last_Updated_By,
Last_Update_Date,
Last_Update_Login,
Unique_ID)
SELECT /*+ leading(ai) index(AI,FII_AP_INVOICE_B_U1) index(FRATES) use_nl(ps) */
TO_NUMBER(TO_CHAR(PS.Second_Discount_Date + 1,'J')) Time_ID,
1 Period_Type_ID,
TRUNC(PS.Second_Discount_Date) + 1 Action_Date,
'DISCOUNT' Action,
g_seq_id Update_Sequence,
AI.Org_ID Org_ID,
AI.Supplier_ID Supplier_ID,
AI.Invoice_ID Invoice_ID,
AI.Base_Currency_Code Base_Currency_Code,
AI.Invoice_Date Trx_Date,
PS.Payment_Num Payment_Num,
TRUNC(PS.Due_Date) Due_Date,
PS.Created_By Created_By,
0 Amount_Remaining,
0 Past_Due_Amount,
-1 * (NVL(PS.Second_Disc_Amt_Available,0) - NVL(DISC.Discount_Taken,0)
- NVL(PS.Third_Disc_Amt_Available,0)) Discount_Available,
0 Discount_Taken,
NVL(PS.Second_Disc_Amt_Available,0) - NVL(DISC.Discount_Taken,0)
- NVL(PS.Third_Disc_Amt_Available,0) Discount_Lost,
0 Payment_Amount,
0 On_Time_Payment_Amt,
0 Late_Payment_Amt,
0 No_Days_Late,
0 Due_Bucket1,
0 Due_Bucket2,
0 Due_Bucket3,
0 Past_Due_Bucket1,
0 Past_Due_Bucket2,
0 Past_Due_Bucket3,
0 Amount_Remaining_B,
0 Past_Due_Amount_B,
ROUND((-1 * (NVL(PS.Second_Disc_Amt_Available,0) - NVL(DISC.Discount_Taken,0)
- NVL(PS.Third_Disc_Amt_Available,0)) * Conversion_Rate)
/ Functional_MAU) * Functional_MAU Discount_Available_B,
0 Discount_Taken_B,
ROUND(((NVL(PS.Second_Disc_Amt_Available,0) - NVL(DISC.Discount_Taken,0)
- NVL(PS.Third_Disc_Amt_Available,0)) * Conversion_Rate)
/ Functional_MAU) * Functional_MAU Discount_Lost_B,
0 Payment_Amount_B,
0 On_Time_Payment_Amt_B,
0 Late_Payment_Amt_B,
0 Due_Bucket1_B,
0 Due_Bucket2_B,
0 Due_Bucket3_B,
0 Past_Due_Bucket1_B,
0 Past_Due_Bucket2_B,
0 Past_Due_Bucket3_B,
0 Prim_Amount_Remaining,
0 Prim_Past_Due_Amount,
ROUND(DECODE(AI.Invoice_Currency_Code, g_prim_currency,
-1 * (NVL(PS.Second_Disc_Amt_Available,0) - NVL(DISC.Discount_Taken,0)
- NVL(PS.Third_Disc_Amt_Available,0)),
((-1 * (NVL(PS.Second_Disc_Amt_Available,0) - NVL(DISC.Discount_Taken,0)
- NVL(PS.Third_Disc_Amt_Available,0)) * Conversion_Rate)
* RATES.Prim_Conversion_Rate)) / g_primary_mau) * g_primary_mau Prim_Discount_Available,
0 Prim_Discount_Taken,
ROUND(DECODE(AI.Invoice_Currency_Code, g_prim_currency,
NVL(PS.Second_Disc_Amt_Available,0) - NVL(DISC.Discount_Taken,0)
- NVL(PS.Third_Disc_Amt_Available,0),
(((NVL(PS.Second_Disc_Amt_Available,0) - NVL(DISC.Discount_Taken,0)
- NVL(PS.Third_Disc_Amt_Available,0)) * Conversion_Rate)
* RATES.Prim_Conversion_Rate)) / g_primary_mau) * g_primary_mau Prim_Discount_Lost,
0 Prim_Payment_Amount,
0 Prim_On_Time_Payment_Amt,
0 Prim_Late_Payment_Amt,
0 Prim_Due_Bucket1,
0 Prim_Due_Bucket2,
0 Prim_Due_Bucket3,
0 Prim_Past_Due_Bucket1,
0 Prim_Past_Due_Bucket2,
0 Prim_Past_Due_Bucket3,
0 Sec_Amount_Remaining,
0 Sec_Past_Due_Amount,
ROUND(DECODE(AI.Invoice_Currency_Code, g_sec_currency,
-1 * (NVL(PS.Second_Disc_Amt_Available,0) - NVL(DISC.Discount_Taken,0)
- NVL(PS.Third_Disc_Amt_Available,0)),
((-1 * (NVL(PS.Second_Disc_Amt_Available,0) - NVL(DISC.Discount_Taken,0)
- NVL(PS.Third_Disc_Amt_Available,0)) * Conversion_Rate)
* RATES.Sec_Conversion_Rate)) / g_secondary_mau) * g_secondary_mau Sec_Discount_Available,
0 Sec_Discount_Taken,
ROUND(DECODE(AI.Invoice_Currency_Code, g_sec_currency,
NVL(PS.Second_Disc_Amt_Available,0) - NVL(DISC.Discount_Taken,0)
- NVL(PS.Third_Disc_Amt_Available,0),
(((NVL(PS.Second_Disc_Amt_Available,0) - NVL(DISC.Discount_Taken,0)
- NVL(PS.Third_Disc_Amt_Available,0)) * Conversion_Rate)
* RATES.Sec_Conversion_Rate)) / g_secondary_mau) * g_secondary_mau Sec_Discount_Lost,
0 Sec_Payment_Amount,
0 Sec_On_Time_Payment_Amt,
0 Sec_Late_Payment_Amt,
0 Sec_Due_Bucket1,
0 Sec_Due_Bucket2,
0 Sec_Due_Bucket3,
0 Sec_Past_Due_Bucket1,
0 Sec_Past_Due_Bucket2,
0 Sec_Past_Due_Bucket3,
sysdate Creation_Date,
g_fii_user_id Last_Updated_By,
sysdate Last_Update_Date,
g_fii_login_id Last_Update_Login,
2 Unique_ID
FROM FII_AP_Invoice_B AI,
FII_AP_PS_Rates_Temp RATES,
FII_AP_Func_Rates_Temp FRATES,
AP_Payment_Schedules_All PS,
(SELECT /*+ index(TEM,FII_AP_WH_TAX_T_N1) */ TEM.Invoice_ID,
TEM.Payment_Num,
SUM(NVL(TEM.WH_Tax_Amount, 0)) WH_Tax_Amount
FROM FII_AP_WH_Tax_T TEM
WHERE TEM.Invoice_id BETWEEN g_start_range and g_end_range
AND TEM.Invoice_Type <> 'PREPAYMENT'
AND TEM.Second_Discount_Date + 1 BETWEEN g_start_date AND LEAST(g_end_date,g_sysdate)
AND TEM.Creation_Date < TEM.Second_Discount_Date + 1
GROUP BY TEM.Invoice_ID,
TEM.Payment_Num,
TEM.Second_Discount_Date) TEMP,
(SELECT /*+ index(prep,FII_AP_PREPAY_T_N1) */ PREP.Invoice_ID,
PREP.Payment_Num,
SUM(NVL(PREP.Prepay_Amount, 0)) Prepay_Amount
FROM FII_AP_Prepay_T PREP
WHERE PREP.Invoice_id BETWEEN g_start_range and g_end_range
AND PREP.Second_Discount_Date + 1 BETWEEN g_start_date AND LEAST(g_end_date,g_sysdate)
AND PREP.Creation_Date < PREP.Second_Discount_Date + 1
GROUP BY PREP.Invoice_ID,
PREP.Payment_Num,
PREP.Second_Discount_Date) PP,
(SELECT /*+ index(PC,FII_AP_PAY_CHK_STG_N1) */ PC.Invoice_ID,
PC.Payment_Num,
SUM(NVL(PC.Discount_Taken, 0)) Discount_Taken,
SUM(NVL(PC.Payment_Amount, 0)) Payment_Amount
FROM FII_AP_PAY_CHK_STG PC
WHERE PC.Invoice_id BETWEEN g_start_range and g_end_range
AND PC.Invoice_Type <> 'PREPAYMENT'
AND PC.Second_Discount_Date + 1 BETWEEN g_start_date AND LEAST(g_end_date,g_sysdate)
AND PC.Invp_Creation_Date < PC.Second_Discount_Date + 1
GROUP BY PC.Invoice_ID,
PC.Payment_Num,
PC.Second_Discount_Date) APC,
(SELECT PC.Invoice_ID Invoice_ID,
PC.Payment_Num Payment_Num,
NVL(SUM(NVL(PC.Discount_Taken,0)),0) Discount_Taken
FROM FII_AP_PAY_CHK_STG PC
WHERE PC.Invoice_ID BETWEEN g_start_range and g_end_range
AND PC.Second_Discount_Date + 1 BETWEEN g_start_date AND LEAST(g_end_date,g_sysdate)
AND PC.Invp_Creation_Date BETWEEN PC.Discount_Date + 1 AND PC.Second_Discount_Date
GROUP BY PC.Invoice_ID,
PC.Payment_Num) DISC
WHERE PS.Invoice_ID = AI.Invoice_ID
AND AI.Invoice_ID BETWEEN g_start_range and g_end_range
AND AI.Invoice_Type NOT IN ('PREPAYMENT')
AND AI.Cancel_Date IS NULL
AND FRATES.To_Currency = AI.Base_Currency_Code
AND FRATES.From_Currency = AI.Payment_Currency_Code
AND FRATES.Trx_Date = AI.Exchange_Date
AND DECODE(AI.Exchange_Rate_Type,'User', AI.Exchange_Rate,1) =
DECODE(FRATES.Conversion_Type,'User', FRATES.Conversion_Rate,1)
AND FRATES.Conversion_Type = AI.Exchange_Rate_Type
AND RATES.Functional_Currency = AI.Base_Currency_Code
AND RATES.Trx_Date = AI.Invoice_Date
AND TRUNC(PS.Second_Discount_Date) + 1 BETWEEN g_start_date AND LEAST(g_end_date,g_sysdate)
AND PS.Invoice_ID = TEMP.Invoice_ID(+)
AND PS.Payment_Num = TEMP.Payment_Num(+)
AND PS.Invoice_ID = PP.Invoice_ID(+)
AND PS.Payment_Num = PP.Payment_Num(+)
AND PS.Invoice_ID = APC.Invoice_ID(+)
AND PS.Payment_Num = APC.Payment_Num(+)
AND PS.Invoice_ID = DISC.Invoice_ID(+)
AND PS.Payment_Num = DISC.Payment_Num(+)
AND ABS(NVL(APC.Payment_Amount,0) + NVL(TEMP.WH_Tax_Amount,0)
+ NVL(PP.Prepay_Amount,0)) < ABS(PS.Gross_Amount);
FII_UTIL.put_line('Inserted ' ||SQL%ROWCOUNT|| ' Discount records into FII_AP_PAY_SCHED_B');
g_state := 'Inserting the Payment Schedules Third Discount Records';
/* Inserting the Discount Date passed records into the summary table.
We will insert only those payment schedules which have not be paid
fully before the third discount date. This check is done by comparing
the gross amount with the payment, prepayment and withheld amount */
INSERT INTO FII_AP_PAY_SCHED_B b
(Time_ID,
Period_Type_ID,
Action_Date,
Action,
Update_Sequence,
Org_ID,
Supplier_ID,
Invoice_ID,
Base_Currency_Code,
Trx_Date,
Payment_Num,
Due_Date,
Created_By,
Amount_Remaining,
Past_Due_Amount,
Discount_Available,
Discount_Taken,
Discount_Lost,
Payment_Amount,
On_Time_Payment_Amt,
Late_Payment_Amt,
No_Days_Late,
Due_Bucket1,
Due_Bucket2,
Due_Bucket3,
Past_Due_Bucket1,
Past_Due_Bucket2,
Past_Due_Bucket3,
Amount_Remaining_B,
Past_Due_Amount_B,
Discount_Available_B,
Discount_Taken_B,
Discount_Lost_B,
Payment_Amount_B,
On_Time_Payment_Amt_B,
Late_Payment_Amt_B,
Due_Bucket1_B,
Due_Bucket2_B,
Due_Bucket3_B,
Past_Due_Bucket1_B,
Past_Due_Bucket2_B,
Past_Due_Bucket3_B,
Prim_Amount_Remaining,
Prim_Past_Due_Amount,
Prim_Discount_Available,
Prim_Discount_Taken,
Prim_Discount_Lost,
Prim_Payment_Amount,
Prim_On_time_Payment_Amt,
Prim_Late_Payment_Amt,
Prim_Due_Bucket1,
Prim_Due_Bucket2,
Prim_Due_Bucket3,
Prim_Past_Due_Bucket1,
Prim_Past_Due_Bucket2,
Prim_Past_Due_Bucket3,
Sec_Amount_Remaining,
Sec_Past_Due_Amount,
Sec_Discount_Available,
Sec_Discount_Taken,
Sec_Discount_Lost,
Sec_Payment_Amount,
Sec_On_time_Payment_Amt,
Sec_Late_Payment_Amt,
Sec_Due_Bucket1,
Sec_Due_Bucket2,
Sec_Due_Bucket3,
Sec_Past_Due_Bucket1,
Sec_Past_Due_Bucket2,
Sec_Past_Due_Bucket3,
Creation_Date,
Last_Updated_By,
Last_Update_Date,
Last_Update_Login,
Unique_ID)
SELECT /*+ leading(ai) index(AI,FII_AP_INVOICE_B_U1) index(FRATES) use_nl(ps) */
TO_NUMBER(TO_CHAR(PS.Third_Discount_Date + 1,'J')) Time_ID,
1 Period_Type_ID,
TRUNC(PS.Third_Discount_Date) + 1 Action_Date,
'DISCOUNT' Action,
g_seq_id Update_Sequence,
AI.Org_ID Org_ID,
AI.Supplier_ID Supplier_ID,
AI.Invoice_ID Invoice_ID,
AI.Base_Currency_Code Base_Currency_Code,
AI.Invoice_Date Trx_Date,
PS.Payment_Num Payment_Num,
TRUNC(PS.Due_Date) Due_Date,
PS.Created_By Created_By,
0 Amount_Remaining,
0 Past_Due_Amount,
-1 * (NVL(PS.Third_Disc_Amt_Available,0)
- NVL(DISC.Discount_Taken,0)) Discount_Available,
0 Discount_Taken,
NVL(PS.Third_Disc_Amt_Available,0)
- NVL(DISC.Discount_Taken,0) Discount_Lost,
0 Payment_Amount,
0 On_Time_Payment_Amt,
0 Late_Payment_Amt,
0 No_Days_Late,
0 Due_Bucket1,
0 Due_Bucket2,
0 Due_Bucket3,
0 Past_Due_Bucket1,
0 Past_Due_Bucket2,
0 Past_Due_Bucket3,
0 Amount_Remaining_B,
0 Past_Due_Amount_B,
ROUND((-1 * (NVL(PS.Third_Disc_Amt_Available,0) - NVL(DISC.Discount_Taken,0)) * Conversion_Rate)
/ Functional_MAU) * Functional_MAU Discount_Available_B,
0 Discount_Taken_B,
ROUND(((NVL(PS.Third_Disc_Amt_Available,0) - NVL(DISC.Discount_Taken,0)) * Conversion_Rate)
/ Functional_MAU) * Functional_MAU Discount_Lost_B,
0 Payment_Amount_B,
0 On_Time_Payment_Amt_B,
0 Late_Payment_Amt_B,
0 Due_Bucket1_B,
0 Due_Bucket2_B,
0 Due_Bucket3_B,
0 Past_Due_Bucket1_B,
0 Past_Due_Bucket2_B,
0 Past_Due_Bucket3_B,
0 Prim_Amount_Remaining,
0 Prim_Past_Due_Amount,
ROUND(DECODE(AI.Invoice_Currency_Code, g_prim_currency,
-1 * (NVL(PS.Third_Disc_Amt_Available,0) - NVL(DISC.Discount_Taken,0)),
((-1 * (NVL(PS.Third_Disc_Amt_Available,0) - NVL(DISC.Discount_Taken,0)) * Conversion_Rate)
* RATES.Prim_Conversion_Rate)) / g_primary_mau) * g_primary_mau Prim_Discount_Available,
0 Prim_Discount_Taken,
ROUND(DECODE(AI.Invoice_Currency_Code, g_prim_currency,
NVL(PS.Third_Disc_Amt_Available,0) - NVL(DISC.Discount_Taken,0),
(((NVL(PS.Third_Disc_Amt_Available,0) - NVL(DISC.Discount_Taken,0)) * Conversion_Rate)
* RATES.Prim_Conversion_Rate)) / g_primary_mau) * g_primary_mau Prim_Discount_Lost,
0 Prim_Payment_Amount,
0 Prim_On_Time_Payment_Amt,
0 Prim_Late_Payment_Amt,
0 Prim_Due_Bucket1,
0 Prim_Due_Bucket2,
0 Prim_Due_Bucket3,
0 Prim_Past_Due_Bucket1,
0 Prim_Past_Due_Bucket2,
0 Prim_Past_Due_Bucket3,
0 Sec_Amount_Remaining,
0 Sec_Past_Due_Amount,
ROUND(DECODE(AI.Invoice_Currency_Code, g_sec_currency,
-1 * (NVL(PS.Third_Disc_Amt_Available,0) - NVL(DISC.Discount_Taken,0)),
((-1 * (NVL(PS.Third_Disc_Amt_Available,0) - NVL(DISC.Discount_Taken,0)) * Conversion_Rate)
* RATES.Sec_Conversion_Rate)) / g_secondary_mau) * g_secondary_mau Sec_Discount_Available,
0 Sec_Discount_Taken,
ROUND(DECODE(AI.Invoice_Currency_Code, g_sec_currency,
NVL(PS.Third_Disc_Amt_Available,0) - NVL(DISC.Discount_Taken,0),
(((NVL(PS.Third_Disc_Amt_Available,0) - NVL(DISC.Discount_Taken,0)) * Conversion_Rate)
* RATES.Sec_Conversion_Rate)) / g_secondary_mau) * g_secondary_mau Sec_Discount_Lost,
0 Sec_Payment_Amount,
0 Sec_On_Time_Payment_Amt,
0 Sec_Late_Payment_Amt,
0 Sec_Due_Bucket1,
0 Sec_Due_Bucket2,
0 Sec_Due_Bucket3,
0 Sec_Past_Due_Bucket1,
0 Sec_Past_Due_Bucket2,
0 Sec_Past_Due_Bucket3,
sysdate Creation_Date,
g_fii_user_id Last_Updated_By,
sysdate Last_Update_Date,
g_fii_login_id Last_Update_Login,
3 Unique_ID
FROM FII_AP_Invoice_B AI,
FII_AP_PS_Rates_Temp RATES,
FII_AP_Func_Rates_Temp FRATES,
AP_Payment_Schedules_All PS,
(SELECT /*+ index(TEM,FII_AP_WH_TAX_T_N1) */ TEM.Invoice_ID,
TEM.Payment_Num,
SUM(NVL(TEM.WH_Tax_Amount, 0)) WH_Tax_Amount
FROM FII_AP_WH_Tax_T TEM
WHERE TEM.Invoice_id BETWEEN g_start_range and g_end_range
AND TEM.Invoice_Type <> 'PREPAYMENT'
AND TEM.Third_Discount_Date + 1 BETWEEN g_start_date AND LEAST(g_end_date,g_sysdate)
AND TEM.Creation_Date < TEM.Third_Discount_Date + 1
GROUP BY TEM.Invoice_ID,
TEM.Payment_Num,
TEM.Third_Discount_Date) TEMP,
(SELECT /*+ index(prep,FII_AP_PREPAY_T_N1) */ PREP.Invoice_ID,
PREP.Payment_Num,
SUM(NVL(PREP.Prepay_Amount, 0)) Prepay_Amount
FROM FII_AP_Prepay_T PREP
WHERE PREP.Invoice_id BETWEEN g_start_range and g_end_range
AND PREP.Third_Discount_Date + 1 BETWEEN g_start_date AND LEAST(g_end_date,g_sysdate)
AND PREP.Creation_Date < PREP.Third_Discount_Date + 1
GROUP BY PREP.Invoice_ID,
PREP.Payment_Num,
PREP.Third_Discount_Date) PP,
(SELECT /*+ index(PC,FII_AP_PAY_CHK_STG_N1) */ PC.Invoice_ID,
PC.Payment_Num,
SUM(NVL(PC.Discount_Taken, 0)) Discount_Taken,
SUM(NVL(PC.Payment_Amount, 0)) Payment_Amount
FROM FII_AP_PAY_CHK_STG PC
WHERE PC.Invoice_id BETWEEN g_start_range and g_end_range
AND PC.Invoice_Type <> 'PREPAYMENT'
AND PC.Third_Discount_Date + 1 BETWEEN g_start_date AND LEAST(g_end_date,g_sysdate)
AND PC.Invp_Creation_Date < PC.Third_Discount_Date + 1
GROUP BY PC.Invoice_ID,
PC.Payment_Num,
PC.Third_Discount_Date) APC,
(SELECT PC.Invoice_ID Invoice_ID,
PC.Payment_Num Payment_Num,
NVL(SUM(NVL(PC.Discount_Taken,0)),0) Discount_Taken
FROM FII_AP_PAY_CHK_STG PC
WHERE PC.Invoice_ID BETWEEN g_start_range and g_end_range
AND PC.Third_Discount_Date + 1 BETWEEN g_start_date AND LEAST(g_end_date,g_sysdate)
AND PC.Invp_Creation_Date BETWEEN PC.Second_Discount_Date + 1
AND PC.Third_Discount_Date
GROUP BY PC.Invoice_ID,
PC.Payment_Num) DISC
WHERE PS.Invoice_ID = AI.Invoice_ID
AND AI.Invoice_ID BETWEEN g_start_range and g_end_range
AND AI.Invoice_Type NOT IN ('PREPAYMENT')
AND AI.Cancel_Date IS NULL
AND FRATES.To_Currency = AI.Base_Currency_Code
AND FRATES.From_Currency = AI.Payment_Currency_Code
AND FRATES.Trx_Date = AI.Exchange_Date
AND DECODE(AI.Exchange_Rate_Type,'User', AI.Exchange_Rate,1) =
DECODE(FRATES.Conversion_Type,'User', FRATES.Conversion_Rate,1)
AND FRATES.Conversion_Type = AI.Exchange_Rate_Type
AND RATES.Functional_Currency = AI.Base_Currency_Code
AND RATES.Trx_Date = AI.Invoice_Date
AND TRUNC(PS.Third_Discount_Date) + 1 BETWEEN g_start_date AND LEAST(g_end_date,g_sysdate)
AND PS.Invoice_ID = TEMP.Invoice_ID(+)
AND PS.Payment_Num = TEMP.Payment_Num(+)
AND PS.Invoice_ID = PP.Invoice_ID(+)
AND PS.Payment_Num = PP.Payment_Num(+)
AND PS.Invoice_ID = APC.Invoice_ID(+)
AND PS.Payment_Num = APC.Payment_Num(+)
AND PS.Invoice_ID = DISC.Invoice_ID(+)
AND PS.Payment_Num = DISC.Payment_Num(+)
AND ABS(NVL(APC.Payment_Amount,0) + NVL(TEMP.WH_Tax_Amount,0)
+ NVL(PP.Prepay_Amount,0)) < ABS(PS.Gross_Amount);
FII_UTIL.put_line('Inserted ' ||SQL%ROWCOUNT|| ' Discount records into FII_AP_PAY_SCHED_B');
g_state := 'Inserting the Payment Schedules Due Action';
/* Inserting the Due Date passed records into the summary table.
We will insert only those payment schedules which have not been paid
fully before the due date. This check is done by comparing
the gross amount with the payment, prepayment and withheld amount */
INSERT INTO FII_AP_PAY_SCHED_B b
(Time_ID,
Period_Type_ID,
Action_Date,
Action,
Update_Sequence,
Org_ID,
Supplier_ID,
Invoice_ID,
Base_Currency_Code,
Trx_Date,
Payment_Num,
Due_Date,
Created_By,
Amount_Remaining,
Past_Due_Amount,
Discount_Available,
Discount_Taken,
Discount_Lost,
Payment_Amount,
On_Time_Payment_Amt,
Late_Payment_Amt,
No_Days_Late,
Due_Bucket1,
Due_Bucket2,
Due_Bucket3,
Past_Due_Bucket1,
Past_Due_Bucket2,
Past_Due_Bucket3,
Amount_Remaining_B,
Past_Due_Amount_B,
Discount_Available_B,
Discount_Taken_B,
Discount_Lost_B,
Payment_Amount_B,
On_Time_Payment_Amt_B,
Late_Payment_Amt_B,
Due_Bucket1_B,
Due_Bucket2_B,
Due_Bucket3_B,
Past_Due_Bucket1_B,
Past_Due_Bucket2_B,
Past_Due_Bucket3_B,
Prim_Amount_Remaining,
Prim_Past_Due_Amount,
Prim_Discount_Available,
Prim_Discount_Taken,
Prim_Discount_Lost,
Prim_Payment_Amount,
Prim_On_time_Payment_Amt,
Prim_Late_Payment_Amt,
Prim_Due_Bucket1,
Prim_Due_Bucket2,
Prim_Due_Bucket3,
Prim_Past_Due_Bucket1,
Prim_Past_Due_Bucket2,
Prim_Past_Due_Bucket3,
Sec_Amount_Remaining,
Sec_Past_Due_Amount,
Sec_Discount_Available,
Sec_Discount_Taken,
Sec_Discount_Lost,
Sec_Payment_Amount,
Sec_On_time_Payment_Amt,
Sec_Late_Payment_Amt,
Sec_Due_Bucket1,
Sec_Due_Bucket2,
Sec_Due_Bucket3,
Sec_Past_Due_Bucket1,
Sec_Past_Due_Bucket2,
Sec_Past_Due_Bucket3,
Creation_Date,
Last_Updated_By,
Last_Update_Date,
Last_Update_Login)
SELECT /*+ leading(ai) index(AI,FII_AP_INVOICE_B_U1) use_nl(ps) */
TO_NUMBER(TO_CHAR(PS.Due_Date + 1,'J')) Time_ID,
1 Period_Type_ID,
TRUNC(PS.Due_Date) + 1 Action_Date,
'DUE' Action,
g_seq_id Update_Sequence,
AI.Org_ID Org_ID,
AI.Supplier_ID Supplier_ID,
AI.Invoice_ID Invoice_ID,
AI.Base_Currency_Code Base_Currency_Code,
AI.Invoice_Date Trx_Date,
PS.Payment_Num Payment_Num,
TRUNC(PS.Due_Date) Due_Date,
PS.Created_By Created_By,
0 Amount_Remaining,
PS.Gross_Amount - NVL(APC.Payment_Amount,0)
- NVL(TEMP.WH_Tax_Amount,0)
- NVL(PP.Prepay_Amount,0) Past_Due_Amount,
0 Discount_Available,
0 Discount_Taken,
0 Discount_Lost,
0 Payment_Amount,
0 On_Time_Payment_Amt,
0 Late_Payment_Amt,
0 No_Days_Late,
0 Due_Bucket1,
0 Due_Bucket2,
-1 * (PS.Gross_Amount - NVL(APC.Payment_Amount,0)
- NVL(TEMP.WH_Tax_Amount,0)
- NVL(PP.Prepay_Amount,0)) Due_Bucket3,
0 Past_Due_Bucket1,
0 Past_Due_Bucket2,
PS.Gross_Amount - NVL(APC.Payment_Amount,0)
- NVL(TEMP.WH_Tax_Amount,0)
- NVL(PP.Prepay_Amount,0) Past_Due_Bucket3,
0 Amount_Remaining_B,
ROUND(((PS.Gross_Amount - NVL(APC.Payment_Amount,0)
- NVL(TEMP.WH_Tax_Amount,0)
- NVL(PP.Prepay_Amount,0)) * Conversion_Rate)
/ Functional_MAU) * Functional_MAU Past_Due_Amount_B,
0 Discount_Available_B,
0 Discount_Taken_B,
0 Discount_Lost_B,
0 Payment_Amount_B,
0 On_Time_Payment_Amt_B,
0 Late_Payment_Amt_B,
0 Due_Bucket1_B,
0 Due_Bucket2_B,
ROUND((-1 * (PS.Gross_Amount - NVL(APC.Payment_Amount,0)
- NVL(TEMP.WH_Tax_Amount,0)
- NVL(PP.Prepay_Amount,0)) * Conversion_Rate)
/ Functional_MAU) * Functional_MAU Due_Bucket3_B,
0 Past_Due_Bucket1_B,
0 Past_Due_Bucket2_B,
ROUND(((PS.Gross_Amount - NVL(APC.Payment_Amount,0)
- NVL(TEMP.WH_Tax_Amount,0)
- NVL(PP.Prepay_Amount,0)) * Conversion_Rate)
/ Functional_MAU) * Functional_MAU Past_Due_Bucket3_B,
0 Prim_Amount_Remaining,
ROUND(DECODE(Invoice_Currency_Code, g_prim_currency,
(PS.Gross_Amount - NVL(APC.Payment_Amount,0)
- NVL(TEMP.WH_Tax_Amount,0)
- NVL(PP.Prepay_Amount,0)),
(((PS.Gross_Amount - NVL(APC.Payment_Amount,0)
- NVL(TEMP.WH_Tax_Amount,0)
- NVL(PP.Prepay_Amount,0)) * Conversion_Rate)
* RATES.Prim_Conversion_Rate)) / g_primary_mau) * g_primary_mau Prim_Past_Due_Amount,
0 Prim_Discount_Available,
0 Prim_Discount_Taken,
0 Prim_Discount_Lost,
0 Prim_Payment_Amount,
0 Prim_On_Time_Payment_Amt,
0 Prim_Late_Payment_Amt,
0 Prim_Due_Bucket1,
0 Prim_Due_Bucket2,
ROUND(DECODE(Invoice_Currency_Code, g_prim_currency,
(-1 * (PS.Gross_Amount - NVL(APC.Payment_Amount,0)
- NVL(TEMP.WH_Tax_Amount,0)
- NVL(PP.Prepay_Amount,0))),
((-1 * (PS.Gross_Amount - NVL(APC.Payment_Amount,0)
- NVL(TEMP.WH_Tax_Amount,0)
- NVL(PP.Prepay_Amount,0)) * Conversion_Rate)
* RATES.Prim_Conversion_Rate)) / g_primary_mau) * g_primary_mau Prim_Due_Bucket3,
0 Prim_Past_Due_Bucket1,
0 Prim_Past_Due_Bucket2,
ROUND(DECODE(Invoice_Currency_Code, g_prim_currency,
(PS.Gross_Amount - NVL(APC.Payment_Amount,0)
- NVL(TEMP.WH_Tax_Amount,0)
- NVL(PP.Prepay_Amount,0)),
(((PS.Gross_Amount - NVL(APC.Payment_Amount,0)
- NVL(TEMP.WH_Tax_Amount,0)
- NVL(PP.Prepay_Amount,0)) * Conversion_Rate)
* RATES.Prim_Conversion_Rate)) / g_primary_mau) * g_primary_mau Prim_Past_Due_Bucket3,
0 Sec_Amount_Remaining,
ROUND(DECODE(Invoice_Currency_Code, g_sec_currency,
(PS.Gross_Amount - NVL(APC.Payment_Amount,0)
- NVL(TEMP.WH_Tax_Amount,0)
- NVL(PP.Prepay_Amount,0)),
(((PS.Gross_Amount - NVL(APC.Payment_Amount,0)
- NVL(TEMP.WH_Tax_Amount,0)
- NVL(PP.Prepay_Amount,0)) * Conversion_Rate)
* RATES.Sec_Conversion_Rate)) / g_secondary_mau) * g_secondary_mau Sec_Past_Due_Amount,
0 Sec_Discount_Available,
0 Sec_Discount_Taken,
0 Sec_Discount_Lost,
0 Sec_Payment_Amount,
0 Sec_On_Time_Payment_Amt,
0 Sec_Late_Payment_Amt,
0 Sec_Due_Bucket1,
0 Sec_Due_Bucket2,
ROUND(DECODE(Invoice_Currency_Code, g_sec_currency,
(-1 * (PS.Gross_Amount - NVL(APC.Payment_Amount,0)
- NVL(TEMP.WH_Tax_Amount,0)
- NVL(PP.Prepay_Amount,0))),
((-1 * (PS.Gross_Amount - NVL(APC.Payment_Amount,0)
- NVL(TEMP.WH_Tax_Amount,0)
- NVL(PP.Prepay_Amount,0)) * Conversion_Rate)
* RATES.Sec_Conversion_Rate)) / g_secondary_mau) * g_secondary_mau Sec_Due_Bucket3,
0 Sec_Past_Due_Bucket1,
0 Sec_Past_Due_Bucket2,
ROUND(DECODE(Invoice_Currency_Code, g_sec_currency,
(PS.Gross_Amount - NVL(APC.Payment_Amount,0)
- NVL(TEMP.WH_Tax_Amount,0)
- NVL(PP.Prepay_Amount,0)),
(((PS.Gross_Amount - NVL(APC.Payment_Amount,0)
- NVL(TEMP.WH_Tax_Amount,0)
- NVL(PP.Prepay_Amount,0)) * Conversion_Rate)
* RATES.Sec_Conversion_Rate)) / g_secondary_mau) * g_secondary_mau Sec_Past_Due_Bucket3,
sysdate Creation_Date,
g_fii_user_id Last_Updated_By,
sysdate Last_Update_Date,
g_fii_login_id Last_Update_Login
FROM FII_AP_Invoice_B AI,
FII_AP_PS_Rates_Temp RATES,
FII_AP_Func_Rates_Temp FRATES,
AP_Payment_Schedules_All PS,
(SELECT /*+ index(TEM,FII_AP_WH_TAX_T_N1) */ TEM.Invoice_ID Invoice_ID,
TEM.Payment_Num Payment_Num,
SUM(NVL(TEM.WH_Tax_Amount, 0)) WH_Tax_Amount
FROM FII_AP_WH_TAX_T TEM
WHERE TEM.Invoice_ID BETWEEN g_start_range and g_end_range
AND TEM.Invoice_Type <> 'PREPAYMENT'
AND TEM.Due_Date + 1 BETWEEN g_start_date AND LEAST(g_end_date, g_sysdate)
AND TEM.Due_Date >= TEM.Entered_Date
AND TRUNC(TEM.Creation_Date) < TRUNC(TEM.Due_Date) + 1
GROUP BY TEM.Invoice_ID,
TEM.Payment_Num,
TEM.Due_Date) TEMP,
(SELECT /*+ index(prep,FII_AP_PREPAY_T_N1) */ PREP.Invoice_ID Invoice_ID,
PREP.Payment_Num Payment_Num,
SUM(NVL(PREP.Prepay_Amount, 0)) Prepay_Amount
FROM FII_AP_Prepay_T PREP
WHERE PREP.Invoice_ID BETWEEN g_start_range and g_end_range
AND PREP.Due_Date + 1 BETWEEN g_start_date AND LEAST(g_end_date, g_sysdate)
AND PREP.Due_Date >= PREP.Entered_Date
AND TRUNC(PREP.Creation_Date) < TRUNC(PREP.Due_Date) + 1
GROUP BY PREP.Invoice_ID,
PREP.Payment_Num,
PREP.Due_Date) PP,
(SELECT /*+ index(PC,FII_AP_PAY_CHK_STG_N1) */ PC.Invoice_ID,
PC.Payment_Num,
SUM(NVL(PC.Payment_Amount, 0)) Payment_Amount
FROM FII_AP_PAY_CHK_STG PC
WHERE PC.Invoice_id BETWEEN g_start_range and g_end_range
AND PC.Invoice_Type <> 'PREPAYMENT'
AND PC.Due_Date + 1 BETWEEN g_start_date AND LEAST(g_end_date, g_sysdate)
AND PC.Due_Date >= PC.Entered_Date
AND PC.Invp_Creation_Date < PC.due_date + 1
GROUP BY PC.Invoice_ID,
PC.Payment_Num,
PC.Due_Date) APC
WHERE PS.Invoice_ID = AI.Invoice_ID
AND AI.Invoice_ID BETWEEN g_start_range and g_end_range
AND AI.Invoice_Type NOT IN ('PREPAYMENT')
AND AI.Cancel_Date IS NULL
AND FRATES.To_Currency = AI.Base_Currency_Code
AND FRATES.From_Currency = AI.Payment_Currency_Code
AND FRATES.Trx_Date = AI.Exchange_Date
AND DECODE(AI.Exchange_Rate_Type,'User', AI.Exchange_Rate,1) =
DECODE(FRATES.Conversion_Type,'User', FRATES.Conversion_Rate,1)
AND FRATES.Conversion_Type = AI.Exchange_Rate_Type
AND RATES.Functional_Currency = AI.Base_Currency_Code
AND RATES.Trx_Date = AI.Invoice_Date
AND TRUNC(PS.Due_Date) + 1 BETWEEN g_start_date
AND LEAST(g_end_date,g_sysdate)
AND TRUNC(PS.Due_Date) >= AI.Entered_Date
AND PS.Invoice_ID = TEMP.Invoice_ID(+)
AND PS.Payment_Num = TEMP.Payment_Num(+)
AND PS.Invoice_ID = PP.Invoice_ID(+)
AND PS.Payment_Num = PP.Payment_Num(+)
AND PS.Invoice_ID = APC.Invoice_ID(+)
AND PS.Payment_Num = APC.Payment_Num(+)
AND ABS(NVL(APC.Payment_Amount,0) + NVL(TEMP.WH_Tax_Amount,0)
+ NVL(PP.Prepay_Amount,0)) < ABS(PS.Gross_Amount);
FII_UTIL.put_line('Inserted ' ||SQL%ROWCOUNT|| ' Due records into FII_AP_PAY_SCHED_B');
g_state := 'Inserting the Payment Schedules Due Bucket2 Action';
/* Inserting the Due Bucket records into the summary table.
We will insert only those payment schedules which have not been paid
fully before the due bucket2. This check is done by comparing
the gross amount with the payment, prepayment and withheld amount */
INSERT INTO FII_AP_PAY_SCHED_B b
(Time_ID,
Period_Type_ID,
Action_Date,
Action,
Update_Sequence,
Org_ID,
Supplier_ID,
Invoice_ID,
Base_Currency_Code,
Trx_Date,
Payment_Num,
Due_Date,
Created_By,
Amount_Remaining,
Past_Due_Amount,
Discount_Available,
Discount_Taken,
Discount_Lost,
Payment_Amount,
On_Time_Payment_Amt,
Late_Payment_Amt,
No_Days_Late,
Due_Bucket1,
Due_Bucket2,
Due_Bucket3,
Past_Due_Bucket1,
Past_Due_Bucket2,
Past_Due_Bucket3,
Amount_Remaining_B,
Past_Due_Amount_B,
Discount_Available_B,
Discount_Taken_B,
Discount_Lost_B,
Payment_Amount_B,
On_Time_Payment_Amt_B,
Late_Payment_Amt_B,
Due_Bucket1_B,
Due_Bucket2_B,
Due_Bucket3_B,
Past_Due_Bucket1_B,
Past_Due_Bucket2_B,
Past_Due_Bucket3_B,
Prim_Amount_Remaining,
Prim_Past_Due_Amount,
Prim_Discount_Available,
Prim_Discount_Taken,
Prim_Discount_Lost,
Prim_Payment_Amount,
Prim_On_time_Payment_Amt,
Prim_Late_Payment_Amt,
Prim_Due_Bucket1,
Prim_Due_Bucket2,
Prim_Due_Bucket3,
Prim_Past_Due_Bucket1,
Prim_Past_Due_Bucket2,
Prim_Past_Due_Bucket3,
Sec_Amount_Remaining,
Sec_Past_Due_Amount,
Sec_Discount_Available,
Sec_Discount_Taken,
Sec_Discount_Lost,
Sec_Payment_Amount,
Sec_On_time_Payment_Amt,
Sec_Late_Payment_Amt,
Sec_Due_Bucket1,
Sec_Due_Bucket2,
Sec_Due_Bucket3,
Sec_Past_Due_Bucket1,
Sec_Past_Due_Bucket2,
Sec_Past_Due_Bucket3,
Creation_Date,
Last_Updated_By,
Last_Update_Date,
Last_Update_Login)
SELECT /*+ leading(ai) index(AI,FII_AP_INVOICE_B_U1) use_nl(ps) */
TO_NUMBER(TO_CHAR((PS.Due_Date - g_due_bucket2),'J')) Time_ID,
1 Period_Type_ID,
(TRUNC(PS.Due_Date) - g_due_bucket2) Action_Date,
'DUE BUCKET' Action,
g_seq_id Update_Sequence,
AI.Org_ID Org_ID,
AI.Supplier_ID Supplier_ID,
AI.Invoice_ID Invoice_ID,
AI.Base_Currency_Code Base_Currency_Code,
AI.Invoice_Date Trx_Date,
PS.Payment_Num Payment_Num,
TRUNC(PS.Due_Date) Due_Date,
PS.Created_By Created_By,
0 Amount_Remaining,
0 Past_Due_Amount,
0 Discount_Available,
0 Discount_Taken,
0 Discount_Lost,
0 Payment_Amount,
0 On_Time_Payment_Amt,
0 Late_Payment_Amt,
0 No_Days_Late,
-1 * (PS.Gross_Amount - NVL(APC.Payment_Amount,0)
- NVL(TEMP.WH_Tax_Amount,0)
- NVL(PP.Prepay_Amount,0)) Due_Bucket1,
PS.Gross_Amount - NVL(APC.Payment_Amount,0)
- NVL(TEMP.WH_Tax_Amount,0)
- NVL(PP.Prepay_Amount,0) Due_Bucket2,
0 Due_Bucket3,
0 Past_Due_Bucket1,
0 Past_Due_Bucket2,
0 Past_Due_Bucket3,
0 Amount_Remaining_B,
0 Past_Due_Amount_B,
0 Discount_Available_B,
0 Discount_Taken_B,
0 Discount_Lost_B,
0 Payment_Amount_B,
0 On_Time_Payment_Amt_B,
0 Late_Payment_Amt_B,
ROUND((-1 * (PS.Gross_Amount - NVL(APC.Payment_Amount,0)
- NVL(TEMP.WH_Tax_Amount,0)
- NVL(PP.Prepay_Amount,0)) * Conversion_Rate)
/ Functional_MAU) * Functional_MAU Due_Bucket1_B,
ROUND(((PS.Gross_Amount - NVL(APC.Payment_Amount,0)
- NVL(TEMP.WH_Tax_Amount,0)
- NVL(PP.Prepay_Amount,0)) * Conversion_Rate)
/ Functional_MAU) * Functional_MAU Due_Bucket2_B,
0 Due_Bucket3_B,
0 Past_Due_Bucket1_B,
0 Past_Due_Bucket2_B,
0 Past_Due_Bucket3_B,
0 Prim_Amount_Remaining,
0 Prim_Past_Due_Amount,
0 Prim_Discount_Available,
0 Prim_Discount_Taken,
0 Prim_Discount_Lost,
0 Prim_Payment_Amount,
0 Prim_On_Time_Payment_Amt,
0 Prim_Late_Payment_Amt,
ROUND(DECODE(AI.Invoice_Currency_Code, g_prim_currency,
-1 * (PS.Gross_Amount - NVL(APC.Payment_Amount,0)
- NVL(TEMP.WH_Tax_Amount,0)
- NVL(PP.Prepay_Amount,0)),
((-1 * (PS.Gross_Amount - NVL(APC.Payment_Amount,0)
- NVL(TEMP.WH_Tax_Amount,0)
- NVL(PP.Prepay_Amount,0)) * Conversion_Rate)
* RATES.Prim_Conversion_Rate)) / g_primary_mau) * g_primary_mau Prim_Due_Bucket1,
ROUND(DECODE(AI.Invoice_Currency_Code, g_prim_currency,
PS.Gross_Amount - NVL(APC.Payment_Amount,0)
- NVL(TEMP.WH_Tax_Amount,0)
- NVL(PP.Prepay_Amount,0),
(((PS.Gross_Amount - NVL(APC.Payment_Amount,0)
- NVL(TEMP.WH_Tax_Amount,0)
- NVL(PP.Prepay_Amount,0)) * Conversion_Rate)
* RATES.Prim_Conversion_Rate)) / g_primary_mau) * g_primary_mau Prim_Due_Bucket2,
0 Prim_Due_Bucket3,
0 Prim_Past_Due_Bucket1,
0 Prim_Past_Due_Bucket2,
0 Prim_Past_Due_Bucket3,
0 Sec_Amount_Remaining,
0 Sec_Past_Due_Amount,
0 Sec_Discount_Available,
0 Sec_Discount_Taken,
0 Sec_Discount_Lost,
0 Sec_Payment_Amount,
0 Sec_On_Time_Payment_Amt,
0 Sec_Late_Payment_Amt,
ROUND(DECODE(AI.Invoice_Currency_Code, g_sec_currency,
-1 * (PS.Gross_Amount - NVL(APC.Payment_Amount,0)
- NVL(TEMP.WH_Tax_Amount,0)
- NVL(PP.Prepay_Amount,0)),
((-1 * (PS.Gross_Amount - NVL(APC.Payment_Amount,0)
- NVL(TEMP.WH_Tax_Amount,0)
- NVL(PP.Prepay_Amount,0)) * Conversion_Rate)
* RATES.Sec_Conversion_Rate)) / g_secondary_mau) * g_secondary_mau Sec_Due_Bucket1,
ROUND(DECODE(AI.Invoice_Currency_Code, g_sec_currency,
PS.Gross_Amount - NVL(APC.Payment_Amount,0)
- NVL(TEMP.WH_Tax_Amount,0)
- NVL(PP.Prepay_Amount,0),
(((PS.Gross_Amount - NVL(APC.Payment_Amount,0)
- NVL(TEMP.WH_Tax_Amount,0)
- NVL(PP.Prepay_Amount,0)) * Conversion_Rate)
* RATES.Sec_Conversion_Rate)) / g_secondary_mau) * g_secondary_mau Sec_Due_Bucket2,
0 Sec_Due_Bucket3,
0 Sec_Past_Due_Bucket1,
0 Sec_Past_Due_Bucket2,
0 Sec_Past_Due_Bucket3,
sysdate Creation_Date,
g_fii_user_id Last_Updated_By,
sysdate Last_Update_Date,
g_fii_login_id Last_Update_Login
FROM FII_AP_Invoice_B AI,
FII_AP_PS_Rates_Temp RATES,
FII_AP_Func_Rates_Temp FRATES,
AP_Payment_Schedules_All PS,
(SELECT /*+ index(TEM,FII_AP_WH_TAX_T_N1) */ TEM.Invoice_ID,
TEM.Payment_Num,
SUM(NVL(TEM.WH_Tax_Amount, 0)) WH_Tax_Amount
FROM FII_AP_WH_Tax_T TEM
WHERE TEM.Invoice_id BETWEEN g_start_range and g_end_range
AND TEM.Invoice_Type <> 'PREPAYMENT'
AND (TEM.Due_Date - g_due_bucket2) BETWEEN g_start_date AND LEAST(g_end_date,g_sysdate)
AND TEM.Creation_Date < (TEM.Due_Date - g_due_bucket2)
GROUP BY TEM.Invoice_ID,
TEM.Payment_Num,
TEM.Due_Date) TEMP,
(SELECT /*+ index(prep,FII_AP_PREPAY_T_N1) */ PREP.Invoice_ID,
PREP.Payment_Num,
SUM(NVL(PREP.Prepay_Amount, 0)) Prepay_Amount
FROM FII_AP_Prepay_T PREP
WHERE PREP.Invoice_id BETWEEN g_start_range and g_end_range
AND (PREP.Due_Date - g_due_bucket2) BETWEEN g_start_date AND LEAST(g_end_date,g_sysdate)
AND PREP.Creation_Date < (PREP.Due_Date - g_due_bucket2)
GROUP BY PREP.Invoice_ID,
PREP.Payment_Num,
PREP.Due_Date) PP,
(SELECT /*+ index(PC,FII_AP_PAY_CHK_STG_N1) */ PC.Invoice_ID,
PC.Payment_Num,
SUM(NVL(PC.Payment_Amount, 0)) Payment_Amount
FROM FII_AP_PAY_CHK_STG PC
WHERE PC.Invoice_id BETWEEN g_start_range and g_end_range
AND PC.Invoice_Type <> 'PREPAYMENT'
AND (PC.Due_Date - g_due_bucket2) BETWEEN g_start_date AND LEAST(g_end_date,g_sysdate)
AND PC.Invp_Creation_Date < (PC.Due_Date - g_due_bucket2)
GROUP BY PC.Invoice_ID,
PC.Payment_Num,
PC.Due_Date) APC
WHERE PS.Invoice_ID = AI.Invoice_ID
AND AI.Invoice_ID BETWEEN g_start_range and g_end_range
AND AI.Invoice_Type NOT IN ('PREPAYMENT')
AND AI.Cancel_Date IS NULL
AND FRATES.To_Currency = AI.Base_Currency_Code
AND FRATES.From_Currency = AI.Payment_Currency_Code
AND FRATES.Trx_Date = AI.Exchange_Date
AND DECODE(AI.Exchange_Rate_Type,'User', AI.Exchange_Rate,1) =
DECODE(FRATES.Conversion_Type,'User', FRATES.Conversion_Rate,1)
AND FRATES.Conversion_Type = AI.Exchange_Rate_Type
AND RATES.Functional_Currency = AI.Base_Currency_Code
AND RATES.Trx_Date = AI.Invoice_Date
AND (TRUNC(PS.Due_Date) - AI.Entered_Date) > g_due_bucket2
AND (TRUNC(PS.Due_Date) - g_due_bucket2) BETWEEN g_start_date
AND LEAST(g_end_date,g_sysdate)
AND PS.Invoice_ID = TEMP.Invoice_ID(+)
AND PS.Payment_Num = TEMP.Payment_Num(+)
AND PS.Invoice_ID = PP.Invoice_ID(+)
AND PS.Payment_Num = PP.Payment_Num(+)
AND PS.Invoice_ID = APC.Invoice_ID(+)
AND PS.Payment_Num = APC.Payment_Num(+)
AND ABS(NVL(APC.Payment_Amount,0) + NVL(TEMP.WH_Tax_Amount,0)
+ NVL(PP.Prepay_Amount,0)) < ABS(PS.Gross_Amount);
FII_UTIL.put_line('Inserted ' ||SQL%ROWCOUNT|| ' Due Bucket2 records into FII_AP_PAY_SCHED_B');
g_state := 'Inserting the Payment Schedules Due Bucket3 Action';
/* Inserting the Due Bucket records into the summary table.
We will insert only those payment schedules which have not been paid
fully before the due bucket3. This check is done by comparing
the gross amount with the payment, prepayment and withheld amount */
INSERT INTO FII_AP_PAY_SCHED_B b
(Time_ID,
Period_Type_ID,
Action_Date,
Action,
Update_Sequence,
Org_ID,
Supplier_ID,
Invoice_ID,
Base_Currency_Code,
Trx_Date,
Payment_Num,
Due_Date,
Created_By,
Amount_Remaining,
Past_Due_Amount,
Discount_Available,
Discount_Taken,
Discount_Lost,
Payment_Amount,
On_Time_Payment_Amt,
Late_Payment_Amt,
No_Days_Late,
Due_Bucket1,
Due_Bucket2,
Due_Bucket3,
Past_Due_Bucket1,
Past_Due_Bucket2,
Past_Due_Bucket3,
Amount_Remaining_B,
Past_Due_Amount_B,
Discount_Available_B,
Discount_Taken_B,
Discount_Lost_B,
Payment_Amount_B,
On_Time_Payment_Amt_B,
Late_Payment_Amt_B,
Due_Bucket1_B,
Due_Bucket2_B,
Due_Bucket3_B,
Past_Due_Bucket1_B,
Past_Due_Bucket2_B,
Past_Due_Bucket3_B,
Prim_Amount_Remaining,
Prim_Past_Due_Amount,
Prim_Discount_Available,
Prim_Discount_Taken,
Prim_Discount_Lost,
Prim_Payment_Amount,
Prim_On_time_Payment_Amt,
Prim_Late_Payment_Amt,
Prim_Due_Bucket1,
Prim_Due_Bucket2,
Prim_Due_Bucket3,
Prim_Past_Due_Bucket1,
Prim_Past_Due_Bucket2,
Prim_Past_Due_Bucket3,
Sec_Amount_Remaining,
Sec_Past_Due_Amount,
Sec_Discount_Available,
Sec_Discount_Taken,
Sec_Discount_Lost,
Sec_Payment_Amount,
Sec_On_time_Payment_Amt,
Sec_Late_Payment_Amt,
Sec_Due_Bucket1,
Sec_Due_Bucket2,
Sec_Due_Bucket3,
Sec_Past_Due_Bucket1,
Sec_Past_Due_Bucket2,
Sec_Past_Due_Bucket3,
Creation_Date,
Last_Updated_By,
Last_Update_Date,
Last_Update_Login)
SELECT /*+ leading(ai) index(AI,FII_AP_INVOICE_B_U1) use_nl(ps) */
TO_NUMBER(TO_CHAR((PS.Due_Date - g_due_bucket3),'J')) Time_ID,
1 Period_Type_ID,
(TRUNC(PS.Due_Date) - g_due_bucket3) Action_Date,
'DUE BUCKET',
g_seq_id Update_Sequence,
AI.Org_ID Org_ID,
AI.Supplier_ID Supplier_ID,
AI.Invoice_ID Invoice_ID,
AI.Base_Currency_Code Base_Currency_Code,
AI.Invoice_Date Trx_Date,
PS.Payment_Num Payment_Num,
TRUNC(PS.Due_Date) Due_Date,
PS.Created_By Created_By,
0 Amount_Remaining,
0 Past_Due_Amount,
0 Discount_Available,
0 Discount_Taken,
0 Discount_Lost,
0 Payment_Amount,
0 On_Time_Payment_Amt,
0 Late_Payment_Amt,
0 No_Days_Late,
0 Due_Bucket1,
-1 * (PS.Gross_Amount - NVL(APC.Payment_Amount,0)
- NVL(TEMP.WH_Tax_Amount,0)
- NVL(PP.Prepay_Amount,0)) Due_Bucket2,
PS.Gross_Amount - NVL(APC.Payment_Amount,0)
- NVL(TEMP.WH_Tax_Amount,0)
- NVL(PP.Prepay_Amount,0) Due_Bucket3,
0 Past_Due_Bucket1,
0 Past_Due_Bucket2,
0 Past_Due_Bucket3,
0 Amount_Remaining_B,
0 Past_Due_Amount_B,
0 Discount_Available_B,
0 Discount_Taken_B,
0 Discount_Lost_B,
0 Payment_Amount_B,
0 On_Time_Payment_Amt_B,
0 Late_Payment_Amt_B,
0 Due_Bucket1_B,
ROUND((-1 * (PS.Gross_Amount - NVL(APC.Payment_Amount,0)
- NVL(TEMP.WH_Tax_Amount,0)
- NVL(PP.Prepay_Amount,0)) * Conversion_Rate)
/ Functional_MAU) * Functional_MAU Due_Bucket2_B,
ROUND(((PS.Gross_Amount - NVL(APC.Payment_Amount,0)
- NVL(TEMP.WH_Tax_Amount,0)
- NVL(PP.Prepay_Amount,0)) * Conversion_Rate)
/ Functional_MAU) * Functional_MAU Due_Bucket3_B,
0 Past_Due_Bucket1_B,
0 Past_Due_Bucket2_B,
0 Past_Due_Bucket3_B,
0 Prim_Amount_Remaining,
0 Prim_Past_Due_Amount,
0 Prim_Discount_Available,
0 Prim_Discount_Taken,
0 Prim_Discount_Lost,
0 Prim_Payment_Amount,
0 Prim_On_Time_Payment_Amt,
0 Prim_Late_Payment_Amt,
0 Prim_Due_Bucket1,
ROUND(DECODE(AI.Invoice_Currency_Code, g_prim_currency,
-1 * (PS.Gross_Amount - NVL(APC.Payment_Amount,0)
- NVL(TEMP.WH_Tax_Amount,0)
- NVL(PP.Prepay_Amount,0)),
((-1 * (PS.Gross_Amount - NVL(APC.Payment_Amount,0)
- NVL(TEMP.WH_Tax_Amount,0)
- NVL(PP.Prepay_Amount,0)) * Conversion_Rate)
* RATES.Prim_Conversion_Rate)) / g_primary_mau) * g_primary_mau Prim_Due_Bucket2,
ROUND(DECODE(AI.Invoice_Currency_Code, g_prim_currency,
PS.Gross_Amount - NVL(APC.Payment_Amount,0)
- NVL(TEMP.WH_Tax_Amount,0)
- NVL(PP.Prepay_Amount,0),
(((PS.Gross_Amount - NVL(APC.Payment_Amount,0)
- NVL(TEMP.WH_Tax_Amount,0)
- NVL(PP.Prepay_Amount,0)) * Conversion_Rate)
* RATES.Prim_Conversion_Rate)) / g_primary_mau) * g_primary_mau Prim_Due_Bucket3,
0 Prim_Past_Due_Bucket1,
0 Prim_Past_Due_Bucket2,
0 Prim_Past_Due_Bucket3,
0 Sec_Amount_Remaining,
0 Sec_Past_Due_Amount,
0 Sec_Discount_Available,
0 Sec_Discount_Taken,
0 Sec_Discount_Lost,
0 Sec_Payment_Amount,
0 Sec_On_Time_Payment_Amt,
0 Sec_Late_Payment_Amt,
0 Sec_Due_Bucket1,
ROUND(DECODE(AI.Invoice_Currency_Code, g_sec_currency,
-1 * (PS.Gross_Amount - NVL(APC.Payment_Amount,0)
- NVL(TEMP.WH_Tax_Amount,0)
- NVL(PP.Prepay_Amount,0)),
((-1 * (PS.Gross_Amount - NVL(APC.Payment_Amount,0)
- NVL(TEMP.WH_Tax_Amount,0)
- NVL(PP.Prepay_Amount,0)) * Conversion_Rate)
* RATES.Sec_Conversion_Rate)) / g_secondary_mau) * g_secondary_mau Sec_Due_Bucket2,
ROUND(DECODE(AI.Invoice_Currency_Code, g_sec_currency,
PS.Gross_Amount - NVL(APC.Payment_Amount,0)
- NVL(TEMP.WH_Tax_Amount,0)
- NVL(PP.Prepay_Amount,0),
(((PS.Gross_Amount - NVL(APC.Payment_Amount,0)
- NVL(TEMP.WH_Tax_Amount,0)
- NVL(PP.Prepay_Amount,0)) * Conversion_Rate)
* RATES.Sec_Conversion_Rate)) / g_secondary_mau) * g_secondary_mau Sec_Due_Bucket3,
0 Sec_Past_Due_Bucket1,
0 Sec_Past_Due_Bucket2,
0 Sec_Past_Due_Bucket3,
sysdate Creation_Date,
g_fii_user_id Last_Updated_By,
sysdate Last_Update_Date,
g_fii_login_id Last_Update_Login
FROM FII_AP_Invoice_B AI,
FII_AP_PS_Rates_Temp RATES,
FII_AP_Func_Rates_Temp FRATES,
AP_Payment_Schedules_All PS,
(SELECT /*+ index(TEM,FII_AP_WH_TAX_T_N1) */ TEM.Invoice_ID,
TEM.Payment_Num,
SUM(NVL(TEM.WH_Tax_Amount, 0)) WH_Tax_Amount
FROM FII_AP_WH_Tax_T TEM
WHERE TEM.Invoice_id BETWEEN g_start_range and g_end_range
AND TEM.Invoice_Type <> 'PREPAYMENT'
AND (TEM.Due_Date - g_due_bucket3) BETWEEN g_start_date AND LEAST(g_end_date,g_sysdate)
AND TEM.Creation_Date < (TEM.Due_Date - g_due_bucket3)
GROUP BY TEM.Invoice_ID,
TEM.Payment_Num,
TEM.Due_Date) TEMP,
(SELECT /*+ index(prep,FII_AP_PREPAY_T_N1) */ PREP.Invoice_ID,
PREP.Payment_Num,
SUM(NVL(PREP.Prepay_Amount, 0)) Prepay_Amount
FROM FII_AP_Prepay_T PREP
WHERE PREP.Invoice_id BETWEEN g_start_range and g_end_range
AND (PREP.Due_Date - g_due_bucket3) BETWEEN g_start_date AND LEAST(g_end_date,g_sysdate)
AND PREP.Creation_Date < (PREP.Due_Date - g_due_bucket3)
GROUP BY PREP.Invoice_ID,
PREP.Payment_Num,
PREP.Due_Date) PP,
(SELECT /*+ index(PC,FII_AP_PAY_CHK_STG_N1) */ PC.Invoice_ID,
PC.Payment_Num,
SUM(NVL(PC.Payment_Amount, 0)) Payment_Amount
FROM FII_AP_PAY_CHK_STG PC
WHERE PC.Invoice_id BETWEEN g_start_range and g_end_range
AND PC.Invoice_Type <> 'PREPAYMENT'
AND (PC.Due_Date - g_due_bucket3) BETWEEN g_start_date AND LEAST(g_end_date,g_sysdate)
AND PC.Invp_Creation_Date < (PC.Due_Date - g_due_bucket3)
GROUP BY PC.Invoice_ID,
PC.Payment_Num,
PC.Due_Date) APC
WHERE PS.Invoice_ID = AI.Invoice_ID
AND AI.Invoice_ID BETWEEN g_start_range and g_end_range
AND AI.Invoice_Type NOT IN ('PREPAYMENT')
AND AI.Cancel_Date IS NULL
AND FRATES.To_Currency = AI.Base_Currency_Code
AND FRATES.From_Currency = AI.Payment_Currency_Code
AND FRATES.Trx_Date = AI.Exchange_Date
AND DECODE(AI.Exchange_Rate_Type,'User', AI.Exchange_Rate,1) =
DECODE(FRATES.Conversion_Type,'User', FRATES.Conversion_Rate,1)
AND FRATES.Conversion_Type = AI.Exchange_Rate_Type
AND RATES.Functional_Currency = AI.Base_Currency_Code
AND RATES.Trx_Date = AI.Invoice_Date
AND (TRUNC(PS.Due_Date) - AI.Entered_Date) > g_due_bucket3
AND (TRUNC(PS.Due_Date) - g_due_bucket3) BETWEEN g_start_date
AND LEAST(g_end_date,g_sysdate)
AND PS.Invoice_ID = TEMP.Invoice_ID(+)
AND PS.Payment_Num = TEMP.Payment_Num(+)
AND PS.Invoice_ID = PP.Invoice_ID(+)
AND PS.Payment_Num = PP.Payment_Num(+)
AND PS.Invoice_ID = APC.Invoice_ID(+)
AND PS.Payment_Num = APC.Payment_Num(+)
AND ABS(NVL(APC.Payment_Amount,0) + NVL(TEMP.WH_Tax_Amount,0)
+ NVL(PP.Prepay_Amount,0)) < ABS(PS.Gross_Amount);
FII_UTIL.put_line('Inserted ' ||SQL%ROWCOUNT|| ' Due Bucket3 records into FII_AP_PAY_SCHED_B');
g_state := 'Inserting the Payment Schedules Past Due Bucket2 Action';
/* Inserting the Past Due Bucket records into the summary table.
We will insert only those payment schedules which have not been paid
fully before the past due bucket2. This check is done by comparing
the gross amount with the payment, prepayment and withheld amount */
INSERT INTO FII_AP_PAY_SCHED_B b
(Time_ID,
Period_Type_ID,
Action_Date,
Action,
Update_Sequence,
Org_ID,
Supplier_ID,
Invoice_ID,
Base_Currency_Code,
Trx_Date,
Payment_Num,
Due_Date,
Created_By,
Amount_Remaining,
Past_Due_Amount,
Discount_Available,
Discount_Taken,
Discount_Lost,
Payment_Amount,
On_Time_Payment_Amt,
Late_Payment_Amt,
No_Days_Late,
Due_Bucket1,
Due_Bucket2,
Due_Bucket3,
Past_Due_Bucket1,
Past_Due_Bucket2,
Past_Due_Bucket3,
Amount_Remaining_B,
Past_Due_Amount_B,
Discount_Available_B,
Discount_Taken_B,
Discount_Lost_B,
Payment_Amount_B,
On_Time_Payment_Amt_B,
Late_Payment_Amt_B,
Due_Bucket1_B,
Due_Bucket2_B,
Due_Bucket3_B,
Past_Due_Bucket1_B,
Past_Due_Bucket2_B,
Past_Due_Bucket3_B,
Prim_Amount_Remaining,
Prim_Past_Due_Amount,
Prim_Discount_Available,
Prim_Discount_Taken,
Prim_Discount_Lost,
Prim_Payment_Amount,
Prim_On_time_Payment_Amt,
Prim_Late_Payment_Amt,
Prim_Due_Bucket1,
Prim_Due_Bucket2,
Prim_Due_Bucket3,
Prim_Past_Due_Bucket1,
Prim_Past_Due_Bucket2,
Prim_Past_Due_Bucket3,
Sec_Amount_Remaining,
Sec_Past_Due_Amount,
Sec_Discount_Available,
Sec_Discount_Taken,
Sec_Discount_Lost,
Sec_Payment_Amount,
Sec_On_time_Payment_Amt,
Sec_Late_Payment_Amt,
Sec_Due_Bucket1,
Sec_Due_Bucket2,
Sec_Due_Bucket3,
Sec_Past_Due_Bucket1,
Sec_Past_Due_Bucket2,
Sec_Past_Due_Bucket3,
Creation_Date,
Last_Updated_By,
Last_Update_Date,
Last_Update_Login)
SELECT /*+ leading(ai) index(AI,FII_AP_INVOICE_B_U1) use_nl(ps) */
TO_NUMBER(TO_CHAR(PS.Due_Date + g_past_bucket3 + 1,'J')) Time_ID,
1 Period_Type_ID,
TRUNC(PS.Due_Date) + g_past_bucket3 + 1 Action_Date,
'PAST BUCKET' Action,
g_seq_id Update_Sequence,
AI.Org_ID Org_ID,
AI.Supplier_ID Supplier_ID,
AI.Invoice_ID Invoice_ID,
AI.Base_Currency_Code Base_Currency_Code,
AI.Invoice_Date Trx_Date,
PS.Payment_Num Payment_Num,
TRUNC(PS.Due_Date) Due_Date,
PS.Created_By Created_By,
0 Amount_Remaining,
0 Past_Due_Amount,
0 Discount_Available,
0 Discount_Taken,
0 Discount_Lost,
0 Payment_Amount,
0 On_Time_Payment_Amt,
0 Late_Payment_Amt,
0 No_Days_Late,
0 Due_Bucket1,
0 Due_Bucket2,
0 Due_Bucket3,
0 Past_Due_Bucket1,
PS.Gross_Amount - NVL(APC.Payment_Amount,0)
- NVL(TEMP.WH_Tax_Amount,0)
- NVL(PP.Prepay_Amount,0) Past_Due_Bucket2,
-1 * (PS.Gross_Amount - NVL(APC.Payment_Amount,0)
- NVL(TEMP.WH_Tax_Amount,0)
- NVL(PP.Prepay_Amount,0)) Past_Due_Bucket3,
0 Amount_Remaining_B,
0 Past_Due_Amount_B,
0 Discount_Available_B,
0 Discount_Taken_B,
0 Discount_Lost_B,
0 Payment_Amount_B,
0 On_Time_Payment_Amt_B,
0 Late_Payment_Amt_B,
0 Due_Bucket1_B,
0 Due_Bucket2_B,
0 Due_Bucket3_B,
0 Past_Due_Bucket1_B,
ROUND(((PS.Gross_Amount - NVL(APC.Payment_Amount,0)
- NVL(TEMP.WH_Tax_Amount,0)
- NVL(PP.Prepay_Amount,0)) * Conversion_Rate)
/ Functional_MAU) * Functional_MAU Past_Due_Bucket2_B,
ROUND((-1 * (PS.Gross_Amount - NVL(APC.Payment_Amount,0)
- NVL(TEMP.WH_Tax_Amount,0)
- NVL(PP.Prepay_Amount,0)) * Conversion_Rate)
/ Functional_MAU) * Functional_MAU Past_Due_Bucket3_B,
0 Prim_Amount_Remaining,
0 Prim_Past_Due_Amount,
0 Prim_Discount_Available,
0 Prim_Discount_Taken,
0 Prim_Discount_Lost,
0 Prim_Payment_Amount,
0 Prim_On_Time_Payment_Amt,
0 Prim_Late_Payment_Amt,
0 Prim_Due_Bucket1,
0 Prim_Due_Bucket2,
0 Prim_Due_Bucket3,
0 Prim_Past_Due_Bucket1,
ROUND(DECODE(AI.Invoice_Currency_Code, g_prim_currency,
PS.Gross_Amount - NVL(APC.Payment_Amount,0)
- NVL(TEMP.WH_Tax_Amount,0)
- NVL(PP.Prepay_Amount,0),
(((PS.Gross_Amount - NVL(APC.Payment_Amount,0)
- NVL(TEMP.WH_Tax_Amount,0)
- NVL(PP.Prepay_Amount,0)) * Conversion_Rate)
* RATES.Prim_Conversion_Rate)) / g_primary_mau) * g_primary_mau Prim_Past_Due_Bucket2,
ROUND(DECODE(AI.Invoice_Currency_Code, g_prim_currency,
-1 * (PS.Gross_Amount - NVL(APC.Payment_Amount,0)
- NVL(TEMP.WH_Tax_Amount,0)
- NVL(PP.Prepay_Amount,0)),
((-1 * (PS.Gross_Amount - NVL(APC.Payment_Amount,0)
- NVL(TEMP.WH_Tax_Amount,0)
- NVL(PP.Prepay_Amount,0)) * Conversion_Rate)
* RATES.Prim_Conversion_Rate)) / g_primary_mau) * g_primary_mau Prim_Past_Due_Bucket3,
0 Sec_Amount_Remaining,
0 Sec_Past_Due_Amount,
0 Sec_Discount_Available,
0 Sec_Discount_Taken,
0 Sec_Discount_Lost,
0 Sec_Payment_Amount,
0 Sec_On_Time_Payment_Amt,
0 Sec_Late_Payment_Amt,
0 Sec_Due_Bucket1,
0 Sec_Due_Bucket2,
0 Sec_Due_Bucket3,
0 Sec_Past_Due_Bucket1,
ROUND(DECODE(AI.Invoice_Currency_Code, g_sec_currency,
PS.Gross_Amount - NVL(APC.Payment_Amount,0)
- NVL(TEMP.WH_Tax_Amount,0)
- NVL(PP.Prepay_Amount,0),
(((PS.Gross_Amount - NVL(APC.Payment_Amount,0)
- NVL(TEMP.WH_Tax_Amount,0)
- NVL(PP.Prepay_Amount,0)) * Conversion_Rate)
* RATES.Sec_Conversion_Rate)) / g_secondary_mau) * g_secondary_mau Sec_Past_Due_Bucket2,
ROUND(DECODE(AI.Invoice_Currency_Code, g_sec_currency,
-1 * (PS.Gross_Amount - NVL(APC.Payment_Amount,0)
- NVL(TEMP.WH_Tax_Amount,0)
- NVL(PP.Prepay_Amount,0)),
((-1 * (PS.Gross_Amount - NVL(APC.Payment_Amount,0)
- NVL(TEMP.WH_Tax_Amount,0)
- NVL(PP.Prepay_Amount,0)) * Conversion_Rate)
* RATES.Sec_Conversion_Rate)) / g_secondary_mau) * g_secondary_mau Sec_Past_Due_Bucket3,
sysdate Creation_Date,
g_fii_user_id Last_Updated_By,
sysdate Last_Update_Date,
g_fii_login_id Last_Update_Login
FROM FII_AP_Invoice_B AI,
FII_AP_PS_Rates_Temp RATES,
FII_AP_Func_Rates_Temp FRATES,
AP_Payment_Schedules_All PS,
(SELECT /*+ index(TEM,FII_AP_WH_TAX_T_N1) */ TEM.Invoice_ID,
TEM.Payment_Num,
SUM(NVL(TEM.WH_Tax_Amount, 0)) WH_Tax_Amount
FROM FII_AP_WH_Tax_T TEM
WHERE TEM.Invoice_id BETWEEN g_start_range and g_end_range
AND TEM.Invoice_Type <> 'PREPAYMENT'
AND (TEM.Due_Date + g_past_bucket3) + 1 BETWEEN g_start_date AND LEAST(g_end_date,g_sysdate)
AND TEM.Creation_Date < (TEM.Due_Date + g_past_bucket3) + 1
GROUP BY TEM.Invoice_ID,
TEM.Payment_Num,
TEM.Due_Date) TEMP,
(SELECT /*+ index(prep,FII_AP_PREPAY_T_N1) */ PREP.Invoice_ID,
PREP.Payment_Num,
SUM(NVL(PREP.Prepay_Amount, 0)) Prepay_Amount
FROM FII_AP_Prepay_T PREP
WHERE PREP.Invoice_id BETWEEN g_start_range and g_end_range
AND (PREP.Due_Date + g_past_bucket3) + 1 BETWEEN g_start_date AND LEAST(g_end_date,g_sysdate)
AND PREP.Creation_Date < (PREP.Due_Date + g_past_bucket3) + 1
GROUP BY PREP.Invoice_ID,
PREP.Payment_Num,
PREP.Due_Date) PP,
(SELECT /*+ index(PC,FII_AP_PAY_CHK_STG_N1) */ PC.Invoice_ID,
PC.Payment_Num,
SUM(NVL(PC.Payment_Amount, 0)) Payment_Amount
FROM FII_AP_PAY_CHK_STG PC
WHERE PC.Invoice_id BETWEEN g_start_range and g_end_range
AND PC.Invoice_Type <> 'PREPAYMENT'
AND (PC.Due_Date + g_past_bucket3) + 1 BETWEEN g_start_date AND LEAST(g_end_date,g_sysdate)
AND PC.Invp_Creation_Date < (PC.Due_Date + g_past_bucket3) + 1
GROUP BY PC.Invoice_ID,
PC.Payment_Num,
PC.Due_Date) APC
WHERE PS.Invoice_ID = AI.Invoice_ID
AND AI.Invoice_ID BETWEEN g_start_range and g_end_range
AND AI.Invoice_Type NOT IN ('PREPAYMENT')
AND AI.Cancel_Date IS NULL
AND FRATES.To_Currency = AI.Base_Currency_Code
AND FRATES.From_Currency = AI.Payment_Currency_Code
AND FRATES.Trx_Date = AI.Exchange_Date
AND DECODE(AI.Exchange_Rate_Type,'User', AI.Exchange_Rate,1) =
DECODE(FRATES.Conversion_Type,'User', FRATES.Conversion_Rate,1)
AND FRATES.Conversion_Type = AI.Exchange_Rate_Type
AND RATES.Functional_Currency = AI.Base_Currency_Code
AND RATES.Trx_Date = AI.Invoice_Date
AND (TRUNC(PS.Due_Date) + g_past_bucket3 + 1) > AI.Entered_Date
AND (TRUNC(PS.Due_Date) + g_past_bucket3) + 1 BETWEEN g_start_date
AND LEAST(g_end_date,g_sysdate)
AND PS.Invoice_ID = TEMP.Invoice_ID(+)
AND PS.Payment_Num = TEMP.Payment_Num(+)
AND PS.Invoice_ID = PP.Invoice_ID(+)
AND PS.Payment_Num = PP.Payment_Num(+)
AND PS.Invoice_ID = APC.Invoice_ID(+)
AND PS.Payment_Num = APC.Payment_Num(+)
AND ABS(NVL(APC.Payment_Amount,0) + NVL(TEMP.WH_Tax_Amount,0)
+ NVL(PP.Prepay_Amount,0)) < ABS(PS.Gross_Amount);
FII_UTIL.put_line('Inserted ' ||SQL%ROWCOUNT|| ' Past Due Bucket2 records into FII_AP_PAY_SCHED_B');
g_state := 'Inserting the Payment Schedules Past Due Bucket1 Action';
/* Inserting the Past Due Bucket records into the summary table.
We will insert only those payment schedules which have not been paid
fully before the past due bucket2. This check is done by comparing
the gross amount with the payment, prepayment and withheld amount */
INSERT INTO FII_AP_PAY_SCHED_B b
(Time_ID,
Period_Type_ID,
Action_Date,
Action,
Update_Sequence,
Org_ID,
Supplier_ID,
Invoice_ID,
Base_Currency_Code,
Trx_Date,
Payment_Num,
Due_Date,
Created_By,
Amount_Remaining,
Past_Due_Amount,
Discount_Available,
Discount_Taken,
Discount_Lost,
Payment_Amount,
On_Time_Payment_Amt,
Late_Payment_Amt,
No_Days_Late,
Due_Bucket1,
Due_Bucket2,
Due_Bucket3,
Past_Due_Bucket1,
Past_Due_Bucket2,
Past_Due_Bucket3,
Amount_Remaining_B,
Past_Due_Amount_B,
Discount_Available_B,
Discount_Taken_B,
Discount_Lost_B,
Payment_Amount_B,
On_Time_Payment_Amt_B,
Late_Payment_Amt_B,
Due_Bucket1_B,
Due_Bucket2_B,
Due_Bucket3_B,
Past_Due_Bucket1_B,
Past_Due_Bucket2_B,
Past_Due_Bucket3_B,
Prim_Amount_Remaining,
Prim_Past_Due_Amount,
Prim_Discount_Available,
Prim_Discount_Taken,
Prim_Discount_Lost,
Prim_Payment_Amount,
Prim_On_time_Payment_Amt,
Prim_Late_Payment_Amt,
Prim_Due_Bucket1,
Prim_Due_Bucket2,
Prim_Due_Bucket3,
Prim_Past_Due_Bucket1,
Prim_Past_Due_Bucket2,
Prim_Past_Due_Bucket3,
Sec_Amount_Remaining,
Sec_Past_Due_Amount,
Sec_Discount_Available,
Sec_Discount_Taken,
Sec_Discount_Lost,
Sec_Payment_Amount,
Sec_On_time_Payment_Amt,
Sec_Late_Payment_Amt,
Sec_Due_Bucket1,
Sec_Due_Bucket2,
Sec_Due_Bucket3,
Sec_Past_Due_Bucket1,
Sec_Past_Due_Bucket2,
Sec_Past_Due_Bucket3,
Creation_Date,
Last_Updated_By,
Last_Update_Date,
Last_Update_Login)
SELECT /*+ leading(ai) index(AI,FII_AP_INVOICE_B_U1) use_nl(ps) */
TO_NUMBER(TO_CHAR(PS.Due_Date + g_past_bucket2 + 1,'J')) Time_ID,
1 Period_Type_ID,
TRUNC(PS.Due_Date) + g_past_bucket2 + 1 Action_Date,
'PAST BUCKET' Action,
g_seq_id Update_Sequence,
AI.Org_ID Org_ID,
AI.Supplier_ID Supplier_ID,
AI.Invoice_ID Invoice_ID,
AI.Base_Currency_Code Base_Currency_Code,
AI.Invoice_Date Trx_Date,
PS.Payment_Num Payment_Num,
TRUNC(PS.Due_Date) Due_Date,
PS.Created_By Created_By,
0 Amount_Remaining,
0 Past_Due_Amount,
0 Discount_Available,
0 Discount_Taken,
0 Discount_Lost,
0 Payment_Amount,
0 On_Time_Payment_Amt,
0 Late_Payment_Amt,
0 No_Days_Late,
0 Due_Bucket1,
0 Due_Bucket2,
0 Due_Bucket3,
PS.Gross_Amount - NVL(APC.Payment_Amount,0)
- NVL(TEMP.WH_Tax_Amount,0)
- NVL(PP.Prepay_Amount,0) Past_Due_Bucket1,
-1 * (PS.Gross_Amount - NVL(APC.Payment_Amount,0)
- NVL(TEMP.WH_Tax_Amount,0)
- NVL(PP.Prepay_Amount,0)) Past_Due_Bucket2,
0 Past_Due_Bucket3,
0 Amount_Remaining_B,
0 Past_Due_Amount_B,
0 Discount_Available_B,
0 Discount_Taken_B,
0 Discount_Lost_B,
0 Payment_Amount_B,
0 On_Time_Payment_Amt_B,
0 Late_Payment_Amt_B,
0 Due_Bucket1_B,
0 Due_Bucket2_B,
0 Due_Bucket3_B,
ROUND(((PS.Gross_Amount - NVL(APC.Payment_Amount,0)
- NVL(TEMP.WH_Tax_Amount,0)
- NVL(PP.Prepay_Amount,0)) * Conversion_Rate)
/ Functional_MAU) * Functional_MAU Past_Due_Bucket1_B,
ROUND((-1 * (PS.Gross_Amount - NVL(APC.Payment_Amount,0)
- NVL(TEMP.WH_Tax_Amount,0)
- NVL(PP.Prepay_Amount,0)) * Conversion_Rate)
/ Functional_MAU) * Functional_MAU Past_Due_Bucket2_B,
0 Past_Due_Bucket3_B,
0 Prim_Amount_Remaining,
0 Prim_Past_Due_Amount,
0 Prim_Discount_Available,
0 Prim_Discount_Taken,
0 Prim_Discount_Lost,
0 Prim_Payment_Amount,
0 Prim_On_Time_Payment_Amt,
0 Prim_Late_Payment_Amt,
0 Prim_Due_Bucket1,
0 Prim_Due_Bucket2,
0 Prim_Due_Bucket3,
ROUND(DECODE(AI.Invoice_Currency_Code, g_prim_currency,
PS.Gross_Amount - NVL(APC.Payment_Amount,0)
- NVL(TEMP.WH_Tax_Amount,0)
- NVL(PP.Prepay_Amount,0),
(((PS.Gross_Amount - NVL(APC.Payment_Amount,0)
- NVL(TEMP.WH_Tax_Amount,0)
- NVL(PP.Prepay_Amount,0)) * Conversion_Rate)
* RATES.Prim_Conversion_Rate)) / g_primary_mau) * g_primary_mau Prim_Past_Due_Bucket1,
ROUND(DECODE(AI.Invoice_Currency_Code, g_prim_currency,
-1 * (PS.Gross_Amount - NVL(APC.Payment_Amount,0)
- NVL(TEMP.WH_Tax_Amount,0)
- NVL(PP.Prepay_Amount,0)),
((-1 * (PS.Gross_Amount - NVL(APC.Payment_Amount,0)
- NVL(TEMP.WH_Tax_Amount,0)
- NVL(PP.Prepay_Amount,0)) * Conversion_Rate)
* RATES.Prim_Conversion_Rate)) / g_primary_mau) * g_primary_mau Prim_Past_Due_Bucket2,
0 Prim_Past_Due_Bucket3,
0 Sec_Amount_Remaining,
0 Sec_Past_Due_Amount,
0 Sec_Discount_Available,
0 Sec_Discount_Taken,
0 Sec_Discount_Lost,
0 Sec_Payment_Amount,
0 Sec_On_Time_Payment_Amt,
0 Sec_Late_Payment_Amt,
0 Sec_Due_Bucket1,
0 Sec_Due_Bucket2,
0 Sec_Due_Bucket3,
ROUND(DECODE(AI.Invoice_Currency_Code, g_sec_currency,
PS.Gross_Amount - NVL(APC.Payment_Amount,0)
- NVL(TEMP.WH_Tax_Amount,0)
- NVL(PP.Prepay_Amount,0),
(((PS.Gross_Amount - NVL(APC.Payment_Amount,0)
- NVL(TEMP.WH_Tax_Amount,0)
- NVL(PP.Prepay_Amount,0)) * Conversion_Rate)
* RATES.Sec_Conversion_Rate)) / g_secondary_mau) * g_secondary_mau Sec_Past_Due_Bucket1,
ROUND(DECODE(AI.Invoice_Currency_Code, g_sec_currency,
-1 * (PS.Gross_Amount - NVL(APC.Payment_Amount,0)
- NVL(TEMP.WH_Tax_Amount,0)
- NVL(PP.Prepay_Amount,0)),
((-1 * (PS.Gross_Amount - NVL(APC.Payment_Amount,0)
- NVL(TEMP.WH_Tax_Amount,0)
- NVL(PP.Prepay_Amount,0)) * Conversion_Rate)
* RATES.Sec_Conversion_Rate)) / g_secondary_mau) * g_secondary_mau Sec_Past_Due_Bucket2,
0 Sec_Past_Due_Bucket3,
sysdate Creation_Date,
g_fii_user_id Last_Updated_By,
sysdate Last_Update_Date,
g_fii_login_id Last_Update_Login
FROM FII_AP_Invoice_B AI,
FII_AP_PS_Rates_Temp RATES,
FII_AP_Func_Rates_Temp FRATES,
AP_Payment_Schedules_All PS,
(SELECT /*+ index(TEM,FII_AP_WH_TAX_T_N1) */ TEM.Invoice_ID,
TEM.Payment_Num,
SUM(NVL(TEM.WH_Tax_Amount, 0)) WH_Tax_Amount
FROM FII_AP_WH_Tax_T TEM
WHERE TEM.Invoice_id BETWEEN g_start_range and g_end_range
AND TEM.Invoice_Type <> 'PREPAYMENT'
AND (TEM.Due_Date + g_past_bucket2) + 1 BETWEEN g_start_date AND LEAST(g_end_date,g_sysdate)
AND TEM.Creation_Date < (TEM.Due_Date + g_past_bucket2) + 1
GROUP BY TEM.Invoice_ID,
TEM.Payment_Num,
TEM.Due_Date) TEMP,
(SELECT /*+ index(prep,FII_AP_PREPAY_T_N1) */ PREP.Invoice_ID,
PREP.Payment_Num,
SUM(NVL(PREP.Prepay_Amount, 0)) Prepay_Amount
FROM FII_AP_Prepay_T PREP
WHERE PREP.Invoice_id BETWEEN g_start_range and g_end_range
AND (PREP.Due_Date + g_past_bucket2) + 1 BETWEEN g_start_date AND LEAST(g_end_date,g_sysdate)
AND PREP.Creation_Date < (PREP.Due_Date + g_past_bucket2) + 1
GROUP BY PREP.Invoice_ID,
PREP.Payment_Num,
PREP.Due_Date) PP,
(SELECT /*+ index(PC,FII_AP_PAY_CHK_STG_N1) */ PC.Invoice_ID,
PC.Payment_Num,
SUM(NVL(PC.Payment_Amount, 0)) Payment_Amount
FROM FII_AP_PAY_CHK_STG PC
WHERE PC.Invoice_id BETWEEN g_start_range and g_end_range
AND PC.Invoice_Type <> 'PREPAYMENT'
AND (PC.Due_Date + g_past_bucket2) + 1 BETWEEN g_start_date AND LEAST(g_end_date,g_sysdate)
AND PC.Invp_Creation_Date < (PC.Due_Date + g_past_bucket2) + 1
GROUP BY PC.Invoice_ID,
PC.Payment_Num,
PC.Due_Date) APC
WHERE PS.Invoice_ID = AI.Invoice_ID
AND AI.Invoice_ID BETWEEN g_start_range and g_end_range
AND AI.Invoice_Type NOT IN ('PREPAYMENT')
AND AI.Cancel_Date IS NULL
AND FRATES.To_Currency = AI.Base_Currency_Code
AND FRATES.From_Currency = AI.Payment_Currency_Code
AND FRATES.Trx_Date = AI.Exchange_Date
AND DECODE(AI.Exchange_Rate_Type,'User', AI.Exchange_Rate,1) =
DECODE(FRATES.Conversion_Type,'User', FRATES.Conversion_Rate,1)
AND FRATES.Conversion_Type = AI.Exchange_Rate_Type
AND RATES.Functional_Currency = AI.Base_Currency_Code
AND RATES.Trx_Date = AI.Invoice_Date
AND (TRUNC(PS.Due_Date) + g_past_bucket2 + 1) > AI.Entered_Date
AND (TRUNC(PS.Due_Date) + g_past_bucket2) + 1 BETWEEN g_start_date
AND LEAST(g_end_date,g_sysdate)
AND PS.Invoice_ID = TEMP.Invoice_ID(+)
AND PS.Payment_Num = TEMP.Payment_Num(+)
AND PS.Invoice_ID = PP.Invoice_ID(+)
AND PS.Payment_Num = PP.Payment_Num(+)
AND PS.Invoice_ID = APC.Invoice_ID(+)
AND PS.Payment_Num = APC.Payment_Num(+)
AND ABS(NVL(APC.Payment_Amount,0) + NVL(TEMP.WH_Tax_Amount,0)
+ NVL(PP.Prepay_Amount,0)) < ABS(PS.Gross_Amount);
FII_UTIL.put_line('Inserted ' ||SQL%ROWCOUNT|| ' Past Due Bucket1 records into FII_AP_PAY_SCHED_B');
SELECT fii_ap_pay_sched_b_s.nextval
INTO g_seq_id
FROM dual;
INSERT INTO FII_AP_PAY_SCHED_B b
(Time_ID,
Period_Type_ID,
Action_Date,
Action,
Update_Sequence,
Org_ID,
Supplier_ID,
Invoice_ID,
Base_Currency_Code,
Trx_Date,
Payment_Num,
Due_Date,
Created_By,
Amount_Remaining,
Past_Due_Amount,
Discount_Available,
Discount_Taken,
Discount_Lost,
Payment_Amount,
On_Time_Payment_Amt,
Late_Payment_Amt,
No_Days_Late,
Due_Bucket1,
Due_Bucket2,
Due_Bucket3,
Past_Due_Bucket1,
Past_Due_Bucket2,
Past_Due_Bucket3,
Amount_Remaining_B,
Past_Due_Amount_B,
Discount_Available_B,
Discount_Taken_B,
Discount_Lost_B,
Payment_Amount_B,
On_Time_Payment_Amt_B,
Late_Payment_Amt_B,
Due_Bucket1_B,
Due_Bucket2_B,
Due_Bucket3_B,
Past_Due_Bucket1_B,
Past_Due_Bucket2_B,
Past_Due_Bucket3_B,
Prim_Amount_Remaining,
Prim_Past_Due_Amount,
Prim_Discount_Available,
Prim_Discount_Taken,
Prim_Discount_Lost,
Prim_Payment_Amount,
Prim_On_time_Payment_Amt,
Prim_Late_Payment_Amt,
Prim_Due_Bucket1,
Prim_Due_Bucket2,
Prim_Due_Bucket3,
Prim_Past_Due_Bucket1,
Prim_Past_Due_Bucket2,
Prim_Past_Due_Bucket3,
Sec_Amount_Remaining,
Sec_Past_Due_Amount,
Sec_Discount_Available,
Sec_Discount_Taken,
Sec_Discount_Lost,
Sec_Payment_Amount,
Sec_On_time_Payment_Amt,
Sec_Late_Payment_Amt,
Sec_Due_Bucket1,
Sec_Due_Bucket2,
Sec_Due_Bucket3,
Sec_Past_Due_Bucket1,
Sec_Past_Due_Bucket2,
Sec_Past_Due_Bucket3,
Creation_Date,
Last_Updated_By,
Last_Update_Date,
Last_Update_Login)
SELECT /*+ no_merge ordered index(PS,FII_AP_INVOICE_B_U1) index(RATES) use_nl(AI) */
TO_NUMBER(TO_CHAR(Action_Date,'J')) Time_ID,
1 Period_Type_ID,
Action_Date,
'CREATION' Action,
g_seq_id Update_Sequence,
Org_ID,
Supplier_ID,
Invoice_ID,
Base_Currency_Code,
Invoice_Date,
Payment_Num,
Due_Date,
Created_By,
Amount_Remaining,
Past_Due_Amount,
Discount_Available,
Discount_Taken,
Discount_Lost,
Payment_Amount,
On_Time_Payment_Amt,
Late_Payment_Amt,
No_Days_Late,
Due_Bucket1,
Due_Bucket2,
Due_Bucket3,
Past_Due_Bucket1,
Past_Due_Bucket2,
Past_Due_Bucket3,
ROUND((Amount_Remaining * Conversion_Rate) / Functional_MAU) * Functional_MAU Amount_Remaining_B,
ROUND((Past_Due_Amount * Conversion_Rate) / Functional_MAU) * Functional_MAU Past_Due_Amount_B,
ROUND((Discount_Available * Conversion_Rate) / Functional_MAU) * Functional_MAU Discount_Available_B,
ROUND((Discount_Taken * Conversion_Rate) / Functional_MAU) * Functional_MAU Discount_Taken_B,
ROUND((Discount_Lost * Conversion_Rate) / Functional_MAU) * Functional_MAU Discount_Lost_B,
ROUND((Payment_Amount * Conversion_Rate) / Functional_MAU) * Functional_MAU Payment_Amount_B,
ROUND((On_Time_Payment_Amt * Conversion_Rate) / Functional_MAU) * Functional_MAU On_Time_Payment_Amt_B,
ROUND((Late_Payment_Amt * Conversion_Rate) / Functional_MAU) * Functional_MAU Late_Payment_Amt_B,
ROUND((Due_Bucket1 * Conversion_Rate) / Functional_MAU) * Functional_MAU Due_Bucket1_B,
ROUND((Due_Bucket2 * Conversion_Rate) / Functional_MAU) * Functional_MAU Due_Bucket2_B,
ROUND((Due_Bucket3 * Conversion_Rate) / Functional_MAU) * Functional_MAU Due_Bucket3_B,
ROUND((Past_Due_Bucket1 * Conversion_Rate) / Functional_MAU) * Functional_MAU Past_Due_Bucket1_B,
ROUND((Past_Due_Bucket2 * Conversion_Rate) / Functional_MAU) * Functional_MAU Past_Due_Bucket2_B,
ROUND((Past_Due_Bucket3 * Conversion_Rate) / Functional_MAU) * Functional_MAU Past_Due_Bucket3_B,
ROUND(DECODE(Invoice_Currency_Code, g_prim_currency, Amount_Remaining,
((Amount_Remaining * Conversion_Rate) * RATES.Prim_Conversion_Rate))
/ g_primary_mau) * g_primary_mau Prim_Amount_Remaining,
ROUND(DECODE(Invoice_Currency_Code, g_prim_currency, Past_Due_Amount,
((Past_Due_Amount * Conversion_Rate) * RATES.Prim_Conversion_Rate))
/ g_primary_mau) * g_primary_mau Prim_Past_Due_Amount,
ROUND(DECODE(Invoice_Currency_Code, g_prim_currency, Discount_Available,
((Discount_Available * Conversion_Rate) * RATES.Prim_Conversion_Rate))
/ g_primary_mau) * g_primary_mau Prim_Discount_Available,
ROUND(DECODE(Invoice_Currency_Code, g_prim_currency, Discount_Taken,
((Discount_Taken * Conversion_Rate) * RATES.Prim_Conversion_Rate))
/ g_primary_mau) * g_primary_mau Prim_Discount_Taken,
ROUND(DECODE(Invoice_Currency_Code, g_prim_currency, Discount_Lost,
((Discount_Lost * Conversion_Rate) * RATES.Prim_Conversion_Rate))
/ g_primary_mau) * g_primary_mau Prim_Discount_Lost,
ROUND(DECODE(Invoice_Currency_Code, g_prim_currency, Payment_Amount,
((Payment_Amount * Conversion_Rate) * RATES.Prim_Conversion_Rate))
/ g_primary_mau) * g_primary_mau Prim_Payment_Amount,
ROUND(DECODE(Invoice_Currency_Code, g_prim_currency, On_Time_Payment_Amt,
((On_Time_Payment_Amt * Conversion_Rate) * RATES.Prim_Conversion_Rate))
/ g_primary_mau) * g_primary_mau Prim_On_Time_Payment_Amt,
ROUND(DECODE(Invoice_Currency_Code, g_prim_currency, Late_Payment_Amt,
((Late_Payment_Amt * Conversion_Rate) * RATES.Prim_Conversion_Rate))
/ g_primary_mau) * g_primary_mau Prim_Late_Payment_Amt,
ROUND(DECODE(Invoice_Currency_Code, g_prim_currency, Due_Bucket1,
((Due_Bucket1 * Conversion_Rate) * RATES.Prim_Conversion_Rate))
/ g_primary_mau) * g_primary_mau Prim_Due_Bucket1,
ROUND(DECODE(Invoice_Currency_Code, g_prim_currency, Due_Bucket2,
((Due_Bucket2 * Conversion_Rate) * RATES.Prim_Conversion_Rate))
/ g_primary_mau) * g_primary_mau Prim_Due_Bucket2,
ROUND(DECODE(Invoice_Currency_Code, g_prim_currency, Due_Bucket3,
((Due_Bucket3 * Conversion_Rate) * RATES.Prim_Conversion_Rate))
/ g_primary_mau) * g_primary_mau Prim_Due_Bucket3,
ROUND(DECODE(Invoice_Currency_Code, g_prim_currency, Past_Due_Bucket1,
((Past_Due_Bucket1 * Conversion_Rate) * RATES.Prim_Conversion_Rate))
/ g_primary_mau) * g_primary_mau Prim_Past_Due_Bucket1,
ROUND(DECODE(Invoice_Currency_Code, g_prim_currency, Past_Due_Bucket2,
((Past_Due_Bucket2 * Conversion_Rate) * RATES.Prim_Conversion_Rate))
/ g_primary_mau) * g_primary_mau Prim_Past_Due_Bucket2,
ROUND(DECODE(Invoice_Currency_Code, g_prim_currency, Past_Due_Bucket3,
((Past_Due_Bucket3 * Conversion_Rate) * RATES.Prim_Conversion_Rate))
/ g_primary_mau) * g_primary_mau Prim_Past_Due_Bucket3,
ROUND(DECODE(Invoice_Currency_Code, g_sec_currency, Amount_Remaining,
((Amount_Remaining * Conversion_Rate) * RATES.Sec_Conversion_Rate))
/ g_secondary_mau) * g_secondary_mau Sec_Amount_Remaining,
ROUND(DECODE(Invoice_Currency_Code, g_sec_currency, Past_Due_Amount,
((Past_Due_Amount * Conversion_Rate) * RATES.Sec_Conversion_Rate))
/ g_secondary_mau) * g_secondary_mau Sec_Past_Due_Amount,
ROUND(DECODE(Invoice_Currency_Code, g_sec_currency, Discount_Available,
((Discount_Available * Conversion_Rate) * RATES.Sec_Conversion_Rate))
/ g_secondary_mau) * g_secondary_mau Sec_Discount_Available,
ROUND(DECODE(Invoice_Currency_Code, g_sec_currency, Discount_Taken,
((Discount_Taken * Conversion_Rate) * RATES.Sec_Conversion_Rate))
/ g_secondary_mau) * g_secondary_mau Sec_Discount_Taken,
ROUND(DECODE(Invoice_Currency_Code, g_sec_currency, Discount_Lost,
((Discount_Lost * Conversion_Rate) * RATES.Sec_Conversion_Rate))
/ g_secondary_mau) * g_secondary_mau Sec_Discount_Lost,
ROUND(DECODE(Invoice_Currency_Code, g_sec_currency, Payment_Amount,
((Payment_Amount * Conversion_Rate) * RATES.Sec_Conversion_Rate))
/ g_secondary_mau) * g_secondary_mau Sec_Payment_Amount,
ROUND(DECODE(Invoice_Currency_Code, g_sec_currency, On_Time_Payment_Amt,
((On_Time_Payment_Amt * Conversion_Rate) * RATES.Sec_Conversion_Rate))
/ g_secondary_mau) * g_secondary_mau Sec_On_Time_Payment_Amt,
ROUND(DECODE(Invoice_Currency_Code, g_sec_currency, Late_Payment_Amt,
((Late_Payment_Amt * Conversion_Rate) * RATES.Sec_Conversion_Rate))
/ g_secondary_mau) * g_secondary_mau Sec_Late_Payment_Amt,
ROUND(DECODE(Invoice_Currency_Code, g_sec_currency, Due_Bucket1,
((Due_Bucket1 * Conversion_Rate) * RATES.Sec_Conversion_Rate))
/ g_secondary_mau) * g_secondary_mau Sec_Due_Bucket1,
ROUND(DECODE(Invoice_Currency_Code, g_sec_currency, Due_Bucket2,
((Due_Bucket2 * Conversion_Rate) * RATES.Sec_Conversion_Rate))
/ g_secondary_mau) * g_secondary_mau Sec_Due_Bucket2,
ROUND(DECODE(Invoice_Currency_Code, g_sec_currency, Due_Bucket3,
((Due_Bucket3 * Conversion_Rate) * RATES.Sec_Conversion_Rate))
/ g_secondary_mau) * g_secondary_mau Sec_Due_Bucket3,
ROUND(DECODE(Invoice_Currency_Code, g_sec_currency, Past_Due_Bucket1,
((Past_Due_Bucket1 * Conversion_Rate) * RATES.Sec_Conversion_Rate))
/ g_secondary_mau) * g_secondary_mau Sec_Past_Due_Bucket1,
ROUND(DECODE(Invoice_Currency_Code, g_sec_currency, Past_Due_Bucket2,
((Past_Due_Bucket2 * Conversion_Rate) * RATES.Sec_Conversion_Rate))
/ g_secondary_mau) * g_secondary_mau Sec_Past_Due_Bucket2,
ROUND(DECODE(Invoice_Currency_Code, g_sec_currency, Past_Due_Bucket3,
((Past_Due_Bucket3 * Conversion_Rate) * RATES.Sec_Conversion_Rate))
/ g_secondary_mau) * g_secondary_mau Sec_Past_Due_Bucket3,
sysdate Creation_Date,
g_fii_user_id Last_Updated_By,
sysdate Last_Update_Date,
g_fii_login_id Last_Update_Login
FROM
(SELECT AI.Entered_Date Action_Date,
AI.Org_Id Org_ID,
AI.Supplier_ID Supplier_ID,
AI.Invoice_Id Invoice_ID,
AI.Base_Currency_Code Base_Currency_Code,
AI.Invoice_Date Invoice_Date,
AI.Invoice_Currency_Code Invoice_Currency_Code,
AI.Payment_Currency_Code Payment_Currency_Code,
AI.Exchange_Rate Exchange_Rate,
AI.Exchange_Date Exchange_Date,
AI.Exchange_Rate_Type Exchange_Rate_Type,
PS.Payment_Num Payment_Num,
TRUNC(PS.Due_Date) Due_Date,
PS.Created_By Created_By,
PS.Gross_Amount Amount_Remaining,
DECODE(SIGN(TRUNC(PS.Due_Date) - AI.Entered_Date), -1,
PS.Gross_Amount, 0) Past_Due_Amount,
NVL(PS.Discount_Amount_Available,0) Discount_Available,
0 Discount_Taken,
0 Discount_Lost,
0 Payment_Amount,
0 On_Time_Payment_Amt,
0 Late_Payment_Amt,
0 No_Days_Late,
CASE
WHEN (TRUNC(PS.Due_Date) - AI.Entered_Date) >= g_due_bucket1
THEN PS.Gross_Amount
ELSE 0
END Due_Bucket1,
CASE
WHEN (TRUNC(PS.Due_Date) - AI.Entered_Date) <= g_due_bucket2
AND (TRUNC(PS.Due_Date) - AI.Entered_Date) > g_due_bucket3
THEN PS.Gross_Amount
ELSE 0
END Due_Bucket2,
CASE
WHEN (TRUNC(PS.Due_Date) - AI.Entered_Date) <= g_due_bucket3
AND (TRUNC(PS.Due_Date) - AI.Entered_Date) >= 0
THEN PS.Gross_Amount
ELSE 0
END Due_Bucket3,
CASE
WHEN (AI.Entered_Date - TRUNC(PS.Due_Date)) >= g_past_bucket1
THEN PS.Gross_Amount
ELSE 0
END Past_Due_Bucket1,
CASE
WHEN (AI.Entered_Date - TRUNC(PS.Due_Date)) <= g_past_bucket2
AND (AI.Entered_Date - TRUNC(PS.Due_Date)) > g_past_bucket3
THEN PS.Gross_Amount
ELSE 0
END Past_Due_Bucket2,
CASE
WHEN (AI.Entered_Date - TRUNC(PS.Due_Date)) <= g_past_bucket3
AND (AI.Entered_Date - TRUNC(PS.Due_Date)) > 0
THEN PS.Gross_Amount
ELSE 0
END Past_Due_Bucket3
FROM AP_Payment_Schedules_All PS,
FII_AP_Invoice_B AI
WHERE PS.Invoice_ID = AI.Invoice_ID
AND AI.Invoice_ID BETWEEN g_start_range and g_end_range
AND AI.Invoice_Type NOT IN ('PREPAYMENT')
AND AI.Cancel_Date IS NULL) PSUM,
FII_AP_PS_Rates_Temp RATES,
FII_AP_Func_Rates_Temp FRATES
WHERE FRATES.To_Currency = PSUM.Base_Currency_Code
AND FRATES.From_Currency = PSUM.Payment_Currency_Code
AND FRATES.Trx_Date = PSUM.Exchange_Date
AND DECODE(PSUM.Exchange_Rate_Type,'User', PSUM.Exchange_Rate,1) =
DECODE(FRATES.Conversion_Type,'User', FRATES.Conversion_Rate,1)
AND FRATES.Conversion_Type = PSUM.Exchange_Rate_Type
AND RATES.Functional_Currency = PSUM.Base_Currency_Code
AND RATES.Trx_Date = PSUM.Invoice_Date;
FII_UTIL.put_line('Inserted ' ||SQL%ROWCOUNT|| ' Creation records into FII_AP_PAY_SCHED_B');
INSERT /*+ append parallel(fii_ap_aging_bkts_b) parallel(fii_ap_due_counts_b) */ ALL
WHEN ( Due_Bucket1_Cnt <> 0
OR Due_Bucket2_Cnt <> 0
OR Due_Bucket3_Cnt <> 0
OR Past_Due_Bucket1_Cnt <> 0
OR Past_Due_Bucket2_Cnt <> 0
OR Past_Due_Bucket3_Cnt <> 0)
THEN INTO FII_AP_AGING_BKTS_B(
Time_ID,
Period_Type_ID,
Org_ID,
Supplier_ID,
Invoice_ID,
Action_Date,
Due_Bucket1_Cnt,
Due_Bucket2_Cnt,
due_bucket3_Cnt,
Past_Due_Bucket3_Cnt,
Past_Due_Bucket2_Cnt,
Past_Due_Bucket1_Cnt,
Created_By,
Creation_Date,
Last_Updated_By,
Last_Update_Date,
Last_Update_Login)
VALUES (
TO_NUMBER(TO_CHAR(Action_Date, 'J')),
1,
Org_ID,
Supplier_ID,
Invoice_ID,
Action_Date,
Due_Bucket1_Cnt,
Due_Bucket2_Cnt,
Due_Bucket3_Cnt,
Past_Due_Bucket3_Cnt,
Past_Due_Bucket2_Cnt,
Past_Due_Bucket1_Cnt,
g_fii_user_id,
sysdate,
g_fii_user_id,
sysdate,
g_fii_login_id)
WHEN ( Due_Cnt <> 0
OR Past_Due_Cnt <> 0)
THEN INTO FII_AP_DUE_COUNTS_B (
Time_ID,
Period_Type_ID,
Org_ID,
Supplier_ID,
Invoice_ID,
Action_Date,
Due_Cnt,
Past_Due_Cnt,
Created_By,
Creation_Date,
Last_Updated_By,
Last_Update_Date,
Last_Update_Login)
VALUES (
TO_NUMBER(TO_CHAR(Action_Date, 'J')),
1,
Org_ID,
Supplier_ID,
Invoice_ID,
Action_Date,
Due_Cnt,
Past_Due_Cnt,
g_fii_user_id,
sysdate,
g_fii_user_id,
sysdate,
g_fii_login_id)
SELECT Org_ID,
Supplier_ID,
Invoice_ID,
SDate Action_Date,
CASE WHEN SB1 > 0
AND NVL(lag(SB1) OVER (PARTITION BY Org_ID, Supplier_ID, Invoice_ID
ORDER BY SDate), 0) = 0
THEN 1
WHEN SB1 = 0
AND NVL(lag(SB1) OVER (PARTITION BY Org_ID, Supplier_ID, Invoice_ID
ORDER BY SDate), 0) > 0 then -1
ELSE 0
END Due_Bucket1_Cnt,
CASE WHEN SB2 > 0
AND NVL(lag(SB2) OVER (PARTITION BY Org_ID, Supplier_ID, Invoice_ID
ORDER BY SDate), 0) = 0
THEN 1
WHEN SB2 = 0
AND NVL(lag(SB2) OVER (PARTITION BY Org_ID, Supplier_ID, Invoice_ID
ORDER BY SDate), 0) > 0
THEN -1
ELSE 0
END Due_Bucket2_Cnt,
CASE WHEN SB3 > 0
AND NVL(lag(SB3) OVER (PARTITION BY Org_ID, Supplier_ID, Invoice_ID
ORDER BY SDate), 0) = 0
THEN 1
WHEN SB3 = 0
AND NVL(lag(SB3) OVER (PARTITION BY Org_ID, Supplier_ID, Invoice_ID
ORDER BY SDate), 0) > 0
THEN -1
ELSE 0
END Due_Bucket3_Cnt,
CASE WHEN SB4 > 0
AND NVL(lag(SB4) OVER (PARTITION BY Org_ID, Supplier_ID, Invoice_ID
ORDER BY SDate), 0) = 0
THEN 1
WHEN SB4 = 0
AND NVL(lag(SB4) OVER(PARTITION BY Org_ID, Supplier_ID, Invoice_ID
ORDER BY SDate), 0) > 0
THEN -1
ELSE 0
END Past_Due_Bucket3_Cnt,
CASE WHEN SB5 > 0
AND NVL(lag(SB5) OVER (PARTITION BY Org_ID, Supplier_ID, Invoice_ID
ORDER BY SDate), 0) = 0
THEN 1
WHEN SB5 = 0
AND NVL(lag(SB5) OVER (PARTITION BY Org_ID, Supplier_ID, Invoice_ID
ORDER BY SDate), 0) > 0
THEN -1
ELSE 0
END Past_Due_Bucket2_Cnt,
CASE WHEN SB6 > 0
AND NVL(lag(SB6) OVER (PARTITION BY Org_ID, Supplier_ID, Invoice_ID
ORDER BY SDate), 0) = 0
THEN 1
WHEN SB6 = 0
AND NVL(lag(SB6) OVER (PARTITION BY Org_ID, Supplier_ID, Invoice_ID
ORDER BY SDate), 0) > 0
THEN -1
ELSE 0
END Past_Due_Bucket1_Cnt,
CASE WHEN D > 0
AND NVL(lag(D) OVER (PARTITION BY Org_ID, Supplier_ID, Invoice_ID
ORDER BY SDate), 0) = 0
THEN 1
WHEN D = 0
AND NVL(lag(D) OVER (PARTITION BY Org_ID, Supplier_ID, Invoice_ID
ORDER BY SDate), 0) > 0
THEN -1
ELSE 0
END Due_Cnt,
CASE WHEN PD > 0
AND nvl(lag(PD) OVER (PARTITION BY Org_ID, Supplier_ID, Invoice_ID
ORDER BY SDate), 0) = 0
THEN 1
WHEN PD = 0
AND NVL(lag(PD) OVER (PARTITION BY Org_ID, Supplier_ID, Invoice_ID
ORDER BY SDate), 0) > 0
THEN -1
ELSE 0
END Past_Due_Cnt
FROM (SELECT Org_ID,
Supplier_ID,
Invoice_ID,
SDate,
SUM(SUM(B1)) OVER (PARTITION BY Org_ID, Supplier_ID, Invoice_ID
ORDER BY SDate
ROWS UNBOUNDED PRECEDING) SB1,
SUM(SUM(B2)) OVER (PARTITION BY Org_ID, Supplier_ID, Invoice_ID
ORDER BY SDate
ROWS UNBOUNDED PRECEDING) SB2,
SUM(SUM(B3)) OVER (PARTITION BY Org_ID, Supplier_ID, Invoice_ID
ORDER BY SDate
ROWS UNBOUNDED PRECEDING) SB3,
SUM(SUM(B4)) OVER (PARTITION BY Org_ID, Supplier_ID, Invoice_ID
ORDER BY SDate
ROWS UNBOUNDED PRECEDING) SB4,
SUM(SUM(B5)) OVER (PARTITION BY Org_ID, Supplier_ID, Invoice_ID
ORDER BY SDate
ROWS UNBOUNDED PRECEDING) SB5,
SUM(SUM(B6)) OVER (PARTITION BY Org_ID, Supplier_ID, Invoice_ID
ORDER BY SDate
ROWS UNBOUNDED PRECEDING) SB6,
SUM(SUM(B1+B2+B3)) OVER (PARTITION BY Org_ID, Supplier_ID, Invoice_ID
ORDER BY SDate
ROWS UNBOUNDED PRECEDING) D,
SUM(SUM(B4+B5+B6)) OVER (PARTITION BY Org_ID, Supplier_ID, Invoice_ID
ORDER BY SDate
ROWS UNBOUNDED PRECEDING) PD
FROM (
SELECT Org_ID,
Supplier_ID,
Invoice_ID,
Payment_Num,
CASE TMP.Marker
WHEN 1 THEN CASE WHEN Creation_Date < Due_Date-g_due_bucket2 THEN Creation_Date END
WHEN 2 THEN CASE WHEN Creation_Date < Due_Date-g_due_bucket2
AND nvl(Fully_Paid_Date, Due_Date-g_due_bucket2) <= trunc(sysdate)
THEN least(nvl(Fully_Paid_Date, Due_Date-g_due_bucket2), Due_Date-g_due_bucket2) END
WHEN 3 THEN CASE WHEN Creation_Date < Due_Date-g_due_bucket3
AND nvl(Fully_Paid_Date, Due_Date-g_due_bucket2) <= trunc(sysdate)
AND nvl(Fully_Paid_Date, Due_Date-g_due_bucket3) >= Due_Date-g_due_bucket2
THEN greatest(Creation_Date, Due_Date-g_due_bucket2) END
WHEN 4 THEN CASE WHEN Creation_Date < Due_Date-g_due_bucket3
AND nvl(Fully_Paid_Date, Due_Date-g_due_bucket3) <= trunc(sysdate)
AND nvl(Fully_Paid_Date, Due_Date-g_due_bucket3) >= Due_Date-g_due_bucket2
THEN least(nvl(Fully_Paid_Date, Due_Date-g_due_bucket3), Due_Date-g_due_bucket3) END
WHEN 5 THEN CASE WHEN Creation_Date < Due_Date+1
AND nvl(Fully_Paid_Date, Due_Date-g_due_bucket3) <= trunc(sysdate)
AND nvl(Fully_Paid_Date, Due_Date) >= Due_Date-g_due_bucket3
THEN greatest(Creation_Date, Due_Date-g_due_bucket3) END
WHEN 6 THEN CASE WHEN Creation_Date < Due_Date+1
AND nvl(Fully_Paid_Date, Due_Date+1) <= trunc(sysdate)
AND nvl(Fully_Paid_Date, Due_Date) >= Due_Date-g_due_bucket3
THEN least(nvl(Fully_Paid_Date, Due_Date+1), Due_Date+1) END
WHEN 7 THEN CASE WHEN Creation_Date <= Due_Date+g_past_bucket3
AND nvl(Fully_Paid_Date, Due_Date+1) <= trunc(sysdate)
AND nvl(Fully_Paid_Date, Due_Date+g_past_bucket3) >= Due_Date+1
THEN greatest(Creation_Date, Due_Date+1) END
WHEN 8 THEN CASE WHEN Creation_Date <= Due_Date+g_past_bucket3
AND nvl(Fully_Paid_Date, Due_Date+g_past_bucket3+1) <= trunc(sysdate)
AND nvl(Fully_Paid_Date, Due_Date+g_past_bucket3) >= Due_Date+1
THEN least(nvl(Fully_Paid_Date, Due_Date+g_past_bucket3+1), Due_Date+g_past_bucket3+1) END
WHEN 9 THEN CASE WHEN Creation_Date <= Due_Date+g_past_bucket2
AND nvl(Fully_Paid_Date, Due_Date+g_past_bucket3+1) <= trunc(sysdate)
AND nvl(Fully_Paid_Date, Due_Date+g_past_bucket2) >= Due_Date+g_past_bucket3+1
THEN greatest(Creation_Date, Due_Date+g_past_bucket3+1) END
WHEN 10 THEN CASE WHEN Creation_Date <= Due_Date+g_past_bucket2
AND nvl(Fully_Paid_Date, Due_Date+g_past_bucket1) <= trunc(sysdate)
AND nvl(Fully_Paid_Date, Due_Date+g_past_bucket2) >= Due_Date+g_past_bucket3+1
THEN least(nvl(Fully_Paid_Date, Due_Date+g_past_bucket1), Due_Date+g_past_bucket1) END
WHEN 11 THEN CASE WHEN nvl(Fully_Paid_Date, Due_Date+g_past_bucket1) >= Due_Date+g_past_bucket1
AND nvl(Fully_Paid_Date, Due_Date+g_past_bucket1) <= trunc(sysdate)
THEN greatest(Creation_Date, Due_Date+g_past_bucket1) END
WHEN 12 THEN CASE WHEN nvl(Fully_Paid_Date, Due_Date+g_past_bucket1) >= Due_Date+g_past_bucket1
THEN Fully_Paid_Date END
END SDate,
decode(TMP.Marker, 1, 1, 2, -1, 0) b1,
decode(TMP.Marker, 3, 1, 4, -1, 0) b2,
decode(TMP.Marker, 5, 1, 6, -1, 0) b3,
decode(TMP.Marker, 7, 1, 8, -1, 0) b4,
decode(TMP.Marker, 9, 1, 10, -1, 0) b5,
decode(TMP.Marker, 11, 1, 12, -1, 0) b6
FROM (SELECT /*+ parallel(ps) parallel(ai) */
AI.Org_ID,
AI.Supplier_ID,
PS.Invoice_ID,
PS.Payment_Num,
AI.Entered_Date Creation_Date,
trunc(PS.Due_Date) Due_Date,
CASE WHEN nvl(PAY.Payment_Amount, 0) + nvl(TEMP.WH_Tax_Amount, 0)
+ nvl(PP.Prepay_Amount, 0) = PS.Gross_Amount
THEN CASE WHEN PAY.Payment_Date is not null
AND PP.Payment_Date is not null
THEN greatest(PAY.Payment_Date, PP.Payment_Date)
ELSE nvl(PAY.Payment_Date, PP.Payment_Date) END
ELSE null END Fully_Paid_Date
FROM AP_Payment_Schedules_All PS,
FII_AP_INVOICE_B AI,
(SELECT /*+ parallel(aip) */ AIP.Invoice_ID,
AIP.Payment_Num,
sum(AIP.Amount + nvl(AIP.Discount_Taken, 0)) Payment_Amount,
trunc(max(AIP.Creation_Date)) Payment_Date
FROM AP_Invoice_Payments_ALL AIP
GROUP BY AIP.Invoice_id, AIP.Payment_Num) PAY,
(SELECT /*+ parallel(t) */ Invoice_ID,
Payment_Num,
sum(WH_Tax_Amount) WH_Tax_Amount
FROM FII_AP_WH_Tax_T t
GROUP BY Invoice_ID, Payment_Num) TEMP,
(SELECT /*+ parallel(p) */ Invoice_ID,
Payment_Num,
sum(Prepay_Amount) Prepay_Amount,
trunc(max(Creation_Date)) Payment_Date
FROM FII_AP_Prepay_T p
GROUP BY Invoice_ID, Payment_Num) PP
WHERE AI.Invoice_ID = PS.Invoice_ID
AND AI.Invoice_Type NOT IN ('PREPAYMENT')
AND AI.Cancel_Date IS NULL
AND PS.Invoice_ID = PAY.Invoice_ID (+)
AND PS.Payment_num = PAY.Payment_Num (+)
AND PS.Invoice_ID = TEMP.Invoice_ID (+)
AND PS.Payment_Num = TEMP.Payment_Num (+)
AND PS.Invoice_ID = PP.Invoice_ID (+)
AND PS.Payment_Num = PP.Payment_Num (+)) Bucket_Calcs,
(SELECT 1 marker FROM DUAL UNION ALL
SELECT 2 marker FROM DUAL UNION ALL
SELECT 3 marker FROM DUAL UNION ALL
SELECT 4 marker FROM DUAL UNION ALL
SELECT 5 marker FROM DUAL UNION ALL
SELECT 6 marker FROM DUAL UNION ALL
SELECT 7 marker FROM DUAL UNION ALL
SELECT 8 marker FROM DUAL UNION ALL
SELECT 9 marker FROM DUAL UNION ALL
SELECT 10 marker FROM DUAL UNION ALL
SELECT 11 marker FROM DUAL UNION ALL
SELECT 12 marker FROM DUAL) TMP)
WHERE SDate IS NOT NULL
GROUP BY Org_ID, Supplier_ID, Invoice_ID, SDate)
COMMIT;
FII_UTIL.put_line('Inserted '|| SQL%ROWCOUNT ||' records into FII_AP_AGING_BKTS_B and FII_AP_DUE_COUNTS_B');
/* Only insert a 'H' record for the first hold in a series of overlapping
holds. The first part of the union does this by checking if this hold is
the first ever for the invoice or if all holds made before this hold have
been released before the hold was made.
Only insert a 'R' record for the last release in a series of overlapping
holds, if all holds have been released. The second part of the union does
this by checking if this hold is the very last to be released or if all holds
released after it were held after this hold was released.
*/
INSERT /*+ append parallel(HH) */ 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 /*+ use_hash(AI,AH) parallel(AI) parallel(AH) */
DISTINCT AI.ORG_ID,
AI.SUPPLIER_ID,
AI.INVOICE_ID,
AH.REC_TYPE,
TRUNC(AH.H_R_DATE) H_R_DATE
FROM FII_AP_INVOICE_B AI,
(SELECT /*+ no_merge */
Invoice_ID,
TRUNC(Hold_Date) H_R_DATE,
'H' Rec_Type
FROM (SELECT /*+ parallel(h) */
Invoice_ID,
Hold_Date,
MAX(Release_Date) OVER (PARTITION BY Invoice_ID
ORDER BY Hold_Date ASC
ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) Max_Previous
FROM (SELECT /*+ parallel(h) no_merge full(h) */
Invoice_ID,
Hold_Date,
DECODE(Release_Lookup_Code, NULL, g_sysdate, last_update_date) Release_Date
FROM AP_Holds_All h
WHERE Hold_Date >= g_start_date
AND Hold_Date+0 <= g_end_date --workaround for pq-between bug
) h
)
WHERE (Max_Previous IS NULL OR TRUNC(Hold_Date) > TRUNC(Max_Previous))
UNION ALL
SELECT Invoice_ID,
TRUNC(Release_Date) H_R_DATE,
'R' Rec_Type
FROM (SELECT /*+ parallel(h) */
Invoice_ID,
Release_Date,
Release_Lookup_Code,
MIN(Hold_Date) OVER (PARTITION BY Invoice_ID
ORDER BY Release_Date ASC
ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING) Min_After
FROM (SELECT /*+ parallel(h) no_merge full(h) */
Invoice_ID,
Hold_Date,
DECODE(Release_Lookup_Code, NULL, g_sysdate, Last_Update_Date) Release_Date,
Release_Lookup_Code
FROM AP_Holds_All h
WHERE Hold_Date >= g_start_date
AND Hold_Date+0 <= g_end_date -- workaround for pq-between bug
) h
)
WHERE (Min_After IS NULL OR TRUNC(Min_After) > TRUNC(Release_Date))
AND Release_Lookup_Code IS NOT NULL) AH --Filter release records for unreleased holds
WHERE AI.Invoice_ID = AH.Invoice_ID
AND AI.Cancel_Date IS NULL
AND AI.Invoice_Type NOT IN ('PREPAYMENT'));
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.Action_Date IN
(SELECT MIN(TRUNC(AH1.Hold_Date))
FROM AP_Holds_ALL AH1, AP_Holds_ALL AH2
WHERE AH1.Invoice_ID = HH.Invoice_ID
AND AH2.Invoice_ID = HH.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.Seq_ID IS NULL;
FII_UTIL.put_line('Updated '|| SQL%ROWCOUNT ||' Release records in the FII_AP_HOLD_HIST_B');
so it is safe to remove this update.
g_state := 'Updating the Hold Count on the Hold and Release records';
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 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 HH2.Rowid <> HH.Rowid)))
WHERE HH.Hold_Count IS NULL;
FII_UTIL.put_line('Updated '|| SQL%ROWCOUNT ||' Hold Counts in the FII_AP_HOLD_HIST_B');
SELECT nvl(sum(decode(status,'UNASSIGNED',1,0)),0),
nvl(sum(decode(status,'FAILED',1,0)),0),
nvl(sum(decode(status,'UNASSIGNED',1, 0)),0),
nvl(sum(decode(status,'COMPLETED', 1, 0)),0),
count(*)
INTO l_unassigned_cnt,
l_failed_cnt,
l_curr_unasgn_cnt,
l_curr_comp_cnt,
l_curr_tot_cnt
FROM FII_AP_PS_WORK_JOBS;
SELECT COUNT(1)
INTO l_unassigned_cnt
FROM FII_AP_PS_WORK_JOBS
WHERE status = 'UNASSIGNED'
AND rownum = 1;
SELECT COUNT(1)
INTO l_failed_cnt
FROM FII_AP_PS_WORK_JOBS
WHERE status = 'FAILED'
AND rownum = 1;
UPDATE FII_AP_PS_WORK_JOBS
SET status = 'IN PROCESS',
worker_number = p_worker_no
WHERE status = 'UNASSIGNED'
AND rownum < 2;
SELECT start_range,
end_range
INTO l_start_range,
l_end_range
FROM FII_AP_PS_WORK_JOBS jobs
WHERE jobs.worker_number = p_worker_no
AND jobs.status = 'IN PROCESS';
UPDATE FII_AP_PS_WORK_JOBS jobs
SET jobs.status = 'COMPLETED'
WHERE jobs.status = 'IN PROCESS'
AND jobs.worker_number = p_worker_no;
UPDATE FII_AP_PS_WORK_JOBS
SET status = 'FAILED'
WHERE worker_number = p_worker_no
AND status = 'IN PROCESS';
UPDATE FII_AP_PS_WORK_JOBS
SET status = 'FAILED'
WHERE worker_number = p_worker_no
AND status = 'IN PROCESS';
FII_UTIL.put_line('Calling the Insert_Rates procedure to insert the missing rate info');
INSERT_RATES;
INSERT_WH_PREPAY_AMOUNT;
INSERT_PAYMENT_CHECK_INFO;
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;
BIS_COLLECTION_UTILITIES.deleteLogForObject('FII_AP_INV_SUM_INC');