1 PACKAGE BODY wsh_interface_validations_pkg AS
2 /* $Header: WSHINVDB.pls 120.10 2012/01/04 18:48:30 skanduku ship $ */
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 -- LSP PROJECT : Added new in parameter p_client_code.
27 -- Trading Partner Id value comes from xml mapping when p_client_code is not NULL
28 PROCEDURE validate_document (
29 p_doc_type IN VARCHAR2,
30 p_doc_number IN VARCHAR2,
31 -- R12.1.1 STANDALONE PROJECT
32 P_doc_revision IN NUMBER,
33 p_trading_partner_Code IN VARCHAR2,
34 p_action_type IN VARCHAR2,
35 p_doc_direction IN VARCHAR2,
36 p_orig_document_number IN VARCHAR2,
37 p_client_code IN VARCHAR2 DEFAULT NULL, -- LSP PROJECT
38 x_trading_partner_ID IN OUT NOCOPY NUMBER, -- LSP PROJECT: make it as in out
39 x_valid_doc OUT NOCOPY VARCHAR2,
40 x_return_status OUT NOCOPY VARCHAR2
41 )
42 IS
43
44 p_duplicate VARCHAR2 (1);
45 p_940_exists VARCHAR2 (1);
46 --R12.1.1 STANDALONE PROJECT
47 l_wms_deployment_mode VARCHAR2(1);
48 invalid_doc_revision EXCEPTION;
49 invalid_doc_number EXCEPTION;
50 invalid_tp EXCEPTION;
51 invalid_doc_direction EXCEPTION;
52 invalid_doc_type EXCEPTION;
53 invalid_action_type EXCEPTION;
54 --
55 l_debug_on BOOLEAN;
56 --
57 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'VALIDATE_DOCUMENT';
58 --
59 BEGIN
60 --
61 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
62 --
63 IF l_debug_on IS NULL
64 THEN
65 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
66 END IF;
67 --
68 IF l_debug_on THEN
69 wsh_debug_sv.push (l_module_name, 'VALIDATE_DOCUMENT');
70 wsh_debug_sv.log (l_module_name, 'DOCUMENT TYPE', p_doc_type);
71 wsh_debug_sv.log (l_module_name, 'DOCUMENT NUMBER', p_doc_number);
72 --R12.1.1 STANDALONE PROJECT
73 wsh_debug_sv.log (l_module_name, 'DOCUMENT REVISION', p_doc_revision);
74 wsh_debug_sv.log (l_module_name, 'TRADING PARTNER', p_trading_partner_Code);
75 wsh_debug_sv.log (l_module_name, 'ACTION TYPE', p_action_type);
76 wsh_debug_sv.log (l_module_name, 'DOCUMENT DIRECTION', p_doc_direction);
77 wsh_debug_sv.log (l_module_name, 'ORIGINAL DOC NUMBER', p_orig_document_number);
78 wsh_debug_sv.log (l_module_name, 'CLIENT CODE', p_client_code); -- LSP PROJECT
79 END IF;
80
81 -- Check if the values passed are Not Null and valid
82 IF (p_doc_number IS NULL)
83 THEN
84 RAISE invalid_doc_number;
85 END IF;
86
87 IF (p_trading_partner_Code IS NULL)
88 THEN
89 RAISE invalid_tp;
90 END IF;
91
92 IF (p_doc_direction IS NULL)
93 THEN
94 RAISE invalid_doc_direction;
95 END IF;
96 --R12.1.1 STANDALONE PROJECT
97 l_wms_deployment_mode := WMS_DEPLOY.WMS_DEPLOYMENT_MODE;
98
99 IF (l_wms_deployment_mode = 'D' OR (l_wms_deployment_mode = 'L' AND p_client_code IS NOT NULL)) THEN --{ LSP PROJECT : consider LSP mode also
100
101 IF ((p_doc_type IS NULL) OR (p_doc_type NOT IN ('SR'))) THEN
102 RAISE invalid_doc_type;
103 END IF;
104
105 IF ((p_action_type IS NULL) OR (p_action_type NOT IN ('A', 'C', 'D'))) THEN
106 RAISE invalid_action_type;
107 END IF;
108
109 IF ((p_doc_direction IS NULL) OR (p_doc_direction NOT IN ('I', 'O'))) THEN
110 RAISE invalid_Doc_direction;
111 END IF;
112
113 IF ((p_doc_revision IS NULL) OR (p_doc_revision <= 0) OR (trunc(p_doc_revision) <> p_doc_revision)) THEN
114 RAISE invalid_Doc_revision;
115 END IF;
116
117 ELSE --} {
118 IF ((p_doc_type IS NULL) OR (p_doc_type NOT IN ('SR', 'SA'))) THEN
119 RAISE invalid_doc_type;
120 END IF;
121
122 IF ((p_action_type IS NULL) OR (p_action_type NOT IN ('A', 'D'))) THEN
123 RAISE invalid_action_type;
124 END IF;
125
126 IF ((p_doc_direction IS NULL) OR (p_doc_direction NOT IN ('I', 'O'))) THEN
127 RAISE invalid_Doc_direction;
128 END IF;
129
130 END IF; --}
131
132 IF l_debug_on THEN
133 wsh_debug_sv.logmsg(l_module_name, 'Valid parameters for Validate Document');
134 END IF;
135 /* Derive Trading_Partner_Id based on Trading_Partner_code */
136
137 BEGIN
138 -- performance repository bug 4891939
139 -- replace org_organization_definitions with mtl_parameters and
140 -- hr_organization_information
141 -- LSP PROJECT : get party_id value for the given client_id(cust_accnt_id)
142 IF (p_client_code IS NULL) THEN
143 --{
144 SELECT mp.organization_id
145 INTO x_trading_partner_ID
146 FROM mtl_parameters mp, hr_organization_information hoi
147 WHERE mp.organization_id = hoi.organization_id and
148 hoi.org_information1 = 'INV' and
149 hoi.org_information2 = 'Y' and
150 hoi.org_information_context = 'CLASS' and
151 mp.organization_code = p_trading_partner_Code;
152 --}
153 END IF;
154
155 EXCEPTION
156 WHEN NO_DATA_FOUND THEN
157 --
158 IF l_debug_on THEN
159 wsh_debug_sv.logmsg(l_module_name, 'Inside No Data Found Exception to Derive TP ID');
160 END IF;
161 --
162 RAISE FND_API.G_EXC_ERROR;
163 --
164 WHEN TOO_MANY_ROWS THEN
165 --
166 IF l_debug_on THEN
167 wsh_debug_sv.logmsg (l_module_name, 'Inside Too many rows Exception to derive TP ID');
168 END IF;
169 --
170 RAISE FND_API.G_EXC_ERROR;
171 END;
172
173 IF l_debug_on THEN
174 wsh_debug_sv.log(l_module_name, 'Derived TP ID successfully', x_trading_partner_ID);
175 END IF;
176
177 /* Check if the current record is a duplicate */
178 BEGIN
179 SELECT 'X'
180 INTO p_duplicate
181 FROM wsh_transactions_history wth
182 WHERE wth.document_type = p_doc_type
183 AND wth.document_number = p_doc_number
184 AND wth.action_type = p_action_type
185 AND wth.trading_partner_id = x_trading_partner_id
186 --R12.1.1 STANDALONE PROJECT
187 -- LSP PROJECT : consider LSP mode also by checking the profile as well as client_code value on WNDI.
188 AND ((l_wms_deployment_mode <> 'D' AND l_wms_deployment_mode <> 'L') OR ((l_wms_deployment_mode = 'D' AND wth.document_revision = p_doc_revision))
189 OR (l_wms_deployment_mode = 'L' AND wth.document_revision = p_doc_revision AND p_client_code IS NOT NULL))
190 AND wth.document_direction = p_doc_direction;
191
192 IF (p_duplicate = 'X')
193 THEN
194 --
195 IF l_debug_on THEN
196 wsh_debug_sv.log(l_module_name, 'EXCEPTION: Found duplicate', p_duplicate);
197 END IF;
198 RAISE FND_API.G_EXC_ERROR;
199 --
200 END IF;
201
202 EXCEPTION
203 WHEN NO_DATA_FOUND THEN
204 --
205 x_return_status := wsh_util_core.g_ret_sts_success;
206 x_valid_doc := fnd_api.g_true;
207 --
208 WHEN TOO_MANY_ROWS THEN
209 --
210 IF l_debug_on THEN
211 wsh_debug_sv.logmsg(l_module_name, 'EXCEPTION: Too many rows when checking for duplicates');
212 END IF;
213 --
214 RAISE FND_API.G_EXC_ERROR;
215 --
216 END;
217
218 IF l_debug_on THEN
219 wsh_debug_sv.log(l_module_name, 'After checking for duplicates, x_return_status', X_Return_Status);
220 END IF;
221
222 IF (p_doc_type = 'SR') THEN --{
223
224 IF p_action_type = 'D' THEN
225
226 /* Check if the Corresponding 940 Add exists when a 940 Cancellation comes in */
227
228 BEGIN
229 -- R12.1.1 STANDALONE PROJECT
230 IF (l_wms_deployment_mode = 'D' OR (l_wms_deployment_mode = 'L' AND p_client_code IS NOT NULL)) THEN ----{ LSP PROJECT : consider LSP mode also
231
232 SELECT 'X'
233 INTO p_940_exists
234 FROM wsh_transactions_history
235 WHERE document_number = p_doc_number
236 AND document_type = 'SR'
237 AND document_direction = 'I'
238 AND action_type in ('A', 'C')
239 AND rownum = 1;
240 ELSE
241 SELECT 'X'
242 INTO p_940_exists
243 FROM wsh_transactions_history
244 WHERE document_number = p_orig_document_number
245 AND document_type = 'SR'
246 AND document_direction = 'I'
247 AND action_type = 'A';
248 END IF;
249
250 IF (p_940_exists = 'X')
251 THEN
252 x_return_status := wsh_util_core.g_ret_sts_success;
253 x_valid_doc := fnd_api.g_true;
254 IF l_debug_on THEN
255 wsh_debug_sv.log(l_module_name, '940 Add Exists for the 940 Cancellation sent, Return Status',X_Return_Status );
256 END IF;
257 END IF;
258 EXCEPTION
259 WHEN NO_DATA_FOUND
260 THEN
261 IF l_debug_on THEN
262 WSH_DEBUG_SV.logmsg(l_module_name, 'EXCEPTION: No corresponding 940-Add exists');
263 END IF;
264 --
265 RAISE FND_API.G_EXC_ERROR;
266 END;
267 END IF; -- End of If (P_ACTION_TYPE = 'D') Then
268
269 IF l_debug_on THEN
270 wsh_debug_sv.log(l_module_name, 'Return Status after checking for 940 Add Exists when 940 Cancellation. Return Status', X_Return_Status );
271 END IF;
272
273 ELSIF (p_doc_type = 'SA' and nvl(fnd_profile.Value('WSH_SR_SOURCE'),'D') <> 'B') THEN --} {--Fulfillment Batch XML Project
274
275 IF l_debug_on THEN
276 wsh_debug_sv.log(l_module_name, 'Before checking for 940 out when 945 comes in, x_return_status ',x_return_status );
277 END IF;
278
279 /* Check if the Corresponding 940 Out exists when a 945 Comes in */
280 BEGIN
281 SELECT 'X'
282 INTO p_940_exists
283 FROM wsh_transactions_history
284 WHERE document_number = p_orig_document_number
285 AND document_type = 'SR'
286 AND document_direction = 'O'
287 AND action_type = 'A';
288
289 IF (p_940_exists = 'X')
290 THEN
291 x_return_status := wsh_util_core.g_ret_sts_success;
292 x_valid_doc := fnd_api.g_true;
293 END IF;
294 EXCEPTION
295 WHEN NO_DATA_FOUND
296 THEN
297 --
298 IF l_debug_on THEN
299 wsh_debug_sv.logmsg(l_module_name, 'EXCEPTION: No data found when checking for 940-O when
300 945-IN comes in');
301 END IF;
302 --
303 RAISE FND_API.G_EXC_ERROR;
304 --
305 END;
306 END IF; -- }
307
308 IF l_debug_on THEN
309 wsh_debug_sv.log (l_module_name, 'Return Status from Validate Document', X_Return_Status );
310 wsh_debug_sv.pop (l_module_name);
311 END IF;
312
313 EXCEPTION
314 WHEN FND_API.G_EXC_ERROR THEN
315 --
316 x_return_status := WSH_UTIL_CORE.g_ret_sts_error;
317 x_Valid_Doc := FND_API.G_FALSE;
318 --
319 IF l_debug_on THEN
320 --
321 WSH_DEBUG_SV.log(l_module_name, 'x_return_Status from Validate Document',X_Return_Status );
322 WSH_DEBUG_SV.log(l_module_name, 'x_valid_doc', x_Valid_Doc);
323 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured',
324 WSH_DEBUG_SV.C_EXCEP_LEVEL);
325 WSH_DEBUG_SV.pop(l_module_name, 'EXCEPTION:FND_API.G_EXC_ERROR');
326 --
327 END IF;
328 --
329 WHEN invalid_doc_number
330 THEN
331 x_return_status := wsh_util_core.g_ret_sts_error;
332 x_valid_doc := fnd_api.g_false;
333 IF l_debug_on THEN
334 WSH_DEBUG_SV.logmsg(l_module_name,'invalid_doc_number exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
335 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:invalid_doc_number');
336 END IF;
337 --R12.1.1 STANDALONE PROJECT
338 WHEN invalid_doc_revision
339 THEN
340 x_return_status := wsh_util_core.g_ret_sts_error;
341 x_valid_doc := fnd_api.g_false;
342 IF l_debug_on THEN
343 WSH_DEBUG_SV.logmsg(l_module_name,'invalid_doc_revision exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
344 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:invalid_doc_revision');
345 END IF;
346 WHEN invalid_tp
347 THEN
348 x_return_status := wsh_util_core.g_ret_sts_error;
349 x_valid_doc := fnd_api.g_false;
350 IF l_debug_on THEN
351 WSH_DEBUG_SV.logmsg(l_module_name,'invalid_tp exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
352 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:invalid_tp');
353 END IF;
354 WHEN invalid_doc_direction
355 THEN
356 x_return_status := wsh_util_core.g_ret_sts_error;
357 x_valid_doc := fnd_api.g_false;
358 IF l_debug_on THEN
359 WSH_DEBUG_SV.logmsg(l_module_name,'invalid_doc_direction exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
360 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:invalid_doc_direction');
361 END IF;
362 WHEN invalid_doc_type
363 THEN
364 x_return_status := wsh_util_core.g_ret_sts_error;
365 x_valid_doc := fnd_api.g_false;
366 IF l_debug_on THEN
367 WSH_DEBUG_SV.logmsg(l_module_name,'invalid_doc_type exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
368 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:invalid_doc_type');
369 END IF;
370 WHEN invalid_action_type
371 THEN
372 x_return_status := wsh_util_core.g_ret_sts_error;
373 x_valid_doc := fnd_api.g_false;
374 IF l_debug_on THEN
375 WSH_DEBUG_SV.logmsg(l_module_name,'invalid_action_type exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
376 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:invalid_action_type');
377 END IF;
378 WHEN OTHERS
379 THEN
380 x_return_status := wsh_util_core.g_ret_sts_unexp_error;
381 x_valid_doc := fnd_api.g_false;
382 IF l_debug_on THEN
383 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,
384 WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
385 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
386 END IF;
387 END validate_document;
388
389 -- LSP PROJECT : API returns client Code associated to the given
390 -- party id and party site id values. It also returns item delimiter
391 -- value. This api is being called from XML gateway inbound mapping.
392 PROCEDURE Get_Client_details (
393 P_trading_partner_id IN NUMBER,
394 P_trading_partner_site_id IN NUMBER,
395 P_trading_partner_type OUT NOCOPY VARCHAR2,
396 P_client_code OUT NOCOPY VARCHAR2,
397 P_item_delimiter OUT NOCOPY VARCHAR2,
398 X_return_status OUT NOCOPY VARCHAR2
399 )
400 IS
401 --
402 l_debug_on BOOLEAN;
403 --
404 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_CLIENT_DETAILS';
405 --
406 BEGIN
407 --
408 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
409 --
410 IF l_debug_on IS NULL
411 THEN
412 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
413 END IF;
414 --
415 IF l_debug_on THEN
416 wsh_debug_sv.push (l_module_name, 'GET_CLIENT_DETAILS');
417 wsh_debug_sv.log (l_module_name, 'P_trading_partner_id', P_trading_partner_id);
418 wsh_debug_sv.log (l_module_name, 'P_trading_partner_site_id', P_trading_partner_site_id);
419 --
420 END IF;
421 --
422 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
423 --
424 IF WMS_DEPLOY.WMS_DEPLOYMENT_MODE = 'D' THEN --Bugfix 13427327
425 IF l_debug_on THEN
426 wsh_debug_sv.log (l_module_name, 'Distributed Mode');
427 wsh_debug_sv.pop (l_module_name);
428 END IF;
429 return;
430 END IF;
431
432 BEGIN
433 SELECT party_type
434 INTO P_trading_partner_type
435 FROM ecx_tp_headers
436 WHERE party_id = P_trading_partner_id
437 AND party_site_id = P_trading_partner_site_id;
438
439 IF P_trading_partner_type = 'C' AND WMS_DEPLOY.WMS_DEPLOYMENT_MODE = 'L' THEN
440 --
441 SELECT client_code
442 INTO p_client_code
443 FROM
444 mtl_client_parameters
445 WHERE trading_partner_site_id = P_trading_partner_site_id;
446 --
447 END IF;
448 EXCEPTION
449 WHEN NO_DATA_FOUND THEN
450 IF l_debug_on THEN
451 WSH_DEBUG_SV.logmsg(l_module_name, 'EXCEPTION: No corresponding client exists');
452 END IF;
453 --
454 RAISE FND_API.G_EXC_ERROR;
455 END;
456 --
457 -- Call inventory API to get the item delimiter..
458 IF p_client_code IS NOT NULL THEN
459 IF l_debug_on THEN
460 wsh_debug_sv.logmsg(l_module_name, 'Calling api WMS_DEPLOY.GET_ITEM_FLEX_DELIMITER', WSH_DEBUG_SV.C_PROC_LEVEL);
461 END IF;
462 P_item_delimiter := wms_deploy.get_item_flex_delimiter;
463 END IF;
464 --
465 --
466 IF l_debug_on THEN
467 wsh_debug_sv.log (l_module_name, 'P_trading_partner_type', P_trading_partner_type);
468 wsh_debug_sv.log (l_module_name, 'p_client_code', p_client_code);
469 wsh_debug_sv.log (l_module_name, 'P_item_delimiter', P_item_delimiter);
470 wsh_debug_sv.log (l_module_name, 'Return Status', x_return_Status );
471 wsh_debug_sv.pop (l_module_name);
472 END IF;
473 --
474 EXCEPTION
475 WHEN FND_API.G_EXC_ERROR THEN
476 --
477 x_return_status := WSH_UTIL_CORE.g_ret_sts_error;
478 --
479 IF l_debug_on THEN
480 --
481 WSH_DEBUG_SV.log(l_module_name, 'x_return_Status from Get_Client_details',X_Return_Status );
482 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured',
483 WSH_DEBUG_SV.C_EXCEP_LEVEL);
484 WSH_DEBUG_SV.pop(l_module_name, 'EXCEPTION:FND_API.G_EXC_ERROR');
485 --
486 END IF;
487 --
488 WHEN OTHERS THEN
489 x_return_status := wsh_util_core.g_ret_sts_unexp_error;
490 IF l_debug_on THEN
491 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,
492 WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
493 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
494 END IF;
495 END Get_Client_details;
496
497 /*==============================================================================
498
499 PROCEDURE NAME: Validate_Deliveries
500
501 This Procedure is called from the Wsh_Inbound_Ship_Advice_Pkg.Process_Ship_Advice,
502 after data is populated into the interface tables.
503
504 This Procedure checks if the Delivery and Delivery Details received in the 945,
505 exists in the Supplier Instance base tables.
506
507 ==============================================================================*/
508
509 PROCEDURE validate_deliveries (
510 p_delivery_id IN NUMBER,
511 x_return_status OUT NOCOPY VARCHAR2
512 )
513 IS
514 x_delivery_exists VARCHAR2 (1);
515 x_delivery_detail_exists VARCHAR2 (1);
516 invalid_delivery_id EXCEPTION;
517 invalid_delivery_detail_id EXCEPTION;
518
519 CURSOR delivery_detail_int_cur (p_delivery_id NUMBER)
520 IS
521 SELECT DISTINCT wdd.delivery_detail_id
522 FROM wsh_del_details_interface wdd,
523 wsh_del_assgn_interface wda
524 WHERE wdd.delivery_detail_interface_id = wda.delivery_detail_interface_id
525 AND wdd.container_flag = 'N'
526 AND wda.delivery_id = p_delivery_id
527 AND wda.interface_action_code = '94X_INBOUND'
528 AND wdd.interface_action_code = '94X_INBOUND';
529 --
530 l_debug_on BOOLEAN;
531 --
532 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'VALIDATE_DELIVERIES';
533 --
534 BEGIN
535 --
536 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
537 --
538 IF l_debug_on IS NULL
539 THEN
540 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
541 END IF;
542 --
543 IF l_debug_on THEN
544 wsh_debug_sv.push (l_module_name, 'VALIDATE_DELIVERIES');
545 wsh_debug_sv.log (l_module_name, 'DELIVERY ID', p_delivery_id);
546 END IF;
547
548 /* Check if the Delivery ID from Interface table exists in WSH_NEW_DELIVERIES Table */
549
550 IF (p_delivery_id IS NOT NULL)
551 THEN
552 BEGIN
553 SELECT 'X'
554 INTO x_delivery_exists
555 FROM wsh_new_deliveries
556 WHERE delivery_id = p_delivery_id;
557 EXCEPTION
558 WHEN NO_DATA_FOUND
559 THEN
560 RAISE invalid_delivery_id;
561 END;
562
563 IF l_debug_on THEN
564 wsh_debug_sv.log (l_module_name, 'Valid parameters for Validate Deliveries. Return Status : ',X_Return_Status );
565 END IF;
566
567 /* If the Delivery exists in WSH_New_Deliveries, Get the corresponding Delivery_Detail_IDs
568 from the interface tables and check if they exist in the base tables, and if they are assigned
569 to the same delivery */
570
571 IF (x_delivery_exists = 'X')
572 THEN
573 BEGIN
574 FOR delivery_detail_int_rec IN delivery_detail_int_cur (p_delivery_id) LOOP
575 BEGIN
576 SELECT 'X'
577 INTO x_delivery_detail_exists
578 FROM wsh_delivery_details wdd,
579 wsh_delivery_assignments_v wda
580 WHERE wdd.delivery_detail_id = wda.delivery_detail_id
581 AND wdd.delivery_detail_id = delivery_detail_int_rec.delivery_detail_id
582 AND wda.delivery_id = p_delivery_id;
583
584 x_return_status := wsh_util_core.g_ret_sts_success;
585 EXCEPTION
586 WHEN NO_DATA_FOUND
587 THEN
588 RAISE invalid_delivery_detail_id;
589 END; -- End of Begin
590 END LOOP; -- End of For Delivery ....
591 END;
592 END IF; -- End of if X_Delivery_Exists
593
594 ELSIF (p_delivery_id IS NULL) THEN
595 x_return_status := wsh_util_core.g_ret_sts_error;
596 END IF; -- if p_delivery_id is null
597
598 IF l_debug_on THEN
599 wsh_debug_sv.log (l_module_name,'940 Add Exists for the 940 Cancellation sent. Return Status',X_Return_Status );
600 wsh_debug_sv.pop (l_module_name);
601 END IF;
602 EXCEPTION
603 WHEN invalid_delivery_id
604 THEN
605 x_return_status := wsh_util_core.g_ret_sts_error;
606 IF l_debug_on THEN
607 WSH_DEBUG_SV.logmsg(l_module_name,'invalid_delivery_id exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
608 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:invalid_delivery_id');
609 END IF;
610 WHEN invalid_delivery_detail_id
611 THEN
612 x_return_status := wsh_util_core.g_ret_sts_error;
613 IF l_debug_on THEN
614 WSH_DEBUG_SV.logmsg(l_module_name,'invalid_delivery_detail_id exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
615 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:invalid_delivery_detail_id');
616 END IF;
617 WHEN OTHERS
618 THEN
619 x_return_status := wsh_util_core.g_ret_sts_unexp_error;
620 IF l_debug_on THEN
621 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,
622 WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
623 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
624 END IF;
625 END validate_deliveries;
626
627 -- TPW - Distributed Organization Changes
628 /*==============================================================================
629
630 PROCEDURE NAME: Validate_Delivery_Details
631
632 This Procedure is called from the Wsh_Inbound_Ship_Advice_Pkg.Process_Ship_Advice,
633 after data is populated into the interface tables.
634
635 This Procedure checks if the Delivery Details received in the 945,
636 exists in the Supplier Instance base tables (for Batch based shipment request).
637
638 ==============================================================================*/
639
640 PROCEDURE validate_delivery_details (
641 p_delivery_interface_id IN NUMBER,
642 x_return_status OUT NOCOPY VARCHAR2
643 )
644 IS
645 l_delivery_detail_exists VARCHAR2 (1);
646 invalid_delivery_detail_id EXCEPTION;
647
648 CURSOR delivery_detail_int_cur (p_delivery_id NUMBER)
649 IS
650 SELECT DISTINCT wdd.source_header_number, wdd.delivery_detail_id, wdd.line_direction
651 FROM wsh_del_details_interface wdd,
652 wsh_del_assgn_interface wda
653 WHERE wdd.delivery_detail_interface_id = wda.delivery_detail_interface_id
654 AND wdd.container_flag <> 'Y'
655 AND wda.delivery_interface_id = p_delivery_interface_id
656 AND wda.interface_action_code = '94X_INBOUND'
657 AND wdd.interface_action_code = '94X_INBOUND';
658 --
659 l_debug_on BOOLEAN;
660 --
661 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'VALIDATE_DELIVERY_DETAILS';
662 --
663 BEGIN
664 --
665 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
666 --
667 IF l_debug_on IS NULL
668 THEN
669 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
670 END IF;
671 --
672 IF l_debug_on THEN
673 wsh_debug_sv.push (l_module_name, 'VALIDATE_DELIVERY_DETAILS');
674 wsh_debug_sv.log (l_module_name, 'DELIVERY INTERFACE ID', p_delivery_interface_id);
675 END IF;
676
677
678 IF (p_delivery_interface_id IS NOT NULL)
679 THEN
680 FOR delivery_detail_int_rec IN delivery_detail_int_cur (p_delivery_interface_id) LOOP
681 BEGIN
682 IF (delivery_detail_int_rec.line_direction = 'IO') THEN
683 SELECT distinct 'X'
684 INTO l_delivery_detail_exists
685 FROM wsh_delivery_details wdd,
686 wsh_delivery_assignments wda,
687 oe_order_lines_all ol,
688 po_requisition_lines_all pl,
689 po_requisition_headers_all ph
690 WHERE wdd.delivery_detail_id = wda.delivery_detail_id
691 AND wdd.released_status in ('R','B','X')
692 AND wdd.source_code = 'OE'
693 AND wdd.source_line_id = ol.line_id
694 AND ol.source_document_line_id = pl.requisition_line_id
695 AND ol.source_document_id = pl.requisition_header_id
696 AND pl.requisition_header_id = ph.requisition_header_id
697 AND pl.line_num = delivery_detail_int_rec.delivery_detail_id
698 AND ph.segment1 = delivery_detail_int_rec.source_header_number;
699 ELSE
700 SELECT distinct 'X'
701 INTO l_delivery_detail_exists
702 FROM wsh_delivery_details wdd,
703 wsh_delivery_assignments wda,
704 wsh_shipment_batches wsb,
705 wsh_transactions_history wth
706 WHERE wdd.delivery_detail_id = wda.delivery_detail_id
707 AND wdd.shipment_line_number = delivery_detail_int_rec.delivery_detail_id
708 AND wdd.released_status in ('R','B','X')--Fulfillment Batch XML Gateway Project
709 AND wdd.source_code = 'OE'
710 AND wdd.shipment_batch_id = wsb.batch_id
711 AND wsb.name = wth.entity_number
712 AND wth.document_number = delivery_detail_int_rec.source_header_number
713 AND wth.entity_type = 'BATCH'
714 AND wth.document_type = 'SR'
715 AND wth.document_direction = 'O';
716 END IF;
717
718 x_return_status := wsh_util_core.g_ret_sts_success;
719 EXCEPTION
720 WHEN NO_DATA_FOUND
721 THEN
722 RAISE invalid_delivery_detail_id;
723 END; -- End of Begin
724 END LOOP;
725
726 ELSIF (p_delivery_interface_id IS NULL) THEN
727 x_return_status := wsh_util_core.g_ret_sts_error;
728 END IF; -- if p_delivery_interface_id is null
729
730 IF l_debug_on THEN
731 wsh_debug_sv.log (l_module_name,'Return Status',X_Return_Status );
732 wsh_debug_sv.pop (l_module_name);
733 END IF;
734 EXCEPTION
735 WHEN invalid_delivery_detail_id
736 THEN
737 x_return_status := wsh_util_core.g_ret_sts_error;
738 IF l_debug_on THEN
739 WSH_DEBUG_SV.logmsg(l_module_name,'invalid_delivery_detail_id exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
740 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:invalid_delivery_detail_id');
741 END IF;
742 WHEN OTHERS
743 THEN
744 x_return_status := wsh_util_core.g_ret_sts_unexp_error;
745 IF l_debug_on THEN
746 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,
747 WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
748 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
749 END IF;
750 END validate_delivery_details;
751
752 /*==============================================================================
753
754 PROCEDURE NAME: Compare_Ship_Request_Advice
755
756 This Procedure is called from the Wsh_Inbound_Ship_Advice_Pkg.Process_Ship_Advice,
757 after data is populated into the interface tables.
758
759 This Procedure checks if the key data elements like Ship To, Inventory Item ID etc
760 have been modified by Third Party Warehouse.
761
762 ==============================================================================*/
763
764 PROCEDURE compare_ship_request_advice (
765 p_delivery_id IN NUMBER,
766 x_return_status OUT NOCOPY VARCHAR2
767 )
768 IS
769 CURSOR delivery_cur
770 IS
771 SELECT customer_id, initial_pickup_location_id,
772 intmed_ship_to_location_id, organization_id,
773 ultimate_dropoff_location_id
774 FROM wsh_new_deliveries
775 WHERE delivery_id = p_delivery_id;
776
777 CURSOR delivery_detail_cur (p_delivery_detail_id NUMBER)
778 IS
779 SELECT DISTINCT delivery_detail_id, customer_id, customer_item_id,
780 deliver_to_location_id, intmed_ship_to_location_id,
781 inventory_item_id, organization_id, ship_from_location_id,
782 ship_to_location_id
783 FROM wsh_delivery_details
784 WHERE delivery_detail_id = p_delivery_detail_id;
785
786 CURSOR delivery_int_cur
787 IS
788 SELECT customer_id, initial_pickup_location_id,
789 intmed_ship_to_location_id, organization_id,
790 ultimate_dropoff_location_id
791 FROM wsh_new_del_interface
792 WHERE delivery_id = p_delivery_id
793 AND INTERFACE_ACTION_CODE ='94X_INBOUND';
794
795 CURSOR delivery_detail_int_cur (p_delivery_id NUMBER)
796 IS
797 SELECT DISTINCT wdd.delivery_detail_id, wdd.customer_id, wdd.customer_item_id,
798 wdd.deliver_to_location_id, wdd.intmed_ship_to_location_id,
799 wdd.inventory_item_id, wdd.organization_id,
800 wdd.ship_from_location_id, wdd.ship_to_location_id
801 FROM wsh_del_assgn_interface wda, wsh_del_details_interface wdd
802 WHERE wda.delivery_id = p_delivery_id
803 AND wda.delivery_detail_interface_id = wdd.delivery_detail_interface_id
804 AND wdd.container_flag = 'N'
805 AND WDD.INTERFACE_ACTION_CODE = '94X_INBOUND'
806 AND WDA.INTERFACE_ACTION_CODE ='94X_INBOUND';
807
808 delivery delivery_cur%ROWTYPE;
809 delivery_int delivery_cur%ROWTYPE;
810 delivery_detail_int delivery_detail_cur%ROWTYPE;
811 invalid_delivery EXCEPTION;
812 data_changed EXCEPTION;
813 --
814 l_debug_on BOOLEAN;
815 --
816 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'COMPARE_SHIP_REQUEST_ADVICE';
817 --
818 BEGIN
819 --
820 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
821 --
822 IF l_debug_on IS NULL
823 THEN
824 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
825 END IF;
826 --
827 IF l_debug_on THEN
828 wsh_debug_sv.push (l_module_name, 'Compare Ship Request Advice');
829 wsh_debug_sv.log (l_module_name, 'DELIVERY ID', p_delivery_id);
830 END IF;
831
832 BEGIN
833 IF (p_delivery_id IS NOT NULL) THEN
834 OPEN delivery_cur;
835 FETCH delivery_cur INTO delivery;
836
837 IF (delivery_cur%NOTFOUND) THEN
838 RAISE invalid_delivery;
839 END IF;
840
841 OPEN delivery_int_cur;
842 FETCH delivery_int_cur INTO delivery_int;
843
844 IF (delivery_int_cur%NOTFOUND)
845 THEN
846 RAISE invalid_delivery;
847 END IF;
848
849 IF l_debug_on THEN
850 wsh_debug_sv.log (l_module_name, 'Valid values for Compare Ship Request Advice');
851 END IF;
852
853 /* Compare values of the Delivery */
854
855 IF (NVL (delivery.customer_id, 0) <> NVL (delivery_int.customer_id, 0))
856 OR (NVL (delivery.initial_pickup_location_id, 0) <> NVL (delivery_int.initial_pickup_location_id, 0))
857 OR (NVL (delivery.intmed_ship_to_location_id, 0) <> NVL (delivery_int.intmed_ship_to_location_id, 0))
858 OR (NVL (delivery.organization_id, 0) <> NVL (delivery_int.organization_id, 0))
859 OR (NVL (delivery.ultimate_dropoff_location_id, 0) <> NVL (delivery_int.ultimate_dropoff_location_id, 0))
860 THEN
861 RAISE data_changed;
862 ELSE
863
864 IF l_debug_on THEN
865 wsh_debug_sv.log (l_module_name, 'Compare Ship Request. Data Did Not Change for Delivery');
866 END IF;
867
868 /* Compare values of the Delivery Details */
869
870 FOR delivery_detail_int IN delivery_detail_int_cur (p_delivery_id)
871 LOOP
872 IF l_debug_on THEN
873 wsh_debug_sv.log (l_module_name, 'Delivery Detail Interface ID ',delivery_detail_int.delivery_detail_id);
874 END IF;
875 FOR delivery_detail IN delivery_detail_cur (delivery_detail_int.delivery_detail_id)
876 LOOP
877 IF l_debug_on THEN
878 wsh_debug_sv.log (l_module_name, 'Delivery Detail ID ',delivery_detail.delivery_detail_id);
879 END IF;
880
881 IF (NVL (delivery_detail.customer_id, 0) <> NVL (delivery_detail_int.customer_id, 0))
882 OR (NVL (delivery_detail.customer_item_id, 0) <> NVL (delivery_detail_int.customer_item_id, 0))
883 OR (NVL (delivery_detail.deliver_to_location_id, 0) <> NVL (delivery_detail_int.deliver_to_location_id, 0))
884 OR (NVL (delivery_detail.intmed_ship_to_location_id, 0) <> NVL (delivery_detail_int.intmed_ship_to_location_id, 0))
885 OR (NVL (delivery_detail.inventory_item_id, 0) <> NVL (delivery_detail_int.inventory_item_id, 0))
886 OR (NVL (delivery_detail.organization_id, 0) <> NVL (delivery_detail_int.organization_id, 0))
887 OR (NVL (delivery_detail.ship_from_location_id, 0) <> NVL (delivery_detail_int.ship_from_location_id, 0))
888 OR (NVL (delivery_detail.ship_to_location_id, 0) <> NVL (delivery_detail_int.ship_to_location_id, 0))
889 THEN
890 RAISE data_changed;
891 ELSE
892 IF l_debug_on THEN
893 wsh_debug_sv.log (l_module_name, 'Compare Ship Request. Data Did Not Change for Delivery Details');
894 END IF;
895
896 x_return_status := wsh_util_core.g_ret_sts_success;
897 END IF; -- if delivery_detail checks
898 END LOOP;
899 END LOOP;
900 END IF; -- if delivery checks
901
902 IF (delivery_int_cur%ISOPEN)
903 THEN
904 CLOSE delivery_int_cur;
905 END IF;
906
907 IF (delivery_cur%ISOPEN)
908 THEN
909 CLOSE delivery_cur;
910 END IF;
911
912 IF (delivery_detail_int_cur%ISOPEN)
913 THEN
914 CLOSE delivery_detail_int_cur;
915 END IF;
916 ELSIF p_delivery_id IS NULL
917 THEN
918 RAISE invalid_delivery;
919 END IF; -- if p_delivery_id is not null
920
921 IF l_debug_on THEN
922 wsh_debug_sv.log (l_module_name, 'Compare Ship Request. Return Status :'||X_Return_Status);
923 wsh_debug_sv.pop (l_module_name);
924 END IF;
925
926 EXCEPTION
927 WHEN invalid_delivery
928 THEN
929 x_return_status := wsh_util_core.g_ret_sts_error;
930 IF l_debug_on THEN
931 WSH_DEBUG_SV.logmsg(l_module_name,'invalid_delivery exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
932 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:invalid_delivery');
933 END IF;
934 WHEN data_changed
935 THEN
936 x_return_status := wsh_util_core.g_ret_sts_error;
937 IF l_debug_on THEN
938 WSH_DEBUG_SV.logmsg(l_module_name,'data_changed exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
939 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:data_changed');
940 END IF;
941 WHEN OTHERS
942 THEN
943 x_return_status := wsh_util_core.g_ret_sts_unexp_error;
944 IF l_debug_on THEN
945 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,
946 WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
947 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
948 END IF;
949 END;
950
951 END compare_ship_request_advice;
952
953
954 /*==============================================================================
955
956 PROCEDURE NAME: Log_Interface_Errors
957
958 This Procedure is called from various procedure whenever an error is detected in
959 the data elements.
960
961 This Procedure accepts upto 6 different tokens and Values Concatenates and stores
962 the resulting message text in WSH_INTERFACE_ERRORS table.
963 ==============================================================================*/
964
965 PROCEDURE log_interface_errors (
966 p_interface_errors_rec IN interface_errors_rec_type,
967 p_msg_data IN VARCHAR2 DEFAULT NULL,
968 p_api_name IN VARCHAR2,
969 x_return_status OUT NOCOPY VARCHAR2
970 )
971 IS
972 pragma AUTONOMOUS_TRANSACTION;
973 invalid_parameters EXCEPTION;
974 l_text VARCHAR2 (4000);
975 l_text_token VARCHAR2 (4000);
976 l_msg_count NUMBER;
977 l_msg_data VARCHAR2(4000);
978 l_msg_details VARCHAR2(4000);
979 --
980 l_debug_on BOOLEAN;
981 --
982 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'LOG_INTERFACE_ERRORS';
983 --
984 BEGIN
985 --
986 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
987 --
988 IF l_debug_on IS NULL
989 THEN
990 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
991 END IF;
992 --
993 IF l_debug_on THEN
994 wsh_debug_sv.push(l_module_name, 'Log_Interface_Errors');
995 wsh_debug_sv.log (l_module_name, 'Interface table name', p_interface_errors_rec.p_interface_table_name);
996 wsh_debug_sv.log (l_module_name, 'Interface Id', p_interface_errors_rec.p_interface_id);
997 wsh_debug_sv.log (l_module_name, 'Message Name', p_interface_errors_rec.p_message_name);
998 END IF;
999
1000 IF (p_msg_data IS NULL) THEN
1001 wsh_util_core.get_messages('Y', l_msg_data, l_msg_details, l_msg_count);
1002
1003 IF (l_msg_data IS NULL) THEN
1004 fnd_message.set_name ('WSH', 'WSH_ERROR_IN_API');
1005 fnd_message.set_token ('API_NAME',p_api_name);
1006 l_text := fnd_message.get;
1007 ELSE
1008 IF (l_msg_count >1 ) THEN
1009 l_text := l_msg_details;
1010 ELSE
1011 l_text := l_msg_data;
1012 END IF;
1013 END IF;
1014
1015 ELSE
1016 l_text := p_msg_data;
1017 END IF;
1018
1019
1020 IF (p_interface_errors_rec.p_interface_table_name IS NOT NULL)
1021 AND (p_interface_errors_rec.p_interface_id IS NOT NULL) THEN
1022
1023 IF l_debug_on THEN
1024 wsh_debug_sv.log (l_module_name, 'Log Interface Errors. Valid Parameters');
1025 END IF;
1026
1027 IF (p_interface_errors_rec.p_message_name IS NOT NULL) THEN
1028 -- Build the Error message string.
1029 fnd_message.set_name ('WSH', p_interface_errors_rec.p_message_name);
1030
1031 -- Replace the tokens with Values.
1032 IF (p_interface_errors_rec.p_token1 IS NOT NULL)
1033 THEN
1034 fnd_message.set_token (p_interface_errors_rec.p_token1,p_interface_errors_rec.p_value1);
1035 END IF;
1036
1037 IF (p_interface_errors_rec.p_token2 IS NOT NULL)
1038 THEN
1039 fnd_message.set_token (p_interface_errors_rec.p_token2,p_interface_errors_rec.p_value2);
1040 END IF;
1041
1042 IF (p_interface_errors_rec.p_token3 IS NOT NULL)
1043 THEN
1044 fnd_message.set_token (p_interface_errors_rec.p_token3,p_interface_errors_rec.p_value3);
1045 END IF;
1046
1047 IF (p_interface_errors_rec.p_token4 IS NOT NULL)
1048 THEN
1049 fnd_message.set_token (p_interface_errors_rec.p_token4,p_interface_errors_rec.p_value4);
1050 END IF;
1051
1052 IF (p_interface_errors_rec.p_token5 IS NOT NULL)
1053 THEN
1054 fnd_message.set_token (p_interface_errors_rec.p_token5,p_interface_errors_rec.p_value5);
1055 END IF;
1056
1057 IF (p_interface_errors_rec.p_token6 IS NOT NULL)
1058 THEN
1059 fnd_message.set_token (p_interface_errors_rec.p_token6,p_interface_errors_rec.p_value6);
1060 END IF;
1061
1062 IF (p_interface_errors_rec.p_token7 IS NOT NULL)
1063 THEN
1064 fnd_message.set_token (p_interface_errors_rec.p_token7,p_interface_errors_rec.p_value7);
1065 END IF;
1066
1067 IF (p_interface_errors_rec.p_token8 IS NOT NULL)
1068 THEN
1069 fnd_message.set_token (p_interface_errors_rec.p_token8,p_interface_errors_rec.p_value8);
1070 END IF;
1071
1072 IF (p_interface_errors_rec.p_token9 IS NOT NULL)
1073 THEN
1074 fnd_message.set_token (p_interface_errors_rec.p_token9,p_interface_errors_rec.p_value9);
1075 END IF;
1076
1077 IF (p_interface_errors_rec.p_token10 IS NOT NULL)
1078 THEN
1079 fnd_message.set_token (p_interface_errors_rec.p_token10,p_interface_errors_rec.p_value10);
1080 END IF;
1081
1082 IF (p_interface_errors_rec.p_token11 IS NOT NULL)
1083 THEN
1084 fnd_message.set_token (p_interface_errors_rec.p_token11,p_interface_errors_rec.p_value11);
1085 END IF;
1086
1087 --Retrieve the error message.
1088 l_text_token := fnd_message.get;
1089
1090 END IF; -- if p_message_name is not null
1091
1092
1093 IF (l_text_token IS NOT NULL) THEN
1094 IF ( length(l_text)+length(l_text_token) < 3997 ) THEN
1095 l_text := l_text ||' , '||l_text_token;
1096 END IF;
1097 END IF;
1098
1099
1100 IF l_debug_on THEN
1101 wsh_debug_sv.log (l_module_name, 'Text: ', l_text);
1102 END IF;
1103 -- Insert error record in WSH_Interface_errors table.
1104 -- We need the check for l_text because the column error_message
1105 -- is a Not_Null column.
1106
1107 IF (l_text IS NOT NULL) THEN
1108 IF l_debug_on THEN
1109 wsh_debug_sv.log (l_module_name, 'Error Message', l_text);
1110 wsh_debug_sv.log (l_module_name, 'Inserting into wsh_interface_errors');
1111 END IF;
1112
1113 INSERT INTO wsh_interface_errors
1114 (interface_error_id,
1115 interface_table_name,
1116 interface_id, error_message, creation_date,
1117 created_by, last_update_date, last_updated_by,
1118 last_update_login,INTERFACE_ACTION_CODE)
1119 VALUES (wsh_interface_errors_s.NEXTVAL,
1120 p_interface_errors_rec.p_interface_table_name,
1121 p_interface_errors_rec.p_interface_id, l_text, SYSDATE,
1122 fnd_global.user_id, SYSDATE, fnd_global.user_id,
1123 fnd_global.user_id,'94X_INBOUND');
1124
1125 COMMIT;
1126
1127 ELSE
1128 IF l_debug_on THEN
1129 wsh_debug_sv.log (l_module_name, 'Text for error_message is null');
1130 END IF;
1131 END IF; -- if l_text is not null
1132 x_return_status := wsh_util_core.g_ret_sts_success;
1133 ELSE
1134 RAISE invalid_parameters;
1135 END IF;
1136
1137 IF l_debug_on THEN
1138 wsh_debug_sv.log (l_module_name, 'Log Interface Errors. Return Status. :'||X_Return_Status);
1139 wsh_debug_sv.pop (l_module_name);
1140 END IF;
1141 EXCEPTION
1142 WHEN invalid_parameters
1143 THEN
1144 x_return_status := wsh_util_core.g_ret_sts_error;
1145 IF l_debug_on THEN
1146 WSH_DEBUG_SV.logmsg(l_module_name,'RECORD_LOCKED exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1147 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:RECORD_LOCKED');
1148 END IF;
1149 WHEN OTHERS
1150 THEN
1151 x_return_status := wsh_util_core.g_ret_sts_unexp_error;
1152 IF l_debug_on THEN
1153 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,
1154 WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1155 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1156 END IF;
1157
1158 END log_interface_errors;
1159
1160 --R12.1.1 STANDALONE PROJECT
1161 /*==============================================================================
1162
1163 PROCEDURE NAME: Log_Interface_Errors (Overloaded)
1164
1165 This Procedure is called from various procedure whenever an error is detected in
1166 the data elements.
1167
1168 ==============================================================================*/
1169
1170 PROCEDURE log_interface_errors (
1171 p_interface_errors_rec_tab IN interface_errors_rec_tab,
1172 p_interface_action_code IN VARCHAR2,
1173 x_return_status OUT NOCOPY VARCHAR2
1174 )
1175 IS
1176 pragma AUTONOMOUS_TRANSACTION;
1177 invalid_parameters EXCEPTION;
1178 l_text VARCHAR2 (2000);
1179 TYPE varchar30_Tab_Type IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
1180 TYPE varchar2000_Tab_Type IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
1181 l_interface_errors_rec_tab interface_errors_rec_tab;
1182 l_interface_table_name_tab varchar30_Tab_Type;
1183 l_interface_id_tab wsh_util_core.Id_Tab_Type;
1184 l_text_tab varchar2000_Tab_Type;
1185 --
1186 l_debug_on BOOLEAN;
1187 --
1188 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'LOG_INTERFACE_ERRORS';
1189 --
1190 BEGIN
1191 --
1192 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1193 --
1194 IF l_debug_on IS NULL
1195 THEN
1196 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1197 END IF;
1198 --
1199 IF l_debug_on THEN
1200 wsh_debug_sv.push(l_module_name, 'Log_Interface_Errors');
1201 wsh_debug_sv.log (l_module_name, 'Message Count', p_interface_errors_rec_tab.COUNT);
1202 wsh_debug_sv.log (l_module_name, 'Interface Action Code', p_interface_action_code);
1203 END IF;
1204
1205 x_return_status := wsh_util_core.g_ret_sts_success;
1206
1207 IF (p_interface_errors_rec_tab.COUNT > 0) THEN
1208 FOR i in p_interface_errors_rec_tab.FIRST..p_interface_errors_rec_tab.LAST LOOP --{
1209
1210 IF (p_interface_errors_rec_tab(i).p_interface_table_name IS NOT NULL)
1211 AND (p_interface_errors_rec_tab(i).p_interface_id IS NOT NULL) THEN
1212
1213 IF (p_interface_errors_rec_tab(i).p_text is NOT NULL) THEN -- {
1214
1215 l_interface_table_name_tab(l_interface_table_name_tab.COUNT+1) := p_interface_errors_rec_tab(i).p_interface_table_name;
1216 l_interface_id_tab(l_interface_id_tab.COUNT+1) := p_interface_errors_rec_tab(i).p_interface_id;
1217 l_text_tab(l_text_tab.COUNT+1) := p_interface_errors_rec_tab(i).p_text;
1218
1219 ELSIF (p_interface_errors_rec_tab(i).p_message_name IS NOT NULL) THEN
1220 -- Build the Error message string.
1221 fnd_message.set_name ('WSH', p_interface_errors_rec_tab(i).p_message_name);
1222
1223 -- Replace the tokens with Values.
1224 IF (p_interface_errors_rec_tab(i).p_token1 IS NOT NULL)
1225 THEN
1226 fnd_message.set_token (p_interface_errors_rec_tab(i).p_token1,p_interface_errors_rec_tab(i).p_value1);
1227 END IF;
1228
1229 IF (p_interface_errors_rec_tab(i).p_token2 IS NOT NULL)
1230 THEN
1231 fnd_message.set_token (p_interface_errors_rec_tab(i).p_token2,p_interface_errors_rec_tab(i).p_value2);
1232 END IF;
1233
1234 IF (p_interface_errors_rec_tab(i).p_token3 IS NOT NULL)
1235 THEN
1236 fnd_message.set_token (p_interface_errors_rec_tab(i).p_token3,p_interface_errors_rec_tab(i).p_value3);
1237 END IF;
1238
1239 IF (p_interface_errors_rec_tab(i).p_token4 IS NOT NULL)
1240 THEN
1241 fnd_message.set_token (p_interface_errors_rec_tab(i).p_token4,p_interface_errors_rec_tab(i).p_value4);
1242 END IF;
1243
1244 IF (p_interface_errors_rec_tab(i).p_token5 IS NOT NULL)
1245 THEN
1246 fnd_message.set_token (p_interface_errors_rec_tab(i).p_token5,p_interface_errors_rec_tab(i).p_value5);
1247 END IF;
1248
1249 IF (p_interface_errors_rec_tab(i).p_token6 IS NOT NULL)
1250 THEN
1251 fnd_message.set_token (p_interface_errors_rec_tab(i).p_token6,p_interface_errors_rec_tab(i).p_value6);
1252 END IF;
1253
1254 IF (p_interface_errors_rec_tab(i).p_token7 IS NOT NULL)
1255 THEN
1256 fnd_message.set_token (p_interface_errors_rec_tab(i).p_token7,p_interface_errors_rec_tab(i).p_value7);
1257 END IF;
1258
1259 IF (p_interface_errors_rec_tab(i).p_token8 IS NOT NULL)
1260 THEN
1261 fnd_message.set_token (p_interface_errors_rec_tab(i).p_token8,p_interface_errors_rec_tab(i).p_value8);
1262 END IF;
1263
1264 IF (p_interface_errors_rec_tab(i).p_token9 IS NOT NULL)
1265 THEN
1266 fnd_message.set_token (p_interface_errors_rec_tab(i).p_token9,p_interface_errors_rec_tab(i).p_value9);
1267 END IF;
1268
1269 IF (p_interface_errors_rec_tab(i).p_token10 IS NOT NULL)
1270 THEN
1271 fnd_message.set_token (p_interface_errors_rec_tab(i).p_token10,p_interface_errors_rec_tab(i).p_value10);
1272 END IF;
1273
1274 IF (p_interface_errors_rec_tab(i).p_token11 IS NOT NULL)
1275 THEN
1276 fnd_message.set_token (p_interface_errors_rec_tab(i).p_token11,p_interface_errors_rec_tab(i).p_value11);
1277 END IF;
1278
1279 --Retrieve the error message.
1280 l_text := fnd_message.get;
1281 IF (l_text IS NOT NULL) THEN
1282 l_interface_table_name_tab(l_interface_table_name_tab.COUNT+1) := p_interface_errors_rec_tab(i).p_interface_table_name;
1283 l_interface_id_tab(l_interface_id_tab.COUNT+1) := p_interface_errors_rec_tab(i).p_interface_id;
1284 l_text_tab(l_text_tab.COUNT+1) := l_text;
1285 END IF;
1286
1287 ELSE
1288 IF l_debug_on THEN
1289 wsh_debug_sv.logmsg(l_module_name, 'Index '||i||' : Message Name or Text is Mandatory');
1290 END IF;
1291 raise invalid_parameters;
1292 END IF; -- }
1293 ELSE
1294 IF l_debug_on THEN
1295 wsh_debug_sv.logmsg(l_module_name, 'Index '||i||' : Interface Table Name AND Interface Error Id are Mandatory');
1296 END IF;
1297 raise invalid_parameters;
1298 END IF;
1299
1300 END LOOP; --}
1301 END IF;
1302
1303 IF (l_text_tab.COUNT > 0) THEN --{
1304
1305 IF (l_text_tab.COUNT > 3) THEN
1306 FORALL i in l_text_tab.FIRST..l_text_tab.LAST
1307 INSERT INTO wsh_interface_errors(
1308 interface_error_id,
1309 interface_table_name,
1310 interface_id,
1311 error_message,
1312 creation_date,
1313 created_by,
1314 last_update_date,
1315 last_updated_by,
1316 last_update_login,
1317 program_application_id,
1318 program_id,
1319 program_update_date,
1320 request_id,
1321 interface_action_code)
1322 VALUES (wsh_interface_errors_s.NEXTVAL,
1323 l_interface_table_name_tab(i),
1324 l_interface_id_tab(i),
1325 l_text_tab(i),
1326 SYSDATE,
1327 fnd_global.user_id,
1328 SYSDATE,
1329 fnd_global.user_id,
1330 fnd_global.user_id,
1331 fnd_global.prog_appl_id,
1332 fnd_global.conc_program_id,
1333 SYSDATE,
1334 fnd_global.conc_request_id,
1335 p_interface_action_code);
1336 ELSE
1337 FOR i in l_text_tab.FIRST..l_text_tab.LAST LOOP
1338 INSERT INTO wsh_interface_errors(
1339 interface_error_id,
1340 interface_table_name,
1341 interface_id,
1342 error_message,
1343 creation_date,
1344 created_by,
1345 last_update_date,
1346 last_updated_by,
1347 last_update_login,
1348 program_application_id,
1349 program_id,
1350 program_update_date,
1351 request_id,
1352 interface_action_code)
1353 VALUES (wsh_interface_errors_s.NEXTVAL,
1354 l_interface_table_name_tab(i),
1355 l_interface_id_tab(i),
1356 l_text_tab(i),
1357 SYSDATE,
1358 fnd_global.user_id,
1359 SYSDATE,
1360 fnd_global.user_id,
1361 fnd_global.user_id,
1362 fnd_global.prog_appl_id,
1363 fnd_global.conc_program_id,
1364 SYSDATE,
1365 fnd_global.conc_request_id,
1366 p_interface_action_code);
1367 END LOOP;
1368 END IF;
1369
1370 COMMIT;
1371
1372 END IF; --}
1373
1374 IF l_debug_on THEN
1375 wsh_debug_sv.logmsg (l_module_name, 'Inserted '||l_text_tab.COUNT||' Interface Error Records');
1376 wsh_debug_sv.log(l_module_name, 'Return Status',x_return_status);
1377 wsh_debug_sv.pop (l_module_name);
1378 END IF;
1379 EXCEPTION
1380 WHEN invalid_parameters
1381 THEN
1382 x_return_status := wsh_util_core.g_ret_sts_error;
1383 IF l_debug_on THEN
1384 WSH_DEBUG_SV.logmsg(l_module_name,'INVALID_PARAMETERS exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1385 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:INVALID_PARAMETERS');
1386 END IF;
1387 WHEN OTHERS
1388 THEN
1389 x_return_status := wsh_util_core.g_ret_sts_unexp_error;
1390 IF l_debug_on THEN
1391 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,
1392 WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1393 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1394 END IF;
1395 END log_interface_errors;
1396
1397 END wsh_interface_validations_pkg;
[Home] [Help]