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