[Home] [Help]
PACKAGE BODY: APPS.WMA_COMPLETION
Source
1 PACKAGE BODY wma_completion AS
2 /* $Header: wmapcmpb.pls 120.4.12000000.3 2007/08/29 14:49:15 sisankar ship $ */
3
4 /**
5 * This procedure is the entry point into the Completion and Return
6 * Processing code for background processing.
7 * Parameters:
8 * parameters CmpParams contains values from the mobile form.
9 * status Indicates success (0), failure (-1).
10 * errMessage The error or warning message, if any.
11 */
12 PROCEDURE process(parameters IN CmpParams,
13 processInv IN VARCHAR2,
14 txnMode IN NUMBER,
15 status OUT NOCOPY NUMBER,
16 errMessage OUT NOCOPY VARCHAR2) IS
17 error VARCHAR2(2000); -- error message
18 errCode VARCHAR2(1);
19 cmpRecord CmpTxnRec; -- record to populate and insert
20 primaryCostMethod NUMBER;
21 msgCount NUMBER;
22 errNum NUMBER;
23 errMsg VARCHAR2(241);
24 retValue NUMBER;
25 returnStatus VARCHAR2(1);
26 labelStatus VARCHAR2(1);
27 dummy VARCHAR2(1);
28 l_msg_stack VARCHAR2(2000);
29 l_serialNum VARCHAR2(30);
30 l_paramTbl wip_logger.param_tbl_t;
31 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
32 l_txnMode NUMBER;
33 l_overCplRec wip_cplProc_priv.completion_rec_t;
34 BEGIN
35 savepoint wma_cmp_proc10;
36
37 if (l_logLevel <= wip_constants.trace_logging) then
38 --logging not fully supported in this package.
39 l_paramTbl(1).paramName := 'not printing params';
40 l_paramTbl(1).paramValue := ' ';
41 --just skip
42 wip_logger.entryPoint(p_procName => 'wma_completion.process',
43 p_params => l_paramTbl,
44 x_returnStatus => errCode);
45 end if;
46
47 status := 0;
48 if (l_logLevel <= wip_constants.full_logging) then
49 wip_logger.log('enter wmapcmpb.process. int id is ' || parameters.transactionIntID, errCode);
50 end if;
51
52 --if the caller has chosen to override the wip parameter, use that value, otherwise use the
53 --value in wip_parameters
54 l_txnMode := nvl(txnMode, wma_derive.getTxnMode(parameters.environment.orgID));
55
56 -- derive and validate all necessary fields for insertion
57 if (derive(cmpRecord, l_overCplRec, parameters, l_txnMode, error) = FALSE) then
58 -- process error
59 fnd_message.set_name('FND', 'FND_GENERIC_MESSAGE');
60 fnd_message.set_token('MESSAGE', error);
61 fnd_msg_pub.add;
62 if (l_logLevel <= wip_constants.full_logging) then
63 wip_logger.log('error from derive', errCode);
64 end if;
65 raise fnd_api.g_exc_unexpected_error;
66 end if;
67 if (l_logLevel <= wip_constants.full_logging) then
68 wip_logger.log('new tmp id is ' || cmpRecord.transaction_interface_id, errCode);
69 end if;
70
71 -- insert into the interface table for background processing
72 if (put(cmpRecord, errMessage) = FALSE) then
73 if (l_logLevel <= wip_constants.full_logging) then
74 wip_logger.log('error from put', errCode);
75 end if;
76 raise fnd_api.g_exc_unexpected_error;
77 end if;
78
79 select primary_cost_method
80 into primaryCostMethod
81 from mtl_parameters
82 where organization_id = parameters.environment.orgID;
83
84 --insert a row into cst_comp_snap_temp
85 /* Fix for bug 4252359. Do this only for non-LPN completions. WMS will
86 take care of LPN completions through bug 4059728*/
87 if(primaryCostMethod in (wip_constants.cost_avg,
88 wip_constants.cost_fifo,
89 wip_constants.cost_lifo)
90 AND parameters.lpnID IS NULL) then
91 retValue := CSTACOSN.op_snapshot(i_txn_temp_id => cmpRecord.transaction_interface_id,
92 err_num => errNum,
93 err_code => errMessage,
94 err_msg => errMessage);
95 if(retValue <> 1) then
96 fnd_message.set_name(application => 'CST',
97 name => 'CST_SNAPSHOT_FAILED');
98 fnd_msg_pub.add;
99 if (l_logLevel <= wip_constants.full_logging) then
100 wip_logger.log('error from cst', errMessage);
101 end if;
102 raise fnd_api.g_exc_unexpected_error;
103 end if;
104 end if;
105
106 wip_utilities.print_label(p_txn_id => cmpRecord.transaction_header_id,
107 p_table_type => 2, --MMTT
108 p_ret_status => returnStatus,
109 p_msg_count => msgCount,
110 p_msg_data => error,
111 p_label_status => labelStatus,
112 p_business_flow_code => 26); -- discrete business flow code
113 -- do not error out if label printing, only put warning message in log
114 if(returnStatus <> fnd_api.g_ret_sts_success) then
115 wip_utilities.get_message_stack(p_msg => l_msg_stack);
116 if (l_logLevel <= wip_constants.full_logging) then
117 wip_logger.log(p_msg => 'An error has occurred with label printing.\n' ||
118 'The following error has occurred during ' ||
119 'printing: ' || l_msg_stack || '\n' ||
120 'Please check the Inventory log file for more ' ||
121 'information.',
122 x_returnStatus =>dummy);
123 end if;
124 end if;
125 if (l_logLevel <= wip_constants.full_logging) then
126 wip_logger.log(p_msg => 'Label printing returned with status ' ||
127 returnStatus,
128 x_returnStatus => dummy);
129 end if;
130
131
132 if (l_logLevel <= wip_constants.full_logging) then
133 wip_logger.log('wma_completion.process: tmpID => ' || cmpRecord.transaction_interface_id, returnStatus);
134 end if;
135
136 --if a return from the serialized page, re-populate the wip_entity_id,
137 --intraoperation_seq_num, and intraoperation_step_type columns in MSN.
138 --select it here to get the serial number before it leaves the temp table.
139 if (parameters.isFromSerializedPage = wip_constants.yes and
140 parameters.transactionType = wip_constants.retassy_type) then
141
142 if(cmpRecord.item_lot_control_code = wip_constants.lot) then
143 select fm_serial_number
144 into l_serialNum
145 from mtl_serial_numbers_temp msnt, mtl_transaction_lots_temp mtlt
146 where mtlt.serial_transaction_temp_id = msnt.transaction_temp_id
147 and mtlt.transaction_temp_id = cmpRecord.transaction_interface_id;
148 else
149 select fm_serial_number
150 into l_serialNum
151 from mtl_serial_numbers_temp
152 where transaction_temp_id = cmpRecord.transaction_interface_id;
153 end if;
154 end if;
155
156 -- call wip_cplProc_priv.processOverCpl() if overomplete transaction and
157 -- transaction_mode is online.
158 if (parameters.overcomplete = true AND
159 l_txnMode = WIP_CONSTANTS.ONLINE) then
160 wip_cplProc_priv.processOverCpl(p_cplRec => l_overCplRec,
161 x_returnStatus => returnStatus);
162
163 if (l_logLevel <= wip_constants.full_logging) then
164 wip_logger.log('wma_completion.process: overcomplete retStatus => ' ||
165 returnStatus, dummy);
166 end if;
167
168 if(returnStatus <> fnd_api.g_ret_sts_success) then
169 raise fnd_api.g_exc_unexpected_error;
170 end if;
171 end if;
172
173 if(fnd_api.to_boolean(processInv)) then
174 wip_mtlTempProc_priv.processTemp(p_initMsgList => fnd_api.g_true,
175 p_txnHdrID => cmpRecord.transaction_header_id,
176 p_txnMode => l_txnMode,
177 p_destroyQtyTrees => fnd_api.g_true,
178 p_endDebug => fnd_api.g_false,
179 x_returnStatus => returnStatus,
180 x_errorMsg => errMessage);
181 else
182 wip_mtlTempProc_priv.processWIP(p_txnTmpID => cmpRecord.transaction_interface_id,
183 p_processLpn => fnd_api.g_true,
184 x_returnStatus => returnStatus,
185 x_errorMsg => errMessage);
186 end if;
187
188 if (l_logLevel <= wip_constants.full_logging) then
189 wip_logger.log('wma_completion.process: retStatus => ' || returnStatus, dummy);
190 end if;
191 if(returnStatus <> fnd_api.g_ret_sts_success) then
192 raise fnd_api.g_exc_unexpected_error;
193 end if;
194
195 --update to MSN must take place after inventory processing as they always clear the
196 --group mark id when processing a serial. Here we have to repopulate the group_mark_id,
197 --wip_entity_id, op_seq, and intra_op columns.
198 if (parameters.isFromSerializedPage = wip_constants.yes and
199 parameters.transactionType = wip_constants.retassy_type) then
200 if (l_logLevel <= wip_constants.full_logging) then
201 wip_logger.log('wma_completion.process: about to update serial', dummy);
202 end if;
203 if(cmpRecord.operation_seq_num = -1) then
204 wip_utilities.update_serial(p_serial_number => l_serialNum,
205 p_inventory_item_id => parameters.itemID,
206 p_organization_id => parameters.environment.orgID,
207 p_wip_entity_id => parameters.wipEntityID,
208 p_operation_seq_num => null,
209 p_intraoperation_step_type => null,
210 x_return_status => returnStatus);
211 else
212 wip_utilities.update_serial(p_serial_number => l_serialNum,
213 p_inventory_item_id => parameters.itemID,
214 p_organization_id => parameters.environment.orgID,
215 p_wip_entity_id => parameters.wipEntityID,
216 p_operation_seq_num => cmpRecord.operation_seq_num,
217 p_intraoperation_step_type => wip_constants.toMove,
218 x_return_status => returnStatus);
219 end if;
220 if (l_logLevel <= wip_constants.full_logging) then
221 wip_logger.log('wma_completion.process: serialization op retStatus => ' || returnStatus, dummy);
222 end if;
223 if(returnStatus <> fnd_api.g_ret_sts_success) then
224 if (l_logLevel <= wip_constants.full_logging) then
225 wip_logger.log('wma_completion.process: retStatus of serial update failure ' || returnStatus, dummy);
226 end if;
227 wip_utilities.get_message_stack(p_msg => errMessage);
228 raise fnd_api.g_exc_unexpected_error;
229 end if;
230 end if;
231 if (l_logLevel <= wip_constants.trace_logging) then
232 wip_logger.exitPoint(p_procName => 'wma_completion.process',
233 p_procReturnStatus => status,
234 p_msg => 'success',
235 x_returnStatus => dummy);
236 end if;
237 wip_logger.cleanUp(dummy);
238 EXCEPTION
239 when fnd_api.g_exc_unexpected_error then
240 status := -1;
241 if (l_logLevel <= wip_constants.trace_logging) then
242 wip_logger.exitPoint(p_procName => 'wma_completion.process',
243 p_procReturnStatus => status,
244 p_msg => 'failure',
245 x_returnStatus => dummy);
246 wip_logger.cleanUp(dummy);
247 end if;
248 rollback to wma_cmp_proc10;
249 when others then
250 status := -1;
251 if (l_logLevel <= wip_constants.trace_logging) then
252 wip_logger.exitPoint(p_procName => 'wma_completion.process',
253 p_procReturnStatus => status,
254 p_msg => 'exception',
255 x_returnStatus => dummy);
256 wip_logger.cleanUp(dummy);
257 end if;
258 rollback to wma_cmp_proc10;
259 returnStatus := fnd_api.g_ret_sts_unexp_error;
260 fnd_message.set_name ('WIP', 'GENERIC_ERROR');
261 fnd_message.set_token ('FUNCTION', 'wma_completion.process');
262 fnd_message.set_token ('ERROR', SQLCODE || ' ' || SQLERRM);
263 errMessage := fnd_message.get;
264 END process;
265
266 FUNCTION putIntoMMTT(lpnCmpRecord IN LpnCmpTxnRec,
267 txnTmpID OUT NOCOPY NUMBER,
268 errMessage OUT NOCOPY VARCHAR2) return boolean
269 is
270 begin
271 insert into mtl_material_transactions_temp
272 (transaction_header_id,
273 transaction_temp_id,
274 completion_transaction_id,
275 transaction_mode,
276 created_by,
277 creation_date,
278 last_update_date,
279 last_updated_by,
280 inventory_item_id,
281 transaction_quantity,
282 transaction_uom,
283 primary_quantity,
284 transaction_date,
285 organization_id,
286 acct_period_id,
287 transaction_action_id,
288 transaction_source_id,
289 transaction_source_type_id,
290 transaction_type_id,
291 wip_entity_type,
292 revision,
293 operation_seq_num,
294 process_flag,
295 posting_flag,
296 final_completion_flag,
297 qa_collection_id,
298 kanban_card_id,
299 lpn_id)
300 values (mtl_material_transactions_s.nextval,
301 mtl_material_transactions_s.nextval,
302 lpnCmpRecord.row.completion_transaction_id,
303 lpnCmpRecord.row.transaction_mode,
304 lpnCmpRecord.row.created_by,
305 lpnCmpRecord.row.creation_date,
306 lpnCmpRecord.row.last_update_date,
307 lpnCmpRecord.row.last_updated_by,
308 lpnCmpRecord.row.inventory_item_id,
309 lpnCmpRecord.row.transaction_quantity,
310 lpnCmpRecord.row.transaction_uom,
311 lpnCmpRecord.row.primary_quantity,
312 lpnCmpRecord.row.transaction_date,
313 lpnCmpRecord.row.organization_id,
314 lpnCmpRecord.row.acct_period_id,
315 lpnCmpRecord.row.transaction_action_id,
316 lpnCmpRecord.row.transaction_source_id,
317 lpnCmpRecord.row.transaction_source_type_id,
318 lpnCmpRecord.row.transaction_type_id,
319 lpnCmpRecord.row.wip_entity_type,
320 lpnCmpRecord.row.bom_revision,
321 lpnCmpRecord.row.operation_seq_num,
322 'Y',
323 'Y',
324 lpnCmpRecord.row.final_completion_flag,
325 lpnCmpRecord.row.qa_collection_id,
326 lpnCmpRecord.row.kanban_card_id,
327 lpnCmpRecord.row.lpn_id) returning transaction_temp_id into txnTmpID;
328 return true;
329 exception when others then
330 errMessage := SQLERRM;
331 return false;
332 end putIntoMMTT;
333
334 PROCEDURE process(parameters IN LpnCmpParams,
335 status OUT NOCOPY NUMBER,
336 errMessage OUT NOCOPY VARCHAR2,
337 cmpl_txnTmpId OUT NOCOPY NUMBER) IS -- Added for Bug 6013398.
338 error VARCHAR2(2000); -- error message
339 lpnCmpRecord LpnCmpTxnRec; -- record to populate and insert
340 l_txnHdrID NUMBER;
341 l_txnTmpID NUMBER;
342 l_retStatus VARCHAR2(1);
343 /* new variables for bug 4253002 */
344 msgCount NUMBER;
345 labelStatus VARCHAR2(1);
346 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
347 l_msg_stack VARCHAR2(2000);
348 dummy VARCHAR2(1);
349 BEGIN
350 savepoint wmapcmpb_proc1000;
351
352 status := 0;
353
354 -- derive and validate all necessary fields for insertion
355 if (derive(lpnCmpRecord, parameters, error) = FALSE) then
356 -- process error
357 rollback to wmapcmpb_proc1000;
358 status := -1;
359 errMessage := error;
360 return;
361 end if;
362
363 --put a dummy record into MMTT...
364 if(putIntoMMTT(lpnCmpRecord, l_txnTmpID, error) = FALSE) then
365 rollback to wmapcmpb_proc1000;
366 status := -1;
367 errMessage := error;
368 return;
369 end if;
370
371 /* Start: Bug 6013398.
372 Start of fix for bug 4253002
373 select transaction_header_id
374 into l_txnHdrID
375 from mtl_material_transactions_temp
376 where transaction_temp_id = l_txnTmpID;
377
378 wip_utilities.print_label(p_txn_id => l_txnTmpID,
379 p_table_type => 2, --MMTT
380 p_ret_status => l_retStatus,
381 p_msg_count => msgCount,
382 p_msg_data => error,
383 p_label_status => labelStatus,
384 p_business_flow_code => 26); -- discrete business flow code
385 -- do not error out if label printing, only put warning message in log
386 if(l_retStatus <> fnd_api.g_ret_sts_success) then
387 wip_utilities.get_message_stack(p_msg => l_msg_stack);
388 if (l_logLevel <= wip_constants.full_logging) then
389 wip_logger.log(p_msg => 'An error has occurred with label printing.\n' ||
390 'The following error has occurred during ' ||
391 'printing: ' || l_msg_stack || '\n' ||
392 'Please check the Inventory log file for more ' ||
393 'information.',
394 x_returnStatus =>dummy);
395 end if;
396 end if;
397 if (l_logLevel <= wip_constants.full_logging) then
398 wip_logger.log(p_msg => 'Label printing returned with status ' ||
399 l_retStatus,
400 x_returnStatus => dummy);
401 end if;
402 Assign value to out parameter and remove the label printing code.
403 Label printing code has been moved to calling procedure wipopsrb.lpnCompleteJob
404 End of fix for bug 4253002 */
405 cmpl_txnTmpID := l_txnTmpID;
406 --End: Bug 6013398.
407 --use it to do WIP processing...
408 wip_mtlTempProc_priv.processWIP(p_txnTmpID => l_txnTmpID,
409 p_processLpn => fnd_api.g_true,
410 x_returnStatus => l_retStatus,
411 x_errorMsg => error);
412 if(l_retStatus <> fnd_api.g_ret_sts_success) then
413 rollback to wmapcmpb_proc1000;
414 status := -1;
415 errMessage := error;
416 return;
417 end if;
418
419 /* commented out for bug 6354507.
420 --and delete it
421 delete mtl_material_transactions_temp
422 where transaction_temp_id = l_txnTmpID; */
423
424 -- insert into the wip table
425 if (put(lpnCmpRecord, error) = FALSE) then
426 -- process error
427 status := -1;
428 errMessage := error;
429 return;
430 end if;
431
432 EXCEPTION
433 when others then
434 rollback to wmapcmpb_proc1000;
435 status := -1;
436 fnd_message.set_name ('WIP', 'GENERIC_ERROR');
437 fnd_message.set_token ('FUNCTION', 'wma_completion.process');
438 fnd_message.set_token ('ERROR', SQLERRM);
439 errMessage := fnd_message.get;
440
441 END process;
442
443 /**
444 * This function derives and validates the values necessary for executing a
445 * completion or return transaction. Given the form parameters, it populates
446 * cmpRecord preparing it to be inserted into the interface table.
447 * Parameters:
448 * cmpRecord record to be populated. The minimum number of fields to
449 * execute the transaction successfully are populated
450 * overCplRec record to be used by wip_cplProc_priv.processOverCpl()
451 * parameters completion or return mobile form parameters
452 * errMessage populated if an error occurrs
453 * Return:
454 * boolean flag indicating the successful derivation of necessary values
455 * HISTORY:
456 * 02-MAR-2006 spondalu ER 4163405: Derived demandSourceHeaderID and demandSourceLineID
457 * from parameters and populated cmpRecord with the same. Also,
458 * restricted call to checkQuantity() to completion transactions only.
459 *
460 */
461 Function derive(cmpRecord IN OUT NOCOPY CmpTxnRec,
462 overCplRec IN OUT NOCOPY wip_cplProc_priv.completion_rec_t,
463 parameters IN CmpParams,
464 txnMode IN NUMBER,
465 errMessage IN OUT NOCOPY VARCHAR2)
466 return boolean IS
467 item wma_common.Item;
468 job wma_common.Job;
469 lastOpSeq NUMBER;
470 periodID NUMBER;
471 availableQty NUMBER;
472 openPastPeriod BOOLEAN := false;
473 primaryCostMethod NUMBER;
474 l_dummy VARCHAR2(1);
475 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
476 BEGIN
477 if (l_logLevel <= wip_constants.full_logging) then
478 wip_logger.log('enter wmapcmpb.derive', l_dummy);
479 end if;
480
481 -- get the job info
482 job := wma_derive.getJob(parameters.wipEntityID);
483 if (job.wipEntityID is null) then
484 fnd_message.set_name ('WIP', 'WIP_JOB_DOES_NOT_EXIST');
485 fnd_message.set_token('INTERFACE', 'wma_completion.derive', TRUE);
486 errMessage := fnd_message.get;
487 return false;
488 end if;
489
490 select primary_cost_method
491 into primaryCostMethod
492 from mtl_parameters
493 where organization_id = parameters.environment.orgID;
494
495
496 --may have to populate the txn temp id for costing.
497 --the conditional population is probably not necessary, need
498 --to check with inv
499 if(parameters.transactionIntID is null or parameters.transactionIntID <= 0) then
500 if (l_logLevel <= wip_constants.full_logging) then
501 wip_logger.log('wmapcmpb.derive: selecting new int id', l_dummy);
502 end if;
503 select mtl_material_transactions_s.nextval
504 into cmpRecord.transaction_interface_id
505 from dual;
506 else
507 if (l_logLevel <= wip_constants.full_logging) then
508 wip_logger.log('wmapcmpb.derive: using old int id', l_dummy);
509 end if;
510 cmpRecord.transaction_interface_id := parameters.transactionIntID;
511 end if;
512
513 /* ER 4163405: Restricting quantity check to completion only */
514 if (parameters.transactionType = WIP_CONSTANTS.CPLASSY_TYPE) then
515 -- validate transaction quantity.
516 if (checkQuantity(parameters, job, errMessage) = false) then
517 if (l_logLevel <= wip_constants.full_logging) then
518 wip_logger.log('wmapcmpb.derive: chkQty returns false', l_dummy);
519 end if;
520 return false;
521 end if;
522 end if;
523
524 if (l_logLevel <= wip_constants.full_logging) then
525 wip_logger.log('wmapcmpb.derive: about to call pjm', l_dummy);
526 wip_logger.log('org:' || parameters.environment.orgID, l_dummy);
527 wip_logger.log('loc:' || parameters.locatorID, l_dummy);
528 wip_logger.log('prj:' || job.projectID, l_dummy);
529 wip_logger.log('tsk:' || job.taskID, l_dummy);
530 end if;
531
532 -- check the project reference
533 if (pjm_project_locator.check_project_references(
534 parameters.environment.orgID,
535 parameters.locatorID,
536 'SPECIFIC',
537 'N',
538 job.projectID,
539 job.taskID) = false) then
540 errMessage := fnd_message.get;
541 if (l_logLevel <= wip_constants.full_logging) then
542 wip_logger.log('wmapcmpb.derive: pjm_project_locator returns false', l_dummy);
543 end if;
544 return false;
545 end if;
546
547 if (l_logLevel <= wip_constants.full_logging) then
548 wip_logger.log('wmapcmpb.derive: about to call getItem', l_dummy);
549 end if;
550
551 -- get the item info
552 item := wma_derive.getItem(parameters.itemID,
553 parameters.environment.orgID,
554 parameters.locatorID);
555 if (item.invItemID is null) then
556 fnd_message.set_name ('WIP', 'WIP_ITEM_DOES_NOT_EXIST');
557 errMessage := fnd_message.get;
558 if (l_logLevel <= wip_constants.full_logging) then
559 wip_logger.log('wmapcmpb.derive: wma_derive.getItem returns false', l_dummy);
560 end if;
561 return false;
562 end if;
563
564 -- get the item revision
565 cmpRecord.revision := null;
566
567 if (l_logLevel <= wip_constants.full_logging) then
568 wip_logger.log('wmapcmpb.derive: about to call getRev', l_dummy);
569 end if;
570
571 if (item.revQtyControlCode = WIP_CONSTANTS.REVISION_CONTROLLED) then
572 if(NOT wma_completion.getRevision(
573 wipEntityID => parameters.wipEntityID,
574 orgID => parameters.environment.orgID,
575 itemID => parameters.itemID,
576 revision => cmpRecord.revision)) then
577 errMessage := substr(fnd_message.get,1,241);
578 if (l_logLevel <= wip_constants.full_logging) then
579 wip_logger.log('wmapcmpb.derive: wma_completion.getRevision returns false', l_dummy);
580 end if;
581 return false;
582 end if; -- getRevision
583 end if; -- revQtyControlCode = WIP_CONSTANTS.REVISION_CONTROLLED
584
585 if (l_logLevel <= wip_constants.full_logging) then
586 wip_logger.log('wmapcmpb.derive: about to call tdatechk', l_dummy);
587 end if;
588
589 -- get the accounting period
590 invttmtx.tdatechk(
591 org_id => parameters.environment.orgID,
592 transaction_date => sysdate,
593 period_id => periodID,
594 open_past_period => openPastPeriod);
595
596 if (periodID = -1 or periodID = 0) then
597 fnd_message.set_name(
598 application => 'INV',
599 name => 'INV_NO_OPEN_PERIOD');
600 errMessage := fnd_message.get;
601 if (l_logLevel <= wip_constants.full_logging) then
602 wip_logger.log('wmapcmpb.derive: tdatechk returns false', l_dummy);
603 end if;
604 return false;
605 end if;
606
607
608 -- get the last operation sequence
609 lastOpSeq := getLastOpSeq (job);
610
611
612 -- set the quantity and the action id depending on the transaction type
613 if (parameters.transactionType = WIP_CONSTANTS.CPLASSY_TYPE) then
614 cmpRecord.transaction_action_id := WIP_CONSTANTS.CPLASSY_ACTION;
615 cmpRecord.transaction_quantity := parameters.transactionQty;
616 else -- return transaction
617 cmpRecord.transaction_action_id := WIP_CONSTANTS.RETASSY_ACTION;
618 cmpRecord.transaction_quantity := parameters.transactionQty * -1;
619 end if;
620 -- primary quantity is always equal to transaction quantity
621 cmpRecord.primary_quantity := cmpRecord.transaction_quantity;
622
623 -- derive and set the rest of the mandatory fields in cmpRecord
624 cmpRecord.transaction_type_id := parameters.transactionType;
625 cmpRecord.transaction_source_id := parameters.wipEntityID;
626 cmpRecord.transaction_source_type_id := INV_RESERVATION_GLOBAL.G_SOURCE_TYPE_WIP;
627 cmpRecord.transaction_header_id := parameters.transactionHeaderID;
628 cmpRecord.completion_transaction_id := parameters.cmpTransactionID;
629 cmpRecord.created_by := parameters.environment.userID;
630 cmpRecord.creation_date := sysdate;
631 if ( parameters.isFromSerializedPage = 1 ) then
632 cmpRecord.source_code := wma_common.SERIALIZATION_SOURCE_CODE;
633 else
634 cmpRecord.source_code := wma_common.SOURCE_CODE;
635 end if;
636 cmpRecord.source_header_id := -1;
637 cmpRecord.source_line_id := -1;
638 -- cmpRecord.lock_flag := 'N';
639 cmpRecord.inventory_item_id := parameters.itemID;
640 cmpRecord.subinventory_code := parameters.subinv;
641 cmpRecord.transaction_uom := parameters.transactionUOM;
642 cmpRecord.transaction_date := sysdate;
643 cmpRecord.organization_id := parameters.environment.orgID;
644 cmpRecord.acct_period_id := periodID;
645 cmpRecord.last_update_date := sysdate;
646 cmpRecord.last_updated_by := parameters.environment.userID;
647 cmpRecord.wip_entity_type := WIP_CONSTANTS.DISCRETE;
648 cmpRecord.locator_id := parameters.locatorID;
649
650 if (parameters.demandSourceHeaderID = 0) then
651 cmpRecord.demand_source_header_id := NULL;
652 else
653 cmpRecord.demand_source_header_id := parameters.demandSourceHeaderID; /* ER 4163405 */
654 end if;
655
656 if (parameters.demandSourceLineID = 0) then
657 cmpRecord.demand_source_line_id := NULL;
658 else
659 cmpRecord.demand_source_line_id := parameters.demandSourceLineID;
660 end if;
661
662 --do not use online processing mode as it will disable over-cpl
663 --processing in the processor (due to desktop forms logic)
664 if(txnMode = wip_constants.online) then
665 cmpRecord.transaction_mode := wip_constants.online;
666 else
667 cmpRecord.transaction_mode := txnMode;
668 end if;
669
670 cmpRecord.operation_seq_num := lastOpSeq;
671
672 cmpRecord.process_flag := wip_constants.mti_inventory;
673
674
675 -- cmpRecord.posting_flag := 'Y';
676 cmpRecord.item_lot_control_code := item.lotControlCode;
677 -- cmpRecord.item_serial_control_code := item.serialNumberControlCode;
678 -- cmpRecord.item_segments := item.itemName;
679 -- cmpRecord.locator_segments := parameters.locatorName;
680 -- cmpRecord.item_inventory_asset_flag := item.invAssetFlag;
681 -- cmpRecord.item_description := item.description;
682 -- cmpRecord.next_lot_number := item.startAutoLotNumber;
683 -- cmpRecord.lot_alpha_prefix := item.autoLotAlphaPrefix;
684 -- cmpRecord.item_primary_uom_code := item.primaryUOMCode;
685 -- cmpRecord.item_revision_qty_control_code := item.revQtyControlCode;
686 -- cmpRecord.item_restrict_locators_code := item.restrictLocatorsCode;
687 -- cmpRecord.item_location_control_code := item.locationControlCode;
688 -- cmpRecord.item_shelf_life_code := item.shelfLifeCode;
689 -- cmpRecord.item_shelf_life_days := item.shelfLifeDays;
690 -- cmpRecord.item_restrict_subinv_code := item.restrictSubinvCode;
691 -- cmpRecord.next_serial_number := item.startAutoSerialNumber;
692 -- cmpRecord.serial_alpha_prefix := item.autoSerialAlfaPrefix;
693 -- cmpRecord.allowed_units_lookup_code := item.allowedUnitsLookupCode;
694
695 /* Fix for bug 4588479; fp 4496088:
696 Setting final_completion_flag to NULL as this
697 will be determined by INV TM before inserting into MMTT. Reverted
698 fix for bug 4115120 */
699 cmpRecord.final_completion_flag := null; -- setting this to null for now
700 -- cmpRecord.source_project_id := job.projectID;
701 -- cmpRecord.source_task_id := job.taskID;
702
703 cmpRecord.project_id := item.projectID;
704 cmpRecord.task_id := item.taskID;
705 cmpRecord.qa_collection_id := parameters.qualityID;
706 cmpRecord.kanban_card_id := parameters.kanbanCardID;
707 cmpRecord.lpn_id := parameters.lpnID;
708 cmpRecord.move_transaction_id := parameters.movTransactionID;
709
710 if (parameters.overcomplete = true) then
711 -- generate an overcompletion txn id only if the assembly has a routing
712 availableQty := getAvailableQty (job);
713 cmpRecord.overcompletion_transaction_qty :=
714 parameters.transactionQty - availableQty;
715 cmpRecord.overcompletion_primary_qty :=
716 parameters.transactionQty - availableQty;
717
718 -- set the value of the record wip_cplProc_priv.completion_rec_t
719 overCplRec.wipEntityType := WIP_CONSTANTS.DISCRETE;
720 overCplRec.wipEntityID := cmpRecord.transaction_source_id;
721 overCplRec.orgID := cmpRecord.organization_id;
722 overCplRec.repLineID := null; -- only used for repetitive
723 overCplRec.itemID := cmpRecord.inventory_item_id;
724 overCplRec.txnActionID := cmpRecord.transaction_action_id;
725 overCplRec.priQty := cmpRecord.transaction_quantity;
726 overCplRec.txnQty := cmpRecord.transaction_quantity;
727 overCplRec.txnDate := cmpRecord.transaction_date;
728 overCplRec.cplTxnID := cmpRecord.completion_transaction_id;
729 overCplRec.movTxnID := cmpRecord.move_transaction_id;
730 overCplRec.kanbanCardID := cmpRecord.kanban_card_id;
731 overCplRec.qaCollectionID := cmpRecord.qa_collection_id;
732 overCplRec.lastOpSeq := cmpRecord.operation_seq_num;
733 overCplRec.revision := cmpRecord.revision;
734 overCplRec.mtlAlcTmpID := null; -- only used for repetitive
735 overCplRec.txnHdrID := cmpRecord.transaction_header_id;
736 overCplRec.txnStatus := null;
737 overCplRec.overCplPriQty := cmpRecord.overcompletion_primary_qty;
738 overCplRec.lastUpdBy := cmpRecord.last_updated_by;
739 overCplRec.createdBy := cmpRecord.created_by;
740 overCplRec.lpnID := null; -- only used for LPN
741 overCplRec.txnMode := cmpRecord.transaction_mode;
742 -- generate overcompletion_transaction_id because move processor need it
743 -- to determine whether we should update quantity at Queue of the first
744 -- operation or not.
745 select wip_transactions_s.nextval
746 into overCplRec.overCplTxnID
747 from dual;
748 end if; -- overcompletion
749
750 return true;
751
752 EXCEPTION
753 when others then
754 fnd_message.set_name ('WIP', 'GENERIC_ERROR');
755 fnd_message.set_token ('FUNCTION', 'wma_completion.derive');
756 fnd_message.set_token ('ERROR', SQLCODE || ' ' || SQLERRM);
757 errMessage := fnd_message.get;
758 return false;
759 END derive;
760
761
762 Function derive(LpnCmpRecord IN OUT NOCOPY LpnCmpTxnRec,
763 parameters IN LpnCmpParams,
764 errMessage IN OUT NOCOPY VARCHAR2)
765 return boolean IS
766 item wma_common.Item;
767 job wma_common.Job;
768 lastOpSeq NUMBER;
769 periodID NUMBER;
770 availableQty NUMBER;
771 openPastPeriod BOOLEAN := false;
772 BEGIN
773
774 -- get the job info
775 job := wma_derive.getJob(parameters.wipEntityID);
776 if (job.wipEntityID is null) then
777 fnd_message.set_name ('WIP', 'WIP_JOB_DOES_NOT_EXIST');
778 fnd_message.set_token('INTERFACE', 'wma_completion.derive', TRUE);
779 errMessage := fnd_message.get;
780 return false;
781 end if;
782
783 -- take the quantity check off. The quantity has been checked before calling
784 -- the on line processor. If we call it again here, since the quantity completed
785 -- is already updated, it will error out. For P1 2324566.
786 -- availableQty := getAvailableQty (job);
787 -- if (checkQuantity(parameters.environment.orgID,
788 -- parameters.wipEntityID,
789 -- parameters.overcomplete,
790 -- parameters.transactionTypeID,
791 -- parameters.transactionQty,
792 -- availableQty,
793 -- job.quantityCompleted,
794 -- errMessage) = false) then
795 -- return false;
796 -- end if;
797
798 -- check the project reference
799 if (pjm_project_locator.check_project_references(
800 parameters.environment.orgID,
801 parameters.locatorID,
802 'SPECIFIC',
803 'N',
804 job.projectID,
805 job.taskID) = false) then
806 errMessage := fnd_message.get;
807 return false;
808 end if;
809
810 -- get the item info
811 item := wma_derive.getItem(parameters.itemID,
812 parameters.environment.orgID,
813 parameters.locatorID);
814 if (item.invItemID is null) then
815 fnd_message.set_name ('WIP', 'WIP_ITEM_DOES_NOT_EXIST');
816 errMessage := fnd_message.get;
817 return false;
818 end if;
819 -- get the item revision
820 LpnCmpRecord.row.bom_revision := null;
821 if (item.revQtyControlCode = WIP_CONSTANTS.REVISION_CONTROLLED) then
822 if(NOT wma_completion.getRevision(
823 wipEntityID => parameters.wipEntityID,
824 orgID => parameters.environment.orgID,
825 itemID => parameters.itemID,
826 revision => lpnCmpRecord.row.bom_revision)) then
827 errMessage := substr(fnd_message.get, 1, 241);
828 return false;
829 end if; -- getRevision
830 end if; -- revQtyControlCode = WIP_CONSTANTS.REVISION_CONTROLLED
831
832 -- get the accounting period
833 invttmtx.tdatechk(
834 org_id => parameters.environment.orgID,
835 transaction_date => sysdate,
836 period_id => periodID,
837 open_past_period => openPastPeriod);
838
839 if (periodID = -1 or periodID = 0) then
840 fnd_message.set_name(
841 application => 'INV',
842 name => 'INV_NO_OPEN_PERIOD');
843 errMessage := fnd_message.get;
844 return false;
845 end if;
846
847 -- get the last operation sequence
848 lastOpSeq := getLastOpSeq (job);
849
850 -- set the quantity and the action id depending on the transaction type
851 if (parameters.transactionTypeID = WIP_CONSTANTS.CPLASSY_TYPE) then
852 lpnCmpRecord.row.transaction_action_id := WIP_CONSTANTS.CPLASSY_ACTION;
853 lpnCmpRecord.row.transaction_quantity := parameters.transactionQty;
854 /* if (parameters.overcomplete = true) then --never happens?
855 -- generate an overcompletion txn id only if the assembly has a routing
856 if (lastOpSeq <> -1) then
857 lpnCmpRecord.row.overcompletion_transaction_id := wma_derive.getNextVal('wip_transactions_s');
858 end if;
859 availableQty := getAvailableQty (job);
860 lpnCmpRecord.row.overcompletion_transaction_qty := parameters.transactionQty - availableQty;
861 lpnCmpRecord.row.overcompletion_primary_qty := parameters.transactionQty - availableQty;
862 end if; -- overcompletion
863 */
864 else -- return transaction
865 lpnCmpRecord.row.transaction_action_id := WIP_CONSTANTS.RETASSY_ACTION;
866 lpnCmpRecord.row.transaction_quantity := parameters.transactionQty * -1;
867 end if;
868
869 -- primary quantity is always equal to transaction quantity
870 lpnCmpRecord.row.primary_quantity := lpnCmpRecord.row.transaction_quantity;
871
872 -- derive and set the rest of the mandatory fields in lpnCmpRecord
873 lpnCmpRecord.row.transaction_type_id := parameters.transactionTypeID;
874 lpnCmpRecord.row.transaction_source_id := parameters.wipEntityID;
875 lpnCmpRecord.row.transaction_source_type_id := 5;
876 lpnCmpRecord.row.header_id := parameters.headerID;
877
878 --do not use online as online will prevent over-completion processing
879 lpnCmpRecord.row.transaction_mode := wip_constants.online;
880
881
882 lpnCmpRecord.row.created_by := parameters.environment.userID;
883 lpnCmpRecord.row.creation_date := sysdate;
884 lpnCmpRecord.row.lock_flag := 'N';
885 lpnCmpRecord.row.inventory_item_id := parameters.itemID;
886 lpnCmpRecord.row.subinventory_code := parameters.subinv;
887 lpnCmpRecord.row.transaction_uom := parameters.transactionUOM;
888 lpnCmpRecord.row.transaction_date := sysdate;
889 lpnCmpRecord.row.organization_id := parameters.environment.orgID;
890 lpnCmpRecord.row.acct_period_id := periodID;
891 lpnCmpRecord.row.last_update_date := sysdate;
892 lpnCmpRecord.row.last_updated_by := parameters.environment.userID;
893 lpnCmpRecord.row.wip_entity_id := parameters.wipEntityID;
894 lpnCmpRecord.row.wip_entity_type := WIP_CONSTANTS.DISCRETE;
895 lpnCmpRecord.row.locator_id := parameters.locatorID;
896 lpnCmpRecord.row.operation_seq_num := lastOpSeq;
897 -- lpnCmpRecord.row.item_description := item.description;
898 lpnCmpRecord.row.qa_collection_id := parameters.qualityID;
899 lpnCmpRecord.row.kanban_card_id := parameters.kanbanCardID;
900 lpnCmpRecord.row.lpn_id := parameters.lpnID;
901 lpnCmpRecord.row.final_completion_flag := null;
902 -- lpnCmpRecord.row.job_project_id := job.projectID;--formerly source_project_id
903 -- lpnCmpRecord.row.job_task_id := job.taskID;--formerly source_task_id
904 lpnCmpRecord.row.item_project_id := item.projectID; --formerly project_id
905 lpnCmpRecord.row.item_task_id := item.taskID;
906 lpnCmpRecord.row.end_item_unit_number := job.endItemUnitNumber;
907 lpnCmpRecord.row.completion_transaction_id := parameters.completionTxnID;
908 return true;
909
910 EXCEPTION
911 when others then
912 fnd_message.set_name ('WIP', 'GENERIC_ERROR');
913 fnd_message.set_token ('FUNCTION', 'wma_completion.derive');
914 fnd_message.set_token ('ERROR', SQLCODE || ' ' || SQLERRM);
915 errMessage := fnd_message.get;
916 return false;
917 END derive;
918
919 /**
920 * checks the transaction quantity entered by the user.
921 * The transaction quantity should not exceed the available quantity
922 * If overcompleting, however, the transaction quantity should
923 * be greater than the available quantity, nd should not exceed the
924 * overcompletion tolerance. In the case of return, the transaction
925 * should not exceed the completed quantity.
926 * If the structures passed to this procedure are not available, use the
927 * overloaded version.
928 * Parameters:
929 * parameters completion or return mobile form parameters
930 * job job to check transaction quantity against
931 * Returns:
932 * boolean flag indicating if validation is successful
933 */
934 FUNCTION checkQuantity (parameters IN CmpParams,
935 job IN wma_common.Job,
936 errMessage IN OUT NOCOPY VARCHAR2) return boolean
937 IS
938 availableQty NUMBER;
939 status BOOLEAN;
940 BEGIN
941 availableQty := getAvailableQty (job);
942
943 status := checkQuantity (parameters.environment.orgID,
944 parameters.wipEntityID,
945 parameters.overcomplete,
946 parameters.transactionType,
947 parameters.transactionQty,
948 availableQty,
949 job.quantityCompleted,
950 errMessage);
951 return status;
952 EXCEPTION
953 when others then
954 return false;
955 END checkQuantity;
956
957
958 /**
959 * checks the transaction quantity entered by the user.
960 * The transaction quantity should not exceed the available quantity
961 * If overcompleting, however, the transaction quantity should
962 * be greater than the available quantity, nd should not exceed the
963 * overcompletion tolerance. In the case of return, the transaction
964 * should not exceed the completed quantity.
965 * Parameters:
966 * orgID the organization job belongs to
967 * wipEntityID the job ID used to check the overcompletion tolerance
968 * overcomplete flag to indicate if user chose to overcomplete
969 * transactionType either WIP_CONSTANTS.CPLASSY_TYPE or RETASSY_TYPE
970 * transactionQty the quantity to transact
971 * availableQty the quantity availabe to the job
972 * completedQty the job quantity completed
973 * Returns:
974 * boolean flag indicating if validation is successful
975 * HISTORY:
976 * 02-MAR-2006 spondalu ER 4163405: Changed the logic in this function.
977 * Transaction quantity was compared with available qty
978 * only during completion. Generalized this for both
979 * completion and return. Changed message to make it
980 * relevant for both completions and returns.
981 *
982 */
983 FUNCTION checkQuantity (orgID IN NUMBER,
984 wipEntityID IN NUMBER,
985 overcomplete IN BOOLEAN,
986 transactionType IN NUMBER,
987 transactionQty IN NUMBER,
988 availableQty IN NUMBER,
989 completedQty IN NUMBER,
990 errMessage IN OUT NOCOPY VARCHAR2) return boolean
991 IS
992 result NUMBER;
993 ocQtyToCheck NUMBER;
994 l_returnStatus VARCHAR2(1);
995 l_params wip_logger.param_tbl_t;
996 l_logLevel NUMBER := to_number(fnd_log.g_current_runtime_level);
997 /* Fixed bug 3693148 */
998 l_job_type NUMBER;
999 BEGIN
1000 if (l_logLevel <= wip_constants.trace_logging) then
1001 l_params(1).paramName := 'not printing params';
1002 l_params(1).paramValue := ' ';
1003 wip_logger.entryPoint(p_procName => 'wma_completion.checkQuantity',
1004 p_params => l_params,
1005 x_returnStatus => l_returnStatus);
1006 end if;
1007
1008 if (transactionQty <= 0) then
1009 fnd_message.set_name ('INV', 'INV_GREATER_THAN_ZERO');
1010 errMessage := fnd_message.get;
1011 if (l_logLevel <= wip_constants.trace_logging) then
1012 wip_logger.exitPoint(p_procName => 'wma_completion.checkQuantity',
1013 p_procReturnStatus => -1,
1014 p_msg => errMessage,
1015 x_returnStatus => l_returnStatus);
1016 end if;
1017 return false;
1018 end if;
1019 if (overcomplete = true) then
1020 -- validate overcompletion quantity
1021 if (transactionQty <= availableQty) then
1022 fnd_message.set_name ('WIP', 'WIP_GREATER_THAN');
1023 fnd_message.set_token('ENTITY1', 'TRANSACTION QUANTITY-CAP', TRUE);
1024 fnd_message.set_token('ENTITY2', 'QTY AVAIL TO COMPLETE', TRUE);
1025 errMessage := fnd_message.get;
1026 if (l_logLevel <= wip_constants.trace_logging) then
1027 wip_logger.exitPoint(p_procName => 'wma_completion.checkQuantity',
1028 p_procReturnStatus => -1,
1029 p_msg => errMessage,
1030 x_returnStatus => l_returnStatus);
1031 end if;
1032 return false;
1033 end if;
1034 -- check overcompletion tolerance
1035 if (getLastOpSeq (wipEntityID, orgID) = -1) then
1036 ocQtyToCheck := transactionQty;
1037 else -- the job has a routing
1038 ocQtyToCheck := transactionQty - availableQty;
1039 end if;
1040 wip_overcompletion.check_tolerance(
1041 p_organization_id => orgID,
1042 p_wip_entity_id => wipEntityID,
1043 p_primary_quantity => ocQtyToCheck,
1044 p_result => result);
1045 if (result = wip_constants.no) then -- quantity exceeds tolerance
1046 fnd_message.set_name ('WIP', 'WIP_OC_TOLERANCE_FAIL');
1047 errMessage := fnd_message.get;
1048 if (l_logLevel <= wip_constants.trace_logging) then
1049 wip_logger.exitPoint(p_procName => 'wma_completion.checkQuantity',
1050 p_procReturnStatus => -1,
1051 p_msg => errMessage,
1052 x_returnStatus => l_returnStatus);
1053 end if;
1054 return false;
1055 end if;
1056 else -- parameters.overcomplete = false
1057 -- validate completion quantity
1058 /* ER 4163405: Checking whether transactionQty is greater than availableQty was
1059 being made only for completion transaction. Removed the if-condition. Now,
1060 this will be checked for both completion and return. Changed message to be more
1061 suitable for both completion and return */
1062 if (transactionQty > availableQty) then
1063 fnd_message.set_name ('INV', 'INV_QTY_LESS_OR_EQUAL');
1064 errMessage := fnd_message.get;
1065 if (l_logLevel <= wip_constants.trace_logging) then
1066 wip_logger.exitPoint(p_procName => 'wma_completion.checkQuantity',
1067 p_procReturnStatus => -1,
1068 p_msg => errMessage,
1069 x_returnStatus => l_returnStatus);
1070 end if;
1071 return false;
1072 end if;
1073
1074 select job_type
1075 into l_job_type
1076 from wip_discrete_jobs
1077 where wip_entity_id = wipEntityID
1078 and organization_id = orgID;
1079
1080 /* ER 4163405: Re-add the condition here for return transactions only */
1081 if (transactionType = wip_constants.retassy_type) then
1082
1083 -- validate return quantity
1084 /* Fixed bug 3693148. We should allow overreturn for non-standard job*/
1085 if (l_job_type = WIP_CONSTANTS.STANDARD AND
1086 transactionQty > completedQty) then
1087 fnd_message.set_name ('WIP', 'WIP_LESS_OR_EQUAL');
1088 fnd_message.set_token('ENTITY1', 'TOTAL TXN QTY-CAP', TRUE);
1089 fnd_message.set_token('ENTITY2', 'JOB COMPLETE QUANTITY', TRUE);
1090 errMessage := fnd_message.get;
1091 if (l_logLevel <= wip_constants.trace_logging) then
1092 wip_logger.exitPoint(p_procName => 'wma_completion.checkQuantity',
1093 p_procReturnStatus => -1,
1094 p_msg => errMessage,
1095 x_returnStatus => l_returnStatus);
1096 end if;
1097 return false;
1098 end if;
1099 end if;
1100 end if; -- quantity validation
1101 if (l_logLevel <= wip_constants.trace_logging) then
1102 wip_logger.exitPoint(p_procName => 'wma_completion.checkQuantity',
1103 p_procReturnStatus => 0,
1104 p_msg => 'success',
1105 x_returnStatus => l_returnStatus);
1106 end if;
1107 return true;
1108 EXCEPTION
1109 when others then
1110 return false;
1111 END checkQuantity;
1112
1113
1114 /**
1115 * Check whether exceeds overcompletion tolerance or not.
1116 */
1117 procedure checkOverCpl(p_orgID in number,
1118 p_wipEntityID in number,
1119 p_overCplQty in number,
1120 x_returnStatus out nocopy varchar2,
1121 x_errMessage out nocopy varchar2) is
1122 result number;
1123 l_returnStatus VARCHAR2(1);
1124 l_params wip_logger.param_tbl_t;
1125 l_logLevel NUMBER := to_number(fnd_log.g_current_runtime_level);
1126 begin
1127 if (l_logLevel <= wip_constants.trace_logging) then
1128 l_params(1).paramName := 'not printing params';
1129 l_params(1).paramValue := ' ';
1130 wip_logger.entryPoint(p_procName => 'wma_completion.checkOverCpl',
1131 p_params => l_params,
1132 x_returnStatus => l_returnStatus);
1133 end if;
1134
1135 x_returnStatus := fnd_api.g_ret_sts_success;
1136 if ( p_overCplQty > 0 ) then
1137 wip_overcompletion.check_tolerance(
1138 p_organization_id => p_orgID,
1139 p_wip_entity_id => p_wipEntityID,
1140 p_primary_quantity => p_overCplQty,
1141 p_result => result);
1142
1143 if (result = WIP_CONSTANTS.NO) then
1144 -- exceed tolerance, set error message
1145 fnd_message.set_name ('WIP', 'WIP_OC_TOLERANCE_FAIL');
1146 x_errMessage := fnd_message.get;
1147 x_returnStatus := fnd_api.g_ret_sts_error;
1148 end if;
1149 end if;
1150
1151 if (l_logLevel <= wip_constants.trace_logging) then
1152 wip_logger.exitPoint(p_procName => 'wma_completion.checkOverCpl',
1153 p_procReturnStatus => 0,
1154 p_msg => x_errMessage,
1155 x_returnStatus => l_returnStatus);
1156 end if;
1157 EXCEPTION
1158 when others then
1159 x_returnStatus := fnd_api.g_ret_sts_error;
1160 if (l_logLevel <= wip_constants.trace_logging) then
1161 wip_logger.exitPoint(p_procName => 'wma_completion.checkOverCpl',
1162 p_procReturnStatus => -1,
1163 p_msg => x_errMessage,
1164 x_returnStatus => l_returnStatus);
1165 end if;
1166 end checkOverCpl;
1167
1168
1169 /**
1170 * given a Job, getLastOpSeq() gets the last operation sequence
1171 * associated with the job if the job has a routing. If the job
1172 * does not have a routing, -1 is returned.
1173 * Parameters:
1174 * job job to get the last operation sequence for.
1175 * Returns:
1176 * number the last operation sequence. -1 if it doesn't exist.
1177 */
1178 FUNCTION getLastOpSeq (job IN wma_common.Job) return number
1179 IS
1180 lastOpSeq NUMBER;
1181 BEGIN
1182 lastOpSeq := getLastOpSeq (job.wipEntityID, job.organizationID);
1183
1184 return lastOpSeq;
1185
1186 END getLastOpSeq;
1187
1188
1189 /**
1190 * given a wipEntityID and an orgID, getLastOpSeq() gets the last
1191 * operation sequence associated with the job if the job has a
1192 * routing. If the job does not have a routing, -1 is returned.
1193 * Parameters:
1194 * wipEntityID the wip_entity_id of the job
1195 * orgID the organization job belongs to.
1196 * Returns:
1197 * number the last operation sequence. -1 if it doesn't exist.
1198 */
1199 FUNCTION getLastOpSeq (wipEntityID IN NUMBER,
1200 orgID IN NUMBER) return number
1201 IS
1202 lastOpSeq NUMBER;
1203
1204 cursor getLastOpSeq (wipEntityID NUMBER, orgID NUMBER) IS
1205 select max(wo.operation_seq_num)
1206 from wip_operations wo
1207 where wo.organization_id = orgID
1208 and wo.wip_entity_id = wipEntityID;
1209 BEGIN
1210
1211 open getLastOpSeq (wipEntityID, orgID);
1212 fetch getLastOpSeq into lastOpSeq;
1213 if (lastOpSeq is null) then
1214 lastOpSeq := -1;
1215 end if;
1216 close getLastOpSeq;
1217
1218 return lastOpSeq;
1219
1220 END getLastOpSeq;
1221
1222
1223 /**
1224 * given a job, getAvilableQty() returns the quantity in the
1225 * To Move step of the final operation if the job has a routing.
1226 * If the job, does not have a routing, getAvailableQty() computes
1227 * the available quantity to complete from the job quantities.
1228 * Parameters:
1229 * job job to find the available quantity for.
1230 * Returns:
1231 * number the available quantity in the job.
1232 */
1233 FUNCTION getAvailableQty (job IN wma_common.Job) return number
1234 IS
1235 lastOpSeq NUMBER;
1236 availableQty NUMBER;
1237
1238 cursor getAvailableQty (v_wipEntityID NUMBER, v_orgID NUMBER, v_lastOpSeq NUMBER) IS
1239 select wo.quantity_waiting_to_move
1240 from wip_operations wo
1241 where wo.organization_id = v_orgID
1242 and wo.wip_entity_id = v_wipEntityID
1243 and wo.operation_seq_num = v_lastOpSeq;
1244 BEGIN
1245
1246 -- get the avilable quantity for the job
1247 lastOpSeq := getLastOpSeq (job);
1248 if (lastOpSeq = -1) then -- if the job does not have a routing
1249 availableQty :=
1250 job.startQuantity - job.quantityCompleted - job.quantityScrapped;
1251 if (availableQty < 0) then -- can happen if previously overcompleted
1252 availableQty := 0;
1253 end if;
1254 else
1255 open getAvailableQty (job.wipEntityID, job.organizationID, lastOpSeq);
1256 fetch getAvailableQty into availableQty;
1257 close getAvailableQty;
1258 end if;
1259
1260 return availableQty;
1261
1262 END getAvailableQty;
1263
1264
1265 /**
1266 * Inserts a populated CmpTxnRec into MTI
1267 * Parameters:
1268 * cmpRecord The CmpTxnRec representing the row to be inserted.
1269 * errMessage populated if an error occurrs
1270 * Return:
1271 * boolean A flag indicating whether table update was successful or not.
1272 * HISTORY:
1273 * 02-MAR-2006 spondalu ER 4163405: populating demandSourceHeaderID and
1274 * demandSourceLineID from CmpTxnRec into MTI.
1275 *
1276 */
1277 Function put(cmpRecord CmpTxnRec, errMessage IN OUT NOCOPY VARCHAR2)
1278 return boolean IS
1279 l_dummy VARCHAR2(1);
1280 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
1281 l_rowID rowid;
1282 l_retStatus VARCHAR2(1);
1283 BEGIN
1284 if (l_logLevel <= wip_constants.full_logging) then
1285 wip_logger.log('before insert', l_dummy);
1286 wip_logger.log('before insert item' || cmpRecord.inventory_item_id, l_dummy);
1287 wip_logger.log('before insert org' || cmpRecord.organization_id, l_dummy);
1288 wip_logger.log('before insert subinv' || cmpRecord.subinventory_code, l_dummy);
1289 wip_logger.log('before insert loc' || cmpRecord.locator_id, l_dummy);
1290 wip_logger.log('before insert action' || cmpRecord.transaction_action_id, l_dummy);
1291 wip_logger.log('before insert movTxnID ' || cmpRecord.move_transaction_id, l_dummy);
1292 wip_logger.log('before insert demandsourceheaderID ' || cmpRecord.demand_source_header_id, l_dummy);
1293 wip_logger.log('before insert demandsourcelineID ' || cmpRecord.demand_source_line_id, l_dummy);
1294 end if;
1295
1296 insert into mtl_transactions_interface
1297 (transaction_header_id,
1298 completion_transaction_id,
1299 move_transaction_id,
1300 transaction_mode,
1301 created_by,
1302 creation_date,
1303 source_code,
1304 source_header_id,
1305 source_line_id,
1306 -- lock_flag,
1307 inventory_item_id,
1308 subinventory_code,
1309 transaction_quantity,
1310 transaction_uom,
1311 primary_quantity,
1312 transaction_date,
1313 organization_id,
1314 acct_period_id,
1315 last_update_date,
1316 last_updated_by,
1317 transaction_action_id,
1318 transaction_source_id,
1319 transaction_source_type_id,
1320 transaction_type_id,
1321 wip_entity_type,
1322 revision,
1323 locator_id,
1324 operation_seq_num,
1325 transaction_interface_id,
1326 process_flag,
1327 final_completion_flag,
1328 source_project_id,
1329 source_task_id,
1330 project_id,
1331 task_id,
1332 qa_collection_id,
1333 overcompletion_transaction_id,
1334 overcompletion_transaction_qty,
1335 overcompletion_primary_qty,
1336 kanban_card_id,
1337 lpn_id,
1338 transaction_batch_id,
1339 transaction_batch_seq,
1340 demand_source_header_id,
1341 demand_source_line)
1342 values (cmpRecord.transaction_header_id,
1343 cmpRecord.completion_transaction_id,
1344 cmpRecord.move_transaction_id,
1345 cmpRecord.transaction_mode,
1346 cmpRecord.created_by,
1347 cmpRecord.creation_date,
1348 cmpRecord.source_code,
1349 cmpRecord.source_header_id,
1350 cmpRecord.source_line_id,
1351 -- cmpRecord.lock_flag,
1352 cmpRecord.inventory_item_id,
1353 cmpRecord.subinventory_code,
1354 cmpRecord.transaction_quantity,
1355 cmpRecord.transaction_uom,
1356 cmpRecord.primary_quantity,
1357 cmpRecord.transaction_date,
1358 cmpRecord.organization_id,
1359 cmpRecord.acct_period_id,
1360 cmpRecord.last_update_date,
1361 cmpRecord.last_updated_by,
1362 cmpRecord.transaction_action_id,
1363 cmpRecord.transaction_source_id,
1364 cmpRecord.transaction_source_type_id,
1365 cmpRecord.transaction_type_id,
1366 cmpRecord.wip_entity_type,
1367 cmpRecord.revision,
1368 cmpRecord.locator_id,
1369 cmpRecord.operation_seq_num,
1370 cmpRecord.transaction_interface_id,
1371 cmpRecord.process_flag,
1372 cmpRecord.final_completion_flag,
1373 cmpRecord.source_project_id,
1374 cmpRecord.source_task_id,
1375 cmpRecord.project_id,
1376 cmpRecord.task_id,
1377 cmpRecord.qa_collection_id,
1378 cmpRecord.overcompletion_transaction_id,
1379 cmpRecord.overcompletion_transaction_qty,
1380 cmpRecord.overcompletion_primary_qty,
1381 cmpRecord.kanban_card_id,
1382 cmpRecord.lpn_id,
1383 cmpRecord.transaction_header_id,
1384 wip_constants.ASSY_BATCH_SEQ,
1385 cmpRecord.demand_source_header_id,
1386 cmpRecord.demand_source_line_id);
1387 if (l_logLevel <= wip_constants.full_logging) then
1388 wip_logger.log('after insert', l_dummy);
1389 end if;
1390
1391
1392 wip_mtlTempProc_priv.validateInterfaceTxns(p_txnHdrID => cmpRecord.transaction_header_id,
1393 p_initMsgList => fnd_api.g_true,
1394 x_returnStatus => l_retStatus);
1395
1396 if(l_retStatus <> fnd_api.g_ret_sts_success) then
1397 if (l_logLevel <= wip_constants.full_logging) then
1398 wip_logger.log('error from validateInterfaceTxns', l_retStatus);
1399 end if;
1400 wip_utilities.get_message_stack(p_msg => errMessage);
1401 return false;
1402 end if;
1403 return true;
1404 EXCEPTION
1405 when others then
1406 if (l_logLevel <= wip_constants.full_logging) then
1407 wip_logger.log('put failed: ' || SQLERRM, l_dummy);
1408 end if;
1409 fnd_message.set_name ('WIP', 'GENERIC_ERROR');
1410 fnd_message.set_token ('FUNCTION', 'wma_completion.put');
1411 fnd_message.set_token ('ERROR', SQLCODE || ' ' || SQLERRM);
1412 errMessage := fnd_message.get;
1413 return false;
1414 END put;
1415
1416
1417 /**
1418 * Inserts a populated CmpTxnRec into wip_lpn_completions
1419 * Parameters:
1420 * lpnCmpRecord The LpnCmpTxnRec representing the row to be inserted.
1421 * errMessage populated if an error occurrs
1422 * Return:
1423 * boolean A flag indicating whether table update was successful or not.
1424 */
1425 Function put(lpnCmpRecord LpnCmpTxnRec, errMessage IN OUT NOCOPY VARCHAR2)
1426 return boolean IS
1427 BEGIN
1428
1429 insert into wip_lpn_completions
1430 (header_id, source_id, source_code,
1431 transaction_mode, created_by,
1432 creation_date, lock_flag,
1433 inventory_item_id, subinventory_code,
1434 transaction_quantity, transaction_uom,
1435 primary_quantity, transaction_date,
1436 organization_id, acct_period_id,
1437 last_update_date, last_updated_by,
1438 transaction_action_id, transaction_source_id,
1439 transaction_source_type_id, transaction_type_id,
1440 wip_entity_id, wip_entity_type, bom_revision,
1441 locator_id, operation_seq_num, item_project_id, item_task_id,
1442 qa_collection_id, kanban_card_id, lpn_id,
1443 end_item_unit_number, completion_transaction_id)
1444 values (lpnCmpRecord.row.header_id,
1445 lpnCmpRecord.row.header_id,
1446 WMA_COMMON.SOURCE_CODE,
1447 lpnCmpRecord.row.transaction_mode,
1448 lpnCmpRecord.row.created_by,
1449 lpnCmpRecord.row.creation_date,
1450 lpnCmpRecord.row.lock_flag,
1451 lpnCmpRecord.row.inventory_item_id,
1452 lpnCmpRecord.row.subinventory_code,
1453 lpnCmpRecord.row.transaction_quantity,
1454 lpnCmpRecord.row.transaction_uom,
1455 lpnCmpRecord.row.primary_quantity,
1456 lpnCmpRecord.row.transaction_date,
1457 lpnCmpRecord.row.organization_id,
1458 lpnCmpRecord.row.acct_period_id,
1459 lpnCmpRecord.row.last_update_date,
1460 lpnCmpRecord.row.last_updated_by,
1461 lpnCmpRecord.row.transaction_action_id,
1462 lpnCmpRecord.row.transaction_source_id,
1463 lpnCmpRecord.row.transaction_source_type_id,
1464 lpnCmpRecord.row.transaction_type_id,
1465 lpnCmpRecord.row.wip_entity_id,
1466 lpnCmpRecord.row.wip_entity_type,
1467 lpnCmpRecord.row.bom_revision,
1468 lpnCmpRecord.row.locator_id,
1469 lpnCmpRecord.row.operation_seq_num,
1470 lpnCmpRecord.row.item_project_id,
1471 --lpnCmpRecord.row.job_project_id,
1472 lpnCmpRecord.row.item_task_id,
1473 --lpnCmpRecord.row.job_task_id,
1474 lpnCmpRecord.row.qa_collection_id,
1475 lpnCmpRecord.row.kanban_card_id,
1476 lpnCmpRecord.row.lpn_id,
1477 lpnCmpRecord.row.end_item_unit_number,
1478 lpnCmpRecord.row.completion_transaction_id);
1479
1480 return true;
1481
1482 EXCEPTION
1483 when others then
1484 fnd_message.set_name ('WIP', 'GENERIC_ERROR');
1485 fnd_message.set_token ('FUNCTION', 'wma_completion.put');
1486 fnd_message.set_token ('ERROR', SQLCODE || ' ' || SQLERRM);
1487 errMessage := fnd_message.get;
1488 return false;
1489 END put;
1490
1491 procedure backflush(p_jobID IN NUMBER,
1492 p_orgID IN NUMBER,
1493 p_cplQty IN NUMBER,
1494 p_overCplQty IN NUMBER,
1495 p_cplTxnID IN NUMBER,
1496 p_movTxnID IN NUMBER,
1497 p_txnDate IN DATE,
1498 p_txnHdrID IN NUMBER,
1499 p_txnMode IN NUMBER := null,
1500 p_objectID in number,
1501 x_lotEntryType OUT NOCOPY NUMBER,
1502 x_compInfo OUT NOCOPY system.wip_lot_serial_obj_t,
1503 x_returnStatus OUT NOCOPY VARCHAR2,
1504 x_errMessage OUT NOCOPY VARCHAR2) IS
1505 l_txnMode NUMBER;
1506 l_minOpSeqNum NUMBER;
1507 l_maxOpSeqNum NUMBER;
1508 l_assyPullStatus VARCHAR2(1) := fnd_api.g_ret_sts_success;
1509 l_opPullStatus VARCHAR2(1) := fnd_api.g_ret_sts_success;
1510 l_compTbl system.wip_component_tbl_t;
1511 l_returnStatus VARCHAR2(1);
1512 l_params wip_logger.param_tbl_t;
1513 l_logLevel NUMBER := to_number(fnd_log.g_current_runtime_level);
1514 begin
1515 if (l_logLevel <= wip_constants.trace_logging) then
1516 l_params(1).paramName := 'not printing params';
1517 l_params(1).paramValue := ' ';
1518 wip_logger.entryPoint(p_procName => 'wma_completion.backflush',
1519 p_params => l_params,
1520 x_returnStatus => l_returnStatus);
1521 end if;
1522
1523 l_txnMode := nvl(p_txnMode, wma_derive.getTxnMode(p_orgID));
1524
1525 select backflush_lot_entry_type
1526 into x_lotEntryType
1527 from wip_parameters
1528 where organization_id = p_orgID;
1529
1530 --change from verify all to exceptions only if in background mode
1531 --this is b/c in background mode, component l/s information should
1532 --not be enterable by the user
1533 if(l_txnMode = wip_constants.background) then
1534 if(x_lotEntryType = wip_constants.recdate_full) then
1535 x_lotEntryType := wip_constants.recdate_exc;
1536 elsif(x_lotEntryType = wip_constants.expdate_full) then
1537 x_lotEntryType := wip_constants.expdate_exc;
1538 end if;
1539 end if;
1540
1541 select nvl(min(operation_seq_num), 1), nvl(max(operation_seq_num), 1)
1542 into l_minOpSeqNum, l_maxOpSeqNum
1543 from wip_operations
1544 where wip_entity_id = p_jobID;
1545
1546 l_compTbl := system.wip_component_tbl_t();
1547
1548 --online. need to backflush components
1549 wip_bflProc_priv.processRequirements(p_wipEntityID => p_jobID,
1550 p_wipEntityType => wip_constants.discrete,
1551 p_cplTxnID => p_cplTxnID,
1552 p_orgID => p_orgID,
1553 p_assyQty => p_cplQty,
1554 p_txnDate => p_txnDate,
1555 p_wipSupplyType => wip_constants.assy_pull,
1556 p_txnHdrID => p_txnHdrID,
1557 p_firstOp => -1,
1558 p_lastOp => l_maxOpSeqNum,
1559 p_mergeMode => fnd_api.g_false,
1560 p_initMsgList => fnd_api.g_true,
1561 p_endDebug => fnd_api.g_true,
1562 p_mtlTxnMode => l_txnMode,
1563 x_compTbl => l_compTbl,
1564 x_returnStatus => x_returnStatus);
1565 if(x_returnStatus <> fnd_api.g_ret_sts_success) then
1566 raise fnd_api.g_exc_unexpected_error;
1567 end if;
1568
1569 if(p_overCplQty > 0) then --backflush move components if any exist
1570 wip_bflProc_priv.processRequirements(p_wipEntityID => p_jobID,
1571 p_wipEntityType => wip_constants.discrete,
1572 p_movTxnID => p_movTxnID,
1573 p_orgID => p_orgID,
1574 p_assyQty => p_overCplQty,
1575 p_txnDate => p_txnDate,
1576 p_wipSupplyType => wip_constants.op_pull,
1577 p_txnHdrID => p_txnHdrID,
1578 p_firstMoveOp => l_minOpSeqNum,
1579 p_lastMoveOp => l_maxOpSeqNum,
1580 p_firstOp => -1,
1581 p_lastOp => l_maxOpSeqNum,
1582 p_mergeMode => fnd_api.g_false,
1583 p_initMsgList => fnd_api.g_false,
1584 p_endDebug => fnd_api.g_true,
1585 p_mtlTxnMode => l_txnMode,
1586 x_compTbl => l_compTbl,
1587 x_returnStatus => x_returnStatus);
1588
1589 if(x_returnStatus <> fnd_api.g_ret_sts_success) then
1590 raise fnd_api.g_exc_unexpected_error;
1591 end if;
1592 end if;
1593
1594 x_compInfo := system.wip_lot_serial_obj_t(null, null, null, l_compTbl, null, null);
1595 x_compInfo.initialize;
1596
1597 wip_autoLotProc_priv.deriveLots(x_compLots => x_compInfo,
1598 p_orgID => p_orgID,
1599 p_wipEntityID => p_jobID,
1600 p_initMsgList => fnd_api.g_false,
1601 p_endDebug => fnd_api.g_true,
1602 p_destroyTrees => fnd_api.g_true,
1603 p_treeMode => inv_quantity_tree_pvt.g_reservation_mode,
1604 p_treeSrcName => null,
1605 x_returnStatus => x_returnStatus);
1606
1607 --if there is missing l/s info for a background txn, error out.
1608 --note that we are ignoring the serialization derivation below b/c
1609 --serialized pages are only online...
1610 if(x_returnStatus = fnd_api.g_ret_sts_error and
1611 l_txnMode = wip_constants.background) then
1612 fnd_message.set_name('WIP', 'WIP_NO_LS_COMP_IN_BKGND');
1613 fnd_msg_pub.add;
1614 raise fnd_api.g_exc_unexpected_error;
1615 end if;
1616
1617 if(x_returnStatus = fnd_api.g_ret_sts_unexp_error) then
1618 raise fnd_api.g_exc_unexpected_error;
1619 end if;
1620
1621 -- derive serial for serialized transaction. We can just check p_objectID.
1622 -- If p_objectID is -1, don't need to call deriveSerial. Otherwise call
1623 -- the API below.
1624 if ( p_objectID <> -1 ) then
1625 wip_autoSerialProc_priv.deriveSerial(x_compLots => x_compInfo,
1626 p_orgID => p_orgID,
1627 p_objectID => p_objectID,
1628 p_initMsgList => fnd_api.g_true,
1629 x_returnStatus => x_returnStatus);
1630 if ( x_returnStatus = fnd_api.g_ret_sts_unexp_error ) then
1631 raise fnd_api.g_exc_unexpected_error;
1632 end if;
1633 end if;
1634
1635 if(l_txnMode = wip_constants.background) then
1636 x_returnStatus := fnd_api.g_ret_sts_success;
1637 if (l_logLevel <= wip_constants.trace_logging) then
1638 wip_logger.exitPoint(p_procName => 'wma_completion.backflush',
1639 p_procReturnStatus => x_returnStatus,
1640 p_msg => x_errMessage,
1641 x_returnStatus => l_returnStatus);
1642 end if;
1643 return; --do nothing for now
1644 end if;
1645
1646 if (l_logLevel <= wip_constants.trace_logging) then
1647 wip_logger.exitPoint(p_procName => 'wma_completion.backflush',
1648 p_procReturnStatus => x_returnStatus,
1649 p_msg => 'success',
1650 x_returnStatus => l_returnStatus);
1651 end if;
1652 exception
1653 when fnd_api.g_exc_unexpected_error then
1654 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
1655 x_errMessage := fnd_msg_pub.get(p_encoded => 'F');
1656 if (l_logLevel <= wip_constants.trace_logging) then
1657 wip_logger.exitPoint(p_procName => 'wma_completion.backflush',
1658 p_procReturnStatus => x_returnStatus,
1659 p_msg => x_errMessage,
1660 x_returnStatus => l_returnStatus);
1661 end if;
1662 rollback;
1663 when others then
1664 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
1665 x_errMessage := SQLERRM;
1666 if (l_logLevel <= wip_constants.trace_logging) then
1667 wip_logger.exitPoint(p_procName => 'wma_completion.backflush',
1668 p_procReturnStatus => x_returnStatus,
1669 p_msg => x_errMessage,
1670 x_returnStatus => l_returnStatus);
1671 end if;
1672 rollback;
1673 end backflush;
1674
1675 /**
1676 * given a wipEntityID, orgID and itemID, getRevision() will validate
1677 * whether bom_revision exists as an item_revision or not. If not
1678 * return false. Otherwise return true. If bom_revision is null, derive
1679 * it based on the transaction_date specified. In mobile it is sysdate.
1680 * Parameters:
1681 * wipEntityID the wip_entity_id of the job
1682 * orgID the organization job belongs to.
1683 * itemID the assembly item ID
1684 * revision bom_revision if bom_revision exists as an item_revision
1685 * if bom_revision is null, derive it based on txn_date
1686 * Returns:
1687 * true if bom_revision exist as an item_revision. Otherwise return false
1688 */
1689 FUNCTION getRevision (wipEntityID IN NUMBER,
1690 orgID IN NUMBER,
1691 itemID IN NUMBER,
1692 revision OUT NOCOPY VARCHAR2) return boolean IS
1693
1694 BEGIN
1695 SELECT NVL(wdj.bom_revision, bom_revisions.get_item_revision_fn
1696 ('EXCLUDE_OPEN_HOLD', -- eco_status
1697 'ALL', -- examine_type
1698 orgID, -- org_id
1699 itemID, -- item_id
1700 sysdate -- rev_date
1701 ))
1702 INTO revision
1703 FROM wip_discrete_jobs wdj,
1704 mtl_item_revisions mir
1705 WHERE wdj.organization_id = mir.organization_id
1706 AND wdj.wip_entity_id = wipEntityID
1707 AND mir.organization_id = orgID
1708 AND mir.inventory_item_id = itemID
1709 AND (mir.revision =
1710 NVL(wdj.bom_revision, bom_revisions.get_item_revision_fn
1711 ('EXCLUDE_OPEN_HOLD', -- eco_status
1712 'ALL', -- examine_type
1713 orgID, -- org_id
1714 itemID, -- item_id
1715 sysdate -- rev_date
1716 )));
1717 return true;
1718 EXCEPTION
1719 WHEN others THEN
1720 fnd_message.set_name('WIP', 'WIP_BOM_ITEM_REVISION');
1721 return false;
1722 END getRevision;
1723 end wma_completion;