[Home] [Help]
PACKAGE BODY: APPS.WSH_WSHRDOPN_XMLP_PKG
Source
1 PACKAGE BODY WSH_WSHRDOPN_XMLP_PKG AS
2 /* $Header: WSHRDOPNB.pls 120.3 2010/08/17 10:42:48 anvarshn ship $ */
3 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
4 CURSOR GET_ORG_CODE IS
5 SELECT
6 ORGANIZATION_CODE
7 FROM
8 MTL_PARAMETERS
9 WHERE ORGANIZATION_ID = P_ORGANIZATION_ID;
10 X_ORGANIZATION MTL_PARAMETERS.ORGANIZATION_CODE%TYPE;
11 CURSOR GET_ORG_NAME IS
12 SELECT
13 NAME
14 FROM
15 HR_ALL_ORGANIZATION_UNITS
16 WHERE ORGANIZATION_ID = P_ORGANIZATION_ID;
17 X_NAME HR_ALL_ORGANIZATION_UNITS.NAME%TYPE;
18 V_SHIP_TO VARCHAR2(20) := NULL;
19 V_CUSTOMER HZ_CUST_ACCOUNTS.ACCOUNT_NUMBER%TYPE;
20 V_CUST_ID NUMBER;
21 CURSOR GET_CUSTOMER IS
22 SELECT
23 ACCOUNT_NUMBER
24 FROM
25 HZ_CUST_ACCOUNTS
26 WHERE CUST_ACCOUNT_ID = V_CUST_ID;
27 --RTV changes
28 CURSOR GET_SUPPLIER IS
29 SELECT
30 SEGMENT1
31 FROM
32 PO_VENDORS
33 WHERE VENDOR_ID = V_CUST_ID;
34 --RTV changes
35 V_SHIP FND_LOOKUP_VALUES_VL.LOOKUP_CODE%TYPE;
36 V_ITEM_DISPLAY WSH_LOOKUPS.MEANING%TYPE;
37 CURSOR GET_ITEM_DISP_MEANING IS
38 SELECT
39 MEANING
40 FROM
41 WSH_LOOKUPS
42 WHERE LOOKUP_TYPE = 'ITEM_DISPLAY'
43 AND LOOKUP_CODE = P_ITEM_DISPLAY;
44 V_CONCAT MTL_SYSTEM_ITEMS_KFV.CONCATENATED_SEGMENTS%TYPE;
45 V_ITEM_ID MTL_SYSTEM_ITEMS_KFV.INVENTORY_ITEM_ID%TYPE;
46 CURSOR GET_ITEM_NAME IS
47 SELECT
48 CONCATENATED_SEGMENTS
49 FROM
50 MTL_SYSTEM_ITEMS_KFV
51 WHERE ORGANIZATION_ID = P_ORGANIZATION_ID
52 AND INVENTORY_ITEM_ID = V_ITEM_ID;
53 V_ITEM_SELECT VARCHAR2(90);
54 V_ITEM_FROM VARCHAR2(150);
55 V_ITEM_WHERE VARCHAR2(240);
56 V_ITEM_QUALIFY VARCHAR2(240);
57 V_ITEM_TOTAL VARCHAR2(720);
58 V_BATCH_ID WSH_PICKING_BATCHES.BATCH_ID%TYPE;
59 V_BTCH_NO WSH_PICKING_BATCHES.NAME%TYPE;
60 CURSOR GET_BATCH_NAME IS
61 SELECT
62 WPB.NAME
63 FROM
64 WSH_PICKING_BATCHES WPB
65 WHERE WPB.BATCH_ID = V_BATCH_ID;
66 V_SORT_DISPLAY FND_LOOKUP_VALUES_VL.MEANING%TYPE;
67 CURSOR GET_SORT_DISP_MEANING IS
68 SELECT
69 MEANING
70 FROM
71 WSH_LOOKUPS
72 WHERE LOOKUP_TYPE = 'OPN_SORT_BY'
73 AND LOOKUP_CODE = P_SORT_BY;
74 BEGIN
75 BEGIN
76 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
77 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
78 EXCEPTION
79 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
80 /*SRW.MESSAGE(1
81 ,'Failed FND SRWINIT.')*/NULL;
82 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
83 END;
84 OPEN GET_ORG_CODE;
85 FETCH GET_ORG_CODE
86 INTO X_ORGANIZATION;
87 IF (GET_ORG_CODE%NOTFOUND) THEN
88 CP_WAREHOUSE_NAME := NULL;
89 ELSE
90 CP_WAREHOUSE_NAME := X_ORGANIZATION;
91 END IF;
92 CLOSE GET_ORG_CODE;
93 OPEN GET_ORG_NAME;
94 FETCH GET_ORG_NAME
95 INTO X_NAME;
96 IF (GET_ORG_NAME%NOTFOUND) THEN
97 CP_WHSE_LONGNAME := NULL;
98 ELSE
99 CP_WHSE_LONGNAME := X_NAME;
100 END IF;
101 CLOSE GET_ORG_NAME;
102 IF (P_SHIP_TO IS NULL) THEN
103 CP_P_SHIPTO_LOCATION := NULL;
104 ELSE
105 CP_P_SHIPTO_LOCATION := WSH_UTIL_CORE.GET_LOCATION_DESCRIPTION(P_SHIP_TO
106 ,'NEW UI CODE');
107 END IF;
108 IF (P_CUSTOMER_ID IS NULL) THEN
109 CP_P_CUSTOMER := NULL;
110 ELSE
111 --RTV changes
112 IF substr(P_CUSTOMER_ID,1,1) = 'V' THEN
113 V_CUST_ID := substr(P_CUSTOMER_ID,3);
114 OPEN GET_SUPPLIER;
115 FETCH GET_SUPPLIER
116 INTO V_CUSTOMER;
117 CLOSE GET_SUPPLIER;
118 CP_P_CUSTOMER := V_CUSTOMER;
119 ELSE
120 V_CUST_ID := substr(P_CUSTOMER_ID,3);
121 OPEN GET_CUSTOMER;
122 FETCH GET_CUSTOMER
123 INTO V_CUSTOMER;
124 CLOSE GET_CUSTOMER;
125 CP_P_CUSTOMER := V_CUSTOMER;
126 END IF;
127 --RTV changes
128 END IF;
129 IF (P_SHIP_METHOD_CODE IS NULL) THEN
130 CP_P_SHIP_METHOD := NULL;
131 ELSE
132 CP_P_SHIP_METHOD := P_SHIP_METHOD_CODE;
133 END IF;
134 CP_P_ITEM_DISPLAY := NULL;
135 OPEN GET_ITEM_DISP_MEANING;
136 FETCH GET_ITEM_DISP_MEANING
137 INTO V_ITEM_DISPLAY;
138 CLOSE GET_ITEM_DISP_MEANING;
139 CP_P_ITEM_DISPLAY := V_ITEM_DISPLAY;
140 CP_P_BATCH_NO := NULL;
141 IF (P_BATCH_NUMBER IS NOT NULL) THEN
142 V_BATCH_ID := P_BATCH_NUMBER;
143 OPEN GET_BATCH_NAME;
144 FETCH GET_BATCH_NAME
145 INTO V_BTCH_NO;
146 CLOSE GET_BATCH_NAME;
147 CP_P_BATCH_NO := V_BTCH_NO;
148 END IF;
149 CP_P_FROM_CREATION_DATE := NULL;
150 CP_P_TO_CREATION_DATE := NULL;
151 IF (P_FROM_CREATION_DATE IS NOT NULL) THEN
152 CP_P_FROM_CREATION_DATE := P_FROM_CREATION_DATE;
153 END IF;
154 IF (P_TO_CREATION_DATE IS NOT NULL) THEN
155 CP_P_TO_CREATION_DATE := P_TO_CREATION_DATE;
156 END IF;
157 CP_P_ITEM_NO := NULL;
158 IF (P_ITEM_ID IS NOT NULL) THEN
159 V_ITEM_ID := P_ITEM_ID;
160 OPEN GET_ITEM_NAME;
161 FETCH GET_ITEM_NAME
162 INTO V_CONCAT;
163 CLOSE GET_ITEM_NAME;
164 CP_P_ITEM_NO := V_CONCAT;
165 END IF;
166 CP_P_SORT_ORDER := NULL;
167 OPEN GET_SORT_DISP_MEANING;
168 FETCH GET_SORT_DISP_MEANING
169 INTO V_SORT_DISPLAY;
170 CLOSE GET_SORT_DISP_MEANING;
171 CP_P_SORT_ORDER := V_SORT_DISPLAY;
172 RETURN (TRUE);
173 END BEFOREREPORT;
174
175 FUNCTION AFTERPFORM RETURN BOOLEAN IS
176 CURSOR GET_ORG_CODE IS
177 SELECT
178 ORGANIZATION_CODE
179 FROM
180 MTL_PARAMETERS
181 WHERE ORGANIZATION_ID = P_ORGANIZATION_ID;
182 X_ORGANIZATION MTL_PARAMETERS.ORGANIZATION_CODE%TYPE;
183 CURSOR GET_ORG_NAME IS
184 SELECT
185 NAME
186 FROM
187 HR_ALL_ORGANIZATION_UNITS
188 WHERE ORGANIZATION_ID = P_ORGANIZATION_ID;
189 X_NAME HR_ALL_ORGANIZATION_UNITS.NAME%TYPE;
190 V_SHIP_TO VARCHAR2(20) := NULL;
191 V_CUSTOMER HZ_CUST_ACCOUNTS.ACCOUNT_NUMBER%TYPE;
192 V_CUST_ID NUMBER;
193 CURSOR GET_CUSTOMER IS
194 SELECT
195 ACCOUNT_NUMBER
196 FROM
197 HZ_CUST_ACCOUNTS
198 WHERE CUST_ACCOUNT_ID = V_CUST_ID;
199 --RTV changes
200 CURSOR GET_SUPPLIER IS
201 SELECT
202 SEGMENT1
203 FROM
204 PO_VENDORS
205 WHERE VENDOR_ID = V_CUST_ID;
206 --RTV changes
207 V_SHIP FND_LOOKUP_VALUES_VL.LOOKUP_CODE%TYPE;
208 V_ITEM_DISPLAY WSH_LOOKUPS.MEANING%TYPE;
209 CURSOR GET_ITEM_DISP_MEANING IS
210 SELECT
211 MEANING
212 FROM
213 WSH_LOOKUPS
214 WHERE LOOKUP_TYPE = 'ITEM_DISPLAY'
215 AND LOOKUP_CODE = P_ITEM_DISPLAY;
216 V_CONCAT MTL_SYSTEM_ITEMS_KFV.CONCATENATED_SEGMENTS%TYPE;
217 V_ITEM_ID MTL_SYSTEM_ITEMS_KFV.INVENTORY_ITEM_ID%TYPE;
218 CURSOR GET_ITEM_NAME IS
219 SELECT
220 CONCATENATED_SEGMENTS
221 FROM
222 MTL_SYSTEM_ITEMS_KFV
223 WHERE ORGANIZATION_ID = P_ORGANIZATION_ID
224 AND INVENTORY_ITEM_ID = V_ITEM_ID;
225 V_ITEM_SELECT VARCHAR2(90);
226 V_ITEM_FROM VARCHAR2(150);
227 V_ITEM_WHERE VARCHAR2(240);
228 V_ITEM_QUALIFY VARCHAR2(240);
229 V_ITEM_TOTAL VARCHAR2(720);
230 V_BATCH_ID WSH_PICKING_BATCHES.BATCH_ID%TYPE;
231 V_BTCH_NO WSH_PICKING_BATCHES.NAME%TYPE;
232 CURSOR GET_BATCH_NAME IS
233 SELECT
234 WPB.NAME
235 FROM
236 WSH_PICKING_BATCHES WPB
237 WHERE WPB.BATCH_ID = V_BATCH_ID;
238 V_SORT_DISPLAY FND_LOOKUP_VALUES_VL.MEANING%TYPE;
239 CURSOR GET_SORT_DISP_MEANING IS
240 SELECT
241 MEANING
242 FROM
243 WSH_LOOKUPS
244 WHERE LOOKUP_TYPE = 'OPN_SORT_BY'
245 AND LOOKUP_CODE = P_SORT_BY;
246 BEGIN
247 IF (P_SHIP_TO IS NULL) THEN
248 F_WHERE := ' ';
249 ELSE
250 V_SHIP_TO := P_SHIP_TO;
251 F_WHERE := F_WHERE || ' and wnd.ultimate_dropoff_location_id = :p_ship_to ';
252 END IF;
253 IF (P_CUSTOMER_ID IS NULL) THEN
254 NULL;
255 ELSE
256 --RTV changes
257 IF (substr(P_CUSTOMER_ID,1,1) = 'V') THEN
258 V_CUST_ID := substr(P_CUSTOMER_ID,3);
259 OPEN GET_SUPPLIER;
260 FETCH GET_SUPPLIER
261 INTO V_CUSTOMER;
262 CLOSE GET_SUPPLIER;
263 F_WHERE := F_WHERE || ' and hca.account_number = ' || '''' || V_CUSTOMER || '''';
264 F_WHERE := F_WHERE || ' and nvl(wnd.consignee_flag,''C'') = ''V''' ;
265 ELSE
266 V_CUST_ID := substr(P_CUSTOMER_ID,3);
267 OPEN GET_CUSTOMER;
268 FETCH GET_CUSTOMER
269 INTO V_CUSTOMER;
270 CLOSE GET_CUSTOMER;
271 F_WHERE := F_WHERE || ' and hca.account_number = ' || '''' || V_CUSTOMER || '''';
272 F_WHERE := F_WHERE || ' and nvl(wnd.consignee_flag,''C'') = ''C''' ;
273 END IF;
274 --RTV changes
275 END IF;
276 IF (P_SHIP_METHOD_CODE IS NULL) THEN
277 NULL;
278 ELSE
279 V_SHIP := P_SHIP_METHOD_CODE;
280 F_WHERE := F_WHERE || ' and wnd.ship_method_Code = :p_ship_method_code';
281 END IF;
282 IF (P_BATCH_NUMBER IS NOT NULL) THEN
283 V_BATCH_ID := P_BATCH_NUMBER;
284 OPEN GET_BATCH_NAME;
285 FETCH GET_BATCH_NAME
286 INTO V_BTCH_NO;
287 CLOSE GET_BATCH_NAME;
288 F_WHERE := ' and wdd.batch_id = ' || '''' || V_BTCH_NO || '''';
289 END IF;
290 IF (P_FROM_CREATION_DATE IS NOT NULL) THEN
291 NULL;
292 END IF;
293 IF (P_TO_CREATION_DATE IS NOT NULL) THEN
294 NULL;
295 END IF;
296 IF (P_FROM_CREATION_DATE IS NULL AND P_TO_CREATION_DATE IS NULL) THEN
297 NULL;
298 ELSE
299 IF (P_FROM_CREATION_DATE IS NOT NULL AND P_TO_CREATION_DATE IS NOT NULL) THEN
300 P_TO_CREATION_DATE := P_TO_CREATION_DATE + (86399 / 86400);
301 F_WHERE := F_WHERE || ' and wnd.creation_date >= to_date(''' || TO_CHAR(P_FROM_CREATION_DATE
302 ,'YYYY/MM/DD HH24:MI:SS') || ''',''YYYY/MM/DD HH24:MI:SS'')' || ' and wnd.creation_date < to_date(''' || TO_CHAR(P_TO_CREATION_DATE
303 ,'YYYY/MM/DD HH24:MI:SS') || ''',''YYYY/MM/DD HH24:MI:SS'')';
304 ELSE
305 IF (P_FROM_CREATION_DATE IS NOT NULL) THEN
306 F_WHERE := F_WHERE || ' and wnd.creation_date >= to_date(''' || TO_CHAR(P_FROM_CREATION_DATE
307 ,'YYYY/MM/DD HH24:MI:SS') || ''',''YYYY/MM/DD HH24:MI:SS'')';
308 ELSE
309 P_TO_CREATION_DATE := P_TO_CREATION_DATE + (86399 / 86400);
310 F_WHERE := F_WHERE || ' and wnd.creation_date < to_date(''' || TO_CHAR(P_TO_CREATION_DATE
311 ,'YYYY/MM/DD HH24:MI:SS') || ''',''YYYY/MM/DD HH24:MI:SS'')';
312 END IF;
313 END IF;
314 END IF;
315 IF (P_ITEM_ID IS NOT NULL) THEN
316 V_ITEM_ID := P_ITEM_ID;
317 OPEN GET_ITEM_NAME;
318 FETCH GET_ITEM_NAME
319 INTO V_CONCAT;
320 CLOSE GET_ITEM_NAME;
321 END IF;
322 IF (P_ITEM_ID IS NULL) THEN
323 NULL;
324 ELSE
325 V_ITEM_SELECT := ' and (wnd.name, wnd.delivery_id) IN (select wnd2.name, wnd2.delivery_id ';
326 V_ITEM_FROM := 'from wsh_new_deliveries wnd2, wsh_delivery_assignments_v wda2, wsh_delivery_details wdd2, mtl_system_items_kfv msik2 ';
327 V_ITEM_WHERE := 'where wnd2.delivery_id = wda2.delivery_id and wda2.delivery_detail_id = wdd2.delivery_detail_id and
328 wnd2.organization_id = msik2.organization_id and wdd2.inventory_item_id = msik2.inventory_item_id and ';
329 V_ITEM_QUALIFY := ' msik2.concatenated_segments = ' || '''' || V_CONCAT || '''' || ')';
330 V_ITEM_TOTAL := V_ITEM_SELECT || V_ITEM_FROM || V_ITEM_WHERE || V_ITEM_QUALIFY;
331 F_WHERE := F_WHERE || V_ITEM_TOTAL;
332 END IF;
333 OPEN GET_SORT_DISP_MEANING;
334 FETCH GET_SORT_DISP_MEANING
335 INTO V_SORT_DISPLAY;
336 CLOSE GET_SORT_DISP_MEANING;
337 IF (P_SORT_BY = NULL) THEN
338 F_SORT_ORDER := 'deliv';
339 END IF;
340 IF (P_SORT_BY = 'D') THEN
341 F_SORT_ORDER := 'deliv';
342 END IF;
343 IF (P_SORT_BY = 'C') THEN
344 F_SORT_ORDER := 'credate, deliv';
345 END IF;
346 IF (P_SORT_BY = 'S') THEN
347 F_SORT_ORDER := 'shippri, deliv';
348 END IF;
349 IF (P_SORT_BY = 'B') THEN
350 F_SORT_ORDER := 'batchname, deliv';
351 END IF;
352 IF (P_SORT_BY = 'M') THEN
353 F_SORT_ORDER := 'shipmeth, deliv';
354 END IF;
355 RETURN (TRUE);
356 END AFTERPFORM;
357
358 FUNCTION BEFOREPFORM RETURN BOOLEAN IS
359 BEGIN
360 RETURN (TRUE);
361 END BEFOREPFORM;
362
363 FUNCTION CF_SHIPMENT_PRIORITYFORMULA(SHIPPRI IN VARCHAR2) RETURN CHAR IS
364 L_MEANING OE_LOOKUPS.MEANING%TYPE;
365 BEGIN
366 IF (SHIPPRI IS NOT NULL AND SHIPPRI <> 'Mixed') THEN
367 SELECT
368 MEANING
369 INTO L_MEANING
370 FROM
371 OE_LOOKUPS
372 WHERE LOOKUP_TYPE = 'SHIPMENT_PRIORITY'
373 AND LOOKUP_CODE = SHIPPRI;
374 RETURN L_MEANING;
375 ELSE
376 RETURN SHIPPRI;
377 END IF;
378 END CF_SHIPMENT_PRIORITYFORMULA;
379
380 FUNCTION CF_LPNFORMULA(CONTAINER_FLAG IN VARCHAR2) RETURN CHAR IS
381 LPN VARCHAR2(10);
382 BEGIN
383 IF CONTAINER_FLAG = 'Y' THEN
384 SELECT
385 MEANING
386 INTO LPN
387 FROM
388 FND_LOOKUPS
389 WHERE LOOKUP_CODE = 'Y'
390 AND LOOKUP_TYPE = 'YES_NO';
391 RETURN (LPN);
392 ELSE
393 RETURN (NULL);
394 END IF;
395 END CF_LPNFORMULA;
396
397 FUNCTION AFTERREPORT RETURN BOOLEAN IS
398 BEGIN
399 BEGIN
400 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
401 EXCEPTION
402 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
403 /*SRW.MESSAGE(1
404 ,'Failed in SRWEXIT')*/NULL;
405 RAISE;
406 END;
407 RETURN (TRUE);
408 END AFTERREPORT;
409
410 FUNCTION CP_WAREHOUSE_NAME_P RETURN VARCHAR2 IS
411 BEGIN
412 RETURN CP_WAREHOUSE_NAME;
413 END CP_WAREHOUSE_NAME_P;
414
415 FUNCTION CP_WHSE_LONGNAME_P RETURN VARCHAR2 IS
416 BEGIN
417 RETURN CP_WHSE_LONGNAME;
418 END CP_WHSE_LONGNAME_P;
419
420 FUNCTION CP_P_SHIPTO_LOCATION_P RETURN VARCHAR2 IS
421 BEGIN
422 RETURN CP_P_SHIPTO_LOCATION;
423 END CP_P_SHIPTO_LOCATION_P;
424
425 FUNCTION CP_P_CUSTOMER_P RETURN VARCHAR2 IS
426 BEGIN
427 RETURN CP_P_CUSTOMER;
428 END CP_P_CUSTOMER_P;
429
430 FUNCTION CP_TEMP_P RETURN VARCHAR2 IS
431 BEGIN
432 RETURN CP_TEMP;
433 END CP_TEMP_P;
434
435 FUNCTION CP_P_SHIP_METHOD_P RETURN VARCHAR2 IS
436 BEGIN
437 RETURN CP_P_SHIP_METHOD;
438 END CP_P_SHIP_METHOD_P;
439
440 FUNCTION CP_P_ITEM_NO_P RETURN VARCHAR2 IS
441 BEGIN
442 RETURN CP_P_ITEM_NO;
443 END CP_P_ITEM_NO_P;
444
445 FUNCTION CP_P_BATCH_NO_P RETURN VARCHAR2 IS
446 BEGIN
447 RETURN CP_P_BATCH_NO;
448 END CP_P_BATCH_NO_P;
449
450 FUNCTION CP_P_FROM_CREATION_DATE_P RETURN DATE IS
451 BEGIN
452 RETURN CP_P_FROM_CREATION_DATE;
453 END CP_P_FROM_CREATION_DATE_P;
454
455 FUNCTION CP_P_TO_CREATION_DATE_P RETURN DATE IS
456 BEGIN
457 RETURN CP_P_TO_CREATION_DATE;
458 END CP_P_TO_CREATION_DATE_P;
459
460 FUNCTION CP_P_ITEM_DISPLAY_P RETURN VARCHAR2 IS
461 BEGIN
462 RETURN CP_P_ITEM_DISPLAY;
463 END CP_P_ITEM_DISPLAY_P;
464
465 FUNCTION CP_P_SORT_ORDER_P RETURN VARCHAR2 IS
466 BEGIN
467 RETURN CP_P_SORT_ORDER;
468 END CP_P_SORT_ORDER_P;
469
470 END WSH_WSHRDOPN_XMLP_PKG;
471