[Home] [Help]
PACKAGE BODY: APPS.MSD_DEM_COLLECT_LEVEL_TYPES
Source
1 PACKAGE BODY MSD_DEM_COLLECT_LEVEL_TYPES AS
2 /* $Header: msddemcltb.pls 120.3.12000000.2 2007/09/25 06:28:14 syenamar noship $ */
3
4 LOC_TEMPORARY_TABLE VARCHAR2(240) := 'MSD_DEM_LOCATIONS_GTT ';
5 LOC_POPULATE_TEMP_TABLE_VIEW VARCHAR2(240) := 'MSD_DEM_LOCATIONS_GTT_V';
6
7 v_collect_level_type varchar2(30);
8
9 procedure delete_duplicates(retcode out nocopy number)
10 as
11
12 cursor get_instances is
13 select instance_id
14 from msc_apps_instances
15 order by instance_id;
16
17 l_stmt varchar2(4000);
18
19 begin
20
21 for c_instance_id in get_instances loop
22
23 l_stmt := 'delete from msd_dem_items_gtt ' ||
24 ' where (inventory_item_id, sr_demand_class_pk) in ' ||
25 ' (select sr_inventory_item_id, sr_demand_class_pk ' ||
26 ' from msd_dem_items_gtt ' ||
27 ' group by sr_inventory_item_id, sr_demand_class_pk ' ||
28 ' having count(*) > 1) ' ||
29 ' and sr_instance_id > :1';
30
31 execute immediate l_stmt using c_instance_id.instance_id;
32
33 end loop;
34
35 exception
36 when others then
37 msd_dem_common_utilities.log_message(substr(SQLERRM,1,150));
38 msd_dem_common_utilities.log_debug(substr(SQLERRM,1,150));
39 retcode := -1;
40
41 end;
42
43
44 procedure populate_temporary_tables(errbuf OUT NOCOPY VARCHAR2,
45 retcode OUT NOCOPY NUMBER,
46 p_instance_id IN NUMBER,
47 p_collect_level_type IN NUMBER,
48 p_plan_id IN NUMBER)
49 as
50
51 l_stmt varchar2(4000);
52
53 l_sales_staging_table VARCHAR2(100) := NULL;
54
55 cursor get_plan_info is
56 select plan_type
57 from msc_plans
58 where plan_id = p_plan_id;
59
60 l_plan_type number;
61
62 begin
63
64 msd_dem_common_utilities.log_debug('In procedure populate_temporary_tables');
65 l_sales_staging_table := msd_dem_common_utilities.get_lookup_value('MSD_DEM_DM_STAGING_TABLES','SALES_STAGING_TABLE');
66 if (l_sales_staging_table IS NULL)
67 then
68 retcode := -1;
69 msd_dem_common_utilities.log_message ('Unable to find sales staging table name');
70 return;
71 end if;
72
73 if p_plan_id <> -1 then
74 open get_plan_info;
75 fetch get_plan_info into l_plan_type;
76 close get_plan_info;
77 else
78 l_plan_type := 0;
79 end if;
80
81 if p_collect_level_type = 1 then
82
83 msd_dem_query_utilities.get_query(retcode, l_stmt, 'LOCATION_GTT', p_instance_id, l_sales_staging_table);
84
85 if retcode = -1 then
86 return;
87 end if;
88
89 elsif p_collect_level_type = 2 then
90
91 msd_dem_query_utilities.get_query(retcode, l_stmt, 'ITEM_GTT', p_instance_id, l_sales_staging_table);
92
93 l_stmt := replace(l_stmt, 'C_SCHEMANAME', fnd_profile.value('MSD_DEM_SCHEMA'));
94
95 if retcode = -1 then
96 return;
97 end if;
98 else
99
100 retcode := -1;
101 return;
102
103 end if;
104
105 l_stmt := replace(l_stmt, 'C_DATA_PLAN_TYPE', l_plan_type);
106 l_stmt := replace(l_stmt, 'C_PLAN_ID', p_plan_id);
107 l_stmt := replace(l_stmt, 'C_INSTANCE_ID', p_instance_id);
108
109 if p_plan_id <> -1 then
110 delete_duplicates(retcode);
111 end if;
112
113 msd_dem_common_utilities.log_debug('Insert statement for populating temporary table for ' ||
114 v_collect_level_type || fnd_global.local_chr(10) || l_stmt || fnd_global.local_chr(10) ||
115 'Instance: ' || p_instance_id || fnd_global.local_chr(10));
116
117 msd_dem_common_utilities.log_debug('Start Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') || fnd_global.local_chr(10));
118 execute immediate l_stmt;
119 msd_dem_common_utilities.log_debug('End Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') || fnd_global.local_chr(10));
120
121 retcode := 0;
122
123 exception
124 when others then
125 errbuf := substr(SQLERRM,1,150);
126 dbms_output.put_line(errbuf);
127 msd_dem_common_utilities.log_message(errbuf);
128 msd_dem_common_utilities.log_debug(errbuf);
129 retcode := -1;
130
131 end;
132
133 procedure populate_demantra_staging(errbuf OUT NOCOPY VARCHAR2,
134 retcode OUT NOCOPY NUMBER,
135 p_instance_id IN NUMBER,
136 p_collect_level_type IN NUMBER,
137 p_plan_id IN NUMBER)
138
139 as
140
141 l_stmt varchar2(12000);
142
143 l_staging_table VARCHAR2(100) := NULL;
144 l_master_org NUMBER := NULL;
145 l_category_set_id NUMBER := NULL;
146
147 l_master_string VARCHAR2(1000) := NULL;
148 l_category_string VARCHAR2(1000) := NULL;
149
150 cursor get_instances is
151 select instance_id
152 from msc_apps_instances;
153
154 begin
155
156 msd_dem_common_utilities.log_debug('In procedure populate_demantra_staging');
157
158 if p_collect_level_type = 1 then
159 l_staging_table := msd_dem_common_utilities.get_lookup_value('MSD_DEM_DM_STAGING_TABLES','LOCATION_STAGING_TABLE');
160
161 msd_dem_query_utilities.get_query(retcode, l_stmt, 'LOCATIONS', p_instance_id, l_staging_table);
162
163 if retcode = -1 then
164 return;
165 end if;
166
167 elsif p_collect_level_type = 2 then
168
169 l_staging_table := msd_dem_common_utilities.get_lookup_value('MSD_DEM_DM_STAGING_TABLES','ITEM_STAGING_TABLE');
170
171 l_master_org := to_number(msd_dem_common_utilities.get_parameter_value (
172 p_instance_id,
173 'MSD_DEM_MASTER_ORG'));
174 l_category_set_id := to_number(msd_dem_common_utilities.get_parameter_value (
175 p_instance_id,
176 'MSD_DEM_CATEGORY_SET_NAME'));
177 IF ( l_master_org IS NULL)
178 THEN
179 retcode := -1;
180 msd_dem_common_utilities.log_message ('Master organization not set.');
181 return;
182 END IF;
183
184 msd_dem_query_utilities.get_query(retcode, l_stmt, 'ITEMS', p_instance_id, l_staging_table);
185
186 if retcode = -1 then
187 return;
188 end if;
189
190 end if;
191
192 msd_dem_common_utilities.log_debug('Insert statement for populating Demantra staging table for ' || v_collect_level_type || fnd_global.local_chr(10) || l_stmt || fnd_global.local_chr(10));
193 IF (p_collect_level_type = 2)
194 THEN
195 msd_dem_common_utilities.log_debug('Bind Variables - ');
196 msd_dem_common_utilities.log_debug('Master Organization Id - ' || to_char(l_master_org));
197 msd_dem_common_utilities.log_debug('Category Set Id - ' || to_char(l_category_set_id));
198 END IF;
199
200 msd_dem_common_utilities.log_debug('Start Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') || fnd_global.local_chr(10));
201 IF (p_collect_level_type = 1)
202 THEN
203 execute immediate l_stmt;
204 ELSIF (p_collect_level_type = 2)
205 THEN
206
207 if p_plan_id is not null then
208
209 l_master_string := 'decode(itt.sr_instance_id';
210 l_category_string := 'decode(item_master.sr_instance_id';
211
212 for get_instances_rec in get_instances loop
213 l_master_org := to_number(msd_dem_common_utilities.get_parameter_value (
214 get_instances_rec.instance_id,
215 'MSD_DEM_MASTER_ORG'));
216 l_category_set_id := to_number(msd_dem_common_utilities.get_parameter_value (
217 get_instances_rec.instance_id,
218 'MSD_DEM_CATEGORY_SET_NAME'));
219
220 l_master_string := l_master_string || ', ' || get_instances_rec.instance_id || ', ''' || l_master_org || '''';
221 l_category_string := l_category_string || ', ' || get_instances_rec.instance_id || ', ' || nvl(to_char(l_category_set_id), 'null');
222
223 end loop;
224
225 l_master_string := l_master_string || ')';
226 l_category_string := l_category_string || ')';
227
228 l_stmt := replace(l_stmt, 'C_INSTANCE_STRING', l_master_string);
229 l_stmt := replace(l_stmt, 'C_CATEGORY_STRING', l_category_string);
230
231 EXECUTE IMMEDIATE l_stmt;
232
233 else
234
235 l_stmt := replace(l_stmt, 'C_INSTANCE_STRING', '''' || l_master_org || '''');
236 l_stmt := replace(l_stmt, 'C_CATEGORY_STRING', l_category_set_id);
237 EXECUTE IMMEDIATE l_stmt;
238 end if;
239
240 END IF;
241 msd_dem_common_utilities.log_debug('End Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') || fnd_global.local_chr(10));
242
243 commit;
244
245 retcode := 0;
246
247 exception
248 when others then
249 errbuf := substr(SQLERRM,1,150);
250 dbms_output.put_line(errbuf);
251 msd_dem_common_utilities.log_message(errbuf);
252 msd_dem_common_utilities.log_debug(errbuf);
253 retcode := -1;
254
255 end;
256
257
258 PROCEDURE POPULATE_NEW_ITEMS (
259 errbuf OUT NOCOPY VARCHAR2,
260 retcode OUT NOCOPY NUMBER,
261 p_sr_instance_id IN NUMBER)
262 IS
263
264 /*** CURSORS ***/
265
266 CURSOR c_check_new_items
267 IS
268 SELECT 1
269 FROM dual
270 WHERE EXISTS (SELECT 1
271 FROM msd_dem_new_items
272 WHERE sr_instance_id = p_sr_instance_id
273 AND process_flag = 2);
274
275
276 /*** LOCAL VARIABLES ***/
277
278 x_errbuf VARCHAR2(200) := NULL;
279 x_retcode VARCHAR2(100) := NULL;
280
281 x_new_items_present NUMBER := NULL;
282
283 x_sql VARCHAR2(32000) := NULL;
284
285 x_item_staging_table VARCHAR2(100) := NULL;
286 x_master_org NUMBER := NULL;
287 x_category_set_id NUMBER := NULL;
288
289 TYPE INV_ITEM_ID_TAB IS TABLE OF msd_dem_new_items.inventory_item_id%TYPE;
290 x_inv_item_id_tab INV_ITEM_ID_TAB;
291
292 BEGIN
293
294 msd_dem_common_utilities.log_debug ('Entering: msd_dem_collect_level_types.populate_new_items');
295
296
297 msd_dem_common_utilities.log_message (' Populate New Items ');
298 msd_dem_common_utilities.log_message (' --------------------');
299
300 /* Check if there are any yet to be processed NPIs */
301 OPEN c_check_new_items;
302 FETCH c_check_new_items INTO x_new_items_present;
303 CLOSE c_check_new_items;
304
305 IF (x_new_items_present = 1)
306 THEN
307 msd_dem_common_utilities.log_message ('Found new items for processing');
308
309 x_item_staging_table := msd_dem_common_utilities.get_lookup_value('MSD_DEM_DM_STAGING_TABLES','ITEM_STAGING_TABLE');
310 x_master_org := to_number(msd_dem_common_utilities.get_parameter_value (
311 p_sr_instance_id,
312 'MSD_DEM_MASTER_ORG'));
313 x_category_set_id := to_number(msd_dem_common_utilities.get_parameter_value (
314 p_sr_instance_id,
315 'MSD_DEM_CATEGORY_SET_NAME'));
316
317 IF ( x_master_org IS NULL)
318 THEN
319 retcode := 1;
320 errbuf := 'Master organization not set.';
321 msd_dem_common_utilities.log_message ('Warning(1): msd_dem_collect_level_types.populate_new_items');
322 msd_dem_common_utilities.log_message (errbuf);
323 RETURN;
324 END IF;
325
326 msd_dem_query_utilities.get_query(x_retcode, x_sql, 'NEW_ITEMS', p_sr_instance_id, x_item_staging_table);
327
328 IF (x_retcode = -1)
329 THEN
330 retcode := 1;
331 errbuf := 'Unable to get the query for populating new items into item staging table';
332 msd_dem_common_utilities.log_message ('Warning(2): msd_dem_collect_level_types.populate_new_items');
333 msd_dem_common_utilities.log_message (errbuf);
334 RETURN;
335 END IF;
336
337 msd_dem_common_utilities.log_debug ('Query - ');
338 msd_dem_common_utilities.log_debug ('Bind Variables - ');
339 msd_dem_common_utilities.log_debug ('Source Instance Id - ' || to_char(p_sr_instance_id));
340 msd_dem_common_utilities.log_debug ('Master Organization Id - ' || to_char(x_master_org));
341 msd_dem_common_utilities.log_debug ('Category Set Id - ' || to_char(x_category_set_id));
342
343 msd_dem_common_utilities.log_debug ('Query Start Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
344 EXECUTE IMMEDIATE x_sql USING p_sr_instance_id, p_sr_instance_id, x_master_org, x_category_set_id;
345 msd_dem_common_utilities.log_debug ('Query End Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
346
347 /* Set the process_flag */
348 UPDATE msd_dem_new_items
349 SET process_flag = 1
350 WHERE sr_instance_id = p_sr_instance_id
351 AND process_flag = 2;
352
353 COMMIT;
354
355 ELSE
356 msd_dem_common_utilities.log_message ('No new items found for processing');
357 END IF;
358
359 msd_dem_common_utilities.log_debug ('Exiting: msd_dem_collect_level_types.populate_new_items');
360
361 retcode := 0;
362
363 EXCEPTION
364 WHEN OTHERS THEN
365 retcode := 1;
366 errbuf := substr(SQLERRM,1,150);
367 msd_dem_common_utilities.log_message ('Exception: msd_dem_collect_level_types.populate_new_items');
368 msd_dem_common_utilities.log_message (errbuf);
369 RETURN;
370
371 END POPULATE_NEW_ITEMS;
372
373
374 procedure truncate_tables(errbuf OUT NOCOPY VARCHAR2,
375 retcode OUT NOCOPY NUMBER,
376 p_collect_level_type IN NUMBER)
377
378 as
379
380 l_stmt varchar2(200);
381
382 begin
383
384 msd_dem_common_utilities.log_debug('In procedure truncate_tables');
385
386 l_stmt := 'truncate table ';
387
388 if p_collect_level_type = 1 then
389
390 l_stmt := l_stmt || msd_dem_common_utilities.get_lookup_value('MSD_DEM_DM_STAGING_TABLES','LOCATION_STAGING_TABLE');
391
392 elsif p_collect_level_type = 2 then
393
394 l_stmt := l_stmt || msd_dem_common_utilities.get_lookup_value('MSD_DEM_DM_STAGING_TABLES','ITEM_STAGING_TABLE');
395
396 end if;
397 if l_stmt = 'truncate table ' then
398 msd_dem_common_utilities.log_message('Staging table not found');
399 retcode := -1;
400 return;
401 end if;
402
403 msd_dem_common_utilities.log_debug('Truncate Statement for ' || v_collect_level_type || fnd_global.local_chr(10) || l_stmt || fnd_global.local_chr(10));
404
405
406 msd_dem_common_utilities.log_debug('Start Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') || fnd_global.local_chr(10));
407 execute immediate l_stmt;
408 execute immediate l_stmt || '_err'; --saravan Bug# 6357056
409 msd_dem_common_utilities.log_debug('Truncated'|| l_stmt || '_err'||'Table'); -- saravan
410 msd_dem_common_utilities.log_debug('End Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') || fnd_global.local_chr(10));
411
412 retcode := 0;
413
414 exception
415 when others then
416 errbuf := substr(SQLERRM,1,150);
417 msd_dem_common_utilities.log_message(errbuf);
418 msd_dem_common_utilities.log_debug(errbuf);
419 retcode := -1;
420
421 end;
422
423 procedure collect_levels(errbuf OUT NOCOPY VARCHAR2,
424 retcode OUT NOCOPY NUMBER,
425 p_instance_id IN NUMBER,
426 p_collect_level_type IN NUMBER,
427 p_plan_id IN NUMBER DEFAULT -1)
428
429 as
430
431 begin
432
433
434 select decode(p_collect_level_type, 1 , 'Locations', 2, 'Items') into v_collect_level_type from dual;
435
436 msd_dem_common_utilities.log_message('Collecting '|| v_collect_level_type || fnd_global.local_chr(10));
437 msd_dem_common_utilities.log_debug('Collecting '|| v_collect_level_type || fnd_global.local_chr(10));
438
439 msd_dem_common_utilities.log_message('Step 1: Cleaning up staging tables for ' || v_collect_level_type || fnd_global.local_chr(10));
440 msd_dem_common_utilities.log_debug('Step 1: Cleaning up staging tables for ' || v_collect_level_type || fnd_global.local_chr(10));
441 truncate_tables(errbuf,retcode,p_collect_level_type);
442
443 if retcode = -1 then
444 return;
445 end if;
446
447 msd_dem_common_utilities.log_message('Step 2: Populating Temporary Tables ' || v_collect_level_type || fnd_global.local_chr(10));
448 msd_dem_common_utilities.log_debug('Step 2: Populating Temporary Tables ' || v_collect_level_type || fnd_global.local_chr(10));
449 populate_temporary_tables(errbuf,retcode,p_instance_id,p_collect_level_type, p_plan_id);
450
451 if retcode = -1 then
452 return;
453 end if;
454
455 msd_dem_common_utilities.log_message('Step 3: Populating Demantra Staging Tables ' || v_collect_level_type || fnd_global.local_chr(10));
456 msd_dem_common_utilities.log_debug('Step 3: Populating Demantra Staging Tables ' || v_collect_level_type || fnd_global.local_chr(10));
457 populate_demantra_staging(errbuf,retcode, p_instance_id, p_collect_level_type, p_plan_id);
458
459 if retcode = -1 then
460 return;
461 end if;
462
463 /* Collect NPIs */
464 /* Bug# 5869314
465 if (p_collect_level_type = 2) then
466 msd_dem_common_utilities.log_debug('Step 4: Populate New Items ');
467 populate_new_items (errbuf,retcode, p_instance_id);
468 */
469 /* Failure of NPIs collection will be a 'Warning' and not 'Error' */
470 /* Bug# 5869314
471 if retcode = 1 then
472 msd_dem_common_utilities.log_message('Warning: msd_dem_collect_level_types.collect_levels');
473 msd_dem_common_utilities.log_message('Collect new items completed with warning(s).');
474 return;
475 end if;
476 end if;
477 */
478 /* Call Custom Hook for Item/Location */
479 IF (p_collect_level_type = 2) /* ITEM */
480 THEN
481
482 msd_dem_common_utilities.log_debug ('Begin Call Custom Hook msd_dem_custom_hooks.item_hook - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
483
484 msd_dem_custom_hooks.item_hook (
485 errbuf,
486 retcode);
487
488 msd_dem_common_utilities.log_debug ('End Call Custom Hook msd_dem_custom_hooks.item_hook - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
489
490 IF (retcode = -1)
491 THEN
492 msd_dem_common_utilities.log_message ('Error: msd_dem_collect_level_types.collect_levels - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
493 msd_dem_common_utilities.log_message ('Error in call to custom hook msd_dem_custom_hooks.item_hook ');
494 RETURN;
495 END IF;
496
497 /* Analyze Item Staging Table */
498 msd_dem_common_utilities.log_debug ('Begin Analyze item staging table - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
499
500 msd_dem_collect_history_data.analyze_table (
501 errbuf,
502 retcode,
503 msd_dem_common_utilities.get_lookup_value('MSD_DEM_DM_STAGING_TABLES','ITEM_STAGING_TABLE'));
504
505 msd_dem_common_utilities.log_debug ('End Analyze item staging table - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
506
507 IF (retcode = 1)
508 THEN
509 msd_dem_common_utilities.log_message ('Warning: msd_dem_collect_level_types.collect_levels - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
510 msd_dem_common_utilities.log_message ('Error while analyzing item staging table. ');
511 END IF;
512
513 ELSIF (p_collect_level_type = 1) /* LOCATION */
514 THEN
515
516 msd_dem_common_utilities.log_debug ('Begin Call Custom Hook msd_dem_custom_hooks.location_hook - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
517
518 msd_dem_custom_hooks.location_hook (
519 errbuf,
520 retcode);
521
522 msd_dem_common_utilities.log_debug ('End Call Custom Hook msd_dem_custom_hooks.location_hook - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
523
524 IF (retcode = -1)
525 THEN
526 msd_dem_common_utilities.log_message ('Error: msd_dem_collect_level_types.collect_levels - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
527 msd_dem_common_utilities.log_message ('Error in call to custom hook msd_dem_custom_hooks.location_hook ');
528 RETURN;
529 END IF;
530
531 /* Analyze Location Staging Table */
532 msd_dem_common_utilities.log_debug ('Begin Analyze location staging table - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
533
534 msd_dem_collect_history_data.analyze_table (
535 errbuf,
536 retcode,
537 msd_dem_common_utilities.get_lookup_value('MSD_DEM_DM_STAGING_TABLES','LOCATION_STAGING_TABLE'));
538
539 msd_dem_common_utilities.log_debug ('End Analyze location staging table - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
540
541 IF (retcode = 1)
542 THEN
543 msd_dem_common_utilities.log_message ('Warning: msd_dem_collect_level_types.collect_levels - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
544 msd_dem_common_utilities.log_message ('Error while analyzing location staging table. ');
545 END IF;
546
547 END IF;
548
549 exception
550 when others then
551 errbuf := substr(SQLERRM,1,150);
552 msd_dem_common_utilities.log_message(errbuf);
553 msd_dem_common_utilities.log_debug(errbuf);
554 retcode := -1;
555
556 end collect_levels;
557
558 END MSD_DEM_COLLECT_LEVEL_TYPES;