[Home] [Help]
PACKAGE BODY: APPS.OPI_DBI_BOUNDS_PKG
Source
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
10 INITIALIZATION_ERROR EXCEPTION;
11 INIT_LOAD_NOT_RUN EXCEPTION;
12 INV_LOAD_NOT_RUN EXCEPTION;
13
14 -- return codes
15 g_ERROR CONSTANT NUMBER := -1;
16 g_WARNING CONSTANT NUMBER := 1;
17 g_ok CONSTANT NUMBER := 0;
18
19 -- ETLs stop reason codes
20 STOP_UNCOSTED CONSTANT VARCHAR2(30) := 'STOP_UNCOSTED';
21 STOP_ALL_COSTED CONSTANT VARCHAR2(30) := 'STOP_ALL_COSTED';
22
23 PROCEDURE maintain_opi_dbi_logs(p_etl_type IN VARCHAR2,
24 p_load_type IN VARCHAR2)
25 IS
26 l_count NUMBER :=0 ;
27 l_init_count NUMBER :=0 ;
28 l_stmt_no NUMBER :=0 ;
29 l_completion_status VARCHAR2(30);
30 l_proc_name VARCHAR2(40);
31 l_debug_msg VARCHAR2(32767);
32 l_debug_mode VARCHAR2(1);
33 l_user_id NUMBER := NVL(fnd_global.USER_ID, -1);
34 l_login_id NUMBER := NVL(fnd_global.LOGIN_ID, -1);
35 l_module_name VARCHAR2(40);
36 l_program_id NUMBER ;
37 l_program_login_id NUMBER ;
38 l_program_application_id NUMBER ;
39 l_request_id NUMBER ;
40
41 BEGIN
42 l_debug_mode := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
43 l_module_name := FND_PROFILE.value('AFLOG_MODULE');
44 l_proc_name := 'maintain_opi_dbi_logs';
45
46 l_program_id := NVL(fnd_global.CONC_PROGRAM_ID,-1);
47 l_program_login_id := NVL(fnd_global.CONC_LOGIN_ID,-1);
48 l_program_application_id := NVL(fnd_global.PROG_APPL_ID,-1);
49 l_request_id := NVL(fnd_global.CONC_REQUEST_ID,-1);
50
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
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
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;
83 l_completion_status := null;
84
85 /* check whether its the first time incremental load */
86 SELECT 1,nvl(completion_status_code,'N') INTO l_count,l_completion_status
87 FROM OPI_DBI_CONC_PROG_RUN_LOG
88 WHERE etl_type = p_etl_type
89 AND load_type = p_load_type
90 AND rownum <= 1;
91 EXCEPTION
92 WHEN NO_DATA_FOUND THEN
93 l_count := 0;
94 END;
95
96
97 if (l_count = 0) then
98 /* No incr load has been run before so check for the previous initial load run
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;
108
109 /* As completion_status_code is updated based on etl_type and load_type success
110 of one record implies success of all the records for that etl_type and load_type */
111
112 SELECT 1,nvl(completion_status_code,'N') into l_init_count,l_completion_status
113 FROM OPI_DBI_CONC_PROG_RUN_LOG
114 WHERE etl_type = p_etl_type
115 AND load_type = 'INIT'
116 AND rownum <= 1;
117
118 EXCEPTION
119 WHEN NO_DATA_FOUND THEN
120 l_init_count := 0;
121 END;
122
123 if (l_init_count = 1 AND l_completion_status = 'S') then
124 /* if prev INIT record exists and successful then create new INCR record and
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(
134 driving_table_code ,
135 etl_type ,
136 load_type ,
137 bound_type ,
138 bound_level_entity_code ,
139 bound_level_entity_id ,
140 from_bound_date ,
141 from_bound_id ,
142 to_bound_date ,
143 to_bound_id ,
144 completion_status_code ,
145 stop_reason_code ,
146 created_by ,
147 creation_date ,
148 last_run_date ,
149 last_update_date ,
150 last_updated_by ,
151 last_update_login ,
152 program_id ,
153 program_login_id ,
154 program_application_id ,
155 request_id
156 )
157 SELECT
158 driving_table_code ,
159 etl_type ,
160 'INCR' ,
161 bound_type ,
162 bound_level_entity_code ,
163 bound_level_entity_id ,
164 to_bound_date ,
165 to_bound_id ,
166 null ,
167 null ,
168 null ,
169 null ,
170 l_user_id ,
171 sysdate ,
172 sysdate ,
173 sysdate ,
174 l_user_id ,
175 l_login_id ,
176 l_program_id ,
177 l_program_login_id ,
181 WHERE etl_type = p_etl_type
178 l_program_application_id ,
179 l_request_id
180 FROM 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);
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');
199 RAISE INIT_LOAD_NOT_RUN;
200 end if;
201
202 else /* This is not the first time incremental load */
203
204 -- check completion_status_code = 'S' for previous INCR record for
205 -- that etl_type and load_type
206
207 if (l_completion_status = 'S') then
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 */
217 l_stmt_no :=40;
218 UPDATE OPI_DBI_CONC_PROG_RUN_LOG prlout
219 SET ( from_bound_id ,
220 from_bound_date ,
221 to_bound_date ,
222 to_bound_id ,
223 completion_status_code,
224 stop_reason_code ,
225 last_run_date ,
226 last_update_date ,
227 last_updated_by ,
228 last_update_login ,
229 program_id ,
230 program_login_id ,
231 program_application_id,
232 request_id
233 ) =
234 (SELECT
235 to_bound_id ,
236 to_bound_date ,
237 null ,
238 null ,
239 null ,
240 null ,
241 sysdate ,
242 sysdate ,
243 l_user_id ,
244 l_login_id ,
245 l_program_id ,
246 l_program_login_id ,
247 l_program_application_id,
248 l_request_id
249 FROM OPI_DBI_CONC_PROG_RUN_LOG prlin
250 WHERE prlin.etl_type = prlout.etl_type
251 AND prlin.load_type = prlout.load_type
252 AND prlin.driving_table_code = prlout.driving_table_code
253 AND nvl(prlin.bound_level_entity_id,-1) = nvl(prlout.bound_level_entity_id,-1))
254 WHERE prlout.etl_type = p_etl_type
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*/
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
272 SET to_bound_id = null,
273 to_bound_date = null,
274 completion_status_code = null,
275 stop_reason_code = null,
276 last_run_date = null , -- last run date should be null at this point
277 last_update_date = sysdate ,
278 last_updated_by = l_user_id ,
279 last_update_login = l_login_id,
280 program_id = l_program_id ,
281 program_login_id = l_program_login_id ,
282 program_application_id = l_program_application_id,
283 request_id = l_request_id
284 WHERE etl_type = p_etl_type
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' ;
292 end if; /* end INCR load updation*/
289 opi_dbi_bounds_pkg.write(g_pkg_name, l_proc_name, l_stmt_no, l_debug_msg);
290 END IF;
291
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);
299 END IF;
300
301 end if ; /* end INCR load count check */
302 end if; /* end running INIT/INCR load */
303
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
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
320
321
322 PROCEDURE call_etl_specific_bound(p_etl_type IN VARCHAR2,
323 p_load_type IN VARCHAR2)
324 IS
325 l_debug_msg VARCHAR2(32767);
326 l_debug_mode VARCHAR2(1);
327 l_stmt_no NUMBER :=0;
328 l_proc_name VARCHAR2(40);
329 l_module_name VARCHAR2(40);
330 BEGIN
331 l_debug_mode := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
332 l_module_name := FND_PROFILE.value('AFLOG_MODULE');
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
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);
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);
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);
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
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) ;
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') ;
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') ;
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
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);
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;
414 l_debug_msg := 'After Calling set_sysdate_bounds for GTV table for COGS '||p_load_type||' load' ;
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
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;
420
421
422 PROCEDURE setup_inv_mmt_bounds(p_load_type IN VARCHAR2)
423 IS
424 l_debug_msg VARCHAR2(32767);
425 l_debug_mode VARCHAR2(1);
426 l_stmt_no NUMBER :=0;
427 l_proc_name VARCHAR2(40);
428 l_module_name VARCHAR2(40);
429 BEGIN
430 l_module_name := FND_PROFILE.value('AFLOG_MODULE');
431 l_proc_name := 'setup_inv_mmt_bounds';
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
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);
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');
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
464 PROCEDURE setup_cogs_mmt_bounds(p_load_type IN VARCHAR2)
465 IS
466 l_debug_msg VARCHAR2(32767);
467 l_debug_mode VARCHAR2(1);
468 l_stmt_no NUMBER := 0;
469 l_proc_name VARCHAR2(40);
470 l_module_name VARCHAR2(40);
471 BEGIN
472 l_debug_mode := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
473 l_proc_name := 'setup_cogs_mmt_bounds';
474
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
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;
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
500 PROCEDURE setup_cc_mmt_bounds(p_load_type IN VARCHAR2)
501 IS
502 l_proc_name VARCHAR2(40);
503 l_debug_msg VARCHAR2(32767);
504 l_debug_mode VARCHAR2(1);
505 l_stmt_no NUMBER := 0;
506 l_module_name VARCHAR2(40);
507 BEGIN
508 l_module_name := FND_PROFILE.value('AFLOG_MODULE');
509 l_proc_name := 'setup_cogs_mmt_bounds';
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
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');
526
527
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);
537
538 end if;
539
543 END IF;
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);
544
545 END setup_cc_mmt_bounds ;
546
547 PROCEDURE create_first_mmt_bounds(p_etl_type IN VARCHAR2)
548 IS
549 l_inv_count NUMBER;
550 l_max_trx_id NUMBER;
551 l_global_start_date DATE;
552 l_stmt_no NUMBER :=0;
553 l_proc_name VARCHAR2(40);
554 l_user_id NUMBER := NVL(fnd_global.USER_ID, -1);
555 l_login_id NUMBER := NVL(fnd_global.LOGIN_ID, -1);
556 l_debug_msg VARCHAR2(32767);
557 l_debug_mode VARCHAR2(1);
558 l_module_name VARCHAR2(40);
559
560 l_program_id NUMBER ;
561 l_program_login_id NUMBER ;
562 l_program_application_id NUMBER ;
563 l_request_id NUMBER ;
564
565 BEGIN
566 l_module_name := FND_PROFILE.value('AFLOG_MODULE');
567 l_debug_mode := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
568 l_proc_name := 'create_first_mmt_bounds';
569
570 l_program_id := NVL(fnd_global.CONC_PROGRAM_ID,-1);
571 l_program_login_id := NVL(fnd_global.CONC_LOGIN_ID,-1);
572 l_program_application_id := NVL(fnd_global.PROG_APPL_ID,-1);
573 l_request_id := NVL(fnd_global.CONC_REQUEST_ID,-1);
574
575 l_stmt_no :=10;
576 select BIS_COMMON_PARAMETERS.GET_GLOBAL_START_DATE into l_global_start_date from DUAL;
577
578 if (l_global_start_date is NULL) then
579 RAISE INITIALIZATION_ERROR ;
580 end if;
581
582 /* check for INV INIT record to reuse bounds for COGS and CYCLE_COUNT loads*/
583
584 if (p_etl_type = 'COGS' or p_etl_type = 'CYCLE_COUNT') then
585
586 BEGIN
587
588 l_inv_count := -1;
589
590 SELECT count(1) INTO l_inv_count FROM OPI_DBI_CONC_PROG_RUN_LOG
591 WHERE etl_type = 'INVENTORY'
592 AND load_type = 'INIT'
593 AND rownum <=1 ;
594
595 EXCEPTION
596 WHEN NO_DATA_FOUND THEN
597 l_inv_count := 0;
598 END;
599
600 end if;
601
602 /* if etl_type is cycle count and inv load is not run then error out */
603
604 if (p_etl_type = 'CYCLE_COUNT' and l_inv_count = 0) then
605 /* Error msg for init load not run prior to incr load */
606 FND_MESSAGE.SET_NAME('INV','OPI_DBI_INV_LOAD_NOT_RUN');
607 RAISE INV_LOAD_NOT_RUN;
608 end if;
609
610 -- cycle count uses the same bounds as Inventory hence for cycle count get
611 -- from and to bounds from inventory record.
612 -- it is not possible that cycle count incr is being run without running incr
613 -- or inventory.
614 if (l_inv_count = 1 and p_etl_type = 'CYCLE_COUNT') then
615 l_stmt_no :=20;
616 INSERT INTO OPI_DBI_CONC_PROG_RUN_LOG(
617 driving_table_code ,
618 etl_type ,
619 load_type ,
620 bound_type ,
621 bound_level_entity_code ,
622 bound_level_entity_id ,
623 from_bound_date ,
624 from_bound_id ,
625 to_bound_date ,
626 to_bound_id ,
627 completion_status_code ,
628 stop_reason_code ,
629 created_by ,
630 creation_date ,
631 last_run_date ,
632 last_update_date ,
633 last_updated_by ,
634 last_update_login ,
635 program_id ,
636 program_login_id ,
637 program_application_id ,
638 request_id)
639 SELECT
640 'MMT' ,
641 p_etl_type ,
642 'INIT' ,
643 bound_type ,
644 bound_level_entity_code ,
645 bound_level_entity_id , /* org frm INV INIT record */
646 null ,
647 from_bound_id , /* from_bound_id from INV record */
648 null ,
649 to_bound_id , /* to_bound_id from INV record */
650 null ,
651 stop_reason_code , /* stop_reason_code copied from INVENTORY record */
652 l_user_id ,
653 sysdate ,
654 sysdate ,
655 sysdate ,
656 l_user_id ,
657 l_login_id ,
658 l_program_id ,
659 l_program_login_id ,
660 l_program_application_id,
661 l_request_id
662 FROM OPI_DBI_CONC_PROG_RUN_LOG
663 WHERE etl_type = 'INVENTORY'
664 AND driving_table_code = 'MMT'
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
677 INSERT INTO OPI_DBI_CONC_PROG_RUN_LOG(
674 -- will not take effect in COGS etl
675 elsif(l_inv_count = 1 and p_etl_type = 'COGS' ) then
676 l_stmt_no :=30;
678 driving_table_code ,
679 etl_type ,
680 load_type ,
681 bound_type ,
682 bound_level_entity_code ,
683 bound_level_entity_id ,
684 from_bound_date ,
685 from_bound_id ,
686 to_bound_date ,
687 to_bound_id ,
688 completion_status_code ,
689 stop_reason_code ,
690 created_by ,
691 creation_date ,
692 last_run_date ,
693 last_update_date ,
694 last_updated_by ,
695 last_update_login ,
696 program_id ,
697 program_login_id ,
698 program_application_id ,
699 request_id
700 )
701 SELECT
702 'MMT' ,
703 p_etl_type ,
704 'INIT' ,
705 bound_type ,
706 bound_level_entity_code ,
707 bound_level_entity_id , /* org frm INV INIT record */
708 null ,
709 from_bound_id , /* min (from_bound_id) from all INV records */
710 null ,
711 null , /* set to null for now updated in set_mmt_new_bounds */
712 null ,
713 stop_reason_code ,
714 l_user_id ,
715 sysdate ,
716 sysdate ,
717 sysdate ,
718 l_user_id ,
719 l_login_id ,
720 l_program_id ,
721 l_program_login_id ,
722 l_program_application_id,
723 l_request_id
724 FROM OPI_DBI_CONC_PROG_RUN_LOG
725 WHERE etl_type = 'INVENTORY'
726 AND driving_table_code = 'MMT'
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' ;
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);
745 END IF;
746
747 -- there are two cases handled in this else.
748 -- 1. COGS etl when INVENTORY is not run
749 -- 2. p_etl_type = INVENTORY.
750 -- in both the cases we compute the start bound from MMT and then set the to_bound
751 else
752 l_max_trx_id := -1;
753 l_stmt_no :=40;
754 SELECT max(transaction_id)+1 INTO l_max_trx_id
755 FROM mtl_material_transactions mmt;
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
765 INSERT INTO OPI_DBI_CONC_PROG_RUN_LOG(
766 driving_table_code ,
767 etl_type ,
768 load_type ,
769 bound_type ,
770 bound_level_entity_code ,
771 bound_level_entity_id ,
772 from_bound_date ,
773 from_bound_id ,
774 to_bound_date ,
775 to_bound_id ,
776 completion_status_code ,
777 stop_reason_code ,
778 created_by ,
779 creation_date ,
780 last_run_date ,
781 last_update_date ,
782 last_updated_by ,
783 last_update_login ,
784 program_id ,
785 program_login_id ,
786 program_application_id ,
787 request_id)
788 SELECT
789 'MMT' ,
790 p_etl_type ,
791 'INIT' ,
792 'ID' ,
793 'ORGANIZATION' ,
794 mp.organization_id ,
795 null ,
796 /* FIRST TXN ID FOR THE ORGANIZATION AFTER GSD .IF There are no records for
797 the org after GSD then incr record wouldn't be created*/
798 min_trx.transaction_id ,
799 null ,
800 /* FIRST UNCOSTED TXN ID FOR THE ORGANIZATION. MAX TRANSACTION OF MMT
801 IN CASE THERE NO UNCOSTED TXN. */
805 decode (uncosted_trx.transaction_id,NULL, 'STOP_ALL_COSTED','STOP_UNCOSTED'),
802 nvl(uncosted_trx.transaction_id,l_max_trx_id) ,
803 null ,
804 /* stop reason code */
806 l_user_id ,
807 sysdate ,
808 sysdate ,
809 sysdate ,
810 l_user_id ,
811 l_login_id ,
812 l_program_id ,
813 l_program_login_id ,
814 l_program_application_id,
815 l_request_id
816 FROM mtl_parameters mp ,
817 (
818 SELECT /*+ no_merge parallel(mmt) */ organization_id,min(transaction_id) transaction_id
819 FROM mtl_material_transactions mmt
820 WHERE transaction_date >= l_global_start_date
821 GROUP BY organization_id
822 )min_trx ,
823 (
824 SELECT /*+ no_merge parallel(mmt) */ organization_id,min(transaction_id) transaction_id
825 FROM mtl_material_transactions mmt
826 WHERE costed_flag in('N','E')
827 AND transaction_date >= l_global_start_date --Bug 5096963
828 GROUP BY organization_id
829 )uncosted_trx
830 WHERE mp.organization_id = min_trx.organization_id
831 AND min_trx.organization_id = uncosted_trx.organization_id(+)
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
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
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
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;
863
864
865 PROCEDURE set_mmt_new_bounds(p_etl_type IN VARCHAR2,p_load_type VARCHAR2)
866 IS
867 l_stmt_no NUMBER :=0;
868 l_max_trx_id NUMBER;
869 l_proc_name VARCHAR2(40);
870 l_user_id NUMBER := NVL(fnd_global.USER_ID, -1);
871 l_login_id NUMBER := NVL(fnd_global.LOGIN_ID, -1);
872 l_debug_msg VARCHAR2(32767);
873 l_debug_mode VARCHAR2(1);
874 l_module_name VARCHAR2(40);
875
876 l_program_id NUMBER ;
877 l_program_login_id NUMBER ;
878 l_program_application_id NUMBER ;
879 l_request_id NUMBER ;
880
881
882 /* open a cursor for holding all new organizations in mtl_parameters after previous INIT-INCR load */
883 CURSOR csr_get_new_org IS
884 SELECT DISTINCT organization_id
885 FROM mtl_parameters
886 WHERE process_enabled_flag <> 'Y'
887 MINUS
888 SELECT DISTINCT bound_level_entity_id
889 FROM OPI_DBI_CONC_PROG_RUN_LOG
890 WHERE etl_type = p_etl_type
891 AND driving_table_code = 'MMT';
892
893 BEGIN
894 l_module_name := FND_PROFILE.value('AFLOG_MODULE');
895 l_debug_mode := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
896 l_proc_name := 'set_mmt_new_bounds';
897
898 l_program_id := NVL(fnd_global.CONC_PROGRAM_ID,-1);
899 l_program_login_id := NVL(fnd_global.CONC_LOGIN_ID,-1);
900 l_program_application_id := NVL(fnd_global.PROG_APPL_ID,-1);
901 l_request_id := NVL(fnd_global.CONC_REQUEST_ID,-1);
902
903 /* copy from bound as max of existing to bound for the new organizations and insert records */
904 /* this code inserts all new organizations from MMT without checking the existence of a
905 transaction for them after global_start_date */
906 /* find new organizations only on INCR load */
907 if (p_load_type = 'INCR') then
908
909 l_stmt_no := 10;
910 FOR c_new_org IN csr_get_new_org LOOP
911 INSERT INTO OPI_DBI_CONC_PROG_RUN_LOG(
912 driving_table_code ,
913 etl_type ,
914 load_type ,
915 bound_type ,
916 bound_level_entity_code ,
917 bound_level_entity_id ,
918 from_bound_date ,
919 from_bound_id ,
920 to_bound_date ,
921 to_bound_id ,
922 completion_status_code ,
923 stop_reason_code ,
924 created_by ,
925 creation_date ,
926 last_run_date ,
927 last_update_date ,
928 last_updated_by ,
929 last_update_login ,
930 program_id ,
931 program_login_id ,
932 program_application_id ,
936 'MMT' ,
933 request_id
934 )
935 SELECT
937 p_etl_type ,
938 'INCR' ,
939 'ID' ,
940 'ORGANIZATION' ,
941 c_new_org.organization_id ,
942 null ,
943 max(to_bound_id) ,
944 null ,
945 null ,
946 null ,
947 null ,
948 l_user_id ,
949 sysdate ,
950 sysdate ,
951 sysdate ,
952 l_user_id ,
953 l_login_id ,
954 l_program_id ,
955 l_program_login_id ,
956 l_program_application_id,
957 l_request_id
958 FROM OPI_DBI_CONC_PROG_RUN_LOG
959 WHERE etl_type = p_etl_type
960 AND driving_table_code = 'MMT';
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
970 l_max_trx_id := -1;
971 l_stmt_no :=20;
972 SELECT max(transaction_id)+1 INTO l_max_trx_id
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 */
982 UPDATE OPI_DBI_CONC_PROG_RUN_LOG prlout
983 SET ( to_bound_id ,
984 stop_reason_code ,
985 completion_status_code ,
986 last_run_date ,
987 last_update_date ,
988 last_updated_by ,
989 last_update_login ,
990 program_id ,
991 program_login_id ,
992 program_application_id ,
993 request_id
994 ) =
995 (select
996 /* FIRST UNCOSTED TXN ID FOR THE ORGANIZATION.
997 MAX TRANSACTION OF MMT IN CASE THERE NO UNCOSTED TXN. */
998 nvl(uncosted_trx.transaction_id,l_max_trx_id),
999 /* stop reason code */
1000 decode(uncosted_trx.transaction_id,NULL,'STOP_ALL_COSTED','STOP_UNCOSTED'),
1001 null ,
1002 sysdate ,
1003 sysdate ,
1004 l_user_id ,
1005 l_login_id ,
1006 l_program_id ,
1007 l_program_login_id ,
1008 l_program_application_id,
1009 l_request_id
1010 from
1011 (SELECT /*+ no_merge parallel(mmt) */ organization_id,min(transaction_id) transaction_id
1012 FROM mtl_material_transactions mmt
1013 WHERE costed_flag in('N','E')
1014 AND transaction_id >= (SELECT from_bound_id FROM opi_dbi_conc_prog_run_log plog
1015 WHERE plog.etl_type = p_etl_type
1016 AND plog.load_type = p_load_type
1017 AND plog.driving_table_code = 'MMT'
1018 AND plog.bound_level_entity_code = 'ORGANIZATION'
1019 AND mmt.organization_id = plog.bound_level_entity_id) --Bug 5096963
1020 GROUP BY organization_id
1021 ) uncosted_trx
1022 , mtl_parameters mp
1023 where prlout.bound_level_entity_id = mp.organization_id
1024 and mp.organization_id = uncosted_trx.organization_id(+))
1025 WHERE prlout.driving_table_code = 'MMT'
1026 AND prlout.etl_type = p_etl_type
1027 AND prlout.load_type = p_load_type
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
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
1044 PROCEDURE setup_inv_wta_bounds(p_load_type IN VARCHAR2)
1045 IS
1046 l_user_id NUMBER := NVL(fnd_global.USER_ID, -1);
1047 l_login_id NUMBER := NVL(fnd_global.LOGIN_ID, -1);
1048 l_stmt_no NUMBER :=0;
1049 l_proc_name VARCHAR2(40);
1050 l_global_start_date DATE;
1051 l_debug_msg VARCHAR2(32767);
1052 l_debug_mode VARCHAR2(1);
1053 l_module_name VARCHAR2(40);
1054
1055 l_program_id NUMBER ;
1056 l_program_login_id NUMBER ;
1057 l_program_application_id NUMBER ;
1058 l_request_id NUMBER ;
1059
1060 BEGIN
1061 l_module_name := FND_PROFILE.value('AFLOG_MODULE');
1065 l_program_id := NVL(fnd_global.CONC_PROGRAM_ID,-1);
1062 l_debug_mode := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
1063 l_proc_name := 'setup_inv_wta_bounds';
1064
1066 l_program_login_id := NVL(fnd_global.CONC_LOGIN_ID,-1);
1067 l_program_application_id := NVL(fnd_global.PROG_APPL_ID,-1);
1068 l_request_id := NVL(fnd_global.CONC_REQUEST_ID,-1);
1069
1070
1071 l_stmt_no := 10;
1072 select BIS_COMMON_PARAMETERS.GET_GLOBAL_START_DATE into l_global_start_date from DUAL;
1073
1074 if (l_global_start_date is NULL) then
1075 RAISE INITIALIZATION_ERROR ;
1076 end if;
1077
1078 IF (p_load_type = 'INIT') THEN
1079 /* insert records with from_bound_id as first transaction after GSD and to_bound_id as max of
1080 transaction_id as of setting bounds from WTA */
1081
1082 l_stmt_no := 20;
1083 INSERT into OPI_DBI_CONC_PROG_RUN_LOG(
1084 driving_table_code ,
1085 etl_type ,
1086 load_type ,
1087 bound_type ,
1088 bound_level_entity_code ,
1089 bound_level_entity_id ,
1090 from_bound_date ,
1091 from_bound_id ,
1092 to_bound_date ,
1093 to_bound_id ,
1094 completion_status_code ,
1095 stop_reason_code ,
1096 created_by ,
1097 creation_date ,
1098 last_run_date ,
1099 last_update_date ,
1100 last_updated_by ,
1101 last_update_login ,
1102 program_id ,
1103 program_login_id ,
1104 program_application_id ,
1105 request_id
1106 )
1107 SELECT
1108 'WTA' ,
1109 'INVENTORY' ,
1110 'INIT' ,
1111 'ID' ,
1112 null ,
1113 null ,
1114 null ,
1115 min(transaction_id) ,
1116 null ,
1117 max(transaction_id)+1 ,
1118 null ,
1119 null ,
1120 l_user_id ,
1121 sysdate ,
1122 sysdate ,
1123 sysdate ,
1124 l_user_id ,
1125 l_login_id ,
1126 l_program_id ,
1127 l_program_login_id ,
1128 l_program_application_id,
1129 l_request_id
1130 FROM wip_transaction_accounts
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 */
1140 l_stmt_no := 30;
1141 UPDATE OPI_DBI_CONC_PROG_RUN_LOG
1142 SET (to_bound_id ,
1143 completion_status_code ,
1144 last_update_date ,
1145 last_updated_by ,
1146 last_update_login ,
1147 program_id ,
1148 program_login_id ,
1149 program_application_id ,
1150 request_id ) =
1151 (SELECT max(transaction_id)+1 ,
1152 null ,
1153 sysdate ,
1154 l_user_id ,
1155 l_login_id ,
1156 l_program_id ,
1157 l_program_login_id ,
1158 l_program_application_id,
1159 l_request_id
1160 FROM wip_transaction_accounts)
1161 WHERE driving_table_code = 'WTA'
1162 AND etl_type = 'INVENTORY'
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
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
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
1186
1187 PROCEDURE set_sysdate_bounds(p_load_type IN VARCHAR2,
1188 p_etl_type IN VARCHAR2,
1189 p_driving_table_code IN VARCHAR2)
1190 IS
1191 l_user_id NUMBER := NVL(fnd_global.USER_ID, -1);
1192 l_login_id NUMBER := NVL(fnd_global.LOGIN_ID, -1);
1196 l_debug_msg VARCHAR2(32767);
1193 l_stmt_no NUMBER :=0;
1194 l_proc_name VARCHAR2(40);
1195 l_global_start_date DATE;
1197 l_debug_mode VARCHAR2(1);
1198 l_module_name VARCHAR2(40);
1199 l_program_id NUMBER ;
1200 l_program_login_id NUMBER ;
1201 l_program_application_id NUMBER ;
1202 l_request_id NUMBER ;
1203
1204
1205 BEGIN
1206
1207 l_module_name := FND_PROFILE.value('AFLOG_MODULE');
1208 l_debug_mode := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
1209 l_proc_name := 'set_sysdate_bounds';
1210
1211 l_program_id := NVL(fnd_global.CONC_PROGRAM_ID,-1);
1212 l_program_login_id := NVL(fnd_global.CONC_LOGIN_ID,-1);
1213 l_program_application_id := NVL(fnd_global.PROG_APPL_ID,-1);
1214 l_request_id := NVL(fnd_global.CONC_REQUEST_ID,-1);
1215
1216 l_stmt_no :=10;
1217 select BIS_COMMON_PARAMETERS.GET_GLOBAL_START_DATE into l_global_start_date from DUAL;
1218
1219 if (l_global_start_date is NULL) then
1220 RAISE INITIALIZATION_ERROR ;
1221 end if;
1222
1223 if (p_load_type = 'INIT') then
1224 l_stmt_no := 20;
1225 INSERT into OPI_DBI_CONC_PROG_RUN_LOG(
1226 driving_table_code ,
1227 etl_type ,
1228 load_type ,
1229 bound_type ,
1230 bound_level_entity_code ,
1231 bound_level_entity_id ,
1232 from_bound_date ,
1233 from_bound_id ,
1234 to_bound_date ,
1235 to_bound_id ,
1236 completion_status_code ,
1237 stop_reason_code ,
1238 created_by ,
1239 creation_date ,
1240 last_run_date ,
1241 last_update_date ,
1242 last_updated_by ,
1243 last_update_login ,
1244 program_id ,
1245 program_login_id ,
1246 program_application_id ,
1247 request_id )
1248 SELECT
1249 p_driving_table_code ,
1250 p_etl_type ,
1251 'INIT' ,
1252 'DATE' ,
1253 null ,
1254 null ,
1255 l_global_start_date ,
1256 null ,
1257 sysdate ,
1258 null ,
1259 null ,
1260 null ,
1261 l_user_id ,
1262 sysdate ,
1263 sysdate ,
1264 sysdate ,
1265 l_user_id ,
1266 l_login_id ,
1267 l_program_id ,
1268 l_program_login_id ,
1269 l_program_application_id,
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;
1279 UPDATE OPI_DBI_CONC_PROG_RUN_LOG
1280 SET TO_BOUND_DATE = sysdate ,
1281 completion_status_code = null ,
1282 LAST_RUN_DATE = sysdate ,
1283 LAST_UPDATE_DATE = sysdate ,
1284 LAST_UPDATED_BY = l_user_id ,
1285 LAST_UPDATE_LOGIN = l_login_id ,
1286 PROGRAM_ID = l_program_id ,
1287 PROGRAM_LOGIN_ID = l_program_login_id ,
1288 PROGRAM_APPLICATION_ID = l_program_application_id ,
1289 REQUEST_ID = l_request_id
1290 WHERE DRIVING_TABLE_CODE = p_driving_table_code
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
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
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
1314 PROCEDURE set_load_successful(p_etl_type IN VARCHAR2,
1315 p_load_type IN VARCHAR2)
1316 IS
1317 l_stmt_no NUMBER := 0;
1318 l_proc_name VARCHAR2(40);
1319 l_debug_msg VARCHAR2(32767);
1320 l_debug_mode VARCHAR2(1);
1321 l_module_name VARCHAR2(40);
1322
1323 BEGIN
1327
1324 l_module_name := FND_PROFILE.value('AFLOG_MODULE');
1325 l_debug_mode := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
1326 l_proc_name := 'set_load_successful';
1328 l_stmt_no := 10;
1329 UPDATE OPI_DBI_CONC_PROG_RUN_LOG
1330 SET completion_status_code = 'S'
1331 WHERE etl_type = p_etl_type
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.';
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
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
1353
1354 /* Generic routine for writing debug messages */
1355
1356 PROCEDURE write (p_pkg_name IN VARCHAR2,
1357 p_proc_name IN VARCHAR2,
1358 p_stmt_no IN NUMBER ,
1359 p_debug_msg IN VARCHAR2)
1360 IS
1361 -- l_debug_mode VARCHAR2(1);
1362 l_proc_name VARCHAR2(40);
1363 -- l_module_name VARCHAR2(40);
1364 BEGIN
1365 -- l_debug_mode := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
1366 -- l_module_name := FND_PROFILE.value('AFLOG_MODULE');
1367
1368 /*
1369 insert into mano_log (pkg, proc_name, stmt_no, msg )
1370 select p_pkg_name, p_proc_name, p_stmt_no, p_debug_msg from dual;
1371 commit;
1372 */
1373 -- This API may be used to print other messages in addition to debug messages.Hence, the check is not done here.
1374 -- IF l_debug_mode = 'Y' AND upper(l_module_name) like 'BIS%' THEN
1375 BIS_COLLECTION_UTILITIES.PUT_LINE(p_pkg_name ||'.'|| p_proc_name||': At statement '
1376 || p_stmt_no ||'#, '||p_debug_msg);
1377 -- END IF;
1378 END write;
1379
1380 /*===============================================================
1381 This procedure prints the MMT bounds at which the Discrete
1382 load stopped and the reason for stopping.
1383
1384 Parameters:
1385 - p_etl_type: ETL type
1386 - p_load_type: ETL load type (INIT/INCR)
1387 =================================================================*/
1388
1389 PROCEDURE print_opi_org_bounds (p_etl_type IN VARCHAR2,
1390 p_load_type IN VARCHAR2) IS
1391
1392 l_proc_name CONSTANT VARCHAR2 (60) := 'print_opi_org_bounds';
1393 l_stmt_id NUMBER;
1394
1395 -- Cursor for all the org bounds
1396
1397 CURSOR opi_org_bounds_csr IS
1398 SELECT mp.organization_code,
1399 log.to_bound_id,
1400 decode (log.stop_reason_code,
1401 STOP_ALL_COSTED, 'All Costed',
1402 STOP_UNCOSTED, 'Uncosted',
1403 'Data Issue?') stop_reason,
1404 nvl (mmt.transaction_date, sysdate) data_until
1405 FROM opi_dbi_conc_prog_run_log log,
1406 mtl_parameters mp,
1407 mtl_material_transactions mmt
1408 WHERE log.driving_table_code = 'MMT'
1409 AND log.to_bound_id = mmt.transaction_id (+)
1410 AND log.bound_level_entity_id = mp.organization_id
1411 AND log.etl_type = p_etl_type
1412 AND log.load_type = p_load_type;
1413
1414 BEGIN
1415
1416 bis_collection_utilities.put_line('Enter print_opi_org_bounds() '||
1417 To_char(Sysdate, 'hh24:mi:ss dd-mon-yyyy'));
1418
1419 -- initialization block
1420 l_stmt_id := 0;
1421
1422 -- print the header
1423 l_stmt_id := 10;
1424
1425 bis_collection_utilities.put_line (
1426 RPAD ('Organization Code', 20) ||
1427 RPAD ('Last Collected Txn Id', 25) ||
1428 RPAD ('Data Collected Until', 25) ||
1429 RPAD ('Reason Stopped', 20));
1430
1431 bis_collection_utilities.put_line (
1432 RPAD ('-----------------', 20) ||
1433 RPAD ('---------------------', 25) ||
1434 RPAD ('--------------------', 25) ||
1435 RPAD ('--------------', 20));
1436 -- just print all the bounds
1437 l_stmt_id := 20;
1438 FOR opi_org_bounds_rec IN opi_org_bounds_csr
1439 LOOP
1440 bis_collection_utilities.put_line (
1441 RPAD (opi_org_bounds_rec.organization_code, 20) ||
1442 RPAD (opi_org_bounds_rec.to_bound_id, 25) ||
1443 RPAD (opi_org_bounds_rec.data_until, 25) ||
1444 RPAD (opi_org_bounds_rec.stop_reason, 20));
1445
1446 END LOOP;
1447
1448
1449 -- print table end
1450 l_stmt_id := 30;
1451
1452
1453 bis_collection_utilities.put_line(LPAD ('', 90, '-'));
1454
1455 RETURN;
1456
1457 bis_collection_utilities.put_line('Exit print_opi_org_bounds() '||
1458 To_char(Sysdate, 'hh24:mi:ss dd-mon-yyyy'));
1459
1460 EXCEPTION
1461
1462 WHEN OTHERS THEN
1463 --{
1464 rollback;
1465
1466 bis_collection_utilities.put_line ('Error when printing org bounds.');
1467
1471 END print_opi_org_bounds;
1468 RAISE; -- propagate exception to wrapper
1469 --}
1470
1472
1473
1474 /*========================================================================
1475
1476 Return true if some rows have bounds that show uncosted transactions.
1477 This can only happen for OPI sourced Material ETLs.
1478
1479 Such rows will be distinguished by the fact that their stop reason
1480 code will be STOP_UNCOSTED. This means that the stop reason code
1481 must not have been wiped out by the etl_report_success API
1482
1483 Parameters:
1484 - p_etl_type: ETL Type
1485
1486 Date Author Action
1487 04/23/03 Dinkar Gupta Wrote Function
1488 07/01/05 Julia Zhang Modified to refer to new log table
1489 ========================================================================*/
1490 FUNCTION bounds_uncosted (p_etl_type IN VARCHAR2,
1491 p_load_type IN VARCHAR2) RETURN BOOLEAN IS
1492
1493 l_proc_name CONSTANT VARCHAR2 (60) := 'bounds_uncosted';
1494 l_stmt_id NUMBER;
1495 l_bounds_uncosted BOOLEAN;
1496 l_warning NUMBER;
1497
1498 BEGIN
1499
1500 bis_collection_utilities.put_line('Enter bounds_uncosted() '||
1501 To_char(Sysdate, 'hh24:mi:ss dd-mon-yyyy'));
1502
1503 -- initialization block
1504 l_stmt_id := 0;
1505 l_bounds_uncosted := false;
1506 l_warning := g_ok;
1507
1508 -- check if any row has uncosted transactions
1509 l_stmt_id := 10;
1510 BEGIN
1511 SELECT g_warning
1512 INTO l_warning
1513 FROM OPI_DBI_CONC_PROG_RUN_LOG
1514 WHERE stop_reason_code = STOP_UNCOSTED
1515 AND etl_type = p_etl_type
1516 AND load_type = p_load_type
1517 AND rownum = 1;
1518 EXCEPTION
1519 WHEN NO_DATA_FOUND THEN
1520 l_warning := g_ok;
1521 END;
1522
1523 -- If there are uncosted transactions, return true
1524 l_stmt_id := 20;
1525 IF (l_warning = g_warning) THEN
1526 --{
1527 l_bounds_uncosted := true;
1528 --}
1529 END IF;
1530
1531 RETURN l_bounds_uncosted;
1532
1533 bis_collection_utilities.put_line('Exit bounds_uncosted() '||
1534 To_char(Sysdate, 'hh24:mi:ss dd-mon-yyyy'));
1535
1536 END bounds_uncosted;
1537
1538
1539 /*===================================================================================
1540 -- This API would insert the ORGANIZATION_ID, LEDGER_ID, LEGAL_ENTITY_ID,COST_TYPE_ID
1541 -- for all the process orgs. This API would be called from each ETL prior to calling
1542 -- OPM loads. Each ETL query would join to the table OPI_DBI_ORG_LE_TEMP to extract rows
1543 -- from gmf_transaction_valuation for an inventory organization corresponding to a
1544 -- specific legal entity/ledger(primary)/cost type/period.
1545
1546 Date Author Action
1547 11/29/05 Suhasini Wrote Procedure
1548 ======================================================================================*/
1549
1550 PROCEDURE load_opm_org_ledger_data
1551 IS
1552 l_stmt_no NUMBER :=0;
1553 l_proc_name VARCHAR2(40);
1554 l_debug_msg VARCHAR2(32767);
1555 l_debug_mode VARCHAR2(1);
1556 l_module_name VARCHAR2(40);
1557 BEGIN
1558 l_proc_name := 'load_opm_org_ledger_data';
1559 l_debug_mode := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
1560 l_module_name := FND_PROFILE.value('AFLOG_MODULE');
1561
1562 -- Deleting rows from the temp table to avoid any undesirable data.
1563
1564 l_stmt_no := 10;
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
1574
1575 l_stmt_no := 20;
1576 INSERT INTO OPI_DBI_ORG_LE_TEMP
1577 (
1578 organization_id ,
1579 ledger_id ,
1580 legal_entity_id ,
1581 valuation_cost_type_id
1582 )
1583 SELECT ood.organization_id ,
1584 gfp.ledger_id ,
1585 gfp.legal_entity_id ,
1586 gfp.cost_type_id
1587 FROM ORG_ORGANIZATION_DEFINITIONS ood,
1588 GMF_FISCAL_POLICIES gfp,
1589 MTL_PARAMETERS mp
1590 WHERE mp.process_enabled_flag = 'Y' --for OPM orgs only
1591 AND mp.organization_id = ood.organization_id
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
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
1608 END opi_dbi_bounds_pkg;