[Home] [Help]
117:
118: BEGIN
119:
120: if g_debug_flag = 'Y' then
121: FII_UTIL.put_line('Calling procedure: CHECK_XLA_CONVERSION_STATUS');
122: FII_UTIL.put_line('');
123: end if;
124:
125: FOR ledger_record in c_non_upgraded_ledgers LOOP
118: BEGIN
119:
120: if g_debug_flag = 'Y' then
121: FII_UTIL.put_line('Calling procedure: CHECK_XLA_CONVERSION_STATUS');
122: FII_UTIL.put_line('');
123: end if;
124:
125: FOR ledger_record in c_non_upgraded_ledgers LOOP
126:
146: g_errbuf := '
147: ---------------------------------
148: Error in Procedure: CHECK_XLA_CONVERSION_STATUS
149: Message: '||sqlerrm;
150: FII_UTIL.put_line(g_errbuf);
151: raise g_procedure_failure;
152:
153: END CHECK_XLA_CONVERSION_STATUS;
154:
162:
163: l_stmt:='DROP TABLE '||g_fii_schema||'.'|| p_table_name;
164:
165: if g_debug_flag = 'Y' then
166: fii_util.put_line('');
167: fii_util.put_line('Dropping temp table '||p_table_name);
168: fii_util.put_line(l_stmt);
169: end if;
170:
163: l_stmt:='DROP TABLE '||g_fii_schema||'.'|| p_table_name;
164:
165: if g_debug_flag = 'Y' then
166: fii_util.put_line('');
167: fii_util.put_line('Dropping temp table '||p_table_name);
168: fii_util.put_line(l_stmt);
169: end if;
170:
171: EXECUTE IMMEDIATE l_stmt;
164:
165: if g_debug_flag = 'Y' then
166: fii_util.put_line('');
167: fii_util.put_line('Dropping temp table '||p_table_name);
168: fii_util.put_line(l_stmt);
169: end if;
170:
171: EXECUTE IMMEDIATE l_stmt;
172:
203: ' truncate partition '||p_partition;
204: END IF;
205:
206: if g_debug_flag = 'Y' then
207: fii_util.put_line(' ');
208: fii_util.put_line(l_stmt);
209: end if;
210: execute immediate l_stmt;
211:
204: END IF;
205:
206: if g_debug_flag = 'Y' then
207: fii_util.put_line(' ');
208: fii_util.put_line(l_stmt);
209: end if;
210: execute immediate l_stmt;
211:
212: EXCEPTION
232: BEGIN
233: -- DEBUG
234: -- return;
235: if g_debug_flag = 'Y' then
236: FII_UTIL.put_line('Running procedure CLEAN_UP');
237: FII_UTIL.put_line('');
238: -- FII_UTIL.put_line('Truncating table FII_AR_REVENUE_JOBS');
239: end if;
240: truncate_table('fii_ar_revenue_jobs');
233: -- DEBUG
234: -- return;
235: if g_debug_flag = 'Y' then
236: FII_UTIL.put_line('Running procedure CLEAN_UP');
237: FII_UTIL.put_line('');
238: -- FII_UTIL.put_line('Truncating table FII_AR_REVENUE_JOBS');
239: end if;
240: truncate_table('fii_ar_revenue_jobs');
241:
234: -- return;
235: if g_debug_flag = 'Y' then
236: FII_UTIL.put_line('Running procedure CLEAN_UP');
237: FII_UTIL.put_line('');
238: -- FII_UTIL.put_line('Truncating table FII_AR_REVENUE_JOBS');
239: end if;
240: truncate_table('fii_ar_revenue_jobs');
241:
242: IF (g_truncate_id = 'Y') THEN
240: truncate_table('fii_ar_revenue_jobs');
241:
242: IF (g_truncate_id = 'Y') THEN
243: -- if g_debug_flag = 'Y' then
244: -- FII_UTIL.put_line('Truncating table FII_AR_REVENUE_ID');
245: -- end if;
246: truncate_table('fii_ar_revenue_id');
247: END IF;
248:
247: END IF;
248:
249: IF (g_truncate_staging = 'Y') THEN
250: -- if g_debug_flag = 'Y' then
251: -- FII_UTIL.put_line('Truncating table FII_AR_REVENUE_STG');
252: -- end if;
253: truncate_table('fii_ar_revenue_stg');
254:
255: END IF;
314: RAISE G_LOGIN_INFO_NOT_AVABLE;
315: END IF;
316:
317: if g_debug_flag = 'Y' then
318: FII_UTIL.put_line('User ID: ' || g_fii_user_id || ' Login ID: ' || g_fii_login_id);
319: end if;
320:
321: EXCEPTION
322: WHEN G_LOGIN_INFO_NOT_AVABLE THEN
319: end if;
320:
321: EXCEPTION
322: WHEN G_LOGIN_INFO_NOT_AVABLE THEN
323: FII_UTIL.put_line('Procedure INIT. Can not get User ID and/or Login ID, therefore program terminated.');
324: g_retcode := -1;
325: RAISE;
326:
327: WHEN OTHERS THEN
362: (SELECT b2.revenue_pk FROM FII_AR_REVENUE_STG b2
363: GROUP BY b2.revenue_pk
364: HAVING count(*)>1) ';
365:
366: fii_util.put_line('');
367: fii_util.put_line('There are duplicate records in the staging table.
368: This needs to be fixed before inserting records in FII_AR_REVENUE_B.
369: Created temp table fii_ar_uni_con_rec which stores
370: records that violate unique constraint on FII_AR_REVENUE_B. Please investigate
363: GROUP BY b2.revenue_pk
364: HAVING count(*)>1) ';
365:
366: fii_util.put_line('');
367: fii_util.put_line('There are duplicate records in the staging table.
368: This needs to be fixed before inserting records in FII_AR_REVENUE_B.
369: Created temp table fii_ar_uni_con_rec which stores
370: records that violate unique constraint on FII_AR_REVENUE_B. Please investigate
371: this table for duplicate records.');
368: This needs to be fixed before inserting records in FII_AR_REVENUE_B.
369: Created temp table fii_ar_uni_con_rec which stores
370: records that violate unique constraint on FII_AR_REVENUE_B. Please investigate
371: this table for duplicate records.');
372: fii_util.put_line('');
373:
374: if g_debug_flag = 'Y' then
375: fii_util.put_line(l_stmt);
376: fii_util.start_timer;
371: this table for duplicate records.');
372: fii_util.put_line('');
373:
374: if g_debug_flag = 'Y' then
375: fii_util.put_line(l_stmt);
376: fii_util.start_timer;
377: end if;
378:
379: EXECUTE IMMEDIATE l_stmt;
372: fii_util.put_line('');
373:
374: if g_debug_flag = 'Y' then
375: fii_util.put_line(l_stmt);
376: fii_util.start_timer;
377: end if;
378:
379: EXECUTE IMMEDIATE l_stmt;
380:
378:
379: EXECUTE IMMEDIATE l_stmt;
380:
381: if g_debug_flag = 'Y' then
382: fii_util.put_line(' Processed '||SQL%ROWCOUNT||' rows');
383: fii_util.stop_timer;
384: fii_util.print_timer('Duration');
385: end if;
386:
379: EXECUTE IMMEDIATE l_stmt;
380:
381: if g_debug_flag = 'Y' then
382: fii_util.put_line(' Processed '||SQL%ROWCOUNT||' rows');
383: fii_util.stop_timer;
384: fii_util.print_timer('Duration');
385: end if;
386:
387: COMMIT;
380:
381: if g_debug_flag = 'Y' then
382: fii_util.put_line(' Processed '||SQL%ROWCOUNT||' rows');
383: fii_util.stop_timer;
384: fii_util.print_timer('Duration');
385: end if;
386:
387: COMMIT;
388:
413:
414: BEGIN
415:
416: if g_debug_flag = 'Y' then
417: fii_util.put_line(' ');
418: fii_util.start_timer;
419: end if;
420:
421:
414: BEGIN
415:
416: if g_debug_flag = 'Y' then
417: fii_util.put_line(' ');
418: fii_util.start_timer;
419: end if;
420:
421:
422: IF (p_view_type_id = 4) THEN
946:
947: l_row_count := SQL%ROWCOUNT;
948:
949: if g_debug_flag = 'Y' then
950: fii_util.put_line('');
951: fii_util.put_line('Inserting records into staging table');
952: fii_util.put_line('Inserted '||l_row_count||' rows');
953: fii_util.stop_timer;
954: fii_util.print_timer('Duration');
947: l_row_count := SQL%ROWCOUNT;
948:
949: if g_debug_flag = 'Y' then
950: fii_util.put_line('');
951: fii_util.put_line('Inserting records into staging table');
952: fii_util.put_line('Inserted '||l_row_count||' rows');
953: fii_util.stop_timer;
954: fii_util.print_timer('Duration');
955: end if;
948:
949: if g_debug_flag = 'Y' then
950: fii_util.put_line('');
951: fii_util.put_line('Inserting records into staging table');
952: fii_util.put_line('Inserted '||l_row_count||' rows');
953: fii_util.stop_timer;
954: fii_util.print_timer('Duration');
955: end if;
956:
949: if g_debug_flag = 'Y' then
950: fii_util.put_line('');
951: fii_util.put_line('Inserting records into staging table');
952: fii_util.put_line('Inserted '||l_row_count||' rows');
953: fii_util.stop_timer;
954: fii_util.print_timer('Duration');
955: end if;
956:
957: commit;
950: fii_util.put_line('');
951: fii_util.put_line('Inserting records into staging table');
952: fii_util.put_line('Inserted '||l_row_count||' rows');
953: fii_util.stop_timer;
954: fii_util.print_timer('Duration');
955: end if;
956:
957: commit;
958:
983:
984: BEGIN
985:
986: if g_debug_flag = 'Y' then
987: fii_util.put_line('');
988: fii_util.put_line('Started inserting rows into staging table for def rev transactions prior to global start date.');
989: fii_util.start_timer;
990: end if;
991:
984: BEGIN
985:
986: if g_debug_flag = 'Y' then
987: fii_util.put_line('');
988: fii_util.put_line('Started inserting rows into staging table for def rev transactions prior to global start date.');
989: fii_util.start_timer;
990: end if;
991:
992: IF (p_view_type_id = 4) THEN
985:
986: if g_debug_flag = 'Y' then
987: fii_util.put_line('');
988: fii_util.put_line('Started inserting rows into staging table for def rev transactions prior to global start date.');
989: fii_util.start_timer;
990: end if;
991:
992: IF (p_view_type_id = 4) THEN
993: -- Bug 4942753: Per Lester's suggestion, reordered the XLA tables
1246:
1247: l_row_count := SQL%ROWCOUNT;
1248:
1249: if g_debug_flag = 'Y' then
1250: fii_util.put_line('');
1251: -- fii_util.put_line('Extracting Deferred Revenue transactions prior to global_start date to staging table');
1252: fii_util.put_line('Inserted '||l_row_count||' rows into staging table.');
1253: fii_util.stop_timer;
1254: fii_util.print_timer('Duration');
1247: l_row_count := SQL%ROWCOUNT;
1248:
1249: if g_debug_flag = 'Y' then
1250: fii_util.put_line('');
1251: -- fii_util.put_line('Extracting Deferred Revenue transactions prior to global_start date to staging table');
1252: fii_util.put_line('Inserted '||l_row_count||' rows into staging table.');
1253: fii_util.stop_timer;
1254: fii_util.print_timer('Duration');
1255: end if;
1248:
1249: if g_debug_flag = 'Y' then
1250: fii_util.put_line('');
1251: -- fii_util.put_line('Extracting Deferred Revenue transactions prior to global_start date to staging table');
1252: fii_util.put_line('Inserted '||l_row_count||' rows into staging table.');
1253: fii_util.stop_timer;
1254: fii_util.print_timer('Duration');
1255: end if;
1256:
1249: if g_debug_flag = 'Y' then
1250: fii_util.put_line('');
1251: -- fii_util.put_line('Extracting Deferred Revenue transactions prior to global_start date to staging table');
1252: fii_util.put_line('Inserted '||l_row_count||' rows into staging table.');
1253: fii_util.stop_timer;
1254: fii_util.print_timer('Duration');
1255: end if;
1256:
1257: commit;
1250: fii_util.put_line('');
1251: -- fii_util.put_line('Extracting Deferred Revenue transactions prior to global_start date to staging table');
1252: fii_util.put_line('Inserted '||l_row_count||' rows into staging table.');
1253: fii_util.stop_timer;
1254: fii_util.print_timer('Duration');
1255: end if;
1256:
1257: commit;
1258:
1281:
1282: SELECT FII_AR_REVENUE_B_S.nextval INTO seq_id FROM dual;
1283:
1284: if g_debug_flag = 'Y' then
1285: fii_util.put_line(' ');
1286: fii_util.put_line('Merging data into base summary table');
1287: fii_util.start_timer;
1288: fii_util.put_line('');
1289: end if;
1282: SELECT FII_AR_REVENUE_B_S.nextval INTO seq_id FROM dual;
1283:
1284: if g_debug_flag = 'Y' then
1285: fii_util.put_line(' ');
1286: fii_util.put_line('Merging data into base summary table');
1287: fii_util.start_timer;
1288: fii_util.put_line('');
1289: end if;
1290:
1283:
1284: if g_debug_flag = 'Y' then
1285: fii_util.put_line(' ');
1286: fii_util.put_line('Merging data into base summary table');
1287: fii_util.start_timer;
1288: fii_util.put_line('');
1289: end if;
1290:
1291: MERGE INTO FII_AR_REVENUE_B f
1284: if g_debug_flag = 'Y' then
1285: fii_util.put_line(' ');
1286: fii_util.put_line('Merging data into base summary table');
1287: fii_util.start_timer;
1288: fii_util.put_line('');
1289: end if;
1290:
1291: MERGE INTO FII_AR_REVENUE_B f
1292: USING (SELECT /*+ cardinality(stg,1) */ * FROM FII_AR_REVENUE_STG stg
1393:
1394:
1395: l_row_count := SQL%ROWCOUNT;
1396: if g_debug_flag = 'Y' then
1397: fii_util.put_line('Processed '||l_row_count||' rows');
1398: fii_util.stop_timer;
1399: fii_util.print_timer('Duration');
1400: end if;
1401:
1394:
1395: l_row_count := SQL%ROWCOUNT;
1396: if g_debug_flag = 'Y' then
1397: fii_util.put_line('Processed '||l_row_count||' rows');
1398: fii_util.stop_timer;
1399: fii_util.print_timer('Duration');
1400: end if;
1401:
1402: commit;
1395: l_row_count := SQL%ROWCOUNT;
1396: if g_debug_flag = 'Y' then
1397: fii_util.put_line('Processed '||l_row_count||' rows');
1398: fii_util.stop_timer;
1399: fii_util.print_timer('Duration');
1400: end if;
1401:
1402: commit;
1403:
1489: -- If yes, we will print out missing rate report and return
1490: -- -1. If there's no missing rate, then we will return 1.
1491: -----------------------------------------------------------
1492: if g_debug_flag = 'Y' then
1493: fii_util.put_line(' ');
1494: fii_util.put_timestamp;
1495: fii_util.put_line('Checking whether there are any missing exchange rates.');
1496: end if;
1497:
1490: -- -1. If there's no missing rate, then we will return 1.
1491: -----------------------------------------------------------
1492: if g_debug_flag = 'Y' then
1493: fii_util.put_line(' ');
1494: fii_util.put_timestamp;
1495: fii_util.put_line('Checking whether there are any missing exchange rates.');
1496: end if;
1497:
1498:
1491: -----------------------------------------------------------
1492: if g_debug_flag = 'Y' then
1493: fii_util.put_line(' ');
1494: fii_util.put_timestamp;
1495: fii_util.put_line('Checking whether there are any missing exchange rates.');
1496: end if;
1497:
1498:
1499: IF g_program_type = 'L' THEN
1548: l_sec_rate_type_name := null;
1549: end if;
1550: exception
1551: when others then
1552: fii_util.write_log(
1553: 'Failed to convert rate_type to rate_type_name' );
1554: raise;
1555: end;
1556:
1611: END IF;
1612:
1613: IF (l_miss_rates_prim > 0) THEN
1614: -- if g_debug_flag = 'Y' then
1615: fii_util.put_line(' ');
1616: fii_util.put_line('There are some missing prim exchange rates.');
1617: -- end if;
1618: bis_collection_utilities.writeMissingRateHeader;
1619:
1612:
1613: IF (l_miss_rates_prim > 0) THEN
1614: -- if g_debug_flag = 'Y' then
1615: fii_util.put_line(' ');
1616: fii_util.put_line('There are some missing prim exchange rates.');
1617: -- end if;
1618: bis_collection_utilities.writeMissingRateHeader;
1619:
1620:
1646: END IF;
1647:
1648: IF (l_miss_rates_sec > 0) THEN
1649: -- if g_debug_flag = 'Y' then
1650: fii_util.put_line(' ');
1651: fii_util.put_line('There are some missing sec conversion rates.');
1652: -- end if;
1653:
1654: bis_collection_utilities.writeMissingRateHeader;
1647:
1648: IF (l_miss_rates_sec > 0) THEN
1649: -- if g_debug_flag = 'Y' then
1650: fii_util.put_line(' ');
1651: fii_util.put_line('There are some missing sec conversion rates.');
1652: -- end if;
1653:
1654: bis_collection_utilities.writeMissingRateHeader;
1655:
1723:
1724: l_count := 0;
1725: g_section := 'Section 10';
1726: -- if g_debug_flag = 'Y' then
1727: -- fii_util.put_line('Identify Change for Revenue and Deferred Revenue records for view type '||p_type);
1728: -- end if;
1729:
1730: -- --------------------------------------------
1731: -- Identify changed rows based on gl date and
1755: from FII_CHANGE_LOG
1756: where log_item = 'AR_MAX_GROUP_ID';
1757:
1758: -- if g_debug_flag = 'Y' then
1759: -- fii_util.put_line ('l_gl_from: ' ||l_gl_from);
1760: -- fii_util.put_line ('l_gl_to: ' ||l_gl_to);
1761: -- end if;
1762:
1763:
1756: where log_item = 'AR_MAX_GROUP_ID';
1757:
1758: -- if g_debug_flag = 'Y' then
1759: -- fii_util.put_line ('l_gl_from: ' ||l_gl_from);
1760: -- fii_util.put_line ('l_gl_to: ' ||l_gl_to);
1761: -- end if;
1762:
1763:
1764: IF (p_type = 'AR ADJ') THEN
1791: and adj.amount <> 0
1792: and t.set_of_books_id = lidset.ledger_id';
1793:
1794: if g_debug_flag = 'Y' then
1795: -- fii_util.put_line(' ');
1796: -- fii_util.put_line(l_stmt);
1797: fii_util.start_timer;
1798: end if;
1799: EXECUTE IMMEDIATE l_stmt using g_usage_code, l_lud_from, l_lud_to;
1792: and t.set_of_books_id = lidset.ledger_id';
1793:
1794: if g_debug_flag = 'Y' then
1795: -- fii_util.put_line(' ');
1796: -- fii_util.put_line(l_stmt);
1797: fii_util.start_timer;
1798: end if;
1799: EXECUTE IMMEDIATE l_stmt using g_usage_code, l_lud_from, l_lud_to;
1800: l_count := SQL%ROWCOUNT;
1793:
1794: if g_debug_flag = 'Y' then
1795: -- fii_util.put_line(' ');
1796: -- fii_util.put_line(l_stmt);
1797: fii_util.start_timer;
1798: end if;
1799: EXECUTE IMMEDIATE l_stmt using g_usage_code, l_lud_from, l_lud_to;
1800: l_count := SQL%ROWCOUNT;
1801: if g_debug_flag = 'Y' then
1798: end if;
1799: EXECUTE IMMEDIATE l_stmt using g_usage_code, l_lud_from, l_lud_to;
1800: l_count := SQL%ROWCOUNT;
1801: if g_debug_flag = 'Y' then
1802: -- fii_util.put_line(' ');
1803: fii_util.stop_timer;
1804: fii_util.print_timer('Duration');
1805: end if;
1806:
1799: EXECUTE IMMEDIATE l_stmt using g_usage_code, l_lud_from, l_lud_to;
1800: l_count := SQL%ROWCOUNT;
1801: if g_debug_flag = 'Y' then
1802: -- fii_util.put_line(' ');
1803: fii_util.stop_timer;
1804: fii_util.print_timer('Duration');
1805: end if;
1806:
1807:
1800: l_count := SQL%ROWCOUNT;
1801: if g_debug_flag = 'Y' then
1802: -- fii_util.put_line(' ');
1803: fii_util.stop_timer;
1804: fii_util.print_timer('Duration');
1805: end if;
1806:
1807:
1808: l_stmt := '
1826: AND t.amount <> 0
1827: AND t.set_of_books_id = lidset.ledger_id';
1828:
1829: if g_debug_flag = 'Y' then
1830: -- fii_util.put_line(' ');
1831: -- fii_util.put_line(l_stmt);
1832: fii_util.start_timer;
1833: end if;
1834: EXECUTE IMMEDIATE l_stmt using g_usage_code, l_gl_from, l_gl_to, l_lud_from, l_lud_to;
1827: AND t.set_of_books_id = lidset.ledger_id';
1828:
1829: if g_debug_flag = 'Y' then
1830: -- fii_util.put_line(' ');
1831: -- fii_util.put_line(l_stmt);
1832: fii_util.start_timer;
1833: end if;
1834: EXECUTE IMMEDIATE l_stmt using g_usage_code, l_gl_from, l_gl_to, l_lud_from, l_lud_to;
1835: l_count := l_count + SQL%ROWCOUNT;
1828:
1829: if g_debug_flag = 'Y' then
1830: -- fii_util.put_line(' ');
1831: -- fii_util.put_line(l_stmt);
1832: fii_util.start_timer;
1833: end if;
1834: EXECUTE IMMEDIATE l_stmt using g_usage_code, l_gl_from, l_gl_to, l_lud_from, l_lud_to;
1835: l_count := l_count + SQL%ROWCOUNT;
1836: if g_debug_flag = 'Y' then
1833: end if;
1834: EXECUTE IMMEDIATE l_stmt using g_usage_code, l_gl_from, l_gl_to, l_lud_from, l_lud_to;
1835: l_count := l_count + SQL%ROWCOUNT;
1836: if g_debug_flag = 'Y' then
1837: -- fii_util.put_line(' ');
1838: fii_util.stop_timer;
1839: fii_util.print_timer('Duration');
1840: end if;
1841: */ NULL;
1834: EXECUTE IMMEDIATE l_stmt using g_usage_code, l_gl_from, l_gl_to, l_lud_from, l_lud_to;
1835: l_count := l_count + SQL%ROWCOUNT;
1836: if g_debug_flag = 'Y' then
1837: -- fii_util.put_line(' ');
1838: fii_util.stop_timer;
1839: fii_util.print_timer('Duration');
1840: end if;
1841: */ NULL;
1842:
1835: l_count := l_count + SQL%ROWCOUNT;
1836: if g_debug_flag = 'Y' then
1837: -- fii_util.put_line(' ');
1838: fii_util.stop_timer;
1839: fii_util.print_timer('Duration');
1840: end if;
1841: */ NULL;
1842:
1843: /* ELSIF (p_type = 'AR INV') THEN
1880: AND ct.last_update_date between to_date(:e,''YYYY/MM/DD HH24:MI:SS'') AND to_date(:f,''YYYY/MM/DD HH24:MI:SS'')
1881: AND ct.set_of_books_id = lidset.ledger_id';
1882:
1883: if g_debug_flag = 'Y' then
1884: -- fii_util.put_line(' ');
1885: -- fii_util.put_line(l_stmt);
1886: fii_util.start_timer;
1887: end if;
1888: EXECUTE IMMEDIATE l_stmt using g_usage_code,l_lud_from, l_lud_to, g_usage_code, l_lud_from, l_lud_to;
1881: AND ct.set_of_books_id = lidset.ledger_id';
1882:
1883: if g_debug_flag = 'Y' then
1884: -- fii_util.put_line(' ');
1885: -- fii_util.put_line(l_stmt);
1886: fii_util.start_timer;
1887: end if;
1888: EXECUTE IMMEDIATE l_stmt using g_usage_code,l_lud_from, l_lud_to, g_usage_code, l_lud_from, l_lud_to;
1889: l_count := SQL%ROWCOUNT;
1882:
1883: if g_debug_flag = 'Y' then
1884: -- fii_util.put_line(' ');
1885: -- fii_util.put_line(l_stmt);
1886: fii_util.start_timer;
1887: end if;
1888: EXECUTE IMMEDIATE l_stmt using g_usage_code,l_lud_from, l_lud_to, g_usage_code, l_lud_from, l_lud_to;
1889: l_count := SQL%ROWCOUNT;
1890: if g_debug_flag = 'Y' then
1887: end if;
1888: EXECUTE IMMEDIATE l_stmt using g_usage_code,l_lud_from, l_lud_to, g_usage_code, l_lud_from, l_lud_to;
1889: l_count := SQL%ROWCOUNT;
1890: if g_debug_flag = 'Y' then
1891: -- fii_util.put_line(' ');
1892: fii_util.stop_timer;
1893: fii_util.print_timer('Duration');
1894: end if;
1895: */
1888: EXECUTE IMMEDIATE l_stmt using g_usage_code,l_lud_from, l_lud_to, g_usage_code, l_lud_from, l_lud_to;
1889: l_count := SQL%ROWCOUNT;
1890: if g_debug_flag = 'Y' then
1891: -- fii_util.put_line(' ');
1892: fii_util.stop_timer;
1893: fii_util.print_timer('Duration');
1894: end if;
1895: */
1896:
1889: l_count := SQL%ROWCOUNT;
1890: if g_debug_flag = 'Y' then
1891: -- fii_util.put_line(' ');
1892: fii_util.stop_timer;
1893: fii_util.print_timer('Duration');
1894: end if;
1895: */
1896:
1897: ELSIF (p_type = 'AR DL') THEN
1937: AND aeh.gl_transfer_status_code = ''Y''
1938: AND aeh.ledger_id = lidset.ledger_id';
1939:
1940: if g_debug_flag = 'Y' then
1941: fii_util.put_line(' ');
1942: -- fii_util.put_line(l_stmt);
1943: fii_util.start_timer;
1944: end if;
1945: EXECUTE IMMEDIATE l_stmt using g_usage_code, l_gl_from, l_gl_to;
1938: AND aeh.ledger_id = lidset.ledger_id';
1939:
1940: if g_debug_flag = 'Y' then
1941: fii_util.put_line(' ');
1942: -- fii_util.put_line(l_stmt);
1943: fii_util.start_timer;
1944: end if;
1945: EXECUTE IMMEDIATE l_stmt using g_usage_code, l_gl_from, l_gl_to;
1946: l_count := SQL%ROWCOUNT;
1939:
1940: if g_debug_flag = 'Y' then
1941: fii_util.put_line(' ');
1942: -- fii_util.put_line(l_stmt);
1943: fii_util.start_timer;
1944: end if;
1945: EXECUTE IMMEDIATE l_stmt using g_usage_code, l_gl_from, l_gl_to;
1946: l_count := SQL%ROWCOUNT;
1947: if g_debug_flag = 'Y' then
1944: end if;
1945: EXECUTE IMMEDIATE l_stmt using g_usage_code, l_gl_from, l_gl_to;
1946: l_count := SQL%ROWCOUNT;
1947: if g_debug_flag = 'Y' then
1948: -- fii_util.put_line(' ');
1949: fii_util.stop_timer;
1950: fii_util.print_timer('Duration');
1951: end if;
1952:
1945: EXECUTE IMMEDIATE l_stmt using g_usage_code, l_gl_from, l_gl_to;
1946: l_count := SQL%ROWCOUNT;
1947: if g_debug_flag = 'Y' then
1948: -- fii_util.put_line(' ');
1949: fii_util.stop_timer;
1950: fii_util.print_timer('Duration');
1951: end if;
1952:
1953: ELSE
1946: l_count := SQL%ROWCOUNT;
1947: if g_debug_flag = 'Y' then
1948: -- fii_util.put_line(' ');
1949: fii_util.stop_timer;
1950: fii_util.print_timer('Duration');
1951: end if;
1952:
1953: ELSE
1954: l_stmt := 'INSERT INTO FII_AR_REVENUE_ID (
1975: AND aeh.gl_transfer_status_code = ''Y''
1976: AND aeh.ledger_id = lidset.ledger_id';
1977:
1978: if g_debug_flag = 'Y' then
1979: -- fii_util.put_line(' ');
1980: -- fii_util.put_line(l_stmt);
1981: fii_util.start_timer;
1982: end if;
1983: EXECUTE IMMEDIATE l_stmt using g_usage_code, l_gl_from, l_gl_to, l_max_group_id;
1976: AND aeh.ledger_id = lidset.ledger_id';
1977:
1978: if g_debug_flag = 'Y' then
1979: -- fii_util.put_line(' ');
1980: -- fii_util.put_line(l_stmt);
1981: fii_util.start_timer;
1982: end if;
1983: EXECUTE IMMEDIATE l_stmt using g_usage_code, l_gl_from, l_gl_to, l_max_group_id;
1984: l_count := SQL%ROWCOUNT;
1977:
1978: if g_debug_flag = 'Y' then
1979: -- fii_util.put_line(' ');
1980: -- fii_util.put_line(l_stmt);
1981: fii_util.start_timer;
1982: end if;
1983: EXECUTE IMMEDIATE l_stmt using g_usage_code, l_gl_from, l_gl_to, l_max_group_id;
1984: l_count := SQL%ROWCOUNT;
1985: if g_debug_flag = 'Y' then
1982: end if;
1983: EXECUTE IMMEDIATE l_stmt using g_usage_code, l_gl_from, l_gl_to, l_max_group_id;
1984: l_count := SQL%ROWCOUNT;
1985: if g_debug_flag = 'Y' then
1986: -- fii_util.put_line(' ');
1987: fii_util.stop_timer;
1988: fii_util.print_timer('Duration');
1989: end if;
1990:
1983: EXECUTE IMMEDIATE l_stmt using g_usage_code, l_gl_from, l_gl_to, l_max_group_id;
1984: l_count := SQL%ROWCOUNT;
1985: if g_debug_flag = 'Y' then
1986: -- fii_util.put_line(' ');
1987: fii_util.stop_timer;
1988: fii_util.print_timer('Duration');
1989: end if;
1990:
1991:
1984: l_count := SQL%ROWCOUNT;
1985: if g_debug_flag = 'Y' then
1986: -- fii_util.put_line(' ');
1987: fii_util.stop_timer;
1988: fii_util.print_timer('Duration');
1989: end if;
1990:
1991:
1992: END IF;
1991:
1992: END IF;
1993:
1994: /* if g_debug_flag = 'Y' then
1995: fii_util.put_line(' ');
1996: fii_util.put_line(l_stmt);
1997: fii_util.start_timer;
1998: end if;
1999: EXECUTE IMMEDIATE l_stmt;
1992: END IF;
1993:
1994: /* if g_debug_flag = 'Y' then
1995: fii_util.put_line(' ');
1996: fii_util.put_line(l_stmt);
1997: fii_util.start_timer;
1998: end if;
1999: EXECUTE IMMEDIATE l_stmt;
2000: l_count := SQL%ROWCOUNT;
1993:
1994: /* if g_debug_flag = 'Y' then
1995: fii_util.put_line(' ');
1996: fii_util.put_line(l_stmt);
1997: fii_util.start_timer;
1998: end if;
1999: EXECUTE IMMEDIATE l_stmt;
2000: l_count := SQL%ROWCOUNT;
2001: if g_debug_flag = 'Y' then
1998: end if;
1999: EXECUTE IMMEDIATE l_stmt;
2000: l_count := SQL%ROWCOUNT;
2001: if g_debug_flag = 'Y' then
2002: fii_util.put_line(' ');
2003: fii_util.stop_timer;
2004: fii_util.print_timer('Duration');
2005: end if; */
2006:
1999: EXECUTE IMMEDIATE l_stmt;
2000: l_count := SQL%ROWCOUNT;
2001: if g_debug_flag = 'Y' then
2002: fii_util.put_line(' ');
2003: fii_util.stop_timer;
2004: fii_util.print_timer('Duration');
2005: end if; */
2006:
2007: END IF;
2000: l_count := SQL%ROWCOUNT;
2001: if g_debug_flag = 'Y' then
2002: fii_util.put_line(' ');
2003: fii_util.stop_timer;
2004: fii_util.print_timer('Duration');
2005: end if; */
2006:
2007: END IF;
2008:
2055: BEGIN
2056: g_section := 'Section 10';
2057:
2058: if g_debug_flag = 'Y' then
2059: fii_util.put_line('Running function IDENTIFY_CHANGE_INIT to identify deferred rev records prior to global start date for view type '||p_type);
2060: end if;
2061:
2062: IF (p_type = 'AR ADJ') THEN
2063:
2153: ) z
2154: where lid is not null ';
2155:
2156: if g_debug_flag = 'Y' then
2157: fii_util.put_line('');
2158: -- fii_util.put_line(l_stmt);
2159: fii_util.start_timer;
2160: end if;
2161:
2154: where lid is not null ';
2155:
2156: if g_debug_flag = 'Y' then
2157: fii_util.put_line('');
2158: -- fii_util.put_line(l_stmt);
2159: fii_util.start_timer;
2160: end if;
2161:
2162: EXECUTE IMMEDIATE l_stmt;
2155:
2156: if g_debug_flag = 'Y' then
2157: fii_util.put_line('');
2158: -- fii_util.put_line(l_stmt);
2159: fii_util.start_timer;
2160: end if;
2161:
2162: EXECUTE IMMEDIATE l_stmt;
2163: l_count := SQL%ROWCOUNT;
2162: EXECUTE IMMEDIATE l_stmt;
2163: l_count := SQL%ROWCOUNT;
2164:
2165: if g_debug_flag = 'Y' then
2166: fii_util.put_line('');
2167: fii_util.stop_timer;
2168: fii_util.print_timer('Duration');
2169: end if;
2170:
2163: l_count := SQL%ROWCOUNT;
2164:
2165: if g_debug_flag = 'Y' then
2166: fii_util.put_line('');
2167: fii_util.stop_timer;
2168: fii_util.print_timer('Duration');
2169: end if;
2170:
2171: END IF;
2164:
2165: if g_debug_flag = 'Y' then
2166: fii_util.put_line('');
2167: fii_util.stop_timer;
2168: fii_util.print_timer('Duration');
2169: end if;
2170:
2171: END IF;
2172:
2177: tabname => 'FII_AR_REVENUE_ID');
2178:
2179: EXECUTE IMMEDIATE 'ALTER SESSION ENABLE PARALLEL DML';
2180:
2181: fii_util.put_line('Altering session enable parallel DML after FND_STATS call, bug 4127183.');
2182:
2183: RETURN(l_count);
2184:
2185: EXCEPTION
2205: BEGIN
2206: -- g_section := 'section 10';
2207:
2208: -- if g_debug_flag = 'Y' then
2209: -- fii_util.put_line(g_section);
2210: -- end if;
2211:
2212: SELECT NVL(item_value, 'N')
2213: INTO l_result
2279: BEGIN
2280: -- g_section := 'section 10';
2281:
2282: -- if g_debug_flag = 'Y' then
2283: -- fii_util.put_line(g_section);
2284: -- end if;
2285:
2286: SELECT NVL(item_value, 'N')
2287: INTO l_result
2359: -- updating processing tables
2360: -- ----------------------------------------------
2361: g_section := 'Section 10';
2362: if g_debug_flag = 'Y' then
2363: fii_util.put_line(' ');
2364: fii_util.put_line('Registering jobs to handle preperation work');
2365: fii_util.start_timer;
2366: end if;
2367:
2360: -- ----------------------------------------------
2361: g_section := 'Section 10';
2362: if g_debug_flag = 'Y' then
2363: fii_util.put_line(' ');
2364: fii_util.put_line('Registering jobs to handle preperation work');
2365: fii_util.start_timer;
2366: end if;
2367:
2368: -- --------------------------------------------------
2361: g_section := 'Section 10';
2362: if g_debug_flag = 'Y' then
2363: fii_util.put_line(' ');
2364: fii_util.put_line('Registering jobs to handle preperation work');
2365: fii_util.start_timer;
2366: end if;
2367:
2368: -- --------------------------------------------------
2369: -- Registering AR change identification as separate
2369: -- Registering AR change identification as separate
2370: -- jobs broken out by time ranges due to volume
2371: -- --------------------------------------------------
2372: if g_debug_flag = 'Y' then
2373: fii_util.put_line('');
2374: fii_util.put_line('Registering jobs for AR INV, AR ADJ trans types: '||
2375: to_char(g_gl_from_date,'YYYY/MM/DD HH24:MI:SS')||' to '||
2376: to_char(g_gl_to_date,'YYYY/MM/DD HH24:MI:SS'));
2377: end if;
2370: -- jobs broken out by time ranges due to volume
2371: -- --------------------------------------------------
2372: if g_debug_flag = 'Y' then
2373: fii_util.put_line('');
2374: fii_util.put_line('Registering jobs for AR INV, AR ADJ trans types: '||
2375: to_char(g_gl_from_date,'YYYY/MM/DD HH24:MI:SS')||' to '||
2376: to_char(g_gl_to_date,'YYYY/MM/DD HH24:MI:SS'));
2377: end if;
2378:
2413: -- --------------------------------------------------
2414: g_section := 'Section 20';
2415:
2416: if g_debug_flag = 'Y' then
2417: fii_util.put_line('');
2418: fii_util.put_line('Registering jobs for AR DL trans type: '||
2419: to_char(g_gl_from_date,'YYYY/MM/DD HH24:MI:SS')||' to '||
2420: to_char(g_gl_to_date,'YYYY/MM/DD HH24:MI:SS'));
2421: end if;
2414: g_section := 'Section 20';
2415:
2416: if g_debug_flag = 'Y' then
2417: fii_util.put_line('');
2418: fii_util.put_line('Registering jobs for AR DL trans type: '||
2419: to_char(g_gl_from_date,'YYYY/MM/DD HH24:MI:SS')||' to '||
2420: to_char(g_gl_to_date,'YYYY/MM/DD HH24:MI:SS'));
2421: end if;
2422:
2481: l_count := l_count + 1;
2482:
2483:
2484: if g_debug_flag = 'Y' then
2485: fii_util.put_line('');
2486: fii_util.put_line('Registered jobs for AR DL: '||l_count||' jobs covering '||
2487: to_char(g_gl_from_date,'YYYY/MM/DD HH24:MI:SS')||' to '||
2488: to_char(g_gl_to_date,'YYYY/MM/DD HH24:MI:SS'));
2489: end if;
2482:
2483:
2484: if g_debug_flag = 'Y' then
2485: fii_util.put_line('');
2486: fii_util.put_line('Registered jobs for AR DL: '||l_count||' jobs covering '||
2487: to_char(g_gl_from_date,'YYYY/MM/DD HH24:MI:SS')||' to '||
2488: to_char(g_gl_to_date,'YYYY/MM/DD HH24:MI:SS'));
2489: end if;
2490:
2493: -- Registering remaining miscellaneous jobs
2494: -- --------------------------------------------------
2495: g_section := 'Section 30';
2496: if g_debug_flag = 'Y' then
2497: fii_util.put_line('');
2498: fii_util.put_line('Registering remaining misc jobs ');
2499: end if;
2500:
2501: INSERT INTO FII_AR_REVENUE_JOBS (
2494: -- --------------------------------------------------
2495: g_section := 'Section 30';
2496: if g_debug_flag = 'Y' then
2497: fii_util.put_line('');
2498: fii_util.put_line('Registering remaining misc jobs ');
2499: end if;
2500:
2501: INSERT INTO FII_AR_REVENUE_JOBS (
2502: function,
2518:
2519: l_count := l_count + 2;
2520:
2521: if g_debug_flag = 'Y' then
2522: fii_util.put_line(' ');
2523: fii_util.put_line('Registered '||l_count||' jobs');
2524: fii_util.stop_timer;
2525: fii_util.print_timer('Duration');
2526: end if;
2519: l_count := l_count + 2;
2520:
2521: if g_debug_flag = 'Y' then
2522: fii_util.put_line(' ');
2523: fii_util.put_line('Registered '||l_count||' jobs');
2524: fii_util.stop_timer;
2525: fii_util.print_timer('Duration');
2526: end if;
2527:
2520:
2521: if g_debug_flag = 'Y' then
2522: fii_util.put_line(' ');
2523: fii_util.put_line('Registered '||l_count||' jobs');
2524: fii_util.stop_timer;
2525: fii_util.print_timer('Duration');
2526: end if;
2527:
2528:
2521: if g_debug_flag = 'Y' then
2522: fii_util.put_line(' ');
2523: fii_util.put_line('Registered '||l_count||' jobs');
2524: fii_util.stop_timer;
2525: fii_util.print_timer('Duration');
2526: end if;
2527:
2528:
2529: EXCEPTION
2574: -- Drop index to improve performance
2575: -- ---------------------------------
2576: g_section := 'Section 50';
2577: if g_debug_flag = 'Y' then
2578: fii_util.put_line('');
2579: fii_util.put_line('Register extraction jobs');
2580: end if;
2581:
2582: l_curr_job_num := 1;
2575: -- ---------------------------------
2576: g_section := 'Section 50';
2577: if g_debug_flag = 'Y' then
2578: fii_util.put_line('');
2579: fii_util.put_line('Register extraction jobs');
2580: end if;
2581:
2582: l_curr_job_num := 1;
2583: FOR l_view_type_id in 3..4 LOOP
2583: FOR l_view_type_id in 3..4 LOOP
2584:
2585: g_section := 'Section 60';
2586: if g_debug_flag = 'Y' then
2587: fii_util.start_timer;
2588: end if;
2589:
2590:
2591: insert into fii_ar_revenue_id (
2603:
2604:
2605: l_count := nvl(SQL%ROWCOUNT,0);
2606: if g_debug_flag = 'Y' then
2607: fii_util.put_line('');
2608: fii_util.put_timestamp;
2609: fii_util.put_line('Registered '||
2610: ceil(l_count / l_batch_size(l_view_type_id))||
2611: ' job(s) for view type '||l_view_type_id);
2604:
2605: l_count := nvl(SQL%ROWCOUNT,0);
2606: if g_debug_flag = 'Y' then
2607: fii_util.put_line('');
2608: fii_util.put_timestamp;
2609: fii_util.put_line('Registered '||
2610: ceil(l_count / l_batch_size(l_view_type_id))||
2611: ' job(s) for view type '||l_view_type_id);
2612: fii_util.stop_timer;
2605: l_count := nvl(SQL%ROWCOUNT,0);
2606: if g_debug_flag = 'Y' then
2607: fii_util.put_line('');
2608: fii_util.put_timestamp;
2609: fii_util.put_line('Registered '||
2610: ceil(l_count / l_batch_size(l_view_type_id))||
2611: ' job(s) for view type '||l_view_type_id);
2612: fii_util.stop_timer;
2613: fii_util.print_timer('Duration');
2608: fii_util.put_timestamp;
2609: fii_util.put_line('Registered '||
2610: ceil(l_count / l_batch_size(l_view_type_id))||
2611: ' job(s) for view type '||l_view_type_id);
2612: fii_util.stop_timer;
2613: fii_util.print_timer('Duration');
2614: end if;
2615: commit;
2616:
2609: fii_util.put_line('Registered '||
2610: ceil(l_count / l_batch_size(l_view_type_id))||
2611: ' job(s) for view type '||l_view_type_id);
2612: fii_util.stop_timer;
2613: fii_util.print_timer('Duration');
2614: end if;
2615: commit;
2616:
2617: WHILE (l_count > 0) LOOP
2688: -- DEBUG
2689: -- return;
2690:
2691: if g_debug_flag = 'Y' then
2692: FII_UTIL.put_line('Calling Procedure: VERIFY_CCID_UP_TO_DATE');
2693: FII_UTIL.put_line('');
2694: end if;
2695:
2696: g_section := 'Section 10';
2689: -- return;
2690:
2691: if g_debug_flag = 'Y' then
2692: FII_UTIL.put_line('Calling Procedure: VERIFY_CCID_UP_TO_DATE');
2693: FII_UTIL.put_line('');
2694: end if;
2695:
2696: g_section := 'Section 10';
2697: -- if g_debug_flag = 'Y' then
2694: end if;
2695:
2696: g_section := 'Section 10';
2697: -- if g_debug_flag = 'Y' then
2698: -- FII_UTIL.put_line(g_section);
2699: -- end if;
2700:
2701: IF(FII_GL_CCID_C.NEW_CCID_IN_GL) THEN
2702: if g_debug_flag = 'Y' then
2699: -- end if;
2700:
2701: IF(FII_GL_CCID_C.NEW_CCID_IN_GL) THEN
2702: if g_debug_flag = 'Y' then
2703: FII_UTIL.put_line('CCID Dimension is not up to date, calling CCID Dimension update
2704: program');
2705: end if;
2706:
2707: l_dummy := FND_REQUEST.SET_MODE(TRUE);
2729: l_devstatus,
2730: l_message);
2731:
2732: if g_debug_flag = 'Y' then
2733: FII_UTIL.put_line('devphase : ' || l_devphase || ' devstatus: ' || l_devstatus);
2734: end if;
2735:
2736: IF (NVL(l_devphase='COMPLETE' AND l_devstatus='NORMAL', FALSE)) THEN
2737: if g_debug_flag = 'Y' then
2734: end if;
2735:
2736: IF (NVL(l_devphase='COMPLETE' AND l_devstatus='NORMAL', FALSE)) THEN
2737: if g_debug_flag = 'Y' then
2738: FII_UTIL.put_line('CCID Dimension populated successfully');
2739: end if;
2740: ELSE
2741: -- if g_debug_flag = 'Y' then
2742: FII_UTIL.put_line('CCID Dimension populated unsuccessfully');
2738: FII_UTIL.put_line('CCID Dimension populated successfully');
2739: end if;
2740: ELSE
2741: -- if g_debug_flag = 'Y' then
2742: FII_UTIL.put_line('CCID Dimension populated unsuccessfully');
2743: -- end if;
2744: raise G_CCID_FAILED;
2745: END IF;
2746:
2745: END IF;
2746:
2747: ELSE
2748: if g_debug_flag = 'Y' then
2749: FII_UTIL.put_line('CCID Dimension is up to date');
2750: FII_UTIL.put_line('');
2751: end if;
2752: END IF;
2753:
2746:
2747: ELSE
2748: if g_debug_flag = 'Y' then
2749: FII_UTIL.put_line('CCID Dimension is up to date');
2750: FII_UTIL.put_line('');
2751: end if;
2752: END IF;
2753:
2754: Exception
2753:
2754: Exception
2755: WHEN G_NO_CHILD_PROCESS THEN
2756: g_retcode := -1;
2757: FII_UTIL.put_line('
2758: ----------------------------
2759: Error in Procedure : VERIFY_CCID_UP_TO_DATE
2760: Phase: Submitting Child process to run CCID program');
2761: raise;
2760: Phase: Submitting Child process to run CCID program');
2761: raise;
2762: WHEN G_CCID_FAILED THEN
2763: g_retcode := -1;
2764: FII_UTIL.put_line('
2765: ----------------------------
2766: Error in Procedure : VERIFY_CCID_UP_TO_DATE
2767: Phase: Running CCID program');
2768: raise;
2767: Phase: Running CCID program');
2768: raise;
2769: WHEN OTHERS Then
2770: g_retcode := -1;
2771: FII_UTIL.put_line('
2772: ----------------------------
2773: Error in Procedure : VERIFY_CCID_UP_TO_DATE
2774: Section: ' || g_section || '
2775: Message: '||sqlerrm);
2850: from FII_AR_REVENUE_B
2851: where transaction_class <> ''ADJ'' ';
2852:
2853: if g_debug_flag = 'Y' then
2854: fii_util.put_line('');
2855: fii_util.put_line('Process step 1');
2856: fii_util.start_timer;
2857: fii_util.put_line('');
2858: fii_util.put_line(l_stmt);
2851: where transaction_class <> ''ADJ'' ';
2852:
2853: if g_debug_flag = 'Y' then
2854: fii_util.put_line('');
2855: fii_util.put_line('Process step 1');
2856: fii_util.start_timer;
2857: fii_util.put_line('');
2858: fii_util.put_line(l_stmt);
2859: end if;
2852:
2853: if g_debug_flag = 'Y' then
2854: fii_util.put_line('');
2855: fii_util.put_line('Process step 1');
2856: fii_util.start_timer;
2857: fii_util.put_line('');
2858: fii_util.put_line(l_stmt);
2859: end if;
2860: execute immediate l_stmt;
2853: if g_debug_flag = 'Y' then
2854: fii_util.put_line('');
2855: fii_util.put_line('Process step 1');
2856: fii_util.start_timer;
2857: fii_util.put_line('');
2858: fii_util.put_line(l_stmt);
2859: end if;
2860: execute immediate l_stmt;
2861: if g_debug_flag = 'Y' then
2854: fii_util.put_line('');
2855: fii_util.put_line('Process step 1');
2856: fii_util.start_timer;
2857: fii_util.put_line('');
2858: fii_util.put_line(l_stmt);
2859: end if;
2860: execute immediate l_stmt;
2861: if g_debug_flag = 'Y' then
2862: fii_util.put_line(' Processed '||SQL%ROWCOUNT||' rows');
2858: fii_util.put_line(l_stmt);
2859: end if;
2860: execute immediate l_stmt;
2861: if g_debug_flag = 'Y' then
2862: fii_util.put_line(' Processed '||SQL%ROWCOUNT||' rows');
2863: fii_util.stop_timer;
2864: fii_util.print_timer('Duration');
2865: end if;
2866: commit;
2859: end if;
2860: execute immediate l_stmt;
2861: if g_debug_flag = 'Y' then
2862: fii_util.put_line(' Processed '||SQL%ROWCOUNT||' rows');
2863: fii_util.stop_timer;
2864: fii_util.print_timer('Duration');
2865: end if;
2866: commit;
2867:
2860: execute immediate l_stmt;
2861: if g_debug_flag = 'Y' then
2862: fii_util.put_line(' Processed '||SQL%ROWCOUNT||' rows');
2863: fii_util.stop_timer;
2864: fii_util.print_timer('Duration');
2865: end if;
2866: commit;
2867:
2868: g_section := 'Section 30';
2877: where wh.invoice_id = trx.customer_trx_id (+)
2878: AND trx.customer_trx_id IS NULL ';
2879:
2880: if g_debug_flag = 'Y' then
2881: fii_util.put_line('');
2882: fii_util.put_line('Process step 2');
2883: fii_util.start_timer;
2884: fii_util.put_line('');
2885: fii_util.put_line(l_stmt);
2878: AND trx.customer_trx_id IS NULL ';
2879:
2880: if g_debug_flag = 'Y' then
2881: fii_util.put_line('');
2882: fii_util.put_line('Process step 2');
2883: fii_util.start_timer;
2884: fii_util.put_line('');
2885: fii_util.put_line(l_stmt);
2886: fii_util.put_line('');
2879:
2880: if g_debug_flag = 'Y' then
2881: fii_util.put_line('');
2882: fii_util.put_line('Process step 2');
2883: fii_util.start_timer;
2884: fii_util.put_line('');
2885: fii_util.put_line(l_stmt);
2886: fii_util.put_line('');
2887: end if;
2880: if g_debug_flag = 'Y' then
2881: fii_util.put_line('');
2882: fii_util.put_line('Process step 2');
2883: fii_util.start_timer;
2884: fii_util.put_line('');
2885: fii_util.put_line(l_stmt);
2886: fii_util.put_line('');
2887: end if;
2888: execute immediate l_stmt;
2881: fii_util.put_line('');
2882: fii_util.put_line('Process step 2');
2883: fii_util.start_timer;
2884: fii_util.put_line('');
2885: fii_util.put_line(l_stmt);
2886: fii_util.put_line('');
2887: end if;
2888: execute immediate l_stmt;
2889: if g_debug_flag = 'Y' then
2882: fii_util.put_line('Process step 2');
2883: fii_util.start_timer;
2884: fii_util.put_line('');
2885: fii_util.put_line(l_stmt);
2886: fii_util.put_line('');
2887: end if;
2888: execute immediate l_stmt;
2889: if g_debug_flag = 'Y' then
2890: fii_util.put_line(' Processed '||SQL%ROWCOUNT||' rows');
2886: fii_util.put_line('');
2887: end if;
2888: execute immediate l_stmt;
2889: if g_debug_flag = 'Y' then
2890: fii_util.put_line(' Processed '||SQL%ROWCOUNT||' rows');
2891: fii_util.stop_timer;
2892: fii_util.print_timer('Duration');
2893: end if;
2894: commit;
2887: end if;
2888: execute immediate l_stmt;
2889: if g_debug_flag = 'Y' then
2890: fii_util.put_line(' Processed '||SQL%ROWCOUNT||' rows');
2891: fii_util.stop_timer;
2892: fii_util.print_timer('Duration');
2893: end if;
2894: commit;
2895:
2888: execute immediate l_stmt;
2889: if g_debug_flag = 'Y' then
2890: fii_util.put_line(' Processed '||SQL%ROWCOUNT||' rows');
2891: fii_util.stop_timer;
2892: fii_util.print_timer('Duration');
2893: end if;
2894: commit;
2895:
2896:
2901: and invoice_id in (select invoice_id
2902: FROM '||g_fii_schema||'.fii_ar_revenue_sum_del2) ';
2903:
2904: if g_debug_flag = 'Y' then
2905: fii_util.put_line('');
2906: fii_util.put_line('Process step 3');
2907: fii_util.start_timer;
2908: fii_util.put_line('');
2909: fii_util.put_line(l_stmt);
2902: FROM '||g_fii_schema||'.fii_ar_revenue_sum_del2) ';
2903:
2904: if g_debug_flag = 'Y' then
2905: fii_util.put_line('');
2906: fii_util.put_line('Process step 3');
2907: fii_util.start_timer;
2908: fii_util.put_line('');
2909: fii_util.put_line(l_stmt);
2910: fii_util.put_line('');
2903:
2904: if g_debug_flag = 'Y' then
2905: fii_util.put_line('');
2906: fii_util.put_line('Process step 3');
2907: fii_util.start_timer;
2908: fii_util.put_line('');
2909: fii_util.put_line(l_stmt);
2910: fii_util.put_line('');
2911: end if;
2904: if g_debug_flag = 'Y' then
2905: fii_util.put_line('');
2906: fii_util.put_line('Process step 3');
2907: fii_util.start_timer;
2908: fii_util.put_line('');
2909: fii_util.put_line(l_stmt);
2910: fii_util.put_line('');
2911: end if;
2912: execute immediate l_stmt;
2905: fii_util.put_line('');
2906: fii_util.put_line('Process step 3');
2907: fii_util.start_timer;
2908: fii_util.put_line('');
2909: fii_util.put_line(l_stmt);
2910: fii_util.put_line('');
2911: end if;
2912: execute immediate l_stmt;
2913: l_count := SQL%ROWCOUNT;
2906: fii_util.put_line('Process step 3');
2907: fii_util.start_timer;
2908: fii_util.put_line('');
2909: fii_util.put_line(l_stmt);
2910: fii_util.put_line('');
2911: end if;
2912: execute immediate l_stmt;
2913: l_count := SQL%ROWCOUNT;
2914: if g_debug_flag = 'Y' then
2911: end if;
2912: execute immediate l_stmt;
2913: l_count := SQL%ROWCOUNT;
2914: if g_debug_flag = 'Y' then
2915: fii_util.put_line('Identified '||l_count||' invoices deleted in transaction system');
2916: fii_util.stop_timer;
2917: fii_util.print_timer('Duration');
2918: end if;
2919: commit;
2912: execute immediate l_stmt;
2913: l_count := SQL%ROWCOUNT;
2914: if g_debug_flag = 'Y' then
2915: fii_util.put_line('Identified '||l_count||' invoices deleted in transaction system');
2916: fii_util.stop_timer;
2917: fii_util.print_timer('Duration');
2918: end if;
2919: commit;
2920:
2913: l_count := SQL%ROWCOUNT;
2914: if g_debug_flag = 'Y' then
2915: fii_util.put_line('Identified '||l_count||' invoices deleted in transaction system');
2916: fii_util.stop_timer;
2917: fii_util.print_timer('Duration');
2918: end if;
2919: commit;
2920:
2921: drop_table('fii_ar_revenue_sum_del1');
2956: ------------------------------------------------------
2957: -- Set default directory in case if the profile option
2958: -- BIS_DEBUG_LOG_DIRECTORY is not set up
2959: ------------------------------------------------------
2960: l_dir:=FII_UTIL.get_utl_file_dir;
2961: -- DEBUG
2962: -- l_dir:='/sqlcom/log/olaptrw';
2963:
2964: ----------------------------------------------------------------
2961: -- DEBUG
2962: -- l_dir:='/sqlcom/log/olaptrw';
2963:
2964: ----------------------------------------------------------------
2965: -- fii_util.initialize will get profile options FII_DEBUG_MODE
2966: -- and BIS_DEBUG_LOG_DIRECTORY and set up the directory where
2967: -- the log files and output files are written to
2968: ----------------------------------------------------------------
2969: fii_util.initialize(p_object_name||'.log',p_object_name||'.out',l_dir, 'FII_AR_REVENUE_B_C_SUBWORKER');
2965: -- fii_util.initialize will get profile options FII_DEBUG_MODE
2966: -- and BIS_DEBUG_LOG_DIRECTORY and set up the directory where
2967: -- the log files and output files are written to
2968: ----------------------------------------------------------------
2969: fii_util.initialize(p_object_name||'.log',p_object_name||'.out',l_dir, 'FII_AR_REVENUE_B_C_SUBWORKER');
2970:
2971: g_fii_user_id := FND_GLOBAL.User_Id;
2972: g_fii_login_id := FND_GLOBAL.Login_Id;
2973:
3006: g_section := 'Section 10';
3007: drop_table('fii_ar_rev_accts_temp');
3008:
3009: if g_debug_flag = 'Y' then
3010: FII_UTIL.put_line('Creating temp table FII_AR_REV_ACCTS_TEMP');
3011: fii_util.start_timer;
3012: end if;
3013:
3014: g_section := 'Section 20';
3007: drop_table('fii_ar_rev_accts_temp');
3008:
3009: if g_debug_flag = 'Y' then
3010: FII_UTIL.put_line('Creating temp table FII_AR_REV_ACCTS_TEMP');
3011: fii_util.start_timer;
3012: end if;
3013:
3014: g_section := 'Section 20';
3015:
3044:
3045: EXECUTE IMMEDIATE l_stmt;
3046:
3047: if g_debug_flag = 'Y' then
3048: fii_util.stop_timer;
3049: fii_util.print_timer('Duration');
3050: end if;
3051:
3052: g_section := 'Section 30';
3045: EXECUTE IMMEDIATE l_stmt;
3046:
3047: if g_debug_flag = 'Y' then
3048: fii_util.stop_timer;
3049: fii_util.print_timer('Duration');
3050: end if;
3051:
3052: g_section := 'Section 30';
3053:
3113:
3114: l_section := 'Section 20';
3115:
3116: if g_debug_flag = 'Y' then
3117: fii_util.put_line(' ');
3118: fii_util.put_line('Loading data into staging table');
3119: fii_util.start_timer;
3120: fii_util.put_line('');
3121: end if;
3114: l_section := 'Section 20';
3115:
3116: if g_debug_flag = 'Y' then
3117: fii_util.put_line(' ');
3118: fii_util.put_line('Loading data into staging table');
3119: fii_util.start_timer;
3120: fii_util.put_line('');
3121: end if;
3122:
3115:
3116: if g_debug_flag = 'Y' then
3117: fii_util.put_line(' ');
3118: fii_util.put_line('Loading data into staging table');
3119: fii_util.start_timer;
3120: fii_util.put_line('');
3121: end if;
3122:
3123: -- Bug 4942753: Per Lester's suggestion, reordered the XLA tables and
3116: if g_debug_flag = 'Y' then
3117: fii_util.put_line(' ');
3118: fii_util.put_line('Loading data into staging table');
3119: fii_util.start_timer;
3120: fii_util.put_line('');
3121: end if;
3122:
3123: -- Bug 4942753: Per Lester's suggestion, reordered the XLA tables and
3124: -- add date filters on the transaction tables adj and ctlgd
3419: AND fslg.usage_code = g_usage_code
3420: AND NVL(ctt.post_to_gl,'Y') = 'Y';
3421:
3422: if g_debug_flag = 'Y' then
3423: fii_util.put_line('Inserted '||SQL%ROWCOUNT||' rows into staging table.');
3424: fii_util.stop_timer;
3425: fii_util.print_timer('Duration');
3426: end if;
3427:
3420: AND NVL(ctt.post_to_gl,'Y') = 'Y';
3421:
3422: if g_debug_flag = 'Y' then
3423: fii_util.put_line('Inserted '||SQL%ROWCOUNT||' rows into staging table.');
3424: fii_util.stop_timer;
3425: fii_util.print_timer('Duration');
3426: end if;
3427:
3428: commit;
3421:
3422: if g_debug_flag = 'Y' then
3423: fii_util.put_line('Inserted '||SQL%ROWCOUNT||' rows into staging table.');
3424: fii_util.stop_timer;
3425: fii_util.print_timer('Duration');
3426: end if;
3427:
3428: commit;
3429:
3451: l_global_prim_curr_code := bis_common_parameters.get_currency_code;
3452: l_global_sec_curr_code := bis_common_parameters.get_secondary_currency_code;
3453:
3454: if g_debug_flag = 'Y' then
3455: fii_util.put_line(' ');
3456: fii_util.put_line('Loading data into rates table');
3457: fii_util.start_timer;
3458: fii_util.put_line('');
3459: end if;
3452: l_global_sec_curr_code := bis_common_parameters.get_secondary_currency_code;
3453:
3454: if g_debug_flag = 'Y' then
3455: fii_util.put_line(' ');
3456: fii_util.put_line('Loading data into rates table');
3457: fii_util.start_timer;
3458: fii_util.put_line('');
3459: end if;
3460:
3453:
3454: if g_debug_flag = 'Y' then
3455: fii_util.put_line(' ');
3456: fii_util.put_line('Loading data into rates table');
3457: fii_util.start_timer;
3458: fii_util.put_line('');
3459: end if;
3460:
3461:
3454: if g_debug_flag = 'Y' then
3455: fii_util.put_line(' ');
3456: fii_util.put_line('Loading data into rates table');
3457: fii_util.start_timer;
3458: fii_util.put_line('');
3459: end if;
3460:
3461:
3462: insert into fii_ar_revenue_rates_temp
3475: from FII_AR_REVENUE_STG stg
3476: );
3477:
3478: if g_debug_flag = 'Y' then
3479: fii_util.put_line('Processed '||SQL%ROWCOUNT||' rows into rates table');
3480: fii_util.stop_timer;
3481: fii_util.print_timer('Duration');
3482: end if;
3483:
3476: );
3477:
3478: if g_debug_flag = 'Y' then
3479: fii_util.put_line('Processed '||SQL%ROWCOUNT||' rows into rates table');
3480: fii_util.stop_timer;
3481: fii_util.print_timer('Duration');
3482: end if;
3483:
3484: EXCEPTION
3477:
3478: if g_debug_flag = 'Y' then
3479: fii_util.put_line('Processed '||SQL%ROWCOUNT||' rows into rates table');
3480: fii_util.stop_timer;
3481: fii_util.print_timer('Duration');
3482: end if;
3483:
3484: EXCEPTION
3485:
3502:
3503: BEGIN
3504:
3505: if g_debug_flag = 'Y' then
3506: fii_util.put_line(' ');
3507: fii_util.put_line('Started loading data into base summary table');
3508: fii_util.start_timer;
3509: fii_util.put_line('');
3510: end if;
3503: BEGIN
3504:
3505: if g_debug_flag = 'Y' then
3506: fii_util.put_line(' ');
3507: fii_util.put_line('Started loading data into base summary table');
3508: fii_util.start_timer;
3509: fii_util.put_line('');
3510: end if;
3511:
3504:
3505: if g_debug_flag = 'Y' then
3506: fii_util.put_line(' ');
3507: fii_util.put_line('Started loading data into base summary table');
3508: fii_util.start_timer;
3509: fii_util.put_line('');
3510: end if;
3511:
3512: SELECT FII_AR_REVENUE_B_S.nextval INTO seq_id FROM dual;
3505: if g_debug_flag = 'Y' then
3506: fii_util.put_line(' ');
3507: fii_util.put_line('Started loading data into base summary table');
3508: fii_util.start_timer;
3509: fii_util.put_line('');
3510: end if;
3511:
3512: SELECT FII_AR_REVENUE_B_S.nextval INTO seq_id FROM dual;
3513:
3603: where TRUNC(stg.GL_DATE) = rates.trx_date
3604: and stg.functional_currency = rates.functional_currency;
3605:
3606: if g_debug_flag = 'Y' then
3607: fii_util.put_line('Inserted '||SQL%ROWCOUNT||' rows into base summary table.');
3608: fii_util.stop_timer;
3609: fii_util.print_timer('Duration');
3610: end if;
3611:
3604: and stg.functional_currency = rates.functional_currency;
3605:
3606: if g_debug_flag = 'Y' then
3607: fii_util.put_line('Inserted '||SQL%ROWCOUNT||' rows into base summary table.');
3608: fii_util.stop_timer;
3609: fii_util.print_timer('Duration');
3610: end if;
3611:
3612: commit;
3605:
3606: if g_debug_flag = 'Y' then
3607: fii_util.put_line('Inserted '||SQL%ROWCOUNT||' rows into base summary table.');
3608: fii_util.stop_timer;
3609: fii_util.print_timer('Duration');
3610: end if;
3611:
3612: commit;
3613:
3637: BEGIN
3638: -- g_section := 'section 10';
3639:
3640: -- if g_debug_flag = 'Y' then
3641: -- fii_util.put_line(g_section);
3642: -- end if;
3643:
3644: IF g_gl_from_date IS NULL THEN
3645: fii_util.put_line(' ');
3641: -- fii_util.put_line(g_section);
3642: -- end if;
3643:
3644: IF g_gl_from_date IS NULL THEN
3645: fii_util.put_line(' ');
3646: fii_util.put_line(
3647: 'Function CHECK_GLOBAL_VARIABLES: g_gl_from_date is NULL');
3648: RETURN TRUE;
3649: ELSIF g_gl_to_date IS NULL THEN
3642: -- end if;
3643:
3644: IF g_gl_from_date IS NULL THEN
3645: fii_util.put_line(' ');
3646: fii_util.put_line(
3647: 'Function CHECK_GLOBAL_VARIABLES: g_gl_from_date is NULL');
3648: RETURN TRUE;
3649: ELSIF g_gl_to_date IS NULL THEN
3650: fii_util.put_line(' ');
3646: fii_util.put_line(
3647: 'Function CHECK_GLOBAL_VARIABLES: g_gl_from_date is NULL');
3648: RETURN TRUE;
3649: ELSIF g_gl_to_date IS NULL THEN
3650: fii_util.put_line(' ');
3651: fii_util.put_line(
3652: 'Function CHECK_GLOBAL_VARIABLES: g_gl_to_date is NULL');
3653: RETURN TRUE;
3654: ELSIF (g_gl_to_date < g_gl_from_date) THEN
3647: 'Function CHECK_GLOBAL_VARIABLES: g_gl_from_date is NULL');
3648: RETURN TRUE;
3649: ELSIF g_gl_to_date IS NULL THEN
3650: fii_util.put_line(' ');
3651: fii_util.put_line(
3652: 'Function CHECK_GLOBAL_VARIABLES: g_gl_to_date is NULL');
3653: RETURN TRUE;
3654: ELSIF (g_gl_to_date < g_gl_from_date) THEN
3655: fii_util.put_line(' ');
3651: fii_util.put_line(
3652: 'Function CHECK_GLOBAL_VARIABLES: g_gl_to_date is NULL');
3653: RETURN TRUE;
3654: ELSIF (g_gl_to_date < g_gl_from_date) THEN
3655: fii_util.put_line(' ');
3656: fii_util.put_line(
3657: 'Function CHECK_GLOBAL_VARIABLES: g_gl_to_date < g_gl_from_date');
3658: RETURN TRUE;
3659: ELSIF g_program_type IS NULL THEN
3652: 'Function CHECK_GLOBAL_VARIABLES: g_gl_to_date is NULL');
3653: RETURN TRUE;
3654: ELSIF (g_gl_to_date < g_gl_from_date) THEN
3655: fii_util.put_line(' ');
3656: fii_util.put_line(
3657: 'Function CHECK_GLOBAL_VARIABLES: g_gl_to_date < g_gl_from_date');
3658: RETURN TRUE;
3659: ELSIF g_program_type IS NULL THEN
3660: fii_util.put_line(' ');
3656: fii_util.put_line(
3657: 'Function CHECK_GLOBAL_VARIABLES: g_gl_to_date < g_gl_from_date');
3658: RETURN TRUE;
3659: ELSIF g_program_type IS NULL THEN
3660: fii_util.put_line(' ');
3661: fii_util.put_line(
3662: 'Function CHECK_GLOBAL_VARIABLES: g_program_type is NULL');
3663: RETURN TRUE;
3664: ELSIF g_program_type = 'I' THEN
3657: 'Function CHECK_GLOBAL_VARIABLES: g_gl_to_date < g_gl_from_date');
3658: RETURN TRUE;
3659: ELSIF g_program_type IS NULL THEN
3660: fii_util.put_line(' ');
3661: fii_util.put_line(
3662: 'Function CHECK_GLOBAL_VARIABLES: g_program_type is NULL');
3663: RETURN TRUE;
3664: ELSIF g_program_type = 'I' THEN
3665: IF g_lud_from_date IS NULL THEN
3662: 'Function CHECK_GLOBAL_VARIABLES: g_program_type is NULL');
3663: RETURN TRUE;
3664: ELSIF g_program_type = 'I' THEN
3665: IF g_lud_from_date IS NULL THEN
3666: fii_util.put_line(' ');
3667: fii_util.put_line(
3668: 'Function CHECK_GLOBAL_VARIABLES: g_lud_from_date is NULL');
3669: RETURN TRUE;
3670: ELSIF g_lud_to_date IS NULL THEN
3663: RETURN TRUE;
3664: ELSIF g_program_type = 'I' THEN
3665: IF g_lud_from_date IS NULL THEN
3666: fii_util.put_line(' ');
3667: fii_util.put_line(
3668: 'Function CHECK_GLOBAL_VARIABLES: g_lud_from_date is NULL');
3669: RETURN TRUE;
3670: ELSIF g_lud_to_date IS NULL THEN
3671: fii_util.put_line(' ');
3667: fii_util.put_line(
3668: 'Function CHECK_GLOBAL_VARIABLES: g_lud_from_date is NULL');
3669: RETURN TRUE;
3670: ELSIF g_lud_to_date IS NULL THEN
3671: fii_util.put_line(' ');
3672: fii_util.put_line(
3673: 'Function CHECK_GLOBAL_VARIABLES: g_lud_to_date is NULL');
3674: RETURN TRUE;
3675: ELSE
3668: 'Function CHECK_GLOBAL_VARIABLES: g_lud_from_date is NULL');
3669: RETURN TRUE;
3670: ELSIF g_lud_to_date IS NULL THEN
3671: fii_util.put_line(' ');
3672: fii_util.put_line(
3673: 'Function CHECK_GLOBAL_VARIABLES: g_lud_to_date is NULL');
3674: RETURN TRUE;
3675: ELSE
3676: RETURN FALSE;
3808:
3809: g_program_type := p_program_type;
3810:
3811: IF l_dir is null THEN
3812: l_dir := FII_UTIL.get_utl_file_dir;
3813: END IF;
3814:
3815: ------------------------------------------------
3816: -- Initialize API will fetch the FII_DEBUG_MODE
3820: -- the log directory
3821: ------------------------------------------------
3822:
3823: IF g_program_type = 'I' THEN
3824: fii_util.initialize('FII_AR_REV_SUM.log','FII_AR_REV_SUM.out',l_dir, 'FII_AR_REVENUE_B_I');
3825: ELSIF g_program_type = 'L' THEN
3826: fii_util.initialize('FII_AR_REV_SUM.log','FII_AR_REV_SUM.out',l_dir, 'FII_AR_REVENUE_B_L');
3827: END IF;
3828:
3822:
3823: IF g_program_type = 'I' THEN
3824: fii_util.initialize('FII_AR_REV_SUM.log','FII_AR_REV_SUM.out',l_dir, 'FII_AR_REVENUE_B_I');
3825: ELSIF g_program_type = 'L' THEN
3826: fii_util.initialize('FII_AR_REV_SUM.log','FII_AR_REV_SUM.out',l_dir, 'FII_AR_REVENUE_B_L');
3827: END IF;
3828:
3829: IF g_program_type = 'I' THEN
3830: IF (NOT BIS_COLLECTION_UTILITIES.setup('FII_AR_REVENUE_B_I')) THEN
3839: END IF;
3840:
3841: EXECUTE IMMEDIATE 'ALTER SESSION ENABLE PARALLEL DML';
3842:
3843: fii_util.put_line('Altering session enable parallel DML after BIS_COLLECTION_UTILITIES.setup call, bug 4127183.');
3844:
3845: --------------------------------------------
3846: -- Initalization
3847: --------------------------------------------
3846: -- Initalization
3847: --------------------------------------------
3848: l_section := 'M-Section 12';
3849: IF g_debug_flag = 'Y' then
3850: fii_util.put_line(' ');
3851: fii_util.put_line('Calling INIT procedure');
3852: END IF;
3853: INIT;
3854:
3847: --------------------------------------------
3848: l_section := 'M-Section 12';
3849: IF g_debug_flag = 'Y' then
3850: fii_util.put_line(' ');
3851: fii_util.put_line('Calling INIT procedure');
3852: END IF;
3853: INIT;
3854:
3855: ----------------------------------------------------
3863: -----------------------------------------------------------
3864:
3865: BEGIN
3866: IF g_debug_flag = 'Y' then
3867: fii_util.put_line('');
3868: fii_util.put_line('Dropped temp table fii_ar_uni_con_rec, if it is there.');
3869: END IF;
3870:
3871:
3864:
3865: BEGIN
3866: IF g_debug_flag = 'Y' then
3867: fii_util.put_line('');
3868: fii_util.put_line('Dropped temp table fii_ar_uni_con_rec, if it is there.');
3869: END IF;
3870:
3871:
3872: EXECUTE IMMEDIATE 'DROP table fii_ar_uni_con_rec ';
3898: g_global_start_date := bis_common_parameters.get_global_start_date;
3899:
3900: IF p_program_type = 'L' THEN
3901: IF g_debug_flag = 'Y' then
3902: fii_util.put_line('');
3903: FII_UTIL.put_line('Running Initial Load, truncate staging and base summary tables.');
3904: END IF;
3905: -- DEBUG
3906: TRUNCATE_TABLE('FII_AR_REVENUE_STG');
3899:
3900: IF p_program_type = 'L' THEN
3901: IF g_debug_flag = 'Y' then
3902: fii_util.put_line('');
3903: FII_UTIL.put_line('Running Initial Load, truncate staging and base summary tables.');
3904: END IF;
3905: -- DEBUG
3906: TRUNCATE_TABLE('FII_AR_REVENUE_STG');
3907: TRUNCATE_TABLE('FII_AR_REVENUE_B');
3918: l_section := 'M-Section 16';
3919:
3920:
3921: IF g_debug_flag = 'Y' then
3922: fii_util.put_line('');
3923: FII_UTIL.put_line('Getting start date and end date for which records are to be collected in base summary table.');
3924: END IF;
3925:
3926: IF p_program_type = 'L' THEN
3919:
3920:
3921: IF g_debug_flag = 'Y' then
3922: fii_util.put_line('');
3923: FII_UTIL.put_line('Getting start date and end date for which records are to be collected in base summary table.');
3924: END IF;
3925:
3926: IF p_program_type = 'L' THEN
3927:
3937: tabname => 'FII_GLOBAL_START_DATES');
3938:
3939: EXECUTE IMMEDIATE 'ALTER SESSION ENABLE PARALLEL DML';
3940:
3941: fii_util.put_line('Altering session enable parallel DML after FND_STATS call, bug 4127183.');
3942:
3943: ELSE
3944:
3945: -----------------------------------------------------------------
4041: -- checking whether global variables : g_gl_from_date,
4042: -- g_gl_to_date, g_program_type are not null
4043: ----------------------------------------------------------
4044: IF g_debug_flag = 'Y' THEN
4045: fii_util.put_line(' ');
4046: fii_util.put_line('Checking that Global Variables are not null.');
4047: END IF;
4048:
4049: IF (CHECK_GLOBAL_VARIABLES) THEN
4042: -- g_gl_to_date, g_program_type are not null
4043: ----------------------------------------------------------
4044: IF g_debug_flag = 'Y' THEN
4045: fii_util.put_line(' ');
4046: fii_util.put_line('Checking that Global Variables are not null.');
4047: END IF;
4048:
4049: IF (CHECK_GLOBAL_VARIABLES) THEN
4050: -- if g_debug_flag = 'Y' then
4047: END IF;
4048:
4049: IF (CHECK_GLOBAL_VARIABLES) THEN
4050: -- if g_debug_flag = 'Y' then
4051: fii_util.put_line(' ');
4052: fii_util.put_line('Function CHECK_GLOBAL_VARIABLES: One of the global variables like g_gl_from_date, g_gl_to_date,
4053: g_program_type, g_lud_from_date, g_lud_to_date is NULL. Hence program terminated');
4054: -- end if;
4055: retcode := -1;
4048:
4049: IF (CHECK_GLOBAL_VARIABLES) THEN
4050: -- if g_debug_flag = 'Y' then
4051: fii_util.put_line(' ');
4052: fii_util.put_line('Function CHECK_GLOBAL_VARIABLES: One of the global variables like g_gl_from_date, g_gl_to_date,
4053: g_program_type, g_lud_from_date, g_lud_to_date is NULL. Hence program terminated');
4054: -- end if;
4055: retcode := -1;
4056: RETURN;
4059: -------------------------------------------------
4060: -- Print out useful date range information
4061: -------------------------------------------------
4062: if g_debug_flag = 'Y' then
4063: fii_util.put_line(' ');
4064: fii_util.put_line('This program will collect data with GL dates between '||
4065: to_char(g_gl_from_date,'MM/DD/YYYY HH24:MI:SS')||' and '||
4066: to_char(g_gl_to_date,'MM/DD/YYYY HH24:MI:SS'));
4067: IF p_program_type = 'I' THEN
4060: -- Print out useful date range information
4061: -------------------------------------------------
4062: if g_debug_flag = 'Y' then
4063: fii_util.put_line(' ');
4064: fii_util.put_line('This program will collect data with GL dates between '||
4065: to_char(g_gl_from_date,'MM/DD/YYYY HH24:MI:SS')||' and '||
4066: to_char(g_gl_to_date,'MM/DD/YYYY HH24:MI:SS'));
4067: IF p_program_type = 'I' THEN
4068: fii_util.put_line('This program will collect data with Last update date range between '||
4064: fii_util.put_line('This program will collect data with GL dates between '||
4065: to_char(g_gl_from_date,'MM/DD/YYYY HH24:MI:SS')||' and '||
4066: to_char(g_gl_to_date,'MM/DD/YYYY HH24:MI:SS'));
4067: IF p_program_type = 'I' THEN
4068: fii_util.put_line('This program will collect data with Last update date range between '||
4069: to_char(g_lud_from_date,'MM/DD/YYYY HH24:MI:SS')||' and '||
4070: to_char(g_lud_to_date,'MM/DD/YYYY HH24:MI:SS'));
4071: end if;
4072: end if;
4072: end if;
4073:
4074: l_section := 'Verifying if all AR periods have been upgraded for XLA';
4075: if g_debug_flag = 'Y' then
4076: FII_UTIL.put_line(l_section);
4077: end if;
4078:
4079: CHECK_XLA_CONVERSION_STATUS;
4080:
4111: fii_message.write_output(
4112: msg_name => 'FII_TRUNC_SUMMARY',
4113: token_num => 0);
4114:
4115: FII_UTIL.put_line('Function CHECK_SLG_ASSIGNMENT: Source Ledger Group setup has changed. Please run the Request Set in the Initial mode to repopulate the summaries.');
4116:
4117: END IF;
4118:
4119:
4127: fii_message.write_output(
4128: msg_name => 'FII_TRUNC_SUMMARY_PRD',
4129: token_num => 0);
4130:
4131: FII_UTIL.put_line('Function CHECK_PRODUCT_ASSIGNMENT: Product Assignment has changed. Please run the Request Set in the Initial mode to repopulate the summaries.');
4132:
4133: END IF;
4134:
4135: -- should fail the program if either slg or prd changed
4144: -----------------------------------------------------------
4145: l_section := 'M-Section 24';
4146:
4147: if g_debug_flag = 'Y' then
4148: fii_util.put_line('');
4149: fii_util.put_line('Calling function REV_ACCTS_CHANGED.');
4150: -- fii_util.put_line(l_stmt);
4151: end if;
4152:
4145: l_section := 'M-Section 24';
4146:
4147: if g_debug_flag = 'Y' then
4148: fii_util.put_line('');
4149: fii_util.put_line('Calling function REV_ACCTS_CHANGED.');
4150: -- fii_util.put_line(l_stmt);
4151: end if;
4152:
4153: g_rev_acct_changed := REV_ACCTS_CHANGED;
4146:
4147: if g_debug_flag = 'Y' then
4148: fii_util.put_line('');
4149: fii_util.put_line('Calling function REV_ACCTS_CHANGED.');
4150: -- fii_util.put_line(l_stmt);
4151: end if;
4152:
4153: g_rev_acct_changed := REV_ACCTS_CHANGED;
4154:
4158: -- Write out translated message to let user
4159: -- know they need to truncate the summary
4160: -- table first before loading
4161: --------------------------------------------
4162: fii_util.put_line('');
4163: fii_util.put_line('Function: REV_ACCTS_CHANGED.
4164: There has been a change in mapping for the Financial Categories Revenue
4165: and/or Deferred Revenue since the last Load / Update program.
4166: Please run the Request Set in the Initial mode to repopulate the summaries.');
4159: -- know they need to truncate the summary
4160: -- table first before loading
4161: --------------------------------------------
4162: fii_util.put_line('');
4163: fii_util.put_line('Function: REV_ACCTS_CHANGED.
4164: There has been a change in mapping for the Financial Categories Revenue
4165: and/or Deferred Revenue since the last Load / Update program.
4166: Please run the Request Set in the Initial mode to repopulate the summaries.');
4167:
4211: -- more new records, we will only process records already
4212: -- in the staging table
4213: ----------------------------------------------------------
4214: if g_debug_flag = 'Y' then
4215: FII_UTIL.put_line('');
4216: FII_UTIL.put_line('Determining if we need to resume from previous run');
4217: FII_UTIL.put_line('');
4218: end if;
4219:
4212: -- in the staging table
4213: ----------------------------------------------------------
4214: if g_debug_flag = 'Y' then
4215: FII_UTIL.put_line('');
4216: FII_UTIL.put_line('Determining if we need to resume from previous run');
4217: FII_UTIL.put_line('');
4218: end if;
4219:
4220: -- Bug 4942753: Change to return 1 if any row exists
4213: ----------------------------------------------------------
4214: if g_debug_flag = 'Y' then
4215: FII_UTIL.put_line('');
4216: FII_UTIL.put_line('Determining if we need to resume from previous run');
4217: FII_UTIL.put_line('');
4218: end if;
4219:
4220: -- Bug 4942753: Change to return 1 if any row exists
4221: BEGIN
4230:
4231: IF (l_stg_count > 0) THEN
4232: g_resume_flag := 'Y';
4233: if g_debug_flag = 'Y' then
4234: FII_UTIL.put_line('Resuming from previous run');
4235: FII_UTIL.put_line('');
4236: end if;
4237: ELSE
4238: g_resume_flag := 'N';
4231: IF (l_stg_count > 0) THEN
4232: g_resume_flag := 'Y';
4233: if g_debug_flag = 'Y' then
4234: FII_UTIL.put_line('Resuming from previous run');
4235: FII_UTIL.put_line('');
4236: end if;
4237: ELSE
4238: g_resume_flag := 'N';
4239: if g_debug_flag = 'Y' then
4236: end if;
4237: ELSE
4238: g_resume_flag := 'N';
4239: if g_debug_flag = 'Y' then
4240: FII_UTIL.put_line('Not resuming from previous run. Starting with fresh collection');
4241: FII_UTIL.put_line('');
4242: end if;
4243: END IF;
4244:
4237: ELSE
4238: g_resume_flag := 'N';
4239: if g_debug_flag = 'Y' then
4240: FII_UTIL.put_line('Not resuming from previous run. Starting with fresh collection');
4241: FII_UTIL.put_line('');
4242: end if;
4243: END IF;
4244:
4245:
4302: tabname => 'FII_AR_REVENUE_STG');
4303:
4304: EXECUTE IMMEDIATE 'ALTER SESSION ENABLE PARALLEL DML';
4305:
4306: fii_util.put_line('Altering session enable parallel DML after FND_STATS call, bug 4127183.');
4307:
4308: -- Bug 4942753: Changed to return 1 if any row exists
4309: BEGIN
4310: /*
4337: -- has been assigned type DR.
4338: if l_count > 0 then
4339: l_count := IDENTIFY_CHANGE_INIT( 'AR INV' );
4340: if g_debug_flag = 'Y' then
4341: fii_util.put_line('Function IDENTIFY_CHANGE_INIT identified '||l_count||' rows');
4342: end if;
4343:
4344: l_count := AR_STG_BF( 4, 1 );
4345: if g_debug_flag = 'Y' then
4342: end if;
4343:
4344: l_count := AR_STG_BF( 4, 1 );
4345: if g_debug_flag = 'Y' then
4346: fii_util.put_line('Function AR_STG_BF collected '||l_count||' rows into staging table.');
4347: end if;
4348: end if;
4349:
4350: AR_RATES;
4351:
4352: ELSE
4353:
4354: if g_debug_flag = 'Y' then
4355: fii_util.put_line(' ');
4356: fii_util.put_timestamp;
4357: end if;
4358: REGISTER_PREP_JOBS;
4359: COMMIT;
4352: ELSE
4353:
4354: if g_debug_flag = 'Y' then
4355: fii_util.put_line(' ');
4356: fii_util.put_timestamp;
4357: end if;
4358: REGISTER_PREP_JOBS;
4359: COMMIT;
4360:
4362: -- Launch workers
4363: -- ------------------------------------------
4364: l_section := 'M-Section 40';
4365: if g_debug_flag = 'Y' then
4366: fii_util.put_line(' ');
4367: fii_util.put_timestamp;
4368: fii_util.put_line('Launching '||p_no_worker||' workers');
4369: fii_util.start_timer;
4370: end if;
4363: -- ------------------------------------------
4364: l_section := 'M-Section 40';
4365: if g_debug_flag = 'Y' then
4366: fii_util.put_line(' ');
4367: fii_util.put_timestamp;
4368: fii_util.put_line('Launching '||p_no_worker||' workers');
4369: fii_util.start_timer;
4370: end if;
4371:
4364: l_section := 'M-Section 40';
4365: if g_debug_flag = 'Y' then
4366: fii_util.put_line(' ');
4367: fii_util.put_timestamp;
4368: fii_util.put_line('Launching '||p_no_worker||' workers');
4369: fii_util.start_timer;
4370: end if;
4371:
4372: FOR i IN 1..p_no_worker LOOP
4365: if g_debug_flag = 'Y' then
4366: fii_util.put_line(' ');
4367: fii_util.put_timestamp;
4368: fii_util.put_line('Launching '||p_no_worker||' workers');
4369: fii_util.start_timer;
4370: end if;
4371:
4372: FOR i IN 1..p_no_worker LOOP
4373: l_worker(i) := LAUNCH_WORKER(i);
4371:
4372: FOR i IN 1..p_no_worker LOOP
4373: l_worker(i) := LAUNCH_WORKER(i);
4374: if g_debug_flag = 'Y' then
4375: fii_util.put_line(' Worker '||i||' request id: '||
4376: l_worker(i));
4377: end if;
4378: END LOOP;
4379:
4408: l_tot_cnt
4409: FROM FII_AR_REVENUE_JOBS;
4410:
4411: if g_debug_flag = 'Y' then
4412: fii_util.put_line('Job status - Unassigned:'||l_unassigned_cnt||
4413: ' In Process:'||l_wip_cnt||
4414: ' Completed:'||l_completed_cnt||
4415: ' Failed:'||l_failed_cnt);
4416: end if;
4464:
4465: END LOOP;
4466:
4467: if g_debug_flag = 'Y' then
4468: fii_util.stop_timer;
4469: fii_util.print_timer('Duration');
4470: end if;
4471: END;
4472:
4465: END LOOP;
4466:
4467: if g_debug_flag = 'Y' then
4468: fii_util.stop_timer;
4469: fii_util.print_timer('Duration');
4470: end if;
4471: END;
4472:
4473: END IF;
4487: ----------------------------------------------------------
4488: g_truncate_staging := 'N';
4489:
4490: if g_debug_flag = 'Y' then
4491: fii_util.put_line('Program running in resume mode. Fixing missing exchange rates in staging table');
4492: fii_util.put_line('');
4493:
4494: FII_UTIL.start_timer;
4495: end if;
4488: g_truncate_staging := 'N';
4489:
4490: if g_debug_flag = 'Y' then
4491: fii_util.put_line('Program running in resume mode. Fixing missing exchange rates in staging table');
4492: fii_util.put_line('');
4493:
4494: FII_UTIL.start_timer;
4495: end if;
4496:
4490: if g_debug_flag = 'Y' then
4491: fii_util.put_line('Program running in resume mode. Fixing missing exchange rates in staging table');
4492: fii_util.put_line('');
4493:
4494: FII_UTIL.start_timer;
4495: end if;
4496:
4497: Update FII_AR_REVENUE_STG stg
4498: SET prim_conversion_rate =
4499: fii_currency.get_global_rate_primary(stg.functional_currency, least(stg.exchange_date, sysdate))
4500: WHERE stg.prim_conversion_rate < 0;
4501: -- commit;
4502: if g_debug_flag = 'Y' then
4503: FII_UTIL.put_line('Updated ' || SQL%ROWCOUNT || ' records for primary currency rates in staging table');
4504: FII_UTIL.stop_timer;
4505: FII_UTIL.print_timer('Duration');
4506:
4507: FII_UTIL.start_timer;
4500: WHERE stg.prim_conversion_rate < 0;
4501: -- commit;
4502: if g_debug_flag = 'Y' then
4503: FII_UTIL.put_line('Updated ' || SQL%ROWCOUNT || ' records for primary currency rates in staging table');
4504: FII_UTIL.stop_timer;
4505: FII_UTIL.print_timer('Duration');
4506:
4507: FII_UTIL.start_timer;
4508: end if;
4501: -- commit;
4502: if g_debug_flag = 'Y' then
4503: FII_UTIL.put_line('Updated ' || SQL%ROWCOUNT || ' records for primary currency rates in staging table');
4504: FII_UTIL.stop_timer;
4505: FII_UTIL.print_timer('Duration');
4506:
4507: FII_UTIL.start_timer;
4508: end if;
4509: commit;
4503: FII_UTIL.put_line('Updated ' || SQL%ROWCOUNT || ' records for primary currency rates in staging table');
4504: FII_UTIL.stop_timer;
4505: FII_UTIL.print_timer('Duration');
4506:
4507: FII_UTIL.start_timer;
4508: end if;
4509: commit;
4510:
4511: Update FII_AR_REVENUE_STG stg
4513: fii_currency.get_global_rate_secondary(stg.functional_currency, least(stg.exchange_date, sysdate))
4514: WHERE stg.sec_conversion_rate < 0;
4515: -- commit;
4516: if g_debug_flag = 'Y' then
4517: FII_UTIL.put_line('Updated ' || SQL%ROWCOUNT || ' records for secondary currency rates in staging table');
4518: FII_UTIL.stop_timer;
4519: FII_UTIL.print_timer('Duration');
4520: end if;
4521: commit;
4514: WHERE stg.sec_conversion_rate < 0;
4515: -- commit;
4516: if g_debug_flag = 'Y' then
4517: FII_UTIL.put_line('Updated ' || SQL%ROWCOUNT || ' records for secondary currency rates in staging table');
4518: FII_UTIL.stop_timer;
4519: FII_UTIL.print_timer('Duration');
4520: end if;
4521: commit;
4522:
4515: -- commit;
4516: if g_debug_flag = 'Y' then
4517: FII_UTIL.put_line('Updated ' || SQL%ROWCOUNT || ' records for secondary currency rates in staging table');
4518: FII_UTIL.stop_timer;
4519: FII_UTIL.print_timer('Duration');
4520: end if;
4521: commit;
4522:
4523:
4523:
4524: END IF; -- IF (g_resume_flag = 'N')
4525:
4526: if g_debug_flag = 'Y' then
4527: fii_util.put_line(' ');
4528: fii_util.put_line('Running function VERIFY_MISSING_RATES.');
4529: end if;
4530:
4531: IF g_program_type = 'I' then
4524: END IF; -- IF (g_resume_flag = 'N')
4525:
4526: if g_debug_flag = 'Y' then
4527: fii_util.put_line(' ');
4528: fii_util.put_line('Running function VERIFY_MISSING_RATES.');
4529: end if;
4530:
4531: IF g_program_type = 'I' then
4532: if g_debug_flag = 'Y' then
4529: end if;
4530:
4531: IF g_program_type = 'I' then
4532: if g_debug_flag = 'Y' then
4533: fii_util.put_line(' ');
4534: fii_util.put_timestamp;
4535: fii_util.put_line('Anayzing staging table before checking missing rates.');
4536: end if;
4537:
4530:
4531: IF g_program_type = 'I' then
4532: if g_debug_flag = 'Y' then
4533: fii_util.put_line(' ');
4534: fii_util.put_timestamp;
4535: fii_util.put_line('Anayzing staging table before checking missing rates.');
4536: end if;
4537:
4538: FND_STATS.gather_table_stats (ownname => g_fii_schema,
4531: IF g_program_type = 'I' then
4532: if g_debug_flag = 'Y' then
4533: fii_util.put_line(' ');
4534: fii_util.put_timestamp;
4535: fii_util.put_line('Anayzing staging table before checking missing rates.');
4536: end if;
4537:
4538: FND_STATS.gather_table_stats (ownname => g_fii_schema,
4539: tabname => 'FII_AR_REVENUE_STG');
4539: tabname => 'FII_AR_REVENUE_STG');
4540:
4541: EXECUTE IMMEDIATE 'ALTER SESSION ENABLE PARALLEL DML';
4542:
4543: fii_util.put_line('Altering session enable parallel DML after FND_STATS call, bug 4127183.');
4544:
4545:
4546: END IF;
4547:
4575: -- into base. We only execute it when running
4576: -- in 'I' mode.
4577: ---------------------------------------------
4578: if g_debug_flag = 'Y' then
4579: fii_util.put_line(' ');
4580: fii_util.put_line('Running procedure to populate base summary table');
4581: end if;
4582: IF (g_program_type = 'L') THEN
4583: AR_SUMMARY (p_parallel_query);
4576: -- in 'I' mode.
4577: ---------------------------------------------
4578: if g_debug_flag = 'Y' then
4579: fii_util.put_line(' ');
4580: fii_util.put_line('Running procedure to populate base summary table');
4581: end if;
4582: IF (g_program_type = 'L') THEN
4583: AR_SUMMARY (p_parallel_query);
4584: ELSE
4583: AR_SUMMARY (p_parallel_query);
4584: ELSE
4585: -- to check for duplicate records in staging table during Update prog.
4586: if g_debug_flag = 'Y' then
4587: fii_util.put_line(' ');
4588: fii_util.put_line('Checking for duplicate records in staging table.');
4589: end if;
4590: SELECT count(*) INTO l_dup
4591: from (SELECT 1
4584: ELSE
4585: -- to check for duplicate records in staging table during Update prog.
4586: if g_debug_flag = 'Y' then
4587: fii_util.put_line(' ');
4588: fii_util.put_line('Checking for duplicate records in staging table.');
4589: end if;
4590: SELECT count(*) INTO l_dup
4591: from (SELECT 1
4592: FROM FII_AR_REVENUE_STG b2
4612: -- after load is completed, insert into table fii_ar_rev_accts
4613: -- new accounts assigned to 'Revenue'.
4614: ---------------------------------------------------------------
4615: if g_debug_flag = 'Y' then
4616: fii_util.put_line(' ');
4617: fii_util.put_line('Inserting new revenue accounts in fii_ar_rev_accts ');
4618: fii_util.start_timer;
4619: end if;
4620:
4613: -- new accounts assigned to 'Revenue'.
4614: ---------------------------------------------------------------
4615: if g_debug_flag = 'Y' then
4616: fii_util.put_line(' ');
4617: fii_util.put_line('Inserting new revenue accounts in fii_ar_rev_accts ');
4618: fii_util.start_timer;
4619: end if;
4620:
4621: /*
4614: ---------------------------------------------------------------
4615: if g_debug_flag = 'Y' then
4616: fii_util.put_line(' ');
4617: fii_util.put_line('Inserting new revenue accounts in fii_ar_rev_accts ');
4618: fii_util.start_timer;
4619: end if;
4620:
4621: /*
4622: IF (p_program_type = 'L') THEN
4641: AND temp.cur_fin_cat_type_code = fra.fin_cat_type_code(+)
4642: AND fra.rev_acct_id IS NULL ';
4643:
4644: if g_debug_flag = 'Y' then
4645: fii_util.put_line('');
4646: -- fii_util.put_line(l_stmt);
4647: end if;
4648:
4649: EXECUTE IMMEDIATE l_stmt;
4642: AND fra.rev_acct_id IS NULL ';
4643:
4644: if g_debug_flag = 'Y' then
4645: fii_util.put_line('');
4646: -- fii_util.put_line(l_stmt);
4647: end if;
4648:
4649: EXECUTE IMMEDIATE l_stmt;
4650:
4673: AND XAD.Assignment_Code = XACA.Assignment_Code)
4674: SELECT * FROM ACCNT_CLASS;
4675:
4676: if g_debug_flag = 'Y' then
4677: fii_util.put_line(' ');
4678: fii_util.stop_timer;
4679: fii_util.print_timer('Duration');
4680: end if;
4681:
4674: SELECT * FROM ACCNT_CLASS;
4675:
4676: if g_debug_flag = 'Y' then
4677: fii_util.put_line(' ');
4678: fii_util.stop_timer;
4679: fii_util.print_timer('Duration');
4680: end if;
4681:
4682: COMMIT;
4675:
4676: if g_debug_flag = 'Y' then
4677: fii_util.put_line(' ');
4678: fii_util.stop_timer;
4679: fii_util.print_timer('Duration');
4680: end if;
4681:
4682: COMMIT;
4683:
4688: FROM fii_ar_revenue_jobs
4689: WHERE function = 'POPULATE_STG';
4690:
4691: if g_debug_flag = 'Y' then
4692: fii_util.put_line(' ');
4693: fii_util.put_line('Processed '||l_count||' row(s) into staging table.');
4694: end if;
4695: END IF;
4696:
4689: WHERE function = 'POPULATE_STG';
4690:
4691: if g_debug_flag = 'Y' then
4692: fii_util.put_line(' ');
4693: fii_util.put_line('Processed '||l_count||' row(s) into staging table.');
4694: end if;
4695: END IF;
4696:
4697: /* SELECT sum(rows_processed)
4698: INTO l_count
4699: FROM fii_ar_revenue_jobs
4700: WHERE function = 'DETECT_DELETED_INV';
4701: if g_debug_flag = 'Y' then
4702: fii_util.put_line('Found '||l_count||' invoice(s) deleted');
4703: end if;
4704: */
4705: COMMIT;
4706:
4757: EXCEPTION
4758: WHEN G_PROCEDURE_FAILURE THEN
4759: Errbuf := g_errbuf;
4760: Retcode := g_retcode;
4761: fii_util.put_line(Errbuf);
4762:
4763: --------------------------------
4764: -- Terminate the child processes
4765: --------------------------------
4777: ---------------------------------
4778: Error in Procedure: MAIN
4779: Section: '||l_section||'
4780: Message: '||sqlerrm;
4781: fii_util.put_line(Errbuf);
4782:
4783: --------------------------------
4784: -- Terminate the child processes
4785: --------------------------------
4829: -- -----------------------------------------------
4830: CHILD_SETUP('FII_AR_REVENUE_B_C_SUBWORKER'||p_worker_no);
4831:
4832: if g_debug_flag = 'Y' then
4833: fii_util.put_line(' ');
4834: fii_util.put_timestamp;
4835: fii_util.put_line('Worker '||p_worker_no||' Starting');
4836: end if;
4837:
4830: CHILD_SETUP('FII_AR_REVENUE_B_C_SUBWORKER'||p_worker_no);
4831:
4832: if g_debug_flag = 'Y' then
4833: fii_util.put_line(' ');
4834: fii_util.put_timestamp;
4835: fii_util.put_line('Worker '||p_worker_no||' Starting');
4836: end if;
4837:
4838: -- ------------------------------------------
4831:
4832: if g_debug_flag = 'Y' then
4833: fii_util.put_line(' ');
4834: fii_util.put_timestamp;
4835: fii_util.put_line('Worker '||p_worker_no||' Starting');
4836: end if;
4837:
4838: -- ------------------------------------------
4839: -- Initalization
4839: -- Initalization
4840: -- ------------------------------------------
4841: l_section := 'W-Section 20';
4842: if g_debug_flag = 'Y' then
4843: fii_util.put_line(' ');
4844: fii_util.put_line('Calling INIT procedure for worker.');
4845: end if;
4846: INIT;
4847:
4840: -- ------------------------------------------
4841: l_section := 'W-Section 20';
4842: if g_debug_flag = 'Y' then
4843: fii_util.put_line(' ');
4844: fii_util.put_line('Calling INIT procedure for worker.');
4845: end if;
4846: INIT;
4847:
4848: g_worker_num := p_worker_no;
4873:
4874: l_section := 'W-Section 40';
4875: IF (l_failed_cnt > 0) THEN
4876: -- if g_debug_flag = 'Y' then
4877: fii_util.put_line('');
4878: fii_util.put_line('Another worker have errored out. Stop processing.');
4879: -- end if;
4880: EXIT;
4881: END IF;
4874: l_section := 'W-Section 40';
4875: IF (l_failed_cnt > 0) THEN
4876: -- if g_debug_flag = 'Y' then
4877: fii_util.put_line('');
4878: fii_util.put_line('Another worker have errored out. Stop processing.');
4879: -- end if;
4880: EXIT;
4881: END IF;
4882:
4882:
4883: IF (l_curr_phase = LAST_PHASE AND
4884: l_unassigned_cnt = 0) THEN
4885: if g_debug_flag = 'Y' then
4886: fii_util.put_line('');
4887: fii_util.put_line('No more jobs left. Terminating.');
4888: end if;
4889: EXIT;
4890: END IF;
4883: IF (l_curr_phase = LAST_PHASE AND
4884: l_unassigned_cnt = 0) THEN
4885: if g_debug_flag = 'Y' then
4886: fii_util.put_line('');
4887: fii_util.put_line('No more jobs left. Terminating.');
4888: end if;
4889: EXIT;
4890: END IF;
4891:
4948:
4949:
4950: l_section := 'W-Section 70';
4951: if g_debug_flag = 'Y' then
4952: fii_util.put_line('');
4953: fii_util.put_line('');
4954: fii_util.put_line('------------------------------------------------------');
4955: fii_util.put_timestamp;
4956: end if;
4949:
4950: l_section := 'W-Section 70';
4951: if g_debug_flag = 'Y' then
4952: fii_util.put_line('');
4953: fii_util.put_line('');
4954: fii_util.put_line('------------------------------------------------------');
4955: fii_util.put_timestamp;
4956: end if;
4957:
4950: l_section := 'W-Section 70';
4951: if g_debug_flag = 'Y' then
4952: fii_util.put_line('');
4953: fii_util.put_line('');
4954: fii_util.put_line('------------------------------------------------------');
4955: fii_util.put_timestamp;
4956: end if;
4957:
4958: IF (l_function = 'IDENTIFY_CHANGE') THEN
4951: if g_debug_flag = 'Y' then
4952: fii_util.put_line('');
4953: fii_util.put_line('');
4954: fii_util.put_line('------------------------------------------------------');
4955: fii_util.put_timestamp;
4956: end if;
4957:
4958: IF (l_function = 'IDENTIFY_CHANGE') THEN
4959:
4957:
4958: IF (l_function = 'IDENTIFY_CHANGE') THEN
4959:
4960: if g_debug_flag = 'Y' then
4961: fii_util.put_line('Job1: Calling IDENTIFY_CHANGE function for Revenue and Deferred Revenue records.');
4962: fii_util.put_line('Parameters: type='||l_char_parameter1);
4963: fii_util.put_line(' gl from date='||
4964: to_char(g_gl_from_date,'YYYY/MM/DD HH24:MI:SS'));
4965: fii_util.put_line(' gl to date='||
4958: IF (l_function = 'IDENTIFY_CHANGE') THEN
4959:
4960: if g_debug_flag = 'Y' then
4961: fii_util.put_line('Job1: Calling IDENTIFY_CHANGE function for Revenue and Deferred Revenue records.');
4962: fii_util.put_line('Parameters: type='||l_char_parameter1);
4963: fii_util.put_line(' gl from date='||
4964: to_char(g_gl_from_date,'YYYY/MM/DD HH24:MI:SS'));
4965: fii_util.put_line(' gl to date='||
4966: to_char(g_gl_to_date,'YYYY/MM/DD HH24:MI:SS'));
4959:
4960: if g_debug_flag = 'Y' then
4961: fii_util.put_line('Job1: Calling IDENTIFY_CHANGE function for Revenue and Deferred Revenue records.');
4962: fii_util.put_line('Parameters: type='||l_char_parameter1);
4963: fii_util.put_line(' gl from date='||
4964: to_char(g_gl_from_date,'YYYY/MM/DD HH24:MI:SS'));
4965: fii_util.put_line(' gl to date='||
4966: to_char(g_gl_to_date,'YYYY/MM/DD HH24:MI:SS'));
4967: fii_util.put_line(' last updated from date='||
4961: fii_util.put_line('Job1: Calling IDENTIFY_CHANGE function for Revenue and Deferred Revenue records.');
4962: fii_util.put_line('Parameters: type='||l_char_parameter1);
4963: fii_util.put_line(' gl from date='||
4964: to_char(g_gl_from_date,'YYYY/MM/DD HH24:MI:SS'));
4965: fii_util.put_line(' gl to date='||
4966: to_char(g_gl_to_date,'YYYY/MM/DD HH24:MI:SS'));
4967: fii_util.put_line(' last updated from date='||
4968: to_char(g_lud_from_date,'YYYY/MM/DD HH24:MI:SS'));
4969: fii_util.put_line(' last updated to date='||
4963: fii_util.put_line(' gl from date='||
4964: to_char(g_gl_from_date,'YYYY/MM/DD HH24:MI:SS'));
4965: fii_util.put_line(' gl to date='||
4966: to_char(g_gl_to_date,'YYYY/MM/DD HH24:MI:SS'));
4967: fii_util.put_line(' last updated from date='||
4968: to_char(g_lud_from_date,'YYYY/MM/DD HH24:MI:SS'));
4969: fii_util.put_line(' last updated to date='||
4970: to_char(g_lud_to_date,'YYYY/MM/DD HH24:MI:SS'));
4971: end if;
4965: fii_util.put_line(' gl to date='||
4966: to_char(g_gl_to_date,'YYYY/MM/DD HH24:MI:SS'));
4967: fii_util.put_line(' last updated from date='||
4968: to_char(g_lud_from_date,'YYYY/MM/DD HH24:MI:SS'));
4969: fii_util.put_line(' last updated to date='||
4970: to_char(g_lud_to_date,'YYYY/MM/DD HH24:MI:SS'));
4971: end if;
4972:
4973: l_count := IDENTIFY_CHANGE(l_char_parameter1);
4971: end if;
4972:
4973: l_count := IDENTIFY_CHANGE(l_char_parameter1);
4974: if g_debug_flag = 'Y' then
4975: fii_util.put_line('Identified '||l_count||' rows');
4976: end if;
4977: ELSIF (l_function = 'VERIFY_CCID_UP_TO_DATE') THEN
4978: if g_debug_flag = 'Y' then
4979: fii_util.put_line('Job2: Calling VERIFY_CCID_UP_TO_DATE procedure.');
4975: fii_util.put_line('Identified '||l_count||' rows');
4976: end if;
4977: ELSIF (l_function = 'VERIFY_CCID_UP_TO_DATE') THEN
4978: if g_debug_flag = 'Y' then
4979: fii_util.put_line('Job2: Calling VERIFY_CCID_UP_TO_DATE procedure.');
4980: end if;
4981: VERIFY_CCID_UP_TO_DATE;
4982: ELSIF (l_function = 'REGISTER_EXTRACT_JOBS') THEN
4983: if g_debug_flag = 'Y' then
4980: end if;
4981: VERIFY_CCID_UP_TO_DATE;
4982: ELSIF (l_function = 'REGISTER_EXTRACT_JOBS') THEN
4983: if g_debug_flag = 'Y' then
4984: fii_util.put_line('Job3: Calling REGISTER_EXTRACT_JOBS procedure.');
4985: end if;
4986: REGISTER_EXTRACT_JOBS;
4987: -- ELSIF (l_function = 'DETECT_DELETED_INV') THEN
4988: -- if g_debug_flag = 'Y' then
4985: end if;
4986: REGISTER_EXTRACT_JOBS;
4987: -- ELSIF (l_function = 'DETECT_DELETED_INV') THEN
4988: -- if g_debug_flag = 'Y' then
4989: -- fii_util.put_line('Job: Detect deleted invoices');
4990: -- end if;
4991: -- l_count := DETECT_DELETED_INV;
4992: ELSIF (l_function = 'POPULATE_STG') THEN
4993: if g_debug_flag = 'Y' then
4990: -- end if;
4991: -- l_count := DETECT_DELETED_INV;
4992: ELSIF (l_function = 'POPULATE_STG') THEN
4993: if g_debug_flag = 'Y' then
4994: fii_util.put_line('Job4: Calling POPULATE_STG function');
4995: fii_util.put_line('Parameters: view type='||l_num_parameter1);
4996: fii_util.put_line(' jobs sequence='||l_num_parameter2);
4997: end if;
4998: l_count := POPULATE_STG(l_num_parameter1,l_num_parameter2);
4991: -- l_count := DETECT_DELETED_INV;
4992: ELSIF (l_function = 'POPULATE_STG') THEN
4993: if g_debug_flag = 'Y' then
4994: fii_util.put_line('Job4: Calling POPULATE_STG function');
4995: fii_util.put_line('Parameters: view type='||l_num_parameter1);
4996: fii_util.put_line(' jobs sequence='||l_num_parameter2);
4997: end if;
4998: l_count := POPULATE_STG(l_num_parameter1,l_num_parameter2);
4999: -- FND_STATS.gather_table_stats
4992: ELSIF (l_function = 'POPULATE_STG') THEN
4993: if g_debug_flag = 'Y' then
4994: fii_util.put_line('Job4: Calling POPULATE_STG function');
4995: fii_util.put_line('Parameters: view type='||l_num_parameter1);
4996: fii_util.put_line(' jobs sequence='||l_num_parameter2);
4997: end if;
4998: l_count := POPULATE_STG(l_num_parameter1,l_num_parameter2);
4999: -- FND_STATS.gather_table_stats
5000: -- (ownname => g_fii_schema,
5038: -- Sleep 60 Seconds
5039: -- -----------------------
5040: l_section := 'W-Section 90';
5041: if g_debug_flag = 'Y' then
5042: fii_util.put_line('');
5043: fii_util.put_line('');
5044: fii_util.put_line('------------------------------------------------------');
5045: fii_util.put_timestamp;
5046: end if;
5039: -- -----------------------
5040: l_section := 'W-Section 90';
5041: if g_debug_flag = 'Y' then
5042: fii_util.put_line('');
5043: fii_util.put_line('');
5044: fii_util.put_line('------------------------------------------------------');
5045: fii_util.put_timestamp;
5046: end if;
5047: dbms_lock.sleep(60);
5040: l_section := 'W-Section 90';
5041: if g_debug_flag = 'Y' then
5042: fii_util.put_line('');
5043: fii_util.put_line('');
5044: fii_util.put_line('------------------------------------------------------');
5045: fii_util.put_timestamp;
5046: end if;
5047: dbms_lock.sleep(60);
5048:
5041: if g_debug_flag = 'Y' then
5042: fii_util.put_line('');
5043: fii_util.put_line('');
5044: fii_util.put_line('------------------------------------------------------');
5045: fii_util.put_timestamp;
5046: end if;
5047: dbms_lock.sleep(60);
5048:
5049: ELSIF (l_curr_comp_cnt = l_curr_tot_cnt) THEN
5060:
5061: END LOOP;
5062:
5063: if g_debug_flag = 'Y' then
5064: fii_util.put_timestamp;
5065: end if;
5066:
5067: EXCEPTION
5068: WHEN G_PROCEDURE_FAILURE THEN
5067: EXCEPTION
5068: WHEN G_PROCEDURE_FAILURE THEN
5069: Errbuf := g_errbuf;
5070: Retcode := g_retcode;
5071: fii_util.put_line(Errbuf);
5072:
5073: WHEN OTHERS THEN
5074: Retcode:= -2;
5075: Errbuf := '
5076: ---------------------------------
5077: Error in Procedure: WORKER
5078: Section: '||l_section||'
5079: Message: '||sqlerrm;
5080: fii_util.put_line(Errbuf);
5081:
5082: -------------------------------------------------
5083: -- Write out translated message to let user know
5084: -- the subworker completed with failure status