[Home] [Help]
PACKAGE BODY: APPS.WMA_MOVE
Source
1 PACKAGE BODY wma_move AS
2 /* $Header: wmapmovb.pls 120.3 2007/09/17 21:26:29 kboonyap ship $ */
3
4 /**
5 * This procedure is the entry point into the Move Processing code
6 * Parameters:
7 * parameters The MoveParam containing values from the mobile move form.
8 * status Indicates success (0), failure (-1).
9 * errMessage The error or warning message, if any.
10 */
11 PROCEDURE process(parameters IN OUT NOCOPY MoveParam,
12 status OUT NOCOPY NUMBER,
13 errMessage OUT NOCOPY VARCHAR2) IS
14 error VARCHAR2(241); -- error message
15 moveRecord MoveTxnRec; -- move record for insertion
16 l_returnStatus VARCHAR2(1);
17 l_opSeq NUMBER;
18 l_step NUMBER;
19 l_mtlMode NUMBER;
20 cmpParams wma_completion.CmpParams;
21 l_params wip_logger.param_tbl_t;
22 l_logLevel NUMBER := to_number(fnd_log.g_current_runtime_level);
23 BEGIN
24 if (l_logLevel <= wip_constants.trace_logging) then
25 l_params(1).paramName := 'not printing params';
26 l_params(1).paramValue := ' ';
27 wip_logger.entryPoint(p_procName => 'wma_move.process',
28 p_params => l_params,
29 x_returnStatus => l_returnStatus);
30 end if;
31
32 status := 0;
33 IF(parameters.txnType = WIP_CONSTANTS.COMP_TXN) THEN
34 -- tell move processor not to process backflush components.
35 -- The last processor should be the one who call Inventory TM.
36 -- In this case it is Completion Processor.
37 l_mtlMode := WIP_CONSTANTS.BACKGROUND;
38 ELSE
39 -- If Move or EZ Return, move processor should be the one who call
40 -- inventory TM because it is the last processor.
41 l_mtlMode := WIP_CONSTANTS.ONLINE;
42 END IF;
43
44 IF(parameters.txnMode = WIP_CONSTANTS.ONLINE AND
45 parameters.txnType = WIP_CONSTANTS.RET_TXN) THEN
46 -- create completionParams structure from moveParams
47 cmpParams.environment := parameters.environment;
48 cmpParams.transactionType := parameters.mtlTxnTypeID;
49 cmpParams.transactionHeaderID := parameters.mtl_header_id;
50 cmpParams.transactionIntID := parameters.mtlTxnIntID;
51 cmpParams.cmpTransactionID := parameters.cmpTxnID;
52 cmpParams.movTransactionID := parameters.txnID;
53 cmpParams.wipEntityID := parameters.wipEntityID;
54 cmpParams.wipEntityName := parameters.wipEntityName;
55 cmpParams.itemID := parameters.itemID;
56 cmpParams.itemName := parameters.itemName;
57 cmpParams.transactionQty := parameters.transactionQty;
58 cmpParams.transactionUOM := parameters.transactionUOM;
59 cmpParams.subinv := parameters.subinv;
60 cmpParams.locatorID := parameters.locatorID;
61 cmpParams.locatorName := parameters.locatorName;
62 cmpParams.kanbanCardID := parameters.kanbanID;
63 cmpParams.qualityID := parameters.qualityID;
64 cmpParams.projectID := parameters.projectID;
65 cmpParams.taskID := parameters.taskID;
66 cmpParams.isFromSerializedPage := parameters.isFromSerializedPage;
67 -- call completion processor to process assy cpl
68 wma_completion.process(parameters => cmpParams,
69 processInv => fnd_api.g_true,
70 status => status,
71 errMessage => errMessage);
72 IF(status <> 0) THEN
73 if (l_logLevel <= wip_constants.trace_logging) then
74 wip_logger.exitPoint(p_procName => 'wma_move.process',
75 p_procReturnStatus => status,
76 p_msg => errMessage,
77 x_returnStatus => l_returnStatus);
78 end if;
79 return;
80 END IF;
81 END IF; -- Easy Return
82
83 /********************* Start doing move ************************/
84 -- first derive all necessary fields for insertion
85 IF(derive(moveRecord => moveRecord,
86 parameters => parameters,
87 errMessage => error) = FALSE) THEN
88 -- process error
89 status := -1;
90 errMessage := error;
91 if (l_logLevel <= wip_constants.trace_logging) then
92 wip_logger.exitPoint(p_procName => 'wma_move.process',
93 p_procReturnStatus => status,
94 p_msg => errMessage,
95 x_returnStatus => l_returnStatus);
96 end if;
97 return;
98 END IF;
99
100 -- now, ready to insert into the interface table
101 IF(put(moveRecord => moveRecord,
102 errMessage => error) = FALSE ) THEN
103 -- process error
104 status := -1;
105 errMessage := error;
106 if (l_logLevel <= wip_constants.trace_logging) then
107 wip_logger.exitPoint(p_procName => 'wma_move.process',
108 p_procReturnStatus => status,
109 p_msg => errMessage,
110 x_returnStatus => l_returnStatus);
111 end if;
112 return;
113 END IF;
114
115 -- Only call the API's below for serial txns
116 IF(parameters.isFromSerializedPage = WIP_CONSTANTS.YES) THEN
117 -- serial txns, so need to insert record into WIP_SERIAL_MOVE_INTERFACE too
118 IF(insertSerial(groupID => moveRecord.row.group_id,
119 transactionID => moveRecord.row.transaction_id,
120 serialNumber => parameters.serial,
121 errMessage => error) = FALSE) THEN
122 -- process error
123 status := -1;
124 errMessage := error;
125 if (l_logLevel <= wip_constants.trace_logging) then
126 wip_logger.exitPoint(p_procName => 'wma_move.process',
127 p_procReturnStatus => status,
128 p_msg => errMessage,
129 x_returnStatus => l_returnStatus);
130 end if;
131 return;
132 END IF;
133 END IF; -- from serialized page
134
135 -- If mobile transaction mode is online, call PL/SQL move_processor to
136 -- process record in WMTI. Otherwise stop here, and let the move manager
137 -- to pick the record
138 IF(parameters.txnMode = WIP_CONSTANTS.ONLINE) THEN
139
140
141 --move op pull txns to mmtt. This is already be done (and they would already be
142 --processed) if the txn is an easy return.
143 --note that for an easy cpl record, the assy l/s info is already written, but the
144 --assy record in MTI does not exist. Thus the 'orphaned' l/s info in MSNI/MTLI will
145 --remain there even after the validateInterfaceTxns() call until the assy record is
146 --inserted and processed in the wma_completion.process() call further below
147 IF(parameters.txnType <> WIP_CONSTANTS.RET_TXN) THEN
148 wip_mtlTempProc_priv.validateInterfaceTxns(p_txnHdrID => parameters.mtl_header_id,
149 p_initMsgList => fnd_api.g_true,
150 x_returnStatus => l_returnStatus);
151 IF(l_returnStatus <> fnd_api.g_ret_sts_success) THEN
152 /* Bug 5727205 : Commented out commit. No need to commit if validation fails.
153 commit; */
154 raise fnd_api.g_exc_unexpected_error;
155 END IF;
156 END IF;
157
158 wip_movProc_priv.processIntf
159 (p_group_id => parameters.txnID,
160 p_child_txn_id => parameters.childTxnID,
161 p_mtl_header_id => parameters.mtl_header_id,
162 p_proc_phase => WIP_CONSTANTS.MOVE_PROC,
163 p_time_out => 0,
164 p_move_mode => WIP_CONSTANTS.ONLINE,
165 p_bf_mode => WIP_CONSTANTS.ONLINE,
166 p_mtl_mode => l_mtlMode,
167 p_endDebug => FND_API.G_TRUE,
168 p_initMsgList => FND_API.G_TRUE,
169 p_insertAssy => FND_API.G_FALSE,
170 p_do_backflush => FND_API.G_FALSE,
171 x_returnStatus => l_returnStatus);
172
173 IF(l_returnStatus <> fnd_api.g_ret_sts_success) THEN
174 raise fnd_api.g_exc_unexpected_error;
175 END IF;
176 ELSE -- Background transaction
177 NULL;
178 END IF;
179
180 /********************* End doing move ************************/
181
182 IF(parameters.txnMode = WIP_CONSTANTS.ONLINE AND
183 parameters.txnType = WIP_CONSTANTS.COMP_TXN) THEN
184 -- create completionParams structure from moveParams
185 cmpParams.environment := parameters.environment;
186 cmpParams.transactionType := parameters.mtlTxnTypeID;
187 cmpParams.transactionHeaderID := parameters.mtl_header_id;
188 cmpParams.transactionIntID := parameters.mtlTxnIntID;
189 cmpParams.cmpTransactionID := parameters.cmpTxnID;
190 cmpParams.movTransactionID := parameters.txnID;
191 cmpParams.wipEntityID := parameters.wipEntityID;
192 cmpParams.wipEntityName := parameters.wipEntityName;
193 cmpParams.itemID := parameters.itemID;
194 cmpParams.itemName := parameters.itemName;
195 cmpParams.transactionQty := parameters.transactionQty;
196 cmpParams.transactionUOM := parameters.transactionUOM;
197 cmpParams.subinv := parameters.subinv;
198 cmpParams.locatorID := parameters.locatorID;
199 cmpParams.locatorName := parameters.locatorName;
200 cmpParams.kanbanCardID := parameters.kanbanID;
201 cmpParams.qualityID := parameters.qualityID;
202 cmpParams.projectID := parameters.projectID;
203 cmpParams.taskID := parameters.taskID;
204 cmpParams.isFromSerializedPage := parameters.isFromSerializedPage;
205 -- call completion processor to derive and insert assembly record
206 -- into MMTT. And also call Inventory TM to process all records in MMTT.
207 wma_completion.process(parameters => cmpParams,
208 processInv => fnd_api.g_true,
209 status => status,
210 errMessage => errMessage);
211 IF(status <> 0) THEN
212 if (l_logLevel <= wip_constants.trace_logging) then
213 wip_logger.exitPoint(p_procName => 'wma_move.process',
214 p_procReturnStatus => status,
215 p_msg => errMessage,
216 x_returnStatus => l_returnStatus);
217 end if;
218 return;
219 END IF;
220 END IF; -- Easy Complete
221
222 if (l_logLevel <= wip_constants.trace_logging) then
223 wip_logger.exitPoint(p_procName => 'wma_move.process',
224 p_procReturnStatus => status,
225 p_msg => 'success',
226 x_returnStatus => l_returnStatus);
227 end if;
228
229 EXCEPTION
230 WHEN fnd_api.g_exc_unexpected_error THEN
231 status := -1;
232 wip_utilities.get_message_stack(p_msg => errMessage);
233 if (l_logLevel <= wip_constants.trace_logging) then
234 wip_logger.exitPoint(p_procName => 'wma_move.process',
235 p_procReturnStatus => status,
236 p_msg => errMessage,
237 x_returnStatus => l_returnStatus);
238 end if;
239 WHEN others THEN
240 status := -1;
241 fnd_message.set_name ('WIP', 'GENERIC_ERROR');
242 fnd_message.set_token ('FUNCTION', 'wma_move.process');
243 fnd_message.set_token ('ERROR', SQLCODE || ' ' || SQLERRM);
244 errMessage := fnd_message.get;
245 if (l_logLevel <= wip_constants.trace_logging) then
246 wip_logger.exitPoint(p_procName => 'wma_move.process',
247 p_procReturnStatus => status,
248 p_msg => errMessage,
249 x_returnStatus => l_returnStatus);
250 end if;
251
252 END process;
253
254 /**
255 * This procedure is a wrapper on top of wip_bflProc_priv.processRequirements
256 * and wip_autoLotProc_priv.deriveLots. This procedure should be called to
257 * check whether we need to gather more lot/serial info from the user or not
258 */
259 PROCEDURE backflush(p_jobID IN NUMBER,
260 p_orgID IN NUMBER,
261 p_childMoveID IN NUMBER,
262 p_moveID IN NUMBER,
263 p_ocQty IN NUMBER,
264 p_moveQty IN NUMBER,
265 p_txnDate IN DATE,
266 p_txnHdrID IN NUMBER,
267 p_fm_op IN NUMBER,
268 p_fm_step IN NUMBER,
269 p_to_op IN NUMBER,
270 p_to_step IN NUMBER,
271 p_cmpTxnID IN NUMBER,
272 p_txnType IN NUMBER,
273 p_objectID IN NUMBER,
274 x_lotEntryType OUT NOCOPY NUMBER,
275 x_compInfo OUT NOCOPY system.wip_lot_serial_obj_t,
276 x_returnStatus OUT NOCOPY VARCHAR2,
277 x_errMessage OUT NOCOPY VARCHAR2) IS
278
279 l_first_bf_op NUMBER := -1;
280 l_last_bf_op NUMBER := -1;
281 l_maxOpSeqNum NUMBER;
282 l_bf_qty NUMBER;
283 l_first_op NUMBER;
284 i NUMBER;
285 l_returnStatus VARCHAR(1);
286 l_compTbl system.wip_component_tbl_t;
287 l_params wip_logger.param_tbl_t;
288 l_logLevel NUMBER := to_number(fnd_log.g_current_runtime_level);
289
290 BEGIN
291 if (l_logLevel <= wip_constants.trace_logging) then
292 l_params(1).paramName := 'p_jobID';
293 l_params(1).paramValue := p_jobID;
294 l_params(2).paramName := 'p_orgID';
295 l_params(2).paramValue := p_orgID;
296 l_params(3).paramName := 'p_childMoveID';
297 l_params(3).paramValue := p_childMoveID;
298 l_params(4).paramName := 'p_moveID';
299 l_params(4).paramValue := p_moveID;
300 l_params(5).paramName := 'p_ocQty';
301 l_params(5).paramValue := p_ocQty;
302 l_params(6).paramName := 'p_moveQty';
303 l_params(6).paramValue := p_moveQty;
304 l_params(7).paramName := 'p_txnDate';
305 l_params(7).paramValue := p_txnDate;
306 l_params(8).paramName := 'p_txnHdrID';
307 l_params(8).paramValue := p_txnHdrID;
308 l_params(9).paramName := 'p_fm_op';
309 l_params(9).paramValue := p_fm_op;
310 l_params(10).paramName := 'p_fm_step';
311 l_params(10).paramValue := p_fm_step;
312 l_params(11).paramName := 'p_to_op';
313 l_params(11).paramValue := p_to_op;
314 l_params(12).paramName := 'p_to_step';
315 l_params(12).paramValue := p_to_step;
316 l_params(13).paramName := 'p_moveQty';
317 l_params(13).paramValue := p_moveQty;
318 -- write parameter value to log file
319 wip_logger.entryPoint(p_procName =>'wma_move.backflush',
320 p_params => l_params,
321 x_returnStatus => l_returnStatus);
322 end if;
323
324 SELECT backflush_lot_entry_type
325 INTO x_lotEntryType
326 FROM wip_parameters
327 WHERE organization_id = p_orgID;
328
329 l_compTbl := system.wip_component_tbl_t();
330
331 IF(p_txnType = WIP_CONSTANTS.COMP_TXN OR
332 p_txnType = WIP_CONSTANTS.RET_TXN) THEN -- Easy Complete/Return
333 -- get the last operation to pass to backflush processor
334 SELECT NVL(MAX(operation_seq_num), 1)
335 INTO l_maxOpSeqNum
336 FROM wip_operations
337 WHERE wip_entity_id = p_jobID;
338
339 IF(p_txnType = WIP_CONSTANTS.COMP_TXN) THEN
340 l_bf_qty := p_moveQty;
341 ELSIF(p_txnType = WIP_CONSTANTS.RET_TXN) THEN
342 l_bf_qty := -1 * p_moveQty;
343 END IF;
344 -- call backflush processor to insert Assembly Pull components
345 wip_bflProc_priv.processRequirements
346 (p_wipEntityID => p_jobID,
347 p_wipEntityType => WIP_CONSTANTS.DISCRETE,
348 p_repSchedID => null,
349 p_repLineID => null,
350 p_cplTxnID => p_cmpTxnID,
351 p_movTxnID => null,
352 p_orgID => p_orgID,
353 p_assyQty => l_bf_qty,
354 p_txnDate => p_txnDate,
355 p_wipSupplyType => WIP_CONSTANTS.ASSY_PULL,
356 p_txnHdrID => p_txnHdrID,
357 p_firstOp => -1, -- for regular completion
358 p_lastOP => l_maxOpSeqNum,
359 p_firstMoveOp => null,
360 p_lastMoveOp => null,
361 p_srcCode => null,
362 p_mergeMode => fnd_api.g_false,
363 p_initMsgList => fnd_api.g_true,
364 p_endDebug => fnd_api.g_false,
365 p_mtlTxnMode => wip_constants.online,
366 x_compTbl => l_compTbl,
367 x_returnStatus => x_returnStatus);
368
369 IF(x_returnStatus <> fnd_api.g_ret_sts_success) THEN
370 raise fnd_api.g_exc_unexpected_error;
371 END IF;
372 END IF;-- Easy Complete/Return
373
374 -- Call bf_require to derive first_bf_op, last_bf_op, and bf_qty before
375 -- call wip_bflProc_priv.processRequirements for Operation Pull components
376
377 bf_require(p_jobID => p_jobID,
378 p_fm_op => p_fm_op,
379 p_fm_step => p_fm_step,
380 p_to_op => p_to_op,
381 p_to_step => p_to_step,
382 p_moveQty => p_moveQty,
383 x_first_bf_op => l_first_bf_op,
384 x_last_bf_op => l_last_bf_op,
385 x_bf_qty => l_bf_qty,
386 x_returnStatus => x_returnStatus,
387 x_errMessage => x_errMessage);
388
389 IF(x_returnStatus <> fnd_api.g_ret_sts_success) THEN
390 fnd_message.set_name('FND', 'FND_GENERIC_MESSAGE');
391 fnd_message.set_token('MESSAGE', x_errMessage);
392 fnd_msg_pub.add;
393 raise fnd_api.g_exc_unexpected_error;
394 END IF;
395
396 IF(l_first_bf_op <> -1) THEN
397
398 -- Call backflush processor to get operation pull components into l_compTbl
399 wip_bflProc_priv.processRequirements
400 (p_wipEntityID => p_jobID,
401 p_wipEntityType => WIP_CONSTANTS.DISCRETE,
402 p_repSchedID => null,
403 p_repLineID => null,
404 p_cplTxnID => null,
405 p_movTxnID => p_moveID,
406 p_orgID => p_orgID,
407 p_assyQty => l_bf_qty,
408 p_txnDate => p_txnDate,
409 p_wipSupplyType => WIP_CONSTANTS.OP_PULL,
410 p_txnHdrID => p_txnHdrID,
411 p_firstOp => l_first_bf_op,
412 p_lastOP => l_last_bf_op,
413 p_firstMoveOp => p_fm_op, -- use to check autocharge
414 p_lastMoveOp => p_to_op, -- use to check autocharge
415 p_srcCode => null,
416 p_mergeMode => fnd_api.g_false,
417 p_initMsgList => fnd_api.g_true,
418 p_endDebug => fnd_api.g_false,
419 p_mtlTxnMode => wip_constants.online,
420 x_compTbl => l_compTbl,
421 x_returnStatus => x_returnStatus);
422
423 IF(x_returnStatus <> fnd_api.g_ret_sts_success) THEN
424 raise fnd_api.g_exc_unexpected_error;
425 END IF;
426 END IF; -- l_first_bf_op <> -1
427
428 -- Call assy_pull_bf to derive first_bf_op, last_bf_op, and bf_qty before
429 -- call wip_bflProc_priv.processRequirements for Assembly Pull components
430 -- This is only for Scrap Transactions
431
432 -- set l_first_bf_op and l_last_bf_op back to -1
433 l_first_bf_op := -1;
434 l_last_bf_op := -1;
435
436 assy_pull_bf(p_jobID => p_jobID,
437 p_fm_op => p_fm_op,
438 p_fm_step => p_fm_step,
439 p_to_op => p_to_op,
440 p_to_step => p_to_step,
441 p_moveQty => p_moveQty,
442 x_first_bf_op => l_first_bf_op,
443 x_last_bf_op => l_last_bf_op,
444 x_bf_qty => l_bf_qty,
445 x_returnStatus => x_returnStatus,
446 x_errMessage => x_errMessage);
447
448 IF(x_returnStatus <> fnd_api.g_ret_sts_success) THEN
449 fnd_message.set_name('FND', 'FND_GENERIC_MESSAGE');
450 fnd_message.set_token('MESSAGE', x_errMessage);
451 fnd_msg_pub.add;
452 raise fnd_api.g_exc_unexpected_error;
453 END IF;
454
455 IF(l_first_bf_op <> -1) THEN
456
457 -- Call backflush processor to get assembly pull components into l_compTbl
458 -- for scrap transactions
459 wip_bflProc_priv.processRequirements
460 (p_wipEntityID => p_jobID,
461 p_wipEntityType => WIP_CONSTANTS.DISCRETE,
462 p_repSchedID => null,
463 p_repLineID => null,
464 p_cplTxnID => null,
465 p_movTxnID => p_moveID,
466 p_orgID => p_orgID,
467 p_assyQty => l_bf_qty,
468 p_txnDate => p_txnDate,
469 p_wipSupplyType => WIP_CONSTANTS.ASSY_PULL,
470 p_txnHdrID => p_txnHdrID,
471 p_firstOp => l_first_bf_op,
472 p_lastOP => l_last_bf_op,
473 p_firstMoveOp => p_fm_op, -- use to check autocharge
474 p_lastMoveOp => p_to_op, -- use to check autocharge
475 p_srcCode => null,
476 p_mergeMode => fnd_api.g_false,
477 p_initMsgList => fnd_api.g_true,
478 p_endDebug => fnd_api.g_false,
479 p_mtlTxnMode => wip_constants.online,
480 x_compTbl => l_compTbl,
481 x_returnStatus => x_returnStatus);
482
483 IF(x_returnStatus <> fnd_api.g_ret_sts_success) THEN
484 raise fnd_api.g_exc_unexpected_error;
485 END IF;
486 END IF; -- l_first_bf_op <> -1
487
488 IF(p_childMoveID <> -1) THEN
489
490 -- Over move transaction, so need to check backflush components for
491 -- child record too.
492 l_first_bf_op := -1;
493 l_last_bf_op := -1;
494 l_bf_qty := 0;
495 SELECT MIN(operation_seq_num)
496 INTO l_first_op
497 FROM wip_operations
498 WHERE wip_entity_id = p_jobID;
499
500 bf_require(p_jobID => p_jobID,
501 p_fm_op => l_first_op,
502 p_fm_step => WIP_CONSTANTS.QUEUE,
503 p_to_op => p_fm_op,
504 p_to_step => p_fm_step,
505 p_moveQty => p_ocQty,
506 x_first_bf_op => l_first_bf_op,
507 x_last_bf_op => l_last_bf_op,
508 x_bf_qty => l_bf_qty,
509 x_returnStatus => x_returnStatus,
510 x_errMessage => x_errMessage);
511
512 IF(x_returnStatus <> fnd_api.g_ret_sts_success) THEN
513 fnd_message.set_name('FND', 'FND_GENERIC_MESSAGE');
514 fnd_message.set_token('MESSAGE', x_errMessage);
515 fnd_msg_pub.add;
516 raise fnd_api.g_exc_unexpected_error;
517 END IF;
518
519 IF(l_first_bf_op <> -1) THEN
520
521 /* Call backflush processor to get component into l_compTbl*/
522 wip_bflProc_priv.processRequirements
523 (p_wipEntityID => p_jobID,
524 p_wipEntityType => WIP_CONSTANTS.DISCRETE,
525 p_repSchedID => null,
526 p_repLineID => null,
527 p_cplTxnID => null,
528 p_movTxnID => p_childMoveID,
529 p_orgID => p_orgID,
530 p_assyQty => l_bf_qty,
531 p_txnDate => p_txnDate,
532 p_wipSupplyType => WIP_CONSTANTS.OP_PULL,
533 p_txnHdrID => p_txnHdrID,
534 p_firstOp => l_first_bf_op,
535 p_lastOP => l_last_bf_op,
536 p_firstMoveOp => p_fm_op, -- use to check autocharge
537 p_lastMoveOp => p_to_op, -- use to check autocharge
538 p_srcCode => null,
539 p_mergeMode => fnd_api.g_false,
540 p_initMsgList => fnd_api.g_true,
541 p_endDebug => fnd_api.g_false,
542 p_mtlTxnMode => wip_constants.online,
543 x_compTbl => l_compTbl,
544 x_returnStatus => x_returnStatus);
545
546 IF(x_returnStatus <> fnd_api.g_ret_sts_success) THEN
547 raise fnd_api.g_exc_unexpected_error;
548 END IF;
549 END IF; -- end l_first_bf_op <> -1
550
551 -- Call assy_pull_bf to derive first_bf_op, last_bf_op, and bf_qty before
552 -- call wip_bflProc_priv.processRequirements for Assembly Pull components
553 -- This is only for Scrap Transactions
554
555 -- set l_first_bf_op and l_last_bf_op back to -1
556 l_first_bf_op := -1;
557 l_last_bf_op := -1;
558
559 assy_pull_bf(p_jobID => p_jobID,
560 p_fm_op => l_first_op,
561 p_fm_step => WIP_CONSTANTS.QUEUE,
562 p_to_op => p_fm_op,
563 p_to_step => p_fm_step,
564 p_moveQty => p_ocQty,
565 x_first_bf_op => l_first_bf_op,
566 x_last_bf_op => l_last_bf_op,
567 x_bf_qty => l_bf_qty,
568 x_returnStatus => x_returnStatus,
569 x_errMessage => x_errMessage);
570
571 IF(x_returnStatus <> fnd_api.g_ret_sts_success) THEN
572 fnd_message.set_name('FND', 'FND_GENERIC_MESSAGE');
573 fnd_message.set_token('MESSAGE', x_errMessage);
574 fnd_msg_pub.add;
575 raise fnd_api.g_exc_unexpected_error;
576 END IF;
577
578 IF(l_first_bf_op <> -1) THEN
579
580 -- Call backflush processor to get assembly pull components into
581 -- l_compTbl for scrap transactions
582 wip_bflProc_priv.processRequirements
583 (p_wipEntityID => p_jobID,
584 p_wipEntityType => WIP_CONSTANTS.DISCRETE,
585 p_repSchedID => null,
586 p_repLineID => null,
587 p_cplTxnID => null,
588 p_movTxnID => p_childMoveID,
589 p_orgID => p_orgID,
590 p_assyQty => l_bf_qty,
591 p_txnDate => p_txnDate,
592 p_wipSupplyType => WIP_CONSTANTS.ASSY_PULL,
593 p_txnHdrID => p_txnHdrID,
594 p_firstOp => l_first_bf_op,
595 p_lastOP => l_last_bf_op,
596 p_firstMoveOp => p_fm_op, -- use to check autocharge
597 p_lastMoveOp => p_to_op, -- use to check autocharge
598 p_srcCode => null,
599 p_mergeMode => fnd_api.g_false,
600 p_initMsgList => fnd_api.g_true,
601 p_endDebug => fnd_api.g_false,
602 p_mtlTxnMode => wip_constants.online,
603 x_compTbl => l_compTbl,
604 x_returnStatus => x_returnStatus);
605
606 IF(x_returnStatus <> fnd_api.g_ret_sts_success) THEN
607 raise fnd_api.g_exc_unexpected_error;
608 END IF;
609 END IF; -- l_first_bf_op <> -1
610
611 END IF; -- end over move transaction
612 if (l_logLevel <= wip_constants.full_logging) then
613 wip_logger.log(p_msg => 'before system.wip_lot_serial_obj_t',
614 x_returnStatus => l_returnStatus);
615 end if;
616 x_compInfo := system.wip_lot_serial_obj_t(null, null, null, l_compTbl,
617 null, null);
618 if (l_logLevel <= wip_constants.full_logging) then
619 wip_logger.log(p_msg => 'after system.wip_lot_serial_obj_t',
620 x_returnStatus => l_returnStatus);
621 end if;
622 x_compInfo.initialize;
623 if (l_logLevel <= wip_constants.full_logging) then
624 wip_logger.log(p_msg => 'after x_compInfo.initialize',
625 x_returnStatus => l_returnStatus);
626 end if;
627 -- derive lot if the component under lot control, if return status
628 -- is 'E' mean cannot derive lot, so the user need to provide more
629 -- info if move_mode is online. For background, error out if cannot
630 -- derive lot.
631 wip_autoLotProc_priv.deriveLots(
632 x_compLots => x_compInfo,
633 p_orgID => p_orgID,
634 p_wipEntityID => p_jobID,
635 p_initMsgList => fnd_api.g_true,
636 p_endDebug => fnd_api.g_false,
637 p_destroyTrees => fnd_api.g_true,
638 p_treeMode => inv_quantity_tree_pvt.g_reservation_mode,
639 p_treeSrcName => null,
640 x_returnStatus => x_returnStatus);
641
642 if (l_logLevel <= wip_constants.full_logging) then
643 wip_logger.log(p_msg => 'after wip_autoLotProc_priv.deriveLots',
644 x_returnStatus => l_returnStatus);
645 end if;
646 IF(x_returnStatus = fnd_api.g_ret_sts_unexp_error) THEN
647 raise fnd_api.g_exc_unexpected_error;
648 END IF;
649
650 -- derive serial for serialized transaction. We can just check p_objectID.
651 -- If p_objectID is -1, don't need to call deriveSerial. Otherwise call
652 -- the API below.
653 IF(p_objectID <> -1) THEN
654 wip_autoSerialProc_priv.deriveSerial(x_compLots => x_compInfo,
655 p_orgID => p_orgID,
656 p_objectID => p_objectID,
657 p_initMsgList => fnd_api.g_true,
658 x_returnStatus => x_returnStatus);
659
660 IF(x_returnStatus = fnd_api.g_ret_sts_unexp_error) THEN
661 raise fnd_api.g_exc_unexpected_error;
662 END IF;
663 END IF;
664 x_returnStatus := fnd_api.g_ret_sts_success;
665 if (l_logLevel <= wip_constants.trace_logging) then
666 wip_logger.exitPoint(p_procName => 'wma_move.backflush',
667 p_procReturnStatus => x_returnStatus,
668 p_msg => 'procedure complete',
669 x_returnStatus => l_returnStatus);
670 end if;
671 EXCEPTION
672 WHEN fnd_api.g_exc_unexpected_error THEN
673 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
674 x_errMessage := fnd_msg_pub.get(p_encoded => 'F');
675 if (l_logLevel <= wip_constants.trace_logging) then
676 wip_logger.exitPoint(p_procName => 'wma_move.backflush',
677 p_procReturnStatus => x_returnStatus,
678 p_msg => x_errMessage,
679 x_returnStatus => l_returnStatus);
680 end if;
681 WHEN others THEN
682 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
683 x_errMessage := 'unexpected error: ' || SQLERRM;
684 if (l_logLevel <= wip_constants.trace_logging) then
685 wip_logger.exitPoint(p_procName => 'wma_move.backflush',
686 p_procReturnStatus => x_returnStatus,
687 p_msg => x_errMessage,
688 x_returnStatus => l_returnStatus);
689 end if;
690 END backflush;
691
692 /**
693 * This function derives and validates the values necessary for executing
694 * the move transaction. Given the form parameters, it populates
695 * moveRecord preparing it to be inserted into the interface table.
696 * This is the most important procedure for move background transaction
697 * Parameters:
698 * moveRecord record to be populated. The minimum number of fields to
699 * execute the move transaction successfully are populated
700 * parameters move mobile form parameters
701 * errMessage populated if an error occurrs
702 * Return:
703 * boolean flag indicating the successful derivation of necessary values
704 */
705 Function derive(moveRecord IN OUT NOCOPY MoveTxnRec,
706 parameters IN MoveParam,
707 errMessage IN OUT NOCOPY VARCHAR2)
708 return boolean IS
709 job wma_common.Job;
710 item wma_common.Item;
711 periodID number;
712 fmOpCode varchar2(5);
713 fmDeptID number;
714 fmDeptCode varchar2(11);
715 fmPrevOpSeq number;
716 fmNextOpSeq number;
717 fmOpExists boolean;
718 toOpCode varchar2(5);
719 toDeptID number;
720 toDeptCode varchar2(11);
721 toPrevOpSeq number;
722 toNextOpSeq number;
723 toOpExists boolean;
724 openPastPeriod boolean := false;
725 txnMode NUMBER;
726 l_returnStatus VARCHAR(1);
727 l_revision VARCHAR2(3);
728 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
729 BEGIN
730
731 -- derive info about the job
732 job := wma_derive.getJob(parameters.wipEntityID);
733
734 if (job.wipEntityID is null) then
735 fnd_message.set_name ('WIP', 'WIP_JOB_DOES_NOT_EXIST');
736 fnd_message.set_token('INTERFACE', 'wma_move.derive', TRUE);
737 errMessage := fnd_message.get;
738 return false;
739 end if;
740
741 if(parameters.txnMode = WIP_CONSTANTS.BACKGROUND AND
742 (parameters.txnType = WIP_CONSTANTS.RET_TXN OR
743 parameters.txnType = WIP_CONSTANTS.COMP_TXN)) then
744 -- Only check revision for background transaction because we skip
745 -- validation code if mobile insert record into WMTI. For online txns
746 -- wma_completion.derive will validate the revision before insert into MMTT
747
748 -- get the item info
749 item := wma_derive.getItem(parameters.itemID,
750 parameters.environment.orgID,
751 parameters.locatorID);
752 if (item.invItemID is null) then
753 fnd_message.set_name ('WIP', 'WIP_ITEM_DOES_NOT_EXIST');
754 errMessage := fnd_message.get;
755 return false;
756 end if;
757
758 -- get the item revision
759 if (item.revQtyControlCode = WIP_CONSTANTS.REVISION_CONTROLLED) then
760 if(NOT wma_completion.getRevision(
761 wipEntityID => parameters.wipEntityID,
762 orgID => parameters.environment.orgID,
763 itemID => parameters.itemID,
764 revision => l_revision)) then
765 errMessage := substr(fnd_message.get,1,241);
766 return false;
767 end if; -- getRevision
768 end if; -- revQtyControlCode = WIP_CONSTANTS.REVISION_CONTROLLED
769 end if; -- Background transaction
770
771 -- derive the accounting period stuff by calling inv routine
772 invttmtx.tdatechk(
773 org_id => parameters.environment.orgID,
774 transaction_date => sysdate,
775 period_id => periodID,
776 open_past_period => openPastPeriod);
777
778 if (periodID = -1 or periodID = 0) then
779 fnd_message.set_name(
780 application => 'INV',
781 name => 'INV_NO_OPEN_PERIOD');
782 errMessage := fnd_message.get;
783 return false;
784 end if;
785
786 -- derive the operation related information based on sequence number (From)
787 wip_operations_info.derive_info(
788 p_org_id => parameters.environment.orgID,
789 p_wip_entity_id => parameters.wipEntityID,
790 p_first_schedule_id => null,
791 p_operation_seq_num => parameters.fmOpSeqNum,
792 p_operation_code => fmOpCode,
793 p_department_id => fmDeptID,
794 p_department_code => fmDeptCode,
795 p_prev_op_seq_num => fmPrevOpSeq,
796 p_next_op_seq_num => fmNextOpSeq,
797 p_operation_exists => fmOpExists);
798
799 -- derive the operation related information based on sequence number (To)
800 wip_operations_info.derive_info(
801 p_org_id => parameters.environment.orgID,
802 p_wip_entity_id => parameters.wipEntityID,
803 p_first_schedule_id => null,
804 p_operation_seq_num => parameters.toOpSeqNum,
805 p_operation_code => toOpCode,
806 p_department_id => toDeptID,
807 p_department_code => toDeptCode,
808 p_prev_op_seq_num => toPrevOpSeq,
809 p_next_op_seq_num => toNextOpSeq,
810 p_operation_exists => toOpExists);
811
812 -- now derive the rest of the mandatory fields in the MoveTxnRec
813 IF(parameters.txnID = NULL) THEN
814 IF (l_logLevel <= wip_constants.full_logging) THEN
815 wip_logger.log(p_msg => 'before wma_derive.getNextVal',
816 x_returnStatus => l_returnStatus);
817 END IF;
818 moveRecord.row.transaction_id := wma_derive.getNextVal
819 ('wip_transactions_s');
820 IF (l_logLevel <= wip_constants.full_logging) THEN
821 wip_logger.log(p_msg => 'after wma_derive.getNextVal',
822 x_returnStatus => l_returnStatus);
823 END IF;
824 ELSE
825 moveRecord.row.transaction_id := parameters.txnID;
826 END IF;
827
828 IF(parameters.txnMode = WIP_CONSTANTS.ONLINE) THEN
829 moveRecord.row.process_status := WIP_CONSTANTS.RUNNING;
830 moveRecord.row.group_id := moveRecord.row.transaction_id;
831 ELSE -- background
832 moveRecord.row.process_status := WIP_CONSTANTS.PENDING;
833 moveRecord.row.group_id := NULL;
834 END IF;
835 moveRecord.row.last_update_date := sysdate;
836 moveRecord.row.last_updated_by := parameters.environment.userID;
837 moveRecord.row.last_updated_by_name := parameters.environment.userName;
838 moveRecord.row.creation_date := sysdate;
839 moveRecord.row.created_by := parameters.environment.userID;
840 moveRecord.row.created_by_name := parameters.environment.userName;
841 moveRecord.row.process_phase := WIP_CONSTANTS.MOVE_PROC;
842
843 moveRecord.row.transaction_type := parameters.txnType;
844 moveRecord.row.organization_id := parameters.environment.orgID;
845 moveRecord.row.organization_code := parameters.environment.orgCode;
846 moveRecord.row.wip_entity_id := parameters.wipEntityID;
847 moveRecord.row.wip_entity_name := parameters.wipEntityName;
848 moveRecord.row.entity_type := WIP_CONSTANTS.DISCRETE; --only support discrete now
849 moveRecord.row.primary_item_id := parameters.itemID;
850 moveRecord.row.line_id := job.lineID;
851 moveRecord.row.line_code := job.lineCode;
852 moveRecord.row.transaction_date := sysdate;
853 moveRecord.row.acct_period_id := periodID;
854 moveRecord.row.fm_operation_seq_num := parameters.fmOpSeqNum;
855 moveRecord.row.fm_operation_code := fmOpCode;
856 moveRecord.row.fm_department_id := fmDeptID;
857 moveRecord.row.fm_department_code := fmDeptCode;
858 moveRecord.row.fm_intraoperation_step_type := parameters.fmStepType;
859 moveRecord.row.to_operation_seq_num := parameters.toOpSeqNum;
860 moveRecord.row.to_operation_code := toOpCode;
861 moveRecord.row.to_department_id := toDeptID;
862 moveRecord.row.to_department_code := toDeptCode;
863 moveRecord.row.to_intraoperation_step_type := parameters.toStepType;
864 moveRecord.row.transaction_quantity := parameters.transactionQty;
865 moveRecord.row.transaction_uom := parameters.transactionUOM;
866 moveRecord.row.primary_quantity := parameters.transactionQty;
867 moveRecord.row.primary_uom := parameters.transactionUOM;
868 moveRecord.row.scrap_account_id := parameters.scrapAcctID;
869 moveRecord.row.reason_id := parameters.reasonID;
870 moveRecord.row.qa_collection_id := parameters.qualityID;
871 moveRecord.row.overcompletion_transaction_qty := parameters.overcompleteQty;
872 moveRecord.row.overcompletion_primary_qty := parameters.overcompleteQty;
873 -- insert different source_code for serialized transactions
874 IF(parameters.isFromSerializedPage = WIP_CONSTANTS.YES) THEN
875 moveRecord.row.source_code := WMA_COMMON.SERIALIZATION_SOURCE_CODE;
876 ELSE
877 moveRecord.row.source_code := WMA_COMMON.SOURCE_CODE;
878 END IF;
879 -- successful, return
880 return true;
881
882 EXCEPTION
883 when others then
884 fnd_message.set_name ('WIP', 'GENERIC_ERROR');
885 fnd_message.set_token ('FUNCTION', 'wma_move.derive');
886 fnd_message.set_token ('ERROR', SQLCODE || ' ' || SQLERRM);
887 errMessage := fnd_message.get;
888 return false;
889 END derive;
890
891 /**
892 * This function is only use for scrap transaction. It takes fm_op, fm_step,
893 * to_op, to_step and check which operation that we need to issue/return
894 * assembly pull components to/from inventory. If the transaction is not
895 * scrap txn, this routine will not set anything.
896 * This function will return the first_operation, last_operation,
897 * and backflush quantity that the user can pass to
898 * wip_bflProc_priv.processRequirements. The caller should check the
899 * original value of x_first_bf_op and compare with the one
900 * that this procedure return. If it is the same value, it mean no backflush
901 * require because this routine will only set the value when backflush is
902 * require.
903 *
904 * NOTE:
905 * This routine only concern abot Assembly Pull components for scrap txns.
906 * For Operation Pull component, please use bf_require procedure instead.
907 */
908
909 PROCEDURE assy_pull_bf(p_jobID IN NUMBER,
910 p_fm_op IN NUMBER,
911 p_fm_step IN NUMBER,
912 p_to_op IN NUMBER,
913 p_to_step IN NUMBER,
914 p_moveQty IN NUMBER,
915 x_first_bf_op OUT NOCOPY NUMBER,
916 x_last_bf_op OUT NOCOPY NUMBER,
917 x_bf_qty OUT NOCOPY NUMBER,
918 x_returnStatus OUT NOCOPY VARCHAR2,
919 x_errMessage OUT NOCOPY VARCHAR2) IS
920
921 CURSOR c_last_bf_op IS
922
923 SELECT p_moveQty *
924 DECODE(
925 SIGN(p_to_step - WIP_CONSTANTS.SCRAP),
926 0, DECODE(SIGN(p_fm_step - WIP_CONSTANTS.SCRAP),
927 0, DECODE(SIGN(p_to_op - p_fm_op),
928 1,1,
929 -1,-1),
930 -1, 1),
931 -1, DECODE(SIGN(p_fm_step - WIP_CONSTANTS.SCRAP),
932 0, -1)) txn_qty,
933 MAX(wop.operation_seq_num) last_op
934 FROM wip_operations wop
935 WHERE wop.wip_entity_id = p_jobID
936 AND ((wop.operation_seq_num = p_fm_op AND
937 p_fm_step = WIP_CONSTANTS.SCRAP)
938 OR
939 (wop.operation_seq_num = p_to_op AND
940 p_to_step = WIP_CONSTANTS.SCRAP)
941 );
942
943 CURSOR c_first_bf_op IS
944
945 SELECT MIN(wop.operation_seq_num) first_op
946 FROM wip_operations wop
947 WHERE wop.wip_entity_id = p_jobID
948 AND wop.operation_seq_num >
949 DECODE(SIGN(p_fm_step - WIP_CONSTANTS.SCRAP),
950 0, DECODE(SIGN(p_to_step - WIP_CONSTANTS.SCRAP),
951 0, DECODE(SIGN(p_to_op - p_fm_op),
952 1, p_fm_op,
953 p_to_op),
954 0),
955 0);
956
957 l_last_bf_op c_last_bf_op%ROWTYPE;
958 l_params wip_logger.param_tbl_t;
959 l_returnStatus VARCHAR(1);
960 l_first_op NUMBER;
961 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
962
963 BEGIN
964 if (l_logLevel <= wip_constants.trace_logging) then
965 l_params(1).paramName := 'p_jobID';
966 l_params(1).paramValue := p_jobID;
967 l_params(2).paramName := 'p_fm_op';
968 l_params(2).paramValue := p_fm_op;
969 l_params(3).paramName := 'p_fm_step';
970 l_params(3).paramValue := p_fm_step;
971 l_params(4).paramName := 'p_to_op';
972 l_params(4).paramValue := p_to_op;
973 l_params(5).paramName := 'p_to_step';
974 l_params(5).paramValue := p_to_step;
975 l_params(6).paramName := 'p_moveQty';
976 l_params(6).paramValue := p_moveQty;
977 -- write parameter value to log file
978 wip_logger.entryPoint(p_procName =>'wma_move.assy_pull_bf',
979 p_params => l_params,
980 x_returnStatus => l_returnStatus);
981 end if;
982
983 OPEN c_last_bf_op;
984 LOOP
985 FETCH c_last_bf_op INTO l_last_bf_op;
986 EXIT WHEN c_last_bf_op%NOTFOUND;
987 IF(l_last_bf_op.last_op IS NOT NULL) THEN
988 -- get the first backflush operation
989 OPEN c_first_bf_op;
990 LOOP
991 FETCH c_first_bf_op INTO l_first_op;
992 EXIT WHEN c_first_bf_op%NOTFOUND;
993 END LOOP;
994
995 -- return operation pull backflush info to the caller
996 x_first_bf_op := l_first_op;
997 x_last_bf_op := l_last_bf_op.last_op;
998 x_bf_qty := l_last_bf_op.txn_qty;
999 END IF;
1000 END LOOP;
1001
1002 -- if cannot find last_bf_op mean, no backflush required for this move txn
1003 -- we don't need to set anything
1004
1005 x_returnStatus := fnd_api.g_ret_sts_success;
1006 if (l_logLevel <= wip_constants.trace_logging) then
1007 wip_logger.exitPoint(p_procName => 'wma_move.assy_pull_bf',
1008 p_procReturnStatus => x_returnStatus,
1009 p_msg => 'procedure complete',
1010 x_returnStatus => l_returnStatus);
1011 end if;
1012
1013 IF(c_last_bf_op%ISOPEN) THEN
1014 CLOSE c_last_bf_op;
1015 END IF;
1016 IF(c_first_bf_op%ISOPEN) THEN
1017 CLOSE c_first_bf_op;
1018 END IF;
1019
1020 EXCEPTION
1021 WHEN others THEN
1022 IF(c_last_bf_op%ISOPEN) THEN
1023 CLOSE c_last_bf_op;
1024 END IF;
1025 IF(c_first_bf_op%ISOPEN) THEN
1026 CLOSE c_first_bf_op;
1027 END IF;
1028 x_errMessage := 'unexpected error: ' || SQLERRM;
1029 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
1030 if (l_logLevel <= wip_constants.trace_logging) then
1031 wip_logger.exitPoint(p_procName => 'wma_move.assy_pull_bf',
1032 p_procReturnStatus => x_returnStatus,
1033 p_msg => x_errMessage,
1034 x_returnStatus => l_returnStatus);
1035 end if;
1036
1037 END assy_pull_bf;
1038
1039 /**
1040 * This function take fm_op, fm_step, to_op, to_step and check whether do we
1041 * need to call backflush or not. If backflush required, this function will
1042 * return the first_operation, last_operation, and backflush quantity that
1043 * the user can pass to wip_bflProc_priv.processRequirements. The caller
1044 * should check the original value of x_first_bf_op and compare with the one
1045 * that this procedure return. If it is the same value, it mean no backflush
1046 * require because this routine will only set the value when backflush is
1047 * require. If the x_bf_qty is positive, it is component issue transaction.
1048 * Otherwise, it is component return transaction.
1049 *
1050 * NOTE:
1051 * This routine support for both regulare move and scrap transaction. However,
1052 * this routine only concern abot Operation Pull components. For scrap txns
1053 * we need to backflush Assembly Pull components too. Please read assy_pull_bf
1054 * procedure for more info
1055 */
1056
1057 PROCEDURE bf_require(p_jobID IN NUMBER,
1058 p_fm_op IN NUMBER,
1059 p_fm_step IN NUMBER,
1060 p_to_op IN NUMBER,
1061 p_to_step IN NUMBER,
1062 p_moveQty IN NUMBER,
1063 x_first_bf_op OUT NOCOPY NUMBER,
1064 x_last_bf_op OUT NOCOPY NUMBER,
1065 x_bf_qty OUT NOCOPY NUMBER,
1066 x_returnStatus OUT NOCOPY VARCHAR2,
1067 x_errMessage OUT NOCOPY VARCHAR2) IS
1068
1069 CURSOR c_last_bf_op IS
1070
1071 SELECT p_moveQty *
1072 DECODE(
1073 SIGN(p_to_op - p_fm_op),
1074 0, DECODE(SIGN(p_to_step - p_fm_step),
1075 1, 1,
1076 -1),
1077 1, 1,
1078 -1,-1) txn_qty,
1079 MAX(wop.operation_seq_num) last_op
1080 FROM wip_operations wop
1081 WHERE wop.wip_entity_id = p_jobID
1082 AND DECODE(SIGN(DECODE(SIGN(p_to_op - p_fm_op),
1083 -1, p_fm_step,
1084 1, p_to_step,
1085 0, DECODE(SIGN(p_to_step - p_fm_step),
1086 1, p_to_step,
1087 p_fm_step))
1088 - WIP_CONSTANTS.RUN),
1089 1, DECODE(SIGN(p_to_op - p_fm_op), -1, p_fm_op, p_to_op)
1090 + 0.0000001,
1091 DECODE(SIGN(p_to_op - p_fm_op), -1, p_fm_op, p_to_op))
1092 > wop.operation_seq_num
1093 AND wop.operation_seq_num >= DECODE(SIGN(p_to_op - p_fm_op),
1094 -1, p_to_op,
1095 p_fm_op)
1096 AND (wop.backflush_flag = WIP_CONSTANTS.YES
1097 OR
1098 (wop.operation_seq_num = p_fm_op AND
1099 p_fm_step = WIP_CONSTANTS.SCRAP)
1100 OR
1101 (wop.operation_seq_num = p_to_op AND
1102 p_to_step = WIP_CONSTANTS.SCRAP));
1103
1104 CURSOR c_first_bf_op IS
1105
1106 SELECT MIN(wop.operation_seq_num) first_op
1107 FROM wip_operations wop
1108 WHERE wop.wip_entity_id = p_jobID
1109 AND wop.operation_seq_num >
1110 (SELECT NVL(MAX(wop1.operation_seq_num), 0)
1111 FROM wip_operations wop1
1112 WHERE wop1.wip_entity_id = p_jobID
1113 AND DECODE(SIGN(DECODE(SIGN(p_to_op - p_fm_op),
1114 -1, p_to_step,
1115 1, p_fm_step,
1116 0, DECODE(SIGN(p_to_step - p_fm_step),
1117 1, p_fm_step,
1118 p_to_step))
1119 - WIP_CONSTANTS.RUN),
1120 1, DECODE(SIGN(p_to_op - p_fm_op), -1,p_to_op, p_fm_op)
1121 + 0.0000001,
1122 DECODE(SIGN(p_to_op - p_fm_op), -1, p_to_op,p_fm_op))
1123 > wop1.operation_seq_num
1124 AND (wop1.backflush_flag = WIP_CONSTANTS.YES
1125 OR
1126 (p_to_op > p_fm_op AND
1127 wop1.operation_seq_num = p_fm_op AND
1128 p_fm_step = WIP_CONSTANTS.SCRAP)
1129 OR
1130 (p_to_op < p_fm_op AND
1131 wop1.operation_seq_num = p_to_op AND
1132 p_to_step = WIP_CONSTANTS.SCRAP)));
1133
1134 CURSOR c_scrap_comp IS
1135
1136 SELECT MIN(wop.operation_seq_num) first_op,
1137 p_to_op last_op,
1138 p_moveQty txn_qty
1139 FROM wip_operations wop
1140 WHERE wop.wip_entity_id = p_jobID
1141 AND wop.operation_seq_num >
1142 (SELECT NVL(MAX(wop1.operation_seq_num),0)
1143 FROM wip_operations wop1
1144 WHERE wop1.wip_entity_id = p_jobID
1145 AND p_fm_op > p_to_op
1146 AND p_to_step = WIP_CONSTANTS.SCRAP
1147 AND p_to_op >= wop1.operation_seq_num
1148 AND (wop1.backflush_flag = WIP_CONSTANTS.YES));
1149
1150 l_last_bf_op c_last_bf_op%ROWTYPE;
1151 l_scrap_comp c_scrap_comp%ROWTYPE;
1152 l_params wip_logger.param_tbl_t;
1153 l_returnStatus VARCHAR(1);
1154 l_first_op NUMBER;
1155 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
1156
1157 BEGIN
1158 l_params(1).paramName := 'p_jobID';
1159 l_params(1).paramValue := p_jobID;
1160 l_params(2).paramName := 'p_fm_op';
1161 l_params(2).paramValue := p_fm_op;
1162 l_params(3).paramName := 'p_fm_step';
1163 l_params(3).paramValue := p_fm_step;
1164 l_params(4).paramName := 'p_to_op';
1165 l_params(4).paramValue := p_to_op;
1166 l_params(5).paramName := 'p_to_step';
1167 l_params(5).paramValue := p_to_step;
1168 l_params(6).paramName := 'p_moveQty';
1169 l_params(6).paramValue := p_moveQty;
1170
1171 -- write parameter value to log file
1172 if (l_logLevel <= wip_constants.trace_logging) then
1173 wip_logger.entryPoint(p_procName =>'wma_move.bf_require',
1174 p_params => l_params,
1175 x_returnStatus => l_returnStatus);
1176 end if;
1177
1178 OPEN c_last_bf_op;
1179 LOOP
1180 FETCH c_last_bf_op INTO l_last_bf_op;
1181 EXIT WHEN c_last_bf_op%NOTFOUND;
1182
1183 -- get the first backflush operation
1184 OPEN c_first_bf_op;
1185 LOOP
1186 FETCH c_first_bf_op INTO l_first_op;
1187 EXIT WHEN c_first_bf_op%NOTFOUND;
1188 END LOOP;
1189
1190 IF(l_last_bf_op.last_op >= l_first_op) THEN
1191 -- return operation pull backflush info to the caller
1192 x_first_bf_op := l_first_op;
1193 x_last_bf_op := l_last_bf_op.last_op;
1194 x_bf_qty := l_last_bf_op.txn_qty;
1195 ELSE
1196 -- only do this for backward move to scrap transactions. We need to issue
1197 -- all operation pull component upto scrap operations.
1198 IF(p_to_op <> p_fm_op AND p_to_step = WIP_CONSTANTS.SCRAP) THEN
1199 OPEN c_scrap_comp;
1200 LOOP
1201 FETCH c_scrap_comp INTO l_scrap_comp;
1202 EXIT WHEN c_scrap_comp%NOTFOUND;
1203 IF(l_scrap_comp.last_op >= l_scrap_comp.first_op) THEN
1204 -- return operation pull backflush info to the caller
1205 x_first_bf_op := l_scrap_comp.first_op;
1206 x_last_bf_op := l_scrap_comp.last_op;
1207 x_bf_qty := l_scrap_comp.txn_qty;
1208 END IF;
1209 END LOOP;
1210 END IF; -- to_op <> fm_op
1211 END IF; -- l_last_bf_op.last_op >= first_op
1212 END LOOP;
1213
1214 -- if cannot find last_bf_op mean, no backflush required for this move txn
1215 -- we don't need to set anything
1216
1217 x_returnStatus := fnd_api.g_ret_sts_success;
1218 if (l_logLevel <= wip_constants.trace_logging) then
1219 wip_logger.exitPoint(p_procName => 'wma_move.bf_require',
1220 p_procReturnStatus => x_returnStatus,
1221 p_msg => 'procedure complete',
1222 x_returnStatus => l_returnStatus);
1223 end if;
1224
1225 IF(c_last_bf_op%ISOPEN) THEN
1226 CLOSE c_last_bf_op;
1227 END IF;
1228 IF(c_first_bf_op%ISOPEN) THEN
1229 CLOSE c_first_bf_op;
1230 END IF;
1231 IF(c_scrap_comp%ISOPEN) THEN
1232 CLOSE c_scrap_comp;
1233 END IF;
1234
1235 EXCEPTION
1236 WHEN others THEN
1237 IF(c_last_bf_op%ISOPEN) THEN
1238 CLOSE c_last_bf_op;
1239 END IF;
1240 IF(c_first_bf_op%ISOPEN) THEN
1241 CLOSE c_first_bf_op;
1242 END IF;
1243 IF(c_scrap_comp%ISOPEN) THEN
1244 CLOSE c_scrap_comp;
1245 END IF;
1246 x_errMessage := 'unexpected error: ' || SQLERRM;
1247 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
1248 if (l_logLevel <= wip_constants.trace_logging) then
1249 wip_logger.exitPoint(p_procName => 'wma_move.bf_require',
1250 p_procReturnStatus => x_returnStatus,
1251 p_msg => x_errMessage,
1252 x_returnStatus => l_returnStatus);
1253 end if;
1254 END bf_require;
1255
1256 /**
1257 * This procedure validates the quantity entered by the user. The transaction
1258 * quantity should be validated against the from operation sequence, from
1259 * intraoperation step type. In the case of overcompletion, the tolerence
1260 * level is checked.
1261 * Check shop floor status between fromOp and toOp, also need to validate
1262 * OSP related information
1263 */
1264 PROCEDURE validate(p_userID IN NUMBER,
1265 p_orgID IN NUMBER,
1266 p_jobID IN NUMBER,
1267 p_fmOp IN NUMBER,
1268 p_fmStep IN NUMBER,
1269 p_toOp IN NUMBER,
1270 p_toStep IN NUMBER,
1271 p_overcomplQty IN NUMBER,
1272 x_returnStatus OUT NOCOPY VARCHAR2,
1273 x_errMessage OUT NOCOPY VARCHAR2) IS
1274
1275 result NUMBER;
1276 message varchar2(80);
1277 l_returnStatus VARCHAR(1);
1278 l_params wip_logger.param_tbl_t;
1279 l_logLevel NUMBER := to_number(fnd_log.g_current_runtime_level);
1280 -- Fixed bug 5252677
1281 l_noMoveCount NUMBER;
1282 BEGIN
1283 if (l_logLevel <= wip_constants.trace_logging) then
1284 l_params(1).paramName := 'not printing params';
1285 l_params(1).paramValue := ' ';
1286 wip_logger.entryPoint(p_procName => 'wma_move.validate',
1287 p_params => l_params,
1288 x_returnStatus => l_returnStatus);
1289 end if;
1290
1291 -- check overcompletion tolerence if overcompletion
1292 IF (p_overcomplQty <> 0) THEN
1293 wip_overcompletion.check_tolerance(
1294 p_organization_id => p_orgID,
1295 p_wip_entity_id => p_jobID,
1296 p_primary_quantity => p_overcomplQty,
1297 p_result => result);
1298
1299 IF (result = WIP_CONSTANTS.NO) THEN
1300 -- exceed tolerance, set error message
1301 fnd_message.set_name ('WIP', 'WIP_OC_TOLERANCE_FAIL');
1302 x_errMessage := fnd_message.get;
1303 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
1304 if (l_logLevel <= wip_constants.trace_logging) then
1305 wip_logger.exitPoint(p_procName => 'wma_move.validate',
1306 p_procReturnStatus => x_returnStatus,
1307 p_msg => x_errMessage,
1308 x_returnStatus => l_returnStatus);
1309 end if;
1310 return;
1311 END IF;
1312 END IF;
1313
1314 -- Fixed bug 5252677. Check no move shopfloor status at from step.
1315 SELECT count(*)
1316 INTO l_noMoveCount
1317 FROM wip_shop_floor_status_codes wsc,
1318 wip_shop_floor_statuses ws
1319 WHERE wsc.organization_id = p_orgID
1320 AND ws.organization_id = wsc.organization_id
1321 AND ws.wip_entity_id = p_jobID
1322 AND ws.operation_seq_num = p_fmOp
1323 AND ws.intraoperation_step_type = p_fmStep
1324 AND ws.shop_floor_status_code = wsc.shop_floor_status_code
1325 AND wsc.status_move_flag = WIP_CONSTANTS.NO
1326 AND NVL(wsc.disable_date, SYSDATE + 1) > SYSDATE;
1327
1328 IF(l_noMoveCount <> 0)THEN
1329 -- From step has no move shopfloor status
1330 fnd_message.set_name ('WIP', 'WIP_STATUS_NO_TXN1');
1331 x_errMessage := fnd_message.get;
1332 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
1333 if (l_logLevel <= wip_constants.trace_logging) then
1334 wip_logger.exitPoint(p_procName => 'wma_move.validate',
1335 p_procReturnStatus => x_returnStatus,
1336 p_msg => x_errMessage,
1337 x_returnStatus => l_returnStatus);
1338 end if;
1339 return;
1340 END IF; -- l_noMoveCount <> 0
1341 -- End fix for bug 5252677.
1342
1343 -- check and make sure no shop floor statuses between the from and to
1344 IF (wip_sf_status.count_no_move_statuses(
1345 p_org_id => p_orgID,
1346 p_wip_id => p_jobID,
1347 p_line_id => null,
1348 p_sched_id => null,
1349 p_fm_op => p_fmOp,
1350 p_fm_step => p_fmStep,
1351 p_to_op => p_toOp,
1352 p_to_step => p_toStep) > 0) THEN
1353 -- There is no-move shop floor status in between
1354 fnd_message.set_name ('WIP', 'WIP_NO_MOVE_SF_STATUS_BETWEEN');
1355 x_errMessage := fnd_message.get;
1356 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
1357 if (l_logLevel <= wip_constants.trace_logging) then
1358 wip_logger.exitPoint(p_procName => 'wma_move.validate',
1359 p_procReturnStatus => x_returnStatus,
1360 p_msg => x_errMessage,
1361 x_returnStatus => l_returnStatus);
1362 end if;
1363 return;
1364 END IF;-- Check no move shopfloor statuses between the from and to
1365
1366 IF(p_fmOp < p_toOp AND
1367 p_toStep = WIP_CONSTANTS.QUEUE) THEN
1368 -- check osp related validation
1369 IF (wip_osp.checkOSP(p_orgID => p_orgID,
1370 p_wipEntityID => p_jobID,
1371 p_entityType => 1, -- Discrete
1372 p_fmOpSeqNum => p_fmOp,
1373 p_toOpSeqNum => p_toOp,
1374 p_toStep => p_toStep,
1375 p_userID => p_userID,
1376 x_msg => message,
1377 x_error => x_errMessage) = false) THEN
1378 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
1379 if (l_logLevel <= wip_constants.trace_logging) then
1380 wip_logger.exitPoint(p_procName => 'wma_move.validate',
1381 p_procReturnStatus => x_returnStatus,
1382 p_msg => x_errMessage,
1383 x_returnStatus => l_returnStatus);
1384 end if;
1385 return;
1386 END IF;
1387 END IF;
1388 x_returnStatus := fnd_api.g_ret_sts_success;
1389 if (l_logLevel <= wip_constants.trace_logging) then
1390 wip_logger.exitPoint(p_procName => 'wma_move.validate',
1391 p_procReturnStatus => x_returnStatus,
1392 p_msg => 'success',
1393 x_returnStatus => l_returnStatus);
1394 end if;
1395 EXCEPTION
1396 WHEN others THEN
1397 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
1398 x_errMessage := 'unexpected error: ' || SQLERRM;
1399 if (l_logLevel <= wip_constants.trace_logging) then
1400 wip_logger.exitPoint(p_procName => 'wma_move.validate',
1401 p_procReturnStatus => x_returnStatus,
1402 p_msg => x_errMessage,
1403 x_returnStatus => l_returnStatus);
1404 end if;
1405
1406 END validate;
1407
1408 /**
1409 * This function inserts into the MOVE_TXN_INTERFACE table with values
1410 * derived and minimally validated by prior validate call with data stored
1411 * in the moveRecord passed in parameter.
1412 * Parameters:
1413 * moveRecord The MoveTxnRec representing the row to be inserted.
1414 * Return:
1415 * boolean A flag indicating whether update successful or not.
1416 */
1417 Function put(moveRecord IN MoveTxnRec,
1418 errMessage IN OUT NOCOPY VARCHAR2)
1419 return boolean IS
1420 BEGIN
1421 insert into wip_move_txn_interface
1422 (group_id,
1423 source_code,
1424 transaction_id,
1425 last_update_date,
1426 last_updated_by,
1427 last_updated_by_name,
1428 creation_date, created_by,
1429 created_by_name,
1430 process_phase,
1431 process_status,
1432 transaction_type,
1433 organization_id,
1434 organization_code,
1435 wip_entity_id,
1436 wip_entity_name,
1437 entity_type,
1438 primary_item_id,
1439 line_id,
1440 line_code,
1441 transaction_date,
1442 acct_period_id,
1443 fm_operation_seq_num,
1444 fm_operation_code,
1445 fm_department_id,
1446 fm_department_code,
1447 fm_intraoperation_step_type,
1448 to_operation_seq_num,
1449 to_operation_code,
1450 to_department_id,
1451 to_department_code,
1452 to_intraoperation_step_type,
1453 transaction_quantity,
1454 transaction_uom,
1455 primary_quantity,
1456 primary_uom,
1457 scrap_account_id,
1458 reason_id,
1459 qa_collection_id,
1460 overcompletion_transaction_qty,
1461 overcompletion_primary_qty
1462 )
1463 values (moveRecord.row.group_id,
1464 moveRecord.row.source_code,
1465 moveRecord.row.transaction_id,
1466 moveRecord.row.last_update_date,
1467 moveRecord.row.last_updated_by,
1468 moveRecord.row.last_updated_by_name,
1469 moveRecord.row.creation_date,
1470 moveRecord.row.created_by,
1471 moveRecord.row.created_by_name,
1472 moveRecord.row.process_phase,
1473 moveRecord.row.process_status,
1474 moveRecord.row.transaction_type,
1475 moveRecord.row.organization_id,
1476 moveRecord.row.organization_code,
1477 moveRecord.row.wip_entity_id,
1478 moveRecord.row.wip_entity_name,
1479 moveRecord.row.entity_type,
1480 moveRecord.row.primary_item_id,
1481 moveRecord.row.line_id,
1482 moveRecord.row.line_code,
1483 moveRecord.row.transaction_date,
1484 moveRecord.row.acct_period_id,
1485 moveRecord.row.fm_operation_seq_num,
1486 moveRecord.row.fm_operation_code,
1487 moveRecord.row.fm_department_id,
1488 moveRecord.row.fm_department_code,
1489 moveRecord.row.fm_intraoperation_step_type,
1490 moveRecord.row.to_operation_seq_num,
1491 moveRecord.row.to_operation_code,
1492 moveRecord.row.to_department_id,
1493 moveRecord.row.to_department_code,
1494 moveRecord.row.to_intraoperation_step_type,
1495 moveRecord.row.transaction_quantity,
1496 moveRecord.row.transaction_uom,
1497 moveRecord.row.primary_quantity,
1498 moveRecord.row.primary_uom,
1499 moveRecord.row.scrap_account_id,
1500 moveRecord.row.reason_id,
1501 moveRecord.row.qa_collection_id,
1502 moveRecord.row.overcompletion_transaction_qty,
1503 moveRecord.row.overcompletion_primary_qty
1504 );
1505 return true;
1506
1507 EXCEPTION
1508 when others then
1509 fnd_message.set_name ('WIP', 'GENERIC_ERROR');
1510 fnd_message.set_token ('FUNCTION', 'wma_move.put');
1511 fnd_message.set_token ('ERROR', SQLCODE || ' ' || SQLERRM);
1512 errMessage := fnd_message.get;
1513 return false;
1514 END put;
1515
1516 /**
1517 * This function inserts into the WIP_SERIAL_MOVE_INTERFACE table with values
1518 * selected from wip_move_txn_interface table
1519 *
1520 * Parameters:
1521 * transactionID The transaction_id in wip_move_txn_interface table
1522 * serialNumber The serial number
1523 * Return:
1524 * boolean A flag indicating whether update successful or not.
1525 */
1526 Function insertSerial(groupID IN NUMBER,
1527 transactionID IN NUMBER,
1528 serialNumber IN VARCHAR2,
1529 errMessage IN OUT NOCOPY VARCHAR2)
1530 return boolean IS
1531 BEGIN
1532 insert into wip_serial_move_interface
1533 (transaction_id,
1534 assembly_serial_number,
1535 creation_date,
1536 created_by,
1537 created_by_name,
1538 last_update_date,
1539 last_updated_by,
1540 last_updated_by_name,
1541 last_update_login,
1542 request_id,
1543 program_application_id,
1544 program_id,
1545 program_update_date
1546 )
1547 select transaction_id,
1548 serialNumber,
1549 creation_date,
1550 created_by,
1551 created_by_name,
1552 last_update_date,
1553 last_updated_by,
1554 last_updated_by_name,
1555 last_update_login,
1556 request_id,
1557 program_application_id,
1558 program_id,
1559 program_update_date
1560 FROM wip_move_txn_interface wmti
1561 WHERE transaction_id = transactionID
1562 AND group_id = groupID;
1563 return true;
1564
1565 EXCEPTION
1566 when others then
1567 fnd_message.set_name ('WIP', 'GENERIC_ERROR');
1568 fnd_message.set_token ('FUNCTION', 'wma_move.insertSerial');
1569 fnd_message.set_token ('ERROR', SQLCODE || ' ' || SQLERRM);
1570 errMessage := fnd_message.get;
1571 return false;
1572 END insertSerial;
1573 END wma_move;