[Home] [Help]
PACKAGE BODY: APPS.WSH_ECE_VIEWS_DEF
Source
1 PACKAGE BODY WSH_ECE_VIEWS_DEF AS
2 /* $Header: WSHECVWB.pls 120.0.12010000.3 2009/11/24 11:44:57 brana ship $ */
3 --
4 G_PKG_NAME CONSTANT VARCHAR2(50) := 'WSH_ECE_VIEWS_DEF';
5 --
6 --FP Bug 3989208
7 G_PAYMENT_TERM_TBL wsh_util_core.char500_tab_type;
8 G_PAYMENT_TERM_EXT_TBL wsh_util_core.char500_tab_type;
9 --
10 FUNCTION get_cont_area_code(contact_id_in NUMBER) return VARCHAR2 IS
11 cont_area_code_x VARCHAR2(10);
12 --
13 l_debug_on BOOLEAN;
14 --
15 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_CONT_AREA_CODE';
16 --
17 BEGIN
18 --Fix for Bug 2378628
19 --
20 -- Debug Statements
21 --
22 --
23 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
24 --
25 IF l_debug_on IS NULL
26 THEN
27 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
28 END IF;
29 --
30 IF l_debug_on THEN
31 WSH_DEBUG_SV.push(l_module_name);
32 --
33 WSH_DEBUG_SV.log(l_module_name,'CONTACT_ID_IN',CONTACT_ID_IN);
34 END IF;
35 --
36 SELECT DISTINCT
37 contact.PHONE_AREA_CODE Area_Code,
38 contact.PHONE_NUMBER Phone_Number
39 INTO
40 cont_area_code_x,
41 WSH_ECE_VIEWS_DEF.cont_phone_number_x
42 FROM HZ_CONTACT_POINTS contact,
43 HZ_CUST_ACCOUNT_ROLES acct_roles
44 WHERE acct_roles.CUST_ACCOUNT_ROLE_ID = contact_id_in
45 AND contact.OWNER_TABLE_NAME = 'HZ_PARTIES'
46 AND acct_roles.PARTY_ID = contact.owner_table_id(+)
47 AND contact.CONTACT_POINT_TYPE IN ( 'PHONE', 'FAX', 'TELEX')
48 AND contact.PHONE_LINE_TYPE = 'GEN'
49 AND contact.PRIMARY_FLAG = 'Y';
50 --End of Fix for Bug 2378628
51
52 --
53 -- Debug Statements
54 --
55 IF l_debug_on THEN
56 WSH_DEBUG_SV.pop(l_module_name);
57 END IF;
58 --
59 return cont_area_code_x;
60
61 EXCEPTION WHEN OTHERS THEN
62 WSH_ECE_VIEWS_DEF.cont_phone_number_x := NULL;
63 cont_area_code_x := NULL;
64 --
65 -- Debug Statements
66 --
67 IF l_debug_on THEN
68 WSH_DEBUG_SV.pop(l_module_name);
69 END IF;
70 --
71 return cont_area_code_x;
72
73 --
74 -- Debug Statements
75 --
76 IF l_debug_on THEN
77 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
78 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
79 END IF;
80 --
81 END;
82
83 FUNCTION get_cont_phone_number RETURN VARCHAR2 IS
84 --
85 l_debug_on BOOLEAN;
86 --
87 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_CONT_PHONE_NUMBER';
88 --
89 BEGIN
90 --
91 -- Debug Statements
92 --
93 --
94 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
95 --
96 IF l_debug_on IS NULL
97 THEN
98 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
99 END IF;
100 --
101 IF l_debug_on THEN
102 WSH_DEBUG_SV.push(l_module_name);
103 END IF;
104 --
105 --
106 -- Debug Statements
107 --
108 IF l_debug_on THEN
109 WSH_DEBUG_SV.pop(l_module_name);
110 END IF;
111 --
112 return WSH_ECE_VIEWS_DEF.cont_phone_number_x;
113 END;
114
115 FUNCTION get_cust_area_code(customer_id_in NUMBER) return VARCHAR2 IS
116 cust_area_code_x VARCHAR2(10);
117 --
118 CURSOR l_cust_ph_num_csr(p_customer_id IN NUMBER ) IS
119 SELECT DISTINCT
120 contact.PHONE_AREA_CODE Area_Code,
121 contact.PHONE_NUMBER Phone_Number,
122 hcar.cust_acct_site_id acct_site_id
123 FROM HZ_CONTACT_POINTS contact,
124 HZ_CUST_ACCOUNT_ROLES hcar
125 WHERE hcar.CUST_ACCOUNT_ID = p_customer_id
126 AND contact.CONTACT_POINT_TYPE = 'PHONE'
127 AND contact.PRIMARY_FLAG = 'Y'
128 AND contact.OWNER_TABLE_NAME = 'HZ_PARTIES'
129 AND contact.OWNER_TABLE_ID = hcar.PARTY_ID
130 AND contact.PHONE_LINE_TYPE = 'GEN'
131 AND hcar.status = 'A'
132 order by nvl(hcar.cust_acct_site_id,-99999);
133
134 l_cust_acct_site_id NUMBER;
135 --
136 l_debug_on BOOLEAN;
137 --
138 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_CUST_AREA_CODE';
139 --
140 BEGIN
141 --Fix for Bug 2378628
142 --
143 -- Debug Statements
144 --
145 --
146 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
147 --
148 IF l_debug_on IS NULL
149 THEN
150 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
151 END IF;
152 --
153 IF l_debug_on THEN
154 WSH_DEBUG_SV.push(l_module_name);
155 --
156 WSH_DEBUG_SV.log(l_module_name,'CUSTOMER_ID_IN',CUSTOMER_ID_IN);
157 END IF;
158 --
159 OPEN l_cust_ph_num_csr(customer_id_in);
160 FETCH l_cust_ph_num_csr INTO cust_area_code_x, WSH_ECE_VIEWS_DEF.cust_phone_number_x, l_cust_acct_site_id;
161 CLOSE l_cust_ph_num_csr;
162 --
163 -- Debug Statements
164 --
165 IF l_debug_on THEN
166 WSH_DEBUG_SV.log(l_module_name,'cust_area_code_x',cust_area_code_x);
167 WSH_DEBUG_SV.log(l_module_name,'phone_number',WSH_ECE_VIEWS_DEF.cust_phone_number_x);
168 WSH_DEBUG_SV.pop(l_module_name);
169 END IF;
170 --
171 return cust_area_code_x;
172
173 EXCEPTION WHEN OTHERS THEN
174 WSH_ECE_VIEWS_DEF.cust_phone_number_x := NULL;
175 cust_area_code_x := NULL;
176 --
177 -- Debug Statements
178 --
179 IF l_debug_on THEN
180 WSH_DEBUG_SV.pop(l_module_name);
181 END IF;
182 --
183 return cust_area_code_x;
184 --
185 -- Debug Statements
186 --
187 IF l_debug_on THEN
188 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
189 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
190 END IF;
191 --
192 END;
193
194 FUNCTION get_cust_phone_number RETURN VARCHAR2 IS
195 --
196 l_debug_on BOOLEAN;
197 --
198 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_CUST_PHONE_NUMBER';
199 --
200 BEGIN
201 --
202 -- Debug Statements
203 --
204 --
205 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
206 --
207 IF l_debug_on IS NULL
208 THEN
209 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
210 END IF;
211 --
212 IF l_debug_on THEN
213 WSH_DEBUG_SV.push(l_module_name);
214 END IF;
215 --
216 --
217 -- Debug Statements
218 --
219 IF l_debug_on THEN
220 WSH_DEBUG_SV.pop(l_module_name);
221 END IF;
222 --
223 return WSH_ECE_VIEWS_DEF.cust_phone_number_x;
224 END;
225
226
227 PROCEDURE get_invoice_number(p_delivery_id IN NUMBER,
228 x_invoice_number OUT NOCOPY NUMBER) IS
229 l_lookup_code Varchar2(30) := NULL;
230 cursor c_lookup_value is
231 select lookup_code from oe_lookups where lookup_type
232 = 'INVOICE_NUMBER_METHOD' and meaning = 'Delivery Name';
233 --
234 l_debug_on BOOLEAN;
235 --
236 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_INVOICE_NUMBER';
237 --
238 BEGIN
239 --
240 -- Debug Statements
241 --
242 --
243 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
244 --
245 IF l_debug_on IS NULL
246 THEN
247 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
248 END IF;
249 --
250 IF l_debug_on THEN
251 WSH_DEBUG_SV.push(l_module_name);
252 --
253 WSH_DEBUG_SV.log(l_module_name,'P_DELIVERY_ID',P_DELIVERY_ID);
254 END IF;
255 --
256 x_invoice_number := NULL;
257 open c_lookup_value;
258 fetch c_lookup_value into l_lookup_code;
259 close c_lookup_value;
260 IF fnd_profile.value('WSH_INVOICE_NUMBERING_METHOD') = l_lookup_code THEN
261 x_invoice_number := p_delivery_id;
262 END IF;
263
264 --
265 -- Debug Statements
266 --
267 IF l_debug_on THEN
268 WSH_DEBUG_SV.pop(l_module_name);
269 END IF;
270 --
271 EXCEPTION WHEN OTHERS THEN
272 x_invoice_number := NULL;
273
274 --
275 -- Debug Statements
276 --
277 IF l_debug_on THEN
278 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
279 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
280 END IF;
281 --
282 END;
283
284 PROCEDURE get_vehicle_type_code(
285 p_vehicle_org_id IN NUMBER,
286 p_vehicle_item_id IN NUMBER,
287 x_vehicle_type_code OUT NOCOPY VARCHAR2 ) IS
288
289 CURSOR c_vehicle_item_type is
290 SELECT item_type FROM mtl_system_items WHERE
291 inventory_item_id = p_vehicle_item_id AND
292 organization_id = p_vehicle_org_id AND
293 vehicle_item_flag = 'Y' ;
294 --
295 l_debug_on BOOLEAN;
296 --
297 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_VEHICLE_TYPE_CODE';
298 --
299 BEGIN
300 --
301 -- Debug Statements
302 --
303 --
304 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
305 --
306 IF l_debug_on IS NULL
307 THEN
308 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
309 END IF;
310 --
311 IF l_debug_on THEN
312 WSH_DEBUG_SV.push(l_module_name);
313 --
314 WSH_DEBUG_SV.log(l_module_name,'P_VEHICLE_ORG_ID',P_VEHICLE_ORG_ID);
315 WSH_DEBUG_SV.log(l_module_name,'P_VEHICLE_ITEM_ID',P_VEHICLE_ITEM_ID);
316 END IF;
317 --
318 IF p_vehicle_org_id is NULL or p_vehicle_item_id is NULL THEN
319 x_vehicle_type_code := NULL;
320 ELSE
321 OPEN c_vehicle_item_type;
322 FETCH c_vehicle_item_type into x_vehicle_type_code;
323 IF (c_vehicle_item_type%NOTFOUND) THEN
324 x_vehicle_type_code := NULL;
325 END IF;
326 CLOSE c_vehicle_item_type;
327 END IF;
328
329 --
330 -- Debug Statements
331 --
332 IF l_debug_on THEN
333 WSH_DEBUG_SV.pop(l_module_name);
334 END IF;
335 --
336 EXCEPTION WHEN OTHERS THEN
337 x_vehicle_type_code := NULL;
338 --
339 -- Debug Statements
340 --
341 IF l_debug_on THEN
342 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
343 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
344 END IF;
345 --
346 END get_vehicle_type_code;
347
348 FUNCTION get_cust_payment_term(p_payment_term_id NUMBER) return VARCHAR2 IS
349
350 l_payment_term_name VARCHAR2(30) := NULL;
351 --Bug 3989208
352 l_return_status VARCHAR2(30);
353 --
354
355 CURSOR c_cust_payment_term is
356 SELECT Name FROM ra_terms
357 WHERE term_id = p_payment_term_id;
358 --
359 l_debug_on BOOLEAN;
360 --
361 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_CUST_PAYMENT_TERM';
362 --
363 BEGIN
364 --
365 -- Debug Statements
366 --
367 --
368 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
369 --
370 IF l_debug_on IS NULL
371 THEN
372 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
373 END IF;
374 --
375 IF l_debug_on THEN
376 WSH_DEBUG_SV.push(l_module_name);
377 --
378 WSH_DEBUG_SV.log(l_module_name,'P_PAYMENT_TERM_ID',P_PAYMENT_TERM_ID);
379 END IF;
380 --
381 IF p_payment_term_id is NULL THEN
382 --
383 -- Debug Statements
384 --
385 IF l_debug_on THEN
386 WSH_DEBUG_SV.pop(l_module_name);
387 END IF;
388 --
389 return NULL;
390 ELSE
391 --Bug 3989208
392 --Cache the payment_term_id and payment_term_name
393
394 wsh_util_core.get_cached_value(
395 p_cache_tbl => g_payment_term_tbl,
396 p_cache_ext_tbl => g_payment_term_ext_tbl,
397 p_value => l_payment_term_name,
398 p_key => p_payment_term_id,
399 p_action => 'GET',
400 x_return_status => l_return_status);
401
402 IF l_debug_on THEN
403 wsh_debug_sv.log(l_module_name, 'Cached l_payment_term_name', l_payment_term_name);
404 wsh_debug_sv.log(l_module_name, 'Get l_return_status', l_return_status);
405 END IF;
406
407 IF l_return_status = wsh_util_core.g_ret_sts_warning
408 THEN
409
410 OPEN c_cust_payment_term;
411 FETCH c_cust_payment_term into l_payment_term_name;
412 IF (c_cust_payment_term%NOTFOUND) THEN
413 l_payment_term_name := NULL;
414 END IF;
415 CLOSE c_cust_payment_term;
416
417 IF l_debug_on THEN
418 wsh_debug_sv.log(l_module_name, 'New l_payment_term_name', l_payment_term_name);
419 END IF;
420
421 --Bug 3989208
422 --Cache the payment_term_id and payment_term_name
423
424 wsh_util_core.get_cached_value(
425 p_cache_tbl => g_payment_term_tbl,
426 p_cache_ext_tbl => g_payment_term_ext_tbl,
427 p_value => l_payment_term_name,
428 p_key => p_payment_term_id,
429 p_action => 'PUT',
430 x_return_status => l_return_status);
431
432 IF l_debug_on THEN
433 wsh_debug_sv.log(l_module_name, 'Put l_return_status', l_return_status);
434 END IF;
435
436 END IF;
437 END IF;
438
439 --
440 -- Debug Statements
441 --
442 IF l_debug_on THEN
443 wsh_debug_sv.log(l_module_name, 'Returning l_payment_term_name=', l_payment_term_name);
444 WSH_DEBUG_SV.pop(l_module_name);
445 END IF;
446 --
447 return l_payment_term_name;
448
449 EXCEPTION WHEN OTHERS THEN
450 l_payment_term_name := NULL;
451 --
452 -- Debug Statements
453 --
454 IF l_debug_on THEN
455 WSH_DEBUG_SV.pop(l_module_name);
456 END IF;
457 --
458 return l_payment_term_name;
459 --
460 -- Debug Statements
461 --
462 IF l_debug_on THEN
463 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
464 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
465 END IF;
466 --
467 END;
468
469
470 procedure get_cross_reference(X_INVENTORY_ITEM_ID IN NUMBER,
471 X_ORGANIZATION_ID IN NUMBER,
472 X_CROSS_REFERENCE OUT NOCOPY VARCHAR2) IS
473
474 CURSOR C_GET_CROSS_REFERENCE_1 IS
475 SELECT CROSS_REFERENCE FROM MTL_CROSS_REFERENCES
476 WHERE INVENTORY_ITEM_ID = X_INVENTORY_ITEM_ID AND
477 ORG_INDEPENDENT_FLAG = 'N' AND ORGANIZATION_ID = X_ORGANIZATION_ID;
478
479 CURSOR C_GET_CROSS_REFERENCE_2 IS
480 SELECT CROSS_REFERENCE FROM MTL_CROSS_REFERENCES
481 WHERE INVENTORY_ITEM_ID = X_INVENTORY_ITEM_ID AND
482 ORG_INDEPENDENT_FLAG = 'Y';
483
484 l_cross_reference VARCHAR2(255) := NULL; --Bugfix 9125033 increase width to 255
485 --
486 l_debug_on BOOLEAN;
487 --
488 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_CROSS_REFERENCE';
489 --
490 BEGIN
491 --
492 -- Debug Statements
493 --
494 --
495 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
496 --
497 IF l_debug_on IS NULL
498 THEN
499 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
500 END IF;
501 --
502 IF l_debug_on THEN
503 WSH_DEBUG_SV.push(l_module_name);
504 --
505 WSH_DEBUG_SV.log(l_module_name,'X_INVENTORY_ITEM_ID',X_INVENTORY_ITEM_ID);
506 WSH_DEBUG_SV.log(l_module_name,'X_ORGANIZATION_ID',X_ORGANIZATION_ID);
507 END IF;
508 --
509 IF(X_INVENTORY_ITEM_ID IS NOT NULL ) THEN
510 open C_GET_CROSS_REFERENCE_1;
511 fetch C_GET_CROSS_REFERENCE_1 into l_cross_reference;
512 IF C_GET_CROSS_REFERENCE_1%NOTFOUND THEN
513 IF X_ORGANIZATION_ID IS NOT NULL THEN
514 open C_GET_CROSS_REFERENCE_2;
515 fetch C_GET_CROSS_REFERENCE_2 into l_cross_reference;
516 IF C_GET_CROSS_REFERENCE_2%NOTFOUND THEN
517 X_CROSS_REFERENCE := NULL;
518 ELSE
519 X_CROSS_REFERENCE := l_cross_reference;
520 END IF;
521 close C_GET_CROSS_REFERENCE_2;
522 ELSE
523 X_CROSS_REFERENCE := NULL;
524 END IF;
525 ELSE
526 X_CROSS_REFERENCE := l_cross_reference;
527 END IF;
528 close C_GET_CROSS_REFERENCE_1;
529 ELSE
530 X_CROSS_REFERENCE := NULL;
531 END IF;
532 --
533 -- Debug Statements
534 --
535 IF l_debug_on THEN
536 WSH_DEBUG_SV.pop(l_module_name);
537 END IF;
538 --
539 END;
540
541 procedure update_del_asn_info(X_DELIVERY_ID IN NUMBER,
542 X_TIME_STAMP_SEQUENCE_NUMBER IN OUT NOCOPY NUMBER,
543 X_TIME_STAMP_DATE IN OUT NOCOPY DATE,
544 X_G_TIME_STAMP_SEQUENCE_NUMBER IN OUT NOCOPY NUMBER,
545 X_G_TIME_STAMP_DATE IN OUT NOCOPY DATE) IS
546 --
547 l_debug_on BOOLEAN;
548 --
549 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UPDATE_DEL_ASN_INFO';
550 --
551 BEGIN
552
553 --
554 -- Debug Statements
555 --
556 --
557 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
558 --
559 IF l_debug_on IS NULL
560 THEN
561 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
562 END IF;
563 --
564 IF l_debug_on THEN
565 WSH_DEBUG_SV.push(l_module_name);
566 --
567 WSH_DEBUG_SV.log(l_module_name,'X_DELIVERY_ID',X_DELIVERY_ID);
568 WSH_DEBUG_SV.log(l_module_name,'X_TIME_STAMP_SEQUENCE_NUMBER',X_TIME_STAMP_SEQUENCE_NUMBER);
569 WSH_DEBUG_SV.log(l_module_name,'X_TIME_STAMP_DATE',X_TIME_STAMP_DATE);
570 WSH_DEBUG_SV.log(l_module_name,'X_G_TIME_STAMP_SEQUENCE_NUMBER',X_G_TIME_STAMP_SEQUENCE_NUMBER);
571 WSH_DEBUG_SV.log(l_module_name,'X_G_TIME_STAMP_DATE',X_G_TIME_STAMP_DATE);
572 END IF;
573 --
574 IF(X_TIME_STAMP_SEQUENCE_NUMBER IS NOT NULL AND X_G_TIME_STAMP_SEQUENCE_NUMBER IS NULL) THEN
575 X_G_TIME_STAMP_SEQUENCE_NUMBER := X_TIME_STAMP_SEQUENCE_NUMBER;
576 X_G_TIME_STAMP_DATE := sysdate;
577 X_TIME_STAMP_DATE := X_G_TIME_STAMP_DATE;
578 ELSIF(X_G_TIME_STAMP_SEQUENCE_NUMBER IS NOT NULL) THEN
579 X_TIME_STAMP_SEQUENCE_NUMBER := X_G_TIME_STAMP_SEQUENCE_NUMBER;
580 X_TIME_STAMP_DATE := X_G_TIME_STAMP_DATE;
581 ELSE
582 SELECT wsh_asn_seq_number_s.nextval,
583 sysdate
584 INTO X_G_TIME_STAMP_SEQUENCE_NUMBER,
585 X_G_TIME_STAMP_DATE
586 FROM dual;
587
588 X_TIME_STAMP_SEQUENCE_NUMBER := X_G_TIME_STAMP_SEQUENCE_NUMBER;
589 X_TIME_STAMP_DATE := X_G_TIME_STAMP_DATE;
590 END IF;
591
592
593 UPDATE WSH_NEW_DELIVERIES
594 SET asn_seq_number = X_G_TIME_STAMP_SEQUENCE_NUMBER,
595 asn_date_sent = X_G_TIME_STAMP_DATE
596 WHERE delivery_id = X_DELIVERY_ID;
597
598
599 --
600 -- Debug Statements
601 --
602 IF l_debug_on THEN
603 WSH_DEBUG_SV.pop(l_module_name);
604 END IF;
605 --
606 END;
607
608
609 procedure get_location_code (
610 p_location_id IN NUMBER,
611 x_location_code OUT NOCOPY VARCHAR2) IS
612
613 BEGIN
614
615 IF p_location_id IS NOT NULL THEN
616 x_location_code := wsh_util_core.get_location_description (
617 p_location_id => p_location_id,
618 p_format => 'CODE');
619 ELSE
620 x_location_code := NULL;
621
622 END IF;
623
624 END get_location_code;
625
626
627 procedure get_location_info (
628 p_location_id IN NUMBER,
629 p_delivery_id IN NUMBER,--Bug 7371411
630 x_location OUT NOCOPY VARCHAR2,
631 x_edi_loc_code OUT NOCOPY VARCHAR2,
632 x_tp_ref_ext1 OUT NOCOPY VARCHAR2,
633 x_tp_ref_ext2 OUT NOCOPY VARCHAR2,
634 x_customer_name OUT NOCOPY VARCHAR2,
635 x_address1 OUT NOCOPY VARCHAR2,
636 x_address2 OUT NOCOPY VARCHAR2,
637 x_address3 OUT NOCOPY VARCHAR2,
638 x_address4 OUT NOCOPY VARCHAR2,
639 x_city OUT NOCOPY VARCHAR2,
640 x_state OUT NOCOPY VARCHAR2,
641 x_postal_code OUT NOCOPY VARCHAR2,
642 x_country OUT NOCOPY VARCHAR2,
643 x_province OUT NOCOPY VARCHAR2,
644 x_county OUT NOCOPY VARCHAR2,
645 x_address_id OUT NOCOPY NUMBER,
646 x_area_code OUT NOCOPY VARCHAR2,
647 x_phone_number OUT NOCOPY VARCHAR2) IS
648
649 -- Bug# 7371411: Getting the operating unit value from DDs which are
650 -- associated to the given delivery. Same logic is being followed
651 -- for ship to locations ( pls refer the view wsh_dsno_deliveries_v)
652
653 CURSOR get_customer_info IS
654 SELECT CL.customer_id,
655 CL.location,
656 CL.tp_location_code_ext,
657 ECH.tp_reference_ext1,
658 ECH.tp_reference_ext2,
659 CL.customer_name,
660 CL.address1,
661 CL.address2,
662 CL.address3,
663 CL.address4,
664 CL.city,
665 CL.postal_code,
666 CL.country,
667 CL.state,
668 CL.province,
669 CL.county,
670 CL.address_id
671 FROM wsh_customer_locations_v CL , ece_tp_headers ECH
672 WHERE CL.tp_header_id = ECH.tp_header_id (+) AND
673 CL.wsh_location_id = p_location_id AND
674 CL.site_use_status = 'A' AND
675 CL.site_use_code = 'SHIP_TO' AND
676 ( CL.ORG_ID IS NULL OR CL.ORG_ID IN
677 ( SELECT first_value(wdd.org_id) over(ORDER BY COUNT(wdd.org_id) DESC ) AS ORG_ID
678 FROM wsh_delivery_assignments wda,
679 wsh_delivery_details wdd
680 WHERE wdd.delivery_detail_id = wda.delivery_detail_id AND
681 wda.delivery_id = p_delivery_id AND
682 wdd.container_flag = 'N'
683 GROUP BY org_id
684 )
685 );
686
687 l_customer_id NUMBER;
688 l_debug_on BOOLEAN;
689 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_LOCATION_INFO';
690
691 BEGIN
692
693 --
694 -- Debug Statements
695 --
696 --
697 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
698 --
699 IF l_debug_on IS NULL
700 THEN
701 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
702 END IF;
703 --
704 IF l_debug_on THEN
705
706 WSH_DEBUG_SV.push(l_module_name);
707 WSH_DEBUG_SV.log(l_module_name, 'p_location_id',p_location_id);
708 WSH_DEBUG_SV.log(l_module_name, 'p_delivery_id',p_delivery_id);
709
710 END IF;
711 --
712
713 IF p_location_id IS NOT NULL THEN
714 OPEN get_customer_info;
715 FETCH get_customer_info
716 INTO l_customer_id,
717 x_location,
718 x_edi_loc_code,
719 x_tp_ref_ext1,
720 x_tp_ref_ext2,
721 x_customer_name,
722 x_address1,
723 x_address2,
724 x_address3,
725 x_address4,
726 x_city,
727 x_postal_code,
728 x_country,
729 x_state,
730 x_province,
731 x_county,
732 x_address_id;
733
734 IF get_customer_info%NOTFOUND THEN
735 CLOSE get_customer_info;
736 x_location := NULL;
737 x_edi_loc_code := NULL;
738 x_tp_ref_ext1 := NULL;
739 x_tp_ref_ext2 := NULL;
740 x_customer_name := NULL;
741 x_address1 := NULL;
742 x_address2 := NULL;
743 x_address3 := NULL;
744 x_address4 := NULL;
745 x_city := NULL;
746 x_state := NULL;
747 x_postal_code := NULL;
748 x_country := NULL;
749 x_province := NULL;
750 x_county := NULL;
751 x_address_id := NULL;
752 x_area_code := NULL;
753 x_phone_number := NULL;
754
755 ELSE
756 x_area_code := wsh_ece_views_def.get_cust_area_code(l_customer_id);
757 x_phone_number := wsh_ece_views_def.get_cust_phone_number;
758 END IF;
759
760 ELSE
761 x_location := NULL;
762 x_edi_loc_code := NULL;
763 x_tp_ref_ext1 := NULL;
764 x_tp_ref_ext2 := NULL;
765 x_customer_name := NULL;
766 x_address1 := NULL;
767 x_address2 := NULL;
768 x_address3 := NULL;
769 x_address4 := NULL;
770 x_city := NULL;
771 x_state := NULL;
772 x_postal_code := NULL;
773 x_country := NULL;
774 x_province := NULL;
775 x_county := NULL;
776 x_address_id := NULL;
777 x_area_code := NULL;
778 x_phone_number := NULL;
779
780 END IF;
781
782 --
783 -- Debug Statements
784 --
785 IF l_debug_on THEN
786 WSH_DEBUG_SV.pop(l_module_name);
787 END IF;
788 --
789
790 END get_location_info;
791
792
793 procedure get_dlvy_location_info (
794 p_intmed_ship_to_location_id IN NUMBER,
795 p_pooled_ship_to_location_id IN NUMBER,
796 p_delivery_id IN NUMBER, --Bug 7371411
797 x_ist_location OUT NOCOPY VARCHAR2,
798 x_ist_edi_loc_code OUT NOCOPY VARCHAR2,
799 x_ist_tp_ref_ext1 OUT NOCOPY VARCHAR2,
800 x_ist_tp_ref_ext2 OUT NOCOPY VARCHAR2,
801 x_ist_customer_name OUT NOCOPY VARCHAR2,
802 x_ist_address1 OUT NOCOPY VARCHAR2,
803 x_ist_address2 OUT NOCOPY VARCHAR2,
804 x_ist_address3 OUT NOCOPY VARCHAR2,
805 x_ist_address4 OUT NOCOPY VARCHAR2,
806 x_ist_city OUT NOCOPY VARCHAR2,
807 x_ist_state OUT NOCOPY VARCHAR2,
808 x_ist_postal_code OUT NOCOPY VARCHAR2,
809 x_ist_country OUT NOCOPY VARCHAR2,
810 x_ist_province OUT NOCOPY VARCHAR2,
811 x_ist_county OUT NOCOPY VARCHAR2,
812 x_ist_address_id OUT NOCOPY NUMBER,
813 x_ist_area_code OUT NOCOPY VARCHAR2,
814 x_ist_phone_number OUT NOCOPY VARCHAR2,
815 x_pst_location OUT NOCOPY VARCHAR2,
816 x_pst_edi_loc_code OUT NOCOPY VARCHAR2,
817 x_pst_tp_ref_ext1 OUT NOCOPY VARCHAR2,
818 x_pst_tp_ref_ext2 OUT NOCOPY VARCHAR2,
819 x_pst_customer_name OUT NOCOPY VARCHAR2,
820 x_pst_address1 OUT NOCOPY VARCHAR2,
821 x_pst_address2 OUT NOCOPY VARCHAR2,
822 x_pst_address3 OUT NOCOPY VARCHAR2,
823 x_pst_address4 OUT NOCOPY VARCHAR2,
824 x_pst_city OUT NOCOPY VARCHAR2,
825 x_pst_state OUT NOCOPY VARCHAR2,
826 x_pst_postal_code OUT NOCOPY VARCHAR2,
827 x_pst_country OUT NOCOPY VARCHAR2,
828 x_pst_province OUT NOCOPY VARCHAR2,
829 x_pst_county OUT NOCOPY VARCHAR2,
830 x_pst_address_id OUT NOCOPY NUMBER,
831 x_pst_area_code OUT NOCOPY VARCHAR2,
832 x_pst_phone_number OUT NOCOPY VARCHAR2 ) IS
833
834
835 l_debug_on BOOLEAN;
836 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_DLVY_LOCATION_INFO';
837
838 BEGIN
839 --
840 -- Debug Statements
841 --
842 --
843 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
844 --
845 IF l_debug_on IS NULL
846 THEN
847 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
848 END IF;
849 --
850 IF l_debug_on THEN
851
852 WSH_DEBUG_SV.push(l_module_name);
853 WSH_DEBUG_SV.log(l_module_name, 'p_intmed_ship_to_location_id',p_intmed_ship_to_location_id);
854 WSH_DEBUG_SV.log(l_module_name, 'p_pooled_ship_to_location_id',p_pooled_ship_to_location_id);
855 WSH_DEBUG_SV.log(l_module_name, 'p_delivery_id',p_delivery_id);
856
857 END IF;
858 --
859
860 IF p_intmed_ship_to_location_id IS NULL THEN
861 x_ist_location := NULL;
862 x_ist_edi_loc_code := NULL;
863 x_ist_tp_ref_ext1 := NULL;
864 x_ist_tp_ref_ext2 := NULL;
865 x_ist_customer_name := NULL;
866 x_ist_address1 := NULL;
867 x_ist_address2 := NULL;
868 x_ist_address3 := NULL;
869 x_ist_address4 := NULL;
870 x_ist_city := NULL;
871 x_ist_country := NULL;
872 x_ist_state := NULL;
873 x_ist_postal_code := NULL;
874 x_ist_province := NULL;
875 x_ist_county := NULL;
876 x_ist_address_id :=NULL;
877 x_ist_area_code := NULL;
878 x_ist_phone_number := NULL;
879 ELSE
880 get_location_info(
881 p_location_id => p_intmed_ship_to_location_id,
882 p_delivery_id => p_delivery_id,
883 x_location => x_ist_location,
884 x_edi_loc_code => x_ist_edi_loc_code,
885 x_tp_ref_ext1 => x_ist_tp_ref_ext1,
886 x_tp_ref_ext2 => x_ist_tp_ref_ext2,
887 x_customer_name => x_ist_customer_name,
888 x_address1 => x_ist_address1,
889 x_address2 => x_ist_address2,
890 x_address3 => x_ist_address3,
891 x_address4 => x_ist_address4,
892 x_city => x_ist_city,
893 x_state => x_ist_state,
894 x_postal_code => x_ist_postal_code,
895 x_country => x_ist_country,
896 x_province => x_ist_province,
897 x_county => x_ist_county,
898 x_address_id => x_ist_address_id,
899 x_area_code => x_ist_area_code,
900 x_phone_number => x_ist_phone_number);
901
902 END IF;
903
904 IF p_pooled_ship_to_location_id IS NULL THEN
905 x_pst_location := NULL;
906 x_pst_edi_loc_code := NULL;
907 x_pst_tp_ref_ext1 := NULL;
908 x_pst_tp_ref_ext2 := NULL;
909 x_pst_customer_name := NULL;
910 x_pst_address1 := NULL;
911 x_pst_address2 := NULL;
912 x_pst_address3 := NULL;
913 x_pst_address4 := NULL;
914 x_pst_city := NULL;
915 x_pst_country := NULL;
916 x_pst_state := NULL;
917 x_pst_postal_code := NULL;
918 x_pst_province := NULL;
919 x_pst_county := NULL;
920 x_pst_address_id := NULL;
921 x_pst_area_code := NULL;
922 x_pst_phone_number := NULL;
923
924 ELSE
925
926 get_location_info(
927 p_location_id => p_pooled_ship_to_location_id,
928 p_delivery_id => p_delivery_id,
929 x_location => x_pst_location,
930 x_edi_loc_code => x_pst_edi_loc_code,
931 x_tp_ref_ext1 => x_pst_tp_ref_ext1,
932 x_tp_ref_ext2 => x_pst_tp_ref_ext2,
933 x_customer_name => x_pst_customer_name,
934 x_address1 => x_pst_address1,
935 x_address2 => x_pst_address2,
936 x_address3 => x_pst_address3,
937 x_address4 => x_pst_address4,
938 x_city => x_pst_city,
939 x_state => x_pst_state,
940 x_postal_code => x_pst_postal_code,
941 x_country => x_pst_country,
942 x_province => x_pst_province,
943 x_county => x_pst_county,
944 x_address_id => x_pst_address_id,
945 x_area_code => x_pst_area_code,
946 x_phone_number => x_pst_phone_number);
947
948 END IF;
949
950 --
951 -- Debug Statements
952 --
953 IF l_debug_on THEN
954 WSH_DEBUG_SV.pop(l_module_name);
955 END IF;
956 --
957
958 END get_dlvy_location_info;
959
960 procedure get_dlvy_dest_cont_info (
961 p_contact_id IN NUMBER,
962 x_dest_cont_last_name OUT NOCOPY VARCHAR2,
963 x_dest_cont_first_name OUT NOCOPY VARCHAR2,
964 x_cont_job_title OUT NOCOPY VARCHAR2 )
965 IS
966
967 l_debug_on BOOLEAN;
968 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_DLVY_DEST_CONT_INFO';
969
970 cursor l_dlvy_dest_cont_csr (p_contact_id IN NUMBER ) is
971 select substrb( REL_PARTY.PERSON_LAST_NAME,1,50) ,
972 substrb( REL_PARTY.PERSON_FIRST_NAME,1,40),
973 ORG_CONT.JOB_TITLE
974 from HZ_CUST_ACCOUNT_ROLES ACCT_ROLE,
975 HZ_RELATIONSHIPS REL,
976 HZ_ORG_CONTACTS ORG_CONT,
977 HZ_PARTIES REL_PARTY
978 WHERE ACCT_ROLE.CUST_ACCOUNT_ROLE_ID = p_contact_id
979 AND REL.PARTY_ID = ACCT_ROLE.PARTY_ID
980 AND ACCT_ROLE.ROLE_TYPE = 'CONTACT'
981 AND REL.RELATIONSHIP_ID = ORG_CONT.PARTY_RELATIONSHIP_ID
982 AND REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
983 AND REL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
984 AND REL.DIRECTIONAL_FLAG = 'F'
985 AND REL.SUBJECT_ID = REL_PARTY.PARTY_ID;
986
987
988
989
990 BEGIN
991 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
992 --
993 IF l_debug_on IS NULL
994 THEN
995 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
996 END IF;
997 --
998 IF l_debug_on THEN
999 WSH_DEBUG_SV.push(l_module_name);
1000 WSH_DEBUG_SV.log(l_module_name, p_contact_id || p_contact_id);
1001 END IF;
1002 --
1003 open l_dlvy_dest_cont_csr(p_contact_id);
1004 fetch l_dlvy_dest_cont_csr into x_dest_cont_last_name,
1005 x_dest_cont_first_name,
1006 x_cont_job_title;
1007 close l_dlvy_dest_cont_csr;
1008 --
1009 IF l_debug_on THEN
1010 WSH_DEBUG_SV.log(l_module_name, x_dest_cont_last_name || x_dest_cont_last_name);
1011 WSH_DEBUG_SV.log(l_module_name, x_dest_cont_first_name || x_dest_cont_first_name);
1012 WSH_DEBUG_SV.log(l_module_name, x_cont_job_title || x_cont_job_title);
1013 END IF;
1014 --
1015 IF l_debug_on THEN
1016 WSH_DEBUG_SV.pop(l_module_name);
1017 END IF;
1018 --
1019 exception
1020 when others then
1021 --
1022 -- Debug Statements
1023 --
1024 IF l_debug_on THEN
1025 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1026 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1027 END IF;
1028 --
1029 END get_dlvy_dest_cont_info;
1030
1031
1032
1033 END;