[Home] [Help]
PACKAGE BODY: APPS.GMP_FORECAST_MIGRATION
Source
1 PACKAGE BODY GMP_forecast_migration AS
2 /* $Header: GMPFCMIB.pls 120.4 2006/04/12 04:19:35 sowsubra noship $ */
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
214 -- ===+++++++====++++ build designator++++=======++++=======
215 l_design_stmt := 'SELECT '||
216 ' forecast_id, '||
217 ' forecast, '||
218 ' substr(forecast,1,10) DESGN, '||
219 ' nvl(forecast_set ,substr(forecast,1,10)) FSET, '||
220 ' 1 DESGN_IND ,' ||
221 ' nvl(consumption_ind, 2), '||
222 ' backward_time_fence, '||
223 ' forward_time_fence '||
224 ' FROM fc_fcst_hdr'||
225 ' WHERE delete_mark = 0 '||
226 ' UNION ALL '||
227 -- Add forecast_sets to the list
228 ' SELECT '||
229 ' -1 , '||
230 ' min(forecast), '||
231 ' forecast_set DESGN , '||
232 ' to_char(NULL) FSET, '||
233 ' 3 DESGN_IND, ' ||
234 ' to_number(NULL), '||
235 ' to_number(NULL), '||
236 ' to_number(NULL) '||
237 ' FROM fc_fcst_hdr'||
238 ' WHERE delete_mark = 0 '||
239 ' AND forecast_set is NOT NULL '||
240 ' GROUP BY forecast_set ' ||
241 ' ORDER BY FSET, 1 DESC , DESGN_IND ' ;
242
243 OPEN fcst_hdr FOR l_design_stmt ;
244 LOOP
245 FETCH fcst_hdr INTO fcst_hdr_tbl(l_cnt);
246 EXIT WHEN fcst_hdr%NOTFOUND ;
247 l_cnt := l_cnt + 1 ;
248 END LOOP ;
249 CLOSE fcst_hdr ;
250 -- ===================== Logic ==============================
251 LOOP
252 EXIT WHEN cnt + 1 > fcst_hdr_tbl.COUNT ;
253
254 IF duplicate_found THEN
255 cnt := cnt ;
256 duplicate_found := FALSE ;
257 ELSE
258 IF temp_name IS NOT NULL THEN
259 IF (fcst_hdr_tbl(cnt).desgn_ind = 1
260 AND fcst_hdr_tbl(cnt).fcst_name <> temp_name )THEN
261 -- fcst_hdr_tbl(cnt).fcst_set := temp_name ;
262 NULL ;
263 ELSIF (fcst_hdr_tbl(cnt).desgn_ind = 3
264 AND fcst_hdr_tbl(cnt).fcst_name <> temp_name )THEN
265 -- This means we changed a set name
266 -- Now change the name in all resords of fcst that used this as set
267 FOR y IN 1..fcst_hdr_tbl.COUNT
268 LOOP
269 IF (fcst_hdr_tbl(y).fcst_set = fcst_hdr_tbl(cnt).fcst_name
270 AND fcst_hdr_tbl(y).desgn_ind = 1 ) THEN
271 fcst_hdr_tbl(y).fcst_set := temp_name ;
272 END IF ;
273 END LOOP;
274 /* nsinghi : Commented the following elsif clause as we will not generate set names. */
275 /* ELSIF (fcst_hdr_tbl(cnt).desgn_ind = 2
276 AND fcst_hdr_tbl(cnt).fcst_name <> temp_name )THEN
277 -- This means we changed a set name that was "generated"
278 -- Now change the name in the resord of fcst that used itself as set
279 FOR y in 1..fcst_hdr_tbl.COUNT
280 LOOP
281 IF (fcst_hdr_tbl(y).orig_forecast = fcst_hdr_tbl(cnt).orig_forecast
282 AND fcst_hdr_tbl(y).desgn_ind = 1 )THEN
283 fcst_hdr_tbl(y).fcst_set := temp_name ;
284 END IF ;
285 END LOOP;
286 */
287 END IF ; -- desgn_ind check
288 fcst_hdr_tbl(cnt).fcst_name := temp_name ;
289 END IF ;
290 cnt := cnt + 1 ;
291 j := 10 ;
292 k := 0 ;
293 END IF ;
294
295 IF j < 10 THEN
296 temp_name := substr(fcst_hdr_tbl(cnt).fcst_name,1,j)||to_char(k) ;
297 ELSE
298 temp_name := fcst_hdr_tbl(cnt).fcst_name ;
299 END IF ;
300
301 curr_cnt := cnt ;
302 i := 1 ;
303
304 LOOP
305 EXIT WHEN i > fcst_hdr_tbl.COUNT ;
306 IF i <> curr_cnt THEN
307 -- so that record is not compared to itself
308 IF temp_name = fcst_hdr_tbl(i).fcst_name THEN
309 duplicate_found := TRUE ;
310 k := k + 1 ;
311 IF k < 10 THEN
312 j := 9 ;
313 ELSIF k < 100 THEN
314 j := 8 ;
315 ELSIF k < 1000 THEN
316 j := 7 ;
317 ELSIF k < 10000 THEN
318 j := 6 ;
319 ELSIF k < 100000 THEN
320 j := 5 ;
321 END IF ;
322 EXIT ;
323 END IF ;
324 END IF ; -- i <> curr_cnt
325 i := i + 1 ;
326 END LOOP ;
327
328 END LOOP ; -- Outer loop
329
330 /*
331 FOR x in 1..fcst_hdr_tbl.COUNT
332 LOOP
333 dbms_output.put_line(fcst_hdr_tbl(x).fcst_id||
334 '='||fcst_hdr_tbl(x).orig_forecast ||
335 '='||fcst_hdr_tbl(x).desgn_ind ||
336 '='||fcst_hdr_tbl(x).fcst_name ||
337 '='||fcst_hdr_tbl(x).fcst_set ) ;
338 END LOOP;
339 */
340 -- ===+++++++====++++ build designator++++=======++++=======
341
342 /* nsinghi: Till this point the code ensures that all the forecast_designator
343 names are not duplicated. */
344
345 cnt := 0;
346 OPEN cur_fcst_dtl FOR l_fcst_stmt;
347 LOOP
348 FETCH cur_fcst_dtl INTO fcst_dtl_tbl(cnt);
349 EXIT WHEN cur_fcst_dtl%NOTFOUND;
350 cnt := cnt + 1;
351 END LOOP;
352 CLOSE cur_fcst_dtl;
353 cnt := cnt - 1;
354
355 IF fcst_dtl_tbl.COUNT > 0 THEN
356 FOR lp_cnt IN fcst_dtl_tbl.FIRST..fcst_dtl_tbl.LAST
357 LOOP
358 write_fcst := FALSE ;
359 write_fcst_set := FALSE ;
360 write_fcst_item := FALSE;
361
362 IF fcst_dtl_tbl(lp_cnt).forecast_set <> prev_forecast_set THEN
363 fcst_counter := 0;
364 fcst_itm_counter := 0;
365 fcst_itrf_counter := 0;
366 END IF;
367
368 IF fcst_dtl_tbl(lp_cnt).forecast_id <> prev_fcst_id THEN
369 FOR i IN fcst_hdr_tbl.FIRST..fcst_hdr_tbl.LAST
370 LOOP
371 IF fcst_dtl_tbl(lp_cnt).forecast_id = fcst_hdr_tbl(i).fcst_id THEN
372 fcst_locn := i ;
373 EXIT ;
374 END IF ;
375 END LOOP ;
376
377 /* Everytime the forecast changes, check if the new forecast or forecast_set name
378 already exist in Oracle Forecasting.
379 If the forecast or forecast set already exist in the discrete mrp_forecast_designator
380 table, then do not migrate that forecast. That forecast will need to be manually
381 inserted to the discrete forecasting module. */
382
383 -- BEGIN
384
385 l_validation_stmt := ' SELECT forecast_designator, forecast_set '||
386 ' FROM mrp_forecast_designators '||
387 ' WHERE (forecast_designator = '|| '''' || fcst_hdr_tbl(fcst_locn).fcst_set || '''' ||
388 ' OR forecast_designator = '|| '''' || fcst_hdr_tbl(fcst_locn).fcst_name|| '''' || ' ) '||
389 ' AND organization_id = '||fcst_dtl_tbl(lp_cnt).organization_id ;
390
391 IF fcst_valid_tbl.COUNT > 0 THEN
392 fcst_valid_tbl.DELETE;
393 END IF;
394
395 i := 0;
396 OPEN cur_fcst_valid FOR l_validation_stmt;
397 LOOP
398 FETCH cur_fcst_valid INTO fcst_valid_tbl(i);
399 EXIT WHEN cur_fcst_valid%NOTFOUND;
400 i := i + 1;
401 END LOOP ;
402 i := i - 1;
403
404 IF fcst_valid_tbl.COUNT > 0 THEN
405 IF fcst_valid_tbl.COUNT > 1 OR
406 (fcst_valid_tbl.COUNT = 1 AND
407 fcst_valid_tbl(0).forecast_designator = fcst_hdr_tbl(fcst_locn).fcst_name) THEN
408 /* As the forecast name is present in Discrete Oracle Forecasting,
409 so the current forecast and any forecast having the same set as current
410 forecast's set should not be migrated. */
411 /* 1. Dont write this forecast and forecast_set.
412 2. Dont write any forecast having fcst_set as set
413 3. Decrease the index to overwrite the already written row.*/
414 fcst_dtl_tbl(lp_cnt).write_row_flag := 0;
415 x_failure_count := x_failure_count + 1;
416 FOR i IN lp_cnt..fcst_dtl_tbl.LAST
417 LOOP
418 IF fcst_dtl_tbl(lp_cnt).forecast_set = fcst_dtl_tbl(i).forecast_set THEN
419 fcst_dtl_tbl(i).write_row_flag := 0;
420 x_failure_count := x_failure_count + 1;
421 ELSIF fcst_dtl_tbl(i).forecast_set > fcst_dtl_tbl(lp_cnt).forecast_set THEN
422 EXIT;
423 END IF;
424 END LOOP;
425
426 IF fcst_counter > 0 THEN
427 /* Atleast 1 or more forecast belonging to this forecast_set has already
428 been written and needs to be overridden. */
429 x_failure_count := x_failure_count + fcst_counter;
430 i_index := i_index - fcst_counter ;
431 fi_index := fi_index - fcst_itm_counter ;
432 f_int_index := f_int_index - fcst_itrf_counter ;
433 /* Resetting the counter values. If the same forecast name appears for
434 different org, the index will not get decremented by counters again. */
435 fcst_counter := 0;
436 fcst_itm_counter := 0;
437 fcst_itrf_counter := 0;
438 END IF;
439
440 /* Log Messages */
441 IF (fcst_valid_tbl.COUNT = 1 AND
442 fcst_valid_tbl(0).forecast_designator = fcst_hdr_tbl(fcst_locn).fcst_name) THEN
443
444 /* FND_FILE.PUT_LINE( FND_FILE.LOG, 'Oracle Process Manufacturing Forecast : '
445 || fcst_hdr_tbl(fcst_locn).fcst_name ||' is defined in Oracle Forecasting ' );
446 FND_FILE.PUT_LINE( FND_FILE.LOG, 'Any Forecast belonging to Forecast Set : '
447 || fcst_hdr_tbl(fcst_locn).fcst_set ||' will NOT be migrated to Oracle Forecasting ' );
448 */
449 GMA_COMMON_LOGGING.gma_migration_central_log (
450 p_run_id => P_migration_run_id,
451 p_log_level => FND_LOG.LEVEL_ERROR,
452 p_message_token => 'GMA_MIGRATION_FAIL',
453 p_table_name => 'FC_FCST_HDR',
454 p_context => 'DUPLICATE_FORECAST',
455 p_app_short_name => 'GMP',
456 P_Param1 => ' Forecast '||fcst_hdr_tbl(fcst_locn).fcst_name||' is already defined in Oracle Forecasting ',
457 P_Param2 => 'Any forecast belonging to Forecast Set '||fcst_hdr_tbl(fcst_locn).fcst_set||' will not be migrated. ');
458 END IF;
459 IF (fcst_valid_tbl.COUNT > 1) THEN
460 /*
461 FND_FILE.PUT_LINE( FND_FILE.LOG, 'Both, Oracle Process Manufacturing Forecast : '
462 || fcst_hdr_tbl(fcst_locn).fcst_name ||' and Forecast Set : '||fcst_hdr_tbl(fcst_locn).fcst_set
463 ||' are defined in Oracle Forecasting ' );
464 FND_FILE.PUT_LINE( FND_FILE.LOG, 'Any Forecast belonging to Forecast Set : '
465 || fcst_hdr_tbl(fcst_locn).fcst_set ||' will NOT be migrated to Oracle Forecasting ' );
466 */
467 GMA_COMMON_LOGGING.gma_migration_central_log (
468 p_run_id => P_migration_run_id,
469 p_log_level => FND_LOG.LEVEL_ERROR,
470 p_message_token => 'GMA_MIGRATION_FAIL',
471 p_table_name => 'FC_FCST_HDR',
472 p_context => 'DUPLICATE_FORECAST',
473 p_app_short_name => 'GMP',
474 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 ',
475 P_Param2 => 'Any forecast belonging to Forecast Set '||fcst_hdr_tbl(fcst_locn).fcst_set||' will not be migrated. ');
476 END IF;
477
478 ELSIF fcst_valid_tbl.COUNT = 1 AND
479 fcst_valid_tbl(0).forecast_designator = fcst_hdr_tbl(fcst_locn).fcst_set THEN
480 /* As the order by clause is on forecast set and as forecast set is defined in
481 Discrete Oracle Forecasting, hence do not send any forecast associated to this
482 forecast set.
483 1. Dont write this forecast and forecast_set.
484 2. Loop forward through the fcst_dtl_tbl(lp_cnt) and set the write_row_flag to 0 for
485 all rows having this forecast_set as set. */
486 fcst_dtl_tbl(lp_cnt).write_row_flag := 0;
487 FOR i IN lp_cnt..fcst_dtl_tbl.LAST
488 LOOP
489 IF fcst_dtl_tbl(lp_cnt).forecast_set = fcst_dtl_tbl(i).forecast_set THEN
490 fcst_dtl_tbl(i).write_row_flag := 0;
491 x_failure_count := x_failure_count + 1;
492 END IF;
493 END LOOP;
494 /*
495 FND_FILE.PUT_LINE( FND_FILE.LOG, 'Oracle Process Manufacturing Forecast Set : '
496 || fcst_hdr_tbl(fcst_locn).fcst_set ||' is defined in Oracle Forecasting ' );
497 FND_FILE.PUT_LINE( FND_FILE.LOG, 'Forecast : '|| fcst_hdr_tbl(fcst_locn).fcst_name
498 ||' belonging to above Forecast Set will NOT be migrated to Oracle Forecasting ' );
499 */
500 GMA_COMMON_LOGGING.gma_migration_central_log (
501 p_run_id => P_migration_run_id,
502 p_log_level => FND_LOG.LEVEL_ERROR,
503 p_message_token => 'GMA_MIGRATION_FAIL',
504 p_table_name => 'FC_FCST_HDR',
505 p_context => 'DUPLICATE_FORECAST',
506 p_app_short_name => 'GMP',
507 P_Param1 => ' Forecast set '||fcst_hdr_tbl(fcst_locn).fcst_set||' is already defined in Oracle Forecasting ',
508 P_Param2 => 'Forecast : '|| fcst_hdr_tbl(fcst_locn).fcst_name ||' belonging to above Forecast Set will NOT be migrated to Oracle Forecasting ');
509
510 END IF; /* fcst_valid_tbl.COUNT > 1 */
511 END IF; /* fcst_valid_tbl.COUNT > 0 */
512 END IF; /* fcst_dtl_tbl(lp_cnt).forecast_id <> prev_fcst_id */
513
514
515
516 IF fcst_dtl_tbl(lp_cnt).forecast_set <> prev_forecast_set THEN
517 write_fcst_set := TRUE ;
518 write_fcst := TRUE ;
519 write_fcst_item := TRUE;
520
521 ELSIF fcst_dtl_tbl(lp_cnt).organization_id <> prev_org_id THEN
522 write_fcst_set := TRUE ;
523 write_fcst := TRUE ;
524 write_fcst_item := TRUE;
525
526 ELSIF fcst_dtl_tbl(lp_cnt).forecast_id <> prev_fcst_id THEN
527 write_fcst := TRUE ;
528 write_fcst_item := TRUE;
529
530 ELSIF fcst_dtl_tbl(lp_cnt).inventory_item_id <> prev_fcst_item THEN
531 write_fcst_item := TRUE;
532
533 END IF;
534
535 prev_forecast_set := fcst_dtl_tbl(lp_cnt).forecast_set;
536 prev_org_id := fcst_dtl_tbl(lp_cnt).organization_id;
537 prev_fcst_id := fcst_dtl_tbl(lp_cnt).forecast_id;
538 prev_fcst_item := fcst_dtl_tbl(lp_cnt).inventory_item_id;
539
540 IF write_fcst_set AND fcst_dtl_tbl(lp_cnt).write_row_flag = 1 THEN
541
542 -- Write the Forecast Set Details to MRP_FORECAST_DESIGNATORS.
543 i_index := i_index + 1 ;
544 i_designator(i_index) := fcst_hdr_tbl(fcst_locn).fcst_set ;
545 i_forecast_set(i_index) := to_char(NULL) ;
546 i_organization_id(i_index) := fcst_dtl_tbl(lp_cnt).organization_id ;
547 i_description(i_index) := fcst_hdr_tbl(fcst_locn).fcst_set ;
548 i_disable_date(i_index) := TO_DATE(NULL); /* disable date */
549 i_consume_forecast(i_index) := NVL(fcst_hdr_tbl(fcst_locn).consumption_ind,2) ;
550 i_backward_update_time_fence(i_index) := fcst_hdr_tbl(fcst_locn).backward_time_fence ;
551 i_forward_update_time_fence(i_index) := fcst_hdr_tbl(fcst_locn).forward_time_fence ;
552
553 -- prev_fcst_set := fcst_hdr_tbl(fcst_locn).fcst_set ;
554 fcst_counter := fcst_counter + 1;
555
556 END IF ;
557
558 IF write_fcst AND fcst_dtl_tbl(lp_cnt).write_row_flag = 1 THEN
559
560 -- Write the Forecast Details to MRP_FORECAST_DESIGNATORS.
561 i_index := i_index + 1 ;
562 i_designator(i_index) := fcst_hdr_tbl(fcst_locn).fcst_name ;
563 i_forecast_set(i_index) := fcst_hdr_tbl(fcst_locn).fcst_set ;
564 i_organization_id(i_index) := fcst_dtl_tbl(lp_cnt).organization_id ;
565 i_description(i_index) := fcst_hdr_tbl(fcst_locn).fcst_name ;
566 i_disable_date(i_index) := TO_DATE(NULL); /* disable date */
567 i_consume_forecast(i_index) := NVL(fcst_hdr_tbl(fcst_locn).consumption_ind,2) ;
568 i_backward_update_time_fence(i_index) := fcst_hdr_tbl(fcst_locn).backward_time_fence ;
569 i_forward_update_time_fence(i_index) := fcst_hdr_tbl(fcst_locn).forward_time_fence ;
570 fcst_counter := fcst_counter + 1;
571
572 END IF ;
573
574 IF write_fcst_item AND fcst_dtl_tbl(lp_cnt).write_row_flag = 1 THEN
575
576 fcst_itm_counter := fcst_itm_counter + 1;
577
578 -- Write the Forecast Items Details to MRP_FORECAST_ITEMS.
579 /* Demands Bulk inserts */
580 fi_index := fi_index + 1 ;
581 f_organization_id(fi_index) := fcst_dtl_tbl(lp_cnt).organization_id ;
582 f_inventory_item_id(fi_index) := fcst_dtl_tbl(lp_cnt).inventory_item_id ;
583 f_forecast_designator(fi_index) := fcst_hdr_tbl(fcst_locn).fcst_name ; /* forecast designator */
584
585 END IF;
586
587 IF fcst_dtl_tbl(lp_cnt).write_row_flag = 1 THEN
588 fcst_itrf_counter := fcst_itrf_counter + 1;
589
590 -- Write Forecast Dates and Qty Details to Interface Table MRP_FORECAST_INTERFACE.
591 f_int_index := f_int_index + 1 ;
592 f_int_organization_id(f_int_index) := fcst_dtl_tbl(lp_cnt).organization_id ;
593 f_int_inventory_item_id(f_int_index) := fcst_dtl_tbl(lp_cnt).inventory_item_id ;
594 f_int_forecast_date(f_int_index) := fcst_dtl_tbl(lp_cnt).trans_date ;
595 f_int_quantity(f_int_index) := fcst_dtl_tbl(lp_cnt).trans_qty ;
596 f_int_forecast_designator(f_int_index) := fcst_hdr_tbl(fcst_locn).fcst_name ; /* forecast designator */
597 END IF;
598
599 END LOOP ;
600 END IF;
601
602 /* ----------------------- Designator Insert --------------------- */
603
604 i := 1 ;
605 -- fnd_file.put_line( FND_FILE.LOG, i_organization_id.FIRST || ' *Designator*' || i_index );
606 IF i_organization_id.FIRST > 0 THEN
607
608 GMA_COMMON_LOGGING.gma_migration_central_log (
609 p_run_id => P_migration_run_id,
610 p_log_level => FND_LOG.LEVEL_PROCEDURE,
611 p_message_token => 'GMA_MIGRATION_TABLE_STARTED',
612 p_table_name => 'MRP_FORECAST_DESIGNATORS',
613 p_context => 'FORECAST',
614 p_app_short_name => 'GMP');
615
616 FOR i IN i_organization_id.FIRST..i_index
617 LOOP
618 INSERT INTO mrp_forecast_designators (
619 forecast_designator,
620 forecast_set,
621 organization_id,
622 description,
623 disable_date,
624 consume_forecast,
625 update_type,
626 backward_update_time_fence,
627 foreward_update_time_fence,
628 bucket_type,
629 last_update_date,
630 last_updated_by,
631 creation_date,
632 created_by
633 )
634 VALUES (
635 i_designator(i) ,
636 i_forecast_set(i) ,
637 i_organization_id(i),
638 i_description(i) ,
639 i_disable_date(i) ,
640 i_consume_forecast(i),
641 6, /* Update Type,For Process value will be 6 */
642 i_backward_update_time_fence(i),
643 i_forward_update_time_fence(i) ,
644 1, /* bucket_type */
645 SYSDATE,
646 0,
647 SYSDATE,
648 0
649 ) ;
650 END LOOP;
651
652 END IF ;
653
654 /* ----------------------- Forecast Item Insert --------------------- */
655 i := 1 ;
656 -- fnd_file.put_line( FND_FILE.LOG, f_organization_id.FIRST || ' *Forecast Item*' || fi_index );
657 IF f_organization_id.FIRST > 0 THEN
658
659 GMA_COMMON_LOGGING.gma_migration_central_log (
660 p_run_id => P_migration_run_id,
661 p_log_level => FND_LOG.LEVEL_PROCEDURE,
662 p_message_token => 'GMA_MIGRATION_TABLE_STARTED',
663 p_table_name => 'MRP_FORECAST_ITEMS',
664 p_context => 'FORECAST',
665 p_app_short_name => 'GMP');
666
667 FOR i IN f_organization_id.FIRST..fi_index
668 LOOP
669 INSERT INTO mrp_forecast_items (
670 organization_id,
671 inventory_item_id,
672 forecast_designator,
673 last_update_date, /* Confirm WHO Column values*/
674 last_updated_by,
675 creation_date,
676 created_by
677 )
678 VALUES (
679 f_organization_id(i),
680 f_inventory_item_id(i),
681 f_forecast_designator(i),
682 SYSDATE,
683 0,
684 SYSDATE,
685 0
686 ) ;
687 END LOOP;
688 END IF ;
689
690 IF p_commit = FND_API.G_TRUE THEN
691 COMMIT;
692 END IF;
693
694 IF i_organization_id.FIRST > 0 THEN
695
696 GMA_COMMON_LOGGING.gma_migration_central_log (
697 p_run_id => P_migration_run_id,
698 p_log_level => FND_LOG.LEVEL_PROCEDURE,
699 p_message_token => 'GMA_MIGRATION_TABLE_SUCCESS',
700 p_table_name => 'MRP_FORECAST_DESIGNATORS',
701 p_context => 'FORECAST',
702 p_app_short_name => 'GMP');
703
704 END IF;
705
706 IF f_organization_id.FIRST > 0 THEN
707
708 GMA_COMMON_LOGGING.gma_migration_central_log (
709 p_run_id => P_migration_run_id,
710 p_log_level => FND_LOG.LEVEL_PROCEDURE,
711 p_message_token => 'GMA_MIGRATION_TABLE_SUCCESS',
712 p_table_name => 'MRP_FORECAST_ITEMS',
713 p_context => 'FORECAST',
714 p_app_short_name => 'GMP');
715
716 END IF;
717
718 /* ----------------------- Forecast Interface Insert --------------------- */
719 i := 1 ;
720 -- fnd_file.put_line( FND_FILE.LOG, f_organization_id.FIRST || ' *Forecast Interface*' || f_int_index );
721 IF f_int_organization_id.FIRST > 0 THEN
722
723 GMA_COMMON_LOGGING.gma_migration_central_log (
724 p_run_id => P_migration_run_id,
725 p_log_level => FND_LOG.LEVEL_PROCEDURE,
726 p_message_token => 'GMA_MIGRATION_TABLE_STARTED',
727 p_table_name => 'MRP_FORECAST_INTERFACE',
728 p_context => 'FORECAST',
729 p_app_short_name => 'GMP');
730
731 FOR i IN f_int_organization_id.FIRST..f_int_index
732 LOOP
733 INSERT INTO mrp_forecast_interface (
734 organization_id,
735 inventory_item_id,
736 forecast_date,
737 quantity,
738 bucket_type,
739 forecast_designator,
740 process_status,
741 confidence_percentage,
742 workday_control,
743 last_update_date, /* Confirm WHO Column values*/
744 last_updated_by,
745 creation_date,
746 created_by
747 )
748 VALUES (
749 f_int_organization_id(i),
750 f_int_inventory_item_id(i),
751 f_int_forecast_date(i),
752 f_int_quantity(i),
753 1, /* bucket_type */
754 f_int_forecast_designator(i),
755 2, /* process_status */
756 100, /* Need to confirm the value */
757 3, /* workday_control */
758 SYSDATE,
759 0,
760 SYSDATE,
761 0
762 ) ;
763 END LOOP;
764 END IF ;
765
766 IF p_commit = FND_API.G_TRUE THEN
767 COMMIT;
768 END IF;
769
770 IF f_int_organization_id.FIRST > 0 THEN
771 GMA_COMMON_LOGGING.gma_migration_central_log (
772 p_run_id => P_migration_run_id,
773 p_log_level => FND_LOG.LEVEL_PROCEDURE,
774 p_message_token => 'GMA_MIGRATION_TABLE_SUCCESS',
775 p_table_name => 'MRP_FORECAST_INTERFACE',
776 p_context => 'FORECAST',
777 p_app_short_name => 'GMP');
778 END IF;
779
780 IF f_organization_id.FIRST > 0 THEN
781
782 return_flag := fnd_concurrent.get_request_status
783 (
784 request_id,
785 'MRP',
786 'MRCRLF',
787 phase,
788 status,
789 dev_phase,
790 dev_status,
791 return_message
792 );
793
794 IF ((dev_phase <> 'RUNNING') AND (dev_phase <> 'PENDING')) THEN
795
796 l_conc_id := Fnd_Request.Submit_Request('MRP', 'MRCRLF', '', '', FALSE,
797 30, chr(0), '', '',
798 '','','','','','','','','','','','','','','','','','','','',
799 '','','','','','','','','','','','','','','','','','','','',
800 '','','','','','','','','','','','','','','','','','','','',
801 '','','','','','','','','','','','','','','','','','','','',
802 '','','','','','','','','','','','','','','','');
803 END IF;
804 END IF;
805 EXCEPTION
806
807 WHEN OTHERS THEN
808
809 GMA_COMMON_LOGGING.gma_migration_central_log (
810 p_run_id => P_migration_run_id,
811 p_log_level => FND_LOG.LEVEL_UNEXPECTED,
812 p_message_token => 'GMA_MIGRATION_DB_ERROR',
813 p_table_name => 'FC_FCST_HDR',
814 p_context => 'FORECAST',
815 p_db_error => SQLERRM,
816 p_app_short_name => 'GMP');
817
818 ROLLBACK;
819
820 /*
821 fnd_file.put_line( FND_FILE.LOG, 'Failure occured during the Migrate_Forecast');
822 fnd_file.put_line( FND_FILE.LOG,SQLERRM);
823 ROLLBACK;
824 */
825
826 END Exec_forecast_Migration ;
827
828 END GMP_forecast_migration;