1 PACKAGE BODY WSH_WSHRDASP_XMLP_PKG AS
2 /* $Header: WSHRDASPB.pls 120.2 2008/02/20 07:17:53 dwkrishn noship $ */
3 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
4 CURSOR GET_BATCH_NAME(V_BATCH_ID IN NUMBER) IS
5 SELECT
6 WPB.NAME
7 FROM
8 WSH_PICKING_BATCHES WPB
9 WHERE WPB.BATCH_ID = V_BATCH_ID;
10 CURSOR GET_CUSTOMER IS
11 SELECT
12 SUBSTRB(PARTY.PARTY_NAME
13 ,1
14 ,50) CUSTOMER_NAME
15 FROM
16 HZ_PARTIES PARTY,
17 HZ_CUST_ACCOUNTS CUST_ACCT
18 WHERE CUST_ACCT.PARTY_ID = PARTY.PARTY_ID
19 AND CUST_ACCT.CUST_ACCOUNT_ID = P_CUSTOMER_ID;
20 CURSOR GET_DELIVERY(P_DELID IN NUMBER) IS
21 SELECT
22 NAME
23 FROM
24 WSH_NEW_DELIVERIES
25 WHERE DELIVERY_ID = P_DELID;
26 CURSOR GET_ORG_NAME IS
27 SELECT
28 NAME
29 FROM
30 HR_ALL_ORGANIZATION_UNITS
31 WHERE ORGANIZATION_ID = P_ORG;
32 CURSOR GET_SHIP_METHOD IS
33 SELECT
34 MEANING
35 FROM
36 FND_LOOKUP_VALUES_VL
37 WHERE LOOKUP_TYPE = 'SHIP METHOD'
38 AND LOOKUP_CODE = P_SHIP_METHOD_CODE;
39 CURSOR GET_SHIP_CONF_RULE_NAME IS
40 SELECT
41 NAME
42 FROM
43 WSH_SHIP_CONFIRM_RULES
44 WHERE SHIP_CONFIRM_RULE_ID = P_SC_RULE_ID;
45 CURSOR GET_YES_NO_LOOKUP IS
46 SELECT
47 MEANING
48 FROM
49 FND_LOOKUPS
50 WHERE LOOKUP_TYPE = 'YES_NO'
51 AND LOOKUP_CODE = P_EXCEPTIONS_ONLY;
52 CURSOR GET_AUTO_SC_ENABLE_MESSAGE IS
53 SELECT
54 MESSAGE_TEXT
55 FROM
56 FND_NEW_MESSAGES
57 WHERE MESSAGE_NAME = 'WSH_AUTO_SC_MESSAGE'
58 AND LANGUAGE_CODE = USERENV('LANG');
59 APF BOOLEAN;
60 BEGIN
61 BEGIN
62 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
63
64 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
65 EXCEPTION
66 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
67 /*SRW.MESSAGE(1
68 ,'Failed FND SRWINIT.')*/NULL;
69 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
70 END;
71 IF P_BATCH_TYPE = 'SC' AND P_SC_BATCH_ID IS NOT NULL THEN
72 OPEN GET_BATCH_NAME(P_SC_BATCH_ID);
73 FETCH GET_BATCH_NAME
74 INTO CP_BATCH_NAME;
75 CLOSE GET_BATCH_NAME;
76 CP_BATCH_ID := P_SC_BATCH_ID;
77 ELSIF P_BATCH_TYPE = 'AP' AND P_AP_BATCH_ID IS NOT NULL THEN
78 OPEN GET_BATCH_NAME(P_AP_BATCH_ID);
79 FETCH GET_BATCH_NAME
80 INTO CP_BATCH_NAME;
81 CLOSE GET_BATCH_NAME;
82 CP_BATCH_ID := P_AP_BATCH_ID;
83 END IF;
84 IF P_SHIP_METHOD_CODE IS NOT NULL THEN
85 CP_SHIP_METHOD := WSH_UTIL_CORE.GET_LOOKUP_MEANING('SHIP METHOD'
86 ,P_SHIP_METHOD_CODE);
87 END IF;
88 IF P_PR_BATCH_ID IS NOT NULL THEN
89 OPEN GET_BATCH_NAME(P_PR_BATCH_ID);
90 FETCH GET_BATCH_NAME
91 INTO CP_PR_BATCH;
92 CLOSE GET_BATCH_NAME;
93 END IF;
94 IF P_SHIP_FROM IS NOT NULL THEN
95 CP_SHIP_FROM := WSH_UTIL_CORE.GET_LOCATION_DESCRIPTION(P_SHIP_FROM
96 ,'NEW UI CODE');
97 END IF;
98 IF P_SHIP_TO IS NOT NULL THEN
99 CP_SHIP_TO := WSH_UTIL_CORE.GET_LOCATION_DESCRIPTION(P_SHIP_TO
100 ,'NEW UI CODE');
101 END IF;
102 IF P_CUSTOMER_ID IS NOT NULL THEN
103 OPEN GET_CUSTOMER;
104 FETCH GET_CUSTOMER
105 INTO CP_CUSTOMER;
106 CLOSE GET_CUSTOMER;
107 END IF;
108 IF P_ORG IS NOT NULL THEN
109 OPEN GET_ORG_NAME;
110 FETCH GET_ORG_NAME
111 INTO CP_ORG;
112 CLOSE GET_ORG_NAME;
113 END IF;
114 IF P_DELIVERY_FROM IS NOT NULL THEN
115 OPEN GET_DELIVERY(P_DELIVERY_FROM);
116 FETCH GET_DELIVERY
117 INTO CP_DELIVERY_FROM;
118 CLOSE GET_DELIVERY;
119 END IF;
120 IF P_DELIVERY_TO IS NOT NULL THEN
121 OPEN GET_DELIVERY(P_DELIVERY_TO);
122 FETCH GET_DELIVERY
123 INTO CP_DELIVERY_TO;
124 CLOSE GET_DELIVERY;
125 END IF;
126 IF P_EXCEPTIONS_ONLY IS NOT NULL THEN
127 CP_EXCEPTIONS_ONLY := WSH_UTIL_CORE.GET_LOOKUP_MEANING('YES_NO',P_EXCEPTIONS_ONLY);
128 END IF;
129 IF P_BATCH_TYPE = 'SC' THEN
130 OPEN GET_SHIP_CONF_RULE_NAME;
131 FETCH GET_SHIP_CONF_RULE_NAME
132 INTO CP_AUTO_CONFIRM_RULE;
133 CLOSE GET_SHIP_CONF_RULE_NAME;
134 END IF;
135 IF P_BATCH_TYPE = 'SC' THEN
136 OPEN GET_AUTO_SC_ENABLE_MESSAGE;
137 FETCH GET_AUTO_SC_ENABLE_MESSAGE
138 INTO P_AUTO_SC_MESSAGE;
139 CLOSE GET_AUTO_SC_ENABLE_MESSAGE;
140 END IF;
141 CP_PICKUP_DATE_FROM :=TO_CHAR (P_PICKUP_DATE_FROM ,'YYYY/MM/DD');
142 CP_PICKUP_DATE_TO := TO_CHAR (P_PICKUP_DATE_TO ,'YYYY/MM/DD');
143 CP_DROPOFF_DATE_FROM := TO_CHAR (P_DROPOFF_DATE_FROM,'yyyy/MM/DD');
144 CP_DROPOFF_DATE_TO := TO_CHAR (P_DROPOFF_DATE_TO,'YYYY/MM/DD');
145
146 LP_PICKUP_DATE_TO := TO_DATE(CP_PICKUP_DATE_TO,'YYYY/MM/DD');
147 LP_DROPOFF_DATE_TO := TO_DATE(CP_DROPOFF_DATE_TO,'YYYY/MM/DD');
148 LP_PICKUP_DATE_FROM := TO_DATE(CP_PICKUP_DATE_FROM,'YYYY/MM/DD');
149 LP_DROPOFF_DATE_FROM := TO_DATE(CP_DROPOFF_DATE_FROM,'YYYY/MM/DD');
150
151 /* LP_PICKUP_DATE_TO := TO_DATE(TO_CHAR (P_PICKUP_DATE_TO ,'DD-MON-YY'),'DD-MON-YY');
152 LP_DROPOFF_DATE_TO := TO_DATE(TO_CHAR (P_DROPOFF_DATE_TO,'DD-MON-YY'),'DD-MON-YY');
153 LP_PICKUP_DATE_FROM := TO_DATE(TO_CHAR (P_PICKUP_DATE_FROM ,'DD-MON-YY'),'DD-MON-YY');
154 LP_DROPOFF_DATE_FROM := TO_DATE(TO_CHAR (P_DROPOFF_DATE_FROM,'DD-MON-YY'),'DD-MON-YY');*/
155
156 APF := AFTERPFORM;
157 RETURN (TRUE);
158 END BEFOREREPORT;
159
160 FUNCTION AFTERPFORM RETURN BOOLEAN IS
161 BEGIN
162 IF (P_BOL_FROM IS NOT NULL) AND (P_BOL_TO IS NOT NULL) THEN
163 IF P_BOL_FROM = P_BOL_TO THEN
164 LP_BOL_NUMBER := ' wbr.bill_of_lading_number = :p_bol_from and ';
165 ELSE
166 LP_BOL_NUMBER := ' wbr.bill_of_lading_number between :p_bol_from and :p_bol_to and ';
167 END IF;
168 ELSIF P_BOL_FROM IS NOT NULL THEN
169 LP_BOL_NUMBER := ' wbr.bill_of_lading_number >= :p_bol_from and ';
170 ELSIF P_BOL_TO IS NOT NULL THEN
171 LP_BOL_NUMBER := ' wbr.bill_of_lading_number <= :p_bol_to and ';
172 ELSE
173 LP_BOL_NUMBER := ' '; --praveen
174 END IF;
175 P_PR_BATCH := ' ';
176 IF P_BATCH_TYPE = 'SC' THEN
177 P_PR_BATCH := ' we.batch_id(+) = wnd.batch_id and ';
178 ELSIF P_BATCH_TYPE = 'AP' THEN
179 P_PR_BATCH := 'we.batch_id(+) = wnd.ap_batch_id and ';
180 END IF;
181 IF P_SHIP_FROM IS NOT NULL THEN
182 P_PR_BATCH := P_PR_BATCH || ' wnd.initial_pickup_location_id = :p_ship_from and ';
183 END IF;
184 IF P_SHIP_TO IS NOT NULL THEN
185 P_PR_BATCH := P_PR_BATCH || ' wnd.ultimate_dropoff_location_id = :p_ship_to and ';
186 END IF;
187 IF P_SC_BATCH_ID IS NOT NULL THEN
188 P_PR_BATCH := P_PR_BATCH || ' NVL(wnd.batch_id, -1) = to_number(:p_sc_batch_id) and ';
189 END IF;
190 IF P_AP_BATCH_ID IS NOT NULL THEN
191 P_PR_BATCH := P_PR_BATCH || ' NVL(wnd.ap_batch_id, -1) = to_number(:p_ap_batch_id) and ';
192 END IF;
193 IF P_PICKUP_DATE_FROM IS NOT NULL THEN
194 P_PR_BATCH := P_PR_BATCH || ' wnd.initial_pickup_date >= to_date(:lp_pickup_date_from,''DD-MON-YY'') and ';
195 END IF;
196 IF LP_PICKUP_DATE_TO IS NOT NULL THEN
197 --LP_PICKUP_DATE_TO := LP_PICKUP_DATE_TO + (86399 / 86400);
198 P_PR_BATCH := P_PR_BATCH || ' wnd.initial_pickup_date <= to_date(:lp_pickup_date_to,''DD-MON-YY'') + (86399 / 86400) and';
199 END IF;
200 IF P_DROPOFF_DATE_FROM IS NOT NULL THEN
201 P_PR_BATCH := P_PR_BATCH || ' wnd.ultimate_dropoff_date >= to_date(:lp_dropoff_date_from,''DD-MON-YY'') and ';
202 END IF;
203 IF LP_DROPOFF_DATE_TO IS NOT NULL THEN
204 -- LP_DROPOFF_DATE_TO := LP_DROPOFF_DATE_TO + (86399 / 86400);
205 P_PR_BATCH := P_PR_BATCH || ' wnd.ultimate_dropoff_date <= to_date(:lp_dropoff_date_to,''DD-MON-YY'') + (86399 / 86400) and ';
206 END IF;
207 IF P_CUSTOMER_ID IS NOT NULL THEN
208 P_PR_BATCH := P_PR_BATCH || ' NVL(wnd.customer_id, -1) = :p_customer_id and ';
209 END IF;
210 IF P_ORG IS NOT NULL THEN
211 P_PR_BATCH := P_PR_BATCH || ' wnd.organization_id = :p_org and ';
212 END IF;
213 IF P_DELIVERY_FROM IS NOT NULL THEN
214 P_PR_BATCH := P_PR_BATCH || ' wnd.delivery_id >= :p_delivery_from and ';
215 END IF;
216 IF P_DELIVERY_TO IS NOT NULL THEN
217 P_PR_BATCH := P_PR_BATCH || ' wnd.delivery_id <= :p_delivery_to and ';
218 END IF;
219 IF P_PLANNED_FLAG IS NOT NULL THEN
220 P_PR_BATCH := P_PR_BATCH || ' NVL(wnd.planned_flag, ''N'') = :p_planned_flag and ';
221 END IF;
222 IF P_SC_RULE_ID IS NOT NULL THEN
223 P_PR_BATCH := P_PR_BATCH || ' NVL(wpb.ship_confirm_rule_id, -1) = :p_sc_rule_id and ';
224 END IF;
225 IF P_PR_BATCH_ID IS NOT NULL THEN
226 P_PR_BATCH := P_PR_BATCH || 'wnd.delivery_id in (select a.delivery_id ' || 'from wsh_delivery_assignments_v a, wsh_delivery_details d ' || 'where d.delivery_detail_id = a.delivery_detail_id ' || 'and d.batch_id = :p_pr_batch_id) and ';
227 END IF;
228 IF P_EXCEPTIONS_ONLY = 'Y' THEN
229 P_EXCEPTIONS := ' we.error_message is null and we.exception_id is not null and ';
230 ELSE
231 P_EXCEPTIONS := ' ';
232 END IF;
233 RETURN (TRUE);
234 END AFTERPFORM;
235
236 FUNCTION BEFOREPFORM RETURN BOOLEAN IS
237 BEGIN
238 RETURN (TRUE);
239 END BEFOREPFORM;
240
241 FUNCTION CF_1FORMULA(DELIVERY_ID IN NUMBER) RETURN VARCHAR2 IS
242 CURSOR C_GET_TRIP_NAME IS
243 SELECT
244 WT.TRIP_ID,
245 WT.NAME
246 FROM
247 WSH_TRIPS WT,
248 WSH_TRIP_STOPS WTS,
249 WSH_DELIVERY_LEGS WDL
250 WHERE WDL.DELIVERY_ID = CF_1FORMULA.DELIVERY_ID
251 AND WTS.STOP_ID = WDL.PICK_UP_STOP_ID
252 AND WTS.TRIP_ID = WT.TRIP_ID;
253 L_TRIP_NAME VARCHAR2(30);
254 BEGIN
255 IF DELIVERY_ID IS NULL THEN
256 CP_TRIP_NAME := NULL;
257 CP_TRIP_ID := NULL;
258 RETURN NULL;
259 ELSE
260 OPEN C_GET_TRIP_NAME;
261 FETCH C_GET_TRIP_NAME
262 INTO CP_TRIP_ID,L_TRIP_NAME;
263 IF C_GET_TRIP_NAME%NOTFOUND THEN
264 CLOSE C_GET_TRIP_NAME;
265 RAISE NO_DATA_FOUND;
266 END IF;
267 CLOSE C_GET_TRIP_NAME;
268 CP_TRIP_NAME := L_TRIP_NAME;
269 RETURN L_TRIP_NAME;
270 END IF;
271 EXCEPTION
272 WHEN NO_DATA_FOUND THEN
273 RETURN NULL;
274 WHEN OTHERS THEN
275 IF C_GET_TRIP_NAME%ISOPEN THEN
276 CLOSE C_GET_TRIP_NAME;
277 END IF;
278 RETURN NULL;
279 END CF_1FORMULA;
280
281 FUNCTION CF_MESSAGE(EXCEPTION_ID IN NUMBER) RETURN VARCHAR2 IS
282 CURSOR SHIP_EXCEPTION IS
283 SELECT
284 MESSAGE
285 FROM
286 WSH_EXCEPTIONS E
287 WHERE E.EXCEPTION_ID = CF_MESSAGE.EXCEPTION_ID
288 AND E.ERROR_MESSAGE is null;
289 CURSOR PACK_EXCEPTION IS
290 SELECT
291 MESSAGE
292 FROM
293 WSH_EXCEPTIONS E
294 WHERE E.EXCEPTION_ID = CF_MESSAGE.EXCEPTION_ID
295 AND E.ERROR_MESSAGE is not null;
296 L_MESSAGE VARCHAR2(2000);
297 BEGIN
298 IF EXCEPTION_ID IS NULL THEN
299 L_MESSAGE := NULL;
300 ELSE
301 IF P_BATCH_TYPE = 'SC' THEN
302 OPEN SHIP_EXCEPTION;
303 FETCH SHIP_EXCEPTION
304 INTO L_MESSAGE;
305 CLOSE SHIP_EXCEPTION;
306 ELSE
307 OPEN PACK_EXCEPTION;
308 FETCH PACK_EXCEPTION
309 INTO L_MESSAGE;
310 CLOSE PACK_EXCEPTION;
311 END IF;
312 END IF;
313 RETURN L_MESSAGE;
314 EXCEPTION
315 WHEN NO_DATA_FOUND THEN
316 RETURN NULL;
317 WHEN OTHERS THEN
318 IF SHIP_EXCEPTION%ISOPEN THEN
319 CLOSE SHIP_EXCEPTION;
320 END IF;
321 IF PACK_EXCEPTION%ISOPEN THEN
322 CLOSE PACK_EXCEPTION;
323 END IF;
324 RETURN NULL;
325 END CF_MESSAGE;
326
327 FUNCTION AFTERREPORT RETURN BOOLEAN IS
328 BEGIN
329 BEGIN
330 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
331 EXCEPTION
332 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
333 /*SRW.MESSAGE(1
334 ,'Failed in SRWEXIT')*/NULL;
335 RAISE;
336 END;
337 RETURN (TRUE);
338 END AFTERREPORT;
339
340 FUNCTION CP_BATCH_NAME_P RETURN VARCHAR2 IS
341 BEGIN
342 RETURN CP_BATCH_NAME;
343 END CP_BATCH_NAME_P;
344
345 FUNCTION CP_CUSTOMER_P RETURN VARCHAR2 IS
346 BEGIN
347 RETURN CP_CUSTOMER;
348 END CP_CUSTOMER_P;
349
350 FUNCTION CP_SHIP_FROM_P RETURN VARCHAR2 IS
351 BEGIN
352 RETURN CP_SHIP_FROM;
353 END CP_SHIP_FROM_P;
354
355 FUNCTION CP_SHIP_TO_P RETURN VARCHAR2 IS
356 BEGIN
357 RETURN CP_SHIP_TO;
358 END CP_SHIP_TO_P;
359
360 FUNCTION CP_ORG_P RETURN VARCHAR2 IS
361 BEGIN
362 RETURN CP_ORG;
363 END CP_ORG_P;
364
365 FUNCTION CP_PR_BATCH_P RETURN VARCHAR2 IS
366 BEGIN
367 RETURN CP_PR_BATCH;
368 END CP_PR_BATCH_P;
369
370 FUNCTION CP_SHIP_METHOD_P RETURN VARCHAR2 IS
371 BEGIN
372 RETURN CP_SHIP_METHOD;
373 END CP_SHIP_METHOD_P;
374
375 FUNCTION CP_DELIVERY_FROM_P RETURN VARCHAR2 IS
376 BEGIN
377 RETURN CP_DELIVERY_FROM;
378 END CP_DELIVERY_FROM_P;
379
380 FUNCTION CP_DELIVERY_TO_P RETURN VARCHAR2 IS
381 BEGIN
382 RETURN CP_DELIVERY_TO;
383 END CP_DELIVERY_TO_P;
384
385 FUNCTION CP_TRIP_NAME_P RETURN VARCHAR2 IS
386 BEGIN
387 RETURN CP_TRIP_NAME;
388 END CP_TRIP_NAME_P;
389
390 FUNCTION CP_TRIP_ID_P RETURN NUMBER IS
391 BEGIN
392 RETURN CP_TRIP_ID;
393 END CP_TRIP_ID_P;
394
395 FUNCTION CP_MESSAGE_P RETURN VARCHAR2 IS
396 BEGIN
397 RETURN CP_MESSAGE;
398 END CP_MESSAGE_P;
399
400 FUNCTION CP_BATCH_ID_P RETURN NUMBER IS
401 BEGIN
402 RETURN CP_BATCH_ID;
403 END CP_BATCH_ID_P;
404
405 FUNCTION CP_EXCEPTIONS_ONLY_P RETURN VARCHAR2 IS
406 BEGIN
407 RETURN CP_EXCEPTIONS_ONLY;
408 END CP_EXCEPTIONS_ONLY_P;
409
410 FUNCTION CP_AUTO_CONFIRM_RULE_P RETURN VARCHAR2 IS
411 BEGIN
412 RETURN CP_AUTO_CONFIRM_RULE;
413 END CP_AUTO_CONFIRM_RULE_P;
414
415 END WSH_WSHRDASP_XMLP_PKG;
416
417
[Home] [Help]