1 PACKAGE BODY WSH_WSHRDMBL_XMLP_PKG AS
2 /* $Header: WSHRDMBLB.pls 120.2 2007/12/25 07:24:10 nchinnam noship $ */
3 FUNCTION CF_SHIPPER_NAMEFORMULA(STOP_ID IN NUMBER) RETURN CHAR IS
4 L_SHIPPER_NAME VARCHAR2(240);
5 BEGIN
6 SELECT
7 ORGANIZATION_NAME
8 INTO L_SHIPPER_NAME
9 FROM
10 WSH_SHIP_FROM_ORGS_V WSFO,
11 WSH_TRIP_STOPS WTS
12 WHERE TRIP_ID = P_TRIP_ID
13 AND WTS.STOP_LOCATION_ID = WSFO.LOCATION_ID
14 AND WTS.STOP_SEQUENCE_NUMBER = (
15 SELECT
16 MIN(STOP_SEQUENCE_NUMBER)
17 FROM
18 WSH_TRIP_STOPS
19 WHERE TRIP_ID = WTS.TRIP_ID );
20 RETURN L_SHIPPER_NAME;
21 EXCEPTION
22 WHEN NO_DATA_FOUND THEN
23 RETURN NULL;
24 WHEN TOO_MANY_ROWS THEN
25 BEGIN
26 SELECT
27 DISTINCT
28 ORGANIZATION_NAME
29 INTO L_SHIPPER_NAME
30 FROM
31 WSH_SHIP_FROM_ORGS_V WSFO,
32 WSH_TRIP_STOPS WTS,
33 WSH_NEW_DELIVERIES WND,
34 WSH_DELIVERY_LEGS WDL
35 WHERE WTS.TRIP_ID = P_TRIP_ID
36 AND WTS.STOP_ID = CF_SHIPPER_NAMEFORMULA.STOP_ID
37 AND WDL.PICK_UP_STOP_ID = WTS.STOP_ID
38 AND WND.DELIVERY_ID = WDL.DELIVERY_ID
39 AND WTS.STOP_LOCATION_ID = WSFO.LOCATION_ID
40 AND WND.ORGANIZATION_ID = WSFO.ORGANIZATION_ID
41 AND WTS.STOP_SEQUENCE_NUMBER = (
42 SELECT
43 MIN(STOP_SEQUENCE_NUMBER)
44 FROM
45 WSH_TRIP_STOPS
46 WHERE TRIP_ID = WTS.TRIP_ID )
47 AND ROWNUM = 1;
48 RETURN L_SHIPPER_NAME;
49 EXCEPTION
50 WHEN NO_DATA_FOUND THEN
51 RETURN NULL;
52 END;
53 END CF_SHIPPER_NAMEFORMULA;
54
55 FUNCTION CF_ADDRESS_TRIPS_STOPSFORMULA(ADDRESS1 IN VARCHAR2
56 ,ADDRESS2 IN VARCHAR2
57 ,ADDRESS3 IN VARCHAR2
58 ,ADDRESS4 IN VARCHAR2) RETURN CHAR IS
59 L_ADDRESS VARCHAR2(1000) := NULL;
60 BEGIN
61 IF ADDRESS1 IS NOT NULL THEN
62 L_ADDRESS := ADDRESS1;
63 END IF;
64 IF ADDRESS2 IS NOT NULL THEN
65 L_ADDRESS := L_ADDRESS || ' ,' || ADDRESS2;
66 END IF;
67 IF ADDRESS3 IS NOT NULL THEN
68 L_ADDRESS := L_ADDRESS || ' ,' || ADDRESS3;
69 END IF;
70 IF ADDRESS4 IS NOT NULL THEN
71 L_ADDRESS := L_ADDRESS || ' ,' || ADDRESS4;
72 END IF;
73 RETURN (L_ADDRESS);
74 END CF_ADDRESS_TRIPS_STOPSFORMULA;
75
76 FUNCTION CF_ADDRESS_DELIVERIES_PICKEDUP(ADDRESS1_DP IN VARCHAR2
77 ,ADDRESS2_DP IN VARCHAR2
78 ,ADDRESS3_DP IN VARCHAR2
79 ,ADDRESS4_DP IN VARCHAR2) RETURN CHAR IS
80 L_ADDRESS VARCHAR2(1000) := NULL;
81 BEGIN
82 IF ADDRESS1_DP IS NOT NULL THEN
83 L_ADDRESS := ADDRESS1_DP;
84 END IF;
85 IF ADDRESS2_DP IS NOT NULL THEN
86 L_ADDRESS := L_ADDRESS || ' ,' || ADDRESS2_DP;
87 END IF;
88 IF ADDRESS3_DP IS NOT NULL THEN
89 L_ADDRESS := L_ADDRESS || ' ,' || ADDRESS3_DP;
90 END IF;
91 IF ADDRESS4_DP IS NOT NULL THEN
92 L_ADDRESS := L_ADDRESS || ' ,' || ADDRESS4_DP;
93 END IF;
94 RETURN (L_ADDRESS);
95 END CF_ADDRESS_DELIVERIES_PICKEDUP;
96
97 FUNCTION AFTERREPORT RETURN BOOLEAN IS
98 L_RETURN_STATUS VARCHAR2(1);
99 BEGIN
100 IF (P_TRIP_ID IS NOT NULL AND NVL(P_PRINT_BOLS
101 ,'N') = 'Y') THEN
102 WSH_MBOLS_PVT.PRINT_BOLS(P_TRIP_ID => P_TRIP_ID
103 ,P_CONC_REQUEST_ID => P_CONC_REQUEST_ID
104 ,X_RETURN_STATUS => L_RETURN_STATUS);
105 /*SRW.MESSAGE(1
106 ,'conc request id' || P_CONC_REQUEST_ID)*/NULL;
107 /*SRW.MESSAGE(1
108 ,'l_return_status -->' || L_RETURN_STATUS)*/NULL;
109 IF L_RETURN_STATUS <> 'S' THEN
110 WSH_UTIL_CORE.PRINTMSG('ERROR: Failed to submit Bill of Lading concurrent request');
111 END IF;
112 END IF;
113 BEGIN
114 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
115 EXCEPTION
116 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
117 /*SRW.MESSAGE(1
118 ,'Failed in SRWEXIT')*/NULL;
119 RAISE;
120 END;
121 RETURN (TRUE);
122 END AFTERREPORT;
123
124 FUNCTION CF_NO_DATA_FOUNDFORMULA RETURN CHAR IS
125 L_TRIP_NAME WSH_TRIPS.NAME%TYPE;
126 BEGIN
127 SELECT
128 NAME
129 INTO L_TRIP_NAME
130 FROM
131 WSH_TRIPS
132 WHERE TRIP_ID = P_TRIP_ID;
133 RETURN ('*** No data found for trip ' || L_TRIP_NAME || ' ***');
134 EXCEPTION
135 WHEN NO_DATA_FOUND THEN
136 RETURN NULL;
137 END CF_NO_DATA_FOUNDFORMULA;
138
139 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
140 BEGIN
141 BEGIN
142 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
143 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
144 EXCEPTION
145 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
146 /*SRW.MESSAGE(1
147 ,'Failed FND SRWINIT.')*/NULL;
148 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
149 END;
150 RETURN (TRUE);
151 END BEFOREREPORT;
152
153 FUNCTION CF_ITEM_DESCRIPTION_IPFORMULA(ITEM_DESCRIPTION_IP IN VARCHAR2
154 ,INVENTORY_ITEM_ID_IP IN NUMBER
155 ,ORGANIZATION_ID_IP IN NUMBER) RETURN CHAR IS
156 CURSOR INVENTORY_LABEL(ID IN NUMBER,ORG_ID IN NUMBER) IS
157 SELECT
158 DESCRIPTION
159 FROM
160 MTL_SYSTEM_ITEMS_VL
161 WHERE INVENTORY_ITEM_ID = ID
162 AND ORGANIZATION_ID = ORG_ID;
163 NAME VARCHAR2(250);
164 BEGIN
165 NAME := ITEM_DESCRIPTION_IP;
166 IF (INVENTORY_ITEM_ID_IP IS NOT NULL) THEN
167 OPEN INVENTORY_LABEL(INVENTORY_ITEM_ID_IP,ORGANIZATION_ID_IP);
168 FETCH INVENTORY_LABEL
169 INTO NAME;
170 IF (INVENTORY_LABEL%NOTFOUND) THEN
171 NAME := ITEM_DESCRIPTION_IP;
172 END IF;
173 CLOSE INVENTORY_LABEL;
174 END IF;
175 RETURN NAME;
176 END CF_ITEM_DESCRIPTION_IPFORMULA;
177
178 FUNCTION CF_ITEM_DESCRIPTION_DFORMULA(ITEM_DESCRIPTION_D IN VARCHAR2
179 ,INVENTORY_ITEM_ID_D IN NUMBER
180 ,ORGANIZATION_ID_D IN NUMBER) RETURN CHAR IS
181 CURSOR INVENTORY_LABEL(ID IN NUMBER,ORG_ID IN NUMBER) IS
182 SELECT
183 DESCRIPTION
184 FROM
185 MTL_SYSTEM_ITEMS_VL
186 WHERE INVENTORY_ITEM_ID = ID
187 AND ORGANIZATION_ID = ORG_ID;
188 NAME VARCHAR2(250);
189 BEGIN
190 NAME := ITEM_DESCRIPTION_D;
191 IF (INVENTORY_ITEM_ID_D IS NOT NULL) THEN
192 OPEN INVENTORY_LABEL(INVENTORY_ITEM_ID_D,ORGANIZATION_ID_D);
193 FETCH INVENTORY_LABEL
194 INTO NAME;
195 IF (INVENTORY_LABEL%NOTFOUND) THEN
196 NAME := ITEM_DESCRIPTION_D;
197 END IF;
198 CLOSE INVENTORY_LABEL;
199 END IF;
200 RETURN NAME;
201 END CF_ITEM_DESCRIPTION_DFORMULA;
202
203 FUNCTION CF_MODE_MEANFORMULA(MODE_OF_TRANSPORT IN VARCHAR2) RETURN CHAR IS
204 L_MODE_MEAN VARCHAR2(80);
205 BEGIN
206 IF MODE_OF_TRANSPORT IS NOT NULL THEN
207 SELECT
208 MEANING
209 INTO L_MODE_MEAN
210 FROM
211 FND_LOOKUP_VALUES_VL
212 WHERE LOOKUP_TYPE = 'WSH_MODE_OF_TRANSPORT'
213 AND LOOKUP_CODE = MODE_OF_TRANSPORT;
214 ELSE
215 L_MODE_MEAN := NULL;
216 END IF;
217 RETURN L_MODE_MEAN;
218 END CF_MODE_MEANFORMULA;
219
220 FUNCTION CF_SERVICE_LEVELFORMULA(SERVICE_LEVEL IN VARCHAR2) RETURN CHAR IS
221 L_SERVICE_LEVEL VARCHAR2(80);
222 L_LOOKUP_TYPE VARCHAR2(30);
223 BEGIN
224 IF SERVICE_LEVEL IS NOT NULL THEN
225 L_LOOKUP_TYPE := 'WSH_SERVICE_LEVELS';
226 SELECT
227 MEANING
228 INTO L_SERVICE_LEVEL
229 FROM
230 FND_LOOKUP_VALUES_VL
231 WHERE LOOKUP_TYPE = L_LOOKUP_TYPE
232 AND LOOKUP_CODE = SERVICE_LEVEL;
233 ELSE
234 L_SERVICE_LEVEL := NULL;
235 END IF;
236 RETURN L_SERVICE_LEVEL;
237 END CF_SERVICE_LEVELFORMULA;
238
239 FUNCTION CF_TRIP_STATUSFORMULA(TRIP_STATUS IN VARCHAR2) RETURN CHAR IS
240 L_TRIP_STATUS VARCHAR2(80);
241 BEGIN
242 SELECT
243 MEANING
244 INTO L_TRIP_STATUS
245 FROM
246 FND_LOOKUP_VALUES_VL
247 WHERE LOOKUP_TYPE = 'TRIP_STATUS'
248 AND LOOKUP_CODE = TRIP_STATUS;
249 RETURN L_TRIP_STATUS;
250 END CF_TRIP_STATUSFORMULA;
251
252 FUNCTION CF_STOP_STATUSFORMULA(STOP_STATUS IN VARCHAR2) RETURN CHAR IS
253 L_STOP_STATUS VARCHAR2(80);
254 BEGIN
255 SELECT
256 MEANING
257 INTO L_STOP_STATUS
258 FROM
259 FND_LOOKUP_VALUES_VL
260 WHERE LOOKUP_TYPE = 'TRIP_STOP_STATUS'
261 AND LOOKUP_CODE = STOP_STATUS;
262 RETURN L_STOP_STATUS;
263 END CF_STOP_STATUSFORMULA;
264
265 FUNCTION CF_WEIGHT_UOMFORMULA(WEIGHT_UOM IN VARCHAR2) RETURN CHAR IS
266 L_WEIGHT_UOM VARCHAR2(25);
267 BEGIN
268 IF WEIGHT_UOM IS NOT NULL THEN
269 SELECT
270 UNIT_OF_MEASURE_TL
271 INTO L_WEIGHT_UOM
272 FROM
273 MTL_UNITS_OF_MEASURE_VL
274 WHERE UOM_CODE = WEIGHT_UOM;
275 ELSE
276 L_WEIGHT_UOM := '';
277 END IF;
278 RETURN L_WEIGHT_UOM;
279 END CF_WEIGHT_UOMFORMULA;
280
281 FUNCTION CF_VOLUME_UOMFORMULA(VOLUME_UOM IN VARCHAR2) RETURN CHAR IS
282 CURSOR L_GET_UOM(P_VOLUME_UOM_CODE IN VARCHAR2) IS
283 SELECT
284 UNIT_OF_MEASURE_TL
285 FROM
286 MTL_UNITS_OF_MEASURE_VL
287 WHERE UOM_CODE = P_VOLUME_UOM_CODE;
288 L_VOLUME_UOM VARCHAR2(25);
289 BEGIN
290 IF VOLUME_UOM IS NOT NULL THEN
291 OPEN L_GET_UOM(VOLUME_UOM);
292 FETCH L_GET_UOM
293 INTO L_VOLUME_UOM;
294 CLOSE L_GET_UOM;
295 ELSE
296 L_VOLUME_UOM := '';
297 END IF;
298 RETURN L_VOLUME_UOM;
299 EXCEPTION
300 WHEN OTHERS THEN
301 IF L_GET_UOM%ISOPEN THEN
302 CLOSE L_GET_UOM;
303 END IF;
304 RETURN NULL;
305 END CF_VOLUME_UOMFORMULA;
306
307 FUNCTION CF_DELIVERY_STATUS_DPFORMULA(DELIVERY_STATUS_DP IN VARCHAR2) RETURN CHAR IS
308 L_STATUS_MEANING VARCHAR2(80);
309 BEGIN
310 SELECT
311 MEANING
312 INTO L_STATUS_MEANING
313 FROM
314 FND_LOOKUP_VALUES_VL
315 WHERE LOOKUP_TYPE = 'DELIVERY_STATUS'
316 AND LOOKUP_CODE = DELIVERY_STATUS_DP;
317 RETURN L_STATUS_MEANING;
318 END CF_DELIVERY_STATUS_DPFORMULA;
319
320 FUNCTION CF_WEIGHT_UOM_DPFORMULA(WEIGHT_UOM_DP IN VARCHAR2) RETURN CHAR IS
321 L_WEIGHT_UNIT VARCHAR2(25);
322 BEGIN
323 IF WEIGHT_UOM_DP IS NOT NULL THEN
324 SELECT
325 UNIT_OF_MEASURE_TL
326 INTO L_WEIGHT_UNIT
327 FROM
328 MTL_UNITS_OF_MEASURE_VL
329 WHERE UOM_CODE = WEIGHT_UOM_DP;
330 ELSE
331 L_WEIGHT_UNIT := NULL;
332 END IF;
333 RETURN L_WEIGHT_UNIT;
334 EXCEPTION
335 WHEN OTHERS THEN
336 RETURN NULL;
337 END CF_WEIGHT_UOM_DPFORMULA;
338
339 FUNCTION CF_VOLUME_UOM_DPFORMULA(VOLUME_UOM_DP IN VARCHAR2) RETURN CHAR IS
340 L_VOLUME_UNIT VARCHAR2(25);
341 BEGIN
342 IF VOLUME_UOM_DP IS NOT NULL THEN
343 SELECT
344 UNIT_OF_MEASURE_TL
345 INTO L_VOLUME_UNIT
346 FROM
347 MTL_UNITS_OF_MEASURE_VL
348 WHERE UOM_CODE = VOLUME_UOM_DP;
349 ELSE
350 L_VOLUME_UNIT := NULL;
351 END IF;
352 RETURN L_VOLUME_UNIT;
353 EXCEPTION
354 WHEN OTHERS THEN
355 RETURN NULL;
356 END CF_VOLUME_UOM_DPFORMULA;
357
358 FUNCTION CF_VOLUME_UOM_DDFORMULA(VOLUME_UOM_DD IN VARCHAR2) RETURN CHAR IS
359 L_VOLUME_UNIT VARCHAR2(25);
360 BEGIN
361 IF VOLUME_UOM_DD IS NOT NULL THEN
362 SELECT
363 UNIT_OF_MEASURE_TL
364 INTO L_VOLUME_UNIT
365 FROM
366 MTL_UNITS_OF_MEASURE_VL
367 WHERE UOM_CODE = VOLUME_UOM_DD;
368 ELSE
369 L_VOLUME_UNIT := NULL;
370 END IF;
371 RETURN L_VOLUME_UNIT;
372 EXCEPTION
373 WHEN OTHERS THEN
374 RETURN NULL;
375 END CF_VOLUME_UOM_DDFORMULA;
376
377 FUNCTION CF_WEIGHT_UOM_DDFORMULA(WEIGHT_UOM_DD IN VARCHAR2) RETURN CHAR IS
378 L_WEIGHT_UNIT VARCHAR2(25);
379 BEGIN
380 IF WEIGHT_UOM_DD IS NOT NULL THEN
381 SELECT
382 UNIT_OF_MEASURE_TL
383 INTO L_WEIGHT_UNIT
384 FROM
385 MTL_UNITS_OF_MEASURE_VL
386 WHERE UOM_CODE = WEIGHT_UOM_DD;
387 ELSE
388 L_WEIGHT_UNIT := NULL;
389 END IF;
390 RETURN L_WEIGHT_UNIT;
391 EXCEPTION
392 WHEN OTHERS THEN
393 RETURN NULL;
394 END CF_WEIGHT_UOM_DDFORMULA;
395
396 FUNCTION CF_DELIVERY_STATUS_DDFORMULA(DELIVERY_STATUS_DD IN VARCHAR2) RETURN CHAR IS
397 L_STATUS_MEANING VARCHAR2(80);
398 BEGIN
399 SELECT
400 MEANING
401 INTO L_STATUS_MEANING
402 FROM
403 FND_LOOKUP_VALUES_VL
404 WHERE LOOKUP_TYPE = 'DELIVERY_STATUS'
405 AND LOOKUP_CODE = DELIVERY_STATUS_DD;
406 RETURN L_STATUS_MEANING;
407 END CF_DELIVERY_STATUS_DDFORMULA;
408
409 FUNCTION CF_COMMODITY_CLASS_PICKEDFORMU(INVENTORY_ITEM_ID_IP IN NUMBER
410 ,ORGANIZATION_ID_IP IN NUMBER) RETURN CHAR IS
411 L_CLASS_LIST VARCHAR2(1000);
412 CURSOR C_CATEGORY(C_INV_ITEM_ID IN NUMBER,C_INV_ORG_ID IN NUMBER) IS
413 SELECT
414 CONCATENATED_SEGMENTS COMM_CLASS
415 FROM
416 MTL_CATEGORIES_KFV MC,
417 MTL_ITEM_CATEGORIES MIC,
418 MTL_CATEGORY_SETS_VL MCSTL
419 WHERE MIC.INVENTORY_ITEM_ID = C_INV_ITEM_ID
420 AND MIC.ORGANIZATION_ID = C_INV_ORG_ID
421 AND MIC.CATEGORY_SET_ID = MCSTL.CATEGORY_SET_ID
422 AND MC.CATEGORY_ID = MIC.CATEGORY_ID
423 AND MCSTL.CATEGORY_SET_NAME = 'WSH_COMMODITY_CODE'
424 ORDER BY
425 MC.CATEGORY_ID;
426 BEGIN
427 /*SRW.REFERENCE(INVENTORY_ITEM_ID_IP)*/NULL;
428 /*SRW.REFERENCE(ORGANIZATION_ID_IP)*/NULL;
429 FOR c_rec IN C_CATEGORY(INVENTORY_ITEM_ID_IP , ORGANIZATION_ID_IP) LOOP
430 IF (L_CLASS_LIST IS NULL) THEN
431 L_CLASS_LIST := C_REC.COMM_CLASS;
432 ELSE
433 L_CLASS_LIST := L_CLASS_LIST || ', ' || C_REC.COMM_CLASS;
434 END IF;
435 END LOOP;
436 RETURN (L_CLASS_LIST);
437 EXCEPTION
438 WHEN OTHERS THEN
439 RETURN (NULL);
440 END CF_COMMODITY_CLASS_PICKEDFORMU;
441
442 FUNCTION CF_COMMODITY_CLASS_DROPPEDFORM(INVENTORY_ITEM_ID_D IN NUMBER
443 ,ORGANIZATION_ID_D IN NUMBER) RETURN CHAR IS
444 L_CLASS_LIST VARCHAR2(1000);
445 CURSOR C_CATEGORY(C_INV_ITEM_ID IN NUMBER,C_INV_ORG_ID IN NUMBER) IS
446 SELECT
447 CONCATENATED_SEGMENTS COMM_CLASS
448 FROM
449 MTL_CATEGORIES_KFV MC,
450 MTL_ITEM_CATEGORIES MIC,
451 MTL_CATEGORY_SETS_VL MCSTL
452 WHERE MIC.INVENTORY_ITEM_ID = C_INV_ITEM_ID
453 AND MIC.ORGANIZATION_ID = C_INV_ORG_ID
454 AND MIC.CATEGORY_SET_ID = MCSTL.CATEGORY_SET_ID
455 AND MC.CATEGORY_ID = MIC.CATEGORY_ID
456 AND MCSTL.CATEGORY_SET_NAME = 'WSH_COMMODITY_CODE'
457 ORDER BY
458 MC.CATEGORY_ID;
459 BEGIN
460 /*SRW.REFERENCE(INVENTORY_ITEM_ID_D)*/NULL;
461 /*SRW.REFERENCE(ORGANIZATION_ID_D)*/NULL;
462 FOR c_rec IN C_CATEGORY(INVENTORY_ITEM_ID_D , ORGANIZATION_ID_D) LOOP
463 IF (L_CLASS_LIST IS NULL) THEN
464 L_CLASS_LIST := C_REC.COMM_CLASS;
465 ELSE
466 L_CLASS_LIST := L_CLASS_LIST || ', ' || C_REC.COMM_CLASS;
467 END IF;
468 END LOOP;
469 RETURN (L_CLASS_LIST);
470 EXCEPTION
471 WHEN OTHERS THEN
472 RETURN (NULL);
473 END CF_COMMODITY_CLASS_DROPPEDFORM;
474
475 FUNCTION CF_PARENT_DEL_NAME_DPFORMULA(DELIVERY_LEG_ID_DP IN NUMBER) RETURN CHAR IS
476 L_PARENT_DEL_NAME VARCHAR2(30) := NULL;
477 BEGIN
478 IF DELIVERY_LEG_ID_DP IS NOT NULL THEN
479 SELECT
480 NAME
481 INTO L_PARENT_DEL_NAME
482 FROM
483 WSH_NEW_DELIVERIES
484 WHERE DELIVERY_ID in (
485 SELECT
486 DELIVERY_ID
487 FROM
488 WSH_DELIVERY_LEGS
489 WHERE DELIVERY_LEG_ID in (
490 SELECT
491 PARENT_DELIVERY_LEG_ID
492 FROM
493 WSH_DELIVERY_LEGS
494 WHERE DELIVERY_LEG_ID = DELIVERY_LEG_ID_DP ) )
495 AND ROWNUM = 1;
496 END IF;
497 RETURN L_PARENT_DEL_NAME;
498 EXCEPTION
499 WHEN OTHERS THEN
500 RETURN L_PARENT_DEL_NAME;
501 END CF_PARENT_DEL_NAME_DPFORMULA;
502
503 FUNCTION CF_PARENT_DEL_NAME_DDFORMULA(DELIVERY_LEG_ID_DD IN NUMBER) RETURN CHAR IS
504 L_PARENT_DEL_NAME VARCHAR2(30) := NULL;
505 BEGIN
506 IF DELIVERY_LEG_ID_DD IS NOT NULL THEN
507 SELECT
508 NAME
509 INTO L_PARENT_DEL_NAME
510 FROM
511 WSH_NEW_DELIVERIES
512 WHERE DELIVERY_ID in (
513 SELECT
514 DELIVERY_ID
515 FROM
516 WSH_DELIVERY_LEGS
517 WHERE DELIVERY_LEG_ID in (
518 SELECT
519 PARENT_DELIVERY_LEG_ID
520 FROM
521 WSH_DELIVERY_LEGS
522 WHERE DELIVERY_LEG_ID = DELIVERY_LEG_ID_DD ) )
523 AND ROWNUM = 1;
524 END IF;
525 RETURN L_PARENT_DEL_NAME;
526 EXCEPTION
527 WHEN OTHERS THEN
528 RETURN L_PARENT_DEL_NAME;
529 END CF_PARENT_DEL_NAME_DDFORMULA;
530
531 FUNCTION CF_FREIGHT_TERMS_DPFORMULA(FREIGHT_TERMS_CODE IN VARCHAR2) RETURN CHAR IS
532 L_FREIGHT_TERMS VARCHAR2(80);
533 BEGIN
534 /*SRW.REFERENCE(FREIGHT_TERMS_CODE)*/NULL;
535 IF FREIGHT_TERMS_CODE IS NOT NULL THEN
536 SELECT
537 FV.FREIGHT_TERMS
538 INTO L_FREIGHT_TERMS
539 FROM
540 OE_FRGHT_TERMS_ACTIVE_V FV
541 WHERE FV.FREIGHT_TERMS_CODE = CF_FREIGHT_TERMS_DPFORMULA.FREIGHT_TERMS_CODE;
542 ELSE
543 L_FREIGHT_TERMS := '';
544 END IF;
545 RETURN (L_FREIGHT_TERMS);
546 EXCEPTION
547 WHEN NO_DATA_FOUND THEN
548 /*SRW.MESSAGE(1
549 ,'Freight terms lookups not found.')*/NULL;
550 RETURN (NULL);
551 WHEN OTHERS THEN
552 RAISE;
553 END CF_FREIGHT_TERMS_DPFORMULA;
554
555 FUNCTION CF_FREIGHT_TERMS_DDFORMULA(FREIGHT_TERMS_DD1 IN VARCHAR2) RETURN CHAR IS
556 L_FREIGHT_TERMS VARCHAR2(80);
557 BEGIN
558 /*SRW.REFERENCE(FREIGHT_TERMS_DD1)*/NULL;
559 IF FREIGHT_TERMS_DD1 IS NOT NULL THEN
560 SELECT
561 FV.FREIGHT_TERMS
562 INTO L_FREIGHT_TERMS
563 FROM
564 OE_FRGHT_TERMS_ACTIVE_V FV
565 WHERE FV.FREIGHT_TERMS_CODE = FREIGHT_TERMS_DD1;
566 ELSE
567 L_FREIGHT_TERMS := '';
568 END IF;
569 RETURN (L_FREIGHT_TERMS);
570 EXCEPTION
571 WHEN NO_DATA_FOUND THEN
572 /*SRW.MESSAGE(1
573 ,'Freight terms lookups not found.')*/NULL;
574 RETURN (NULL);
575 WHEN OTHERS THEN
576 RAISE;
577 END CF_FREIGHT_TERMS_DDFORMULA;
578
579 END WSH_WSHRDMBL_XMLP_PKG;
580
581
[Home] [Help]