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.6.12010000.2 2008/09/08 11:26:52 vrepaka 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   BEGIN
89 
90     log_message('***************** Entered in the procedure - LAUNCH **********');
91 
92     OPEN get_dm_schema;
93     FETCH get_dm_schema
94     INTO msd_schema_name;
95     CLOSE get_dm_schema;
96 
97     IF msd_schema_name IS NOT NULL THEN
98 
99       IF(p_stream_id < 6) THEN
100         lv_retval := fnd_installation.get_app_info('MSD',   lv_dummy1,   lv_dummy2,   msd_schema_name);
101       END IF;
102 
103     ELSE
104       lv_retval := fnd_installation.get_app_info('MSD',   lv_dummy1,   lv_dummy2,   msd_schema_name);
105     END IF;
106 
107     lv_retval := fnd_installation.get_app_info('MSC',   lv_dummy1,   lv_dummy2,   msc_schema_name);
108 
109     --LOG_MESSAGE('Fetched the schema name from profile MSD_DEM_SCHEMA as '||msd_schema_name);
110 
111     IF p_stream_id = 1 THEN
112 
113       l_entity_name := 'InstallBaseHistory';
114       v_sql_stmt := 01;
115       lv_sql_stmt := ' UPDATE  ' || msd_schema_name || '.MSD_DEM_INS_BASE_HISTORY t1'
116       || ' SET t1.level1_sr_pk = ( SELECT t2.sr_inventory_item_id '
117       || '    FROM msc_system_items t2 '
118       || '    WHERE t2.sr_instance_id  =  :p_instance_id '
119       || '                           AND   t2.item_name       = t1.level1 '
120       || '                           AND   rownum = 1) ';
121 
122     END IF;
123 
124     IF p_stream_id = 2 THEN
125 
126       l_entity_name := 'Field Service Usage History';
127       v_sql_stmt := 02;
128       lv_sql_stmt := ' UPDATE  ' || msd_schema_name
129       || '.MSD_DEM_FLD_SER_USG_HISTORY t1'
130       || ' SET t1.level1_sr_pk = ( SELECT distinct(t2.sr_inventory_item_id) '
131        || '            FROM msc_system_items t2 '
132        || '          WHERE t2.sr_instance_id  =  :p_instance_id '
133        || '          AND   t2.item_name       = t1.level1 '
134        || '          AND   rownum = 1) ' ;
135 
136       lv_sql_stmt1 := ' UPDATE ' || msd_schema_name
137       || '.MSD_DEM_FLD_SER_USG_HISTORY t1'
138       || ' SET t1.level2_sr_pk = ( SELECT t2.region_id '
139       || '                       FROM msc_regions t2 '
140       || '              where t2.sr_instance_id = :p_instance_id '
141        || '               AND t2.zone = t1.level2 '
142        || '               AND t2.zone_usage = 1)'  ;
143 
144     END IF;
145 
146     IF p_stream_id = 3 THEN
147 
148       l_entity_name := 'Depot Repair Usage History';
149       v_sql_stmt := 03;
150       lv_sql_stmt := ' UPDATE  '
151        || msd_schema_name
152         || '.MSD_DEM_DPT_REP_USG_HISTORY t1'
153 	|| ' SET t1.level1_sr_pk = ( SELECT distinct(t2.sr_inventory_item_id) '
154 	|| '                           FROM msc_system_items t2 '
155 	|| '                  WHERE t2.sr_instance_id  =  :p_instance_id '
156 	|| '                    AND   t2.item_name       = t1.level1) ';
157 
158       lv_sql_stmt1 := ' UPDATE '
159       || msd_schema_name
160       || '.MSD_DEM_DPT_REP_USG_HISTORY t1'
161       || ' SET t1.level2_sr_pk = ( SELECT t2.sr_tp_id '
162       || '                       FROM msc_trading_partners t2 '
163       || '              where t2.sr_instance_id = :p_instance_id '
164       || '              AND t2.organization_code = t1.level2) ';
165 
166     END IF;
167 
168     IF p_stream_id = 4 THEN
169 
170       l_entity_name := 'Service Part Return History';
171       v_sql_stmt := 04;
172       lv_sql_stmt := ' UPDATE  '
173       || msd_schema_name
174       || '.MSD_DEM_SRP_RETURN_HISTORY t1'
175       || ' SET t1.level1_sr_pk = ( SELECT distinct(t2.sr_inventory_item_id) '
176       || '                           FROM msc_system_items t2 '
177       || '                WHERE t2.sr_instance_id  =  :p_instance_id '
178       || '                           AND   t2.item_name       = t1.level1 '
179       || '                           AND   rownum = 1) ';
180 
181       lv_sql_stmt1 := ' UPDATE '
182       || msd_schema_name || '.MSD_DEM_SRP_RETURN_HISTORY t1'
183       || ' SET t1.level2_sr_pk = ( SELECT t2.region_id '
184       || '                       FROM msc_regions t2 '
185       || '             where t2.sr_instance_id = :p_instance_id '
186       || '                       AND t2.zone = t1.level2 '
187       || '                       AND t2.zone_usage = 1)';
188 
189     END IF;
190 
191     IF p_stream_id = 5 THEN
192 
193       l_entity_name := 'Failure Rates';
194       v_sql_stmt := 05;
195 
196       lv_sql_stmt := 'UPDATE ' || msc_schema_name
197       || '.MSC_ITEM_FAILURE_RATES t1'
198       || ' SET t1.using_assembly_id = nvl((select distinct(t2.inventory_item_id)'
199       || '                             FROM msc_system_items t2 '
200       || '                  where t2.sr_instance_id = :p_instance_id '
201       || '                     and t2.item_name = t1.using_assembly_name),-55555) ';
202 
203       lv_sql_stmt1 := 'UPDATE ' || msc_schema_name
204       || '.MSC_ITEM_FAILURE_RATES t1'
205       || ' SET t1.inventory_item_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.item_name),-55555) ';
209 
210       lv_sql_stmt2 := ' UPDATE ' || msc_schema_name
211       || '.MSC_ITEM_FAILURE_RATES '
212       || ' SET FAILURE_RATE = 1'
213       || ' where failure_rate > 1'
214       || ' and process_flag = ''N'' ';
215 
216       lv_sql_stmt3 := ' UPDATE ' || msc_schema_name
217       || '.MSC_ITEM_FAILURE_RATES '
218       || ' SET FAILURE_RATE = 0'
219       || ' where failure_rate < 0'
220       || ' and process_flag = ''N'' ';
221 
222       lv_sql_stmt4 := ' UPDATE ' || msc_schema_name
223       || '.MSC_ITEM_FAILURE_RATES '
224       || ' SET PROCESS_FLAG = ''P'''
225       || ' where PROCESS_FLAG = ''N''';
226 
227       lv_sql_stmt5 := ' UPDATE ' || msc_schema_name
228       || '.MSC_ITEM_FAILURE_RATES '
229       || ' SET PROCESS_FLAG = ''E'''
230       || ' where PROCESS_FLAG = ''N'''
231       || ' and (USING_ASSEMBLY_ID = -55555 or INVENTORY_ITEM_ID = -55555) ';
232 
233       lv_sql_stmt6 := 'DELETE FROM ' || msc_schema_name
234       || '.MSC_ITEM_FAILURE_RATES '
235       || 'WHERE PROCESS_FLAG = ''E''';
236 
237     END IF;
238 
239     IF p_stream_id = 6 THEN
240 
241       l_entity_name := 'Product Return History';
242       v_sql_stmt := 06;
243       --lv_sql_stmt := ' UPDATE  ' || msd_schema_name
244       --|| '.MSD_DEM_RETURN_HISTORY t1'
245       -- || ' SET t1.level1_sr_pk = ( SELECT distinct(t2.sr_inventory_item_id) '
246       --|| '                           FROM msc_system_items t2 '
247       -- || '                      WHERE t2.sr_instance_id  =  :p_instance_id '
248       -- || '                           AND   t2.item_name       = t1.level1) ';
249 
250     END IF;
251 
252     IF p_stream_id = 7 THEN
253 
254       l_entity_name := 'Forecast Data';
255       v_sql_stmt := 07;
256 
257 	  OPEN check_srp_plan;
258       FETCH check_srp_plan
259       INTO l_demand_plan_id;
260       CLOSE check_srp_plan;
261 
262       OPEN get_scn_id;
263       FETCH get_scn_id
264       INTO l_scenario_id;
265       CLOSE get_scn_id;
266 
267       IF l_demand_plan_id IS NULL THEN
268 
269         INSERT
270         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)
271         VALUES(5555555,   -23453,   'SRP DUMMY PLAN',   trunc(sysdate),   fnd_global.user_id,   trunc(sysdate),   fnd_global.user_id,   -23453,   'N');
272 
273       END IF;
274 
275       IF l_scenario_id IS NOT NULL THEN
276 
277         OPEN get_scn_name;
278         FETCH get_scn_name
279         INTO l_scenario_name;
280         CLOSE get_scn_name;
281 
282 	lv_sql_stmt := 'DELETE FROM MSD_DP_SCENARIO_ENTRIES WHERE demand_plan_id = 5555555 and scenario_id = :l_scenario_id';
283 	EXECUTE IMMEDIATE lv_sql_stmt USING l_scenario_id;
284 
285           UPDATE MSD_DP_SCENARIO_ENTRIES SET SCENARIO_ID = l_scenario_id where scenario_name = l_scenario_name
286           and demand_plan_id = 5555555 and scenario_id = -23453;
287 
288 		END IF;
289 
290       IF l_scenario_id IS NULL THEN
291         bind_var := ':l_scenario_name,';
292 
293         OPEN get_scn_name;
294         FETCH get_scn_name
295         INTO l_scenario_name;
296         CLOSE get_scn_name;
297         lv_sql_stmt := 'INSERT INTO MSD_DP_SCENARIOS(demand_plan_id,scenario_id,scenario_name, '
298 	|| 'last_update_date,last_updated_by,creation_date,created_by)'
299 	|| 'values(5555555,MSD_DP_SCENARIOS_S.nextval,' || bind_var
300 	|| 'trunc(sysdate),fnd_global.user_id,trunc(sysdate),fnd_global.user_id)';
301         EXECUTE IMMEDIATE lv_sql_stmt USING l_scenario_name;
302 
303         INSERT INTO MSD_DP_SCENARIO_OUTPUT_LEVELS(demand_plan_id,scenario_id,level_id,last_update_date,last_updated_by,creation_date,created_by)
304         values(5555555,MSD_DP_SCENARIOS_S.CURRVAL,1,trunc(sysdate),fnd_global.user_id,trunc(sysdate),fnd_global.user_id);
305 
306         OPEN get_scn_id;
307         FETCH get_scn_id
308         INTO l_scenario_id;
309         CLOSE get_scn_id;
310 
311         lv_sql_stmt1 := 'UPDATE MSD_DP_SCENARIO_ENTRIES SET SCENARIO_ID = MSD_DP_SCENARIOS_S.CURRVAL' || ' WHERE SCENARIO_NAME = :l_scenario_name ';
312         log_message(lv_sql_stmt1);
313         EXECUTE IMMEDIATE lv_sql_stmt1 USING l_scenario_name;
314       END IF;
315 
316       COMMIT;
317 
318       lv_sql_stmt2 := 'DELETE FROM MSD_DP_SCN_ENTRIES_DENORM ' || 'WHERE SCENARIO_ID = :l_scenario_id ' || 'AND DEMAND_PLAN_ID = 5555555';
319 
320       EXECUTE IMMEDIATE lv_sql_stmt2 USING l_scenario_id;
321 
322       INSERT
323       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)
324         (SELECT mdse.demand_plan_id,
325            mdse.scenario_id,
326            mdse.entry_id,
327            msi.sr_inventory_item_id,
328            mtp.sr_tp_id,
329            mdse.time_lvl_val_from,
330            mdse.time_lvl_val_to,
331            mdse.total_quantity,
332            mdse.creation_date,
333            mdse.created_by
334          FROM msd_dp_scenario_entries mdse,
335            msc_trading_partners mtp,
336            msc_system_items msi
337          WHERE mdse.demand_plan_id = 5555555
338          AND mdse.scenario_id = l_scenario_id
339          AND mdse.organization_lvl_val = mtp.organization_code
340          AND mdse.product_lvl_val = msi.item_name
341          AND mtp.sr_instance_id = p_instance_id
342          AND mtp.partner_type = 3
343          AND mtp.sr_tp_id = msi.organization_id
344          AND msi.sr_instance_id = p_instance_id
345          AND msi.plan_id = -1)
346       ;
347 
348       DELETE FROM msd_dp_scenario_entries mdse
349       WHERE mdse.scenario_id = l_scenario_id
350        AND mdse.organization_lvl_val IN
351         (SELECT organization_code
352          FROM msc_trading_partners
353          WHERE sr_instance_id = p_instance_id
354          AND partner_type = 3
355          AND organization_code LIKE mdse.organization_lvl_val
356          AND rownum = 1)
357       AND mdse.product_lvl_val IN
358         (SELECT item_name
359          FROM msc_system_items
360          WHERE sr_instance_id = p_instance_id
361          AND item_name LIKE mdse.product_lvl_val
362          AND rownum = 1)
363       ;
364 
365       OPEN get_count_err_records(l_scenario_id);
366       FETCH get_count_err_records
367       INTO l_err_count;
368       CLOSE get_count_err_records;
369 
370       IF l_err_count > 0 THEN
371         log_message('Records with demand_plan_id 5555555 and scenario_id '|| l_scenario_id ||' in table  MSD_DP_SCENARIO_ENTRIES errored out.');
372         retcode := g_error;
373 
374 		END IF;
375 
376       COMMIT;
377 
378     END IF;
379 
380     IF v_debug THEN
381       log_message(lv_sql_stmt);
382 
383       IF lv_sql_stmt1 IS NOT NULL THEN
384         log_message(lv_sql_stmt1);
385       END IF;
386 
387       IF lv_sql_stmt2 IS NOT NULL THEN
388         log_message(lv_sql_stmt2);
389       END IF;
390 
391     END IF;
392 
393     BEGIN
394 
395       IF lv_sql_stmt IS NOT NULL
396        AND p_stream_id < 7 THEN
397         EXECUTE IMMEDIATE lv_sql_stmt USING p_instance_id;
398       END IF;
399 
400       IF lv_sql_stmt1 IS NOT NULL
401        AND p_stream_id < 7 THEN
402         EXECUTE IMMEDIATE lv_sql_stmt1 USING p_instance_id;
403       END IF;
404 
405       IF lv_sql_stmt2 IS NOT NULL
406        AND p_stream_id < 7 THEN
407         EXECUTE IMMEDIATE lv_sql_stmt2;
408       END IF;
409 
410       IF lv_sql_stmt3 IS NOT NULL
411        AND p_stream_id < 7 THEN
412         EXECUTE IMMEDIATE lv_sql_stmt3;
413       END IF;
414 
415       IF lv_sql_stmt5 IS NOT NULL
416        AND p_stream_id < 7 THEN
417         EXECUTE IMMEDIATE lv_sql_stmt5;
418 
419         LOG_MESSAGE('The following records errored out during validation : ');
420 
421         FOR failure_rate_id in get_err_records
422         LOOP
423           LOG_MESSAGE(failure_rate_id.FAILURE_RATE || '~' ||
424                       failure_rate_id.COLLECTED_FLAG || '~' ||
425                       failure_rate_id.RETIREMENT_RATE || '~' ||
426                       failure_rate_id.ITEM_NAME || '~' ||
427                       failure_rate_id.USING_ASSEMBLY_NAME
428           );
429           retcode := g_error;
430         EXIT WHEN get_err_records % NOTFOUND;
431         END LOOP;
432       END IF;
433 
434       IF lv_sql_stmt4 IS NOT NULL
435        AND p_stream_id < 7 THEN
436         EXECUTE IMMEDIATE lv_sql_stmt4;
437       END IF;
438 
439       IF lv_sql_stmt6 IS NOT NULL
440        AND p_stream_id < 7 THEN
441         EXECUTE IMMEDIATE lv_sql_stmt6;
442       END IF;
443 
444     EXCEPTION
445     WHEN others THEN
446       log_message('Error generating Source Keys');
447 
448       lv_error_text := SUBSTR('MSD_SRP_PROCESS_STREAM_DATA.LAUNCH ' || '(' || v_sql_stmt || ')' || sqlerrm,   1,   240);
449       log_message(lv_error_text);
450 
451       errbuf := lv_error_text;
452       retcode := g_error;
453     END;
454     --Final commit
455     COMMIT;
456 
457     log_message('***************** Exiting from the procedure - LAUNCH **********');
458 
459   END launch;
460 
461 END msd_srp_process_stream_data;
462