[Home] [Help]
PACKAGE BODY: APPS.WSH_ECE_VIEWS_DEF
Source
1 PACKAGE BODY WSH_ECE_VIEWS_DEF AS
2 /* $Header: WSHECVWB.pls 120.0 2005/05/26 17:06:59 appldev noship $ */
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(25) := NULL;
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 x_location OUT NOCOPY VARCHAR2,
630 x_edi_loc_code OUT NOCOPY VARCHAR2,
631 x_tp_ref_ext1 OUT NOCOPY VARCHAR2,
632 x_tp_ref_ext2 OUT NOCOPY VARCHAR2,
633 x_customer_name OUT NOCOPY VARCHAR2,
634 x_address1 OUT NOCOPY VARCHAR2,
635 x_address2 OUT NOCOPY VARCHAR2,
636 x_address3 OUT NOCOPY VARCHAR2,
637 x_address4 OUT NOCOPY VARCHAR2,
638 x_city OUT NOCOPY VARCHAR2,
639 x_state OUT NOCOPY VARCHAR2,
640 x_postal_code OUT NOCOPY VARCHAR2,
641 x_country OUT NOCOPY VARCHAR2,
642 x_province OUT NOCOPY VARCHAR2,
643 x_county OUT NOCOPY VARCHAR2,
644 x_address_id OUT NOCOPY NUMBER,
645 x_area_code OUT NOCOPY VARCHAR2,
646 x_phone_number OUT NOCOPY VARCHAR2) IS
647
648 CURSOR get_customer_info IS
649 SELECT CL.customer_id,
650 CL.location,
651 CL.tp_location_code_ext,
652 ECH.tp_reference_ext1,
653 ECH.tp_reference_ext2,
654 CL.customer_name,
655 CL.address1,
656 CL.address2,
657 CL.address3,
658 CL.address4,
659 CL.city,
660 CL.postal_code,
661 CL.country,
662 CL.state,
663 CL.province,
664 CL.county,
665 CL.address_id
666 FROM wsh_customer_locations_v CL , ece_tp_headers ECH
667 WHERE CL.tp_header_id = ECH.tp_header_id (+) AND
668 CL.wsh_location_id = p_location_id AND
669 CL.site_use_status = 'A' AND
670 CL.site_use_code = 'SHIP_TO';
671
672 l_customer_id NUMBER;
673 l_debug_on BOOLEAN;
674 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_LOCATION_INFO';
675
676 BEGIN
677
678 --
679 -- Debug Statements
680 --
681 --
682 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
683 --
684 IF l_debug_on IS NULL
685 THEN
686 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
687 END IF;
688 --
689 IF l_debug_on THEN
690
691 WSH_DEBUG_SV.push(l_module_name);
692 WSH_DEBUG_SV.log(l_module_name, p_location_id);
693
694 END IF;
695 --
696
697 IF p_location_id IS NOT NULL THEN
698 OPEN get_customer_info;
699 FETCH get_customer_info
700 INTO l_customer_id,
701 x_location,
702 x_edi_loc_code,
703 x_tp_ref_ext1,
704 x_tp_ref_ext2,
705 x_customer_name,
706 x_address1,
707 x_address2,
708 x_address3,
709 x_address4,
710 x_city,
711 x_postal_code,
712 x_country,
713 x_state,
714 x_province,
715 x_county,
716 x_address_id;
717
718 IF get_customer_info%NOTFOUND THEN
719 CLOSE get_customer_info;
720 x_location := NULL;
721 x_edi_loc_code := NULL;
722 x_tp_ref_ext1 := NULL;
723 x_tp_ref_ext2 := NULL;
724 x_customer_name := NULL;
725 x_address1 := NULL;
726 x_address2 := NULL;
727 x_address3 := NULL;
728 x_address4 := NULL;
729 x_city := NULL;
730 x_state := NULL;
731 x_postal_code := NULL;
732 x_country := NULL;
733 x_province := NULL;
734 x_county := NULL;
735 x_address_id := NULL;
736 x_area_code := NULL;
737 x_phone_number := NULL;
738
739 ELSE
740 x_area_code := wsh_ece_views_def.get_cust_area_code(l_customer_id);
741 x_phone_number := wsh_ece_views_def.get_cust_phone_number;
742 END IF;
743
744 ELSE
745 x_location := NULL;
746 x_edi_loc_code := NULL;
747 x_tp_ref_ext1 := NULL;
748 x_tp_ref_ext2 := NULL;
749 x_customer_name := NULL;
750 x_address1 := NULL;
751 x_address2 := NULL;
752 x_address3 := NULL;
753 x_address4 := NULL;
754 x_city := NULL;
755 x_state := NULL;
756 x_postal_code := NULL;
757 x_country := NULL;
758 x_province := NULL;
759 x_county := NULL;
760 x_address_id := NULL;
761 x_area_code := NULL;
762 x_phone_number := NULL;
763
764 END IF;
765
766 --
767 -- Debug Statements
768 --
769 IF l_debug_on THEN
770 WSH_DEBUG_SV.pop(l_module_name);
771 END IF;
772 --
773
774 END get_location_info;
775
776
777 procedure get_dlvy_location_info (
778 p_intmed_ship_to_location_id IN NUMBER,
779 p_pooled_ship_to_location_id IN NUMBER,
780 x_ist_location OUT NOCOPY VARCHAR2,
781 x_ist_edi_loc_code OUT NOCOPY VARCHAR2,
782 x_ist_tp_ref_ext1 OUT NOCOPY VARCHAR2,
783 x_ist_tp_ref_ext2 OUT NOCOPY VARCHAR2,
784 x_ist_customer_name OUT NOCOPY VARCHAR2,
785 x_ist_address1 OUT NOCOPY VARCHAR2,
786 x_ist_address2 OUT NOCOPY VARCHAR2,
787 x_ist_address3 OUT NOCOPY VARCHAR2,
788 x_ist_address4 OUT NOCOPY VARCHAR2,
789 x_ist_city OUT NOCOPY VARCHAR2,
790 x_ist_state OUT NOCOPY VARCHAR2,
791 x_ist_postal_code OUT NOCOPY VARCHAR2,
792 x_ist_country OUT NOCOPY VARCHAR2,
793 x_ist_province OUT NOCOPY VARCHAR2,
794 x_ist_county OUT NOCOPY VARCHAR2,
795 x_ist_address_id OUT NOCOPY NUMBER,
796 x_ist_area_code OUT NOCOPY VARCHAR2,
797 x_ist_phone_number OUT NOCOPY VARCHAR2,
798 x_pst_location OUT NOCOPY VARCHAR2,
799 x_pst_edi_loc_code OUT NOCOPY VARCHAR2,
800 x_pst_tp_ref_ext1 OUT NOCOPY VARCHAR2,
801 x_pst_tp_ref_ext2 OUT NOCOPY VARCHAR2,
802 x_pst_customer_name OUT NOCOPY VARCHAR2,
803 x_pst_address1 OUT NOCOPY VARCHAR2,
804 x_pst_address2 OUT NOCOPY VARCHAR2,
805 x_pst_address3 OUT NOCOPY VARCHAR2,
806 x_pst_address4 OUT NOCOPY VARCHAR2,
807 x_pst_city OUT NOCOPY VARCHAR2,
808 x_pst_state OUT NOCOPY VARCHAR2,
809 x_pst_postal_code OUT NOCOPY VARCHAR2,
810 x_pst_country OUT NOCOPY VARCHAR2,
811 x_pst_province OUT NOCOPY VARCHAR2,
812 x_pst_county OUT NOCOPY VARCHAR2,
813 x_pst_address_id OUT NOCOPY NUMBER,
814 x_pst_area_code OUT NOCOPY VARCHAR2,
815 x_pst_phone_number OUT NOCOPY VARCHAR2 ) IS
816
817
818 l_debug_on BOOLEAN;
819 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_DLVY_LOCATION_INFO';
820
821 BEGIN
822 --
823 -- Debug Statements
824 --
825 --
826 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
827 --
828 IF l_debug_on IS NULL
829 THEN
830 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
831 END IF;
832 --
833 IF l_debug_on THEN
834
835 WSH_DEBUG_SV.push(l_module_name);
836 WSH_DEBUG_SV.log(l_module_name, p_intmed_ship_to_location_id);
837 WSH_DEBUG_SV.log(l_module_name, p_pooled_ship_to_location_id);
838
839 END IF;
840 --
841
842 IF p_intmed_ship_to_location_id IS NULL THEN
843 x_ist_location := NULL;
844 x_ist_edi_loc_code := NULL;
845 x_ist_tp_ref_ext1 := NULL;
846 x_ist_tp_ref_ext2 := NULL;
847 x_ist_customer_name := NULL;
848 x_ist_address1 := NULL;
849 x_ist_address2 := NULL;
850 x_ist_address3 := NULL;
851 x_ist_address4 := NULL;
852 x_ist_city := NULL;
853 x_ist_country := NULL;
854 x_ist_state := NULL;
855 x_ist_postal_code := NULL;
856 x_ist_province := NULL;
857 x_ist_county := NULL;
858 x_ist_address_id :=NULL;
859 x_ist_area_code := NULL;
860 x_ist_phone_number := NULL;
861 ELSE
862 get_location_info(
863 p_location_id => p_intmed_ship_to_location_id,
864 x_location => x_ist_location,
865 x_edi_loc_code => x_ist_edi_loc_code,
866 x_tp_ref_ext1 => x_ist_tp_ref_ext1,
867 x_tp_ref_ext2 => x_ist_tp_ref_ext2,
868 x_customer_name => x_ist_customer_name,
869 x_address1 => x_ist_address1,
870 x_address2 => x_ist_address2,
871 x_address3 => x_ist_address3,
872 x_address4 => x_ist_address4,
873 x_city => x_ist_city,
874 x_state => x_ist_state,
875 x_postal_code => x_ist_postal_code,
876 x_country => x_ist_country,
877 x_province => x_ist_province,
878 x_county => x_ist_county,
879 x_address_id => x_ist_address_id,
880 x_area_code => x_ist_area_code,
881 x_phone_number => x_ist_phone_number);
882
883 END IF;
884
885 IF p_pooled_ship_to_location_id IS NULL THEN
886 x_pst_location := NULL;
887 x_pst_edi_loc_code := NULL;
888 x_pst_tp_ref_ext1 := NULL;
889 x_pst_tp_ref_ext2 := NULL;
890 x_pst_customer_name := NULL;
891 x_pst_address1 := NULL;
892 x_pst_address2 := NULL;
893 x_pst_address3 := NULL;
894 x_pst_address4 := NULL;
895 x_pst_city := NULL;
896 x_pst_country := NULL;
897 x_pst_state := NULL;
898 x_pst_postal_code := NULL;
899 x_pst_province := NULL;
900 x_pst_county := NULL;
901 x_pst_address_id := NULL;
902 x_pst_area_code := NULL;
903 x_pst_phone_number := NULL;
904
905 ELSE
906
907 get_location_info(
908 p_location_id => p_pooled_ship_to_location_id,
909 x_location => x_pst_location,
910 x_edi_loc_code => x_pst_edi_loc_code,
911 x_tp_ref_ext1 => x_pst_tp_ref_ext1,
912 x_tp_ref_ext2 => x_pst_tp_ref_ext2,
913 x_customer_name => x_pst_customer_name,
914 x_address1 => x_pst_address1,
915 x_address2 => x_pst_address2,
916 x_address3 => x_pst_address3,
917 x_address4 => x_pst_address4,
918 x_city => x_pst_city,
919 x_state => x_pst_state,
920 x_postal_code => x_pst_postal_code,
921 x_country => x_pst_country,
922 x_province => x_pst_province,
923 x_county => x_pst_county,
924 x_address_id => x_pst_address_id,
925 x_area_code => x_pst_area_code,
926 x_phone_number => x_pst_phone_number);
927
928 END IF;
929
930 --
931 -- Debug Statements
932 --
933 IF l_debug_on THEN
934 WSH_DEBUG_SV.pop(l_module_name);
935 END IF;
936 --
937
938 END get_dlvy_location_info;
939
940 procedure get_dlvy_dest_cont_info (
941 p_contact_id IN NUMBER,
942 x_dest_cont_last_name OUT NOCOPY VARCHAR2,
943 x_dest_cont_first_name OUT NOCOPY VARCHAR2,
944 x_cont_job_title OUT NOCOPY VARCHAR2 )
945 IS
946
947 l_debug_on BOOLEAN;
948 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_DLVY_DEST_CONT_INFO';
949
950 cursor l_dlvy_dest_cont_csr (p_contact_id IN NUMBER ) is
951 select substrb( REL_PARTY.PERSON_LAST_NAME,1,50) ,
952 substrb( REL_PARTY.PERSON_FIRST_NAME,1,40),
953 ORG_CONT.JOB_TITLE
954 from HZ_CUST_ACCOUNT_ROLES ACCT_ROLE,
955 HZ_RELATIONSHIPS REL,
956 HZ_ORG_CONTACTS ORG_CONT,
957 HZ_PARTIES REL_PARTY
958 WHERE ACCT_ROLE.CUST_ACCOUNT_ROLE_ID = p_contact_id
959 AND REL.PARTY_ID = ACCT_ROLE.PARTY_ID
960 AND ACCT_ROLE.ROLE_TYPE = 'CONTACT'
961 AND REL.RELATIONSHIP_ID = ORG_CONT.PARTY_RELATIONSHIP_ID
962 AND REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
963 AND REL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
964 AND REL.DIRECTIONAL_FLAG = 'F'
965 AND REL.SUBJECT_ID = REL_PARTY.PARTY_ID;
966
967
968
969
970 BEGIN
971 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
972 --
973 IF l_debug_on IS NULL
974 THEN
975 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
976 END IF;
977 --
978 IF l_debug_on THEN
979 WSH_DEBUG_SV.push(l_module_name);
980 WSH_DEBUG_SV.log(l_module_name, p_contact_id || p_contact_id);
981 END IF;
982 --
983 open l_dlvy_dest_cont_csr(p_contact_id);
984 fetch l_dlvy_dest_cont_csr into x_dest_cont_last_name,
985 x_dest_cont_first_name,
986 x_cont_job_title;
987 close l_dlvy_dest_cont_csr;
988 --
989 IF l_debug_on THEN
990 WSH_DEBUG_SV.log(l_module_name, x_dest_cont_last_name || x_dest_cont_last_name);
991 WSH_DEBUG_SV.log(l_module_name, x_dest_cont_first_name || x_dest_cont_first_name);
992 WSH_DEBUG_SV.log(l_module_name, x_cont_job_title || x_cont_job_title);
993 END IF;
994 --
995 IF l_debug_on THEN
996 WSH_DEBUG_SV.pop(l_module_name);
997 END IF;
998 --
999 exception
1000 when others then
1001 --
1002 -- Debug Statements
1003 --
1004 IF l_debug_on THEN
1005 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1006 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1007 END IF;
1008 --
1009 END get_dlvy_dest_cont_info;
1010
1011
1012
1013 END;