1 PACKAGE BODY wsh_interface_validations_pkg AS
2 /* $Header: WSHINVDB.pls 120.2 2006/10/18 18:59:08 bsadri noship $ */
3
4 /*==============================================================================
5
6 PROCEDURE NAME: Validate_Document
7
8 This Procedure is called from the XML Gateway, even before data is populated
9 into the interface tables.
10 This Procedure checks for basic validations in the incoming XML message.
11
12 ** When the 940 or 945 comes in, it checks if the message received is not
13 duplicate.
14
15 ** When the 940 Cancellation comes in at the TPW instance, it checks
16 if the corresponding 940 add exists.
17
18 ** When the 945 comes in at the Supplier Instance, it checks if the
19 corresponding 940 out exists.
20
21 ==============================================================================*/
22
23 --
24 G_PKG_NAME CONSTANT VARCHAR2(50) := 'WSH_INTERFACE_VALIDATIONS_PKG';
25 --
26 PROCEDURE validate_document (
27 p_doc_type IN VARCHAR2,
28 p_doc_number IN VARCHAR2,
29 p_trading_partner_Code IN VARCHAR2,
30 p_action_type IN VARCHAR2,
31 p_doc_direction IN VARCHAR2,
32 p_orig_document_number IN VARCHAR2,
33 x_trading_partner_ID OUT NOCOPY NUMBER,
34 x_valid_doc OUT NOCOPY VARCHAR2,
35 x_return_status OUT NOCOPY VARCHAR2
36 )
37 IS
38 p_duplicate VARCHAR2 (1);
39 p_940_exists VARCHAR2 (1);
40 invalid_doc_number EXCEPTION;
41 invalid_tp EXCEPTION;
42 invalid_doc_direction EXCEPTION;
43 invalid_doc_type EXCEPTION;
44 invalid_action_type EXCEPTION;
45 --
46 l_debug_on BOOLEAN;
47 --
48 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'VALIDATE_DOCUMENT';
49 --
50 BEGIN
51 --
52 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
53 --
54 IF l_debug_on IS NULL
55 THEN
56 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
57 END IF;
58 --
59 IF l_debug_on THEN
60 wsh_debug_sv.push (l_module_name, 'VALIDATE_DOCUMENT');
61 wsh_debug_sv.log (l_module_name, 'DOCUMENT TYPE', p_doc_type);
62 wsh_debug_sv.log (l_module_name, 'DOCUMENT NUMBER', p_doc_number);
63 wsh_debug_sv.log (l_module_name, 'TRADING PARTNER', p_trading_partner_Code);
64 wsh_debug_sv.log (l_module_name, 'ACTION TYPE', p_action_type);
65 wsh_debug_sv.log (l_module_name, 'DOCUMENT DIRECTION', p_doc_direction);
66 wsh_debug_sv.log (l_module_name, 'ORIGINAL DOC NUMBER', p_orig_document_number);
67 END IF;
68
69 -- Check if the values passed are Not Null and valid
70
71 IF (p_doc_number IS NULL)
72 THEN
73 RAISE invalid_doc_number;
74 END IF;
75
76 IF (p_trading_partner_Code IS NULL)
77 THEN
78 RAISE invalid_tp;
79 END IF;
80
81 IF (p_doc_direction IS NULL)
82 THEN
83 RAISE invalid_doc_direction;
84 END IF;
85
86 IF ((p_doc_type IS NULL) OR (p_doc_type NOT IN ('SR', 'SA'))) THEN
87 RAISE invalid_doc_type;
88 END IF;
89
90 IF ((p_action_type IS NULL) OR (p_action_type NOT IN ('A', 'D'))) THEN
91 RAISE invalid_action_type;
92 END IF;
93
94 IF ((p_doc_direction IS NULL) OR (p_doc_direction NOT IN ('I', 'O'))) THEN
95 RAISE invalid_Doc_direction;
96 END IF;
97
98 IF l_debug_on THEN
99 wsh_debug_sv.logmsg(l_module_name, 'Valid parameters for Validate Document');
100 END IF;
101 /* Derive Trading_Partner_Id based on Trading_Partner_code */
102
103 BEGIN
104 -- performance repository bug 4891939
105 -- replace org_organization_definitions with mtl_parameters and
106 -- hr_organization_information
107 SELECT mp.organization_id
108 INTO x_trading_partner_ID
109 from mtl_parameters mp, hr_organization_information hoi
110 where mp.organization_id = hoi.organization_id and
111 hoi.org_information1 = 'INV' and
112 hoi.org_information2 = 'Y' and
113 hoi.org_information_context = 'CLASS' and
114 mp.organization_code = p_trading_partner_Code;
115
116 EXCEPTION
117 WHEN NO_DATA_FOUND THEN
118 --
119 IF l_debug_on THEN
120 wsh_debug_sv.logmsg(l_module_name, 'Inside No Data Found Exception to Derive TP ID');
121 END IF;
122 --
123 RAISE FND_API.G_EXC_ERROR;
124 --
125 WHEN TOO_MANY_ROWS THEN
126 --
127 IF l_debug_on THEN
128 wsh_debug_sv.logmsg (l_module_name, 'Inside Too many rows Exception to derive TP ID');
129 END IF;
130 --
131 RAISE FND_API.G_EXC_ERROR;
132 END;
133
134 IF l_debug_on THEN
135 wsh_debug_sv.log(l_module_name, 'Derived TP ID successfully', x_trading_partner_ID);
136 END IF;
137
138 /* Check if the current record is a duplicate */
139 BEGIN
140 SELECT 'X'
141 INTO p_duplicate
142 FROM wsh_transactions_history
143 WHERE document_type = p_doc_type
144 AND document_number = p_doc_number
145 AND action_type = p_action_type
146 AND trading_partner_id = x_trading_partner_id
147 AND document_direction = p_doc_direction;
148
149 IF (p_duplicate = 'X')
150 THEN
151 --
152 IF l_debug_on THEN
153 wsh_debug_sv.log(l_module_name, 'EXCEPTION: Found duplicate', p_duplicate);
154 END IF;
155 RAISE FND_API.G_EXC_ERROR;
156 --
157 END IF;
158
159 EXCEPTION
160 WHEN NO_DATA_FOUND THEN
161 --
162 x_return_status := wsh_util_core.g_ret_sts_success;
163 x_valid_doc := fnd_api.g_true;
164 --
165 WHEN TOO_MANY_ROWS THEN
166 --
167 IF l_debug_on THEN
168 wsh_debug_sv.logmsg(l_module_name, 'EXCEPTION: Too many rows when checking for duplicates');
169 END IF;
170 --
171 RAISE FND_API.G_EXC_ERROR;
172 --
173 END;
174
175 IF l_debug_on THEN
176 wsh_debug_sv.log(l_module_name, 'After checking for duplicates, x_return_status', X_Return_Status);
177 END IF;
178
179 IF (p_doc_type = 'SR') THEN
180
181 IF (p_action_type = 'D') THEN
182
183 /* Check if the Corresponding 940 Add exists when a 940 Cancellation comes in */
184
185 BEGIN
186 SELECT 'X'
187 INTO p_940_exists
188 FROM wsh_transactions_history
189 WHERE document_number = p_orig_document_number
190 AND document_type = 'SR'
191 AND document_direction = 'I'
192 AND action_type = 'A';
193
194 IF (p_940_exists = 'X')
195 THEN
196 x_return_status := wsh_util_core.g_ret_sts_success;
197 x_valid_doc := fnd_api.g_true;
198 IF l_debug_on THEN
199 wsh_debug_sv.log(l_module_name, '940 Add Exists for the 940 Cancellation sent, Return Status',X_Return_Status );
200 END IF;
201 END IF;
202 EXCEPTION
203 WHEN NO_DATA_FOUND
204 THEN
205 IF l_debug_on THEN
206 WSH_DEBUG_SV.logmsg(l_module_name, 'EXCEPTION: No corresponding 940-Add exists');
207 END IF;
208 --
209 RAISE FND_API.G_EXC_ERROR;
210 END;
211 END IF; -- End of If (P_ACTION_TYPE = 'D') Then
212
213 IF l_debug_on THEN
214 wsh_debug_sv.log(l_module_name, 'Return Status after checking for 940 Add Exists when 940 Cancellation. Return Status', X_Return_Status );
215 END IF;
216
217 ELSIF (p_doc_type = 'SA') THEN
218
219 IF l_debug_on THEN
220 wsh_debug_sv.log(l_module_name, 'Before checking for 940 out when 945 comes in, x_return_status ',x_return_status );
221 END IF;
222
223 /* Check if the Corresponding 940 Out exists when a 945 Comes in */
224 BEGIN
225 SELECT 'X'
226 INTO p_940_exists
227 FROM wsh_transactions_history
228 WHERE document_number = p_orig_document_number
229 AND document_type = 'SR'
230 AND document_direction = 'O'
231 AND action_type = 'A';
232
233 IF (p_940_exists = 'X')
234 THEN
235 x_return_status := wsh_util_core.g_ret_sts_success;
236 x_valid_doc := fnd_api.g_true;
237 END IF;
238 EXCEPTION
239 WHEN NO_DATA_FOUND
240 THEN
241 --
242 IF l_debug_on THEN
243 wsh_debug_sv.logmsg(l_module_name, 'EXCEPTION: No data found when checking for 940-O when
244 945-IN comes in');
245 END IF;
246 --
247 RAISE FND_API.G_EXC_ERROR;
248 --
249 END;
250 END IF; -- End of If (P_DOC_TYPE = 'SR') Then
251
252 IF l_debug_on THEN
253 wsh_debug_sv.log (l_module_name, 'Return Status from Validate Document', X_Return_Status );
254 wsh_debug_sv.pop (l_module_name);
255 END IF;
256
257 EXCEPTION
258 WHEN FND_API.G_EXC_ERROR THEN
259 --
260 x_return_status := WSH_UTIL_CORE.g_ret_sts_error;
261 x_Valid_Doc := FND_API.G_FALSE;
262 --
263 IF l_debug_on THEN
264 --
265 WSH_DEBUG_SV.log(l_module_name, 'x_return_Status from Validate Document',X_Return_Status );
266 WSH_DEBUG_SV.log(l_module_name, 'x_valid_doc', x_Valid_Doc);
267 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured',
268 WSH_DEBUG_SV.C_EXCEP_LEVEL);
269 WSH_DEBUG_SV.pop(l_module_name, 'EXCEPTION:FND_API.G_EXC_ERROR');
270 --
271 END IF;
272 --
273 WHEN invalid_doc_number
274 THEN
275 x_return_status := wsh_util_core.g_ret_sts_error;
276 x_valid_doc := fnd_api.g_false;
277 IF l_debug_on THEN
278 WSH_DEBUG_SV.logmsg(l_module_name,'invalid_doc_number exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
279 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:invalid_doc_number');
280 END IF;
281 WHEN invalid_tp
282 THEN
283 x_return_status := wsh_util_core.g_ret_sts_error;
284 x_valid_doc := fnd_api.g_false;
285 IF l_debug_on THEN
286 WSH_DEBUG_SV.logmsg(l_module_name,'invalid_tp exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
287 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:invalid_tp');
288 END IF;
289 WHEN invalid_doc_direction
290 THEN
291 x_return_status := wsh_util_core.g_ret_sts_error;
292 x_valid_doc := fnd_api.g_false;
293 IF l_debug_on THEN
294 WSH_DEBUG_SV.logmsg(l_module_name,'invalid_doc_direction exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
295 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:invalid_doc_direction');
296 END IF;
297 WHEN invalid_doc_type
298 THEN
299 x_return_status := wsh_util_core.g_ret_sts_error;
300 x_valid_doc := fnd_api.g_false;
301 IF l_debug_on THEN
302 WSH_DEBUG_SV.logmsg(l_module_name,'invalid_doc_type exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
303 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:invalid_doc_type');
304 END IF;
305 WHEN invalid_action_type
306 THEN
307 x_return_status := wsh_util_core.g_ret_sts_error;
308 x_valid_doc := fnd_api.g_false;
309 IF l_debug_on THEN
310 WSH_DEBUG_SV.logmsg(l_module_name,'invalid_action_type exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
311 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:invalid_action_type');
312 END IF;
313 WHEN OTHERS
314 THEN
315 x_return_status := wsh_util_core.g_ret_sts_unexp_error;
316 x_valid_doc := fnd_api.g_false;
317 IF l_debug_on THEN
318 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,
319 WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
320 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
321 END IF;
322 END validate_document;
323
324
325 /*==============================================================================
326
327 PROCEDURE NAME: Validate_Deliveries
328
329 This Procedure is called from the Wsh_Inbound_Ship_Advice_Pkg.Process_Ship_Advice,
330 after data is populated into the interface tables.
331
332 This Procedure checks if the Delivery and Delivery Details received in the 945,
333 exists in the Supplier Instance base tables.
334
335 ==============================================================================*/
336
337 PROCEDURE validate_deliveries (
338 p_delivery_id IN NUMBER,
339 x_return_status OUT NOCOPY VARCHAR2
340 )
341 IS
342 x_delivery_exists VARCHAR2 (1);
343 x_delivery_detail_exists VARCHAR2 (1);
344 invalid_delivery_id EXCEPTION;
345 invalid_delivery_detail_id EXCEPTION;
346
347 CURSOR delivery_detail_int_cur (p_delivery_id NUMBER)
348 IS
349 SELECT DISTINCT wdd.delivery_detail_id
350 FROM wsh_del_details_interface wdd,
351 wsh_del_assgn_interface wda
352 WHERE wdd.delivery_detail_interface_id = wda.delivery_detail_interface_id
353 AND wdd.container_flag = 'N'
354 AND wda.delivery_id = p_delivery_id
355 AND wda.interface_action_code = '94X_INBOUND'
356 AND wdd.interface_action_code = '94X_INBOUND';
357 --
358 l_debug_on BOOLEAN;
359 --
360 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'VALIDATE_DELIVERIES';
361 --
362 BEGIN
363 --
364 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
365 --
366 IF l_debug_on IS NULL
367 THEN
368 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
369 END IF;
370 --
371 IF l_debug_on THEN
372 wsh_debug_sv.push (l_module_name, 'VALIDATE_DELIVERIES');
373 wsh_debug_sv.log (l_module_name, 'DELIVERY ID', p_delivery_id);
374 END IF;
375
376 /* Check if the Delivery ID from Interface table exists in WSH_NEW_DELIVERIES Table */
377
378 IF (p_delivery_id IS NOT NULL)
379 THEN
380 BEGIN
381 SELECT 'X'
382 INTO x_delivery_exists
383 FROM wsh_new_deliveries
384 WHERE delivery_id = p_delivery_id;
385 EXCEPTION
386 WHEN NO_DATA_FOUND
387 THEN
388 RAISE invalid_delivery_id;
389 END;
390
391 IF l_debug_on THEN
392 wsh_debug_sv.log (l_module_name, 'Valid parameters for Validate Deliveries. Return Status : ',X_Return_Status );
393 END IF;
394
395 /* If the Delivery exists in WSH_New_Deliveries, Get the corresponding Delivery_Detail_IDs
396 from the interface tables and check if they exist in the base tables, and if they are assigned
397 to the same delivery */
398
399 IF (x_delivery_exists = 'X')
400 THEN
401 BEGIN
402 FOR delivery_detail_int_rec IN delivery_detail_int_cur (p_delivery_id) LOOP
403 BEGIN
404 SELECT 'X'
405 INTO x_delivery_detail_exists
406 FROM wsh_delivery_details wdd,
407 wsh_delivery_assignments_v wda
408 WHERE wdd.delivery_detail_id = wda.delivery_detail_id
409 AND wdd.delivery_detail_id = delivery_detail_int_rec.delivery_detail_id
410 AND wda.delivery_id = p_delivery_id;
411
412 x_return_status := wsh_util_core.g_ret_sts_success;
413 EXCEPTION
414 WHEN NO_DATA_FOUND
415 THEN
416 RAISE invalid_delivery_detail_id;
417 END; -- End of Begin
418 END LOOP; -- End of For Delivery ....
419 END;
420 END IF; -- End of if X_Delivery_Exists
421
422 ELSIF (p_delivery_id IS NULL) THEN
423 x_return_status := wsh_util_core.g_ret_sts_error;
424 END IF; -- if p_delivery_id is null
425
426 IF l_debug_on THEN
427 wsh_debug_sv.log (l_module_name,'940 Add Exists for the 940 Cancellation sent. Return Status',X_Return_Status );
428 wsh_debug_sv.pop (l_module_name);
429 END IF;
430 EXCEPTION
431 WHEN invalid_delivery_id
432 THEN
433 x_return_status := wsh_util_core.g_ret_sts_error;
434 IF l_debug_on THEN
435 WSH_DEBUG_SV.logmsg(l_module_name,'invalid_delivery_id exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
436 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:invalid_delivery_id');
437 END IF;
438 WHEN invalid_delivery_detail_id
439 THEN
440 x_return_status := wsh_util_core.g_ret_sts_error;
441 IF l_debug_on THEN
442 WSH_DEBUG_SV.logmsg(l_module_name,'invalid_delivery_detail_id exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
443 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:invalid_delivery_detail_id');
444 END IF;
445 WHEN OTHERS
446 THEN
447 x_return_status := wsh_util_core.g_ret_sts_unexp_error;
448 IF l_debug_on THEN
449 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,
450 WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
451 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
452 END IF;
453 END validate_deliveries;
454
455
456 /*==============================================================================
457
458 PROCEDURE NAME: Compare_Ship_Request_Advice
459
460 This Procedure is called from the Wsh_Inbound_Ship_Advice_Pkg.Process_Ship_Advice,
461 after data is populated into the interface tables.
462
463 This Procedure checks if the key data elements like Ship To, Inventory Item ID etc
464 have been modified by Third Party Warehouse.
465
466 ==============================================================================*/
467
468 PROCEDURE compare_ship_request_advice (
469 p_delivery_id IN NUMBER,
470 x_return_status OUT NOCOPY VARCHAR2
471 )
472 IS
473 CURSOR delivery_cur
474 IS
475 SELECT customer_id, initial_pickup_location_id,
476 intmed_ship_to_location_id, organization_id,
477 ultimate_dropoff_location_id
478 FROM wsh_new_deliveries
479 WHERE delivery_id = p_delivery_id;
480
481 CURSOR delivery_detail_cur (p_delivery_detail_id NUMBER)
482 IS
483 SELECT DISTINCT delivery_detail_id, customer_id, customer_item_id,
484 deliver_to_location_id, intmed_ship_to_location_id,
485 inventory_item_id, organization_id, ship_from_location_id,
486 ship_to_location_id
487 FROM wsh_delivery_details
488 WHERE delivery_detail_id = p_delivery_detail_id;
489
490 CURSOR delivery_int_cur
491 IS
492 SELECT customer_id, initial_pickup_location_id,
493 intmed_ship_to_location_id, organization_id,
494 ultimate_dropoff_location_id
495 FROM wsh_new_del_interface
496 WHERE delivery_id = p_delivery_id
497 AND INTERFACE_ACTION_CODE ='94X_INBOUND';
498
499 CURSOR delivery_detail_int_cur (p_delivery_id NUMBER)
500 IS
501 SELECT DISTINCT wdd.delivery_detail_id, wdd.customer_id, wdd.customer_item_id,
502 wdd.deliver_to_location_id, wdd.intmed_ship_to_location_id,
503 wdd.inventory_item_id, wdd.organization_id,
504 wdd.ship_from_location_id, wdd.ship_to_location_id
505 FROM wsh_del_assgn_interface wda, wsh_del_details_interface wdd
506 WHERE wda.delivery_id = p_delivery_id
507 AND wda.delivery_detail_interface_id = wdd.delivery_detail_interface_id
508 AND wdd.container_flag = 'N'
509 AND WDD.INTERFACE_ACTION_CODE = '94X_INBOUND'
510 AND WDA.INTERFACE_ACTION_CODE ='94X_INBOUND';
511
512 delivery delivery_cur%ROWTYPE;
513 delivery_int delivery_cur%ROWTYPE;
514 delivery_detail_int delivery_detail_cur%ROWTYPE;
515 invalid_delivery EXCEPTION;
516 data_changed EXCEPTION;
517 --
518 l_debug_on BOOLEAN;
519 --
520 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'COMPARE_SHIP_REQUEST_ADVICE';
521 --
522 BEGIN
523 --
524 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
525 --
526 IF l_debug_on IS NULL
527 THEN
528 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
529 END IF;
530 --
531 IF l_debug_on THEN
532 wsh_debug_sv.push (l_module_name, 'Compare Ship Request Advice');
533 wsh_debug_sv.log (l_module_name, 'DELIVERY ID', p_delivery_id);
534 END IF;
535
536 BEGIN
537 IF (p_delivery_id IS NOT NULL) THEN
538 OPEN delivery_cur;
539 FETCH delivery_cur INTO delivery;
540
541 IF (delivery_cur%NOTFOUND) THEN
542 RAISE invalid_delivery;
543 END IF;
544
545 OPEN delivery_int_cur;
546 FETCH delivery_int_cur INTO delivery_int;
547
548 IF (delivery_int_cur%NOTFOUND)
549 THEN
550 RAISE invalid_delivery;
551 END IF;
552
553 IF l_debug_on THEN
554 wsh_debug_sv.log (l_module_name, 'Valid values for Compare Ship Request Advice');
555 END IF;
556
557 /* Compare values of the Delivery */
558
559 IF (NVL (delivery.customer_id, 0) <> NVL (delivery_int.customer_id, 0))
560 OR (NVL (delivery.initial_pickup_location_id, 0) <> NVL (delivery_int.initial_pickup_location_id, 0))
561 OR (NVL (delivery.intmed_ship_to_location_id, 0) <> NVL (delivery_int.intmed_ship_to_location_id, 0))
562 OR (NVL (delivery.organization_id, 0) <> NVL (delivery_int.organization_id, 0))
563 OR (NVL (delivery.ultimate_dropoff_location_id, 0) <> NVL (delivery_int.ultimate_dropoff_location_id, 0))
564 THEN
565 RAISE data_changed;
566 ELSE
567
568 IF l_debug_on THEN
569 wsh_debug_sv.log (l_module_name, 'Compare Ship Request. Data Did Not Change for Delivery');
570 END IF;
571
572 /* Compare values of the Delivery Details */
573
574 FOR delivery_detail_int IN delivery_detail_int_cur (p_delivery_id)
575 LOOP
576 IF l_debug_on THEN
577 wsh_debug_sv.log (l_module_name, 'Delivery Detail Interface ID ',delivery_detail_int.delivery_detail_id);
578 END IF;
579 FOR delivery_detail IN delivery_detail_cur (delivery_detail_int.delivery_detail_id)
580 LOOP
581 IF l_debug_on THEN
582 wsh_debug_sv.log (l_module_name, 'Delivery Detail ID ',delivery_detail.delivery_detail_id);
583 END IF;
584
585 IF (NVL (delivery_detail.customer_id, 0) <> NVL (delivery_detail_int.customer_id, 0))
586 OR (NVL (delivery_detail.customer_item_id, 0) <> NVL (delivery_detail_int.customer_item_id, 0))
587 OR (NVL (delivery_detail.deliver_to_location_id, 0) <> NVL (delivery_detail_int.deliver_to_location_id, 0))
588 OR (NVL (delivery_detail.intmed_ship_to_location_id, 0) <> NVL (delivery_detail_int.intmed_ship_to_location_id, 0))
589 OR (NVL (delivery_detail.inventory_item_id, 0) <> NVL (delivery_detail_int.inventory_item_id, 0))
590 OR (NVL (delivery_detail.organization_id, 0) <> NVL (delivery_detail_int.organization_id, 0))
591 OR (NVL (delivery_detail.ship_from_location_id, 0) <> NVL (delivery_detail_int.ship_from_location_id, 0))
592 OR (NVL (delivery_detail.ship_to_location_id, 0) <> NVL (delivery_detail_int.ship_to_location_id, 0))
593 THEN
594 RAISE data_changed;
595 ELSE
596 IF l_debug_on THEN
597 wsh_debug_sv.log (l_module_name, 'Compare Ship Request. Data Did Not Change for Delivery Details');
598 END IF;
599
600 x_return_status := wsh_util_core.g_ret_sts_success;
601 END IF; -- if delivery_detail checks
602 END LOOP;
603 END LOOP;
604 END IF; -- if delivery checks
605
606 IF (delivery_int_cur%ISOPEN)
607 THEN
608 CLOSE delivery_int_cur;
609 END IF;
610
611 IF (delivery_cur%ISOPEN)
612 THEN
613 CLOSE delivery_cur;
614 END IF;
615
616 IF (delivery_detail_int_cur%ISOPEN)
617 THEN
618 CLOSE delivery_detail_int_cur;
619 END IF;
620 ELSIF p_delivery_id IS NULL
621 THEN
622 RAISE invalid_delivery;
623 END IF; -- if p_delivery_id is not null
624
625 IF l_debug_on THEN
626 wsh_debug_sv.log (l_module_name, 'Compare Ship Request. Return Status :'||X_Return_Status);
627 wsh_debug_sv.pop (l_module_name);
628 END IF;
629
630 EXCEPTION
631 WHEN invalid_delivery
632 THEN
633 x_return_status := wsh_util_core.g_ret_sts_error;
634 IF l_debug_on THEN
635 WSH_DEBUG_SV.logmsg(l_module_name,'invalid_delivery exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
636 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:invalid_delivery');
637 END IF;
638 WHEN data_changed
639 THEN
640 x_return_status := wsh_util_core.g_ret_sts_error;
641 IF l_debug_on THEN
642 WSH_DEBUG_SV.logmsg(l_module_name,'data_changed exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
643 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:data_changed');
644 END IF;
645 WHEN OTHERS
646 THEN
647 x_return_status := wsh_util_core.g_ret_sts_unexp_error;
648 IF l_debug_on THEN
649 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,
650 WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
651 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
652 END IF;
653 END;
654
655 END compare_ship_request_advice;
656
657
658 /*==============================================================================
659
660 PROCEDURE NAME: Log_Interface_Errors
661
662 This Procedure is called from various procedure whenever an error is detected in
663 the data elements.
664
665 This Procedure accepts upto 6 different tokens and Values Concatenates and stores
666 the resulting message text in WSH_INTERFACE_ERRORS table.
667 ==============================================================================*/
668
669 PROCEDURE log_interface_errors (
670 p_interface_errors_rec IN interface_errors_rec_type,
671 p_msg_data IN VARCHAR2 DEFAULT NULL,
672 p_api_name IN VARCHAR2,
673 x_return_status OUT NOCOPY VARCHAR2
674 )
675 IS
676 pragma AUTONOMOUS_TRANSACTION;
677 invalid_parameters EXCEPTION;
678 l_text VARCHAR2 (4000);
679 l_text_token VARCHAR2 (4000);
680 l_msg_count NUMBER;
681 l_msg_data VARCHAR2(4000);
682 l_msg_details VARCHAR2(4000);
683 --
684 l_debug_on BOOLEAN;
685 --
686 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'LOG_INTERFACE_ERRORS';
687 --
688 BEGIN
689 --
690 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
691 --
692 IF l_debug_on IS NULL
693 THEN
694 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
695 END IF;
696 --
697 IF l_debug_on THEN
698 wsh_debug_sv.push(l_module_name, 'Log_Interface_Errors');
699 wsh_debug_sv.log (l_module_name, 'Interface table name', p_interface_errors_rec.p_interface_table_name);
700 wsh_debug_sv.log (l_module_name, 'Interface Id', p_interface_errors_rec.p_interface_id);
701 wsh_debug_sv.log (l_module_name, 'Message Name', p_interface_errors_rec.p_message_name);
702 END IF;
703
704 IF (p_msg_data IS NULL) THEN
705 wsh_util_core.get_messages('Y', l_msg_data, l_msg_details, l_msg_count);
706
707 IF (l_msg_data IS NULL) THEN
708 fnd_message.set_name ('WSH', 'WSH_ERROR_IN_API');
709 fnd_message.set_token ('API_NAME',p_api_name);
710 l_text := fnd_message.get;
711 ELSE
712 IF (l_msg_count >1 ) THEN
713 l_text := l_msg_details;
714 ELSE
715 l_text := l_msg_data;
716 END IF;
717 END IF;
718
719 ELSE
720 l_text := p_msg_data;
721 END IF;
722
723
724 IF (p_interface_errors_rec.p_interface_table_name IS NOT NULL)
725 AND (p_interface_errors_rec.p_interface_id IS NOT NULL) THEN
726
727 IF l_debug_on THEN
728 wsh_debug_sv.log (l_module_name, 'Log Interface Errors. Valid Parameters');
729 END IF;
730
731 IF (p_interface_errors_rec.p_message_name IS NOT NULL) THEN
732 -- Build the Error message string.
733 fnd_message.set_name ('WSH', p_interface_errors_rec.p_message_name);
734
735 -- Replace the tokens with Values.
736 IF (p_interface_errors_rec.p_token1 IS NOT NULL)
737 THEN
738 fnd_message.set_token (p_interface_errors_rec.p_token1,p_interface_errors_rec.p_value1);
739 END IF;
740
741 IF (p_interface_errors_rec.p_token2 IS NOT NULL)
742 THEN
743 fnd_message.set_token (p_interface_errors_rec.p_token2,p_interface_errors_rec.p_value2);
744 END IF;
745
746 IF (p_interface_errors_rec.p_token3 IS NOT NULL)
747 THEN
748 fnd_message.set_token (p_interface_errors_rec.p_token3,p_interface_errors_rec.p_value3);
749 END IF;
750
751 IF (p_interface_errors_rec.p_token4 IS NOT NULL)
752 THEN
753 fnd_message.set_token (p_interface_errors_rec.p_token4,p_interface_errors_rec.p_value4);
754 END IF;
755
756 IF (p_interface_errors_rec.p_token5 IS NOT NULL)
757 THEN
758 fnd_message.set_token (p_interface_errors_rec.p_token5,p_interface_errors_rec.p_value5);
759 END IF;
760
761 IF (p_interface_errors_rec.p_token6 IS NOT NULL)
762 THEN
763 fnd_message.set_token (p_interface_errors_rec.p_token6,p_interface_errors_rec.p_value6);
764 END IF;
765
766 IF (p_interface_errors_rec.p_token7 IS NOT NULL)
767 THEN
768 fnd_message.set_token (p_interface_errors_rec.p_token7,p_interface_errors_rec.p_value7);
769 END IF;
770
771 IF (p_interface_errors_rec.p_token8 IS NOT NULL)
772 THEN
773 fnd_message.set_token (p_interface_errors_rec.p_token8,p_interface_errors_rec.p_value8);
774 END IF;
775
776 IF (p_interface_errors_rec.p_token9 IS NOT NULL)
777 THEN
778 fnd_message.set_token (p_interface_errors_rec.p_token9,p_interface_errors_rec.p_value9);
779 END IF;
780
781 IF (p_interface_errors_rec.p_token10 IS NOT NULL)
782 THEN
783 fnd_message.set_token (p_interface_errors_rec.p_token10,p_interface_errors_rec.p_value10);
784 END IF;
785
786 IF (p_interface_errors_rec.p_token11 IS NOT NULL)
787 THEN
788 fnd_message.set_token (p_interface_errors_rec.p_token11,p_interface_errors_rec.p_value11);
789 END IF;
790
791 --Retrieve the error message.
792 l_text_token := fnd_message.get;
793
794 END IF; -- if p_message_name is not null
795
796
797 IF (l_text_token IS NOT NULL) THEN
798 IF ( length(l_text)+length(l_text_token) < 3997 ) THEN
799 l_text := l_text ||' , '||l_text_token;
800 END IF;
801 END IF;
802
803
804 IF l_debug_on THEN
805 wsh_debug_sv.log (l_module_name, 'Text: ', l_text);
806 END IF;
807 -- Insert error record in WSH_Interface_errors table.
808 -- We need the check for l_text because the column error_message
809 -- is a Not_Null column.
810
811 IF (l_text IS NOT NULL) THEN
812 IF l_debug_on THEN
813 wsh_debug_sv.log (l_module_name, 'Error Message', l_text);
814 wsh_debug_sv.log (l_module_name, 'Inserting into wsh_interface_errors');
815 END IF;
816
817 INSERT INTO wsh_interface_errors
818 (interface_error_id,
819 interface_table_name,
820 interface_id, error_message, creation_date,
821 created_by, last_update_date, last_updated_by,
822 last_update_login,INTERFACE_ACTION_CODE)
823 VALUES (wsh_interface_errors_s.NEXTVAL,
824 p_interface_errors_rec.p_interface_table_name,
825 p_interface_errors_rec.p_interface_id, l_text, SYSDATE,
826 fnd_global.user_id, SYSDATE, fnd_global.user_id,
827 fnd_global.user_id,'94X_INBOUND');
828
829 COMMIT;
830
831 ELSE
832 IF l_debug_on THEN
833 wsh_debug_sv.log (l_module_name, 'Text for error_message is null');
834 END IF;
835 END IF; -- if l_text is not null
836 x_return_status := wsh_util_core.g_ret_sts_success;
837 ELSE
838 RAISE invalid_parameters;
839 END IF;
840
841 IF l_debug_on THEN
842 wsh_debug_sv.log (l_module_name, 'Log Interface Errors. Return Status. :'||X_Return_Status);
843 wsh_debug_sv.pop (l_module_name);
844 END IF;
845 EXCEPTION
846 WHEN invalid_parameters
847 THEN
848 x_return_status := wsh_util_core.g_ret_sts_error;
849 IF l_debug_on THEN
850 WSH_DEBUG_SV.logmsg(l_module_name,'RECORD_LOCKED exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
851 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:RECORD_LOCKED');
852 END IF;
853 WHEN OTHERS
854 THEN
855 x_return_status := wsh_util_core.g_ret_sts_unexp_error;
856 IF l_debug_on THEN
857 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,
858 WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
859 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
860 END IF;
861
862 END log_interface_errors;
863 END wsh_interface_validations_pkg;
[Home] [Help]