1 PACKAGE BODY WSH_CAL_ASG_VALIDATIONS AS
2 -- $Header: WSHCAVLB.pls 120.2.12010000.3 2009/11/02 12:41:58 skanduku ship $
3
4 /*+=======================================================================+
5 --| Copyright (c) 1998 Oracle Corporation |
6 --| Redwood Shores, CA, USA |
7 --| All rights reserved. |
8 --+=======================================================================+
9 --| FILENAME |
10 --| WSHCAVLB.pls |
11 --| |
12 --| DESCRIPTION |
13 --| Validation API for transportation calendar |
14 --| |
15 --| HISTORY |
16 --| 06/29/99 dmay Created |
17 --+======================================================================*/
18 --===================
19 -- CONSTANTS
20 --===================
21 G_PKG_NAME CONSTANT VARCHAR2(30) := 'WSH_CAL_ASG_VALIDATIONS';
22 -- add your constants here if any
23
24 --===================
25 -- PUBLIC VARIABLES
26 --===================
27 -- add your public global variables here if any
28 wsh_missing_mandatory_attr EXCEPTION;
29 wsh_bad_parameter EXCEPTION;
30
31 --===================
32 -- PROCEDURES
33 --===================
34 --First, private procedure declarations
35 --========================================================================
36 -- PROCEDURE : Find_Valid_Date GROUP
37 -- PARAMETERS:
38 -- p_input_date Date to validate
39 -- p_calendar_type Calendar type -- SHIPPING, RECEIVING, or
40 -- CARRIER
41 -- p_location_id Location ID (optional)
42 -- p_vendor_site_id Vendor Site ID (optional)
43 -- p_customer_site_use_id Customer Site Use ID (optional)
44 -- p_freight_code Carrier code (optional)
45 -- p_freight_org_id Carrier Organization ID (optional)
46 -- x_suggest_date Suggested valid date
47 -- x_success Did we find a date?
48 -- p_threshold Number of dates to try before failure
49 -- p_which_way FORWARD or BACK -- which way to search
50 -- p_time_matters Is time important for this date?
51 -- VERSION : current version 1.0
52 -- initial version 1.0
53 -- COMMENT : Searches for a valid date near a given date by checking
54 -- nearby dates against a given calendar. Decrement if
55 -- finding ship date, increment if finding receive date
56 --========================================================================
57 PROCEDURE Find_Valid_Date
58 ( p_input_date IN DATE
59 , p_calendar_type IN VARCHAR2
60 , p_assoc_type IN VARCHAR2
61 , p_location_id IN NUMBER
62 , p_vendor_site_id IN NUMBER
63 , p_customer_site_use_id IN NUMBER
64 , p_freight_code IN VARCHAR2
65 , p_freight_org_id IN NUMBER
66 , x_suggest_date OUT NOCOPY DATE
67 , x_success OUT NOCOPY BOOLEAN
68 , p_threshold IN NUMBER
69 , p_which_way IN VARCHAR2
70 , p_time_matters IN BOOLEAN
71 );
72 --========================================================================
73 -- PROCEDURE : Single_Date PRIVATE
74 -- PARAMETERS:
75 -- p_date Date to validate
76 -- p_calendar_type Calendar type -- SHIPPING, RECEIVING, or
77 -- CARRIER
78 -- p_assoc_type Type of site
82 -- p_freight_code Carrier code (optional)
79 -- p_location_id Location ID (optional)
80 -- p_vendor_site_id Vendor Site ID (optional)
81 -- p_customer_site_use_id Customer Site Use ID (optional)
83 -- p_freight_org_id Carrier Organization ID (optional)
84 -- p_date_is_valid Return information
85 -- p_time_matters Is time important for this date?
86 -- VERSION : current version 1.0
87 -- initial version 1.0
88 -- COMMENT : Validates a shipping or receiving date against a shipping
89 -- calendar and against a carrier calendar
90 --========================================================================
91 PROCEDURE Single_Date
92 ( p_date IN DATE
93 , p_calendar_type IN VARCHAR2
94 , p_assoc_type IN VARCHAR2
95 , p_location_id IN NUMBER
96 , p_vendor_site_id IN NUMBER
97 , p_customer_site_use_id IN NUMBER
98 , p_freight_code IN VARCHAR2
99 , p_freight_org_id IN NUMBER
100 , p_date_is_valid OUT NOCOPY BOOLEAN
101 , p_time_matters IN BOOLEAN
102 );
103 --Now the package body
104
105 --========================================================================
106 -- PROCEDURE : Get_Calendar PUBLIC
107 -- PARAMETERS: p_api_version_number known api versionerror buffer
108 -- p_init_msg_list FND_API.G_TRUE to reset list
109 -- x_return_status return status
110 -- x_msg_count number of messages in the list
111 -- x_msg_data text of messages
112 -- p_entity_type Entity - 'CUSTOMER','ORG','VENDOR'
113 -- p_entity_id Entity Id - Customer_id, vendor_id, org_id
114 -- p_location_id Location ID (optional)
115 -- x_calendar_code Return calendar code
116 -- VERSION : current version 1.0
117 -- initial version 1.0
118 -- COMMENT : Returns a calendar code for a given customer,vendor or org and
119 -- location combination. If location is not specified then this
120 -- returns the default calendar code
121 --========================================================================
122
123 PROCEDURE Get_Calendar
124 ( p_api_version_number IN NUMBER
125 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
126 , x_return_status OUT NOCOPY VARCHAR2
127 , x_msg_count OUT NOCOPY NUMBER
128 , x_msg_data OUT NOCOPY VARCHAR2
129 , p_entity_type IN VARCHAR2
130 , p_entity_id IN NUMBER
131 , p_location_id IN NUMBER
132 , x_calendar_code OUT NOCOPY VARCHAR2
133 ) IS
134 l_api_version_number CONSTANT NUMBER := 1.0;
135 l_api_name CONSTANT VARCHAR2(30):= 'Get_Calendar';
136 -- <insert here your local variables declaration>
137 -- order by location_id is to ensure that the default calendar entry is
138 -- selected last.
139
140 CURSOR customer_cal IS
141 SELECT calendar_code
142 FROM wsh_calendar_assignments
143 WHERE customer_id = p_entity_id AND
144 calendar_type = 'RECEIVING' AND
145 nvl(location_id, nvl(p_location_id, -1)) = nvl(p_location_id, -1) AND
146 enabled_flag = 'Y'
147 ORDER BY location_id;
148
149 CURSOR org_cal IS
150 SELECT calendar_code
151 FROM wsh_calendar_assignments
152 WHERE organization_id = p_entity_id AND
153 calendar_type = 'SHIPPING' AND
154 nvl(location_id, nvl(p_location_id, -1)) = nvl(p_location_id,-1) AND
155 enabled_flag = 'Y'
156 ORDER BY location_id;
157
158 --
159 l_debug_on BOOLEAN;
160 --
161 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_CALENDAR';
162 --
163 BEGIN
164 -- Standard call to check for call compatibility
165 --
166 -- Debug Statements
167 --
168 --
169 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
170 --
171 IF l_debug_on IS NULL
172 THEN
173 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
174 END IF;
175 --
176 IF l_debug_on THEN
177 WSH_DEBUG_SV.push(l_module_name);
178 --
179 WSH_DEBUG_SV.log(l_module_name,'P_API_VERSION_NUMBER',P_API_VERSION_NUMBER);
180 WSH_DEBUG_SV.log(l_module_name,'P_INIT_MSG_LIST',P_INIT_MSG_LIST);
181 WSH_DEBUG_SV.log(l_module_name,'P_ENTITY_TYPE',P_ENTITY_TYPE);
182 WSH_DEBUG_SV.log(l_module_name,'P_ENTITY_ID',P_ENTITY_ID);
183 WSH_DEBUG_SV.log(l_module_name,'P_LOCATION_ID',P_LOCATION_ID);
184 END IF;
185 --
186 IF NOT FND_API.Compatible_API_Call
187 ( l_api_version_number
188 , p_api_version_number
189 , l_api_name
190 , G_PKG_NAME
191 )
192 THEN
193 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
194 END IF;
195
196 -- Initialize message stack if required
197 IF FND_API.to_Boolean(p_init_msg_list)
198 THEN
199 FND_MSG_PUB.initialize;
200 END IF;
201
202 -- BEGIN PROCEDURE LOGIC
203
204 IF (p_entity_type = 'CUSTOMER') THEN
205 OPEN customer_cal;
206 FETCH customer_cal INTO x_calendar_code;
207 CLOSE customer_cal;
208 ELSIF (p_entity_type = 'ORG') THEN
209 OPEN org_cal;
210 FETCH org_cal INTO x_calendar_code;
211 CLOSE org_cal;
212 END IF;
213
214 -- END PROCEDURE LOGIC
215 -- report success
216 x_return_status := FND_API.G_RET_STS_SUCCESS;
217 FND_MSG_PUB.Count_And_Get
218 ( p_count => x_msg_count
219 , p_data => x_msg_data
220 );
224 IF l_debug_on THEN
221 --
222 -- Debug Statements
223 --
225 WSH_DEBUG_SV.pop(l_module_name);
226 END IF;
227 --
228 EXCEPTION
229 WHEN FND_API.G_EXC_ERROR THEN
230 x_return_status := FND_API.G_RET_STS_ERROR;
231 -- Get message count and data
232 FND_MSG_PUB.Count_And_Get
233 ( p_count => x_msg_count
234 , p_data => x_msg_data
235 );
236
237 --
238 -- Debug Statements
239 --
240 IF l_debug_on THEN
241 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
242 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
243 END IF;
244 --
245 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
246 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
247 -- Get message count and data
248 FND_MSG_PUB.Count_And_Get
249 ( p_count => x_msg_count
250 , p_data => x_msg_data
251 );
252
253 --
254 -- Debug Statements
255 --
256 IF l_debug_on THEN
257 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_UNEXPECTED_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
258 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
259 END IF;
260 --
261 WHEN OTHERS THEN
262 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
263 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
264 THEN
265 FND_MSG_PUB.Add_Exc_Msg
266 ( G_PKG_NAME
267 , '_x_'
268 );
269 END IF;
270 -- Get message count and data
271 FND_MSG_PUB.Count_And_Get
272 ( p_count => x_msg_count
273 , p_data => x_msg_data
274 );
275 --
276 -- Debug Statements
277 --
278 IF l_debug_on THEN
279 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
280 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
281 END IF;
282 --
283 END Get_Calendar;
284
285 --========================================================================
286 -- PROCEDURE : Transport_Dates PUBLIC
287 -- PARAMETERS: p_api_version_number known api versionerror buffer
288 -- p_init_msg_list FND_API.G_TRUE to reset list
289 -- x_return_status return status
290 -- x_msg_count number of messages in the list
291 -- x_msg_data text of messages
292 -- p_priority 'SHIP' or 'RECEIVE': which is
293 -- more important
294 -- p_ship_date Date to validate
295 -- p_ship_assoc_type Association type for ship location
296 -- p_ship_location_id Location ID (optional)
297 -- p_ship_vendor_site_id Vendor Site ID (optional)
298 -- p_ship_customer_site_use_id Customer Site Use ID (optional)
299 -- p_ship_time_matters Should we care about ship time?
300 -- p_freight_code Carrier code (optional)
301 -- p_freight_org_id Carrier Organization ID (optional)
302 -- p_receive_date Date to validate
303 -- p_receive_assoc_type Association type for ship location
304 -- p_receive_location_id Location ID (optional)
305 -- p_receive_vendor_site_id Vendor Site ID (optional)
306 -- p_receive_customer_site_use_id Customer Site Use ID (optional)
307 -- p_receive_time_matters Should we care about receive time?
308 -- x_return code Return code
309 -- x_suggest_ship_date Ship date suggestion
310 -- x_suggest_receive_date Receiving date suggestion
311 -- p_primary_threshold Threshold for most important date
312 -- p_secondary_threshold Threshold for least important date
313 -- VERSION : current version 1.0
314 -- initial version 1.0
315 -- COMMENT : Validates a shipping or receiving date, or both, against a
316 -- transportation calendar and against a carrier calendar
317 --Similar in structure to the existing Bill of Materials Workday Calendar,
318 --the Transportation Calendar defines the valid shipping days for a location,
319 --supplier, customer, or carrier, and consists of a repeating pattern of days
320 --on and days off and exceptions to that pattern. Shifts associated with
321 --these calendars determine specific times during the day when material may be
322 --shipped or received.
323 -- This routine accepts a shipping date, or a receiving date, or both.
324 --Whatever dates it receives it validates against calendars, also provided as
325 --parameters. If a carrier and carrier calendar are specified the date(s) are
326 --validated against that calendar as well. If both ship and receive dates are
327 --provided, the user must also specify which date is more important, the
328 --ship or receive date. The more important date is validated first. Dates may
329 --slip within a user-provided tolerance: ship dates slip back, receive dates
330 --slip forward. The user may also specify whether time of day is important.
331 --If so, dates will only validate if they are valid for that specific time.
332 -- Value of parameter x_return_code indicates the following:
333 -- 0 Complete success
334 -- 1 dates slid, within tolerance
335 -- 2 lead time had to expand
336 -- 3 carrier calendar fails
337 -- 4 secondary date out of tolerance
338 -- 5 primary date out of tolerance
339 -- 6 error condition -- bad parameters
343 --========================================================================
340 -- Higher-numbered error conditions take precedence. For
341 -- instance, if the primary date is out of tolerance AND
342 -- a carrier calendar fails, return code 5 is generated
344 PROCEDURE Transport_Dates
345 ( p_api_version_number IN NUMBER
346 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
347 , x_return_status OUT NOCOPY VARCHAR2
348 , x_msg_count OUT NOCOPY NUMBER
349 , x_msg_data OUT NOCOPY VARCHAR2
350 , p_priority IN VARCHAR2
351 , p_ship_date IN DATE
352 , p_ship_assoc_type IN VARCHAR2
353 , p_ship_location_id IN NUMBER := NULL
354 , p_ship_vendor_site_id IN NUMBER := NULL
355 , p_ship_customer_site_use_id IN NUMBER := NULL
356 , p_ship_time_matters IN BOOLEAN
357 , p_freight_code IN VARCHAR2
358 , p_freight_org_id IN NUMBER
359 , p_receive_date IN DATE
360 , p_receive_assoc_type IN VARCHAR2
361 , p_receive_location_id IN NUMBER := NULL
362 , p_receive_vendor_site_id IN NUMBER := NULL
363 , p_receive_customer_site_use_id IN NUMBER := NULL
364 , p_receive_time_matters IN BOOLEAN
365 , x_return_code OUT NOCOPY NUMBER
366 , x_suggest_ship_date OUT NOCOPY DATE
367 , x_suggest_receive_date OUT NOCOPY DATE
368 , p_primary_threshold IN NUMBER
369 , p_secondary_threshold IN NUMBER
370 ) IS
371 l_api_version_number CONSTANT NUMBER := 1.0;
372 l_api_name CONSTANT VARCHAR2(30):= 'Transport_Dates';
373 l_found_good_date BOOLEAN;
374 l_lead_time NUMBER;
375 --Added for the bug 8567091
376 l_primary_threshold NUMBER;
377 l_suggest_ship_date DATE;
378
379 -- <insert here your local variables declaration>
380 --
381 l_debug_on BOOLEAN;
382 --
383 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'TRANSPORT_DATES';
384 --
385 BEGIN
386 -- Standard call to check for call compatibility
387 --
388 -- Debug Statements
389 --
390 --
391 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
392 --
393 IF l_debug_on IS NULL
394 THEN
395 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
396 END IF;
397 --
398 IF l_debug_on THEN
399 WSH_DEBUG_SV.push(l_module_name);
400 --
401 WSH_DEBUG_SV.log(l_module_name,'P_API_VERSION_NUMBER',P_API_VERSION_NUMBER);
402 WSH_DEBUG_SV.log(l_module_name,'P_INIT_MSG_LIST',P_INIT_MSG_LIST);
403 WSH_DEBUG_SV.log(l_module_name,'P_PRIORITY',P_PRIORITY);
404 WSH_DEBUG_SV.log(l_module_name,'P_SHIP_DATE',P_SHIP_DATE);
405 WSH_DEBUG_SV.log(l_module_name,'P_SHIP_ASSOC_TYPE',P_SHIP_ASSOC_TYPE);
406 WSH_DEBUG_SV.log(l_module_name,'P_SHIP_LOCATION_ID',P_SHIP_LOCATION_ID);
407 WSH_DEBUG_SV.log(l_module_name,'P_SHIP_VENDOR_SITE_ID',P_SHIP_VENDOR_SITE_ID);
408 WSH_DEBUG_SV.log(l_module_name,'P_SHIP_CUSTOMER_SITE_USE_ID',P_SHIP_CUSTOMER_SITE_USE_ID);
409 WSH_DEBUG_SV.log(l_module_name,'P_SHIP_TIME_MATTERS',P_SHIP_TIME_MATTERS);
410 WSH_DEBUG_SV.log(l_module_name,'P_FREIGHT_CODE',P_FREIGHT_CODE);
411 WSH_DEBUG_SV.log(l_module_name,'P_FREIGHT_ORG_ID',P_FREIGHT_ORG_ID);
412 WSH_DEBUG_SV.log(l_module_name,'P_RECEIVE_DATE',P_RECEIVE_DATE);
413 WSH_DEBUG_SV.log(l_module_name,'P_RECEIVE_ASSOC_TYPE',P_RECEIVE_ASSOC_TYPE);
414 WSH_DEBUG_SV.log(l_module_name,'P_RECEIVE_LOCATION_ID',P_RECEIVE_LOCATION_ID);
415 WSH_DEBUG_SV.log(l_module_name,'P_RECEIVE_VENDOR_SITE_ID',P_RECEIVE_VENDOR_SITE_ID);
416 WSH_DEBUG_SV.log(l_module_name,'P_RECEIVE_CUSTOMER_SITE_USE_ID',P_RECEIVE_CUSTOMER_SITE_USE_ID);
417 WSH_DEBUG_SV.log(l_module_name,'P_RECEIVE_TIME_MATTERS',P_RECEIVE_TIME_MATTERS);
418 WSH_DEBUG_SV.log(l_module_name,'P_PRIMARY_THRESHOLD',P_PRIMARY_THRESHOLD);
419 WSH_DEBUG_SV.log(l_module_name,'P_SECONDARY_THRESHOLD',P_SECONDARY_THRESHOLD);
420 END IF;
421 --
422 IF NOT FND_API.Compatible_API_Call
423 ( l_api_version_number
424 , p_api_version_number
425 , l_api_name
426 , G_PKG_NAME
427 )
428 THEN
429 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
430 END IF;
431
432 -- Initialize message stack if required
433 IF FND_API.to_Boolean(p_init_msg_list)
434 THEN
435 FND_MSG_PUB.initialize;
436 END IF;
437
438 -- BEGIN PROCEDURE LOGIC
439 x_suggest_ship_date := p_ship_date;
440 x_suggest_receive_date := p_receive_date;
441 x_return_status := FND_API.G_RET_STS_SUCCESS;
442
443 IF p_ship_date IS NULL THEN
444 IF p_receive_date IS NULL THEN
445 -- no dates to validate
446 RAISE wsh_missing_mandatory_attr;
447 ELSE
448 -- validate receive date
449 --
450 -- Debug Statements
451 --
452 IF l_debug_on THEN
453 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_CAL_ASG_VALIDATIONS.FIND_VALID_DATE',WSH_DEBUG_SV.C_PROC_LEVEL);
454 END IF;
455 --
456 WSH_CAL_ASG_VALIDATIONS.Find_Valid_Date
457 ( p_receive_date
458 , 'RECEIVING'
459 , p_receive_assoc_type
460 , p_receive_location_id
461 , p_receive_vendor_site_id
462 , p_receive_customer_site_use_id
463 , p_freight_code
464 , p_freight_org_id
465 , x_suggest_receive_date
466 , l_found_good_date
467 , p_primary_threshold
468 , 'BACK'
469 , p_receive_time_matters
470 );
471 IF l_found_good_date THEN
472 --
476 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_CAL_ASG_VALIDATIONS.SINGLE_DATE',WSH_DEBUG_SV.C_PROC_LEVEL);
473 -- Debug Statements
474 --
475 IF l_debug_on THEN
477 END IF;
478 --
479 WSH_CAL_ASG_VALIDATIONS.Single_Date(
480 x_suggest_receive_date
481 , 'CARRIER'
482 , 'CARRIER'
483 , p_receive_location_id
484 , p_receive_vendor_site_id
485 , p_receive_customer_site_use_id
486 , p_freight_code
487 , p_freight_org_id
488 , l_found_good_date
489 , p_receive_time_matters
490 );
491 IF l_found_good_date THEN
492 IF x_suggest_receive_date = p_receive_date THEN
493 x_return_code := 0;
494 ELSE
495 x_return_code := 1;
496 END IF;
497 ELSE
498 x_return_code := 3;
499 END IF;
500 ELSE
501 x_return_code := 5;
502 END IF;
503 END IF;
504 ELSE
505 IF p_receive_date IS NULL THEN
506 -- validate ship date
507 --
508 -- Debug Statements
509 --
510 IF l_debug_on THEN
511 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_CAL_ASG_VALIDATIONS.FIND_VALID_DATE',WSH_DEBUG_SV.C_PROC_LEVEL);
512 END IF;
513 --
514 WSH_CAL_ASG_VALIDATIONS.Find_Valid_Date
515 ( p_ship_date
516 , 'SHIPPING'
517 , p_ship_assoc_type
518 , p_ship_location_id
519 , p_ship_vendor_site_id
520 , p_ship_customer_site_use_id
521 , p_freight_code
522 , p_freight_org_id
523 , x_suggest_ship_date
524 , l_found_good_date
525 , p_primary_threshold
526 , 'BACK'
527 , p_ship_time_matters
528 );
529 IF l_found_good_date THEN
530 --
531 -- Debug Statements
532 --
533 IF l_debug_on THEN
534 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_CAL_ASG_VALIDATIONS.SINGLE_DATE',WSH_DEBUG_SV.C_PROC_LEVEL);
535 END IF;
536 --
537 WSH_CAL_ASG_VALIDATIONS.Single_Date(
538 x_suggest_ship_date
539 , 'CARRIER'
540 , 'CARRIER'
541 , p_ship_location_id
542 , p_ship_vendor_site_id
543 , p_ship_customer_site_use_id
544 , p_freight_code
545 , p_freight_org_id
546 , l_found_good_date
547 , p_ship_time_matters
548 );
549 IF l_found_good_date THEN
550 IF x_suggest_ship_date = p_ship_date THEN
551 x_return_code := 0;
552 ELSE
553 x_return_code := 1;
554 END IF;
555 ELSE
556 x_return_code := 3;
557 END IF;
558 ELSE
559 x_return_code := 5;
560 END IF;
561 ELSE
562 -- Need to validate both dates
563 l_lead_time := p_receive_date - p_ship_date;
564 IF l_lead_time < 0 THEN
565 RAISE wsh_bad_parameter;
566 END IF;
567 IF p_priority = 'SHIP' THEN
568 -- find valid ship date first
569 --
570 -- Debug Statements
571 --
572 IF l_debug_on THEN
573 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_CAL_ASG_VALIDATIONS.FIND_VALID_DATE',WSH_DEBUG_SV.C_PROC_LEVEL);
574 END IF;
575 --
576
577 /*
578 WSH_CAL_ASG_VALIDATIONS.Find_Valid_Date
579 ( p_ship_date
580 , 'SHIPPING'
581 , p_ship_assoc_type
582 , p_ship_location_id
583 , p_ship_vendor_site_id
584 , p_ship_customer_site_use_id
585 , p_freight_code
586 , p_freight_org_id
587 , x_suggest_ship_date
588 , l_found_good_date
589 , p_primary_threshold
590 , 'FORWARD'
591 , p_ship_time_matters
592 );
593 IF l_found_good_date THEN
594 --
595 -- Debug Statements
596 --
597 IF l_debug_on THEN
598 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_CAL_ASG_VALIDATIONS.FIND_VALID_DATE',WSH_DEBUG_SV.C_PROC_LEVEL);
599 END IF;
600 --
601 WSH_CAL_ASG_VALIDATIONS.Find_Valid_Date
602 ( p_receive_date - (p_ship_date - x_suggest_ship_date)
603 , 'RECEIVING'
604 , p_receive_assoc_type
605 , p_receive_location_id
606 , p_receive_vendor_site_id
607 , p_receive_customer_site_use_id
608 , p_freight_code
609 , p_freight_org_id
610 , x_suggest_receive_date
611 , l_found_good_date
612 , p_secondary_threshold
613 , 'FORWARD'
614 , p_receive_time_matters
615 );
616 IF l_found_good_date THEN
617 IF (p_receive_date - x_suggest_receive_date)
618 <= p_secondary_threshold THEN
619 IF x_suggest_receive_date - x_suggest_ship_date
620 = l_lead_time THEN
621 IF x_suggest_ship_date = p_ship_date THEN
622 x_return_code := 0;
623 ELSE
624 x_return_code := 1;
625 END IF;
626 ELSE
630 x_return_code := 4;
627 x_return_code := 2;
628 END IF;
629 ELSE
631 END IF;
632 ELSE
633 x_return_code := 4;
634 END IF;
635 ELSE
636 x_return_code := 5;
637 END IF;
638 IF x_return_code IN (0,1,2) THEN
639 --
640 -- Debug Statements
641 --
642 IF l_debug_on THEN
643 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_CAL_ASG_VALIDATIONS.SINGLE_DATE',WSH_DEBUG_SV.C_PROC_LEVEL);
644 END IF;
645 --
646 WSH_CAL_ASG_VALIDATIONS.Single_Date(
647 x_suggest_ship_date
648 , 'CARRIER'
649 , p_ship_assoc_type
650 , p_ship_location_id
651 , p_ship_vendor_site_id
652 , p_ship_customer_site_use_id
653 , p_freight_code
654 , p_freight_org_id
655 , l_found_good_date
656 , p_ship_time_matters
657 );
658 IF l_found_good_date then
659 --
660 -- Debug Statements
661 --
662 IF l_debug_on THEN
663 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_CAL_ASG_VALIDATIONS.SINGLE_DATE',WSH_DEBUG_SV.C_PROC_LEVEL);
664 END IF;
665 --
666 WSH_CAL_ASG_VALIDATIONS.Single_Date(
667 x_suggest_receive_date
668 , 'CARRIER'
669 , p_receive_assoc_type
670 , p_receive_location_id
671 , p_receive_vendor_site_id
672 , p_receive_customer_site_use_id
673 , p_freight_code
674 , p_freight_org_id
675 , l_found_good_date
676 , p_receive_time_matters
677 );
678 IF l_found_good_date then
679 x_return_code := x_return_code;
680 ELSE
681 x_return_code := 3;
682 END IF;
683 ELSE
684 x_return_code := 3;
685 END IF;
686 END IF;*/
687 l_suggest_ship_date := p_ship_date;
688 l_primary_threshold := 0;
689
690 --bug 8567091: Doing the Calendar validation in a loop
691 -- Previously if the suggested_ship_date is not valid as per Org's Carrier Calendar,
692 -- no message was displayed.Neither was a next valid date calculated.
693 -- As per the bug 8567091,the nearest valid ship date as per carrier calendar
694 -- should be calculated.(So Find_Valid_Date is called for carrier Calendar validation
695 -- instead of Single_Date)
696 -- And the new suggested ship date should again be validated against Shipping Calendar.
697 -- Hence the loop.
698
699 WHILE l_primary_threshold <= p_primary_threshold LOOP
700 --{
701 IF l_debug_on THEN
702 WSH_DEBUG_SV.log(l_module_name,'l_suggest_ship_date',l_suggest_ship_date);
703 WSH_DEBUG_SV.log(l_module_name,'l_primary_threshold',l_primary_threshold);
704 END IF;
705
706 WSH_CAL_ASG_VALIDATIONS.Find_Valid_Date
707 ( l_suggest_ship_date
708 , 'SHIPPING'
709 , p_ship_assoc_type
710 , p_ship_location_id
711 , p_ship_vendor_site_id
712 , p_ship_customer_site_use_id
713 , p_freight_code
714 , p_freight_org_id
715 , x_suggest_ship_date
716 , l_found_good_date
717 , (p_primary_threshold - l_primary_threshold )
718 , 'FORWARD'
719 , p_ship_time_matters
720 );
721 IF l_found_good_date THEN
722 --{
723 IF l_debug_on THEN
724 WSH_DEBUG_SV.log(l_module_name,'x_suggest_ship_date',x_suggest_ship_date);
725 END IF;
726
727 l_primary_threshold := l_primary_threshold + (x_suggest_ship_date - l_suggest_ship_date);
728 IF l_debug_on THEN
729 WSH_DEBUG_SV.log(l_module_name,'l_primary_threshold',l_primary_threshold);
730 END IF;
731 --
732 -- Debug Statements
733 --
734 IF l_debug_on THEN
735 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_CAL_ASG_VALIDATIONS.FIND_VALID_DATE',WSH_DEBUG_SV.C_PROC_LEVEL);
736 END IF;
737 --
738 WSH_CAL_ASG_VALIDATIONS.Find_Valid_Date
739 ( p_receive_date - (p_ship_date - x_suggest_ship_date)
740 , 'RECEIVING'
741 , p_receive_assoc_type
742 , p_receive_location_id
743 , p_receive_vendor_site_id
744 , p_receive_customer_site_use_id
745 , p_freight_code
746 , p_freight_org_id
747 , x_suggest_receive_date
748 , l_found_good_date
749 , p_secondary_threshold
750 , 'FORWARD'
751 , p_receive_time_matters
752 );
753 IF l_found_good_date THEN
754 --{
755 IF (p_receive_date - x_suggest_receive_date)
756 <= p_secondary_threshold THEN
760 x_return_code := 0;
757 IF x_suggest_receive_date - x_suggest_ship_date
758 = l_lead_time THEN
759 IF x_suggest_ship_date = p_ship_date THEN
761 ELSE
762 x_return_code := 1;
763 END IF;
764 ELSE
765 x_return_code := 2;
766 END IF;
767 ELSE
768 x_return_code := 4;
769 EXIT;
770 END IF;
771 ELSE
772 x_return_code := 4;
773 EXIT;
774 --}
775 END IF;
776 ELSE
777 x_return_code := 5;
778 --Bug 8567091: Resetting suggested_receive_date to 'p_receive_date',
779 -- in the case where no suggested Ship date is not found in 10 days
780 x_suggest_receive_date := p_receive_date;
781 EXIT;
782 --}
783 END IF;
784 IF x_return_code IN (0,1,2) THEN
785 --
786 -- Debug Statements
787 --
788 l_suggest_ship_date := x_suggest_ship_date;
789 IF l_debug_on THEN
790 WSH_DEBUG_SV.log(l_module_name,'l_suggest_ship_date',l_suggest_ship_date);
791 WSH_DEBUG_SV.log(l_module_name,'l_primary_threshold',l_primary_threshold);
792 END IF;
793 --Bug 8567091 : 'Find_Valid_Date' is called for carrier Calendar validation instead of 'Single_Date'
794
795 WSH_CAL_ASG_VALIDATIONS.Find_Valid_Date
796 ( l_suggest_ship_date
797 , 'CARRIER'
798 , p_ship_assoc_type
799 , p_ship_location_id
800 , p_ship_vendor_site_id
801 , p_ship_customer_site_use_id
802 , p_freight_code
803 , p_freight_org_id
804 , x_suggest_ship_date
805 , l_found_good_date
806 , (p_primary_threshold - l_primary_threshold)
807 , 'FORWARD'
808 , p_ship_time_matters
809 );
810 IF l_found_good_date then
811
812 IF l_debug_on THEN
813 WSH_DEBUG_SV.log(l_module_name,'x_suggest_ship_date',x_suggest_ship_date);
814 END IF;
815
816 IF x_suggest_ship_date = l_suggest_ship_date THEN
817 --
818 -- Debug Statements
819 --
820 IF l_debug_on THEN
821 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_CAL_ASG_VALIDATIONS.SINGLE_DATE',WSH_DEBUG_SV.C_PROC_LEVEL);
822 END IF;
823 --
824 WSH_CAL_ASG_VALIDATIONS.Single_Date(
825 x_suggest_receive_date
826 , 'CARRIER'
827 , p_receive_assoc_type
828 , p_receive_location_id
829 , p_receive_vendor_site_id
830 , p_receive_customer_site_use_id
831 , p_freight_code
832 , p_freight_org_id
833 , l_found_good_date
834 , p_receive_time_matters
835 );
836 IF l_found_good_date then
837 x_return_code := x_return_code;
838 ELSE
839 x_return_code := 3;
840 END IF;
841 EXIT;
842 ELSE
843 l_primary_threshold := l_primary_threshold + (x_suggest_ship_date - l_suggest_ship_date);
844 l_suggest_ship_date := x_suggest_ship_date ;
845 x_return_code := 3;
846 END IF;
847 ELSE
848 --Bug 8567091 : Return code as 5 when a valid date in Org's carrier calendar is not
849 -- found with in the threshold(10 days)
850 x_return_code := 5;
851 --Bug 8567091: Resetting suggested_receive_date to 'p_receive_date',
852 -- in the case where no suggested Ship date is not found in 10 days
853 x_suggest_receive_date := p_receive_date;
854 EXIT;
855 END IF;
856 END IF;
857 --}
858 END LOOP;
859
860 ELSIF p_priority = 'RECEIVE' THEN
861 -- find valid receive date first
862 --
863 -- Debug Statements
864 --
865 IF l_debug_on THEN
866 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_CAL_ASG_VALIDATIONS.FIND_VALID_DATE',WSH_DEBUG_SV.C_PROC_LEVEL);
867 END IF;
868 --
869 WSH_CAL_ASG_VALIDATIONS.Find_Valid_Date
870 ( p_receive_date
871 , 'RECEIVING'
872 , p_receive_assoc_type
873 , p_receive_location_id
874 , p_receive_vendor_site_id
875 , p_receive_customer_site_use_id
876 , p_freight_code
877 , p_freight_org_id
878 , x_suggest_receive_date
879 , l_found_good_date
883 );
880 , p_primary_threshold
881 , 'BACK'
882 , p_receive_time_matters
884 IF l_found_good_date THEN
885 --
886 -- Debug Statements
887 --
888 IF l_debug_on THEN
889 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_CAL_ASG_VALIDATIONS.FIND_VALID_DATE',WSH_DEBUG_SV.C_PROC_LEVEL);
890 END IF;
891 --
892 WSH_CAL_ASG_VALIDATIONS.Find_Valid_Date
893 ( p_ship_date - (p_receive_date - x_suggest_receive_date)
894 , 'SHIPPING'
895 , p_ship_assoc_type
896 , p_ship_location_id
897 , p_ship_vendor_site_id
898 , p_ship_customer_site_use_id
899 , p_freight_code
900 , p_freight_org_id
901 , x_suggest_ship_date
902 , l_found_good_date
903 , p_secondary_threshold
904 , 'BACK'
905 , p_ship_time_matters
906 );
907 IF l_found_good_date THEN
908 IF (p_ship_date - x_suggest_ship_date)
909 <= p_secondary_threshold THEN
910 IF x_suggest_receive_date - x_suggest_ship_date
911 = l_lead_time THEN
912 IF x_suggest_ship_date = p_ship_date THEN
913 x_return_code := 0;
914 ELSE
915 x_return_code := 1;
916 END IF;
917 ELSE
918 x_return_code := 2;
919 END IF;
920 ELSE
921 x_return_code := 4;
922 END IF;
923 ELSE
924 x_return_code := 4;
925 END IF;
926 ELSE
927 x_return_code := 5;
928 END IF;
929 IF x_return_code IN (0,1,2) THEN
930 --
931 -- Debug Statements
932 --
933 IF l_debug_on THEN
934 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_CAL_ASG_VALIDATIONS.SINGLE_DATE',WSH_DEBUG_SV.C_PROC_LEVEL);
935 END IF;
936 --
937 WSH_CAL_ASG_VALIDATIONS.Single_Date(
938 x_suggest_ship_date
939 , 'CARRIER'
940 , p_ship_assoc_type
941 , p_ship_location_id
942 , p_ship_vendor_site_id
943 , p_ship_customer_site_use_id
944 , p_freight_code
945 , p_freight_org_id
946 , l_found_good_date
947 , p_ship_time_matters
948 );
949 IF l_found_good_date then
950 --
951 -- Debug Statements
952 --
953 IF l_debug_on THEN
954 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_CAL_ASG_VALIDATIONS.SINGLE_DATE',WSH_DEBUG_SV.C_PROC_LEVEL);
955 END IF;
956 --
957 WSH_CAL_ASG_VALIDATIONS.Single_Date(
958 x_suggest_receive_date
959 , p_receive_assoc_type
960 , 'CARRIER'
961 , p_receive_location_id
962 , p_receive_vendor_site_id
963 , p_receive_customer_site_use_id
964 , p_freight_code
965 , p_freight_org_id
966 , l_found_good_date
967 , p_receive_time_matters
968 );
969 IF l_found_good_date then
970 x_return_code := x_return_code;
971 ELSE
972 x_return_code := 3;
973 END IF;
974 ELSE
975 x_return_code := 3;
976 END IF;
977 END IF;
978 ELSE
979 RAISE wsh_bad_parameter;
980 END IF;
981 END IF;
982 END IF;
983 -- END PROCEDURE LOGIC
984 -- report success
985 x_return_status := FND_API.G_RET_STS_SUCCESS;
986 FND_MSG_PUB.Count_And_Get
987 ( p_count => x_msg_count
988 , p_data => x_msg_data
989 );
990 --
991 -- Debug Statements
992 --
993 IF l_debug_on THEN
994 WSH_DEBUG_SV.pop(l_module_name);
995 END IF;
996 --
997 EXCEPTION
998 -- If we get no calendars to validate against, return success
999 WHEN wsh_missing_mandatory_attr THEN
1000 x_return_code := 0;
1001 -- If we get bad parameters, return error
1002 --
1003 -- Debug Statements
1004 --
1005 IF l_debug_on THEN
1006 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_MISSING_MANDATORY_ATTR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1007 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_MISSING_MANDATORY_ATTR');
1008 END IF;
1009 --
1010 WHEN wsh_bad_parameter THEN
1011 x_return_code := 6;
1012
1013 --
1014 -- Debug Statements
1015 --
1016 IF l_debug_on THEN
1017 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_BAD_PARAMETER exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1018 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_BAD_PARAMETER');
1019 END IF;
1020 --
1021 WHEN FND_API.G_EXC_ERROR THEN
1022 x_return_status := FND_API.G_RET_STS_ERROR;
1023 -- Get message count and data
1024 FND_MSG_PUB.Count_And_Get
1025 ( p_count => x_msg_count
1026 , p_data => x_msg_data
1027 );
1028
1029 --
1030 -- Debug Statements
1031 --
1032 IF l_debug_on THEN
1033 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1034 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
1035 END IF;
1036 --
1040 FND_MSG_PUB.Count_And_Get
1037 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1038 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1039 -- Get message count and data
1041 ( p_count => x_msg_count
1042 , p_data => x_msg_data
1043 );
1044
1045 --
1046 -- Debug Statements
1047 --
1048 IF l_debug_on THEN
1049 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_UNEXPECTED_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1050 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
1051 END IF;
1052 --
1053 WHEN OTHERS THEN
1054 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1055 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1056 THEN
1057 FND_MSG_PUB.Add_Exc_Msg
1058 ( G_PKG_NAME
1059 , '_x_'
1060 );
1061 END IF;
1062 -- Get message count and data
1063 FND_MSG_PUB.Count_And_Get
1064 ( p_count => x_msg_count
1065 , p_data => x_msg_data
1066 );
1067 --
1068 -- Debug Statements
1069 --
1070 IF l_debug_on THEN
1071 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1072 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1073 END IF;
1074 --
1075 END Transport_Dates;
1076 --========================================================================
1077 -- PROCEDURE : Find_Valid_Date PRIVATE
1078 -- PARAMETERS:
1079 -- p_input_date Date to validate
1080 -- p_calendar_type Calendar type -- SHIPPING, RECEIVING, or
1081 -- CARRIER
1082 -- p_location_id Location ID (optional)
1083 -- p_vendor_site_id Vendor Site ID (optional)
1084 -- p_customer_site_use_id Customer Site Use ID (optional)
1085 -- p_carrier_code Carrier code (optional)
1086 -- p_carrier_org_id Carrier Organization ID (optional)
1087 -- x_suggest_date Suggested valid date
1088 -- x_success Did we find a date?
1089 -- p_threshold Number of dates to try before failure
1090 -- p_which_way search FORWARD or BACK
1091 -- p_time_matters Is time important for this date?
1092 -- VERSION : current version 1.0
1093 -- initial version 1.0
1094 -- COMMENT : Searches for a valid date near a given date by checking
1095 -- nearby dates against a given calendar. Decrement if
1096 -- finding ship date, increment if finding receive date
1097 --========================================================================
1098 PROCEDURE Find_Valid_Date
1099 ( p_input_date IN DATE
1100 , p_calendar_type IN VARCHAR2
1101 , p_assoc_type IN VARCHAR2
1102 , p_location_id IN NUMBER
1103 , p_vendor_site_id IN NUMBER
1104 , p_customer_site_use_id IN NUMBER
1105 , p_freight_code IN VARCHAR2
1106 , p_freight_org_id IN NUMBER
1107 , x_suggest_date OUT NOCOPY DATE
1108 , x_success OUT NOCOPY BOOLEAN
1109 , p_threshold IN NUMBER
1110 , p_which_way IN VARCHAR2
1111 , p_time_matters IN BOOLEAN
1112 ) IS
1113 l_api_name CONSTANT VARCHAR2(30):= 'Find_Valid_Date';
1114 l_number_tried NUMBER := 0;
1115 --
1116 l_debug_on BOOLEAN;
1117 --
1118 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'FIND_VALID_DATE';
1119 --
1120 BEGIN
1121 --
1122 -- Debug Statements
1123 --
1124 --
1125 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1126 --
1127 IF l_debug_on IS NULL
1128 THEN
1129 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1130 END IF;
1131 --
1132 IF l_debug_on THEN
1133 WSH_DEBUG_SV.push(l_module_name);
1134 --
1135 WSH_DEBUG_SV.log(l_module_name,'P_INPUT_DATE',P_INPUT_DATE);
1136 WSH_DEBUG_SV.log(l_module_name,'P_CALENDAR_TYPE',P_CALENDAR_TYPE);
1137 WSH_DEBUG_SV.log(l_module_name,'P_ASSOC_TYPE',P_ASSOC_TYPE);
1138 WSH_DEBUG_SV.log(l_module_name,'P_LOCATION_ID',P_LOCATION_ID);
1139 WSH_DEBUG_SV.log(l_module_name,'P_VENDOR_SITE_ID',P_VENDOR_SITE_ID);
1140 WSH_DEBUG_SV.log(l_module_name,'P_CUSTOMER_SITE_USE_ID',P_CUSTOMER_SITE_USE_ID);
1141 WSH_DEBUG_SV.log(l_module_name,'P_FREIGHT_CODE',P_FREIGHT_CODE);
1142 WSH_DEBUG_SV.log(l_module_name,'P_FREIGHT_ORG_ID',P_FREIGHT_ORG_ID);
1143 WSH_DEBUG_SV.log(l_module_name,'P_THRESHOLD',P_THRESHOLD);
1144 WSH_DEBUG_SV.log(l_module_name,'P_WHICH_WAY',P_WHICH_WAY);
1145 WSH_DEBUG_SV.log(l_module_name,'P_TIME_MATTERS',P_TIME_MATTERS);
1146 END IF;
1147 --
1148 x_success := FALSE;
1149 WHILE nvl(x_success,FALSE) = FALSE AND l_number_tried <= p_threshold LOOP
1150 IF p_which_way = 'FORWARD' THEN
1151 x_suggest_date := p_input_date + l_number_tried;
1152 ELSE
1153 x_suggest_date := p_input_date - l_number_tried;
1154 END IF;
1155 --
1156 -- Debug Statements
1157 --
1158 IF l_debug_on THEN
1159 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_CAL_ASG_VALIDATIONS.SINGLE_DATE',WSH_DEBUG_SV.C_PROC_LEVEL);
1160 END IF;
1161 --
1162 WSH_CAL_ASG_VALIDATIONS.Single_Date(
1163 x_suggest_date
1164 , p_calendar_type
1165 , p_assoc_type
1166 , p_location_id
1167 , p_vendor_site_id
1168 , p_customer_site_use_id
1169 , p_freight_code
1170 , p_freight_org_id
1171 , x_success
1172 , p_time_matters
1173 );
1174
1178 IF NOT x_success THEN
1175 l_number_tried := l_number_tried + 1;
1176 END LOOP;
1177
1179 x_suggest_date := p_input_date;
1180 END IF;
1181 -- <end of API logic>
1182 --
1183 -- Debug Statements
1184 --
1185 IF l_debug_on THEN
1186 WSH_DEBUG_SV.pop(l_module_name);
1187 END IF;
1188 --
1189 END Find_Valid_Date;
1190 --========================================================================
1191 -- PROCEDURE : Single_Date PRIVATE
1192 -- PARAMETERS:
1193 -- p_date Date to validate
1194 -- p_calendar_type Calendar type -- SHIPPING, RECEIVING, or
1195 -- CARRIER
1196 -- p_assoc_type Type of site
1197 -- p_location_id Location ID (optional)
1198 -- p_vendor_site_id Vendor Site ID (optional)
1199 -- p_customer_site_use_id Customer Site Use ID (optional)
1200 -- p_freight_code Carrier code (optional)
1201 -- p_freight_org_id Carrier Organization ID (optional)
1202 -- p_date_is_valid Return information
1203 -- p_time_matters Is time important for this date?
1204 -- VERSION : current version 1.0
1205 -- initial version 1.0
1206 -- COMMENT : Validates a shipping or receiving date against a shipping
1207 -- calendar and against a carrier calendar
1208 --========================================================================
1209 PROCEDURE Single_Date
1210 ( p_date IN DATE
1211 , p_calendar_type IN VARCHAR2
1212 , p_assoc_type IN VARCHAR2
1213 , p_location_id IN NUMBER
1214 , p_vendor_site_id IN NUMBER
1215 , p_customer_site_use_id IN NUMBER
1216 , p_freight_code IN VARCHAR2
1217 , p_freight_org_id IN NUMBER
1218 , p_date_is_valid OUT NOCOPY BOOLEAN
1219 , p_time_matters IN BOOLEAN
1220 )
1221 IS
1222 l_api_name CONSTANT VARCHAR2(30):= 'Single_Date';
1223 l_calendar_code VARCHAR2(10);
1224 l_err_code NUMBER;
1225 l_err_meg VARCHAR(200);
1226 l_enabled_flag VARCHAR2(1);
1227 l_entity_type VARCHAR2(12);
1228 l_site_id NUMBER;
1229 l_freight_code VARCHAR2(30);--6156495:Local variable added
1230
1231 CURSOR c_get_calendar_code IS
1232 SELECT WCA.CALENDAR_CODE,
1233 WCA.ENABLED_FLAG
1234 FROM WSH_CALENDAR_ASSIGNMENTS WCA ,
1235 PO_VENDORS POV ,
1236 PO_VENDOR_SITES_all POVS ,
1237 hr_organization_units HR
1238 WHERE WCA.ASSOCIATION_TYPE = 'VENDOR_SITE'
1239 AND WCA.VENDOR_SITE_ID = POVS.VENDOR_SITE_ID
1240 AND POV.VENDOR_ID = POVS.VENDOR_ID
1241 AND hr.organization_id = povs.org_id
1242 ---
1243 AND 'VENDOR' = l_entity_type
1244 AND WCA.CALENDAR_TYPE = p_calendar_type
1245 AND WCA.ASSOCIATION_TYPE = p_assoc_type
1246 AND NVL(WCA.FREIGHT_CODE,'All') = NVL(p_freight_code,'All')
1247 AND ( POVS.VENDOR_SITE_ID = l_site_id OR
1248 WCA.LOCATION_ID = p_location_id)
1249 UNION ALL
1250
1251 SELECT WCA.CALENDAR_CODE,
1252 WCA.ENABLED_FLAG
1253 FROM WSH_CALENDAR_ASSIGNMENTS WCA,
1254 hz_relationships rel,
1255 hz_party_sites hps,
1256 hz_party_site_uses hpsu,
1257 wsh_locations wl
1258 WHERE WCA.ASSOCIATION_TYPE = 'VENDOR_LOCATION'
1259 AND WCA.LOCATION_ID = WL.WSH_LOCATION_ID
1260 AND hps.party_id = rel.subject_id
1261 AND hps.party_site_id = hpsu.party_site_id
1262 AND hps.location_id = wl.wsh_location_id
1263 AND site_use_type = 'SUPPLIER_SHIP_FROM'
1264 AND rel.relationship_type = 'POS_VENDOR_PARTY'
1265 AND rel.object_table_name = 'PO_VENDORS'
1266 AND rel.object_type = 'POS_VENDOR'
1267 AND rel.subject_table_name = 'HZ_PARTIES'
1268 AND rel.subject_type = 'ORGANIZATION'
1269 ----
1270 AND 'VENDOR' = l_entity_type
1271 AND WCA.CALENDAR_TYPE = p_calendar_type
1272 AND WCA.ASSOCIATION_TYPE = p_assoc_type
1273 AND NVL(WCA.FREIGHT_CODE,'All') = NVL(p_freight_code,'All')
1274 AND ( WL.WSH_LOCATION_ID = l_site_id OR
1275 WCA.LOCATION_ID = p_location_id)
1276 UNION ALL
1277 SELECT WCA.CALENDAR_CODE,
1278 WCA.ENABLED_FLAG
1279 FROM HZ_CUST_ACCT_SITES_ALL HCAS,
1280 HZ_CUST_SITE_USES_ALL HCSU,
1281 HZ_PARTY_SITES HPS,
1282 HZ_CUST_ACCOUNTS HCA,
1283 WSH_LOCATIONS WLO,
1284 WSH_CALENDAR_ASSIGNMENTS WCA
1285 WHERE HCSU.CUST_ACCT_SITE_ID = HCAS.CUST_ACCT_SITE_ID
1286 and HCAS.PARTY_SITE_ID = HPS.PARTY_SITE_ID
1287 AND HCAS.cust_account_id = hca.cust_account_id
1288 AND HPS.LOCATION_ID = WLO.source_location_id(+)
1289 AND WLO.location_source_code(+) ='HZ'
1290 AND WCA.CUSTOMER_SITE_USE_ID = HCSU.SITE_USE_ID
1291 AND WCA.ASSOCIATION_TYPE = 'CUSTOMER_SITE'
1292 ----
1293 AND 'CUSTOMER' = l_entity_type
1294 AND WCA.CALENDAR_TYPE = p_calendar_type
1295 AND WCA.ASSOCIATION_TYPE = p_assoc_type
1296 -- AND NVL(WCA.FREIGHT_CODE,'All') = NVL(p_freight_code,'All')--6156495
1297 AND NVL(WCA.FREIGHT_CODE,'All') = NVL(l_freight_code,'All')
1298 AND ( HCSU.SITE_USE_ID = l_site_id OR
1299 WCA.LOCATION_ID = p_location_id)
1300 UNION ALL
1301
1302 SELECT WCA.CALENDAR_CODE,
1303 WCA.ENABLED_FLAG
1304 FROM WSH_CALENDAR_ASSIGNMENTS WCA ,
1305 WSH_LOCATIONS WLO
1306 WHERE WCA.ASSOCIATION_TYPE = 'HR_LOCATION'
1307 AND WCA.LOCATION_ID = WLO.WSH_LOCATION_ID(+)
1308 ----
1309 AND 'ORGANIZATION' = l_entity_type
1310 AND WCA.CALENDAR_TYPE = p_calendar_type
1311 AND WCA.ASSOCIATION_TYPE = p_assoc_type
1312 -- AND NVL(WCA.FREIGHT_CODE,'All') = NVL(p_freight_code,'All')---6156495
1313 AND NVL(WCA.FREIGHT_CODE,'All') = NVL(l_freight_code,'All')
1314 AND (WLO.WSH_LOCATION_ID = l_site_id OR
1315 WCA.LOCATION_ID =p_location_id)
1316 UNION ALL
1317 SELECT WCA.CALENDAR_CODE,
1318 WCA.ENABLED_FLAG
1319 FROM WSH_CALENDAR_ASSIGNMENTS WCA ,
1320 HZ_PARTY_SITES HPS
1321 WHERE WCA.ASSOCIATION_TYPE = 'CARRIER_SITE'
1322 AND WCA.CARRIER_ID = HPS.PARTY_ID
1323 AND WCA.CARRIER_SITE_ID = HPS.PARTY_SITE_ID
1324 ----
1325 AND 'CARRIER' = l_entity_type
1326 AND WCA.CALENDAR_TYPE = p_calendar_type
1327 AND WCA.ASSOCIATION_TYPE = p_assoc_type
1328 AND NVL(WCA.FREIGHT_CODE,'All') = NVL(p_freight_code,'All')
1329 AND (WCA.CARRIER_SITE_ID = l_site_id OR
1330 WCA.LOCATION_ID =p_location_id);
1331
1332 --Bug 8855773:Added the cursor, that would get the default shipping/receiving/carrier Calendar
1333 -- defined for a particular customer or an Organization.
1334 CURSOR c_get_calendar_code_wo_loc IS
1335 SELECT WCA.CALENDAR_CODE,WCA.ENABLED_FLAG
1336 FROM HZ_CUST_ACCT_SITES_ALL HCAS,
1337 HZ_CUST_SITE_USES_ALL HCSU,
1338 HZ_CUST_ACCOUNTS HCA,
1339 HZ_PARTY_SITES HPS,
1340 WSH_LOCATIONS WLO,
1341 wsh_calendar_assignments WCA
1342 WHERE HCAS.party_site_id = HPS.party_site_id
1343 AND HCSU.CUST_ACCT_SITE_ID = HCAS.CUST_ACCT_SITE_ID
1344 AND HCAS.cust_account_id = hca.cust_account_id
1345 AND (HCSU.SITE_USE_ID = l_site_id OR
1346 HPS.location_id = p_location_id)
1347 AND WCA.customer_id = HCAS.cust_account_id
1348 AND HPS.LOCATION_ID = WLO.source_location_id(+)
1349 AND WLO.location_source_code(+) ='HZ'
1350 AND WCA.CALENDAR_TYPE = p_calendar_type
1351 AND wca.location_id IS NULL
1352 AND WCA.ASSOCIATION_TYPE = 'CUSTOMER'
1353 AND 'CUSTOMER' = l_entity_type
1354 AND 'CUSTOMER_SITE' = p_assoc_type
1355 AND NVL(WCA.FREIGHT_CODE,'All') = NVL(l_freight_code,'All')
1356
1357 UNION ALL
1358
1359 SELECT WCA.CALENDAR_CODE,
1360 WCA.ENABLED_FLAG
1361 FROM WSH_CALENDAR_ASSIGNMENTS WCA ,
1362 WSH_LOCATIONS WLO,
1363 HR_ORGANIZATION_UNITS HOU
1364 WHERE HOU.LOCATION_ID = WLO.source_LOCATION_ID
1365 And wlo.location_source_code='HR'
1366 AND WLO.wsh_location_id = p_location_id
1367 AND WCA.organization_id = HOU.organization_id
1368 AND 'ORGANIZATION' = l_entity_type
1369 AND WCA.CALENDAR_TYPE = p_calendar_type
1370 AND WCA.ASSOCIATION_TYPE = 'ORGANIZATION'
1371 AND 'HR_LOCATION' = p_assoc_type
1372 AND NVL(WCA.FREIGHT_CODE,'All') = NVL(l_freight_code,'All');
1373
1374
1375
1376
1377
1378
1379
1380
1381 --
1382 l_debug_on BOOLEAN;
1383 --
1384 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'SINGLE_DATE';
1385 --
1386 BEGIN
1387 -- <begin procedure logic>
1388 --
1389 -- Debug Statements
1390 --
1391 --
1392 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1393 --
1394 IF l_debug_on IS NULL
1395 THEN
1396 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1397 END IF;
1398 --
1399 IF l_debug_on THEN
1400 WSH_DEBUG_SV.push(l_module_name);
1401 --
1402 WSH_DEBUG_SV.log(l_module_name,'P_DATE',P_DATE);
1403 WSH_DEBUG_SV.log(l_module_name,'P_CALENDAR_TYPE',P_CALENDAR_TYPE);
1404 WSH_DEBUG_SV.log(l_module_name,'P_ASSOC_TYPE',P_ASSOC_TYPE);
1405 WSH_DEBUG_SV.log(l_module_name,'P_LOCATION_ID',P_LOCATION_ID);
1406 WSH_DEBUG_SV.log(l_module_name,'P_VENDOR_SITE_ID',P_VENDOR_SITE_ID);
1407 WSH_DEBUG_SV.log(l_module_name,'P_CUSTOMER_SITE_USE_ID',P_CUSTOMER_SITE_USE_ID);
1408 WSH_DEBUG_SV.log(l_module_name,'P_FREIGHT_CODE',P_FREIGHT_CODE);
1409 WSH_DEBUG_SV.log(l_module_name,'P_FREIGHT_ORG_ID',P_FREIGHT_ORG_ID);
1410 WSH_DEBUG_SV.log(l_module_name,'P_TIME_MATTERS',P_TIME_MATTERS);
1411 END IF;
1412 --
1413 l_enabled_flag := 'N';
1414 l_calendar_code := NULL;
1415 l_freight_code := NULL;--6156495
1416
1417 IF p_assoc_type = 'CUSTOMER_SITE' THEN
1418 l_entity_type := 'CUSTOMER';
1419 l_site_id := p_customer_site_use_id;
1420 IF p_calendar_type ='CARRIER' THEN
1421 l_freight_code:= p_freight_code;--6156495
1422 END IF;
1423 ELSIF p_assoc_type = 'HR_LOCATION' THEN
1424 l_entity_type := 'ORGANIZATION';
1425 l_site_id := p_location_id;
1426 IF p_calendar_type ='CARRIER' THEN
1427 l_freight_code:= p_freight_code;--6156495
1428 END IF;
1429 ELSIF p_assoc_type = 'CARRIER_SITE' THEN
1430 l_entity_type := 'CARRIER';
1431 l_freight_code:= p_freight_code;--6156495
1432 ELSIF p_assoc_type in ('VENDOR_SITE', 'VENDOR_LOCATION') THEN
1433 l_entity_type := 'VENDOR';
1434 IF p_assoc_type = 'VENDOR_SITE' THEN
1435 l_site_id := p_vendor_site_id;
1436 ELSE
1437 l_site_id := p_location_id;
1438 END IF;
1439 END IF;
1440
1441 OPEN c_get_calendar_code;
1442 FETCH c_get_calendar_code INTO l_calendar_code, l_enabled_flag;
1443 --Bug 8855773:When no calendar association is found, for the given location,
1444 -- should derive the default calendar at the higher level.
1445 IF c_get_calendar_code%NOTFOUND THEN
1446 IF l_debug_on THEN
1447 WSH_DEBUG_SV.logmsg(l_module_name,'Getting default calendar for Customer/organization as no cal association is found for the location.');
1448 END IF;
1449
1450 OPEN c_get_calendar_code_wo_loc;
1451 FETCH c_get_calendar_code_wo_loc INTO l_calendar_code, l_enabled_flag;
1452 CLOSE c_get_calendar_code_wo_loc;
1453 END IF;
1454
1455 CLOSE c_get_calendar_code;
1456
1457 p_date_is_valid := TRUE;
1458
1459 IF l_calendar_code IS NOT NULL AND l_enabled_flag = 'Y' THEN
1460 IF l_debug_on THEN
1461 WSH_DEBUG_SV.logmsg(l_module_name,'l_calendar_code '||l_calendar_code);
1462 END IF;
1463 IF p_time_matters THEN
1464 --
1465 -- Debug Statements
1466 --
1467 IF l_debug_on THEN
1468 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit BOM_CALENDAR_API_BK.CHECK_WORKING_SHIFT',WSH_DEBUG_SV.C_PROC_LEVEL);
1469 END IF;
1470 --
1471 p_date_is_valid := BOM_CALENDAR_API_BK.Check_Working_Shift(
1472 l_calendar_code
1473 , p_date
1474 , l_err_code
1475 , l_err_meg);
1476 ELSE
1477 --
1478 -- Debug Statements
1479 --
1480 IF l_debug_on THEN
1481 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit BOM_CALENDAR_API_BK.CHECK_WORKING_DAY',WSH_DEBUG_SV.C_PROC_LEVEL);
1482 END IF;
1483 --
1484 BOM_CALENDAR_API_BK.Check_Working_Day(
1485 l_calendar_code
1486 ,p_date
1487 ,p_date_is_valid
1488 ,l_err_code
1489 ,l_err_meg);
1490 END IF;
1491 END IF;
1492
1493 -- <end procedure logic>
1494 --
1495 -- Debug Statements
1496 --
1497 IF l_debug_on THEN
1498 WSH_DEBUG_SV.pop(l_module_name);
1499 END IF;
1500 --
1501 EXCEPTION
1502 WHEN NO_DATA_FOUND THEN
1503 p_date_is_valid := TRUE;
1504 --
1505 -- Debug Statements
1506 --
1507 IF l_debug_on THEN
1508 WSH_DEBUG_SV.logmsg(l_module_name,'NO_DATA_FOUND exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1509 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:NO_DATA_FOUND');
1510 END IF;
1511 --
1512 WHEN OTHERS THEN
1513 RAISE;
1514 --
1515 -- Debug Statements
1516 --
1517 IF l_debug_on THEN
1518 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1519 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1520 END IF;
1521 --
1522 END Single_Date;
1523 END WSH_CAL_ASG_VALIDATIONS;
[Home] [Help]