[Home] [Help]
17: ----------------------
18: --forward declarations
19: ----------------------
20: procedure fillCplParamTbl(p_cplRec IN completion_rec_t,
21: x_params OUT NOCOPY wip_logger.param_tbl_t);
22:
23: procedure processRepetitive(p_cplRec IN completion_rec_t,
24: p_txnTmpID IN NUMBER,
25: x_returnStatus OUT NOCOPY VARCHAR2);
38: x_returnStatus OUT NOCOPY VARCHAR2) is
39:
40: l_cplRec completion_rec_t;
41: l_wipEntityType NUMBER;
42: l_params wip_logger.param_tbl_t;
43: l_returnStatus VARCHAR2(1);
44: l_retValue VARCHAR2(10);
45: l_msgCount NUMBER;
46: l_errMsg VARCHAR2(240);
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,
67: x_returnStatus => x_returnStatus);
68:
69: if(x_returnStatus <> fnd_api.g_ret_sts_success) then
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
207: end if;
205: p_msg => 'procedure success.',
206: x_returnStatus => l_returnStatus); --discard logging return status
207: end if;
208: if(fnd_api.to_boolean(p_endDebug)) then
209: wip_logger.cleanUp(x_returnStatus => l_returnStatus); --discard logging return status
210: end if;
211: exception
212: when no_data_found then
213: rollback to wipcplpb20;
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
220: end if;
218: p_msg => 'no data found',
219: x_returnStatus => l_returnStatus); --discard logging return status
220: end if;
221: if(fnd_api.to_boolean(p_endDebug)) then
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
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
231: --when the record is processed again, only the material processing and inv txn will occur
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
237: end if;
235: p_msg => 'need to collect l/s info',
236: x_returnStatus => l_returnStatus); --discard logging return status
237: end if;
238: if(fnd_api.to_boolean(p_endDebug)) then
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
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
248: end if;
246: p_msg => l_errMsg,
247: x_returnStatus => l_returnStatus); --discard logging return status
248: end if;
249: if(fnd_api.to_boolean(p_endDebug)) then
250: wip_logger.cleanUp(x_returnStatus => l_returnStatus); --discard logging return status
251: end if;
252: when others then
253: rollback to wipcplpb20;
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
263: end if;
261: p_msg => 'unexpected error: ' || SQLERRM,
262: x_returnStatus => l_returnStatus); --discard logging return status
263: end if;
264: if(fnd_api.to_boolean(p_endDebug)) then
265: wip_logger.cleanUp(x_returnStatus => l_returnStatus); --discard logging return status
266: end if;
267: end processTemp;
268:
269: procedure processDiscrete(p_cplRec IN completion_rec_t,
276: l_cplDate DATE;
277: l_toMoveQty NUMBER;
278: l_msgCount NUMBER;
279: l_paramCount NUMBER;
280: l_params wip_logger.param_tbl_t;
281: l_errMsg VARCHAR2(240);
282: l_msgTxt VARCHAR2(2000);
283: l_returnStatus VARCHAR2(1);
284: l_qtyAvailToComplete NUMBER;
298: l_paramCount := l_params.count;
299: l_params(l_paramCount + 1).paramName := 'p_txnTmpID';
300: l_params(l_paramCount + 1).paramValue := p_txnTmpID;
301:
302: wip_logger.entryPoint(p_procName => 'wip_cplProc_priv.processDiscrete',
303: p_params => l_params,
304: x_returnStatus => x_returnStatus);
305: if(x_returnStatus <> fnd_api.g_ret_sts_success) then
306: raise fnd_api.g_exc_unexpected_error;
474:
475: end if;
476:
477: if (l_logLevel <= wip_constants.trace_logging) then
478: wip_logger.exitPoint(p_procName => 'wip_cplProc_priv.processDiscrete',
479: p_procReturnStatus => x_returnStatus,
480: p_msg => 'procedure success.',
481: x_returnStatus => l_returnStatus); --discard logging return status
482: end if;
483: exception
484: when fnd_api.g_exc_unexpected_error then
485: x_returnStatus := fnd_api.g_ret_sts_unexp_error;
486: if (l_logLevel <= wip_constants.trace_logging) then
487: wip_logger.exitPoint(p_procName => 'wip_cplProc_priv.processDiscrete',
488: p_procReturnStatus => x_returnStatus,
489: p_msg => l_errMsg,
490: x_returnStatus => l_returnStatus); --discard logging return status
491: end if;
491: end if;
492: when wip_constants.records_locked then
493: x_returnStatus := fnd_api.g_ret_sts_unexp_error;
494: if (l_logLevel <= wip_constants.trace_logging) then
495: wip_logger.exitPoint(p_procName => 'wip_cplProc_priv.processDiscrete',
496: p_procReturnStatus => x_returnStatus,
497: p_msg => 'records were locked',
498: x_returnStatus => l_returnStatus); --discard logging return status
499: end if;
506: fnd_msg_pub.add_exc_msg(p_pkg_name => 'wip_cplProc_priv',
507: p_procedure_name => 'processDiscrete',
508: p_error_text => SQLERRM);
509: if (l_logLevel <= wip_constants.trace_logging) then
510: wip_logger.exitPoint(p_procName => 'wip_cplProc_priv.processDiscrete',
511: p_procReturnStatus => x_returnStatus,
512: p_msg => 'unexpected error: ' || SQLERRM,
513: x_returnStatus => l_returnStatus); --discard logging return status
514: end if;
524: l_firstSchedID NUMBER;
525: l_lastSchedID NUMBER;
526: l_schedRec schedule_rec_t;
527: l_status NUMBER;
528: l_params wip_logger.param_tbl_t;
529: l_paramCount NUMBER;
530: l_returnStatus VARCHAR2(1);
531: l_errMsg VARCHAR2(240);
532: l_nullObj system.wip_component_tbl_t := null;
581: l_paramCount := l_params.count;
582: l_params(l_paramCount + 1).paramName := 'p_txnTmpID';
583: l_params(l_paramCount + 1).paramValue := p_txnTmpID;
584:
585: wip_logger.entryPoint(p_procName => 'wip_cplProc_priv.processRepetitive',
586: p_params => l_params,
587: x_returnStatus => x_returnStatus);
588:
589: if(x_returnStatus <> fnd_api.g_ret_sts_success) then
654: raise fnd_api.g_exc_unexpected_error;
655: end if;
656: end if;
657: if (l_logLevel <= wip_constants.full_logging) then
658: wip_logger.log('schedID' || l_schedRecTbl.schedID(i), l_returnStatus);
659: wip_logger.log('startQty ' || l_schedRecTbl.startQty(i), l_returnStatus);
660: wip_logger.log('cpldQty ' || l_schedRecTbl.cpldQty(i), l_returnStatus);
661: wip_logger.log('scrapQty ' || l_scrapQty, l_returnStatus);
662: wip_logger.log('preAlcQty ' || l_schedRecTbl.preAlcQty(i), l_returnStatus);
655: end if;
656: end if;
657: if (l_logLevel <= wip_constants.full_logging) then
658: wip_logger.log('schedID' || l_schedRecTbl.schedID(i), l_returnStatus);
659: wip_logger.log('startQty ' || l_schedRecTbl.startQty(i), l_returnStatus);
660: wip_logger.log('cpldQty ' || l_schedRecTbl.cpldQty(i), l_returnStatus);
661: wip_logger.log('scrapQty ' || l_scrapQty, l_returnStatus);
662: wip_logger.log('preAlcQty ' || l_schedRecTbl.preAlcQty(i), l_returnStatus);
663: end if;
656: end if;
657: if (l_logLevel <= wip_constants.full_logging) then
658: wip_logger.log('schedID' || l_schedRecTbl.schedID(i), l_returnStatus);
659: wip_logger.log('startQty ' || l_schedRecTbl.startQty(i), l_returnStatus);
660: wip_logger.log('cpldQty ' || l_schedRecTbl.cpldQty(i), l_returnStatus);
661: wip_logger.log('scrapQty ' || l_scrapQty, l_returnStatus);
662: wip_logger.log('preAlcQty ' || l_schedRecTbl.preAlcQty(i), l_returnStatus);
663: end if;
664:
657: if (l_logLevel <= wip_constants.full_logging) then
658: wip_logger.log('schedID' || l_schedRecTbl.schedID(i), l_returnStatus);
659: wip_logger.log('startQty ' || l_schedRecTbl.startQty(i), l_returnStatus);
660: wip_logger.log('cpldQty ' || l_schedRecTbl.cpldQty(i), l_returnStatus);
661: wip_logger.log('scrapQty ' || l_scrapQty, l_returnStatus);
662: wip_logger.log('preAlcQty ' || l_schedRecTbl.preAlcQty(i), l_returnStatus);
663: end if;
664:
665: if(l_schedRecTbl.startQty(i) - l_schedRecTbl.cpldQty(i) - l_scrapQty <= l_schedRecTbl.preAlcQty(i) AND
658: wip_logger.log('schedID' || l_schedRecTbl.schedID(i), l_returnStatus);
659: wip_logger.log('startQty ' || l_schedRecTbl.startQty(i), l_returnStatus);
660: wip_logger.log('cpldQty ' || l_schedRecTbl.cpldQty(i), l_returnStatus);
661: wip_logger.log('scrapQty ' || l_scrapQty, l_returnStatus);
662: wip_logger.log('preAlcQty ' || l_schedRecTbl.preAlcQty(i), l_returnStatus);
663: end if;
664:
665: if(l_schedRecTbl.startQty(i) - l_schedRecTbl.cpldQty(i) - l_scrapQty <= l_schedRecTbl.preAlcQty(i) AND
666: p_cplRec.txnActionID = wip_constants.cplassy_action) then
726: from wip_mtl_allocations_temp wmat
727: where wmat.transaction_temp_id = p_txnTmpID;
728:
729: if (l_logLevel <= wip_constants.full_logging) then
730: wip_logger.log('inserted ' || SQL%ROWCOUNT || ' rows into MMTA', l_returnStatus);
731: end if;
732: delete wip_mtl_allocations_temp
733: where transaction_temp_id = p_txnTmpID;
734:
732: delete wip_mtl_allocations_temp
733: where transaction_temp_id = p_txnTmpID;
734:
735: if (l_logLevel <= wip_constants.full_logging) then
736: wip_logger.log('deleted ' || SQL%ROWCOUNT || ' rows from WMAT', l_returnStatus);
737: end if;
738:
739: if (l_logLevel <= wip_constants.trace_logging) then
740: wip_logger.exitPoint(p_procName => 'wip_cplProc_priv.processRepetitive',
736: wip_logger.log('deleted ' || SQL%ROWCOUNT || ' rows from WMAT', l_returnStatus);
737: end if;
738:
739: if (l_logLevel <= wip_constants.trace_logging) then
740: wip_logger.exitPoint(p_procName => 'wip_cplProc_priv.processRepetitive',
741: p_procReturnStatus => x_returnStatus,
742: p_msg => 'procedure success.',
743: x_returnStatus => l_returnStatus); --discard logging return status
744: end if;
746: exception
747: when fnd_api.g_exc_unexpected_error then
748: x_returnStatus := fnd_api.g_ret_sts_unexp_error;
749: if (l_logLevel <= wip_constants.trace_logging) then
750: wip_logger.exitPoint(p_procName => 'wip_cplProc_priv.processRepetitive',
751: p_procReturnStatus => x_returnStatus,
752: p_msg => l_errMsg,
753: x_returnStatus => l_returnStatus); --discard logging return status
754: end if;
757: fnd_msg_pub.add_exc_msg(p_pkg_name => 'wip_cplProc_priv',
758: p_procedure_name => 'processRepetitive',
759: p_error_text => SQLERRM);
760: if (l_logLevel <= wip_constants.trace_logging) then
761: wip_logger.exitPoint(p_procName => 'wip_cplProc_priv.processRepetitive',
762: p_procReturnStatus => x_returnStatus,
763: p_msg => 'unexpected error: ' || SQLERRM,
764: x_returnStatus => l_returnStatus); --discard logging return status
765: end if;
777: procedure processOverCpl(p_cplRec IN OUT NOCOPY completion_rec_t,
778: x_returnStatus OUT NOCOPY VARCHAR2) is
779:
780: l_errMsg VARCHAR2(240);
781: l_params wip_logger.param_tbl_t;
782: l_returnStatus VARCHAR2(1);
783: l_firstSchedID NUMBER;
784: l_lastSchedID NUMBER;
785: l_firstOpSeq NUMBER;
800: begin
801: if (l_logLevel <= wip_constants.trace_logging) then
802: fillCplParamTbl(p_cplRec => p_cplRec,
803: x_params => l_params);
804: wip_logger.entryPoint(p_procName => 'wip_cplProc_priv.processOverCpl',
805: p_params => l_params,
806: x_returnStatus => x_returnStatus);
807:
808: if(x_returnStatus <> fnd_api.g_ret_sts_success) then
910: end if;
911: end if;
912:
913: if (l_logLevel <= wip_constants.trace_logging) then
914: wip_logger.exitPoint(p_procName => 'wip_cplProc_priv.processOverCpl',
915: p_procReturnStatus => x_returnStatus,
916: p_msg => 'procedure success.',
917: x_returnStatus => l_returnStatus); --discard logging return status
918: end if;
918: end if;
919: exception
920: when fnd_api.g_exc_unexpected_error then
921: if (l_logLevel <= wip_constants.trace_logging) then
922: wip_logger.exitPoint(p_procName => 'wip_cplProc_priv.processOverCpl',
923: p_procReturnStatus => x_returnStatus,
924: p_msg => l_errMsg,
925: x_returnStatus => l_returnStatus); --discard logging return status
926: end if;
929: fnd_msg_pub.add_exc_msg(p_pkg_name => 'wip_cplProc_priv',
930: p_procedure_name => 'processOverCpl',
931: p_error_text => SQLERRM);
932: if (l_logLevel <= wip_constants.trace_logging) then
933: wip_logger.exitPoint(p_procName => 'wip_cplProc_priv.processOverCpl',
934: p_procReturnStatus => x_returnStatus,
935: p_msg => 'unexpected error: ' || SQLERRM,
936: x_returnStatus => l_returnStatus); --discard logging return status
937: end if;
1039: txnQty num_tbl_t,
1040: overCplQty num_tbl_t,
1041: orgID num_tbl_t);
1042: l_logLevel NUMBER := fnd_log.g_current_runtime_level;
1043: l_params wip_logger.param_tbl_t;
1044: l_lastOpSeq NUMBER;
1045: l_schedQty NUMBER;
1046: l_returnStatus VARCHAR2(1);
1047: l_errMsg VARCHAR2(2000);
1068: l_params(5).paramValue := p_repLineID;
1069: l_params(6).paramName := 'p_tblName';
1070: l_params(6).paramValue := p_tblName;
1071:
1072: wip_logger.entryPoint(p_procName => 'wip_cplProc_priv.preAllocateSchedules',
1073: p_params => l_params,
1074: x_returnStatus => x_returnStatus);
1075:
1076: if(x_returnStatus <> fnd_api.g_ret_sts_success) then
1079: end if;
1080:
1081: if(p_tblName = wip_constants.MTI_TBL) then
1082: if (l_logLevel <= wip_constants.full_logging) then
1083: wip_logger.log('.25', l_returnStatus);
1084: end if;
1085: open c_mtiTxns;
1086: fetch c_mtiTxns
1087: bulk collect into l_txnRecTbl.txnID,
1093: l_txnRecTbl.overCplQty,
1094: l_txnRecTbl.orgID;
1095: close c_mtiTxns;
1096: if (l_logLevel <= wip_constants.full_logging) then
1097: wip_logger.log('mti row count is ' || l_txnRecTbl.txnID.count, l_returnStatus);
1098: end if;
1099: else
1100: if (l_logLevel <= wip_constants.full_logging) then
1101: wip_logger.log('1', l_returnStatus);
1097: wip_logger.log('mti row count is ' || l_txnRecTbl.txnID.count, l_returnStatus);
1098: end if;
1099: else
1100: if (l_logLevel <= wip_constants.full_logging) then
1101: wip_logger.log('1', l_returnStatus);
1102: end if;
1103: open c_mmttTxns;
1104: fetch c_mmttTxns
1105: bulk collect into l_txnRecTbl.txnID,
1111: l_txnRecTbl.overCplQty,
1112: l_txnRecTbl.orgID;
1113: close c_mmttTxns;
1114: if (l_logLevel <= wip_constants.full_logging) then
1115: wip_logger.log('2', l_returnStatus);
1116: end if;
1117: end if;
1118:
1119: for i in 1..l_txnRecTbl.txnID.count loop
1117: end if;
1118:
1119: for i in 1..l_txnRecTbl.txnID.count loop
1120: if (l_logLevel <= wip_constants.full_logging) then
1121: wip_logger.log('processing cpl tmpID:' || l_txnRecTbl.txnID(i) || '; qty:' || l_txnRecTbl.priQty(i), l_returnStatus);
1122: end if;
1123:
1124: if(l_txnRecTbl.lastOpSeq(i) is null or l_txnRecTbl.lastOpSeq(i) < 0) then
1125: l_lastOpSeq := 1; --if no rtg, components have op_seq = 1
1127: l_lastOpSeq := l_txnRecTbl.lastOpSeq(i);
1128: end if;
1129:
1130: if (l_logLevel <= wip_constants.full_logging) then
1131: wip_logger.log('last op seq is ' || l_lastOpSeq, l_returnStatus);
1132: wip_logger.log('action is ' || l_lastOpSeq, l_returnStatus);
1133: end if;
1134: l_remainingQty := l_txnRecTbl.priQty(i);
1135:
1128: end if;
1129:
1130: if (l_logLevel <= wip_constants.full_logging) then
1131: wip_logger.log('last op seq is ' || l_lastOpSeq, l_returnStatus);
1132: wip_logger.log('action is ' || l_lastOpSeq, l_returnStatus);
1133: end if;
1134: l_remainingQty := l_txnRecTbl.priQty(i);
1135:
1136: if(p_txnActionID = wip_constants.cplassy_action) then
1186:
1187: l_lastSchedID := l_schedRecTbl.repSchedID(j);--the last schedule fetched is the last one open for completions
1188:
1189: if(l_logLevel <= wip_constants.full_logging) then
1190: wip_logger.log('processing sched ' || l_schedRecTbl.repSchedID(j) || ' w/open qty of ' || l_schedQty, l_returnStatus);
1191: wip_logger.log('availQty: ' || l_schedRecTbl.availQty(j), l_returnStatus);
1192: wip_logger.log('startQty: ' || l_startQty, l_returnStatus);
1193: wip_logger.log('tempQty: ' || l_schedRecTbl.tempQty(j), l_returnStatus);
1194: wip_logger.log('remainQty: ' || l_remainingQty, l_returnStatus);
1187: l_lastSchedID := l_schedRecTbl.repSchedID(j);--the last schedule fetched is the last one open for completions
1188:
1189: if(l_logLevel <= wip_constants.full_logging) then
1190: wip_logger.log('processing sched ' || l_schedRecTbl.repSchedID(j) || ' w/open qty of ' || l_schedQty, l_returnStatus);
1191: wip_logger.log('availQty: ' || l_schedRecTbl.availQty(j), l_returnStatus);
1192: wip_logger.log('startQty: ' || l_startQty, l_returnStatus);
1193: wip_logger.log('tempQty: ' || l_schedRecTbl.tempQty(j), l_returnStatus);
1194: wip_logger.log('remainQty: ' || l_remainingQty, l_returnStatus);
1195: end if;
1188:
1189: if(l_logLevel <= wip_constants.full_logging) then
1190: wip_logger.log('processing sched ' || l_schedRecTbl.repSchedID(j) || ' w/open qty of ' || l_schedQty, l_returnStatus);
1191: wip_logger.log('availQty: ' || l_schedRecTbl.availQty(j), l_returnStatus);
1192: wip_logger.log('startQty: ' || l_startQty, l_returnStatus);
1193: wip_logger.log('tempQty: ' || l_schedRecTbl.tempQty(j), l_returnStatus);
1194: wip_logger.log('remainQty: ' || l_remainingQty, l_returnStatus);
1195: end if;
1196:
1189: if(l_logLevel <= wip_constants.full_logging) then
1190: wip_logger.log('processing sched ' || l_schedRecTbl.repSchedID(j) || ' w/open qty of ' || l_schedQty, l_returnStatus);
1191: wip_logger.log('availQty: ' || l_schedRecTbl.availQty(j), l_returnStatus);
1192: wip_logger.log('startQty: ' || l_startQty, l_returnStatus);
1193: wip_logger.log('tempQty: ' || l_schedRecTbl.tempQty(j), l_returnStatus);
1194: wip_logger.log('remainQty: ' || l_remainingQty, l_returnStatus);
1195: end if;
1196:
1197: --if the revisions don't match...
1190: wip_logger.log('processing sched ' || l_schedRecTbl.repSchedID(j) || ' w/open qty of ' || l_schedQty, l_returnStatus);
1191: wip_logger.log('availQty: ' || l_schedRecTbl.availQty(j), l_returnStatus);
1192: wip_logger.log('startQty: ' || l_startQty, l_returnStatus);
1193: wip_logger.log('tempQty: ' || l_schedRecTbl.tempQty(j), l_returnStatus);
1194: wip_logger.log('remainQty: ' || l_remainingQty, l_returnStatus);
1195: end if;
1196:
1197: --if the revisions don't match...
1198: if(l_schedRecTbl.bomRev(j) <> l_txnRecTbl.revision(i) and
1206: --complete the lesser of the open quantity and the remaining transaction qty
1207:
1208: if(l_schedQty <> 0) then
1209: if (l_logLevel <= wip_constants.full_logging) then
1210: wip_logger.log('inserting into temp table; sched:' || l_schedRecTbl.repSchedID(j) || '; qty:' || l_schedQty, l_returnStatus);
1211: end if;
1212: insert into wip_mtl_allocations_temp
1213: (transaction_temp_id,
1214: completion_transaction_id,
1260: raise fnd_api.g_exc_unexpected_error; -- couldn't find any open schedules
1261: end if;
1262:
1263: if (l_logLevel <= wip_constants.full_logging) then
1264: wip_logger.log('txnID' || l_txnRecTbl.txnID(i), l_returnStatus);
1265: wip_logger.log('rmnQty' || l_remainingQty, l_returnStatus);
1266: wip_logger.log('schedID' || l_lastSchedID, l_returnStatus);
1267: end if;
1268:
1261: end if;
1262:
1263: if (l_logLevel <= wip_constants.full_logging) then
1264: wip_logger.log('txnID' || l_txnRecTbl.txnID(i), l_returnStatus);
1265: wip_logger.log('rmnQty' || l_remainingQty, l_returnStatus);
1266: wip_logger.log('schedID' || l_lastSchedID, l_returnStatus);
1267: end if;
1268:
1269: --in below stmt, transaction_quantity = old_qty + (conversion ratio * new primary_qty)
1262:
1263: if (l_logLevel <= wip_constants.full_logging) then
1264: wip_logger.log('txnID' || l_txnRecTbl.txnID(i), l_returnStatus);
1265: wip_logger.log('rmnQty' || l_remainingQty, l_returnStatus);
1266: wip_logger.log('schedID' || l_lastSchedID, l_returnStatus);
1267: end if;
1268:
1269: --in below stmt, transaction_quantity = old_qty + (conversion ratio * new primary_qty)
1270: update wip_mtl_allocations_temp
1274: where transaction_temp_id = l_txnRecTbl.txnID(i)
1275: and repetitive_schedule_id = l_lastSchedID;
1276:
1277: if(SQL%ROWCOUNT = 0) then
1278: wip_logger.log('l_txnRecTbl.overCplQty = '||to_char(l_txnRecTbl.overCplQty(i)), l_returnStatus);
1279:
1280: /* Fix for bug 5373061: We will reach here even if no overcompletion is involved. Back-dated
1281: transactions can pick up older schedules which do not have open quantity. Do not allocate
1282: to the older schedule. Throw error */
1292: end if;
1293: end if; /*Fix for Bug#6018877 - FP of bug#6004763*/
1294:
1295: if (l_logLevel <= wip_constants.full_logging) then
1296: wip_logger.log('update failed. inserting new row', l_returnStatus);
1297: end if;
1298: /* Fixed bug 3698513. Completion_transaction_id is a not null column
1299: * , so we have to insert a value into this column. This bug only
1300: * occur when overcompletion and available quantity is zero.
1338: end loop;
1339:
1340:
1341: if (l_logLevel <= wip_constants.trace_logging) then
1342: wip_logger.exitPoint(p_procName => 'wip_cplProc_priv.preAllocateSchedules',
1343: p_procReturnStatus => x_returnStatus,
1344: p_msg => 'procedure success.',
1345: x_returnStatus => l_returnStatus); --discard logging return status
1346: end if;
1348: when fnd_api.g_exc_unexpected_error then
1349: rollback to wipcplpb40;
1350: x_returnStatus := fnd_api.g_ret_sts_unexp_error;
1351: if (l_logLevel <= wip_constants.trace_logging) then
1352: wip_logger.exitPoint(p_procName => 'wip_cplProc_priv.preAllocateSchedules',
1353: p_procReturnStatus => x_returnStatus,
1354: p_msg => l_errMsg,
1355: x_returnStatus => l_returnStatus); --discard logging return status
1356: end if;
1359: fnd_msg_pub.add_exc_msg(p_pkg_name => 'wip_cplProc_priv',
1360: p_procedure_name => 'preAllocateSchedules',
1361: p_error_text => SQLERRM);
1362: if (l_logLevel <= wip_constants.trace_logging) then
1363: wip_logger.exitPoint(p_procName => 'wip_cplProc_priv.preAllocateSchedules',
1364: p_procReturnStatus => x_returnStatus,
1365: p_msg => 'unexpected error: ' || SQLERRM,
1366: x_returnStatus => l_returnStatus); --discard logging return status
1367: end if;
1396: l_schedID NUMBER;
1397: l_errMsg VARCHAR2(2000);
1398: l_returnStatus VARCHAR2(1);
1399: l_logLevel NUMBER := fnd_log.g_current_runtime_level;
1400: l_params wip_logger.param_tbl_t;
1401: begin
1402: --need to find the first open schedule. if we have pre-allocated completions, we have to see
1403: --which ones will be completed by the time this return is processed.
1404: if (l_logLevel <= wip_constants.trace_logging) then
1412: l_params(4).paramValue := p_repLineID;
1413: l_params(5).paramName := 'p_tblName';
1414: l_params(5).paramValue := p_tblName;
1415:
1416: wip_logger.entryPoint(p_procName => 'wip_cplProc_priv.preAllocateRets',
1417: p_params => l_params,
1418: x_returnStatus => x_returnStatus);
1419:
1420: if(x_returnStatus <> fnd_api.g_ret_sts_success) then
1425: for l_schedRec in c_openScheds loop
1426: l_schedID := l_schedRec.repetitive_schedule_id;
1427:
1428: if(l_logLevel <= wip_constants.full_logging) then
1429: wip_logger.log('l_schedRec.cplQty' || l_schedRec.cplQty, l_returnStatus);
1430: wip_logger.log('l_schedRec.tempQty' || l_schedRec.tempQty, l_returnStatus);
1431: wip_logger.log('l_schedRec.startQty' || l_schedRec.startQty, l_returnStatus);
1432: end if;
1433:
1426: l_schedID := l_schedRec.repetitive_schedule_id;
1427:
1428: if(l_logLevel <= wip_constants.full_logging) then
1429: wip_logger.log('l_schedRec.cplQty' || l_schedRec.cplQty, l_returnStatus);
1430: wip_logger.log('l_schedRec.tempQty' || l_schedRec.tempQty, l_returnStatus);
1431: wip_logger.log('l_schedRec.startQty' || l_schedRec.startQty, l_returnStatus);
1432: end if;
1433:
1434: if(greatest(l_schedRec.cplQty, 0) + nvl(l_schedRec.tempQty, 0) < l_schedRec.startQty) then
1427:
1428: if(l_logLevel <= wip_constants.full_logging) then
1429: wip_logger.log('l_schedRec.cplQty' || l_schedRec.cplQty, l_returnStatus);
1430: wip_logger.log('l_schedRec.tempQty' || l_schedRec.tempQty, l_returnStatus);
1431: wip_logger.log('l_schedRec.startQty' || l_schedRec.startQty, l_returnStatus);
1432: end if;
1433:
1434: if(greatest(l_schedRec.cplQty, 0) + nvl(l_schedRec.tempQty, 0) < l_schedRec.startQty) then
1435: if (l_logLevel <= wip_constants.full_logging) then
1432: end if;
1433:
1434: if(greatest(l_schedRec.cplQty, 0) + nvl(l_schedRec.tempQty, 0) < l_schedRec.startQty) then
1435: if (l_logLevel <= wip_constants.full_logging) then
1436: wip_logger.log('found open schedule' || l_schedID, l_returnStatus);
1437: end if;
1438: exit;
1439: end if;
1440: end loop;
1438: exit;
1439: end if;
1440: end loop;
1441: if (l_logLevel <= wip_constants.full_logging) then
1442: wip_logger.log('returning to schedule' || l_schedID, l_returnStatus);
1443: end if;
1444:
1445: if(p_tblName = wip_constants.MMTT_TBL) then
1446: insert into wip_mtl_allocations_temp
1516: and mti.transaction_action_id = wip_constants.retassy_action;
1517: end if;
1518:
1519: if (l_logLevel <= wip_constants.full_logging) then
1520: wip_logger.log('inserted ' || SQL%ROWCOUNT || ' rows', l_returnStatus);
1521: end if;
1522:
1523: x_returnStatus := fnd_api.g_ret_sts_success;
1524:
1522:
1523: x_returnStatus := fnd_api.g_ret_sts_success;
1524:
1525: if (l_logLevel <= wip_constants.trace_logging) then
1526: wip_logger.exitPoint(p_procName => 'wip_cplProc_priv.preAllocateRets',
1527: p_procReturnStatus => x_returnStatus,
1528: p_msg => 'success',
1529: x_returnStatus => l_returnStatus); --discard logging return status
1530: end if;
1532: exception
1533: when others then
1534: x_returnStatus := fnd_api.g_ret_sts_unexp_error;
1535: if (l_logLevel <= wip_constants.trace_logging) then
1536: wip_logger.exitPoint(p_procName => 'wip_cplProc_priv.preAllocateRets',
1537: p_procReturnStatus => x_returnStatus,
1538: p_msg => 'unexpected error: ' || SQLERRM,
1539: x_returnStatus => l_returnStatus); --discard logging return status
1540: end if;
1549: p_endDebug IN VARCHAR2,
1550: x_returnStatus OUT NOCOPY VARCHAR2) IS
1551:
1552: l_logLevel NUMBER := fnd_log.g_current_runtime_level;
1553: l_params wip_logger.param_tbl_t;
1554: l_returnStatus VARCHAR2(1);
1555: l_errMsg VARCHAR2(2000);
1556: begin
1557: savepoint wipcplpb40;
1559: if (l_logLevel <= wip_constants.trace_logging) then
1560: l_params(1).paramName := 'p_cplTxnID';
1561: l_params(1).paramValue := p_cplTxnID;
1562:
1563: wip_logger.entryPoint(p_procName => 'wip_cplProc_priv.preAllocateSchedules',
1564: p_params => l_params,
1565: x_returnStatus => x_returnStatus);
1566:
1567: if(x_returnStatus <> fnd_api.g_ret_sts_success) then
1589: raise fnd_api.g_exc_unexpected_error;
1590: end if;
1591:
1592: if (l_logLevel <= wip_constants.trace_logging) then
1593: wip_logger.exitPoint(p_procName => 'wip_cplProc_priv.preAllocateSchedules',
1594: p_procReturnStatus => x_returnStatus,
1595: p_msg => 'procedure success.',
1596: x_returnStatus => l_returnStatus); --discard logging return status
1597: if(fnd_api.to_boolean(p_endDebug)) then
1594: p_procReturnStatus => x_returnStatus,
1595: p_msg => 'procedure success.',
1596: x_returnStatus => l_returnStatus); --discard logging return status
1597: if(fnd_api.to_boolean(p_endDebug)) then
1598: wip_logger.cleanUp(x_returnStatus => l_returnStatus); --discard logging return status
1599: end if;
1600: end if;
1601:
1602: exception
1602: exception
1603: when fnd_api.g_exc_unexpected_error then
1604: rollback to wipcplpb40;
1605: if (l_logLevel <= wip_constants.trace_logging) then
1606: wip_logger.exitPoint(p_procName => 'wip_cplProc_priv.preAllocateSchedules',
1607: p_procReturnStatus => x_returnStatus,
1608: p_msg => l_errMsg,
1609: x_returnStatus => l_returnStatus); --discard logging return status
1610: if(fnd_api.to_boolean(p_endDebug)) then
1607: p_procReturnStatus => x_returnStatus,
1608: p_msg => l_errMsg,
1609: x_returnStatus => l_returnStatus); --discard logging return status
1610: if(fnd_api.to_boolean(p_endDebug)) then
1611: wip_logger.cleanUp(x_returnStatus => l_returnStatus); --discard logging return status
1612: end if;
1613: end if;
1614: when others then
1615: rollback to wipcplpb40;
1617: fnd_msg_pub.add_exc_msg(p_pkg_name => 'wip_cplProc_priv',
1618: p_procedure_name => 'preAllocateSchedules',
1619: p_error_text => SQLERRM);
1620: if (l_logLevel <= wip_constants.trace_logging) then
1621: wip_logger.exitPoint(p_procName => 'wip_cplProc_priv.preAllocateSchedules',
1622: p_procReturnStatus => x_returnStatus,
1623: p_msg => 'unexpected error: ' || SQLERRM,
1624: x_returnStatus => l_returnStatus); --discard logging return status
1625: if(fnd_api.to_boolean(p_endDebug)) then
1622: p_procReturnStatus => x_returnStatus,
1623: p_msg => 'unexpected error: ' || SQLERRM,
1624: x_returnStatus => l_returnStatus); --discard logging return status
1625: if(fnd_api.to_boolean(p_endDebug)) then
1626: wip_logger.cleanUp(x_returnStatus => l_returnStatus); --discard logging return status
1627: end if;
1628: end if;
1629: end preAllocateSchedules;
1630: */
1628: end if;
1629: end preAllocateSchedules;
1630: */
1631: procedure fillCplParamTbl(p_cplRec IN completion_rec_t,
1632: x_params OUT NOCOPY wip_logger.param_tbl_t) is
1633: begin
1634: x_params(1).paramName := 'p_cplRec.wipEntityType';
1635: x_params(1).paramValue := p_cplRec.wipEntityType;
1636: x_params(2).paramName := 'p_cplRec.wipEntityID';
1717: l_label_status VARCHAR2(1);
1718: l_msg_stack VARCHAR2(2000);
1719: l_process_phase VARCHAR2(3);
1720: l_return_status VARCHAR(1);
1721: l_params wip_logger.param_tbl_t;
1722: l_oc_rec wip_cplProc_priv.completion_rec_t;
1723: -- new variables for serialization
1724: l_op_seq NUMBER;
1725: l_step NUMBER;
1736: l_params(3).paramName := 'p_mtl_header_id';
1737: l_params(3).paramValue := p_mtl_header_id;
1738: l_params(4).paramName := 'p_oc_primary_qty';
1739: l_params(4).paramValue := p_oc_primary_qty;
1740: wip_logger.entryPoint(p_procName => 'wip_cplProc_priv.processOATxn',
1741: p_params => l_params,
1742: x_returnStatus => l_return_status);
1743: END IF;
1744: l_process_phase := '2';
1797: fnd_message.set_name('WIP', 'WIP_UPDATED_ROWS');
1798: fnd_message.set_token('ENTITY1', SQL%ROWCOUNT);
1799: fnd_message.set_token('ENTITY2', 'MTL_TRANSACTION_LOTS_INTERFACE');
1800: l_msg := fnd_message.get;
1801: wip_logger.log(p_msg => l_msg,
1802: x_returnStatus => l_return_status);
1803: END IF;
1804:
1805: /* IF Exp date null in MTL_LOT_NUMBERS should I leave it null */
1841: fnd_message.set_name('WIP', 'WIP_UPDATED_ROWS');
1842: fnd_message.set_token('ENTITY1', SQL%ROWCOUNT);
1843: fnd_message.set_token('ENTITY2', 'MTL_TRANSACTION_LOTS_INTERFACE');
1844: l_msg := fnd_message.get;
1845: wip_logger.log(p_msg => l_msg,
1846: x_returnStatus => l_return_status);
1847: END IF;
1848:
1849: END IF; -- (l_mti_lot_rec > 0)
1893: -- do not error out if label printing, only put warning message in log
1894: IF(x_returnStatus <> fnd_api.g_ret_sts_success) THEN
1895: wip_utilities.get_message_stack(p_msg => l_msg_stack);
1896: IF (l_log_level <= wip_constants.full_logging) then
1897: wip_logger.log(p_msg => 'An error has occurred with label printing.\n' ||
1898: 'The following error has occurred during ' ||
1899: 'printing: ' || l_msg_stack || '\n' ||
1900: 'Please check the Inventory log file for more ' ||
1901: 'information.',
1997:
1998: x_returnStatus := fnd_api.g_ret_sts_success;
1999: -- write to the log file
2000: IF (l_log_level <= wip_constants.trace_logging) THEN
2001: wip_logger.exitPoint(p_procName => 'wip_cplProc_priv.processOATxn',
2002: p_procReturnStatus => x_returnStatus,
2003: p_msg => 'procedure complete',
2004: x_returnStatus => l_return_status);
2005: END IF;
2003: p_msg => 'procedure complete',
2004: x_returnStatus => l_return_status);
2005: END IF;
2006: -- close log file
2007: wip_logger.cleanUp(x_returnStatus => l_return_status);
2008:
2009: EXCEPTION
2010: WHEN fnd_api.g_exc_unexpected_error THEN
2011: IF(c_cmp_txn%ISOPEN) THEN
2014: ROLLBACK TO SAVEPOINT s_oa_txn_proc;
2015: x_returnStatus := fnd_api.g_ret_sts_error;
2016:
2017: IF (l_log_level <= wip_constants.trace_logging) THEN
2018: wip_logger.exitPoint(p_procName => 'wip_cplProc_priv.processOATxn',
2019: p_procReturnStatus => x_returnStatus,
2020: p_msg => 'wip_cplProc_priv.processOATxn failed : '
2021: || l_process_phase,
2022: x_returnStatus => l_return_status);
2021: || l_process_phase,
2022: x_returnStatus => l_return_status);
2023: END IF;
2024: -- close log file
2025: wip_logger.cleanUp(x_returnStatus => l_return_status);
2026: WHEN others THEN
2027: IF(c_cmp_txn%ISOPEN) THEN
2028: CLOSE c_cmp_txn;
2029: END IF;
2036: fnd_message.set_token('MESSAGE', l_error_msg);
2037: fnd_msg_pub.add;
2038:
2039: IF (l_log_level <= wip_constants.trace_logging) THEN
2040: wip_logger.exitPoint(p_procName => 'wip_cplProc_priv.processOATxn',
2041: p_procReturnStatus => x_returnStatus,
2042: p_msg => l_error_msg || ' : ' || l_process_phase,
2043: x_returnStatus => l_return_status);
2044: END IF;
2042: p_msg => l_error_msg || ' : ' || l_process_phase,
2043: x_returnStatus => l_return_status);
2044: END IF;
2045: -- close log file
2046: wip_logger.cleanUp(x_returnStatus => l_return_status);
2047: END processOATxn;
2048: end wip_cplProc_priv;