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