[Home] [Help]
258: end if;
259:
260: SELECT max(invoice_ID), min(invoice_ID), COUNT(*)
261: INTO l_max_number, l_start_number, l_inv_count
262: FROM FII_AP_INVOICE_B;
263:
264: IF l_inv_count < (g_no_worker * 20000) THEN
265: l_job_size := round(l_inv_count/g_no_worker);
266: ELSE
271: LOOP
272:
273: SELECT MAX(INVOICE_ID) INTO l_end_number
274: FROM (SELECT invoice_id
275: FROM fii_ap_invoice_b
276: WHERE invoice_id >= l_start_number
277: ORDER BY invoice_id)
278: WHERE rownum < l_job_size;
279:
548: ROUND(PS.Gross_Amount *
549: SUM(AID.Amount)/AI.Invoice_Amount
550: / FC.Minimum_Accountable_Unit) * FC.Minimum_Accountable_Unit))
551: WH_Tax_Amount
552: FROM FII_AP_Invoice_B AI,
553: AP_Invoice_Distributions_All AID,
554: AP_Invoice_Lines_All AIL,
555: AP_Payment_Schedules_All PS,
556: FND_Currencies FC
687: TRUNC(PS.Second_Discount_Date) Second_Discount_Date,
688: TRUNC(PS.Third_Discount_Date) Third_Discount_Date,
689: TRUNC(AI.Entered_Date) Entered_Date,
690: PS.Gross_Amount - PS.Amount_Remaining - NVL(PAY.Payment_Amount,0) - NVL(TEMP.WH_Tax_Amount, 0) PP_Amount
691: FROM FII_AP_INVOICE_B AI,
692: AP_PAYMENT_SCHEDULES_ALL PS,
693: (SELECT /*+ parallel(AI) parallel(AIP) parallel(PS) */
694: AIP.Invoice_ID Invoice_ID,
695: AIP.Payment_Num Payment_Num,
694: AIP.Invoice_ID Invoice_ID,
695: AIP.Payment_Num Payment_Num,
696: SUM(AIP.Amount + NVL(AIP.Discount_Taken, 0)) Payment_Amount
697: FROM AP_Invoice_Payments_All AIP,
698: FII_AP_Invoice_B AI,
699: AP_Payment_Schedules_All PS
700: WHERE PS.Invoice_ID = AI.Invoice_ID
701: AND AIP.Invoice_ID = AI.Invoice_ID
702: AND AI.Invoice_Type NOT IN ('PREPAYMENT')
790: TRUNC(PS.Second_Discount_Date) Second_Discount_Date,
791: TRUNC(PS.Third_Discount_Date) Third_Discount_Date,
792: TRUNC(AI.Entered_Date) Entered_Date,
793: TRUNC(AIP.Creation_Date) Invp_Creation_Date
794: FROM FII_AP_INVOICE_B AI,
795: AP_Checks_All AC,
796: AP_Invoice_Payments_All AIP,
797: AP_Payment_Schedules_All PS
798: WHERE AI.Invoice_ID = PS.Invoice_ID
1168: g_fii_user_id Last_Updated_By,
1169: sysdate Last_Update_Date,
1170: g_fii_login_id Last_Update_Login
1171: FROM AP_Holds_All AH,
1172: FII_AP_Invoice_B AI
1173: WHERE AH.Invoice_ID = AI.Invoice_ID
1174: AND AI.Cancel_Date IS NULL
1175: AND AI.Invoice_Type NOT IN ('PREPAYMENT')
1176: AND TRUNC(AH.Hold_Date) >= g_start_date
1201: ------------------------------------------------------------------
1202: -- Procedure POPULATE_INV_BASE_SUM
1203: -- Purpose
1204: -- This POPULATE_INV_BASE_SUM routine inserts records into the
1205: -- FII AP Invoice Base summary table.
1206: ------------------------------------------------------------------
1207:
1208: PROCEDURE POPULATE_INV_BASE_SUM IS
1209:
1210: l_stmt VARCHAR2(20000);
1211:
1212: BEGIN
1213:
1214: g_state := 'Deleting records from FII_AP_INVOICE_B that are already existing';
1215: /* For Initial Load we will truncate the data in the invoice base summary table
1216: and re-populate this table */
1217: TRUNCATE_TABLE('MLOG$_FII_AP_INVOICE_B');
1218: TRUNCATE_TABLE('FII_AP_INVOICE_B');
1213:
1214: g_state := 'Deleting records from FII_AP_INVOICE_B that are already existing';
1215: /* For Initial Load we will truncate the data in the invoice base summary table
1216: and re-populate this table */
1217: TRUNCATE_TABLE('MLOG$_FII_AP_INVOICE_B');
1218: TRUNCATE_TABLE('FII_AP_INVOICE_B');
1219:
1220: g_state := 'Populating FII_AP_INVOICE_B FROM AP_INVOICES_ALL table';
1221: if g_debug_flag = 'Y' then
1214: g_state := 'Deleting records from FII_AP_INVOICE_B that are already existing';
1215: /* For Initial Load we will truncate the data in the invoice base summary table
1216: and re-populate this table */
1217: TRUNCATE_TABLE('MLOG$_FII_AP_INVOICE_B');
1218: TRUNCATE_TABLE('FII_AP_INVOICE_B');
1219:
1220: g_state := 'Populating FII_AP_INVOICE_B FROM AP_INVOICES_ALL table';
1221: if g_debug_flag = 'Y' then
1222: FII_UTIL.put_line(g_state);
1216: and re-populate this table */
1217: TRUNCATE_TABLE('MLOG$_FII_AP_INVOICE_B');
1218: TRUNCATE_TABLE('FII_AP_INVOICE_B');
1219:
1220: g_state := 'Populating FII_AP_INVOICE_B FROM AP_INVOICES_ALL table';
1221: if g_debug_flag = 'Y' then
1222: FII_UTIL.put_line(g_state);
1223: FII_UTIL.start_timer;
1224: FII_UTIL.put_line('');
1242: table. We will insert the maximum due_date, maximum discount offered
1243: and the first hold date in this table. */
1244:
1245:
1246: INSERT /*+ append parallel(S) */ INTO FII_AP_INVOICE_B S
1247: (Org_ID,
1248: Supplier_ID,
1249: Invoice_ID,
1250: Invoice_Type,
1456: AND IB.Base_Currency_Code = FRATES.To_Currency;
1457:
1458:
1459: if g_debug_flag = 'Y' then
1460: FII_UTIL.put_line('Inserted ' ||SQL%ROWCOUNT|| ' records into FII_AP_INVOICE_B');
1461: FII_UTIL.stop_timer;
1462: FII_UTIL.print_timer('Duration');
1463: FII_UTIL.put_line('');
1464: end if;
1464: end if;
1465:
1466: COMMIT;
1467:
1468: FND_STATS.GATHER_TABLE_STATS(g_fii_schema,'FII_AP_INVOICE_B');
1469: EXECUTE IMMEDIATE 'ALTER SESSION ENABLE PARALLEL DML';
1470:
1471:
1472: EXCEPTION
1811: AC.Check_Date,
1812: DECODE(IBY_SYS_PROF_B.Processing_Type,NULL,DECODE(AC.Payment_Method_Lookup_Code, 'EFT', 'E', 'WIRE', 'E', 'M')
1813: ,DECODE(IBY_SYS_PROF_B.Processing_Type, 'ELECTRONIC', 'E', 'M')) PAYMENT_METHOD,
1814: AIP.Invoice_Payment_ID
1815: FROM FII_AP_Invoice_B AI,
1816: AP_Payment_Schedules_All PS,
1817: (SELECT /*+ leading(aip_pp) use_nl(aip_pp,pp) */ AIP_PP.Invoice_ID,
1818: AIP_PP.Payment_Num,
1819: AIP_PP.Creation_Date,
1980: Check_Date,
1981: Payment_Method,
1982: Inv_Pymt_Flag,
1983: Unique_ID)
1984: SELECT /*+ ordered use_nl(PS) index(ai, FII_AP_INVOICE_B_U1 )
1985: use_nl(ai) use_nl(apc)*/
1986: TO_NUMBER(TO_CHAR(AIP.Creation_Date,'J')) Time_ID,
1987: 1 Period_Type_ID,
1988: TRUNC(AIP.Creation_Date) Action_Date,
2075: AND PSUM.Period_Type_ID (+) = 1
2076: GROUP BY PS.Invoice_ID,
2077: PS.Payment_Num) DISC,
2078: AP_Payment_Schedules_All PS,
2079: FII_AP_Invoice_B AI,
2080: (SELECT /*+ merge(aip_pp) use_nl(aip_pp, pp) */
2081: AIP_PP.Invoice_ID,
2082: AIP_PP.Payment_Num,
2083: AIP_PP.Creation_Date,
2410: sysdate Last_Update_Date,
2411: g_fii_login_id Last_Update_Login,
2412: TEMP.Inv_Pymt_Flag,
2413: TEMP.Unique_ID
2414: FROM FII_AP_Invoice_B AI,
2415: FII_AP_Pay_Sched_Temp TEMP,
2416: FII_AP_PS_Rates_Temp RATES,
2417: FII_AP_Func_Rates_Temp FRATES
2418: WHERE TEMP.Invoice_ID = AI.Invoice_ID
2800: sysdate Creation_Date,
2801: g_fii_user_id Last_Updated_By,
2802: sysdate Last_Update_Date,
2803: g_fii_login_id Last_Update_Login
2804: FROM FII_AP_Invoice_B AI,
2805: AP_Invoice_Distributions_All AID,
2806: AP_Invoice_Lines_All AIL,
2807: AP_Payment_Schedules_All PS,
2808: FII_AP_PS_Rates_Temp RATES,
3225: sysdate Last_Update_Date,
3226: g_fii_login_id Last_Update_Login,
3227: 'N' Inv_Pymt_Flag,
3228: AC.Check_ID Unique_ID
3229: FROM FII_AP_INVOICE_B AI,
3230: AP_Payment_Schedules_All PS,
3231: (SELECT /*+ use_nl(aps3,pp) */APS3.Invoice_ID,
3232: APS3.Payment_Num,
3233: APS3.Creation_Date,
3443: Last_Updated_By,
3444: Last_Update_Date,
3445: Last_Update_Login,
3446: Unique_ID)
3447: SELECT /*+ leading(ai) index(AI,FII_AP_INVOICE_B_U1) index(FRATES) use_nl(ps) */
3448: TO_NUMBER(TO_CHAR(PS.Discount_Date + 1,'J')) Time_ID,
3449: 1 Period_Type_ID,
3450: TRUNC(PS.Discount_Date) + 1 Action_Date,
3451: 'DISCOUNT' Action,
3545: g_fii_user_id Last_Updated_By,
3546: sysdate Last_Update_Date,
3547: g_fii_login_id Last_Update_Login,
3548: 1 Unique_ID
3549: FROM FII_AP_Invoice_B AI,
3550: FII_AP_PS_Rates_Temp RATES,
3551: FII_AP_Func_Rates_Temp FRATES,
3552: AP_Payment_Schedules_All PS,
3553: (SELECT /*+ index(TEM,FII_AP_WH_TAX_T_N1) */ TEM.Invoice_ID,
3706: Last_Updated_By,
3707: Last_Update_Date,
3708: Last_Update_Login,
3709: Unique_ID)
3710: SELECT /*+ leading(ai) index(AI,FII_AP_INVOICE_B_U1) index(FRATES) use_nl(ps) */
3711: TO_NUMBER(TO_CHAR(PS.Second_Discount_Date + 1,'J')) Time_ID,
3712: 1 Period_Type_ID,
3713: TRUNC(PS.Second_Discount_Date) + 1 Action_Date,
3714: 'DISCOUNT' Action,
3808: g_fii_user_id Last_Updated_By,
3809: sysdate Last_Update_Date,
3810: g_fii_login_id Last_Update_Login,
3811: 2 Unique_ID
3812: FROM FII_AP_Invoice_B AI,
3813: FII_AP_PS_Rates_Temp RATES,
3814: FII_AP_Func_Rates_Temp FRATES,
3815: AP_Payment_Schedules_All PS,
3816: (SELECT /*+ index(TEM,FII_AP_WH_TAX_T_N1) */ TEM.Invoice_ID,
3980: Last_Updated_By,
3981: Last_Update_Date,
3982: Last_Update_Login,
3983: Unique_ID)
3984: SELECT /*+ leading(ai) index(AI,FII_AP_INVOICE_B_U1) index(FRATES) use_nl(ps) */
3985: TO_NUMBER(TO_CHAR(PS.Third_Discount_Date + 1,'J')) Time_ID,
3986: 1 Period_Type_ID,
3987: TRUNC(PS.Third_Discount_Date) + 1 Action_Date,
3988: 'DISCOUNT' Action,
4072: g_fii_user_id Last_Updated_By,
4073: sysdate Last_Update_Date,
4074: g_fii_login_id Last_Update_Login,
4075: 3 Unique_ID
4076: FROM FII_AP_Invoice_B AI,
4077: FII_AP_PS_Rates_Temp RATES,
4078: FII_AP_Func_Rates_Temp FRATES,
4079: AP_Payment_Schedules_All PS,
4080: (SELECT /*+ index(TEM,FII_AP_WH_TAX_T_N1) */ TEM.Invoice_ID,
4273: Creation_Date,
4274: Last_Updated_By,
4275: Last_Update_Date,
4276: Last_Update_Login)
4277: SELECT /*+ leading(ai) index(AI,FII_AP_INVOICE_B_U1) use_nl(ps) */
4278: TO_NUMBER(TO_CHAR(PS.Due_Date + 1,'J')) Time_ID,
4279: 1 Period_Type_ID,
4280: TRUNC(PS.Due_Date) + 1 Action_Date,
4281: 'DUE' Action,
4405: sysdate Creation_Date,
4406: g_fii_user_id Last_Updated_By,
4407: sysdate Last_Update_Date,
4408: g_fii_login_id Last_Update_Login
4409: FROM FII_AP_Invoice_B AI,
4410: FII_AP_PS_Rates_Temp RATES,
4411: FII_AP_Func_Rates_Temp FRATES,
4412: AP_Payment_Schedules_All PS,
4413: (SELECT /*+ index(TEM,FII_AP_WH_TAX_T_N1) */ TEM.Invoice_ID Invoice_ID,
4597: Creation_Date,
4598: Last_Updated_By,
4599: Last_Update_Date,
4600: Last_Update_Login)
4601: SELECT /*+ leading(ai) index(AI,FII_AP_INVOICE_B_U1) use_nl(ps) */
4602: TO_NUMBER(TO_CHAR((PS.Due_Date - g_due_bucket2),'J')) Time_ID,
4603: 1 Period_Type_ID,
4604: (TRUNC(PS.Due_Date) - g_due_bucket2) Action_Date,
4605: 'DUE BUCKET' Action,
4710: sysdate Creation_Date,
4711: g_fii_user_id Last_Updated_By,
4712: sysdate Last_Update_Date,
4713: g_fii_login_id Last_Update_Login
4714: FROM FII_AP_Invoice_B AI,
4715: FII_AP_PS_Rates_Temp RATES,
4716: FII_AP_Func_Rates_Temp FRATES,
4717: AP_Payment_Schedules_All PS,
4718: (SELECT /*+ index(TEM,FII_AP_WH_TAX_T_N1) */ TEM.Invoice_ID,
4872: Creation_Date,
4873: Last_Updated_By,
4874: Last_Update_Date,
4875: Last_Update_Login)
4876: SELECT /*+ leading(ai) index(AI,FII_AP_INVOICE_B_U1) use_nl(ps) */
4877: TO_NUMBER(TO_CHAR((PS.Due_Date - g_due_bucket3),'J')) Time_ID,
4878: 1 Period_Type_ID,
4879: (TRUNC(PS.Due_Date) - g_due_bucket3) Action_Date,
4880: 'DUE BUCKET',
4985: sysdate Creation_Date,
4986: g_fii_user_id Last_Updated_By,
4987: sysdate Last_Update_Date,
4988: g_fii_login_id Last_Update_Login
4989: FROM FII_AP_Invoice_B AI,
4990: FII_AP_PS_Rates_Temp RATES,
4991: FII_AP_Func_Rates_Temp FRATES,
4992: AP_Payment_Schedules_All PS,
4993: (SELECT /*+ index(TEM,FII_AP_WH_TAX_T_N1) */ TEM.Invoice_ID,
5174: Creation_Date,
5175: Last_Updated_By,
5176: Last_Update_Date,
5177: Last_Update_Login)
5178: SELECT /*+ leading(ai) index(AI,FII_AP_INVOICE_B_U1) use_nl(ps) */
5179: TO_NUMBER(TO_CHAR(PS.Due_Date + g_past_bucket3 + 1,'J')) Time_ID,
5180: 1 Period_Type_ID,
5181: TRUNC(PS.Due_Date) + g_past_bucket3 + 1 Action_Date,
5182: 'PAST BUCKET' Action,
5287: sysdate Creation_Date,
5288: g_fii_user_id Last_Updated_By,
5289: sysdate Last_Update_Date,
5290: g_fii_login_id Last_Update_Login
5291: FROM FII_AP_Invoice_B AI,
5292: FII_AP_PS_Rates_Temp RATES,
5293: FII_AP_Func_Rates_Temp FRATES,
5294: AP_Payment_Schedules_All PS,
5295: (SELECT /*+ index(TEM,FII_AP_WH_TAX_T_N1) */ TEM.Invoice_ID,
5447: Creation_Date,
5448: Last_Updated_By,
5449: Last_Update_Date,
5450: Last_Update_Login)
5451: SELECT /*+ leading(ai) index(AI,FII_AP_INVOICE_B_U1) use_nl(ps) */
5452: TO_NUMBER(TO_CHAR(PS.Due_Date + g_past_bucket2 + 1,'J')) Time_ID,
5453: 1 Period_Type_ID,
5454: TRUNC(PS.Due_Date) + g_past_bucket2 + 1 Action_Date,
5455: 'PAST BUCKET' Action,
5560: sysdate Creation_Date,
5561: g_fii_user_id Last_Updated_By,
5562: sysdate Last_Update_Date,
5563: g_fii_login_id Last_Update_Login
5564: FROM FII_AP_Invoice_B AI,
5565: FII_AP_PS_Rates_Temp RATES,
5566: FII_AP_Func_Rates_Temp FRATES,
5567: AP_Payment_Schedules_All PS,
5568: (SELECT /*+ index(TEM,FII_AP_WH_TAX_T_N1) */ TEM.Invoice_ID,
5647: ------------------------------------------------------------------
5648: -- Procedure POPULATE_PAY_SCHED_SUM
5649: -- Purpose
5650: -- This POPULATE_PAY_SCHED_SUM routine inserts records into the
5651: -- FII AP Invoice Base summary table.
5652: ------------------------------------------------------------------
5653:
5654: PROCEDURE POPULATE_PAY_SCHED_SUM(
5655: P_Start_Range IN NUMBER,
5764: Creation_Date,
5765: Last_Updated_By,
5766: Last_Update_Date,
5767: Last_Update_Login)
5768: SELECT /*+ no_merge ordered index(PS,FII_AP_INVOICE_B_U1) index(RATES) use_nl(AI) */
5769: TO_NUMBER(TO_CHAR(Action_Date,'J')) Time_ID,
5770: 1 Period_Type_ID,
5771: Action_Date,
5772: 'CREATION' Action,
5955: THEN PS.Gross_Amount
5956: ELSE 0
5957: END Past_Due_Bucket3
5958: FROM AP_Payment_Schedules_All PS,
5959: FII_AP_Invoice_B AI
5960: WHERE PS.Invoice_ID = AI.Invoice_ID
5961: AND AI.Invoice_ID BETWEEN g_start_range and g_end_range
5962: AND AI.Invoice_Type NOT IN ('PREPAYMENT')
5963: AND AI.Cancel_Date IS NULL) PSUM,
6387: THEN greatest(PAY.Payment_Date, PP.Payment_Date)
6388: ELSE nvl(PAY.Payment_Date, PP.Payment_Date) END
6389: ELSE null END Fully_Paid_Date
6390: FROM AP_Payment_Schedules_All PS,
6391: FII_AP_INVOICE_B AI,
6392: (SELECT /*+ parallel(aip) */ AIP.Invoice_ID,
6393: AIP.Payment_Num,
6394: sum(AIP.Amount + nvl(AIP.Discount_Taken, 0)) Payment_Amount,
6395: trunc(max(AIP.Creation_Date)) Payment_Date
6544: AI.SUPPLIER_ID,
6545: AI.INVOICE_ID,
6546: AH.REC_TYPE,
6547: TRUNC(AH.H_R_DATE) H_R_DATE
6548: FROM FII_AP_INVOICE_B AI,
6549: (SELECT /*+ no_merge */
6550: Invoice_ID,
6551: TRUNC(Hold_Date) H_R_DATE,
6552: 'H' Rec_Type