1 PACKAGE BODY WSH_CAL_ASG_VALIDATIONS AS
2 -- $Header: WSHCAVLB.pls 120.2 2007/11/02 06:22:32 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
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)
82 -- p_freight_code Carrier code (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 );
221 --
222 -- Debug Statements
223 --
224 IF l_debug_on THEN
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
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
343 --========================================================================
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
358 , p_freight_org_id IN NUMBER
355 , p_ship_customer_site_use_id IN NUMBER := NULL
356 , p_ship_time_matters IN BOOLEAN
357 , p_freight_code IN VARCHAR2
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 -- <insert here your local variables declaration>
376 --
377 l_debug_on BOOLEAN;
378 --
379 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'TRANSPORT_DATES';
380 --
381 BEGIN
382 -- Standard call to check for call compatibility
383 --
384 -- Debug Statements
385 --
386 --
387 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
388 --
389 IF l_debug_on IS NULL
390 THEN
391 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
392 END IF;
393 --
394 IF l_debug_on THEN
395 WSH_DEBUG_SV.push(l_module_name);
396 --
397 WSH_DEBUG_SV.log(l_module_name,'P_API_VERSION_NUMBER',P_API_VERSION_NUMBER);
398 WSH_DEBUG_SV.log(l_module_name,'P_INIT_MSG_LIST',P_INIT_MSG_LIST);
399 WSH_DEBUG_SV.log(l_module_name,'P_PRIORITY',P_PRIORITY);
400 WSH_DEBUG_SV.log(l_module_name,'P_SHIP_DATE',P_SHIP_DATE);
401 WSH_DEBUG_SV.log(l_module_name,'P_SHIP_ASSOC_TYPE',P_SHIP_ASSOC_TYPE);
402 WSH_DEBUG_SV.log(l_module_name,'P_SHIP_LOCATION_ID',P_SHIP_LOCATION_ID);
403 WSH_DEBUG_SV.log(l_module_name,'P_SHIP_VENDOR_SITE_ID',P_SHIP_VENDOR_SITE_ID);
404 WSH_DEBUG_SV.log(l_module_name,'P_SHIP_CUSTOMER_SITE_USE_ID',P_SHIP_CUSTOMER_SITE_USE_ID);
405 WSH_DEBUG_SV.log(l_module_name,'P_SHIP_TIME_MATTERS',P_SHIP_TIME_MATTERS);
406 WSH_DEBUG_SV.log(l_module_name,'P_FREIGHT_CODE',P_FREIGHT_CODE);
407 WSH_DEBUG_SV.log(l_module_name,'P_FREIGHT_ORG_ID',P_FREIGHT_ORG_ID);
408 WSH_DEBUG_SV.log(l_module_name,'P_RECEIVE_DATE',P_RECEIVE_DATE);
409 WSH_DEBUG_SV.log(l_module_name,'P_RECEIVE_ASSOC_TYPE',P_RECEIVE_ASSOC_TYPE);
410 WSH_DEBUG_SV.log(l_module_name,'P_RECEIVE_LOCATION_ID',P_RECEIVE_LOCATION_ID);
411 WSH_DEBUG_SV.log(l_module_name,'P_RECEIVE_VENDOR_SITE_ID',P_RECEIVE_VENDOR_SITE_ID);
412 WSH_DEBUG_SV.log(l_module_name,'P_RECEIVE_CUSTOMER_SITE_USE_ID',P_RECEIVE_CUSTOMER_SITE_USE_ID);
413 WSH_DEBUG_SV.log(l_module_name,'P_RECEIVE_TIME_MATTERS',P_RECEIVE_TIME_MATTERS);
414 WSH_DEBUG_SV.log(l_module_name,'P_PRIMARY_THRESHOLD',P_PRIMARY_THRESHOLD);
415 WSH_DEBUG_SV.log(l_module_name,'P_SECONDARY_THRESHOLD',P_SECONDARY_THRESHOLD);
416 END IF;
417 --
418 IF NOT FND_API.Compatible_API_Call
419 ( l_api_version_number
420 , p_api_version_number
421 , l_api_name
422 , G_PKG_NAME
423 )
424 THEN
425 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
426 END IF;
427
428 -- Initialize message stack if required
429 IF FND_API.to_Boolean(p_init_msg_list)
430 THEN
431 FND_MSG_PUB.initialize;
432 END IF;
433
434 -- BEGIN PROCEDURE LOGIC
435 x_suggest_ship_date := p_ship_date;
436 x_suggest_receive_date := p_receive_date;
437 x_return_status := FND_API.G_RET_STS_SUCCESS;
438
439 IF p_ship_date IS NULL THEN
440 IF p_receive_date IS NULL THEN
441 -- no dates to validate
442 RAISE wsh_missing_mandatory_attr;
443 ELSE
444 -- validate receive date
445 --
446 -- Debug Statements
447 --
448 IF l_debug_on THEN
449 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_CAL_ASG_VALIDATIONS.FIND_VALID_DATE',WSH_DEBUG_SV.C_PROC_LEVEL);
450 END IF;
451 --
452 WSH_CAL_ASG_VALIDATIONS.Find_Valid_Date
453 ( p_receive_date
454 , 'RECEIVING'
455 , p_receive_assoc_type
456 , p_receive_location_id
457 , p_receive_vendor_site_id
458 , p_receive_customer_site_use_id
459 , p_freight_code
460 , p_freight_org_id
461 , x_suggest_receive_date
462 , l_found_good_date
463 , p_primary_threshold
464 , 'BACK'
465 , p_receive_time_matters
466 );
467 IF l_found_good_date THEN
468 --
469 -- Debug Statements
470 --
471 IF l_debug_on THEN
472 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_CAL_ASG_VALIDATIONS.SINGLE_DATE',WSH_DEBUG_SV.C_PROC_LEVEL);
473 END IF;
474 --
475 WSH_CAL_ASG_VALIDATIONS.Single_Date(
476 x_suggest_receive_date
477 , 'CARRIER'
478 , 'CARRIER'
479 , p_receive_location_id
480 , p_receive_vendor_site_id
481 , p_receive_customer_site_use_id
485 , p_receive_time_matters
482 , p_freight_code
483 , p_freight_org_id
484 , l_found_good_date
486 );
487 IF l_found_good_date THEN
488 IF x_suggest_receive_date = p_receive_date THEN
489 x_return_code := 0;
490 ELSE
491 x_return_code := 1;
492 END IF;
493 ELSE
494 x_return_code := 3;
495 END IF;
496 ELSE
497 x_return_code := 5;
498 END IF;
499 END IF;
500 ELSE
501 IF p_receive_date IS NULL THEN
502 -- validate ship date
503 --
504 -- Debug Statements
505 --
506 IF l_debug_on THEN
507 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_CAL_ASG_VALIDATIONS.FIND_VALID_DATE',WSH_DEBUG_SV.C_PROC_LEVEL);
508 END IF;
509 --
510 WSH_CAL_ASG_VALIDATIONS.Find_Valid_Date
511 ( p_ship_date
512 , 'SHIPPING'
513 , p_ship_assoc_type
514 , p_ship_location_id
515 , p_ship_vendor_site_id
516 , p_ship_customer_site_use_id
517 , p_freight_code
518 , p_freight_org_id
519 , x_suggest_ship_date
520 , l_found_good_date
521 , p_primary_threshold
522 , 'BACK'
523 , p_ship_time_matters
524 );
525 IF l_found_good_date THEN
526 --
527 -- Debug Statements
528 --
529 IF l_debug_on THEN
530 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_CAL_ASG_VALIDATIONS.SINGLE_DATE',WSH_DEBUG_SV.C_PROC_LEVEL);
531 END IF;
532 --
533 WSH_CAL_ASG_VALIDATIONS.Single_Date(
534 x_suggest_ship_date
535 , 'CARRIER'
536 , 'CARRIER'
537 , p_ship_location_id
538 , p_ship_vendor_site_id
539 , p_ship_customer_site_use_id
540 , p_freight_code
541 , p_freight_org_id
542 , l_found_good_date
543 , p_ship_time_matters
544 );
545 IF l_found_good_date THEN
546 IF x_suggest_ship_date = p_ship_date THEN
547 x_return_code := 0;
548 ELSE
549 x_return_code := 1;
550 END IF;
551 ELSE
552 x_return_code := 3;
553 END IF;
554 ELSE
555 x_return_code := 5;
556 END IF;
557 ELSE
558 -- Need to validate both dates
559 l_lead_time := p_receive_date - p_ship_date;
560 IF l_lead_time < 0 THEN
561 RAISE wsh_bad_parameter;
562 END IF;
563 IF p_priority = 'SHIP' THEN
564 -- find valid ship date first
565 --
566 -- Debug Statements
567 --
568 IF l_debug_on THEN
569 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_CAL_ASG_VALIDATIONS.FIND_VALID_DATE',WSH_DEBUG_SV.C_PROC_LEVEL);
570 END IF;
571 --
572 WSH_CAL_ASG_VALIDATIONS.Find_Valid_Date
573 ( p_ship_date
574 , 'SHIPPING'
575 , p_ship_assoc_type
576 , p_ship_location_id
577 , p_ship_vendor_site_id
578 , p_ship_customer_site_use_id
579 , p_freight_code
580 , p_freight_org_id
581 , x_suggest_ship_date
582 , l_found_good_date
583 , p_primary_threshold
584 , 'FORWARD'
585 , p_ship_time_matters
586 );
587 IF l_found_good_date THEN
588 --
589 -- Debug Statements
590 --
591 IF l_debug_on THEN
592 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_CAL_ASG_VALIDATIONS.FIND_VALID_DATE',WSH_DEBUG_SV.C_PROC_LEVEL);
593 END IF;
594 --
595 WSH_CAL_ASG_VALIDATIONS.Find_Valid_Date
596 ( p_receive_date - (p_ship_date - x_suggest_ship_date)
597 , 'RECEIVING'
598 , p_receive_assoc_type
599 , p_receive_location_id
600 , p_receive_vendor_site_id
601 , p_receive_customer_site_use_id
602 , p_freight_code
603 , p_freight_org_id
604 , x_suggest_receive_date
605 , l_found_good_date
606 , p_secondary_threshold
607 , 'FORWARD'
608 , p_receive_time_matters
609 );
610 IF l_found_good_date THEN
611 IF (p_receive_date - x_suggest_receive_date)
612 <= p_secondary_threshold THEN
613 IF x_suggest_receive_date - x_suggest_ship_date
614 = l_lead_time THEN
615 IF x_suggest_ship_date = p_ship_date THEN
616 x_return_code := 0;
617 ELSE
618 x_return_code := 1;
619 END IF;
620 ELSE
621 x_return_code := 2;
622 END IF;
623 ELSE
624 x_return_code := 4;
625 END IF;
626 ELSE
627 x_return_code := 4;
628 END IF;
629 ELSE
633 --
630 x_return_code := 5;
631 END IF;
632 IF x_return_code IN (0,1,2) THEN
634 -- Debug Statements
635 --
636 IF l_debug_on THEN
637 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_CAL_ASG_VALIDATIONS.SINGLE_DATE',WSH_DEBUG_SV.C_PROC_LEVEL);
638 END IF;
639 --
640 WSH_CAL_ASG_VALIDATIONS.Single_Date(
641 x_suggest_ship_date
642 , 'CARRIER'
643 , p_ship_assoc_type
644 , p_ship_location_id
645 , p_ship_vendor_site_id
646 , p_ship_customer_site_use_id
647 , p_freight_code
648 , p_freight_org_id
649 , l_found_good_date
650 , p_ship_time_matters
651 );
652 IF l_found_good_date then
653 --
654 -- Debug Statements
655 --
656 IF l_debug_on THEN
657 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_CAL_ASG_VALIDATIONS.SINGLE_DATE',WSH_DEBUG_SV.C_PROC_LEVEL);
658 END IF;
659 --
660 WSH_CAL_ASG_VALIDATIONS.Single_Date(
661 x_suggest_receive_date
662 , 'CARRIER'
663 , p_receive_assoc_type
664 , p_receive_location_id
665 , p_receive_vendor_site_id
666 , p_receive_customer_site_use_id
667 , p_freight_code
668 , p_freight_org_id
669 , l_found_good_date
670 , p_receive_time_matters
671 );
672 IF l_found_good_date then
673 x_return_code := x_return_code;
674 ELSE
675 x_return_code := 3;
676 END IF;
677 ELSE
678 x_return_code := 3;
679 END IF;
680 END IF;
681 ELSIF p_priority = 'RECEIVE' THEN
682 -- find valid receive date first
683 --
684 -- Debug Statements
685 --
686 IF l_debug_on THEN
687 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_CAL_ASG_VALIDATIONS.FIND_VALID_DATE',WSH_DEBUG_SV.C_PROC_LEVEL);
688 END IF;
689 --
690 WSH_CAL_ASG_VALIDATIONS.Find_Valid_Date
691 ( p_receive_date
692 , 'RECEIVING'
693 , p_receive_assoc_type
694 , p_receive_location_id
695 , p_receive_vendor_site_id
696 , p_receive_customer_site_use_id
697 , p_freight_code
698 , p_freight_org_id
699 , x_suggest_receive_date
700 , l_found_good_date
701 , p_primary_threshold
702 , 'BACK'
703 , p_receive_time_matters
704 );
705 IF l_found_good_date THEN
706 --
707 -- Debug Statements
708 --
709 IF l_debug_on THEN
710 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_CAL_ASG_VALIDATIONS.FIND_VALID_DATE',WSH_DEBUG_SV.C_PROC_LEVEL);
711 END IF;
712 --
713 WSH_CAL_ASG_VALIDATIONS.Find_Valid_Date
714 ( p_ship_date - (p_receive_date - x_suggest_receive_date)
715 , 'SHIPPING'
716 , p_ship_assoc_type
717 , p_ship_location_id
718 , p_ship_vendor_site_id
719 , p_ship_customer_site_use_id
720 , p_freight_code
721 , p_freight_org_id
722 , x_suggest_ship_date
723 , l_found_good_date
724 , p_secondary_threshold
725 , 'BACK'
726 , p_ship_time_matters
727 );
728 IF l_found_good_date THEN
729 IF (p_ship_date - x_suggest_ship_date)
730 <= p_secondary_threshold THEN
731 IF x_suggest_receive_date - x_suggest_ship_date
732 = l_lead_time THEN
733 IF x_suggest_ship_date = p_ship_date THEN
734 x_return_code := 0;
735 ELSE
736 x_return_code := 1;
737 END IF;
738 ELSE
739 x_return_code := 2;
740 END IF;
741 ELSE
742 x_return_code := 4;
743 END IF;
744 ELSE
745 x_return_code := 4;
746 END IF;
747 ELSE
748 x_return_code := 5;
749 END IF;
750 IF x_return_code IN (0,1,2) THEN
751 --
752 -- Debug Statements
753 --
754 IF l_debug_on THEN
755 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_CAL_ASG_VALIDATIONS.SINGLE_DATE',WSH_DEBUG_SV.C_PROC_LEVEL);
756 END IF;
757 --
758 WSH_CAL_ASG_VALIDATIONS.Single_Date(
759 x_suggest_ship_date
760 , 'CARRIER'
761 , p_ship_assoc_type
762 , p_ship_location_id
763 , p_ship_vendor_site_id
764 , p_ship_customer_site_use_id
765 , p_freight_code
766 , p_freight_org_id
767 , l_found_good_date
768 , p_ship_time_matters
769 );
773 --
770 IF l_found_good_date then
771 --
772 -- Debug Statements
774 IF l_debug_on THEN
775 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_CAL_ASG_VALIDATIONS.SINGLE_DATE',WSH_DEBUG_SV.C_PROC_LEVEL);
776 END IF;
777 --
778 WSH_CAL_ASG_VALIDATIONS.Single_Date(
779 x_suggest_receive_date
780 , p_receive_assoc_type
781 , 'CARRIER'
782 , p_receive_location_id
783 , p_receive_vendor_site_id
784 , p_receive_customer_site_use_id
785 , p_freight_code
786 , p_freight_org_id
787 , l_found_good_date
788 , p_receive_time_matters
789 );
790 IF l_found_good_date then
791 x_return_code := x_return_code;
792 ELSE
793 x_return_code := 3;
794 END IF;
795 ELSE
796 x_return_code := 3;
797 END IF;
798 END IF;
799 ELSE
800 RAISE wsh_bad_parameter;
801 END IF;
802 END IF;
803 END IF;
804 -- END PROCEDURE LOGIC
805 -- report success
806 x_return_status := FND_API.G_RET_STS_SUCCESS;
807 FND_MSG_PUB.Count_And_Get
808 ( p_count => x_msg_count
809 , p_data => x_msg_data
810 );
811 --
812 -- Debug Statements
813 --
814 IF l_debug_on THEN
815 WSH_DEBUG_SV.pop(l_module_name);
816 END IF;
817 --
818 EXCEPTION
819 -- If we get no calendars to validate against, return success
820 WHEN wsh_missing_mandatory_attr THEN
821 x_return_code := 0;
822 -- If we get bad parameters, return error
823 --
824 -- Debug Statements
825 --
826 IF l_debug_on THEN
827 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_MISSING_MANDATORY_ATTR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
828 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_MISSING_MANDATORY_ATTR');
829 END IF;
830 --
831 WHEN wsh_bad_parameter THEN
832 x_return_code := 6;
833
834 --
835 -- Debug Statements
836 --
837 IF l_debug_on THEN
838 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_BAD_PARAMETER exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
839 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_BAD_PARAMETER');
840 END IF;
841 --
842 WHEN FND_API.G_EXC_ERROR THEN
843 x_return_status := FND_API.G_RET_STS_ERROR;
844 -- Get message count and data
845 FND_MSG_PUB.Count_And_Get
846 ( p_count => x_msg_count
847 , p_data => x_msg_data
848 );
849
850 --
851 -- Debug Statements
852 --
853 IF l_debug_on THEN
854 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
855 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
856 END IF;
857 --
858 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
859 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
860 -- Get message count and data
861 FND_MSG_PUB.Count_And_Get
862 ( p_count => x_msg_count
863 , p_data => x_msg_data
864 );
865
866 --
867 -- Debug Statements
868 --
869 IF l_debug_on THEN
870 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_UNEXPECTED_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
871 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
872 END IF;
873 --
874 WHEN OTHERS THEN
875 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
876 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
877 THEN
878 FND_MSG_PUB.Add_Exc_Msg
879 ( G_PKG_NAME
880 , '_x_'
881 );
882 END IF;
883 -- Get message count and data
884 FND_MSG_PUB.Count_And_Get
885 ( p_count => x_msg_count
886 , p_data => x_msg_data
887 );
888 --
889 -- Debug Statements
890 --
891 IF l_debug_on THEN
892 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
893 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
894 END IF;
895 --
896 END Transport_Dates;
897 --========================================================================
898 -- PROCEDURE : Find_Valid_Date PRIVATE
899 -- PARAMETERS:
900 -- p_input_date Date to validate
901 -- p_calendar_type Calendar type -- SHIPPING, RECEIVING, or
902 -- CARRIER
903 -- p_location_id Location ID (optional)
904 -- p_vendor_site_id Vendor Site ID (optional)
905 -- p_customer_site_use_id Customer Site Use ID (optional)
906 -- p_carrier_code Carrier code (optional)
907 -- p_carrier_org_id Carrier Organization ID (optional)
908 -- x_suggest_date Suggested valid date
909 -- x_success Did we find a date?
910 -- p_threshold Number of dates to try before failure
911 -- p_which_way search FORWARD or BACK
912 -- p_time_matters Is time important for this date?
916 -- nearby dates against a given calendar. Decrement if
913 -- VERSION : current version 1.0
914 -- initial version 1.0
915 -- COMMENT : Searches for a valid date near a given date by checking
917 -- finding ship date, increment if finding receive date
918 --========================================================================
919 PROCEDURE Find_Valid_Date
920 ( p_input_date IN DATE
921 , p_calendar_type IN VARCHAR2
922 , p_assoc_type IN VARCHAR2
923 , p_location_id IN NUMBER
924 , p_vendor_site_id IN NUMBER
925 , p_customer_site_use_id IN NUMBER
926 , p_freight_code IN VARCHAR2
927 , p_freight_org_id IN NUMBER
928 , x_suggest_date OUT NOCOPY DATE
929 , x_success OUT NOCOPY BOOLEAN
930 , p_threshold IN NUMBER
931 , p_which_way IN VARCHAR2
932 , p_time_matters IN BOOLEAN
933 ) IS
934 l_api_name CONSTANT VARCHAR2(30):= 'Find_Valid_Date';
935 l_number_tried NUMBER := 0;
936 --
937 l_debug_on BOOLEAN;
938 --
939 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'FIND_VALID_DATE';
940 --
941 BEGIN
942 --
943 -- Debug Statements
944 --
945 --
946 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
947 --
948 IF l_debug_on IS NULL
949 THEN
950 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
951 END IF;
952 --
953 IF l_debug_on THEN
954 WSH_DEBUG_SV.push(l_module_name);
955 --
956 WSH_DEBUG_SV.log(l_module_name,'P_INPUT_DATE',P_INPUT_DATE);
957 WSH_DEBUG_SV.log(l_module_name,'P_CALENDAR_TYPE',P_CALENDAR_TYPE);
958 WSH_DEBUG_SV.log(l_module_name,'P_ASSOC_TYPE',P_ASSOC_TYPE);
959 WSH_DEBUG_SV.log(l_module_name,'P_LOCATION_ID',P_LOCATION_ID);
960 WSH_DEBUG_SV.log(l_module_name,'P_VENDOR_SITE_ID',P_VENDOR_SITE_ID);
961 WSH_DEBUG_SV.log(l_module_name,'P_CUSTOMER_SITE_USE_ID',P_CUSTOMER_SITE_USE_ID);
962 WSH_DEBUG_SV.log(l_module_name,'P_FREIGHT_CODE',P_FREIGHT_CODE);
963 WSH_DEBUG_SV.log(l_module_name,'P_FREIGHT_ORG_ID',P_FREIGHT_ORG_ID);
964 WSH_DEBUG_SV.log(l_module_name,'P_THRESHOLD',P_THRESHOLD);
965 WSH_DEBUG_SV.log(l_module_name,'P_WHICH_WAY',P_WHICH_WAY);
966 WSH_DEBUG_SV.log(l_module_name,'P_TIME_MATTERS',P_TIME_MATTERS);
967 END IF;
968 --
969 x_success := FALSE;
970 WHILE nvl(x_success,FALSE) = FALSE AND l_number_tried <= p_threshold LOOP
971 IF p_which_way = 'FORWARD' THEN
972 x_suggest_date := p_input_date + l_number_tried;
973 ELSE
974 x_suggest_date := p_input_date - l_number_tried;
975 END IF;
976 --
977 -- Debug Statements
978 --
979 IF l_debug_on THEN
980 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_CAL_ASG_VALIDATIONS.SINGLE_DATE',WSH_DEBUG_SV.C_PROC_LEVEL);
981 END IF;
982 --
983 WSH_CAL_ASG_VALIDATIONS.Single_Date(
984 x_suggest_date
985 , p_calendar_type
986 , p_assoc_type
987 , p_location_id
988 , p_vendor_site_id
989 , p_customer_site_use_id
990 , p_freight_code
991 , p_freight_org_id
992 , x_success
993 , p_time_matters
994 );
995
996 l_number_tried := l_number_tried + 1;
997 END LOOP;
998
999 IF NOT x_success THEN
1000 x_suggest_date := p_input_date;
1001 END IF;
1002 -- <end of API logic>
1003 --
1004 -- Debug Statements
1005 --
1006 IF l_debug_on THEN
1007 WSH_DEBUG_SV.pop(l_module_name);
1008 END IF;
1009 --
1010 END Find_Valid_Date;
1011 --========================================================================
1012 -- PROCEDURE : Single_Date PRIVATE
1013 -- PARAMETERS:
1014 -- p_date Date to validate
1015 -- p_calendar_type Calendar type -- SHIPPING, RECEIVING, or
1016 -- CARRIER
1017 -- p_assoc_type Type of site
1018 -- p_location_id Location ID (optional)
1019 -- p_vendor_site_id Vendor Site ID (optional)
1020 -- p_customer_site_use_id Customer Site Use ID (optional)
1021 -- p_freight_code Carrier code (optional)
1022 -- p_freight_org_id Carrier Organization ID (optional)
1023 -- p_date_is_valid Return information
1024 -- p_time_matters Is time important for this date?
1025 -- VERSION : current version 1.0
1026 -- initial version 1.0
1027 -- COMMENT : Validates a shipping or receiving date against a shipping
1028 -- calendar and against a carrier calendar
1029 --========================================================================
1030 PROCEDURE Single_Date
1031 ( p_date IN DATE
1032 , p_calendar_type IN VARCHAR2
1033 , p_assoc_type IN VARCHAR2
1034 , p_location_id IN NUMBER
1035 , p_vendor_site_id IN NUMBER
1036 , p_customer_site_use_id IN NUMBER
1037 , p_freight_code IN VARCHAR2
1038 , p_freight_org_id IN NUMBER
1039 , p_date_is_valid OUT NOCOPY BOOLEAN
1040 , p_time_matters IN BOOLEAN
1041 )
1042 IS
1043 l_api_name CONSTANT VARCHAR2(30):= 'Single_Date';
1044 l_calendar_code VARCHAR2(10);
1048 l_entity_type VARCHAR2(12);
1045 l_err_code NUMBER;
1046 l_err_meg VARCHAR(200);
1047 l_enabled_flag VARCHAR2(1);
1049 l_site_id NUMBER;
1050 l_freight_code VARCHAR2(30);--6156495:Local variable added
1051
1052 CURSOR c_get_calendar_code IS
1053 SELECT WCA.CALENDAR_CODE,
1054 WCA.ENABLED_FLAG
1055 FROM WSH_CALENDAR_ASSIGNMENTS WCA ,
1056 PO_VENDORS POV ,
1057 PO_VENDOR_SITES_all POVS ,
1058 hr_organization_units HR
1059 WHERE WCA.ASSOCIATION_TYPE = 'VENDOR_SITE'
1060 AND WCA.VENDOR_SITE_ID = POVS.VENDOR_SITE_ID
1061 AND POV.VENDOR_ID = POVS.VENDOR_ID
1062 AND hr.organization_id = povs.org_id
1063 ---
1064 AND 'VENDOR' = l_entity_type
1065 AND WCA.CALENDAR_TYPE = p_calendar_type
1066 AND WCA.ASSOCIATION_TYPE = p_assoc_type
1067 AND NVL(WCA.FREIGHT_CODE,'All') = NVL(p_freight_code,'All')
1068 AND ( POVS.VENDOR_SITE_ID = l_site_id OR
1069 WCA.LOCATION_ID = p_location_id)
1070 UNION ALL
1071
1072 SELECT WCA.CALENDAR_CODE,
1073 WCA.ENABLED_FLAG
1074 FROM WSH_CALENDAR_ASSIGNMENTS WCA,
1075 hz_relationships rel,
1076 hz_party_sites hps,
1077 hz_party_site_uses hpsu,
1078 wsh_locations wl
1079 WHERE WCA.ASSOCIATION_TYPE = 'VENDOR_LOCATION'
1080 AND WCA.LOCATION_ID = WL.WSH_LOCATION_ID
1081 AND hps.party_id = rel.subject_id
1082 AND hps.party_site_id = hpsu.party_site_id
1083 AND hps.location_id = wl.wsh_location_id
1084 AND site_use_type = 'SUPPLIER_SHIP_FROM'
1085 AND rel.relationship_type = 'POS_VENDOR_PARTY'
1086 AND rel.object_table_name = 'PO_VENDORS'
1087 AND rel.object_type = 'POS_VENDOR'
1088 AND rel.subject_table_name = 'HZ_PARTIES'
1089 AND rel.subject_type = 'ORGANIZATION'
1090 ----
1091 AND 'VENDOR' = l_entity_type
1092 AND WCA.CALENDAR_TYPE = p_calendar_type
1093 AND WCA.ASSOCIATION_TYPE = p_assoc_type
1094 AND NVL(WCA.FREIGHT_CODE,'All') = NVL(p_freight_code,'All')
1095 AND ( WL.WSH_LOCATION_ID = l_site_id OR
1096 WCA.LOCATION_ID = p_location_id)
1097 UNION ALL
1098 SELECT WCA.CALENDAR_CODE,
1099 WCA.ENABLED_FLAG
1100 FROM HZ_CUST_ACCT_SITES_ALL HCAS,
1101 HZ_CUST_SITE_USES_ALL HCSU,
1102 HZ_PARTY_SITES HPS,
1103 HZ_CUST_ACCOUNTS HCA,
1104 WSH_LOCATIONS WLO,
1105 WSH_CALENDAR_ASSIGNMENTS WCA
1106 WHERE HCSU.CUST_ACCT_SITE_ID = HCAS.CUST_ACCT_SITE_ID
1107 and HCAS.PARTY_SITE_ID = HPS.PARTY_SITE_ID
1108 AND HCAS.cust_account_id = hca.cust_account_id
1109 AND HPS.LOCATION_ID = WLO.source_location_id(+)
1110 AND WLO.location_source_code(+) ='HZ'
1111 AND WCA.CUSTOMER_SITE_USE_ID = HCSU.SITE_USE_ID
1112 AND WCA.ASSOCIATION_TYPE = 'CUSTOMER_SITE'
1113 ----
1114 AND 'CUSTOMER' = l_entity_type
1115 AND WCA.CALENDAR_TYPE = p_calendar_type
1116 AND WCA.ASSOCIATION_TYPE = p_assoc_type
1117 -- AND NVL(WCA.FREIGHT_CODE,'All') = NVL(p_freight_code,'All')--6156495
1118 AND NVL(WCA.FREIGHT_CODE,'All') = NVL(l_freight_code,'All')
1119 AND ( HCSU.SITE_USE_ID = l_site_id OR
1120 WCA.LOCATION_ID = p_location_id)
1121 UNION ALL
1122
1123 SELECT WCA.CALENDAR_CODE,
1124 WCA.ENABLED_FLAG
1125 FROM WSH_CALENDAR_ASSIGNMENTS WCA ,
1126 WSH_LOCATIONS WLO
1127 WHERE WCA.ASSOCIATION_TYPE = 'HR_LOCATION'
1128 AND WCA.LOCATION_ID = WLO.WSH_LOCATION_ID(+)
1129 ----
1130 AND 'ORGANIZATION' = l_entity_type
1131 AND WCA.CALENDAR_TYPE = p_calendar_type
1132 AND WCA.ASSOCIATION_TYPE = p_assoc_type
1133 -- AND NVL(WCA.FREIGHT_CODE,'All') = NVL(p_freight_code,'All')---6156495
1134 AND NVL(WCA.FREIGHT_CODE,'All') = NVL(l_freight_code,'All')
1135 AND (WLO.WSH_LOCATION_ID = l_site_id OR
1136 WCA.LOCATION_ID =p_location_id)
1137 UNION ALL
1138 SELECT WCA.CALENDAR_CODE,
1139 WCA.ENABLED_FLAG
1140 FROM WSH_CALENDAR_ASSIGNMENTS WCA ,
1141 HZ_PARTY_SITES HPS
1142 WHERE WCA.ASSOCIATION_TYPE = 'CARRIER_SITE'
1143 AND WCA.CARRIER_ID = HPS.PARTY_ID
1144 AND WCA.CARRIER_SITE_ID = HPS.PARTY_SITE_ID
1145 ----
1146 AND 'CARRIER' = l_entity_type
1147 AND WCA.CALENDAR_TYPE = p_calendar_type
1148 AND WCA.ASSOCIATION_TYPE = p_assoc_type
1149 AND NVL(WCA.FREIGHT_CODE,'All') = NVL(p_freight_code,'All')
1150 AND (WCA.CARRIER_SITE_ID = l_site_id OR
1151 WCA.LOCATION_ID =p_location_id);
1152
1153
1154
1155
1156
1157
1158
1159
1160
1161 --
1162 l_debug_on BOOLEAN;
1163 --
1164 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'SINGLE_DATE';
1165 --
1166 BEGIN
1167 -- <begin procedure logic>
1168 --
1169 -- Debug Statements
1170 --
1171 --
1172 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1173 --
1174 IF l_debug_on IS NULL
1175 THEN
1176 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1177 END IF;
1178 --
1179 IF l_debug_on THEN
1180 WSH_DEBUG_SV.push(l_module_name);
1181 --
1182 WSH_DEBUG_SV.log(l_module_name,'P_DATE',P_DATE);
1183 WSH_DEBUG_SV.log(l_module_name,'P_CALENDAR_TYPE',P_CALENDAR_TYPE);
1184 WSH_DEBUG_SV.log(l_module_name,'P_ASSOC_TYPE',P_ASSOC_TYPE);
1188 WSH_DEBUG_SV.log(l_module_name,'P_FREIGHT_CODE',P_FREIGHT_CODE);
1185 WSH_DEBUG_SV.log(l_module_name,'P_LOCATION_ID',P_LOCATION_ID);
1186 WSH_DEBUG_SV.log(l_module_name,'P_VENDOR_SITE_ID',P_VENDOR_SITE_ID);
1187 WSH_DEBUG_SV.log(l_module_name,'P_CUSTOMER_SITE_USE_ID',P_CUSTOMER_SITE_USE_ID);
1189 WSH_DEBUG_SV.log(l_module_name,'P_FREIGHT_ORG_ID',P_FREIGHT_ORG_ID);
1190 WSH_DEBUG_SV.log(l_module_name,'P_TIME_MATTERS',P_TIME_MATTERS);
1191 END IF;
1192 --
1193 l_enabled_flag := 'N';
1194 l_calendar_code := NULL;
1195 l_freight_code := NULL;--6156495
1196
1197 IF p_assoc_type = 'CUSTOMER_SITE' THEN
1198 l_entity_type := 'CUSTOMER';
1199 l_site_id := p_customer_site_use_id;
1200 IF p_calendar_type ='CARRIER' THEN
1201 l_freight_code:= p_freight_code;--6156495
1202 END IF;
1203 ELSIF p_assoc_type = 'HR_LOCATION' THEN
1204 l_entity_type := 'ORGANIZATION';
1205 l_site_id := p_location_id;
1206 IF p_calendar_type ='CARRIER' THEN
1207 l_freight_code:= p_freight_code;--6156495
1208 END IF;
1209 ELSIF p_assoc_type = 'CARRIER_SITE' THEN
1210 l_entity_type := 'CARRIER';
1211 l_freight_code:= p_freight_code;--6156495
1212 ELSIF p_assoc_type in ('VENDOR_SITE', 'VENDOR_LOCATION') THEN
1213 l_entity_type := 'VENDOR';
1214 IF p_assoc_type = 'VENDOR_SITE' THEN
1215 l_site_id := p_vendor_site_id;
1216 ELSE
1217 l_site_id := p_location_id;
1218 END IF;
1219 END IF;
1220
1221 OPEN c_get_calendar_code;
1222 FETCH c_get_calendar_code INTO l_calendar_code, l_enabled_flag;
1223 CLOSE c_get_calendar_code;
1224
1225 p_date_is_valid := TRUE;
1226
1227 IF l_calendar_code IS NOT NULL AND l_enabled_flag = 'Y' THEN
1228 IF p_time_matters THEN
1229 --
1230 -- Debug Statements
1231 --
1232 IF l_debug_on THEN
1233 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit BOM_CALENDAR_API_BK.CHECK_WORKING_SHIFT',WSH_DEBUG_SV.C_PROC_LEVEL);
1234 END IF;
1235 --
1236 p_date_is_valid := BOM_CALENDAR_API_BK.Check_Working_Shift(
1237 l_calendar_code
1238 , p_date
1239 , l_err_code
1240 , l_err_meg);
1241 ELSE
1242 --
1243 -- Debug Statements
1244 --
1245 IF l_debug_on THEN
1246 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit BOM_CALENDAR_API_BK.CHECK_WORKING_DAY',WSH_DEBUG_SV.C_PROC_LEVEL);
1247 END IF;
1248 --
1249 BOM_CALENDAR_API_BK.Check_Working_Day(
1250 l_calendar_code
1251 ,p_date
1252 ,p_date_is_valid
1253 ,l_err_code
1254 ,l_err_meg);
1255 END IF;
1256 END IF;
1257
1258 -- <end procedure logic>
1259 --
1260 -- Debug Statements
1261 --
1262 IF l_debug_on THEN
1263 WSH_DEBUG_SV.pop(l_module_name);
1264 END IF;
1265 --
1266 EXCEPTION
1267 WHEN NO_DATA_FOUND THEN
1268 p_date_is_valid := TRUE;
1269 --
1270 -- Debug Statements
1271 --
1272 IF l_debug_on THEN
1273 WSH_DEBUG_SV.logmsg(l_module_name,'NO_DATA_FOUND exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1274 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:NO_DATA_FOUND');
1275 END IF;
1276 --
1277 WHEN OTHERS THEN
1278 RAISE;
1279 --
1280 -- Debug Statements
1281 --
1282 IF l_debug_on THEN
1283 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1284 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1285 END IF;
1286 --
1287 END Single_Date;
1288 END WSH_CAL_ASG_VALIDATIONS;
[Home] [Help]