[Home] [Help]
PACKAGE BODY: APPS.RLM_SHIP_DELIVERY_PATTERN_SV
Source
1 PACKAGE BODY RLM_SHIP_DELIVERY_PATTERN_SV as
2 /* $Header: RLMDPSDB.pls 120.2 2005/07/17 18:31:28 rlanka ship $*/
3
4 --
5 l_DEBUG NUMBER := NVL(fnd_profile.value('RLM_DEBUG_MODE'),-1);
6 --
7
8 /*=============================================================================
9
10 PROCEDURE NAME: calc_scheduled_ship_date
11
12 ==============================================================================*/
13
14 PROCEDURE calc_scheduled_ship_date(x_Input IN rlm_ship_delivery_pattern_sv.t_InputRec,
15 x_QuantityDate OUT NOCOPY rlm_ship_delivery_pattern_sv.t_OutputTable,
16 x_ReturnMessage OUT NOCOPY rlm_ship_delivery_pattern_sv.t_ErrorMsgTable,
17 x_ReturnStatus OUT NOCOPY NUMBER)
18 IS
19 --
20 v_Progress VARCHAR2(3) := '010';
21 v_SdpCode VARCHAR2(30);
22 v_DailyPercent rlm_core_sv.t_NumberTable;
23 v_WeeklyBucket t_BucketTable;
24 v_QuantityDate t_OutputTable;
25 v_LeadTime t_LeadTimeRec;
26 v_ReturnMessage t_ErrorMsgTable;
27 v_Input t_InputRec;
28 v_ShipMethod VARCHAR2(30);
29 v_SdpCodeReturnStatus NUMBER;
30 v_BreakBucketReturnStatus NUMBER;
31 e_ErrorCondition EXCEPTION;
32 v_tot_percent NUMBER;
33 x_message VARCHAR2(4000);
34 v_temp_shipdate DATE;
35 v_supplier_shp_calendar_cd VARCHAR2(50);
36 v_customer_rcv_calendar_cd VARCHAR2(50);
37 v_ShipLocationId NUMBER;
38 v_RcvLocationId NUMBER;
39 v_return_status VARCHAR2(1);
40 v_msg_count NUMBER;
41 v_msg_data VARCHAR2(2000);
42 v_entity VARCHAR2(100) := 'RCV';
43 e_ShpCalAPIFailed EXCEPTION;
44 e_RcvCalAPIFailed EXCEPTION;
45 e_ShpCalAPINULL EXCEPTION;
46 e_RcvCalAPINULL EXCEPTION;
47 e_SDPIntransitSetupDeliver EXCEPTION; -- Bug 3682051
48 e_SDPIntransitSetupShip EXCEPTION; -- Bug 3682051
49 v_summary VARCHAR2(3000);
50 v_details VARCHAR2(3000);
51 v_loop NUMBER;
52 e_SDPFailed EXCEPTION;
53 v_temp_LeadTime t_LeadTimeRec;
54 v_lead_count NUMBER;
55 j NUMBER;
56 trnc NUMBER;
57 --
58 BEGIN
59 --
60 IF (l_debug <> -1) THEN
61 rlm_core_sv.dpush(g_SDEBUG,'calc_scheduled_ship_date');
62 rlm_core_sv.dlog(g_DEBUG,'ShipDeliveryRuleName',
63 x_Input.ShipDeliveryRuleName);
64 rlm_core_sv.dlog(g_DEBUG,'ItemDetailSubtype',
65 x_Input.ItemDetailSubtype);
66 rlm_core_sv.dlog(g_DEBUG,'DateTypeCode', x_Input.DateTypeCode);
67 rlm_core_sv.dlog(g_DEBUG,'StartDateTime', x_Input.StartDateTime);
68 rlm_core_sv.dlog(g_DEBUG,'ShipToAddressId', x_Input.ShipToAddressId);
69 rlm_core_sv.dlog(g_DEBUG,'ShipToSiteUseId', x_Input.ShipToSiteUseId);
70 rlm_core_sv.dlog(g_DEBUG,'ShipFromOrgId', x_Input.ShipFromOrgId );
71 rlm_core_sv.dlog(g_DEBUG,'CustomerItemId', x_Input.CustomerItemId);
72 rlm_core_sv.dlog(g_DEBUG,'PrimaryQuantity ',x_Input.PrimaryQuantity);
73 rlm_core_sv.dlog(g_DEBUG,'EndDateTime ', x_Input.EndDateTime);
74 rlm_core_sv.dlog(g_DEBUG,'DefaultSDP ', x_Input.DefaultSDP);
75 rlm_core_sv.dlog(g_DEBUG,'ship_method ', x_Input.ship_method);
76 rlm_core_sv.dlog(g_DEBUG,'Intransit_time ', x_Input.Intransit_time);
77 rlm_core_sv.dlog(g_DEBUG,'time_uom_code ', x_Input.time_uom_code);
78 rlm_core_sv.dlog(g_DEBUG,'exclude non workdays flag', x_input.exclude_non_workdays_flag);
79 rlm_core_sv.dlog(g_DEBUG,'ShipToCustomerId', x_Input.ShiptoCustomerId );
80 END IF;
81 --
82 x_ReturnStatus := g_SUCCESS;
83 --
84 v_ReturnMessage := x_ReturnMessage;
85 --
86 v_Input := x_Input;
87 --
88 IF (l_debug <> -1) THEN
89 rlm_core_sv.dlog(g_DEBUG,'ShipToAddressId', v_Input.ShipToAddressId);
90 rlm_core_sv.dlog(g_DEBUG,'ShipFromOrgId', v_Input.ShipFromOrgId );
91 END IF;
92 --
93 /* Call shipping API to get calendars from Shipping Tables */
94 --
95 v_RcvLocationId := WSH_UTIL_CORE.Cust_Site_To_Location(
96 v_Input.ShipToSiteUseId);
97 --
98 IF (l_debug <> -1) THEN
99 rlm_core_sv.dlog(g_DEBUG,'v_RcvLocationId ', v_RcvLocationId);
100 rlm_core_sv.dlog(g_DEBUG,'CustomerId', v_Input.ShipToCustomerId);
101 rlm_core_sv.dlog(g_DEBUG,'CustomerId', v_Input.CustomerId);
102 END IF;
103 --
104 WSH_CAL_ASG_VALIDATIONS.Get_Calendar
105 ( p_api_version_number => 1.0,
106 p_init_msg_list => FND_API.G_FALSE,
107 x_return_status => v_return_status,
108 x_msg_count => v_msg_count,
109 x_msg_data => v_msg_data,
110 p_entity_type => 'CUSTOMER',
111 p_entity_id => nvl(v_Input.ShipToCustomerId,
112 v_Input.CustomerId),
113 p_location_id => v_RcvLocationId,
114 x_calendar_code => v_customer_rcv_calendar_cd
115 );
116 --
117 IF (l_debug <> -1) THEN
118 rlm_core_sv.dlog(g_DEBUG,'v_return_status ', v_return_status);
119 rlm_core_sv.dlog(g_DEBUG,'customer_rcv_calendar_cd',
120 v_customer_rcv_calendar_cd);
121 rlm_core_sv.dlog(g_DEBUG,'v_msg_count', v_msg_count);
122 rlm_core_sv.dlog(g_DEBUG,'v_msg_data', v_msg_data);
123 END IF;
124 --
125 IF v_return_status = FND_API.G_RET_STS_ERROR OR
126 v_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
127 --
128 raise e_RcvCalAPIFailed;
129 --
130 END IF;
131 --
132 /*
133 -- Bug 3733396 : Do not raise error if receiving calendar is not specified
134 --
135 IF v_customer_rcv_calendar_cd is NULL THEN
136 --
137 raise e_RcvCalAPINULL;
138 --
139 END IF;
140 */
141 --
142 v_entity := 'SHP';
143 v_ShipLocationId := WSH_UTIL_CORE.Org_To_Location( v_Input.ShipFromOrgId);
144 --
145 IF (l_debug <> -1) THEN
146 rlm_core_sv.dlog(g_DEBUG,'ShipFromOrgId ', v_Input.ShipFromOrgId);
147 rlm_core_sv.dlog(g_DEBUG,'v_ShipLocationId ', v_ShipLocationId);
148 END IF;
149 --
150 WSH_CAL_ASG_VALIDATIONS.Get_Calendar
151 ( p_api_version_number => 1.0,
152 p_init_msg_list => FND_API.G_FALSE,
153 x_return_status => v_return_status,
154 x_msg_count => v_msg_count,
155 x_msg_data => v_msg_data,
156 p_entity_type => 'ORG',
157 p_entity_id => v_Input.ShipFromOrgId,
158 p_location_id => v_ShiplocationId,
159 x_calendar_code => v_supplier_shp_calendar_cd
160 );
161 --
162 IF (l_debug <> -1) THEN
163 rlm_core_sv.dlog(g_DEBUG,'v_return_status ', v_return_status);
164 rlm_core_sv.dlog(g_DEBUG,'v_msg_count', v_msg_count);
165 rlm_core_sv.dlog(g_DEBUG,'v_msg_data', v_msg_data);
166 rlm_core_sv.dlog(g_DEBUG,'v_supplier_shp_calendar_cd',
167 v_supplier_shp_calendar_cd);
168 END IF;
169 --
170 IF v_return_status = FND_API.G_RET_STS_ERROR OR
171 v_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
172 --
173 raise e_ShpCalAPIFailed;
174 --
175 END IF;
176 --
177 IF v_supplier_shp_calendar_cd is NULL THEN
178 --
179 raise e_ShpCalAPINULL;
180 --
181 END IF;
182 --
183 v_Input.supplier_shp_calendar_cd := v_supplier_shp_calendar_cd;
184 v_Input.customer_rcv_calendar_cd := v_customer_rcv_calendar_cd;
185 --
186 --Determine the correct Ship Delivery Pattern Code
187 --
188 v_SDPCOde := v_Input.ShipDeliveryRuleName;
189 --
190 determine_sdp_code(v_Input.ShipDeliveryRuleName,
191 v_Input.use_edi_sdp_code_flag,
192 v_Input.DefaultSDP,
193 v_Input.CustomerId,
194 v_Input.ShipFromOrgId,
195 v_Input.ShipToAddressId,
196 v_ReturnMessage,
197 v_SdpCode,
198 v_SdpCodeReturnStatus);
199 --
200 IF v_SdpCodeReturnStatus = g_RaiseErr THEN
201 -- bug 1428466
202 raise e_SDPFailed;
203 END IF;
204 --
205 set_return_status(x_ReturnStatus,v_SdpCodeReturnStatus);
206 --
207 --Find the daily percentages
208 --
209 IF v_SdpCode IS NOT NULL THEN
210 --
211 v_DailyPercent := find_daily_percent(v_SdpCode);
212 --
213 v_tot_percent := v_DailyPercent(1) +
214 v_DailyPercent(2) +
215 v_DailyPercent(3) +
216 v_DailyPercent(4) +
217 v_DailyPercent(5) +
218 v_DailyPercent(6) +
219 v_DailyPercent(7) ;
220 --
221 -- If the total percent = 0 means that no sdp code needs to be applied
222 -- therefore the ship date and the receive date becomes the start date
223 --
224 ELSE
225 --
226 v_tot_percent := 0;
227 --
228 END IF;
229 --
230 IF (l_debug <> -1) THEN
231 rlm_core_sv.dlog(g_DEBUG,' v_tot_percent',v_tot_percent);
232 END IF;
233 --
234 --Find the appropriate lead time
235 --
236 v_ShipMethod := v_Input.ship_method;
237 --
238 v_LeadTime.time := v_Input.Intransit_time;
239 --
240 v_LeadTime.uom := v_Input.time_uom_code;
241 --
242 IF (v_Input.DateTypeCode = 'DELIVER') THEN
243 --
244 IF (v_LeadTime.Time IS NULL) THEN
245 --
246 set_return_status(x_ReturnStatus,g_ERROR);
247 --
248 IF (l_debug <> -1) THEN
249 rlm_core_sv.dlog(g_DEBUG,' Lead time is NULL, ERROR condition');
250 END IF;
251 --
252 --v_ReturnMessage.
253 rlm_message_sv.get_msg_text('RLM_NULL_LEAD_TIME',
254 x_message);
255
256 get_err_message(x_message, 'RLM_NULL_LEAD_TIME',-1, v_ReturnMessage);
257 --
258 END IF;
259 --
260 ELSIF (v_Input.DateTypeCode = 'SHIP') THEN
261 --
262 IF (v_LeadTime.Time IS NULL) THEN
263 --
264 set_return_status(x_ReturnStatus,g_WARNING);
265 --
266 IF (l_debug <> -1) THEN
267 rlm_core_sv.dlog(g_DEBUG,' Lead time is NULL, WARNING condition');
268 END IF;
269 --
270 rlm_message_sv.get_msg_text('RLM_NULL_LEAD_TIME',
271 x_message);
272 get_err_message( x_message,'RLM_NULL_LEAD_TIME',0, v_ReturnMessage);
273 --
274 END IF;
275 --
276 END IF;
277 --
278 -- Break buckets
279 --
280 IF (l_debug <> -1) THEN
281 rlm_core_sv.dlog(g_DEBUG,'ItemDetailSubtype',v_Input.ItemDetailSubtype);
282 END IF;
283 --
284 IF (v_Input.ItemDetailSubtype IN (g_WEEK,
285 g_FLEXIBLE,
286 g_MONTH,
287 g_QUARTER)) THEN
288 --
289 RLM_TPA_SV.break_bucket(v_Input,
290 v_ReturnMessage,
291 v_WeeklyBucket,
292 v_BreakBucketReturnStatus);
293 --
294 IF (l_debug <> -1) THEN
295 rlm_core_sv.dlog(g_DEBUG,' v_BreakBucketReturnStatus',
296 v_BreakBucketReturnStatus);
297 rlm_core_sv.dlog(g_DEBUG,' v_WeeklyBucket.COUNT ',
298 v_WeeklyBucket.COUNT);
299 END IF;
300 --
301 set_return_status(x_ReturnStatus,v_BreakBucketReturnStatus);
302 --
303 END IF;
304 --
305 --Check for API validation error condition
306 --
307 IF (x_ReturnStatus = g_ERROR) THEN
308 --
309 raise e_ErrorCondition;
310 --
311 END IF;
312 --
313 --Apply SDPC
314 --
315 IF (v_Input.ItemDetailSubtype IN (g_WEEK,
316 g_FLEXIBLE,
317 g_MONTH,
318 g_QUARTER)) THEN
319 --
320 FOR i IN 1..v_WeeklyBucket.COUNT LOOP
321 --
322 IF v_tot_percent <> 0 THEN
323 --
324 RLM_TPA_SV.apply_sdp_to_weekly_bucket(
325 v_Input,
326 v_WeeklyBucket(i).ItemDetailSubtype,
327 v_DailyPercent,
328 v_WeeklyBucket(i).StartDateTime,
329 v_WeeklyBucket(i).PrimaryQuantity,
330 v_WeeklyBucket(i).WholeNumber,
331 x_QuantityDate);
332 --
333 ELSE
334 --
335 x_QuantityDate(i).PlannedReceiveDate :=
336 v_WeeklyBucket(i).StartDateTime;
337 --
338 x_QuantityDate(i).PlannedShipmentDate :=
339 v_WeeklyBucket(i).StartDateTime;
340 --
341 x_QuantityDate(i).primaryQuantity :=
342 v_WeeklyBucket(i).PrimaryQuantity;
343 --
344 x_QuantityDate(i).ItemDetailSubtype :=
345 v_WeeklyBucket(i).ItemDetailSubtype;
346 --
347 END IF;
348 --
349 END LOOP;
350 --
351 ELSIF (v_Input.ItemDetailSubtype = g_DAY) THEN
352 --
353 IF v_tot_percent <> 0 THEN
354 --
355 RLM_TPA_SV.apply_sdp_to_daily_bucket(v_Input,
356 v_Input.ItemDetailSubtype,
357 v_DailyPercent,
358 v_Input.StartDateTime,
359 v_Input.PrimaryQuantity,
360 x_QuantityDate);
361 --
362 ELSE
363 --
364 x_QuantityDate(1).PlannedReceiveDate :=
365 v_Input.StartDateTime;
366 x_QuantityDate(1).PlannedShipmentDate :=
367 v_Input.StartDateTime;
368 x_QuantityDate(1).primaryQuantity :=
369 v_Input.PrimaryQuantity;
370 x_QuantityDate(1).ItemDetailSubtype :=
371 g_Day;
372 --
373 END IF;
374 --
375 END IF;
376 --
377 IF (l_debug <> -1) THEN
378 rlm_core_sv.dlog(g_DEBUG,'x_QuantityDate.COUNT',x_QuantityDate.COUNT);
379 rlm_core_sv.dlog(g_DEBUG,'rcv_calendar',v_input.customer_rcv_calendar_cd);
380 rlm_core_sv.dlog(g_DEBUG,'DateTypeCode',v_Input.DateTypeCode);
381 END IF;
382 --
383 -- Apply Lead Times
384 --
385 IF (v_Input.DateTypeCode = 'DELIVER') THEN
386 --
387 v_temp_LeadTime.Time:= v_LeadTime.Time;
388 v_temp_LeadTime.UOM:= v_LeadTime.UOM;
389 --
390 FOR i IN 1..x_QuantityDate.COUNT LOOP
391 --
392 IF(nvl(v_input.exclude_non_workdays_flag, 'N') = 'Y') THEN
393 --
394 /* add exclude non-workdays code here*/
395 v_lead_count :=0;
396 trnc:=0;
397
398 IF(v_LeadTime.UOM ='HR') THEN
399 --
400 v_temp_LeadTime.Time:= v_LeadTime.Time/24;
401 trnc:=v_temp_LeadTime.Time-TRUNC(v_temp_LeadTime.Time,0);
402 --
403 ELSE
404 --
405 v_temp_LeadTime.Time:= v_LeadTime.Time;
406 --
407 END IF;
408
409 v_temp_LeadTime.UOM :='DAY';
410 j:=trnc;
411 --
412 WHILE (v_lead_count < TRUNC(v_temp_LeadTime.Time,0)) LOOP
413 --
414 j:=j+1;
415 --
416 IF RLM_TPA_SV.check_send_date(v_Input,x_QuantityDate(i).PlannedReceiveDate - j) THEN
417 --
418 v_lead_count:= v_lead_count+1;
419 IF (l_debug <> -1) THEN
420 rlm_core_sv.dlog(g_DEBUG,'Found a valid send date');
421 END IF;
422 --
423 END IF;
424 --
425 END LOOP;
426 --
427 v_temp_LeadTime.Time:= j;
428
429 IF (l_debug <> -1) THEN
430 rlm_core_sv.dlog(g_DEBUG,'Effective Lead Time',v_temp_LeadTime.TIME);
431 END IF;
432 --
433 END IF;
434 --
435
436 --global_atp
437 IF NOT v_Input.ATPItemFlag THEN
438 apply_lead_time(v_temp_LeadTime, x_QuantityDate(i), 'SUBTRACT');
439 END IF;
440 --
441 --
442 --bug 1970599
443 v_loop := 0;
444 --
445 v_temp_shipdate := x_QuantityDate(i).PlannedShipmentDate;
446 --
447 WHILE (RLM_TPA_SV.check_send_date(v_Input,
448 x_QuantityDate(i).PlannedShipmentDate) = FALSE)
449 AND (v_loop < 40) --bug 2144910
450 LOOP
451 --
452 RLM_TPA_SV.determine_send_date(v_Input,
453 v_DailyPercent,
454 x_QuantityDate(i).PlannedReceiveDate);
455 x_QuantityDate(i).PlannedShipmentDate :=
456 x_QuantityDate(i).PlannedReceiveDate ;
457 IF NOT v_Input.ATPItemFlag THEN
458 --
459 apply_lead_time(v_LeadTime, x_QuantityDate(i), 'SUBTRACT');
460 --
461 END IF;
462 --
463 v_loop := v_loop + 1;
464 --
465 END LOOP;
466 --
467 -- WARNING
468 --
469 -- Bug 2955782 : Added CUST_ITEM and QTY tokens to the message
470 -- RLM_SHIP_DATE_OUTOFSYNC.
471
472 IF v_loop > 0 AND v_loop < 40 THEN --bug 3682051
473 rlm_message_sv.get_msg_text(
474 x_message_name => 'RLM_SHIP_DATE_OUTOFSYNC',
475 x_text => x_message,
476 x_token1 => 'SHIP_DATE',
477 x_value1 => v_temp_shipdate,
478 x_token2 => 'CUST_ITEM',
479 x_value2 => rlm_core_sv.get_item_number(x_input.CustomerItemId),
480 x_token3 => 'QTY',
481 x_value3 => v_Input.PrimaryQuantity,
482 x_token4 => 'SHIP_NEW_DATE',
483 x_value4 => x_QuantityDate(i).PlannedShipmentDate,
484 x_token5 => 'CALENDAR',
485 x_value5 => v_Input.supplier_shp_calendar_cd);
486
487 get_err_message( x_message,'RLM_SHIP_DATE_OUTOFSYNC',0,
488 v_ReturnMessage);
489 --
490 set_return_status(x_ReturnStatus,g_WARNING);
491 --
492 ELSIF v_loop = 40 THEN
493 --
494 raise e_SDPIntransitSetupDeliver;
495 --
496 END IF;
497 --
498 --
499 --bug 1970599
500 --
501 IF (RLM_TPA_SV.check_receive_date(v_Input,
502 x_QuantityDate(i).PlannedReceiveDate) = FALSE)
503 THEN
504 --
505 -- Generate Warning
506 --
507 -- Bug 2955782 : Added CUST_ITEM and QTY tokens to the message
508 -- RLM_RECVD_DATE_CLOSED.
509
510 rlm_message_sv.get_msg_text(
511 x_message_name => 'RLM_RECVD_DATE_CLOSED',
512 x_text => x_message,
513 x_token1 => 'CUST_ITEM',
514 x_value1 => rlm_core_sv.get_item_number(x_input.CustomerItemId),
515 x_token2 => 'QTY',
516 x_value2 => v_Input.PrimaryQuantity,
517 x_token3 => 'CALENDAR',
518 x_value3 => v_Input.customer_rcv_calendar_cd,
519 x_token4 => 'RECV_DATE',
520 x_value4 => x_QuantityDate(i).PlannedReceiveDate);
521
522 get_err_message( x_message,'RLM_RECVD_DATE_CLOSED',0,
523 v_ReturnMessage);
524
525 --
526 set_return_status(x_ReturnStatus,g_WARNING);
527 --
528 END IF;
529 --
530 END LOOP;
531 --
532 ELSIF (v_Input.DateTypeCode = 'SHIP') THEN
533 --
534 FOR i IN 1..x_QuantityDate.COUNT LOOP
535 --
536 --bug 1970599
537 v_loop := 0;
538 WHILE (RLM_TPA_SV.check_send_date(v_Input,
539 x_QuantityDate(i).PlannedShipmentDate) = FALSE)
540 AND (v_loop < 40 ) --bug 2144910
541 LOOP
542 --
543 RLM_TPA_SV.determine_send_date(
544 v_Input,
545 v_DailyPercent,
546 x_QuantityDate(i).PlannedShipmentDate);
547 --
548 v_loop := v_loop + 1;
549 --
550 END LOOP;
551 --
552 IF (RLM_TPA_SV.check_send_date(v_Input,
553 x_QuantityDate(i).PlannedShipmentDate) = FALSE)
554 THEN
555 --
556 -- Generate Warning
557 --
558 -- Bug 2955782 : Added CUST_ITEM and QTY tokens to the message
559 -- RLM_SHIP_DATE_CLOSED.
560
561 rlm_message_sv.get_msg_text(
562 x_message_name => 'RLM_SHIP_DATE_CLOSED',
563 x_text => x_message,
564 x_token1 => 'CUST_ITEM',
565 x_value1 => rlm_core_sv.get_item_number(x_input.CustomerItemId),
566 x_token2 => 'QTY',
567 x_value2 => v_Input.PrimaryQuantity,
568 x_token3 => 'SHIP_DATE',
569 x_value3 => x_QuantityDate(i).PlannedShipmentDate,
570 x_token4 => 'CALENDAR',
571 x_value4 => v_Input.supplier_shp_calendar_cd);
572
573 get_err_message( x_message,'RLM_SHIP_DATE_CLOSED',-1, v_ReturnMessage);
574 --
575 x_ReturnMessage := v_ReturnMessage;
576 raise e_SDPIntransitSetupShip;
577 --
578 END IF;
579 --
580 IF (v_LeadTime.Time IS NULL) THEN
581 --
582 x_QuantityDate(i).PlannedReceiveDate :=
583 x_QuantityDate(i).PlannedShipmentDate;
584 --
585 ELSE
586 --global_atp
587 IF NOT v_Input.ATPItemFlag THEN
588 apply_lead_time(v_LeadTime, x_QuantityDate(i), 'ADD');
589 END IF;
590 --
591 IF RLM_TPA_SV.check_receive_date(v_Input,
592 x_QuantityDate(i).PlannedReceiveDate) = FALSE THEN
593 --
594 /*
595 IF (l_debug <> -1) THEN
596 rlm_core_sv.dlog(g_DEBUG,'RLM_RECVD_DATE_CLOSED',
597 x_QuantityDate(i).PlannedReceiveDate);
598 END IF;
599
600 rlm_message_sv.get_msg_text(
601 x_message_name => 'RLM_RECVD_DATE_CLOSED',
602 x_text => x_message,
603 x_token1 => 'CALENDAR',
604 x_value1 => v_Input.customer_rcv_calendar_cd,
605 x_token2 => 'RECV_DATE',
606 x_value2 => x_QuantityDate(i).PlannedReceiveDate);
607
608 get_err_message( x_message,'RLM_RECVD_DATE_CLOSED',0,
609 v_ReturnMessage);
610 */
611
612 set_return_status(x_ReturnStatus,g_WARNING);
613 END IF;
614 --
615 --
616 END IF;
617 --
618 END LOOP;
619 --
620 END IF;
621 --
622 x_ReturnMessage := v_ReturnMessage;
623 --
624 IF (l_debug <> -1) THEN
625 rlm_core_sv.dlog(g_DEBUG,' x_ReturnMessage.COUNT ',x_ReturnMessage.COUNT);
626 rlm_core_sv.dlog(g_DEBUG,' x_ReturnStatus ',x_ReturnStatus);
627 rlm_core_sv.dpop(g_SDEBUG);
628 END IF;
629 --
630 EXCEPTION
631 --
632 WHEN e_ShpCalAPINULL THEN
633 --
634 set_return_status(x_ReturnStatus,g_Error);
635 rlm_message_sv.get_msg_text(
636 x_message_name => 'RLM_NO_SHP_CALENDAR',
637 x_text => x_message,
638 x_token1 => 'ORG',
639 x_value1 => rlm_core_sv.get_ship_from(v_Input.ShipFromOrgId));
640 --
641 IF (l_debug <> -1) THEN
642 rlm_core_sv.dlog(g_DEBUG,' x_message ',x_message);
643 END IF;
644 --
645 get_err_message( x_message,'RLM_NO_SHP_CALENDAR',-1, v_ReturnMessage);
646 x_ReturnMessage := v_ReturnMessage;
647 --
648 IF (l_debug <> -1) THEN
649 rlm_core_sv.dlog(g_DEBUG,' x_ReturnStatus ',x_ReturnStatus);
650 rlm_core_sv.dpop(g_SDEBUG,'e_ShpCalAPINULL');
651 END IF;
652 --
653 WHEN e_RcvCalAPINULL THEN
654 --
655 set_return_status(x_ReturnStatus,g_Error);
656 rlm_message_sv.get_msg_text(
657 x_message_name => 'RLM_NO_RCV_CALENDAR',
658 x_text => x_message,
659 x_token1 => 'CUSTOMER',
660 x_value1 => rlm_core_sv.get_customer_name(v_Input.CustomerId));
661 --
662 IF (l_debug <> -1) THEN
663 rlm_core_sv.dlog(g_DEBUG,' x_message ',x_message);
664 END IF;
665 --
666 get_err_message( x_message,'RLM_NO_RCV_CALENDAR',-1, v_ReturnMessage);
667 x_ReturnMessage := v_ReturnMessage;
668 --
669 IF (l_debug <> -1) THEN
670 rlm_core_sv.dlog(g_DEBUG,' x_ReturnStatus ',x_ReturnStatus);
671 rlm_core_sv.dpop(g_SDEBUG,'e_RcvCalAPINULL');
672 END IF;
673 --
674 WHEN e_ShpCalAPIFailed THEN
675 --
676 set_return_status(x_ReturnStatus,g_Error);
677 WSH_UTIL_CORE.Get_Messages('N',v_summary, v_details, v_msg_count);
678 --
679 IF (l_debug <> -1) THEN
680 rlm_core_sv.dlog(g_DEBUG,' v_summary ',v_summary);
681 rlm_core_sv.dlog(g_DEBUG,' v_details ',v_details);
682 END IF;
683 --
684 get_err_message(v_summary,NULL,-1, v_ReturnMessage);
685 x_ReturnMessage := v_ReturnMessage;
686 --
687 IF (l_debug <> -1) THEN
688 rlm_core_sv.dlog(g_DEBUG,' x_ReturnMessage.COUNT ',x_ReturnMessage.COUNT);
689 rlm_core_sv.dlog(g_DEBUG,' x_ReturnStatus ',x_ReturnStatus);
690 rlm_core_sv.dpop(g_SDEBUG,'e_ShpCalAPIFailed');
691 END IF;
692 --
693 WHEN e_RcvCalAPIFailed THEN
694 --
695 set_return_status(x_ReturnStatus,g_Error);
696 WSH_UTIL_CORE.Get_Messages('N',v_summary, v_details, v_msg_count);
697 --
698 IF (l_debug <> -1) THEN
699 rlm_core_sv.dlog(g_DEBUG,' v_summary ',v_summary);
700 rlm_core_sv.dlog(g_DEBUG,' v_details ',v_details);
701 END IF;
702 --
703 get_err_message(v_summary,NULL,-1, v_ReturnMessage);
704 x_ReturnMessage := v_ReturnMessage;
705 --
706 IF (l_debug <> -1) THEN
707 rlm_core_sv.dlog(g_DEBUG,' x_ReturnMessage.COUNT ',x_ReturnMessage.COUNT);
708 rlm_core_sv.dlog(g_DEBUG,' x_ReturnStatus ',x_ReturnStatus);
709 rlm_core_sv.dpop(g_SDEBUG,'e_RcvCalAPIFailed');
710 END IF;
711 --
712 WHEN e_SDPIntransitSetupDeliver THEN
713 --
714 -- Bug 3671477
715 --
716 set_return_status(x_ReturnStatus,g_Error);
717 rlm_message_sv.get_msg_text(
718 x_message_name => 'RLM_INVALID_SDP_INTRANSIT',
719 x_text => x_message);
720 get_err_message( x_message,'RLM_INVALID_SDP_INTRANSIT',-1, v_ReturnMessage);
721 x_ReturnMessage := v_ReturnMessage;
722 --
723 IF (l_debug <> -1) THEN
724 rlm_core_sv.dlog(g_DEBUG,' x_message ',x_message);
725 rlm_core_sv.dlog(g_DEBUG,' x_ReturnStatus ',x_ReturnStatus);
726 rlm_core_sv.dpop(g_SDEBUG,'e_SDPIntransitSetupDeliver');
727 END IF;
728 --
729 WHEN e_SDPIntransitSetupShip THEN
730 --
731 -- Bug 3671477
732 --
733 set_return_status(x_ReturnStatus,g_Error);
734 --
735 IF (l_debug <> -1) THEN
736 rlm_core_sv.dlog(g_DEBUG,' x_message ',x_message);
737 rlm_core_sv.dlog(g_DEBUG,' x_ReturnStatus ',x_ReturnStatus);
738 rlm_core_sv.dpop(g_SDEBUG,'e_SDPIntransitSetup');
739 END IF;
740 --
741 WHEN NO_DATA_FOUND THEN
742 --
743 set_return_status(x_ReturnStatus, g_Error);
744 IF v_entity = 'RCV' THEN
745 v_entity := rlm_core_sv.get_ship_from(v_Input.ShipFromOrgId);
746 ELSE
747 v_entity := rlm_core_sv.get_ship_to(v_Input.ShipToAddressId);
748 END IF;
749 rlm_message_sv.get_msg_text(
750 x_message_name => 'RLM_NO_LOCATION_CODE',
751 x_text => x_message,
752 x_token1 => 'ENTITY',
753 x_value1 => v_entity);
754 get_err_message( x_message,'RLM_NO_LOCATION_CODE',-1, v_ReturnMessage);
755 x_ReturnMessage := v_ReturnMessage;
756 --
757 IF (l_debug <> -1) THEN
758 rlm_core_sv.dlog(g_DEBUG,' x_ReturnMessage.COUNT ',x_ReturnMessage.COUNT);
759 rlm_core_sv.dlog(g_DEBUG,' x_ReturnStatus ',x_ReturnStatus);
760 rlm_core_sv.dpop(g_SDEBUG,'NO_DATA_FOUND');
761 END IF;
762 --
763 WHEN e_ErrorCondition THEN
764 --
765 x_ReturnMessage := v_ReturnMessage;
766 --
767 IF (l_debug <> -1) THEN
768 rlm_core_sv.dlog(g_DEBUG,' x_ReturnMessage.COUNT ',x_ReturnMessage.COUNT);
769 rlm_core_sv.dlog(g_DEBUG,' x_ReturnStatus ',x_ReturnStatus);
770 rlm_core_sv.dpop(g_SDEBUG,'e_ErrorCondition');
771 END IF;
772 --
773 WHEN e_SDPFailed THEN
774 x_ReturnStatus := g_RaiseErr;
775 x_ReturnMessage := v_ReturnMessage;
776 --
777 IF (l_debug <> -1) THEN
778 rlm_core_sv.dpop(g_SDEBUG,'calc_scheduled_ship_date g_RaiseErr');
779 END IF;
780 --
781 WHEN OTHERS THEN
782 --
783 rlm_message_sv.sql_error('rlm_ship_delivery_pattern_sv.calc_scheduled_ship_date', v_Progress);
784 --
785 IF (l_debug <> -1) THEN
786 rlm_core_sv.dpop(g_SDEBUG,'EXCEPTION: '||SUBSTR(SQLERRM,1,200));
787 END IF;
788 --
789 raise;
790 --
791 END calc_scheduled_ship_date;
792 --
793 /*=============================================================================
794
795 FUNCTION NAME: determine_sdp_code
796
797 ==============================================================================*/
798
799 PROCEDURE determine_sdp_code(
800 ShipDeliveryRuleName IN VARCHAR2,
801 use_edi_sdp_code_flag IN rlm_cust_shipto_terms.use_edi_sdp_code_flag%TYPE,
802 DefaultSDP IN rlm_cust_shipto_terms.ship_delivery_rule_name%TYPE,
803 x_customer_id IN NUMBER,
804 x_shipFromOrg IN NUMBER,
805 x_shipTo IN NUMBER,
806 x_ReturnMessage IN OUT NOCOPY t_ErrorMsgTable,
807 x_SdpCode OUT NOCOPY VARCHAR2,
808 x_ReturnStatus OUT NOCOPY NUMBER)
809 IS
810 --
811 v_Progress varchar2(3) :='010';
812 v_Exists NUMBER := 0;
813 x_message VARCHAR2(4000);
814 e_SDPFailed EXCEPTION;
815 --
816 BEGIN
817 --
818 IF (l_debug <> -1) THEN
819 rlm_core_sv.dpush(g_SDEBUG,'determine_sdp_code');
820 rlm_core_sv.dlog(g_DEBUG,'ShipDeliveryRuleName ',ShipDeliveryRuleName );
821 rlm_core_sv.dlog(g_DEBUG,'use_edi_sdp_code_flag ',use_edi_sdp_code_flag );
822 rlm_core_sv.dlog(g_DEBUG,'DefaultSDP ',DefaultSDP );
823 END IF;
824 --
825 IF (nvl(use_edi_sdp_code_flag,'N') = 'Y' ) THEN
826 --
827 x_SdpCode := ShipDeliveryRuleName;
828 --
829 IF (x_SdpCode IS NOT NULL) THEN
830 --
831 SELECT count(*)
832 INTO v_Exists
833 FROM rlm_ship_delivery_codes
834 WHERE ship_delivery_rule_name = x_SdpCode;
835 --
836 IF (v_Exists > 0) THEN
837 --
838 x_ReturnStatus := g_SUCCESS;
839 --
840 IF (l_debug <> -1) THEN
841 rlm_core_sv.dlog(g_DEBUG,'successful and value returned');
842 END IF;
843 --
844 ELSE
845 --
846 /*WARNING RLM_INVALID_SDP*/
847 x_ReturnStatus := g_WARNING;
848 --
849 rlm_message_sv.get_msg_text(
850 x_message_name => 'RLM_INVALID_SDP',
851 x_text => x_message,
852 x_token1 => 'SDP_CODE',
853 x_value1 => x_SdpCode);
854 get_err_message(x_message,'RLM_INVALID_SDP',0, x_ReturnMessage);
855 --
856 IF DefaultSDP IS NULL THEN
857 --
858 -- bug 1428466
859 --
860 IF (l_debug <> -1) THEN
861 rlm_core_sv.dlog(g_DEBUG,'Error Default SDP is NULL');
862 END IF;
863 --
864 rlm_message_sv.get_msg_text(
865 x_message_name => 'RLM_NULL_SDP',
866 x_text => x_message,
867 x_token1 => 'SHIPFROM',
868 x_value1 => rlm_core_sv.get_ship_from(x_shipFromOrg),
869 x_token2 => 'CUSTOMER',
870 x_value2 => rlm_core_sv.get_customer_name(x_customer_id),
871 x_token3 => 'SHIPTO',
872 x_value3 => rlm_core_sv.get_ship_to(x_shipTo));
873 get_err_message( x_message,'RLM_NULL_SDP',-1,
874 x_ReturnMessage);
875 raise e_SDPFailed;
876 ELSE
877 x_SdpCode := DefaultSDP;
878 --
879 IF (l_debug <> -1) THEN
880 rlm_core_sv.dlog(g_DEBUG,'invalid input sdp, defaults applied');
881 END IF;
882 --
883 END IF;
884 --
885 END IF;
886 --
887 ELSE
888 --
889 -- WARNING RLM_NULL_SDP_ON_EDI
890 IF DefaultSDP IS NULL THEN
891 --
892 -- bug 1428466
893 --
894 IF (l_debug <> -1) THEN
895 rlm_core_sv.dlog(g_DEBUG,'Error Default SDP is NULL');
896 END IF;
897 --
898 rlm_message_sv.get_msg_text(
899 x_message_name => 'RLM_NULL_SDP',
900 x_text => x_message,
901 x_token1 => 'SHIPFROM',
902 x_value1 => rlm_core_sv.get_ship_from(x_shipFromOrg),
903 x_token2 => 'CUSTOMER',
904 x_value2 => rlm_core_sv.get_customer_name(x_customer_id),
905 x_token3 => 'SHIPTO',
906 x_value3 => rlm_core_sv.get_ship_to(x_shipTo));
907 get_err_message( x_message,'RLM_NULL_SDP',-1,
908 x_ReturnMessage);
909 raise e_SDPFailed;
910 ELSE
911 x_SdpCode := DefaultSDP;
912 x_ReturnStatus := g_WARNING;
913 BEGIN
914 rlm_message_sv.get_msg_text(
915 x_message_name => 'RLM_NULL_SDP_ON_EDI',
916 x_text => x_message,
917 x_token1 => 'SHIPFROM',
918 x_value1 => rlm_core_sv.get_ship_from(x_shipFromOrg),
919 x_token2 => 'CUSTOMER',
920 x_value2 => rlm_core_sv.get_customer_name(x_customer_id),
921 x_token3 => 'SHIPTO',
922 x_value3 => rlm_core_sv.get_ship_to(x_shipTo),
923 x_token4 => 'SDP_CODE',
924 x_value4 => DefaultSDP);
925 EXCEPTION
926 WHEN OTHERS THEN
927 --
928 IF (l_debug <> -1) THEN
929 rlm_core_sv.dlog(g_DEBUG,'Problem in creating the message: '||
930 'RLM_NULL_SDP_ON_EDI');
931 END IF;
932 --
933 x_message := 'Could not create RLM_NULL_SDP_ON_EDI';
934 END;
935 --
936 get_err_message( x_message,'RLM_NULL_SDP_ON_EDI',0,
937 x_ReturnMessage);
938 --
939 IF (l_debug <> -1) THEN
940 rlm_core_sv.dlog(g_DEBUG,'null input sdp, defaults applied');
941 END IF;
942 --
943 END IF;
944 --
945 END IF;
946 --
947 ELSE
948 --
949 IF (DefaultSDP IS NOT NULL) THEN
950 --
951 x_SdpCode := DefaultSDP;
952 x_ReturnStatus := g_SUCCESS;
953 --
954 IF (l_debug <> -1) THEN
955 rlm_core_sv.dlog(g_DEBUG,'null input sdp, defaults applied');
956 END IF;
957 --
958 ELSE
959 --
960 /*ERROR RLM_NULL_SDP*/
961 x_SdpCode := DefaultSDP;
962 x_ReturnStatus := g_ERROR;
963 rlm_message_sv.get_msg_text(
964 x_message_name => 'RLM_NULL_SDP',
965 x_text => x_message,
966 x_token1 => 'SHIPFROM',
967 x_value1 => rlm_core_sv.get_ship_from(x_shipFromOrg),
968 x_token2 => 'CUSTOMER',
969 x_value2 => rlm_core_sv.get_customer_name(x_customer_id),
970 x_token3 => 'SHIPTO',
971 x_value3 => rlm_core_sv.get_ship_to(x_shipTo));
972 get_err_message( x_message,'RLM_NULL_SDP',-1, x_ReturnMessage);
973 --
974 IF (l_debug <> -1) THEN
975 rlm_core_sv.dlog(g_DEBUG,'null default sdp');
976 END IF;
977 --
978 raise e_SDPFailed;
979 --
980 END IF;
981 --
982 END IF;
983 --
984 IF (l_debug <> -1) THEN
985 rlm_core_sv.dlog(g_DEBUG,'x_ReturnStatus ',x_ReturnStatus);
986 rlm_core_sv.dlog(g_DEBUG,'x_SdpCode', x_SdpCode);
987 rlm_core_sv.dpop(g_SDEBUG);
988 END IF;
989 --
990 EXCEPTION
991 --
992 WHEN e_SDPFailed THEN
993 --
994 x_ReturnStatus := g_RaiseErr;
995 --
996 IF (l_debug <> -1) THEN
997 rlm_core_sv.dpop(g_SDEBUG,'determine_sdp_code, g_RaiseErr');
998 END IF;
999 --
1000 WHEN OTHERS THEN
1001 --
1002 x_ReturnStatus := g_ERROR;
1003 rlm_message_sv.sql_error('rlm_ship_delivery_pattern_sv.determine_sdp_code'
1004 ,v_Progress);
1005 IF (l_debug <> -1) THEN
1006 rlm_core_sv.dpop(g_SDEBUG,'EXCEPTION: '||SUBSTR(SQLERRM,1,200));
1007 END IF;
1008 --
1009 raise;
1010 --
1011 END determine_sdp_code;
1012
1013 /*=============================================================================
1014
1015 FUNCTION NAME: find_default_sdp_code
1016
1017 ==============================================================================*/
1018
1019 FUNCTION find_default_sdp_code( x_ShipFromOrgId IN NUMBER,
1020 x_ShipToAddressId IN NUMBER,
1021 x_CustomerItemId IN NUMBER)
1022 RETURN VARCHAR2
1023 IS
1024 --
1025 v_SdpCode VARCHAR2(30);
1026 x_message VARCHAR2(4000);
1027 --
1028 BEGIN
1029 --
1030 SELECT ship_delivery_rule_name
1031 INTO v_SdpCode
1032 FROM RLM_CUST_ITEM_TERMS_ALL
1033 WHERE customer_item_id = x_CustomerItemId
1034 AND ship_from_org_id = x_ShipFromOrgId
1035 AND address_id = x_ShipToAddressId;
1036 --
1037 IF (v_SdpCode IS NULL) THEN
1038 --
1039 SELECT ship_delivery_rule_name
1040 INTO v_SdpCode
1041 FROM RLM_CUST_SHIPTO_TERMS_ALL
1042 WHERE ship_from_org_id = x_ShipFromOrgId
1043 AND address_id = x_ShipToAddressId;
1044 --
1045 END IF;
1046 --
1047 RETURN(v_SdpCode);
1048 --
1049 END find_default_sdp_code;
1050
1051 /*=====================================================================
1052
1053 PROCEDURE NAME: set_return_status
1054
1055 ==============================================================================*/
1056
1057 PROCEDURE set_return_status(x_ReturnStatus IN OUT NOCOPY NUMBER,
1058 x_InputStatus IN NUMBER)
1059 IS
1060 BEGIN
1061 --
1062 IF (x_InputStatus > x_ReturnStatus) THEN
1063 x_ReturnStatus := x_InputStatus;
1064 END IF;
1065 --
1066 END set_return_status;
1067
1068 /*=============================================================================
1069
1070 FUNCTION NAME: find_daily_percent
1071
1072 ==============================================================================*/
1073
1074 FUNCTION find_daily_percent(x_RuleName IN VARCHAR2)
1075 RETURN rlm_core_sv.t_NumberTable
1076 IS
1077 --
1078 v_Progress VARCHAR2(3) :='010';
1079 v_DailyPercent rlm_core_sv.t_NumberTable;
1080 --
1081 BEGIN
1082 --
1083 IF (l_debug <> -1) THEN
1084 rlm_core_sv.dpush(g_SDEBUG,'find_daily_percent');
1085 rlm_core_sv.dlog(g_DEBUG,'x_RuleName ',x_RuleName );
1086 END IF;
1087 --
1088 SELECT sunday_percent/100,
1089 monday_percent/100,
1090 tuesday_percent/100,
1091 wednesday_percent/100,
1092 thursday_percent/100,
1093 friday_percent/100,
1094 saturday_percent/100
1095 INTO v_DailyPercent(g_SundayDOW),
1096 v_DailyPercent(g_MondayDOW),
1097 v_DailyPercent(g_TuesdayDOW),
1098 v_DailyPercent(g_WednesdayDOW),
1099 v_DailyPercent(g_ThursdayDOW),
1100 v_DailyPercent(g_FridayDOW),
1101 v_DailyPercent(g_SaturdayDOW)
1102 FROM rlm_ship_delivery_codes
1103 WHERE ship_delivery_rule_name = x_RuleName;
1104 --
1105 IF (l_debug <> -1) THEN
1106 rlm_core_sv.dlog(g_DEBUG,'v_DailyPercent(1) ', v_DailyPercent(1));
1107 rlm_core_sv.dlog(g_DEBUG,'v_DailyPercent(2) ', v_DailyPercent(2));
1108 rlm_core_sv.dlog(g_DEBUG,'v_DailyPercent(3) ', v_DailyPercent(3));
1109 rlm_core_sv.dlog(g_DEBUG,'v_DailyPercent(4) ', v_DailyPercent(4));
1110 rlm_core_sv.dlog(g_DEBUG,'v_DailyPercent(5) ', v_DailyPercent(5));
1111 rlm_core_sv.dlog(g_DEBUG,'v_DailyPercent(6) ', v_DailyPercent(6));
1112 rlm_core_sv.dlog(g_DEBUG,'v_DailyPercent(7) ', v_DailyPercent(7));
1113 rlm_core_sv.dpop(g_SDEBUG,'successfullly returned value');
1114 END IF;
1115 --
1116 RETURN(v_DailyPercent);
1117 --
1118 EXCEPTION
1119 --
1120 WHEN NO_DATA_FOUND THEN
1121 --
1122 rlm_message_sv.sql_error('rlm_ship_delivery_pattern_sv.find_daily_percent', v_Progress);
1123 --
1124 IF (l_debug <> -1) THEN
1125 rlm_core_sv.dlog(g_DEBUG,'EXCEPTION: When no data found sql error');
1126 rlm_core_sv.dpop(g_SDEBUG);
1127 END IF;
1128 --
1129 raise;
1130 --
1131 WHEN OTHERS THEN
1132 --
1133 rlm_message_sv.sql_error('rlm_ship_delivery_pattern_sv.find_daily_percent', v_Progress);
1134 --
1135 IF (l_debug <> -1) THEN
1136 rlm_core_sv.dpop(g_SDEBUG,'EXCEPTION: '||SUBSTR(SQLERRM,1,200));
1137 END IF;
1138 --
1139 raise;
1140 --
1141 END find_daily_percent;
1142
1143 /*=============================================================================
1144
1145 FUNCTION NAME: get_ship_method
1146
1147 ==============================================================================*/
1148
1149 FUNCTION get_ship_method(x_ShipFromOrgId IN NUMBER,
1150 x_ShipToAddressId IN NUMBER,
1151 x_CustomerItemId IN NUMBER)
1152 RETURN VARCHAR2
1153 IS
1154 v_Progress VARCHAR2(3) :='010';
1155 v_ShipMethod VARCHAR(30);
1156 x_message VARCHAR2(4000);
1157
1158 BEGIN
1159 --
1160 IF (l_debug <> -1) THEN
1161 rlm_core_sv.dpush(g_SDEBUG,'get_ship_method');
1162 rlm_core_sv.dlog(g_DEBUG,'x_ShipFromOrgId ',x_ShipFromOrgId);
1163 rlm_core_sv.dlog(g_DEBUG,'x_ShipToAddressId ',x_ShipToAddressId);
1164 rlm_core_sv.dlog(g_DEBUG,'x_CustomerItemId ',x_CustomerItemId);
1165 END IF;
1166 --
1167 SELECT ship_method
1168 INTO v_ShipMethod
1169 FROM RLM_CUST_ITEM_TERMS_ALL
1170 WHERE ship_from_org_id = x_ShipFromOrgId AND
1171 address_id = x_ShipToAddressId AND
1172 customer_item_id = x_CustomerItemId;
1173 --
1174 IF (v_ShipMethod IS NULL) THEN
1175 --
1176 SELECT ship_method
1177 INTO v_ShipMethod
1178 FROM RLM_CUST_SHIPTO_TERMS_ALL
1179 WHERE ship_from_org_id = x_ShipFromOrgId AND
1180 address_id = x_ShipToAddressId;
1181 --
1182 IF (l_debug <> -1) THEN
1183 rlm_core_sv.dlog(g_DEBUG,'v_ShipMethod ',v_ShipMethod);
1184 rlm_core_sv.dpop(g_SDEBUG,'Found at Address level');
1185 END IF;
1186 --
1187 RETURN(v_ShipMethod);
1188 --
1189 ELSE
1190 --
1191 IF (l_debug <> -1) THEN
1192 rlm_core_sv.dlog(g_DEBUG,'v_ShipMethod ',v_ShipMethod);
1193 rlm_core_sv.dpop(g_SDEBUG,'Found at Item level');
1194 END IF;
1195 --
1196 RETURN(v_ShipMethod);
1197 --
1198 END IF;
1199 --
1200 END get_ship_method;
1201
1202 /*=============================================================================
1203
1204 FUNCTION NAME: determine_lead_time
1205
1206 ==============================================================================*/
1207
1208 FUNCTION determine_lead_time(x_ShipFromOrgId IN NUMBER,
1209 x_ShipToAddressId IN NUMBER,
1210 x_ShipMethod IN VARCHAR2)
1211 RETURN t_LeadTimeRec
1212 IS
1213 v_Progress VARCHAR2(3) :='010';
1214 v_LeadTime t_LeadTimeRec;
1215 x_message VARCHAR2(4000);
1216
1217 BEGIN
1218 --
1219 IF (l_debug <> -1) THEN
1220 rlm_core_sv.dpush(g_SDEBUG,'determine_lead_time');
1221 rlm_core_sv.dlog(g_DEBUG,'x_ShipFromOrgId ',x_ShipFromOrgId);
1222 rlm_core_sv.dlog(g_DEBUG,'x_ShipToAddressId ',x_ShipToAddressId);
1223 rlm_core_sv.dlog(g_DEBUG,'x_ShipMethod ',x_ShipMethod);
1224 END IF;
1225 --
1226 SELECT intransit_time, time_uom_code
1227 INTO v_LeadTime.Time, v_LeadTime.Uom
1228 FROM mtl_interorg_ship_methods
1229 WHERE from_organization_id = x_ShipFromOrgId AND
1230 to_organization_id = x_ShipToAddressId AND
1231 ship_method = x_ShipMethod;
1232 --
1233 IF (v_LeadTime.Uom IS NULL) THEN
1234 v_LeadTime.Uom := 'DAY';
1235 END IF;
1236 --
1237 IF (l_debug <> -1) THEN
1238 rlm_core_sv.dlog(g_DEBUG,'v_LeadTime.Time ',v_LeadTime.Time);
1239 rlm_core_sv.dlog(g_DEBUG,'v_LeadTime.Uom ',v_LeadTime.Uom);
1240 rlm_core_sv.dpop(g_SDEBUG,'Lead time found');
1241 END IF;
1242 --
1243 RETURN(v_LeadTime);
1244 --
1245 EXCEPTION
1246 WHEN NO_DATA_FOUND THEN
1247 --
1248 IF (l_debug <> -1) THEN
1249 rlm_core_sv.dpop(g_SDEBUG,'Lead time not found');
1250 END IF;
1251 --
1252 RETURN(null);
1253 --
1254 END determine_lead_time;
1255
1256
1257 /*=============================================================================
1258
1259 PROCEDURE NAME: break_bucket
1260
1261 ==============================================================================*/
1262
1263 PROCEDURE break_bucket(
1264 x_Input IN RLM_SHIP_DELIVERY_PATTERN_SV.t_InputRec,
1265 x_ReturnMessage IN OUT NOCOPY RLM_SHIP_DELIVERY_PATTERN_SV.t_ErrorMsgTable,
1266 x_WeeklyBucket OUT NOCOPY RLM_SHIP_DELIVERY_PATTERN_SV.t_BucketTable,
1267 x_ReturnStatus OUT NOCOPY NUMBER)
1268 IS
1269 v_Progress VARCHAR2(3) :='010';
1270 v_ReturnMessage VARCHAR2(30) ;
1271 v_Count NUMBER := 1;
1272 v_Buckets NUMBER := 1;
1273 v_WholeNumber BOOLEAN := TRUE;
1274 v_LastDayQuarter DATE ;
1275 v_WeeksInQuarter NUMBER := 12;
1276 v_WrongDateException EXCEPTION;
1277 x_message VARCHAR2(4000);
1278
1279 BEGIN
1280 --
1281 IF (l_debug <> -1) THEN
1282 rlm_core_sv.dpush(g_SDEBUG,'break_bucket');
1283 END IF;
1284 --
1285 /*Check if input Primary Quantity is a whole number*/
1286 --
1287 IF (MOD(x_Input.PrimaryQuantity,1)>0) THEN
1288 --
1289 v_WholeNumber := FALSE;
1290 --
1291 END IF;
1292 --
1293 IF (x_Input.ItemDetailSubtype = g_QUARTER) THEN
1294 --
1295 IF (RLM_TPA_SV.check_start_date(x_Input,'QUARTER') = FALSE) THEN
1296 --
1297 raise v_WrongDateException;
1298 --
1299 ELSE
1300 --
1301 v_LastDayQuarter := last_day(ADD_MONTHS(x_Input.StartDateTime,2));
1302 --
1303 IF (l_debug <> -1) THEN
1304 rlm_core_sv.dlog(g_DEBUG,'v_LastDayQuarter', v_LastDayQuarter);
1305 END IF;
1306 --
1307 v_WeeksInQuarter := TRUNC((v_LastDayQuarter -
1308 x_Input.StartDateTime)/7 );
1309 --
1310 IF (l_debug <> -1) THEN
1311 rlm_core_sv.dlog(g_DEBUG,'v_WeeksInQuarter', v_WeeksInQuarter);
1312 END IF;
1313 --
1314 FOR week IN 0..v_WeeksInQuarter-1 LOOP
1315 --
1316 IF (l_debug <> -1) THEN
1317 rlm_core_sv.dlog(g_DEBUG,'week', week);
1318 END IF;
1319 --
1320 x_WeeklyBucket(v_Count).StartDateTime :=
1321 x_Input.StartDateTime + 7*week;
1322 --
1323 IF (l_debug <> -1) THEN
1324 rlm_core_sv.dlog(g_DEBUG,'StartDateTime ',
1325 x_WeeklyBucket(v_Count).StartDateTime);
1326 END IF;
1327 --
1328 x_WeeklyBucket(v_Count).PrimaryQuantity :=
1329 RLM_TPA_SV.get_weekly_quantity(v_WholeNumber,
1330 v_Count,
1331 x_Input,
1332 v_WeeksInQuarter);
1333 --
1334 IF (l_debug <> -1) THEN
1335 rlm_core_sv.dlog(g_DEBUG,'PrimaryQuantity',
1336 x_WeeklyBucket(v_Count).PrimaryQuantity);
1337 END IF;
1338 --
1339 v_Count := v_Count + 1;
1340 --
1341 IF (l_debug <> -1) THEN
1342 rlm_core_sv.dlog(g_DEBUG,'v_Count', v_Count);
1343 END IF;
1344 --
1345 END LOOP;
1346 --
1347 END IF;
1348 --
1349 ELSIF (x_Input.ItemDetailSubtype = g_MONTH) THEN
1350 --
1351 IF (RLM_TPA_SV.check_start_date(x_Input,'MONTH') = FALSE) THEN
1352 raise v_WrongDateException;
1353 --
1354 ELSE
1355 --
1356 FOR day IN 0..3 LOOP
1357 x_WeeklyBucket(v_Count).StartDateTime := x_Input.StartDateTime + 7*day;
1358 --
1359 IF (l_debug <> -1) THEN
1360 rlm_core_sv.dlog(g_DEBUG,'StartDateTime ', x_WeeklyBucket(v_Count).StartDateTime);
1361 END IF;
1362 x_WeeklyBucket(v_Count).PrimaryQuantity := RLM_TPA_SV.get_weekly_quantity(v_WholeNumber, v_Count,
1363 x_Input, 4);
1364 --
1365 IF (l_debug <> -1) THEN
1366 rlm_core_sv.dlog(g_DEBUG,'PrimaryQuantity', x_WeeklyBucket(v_Count).PrimaryQuantity);
1367 END IF;
1368 --
1369 v_Count := v_Count + 1;
1370 --
1371 END LOOP;
1372 --
1373 END IF;
1374 --
1375 ELSIF (x_Input.ItemDetailSubtype = g_FLEXIBLE) THEN
1376 WHILE ( (x_Input.StartDateTime+(7*v_Buckets)) <= x_Input.EndDateTime ) LOOP
1377 --
1378 v_Buckets := v_Buckets + 1;
1379 --
1380 END LOOP;
1381 /*Now v_Buckets gives the number of weekly buckets*/
1382 --
1383 FOR v_Count IN 1..v_Buckets LOOP
1384 --
1385 x_WeeklyBucket(v_Count).StartDateTime := x_Input.StartDateTime + 7*(v_Count-1);
1386 --
1387 IF (l_debug <> -1) THEN
1388 rlm_core_sv.dlog(g_DEBUG,'StartDateTime ', x_WeeklyBucket(v_Count).StartDateTime);
1389 END IF;
1390 --
1391 x_WeeklyBucket(v_Count).PrimaryQuantity := RLM_TPA_SV.get_weekly_quantity(v_WholeNumber, v_Count,
1392 x_Input, v_Buckets);
1393 --
1394 IF (l_debug <> -1) THEN
1395 rlm_core_sv.dlog(g_DEBUG,'PrimaryQuantity', x_WeeklyBucket(v_Count).PrimaryQuantity);
1396 END IF;
1397 --
1398 END LOOP;
1399 --
1400 ELSIF (x_Input.ItemDetailSubtype = g_WEEK) THEN
1401 --
1402 IF (RLM_TPA_SV.check_start_date(x_Input,'WEEK') = FALSE) THEN
1403 --
1404 raise v_WrongDateException;
1405 --
1406 ELSE
1407 --
1408 x_WeeklyBucket(v_Count).StartDateTime := x_Input.StartDateTime;
1409 x_WeeklyBucket(v_Count).PrimaryQuantity := x_Input.PrimaryQuantity;
1410 --
1411 IF (l_debug <> -1) THEN
1412 rlm_core_sv.dlog(g_DEBUG,'StartDateTime ', x_WeeklyBucket(v_Count).StartDateTime);
1413 rlm_core_sv.dlog(g_DEBUG,'PrimaryQuantity', x_WeeklyBucket(v_Count).PrimaryQuantity);
1414 END IF;
1415 --
1416 END IF;
1417 --
1418 END IF;
1419 --
1420 FOR v_Count IN 1..x_WeeklyBucket.COUNT LOOP
1421 --
1422 x_WeeklyBucket(v_Count).ShipDeliveryRuleName := x_Input.ShipDeliveryRuleName;
1423 x_WeeklyBucket(v_Count).ItemDetailSubtype := g_WEEK;
1424 x_WeeklyBucket(v_Count).DateTypeCode := x_Input.DateTypeCode;
1425 x_WeeklyBucket(v_Count).ShipToAddressId := x_Input.ShipToAddressId;
1426 x_WeeklyBucket(v_Count).ShipFromOrgId := x_Input.ShipFromOrgId;
1427 x_WeeklyBucket(v_Count).CustomerItemId := x_Input.CustomerItemId;
1428 x_WeeklyBucket(v_Count).EndDateTime := x_Input.EndDateTime;
1429 x_WeeklyBucket(v_Count).WholeNumber := v_WholeNumber;
1430 --
1431 END LOOP;
1432 --
1433 x_ReturnStatus := g_SUCCESS;
1434 --
1435 IF (l_debug <> -1) THEN
1436 rlm_core_sv.dlog(g_DEBUG,' Num of recs in x_WeeklyBucket',x_WeeklyBucket.COUNT);
1437 rlm_core_sv.dlog(g_DEBUG,' x_ReturnStatus ',x_ReturnStatus);
1438 rlm_core_sv.dpop(g_SDEBUG,'successful');
1439 END IF;
1440 --
1441 EXCEPTION
1442 WHEN v_WrongDateException THEN
1443 x_ReturnStatus := g_ERROR;
1444 --
1445 IF (l_debug <> -1) THEN
1446 rlm_core_sv.dlog(g_DEBUG,' x_ReturnStatus ',x_ReturnStatus);
1447 END IF;
1448 --
1449 rlm_message_sv.get_msg_text(x_message_name => 'RLM_INVALID_DATE_FOR_BUCKET',
1450 x_text => x_message,
1451 x_token1 => 'START_DATE',
1452 x_value1 => x_Input.StartDateTime,
1453 x_token2 => 'BUCKET',
1454 x_value2 => rlm_core_sv.get_lookup_meaning('RLM_DEMAND_SUBTYPE',
1455 x_Input.ItemDetailSubType));
1456 get_err_message( x_message,'RLM_INVALID_DATE_FOR_BUCKET',-1, x_ReturnMessage);
1457 --
1458 IF (l_debug <> -1) THEN
1459 rlm_core_sv.dpop(g_SDEBUG,'unsuccessful');
1460 END IF;
1461 --
1462 END break_bucket;
1463
1464 /*=============================================================================
1465
1466 FUNCTION NAME: get_weekly_quantity
1467
1468 ==============================================================================*/
1469
1470 FUNCTION get_weekly_quantity(x_WholeNumber IN BOOLEAN,
1471 x_Count IN NUMBER,
1472 x_Input IN rlm_ship_delivery_pattern_sv.t_InputRec,
1473 x_DivideBy IN NUMBER)
1474 RETURN NUMBER
1475 IS
1476 --
1477 v_WeeklyQuantity NUMBER;
1478 v_Temp NUMBER;
1479 x_Quantity NUMBER;
1480 v_TruncateTo NUMBER;
1481 x_message VARCHAR2(4000);
1482 --
1483 BEGIN
1484 --
1485 IF (l_debug <> -1) THEN
1486 rlm_core_sv.dpush(g_SDEBUG,'get_weekly_quantity');
1487 rlm_core_sv.dlog(g_DEBUG,'x_WholeNumber ',x_WholeNumber );
1488 rlm_core_sv.dlog(g_DEBUG,'x_Count ',x_Count );
1489 rlm_core_sv.dlog(g_DEBUG,'x_Quantity ',x_Input.PrimaryQuantity);
1490 rlm_core_sv.dlog(g_DEBUG,'x_DivideBy ',x_DivideBy );
1491 END IF;
1492 --
1493 x_Quantity := x_Input.PrimaryQuantity;
1494 v_TruncateTo := get_precision();
1495 --
1496 IF (x_Count = 1) THEN
1497 IF (x_WholeNumber = TRUE) THEN
1498 v_WeeklyQuantity := FLOOR(x_Quantity/x_DivideBy) + MOD(x_Quantity,x_DivideBy);
1499 ELSE
1500 v_Temp := TRUNC((x_Quantity/x_DivideBy),v_TruncateTo);
1501 v_WeeklyQuantity := v_Temp + (x_Quantity - v_Temp*x_DivideBy);
1502 END IF;
1503 ELSE
1504 IF (x_WholeNumber = TRUE) THEN
1505 v_WeeklyQuantity := FLOOR(x_Quantity/x_DivideBy) ;
1506 ELSE
1507 v_WeeklyQuantity := TRUNC((x_Quantity/x_DivideBy),v_TruncateTo);
1508 END IF;
1509 END IF;
1510 --
1511 IF (l_debug <> -1) THEN
1512 rlm_core_sv.dlog(g_DEBUG,' v_WeeklyQuantity ',v_WeeklyQuantity);
1513 rlm_core_sv.dpop(g_SDEBUG,'successful');
1514 END IF;
1515 --
1516 RETURN(v_WeeklyQuantity);
1517 --
1518 END get_weekly_quantity;
1519
1520 /*=============================================================================
1521
1522 FUNCTION NAME: get_precision
1523
1524 ==============================================================================*/
1525
1526 FUNCTION get_precision
1527 RETURN NUMBER
1528 IS
1529 BEGIN
1530 --
1531 IF (l_debug <> -1) THEN
1532 rlm_core_sv.dpush(g_SDEBUG,'get_precision');
1533 rlm_core_sv.dlog(g_DEBUG,' g_PRECISION ',g_PRECISION );
1534 rlm_core_sv.dpop(g_SDEBUG,'successful');
1535 END IF;
1536 --
1537 RETURN(g_PRECISION);
1538 --
1539 END get_precision;
1540
1541
1542 /*=============================================================================
1543
1544 PROCEDURE NAME: apply_sdp_to_weekly_bucket
1545
1546 ==============================================================================*/
1547
1548 PROCEDURE apply_sdp_to_weekly_bucket(
1549 x_Input IN RLM_SHIP_DELIVERY_PATTERN_SV.t_InputRec,
1550 x_ItemDetailSubtype IN VARCHAR2,
1551 x_DailyPercent IN rlm_core_sv.t_NumberTable,
1552 x_StartDateTime IN DATE,
1553 x_PrimaryQuantity IN NUMBER,
1554 x_WholeNumber IN BOOLEAN,
1555 x_QuantityDate IN OUT NOCOPY RLM_SHIP_DELIVERY_PATTERN_SV.t_OutputTable)
1556 IS
1557 v_Progress VARCHAR2(3) :='010';
1558 v_Count NUMBER;
1559 v_MondayDate DATE;
1560 v_Changed BOOLEAN := FALSE;
1561 v_Sum NUMBER := 0;
1562 v_InitialCount NUMBER;
1563 v_TruncateTo NUMBER;
1564 x_message VARCHAR2(4000);
1565 BEGIN
1566 --
1567 IF (l_debug <> -1) THEN
1568 rlm_core_sv.dpush(g_SDEBUG,'apply_sdp_to_weekly_bucket');
1569 rlm_core_sv.dlog(g_DEBUG,'x_ItemDetailSubtype ',x_ItemDetailSubtype );
1570 rlm_core_sv.dlog(g_DEBUG,'x_StartDateTime ',x_StartDateTime );
1571 rlm_core_sv.dlog(g_DEBUG,'x_PrimaryQuantity ',x_PrimaryQuantity );
1572 rlm_core_sv.dlog(g_DEBUG,'x_WholeNumber ',x_WholeNumber );
1573 END IF;
1574 --
1575 v_TruncateTo := get_precision();
1576 v_Count := x_QuantityDate.COUNT + 1;
1577 v_InitialCount := v_Count;
1578 v_MondayDate := RLM_TPA_SV.find_monday_date(x_Input, x_StartDateTime);
1579 --
1580 IF (l_debug <> -1) THEN
1581 rlm_core_sv.dlog(g_DEBUG,'v_TruncateTo ',v_TruncateTo );
1582 rlm_core_sv.dlog(g_DEBUG,'v_Count ',v_Count );
1583 rlm_core_sv.dlog(g_DEBUG,'v_MondayDate ',v_MondayDate );
1584 END IF;
1585 --
1586 FOR i IN 1..7 LOOP
1587 --
1588 IF (x_DailyPercent(i) > 0) THEN
1589 --
1590 IF (l_debug <> -1) THEN
1591 rlm_core_sv.dlog(g_DEBUG,'x_DailyPercent(i)',x_DailyPercent(i));
1592 END IF;
1593 --
1594 x_QuantityDate(v_Count).PlannedReceiveDate := v_MondayDate +
1595 (i - g_MondayDOW);
1596 x_QuantityDate(v_Count).PlannedShipmentDate := v_MondayDate +
1597 (i - g_MondayDOW);
1598 x_QuantityDate(v_Count).ItemDetailSubtype := g_Day;
1599 --
1600 IF (x_WholeNumber = TRUE) THEN
1601 --
1602 x_QuantityDate(v_Count).PrimaryQuantity :=
1603 FLOOR(x_PrimaryQuantity * x_DailyPercent(i));
1604 v_Sum := v_Sum + x_QuantityDate(v_Count).PrimaryQuantity;
1605 --
1606 ELSE
1607 --
1608 x_QuantityDate(v_Count).PrimaryQuantity :=
1609 TRUNC(x_PrimaryQuantity * x_DailyPercent(i), v_TruncateTo);
1610 v_Sum := v_Sum + x_QuantityDate(v_Count).PrimaryQuantity;
1611 --
1612 END IF;
1613 --
1614 IF (l_debug <> -1) THEN
1615 rlm_core_sv.dlog(g_DEBUG,'PlannedReceiveDate ',
1616 x_QuantityDate(v_Count).PlannedReceiveDate);
1617 rlm_core_sv.dlog(g_DEBUG,'PlannedShipmentDate ',
1618 x_QuantityDate(v_Count).PlannedShipmentDate);
1619 rlm_core_sv.dlog(g_DEBUG,'PrimaryQuantity ',
1620 x_QuantityDate(v_Count).PrimaryQuantity);
1621 END IF;
1622 --
1623 v_Changed := TRUE;
1624 v_Count := v_Count + 1;
1625 --
1626 END IF;
1627 --
1628 END LOOP;
1629 --
1630 IF (v_Changed = TRUE) THEN
1631 --
1632 IF (l_debug <> -1) THEN
1633 rlm_core_sv.dlog(g_DEBUG,'v_Changed is TRUE ');
1634 END IF;
1635 --
1636 x_QuantityDate(v_InitialCount).PrimaryQuantity :=
1637 x_QuantityDate(v_InitialCount).PrimaryQuantity +
1638 (x_PrimaryQuantity - v_Sum);
1639 x_QuantityDate(v_InitialCount).ItemDetailSubtype := g_Day;
1640 --
1641 IF (l_debug <> -1) THEN
1642 rlm_core_sv.dlog(g_DEBUG,'PrimaryQuantity ',
1643 x_QuantityDate(v_InitialCount).PrimaryQuantity);
1644 rlm_core_sv.dlog(g_DEBUG,'ItemDetailSubtype ',
1645 x_QuantityDate(v_InitialCount).ItemDetailSubtype);
1646 END IF;
1647 --
1648 ELSIF (v_Changed = FALSE) THEN
1649 --
1650 x_QuantityDate(v_Count).PlannedReceiveDate := x_StartDateTime;
1651 --
1652 x_QuantityDate(v_Count).PlannedShipmentDate := x_StartDateTime;
1653 --
1654 x_QuantityDate(v_Count).PrimaryQuantity := x_PrimaryQuantity;
1655 --
1656 x_QuantityDate(v_Count).ItemDetailSubtype := g_Day;
1657 --
1658 IF (l_debug <> -1) THEN
1659 rlm_core_sv.dlog(g_DEBUG ,'PlannedReceiveDate ',
1660 x_QuantityDate(v_Count).PlannedReceiveDate);
1661 rlm_core_sv.dlog(g_DEBUG, 'PlannedShipmentDate ',
1662 x_QuantityDate(v_Count).PlannedShipmentDate);
1663 rlm_core_sv.dlog(g_DEBUG, 'PrimaryQuantity ',
1664 x_QuantityDate(v_Count).PrimaryQuantity);
1665 END IF;
1666 --
1667 END IF;
1668 --
1669 IF (l_debug <> -1) THEN
1670 rlm_core_sv.dpop(g_SDEBUG,'successful');
1671 END IF;
1672 --
1673 EXCEPTION
1674 --
1675 WHEN OTHERS THEN
1676 --
1677 rlm_message_sv.sql_error('rlm_ship_delivery_pattern_sv.apply_sdp_to_weekly_bucket', v_Progress);
1678 --
1679 IF (l_debug <> -1) THEN
1680 rlm_core_sv.dpop(g_SDEBUG,'EXCEPTION: '||SUBSTR(SQLERRM,1,200));
1681 END IF;
1682 --
1683 raise;
1684 --
1685 END apply_sdp_to_weekly_bucket;
1686
1687
1688 /*=============================================================================
1689
1690 PROCEDURE NAME: apply_sdp_to_daily_bucket
1691
1692 ==============================================================================*/
1693
1694 PROCEDURE apply_sdp_to_daily_bucket(
1695 x_input IN rlm_ship_delivery_pattern_sv.t_InputRec,
1696 x_ItemDetailSubtype IN VARCHAR2,
1697 x_DailyPercent IN rlm_core_sv.t_NumberTable,
1698 x_StartDateTime IN DATE,
1699 x_PrimaryQuantity IN NUMBER,
1700 x_QuantityDate IN OUT NOCOPY rlm_ship_delivery_pattern_sv.t_OutputTable)
1701 IS
1702 v_Progress VARCHAR2(3) :='010';
1703 x_message VARCHAR2(4000);
1704
1705 BEGIN
1706 --
1707 IF (l_debug <> -1) THEN
1708 rlm_core_sv.dpush(g_SDEBUG,'apply_sdp_to_daily_bucket');
1709 rlm_core_sv.dlog(g_DEBUG,'CustomerId',x_Input.CustomerId);
1710 rlm_core_sv.dlog(g_DEBUG,'ShipFromOrgId',x_Input.ShipFromOrgId);
1711 rlm_core_sv.dlog(g_DEBUG,'x_ItemDetailSubtype', x_Input.ItemDetailSubtype );
1712 rlm_core_sv.dlog(g_DEBUG,'x_StartDateTime', x_Input.StartDateTime);
1713 rlm_core_sv.dlog(g_DEBUG,'x_PrimaryQuantity',x_Input.PrimaryQuantity);
1714 END IF;
1715 --
1716 IF(RLM_TPA_SV.valid_sdp_date(x_Input,x_DailyPercent)) THEN
1717 --
1718 x_QuantityDate(1).PlannedReceiveDate := x_Input.StartDateTime;
1719 x_QuantityDate(1).PlannedShipmentDate := x_Input.StartDateTime;
1720 --
1721 ELSE
1722 --
1723 x_QuantityDate(1).PlannedReceiveDate :=
1724 RLM_TPA_SV.previous_valid_sdp_date(x_Input,
1725 x_Input.StartDateTime, x_DailyPercent);
1726 x_QuantityDate(1).PlannedShipmentDate :=
1727 x_QuantityDate(1).PlannedReceiveDate;
1728 --
1729 END IF;
1730 --
1731 x_QuantityDate(1).PrimaryQuantity := x_Input.PrimaryQuantity;
1732 x_QuantityDate(1).ItemDetailSubtype := g_Day;
1733 --
1734 IF (l_debug <> -1) THEN
1735 rlm_core_sv.dlog(g_DEBUG,'x_QuantityDate(1).PlannedReceiveDate',
1736 x_QuantityDate(1).PlannedReceiveDate);
1737 rlm_core_sv.dlog(g_DEBUG,'x_QuantityDate(1).PlannedShipmentDate',
1738 x_QuantityDate(1).PlannedShipmentDate);
1739 rlm_core_sv.dlog(g_DEBUG,'x_QuantityDate(1).PrimaryQuantity',
1740 x_QuantityDate(1).PrimaryQuantity);
1741 rlm_core_sv.dlog(g_DEBUG,'x_QuantityDate(1).ItemDetailSubtype',
1742 x_QuantityDate(1).ItemDetailSubtype);
1743 rlm_core_sv.dpop(g_SDEBUG,'successful');
1744 END IF;
1745 --
1746 END apply_sdp_to_daily_bucket;
1747
1748
1749 /*=============================================================================
1750
1751 FUNCTION NAME: check_start_date
1752
1753 ==============================================================================*/
1754
1755 FUNCTION check_start_date(
1756 x_Input IN rlm_ship_delivery_pattern_sv.t_InputRec,
1757 x_BucketType IN VARCHAR2)
1758 RETURN BOOLEAN
1759 IS
1760 v_Progress VARCHAR2(3) :='010';
1761 v_ReturnStatus BOOLEAN := TRUE;
1762 x_message VARCHAR2(4000);
1763
1764 BEGIN
1765 --
1766 IF (l_debug <> -1) THEN
1767 rlm_core_sv.dpush(g_SDEBUG,'check_start_date');
1768 rlm_core_sv.dlog(g_DEBUG, 'x_StartDateTime', x_Input.StartDateTime );
1769 rlm_core_sv.dlog(g_DEBUG, 'x_BucketType', x_BucketType );
1770 rlm_core_sv.dlog(g_DEBUG, 'v_ReturnStatus', v_ReturnStatus );
1771 END IF;
1772
1773 --
1774 -- Bug 1867988
1775 -- Do not validate x_Input.StartDateTime for quarterly and monthly buckets
1776 /*
1777 IF (x_BucketType='QUARTER') THEN
1778 --
1779 IF (to_char(x_Input.StartDateTime,'DD-MM')
1780 IN ('01-01','01-04','01-07','01-10')) THEN
1781 v_ReturnStatus := TRUE;
1782 ELSE
1783 v_ReturnStatus := FALSE;
1784 END IF;
1785 --
1786 ELSIF (x_BucketType='MONTH') THEN
1787 IF (to_char(x_Input.StartDateTime,'DD-MM') IN ('01-01',
1788 '01-02',
1789 '01-03',
1790 '01-04',
1791 '01-05',
1792 '01-06',
1793 '01-07',
1794 '01-08',
1795 '01-09',
1796 '01-10',
1797
1798 '01-12')) THEN
1799 v_ReturnStatus := TRUE;
1800 ELSE
1801 v_ReturnStatus := FALSE;
1802 END IF;
1803 */
1804
1805 /*
1806 ELSIF (x_BucketType='WEEK') THEN
1807 IF (to_char(x_StartDateTime,'D')= g_MondayDOW) THEN
1808 v_ReturnStatus := TRUE;
1809 ELSE
1810 v_ReturnStatus := FALSE;
1811 END IF;
1812
1813 END IF;
1814 */
1815
1816 --
1817 IF (l_debug <> -1) THEN
1818 rlm_core_sv.dlog(g_DEBUG,' v_ReturnStatus', v_ReturnStatus );
1819 rlm_core_sv.dpop(g_SDEBUG,'check_start_date exited');
1820 END IF;
1821 --
1822 RETURN(v_ReturnStatus);
1823 --
1824 END check_start_date;
1825
1826
1827 /*=============================================================================
1828
1829 FUNCTION NAME: find_monday_date
1830
1831 ==============================================================================*/
1832
1833 FUNCTION find_monday_date(x_Input IN rlm_ship_delivery_pattern_sv.t_InputRec,
1834 x_Date IN DATE)
1835 RETURN DATE
1836 IS
1837 v_MondayDate DATE;
1838 x_message VARCHAR2(4000);
1839 BEGIN
1840 --
1841 IF (l_debug <> -1) THEN
1842 rlm_core_sv.dpush(g_SDEBUG,'find_monday_date');
1843 rlm_core_sv.dlog(g_DEBUG,' x_Date', x_Date );
1844 rlm_core_sv.dlog(g_DEBUG,' g_SundayDOW ', g_SundayDOW);
1845 END IF;
1846 --
1847 IF (to_char(x_Date,'D') = g_SundayDOW ) THEN
1848 v_MondayDate := x_Date + 1;
1849 ELSE
1850 v_MondayDate := x_Date - (to_number(to_char(x_Date,'D'))- g_MondayDOW);
1851 END IF;
1852 --
1853 /*
1854 IF (to_char(x_Date,'D') = '1') THEN
1855 v_MondayDate := x_Date + 1;
1856 ELSE
1857 v_MondayDate := x_Date - (to_number(to_char(x_Date,to_char(to_date('01/06/1997','DD/MM/YYYY'))'D'))-2);
1858 END IF;
1859 */
1860 --
1861 IF (l_debug <> -1) THEN
1862 rlm_core_sv.dlog(g_DEBUG,' v_MondayDate ', v_MondayDate);
1863 rlm_core_sv.dpop(g_SDEBUG,'value returned');
1864 END IF;
1865 --
1866 RETURN(v_MondayDate);
1867 --
1868 END find_monday_date;
1869
1870
1871 /*=============================================================================
1872
1873 FUNCTION NAME: valid_sdp_date
1874
1875 ==============================================================================*/
1876
1877 FUNCTION valid_sdp_date(
1878 x_Input IN rlm_ship_delivery_pattern_sv.t_InputRec,
1879 x_DailyPercent IN rlm_core_sv.t_NumberTable)
1880 RETURN BOOLEAN
1881 IS
1882 v_Valid BOOLEAN := FALSE;
1883 v_DayCount NUMBER := 1;
1884 x_message VARCHAR2(4000);
1885 BEGIN
1886 --
1887 IF (l_debug <> -1) THEN
1888 rlm_core_sv.dpush(g_SDEBUG,'valid_sdp_date');
1889 rlm_core_sv.dlog(g_DEBUG,'x_Date', x_Input.StartDateTime);
1890 --rlm_core_sv.dlog(g_DEBUG,'x_DailyPercent', x_DailyPercent);
1891 END IF;
1892 --
1893 WHILE ((v_Valid = FALSE) AND (v_DayCount<7)) LOOP
1894 --
1895 IF (l_debug <> -1) THEN
1896 rlm_core_sv.dlog(g_DEBUG, 'v_DayCount', v_DayCount);
1897 END IF;
1898 --
1899 IF ((to_number(to_char(x_Input.StartDateTime,'D'))=
1900 (v_DayCount)) AND (x_DailyPercent(v_DayCount)>0)) THEN
1901 --
1902 IF (l_debug <> -1) THEN
1903 rlm_core_sv.dlog(g_DEBUG,'v_Valid is', TRUE);
1904 END IF;
1905 --
1906 v_Valid := TRUE ;
1907 --
1908 END IF;
1909 --
1910 v_DayCount := v_DayCount +1;
1911 --
1912 END LOOP;
1913 --
1914 IF (l_debug <> -1) THEN
1915 rlm_core_sv.dlog(g_DEBUG,'v_Valid', v_Valid);
1916 rlm_core_sv.dpop(g_SDEBUG,'value returned');
1917 END IF;
1918 --
1919 RETURN(v_Valid);
1920 --
1921 END valid_sdp_date;
1922
1923 /*=============================================================================
1924
1925 FUNCTION NAME: previous_valid_sdp_date
1926
1927 ==============================================================================*/
1928
1929 FUNCTION previous_valid_sdp_date(
1930 x_Input IN rlm_ship_delivery_pattern_sv.t_InputRec,
1931 x_Date IN DATE,
1932 x_DailyPercent IN rlm_core_sv.t_NumberTable)
1933 RETURN DATE
1934 IS
1935 v_FOUND BOOLEAN := FALSE;
1936 v_DayOfTheWeek NUMBER;
1937 v_Count NUMBER;
1938 v_ReturnDate DATE;
1939 v_SubtractDays NUMBER :=1;
1940 x_message VARCHAR2(4000);
1941
1942 BEGIN
1943 --
1944 IF (l_debug <> -1) THEN
1945 rlm_core_sv.dpush(g_SDEBUG,'previous_valid_sdp_date');
1946 rlm_core_sv.dlog(g_DEBUG,'x_Date ', x_Date );
1947 END IF;
1948 --
1949 v_DayOfTheWeek := to_number(to_char(x_Date,'D'));
1950 --
1951 IF (l_debug <> -1) THEN
1952 rlm_core_sv.dlog(g_DEBUG,'v_DayOfTheWeek', v_DayOfTheWeek);
1953 END IF;
1954 --
1955 IF (v_DayOfTheWeek = 1) THEN
1956 v_Count := 7;
1957 ELSE
1958 v_Count := v_DayOfTheWeek - 1;
1959 END IF;
1960 --
1961 IF (l_debug <> -1) THEN
1962 rlm_core_sv.dlog(g_DEBUG,'v_Count', v_Count);
1963 END IF;
1964 --
1965 WHILE ((v_Count>0) AND (v_FOUND=FALSE)) LOOP
1966 --
1967 IF (x_DailyPercent(v_Count) > 0) THEN
1968 --
1969 IF (l_debug <> -1) THEN
1970 rlm_core_sv.dlog(g_DEBUG,'v_FOUND is ', TRUE);
1971 END IF;
1972 --
1973 v_FOUND := TRUE;
1974 v_ReturnDate := x_Date - v_SubtractDays;
1975 --
1976 IF (l_debug <> -1) THEN
1977 rlm_core_sv.dlog(g_DEBUG,'v_ReturnDate ', v_ReturnDate);
1978 END IF;
1979 --
1980 END IF;
1981 --
1982 v_Count := v_Count - 1;
1983 --
1984 IF (l_debug <> -1) THEN
1985 rlm_core_sv.dlog(g_DEBUG,'v_Count', v_Count);
1986 END IF;
1987 --
1988 v_SubtractDays := v_SubtractDays + 1;
1989 --
1990 IF (l_debug <> -1) THEN
1991 rlm_core_sv.dlog(g_DEBUG,'v_SubtractDays', v_SubtractDays);
1992 END IF;
1993 --
1994 END LOOP;
1995 --
1996 IF ((v_DayOfTheWeek <> 1)AND(v_FOUND=FALSE)) THEN
1997 v_Count := 7;
1998 --
1999 IF (l_debug <> -1) THEN
2000 rlm_core_sv.dlog(g_DEBUG,'v_Count', v_Count);
2001 END IF;
2002 --
2003 WHILE ( (v_Count>(v_DayOfTheWeek-1)) AND (v_FOUND=FALSE) ) LOOP
2004 --
2005 IF (x_DailyPercent(v_Count) > 0) THEN
2006 --
2007 IF (l_debug <> -1) THEN
2008 rlm_core_sv.dlog(g_DEBUG,'v_FOUND is', TRUE);
2009 END IF;
2010 --
2011 v_FOUND := TRUE;
2012 v_ReturnDate := x_Date - v_SubtractDays;
2013 --
2014 IF (l_debug <> -1) THEN
2015 rlm_core_sv.dlog(g_DEBUG,'v_ReturnDate ', v_ReturnDate);
2016 END IF;
2017 --
2018 END IF;
2019 --
2020 v_Count := v_Count - 1;
2021 --
2022 IF (l_debug <> -1) THEN
2023 rlm_core_sv.dlog(g_DEBUG,'v_Count', v_Count);
2024 END IF;
2025 --
2026 v_SubtractDays := v_SubtractDays + 1;
2027 --
2028 IF (l_debug <> -1) THEN
2029 rlm_core_sv.dlog(g_DEBUG,'v_SubtractDays', v_SubtractDays);
2030 END IF;
2031 --
2032 END LOOP;
2033 --
2034 END IF;
2035 --
2036 IF (v_FOUND = FALSE) THEN
2037 --
2038 IF (l_debug <> -1) THEN
2039 rlm_core_sv.dlog(g_DEBUG,'v_FOUND is ', FALSE);
2040 rlm_core_sv.dpop(g_SDEBUG,'unsuccessful');
2041 END IF;
2042 --
2043 RETURN(x_Date - 1);
2044 --
2045 ELSE
2046 --
2047 IF (l_debug <> -1) THEN
2048 rlm_core_sv.dlog(g_DEBUG,'v_ReturnDate ', v_ReturnDate);
2049 rlm_core_sv.dpop(g_SDEBUG,'value returned successfully');
2050 END IF;
2051 --
2052 RETURN(v_ReturnDate);
2053 --
2054 END IF;
2055 --
2056 EXCEPTION
2057 --
2058 WHEN OTHERS THEN
2059 --
2060 IF (l_debug <> -1) THEN
2061 rlm_core_sv.dpop(g_SDEBUG,'previous_valid_sdp_date '||'EXCEPTION:
2062 '||SUBSTR(SQLERRM,1,200));
2063 END IF;
2064 --
2065 raise;
2066 --
2067 END previous_valid_sdp_date;
2068
2069
2070 /*=============================================================================
2071
2072 FUNCTION NAME: check_receive_date
2073
2074 ==============================================================================*/
2075
2076 FUNCTION check_receive_date(x_Input IN rlm_ship_delivery_pattern_sv.t_InputRec,
2077 x_ReceiveDate IN DATE)
2078 RETURN BOOLEAN
2079 IS
2080 --
2081 v_WorkingDay BOOLEAN := TRUE;
2082 v_ErrCode NUMBER;
2083 v_ErrMesg VARCHAR2(100);
2084 x_message VARCHAR2(4000);
2085 --
2086 BEGIN
2087 --
2088 IF (l_debug <> -1) THEN
2089 rlm_core_sv.dpush(g_SDEBUG,'check_receive_date');
2090 rlm_core_sv.dlog(g_DEBUG,'x_ShipToAddressId ',x_Input.ShipToAddressId);
2091 rlm_core_sv.dlog(g_DEBUG,'x_ReceiveDate ',x_ReceiveDate);
2092 rlm_core_sv.dlog(g_DEBUG,'x_CalCode',x_Input.customer_rcv_calendar_cd);
2093 END IF;
2094 --
2095 -- Bug 3733396 : Call BOM API only if we have a valid RECEIVING calendar
2096 --
2097 IF x_Input.customer_rcv_calendar_cd IS NOT NULL THEN
2098 --
2099 bom_calendar_api_bk.check_working_day(x_Input.customer_rcv_calendar_cd,
2100 x_ReceiveDate,
2101 v_WorkingDay,
2102 v_ErrCode,
2103 v_ErrMesg);
2104 --
2105 END IF;
2106 --
2107 if(v_WorkingDay = TRUE) then
2108 --
2109 IF (l_debug <> -1) THEN
2110 rlm_core_sv.dpop(g_SDEBUG,'returns TRUE');
2111 END IF;
2112 --
2113 return(TRUE);
2114 --
2115 else
2116 --
2117 IF (l_debug <> -1) THEN
2118 rlm_core_sv.dpop(g_SDEBUG,'returns FALSE');
2119 END IF;
2120 --
2121 return(FALSE);
2122 --
2123 end if;
2124
2125 END check_receive_date;
2126
2127 /*=============================================================================
2128
2129 PROCEDURE NAME: determine_receive_date
2130
2131 ==============================================================================*/
2132
2133 PROCEDURE determine_receive_date(x_Input IN RLM_SHIP_DELIVERY_PATTERN_SV.t_InputRec,
2134 x_DailyPercent IN rlm_core_sv.t_NumberTable,
2135 x_ReceiveDate IN OUT NOCOPY DATE)
2136 IS
2137
2138 BEGIN
2139 --
2140 IF (l_debug <> -1) THEN
2141 rlm_core_sv.dpush(g_SDEBUG,'determine_receive_date');
2142 END IF;
2143 --
2144 -- rlm_core_sv.dlog(g_DEBUG,' x_DailyPercent ',x_DailyPercent);
2145 --
2146 x_ReceiveDate := RLM_TPA_SV.previous_valid_sdp_date( x_Input,
2147 x_ReceiveDate,x_DailyPercent);
2148 --
2149 IF (l_debug <> -1) THEN
2150 rlm_core_sv.dlog(g_DEBUG,' x_ReceiveDate ',x_ReceiveDate);
2151 rlm_core_sv.dpop(g_SDEBUG,'value returned');
2152 END IF;
2153 --
2154 END determine_receive_date;
2155
2156 /*=============================================================================
2157
2158 PROCEDURE NAME: apply_lead_time
2159
2160 ==============================================================================*/
2161
2162 PROCEDURE apply_lead_time (x_LeadTime IN t_LeadTimeRec,
2163 x_QuantityDateRec IN OUT NOCOPY t_OutputRec,
2164 x_LeadType IN VARCHAR2)
2165 IS
2166
2167 BEGIN
2168 --
2169 IF (l_debug <> -1) THEN
2170 rlm_core_sv.dpush(g_SDEBUG,'apply_lead_time');
2171 rlm_core_sv.dlog(g_DEBUG,' x_LeadTime.Time ',x_LeadTime.Time);
2172 rlm_core_sv.dlog(g_DEBUG,' x_LeadTime.Uom ',x_LeadTime.Uom);
2173 rlm_core_sv.dlog(g_DEBUG,' x_QuantityDateRec.PlannedShipmentDate ',x_QuantityDateRec.PlannedShipmentDate);
2174 rlm_core_sv.dlog(g_DEBUG,' x_QuantityDateRec.PlannedReceiveDate ',x_QuantityDateRec.PlannedReceiveDate);
2175 rlm_core_sv.dlog(g_DEBUG,' x_LeadType ',x_LeadType);
2176 END IF;
2177 --
2178 IF (x_LeadType = 'SUBTRACT') THEN
2179 IF (x_LeadTime.Uom = 'DAY') THEN
2180 x_QuantityDateRec.PlannedShipmentDate := x_QuantityDateRec.PlannedReceiveDate - x_LeadTime.Time;
2181 ELSIF (x_LeadTime.Uom = 'HR') THEN
2182 x_QuantityDateRec.PlannedShipmentDate := x_QuantityDateRec.PlannedReceiveDate - (x_LeadTime.Time/24);
2183 END IF;
2184 ELSIF (x_LeadType = 'ADD') THEN
2185 IF (x_LeadTime.Uom = 'DAY') THEN
2186 x_QuantityDateRec.PlannedReceiveDate := x_QuantityDateRec.PlannedShipmentDate + x_LeadTime.Time;
2187 ELSIF (x_LeadTime.Uom = 'HR') THEN
2188 x_QuantityDateRec.PlannedReceiveDate := x_QuantityDateRec.PlannedReceiveDate - (x_LeadTime.Time/24);
2189 END IF;
2190 END IF;
2191 --
2192 IF (l_debug <> -1) THEN
2193 rlm_core_sv.dlog(g_DEBUG,' x_QuantityDateRec.PlannedShipmentDate ',x_QuantityDateRec.PlannedShipmentDate);
2194 rlm_core_sv.dlog(g_DEBUG,' x_QuantityDateRec.PlannedReceiveDate ',x_QuantityDateRec.PlannedReceiveDate);
2195 rlm_core_sv.dpop(g_SDEBUG,'successful');
2196 END IF;
2197 --
2198 END apply_lead_time;
2199
2200 /*=============================================================================
2201
2202 FUNCTION NAME: check_send_date
2203
2204 ==============================================================================*/
2205
2206 FUNCTION check_send_date (x_Input IN RLM_SHIP_DELIVERY_PATTERN_SV.t_InputRec,
2207 x_ShipmentDate IN DATE)
2208 RETURN BOOLEAN
2209 IS
2210 --
2211 v_WorkingDay BOOLEAN := FALSE;
2212 v_ErrCode NUMBER;
2213 v_ErrMesg VARCHAR2(100);
2214 x_message VARCHAR2(4000);
2215 --
2216
2217 BEGIN
2218 --
2219 IF (l_debug <> -1) THEN
2220 rlm_core_sv.dpush(g_SDEBUG,'check_send_date');
2221 rlm_core_sv.dlog(g_DEBUG,' x_ShipFromOrgId ',x_Input.ShipFromOrgId);
2222 rlm_core_sv.dlog(g_DEBUG,' x_ShipmentDate ',x_ShipmentDate);
2223 rlm_core_sv.dlog(g_DEBUG,' x_CalCode ',x_Input.supplier_shp_calendar_cd);
2224 END IF;
2225 --
2226 bom_calendar_api_bk.check_working_day(x_Input.supplier_shp_calendar_cd,
2227 x_ShipmentDate,
2228 v_WorkingDay,
2229 v_ErrCode,
2230 v_ErrMesg);
2231 --
2232 if(v_WorkingDay = TRUE) then
2233 --
2234 IF (l_debug <> -1) THEN
2235 rlm_core_sv.dpop(g_SDEBUG,'returns TRUE');
2236 END IF;
2237 --
2238 return(TRUE);
2239 --
2240 else
2241 --
2242 IF (l_debug <> -1) THEN
2243 rlm_core_sv.dpop(g_SDEBUG,'returns FALSE');
2244 END IF;
2245 --
2246 return(FALSE);
2247 --
2248 end if;
2249
2250 END check_send_date;
2251
2252 /*=============================================================================
2253
2254 PROCEDURE NAME: determine_send_date
2255
2256 ==============================================================================*/
2257
2258 PROCEDURE determine_send_date(
2259 x_Input IN rlm_ship_delivery_pattern_sv.t_InputRec,
2260 x_DailyPercent IN rlm_core_sv.t_NumberTable,
2261 x_ShipmentDate IN OUT NOCOPY DATE)
2262 IS
2263
2264 BEGIN
2265 --
2266 IF (l_debug <> -1) THEN
2267 rlm_core_sv.dpush(g_SDEBUG,'determine_send_date');
2268 rlm_core_sv.dlog(g_DEBUG,' x_ShipmentDate ',x_ShipmentDate);
2269 END IF;
2270 --
2271 x_ShipmentDate := RLM_TPA_SV.previous_valid_sdp_date(x_Input,x_ShipmentDate,x_DailyPercent);
2272 /*IF Shipping calendar says it is not possible to ship then backup date to
2273 the previous valid date according to SDP*/
2274 null;
2275 --
2276 IF (l_debug <> -1) THEN
2277 rlm_core_sv.dpop(g_SDEBUG,'does nothing');
2278 END IF;
2279 --
2280 EXCEPTION
2281 WHEN OTHERS THEN
2282 --
2283 IF (l_debug <> -1) THEN
2284 rlm_core_sv.dpop(g_SDEBUG,'determine_send_date '||'EXCEPTION:
2285 '||SUBSTR(SQLERRM,1,200));
2286 END IF;
2287 --
2288 raise;
2289 END determine_send_date;
2290
2291 /*=============================================================================
2292
2293 PROCEDURE NAME: get_err_msg
2294
2295 ==============================================================================*/
2296
2297 PROCEDURE get_err_message (
2298 x_ErrorMessage IN VARCHAR2,
2299 x_ErrorMessageName IN VARCHAR2,
2300 x_ErrorType IN NUMBER,
2301 x_ErrMsgTab IN OUT NOCOPY t_ErrorMsgTable)
2302 IS
2303 --
2304 v_index BINARY_INTEGER;
2305 --
2306 BEGIN
2307 --
2308 IF (l_debug <> -1) THEN
2309 rlm_core_sv.dpush(g_SDEBUG,'get_err_message');
2310 END IF;
2311 --
2312 v_index := nvl(x_ErrMsgTab.LAST, 0) + 1;
2313 --
2314 IF (l_debug <> -1) THEN
2315 rlm_core_sv.dlog(g_DEBUG,' Index ',v_index);
2316 rlm_core_sv.dlog(g_DEBUG,' x_ErrorMessage ',x_ErrorMessage);
2317 END IF;
2318 --
2319 x_ErrMsgTab(v_index).ErrMessage := x_ErrorMessage;
2320 x_ErrMsgTab(v_index).ErrMessageName := x_ErrorMessageName;
2321 x_ErrMsgTab(v_index).ErrType := x_ErrorType;
2322 --
2323 IF (l_debug <> -1) THEN
2324 rlm_core_sv.dpop(g_SDEBUG);
2325 END IF;
2326 --
2327 END get_err_message;
2328
2329 /*===========================================================================
2330
2331 FUNCTION NAME: GetTPContext
2332
2333 ===========================================================================*/
2334 PROCEDURE GetTPContext(
2335 x_Input IN rlm_ship_delivery_pattern_sv.t_InputRec,
2336 x_customer_number OUT NOCOPY VARCHAR2,
2337 x_ship_to_ece_locn_code OUT NOCOPY VARCHAR2,
2338 x_bill_to_ece_locn_code OUT NOCOPY VARCHAR2,
2339 x_inter_ship_to_ece_locn_code OUT NOCOPY VARCHAR2,
2340 x_tp_group_code OUT NOCOPY VARCHAR2)
2341 IS
2342 --
2343 v_Progress VARCHAR2(3) := '010';
2344 --
2345 -- Following cursor is changed as per TCA obsolescence project.
2346 CURSOR C is
2347 SELECT ETG.TP_GROUP_CODE
2348 INTO x_tp_group_code
2349 FROM ECE_TP_GROUP ETG,
2350 ECE_TP_HEADERS ETH,
2351 HZ_CUST_ACCT_SITES ACCT_SITE
2352 WHERE ETG.TP_GROUP_ID = ETH.TP_GROUP_ID
2353 and ETH.TP_HEADER_ID = ACCT_SITE.TP_HEADER_ID
2354 and ACCT_SITE.CUST_ACCOUNT_ID = x_Input.ShiptoCustomerId
2355 and ACCT_SITE.CUST_ACCT_SITE_ID = x_Input.ShipToAddressId;
2356
2357
2358 --
2359 BEGIN
2360 --
2361 IF (l_debug <> -1) THEN
2362 rlm_core_sv.dpush(g_SDEBUG,'GetTPContext');
2363 rlm_core_sv.dlog(g_DEBUG,'customer_id', x_Input.CustomerId);
2364 rlm_core_sv.dlog(g_DEBUG,'ship_to_address_id', x_Input.ShipToAddressId);
2365 rlm_core_sv.dlog(g_DEBUG,'bill_to_address_id', x_Input.BillToAddressId);
2366 rlm_core_sv.dlog(g_DEBUG,'INtShipToAddressId',
2367 x_Input.IntShipToAddressId);
2368 END IF;
2369 --
2370 BEGIN
2371 --
2372 -- Following query is changed as per TCA obsolescence project.
2373 SELECT ece_tp_location_code
2374 INTO x_ship_to_ece_locn_code
2375 FROM HZ_CUST_ACCT_SITES ACCT_SITE
2376 WHERE ACCT_SITE.CUST_ACCT_SITE_ID = x_Input.ShipToAddressId;
2377 --
2378 EXCEPTION
2379 --
2380 WHEN NO_DATA_FOUND THEN
2381 x_ship_to_ece_locn_code := NULL;
2382 WHEN OTHERS THEN
2383 --
2384 IF (l_debug <> -1) THEN
2385 rlm_core_sv.dlog(g_DEBUG,'EXCEPTION: '||SUBSTR(SQLERRM,1,200));
2386 END IF;
2387 --
2388 raise;
2389 END;
2390
2391 BEGIN
2392 --
2393 -- Following query is changed as per TCA obsolescence project.
2394 SELECT ece_tp_location_code
2395 INTO x_bill_to_ece_locn_code
2396 FROM HZ_CUST_ACCT_SITES ACCT_SITE
2397 WHERE ACCT_SITE.CUST_ACCT_SITE_ID = x_Input.BillToAddressId;
2398 --
2399 EXCEPTION
2400 --
2401 WHEN NO_DATA_FOUND THEN
2402 x_bill_to_ece_locn_code := NULL;
2403 WHEN OTHERS THEN
2404 --
2405 IF (l_debug <> -1) THEN
2406 rlm_core_sv.dlog(g_DEBUG,'EXCEPTION: '||SUBSTR(SQLERRM,1,200));
2407 END IF;
2408 --
2409 raise;
2410 --
2411 END;
2412
2413 BEGIN
2414 --
2415 -- Following query is changed as per TCA obsolescence project.
2416 SELECT ece_tp_location_code
2417 INTO x_inter_ship_to_ece_locn_code
2418 FROM HZ_CUST_ACCT_SITES ACCT_SITE
2419 WHERE ACCT_SITE.CUST_ACCT_SITE_ID = x_Input.IntShipToAddressId;
2420 --
2421 EXCEPTION
2422 --
2423 WHEN NO_DATA_FOUND THEN
2424 x_inter_ship_to_ece_locn_code := NULL;
2425 WHEN OTHERS THEN
2426 --
2427 IF (l_debug <> -1) THEN
2428 rlm_core_sv.dlog(g_DEBUG,'EXCEPTION: '||SUBSTR(SQLERRM,1,200));
2429 END IF;
2430 --
2431 raise;
2432 --
2433 END;
2434 --
2435 IF x_Input.CustomerId is NOT NULL THEN
2436 --
2437 BEGIN
2438 --
2439 -- Following query is changed as per TCA obsolescence project.
2440 SELECT account_number
2441 INTO x_customer_number
2442 FROM HZ_CUST_ACCOUNTS CUST_ACCT
2443 WHERE CUST_ACCT.CUST_ACCOUNT_ID = x_Input.CustomerId;
2444 --
2445 EXCEPTION
2446 --
2447 WHEN NO_DATA_FOUND THEN
2448 x_customer_number := NULL;
2449 WHEN OTHERS THEN
2450 --
2451 IF (l_debug <> -1) THEN
2452 rlm_core_sv.dlog(g_DEBUG,'EXCEPTION: '||SUBSTR(SQLERRM,1,200));
2453 END IF;
2454 --
2455 raise;
2456 END;
2457 --
2458 OPEN C;
2459 FETCH C INTO x_tp_Group_code;
2460 IF C%NOTFOUND THEN
2461 raise NO_DATA_FOUND;
2462 END IF;
2463 CLOSE C;
2464 --
2465 END IF;
2466 --
2467 IF (l_debug <> -1) THEN
2468 rlm_core_sv.dlog(g_DEBUG, 'customer_number', x_customer_number);
2469 rlm_core_sv.dlog(g_DEBUG,'x_ship_to_ece_locn_code', x_ship_to_ece_locn_code);
2470 rlm_core_sv.dlog(g_DEBUG,'x_bill_to_ece_locn_code', x_bill_to_ece_locn_code);
2471 rlm_core_sv.dlog(g_DEBUG,'x_inter_ship_to_ece_locn_code', x_inter_ship_to_ece_locn_code);
2472 rlm_core_sv.dlog(g_DEBUG,'x_tp_Group_code', x_tp_Group_code);
2473 rlm_core_sv.dpop(g_SDEBUG);
2474 END IF;
2475 --
2476 EXCEPTION
2477 --
2478 WHEN NO_DATA_FOUND THEN
2479 --
2480 x_tp_Group_code := NULL;
2481 --
2482 IF (l_debug <> -1) THEN
2483 rlm_core_sv.dlog(g_DEBUG, 'No data found for x_tp_Group_code');
2484 rlm_core_sv.dpop(g_SDEBUG);
2485 END IF;
2486 --
2487 WHEN OTHERS THEN
2488 --
2489 rlm_message_sv.sql_error('rlm_ship_delivery_pattern_sv.GetTPContext',v_Progress);
2490 --
2491 IF (l_debug <> -1) THEN
2492 rlm_core_sv.dpop(g_SDEBUG,'EXCEPTION: '||SUBSTR(SQLERRM,1,200));
2493 END IF;
2494 --
2495 raise;
2496
2497 END GetTPContext;
2498
2499 END rlm_ship_delivery_pattern_sv;