1 PACKAGE BODY WSH_WSHRDBOL_XMLP_PKG AS
2 /* $Header: WSHRDBOLB.pls 120.3.12010000.2 2008/09/15 15:49:14 gbhargav 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 L_DESCRIPTION := WSH_UTIL_CORE.GENERIC_FLEX_NAME(UNPACKED_ITEM_INV_ITEM_ID
173 ,UNPACKED_ITEM_ORGANIZATION_ID
174 ,'INV'
175 ,P_ITEM_FLEX_CODE
176 ,101);
177 ELSE
178 L_DESCRIPTION := WSH_UTIL_CORE.GENERIC_FLEX_NAME(UNPACKED_ITEM_INV_ITEM_ID
179 ,UNPACKED_ITEM_ORGANIZATION_ID
180 ,'INV'
181 ,P_ITEM_FLEX_CODE
182 ,101) || ' ' || L_ITEM_DESC;
183 END IF;
184 IF (NVL(UNPACKED_ITEM_QUANTITY_UOM2
185 ,'*') <> '*') THEN
186 L_SECOND_DESC := ' ( ' || NVL(UNPACKED_ITEM_SHIPPED_QTY2
187 ,0) || ' ' || UNPACKED_ITEM_QUANTITY_UOM2 || ' )';
188 ELSE
189 L_SECOND_DESC := NULL;
190 END IF;
191 FND_MESSAGE.SET_NAME('WSH'
192 ,'WSH_WEB_OF_LABEL');
193 MESG := FND_MESSAGE.GET;
194 RETURN (TO_CHAR(UNPACKED_ITEM_SHIPPED_QUANTITY) || ' ' || UNPACKED_ITEM_QUANTITY_UOM || L_SECOND_DESC || ' ' || MESG || ' ' || L_DESCRIPTION);
195 END CF_UNPACKED_ITEM_DESCFORMULA;
196
197 FUNCTION CF_UNPACKED_ITEM_WTFORMULA(UNPACKED_ITEM_NET_WEIGHT IN NUMBER
198 ,UNPACKED_ITEM_WEIGHT_UOM IN VARCHAR2) RETURN CHAR IS
199 BEGIN
200 /*SRW.REFERENCE(UNPACKED_ITEM_NET_WEIGHT)*/NULL;
201 /*SRW.REFERENCE(UNPACKED_ITEM_WEIGHT_UOM)*/NULL;
202 RETURN (TO_CHAR(UNPACKED_ITEM_NET_WEIGHT) || ' ' || UNPACKED_ITEM_WEIGHT_UOM);
203 END CF_UNPACKED_ITEM_WTFORMULA;
204
205 FUNCTION CF_UNPACKED_ITEM_VOLFORMULA(UNPACKED_ITEM_VOLUME IN NUMBER
206 ,UNPACKED_ITEM_VOLUME_UOM IN VARCHAR2) RETURN CHAR IS
207 BEGIN
208 /*SRW.REFERENCE(UNPACKED_ITEM_VOLUME)*/NULL;
209 /*SRW.REFERENCE(UNPACKED_ITEM_VOLUME_UOM)*/NULL;
210 RETURN (TO_CHAR(UNPACKED_ITEM_VOLUME) || ' ' || UNPACKED_ITEM_VOLUME_UOM);
211 END CF_UNPACKED_ITEM_VOLFORMULA;
212
213 FUNCTION CF_FC_COLLECT_CURRENCYFORMULA(FC_COLLECT_CURRENCY IN VARCHAR2) RETURN NUMBER IS
214 BEGIN
215 /*SRW.REFERENCE(FC_COLLECT_CURRENCY)*/NULL;
216 CP_TOTAL_COLLECT_CURRENCY := FC_COLLECT_CURRENCY;
217 RETURN (0);
218 END CF_FC_COLLECT_CURRENCYFORMULA;
219
220 FUNCTION CP_NUM_PACKAGESFORMULA RETURN NUMBER IS
221 BEGIN
222 NULL;
223 END CP_NUM_PACKAGESFORMULA;
224
225 FUNCTION CF_NUM_OF_LPN1FORMULA(DELIVERY_ID3 IN NUMBER) RETURN NUMBER IS
226 CURSOR LPN IS
227 SELECT
228 NUMBER_OF_LPN
229 FROM
230 WSH_NEW_DELIVERIES
231 WHERE DELIVERY_ID = DELIVERY_ID3;
232 L_NUM_LPN NUMBER;
233 BEGIN
234 OPEN LPN;
235 FETCH LPN
236 INTO L_NUM_LPN;
237 CLOSE LPN;
238 RETURN (L_NUM_LPN);
239 END CF_NUM_OF_LPN1FORMULA;
240
241 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
242 BEGIN
243 BEGIN
244 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
245 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
246 EXCEPTION
247 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
248 /*SRW.MESSAGE(1
249 ,'Failed FND SRWINIT.')*/NULL;
250 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
251 END;
252 RETURN (TRUE);
253 END BEFOREREPORT;
254
255 FUNCTION AFTERREPORT RETURN BOOLEAN IS
256 BEGIN
257 BEGIN
258 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
259 EXCEPTION
260 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
261 /*SRW.MESSAGE(1
262 ,'Failed in SRWEXIT')*/NULL;
263 RAISE;
264 END;
265 RETURN (TRUE);
266 END AFTERREPORT;
267
268 FUNCTION LP_WHERE_CLAUSEVALIDTRIGGER RETURN BOOLEAN IS
269 BEGIN
270 RETURN (TRUE);
271 END LP_WHERE_CLAUSEVALIDTRIGGER;
272
273 FUNCTION CF_DOCK_CODEFORMULA(DELIVERY_ID3 IN NUMBER) RETURN CHAR IS
274 L_DOCK_CODE WSH_DELIVERY_DETAILS.CUSTOMER_DOCK_CODE%TYPE;
275 L_PREV_DOCK_CODE WSH_DELIVERY_DETAILS.CUSTOMER_DOCK_CODE%TYPE;
276 L_COUNT NUMBER;
277 CURSOR C_DOCK IS
278 SELECT
279 DISTINCT
280 ( WDD.CUSTOMER_DOCK_CODE )
281 FROM
282 WSH_DELIVERY_DETAILS WDD,
283 WSH_DELIVERY_ASSIGNMENTS_V WDA
284 WHERE WDA.DELIVERY_ID = DELIVERY_ID3
285 AND WDA.DELIVERY_DETAIL_ID = WDD.DELIVERY_DETAIL_ID
286 AND NVL(WDD.CONTAINER_FLAG
287 ,'N') = 'N'
288 AND WDD.CUSTOMER_DOCK_CODE is not null
289 GROUP BY
290 WDD.CUSTOMER_DOCK_CODE;
291 BEGIN
292 L_COUNT := 0;
293 L_PREV_DOCK_CODE := NULL;
294 L_DOCK_CODE := NULL;
295 OPEN C_DOCK;
296 LOOP
297 FETCH C_DOCK
298 INTO L_DOCK_CODE;
299 EXIT WHEN C_DOCK%NOTFOUND;
300 L_COUNT := L_COUNT + 1;
301 IF (L_COUNT > 1) THEN
302 IF (L_DOCK_CODE <> L_PREV_DOCK_CODE) THEN
303 L_DOCK_CODE := NULL;
304 CLOSE C_DOCK;
305 EXIT;
306 END IF;
307 END IF;
308 L_PREV_DOCK_CODE := L_DOCK_CODE;
309 END LOOP;
310 CLOSE C_DOCK;
311 RETURN L_DOCK_CODE;
312 EXCEPTION
313 WHEN NO_DATA_FOUND THEN
314 L_DOCK_CODE := NULL;
315 RETURN (L_DOCK_CODE);
316 WHEN OTHERS THEN
317 L_DOCK_CODE := NULL;
318 RETURN (L_DOCK_CODE);
319 END CF_DOCK_CODEFORMULA;
320
321 FUNCTION CF_CARRIER_NAMEFORMULA(SHIP_METHOD IN VARCHAR2) RETURN CHAR IS
322 L_CARRIER_NAME VARCHAR2(30);
323 L_CARRIER_ID NUMBER;
324 BEGIN
325 SELECT
326 CARRIER_ID
327 INTO L_CARRIER_ID
328 FROM
329 WSH_CARRIER_SERVICES
330 WHERE SHIP_METHOD_CODE = SHIP_METHOD;
331 SELECT
332 PARTY_NAME
333 INTO L_CARRIER_NAME
334 FROM
335 HZ_PARTIES
336 WHERE PARTY_ID = L_CARRIER_ID;
337 RETURN (L_CARRIER_NAME);
338 EXCEPTION
339 WHEN OTHERS THEN
340 RETURN (NULL);
341 END CF_CARRIER_NAMEFORMULA;
342
343 FUNCTION CF_MBOL_NUMBERFORMULA(TRIP_ID1 IN NUMBER) RETURN CHAR IS
344 L_MBOL_NUMBER VARCHAR2(50);
345 BEGIN
346 SELECT
347 SEQUENCE_NUMBER
348 INTO L_MBOL_NUMBER
349 FROM
350 WSH_DOCUMENT_INSTANCES
351 WHERE ENTITY_ID = TRIP_ID1
352 AND ENTITY_NAME = 'WSH_TRIPS';
353 RETURN L_MBOL_NUMBER;
354 EXCEPTION
355 WHEN NO_DATA_FOUND THEN
356 RETURN NULL;
357 END CF_MBOL_NUMBERFORMULA;
358
359 FUNCTION CF_CUSTOMER_NAMEFORMULA(CUSTOMER_NAME IN VARCHAR2) RETURN CHAR IS
360 L_CUSTOMER_NAME HZ_PARTIES.PARTY_NAME%TYPE;
361 L_PERSON_TITLE HZ_PARTIES.PERSON_TITLE%TYPE;
362 L_PERSON_TITLE_UP HZ_PARTIES.PERSON_TITLE%TYPE;
363 L_LOOKUP_TYPE VARCHAR2(20);
364 BEGIN
365 /*SRW.REFERENCE(CUSTOMER_NAME)*/NULL;
366 SELECT
367 PARTY_NAME,
368 NVL(PERSON_PRE_NAME_ADJUNCT
369 ,PERSON_TITLE) TITLE
370 INTO L_CUSTOMER_NAME,L_PERSON_TITLE
371 FROM
372 HZ_PARTIES
373 WHERE PARTY_NAME = CUSTOMER_NAME;
374 IF L_PERSON_TITLE IS NOT NULL THEN
375 BEGIN
376 L_LOOKUP_TYPE := 'RESPONSIBILITY';
377 L_PERSON_TITLE_UP := UPPER(L_PERSON_TITLE);
378 SELECT
379 MEANING || ' ' || L_CUSTOMER_NAME
380 INTO L_CUSTOMER_NAME
381 FROM
382 AR_LOOKUPS
383 WHERE LOOKUP_CODE = L_PERSON_TITLE_UP
384 AND LOOKUP_TYPE = L_LOOKUP_TYPE;
385 EXCEPTION
386 WHEN OTHERS THEN
387 L_CUSTOMER_NAME := L_PERSON_TITLE || ' ' || L_CUSTOMER_NAME;
388 END;
389 END IF;
390 RETURN (L_CUSTOMER_NAME);
391 EXCEPTION
392 WHEN OTHERS THEN
393 RETURN (CUSTOMER_NAME);
394 END CF_CUSTOMER_NAMEFORMULA;
395
396 FUNCTION CF_COMMODITY_CLASSFORMULA(UNPACKED_ITEM_INV_ITEM_ID IN NUMBER
397 ,UNPACKED_ITEM_ORGANIZATION_ID IN NUMBER) RETURN CHAR IS
398 L_CLASS_LIST VARCHAR2(1000);
399 CURSOR C_CATEGORY(C_INV_ITEM_ID IN NUMBER,C_INV_ORG_ID IN NUMBER) IS
400 SELECT
401 CONCATENATED_SEGMENTS COMM_CLASS
402 FROM
403 MTL_CATEGORIES_KFV MC,
404 MTL_ITEM_CATEGORIES MIC,
405 MTL_CATEGORY_SETS_VL MCSTL
406 WHERE MIC.INVENTORY_ITEM_ID = C_INV_ITEM_ID
407 AND MIC.ORGANIZATION_ID = C_INV_ORG_ID
408 AND MIC.CATEGORY_SET_ID = MCSTL.CATEGORY_SET_ID
409 AND MC.CATEGORY_ID = MIC.CATEGORY_ID
410 AND MCSTL.CATEGORY_SET_NAME = 'WSH_COMMODITY_CODE'
411 ORDER BY
412 MC.CATEGORY_ID;
413 BEGIN
414 /*SRW.REFERENCE(UNPACKED_ITEM_INV_ITEM_ID)*/NULL;
415 /*SRW.REFERENCE(UNPACKED_ITEM_ORGANIZATION_ID)*/NULL;
416 FOR c_rec IN C_CATEGORY(UNPACKED_ITEM_INV_ITEM_ID,UNPACKED_ITEM_ORGANIZATION_ID) LOOP
417 IF (L_CLASS_LIST IS NULL) THEN
418 L_CLASS_LIST := C_REC.COMM_CLASS;
419 ELSE
420 L_CLASS_LIST := L_CLASS_LIST || ', ' || C_REC.COMM_CLASS;
421 END IF;
422 END LOOP;
423 RETURN (L_CLASS_LIST);
424 EXCEPTION
425 WHEN OTHERS THEN
426 RETURN (NULL);
427 END CF_COMMODITY_CLASSFORMULA;
428
429 FUNCTION CF_DETAIL_COMM_CLASSFORMULA(DELIVERY_DETAIL_ID IN NUMBER) RETURN CHAR IS
430 L_CLASS_LIST VARCHAR2(4000);
431 CURSOR C_CATEGORY(C_INV_ITEM_ID IN NUMBER,C_INV_ORG_ID IN NUMBER) IS
432 SELECT
433 MC.SEGMENT1 || '-' || MC.SEGMENT2 || '-' || MC.SEGMENT3 COMM_CLASS
434 FROM
435 MTL_CATEGORIES MC,
436 MTL_ITEM_CATEGORIES MIC,
437 MTL_CATEGORY_SETS_VL MCSTL
438 WHERE MIC.INVENTORY_ITEM_ID = C_INV_ITEM_ID
439 AND MIC.ORGANIZATION_ID = C_INV_ORG_ID
440 AND MIC.CATEGORY_SET_ID = MCSTL.CATEGORY_SET_ID
441 AND MC.CATEGORY_ID = MIC.CATEGORY_ID
442 AND MCSTL.CATEGORY_SET_NAME = 'WSH_COMMODITY_CODE'
443 ORDER BY
444 MC.CATEGORY_ID;
445 CURSOR C_DLV_DETAIL(C_DELIVERY_DETAIL_ID IN NUMBER) IS
446 SELECT
447 DISTINCT
448 INVENTORY_ITEM_ID,
449 ORGANIZATION_ID
450 FROM
451 WSH_DELIVERY_DETAILS WDD
452 WHERE WDD.DELIVERY_DETAIL_ID IN (
453 SELECT
454 WDA.DELIVERY_DETAIL_ID
455 FROM
456 WSH_DELIVERY_ASSIGNMENTS WDA
457 START WITH WDA.DELIVERY_DETAIL_ID = C_DELIVERY_DETAIL_ID
458 CONNECT BY prior WDA.DELIVERY_DETAIL_ID = WDA.PARENT_DELIVERY_DETAIL_ID )
459 AND WDD.CONTAINER_FLAG = 'N';
460 BEGIN
461 /*SRW.REFERENCE(DELIVERY_DETAIL_ID)*/NULL;
462 FOR c_1 IN C_DLV_DETAIL(DELIVERY_DETAIL_ID) LOOP
463 FOR c_cat IN C_CATEGORY(c_1.inventory_item_id,c_1.organization_id) LOOP
464 IF (L_CLASS_LIST IS NULL) THEN
465 L_CLASS_LIST := C_CAT.COMM_CLASS;
466 ELSE
467 L_CLASS_LIST := L_CLASS_LIST || ', ' || C_CAT.COMM_CLASS;
468 END IF;
469 END LOOP;
470 END LOOP;
471 IF (LENGTH(L_CLASS_LIST) > 2000) THEN
472 L_CLASS_LIST := SUBSTR(L_CLASS_LIST
473 ,1
474 ,2000);
475 END IF;
476 RETURN (L_CLASS_LIST);
477 EXCEPTION
478 WHEN OTHERS THEN
479 RETURN (NULL);
480 END CF_DETAIL_COMM_CLASSFORMULA;
481
482 FUNCTION CF_SRC_HDR_IDFORMULA RETURN NUMBER IS
483 CURSOR C_MATCH_DLV_DETAIL(C_INV_ITEM_ID IN NUMBER,C_INV_ORG_ID IN NUMBER,C_DELIVERY_ID IN NUMBER) IS
484 SELECT
485 WDD.DELIVERY_DETAIL_ID,
486 WDD.SOURCE_HEADER_ID,
487 WDD.SOURCE_LINE_ID,
488 WDD.TOP_MODEL_LINE_ID
489 FROM
490 WSH_DELIVERY_DETAILS WDD,
491 WSH_DELIVERY_ASSIGNMENTS WDA
492 WHERE WDA.DELIVERY_ID = C_DELIVERY_ID
493 AND WDA.DELIVERY_DETAIL_ID = WDD.DELIVERY_DETAIL_ID
494 AND WDD.INVENTORY_ITEM_ID = C_INV_ITEM_ID
495 AND WDD.ORGANIZATION_ID = C_INV_ORG_ID
496 AND WDD.TOP_MODEL_LINE_ID IS NOT NULL
497 AND ROWNUM = 1;
498 L_SRC_HEADER_ID NUMBER;
499 L_SRC_LINE_ID NUMBER;
500 L_TOP_MODEL_LINE_ID NUMBER;
501 BEGIN
502 RETURN (L_SRC_HEADER_ID);
503 EXCEPTION
504 WHEN OTHERS THEN
505 CP_TOP_MODEL_LINE_ID := NULL;
506 CP_SRC_LINE_ID := NULL;
507 RETURN (NULL);
508 END CF_SRC_HDR_IDFORMULA;
509
510 FUNCTION CP_CLASSIFICATION_P RETURN VARCHAR2 IS
511 BEGIN
512 RETURN CP_CLASSIFICATION;
513 END CP_CLASSIFICATION_P;
514
515 FUNCTION CP_NUM_PACKAGES_P RETURN NUMBER IS
516 BEGIN
517 RETURN CP_NUM_PACKAGES;
518 END CP_NUM_PACKAGES_P;
519
520 FUNCTION CP_TOP_MODEL_LINE_ID_P RETURN NUMBER IS
521 BEGIN
522 RETURN CP_TOP_MODEL_LINE_ID;
523 END CP_TOP_MODEL_LINE_ID_P;
524
525 FUNCTION CP_SRC_LINE_ID_P RETURN NUMBER IS
526 BEGIN
527 RETURN CP_SRC_LINE_ID;
528 END CP_SRC_LINE_ID_P;
529
530 FUNCTION CP_CONTAINER_CLASSIFICATION_P RETURN VARCHAR2 IS
531 BEGIN
532 RETURN CP_CONTAINER_CLASSIFICATION;
533 END CP_CONTAINER_CLASSIFICATION_P;
534
535 FUNCTION CP_HM_P RETURN VARCHAR2 IS
536 BEGIN
537 RETURN CP_HM;
538 END CP_HM_P;
539
540 FUNCTION CP_TOTAL_PREPAID_AMOUNT_P RETURN NUMBER IS
541 BEGIN
542 RETURN CP_TOTAL_PREPAID_AMOUNT;
543 END CP_TOTAL_PREPAID_AMOUNT_P;
544
545 FUNCTION CP_TOTAL_PREPAID_CURRENCY_P RETURN VARCHAR2 IS
546 BEGIN
547 RETURN CP_TOTAL_PREPAID_CURRENCY;
548 END CP_TOTAL_PREPAID_CURRENCY_P;
549
550 FUNCTION CP_TOTAL_COLLECT_CURRENCY_P RETURN VARCHAR2 IS
551 BEGIN
552 RETURN CP_TOTAL_COLLECT_CURRENCY;
553 END CP_TOTAL_COLLECT_CURRENCY_P;
554
555 END WSH_WSHRDBOL_XMLP_PKG;
556
557
[Home] [Help]