[Home] [Help]
1: package body wip_cplProc_priv as
2: /* $Header: wipcplpb.pls 120.12 2007/09/17 19:53:45 vjambhek 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;
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;
477: fnd_message.set_name('INV', 'INV_WIP_WORK_ORDER_LOCKED');
478: fnd_msg_pub.add;
479: when others then
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',
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;
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
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;
727: x_returnStatus => l_returnStatus); --discard logging return status
728: end if;
729: when others then
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',
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;
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;
899: x_returnStatus => l_returnStatus); --discard logging return status
900: end if;
901: when others then
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',
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;
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
1227: if(l_remainingQty <> 0) then -- over-completion/return
1228: if(l_lastSchedID is null) then
1229: /* Fix for bug 5373061: Passed missing token */
1230: fnd_message.set_name('WIP', 'WIP_INT_ERROR_NO_SCHED');
1231: fnd_message.set_token('ROUTINE', 'wip_cplProc_priv.preAllocateSchedules');
1232: fnd_msg_pub.add;
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;
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;
1264: l_errMsg := 'did not find any schedules.';
1265: raise fnd_api.g_exc_unexpected_error; -- couldn't find any open schedules
1266: 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
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;
1329: x_returnStatus => l_returnStatus); --discard logging return status
1330: end if;
1331: when others then
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',
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;
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
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;
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
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
1587: end if;
1588: when others then
1589: rollback to wipcplpb40;
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',
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
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;
1700: BEGIN
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';
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);
1808:
1809: IF(x_returnStatus <> fnd_api.g_ret_sts_success) THEN
1810: RAISE fnd_api.g_exc_unexpected_error;
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;
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);
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);
1889: END IF;
1890: -- close log file
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;
1910: END IF;
1911: -- close log file
1912: wip_logger.cleanUp(x_returnStatus => l_return_status);
1913: END processOATxn;
1914: end wip_cplProc_priv;