eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.MSD_SRP_PROCESS_STREAM_DATA

Source


1 PACKAGE BODY msd_srp_process_stream_data AS -- body
2   /* $Header: MSDSRPPPB.pls 120.8 2010/10/22 08:07:25 sjagathe ship $*/
3 
4    null_char constant VARCHAR2(6) := '-23453';
5 
6   g_error constant NUMBER := 2;
7   g_warning constant NUMBER := 1;
8 
9   v_sql_stmt pls_integer;
10   v_debug boolean := nvl(fnd_profile.VALUE('MRP_DEBUG'),   'N') = 'Y';
11 
12   PROCEDURE log_message(p_error_text IN VARCHAR2) IS
13   BEGIN
14 
15     IF fnd_global.conc_request_id > 0 THEN
16       fnd_file.PUT_LINE(fnd_file.LOG,   p_error_text);
17     END IF;
18 
19   EXCEPTION
20   WHEN others THEN
21     RETURN;
22   END log_message;
23 
24   PROCEDURE launch(errbuf OUT nocopy VARCHAR2,   retcode OUT nocopy NUMBER,   p_instance_id IN NUMBER,   p_stream_id IN NUMBER) IS
25 
26   CURSOR get_dm_schema IS
27   SELECT owner
28   FROM dba_objects
29   WHERE owner = owner
30    AND object_type = 'TABLE'
31    AND object_name = 'MDP_MATRIX'
32   ORDER BY created DESC;
33 
34   CURSOR check_srp_plan IS
35   SELECT demand_plan_id
36   FROM msd_demand_plans
37   WHERE demand_plan_id = 5555555;
38 
39   CURSOR get_scn_id IS
40   SELECT scenario_id
41   FROM msd_dp_scenarios
42   WHERE scenario_name LIKE
43     (SELECT DISTINCT(scenario_name)
44      FROM msd_dp_scenario_entries
45 	 WHERE scenario_id = -23453)
46   ;
47 
48   CURSOR get_scn_name IS
49   SELECT DISTINCT(scenario_name)
50   FROM msd_dp_scenario_entries
51   WHERE scenario_id = -23453;
52 
53   CURSOR get_count_err_records(l_scenario_id NUMBER) IS
54   SELECT COUNT(*)
55   FROM msd_dp_scenario_entries
56   WHERE demand_plan_id = 5555555
57    AND scenario_id = l_scenario_id;
58 
59   CURSOR get_err_records IS
60   SELECT * from MSC_ITEM_FAILURE_RATES
61   where process_flag = 'E';
62 
63   msd_schema_name VARCHAR2(100);
64   msc_schema_name VARCHAR2(100);
65   l_scenario_name VARCHAR2(100);
66   l_entity_name VARCHAR2(100);
67   lv_sql_stmt VARCHAR2(4000);
68   lv_sql_stmt1 VARCHAR2(4000);
69   lv_sql_stmt2 VARCHAR2(4000);
70   lv_sql_stmt3 VARCHAR2(4000);
71   lv_sql_stmt4 VARCHAR2(4000);
72   lv_sql_stmt5 VARCHAR2(4000);
73   lv_sql_stmt6 VARCHAR2(4000);
74   lv_error_text VARCHAR2(1000);
75   lv_dummy1 VARCHAR2(32);
76   lv_dummy2 VARCHAR2(32);
77 
78   bind_var VARCHAR2(20);
79 
80   l_scenario_id NUMBER;
81   l_demand_plan_id NUMBER;
82   l_err_count NUMBER;
83 
84   lv_retval boolean;
85 
86   failure_rate_id get_err_records % rowtype;
87 
88   x_table_name   VARCHAR2(50) := NULL;
89   x_index_name   VARCHAR2(50) := NULL;
90   x_owner        VARCHAR2(50) := 'MSD';
91   x_sql          VARCHAR2(2000) := NULL;
92   x_is_present   NUMBER        := 0;
93   x_rows_deleted NUMBER        := 0;
94 
95   BEGIN
96 
97     log_message('***************** Entered in the procedure - LAUNCH **********');
98 
99     OPEN get_dm_schema;
100     FETCH get_dm_schema
101     INTO msd_schema_name;
102     CLOSE get_dm_schema;
103 
104     IF msd_schema_name IS NOT NULL THEN
105 
106       IF(p_stream_id < 6) THEN
107         lv_retval := fnd_installation.get_app_info('MSD',   lv_dummy1,   lv_dummy2,   msd_schema_name);
108       END IF;
109 
110     ELSE
111       lv_retval := fnd_installation.get_app_info('MSD',   lv_dummy1,   lv_dummy2,   msd_schema_name);
112     END IF;
113 
114     lv_retval := fnd_installation.get_app_info('MSC',   lv_dummy1,   lv_dummy2,   msc_schema_name);
115 
116     --LOG_MESSAGE('Fetched the schema name from profile MSD_DEM_SCHEMA as '||msd_schema_name);
117 
118     IF p_stream_id = 1 THEN
119 
120       l_entity_name := 'InstallBaseHistory';
121       v_sql_stmt := 01;
122       lv_sql_stmt := ' UPDATE  ' || msd_schema_name || '.MSD_DEM_INS_BASE_HISTORY t1'
123       || ' SET t1.level1_sr_pk = ( SELECT t2.sr_inventory_item_id '
124       || '    FROM msc_system_items t2 '
125       || '    WHERE t2.sr_instance_id  =  :p_instance_id '
126       || '                           AND   t2.item_name       = t1.level1 '
127       || '                           AND   rownum = 1) ';
128 
129     END IF;
130 
131     IF p_stream_id = 2 THEN
132 
133       l_entity_name := 'Field Service Usage History';
134       v_sql_stmt := 02;
135       lv_sql_stmt := ' UPDATE  ' || msd_schema_name
136       || '.MSD_DEM_FLD_SER_USG_HISTORY t1'
137       || ' SET t1.level1_sr_pk = ( SELECT distinct(t2.sr_inventory_item_id) '
138        || '            FROM msc_system_items t2 '
139        || '          WHERE t2.sr_instance_id  =  :p_instance_id '
140        || '          AND   t2.item_name       = t1.level1 '
141        || '          AND   rownum = 1) ' ;
142 
143       lv_sql_stmt1 := ' UPDATE ' || msd_schema_name
144       || '.MSD_DEM_FLD_SER_USG_HISTORY t1'
145       || ' SET t1.level2_sr_pk = ( SELECT t2.region_id '
146       || '                       FROM msc_regions t2 '
147       || '              where t2.sr_instance_id = :p_instance_id '
148        || '               AND t2.zone = t1.level2 '
149        || '               AND t2.zone_usage = 1)'  ;
150 
151     END IF;
152 
153     IF p_stream_id = 3 THEN
154 
155       l_entity_name := 'Depot Repair Usage History';
156       v_sql_stmt := 03;
157       lv_sql_stmt := ' UPDATE  '
158        || msd_schema_name
159         || '.MSD_DEM_DPT_REP_USG_HISTORY t1'
160 	|| ' SET t1.level1_sr_pk = ( SELECT distinct(t2.sr_inventory_item_id) '
161 	|| '                           FROM msc_system_items t2 '
162 	|| '                  WHERE t2.sr_instance_id  =  :p_instance_id '
163 	|| '                    AND   t2.item_name       = t1.level1) ';
164 
165       lv_sql_stmt1 := ' UPDATE '
166       || msd_schema_name
167       || '.MSD_DEM_DPT_REP_USG_HISTORY t1'
168       || ' SET t1.level2_sr_pk = ( SELECT t2.sr_tp_id '
169       || '                       FROM msc_trading_partners t2 '
170       || '              where t2.sr_instance_id = :p_instance_id '
171       || '              AND t2.organization_code = t1.level2) ';
172 
173     END IF;
174 
175     IF p_stream_id = 4 THEN
176 
177       l_entity_name := 'Service Part Return History';
178       v_sql_stmt := 04;
179       lv_sql_stmt := ' UPDATE  '
180       || msd_schema_name
181       || '.MSD_DEM_SRP_RETURN_HISTORY t1'
182       || ' SET t1.level1_sr_pk = ( SELECT distinct(t2.sr_inventory_item_id) '
183       || '                           FROM msc_system_items t2 '
184       || '                WHERE t2.sr_instance_id  =  :p_instance_id '
185       || '                           AND   t2.item_name       = t1.level1 '
186       || '                           AND   rownum = 1) ';
187 
188       lv_sql_stmt1 := ' UPDATE '
189       || msd_schema_name || '.MSD_DEM_SRP_RETURN_HISTORY t1'
190       || ' SET t1.level2_sr_pk = ( SELECT t2.region_id '
191       || '                       FROM msc_regions t2 '
192       || '             where t2.sr_instance_id = :p_instance_id '
193       || '                       AND t2.zone = t1.level2 '
194       || '                       AND t2.zone_usage = 1)';
195 
196     END IF;
197 
198     IF p_stream_id = 5 THEN
199 
200       l_entity_name := 'Failure Rates';
201       v_sql_stmt := 05;
202 
203       lv_sql_stmt := 'UPDATE ' || msc_schema_name
204       || '.MSC_ITEM_FAILURE_RATES t1'
205       || ' SET t1.using_assembly_id = nvl((select distinct(t2.inventory_item_id)'
206       || '                             FROM msc_system_items t2 '
207       || '                  where t2.sr_instance_id = :p_instance_id '
208       || '                     and t2.item_name = t1.using_assembly_name),-55555) ';
209 
210       lv_sql_stmt1 := 'UPDATE ' || msc_schema_name
211       || '.MSC_ITEM_FAILURE_RATES t1'
212       || ' SET t1.inventory_item_id = nvl((select distinct(t2.inventory_item_id) '
213       || '                             FROM msc_system_items t2 '
214       || '                      where t2.sr_instance_id = :p_instance_id '
215       || '                             and t2.item_name = t1.item_name),-55555) ';
216 
217       lv_sql_stmt2 := ' UPDATE ' || msc_schema_name
218       || '.MSC_ITEM_FAILURE_RATES '
219       || ' SET FAILURE_RATE = 1'
220       || ' where failure_rate > 1'
221       || ' and process_flag = ''N'' ';
222 
223       lv_sql_stmt3 := ' UPDATE ' || msc_schema_name
224       || '.MSC_ITEM_FAILURE_RATES '
225       || ' SET FAILURE_RATE = 0'
226       || ' where failure_rate < 0'
227       || ' and process_flag = ''N'' ';
228 
229       lv_sql_stmt4 := ' UPDATE ' || msc_schema_name
230       || '.MSC_ITEM_FAILURE_RATES '
231       || ' SET PROCESS_FLAG = ''P'''
232       || ' where PROCESS_FLAG = ''N''';
233 
234       lv_sql_stmt5 := ' UPDATE ' || msc_schema_name
235       || '.MSC_ITEM_FAILURE_RATES '
236       || ' SET PROCESS_FLAG = ''E'''
237       || ' where PROCESS_FLAG = ''N'''
238       || ' and (USING_ASSEMBLY_ID = -55555 or INVENTORY_ITEM_ID = -55555) ';
239 
240       lv_sql_stmt6 := 'DELETE FROM ' || msc_schema_name
241       || '.MSC_ITEM_FAILURE_RATES '
242       || 'WHERE PROCESS_FLAG = ''E''';
243 
244     END IF;
245 
246     IF p_stream_id = 6 THEN
247 
248       l_entity_name := 'Product Return History';
249       v_sql_stmt := 06;
250       --lv_sql_stmt := ' UPDATE  ' || msd_schema_name
251       --|| '.MSD_DEM_RETURN_HISTORY t1'
252       -- || ' SET t1.level1_sr_pk = ( SELECT distinct(t2.sr_inventory_item_id) '
253       --|| '                           FROM msc_system_items t2 '
254       -- || '                      WHERE t2.sr_instance_id  =  :p_instance_id '
255       -- || '                           AND   t2.item_name       = t1.level1) ';
256 
257     END IF;
258 
259     IF p_stream_id = 7 THEN
260 
261       l_entity_name := 'Forecast Data';
262       v_sql_stmt := 07;
263 
264 	  OPEN check_srp_plan;
265       FETCH check_srp_plan
266       INTO l_demand_plan_id;
267       CLOSE check_srp_plan;
268 
269       OPEN get_scn_id;
270       FETCH get_scn_id
271       INTO l_scenario_id;
272       CLOSE get_scn_id;
273 
274       IF l_demand_plan_id IS NULL THEN
275 
276         INSERT
277         INTO msd_demand_plans(demand_plan_id,   organization_id,   demand_plan_name,   last_update_date,   last_updated_by,   creation_date,   created_by,   sr_instance_id,   use_org_specific_bom_flag)
278         VALUES(5555555,   -23453,   'SRP DUMMY PLAN',   trunc(sysdate),   fnd_global.user_id,   trunc(sysdate),   fnd_global.user_id,   -23453,   'N');
279 
280       END IF;
281 
282       IF l_scenario_id IS NOT NULL THEN
283 
284         OPEN get_scn_name;
285         FETCH get_scn_name
286         INTO l_scenario_name;
287         CLOSE get_scn_name;
288 
289 	lv_sql_stmt := 'DELETE FROM MSD_DP_SCENARIO_ENTRIES WHERE demand_plan_id = 5555555 and scenario_id = :l_scenario_id';
290 	EXECUTE IMMEDIATE lv_sql_stmt USING l_scenario_id;
291 
292           UPDATE MSD_DP_SCENARIO_ENTRIES SET SCENARIO_ID = l_scenario_id where scenario_name = l_scenario_name
293           and demand_plan_id = 5555555 and scenario_id = -23453;
294 
295 		END IF;
296 
297       IF l_scenario_id IS NULL THEN
298         bind_var := ':l_scenario_name,';
299 
300         OPEN get_scn_name;
301         FETCH get_scn_name
302         INTO l_scenario_name;
303         CLOSE get_scn_name;
304         lv_sql_stmt := 'INSERT INTO MSD_DP_SCENARIOS(demand_plan_id,scenario_id,scenario_name, '
305 	|| 'last_update_date,last_updated_by,creation_date,created_by)'
306 	|| 'values(5555555,MSD_DP_SCENARIOS_S.nextval,' || bind_var
307 	|| 'trunc(sysdate),fnd_global.user_id,trunc(sysdate),fnd_global.user_id)';
308         EXECUTE IMMEDIATE lv_sql_stmt USING l_scenario_name;
309 
310         INSERT INTO MSD_DP_SCENARIO_OUTPUT_LEVELS(demand_plan_id,scenario_id,level_id,last_update_date,last_updated_by,creation_date,created_by)
311         values(5555555,MSD_DP_SCENARIOS_S.CURRVAL,1,trunc(sysdate),fnd_global.user_id,trunc(sysdate),fnd_global.user_id);
312 
313         OPEN get_scn_id;
314         FETCH get_scn_id
315         INTO l_scenario_id;
316         CLOSE get_scn_id;
317 
318         lv_sql_stmt1 := 'UPDATE MSD_DP_SCENARIO_ENTRIES SET SCENARIO_ID = MSD_DP_SCENARIOS_S.CURRVAL' || ' WHERE SCENARIO_NAME = :l_scenario_name ';
319         log_message(lv_sql_stmt1);
320         EXECUTE IMMEDIATE lv_sql_stmt1 USING l_scenario_name;
321       END IF;
322 
323       COMMIT;
324 
325       lv_sql_stmt2 := 'DELETE FROM MSD_DP_SCN_ENTRIES_DENORM ' || 'WHERE SCENARIO_ID = :l_scenario_id ' || 'AND DEMAND_PLAN_ID = 5555555';
326 
327       EXECUTE IMMEDIATE lv_sql_stmt2 USING l_scenario_id;
328 
329       INSERT
330       INTO msd_dp_scn_entries_denorm(demand_plan_id,   scenario_id,   demand_id,   sr_inventory_item_id,   sr_organization_id,   start_time,   end_time,   quantity,   creation_date,   created_by)
331         (SELECT mdse.demand_plan_id,
332            mdse.scenario_id,
333            mdse.entry_id,
334            msi.sr_inventory_item_id,
335            mtp.sr_tp_id,
336            mdse.time_lvl_val_from,
337            mdse.time_lvl_val_to,
338            mdse.total_quantity,
339            mdse.creation_date,
340            mdse.created_by
341          FROM msd_dp_scenario_entries mdse,
342            msc_trading_partners mtp,
343            msc_system_items msi
344          WHERE mdse.demand_plan_id = 5555555
345          AND mdse.scenario_id = l_scenario_id
346          AND mdse.organization_lvl_val = mtp.organization_code
347          AND mdse.product_lvl_val = msi.item_name
348          AND mtp.sr_instance_id = p_instance_id
349          AND mtp.partner_type = 3
350          AND mtp.sr_tp_id = msi.organization_id
351          AND msi.sr_instance_id = p_instance_id
352          AND msi.plan_id = -1)
353       ;
354 
355       DELETE FROM msd_dp_scenario_entries mdse
356       WHERE mdse.scenario_id = l_scenario_id
357        AND mdse.organization_lvl_val IN
358         (SELECT organization_code
359          FROM msc_trading_partners
360          WHERE sr_instance_id = p_instance_id
361          AND partner_type = 3
362          AND organization_code LIKE mdse.organization_lvl_val
363          AND rownum = 1)
364       AND mdse.product_lvl_val IN
365         (SELECT item_name
366          FROM msc_system_items
367          WHERE sr_instance_id = p_instance_id
368          AND item_name LIKE mdse.product_lvl_val
369          AND rownum = 1)
370       ;
371 
372       OPEN get_count_err_records(l_scenario_id);
373       FETCH get_count_err_records
374       INTO l_err_count;
375       CLOSE get_count_err_records;
376 
377       IF l_err_count > 0 THEN
378         log_message('Records with demand_plan_id 5555555 and scenario_id '|| l_scenario_id ||' in table  MSD_DP_SCENARIO_ENTRIES errored out.');
379         retcode := g_error;
380 
381 		END IF;
382 
383       COMMIT;
384 
385     END IF;
386 
387     IF v_debug THEN
388       log_message(lv_sql_stmt);
389 
390       IF lv_sql_stmt1 IS NOT NULL THEN
391         log_message(lv_sql_stmt1);
392       END IF;
393 
394       IF lv_sql_stmt2 IS NOT NULL THEN
395         log_message(lv_sql_stmt2);
396       END IF;
397 
398     END IF;
399 
400     BEGIN
401 
402       IF lv_sql_stmt IS NOT NULL
403        AND p_stream_id < 7 THEN
404         EXECUTE IMMEDIATE lv_sql_stmt USING p_instance_id;
405       END IF;
406 
407       IF lv_sql_stmt1 IS NOT NULL
408        AND p_stream_id < 7 THEN
409         EXECUTE IMMEDIATE lv_sql_stmt1 USING p_instance_id;
410       END IF;
411 
412       IF lv_sql_stmt2 IS NOT NULL
413        AND p_stream_id < 7 THEN
414         EXECUTE IMMEDIATE lv_sql_stmt2;
415       END IF;
416 
417       IF lv_sql_stmt3 IS NOT NULL
418        AND p_stream_id < 7 THEN
419         EXECUTE IMMEDIATE lv_sql_stmt3;
420       END IF;
421 
422       IF lv_sql_stmt5 IS NOT NULL
423        AND p_stream_id < 7 THEN
424         EXECUTE IMMEDIATE lv_sql_stmt5;
425 
426         LOG_MESSAGE('The following records errored out during validation : ');
427 
428         FOR failure_rate_id in get_err_records
429         LOOP
430           LOG_MESSAGE(failure_rate_id.FAILURE_RATE || '~' ||
431                       failure_rate_id.COLLECTED_FLAG || '~' ||
432                       failure_rate_id.RETIREMENT_RATE || '~' ||
433                       failure_rate_id.ITEM_NAME || '~' ||
434                       failure_rate_id.USING_ASSEMBLY_NAME
435           );
436           retcode := g_error;
437         EXIT WHEN get_err_records % NOTFOUND;
438         END LOOP;
439       END IF;
440 
441       IF lv_sql_stmt4 IS NOT NULL
442        AND p_stream_id < 7 THEN
443         EXECUTE IMMEDIATE lv_sql_stmt4;
444       END IF;
445 
446       IF lv_sql_stmt6 IS NOT NULL
447        AND p_stream_id < 7 THEN
448         EXECUTE IMMEDIATE lv_sql_stmt6;
449       END IF;
450 
451     EXCEPTION
452     WHEN others THEN
453       log_message('Error generating Source Keys');
454 
455       lv_error_text := SUBSTR('MSD_SRP_PROCESS_STREAM_DATA.LAUNCH ' || '(' || v_sql_stmt || ')' || sqlerrm,   1,   240);
456       log_message(lv_error_text);
457 
458       errbuf := lv_error_text;
459       retcode := g_error;
460     END;
461     --Final commit
462     COMMIT;
463 
464     /* 10224184 - Check and remove duplicates and add unique indexes after data load */
465 
466     IF p_stream_id = 3 THEN
467 
468        x_table_name := 'MSD_DEM_DPT_REP_USG_HISTORY';
469        x_index_name := 'MSD_DEM_DPT_REP_USG_HIST_U1';
470 
471     END IF;
472 
473     IF p_stream_id = 2 THEN
474 
475        x_table_name := 'MSD_DEM_FLD_SER_USG_HISTORY';
476        x_index_name := 'MSD_DEM_FLD_SER_USG_HIST_U1';
477 
478     END IF;
479 
480     IF p_stream_id = 4 THEN
481 
482        x_table_name := 'MSD_DEM_SRP_RETURN_HISTORY';
483        x_index_name := 'MSD_DEM_SRP_RET_HIST_U1';
484 
485     END IF;
486 
487     IF (x_table_name IS NOT NULL AND x_index_name IS NOT NULL AND p_stream_id IN (3,2,4)) THEN
488 
489        msd_dem_common_utilities.log_debug ('Table Name - ' || x_table_name);
490        msd_dem_common_utilities.log_debug ('Index Name - ' || x_index_name);
491 
492        msd_dem_common_utilities.log_debug ('Checking for duplicates - ');
493        x_sql := 'SELECT sum(a) from (select 1 a FROM ' || x_table_name
494                                 || ' GROUP BY LEVEL1_SR_PK, LEVEL2_SR_PK, SDATE '
495                                 || ' HAVING count(1) > 1)';
496        msd_dem_common_utilities.log_debug (x_sql);
497        EXECUTE IMMEDIATE x_sql INTO x_is_present;
498 
499        IF (x_is_present > 0) THEN
500           --msd_dem_common_utilities.log_message('Duplicates found in table ' || x_table_name);
501           --msd_dem_common_utilities.log_message('Deleting duplicates ... ');
502 
503           x_sql := 'DELETE FROM ' || x_table_name
504                    || ' WHERE rowid NOT IN (SELECT max(rowid) FROM ' || x_table_name
505                    || '                     GROUP BY LEVEL1_SR_PK, LEVEL2_SR_PK, SDATE ) ';
506           msd_dem_common_utilities.log_debug (x_sql);
507           EXECUTE IMMEDIATE x_sql;
508           x_rows_deleted := SQL%ROWCOUNT;
509           msd_dem_common_utilities.log_message ('Duplicate rows deleted - ' || x_rows_deleted);
510 
511        END IF;
512 
513        msd_dem_common_utilities.log_message('Adding Index - ' || x_index_name);
514        x_sql := 'CREATE UNIQUE INDEX ' || x_owner || '.' || x_index_name
515                 || ' ON ' || x_owner || '.' || x_table_name
516                 || '(LEVEL1_SR_PK, LEVEL2_SR_PK, SDATE) ';
517        msd_dem_common_utilities.log_debug (x_sql);
518        EXECUTE IMMEDIATE x_sql;
519        msd_dem_common_utilities.log_message('Created Index - ' || x_index_name);
520 
521        msd_dem_common_utilities.log_message('Analyzing Table - ' || x_table_name);
522        fnd_stats.gather_table_stats(x_owner, x_table_name, 10, 4);
523 
524 
525     END IF;
526 
527     log_message('***************** Exiting from the procedure - LAUNCH **********');
528 
529   END launch;
530 
531 END msd_srp_process_stream_data;
532