[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.11.12020000.2 2012/09/25 10:22:06 kkhatri ship $ */
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 /*
10 * This function will check whether the data model of demantra is that of 7.2 or 7.3 version.
11 * levels used item , item_attribute_1 & attributes used item_description
12 * if the data model looks like 7.2 then return OLD
13 * if the data model doesnot look like 7.2 then return NEW
14 *
15 */
16
17 --bug 13716381 --kkhatri--12.3
18 --bug 14670484 --kkhatri--12.2.1
19 FUNCTION check_data_model(
20 errbuf OUT NOCOPY VARCHAR2,
21 retcode OUT NOCOPY NUMBER
22 )
23 RETURN varchar2
24
25 IS
26 x_sql varchar2(1000) := null ;
27 x_dem_schema varchar2(100) := FND_PROFILE.value('MSD_DEM_SCHEMA');
28 flag_720_ic NUMBER;
29 flag_720_id NUMBER;
30 flag_720_ia NUMBER;
31 flag_dm_active NUMBER;
32
33 BEGIN
34
35 msd_dem_common_utilities.log_debug ('Entering: msd_dem_collect_level_types.check_data_model');
36
37 x_sql := ' SELECT NVL(COUNT(1),0) FROM ' || x_dem_schema || '.DM_WIZ_DM_DEF '
38 || ' WHERE DATA_MODEL_TREE_ID = 15'
39 || ' AND IS_ACTIVE = 1' ;
40 EXECUTE IMMEDIATE x_sql INTO flag_dm_active ;
41
42 IF (flag_dm_active = 1) THEN
43
44 /*Query for 720 + item code */
45 x_sql := ' SELECT NVL(COUNT(1),0) FROM ' || x_dem_schema || '.E_PLAN_TREE '
46 || ' WHERE UPPER(TABLE_NAME) LIKE ''T_SRC_ITEM_TMPL'' '
47 || ' AND UPPER(FIELD_NAME) LIKE ''DM_ITEM_CODE'' '
48 || ' AND UPPER(E_PLAN_NAME) = ''I_ATT_1'' '
49 || ' AND UPPER(E_PLAN_TABLE_NAME) = ''T_EP_I_ATT_1'' '
50 || ' AND MODEL_VERSION = 15';
51 EXECUTE IMMEDIATE x_sql INTO flag_720_ic ;
52
53 /*Query for 720 + item desc*/
54 x_sql := ' SELECT NVL(COUNT(1),0) FROM ' || x_dem_schema || '.E_PLAN_TREE '
55 || ' WHERE UPPER(TABLE_NAME) = ''T_SRC_ITEM_TMPL'' '
56 || ' AND UPPER(FIELD_NAME) = ''DM_ITEM_DESC'' '
57 || ' AND UPPER(E_PLAN_NAME) = ''I_ATT_1_DESC'' '
58 || ' AND MODEL_VERSION = 15';
59 EXECUTE IMMEDIATE x_sql INTO flag_720_id ;
60
61 /*Query for 720 + item attribute 1 */
62 x_sql := ' SELECT NVL(COUNT(1),0) FROM ' || x_dem_schema || '.E_PLAN_TREE '
63 || ' WHERE UPPER(TABLE_NAME) LIKE ''T_SRC_ITEM_TMPL'' '
64 || ' AND UPPER(FIELD_NAME) LIKE ''T_EP_I_ATT_1'' '
65 || ' AND MODEL_VERSION = 15';
66 EXECUTE IMMEDIATE x_sql INTO flag_720_ia ;
67
68 IF (flag_720_ic = 1 and flag_720_id = 1 and flag_720_ia = 0 ) THEN
69 msd_dem_common_utilities.log_debug ('Data Model is equivalent to 7.2 ');
70 return 'OLD' ;
71 ELSE
72 msd_dem_common_utilities.log_debug ('Data Model is equivalent to 7.3 or might be customized. ');
73 return 'NEW';
74 END IF;
75
76 END IF;
77 RETURN 'NEW';
78
79 EXCEPTION
80 WHEN OTHERS THEN
81 retcode := 1;
82 errbuf := substr(SQLERRM,1,150);
83 msd_dem_common_utilities.log_message ('Exception: msd_dem_collect_level_types.check_data_model');
84 msd_dem_common_utilities.log_message (errbuf);
85 RETURN 'NEW' ;
86
87 END check_data_model;
88
89 procedure delete_duplicates(retcode out nocopy number)
90 as
91
92 cursor get_instances is
93 select instance_id
94 from msc_apps_instances
95 order by instance_id;
96
97 l_stmt varchar2(4000);
98
99 begin
100
101 for c_instance_id in get_instances loop
102
103 l_stmt := 'delete from msd_dem_items_gtt ' ||
104 ' where (inventory_item_id, sr_demand_class_pk) in ' ||
105 ' (select sr_inventory_item_id, sr_demand_class_pk ' ||
106 ' from msd_dem_items_gtt ' ||
107 ' group by sr_inventory_item_id, sr_demand_class_pk ' ||
108 ' having count(*) > 1) ' ||
109 ' and sr_instance_id > :1';
110
111 execute immediate l_stmt using c_instance_id.instance_id;
112
113 end loop;
114
115 exception
116 when others then
117 msd_dem_common_utilities.log_message(substr(SQLERRM,1,150));
118 msd_dem_common_utilities.log_debug(substr(SQLERRM,1,150));
119 retcode := -1;
120
121 end;
122
123
124 procedure populate_temporary_tables(errbuf OUT NOCOPY VARCHAR2,
125 retcode OUT NOCOPY NUMBER,
126 p_instance_id IN NUMBER,
127 p_collect_level_type IN NUMBER,
128 p_plan_id IN NUMBER)
129 as
130
131 l_stmt varchar2(4000);
132
133 l_sales_staging_table VARCHAR2(100) := NULL;
134
135 cursor get_plan_info is
136 select plan_type
137 from msc_plans
138 where plan_id = p_plan_id;
139
140 l_plan_type number;
141
142 begin
143
144 msd_dem_common_utilities.log_debug('In procedure populate_temporary_tables');
145 l_sales_staging_table := msd_dem_common_utilities.get_lookup_value('MSD_DEM_DM_STAGING_TABLES','SALES_STAGING_TABLE');
146 if (l_sales_staging_table IS NULL)
147 then
148 retcode := -1;
149 msd_dem_common_utilities.log_message ('Unable to find sales staging table name');
150 return;
151 end if;
152
153 if p_plan_id <> -1 then
154 open get_plan_info;
155 fetch get_plan_info into l_plan_type;
156 close get_plan_info;
157 else
158 l_plan_type := 0;
159 end if;
160
161 if p_collect_level_type = 1 then
162
163 msd_dem_query_utilities.get_query(retcode, l_stmt, 'LOCATION_GTT', p_instance_id, l_sales_staging_table);
164
165 if retcode = -1 then
166 return;
167 end if;
168
169 elsif p_collect_level_type = 2 then
170
171 msd_dem_query_utilities.get_query(retcode, l_stmt, 'ITEM_GTT', p_instance_id, l_sales_staging_table);
172
173 l_stmt := replace(l_stmt, 'C_SCHEMANAME', fnd_profile.value('MSD_DEM_SCHEMA'));
174
175 if retcode = -1 then
176 return;
177 end if;
178 else
179
180 retcode := -1;
181 return;
182
183 end if;
184
185 l_stmt := replace(l_stmt, 'C_DATA_PLAN_TYPE', l_plan_type);
186 l_stmt := replace(l_stmt, 'C_PLAN_ID', p_plan_id);
187 l_stmt := replace(l_stmt, 'C_INSTANCE_ID', p_instance_id);
188
189 if p_plan_id <> -1 then
190 delete_duplicates(retcode);
191 end if;
192
193 msd_dem_common_utilities.log_debug('Insert statement for populating temporary table for ' ||
194 v_collect_level_type || fnd_global.local_chr(10) || l_stmt || fnd_global.local_chr(10) ||
195 'Instance: ' || p_instance_id || fnd_global.local_chr(10));
196
197 msd_dem_common_utilities.log_debug('Start Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') || fnd_global.local_chr(10));
198 execute immediate l_stmt;
199 msd_dem_common_utilities.log_debug('End Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') || fnd_global.local_chr(10));
200
201 retcode := 0;
202
203 exception
204 when others then
205 errbuf := substr(SQLERRM,1,150);
206 dbms_output.put_line(errbuf);
207 msd_dem_common_utilities.log_message(errbuf);
208 msd_dem_common_utilities.log_debug(errbuf);
209 retcode := -1;
210
211 end;
212
213 procedure populate_demantra_staging(errbuf OUT NOCOPY VARCHAR2,
214 retcode OUT NOCOPY NUMBER,
215 p_instance_id IN NUMBER,
216 p_collect_level_type IN NUMBER,
217 p_plan_id IN NUMBER)
218
219 as
220
221 l_stmt varchar2(32000);
222
223 l_staging_table VARCHAR2(100) := NULL;
224 l_master_org NUMBER := NULL;
225 l_category_set_id NUMBER := NULL;
226
227 l_master_string VARCHAR2(1000) := NULL;
228 l_category_string VARCHAR2(1000) := NULL;
229
230 cursor get_instances is
231 select instance_id
232 from msc_apps_instances;
233
234 x_dem_version VARCHAR2(10) := msd_dem_common_utilities.get_demantra_version;
235
236 begin
237
238 msd_dem_common_utilities.log_debug('In procedure populate_demantra_staging');
239
240 if p_collect_level_type = 1 then
241 l_staging_table := msd_dem_common_utilities.get_lookup_value('MSD_DEM_DM_STAGING_TABLES','LOCATION_STAGING_TABLE');
242
243 msd_dem_query_utilities.get_query(retcode, l_stmt, 'LOCATIONS', p_instance_id, l_staging_table);
244
245 if retcode = -1 then
246 return;
247 end if;
248
249 elsif p_collect_level_type = 2 then
250
251 l_staging_table := msd_dem_common_utilities.get_lookup_value('MSD_DEM_DM_STAGING_TABLES','ITEM_STAGING_TABLE');
252
253 l_master_org := to_number(msd_dem_common_utilities.get_parameter_value (
254 p_instance_id,
255 'MSD_DEM_MASTER_ORG'));
256 l_category_set_id := to_number(msd_dem_common_utilities.get_parameter_value (
257 p_instance_id,
258 'MSD_DEM_CATEGORY_SET_NAME'));
259 IF ( l_master_org IS NULL)
260 THEN
261 retcode := -1;
262 msd_dem_common_utilities.log_message ('Master organization not set.');
263 return;
264 END IF;
265
266 -- bug 9341065 -- nallkuma
267 --bug 13716381 --kkhatri--12.3
268 --bug 14670484 --kkhatri--12.2.1
269 IF (x_dem_version = '7.2' or check_data_model(errbuf,retcode) = 'OLD')
270 THEN
271 msd_dem_query_utilities.get_query(retcode, l_stmt, 'ITEMS', p_instance_id, l_staging_table);
272 ELSE
273 msd_dem_query_utilities.get_query(retcode, l_stmt, 'ITEMS_730', p_instance_id, l_staging_table);
274 END IF;
275
276 if retcode = -1 then
277 return;
278 end if;
279
280 end if;
281
282 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));
283 IF (p_collect_level_type = 2)
284 THEN
285 msd_dem_common_utilities.log_debug('Bind Variables - ');
286 msd_dem_common_utilities.log_debug('Master Organization Id - ' || to_char(l_master_org));
287 msd_dem_common_utilities.log_debug('Category Set Id - ' || to_char(l_category_set_id));
288 END IF;
289
290 msd_dem_common_utilities.log_debug('Start Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') || fnd_global.local_chr(10));
291 IF (p_collect_level_type = 1)
292 THEN
293 execute immediate l_stmt;
294 ELSIF (p_collect_level_type = 2)
295 THEN
296
297 if p_plan_id is not null then
298
299 l_master_string := 'decode(itt.sr_instance_id';
300 l_category_string := 'decode(item_master.sr_instance_id';
301
302 for get_instances_rec in get_instances loop
303 l_master_org := to_number(msd_dem_common_utilities.get_parameter_value (
304 get_instances_rec.instance_id,
305 'MSD_DEM_MASTER_ORG'));
306 l_category_set_id := to_number(msd_dem_common_utilities.get_parameter_value (
307 get_instances_rec.instance_id,
308 'MSD_DEM_CATEGORY_SET_NAME'));
309
310 l_master_string := l_master_string || ', ' || get_instances_rec.instance_id || ', ''' || l_master_org || '''';
311 l_category_string := l_category_string || ', ' || get_instances_rec.instance_id || ', ' || nvl(to_char(l_category_set_id), 'null');
312
313 end loop;
314
315 l_master_string := l_master_string || ')';
316 l_category_string := l_category_string || ')';
317
318 l_stmt := replace(l_stmt, 'C_INSTANCE_STRING', l_master_string);
319 l_stmt := replace(l_stmt, 'C_CATEGORY_STRING', l_category_string);
320
321 EXECUTE IMMEDIATE l_stmt;
322
323 else
324
325 l_stmt := replace(l_stmt, 'C_INSTANCE_STRING', '''' || l_master_org || '''');
326 l_stmt := replace(l_stmt, 'C_CATEGORY_STRING', l_category_set_id);
327 EXECUTE IMMEDIATE l_stmt;
328 end if;
329
330 END IF;
331 msd_dem_common_utilities.log_debug('End Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') || fnd_global.local_chr(10));
332
333 commit;
334
335 retcode := 0;
336
337 exception
338 when others then
339 errbuf := substr(SQLERRM,1,150);
340 dbms_output.put_line(errbuf);
341 msd_dem_common_utilities.log_message(errbuf);
342 msd_dem_common_utilities.log_debug(errbuf);
343 retcode := -1;
344
345 end;
346
347
348 PROCEDURE POPULATE_NEW_ITEMS (
349 errbuf OUT NOCOPY VARCHAR2,
350 retcode OUT NOCOPY NUMBER,
351 p_sr_instance_id IN NUMBER)
352 IS
353
354 /*** CURSORS ***/
355
356 CURSOR c_check_new_items
357 IS
358 SELECT 1
359 FROM dual
360 WHERE EXISTS (SELECT 1
361 FROM msd_dem_new_items
362 WHERE sr_instance_id = p_sr_instance_id
363 AND process_flag = 2);
364
365
366 /*** LOCAL VARIABLES ***/
367
368 x_errbuf VARCHAR2(200) := NULL;
369 x_retcode VARCHAR2(100) := NULL;
370
371 x_new_items_present NUMBER := NULL;
372
373 x_sql VARCHAR2(32000) := NULL;
374
375 x_item_staging_table VARCHAR2(100) := NULL;
376 x_master_org NUMBER := NULL;
377 x_category_set_id NUMBER := NULL;
378
379 TYPE INV_ITEM_ID_TAB IS TABLE OF msd_dem_new_items.inventory_item_id%TYPE;
380 x_inv_item_id_tab INV_ITEM_ID_TAB;
381
382 BEGIN
383
384 msd_dem_common_utilities.log_debug ('Entering: msd_dem_collect_level_types.populate_new_items');
385
386
387 msd_dem_common_utilities.log_message (' Populate New Items ');
388 msd_dem_common_utilities.log_message (' --------------------');
389
390 /* Check if there are any yet to be processed NPIs */
391 OPEN c_check_new_items;
392 FETCH c_check_new_items INTO x_new_items_present;
393 CLOSE c_check_new_items;
394
395 IF (x_new_items_present = 1)
396 THEN
397 msd_dem_common_utilities.log_message ('Found new items for processing');
398
399 x_item_staging_table := msd_dem_common_utilities.get_lookup_value('MSD_DEM_DM_STAGING_TABLES','ITEM_STAGING_TABLE');
400 x_master_org := to_number(msd_dem_common_utilities.get_parameter_value (
401 p_sr_instance_id,
402 'MSD_DEM_MASTER_ORG'));
403 x_category_set_id := to_number(msd_dem_common_utilities.get_parameter_value (
404 p_sr_instance_id,
405 'MSD_DEM_CATEGORY_SET_NAME'));
406
407 IF ( x_master_org IS NULL)
408 THEN
409 retcode := 1;
410 errbuf := 'Master organization not set.';
411 msd_dem_common_utilities.log_message ('Warning(1): msd_dem_collect_level_types.populate_new_items');
412 msd_dem_common_utilities.log_message (errbuf);
413 RETURN;
414 END IF;
415
416 msd_dem_query_utilities.get_query(x_retcode, x_sql, 'NEW_ITEMS', p_sr_instance_id, x_item_staging_table);
417
418 IF (x_retcode = -1)
419 THEN
420 retcode := 1;
421 errbuf := 'Unable to get the query for populating new items into item staging table';
422 msd_dem_common_utilities.log_message ('Warning(2): msd_dem_collect_level_types.populate_new_items');
423 msd_dem_common_utilities.log_message (errbuf);
424 RETURN;
425 END IF;
426
427 msd_dem_common_utilities.log_debug ('Query - ');
428 msd_dem_common_utilities.log_debug ('Bind Variables - ');
429 msd_dem_common_utilities.log_debug ('Source Instance Id - ' || to_char(p_sr_instance_id));
430 msd_dem_common_utilities.log_debug ('Master Organization Id - ' || to_char(x_master_org));
431 msd_dem_common_utilities.log_debug ('Category Set Id - ' || to_char(x_category_set_id));
432
433 msd_dem_common_utilities.log_debug ('Query Start Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
434 EXECUTE IMMEDIATE x_sql USING p_sr_instance_id, p_sr_instance_id, x_master_org, x_category_set_id;
435 msd_dem_common_utilities.log_debug ('Query End Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
436
437 /* Set the process_flag */
438 UPDATE msd_dem_new_items
439 SET process_flag = 1
440 WHERE sr_instance_id = p_sr_instance_id
441 AND process_flag = 2;
442
443 COMMIT;
444
445 ELSE
446 msd_dem_common_utilities.log_message ('No new items found for processing');
447 END IF;
448
449 msd_dem_common_utilities.log_debug ('Exiting: msd_dem_collect_level_types.populate_new_items');
450
451 retcode := 0;
452
453 EXCEPTION
454 WHEN OTHERS THEN
455 retcode := 1;
456 errbuf := substr(SQLERRM,1,150);
457 msd_dem_common_utilities.log_message ('Exception: msd_dem_collect_level_types.populate_new_items');
458 msd_dem_common_utilities.log_message (errbuf);
459 RETURN;
460
461 END POPULATE_NEW_ITEMS;
462
463
464 procedure truncate_tables(errbuf OUT NOCOPY VARCHAR2,
465 retcode OUT NOCOPY NUMBER,
466 p_collect_level_type IN NUMBER)
467
468 as
469
470 l_stmt varchar2(200);
471
472 begin
473
474 msd_dem_common_utilities.log_debug('In procedure truncate_tables');
475
476 l_stmt := 'truncate table ';
477
478 if p_collect_level_type = 1 then
479
480 l_stmt := l_stmt || msd_dem_common_utilities.get_lookup_value('MSD_DEM_DM_STAGING_TABLES','LOCATION_STAGING_TABLE');
481
482 elsif p_collect_level_type = 2 then
483
484 l_stmt := l_stmt || msd_dem_common_utilities.get_lookup_value('MSD_DEM_DM_STAGING_TABLES','ITEM_STAGING_TABLE');
485
486 end if;
487 if l_stmt = 'truncate table ' then
488 msd_dem_common_utilities.log_message('Staging table not found');
489 retcode := -1;
490 return;
491 end if;
492
493 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));
494
495
496 msd_dem_common_utilities.log_debug('Start Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') || fnd_global.local_chr(10));
497 execute immediate l_stmt;
498 execute immediate l_stmt || '_err'; --saravan Bug# 6357056
499 msd_dem_common_utilities.log_debug('Truncated'|| l_stmt || '_err'||'Table'); -- saravan
500 msd_dem_common_utilities.log_debug('End Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') || fnd_global.local_chr(10));
501
502 retcode := 0;
503
504 exception
505 when others then
506 errbuf := substr(SQLERRM,1,150);
507 msd_dem_common_utilities.log_message(errbuf);
508 msd_dem_common_utilities.log_debug(errbuf);
509 retcode := -1;
510
511 end;
512
513
514
515 /*
516 *
517 */
518 PROCEDURE POPULATE_ORGANIZATION_TYPE (
519 errbuf OUT NOCOPY VARCHAR2,
520 retcode OUT NOCOPY VARCHAR2,
521 p_for_spf IN NUMBER )
522 IS
523
524 var_schema VARCHAR2(100) DEFAULT fnd_profile.value('MSD_DEM_SCHEMA');
525 var_count NUMBER DEFAULT 0;
526
527 var_org_type_code VARCHAR2(255) DEFAULT NULL;
528 var_org_type_desc VARCHAR2(255) DEFAULT NULL;
529
530 BEGIN
531
532 msd_dem_common_utilities.log_debug ('Entering msd_dem_collect_level_types.populate_organization_type...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
533
534 /* Check if the new columns for Org Type are available in T_SRC_LOC_TMPL */
535 SELECT count(*)
536 INTO var_count
537 FROM dba_tab_columns
538 WHERE owner = var_schema
539 AND table_name = 'T_SRC_LOC_TMPL'
540 AND column_name in ('ORG_TYPE_CODE', 'ORG_TYPE_DESC');
541
542 IF (var_count <> 2)
543 THEN
544 msd_dem_common_utilities.log_debug('Columns ORG_TYPE_CODE and ORG_TYPE_DESC not found in T_SRC_LOC_TMPL. Hence exiting without processing');
545 RETURN;
546 END IF;
547
548 /* Check if the new table T_EP_ORG_TYPE is available */
549 var_count := 0 ;
550
551 SELECT count(1)
552 INTO var_count
553 FROM dba_tables
554 WHERE owner = var_schema
555 AND table_name = 'T_EP_ORG_TYPE';
556
557 IF (var_count <> 1)
558 THEN
559 msd_dem_common_utilities.log_debug('Table T_EP_ORG_TYPE not found. Hence exiting without processing');
560 RETURN;
561 END IF;
562
563 /* Get the org type code and description */
564 BEGIN
565
566 EXECUTE IMMEDIATE 'SELECT org_type_code, org_type_desc FROM ' || var_schema || '.t_ep_org_type '
567 || ' WHERE t_ep_org_type_ep_id = decode(' || to_char(p_for_spf) || ' , 1, 1, 0) '
568 INTO var_org_type_code, var_org_type_desc;
569
570 EXCEPTION
571 WHEN NO_DATA_FOUND THEN
572 IF (p_for_spf = 1)
573 THEN
574 var_org_type_code := '1';
575 var_org_type_desc := 'Spares Forecasting';
576 ELSE
577 var_org_type_code := '0';
578 var_org_type_desc := 'Demand Management';
579 END IF;
580
581 WHEN OTHERS THEN
582 msd_dem_common_utilities.log_debug('Error while trying to get org type code and desc. Hence exiting without processing');
583 retcode := -1;
584 errbuf := substr(SQLERRM,1,150);
585 RETURN;
586
587 END;
588
589 /* Update org type and code in t_src_loc_tmpl */
590 msd_dem_common_utilities.log_message ('Updating org type and code in location staging...');
591 EXECUTE IMMEDIATE 'UPDATE ' || var_schema || '.T_SRC_LOC_TMPL '
592 || ' SET org_type_code = ''' || var_org_type_code || ''', '
593 || ' org_type_desc = ''' || var_org_type_desc || ''' ';
594
595 COMMIT;
596
597 msd_dem_common_utilities.log_debug ('Exiting msd_dem_collect_level_types.populate_organization_type...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
598
599 EXCEPTION
600 WHEN OTHERS THEN
601 retcode := -1;
602 errbuf := substr(SQLERRM,1,150);
603 msd_dem_common_utilities.log_message ('msd_dem_collect_level_types.populate_organization_type - ERROR ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
604 msd_dem_common_utilities.log_message (errbuf);
605 RETURN;
606
607 END POPULATE_ORGANIZATION_TYPE;
608 /*
609 *
610 * This procedure updates the column t_src_item_tmpl.t_ep_i_att_10
611 * For items which are already available in demantra
612 * with (members of the item_type level) from t_ep_item & t_ep_i_att_10 tables.
613 * For items which are new to demantra
614 * not updated.
615 *
616 * This is done so that an item cannot have 2 item_type levels as parents
617 *
618 */
619 PROCEDURE populate_item_type(errbuf OUT NOCOPY VARCHAR2,
620 retcode OUT NOCOPY NUMBER )
621 AS
622
623 x_dem_schema VARCHAR2(100) DEFAULT fnd_profile.value('MSD_DEM_SCHEMA');
624 x_sql VARCHAR2(500);
625 x_count NUMBER(1) := 0 ;
626
627
628 BEGIN
629
630 msd_dem_common_utilities.log_debug ('Entering msd_dem_collect_level_types.populate_item_type...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
631
632 x_sql := ' select count(nvl(1,0)) from all_tables where table_name = ''T_EP_I_ATT_10'' and owner = ''' || x_dem_schema || '''' ;
633 EXECUTE IMMEDIATE x_sql INTO x_count ;
634
635 IF (x_count = 0) THEN
636 msd_dem_common_utilities.log_debug('Warning: T_EP_I_ATT_10 table doesnot exist in demantra schema.' );
637 msd_dem_common_utilities.log_debug ('Exiting msd_dem_collect_level_types.populate_item_type...No Action Perfomed...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
638 retcode := 1;
639 errbuf := 'T_EP_I_ATT_10 table doesnot exist in demantra schema.';
640 return;
641 END IF ;
642
643 x_sql := ' UPDATE ' || x_dem_schema || '.t_src_item_tmpl tmpl ' ||
644 ' SET t_ep_i_att_10 = NVL((SELECT it.i_att_10 ' ||
645 'FROM ' ||
646 x_dem_schema || '.t_ep_item tei, ' || x_dem_schema || '.t_ep_i_att_10 it ' ||
647 'WHERE
648 tmpl.dm_item_code = tei.item
649 AND tei.t_ep_i_att_10_ep_id = it.t_ep_i_att_10_ep_id)
650 ,t_ep_i_att_10) ';
651
652 EXECUTE IMMEDIATE x_sql ;
653 COMMIT;
654
655 msd_dem_common_utilities.log_debug('Update sql for item_type level - ' || x_sql);
656 msd_dem_common_utilities.log_debug ('Exiting msd_dem_collect_level_types.populate_item_type...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
657
658 EXCEPTION
659 WHEN OTHERS THEN
660 errbuf := substr(SQLERRM,1,150);
661 msd_dem_common_utilities.log_message(errbuf);
662 msd_dem_common_utilities.log_debug(errbuf);
663 RETCODE := -1;
664 END populate_item_type;
665
666
667
668 procedure collect_levels(errbuf OUT NOCOPY VARCHAR2,
669 retcode OUT NOCOPY NUMBER,
670 p_instance_id IN NUMBER,
671 p_collect_level_type IN NUMBER,
672 p_plan_id IN NUMBER DEFAULT -1,
673 p_for_spf IN NUMBER DEFAULT 2 )
674
675 as
676 l_schema_name VARCHAR2(100);
677 begin
678
679 -- BUG#8267074 nallkuma
680 l_schema_name := substr(msd_dem_common_utilities.get_lookup_value('MSD_DEM_DM_STAGING_TABLES','SALES_STAGING_TABLE')
681 , 1
682 , instr(msd_dem_common_utilities.get_lookup_value('MSD_DEM_DM_STAGING_TABLES','SALES_STAGING_TABLE'), '.')-1) ;
683
684 IF (l_schema_name = 'MSD' ) then -- BUG#8267074 nallkuma
685 msd_dem_common_utilities.log_message('NO ACTION performed related to levels since there is NO demantra schema found');
686 msd_dem_common_utilities.log_message('NO ACTION performed related to levels assuming its SRP collections');
687 return;
688 End if ;
689
690 select decode(p_collect_level_type, 1 , 'Locations', 2, 'Items') into v_collect_level_type from dual;
691
692 msd_dem_common_utilities.log_message('Collecting '|| v_collect_level_type || fnd_global.local_chr(10));
693 msd_dem_common_utilities.log_debug('Collecting '|| v_collect_level_type || fnd_global.local_chr(10));
694
695 msd_dem_common_utilities.log_message('Step 1: Cleaning up staging tables for ' || v_collect_level_type || fnd_global.local_chr(10));
696 msd_dem_common_utilities.log_debug('Step 1: Cleaning up staging tables for ' || v_collect_level_type || fnd_global.local_chr(10));
697 truncate_tables(errbuf,retcode,p_collect_level_type);
698
699 if retcode = -1 then
700 return;
701 end if;
702
703 msd_dem_common_utilities.log_message('Step 2: Populating Temporary Tables ' || v_collect_level_type || fnd_global.local_chr(10));
704 msd_dem_common_utilities.log_debug('Step 2: Populating Temporary Tables ' || v_collect_level_type || fnd_global.local_chr(10));
705 populate_temporary_tables(errbuf,retcode,p_instance_id,p_collect_level_type, p_plan_id);
706
707 if retcode = -1 then
708 return;
709 end if;
710
711 msd_dem_common_utilities.log_message('Step 3: Populating Demantra Staging Tables ' || v_collect_level_type || fnd_global.local_chr(10));
712 msd_dem_common_utilities.log_debug('Step 3: Populating Demantra Staging Tables ' || v_collect_level_type || fnd_global.local_chr(10));
713 populate_demantra_staging(errbuf,retcode, p_instance_id, p_collect_level_type, p_plan_id);
714
715 if retcode = -1 then
716 return;
717 end if;
718
719 /* Populate Organization Type */
720 IF (p_collect_level_type = 1)
721 THEN
722
723 populate_organization_type (errbuf, retcode, p_for_spf);
724 IF (retcode = -1)
725 THEN
726 msd_dem_common_utilities.log_message ('Error in call to populate_organization_type');
727 RETURN;
728 END IF;
729
730 END IF;
731
732 /* Call Custom Hook for Item/Location */
733 IF (p_collect_level_type = 2) /* ITEM */
734 THEN
735
736 msd_dem_common_utilities.log_message('Step 4: Updating Demantra Item Staging Table for item_type level' || fnd_global.local_chr(10));
737 msd_dem_common_utilities.log_debug('Step 4: Updating Demantra Item Staging Table for item_type level' || fnd_global.local_chr(10));
738 populate_item_type(errbuf,retcode);
739
740 IF retcode = -1 THEN
741 RETURN;
742 msd_dem_common_utilities.log_message ('Error in Updating Demantra Item Staging Table for item_type level');
743 END IF;
744
745 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'));
746
747 msd_dem_custom_hooks.item_hook (
748 errbuf,
749 retcode);
750
751 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'));
752
753 IF (retcode = -1)
754 THEN
755 msd_dem_common_utilities.log_message ('Error: msd_dem_collect_level_types.collect_levels - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
756 msd_dem_common_utilities.log_message ('Error in call to custom hook msd_dem_custom_hooks.item_hook ');
757 RETURN;
758 END IF;
759
760 /* Analyze Item Staging Table */
761 msd_dem_common_utilities.log_debug ('Begin Analyze item staging table - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
762
763 msd_dem_collect_history_data.analyze_table (
764 errbuf,
765 retcode,
766 msd_dem_common_utilities.get_lookup_value('MSD_DEM_DM_STAGING_TABLES','ITEM_STAGING_TABLE'));
767
768 msd_dem_common_utilities.log_debug ('End Analyze item staging table - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
769
770 IF (retcode = 1)
771 THEN
772 msd_dem_common_utilities.log_message ('Warning: msd_dem_collect_level_types.collect_levels - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
773 msd_dem_common_utilities.log_message ('Error while analyzing item staging table. ');
774 END IF;
775
776 ELSIF (p_collect_level_type = 1) /* LOCATION */
777 THEN
778
779 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'));
780
781 msd_dem_custom_hooks.location_hook (
782 errbuf,
783 retcode);
784
785 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'));
786
787 IF (retcode = -1)
788 THEN
789 msd_dem_common_utilities.log_message ('Error: msd_dem_collect_level_types.collect_levels - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
790 msd_dem_common_utilities.log_message ('Error in call to custom hook msd_dem_custom_hooks.location_hook ');
791 RETURN;
792 END IF;
793
794 /* Analyze Location Staging Table */
795 msd_dem_common_utilities.log_debug ('Begin Analyze location staging table - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
796
797 msd_dem_collect_history_data.analyze_table (
798 errbuf,
799 retcode,
800 msd_dem_common_utilities.get_lookup_value('MSD_DEM_DM_STAGING_TABLES','LOCATION_STAGING_TABLE'));
801
802 msd_dem_common_utilities.log_debug ('End Analyze location staging table - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
803
804 IF (retcode = 1)
805 THEN
806 msd_dem_common_utilities.log_message ('Warning: msd_dem_collect_level_types.collect_levels - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
807 msd_dem_common_utilities.log_message ('Error while analyzing location staging table. ');
808 END IF;
809
810 END IF;
811
812 exception
813 when others then
814 errbuf := substr(SQLERRM,1,150);
815 msd_dem_common_utilities.log_message(errbuf);
816 msd_dem_common_utilities.log_debug(errbuf);
817 retcode := -1;
818
819 end collect_levels;
820
821 END MSD_DEM_COLLECT_LEVEL_TYPES;