[Home] [Help]
1: PACKAGE BODY opi_dbi_bounds_pkg AS
2: --$Header: OPIINVBNDB.pls 120.10 2006/03/20 00:33:16 srayadur noship $
3:
4: --Global variables
5: g_pkg_name VARCHAR2(40) := 'OPI_DBI_BOUNDS_PKG';
1: PACKAGE BODY opi_dbi_bounds_pkg AS
2: --$Header: OPIINVBNDB.pls 120.10 2006/03/20 00:33:16 srayadur noship $
3:
4: --Global variables
5: g_pkg_name VARCHAR2(40) := 'OPI_DBI_BOUNDS_PKG';
6:
7: C_ERRBUF_SIZE CONSTANT NUMBER := 300; -- length of formatted error message
8:
9: -- User Defined Exceptions
51: IF (p_load_type = 'INIT') THEN /* running initial load */
52:
53: IF l_debug_mode = 'Y' AND upper(l_module_name) like 'BIS%' THEN
54: l_debug_msg := 'Running initial load for '||p_etl_type ;
55: opi_dbi_bounds_pkg.write(g_pkg_name, l_proc_name, l_stmt_no, l_debug_msg);
56: END IF;
57:
58: l_stmt_no := 10;
59: DELETE FROM OPI_DBI_CONC_PROG_RUN_LOG
60: WHERE etl_type = p_etl_type;
61:
62: IF l_debug_mode = 'Y' AND upper(l_module_name) like 'BIS%' THEN
63: l_debug_msg := 'Deleted '||to_char(sql%rowcount)||' rows from OPI_DBI_CONC_PROG_RUN_LOG' ;
64: opi_dbi_bounds_pkg.write(g_pkg_name, l_proc_name, l_stmt_no, l_debug_msg);
65: END IF;
66:
67: OPI_DBI_BOUNDS_PKG.CALL_ETL_SPECIFIC_BOUND(p_etl_type,p_load_type);
68:
63: l_debug_msg := 'Deleted '||to_char(sql%rowcount)||' rows from OPI_DBI_CONC_PROG_RUN_LOG' ;
64: opi_dbi_bounds_pkg.write(g_pkg_name, l_proc_name, l_stmt_no, l_debug_msg);
65: END IF;
66:
67: OPI_DBI_BOUNDS_PKG.CALL_ETL_SPECIFIC_BOUND(p_etl_type,p_load_type);
68:
69: IF l_debug_mode = 'Y' AND upper(l_module_name) like 'BIS%' THEN
70: l_debug_msg := 'After Calling Call ETL specific bounds' ;
71: opi_dbi_bounds_pkg.write(g_pkg_name, l_proc_name, l_stmt_no, l_debug_msg);
67: OPI_DBI_BOUNDS_PKG.CALL_ETL_SPECIFIC_BOUND(p_etl_type,p_load_type);
68:
69: IF l_debug_mode = 'Y' AND upper(l_module_name) like 'BIS%' THEN
70: l_debug_msg := 'After Calling Call ETL specific bounds' ;
71: opi_dbi_bounds_pkg.write(g_pkg_name, l_proc_name, l_stmt_no, l_debug_msg);
72: END IF;
73:
74: ELSIF (p_load_type = 'INCR') THEN /* running incremental Load */
75:
74: ELSIF (p_load_type = 'INCR') THEN /* running incremental Load */
75:
76: IF l_debug_mode = 'Y' AND upper(l_module_name) like 'BIS%' THEN
77: l_debug_msg := 'Running Incremental Load for '||p_etl_type ;
78: opi_dbi_bounds_pkg.write(g_pkg_name, l_proc_name, l_stmt_no, l_debug_msg);
79: END IF;
80:
81: BEGIN
82: l_count := -1;
99: and status */
100:
101: IF l_debug_mode = 'Y' AND upper(l_module_name) like 'BIS%' THEN
102: l_debug_msg := 'First Time Incremental Load for '||p_etl_type ;
103: opi_dbi_bounds_pkg.write(g_pkg_name, l_proc_name, l_stmt_no, l_debug_msg);
104: END IF;
105:
106: BEGIN
107: l_init_count := -1;
125: copy to bounds of previous INIT record to from bounds of the new INCR record*/
126:
127: IF l_debug_mode = 'Y' AND upper(l_module_name) like 'BIS%' THEN
128: l_debug_msg := 'Init Load successful for '||p_etl_type;
129: opi_dbi_bounds_pkg.write(g_pkg_name, l_proc_name, l_stmt_no, l_debug_msg);
130: END IF;
131:
132: l_stmt_no := 30;
133: INSERT INTO OPI_DBI_CONC_PROG_RUN_LOG(
182: AND load_type = 'INIT';
183:
184: IF l_debug_mode = 'Y' AND upper(l_module_name) like 'BIS%' THEN
185: l_debug_msg := 'Inserted '||to_char(sql%rowcount)||' rows into OPI_DBI_CONC_PROG_RUN_LOG' ;
186: opi_dbi_bounds_pkg.write(g_pkg_name, l_proc_name, l_stmt_no, l_debug_msg);
187: END IF;
188:
189: -- set to bounds by calling call etl specific bound
190: OPI_DBI_BOUNDS_PKG.CALL_ETL_SPECIFIC_BOUND(p_etl_type,p_load_type);
186: opi_dbi_bounds_pkg.write(g_pkg_name, l_proc_name, l_stmt_no, l_debug_msg);
187: END IF;
188:
189: -- set to bounds by calling call etl specific bound
190: OPI_DBI_BOUNDS_PKG.CALL_ETL_SPECIFIC_BOUND(p_etl_type,p_load_type);
191:
192: IF l_debug_mode = 'Y' AND upper(l_module_name) like 'BIS%' THEN
193: l_debug_msg := 'After Calling call_etl_specific_bound for '||p_etl_type||' '||p_load_type||' load' ;
194: opi_dbi_bounds_pkg.write(g_pkg_name, l_proc_name, l_stmt_no, l_debug_msg);
190: OPI_DBI_BOUNDS_PKG.CALL_ETL_SPECIFIC_BOUND(p_etl_type,p_load_type);
191:
192: IF l_debug_mode = 'Y' AND upper(l_module_name) like 'BIS%' THEN
193: l_debug_msg := 'After Calling call_etl_specific_bound for '||p_etl_type||' '||p_load_type||' load' ;
194: opi_dbi_bounds_pkg.write(g_pkg_name, l_proc_name, l_stmt_no, l_debug_msg);
195: END IF;
196:
197: else /* Initial Load is not run prior to Incr Load failed */
198: FND_MESSAGE.SET_NAME('INV','OPI_DBI_INIT_LOAD_NOT_RUN');
208: /* last INCR run successful for all driving tables*/
209:
210: IF l_debug_mode = 'Y' AND upper(l_module_name) like 'BIS%' THEN
211: l_debug_msg := 'Last INCR Load successful . Updating to bounds...';
212: opi_dbi_bounds_pkg.write(g_pkg_name, l_proc_name, l_stmt_no, l_debug_msg);
213: END IF;
214:
215: /* update from_bound_id and from_bound_date as previous to_bound_id and
216: to_bound_date */
255: AND prlout.load_type = p_load_type;
256:
257: IF l_debug_mode = 'Y' AND upper(l_module_name) like 'BIS%' THEN
258: l_debug_msg := 'Updated '||to_char(sql%rowcount)||' rows in OPI_DBI_CONC_PROG_RUN_LOG' ;
259: opi_dbi_bounds_pkg.write(g_pkg_name, l_proc_name, l_stmt_no, l_debug_msg);
260: END IF;
261:
262: else
263: /* if last INCR run was unsuccessful set to bounds to NULL*/
263: /* if last INCR run was unsuccessful set to bounds to NULL*/
264:
265: IF l_debug_mode = 'Y' AND upper(l_module_name) like 'BIS%' THEN
266: l_debug_msg := 'Last INCR Load failed for '||p_etl_type;
267: opi_dbi_bounds_pkg.write(g_pkg_name, l_proc_name, l_stmt_no, l_debug_msg);
268: END IF;
269:
270: l_stmt_no := 50;
271: UPDATE OPI_DBI_CONC_PROG_RUN_LOG
285: AND load_type = p_load_type;
286:
287: IF l_debug_mode = 'Y' AND upper(l_module_name) like 'BIS%' THEN
288: l_debug_msg := 'Updated '||to_char(sql%rowcount)||' rows in OPI_DBI_CONC_PROG_RUN_LOG' ;
289: opi_dbi_bounds_pkg.write(g_pkg_name, l_proc_name, l_stmt_no, l_debug_msg);
290: END IF;
291:
292: end if; /* end INCR load updation*/
293:
290: END IF;
291:
292: end if; /* end INCR load updation*/
293:
294: OPI_DBI_BOUNDS_PKG.CALL_ETL_SPECIFIC_BOUND(p_etl_type,p_load_type);
295:
296: IF l_debug_mode = 'Y' AND upper(l_module_name) like 'BIS%' THEN
297: l_debug_msg := 'After Calling call_etl_specific_bound for '||p_etl_type||' '||p_load_type||' load' ;
298: opi_dbi_bounds_pkg.write(g_pkg_name, l_proc_name, l_stmt_no, l_debug_msg);
294: OPI_DBI_BOUNDS_PKG.CALL_ETL_SPECIFIC_BOUND(p_etl_type,p_load_type);
295:
296: IF l_debug_mode = 'Y' AND upper(l_module_name) like 'BIS%' THEN
297: l_debug_msg := 'After Calling call_etl_specific_bound for '||p_etl_type||' '||p_load_type||' load' ;
298: opi_dbi_bounds_pkg.write(g_pkg_name, l_proc_name, l_stmt_no, l_debug_msg);
299: END IF;
300:
301: end if ; /* end INCR load count check */
302: end if; /* end running INIT/INCR load */
304: EXCEPTION
305: WHEN INIT_LOAD_NOT_RUN THEN
306: IF l_debug_mode = 'Y' AND upper(l_module_name) like 'BIS%' THEN
307: l_debug_msg := FND_MESSAGE.GET;
308: opi_dbi_bounds_pkg.write(g_pkg_name, l_proc_name, l_stmt_no, l_debug_msg);
309: END IF;
310: RAISE;
311:
312: WHEN OTHERS THEN
311:
312: WHEN OTHERS THEN
313: IF l_debug_mode = 'Y' AND upper(l_module_name) like 'BIS%' THEN
314: l_debug_msg := SQLERRM ;
315: opi_dbi_bounds_pkg.write(g_pkg_name, l_proc_name, l_stmt_no, l_debug_msg);
316: END IF;
317: RAISE;
318: END maintain_opi_dbi_logs;
319:
333: l_proc_name := 'call_etl_specific_bound';
334:
335: IF l_debug_mode = 'Y' AND upper(l_module_name) like 'BIS%' THEN
336: l_debug_msg := 'inside call_etl_specific_bound. '||p_load_type||' load' ;
337: opi_dbi_bounds_pkg.write(g_pkg_name, l_proc_name, l_stmt_no, l_debug_msg);
338: END IF;
339:
340: if (p_etl_type = 'INVENTORY') then
341:
339:
340: if (p_etl_type = 'INVENTORY') then
341:
342: l_stmt_no := 10;
343: OPI_DBI_BOUNDS_PKG.setup_inv_mmt_bounds(p_load_type);
344:
345: IF l_debug_mode = 'Y' AND upper(l_module_name) like 'BIS%' THEN
346: l_debug_msg := 'After Calling setup_inv_mmt_bounds for INVENTORY '||p_load_type||' load' ;
347: opi_dbi_bounds_pkg.write(g_pkg_name, l_proc_name, l_stmt_no, l_debug_msg);
343: OPI_DBI_BOUNDS_PKG.setup_inv_mmt_bounds(p_load_type);
344:
345: IF l_debug_mode = 'Y' AND upper(l_module_name) like 'BIS%' THEN
346: l_debug_msg := 'After Calling setup_inv_mmt_bounds for INVENTORY '||p_load_type||' load' ;
347: opi_dbi_bounds_pkg.write(g_pkg_name, l_proc_name, l_stmt_no, l_debug_msg);
348: END IF;
349:
350: l_stmt_no := 20;
351: OPI_DBI_BOUNDS_PKG.setup_inv_wta_bounds(p_load_type);
347: opi_dbi_bounds_pkg.write(g_pkg_name, l_proc_name, l_stmt_no, l_debug_msg);
348: END IF;
349:
350: l_stmt_no := 20;
351: OPI_DBI_BOUNDS_PKG.setup_inv_wta_bounds(p_load_type);
352:
353: IF l_debug_mode = 'Y' AND upper(l_module_name) like 'BIS%' THEN
354: l_debug_msg := 'After Calling setup_inv_wta_bounds for INVENTORY '||p_load_type||' load' ;
355: opi_dbi_bounds_pkg.write(g_pkg_name, l_proc_name, l_stmt_no, l_debug_msg);
351: OPI_DBI_BOUNDS_PKG.setup_inv_wta_bounds(p_load_type);
352:
353: IF l_debug_mode = 'Y' AND upper(l_module_name) like 'BIS%' THEN
354: l_debug_msg := 'After Calling setup_inv_wta_bounds for INVENTORY '||p_load_type||' load' ;
355: opi_dbi_bounds_pkg.write(g_pkg_name, l_proc_name, l_stmt_no, l_debug_msg);
356: END IF;
357:
358: l_stmt_no := 30;
359: OPI_DBI_BOUNDS_PKG.set_sysdate_bounds(p_load_type,p_etl_type,'GTV');
355: opi_dbi_bounds_pkg.write(g_pkg_name, l_proc_name, l_stmt_no, l_debug_msg);
356: END IF;
357:
358: l_stmt_no := 30;
359: OPI_DBI_BOUNDS_PKG.set_sysdate_bounds(p_load_type,p_etl_type,'GTV');
360:
361: IF l_debug_mode = 'Y' AND upper(l_module_name) like 'BIS%' THEN
362: l_debug_msg := 'After Calling set_sysdate_bounds for INVENTORY '||p_load_type||' load' ;
363: opi_dbi_bounds_pkg.write(g_pkg_name, l_proc_name, l_stmt_no, l_debug_msg);
359: OPI_DBI_BOUNDS_PKG.set_sysdate_bounds(p_load_type,p_etl_type,'GTV');
360:
361: IF l_debug_mode = 'Y' AND upper(l_module_name) like 'BIS%' THEN
362: l_debug_msg := 'After Calling set_sysdate_bounds for INVENTORY '||p_load_type||' load' ;
363: opi_dbi_bounds_pkg.write(g_pkg_name, l_proc_name, l_stmt_no, l_debug_msg);
364: END IF;
365:
366: elsif (p_etl_type = 'CYCLE_COUNT') then
367: IF l_debug_mode = 'Y' AND upper(l_module_name) like 'BIS%' THEN
365:
366: elsif (p_etl_type = 'CYCLE_COUNT') then
367: IF l_debug_mode = 'Y' AND upper(l_module_name) like 'BIS%' THEN
368: l_debug_msg := 'Before Calling setup_cc_mmt_bounds for CYCLE_COUNT '||p_load_type||' load' ;
369: opi_dbi_bounds_pkg.write(g_pkg_name, l_proc_name, l_stmt_no, l_debug_msg);
370: END IF;
371:
372: l_stmt_no := 40;
373: OPI_DBI_BOUNDS_PKG.setup_cc_mmt_bounds(p_load_type) ;
369: opi_dbi_bounds_pkg.write(g_pkg_name, l_proc_name, l_stmt_no, l_debug_msg);
370: END IF;
371:
372: l_stmt_no := 40;
373: OPI_DBI_BOUNDS_PKG.setup_cc_mmt_bounds(p_load_type) ;
374:
375: IF l_debug_mode = 'Y' AND upper(l_module_name) like 'BIS%' THEN
376: l_debug_msg := 'After Calling setup_cc_mmt_bounds for CYCLE_COUNT '||p_load_type||' load' ;
377: opi_dbi_bounds_pkg.write(g_pkg_name, l_proc_name, l_stmt_no, l_debug_msg);
373: OPI_DBI_BOUNDS_PKG.setup_cc_mmt_bounds(p_load_type) ;
374:
375: IF l_debug_mode = 'Y' AND upper(l_module_name) like 'BIS%' THEN
376: l_debug_msg := 'After Calling setup_cc_mmt_bounds for CYCLE_COUNT '||p_load_type||' load' ;
377: opi_dbi_bounds_pkg.write(g_pkg_name, l_proc_name, l_stmt_no, l_debug_msg);
378: END IF;
379:
380: l_stmt_no := 50;
381: OPI_DBI_BOUNDS_PKG.set_sysdate_bounds(p_load_type, p_etl_type, 'CCE') ;
377: opi_dbi_bounds_pkg.write(g_pkg_name, l_proc_name, l_stmt_no, l_debug_msg);
378: END IF;
379:
380: l_stmt_no := 50;
381: OPI_DBI_BOUNDS_PKG.set_sysdate_bounds(p_load_type, p_etl_type, 'CCE') ;
382:
383: IF l_debug_mode = 'Y' AND upper(l_module_name) like 'BIS%' THEN
384: l_debug_msg := 'After Calling set_sysdate_bounds for CCE table for CYCLE_COUNT'||p_load_type||' load' ;
385: opi_dbi_bounds_pkg.write(g_pkg_name, l_proc_name, l_stmt_no, l_debug_msg);
381: OPI_DBI_BOUNDS_PKG.set_sysdate_bounds(p_load_type, p_etl_type, 'CCE') ;
382:
383: IF l_debug_mode = 'Y' AND upper(l_module_name) like 'BIS%' THEN
384: l_debug_msg := 'After Calling set_sysdate_bounds for CCE table for CYCLE_COUNT'||p_load_type||' load' ;
385: opi_dbi_bounds_pkg.write(g_pkg_name, l_proc_name, l_stmt_no, l_debug_msg);
386: END IF;
387:
388: l_stmt_no := 60;
389: OPI_DBI_BOUNDS_PKG.set_sysdate_bounds(p_load_type, p_etl_type, 'GTV') ;
385: opi_dbi_bounds_pkg.write(g_pkg_name, l_proc_name, l_stmt_no, l_debug_msg);
386: END IF;
387:
388: l_stmt_no := 60;
389: OPI_DBI_BOUNDS_PKG.set_sysdate_bounds(p_load_type, p_etl_type, 'GTV') ;
390:
391: IF l_debug_mode = 'Y' AND upper(l_module_name) like 'BIS%' THEN
392: l_debug_msg := 'After Calling set_sysdate_bounds for GTV table for CYCLE_COUNT'||p_load_type||' load' ;
393: opi_dbi_bounds_pkg.write(g_pkg_name, l_proc_name, l_stmt_no, l_debug_msg);
389: OPI_DBI_BOUNDS_PKG.set_sysdate_bounds(p_load_type, p_etl_type, 'GTV') ;
390:
391: IF l_debug_mode = 'Y' AND upper(l_module_name) like 'BIS%' THEN
392: l_debug_msg := 'After Calling set_sysdate_bounds for GTV table for CYCLE_COUNT'||p_load_type||' load' ;
393: opi_dbi_bounds_pkg.write(g_pkg_name, l_proc_name, l_stmt_no, l_debug_msg);
394: END IF;
395:
396: elsif(p_etl_type = 'COGS') then
397: IF l_debug_mode = 'Y' AND upper(l_module_name) like 'BIS%' THEN
395:
396: elsif(p_etl_type = 'COGS') then
397: IF l_debug_mode = 'Y' AND upper(l_module_name) like 'BIS%' THEN
398: l_debug_msg := 'Before Calling setup_cogs_mmt_bounds for COGS '||p_load_type||' load' ;
399: opi_dbi_bounds_pkg.write(g_pkg_name, l_proc_name, l_stmt_no, l_debug_msg);
400: END IF;
401:
402: l_stmt_no := 40;
403: OPI_DBI_BOUNDS_PKG.setup_cogs_mmt_bounds(p_load_type);
399: opi_dbi_bounds_pkg.write(g_pkg_name, l_proc_name, l_stmt_no, l_debug_msg);
400: END IF;
401:
402: l_stmt_no := 40;
403: OPI_DBI_BOUNDS_PKG.setup_cogs_mmt_bounds(p_load_type);
404:
405: IF l_debug_mode = 'Y' AND upper(l_module_name) like 'BIS%' THEN
406: l_debug_msg := 'After Calling setup_cogs_mmt_bounds for COGS '||p_load_type||' load' ;
407: opi_dbi_bounds_pkg.write(g_pkg_name, l_proc_name, l_stmt_no, l_debug_msg);
403: OPI_DBI_BOUNDS_PKG.setup_cogs_mmt_bounds(p_load_type);
404:
405: IF l_debug_mode = 'Y' AND upper(l_module_name) like 'BIS%' THEN
406: l_debug_msg := 'After Calling setup_cogs_mmt_bounds for COGS '||p_load_type||' load' ;
407: opi_dbi_bounds_pkg.write(g_pkg_name, l_proc_name, l_stmt_no, l_debug_msg);
408: END IF;
409:
410: l_stmt_no := 50;
411: OPI_DBI_BOUNDS_PKG.set_sysdate_bounds(p_load_type, p_etl_type, 'GTV');
407: opi_dbi_bounds_pkg.write(g_pkg_name, l_proc_name, l_stmt_no, l_debug_msg);
408: END IF;
409:
410: l_stmt_no := 50;
411: OPI_DBI_BOUNDS_PKG.set_sysdate_bounds(p_load_type, p_etl_type, 'GTV');
412:
413: IF l_debug_mode = 'Y' AND upper(l_module_name) like 'BIS%' THEN
414: l_debug_msg := 'After Calling set_sysdate_bounds for GTV table for COGS '||p_load_type||' load' ;
415: opi_dbi_bounds_pkg.write(g_pkg_name, l_proc_name, l_stmt_no, l_debug_msg);
411: OPI_DBI_BOUNDS_PKG.set_sysdate_bounds(p_load_type, p_etl_type, 'GTV');
412:
413: IF l_debug_mode = 'Y' AND upper(l_module_name) like 'BIS%' THEN
414: l_debug_msg := 'After Calling set_sysdate_bounds for GTV table for COGS '||p_load_type||' load' ;
415: opi_dbi_bounds_pkg.write(g_pkg_name, l_proc_name, l_stmt_no, l_debug_msg);
416: END IF;
417:
418: end if;
419: END call_etl_specific_bound;
432: l_debug_mode := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
433:
434: IF l_debug_mode = 'Y' AND upper(l_module_name) like 'BIS%' THEN
435: l_debug_msg := 'inside setup inv mmt bounds '||p_load_type||' load' ;
436: opi_dbi_bounds_pkg.write(g_pkg_name, l_proc_name, l_stmt_no, l_debug_msg);
437: END IF;
438:
439: if (p_load_type = 'INIT') then
440:
438:
439: if (p_load_type = 'INIT') then
440:
441: l_stmt_no :=10;
442: OPI_DBI_BOUNDS_PKG.create_first_mmt_bounds('INVENTORY');
443:
444: IF l_debug_mode = 'Y' AND upper(l_module_name) like 'BIS%' THEN
445: l_debug_msg := 'After Calling create_first_mmt_bounds for INVENTORY '||p_load_type||' load' ;
446: opi_dbi_bounds_pkg.write(g_pkg_name, l_proc_name, l_stmt_no, l_debug_msg);
442: OPI_DBI_BOUNDS_PKG.create_first_mmt_bounds('INVENTORY');
443:
444: IF l_debug_mode = 'Y' AND upper(l_module_name) like 'BIS%' THEN
445: l_debug_msg := 'After Calling create_first_mmt_bounds for INVENTORY '||p_load_type||' load' ;
446: opi_dbi_bounds_pkg.write(g_pkg_name, l_proc_name, l_stmt_no, l_debug_msg);
447: END IF;
448: else
449: IF l_debug_mode = 'Y' AND upper(l_module_name) like 'BIS%' THEN
450: l_debug_msg := 'Before Calling set_mmt_new_bounds for INVENTORY '||p_load_type||' load' ;
447: END IF;
448: else
449: IF l_debug_mode = 'Y' AND upper(l_module_name) like 'BIS%' THEN
450: l_debug_msg := 'Before Calling set_mmt_new_bounds for INVENTORY '||p_load_type||' load' ;
451: opi_dbi_bounds_pkg.write(g_pkg_name, l_proc_name, l_stmt_no, l_debug_msg);
452: END IF;
453:
454: l_stmt_no :=20;
455: OPI_DBI_BOUNDS_PKG.set_mmt_new_bounds('INVENTORY','INCR');
451: opi_dbi_bounds_pkg.write(g_pkg_name, l_proc_name, l_stmt_no, l_debug_msg);
452: END IF;
453:
454: l_stmt_no :=20;
455: OPI_DBI_BOUNDS_PKG.set_mmt_new_bounds('INVENTORY','INCR');
456:
457: IF l_debug_mode = 'Y' AND upper(l_module_name) like 'BIS%' THEN
458: l_debug_msg := 'After Calling set_mmt_new_bounds for INVENTORY '||p_load_type||' load' ;
459: opi_dbi_bounds_pkg.write(g_pkg_name, l_proc_name, l_stmt_no, l_debug_msg);
455: OPI_DBI_BOUNDS_PKG.set_mmt_new_bounds('INVENTORY','INCR');
456:
457: IF l_debug_mode = 'Y' AND upper(l_module_name) like 'BIS%' THEN
458: l_debug_msg := 'After Calling set_mmt_new_bounds for INVENTORY '||p_load_type||' load' ;
459: opi_dbi_bounds_pkg.write(g_pkg_name, l_proc_name, l_stmt_no, l_debug_msg);
460: END IF;
461: end if;
462: END setup_inv_mmt_bounds;
463:
475:
476: if (p_load_type = 'INIT') then
477: IF l_debug_mode = 'Y' AND upper(l_module_name) like 'BIS%' THEN
478: l_debug_msg := 'Before Calling create_first_mmt_bounds for COGS '||p_load_type||' load' ;
479: opi_dbi_bounds_pkg.write(g_pkg_name, l_proc_name, l_stmt_no, l_debug_msg);
480: END IF;
481: l_stmt_no := 10;
482: OPI_DBI_BOUNDS_PKG.create_first_mmt_bounds('COGS');
483:
478: l_debug_msg := 'Before Calling create_first_mmt_bounds for COGS '||p_load_type||' load' ;
479: opi_dbi_bounds_pkg.write(g_pkg_name, l_proc_name, l_stmt_no, l_debug_msg);
480: END IF;
481: l_stmt_no := 10;
482: OPI_DBI_BOUNDS_PKG.create_first_mmt_bounds('COGS');
483:
484: else
485: IF l_debug_mode = 'Y' AND upper(l_module_name) like 'BIS%' THEN
486: l_debug_msg := 'Before Calling set_mmt_new_bounds for COGS '||p_load_type||' load' ;
483:
484: else
485: IF l_debug_mode = 'Y' AND upper(l_module_name) like 'BIS%' THEN
486: l_debug_msg := 'Before Calling set_mmt_new_bounds for COGS '||p_load_type||' load' ;
487: opi_dbi_bounds_pkg.write(g_pkg_name, l_proc_name, l_stmt_no, l_debug_msg);
488: END IF;
489: l_stmt_no :=20;
490: OPI_DBI_BOUNDS_PKG.set_mmt_new_bounds('COGS','INCR');
491: end if;
486: l_debug_msg := 'Before Calling set_mmt_new_bounds for COGS '||p_load_type||' load' ;
487: opi_dbi_bounds_pkg.write(g_pkg_name, l_proc_name, l_stmt_no, l_debug_msg);
488: END IF;
489: l_stmt_no :=20;
490: OPI_DBI_BOUNDS_PKG.set_mmt_new_bounds('COGS','INCR');
491: end if;
492:
493: IF l_debug_mode = 'Y' AND upper(l_module_name) like 'BIS%' THEN
494: l_debug_msg := 'end of setup cogs mmt bounds '||p_load_type||' load' ;
491: end if;
492:
493: IF l_debug_mode = 'Y' AND upper(l_module_name) like 'BIS%' THEN
494: l_debug_msg := 'end of setup cogs mmt bounds '||p_load_type||' load' ;
495: opi_dbi_bounds_pkg.write(g_pkg_name, l_proc_name, l_stmt_no, l_debug_msg);
496: END IF;
497:
498: END setup_cogs_mmt_bounds;
499:
510: l_debug_mode := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
511:
512: IF l_debug_mode = 'Y' AND upper(l_module_name) like 'BIS%' THEN
513: l_debug_msg := 'inside setup_cc_mmt_bounds '||p_load_type||' load' ;
514: opi_dbi_bounds_pkg.write(g_pkg_name, l_proc_name, l_stmt_no, l_debug_msg);
515: END IF;
516:
517: if (p_load_type = 'INIT') then
518:
517: if (p_load_type = 'INIT') then
518:
519: IF l_debug_mode = 'Y' AND upper(l_module_name) like 'BIS%' THEN
520: l_debug_msg := 'Before Calling create_first_mmt_bounds for CYCLE_COUNT '||p_load_type||' load' ;
521: opi_dbi_bounds_pkg.write(g_pkg_name, l_proc_name, l_stmt_no, l_debug_msg);
522: END IF;
523:
524: l_stmt_no := 10;
525: OPI_DBI_BOUNDS_PKG.create_first_mmt_bounds('CYCLE_COUNT');
521: opi_dbi_bounds_pkg.write(g_pkg_name, l_proc_name, l_stmt_no, l_debug_msg);
522: END IF;
523:
524: l_stmt_no := 10;
525: OPI_DBI_BOUNDS_PKG.create_first_mmt_bounds('CYCLE_COUNT');
526:
527:
528: elsif (p_load_type = 'INCR') then
529:
528: elsif (p_load_type = 'INCR') then
529:
530: IF l_debug_mode = 'Y' AND upper(l_module_name) like 'BIS%' THEN
531: l_debug_msg := 'Before Calling set_mmt_new_bounds for CYCLE_COUNT '||p_load_type||' load' ;
532: opi_dbi_bounds_pkg.write(g_pkg_name, l_proc_name, l_stmt_no, l_debug_msg);
533: END IF;
534:
535: l_stmt_no := 20;
536: OPI_DBI_BOUNDS_PKG.set_mmt_new_bounds('CYCLE_COUNT',p_load_type);
532: opi_dbi_bounds_pkg.write(g_pkg_name, l_proc_name, l_stmt_no, l_debug_msg);
533: END IF;
534:
535: l_stmt_no := 20;
536: OPI_DBI_BOUNDS_PKG.set_mmt_new_bounds('CYCLE_COUNT',p_load_type);
537:
538: end if;
539:
540: IF l_debug_mode = 'Y' AND upper(l_module_name) like 'BIS%' THEN
538: end if;
539:
540: IF l_debug_mode = 'Y' AND upper(l_module_name) like 'BIS%' THEN
541: l_debug_msg := 'end of setup_cc_mmt_bounds '||p_load_type||' load' ;
542: opi_dbi_bounds_pkg.write(g_pkg_name, l_proc_name, l_stmt_no, l_debug_msg);
543: END IF;
544:
545: END setup_cc_mmt_bounds ;
546:
665: AND load_type = 'INIT';
666:
667: IF l_debug_mode = 'Y' AND upper(l_module_name) like 'BIS%' THEN
668: l_debug_msg := 'Inserted '||to_char(sql%rowcount)||' rows into OPI_DBI_CONC_PROG_RUN_LOG' ;
669: opi_dbi_bounds_pkg.write(g_pkg_name, l_proc_name, l_stmt_no, l_debug_msg);
670: END IF;
671:
672: -- for COGS we only copy the from bounds from inventory. to bounds are recalculated.
673: -- in case GSD is modified Inventory load should be run prior to COGS else change of GSD
727: AND load_type = 'INIT';
728:
729: IF l_debug_mode = 'Y' AND upper(l_module_name) like 'BIS%' THEN
730: l_debug_msg := 'Inserted '||to_char(sql%rowcount)||' rows into OPI_DBI_CONC_PROG_RUN_LOG' ;
731: opi_dbi_bounds_pkg.write(g_pkg_name, l_proc_name, l_stmt_no, l_debug_msg);
732: END IF;
733:
734: IF l_debug_mode = 'Y' AND upper(l_module_name) like 'BIS%' THEN
735: l_debug_msg := 'Before Calling set_mmt_new_bounds for '||p_etl_type||' INIT load' ;
732: END IF;
733:
734: IF l_debug_mode = 'Y' AND upper(l_module_name) like 'BIS%' THEN
735: l_debug_msg := 'Before Calling set_mmt_new_bounds for '||p_etl_type||' INIT load' ;
736: opi_dbi_bounds_pkg.write(g_pkg_name, l_proc_name, l_stmt_no, l_debug_msg);
737: END IF;
738:
739: -- set the to_bounds.
740: OPI_DBI_BOUNDS_PKG.set_mmt_new_bounds('COGS','INIT');
736: opi_dbi_bounds_pkg.write(g_pkg_name, l_proc_name, l_stmt_no, l_debug_msg);
737: END IF;
738:
739: -- set the to_bounds.
740: OPI_DBI_BOUNDS_PKG.set_mmt_new_bounds('COGS','INIT');
741:
742: IF l_debug_mode = 'Y' AND upper(l_module_name) like 'BIS%' THEN
743: l_debug_msg := 'After Calling set_mmt_new_bounds for '||p_etl_type||' INIT load' ;
744: opi_dbi_bounds_pkg.write(g_pkg_name, l_proc_name, l_stmt_no, l_debug_msg);
740: OPI_DBI_BOUNDS_PKG.set_mmt_new_bounds('COGS','INIT');
741:
742: IF l_debug_mode = 'Y' AND upper(l_module_name) like 'BIS%' THEN
743: l_debug_msg := 'After Calling set_mmt_new_bounds for '||p_etl_type||' INIT load' ;
744: opi_dbi_bounds_pkg.write(g_pkg_name, l_proc_name, l_stmt_no, l_debug_msg);
745: END IF;
746:
747: -- there are two cases handled in this else.
748: -- 1. COGS etl when INVENTORY is not run
756:
757: IF l_debug_mode = 'Y' AND upper(l_module_name) like 'BIS%' THEN
758: l_debug_msg := 'Max transaction_id from MMT is '||to_char(l_max_trx_id)
759: ||' as on '||to_char(sysdate);
760: opi_dbi_bounds_pkg.write(g_pkg_name, l_proc_name, l_stmt_no, l_debug_msg);
761: END IF;
762:
763: l_stmt_no :=50;
764:
832: AND mp.process_enabled_flag <> 'Y';
833:
834: IF l_debug_mode = 'Y' AND upper(l_module_name) like 'BIS%' THEN
835: l_debug_msg := 'Inserted '||to_char(sql%rowcount)||' rows into OPI_DBI_CONC_PROG_RUN_LOG' ;
836: opi_dbi_bounds_pkg.write(g_pkg_name, l_proc_name, l_stmt_no, l_debug_msg);
837: END IF;
838: end if;
839:
840: EXCEPTION
840: EXCEPTION
841: WHEN INITIALIZATION_ERROR THEN
842: IF l_debug_mode = 'Y' AND upper(l_module_name) like 'BIS%' THEN
843: l_debug_msg := 'Global Start Date is NULL' ;
844: opi_dbi_bounds_pkg.write(g_pkg_name, l_proc_name, l_stmt_no, l_debug_msg);
845: END IF;
846: RAISE;
847:
848: WHEN INV_LOAD_NOT_RUN THEN
847:
848: WHEN INV_LOAD_NOT_RUN THEN
849: IF l_debug_mode = 'Y' AND upper(l_module_name) like 'BIS%' THEN
850: l_debug_msg := FND_MESSAGE.GET;
851: opi_dbi_bounds_pkg.write(g_pkg_name, l_proc_name, l_stmt_no, l_debug_msg);
852: END IF;
853: RAISE;
854:
855: WHEN OTHERS THEN
854:
855: WHEN OTHERS THEN
856: IF l_debug_mode = 'Y' AND upper(l_module_name) like 'BIS%' THEN
857: l_debug_msg := SQLERRM ;
858: opi_dbi_bounds_pkg.write(g_pkg_name, l_proc_name, l_stmt_no, l_debug_msg);
859: END IF;
860: RAISE;
861:
862: END create_first_mmt_bounds;
961: END LOOP;
962:
963: IF l_debug_mode = 'Y' AND upper(l_module_name) like 'BIS%' THEN
964: l_debug_msg := 'Inserted '||to_char(sql%rowcount)||' rows into OPI_DBI_CONC_PROG_RUN_LOG' ;
965: opi_dbi_bounds_pkg.write(g_pkg_name, l_proc_name, l_stmt_no, l_debug_msg);
966: END IF;
967:
968: end if; /* end insert new org */
969:
973: FROM mtl_material_transactions mmt;
974:
975: IF l_debug_mode = 'Y' AND upper(l_module_name) like 'BIS%' THEN
976: l_debug_msg := 'Max transaction_id from MMT is '||to_char(l_max_trx_id)||' as on '||to_char(sysdate);
977: opi_dbi_bounds_pkg.write(g_pkg_name, l_proc_name, l_stmt_no, l_debug_msg);
978: END IF;
979:
980: l_stmt_no := 30;
981: /* update to bounds for all records as first uncosted transaction */
1028: AND prlout.bound_level_entity_code = 'ORGANIZATION';
1029:
1030: IF l_debug_mode = 'Y' AND upper(l_module_name) like 'BIS%' THEN
1031: l_debug_msg := 'Updated '||to_char(sql%rowcount)||' rows in OPI_DBI_CONC_PROG_RUN_LOG' ;
1032: opi_dbi_bounds_pkg.write(g_pkg_name, l_proc_name, l_stmt_no, l_debug_msg);
1033: END IF;
1034:
1035: EXCEPTION
1036: WHEN OTHERS THEN
1035: EXCEPTION
1036: WHEN OTHERS THEN
1037: IF l_debug_mode = 'Y' AND upper(l_module_name) like 'BIS%' THEN
1038: l_debug_msg := SQLERRM ;
1039: opi_dbi_bounds_pkg.write(g_pkg_name, l_proc_name, l_stmt_no, l_debug_msg);
1040: END IF;
1041: RAISE;
1042: END set_mmt_new_bounds;
1043:
1131: WHERE transaction_date >= l_global_start_date;
1132:
1133: IF l_debug_mode = 'Y' AND upper(l_module_name) like 'BIS%' THEN
1134: l_debug_msg := 'Inserted '||to_char(sql%rowcount)||' rows into OPI_DBI_CONC_PROG_RUN_LOG' ;
1135: opi_dbi_bounds_pkg.write(g_pkg_name, l_proc_name, l_stmt_no, l_debug_msg);
1136: END IF;
1137:
1138: ELSIF (p_load_type = 'INCR') then
1139: /* Update to_bound as max of transaction_id as of setting the bounds */
1163: AND load_type = p_load_type;
1164:
1165: IF l_debug_mode = 'Y' AND upper(l_module_name) like 'BIS%' THEN
1166: l_debug_msg := 'Updated '||to_char(sql%rowcount)||' rows in OPI_DBI_CONC_PROG_RUN_LOG' ;
1167: opi_dbi_bounds_pkg.write(g_pkg_name, l_proc_name, l_stmt_no, l_debug_msg);
1168: END IF;
1169:
1170: END IF;
1171: EXCEPTION
1171: EXCEPTION
1172: WHEN INITIALIZATION_ERROR THEN
1173: IF l_debug_mode = 'Y' AND upper(l_module_name) like 'BIS%' THEN
1174: l_debug_msg := 'Global Start Date is NULL' ;
1175: opi_dbi_bounds_pkg.write(g_pkg_name, l_proc_name, l_stmt_no, l_debug_msg);
1176: END IF;
1177: RAISE;
1178: WHEN OTHERS THEN
1179: IF l_debug_mode = 'Y' AND upper(l_module_name) like 'BIS%' THEN
1177: RAISE;
1178: WHEN OTHERS THEN
1179: IF l_debug_mode = 'Y' AND upper(l_module_name) like 'BIS%' THEN
1180: l_debug_msg := SQLERRM ;
1181: opi_dbi_bounds_pkg.write(g_pkg_name, l_proc_name, l_stmt_no, l_debug_msg);
1182: END IF;
1183: RAISE;
1184: END setup_inv_wta_bounds ;
1185:
1270: l_request_id
1271: FROM DUAL ;
1272: IF l_debug_mode = 'Y' AND upper(l_module_name) like 'BIS%' THEN
1273: l_debug_msg := 'Inserted '||to_char(sql%rowcount)||' rows into OPI_DBI_CONC_PROG_RUN_LOG' ;
1274: opi_dbi_bounds_pkg.write(g_pkg_name, l_proc_name, l_stmt_no, l_debug_msg);
1275: END IF;
1276:
1277: elsif (p_load_type = 'INCR') then
1278: l_stmt_no := 30;
1291: AND ETL_TYPE = p_etl_type
1292: AND LOAD_TYPE = 'INCR';
1293: IF l_debug_mode = 'Y' AND upper(l_module_name) like 'BIS%' THEN
1294: l_debug_msg := 'Updated '||to_char(sql%rowcount)||' rows in OPI_DBI_CONC_PROG_RUN_LOG' ;
1295: opi_dbi_bounds_pkg.write(g_pkg_name, l_proc_name, l_stmt_no, l_debug_msg);
1296: END IF;
1297: end if;
1298:
1299: EXCEPTION
1299: EXCEPTION
1300: WHEN INITIALIZATION_ERROR THEN
1301: IF l_debug_mode = 'Y' AND upper(l_module_name) like 'BIS%' THEN
1302: l_debug_msg := 'Global Start Date is NULL' ;
1303: opi_dbi_bounds_pkg.write(g_pkg_name, l_proc_name, l_stmt_no, l_debug_msg);
1304: END IF;
1305: RAISE;
1306: WHEN OTHERS THEN
1307: IF l_debug_mode = 'Y' AND upper(l_module_name) like 'BIS%' THEN
1305: RAISE;
1306: WHEN OTHERS THEN
1307: IF l_debug_mode = 'Y' AND upper(l_module_name) like 'BIS%' THEN
1308: l_debug_msg := SQLERRM ;
1309: opi_dbi_bounds_pkg.write(g_pkg_name, l_proc_name, l_stmt_no, l_debug_msg);
1310: END IF;
1311: RAISE;
1312: END set_sysdate_bounds ;
1313:
1332: AND load_type = p_load_type; /*update log table with status success */
1333:
1334: IF l_debug_mode = 'Y' AND upper(l_module_name) like 'BIS%' THEN
1335: l_debug_msg := 'Updated status to success for '||to_char(sql%rowcount)||' rows';
1336: opi_dbi_bounds_pkg.write(g_pkg_name, l_proc_name, l_stmt_no, l_debug_msg);
1337: END IF;
1338:
1339: IF l_debug_mode = 'Y' AND upper(l_module_name) like 'BIS%' THEN
1340: l_debug_msg := p_load_type||' load run for '||p_etl_type||' successful.';
1337: END IF;
1338:
1339: IF l_debug_mode = 'Y' AND upper(l_module_name) like 'BIS%' THEN
1340: l_debug_msg := p_load_type||' load run for '||p_etl_type||' successful.';
1341: opi_dbi_bounds_pkg.write(g_pkg_name, l_proc_name, l_stmt_no, l_debug_msg);
1342: END IF;
1343:
1344: EXCEPTION
1345: WHEN OTHERS THEN
1344: EXCEPTION
1345: WHEN OTHERS THEN
1346: IF l_debug_mode = 'Y' AND upper(l_module_name) like 'BIS%' THEN
1347: l_debug_msg := SQLERRM;
1348: opi_dbi_bounds_pkg.write(g_pkg_name, l_proc_name, l_stmt_no, l_debug_msg);
1349: END IF;
1350: RAISE;
1351: END set_load_successful;
1352:
1565: DELETE FROM OPI_DBI_ORG_LE_TEMP;
1566:
1567: IF l_debug_mode = 'Y' AND upper(l_module_name) like 'BIS%' THEN
1568: l_debug_msg := 'Deleted '||to_char(sql%rowcount)||' rows from OPI_DBI_ORG_LE_TEMP' ;
1569: opi_dbi_bounds_pkg.write(g_pkg_name, l_proc_name, l_stmt_no, l_debug_msg);
1570: END IF;
1571:
1572: -- Inserting rows into the temp table from org_organization_definitions and
1573: -- gmf_fiscal_policies
1592: AND ood.legal_entity = gfp.legal_entity_id ;
1593:
1594: IF l_debug_mode = 'Y' AND upper(l_module_name) like 'BIS%' THEN
1595: l_debug_msg := 'Inserted '||to_char(sql%rowcount)||' rows from OPI_DBI_ORG_LE_TEMP' ;
1596: opi_dbi_bounds_pkg.write(g_pkg_name, l_proc_name, l_stmt_no, l_debug_msg);
1597: END IF;
1598:
1599: EXCEPTION
1600: WHEN OTHERS THEN
1599: EXCEPTION
1600: WHEN OTHERS THEN
1601: IF l_debug_mode = 'Y' AND upper(l_module_name) like 'BIS%' THEN
1602: l_debug_msg := 'Error in deleting/inserting OPM org ledger data into OPI_DBI_ORG_LE_TEMP' ;
1603: opi_dbi_bounds_pkg.write(g_pkg_name, l_proc_name, l_stmt_no, l_debug_msg);
1604: END IF;
1605: RAISE;
1606: END load_opm_org_ledger_data;
1607:
1604: END IF;
1605: RAISE;
1606: END load_opm_org_ledger_data;
1607:
1608: END opi_dbi_bounds_pkg;