[Home] [Help]
58: if(fnd_api.to_boolean(p_initMsgList)) then
59: fnd_msg_pub.initialize;
60: end if;
61:
62: if (l_logLevel <= wip_constants.trace_logging) then
63: l_params(1).paramName := 'p_txnTmpID';
64: l_params(1).paramValue := p_txnTmpID;
65: wip_logger.entryPoint(p_procName => 'wip_cplProc_priv.processTemp',
66: p_params => l_params,
145: end if;
146:
147: --if the txn mode is online, the form does the over-cpl move as it must do one move for all the
148: --cpl records in the multi-row block. In all other cases, the move is done here.
149: if(l_cplRec.txnMode <> wip_constants.online) then
150: processOverCpl(p_cplRec => l_cplRec,
151: x_returnStatus => x_returnStatus);
152: end if;
153:
156: raise fnd_api.g_exc_unexpected_error;
157: end if;
158: end if;
159:
160: if(l_cplRec.wipEntityType = wip_constants.repetitive) then
161: processRepetitive(p_cplRec => l_cplRec,
162: p_txnTmpID => p_txnTmpID,
163: x_returnStatus => x_returnStatus);
164:
198: raise fnd_api.g_exc_unexpected_error;
199: end if;
200: end if;
201:
202: if(l_logLevel <= wip_constants.trace_logging) then
203: wip_logger.exitPoint(p_procName => 'wip_cplProc_priv.processTemp',
204: p_procReturnStatus => x_returnStatus,
205: p_msg => 'procedure success.',
206: x_returnStatus => l_returnStatus); --discard logging return status
211: exception
212: when no_data_found then
213: rollback to wipcplpb20;
214: x_returnStatus := fnd_api.g_ret_sts_unexp_error;
215: if (l_logLevel <= wip_constants.trace_logging) then
216: wip_logger.exitPoint(p_procName => 'wip_cplProc_priv.processTemp',
217: p_procReturnStatus => x_returnStatus,
218: p_msg => 'no data found',
219: x_returnStatus => l_returnStatus); --discard logging return status
222: wip_logger.cleanUp(x_returnStatus => l_returnStatus); --discard logging return status
223: end if;
224: fnd_message.set_name('WIP', 'INVALID_MMTT_TEMP_ID');
225: fnd_msg_pub.add;
226: if (l_logLevel <= wip_constants.full_logging) then
227: wip_logger.log(SQLERRM, l_returnStatus);
228: end if;
229: when fnd_api.g_exc_error then --could not derive all lot/serial info for components
230: --do *not* rollback. leave the component records in mmtt/mtlt for the caller to query/complete
228: end if;
229: when fnd_api.g_exc_error then --could not derive all lot/serial info for components
230: --do *not* rollback. leave the component records in mmtt/mtlt for the caller to query/complete
231: --when the record is processed again, only the material processing and inv txn will occur
232: if (l_logLevel <= wip_constants.trace_logging) then
233: wip_logger.exitPoint(p_procName => 'wip_cplProc_priv.processTemp',
234: p_procReturnStatus => x_returnStatus,
235: p_msg => 'need to collect l/s info',
236: x_returnStatus => l_returnStatus); --discard logging return status
239: wip_logger.cleanUp(x_returnStatus => l_returnStatus); --discard logging return status
240: end if;
241: when fnd_api.g_exc_unexpected_error then
242: rollback to wipcplpb20;
243: if (l_logLevel <= wip_constants.trace_logging) then
244: wip_logger.exitPoint(p_procName => 'wip_cplProc_priv.processTemp',
245: p_procReturnStatus => x_returnStatus,
246: p_msg => l_errMsg,
247: x_returnStatus => l_returnStatus); --discard logging return status
254: x_returnStatus := fnd_api.g_ret_sts_unexp_error;
255: fnd_msg_pub.add_exc_msg(p_pkg_name => 'wip_cplProc_priv',
256: p_procedure_name => 'processTemp',
257: p_error_text => SQLERRM);
258: if (l_logLevel <= wip_constants.trace_logging) then
259: wip_logger.exitPoint(p_procName => 'wip_cplProc_priv.processTemp',
260: p_procReturnStatus => x_returnStatus,
261: p_msg => 'unexpected error: ' || SQLERRM,
262: x_returnStatus => l_returnStatus); --discard logging return status
289: -- Fixed bug 3678776. We should allow user to overreturn assembly back
290: -- to non-standard job.
291: l_jobType NUMBER;
292: begin
293: if (l_logLevel <= wip_constants.trace_logging) then
294: fillCplParamTbl(p_cplRec => p_cplRec,
295: x_params => l_params);
296: l_paramCount := l_params.count;
297: l_params(l_paramCount + 1).paramName := 'p_txnTmpID';
320: from wip_discrete_jobs
321: where wip_entity_id = p_cplRec.wipEntityID
322: for update of quantity_completed nowait;
323:
324: if(p_cplRec.txnActionID = wip_constants.cplassy_action) then
325: if(p_cplRec.priQty >= l_qtyAvailToComplete) then
326: l_jobStatus := wip_constants.comp_chrg;
327: l_cplDate := nvl(l_cplDate, p_cplRec.txnDate); --Bug 4864403 Only change date_completed if it is null
328: end if;
322: for update of quantity_completed nowait;
323:
324: if(p_cplRec.txnActionID = wip_constants.cplassy_action) then
325: if(p_cplRec.priQty >= l_qtyAvailToComplete) then
326: l_jobStatus := wip_constants.comp_chrg;
327: l_cplDate := nvl(l_cplDate, p_cplRec.txnDate); --Bug 4864403 Only change date_completed if it is null
328: end if;
329: --allocate completions to sales orders
330: l_errMsg := 'SO allocation failed.'; --set message in case it fails
344: --if so allocation went ok, then update the kanban card if it exists
345: if(x_returnStatus = fnd_api.g_ret_sts_success and
346: p_cplRec.kanbanCardID is not null) then
347: l_errMsg := 'Kanban update failed.'; --set message in case it fails
348: if(p_cplRec.wipEntityType = wip_constants.lotbased) then
349: l_docType := 8;
350: else
351: l_docType := inv_kanban_pvt.g_doc_type_discrete_job;
352: end if;
362: end if;
363: else --a return
364: /* Bug 4864403 Change Status and date_completed when quantity_completed is less than start_quantity*/
365: if(l_qtyAvailToComplete > p_cplRec.priQty) then
366: l_jobStatus := wip_constants.released; --make sure the job status gets flipped back to released
367: l_cplDate := NULL;
368: end if;
369: -- Fixed bug 3678776. We should allow user to overreturn assembly back
370: -- to non-standard job.
368: end if;
369: -- Fixed bug 3678776. We should allow user to overreturn assembly back
370: -- to non-standard job.
371: if(abs(p_cplRec.priQty) > l_qtyCompleted AND
372: l_jobType = WIP_CONSTANTS.STANDARD) then
373: fnd_message.set_name('WIP', 'WIP_LESS_OR_EQUAL');
374: fnd_message.set_token('ENTITY1', 'total txn qty-cap', true);
375: fnd_message.set_token('ENTITY2', 'job complete quantity', true);
376: fnd_msg_pub.add;
423: raise fnd_api.g_exc_unexpected_error;
424: end if;
425: update wip_operations --lower the quantity available to complete
426: set quantity_waiting_to_move = quantity_waiting_to_move - p_cplRec.priQty,
427: date_last_moved = decode(p_cplRec.txnActionID, wip_constants.cplassy_action, p_cplRec.txnDate, date_last_moved),
428: last_updated_by = fnd_global.user_id,
429: last_update_date = sysdate,
430: request_id = fnd_global.conc_request_id,
431: program_application_id = fnd_global.prog_appl_id,
450: -- end of BUG 4869979 (FP 5107900)
451:
452: end if;
453:
454: if (l_logLevel <= wip_constants.trace_logging) then
455: wip_logger.exitPoint(p_procName => 'wip_cplProc_priv.processDiscrete',
456: p_procReturnStatus => x_returnStatus,
457: p_msg => 'procedure success.',
458: x_returnStatus => l_returnStatus); --discard logging return status
459: end if;
460: exception
461: when fnd_api.g_exc_unexpected_error then
462: x_returnStatus := fnd_api.g_ret_sts_unexp_error;
463: if (l_logLevel <= wip_constants.trace_logging) then
464: wip_logger.exitPoint(p_procName => 'wip_cplProc_priv.processDiscrete',
465: p_procReturnStatus => x_returnStatus,
466: p_msg => l_errMsg,
467: x_returnStatus => l_returnStatus); --discard logging return status
465: p_procReturnStatus => x_returnStatus,
466: p_msg => l_errMsg,
467: x_returnStatus => l_returnStatus); --discard logging return status
468: end if;
469: when wip_constants.records_locked then
470: x_returnStatus := fnd_api.g_ret_sts_unexp_error;
471: if (l_logLevel <= wip_constants.trace_logging) then
472: wip_logger.exitPoint(p_procName => 'wip_cplProc_priv.processDiscrete',
473: p_procReturnStatus => x_returnStatus,
467: x_returnStatus => l_returnStatus); --discard logging return status
468: end if;
469: when wip_constants.records_locked then
470: x_returnStatus := fnd_api.g_ret_sts_unexp_error;
471: if (l_logLevel <= wip_constants.trace_logging) then
472: wip_logger.exitPoint(p_procName => 'wip_cplProc_priv.processDiscrete',
473: p_procReturnStatus => x_returnStatus,
474: p_msg => 'records were locked',
475: x_returnStatus => l_returnStatus); --discard logging return status
480: x_returnStatus := fnd_api.g_ret_sts_unexp_error;
481: fnd_msg_pub.add_exc_msg(p_pkg_name => 'wip_cplProc_priv',
482: p_procedure_name => 'processDiscrete',
483: p_error_text => SQLERRM);
484: if (l_logLevel <= wip_constants.trace_logging) then
485: wip_logger.exitPoint(p_procName => 'wip_cplProc_priv.processDiscrete',
486: p_procReturnStatus => x_returnStatus,
487: p_msg => 'unexpected error: ' || SQLERRM,
488: x_returnStatus => l_returnStatus); --discard logging return status
534: wip_repetitive_schedules wrs,
535: wip_mtl_allocations_temp wmat
536: where wrs.wip_entity_id = p_cplRec.wipEntityID
537: and wrs.line_id = p_cplRec.repLineID
538: and wrs.status_type in (wip_constants.released, wip_constants.comp_chrg)
539: and wrs.repetitive_schedule_id = wo.repetitive_schedule_id (+)
540: and wrs.wip_entity_id = wo.wip_entity_id (+)
541: and v_lastOpSeq = wo.operation_seq_num (+)
542: and wrs.repetitive_schedule_id = wmat.repetitive_schedule_id
549: l_cplStatus NUMBER;
550: l_rollFwdSuccess NUMBER;
551: begin
552: x_returnStatus := fnd_api.g_ret_sts_success;
553: if (l_logLevel <= wip_constants.trace_logging) then
554: fillCplParamTbl(p_cplRec => p_cplRec,
555: x_params => l_params);
556: l_paramCount := l_params.count;
557: l_params(l_paramCount + 1).paramName := 'p_txnTmpID';
566: end if;
567: end if;
568:
569:
570: if(p_cplRec.txnActionID = wip_constants.cplassy_action AND
571: p_cplRec.kanbanCardID is not null) then
572: inv_kanban_pvt.update_card_supply_status
573: (p_kanban_card_id => p_cplRec.kanbanCardID,
574: p_supply_status => inv_kanban_pvt.g_supply_status_full,
628: l_errMsg := 'not enough qty in to move of last op';
629: raise fnd_api.g_exc_unexpected_error;
630: end if;
631: end if;
632: if (l_logLevel <= wip_constants.full_logging) then
633: wip_logger.log('schedID' || l_schedRecTbl.schedID(i), l_returnStatus);
634: wip_logger.log('startQty ' || l_schedRecTbl.startQty(i), l_returnStatus);
635: wip_logger.log('cpldQty ' || l_schedRecTbl.cpldQty(i), l_returnStatus);
636: wip_logger.log('scrapQty ' || l_scrapQty, l_returnStatus);
637: wip_logger.log('preAlcQty ' || l_schedRecTbl.preAlcQty(i), l_returnStatus);
638: end if;
639:
640: if(l_schedRecTbl.startQty(i) - l_schedRecTbl.cpldQty(i) - l_scrapQty <= l_schedRecTbl.preAlcQty(i) AND
641: p_cplRec.txnActionID = wip_constants.cplassy_action) then
642: l_status := wip_constants.comp_chrg;
643: else
644: l_status := wip_constants.released;
645: end if;
638: end if;
639:
640: if(l_schedRecTbl.startQty(i) - l_schedRecTbl.cpldQty(i) - l_scrapQty <= l_schedRecTbl.preAlcQty(i) AND
641: p_cplRec.txnActionID = wip_constants.cplassy_action) then
642: l_status := wip_constants.comp_chrg;
643: else
644: l_status := wip_constants.released;
645: end if;
646:
640: if(l_schedRecTbl.startQty(i) - l_schedRecTbl.cpldQty(i) - l_scrapQty <= l_schedRecTbl.preAlcQty(i) AND
641: p_cplRec.txnActionID = wip_constants.cplassy_action) then
642: l_status := wip_constants.comp_chrg;
643: else
644: l_status := wip_constants.released;
645: end if;
646:
647: update wip_repetitive_schedules
648: set quantity_completed = quantity_completed + l_schedRecTbl.preAlcQty(i),
654: program_id = fnd_global.conc_program_id,
655: program_update_date = sysdate
656: where rowid = chartorowid(l_schedRecTbl.wrsRowID(i));
657:
658: if(l_status = wip_constants.comp_chrg) then
659: wip_repetitive_utilities.roll_forward_cover(p_closed_sched_id => l_schedRecTbl.schedID(i),
660: p_rollfwd_sched_id => null, --doesn't seem to be in use
661: p_rollfwd_type => wip_constants.roll_complete,
662: p_org_id => p_cplRec.orgID,
657:
658: if(l_status = wip_constants.comp_chrg) then
659: wip_repetitive_utilities.roll_forward_cover(p_closed_sched_id => l_schedRecTbl.schedID(i),
660: p_rollfwd_sched_id => null, --doesn't seem to be in use
661: p_rollfwd_type => wip_constants.roll_complete,
662: p_org_id => p_cplRec.orgID,
663: p_update_status => wip_constants.yes,
664: p_success_flag => l_rollFwdSuccess,
665: p_error_msg => l_errMsg);
659: wip_repetitive_utilities.roll_forward_cover(p_closed_sched_id => l_schedRecTbl.schedID(i),
660: p_rollfwd_sched_id => null, --doesn't seem to be in use
661: p_rollfwd_type => wip_constants.roll_complete,
662: p_org_id => p_cplRec.orgID,
663: p_update_status => wip_constants.yes,
664: p_success_flag => l_rollFwdSuccess,
665: p_error_msg => l_errMsg);
666: if(l_rollFwdSuccess <> wip_constants.yes) then
667: -- fnd_msg_pub.add; --assume error message is still current
662: p_org_id => p_cplRec.orgID,
663: p_update_status => wip_constants.yes,
664: p_success_flag => l_rollFwdSuccess,
665: p_error_msg => l_errMsg);
666: if(l_rollFwdSuccess <> wip_constants.yes) then
667: -- fnd_msg_pub.add; --assume error message is still current
668: x_returnStatus := fnd_api.g_ret_sts_unexp_error;
669: l_errMsg := 'roll forward failed for schedule ' || l_schedRec.repSchedID;
670: end if;
700: wmat.transaction_date
701: from wip_mtl_allocations_temp wmat
702: where wmat.transaction_temp_id = p_txnTmpID;
703:
704: if (l_logLevel <= wip_constants.full_logging) then
705: wip_logger.log('inserted ' || SQL%ROWCOUNT || ' rows into MMTA', l_returnStatus);
706: end if;
707: delete wip_mtl_allocations_temp
708: where transaction_temp_id = p_txnTmpID;
706: end if;
707: delete wip_mtl_allocations_temp
708: where transaction_temp_id = p_txnTmpID;
709:
710: if (l_logLevel <= wip_constants.full_logging) then
711: wip_logger.log('deleted ' || SQL%ROWCOUNT || ' rows from WMAT', l_returnStatus);
712: end if;
713:
714: if (l_logLevel <= wip_constants.trace_logging) then
710: if (l_logLevel <= wip_constants.full_logging) then
711: wip_logger.log('deleted ' || SQL%ROWCOUNT || ' rows from WMAT', l_returnStatus);
712: end if;
713:
714: if (l_logLevel <= wip_constants.trace_logging) then
715: wip_logger.exitPoint(p_procName => 'wip_cplProc_priv.processRepetitive',
716: p_procReturnStatus => x_returnStatus,
717: p_msg => 'procedure success.',
718: x_returnStatus => l_returnStatus); --discard logging return status
719: end if;
720:
721: exception
722: when fnd_api.g_exc_unexpected_error then
723: if (l_logLevel <= wip_constants.trace_logging) then
724: wip_logger.exitPoint(p_procName => 'wip_cplProc_priv.processRepetitive',
725: p_procReturnStatus => x_returnStatus,
726: p_msg => l_errMsg,
727: x_returnStatus => l_returnStatus); --discard logging return status
730: x_returnStatus := fnd_api.g_ret_sts_unexp_error;
731: fnd_msg_pub.add_exc_msg(p_pkg_name => 'wip_cplProc_priv',
732: p_procedure_name => 'processRepetitive',
733: p_error_text => SQLERRM);
734: if (l_logLevel <= wip_constants.trace_logging) then
735: wip_logger.exitPoint(p_procName => 'wip_cplProc_priv.processRepetitive',
736: p_procReturnStatus => x_returnStatus,
737: p_msg => 'unexpected error: ' || SQLERRM,
738: x_returnStatus => l_returnStatus); --discard logging return status
771: where wip_entity_id = v_wipEntityID
772: for update of required_quantity nowait;
773:
774: begin
775: if (l_logLevel <= wip_constants.trace_logging) then
776: fillCplParamTbl(p_cplRec => p_cplRec,
777: x_params => l_params);
778: wip_logger.entryPoint(p_procName => 'wip_cplProc_priv.processOverCpl',
779: p_params => l_params,
784: raise fnd_api.g_exc_unexpected_error;
785: end if;
786: end if;
787:
788: if(p_cplRec.wipEntityType = wip_constants.repetitive) then
789: wip_repetitive_utilities.get_first_last_sched(p_wip_entity_id => p_cplRec.wipEntityID,
790: p_org_id => p_cplRec.orgID,
791: p_line_id => p_cplRec.repLineID,
792: x_first_sched_id => l_firstSchedID,
806: p_primary_quantity => p_cplRec.overCplPriQty,
807: p_result => x_returnStatus);
808:
809:
810: if(x_returnStatus = wip_constants.no) then
811: x_returnStatus := fnd_api.g_ret_sts_unexp_error;
812: fnd_message.set_name('WIP', 'WIP_OC_TOLERANCE_FAIL');
813: fnd_msg_pub.add;
814: l_errMsg := 'cpl exceeded tolerances';
821: --if a routing exists, insert a move record and perform the move
822: if(p_cplRec.lastOpSeq > 0) then
823:
824: wip_overcompletion.insert_oc_move_txn( p_primary_quantity => p_cplRec.overCplPriQty,
825: p_cpl_profile => wip_constants.online,
826: p_oc_txn_id => p_cplRec.overCplTxnID,
827: p_parent_cpl_txn_id => p_cplRec.cplTxnID,
828: p_first_schedule_id => l_firstSchedID,
829: p_user_id => p_cplRec.lastUpdBy, --fnd_global.user_id,
849:
850: wip_movProc_priv.processIntf(p_group_id => p_cplRec.movTxnID,
851: p_child_txn_id => -1,
852: p_mtl_header_id => p_cplRec.txnHdrID,
853: p_proc_phase => wip_constants.move_proc,
854: p_time_out => 0,
855: p_move_mode => wip_constants.online,
856: p_bf_mode => wip_constants.online, --ignored
857: p_mtl_mode => wip_constants.no_processing,--do not call inv TM at all
851: p_child_txn_id => -1,
852: p_mtl_header_id => p_cplRec.txnHdrID,
853: p_proc_phase => wip_constants.move_proc,
854: p_time_out => 0,
855: p_move_mode => wip_constants.online,
856: p_bf_mode => wip_constants.online, --ignored
857: p_mtl_mode => wip_constants.no_processing,--do not call inv TM at all
858: p_endDebug => fnd_api.g_false,
859: p_initMsgList => fnd_api.g_false,
852: p_mtl_header_id => p_cplRec.txnHdrID,
853: p_proc_phase => wip_constants.move_proc,
854: p_time_out => 0,
855: p_move_mode => wip_constants.online,
856: p_bf_mode => wip_constants.online, --ignored
857: p_mtl_mode => wip_constants.no_processing,--do not call inv TM at all
858: p_endDebug => fnd_api.g_false,
859: p_initMsgList => fnd_api.g_false,
860: p_insertAssy => fnd_api.g_true,
853: p_proc_phase => wip_constants.move_proc,
854: p_time_out => 0,
855: p_move_mode => wip_constants.online,
856: p_bf_mode => wip_constants.online, --ignored
857: p_mtl_mode => wip_constants.no_processing,--do not call inv TM at all
858: p_endDebug => fnd_api.g_false,
859: p_initMsgList => fnd_api.g_false,
860: p_insertAssy => fnd_api.g_true,
861: p_do_backflush => fnd_api.g_false,--backflush was already done
867: raise fnd_api.g_exc_unexpected_error;
868: end if;
869: --otherwise we just have to increase the component requirements in wro
870: else
871: if(p_cplRec.wipEntityType = wip_constants.repetitive) then
872: open c_repRows(v_schedID => l_lastSchedID);
873: update wip_requirement_operations
874: set required_quantity = round(required_quantity + p_cplRec.overCplPriQty * quantity_per_assembly, wip_constants.inv_max_precision)
875: where repetitive_schedule_id = l_lastSchedID;
870: else
871: if(p_cplRec.wipEntityType = wip_constants.repetitive) then
872: open c_repRows(v_schedID => l_lastSchedID);
873: update wip_requirement_operations
874: set required_quantity = round(required_quantity + p_cplRec.overCplPriQty * quantity_per_assembly, wip_constants.inv_max_precision)
875: where repetitive_schedule_id = l_lastSchedID;
876: close c_repRows;
877: else
878: open c_discRows(v_wipEntityID => p_cplRec.wipEntityID);
876: close c_repRows;
877: else
878: open c_discRows(v_wipEntityID => p_cplRec.wipEntityID);
879: update wip_requirement_operations
880: set required_quantity = round(required_quantity + p_cplRec.overCplPriQty * quantity_per_assembly, wip_constants.inv_max_precision)
881: where wip_entity_id = p_cplRec.wipEntityID
882: AND nvl(basis_type,1) <> WIP_CONSTANTS.LOT_BASED_MTL; /* LBM Project */
883: close c_discRows;
884: end if;
878: open c_discRows(v_wipEntityID => p_cplRec.wipEntityID);
879: update wip_requirement_operations
880: set required_quantity = round(required_quantity + p_cplRec.overCplPriQty * quantity_per_assembly, wip_constants.inv_max_precision)
881: where wip_entity_id = p_cplRec.wipEntityID
882: AND nvl(basis_type,1) <> WIP_CONSTANTS.LOT_BASED_MTL; /* LBM Project */
883: close c_discRows;
884: end if;
885: end if;
886:
883: close c_discRows;
884: end if;
885: end if;
886:
887: if (l_logLevel <= wip_constants.trace_logging) then
888: wip_logger.exitPoint(p_procName => 'wip_cplProc_priv.processOverCpl',
889: p_procReturnStatus => x_returnStatus,
890: p_msg => 'procedure success.',
891: x_returnStatus => l_returnStatus); --discard logging return status
891: x_returnStatus => l_returnStatus); --discard logging return status
892: end if;
893: exception
894: when fnd_api.g_exc_unexpected_error then
895: if (l_logLevel <= wip_constants.trace_logging) then
896: wip_logger.exitPoint(p_procName => 'wip_cplProc_priv.processOverCpl',
897: p_procReturnStatus => x_returnStatus,
898: p_msg => l_errMsg,
899: x_returnStatus => l_returnStatus); --discard logging return status
902: x_returnStatus := fnd_api.g_ret_sts_unexp_error;
903: fnd_msg_pub.add_exc_msg(p_pkg_name => 'wip_cplProc_priv',
904: p_procedure_name => 'processOverCpl',
905: p_error_text => SQLERRM);
906: if (l_logLevel <= wip_constants.trace_logging) then
907: wip_logger.exitPoint(p_procName => 'wip_cplProc_priv.processOverCpl',
908: p_procReturnStatus => x_returnStatus,
909: p_msg => 'unexpected error: ' || SQLERRM,
910: x_returnStatus => l_returnStatus); --discard logging return status
932: wip_mtl_allocations_temp wmat
933: where wrs.wip_entity_id = v_wipEntityID
934: and wrs.line_id = v_repLineID
935: and wrs.date_released < v_txnDate
936: and wrs.status_type in (wip_constants.released, wip_constants.comp_chrg)
937: and wrs.repetitive_schedule_id = wo.repetitive_schedule_id (+)
938: and wrs.wip_entity_id = wo.wip_entity_id (+)
939: and v_lastOpSeq = wo.operation_seq_num (+)
940: and wrs.repetitive_schedule_id = wmat.repetitive_schedule_id (+)
957: wip_mtl_allocations_temp wmat
958: where wrs.wip_entity_id = v_wipEntityID
959: and wrs.line_id = v_repLineID
960: and wrs.date_released < v_txnDate
961: and wrs.status_type in (wip_constants.released, wip_constants.comp_chrg)
962: and wrs.repetitive_schedule_id = wmat.repetitive_schedule_id (+)
963: group by wrs.repetitive_schedule_id,
964: wrs.bom_revision,
965: wrs.daily_production_rate,
981: where completion_transaction_id = p_cplTxnID
982: and transaction_header_id = p_txnHdrID
983: and transaction_source_id = p_wipEntityID
984: and transaction_source_type_id = 5
985: and transaction_action_id in (wip_constants.cplassy_action, wip_constants.retassy_action);
986:
987: cursor c_mtiTxns is
988: select transaction_interface_id,
989: operation_seq_num lastOpSeq,
997: where completion_transaction_id = p_cplTxnID
998: and transaction_header_id = p_txnHdrID
999: and transaction_source_id = p_wipEntityID
1000: and transaction_source_type_id = 5
1001: and transaction_action_id in (wip_constants.cplassy_action, wip_constants.retassy_action);
1002:
1003: type schedule_recTbl_t is record(repSchedID num_tbl_t,
1004: bomRev char_tbl_t,
1005: availQty num_tbl_t,
1028: x_returnStatus := fnd_api.g_ret_sts_success;
1029:
1030: savepoint wipcplpb40;
1031:
1032: if (l_logLevel <= wip_constants.trace_logging) then
1033: l_params(1).paramName := 'p_txnHdrID';
1034: l_params(1).paramValue := p_txnHdrID;
1035: l_params(2).paramName := 'p_cplTxnID';
1036: l_params(2).paramValue := p_cplTxnID;
1051: raise fnd_api.g_exc_unexpected_error;
1052: end if;
1053: end if;
1054:
1055: if(p_tblName = wip_constants.MTI_TBL) then
1056: if (l_logLevel <= wip_constants.full_logging) then
1057: wip_logger.log('.25', l_returnStatus);
1058: end if;
1059: open c_mtiTxns;
1052: end if;
1053: end if;
1054:
1055: if(p_tblName = wip_constants.MTI_TBL) then
1056: if (l_logLevel <= wip_constants.full_logging) then
1057: wip_logger.log('.25', l_returnStatus);
1058: end if;
1059: open c_mtiTxns;
1060: fetch c_mtiTxns
1066: l_txnRecTbl.txnQty,
1067: l_txnRecTbl.overCplQty,
1068: l_txnRecTbl.orgID;
1069: close c_mtiTxns;
1070: if (l_logLevel <= wip_constants.full_logging) then
1071: wip_logger.log('mti row count is ' || l_txnRecTbl.txnID.count, l_returnStatus);
1072: end if;
1073: else
1074: if (l_logLevel <= wip_constants.full_logging) then
1070: if (l_logLevel <= wip_constants.full_logging) then
1071: wip_logger.log('mti row count is ' || l_txnRecTbl.txnID.count, l_returnStatus);
1072: end if;
1073: else
1074: if (l_logLevel <= wip_constants.full_logging) then
1075: wip_logger.log('1', l_returnStatus);
1076: end if;
1077: open c_mmttTxns;
1078: fetch c_mmttTxns
1084: l_txnRecTbl.txnQty,
1085: l_txnRecTbl.overCplQty,
1086: l_txnRecTbl.orgID;
1087: close c_mmttTxns;
1088: if (l_logLevel <= wip_constants.full_logging) then
1089: wip_logger.log('2', l_returnStatus);
1090: end if;
1091: end if;
1092:
1090: end if;
1091: end if;
1092:
1093: for i in 1..l_txnRecTbl.txnID.count loop
1094: if (l_logLevel <= wip_constants.full_logging) then
1095: wip_logger.log('processing cpl tmpID:' || l_txnRecTbl.txnID(i) || '; qty:' || l_txnRecTbl.priQty(i), l_returnStatus);
1096: end if;
1097:
1098: if(l_txnRecTbl.lastOpSeq(i) is null or l_txnRecTbl.lastOpSeq(i) < 0) then
1100: else
1101: l_lastOpSeq := l_txnRecTbl.lastOpSeq(i);
1102: end if;
1103:
1104: if (l_logLevel <= wip_constants.full_logging) then
1105: wip_logger.log('last op seq is ' || l_lastOpSeq, l_returnStatus);
1106: wip_logger.log('action is ' || l_lastOpSeq, l_returnStatus);
1107: end if;
1108: l_remainingQty := l_txnRecTbl.priQty(i);
1106: wip_logger.log('action is ' || l_lastOpSeq, l_returnStatus);
1107: end if;
1108: l_remainingQty := l_txnRecTbl.priQty(i);
1109:
1110: if(p_txnActionID = wip_constants.cplassy_action) then
1111: /* Fix for bug 5373061: Pass txnDate to cursor to fetch
1112: only valid schedules as on transaction date */
1113: open c_repCplScheds(v_lastOpSeq => l_txnRecTbl.lastOpSeq(i),
1114: v_wipEntityID => p_wipEntityID,
1137: close c_repRetScheds;
1138: end if;
1139: for j in 1..l_schedRecTbl.repSchedID.count loop
1140:
1141: if(p_txnActionID = wip_constants.cplassy_action) then
1142: if(l_schedRecTbl.availQty(j) is null) then
1143: select ((wrs.daily_production_rate * wrs.processing_work_days) - wrs.quantity_completed) - nvl(sum(wo.quantity_scrapped), 0)
1144: into l_schedRecTbl.availQty(j)
1145: from wip_repetitive_schedules wrs,
1159:
1160:
1161: l_lastSchedID := l_schedRecTbl.repSchedID(j);--the last schedule fetched is the last one open for completions
1162:
1163: if(l_logLevel <= wip_constants.full_logging) then
1164: wip_logger.log('processing sched ' || l_schedRecTbl.repSchedID(j) || ' w/open qty of ' || l_schedQty, l_returnStatus);
1165: wip_logger.log('availQty: ' || l_schedRecTbl.availQty(j), l_returnStatus);
1166: wip_logger.log('startQty: ' || l_startQty, l_returnStatus);
1167: wip_logger.log('tempQty: ' || l_schedRecTbl.tempQty(j), l_returnStatus);
1179:
1180: --complete the lesser of the open quantity and the remaining transaction qty
1181:
1182: if(l_schedQty <> 0) then
1183: if (l_logLevel <= wip_constants.full_logging) then
1184: wip_logger.log('inserting into temp table; sched:' || l_schedRecTbl.repSchedID(j) || '; qty:' || l_schedQty, l_returnStatus);
1185: end if;
1186: insert into wip_mtl_allocations_temp
1187: (transaction_temp_id,
1213: fnd_global.conc_request_id,
1214: fnd_global.prog_appl_id,
1215: fnd_global.conc_program_id,
1216: sysdate,
1217: round(l_txnRecTbl.txnQty(i) * l_schedQty/l_txnRecTbl.priQty(i), wip_constants.inv_max_precision), --% of qty completed * txn Qty
1218: l_schedQty,
1219: l_txnRecTbl.txnDate(i));
1220:
1221: l_remainingQty := l_remainingQty - l_schedQty;
1233: l_errMsg := 'did not find any schedules.';
1234: raise fnd_api.g_exc_unexpected_error; -- couldn't find any open schedules
1235: end if;
1236:
1237: if (l_logLevel <= wip_constants.full_logging) then
1238: wip_logger.log('txnID' || l_txnRecTbl.txnID(i), l_returnStatus);
1239: wip_logger.log('rmnQty' || l_remainingQty, l_returnStatus);
1240: wip_logger.log('schedID' || l_lastSchedID, l_returnStatus);
1241: end if;
1255: transactions can pick up older schedules which do not have open quantity. Do not allocate
1256: to the older schedule. Throw error */
1257:
1258: /* Fix for Bug#6018877 - FP of bug#6004763. Added the if condition check not to consider Return txn */
1259: if(p_txnActionID <> wip_constants.retassy_action) then
1260: if(l_txnRecTbl.overCplQty(i) IS NULL) then
1261: fnd_message.set_name('WIP', 'WIP_INT_ERROR_NO_SCHED');
1262: fnd_message.set_token('ROUTINE', 'wip_cplProc_priv.preAllocateSchedules');
1263: fnd_msg_pub.add;
1265: raise fnd_api.g_exc_unexpected_error; -- couldn't find any open schedules
1266: end if;
1267: end if; /*Fix for Bug#6018877 - FP of bug#6004763*/
1268:
1269: if (l_logLevel <= wip_constants.full_logging) then
1270: wip_logger.log('update failed. inserting new row', l_returnStatus);
1271: end if;
1272: /* Fixed bug 3698513. Completion_transaction_id is a not null column
1273: * , so we have to insert a value into this column. This bug only
1311: end if;
1312: end loop;
1313:
1314:
1315: if (l_logLevel <= wip_constants.trace_logging) then
1316: wip_logger.exitPoint(p_procName => 'wip_cplProc_priv.preAllocateSchedules',
1317: p_procReturnStatus => x_returnStatus,
1318: p_msg => 'procedure success.',
1319: x_returnStatus => l_returnStatus); --discard logging return status
1321: exception
1322: when fnd_api.g_exc_unexpected_error then
1323: rollback to wipcplpb40;
1324: x_returnStatus := fnd_api.g_ret_sts_unexp_error;
1325: if (l_logLevel <= wip_constants.trace_logging) then
1326: wip_logger.exitPoint(p_procName => 'wip_cplProc_priv.preAllocateSchedules',
1327: p_procReturnStatus => x_returnStatus,
1328: p_msg => l_errMsg,
1329: x_returnStatus => l_returnStatus); --discard logging return status
1332: x_returnStatus := fnd_api.g_ret_sts_unexp_error;
1333: fnd_msg_pub.add_exc_msg(p_pkg_name => 'wip_cplProc_priv',
1334: p_procedure_name => 'preAllocateSchedules',
1335: p_error_text => SQLERRM);
1336: if (l_logLevel <= wip_constants.trace_logging) then
1337: wip_logger.exitPoint(p_procName => 'wip_cplProc_priv.preAllocateSchedules',
1338: p_procReturnStatus => x_returnStatus,
1339: p_msg => 'unexpected error: ' || SQLERRM,
1340: x_returnStatus => l_returnStatus); --discard logging return status
1356: from wip_repetitive_schedules wrs,
1357: wip_mtl_allocations_temp wmat
1358: where wrs.wip_entity_id = p_wipEntityID
1359: and wrs.line_id = p_repLineID
1360: and wrs.status_type in (wip_constants.released, wip_constants.comp_chrg)
1361: and wrs.repetitive_schedule_id = wmat.repetitive_schedule_id (+)
1362: group by wrs.repetitive_schedule_id,
1363: wrs.bom_revision,
1364: wrs.daily_production_rate,
1374: l_params wip_logger.param_tbl_t;
1375: begin
1376: --need to find the first open schedule. if we have pre-allocated completions, we have to see
1377: --which ones will be completed by the time this return is processed.
1378: if (l_logLevel <= wip_constants.trace_logging) then
1379: l_params(1).paramName := 'p_txnHdrID';
1380: l_params(1).paramValue := p_txnHdrID;
1381: l_params(2).paramName := 'p_cplTxnID';
1382: l_params(2).paramValue := p_cplTxnID;
1398:
1399: for l_schedRec in c_openScheds loop
1400: l_schedID := l_schedRec.repetitive_schedule_id;
1401:
1402: if(l_logLevel <= wip_constants.full_logging) then
1403: wip_logger.log('l_schedRec.cplQty' || l_schedRec.cplQty, l_returnStatus);
1404: wip_logger.log('l_schedRec.tempQty' || l_schedRec.tempQty, l_returnStatus);
1405: wip_logger.log('l_schedRec.startQty' || l_schedRec.startQty, l_returnStatus);
1406: end if;
1405: wip_logger.log('l_schedRec.startQty' || l_schedRec.startQty, l_returnStatus);
1406: end if;
1407:
1408: if(greatest(l_schedRec.cplQty, 0) + nvl(l_schedRec.tempQty, 0) < l_schedRec.startQty) then
1409: if (l_logLevel <= wip_constants.full_logging) then
1410: wip_logger.log('found open schedule' || l_schedID, l_returnStatus);
1411: end if;
1412: exit;
1413: end if;
1411: end if;
1412: exit;
1413: end if;
1414: end loop;
1415: if (l_logLevel <= wip_constants.full_logging) then
1416: wip_logger.log('returning to schedule' || l_schedID, l_returnStatus);
1417: end if;
1418:
1419: if(p_tblName = wip_constants.MMTT_TBL) then
1415: if (l_logLevel <= wip_constants.full_logging) then
1416: wip_logger.log('returning to schedule' || l_schedID, l_returnStatus);
1417: end if;
1418:
1419: if(p_tblName = wip_constants.MMTT_TBL) then
1420: insert into wip_mtl_allocations_temp
1421: (transaction_temp_id,
1422: repetitive_schedule_id,
1423: organization_id,
1450: mmtt.transaction_date
1451: from mtl_material_transactions_temp mmtt
1452: where mmtt.transaction_header_id = p_txnHdrID
1453: and mmtt.completion_transaction_id = p_cplTxnID
1454: and mmtt.transaction_action_id = wip_constants.retassy_action;
1455: elsif(p_tblName = wip_constants.MTI_TBL) then
1456: insert into wip_mtl_allocations_temp
1457: (transaction_temp_id,
1458: repetitive_schedule_id,
1451: from mtl_material_transactions_temp mmtt
1452: where mmtt.transaction_header_id = p_txnHdrID
1453: and mmtt.completion_transaction_id = p_cplTxnID
1454: and mmtt.transaction_action_id = wip_constants.retassy_action;
1455: elsif(p_tblName = wip_constants.MTI_TBL) then
1456: insert into wip_mtl_allocations_temp
1457: (transaction_temp_id,
1458: repetitive_schedule_id,
1459: organization_id,
1486: mti.transaction_date
1487: from mtl_transactions_interface mti
1488: where mti.transaction_header_id = p_txnHdrID
1489: and mti.completion_transaction_id = p_cplTxnID
1490: and mti.transaction_action_id = wip_constants.retassy_action;
1491: end if;
1492:
1493: if (l_logLevel <= wip_constants.full_logging) then
1494: wip_logger.log('inserted ' || SQL%ROWCOUNT || ' rows', l_returnStatus);
1489: and mti.completion_transaction_id = p_cplTxnID
1490: and mti.transaction_action_id = wip_constants.retassy_action;
1491: end if;
1492:
1493: if (l_logLevel <= wip_constants.full_logging) then
1494: wip_logger.log('inserted ' || SQL%ROWCOUNT || ' rows', l_returnStatus);
1495: end if;
1496:
1497: x_returnStatus := fnd_api.g_ret_sts_success;
1495: end if;
1496:
1497: x_returnStatus := fnd_api.g_ret_sts_success;
1498:
1499: if (l_logLevel <= wip_constants.trace_logging) then
1500: wip_logger.exitPoint(p_procName => 'wip_cplProc_priv.preAllocateRets',
1501: p_procReturnStatus => x_returnStatus,
1502: p_msg => 'success',
1503: x_returnStatus => l_returnStatus); --discard logging return status
1505:
1506: exception
1507: when others then
1508: x_returnStatus := fnd_api.g_ret_sts_unexp_error;
1509: if (l_logLevel <= wip_constants.trace_logging) then
1510: wip_logger.exitPoint(p_procName => 'wip_cplProc_priv.preAllocateRets',
1511: p_procReturnStatus => x_returnStatus,
1512: p_msg => 'unexpected error: ' || SQLERRM,
1513: x_returnStatus => l_returnStatus); --discard logging return status
1529: l_errMsg VARCHAR2(2000);
1530: begin
1531: savepoint wipcplpb40;
1532:
1533: if (l_logLevel <= wip_constants.trace_logging) then
1534: l_params(1).paramName := 'p_cplTxnID';
1535: l_params(1).paramValue := p_cplTxnID;
1536:
1537: wip_logger.entryPoint(p_procName => 'wip_cplProc_priv.preAllocateSchedules',
1542: raise fnd_api.g_exc_unexpected_error;
1543: end if;
1544: end if;
1545:
1546: if(p_txnActionID = wip_constants.cplassy_action) then
1547: preAllocateCpls(p_txnHdrID => p_txnHdrID,
1548: p_cplTxnID => p_cplTxnID,
1549: p_wipEntityID => p_wipEntityID,
1550: p_repLineID => p_repLineID,
1562: if(x_returnStatus <> fnd_api.g_ret_sts_success) then
1563: raise fnd_api.g_exc_unexpected_error;
1564: end if;
1565:
1566: if (l_logLevel <= wip_constants.trace_logging) then
1567: wip_logger.exitPoint(p_procName => 'wip_cplProc_priv.preAllocateSchedules',
1568: p_procReturnStatus => x_returnStatus,
1569: p_msg => 'procedure success.',
1570: x_returnStatus => l_returnStatus); --discard logging return status
1575:
1576: exception
1577: when fnd_api.g_exc_unexpected_error then
1578: rollback to wipcplpb40;
1579: if (l_logLevel <= wip_constants.trace_logging) then
1580: wip_logger.exitPoint(p_procName => 'wip_cplProc_priv.preAllocateSchedules',
1581: p_procReturnStatus => x_returnStatus,
1582: p_msg => l_errMsg,
1583: x_returnStatus => l_returnStatus); --discard logging return status
1590: x_returnStatus := fnd_api.g_ret_sts_unexp_error;
1591: fnd_msg_pub.add_exc_msg(p_pkg_name => 'wip_cplProc_priv',
1592: p_procedure_name => 'preAllocateSchedules',
1593: p_error_text => SQLERRM);
1594: if (l_logLevel <= wip_constants.trace_logging) then
1595: wip_logger.exitPoint(p_procName => 'wip_cplProc_priv.preAllocateSchedules',
1596: p_procReturnStatus => x_returnStatus,
1597: p_msg => 'unexpected error: ' || SQLERRM,
1598: x_returnStatus => l_returnStatus); --discard logging return status
1698: l_op_seq NUMBER;
1699: l_step NUMBER;
1700: BEGIN
1701: l_process_phase := '1';
1702: IF (l_log_level <= wip_constants.trace_logging) THEN
1703: l_params(1).paramName := 'p_org_id';
1704: l_params(1).paramValue := p_org_id;
1705: l_params(2).paramName := 'p_interface_id';
1706: l_params(2).paramValue := p_interface_id;
1732: WHERE organization_id = p_org_id;
1733:
1734: l_process_phase := '4';
1735: --insert a row into cst_comp_snap_temp
1736: IF(l_cost_method IN (wip_constants.cost_avg,wip_constants.cost_fifo,
1737: wip_constants.cost_lifo)) THEN
1738: l_ret_value := CSTACOSN.op_snapshot(i_txn_temp_id => p_interface_id,
1739: err_num => l_err_num,
1740: err_code => l_error_msg,
1733:
1734: l_process_phase := '4';
1735: --insert a row into cst_comp_snap_temp
1736: IF(l_cost_method IN (wip_constants.cost_avg,wip_constants.cost_fifo,
1737: wip_constants.cost_lifo)) THEN
1738: l_ret_value := CSTACOSN.op_snapshot(i_txn_temp_id => p_interface_id,
1739: err_num => l_err_num,
1740: err_code => l_error_msg,
1741: err_msg => l_error_msg);
1758: p_business_flow_code => 26); -- discrete business flow code
1759: -- do not error out if label printing, only put warning message in log
1760: IF(x_returnStatus <> fnd_api.g_ret_sts_success) THEN
1761: wip_utilities.get_message_stack(p_msg => l_msg_stack);
1762: IF (l_log_level <= wip_constants.full_logging) then
1763: wip_logger.log(p_msg => 'An error has occurred with label printing.\n' ||
1764: 'The following error has occurred during ' ||
1765: 'printing: ' || l_msg_stack || '\n' ||
1766: 'Please check the Inventory log file for more ' ||
1777: FETCH c_cmp_txn INTO l_cmp_txn;
1778:
1779: IF(p_oc_primary_qty IS NOT NULL AND p_oc_primary_qty > 0) THEN
1780: -- Initialize l_oc_rec
1781: l_oc_rec.wipEntityType := WIP_CONSTANTS.DISCRETE;
1782: l_oc_rec.wipEntityID := l_cmp_txn.wip_entity_id;
1783: l_oc_rec.orgID := l_cmp_txn.org_id;
1784: l_oc_rec.repLineID := null; -- only used for repetitive
1785: l_oc_rec.itemID := l_cmp_txn.item_id;
1799: l_oc_rec.overCplPriQty := p_oc_primary_qty;
1800: l_oc_rec.lastUpdBy := l_cmp_txn.last_updated_by;
1801: l_oc_rec.createdBy := l_cmp_txn.created_by;
1802: l_oc_rec.lpnID := null; -- only used for LPN
1803: l_oc_rec.txnMode := WIP_CONSTANTS.ONLINE;
1804: l_oc_rec.overCplTxnID := l_cmp_txn.oc_txn_id;
1805:
1806: wip_cplProc_priv.processOverCpl(p_cplRec => l_oc_rec,
1807: x_returnStatus => x_returnStatus);
1814: l_process_phase := '7';
1815: wip_mtlTempProc_priv.processTemp
1816: (p_initMsgList => fnd_api.g_true,
1817: p_txnHdrID => p_mtl_header_id,
1818: p_txnMode => WIP_CONSTANTS.ONLINE,
1819: x_returnStatus => x_returnStatus,
1820: x_errorMsg => l_error_msg);
1821:
1822: IF(x_returnStatus <> fnd_api.g_ret_sts_success) THEN
1831: -- clear the group mark id when processing a serial. Here we have to
1832: -- repopulate the group_mark_id, wip_entity_id, op_seq, and
1833: -- intra_op columns.
1834: IF (p_assySerial IS NOT NULL AND
1835: l_cmp_txn.action_id = WIP_CONSTANTS.RETASSY_ACTION) THEN
1836: -- Check whether the job has routing or not.
1837: IF(l_cmp_txn.op_seq_num = -1) THEN
1838: -- No routing
1839: l_op_seq := null;
1839: l_op_seq := null;
1840: l_step := null;
1841: ELSE
1842: l_op_seq := l_cmp_txn.op_seq_num;
1843: l_step := WIP_CONSTANTS.TOMOVE;
1844: END IF;
1845:
1846: wip_utilities.update_serial(p_serial_number => p_assySerial,
1847: p_inventory_item_id => l_cmp_txn.item_id,
1862: END IF;
1863:
1864: x_returnStatus := fnd_api.g_ret_sts_success;
1865: -- write to the log file
1866: IF (l_log_level <= wip_constants.trace_logging) THEN
1867: wip_logger.exitPoint(p_procName => 'wip_cplProc_priv.processOATxn',
1868: p_procReturnStatus => x_returnStatus,
1869: p_msg => 'procedure complete',
1870: x_returnStatus => l_return_status);
1879: END IF;
1880: ROLLBACK TO SAVEPOINT s_oa_txn_proc;
1881: x_returnStatus := fnd_api.g_ret_sts_error;
1882:
1883: IF (l_log_level <= wip_constants.trace_logging) THEN
1884: wip_logger.exitPoint(p_procName => 'wip_cplProc_priv.processOATxn',
1885: p_procReturnStatus => x_returnStatus,
1886: p_msg => 'wip_cplProc_priv.processOATxn failed : '
1887: || l_process_phase,
1901: fnd_message.set_name('FND', 'FND_GENERIC_MESSAGE');
1902: fnd_message.set_token('MESSAGE', l_error_msg);
1903: fnd_msg_pub.add;
1904:
1905: IF (l_log_level <= wip_constants.trace_logging) THEN
1906: wip_logger.exitPoint(p_procName => 'wip_cplProc_priv.processOATxn',
1907: p_procReturnStatus => x_returnStatus,
1908: p_msg => l_error_msg || ' : ' || l_process_phase,
1909: x_returnStatus => l_return_status);