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