[Home] [Help]
PACKAGE BODY: APPS.WIP_MOVPROC_GRP
Source
1 PACKAGE BODY wip_movProc_grp AS
2 /* $Header: wipmvgpb.pls 120.1 2006/04/27 16:58:19 kboonyap noship $*/
3 PROCEDURE backflush(p_wipEntityID IN NUMBER,
4 p_orgID IN NUMBER,
5 p_primaryQty IN NUMBER,
6 p_txnDate IN DATE,
7 p_txnHdrID IN NUMBER,
8 p_txnType IN NUMBER,
9 p_fmOp IN NUMBER,
10 p_fmStep IN NUMBER,
11 p_toOp IN NUMBER,
12 p_toStep IN NUMBER,
13 p_movTxnID IN NUMBER,
14 p_cplTxnID IN NUMBER:= NULL,
15 x_lotSerRequired OUT NOCOPY NUMBER,
16 x_compInfo OUT NOCOPY system.wip_lot_serial_obj_t,
17 x_returnStatus OUT NOCOPY VARCHAR2) IS
18
19 BEGIN
20 wip_bflproc_priv.backflush(
21 p_wipEntityID => p_wipEntityID,
22 p_orgID => p_orgID,
23 p_primaryQty => p_primaryQty,
24 p_txnDate => p_txnDate,
25 p_txnHdrID => p_txnHdrID,
26 p_txnType => p_txnType,
27 p_entityType => WIP_CONSTANTS.LOTBASED,
28 p_fmOp => p_fmOp,
29 p_fmStep => p_fmStep,
30 p_toOp => p_toOp,
31 p_toStep => p_toStep,
32 p_movTxnID => p_movTxnID,
33 p_cplTxnID => p_cplTxnID,
34 x_compInfo => x_compInfo,
35 x_lotSerRequired => x_lotSerRequired,
36 x_returnStatus => x_returnStatus);
37
38 END backflush;
39
40 PROCEDURE backflushIntoMMTT(p_wipEntityID IN NUMBER,
41 p_orgID IN NUMBER,
42 p_primaryQty IN NUMBER,
43 p_txnDate IN DATE,
44 p_txnHdrID IN NUMBER,
45 p_txnType IN NUMBER,
46 p_fmOp IN NUMBER,
47 p_fmStep IN NUMBER,
48 p_toOp IN NUMBER,
49 p_toStep IN NUMBER,
50 p_movTxnID IN NUMBER,
51 p_cplTxnID IN NUMBER:= NULL,
52 p_mtlTxnMode IN NUMBER,
53 p_reasonID IN NUMBER:= NULL,
54 p_reference IN VARCHAR2:= NULL,
55 x_bfRequired OUT NOCOPY NUMBER,
56 x_returnStatus OUT NOCOPY VARCHAR2) IS
57
58 l_lotSerRequired NUMBER; -- throw away value
59 BEGIN
60 wip_bflProc_priv.backflush(
61 p_wipEntityID => p_wipEntityID,
62 p_orgID => p_orgID,
63 p_primaryQty => p_primaryQty,
64 p_txnDate => p_txnDate,
65 p_txnHdrID => p_txnHdrID,
66 -- Fixed bug 5056289. Populate batch_id with move_id instead of header_id.
67 p_batchID => p_movTxnID,
68 p_txnType => p_txnType,
69 p_entityType => WIP_CONSTANTS.LOTBASED,
70 p_tblName => WIP_CONSTANTS.MMTT_TBL,
71 p_fmOp => p_fmOp,
72 p_fmStep => p_fmStep,
73 p_toOp => p_toOp,
74 p_toStep => p_toStep,
75 p_movTxnID => p_movTxnID,
76 p_cplTxnID => p_cplTxnID,
77 p_mtlTxnMode => p_mtlTxnMode,
78 p_reasonID => p_reasonID,
79 p_reference => p_reference,
80 x_lotSerRequired => l_lotSerRequired,
81 x_bfRequired => x_bfRequired,
82 x_returnStatus => x_returnStatus);
83 END backflushIntoMMTT;
84
85 PROCEDURE backflushIntoMTI(p_wipEntityID IN NUMBER,
86 p_orgID IN NUMBER,
87 p_primaryQty IN NUMBER,
88 p_txnDate IN DATE,
89 p_txnHdrID IN NUMBER,
90 p_txnType IN NUMBER,
91 p_fmOp IN NUMBER,
92 p_fmStep IN NUMBER,
93 p_toOp IN NUMBER,
94 p_toStep IN NUMBER,
95 p_movTxnID IN NUMBER,
96 p_cplTxnID IN NUMBER:= NULL,
97 p_mtlTxnMode IN NUMBER,
98 p_reasonID IN NUMBER:= NULL,
99 p_reference IN VARCHAR2:= NULL,
100 x_lotSerRequired OUT NOCOPY NUMBER,
101 x_returnStatus OUT NOCOPY VARCHAR2) IS
102
103 l_bfRequired NUMBER; -- throw away value
104 BEGIN
105 wip_bflProc_priv.backflush(
106 p_wipEntityID => p_wipEntityID,
107 p_orgID => p_orgID,
108 p_primaryQty => p_primaryQty,
109 p_txnDate => p_txnDate,
110 p_txnHdrID => p_txnHdrID,
111 -- Fixed bug 5056289. Populate batch_id with move_id instead of header_id.
112 p_batchID => p_movTxnID,
113 p_txnType => p_txnType,
114 p_entityType => WIP_CONSTANTS.LOTBASED,
115 p_tblName => WIP_CONSTANTS.MTI_TBL,
116 p_fmOp => p_fmOp,
117 p_fmStep => p_fmStep,
118 p_toOp => p_toOp,
119 p_toStep => p_toStep,
120 p_movTxnID => p_movTxnID,
121 p_cplTxnID => p_cplTxnID,
122 p_mtlTxnMode => p_mtlTxnMode,
123 p_reasonID => p_reasonID,
124 p_reference => p_reference,
125 x_lotSerRequired => x_lotSerRequired,
126 x_bfRequired => l_bfRequired,
127 x_returnStatus => x_returnStatus);
128 END backflushIntoMTI;
129
130 /*************************************************************************
131 * This procedure should be called if caller want to process one record
132 * at a time.
133 *************************************************************************/
134 PROCEDURE processInterface(p_movTxnID IN NUMBER,
135 p_procPhase IN NUMBER,
136 p_txnHdrID IN NUMBER,
137 p_mtlMode IN NUMBER,
138 p_cplTxnID IN NUMBER := NULL,
139 p_commit IN VARCHAR2 := NULL,
140 x_returnStatus OUT NOCOPY VARCHAR2,
141 x_errorMsg OUT NOCOPY VARCHAR2) IS
142
143 CURSOR c_errors(p_transaction_id NUMBER) IS
144 SELECT error_column,
145 error_message
146 FROM wip_txn_interface_errors
147 WHERE transaction_id = p_transaction_id;
148
149 l_returnStatus VARCHAR2(1);
150 l_params wip_logger.param_tbl_t;
151 l_groupID NUMBER;
152 l_move_mode NUMBER;
153 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
154 l_errors c_errors%ROWTYPE;
155 BEGIN
156
157 IF(l_logLevel <= WIP_CONSTANTS.TRACE_LOGGING) THEN
158 l_params(1).paramName := 'p_movTxnID';
159 l_params(1).paramValue := p_movTxnID;
160 l_params(2).paramName := 'p_procPhase';
161 l_params(2).paramValue := p_procPhase;
162 l_params(3).paramName := 'p_txnHdrID';
163 l_params(3).paramValue := p_txnHdrID;
164 l_params(4).paramName := 'p_mtlMode';
165 l_params(4).paramValue := p_mtlMode;
166 l_params(5).paramName := 'p_cplTxnID';
167 l_params(5).paramValue := p_cplTxnID;
168 l_params(6).paramName := 'p_commit';
169 l_params(6).paramValue := p_commit;
170
171 wip_logger.entryPoint(p_procName => 'wip_movProc_grp.processInterface',
172 p_params => l_params,
173 x_returnStatus => l_returnStatus);
174
175 END IF;
176
177 SELECT group_id
178 INTO l_groupID
179 FROM wip_move_txn_interface
180 WHERE transaction_id = p_movTxnID
181 AND process_phase = p_procPhase
182 AND process_status = WIP_CONSTANTS.RUNNING
183 AND group_id IS NOT NULL;
184
185 IF(p_procPhase = WIP_CONSTANTS.MOVE_VAL) THEN
186 l_move_mode := WIP_CONSTANTS.BACKGROUND;
187 ELSE
188 l_move_mode := WIP_CONSTANTS.ONLINE;
189 END IF;
190
191 wip_movProc_priv.processIntf(p_group_id => l_groupID,
192 p_child_txn_id => -1,
193 p_mtl_header_id => p_txnHdrID,
194 p_proc_phase => p_procPhase,
195 p_time_out => 0,
196 p_move_mode => l_move_mode,
197 p_bf_mode => WIP_CONSTANTS.ONLINE,
198 p_mtl_mode => p_mtlMode,
199 p_endDebug => fnd_api.g_false,
200 p_initMsgList => fnd_api.g_true,
201 p_insertAssy => fnd_api.g_true,
202 p_do_backflush => fnd_api.g_false,
203 p_cmp_txn_id => p_cplTxnID,
204 x_returnStatus => x_returnStatus);
205
206 IF(x_returnStatus <> fnd_api.g_ret_sts_success) THEN
207 raise fnd_api.g_exc_error;
208 END IF;
209
210 IF(fnd_api.to_boolean(p_commit)) THEN
211 COMMIT;
212 END IF;
213
214 IF (l_logLevel <= wip_constants.trace_logging) THEN
215 wip_logger.exitPoint(p_procName => 'wip_movProc_grp.processInterface',
216 p_procReturnStatus => x_returnStatus,
217 p_msg => 'procedure complete',
218 x_returnStatus => l_returnStatus);
219 wip_logger.cleanUp(x_returnStatus => l_returnStatus);
220 END IF;
221 EXCEPTION
222 WHEN fnd_api.g_exc_error THEN
223 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
224
225 IF(l_move_mode = WIP_CONSTANTS.BACKGROUND) THEN
226 FOR l_errors IN c_errors(p_transaction_id => p_movTxnID) LOOP
227 x_errorMsg := x_errorMsg || l_errors.error_column ||':' ||
228 l_errors.error_message || '; ';
229 END LOOP;
230 x_errorMsg := substr(x_errorMsg, 1, 1000);
231 ELSE
232 -- get error message from message stack
233 inv_mobile_helper_functions.get_stacked_messages(x_errorMsg);
234 END IF;
235
236 IF (l_logLevel <= wip_constants.trace_logging) THEN
237 wip_logger.exitPoint(p_procName => 'wip_movProc_grp.processInterface',
238 p_procReturnStatus => x_returnStatus,
239 p_msg => 'wip_movProc_priv.processIntf failed',
240 x_returnStatus => l_returnStatus);
241 wip_logger.cleanUp(x_returnStatus => l_returnStatus);
242 END IF;
243
244 WHEN TOO_MANY_ROWS THEN
245 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
246 fnd_message.set_name('WIP', 'WIP_NOT_VALID');
247 fnd_message.set_token('ENTITY', 'TRANSACTION_ID');
248 x_errorMsg := substr(fnd_message.get, 1, 1000);
249
250 IF (l_logLevel <= wip_constants.trace_logging) THEN
251 wip_logger.exitPoint(p_procName => 'wip_movProc_grp.processInterface',
252 p_procReturnStatus => x_returnStatus,
253 p_msg => 'to many rows: ' || SQLERRM,
254 x_returnStatus => l_returnStatus);
255 wip_logger.cleanUp(x_returnStatus => l_returnStatus);
256 END IF;
257
258 WHEN NO_DATA_FOUND THEN
259 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
260 fnd_message.set_name('WIP', 'WIP_NOT_VALID');
261 fnd_message.set_token('ENTITY',
262 'TRANSACTION_ID/GROUP_ID/PROCESS_PHASE/PROCESS_STATUS');
263 x_errorMsg := substr(fnd_message.get, 1, 1000);
264
265 IF (l_logLevel <= wip_constants.trace_logging) THEN
266 wip_logger.exitPoint(p_procName => 'wip_movProc_grp.processInterface',
267 p_procReturnStatus => x_returnStatus,
268 p_msg => 'no data found: ' || SQLERRM,
269 x_returnStatus => l_returnStatus);
270 wip_logger.cleanUp(x_returnStatus => l_returnStatus);
271 END IF;
272
273 WHEN others THEN
274 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
275 x_errorMsg := 'unexpected error: ' || SQLERRM || 'SQLCODE = ' || SQLCODE;
276 IF (l_logLevel <= wip_constants.trace_logging) THEN
277 wip_logger.exitPoint(p_procName => 'wip_movProc_grp.processInterface',
278 p_procReturnStatus => x_returnStatus,
279 p_msg => x_errorMsg,
280 x_returnStatus => l_returnStatus);
281 wip_logger.cleanUp(x_returnStatus => l_returnStatus);
282 END IF;
283
284 END processInterface;
285
286 /*************************************************************************
287 * This procedure should be called if caller want to do batch processing
288 * for multiple records in WMTI.
289 *************************************************************************/
290 PROCEDURE processInterface(p_groupID IN NUMBER,
291 p_commit IN VARCHAR2 := NULL,
292 x_returnStatus OUT NOCOPY VARCHAR2) IS
293 l_returnStatus VARCHAR2(1);
294 l_params wip_logger.param_tbl_t;
295 l_groupID NUMBER;
296 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
297 BEGIN
298
299 IF(l_logLevel <= WIP_CONSTANTS.TRACE_LOGGING) THEN
300 l_params(1).paramName := 'p_groupID';
301 l_params(1).paramValue := p_groupID;
302 l_params(2).paramName := 'p_commit';
303 l_params(2).paramValue := p_commit;
304 wip_logger.entryPoint(p_procName => 'wip_movProc_grp.processInterface',
305 p_params => l_params,
306 x_returnStatus => l_returnStatus);
307
308 END IF;
309
310 wip_movProc_priv.processIntf(p_group_id => p_groupID,
311 p_child_txn_id => -1,
312 p_mtl_header_id => -1,
313 p_proc_phase => WIP_CONSTANTS.MOVE_VAL,
314 p_time_out => 0,
315 p_move_mode => WIP_CONSTANTS.BACKGROUND,
316 p_bf_mode => WIP_CONSTANTS.ONLINE,
317 p_mtl_mode => WIP_CONSTANTS.ONLINE,
318 p_endDebug => fnd_api.g_false,
319 p_initMsgList => fnd_api.g_true,
320 p_insertAssy => fnd_api.g_true,
321 p_do_backflush => fnd_api.g_true,
322 x_returnStatus => x_returnStatus);
323
324 IF(x_returnStatus <> fnd_api.g_ret_sts_success) THEN
325 raise fnd_api.g_exc_error;
326 END IF;
327
328 IF(fnd_api.to_boolean(p_commit)) THEN
329 COMMIT;
330 END IF;
331
332 IF (l_logLevel <= wip_constants.trace_logging) THEN
333 wip_logger.exitPoint(p_procName => 'wip_movProc_grp.processInterface',
334 p_procReturnStatus => x_returnStatus,
335 p_msg => 'no record in this group error out',
336 x_returnStatus => l_returnStatus);
337 wip_logger.cleanUp(x_returnStatus => l_returnStatus);
338 END IF;
339 EXCEPTION
340
341 WHEN fnd_api.g_exc_error THEN
342 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
343
344 IF (l_logLevel <= wip_constants.trace_logging) THEN
345 wip_logger.exitPoint(p_procName => 'wip_movProc_grp.processInterface',
346 p_procReturnStatus => x_returnStatus,
347 p_msg => 'some records in this group error out',
348 x_returnStatus => l_returnStatus);
349 wip_logger.cleanUp(x_returnStatus => l_returnStatus);
350 END IF;
351
352 WHEN others THEN
353 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
354
355 IF (l_logLevel <= wip_constants.trace_logging) THEN
356 wip_logger.exitPoint(p_procName => 'wip_movProc_grp.processInterface',
357 p_procReturnStatus => x_returnStatus,
358 p_msg => 'unexpected error: ' || SQLERRM,
359 x_returnStatus => l_returnStatus);
360 wip_logger.cleanUp(x_returnStatus => l_returnStatus);
361 END IF;
362 END processInterface;
363
364 END wip_movProc_grp;