1 PACKAGE BODY WSH_PO_INTEGRATION_GRP AS
2 /* $Header: WSHPOGPB.pls 120.6 2005/08/22 11:43:13 rlanka noship $ */
3 --
4 G_PKG_NAME CONSTANT VARCHAR2(50) := 'WSH_PO_INTEGRATION_GRP';
5 --
6
7
8 -- Start of comments
9 -- API name : check_purge
10 -- Type : Public
11 -- Pre-reqs : None.
12 -- Function : This API on receiving a set of header IDs,determines
13 -- whether they are eligible for purging or not by checking
14 -- for pending transactions against them and appropriately
15 -- sets a flag.
16 -- Parameters :
17 -- IN:
18 -- p_api_version_number IN NUMBER
19 -- p_init_msg_list IN VARCHAR2
20 -- p_commit IN VARCHAR2
21 -- p_in_rec IN WSH_PO_INTG_TYPES_GRP.purge_in_rectype
22 -- A table of records which conatins Header Ids of lines as one of its field.
23 -- IN OUT:
24 --
25 -- OUT:
26 -- x_msg_count OUT NOCOPY NUMBER
27 -- x_msg_data OUT NOCOPY VARCHAR2
28 -- x_out_rec OUT NOCOPY WSH_PO_INTG_TYPES_GRP.purge_out_rectype
29 -- A table of records which contains the flag field. For each record of the i/p
30 -- p_in_rec there is a corresponding entry in this table of records.
31 -- This field is set to 'Y' if there are no pending transactions against the
32 -- header ID and set to 'N' if there are pending transactions against this
33 -- header ID .
34 -- x_return_status OUT NOCOPY VARCHAR2
35 -- Version : 1.0
36 -- Previous version 1.0
37 -- Initial version 1.0
38 -- End of comments
39
40
41 PROCEDURE check_purge(
42 ---- Standard parameters
43 p_api_version_number IN NUMBER,
44 p_init_msg_list IN VARCHAR2,
45 p_commit IN VARCHAR2,
46 x_return_status OUT NOCOPY VARCHAR2,
47 x_msg_count OUT NOCOPY NUMBER,
48 x_msg_data OUT NOCOPY VARCHAR2,
49 -- procedure specific parameters
50 p_in_rec IN WSH_PO_INTG_TYPES_GRP.purge_in_rectype,
51 x_out_rec OUT NOCOPY WSH_PO_INTG_TYPES_GRP.purge_out_rectype
52 ) AS
53
54 l_purge NUMBER := 0;
55 l_api_version_number NUMBER := 1.0;
56 l_api_name CONSTANT VARCHAR2(30) := 'check_purge';
57 l_return_status VARCHAR2(1);
58 l_extend_count NUMBER;
59 --
60 l_debug_on BOOLEAN;
61 --
62 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CHECK_PURGE';
63 --
64 BEGIN
65
66
67 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS ;
68 --
69 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
70 --
71 IF l_debug_on IS NULL
72 THEN
73 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
74 END IF;
75 --
76 --
77 -- Debug Statements
78 --
79 IF l_debug_on THEN
80 WSH_DEBUG_SV.push(l_module_name);
81 --
82 WSH_DEBUG_SV.log(l_module_name,'P_API_VERSION_NUMBER',P_API_VERSION_NUMBER);
83 WSH_DEBUG_SV.log(l_module_name,'P_INIT_MSG_LIST',P_INIT_MSG_LIST);
84 WSH_DEBUG_SV.log(l_module_name,'P_COMMIT',P_COMMIT);
85 WSH_DEBUG_SV.log(l_module_name,'p_in_rec.header_ids.COUNT',p_in_rec.header_ids.COUNT);
86 END IF;
87 --
88 IF NOT FND_API.compatible_api_call(
89 l_api_version_number,
90 p_api_version_number,
91 l_api_name,
92 G_PKG_NAME) THEN
93 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
94 END IF;
95
96 IF FND_API.to_boolean(p_init_msg_list) THEN
97 FND_MSG_PUB.initialize;
98 END IF;
99
100
101 -- Loop goes through all the header IDs present in p_in_rec.
102 FOR i IN 1..p_in_rec.header_ids.COUNT LOOP
103 --
104 -- Debug Statements
105 --
106 IF l_debug_on THEN
107 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_PO_CMG_PVT.CHECK_PENDING_TXNS',WSH_DEBUG_SV.C_PROC_LEVEL);
108 END IF;
109 --
110
111 -- A function call to determine for checking pending transactions.
112 -- If the function returns
113 -- 0 -> implies no pending transactions.
114 -- 1 -> implies pending transactions.
115 l_purge := WSH_PO_CMG_PVT.check_pending_txns(p_in_rec.header_ids(i),NULL,NULL,NULL);
116 IF l_purge = 0 THEN
117 x_out_rec.purge_allowed(i) := 'Y';
118 ELSIF l_purge = 1 THEN
119 x_out_rec.purge_allowed(i) := 'N';
120 END IF;
121 END LOOP;
122
123
124 FND_MSG_PUB.Count_And_Get(
125 p_count => x_msg_count,
126 p_data => x_msg_data,
127 p_encoded => FND_API.G_FALSE);
128
129 IF FND_API.TO_BOOLEAN(p_commit) THEN
130 COMMIT WORK;
131 END IF;
132
133
134
135 --
136 -- Debug Statements
137 --
138 IF l_debug_on THEN
139 WSH_DEBUG_SV.pop(l_module_name);
140 END IF;
141 --
142 EXCEPTION
143 WHEN OTHERS THEN
144 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
145 WSH_UTIL_CORE.Default_Handler('WSH_PO_INTEGRATION_GRP.check_purge',l_module_name);
146 --
147 -- Debug Statements
148 --
149 IF l_debug_on THEN
150 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
151 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
152 END IF;
153 --
154 END check_purge;
155
156
157
158 -- Start of comments
159 -- API name : purge
160 -- Type : Public
161 -- Pre-reqs : None.
162 -- Function : This API receives a set of header IDs, the API changes
163 -- the status of the corresponding delivery detail IDs to
164 -- 'Purge(P)' status after unassigning them from their
165 -- respective deliveries.
166 -- Parameters :
167 -- IN:
168 -- p_api_version_number IN NUMBER
169 -- p_init_msg_list IN VARCHAR2
170 -- p_commit IN VARCHAR2
171 -- p_in_rec IN WSH_PO_INTG_TYPES_GRP.purge_in_rectype
172 -- A table of records which contains the header ID to be purged.
173 -- IN OUT:
174 --
175 -- OUT:
176 -- x_return_status OUT NOCOPY VARCHAR2
177 -- x_msg_count OUT NOCOPY NUMBER
178 -- x_msg_data OUT NOCOPY VARCHAR2
179 -- Version : 1.0
180 -- Previous version 1.0
181 -- Initial version 1.0
182 -- End of comments
183
184
185 PROCEDURE purge(
186 ---- Standard parameters
187 p_api_version_number IN NUMBER,
188 p_init_msg_list IN VARCHAR2,
189 p_commit IN VARCHAR2,
190 x_return_status OUT NOCOPY VARCHAR2,
191 x_msg_count OUT NOCOPY NUMBER,
192 x_msg_data OUT NOCOPY VARCHAR2,
193 -- procedure specific parameters
194 p_in_rec IN WSH_PO_INTG_TYPES_GRP.purge_in_rectype
195 ) IS
196
197 l_api_version_number NUMBER := 1.0;
198 l_api_name CONSTANT VARCHAR2(30) := 'purge';
199 l_return_status VARCHAR2(1);
200
201 l_detail_tab WSH_UTIL_CORE.id_tab_type; -- DBI Project
202 l_dbi_rs VARCHAR2(1); -- DBI Project
203
204 --
205 l_debug_on BOOLEAN;
206 --
207 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'PURGE';
208 --
209 BEGIN
210
211 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS ;
212 --
213 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
214 --
215 IF l_debug_on IS NULL
216 THEN
217 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
218 END IF;
219 --
220 --
221 -- Debug Statements
222 --
223 IF l_debug_on THEN
224 WSH_DEBUG_SV.push(l_module_name);
225 --
226 WSH_DEBUG_SV.log(l_module_name,'P_API_VERSION_NUMBER',P_API_VERSION_NUMBER);
227 WSH_DEBUG_SV.log(l_module_name,'P_INIT_MSG_LIST',P_INIT_MSG_LIST);
228 WSH_DEBUG_SV.log(l_module_name,'P_COMMIT',P_COMMIT);
229 WSH_DEBUG_SV.log(l_module_name,'p_in_rec.header_ids.count',p_in_rec.header_ids.count);
230 END IF;
231 --
232 SAVEPOINT PURGE_PVT;
233
234 IF NOT FND_API.compatible_api_call(
235 l_api_version_number,
236 p_api_version_number,
237 l_api_name,
238 G_PKG_NAME) THEN
239 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
240 END IF;
241
242 IF FND_API.to_boolean(p_init_msg_list) THEN
243 FND_MSG_PUB.initialize;
244 END IF;
245
246
247 --updates the status of all i/p lines to 'P' (purge).
248 FORALL i IN p_in_rec.header_ids.FIRST..p_in_rec.header_ids.LAST
249 UPDATE wsh_delivery_details
250 SET released_status = 'P'
251 where source_header_id = p_in_rec.header_ids(i)
252 AND source_code = 'PO'
253 RETURNING delivery_detail_id BULK COLLECT INTO l_detail_tab;
254
255 --
256 -- DBI Project
257 -- Update of wsh_delivery_details where released_status
258 -- are changed, call DBI API after the update.
259 -- This API will also check for DBI Installed or not
260 IF l_debug_on THEN
261 WSH_DEBUG_SV.log(l_module_name,'Calling DBI API.Delivery Details l_detail_tab count : ',l_detail_tab.COUNT);
262 END IF;
263 WSH_INTEGRATION.DBI_Update_Detail_Log
264 (p_delivery_detail_id_tab => l_detail_tab,
265 p_dml_type => 'UPDATE',
266 x_return_status => l_dbi_rs);
267
268 IF l_debug_on THEN
269 WSH_DEBUG_SV.log(l_module_name,'Return Status after DBI Call-',l_dbi_rs);
270 END IF;
271 IF l_dbi_rs = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
272 x_return_status := l_dbi_rs;
273 Rollback to PURGE_PVT;
274 -- just pass this return status to caller API
275 IF l_debug_on THEN
276 WSH_DEBUG_SV.logmsg(l_module_name,'DBI API Returned Unexpected error '||x_return_status);
277 WSH_DEBUG_SV.pop(l_module_name);
278 END IF;
279 return;
280 END IF;
281 -- End of Code for DBI Project
282 --
283
284 FND_MSG_PUB.Count_And_Get(
285 p_count => x_msg_count,
286 p_data => x_msg_data,
287 p_encoded => FND_API.G_FALSE);
288
289 IF FND_API.TO_BOOLEAN(p_commit) THEN
290 COMMIT WORK;
291 END IF;
292
293
294 --
295 -- Debug Statements
296 --
297 IF l_debug_on THEN
298 WSH_DEBUG_SV.pop(l_module_name);
299 END IF;
300 --
301 EXCEPTION
302
303 WHEN OTHERS THEN
304 ROLLBACK TO PURGE_PVT;
305 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
306 WSH_UTIL_CORE.ADD_MESSAGE(l_return_status,l_module_name);
307 WSH_UTIL_CORE.DEFAULT_HANDLER('WSH_PO_INTEGRATION_GRP.Purge',l_module_name);
308
309 --
310 -- Debug Statements
311 --
312 IF l_debug_on THEN
313 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
314 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
315 END IF;
316 --
317 END purge;
318
319
320 --======================================================================
321 -- PROCEDURE : vendor_merge
322 --
323 -- COMMENT :
324 -- HISTORY : Created the API.
325 --======================================================================
326
327 PROCEDURE vendor_merge(
328 P_api_version_number IN NUMBER,
329 P_init_msg_list IN VARCHAR2,
330 P_commit IN VARCHAR2,
331 X_return_status OUT NOCOPY VARCHAR2,
332 X_msg_count OUT NOCOPY NUMBER,
333 X_msg_data OUT NOCOPY VARCHAR2,
334 P_in_rec IN WSH_PO_INTG_TYPES_GRP.merge_in_rectype,
335 X_out_rec OUT NOCOPY WSH_PO_INTG_TYPES_GRP.merge_out_rectype) IS
336
337 l_api_version_number NUMBER := 1.0;
338 l_api_name CONSTANT VARCHAR2(30) := 'vendor_merge';
339 l_return_status VARCHAR2(1);
340 --
341 l_debug_on BOOLEAN;
342 --
343 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'VENDOR_MERGE';
344 --
345 BEGIN
346 --
347 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS ;
348 --
349 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
350 --
351 IF l_debug_on IS NULL
352 THEN
353 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
354 END IF;
355 --
356 IF l_debug_on THEN
357 WSH_DEBUG_SV.push(l_module_name);
358 WSH_DEBUG_SV.log(l_module_name,'P_API_VERSION_NUMBER',P_API_VERSION_NUMBER);
359 WSH_DEBUG_SV.log(l_module_name,'P_INIT_MSG_LIST',P_INIT_MSG_LIST);
360 WSH_DEBUG_SV.log(l_module_name,'P_COMMIT',P_COMMIT);
361 END IF;
362 --
363 IF NOT FND_API.compatible_api_call(
364 l_api_version_number,
365 p_api_version_number,
366 l_api_name,
367 G_PKG_NAME) THEN
368 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
369 END IF;
370 --
371 IF FND_API.to_boolean(p_init_msg_list) THEN
372 FND_MSG_PUB.initialize;
373 END IF;
374 --
375 FND_MSG_PUB.Count_And_Get(
376 p_count => x_msg_count,
377 p_data => x_msg_data,
378 p_encoded => FND_API.G_FALSE);
379 --
380 IF FND_API.TO_BOOLEAN(p_commit) THEN
381 COMMIT WORK;
382 END IF;
383 --
384 IF l_debug_on THEN
385 WSH_DEBUG_SV.pop(l_module_name);
386 END IF;
387 --
388 EXCEPTION
389 --
390 WHEN OTHERS THEN
391 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
392 WSH_UTIL_CORE.ADD_MESSAGE(l_return_status,l_module_name);
393 WSH_UTIL_CORE.DEFAULT_HANDLER('WSH_PO_INTEGRATION_GRP.VENDOR_MERGE',l_module_name);
394 --
395 IF l_debug_on THEN
396 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
397 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
398 END IF;
399 --
400 END vendor_merge;
401
402
403 --
404 -- Pre-reqs : None
405 --
406 -- Parameters
407 -- p_api_version_number known api version error number
408 -- p_init_msg_list FND_API.G_TRUE to reset list
409 -- p_commit indicates whether to commit or not.
410 -- x_return_status return status
411 -- x_msg_count number of messages in the list
412 -- x_msg_data text of messages
413 -- p_in_rec Input record structure that holds the
414 -- routing response number
415 -- x_out_rec Output record structure that holds info.
416 -- whether delivery info. has changed or not.
417 --
418 -- Purpose : This procedure is used to determine whether delivery characteristics have
419 -- changed since the time the routing response was generated.
420 -- This API checks the last_update_date on all the tables related to the
421 -- routing response and if any of the dates is greater than the
422 -- last_update_date from WSH_INBOUND_TXN_HISTORY, this API
423 -- returns TRUE, else it returns FALSE
424 --
425 -- Version : 1.0
426 --
427 PROCEDURE HasDeliveryInfoChanged(
428 P_api_version_number IN NUMBER,
429 P_init_msg_list IN VARCHAR2,
430 P_commit IN VARCHAR2,
431 x_return_status OUT NOCOPY VARCHAR2,
432 x_msg_count OUT NOCOPY NUMBER,
433 x_msg_data OUT NOCOPY VARCHAR2,
434 P_in_rec IN WSH_PO_INTG_TYPES_GRP.delInfo_in_rectype,
435 x_out_rec OUT NOCOPY WSH_PO_INTG_TYPES_GRP.delInfo_out_rectype)
436 IS
437 --
438 CURSOR c_GetDelId(p_respNum VARCHAR2) IS
439 SELECT wth.shipment_header_id, wth.last_update_date, wnd.last_update_date
440 FROM wsh_inbound_txn_history wth, wsh_new_deliveries wnd
441 WHERE receipt_number like p_respNum
442 AND shipment_header_id = wnd.delivery_id
443 AND transaction_type = 'ROUTING_RESPONSE'
444 ORDER BY NVL(revision_number, -99) DESC;
445 --
446 CURSOR c_GetLastUpdateDates(p_delId NUMBER) IS
447 SELECT MAX(wda.last_update_date), MAX(wdd.last_update_date)
448 FROM wsh_delivery_assignments_v wda,wsh_delivery_details wdd
449 WHERE wdd.delivery_detail_id = wda.delivery_detail_id
450 AND wda.delivery_id = p_delId;
451 --
452 -- This cursor gets the dates for the initial pickup location
453 --
454 CURSOR c_GetPTripLastUpdateDate(p_delId NUMBER) IS
455 SELECT wts.last_update_date, wdl.last_update_date, wt.last_update_date
456 FROM wsh_new_deliveries wnd,
457 wsh_delivery_legs wdl,
458 wsh_trip_stops wts,
459 wsh_trips wt
460 WHERE wnd.delivery_id = p_delId
461 AND wnd.delivery_id = wdl.delivery_id
462 AND wdl.pick_up_stop_id = wts.stop_id
463 AND wnd.initial_pickup_location_id = wts.stop_location_id
464 AND wts.trip_id = wt.trip_id
465 AND wnd.shipping_control='BUYER';
466 --
467 -- This cursor gets the dates for the ultimate dropoff location
468 --
469 CURSOR c_GetDTripLastUpdateDate(p_delId NUMBER) IS
470 SELECT wts.last_update_date, wdl.last_update_date, wt.last_update_date
471 FROM wsh_new_deliveries wnd,
472 wsh_delivery_legs wdl,
473 wsh_trip_stops wts,
474 wsh_trips wt
475 WHERE wnd.delivery_id = p_delId
476 AND wnd.delivery_id = wdl.delivery_id
477 AND wdl.pick_up_stop_id = wts.stop_id
478 AND wnd.ultimate_dropoff_location_id = wts.stop_location_id
479 AND wts.trip_id = wt.trip_id
480 AND wnd.shipping_control='BUYER';
481 --
482 CURSOR c_GetCarrierLastUpdateDate(p_delId NUMBER) IS
483 SELECT MAX(wcs.last_update_date), MAX(wocs.last_update_date)
484 FROM wsh_new_deliveries wnd, wsh_carrier_sites wcs,
485 wsh_org_carrier_sites wocs
486 WHERE wnd.organization_id = wocs.organization_id
487 AND wcs.carrier_id = wnd.carrier_id
488 AND wnd.delivery_id = p_delId;
489 --
490 l_deliveryId WSH_INBOUND_TXN_HISTORY.shipment_header_id%TYPE;
491 l_wndUpdateDate DATE;
492 l_wdaUpdateDate DATE;
493 l_wddUpdateDate DATE;
494 l_wdlUpdateDate DATE;
495 l_wtsUpdateDate DATE;
496 l_wtUpdateDate DATE;
497 l_wdlUpdateDate1 DATE;
498 l_wtsUpdateDate1 DATE;
499 l_wtUpdateDate1 DATE;
500 l_ibUpdateDate DATE;
501 l_wcsUpdateDate DATE;
502 l_wocsUpdateDate DATE;
503 l_changed BOOLEAN := FALSE;
504 --
505 l_api_version_number CONSTANT NUMBER := 1.0;
506 l_api_name CONSTANT VARCHAR2(30) := 'HasDeliveryInfoChanged';
507 l_return_status VARCHAR2(1);
508 --
509 l_debug_on BOOLEAN;
510 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'HasDeliveryInfoChanged';
511 --
512 BEGIN
513 --
514 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
515 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
516 --
517 IF l_debug_on IS NULL THEN
518 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
519 END IF;
520 --
521 IF l_debug_on THEN
522 WSH_DEBUG_SV.push(l_module_name);
523 WSH_DEBUG_SV.log(l_module_name,'p_api_version_number',P_API_VERSION_NUMBER);
524 WSH_DEBUG_SV.log(l_module_name,'p_init_msg_list',P_INIT_MSG_LIST);
525 WSH_DEBUG_SV.log(l_module_name,'p_commit',P_COMMIT);
526 WSH_DEBUG_SV.log(l_module_name,'Routing Response Number', p_in_rec.routingRespNum);
527 END IF;
528 --
529 IF NOT FND_API.compatible_api_call(
530 l_api_version_number,
531 p_api_version_number,
532 l_api_name,
533 G_PKG_NAME) THEN
534 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
535 END IF;
536 --
537 IF FND_API.to_boolean(p_init_msg_list) THEN
538 FND_MSG_PUB.initialize;
539 END IF;
540 --
541 -- Get last_update_date from WND and WTH
542 --
543 OPEN c_GetDelId(p_in_rec.routingRespNum);
544 FETCH c_GetDelId INTO l_deliveryId, l_ibUpdateDate, l_wndUpdateDate;
545 CLOSE c_GetDelId;
546 --
547 IF l_debug_on THEN
548 wsh_debug_sv.log(l_module_name, 'Delivery Id', l_deliveryId);
549 wsh_debug_sv.log(l_module_name, 'last update date in IB TXN history', l_ibUpdateDate);
550 wsh_debug_sv.log(l_module_name, 'WND Last Update Date', l_wndUpdateDate);
551 END IF;
552 --
553 -- Get last_update_dates of various entities tied to delivery
554 --
555 OPEN c_GetLastUpdateDates(l_deliveryId);
556 FETCH c_GetLastUpdateDates INTO l_wdaUpdateDate, l_wddUpdateDate;
557 CLOSE c_GetLastUpdateDates;
558 --
559 OPEN c_GetPTripLastUpdateDate(l_deliveryId);
560 FETCH c_GetPTripLastUpdateDate INTO l_wtsUpdateDate, l_wdlUpdateDate, l_wtUpdateDate;
561 CLOSE c_GetPTripLastUpdateDate;
562 --
563 OPEN c_GetDTripLastUpdateDate(l_deliveryId);
564 FETCH c_GetDTripLastUpdateDate INTO l_wtsUpdateDate1, l_wdlUpdateDate1, l_wtUpdateDate1;
565 CLOSE c_GetDTripLastUpdateDate;
566 --
567 OPEN c_GetCarrierLastUpdateDate(l_deliveryId);
568 FETCH c_GetCarrierLastUpdateDate INTO l_wcsUpdateDate, l_wocsUpdateDate;
569 CLOSE c_GetCarrierLastUpdateDate;
570 --
571 IF l_debug_on THEN
572 wsh_debug_sv.log(l_module_name, 'WND Update Date', l_wndUpdateDate);
573 wsh_debug_sv.log(l_module_name, 'WDA Update Date', l_wdaUpdateDate);
574 wsh_debug_sv.log(l_module_name, 'WDD Update Date', l_wddUpdateDate);
575 wsh_debug_sv.log(l_module_name, 'WTS Update Date using Pickup Stop', l_wtsUpdateDate);
576 wsh_debug_sv.log(l_module_name, 'WDL Update Date using Pickup Stop', l_wdlUpdateDate);
577 wsh_debug_sv.log(l_module_name, 'WT Update Date using Pickup Stop', l_wtUpdateDate);
578 wsh_debug_sv.log(l_module_name, 'WTS Update Date using Dropoff Stop', l_wtsUpdateDate1);
579 wsh_debug_sv.log(l_module_name, 'WDL Update Date using Dropoff Stop', l_wdlUpdateDate1);
580 wsh_debug_sv.log(l_module_name, 'WT Update Date using Dropoff Stop', l_wtUpdateDate1);
581 wsh_debug_sv.log(l_module_name, 'WCS Update Date', l_wcsUpdateDate);
582 wsh_debug_sv.log(l_module_name, 'WOCS Update Date', l_wocsUpdateDate);
583 END IF;
584 --
585 -- Compare the last_update_dates
586 --
587 IF l_ibUpdateDate < l_wndUpdateDate OR
588 l_ibUpdateDate < l_wdaUpdateDate OR
589 l_ibUpdateDate < l_wddUpdateDate OR
590 l_ibUpdateDate < l_wtsUpdateDate OR
591 l_ibUpdateDate < l_wdlUpdateDate OR
592 l_ibUpdateDate < l_wtUpdateDate OR
593 l_ibUpdateDate < l_wtsUpdateDate1 OR
594 l_ibUpdateDate < l_wdlUpdateDate1 OR
595 l_ibUpdateDate < l_wtUpdateDate1 OR
596 l_ibUpdateDate < l_wcsUpdateDate OR
597 l_ibUpdateDate < l_wocsUpdateDate THEN
598 l_changed := TRUE;
599 END IF;
600 --
601 x_out_rec.hasChanged := l_changed;
602 --
603 FND_MSG_PUB.Count_And_Get(
604 p_count => x_msg_count,
605 p_data => x_msg_data,
606 p_encoded => FND_API.G_FALSE);
607 --
608 IF l_debug_on THEN
609 wsh_debug_sv.log(l_module_name, 'l_changed', l_changed);
610 wsh_debug_sv.pop(l_module_name);
611 END IF;
612 --
613 EXCEPTION
614 --
615 WHEN FND_API.G_EXC_ERROR THEN
616 --
617 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
618 --
619 FND_MSG_PUB.Count_And_Get
620 (
621 p_count => x_msg_count,
622 p_data => x_msg_data,
623 p_encoded => FND_API.G_FALSE
624 );
625 --
626 IF l_debug_on THEN
627 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR',WSH_DEBUG_SV.C_EXCEP_LEVEL);
628 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
629 END IF;
630 --
631 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
632 --
633 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
634 --
635 FND_MSG_PUB.Count_And_Get
636 (
637 p_count => x_msg_count,
638 p_data => x_msg_data,
639 p_encoded => FND_API.G_FALSE
640 );
641 --
642 IF l_debug_on THEN
643 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_UNEXP_ERROR',
644 WSH_DEBUG_SV.C_EXCEP_LEVEL);
645 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
646 END IF;
647 --
648 WHEN OTHERS THEN
649 --
650 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
651 --
652 WSH_UTIL_CORE.ADD_MESSAGE(l_return_status,l_module_name);
653 WSH_UTIL_CORE.DEFAULT_HANDLER('WSH_PO_INTEGRATION_GRP.HasDeliveryInfoChanged',l_module_name);
654 --
655 IF l_debug_on THEN
656 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. '
657 || ' Oracle error message is '
658 || SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
659 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
660 END IF;
661 --
662 END HasDeliveryInfoChanged;
663
664 -- { IB-Phase-2
665 --=============================================================================
666 -- API name : validateASNReceiptShipFrom
667 -- Type : public.
668 -- Function :
669 -- Pre-reqs : None.
670 -- Parameters :
671 -- p_api_version_number IN NUMBER
672 -- p_init_msg_list IN VARCHAR2
673 -- p_in_rec IN WSH_PO_INTEGRATION_GRP.validateSF_in_rec_type
674 -- p_commit IN VARCHAR2
675 -- x_return_status OUT NOCOPY VARCHAR2
676 -- x_out_rec OUT WSH_PO_INTEGRATION_GRP.validateSF_out_rec_type
677 -- x_msg_count OUT NOCOPY NUMBER
678 -- x_msg_data OUT NOCOPY VARCHAR2
679 -- Comment :This API will be called whenever a ASN is created with a
680 -- ShipFromLocation on the IssupplierPortal Page. This API
681 -- determines whether the ASN can be created for the given
682 -- ShipFromLocation based on the following points. It returns
683 -- TRUE or FALSE to indicate this
684 -- TRUE - ASN can be created.
685 -- FALSE - ASN cannot be created.
686 -- a) IS the ShipFromLocationId passed through input parameter
687 -- p_in_rec a valid WSH Location.
688 -- AND
689 -- b) There is a open Delivery Line (for the input PO line and PO
690 -- Shipment Line) with the ShipFromLocation
691 -- as the one specified as the input parameter or has a
692 -- value of -1 as its ShipFromLocation. Return TRUE.
693 -- c) IF (b) above is false (no Delivery lines satisfy (b) ), then
694 -- check if there are open Delivery Lines for the input PO line
695 -- and PO Shipment Line). If so return FALSE, other wise return
696 -- TRUE.
697 --=============================================================================
698 PROCEDURE validateASNReceiptShipFrom
699 (
700 p_api_version_number IN NUMBER,
701 p_init_msg_list IN VARCHAR2,
702 p_in_rec IN WSH_PO_INTEGRATION_GRP.validateSF_in_rec_type,
703 p_commit IN VARCHAR2,
704 x_return_status OUT NOCOPY VARCHAR2,
705 x_out_rec OUT NOCOPY WSH_PO_INTEGRATION_GRP.validateSF_out_rec_type,
706 x_msg_count OUT NOCOPY NUMBER,
707 x_msg_data OUT NOCOPY VARCHAR2
708 )
709 IS
710
711 Cursor valid_wsh_sf_loc_csr(p_location_id NUMBER)
712 IS
713 Select wsh_location_id
714 from wsh_locations
715 where
716 source_location_id = p_location_id
717 and location_source_code = 'HZ';
718
719
720 Cursor open_line_with_sf_csr (p_shipment_line_id NUMBER,
721 p_po_line_id NUMBER,
722 p_location_id NUMBER)
723 IS
724 Select 1
725 from wsh_delivery_details
726 where
727 po_shipment_line_id = p_shipment_line_id
728 and (ship_from_location_id = p_location_id or ship_from_location_id = -1 )
729 and released_status = 'X'
730 and source_code = 'PO'
731 and source_line_id = p_po_line_id
732 and rownum=1;
733
734 Cursor open_line_without_sf_csr (p_shipment_line_id NUMBER,
735 p_po_line_id NUMBER)
736 IS
737 Select 1
738 from wsh_delivery_details
739 where
740 po_shipment_line_id = p_shipment_line_id
741 and released_status = 'X'
742 and source_code = 'PO'
743 and source_line_id = p_po_line_id
744 and rownum =1;
745
746
747 l_api_version_number NUMBER := 1.0;
748 l_api_name CONSTANT VARCHAR2(30) := 'validateASNReceiptShipFrom';
749 l_return_status VARCHAR2(1);
750
751 l_index NUMBER;
752 l_temp NUMBER;
753 l_wsh_location_id NUMBER;
754
755 --
756 l_debug_on BOOLEAN;
757 --
758 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'VALIDATEASNRECEIPTSHIPFROM';
759 --
760
761 BEGIN
762
763 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS ;
764 --
765 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
766 --
767 IF l_debug_on IS NULL
768 THEN
769 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
770 END IF;
771 --
772 --
773 -- Debug Statements
774 --
775 IF l_debug_on THEN
776 WSH_DEBUG_SV.push(l_module_name);
777 WSH_DEBUG_SV.log(l_module_name,'p_api_version_number',p_api_version_number);
778 WSH_DEBUG_SV.log(l_module_name,'p_init_msg_list',p_init_msg_list);
779 WSH_DEBUG_SV.log(l_module_name,'p_commit',p_commit);
780 WSH_DEBUG_SV.log(l_module_name,'p_in_rec.po_line_id_tbl.count',p_in_rec.po_line_id_tbl.count);
781 WSH_DEBUG_SV.log(l_module_name,'p_in_rec.po_shipment_line_id_tbl.count',p_in_rec.po_shipment_line_id_tbl.count);
782 WSH_DEBUG_SV.log(l_module_name,'p_in_rec.ship_from_location_id',p_in_rec.ship_from_location_id);
783 END IF;
784 --
785 IF NOT FND_API.compatible_api_call(
786 l_api_version_number,
787 p_api_version_number,
788 l_api_name,
789 G_PKG_NAME) THEN
790 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
791 END IF;
792 --
793 IF FND_API.to_boolean(p_init_msg_list) THEN
794 FND_MSG_PUB.initialize;
795 END IF;
796 --
797 x_out_rec.is_valid := TRUE;
798 --
799 IF p_in_rec.po_line_id_tbl.count = 0 or p_in_rec.po_shipment_line_id_tbl.count = 0
800 THEN
801 IF l_debug_on THEN
802 WSH_DEBUG_SV.logmsg(l_module_name,' Table count of Shipment Line IDs or PO Line IDs are not equal is/are Zero');
803 END IF;
804 raise FND_API.G_EXC_ERROR;
805 END IF;
806 --
807 IF p_in_rec.po_line_id_tbl.count <> p_in_rec.po_shipment_line_id_tbl.count THEN
808 IF l_debug_on THEN
809 WSH_DEBUG_SV.logmsg(l_module_name,' Table count of Shipment Line IDs and PO Line IDs are not equal');
810 END IF;
811 raise FND_API.G_EXC_ERROR;
812 END IF;
813 --
814 --
815 OPEN valid_wsh_sf_loc_csr(p_in_rec.ship_from_location_id);
816 FETCH valid_wsh_sf_loc_csr INTO l_wsh_location_id;
817 CLOSE valid_wsh_sf_loc_csr;
818 --
819 IF l_debug_on THEN
820 WSH_DEBUG_SV.log(l_module_name,'p_in_rec.ship_from_location_id',p_in_rec.ship_from_location_id);
821 WSH_DEBUG_SV.log(l_module_name,'l_wsh_location_id',l_wsh_location_id);
822 END IF;
823 --
824 IF l_wsh_location_id is NULL THEN
825 IF l_debug_on THEN
826 WSH_DEBUG_SV.logmsg(l_module_name,' Invalid ShipFromLocation ID from PO');
827 END IF;
828 --
829 FND_MESSAGE.SET_NAME('WSH', 'WSH_IB_INVALID_WSH_LOC');
830 WSH_UTIL_CORE.add_message (wsh_util_core.g_ret_sts_error,l_module_name);
831 --
832 RAISE FND_API.G_EXC_ERROR;
833 END IF;
834 --
835 l_index := p_in_rec.po_line_id_tbl.FIRST;
836 WHILE l_index is not null LOOP
837 IF l_debug_on THEN
838 WSH_DEBUG_SV.log(l_module_name,'p_in_rec.po_shipment_line_id_tbl(l_index)',p_in_rec.po_shipment_line_id_tbl(l_index));
839 WSH_DEBUG_SV.log(l_module_name,'p_in_rec.po_line_id_tbl(l_index)',p_in_rec.po_line_id_tbl(l_index));
840 END IF;
841 --
842 OPEN open_line_with_sf_csr (p_in_rec.po_shipment_line_id_tbl(l_index),
843 p_in_rec.po_line_id_tbl(l_index),
844 l_wsh_location_id);
845 FETCH open_line_with_sf_csr INTO l_temp;
846 --
847 IF open_line_with_sf_csr%NOTFOUND THEN
848 OPEN open_line_without_sf_csr(p_in_rec.po_shipment_line_id_tbl(l_index),
849 p_in_rec.po_line_id_tbl(l_index));
850 FETCH open_line_without_sf_csr INTO l_temp;
851 --
852 IF open_line_without_sf_csr%FOUND THEN
853 -- raise an error , no suitable open lines found
854 CLOSE open_line_without_sf_csr;
855 CLOSE open_line_with_sf_csr;
856 --
857 FND_MESSAGE.SET_NAME('WSH', 'WSH_IB_NO_OPEN_LINES');
858 WSH_UTIL_CORE.add_message (wsh_util_core.g_ret_sts_error,l_module_name);
859 --
860 RAISE FND_API.G_EXC_ERROR;
861 END IF;
862 CLOSE open_line_without_sf_csr;
863 END IF;
864 CLOSE open_line_with_sf_csr;
865 l_index := p_in_rec.po_line_id_tbl.NEXT(l_index);
866 END LOOP;
867
868
869 FND_MSG_PUB.Count_And_Get(
870 p_count => x_msg_count,
871 p_data => x_msg_data,
872 p_encoded => FND_API.G_FALSE);
873
874 IF FND_API.TO_BOOLEAN(p_commit) THEN
875 COMMIT WORK;
876 END IF;
877 --
878 -- Debug Statements
879 --
880 IF l_debug_on THEN
881 WSH_DEBUG_SV.pop(l_module_name);
882 END IF;
883 --
884
885 EXCEPTION
886 WHEN FND_API.G_EXC_ERROR THEN
887 --
888 x_out_rec.is_valid := FALSE;
889 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
890 --
891 FND_MSG_PUB.Count_And_Get
892 (
893 p_count => x_msg_count,
894 p_data => x_msg_data,
895 p_encoded => FND_API.G_FALSE
896 );
897 --
898 IF l_debug_on THEN
899 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR',WSH_DEBUG_SV.C_EXCEP_LEVEL);
900 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
901 END IF;
902 --
903 WHEN OTHERS THEN
904 --
905 x_out_rec.is_valid := FALSE;
906 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
907 --
908 WSH_UTIL_CORE.DEFAULT_HANDLER('WSH_PO_INTEGRATION_GRP.validateASNReceiptShipFrom',l_module_name);
909 --
910 IF l_debug_on THEN
911 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. '
912 || ' Oracle error message is '
913 || SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
914 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
915 END IF;
916 --
917 END validateASNReceiptShipFrom;
918 --IB-Phase-2
919
920 END WSH_PO_INTEGRATION_GRP;
[Home] [Help]