[Home] [Help]
6055: FII_UTIL.put_line(g_state);
6056: end if;
6057:
6058: /* **************************************************************************
6059: Steps to populate FII_AP_AGING_BKTS_B and FII_AP_DUE_COUNTS_B:
6060:
6061: 1. To determine the action_date that a payment schedule enters
6062: and exits a bucket, if it enters a bucket at all, we need to know
6063: the creation date, due date,and fully paid date of each payment
6075: fully paid date, the date a payment schedule moves buckets, or
6076: null. We also have a variable for each bucket. For entry into a
6077: bucket we store a +1, for exit from a bucket we store a -1. We do
6078: not keep rows with a null date. This subquery is essentially
6079: FII_AP_AGING_BKTS_B at the payment schedule level.
6080:
6081: 4. To populate FII_AP_AGING_BKTS_B at the invoice level, group by
6082: invoice_id and action_date (SDate). Then for each date in order
6083: from earliest to latest date, keep a cumulative sum for each
6077: bucket we store a +1, for exit from a bucket we store a -1. We do
6078: not keep rows with a null date. This subquery is essentially
6079: FII_AP_AGING_BKTS_B at the payment schedule level.
6080:
6081: 4. To populate FII_AP_AGING_BKTS_B at the invoice level, group by
6082: invoice_id and action_date (SDate). Then for each date in order
6083: from earliest to latest date, keep a cumulative sum for each
6084: bucket. For example, let's say an invoice has two payment
6085: schedules. The first goes into bucket 1 on 01-01-2004, the second
6106: 01-01-2004 1
6107: 01-15-2004 0
6108: 01-31-2004 -1
6109:
6110: 6. Populate FII_AP_AGING_BKTS_B with those records with non-zero Due
6111: Bucket or Past Due Bucket Counts. Populate FII_AP_DUE_COUNTS_B
6112: with those records with non-zero Due or Past Due Counts.
6113: ************************************************************************** */
6114:
6120:
6121:
6122: /* For Initial Load we will truncate the data in the count table
6123: and re-populate this table */
6124: TRUNCATE_TABLE('MLOG$_FII_AP_AGING_BKTS_B');
6125: TRUNCATE_TABLE('MLOG$_FII_AP_DUE_COUNTS_B');
6126:
6127: TRUNCATE_TABLE('FII_AP_AGING_BKTS_B');
6128: TRUNCATE_TABLE('FII_AP_DUE_COUNTS_B');
6123: and re-populate this table */
6124: TRUNCATE_TABLE('MLOG$_FII_AP_AGING_BKTS_B');
6125: TRUNCATE_TABLE('MLOG$_FII_AP_DUE_COUNTS_B');
6126:
6127: TRUNCATE_TABLE('FII_AP_AGING_BKTS_B');
6128: TRUNCATE_TABLE('FII_AP_DUE_COUNTS_B');
6129:
6130: g_state := 'Populating FII_AP_AGING_BKTS_B and FII_AP_DUE_COUNTS_B table';
6131: if g_debug_flag = 'Y' then
6126:
6127: TRUNCATE_TABLE('FII_AP_AGING_BKTS_B');
6128: TRUNCATE_TABLE('FII_AP_DUE_COUNTS_B');
6129:
6130: g_state := 'Populating FII_AP_AGING_BKTS_B and FII_AP_DUE_COUNTS_B table';
6131: if g_debug_flag = 'Y' then
6132: FII_UTIL.put_line(g_state);
6133: FII_UTIL.start_timer;
6134: FII_UTIL.put_line('');
6134: FII_UTIL.put_line('');
6135: end if;
6136:
6137:
6138: INSERT /*+ append parallel(fii_ap_aging_bkts_b) parallel(fii_ap_due_counts_b) */ ALL
6139: WHEN ( Due_Bucket1_Cnt <> 0
6140: OR Due_Bucket2_Cnt <> 0
6141: OR Due_Bucket3_Cnt <> 0
6142: OR Past_Due_Bucket1_Cnt <> 0
6141: OR Due_Bucket3_Cnt <> 0
6142: OR Past_Due_Bucket1_Cnt <> 0
6143: OR Past_Due_Bucket2_Cnt <> 0
6144: OR Past_Due_Bucket3_Cnt <> 0)
6145: THEN INTO FII_AP_AGING_BKTS_B(
6146: Time_ID,
6147: Period_Type_ID,
6148: Org_ID,
6149: Supplier_ID,
6433: COMMIT;
6434:
6435:
6436: if g_debug_flag = 'Y' then
6437: FII_UTIL.put_line('Inserted '|| SQL%ROWCOUNT ||' records into FII_AP_AGING_BKTS_B and FII_AP_DUE_COUNTS_B');
6438: FII_UTIL.stop_timer;
6439: FII_UTIL.print_timer('Duration');
6440: FII_UTIL.put_line('');
6441: end if;
6439: FII_UTIL.print_timer('Duration');
6440: FII_UTIL.put_line('');
6441: end if;
6442:
6443: -- g_state := 'Truncating temp tables used to populate FII_AP_AGING_BKTS_B and FII_AP_DUE_COUNTS_B';
6444: -- TRUNCATE_TABLE('FII_AP_BUCKET_CALCS');
6445: -- TRUNCATE_TABLE('FII_AP_PS_BUCKETS');
6446: -- TRUNCATE_TABLE('FII_AP_INDEX_TEMP');
6447: