1 PACKAGE BODY WSH_WSHRDBOL_XMLP_PKG AS
2 /* $Header: WSHRDBOLB.pls 120.3.12010000.3 2009/12/03 10:39:11 mvudugul ship $ */
3 FUNCTION AFTERPFORM RETURN BOOLEAN IS
4 BEGIN
5 P_DELIVERY_DATE_HIGH_V:=P_DELIVERY_DATE_HIGH;
6 LP_WHERE_CLAUSE := 'where 1 = 1';
7 IF P_DELIVERY_LEG_ID IS NOT NULL THEN
8 LP_DELIVERY_LEG_ID := 'AND wbrv.delivery_leg_id = :P_DELIVERY_LEG_ID';
9 END IF;
10 IF P_DELIVERY_DATE_HIGH IS NOT NULL THEN
11 --P_DELIVERY_DATE_HIGH := P_DELIVERY_DATE_HIGH + (86399 / 86400);
12 P_DELIVERY_DATE_HIGH_V := P_DELIVERY_DATE_HIGH + (86399 / 86400);
13 END IF;
14 --IF P_DELIVERY_DATE_LOW IS NOT NULL OR P_DELIVERY_DATE_HIGH IS NOT NULL THEN
15 IF P_DELIVERY_DATE_LOW IS NOT NULL OR P_DELIVERY_DATE_HIGH_V IS NOT NULL THEN
16 IF P_DELIVERY_DATE_LOW IS NULL THEN
17 --LP_DATE_RANGE := 'AND wbrv.delivery_leg_id IN (select distinct delivery_leg_id from wsh_delivery_legs where pick_up_stop_id in (select stop_id from wsh_trip_stops where planned_departure_date <= :P_DELIVERY_DATE_HIGH))';
18 LP_DATE_RANGE := 'AND wbrv.delivery_leg_id IN (select distinct delivery_leg_id from wsh_delivery_legs where pick_up_stop_id in (select stop_id from wsh_trip_stops where planned_departure_date <= :P_DELIVERY_DATE_HIGH_V))';
19 --ELSIF P_DELIVERY_DATE_HIGH IS NULL THEN
20 ELSIF P_DELIVERY_DATE_HIGH_V IS NULL THEN
21 LP_DATE_RANGE := 'AND wbrv.delivery_leg_id IN (select distinct delivery_leg_id from wsh_delivery_legs where pick_up_stop_id in (select stop_id from wsh_trip_stops where planned_departure_date >= :P_DELIVERY_DATE_LOW))';
22 ELSE
23 --LP_DATE_RANGE := 'AND wbrv.delivery_leg_id in (select distinct delivery_leg_id from wsh_delivery_legs where pick_up_stop_id in
24 --(select stop_id from wsh_trip_stops where planned_departure_date between :P_DELIVERY_DATE_LOW and :P_DELIVERY_DATE_HIGH))';
25 LP_DATE_RANGE := 'AND wbrv.delivery_leg_id in (select distinct delivery_leg_id from wsh_delivery_legs where pick_up_stop_id in
26 (select stop_id from wsh_trip_stops where planned_departure_date between :P_DELIVERY_DATE_LOW and :P_DELIVERY_DATE_HIGH_V))';
27 END IF;
28 END IF;
29 IF P_FREIGHT_CODE IS NOT NULL THEN
30 LP_FREIGHT_CODE := 'AND wbrv.ship_method = :P_FREIGHT_CODE';
31 END IF;
32 IF P_DELIVERY_ID IS NOT NULL THEN
33 LP_DELIVERY_ID := 'AND (wbrv.delivery_id = :P_DELIVERY_ID or
34 exists (select delivery_id from wsh_delivery_legs wdl1
35 where wdl1.delivery_id = wbrv.delivery_id
36 and delivery_leg_id in
37 (select parent_delivery_leg_id from wsh_delivery_legs
38 where delivery_id = :P_DELIVERY_ID))
39 or
40 exists ( select delivery_id from wsh_delivery_legs wdl2
41 where wdl2.delivery_id = wbrv.delivery_id
42 and parent_delivery_leg_id in
43 (select delivery_leg_id from wsh_delivery_legs
44 where delivery_id = :P_DELIVERY_ID))
45 )';
46 END IF;
47 --Commented following in bug 7409923
48 /*
49 IF P_DELIVERY_ID IS NULL AND P_TRIP_ID IS NULL THEN
50 LP_DELIVERY_ID := 'AND (exists (select delivery_id from wsh_delivery_legs wdl1
51 where wdl1.delivery_id = wbrv.delivery_id
52 and delivery_leg_id in
53 (select parent_delivery_leg_id from wsh_delivery_legs
54 where rownum=1))
55 or
56 exists ( select delivery_id from wsh_delivery_legs wdl2
57 where wdl2.delivery_id = wbrv.delivery_id
58 and parent_delivery_leg_id is not null)
59 )';
60 END IF;
61 */
62
63 IF P_ORGANIZATION_ID IS NOT NULL THEN
64 LP_ORGANIZATION_ID := 'AND wbrv.organization_id = :P_ORGANIZATION_ID';
65 END IF;
66 IF P_TRIP_ID IS NOT NULL THEN
67 LP_TRIP_ID := 'AND wbrv.delivery_leg_id in (select distinct delivery_leg_id from wsh_delivery_legs where pick_up_stop_id in (select stop_id from wsh_trip_stops where trip_id = :P_TRIP_ID ))';
68 END IF;
69 RETURN (TRUE);
70 END AFTERPFORM;
71
72 FUNCTION CF_LPNFORMULA(MASTER_CONTAINER_LPN IN VARCHAR2) RETURN CHAR IS
73 L_LPN VARCHAR2(30);
74 BEGIN
75 /*SRW.REFERENCE(MASTER_CONTAINER_LPN)*/NULL;
76 RETURN ('LPN# ' || MASTER_CONTAINER_LPN);
77 END CF_LPNFORMULA;
78
79 FUNCTION CF_CONTAINER_ITEM_DESCRIPTIONF(DELIVERY_DETAIL_ID IN NUMBER
80 ,ORGANIZATION_ID IN NUMBER) RETURN CHAR IS
81 L_DESCRIPTION VARCHAR2(32767) := NULL;
82 L_CLASSIFICATION VARCHAR2(32767) := NULL;
83 L_HM VARCHAR2(1) := NULL;
84 L_NUM_PACKAGES NUMBER := NULL;
85 L_RETURN_STATUS VARCHAR2(1) := NULL;
86 BEGIN
87 /*SRW.REFERENCE(DELIVERY_DETAIL_ID)*/NULL;
88 WSH_BOLS_UTIL_PKG.GET_MASTER_CONTAINER_CONTENTS(DELIVERY_DETAIL_ID
89 ,'REPORT'
90 ,P_ITEM_DISPLAY
91 ,ORGANIZATION_ID
92 ,L_CLASSIFICATION
93 ,L_DESCRIPTION
94 ,L_HM
95 ,L_NUM_PACKAGES
96 ,L_RETURN_STATUS);
97 CP_CLASSIFICATION := L_CLASSIFICATION;
98 CP_HM := L_HM;
99 CP_NUM_PACKAGES := L_NUM_PACKAGES;
100 RETURN (L_DESCRIPTION);
101 END CF_CONTAINER_ITEM_DESCRIPTIONF;
102
103 FUNCTION CF_CONTAINER_GROSS_WTFORMULA(MASTER_CONTAINER_GROSS_WEIGHT IN NUMBER
104 ,MASTER_CONTAINER_WEIGHT_UOM IN VARCHAR2) RETURN CHAR IS
105 BEGIN
106 /*SRW.REFERENCE(MASTER_CONTAINER_GROSS_WEIGHT)*/NULL;
107 /*SRW.REFERENCE(MASTER_CONTAINER_WEIGHT_UOM)*/NULL;
108 RETURN ('G ' || TO_CHAR(MASTER_CONTAINER_GROSS_WEIGHT) || ' ' || MASTER_CONTAINER_WEIGHT_UOM);
109 END CF_CONTAINER_GROSS_WTFORMULA;
110
111 FUNCTION CF_1FORMULA(MASTER_CONTAINER_NET_WEIGHT IN NUMBER
112 ,MASTER_CONTAINER_WEIGHT_UOM IN VARCHAR2) RETURN CHAR IS
113 BEGIN
114 /*SRW.REFERENCE(MASTER_CONTAINER_NET_WEIGHT)*/NULL;
115 /*SRW.REFERENCE(MASTER_CONTAINER_WEIGHT_UOM)*/NULL;
116 RETURN ('N ' || TO_CHAR(MASTER_CONTAINER_NET_WEIGHT) || ' ' || MASTER_CONTAINER_WEIGHT_UOM);
117 END CF_1FORMULA;
118
119 FUNCTION CF_CONTAINER_TARE_WTFORMULA(MASTER_CONTAINER_TARE_WEIGHT IN NUMBER
120 ,MASTER_CONTAINER_WEIGHT_UOM IN VARCHAR2) RETURN CHAR IS
121 BEGIN
122 /*SRW.REFERENCE(MASTER_CONTAINER_TARE_WEIGHT)*/NULL;
123 /*SRW.REFERENCE(MASTER_CONTAINER_WEIGHT_UOM)*/NULL;
124 RETURN ('T ' || TO_CHAR(MASTER_CONTAINER_TARE_WEIGHT) || ' ' || MASTER_CONTAINER_WEIGHT_UOM);
125 END CF_CONTAINER_TARE_WTFORMULA;
126
127 FUNCTION CF_CONTAINER_VOLUMEFORMULA(MASTER_CONTAINER_VOLUME IN NUMBER
128 ,MASTER_CONTAINER_VOLUME_UOM IN VARCHAR2) RETURN CHAR IS
129 BEGIN
130 /*SRW.REFERENCE(MASTER_CONTAINER_VOLUME)*/NULL;
131 /*SRW.REFERENCE(MASTER_CONTAINER_VOLUME_UOM)*/NULL;
132 RETURN (TO_CHAR(MASTER_CONTAINER_VOLUME) || ' ' || MASTER_CONTAINER_VOLUME_UOM);
133 END CF_CONTAINER_VOLUMEFORMULA;
134
135 FUNCTION CF_UNPACKED_ITEM_DESCFORMULA(UNPACKED_ITEM_DESCRIPTION IN VARCHAR2
136 ,UNPACKED_ITEM_SHIPPED_QUANTITY IN NUMBER
137 ,UNPACKED_ITEM_QUANTITY_UOM IN VARCHAR2
138 ,UNPACKED_ITEM_ORGANIZATION_ID IN NUMBER
139 ,UNPACKED_ITEM_INV_ITEM_ID IN NUMBER
140 ,UNPACKED_ITEM_QUANTITY_UOM2 IN VARCHAR2
141 ,UNPACKED_ITEM_SHIPPED_QTY2 IN NUMBER) RETURN CHAR IS
142 CURSOR INVENTORY_LABEL(ID IN NUMBER,ORG_ID IN NUMBER) IS
143 SELECT
144 DESCRIPTION
145 FROM
146 MTL_SYSTEM_ITEMS_VL
147 WHERE INVENTORY_ITEM_ID = ID
148 AND ORGANIZATION_ID = ORG_ID;
149 L_DESCRIPTION VARCHAR2(1900);
150 L_SECOND_DESC VARCHAR2(100);
151 MESG VARCHAR2(10);
152 L_ITEM_DESC VARCHAR2(250);
153 BEGIN
154 /*SRW.REFERENCE(UNPACKED_ITEM_DESCRIPTION)*/NULL;
155 /*SRW.REFERENCE(UNPACKED_ITEM_SHIPPED_QUANTITY)*/NULL;
156 /*SRW.REFERENCE(UNPACKED_ITEM_QUANTITY_UOM)*/NULL;
157 /*SRW.REFERENCE(UNPACKED_ITEM_ORGANIZATION_ID)*/NULL;
158 /*SRW.REFERENCE(UNPACKED_ITEM_INV_ITEM_ID)*/NULL;
159 L_ITEM_DESC := UNPACKED_ITEM_DESCRIPTION;
160 IF (UNPACKED_ITEM_INV_ITEM_ID IS NOT NULL) THEN
161 OPEN INVENTORY_LABEL(UNPACKED_ITEM_INV_ITEM_ID,UNPACKED_ITEM_ORGANIZATION_ID);
162 FETCH INVENTORY_LABEL
163 INTO L_ITEM_DESC;
164 IF (INVENTORY_LABEL%NOTFOUND) THEN
165 L_ITEM_DESC := UNPACKED_ITEM_DESCRIPTION;
166 END IF;
167 CLOSE INVENTORY_LABEL;
168 END IF;
169 IF P_ITEM_DISPLAY = 'D' THEN
170 L_DESCRIPTION := L_ITEM_DESC;
171 ELSIF P_ITEM_DISPLAY = 'F' THEN
172 /* commented by anvarshn for lsp . added code to replace GENERIC_FLEX_NAME with get_item_name
173 L_DESCRIPTION := WSH_UTIL_CORE.GENERIC_FLEX_NAME(UNPACKED_ITEM_INV_ITEM_ID
174 ,UNPACKED_ITEM_ORGANIZATION_ID
175 ,'INV'
176 ,P_ITEM_FLEX_CODE
177 ,101);
178 ELSE
179 L_DESCRIPTION := WSH_UTIL_CORE.GENERIC_FLEX_NAME(UNPACKED_ITEM_INV_ITEM_ID
180 ,UNPACKED_ITEM_ORGANIZATION_ID
181 ,'INV'
182 ,P_ITEM_FLEX_CODE
183 ,101) || ' ' || L_ITEM_DESC;
184 */
185 L_DESCRIPTION := WSH_UTIL_CORE.get_item_name(UNPACKED_ITEM_INV_ITEM_ID
186 ,UNPACKED_ITEM_ORGANIZATION_ID
187 ,'MSTK'
188 ,101,'Y');
189 ELSE
190 L_DESCRIPTION := WSH_UTIL_CORE.get_item_name(UNPACKED_ITEM_INV_ITEM_ID
191 ,UNPACKED_ITEM_ORGANIZATION_ID
192 ,'MSTK'
193 ,101, 'Y') || ' ' || L_ITEM_DESC;
194 END IF;
195 IF (NVL(UNPACKED_ITEM_QUANTITY_UOM2
196 ,'*') <> '*') THEN
197 L_SECOND_DESC := ' ( ' || NVL(UNPACKED_ITEM_SHIPPED_QTY2
198 ,0) || ' ' || UNPACKED_ITEM_QUANTITY_UOM2 || ' )';
199 ELSE
200 L_SECOND_DESC := NULL;
201 END IF;
202 FND_MESSAGE.SET_NAME('WSH'
203 ,'WSH_WEB_OF_LABEL');
204 MESG := FND_MESSAGE.GET;
205 RETURN (TO_CHAR(UNPACKED_ITEM_SHIPPED_QUANTITY) || ' ' || UNPACKED_ITEM_QUANTITY_UOM || L_SECOND_DESC || ' ' || MESG || ' ' || L_DESCRIPTION);
206 END CF_UNPACKED_ITEM_DESCFORMULA;
207
208 FUNCTION CF_UNPACKED_ITEM_WTFORMULA(UNPACKED_ITEM_NET_WEIGHT IN NUMBER
209 ,UNPACKED_ITEM_WEIGHT_UOM IN VARCHAR2) RETURN CHAR IS
210 BEGIN
211 /*SRW.REFERENCE(UNPACKED_ITEM_NET_WEIGHT)*/NULL;
212 /*SRW.REFERENCE(UNPACKED_ITEM_WEIGHT_UOM)*/NULL;
213 RETURN (TO_CHAR(UNPACKED_ITEM_NET_WEIGHT) || ' ' || UNPACKED_ITEM_WEIGHT_UOM);
214 END CF_UNPACKED_ITEM_WTFORMULA;
215
216 FUNCTION CF_UNPACKED_ITEM_VOLFORMULA(UNPACKED_ITEM_VOLUME IN NUMBER
217 ,UNPACKED_ITEM_VOLUME_UOM IN VARCHAR2) RETURN CHAR IS
218 BEGIN
219 /*SRW.REFERENCE(UNPACKED_ITEM_VOLUME)*/NULL;
220 /*SRW.REFERENCE(UNPACKED_ITEM_VOLUME_UOM)*/NULL;
221 RETURN (TO_CHAR(UNPACKED_ITEM_VOLUME) || ' ' || UNPACKED_ITEM_VOLUME_UOM);
222 END CF_UNPACKED_ITEM_VOLFORMULA;
223
224 FUNCTION CF_FC_COLLECT_CURRENCYFORMULA(FC_COLLECT_CURRENCY IN VARCHAR2) RETURN NUMBER IS
225 BEGIN
226 /*SRW.REFERENCE(FC_COLLECT_CURRENCY)*/NULL;
227 CP_TOTAL_COLLECT_CURRENCY := FC_COLLECT_CURRENCY;
228 RETURN (0);
229 END CF_FC_COLLECT_CURRENCYFORMULA;
230
231 FUNCTION CP_NUM_PACKAGESFORMULA RETURN NUMBER IS
232 BEGIN
233 NULL;
234 END CP_NUM_PACKAGESFORMULA;
235
236 FUNCTION CF_NUM_OF_LPN1FORMULA(DELIVERY_ID3 IN NUMBER) RETURN NUMBER IS
237 CURSOR LPN IS
238 SELECT
239 NUMBER_OF_LPN
240 FROM
241 WSH_NEW_DELIVERIES
242 WHERE DELIVERY_ID = DELIVERY_ID3;
243 L_NUM_LPN NUMBER;
244 BEGIN
245 OPEN LPN;
246 FETCH LPN
247 INTO L_NUM_LPN;
248 CLOSE LPN;
249 RETURN (L_NUM_LPN);
250 END CF_NUM_OF_LPN1FORMULA;
251
252 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
253 BEGIN
254 BEGIN
255 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
256 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
257 EXCEPTION
258 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
259 /*SRW.MESSAGE(1
260 ,'Failed FND SRWINIT.')*/NULL;
261 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
262 END;
263 RETURN (TRUE);
264 END BEFOREREPORT;
265
266 FUNCTION AFTERREPORT RETURN BOOLEAN IS
267 BEGIN
268 BEGIN
269 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
270 EXCEPTION
271 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
272 /*SRW.MESSAGE(1
273 ,'Failed in SRWEXIT')*/NULL;
274 RAISE;
275 END;
276 RETURN (TRUE);
277 END AFTERREPORT;
278
279 FUNCTION LP_WHERE_CLAUSEVALIDTRIGGER RETURN BOOLEAN IS
280 BEGIN
281 RETURN (TRUE);
282 END LP_WHERE_CLAUSEVALIDTRIGGER;
283
284 FUNCTION CF_DOCK_CODEFORMULA(DELIVERY_ID3 IN NUMBER) RETURN CHAR IS
285 L_DOCK_CODE WSH_DELIVERY_DETAILS.CUSTOMER_DOCK_CODE%TYPE;
286 L_PREV_DOCK_CODE WSH_DELIVERY_DETAILS.CUSTOMER_DOCK_CODE%TYPE;
287 L_COUNT NUMBER;
288 CURSOR C_DOCK IS
289 SELECT
290 DISTINCT
291 ( WDD.CUSTOMER_DOCK_CODE )
292 FROM
293 WSH_DELIVERY_DETAILS WDD,
294 WSH_DELIVERY_ASSIGNMENTS_V WDA
295 WHERE WDA.DELIVERY_ID = DELIVERY_ID3
296 AND WDA.DELIVERY_DETAIL_ID = WDD.DELIVERY_DETAIL_ID
297 AND NVL(WDD.CONTAINER_FLAG
298 ,'N') = 'N'
299 AND WDD.CUSTOMER_DOCK_CODE is not null
300 GROUP BY
301 WDD.CUSTOMER_DOCK_CODE;
302 BEGIN
303 L_COUNT := 0;
304 L_PREV_DOCK_CODE := NULL;
305 L_DOCK_CODE := NULL;
306 OPEN C_DOCK;
307 LOOP
308 FETCH C_DOCK
309 INTO L_DOCK_CODE;
310 EXIT WHEN C_DOCK%NOTFOUND;
311 L_COUNT := L_COUNT + 1;
312 IF (L_COUNT > 1) THEN
313 IF (L_DOCK_CODE <> L_PREV_DOCK_CODE) THEN
314 L_DOCK_CODE := NULL;
315 CLOSE C_DOCK;
316 EXIT;
317 END IF;
318 END IF;
319 L_PREV_DOCK_CODE := L_DOCK_CODE;
320 END LOOP;
321 CLOSE C_DOCK;
322 RETURN L_DOCK_CODE;
323 EXCEPTION
324 WHEN NO_DATA_FOUND THEN
325 L_DOCK_CODE := NULL;
326 RETURN (L_DOCK_CODE);
327 WHEN OTHERS THEN
328 L_DOCK_CODE := NULL;
329 RETURN (L_DOCK_CODE);
330 END CF_DOCK_CODEFORMULA;
331
332 FUNCTION CF_CARRIER_NAMEFORMULA(SHIP_METHOD IN VARCHAR2) RETURN CHAR IS
333 L_CARRIER_NAME VARCHAR2(30);
334 L_CARRIER_ID NUMBER;
335 BEGIN
336 SELECT
337 CARRIER_ID
338 INTO L_CARRIER_ID
339 FROM
340 WSH_CARRIER_SERVICES
341 WHERE SHIP_METHOD_CODE = SHIP_METHOD;
342 SELECT
343 PARTY_NAME
344 INTO L_CARRIER_NAME
345 FROM
346 HZ_PARTIES
347 WHERE PARTY_ID = L_CARRIER_ID;
348 RETURN (L_CARRIER_NAME);
349 EXCEPTION
350 WHEN OTHERS THEN
351 RETURN (NULL);
352 END CF_CARRIER_NAMEFORMULA;
353
354 FUNCTION CF_MBOL_NUMBERFORMULA(TRIP_ID1 IN NUMBER) RETURN CHAR IS
355 L_MBOL_NUMBER VARCHAR2(50);
356 BEGIN
357 SELECT
358 SEQUENCE_NUMBER
359 INTO L_MBOL_NUMBER
360 FROM
361 WSH_DOCUMENT_INSTANCES
362 WHERE ENTITY_ID = TRIP_ID1
363 AND ENTITY_NAME = 'WSH_TRIPS';
364 RETURN L_MBOL_NUMBER;
365 EXCEPTION
366 WHEN NO_DATA_FOUND THEN
367 RETURN NULL;
368 END CF_MBOL_NUMBERFORMULA;
369
370 FUNCTION CF_CUSTOMER_NAMEFORMULA(CUSTOMER_NAME IN VARCHAR2) RETURN CHAR IS
371 L_CUSTOMER_NAME HZ_PARTIES.PARTY_NAME%TYPE;
372 L_PERSON_TITLE HZ_PARTIES.PERSON_TITLE%TYPE;
373 L_PERSON_TITLE_UP HZ_PARTIES.PERSON_TITLE%TYPE;
374 L_LOOKUP_TYPE VARCHAR2(20);
375 BEGIN
376 /*SRW.REFERENCE(CUSTOMER_NAME)*/NULL;
377 SELECT
378 PARTY_NAME,
379 NVL(PERSON_PRE_NAME_ADJUNCT
380 ,PERSON_TITLE) TITLE
381 INTO L_CUSTOMER_NAME,L_PERSON_TITLE
382 FROM
383 HZ_PARTIES
384 WHERE PARTY_NAME = CUSTOMER_NAME;
385 IF L_PERSON_TITLE IS NOT NULL THEN
386 BEGIN
387 L_LOOKUP_TYPE := 'RESPONSIBILITY';
388 L_PERSON_TITLE_UP := UPPER(L_PERSON_TITLE);
389 SELECT
390 MEANING || ' ' || L_CUSTOMER_NAME
391 INTO L_CUSTOMER_NAME
392 FROM
393 AR_LOOKUPS
394 WHERE LOOKUP_CODE = L_PERSON_TITLE_UP
395 AND LOOKUP_TYPE = L_LOOKUP_TYPE;
396 EXCEPTION
397 WHEN OTHERS THEN
398 L_CUSTOMER_NAME := L_PERSON_TITLE || ' ' || L_CUSTOMER_NAME;
399 END;
400 END IF;
401 RETURN (L_CUSTOMER_NAME);
402 EXCEPTION
403 WHEN OTHERS THEN
404 RETURN (CUSTOMER_NAME);
405 END CF_CUSTOMER_NAMEFORMULA;
406
407 FUNCTION CF_COMMODITY_CLASSFORMULA(UNPACKED_ITEM_INV_ITEM_ID IN NUMBER
408 ,UNPACKED_ITEM_ORGANIZATION_ID IN NUMBER) RETURN CHAR IS
409 L_CLASS_LIST VARCHAR2(1000);
410 CURSOR C_CATEGORY(C_INV_ITEM_ID IN NUMBER,C_INV_ORG_ID IN NUMBER) IS
411 SELECT
412 CONCATENATED_SEGMENTS COMM_CLASS
413 FROM
414 MTL_CATEGORIES_KFV MC,
415 MTL_ITEM_CATEGORIES MIC,
416 MTL_CATEGORY_SETS_VL MCSTL
417 WHERE MIC.INVENTORY_ITEM_ID = C_INV_ITEM_ID
418 AND MIC.ORGANIZATION_ID = C_INV_ORG_ID
419 AND MIC.CATEGORY_SET_ID = MCSTL.CATEGORY_SET_ID
420 AND MC.CATEGORY_ID = MIC.CATEGORY_ID
421 AND MCSTL.CATEGORY_SET_NAME = 'WSH_COMMODITY_CODE'
422 ORDER BY
423 MC.CATEGORY_ID;
424 BEGIN
425 /*SRW.REFERENCE(UNPACKED_ITEM_INV_ITEM_ID)*/NULL;
426 /*SRW.REFERENCE(UNPACKED_ITEM_ORGANIZATION_ID)*/NULL;
427 FOR c_rec IN C_CATEGORY(UNPACKED_ITEM_INV_ITEM_ID,UNPACKED_ITEM_ORGANIZATION_ID) LOOP
428 IF (L_CLASS_LIST IS NULL) THEN
429 L_CLASS_LIST := C_REC.COMM_CLASS;
430 ELSE
431 L_CLASS_LIST := L_CLASS_LIST || ', ' || C_REC.COMM_CLASS;
432 END IF;
433 END LOOP;
434 RETURN (L_CLASS_LIST);
435 EXCEPTION
436 WHEN OTHERS THEN
437 RETURN (NULL);
438 END CF_COMMODITY_CLASSFORMULA;
439
440 FUNCTION CF_DETAIL_COMM_CLASSFORMULA(DELIVERY_DETAIL_ID IN NUMBER) RETURN CHAR IS
441 L_CLASS_LIST VARCHAR2(4000);
442 CURSOR C_CATEGORY(C_INV_ITEM_ID IN NUMBER,C_INV_ORG_ID IN NUMBER) IS
443 SELECT
444 MC.SEGMENT1 || '-' || MC.SEGMENT2 || '-' || MC.SEGMENT3 COMM_CLASS
445 FROM
446 MTL_CATEGORIES MC,
447 MTL_ITEM_CATEGORIES MIC,
448 MTL_CATEGORY_SETS_VL MCSTL
449 WHERE MIC.INVENTORY_ITEM_ID = C_INV_ITEM_ID
450 AND MIC.ORGANIZATION_ID = C_INV_ORG_ID
451 AND MIC.CATEGORY_SET_ID = MCSTL.CATEGORY_SET_ID
452 AND MC.CATEGORY_ID = MIC.CATEGORY_ID
453 AND MCSTL.CATEGORY_SET_NAME = 'WSH_COMMODITY_CODE'
454 ORDER BY
455 MC.CATEGORY_ID;
456 CURSOR C_DLV_DETAIL(C_DELIVERY_DETAIL_ID IN NUMBER) IS
457 SELECT
458 DISTINCT
459 INVENTORY_ITEM_ID,
460 ORGANIZATION_ID
461 FROM
462 WSH_DELIVERY_DETAILS WDD
463 WHERE WDD.DELIVERY_DETAIL_ID IN (
464 SELECT
465 WDA.DELIVERY_DETAIL_ID
466 FROM
467 WSH_DELIVERY_ASSIGNMENTS WDA
468 START WITH WDA.DELIVERY_DETAIL_ID = C_DELIVERY_DETAIL_ID
469 CONNECT BY prior WDA.DELIVERY_DETAIL_ID = WDA.PARENT_DELIVERY_DETAIL_ID )
470 AND WDD.CONTAINER_FLAG = 'N';
471 BEGIN
472 /*SRW.REFERENCE(DELIVERY_DETAIL_ID)*/NULL;
473 FOR c_1 IN C_DLV_DETAIL(DELIVERY_DETAIL_ID) LOOP
474 FOR c_cat IN C_CATEGORY(c_1.inventory_item_id,c_1.organization_id) LOOP
475 IF (L_CLASS_LIST IS NULL) THEN
476 L_CLASS_LIST := C_CAT.COMM_CLASS;
477 ELSE
478 L_CLASS_LIST := L_CLASS_LIST || ', ' || C_CAT.COMM_CLASS;
479 END IF;
480 END LOOP;
481 END LOOP;
482 IF (LENGTH(L_CLASS_LIST) > 2000) THEN
483 L_CLASS_LIST := SUBSTR(L_CLASS_LIST
484 ,1
485 ,2000);
486 END IF;
487 RETURN (L_CLASS_LIST);
488 EXCEPTION
489 WHEN OTHERS THEN
490 RETURN (NULL);
491 END CF_DETAIL_COMM_CLASSFORMULA;
492
493 FUNCTION CF_SRC_HDR_IDFORMULA RETURN NUMBER IS
494 CURSOR C_MATCH_DLV_DETAIL(C_INV_ITEM_ID IN NUMBER,C_INV_ORG_ID IN NUMBER,C_DELIVERY_ID IN NUMBER) IS
495 SELECT
496 WDD.DELIVERY_DETAIL_ID,
497 WDD.SOURCE_HEADER_ID,
498 WDD.SOURCE_LINE_ID,
499 WDD.TOP_MODEL_LINE_ID
500 FROM
501 WSH_DELIVERY_DETAILS WDD,
502 WSH_DELIVERY_ASSIGNMENTS WDA
503 WHERE WDA.DELIVERY_ID = C_DELIVERY_ID
504 AND WDA.DELIVERY_DETAIL_ID = WDD.DELIVERY_DETAIL_ID
505 AND WDD.INVENTORY_ITEM_ID = C_INV_ITEM_ID
506 AND WDD.ORGANIZATION_ID = C_INV_ORG_ID
507 AND WDD.TOP_MODEL_LINE_ID IS NOT NULL
508 AND ROWNUM = 1;
509 L_SRC_HEADER_ID NUMBER;
510 L_SRC_LINE_ID NUMBER;
511 L_TOP_MODEL_LINE_ID NUMBER;
512 BEGIN
513 RETURN (L_SRC_HEADER_ID);
514 EXCEPTION
515 WHEN OTHERS THEN
516 CP_TOP_MODEL_LINE_ID := NULL;
517 CP_SRC_LINE_ID := NULL;
518 RETURN (NULL);
519 END CF_SRC_HDR_IDFORMULA;
520
521 FUNCTION CP_CLASSIFICATION_P RETURN VARCHAR2 IS
522 BEGIN
523 RETURN CP_CLASSIFICATION;
524 END CP_CLASSIFICATION_P;
525
526 FUNCTION CP_NUM_PACKAGES_P RETURN NUMBER IS
527 BEGIN
528 RETURN CP_NUM_PACKAGES;
529 END CP_NUM_PACKAGES_P;
530
531 FUNCTION CP_TOP_MODEL_LINE_ID_P RETURN NUMBER IS
532 BEGIN
533 RETURN CP_TOP_MODEL_LINE_ID;
534 END CP_TOP_MODEL_LINE_ID_P;
535
536 FUNCTION CP_SRC_LINE_ID_P RETURN NUMBER IS
537 BEGIN
538 RETURN CP_SRC_LINE_ID;
539 END CP_SRC_LINE_ID_P;
540
541 FUNCTION CP_CONTAINER_CLASSIFICATION_P RETURN VARCHAR2 IS
542 BEGIN
543 RETURN CP_CONTAINER_CLASSIFICATION;
544 END CP_CONTAINER_CLASSIFICATION_P;
545
546 FUNCTION CP_HM_P RETURN VARCHAR2 IS
547 BEGIN
548 RETURN CP_HM;
549 END CP_HM_P;
550
551 FUNCTION CP_TOTAL_PREPAID_AMOUNT_P RETURN NUMBER IS
552 BEGIN
553 RETURN CP_TOTAL_PREPAID_AMOUNT;
554 END CP_TOTAL_PREPAID_AMOUNT_P;
555
556 FUNCTION CP_TOTAL_PREPAID_CURRENCY_P RETURN VARCHAR2 IS
557 BEGIN
558 RETURN CP_TOTAL_PREPAID_CURRENCY;
559 END CP_TOTAL_PREPAID_CURRENCY_P;
560
561 FUNCTION CP_TOTAL_COLLECT_CURRENCY_P RETURN VARCHAR2 IS
562 BEGIN
563 RETURN CP_TOTAL_COLLECT_CURRENCY;
564 END CP_TOTAL_COLLECT_CURRENCY_P;
565
566 END WSH_WSHRDBOL_XMLP_PKG;
567
568
[Home] [Help]