[Home] [Help]
PACKAGE BODY: APPS.RLM_FORECAST_SV
Source
1 PACKAGE BODY RLM_FORECAST_SV as
2 /* $Header: RLMDPFPB.pls 120.7 2011/12/20 07:25:14 sunilku ship $ */
3 /*======================= RLM_FORECAST_SV ============================*/
4
5 --
6 l_DEBUG NUMBER := NVL(fnd_profile.value('RLM_DEBUG_MODE'),-1);
7 --
8 g_MRP_ListName VARCHAR2(30) := NULL; --Bugfix 8326871
9 g_total_time NUMBER:=0; --Bugfix 12863728
10 /*===========================================================================
11
12 PROCEDURE NAME: ManageForecast
13
14 ===========================================================================*/
15 PROCEDURE ManageForecast(x_InterfaceHeaderId IN NUMBER,
16 x_sched_rec IN RLM_INTERFACE_HEADERS%ROWTYPE,
17 x_Group_rec IN OUT NOCOPY rlm_dp_sv.t_Group_rec,
18 x_ReturnStatus OUT NOCOPY NUMBER)
19 IS
20 --
21 v_SubGroup_ref t_Cursor_ref;
22 v_SubGroup_rec rlm_dp_sv.t_Group_rec;
23 x_HeaderStatus NUMBER;
24 x_progress VARCHAR2(3) := '010';
25 t_forecast mrp_forecast_interface_pk.t_forecast_interface;
26 empty_designator mrp_forecast_interface_pk.t_forecast_designator;
27 empty_forecast mrp_forecast_interface_pk.t_forecast_interface;
28 t_designator mrp_forecast_interface_pk.t_forecast_designator;
29 e_forecastapifailed EXCEPTION;
30 e_lines_locked EXCEPTION;
31 e_no_forecast EXCEPTION;
32 v_last NUMBER;
33 v_counter NUMBER;
34 v_mrp_count NUMBER:=0; /*2816086*/
35 v_designator VARCHAR2(10);
36 v_InterfaceLineId NUMBER;
37 /* Bugfix 12863728 */
38 l_start_time NUMBER;
39 l_end_time NUMBER;
40 v_msg_text VARCHAR2(32000);
41 --
42 BEGIN
43 --
44 IF (l_debug <> -1) THEN
45 rlm_core_sv.dpush(k_SDEBUG,'ManageForecast');
46 rlm_core_sv.dlog(k_DEBUG,'x_InterfaceHeaderId',x_InterfaceHeaderId);
47 END IF;
48 --
49 x_ReturnStatus := rlm_core_sv.k_PROC_SUCCESS;
50 --
51 IF NOT rlm_dp_sv.CheckForecast(x_InterfaceHeaderId,x_Group_rec) THEN
52 --
53 IF (l_debug <> -1) THEN
54 rlm_core_sv.dlog(k_DEBUG,'No Forecast processing');
55 END IF;
56 --
57 RAISE e_no_forecast;
58 --
59 END IF;
60 --
61 /* Bill_to information is used to get the designator. Since we
62 do not have the bill to information in the x_Group_rec, we
63 initialize the group (by bill_to) and then process the sub groups */
64 --
65 RLM_TPA_SV.InitializeGroup(x_Sched_rec,
66 v_SubGroup_ref,
67 x_Group_rec);
68 --
69 WHILE FetchGroup(v_SubGroup_ref, v_SubGroup_rec) LOOP
70 --
71 IF x_Group_rec.IsSourced THEN
72 --
73 IF NOT LockLines(v_SubGroup_rec, x_InterfaceHeaderId) THEN
74 --
75 IF (l_debug <> -1) THEN
76 rlm_core_sv.dlog(k_DEBUG,'RLM_LOCK_NOT_OBTAINED');
77 END IF;
78 --
79 RAISE e_lines_locked;
80 --
81 END IF;
82 --
83 END IF;
84 --
85
86 IF (l_debug <> -1) THEN
87 --
88 rlm_core_sv.dlog(k_DEBUG,'customer_id' ,v_subgroup_rec.customer_id);
89 rlm_core_sv.dlog(k_DEBUG,'ship_to_customer_id',v_subgroup_rec.ship_to_customer_id);
90 rlm_core_sv.dlog(k_DEBUG,'ship_from_org_id' ,v_subgroup_rec.ship_from_org_id);
91 rlm_core_sv.dlog(k_DEBUG,'ship_to_org_id' ,v_subgroup_rec.ship_to_address_id);
92 rlm_core_sv.dlog(k_DEBUG,'ship_to_site_use_id' ,v_subgroup_rec.ship_to_site_use_id);
93 rlm_core_sv.dlog(k_DEBUG,'bill_to_address_id' ,v_subgroup_rec.bill_to_address_id);
94 rlm_core_sv.dlog(k_DEBUG,'bill_to_site_use_id' ,v_subgroup_rec.bill_to_site_use_id);
95 rlm_core_sv.dlog(k_DEBUG,'customer_item_id' ,v_subgroup_rec.customer_item_id);
96 rlm_core_sv.dlog(k_DEBUG,'inventory_item_id' ,v_subgroup_rec.inventory_item_id);
97 rlm_core_sv.dlog(k_DEBUG,'industry_attribute15' ,v_subgroup_rec.industry_attribute15);
98 --
99 END IF;
100
101 RLM_TPA_SV.ManageGroupForecast(x_sched_rec,
102 v_SubGroup_rec,
103 t_forecast,
104 t_designator,
105 x_ReturnStatus);
106
107 IF(x_Sched_rec.schedule_purpose = k_REPLACE_ALL) THEN
108
109 ProcessReplaceAll(x_sched_rec,
110 v_SubGroup_rec,
111 t_designator);
112
113 END IF; --check for replace_all
114
115 END LOOP;
116 --
117 CLOSE v_SubGroup_ref;
118
119 IF (l_debug <> -1) THEN
120 --
121 rlm_core_sv.dlog(k_DEBUG,'before mrp_forecast_interface_pk api');
122 --
123 END IF;
124
125 SELECT hsecs INTO l_start_time from v$timer; --Bugfix 12863728
126
127 IF mrp_forecast_interface_pk.mrp_forecast_interface(t_forecast,
128 t_designator) THEN
129 IF (l_debug <> -1) THEN
130 --
131 rlm_core_sv.dlog(k_DEBUG,'after mrp_forecast_interface_pk.
132 mrp_forecast_interface api');
133 --
134 END IF;
135 --
136 SELECT hsecs INTO l_end_time from v$timer; --Bugfix 12863728
137
138 /* Bugfix 12863728 */
139 v_msg_text :='Time spent in MRP call - '|| (l_end_time-l_start_time)/100;
140 fnd_file.put_line(fnd_file.log, v_msg_text);
141 g_total_time := g_total_time + ((l_end_time-l_start_time)/100);
142 --
143 v_msg_text := 'Total time spent in MRP - '|| g_total_time;
144 fnd_file.put_line(fnd_file.log, v_msg_text);
145 --
146 FOR v_counter IN 1..t_designator.COUNT LOOP
147
148 IF (l_debug <> -1) THEN
149 --
150 rlm_core_sv.dlog(k_DEBUG,'Inserted new forecast for ', t_designator(v_counter).forecast_designator);
151 --
152 END IF;
153 END LOOP;
154 --
155 RLM_TPA_SV.ProcessTable(x_Sched_rec, x_Group_rec, t_forecast);
156
157 --
158 ELSE
159 --
160 x_progress :='070';
161 --
162 IF (l_debug <> -1) THEN
163 rlm_core_sv.dlog(k_DEBUG,'MRP Forecast API Failed ');
164 END IF;
165 --
166
167 FOR v_counter IN 1..t_designator.COUNT LOOP
168 --
169 v_designator := t_designator(v_counter).forecast_designator;
170 --
171 END LOOP;
172 --
173 SELECT line_id
174 INTO v_InterfaceLineId
175 FROM rlm_interface_lines
176 WHERE header_id = x_Sched_rec.header_id
177 AND ship_from_org_id = x_Group_rec.ship_from_org_id
178 AND ship_to_address_id = x_Group_rec.ship_to_address_id
179 AND customer_item_id = x_Group_rec.customer_item_id
180 AND rownum = 1;
181 --
182 rlm_message_sv.app_error(
183 x_ExceptionLevel => rlm_message_sv.k_error_level,
184 x_MessageName => 'RLM_FORECAST_API_FAILED',
185 x_InterfaceHeaderId => x_sched_rec.header_id,
186 x_InterfaceLineId => v_InterfaceLineId,
187 x_ScheduleHeaderId => x_sched_rec.schedule_header_id,
188 x_ScheduleLineId => NULL,
189 x_Token1 => 'GROUP',
190 x_value1 => rlm_core_sv.get_ship_from(x_Group_rec.ship_from_org_id)||'-'||
191 rlm_core_sv.get_ship_to(x_Group_rec.ship_to_address_id)||'-'||
192 rlm_core_sv.get_item_number(x_Group_rec.customer_item_id),
193 x_Token2 => 'FORECAST_DESIGNATOR',
194 x_value2 => v_designator);
195 --
196 RAISE e_group_error;
197 --
198 END IF;
199 --
200 t_forecast.DELETE;
201 t_designator.DELETE;
202 --
203 --x_ReturnStatus := rlm_core_sv.k_PROC_SUCCESS;
204 --
205 IF (l_debug <> -1) THEN
206 rlm_core_sv.dpop(k_SDEBUG);
207 END IF;
208 --
209 EXCEPTION
210 --
211 WHEN e_Group_Error THEN
212 --
213 t_forecast.DELETE;
214 t_designator.DELETE;
215 x_ReturnStatus := rlm_core_sv.k_PROC_ERROR;
216 --
217 IF (l_debug <> -1) THEN
218 rlm_core_sv.dlog(k_DEBUG,'progress',x_Progress);
219 rlm_core_sv.dpop(k_SDEBUG, 'GROUP ERROR');
220 END IF;
221 --
222 WHEN e_lines_locked THEN
223 --
224 t_forecast.DELETE;
225 t_designator.DELETE;
226 x_ReturnStatus := rlm_core_sv.k_PROC_ERROR;
227 --
228 SELECT line_id
229 INTO v_InterfaceLineId
230 FROM rlm_interface_lines
231 WHERE header_id = x_Sched_rec.header_id
232 AND ship_from_org_id = x_Group_rec.ship_from_org_id
233 AND ship_to_address_id = x_Group_rec.ship_to_address_id
234 AND customer_item_id = x_Group_rec.customer_item_id
235 AND rownum = 1;
236 --
237 rlm_message_sv.app_error(
238 x_ExceptionLevel => rlm_message_sv.k_error_level,
239 x_MessageName => 'RLM_LOCK_NOT_OBTAINED',
240 x_InterfaceHeaderId => x_sched_rec.header_id,
241 x_InterfaceLineId => v_InterfaceLineId,
242 x_ScheduleHeaderId => x_sched_rec.schedule_header_id,
243 x_ScheduleLineId => NULL,
244 x_Token1 => 'SCHED_REF',
245 x_value1 => x_sched_rec.schedule_reference_num);
246 --
247 IF (l_debug <> -1) THEN
248 rlm_core_sv.dpop(k_DEBUG,'lines are locked');
249 END IF;
250 --
251 WHEN NO_DATA_FOUND THEN
252 --
253 t_forecast.DELETE;
254 t_designator.DELETE;
255 x_ReturnStatus := rlm_core_sv.k_PROC_ERROR;
256 --
257 IF (l_debug <> -1) THEN
258 rlm_core_sv.dlog(k_DEBUG,'No data found in Interface headers for headerId',
259 x_InterfaceHeaderId);
260 rlm_core_sv.dpop(k_SDEBUG);
261 END IF;
262 --
263 WHEN e_no_forecast THEN
264 --
265 IF (l_debug <> -1) THEN
266 rlm_core_sv.dpop(k_SDEBUG,'ManageForecast: e_no_forecast');
267 END IF;
268 --
269 WHEN OTHERS THEN
270 --
271 t_forecast.DELETE;
272 t_designator.DELETE;
273 x_ReturnStatus := rlm_core_sv.k_PROC_ERROR;
274 --
275 rlm_message_sv.sql_error('rlm_forecast_sv.ManageForecast',x_progress);
276 --
277 IF (l_debug <> -1) THEN
278 rlm_core_sv.dpop(k_SDEBUG,'EXCEPTION: '||SUBSTR(SQLERRM,1,200));
279 END IF;
280 --
281 END ManageForecast;
282
283
284 /*===========================================================================
285
286 PROCEDURE NAME: ManageGroupForecast
287
288 ===========================================================================*/
289 PROCEDURE ManageGroupForecast(x_sched_rec IN RLM_INTERFACE_HEADERS%ROWTYPE,
290 x_Group_rec IN OUT NOCOPY rlm_dp_sv.t_Group_rec,
291 x_forecast IN OUT NOCOPY
292 mrp_forecast_interface_pk.t_forecast_interface,
293 x_designator IN OUT NOCOPY
294 mrp_forecast_interface_pk.t_forecast_designator,
295 x_ReturnStatus OUT NOCOPY NUMBER)
296 IS
297 --
298 v_forecast_designator mrp_forecast_designators.forecast_designator%TYPE;
299 x_progress VARCHAR2(3) := '010';
300 v_InterfaceLineId NUMBER;
301 --
302 BEGIN
303 --
304 IF (l_debug <> -1) THEN
305 rlm_core_sv.dpush(k_SDEBUG,'ManageGroupForecast');
306 rlm_core_sv.dlog(k_DEBUG,'x_Group_rec.customer_id',
307 x_Group_rec.customer_id);
308 rlm_core_sv.dlog(k_DEBUG,'x_Group_rec.ship_from_org_id',
309 x_Group_rec.ship_from_org_id);
310 rlm_core_sv.dlog(k_DEBUG,'x_Group_rec.ship_to_site_use_id',
311 x_Group_rec.ship_to_site_use_id);
312 rlm_core_sv.dlog(k_DEBUG,'x_Group_rec.bill_to_site_use_id',
313 x_Group_rec.bill_to_site_use_id);
314 END IF;
315 --
316 RLM_TPA_SV.GetDesignator(x_sched_rec,
317 x_Group_rec,
318 x_Group_rec.customer_id,
319 x_Group_rec.ship_from_org_id,
320 x_Group_rec.ship_to_site_use_id,
321 x_Group_rec.bill_to_site_use_id,
322 x_Group_rec.bill_to_address_id,
323 v_forecast_designator,
324 x_Group_rec.ship_to_customer_id);
325 --
326 -- Validate the forecast designator as it is not done in
327 -- validate demand
328 --
329 IF (l_debug <> -1) THEN
330 rlm_core_sv.dlog(k_DEBUG,'forecast designator',v_forecast_designator);
331 rlm_core_sv.dlog(k_DEBUG,'x_Sched_rec.schedule_purpose',
332 x_Sched_rec.schedule_purpose);
333 END IF;
334 --
335 --
336 -- Bug 2766271 - Validate Forecast Designator
337 --
338 IF v_forecast_designator IS NULL THEN
339 --
340 IF (l_debug <> -1) THEN
341 rlm_core_sv.dlog(k_DEBUG, 'Null Forecast Designator, raising group error');
342 END IF;
343 --
344 SELECT line_id
345 INTO v_InterfaceLineId
346 FROM rlm_interface_lines
347 WHERE header_id = x_Sched_rec.header_id
348 AND ship_from_org_id = x_Group_rec.ship_from_org_id
349 AND ship_to_address_id = x_Group_rec.ship_to_address_id
350 AND customer_item_id = x_Group_rec.customer_item_id
351 AND rownum = 1;
352 --
353 rlm_message_sv.app_error(
354 x_ExceptionLevel => rlm_message_sv.k_error_level,
355 x_MessageName => 'RLM_NO_FORECAST_DESIG',
356 x_InterfaceHeaderId => x_Sched_rec.header_id,
357 x_InterfaceLineId => v_InterfaceLineId,
358 x_ScheduleHeaderId => x_Sched_rec.schedule_header_id,
359 x_ScheduleLineId => NULL,
360 x_Token1=>'CUST',
361 x_Value1=> rlm_core_sv.get_customer_name(x_Sched_rec.customer_id));
362 --
363 RAISE e_Group_Error;
364 --
365 END IF;
366 --
367 IF x_Sched_rec.schedule_purpose = k_ADD THEN
368 --
369 IF (l_debug <> -1) THEN
370 rlm_core_sv.dlog(k_DEBUG,'No Lines in t_designator as schedule purpose = ADD');
371 END IF;
372 --
373 ELSIF x_Sched_rec.schedule_purpose IN (k_CANCEL,
374 k_DELETE,
375 k_REPLACE,
376 k_REPLACE_ALL,
377 k_ORIGINAL,
378 k_CHANGE) THEN
379 --
380 x_designator(x_designator.COUNT + 1).forecast_designator :=
381 v_forecast_designator;
382 x_designator(x_designator.COUNT).organization_id :=
383 x_Group_rec.ship_from_org_id;
384 x_designator(x_designator.COUNT).inventory_item_id :=
385 x_Group_rec.inventory_item_id;
386 --
387 IF (l_debug <> -1) THEN
388 rlm_core_sv.dlog(k_DEBUG,'x_designator.forecast_designator',
389 x_designator(x_designator.COUNT).forecast_designator);
390 rlm_core_sv.dlog(k_DEBUG,'x_designator.organization_id',
391 x_designator(x_designator.COUNT).organization_id);
392 rlm_core_sv.dlog(k_DEBUG,'x_designator.inventory_item_id',
393 x_designator(x_designator.COUNT).inventory_item_id);
394 END IF;
395 --
396 END IF;
397 --
398 IF x_Sched_rec.schedule_purpose NOT IN (k_CANCEL,k_DELETE) THEN
399 --
400 RLM_TPA_SV.LoadForecast(x_Sched_rec,
401 x_Group_rec ,
402 x_forecast,
403 v_forecast_designator);
404 --
405 END IF;
406 --
407 x_ReturnStatus := rlm_core_sv.k_PROC_SUCCESS;
408 --
409 IF (l_debug <> -1) THEN
410 rlm_core_sv.dpop(k_DEBUG);
411 END IF;
412 --
413 EXCEPTION
414 WHEN e_Group_Error THEN
415 --
416 x_ReturnStatus := rlm_core_sv.k_PROC_ERROR;
417 --
418 IF (l_debug <> -1) THEN
419 rlm_core_sv.dlog(k_DEBUG,'ManageGroupForecast : progress',x_Progress);
420 rlm_core_sv.dpop(k_SDEBUG, 'GROUP ERROR');
421 END IF;
422 --
423 RAISE;
424 --
425 WHEN OTHERS THEN
426 --
427 x_ReturnStatus := rlm_core_sv.k_PROC_ERROR;
428 rlm_message_sv.sql_error('rlm_forecast_sv.ManageForecast',x_progress);
429 --
430 IF (l_debug <> -1) THEN
431 rlm_core_sv.dpop(k_SDEBUG,'EXCEPTION: '||SUBSTR(SQLERRM,1,200));
432 END IF;
433 --
434 raise;
435 --
436 END ManageGroupForecast;
437
438 /*===========================================================================
439
440 PROCEDURE InitializeGroup
441
442 ===========================================================================*/
443 PROCEDURE InitializeGroup(x_Sched_rec IN rlm_interface_headers%ROWTYPE,
444 x_Group_ref IN OUT NOCOPY rlm_forecast_sv.t_Cursor_ref,
445 x_Group_rec IN rlm_dp_sv.t_Group_rec)
446 IS
447 x_progress VARCHAR2(3) := '010';
448
449 BEGIN
450 --
451 IF (l_debug <> -1) THEN
452 rlm_core_sv.dpush(k_SDEBUG,'InitializeGroup');
453 END IF;
454 --
455 OPEN x_Group_ref FOR
456 SELECT rih.customer_id,
457 ril.ship_from_org_id,
458 ril.ship_to_address_id,
459 ril.ship_to_site_use_id,
460 ril.bill_to_address_id,
461 ril.bill_to_site_use_id,
462 ril.customer_item_id,
463 ril.inventory_item_id,
464 ril.industry_attribute15,
465 ril.ship_to_customer_id
466 FROM rlm_interface_headers rih,
467 rlm_interface_lines_all ril
468 WHERE rih.header_id = x_Sched_rec.header_id
469 AND ril.header_id = rih.header_id
470 AND ril.industry_attribute15 = x_Group_rec.ship_from_org_id
471 AND ril.item_detail_type = k_MRP_FORECAST
472 AND ril.inventory_item_id = x_Group_rec.inventory_item_id
473 AND ril.ship_to_address_id = x_Group_rec.ship_to_address_id
474 AND ril.process_status = rlm_core_sv.k_PS_AVAILABLE
475 AND rih.org_id = ril.org_id
476 GROUP BY rih.customer_id,
477 ril.ship_from_org_id,
478 ril.ship_to_address_id,
479 ril.ship_to_site_use_id,
480 ril.bill_to_address_id,
481 ril.bill_to_site_use_id,
482 ril.customer_item_id,
483 ril.inventory_item_id,
484 ril.industry_attribute15,
485 ril.ship_to_customer_id
486 ORDER BY
487 ril.ship_to_site_use_id,
488 ril.bill_to_site_use_id,
489 ril.customer_item_id;
490 --
491 IF (l_debug <> -1) THEN
492 rlm_core_sv.dpop(k_SDEBUG);
493 END IF;
494 --
495 EXCEPTION
496 WHEN OTHERS THEN
497 rlm_message_sv.sql_error('RLM_FORECAST_SV.InitializeGroup',x_progress);
498 --
499 IF (l_debug <> -1) THEN
500 rlm_core_sv.dpop(k_SDEBUG,'EXCEPTION: '||SUBSTR(SQLERRM,1,200));
501 END IF;
502 --
503 raise e_group_error;
504
505 END InitializeGroup;
506
507 /*===========================================================================
508
509 FUNCTION FetchGroup
510
511 ===========================================================================*/
512 FUNCTION FetchGroup(x_Group_ref IN OUT NOCOPY t_Cursor_ref,
513 x_Group_rec IN OUT NOCOPY rlm_dp_sv.t_Group_rec)
514 RETURN BOOLEAN
515 IS
516
517 x_progress VARCHAR2(3) := '010';
518 BEGIN
519 --
520 IF (l_debug <> -1) THEN
521 rlm_core_sv.dpush(k_SDEBUG,'FetchGroup');
522 END IF;
523 --
524 FETCH x_Group_ref INTO
525 x_Group_rec.customer_id,
526 x_Group_rec.ship_from_org_id,
527 x_Group_rec.ship_to_address_id,
528 x_Group_rec.ship_to_site_use_id,
529 x_Group_rec.bill_to_address_id,
530 x_Group_rec.bill_to_site_use_id,
531 x_Group_rec.customer_item_id,
532 x_Group_rec.inventory_item_id,
533 x_Group_rec.industry_attribute15,
534 x_Group_rec.ship_to_customer_id;
535 --
536 IF x_Group_ref%NOTFOUND THEN
537 --
538 IF (l_debug <> -1) THEN
539 rlm_core_sv.dpop(k_SDEBUG, 'false');
540 END IF;
541 --
542 RETURN(FALSE);
543 --
544 ELSE
545 --
546 IF (l_debug <> -1) THEN
547 rlm_core_sv.dpop(k_SDEBUG, 'true');
548 END IF;
549 --
550 RETURN(TRUE);
551 --
552 END IF;
553
554 EXCEPTION
555 WHEN OTHERS THEN
556 rlm_message_sv.sql_error('rlm_forecast_sv.FetchGroup',x_progress);
557 --
558 IF (l_debug <> -1) THEN
559 rlm_core_sv.dpop(k_SDEBUG,'EXCEPTION: '||SUBSTR(SQLERRM,1,200));
560 END IF;
561 --
562 raise;
563
564 END FetchGroup;
565
566 /*===========================================================================
567
568 FUNCTION LockHeaders
569
570 ===========================================================================*/
571 FUNCTION LockHeaders (x_header_id IN NUMBER)
572 RETURN BOOLEAN
573 IS
574 x_progress VARCHAR2(3) := '010';
575
576 CURSOR c IS
577 SELECT *
578 FROM rlm_interface_headers
579 WHERE header_id = x_header_id
580 AND process_status = rlm_core_sv.k_PS_AVAILABLE
581 FOR UPDATE NOWAIT;
582
583 BEGIN
584 --
585 IF (l_debug <> -1) THEN
586 rlm_core_sv.dpush(k_SDEBUG,'LockHeaders');
587 rlm_core_sv.dlog(k_DEBUG,'Locking RLM_INTERFACE_HEADERS');
588 END IF;
589 --
590 OPEN c;
591 --
592 CLOSE c;
593 --
594 IF (l_debug <> -1) THEN
595 rlm_core_sv.dlog(k_DEBUG,'Returning True ');
596 rlm_core_sv.dpop(k_SDEBUG);
597 END IF;
598 --
599 RETURN TRUE;
600 --
601 EXCEPTION
602 WHEN APP_EXCEPTION.RECORD_LOCK_EXCEPTION THEN
603 --
604 IF (l_debug <> -1) THEN
605 rlm_core_sv.dlog(k_DEBUG,'Returning FALSE');
606 rlm_core_sv.dlog(k_DEBUG,'progress',x_Progress);
607 rlm_core_sv.dpop(k_SDEBUG,'EXCEPTION: OTHER - sql error');
608 END IF;
609 --
610 RETURN FALSE;
611
612 WHEN OTHERS THEN
613 rlm_message_sv.sql_error('rlm_forecast_sv.LockHeaders',x_progress);
614 --
615 IF (l_debug <> -1) THEN
616 rlm_core_sv.dlog(k_DEBUG,'Returning FALSE from WHEN OTHERS ');
617 rlm_core_sv.dlog(k_DEBUG,'progress',x_Progress);
618 rlm_core_sv.dpop(k_SDEBUG,'EXCEPTION: OTHER - sql error');
619 END IF;
620 --
621 RETURN FALSE;
622
623 END LockHeaders;
624
625 /*===========================================================================
626
627 PROCEDURE UpdateHeaderStatus
628
629 ===========================================================================*/
630 PROCEDURE UpdateHeaderStatus( x_HeaderId IN NUMBER,
631 x_ScheduleHeaderId IN NUMBER,
632 x_status IN NUMBER)
633 IS
634 x_progress VARCHAR2(3) := '010';
635
636 BEGIN
637 --
638 IF (l_debug <> -1) THEN
639 rlm_core_sv.dpush(k_SDEBUG,'UpdateHeaderStatus');
640 rlm_core_sv.dlog(k_DEBUG,'UpdateHeaderStatus to ', x_status);
641 END IF;
642 --
643 UPDATE rlm_interface_headers
644 SET process_status = x_Status
645 WHERE header_id = x_HeaderId;
646 --
647 UPDATE rlm_schedule_headers
648 SET process_status = x_Status
649 WHERE header_id = x_ScheduleHeaderId;
650 --
651 IF (l_debug <> -1) THEN
652 rlm_core_sv.dpop(k_SDEBUG);
653 END IF;
654 --
655 EXCEPTION
656 --
657 WHEN OTHERS THEN
658 --
659 rlm_message_sv.sql_error('rlm_forecast_sv.UpdateHeaderStatus',x_progress);
660 --
661 IF (l_debug <> -1) THEN
662 rlm_core_sv.dlog(k_DEBUG,'progress',x_Progress);
663 rlm_core_sv.dpop(k_SDEBUG,'EXCEPTION: OTHER - sql error');
664 END IF;
665 --
666 RAISE ;
667 --
668 END UpdateHeaderStatus;
669
670 /*===========================================================================
671
672 FUNCTION LockLines
673
674 ===========================================================================*/
675 FUNCTION LockLines (x_Group_rec IN rlm_dp_sv.t_Group_rec,
676 x_header_id IN NUMBER)
677 RETURN BOOLEAN
678 IS
679 x_progress VARCHAR2(3) := '010';
680
681 CURSOR c IS
682 SELECT *
683 FROM rlm_interface_lines_all
684 WHERE header_id = x_header_id
685 AND ship_from_org_id = x_Group_rec.ship_from_org_id
686 AND ship_to_site_use_id = x_Group_rec.ship_to_site_use_id
687 AND bill_to_site_use_id = x_Group_rec.bill_to_site_use_id
688 AND customer_item_id = x_Group_rec.customer_item_id
689 AND inventory_item_id = x_Group_rec.inventory_item_id
690 AND process_status = rlm_core_sv.k_PS_AVAILABLE
691 FOR UPDATE NOWAIT;
692
693 BEGIN
694 --
695 IF (l_debug <> -1) THEN
696 rlm_core_sv.dpush(k_SDEBUG,'LockLines');
697 rlm_core_sv.dlog(k_DEBUG,'Locking RLM_INTERFACE_LINES');
698 END IF;
699 --
700 OPEN c;
701 --
702 CLOSE c;
703 --
704 IF (l_debug <> -1) THEN
705 rlm_core_sv.dlog(k_DEBUG,'Returning True ');
706 rlm_core_sv.dpop(k_SDEBUG);
707 END IF;
708 --
709 RETURN TRUE;
710 --
711 EXCEPTION
712 WHEN APP_EXCEPTION.RECORD_LOCK_EXCEPTION THEN
713 --
714 IF (l_debug <> -1) THEN
715 rlm_core_sv.dlog(k_DEBUG,'Returning FALSE');
716 rlm_core_sv.dlog(k_DEBUG,'progress',x_Progress);
717 rlm_core_sv.dpop(k_SDEBUG,'EXCEPTION: OTHER - sql error');
718 END IF;
719 --
720 RETURN FALSE;
721
722 WHEN OTHERS THEN
723 rlm_message_sv.sql_error('rlm_forecast_sv.LockLines',x_progress);
724 --
725 IF (l_debug <> -1) THEN
726 rlm_core_sv.dlog(k_DEBUG,'Returning FALSE OTHERS ');
727 rlm_core_sv.dlog(k_DEBUG,'progress',x_Progress);
728 rlm_core_sv.dpop(k_SDEBUG,'EXCEPTION: OTHER - sql error');
729 END IF;
730 --
731 RETURN FALSE;
732
733 END LockLines;
734 /*===========================================================================
735
736 FUNCTION UpdateGroupStatus
737
738 ===========================================================================*/
739 PROCEDURE UpdateGroupStatus( x_header_id IN NUMBER,
740 x_ScheduleHeaderId IN NUMBER,
741 x_Group_rec IN rlm_dp_sv.t_Group_rec,
742 x_status IN NUMBER,
743 x_UpdateLevel IN VARCHAR2)
744 IS
745 x_progress VARCHAR2(3) := '010';
746
747 BEGIN
748 --
749 IF (l_debug <> -1) THEN
750 rlm_core_sv.dpush(k_SDEBUG,'UpdateGroupStatus');
751 rlm_core_sv.dlog(k_DEBUG,'UpdateGroupStatus to ', x_status);
752 rlm_core_sv.dlog(k_DEBUG,'x_header_id ', x_header_id);
753 rlm_core_sv.dlog(k_DEBUG,'x_Group_rec.ship_from_org_id ',
754 x_Group_rec.ship_from_org_id);
755 rlm_core_sv.dlog(k_DEBUG,'x_Group_rec.ship_to_site_use_id ',
756 x_Group_rec.ship_to_site_use_id);
757 rlm_core_sv.dlog(k_DEBUG,'x_Group_rec.bill_to_site_use_id ',
758 x_Group_rec.bill_to_site_use_id);
759 rlm_core_sv.dlog(k_DEBUG,'x_Group_rec.customer_item_id ',
760 x_Group_rec.customer_item_id);
761 rlm_core_sv.dlog(k_DEBUG,'x_ScheduleHeaderId ', x_ScheduleHeaderId);
762 rlm_core_sv.dlog(k_DEBUG,'x_UpdateLevel to ', x_UpdateLevel);
763 END IF;
764 --
765 IF x_UpdateLevel <> 'GROUP' THEN
766 --
767 UPDATE rlm_interface_lines
768 SET process_status = x_Status
769 WHERE header_id = x_header_id
770 AND process_status = rlm_core_sv.k_PS_AVAILABLE
771 AND Item_detail_type = k_MRP_FORECAST;
772 --
773 UPDATE rlm_schedule_lines
774 SET process_status = x_Status
775 WHERE header_id = x_ScheduleHeaderid
776 AND process_status in (rlm_core_sv.k_PS_AVAILABLE,
777 rlm_core_sv.k_PS_ERROR)
778 AND item_detail_type = k_MRP_FORECAST;
779 --
780 ELSE
781 --
782 UPDATE rlm_schedule_lines_all
783 SET process_status = x_Status
784 WHERE header_id = x_ScheduleheaderId
785 AND ship_from_org_id = x_Group_rec.ship_from_org_id
786 AND nvl(ship_to_site_use_id,k_NNULL)
787 = nvl(x_Group_rec.ship_to_site_use_id,k_NNULL)
788 AND nvl(bill_to_site_use_id,k_NNULL)
789 = nvl(x_Group_rec.bill_to_site_use_id,k_NNULL)
790 AND inventory_item_id = x_Group_rec.inventory_item_id
791 AND process_status IN (rlm_core_sv.k_PS_AVAILABLE,
792 rlm_core_sv.k_PS_ERROR)
793 AND line_id IN ( select schedule_line_id
794 from rlm_interface_lines
795 WHERE header_id = x_header_id
796 AND ship_from_org_id = x_Group_rec.ship_from_org_id
797 AND nvl(ship_to_site_use_id,k_NNULL)
798 = nvl(x_Group_rec.ship_to_site_use_id,k_NNULL)
799 AND nvl(bill_to_site_use_id,k_NNULL)
800 = nvl(x_Group_rec.bill_to_site_use_id,k_NNULL)
801 AND inventory_item_id = x_Group_rec.inventory_item_id
802 AND process_status IN (rlm_core_sv.k_PS_AVAILABLE,
803 rlm_core_sv.k_PS_ERROR)
804 AND item_detail_type = k_MRP_FORECAST);
805 --
806 IF (l_debug <> -1) THEN
807 rlm_core_sv.dlog(k_DEBUG,'No of Schedule Lines Updated ', SQL%ROWCOUNT);
808 END IF;
809 --
810 UPDATE rlm_interface_lines
811 SET process_status = x_Status
812 WHERE header_id = x_header_id
813 AND ship_from_org_id = x_Group_rec.ship_from_org_id
814 AND nvl(ship_to_site_use_id,k_NNULL)
815 = nvl(x_Group_rec.ship_to_site_use_id,k_NNULL)
816 AND nvl(bill_to_site_use_id,k_NNULL)
817 = nvl(x_Group_rec.bill_to_site_use_id,k_NNULL)
818 AND inventory_item_id = x_Group_rec.inventory_item_id
819 AND process_status IN (rlm_core_sv.k_PS_AVAILABLE,
820 rlm_core_sv.k_PS_ERROR)
821 AND item_detail_type = k_MRP_FORECAST;
822 --
823 IF (l_debug <> -1) THEN
824 rlm_core_sv.dlog(k_DEBUG,'No of interface Lines Updated ', SQL%ROWCOUNT);
825 END IF;
826 --
827 END IF;
828 --
829 IF (l_debug <> -1) THEN
830 rlm_core_sv.dpop(k_SDEBUG);
831 END IF;
832 --
833 EXCEPTION
834 --
835 WHEN OTHERS THEN
836 --
837 rlm_message_sv.sql_error('rlm_forecast_sv.UpdateGroupStatus',x_progress);
838 --
839 IF (l_debug <> -1) THEN
840 rlm_core_sv.dlog(k_DEBUG,'progress',x_Progress);
841 rlm_core_sv.dpop(k_SDEBUG,'EXCEPTION: OTHER - sql error');
842 END IF;
843 --
844 RAISE ;
845 --
846 END UpdateGroupStatus;
847
848 /*===========================================================================
849
850 PROCEDURE NAME: initialize_table
851
852 ===========================================================================*/
853
854 PROCEDURE initialize_table(
855 t_forecast IN OUT NOCOPY mrp_forecast_interface_pk.t_forecast_interface)
856 IS
857 --
858 x_progress number :='010';
859 --
860 BEGIN
861 --
862 IF (l_debug <> -1) THEN
863 rlm_core_sv.dpush(k_SDEBUG,'initialize_table');
864 END IF;
865 --
866 t_forecast.DELETE;
867 --
868 IF (l_debug <> -1) THEN
869 rlm_core_sv.dpop(k_SDEBUG);
870 END IF;
871 --
872 EXCEPTION
873 WHEN OTHERS THEN
874 rlm_message_sv.sql_error ('RLM_forecast_sv.initialize_table', x_progress);
875 --
876 IF (l_debug <> -1) THEN
877 rlm_core_sv.dpop(k_SDEBUG,'EXCEPTION: OTHERS - sql error');
878 END IF;
879 --
880 raise;
881
882 END initialize_table;
883
884 /*===========================================================================
885
886 PROCEDURE NAME: LoadForecast
887
888 ===========================================================================*/
889
890 PROCEDURE LoadForecast(
891 x_Sched_rec IN RLM_INTERFACE_HEADERS%ROWTYPE,
892 x_Group_rec IN rlm_dp_sv.t_Group_rec,
893 t_forecast IN OUT NOCOPY mrp_forecast_interface_pk.t_forecast_interface,
894 x_forecast_designator IN
895 mrp_forecast_designators.forecast_designator%TYPE)
896 IS
897
898
899 --
900 CURSOR C IS
901 SELECT ril.inventory_item_id inventory_item_id,
902 ril.ship_from_org_id organization_id,
903 ril.request_date forecast_date,
904 ril.primary_quantity quantity,
905 ril.uom_code uom_code, -- Bug 4176961
906 ril.primary_uom_code primary_uom_code, -- Bug 4176961
907 ril.item_detail_subtype bucket_type,
908 ril.line_id demand_stream_id,
909 ril.industry_attribute1 attribute01,
910 ril.industry_attribute2 attribute02,
911 ril.industry_attribute3 attribute03,
912 ril.industry_attribute4 attribute04,
913 ril.industry_attribute5 attribute05,
914 ril.industry_attribute6 attribute06,
915 ril.industry_attribute7 attribute07,
916 ril.industry_attribute8 attribute08,
917 ril.industry_attribute9 attribute09,
918 ril.industry_attribute10 attribute10,
919 ril.industry_attribute11 attribute11,
920 ril.industry_attribute12 attribute12,
921 ril.industry_attribute13 attribute13,
922 ril.industry_attribute14 attribute14,
923 ril.industry_attribute15 attribute15
924 FROM rlm_interface_lines ril
925 WHERE ril.ship_from_org_id = x_Group_rec.ship_from_org_id
926 AND ril.header_id = x_Sched_rec.header_id
927 AND nvl(ril.ship_to_site_use_id ,k_NNULL)
928 = nvl(x_Group_rec.ship_to_site_use_id,k_NNULL)
929 AND ril.bill_to_site_use_id IS NULL
930 AND ril.inventory_item_id = x_Group_rec.inventory_item_id
931 AND ril.customer_item_id = x_Group_rec.customer_item_id
932 AND ril.item_detail_type = k_MRP_FORECAST
933 AND ril.process_status = rlm_core_sv.k_PS_AVAILABLE
934 AND ril.request_date is NOT NULL --bug 2882311
935 AND ril.primary_quantity <> 0
936 --bug 1786492
937
938 UNION
939
940 SELECT ril.inventory_item_id inventory_item_id,
941 ril.ship_from_org_id organization_id,
942 ril.request_date forecast_date,
943 ril.primary_quantity quantity,
944 ril.uom_code uom_code, -- Bug 4176961
945 ril.primary_uom_code primary_uom_code, -- Bug 4176961
946 ril.item_detail_subtype bucket_type,
947 ril.line_id demand_stream_id,
948 ril.industry_attribute1 attribute01,
949 ril.industry_attribute2 attribute02,
950 ril.industry_attribute3 attribute03,
951 ril.industry_attribute4 attribute04,
952 ril.industry_attribute5 attribute05,
953 ril.industry_attribute6 attribute06,
954 ril.industry_attribute7 attribute07,
955 ril.industry_attribute8 attribute08,
956 ril.industry_attribute9 attribute09,
957 ril.industry_attribute10 attribute10,
958 ril.industry_attribute11 attribute11,
959 ril.industry_attribute12 attribute12,
960 ril.industry_attribute13 attribute13,
961 ril.industry_attribute14 attribute14,
962 ril.industry_attribute15 attribute15
963 FROM rlm_interface_lines ril
964 WHERE ril.ship_from_org_id = x_Group_rec.ship_from_org_id
965 AND ril.header_id = x_Sched_rec.header_id
966 AND nvl(ril.ship_to_site_use_id ,k_NNULL)
967 = nvl(x_Group_rec.ship_to_site_use_id,k_NNULL)
968 AND ril.bill_to_site_use_id IS NULL
969 AND ril.inventory_item_id = x_Group_rec.inventory_item_id
970 AND ril.item_detail_type = k_MRP_FORECAST
971 AND ril.process_status = rlm_core_sv.k_PS_PROCESSED
972 AND ril.primary_quantity <> 0
973 AND x_Sched_rec.schedule_purpose <> k_ADD; --Bugfix 9688324
974
975
976 CURSOR C_bill IS
977 SELECT ril.inventory_item_id inventory_item_id,
978 ril.ship_from_org_id organization_id,
979 ril.request_date forecast_date,
980 ril.primary_quantity quantity,
981 ril.uom_code uom_code, -- Bug 4176961
982 ril.primary_uom_code primary_uom_code, -- Bug 4176961
983 ril.item_detail_subtype bucket_type,
984 ril.line_id demand_stream_id,
985 ril.industry_attribute1 attribute01,
986 ril.industry_attribute2 attribute02,
987 ril.industry_attribute3 attribute03,
988 ril.industry_attribute4 attribute04,
989 ril.industry_attribute5 attribute05,
990 ril.industry_attribute6 attribute06,
991 ril.industry_attribute7 attribute07,
992 ril.industry_attribute8 attribute08,
993 ril.industry_attribute9 attribute09,
994 ril.industry_attribute10 attribute10,
995 ril.industry_attribute11 attribute11,
996 ril.industry_attribute12 attribute12,
997 ril.industry_attribute13 attribute13,
998 ril.industry_attribute14 attribute14,
999 ril.industry_attribute15 attribute15
1000 FROM rlm_interface_lines ril
1001 WHERE ril.ship_from_org_id = x_Group_rec.ship_from_org_id
1002 AND ril.header_id = x_Sched_rec.header_id
1003 AND nvl(ril.ship_to_site_use_id ,k_NNULL)
1004 = nvl(x_Group_rec.ship_to_site_use_id,k_NNULL)
1005 AND bill_to_site_use_id = x_Group_rec.bill_to_site_use_id
1006 AND ril.inventory_item_id = x_Group_rec.inventory_item_id
1007 AND ril.customer_item_id = x_Group_rec.customer_item_id
1008 AND ril.item_detail_type = k_MRP_FORECAST
1009 AND ril.process_status = rlm_core_sv.k_PS_AVAILABLE
1010 AND ril.request_date is NOT NULL --bug 2882311
1011 AND ril.primary_quantity <> 0
1012 --bug 1786492
1013
1014 UNION
1015
1016 SELECT ril.inventory_item_id inventory_item_id,
1017 ril.ship_from_org_id organization_id,
1018 ril.request_date forecast_date,
1019 ril.primary_quantity quantity,
1020 ril.uom_code uom_code, -- Bug 4176961
1021 ril.primary_uom_code primary_uom_code, -- Bug 4176961
1022 ril.item_detail_subtype bucket_type,
1023 ril.line_id demand_stream_id,
1024 ril.industry_attribute1 attribute01,
1025 ril.industry_attribute2 attribute02,
1026 ril.industry_attribute3 attribute03,
1027 ril.industry_attribute4 attribute04,
1028 ril.industry_attribute5 attribute05,
1029 ril.industry_attribute6 attribute06,
1030 ril.industry_attribute7 attribute07,
1031 ril.industry_attribute8 attribute08,
1032 ril.industry_attribute9 attribute09,
1033 ril.industry_attribute10 attribute10,
1034 ril.industry_attribute11 attribute11,
1035 ril.industry_attribute12 attribute12,
1036 ril.industry_attribute13 attribute13,
1037 ril.industry_attribute14 attribute14,
1038 ril.industry_attribute15 attribute15
1039 FROM rlm_interface_lines ril
1040 WHERE ril.ship_from_org_id = x_Group_rec.ship_from_org_id
1041 AND ril.header_id = x_Sched_rec.header_id
1042 AND nvl(ril.ship_to_site_use_id ,k_NNULL)
1043 = nvl(x_Group_rec.ship_to_site_use_id,k_NNULL)
1044 AND bill_to_site_use_id = x_Group_rec.bill_to_site_use_id
1045 AND ril.inventory_item_id = x_Group_rec.inventory_item_id
1046 AND ril.item_detail_type = k_MRP_FORECAST
1047 AND ril.process_status = rlm_core_sv.k_PS_PROCESSED
1048 AND ril.primary_quantity <> 0
1049 AND x_Sched_rec.schedule_purpose <> k_ADD; --Bugfix 9688324
1050
1051 --
1052 Recinfo C%ROWTYPE;
1053 --
1054 index_cnt number ;
1055 --
1056 x_progress number :='010';
1057 --
1058 BEGIN
1059 --
1060 IF (l_debug <> -1) THEN
1061 rlm_core_sv.dpush(k_SDEBUG,'LoadForecast');
1062 rlm_core_sv.dlog(k_DEBUG,'x_Group_rec.ship_from_org_id',
1063 x_Group_rec.ship_from_org_id);
1064 rlm_core_sv.dlog(k_DEBUG,'x_Group_rec.bill_to_site_use_id',
1065 x_Group_rec.bill_to_site_use_id);
1066 rlm_core_sv.dlog(k_DEBUG,'x_Group_rec.ship_to_site_use_id',
1067 x_Group_rec.ship_to_site_use_id);
1068 rlm_core_sv.dlog(k_DEBUG,'x_Group_rec.customer_id',
1069 x_Group_rec.customer_id);
1070 rlm_core_sv.dlog(k_DEBUG,'x_Group_rec.inventory_item_id',
1071 x_Group_rec.inventory_item_id);
1072 rlm_core_sv.dlog(k_DEBUG,'x_forecast_designator',
1073 x_forecast_designator);
1074 rlm_core_sv.dlog(k_DEBUG,'k_MRP_FORECAST',
1075 k_MRP_FORECAST);
1076 rlm_core_sv.dlog(k_DEBUG,'rlm_core_sv.k_PS_AVAILABLE',
1077 rlm_core_sv.k_PS_AVAILABLE);
1078 END IF;
1079 --
1080 /*==============================================================================
1081 =======
1082 Loading Records in PL/SQL Table
1083 ================================================================================
1084 =====*/
1085
1086 IF(x_Group_rec.bill_to_site_use_id IS NULL) THEN
1087 --
1088 FOR Recinfo in C
1089 LOOP
1090 /* Bug 4176961 : Start */
1091 IF (RLM_VALIDATEDEMAND_SV.g_convert_uom) THEN
1092 --
1093 IF (nvl(Recinfo.uom_code,'-99') <> nvl(Recinfo.primary_uom_code,'99')) THEN
1094 --
1095 IF (l_debug <> -1) THEN
1096 rlm_core_sv.dlog(k_DEBUG,'Recinfo.uom_code',Recinfo.uom_code);
1097 rlm_core_sv.dlog(k_DEBUG,'Recinfo.primary_uom_code',Recinfo.primary_uom_code);
1098 rlm_core_sv.dlog(k_DEBUG,'Before conversion: Recinfo.quantity',Recinfo.quantity);
1099 END IF;
1100 --
1101 Convert_UOM (Recinfo.uom_code,
1102 Recinfo.primary_uom_code,
1103 Recinfo.quantity,
1104 Recinfo.inventory_item_id,
1105 Recinfo.organization_id);
1106 --
1107 IF (l_debug <> -1) THEN
1108 rlm_core_sv.dlog(k_DEBUG,'After conversion: Recinfo.quantity',Recinfo.quantity);
1109 END IF;
1110 --
1111 END IF;
1112 --
1113 END IF;
1114 /* Bug 4176961 : End */
1115 --
1116 index_cnt := t_forecast.COUNT + 1;
1117 t_forecast(index_cnt).inventory_item_id := Recinfo.inventory_item_id;
1118 t_forecast(index_cnt).forecast_designator :=x_forecast_designator;
1119 t_forecast(index_cnt).organization_id :=Recinfo.organization_id;
1120 t_forecast(index_cnt).forecast_date :=Recinfo.forecast_date;
1121 t_forecast(index_cnt).last_update_date :=sysdate;
1122 t_forecast(index_cnt).creation_date :=sysdate;
1123 t_forecast(index_cnt).created_by := fnd_global.user_id;
1124 t_forecast(index_cnt).last_update_login := fnd_global.login_id;
1125 t_forecast(index_cnt).quantity :=Recinfo.quantity;
1126 t_forecast(index_cnt).process_status :=2;
1127 t_forecast(index_cnt).confidence_percentage :=100;
1128 t_forecast(index_cnt).comments :=null;
1129 t_forecast(index_cnt).error_message :=null;
1130 t_forecast(index_cnt).request_id :=null;
1131 t_forecast(index_cnt).program_application_id :=null;
1132 t_forecast(index_cnt).program_id :=null;
1133 t_forecast(index_cnt).program_update_date :=null;
1134 t_forecast(index_cnt).workday_control :=3;
1135 t_forecast(index_cnt).bucket_type :=Recinfo.bucket_type;
1136 t_forecast(index_cnt).forecast_end_date :=null;
1137 t_forecast(index_cnt).transaction_id :=null;
1138 t_forecast(index_cnt).source_code :='RLM';
1139 t_forecast(index_cnt).source_line_id :=Recinfo.demand_stream_id;
1140 t_forecast(index_cnt).attribute1 :=Recinfo.attribute01;
1141 t_forecast(index_cnt).attribute2 :=Recinfo.attribute02;
1142 t_forecast(index_cnt).attribute3 :=Recinfo.attribute03;
1143 t_forecast(index_cnt).attribute4 :=Recinfo.attribute04;
1144 t_forecast(index_cnt).attribute5 :=Recinfo.attribute05;
1145 t_forecast(index_cnt).attribute6 :=Recinfo.attribute06;
1146 t_forecast(index_cnt).attribute7 :=Recinfo.attribute07;
1147 t_forecast(index_cnt).attribute8 :=Recinfo.attribute08;
1148 t_forecast(index_cnt).attribute9 :=Recinfo.attribute09;
1149 t_forecast(index_cnt).attribute10 :=Recinfo.attribute10;
1150 t_forecast(index_cnt).attribute11 :=Recinfo.attribute11;
1151 t_forecast(index_cnt).attribute12 :=Recinfo.attribute12;
1152 t_forecast(index_cnt).attribute13 :=Recinfo.attribute13;
1153 t_forecast(index_cnt).attribute14 :=Recinfo.attribute14;
1154 t_forecast(index_cnt).attribute15 :=Recinfo.attribute15;
1155 t_forecast(index_cnt).project_id :=NULL;
1156 t_forecast(index_cnt).task_id :=NULL;
1157 t_forecast(index_cnt).line_id :=NULL;
1158 --
1159 IF (l_debug <> -1) THEN
1160 rlm_core_sv.dlog(k_DEBUG,'t_forecast(' || index_cnt || ').source_line_id',
1161 t_forecast(index_cnt).source_line_id);
1162 rlm_core_sv.dlog(k_DEBUG,'t_forecast(' || index_cnt || ').inventory_item_id',
1163 t_forecast(index_cnt).inventory_item_id);
1164 rlm_core_sv.dlog(k_DEBUG,'t_forecast(' || index_cnt || ').forecast_designator',
1165 t_forecast(index_cnt).forecast_designator);
1166 rlm_core_sv.dlog(k_DEBUG,'t_forecast(' || index_cnt || ').organization_id',
1167 t_forecast(index_cnt).organization_id);
1168 rlm_core_sv.dlog(k_DEBUG,'t_forecast(' || index_cnt || ').forecast_date',
1172 END IF;
1169 t_forecast(index_cnt).forecast_date);
1170 rlm_core_sv.dlog(k_DEBUG,'t_forecast(' || index_cnt || ').quantity',
1171 t_forecast(index_cnt).quantity);
1173 --
1174 END LOOP;
1175
1176 ELSE
1177 --
1178 FOR Recinfo in C_bill
1179 LOOP
1180 /* Bug 4176961 : Start */
1181 IF (RLM_VALIDATEDEMAND_SV.g_convert_uom) THEN
1182 --
1183 IF (nvl(Recinfo.uom_code,'-99') <> nvl(Recinfo.primary_uom_code,'99')) THEN
1184 --
1185 IF (l_debug <> -1) THEN
1186 rlm_core_sv.dlog(k_DEBUG,'Recinfo.uom_code',Recinfo.uom_code);
1187 rlm_core_sv.dlog(k_DEBUG,'Recinfo.primary_uom_code',Recinfo.primary_uom_code);
1188 rlm_core_sv.dlog(k_DEBUG,'Before conversion: Recinfo.quantity',Recinfo.quantity);
1189 END IF;
1190 --
1191 Convert_UOM (Recinfo.uom_code,
1192 Recinfo.primary_uom_code,
1193 Recinfo.quantity,
1194 Recinfo.inventory_item_id,
1195 Recinfo.organization_id);
1196 --
1197 IF (l_debug <> -1) THEN
1198 rlm_core_sv.dlog(k_DEBUG,'After conversion: Recinfo.quantity',Recinfo.quantity);
1199 END IF;
1200 --
1201 END IF;
1202 --
1203 END IF;
1204 /* Bug 4176961 : End */
1205 --
1206 index_cnt := t_forecast.COUNT + 1;
1207 t_forecast(index_cnt).inventory_item_id := Recinfo.inventory_item_id;
1208 t_forecast(index_cnt).forecast_designator :=x_forecast_designator;
1209 t_forecast(index_cnt).organization_id :=Recinfo.organization_id;
1210 t_forecast(index_cnt).forecast_date :=Recinfo.forecast_date;
1211 t_forecast(index_cnt).last_update_date :=sysdate;
1212 t_forecast(index_cnt).creation_date :=sysdate;
1213 t_forecast(index_cnt).created_by := fnd_global.user_id;
1214 t_forecast(index_cnt).last_update_login := fnd_global.login_id;
1215 t_forecast(index_cnt).quantity :=Recinfo.quantity;
1216 t_forecast(index_cnt).process_status :=2;
1217 t_forecast(index_cnt).confidence_percentage :=100;
1218 t_forecast(index_cnt).comments :=null;
1219 t_forecast(index_cnt).error_message :=null;
1220 t_forecast(index_cnt).request_id :=null;
1221 t_forecast(index_cnt).program_application_id :=null;
1222 t_forecast(index_cnt).program_id :=null;
1223 t_forecast(index_cnt).program_update_date :=null;
1224 t_forecast(index_cnt).workday_control :=3;
1225 t_forecast(index_cnt).bucket_type :=Recinfo.bucket_type;
1226 t_forecast(index_cnt).forecast_end_date :=null;
1227 t_forecast(index_cnt).transaction_id :=null;
1228 t_forecast(index_cnt).source_code :='RLM';
1229 t_forecast(index_cnt).source_line_id :=Recinfo.demand_stream_id;
1230 t_forecast(index_cnt).attribute1 :=Recinfo.attribute01;
1231 t_forecast(index_cnt).attribute2 :=Recinfo.attribute02;
1232 t_forecast(index_cnt).attribute3 :=Recinfo.attribute03;
1233 t_forecast(index_cnt).attribute4 :=Recinfo.attribute04;
1234 t_forecast(index_cnt).attribute5 :=Recinfo.attribute05;
1235 t_forecast(index_cnt).attribute6 :=Recinfo.attribute06;
1236 t_forecast(index_cnt).attribute7 :=Recinfo.attribute07;
1237 t_forecast(index_cnt).attribute8 :=Recinfo.attribute08;
1238 t_forecast(index_cnt).attribute9 :=Recinfo.attribute09;
1239 t_forecast(index_cnt).attribute10 :=Recinfo.attribute10;
1240 t_forecast(index_cnt).attribute11 :=Recinfo.attribute11;
1241 t_forecast(index_cnt).attribute12 :=Recinfo.attribute12;
1242 t_forecast(index_cnt).attribute13 :=Recinfo.attribute13;
1243 t_forecast(index_cnt).attribute14 :=Recinfo.attribute14;
1244 t_forecast(index_cnt).attribute15 :=Recinfo.attribute15;
1245 t_forecast(index_cnt).project_id :=NULL;
1246 t_forecast(index_cnt).task_id :=NULL;
1247 t_forecast(index_cnt).line_id :=NULL;
1248 --
1249 IF (l_debug <> -1) THEN
1250 rlm_core_sv.dlog(k_DEBUG,'t_forecast(' || index_cnt || ').source_line_id',
1251 t_forecast(index_cnt).source_line_id);
1255 t_forecast(index_cnt).forecast_designator);
1252 rlm_core_sv.dlog(k_DEBUG,'t_forecast(' || index_cnt || ').inventory_item_id',
1253 t_forecast(index_cnt).inventory_item_id);
1254 rlm_core_sv.dlog(k_DEBUG,'t_forecast(' || index_cnt || ').forecast_designator',
1256 rlm_core_sv.dlog(k_DEBUG,'t_forecast(' || index_cnt || ').organization_id',
1257 t_forecast(index_cnt).organization_id);
1258 rlm_core_sv.dlog(k_DEBUG,'t_forecast(' || index_cnt || ').forecast_date',
1259 t_forecast(index_cnt).forecast_date);
1260 rlm_core_sv.dlog(k_DEBUG,'t_forecast(' || index_cnt || ').quantity',
1261 t_forecast(index_cnt).quantity);
1262 END IF;
1263 --
1264 END LOOP;
1265 --
1266 END IF;
1267 --
1268 IF (l_debug <> -1) THEN
1269 rlm_core_sv.dlog(k_DEBUG,'COUNT',index_cnt);
1270 rlm_core_sv.dpop(k_SDEBUG);
1271 END IF;
1272 --
1273 EXCEPTION
1274 /* Bug 4176961 : UOM conversion */
1275 WHEN e_Group_Error THEN
1276 --
1277 IF (l_debug <> -1) THEN
1278 rlm_core_sv.dlog(k_DEBUG,'progress',x_Progress);
1279 rlm_core_sv.dpop(k_SDEBUG, 'GROUP ERROR');
1280 END IF;
1281 --
1282 raise;
1283
1284 WHEN OTHERS THEN
1285 rlm_message_sv.sql_error ('RLM_forecast_sv.LoadForecast', x_progress);
1286 --
1287 IF (l_debug <> -1) THEN
1288 rlm_core_sv.dpop(k_SDEBUG,'EXCEPTION: OTHERS - sql error');
1289 END IF;
1290 --
1291 raise;
1292
1293 END LoadForecast;
1294
1295 /*===========================================================================
1296
1297 PROCEDURE NAME: process_table
1298
1299 ===========================================================================*/
1300
1301 PROCEDURE ProcessTable(
1302 x_Sched_rec IN RLM_INTERFACE_HEADERS%ROWTYPE,
1303 x_Group_rec IN rlm_dp_sv.t_Group_rec,
1304 t_Forecast IN mrp_forecast_interface_pk.t_forecast_interface)
1305 IS
1306
1307 v_Result NUMBER;
1308 v_ProcessStatus NUMBER := 5;
1309 x_Progress VARCHAR2(3) := '010';
1310 --
1311 v_line_error BOOLEAN;
1312 --
1313 BEGIN
1314 --
1315 IF (l_debug <> -1) THEN
1316 rlm_core_sv.dpush(k_SDEBUG,'process_table');
1317 END IF;
1318 --
1319 v_line_error := FALSE;
1320 --
1321 FOR v_Count IN 1..t_Forecast.COUNT LOOP
1322 --
1323 x_Progress := '020';
1324 --
1325 IF (l_debug <> -1) THEN
1326 rlm_core_sv.dlog(k_DEBUG,'process status of forecast lines',
1327 t_Forecast(v_Count).process_status);
1328 END IF;
1329 --
1330 IF t_Forecast(v_Count).process_status = 4 THEN
1331 --
1332 x_Progress := '030';
1333 v_line_error := TRUE;
1334 --
1335 IF (l_debug <> -1) THEN
1336 rlm_core_sv.dlog(k_DEBUG,'RLM_FORECAST_FAILED',
1337 t_Forecast(v_Count).error_message);
1338 END IF;
1339 --
1340 rlm_message_sv.app_error(
1341 x_ExceptionLevel => rlm_message_sv.k_error_level,
1342 x_MessageName => 'RLM_FORECAST_FAILED',
1343 x_InterfaceHeaderId => x_sched_rec.header_id,
1344 x_InterfaceLineId => t_forecast(v_Count).source_line_id,
1345 x_ScheduleHeaderId => x_sched_rec.schedule_header_id,
1346 x_ScheduleLineId => NULL,
1347 x_Token1 => 'MESSAGE_TEXT',
1348 x_value1 => t_Forecast(v_Count).error_message,
1349 x_Token2 => 'QUANTITY',
1350 x_value2 => t_Forecast(v_Count).quantity,
1351 x_Token3 => 'GROUP',
1352 x_value3 => rlm_core_sv.get_ship_from(x_Group_rec.ship_from_org_id)||'-'||
1353 rlm_core_sv.get_ship_to(x_Group_rec.ship_to_address_id)||'-'||
1354 rlm_core_sv.get_item_number(x_Group_rec.customer_item_id),
1355 x_Token4 => 'REQ_DATE',
1356 x_value4 => t_Forecast(v_Count).forecast_date,
1357 x_Token5 => 'START_DATE_TIME',
1358 x_value5 => to_date(t_Forecast(v_Count).attribute2,'YYYY/MM/DD HH24:MI:SS'),
1359 x_Token6 => 'FORECAST_DESIGNATOR',
1360 x_value6 => t_Forecast(v_Count).forecast_designator);
1361 --
1362 x_Progress := '040';
1363 --
1364 END IF;
1365 --
1366 END LOOP;
1367 --
1368 x_Progress := '050';
1369 --
1370 -- Bug 4716501 : Even if one MRP line is in error status, DSP should fail the
1371 -- entire group.
1372 --
1373 IF v_line_error THEN
1374 --{
1375 IF (l_debug <> -1) THEN
1376 rlm_core_sv.dlog(k_DEBUG, 'At least one MRP line is in error, so fail entire group');
1377 END IF;
1378 --
1379 RAISE e_Group_error;
1380 --}
1381 END IF;
1382 --
1383 IF (l_debug <> -1) THEN
1384 rlm_core_sv.dpop(k_SDEBUG);
1385 END IF;
1386 --
1387 EXCEPTION
1388 --
1389 WHEN e_Group_error THEN
1390 --
1391 IF (l_debug <> -1) THEN
1392 rlm_core_sv.dlog(k_DEBUG, 'x_Progress', x_Progress);
1393 rlm_core_sv.dpop(k_SDEBUG, 'e_Group_error');
1394 END IF;
1395 --
1396 RAISE;
1397 --
1398 WHEN OTHERS THEN
1399 rlm_message_sv.sql_error ('RLM_forecast_sv.process_table', x_progress);
1400 --
1401 IF (l_debug <> -1) THEN
1402 rlm_core_sv.dpop(k_SDEBUG,'EXCEPTION: OTHERS - sql error');
1403 END IF;
1404 --
1405 raise;
1406
1407 END ProcessTable;
1408
1409 /*=============================================================================
1410 PROCEDURE NAME: get_designator
1411 PURPOSE: Fetches the matching forecast designator
1412 ==============================================================================*/
1413 PROCEDURE GetDesignator( x_Sched_rec IN RLM_INTERFACE_HEADERS%ROWTYPE DEFAULT NULL,
1414 x_Group_rec IN rlm_dp_sv.t_Group_rec DEFAULT NULL,
1415 x_Customer_id IN NUMBER,
1416 x_ShipFromOrgId IN NUMBER,
1417 x_Ship_Site_Id IN NUMBER,
1418 x_bill_site_id IN NUMBER,
1419 x_bill_address_Id IN NUMBER,
1420 x_ForecastDesignator IN OUT NOCOPY VARCHAR2,
1421 x_ship_to_customer_id IN NUMBER)
1422 IS
1423
1424 v_progress VARCHAR2(3) := '010';
1425 v_bill_site_id NUMBER;
1426 v_bill_address_id NUMBER;
1427 v_ListName VARCHAR2(30);
1428 v_ListNametmp VARCHAR2(30);
1429
1430 BEGIN
1431 --
1432 IF (l_debug <> -1) THEN
1433 rlm_core_sv.dpush(K_SDEBUG, 'GetDesignator');
1434 rlm_core_sv.dlog(k_DEBUG, 'customer_id',x_customer_id);
1435 rlm_core_sv.dlog(k_DEBUG, 'x_ship_to_customer_id',x_ship_to_customer_id);
1436 rlm_core_sv.dlog(k_DEBUG, 'ship_from_org_id',x_ShipFromOrgId);
1437 rlm_core_sv.dlog(k_DEBUG, 'x_Ship_Site_Id',x_Ship_Site_Id);
1438 rlm_core_sv.dlog(k_DEBUG, 'x_bill_site_id',x_bill_site_id);
1439 rlm_core_sv.dlog(k_DEBUG, 'x_bill_address_Id',x_bill_address_Id);
1440 END IF;
1441
1442 --Bugfix 8326871 Start
1443
1444 IF g_MRP_ListName IS NULL THEN
1445 fnd_profile.get('RLM_SELECTION_LIST',v_ListName);
1446 IF (l_debug <> -1) THEN
1447 rlm_core_sv.dlog(k_DEBUG,'v_ListName',v_ListName);
1448 END IF;
1449 g_MRP_ListName := v_ListName;
1450 ELSE
1451 IF (l_debug <> -1) THEN
1452 rlm_core_sv.dlog(k_DEBUG,'g_MRP_ListName',g_MRP_ListName);
1453 END IF;
1454 v_ListName := g_MRP_ListName;
1455 END IF;
1456
1457 --
1458 IF (l_debug <> -1) THEN
1459 rlm_core_sv.dlog(k_DEBUG,'Profile: RLM_SELECTION_LIST',v_ListName);
1460 END IF;
1461 --
1462 --Bugfix 8326871 End
1463 --
1464 v_listNameTmp := v_ListName;
1465 --
1466 BEGIN
1467 --
1468 SELECT SUBSTR(v_ListName,1,INSTR(v_ListName,',')-1)
1469 INTO v_ListName
1470 FROM DUAL;
1471 --
1472 EXCEPTION
1473 WHEN OTHERS THEN
1474 --
1475 IF (l_debug <> -1) THEN
1476 rlm_core_sv.dlog(k_DEBUG, 'error getting selection list',
1477 SUBSTR(SQLERRM,1,200));
1478 END IF;
1479 --
1480 END;
1481 --
1482 IF v_listName IS NULL THEN
1483 v_ListName := V_ListNameTmp;
1484 END IF;
1485 --
1486 IF (l_debug <> -1) THEN
1487 rlm_core_sv.dlog(k_DEBUG,'v_ListName',v_ListName);
1488 END IF;
1489 --
1490 IF (x_bill_site_id IS NOT NULL) THEN
1491 --
1492 -- Following query is changed as per TCA obsolescence project.
1493 SELECT CUST_SITE.CUST_ACCT_SITE_ID
1494 INTO v_bill_address_Id
1495 FROM HZ_CUST_ACCT_SITES ACCT_SITE ,
1496 HZ_CUST_SITE_USES_ALL CUST_SITE
1497 WHERE CUST_SITE.site_use_id = x_bill_site_id
1498 AND CUST_SITE.site_use_code = 'BILL_TO'
1499 AND CUST_SITE.status = 'A'
1500 AND CUST_SITE.CUST_ACCT_SITE_ID = ACCT_SITE.CUST_ACCT_SITE_ID
1501 AND ACCT_SITE.status = 'A'
1502 AND CUST_SITE.org_id = ACCT_SITE.org_id;
1503 --
1504 v_bill_Site_Id := x_bill_site_id;
1505 --
1506 IF (l_debug <> -1) THEN
1507 rlm_core_sv.dlog(k_DEBUG, 'v_bill_address_Id',v_bill_address_Id);
1508 END IF;
1509 --
1510 ELSIF (x_bill_address_id IS NOT NULL) THEN
1511 --
1512 -- Following query is changed as per TCA obsolescence project.
1513 SELECT CUST_SITE.SITE_USE_ID
1514 INTO v_bill_site_id
1515 FROM HZ_CUST_ACCT_SITES ACCT_SITE ,
1516 HZ_CUST_SITE_USES_ALL CUST_SITE
1517 WHERE CUST_SITE.CUST_ACCT_SITE_ID = x_bill_address_id
1518 AND CUST_SITE.site_use_code = 'BILL_TO'
1519 AND CUST_SITE.status = 'A'
1520 AND CUST_SITE.CUST_ACCT_SITE_ID = ACCT_SITE.CUST_ACCT_SITE_ID
1521 AND ACCT_SITE.status = 'A'
1522 AND CUST_SITE.org_id = ACCT_SITE.org_id;
1523 --
1524 v_bill_address_Id := x_bill_address_id;
1525 --
1526 IF (l_debug <> -1) THEN
1527 rlm_core_sv.dlog(k_DEBUG, 'v_bill_Site_Id',v_bill_Site_Id);
1528 END IF;
1529 --
1530 ELSE
1531 --
1532 v_bill_address_Id := null;
1533 v_bill_Site_Id := null;
1534 --
1535 IF (l_debug <> -1) THEN
1536 rlm_core_sv.dlog(k_DEBUG, 'v_bill_address_Id',v_bill_address_Id);
1537 rlm_core_sv.dlog(k_DEBUG, 'v_bill_Site_Id',v_bill_Site_Id);
1538 END IF;
1539 --
1540 END IF;
1541 --
1542 SELECT forecast_designator
1543 INTO x_ForecastDesignator
1544 FROM mrp_forecast_designators
1545 WHERE customer_id=NVL(x_ship_to_customer_id, x_customer_id)
1546 AND (bill_id = v_bill_site_id
1547 OR (bill_id IS NULL AND v_bill_site_id IS NULL))
1548 AND (ship_id = x_Ship_Site_Id
1549 OR (ship_id IS NULL AND x_Ship_Site_Id IS NULL))
1550 AND (organization_id = x_ShipFromOrgId
1551 OR (organization_id IS NULL AND x_ShipFromOrgId IS NULL))
1552 AND forecast_designator IN
1553 (SELECT source_forecast_designator
1554 FROM mrp_load_parameters
1555 WHERE source_organization_id = x_ShipFromOrgId
1556 AND selection_list_type = 2
1557 AND selection_list_name = v_ListName);
1558 --
1559 IF (l_debug <> -1) THEN
1560 rlm_core_sv.dlog(k_DEBUG, 'No of rows for forecast designator',SQL%ROWCOUNT);
1561 rlm_core_sv.dlog(k_DEBUG, 'x_ForecastDesignator',x_ForecastDesignator);
1562 rlm_core_sv.dpop(K_SDEBUG);
1563 END IF;
1564 --
1565 EXCEPTION
1566 --
1567 WHEN NO_DATA_FOUND THEN
1568 --
1569 BEGIN
1570 /* To make match only at ShipTo level,additional check for NULL bill_id */
1571 --
1572 SELECT forecast_designator
1573 INTO x_ForecastDesignator
1574 FROM mrp_forecast_designators
1575 WHERE customer_id= nvl(x_ship_to_customer_id, x_customer_id)
1576 AND ship_id = x_Ship_Site_Id
1577 AND bill_id IS NULL
1578 AND organization_id = x_ShipFromOrgId
1579 AND forecast_designator IN
1580 (SELECT source_forecast_designator
1581 FROM mrp_load_parameters
1582 WHERE source_organization_id = x_ShipFromOrgId
1583 AND selection_list_type = 2
1584 AND selection_list_name = v_ListName);
1585 --
1586 IF (l_debug <> -1) THEN
1587 rlm_core_sv.dlog(k_DEBUG, 'No of rows for forecast designator at CT/ST',SQL%ROWCOUNT);
1588 rlm_core_sv.dlog(k_DEBUG, 'x_ForecastDesignator',x_ForecastDesignator);
1589 rlm_core_sv.dpop(K_SDEBUG);
1590 END IF;
1591 --
1592 EXCEPTION
1593 --
1594 /*2328087*/
1595 WHEN NO_DATA_FOUND THEN
1596 --
1597 IF (l_debug <> -1) THEN
1598 rlm_core_sv.dlog(k_DEBUG, 'No data found for forecast designator at ShiptoCT/ST',SQL%ROWCOUNT);
1599 END IF;
1600 --
1601 BEGIN
1602 --
1603 SELECT forecast_designator
1604 INTO x_ForecastDesignator
1605 FROM mrp_forecast_designators
1606 WHERE customer_id= nvl(x_ship_to_customer_id, x_customer_id)
1607 AND ship_id IS NULL
1608 AND bill_id IS NULL
1609 AND organization_id = x_ShipFromOrgId
1610 AND forecast_designator IN
1611 (SELECT source_forecast_designator
1612 FROM mrp_load_parameters
1613 WHERE source_organization_id = x_ShipFromOrgId
1614 AND selection_list_type = 2
1615 AND selection_list_name = v_ListName);
1616 --
1617 IF (l_debug <> -1) THEN
1618 rlm_core_sv.dlog(k_DEBUG, 'No of rows for forecast designator at CT',SQL%ROWCOUNT);
1619 rlm_core_sv.dlog(k_DEBUG, 'x_ForecastDesignator',x_ForecastDesignator);
1620 rlm_core_sv.dpop(K_SDEBUG);
1621 END IF;
1622 --
1623 EXCEPTION
1624 --
1625 WHEN NO_DATA_FOUND THEN
1626 --
1627 IF (l_debug <> -1) THEN
1628 rlm_core_sv.dlog(k_DEBUG, 'No forecast designator for ship to
1629 customer',SQL%ROWCOUNT);
1630 END IF;
1631 --
1632 IF x_ship_to_customer_id is NULL OR
1633 x_ship_to_customer_id = x_customer_id THEN
1634 --
1635 x_ForecastDesignator := NULL;
1636 --
1637 IF (l_debug <> -1) THEN
1638 rlm_core_sv.dpop(K_SDEBUG);
1639 END IF;
1640 --
1641 ELSE
1642 --
1643 BEGIN
1644 --{
1645 SELECT forecast_designator
1646 INTO x_ForecastDesignator
1647 FROM mrp_forecast_designators
1648 WHERE customer_id= x_customer_id
1649 AND ship_id IS NULL
1650 AND bill_id IS NULL
1651 AND organization_id = x_ShipFromOrgId
1652 AND forecast_designator IN
1653 (SELECT source_forecast_designator
1654 FROM mrp_load_parameters
1655 WHERE source_organization_id = x_ShipFromOrgId
1656 AND selection_list_type = 2
1657 AND selection_list_name = v_ListName);
1658 --
1659 IF (l_debug <> -1) THEN
1660 rlm_core_sv.dlog(k_DEBUG, 'No of rows for forecast designator at CT',SQL%ROWCOUNT);
1661 rlm_core_sv.dlog(k_DEBUG, 'x_ForecastDesignator',x_ForecastDesignator);
1662 rlm_core_sv.dpop(K_SDEBUG);
1663 END IF;
1664 --
1665 EXCEPTION
1666 --
1667 WHEN NO_DATA_FOUND THEN
1668 --
1669 x_ForecastDesignator := NULL;
1670 --
1671 IF (l_debug <> -1) THEN
1672 rlm_core_sv.dpop(K_SDEBUG, 'No forecast Designator found for header level customer');
1673 END IF;
1674 --
1675 WHEN TOO_MANY_ROWS THEN
1676 --
1677 x_ForecastDesignator := NULL;
1678 --
1679 IF (l_debug <> -1) THEN
1680 rlm_core_sv.dpop(K_SDEBUG, 'Too many rows found for
1681 header level customer');
1682 END IF;
1683 --
1684 END;
1685 --}
1686 END IF;
1687 --
1688 WHEN TOO_MANY_ROWS THEN
1689 --
1690 x_ForecastDesignator := NULL;
1691 --
1692 IF (l_debug <> -1) THEN
1693 rlm_core_sv.dpop(K_SDEBUG, 'Too many rows');
1694 END IF;
1695 --
1696 END;
1697 --
1698 WHEN TOO_MANY_ROWS THEN
1699 --
1700 x_ForecastDesignator := NULL;
1701 --
1702 IF (l_debug <> -1) THEN
1703 rlm_core_sv.dpop(K_SDEBUG, 'Too many rows');
1704 END IF;
1705 --
1706 END;
1707 --
1708 WHEN TOO_MANY_ROWS THEN
1709 --
1710 x_ForecastDesignator := NULL;
1711 --
1712 IF (l_debug <> -1) THEN
1713 rlm_core_sv.dpop(K_SDEBUG, 'Too many rows');
1714 END IF;
1715 --
1716 WHEN OTHERS THEN
1717 --
1718 x_ForecastDesignator := NULL;
1719 --
1720 IF (l_debug <> -1) THEN
1721 rlm_core_sv.dpop(K_SDEBUG,'EXCEPTION: '||SUBSTR(SQLERRM,1,200));
1722 END IF;
1723 --
1724 raise;
1725 --
1726 END GetDesignator;
1727
1728
1729
1730 /*=============================================================================
1731 PROCEDURE NAME: emptyforecast
1732 PURPOSE: Deletes all the forecast for a designator
1733 ==============================================================================*/
1734 PROCEDURE EmptyForecast( x_sched_rec IN RLM_INTERFACE_HEADERS%ROWTYPE,
1735 x_Group_rec IN OUT NOCOPY rlm_dp_sv.t_Group_rec,
1736 x_forecast IN OUT NOCOPY
1737 mrp_forecast_interface_pk.t_forecast_interface,
1738 x_designator IN OUT NOCOPY
1739 mrp_forecast_interface_pk.t_forecast_designator,
1740 x_t_designator IN OUT NOCOPY
1741 mrp_forecast_interface_pk.t_forecast_designator)
1742 IS
1743 --
1744 x_progress VARCHAR2(3) := '010';
1745 v_InterfaceLineId NUMBER;
1746 --
1747 BEGIN
1748 --
1749 IF (l_debug <> -1) THEN
1750 rlm_core_sv.dpush(K_SDEBUG, 'EmptyForecast');
1751 END IF;
1752
1753 --empty all the forecast lines for the designator
1754 x_designator(1).inventory_item_id := NULL;
1755 x_designator(1).forecast_designator := x_t_designator(x_t_designator.COUNT).forecast_designator;
1756 x_designator(1).organization_id:=x_t_designator(x_t_designator.COUNT).organization_id;
1757 --
1758 IF (l_debug <> -1) THEN
1759 rlm_core_sv.dlog(k_DEBUG,'before mrp_forecast_interface_pk api for REPLACE');
1760 END IF;
1761 --
1762 IF mrp_forecast_interface_pk.mrp_forecast_interface(x_forecast,
1763 x_designator) THEN
1764 --
1765 g_designator_tab(g_designator_tab.count+1).designator :=
1766 x_t_designator(x_t_designator.COUNT).forecast_designator;
1767 g_designator_tab(g_designator_tab.count+1).organization_id :=
1768 x_t_designator(x_t_designator.COUNT).organization_id; --Bugfix 6817494
1769 IF (l_debug <> -1) THEN
1770 rlm_core_sv.dlog(k_DEBUG,'after mrp_forecast_interface_pk.mrp_forecast_interface api for REPLACE');
1771 rlm_core_sv.dlog(k_DEBUG,'Old forecast deleted for ', x_designator(1).forecast_designator);
1772 END IF;
1773
1774 ELSE
1775 --
1776 x_progress :='060';
1777 --
1778 IF (l_debug <> -1) THEN
1779 rlm_core_sv.dlog(k_DEBUG,'MRP Forecast API Failed ');
1780 END IF;
1781 --
1782 SELECT line_id
1783 INTO v_InterfaceLineId
1784 FROM rlm_interface_lines
1785 WHERE header_id = x_Sched_rec.header_id
1786 AND ship_from_org_id = x_Group_rec.ship_from_org_id
1787 AND ship_to_address_id = x_Group_rec.ship_to_address_id
1788 AND customer_item_id = x_Group_rec.customer_item_id
1789 AND rownum = 1;
1790 --
1791 rlm_message_sv.app_error(
1792 x_ExceptionLevel => rlm_message_sv.k_error_level,
1793 x_MessageName => 'RLM_FORECAST_API_FAILED',
1794 x_InterfaceHeaderId => x_sched_rec.header_id,
1795 x_InterfaceLineId => v_InterfaceLineId,
1796 x_ScheduleHeaderId => x_sched_rec.schedule_header_id,
1797 x_ScheduleLineId => NULL,
1798 x_Token1 => 'ORGANIZATION_ID',
1799 x_value1 => x_Group_rec.ship_from_org_id,
1800 x_Token2 => 'FORECAST_DESIGNATOR',
1801 x_value2 => x_designator(1).forecast_designator);
1802 --
1803 RAISE e_group_error;
1804 --
1805 END IF;
1806 --
1807 IF (l_debug <> -1) THEN
1808 rlm_core_sv.dpop(K_SDEBUG);
1809 END IF;
1810 --
1811 EXCEPTION
1812 --
1813 WHEN e_Group_Error THEN
1814 --
1815 IF (l_debug <> -1) THEN
1816 rlm_core_sv.dlog(k_DEBUG,'progress',x_Progress);
1817 rlm_core_sv.dpop(k_SDEBUG, 'GROUP ERROR');
1818 END IF;
1819 --
1820 raise;
1821 --
1822 WHEN OTHERS THEN
1823 --
1824 IF (l_debug <> -1) THEN
1825 rlm_core_sv.dpop(K_SDEBUG,'EXCEPTION: '||SUBSTR(SQLERRM,1,200));
1826 END IF;
1827 --
1828 raise;
1829 --
1830 END EmptyForecast;
1831
1832
1833 PROCEDURE ProcessReplaceAll (x_sched_rec IN RLM_INTERFACE_HEADERS%ROWTYPE,
1834 x_Group_rec IN OUT NOCOPY rlm_dp_sv.t_Group_rec,
1835 x_designator IN OUT NOCOPY mrp_forecast_interface_pk.t_forecast_designator)
1836
1837 IS
1838 --
1839 t_forecast mrp_forecast_interface_pk.t_forecast_interface;
1840 empty_designator mrp_forecast_interface_pk.t_forecast_designator;
1841 empty_forecast mrp_forecast_interface_pk.t_forecast_interface;
1842 v_last NUMBER;
1843 v_counter NUMBER;
1844 v_mrp_count NUMBER:=0;
1845 v_designator VARCHAR2(10);
1846 --
1847 BEGIN
1848 --
1849 IF (l_debug <> -1) THEN
1850 rlm_core_sv.dpush(k_SDEBUG,'ProcessReplaceAll');
1851 END IF;
1852 --
1853 -- count the no of mrp lines for the given header and status =5 /*2816086*/
1854 --
1855 IF x_Group_rec.bill_to_site_use_id IS NOT NULL THEN
1856 --
1857 select count(*) into v_mrp_count
1858 from rlm_interface_lines
1859 where header_id = x_Sched_rec.header_id
1860 and item_detail_type = k_MRP_FORECAST
1861 and process_status = 5
1862 and bill_to_site_use_id = x_Group_rec.bill_to_site_use_id
1863 and nvl(ship_to_site_use_id, k_NNULL)
1864 = nvl(x_Group_rec.ship_to_site_use_id, k_NNULL)
1865 and ship_from_org_id = x_Group_rec.ship_from_org_id;
1866 --
1867 ELSE
1868 --
1869 select count(*) into v_mrp_count
1870 from rlm_interface_lines
1871 where header_id = x_Sched_rec.header_id
1872 and item_detail_type = k_MRP_FORECAST
1873 and process_status = 5
1874 and bill_to_site_use_id IS NULL
1875 and nvl(ship_to_site_use_id ,k_NNULL)
1876 = nvl(x_Group_rec.ship_to_site_use_id,k_NNULL)
1877 and ship_from_org_id = x_Group_rec.ship_from_org_id;
1878 --
1879 END IF;
1880 --
1881 v_last := g_designator_tab.last;
1882 v_counter := g_designator_tab.first;
1883 --
1884 IF (v_mrp_count = 0) THEN /*2816086*/
1885 --
1886 IF(g_designator_tab.COUNT <> 0) THEN
1887 --{
1888 WHILE v_counter <= v_last LOOP
1889 --{
1890 IF (g_designator_tab(v_counter).designator = x_designator(x_designator.COUNT).forecast_designator)
1891 AND (g_designator_tab(v_counter).organization_id = x_designator(x_designator.COUNT).organization_id) THEN --Bugfix 6817494
1892 --
1893 k_REPLACE_FLAG := FALSE;
1894 --
1895 IF (l_debug <> -1) THEN
1896 rlm_core_sv.dlog(k_DEBUG,'already deleted old forecast for designator', g_designator_tab(v_counter).designator);
1897 END IF;
1898 --
1899 EXIT;
1900 --
1901 ELSE
1902 --
1903 k_REPLACE_FLAG := TRUE;
1904 --
1905 END IF;
1906 --
1907 v_counter := v_counter+1;
1908 --}
1909 END LOOP; --loop for designators already deleted
1910 --
1911 IF (k_REPLACE_FLAG = TRUE) THEN
1912 --
1913 RLM_TPA_SV.emptyforecast( x_sched_rec,
1914 x_Group_rec,
1915 empty_forecast,
1916 empty_designator,
1917 x_designator);
1918 --
1919 END IF;
1920 --}
1921 ELSE
1922 --
1923 RLM_TPA_SV.emptyforecast( x_sched_rec,
1924 x_Group_rec,
1925 empty_forecast,
1926 empty_designator,
1927 x_designator);
1928 --
1929 END IF; --check for g_designator_tab
1930 --
1931 END IF; --check for v_mrp_count
1932 --
1933 IF (l_debug <> -1) THEN
1934 rlm_core_sv.dpop(k_SDEBUG,'ProcessReplaceAll');
1935 END IF;
1936 --
1937 EXCEPTION
1938 --
1939 WHEN OTHERS THEN
1940 --
1941 IF (l_debug <> -1) THEN
1942 rlm_core_sv.dpop(K_SDEBUG,'EXCEPTION: '||SUBSTR(SQLERRM,1,200));
1943 END IF;
1944 --
1945 RAISE;
1946 --
1947 END ProcessReplaceAll;
1948
1949
1950 /*===========================================================================
1951
1952 PROCEDURE NAME: GetTPContext
1953
1954 DESCRIPTION: This procedure returns the tp group context.
1955 This procedure returns a null x_ship_to_ece_locn_code,
1956 and null x_inter_ship_to_ece_locn_code
1957
1958 CHANGE HISTORY: created jckwok 12/11/03
1959
1960 ===========================================================================*/
1961 PROCEDURE GetTPContext( x_sched_rec IN RLM_INTERFACE_HEADERS%ROWTYPE DEFAULT NULL,
1962 x_group_rec IN rlm_dp_sv.t_Group_rec DEFAULT NULL,
1963 x_customer_number OUT NOCOPY VARCHAR2,
1964 x_ship_to_ece_locn_code OUT NOCOPY VARCHAR2,
1965 x_bill_to_ece_locn_code OUT NOCOPY VARCHAR2,
1966 x_inter_ship_to_ece_locn_code OUT NOCOPY VARCHAR2,
1967 x_tp_group_code OUT NOCOPY VARCHAR2)
1968 IS
1969 --
1970 v_Progress VARCHAR2(3) := '010';
1971 --
1972 BEGIN
1973 --
1974 IF (l_debug <> -1) THEN
1975 rlm_core_sv.dpush(k_SDEBUG,'GetTPContext');
1976 rlm_core_sv.dlog(k_DEBUG,'customer_id', x_sched_rec.customer_id);
1977 rlm_core_sv.dlog(k_DEBUG,'x_sched_rec.ece_tp_translator_code',
1978 x_sched_rec.ece_tp_translator_code);
1979 rlm_core_sv.dlog(k_DEBUG,'x_sched_rec.ece_tp_location_code_ext',
1980 x_sched_rec.ece_tp_location_code_ext);
1981 rlm_core_sv.dlog(k_DEBUG,'x_group_rec.ship_to_address_id',
1982 x_group_rec.ship_to_address_id);
1983 END IF;
1984 --
1985 IF x_sched_rec.ECE_TP_LOCATION_CODE_EXT is NOT NULL THEN
1986 -- Following query is changed as per TCA obsolescence project.
1987 SELECT ETG.TP_GROUP_CODE
1988 INTO x_tp_group_code
1989 FROM ECE_TP_GROUP ETG,
1990 ECE_TP_HEADERS ETH,
1991 HZ_CUST_ACCT_SITES ACCT_SITE
1992 WHERE ETG.TP_GROUP_ID = ETH.TP_GROUP_ID
1993 and ETH.TP_HEADER_ID = ACCT_SITE.TP_HEADER_ID
1994 and ACCT_SITE.CUST_ACCOUNT_ID = x_sched_rec.CUSTOMER_ID
1995 and ACCT_SITE.ECE_TP_LOCATION_CODE = x_Sched_rec.ECE_TP_LOCATION_CODE_EXT;
1996
1997 ELSE
1998 x_tp_group_code := x_sched_rec.ECE_TP_TRANSLATOR_CODE;
1999 END IF;
2000 --
2001 BEGIN
2002 --
2003 -- Following query is changed as per TCA obsolescence project.
2004 SELECT ece_tp_location_code
2005 INTO x_ship_to_ece_locn_code
2006 FROM HZ_CUST_ACCT_SITES ACCT_SITE
2007 WHERE ACCT_SITE.CUST_ACCT_SITE_ID = x_group_rec.ship_to_address_id;
2008 --
2009 EXCEPTION
2010 WHEN NO_DATA_FOUND THEN
2011 x_ship_to_ece_locn_code := NULL;
2012 END;
2013 --
2014 IF x_sched_rec.customer_id is NOT NULL THEN
2015 --
2016 -- Following query is changed as per TCA obsolescence project.
2017 SELECT account_number
2018 INTO x_customer_number
2019 FROM HZ_CUST_ACCOUNTS CUST_ACCT
2020 WHERE CUST_ACCT.CUST_ACCOUNT_ID = x_sched_rec.customer_id;
2021 --
2022 END IF;
2023 --
2024 IF (l_debug <> -1) THEN
2025 rlm_core_sv.dlog(k_DEBUG, 'customer_number', x_customer_number);
2026 rlm_core_sv.dlog(k_DEBUG,'x_ship_to_ece_locn_code', x_ship_to_ece_locn_code);
2027 rlm_core_sv.dlog(k_DEBUG, 'x_bill_to_ece_locn_code', x_bill_to_ece_locn_code);
2028 rlm_core_sv.dlog(k_DEBUG, 'x_inter_ship_to_ece_locn_code', x_inter_ship_to_ece_locn_code);
2029 rlm_core_sv.dlog(k_DEBUG, 'x_tp_group_code',x_tp_group_code);
2030 rlm_core_sv.dpop(k_SDEBUG);
2031 END IF;
2032 --
2033 EXCEPTION
2034 --
2035 WHEN NO_DATA_FOUND THEN
2036 --
2037 x_customer_number := NULL;
2038 IF (l_debug <> -1) THEN
2039 rlm_core_sv.dlog(k_DEBUG, 'No data found for' , x_sched_rec.customer_id);
2040 rlm_core_sv.dpop(k_SDEBUG);
2041 END IF;
2042 --
2043 WHEN OTHERS THEN
2044 --
2048 END IF;
2045 rlm_message_sv.sql_error('rlm_validatedemand_sv.GetTPContext',v_Progress);
2046 IF (l_debug <> -1) THEN
2047 rlm_core_sv.dpop(k_SDEBUG,'EXCEPTION: '||SUBSTR(SQLERRM,1,200));
2049 raise;
2050
2051 END GetTPContext;
2052
2053 /* Bug 4176961 : UOM conversion */
2054 PROCEDURE Convert_UOM (from_uom IN VARCHAR2,
2055 to_uom IN VARCHAR2,
2056 quantity IN OUT NOCOPY NUMBER,
2057 p_item_id IN NUMBER,
2058 p_org_id IN NUMBER)
2059 IS
2060
2061 result NUMBER;
2062 v_item_number MTL_ITEM_FLEXFIELDS.ITEM_NUMBER%TYPE;
2063 v_ShipFromOrgName VARCHAR2(250) DEFAULT NULL;
2064 e_PrimaryCodeMissing EXCEPTION;
2065 e_UndefinedUOMConversion EXCEPTION;
2066
2067 CURSOR c IS
2068 select item_number
2069 from mtl_item_flexfields
2070 where inventory_item_id = p_item_id
2071 and organization_id = p_org_id;
2072 --
2073 BEGIN
2074 --
2075 IF (l_debug <> -1) THEN
2076 rlm_core_sv.dpush(k_SDEBUG,'Convert_UOM');
2077 rlm_core_sv.dlog(k_DEBUG,'from_uom',from_uom);
2078 rlm_core_sv.dlog(k_DEBUG,'to_uom',to_uom);
2079 rlm_core_sv.dlog(k_DEBUG,'quantity',quantity);
2080 rlm_core_sv.dlog(k_DEBUG,'p_item_id',p_item_id);
2081 rlm_core_sv.dlog(k_DEBUG,'p_org_id',p_org_id);
2082 END IF;
2083 --
2084 IF to_uom IS NULL THEN
2085 raise e_PrimaryCodeMissing;
2086 END IF;
2087 --
2088 IF quantity is NULL THEN
2089 result := NULL;
2090 ELSE
2091 --
2092 IF from_uom = to_uom THEN
2093 result := round(quantity,9);
2094 ELSIF (from_uom IS NULL) THEN
2095 result := 0;
2096 ELSE
2097 --
2098 result := INV_CONVERT.inv_um_convert(p_item_id,
2099 9,
2100 quantity,
2101 from_uom,
2102 to_uom,
2103 NULL,
2104 NULL);
2105 IF (result = -99999) THEN
2106 result := 0;
2107 raise e_UndefinedUOMConversion;
2108 END IF;
2109 --
2110 END IF;
2111 --
2112 END IF;
2113 --
2114 quantity := result;
2115 --
2116 IF (l_debug <> -1) THEN
2117 rlm_core_sv.dlog(k_DEBUG,'result',result);
2118 rlm_core_sv.dpop(k_SDEBUG);
2119 END IF;
2120 --
2121 EXCEPTION
2122 --
2123 WHEN e_PrimaryCodeMissing THEN
2124 --
2125 BEGIN
2126 --
2127 OPEN c;
2128 FETCH c into v_item_number;
2129 --
2130 v_ShipFromOrgName := RLM_CORE_SV.get_ship_from(p_org_id);
2131 --
2132 rlm_message_sv.app_error(
2133 x_ExceptionLevel => rlm_message_sv.k_error_level,
2134 x_MessageName => 'RLM_ERROR_NO_PRIMARY_UOM',
2135 x_token1=> 'INVITM',
2136 x_value1=> v_item_number,
2137 x_token2=> 'SHP_FRM_ORG',
2138 x_value2=> v_ShipFromOrgName);
2139 CLOSE c;
2140 --
2141 IF (l_debug <> -1) THEN
2142 rlm_core_sv.dpop(k_SDEBUG,'EXCEPTION: RLM_ERROR_NO_PRIMARY_UOM');
2143 END IF;
2144 --
2145 EXCEPTION
2146 --
2147 WHEN NO_DATA_FOUND THEN
2148 --
2149 IF (l_debug <> -1) THEN
2150 rlm_core_sv.dpop(K_SDEBUG, 'No data found');
2151 END IF;
2152 --
2153 END;
2154 --
2155 raise e_Group_Error;
2156 --
2157 WHEN e_UndefinedUOMConversion THEN
2158 --
2159 BEGIN
2160 --
2161 OPEN c;
2162 FETCH c into v_item_number;
2163 --
2164 rlm_message_sv.app_error(
2165 x_ExceptionLevel => rlm_message_sv.k_error_level,
2166 x_MessageName => 'RLM_UNDEF_UOM_CONVERSION',
2167 x_token1=> 'FROM_UOM',
2168 x_value1=> from_uom,
2169 x_token2=> 'TO_UOM',
2170 x_value2=> to_uom,
2171 x_token3=> 'INVITM',
2172 x_value3=> v_item_number);
2173 CLOSE c;
2174 --
2175 IF (l_debug <> -1) THEN
2176 rlm_core_sv.dpop(k_SDEBUG,'EXCEPTION: RLM_UNDEF_UOM_CONVERSION');
2177 END IF;
2178 --
2179 EXCEPTION
2180 --
2181 WHEN NO_DATA_FOUND THEN
2182 --
2183 IF (l_debug <> -1) THEN
2184 rlm_core_sv.dpop(K_SDEBUG, 'No data found');
2185 END IF;
2186 --
2187 END;
2188 --
2189 raise e_Group_Error;
2190 --
2191 WHEN OTHERS THEN
2192 --
2193 IF (l_debug <> -1) THEN
2194 rlm_core_sv.dpop(k_SDEBUG,'EXCEPTION: '||SUBSTR(SQLERRM,1,200));
2195 END IF;
2196 --
2197 raise;
2198 --
2199 END Convert_UOM;
2200
2201 END RLM_FORECAST_SV;