[Home] [Help]
PACKAGE BODY: APPS.RCV_TRANSACTIONS_HISTORY_PKG
Source
1 PACKAGE BODY rcv_transactions_history_pkg as
2 /* $Header: RCVTXHSB.pls 120.0.12010000.11 2010/04/13 11:23:19 smididud noship $ */
3
4 g_asn_debug VARCHAR2(1) := asn_debug.is_debug_on; -- Bug 9152790
5
6 --
7 G_PKG_NAME CONSTANT VARCHAR2(50) := 'RCV_TRANSACTIONS_HISTORY_PKG';
8 --
9
10 PROCEDURE Create_Update_Txns_History(
11 p_txns_history_rec IN OUT NOCOPY Txns_History_Record_Type,
12 P_xml_document_id IN NUMBER,
13 x_txns_id OUT NOCOPY NUMBER,
14 x_return_status OUT NOCOPY VARCHAR2
15 ) IS
16
17 -- local variables
18 l_txns_id NUMBER;
19 l_exist_check NUMBER := 0;
20
21 l_transaction_id NUMBER;
22 x_transaction_id NUMBER;
23 l_transaction_status VARCHAR2(2);
24 l_trans_status VARCHAR2(5);
25 l_msg_data VARCHAR2(3000);
26 l_xml_document_id NUMBER;
27 l_return_status VARCHAR2(2);
28
29 --exceptions
30 invalid_status exception;
31 invalid_action exception;
32 invalid_entity_type exception;
33 invalid_direction exception;
34 invalid_document_type exception;
35
36 --cursors
37 CURSOR txn_cur IS
38 SELECT transaction_id, transaction_status
39 FROM MTL_TXNS_HISTORY
40 WHERE document_type = p_txns_history_rec.document_type AND
41 document_number = p_txns_history_rec.document_number AND
42 document_direction = p_txns_history_rec.document_direction AND
43 action_type = p_txns_history_rec.action_type AND
44 entity_number = p_txns_history_rec.entity_number AND
45 entity_type = p_txns_history_rec.entity_type AND
46 trading_partner_id = p_txns_history_rec.trading_partner_id AND
47 event_name = p_txns_history_rec.event_name AND
48 event_key = p_txns_history_rec.event_key AND
49 transaction_status = 'IP'
50 FOR UPDATE NOWAIT;
51
52
53
54 BEGIN
55
56
57 IF (g_asn_debug = 'Y') THEN
58 asn_debug.put_line('Entering Create_Update_Txns_History');
59 asn_debug.put_line('Entity Type is '||p_txns_history_Rec.entity_type);
60 asn_debug.put_line('Entity number is '||p_txns_history_Rec.entity_number);
61 asn_debug.put_line('Transaction status is '||p_txns_history_Rec.transaction_status);
62 END IF;
63
64 x_return_status := rcv_error_pkg.g_ret_sts_success;
65 l_xml_document_id := P_xml_document_id;
66
67 IF (g_asn_debug = 'Y') THEN
68 asn_debug.put_line('l_xml_document_id is '|| to_char(l_xml_document_id));
69 END IF;
70
71
72 -- First check for null values
73 IF (
74 p_txns_history_rec.document_type IS NOT NULL AND
75 p_txns_history_rec.document_number IS NOT NULL AND
76 p_txns_history_rec.document_direction IS NOT NULL AND
77 p_txns_history_rec.transaction_status IS NOT NULL AND
78 p_txns_history_rec.entity_type IS NOT NULL AND
79 p_txns_history_rec.entity_number IS NOT NULL AND
80 p_txns_history_rec.action_type IS NOT NULL AND
81 p_txns_history_rec.trading_partner_id IS NOT NULL
82 ) THEN
83
84 IF(p_txns_history_rec.document_type NOT IN('RC')) THEN
85 raise invalid_document_type;
86 END IF;
87
88
89 IF(p_txns_history_rec.document_direction NOT IN('I', 'O')) THEN
90 raise invalid_direction;
91 END IF;
92
93
94 IF(p_txns_history_rec.entity_type NOT IN('RCPT')) THEN
95 raise invalid_entity_type;
96 END IF;
97
98 IF(p_txns_history_rec.action_type NOT IN('A', 'D', 'C')) THEN
99 raise invalid_action;
100 END IF;
101
102 IF(p_txns_history_rec.transaction_status NOT IN('ST', 'IP', 'ER', 'SC', 'AP')) THEN
103 raise invalid_status;
104 END IF;
105
106 -- Check if a record already exists
107
108
109 IF (g_asn_debug = 'Y') THEN
110 asn_debug.put_line('Opening txn_cur');
111 END IF;
112
113 OPEN txn_cur;
114
115
116 IF (g_asn_debug = 'Y') THEN
117 asn_debug.put_line('Fetching txn_cur');
118 END IF;
119
120
121
122 FETCH txn_cur INTO l_transaction_id,l_transaction_status;
123
124 IF (txn_cur%NOTFOUND) THEN
125
126 l_trans_status := p_txns_history_rec.transaction_status;
127
128 -- Record does not exist. So create a new record
129
130 -- Before Insert Check for validity of data
131 -- Need to validate document_direction, entity_type, action_type
132 -- ctd.. transaction_status, document_type
133
134
135 SELECT MTL_TXNS_HISTORY_S.nextval
136 INTO x_transaction_id
137 FROM dual;
138
139 IF (g_asn_debug = 'Y') THEN
140 asn_debug.put_line('Transaction ID is '|| x_transaction_id);
141 asn_debug.put_line('Inserting into MTL_TXNS_HISTORY');
142 END IF;
143
144
145 INSERT INTO MTL_TXNS_HISTORY(
146 TRANSACTION_ID,
147 DOCUMENT_TYPE,
148 DOCUMENT_NUMBER,
149 DOCUMENT_DIRECTION,
150 TRANSACTION_STATUS,
151 ACTION_TYPE,
152 ENTITY_NUMBER,
153 ENTITY_TYPE,
154 TRADING_PARTNER_ID,
155 EVENT_NAME,
156 EVENT_KEY,
157 ITEM_TYPE,
158 CREATION_DATE,
159 CREATED_BY,
160 LAST_UPDATE_DATE,
161 LAST_UPDATED_BY,
162 LAST_UPDATE_LOGIN,
163 DOCUMENT_REVISION,
164 PROGRAM_APPLICATION_ID,
165 PROGRAM_ID,
166 PROGRAM_UPDATE_DATE,
167 REQUEST_ID,
168 ATTRIBUTE_CATEGORY,
169 ATTRIBUTE1,
170 ATTRIBUTE2,
171 ATTRIBUTE3,
172 ATTRIBUTE4,
173 ATTRIBUTE5,
174 ATTRIBUTE6,
175 ATTRIBUTE7,
176 ATTRIBUTE8,
177 ATTRIBUTE9,
178 ATTRIBUTE10,
179 ATTRIBUTE11,
180 ATTRIBUTE12,
181 ATTRIBUTE13,
182 ATTRIBUTE14,
183 ATTRIBUTE15,
184 CLIENT_CODE)
185 VALUES( x_transaction_id,
186 p_txns_history_rec.document_type,
187 p_txns_history_rec.document_number,
188 p_txns_history_rec.document_direction,
189 -- k proj bmso p_txns_history_rec.transaction_status,
190 l_trans_status,
191 p_txns_history_rec.action_type,
192 p_txns_history_rec.entity_number,
193 p_txns_history_rec.entity_type,
194 p_txns_history_rec.trading_partner_id,
195 p_txns_history_rec.EVENT_NAME,
196 p_txns_history_rec.EVENT_KEY,
197 p_txns_history_rec.ITEM_TYPE,
198 SYSDATE,
199 FND_GLOBAL.USER_ID,
200 SYSDATE,
201 FND_GLOBAL.USER_ID,
202 FND_GLOBAL.USER_ID,
203 p_txns_history_rec.DOCUMENT_REVISION,
204 fnd_global.prog_appl_id,
205 fnd_global.conc_program_id,
206 SYSDATE,
207 fnd_global.conc_request_id,
208 p_txns_history_rec.ATTRIBUTE_CATEGORY,
209 p_txns_history_rec.ATTRIBUTE1,
210 p_txns_history_rec.ATTRIBUTE2,
211 p_txns_history_rec.ATTRIBUTE3,
212 p_txns_history_rec.ATTRIBUTE4,
213 p_txns_history_rec.ATTRIBUTE5,
214 p_txns_history_rec.ATTRIBUTE6,
215 p_txns_history_rec.ATTRIBUTE7,
216 p_txns_history_rec.ATTRIBUTE8,
217 p_txns_history_rec.ATTRIBUTE9,
218 p_txns_history_rec.ATTRIBUTE10,
219 p_txns_history_rec.ATTRIBUTE11,
220 p_txns_history_rec.ATTRIBUTE12,
221 p_txns_history_rec.ATTRIBUTE13,
222 p_txns_history_rec.ATTRIBUTE14,
223 p_txns_history_rec.ATTRIBUTE15,
224 p_txns_history_rec.client_code);
225
226
227 x_txns_id := x_transaction_id;
228
229 IF (g_asn_debug = 'Y') THEN
230 asn_debug.put_line('Record inserted into MTL_TXNS_HISTORY');
231 asn_debug.put_line('x_transaction_id is '||x_transaction_id);
232 END IF;
233
234 ELSE
235
236 IF (g_asn_debug = 'Y') THEN
237 asn_debug.put_line('transaction_id is ' || l_transaction_id);
238 asn_debug.put_line('l_transaction_status is ' || l_transaction_status);
239 asn_debug.put_line('p_txns_history_rec.transaction_status is ' || p_txns_history_rec.transaction_status);
240 asn_debug.put_line('Record already exists. So need to update in MTL_TXNS_HISTORY');
241 END IF;
242
243 -- Record already exists. So Need to Update
244 -- Before Update Check for validity of status
245
246 IF(l_transaction_status in ('IP', 'AP') AND p_txns_history_rec.transaction_status NOT IN('ER','ST','IP')) THEN
247 raise invalid_status;
248
249 ELSIF(l_transaction_status = 'ER' AND p_txns_history_rec.transaction_status NOT IN('IP','ER','ST')) THEN
250 raise invalid_status;
251
252 END IF; -- if l_transaction_status checks
253
254 IF (g_asn_debug = 'Y') THEN
255 asn_debug.put_line('Updating MTL_TXNS_HISTORY');
256 END IF;
257
258
259 UPDATE MTL_TXNS_HISTORY
260 SET entity_number = p_txns_history_rec.entity_number,
261 entity_type = p_txns_history_rec.entity_type,
262 transaction_status = p_txns_history_rec.transaction_status,
263 event_name = p_txns_history_rec.event_name,
264 event_key = p_txns_history_rec.event_key,
265 item_type = p_txns_history_rec.item_type,
266 last_update_date = SYSDATE,
270 program_update_date = SYSDATE,
267 last_updated_by = fnd_global.user_id,
268 program_application_id = fnd_global.prog_appl_id,
269 program_id = fnd_global.conc_program_id,
271 request_id = fnd_global.conc_request_id,
272 attribute_category = p_txns_history_rec.ATTRIBUTE_CATEGORY,
273 attribute1 = p_txns_history_rec.ATTRIBUTE1,
274 attribute2 = p_txns_history_rec.ATTRIBUTE2,
275 attribute3 = p_txns_history_rec.ATTRIBUTE3,
276 attribute4 = p_txns_history_rec.ATTRIBUTE4,
277 attribute5 = p_txns_history_rec.ATTRIBUTE5,
278 attribute6 = p_txns_history_rec.ATTRIBUTE6,
279 attribute7 = p_txns_history_rec.ATTRIBUTE7,
280 attribute8 = p_txns_history_rec.ATTRIBUTE8,
281 attribute9 = p_txns_history_rec.ATTRIBUTE9,
282 attribute10 = p_txns_history_rec.ATTRIBUTE10,
283 attribute11 = p_txns_history_rec.ATTRIBUTE11,
284 attribute12 = p_txns_history_rec.ATTRIBUTE12,
285 attribute13 = p_txns_history_rec.ATTRIBUTE13,
286 attribute14 = p_txns_history_rec.ATTRIBUTE14,
287 attribute15 = p_txns_history_rec.ATTRIBUTE15
288 WHERE transaction_id = l_transaction_id;
289
290 IF (g_asn_debug = 'Y') THEN
291 asn_debug.put_line('MTL_TXNS_HISTORY record updated');
292 END IF;
293
294
295 IF (l_xml_document_id is null) THEN
296
297 IF (p_txns_history_rec.client_code is not null) THEN
298
299 IF (g_asn_debug = 'Y') THEN
300 asn_debug.put_line('LSP : Updating RT.receipt_confirmation_extracted to Y');
301 END IF;
302
303 UPDATE rcv_transactions rt
304 SET rt.receipt_confirmation_extracted = 'Y',
305 rt.xml_document_id = p_txns_history_rec.document_number
306 WHERE rt.shipment_header_id = p_txns_history_rec.document_number
307 AND nvl(rt.receipt_confirmation_extracted, 'N') = 'P'
308 AND xml_document_id is null
309 AND (rt.TRANSACTION_TYPE = 'DELIVER' OR
310 (rt.TRANSACTION_TYPE IN ('CORRECT', 'RETURN TO RECEIVING')
311 AND EXISTS (SELECT '1' FROM rcv_transactions rt2
312 WHERE rt.parent_transaction_id = rt2.transaction_id
313 AND rt2.transaction_type = 'DELIVER')
314 ) OR
315 (rt.TRANSACTION_TYPE IN ('CORRECT')
316 AND EXISTS (SELECT '1' FROM rcv_transactions rt3
317 WHERE rt.parent_transaction_id = rt3.transaction_id
318 AND rt3.transaction_type = 'RETURN TO RECEIVING')
319 )
320 )
321 AND EXISTS (SELECT '1' FROM rcv_shipment_lines rsl
322 WHERE rsl.shipment_line_id = rt.shipment_line_id
323 AND wms_deploy.get_client_code(rsl.item_id) = p_txns_history_rec.client_code);
324
325 ELSE
326
327 IF (g_asn_debug = 'Y') THEN
328 asn_debug.put_line('Distributed : Updating RT.receipt_confirmation_extracted to Y');
329 END IF;
330
331
332 UPDATE rcv_transactions rt
333 SET rt.receipt_confirmation_extracted = 'Y',
334 rt.xml_document_id = p_txns_history_rec.document_number
335 WHERE rt.shipment_header_id = p_txns_history_rec.document_number
336 AND nvl(rt.receipt_confirmation_extracted, 'N') = 'P'
337 and xml_document_id is null
338 AND (rt.TRANSACTION_TYPE = 'DELIVER' OR
339 (rt.TRANSACTION_TYPE IN ('CORRECT', 'RETURN TO RECEIVING')
340 AND EXISTS (SELECT '1' FROM rcv_transactions rt2
341 WHERE rt.parent_transaction_id = rt2.transaction_id
342 AND rt2.transaction_type = 'DELIVER')
343 ) OR
344 (rt.TRANSACTION_TYPE IN ('CORRECT')
345 AND EXISTS (SELECT '1' FROM rcv_transactions rt3
346 WHERE rt.parent_transaction_id = rt3.transaction_id
347 AND rt3.transaction_type = 'RETURN TO RECEIVING')
348 )
349 );
350
351
352 END IF;
353
354 END IF;
355
356
357 END IF; -- if txn_cur%notfound
358
359
363 IF (g_asn_debug = 'Y') THEN
360 IF(txn_cur%ISOPEN) THEN
361 CLOSE txn_cur;
362
364 asn_debug.put_line('Closing txn_cur');
365 END IF;
366
367 END IF;
368 ELSE
369
370 -- Not Null checks failed. Return Error
371 x_return_status := rcv_error_pkg.g_ret_sts_error;
372
373 END IF; -- if p_txns_history_rec columns are not null
374
375 IF (g_asn_debug = 'Y') THEN
376 asn_debug.put_line('Exiting Create_Update_Txns_History call');
377 END IF;
378
379 EXCEPTION
380 WHEN invalid_status THEN
381 x_return_status := rcv_error_pkg.g_ret_sts_error;
382 IF (g_asn_debug = 'Y') THEN
383 asn_debug.put_line('invalid_status exception has occured.');
384 END IF;
385 WHEN invalid_action THEN
386 x_return_status := rcv_error_pkg.g_ret_sts_error;
387 IF (g_asn_debug = 'Y') THEN
388 asn_debug.put_line('invalid_action exception has occured.');
389 END IF;
390 WHEN invalid_entity_type THEN
391 x_return_status := rcv_error_pkg.g_ret_sts_error;
392 IF (g_asn_debug = 'Y') THEN
393 asn_debug.put_line('invalid_entity_type exception has occured.');
394 END IF;
395 WHEN invalid_direction THEN
396 x_return_status := rcv_error_pkg.g_ret_sts_error;
397 IF (g_asn_debug = 'Y') THEN
398 asn_debug.put_line('invalid_direction exception has occured.');
399 END IF;
400 WHEN invalid_document_type THEN
401 x_return_status := rcv_error_pkg.g_ret_sts_error;
402 IF (g_asn_debug = 'Y') THEN
403 asn_debug.put_line('invalid_document_type exception has occured.');
404 END IF;
405 WHEN Others THEN
406 x_return_status := rcv_error_pkg.g_ret_sts_error;
407 IF (g_asn_debug = 'Y') THEN
408 asn_debug.put_line('Unexpected error has occured. Oracle error message is '|| SQLERRM);
409 END IF;
410
411 END Create_Update_Txns_History;
412
413
414 PROCEDURE Get_Txns_History(
415 p_item_type IN VARCHAR2,
416 p_event_key IN VARCHAR2,
417 p_direction IN VARCHAR2,
418 p_document_type IN VARCHAR2,
419 p_txns_history_rec OUT NOCOPY Txns_History_Record_Type,
420 x_return_status OUT NOCOPY VARCHAR2
421 ) IS
422
423 CURSOR txns_history_cur IS
424 SELECT wth.transaction_id,
425 wth.document_type,
426 wth.document_direction,
427 wth.document_number,
428 wth.entity_number,
429 wth.entity_type,
430 wth.trading_partner_id,
431 wth.action_type,
432 wth.transaction_status,
433 wth.event_name,
434 wth.event_key ,
435 wth.item_type,
436 wth.document_revision,
437 wth.attribute_category,
438 wth.attribute1,
439 wth.attribute2,
440 wth.attribute3,
441 wth.attribute4,
442 wth.attribute5,
443 wth.attribute6,
444 wth.attribute7,
445 wth.attribute8,
446 wth.attribute9,
447 wth.attribute10,
448 wth.attribute11,
449 wth.attribute12,
450 wth.attribute13,
451 wth.attribute14,
452 wth.attribute15,
453 wth.client_code
454 FROM MTL_TXNS_HISTORY wth
455 WHERE wth.item_type = p_item_type
456 and wth.event_key = p_event_key
457 and wth.document_direction = p_direction
458 and wth.document_type = p_document_type
459 and rownum = 1;
460
461 --exceptions
462 no_record_found exception;
463
464 BEGIN
465
466 IF (g_asn_debug = 'Y') THEN
467 asn_debug.put_line('Entering Get_Txns_History');
468 END IF;
469
470 x_return_status := rcv_error_pkg.g_ret_sts_success;
471
472 OPEN txns_history_cur;
473 FETCH txns_history_cur INTO p_txns_history_rec;
474
475 IF(txns_history_cur%NOTFOUND) THEN
476 raise no_record_found;
477 END IF;
478
479 CLOSE txns_history_cur;
480
481 IF (g_asn_debug = 'Y') THEN
482 asn_debug.put_line('Exiting Get_Txns_History');
483 END IF;
484
485 EXCEPTION
486 WHEN no_record_found THEN
487
488 IF(txns_history_cur%ISOPEN) THEN
489 CLOSE txns_history_cur;
490 END IF;
491
492 x_return_status := rcv_error_pkg.g_ret_sts_error;
493 IF (g_asn_debug = 'Y') THEN
494 asn_debug.put_line('no_record_found exception has occured.');
495 END IF;
496
497 WHEN Others THEN
498 x_return_status := rcv_error_pkg.g_ret_sts_unexp_error;
499 IF (g_asn_debug = 'Y') THEN
500 asn_debug.put_line('Unexpected error has occured. Oracle error message is '|| SQLERRM);
501 END IF;
502 END Get_Txns_History;
503
504 END RCV_TRANSACTIONS_HISTORY_PKG;