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