[Home] [Help]
1: package body wip_cplProc_priv as
2: /* $Header: wipcplpb.pls 120.13.12020000.3 2013/02/05 13:37:46 akuppa ship $ */
3:
4: ---------------
5: --private types
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;
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;
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;
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;
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',
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;
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;
502: fnd_message.set_token('JOBNAME', l_job_name);
503: fnd_msg_pub.add;
504: when others then
505: x_returnStatus := fnd_api.g_ret_sts_unexp_error;
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',
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;
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
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;
753: x_returnStatus => l_returnStatus); --discard logging return status
754: end if;
755: when others then
756: x_returnStatus := fnd_api.g_ret_sts_unexp_error;
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',
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;
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;
925: x_returnStatus => l_returnStatus); --discard logging return status
926: end if;
927: when others then
928: x_returnStatus := fnd_api.g_ret_sts_unexp_error;
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',
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;
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
1253: if(l_remainingQty <> 0) then -- over-completion/return
1254: if(l_lastSchedID is null) then
1255: /* Fix for bug 5373061: Passed missing token */
1256: fnd_message.set_name('WIP', 'WIP_INT_ERROR_NO_SCHED');
1257: fnd_message.set_token('ROUTINE', 'wip_cplProc_priv.preAllocateSchedules');
1258: fnd_msg_pub.add;
1259: l_errMsg := 'did not find any schedules.';
1260: raise fnd_api.g_exc_unexpected_error; -- couldn't find any open schedules
1261: end if;
1284: /* Fix for Bug#6018877 - FP of bug#6004763. Added the if condition check not to consider Return txn */
1285: if(p_txnActionID <> wip_constants.retassy_action) then
1286: if(l_txnRecTbl.overCplQty(i) IS NULL) then
1287: fnd_message.set_name('WIP', 'WIP_INT_ERROR_NO_SCHED');
1288: fnd_message.set_token('ROUTINE', 'wip_cplProc_priv.preAllocateSchedules');
1289: fnd_msg_pub.add;
1290: l_errMsg := 'did not find any schedules.';
1291: raise fnd_api.g_exc_unexpected_error; -- couldn't find any open schedules
1292: end if;
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;
1355: x_returnStatus => l_returnStatus); --discard logging return status
1356: end if;
1357: when others then
1358: x_returnStatus := fnd_api.g_ret_sts_unexp_error;
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',
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;
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
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;
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
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
1613: end if;
1614: when others then
1615: rollback to wipcplpb40;
1616: x_returnStatus := fnd_api.g_ret_sts_unexp_error;
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',
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
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;
1726: l_mti_lot_rec 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';
1936: l_oc_rec.lpnID := null; -- only used for LPN
1937: l_oc_rec.txnMode := WIP_CONSTANTS.ONLINE;
1938: l_oc_rec.overCplTxnID := l_cmp_txn.oc_txn_id;
1939:
1940: wip_cplProc_priv.processOverCpl(p_cplRec => l_oc_rec,
1941: x_returnStatus => x_returnStatus);
1942:
1943: IF(x_returnStatus <> fnd_api.g_ret_sts_success) THEN
1944: RAISE fnd_api.g_exc_unexpected_error;
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;
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);
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);
2023: END IF;
2024: -- close log file
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;
2044: END IF;
2045: -- close log file
2046: wip_logger.cleanUp(x_returnStatus => l_return_status);
2047: END processOATxn;
2048: end wip_cplProc_priv;