[Home] [Help]
PACKAGE BODY: APPS.CSI_RESUBMIT_PUB
Source
1 PACKAGE BODY csi_resubmit_pub AS
2 /* $Header: csiprshb.pls 120.1 2006/01/05 22:20:54 srsarava noship $ */
3
4 PROCEDURE debug(
5 p_message IN VARCHAR2)
6 IS
7 BEGIN
8
9 csi_t_gen_utility_pvt.build_file_name(
10 p_file_segment1 => 'csi',
11 p_file_segment2 => to_char(sysdate,'DDMONYYYY'));
12
13 csi_t_gen_utility_pvt.add(p_message);
14
15 IF fnd_profile.value('CONC_REQUEST_ID') is not null THEN
16 fnd_file.put_line(fnd_file.log, p_message);
17 END IF;
18
19 END debug;
20
21 PROCEDURE process_failed_in_q
22 IS
23 CURSOR stuck_cur IS
24 SELECT msg_id,
25 msg_code,
26 msg_status,
27 body_text,
28 description
29 FROM xnp_msgs
30 WHERE msg_code like 'CSI%'
31 AND msg_status in ('FAILED', 'REJECTED');
32
33 -- variables for decode
34 l_amount integer;
35 l_msg_text varchar2(32767);
36 l_element_name varchar2(80);
37 l_element_value varchar2(200);
38
39 -- other variables
40 l_fixed_flag varchar2(1) := 'N';
41 l_failure_message varchar2(2000);
42
43 BEGIN
44
45 FOR stuck_rec in stuck_cur
46 LOOP
47
48 l_failure_message := null;
49
50 BEGIN
51
52 l_amount := null;
53 l_amount := dbms_lob.getlength(stuck_rec.body_text);
54 l_msg_text := null;
55
56 dbms_lob.read(
57 lob_loc => stuck_rec.body_text,
58 amount => l_amount,
59 offset => 1,
60 buffer => l_msg_text );
61
62 l_element_value := null;
63
64 IF stuck_rec.msg_code in ('CSISOFUL', 'CSIRMAFL') THEN
65 l_element_name := 'ORDER_LINE_ID';
66 xnp_xml_utils.decode(l_msg_text, 'ORDER_LINE_ID', l_element_value);
67 ELSE
68 l_element_name := 'MTL_TRANSACTION_ID';
69 xnp_xml_utils.decode(l_msg_text, 'MTL_TRANSACTION_ID', l_element_value);
70 END IF;
71
72 BEGIN
73 l_fixed_flag := 'N';
74 xnp_message.fix(
75 p_msg_id => stuck_rec.msg_id);
76 l_fixed_flag := 'Y';
77 EXCEPTION
78 WHEN others THEN
79 l_failure_message := 'Failed in xnp_message.fix : '||sqlerrm;
80 END;
81
82 EXCEPTION
83 WHEN others THEN
84 l_failure_message := 'Failed to decode : '||sqlerrm;
85 END;
86
87 debug('sfm message queue record # '||stuck_cur%rowcount);
88 debug(' msg_code '||stuck_rec.msg_code);
89 debug(' msg_id '||stuck_rec.msg_id);
90 debug(' msg_status '||stuck_rec.msg_status);
91 debug(' description '||stuck_rec.description);
92 debug(' msg_element_name '||l_element_name);
93 debug(' msg_element_value '||l_element_value);
94 debug(' re_queued_flag '||l_fixed_flag);
95 debug(' failure_message '||l_failure_message);
96
97 END LOOP;
98
99 EXCEPTION
100 WHEN others THEN
101 null;
102 END process_failed_in_q;
103
104 PROCEDURE resubmit_interface(
105 errbuf OUT nocopy varchar2,
106 retcode OUT nocopy number,
107 p_option IN varchar2)
108 IS
109 BEGIN
110
111 debug(' ');
112 debug('START resubmit_errors-'||p_option||'-'||to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'));
113
114 IF p_option = 'SELECTED' THEN
115 -- process only the records in csi_txn_errors with processed_flag = 'R'
116 resubmit_error_txns(errbuf,retcode,p_option);
117 ELSIF p_option = 'ALL' THEN
118 -- process all records in csi_txn_errors processed_flag in ('E', 'R')
119 resubmit_error_txns(errbuf,retcode,p_option);
120 ELSIF p_option = 'WAITING' THEN
121 -- process only the records in csi_txn_errors with processed_flag = 'W'
122 resubmit_error_txns(errbuf,retcode,'W');
123 ELSIF p_option = 'FAILED_IN_Q' THEN
124 -- dequeueus the CSI messages in SFM queue with the status in (FAILED, REJECTED)
125 process_failed_in_q;
126 END IF;
127
128 debug('END resubmit_errors-'||to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'));
129
130 END resubmit_interface;
131
132 PROCEDURE resubmit_waiting_txns(
133 errbuf OUT nocopy varchar2,
134 retcode OUT nocopy number)
135 IS
136 BEGIN
137 resubmit_error_txns(errbuf,retcode,'W');
138 END Resubmit_Waiting_Txns;
139
140 PROCEDURE resubmit(
141 p_error_rec IN csi_txn_errors%rowtype,
142 x_return_status OUT nocopy varchar2,
143 x_error_message OUT nocopy varchar2)
144 IS
145 l_trx_error_rec csi_datastructures_pub.transaction_error_rec;
146 l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
147 l_error_message varchar2(2000);
148
149 CURSOR c_txn_info (pc_txn_type_id IN NUMBER) is
150 SELECT seeded_flag,source_application_id
151 FROM csi_txn_types
152 WHERE transaction_type_id = pc_txn_type_id;
153
154 r_txn_info c_txn_info%rowtype;
155
156 BEGIN
157
158 x_return_status := fnd_api.g_ret_sts_success;
159
160 fnd_msg_pub.initialize;
161
162 UPDATE csi_txn_errors
163 SET processed_flag = 'Y'
164 WHERE transaction_error_id = p_error_rec.transaction_error_id;
165
166 OPEN c_txn_info(p_error_rec.transaction_type_id);
167 FETCH c_txn_info into r_txn_info;
168 CLOSE c_txn_info;
169
170 -- First check to see if this is a CSE Error
171
172 IF p_error_rec.transaction_type_id in (106,107,108,109,110,111,103,104,105) THEN
173
174 cse_redo_pkg.redo_logic(
175 p_txn_type_id => p_error_rec.transaction_type_id,
176 p_stage => p_error_rec.error_stage,
177 p_body_text => p_error_rec.message_string,
178 x_return_status => l_return_status,
179 x_error_message => l_error_message);
180
181 IF l_return_status <> fnd_api.g_ret_sts_success THEN
182 RAISE fnd_api.g_exc_error;
183 END IF;
184
185 -- Second check if its seeded then it must be a CSI error so process thru
186 -- CSI reprocess logic.
187
188 ELSIF r_txn_info.seeded_flag = 'Y' AND
189 r_txn_info.source_application_id NOT IN (873,140) AND
190 p_error_rec.transaction_type_id <> 105 THEN
191
192 csi_inv_txnstub_pkg.execute_trx_dpl(
193 p_transaction_type => p_error_rec.source_type,
194 p_transaction_id => p_error_rec.source_id,
195 x_trx_return_status => l_return_status,
196 x_trx_error_rec => l_trx_error_rec);
197
198 IF l_return_status <> fnd_api.g_ret_sts_success THEN
199 l_error_message := l_trx_error_rec.error_text;
200 RAISE fnd_api.g_exc_error;
201 END IF;
202
203 ELSE
204
205 /* Neither a core EIB trackable transaction Nor a CSI Core Transaction.
206 Call client extension to check against additional transaction types. */
207
208 csi_client_ext_pub.csi_error_resubmit(
209 p_transaction_id => p_error_rec.transaction_type_id,
210 x_return_status => l_return_status,
211 x_error_message => l_error_message);
212
213 -- If no custom code exists this will return an error so that the error
214 -- is retained for the custom defined transaction. After code is entered
215 -- to reprocess then the transaction should get processed.
216
217 IF l_return_status <> fnd_api.g_ret_sts_success THEN
218 l_error_message := l_trx_error_rec.error_text;
219 RAISE fnd_api.g_exc_error;
220 END IF;
221
222 END IF;
223
224 EXCEPTION
225 WHEN fnd_api.g_exc_error THEN
226 x_return_status := fnd_api.g_ret_sts_error;
227 x_error_message := l_error_message;
228 WHEN others THEN
229 x_return_status := fnd_api.g_ret_sts_error;
230 x_error_message := 'Error in resubmit: '||substr(sqlerrm, 1, 540);
231 END resubmit;
232
233
234 PROCEDURE resubmit_error_txns(
235 errbuf OUT nocopy varchar2,
236 retcode OUT nocopy number,
237 process_flag IN varchar2)
238 IS
239
240 l_error_message varchar2(2000);
241 l_return_status varchar2(1):= fnd_api.g_ret_sts_success;
242
243 process_success exception;
244 process_failure exception;
245
246 --Fix for bug 4907969
247 l_val_1 varchar2(1);
248 l_val_2 varchar2(1);
249
250 CURSOR resubmit_csr IS
251 SELECT cte.*
252 FROM csi_txn_errors cte,
253 mtl_material_transactions mmt
254 WHERE cte.processed_flag IN (l_val_1,l_val_2)
255 AND cte.inv_material_transaction_id = mmt.transaction_id(+)
256 ORDER BY mmt.creation_date asc , mmt.transaction_id asc;
257
258
259 BEGIN
260 --Fix for bug 4907969
261 IF process_flag = 'ALL' THEN
262 l_val_1 := 'E';
263 l_val_2 := 'R';
264 ELSIF process_flag = 'SELECTED' THEN
265 l_val_1 := 'R';
266 l_val_2 := 'R';
267 ELSIF process_flag = 'W' THEN
268 l_val_1 := 'W';
269 l_val_2 := 'W';
270 END IF;
271
272 FOR error_rec IN resubmit_csr
273 LOOP
274
275 BEGIN
276
277 debug(' START '||error_rec.source_type||'-'||to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'));
278 debug(' txn_type_id : '||error_rec.transaction_type_id);
279 debug(' source_type : '||error_rec.source_type);
280 debug(' source_id : '||error_rec.source_id);
281
282 resubmit(
283 p_error_rec => error_rec,
284 x_return_status => l_return_status,
285 x_error_message => l_error_message);
286
287 IF l_return_status <> fnd_api.g_ret_sts_success THEN
288 RAISE process_failure;
289 ELSE
290 RAISE process_success;
291 END IF;
292
293 EXCEPTION
294 WHEN process_success THEN
295
296 debug(' status : SUCCESS');
297 debug(' END '||error_rec.source_type||'-'||to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'));
298
299 /* Added by epajaril for 11.5.10 enhancement */
300 DELETE from csi_txn_errors
301 WHERE transaction_error_id = error_rec.transaction_error_id;
302
303 WHEN process_failure THEN
304
305 IF nvl(l_error_message, fnd_api.g_miss_char) = fnd_api.g_miss_char THEN
306 l_error_message := error_rec.error_text;
307 END IF;
308
309 debug(' status : FAILED AGAIN');
310 debug(' error : '||l_error_message);
311 debug(' END '||error_rec.source_type||'-'||to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'));
312
313 UPDATE csi_txn_errors
314 SET processed_flag = 'E',
315 error_text = l_error_message ,
316 last_update_date = sysdate,
317 last_update_login = fnd_global.login_id,
318 last_updated_by = fnd_global.user_id
319 WHERE transaction_error_id = error_rec.transaction_error_id;
320
321 END;
322 commit;
323 END LOOP;
324 EXCEPTION
325 WHEN OTHERS THEN
326 null;
327 --will put code here
328 END resubmit_error_txns;
329
330 END csi_resubmit_pub;