[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;
296: l_paramCount := l_params.count;
297: l_params(l_paramCount + 1).paramName := 'p_txnTmpID';
298: l_params(l_paramCount + 1).paramValue := p_txnTmpID;
299:
300: wip_logger.entryPoint(p_procName => 'wip_cplProc_priv.processDiscrete',
301: p_params => l_params,
302: x_returnStatus => x_returnStatus);
303: if(x_returnStatus <> fnd_api.g_ret_sts_success) then
304: raise fnd_api.g_exc_unexpected_error;
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
468: end if;
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
476: end if;
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
489: end if;
499: l_firstSchedID NUMBER;
500: l_lastSchedID NUMBER;
501: l_schedRec schedule_rec_t;
502: l_status NUMBER;
503: l_params wip_logger.param_tbl_t;
504: l_paramCount NUMBER;
505: l_returnStatus VARCHAR2(1);
506: l_errMsg VARCHAR2(240);
507: l_nullObj system.wip_component_tbl_t := null;
556: l_paramCount := l_params.count;
557: l_params(l_paramCount + 1).paramName := 'p_txnTmpID';
558: l_params(l_paramCount + 1).paramValue := p_txnTmpID;
559:
560: wip_logger.entryPoint(p_procName => 'wip_cplProc_priv.processRepetitive',
561: p_params => l_params,
562: x_returnStatus => x_returnStatus);
563:
564: if(x_returnStatus <> fnd_api.g_ret_sts_success) then
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);
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;
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:
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
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
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;
709:
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
715: wip_logger.exitPoint(p_procName => 'wip_cplProc_priv.processRepetitive',
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
728: end if;
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
739: end if;
751: procedure processOverCpl(p_cplRec IN OUT NOCOPY completion_rec_t,
752: x_returnStatus OUT NOCOPY VARCHAR2) is
753:
754: l_errMsg VARCHAR2(240);
755: l_params wip_logger.param_tbl_t;
756: l_returnStatus VARCHAR2(1);
757: l_firstSchedID NUMBER;
758: l_lastSchedID NUMBER;
759: l_firstOpSeq NUMBER;
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,
780: x_returnStatus => x_returnStatus);
781:
782: if(x_returnStatus <> fnd_api.g_ret_sts_success) then
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
892: end if;
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
900: end if;
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
911: end if;
1013: txnQty num_tbl_t,
1014: overCplQty num_tbl_t,
1015: orgID num_tbl_t);
1016: l_logLevel NUMBER := fnd_log.g_current_runtime_level;
1017: l_params wip_logger.param_tbl_t;
1018: l_lastOpSeq NUMBER;
1019: l_schedQty NUMBER;
1020: l_returnStatus VARCHAR2(1);
1021: l_errMsg VARCHAR2(2000);
1042: l_params(5).paramValue := p_repLineID;
1043: l_params(6).paramName := 'p_tblName';
1044: l_params(6).paramValue := p_tblName;
1045:
1046: wip_logger.entryPoint(p_procName => 'wip_cplProc_priv.preAllocateSchedules',
1047: p_params => l_params,
1048: x_returnStatus => x_returnStatus);
1049:
1050: if(x_returnStatus <> fnd_api.g_ret_sts_success) then
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
1061: bulk collect into l_txnRecTbl.txnID,
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
1075: wip_logger.log('1', l_returnStatus);
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
1079: bulk collect into l_txnRecTbl.txnID,
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:
1093: for i in 1..l_txnRecTbl.txnID.count loop
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
1099: l_lastOpSeq := 1; --if no rtg, components have op_seq = 1
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);
1109:
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);
1109:
1110: if(p_txnActionID = wip_constants.cplassy_action) then
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);
1168: wip_logger.log('remainQty: ' || l_remainingQty, l_returnStatus);
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);
1168: wip_logger.log('remainQty: ' || l_remainingQty, l_returnStatus);
1169: end if;
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);
1168: wip_logger.log('remainQty: ' || l_remainingQty, l_returnStatus);
1169: end if;
1170:
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);
1168: wip_logger.log('remainQty: ' || l_remainingQty, l_returnStatus);
1169: end if;
1170:
1171: --if the revisions don't match...
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);
1168: wip_logger.log('remainQty: ' || l_remainingQty, l_returnStatus);
1169: end if;
1170:
1171: --if the revisions don't match...
1172: if(l_schedRecTbl.bomRev(j) <> l_txnRecTbl.revision(i) and
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,
1188: completion_transaction_id,
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;
1242:
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;
1242:
1243: --in below stmt, transaction_quantity = old_qty + (conversion ratio * new primary_qty)
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;
1242:
1243: --in below stmt, transaction_quantity = old_qty + (conversion ratio * new primary_qty)
1244: update wip_mtl_allocations_temp
1248: where transaction_temp_id = l_txnRecTbl.txnID(i)
1249: and repetitive_schedule_id = l_lastSchedID;
1250:
1251: if(SQL%ROWCOUNT = 0) then
1252: wip_logger.log('l_txnRecTbl.overCplQty = '||to_char(l_txnRecTbl.overCplQty(i)), l_returnStatus);
1253:
1254: /* Fix for bug 5373061: We will reach here even if no overcompletion is involved. Back-dated
1255: transactions can pick up older schedules which do not have open quantity. Do not allocate
1256: to the older schedule. Throw error */
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
1274: * occur when overcompletion and available quantity is zero.
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
1320: end if;
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
1330: end if;
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
1341: end if;
1370: l_schedID NUMBER;
1371: l_errMsg VARCHAR2(2000);
1372: l_returnStatus VARCHAR2(1);
1373: l_logLevel NUMBER := fnd_log.g_current_runtime_level;
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
1386: l_params(4).paramValue := p_repLineID;
1387: l_params(5).paramName := 'p_tblName';
1388: l_params(5).paramValue := p_tblName;
1389:
1390: wip_logger.entryPoint(p_procName => 'wip_cplProc_priv.preAllocateRets',
1391: p_params => l_params,
1392: x_returnStatus => x_returnStatus);
1393:
1394: if(x_returnStatus <> fnd_api.g_ret_sts_success) then
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;
1407:
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;
1407:
1408: if(greatest(l_schedRec.cplQty, 0) + nvl(l_schedRec.tempQty, 0) < l_schedRec.startQty) then
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;
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
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;
1414: end loop;
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
1420: insert into wip_mtl_allocations_temp
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;
1498:
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
1504: end if;
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
1514: end if;
1523: p_endDebug IN VARCHAR2,
1524: x_returnStatus OUT NOCOPY VARCHAR2) IS
1525:
1526: l_logLevel NUMBER := fnd_log.g_current_runtime_level;
1527: l_params wip_logger.param_tbl_t;
1528: l_returnStatus VARCHAR2(1);
1529: l_errMsg VARCHAR2(2000);
1530: begin
1531: savepoint wipcplpb40;
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',
1538: p_params => l_params,
1539: x_returnStatus => x_returnStatus);
1540:
1541: 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
1571: if(fnd_api.to_boolean(p_endDebug)) then
1568: p_procReturnStatus => x_returnStatus,
1569: p_msg => 'procedure success.',
1570: x_returnStatus => l_returnStatus); --discard logging return status
1571: if(fnd_api.to_boolean(p_endDebug)) then
1572: wip_logger.cleanUp(x_returnStatus => l_returnStatus); --discard logging return status
1573: end if;
1574: end if;
1575:
1576: exception
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
1584: if(fnd_api.to_boolean(p_endDebug)) then
1581: p_procReturnStatus => x_returnStatus,
1582: p_msg => l_errMsg,
1583: x_returnStatus => l_returnStatus); --discard logging return status
1584: if(fnd_api.to_boolean(p_endDebug)) then
1585: wip_logger.cleanUp(x_returnStatus => l_returnStatus); --discard logging return status
1586: end if;
1587: end if;
1588: when others then
1589: rollback to wipcplpb40;
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
1599: if(fnd_api.to_boolean(p_endDebug)) then
1596: p_procReturnStatus => x_returnStatus,
1597: p_msg => 'unexpected error: ' || SQLERRM,
1598: x_returnStatus => l_returnStatus); --discard logging return status
1599: if(fnd_api.to_boolean(p_endDebug)) then
1600: wip_logger.cleanUp(x_returnStatus => l_returnStatus); --discard logging return status
1601: end if;
1602: end if;
1603: end preAllocateSchedules;
1604: */
1602: end if;
1603: end preAllocateSchedules;
1604: */
1605: procedure fillCplParamTbl(p_cplRec IN completion_rec_t,
1606: x_params OUT NOCOPY wip_logger.param_tbl_t) is
1607: begin
1608: x_params(1).paramName := 'p_cplRec.wipEntityType';
1609: x_params(1).paramValue := p_cplRec.wipEntityType;
1610: x_params(2).paramName := 'p_cplRec.wipEntityID';
1691: l_label_status VARCHAR2(1);
1692: l_msg_stack VARCHAR2(2000);
1693: l_process_phase VARCHAR2(3);
1694: l_return_status VARCHAR(1);
1695: l_params wip_logger.param_tbl_t;
1696: l_oc_rec wip_cplProc_priv.completion_rec_t;
1697: -- new variables for serialization
1698: l_op_seq NUMBER;
1699: l_step NUMBER;
1707: l_params(3).paramName := 'p_mtl_header_id';
1708: l_params(3).paramValue := p_mtl_header_id;
1709: l_params(4).paramName := 'p_oc_primary_qty';
1710: l_params(4).paramValue := p_oc_primary_qty;
1711: wip_logger.entryPoint(p_procName => 'wip_cplProc_priv.processOATxn',
1712: p_params => l_params,
1713: x_returnStatus => l_return_status);
1714: END IF;
1715: l_process_phase := '2';
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 ' ||
1767: 'information.',
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);
1871: END IF;
1869: p_msg => 'procedure complete',
1870: x_returnStatus => l_return_status);
1871: END IF;
1872: -- close log file
1873: wip_logger.cleanUp(x_returnStatus => l_return_status);
1874:
1875: EXCEPTION
1876: WHEN fnd_api.g_exc_unexpected_error THEN
1877: IF(c_cmp_txn%ISOPEN) THEN
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,
1888: x_returnStatus => l_return_status);
1887: || l_process_phase,
1888: x_returnStatus => l_return_status);
1889: END IF;
1890: -- close log file
1891: wip_logger.cleanUp(x_returnStatus => l_return_status);
1892: WHEN others THEN
1893: IF(c_cmp_txn%ISOPEN) THEN
1894: CLOSE c_cmp_txn;
1895: END IF;
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);
1910: END IF;
1908: p_msg => l_error_msg || ' : ' || l_process_phase,
1909: x_returnStatus => l_return_status);
1910: END IF;
1911: -- close log file
1912: wip_logger.cleanUp(x_returnStatus => l_return_status);
1913: END processOATxn;
1914: end wip_cplProc_priv;