1 PACKAGE BODY WSH_WSHRDXCP_XMLP_PKG AS
2 /* $Header: WSHRDXCPB.pls 120.3 2008/02/18 10:42:26 dwkrishn noship $ */
3 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
4 C_DATE_FORMAT varchar2(20);
5 BEGIN
6 BEGIN
7 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
8 C_DATE_FORMAT := 'DD-MON-YY';
9 CP_CREATION_DATE_FROM := to_char(P_CREATION_DATE_FROM,C_DATE_FORMAT);
10 CP_CREATION_DATE_TO := to_char(P_CREATION_DATE_TO,C_DATE_FORMAT);
11 CP_LAST_UPDATE_DATE_FROM := to_char(P_LAST_UPDATE_DATE_FROM,C_DATE_FORMAT);
12 CP_LAST_UPDATE_DATE_TO := to_char(P_LAST_UPDATE_DATE_TO,C_DATE_FORMAT);
13 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
14 EXCEPTION
15 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
16 /*SRW.MESSAGE(1
17 ,'Failed FND SRWINIT.')*/NULL;
18 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
19 END;
20 DECLARE
21 P_ORG_ID_CHAR VARCHAR2(100) := TO_CHAR(P_ORG_ID);
22 BEGIN
23 /*SRW.USER_EXIT('FND PUTPROFILE NAME="' || 'MFG_ORGANIZATION_ID' || '" FIELD="' || P_ORG_ID_CHAR || '"')*/NULL;
24 EXCEPTION
25 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
26 /*SRW.MESSAGE(020
27 ,'Failed in before report trigger, setting org profile ')*/NULL;
28 RAISE;
29 END;
30 BEGIN
31 NULL;
32 EXCEPTION
33 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
34 /*SRW.MESSAGE(1
35 ,'Before Report: LocatorFlex')*/NULL;
36 END;
37 BEGIN
38 NULL;
39 EXCEPTION
40 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
41 /*SRW.MESSAGE(1
42 ,'Before Report: ItemFlex')*/NULL;
43 END;
44 DECLARE
45 CURSOR WAREHOUSE_NAME IS
46 SELECT
47 NAME
48 FROM
49 HR_ORGANIZATION_UNITS
50 WHERE ORGANIZATION_ID = P_ORG_ID;
51 BEGIN
52 OPEN WAREHOUSE_NAME;
53 FETCH WAREHOUSE_NAME
54 INTO CP_WAREHOUSE_NAME;
55 CLOSE WAREHOUSE_NAME;
56 CP_EXCEPTION_LOCATION_NAME := WSH_UTIL_CORE.GET_LOCATION_DESCRIPTION(P_EXCEPTION_LOCATION_ID
57 ,'NEW UI CODE');
58 CP_SHIP_FROM_LOCATION_NAME := WSH_UTIL_CORE.GET_LOCATION_DESCRIPTION(P_LOGGED_LOCATION_ID
59 ,'NEW UI CODE');
60 END;
61 RETURN (TRUE);
62 END BEFOREREPORT;
63
64 FUNCTION AFTERREPORT RETURN BOOLEAN IS
65 BEGIN
66 BEGIN
67 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
68 EXCEPTION
69 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
70 /*SRW.MESSAGE(1
71 ,'Failed in SRWEXIT')*/NULL;
72 RAISE;
73 END;
74 RETURN (TRUE);
75 END AFTERREPORT;
76
77 FUNCTION AFTERPFORM RETURN BOOLEAN IS
78 L_DEL_LINES_CONT VARCHAR2(5000);
79 BEGIN
80 BEGIN
81 P_BATCHES := ' ';
82 P_WSH_PICKING_BATCHES_TABLE := ' ';
83 P_WE_WPB_OUTER_JOIN := ' ';
84 P_HINT_1 := ' ';
85 IF (P_MOVE_ORDER_LOW IS NOT NULL OR P_MOVE_ORDER_HIGH IS NOT NULL) THEN
86 P_WSH_PICKING_BATCHES_TABLE := ' wsh_picking_batches wpb, ';
87 P_WE_WPB_OUTER_JOIN := ' and wpb.batch_id = we.batch_id ';
88 P_HINT_1 := '/*+leading(wpb)*/ ';
89 IF (P_MOVE_ORDER_LOW IS NOT NULL AND P_MOVE_ORDER_HIGH IS NOT NULL) THEN
90 IF (P_MOVE_ORDER_LOW < P_MOVE_ORDER_HIGH) THEN
91 P_BATCHES := ' and wpb.name between :p_move_order_low and :p_move_order_high ';
92 ELSIF (P_MOVE_ORDER_LOW = P_MOVE_ORDER_HIGH) THEN
93 P_BATCHES := ' and wpb.name = :p_move_order_low ';
94 END IF;
95 ELSIF P_MOVE_ORDER_LOW IS NULL THEN
96 P_BATCHES := ' and wpb.name <= :p_move_order_high ';
97 ELSE
98 P_BATCHES := ' and wpb.name >= :p_move_order_low ';
99 END IF;
100 END IF;
101 END;
102 P_REQUEST := ' ';
103 P_EXCEPTION := ' ';
104 P_LOGGING_ENTITY_LOCATION := ' ';
105 P_TRIP := ' ';
106 P_DELIVERY := ' ';
107 P_SEVERITY_STATUS := ' ';
108 P_CREATION_UPDATE_DATE := ' ';
109 IF (P_REQUEST_ID IS NOT NULL) THEN
110 P_REQUEST := ' and we.request_id = :p_request_id ';
111 END IF;
112 IF (P_EXCEPTION_NAME IS NOT NULL) THEN
113 P_EXCEPTION := ' and we.exception_name = :p_exception_name ';
114 END IF;
115 IF (P_LOGGING_ENTITY IS NOT NULL) THEN
116 P_LOGGING_ENTITY_LOCATION := ' and we.logging_entity = :p_logging_entity ';
117 END IF;
118 IF (P_LOGGED_LOCATION_ID IS NOT NULL) THEN
119 P_LOGGING_ENTITY_LOCATION := P_LOGGING_ENTITY_LOCATION || ' and we.logged_at_location_id = :p_logged_location_id ';
120 END IF;
121 IF (P_EXCEPTION_LOCATION_ID IS NOT NULL) THEN
122 P_LOGGING_ENTITY_LOCATION := P_LOGGING_ENTITY_LOCATION || ' and we.exception_location_id = :p_exception_location_id ';
123 END IF;
124 IF (P_TRIP_ID_LOW IS NOT NULL) THEN
125 P_TRIP := ' and nvl(we.trip_id, -1) >= decode(:p_request_id, null, :p_trip_id_low, nvl(we.trip_id, -1)) ';
126 END IF;
127 IF (P_TRIP_ID_HIGH IS NOT NULL) THEN
128 P_TRIP := P_TRIP || ' and nvl(we.trip_id, -1) <= decode(:p_request_id, null, :p_trip_id_high, nvl(we.trip_id, -1)) ';
129 END IF;
130 IF (P_REQUEST_ID IS NULL) THEN
131 IF (P_DELIVERY_ID_LOW IS NOT NULL) THEN
132 P_DELIVERY := ' and we.delivery_id >= :p_delivery_id_low ';
133 END IF;
134 IF (P_DELIVERY_ID_HIGH IS NOT NULL) THEN
135 P_DELIVERY := P_DELIVERY || ' and we.delivery_id <= :p_delivery_id_high ';
136 END IF;
137 END IF;
138 IF (P_SEVERITY IS NOT NULL) THEN
139 P_SEVERITY_STATUS := ' and we.severity = :p_severity ';
140 END IF;
141 IF (P_STATUS IS NOT NULL) THEN
142 P_SEVERITY_STATUS := P_SEVERITY_STATUS || ' and we.status = :p_status ';
143 END IF;
144 IF (P_CREATION_DATE_FROM IS NOT NULL) THEN
145 P_CREATION_UPDATE_DATE := ' and we.creation_date >= :p_creation_date_from ';
146 END IF;
147 IF (P_CREATION_DATE_TO IS NOT NULL) THEN
148 P_CREATION_UPDATE_DATE := P_CREATION_UPDATE_DATE || ' and we.creation_date - ( 86399/86400 ) <= :p_creation_date_to ';
149 END IF;
150 IF (P_LAST_UPDATE_DATE_FROM IS NOT NULL) THEN
151 P_CREATION_UPDATE_DATE := P_CREATION_UPDATE_DATE || ' and we.last_update_date >= :p_last_update_date_from ';
152 END IF;
153 IF (P_LAST_UPDATE_DATE_TO IS NOT NULL) THEN
154 P_CREATION_UPDATE_DATE := P_CREATION_UPDATE_DATE || ' and we.last_update_date - ( 86399/86400 ) <= :p_last_update_date_to ';
155 END IF;
156 L_DEL_LINES_CONT := '';
157 IF P_DELIVERY_ID_LOW IS NOT NULL OR P_DELIVERY_ID_HIGH IS NOT NULL THEN
158 L_DEL_LINES_CONT := 'UNION select
159 EXCEPTION_ID
160 ,LOGGED_AT_LOCATION_ID
161 ,LE.MEANING LOGGING_ENTITY_MEANING
162 ,EXCEPTION_NAME
163 ,EXCEPTION_LOCATION_ID
164 ,MESSAGE
165 ,SEV.MEANING SEVERITY_MEANING
166 ,MANUALLY_LOGGED
167 ,STA.MEANING STATUS_MEANING
168 ,we.TRIP_ID
169 ,TRIP_NAME
170 ,we.TRIP_STOP_ID
171 ,we.DELIVERY_ID
172 ,DELIVERY_NAME
173 ,we.DELIVERY_DETAIL_ID
174 ,DELIVERY_ASSIGNMENT_ID
175 ,we.CONTAINER_NAME
176 , P_ITEM_FLEXSQL C_ITEM_FLEXDAT
177 ,we.INVENTORY_ITEM_ID
178 ,we.LOT_NUMBER
179 -- HW OPM Convergence Project. Commented Sublot
180 -- ,we.SUBLOT_NUMBER
181 ,we.REVISION
182 ,we.SERIAL_NUMBER
183 ,UNIT_OF_MEASURE
184 ,UNIT_OF_MEASURE2
185 ,QUANTITY
186 ,QUANTITY2
187 ,we.SUBINVENTORY
188 ,we.LOCATOR_ID
189 , P_LOCATOR_FLEXSQL C_LOCATOR_FLEXDAT
190 ,ARRIVAL_DATE
191 ,DEPARTURE_DATE
192 ,ERROR_MESSAGE
193 ,we.CREATION_DATE
194 ,we.REQUEST_ID
195 ,WND.NAME DEL_NAME
196 ,WT.NAME TRP_NAME
197 from
198 wsh_exceptions we,
199 mtl_system_items sys,
200 mtl_item_locations loc, ' || P_WSH_PICKING_BATCHES_TABLE || ' wsh_lookups sta,
201 wsh_lookups sev,
202 wsh_lookups le,
203 WSH_NEW_DELIVERIES WND,
204 WSH_TRIPS WT,
205 WSH_TRIP_STOPS WTS,
206 WSH_DELIVERY_DETAILS WDD
207 where
208 sta.lookup_type = ''EXCEPTION_STATUS''
209 and sta.lookup_code = we.status
210 and sev.lookup_type IN ( ''EXCEPTION_SEVERITY'',''EXCEPTION_BEHAVIOR'')
211 and sev.lookup_code = we.severity
212 and le.lookup_type = ''LOGGING_ENTITY''
213 and le.lookup_code = logging_entity
214 and sys.organization_id(+) = we.exception_location_id
215 and sys.inventory_item_id(+) = we.inventory_item_id
216 and loc.inventory_location_id(+) = we.locator_id
217 and loc.organization_id(+) = we.exception_location_id ' || P_WE_WPB_OUTER_JOIN || ' and (we.exception_name IS NULL OR we.exception_name not like ''WSH_IB%'')
218 and we.DELIVERY_ID = WND.DELIVERY_ID (+)
219 and we.TRIP_ID = WT.TRIP_ID (+)
220 and we.TRIP_STOP_ID = WTS.STOP_ID (+)
221 and we.DELIVERY_DETAIL_ID = WDD.DELIVERY_DETAIL_ID (+)
222 and ( WND.SHIPMENT_DIRECTION is null or WND.SHIPMENT_DIRECTION IN (''O'', ''IO'') ) --Bug# 3748009
223 and ( WND.DELIVERY_TYPE is null or WND.DELIVERY_TYPE = ''STANDARD'') -- R12 MDC changes
224 and ( WT.SHIPMENTS_TYPE_FLAG is null or WT.SHIPMENTS_TYPE_FLAG IN (''O'', ''M'') ) --Bug# 3748009
225 and ( WTS.SHIPMENTS_TYPE_FLAG is null or WTS.SHIPMENTS_TYPE_FLAG IN (''O'', ''M'') ) --Bug# 3748009
226 and ( WDD.LINE_DIRECTION is null or WDD.LINE_DIRECTION IN (''O'', ''IO'') ) ';
227 L_DEL_LINES_CONT := L_DEL_LINES_CONT || P_BATCHES;
228 L_DEL_LINES_CONT := L_DEL_LINES_CONT || ' and (
229 we.exception_id IN (
230 SELECT we1.exception_id
231 FROM wsh_exceptions we1, wsh_delivery_assignments_v wda
232 WHERE we1.delivery_detail_id = wda.delivery_detail_id ';
233 IF P_DELIVERY_ID_LOW IS NOT NULL THEN
234 L_DEL_LINES_CONT := L_DEL_LINES_CONT || ' AND wda.delivery_id >= :p_delivery_id_low ';
235 END IF;
236 IF P_DELIVERY_ID_HIGH IS NOT NULL THEN
237 L_DEL_LINES_CONT := L_DEL_LINES_CONT || ' AND wda.delivery_id <= :p_delivery_id_high ';
238 END IF;
239 L_DEL_LINES_CONT := L_DEL_LINES_CONT || ' AND wda.delivery_id IS NOT NULL
240 AND we1.status <> ''CLOSED''
241 )
242 OR we.exception_id IN (
243 SELECT we1.exception_id
244 FROM wsh_exceptions we1, wsh_delivery_assignments_v wda, wsh_delivery_details wdd1
245 WHERE we1.delivery_detail_id = wdd1.delivery_detail_id --LPN Synch Up.added delivery_detail_id instead of container_name..samanna
246 AND wdd1.container_flag = ''Y''
247 AND wdd1.delivery_detail_id = wda.delivery_detail_id ';
248 IF P_DELIVERY_ID_LOW IS NOT NULL THEN
249 L_DEL_LINES_CONT := L_DEL_LINES_CONT || ' AND wda.delivery_id >= :p_delivery_id_low ';
250 END IF;
251 IF P_DELIVERY_ID_HIGH IS NOT NULL THEN
252 L_DEL_LINES_CONT := L_DEL_LINES_CONT || ' AND wda.delivery_id <= :p_delivery_id_high ';
253 END IF;
254 L_DEL_LINES_CONT := L_DEL_LINES_CONT || ' AND wda.delivery_id IS NOT NULL
255 AND we1.status <> ''CLOSED''
256 ) )';
257 P_DEL_LINES_CONT := L_DEL_LINES_CONT;
258 END IF;
259 IF P_SORT_BY = '1' THEN
260 P_ORDER_BY := ' order by we.exception_name ';
261 END IF;
262 IF P_SORT_BY = '2' THEN
263 P_ORDER_BY := ' order by we.severity, we.trip_name, we.delivery_name ';
264 END IF;
265 IF P_SORT_BY = '3' THEN
266 P_ORDER_BY := ' order by we.severity, we.delivery_name, we.trip_name ';
267 END IF;
268 IF P_SORT_BY = '4' THEN
269 P_ORDER_BY := ' order by we.status, we.trip_name, we.delivery_name ';
270 END IF;
271 IF P_SORT_BY = '5' THEN
272 P_ORDER_BY := ' order by we.status, we.delivery_name, we.trip_name ';
273 END IF;
274 IF P_SORT_BY = '6' THEN
275 P_ORDER_BY := ' order by we.trip_name, we.delivery_name ';
276 END IF;
277 IF P_SORT_BY = '7' THEN
278 P_ORDER_BY := ' order by we.delivery_name, we.trip_name ';
279 END IF;
280 IF P_SORT_BY = '8' THEN
281 P_ORDER_BY := ' order by we.creation_date, we.trip_name, we.delivery_name ';
282 END IF;
283 IF P_SORT_BY = '9' THEN
284 P_ORDER_BY := ' order by we.creation_date, we.delivery_name, we.trip_name ';
285 END IF;
286 IF P_SORT_BY = '10' THEN
287 P_ORDER_BY := ' order by we.logging_entity ';
288 END IF;
289 RETURN (TRUE);
290 END AFTERPFORM;
291
292 FUNCTION C_LOGGED_LOCATIONFORMULA(LOGGED_AT_LOCATION_ID IN NUMBER) RETURN CHAR IS
293 L_LOGGED_LOCATION VARCHAR2(121);
294 BEGIN
295 IF LOGGED_AT_LOCATION_ID IS NOT NULL THEN
296 L_LOGGED_LOCATION := WSH_UTIL_CORE.GET_LOCATION_DESCRIPTION(LOGGED_AT_LOCATION_ID
297 ,'NEW UI CODE');
298 RETURN (L_LOGGED_LOCATION);
299 ELSE
300 RETURN (NULL);
301 END IF;
305 L_EXCEPTION_LOCATION VARCHAR2(121);
302 END C_LOGGED_LOCATIONFORMULA;
303
304 FUNCTION C_EXCEPTION_LOCATIONFORMULA(EXCEPTION_LOCATION_ID IN NUMBER) RETURN CHAR IS
306 BEGIN
307 IF EXCEPTION_LOCATION_ID IS NOT NULL THEN
308 L_EXCEPTION_LOCATION := WSH_UTIL_CORE.GET_LOCATION_DESCRIPTION(EXCEPTION_LOCATION_ID
309 ,'NEW UI CODE');
310 RETURN (L_EXCEPTION_LOCATION);
311 ELSE
312 RETURN (NULL);
313 END IF;
314 END C_EXCEPTION_LOCATIONFORMULA;
315
316 FUNCTION CF_CONTAINER_NAMEFORMULA(CONTAINER_NAME IN VARCHAR2
317 ,DELIVERY_DETAIL_ID IN NUMBER) RETURN VARCHAR2 IS
318 CURSOR C_NAME(X_DETAIL_ID IN NUMBER) IS
319 SELECT
320 CONTAINER_NAME,
321 CONTAINER_FLAG
322 FROM
323 WSH_DELIVERY_DETAILS WDD
324 WHERE WDD.DELIVERY_DETAIL_ID = X_DETAIL_ID;
325 L_INFO C_NAME%ROWTYPE;
326 BEGIN
327 IF CONTAINER_NAME IS NOT NULL THEN
328 CP_DELIVERY_DETAIL_ID := DELIVERY_DETAIL_ID;
329 RETURN CONTAINER_NAME;
330 END IF;
331 IF DELIVERY_DETAIL_ID IS NULL THEN
332 CP_DELIVERY_DETAIL_ID := NULL;
333 RETURN NULL;
334 END IF;
335 OPEN C_NAME(DELIVERY_DETAIL_ID);
336 FETCH C_NAME
337 INTO L_INFO;
338 CLOSE C_NAME;
339 IF L_INFO.CONTAINER_FLAG = 'Y' THEN
340 CP_DELIVERY_DETAIL_ID := NULL;
341 ELSE
342 CP_DELIVERY_DETAIL_ID := DELIVERY_DETAIL_ID;
343 END IF;
344 RETURN L_INFO.CONTAINER_NAME;
345 EXCEPTION
346 WHEN OTHERS THEN
347 IF C_NAME%ISOPEN THEN
348 CLOSE C_NAME;
349 END IF;
350 CP_DELIVERY_DETAIL_ID := DELIVERY_DETAIL_ID;
351 RETURN CONTAINER_NAME;
352 END CF_CONTAINER_NAMEFORMULA;
353
354 FUNCTION CF_DELIVERY_NAMEFORMULA(DELIVERY_DETAIL_ID IN NUMBER
355 ,DEL_NAME IN VARCHAR2) RETURN CHAR IS
356 L_DELIVERY VARCHAR2(30);
357 CURSOR C_DELIVERY(P_DELIVERY_DETAIL_ID IN NUMBER) IS
358 SELECT
359 NAME
360 FROM
361 WSH_NEW_DELIVERIES WND,
362 WSH_DELIVERY_DETAILS WDD,
363 WSH_DELIVERY_ASSIGNMENTS_V WDA
364 WHERE WDD.DELIVERY_DETAIL_ID = P_DELIVERY_DETAIL_ID
365 AND WDA.DELIVERY_DETAIL_ID = WDD.DELIVERY_DETAIL_ID
366 AND WDA.DELIVERY_ID = WND.DELIVERY_ID
367 AND WND.DELIVERY_TYPE = 'STANDARD';
368 BEGIN
369 IF DELIVERY_DETAIL_ID IS NOT NULL AND DEL_NAME IS NULL THEN
370 OPEN C_DELIVERY(DELIVERY_DETAIL_ID);
371 FETCH C_DELIVERY
372 INTO L_DELIVERY;
373 CLOSE C_DELIVERY;
374 ELSE
375 L_DELIVERY := DEL_NAME;
376 END IF;
377 RETURN (L_DELIVERY);
378 END CF_DELIVERY_NAMEFORMULA;
379
380 FUNCTION CP_DELIVERY_DETAIL_ID_P RETURN NUMBER IS
381 BEGIN
382 RETURN CP_DELIVERY_DETAIL_ID;
383 END CP_DELIVERY_DETAIL_ID_P;
384
385 FUNCTION CP_EXCEPTION_LOCATION_NAME_P RETURN VARCHAR2 IS
386 BEGIN
387 RETURN CP_EXCEPTION_LOCATION_NAME;
388 END CP_EXCEPTION_LOCATION_NAME_P;
389
390 FUNCTION CP_SHIP_FROM_LOCATION_NAME_P RETURN VARCHAR2 IS
391 BEGIN
392 RETURN CP_SHIP_FROM_LOCATION_NAME;
393 END CP_SHIP_FROM_LOCATION_NAME_P;
394
395 FUNCTION CP_WAREHOUSE_NAME_P RETURN VARCHAR2 IS
396 BEGIN
397 RETURN CP_WAREHOUSE_NAME;
398 END CP_WAREHOUSE_NAME_P;
399
400 END WSH_WSHRDXCP_XMLP_PKG;
401
[Home] [Help]