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