1 PACKAGE BODY WSH_WSHRDPAK_XMLP_PKG AS
2 /* $Header: WSHRDPAKB.pls 120.8 2011/03/15 09:21:21 ashimalh ship $ */
3
4 FUNCTION AFTERPFORM RETURN BOOLEAN IS
5 BEGIN
6 --STANDALONE CHANGES
7 IF WMS_DEPLOY.WMS_DEPLOYMENT_MODE = 'D' THEN
8 P_STANDALONE := 'Y';
9 ELSE
10 P_STANDALONE := 'N';
11 END IF;
12
13 RETURN (TRUE);
14
15 END AFTERPFORM;
16
17 FUNCTION AFTERREPORT RETURN BOOLEAN IS
18 L_RETURN_STATUS VARCHAR2(5);
19 L_MSG_COUNT NUMBER;
20 L_MSG_DATA VARCHAR2(3000);
21 BEGIN
22 BEGIN
23 IF P_PRINT_MODE = 'FINAL' THEN
24 WSH_DOCUMENT_PVT.SET_FINAL_PRINT_DATE(1.0
25 ,NULL
26 ,'T'
27 ,NULL
28 ,L_RETURN_STATUS
29 ,L_MSG_COUNT
30 ,L_MSG_DATA
31 ,P_DELIVERY_ID
32 ,'PACK_TYPE'
33 ,CP_PRINT_DATE);
34 IF L_RETURN_STATUS <> 'S' THEN
35 /*SRW.MESSAGE(1
36 ,'Fatal error encountered when trying to set final print date.')*/NULL;
37 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
38 END IF;
39 END IF;
40 END;
41 BEGIN
42 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
43 EXCEPTION
44 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
45 /*SRW.MESSAGE(1
46 ,'Failed in SRWEXIT')*/NULL;
47 RAISE;
48 END;
49 RETURN (TRUE);
50 END AFTERREPORT;
51 FUNCTION CF_ITEM_NUMFORMULA(C_ITEM_FLEX IN VARCHAR2
52 ,C_INV_ITEM_ID IN NUMBER
53 ,C_DEL_ORG_ID IN NUMBER) RETURN VARCHAR2 IS
54 L_ITEM_FLEX VARCHAR2(2000);
55 BEGIN
56 /*SRW.REFERENCE(C_ITEM_FLEX)*/NULL;
57 -- LSP PROJECT : passing p_remove_client_code as 'Y'
58 L_ITEM_FLEX := WSH_UTIL_CORE.GET_ITEM_NAME(p_item_id => C_INV_ITEM_ID
59 ,p_organization_id => NVL(C_DEL_ORG_ID,P_ORGANIZATION_ID)
60 ,p_remove_client_code => 'Y');
61 RETURN (L_ITEM_FLEX);
62 EXCEPTION
63 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
64 RETURN ('User Exit Failure');
65 END CF_ITEM_NUMFORMULA;
66 FUNCTION CF_FREIGHT_CARRIERFORMULA(C_SHIP_METHOD IN VARCHAR2
67 ,C_DEL_ORG_ID IN NUMBER
68 ,C_Q2_DELIVERY_ID IN NUMBER) RETURN VARCHAR2 IS
69 L_CARRIER VARCHAR2(35);
70 L_COUNT NUMBER;
71 L_SHIP_METHOD_CODE WSH_TRIPS.SHIP_METHOD_CODE%TYPE;
72 L_TRIP_PRESENT BOOLEAN := FALSE;
73 BEGIN
74 /*SRW.REFERENCE(C_SHIP_METHOD)*/NULL;
75 /*SRW.REFERENCE(C_DEL_ORG_ID)*/NULL;
76 /*SRW.REFERENCE(P_ORGANIZATION_ID)*/NULL;
77 /*SRW.REFERENCE(C_Q2_DELIVERY_ID)*/NULL;
78 BEGIN
79 L_SHIP_METHOD_CODE := NULL;
80 SELECT
81 WT.SHIP_METHOD_CODE
82 INTO L_SHIP_METHOD_CODE
83 FROM
84 WSH_TRIPS WT,
85 WSH_TRIP_STOPS WTS,
86 WSH_DELIVERY_ASSIGNMENTS_V WDA,
87 WSH_DELIVERY_LEGS WDL
88 WHERE WTS.STOP_ID = WDL.PICK_UP_STOP_ID
89 AND WDA.DELIVERY_ID = C_Q2_DELIVERY_ID
90 AND WDA.DELIVERY_ID = WDL.DELIVERY_ID
91 AND WTS.TRIP_ID = WT.TRIP_ID
92 AND ROWNUM = 1;
93 IF SQL%NOTFOUND THEN
94 NULL;
95 END IF;
96 EXCEPTION
97 WHEN NO_DATA_FOUND THEN
98 L_SHIP_METHOD_CODE := NULL;
99 END;
100 IF L_SHIP_METHOD_CODE IS NULL THEN
101 L_SHIP_METHOD_CODE := C_SHIP_METHOD;
102 ELSE
103 L_TRIP_PRESENT := TRUE;
104 END IF;
105 IF L_SHIP_METHOD_CODE IS NOT NULL THEN
106 SELECT
107 COUNT(NVL(SUBSTRB(A.SHIP_METHOD_MEANING
108 ,1
109 ,35)
110 ,L_SHIP_METHOD_CODE))
111 INTO L_COUNT
112 FROM
113 WSH_CARRIER_SERVICES A,
114 WSH_ORG_CARRIER_SERVICES B
115 WHERE A.SHIP_METHOD_CODE = L_SHIP_METHOD_CODE
116 AND A.CARRIER_SERVICE_ID = B.CARRIER_SERVICE_ID
117 AND B.ORGANIZATION_ID = NVL(C_DEL_ORG_ID
118 ,P_ORGANIZATION_ID);
119 IF NVL(L_COUNT
120 ,0) <> 1 THEN
121 L_CARRIER := '';
122 ELSE
123 SELECT
124 NVL(SUBSTRB(A.SHIP_METHOD_MEANING
125 ,1
126 ,35)
127 ,L_SHIP_METHOD_CODE)
128 INTO L_CARRIER
129 FROM
130 WSH_CARRIER_SERVICES A,
131 WSH_ORG_CARRIER_SERVICES B
132 WHERE A.SHIP_METHOD_CODE = L_SHIP_METHOD_CODE
133 AND A.CARRIER_SERVICE_ID = B.CARRIER_SERVICE_ID
134 AND B.ORGANIZATION_ID = NVL(C_DEL_ORG_ID
135 ,P_ORGANIZATION_ID);
136 END IF;
137 ELSE
138 L_CARRIER := '';
139 END IF;
140 IF (L_TRIP_PRESENT AND L_CARRIER IS NULL) THEN
141 L_CARRIER := L_SHIP_METHOD_CODE;
142 END IF;
143 RETURN (L_CARRIER);
144 END CF_FREIGHT_CARRIERFORMULA;
145 FUNCTION CF_FREIGHT_TERMSFORMULA(C_FREIGHT_TERMS_CODE IN VARCHAR2) RETURN VARCHAR2 IS
146 L_FREIGHT_TERMS VARCHAR2(80);
147 BEGIN
148 /*SRW.REFERENCE(C_FREIGHT_TERMS_CODE)*/NULL;
149 IF CP_SOURCE_CODE = 'OE' THEN
150 IF C_FREIGHT_TERMS_CODE IS NOT NULL THEN
151 SELECT
152 FV.FREIGHT_TERMS
153 INTO L_FREIGHT_TERMS
154 FROM
155 OE_FRGHT_TERMS_ACTIVE_V FV
156 WHERE FV.FREIGHT_TERMS_CODE = C_FREIGHT_TERMS_CODE;
157 ELSE
158 L_FREIGHT_TERMS := '';
159 END IF;
160 ELSIF CP_SOURCE_CODE = 'OKE' THEN
161 L_FREIGHT_TERMS := '';
162 END IF;
163 RETURN (L_FREIGHT_TERMS);
164 EXCEPTION
165 WHEN NO_DATA_FOUND THEN
166 /*SRW.MESSAGE(1
167 ,'Freight terms lookups not found.')*/NULL;
168 RETURN (NULL);
169 WHEN OTHERS THEN
170 RAISE;
171 END CF_FREIGHT_TERMSFORMULA;
172 FUNCTION CF_FOBFORMULA(C_FOB_CODE IN VARCHAR2) RETURN VARCHAR2 IS
173 L_FOB VARCHAR2(80);
174 BEGIN
175 /*SRW.REFERENCE(C_FOB_CODE)*/NULL;
176 IF CP_SOURCE_CODE = 'OE' THEN
177 IF C_FOB_CODE IS NOT NULL THEN
178 SELECT
179 FOB
180 INTO L_FOB
181 FROM
182 OE_FOBS_ACTIVE_V FV
183 WHERE FV.FOB_CODE = C_FOB_CODE;
184 ELSE
185 L_FOB := '';
186 END IF;
187 ELSIF CP_SOURCE_CODE = 'OKE' THEN
188 L_FOB := '';
189 END IF;
190 RETURN (L_FOB);
191 EXCEPTION
192 WHEN NO_DATA_FOUND THEN
193 /*SRW.MESSAGE(1
194 ,'FOB lookups not found.')*/NULL;
195 RETURN (NULL);
196 WHEN OTHERS THEN
197 RAISE;
198 END CF_FOBFORMULA;
199 FUNCTION CF_CUST_ITEM_NUMFORMULA(C_CUSTOMER_ITEM_ID IN NUMBER) RETURN VARCHAR2 IS
200 L_CUST_ITEM_NUMBER VARCHAR2(50);
201 BEGIN
202 /*SRW.REFERENCE(C_CUSTOMER_ITEM_ID)*/NULL;
203 IF C_CUSTOMER_ITEM_ID IS NOT NULL THEN
204 SELECT
205 CUSTOMER_ITEM_NUMBER
206 INTO L_CUST_ITEM_NUMBER
207 FROM
208 MTL_CUSTOMER_ITEMS MCI
209 WHERE MCI.CUSTOMER_ITEM_ID = C_CUSTOMER_ITEM_ID;
210 ELSE
211 L_CUST_ITEM_NUMBER := '';
212 END IF;
213 RETURN (L_CUST_ITEM_NUMBER);
214 EXCEPTION
215 WHEN OTHERS THEN
216 RETURN ('');
217 END CF_CUST_ITEM_NUMFORMULA;
218 FUNCTION CF_FROM_CITY_STATE_ZIPFORMULA(C_FROM_CITY IN VARCHAR2
219 ,C_FROM_POSTAL_CODE IN VARCHAR2
220 ,C_FROM_REGION IN VARCHAR2) RETURN VARCHAR2 IS
221 CITY_REGION_POSTAL VARCHAR2(190);
222 BEGIN
223 /*SRW.REFERENCE(C_FROM_CITY)*/NULL;
224 /*SRW.REFERENCE(C_FROM_POSTAL_CODE)*/NULL;
225 /*SRW.REFERENCE(C_FROM_REGION)*/NULL;
226 CITY_REGION_POSTAL := WSH_UTIL_CORE.CITY_REGION_POSTAL(C_FROM_CITY
227 ,C_FROM_REGION
228 ,C_FROM_POSTAL_CODE);
229 IF (CITY_REGION_POSTAL IS NULL) THEN
230 CITY_REGION_POSTAL := C_FROM_CITY || ', ' || C_FROM_REGION || ' ' || C_FROM_POSTAL_CODE;
231 END IF;
232 RETURN (CITY_REGION_POSTAL);
233 END CF_FROM_CITY_STATE_ZIPFORMULA;
234 FUNCTION CF_TO_CITY_STATE_ZIPFORMULA(C_TO_CITY IN VARCHAR2
235 ,C_TO_POSTAL_CODE IN VARCHAR2
236 ,C_TO_REGION IN VARCHAR2) RETURN VARCHAR2 IS
237 CITY_REGION_POSTAL VARCHAR2(190);
238 BEGIN
239 /*SRW.REFERENCE(C_TO_CITY)*/NULL;
240 /*SRW.REFERENCE(C_TO_POSTAL_CODE)*/NULL;
241 /*SRW.REFERENCE(C_TO_REGION)*/NULL;
242 CITY_REGION_POSTAL := WSH_UTIL_CORE.CITY_REGION_POSTAL(C_TO_CITY
243 ,C_TO_REGION
244 ,C_TO_POSTAL_CODE);
245 RETURN (CITY_REGION_POSTAL);
246 END CF_TO_CITY_STATE_ZIPFORMULA;
247 FUNCTION CF_BILL_CITY_STATE_ZIPFORMULA( CP_BILL_TOWN_OR_CITY in varchar2,CP_BILL_REGION in varchar2,
248 CP_BILL_POSTAL_CODE in varchar2 ) RETURN VARCHAR2 IS
249 CITY_REGION_POSTAL VARCHAR2(190);
250 BEGIN
251 /*SRW.REFERENCE(CP_BILL_TOWN_OR_CITY)*/NULL;
252 /*SRW.REFERENCE(CP_BILL_POSTAL_CODE)*/NULL;
253 /*SRW.REFERENCE(CP_BILL_REGION)*/NULL;
254 CITY_REGION_POSTAL := WSH_UTIL_CORE.CITY_REGION_POSTAL(CP_BILL_TOWN_OR_CITY
255 ,CP_BILL_REGION
256 ,CP_BILL_POSTAL_CODE);
257 IF (CITY_REGION_POSTAL IS NULL AND CP_BILL_TOWN_OR_CITY IS NOT NULL) THEN --RTV changes
258 CITY_REGION_POSTAL := CP_BILL_TOWN_OR_CITY || ', ' || CP_BILL_REGION || ' ' || CP_BILL_POSTAL_CODE;
259 END IF;
260 RETURN (CITY_REGION_POSTAL);
261 END CF_BILL_CITY_STATE_ZIPFORMULA;
262 FUNCTION CF_CUM_QTYFORMULA(C_DEL_CUSTOMER_ID IN NUMBER
263 ,C_CUSTOMER_ID IN NUMBER
264 ,C_SRC_LINE_ID IN NUMBER) RETURN NUMBER IS
265 L_RESULT NUMBER;
266 L_RETURN_STATUS VARCHAR2(5);
267 L_MSG_COUNT NUMBER;
268 L_MSG_DATA VARCHAR2(3000);
269 L_CUSTOMER_ID WSH_NEW_DELIVERIES.CUSTOMER_ID%TYPE;
270 BEGIN
271 /*SRW.REFERENCE(C_DEL_CUSTOMER_ID)*/NULL;
272 /*SRW.REFERENCE(C_CUSTOMER_ID)*/NULL;
273 /*SRW.REFERENCE(C_SRC_LINE_ID)*/NULL;
274 L_CUSTOMER_ID := NVL(C_CUSTOMER_ID
275 ,C_DEL_CUSTOMER_ID);
276 L_RESULT := WSH_DOCUMENT_PVT.GET_CUMQTY(1.0
277 ,NULL
278 ,NULL
279 ,NULL
280 ,L_RETURN_STATUS
281 ,L_MSG_COUNT
282 ,L_MSG_DATA
283 ,L_CUSTOMER_ID
284 ,C_SRC_LINE_ID);
285 RETURN ROUND(NVL(L_RESULT
286 ,0)
287 ,P_QUANTITY_PRECISION);
288 END CF_CUM_QTYFORMULA;
289 FUNCTION CF_UNSHIPPED_QTYFORMULA(C_SRC_LINE_ID IN NUMBER) RETURN NUMBER IS
290 TOTAL_UNSHIPPED_QUANTITY NUMBER;
291 L_LINE_SET_ID NUMBER;
292 L_LINE_ID NUMBER;
293 BEGIN
294 IF CP_SOURCE_CODE = 'OE' THEN
295 SELECT
296 NVL(L.LINE_SET_ID
297 ,-999),
298 L.LINE_ID
299 INTO L_LINE_SET_ID,L_LINE_ID
300 FROM
301 OE_ORDER_LINES_ALL L
302 WHERE L.LINE_ID = C_SRC_LINE_ID;
303 SELECT
304 SUM(WDD.REQUESTED_QUANTITY) - NVL(SUM(WDD.SHIPPED_QUANTITY)
305 ,0)
306 INTO TOTAL_UNSHIPPED_QUANTITY
307 FROM
308 WSH_DELIVERY_DETAILS WDD,
309 OE_ORDER_LINES_ALL L
310 WHERE WDD.SOURCE_LINE_ID = L.LINE_ID
311 AND WDD.SOURCE_CODE = 'OE'
312 AND WDD.CONTAINER_FLAG = 'N'
313 AND ( ( L.LINE_SET_ID is not null
314 AND L.LINE_SET_ID = L_LINE_SET_ID )
315 OR ( L.LINE_ID = L_LINE_ID ) );
316 ELSIF CP_SOURCE_CODE = 'OKE' THEN
317 SELECT
318 SUM(WDD.REQUESTED_QUANTITY) - NVL(SUM(WDD.SHIPPED_QUANTITY)
319 ,0)
320 INTO TOTAL_UNSHIPPED_QUANTITY
321 FROM
322 WSH_DELIVERY_DETAILS WDD
323 WHERE WDD.SOURCE_LINE_ID = C_SRC_LINE_ID
324 AND WDD.SOURCE_CODE = 'OKE';
325 END IF;
326 RETURN TOTAL_UNSHIPPED_QUANTITY;
327 END CF_UNSHIPPED_QTYFORMULA;
328 FUNCTION CF_FROM_ADDR_2FORMULA(C_FROM_ADDRESS_2 IN VARCHAR2
329 ,C_FROM_ADDRESS_3 IN VARCHAR2
330 ,CF_FROM_CITY_STATE_ZIP IN VARCHAR2
331 ,C_FROM_COUNTRY IN VARCHAR2) RETURN VARCHAR2 IS
332 BEGIN
333 /*SRW.REFERENCE(C_FROM_ADDRESS_2)*/NULL;
334 /*SRW.REFERENCE(C_FROM_ADDRESS_3)*/NULL;
335 /*SRW.REFERENCE(CF_FROM_CITY_STATE_ZIP)*/NULL;
336 /*SRW.REFERENCE(C_FROM_COUNTRY)*/NULL;
337 IF (C_FROM_ADDRESS_2 IS NOT NULL) THEN
338 RETURN (C_FROM_ADDRESS_2);
339 ELSIF (C_FROM_ADDRESS_2 IS NULL AND C_FROM_ADDRESS_3 IS NOT NULL) THEN
340 RETURN (C_FROM_ADDRESS_3);
341 ELSIF (C_FROM_ADDRESS_2 IS NULL AND C_FROM_ADDRESS_3 IS NULL) THEN
342 RETURN (CF_FROM_CITY_STATE_ZIP || ', ' || C_FROM_COUNTRY);
343 END IF;
344 END CF_FROM_ADDR_2FORMULA;
345 FUNCTION CF_FROM_ADDR3FORMULA(C_FROM_ADDRESS_2 IN VARCHAR2
346 ,C_FROM_ADDRESS_3 IN VARCHAR2
347 ,CF_FROM_CITY_STATE_ZIP IN VARCHAR2
348 ,C_FROM_COUNTRY IN VARCHAR2) RETURN VARCHAR2 IS
349 BEGIN
350 /*SRW.REFERENCE(C_FROM_ADDRESS_2)*/NULL;
351 /*SRW.REFERENCE(C_FROM_ADDRESS_3)*/NULL;
352 /*SRW.REFERENCE(CF_FROM_CITY_STATE_ZIP)*/NULL;
353 /*SRW.REFERENCE(C_FROM_COUNTRY)*/NULL;
354 IF (C_FROM_ADDRESS_2 IS NOT NULL AND C_FROM_ADDRESS_3 IS NOT NULL) THEN
355 RETURN (C_FROM_ADDRESS_3);
356 ELSIF (C_FROM_ADDRESS_2 IS NOT NULL AND C_FROM_ADDRESS_3 IS NULL) THEN
357 RETURN (CF_FROM_CITY_STATE_ZIP || ', ' || C_FROM_COUNTRY);
358 ELSIF (C_FROM_ADDRESS_2 IS NULL AND C_FROM_ADDRESS_3 IS NOT NULL) THEN
359 RETURN (CF_FROM_CITY_STATE_ZIP || ', ' || C_FROM_COUNTRY);
360 ELSIF (C_FROM_ADDRESS_2 IS NULL AND C_FROM_ADDRESS_3 IS NULL) THEN
361 RETURN (' ');
362 END IF;
363 END CF_FROM_ADDR3FORMULA;
364 FUNCTION CF_FROM_CITYFORMULA(C_FROM_ADDRESS_2 IN VARCHAR2
365 ,C_FROM_ADDRESS_3 IN VARCHAR2
366 ,CF_FROM_CITY_STATE_ZIP IN VARCHAR2
367 ,C_FROM_COUNTRY IN VARCHAR2) RETURN CHAR IS
368 BEGIN
369 /*SRW.REFERENCE(C_FROM_ADDRESS_2)*/NULL;
370 /*SRW.REFERENCE(C_FROM_ADDRESS_3)*/NULL;
371 /*SRW.REFERENCE(CF_FROM_CITY_STATE_ZIP)*/NULL;
372 /*SRW.REFERENCE(C_FROM_COUNTRY)*/NULL;
373 IF (C_FROM_ADDRESS_2 IS NOT NULL AND C_FROM_ADDRESS_3 IS NOT NULL) THEN
374 RETURN (CF_FROM_CITY_STATE_ZIP || ', ' || C_FROM_COUNTRY);
375 ELSIF (C_FROM_ADDRESS_2 IS NULL OR C_FROM_ADDRESS_3 IS NULL) THEN
376 RETURN (' ');
377 END IF;
378 END CF_FROM_CITYFORMULA;
379 FUNCTION CF_TO_ADDR_2FORMULA(C_TO_ADDRESS_2 IN VARCHAR2
380 ,C_TO_ADDRESS_3 IN VARCHAR2
381 ,C_TO_ADDRESS_4 IN VARCHAR2
382 ,CF_TO_CITY_STATE_ZIP IN VARCHAR2
383 ,C_TO_COUNTRY IN VARCHAR2) RETURN CHAR IS
384 BEGIN
385 /*SRW.REFERENCE(C_TO_ADDRESS_2)*/NULL;
386 /*SRW.REFERENCE(C_TO_ADDRESS_3)*/NULL;
387 /*SRW.REFERENCE(C_TO_ADDRESS_4)*/NULL;
388 /*SRW.REFERENCE(CF_TO_CITY_STATE_ZIP)*/NULL;
389 /*SRW.REFERENCE(C_TO_COUNTRY)*/NULL;
390 IF (C_TO_ADDRESS_2 IS NOT NULL) THEN
391 RETURN (C_TO_ADDRESS_2);
392 ELSIF (C_TO_ADDRESS_2 IS NULL AND C_TO_ADDRESS_3 IS NOT NULL) THEN
393 RETURN (C_TO_ADDRESS_3);
394 ELSIF (C_TO_ADDRESS_2 IS NULL AND C_TO_ADDRESS_3 IS NULL AND C_TO_ADDRESS_4 IS NOT NULL) THEN
395 RETURN (C_TO_ADDRESS_4);
396 ELSIF (C_TO_ADDRESS_2 IS NULL AND C_TO_ADDRESS_3 IS NULL AND C_TO_ADDRESS_4 IS NULL) THEN
397 IF (CF_TO_CITY_STATE_ZIP IS NULL) THEN
398 RETURN (C_TO_COUNTRY);
399 ELSE
400 RETURN (CF_TO_CITY_STATE_ZIP || ', ' || C_TO_COUNTRY);
401 END IF;
402 END IF;
403 END CF_TO_ADDR_2FORMULA;
404 FUNCTION CF_TO_ADDR_3FORMULA(C_TO_ADDRESS_2 IN VARCHAR2
405 ,C_TO_ADDRESS_3 IN VARCHAR2
406 ,C_TO_ADDRESS_4 IN VARCHAR2
407 ,CF_TO_CITY_STATE_ZIP IN VARCHAR2
408 ,C_TO_COUNTRY IN VARCHAR2) RETURN CHAR IS
409 L_CF_TO_CITY_STATE_ZIP VARCHAR2(190);
410 BEGIN
411 /*SRW.REFERENCE(C_TO_ADDRESS_2)*/NULL;
412 /*SRW.REFERENCE(C_TO_ADDRESS_3)*/NULL;
413 /*SRW.REFERENCE(C_TO_ADDRESS_4)*/NULL;
414 /*SRW.REFERENCE(CF_TO_CITY_STATE_ZIP)*/NULL;
415 /*SRW.REFERENCE(C_TO_COUNTRY)*/NULL;
416 IF (CF_TO_CITY_STATE_ZIP IS NULL) THEN
417 L_CF_TO_CITY_STATE_ZIP := '';
418 ELSE
419 L_CF_TO_CITY_STATE_ZIP := CF_TO_CITY_STATE_ZIP || ', ';
420 END IF;
421 IF (C_TO_ADDRESS_2 IS NOT NULL AND C_TO_ADDRESS_3 IS NOT NULL AND C_TO_ADDRESS_4 IS NOT NULL) THEN
422 RETURN (C_TO_ADDRESS_3);
423 ELSIF (C_TO_ADDRESS_2 IS NOT NULL AND C_TO_ADDRESS_3 IS NULL AND C_TO_ADDRESS_4 IS NULL) THEN
424 RETURN (L_CF_TO_CITY_STATE_ZIP || C_TO_COUNTRY);
425 ELSIF (C_TO_ADDRESS_2 IS NOT NULL AND C_TO_ADDRESS_3 IS NULL AND C_TO_ADDRESS_4 IS NOT NULL) THEN
426 RETURN (C_TO_ADDRESS_4);
427 ELSIF (C_TO_ADDRESS_2 IS NULL AND C_TO_ADDRESS_3 IS NOT NULL AND C_TO_ADDRESS_4 IS NOT NULL) THEN
428 RETURN (C_TO_ADDRESS_4);
429 ELSIF (C_TO_ADDRESS_2 IS NOT NULL AND C_TO_ADDRESS_3 IS NOT NULL AND C_TO_ADDRESS_4 IS NULL) THEN
430 RETURN (C_TO_ADDRESS_3);
431 ELSIF (C_TO_ADDRESS_2 IS NULL AND C_TO_ADDRESS_3 IS NOT NULL AND C_TO_ADDRESS_4 IS NULL) THEN
432 RETURN (L_CF_TO_CITY_STATE_ZIP || C_TO_COUNTRY);
433 ELSIF (C_TO_ADDRESS_2 IS NULL AND C_TO_ADDRESS_3 IS NULL AND C_TO_ADDRESS_4 IS NOT NULL) THEN
434 RETURN (L_CF_TO_CITY_STATE_ZIP || C_TO_COUNTRY);
435 ELSIF (C_TO_ADDRESS_2 IS NULL AND C_TO_ADDRESS_3 IS NULL AND C_TO_ADDRESS_4 IS NULL) THEN
436 RETURN (' ');
437 END IF;
438 END CF_TO_ADDR_3FORMULA;
439 FUNCTION CF_TO_CITYFORMULA(C_TO_ADDRESS_2 IN VARCHAR2
440 ,C_TO_ADDRESS_3 IN VARCHAR2
441 ,CF_TO_CITY_STATE_ZIP IN VARCHAR2
442 ,C_TO_COUNTRY IN VARCHAR2
443 ,C_TO_ADDRESS_4 IN VARCHAR2) RETURN CHAR IS
444 BEGIN
445 /*SRW.REFERENCE(C_TO_ADDRESS_2)*/NULL;
446 /*SRW.REFERENCE(C_TO_ADDRESS_3)*/NULL;
447 /*SRW.REFERENCE(CF_TO_CITY_STATE_ZIP)*/NULL;
448 /*SRW.REFERENCE(C_TO_COUNTRY)*/NULL;
449 /*SRW.REFERENCE(C_TO_ADDRESS_4)*/NULL;
450 IF (C_TO_ADDRESS_2 IS NOT NULL AND C_TO_ADDRESS_3 IS NOT NULL AND C_TO_ADDRESS_4 IS NOT NULL) THEN
451 RETURN (CF_TO_CITY_STATE_ZIP || ', ' || C_TO_COUNTRY);
452 ELSE
453 RETURN (' ');
454 END IF;
455 END CF_TO_CITYFORMULA;
456 FUNCTION CF_BILL_ADDR_2FORMULA(CF_BILL_CITY_STATE_ZIP IN VARCHAR2) RETURN CHAR IS
457 BEGIN
458 /*SRW.REFERENCE(CP_BILL_ADDRESS_LINE_2)*/NULL;
459 /*SRW.REFERENCE(CP_BILL_ADDRESS_LINE_3)*/NULL;
460 /*SRW.REFERENCE(CP_BILL_ADDRESS_LINE_4)*/NULL;
461 /*SRW.REFERENCE(CF_BILL_CITY_STATE_ZIP)*/NULL;
462 /*SRW.REFERENCE(CP_BILL_COUNTRY)*/NULL;
463 IF (CP_BILL_ADDRESS_LINE_2 IS NOT NULL) THEN
464 RETURN (CP_BILL_ADDRESS_LINE_2);
465 ELSIF (CP_BILL_ADDRESS_LINE_2 IS NULL AND CP_BILL_ADDRESS_LINE_3 IS NOT NULL) THEN
466 RETURN (CP_BILL_ADDRESS_LINE_3);
467 ELSIF (CP_BILL_ADDRESS_LINE_2 IS NULL AND CP_BILL_ADDRESS_LINE_3 IS NULL AND CP_BILL_ADDRESS_LINE_4 IS NOT NULL) THEN
468 RETURN (CP_BILL_ADDRESS_LINE_4);
469 ELSIF (CP_BILL_ADDRESS_LINE_2 IS NULL AND CP_BILL_ADDRESS_LINE_3 IS NULL AND CP_BILL_ADDRESS_LINE_4 IS NULL) THEN
470 --RTV changes
471 IF (CF_BILL_CITY_STATE_ZIP IS NOT NULL AND CP_BILL_COUNTRY IS NOT NULL) THEN
472 RETURN (CF_BILL_CITY_STATE_ZIP || ', ' || CP_BILL_COUNTRY);
473 ELSIF (CF_BILL_CITY_STATE_ZIP IS NULL AND CP_BILL_COUNTRY IS NOT NULL) THEN
474 RETURN (CP_BILL_COUNTRY);
475 ELSE
476 RETURN (CF_BILL_CITY_STATE_ZIP);
477 END IF;
478 --RTV changes
479 END IF;
480 END CF_BILL_ADDR_2FORMULA;
481 FUNCTION CF_BILL_ADDR_3FORMULA(CF_BILL_CITY_STATE_ZIP IN VARCHAR2) RETURN CHAR IS
482 BEGIN
483 /*SRW.REFERENCE(CP_BILL_ADDRESS_LINE_2)*/NULL;
484 /*SRW.REFERENCE(CP_BILL_ADDRESS_LINE_3)*/NULL;
485 /*SRW.REFERENCE(CP_BILL_ADDRESS_LINE_4)*/NULL;
486 /*SRW.REFERENCE(CF_BILL_CITY_STATE_ZIP)*/NULL;
487 /*SRW.REFERENCE(CP_BILL_COUNTRY)*/NULL;
488 IF (CP_BILL_ADDRESS_LINE_2 IS NOT NULL AND CP_BILL_ADDRESS_LINE_3 IS NOT NULL) THEN
489 RETURN (CP_BILL_ADDRESS_LINE_3);
490 ELSIF (CP_BILL_ADDRESS_LINE_2 IS NOT NULL AND CP_BILL_ADDRESS_LINE_3 IS NULL AND CP_BILL_ADDRESS_LINE_4 IS NOT NULL) THEN
491 RETURN (CP_BILL_ADDRESS_LINE_4);
492 ELSIF (CP_BILL_ADDRESS_LINE_2 IS NOT NULL AND CP_BILL_ADDRESS_LINE_3 IS NULL AND CP_BILL_ADDRESS_LINE_4 IS NULL) THEN
493 RETURN (CF_BILL_CITY_STATE_ZIP || ', ' || CP_BILL_COUNTRY);
494 ELSIF (CP_BILL_ADDRESS_LINE_2 IS NULL AND CP_BILL_ADDRESS_LINE_3 IS NOT NULL AND CP_BILL_ADDRESS_LINE_4 IS NOT NULL) THEN
495 RETURN (CP_BILL_ADDRESS_LINE_4);
496 ELSIF (CP_BILL_ADDRESS_LINE_2 IS NULL AND CP_BILL_ADDRESS_LINE_3 IS NULL AND CP_BILL_ADDRESS_LINE_4 IS NOT NULL) THEN
497 RETURN (CF_BILL_CITY_STATE_ZIP || ', ' || CP_BILL_COUNTRY);
498 ELSIF (CP_BILL_ADDRESS_LINE_2 IS NULL AND CP_BILL_ADDRESS_LINE_3 IS NOT NULL AND CP_BILL_ADDRESS_LINE_4 IS NULL) THEN
499 RETURN (CF_BILL_CITY_STATE_ZIP || ', ' || CP_BILL_COUNTRY);
500 ELSIF (CP_BILL_ADDRESS_LINE_2 IS NULL AND CP_BILL_ADDRESS_LINE_3 IS NULL AND CP_BILL_ADDRESS_LINE_4 IS NULL) THEN
501 RETURN ('');
502 END IF;
503 END CF_BILL_ADDR_3FORMULA;
504 FUNCTION CF_BILL_CITYFORMULA(CF_BILL_CITY_STATE_ZIP IN VARCHAR2) RETURN CHAR IS
505 BEGIN
506 /*SRW.REFERENCE(CP_BILL_ADDRESS_LINE_2)*/NULL;
507 /*SRW.REFERENCE(CP_BILL_ADDRESS_LINE_3)*/NULL;
508 /*SRW.REFERENCE(CP_BILL_ADDRESS_LINE_4)*/NULL;
509 /*SRW.REFERENCE(CF_BILL_CITY_STATE_ZIP)*/NULL;
510 /*SRW.REFERENCE(CP_BILL_COUNTRY)*/NULL;
511 IF (CP_BILL_ADDRESS_LINE_2 IS NOT NULL AND CP_BILL_ADDRESS_LINE_3 IS NOT NULL AND CP_BILL_ADDRESS_LINE_4 IS NOT NULL) THEN
512 RETURN (CF_BILL_CITY_STATE_ZIP || ', ' || CP_BILL_COUNTRY);
513 ELSIF (CP_BILL_ADDRESS_LINE_2 IS NULL OR CP_BILL_ADDRESS_LINE_3 IS NULL OR CP_BILL_ADDRESS_LINE_4 IS NULL) THEN
514 RETURN ('');
515 END IF;
516 END CF_BILL_CITYFORMULA;
517 FUNCTION CF_UNSHIP_ITEM_NAMEFORMULA(BO_INVENTORY_ITEM_ID IN NUMBER
518 ,BO_ORGANIZATION_ID IN NUMBER) RETURN CHAR IS
519 L_BO_ITEM_FLEX VARCHAR2(2000);
520 BEGIN
521 /*SRW.REFERENCE(BO_INVENTORY_ITEM_ID)*/NULL;
522 /*SRW.REFERENCE(BO_ORGANIZATION_ID)*/NULL;
523 L_BO_ITEM_FLEX := WSH_UTIL_CORE.GET_ITEM_NAME(BO_INVENTORY_ITEM_ID
524 ,NVL(BO_ORGANIZATION_ID
525 ,P_ORGANIZATION_ID));
526 RETURN (L_BO_ITEM_FLEX);
527 EXCEPTION
528 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
529 RETURN ('User Exit Failure');
530 END CF_UNSHIP_ITEM_NAMEFORMULA;
531 FUNCTION CF_NUM_OF_LPNSFORMULA(C_Q1_DELIVERY_ID IN NUMBER
532 ,NUM_LPN IN NUMBER) RETURN NUMBER IS
533 BEGIN
534 DECLARE
535 CURSOR BOXES IS
536 SELECT
537 count(*)
538 FROM
539 WSH_DELIVERY_ASSIGNMENTS_V WDA,
540 WSH_DELIVERY_DETAILS WDD
541 WHERE WDD.DELIVERY_DETAIL_ID = WDA.DELIVERY_DETAIL_ID
542 AND WDD.CONTAINER_FLAG = 'Y'
543 AND WDA.PARENT_DELIVERY_DETAIL_ID IS NULL
544 AND WDA.DELIVERY_ID is not null
545 AND WDA.DELIVERY_ID = C_Q1_DELIVERY_ID;
546 NUM_OF_BOXES NUMBER;
547 BEGIN
548 IF (NUM_LPN IS NULL) THEN
549 OPEN BOXES;
550 FETCH BOXES
551 INTO NUM_OF_BOXES;
552 CLOSE BOXES;
553 RETURN (NUM_OF_BOXES);
554 ELSE
555 RETURN (NUM_LPN);
556 END IF;
557 END;
558 RETURN NULL;
559 END CF_NUM_OF_LPNSFORMULA;
560 FUNCTION CF_BILL_TO_LOC1FORMULA(CF_OE_LINE_ID IN NUMBER
561 ,F_OE_LINE_ID IN NUMBER) RETURN NUMBER IS
562 BEGIN
563 DECLARE
564
565 -- RTV Changes
566 CURSOR C_RTV_BILL_TO_SITE IS
567 SELECT VENDOR_SITE_ID
568 FROM WSH_DELIVERY_DETAILS WDD,
569 RCV_TRANSACTIONS_INTERFACE RTI
570 WHERE WDD.SOURCE_CODE = 'RTV'
571 AND WDD.SOURCE_LINE_ID = F_OE_LINE_ID
572 AND WDD.SOURCE_LINE_ID = RTI.INTERFACE_TRANSACTION_ID
573 AND ROWNUM = 1
574 UNION
575 SELECT VENDOR_SITE_ID
576 FROM WSH_DELIVERY_DETAILS WDD,
577 RCV_TRANSACTIONS RT
578 WHERE WDD.SOURCE_CODE = 'RTV'
579 AND WDD.SOURCE_LINE_ID = F_OE_LINE_ID
580 AND WDD.DELIVERY_DETAIL_ID = RT.INTERFACE_SOURCE_LINE_ID
581 AND WDD.RELEASED_STATUS = 'C'
582 AND WDD.INV_INTERFACED_FLAG = 'Y'
583 AND RT.TRANSACTION_TYPE = 'RETURN TO VENDOR'
584 AND ROWNUM = 1;
585
586 -- RTV Changes
587 PO_SHIP_LINE_ID NUMBER;
588 BILL_TO_SITE_ID NUMBER;
589 BILL_TO_LOC_ID NUMBER;
590 BEGIN
591 /*SRW.REFERENCE(CF_OE_LINE_ID)*/NULL;
592 IF CP_SOURCE_CODE = 'OE' THEN
593 SELECT
594 PS.LOCATION_ID
595 INTO BILL_TO_LOC_ID
596 FROM
597 HZ_PARTY_SITES PS,
598 HZ_CUST_ACCT_SITES_ALL CA,
599 HZ_CUST_SITE_USES_ALL SU,
600 OE_ORDER_LINES_ALL OLA
601 WHERE OLA.LINE_ID = CF_OE_LINE_ID
602 AND SU.SITE_USE_ID = OLA.INVOICE_TO_ORG_ID
603 AND SU.CUST_ACCT_SITE_ID = CA.CUST_ACCT_SITE_ID
604 AND CA.PARTY_SITE_ID = PS.PARTY_SITE_ID;
605 IF SQL%NOTFOUND THEN
606 RETURN (NULL);
607 END IF;
608 ELSIF CP_SOURCE_CODE = 'OKE' THEN
609 BILL_TO_LOC_ID := OKE_DTS_WSH_PKG.BILL_TO_LOCATION(F_OE_LINE_ID);
610 --RTV changes
611 ELSIF CP_SOURCE_CODE = 'RTV' THEN
612 BEGIN
613 SELECT SHIP_TO_LOCATION_ID,
614 PO_SHIPMENT_LINE_ID
615 INTO BILL_TO_LOC_ID,
616 PO_SHIP_LINE_ID
617 FROM WSH_DELIVERY_DETAILS
618 WHERE SOURCE_CODE = 'RTV'
619 AND SOURCE_LINE_ID = F_OE_LINE_ID
620 AND ROWNUM = 1;
621 EXCEPTION
622 WHEN NO_DATA_FOUND THEN
623 RETURN (NULL);
624 END;
625
626 IF PO_SHIP_LINE_ID IS NOT NULL THEN
627 BILL_TO_LOC_ID := NULL;
628
629 OPEN C_RTV_BILL_TO_SITE;
630 FETCH C_RTV_BILL_TO_SITE INTO BILL_TO_SITE_ID;
631 CLOSE C_RTV_BILL_TO_SITE;
632
633 IF BILL_TO_SITE_ID IS NOT NULL THEN
634 SELECT HPS.LOCATION_ID
635 INTO BILL_TO_LOC_ID
636 FROM PO_VENDOR_SITES_ALL PVS,
637 HZ_PARTY_SITES HPS
638 WHERE PVS.VENDOR_SITE_ID = BILL_TO_SITE_ID
639 AND PVS.PARTY_SITE_ID = HPS.PARTY_SITE_ID;
640 END IF;
641 END IF;
642 END IF;
643 RETURN (BILL_TO_LOC_ID);
644 EXCEPTION
645 WHEN NO_DATA_FOUND THEN
646 RETURN (NULL);
647 WHEN OTHERS THEN
648 RAISE;
649 END;
650 END CF_BILL_TO_LOC1FORMULA;
651 FUNCTION CF_OE_LINE_ID1FORMULA RETURN NUMBER IS
652 BEGIN
653 DECLARE
654 OE_LINE_ID NUMBER;
655 SHP_TO_CONTACT_ID NUMBER;
656 BILL_TO_CONTACT_ID NUMBER;
657 BEGIN
658 /*SRW.REFERENCE(P_DELIVERY_ID)*/NULL;
659 IF CP_SOURCE_CODE = 'OE' THEN
660 SELECT
661 WDD.SOURCE_LINE_ID,
662 OLA.SHIP_TO_CONTACT_ID,
663 OLA.INVOICE_TO_CONTACT_ID
664 INTO OE_LINE_ID,SHP_TO_CONTACT_ID,BILL_TO_CONTACT_ID
665 FROM
666 WSH_DELIVERY_DETAILS WDD,
667 WSH_DELIVERY_ASSIGNMENTS_V WDA,
668 OE_ORDER_LINES_ALL OLA
669 WHERE WDD.DELIVERY_DETAIL_ID = WDA.DELIVERY_DETAIL_ID
670 AND NVL(WDD.CONTAINER_FLAG
671 ,'N') = 'N'
672 AND WDA.DELIVERY_ID = P_DELIVERY_ID
673 AND WDA.DELIVERY_ID is not null
674 AND WDD.SOURCE_CODE = 'OE'
675 AND WDD.SOURCE_LINE_ID = OLA.LINE_ID
676 AND ROWNUM < 2;
677 IF SQL%NOTFOUND THEN
678 CP_SHIP_TO_CONTACT_ID := NULL;
679 CP_BILL_TO_CONTACT_ID := NULL;
680 RETURN (NULL);
681 END IF;
682 CP_SHIP_TO_CONTACT_ID := SHP_TO_CONTACT_ID;
683 CP_BILL_TO_CONTACT_ID := BILL_TO_CONTACT_ID;
684 ELSIF CP_SOURCE_CODE = 'OKE' THEN
685 SELECT
686 WDD.SOURCE_LINE_ID
687 INTO OE_LINE_ID
688 FROM
689 WSH_DELIVERY_DETAILS WDD,
690 WSH_DELIVERY_ASSIGNMENTS_V WDA,
691 OE_ORDER_LINES_ALL OLA
692 WHERE WDD.DELIVERY_DETAIL_ID = WDA.DELIVERY_DETAIL_ID
693 AND WDD.CONTAINER_FLAG = 'N'
694 AND WDA.DELIVERY_ID = P_DELIVERY_ID
695 AND WDA.DELIVERY_ID is not null
696 AND WDD.SOURCE_CODE = 'OKE'
697 AND ROWNUM < 2;
698 CP_SHIP_TO_CONTACT_ID := NULL;
699 CP_BILL_TO_CONTACT_ID := NULL;
700 END IF;
701 RETURN (OE_LINE_ID);
702 EXCEPTION
703 WHEN NO_DATA_FOUND THEN
704 CP_SHIP_TO_CONTACT_ID := NULL;
705 CP_BILL_TO_CONTACT_ID := NULL;
706 RETURN (NULL);
707 WHEN OTHERS THEN
708 RAISE;
709 END;
710 END CF_OE_LINE_ID1FORMULA;
711 FUNCTION CF_BILL_TO_CONTACT1FORMULA RETURN CHAR IS
712 CONTACT_NAME HZ_PARTIES.PARTY_NAME%TYPE;
713 L_PERSON_TITLE HZ_PARTIES.PERSON_TITLE%TYPE;
714 L_PERSON_TITLE_UP HZ_PARTIES.PERSON_TITLE%TYPE;
715 L_LOOKUP_TYPE VARCHAR2(20);
716 BEGIN
717 /*SRW.REFERENCE(CP_BILL_TO_CONTACT_ID)*/NULL;
718 IF (CP_BILL_TO_CONTACT_ID IS NOT NULL) THEN
719 SELECT
720 PARTY.PARTY_NAME,
721 NVL(PARTY.PERSON_PRE_NAME_ADJUNCT
722 ,PARTY.PERSON_TITLE) TITLE
723 INTO CONTACT_NAME,L_PERSON_TITLE
724 FROM
725 HZ_CUST_ACCOUNT_ROLES ACCT_ROLE,
726 HZ_PARTIES PARTY,
727 HZ_RELATIONSHIPS REL,
728 HZ_ORG_CONTACTS ORG_CONT,
729 HZ_PARTIES REL_PARTY
730 WHERE ACCT_ROLE.CUST_ACCOUNT_ROLE_ID = CP_BILL_TO_CONTACT_ID
731 AND ACCT_ROLE.PARTY_ID = REL.PARTY_ID
732 AND ACCT_ROLE.ROLE_TYPE = 'CONTACT'
733 AND REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
734 AND REL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
735 AND REL.DIRECTIONAL_FLAG = 'F'
736 AND ORG_CONT.PARTY_RELATIONSHIP_ID = REL.RELATIONSHIP_ID
737 AND REL.SUBJECT_ID = PARTY.PARTY_ID
738 AND REL.PARTY_ID = REL_PARTY.PARTY_ID;
739 IF L_PERSON_TITLE IS NOT NULL THEN
740 BEGIN
741 L_LOOKUP_TYPE := 'RESPONSIBILITY';
742 L_PERSON_TITLE_UP := UPPER(L_PERSON_TITLE);
743 SELECT
744 MEANING || ' ' || CONTACT_NAME
745 INTO CONTACT_NAME
746 FROM
747 AR_LOOKUPS
748 WHERE LOOKUP_CODE = L_PERSON_TITLE_UP
749 AND LOOKUP_TYPE = L_LOOKUP_TYPE;
750 EXCEPTION
751 WHEN OTHERS THEN
752 CONTACT_NAME := L_PERSON_TITLE || ' ' || CONTACT_NAME;
753 END;
754 END IF;
755 ELSE
756 CONTACT_NAME := ' ';
757 END IF;
758 RETURN (CONTACT_NAME);
759 EXCEPTION
760 WHEN NO_DATA_FOUND THEN
761 CONTACT_NAME := ' ';
762 RETURN (CONTACT_NAME);
763 WHEN OTHERS THEN
764 RAISE;
765 END CF_BILL_TO_CONTACT1FORMULA;
766 FUNCTION CF_SHIP_TO_CONTACT1FORMULA RETURN CHAR IS
767 CONTACT_NAME HZ_PARTIES.PARTY_NAME%TYPE;
768 L_PERSON_TITLE HZ_PARTIES.PERSON_TITLE%TYPE;
769 L_PERSON_TITLE_UP HZ_PARTIES.PERSON_TITLE%TYPE;
770 L_LOOKUP_TYPE VARCHAR2(20);
771 BEGIN
772 /*SRW.REFERENCE(CP_SHIP_TO_CONTACT_ID)*/NULL;
773 IF (CP_SHIP_TO_CONTACT_ID IS NOT NULL) THEN
774 SELECT
775 PARTY.PARTY_NAME,
776 NVL(PARTY.PERSON_PRE_NAME_ADJUNCT
777 ,PARTY.PERSON_TITLE) TITLE
778 INTO CONTACT_NAME,L_PERSON_TITLE
779 FROM
780 HZ_CUST_ACCOUNT_ROLES ACCT_ROLE,
781 HZ_PARTIES PARTY,
782 HZ_RELATIONSHIPS REL,
783 HZ_ORG_CONTACTS ORG_CONT,
784 HZ_PARTIES REL_PARTY
785 WHERE ACCT_ROLE.CUST_ACCOUNT_ROLE_ID = CP_SHIP_TO_CONTACT_ID
786 AND ACCT_ROLE.PARTY_ID = REL.PARTY_ID
787 AND ACCT_ROLE.ROLE_TYPE = 'CONTACT'
788 AND REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
789 AND REL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
790 AND REL.DIRECTIONAL_FLAG = 'F'
791 AND ORG_CONT.PARTY_RELATIONSHIP_ID = REL.RELATIONSHIP_ID
792 AND REL.SUBJECT_ID = PARTY.PARTY_ID
793 AND REL.PARTY_ID = REL_PARTY.PARTY_ID;
794 IF L_PERSON_TITLE IS NOT NULL THEN
795 BEGIN
796 L_LOOKUP_TYPE := 'RESPONSIBILITY';
797 L_PERSON_TITLE_UP := UPPER(L_PERSON_TITLE);
798 SELECT
799 MEANING || ' ' || CONTACT_NAME
800 INTO CONTACT_NAME
801 FROM
802 AR_LOOKUPS
803 WHERE LOOKUP_CODE = L_PERSON_TITLE_UP
804 AND LOOKUP_TYPE = L_LOOKUP_TYPE;
805 EXCEPTION
806 WHEN OTHERS THEN
807 CONTACT_NAME := L_PERSON_TITLE || ' ' || CONTACT_NAME;
808 END;
809 END IF;
810 ELSE
811 CONTACT_NAME := ' ';
812 END IF;
813 RETURN (CONTACT_NAME);
814 EXCEPTION
815 WHEN NO_DATA_FOUND THEN
816 CONTACT_NAME := ' ';
817 RETURN (CONTACT_NAME);
818 WHEN OTHERS THEN
819 RAISE;
820 END CF_SHIP_TO_CONTACT1FORMULA;
821 FUNCTION F_BILL_TO_CUST_NAME1FORMULA(F_OE_LINE_ID IN NUMBER) RETURN CHAR IS
822 BEGIN
823 DECLARE
824 BILL_TO_CUST_NAME HZ_PARTIES.PARTY_NAME%TYPE;
825 L_PERSON_TITLE HZ_PARTIES.PERSON_TITLE%TYPE;
826 L_PERSON_TITLE_UP HZ_PARTIES.PERSON_TITLE%TYPE;
827 L_LOOKUP_TYPE VARCHAR2(20);
828 BEGIN
829 /*SRW.REFERENCE(F_OE_LINE_ID)*/NULL;
830 IF CP_SOURCE_CODE = 'OE' THEN
831 SELECT
832 HP.PARTY_NAME,
833 NVL(HP.PERSON_PRE_NAME_ADJUNCT
834 ,HP.PERSON_TITLE) TITLE
835 INTO BILL_TO_CUST_NAME,L_PERSON_TITLE
836 FROM
837 HZ_PARTY_SITES PS,
838 HZ_CUST_ACCT_SITES_ALL CA,
839 HZ_CUST_SITE_USES_ALL SU,
840 HZ_PARTIES HP,
841 OE_ORDER_LINES_ALL OLA
842 WHERE OLA.LINE_ID = F_OE_LINE_ID
843 AND SU.SITE_USE_ID = OLA.INVOICE_TO_ORG_ID
844 AND SU.CUST_ACCT_SITE_ID = CA.CUST_ACCT_SITE_ID
845 AND CA.PARTY_SITE_ID = PS.PARTY_SITE_ID
846 AND HP.PARTY_ID = PS.PARTY_ID;
847 IF L_PERSON_TITLE IS NOT NULL THEN
848 BEGIN
849 L_LOOKUP_TYPE := 'RESPONSIBILITY';
850 L_PERSON_TITLE_UP := UPPER(L_PERSON_TITLE);
851 SELECT
852 MEANING || ' ' || BILL_TO_CUST_NAME
853 INTO BILL_TO_CUST_NAME
854 FROM
855 AR_LOOKUPS
856 WHERE LOOKUP_CODE = L_PERSON_TITLE_UP
857 AND LOOKUP_TYPE = L_LOOKUP_TYPE;
858 EXCEPTION
859 WHEN OTHERS THEN
860 BILL_TO_CUST_NAME := L_PERSON_TITLE || ' ' || BILL_TO_CUST_NAME;
861 END;
862 END IF;
863 IF SQL%NOTFOUND THEN
864 RETURN (NULL);
865 END IF;
866 ELSIF CP_SOURCE_CODE = 'OKE' THEN
867 BILL_TO_CUST_NAME := '';
868 --RTV changes
869 ELSIF CP_SOURCE_CODE = 'RTV' THEN
870 SELECT
871 HP.PARTY_NAME,
872 NVL(HP.PERSON_PRE_NAME_ADJUNCT
873 ,HP.PERSON_TITLE) TITLE
874 INTO BILL_TO_CUST_NAME,L_PERSON_TITLE
875 FROM
876 WSH_DELIVERY_DETAILS WDD,
877 PO_VENDORS PV,
878 HZ_PARTIES HP
879 WHERE WDD.SOURCE_LINE_ID = F_OE_LINE_ID
880 AND WDD.SOURCE_CODE = 'RTV'
881 AND WDD.CUSTOMER_ID = PV.VENDOR_ID
882 AND PV.PARTY_ID = HP.PARTY_ID
883 AND ROWNUM = 1;
884
885 IF L_PERSON_TITLE IS NOT NULL THEN
886 BEGIN
887 L_LOOKUP_TYPE := 'RESPONSIBILITY';
888 L_PERSON_TITLE_UP := UPPER(L_PERSON_TITLE);
889 SELECT
890 MEANING || ' ' || BILL_TO_CUST_NAME
891 INTO BILL_TO_CUST_NAME
892 FROM
893 AR_LOOKUPS
894 WHERE LOOKUP_CODE = L_PERSON_TITLE_UP
895 AND LOOKUP_TYPE = L_LOOKUP_TYPE;
896 EXCEPTION
897 WHEN OTHERS THEN
898 BILL_TO_CUST_NAME := L_PERSON_TITLE || ' ' || BILL_TO_CUST_NAME;
899 END;
900 END IF;
901 --RTV changes
902 END IF;
903 RETURN (BILL_TO_CUST_NAME);
904 EXCEPTION
905 WHEN NO_DATA_FOUND THEN
906 RETURN (NULL);
907 WHEN OTHERS THEN
908 RAISE;
909 END;
910 END F_BILL_TO_CUST_NAME1FORMULA;
911 FUNCTION CF_LINE_TAX_CODE1FORMULA(F_OE_LINE_ID IN NUMBER) RETURN CHAR IS
912 BEGIN
913 DECLARE
914 L_LINE_TAX_CODE OE_ORDER_LINES_ALL.TAX_CODE%TYPE;
915 BEGIN
916 /*SRW.REFERENCE(F_OE_LINE_ID)*/NULL;
917 IF CP_SOURCE_CODE = 'OKE' THEN
918 L_LINE_TAX_CODE := '';
919 ELSIF CP_SOURCE_CODE = 'OE' THEN
920 SELECT
921 TAX_CODE
922 INTO L_LINE_TAX_CODE
923 FROM
924 OE_ORDER_LINES_ALL
925 WHERE LINE_ID = F_OE_LINE_ID;
926 IF SQL%NOTFOUND THEN
927 RETURN (NULL);
928 END IF;
929 END IF;
930 RETURN (L_LINE_TAX_CODE);
931 EXCEPTION
932 WHEN NO_DATA_FOUND THEN
933 RETURN (NULL);
934 WHEN OTHERS THEN
935 RAISE;
936 END;
937 END CF_LINE_TAX_CODE1FORMULA;
938 FUNCTION F_SHIP_TO_CUST_NAME1FORMULA(F_DEL_DETAIL_ID IN NUMBER) RETURN CHAR IS
939 BEGIN
940 DECLARE
941 SHIP_TO_CUST_NAME HZ_PARTIES.PARTY_NAME%TYPE;
942 L_PERSON_TITLE HZ_PARTIES.PERSON_TITLE%TYPE;
943 L_PERSON_TITLE_UP HZ_PARTIES.PERSON_TITLE%TYPE;
944 L_LOOKUP_TYPE VARCHAR2(20);
945 SHP_TO_SITE_USE_ID NUMBER;
946 CONS_FLAG VARCHAR2(1);
947 BEGIN
948 /*SRW.REFERENCE(F_SHIP_TO_SITE_USE_ID)*/NULL;
949 --RTV changes
950 SELECT
951 SHIP_TO_SITE_USE_ID,
952 CONSIGNEE_FLAG
953 INTO SHP_TO_SITE_USE_ID,CONS_FLAG
954 FROM
955 WSH_DELIVERY_DETAILS
956 WHERE DELIVERY_DETAIL_ID = F_DEL_DETAIL_ID
957 AND SOURCE_CODE = CP_SOURCE_CODE
958 AND ROWNUM < 2;
959
960 IF nvl(CONS_FLAG,'C') = 'V' THEN
961 SELECT
962 HP.PARTY_NAME,
963 NVL(HP.PERSON_PRE_NAME_ADJUNCT
964 ,HP.PERSON_TITLE) TITLE
965 INTO SHIP_TO_CUST_NAME,L_PERSON_TITLE
966 FROM
967 HZ_PARTY_SITE_USES PSU,
968 HZ_PARTY_SITES PS,
969 HZ_PARTIES HP
970 WHERE PSU.PARTY_SITE_USE_ID = SHP_TO_SITE_USE_ID
971 AND PSU.SITE_USE_TYPE = 'PURCHASING'
972 AND PS.PARTY_SITE_ID = PSU.PARTY_SITE_ID
973 AND HP.PARTY_ID = PS.PARTY_ID;
974 --RTV changes
975 ELSE
976 SELECT
977 HP.PARTY_NAME,
978 NVL(HP.PERSON_PRE_NAME_ADJUNCT
979 ,HP.PERSON_TITLE) TITLE
980 INTO SHIP_TO_CUST_NAME,L_PERSON_TITLE
981 FROM
982 HZ_PARTY_SITES PS,
983 HZ_CUST_ACCT_SITES_ALL CA,
984 HZ_CUST_SITE_USES_ALL SU,
985 HZ_PARTIES HP
986 WHERE SU.SITE_USE_ID = SHP_TO_SITE_USE_ID --RTV changes
987 AND SU.CUST_ACCT_SITE_ID = CA.CUST_ACCT_SITE_ID
988 AND CA.PARTY_SITE_ID = PS.PARTY_SITE_ID
989 AND HP.PARTY_ID = PS.PARTY_ID;
990 END IF;
991
992 IF L_PERSON_TITLE IS NOT NULL THEN
993 BEGIN
994 L_LOOKUP_TYPE := 'RESPONSIBILITY';
995 L_PERSON_TITLE_UP := UPPER(L_PERSON_TITLE);
996 SELECT
997 MEANING || ' ' || SHIP_TO_CUST_NAME
998 INTO SHIP_TO_CUST_NAME
999 FROM
1000 AR_LOOKUPS
1001 WHERE LOOKUP_CODE = L_PERSON_TITLE_UP
1002 AND LOOKUP_TYPE = L_LOOKUP_TYPE;
1003 EXCEPTION
1004 WHEN OTHERS THEN
1005 SHIP_TO_CUST_NAME := L_PERSON_TITLE || ' ' || SHIP_TO_CUST_NAME;
1006 END;
1007 END IF;
1008 IF SQL%NOTFOUND THEN
1009 RETURN (NULL);
1010 END IF;
1011 RETURN (SHIP_TO_CUST_NAME);
1012 EXCEPTION
1013 WHEN NO_DATA_FOUND THEN
1014 RETURN (NULL);
1015 WHEN OTHERS THEN
1016 RAISE;
1017 END;
1018 END F_SHIP_TO_CUST_NAME1FORMULA;
1019 FUNCTION F_SHIP_TO_SITE_USE_ID1FORMULA(F_DEL_DETAIL_ID IN NUMBER) RETURN NUMBER IS
1020 BEGIN
1021 DECLARE
1022 SHP_TO_SITE_USE_ID NUMBER;
1023 BEGIN
1024 /*SRW.REFERENCE(F_DEL_DETAIL_ID)*/NULL;
1025 IF CP_SOURCE_CODE = 'OE' THEN
1026 SELECT
1027 SHIP_TO_SITE_USE_ID
1028 INTO SHP_TO_SITE_USE_ID
1029 FROM
1030 WSH_DELIVERY_DETAILS
1031 WHERE DELIVERY_DETAIL_ID = F_DEL_DETAIL_ID
1032 AND SOURCE_CODE = 'OE'
1033 AND ROWNUM < 2;
1034 IF SQL%NOTFOUND THEN
1035 RETURN (NULL);
1036 END IF;
1037 ELSIF CP_SOURCE_CODE = 'OKE' THEN
1038 SELECT
1039 SHIP_TO_SITE_USE_ID
1040 INTO SHP_TO_SITE_USE_ID
1041 FROM
1042 WSH_DELIVERY_DETAILS
1043 WHERE DELIVERY_DETAIL_ID = F_DEL_DETAIL_ID
1044 AND SOURCE_CODE = 'OKE'
1045 AND ROWNUM < 2;
1046 END IF;
1047 RETURN (SHP_TO_SITE_USE_ID);
1048 EXCEPTION
1049 WHEN NO_DATA_FOUND THEN
1050 RETURN (NULL);
1051 WHEN OTHERS THEN
1052 RAISE;
1053 END;
1054 END F_SHIP_TO_SITE_USE_ID1FORMULA;
1055 FUNCTION F_BILL_TO_LOC_ID1FORMULA(F_OE_LINE_ID IN NUMBER) RETURN NUMBER IS
1056 BEGIN
1057 DECLARE
1058 -- RTV Changes
1059 CURSOR C_RTV_BILL_TO_SITE IS
1060 SELECT VENDOR_SITE_ID
1061 FROM WSH_DELIVERY_DETAILS WDD,
1062 RCV_TRANSACTIONS_INTERFACE RTI
1063 WHERE WDD.SOURCE_CODE = 'RTV'
1064 AND WDD.SOURCE_LINE_ID = F_OE_LINE_ID
1065 AND WDD.SOURCE_LINE_ID = RTI.INTERFACE_TRANSACTION_ID
1066 AND ROWNUM = 1
1067 UNION
1068 SELECT VENDOR_SITE_ID
1069 FROM WSH_DELIVERY_DETAILS WDD,
1070 RCV_TRANSACTIONS RT
1071 WHERE WDD.SOURCE_CODE = 'RTV'
1072 AND WDD.SOURCE_LINE_ID = F_OE_LINE_ID
1073 AND WDD.DELIVERY_DETAIL_ID = RT.INTERFACE_SOURCE_LINE_ID
1074 AND WDD.RELEASED_STATUS = 'C'
1075 AND WDD.INV_INTERFACED_FLAG = 'Y'
1076 AND RT.TRANSACTION_TYPE = 'RETURN TO VENDOR'
1077 AND ROWNUM = 1;
1078
1079 -- RTV Changes
1080 PO_SHIP_LINE_ID NUMBER;
1081 BILL_TO_SITE_ID NUMBER;
1082 BILL_TO_LOC_ID NUMBER;
1083 BEGIN
1084 /*SRW.REFERENCE(F_OE_LINE_ID)*/NULL;
1085 IF CP_SOURCE_CODE = 'OE' THEN
1086 SELECT
1087 PS.LOCATION_ID
1088 INTO BILL_TO_LOC_ID
1089 FROM
1090 HZ_PARTY_SITES PS,
1091 HZ_CUST_ACCT_SITES_ALL CA,
1092 HZ_CUST_SITE_USES_ALL SU,
1093 OE_ORDER_LINES_ALL OLA
1094 WHERE OLA.LINE_ID = F_OE_LINE_ID
1095 AND SU.SITE_USE_ID = OLA.INVOICE_TO_ORG_ID
1096 AND SU.CUST_ACCT_SITE_ID = CA.CUST_ACCT_SITE_ID
1097 AND CA.PARTY_SITE_ID = PS.PARTY_SITE_ID;
1098 IF SQL%NOTFOUND THEN
1099 RETURN (NULL);
1100 END IF;
1101 ELSIF CP_SOURCE_CODE = 'OKE' THEN
1102 BILL_TO_LOC_ID := OKE_DTS_WSH_PKG.BILL_TO_LOCATION(F_OE_LINE_ID);
1103 --RTV changes
1104 ELSIF CP_SOURCE_CODE = 'RTV' THEN
1105 BEGIN
1106 SELECT SHIP_TO_LOCATION_ID,
1107 PO_SHIPMENT_LINE_ID
1108 INTO BILL_TO_LOC_ID,
1109 PO_SHIP_LINE_ID
1110 FROM WSH_DELIVERY_DETAILS
1111 WHERE SOURCE_CODE = 'RTV'
1112 AND SOURCE_LINE_ID = F_OE_LINE_ID
1113 AND ROWNUM = 1;
1114 EXCEPTION
1115 WHEN NO_DATA_FOUND THEN
1116 RETURN (NULL);
1117 END;
1118
1119 IF PO_SHIP_LINE_ID IS NOT NULL THEN
1120 BILL_TO_LOC_ID := NULL;
1121
1122 OPEN C_RTV_BILL_TO_SITE;
1123 FETCH C_RTV_BILL_TO_SITE INTO BILL_TO_SITE_ID;
1124 CLOSE C_RTV_BILL_TO_SITE;
1125
1126 IF BILL_TO_SITE_ID IS NOT NULL THEN
1127 SELECT HPS.LOCATION_ID
1128 INTO BILL_TO_LOC_ID
1129 FROM PO_VENDOR_SITES_ALL PVS,
1130 HZ_PARTY_SITES HPS
1131 WHERE PVS.VENDOR_SITE_ID = BILL_TO_SITE_ID
1132 AND PVS.PARTY_SITE_ID = HPS.PARTY_SITE_ID;
1133 END IF;
1134 END IF;
1135 END IF;
1136 RETURN (BILL_TO_LOC_ID);
1137 EXCEPTION
1138 WHEN NO_DATA_FOUND THEN
1139 RETURN (NULL);
1140 WHEN OTHERS THEN
1141 RAISE;
1142 END;
1143 END F_BILL_TO_LOC_ID1FORMULA;
1144 FUNCTION F_OE_LINE_ID1FORMULA RETURN NUMBER IS
1145 BEGIN
1146 DECLARE
1147 OE_LINE_ID NUMBER;
1148 SHP_TO_CONTACT_ID NUMBER;
1149 BILL_TO_CONTACT_ID NUMBER;
1150 BEGIN
1151 /*SRW.REFERENCE(P_DELIVERY_ID)*/NULL;
1152 IF CP_SOURCE_CODE = 'OE' THEN
1153 SELECT
1154 WDD.SOURCE_LINE_ID,
1155 OLA.SHIP_TO_CONTACT_ID,
1156 OLA.INVOICE_TO_CONTACT_ID
1157 INTO OE_LINE_ID,SHP_TO_CONTACT_ID,BILL_TO_CONTACT_ID
1158 FROM
1159 WSH_DELIVERY_DETAILS WDD,
1160 WSH_DELIVERY_ASSIGNMENTS_V WDA,
1161 OE_ORDER_LINES_ALL OLA
1162 WHERE WDD.DELIVERY_DETAIL_ID = WDA.DELIVERY_DETAIL_ID
1163 AND NVL(WDD.CONTAINER_FLAG
1164 ,'N') = 'N'
1165 AND WDA.DELIVERY_ID = P_DELIVERY_ID
1166 AND WDA.DELIVERY_ID is not null
1167 AND WDD.SOURCE_CODE = 'OE'
1168 AND WDD.SOURCE_LINE_ID = OLA.LINE_ID
1169 AND ROWNUM < 2;
1170 IF SQL%NOTFOUND THEN
1171 CP_SHIP_TO_CONTACT_ID := NULL;
1172 CP_BILL_TO_CONTACT_ID := NULL;
1173 RETURN (NULL);
1174 END IF;
1175 CP_SHIP_TO_CONTACT_ID := SHP_TO_CONTACT_ID;
1176 CP_BILL_TO_CONTACT_ID := BILL_TO_CONTACT_ID;
1177 ELSIF CP_SOURCE_CODE = 'OKE' THEN
1178 SELECT
1179 WDD.SOURCE_LINE_ID
1180 INTO OE_LINE_ID
1181 FROM
1182 WSH_DELIVERY_DETAILS WDD,
1183 WSH_DELIVERY_ASSIGNMENTS_V WDA
1184 WHERE WDD.DELIVERY_DETAIL_ID = WDA.DELIVERY_DETAIL_ID
1185 AND WDD.CONTAINER_FLAG = 'N'
1186 AND WDA.DELIVERY_ID = P_DELIVERY_ID
1187 AND WDA.DELIVERY_ID is not null
1188 AND WDD.SOURCE_CODE = 'OKE'
1189 AND ROWNUM < 2;
1190 CP_SHIP_TO_CONTACT_ID := NULL;
1191 CP_BILL_TO_CONTACT_ID := NULL;
1192 --RTV changes
1193 ELSIF CP_SOURCE_CODE = 'RTV' THEN
1194 SELECT SOURCE_LINE_ID
1195 INTO OE_LINE_ID
1196 FROM
1197 WSH_DELIVERY_DETAILS WDD,
1198 WSH_DELIVERY_ASSIGNMENTS_V WDA
1199 WHERE WDD.DELIVERY_DETAIL_ID = WDA.DELIVERY_DETAIL_ID
1200 AND WDD.CONTAINER_FLAG = 'N'
1201 AND WDA.DELIVERY_ID = P_DELIVERY_ID
1202 AND WDA.DELIVERY_ID is not null
1203 AND WDD.SOURCE_CODE = 'RTV'
1204 AND ROWNUM < 2;
1205 CP_SHIP_TO_CONTACT_ID := NULL;
1206 CP_BILL_TO_CONTACT_ID := NULL;
1207 END IF;
1208 RETURN (OE_LINE_ID);
1209 EXCEPTION
1210 WHEN NO_DATA_FOUND THEN
1211 CP_SHIP_TO_CONTACT_ID := NULL;
1212 CP_BILL_TO_CONTACT_ID := NULL;
1213 RETURN (NULL);
1214 WHEN OTHERS THEN
1215 RAISE;
1216 END;
1217 END F_OE_LINE_ID1FORMULA;
1218 FUNCTION F_DEL_DETAIL_ID1FORMULA RETURN NUMBER IS
1219 BEGIN
1220 DECLARE
1221 DEL_DTL_ID NUMBER;
1222 L_COUNT NUMBER := 0;
1223 L_SOURCE_CODE VARCHAR2(30);
1224 BEGIN
1225 /*SRW.REFERENCE(P_DELIVERY_ID)*/NULL;
1226 SELECT
1227 count(distinct WDD.SOURCE_CODE)
1228 INTO L_COUNT
1229 FROM
1230 WSH_DELIVERY_DETAILS WDD,
1231 WSH_DELIVERY_ASSIGNMENTS_V WDA
1232 WHERE WDA.DELIVERY_ID IS NOT NULL
1233 AND WDA.DELIVERY_DETAIL_ID = WDD.DELIVERY_DETAIL_ID
1234 AND WDA.DELIVERY_ID = P_DELIVERY_ID
1235 AND WDD.CONTAINER_FLAG = 'N'
1236 GROUP BY
1237 WDD.SOURCE_CODE;
1238 IF L_COUNT > 1 THEN
1239 NULL;
1240 ELSIF L_COUNT = 1 THEN
1241 SELECT
1242 WDD.SOURCE_CODE,
1243 WDD.DELIVERY_DETAIL_ID
1244 INTO L_SOURCE_CODE,DEL_DTL_ID
1245 FROM
1246 WSH_DELIVERY_DETAILS WDD,
1247 WSH_DELIVERY_ASSIGNMENTS_V WDA
1248 WHERE WDD.DELIVERY_DETAIL_ID = WDA.DELIVERY_DETAIL_ID
1249 AND WDD.CONTAINER_FLAG = 'N'
1250 AND WDA.DELIVERY_ID = P_DELIVERY_ID
1251 AND WDA.DELIVERY_ID is not null
1252 AND ROWNUM < 2;
1253 CP_SOURCE_CODE := L_SOURCE_CODE;
1254 END IF;
1255 IF SQL%NOTFOUND THEN
1256 RETURN (NULL);
1257 END IF;
1258 RETURN (DEL_DTL_ID);
1259 EXCEPTION
1260 WHEN NO_DATA_FOUND THEN
1261 RETURN (NULL);
1262 WHEN OTHERS THEN
1263 RAISE;
1264 END;
1265 END F_DEL_DETAIL_ID1FORMULA;
1266 FUNCTION CF_TO_ADDR_4FORMULA(CF_TO_CITY_STATE_ZIP IN VARCHAR2
1267 ,C_TO_COUNTRY IN VARCHAR2
1268 ,C_TO_ADDRESS_2 IN VARCHAR2
1269 ,C_TO_ADDRESS_3 IN VARCHAR2
1270 ,C_TO_ADDRESS_4 IN VARCHAR2) RETURN CHAR IS
1271 L_CF_TO_CITY_STATE_ZIP VARCHAR2(190);
1272 BEGIN
1273 /*SRW.REFERENCE(CF_TO_CITY_STATE_ZIP)*/NULL;
1274 /*SRW.REFERENCE(C_TO_COUNTRY)*/NULL;
1275 /*SRW.REFERENCE(C_TO_ADDRESS_2)*/NULL;
1276 /*SRW.REFERENCE(C_TO_ADDRESS_3)*/NULL;
1277 IF (CF_TO_CITY_STATE_ZIP IS NULL) THEN
1278 L_CF_TO_CITY_STATE_ZIP := '';
1279 ELSE
1280 L_CF_TO_CITY_STATE_ZIP := CF_TO_CITY_STATE_ZIP || ', ';
1281 END IF;
1282 IF (C_TO_ADDRESS_2 IS NOT NULL AND C_TO_ADDRESS_3 IS NOT NULL AND C_TO_ADDRESS_4 IS NOT NULL) THEN
1283 RETURN (C_TO_ADDRESS_4);
1284 ELSIF (C_TO_ADDRESS_2 IS NOT NULL AND C_TO_ADDRESS_3 IS NULL AND C_TO_ADDRESS_4 IS NULL) THEN
1285 RETURN (' ');
1286 ELSIF (C_TO_ADDRESS_2 IS NOT NULL AND C_TO_ADDRESS_3 IS NULL AND C_TO_ADDRESS_4 IS NOT NULL) THEN
1287 RETURN (L_CF_TO_CITY_STATE_ZIP || C_TO_COUNTRY);
1288 ELSIF (C_TO_ADDRESS_2 IS NULL AND C_TO_ADDRESS_3 IS NOT NULL AND C_TO_ADDRESS_4 IS NOT NULL) THEN
1289 RETURN (L_CF_TO_CITY_STATE_ZIP || C_TO_COUNTRY);
1290 ELSIF (C_TO_ADDRESS_2 IS NOT NULL AND C_TO_ADDRESS_3 IS NOT NULL AND C_TO_ADDRESS_4 IS NULL) THEN
1291 RETURN (L_CF_TO_CITY_STATE_ZIP || C_TO_COUNTRY);
1292 ELSIF (C_TO_ADDRESS_2 IS NULL AND C_TO_ADDRESS_3 IS NOT NULL AND C_TO_ADDRESS_4 IS NULL) THEN
1293 RETURN (' ');
1294 ELSIF (C_TO_ADDRESS_2 IS NULL AND C_TO_ADDRESS_3 IS NULL AND C_TO_ADDRESS_4 IS NOT NULL) THEN
1295 RETURN (' ');
1296 ELSIF (C_TO_ADDRESS_2 IS NULL AND C_TO_ADDRESS_3 IS NULL AND C_TO_ADDRESS_4 IS NULL) THEN
1297 RETURN (' ');
1298 END IF;
1299 END CF_TO_ADDR_4FORMULA;
1300 FUNCTION CF_BILL_ADDR_4FORMULA(CF_BILL_CITY_STATE_ZIP IN VARCHAR2) RETURN CHAR IS
1301 BEGIN
1302 /*SRW.REFERENCE(CP_BILL_ADDRESS_LINE_2)*/NULL;
1303 /*SRW.REFERENCE(CP_BILL_ADDRESS_LINE_3)*/NULL;
1304 /*SRW.REFERENCE(CP_BILL_ADDRESS_LINE_4)*/NULL;
1305 /*SRW.REFERENCE(CF_BILL_CITY_STATE_ZIP)*/NULL;
1306 /*SRW.REFERENCE(CP_BILL_COUNTRY)*/NULL;
1307 IF (CP_BILL_ADDRESS_LINE_2 IS NOT NULL AND CP_BILL_ADDRESS_LINE_3 IS NOT NULL AND CP_BILL_ADDRESS_LINE_4 IS NOT NULL) THEN
1308 RETURN (CP_BILL_ADDRESS_LINE_4);
1309 ELSIF (CP_BILL_ADDRESS_LINE_2 IS NOT NULL AND CP_BILL_ADDRESS_LINE_3 IS NULL AND CP_BILL_ADDRESS_LINE_4 IS NOT NULL) THEN
1310 RETURN (CF_BILL_CITY_STATE_ZIP || ', ' || CP_BILL_COUNTRY);
1311 ELSIF (CP_BILL_ADDRESS_LINE_2 IS NOT NULL AND CP_BILL_ADDRESS_LINE_3 IS NULL AND CP_BILL_ADDRESS_LINE_4 IS NULL) THEN
1312 RETURN ('');
1313 ELSIF (CP_BILL_ADDRESS_LINE_2 IS NOT NULL AND CP_BILL_ADDRESS_LINE_3 IS NOT NULL AND CP_BILL_ADDRESS_LINE_4 IS NULL) THEN
1314 RETURN (CF_BILL_CITY_STATE_ZIP || ', ' || CP_BILL_COUNTRY);
1315 ELSIF (CP_BILL_ADDRESS_LINE_2 IS NULL AND CP_BILL_ADDRESS_LINE_3 IS NOT NULL AND CP_BILL_ADDRESS_LINE_4 IS NOT NULL) THEN
1316 RETURN (CF_BILL_CITY_STATE_ZIP || ', ' || CP_BILL_COUNTRY);
1317 ELSIF (CP_BILL_ADDRESS_LINE_2 IS NULL AND CP_BILL_ADDRESS_LINE_3 IS NULL AND CP_BILL_ADDRESS_LINE_4 IS NOT NULL) THEN
1318 RETURN ('');
1319 ELSIF (CP_BILL_ADDRESS_LINE_2 IS NULL AND CP_BILL_ADDRESS_LINE_3 IS NOT NULL AND CP_BILL_ADDRESS_LINE_4 IS NULL) THEN
1320 RETURN ('');
1321 ELSIF (CP_BILL_ADDRESS_LINE_2 IS NULL AND CP_BILL_ADDRESS_LINE_3 IS NULL AND CP_BILL_ADDRESS_LINE_4 IS NULL) THEN
1322 RETURN ('');
1323 END IF;
1324 END CF_BILL_ADDR_4FORMULA;
1325 FUNCTION CF_REQUESTOR_NAMEFORMULA(ATTACH_LINE_ID IN NUMBER) RETURN CHAR IS
1326 REQ_NAME VARCHAR2(240);
1327 ORDER_NUMBER VARCHAR2(40);
1328 BEGIN
1329 /* SELECT
1330 TO_CHAR(BH.ORDER_NUMBER),
1331 RE.FULL_NAME
1332 INTO ORDER_NUMBER,REQ_NAME
1333 FROM
1334 OE_ORDER_HEADERS_ALL BH,
1335 PO_REQUISITION_HEADERS_ALL RH,
1336 HR_EMPLOYEES RE
1337 WHERE RH.TYPE_LOOKUP_CODE = 'INTERNAL'
1338 AND RH.PREPARER_ID = RE.EMPLOYEE_ID
1339 AND BH.HEADER_ID = ATTACH_ORDER_ID
1340 AND BH.SOURCE_DOCUMENT_ID = RH.REQUISITION_HEADER_ID
1341 AND BH.ORDER_SOURCE_ID = 10;*/
1342
1343 select to_char(bh.order_number),re.full_name
1344 into order_number, req_name
1345 from oe_order_HEADERS_ALL bh,
1346 oe_order_lines_all ool,
1347 po_requisition_lines_all pll,
1348 HR_EMPLOYEES re
1349 where pll.SOURCE_TYPE_CODE = 'INVENTORY'
1350 AND pll.to_person_id = re.employee_id
1351 and bh.header_id = ool.header_id
1352 and ool.SOURCE_DOCUMENT_LINE_ID = pll.REQUISITION_LINE_ID
1353 and ool.SOURCE_DOCUMENT_ID = pll.REQUISITION_HEADER_ID
1354 and bh.order_source_id = 10
1355 and ool.line_id = ATTACH_LINE_ID ;
1356 CP_INTERNAL_SALES_ORDER := ORDER_NUMBER;
1357 RETURN REQ_NAME;
1358 EXCEPTION
1359 WHEN OTHERS THEN
1360 RETURN NULL;
1361 END CF_REQUESTOR_NAMEFORMULA;
1362 FUNCTION CF_ITEM_DESCRIPTIONFORMULA(C_ITEM_DESCRIPTION IN VARCHAR2
1363 ,C_INV_ITEM_ID IN NUMBER
1364 ,C_ORGANIZATION_ID IN NUMBER) RETURN CHAR IS
1365 CURSOR INVENTORY_LABEL(ID IN NUMBER,ORG_ID IN NUMBER) IS
1366 SELECT
1367 DESCRIPTION
1368 FROM
1369 MTL_SYSTEM_ITEMS_VL
1370 WHERE INVENTORY_ITEM_ID = ID
1371 AND ORGANIZATION_ID = ORG_ID;
1372 L_ITEM_DESC VARCHAR2(250);
1373 BEGIN
1374 L_ITEM_DESC := C_ITEM_DESCRIPTION;
1375 IF (C_INV_ITEM_ID IS NOT NULL) THEN
1376 OPEN INVENTORY_LABEL(C_INV_ITEM_ID,C_ORGANIZATION_ID);
1377 FETCH INVENTORY_LABEL
1378 INTO L_ITEM_DESC;
1379 IF (INVENTORY_LABEL%NOTFOUND) THEN
1380 L_ITEM_DESC := C_ITEM_DESCRIPTION;
1381 END IF;
1382 CLOSE INVENTORY_LABEL;
1383 END IF;
1384 RETURN L_ITEM_DESC;
1385 END CF_ITEM_DESCRIPTIONFORMULA;
1386 FUNCTION CF_ITEM_DISPLAYFORMULA RETURN CHAR IS
1387 BEGIN
1388 RETURN (P_ITEM_DISPLAY);
1389 END CF_ITEM_DISPLAYFORMULA;
1390
1391 FUNCTION CF_DISPLAY_UNSHIPPEDFORMULA RETURN CHAR IS
1392 BEGIN
1393 --STANDALONE CHANGES
1394 IF P_STANDALONE = 'Y' THEN
1395 RETURN ('N');
1396 ELSE
1397 RETURN(p_display_unshipped);
1398 END IF;
1399 END CF_DISPLAY_UNSHIPPEDFORMULA;
1400
1401 FUNCTION CF_PRINT_CUST_ITEMFORMULA RETURN CHAR IS
1402 BEGIN
1403 RETURN (P_PRINT_CUST_ITEM);
1404 END CF_PRINT_CUST_ITEMFORMULA;
1405 FUNCTION CF_CARRIER_ADDRFORMULA(WND_CARRIER_ID IN NUMBER
1406 ,C_DEL_ORG_ID IN NUMBER
1407 ,C_Q2_DELIVERY_ID IN NUMBER) RETURN CHAR IS
1408 CURSOR C_CARRIER_ADDRESS(C_ORGANIZATION_ID IN NUMBER,C_CARRIER_ID IN NUMBER,C_DELIVERY_ID IN NUMBER) IS
1409 SELECT
1410 LOC.ADDRESS1 || ', ' || LOC.ADDRESS2 || ', ' || LOC.ADDRESS3 || ', ' || LOC.ADDRESS4 || ', ' || LOC.CITY || ', ' || LOC.STATE || ', ' || LOC.COUNTRY || ', ' || LOC.POSTAL_CODE CARRIER_ADDR
1411 FROM
1412 WSH_ORG_CARRIER_SITES ORG_SITES,
1413 WSH_NEW_DELIVERIES DEL,
1414 HZ_PARTY_SITES HZ_SITES,
1415 HZ_LOCATIONS LOC
1416 WHERE ORG_SITES.ORGANIZATION_ID = DEL.ORGANIZATION_ID
1417 AND HZ_SITES.PARTY_SITE_ID = ORG_SITES.CARRIER_SITE_ID
1418 AND HZ_SITES.PARTY_ID = C_CARRIER_ID
1419 AND ORG_SITES.ENABLED_FLAG = 'Y'
1420 AND LOC.LOCATION_ID = HZ_SITES.LOCATION_ID
1421 AND DEL.DELIVERY_ID = C_DELIVERY_ID;
1422 L_CARRIER_ADDR VARCHAR2(2000);
1423 BEGIN
1424 IF (WND_CARRIER_ID IS NOT NULL) THEN
1425 FOR c_rec IN C_CARRIER_ADDRESS(c_organization_id =>c_del_org_id,c_carrier_id =>wnd_carrier_id,c_delivery_id =>c_q2_delivery_id)
1426 LOOP
1427 L_CARRIER_ADDR := C_REC.CARRIER_ADDR;
1428 END LOOP;
1429 ELSE
1430 L_CARRIER_ADDR := ' ';
1431 END IF;
1432 RETURN L_CARRIER_ADDR;
1433 END CF_CARRIER_ADDRFORMULA;
1434 FUNCTION CF_CARRIER_ADDRESS1FORMULA(TRIP_CARRIER_ID IN NUMBER
1435 ,C_TRIP_DELIVERY_ID IN NUMBER) RETURN CHAR IS
1436 CURSOR C_CARRIER_ADDRESS(C_CARRIER_ID IN NUMBER,C_DELIVERY_ID IN NUMBER) IS
1437 SELECT
1438 LOC.ADDRESS1 || ', ' || LOC.ADDRESS2 || ', ' || LOC.ADDRESS3 || ', ' || LOC.ADDRESS4 || ', ' || LOC.CITY || ', ' || LOC.STATE || ', ' || LOC.COUNTRY || ', ' || LOC.POSTAL_CODE CARRIER_ADDR
1439 FROM
1440 WSH_ORG_CARRIER_SITES ORG_SITES,
1441 WSH_NEW_DELIVERIES DEL,
1442 HZ_PARTY_SITES HZ_SITES,
1443 HZ_LOCATIONS LOC
1444 WHERE ORG_SITES.ORGANIZATION_ID = DEL.ORGANIZATION_ID
1445 AND HZ_SITES.PARTY_SITE_ID = ORG_SITES.CARRIER_SITE_ID
1446 AND HZ_SITES.PARTY_ID = C_CARRIER_ID
1447 AND ORG_SITES.ENABLED_FLAG = 'Y'
1448 AND LOC.LOCATION_ID = HZ_SITES.LOCATION_ID
1449 AND DEL.DELIVERY_ID = C_DELIVERY_ID;
1450 L_CARRIER_ADDR VARCHAR2(2000);
1451 CURSOR C_DEL_CARRIER(C_DELIVERY_ID IN NUMBER) IS
1452 SELECT
1453 WND.CARRIER_ID DEL_CARRIER_ID
1454 FROM
1455 WSH_NEW_DELIVERIES WND
1456 WHERE WND.DELIVERY_ID = C_DELIVERY_ID;
1457 BEGIN
1458 IF (TRIP_CARRIER_ID IS NOT NULL) THEN
1459 FOR c_rec IN C_CARRIER_ADDRESS(c_carrier_id => trip_carrier_id,c_delivery_id => c_trip_delivery_id)
1460 LOOP
1461 L_CARRIER_ADDR := C_REC.CARRIER_ADDR;
1462 EXIT;
1463 END LOOP;
1464 ELSE
1465 FOR c_rec IN C_DEL_CARRIER(c_delivery_id =>c_trip_delivery_id) LOOP
1466 IF (C_REC.DEL_CARRIER_ID IS NOT NULL) THEN
1467 FOR c_rec1 IN C_CARRIER_ADDRESS(c_carrier_id => c_rec.del_carrier_id,c_delivery_id =>c_trip_delivery_id) LOOP
1468 L_CARRIER_ADDR := C_REC1.CARRIER_ADDR;
1469 END LOOP;
1470 END IF;
1471 EXIT;
1472 END LOOP;
1473 IF (L_CARRIER_ADDR IS NULL) THEN
1474 L_CARRIER_ADDR := ' ';
1475 END IF;
1476 END IF;
1477 RETURN L_CARRIER_ADDR;
1478 END CF_CARRIER_ADDRESS1FORMULA;
1479 FUNCTION CF_VAT_REG_NUMFORMULA(C_Q2_DELIVERY_ID IN NUMBER
1480 ,C_DEL_ORG_ID IN NUMBER) RETURN VARCHAR2 IS
1481 L_VAT_REG_NUM VARCHAR2(60);
1482 L_TAX_REG_NUM VARCHAR2(60);
1483 L_RETURN_STATUS VARCHAR2(50);
1484 L_MSG_COUNT NUMBER;
1485 L_MSG_DATA VARCHAR2(50);
1486 L_INV_LE_INFO XLE_BUSINESSINFO_GRP.INV_ORG_REC_TYPE;
1487 L_LEGAL_ENTITY_ID NUMBER;
1488 L_REGISTRATION_NUMBER NUMBER;
1489 L_OPERATING_UNIT_ID NUMBER;
1490 CURSOR TAX_REG_NUM_CSR(P_ORG_ID IN NUMBER) IS
1491 SELECT
1492 RCODES.REPORTING_CODE_CHAR_VALUE
1493 FROM
1494 ZX_REPORTING_TYPES_B RTYPES,
1495 ZX_REPORT_CODES_ASSOC RCODES,
1496 ZX_PARTY_TAX_PROFILE PTP
1497 WHERE RCODES.ENTITY_CODE = 'ZX_PARTY_TAX_PROFILE'
1498 AND RCODES.ENTITY_ID = PTP.PARTY_TAX_PROFILE_ID
1499 AND RCODES.REPORTING_TYPE_ID = RTYPES.REPORTING_TYPE_ID
1500 AND RTYPES.REPORTING_TYPE_CODE = 'AR-SYSTEM-PARAM-REG-NUM'
1501 AND PTP.PARTY_TYPE_CODE = 'OU'
1502 AND PTP.PARTY_ID = P_ORG_ID;
1503 BEGIN
1504 L_OPERATING_UNIT_ID := WSH_UTIL_CORE.GET_OPERATINGUNIT_ID(C_Q2_DELIVERY_ID);
1505 OPEN TAX_REG_NUM_CSR(L_OPERATING_UNIT_ID);
1506 FETCH TAX_REG_NUM_CSR
1507 INTO L_TAX_REG_NUM;
1508 IF TAX_REG_NUM_CSR%NOTFOUND OR L_TAX_REG_NUM IS NULL THEN
1509 CLOSE TAX_REG_NUM_CSR;
1510 XLE_BUSINESSINFO_GRP.GET_INVORG_INFO(X_RETURN_STATUS => L_RETURN_STATUS
1511 ,X_MSG_DATA => L_MSG_DATA
1512 ,P_INVORG_ID => C_DEL_ORG_ID
1513 ,P_LE_ID => NULL
1514 ,P_PARTY_ID => NULL
1515 ,X_INV_LE_INFO => L_INV_LE_INFO);
1516 IF L_RETURN_STATUS <> 'S' OR L_INV_LE_INFO(1).LEGAL_ENTITY_ID IS NULL THEN
1517 L_VAT_REG_NUM := NULL;
1518 ELSE
1519 L_RETURN_STATUS := NULL;
1520 L_MSG_DATA := NULL;
1521 XLE_UTILITIES_GRP.GET_FP_VATREGISTRATION_LEID(P_API_VERSION => 1.0
1522 ,P_INIT_MSG_LIST => 'TRUE'
1523 ,P_COMMIT => NULL
1524 ,P_EFFECTIVE_DATE => SYSDATE
1525 ,X_RETURN_STATUS => L_RETURN_STATUS
1526 ,X_MSG_COUNT => L_MSG_COUNT
1527 ,X_MSG_DATA => L_MSG_DATA
1528 ,P_LEGAL_ENTITY_ID => L_INV_LE_INFO(1).LEGAL_ENTITY_ID
1529 ,X_REGISTRATION_NUMBER => L_REGISTRATION_NUMBER);
1530 L_VAT_REG_NUM := L_REGISTRATION_NUMBER;
1531 IF L_REGISTRATION_NUMBER IS NOT NULL THEN
1532 P_TAX_VAT_FLAG := 0;
1533 ELSE
1534 P_TAX_VAT_FLAG := 1;
1535 END IF;
1536 END IF;
1537 ELSE
1538 CLOSE TAX_REG_NUM_CSR;
1539 L_VAT_REG_NUM := L_TAX_REG_NUM;
1540 P_TAX_VAT_FLAG := 1;
1541 END IF;
1542 RETURN (L_VAT_REG_NUM);
1543 EXCEPTION
1544 WHEN OTHERS THEN
1545 IF TAX_REG_NUM_CSR%ISOPEN THEN
1546 CLOSE TAX_REG_NUM_CSR;
1547 END IF;
1548 END CF_VAT_REG_NUMFORMULA;
1549 FUNCTION CP_INTERNAL_SALES_ORDER_P RETURN VARCHAR2 IS
1550 BEGIN
1551 RETURN CP_INTERNAL_SALES_ORDER;
1552 END CP_INTERNAL_SALES_ORDER_P;
1553 FUNCTION CP_WAREHOUSE_NAME_P RETURN VARCHAR2 IS
1554 BEGIN
1555 RETURN CP_WAREHOUSE_NAME;
1556 END CP_WAREHOUSE_NAME_P;
1557 FUNCTION CP_DRAFT_OR_FINAL_P RETURN VARCHAR2 IS
1558 BEGIN
1559 RETURN CP_DRAFT_OR_FINAL;
1560 END CP_DRAFT_OR_FINAL_P;
1561 FUNCTION CP_PRINT_DATE_P RETURN DATE IS
1562 BEGIN
1563 RETURN CP_PRINT_DATE;
1564 END CP_PRINT_DATE_P;
1565 FUNCTION CP_RLM_PRINT_CUM_DATA_P RETURN VARCHAR2 IS
1566 BEGIN
1567 RETURN CP_RLM_PRINT_CUM_DATA;
1568 END CP_RLM_PRINT_CUM_DATA_P;
1569 FUNCTION CP_SOURCE_CODE_P RETURN VARCHAR2 IS
1570 BEGIN
1571 RETURN CP_SOURCE_CODE;
1572 END CP_SOURCE_CODE_P;
1573 FUNCTION CP_BILL_TO_CONTACT_ID_P RETURN NUMBER IS
1574 BEGIN
1575 RETURN CP_BILL_TO_CONTACT_ID;
1576 END CP_BILL_TO_CONTACT_ID_P;
1577 FUNCTION CP_SHIP_TO_CONTACT_ID_P RETURN NUMBER IS
1578 BEGIN
1579 RETURN CP_SHIP_TO_CONTACT_ID;
1580 END CP_SHIP_TO_CONTACT_ID_P;
1581 FUNCTION CP_BILL_ADDRESS_LINE_1_P RETURN VARCHAR2 IS
1582 BEGIN
1583 RETURN CP_BILL_ADDRESS_LINE_1;
1584 END CP_BILL_ADDRESS_LINE_1_P;
1585 FUNCTION CP_BILL_ADDRESS_LINE_2_P RETURN VARCHAR2 IS
1586 BEGIN
1587 RETURN CP_BILL_ADDRESS_LINE_2;
1588 END CP_BILL_ADDRESS_LINE_2_P;
1589 FUNCTION CP_BILL_ADDRESS_LINE_3_P RETURN VARCHAR2 IS
1590 BEGIN
1591 RETURN CP_BILL_ADDRESS_LINE_3;
1592 END CP_BILL_ADDRESS_LINE_3_P;
1593 FUNCTION CP_BILL_TOWN_OR_CITY_P RETURN VARCHAR2 IS
1594 BEGIN
1595 RETURN CP_BILL_TOWN_OR_CITY;
1596 END CP_BILL_TOWN_OR_CITY_P;
1597 FUNCTION CP_BILL_REGION_P RETURN VARCHAR2 IS
1598 BEGIN
1599 RETURN CP_BILL_REGION;
1600 END CP_BILL_REGION_P;
1601 FUNCTION CP_BILL_POSTAL_CODE_P RETURN VARCHAR2 IS
1602 BEGIN
1603 RETURN CP_BILL_POSTAL_CODE;
1604 END CP_BILL_POSTAL_CODE_P;
1605 FUNCTION CP_BILL_COUNTRY_P RETURN VARCHAR2 IS
1606 BEGIN
1607 RETURN CP_BILL_COUNTRY;
1608 END CP_BILL_COUNTRY_P;
1609 FUNCTION CP_BILL_ADDRESS_LINE_4_P RETURN VARCHAR2 IS
1610 BEGIN
1611 RETURN CP_BILL_ADDRESS_LINE_4;
1612 END CP_BILL_ADDRESS_LINE_4_P;
1613 --ADDED
1614 function BeforeReport return boolean is
1615 l_result VARCHAR2(1);
1616 l_return_status VARCHAR2(5);
1617 l_msg_count NUMBER;
1618 l_msg_data VARCHAR2(3000);
1619 l_cum_profile VARCHAR2(5);
1620 begin
1621 BEGIN
1622 l_result:= WSH_Document_PVT.is_final(1.0,NULL,NULL,NULL,l_return_status,l_msg_count,l_msg_data,p_delivery_id,'PACK_TYPE');
1623 IF FND_API.to_boolean(l_result)
1624 THEN
1625 NULL;
1626 END IF;
1627 END;
1628 BEGIN
1629 l_cum_profile:=FND_PROFILE.value('RLM_PRINT_CUM_DATA');
1630 IF l_cum_profile='Y'
1631 THEN
1632 cp_rlm_print_cum_data:='Y';
1633 ELSE
1634 cp_rlm_print_cum_data:='N';
1635 END IF;
1636 END;
1637 BEGIN
1638 IF p_print_mode IS NOT NULL
1639 THEN
1640 cp_draft_or_final := WSH_UTIL_CORE.Get_Lookup_Meaning( p_lookup_type => 'PACK_MODE',
1641 p_lookup_code =>p_print_mode);
1642 ELSE
1643 NULL;
1644 END IF;
1645 IF p_delivery_id IS NOT NULL THEN
1646 SELECT hr.name
1647 INTO cp_warehouse_name
1648 FROM hr_organization_units hr,
1649 wsh_new_deliveries del
1650 WHERE del.organization_id = hr.organization_id
1651 AND del.delivery_id = p_delivery_id;
1652 ELSIF p_organization_id IS NOT NULL
1653 THEN
1654 SELECT name
1655 INTO cp_warehouse_name
1656 FROM hr_organization_units
1657 WHERE organization_id=p_organization_id;
1658 p_organizationid_1:='AND det.organization_id = :p_organization_id';
1659 ELSE
1660 NULL;
1661 END IF;
1662 cp_print_date:=SYSDATE;
1663 EXCEPTION
1664 WHEN OTHERS THEN
1665 NULL;
1666 END;
1667 BEGIN
1668 IF p_sort='CUST'
1669 THEN
1670 IF p_print_cust_item = 'Y'
1671 THEN
1672 p_customer_item_number:='mci.customer_item_number';
1673 ELSE
1674 IF p_item_display='D'
1675 THEN
1676 p_customer_item_number := '''''';
1677 p_item_flex := '''''';
1678 ELSE
1679 p_customer_item_number := '''''';
1680 END IF;
1681 END IF;
1682 ELSE
1683 IF p_item_display='D'
1684 THEN
1685 p_customer_item_number := '''''';
1686 p_item_flex :='''''';
1687 ELSE
1688 p_customer_item_number := '''''';
1689 END IF;
1690 END IF;
1691 END;
1692 IF (p_item_display = 'B' or p_item_display = 'F' ) THEN
1693 BEGIN
1694 NULL;
1695 END;
1696 END IF;
1697 return(true);
1698 end;
1699 FUNCTION Address_New(cf_bill_to_loc in number) RETURN VARCHAR2 IS
1700 l_location_id NUMBER;
1701 l_address_line_1 hz_locations.address1%TYPE;
1702 l_address_line_2 hz_locations.address2%TYPE;
1703 l_address_line_3 hz_locations.address3%TYPE;
1704 l_address_line_4 hz_locations.address4%TYPE;
1705 l_town_or_city hz_locations.city%TYPE;
1706 l_region hz_locations.county%TYPE;
1707 l_postal_code hz_locations.postal_code%TYPE;
1708 l_country fnd_territories_tl.territory_short_name%TYPE;
1709 BEGIN
1710 SELECT loc_bill.address1,
1711 loc_bill.address2,
1712 loc_bill.address3,
1713 loc_bill.address4,
1714 loc_bill.city,
1715 nvl(nvl(loc_bill.province,loc_bill.state),loc_bill.county),
1716 loc_bill.postal_code,
1717 terr_bill.territory_short_name
1718 INTO l_address_line_1,
1719 l_address_line_2,
1720 l_address_line_3,
1721 l_address_line_4,
1722 l_town_or_city,
1723 l_region,
1724 l_postal_code,
1725 l_country
1726 FROM hz_locations loc_bill,
1727 fnd_territories_tl terr_bill
1728 WHERE loc_bill.country = terr_bill.territory_code(+)
1729 AND decode(loc_bill.country,null,userenv('LANG'),terr_bill.language) = userenv('LANG')
1730 AND loc_bill.location_id =cf_bill_to_loc;
1731 cp_bill_address_line_1 := l_address_line_1;
1732 cp_bill_address_line_2 := l_address_line_2;
1733 cp_bill_address_line_3 := l_address_line_3;
1734 cp_bill_address_line_4 := l_address_line_4;
1735 cp_bill_town_or_city := l_town_or_city;
1736 cp_bill_region := l_region;
1737 cp_bill_postal_code := l_postal_code;
1738 cp_bill_country := l_country;
1739 return(' ');
1740 EXCEPTION WHEN OTHERS THEN
1741 RETURN (' ');
1742 END Address_New;
1743 END WSH_WSHRDPAK_XMLP_PKG;
1744
[Home] [Help]