[Home] [Help]
PACKAGE BODY: APPS.WSH_TRANSACTIONS_HISTORY_PKG
Source
1 PACKAGE BODY WSH_TRANSACTIONS_HISTORY_PKG as
2 /* $Header: WSHTXHSB.pls 120.4 2011/12/21 10:15:02 skanduku ship $ */
3
4 --
5 G_PKG_NAME CONSTANT VARCHAR2(50) := 'WSH_TRANSACTIONS_HISTORY_PKG';
6 --
7
8 PROCEDURE Create_Update_Txns_History(
9 p_txns_history_rec IN OUT NOCOPY Txns_History_Record_Type,
10 x_txns_id OUT NOCOPY NUMBER,
11 x_return_status OUT NOCOPY VARCHAR2
12 ) IS
13
14 -- local variables
15 l_txns_id NUMBER;
16 l_exist_check NUMBER := 0;
17
18 l_transaction_id NUMBER;
19 x_transaction_id NUMBER;
20 l_transaction_status VARCHAR2(2);
21
22 --exceptions
23 invalid_status exception;
24 invalid_action exception;
25 invalid_entity_type exception;
26 invalid_direction exception;
27 invalid_document_type exception;
28
29 --cursors
30 CURSOR txn_cur IS
31 SELECT transaction_id, transaction_status
32 FROM wsh_transactions_history
33 WHERE document_type = p_txns_history_rec.document_type AND
34 document_number = p_txns_history_rec.document_number AND
35 document_direction = p_txns_history_rec.document_direction AND
36 action_type = p_txns_history_rec.action_type AND
37 entity_number = p_txns_history_rec.entity_number AND
38 entity_type = p_txns_history_rec.entity_type AND
39 trading_partner_id = p_txns_history_rec.trading_partner_id
40 FOR UPDATE NOWAIT;
41 --k proj bmso
42
43 l_status_code VARCHAR2(5);
44 l_trans_status VARCHAR2(5);
45 l_loc_interface_error_rec WSH_INTERFACE_VALIDATIONS_PKG.interface_errors_rec_type;
46 l_msg_data VARCHAR2(3000);
47 l_number_of_warnings NUMBER := 0;
48 l_number_of_errors NUMBER := 0;
49 l_return_status VARCHAR2(2);
50
51 CURSOR c_get_del_status (v_doc_number varchar2) IS
52 SELECT wnd.status_code
53 FROM wsh_new_deliveries wnd,
54 wsh_transactions_history wth
55 WHERE wth.document_number = v_doc_number
56 AND wth.entity_type = 'DLVY'
57 AND wth.document_type = 'SR'
58 AND wth.document_direction = 'O'
59 AND wth.action_type = 'A'
60 AND wth.entity_number = wnd.name
61 ORDER BY wth.transaction_id desc;
62
63 --
64 l_debug_on BOOLEAN;
65 --
66 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CREATE_UPDATE_TXNS_HISTORY';
67 --
68 BEGIN
69 --
70 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
71 --
72 IF l_debug_on IS NULL
73 THEN
74 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
75 END IF;
76 --
77 IF l_debug_on THEN
78 wsh_debug_sv.push(l_module_name,'Create_Update_Txns_History');
79 wsh_debug_sv.log (l_module_name, 'Transaction ID', p_txns_history_Rec.transaction_id);
80 wsh_debug_sv.log (l_module_name, 'document Type', p_txns_history_rec.document_type);
81 wsh_debug_sv.log (l_module_name, 'Doc Direction', p_txns_history_Rec.document_direction);
82 wsh_debug_sv.log (l_module_name, 'Doc number', p_txns_history_Rec.document_number);
83 wsh_debug_sv.log (l_module_name, 'Orig doc num', p_txns_history_Rec.orig_document_number);
84 wsh_debug_sv.log (l_module_name, 'Entity Type', p_txns_history_Rec.entity_type);
85 wsh_debug_sv.log (l_module_name, 'Entity number', p_txns_history_Rec.entity_number);
86 wsh_debug_sv.log (l_module_name, 'TP id', p_txns_history_Rec.trading_partner_id);
87 wsh_debug_sv.log (l_module_name, 'Action type', p_txns_history_Rec.action_type);
88 wsh_debug_sv.log (l_module_name, 'Transaction status', p_txns_history_Rec.transaction_status);
89 wsh_debug_sv.log (l_module_name, 'ECX Message ID', p_txns_history_Rec.ecx_message_id);
90 wsh_debug_sv.log (l_module_name, 'Event Name', p_txns_history_Rec.event_name);
91 wsh_debug_sv.log (l_module_name, 'Event Key', p_txns_history_Rec.event_key);
92 wsh_debug_sv.log (l_module_name, 'Item Type', p_txns_history_Rec.item_type);
93 wsh_debug_sv.log (l_module_name, 'In. control num', p_txns_history_Rec.internal_control_number);
94 --R12.1.1 STANDALONE PROJECT
95 wsh_debug_sv.log (l_module_name, 'Doc Revision', p_txns_history_Rec.document_revision);
96 END IF;
97
98 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
99
100 -- First check for null values
101 IF (
102 p_txns_history_rec.document_type IS NOT NULL AND
103 p_txns_history_rec.document_number IS NOT NULL AND
104 p_txns_history_rec.document_direction IS NOT NULL AND
105 p_txns_history_rec.transaction_status IS NOT NULL AND
106 p_txns_history_rec.entity_type IS NOT NULL AND
107 p_txns_history_rec.entity_number IS NOT NULL AND
108 p_txns_history_rec.action_type IS NOT NULL AND
109 p_txns_history_rec.trading_partner_id IS NOT NULL
110 ) THEN
111
112 -- validate the values
113 --Added Document Type SS for ShipScreening for ITM -AJPRABHA
114 IF(p_txns_history_rec.document_type NOT IN('SR', 'SA', 'SS')) THEN
115 raise invalid_document_type;
116 END IF;
117
118 IF(p_txns_history_rec.document_direction NOT IN('I', 'O')) THEN
119 raise invalid_direction;
120 END IF;
121 --R12.1.1 STANDALONE PROJECT
122 IF(p_txns_history_rec.entity_type NOT IN('DLVY', 'DLVY_INT', 'ORDER', 'BATCH')) THEN --Fulfillment Batch XML Project (Added BATCH)
123 raise invalid_entity_type;
124 END IF;
125 --R12.1.1 STANDALONE PROJECT
126 IF(p_txns_history_rec.action_type NOT IN('A', 'D', 'C')) THEN
127 raise invalid_action;
128 END IF;
129 --R12.1.1 STANDALONE PROJECT
130 IF(p_txns_history_rec.transaction_status NOT IN('ST', 'IP', 'ER', 'SC', 'AP')) THEN
131 raise invalid_status;
132 END IF;
133
134 -- Check if a record already exists
135
136 OPEN txn_cur;
137
138 FETCH txn_cur INTO l_transaction_id,l_transaction_status;
139
140 IF (txn_cur%NOTFOUND) THEN
141 IF l_debug_on THEN
142 wsh_debug_sv.log (l_module_name,'Record does not exist.
143 So create a new record in wsh_transactions_history');
144 wsh_debug_sv.log (l_module_name,'document_direction ',
145 p_txns_history_rec.document_direction);
146 wsh_debug_sv.log (l_module_name,'document_type ',
147 p_txns_history_rec.document_type);
148 wsh_debug_sv.log (l_module_name,'entity_number ',
149 p_txns_history_rec.entity_number);
150 END IF;
151 --bmso k proj
152 l_trans_status :=
153 p_txns_history_rec.transaction_status;
154 IF p_txns_history_rec.document_type = 'SA'
155 AND p_txns_history_rec.document_direction = 'I'
156 THEN --{
157 --
158 OPEN c_get_del_status(to_number(p_txns_history_rec.orig_document_number));
159 FETCH c_get_del_status INTO l_status_code;
160 CLOSE c_get_del_status;
161 --
162 IF l_debug_on THEN
163 wsh_debug_sv.log (l_module_name,
164 'entity_number ',
165 p_txns_history_rec.entity_number);
166 wsh_debug_sv.log (l_module_name,
167 'l_status_code ', l_status_code);
168 END IF;
169 --
170 IF l_status_code NOT IN ('SC','SR') THEN --{
171
172 -- the delivery has been unlocked , set the status
173 -- to error and insert an error message.
174
175 l_trans_status := 'SX';
176 l_loc_interface_error_rec.p_interface_table_name
177 := 'WSH_NEW_DEL_INTERFACE';
178 l_loc_interface_error_rec.p_interface_id :=
179 to_number(p_txns_history_rec.entity_number);
180 l_msg_data := FND_MESSAGE.GET_STRING('WSH',
181 'WSH_DEL_OPEN');
182
183 WSH_INTERFACE_VALIDATIONS_PKG.Log_Interface_Errors(
184 p_interface_errors_rec =>
185 l_loc_interface_error_rec,
186 p_msg_data => l_msg_data,
187 p_api_name => 'WSH_TRANSACTIONS_HISTORY_PKG.Create_Update_Txns_History',
188 x_return_status => l_return_status);
189
190 wsh_util_core.api_post_call(
191 p_return_status => l_return_status,
192 x_num_warnings => l_number_of_warnings,
193 x_num_errors => l_number_of_errors);
194
195 END IF; --}
196 --
197 END IF; --}
198 -- Record does not exist. So create a new record
199
200 -- Before Insert Check for validity of data
201 -- Need to validate document_direction, entity_type, action_type
202 -- ctd.. transaction_status, document_type
203
204 SELECT WSH_TRANSACTION_S.nextval
205 INTO x_transaction_id
206 FROM dual;
207
208 INSERT INTO wsh_transactions_history(
209 TRANSACTION_ID,
210 DOCUMENT_TYPE,
211 DOCUMENT_NUMBER,
212 ORIG_DOCUMENT_NUMBER,
213 DOCUMENT_DIRECTION,
214 TRANSACTION_STATUS,
215 ACTION_TYPE,
216 ENTITY_NUMBER,
217 ENTITY_TYPE,
218 TRADING_PARTNER_ID,
219 ECX_MESSAGE_ID,
220 EVENT_NAME,
221 EVENT_KEY,
222 ITEM_TYPE,
223 INTERNAL_CONTROL_NUMBER,
224 CREATION_DATE,
225 CREATED_BY,
226 LAST_UPDATE_DATE,
227 LAST_UPDATED_BY,
228 LAST_UPDATE_LOGIN,
229 --R12.1.1 STANDALONE PROJECT
230 DOCUMENT_REVISION,
231 PROGRAM_APPLICATION_ID,
232 PROGRAM_ID,
233 PROGRAM_UPDATE_DATE,
234 REQUEST_ID,
235 ATTRIBUTE_CATEGORY,
236 ATTRIBUTE1,
237 ATTRIBUTE2,
238 ATTRIBUTE3,
239 ATTRIBUTE4,
240 ATTRIBUTE5,
241 ATTRIBUTE6,
242 ATTRIBUTE7,
243 ATTRIBUTE8,
244 ATTRIBUTE9,
245 ATTRIBUTE10,
246 ATTRIBUTE11,
247 ATTRIBUTE12,
248 ATTRIBUTE13,
249 ATTRIBUTE14,
250 ATTRIBUTE15)
251 VALUES( x_transaction_id,
252 p_txns_history_rec.document_type,
253 p_txns_history_rec.document_number,
254 p_txns_history_rec.orig_document_number,
255 p_txns_history_rec.document_direction,
256 -- k proj bmso p_txns_history_rec.transaction_status,
257 l_trans_status,
258 p_txns_history_rec.action_type,
259 p_txns_history_rec.entity_number,
260 p_txns_history_rec.entity_type,
261 p_txns_history_rec.trading_partner_id,
262 p_txns_history_rec.ECX_MESSAGE_ID,
263 p_txns_history_rec.EVENT_NAME,
264 p_txns_history_rec.EVENT_KEY,
265 p_txns_history_rec.ITEM_TYPE,
266 p_txns_history_rec.INTERNAL_CONTROL_NUMBER,
267 SYSDATE,
268 FND_GLOBAL.USER_ID,
269 SYSDATE,
270 FND_GLOBAL.USER_ID,
271 FND_GLOBAL.USER_ID,
272 --R12.1.1 STANDALONE PROJECT
273 p_txns_history_rec.DOCUMENT_REVISION,
274 fnd_global.prog_appl_id,
275 fnd_global.conc_program_id,
276 SYSDATE,
277 fnd_global.conc_request_id,
278 p_txns_history_rec.ATTRIBUTE_CATEGORY,
279 p_txns_history_rec.ATTRIBUTE1,
280 p_txns_history_rec.ATTRIBUTE2,
281 p_txns_history_rec.ATTRIBUTE3,
282 p_txns_history_rec.ATTRIBUTE4,
283 p_txns_history_rec.ATTRIBUTE5,
284 p_txns_history_rec.ATTRIBUTE6,
285 p_txns_history_rec.ATTRIBUTE7,
286 p_txns_history_rec.ATTRIBUTE8,
287 p_txns_history_rec.ATTRIBUTE9,
288 p_txns_history_rec.ATTRIBUTE10,
289 p_txns_history_rec.ATTRIBUTE11,
290 p_txns_history_rec.ATTRIBUTE12,
291 p_txns_history_rec.ATTRIBUTE13,
292 p_txns_history_rec.ATTRIBUTE14,
293 p_txns_history_rec.ATTRIBUTE15);
294
295 x_txns_id := x_transaction_id;
296
297 ELSE
298 IF l_debug_on THEN
299 wsh_debug_sv.log (l_module_name,'Record already exists. So Need to Update in wsh_transactions_history');
300 END IF;
301 -- Record already exists. So Need to Update
302 -- Before Update Check for validity of status
303
304 IF(l_transaction_status = 'ST' AND p_txns_history_rec.transaction_status <> 'SC') THEN
305
306 raise invalid_status;
307 -- R12.1.1 STANDALONE PROJECT
308 ELSIF(l_transaction_status in ('IP', 'AP') AND p_txns_history_rec.transaction_status NOT IN('ER', 'SC', 'ST')) THEN
309 raise invalid_status;
310 --Fulfillment Batch XML Project : Included status 'ST'
311 ELSIF(l_transaction_status = 'ER' AND p_txns_history_rec.transaction_status NOT IN('IP','ER', 'SC','ST')) THEN
312 raise invalid_status;
313 ELSIF(l_transaction_status = 'SC') THEN
314 raise invalid_status;
315
316 END IF; -- if l_transaction_status checks
317
318
319 UPDATE wsh_transactions_history
320 SET entity_number = p_txns_history_rec.entity_number,
321 entity_type = p_txns_history_rec.entity_type,
322 transaction_status = p_txns_history_rec.transaction_status,
323 ecx_message_id = p_txns_history_rec.ecx_message_id,
324 event_name = p_txns_history_rec.event_name,
325 event_key = p_txns_history_rec.event_key,
326 internal_control_number = p_txns_history_rec.internal_control_number,
327 item_type = p_txns_history_rec.item_type,
328 last_update_date = SYSDATE,
329 last_updated_by = fnd_global.user_id,
330 --R12.1.1 STANDALONE PROJECT
331 program_application_id = fnd_global.prog_appl_id,
332 program_id = fnd_global.conc_program_id,
333 program_update_date = SYSDATE,
334 request_id = fnd_global.conc_request_id,
335 attribute_category = p_txns_history_rec.ATTRIBUTE_CATEGORY,
336 attribute1 = p_txns_history_rec.ATTRIBUTE1,
337 attribute2 = p_txns_history_rec.ATTRIBUTE2,
338 attribute3 = p_txns_history_rec.ATTRIBUTE3,
339 attribute4 = p_txns_history_rec.ATTRIBUTE4,
340 attribute5 = p_txns_history_rec.ATTRIBUTE5,
341 attribute6 = p_txns_history_rec.ATTRIBUTE6,
342 attribute7 = p_txns_history_rec.ATTRIBUTE7,
343 attribute8 = p_txns_history_rec.ATTRIBUTE8,
344 attribute9 = p_txns_history_rec.ATTRIBUTE9,
345 attribute10 = p_txns_history_rec.ATTRIBUTE10,
346 attribute11 = p_txns_history_rec.ATTRIBUTE11,
347 attribute12 = p_txns_history_rec.ATTRIBUTE12,
348 attribute13 = p_txns_history_rec.ATTRIBUTE13,
349 attribute14 = p_txns_history_rec.ATTRIBUTE14,
350 attribute15 = p_txns_history_rec.ATTRIBUTE15
351 WHERE transaction_id = l_transaction_id;
352
353
354 END IF; -- if txn_cur%notfound
355
356 IF(txn_cur%ISOPEN) THEN
357 CLOSE txn_cur;
358 END IF;
359 ELSE
360
361 -- Not Null checks failed. Return Error
362 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
363
364 END IF; -- if p_txns_history_rec columns are not null
365
366 IF l_debug_on THEN
367 wsh_debug_sv.pop(l_module_name);
368 END IF;
369 EXCEPTION
370 WHEN invalid_status THEN
371 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
372 IF l_debug_on THEN
373 WSH_DEBUG_SV.logmsg(l_module_name,'invalid_status exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
374 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:invalid_status');
375 END IF;
376 WHEN invalid_action THEN
377 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
378 IF l_debug_on THEN
379 WSH_DEBUG_SV.logmsg(l_module_name,'invalid_action exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
380 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:invalid_action');
381 END IF;
382 WHEN invalid_entity_type THEN
383 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
384 IF l_debug_on THEN
385 WSH_DEBUG_SV.logmsg(l_module_name,'invalid_entity_type exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
386 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:invalid_entity_type');
387 END IF;
388 WHEN invalid_direction THEN
389 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
390 IF l_debug_on THEN
391 WSH_DEBUG_SV.logmsg(l_module_name,'invalid_direction exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
392 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:invalid_direction');
393 END IF;
394 WHEN invalid_document_type THEN
395 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
396 IF l_debug_on THEN
397 WSH_DEBUG_SV.logmsg(l_module_name,'invalid_document_type exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
398 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:invalid_document_type');
399 END IF;
400 WHEN Others THEN
401 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
402 IF l_debug_on THEN
403 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,
404 WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
405 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
406 END IF;
407
408 END Create_Update_Txns_History;
409
410
411 PROCEDURE Get_Txns_History(
412 p_item_type IN VARCHAR2,
413 p_event_key IN VARCHAR2,
414 p_direction IN VARCHAR2,
415 p_document_type IN VARCHAR2,
416 p_txns_history_rec OUT NOCOPY Txns_History_Record_Type,
417 x_return_status OUT NOCOPY VARCHAR2
418 ) IS
419 -- LSP PROJECT : Added wndi table to get client_code
420 CURSOR txns_history_cur IS
421 SELECT wth.transaction_id,
422 wth.document_type,
423 wth.document_direction,
424 wth.document_number,
425 wth.orig_document_number,
426 wth.entity_number,
427 wth.entity_type,
428 wth.trading_partner_id,
429 wth.action_type,
430 wth.transaction_status,
431 wth.ecx_message_id,
432 wth.event_name,
433 wth.event_key ,
434 wth.item_type,
435 wth.internal_control_number,
436 --R12.1.1 STANDALONE PROJECT
437 wth.document_revision,
438 wth.attribute_category,
439 wth.attribute1,
440 wth.attribute2,
441 wth.attribute3,
442 wth.attribute4,
443 wth.attribute5,
444 wth.attribute6,
445 wth.attribute7,
446 wth.attribute8,
447 wth.attribute9,
448 wth.attribute10,
449 wth.attribute11,
450 wth.attribute12,
451 wth.attribute13,
452 wth.attribute14,
453 wth.attribute15,
454 wndi.client_code
455 FROM wsh_transactions_history wth,
456 wsh_new_del_interface wndi
457 WHERE wth.item_type = p_item_type
458 and wth.event_key = p_event_key
459 and wth.document_direction = p_direction
460 and wth.document_type = p_document_type
461 and wth.entity_number = wndi.delivery_interface_id (+);
462
463 --exceptions
464 no_record_found exception;
465
466 --
467 l_debug_on BOOLEAN;
468 --
469 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_TXNS_HISTORY';
470 --
471 BEGIN
472 --
473 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
474 --
475 IF l_debug_on IS NULL
476 THEN
477 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
478 END IF;
479 --
480 IF l_debug_on THEN
481 wsh_debug_sv.push(l_module_name,'Get_Txns_History');
482 wsh_debug_sv.log (l_module_name, 'Item Type', p_item_type);
483 wsh_debug_sv.log (l_module_name, 'Event Key', p_event_key);
484 wsh_debug_sv.log (l_module_name, 'Direction' , p_direction);
485 wsh_debug_sv.log (l_module_name, 'Document Type', p_document_type);
486 END IF;
487
488 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
489
490 OPEN txns_history_cur;
491 FETCH txns_history_cur INTO p_txns_history_rec;
492
493 IF(txns_history_cur%NOTFOUND) THEN
494 raise no_record_found;
495 END IF;
496
497 CLOSE txns_history_cur;
498
499 IF l_debug_on THEN
500 wsh_debug_sv.pop(l_module_name);
501 END IF;
502
503 EXCEPTION
504 WHEN no_record_found THEN
505
506 IF(txns_history_cur%ISOPEN) THEN
507 CLOSE txns_history_cur;
508 END IF;
509
510 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
511 IF l_debug_on THEN
512 WSH_DEBUG_SV.logmsg(l_module_name,'RECORD_LOCKED exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
513 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:RECORD_LOCKED');
514 END IF;
515 WHEN Others THEN
516 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
517 IF l_debug_on THEN
518 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,
519 WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
520 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
521 END IF;
522 END Get_Txns_History;
523
524 /*
525 -----------------------------------------------------------------------------
526 PROCEDURE : Create_Txns_History
527 PARAMETERS :
528 DESCRIPTION : This procedure is written for use by the inbound mapping.
529 Since XML gateway does not support calls to procedures with record types as
530 parameters, we need this wrapper. This takes in the individual columns,
531 creates a txns-history record and calls the create_update_txns_history
532 procedure with that record
533 -----------------------------------------------------------------------------
534 */
535 PROCEDURE Create_Txns_History(
536 p_transaction_id IN NUMBER,
537 p_document_type IN VARCHAR2,
538 p_document_direction IN VARCHAR2,
539 p_document_number IN VARCHAR2,
540 p_orig_document_number IN VARCHAR2,
541 p_entity_number IN VARCHAR2,
542 p_entity_type IN VARCHAR2,
543 p_trading_partner_id IN NUMBER,
544 p_action_type IN VARCHAR2,
545 p_transaction_status IN VARCHAR2,
546 p_ecx_message_id IN VARCHAR2,
547 p_event_name IN VARCHAR2,
548 p_event_key IN VARCHAR2,
549 p_item_type IN VARCHAR2,
550 p_internal_control_number IN VARCHAR2,
551 --R12.1.1 STANDALONE PROJECT
552 p_document_revision IN NUMBER DEFAULT NULL,
553 x_return_status OUT NOCOPY VARCHAR2) IS
554
555 l_txn_hist_rec Txns_History_Record_Type;
556 l_return_status VARCHAR2(30);
557 l_txn_id NUMBER;
558
559 create_update_failed exception;
560
561 --
562 l_debug_on BOOLEAN;
563 --
564 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CREATE_TXNS_HISTORY';
565 --
566 BEGIN
567 --
568 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
569 --
570 IF l_debug_on IS NULL
571 THEN
572 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
573 END IF;
574 --
575 IF l_debug_on THEN
576 wsh_debug_sv.push(l_module_name,'Get_Txns_History');
577 wsh_debug_sv.log (l_module_name, 'Transaction ID', p_transaction_id);
578 wsh_debug_sv.log (l_module_name, 'document Type', p_document_type);
579 wsh_debug_sv.log (l_module_name, 'Doc Direction', p_document_direction);
580 wsh_debug_sv.log (l_module_name, 'Doc number', p_document_number);
581 wsh_debug_sv.log (l_module_name, 'Orig doc num', p_orig_document_number);
582 wsh_debug_sv.log (l_module_name, 'Entity Type', p_entity_type);
583 wsh_debug_sv.log (l_module_name, 'Entity number', p_entity_number);
584 wsh_debug_sv.log (l_module_name, 'TP id', p_trading_partner_id);
585 wsh_debug_sv.log (l_module_name, 'Action type', p_action_type);
586 wsh_debug_sv.log (l_module_name, 'Transaction status', p_transaction_status);
587 wsh_debug_sv.log (l_module_name, 'ECX Message ID', p_ecx_message_id);
588 wsh_debug_sv.log (l_module_name, 'Event Name', p_event_name);
589 wsh_debug_sv.log (l_module_name, 'Event Key', p_event_key);
590 wsh_debug_sv.log (l_module_name, 'Item Type', p_item_type);
591 wsh_debug_sv.log (l_module_name, 'In. control num', p_internal_control_number);
592 --R12.1.1 STANDALONE PROJECT
593 wsh_debug_sv.log (l_module_name, 'Document Revision', p_document_revision);
594 END IF;
595
596 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
597
598 l_txn_hist_rec.transaction_id := p_transaction_id;
599 l_txn_hist_rec.document_type := p_document_type;
600 l_txn_hist_rec.document_direction := p_document_direction;
601 l_txn_hist_rec.document_number := p_document_number;
602 l_txn_hist_rec.orig_document_number := p_orig_document_number;
603 l_txn_hist_rec.entity_number := p_entity_number;
604 l_txn_hist_rec.entity_type := p_entity_type;
605 l_txn_hist_rec.trading_partner_id := p_trading_partner_id;
606 l_txn_hist_rec.action_type := p_action_type;
607 l_txn_hist_rec.transaction_status := p_transaction_status;
608 l_txn_hist_rec.ecx_message_id := p_ecx_message_id;
609 l_txn_hist_rec.event_name := p_event_name;
610 l_txn_hist_rec.event_key := p_event_key;
611 l_txn_hist_rec.item_type := p_item_type;
612 l_txn_hist_rec.internal_control_number := p_internal_control_number;
613 --Fulfillment Batch XML Gateway Project
614 IF NOT (p_document_type = 'SA' AND p_document_direction ='I' AND p_event_name='oracle.apps.wsh.batch.bsai' ) THEN
615 l_txn_hist_rec.document_revision := p_document_revision;
616 END IF;
617 Create_Update_Txns_History(
618 p_txns_history_rec => l_txn_hist_rec,
619 x_txns_id => l_txn_id,
620 x_return_status => l_return_status);
621
622 IF l_debug_on THEN
623 wsh_debug_sv.log (l_module_name, 'Return status from Create_Update_Txns_History', l_return_status);
624 END IF;
625
626 IF(l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
627 raise create_update_failed;
628 END IF;
629 IF l_debug_on THEN
630 wsh_debug_sv.pop(l_module_name);
631 END IF;
632 EXCEPTION
633 WHEN create_update_failed THEN
634 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
635 IF l_debug_on THEN
636 wsh_debug_sv.pop(l_module_name);
637 END IF;
638 WHEN Others THEN
639 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
640 IF l_debug_on THEN
641 wsh_debug_sv.pop(l_module_name,'EXCEPTION: '||SUBSTR(SQLERRM,1,200));
642 END IF;
643 END Create_Txns_History;
644
645 END WSH_TRANSACTIONS_HISTORY_PKG;