[Home] [Help]
PACKAGE BODY: APPS.WSH_INBOUND_TXN_HISTORY_PKG
Source
1 PACKAGE BODY WSH_INBOUND_TXN_HISTORY_PKG as
2 /* $Header: WSHIBTXB.pls 120.0 2005/05/26 18:03:27 appldev noship $ */
3
4
5 --
6 G_PKG_NAME CONSTANT VARCHAR2(50) := 'WSH_INBOUND_TXN_HISTORY_PKG';
7 --
8 --===================
9 -- PROCEDURES
10 --===================
11
12 PROCEDURE create_txn_history_bulk
13 (
14 x_inboundTxnHistory_recTbl IN OUT NOCOPY inboundTxnHistory_recTbl_type,
15 x_return_status OUT NOCOPY VARCHAR2
16 )
17 IS
18 --{
19 l_param_name VARCHAR2(150);
20 l_index NUMBER;
21 l_inputCount NUMBER;
22 l_resultCount NUMBER;
23 --}
24 --
25 l_debug_on BOOLEAN;
26 --
27 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CREATE_TXN_HISTORY_BULK';
28 --
29 BEGIN
30 --{
31 --
32 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
33 --
34 IF l_debug_on IS NULL
35 THEN
36 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
37 END IF;
38 --
39 l_inputCount := x_inboundTxnHistory_recTbl.transaction_type.COUNT;
40 --
41 -- Debug Statements
42 --
43 IF l_debug_on THEN
44 WSH_DEBUG_SV.push(l_module_name);
45 WSH_DEBUG_SV.log(l_module_name,'l_inputCount',l_inputCount);
46 END IF;
47 --
48 x_return_status := wsh_util_core.g_ret_sts_success;
49 --
50 --
51 l_index := x_inboundTxnHistory_recTbl.transaction_type.FIRST;
52 --
53 WHILE l_index IS NOT NULL
54 LOOP
55 --{
56 IF (x_inboundTxnHistory_recTbl.transaction_type(l_index) is null) THEN
57 l_param_name := 'x_inboundTxnHistory_recTbl.transaction_type' || '(' || l_index || ')';
58 ELSIF x_inboundTxnHistory_recTbl.transaction_type(l_index) <> 'ROUTING_REQUEST'
59 AND x_inboundTxnHistory_recTbl.shipment_header_id(l_index) IS NULL THEN
60 l_param_name := 'x_inboundTxnHistory_recTbl.shipment_header_id' || '(' || l_index || ')';
61 ELSIF x_inboundTxnHistory_recTbl.status(l_index) IS NULL THEN
62 l_param_name := 'x_inboundTxnHistory_recTbl.status' || '(' || l_index || ')';
63 ELSIF x_inboundTxnHistory_recTbl.transaction_type(l_index) IN ('ASN', 'CANCEL_ASN') THEN
64 IF x_inboundTxnHistory_recTbl.shipment_number(l_index) IS NULL THEN
65 l_param_name := 'x_inboundTxnHistory_recTbl.shipment_number' || '(' || l_index || ')';
66 END IF;
67 ELSIF x_inboundTxnHistory_recTbl.transaction_type(l_index) IN (
68 'RECEIPT',
69 'RECEIPT_CORRECTION', 'RECEIPT_CORRECTION_POSITIVE','RECEIPT_CORRECTION_NEGATIVE',
70 'RTV' ,
71 'RTV_CORRECTION','RTV_CORRECTION_POSITIVE','RTV_CORRECTION_NEGATIVE'
72 )
73 THEN
74 IF x_inboundTxnHistory_recTbl.receipt_number(l_index) IS NULL THEN
75 l_param_name := 'x_inboundTxnHistory_recTbl.receipt_number' || '(' || l_index || ')';
76 END IF;
77 END IF;
78
79 IF l_param_name is not null THEN
80 FND_MESSAGE.SET_NAME('WSH','WSH_REQUIRED_FIELD_NULL');
81 FND_MESSAGE.SET_TOKEN('FIELD_NAME',l_param_name);
82 x_return_status := wsh_util_core.g_ret_sts_error;
83 wsh_util_core.add_message(x_return_status,l_module_name);
84 RAISE FND_API.G_EXC_ERROR;
85 END IF;
86 --
87 --
88 l_index := x_inboundTxnHistory_recTbl.transaction_type.NEXT(l_index);
89 --}
90 END LOOP;
91 --select wsh_inbound_txn_history_s.nextval into l_txn_id from dual;
92 --l_txn_id := wsh_inbound_txn_history_s.nextval;
93
94 FORALL i IN x_inboundTxnHistory_recTbl.transaction_type.FIRST..x_inboundTxnHistory_recTbl.transaction_type.LAST
95 insert into wsh_inbound_txn_history
96 (TRANSACTION_ID,
97 RECEIPT_NUMBER,
98 REVISION_NUMBER,
99 SHIPMENT_NUMBER,
100 TRANSACTION_TYPE,
101 SHIPMENT_HEADER_ID,
102 PARENT_SHIPMENT_HEADER_ID,
103 ORGANIZATION_ID,
104 SUPPLIER_ID,
105 SHIPPED_DATE,
106 RECEIPT_DATE,
107 STATUS,
108 MAX_RCV_TRANSACTION_ID,
109 CARRIER_ID,
110 MATCH_REVERTED_BY,
111 MATCHED_BY,
112 SHIPMENT_LINE_ID,
113 OBJECT_VERSION_NUMBER,
114 SHIP_FROM_LOCATION_ID,-- IB-Phase-2
115 LAST_UPDATE_DATE,
116 LAST_UPDATED_BY,
117 CREATION_DATE,
118 CREATED_BY,
119 LAST_UPDATE_LOGIN)
120 values(wsh_inbound_txn_history_s.nextval,
121 x_inboundTxnHistory_recTbl.RECEIPT_NUMBER(i),
122 x_inboundTxnHistory_recTbl.REVISION_NUMBER(i),
123 x_inboundTxnHistory_recTbl.SHIPMENT_NUMBER(i),
124 x_inboundTxnHistory_recTbl.TRANSACTION_TYPE(i),
125 x_inboundTxnHistory_recTbl.SHIPMENT_HEADER_ID(i),
126 x_inboundTxnHistory_recTbl.PARENT_SHIPMENT_HEADER_ID(i),
127 x_inboundTxnHistory_recTbl.ORGANIZATION_ID(i),
128 x_inboundTxnHistory_recTbl.SUPPLIER_ID(i),
129 x_inboundTxnHistory_recTbl.SHIPPED_DATE(i),
130 x_inboundTxnHistory_recTbl.RECEIPT_DATE(i),
131 x_inboundTxnHistory_recTbl.STATUS(i),
132 x_inboundTxnHistory_recTbl.MAX_RCV_TRANSACTION_ID(i),
133 x_inboundTxnHistory_recTbl.CARRIER_ID(i),
134 x_inboundTxnHistory_recTbl.MATCH_REVERTED_BY(i),
135 x_inboundTxnHistory_recTbl.MATCHED_BY(i),
136 x_inboundTxnHistory_recTbl.SHIPMENT_LINE_ID(i),
137 1,
138 x_inboundTxnHistory_recTbl.SHIP_FROM_LOCATION_ID(i),-- IB-Phase-2
139 SYSDATE,
140 FND_GLOBAL.USER_ID,
141 SYSDATE,
142 FND_GLOBAL.USER_ID,
143 FND_GLOBAL.LOGIN_ID)
144 RETURNING transaction_id BULK COLLECT INTO x_inboundTxnHistory_recTbl.TRANSACTION_ID;
145 --
146 --
147 l_resultCount := SQL%ROWCOUNT;
148 --
149 IF l_resultCount <> l_inputCount
150 THEN
151 --{
152 --
153 IF l_debug_on THEN
154 WSH_DEBUG_SV.log(l_module_name,'l_resultCount',l_resultCount);
155 END IF;
156 --
157 FND_MESSAGE.SET_NAME('WSH','WSH_IB_TXN_BULK_INSERT_ERROR');
158 wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_ERROR,l_module_name);
159 RAISE FND_API.G_EXC_ERROR;
160 --}
161 END IF;
162 --}
163 --
164 -- Debug Statements
165 --
166 IF l_debug_on THEN
167 WSH_DEBUG_SV.pop(l_module_name);
168 END IF;
169 --
170 EXCEPTION
171 --{
172 WHEN FND_API.G_EXC_ERROR THEN
173 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
174 --
175 -- Debug Statements
176 --
177 IF l_debug_on THEN
178 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
179 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
180 END IF;
181 --
182 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
183 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
184 --
185 -- Debug Statements
186 --
187 IF l_debug_on THEN
188 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_UNEXPECTED_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
189 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
190 END IF;
191 --
192 WHEN WSH_UTIL_CORE.G_EXC_WARNING THEN
193 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
194 --
195 -- Debug Statements
196 --
197 IF l_debug_on THEN
198 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_UTIL_CORE.G_EXC_WARNING exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
199 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_UTIL_CORE.G_EXC_WARNING');
200 END IF;
201 --
202 WHEN OTHERS THEN
203 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
204 wsh_util_core.default_handler('WSH_INBOUND_TXN_HISTORY_PKG.create_txn_history_bulk');
205 --}
206 --
207 -- Debug Statements
208 --
209 IF l_debug_on THEN
210 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
211 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
212 END IF;
213 --
214 END create_txn_history_bulk;
215
216
217 PROCEDURE autonomous_Create_bulk
218 (
219 x_inboundTxnHistory_recTbl IN OUT NOCOPY inboundTxnHistory_recTbl_type,
220 x_return_status OUT NOCOPY VARCHAR2
221 )
222 IS
223 PRAGMA AUTONOMOUS_TRANSACTION;
224 --{
225 l_num_warnings NUMBER := 0;
226 l_num_errors NUMBER := 0;
227 l_return_status VARCHAR2(30);
228 --}
229 --
230 l_debug_on BOOLEAN;
231 --
232 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'AUTONOMOUS_CREATE_bulk';
233 --
234 BEGIN
235 --{
236 --
237 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
238 --
239 IF l_debug_on IS NULL
240 THEN
241 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
242 END IF;
243 --
244 --
245 -- Debug Statements
246 --
247 IF l_debug_on THEN
248 WSH_DEBUG_SV.push(l_module_name);
249 END IF;
250 --
251 x_return_status := wsh_util_core.g_ret_sts_success;
252 --
253 --
254 IF l_debug_on THEN
255 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit create_txn_history_bulk',WSH_DEBUG_SV.C_PROC_LEVEL);
256 END IF;
257 --
258 create_txn_history_bulk
259 (
260 x_inboundTxnHistory_recTbl => x_inboundTxnHistory_recTbl,
261 x_return_status => l_return_status
262 );
263 --
264 IF l_debug_on THEN
265 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
266 END IF;
267 --
268 wsh_util_core.api_post_call
269 (
270 p_return_status => l_return_status,
271 x_num_warnings => l_num_warnings,
272 x_num_errors => l_num_errors
273 );
274 --
275 COMMIT;
276 --
277 IF l_debug_on THEN
278 WSH_DEBUG_SV.logmsg(l_module_name,
279 'Number of Errors='||l_num_errors||',Number of Warnings='||l_num_warnings);
280 END IF;
281 --
282 IF l_num_errors > 0
283 THEN
284 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
285 ELSIF l_num_warnings > 0
286 THEN
287 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
288 ELSE
289 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
290 END IF;
291 --
292 --
293 IF l_debug_on THEN
294 WSH_DEBUG_SV.pop(l_module_name);
295 END IF;
296 --
297 --}
298 EXCEPTION
299 --{
300 WHEN FND_API.G_EXC_ERROR THEN
301 ROLLBACK;
302 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
303 --
304 -- Debug Statements
305 --
306 IF l_debug_on THEN
307 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
308 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
309 END IF;
310 --
311 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
312 ROLLBACK;
313 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
314 --
315 -- Debug Statements
316 --
317 IF l_debug_on THEN
318 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_UNEXPECTED_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
319 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
320 END IF;
321 --
322 WHEN WSH_UTIL_CORE.G_EXC_WARNING THEN
323 COMMIT;
324 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
325 --
326 -- Debug Statements
327 --
328 IF l_debug_on THEN
329 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_UTIL_CORE.G_EXC_WARNING exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
330 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_UTIL_CORE.G_EXC_WARNING');
331 END IF;
332 --
333 WHEN OTHERS THEN
334 ROLLBACK;
335 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
336 wsh_util_core.default_handler('WSH_INBOUND_TXN_HISTORY_PKG.AUTONOMOUS_CREATE_bulk');
337 --}
338 --
339 -- Debug Statements
340 --
341 IF l_debug_on THEN
342 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
343 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
344 END IF;
345 --
346 END autonomous_Create_bulk;
347
348 --========================================================================
349 -- PROCEDURE : Create_Txn_History This procedure is used to create
350 -- a record in the wsh_inbound_txn_history
351 -- table
352 --
353 -- PARAMETERS: p_txn_history_rec This is of type ib_txn_history_rec_type.
354 -- x_txn_id Transacion Id returned by the API
355 -- after inserting a record into
356 -- wsh_inbound_txn_history.
357 -- x_return_status return status of the API.
358
359 -- VERSION : current version 1.0
360 -- initial version 1.0
361 -- COMMENT : This procedure is used to create a record in the
362 -- wsh_inbound_txn_history table.
363 -- The following are the valid transaction types -
364 -- ASN, RECEIPT, RECEIPT_ADD, RECEIPT_CORRECTION_NEGATIVE,
365 -- RECEIPT_CORRECTION_POSITIVE, ROUTING_REQUEST,
366 -- ROUTING_RESPONSE, RTV, RECEIPT_CORRECTION, RTV_CORRECTION,
367 -- CANCEL_ASN, RTV_CORRECTION_POSITIVE,RTV_CORRECTION_NEGATIVE,
368 -- RECEIPT_HEADER_UPD.
369 --========================================================================
370 PROCEDURE create_txn_history (
371 p_txn_history_rec IN ib_txn_history_rec_type,
372 x_txn_id OUT NOCOPY NUMBER,
373 x_return_status OUT NOCOPY VARCHAR2
374 )
375 IS
376 --{
377 l_param_name VARCHAR2(32767);
378 l_txn_id NUMBER;
379 --}
380 --
381 l_debug_on BOOLEAN;
382 --
383 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CREATE_TXN_HISTORY';
384 --
385 BEGIN
386 --{
387 --
388 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
389 --
390 IF l_debug_on IS NULL
391 THEN
392 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
393 END IF;
394 --
395 --
396 -- Debug Statements
397 --
398 IF l_debug_on THEN
399 WSH_DEBUG_SV.push(l_module_name);
400 END IF;
401 --
402 x_return_status := wsh_util_core.g_ret_sts_success;
403
404 -- This to verify all the mandatory input parameters for each transaction
405 -- type.
406 IF (p_txn_history_rec.transaction_type is null) THEN
407 l_param_name := 'p_txn_history_rec.transaction_type';
408 ELSIF p_txn_history_rec.transaction_type <> 'ROUTING_REQUEST'
409 AND p_txn_history_rec.shipment_header_id IS NULL THEN
410 l_param_name := 'p_txn_history_rec.shipment_header_id';
411 ELSIF p_txn_history_rec.status IS NULL THEN
412 l_param_name := 'p_txn_history_rec.status';
413 ELSIF p_txn_history_rec.transaction_type IN ('ASN', 'CANCEL_ASN') THEN
414 IF p_txn_history_rec.shipment_number IS NULL THEN
415 l_param_name := 'p_txn_history_rec.shipment_number';
416 END IF;
417 ELSIF p_txn_history_rec.transaction_type IN (
418 'RECEIPT',
419 'RECEIPT_CORRECTION', 'RECEIPT_CORRECTION_POSITIVE','RECEIPT_CORRECTION_NEGATIVE',
420 'RTV' ,
421 'RTV_CORRECTION','RTV_CORRECTION_POSITIVE','RTV_CORRECTION_NEGATIVE'
422 )
423 THEN
424 IF p_txn_history_rec.receipt_number IS NULL THEN
425 l_param_name := 'p_txn_history_rec.receipt_number';
426 END IF;
427 END IF;
428
429 IF l_param_name is not null THEN
430 FND_MESSAGE.SET_NAME('WSH','WSH_REQUIRED_FIELD_NULL');
431 FND_MESSAGE.SET_TOKEN('FIELD_NAME',l_param_name);
432 x_return_status := wsh_util_core.g_ret_sts_error;
433 wsh_util_core.add_message(x_return_status,l_module_name);
434 RAISE FND_API.G_EXC_ERROR;
435 END IF;
436
437 --select wsh_inbound_txn_history_s.nextval into l_txn_id from dual;
438 --l_txn_id := wsh_inbound_txn_history_s.nextval;
439
440 insert into wsh_inbound_txn_history
441 (TRANSACTION_ID,
442 RECEIPT_NUMBER,
443 REVISION_NUMBER,
444 SHIPMENT_NUMBER,
445 TRANSACTION_TYPE,
446 SHIPMENT_HEADER_ID,
447 PARENT_SHIPMENT_HEADER_ID,
448 ORGANIZATION_ID,
449 SUPPLIER_ID,
450 SHIPPED_DATE,
451 RECEIPT_DATE,
452 STATUS,
453 MAX_RCV_TRANSACTION_ID,
454 CARRIER_ID,
455 MATCH_REVERTED_BY,
456 MATCHED_BY,
457 SHIPMENT_LINE_ID,
458 OBJECT_VERSION_NUMBER,
459 SHIP_FROM_LOCATION_ID, -- IB-Phase-2
460 LAST_UPDATE_DATE,
461 LAST_UPDATED_BY,
462 CREATION_DATE,
463 CREATED_BY,
464 LAST_UPDATE_LOGIN)
465 values(wsh_inbound_txn_history_s.nextval,
466 p_txn_history_rec.RECEIPT_NUMBER,
467 p_txn_history_rec.REVISION_NUMBER,
468 p_txn_history_rec.SHIPMENT_NUMBER,
469 p_txn_history_rec.TRANSACTION_TYPE,
470 p_txn_history_rec.SHIPMENT_HEADER_ID,
471 p_txn_history_rec.PARENT_SHIPMENT_HEADER_ID,
472 p_txn_history_rec.ORGANIZATION_ID,
473 p_txn_history_rec.SUPPLIER_ID,
474 p_txn_history_rec.SHIPPED_DATE,
475 p_txn_history_rec.RECEIPT_DATE,
476 p_txn_history_rec.STATUS,
477 p_txn_history_rec.MAX_RCV_TRANSACTION_ID,
478 p_txn_history_rec.CARRIER_ID,
479 p_txn_history_rec.MATCH_REVERTED_BY,
480 p_txn_history_rec.MATCHED_BY,
481 p_txn_history_rec.SHIPMENT_LINE_ID,
482 1,
483 p_txn_history_rec.SHIP_FROM_LOCATION_ID, -- IB-Phase-2
484 SYSDATE,
485 FND_GLOBAL.USER_ID,
486 SYSDATE,
487 FND_GLOBAL.USER_ID,
488 FND_GLOBAL.LOGIN_ID)
489 RETURNING transaction_id into x_txn_id;
490 --}
491 --
492 -- Debug Statements
493 --
494 IF l_debug_on THEN
495 WSH_DEBUG_SV.pop(l_module_name);
496 END IF;
497 --
498 EXCEPTION
499 --{
500 WHEN FND_API.G_EXC_ERROR THEN
501 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
502 --
503 -- Debug Statements
504 --
505 IF l_debug_on THEN
506 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
507 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
508 END IF;
509 --
510 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
511 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
512 --
513 -- Debug Statements
514 --
515 IF l_debug_on THEN
516 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_UNEXPECTED_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
517 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
518 END IF;
519 --
520 WHEN WSH_UTIL_CORE.G_EXC_WARNING THEN
521 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
522 --
523 -- Debug Statements
524 --
525 IF l_debug_on THEN
526 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_UTIL_CORE.G_EXC_WARNING exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
527 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_UTIL_CORE.G_EXC_WARNING');
528 END IF;
529 --
530 WHEN OTHERS THEN
531 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
532 wsh_util_core.default_handler('WSH_INBOUND_TXN_HISTORY_PKG.CREATE_TXN_HISTORY');
533 --}
534 --
535 -- Debug Statements
536 --
537 IF l_debug_on THEN
538 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
539 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
540 END IF;
541 --
542 END create_txn_history;
543
544 PROCEDURE autonomous_Create (
545 p_txn_history_rec IN ib_txn_history_rec_type,
546 x_txn_id OUT NOCOPY NUMBER,
547 x_return_status OUT NOCOPY VARCHAR2
548 )
549 IS
550 PRAGMA AUTONOMOUS_TRANSACTION;
551 --{
552 l_num_warnings NUMBER := 0;
553 l_num_errors NUMBER := 0;
554 l_return_status VARCHAR2(30);
555 --}
556 --
557 l_debug_on BOOLEAN;
558 --
559 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'AUTONOMOUS_CREATE';
560 --
561 BEGIN
562 --{
563 --
564 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
565 --
566 IF l_debug_on IS NULL
567 THEN
568 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
569 END IF;
570 --
571 --
572 -- Debug Statements
573 --
574 IF l_debug_on THEN
575 WSH_DEBUG_SV.push(l_module_name);
576 END IF;
577 --
578 x_return_status := wsh_util_core.g_ret_sts_success;
579 --
580 --
581 IF l_debug_on THEN
582 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit create_txn_history',WSH_DEBUG_SV.C_PROC_LEVEL);
583 END IF;
584 --
585 create_txn_history
586 (
587 p_txn_history_rec => p_txn_history_rec,
588 x_txn_id => x_txn_id,
589 x_return_status => l_return_status
590 );
591 --
592 IF l_debug_on THEN
593 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
594 END IF;
595 --
596 wsh_util_core.api_post_call
597 (
598 p_return_status => l_return_status,
599 x_num_warnings => l_num_warnings,
600 x_num_errors => l_num_errors
601 );
602 --
603 COMMIT;
604 --
605 IF l_debug_on THEN
606 WSH_DEBUG_SV.logmsg(l_module_name,
607 'Number of Errors='||l_num_errors||',Number of Warnings='||l_num_warnings);
608 END IF;
609 --
610 IF l_num_errors > 0
611 THEN
612 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
613 ELSIF l_num_warnings > 0
614 THEN
615 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
616 ELSE
617 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
618 END IF;
619 --
620 --
621 IF l_debug_on THEN
622 WSH_DEBUG_SV.pop(l_module_name);
623 END IF;
624 --
625 --}
626 EXCEPTION
627 --{
628 WHEN FND_API.G_EXC_ERROR THEN
629 ROLLBACK;
630 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
631 --
632 -- Debug Statements
633 --
634 IF l_debug_on THEN
635 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
636 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
637 END IF;
638 --
639 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
640 ROLLBACK;
641 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
642 --
643 -- Debug Statements
644 --
645 IF l_debug_on THEN
646 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_UNEXPECTED_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
647 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
648 END IF;
649 --
650 WHEN WSH_UTIL_CORE.G_EXC_WARNING THEN
651 COMMIT;
652 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
653 --
654 -- Debug Statements
655 --
656 IF l_debug_on THEN
657 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_UTIL_CORE.G_EXC_WARNING exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
658 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_UTIL_CORE.G_EXC_WARNING');
659 END IF;
660 --
661 WHEN OTHERS THEN
662 ROLLBACK;
663 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
664 wsh_util_core.default_handler('WSH_INBOUND_TXN_HISTORY_PKG.AUTONOMOUS_CREATE');
665 --}
666 --
667 -- Debug Statements
668 --
669 IF l_debug_on THEN
670 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
671 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
672 END IF;
673 --
674 END autonomous_Create;
675
676 --========================================================================
677 -- PROCEDURE : Update_Txn_History This procedure is used to update
678 -- a record in the wsh_inbound_txn_history
679 -- table
680 --
681 -- PARAMETERS: p_txn_history_rec This is of type ib_txn_history_rec_type.
682 -- x_return_status return status of the API.
683
684 -- VERSION : current version 1.0
685 -- initial version 1.0
686 -- COMMENT : This procedure is used to a update a record (all the attributes)
687 -- in the wsh_inbound_txn_history table.
688 --========================================================================
689 PROCEDURE update_txn_history (
690 p_txn_history_rec IN ib_txn_history_rec_type,
691 x_return_status OUT NOCOPY VARCHAR2
692 )
693 IS
694 --{
695 --}
696 --
697 l_debug_on BOOLEAN;
698 --
699 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UPDATE_TXN_HISTORY';
700 --
701 BEGIN
702 --{
703 --
704 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
705 --
706 IF l_debug_on IS NULL
707 THEN
708 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
709 END IF;
710 --
711 --
712 -- Debug Statements
713 --
714 IF l_debug_on THEN
715 WSH_DEBUG_SV.push(l_module_name);
716 END IF;
717 --
718 x_return_status := wsh_util_core.g_ret_sts_success;
719 update wsh_inbound_txn_history
720 set RECEIPT_NUMBER = DECODE
721 (
722 p_txn_history_rec.RECEIPT_NUMBER,
723 FND_API.G_MISS_CHAR,NULL,
724 NULL,RECEIPT_NUMBER,
725 p_txn_history_rec.RECEIPT_NUMBER),
726 REVISION_NUMBER = DECODE
727 (
728 p_txn_history_rec.REVISION_NUMBER,
729 FND_API.G_MISS_CHAR,NULL,
730 NULL,REVISION_NUMBER,
731 p_txn_history_rec.REVISION_NUMBER),
732 SHIPMENT_NUMBER = DECODE
733 (
734 p_txn_history_rec.SHIPMENT_NUMBER,
735 FND_API.G_MISS_CHAR,NULL,
736 NULL,SHIPMENT_NUMBER,
737 p_txn_history_rec.SHIPMENT_NUMBER),
738 TRANSACTION_TYPE = DECODE
739 (
740 p_txn_history_rec.TRANSACTION_TYPE,
741 FND_API.G_MISS_CHAR,NULL,
742 NULL,TRANSACTION_TYPE,
743 p_txn_history_rec.TRANSACTION_TYPE),
744 SHIPMENT_HEADER_ID = DECODE
745 (
746 p_txn_history_rec.SHIPMENT_HEADER_ID,
747 FND_API.G_MISS_NUM,NULL,
748 NULL,SHIPMENT_HEADER_ID,
749 p_txn_history_rec.SHIPMENT_HEADER_ID),
750 PARENT_SHIPMENT_HEADER_ID = DECODE
751 (
752 p_txn_history_rec.PARENT_SHIPMENT_HEADER_ID,
753 FND_API.G_MISS_NUM,NULL,
754 NULL,PARENT_SHIPMENT_HEADER_ID,
755 p_txn_history_rec.PARENT_SHIPMENT_HEADER_ID),
756 ORGANIZATION_ID = DECODE
757 (
758 p_txn_history_rec.ORGANIZATION_ID,
759 FND_API.G_MISS_NUM,NULL,
760 NULL,ORGANIZATION_ID,
761 p_txn_history_rec.ORGANIZATION_ID),
762 SUPPLIER_ID = DECODE
763 (
764 p_txn_history_rec.SUPPLIER_ID,
765 FND_API.G_MISS_NUM,NULL,
766 NULL,SUPPLIER_ID,
767 p_txn_history_rec.SUPPLIER_ID),
768 SHIPPED_DATE = DECODE
769 (
770 p_txn_history_rec.SHIPPED_DATE,
771 FND_API.G_MISS_DATE,NULL,
772 NULL,SHIPPED_DATE,
773 p_txn_history_rec.SHIPPED_DATE),
774 RECEIPT_DATE = DECODE
775 (
776 p_txn_history_rec.RECEIPT_DATE,
777 FND_API.G_MISS_DATE,NULL,
778 NULL,RECEIPT_DATE,
779 p_txn_history_rec.RECEIPT_DATE),
780 STATUS = DECODE
781 (
782 p_txn_history_rec.STATUS,
783 FND_API.G_MISS_CHAR,NULL,
784 NULL,STATUS,
785 p_txn_history_rec.STATUS),
786 MAX_RCV_TRANSACTION_ID = DECODE
787 (
788 p_txn_history_rec.MAX_RCV_TRANSACTION_ID,
789 FND_API.G_MISS_NUM,NULL,
790 NULL,MAX_RCV_TRANSACTION_ID,
791 p_txn_history_rec.MAX_RCV_TRANSACTION_ID),
792 CARRIER_ID = DECODE
793 (
794 p_txn_history_rec.CARRIER_ID,
795 FND_API.G_MISS_NUM,NULL,
796 NULL,CARRIER_ID,
797 p_txn_history_rec.CARRIER_ID),
798 MATCH_REVERTED_BY = DECODE
799 (
800 p_txn_history_rec.MATCH_REVERTED_BY,
801 FND_API.G_MISS_NUM,NULL,
802 NULL,MATCH_REVERTED_BY,
803 p_txn_history_rec.MATCH_REVERTED_BY),
804 MATCHED_BY = DECODE
805 (
806 p_txn_history_rec.MATCHED_BY,
807 FND_API.G_MISS_NUM,NULL,
808 NULL,MATCHED_BY,
809 p_txn_history_rec.MATCHED_BY),
810 SHIPMENT_LINE_ID = DECODE
811 (
812 p_txn_history_rec.SHIPMENT_LINE_ID,
813 FND_API.G_MISS_NUM,NULL,
814 NULL,SHIPMENT_LINE_ID,
815 p_txn_history_rec.SHIPMENT_LINE_ID),
816 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
817 -- { IB-Phase-2
818 SHIP_FROM_LOCATION_ID = DECODE
819 (
820 p_txn_history_rec.SHIP_FROM_LOCATION_ID,
821 FND_API.G_MISS_NUM,NULL,
822 NULL,SHIP_FROM_LOCATION_ID,
823 p_txn_history_rec.SHIP_FROM_LOCATION_ID),
824 -- } IB-Phase-2
825 LAST_UPDATE_DATE = SYSDATE,
826 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
827 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
828 where TRANSACTION_ID = p_txn_history_rec.transaction_id;
829 --}
830 --
831 -- Debug Statements
832 --
833 IF l_debug_on THEN
834 WSH_DEBUG_SV.pop(l_module_name);
835 END IF;
836 --
837 EXCEPTION
838 --{
839 WHEN FND_API.G_EXC_ERROR THEN
840 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
841 --
842 -- Debug Statements
843 --
844 IF l_debug_on THEN
845 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
846 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
847 END IF;
848 --
849 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
850 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
851 --
852 -- Debug Statements
853 --
854 IF l_debug_on THEN
855 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_UNEXPECTED_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
856 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
857 END IF;
858 --
859 WHEN WSH_UTIL_CORE.G_EXC_WARNING THEN
860 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
861 --
862 -- Debug Statements
863 --
864 IF l_debug_on THEN
865 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_UTIL_CORE.G_EXC_WARNING exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
866 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_UTIL_CORE.G_EXC_WARNING');
867 END IF;
868 --
869 WHEN OTHERS THEN
870 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
871 wsh_util_core.default_handler('WSH_INBOUND_TXN_HISTORY_PKG.UPDATE_TXN_HISTORY');
872 --}
873 --
874 -- Debug Statements
875 --
876 IF l_debug_on THEN
877 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
878 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
879 END IF;
880 --
881 END update_txn_history;
882
883 --========================================================================
884 -- PROCEDURE : Delete_Txn_History This procedure is used to delete
885 -- a record in the wsh_inbound_txn_history
886 -- table
887 --
888 -- PARAMETERS: p_transaction_id This is unique identifier of a record
889 -- in wsh_inbound_txn_history.
890 -- x_return_status return status of the API.
891 --
892 -- VERSION : current version 1.0
893 -- initial version 1.0
894 -- COMMENT : This procedure is used to delete a record in the
895 -- wsh_inbound_txn_history table.
896 --========================================================================
897 PROCEDURE delete_txn_history (
898 p_transaction_id IN NUMBER,
899 x_return_status OUT NOCOPY VARCHAR2
900 )
901 IS
902 --{
903 --}
904 --
905 l_debug_on BOOLEAN;
906 --
907 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'DELETE_TXN_HISTORY';
908 --
909 BEGIN
910 --{
911 --
912 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
913 --
914 IF l_debug_on IS NULL
915 THEN
916 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
917 END IF;
918 --
919 --
920 -- Debug Statements
921 --
922 IF l_debug_on THEN
923 WSH_DEBUG_SV.push(l_module_name);
924 --
925 WSH_DEBUG_SV.log(l_module_name,'P_TRANSACTION_ID',P_TRANSACTION_ID);
926 END IF;
927 --
928 x_return_status := wsh_util_core.g_ret_sts_success;
929 delete from wsh_inbound_txn_history
930 where transaction_id = p_transaction_id;
931 --}
932 --
933 -- Debug Statements
934 --
935 IF l_debug_on THEN
936 WSH_DEBUG_SV.pop(l_module_name);
937 END IF;
938 --
939 EXCEPTION
940 --{
941 WHEN FND_API.G_EXC_ERROR THEN
942 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
943 --
944 -- Debug Statements
945 --
946 IF l_debug_on THEN
947 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
948 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
949 END IF;
950 --
951 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
952 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
953 --
954 -- Debug Statements
955 --
956 IF l_debug_on THEN
957 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_UNEXPECTED_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
958 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
959 END IF;
960 --
961 WHEN WSH_UTIL_CORE.G_EXC_WARNING THEN
962 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
963 --
964 -- Debug Statements
965 --
966 IF l_debug_on THEN
967 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_UTIL_CORE.G_EXC_WARNING exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
968 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_UTIL_CORE.G_EXC_WARNING');
969 END IF;
970 --
971 WHEN OTHERS THEN
972 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
973 wsh_util_core.default_handler('WSH_INBOUND_TXN_HISTORY_PKG.DELETE_TXN_HISTORY');
974 --}
975 --
976 -- Debug Statements
977 --
978 IF l_debug_on THEN
979 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
980 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
981 END IF;
982 --
983 END delete_txn_history;
984
985 --========================================================================
986 -- PROCEDURE : Get_Txn_History This procedure is used to get the record
987 -- from wsh_inbound_txn_history based on the
988 -- inputs shipment_header_id
989 -- ,transaction_type and transaction_id.
990 --
991 -- PARAMETERS: p_transaction_id This is unique identifier of a record
992 -- in wsh_inbound_txn_history.
993 -- p_shipment_header_id Shipment_Header_id of the transaction.
994 -- p_transaction_type Type of Transaction.
995 -- x_txn_history_rec This is of type ib_txn_history_rec_type.
996 -- x_return_status return status of the API.
997
998 -- VERSION : current version 1.0
999 -- initial version 1.0
1000 -- COMMENT : This procedure is used to a get the record from
1001 -- wsh_inbound_txn_history based on the inputs
1002 -- shipment_header_id, transaction_type, and transaction_id.
1003 --========================================================================
1004 PROCEDURE get_txn_history (
1005 p_transaction_id IN NUMBER DEFAULT NULL,
1006 p_shipment_header_id IN NUMBER DEFAULT NULL,
1007 p_transaction_type IN VARCHAR2 DEFAULT NULL,
1008 x_txn_history_rec OUT NOCOPY ib_txn_history_rec_type,
1009 x_return_status OUT NOCOPY VARCHAR2
1010 )
1011 IS
1012 --{
1013 -- This cursor is used to get all the attributes of
1014 -- wsh_inbound_txn_history based on the input parameters.
1015 cursor l_txn_history_csr is
1016 select TRANSACTION_ID,
1017 RECEIPT_NUMBER,
1018 REVISION_NUMBER,
1019 SHIPMENT_NUMBER,
1020 TRANSACTION_TYPE,
1021 SHIPMENT_HEADER_ID,
1022 PARENT_SHIPMENT_HEADER_ID,
1023 ORGANIZATION_ID,
1024 SUPPLIER_ID,
1025 SHIPPED_DATE,
1026 RECEIPT_DATE,
1027 STATUS,
1028 MAX_RCV_TRANSACTION_ID,
1029 CARRIER_ID,
1030 MATCH_REVERTED_BY,
1031 MATCHED_BY,
1032 SHIPMENT_LINE_ID,
1033 OBJECT_VERSION_NUMBER,
1034 SHIP_FROM_LOCATION_ID -- IB-Phase-2
1035 from wsh_inbound_txn_history
1036 where transaction_id = p_transaction_id
1037 or (p_transaction_id is null
1038 AND shipment_header_id = p_shipment_header_id
1039 AND transaction_type = p_transaction_type);
1040 --}
1041 --
1042 l_debug_on BOOLEAN;
1043 --
1044 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_TXN_HISTORY';
1045 --
1046 BEGIN
1047 --{
1048 --
1049 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1050 --
1051 IF l_debug_on IS NULL
1052 THEN
1053 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1054 END IF;
1055 --
1056 --
1057 -- Debug Statements
1058 --
1059 IF l_debug_on THEN
1060 WSH_DEBUG_SV.push(l_module_name);
1061 --
1062 WSH_DEBUG_SV.log(l_module_name,'P_TRANSACTION_ID',P_TRANSACTION_ID);
1063 WSH_DEBUG_SV.log(l_module_name,'P_SHIPMENT_HEADER_ID',P_SHIPMENT_HEADER_ID);
1064 WSH_DEBUG_SV.log(l_module_name,'P_TRANSACTION_TYPE',P_TRANSACTION_TYPE);
1065 END IF;
1066 --
1067 x_return_status := wsh_util_core.g_ret_sts_success;
1068 IF (p_transaction_id IS NULL AND (p_shipment_header_id IS NULL or p_transaction_type is NULL) ) THEN
1069 --{
1070 RAISE FND_API.G_EXC_ERROR;
1071 --}
1072 END IF;
1073 open l_txn_history_csr;
1074 fetch l_txn_history_csr into x_txn_history_rec;
1075 close l_txn_history_csr;
1076 --}
1077 --
1078 -- Debug Statements
1079 --
1080 IF l_debug_on THEN
1081 WSH_DEBUG_SV.pop(l_module_name);
1082 END IF;
1083 --
1084 EXCEPTION
1085 --{
1086 WHEN FND_API.G_EXC_ERROR THEN
1087 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1088 --
1089 -- Debug Statements
1090 --
1091 IF l_debug_on THEN
1092 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1093 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
1094 END IF;
1095 --
1096 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1097 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
1098 --
1099 -- Debug Statements
1100 --
1101 IF l_debug_on THEN
1102 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_UNEXPECTED_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1103 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
1104 END IF;
1105 --
1106 WHEN WSH_UTIL_CORE.G_EXC_WARNING THEN
1107 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1108 --
1109 -- Debug Statements
1110 --
1111 IF l_debug_on THEN
1112 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_UTIL_CORE.G_EXC_WARNING exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1113 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_UTIL_CORE.G_EXC_WARNING');
1114 END IF;
1115 --
1116 WHEN OTHERS THEN
1117 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
1118 wsh_util_core.default_handler('WSH_INBOUND_TXN_HISTORY_PKG.GET_TXN_HISTORY');
1119 --}
1120 --
1121 -- Debug Statements
1122 --
1123 IF l_debug_on THEN
1124 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1125 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1126 END IF;
1127 --
1128 END get_txn_history;
1129 --
1130 --
1131 --========================================================================
1132 -- PROCEDURE : Post_Process This procedure is used to update the
1133 -- status column of the record in
1134 -- wsh_inbound_txn_history based on the
1135 -- inputs
1136 --
1137 -- PARAMETERS: p_shipment_header_id Shipment_Header_id of the transaction.
1138 -- p_max_rcv_txn_id Maximum rcv_transaction_id stored in
1139 -- wsh_inbound_txn_history.
1140 -- p_txn_status New Status of the transaction.
1141 -- p_txn_type Type of Transaction.
1142 -- x_txn_history_rec This is of type ib_txn_history_rec_type.
1143 -- x_return_status return status of the API.
1144
1145 -- VERSION : current version 1.0
1146 -- initial version 1.0
1147 -- COMMENT : This procedure is not being used any more.
1148 --========================================================================
1149 PROCEDURE post_process (
1150 p_shipment_header_id IN NUMBER,
1151 p_max_rcv_txn_id IN NUMBER,
1152 p_txn_status IN VARCHAR2,
1153 p_txn_type IN VARCHAR2,
1154 x_return_status OUT NOCOPY VARCHAR2
1155 )
1156 IS
1157 --{
1158 l_txn_id_tab wsh_util_core.id_tab_type;
1159 cursor l_txn_history_csr(p_shipment_header_id NUMBER) is
1160 select transaction_id
1161 from wsh_inbound_txn_history
1162 where transaction_type not in ('RECEIPT', 'ASN')
1163 and shipment_header_id = p_shipment_header_id;
1164
1165 l_txn_history_rec ib_txn_history_rec_type;
1166 l_return_status VARCHAR2(1);
1167 l_num_warnings NUMBER;
1168 l_num_errors NUMBER;
1169 --}
1170 --
1171 l_debug_on BOOLEAN;
1172 --
1173 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'POST_PROCESS';
1174 --
1175 BEGIN
1176 --{
1177 /*
1178 IF p_txn_type IS NULL THEN
1179 --{
1180 IF p_txn_status = 'MATCHED' THEN
1181 --{
1182 open l_txn_history_csr(p_shipment_header_id);
1183 fetch l_txn_history_csr bulk collect into l_txn_id_tab;
1184 IF l_txn_id_tab.count > 0 THEN
1185 --{
1186
1187 FORALL i in l_txn_id_tab.first..l_txn_id_tab.last
1188 delete from wsh_inbound_txn_history
1189 where transaction_id = l_txn_id_tab(i);
1190
1191 get_txn_history (
1192 p_shipment_header_id => p_shipment_header_id,
1193 p_transaction_type => p_txn_type,
1194 x_txn_history_rec => l_txn_history_rec,
1195 x_return_status => l_return_status);
1196
1197 wsh_util_core.api_post_call(
1198 p_return_status => l_return_status,
1199 x_num_warnings => l_num_warnings,
1200 x_num_errors => l_num_errors);
1201
1202 l_txn_history_rec.status := 'MATCHED';
1203 l_txn_history_rec.max_rcv_transaction_id := p_max_rcv_txn_id;
1204
1205 update_txn_history (
1206 p_txn_history_rec => l_txn_history_rec,
1207 x_return_status => l_return_status);
1208
1209 wsh_util_core.api_post_call(
1210 p_return_status => l_return_status,
1211 x_num_warnings => l_num_warnings,
1212 x_num_errors => l_num_errors);
1213
1214 --}
1215 END IF;
1216 --}
1217 END IF;
1218 --}
1219 ELSIF p_txn_type IN ('ASN', 'RECEIPT') THEN
1220 --{
1221 get_txn_history (
1222 p_shipment_header_id => p_shipment_header_id,
1223 p_transaction_type => p_txn_type,
1224 x_txn_history_rec => l_txn_history_rec,
1225 x_return_status => l_return_status);
1226
1227 wsh_util_core.api_post_call(
1228 p_return_status => l_return_status,
1229 x_num_warnings => l_num_warnings,
1230 x_num_errors => l_num_errors);
1231
1232 l_txn_history_rec.status := p_txn_status;
1233
1234 update_txn_history (
1235 p_txn_history_rec => l_txn_history_rec,
1236 x_return_status => l_return_status);
1237
1238 wsh_util_core.api_post_call(
1239 p_return_status => l_return_status,
1240 x_num_warnings => l_num_warnings,
1241 x_num_errors => l_num_errors);
1242 --}
1243 END IF;
1244 --}
1245 */
1246 --
1247 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1248 --
1249 IF l_debug_on IS NULL
1250 THEN
1251 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1252 END IF;
1253 --
1254 --
1255 -- Debug Statements
1256 --
1257 IF l_debug_on THEN
1258 WSH_DEBUG_SV.push(l_module_name);
1259 --
1260 WSH_DEBUG_SV.log(l_module_name,'P_SHIPMENT_HEADER_ID',P_SHIPMENT_HEADER_ID);
1261 WSH_DEBUG_SV.log(l_module_name,'P_MAX_RCV_TXN_ID',P_MAX_RCV_TXN_ID);
1262 WSH_DEBUG_SV.log(l_module_name,'P_TXN_STATUS',P_TXN_STATUS);
1263 WSH_DEBUG_SV.log(l_module_name,'P_TXN_TYPE',P_TXN_TYPE);
1264 END IF;
1265 --
1266 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1267 --
1268 -- Debug Statements
1269 --
1270 IF l_debug_on THEN
1271 WSH_DEBUG_SV.pop(l_module_name);
1272 END IF;
1273 --
1274 EXCEPTION
1275 --{
1276 WHEN FND_API.G_EXC_ERROR THEN
1277 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1278 --
1279 -- Debug Statements
1280 --
1281 IF l_debug_on THEN
1282 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1283 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
1284 END IF;
1285 --
1286 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1287 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
1288 --
1289 -- Debug Statements
1290 --
1291 IF l_debug_on THEN
1292 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_UNEXPECTED_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1293 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
1294 END IF;
1295 --
1296 WHEN WSH_UTIL_CORE.G_EXC_WARNING THEN
1297 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1298 --
1299 -- Debug Statements
1300 --
1301 IF l_debug_on THEN
1302 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_UTIL_CORE.G_EXC_WARNING exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1303 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_UTIL_CORE.G_EXC_WARNING');
1304 END IF;
1305 --
1306 WHEN OTHERS THEN
1307 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
1308 wsh_util_core.default_handler('WSH_INBOUND_TXN_HISTORY_PKG.POST_PROCESS');
1309 --}
1310 --
1311 -- Debug Statements
1312 --
1313 IF l_debug_on THEN
1314 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1315 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1316 END IF;
1317 --
1318 END post_process;
1319
1320
1321 PROCEDURE post_process
1322 (
1323 p_shipment_header_id IN NUMBER,
1324 p_max_rcv_txn_id IN NUMBER,
1325 p_action_code IN VARCHAR2, -- MATCHED/CANCEL/REVERT
1326 p_txn_type IN VARCHAR2, -- ASN/RECEIPT
1327 p_object_version_number IN NUMBER,
1328 x_return_status OUT NOCOPY VARCHAR2
1329 )
1330 IS
1331 --{
1332
1333 cursor txn_csr (p_shipment_header_id NUMBER) is
1334 select 1
1335 from wsh_inbound_txn_history
1336 where transaction_type not in ('RECEIPT', 'ASN')
1337 and shipment_header_id = p_shipment_header_id;
1338
1339 l_txn_history_rec ib_txn_history_rec_type;
1340 l_Receipttxn_history_rec ib_txn_history_rec_type;
1341 l_return_status VARCHAR2(1);
1342 l_locked VARCHAR2(1);
1343 l_status_code VARCHAR2(30);
1344 l_num_warnings NUMBER := 0;
1345 l_num_errors NUMBER := 0;
1346 l_max_txn_id NUMBER;
1347 l_txn_id NUMBER;
1348 l_dummy NUMBER := 0;
1349 --}
1350 --
1351 l_debug_on BOOLEAN;
1352 --
1353 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'POST_PROCESS';
1354 --
1355 BEGIN
1356 --{
1357 --
1358 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1359 --
1360 IF l_debug_on IS NULL
1361 THEN
1362 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1363 END IF;
1364 --
1365 --
1366 -- Debug Statements
1367 --
1368 IF l_debug_on THEN
1369 WSH_DEBUG_SV.push(l_module_name);
1370 --
1371 WSH_DEBUG_SV.log(l_module_name,'P_SHIPMENT_HEADER_ID',P_SHIPMENT_HEADER_ID);
1372 WSH_DEBUG_SV.log(l_module_name,'P_MAX_RCV_TXN_ID',P_MAX_RCV_TXN_ID);
1373 WSH_DEBUG_SV.log(l_module_name,'P_ACTION_CODE',P_ACTION_CODE);
1374 WSH_DEBUG_SV.log(l_module_name,'P_TXN_TYPE',P_TXN_TYPE);
1375 WSH_DEBUG_SV.log(l_module_name,'P_OBJECT_VERSION_NUMBER',P_OBJECT_VERSION_NUMBER);
1376 END IF;
1377 --
1378 x_return_status := wsh_util_core.g_ret_sts_success;
1379 --
1380 --
1381 IF l_debug_on THEN
1382 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit lock_asn_receipt_header',WSH_DEBUG_SV.C_PROC_LEVEL);
1383 END IF;
1384 --
1385 lock_asn_receipt_header
1386 (
1387 p_shipment_header_id => p_shipment_header_id,
1388 p_transaction_type => p_txn_type,
1389 p_on_error => 'RETRY', --'RETURN',
1390 p_on_noDataFound => WSH_UTIL_CORE.G_RET_STS_ERROR,
1391 x_txn_history_rec => l_txn_history_rec,
1392 x_return_status => l_return_status,
1393 x_locked => l_locked
1394 );
1395 --
1396 --
1397 --
1398 -- Debug Statements
1399 --
1400 IF l_debug_on THEN
1401 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
1402 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
1403 END IF;
1404 --
1405 wsh_util_core.api_post_call
1406 (
1407 p_return_status => l_return_status,
1408 x_num_warnings => l_num_warnings,
1409 x_num_errors => l_num_errors
1410 );
1411 --
1412 --
1413 IF p_txn_type = 'ASN'
1414 AND p_action_code = 'REVERT'
1415 THEN
1416 --{
1417 IF l_debug_on THEN
1418 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit lock_asn_receipt_header for receipt-asn',WSH_DEBUG_SV.C_PROC_LEVEL);
1419 END IF;
1420 --
1421 lock_asn_receipt_header
1422 (
1423 p_shipment_header_id => p_shipment_header_id,
1424 p_transaction_type => 'RECEIPT',
1425 p_on_error => 'RETRY', --'RETURN',
1426 p_on_noDataFound => WSH_UTIL_CORE.G_RET_STS_SUCCESS,
1427 x_txn_history_rec => l_Receipttxn_history_rec,
1428 x_return_status => l_return_status,
1429 x_locked => l_locked
1430 );
1431 --
1432 --
1433 --
1434 -- Debug Statements
1435 --
1436 IF l_debug_on THEN
1437 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
1438 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
1439 END IF;
1440 --
1441 wsh_util_core.api_post_call
1442 (
1443 p_return_status => l_return_status,
1444 x_num_warnings => l_num_warnings,
1445 x_num_errors => l_num_errors
1446 );
1447 --
1448 --
1449 IF l_Receipttxn_history_rec.status LIKE 'MATCHED%'
1450 THEN
1451 --{
1452 FND_MESSAGE.SET_NAME('WSH','WSH_ASN_REVERT_ERROR');
1453 FND_MESSAGE.SET_TOKEN('SHIPMENT_NUMBER',l_txn_history_rec.shipment_number);
1454 wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_ERROR,l_module_name);
1455 RAISE FND_API.G_EXC_ERROR;
1456 --}
1457 END IF;
1458 --}
1459 END IF;
1460 --
1461 --
1462 /*
1463 IF l_locked = 'N'
1464 THEN
1465 --{
1466 FND_MESSAGE.SET_NAME('WSH','WSH_IB_TXN_LOCK_ERROR');
1467 wsh_util_core.add_message(x_return_status,l_module_name);
1468 RAISE FND_API.G_EXC_ERROR;
1469 --}
1470 END IF;
1471 */
1472 --
1473 --
1474 IF l_txn_history_rec.object_version_number > p_object_version_number
1475 THEN
1476 --{
1477 FND_MESSAGE.SET_NAME('WSH','WSH_IB_TXN_CHANGE_ERROR');
1478 wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_ERROR,l_module_name);
1479 RAISE FND_API.G_EXC_ERROR;
1480 --}
1481 END IF;
1482 --
1483 --
1484 --
1485 --
1486 IF p_action_code = 'MATCHED'
1487 THEN
1488 l_txn_id := p_max_rcv_txn_id;
1489 ELSIF p_action_code = 'REVERT'
1490 THEN
1491 l_txn_id := 1E38;
1492 ELSE
1493 l_txn_id := 0;
1494 END IF;
1495 --
1496 IF l_txn_id > 0
1497 THEN
1498 --{
1499 DELETE wsh_inbound_txn_history
1500 WHERE transaction_type not in (C_ASN, C_RECEIPT)
1501 AND shipment_header_id = p_shipment_header_id
1502 AND max_rcv_transaction_id <= l_txn_id;
1503 --
1504 IF l_debug_on THEN
1505 WSH_DEBUG_SV.log(l_module_name,'Number of Records deleted from transaction history',SQL%ROWCOUNT);
1506 END IF;
1507 --
1508 --}
1509 END IF;
1510 --
1511 --
1512 IF p_action_code = 'REVERT'
1513 THEN
1514 l_txn_history_rec.status := C_PENDING;
1515 l_txn_history_rec.MATCH_REVERTED_BY := FND_GLOBAL.USER_ID;
1516 ELSIF p_action_code = 'CANCEL'
1517 THEN
1518 IF p_txn_type = C_ASN
1519 THEN
1520 l_txn_history_rec.status := C_CANCELLED;
1521 ELSE
1522 l_txn_history_rec.status := C_PENDING;
1523 END IF;
1524 ELSE
1525 --{
1526 OPEN txn_csr (p_shipment_header_id);
1527 FETCH txn_csr INTO l_dummy;
1528 CLOSE txn_csr;
1529 --
1530 IF l_dummy = 1
1531 THEN
1532 l_txn_history_rec.status := C_MATCHED_AND_CHILD_PENDING;
1533 ELSE
1534 l_txn_history_rec.status := C_MATCHED;
1535 END IF;
1536 --
1537 l_txn_history_rec.MATCHED_BY := FND_GLOBAL.USER_ID;
1538 --}
1539 END IF;
1540 --
1541 --
1542 IF l_debug_on THEN
1543 WSH_DEBUG_SV.log(l_module_name,'l_txn_history_rec.status',l_txn_history_rec.status);
1544 WSH_DEBUG_SV.log(l_module_name,'l_txn_history_rec.MATCH_REVERTED_BY',l_txn_history_rec.MATCH_REVERTED_BY);
1545 WSH_DEBUG_SV.log(l_module_name,'l_txn_history_rec.MATCHED_BY',l_txn_history_rec.MATCHED_BY);
1546 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit update_txn_history',WSH_DEBUG_SV.C_PROC_LEVEL);
1547 END IF;
1548 --
1549 update_txn_history
1550 (
1551 p_txn_history_rec => l_txn_history_rec,
1552 x_return_status => l_return_status
1553 );
1554 --
1555 --
1556 --
1557 -- Debug Statements
1558 --
1559 IF l_debug_on THEN
1560 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
1561 END IF;
1562 --
1563 wsh_util_core.api_post_call
1564 (
1565 p_return_status => l_return_status,
1566 x_num_warnings => l_num_warnings,
1567 x_num_errors => l_num_errors
1568 );
1569 --
1570 --
1571 IF p_txn_type = 'ASN'
1572 AND p_action_code = 'REVERT'
1573 AND l_Receipttxn_history_rec.transaction_id IS NOT NULL
1574 THEN
1575 --{
1576 IF l_debug_on THEN
1577 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit update_txn_history:receipt-asn',WSH_DEBUG_SV.C_PROC_LEVEL);
1578 END IF;
1579 --
1580 update_txn_history
1581 (
1582 p_txn_history_rec => l_Receipttxn_history_rec,
1583 x_return_status => l_return_status
1584 );
1585 --
1586 --
1587 --
1588 -- Debug Statements
1589 --
1590 IF l_debug_on THEN
1591 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
1592 END IF;
1593 --
1594 wsh_util_core.api_post_call
1595 (
1596 p_return_status => l_return_status,
1597 x_num_warnings => l_num_warnings,
1598 x_num_errors => l_num_errors
1599 );
1600 --}
1601 END IF;
1602 --
1603 --
1604 IF l_debug_on THEN
1605 WSH_DEBUG_SV.logmsg(l_module_name,
1606 'Number of Errors='||l_num_errors||',Number of Warnings='||l_num_warnings);
1607 END IF;
1608 --
1609 IF l_num_errors > 0
1610 THEN
1611 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1612 ELSIF l_num_warnings > 0
1613 THEN
1614 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1615 ELSE
1616 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1617 END IF;
1618 --
1619 IF l_debug_on THEN
1620 WSH_DEBUG_SV.pop(l_module_name);
1621 END IF;
1622 --
1623 --}
1624 EXCEPTION
1625 --{
1626 WHEN FND_API.G_EXC_ERROR THEN
1627 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1628 --
1629 -- Debug Statements
1630 --
1631 IF l_debug_on THEN
1632 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1633 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
1634 END IF;
1635 --
1636 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1637 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
1638 --
1639 -- Debug Statements
1640 --
1641 IF l_debug_on THEN
1642 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_UNEXPECTED_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1643 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
1644 END IF;
1645 --
1646 WHEN WSH_UTIL_CORE.G_EXC_WARNING THEN
1647 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1648 --
1649 -- Debug Statements
1650 --
1651 IF l_debug_on THEN
1652 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_UTIL_CORE.G_EXC_WARNING exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1653 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_UTIL_CORE.G_EXC_WARNING');
1654 END IF;
1655 --
1656 WHEN OTHERS THEN
1657 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
1658 wsh_util_core.default_handler('WSH_INBOUND_TXN_HISTORY_PKG.POST_PROCESS',l_module_name);
1659 --
1660 -- Debug Statements
1661 --
1662 IF l_debug_on THEN
1663 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1664 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1665 END IF;
1666 --
1667 --}
1668 END post_process;
1669
1670
1671 PROCEDURE lock_asn_receipt_header
1672 (
1673 p_shipment_header_id IN NUMBER DEFAULT NULL,
1674 p_transaction_type IN VARCHAR2 DEFAULT NULL,
1675 p_on_error IN VARCHAR2 DEFAULT 'RETURN', -- 'RETRY'
1676 p_on_noDataFound IN VARCHAR2 DEFAULT WSH_UTIL_CORE.G_RET_STS_ERROR, --WSH_UTIL_CORE.G_RET_STS_SUCCESS
1677 x_txn_history_rec OUT NOCOPY ib_txn_history_rec_type,
1678 x_return_status OUT NOCOPY VARCHAR2,
1679 x_locked OUT NOCOPY VARCHAR2 -- Y/N
1680 )
1681 IS
1682 --{
1683 CURSOR txn_csr is
1684 SELECT TRANSACTION_ID,
1685 RECEIPT_NUMBER,
1686 REVISION_NUMBER,
1687 SHIPMENT_NUMBER,
1688 TRANSACTION_TYPE,
1689 SHIPMENT_HEADER_ID,
1690 PARENT_SHIPMENT_HEADER_ID,
1691 ORGANIZATION_ID,
1692 SUPPLIER_ID,
1693 SHIPPED_DATE,
1694 RECEIPT_DATE,
1695 STATUS,
1696 MAX_RCV_TRANSACTION_ID,
1697 CARRIER_ID,
1698 MATCH_REVERTED_BY,
1699 MATCHED_BY,
1700 SHIPMENT_LINE_ID,
1701 OBJECT_VERSION_NUMBER,
1702 SHIP_FROM_LOCATION_ID -- IB-Phase-2
1703 FROM wsh_inbound_txn_history
1704 WHERE shipment_header_id = p_shipment_header_id
1705 AND transaction_type = p_transaction_type
1706 FOR UPDATE OF STATUS NOWAIT;
1707
1708 l_param_name VARCHAR2(200);
1709 l_found BOOLEAN;
1710 --
1711 record_locked exception;
1712 PRAGMA EXCEPTION_INIT(record_locked, -54);
1713
1714 --}
1715 --
1716 l_debug_on BOOLEAN;
1717 --
1718 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'LOCK_ASN_RECEIPT_HEADER';
1719 --
1720 BEGIN
1721 --{
1722 --
1723 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1724 --
1725 IF l_debug_on IS NULL
1726 THEN
1727 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1728 END IF;
1729 --
1730 --
1731 -- Debug Statements
1732 --
1733 IF l_debug_on THEN
1734 WSH_DEBUG_SV.push(l_module_name);
1735 --
1736 WSH_DEBUG_SV.log(l_module_name,'P_SHIPMENT_HEADER_ID',P_SHIPMENT_HEADER_ID);
1737 WSH_DEBUG_SV.log(l_module_name,'P_TRANSACTION_TYPE',P_TRANSACTION_TYPE);
1738 WSH_DEBUG_SV.log(l_module_name,'P_ON_ERROR',P_ON_ERROR);
1739 WSH_DEBUG_SV.log(l_module_name,'p_on_noDataFound',p_on_noDataFound);
1740 END IF;
1741 --
1742 x_return_status := wsh_util_core.g_ret_sts_success;
1743 x_locked := 'N';
1744 --
1745 IF p_shipment_header_id IS NULL
1746 THEN
1747 l_param_name := 'p_shipment_header_id';
1748 ELSIF p_transaction_type IS NULL
1749 THEN
1750 l_param_name := 'p_transaction_type';
1751 END IF;
1752 --
1753 IF l_param_name is not null
1754 THEN
1755 FND_MESSAGE.SET_NAME('WSH','WSH_REQUIRED_FIELD_NULL');
1756 FND_MESSAGE.SET_TOKEN('FIELD_NAME',l_param_name);
1757 wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_ERROR,l_module_name);
1758 RAISE FND_API.G_EXC_ERROR;
1759 END IF;
1760 --
1761 --
1762 IF p_transaction_type NOT IN ( C_ASN, C_RECEIPT )
1763 THEN
1764 --{
1765 FND_MESSAGE.SET_NAME('WSH','WSH_PUB_INVALID_PARAMETER');
1766 FND_MESSAGE.SET_TOKEN('FIELD_NAME','p_transaction_type');
1767 wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_ERROR,l_module_name);
1768 RAISE FND_API.G_EXC_ERROR;
1769 --}
1770 END IF;
1771 --
1772 --
1773 IF p_on_error NOT IN ( 'RETURN', 'RETRY' )
1774 THEN
1775 --{
1776 FND_MESSAGE.SET_NAME('WSH','WSH_PUB_INVALID_PARAMETER');
1777 FND_MESSAGE.SET_TOKEN('FIELD_NAME','p_on_error');
1778 wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_ERROR,l_module_name);
1779 RAISE FND_API.G_EXC_ERROR;
1780 --}
1781 END IF;
1782 --
1783 --
1784 IF p_on_noDataFound NOT IN ( WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_SUCCESS )
1785 THEN
1786 --{
1787 FND_MESSAGE.SET_NAME('WSH','WSH_PUB_INVALID_PARAMETER');
1788 FND_MESSAGE.SET_TOKEN('FIELD_NAME','p_on_noDataFound');
1789 wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_ERROR,l_module_name);
1790 RAISE FND_API.G_EXC_ERROR;
1791 --}
1792 END IF;
1793 --
1794 --
1795 LOOP
1796 --{
1797 BEGIN
1798 --{
1799 OPEN txn_csr;
1800 FETCH txn_csr INTO x_txn_history_rec;
1801 --
1802 l_found := txn_csr%FOUND;
1803 --
1804 CLOSE txn_csr;
1805 --
1806 IF l_found
1807 THEN
1808 x_locked := 'Y';
1809 ELSIF p_on_noDataFound = WSH_UTIL_CORE.G_RET_STS_ERROR
1810 THEN
1811 FND_MESSAGE.SET_NAME('WSH','WSH_IB_TXN_NOT_FOUND');
1812 FND_MESSAGE.SET_TOKEN('TXN_TYPE',p_transaction_type);
1813 FND_MESSAGE.SET_TOKEN('SHIPMENT_HEADER_ID',p_shipment_header_id);
1814 wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_ERROR,l_module_name);
1815 RAISE FND_API.G_EXC_ERROR;
1816 END IF;
1817 --
1818 EXIT;
1819 --}
1820 EXCEPTION
1821 --{
1822 WHEN RECORD_LOCKED THEN
1823 IF txn_csr%ISOPEN
1824 THEN
1825 CLOSE txn_csr;
1826 END IF;
1827 --
1828 IF p_on_error = 'RETURN'
1829 THEN
1830 EXIT;
1831 END IF;
1832 --}
1833 END;
1834 --}
1835 END LOOP;
1836 --
1837 -- Debug Statements
1838 --
1839 IF l_debug_on THEN
1840 WSH_DEBUG_SV.pop(l_module_name);
1841 END IF;
1842 --
1843 --}
1844 EXCEPTION
1845 --{
1846 WHEN FND_API.G_EXC_ERROR THEN
1847 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1848 --
1849 -- Debug Statements
1850 --
1851 IF l_debug_on THEN
1852 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1853 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
1854 END IF;
1855 --
1856 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1857 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
1858 --
1859 -- Debug Statements
1860 --
1861 IF l_debug_on THEN
1862 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_UNEXPECTED_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1863 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
1864 END IF;
1865 --
1866 WHEN WSH_UTIL_CORE.G_EXC_WARNING THEN
1867 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1868 --
1869 -- Debug Statements
1870 --
1871 IF l_debug_on THEN
1872 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_UTIL_CORE.G_EXC_WARNING exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1873 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_UTIL_CORE.G_EXC_WARNING');
1874 END IF;
1875 --
1876 WHEN OTHERS THEN
1877 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
1878 wsh_util_core.default_handler('WSH_INBOUND_TXN_HISTORY_PKG.lock_asn_receipt_header',l_module_name);
1879 --
1880 -- Debug Statements
1881 --
1882 IF l_debug_on THEN
1883 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1884 END IF;
1885 --
1886 --}
1887 END lock_asn_receipt_header;
1888
1889 PROCEDURE lock_n_roll
1890 (
1891 p_transaction_id IN NUMBER DEFAULT NULL,
1892 x_return_status OUT NOCOPY VARCHAR2,
1893 x_locked OUT NOCOPY VARCHAR2 -- Y/N
1894 )
1895 IS
1896 --{
1897 CURSOR txn_csr is
1898 SELECT 1
1899 FROM wsh_inbound_txn_history
1900 WHERE transaction_id = p_transaction_id
1901 FOR UPDATE OF STATUS NOWAIT;
1902
1903 l_param_name VARCHAR2(200);
1904 l_found BOOLEAN;
1905 l_dummy NUMBER;
1906 --
1907 record_locked exception;
1908 PRAGMA EXCEPTION_INIT(record_locked, -54);
1909
1910 --}
1911 --
1912 l_debug_on BOOLEAN;
1913 --
1914 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'lock_n_roll';
1915 --
1916 BEGIN
1917 --{
1918 --
1919 SAVEPOINT lock_n_roll_sp;
1920 --
1921 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1922 --
1923 IF l_debug_on IS NULL
1924 THEN
1925 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1926 END IF;
1927 --
1928 --
1929 -- Debug Statements
1930 --
1931 IF l_debug_on THEN
1932 WSH_DEBUG_SV.push(l_module_name);
1933 --
1934 WSH_DEBUG_SV.log(l_module_name,'p_transaction_id',p_transaction_id);
1935 END IF;
1936 --
1937 x_return_status := wsh_util_core.g_ret_sts_success;
1938 x_locked := 'N';
1939 --
1940 IF p_transaction_id IS NULL
1941 THEN
1942 l_param_name := 'p_shipment_header_id';
1943 END IF;
1944 --
1945 IF l_param_name is not null
1946 THEN
1947 FND_MESSAGE.SET_NAME('WSH','WSH_REQUIRED_FIELD_NULL');
1948 FND_MESSAGE.SET_TOKEN('FIELD_NAME',l_param_name);
1949 wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_ERROR,l_module_name);
1950 RAISE FND_API.G_EXC_ERROR;
1951 END IF;
1952 --
1953 --
1954 OPEN txn_csr;
1955 FETCH txn_csr INTO l_dummy;
1956 --
1957 l_found := txn_csr%FOUND;
1958 --
1959 CLOSE txn_csr;
1960 --
1961 IF l_found
1962 THEN
1963 x_locked := 'Y';
1964 ELSE
1965 FND_MESSAGE.SET_NAME('WSH','WSH_IB_TXN_UPDATE_ERROR');
1966 FND_MESSAGE.SET_TOKEN('TRANSACTION_ID',p_transaction_id);
1967 wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_ERROR,l_module_name);
1968 RAISE FND_API.G_EXC_ERROR;
1969 END IF;
1970 --
1971 -- Debug Statements
1972 --
1973 ROLLBACK TO SAVEPOINT lock_n_roll_sp;
1974 --
1975 IF l_debug_on THEN
1976 WSH_DEBUG_SV.pop(l_module_name);
1977 END IF;
1978 --
1979 --}
1980 EXCEPTION
1981 --{
1982 WHEN RECORD_LOCKED THEN
1983 ROLLBACK TO SAVEPOINT lock_n_roll_sp;
1984 IF l_debug_on THEN
1985 WSH_DEBUG_SV.logmsg(l_module_name,'RECORD_LOCKED exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1986 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
1987 END IF;
1988 --
1989 WHEN FND_API.G_EXC_ERROR THEN
1990 ROLLBACK TO SAVEPOINT lock_n_roll_sp;
1991 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1992 --
1993 -- Debug Statements
1994 --
1995 IF l_debug_on THEN
1996 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1997 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
1998 END IF;
1999 --
2000 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2001 ROLLBACK TO SAVEPOINT lock_n_roll_sp;
2002 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
2003 --
2004 -- Debug Statements
2005 --
2006 IF l_debug_on THEN
2007 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_UNEXPECTED_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2008 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
2009 END IF;
2010 --
2011 WHEN WSH_UTIL_CORE.G_EXC_WARNING THEN
2012 ROLLBACK TO SAVEPOINT lock_n_roll_sp;
2013 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
2014 --
2015 -- Debug Statements
2016 --
2017 IF l_debug_on THEN
2018 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_UTIL_CORE.G_EXC_WARNING exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2019 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_UTIL_CORE.G_EXC_WARNING');
2020 END IF;
2021 --
2022 WHEN OTHERS THEN
2023 ROLLBACK TO SAVEPOINT lock_n_roll_sp;
2024 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
2025 wsh_util_core.default_handler('WSH_INBOUND_TXN_HISTORY_PKG.lock_n_roll',l_module_name);
2026 --
2027 -- Debug Statements
2028 --
2029 IF l_debug_on THEN
2030 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2031 END IF;
2032 --
2033 --}
2034 END lock_n_roll;
2035
2036
2037 PROCEDURE getTransactionTypeMeaning
2038 (
2039 p_transactionType IN VARCHAR2,
2040 x_transactionMeaning OUT NOCOPY VARCHAR2,
2041 x_return_status OUT NOCOPY VARCHAR2
2042 )
2043 IS
2044 --{
2045 CURSOR lookup_csr (p_lookupCode IN VARCHAR2,p_lookupType IN VARCHAR2)
2046 IS
2047 SELECT meaning,
2048 description
2049 FROM FND_LOOKUP_VALUES_VL
2050 WHERE lookup_code = p_lookupCode
2051 AND lookup_type = p_lookupType;
2052
2053 l_lookup_rec lookup_csr%ROWTYPE;
2054 --
2055 l_debug_on BOOLEAN;
2056 --
2057 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'getTransactionTypeMeaning';
2058 --}
2059 BEGIN
2060 --{
2061 --
2062 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2063 --
2064 IF l_debug_on IS NULL
2065 THEN
2066 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2067 END IF;
2068 --
2069 --
2070 IF l_debug_on THEN
2071 WSH_DEBUG_SV.push(l_module_name);
2072 --
2073 WSH_DEBUG_SV.log(l_module_name,'p_transactionType',p_transactionType);
2074 END IF;
2075 --
2076 x_return_status := wsh_util_core.g_ret_sts_success;
2077 --
2078 OPEN lookup_csr
2079 (
2080 p_lookupCode => p_transactionType,
2081 p_lookupType => 'WSH_IB_TXN_TYPE'
2082 );
2083 FETCH lookup_csr INTO l_lookup_rec;
2084 CLOSE lookup_csr;
2085 --
2086 x_transactionMeaning := l_lookup_rec.meaning;
2087 --
2088 IF l_lookup_rec.meaning IS NULL
2089 THEN
2090 FND_MESSAGE.SET_NAME('WSH','WSH_IB_INVALID_TXN_TYPE');
2091 FND_MESSAGE.SET_TOKEN('TXN_TYPE',p_transactionType);
2092 wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_ERROR,l_module_name);
2093 RAISE FND_API.G_EXC_ERROR;
2094 END IF;
2095 --
2096 --
2097 IF l_debug_on THEN
2098 WSH_DEBUG_SV.pop(l_module_name);
2099 END IF;
2100 --
2101 --}
2102 EXCEPTION
2103 --{
2104 WHEN FND_API.G_EXC_ERROR THEN
2105 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2106 --
2107 -- Debug Statements
2108 --
2109 IF l_debug_on THEN
2110 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2111 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
2112 END IF;
2113 --
2114 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2115 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
2116 --
2117 -- Debug Statements
2118 --
2119 IF l_debug_on THEN
2120 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_UNEXPECTED_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2121 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
2122 END IF;
2123 --
2124 WHEN WSH_UTIL_CORE.G_EXC_WARNING THEN
2125 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
2126 --
2127 -- Debug Statements
2128 --
2129 IF l_debug_on THEN
2130 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_UTIL_CORE.G_EXC_WARNING exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2131 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_UTIL_CORE.G_EXC_WARNING');
2132 END IF;
2133 --
2134 WHEN OTHERS THEN
2135 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
2136 wsh_util_core.default_handler('WSH_INBOUND_TXN_HISTORY_PKG.getTransactionTypeMeaning',l_module_name);
2137 --
2138 -- Debug Statements
2139 --
2140 IF l_debug_on THEN
2141 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2142 END IF;
2143 --
2144 --}
2145 END getTransactionTypeMeaning;
2146
2147 END WSH_INBOUND_TXN_HISTORY_PKG;