[Home] [Help]
PACKAGE BODY: APPS.WIP_MTLINTERFACEPROC_PUB
Source
1 package body wip_mtlInterfaceProc_pub as
2 /* $Header: wipintpb.pls 120.0.12000000.2 2007/05/17 13:37:06 mraman ship $ */
3
4 type err_tbl_t is table of varchar2(240);
5 type num_tbl_t is table of number;
6
7 /*forward declarations */
8 procedure processInterfaceRows(p_initMsgList IN VARCHAR2,
9 p_processInv IN VARCHAR2,
10 p_endDebug IN VARCHAR2,
11 p_txnHdrID IN NUMBER,
12 x_returnStatus OUT NOCOPY VARCHAR2);
13
14 /* public procedures */
15 procedure processInterface(p_txnHdrID IN NUMBER,
16 p_commit IN VARCHAR2 := fnd_api.g_false,
17 x_returnStatus OUT NOCOPY VARCHAR2) is
18 l_returnStatus VARCHAR2(1);
19 l_params wip_logger.param_tbl_t;
20 l_errCodeTbl err_tbl_t;
21 l_errExpTbl err_tbl_t;
22 l_intTbl num_tbl_t;
23 l_errMessage VARCHAR2(2000);
24 l_errCount NUMBER;
25 l_logLevel NUMBER := to_number(fnd_log.g_current_runtime_level);
26 begin
27 savepoint wipintpb0;
28
29 if (l_logLevel <= wip_constants.trace_logging) then
30 l_params(1).paramName := 'p_txnHdrID';
31 l_params(1).paramValue := p_txnHdrID;
32 wip_logger.entryPoint(p_procName => 'wip_mtlInterfaceProc_pub.processInterface',
33 p_params => l_params,
34 x_returnStatus => x_returnStatus);
35 end if;
36
37 fnd_msg_pub.initialize;
38
39 processInterfaceRows(p_initMsgList => fnd_api.g_true,
40 p_processInv => fnd_api.g_true,
41 p_endDebug => fnd_api.g_false,
42 p_txnHdrID => p_txnHdrID,
43 x_returnStatus => x_returnStatus);
44
45 --unexpected error occurred, return status gets set to unexpected error
46 if(x_returnStatus <> fnd_api.g_ret_sts_success) then
47 raise fnd_api.g_exc_unexpected_error;
48 end if;
49
50 if(fnd_api.to_boolean(p_commit)) then
51 commit;
52 end if;
53
54 if (l_logLevel <= wip_constants.trace_logging) then
55 wip_logger.exitPoint(p_procName => 'wip_mtlInterfaceProc_pub.processInterface',
56 p_procReturnStatus => x_returnStatus,
57 p_msg => 'attempted to process all rows successfully (no exceptions encountered).',
58 x_returnStatus => l_returnStatus); --discard logging return status
59 wip_logger.cleanUp(x_returnStatus => l_returnStatus); --discard logging return status
60 end if;
61 exception
62 when fnd_api.g_exc_unexpected_error then
63 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
64 wip_utilities.get_message_stack(p_msg => l_errMessage,
65 p_separator => ' ',
66 p_delete_stack => fnd_api.g_false);
67 if (l_logLevel <= wip_constants.trace_logging) then
68 wip_logger.exitPoint(p_procName => 'wip_mtlInterfaceProc_pub.processInterface',
69 p_procReturnStatus => x_returnStatus,
70 p_msg => 'proc failure:' || l_errMessage,
71 x_returnStatus => l_returnStatus); --discard logging return status
72
73 wip_logger.cleanUp(x_returnStatus => l_returnStatus); --discard logging return status
74 end if;
75
76 l_errMessage := substr(l_errMessage,1,240);
77
78 select transaction_interface_id, error_code, error_explanation
79 bulk collect into l_intTbl, l_errCodeTbl, l_errExpTbl
80 from mtl_transactions_interface
81 where transaction_header_id = p_txnHdrID
82 and process_flag = 3;
83
84 rollback to wipintpb0;
85
86 for i in 1..l_intTbl.count loop
87 update mtl_transactions_interface
88 set error_code = l_errCodeTbl(i),
89 error_explanation = l_errExpTbl(i),
90 process_flag = wip_constants.mti_error
91 where transaction_header_id = p_txnHdrID
92 and transaction_interface_id = l_intTbl(i);
93 end loop;
94 when others then --some unhandled exception occurred. rollback everything.
95 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
96 if (l_logLevel <= wip_constants.trace_logging) then
97 wip_logger.exitPoint(p_procName => 'wip_mtlInterfaceProc_pub.processInterface',
98 p_procReturnStatus => x_returnStatus,
99 p_msg => 'exception' || SQLERRM,
100 x_returnStatus => l_returnStatus); --discard logging return status
101
102 wip_logger.cleanUp(x_returnStatus => l_returnStatus); --discard logging return status
103 end if;
104 rollback to wipintpb0;
105 fnd_msg_pub.add_exc_msg(p_pkg_name => 'wip_mtlInterfaceProc_pub',
106 p_procedure_name => 'processInterface',
107 p_error_text => SQLERRM);
108 wip_utilities.get_message_stack(p_msg => l_errMessage,
109 p_separator => ' ',
110 p_delete_stack => fnd_api.g_false);
111 l_errMessage := substr(l_errMessage,1,240);
112 update mtl_transactions_interface
113 set error_code = 'wip_mtlInterfaceProc_pub.processInterface()',
114 error_explanation = l_errMessage,
115 process_flag = wip_constants.mti_error
116 where transaction_header_id = p_txnHdrID;
117 end processInterface;
118
119 procedure processInterface(p_txnIntID IN NUMBER,
120 p_commit IN VARCHAR2 := fnd_api.g_false,
121 x_returnStatus OUT NOCOPY VARCHAR2,
122 x_errorMsg OUT NOCOPY VARCHAR2) is
123 l_rowID rowid;
124 l_txnHdrID NUMBER;
125
126 cursor c_mtiRow is
127 select error_explanation
128 from mtl_transactions_interface
129 where transaction_interface_id = p_txnIntID;
130 l_params wip_logger.param_tbl_t;
131 l_msg VARCHAR2(100);
132 l_returnStatus VARCHAR2(1);
133 l_logLevel NUMBER := to_number(fnd_log.g_current_runtime_level);
134 begin
135 savepoint wipintpb10;
136
137 if (l_logLevel <= wip_constants.trace_logging) then
138 l_params(1).paramName := 'p_txnIntID';
139 l_params(1).paramValue := p_txnIntID;
140 wip_logger.entryPoint(p_procName => 'wip_mtlInterfaceProc_pub.processInterface',
141 p_params => l_params,
142 x_returnStatus => x_returnStatus);
143
144 if(x_returnStatus <> fnd_api.g_ret_sts_success) then
145 raise fnd_api.g_exc_error;
146 end if;
147 wip_logger.cleanUp(x_returnStatus=>l_returnStatus);
148 end if;
149
150 --must be a single wip transaction that has not yet errored
151 select rowid, transaction_header_id
152 into l_rowID, l_txnHdrID
153 from mtl_transactions_interface
154 where transaction_interface_id = p_txnIntID
155 and transaction_source_type_id = 5
156 and process_flag = wip_constants.mti_inventory;
157
158
159 processInterfaceRows(p_initMsgList => fnd_api.g_true,
160 p_processInv => fnd_api.g_true,
161 p_endDebug => fnd_api.g_false,
162 p_txnHdrID => l_txnHdrID,
163 x_returnStatus => x_returnStatus);
164
165
166 if(x_returnStatus <> fnd_api.g_ret_sts_success) then
167 l_msg := 'process interface rows failed';
168 raise fnd_api.g_exc_error;
169 end if;
170
171
172 if(fnd_api.to_boolean(p_commit)) then
173 commit;
174 end if;
175
176 l_msg := 'success';
177 if (l_logLevel <= wip_constants.trace_logging) then
178 wip_logger.exitPoint(p_procName => 'wip_mtlInterfaceProc_pub.processInterface',
179 p_procReturnStatus => x_returnStatus,
180 p_msg => l_msg,
181 x_returnStatus => l_returnStatus); --discard logging return status
182
183 wip_logger.cleanUp(x_returnStatus => l_returnStatus); --discard logging return status
184 end if;
185
186 exception
187 when fnd_api.g_exc_error then
188 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
189 wip_utilities.get_message_stack(p_msg => x_errorMsg,
190 p_separator => ' ',
191 p_delete_stack => fnd_api.g_false);
192 if (l_logLevel <= wip_constants.trace_logging) then
193 wip_logger.exitPoint(p_procName => 'wip_mtlInterfaceProc_pub.processInterface',
194 p_procReturnStatus => x_returnStatus,
195 p_msg => 'MTI failure: ' || l_msg,
196 x_returnStatus => l_returnStatus); --discard logging return status
197 wip_logger.cleanUp(x_returnStatus => l_returnStatus); --discard logging return status
198 end if;
199 when TOO_MANY_ROWS then
200 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
201 fnd_msg_pub.add_exc_msg(p_pkg_name => 'wip_mtlInterfaceProc_pub',
202 p_procedure_name => 'processInterface',
203 p_error_text => SQLERRM);
204 wip_utilities.get_message_stack(p_msg => x_errorMsg,
205 p_separator => ' ',
206 p_delete_stack => fnd_api.g_false);
207
208 if (l_logLevel <= wip_constants.trace_logging) then
209 wip_logger.exitPoint(p_procName => 'wip_mtlInterfaceProc_pub.processInterface',
210 p_procReturnStatus => x_returnStatus,
211 p_msg => 'to many rows: ' || SQLERRM,
212 x_returnStatus => l_returnStatus); --discard logging return status
213 wip_logger.cleanUp(x_returnStatus => l_returnStatus); --discard logging return status
214 end if;
215 when NO_DATA_FOUND then
216 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
217 fnd_msg_pub.add_exc_msg(p_pkg_name => 'wip_mtlInterfaceProc_pub',
218 p_procedure_name => 'processInterface',
219 p_error_text => SQLERRM);
220
221 wip_utilities.get_message_stack(p_msg => x_errorMsg,
222 p_separator => ' ',
223 p_delete_stack => fnd_api.g_false);
224
225 if (l_logLevel <= wip_constants.trace_logging) then
226 wip_logger.exitPoint(p_procName => 'wip_mtlInterfaceProc_pub.processInterface',
227 p_procReturnStatus => x_returnStatus,
228 p_msg => 'no data found: ' || SQLERRM,
229 x_returnStatus => l_returnStatus); --discard logging return status
230 wip_logger.cleanUp(x_returnStatus => l_returnStatus); --discard logging return status
231 end if;
232 when others then
233 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
234 fnd_msg_pub.add_exc_msg(p_pkg_name => 'wip_mtlInterfaceProc_pub',
235 p_procedure_name => 'processInterface',
236 p_error_text => SQLERRM);
237 wip_utilities.get_message_stack(p_msg => x_errorMsg,
238 p_separator => ' ',
239 p_delete_stack => fnd_api.g_false);
240
241 if (l_logLevel <= wip_constants.trace_logging) then
242 wip_logger.exitPoint(p_procName => 'wip_mtlInterfaceProc_pub.processInterface',
243 p_procReturnStatus => x_returnStatus,
244 p_msg => 'MMTT failure: ' || l_msg,
245 x_returnStatus => l_returnStatus); --discard logging return status
246 wip_logger.cleanUp(x_returnStatus => l_returnStatus); --discard logging return status
247 end if;
248 end processInterface;
249
250
251 /* private procedures */
252 procedure processInterfaceRows(p_initMsgList IN VARCHAR2,
253 p_processInv IN VARCHAR2,
254 p_endDebug IN VARCHAR2,
255 p_txnHdrID IN NUMBER,
256 x_returnStatus OUT NOCOPY VARCHAR2) is
257
258 l_returnStatus VARCHAR2(1);
259 cursor c_mtiRows is
260 select rowid,
261 transaction_interface_id txnIntID
262 from mtl_transactions_interface
263 where transaction_header_id = p_txnHdrID
264 and process_flag = 1;
265
266 cursor c_mmttRows is
267 select transaction_temp_id txnTmpID,
268 transaction_action_id txnActionID,
269 completion_transaction_id cplTxnID,
270 move_transaction_id movTxnID
271 from mtl_material_transactions_temp
272 where transaction_header_id = p_txnHdrID;
273 l_params wip_logger.param_tbl_t;
274 l_msg VARCHAR2(2000);/* Fix for bug 6034320 */
275 l_logLevel NUMBER := to_number(fnd_log.g_current_runtime_level);
276 l_retStatus NUMBER;
277 l_msgCount NUMBER;
278 l_msgData VARCHAR2(2000);
279 l_txnCount NUMBER;
280 begin
281
282 if (l_logLevel <= wip_constants.trace_logging) then
283 l_params(1).paramName := 'p_processInv (now ignored)';
284 l_params(1).paramValue := p_processInv;
285 l_params(2).paramName := 'p_txnHdrID';
286 l_params(2).paramValue := p_txnHdrID;
287 wip_logger.entryPoint(p_procName => 'wip_mtlInterfaceProc_pub.processInterfaceRows',
288 p_params => l_params,
289 x_returnStatus => x_returnStatus);
290
291 if(x_returnStatus <> fnd_api.g_ret_sts_success) then
292 raise fnd_api.g_exc_unexpected_error;
293 end if;
294 wip_logger.cleanUp(x_returnStatus=>l_returnStatus);
295 end if;
296
297 x_returnStatus := fnd_api.g_ret_sts_success;
298
299 l_msg := 'success';
300
301 l_retStatus := inv_txn_manager_pub.process_transactions(p_api_version => 1.0,
302 p_init_msg_list => fnd_api.g_true,
303 p_commit => fnd_api.g_false,
304 p_validation_level => fnd_api.g_valid_level_full,
305 p_table => 1,
306 p_header_id => p_txnHdrID,
307 x_return_status => x_returnStatus,
308 x_msg_count => l_msgCount,
309 x_msg_data => l_msgData,
310 x_trans_count => l_txnCount);
311 if(l_retStatus <> 0) then
312 if(l_msgData is not null) then
313 fnd_message.set_name('FND', 'FND_GENERIC_MESSAGE');
314 fnd_message.set_token('MESSAGE', l_msgData);
315 fnd_msg_pub.add;
316 end if;
317 l_msg := 'error from INV MTI processor:' || l_msgData;
318 raise fnd_api.g_exc_error;
319 end if;
320
321 if (l_logLevel <= wip_constants.trace_logging) then
322 wip_logger.exitPoint(p_procName => 'wip_mtlInterfaceProc_pub.processInterfaceRows',
323 p_procReturnStatus => x_returnStatus,
324 p_msg => l_msg,
325 x_returnStatus => l_returnStatus); --discard logging return status
326 end if;
327 if(fnd_api.to_boolean(p_endDebug)) then
328 wip_logger.cleanUp(x_returnStatus => l_returnStatus); --discard logging return status
329 end if;
330 exception
331 when fnd_api.g_exc_unexpected_error then
332 x_returnStatus := fnd_api.g_ret_sts_error;
333 if (l_logLevel <= wip_constants.trace_logging) then
334 wip_logger.exitPoint(p_procName => 'wip_mtlInterfaceProc_pub.processInterfaceRows',
335 p_procReturnStatus => x_returnStatus,
336 p_msg => l_msg,
337 x_returnStatus => l_returnStatus); --discard logging return status
338 end if;
339 if(fnd_api.to_boolean(p_endDebug)) then
340 wip_logger.cleanUp(x_returnStatus => l_returnStatus); --discard logging return status
341 end if;
342 when fnd_api.g_exc_error then
343 x_returnStatus := fnd_api.g_ret_sts_error;
344 if (l_logLevel <= wip_constants.trace_logging) then
345 wip_logger.exitPoint(p_procName => 'wip_mtlInterfaceProc_pub.processInterfaceRows',
346 p_procReturnStatus => x_returnStatus,
347 p_msg => l_msg,
348 x_returnStatus => l_returnStatus); --discard logging return status
349 end if;
350 if(fnd_api.to_boolean(p_endDebug)) then
351 wip_logger.cleanUp(x_returnStatus => l_returnStatus); --discard logging return status
352 end if;
353
354 when others then
355 x_returnStatus := fnd_api.g_ret_sts_error;
356 l_msg := 'unexpected error: ' || SQLERRM;
357 fnd_msg_pub.add_exc_msg(p_pkg_name => 'wip_mtlInterfaceProc_pub',
358 p_procedure_name => 'processInterfaceRows',
359 p_error_text => SQLERRM);
360 if (l_logLevel <= wip_constants.trace_logging) then
361 wip_logger.exitPoint(p_procName => 'wip_mtlInterfaceProc_pub.processInterfaceRows',
362 p_procReturnStatus => x_returnStatus,
363 p_msg => l_msg,
364 x_returnStatus => l_returnStatus); --discard logging return status
365 end if;
366 if(fnd_api.to_boolean(p_endDebug)) then
367 wip_logger.cleanUp(x_returnStatus => l_returnStatus); --discard logging return status
368 end if;
369 end processInterfaceRows;
370
371 end wip_mtlInterfaceProc_pub;