eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.WSH_TPA_SELECTOR_PKG

Source


1 PACKAGE BODY WSH_TPA_SELECTOR_PKG AS
2 /* $Header: WSHTPSLB.pls 120.0 2005/05/26 18:02:23 appldev noship $ */
3 
4 
5 --
6 -- Procedure:	DeliveryTP
7 -- Parameters:	p_delivery_id		Delivery being processed
8 --		x_customer_number	Standard TPS function attributes
9 -- 		x_ship_to_ece_locn_code
10 --		x_inter_ship_to_ece_locn_code
11 --		x_bill_to_ece_locn_code
12 --		x_tp_group_code
13 --
14 
15 PROCEDURE DeliveryTP (
16 	p_delivery_id			IN	NUMBER,
17 	x_customer_number		OUT NOCOPY 	VARCHAR2,
18 	x_ship_to_ece_locn_code		OUT NOCOPY 	VARCHAR2,
19 	x_inter_ship_to_ece_locn_code	OUT NOCOPY 	VARCHAR2,
20 	x_bill_to_ece_locn_code		OUT NOCOPY 	VARCHAR2,
21 	x_tp_group_code			OUT NOCOPY 	VARCHAR2) IS
22 
23 CURSOR Get_First_Line IS
24 SELECT delivery_detail_id
25 FROM wsh_delivery_assignments_v
26 WHERE delivery_id = p_delivery_id
27 AND rownum < 2;
28 
29 l_del_detail_id 	NUMBER;
30 
31 BEGIN
32 
33   IF p_delivery_id IS NULL THEN
34 	x_customer_number := NULL;
35 	x_ship_to_ece_locn_code := NULL;
36 	x_inter_ship_to_ece_locn_code := NULL;
37 	x_bill_to_ece_locn_code := NULL;
38 	x_tp_group_code := NULL;
39 	return;
40   END IF;
41 
42   OPEN Get_First_Line;
43 
44   FETCH Get_First_Line INTO l_del_detail_id;
45 
46   IF Get_First_Line%NOTFOUND OR l_del_detail_id IS NULL THEN
47 	CLOSE Get_First_Line;
48 	x_customer_number := NULL;
49 	x_ship_to_ece_locn_code := NULL;
50 	x_inter_ship_to_ece_locn_code := NULL;
51 	x_bill_to_ece_locn_code := NULL;
52 	x_tp_group_code := NULL;
53 	return;
54   END IF;
55 
56   IF Get_First_Line%ISOPEN THEN
57 	CLOSE Get_First_Line;
58   END IF;
59 
60   WSH_TPA_SELECTOR_PKG.DeliveryDetailTP (
61   			l_del_detail_id,
62 			x_customer_number,
63 			x_ship_to_ece_locn_code,
64 			x_inter_ship_to_ece_locn_code,
65 			x_bill_to_ece_locn_code,
66 			x_tp_group_code);
67 
68   return;
69 
70 EXCEPTION
71 
72   WHEN OTHERS THEN
73 	x_customer_number := NULL;
74 	x_ship_to_ece_locn_code := NULL;
75 	x_inter_ship_to_ece_locn_code := NULL;
76 	x_bill_to_ece_locn_code := NULL;
77 	x_tp_group_code := NULL;
78 	return;
79 
80 END DeliveryTP;
81 
82 
83 --
84 -- Procedure:	ContainerTP
85 -- Parameters:	p_container_instance_id	Container being processed
86 --		x_customer_number	Standard TPS function attributes
87 -- 		x_ship_to_ece_locn_code
88 --		x_inter_ship_to_ece_locn_code
89 --		x_bill_to_ece_locn_code
90 --		x_tp_group_code
91 --
92 
93 PROCEDURE ContainerTP (
94 	p_container_instance_id		IN	NUMBER,
95 	x_customer_number		OUT NOCOPY 	VARCHAR2,
96 	x_ship_to_ece_locn_code		OUT NOCOPY 	VARCHAR2,
97 	x_inter_ship_to_ece_locn_code	OUT NOCOPY 	VARCHAR2,
98 	x_bill_to_ece_locn_code		OUT NOCOPY 	VARCHAR2,
99 	x_tp_group_code			OUT NOCOPY 	VARCHAR2) IS
100 
101 
102 CURSOR Get_First_Line IS
103 SELECT delivery_detail_id
104 FROM wsh_delivery_assignments_v
105 WHERE parent_delivery_detail_id = p_container_instance_id
106 AND rownum < 2;
107 
108 l_del_detail_id 	NUMBER;
109 
110 BEGIN
111 
112   IF p_container_instance_id IS NULL THEN
113 	x_customer_number := NULL;
114 	x_ship_to_ece_locn_code := NULL;
115 	x_inter_ship_to_ece_locn_code := NULL;
116 	x_bill_to_ece_locn_code := NULL;
117 	x_tp_group_code := NULL;
118 	return;
119   END IF;
120 
121   OPEN Get_First_Line;
122 
123   FETCH Get_First_Line INTO l_del_detail_id;
124 
125   IF Get_First_Line%NOTFOUND OR l_del_detail_id IS NULL THEN
126 	CLOSE Get_First_Line;
127 	x_customer_number := NULL;
128 	x_ship_to_ece_locn_code := NULL;
129 	x_inter_ship_to_ece_locn_code := NULL;
130 	x_bill_to_ece_locn_code := NULL;
131 	x_tp_group_code := NULL;
132 	return;
133   END IF;
134 
135   IF Get_First_Line%ISOPEN THEN
136 	CLOSE Get_First_Line;
137   END IF;
138 
139   WSH_TPA_SELECTOR_PKG.DeliveryDetailTP (
140   			p_container_instance_id,
141 			x_customer_number,
142 			x_ship_to_ece_locn_code,
143 			x_inter_ship_to_ece_locn_code,
144 			x_bill_to_ece_locn_code,
145 			x_tp_group_code);
146 
147   return;
148 
149 EXCEPTION
150 
151   WHEN OTHERS THEN
152 	x_customer_number := NULL;
153 	x_ship_to_ece_locn_code := NULL;
154 	x_inter_ship_to_ece_locn_code := NULL;
155 	x_bill_to_ece_locn_code := NULL;
156 	x_tp_group_code := NULL;
157 	return;
158 
159 END ContainerTP;
160 
161 
162 --
163 -- Procedure:	DeliveryDetailTP
164 -- Parameters:	p_delivery_detail_id	Delivery Detail being processed
165 --		x_customer_number	Standard TPS function attributes
166 -- 		x_ship_to_ece_locn_code
167 --		x_inter_ship_to_ece_locn_code
168 --		x_bill_to_ece_locn_code
169 --		x_tp_group_code
170 --
171 
172 PROCEDURE DeliveryDetailTP (
173 	p_delivery_detail_id		IN	NUMBER,
174 	x_customer_number		OUT NOCOPY 	VARCHAR2,
175 	x_ship_to_ece_locn_code		OUT NOCOPY 	VARCHAR2,
176 	x_inter_ship_to_ece_locn_code	OUT NOCOPY 	VARCHAR2,
177 	x_bill_to_ece_locn_code		OUT NOCOPY 	VARCHAR2,
178 	x_tp_group_code			OUT NOCOPY 	VARCHAR2) IS
179 
180 
181 CURSOR Get_Line_Info (v_line_id NUMBER) IS
182 SELECT ship_to_org_id, invoice_to_org_id, intmed_ship_to_org_id
183 FROM OE_ORDER_LINES_ALL
184 WHERE line_id = v_line_id;
185 
186 CURSOR Get_Detail_Info IS
187 SELECT source_line_id, customer_id
188 FROM WSH_DELIVERY_DETAILS
189 WHERE delivery_detail_id = p_delivery_detail_id
190 AND source_code = 'OE';
191 
192 CURSOR Get_Ece_Loc_Code (v_site_use_id NUMBER) IS-- TCA View Removal starts
193 SELECT Acct_site.Ece_tp_location_code,
194 	   Acct_site.Tp_header_id /* TP_HEADER_ID */
195 FROM hz_cust_acct_sites_all acct_site ,
196 	hz_cust_site_uses_all site_uses
197 WHERE site_uses.site_use_id = v_site_use_id AND
198 	  acct_site.cust_acct_site_id/*address-id*/ = site_uses.cust_acct_site_id; -- TCA View Removal ends
199 
200 
201 
202 CURSOR Get_Tp_Code (v_tp_hdr_id NUMBER) IS -- TCA View Removal starts
203 SELECT etg.tp_group_code
204 FROM ece_tp_group etg,
205      ece_tp_headers eth,
206      hz_cust_acct_sites_all acct_site
207 WHERE acct_site.tp_header_id = v_tp_hdr_id
208   AND acct_site.tp_header_id = eth.tp_header_id
209   AND eth.tp_group_id = etg.tp_group_id;		-- -- TCA View Removal ends
210 
211 
212 
213 CURSOR Get_Cust_Number (v_cust_id NUMBER) IS   -- TCA View Removal starts
214 SELECT account_number /* customer number */
215 FROM  hz_cust_accounts
216 WHERE cust_account_id /*customer_id*/ = v_cust_id; -- TCA View Removal ends
217 
218 
219 l_src_line_id 			NUMBER;
220 l_cust_id			NUMBER;
221 
222 l_ship_to_org_id		NUMBER;
223 l_inter_ship_to_org_id		NUMBER;
224 l_bill_to_org_id		NUMBER;
225 
226 l_ship_to_ece_locn_code		VARCHAR2(40);
227 l_bill_to_ece_locn_code		VARCHAR2(40);
228 l_inter_to_ece_locn_code	VARCHAR2(40);
229 
230 l_ece_tp_loc_code		VARCHAR2(40);
231 
232 l_tp_header_id			NUMBER;
233 l_tp_group_code			VARCHAR2(35);
234 
235 l_cust_number			VARCHAR2(30);
236 
237 BEGIN
238 
239  IF p_delivery_detail_id IS NULL THEN
240 	x_customer_number := NULL;
241 	x_ship_to_ece_locn_code := NULL;
242 	x_inter_ship_to_ece_locn_code := NULL;
243 	x_bill_to_ece_locn_code := NULL;
244 	x_tp_group_code := NULL;
245 	return;
246  END IF;
247 
248 
249  OPEN Get_Detail_Info;
250 
251  FETCH Get_Detail_Info INTO
252   l_src_line_id,
253   l_cust_id;
254 
255  IF Get_Detail_Info%NOTFOUND OR l_src_line_id IS NULL THEN
256 	CLOSE Get_Detail_Info;
257 	x_customer_number := NULL;
258 	x_ship_to_ece_locn_code := NULL;
259 	x_inter_ship_to_ece_locn_code := NULL;
260 	x_bill_to_ece_locn_code := NULL;
261 	x_tp_group_code := NULL;
262 	return;
263  END IF;
264 
265  IF Get_Detail_Info%ISOPEN THEN
266 	CLOSE Get_Detail_Info;
267  END IF;
268 
269  IF l_cust_id IS NOT NULL THEN
270 
271  	OPEN Get_Cust_Number (l_cust_id);
272 
273 	FETCH Get_Cust_Number INTO l_cust_number;
274 
275 	IF Get_Cust_Number%NOTFOUND THEN
276 		CLOSE Get_Cust_Number;
277 		l_cust_number := NULL;
278 	END IF;
279 
280 	IF Get_Cust_Number%ISOPEN THEN
281 		CLOSE Get_Cust_Number;
282 	END IF;
283  ELSE
284 	l_cust_number := NULL;
285  END IF;
286 
287  x_customer_number := l_cust_number;
288 
289  OPEN Get_Line_Info (l_src_line_id);
290 
291  FETCH Get_Line_Info INTO
292 	l_ship_to_org_id,
293 	l_bill_to_org_id,
294 	l_inter_ship_to_org_id;
295 
296  IF Get_Line_Info%NOTFOUND THEN
297 	CLOSE Get_Line_Info;
298 	x_customer_number := NULL;
299 	x_ship_to_ece_locn_code := NULL;
300 	x_inter_ship_to_ece_locn_code := NULL;
301 	x_bill_to_ece_locn_code := NULL;
302 	x_tp_group_code := NULL;
303 	return;
304  END IF;
305 
306  IF Get_Line_Info%ISOPEN THEN
307 	CLOSE Get_Line_Info;
308  END IF;
309 
310  IF l_ship_to_org_id IS NOT NULL THEN
311 
312 	OPEN Get_Ece_Loc_Code (l_ship_to_org_id);
313 
314 	FETCH Get_Ece_Loc_Code INTO
315 		l_ece_tp_loc_code,
316 		l_tp_header_id;
317 
318 	IF Get_Ece_Loc_Code%NOTFOUND THEN
319 		CLOSE Get_Ece_Loc_Code;
320 		x_ship_to_ece_locn_code := NULL;
321 		x_tp_group_code := NULL;
322 	ELSE
323 
324 		IF l_tp_header_id IS NOT NULL THEN
325 
326 			OPEN Get_Tp_Code (l_tp_header_id);
327 
328 			FETCH Get_Tp_Code INTO l_tp_group_code;
329 
330 			IF Get_Tp_Code%NOTFOUND THEN
331 				CLOSE Get_Tp_Code;
332 				x_tp_group_code := NULL;
333 			END IF;
334 
335 			IF Get_Tp_Code%ISOPEN THEN
336 				CLOSE Get_Tp_Code;
337 			END IF;
338 		ELSE
339 			x_tp_group_code := NULL;
340 		END IF;
341 
342 	END IF;
343 
344  ELSE
345 	x_ship_to_ece_locn_code := NULL;
346 	x_tp_group_code := NULL;
347  END IF;
348 
349  IF l_bill_to_org_id IS NOT NULL THEN
350 
351 	OPEN Get_Ece_Loc_Code (l_bill_to_org_id);
352 
353 	FETCH Get_Ece_Loc_Code INTO
354 		l_ece_tp_loc_code,
355 		l_tp_header_id;
356 
357 	IF Get_Ece_Loc_Code%NOTFOUND THEN
358 		CLOSE Get_Ece_Loc_Code;
359 		x_bill_to_ece_locn_code := NULL;
360 	ELSE
361 
362 		x_bill_to_ece_locn_code := l_ece_tp_loc_code;
363 
364 	END IF;
365 
366  ELSE
367 	x_bill_to_ece_locn_code := NULL;
368  END IF;
369 
370  IF l_inter_ship_to_org_id IS NOT NULL THEN
371 
372 	OPEN Get_Ece_Loc_Code (l_inter_ship_to_org_id);
373 
374 	FETCH Get_Ece_Loc_Code INTO
375 		l_ece_tp_loc_code,
376 		l_tp_header_id;
377 
378 	IF Get_Ece_Loc_Code%NOTFOUND THEN
379 		CLOSE Get_Ece_Loc_Code;
380 		x_inter_ship_to_ece_locn_code := NULL;
381 	ELSE
382 
383 		x_inter_ship_to_ece_locn_code := l_ece_tp_loc_code;
384 
385 	END IF;
386 
387  ELSE
388 	x_inter_ship_to_ece_locn_code := NULL;
389  END IF;
390 
391 EXCEPTION
392 
393   WHEN OTHERS THEN
394 	x_customer_number := NULL;
395 	x_ship_to_ece_locn_code := NULL;
396 	x_inter_ship_to_ece_locn_code := NULL;
397 	x_bill_to_ece_locn_code := NULL;
398 	x_tp_group_code := NULL;
399 	return;
400 
401 END DeliveryDetailTP;
402 
403 
404 --
405 -- Procedure:	FreightCostTP
406 -- Parameters:	p_delivery_id		Delivery being processed
407 --		p_container_instance_id Conatiner being processed
408 --		x_customer_number	Standard TPS function attributes
409 -- 		x_ship_to_ece_locn_code
410 --		x_inter_ship_to_ece_locn_code
411 --		x_bill_to_ece_locn_code
412 --		x_tp_group_code
413 --
414 
415 PROCEDURE FreightCostTP (
416 	p_delivery_id			IN	NUMBER,
417 	p_container_instance_id	IN	NUMBER,
418 	x_customer_number		OUT NOCOPY 	VARCHAR2,
419 	x_ship_to_ece_locn_code		OUT NOCOPY 	VARCHAR2,
420 	x_inter_ship_to_ece_locn_code	OUT NOCOPY 	VARCHAR2,
421 	x_bill_to_ece_locn_code		OUT NOCOPY 	VARCHAR2,
422 	x_tp_group_code			OUT NOCOPY 	VARCHAR2) IS
423 
424 BEGIN
425 
426   IF p_delivery_id IS NULL AND p_container_instance_id IS NULL THEN
427 	x_customer_number := NULL;
428 	x_ship_to_ece_locn_code := NULL;
429 	x_inter_ship_to_ece_locn_code := NULL;
430 	x_bill_to_ece_locn_code := NULL;
431 	x_tp_group_code := NULL;
432 	return;
433   END IF;
434 
435   IF p_delivery_id IS NOT NULL THEN
436 
437 	WSH_TPA_SELECTOR_PKG.DeliveryTP (
438   			p_delivery_id,
439 			x_customer_number,
440 			x_ship_to_ece_locn_code,
441 			x_inter_ship_to_ece_locn_code,
442 			x_bill_to_ece_locn_code,
443 			x_tp_group_code);
444 	return;
445 
446   ELSIF p_container_instance_id IS NOT NULL THEN
447 
448   	WSH_TPA_SELECTOR_PKG.ContainerTP (
449   			p_container_instance_id,
450 			x_customer_number,
451 			x_ship_to_ece_locn_code,
452 			x_inter_ship_to_ece_locn_code,
453 			x_bill_to_ece_locn_code,
454 			x_tp_group_code);
455 
456 	return;
457 
458   ELSE
459 	x_customer_number := NULL;
460 	x_ship_to_ece_locn_code := NULL;
461 	x_inter_ship_to_ece_locn_code := NULL;
462 	x_bill_to_ece_locn_code := NULL;
463 	x_tp_group_code := NULL;
464 	return;
465   END IF;
466 
467 EXCEPTION
468 
469   WHEN OTHERS THEN
470 	x_customer_number := NULL;
471 	x_ship_to_ece_locn_code := NULL;
472 	x_inter_ship_to_ece_locn_code := NULL;
473 	x_bill_to_ece_locn_code := NULL;
474 	x_tp_group_code := NULL;
475 	return;
476 
477 END FreightCostTP;
478 
479 
480 
481 --
482 -- Procedure:	DefaultTP
483 -- Parameters:	p_entity_id		entity id being processed
484 --		p_entity_type		entity type being processed. right now
485 --					it supports only 'DELIVERY'
486 --		x_customer_number	Standard TPS function attributes
487 -- 		x_ship_to_ece_locn_code
488 --		x_inter_ship_to_ece_locn_code
489 --		x_bill_to_ece_locn_code
490 --		x_tp_group_code
491 --
492 
493 PROCEDURE DefaultTP (
494 	p_entity_id			IN	NUMBER,
495 	p_entity_type			IN 	VARCHAR2,
496 	x_customer_number		OUT NOCOPY 	VARCHAR2,
497 	x_ship_to_ece_locn_code		OUT NOCOPY 	VARCHAR2,
498 	x_inter_ship_to_ece_locn_code	OUT NOCOPY 	VARCHAR2,
499 	x_bill_to_ece_locn_code		OUT NOCOPY 	VARCHAR2,
500 	x_tp_group_code			OUT NOCOPY 	VARCHAR2) IS
501 
502 BEGIN
503 
504   IF p_entity_type = 'DELIVERY' THEN
505   	IF p_entity_id IS NOT NULL THEN
506 
507 		WSH_TPA_SELECTOR_PKG.DeliveryTP (
508   				p_entity_id,
509 				x_customer_number,
510 				x_ship_to_ece_locn_code,
511 				x_inter_ship_to_ece_locn_code,
512 				x_bill_to_ece_locn_code,
513 				x_tp_group_code);
514 		return;
515 
516 	ELSE
517 		x_customer_number := NULL;
518 		x_ship_to_ece_locn_code := NULL;
519 		x_inter_ship_to_ece_locn_code := NULL;
520 		x_bill_to_ece_locn_code := NULL;
521 		x_tp_group_code := NULL;
522 		return;
523 	END IF;
524   ELSE
525 	x_customer_number := NULL;
526 	x_ship_to_ece_locn_code := NULL;
527 	x_inter_ship_to_ece_locn_code := NULL;
528 	x_bill_to_ece_locn_code := NULL;
529 	x_tp_group_code := NULL;
530 	return;
531   END IF;
532 
533 EXCEPTION
534 
535   WHEN OTHERS THEN
536 	x_customer_number := NULL;
537 	x_ship_to_ece_locn_code := NULL;
538 	x_inter_ship_to_ece_locn_code := NULL;
539 	x_bill_to_ece_locn_code := NULL;
540 	x_tp_group_code := NULL;
541 	return;
542 
543 END DefaultTP;
544 
545 
546 END WSH_TPA_SELECTOR_PKG;