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