[Home] [Help]
589: COMMIT;
590:
591:
592:
593: g_state := 'Inserting records into the FII_AP_Prepay_T table';
594: if g_debug_flag = 'Y' then
595: FII_UTIL.put_line('');
596: FII_UTIL.put_line(g_state);
597: fii_util.start_timer;
597: fii_util.start_timer;
598: fii_util.put_line('');
599: end if;
600:
601: INSERT /*+ append parallel(T) */ INTO fii_ap_prepay_t T
602: (Invoice_ID,
603: Payment_Num,
604: Creation_Date,
605: Due_Date,
721: (AID_Prepay.First + 1 <= PS_Prepay.First_PP + 1 AND PS_Prepay.First_PP + 1 <= AID_Prepay.Last));
722:
723:
724: if g_debug_flag = 'Y' then
725: fii_util.put_line('Inserted '||SQL%ROWCOUNT||' records into FII_AP_Prepay_T');
726: fii_util.stop_timer;
727: fii_util.print_timer('Duration');
728: end if;
729:
727: fii_util.print_timer('Duration');
728: end if;
729:
730:
731: FND_STATS.GATHER_TABLE_STATS(OWNNAME => 'FII', TABNAME => 'FII_AP_PREPAY_T');
732: EXECUTE IMMEDIATE 'ALTER SESSION ENABLE PARALLEL DML';
733:
734: COMMIT;
735:
1849: AIP_WH.Amount,
1850: AIP_WH.Discount_Taken,
1851: AIP_WH.Check_ID,
1852: AIP_WH.Invoice_Payment_ID) AIP_PP,
1853: FII_AP_Prepay_T PP
1854: WHERE AIP_PP.Invoice_ID = PP.Invoice_ID (+)
1855: AND AIP_PP.Payment_Num = PP.Payment_Num (+)
1856: GROUP BY AIP_PP.Invoice_ID,
1857: AIP_PP.Payment_Num,
2113: AIP_WH.Amount,
2114: AIP_WH.Discount_Taken,
2115: AIP_WH.Check_ID,
2116: AIP_WH.Invoice_Payment_ID) AIP_PP,
2117: FII_AP_Prepay_T PP
2118: WHERE AIP_PP.Invoice_ID = PP.Invoice_ID (+)
2119: AND AIP_PP.Payment_Num = PP.Payment_Num (+)
2120: GROUP BY AIP_PP.Invoice_ID,
2121: AIP_PP.Payment_Num,
3253: APS1.Check_ID,
3254: NVL(SUM(CASE WHEN APC.Invp_Creation_Date IS NOT NULL
3255: AND APC.Invp_Creation_Date <= APS1.Creation_Date
3256: THEN APC.Payment_Amount ELSE 0 END),0) Pay_Amount
3257: FROM FII_AP_Prepay_T APS1,
3258: FII_AP_Pay_Chk_Stg APC
3259: WHERE APS1.Invoice_ID BETWEEN g_start_range and g_end_range
3260: AND APS1.Invoice_ID = APC.Invoice_ID (+)
3261: AND APS1.Payment_Num = APC.Payment_Num (+)
3272: APS2.Creation_Date,
3273: APS2.Prepay_Amount,
3274: APS2.Check_ID,
3275: APS2.Pay_Amount) APS3,
3276: FII_AP_Prepay_T PP
3277: WHERE APS3.Invoice_ID = PP.Invoice_ID (+)
3278: AND APS3.Payment_Num = PP.Payment_Num (+)
3279: Group By APS3.Invoice_ID,
3280: APS3.Payment_Num,
3560: AND TEM.Creation_Date < TRUNC(TEM.Discount_Date) + 1
3561: GROUP BY TEM.Invoice_ID,
3562: TEM.Payment_Num,
3563: TEM.Discount_Date) TEMP,
3564: (SELECT /*+ index(prep,FII_AP_PREPAY_T_N1) */ PREP.Invoice_ID,
3565: PREP.Payment_Num,
3566: SUM(NVL(PREP.Prepay_Amount, 0)) Prepay_Amount
3567: FROM FII_AP_Prepay_T PREP
3568: WHERE PREP.Invoice_id BETWEEN g_start_range and g_end_range
3563: TEM.Discount_Date) TEMP,
3564: (SELECT /*+ index(prep,FII_AP_PREPAY_T_N1) */ PREP.Invoice_ID,
3565: PREP.Payment_Num,
3566: SUM(NVL(PREP.Prepay_Amount, 0)) Prepay_Amount
3567: FROM FII_AP_Prepay_T PREP
3568: WHERE PREP.Invoice_id BETWEEN g_start_range and g_end_range
3569: AND TRUNC(PREP.Discount_Date) + 1 BETWEEN g_start_date AND LEAST(g_end_date,g_sysdate)
3570: AND PREP.Creation_Date < TRUNC(PREP.Discount_Date) + 1
3571: GROUP BY PREP.Invoice_ID,
3823: AND TEM.Creation_Date < TEM.Second_Discount_Date + 1
3824: GROUP BY TEM.Invoice_ID,
3825: TEM.Payment_Num,
3826: TEM.Second_Discount_Date) TEMP,
3827: (SELECT /*+ index(prep,FII_AP_PREPAY_T_N1) */ PREP.Invoice_ID,
3828: PREP.Payment_Num,
3829: SUM(NVL(PREP.Prepay_Amount, 0)) Prepay_Amount
3830: FROM FII_AP_Prepay_T PREP
3831: WHERE PREP.Invoice_id BETWEEN g_start_range and g_end_range
3826: TEM.Second_Discount_Date) TEMP,
3827: (SELECT /*+ index(prep,FII_AP_PREPAY_T_N1) */ PREP.Invoice_ID,
3828: PREP.Payment_Num,
3829: SUM(NVL(PREP.Prepay_Amount, 0)) Prepay_Amount
3830: FROM FII_AP_Prepay_T PREP
3831: WHERE PREP.Invoice_id BETWEEN g_start_range and g_end_range
3832: AND PREP.Second_Discount_Date + 1 BETWEEN g_start_date AND LEAST(g_end_date,g_sysdate)
3833: AND PREP.Creation_Date < PREP.Second_Discount_Date + 1
3834: GROUP BY PREP.Invoice_ID,
4087: AND TEM.Creation_Date < TEM.Third_Discount_Date + 1
4088: GROUP BY TEM.Invoice_ID,
4089: TEM.Payment_Num,
4090: TEM.Third_Discount_Date) TEMP,
4091: (SELECT /*+ index(prep,FII_AP_PREPAY_T_N1) */ PREP.Invoice_ID,
4092: PREP.Payment_Num,
4093: SUM(NVL(PREP.Prepay_Amount, 0)) Prepay_Amount
4094: FROM FII_AP_Prepay_T PREP
4095: WHERE PREP.Invoice_id BETWEEN g_start_range and g_end_range
4090: TEM.Third_Discount_Date) TEMP,
4091: (SELECT /*+ index(prep,FII_AP_PREPAY_T_N1) */ PREP.Invoice_ID,
4092: PREP.Payment_Num,
4093: SUM(NVL(PREP.Prepay_Amount, 0)) Prepay_Amount
4094: FROM FII_AP_Prepay_T PREP
4095: WHERE PREP.Invoice_id BETWEEN g_start_range and g_end_range
4096: AND PREP.Third_Discount_Date + 1 BETWEEN g_start_date AND LEAST(g_end_date,g_sysdate)
4097: AND PREP.Creation_Date < PREP.Third_Discount_Date + 1
4098: GROUP BY PREP.Invoice_ID,
4421: AND TRUNC(TEM.Creation_Date) < TRUNC(TEM.Due_Date) + 1
4422: GROUP BY TEM.Invoice_ID,
4423: TEM.Payment_Num,
4424: TEM.Due_Date) TEMP,
4425: (SELECT /*+ index(prep,FII_AP_PREPAY_T_N1) */ PREP.Invoice_ID Invoice_ID,
4426: PREP.Payment_Num Payment_Num,
4427: SUM(NVL(PREP.Prepay_Amount, 0)) Prepay_Amount
4428: FROM FII_AP_Prepay_T PREP
4429: WHERE PREP.Invoice_ID BETWEEN g_start_range and g_end_range
4424: TEM.Due_Date) TEMP,
4425: (SELECT /*+ index(prep,FII_AP_PREPAY_T_N1) */ PREP.Invoice_ID Invoice_ID,
4426: PREP.Payment_Num Payment_Num,
4427: SUM(NVL(PREP.Prepay_Amount, 0)) Prepay_Amount
4428: FROM FII_AP_Prepay_T PREP
4429: WHERE PREP.Invoice_ID BETWEEN g_start_range and g_end_range
4430: AND PREP.Due_Date + 1 BETWEEN g_start_date AND LEAST(g_end_date, g_sysdate)
4431: AND PREP.Due_Date >= PREP.Entered_Date
4432: AND TRUNC(PREP.Creation_Date) < TRUNC(PREP.Due_Date) + 1
4725: AND TEM.Creation_Date < (TEM.Due_Date - g_due_bucket2)
4726: GROUP BY TEM.Invoice_ID,
4727: TEM.Payment_Num,
4728: TEM.Due_Date) TEMP,
4729: (SELECT /*+ index(prep,FII_AP_PREPAY_T_N1) */ PREP.Invoice_ID,
4730: PREP.Payment_Num,
4731: SUM(NVL(PREP.Prepay_Amount, 0)) Prepay_Amount
4732: FROM FII_AP_Prepay_T PREP
4733: WHERE PREP.Invoice_id BETWEEN g_start_range and g_end_range
4728: TEM.Due_Date) TEMP,
4729: (SELECT /*+ index(prep,FII_AP_PREPAY_T_N1) */ PREP.Invoice_ID,
4730: PREP.Payment_Num,
4731: SUM(NVL(PREP.Prepay_Amount, 0)) Prepay_Amount
4732: FROM FII_AP_Prepay_T PREP
4733: WHERE PREP.Invoice_id BETWEEN g_start_range and g_end_range
4734: AND (PREP.Due_Date - g_due_bucket2) BETWEEN g_start_date AND LEAST(g_end_date,g_sysdate)
4735: AND PREP.Creation_Date < (PREP.Due_Date - g_due_bucket2)
4736: GROUP BY PREP.Invoice_ID,
5000: AND TEM.Creation_Date < (TEM.Due_Date - g_due_bucket3)
5001: GROUP BY TEM.Invoice_ID,
5002: TEM.Payment_Num,
5003: TEM.Due_Date) TEMP,
5004: (SELECT /*+ index(prep,FII_AP_PREPAY_T_N1) */ PREP.Invoice_ID,
5005: PREP.Payment_Num,
5006: SUM(NVL(PREP.Prepay_Amount, 0)) Prepay_Amount
5007: FROM FII_AP_Prepay_T PREP
5008: WHERE PREP.Invoice_id BETWEEN g_start_range and g_end_range
5003: TEM.Due_Date) TEMP,
5004: (SELECT /*+ index(prep,FII_AP_PREPAY_T_N1) */ PREP.Invoice_ID,
5005: PREP.Payment_Num,
5006: SUM(NVL(PREP.Prepay_Amount, 0)) Prepay_Amount
5007: FROM FII_AP_Prepay_T PREP
5008: WHERE PREP.Invoice_id BETWEEN g_start_range and g_end_range
5009: AND (PREP.Due_Date - g_due_bucket3) BETWEEN g_start_date AND LEAST(g_end_date,g_sysdate)
5010: AND PREP.Creation_Date < (PREP.Due_Date - g_due_bucket3)
5011: GROUP BY PREP.Invoice_ID,
5302: AND TEM.Creation_Date < (TEM.Due_Date + g_past_bucket3) + 1
5303: GROUP BY TEM.Invoice_ID,
5304: TEM.Payment_Num,
5305: TEM.Due_Date) TEMP,
5306: (SELECT /*+ index(prep,FII_AP_PREPAY_T_N1) */ PREP.Invoice_ID,
5307: PREP.Payment_Num,
5308: SUM(NVL(PREP.Prepay_Amount, 0)) Prepay_Amount
5309: FROM FII_AP_Prepay_T PREP
5310: WHERE PREP.Invoice_id BETWEEN g_start_range and g_end_range
5305: TEM.Due_Date) TEMP,
5306: (SELECT /*+ index(prep,FII_AP_PREPAY_T_N1) */ PREP.Invoice_ID,
5307: PREP.Payment_Num,
5308: SUM(NVL(PREP.Prepay_Amount, 0)) Prepay_Amount
5309: FROM FII_AP_Prepay_T PREP
5310: WHERE PREP.Invoice_id BETWEEN g_start_range and g_end_range
5311: AND (PREP.Due_Date + g_past_bucket3) + 1 BETWEEN g_start_date AND LEAST(g_end_date,g_sysdate)
5312: AND PREP.Creation_Date < (PREP.Due_Date + g_past_bucket3) + 1
5313: GROUP BY PREP.Invoice_ID,
5575: AND TEM.Creation_Date < (TEM.Due_Date + g_past_bucket2) + 1
5576: GROUP BY TEM.Invoice_ID,
5577: TEM.Payment_Num,
5578: TEM.Due_Date) TEMP,
5579: (SELECT /*+ index(prep,FII_AP_PREPAY_T_N1) */ PREP.Invoice_ID,
5580: PREP.Payment_Num,
5581: SUM(NVL(PREP.Prepay_Amount, 0)) Prepay_Amount
5582: FROM FII_AP_Prepay_T PREP
5583: WHERE PREP.Invoice_id BETWEEN g_start_range and g_end_range
5578: TEM.Due_Date) TEMP,
5579: (SELECT /*+ index(prep,FII_AP_PREPAY_T_N1) */ PREP.Invoice_ID,
5580: PREP.Payment_Num,
5581: SUM(NVL(PREP.Prepay_Amount, 0)) Prepay_Amount
5582: FROM FII_AP_Prepay_T PREP
5583: WHERE PREP.Invoice_id BETWEEN g_start_range and g_end_range
5584: AND (PREP.Due_Date + g_past_bucket2) + 1 BETWEEN g_start_date AND LEAST(g_end_date,g_sysdate)
5585: AND PREP.Creation_Date < (PREP.Due_Date + g_past_bucket2) + 1
5586: GROUP BY PREP.Invoice_ID,
6403: (SELECT /*+ parallel(p) */ Invoice_ID,
6404: Payment_Num,
6405: sum(Prepay_Amount) Prepay_Amount,
6406: trunc(max(Creation_Date)) Payment_Date
6407: FROM FII_AP_Prepay_T p
6408: GROUP BY Invoice_ID, Payment_Num) PP
6409: WHERE AI.Invoice_ID = PS.Invoice_ID
6410: AND AI.Invoice_Type NOT IN ('PREPAYMENT')
6411: AND AI.Cancel_Date IS NULL
7031: TRUNCATE_TABLE('FII_AP_FUNC_RATES_TEMP');
7032: TRUNCATE_TABLE('FII_AP_PS_WORK_JOBS');
7033: TRUNCATE_TABLE('FII_AP_PAY_SCHED_TEMP');
7034: TRUNCATE_TABLE('FII_AP_WH_TAX_T');
7035: TRUNCATE_TABLE('FII_AP_PREPAY_T');
7036: TRUNCATE_TABLE('FII_AP_PAY_CHK_STG');
7037:
7038: COMMIT;
7039:
7203:
7204: g_state := 'Truncating temp tables after used to populate base tables';
7205: TRUNCATE_TABLE('FII_AP_PAY_SCHED_TEMP');
7206: TRUNCATE_TABLE('FII_AP_WH_TAX_T');
7207: TRUNCATE_TABLE('FII_AP_PREPAY_T');
7208: TRUNCATE_TABLE('FII_AP_PS_WORK_JOBS');
7209: TRUNCATE_TABLE('FII_AP_PAY_CHK_STG');
7210:
7211: END IF;