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