[Home] [Help]
PACKAGE BODY: APPS.GMP_FORECAST_MIGRATION
Source
1 PACKAGE BODY GMP_forecast_migration AS
2 /* $Header: GMPFCMIB.pls 120.6 2011/05/06 12:57:34 vkinduri ship $ */
3
4 TYPE organization_id IS TABLE OF mrp_forecast_designators.organization_id%TYPE
5 INDEX BY BINARY_INTEGER;
6 f_organization_id organization_id;
7 f_int_organization_id organization_id;
8 i_organization_id organization_id;
9
10 TYPE inventory_item_id IS TABLE OF mrp_forecast_items.inventory_item_id%TYPE
11 INDEX BY BINARY_INTEGER;
12 f_inventory_item_id inventory_item_id ;
13 f_int_inventory_item_id inventory_item_id ;
14
15 TYPE forecast_designator IS TABLE OF mrp_forecast_designators.forecast_designator%TYPE INDEX BY BINARY_INTEGER;
16 i_designator forecast_designator ;
17 f_forecast_designator forecast_designator ;
18 f_int_forecast_designator forecast_designator ;
19
20 TYPE forecast_set IS TABLE OF mrp_forecast_designators.forecast_set%TYPE INDEX BY BINARY_INTEGER;
21 i_forecast_set forecast_set;
22
23 TYPE description IS TABLE OF mrp_forecast_designators.description%TYPE INDEX BY BINARY_INTEGER;
24 i_description description ;
25
26 TYPE disable_date IS TABLE OF mrp_forecast_designators.disable_date%TYPE INDEX BY BINARY_INTEGER;
27 i_disable_date disable_date ;
28
29 TYPE tconsume_forecast IS TABLE OF mrp_forecast_designators.consume_forecast%TYPE
30 INDEX BY BINARY_INTEGER;
31 i_consume_forecast tconsume_forecast ;
32
33 TYPE backward_update_time_fence IS TABLE OF mrp_forecast_designators.backward_update_time_fence%TYPE
34 INDEX BY BINARY_INTEGER;
35 i_backward_update_time_fence backward_update_time_fence ;
36
37 TYPE forward_update_time_fence IS TABLE OF mrp_forecast_designators.foreward_update_time_fence%TYPE
38 INDEX BY BINARY_INTEGER;
39 i_forward_update_time_fence forward_update_time_fence ;
40
41 TYPE quantity IS TABLE OF mrp_forecast_interface.quantity%TYPE
42 INDEX BY BINARY_INTEGER;
43 f_int_quantity quantity ;
44
45 TYPE forecast_date IS TABLE OF mrp_forecast_interface.forecast_date%TYPE
46 INDEX BY BINARY_INTEGER;
47 f_int_forecast_date forecast_date ;
48
49 TYPE fcst_hdr_rec IS RECORD (
50 fcst_id NUMBER,
51 orig_forecast VARCHAR2(16),
52 fcst_name VARCHAR2(10),
53 fcst_set VARCHAR2(10),
54 desgn_ind NUMBER,
55 consumption_ind NUMBER,
56 backward_time_fence NUMBER,
57 forward_time_fence NUMBER
58 );
59 TYPE fcst_dtl_tbl_rec IS RECORD
60 (
61 inventory_item_id NUMBER,
62 organization_id NUMBER,
63 forecast_id NUMBER,
64 line_id NUMBER,
65 forecast VARCHAR2(16),
66 forecast_set VARCHAR2(10),
67 trans_date DATE,
68 orgn_code VARCHAR2(4),
69 trans_qty NUMBER,
70 write_row_flag NUMBER(1)
71 );
72 TYPE fcst_dtl_tbl_typ IS TABLE OF fcst_dtl_tbl_rec
73 INDEX BY BINARY_INTEGER ;
74
75 fcst_dtl_tbl fcst_dtl_tbl_typ ;
76
77 TYPE fcst_valid_rec IS RECORD
78 (
79 forecast_designator VARCHAR2(10),
80 forecast_set VARCHAR2(10)
81 );
82 TYPE fcst_valid_typ IS TABLE OF fcst_valid_rec
83 INDEX BY BINARY_INTEGER ;
84
85 fcst_valid_tbl fcst_valid_typ;
86
87 TYPE fcst_hdr_tab_typ IS TABLE OF fcst_hdr_rec
88 INDEX BY BINARY_INTEGER ;
89
90 fcst_hdr_tbl fcst_hdr_tab_typ ;
91
92 PROCEDURE Exec_forecast_Migration
93 ( P_migration_run_id IN NUMBER,
94 P_commit IN VARCHAR2,
95 X_failure_count OUT NOCOPY NUMBER
96 )
97 IS
98
99 TYPE gmp_cursor_typ IS REF CURSOR;
100 fcst_hdr gmp_cursor_typ;
101 cur_fcst_dtl gmp_cursor_typ;
102 cur_fcst_valid gmp_cursor_typ;
103
104 cnt NUMBER ;
105 l_cnt NUMBER ;
106 curr_cnt NUMBER ;
107 temp_name VARCHAR2(10);
108 i NUMBER ;
109 j NUMBER ;
110 k NUMBER ;
111 x NUMBER ;
112 duplicate_found BOOLEAN ;
113 prev_org_id NUMBER ;
114 prev_fcst_id NUMBER ;
115 prev_forecast_set VARCHAR2(10);
116 --prev_fcst_set VARCHAR2(10);
117 prev_fcst VARCHAR2(10);
118 prev_fcst_item NUMBER ;
119 write_fcst BOOLEAN ;
120 write_fcst_set BOOLEAN ;
121 write_fcst_item BOOLEAN ;
122 fcst_locn NUMBER ;
123 l_exist_flag NUMBER ;
124
125 l_design_stmt VARCHAR2(5000) ;
126 l_fcst_stmt VARCHAR2(5000) ;
127 l_validation_stmt VARCHAR2(5000) ;
128 fi_index NUMBER ;
129 f_int_index NUMBER ;
130 i_index NUMBER ;
131 null_value VARCHAR2(2) ;
132 fcst_counter NUMBER ;
133 fcst_itm_counter NUMBER ;
134 fcst_itrf_counter NUMBER ;
135 l_location VARCHAR2(500);
136 l_conc_id NUMBER ;
137 request_id NUMBER;
138 status VARCHAR2(80);
139 phase VARCHAR2(80);
140 dev_status VARCHAR2(80);
141 dev_phase VARCHAR2(80);
142 return_message VARCHAR2(80);
143 return_flag BOOLEAN;
144
145 BEGIN
146
147 /* Intialize the variables */
148 l_location := NULL;
149 prev_fcst_item := 0 ;
150 temp_name := NULL ;
151 cnt := 0 ;
152 l_cnt := 1 ;
153 curr_cnt := 0 ;
154 duplicate_found := FALSE ;
155
156 i := 1 ;
157 j := 10 ;
158 k := 0 ;
159 x := 1;
160
161 prev_org_id := 0 ;
162 prev_fcst_id := 0 ;
163
164 fi_index := 0 ;
165 f_int_index := 0 ;
166 i_index := 0 ;
167
168 null_value := null ;
169 fcst_counter := 0;
170 fcst_itm_counter := 0;
171 fcst_itrf_counter := 0;
172 l_conc_id := 0;
173
174 --prev_fcst_set := '-1' ;
175 prev_fcst := '-1';
176 l_exist_flag := 0;
177
178 l_fcst_stmt := 'SELECT '
179 || ' msi.inventory_item_id, '
180 || ' nvl(sy.organization_id,msi.organization_id), ' /*B4931593*/
181 /*B4931593 - The forecast data is moved to the migrated organization.*/
182 || ' h.forecast_id, '
183 || ' d.line_id, '
184 || ' h.forecast, '
185 || ' h.forecast_set FSET , '
186 || ' d.trans_date, '
187 || ' d.orgn_code, '
188 || ' (d.trans_qty * -1) trans_qty, '
189 || ' 1 write_row_flag '
190 || ' FROM '
191 || ' mtl_system_items msi, '
192 || ' ic_item_mst iim, '
193 || ' ic_whse_mst wm, '
194 || ' fc_fcst_hdr h, '
195 || ' sy_orgn_mst sy, '/*B4931593*/
196 || ' fc_fcst_dtl d '
197 || ' WHERE '
198 || ' msi.organization_id = wm.organization_id '
199 || ' and sy.orgn_code = d.orgn_code '/*B4931593*/
200 || ' and sy.migrated_ind = 1 '/*B4931593*/
201 || ' and msi.segment1 = iim.item_no '
202 || ' and wm.delete_mark = 0 '
203 || ' and h.forecast_id = d.forecast_id '
204 || ' and d.forecast_id > 0 '
205 || ' and d.item_id = iim.item_id '
206 || ' and d.whse_code = wm.whse_code '
207 || ' and d.orgn_code = wm.orgn_code '
208 || ' and h.forecast_set is NOT NULL '
209 || ' and h.delete_mark = 0 '
210 || ' and d.delete_mark = 0 '
211 || ' and d.trans_qty <> 0 '
212 -- || ' ORDER BY FSET, wm.organization_id ,h.forecast_id, msi.inventory_item_id ' ;
213 -- B9711637, to avoid duplicate designators
214 ||'ORDER BY FSET,nvl(sy.organization_id,msi.organization_id),h.forecast_id, msi.inventory_item_id ' ;
215
216 -- ===+++++++====++++ build designator++++=======++++=======
217 l_design_stmt := 'SELECT '||
218 ' forecast_id, '||
219 ' forecast, '||
220 ' substr(forecast,1,10) DESGN, '||
221 ' nvl(forecast_set ,substr(forecast,1,10)) FSET, '||
222 ' 1 DESGN_IND ,' ||
223 ' nvl(consumption_ind, 2), '||
224 ' backward_time_fence, '||
225 ' forward_time_fence '||
226 ' FROM fc_fcst_hdr'||
227 ' WHERE delete_mark = 0 '||
228 ' UNION ALL '||
229 -- Add forecast_sets to the list
230 ' SELECT '||
231 ' -1 , '||
232 ' min(forecast), '||
233 ' forecast_set DESGN , '||
234 ' to_char(NULL) FSET, '||
235 ' 3 DESGN_IND, ' ||
236 ' to_number(NULL), '||
237 ' to_number(NULL), '||
238 ' to_number(NULL) '||
239 ' FROM fc_fcst_hdr'||
240 ' WHERE delete_mark = 0 '||
241 ' AND forecast_set is NOT NULL '||
242 ' GROUP BY forecast_set ' ||
243 ' ORDER BY FSET, 1 DESC , DESGN_IND ' ;
244
245 OPEN fcst_hdr FOR l_design_stmt ;
246 LOOP
247 FETCH fcst_hdr INTO fcst_hdr_tbl(l_cnt);
248 EXIT WHEN fcst_hdr%NOTFOUND ;
249 l_cnt := l_cnt + 1 ;
250 END LOOP ;
251 CLOSE fcst_hdr ;
252 -- ===================== Logic ==============================
253 LOOP
254 EXIT WHEN cnt + 1 > fcst_hdr_tbl.COUNT ;
255
256 IF duplicate_found THEN
257 cnt := cnt ;
258 duplicate_found := FALSE ;
259 ELSE
260 IF temp_name IS NOT NULL THEN
261 IF (fcst_hdr_tbl(cnt).desgn_ind = 1
262 AND fcst_hdr_tbl(cnt).fcst_name <> temp_name )THEN
263 -- fcst_hdr_tbl(cnt).fcst_set := temp_name ;
264 NULL ;
265 ELSIF (fcst_hdr_tbl(cnt).desgn_ind = 3
266 AND fcst_hdr_tbl(cnt).fcst_name <> temp_name )THEN
267 -- This means we changed a set name
268 -- Now change the name in all resords of fcst that used this as set
269 FOR y IN 1..fcst_hdr_tbl.COUNT
270 LOOP
271 IF (fcst_hdr_tbl(y).fcst_set = fcst_hdr_tbl(cnt).fcst_name
272 AND fcst_hdr_tbl(y).desgn_ind = 1 ) THEN
273 fcst_hdr_tbl(y).fcst_set := temp_name ;
274 END IF ;
275 END LOOP;
276 /* nsinghi : Commented the following elsif clause as we will not generate set names. */
277 /* ELSIF (fcst_hdr_tbl(cnt).desgn_ind = 2
278 AND fcst_hdr_tbl(cnt).fcst_name <> temp_name )THEN
279 -- This means we changed a set name that was "generated"
280 -- Now change the name in the resord of fcst that used itself as set
281 FOR y in 1..fcst_hdr_tbl.COUNT
282 LOOP
283 IF (fcst_hdr_tbl(y).orig_forecast = fcst_hdr_tbl(cnt).orig_forecast
284 AND fcst_hdr_tbl(y).desgn_ind = 1 )THEN
285 fcst_hdr_tbl(y).fcst_set := temp_name ;
286 END IF ;
287 END LOOP;
288 */
289 END IF ; -- desgn_ind check
290 fcst_hdr_tbl(cnt).fcst_name := temp_name ;
291 END IF ;
292 cnt := cnt + 1 ;
293 j := 10 ;
294 k := 0 ;
295 END IF ;
296
297 IF j < 10 THEN
298 temp_name := substr(fcst_hdr_tbl(cnt).fcst_name,1,j)||to_char(k) ;
299 ELSE
300 temp_name := fcst_hdr_tbl(cnt).fcst_name ;
301 END IF ;
302
303 curr_cnt := cnt ;
304 i := 1 ;
305
306 LOOP
307 EXIT WHEN i > fcst_hdr_tbl.COUNT ;
308 IF i <> curr_cnt THEN
309 -- so that record is not compared to itself
310 IF temp_name = fcst_hdr_tbl(i).fcst_name THEN
311 duplicate_found := TRUE ;
312 k := k + 1 ;
313 IF k < 10 THEN
314 j := 9 ;
315 ELSIF k < 100 THEN
316 j := 8 ;
317 ELSIF k < 1000 THEN
318 j := 7 ;
319 ELSIF k < 10000 THEN
320 j := 6 ;
321 ELSIF k < 100000 THEN
322 j := 5 ;
323 END IF ;
324 EXIT ;
325 END IF ;
326 END IF ; -- i <> curr_cnt
327 i := i + 1 ;
328 END LOOP ;
329
330 END LOOP ; -- Outer loop
331
332 /*
333 FOR x in 1..fcst_hdr_tbl.COUNT
334 LOOP
335 dbms_output.put_line(fcst_hdr_tbl(x).fcst_id||
336 '='||fcst_hdr_tbl(x).orig_forecast ||
337 '='||fcst_hdr_tbl(x).desgn_ind ||
338 '='||fcst_hdr_tbl(x).fcst_name ||
339 '='||fcst_hdr_tbl(x).fcst_set ) ;
340 END LOOP;
341 */
342 -- ===+++++++====++++ build designator++++=======++++=======
343
344 /* nsinghi: Till this point the code ensures that all the forecast_designator
345 names are not duplicated. */
346
347 cnt := 0;
348 OPEN cur_fcst_dtl FOR l_fcst_stmt;
349 LOOP
350 FETCH cur_fcst_dtl INTO fcst_dtl_tbl(cnt);
351 EXIT WHEN cur_fcst_dtl%NOTFOUND;
352 cnt := cnt + 1;
353 END LOOP;
354 CLOSE cur_fcst_dtl;
355 cnt := cnt - 1;
356
357 IF fcst_dtl_tbl.COUNT > 0 THEN
358 FOR lp_cnt IN fcst_dtl_tbl.FIRST..fcst_dtl_tbl.LAST
359 LOOP
360 write_fcst := FALSE ;
361 write_fcst_set := FALSE ;
362 write_fcst_item := FALSE;
363
364 IF fcst_dtl_tbl(lp_cnt).forecast_set <> prev_forecast_set THEN
365 fcst_counter := 0;
366 fcst_itm_counter := 0;
367 fcst_itrf_counter := 0;
368 END IF;
369
370 IF fcst_dtl_tbl(lp_cnt).forecast_id <> prev_fcst_id THEN
371 FOR i IN fcst_hdr_tbl.FIRST..fcst_hdr_tbl.LAST
372 LOOP
373 IF fcst_dtl_tbl(lp_cnt).forecast_id = fcst_hdr_tbl(i).fcst_id THEN
374 fcst_locn := i ;
375 EXIT ;
376 END IF ;
377 END LOOP ;
378
379 /* Everytime the forecast changes, check if the new forecast or forecast_set name
380 already exist in Oracle Forecasting.
381 If the forecast or forecast set already exist in the discrete mrp_forecast_designator
382 table, then do not migrate that forecast. That forecast will need to be manually
383 inserted to the discrete forecasting module. */
384
385 -- BEGIN
386
387 l_validation_stmt := ' SELECT forecast_designator, forecast_set '||
388 ' FROM mrp_forecast_designators '||
389 ' WHERE (forecast_designator = '|| '''' || fcst_hdr_tbl(fcst_locn).fcst_set || '''' ||
390 ' OR forecast_designator = '|| '''' || fcst_hdr_tbl(fcst_locn).fcst_name|| '''' || ' ) '||
391 ' AND organization_id = '||fcst_dtl_tbl(lp_cnt).organization_id ;
392
393 IF fcst_valid_tbl.COUNT > 0 THEN
394 fcst_valid_tbl.DELETE;
395 END IF;
396
397 i := 0;
398 OPEN cur_fcst_valid FOR l_validation_stmt;
399 LOOP
400 FETCH cur_fcst_valid INTO fcst_valid_tbl(i);
401 EXIT WHEN cur_fcst_valid%NOTFOUND;
402 i := i + 1;
403 END LOOP ;
404 i := i - 1;
405
406 IF fcst_valid_tbl.COUNT > 0 THEN
407 IF fcst_valid_tbl.COUNT > 1 OR
408 (fcst_valid_tbl.COUNT = 1 AND
409 fcst_valid_tbl(0).forecast_designator = fcst_hdr_tbl(fcst_locn).fcst_name) THEN
410 /* As the forecast name is present in Discrete Oracle Forecasting,
411 so the current forecast and any forecast having the same set as current
412 forecast's set should not be migrated. */
413 /* 1. Dont write this forecast and forecast_set.
414 2. Dont write any forecast having fcst_set as set
415 3. Decrease the index to overwrite the already written row.*/
416 fcst_dtl_tbl(lp_cnt).write_row_flag := 0;
417 x_failure_count := x_failure_count + 1;
418 FOR i IN lp_cnt..fcst_dtl_tbl.LAST
419 LOOP
420 IF fcst_dtl_tbl(lp_cnt).forecast_set = fcst_dtl_tbl(i).forecast_set THEN
421 fcst_dtl_tbl(i).write_row_flag := 0;
422 x_failure_count := x_failure_count + 1;
423 ELSIF fcst_dtl_tbl(i).forecast_set > fcst_dtl_tbl(lp_cnt).forecast_set THEN
424 EXIT;
425 END IF;
426 END LOOP;
427
428 IF fcst_counter > 0 THEN
429 /* Atleast 1 or more forecast belonging to this forecast_set has already
430 been written and needs to be overridden. */
431 x_failure_count := x_failure_count + fcst_counter;
432 i_index := i_index - fcst_counter ;
433 fi_index := fi_index - fcst_itm_counter ;
434 f_int_index := f_int_index - fcst_itrf_counter ;
435 /* Resetting the counter values. If the same forecast name appears for
436 different org, the index will not get decremented by counters again. */
437 fcst_counter := 0;
438 fcst_itm_counter := 0;
439 fcst_itrf_counter := 0;
440 END IF;
441
442 /* Log Messages */
443 IF (fcst_valid_tbl.COUNT = 1 AND
444 fcst_valid_tbl(0).forecast_designator = fcst_hdr_tbl(fcst_locn).fcst_name) THEN
445
446 /* FND_FILE.PUT_LINE( FND_FILE.LOG, 'Oracle Process Manufacturing Forecast : '
447 || fcst_hdr_tbl(fcst_locn).fcst_name ||' is defined in Oracle Forecasting ' );
448 FND_FILE.PUT_LINE( FND_FILE.LOG, 'Any Forecast belonging to Forecast Set : '
449 || fcst_hdr_tbl(fcst_locn).fcst_set ||' will NOT be migrated to Oracle Forecasting ' );
450 */
451 GMA_COMMON_LOGGING.gma_migration_central_log (
452 p_run_id => P_migration_run_id,
453 p_log_level => FND_LOG.LEVEL_ERROR,
454 p_message_token => 'GMA_MIGRATION_FAIL',
455 p_table_name => 'FC_FCST_HDR',
456 p_context => 'DUPLICATE_FORECAST',
457 p_app_short_name => 'GMP',
458 P_Param1 => ' Forecast '||fcst_hdr_tbl(fcst_locn).fcst_name||' is already defined in Oracle Forecasting ',
459 P_Param2 => 'Any forecast belonging to Forecast Set '||fcst_hdr_tbl(fcst_locn).fcst_set||' will not be migrated. ');
460 END IF;
461 IF (fcst_valid_tbl.COUNT > 1) THEN
462 /*
463 FND_FILE.PUT_LINE( FND_FILE.LOG, 'Both, Oracle Process Manufacturing Forecast : '
464 || fcst_hdr_tbl(fcst_locn).fcst_name ||' and Forecast Set : '||fcst_hdr_tbl(fcst_locn).fcst_set
465 ||' are defined in Oracle Forecasting ' );
466 FND_FILE.PUT_LINE( FND_FILE.LOG, 'Any Forecast belonging to Forecast Set : '
467 || fcst_hdr_tbl(fcst_locn).fcst_set ||' will NOT be migrated to Oracle Forecasting ' );
468 */
469 GMA_COMMON_LOGGING.gma_migration_central_log (
470 p_run_id => P_migration_run_id,
471 p_log_level => FND_LOG.LEVEL_ERROR,
472 p_message_token => 'GMA_MIGRATION_FAIL',
473 p_table_name => 'FC_FCST_HDR',
474 p_context => 'DUPLICATE_FORECAST',
475 p_app_short_name => 'GMP',
476 P_Param1 => 'Both forecast '||fcst_hdr_tbl(fcst_locn).fcst_name||' and forecast set '||fcst_hdr_tbl(fcst_locn).fcst_set||' are already defined in Oracle Forecasting ',
477 P_Param2 => 'Any forecast belonging to Forecast Set '||fcst_hdr_tbl(fcst_locn).fcst_set||' will not be migrated. ');
478 END IF;
479
480 ELSIF fcst_valid_tbl.COUNT = 1 AND
481 fcst_valid_tbl(0).forecast_designator = fcst_hdr_tbl(fcst_locn).fcst_set THEN
482 /* As the order by clause is on forecast set and as forecast set is defined in
483 Discrete Oracle Forecasting, hence do not send any forecast associated to this
484 forecast set.
485 1. Dont write this forecast and forecast_set.
486 2. Loop forward through the fcst_dtl_tbl(lp_cnt) and set the write_row_flag to 0 for
487 all rows having this forecast_set as set. */
488 fcst_dtl_tbl(lp_cnt).write_row_flag := 0;
489 FOR i IN lp_cnt..fcst_dtl_tbl.LAST
490 LOOP
491 IF fcst_dtl_tbl(lp_cnt).forecast_set = fcst_dtl_tbl(i).forecast_set THEN
492 fcst_dtl_tbl(i).write_row_flag := 0;
493 x_failure_count := x_failure_count + 1;
494 END IF;
495 END LOOP;
496 /*
497 FND_FILE.PUT_LINE( FND_FILE.LOG, 'Oracle Process Manufacturing Forecast Set : '
498 || fcst_hdr_tbl(fcst_locn).fcst_set ||' is defined in Oracle Forecasting ' );
499 FND_FILE.PUT_LINE( FND_FILE.LOG, 'Forecast : '|| fcst_hdr_tbl(fcst_locn).fcst_name
500 ||' belonging to above Forecast Set will NOT be migrated to Oracle Forecasting ' );
501 */
502 GMA_COMMON_LOGGING.gma_migration_central_log (
503 p_run_id => P_migration_run_id,
504 p_log_level => FND_LOG.LEVEL_ERROR,
505 p_message_token => 'GMA_MIGRATION_FAIL',
506 p_table_name => 'FC_FCST_HDR',
507 p_context => 'DUPLICATE_FORECAST',
508 p_app_short_name => 'GMP',
509 P_Param1 => ' Forecast set '||fcst_hdr_tbl(fcst_locn).fcst_set||' is already defined in Oracle Forecasting ',
510 P_Param2 => 'Forecast : '|| fcst_hdr_tbl(fcst_locn).fcst_name ||' belonging to above Forecast Set will NOT be migrated to Oracle Forecasting ');
511
512 END IF; /* fcst_valid_tbl.COUNT > 1 */
513 END IF; /* fcst_valid_tbl.COUNT > 0 */
514 END IF; /* fcst_dtl_tbl(lp_cnt).forecast_id <> prev_fcst_id */
515
516
517
518 IF fcst_dtl_tbl(lp_cnt).forecast_set <> prev_forecast_set THEN
519 write_fcst_set := TRUE ;
520 write_fcst := TRUE ;
521 write_fcst_item := TRUE;
522
523 ELSIF fcst_dtl_tbl(lp_cnt).organization_id <> prev_org_id THEN
524 write_fcst_set := TRUE ;
525 write_fcst := TRUE ;
526 write_fcst_item := TRUE;
527
528 ELSIF fcst_dtl_tbl(lp_cnt).forecast_id <> prev_fcst_id THEN
529 write_fcst := TRUE ;
530 write_fcst_item := TRUE;
531
532 ELSIF fcst_dtl_tbl(lp_cnt).inventory_item_id <> prev_fcst_item THEN
533 write_fcst_item := TRUE;
534
535 END IF;
536
537 prev_forecast_set := fcst_dtl_tbl(lp_cnt).forecast_set;
538 prev_org_id := fcst_dtl_tbl(lp_cnt).organization_id;
539 prev_fcst_id := fcst_dtl_tbl(lp_cnt).forecast_id;
540 prev_fcst_item := fcst_dtl_tbl(lp_cnt).inventory_item_id;
541
542 IF write_fcst_set AND fcst_dtl_tbl(lp_cnt).write_row_flag = 1 THEN
543
544 -- Write the Forecast Set Details to MRP_FORECAST_DESIGNATORS.
545 i_index := i_index + 1 ;
546 i_designator(i_index) := fcst_hdr_tbl(fcst_locn).fcst_set ;
547 i_forecast_set(i_index) := to_char(NULL) ;
548 i_organization_id(i_index) := fcst_dtl_tbl(lp_cnt).organization_id ;
549 i_description(i_index) := fcst_hdr_tbl(fcst_locn).fcst_set ;
550 i_disable_date(i_index) := TO_DATE(NULL); /* disable date */
551 i_consume_forecast(i_index) := NVL(fcst_hdr_tbl(fcst_locn).consumption_ind,2) ;
552 i_backward_update_time_fence(i_index) := fcst_hdr_tbl(fcst_locn).backward_time_fence ;
553 i_forward_update_time_fence(i_index) := fcst_hdr_tbl(fcst_locn).forward_time_fence ;
554
555 -- prev_fcst_set := fcst_hdr_tbl(fcst_locn).fcst_set ;
556 fcst_counter := fcst_counter + 1;
557
558 END IF ;
559
560 IF write_fcst AND fcst_dtl_tbl(lp_cnt).write_row_flag = 1 THEN
561
562 -- Write the Forecast Details to MRP_FORECAST_DESIGNATORS.
563 i_index := i_index + 1 ;
564 i_designator(i_index) := fcst_hdr_tbl(fcst_locn).fcst_name ;
565 i_forecast_set(i_index) := fcst_hdr_tbl(fcst_locn).fcst_set ;
566 i_organization_id(i_index) := fcst_dtl_tbl(lp_cnt).organization_id ;
567 i_description(i_index) := fcst_hdr_tbl(fcst_locn).fcst_name ;
568 i_disable_date(i_index) := TO_DATE(NULL); /* disable date */
569 i_consume_forecast(i_index) := NVL(fcst_hdr_tbl(fcst_locn).consumption_ind,2) ;
570 i_backward_update_time_fence(i_index) := fcst_hdr_tbl(fcst_locn).backward_time_fence ;
571 i_forward_update_time_fence(i_index) := fcst_hdr_tbl(fcst_locn).forward_time_fence ;
572 fcst_counter := fcst_counter + 1;
573
574 END IF ;
575
576 IF write_fcst_item AND fcst_dtl_tbl(lp_cnt).write_row_flag = 1 THEN
577
578 fcst_itm_counter := fcst_itm_counter + 1;
579
580 -- Write the Forecast Items Details to MRP_FORECAST_ITEMS.
581 /* Demands Bulk inserts */
582 fi_index := fi_index + 1 ;
583 f_organization_id(fi_index) := fcst_dtl_tbl(lp_cnt).organization_id ;
584 f_inventory_item_id(fi_index) := fcst_dtl_tbl(lp_cnt).inventory_item_id ;
585 f_forecast_designator(fi_index) := fcst_hdr_tbl(fcst_locn).fcst_name ; /* forecast designator */
586
587 END IF;
588
589 IF fcst_dtl_tbl(lp_cnt).write_row_flag = 1 THEN
590 fcst_itrf_counter := fcst_itrf_counter + 1;
591
592 -- Write Forecast Dates and Qty Details to Interface Table MRP_FORECAST_INTERFACE.
593 f_int_index := f_int_index + 1 ;
594 f_int_organization_id(f_int_index) := fcst_dtl_tbl(lp_cnt).organization_id ;
595 f_int_inventory_item_id(f_int_index) := fcst_dtl_tbl(lp_cnt).inventory_item_id ;
596 f_int_forecast_date(f_int_index) := fcst_dtl_tbl(lp_cnt).trans_date ;
597 f_int_quantity(f_int_index) := fcst_dtl_tbl(lp_cnt).trans_qty ;
598 f_int_forecast_designator(f_int_index) := fcst_hdr_tbl(fcst_locn).fcst_name ; /* forecast designator */
599 END IF;
600
601 END LOOP ;
602 END IF;
603
604 /* ----------------------- Designator Insert --------------------- */
605
606 i := 1 ;
607 -- fnd_file.put_line( FND_FILE.LOG, i_organization_id.FIRST || ' *Designator*' || i_index );
608 IF i_organization_id.FIRST > 0 THEN
609
610 GMA_COMMON_LOGGING.gma_migration_central_log (
611 p_run_id => P_migration_run_id,
612 p_log_level => FND_LOG.LEVEL_PROCEDURE,
613 p_message_token => 'GMA_MIGRATION_TABLE_STARTED',
614 p_table_name => 'MRP_FORECAST_DESIGNATORS',
615 p_context => 'FORECAST',
616 p_app_short_name => 'GMP');
617
618 FOR i IN i_organization_id.FIRST..i_index
619 LOOP
620 INSERT INTO mrp_forecast_designators (
621 forecast_designator,
622 forecast_set,
623 organization_id,
624 description,
625 disable_date,
626 consume_forecast,
627 update_type,
628 backward_update_time_fence,
629 foreward_update_time_fence,
630 bucket_type,
631 last_update_date,
632 last_updated_by,
633 creation_date,
634 created_by
635 )
636 VALUES (
637 i_designator(i) ,
638 i_forecast_set(i) ,
639 i_organization_id(i),
640 i_description(i) ,
641 i_disable_date(i) ,
642 i_consume_forecast(i),
643 6, /* Update Type,For Process value will be 6 */
644 i_backward_update_time_fence(i),
645 i_forward_update_time_fence(i) ,
646 1, /* bucket_type */
647 SYSDATE,
648 0,
649 SYSDATE,
650 0
651 ) ;
652 END LOOP;
653
654 END IF ;
655
656 /* ----------------------- Forecast Item Insert --------------------- */
657 i := 1 ;
658 -- fnd_file.put_line( FND_FILE.LOG, f_organization_id.FIRST || ' *Forecast Item*' || fi_index );
659 IF f_organization_id.FIRST > 0 THEN
660
661 GMA_COMMON_LOGGING.gma_migration_central_log (
662 p_run_id => P_migration_run_id,
663 p_log_level => FND_LOG.LEVEL_PROCEDURE,
664 p_message_token => 'GMA_MIGRATION_TABLE_STARTED',
665 p_table_name => 'MRP_FORECAST_ITEMS',
666 p_context => 'FORECAST',
667 p_app_short_name => 'GMP');
668
669 FOR i IN f_organization_id.FIRST..fi_index
670 LOOP
671 INSERT INTO mrp_forecast_items (
672 organization_id,
673 inventory_item_id,
674 forecast_designator,
675 last_update_date, /* Confirm WHO Column values*/
676 last_updated_by,
677 creation_date,
678 created_by
679 )
680 VALUES (
681 f_organization_id(i),
682 f_inventory_item_id(i),
683 f_forecast_designator(i),
684 SYSDATE,
685 0,
686 SYSDATE,
687 0
688 ) ;
689 END LOOP;
690 END IF ;
691
692 IF p_commit = FND_API.G_TRUE THEN
693 COMMIT;
694 END IF;
695
696 IF i_organization_id.FIRST > 0 THEN
697
698 GMA_COMMON_LOGGING.gma_migration_central_log (
699 p_run_id => P_migration_run_id,
700 p_log_level => FND_LOG.LEVEL_PROCEDURE,
701 p_message_token => 'GMA_MIGRATION_TABLE_SUCCESS',
702 p_table_name => 'MRP_FORECAST_DESIGNATORS',
703 p_context => 'FORECAST',
704 p_app_short_name => 'GMP');
705
706 END IF;
707
708 IF f_organization_id.FIRST > 0 THEN
709
710 GMA_COMMON_LOGGING.gma_migration_central_log (
711 p_run_id => P_migration_run_id,
712 p_log_level => FND_LOG.LEVEL_PROCEDURE,
713 p_message_token => 'GMA_MIGRATION_TABLE_SUCCESS',
714 p_table_name => 'MRP_FORECAST_ITEMS',
715 p_context => 'FORECAST',
716 p_app_short_name => 'GMP');
717
718 END IF;
719
720 /* ----------------------- Forecast Interface Insert --------------------- */
721 i := 1 ;
722 -- fnd_file.put_line( FND_FILE.LOG, f_organization_id.FIRST || ' *Forecast Interface*' || f_int_index );
723 IF f_int_organization_id.FIRST > 0 THEN
724
725 GMA_COMMON_LOGGING.gma_migration_central_log (
726 p_run_id => P_migration_run_id,
727 p_log_level => FND_LOG.LEVEL_PROCEDURE,
728 p_message_token => 'GMA_MIGRATION_TABLE_STARTED',
729 p_table_name => 'MRP_FORECAST_INTERFACE',
730 p_context => 'FORECAST',
731 p_app_short_name => 'GMP');
732
733 FOR i IN f_int_organization_id.FIRST..f_int_index
734 LOOP
735 INSERT INTO mrp_forecast_interface (
736 organization_id,
737 inventory_item_id,
738 forecast_date,
739 quantity,
740 bucket_type,
741 forecast_designator,
742 process_status,
743 confidence_percentage,
744 workday_control,
745 last_update_date, /* Confirm WHO Column values*/
746 last_updated_by,
747 creation_date,
748 created_by
749 )
750 VALUES (
751 f_int_organization_id(i),
752 f_int_inventory_item_id(i),
753 f_int_forecast_date(i),
754 f_int_quantity(i),
755 1, /* bucket_type */
756 f_int_forecast_designator(i),
757 2, /* process_status */
758 100, /* Need to confirm the value */
759 3, /* workday_control */
760 SYSDATE,
761 0,
762 SYSDATE,
763 0
764 ) ;
765 END LOOP;
766 END IF ;
767
768 IF p_commit = FND_API.G_TRUE THEN
769 COMMIT;
770 END IF;
771
772 IF f_int_organization_id.FIRST > 0 THEN
773 GMA_COMMON_LOGGING.gma_migration_central_log (
774 p_run_id => P_migration_run_id,
775 p_log_level => FND_LOG.LEVEL_PROCEDURE,
776 p_message_token => 'GMA_MIGRATION_TABLE_SUCCESS',
777 p_table_name => 'MRP_FORECAST_INTERFACE',
778 p_context => 'FORECAST',
779 p_app_short_name => 'GMP');
780 END IF;
781
782 IF f_organization_id.FIRST > 0 THEN
783
784 return_flag := fnd_concurrent.get_request_status
785 (
786 request_id,
787 'MRP',
788 'MRCRLF',
789 phase,
790 status,
791 dev_phase,
792 dev_status,
793 return_message
794 );
795
796 IF ((dev_phase <> 'RUNNING') AND (dev_phase <> 'PENDING')) THEN
797
798 l_conc_id := Fnd_Request.Submit_Request('MRP', 'MRCRLF', '', '', FALSE,
799 30, chr(0), '', '',
800 '','','','','','','','','','','','','','','','','','','','',
801 '','','','','','','','','','','','','','','','','','','','',
802 '','','','','','','','','','','','','','','','','','','','',
803 '','','','','','','','','','','','','','','','','','','','',
804 '','','','','','','','','','','','','','','','');
805 END IF;
806 END IF;
807 EXCEPTION
808
809 WHEN OTHERS THEN
810
811 GMA_COMMON_LOGGING.gma_migration_central_log (
812 p_run_id => P_migration_run_id,
813 p_log_level => FND_LOG.LEVEL_UNEXPECTED,
814 p_message_token => 'GMA_MIGRATION_DB_ERROR',
815 p_table_name => 'FC_FCST_HDR',
816 p_context => 'FORECAST',
817 p_db_error => SQLERRM,
818 p_app_short_name => 'GMP');
819
820 ROLLBACK;
821
822 /*
823 fnd_file.put_line( FND_FILE.LOG, 'Failure occured during the Migrate_Forecast');
824 fnd_file.put_line( FND_FILE.LOG,SQLERRM);
825 ROLLBACK;
826 */
827
828 END Exec_forecast_Migration ;
829
830 END GMP_forecast_migration;