[Home] [Help]
PACKAGE BODY: APPS.INV_TRANSACTIONS_UTIL2
Source
1 PACKAGE BODY inv_transactions_util2 AS
2 /* $Header: INVUTL2B.pls 120.0.12010000.2 2010/02/03 20:40:36 musinha noship $ */
3
4 g_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
5
6 G_PKG_NAME CONSTANT VARCHAR2(50) := 'INV_TRANSACTIONS_UTIL2';
7
8 PROCEDURE Update_Txn_Hist_Err_WF( Item_type IN VARCHAR2,
9 Item_key IN VARCHAR2,
10 Actid IN NUMBER,
11 Funcmode IN VARCHAR2,
12 Resultout OUT NOCOPY VARCHAR2
13 )
14
15 IS
16 l_return_status VARCHAR2(1);
17
18 update_history EXCEPTION;
19
20
21 BEGIN
22
23 if (g_debug = 1) then
24 inv_trx_util_pub.TRACE('Entering Update_Txn_Hist_Err_WF', 'INV_TRANSACTIONS_UTIL2', 9);
25 inv_trx_util_pub.TRACE('item_type is '||item_type, 'INV_TRANSACTIONS_UTIL2', 9);
26 inv_trx_util_pub.TRACE('item_key is '||item_key, 'INV_TRANSACTIONS_UTIL2', 9);
27 inv_trx_util_pub.TRACE('actid is '||actid, 'INV_TRANSACTIONS_UTIL2', 9);
28 inv_trx_util_pub.TRACE('funcmode is '||funcmode, 'INV_TRANSACTIONS_UTIL2', 9);
29 end if;
30
31 IF ( funcmode = 'RUN' ) THEN
32 Update_Txn_History ( Item_type,
33 Item_key,
34 'ER',
35 l_return_status
36 );
37
38 if (g_debug = 1) then
39 inv_trx_util_pub.TRACE('l_return_status is '||l_return_status, 'INV_TRANSACTIONS_UTIL2', 9);
40 end if;
41
42 IF ( l_return_status <> rcv_error_pkg.g_ret_sts_success ) THEN
43 raise update_history;
44 ELSE
45 resultout := 'COMPLETE:SUCCESS';
46 if (g_debug = 1) then
47 inv_trx_util_pub.TRACE('resultout is '||resultout, 'INV_TRANSACTIONS_UTIL2', 9);
48 end if;
49 RETURN;
50 END IF;
51
52 END IF;
53
54 EXCEPTION
55 WHEN update_history THEN
56 resultout := 'COMPLETE:FAILURE';
57 if (g_debug = 1) then
58 inv_trx_util_pub.TRACE('update_history exception has occured', 'INV_TRANSACTIONS_UTIL2', 9);
59 end if;
60 raise;
61 WHEN OTHERS THEN
62 resultout := 'COMPLETE:FAILURE';
63 if (g_debug = 1) then
64 inv_trx_util_pub.TRACE('Unexpected error has occured. Oracle error message is '|| SQLERRM, 'INV_TRANSACTIONS_UTIL2', 9);
65 end if;
66 raise;
67 END Update_Txn_Hist_Err_WF;
68
69 PROCEDURE Update_Txn_Hist_Success_WF( Item_type IN VARCHAR2,
70 Item_key IN VARCHAR2,
71 Actid IN NUMBER,
72 Funcmode IN VARCHAR2,
73 Resultout OUT NOCOPY VARCHAR2
74 )
75
76 IS
77 l_return_status VARCHAR2(1);
78
79 update_history EXCEPTION;
80
81
82 BEGIN
83
84 if (g_debug = 1) then
85 inv_trx_util_pub.TRACE('Entering Update_Txn_Hist_Success_WF', 'INV_TRANSACTIONS_UTIL2', 9);
86 inv_trx_util_pub.TRACE('item_type is '||item_type, 'INV_TRANSACTIONS_UTIL2', 9);
87 inv_trx_util_pub.TRACE('item_key is '||item_key, 'INV_TRANSACTIONS_UTIL2', 9);
88 inv_trx_util_pub.TRACE('actid is '||actid, 'INV_TRANSACTIONS_UTIL2', 9);
89 inv_trx_util_pub.TRACE('funcmode is '||funcmode, 'INV_TRANSACTIONS_UTIL2', 9);
90 end if;
91
92 IF ( funcmode = 'RUN' ) THEN
93
94
95 Update_Txn_History ( Item_type,
96 Item_key,
97 'ST',
98 l_return_status
99 );
100
101 if (g_debug = 1) then
102 inv_trx_util_pub.TRACE('Update_Txn_Hist_Success_WF.l_return_status is '||l_return_status, 'INV_TRANSACTIONS_UTIL2', 9);
103 end if;
104
105 IF ( l_return_status <> rcv_error_pkg.g_ret_sts_success ) THEN
106 raise update_history;
107 ELSE
108 resultout := 'COMPLETE:SUCCESS';
109 if (g_debug = 1) then
110 inv_trx_util_pub.TRACE('Update_Txn_Hist_Success_WF.resultout is '||resultout, 'INV_TRANSACTIONS_UTIL2', 9);
111 end if;
112 RETURN;
113 END IF;
114 END IF;
115
116 if (g_debug = 1) then
117 inv_trx_util_pub.TRACE('Exiting Update_Txn_Hist_Success_WF', 'INV_TRANSACTIONS_UTIL2', 9);
118 end if;
119
120 resultout := 'COMPLETE:SUCCESS';
121
122 EXCEPTION
123 WHEN update_history THEN
124
125 update mtl_txns_history
126 set transaction_status = 'ER'
127 where event_name = Item_type
128 and event_key = Item_key
129 and transaction_status = 'IP';
130
131
132 resultout := 'COMPLETE:FAILURE';
133 if (g_debug = 1) then
134 inv_trx_util_pub.TRACE('update_history exception has occured.', 'INV_TRANSACTIONS_UTIL2', 9);
135 end if;
136 raise;
137 WHEN OTHERS THEN
138
139 update mtl_txns_history
140 set transaction_status = 'ER'
141 where event_name = Item_type
142 and event_key = Item_key
143 and transaction_status = 'IP';
144
145
146 resultout := 'COMPLETE:FAILURE';
147 if (g_debug = 1) then
148 inv_trx_util_pub.TRACE('Unexpected error has occured. Oracle error message is '|| SQLERRM, 'INV_TRANSACTIONS_UTIL2', 9);
149 end if;
150 raise;
151 END Update_Txn_Hist_Success_WF;
152
153
154 PROCEDURE Update_Txn_History ( p_item_type IN VARCHAR2,
155 p_item_key IN VARCHAR2,
156 p_transaction_status IN VARCHAR2,
157 x_return_status OUT NOCOPY VARCHAR2
158 )
159 IS
160
161
162 pragma AUTONOMOUS_TRANSACTION;
163
164 l_txns_history_rec INV_TRANSACTIONS_HISTORY_PKG.Txns_History_Record_Type;
165 l_document_type VARCHAR2(10);
166 l_txn_direction VARCHAR2(10);
167 l_txn_id NUMBER;
168 l_document_number NUMBER := NULL;
169 l_xml_document_id NUMBER := NULL;
170 l_dummy NUMBER := 0;
171
172 update_history EXCEPTION;
173
174
175 BEGIN
176
177 if (g_debug = 1) then
178 inv_trx_util_pub.TRACE('Entering Update_Txn_History', 'INV_TRANSACTIONS_UTIL2', 9);
179 inv_trx_util_pub.TRACE('p_transaction_status is '||p_transaction_status, 'INV_TRANSACTIONS_UTIL2', 9);
180 end if;
181
182 IF ( p_item_type = 'INVADJTO' ) THEN
183 l_document_type := 'ADJ';
184 l_txn_direction := 'O';
185 ELSE
186 l_document_type := 'SA';
187 l_txn_direction := 'O';
188 END IF;
189
190 if (g_debug = 1) then
191 inv_trx_util_pub.TRACE('entity number: '||l_txns_history_rec.entity_number, 'INV_TRANSACTIONS_HISTORY_PKG', 9);
192 end if;
193
194 inv_transactions_history_pkg.Get_Txns_History( p_item_type,
195 p_item_key,
196 l_txn_direction,
197 l_document_type,
198 l_txns_history_rec,
199 x_return_status );
200
201
202
203 if (g_debug = 1) then
204 inv_trx_util_pub.TRACE('Update_Txn_History.x_return_status is '||x_return_status, 'INV_TRANSACTIONS_UTIL2', 9);
205 end if;
206
207 l_txns_history_rec.transaction_status := p_transaction_status;
208
209 IF ( x_return_status <> rcv_error_pkg.g_ret_sts_success ) THEN
210 raise update_history;
211 END IF;
212
213 INV_TRANSACTIONS_HISTORY_PKG.Create_Update_Txns_History ( l_txns_history_rec,
214 l_xml_document_id, -- being passed as null at this point
215 l_txn_id,
216 x_return_status );
217
218
219 if (g_debug = 1) then
220 inv_trx_util_pub.TRACE('Update_Txn_History.x_return_status is '||x_return_status, 'INV_TRANSACTIONS_UTIL2', 9);
221 end if;
222
223 IF ( x_return_status <> rcv_error_pkg.g_ret_sts_success ) THEN
224 raise update_history;
225 ELSE
226 COMMIT;
227 END IF;
228
229 if (g_debug = 1) then
230 inv_trx_util_pub.TRACE('Exiting Update_Txn_History', 'INV_TRANSACTIONS_UTIL2', 9);
231 end if;
232
233 EXCEPTION
234 WHEN update_history THEN
235
236 update mtl_txns_history
237 set transaction_status = 'ER'
238 where event_name = p_item_type
239 and event_key = p_item_key
240 and transaction_status = 'IP';
241
242 x_return_status := rcv_error_pkg.g_ret_sts_error;
243 if (g_debug = 1) then
244 inv_trx_util_pub.TRACE('update_history exception has occured.', 'INV_TRANSACTIONS_UTIL2', 9);
245 end if;
246 ROLLBACK;
247 WHEN OTHERS THEN
248
249 update mtl_txns_history
250 set transaction_status = 'ER'
251 where event_name = p_item_type
252 and event_key = p_item_key
253 and transaction_status = 'IP';
254
255 x_return_status := rcv_error_pkg.g_ret_sts_error;
256 if (g_debug = 1) then
257 inv_trx_util_pub.TRACE('Unexpected error has occured. Oracle error message is '|| SQLERRM, 'INV_TRANSACTIONS_UTIL2', 9);
258 end if;
259 ROLLBACK;
260 END Update_Txn_History;
261
262 --k proj
263
264 PROCEDURE send_inventory_adjustment ( P_Entity_ID IN NUMBER,
265 P_Entity_Type IN VARCHAR2,
266 P_Action_Type IN VARCHAR2,
267 P_Document_Type IN VARCHAR2,
268 P_Org_ID IN NUMBER,
269 P_client_code IN VARCHAR2,
270 p_xml_document_id IN NUMBER,
271 X_Return_Status OUT NOCOPY VARCHAR2 )
272 IS
273
274 l_orig_Event_Key VARCHAR2 (240);
275 l_curr_txn_hist_record INV_TRANSACTIONS_HISTORY_PKG.Txns_History_Record_Type;
276 l_Return_Status VARCHAR2 (1);
277 l_wms_deployment_mode VARCHAR2(1);
278 l_party_id NUMBER;
279 l_xml_document_id NUMBER;
280
281 invalid_entity_type EXCEPTION;
282 invalid_action_type EXCEPTION;
283 invalid_doc_type EXCEPTION;
284 raise_event_error EXCEPTION;
285
286 BEGIN
287
288 if (g_debug = 1) then
289 inv_trx_util_pub.TRACE('Entering send_inventory_adjustment', 'INV_TRANSACTIONS_UTIL2', 9);
290 inv_trx_util_pub.TRACE('Entity_ID is ' || P_Entity_ID, 'INV_TRANSACTIONS_UTIL2', 9);
291 inv_trx_util_pub.TRACE('Entity Type is ' || P_Entity_Type, 'INV_TRANSACTIONS_UTIL2', 9);
292 inv_trx_util_pub.TRACE('Action Type is ' || P_Action_Type, 'INV_TRANSACTIONS_UTIL2', 9);
293 inv_trx_util_pub.TRACE('Dcument Type is ' || p_document_type, 'INV_TRANSACTIONS_UTIL2', 9);
294 inv_trx_util_pub.TRACE('Org ID is ' || to_char(P_Org_ID), 'INV_TRANSACTIONS_UTIL2', 9);
295 inv_trx_util_pub.TRACE('Client Code is '|| P_client_code, 'INV_TRANSACTIONS_UTIL2', 9);
296 inv_trx_util_pub.TRACE('XML Document ID is '|| to_char(p_xml_document_id), 'INV_TRANSACTIONS_UTIL2', 9);
297 end if;
298
299 X_Return_Status := rcv_error_pkg.g_ret_sts_success;
300
301 IF ( P_Entity_TYPE <> 'INVADJ' ) THEN
302 RAISE invalid_entity_type;
303 ELSIF ( P_Action_TYPE <> 'A' ) THEN
304 RAISE invalid_action_type;
305 ELSIF ( P_Document_TYPE <> 'ADJ' ) THEN
306 RAISE invalid_doc_type;
307 END IF;
308
309 l_xml_document_id := P_xml_document_id;
310
311 SELECT po_wf_itemkey_s.NEXTVAL
312 INTO l_orig_Event_Key
313 FROM DUAL;
314
315 l_curr_txn_hist_record.Document_Type := P_Document_Type;
316 l_curr_txn_hist_record.Document_Direction := 'O';
317 l_curr_txn_hist_record.Entity_Number := P_Entity_ID;
318 l_curr_txn_hist_record.Entity_Type := P_Entity_TYPE;
319
320 l_curr_txn_hist_record.Event_Name := 'oracle.apps.inv.standalone.adjo';
321 l_curr_txn_hist_record.Item_Type := 'INVADJTO';
322 l_curr_txn_hist_record.Event_Key := l_orig_Event_Key;
323 l_curr_txn_hist_record.Action_Type := P_Action_Type;
324 l_curr_txn_hist_record.Transaction_Status := 'IP';
325 --l_curr_txn_hist_record.ecx_message_id := p_xml_document_id;
326 l_curr_txn_hist_record.Document_Number := P_Entity_ID;
327
328 l_wms_deployment_mode := wms_deploy.wms_deployment_mode;
329
330 If (l_wms_deployment_mode = 'L') then
331
332 l_curr_txn_hist_record.Client_Code := P_client_code;
333
334 SELECT party_id
335 INTO l_party_id
336 FROM hz_cust_accounts
337 WHERE cust_account_id IN (SELECT client_id
338 FROM mtl_client_parameters
339 WHERE client_code = P_client_code);
340 else
341
342 select location_id
343 into l_party_id
344 from hr_organization_units_v
345 where organization_id = P_Org_ID
346 and rownum = 1;
347
348 end If;
349
350 l_curr_txn_hist_record.Trading_Partner_ID := l_party_id;
351
352 if (g_debug = 1) then
353 inv_trx_util_pub.TRACE('Item Type is ' || l_curr_txn_hist_record.Item_Type, 'INV_TRANSACTIONS_UTIL2', 9);
354 inv_trx_util_pub.TRACE('Event Name is ' || l_curr_txn_hist_record.Event_Name, 'INV_TRANSACTIONS_UTIL2', 9);
355 inv_trx_util_pub.TRACE('Event Key is ' || l_curr_txn_hist_record.Event_Key, 'INV_TRANSACTIONS_UTIL2', 9);
356 inv_trx_util_pub.TRACE('Trading Partner ID is ' || To_Char(l_curr_txn_hist_record.Trading_Partner_ID), 'INV_TRANSACTIONS_UTIL2', 9);
357 inv_trx_util_pub.TRACE('Document Type is ' || l_curr_txn_hist_record.Document_Type, 'INV_TRANSACTIONS_UTIL2', 9);
358 inv_trx_util_pub.TRACE('Document Direction is ' || l_curr_txn_hist_record.Document_Direction, 'INV_TRANSACTIONS_UTIL2', 9);
359 inv_trx_util_pub.TRACE('Document Number is ' || to_char(l_curr_txn_hist_record.Document_Number), 'INV_TRANSACTIONS_UTIL2', 9);
360 end if;
361
362
363 /* Raise event will insert the record into the transaction history table
364 for the current transaction.
365 */
366
367 INV_EXTERNAL_INTERFACE_SV.Raise_Event ( l_curr_txn_hist_record,
368 l_xml_document_id,
369 l_Return_Status );
370
371 if (g_debug = 1) then
372 inv_trx_util_pub.TRACE('send_inventory_adjustment.l_Return_Status is '||l_Return_Status, 'INV_TRANSACTIONS_UTIL2', 9);
373 inv_trx_util_pub.TRACE('Exiting send_inventory_adjustment', 'INV_TRANSACTIONS_UTIL2', 9);
374 end if;
375
376 IF (l_Return_Status <> rcv_error_pkg.g_ret_sts_success ) THEN
377 RAISE raise_event_error;
378 END IF;
379
380 EXCEPTION
381
382 WHEN invalid_entity_type THEN
383 X_Return_Status := rcv_error_pkg.g_ret_sts_error;
384 if (g_debug = 1) then
385 inv_trx_util_pub.TRACE('invalid_entity_type exception has occured', 'INV_TRANSACTIONS_UTIL2', 9);
386 end if;
387
388 WHEN invalid_action_type THEN
389 X_Return_Status := rcv_error_pkg.g_ret_sts_error;
390 if (g_debug = 1) then
391 inv_trx_util_pub.TRACE('invalid_action_type exception has occured', 'INV_TRANSACTIONS_UTIL2', 9);
392 end if;
393
394 WHEN invalid_doc_type THEN
395 X_Return_Status := rcv_error_pkg.g_ret_sts_error;
396 if (g_debug = 1) then
397 inv_trx_util_pub.TRACE('invalid_doc_type exception has occured', 'INV_TRANSACTIONS_UTIL2', 9);
398 end if;
399
400 WHEN raise_event_error THEN
401 X_Return_Status := rcv_error_pkg.g_ret_sts_error;
402 if (g_debug = 1) then
403 inv_trx_util_pub.TRACE('raise_event_error exception has occured, error message is '|| SQLERRM, 'INV_TRANSACTIONS_UTIL2', 9);
404 end if;
405
406 WHEN OTHERS THEN
407 X_Return_Status := rcv_error_pkg.g_ret_sts_error;
408 if (g_debug = 1) then
409 inv_trx_util_pub.TRACE('Unexpected error has occured. Oracle error message is '|| SQLERRM, 'INV_TRANSACTIONS_UTIL2', 9);
410 end if;
411
412 END send_inventory_adjustment;
413
414
415 PROCEDURE Send_Document( p_entity_id IN NUMBER,
416 p_entity_type IN VARCHAR2,
417 p_action_type IN VARCHAR2,
418 p_document_type IN VARCHAR2,
419 p_organization_id IN NUMBER,
420 p_client_code IN VARCHAR2,
421 p_xml_document_id IN NUMBER,
422 x_return_status OUT NOCOPY VARCHAR2)
423
424 IS
425
426 invalid_doc_type EXCEPTION;
427
428 BEGIN
429
430 if (g_debug = 1) then
431 inv_trx_util_pub.TRACE('Entering Send_Document', 'INV_TRANSACTIONS_UTIL2', 9);
432 end if;
433
434 IF ( p_document_type = 'ADJ' ) THEN
435
436 send_inventory_adjustment(p_entity_id,
437 p_entity_type,
438 p_action_type,
439 p_document_type,
440 p_organization_id,
441 p_client_code,
442 p_xml_document_id,
443 x_return_status);
444
445 if (g_debug = 1) then
446 inv_trx_util_pub.TRACE('Exiting Send_Document', 'INV_TRANSACTIONS_UTIL2', 9);
447 inv_trx_util_pub.TRACE('Send_Document.x_return_status is '|| x_return_status, 'INV_TRANSACTIONS_UTIL2', 9);
448 end if;
449
450 ELSE
451 raise invalid_doc_type;
452 END IF;
453
454 EXCEPTION
455
456 WHEN invalid_doc_type THEN
457 x_return_status := rcv_error_pkg.g_ret_sts_error;
458
459 if (g_debug = 1) then
460 inv_trx_util_pub.TRACE('invalid_doc_type exception has occured.', 'INV_TRANSACTIONS_UTIL2', 9);
461 end if;
462
463 WHEN OTHERS THEN
464 x_return_status := rcv_error_pkg.g_ret_sts_error;
465
466 if (g_debug = 1) then
467 inv_trx_util_pub.TRACE('Unexpected error has occured. Oracle error message is '|| SQLERRM, 'INV_TRANSACTIONS_UTIL2', 9);
468 end if;
469
470 END Send_Document;
471
472
473 END INV_TRANSACTIONS_UTIL2;