1 PACKAGE BODY WSH_WSHRDPIK_XMLP_PKG AS
2 /* $Header: WSHRDPIKB.pls 120.4.12020000.2 2012/07/04 10:49:20 suppal ship $ */
3 FUNCTION AFTERPFORM RETURN BOOLEAN IS
4 CURSOR MAP_YES_NO(T IN VARCHAR2) IS
5 SELECT
6 MEANING
7 FROM
8 OE_LOOKUPS
9 WHERE LOOKUP_CODE = T
10 AND LOOKUP_TYPE = 'YES_NO'
11 AND TRUNC(SYSDATE) BETWEEN NVL(START_DATE_ACTIVE
12 ,TRUNC(SYSDATE))
13 AND NVL(END_DATE_ACTIVE
14 ,TRUNC(SYSDATE));
15 CURSOR STRUCT_NUM(FLEX_CODE IN VARCHAR2) IS
16 SELECT
17 ID_FLEX_NUM
18 FROM
19 FND_ID_FLEX_STRUCTURES
20 WHERE ID_FLEX_CODE = FLEX_CODE;
21 CLAUSE VARCHAR2(300);
22 VALUE VARCHAR2(300);
23 STRUCT_NUMBER NUMBER;
24 BEGIN
25 BEGIN
26 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
27 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
28 EXCEPTION
29 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
30 /*SRW.MESSAGE(1000
31 ,'Failed in After Form trigger')*/NULL;
32 RETURN (FALSE);
33 END;
34 IF (P_MOVE_ORDER_LOW IS NOT NULL AND P_MOVE_ORDER_HIGH IS NOT NULL) THEN
35 IF (P_MOVE_ORDER_LOW = P_MOVE_ORDER_HIGH) THEN
36 LP_MO_CLAUSE := 'and mtrh.request_number = :p_move_order_high';
37 ELSE
38 LP_MO_CLAUSE := 'and mtrh.request_number between :p_move_order_low and :p_move_order_high';
39 END IF;
40 ELSIF (P_MOVE_ORDER_LOW IS NULL AND P_MOVE_ORDER_HIGH IS NOT NULL) THEN
41 LP_MO_CLAUSE := 'and mtrh.request_number <= :p_move_order_high';
42 ELSIF (P_MOVE_ORDER_LOW IS NOT NULL AND P_MOVE_ORDER_HIGH IS NULL) THEN
43 LP_MO_CLAUSE := 'and mtrh.request_number >= :p_move_order_low';
44 END IF;
45 IF P_ITEM_DISPLAY = 'D' THEN
46 LP_ITEM_DISPLAY_VALUE := 'decode(wdd.inventory_item_id,NULL,wdd.item_description, msitl.description)';
47 ELSE
48 LP_ITEM_DISPLAY_VALUE := 'to_char(wdd.inventory_item_id)';
49 END IF;
50 IF (P_PICK_SLIP_NUM_LOW IS NOT NULL AND P_PICK_SLIP_NUM_HIGH IS NOT NULL) THEN
51 IF (P_PICK_SLIP_NUM_LOW = P_PICK_SLIP_NUM_HIGH) THEN
52 LP_PICK_SLIP_NUM := 'and wpsv.pick_slip_number = :p_pick_slip_num_high';
53 LP_PICK_SLIP_NUM_MMT := 'and mmt.pick_slip_number = :p_pick_slip_num_high';
54 LP_PICK_SLIP_NUM_MTRL := 'and mtrl.pick_slip_number = :p_pick_slip_num_high';
55 ELSE
56 LP_PICK_SLIP_NUM := 'and (wpsv.pick_slip_number between :p_pick_slip_num_low and :p_pick_slip_num_high)';
57 LP_PICK_SLIP_NUM_MMT := 'and (mmt.pick_slip_number between :p_pick_slip_num_low and :p_pick_slip_num_high)';
58 LP_PICK_SLIP_NUM_MTRL := 'and (mtrl.pick_slip_number between :p_pick_slip_num_low and :p_pick_slip_num_high)';
59 END IF;
60 ELSIF (P_PICK_SLIP_NUM_LOW IS NULL AND P_PICK_SLIP_NUM_HIGH IS NOT NULL) THEN
61 LP_PICK_SLIP_NUM := 'and wpsv.pick_slip_number <= :p_pick_slip_num_high';
62 LP_PICK_SLIP_NUM_MMT := 'and mmt.pick_slip_number <= :p_pick_slip_num_high';
63 LP_PICK_SLIP_NUM_MTRL := 'and mtrl.pick_slip_number <= :p_pick_slip_num_high';
64 ELSIF (P_PICK_SLIP_NUM_LOW IS NOT NULL AND P_PICK_SLIP_NUM_HIGH IS NULL) THEN
65 LP_PICK_SLIP_NUM := 'and wpsv.pick_slip_number >= :p_pick_slip_num_low';
66 LP_PICK_SLIP_NUM_MMT := 'and mmt.pick_slip_number >= :p_pick_slip_num_low';
67 LP_PICK_SLIP_NUM_MTRL := 'and mtrl.pick_slip_number >= :p_pick_slip_num_low';
68 END IF;
69 IF (P_ORDER_NUM_HIGH IS NOT NULL AND P_ORDER_NUM_LOW IS NOT NULL) THEN
70 IF (P_ORDER_NUM_HIGH = P_ORDER_NUM_LOW) THEN
71 LP_ORDER_NUM := ' and wdd.source_header_number = :p_order_num_low';
72 ELSE --Bug9508781
73 LP_ORDER_NUM := ' and wdd.source_header_number >= :p_order_num_low and wdd.source_header_number <= :p_order_num_high';
74 END IF;
75 ELSIF (P_ORDER_NUM_LOW IS NOT NULL) THEN
76 LP_ORDER_NUM := ' and wdd.source_header_number >= :p_order_num_low';
77 ELSIF (P_ORDER_NUM_HIGH IS NOT NULL) THEN
78 LP_ORDER_NUM := ' and wdd.source_header_number <= :p_order_num_high ';
79 END IF;
80 IF P_ORDER_TYPE_ID IS NOT NULL THEN
81 LP_ORDER_TYPE := ' AND wdd.source_header_type_id = :p_order_type_id ';
82 END IF;
83 IF LP_DETAIL_DATE_HIGH IS NOT NULL THEN
84 LP_DETAIL_DATE_HIGH := LP_DETAIL_DATE_HIGH + (86399 / 86400);
85 END IF;
86 IF (P_DETAIL_DATE_LOW IS NOT NULL AND LP_DETAIL_DATE_HIGH IS NOT NULL) THEN
87 LP_DETAIL_DATE_MMT := 'and (mmt.transaction_date between :p_detail_date_low and :lp_detail_date_high)';
88 LP_DETAIL_DATE_MTRL := 'and (mtrl.pick_slip_date between :p_detail_date_low and :lp_detail_date_high)';
89 LP_DETAIL_DATE_UNPICK := 'and (wpsv.creation_date between :p_detail_date_low and :lp_detail_date_high)';
90 ELSIF (P_DETAIL_DATE_LOW IS NULL AND LP_DETAIL_DATE_HIGH IS NOT NULL) THEN
91 LP_DETAIL_DATE_MMT := 'and mmt.transaction_date <= :lp_detail_date_high';
92 LP_DETAIL_DATE_MTRL := 'and mtrl.pick_slip_date <= :lp_detail_date_high';
93 LP_DETAIL_DATE_UNPICK := 'and wpsv.creation_date <= :lp_detail_date_high';
94 ELSIF (P_DETAIL_DATE_LOW IS NOT NULL AND P_DETAIL_DATE_HIGH IS NULL) THEN
95 LP_DETAIL_DATE_MMT := 'and mmt.transaction_date >= :p_detail_date_low';
96 LP_DETAIL_DATE_MTRL := 'and mtrl.pick_slip_date >= :p_detail_date_low';
97 LP_DETAIL_DATE_UNPICK := 'and wpsv.creation_date >= :p_detail_date_low';
98 END IF;
99 IF (P_CUSTOMER_ID IS NOT NULL) THEN
100 LP_CUSTOMER_ID := 'and wdd.customer_id = :p_customer_id';
101 END IF;
102 IF (P_FREIGHT_CODE IS NOT NULL) THEN
103 LP_SHIP_METHOD_CODE := 'and wdd.ship_method_code = :p_freight_code';
104 END IF;
105 LP_PICK_STATUS := ' ';
106 LP_PICK_STATUS_UNPICK := ' ';
107 IF P_PICK_STATUS <> 'A' THEN
108 IF P_PICK_STATUS = 'U' THEN
109 P_PICK_STATUS_VALUE := 'UNPICKED';
110 LP_PICK_STATUS := 'and 1 <> 1';
111 ELSIF P_PICK_STATUS = 'P' THEN
112 P_PICK_STATUS_VALUE := 'PICKED';
113 LP_PICK_STATUS_UNPICK := 'and 1 <> 1';
114 END IF;
115 END IF;
116 IF P_ORGANIZATION_ID IS NOT NULL THEN
117 LP_WAREHOUSE_CLAUSE := 'AND mtrl.organization_id = :p_organization_id';
118 END IF;
119 IF P_PRINTER_NAME = '-1' THEN
120 LP_PRINTER_NAME := 'and wpsv.subinventory_code not in
121 ( Select subinventory from wsh_report_printers wrp
122 where wrp.level_type_id = :P_LEVEL_TYPE_ID1
123 and wrp.enabled_flag = :P_ENABLED_FLAG
124 and wrp.CONCURRENT_PROGRAM_ID = (
125 select concurrent_program_id from
126 fnd_concurrent_programs_vl
127 where concurrent_program_name = :P_CONCURRENT_PROGRAM_NAME
128 and application_id = :P_APPLICATION_ID
129 and rownum = 1 )
130 and wrp.organization_id = wdd.organization_id )
131 and wdd.organization_id not in
132 ( Select wrp.level_value_id from wsh_report_printers wrp
133 where wrp.level_type_id = :P_LEVEL_TYPE_ID2
134 and wrp.enabled_flag = :P_ENABLED_FLAG
135 and wrp.CONCURRENT_PROGRAM_ID = (
136 select concurrent_program_id from
137 fnd_concurrent_programs_vl
138 where concurrent_program_name = :P_CONCURRENT_PROGRAM_NAME
139 and application_id = :P_APPLICATION_ID
140 and rownum = 1 ) ) ';
141 ELSIF P_PRINTER_NAME IS NOT NULL THEN
142 LP_PRINTER_NAME := 'and ( wpsv.subinventory_code in
143 ( Select wrp.subinventory from wsh_report_printers wrp
144 where wrp.level_type_id = :P_LEVEL_TYPE_ID1
145 and wrp.enabled_flag = :P_ENABLED_FLAG
146 and wrp.CONCURRENT_PROGRAM_ID = (
147 select concurrent_program_id from
148 fnd_concurrent_programs_vl
149 where concurrent_program_name = :P_CONCURRENT_PROGRAM_NAME
150 and application_id = :P_APPLICATION_ID
151 and rownum = 1 )
152 and wrp.organization_id = wdd.organization_id
153 and wrp.printer_name = :p_printer_name )
154 or (wdd.organization_id in
155 ( Select wrp.level_value_id from wsh_report_printers wrp
156 where wrp.level_type_id = :P_LEVEL_TYPE_ID2
157 and wrp.enabled_flag = :P_ENABLED_FLAG
158 and wrp.printer_name = :p_printer_name
159 and wrp.CONCURRENT_PROGRAM_ID = (
160 select concurrent_program_id from
161 fnd_concurrent_programs_vl
162 where concurrent_program_name = :P_CONCURRENT_PROGRAM_NAME
163 and application_id = :P_APPLICATION_ID
164 and rownum = 1 ) )
165 and wpsv.subinventory_code not in
166 ( select wrp.subinventory
167 from wsh_Report_printers wrp
168 where wrp.level_type_id = :P_LEVEL_TYPE_ID1
169 and wrp.enabled_flag = :P_ENABLED_FLAG
170 and wrp.CONCURRENT_PROGRAM_ID =(
171 select concurrent_program_id from
172 fnd_concurrent_programs_vl
173 where concurrent_program_name = :P_CONCURRENT_PROGRAM_NAME
174 and application_id = :P_APPLICATION_ID
175 and rownum = 1 )
176 and wrp.organization_id = wdd.organization_id )))' ; --bug 9278128
177 END IF;
178 OPEN MAP_YES_NO('Y');
179 FETCH MAP_YES_NO
180 INTO LP_YES;
181 CLOSE MAP_YES_NO;
182 OPEN MAP_YES_NO('N');
183 FETCH MAP_YES_NO
184 INTO LP_NO;
185 CLOSE MAP_YES_NO;
186 OPEN STRUCT_NUM(P_ITEM_FLEX_CODE);
187 FETCH STRUCT_NUM
188 INTO STRUCT_NUMBER;
189 CLOSE STRUCT_NUM;
190 LP_STRUCTURE_NUM := STRUCT_NUMBER;
191 RETURN TRUE;
192 EXCEPTION
193 WHEN OTHERS THEN
194 IF MAP_YES_NO%ISOPEN THEN
195 CLOSE MAP_YES_NO;
196 END IF;
197 RETURN FALSE;
198 END AFTERPFORM;
199
200 FUNCTION AFTERREPORT RETURN BOOLEAN IS
201 BEGIN
202 BEGIN
203 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
204 EXCEPTION
205 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
206 /*SRW.MESSAGE(1
207 ,'Failed in SRWEXIT')*/NULL;
208 RAISE;
209 END;
210 RETURN (TRUE);
211 END AFTERREPORT;
212
213 FUNCTION BEFOREPFORM RETURN BOOLEAN IS
214 BEGIN
215 RETURN (TRUE);
216 END BEFOREPFORM;
217
218 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
219 BEGIN
220 BEGIN
221 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
222 LP_DETAIL_DATE_HIGH := P_DETAIL_DATE_HIGH;
223 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
224 EXCEPTION
225 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
226 /*SRW.MESSAGE(1
227 ,'Failed FND SRWINIT.')*/NULL;
228 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
229 END;
230 RETURN (TRUE);
231 END BEFOREREPORT;
232
233 FUNCTION F_LOT_QUANTITYFORMULA(DELIVERY_DETAIL_ID3 IN NUMBER
234 ,LOT_TXN_ID IN NUMBER
235 ,LOT_NUMBER1 IN VARCHAR2) RETURN NUMBER IS
236 LOT_QTY NUMBER := NULL;
237 BEGIN
238 IF DELIVERY_DETAIL_ID3 = -99 THEN
239 BEGIN
240 SELECT
241 SUM(ABS(TRANSACTION_QUANTITY))
242 INTO LOT_QTY
243 FROM
244 MTL_TRANSACTION_LOT_NUMBERS
245 WHERE TRANSACTION_ID = LOT_TXN_ID
246 AND LOT_NUMBER = LOT_NUMBER1
247 GROUP BY
248 TRANSACTION_ID,
249 LOT_NUMBER;
250 RETURN (LOT_QTY);
251 EXCEPTION
252 WHEN NO_DATA_FOUND THEN
253 BEGIN
254 SELECT
255 SUM(ABS(TRANSACTION_QUANTITY))
256 INTO LOT_QTY
257 FROM
258 MTL_TRANSACTION_LOTS_TEMP
259 WHERE TRANSACTION_TEMP_ID = LOT_TXN_ID
260 AND LOT_NUMBER = LOT_NUMBER1
261 GROUP BY
262 TRANSACTION_TEMP_ID,
263 LOT_NUMBER;
264 RETURN (LOT_QTY);
265 EXCEPTION
266 WHEN NO_DATA_FOUND THEN
267 BEGIN
268 SELECT
269 SUM(ABS(TRANSACTION_QUANTITY))
270 INTO LOT_QTY
271 FROM
272 MTL_MATERIAL_TRANSACTIONS_TEMP
273 WHERE TRANSACTION_TEMP_ID = LOT_TXN_ID
274 AND ( REVISION IS NOT NULL
275 OR LOCATOR_ID IS NOT NULL )
276 GROUP BY
277 TRANSACTION_TEMP_ID;
278 RETURN (LOT_QTY);
279 EXCEPTION
280 WHEN NO_DATA_FOUND THEN
281 RETURN (NULL);
282 END;
283 END;
284 END;
285 ELSE
286 SELECT
287 NVL(PICKED_QUANTITY,REQUESTED_QUANTITY)
288 INTO LOT_QTY
289 FROM
290 WSH_DELIVERY_DETAILS
291 WHERE DELIVERY_DETAIL_ID = DELIVERY_DETAIL_ID3;
292 END IF;
293 RETURN LOT_QTY;
294 END F_LOT_QUANTITYFORMULA;
295
296 FUNCTION G_TRANSACTION_TEMP_IDGROUPFILT(ORGANIZATION_ID IN NUMBER) RETURN BOOLEAN IS
297 BEGIN
298 IF WSH_UTIL_VALIDATE.CHECK_WMS_ORG(ORGANIZATION_ID) = 'Y' THEN
299 RETURN (TRUE);
300 ELSE
301 RETURN (FALSE);
302 END IF;
303 END G_TRANSACTION_TEMP_IDGROUPFILT;
304
305 FUNCTION F_FROM_LOCATIONFORMULA(FROM_LOCATOR_ID IN NUMBER
306 ,ORGANIZATION_ID IN NUMBER) RETURN CHAR IS
307 NAME VARCHAR(2000) := NULL;
308 RESULT BOOLEAN := TRUE;
309 BEGIN
310 IF FROM_LOCATOR_ID IS NULL THEN
311 RETURN '';
312 END IF;
313 RESULT := FND_FLEX_KEYVAL.VALIDATE_CCID(APPL_SHORT_NAME => 'INV'
314 ,KEY_FLEX_CODE => P_LOCATOR_FLEX_CODE
315 ,STRUCTURE_NUMBER => LP_STRUCTURE_NUM
316 ,COMBINATION_ID => FROM_LOCATOR_ID
317 ,DATA_SET => ORGANIZATION_ID);
318 IF RESULT THEN
319 NAME := FND_FLEX_KEYVAL.CONCATENATED_VALUES;
320 END IF;
321 RETURN NAME;
322 END F_FROM_LOCATIONFORMULA;
323
324 FUNCTION F_ITEM_DESCRIPTIONFORMULA(ITEM_INFO IN VARCHAR2
325 ,INVENTORY_ITEM_ID IN NUMBER
326 ,ORGANIZATION_ID IN NUMBER
327 ,ITEM_DESCRIPTION IN VARCHAR2) RETURN CHAR IS
328 NAME VARCHAR(2000) := NULL;
329 BEGIN
330 IF P_ITEM_DISPLAY = 'D' THEN
331 RETURN ITEM_INFO;
332 END IF;
333 IF P_ITEM_DISPLAY = 'F' THEN
334 NAME := WSH_UTIL_CORE.GET_ITEM_NAME(INVENTORY_ITEM_ID
335 ,ORGANIZATION_ID
336 ,P_ITEM_FLEX_CODE
337 ,LP_STRUCTURE_NUM);
338 RETURN NAME;
339 ELSE
340 NAME := WSH_UTIL_CORE.GET_ITEM_NAME(INVENTORY_ITEM_ID
341 ,ORGANIZATION_ID
342 ,P_ITEM_FLEX_CODE
343 ,LP_STRUCTURE_NUM);
344 RETURN NAME || ' ' || ITEM_DESCRIPTION;
345 END IF;
346 END F_ITEM_DESCRIPTIONFORMULA;
347
348 FUNCTION F_REQUESTED_QUANTITYFORMULA(SOURCE_HEADER_ID1 IN NUMBER
349 ,SOURCE_LINE_ID1 IN NUMBER
350 ,MOVE_ORDER_LINE_ID1 IN NUMBER) RETURN NUMBER IS
351 REQ_QTY NUMBER;
352 BEGIN
353 SELECT
354 SUM(REQUESTED_QUANTITY)
355 INTO REQ_QTY
356 FROM
357 WSH_DELIVERY_DETAILS
358 WHERE SOURCE_HEADER_ID = SOURCE_HEADER_ID1
359 AND SOURCE_LINE_ID = SOURCE_LINE_ID1
360 AND MOVE_ORDER_LINE_ID = MOVE_ORDER_LINE_ID1
361 AND NVL(LINE_DIRECTION
362 ,'O') IN ( 'O' , 'IO' )
363 AND CONTAINER_FLAG in ( 'Y' , 'N' );
364 RETURN (REQ_QTY);
365 END F_REQUESTED_QUANTITYFORMULA;
366
367 FUNCTION F_SHIPPED_QUANTITYFORMULA RETURN NUMBER IS
368 SHP_QTY NUMBER := NULL;
369 BEGIN
370 RETURN (SHP_QTY);
371 END F_SHIPPED_QUANTITYFORMULA;
372
373 FUNCTION F_TO_LOCATIONFORMULA(TO_LOCATOR_ID IN NUMBER
374 ,ORGANIZATION_ID IN NUMBER) RETURN CHAR IS
375 NAME VARCHAR(2000) := NULL;
376 RESULT BOOLEAN := TRUE;
377 BEGIN
378 IF TO_LOCATOR_ID IS NULL THEN
379 RETURN '';
380 END IF;
381 RESULT := FND_FLEX_KEYVAL.VALIDATE_CCID(APPL_SHORT_NAME => 'INV'
382 ,KEY_FLEX_CODE => P_LOCATOR_FLEX_CODE
383 ,STRUCTURE_NUMBER => LP_STRUCTURE_NUM
384 ,COMBINATION_ID => TO_LOCATOR_ID
385 ,DATA_SET => ORGANIZATION_ID);
386 IF RESULT THEN
387 NAME := FND_FLEX_KEYVAL.CONCATENATED_VALUES;
388 END IF;
389 RETURN NAME;
390 END F_TO_LOCATIONFORMULA;
391
392 FUNCTION CF_REVISIONFORMULA(TRANSACTION_ID1 IN NUMBER) RETURN CHAR IS
393 REVISION MTL_ITEM_REVISIONS.REVISION%TYPE;
394 BEGIN
395 REVISION := ' ';
396 BEGIN
397 SELECT
398 REVISION
399 INTO REVISION
400 FROM
401 MTL_MATERIAL_TRANSACTIONS_TEMP
402 WHERE TRANSACTION_TEMP_ID = TRANSACTION_ID1;
403 EXCEPTION
404 WHEN NO_DATA_FOUND THEN
405 BEGIN
406 SELECT
407 REVISION
408 INTO REVISION
409 FROM
410 MTL_MATERIAL_TRANSACTIONS
411 WHERE TRANSACTION_ID = TRANSACTION_ID1;
412 EXCEPTION
413 WHEN NO_DATA_FOUND THEN
414 REVISION := ' ';
415 END;
416 END;
417 RETURN (REVISION);
418 END CF_REVISIONFORMULA;
419
420 FUNCTION CF_TRIP_IDFORMULA(DELIVERY_ID1 IN NUMBER
421 ,INITIAL_PICKUP_LOCATION_ID IN NUMBER) RETURN NUMBER IS
422 CURSOR C_GET_TRIP_NAME IS
423 SELECT
424 WT.TRIP_ID,
425 WT.NAME
426 FROM
427 WSH_TRIPS WT,
428 WSH_TRIP_STOPS WTS,
429 WSH_DELIVERY_LEGS WDL
430 WHERE WDL.DELIVERY_ID = DELIVERY_ID1
431 AND WTS.STOP_LOCATION_ID = INITIAL_PICKUP_LOCATION_ID
432 AND WTS.STOP_ID = WDL.PICK_UP_STOP_ID
433 AND WTS.TRIP_ID = WT.TRIP_ID;
434 BEGIN
435 IF DELIVERY_ID1 IS NULL THEN
436 CP_TRIP_NAME := NULL;
437 RETURN NULL;
438 ELSE
439 IF DELIVERY_ID1 = CP_CACHE_DELIVERY_ID THEN
440 CP_TRIP_NAME := CP_CACHE_TRIP_NAME;
441 RETURN CP_CACHE_TRIP_ID;
442 ELSE
443 OPEN C_GET_TRIP_NAME;
444 FETCH C_GET_TRIP_NAME
445 INTO CP_CACHE_TRIP_ID,CP_CACHE_TRIP_NAME;
446 IF C_GET_TRIP_NAME%NOTFOUND THEN
447 CLOSE C_GET_TRIP_NAME;
448 RAISE NO_DATA_FOUND;
449 END IF;
450 CLOSE C_GET_TRIP_NAME;
451 CP_CACHE_DELIVERY_ID := DELIVERY_ID1;
452 CP_TRIP_NAME := CP_CACHE_TRIP_NAME;
453 RETURN CP_CACHE_TRIP_ID;
454 END IF;
455 END IF;
456 EXCEPTION
457 WHEN NO_DATA_FOUND THEN
458 RETURN NULL;
459 WHEN OTHERS THEN
460 IF C_GET_TRIP_NAME%ISOPEN THEN
461 CLOSE C_GET_TRIP_NAME;
462 END IF;
463 RETURN NULL;
464 END CF_TRIP_IDFORMULA;
465
466 FUNCTION CF_CUSTOMER_NAMEFORMULA(CUSTOMER_FLAG IN VARCHAR2
467 ,SOURCE_HEADER_ID IN NUMBER) RETURN CHAR IS
468 CURSOR CUSTOMER_NAME(O_ID IN NUMBER) IS
469 SELECT
470 SUBSTRB(PARTY.PARTY_NAME
471 ,1
472 ,50) CUSTOMER_NAME
473 FROM
474 HZ_PARTIES PARTY,
475 HZ_CUST_ACCOUNTS CUST_ACCT,
476 OE_ORDER_HEADERS_ALL OH
477 WHERE CUST_ACCT.PARTY_ID = PARTY.PARTY_ID
478 AND OH.HEADER_ID = O_ID
479 AND CUST_ACCT.CUST_ACCOUNT_ID = OH.SOLD_TO_ORG_ID;
480 NAME HZ_PARTIES.PARTY_NAME%TYPE := ' ';
481 BEGIN
482 IF CUSTOMER_FLAG = 'Y' THEN
483 OPEN CUSTOMER_NAME(SOURCE_HEADER_ID);
484 FETCH CUSTOMER_NAME
485 INTO NAME;
486 CLOSE CUSTOMER_NAME;
487 END IF;
488 RETURN NAME;
489 EXCEPTION
490 WHEN OTHERS THEN
491 RETURN ' ';
492 END CF_CUSTOMER_NAMEFORMULA;
493
494 FUNCTION CF_CUSTOMERFORMULA(CUSTOMER_FLAG IN VARCHAR2
495 ,SOURCE_HEADER_ID IN NUMBER) RETURN CHAR IS
496 CURSOR CUSTOMER_NAME(O_ID IN NUMBER) IS
497 SELECT
498 SUBSTRB(PARTY.PARTY_NAME
499 ,1
500 ,50) CUSTOMER_NAME
501 FROM
502 HZ_PARTIES PARTY,
503 HZ_CUST_ACCOUNTS CUST_ACCT,
504 OE_ORDER_HEADERS_ALL OH
505 WHERE CUST_ACCT.PARTY_ID = PARTY.PARTY_ID
506 AND OH.HEADER_ID = O_ID
507 AND CUST_ACCT.CUST_ACCOUNT_ID = OH.SOLD_TO_ORG_ID;
508 NAME HZ_PARTIES.PARTY_NAME%TYPE := ' ';
509 BEGIN
510 IF CUSTOMER_FLAG = 'Y' THEN
511 OPEN CUSTOMER_NAME(SOURCE_HEADER_ID);
512 FETCH CUSTOMER_NAME
513 INTO NAME;
514 CLOSE CUSTOMER_NAME;
515 END IF;
516 RETURN NAME;
517 EXCEPTION
518 WHEN OTHERS THEN
519 RETURN ' ';
520 END CF_CUSTOMERFORMULA;
521
522 FUNCTION CF_ORDER_NUMBERFORMULA(ORDER_NUMBER_FLAG IN VARCHAR2
523 ,SOURCE_HEADER_NUMBER IN VARCHAR2) RETURN NUMBER IS
524 BEGIN
525 IF ORDER_NUMBER_FLAG = 'Y' THEN
526 RETURN SOURCE_HEADER_NUMBER;
527 ELSE
528 RETURN NULL;
529 END IF;
530 END CF_ORDER_NUMBERFORMULA;
531
532 FUNCTION CF_TRIP_STOP_ADDRESS1FORMULA(TRIP_STOP_FLAG IN VARCHAR2
533 ,DELIVERY_FLAG IN VARCHAR2
534 ,CF_DELIVERY_ID IN NUMBER
535 ,PICK_SLIP_NUMBER IN NUMBER) RETURN CHAR IS
536 CURSOR TRIP_STOP_FROM_DLVY(P_DELIVERY_ID IN NUMBER) IS
537 SELECT
538 LOC.ADDRESS1,
539 LOC.ADDRESS2,
540 LOC.ADDRESS3,
541 LOC.ADDRESS4,
542 LOC.CITY || DECODE(LOC.CITY
543 ,NULL
544 ,''
545 ,', ') || NVL(LOC.STATE
546 ,LOC.PROVINCE) || DECODE(LOC.POSTAL_CODE
547 ,NULL
548 ,''
549 ,' ' || LOC.POSTAL_CODE) || DECODE(NVL(LOC.STATE
550 ,NVL(LOC.PROVINCE
551 ,LOC.POSTAL_CODE))
552 ,NULL
553 ,''
554 ,', ') || LOC.COUNTRY
555 FROM
556 WSH_TRIP_STOPS WTS,
557 WSH_DELIVERY_LEGS WDL,
558 WSH_LOCATIONS LOC
559 WHERE WDL.DELIVERY_ID = P_DELIVERY_ID
560 AND WDL.PICK_UP_STOP_ID = WTS.STOP_ID
561 AND WTS.STOP_LOCATION_ID = LOC.WSH_LOCATION_ID;
562 CURSOR TRIP_STOP_FROM_PS(X_PS_NUMBER IN NUMBER) IS
563 SELECT
564 LOC.ADDRESS1,
565 LOC.ADDRESS2,
566 LOC.ADDRESS3,
567 LOC.ADDRESS4,
568 LOC.CITY || DECODE(LOC.CITY
569 ,NULL
570 ,''
571 ,', ') || NVL(LOC.STATE
572 ,LOC.PROVINCE) || DECODE(LOC.POSTAL_CODE
573 ,NULL
574 ,''
575 ,' ' || LOC.POSTAL_CODE) || DECODE(NVL(LOC.STATE
576 ,NVL(LOC.PROVINCE
577 ,LOC.POSTAL_CODE))
578 ,NULL
579 ,''
580 ,', ') || LOC.COUNTRY
581 FROM
582 WSH_TRIP_STOPS WTS,
583 WSH_DELIVERY_LEGS WDL,
584 WSH_DELIVERY_ASSIGNMENTS_V WDA,
585 WSH_DELIVERY_DETAILS WDD,
586 MTL_MATERIAL_TRANSACTIONS_TEMP MMTT,
587 WSH_LOCATIONS LOC
588 WHERE MMTT.PICK_SLIP_NUMBER = X_PS_NUMBER
589 AND MMTT.MOVE_ORDER_LINE_ID = WDD.MOVE_ORDER_LINE_ID
590 AND MMTT.PICK_SLIP_NUMBER IS NOT NULL
591 AND ABS(NVL(MMTT.TRANSACTION_QUANTITY
592 ,0)) > 0
593 AND WDD.DELIVERY_DETAIL_ID = WDA.DELIVERY_DETAIL_ID
594 AND WDA.DELIVERY_ID = WDL.DELIVERY_ID
595 AND WDL.PICK_UP_STOP_ID = WTS.STOP_ID
596 AND WTS.STOP_LOCATION_ID = LOC.WSH_LOCATION_ID
597 AND NVL(WDD.LINE_DIRECTION
598 ,'O') IN ( 'O' , 'IO' )
599 AND WDD.CONTAINER_FLAG IN ( 'N' , 'Y' )
600 UNION ALL
601 SELECT
602 LOC.ADDRESS1,
603 LOC.ADDRESS2,
604 LOC.ADDRESS3,
605 LOC.ADDRESS4,
606 LOC.CITY || DECODE(LOC.CITY
607 ,NULL
608 ,''
609 ,', ') || NVL(LOC.STATE
610 ,LOC.PROVINCE) || DECODE(LOC.POSTAL_CODE
611 ,NULL
612 ,''
613 ,' ' || LOC.POSTAL_CODE) || DECODE(NVL(LOC.STATE
614 ,NVL(LOC.PROVINCE
615 ,LOC.POSTAL_CODE))
616 ,NULL
617 ,''
618 ,', ') || LOC.COUNTRY
619 FROM
620 WSH_TRIP_STOPS WTS,
621 WSH_DELIVERY_LEGS WDL,
622 WSH_DELIVERY_ASSIGNMENTS_V WDA,
623 WSH_DELIVERY_DETAILS WDD,
624 MTL_MATERIAL_TRANSACTIONS MMT,
625 WSH_LOCATIONS LOC
626 WHERE MMT.PICK_SLIP_NUMBER = X_PS_NUMBER
627 AND MMT.MOVE_ORDER_LINE_ID = WDD.MOVE_ORDER_LINE_ID
628 AND MMT.PICK_SLIP_NUMBER IS NOT NULL
629 AND NVL(MMT.TRANSACTION_QUANTITY
630 ,0) < 0
631 AND WDD.DELIVERY_DETAIL_ID = WDA.DELIVERY_DETAIL_ID
632 AND WDA.DELIVERY_ID = WDL.DELIVERY_ID
633 AND WDL.PICK_UP_STOP_ID = WTS.STOP_ID
634 AND WTS.STOP_LOCATION_ID = LOC.WSH_LOCATION_ID
635 AND NVL(WDD.LINE_DIRECTION
636 ,'O') IN ( 'O' , 'IO' )
637 AND WDD.CONTAINER_FLAG IN ( 'N' , 'Y' )
638 UNION ALL
639 SELECT
640 LOC.ADDRESS1,
641 LOC.ADDRESS2,
642 LOC.ADDRESS3,
643 LOC.ADDRESS4,
644 LOC.CITY || DECODE(LOC.CITY
645 ,NULL
646 ,''
647 ,', ') || NVL(LOC.STATE
648 ,LOC.PROVINCE) || DECODE(LOC.POSTAL_CODE
649 ,NULL
650 ,''
651 ,' ' || LOC.POSTAL_CODE) || DECODE(NVL(LOC.STATE
652 ,NVL(LOC.PROVINCE
653 ,LOC.POSTAL_CODE))
654 ,NULL
655 ,''
656 ,', ') || LOC.COUNTRY
657 FROM
658 WSH_TRIP_STOPS WTS,
659 WSH_DELIVERY_LEGS WDL,
660 WSH_DELIVERY_ASSIGNMENTS_V WDA,
661 WSH_DELIVERY_DETAILS WDD,
662 MTL_TXN_REQUEST_LINES MTRL,
663 WSH_LOCATIONS LOC
664 WHERE MTRL.PICK_SLIP_NUMBER = X_PS_NUMBER
665 AND MTRL.LINE_ID = WDD.MOVE_ORDER_LINE_ID
666 AND MTRL.PICK_SLIP_NUMBER IS NOT NULL
667 AND WDD.DELIVERY_DETAIL_ID = WDA.DELIVERY_DETAIL_ID
668 AND WDA.DELIVERY_ID = WDL.DELIVERY_ID
669 AND WDL.PICK_UP_STOP_ID = WTS.STOP_ID
670 AND WTS.STOP_LOCATION_ID = LOC.WSH_LOCATION_ID
671 AND NVL(WDD.LINE_DIRECTION
672 ,'O') IN ( 'O' , 'IO' )
673 AND WDD.CONTAINER_FLAG IN ( 'N' , 'Y' );
674 ADDR1 VARCHAR2(240) := ' ';
675 ADDR2 VARCHAR2(240) := ' ';
676 ADDR3 VARCHAR2(240) := ' ';
677 ADDR4 VARCHAR2(240) := ' ';
678 ADDR5 VARCHAR2(300) := ' ';
679 BEGIN
680 /*SRW.REFERENCE(TS_ADDR1)*/NULL;
681 /*SRW.REFERENCE(TS_ADDR2)*/NULL;
682 /*SRW.REFERENCE(TS_ADDR3)*/NULL;
683 /*SRW.REFERENCE(TS_ADDR4)*/NULL;
684 /*SRW.REFERENCE(TS_ADDR5)*/NULL;
685 IF TRIP_STOP_FLAG = 'Y' THEN
686 IF DELIVERY_FLAG = 'Y' THEN
687 OPEN TRIP_STOP_FROM_DLVY(CF_DELIVERY_ID);
688 FETCH TRIP_STOP_FROM_DLVY
689 INTO ADDR1,ADDR2,ADDR3,ADDR4,ADDR5;
690 CLOSE TRIP_STOP_FROM_DLVY;
691 ELSE
692 OPEN TRIP_STOP_FROM_PS(PICK_SLIP_NUMBER);
693 FETCH TRIP_STOP_FROM_PS
694 INTO ADDR1,ADDR2,ADDR3,ADDR4,ADDR5;
695 CLOSE TRIP_STOP_FROM_PS;
696 END IF;
697 TS_ADDR1 := ADDR1;
698 IF (ADDR2 IS NOT NULL) THEN
699 TS_ADDR2 := ADDR2;
700 ELSIF (ADDR2 IS NULL AND ADDR3 IS NOT NULL) THEN
701 TS_ADDR2 := ADDR3;
702 ELSIF (ADDR2 IS NULL AND ADDR3 IS NULL AND ADDR4 IS NOT NULL) THEN
703 TS_ADDR2 := ADDR4;
704 ELSIF (ADDR2 IS NULL AND ADDR3 IS NULL AND ADDR4 IS NULL) THEN
705 TS_ADDR2 := ADDR5;
706 ELSE
707 TS_ADDR2 := ' ';
708 END IF;
709 IF (ADDR2 IS NOT NULL AND ADDR3 IS NOT NULL) THEN
710 TS_ADDR3 := ADDR3;
711 ELSIF (ADDR2 IS NOT NULL AND ADDR3 IS NULL AND ADDR4 IS NOT NULL) THEN
712 TS_ADDR3 := ADDR4;
713 ELSIF (ADDR2 IS NOT NULL AND ADDR3 IS NULL AND ADDR4 IS NULL) THEN
714 TS_ADDR3 := ADDR5;
715 ELSIF (ADDR2 IS NULL AND ADDR3 IS NOT NULL AND ADDR4 IS NOT NULL) THEN
716 TS_ADDR3 := ADDR4;
717 ELSIF (ADDR2 IS NULL AND ADDR3 IS NOT NULL AND ADDR4 IS NULL) THEN
718 TS_ADDR3 := ADDR5;
719 ELSIF (ADDR2 IS NULL AND ADDR3 IS NULL AND ADDR4 IS NOT NULL) THEN
720 TS_ADDR3 := ADDR5;
721 ELSIF (ADDR2 IS NULL AND ADDR3 IS NULL AND ADDR4 IS NULL) THEN
722 TS_ADDR3 := ' ';
723 END IF;
724 IF (ADDR2 IS NOT NULL AND ADDR3 IS NOT NULL AND ADDR4 IS NOT NULL) THEN
725 TS_ADDR4 := ADDR4;
726 ELSIF (ADDR2 IS NOT NULL AND ADDR3 IS NULL AND ADDR4 IS NULL) THEN
727 TS_ADDR4 := ' ';
728 ELSIF (ADDR2 IS NOT NULL AND ADDR3 IS NULL AND ADDR4 IS NOT NULL) THEN
729 TS_ADDR4 := ADDR5;
730 ELSIF (ADDR2 IS NULL AND ADDR3 IS NOT NULL AND ADDR4 IS NOT NULL) THEN
731 TS_ADDR4 := ADDR5;
732 ELSIF (ADDR2 IS NOT NULL AND ADDR3 IS NOT NULL AND ADDR4 IS NULL) THEN
733 TS_ADDR4 := ADDR5;
734 ELSIF (ADDR2 IS NULL AND ADDR3 IS NOT NULL AND ADDR4 IS NULL) THEN
735 TS_ADDR4 := ' ';
736 ELSIF (ADDR2 IS NULL AND ADDR3 IS NULL AND ADDR4 IS NOT NULL) THEN
737 TS_ADDR4 := ' ';
738 ELSIF (ADDR2 IS NULL AND ADDR3 IS NULL AND ADDR4 IS NULL) THEN
739 TS_ADDR4 := ' ';
740 END IF;
741 IF ((ADDR2 IS NULL) OR (ADDR3 IS NULL) OR (ADDR4 IS NULL)) THEN
742 TS_ADDR5 := ' ';
743 ELSE
744 TS_ADDR5 := ADDR5;
745 END IF;
746 END IF;
747 RETURN ' ';
748 EXCEPTION
749 WHEN OTHERS THEN
750 RETURN ' ';
751 END CF_TRIP_STOP_ADDRESS1FORMULA;
752
753 FUNCTION CF_DELIVERYFORMULA(DELIVERY_FLAG IN VARCHAR2
754 ,DELIVERY_NAME IN VARCHAR2) RETURN CHAR IS
755 BEGIN
756 IF DELIVERY_FLAG = 'Y' THEN
757 RETURN DELIVERY_NAME;
758 ELSE
759 RETURN ' ';
760 END IF;
761 END CF_DELIVERYFORMULA;
762
763 FUNCTION CF_REQUISITION_NUMBERFORMULA(SOURCE_HEADER_ID IN NUMBER) RETURN CHAR IS
764 CURSOR REQUISITION(X_ORDER_HEADER IN NUMBER) IS
765 SELECT
766 ORIG_SYS_DOCUMENT_REF
767 FROM
768 OE_ORDER_HEADERS_ALL
769 WHERE HEADER_ID = X_ORDER_HEADER
770 AND SOURCE_DOCUMENT_TYPE_ID = 10;
771 REQN_NUMBER OE_ORDER_HEADERS_ALL.ORIG_SYS_DOCUMENT_REF%TYPE := ' ';
772 BEGIN
773 OPEN REQUISITION(SOURCE_HEADER_ID);
774 FETCH REQUISITION
775 INTO REQN_NUMBER;
776 CLOSE REQUISITION;
777 RETURN REQN_NUMBER;
778 EXCEPTION
779 WHEN OTHERS THEN
780 RETURN ' ';
781 END CF_REQUISITION_NUMBERFORMULA;
782
783 FUNCTION CF_SUBINVENTORYFORMULA(SUBINVENTORY_FLAG IN VARCHAR2
784 ,FROM_SUBINVENTORY IN VARCHAR2) RETURN CHAR IS
785 BEGIN
786 IF SUBINVENTORY_FLAG = 'Y' THEN
787 RETURN FROM_SUBINVENTORY;
788 ELSE
789 RETURN ' ';
790 END IF;
791 END CF_SUBINVENTORYFORMULA;
792
793 FUNCTION CF_SHIPMENT_PRIORITYFORMULA(SHIPMENT_PRIORITY_FLAG IN VARCHAR2
794 ,PRIORITY IN VARCHAR2) RETURN CHAR IS
795 BEGIN
796 /*SRW.REFERENCE(SHIPMENT_PRIORITY_FLAG)*/NULL;
797 IF SHIPMENT_PRIORITY_FLAG = 'Y' THEN
798 RETURN PRIORITY;
799 ELSE
800 RETURN ' ';
801 END IF;
802 END CF_SHIPMENT_PRIORITYFORMULA;
803
804 FUNCTION CF_SHIP_TO_ADDRESSFORMULA(SHIP_TO_FLAG IN VARCHAR2
805 ,PICK_SLIP_NUMBER IN NUMBER) RETURN CHAR IS
806 CURSOR SHIP_TO(X_PS_NUM IN NUMBER) IS
807 SELECT
808 LOC.ADDRESS1,
809 LOC.ADDRESS2,
810 LOC.ADDRESS3,
811 LOC.ADDRESS4,
812 LOC.CITY || DECODE(LOC.CITY
813 ,NULL
814 ,''
815 ,', ') || NVL(LOC.STATE
816 ,LOC.PROVINCE) || DECODE(LOC.POSTAL_CODE
817 ,NULL
818 ,''
819 ,' ' || LOC.POSTAL_CODE) || DECODE(NVL(LOC.STATE
820 ,NVL(LOC.PROVINCE
821 ,LOC.POSTAL_CODE))
822 ,NULL
823 ,''
824 ,', ') || LOC.COUNTRY
825 FROM
826 WSH_LOCATIONS LOC
827 WHERE LOC.WSH_LOCATION_ID = (
828 SELECT
829 WDD.SHIP_TO_LOCATION_ID
830 FROM
831 WSH_DELIVERY_DETAILS WDD,
832 WSH_PICK_SLIP_V WPSV
833 WHERE WPSV.PICK_SLIP_NUMBER = X_PS_NUM
834 AND WPSV.MOVE_ORDER_LINE_ID = WDD.MOVE_ORDER_LINE_ID
835 AND ROWNUM = 1 );
836 ADDR1 VARCHAR2(240) := ' ';
837 ADDR2 VARCHAR2(240) := ' ';
838 ADDR3 VARCHAR2(240) := ' ';
839 ADDR4 VARCHAR2(240) := ' ';
840 ADDR5 VARCHAR2(300) := ' ';
841 BEGIN
842 /*SRW.REFERENCE(ST_ADDR1)*/NULL;
843 /*SRW.REFERENCE(ST_ADDR2)*/NULL;
844 /*SRW.REFERENCE(ST_ADDR3)*/NULL;
845 /*SRW.REFERENCE(ST_ADDR4)*/NULL;
846 /*SRW.REFERENCE(ST_ADDR5)*/NULL;
847 IF SHIP_TO_FLAG = 'Y' THEN
848 OPEN SHIP_TO(PICK_SLIP_NUMBER);
849 FETCH SHIP_TO
850 INTO ADDR1,ADDR2,ADDR3,ADDR4,ADDR5;
851 CLOSE SHIP_TO;
852 ST_ADDR1 := ADDR1;
853 IF (ADDR2 IS NOT NULL) THEN
854 ST_ADDR2 := ADDR2;
855 ELSIF (ADDR2 IS NULL AND ADDR3 IS NOT NULL) THEN
856 ST_ADDR2 := ADDR3;
857 ELSIF (ADDR2 IS NULL AND ADDR3 IS NULL AND ADDR4 IS NOT NULL) THEN
858 ST_ADDR2 := ADDR4;
859 ELSIF (ADDR2 IS NULL AND ADDR3 IS NULL AND ADDR4 IS NULL) THEN
860 ST_ADDR2 := ADDR5;
861 ELSE
862 ST_ADDR2 := ' ';
863 END IF;
864 IF (ADDR2 IS NOT NULL AND ADDR3 IS NOT NULL) THEN
865 ST_ADDR3 := ADDR3;
866 ELSIF (ADDR2 IS NOT NULL AND ADDR3 IS NULL AND ADDR4 IS NOT NULL) THEN
867 ST_ADDR3 := ADDR4;
868 ELSIF (ADDR2 IS NOT NULL AND ADDR3 IS NULL AND ADDR4 IS NULL) THEN
869 ST_ADDR3 := ADDR5;
870 ELSIF (ADDR2 IS NULL AND ADDR3 IS NOT NULL AND ADDR4 IS NOT NULL) THEN
871 ST_ADDR3 := ADDR4;
872 ELSIF (ADDR2 IS NULL AND ADDR3 IS NOT NULL AND ADDR4 IS NULL) THEN
873 ST_ADDR3 := ADDR5;
874 ELSIF (ADDR2 IS NULL AND ADDR3 IS NULL AND ADDR4 IS NOT NULL) THEN
875 ST_ADDR3 := ADDR5;
876 ELSIF (ADDR2 IS NULL AND ADDR3 IS NULL AND ADDR4 IS NULL) THEN
877 ST_ADDR3 := ' ';
878 END IF;
879 IF (ADDR2 IS NOT NULL AND ADDR3 IS NOT NULL AND ADDR4 IS NOT NULL) THEN
880 ST_ADDR4 := ADDR4;
881 ELSIF (ADDR2 IS NOT NULL AND ADDR3 IS NULL AND ADDR4 IS NULL) THEN
882 ST_ADDR4 := ' ';
883 ELSIF (ADDR2 IS NOT NULL AND ADDR3 IS NULL AND ADDR4 IS NOT NULL) THEN
884 ST_ADDR4 := ADDR5;
885 ELSIF (ADDR2 IS NULL AND ADDR3 IS NOT NULL AND ADDR4 IS NOT NULL) THEN
886 ST_ADDR4 := ADDR5;
887 ELSIF (ADDR2 IS NOT NULL AND ADDR3 IS NOT NULL AND ADDR4 IS NULL) THEN
888 ST_ADDR4 := ADDR5;
889 ELSIF (ADDR2 IS NULL AND ADDR3 IS NOT NULL AND ADDR4 IS NULL) THEN
890 ST_ADDR4 := ' ';
891 ELSIF (ADDR2 IS NULL AND ADDR3 IS NULL AND ADDR4 IS NOT NULL) THEN
892 ST_ADDR4 := ' ';
893 ELSIF (ADDR2 IS NULL AND ADDR3 IS NULL AND ADDR4 IS NULL) THEN
894 ST_ADDR4 := ' ';
895 END IF;
896 IF ((ADDR2 IS NULL) OR (ADDR3 IS NULL) OR (ADDR4 IS NULL)) THEN
897 ST_ADDR5 := ' ';
898 ELSE
899 ST_ADDR5 := ADDR5;
900 END IF;
901 END IF;
902 RETURN ' ';
903 EXCEPTION
904 WHEN OTHERS THEN
905 RETURN '';
906 END CF_SHIP_TO_ADDRESSFORMULA;
907
908 FUNCTION CF_CARRIERFORMULA(CARRIER_FLAG IN VARCHAR2
909 ,CARRIER IN VARCHAR2) RETURN CHAR IS
910 BEGIN
911 DECLARE
912 L_MEANING FND_LOOKUP_VALUES_VL.MEANING%TYPE;
913 CURSOR SHP_MTHD(X_CARRIER IN VARCHAR) IS
914 SELECT
915 MEANING
916 FROM
917 FND_LOOKUP_VALUES_VL
918 WHERE LOOKUP_CODE = X_CARRIER
919 AND LOOKUP_TYPE = 'SHIP_METHOD'
920 AND VIEW_APPLICATION_ID = 3;
921 BEGIN
922 IF CARRIER_FLAG = 'Y' THEN
923 OPEN SHP_MTHD(CARRIER);
924 FETCH SHP_MTHD
925 INTO L_MEANING;
926 CLOSE SHP_MTHD;
927 RETURN L_MEANING;
928 ELSE
929 RETURN ' ';
930 END IF;
931 END;
932 END CF_CARRIERFORMULA;
933
934 FUNCTION CF_TEMPFORMULA(SHIPMENT_PRIORITY_FLAG IN VARCHAR2
935 ,PRIORITY IN VARCHAR2) RETURN CHAR IS
936 BEGIN
937 IF SHIPMENT_PRIORITY_FLAG = 'Y' THEN
938 RETURN PRIORITY;
939 ELSE
940 RETURN ' ';
941 END IF;
942 END CF_TEMPFORMULA;
943
944 FUNCTION CF_DELIVERY_IDFORMULA(DELIVERY_FLAG IN VARCHAR2
945 ,DELIVERY_ID IN NUMBER) RETURN NUMBER IS
946 BEGIN
947 IF DELIVERY_FLAG = 'Y' THEN
948 RETURN (DELIVERY_ID);
949 ELSE
950 RETURN -1;
951 END IF;
952 END CF_DELIVERY_IDFORMULA;
953
954 FUNCTION CF_WAREHOUSEFORMULA(ORGANIZATION_ID1 IN NUMBER) RETURN CHAR IS
955 CURSOR WAREHOUSE_NAME IS
956 SELECT
957 NAME
958 FROM
959 HR_ORGANIZATION_UNITS
960 WHERE ORGANIZATION_ID = ORGANIZATION_ID1;
961 CURSOR WAREHOUSE_CODE IS
962 SELECT
963 ORGANIZATION_CODE
964 FROM
965 MTL_PARAMETERS
966 WHERE ORGANIZATION_ID = ORGANIZATION_ID1;
967 BEGIN
968 OPEN WAREHOUSE_NAME;
969 FETCH WAREHOUSE_NAME
970 INTO CP_WAREHOUSE_NAME;
971 CLOSE WAREHOUSE_NAME;
972 OPEN WAREHOUSE_CODE;
973 FETCH WAREHOUSE_CODE
974 INTO CP_WAREHOUSE_CODE;
975 CLOSE WAREHOUSE_CODE;
976 RETURN ' ';
977 EXCEPTION
978 WHEN OTHERS THEN
979 IF WAREHOUSE_NAME%ISOPEN THEN
980 CLOSE WAREHOUSE_NAME;
981 END IF;
982 IF WAREHOUSE_CODE%ISOPEN THEN
983 CLOSE WAREHOUSE_CODE;
984 END IF;
985 RETURN ' ';
986 END CF_WAREHOUSEFORMULA;
987
988 FUNCTION CF_TRIP_CHRFORMULA(CF_TRIP_ID IN NUMBER) RETURN CHAR IS
989 BEGIN
990 RETURN (TO_CHAR(CF_TRIP_ID));
991 END CF_TRIP_CHRFORMULA;
992
993 FUNCTION CF_FREIGHT_TERMS_NAMEFORMULA(FREIGHT_TERMS IN VARCHAR2
994 ,SOURCE_CODE IN VARCHAR2) RETURN CHAR IS
995 L_FREIGHT_TERMS VARCHAR2(80);
996 CURSOR L_GET_FREIGHT_TERMS IS
997 SELECT
998 FV.FREIGHT_TERMS
999 FROM
1000 OE_FRGHT_TERMS_ACTIVE_V FV
1001 WHERE FV.FREIGHT_TERMS_CODE = FREIGHT_TERMS;
1002 BEGIN
1003 IF SOURCE_CODE = 'OE' THEN
1004 IF FREIGHT_TERMS IS NOT NULL THEN
1005 OPEN L_GET_FREIGHT_TERMS;
1006 FETCH L_GET_FREIGHT_TERMS
1007 INTO L_FREIGHT_TERMS;
1008 CLOSE L_GET_FREIGHT_TERMS;
1009 ELSE
1010 L_FREIGHT_TERMS := NULL;
1011 END IF;
1012 ELSIF SOURCE_CODE = 'OKE' THEN
1013 L_FREIGHT_TERMS := NULL;
1014 END IF;
1015 RETURN (L_FREIGHT_TERMS);
1016 EXCEPTION
1017 WHEN OTHERS THEN
1018 IF L_GET_FREIGHT_TERMS%ISOPEN THEN
1019 CLOSE L_GET_FREIGHT_TERMS;
1020 END IF;
1021 RAISE;
1022 END CF_FREIGHT_TERMS_NAMEFORMULA;
1023
1024 FUNCTION CP_WAREHOUSE_CODE_P RETURN VARCHAR2 IS
1025 BEGIN
1026 RETURN CP_WAREHOUSE_CODE;
1027 END CP_WAREHOUSE_CODE_P;
1028
1029 FUNCTION CP_WAREHOUSE_NAME_P RETURN VARCHAR2 IS
1030 BEGIN
1031 RETURN CP_WAREHOUSE_NAME;
1032 END CP_WAREHOUSE_NAME_P;
1033
1034 FUNCTION ST_ADDR1_P RETURN VARCHAR2 IS
1035 BEGIN
1036 RETURN ST_ADDR1;
1037 END ST_ADDR1_P;
1038
1039 FUNCTION TS_ADDR3_P RETURN VARCHAR2 IS
1040 BEGIN
1041 RETURN TS_ADDR3;
1042 END TS_ADDR3_P;
1043
1044 FUNCTION TS_ADDR4_P RETURN VARCHAR2 IS
1045 BEGIN
1046 RETURN TS_ADDR4;
1047 END TS_ADDR4_P;
1048
1049 FUNCTION TS_ADDR5_P RETURN VARCHAR2 IS
1050 BEGIN
1051 RETURN TS_ADDR5;
1052 END TS_ADDR5_P;
1053
1054 FUNCTION TS_ADDR1_P RETURN VARCHAR2 IS
1055 BEGIN
1056 RETURN TS_ADDR1;
1057 END TS_ADDR1_P;
1058
1059 FUNCTION TS_ADDR2_P RETURN VARCHAR2 IS
1060 BEGIN
1061 RETURN TS_ADDR2;
1062 END TS_ADDR2_P;
1063
1064 FUNCTION ST_ADDR4_P RETURN VARCHAR2 IS
1065 BEGIN
1066 RETURN ST_ADDR4;
1067 END ST_ADDR4_P;
1068
1069 FUNCTION ST_ADDR5_P RETURN VARCHAR2 IS
1070 BEGIN
1071 RETURN ST_ADDR5;
1072 END ST_ADDR5_P;
1073
1074 FUNCTION ST_ADDR2_P RETURN VARCHAR2 IS
1075 BEGIN
1076 RETURN ST_ADDR2;
1077 END ST_ADDR2_P;
1078
1079 FUNCTION ST_ADDR3_P RETURN VARCHAR2 IS
1080 BEGIN
1081 RETURN ST_ADDR3;
1082 END ST_ADDR3_P;
1083
1084 FUNCTION CP_TRIP_NAME_P RETURN VARCHAR2 IS
1085 BEGIN
1086 RETURN CP_TRIP_NAME;
1087 END CP_TRIP_NAME_P;
1088
1089 FUNCTION CP_CACHE_DELIVERY_ID_P RETURN NUMBER IS
1090 BEGIN
1091 RETURN CP_CACHE_DELIVERY_ID;
1092 END CP_CACHE_DELIVERY_ID_P;
1093
1094 FUNCTION CP_CACHE_TRIP_NAME_P RETURN VARCHAR2 IS
1095 BEGIN
1096 RETURN CP_CACHE_TRIP_NAME;
1097 END CP_CACHE_TRIP_NAME_P;
1098
1099 FUNCTION CP_CACHE_TRIP_ID_P RETURN NUMBER IS
1100 BEGIN
1101 RETURN CP_CACHE_TRIP_ID;
1102 END CP_CACHE_TRIP_ID_P;
1103
1104 FUNCTION GET_DELIMITER(APPLICATION_SHORT_NAME IN VARCHAR2
1105 ,KEY_FLEX_CODE IN VARCHAR2
1106 ,STRUCTURE_NUMBER IN NUMBER) RETURN VARCHAR2 IS
1107 X0 VARCHAR2(2000);
1108 BEGIN
1109 /* STPROC.INIT('begin :X0 := FND_FLEX_EXT.GET_DELIMITER(:APPLICATION_SHORT_NAME, :KEY_FLEX_CODE, :STRUCTURE_NUMBER); end;');
1110 STPROC.BIND_O(X0);
1111 STPROC.BIND_I(APPLICATION_SHORT_NAME);
1112 STPROC.BIND_I(KEY_FLEX_CODE);
1113 STPROC.BIND_I(STRUCTURE_NUMBER);
1114 STPROC.EXECUTE;
1115 STPROC.RETRIEVE(1
1116 ,X0);*/
1117 X0 := FND_FLEX_EXT.GET_DELIMITER(APPLICATION_SHORT_NAME, KEY_FLEX_CODE, STRUCTURE_NUMBER);
1118 RETURN X0;
1119 END GET_DELIMITER;
1120
1121 FUNCTION GET_CCID(APPLICATION_SHORT_NAME IN VARCHAR2
1122 ,KEY_FLEX_CODE IN VARCHAR2
1123 ,STRUCTURE_NUMBER IN NUMBER
1124 ,VALIDATION_DATE IN VARCHAR2
1125 ,CONCATENATED_SEGMENTS IN VARCHAR2) RETURN NUMBER IS
1126 X0 NUMBER;
1127 BEGIN
1128 /*STPROC.INIT('begin :X0 := FND_FLEX_EXT.GET_CCID(:APPLICATION_SHORT_NAME, :KEY_FLEX_CODE, :STRUCTURE_NUMBER, :VALIDATION_DATE, :CONCATENATED_SEGMENTS); end;');
1129 STPROC.BIND_O(X0);
1130 STPROC.BIND_I(APPLICATION_SHORT_NAME);
1131 STPROC.BIND_I(KEY_FLEX_CODE);
1132 STPROC.BIND_I(STRUCTURE_NUMBER);
1133 STPROC.BIND_I(VALIDATION_DATE);
1134 STPROC.BIND_I(CONCATENATED_SEGMENTS);
1135 STPROC.EXECUTE;
1136 STPROC.RETRIEVE(1
1137 ,X0);*/
1138 X0 := FND_FLEX_EXT.GET_CCID(APPLICATION_SHORT_NAME, KEY_FLEX_CODE, STRUCTURE_NUMBER, VALIDATION_DATE, CONCATENATED_SEGMENTS);
1139 RETURN X0;
1140 END GET_CCID;
1141
1142 FUNCTION GET_SEGS(APPLICATION_SHORT_NAME IN VARCHAR2
1143 ,KEY_FLEX_CODE IN VARCHAR2
1144 ,STRUCTURE_NUMBER IN NUMBER
1145 ,COMBINATION_ID IN NUMBER) RETURN VARCHAR2 IS
1146 X0 VARCHAR2(2000);
1147 BEGIN
1148 /* STPROC.INIT('begin :X0 := FND_FLEX_EXT.GET_SEGS(:APPLICATION_SHORT_NAME, :KEY_FLEX_CODE, :STRUCTURE_NUMBER, :COMBINATION_ID); end;');
1149 STPROC.BIND_O(X0);
1150 STPROC.BIND_I(APPLICATION_SHORT_NAME);
1151 STPROC.BIND_I(KEY_FLEX_CODE);
1152 STPROC.BIND_I(STRUCTURE_NUMBER);
1153 STPROC.BIND_I(COMBINATION_ID);
1154 STPROC.EXECUTE;
1155 STPROC.RETRIEVE(1
1156 ,X0);*/
1157 X0 := FND_FLEX_EXT.GET_SEGS(APPLICATION_SHORT_NAME, KEY_FLEX_CODE, STRUCTURE_NUMBER, COMBINATION_ID);
1158 RETURN X0;
1159 END GET_SEGS;
1160 function B_task_idFt(organization_id varchar2) return varchar2 is
1161 begin
1162 if WSH_UTIL_VALIDATE.Check_Wms_Org(organization_id)='Y' then
1163 return ('TRUE');
1164 else
1165 return ('FALSE');
1166 end if;
1167 end;
1168
1169 END WSH_WSHRDPIK_XMLP_PKG;
1170
[Home] [Help]