335: -- Start of API savepoint
336: SAVEPOINT Create_Pick_PUB;
337:
338: -- initialize message list
339: FND_MSG_PUB.initialize;
340:
341: IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
342: p_api_version_number,
343: l_api_name,
423: WHEN OTHERS THEN
424: fnd_message.set_name('CSP', 'CSP_UNEXPECTED_EXEC_ERRORS');
425: fnd_message.set_token ('ROUTINE', l_api_name, TRUE);
426: fnd_message.set_token ('SQLERRM', sqlerrm, TRUE);
427: fnd_msg_pub.add;
428: RAISE EXCP_USER_DEFINED;
429: END;
430: END IF;
431:
477: WHEN OTHERS THEN
478: fnd_message.set_name('CSP', 'CSP_UNEXPECTED_EXEC_ERRORS');
479: fnd_message.set_token ('ROUTINE', l_api_name, TRUE);
480: fnd_message.set_token ('SQLERRM', sqlerrm, TRUE);
481: fnd_msg_pub.add;
482: RAISE EXCP_USER_DEFINED;
483: END;
484: END LOOP;
485:
562:
563: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
564: fnd_message.set_name ('CSP', 'CSP_MO_DETAILING_ERROR');
565: fnd_message.set_token ('MOVE_ORDER_LINE_ID', to_char(mo_line_rec.line_id), FALSE);
566: fnd_msg_pub.add;
567:
568: for j in reverse 1..fnd_msg_pub.count_msg loop
569: fnd_msg_pub.get
570: ( j
564: fnd_message.set_name ('CSP', 'CSP_MO_DETAILING_ERROR');
565: fnd_message.set_token ('MOVE_ORDER_LINE_ID', to_char(mo_line_rec.line_id), FALSE);
566: fnd_msg_pub.add;
567:
568: for j in reverse 1..fnd_msg_pub.count_msg loop
569: fnd_msg_pub.get
570: ( j
571: , FND_API.G_FALSE
572: , t_msg_data
565: fnd_message.set_token ('MOVE_ORDER_LINE_ID', to_char(mo_line_rec.line_id), FALSE);
566: fnd_msg_pub.add;
567:
568: for j in reverse 1..fnd_msg_pub.count_msg loop
569: fnd_msg_pub.get
570: ( j
571: , FND_API.G_FALSE
572: , t_msg_data
573: , t_msg_dummy
751:
752: EXCEPTION
753: WHEN EXCP_USER_DEFINED THEN
754: Rollback to Create_Pick_PUB;
755: fnd_msg_pub.count_and_get
756: ( p_count => x_msg_count
757: , p_data => x_msg_data);
758: x_return_status := FND_API.G_RET_STS_ERROR;
759:
760: WHEN FND_API.G_EXC_ERROR THEN
761: JTF_PLSQL_API.HANDLE_EXCEPTIONS(
762: P_API_NAME => L_API_NAME
763: ,P_PKG_NAME => G_PKG_NAME
764: ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
765: ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
766: ,X_MSG_COUNT => X_MSG_COUNT
767: ,X_MSG_DATA => X_MSG_DATA
768: ,X_RETURN_STATUS => X_RETURN_STATUS);
769: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
770: JTF_PLSQL_API.HANDLE_EXCEPTIONS(
771: P_API_NAME => L_API_NAME
772: ,P_PKG_NAME => G_PKG_NAME
773: ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
774: ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
775: ,X_MSG_COUNT => X_MSG_COUNT
776: ,X_MSG_DATA => X_MSG_DATA
777: ,X_RETURN_STATUS => X_RETURN_STATUS);
779: Rollback to Create_Pick_PUB;
780: fnd_message.set_name('CSP', 'CSP_UNEXPECTED_EXEC_ERRORS');
781: fnd_message.set_token ('ROUTINE', l_api_name, TRUE);
782: fnd_message.set_token ('SQLERRM', sqlerrm, TRUE);
783: fnd_msg_pub.add;
784: fnd_msg_pub.count_and_get
785: ( p_count => x_msg_count
786: , p_data => x_msg_data);
787: x_return_status := fnd_api.g_ret_sts_error;
780: fnd_message.set_name('CSP', 'CSP_UNEXPECTED_EXEC_ERRORS');
781: fnd_message.set_token ('ROUTINE', l_api_name, TRUE);
782: fnd_message.set_token ('SQLERRM', sqlerrm, TRUE);
783: fnd_msg_pub.add;
784: fnd_msg_pub.count_and_get
785: ( p_count => x_msg_count
786: , p_data => x_msg_data);
787: x_return_status := fnd_api.g_ret_sts_error;
788: END create_pick;
923: IF (l_fm_number IS NULL AND l_to_number IS NOT NULL)
924: -- OR (l_fm_number IS NOT NULL AND l_to_number IS NULL)
925: OR (nvl(to_number(l_to_number), l_fm_number) < to_number(l_fm_number)) THEN
926: fnd_message.set_name ('CSP', 'CSP_INVALID_SERIAL_RANGE');
927: fnd_msg_pub.add;
928: CLOSE l_Get_Mmtt_Csr;
929: CLOSE l_Get_Serial_Numbers_Csr;
930: CLOSE l_Get_Picklist_Lines_Csr;
931: RAISE EXCP_USER_DEFINED;
1023: If sql%notfound Then
1024: fnd_message.set_name('CSP', 'CSP_UNEXPECTED_EXEC_ERRORS');
1025: fnd_message.set_token ('ROUTINE', l_api_name, FALSE);
1026: fnd_message.set_token ('SQLERRM', sqlerrm, TRUE);
1027: fnd_msg_pub.add;
1028: CLOSE l_Get_Serial_Numbers_Csr;
1029: RETURN FALSE;
1030: End If;
1031: End If;
1033:
1034: If l_Get_Serial_Numbers_Csr%ROWCOUNT = 0 THEN
1035: fnd_message.set_name ('CSP', 'CSP_PICK_SERIAL_LOT_FAILURE');
1036: fnd_message.set_token('PICKLIST_HEADER_ID', to_char(p_picklist_header_id), FALSE);
1037: fnd_msg_pub.add;
1038: CLOSE l_Get_Serial_Numbers_Csr;
1039: Return False;
1040: End If;
1041:
1053: x_return_status := fnd_api.g_ret_sts_success;
1054:
1055: -- initialize message list
1056: IF fnd_api.to_boolean(p_init_msg_list) THEN
1057: FND_MSG_PUB.initialize;
1058: END IF;
1059:
1060: -- Standard call to check for call compatibility.
1061: IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1068:
1069: IF p_organization_id IS NULL THEN
1070: fnd_message.set_name ('CSP', 'CSP_MISSING_PARAMETERS');
1071: fnd_message.set_token ('PARAMETER', 'p_organization_id', TRUE);
1072: fnd_msg_pub.add;
1073: RAISE EXCP_USER_DEFINED;
1074: ELSE
1075: -- Check whether the organizaton exists.
1076: BEGIN
1079: where organization_id = p_organization_id;
1080: EXCEPTION
1081: WHEN NO_DATA_FOUND THEN
1082: FND_MESSAGE.SET_NAME ('INV', 'INVALID ORGANIZATION');
1083: FND_MSG_PUB.ADD;
1084: RAISE EXCP_USER_DEFINED;
1085: WHEN OTHERS THEN
1086: fnd_message.set_name('CSP', 'CSP_UNEXPECTED_ERRORS');
1087: fnd_message.set_token('ERR_FIELD', 'p_organization_id', TRUE);
1086: fnd_message.set_name('CSP', 'CSP_UNEXPECTED_ERRORS');
1087: fnd_message.set_token('ERR_FIELD', 'p_organization_id', TRUE);
1088: fnd_message.set_token('ROUTINE', l_api_name, TRUE);
1089: fnd_message.set_token('TABLE', 'mtl_organizations', TRUE);
1090: FND_MSG_PUB.ADD;
1091: RAISE EXCP_USER_DEFINED;
1092: END;
1093: END IF;
1094:
1094:
1095: IF p_picklist_header_id IS NULL THEN
1096: fnd_message.set_name ('CSP', 'CSP_MISSING_PARAMETERS');
1097: fnd_message.set_token ('PARAMETER', 'p_picklist_header_id', TRUE);
1098: fnd_msg_pub.add;
1099: RAISE EXCP_USER_DEFINED;
1100: ELSE
1101: -- check whether the organizaton exists.
1102: BEGIN
1107: EXCEPTION
1108: WHEN NO_DATA_FOUND THEN
1109: fnd_message.set_name ('CSP', 'CSP_INVALID_PICKLIST_HEADER');
1110: fnd_message.set_token ('HEADER_ID', to_char(p_picklist_header_id), TRUE);
1111: fnd_msg_pub.add;
1112: RAISE EXCP_USER_DEFINED;
1113: WHEN OTHERS THEN
1114: fnd_message.set_name('CSP', 'CSP_UNEXPECTED_ERRORS');
1115: fnd_message.set_token('ERR_FIELD', 'p_picklist_header_id', TRUE);
1114: fnd_message.set_name('CSP', 'CSP_UNEXPECTED_ERRORS');
1115: fnd_message.set_token('ERR_FIELD', 'p_picklist_header_id', TRUE);
1116: fnd_message.set_token('ROUTINE', l_api_name, TRUE);
1117: fnd_message.set_token('TABLE', 'csp_picklist_headers', TRUE);
1118: FND_MSG_PUB.ADD;
1119: RAISE EXCP_USER_DEFINED;
1120: END;
1121: END IF;
1122: -- get all picklist lines
1148:
1149: IF l_Get_Mmtt_Csr%NOTFOUND THEN
1150: fnd_message.set_name ('CSP', 'CSP_NO_TXN_RECORD');
1151: fnd_message.set_token ('PICKLIST_ID', to_char(l_picklist_line_rec.picklist_line_id), TRUE);
1152: fnd_msg_pub.add;
1153: CLOSE l_Get_Mmtt_Csr;
1154: CLOSE l_Get_Picklist_Lines_Csr;
1155: RAISE EXCP_USER_DEFINED;
1156: END IF;
1170: IF not (transact_serial(l_lot_number_rec.serial_transaction_temp_id)) THEN
1171: fnd_message.set_name('CSP', 'CSP_UNEXPECTED_EXEC_ERRORS');
1172: fnd_message.set_token ('ROUTINE', l_api_name, TRUE);
1173: fnd_message.set_token ('SQLERRM', sqlerrm, TRUE);
1174: fnd_msg_pub.add;
1175: CLOSE l_Get_Mmtt_Csr;
1176: CLOSE l_Get_Picklist_Lines_Csr;
1177: RAISE EXCP_USER_DEFINED;
1178: END IF;
1211:
1212: IF l_Get_Mtlt_Csr%ROWCOUNT = 0 THEN
1213: fnd_message.set_name ('CSP', 'CSP_PICK_SERIAL_LOT_FAILURE');
1214: fnd_message.set_token('PICKLIST_HEADER_ID', to_char(p_picklist_header_id), FALSE);
1215: fnd_msg_pub.ADD;
1216: CLOSE l_Get_Mmtt_Csr;
1217: CLOSE l_Get_Picklist_Lines_Csr;
1218: RAISE EXCP_USER_DEFINED;
1219: ELSE
1230: IF not (transact_serial(l_picklist_line_rec.transaction_temp_id)) THEN
1231: fnd_message.set_name('CSP', 'CSP_UNEXPECTED_EXEC_ERRORS');
1232: fnd_message.set_token ('ROUTINE', l_api_name, TRUE);
1233: fnd_message.set_token ('SQLERRM', sqlerrm, TRUE);
1234: fnd_msg_pub.add;
1235: CLOSE l_Get_Mmtt_Csr;
1236: CLOSE l_Get_Picklist_Lines_Csr;
1237: RAISE EXCP_USER_DEFINED;
1238: END IF;
1273:
1274: EXCEPTION
1275: WHEN EXCP_USER_DEFINED THEN
1276: Rollback to Confirm_Pick_PUB;
1277: fnd_msg_pub.count_and_get
1278: ( p_count => x_msg_count
1279: , p_data => x_msg_data);
1280: x_return_status := FND_API.G_RET_STS_ERROR;
1281:
1282: WHEN FND_API.G_EXC_ERROR THEN
1283: JTF_PLSQL_API.HANDLE_EXCEPTIONS(
1284: P_API_NAME => L_API_NAME
1285: ,P_PKG_NAME => G_PKG_NAME
1286: ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
1287: ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
1288: ,X_MSG_COUNT => X_MSG_COUNT
1289: ,X_MSG_DATA => X_MSG_DATA
1290: ,X_RETURN_STATUS => X_RETURN_STATUS);
1291: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1292: JTF_PLSQL_API.HANDLE_EXCEPTIONS(
1293: P_API_NAME => L_API_NAME
1294: ,P_PKG_NAME => G_PKG_NAME
1295: ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
1296: ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
1297: ,X_MSG_COUNT => X_MSG_COUNT
1298: ,X_MSG_DATA => X_MSG_DATA
1299: ,X_RETURN_STATUS => X_RETURN_STATUS);
1301: Rollback to Confirm_Pick_PUB;
1302: fnd_message.set_name('CSP', 'CSP_UNEXPECTED_EXEC_ERRORS');
1303: fnd_message.set_token ('ROUTINE', l_api_name, TRUE);
1304: fnd_message.set_token ('SQLERRM', sqlerrm, TRUE);
1305: fnd_msg_pub.add;
1306: fnd_msg_pub.count_and_get
1307: ( p_count => x_msg_count
1308: , p_data => x_msg_data);
1309: x_return_status := fnd_api.g_ret_sts_error;
1302: fnd_message.set_name('CSP', 'CSP_UNEXPECTED_EXEC_ERRORS');
1303: fnd_message.set_token ('ROUTINE', l_api_name, TRUE);
1304: fnd_message.set_token ('SQLERRM', sqlerrm, TRUE);
1305: fnd_msg_pub.add;
1306: fnd_msg_pub.count_and_get
1307: ( p_count => x_msg_count
1308: , p_data => x_msg_data);
1309: x_return_status := fnd_api.g_ret_sts_error;
1310:
1554: x_return_status := fnd_api.g_ret_sts_success;
1555:
1556: -- initialize message list
1557: IF fnd_api.to_boolean(p_init_msg_list) THEN
1558: FND_MSG_PUB.initialize;
1559: END IF;
1560:
1561: -- Standard call to check for call compatibility.
1562: IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1570: IF p_validation_level = FND_API.G_VALID_LEVEL_FULL THEN
1571: IF p_organization_id IS NULL THEN
1572: fnd_message.set_name ('CSP', 'CSP_MISSING_PARAMETERS');
1573: fnd_message.set_token ('PARAMETER', 'p_organization_id', TRUE);
1574: fnd_msg_pub.add;
1575: RAISE EXCP_USER_DEFINED;
1576: ELSE
1577: -- check whether the organizaton exists.
1578: BEGIN
1581: where organization_id = p_organization_id;
1582: EXCEPTION
1583: WHEN NO_DATA_FOUND THEN
1584: FND_MESSAGE.SET_NAME ('INV', 'INVALID ORGANIZATION');
1585: FND_MSG_PUB.ADD;
1586: RAISE EXCP_USER_DEFINED;
1587: WHEN OTHERS THEN
1588: fnd_message.set_name('CSP', 'CSP_UNEXPECTED_ERRORS');
1589: fnd_message.set_token('ERR_FIELD', 'p_organization_id', TRUE);
1588: fnd_message.set_name('CSP', 'CSP_UNEXPECTED_ERRORS');
1589: fnd_message.set_token('ERR_FIELD', 'p_organization_id', TRUE);
1590: fnd_message.set_token('ROUTINE', l_api_name, TRUE);
1591: fnd_message.set_token('TABLE', 'mtl_organizations', TRUE);
1592: FND_MSG_PUB.ADD;
1593: RAISE EXCP_USER_DEFINED;
1594: END;
1595: END IF;
1596: END IF;
1599: FETCH l_ml_records INTO l_csp_mtltxn_rec;
1600:
1601: IF l_ml_records%NOTFOUND THEN
1602: fnd_message.set_name ('CSP', 'CSP_NO_MO_TXN_RECORD');
1603: fnd_msg_pub.add;
1604: CLOSE l_ml_records;
1605: RAISE EXCP_USER_DEFINED;
1606: END IF;
1607:
1649:
1650: EXCEPTION
1651: WHEN EXCP_USER_DEFINED THEN
1652: Rollback to Update_Misc_MMTT_PUB;
1653: fnd_msg_pub.count_and_get
1654: ( p_count => x_msg_count
1655: , p_data => x_msg_data);
1656: x_return_status := FND_API.G_RET_STS_ERROR;
1657: WHEN FND_API.G_EXC_ERROR THEN
1657: WHEN FND_API.G_EXC_ERROR THEN
1658: JTF_PLSQL_API.HANDLE_EXCEPTIONS(
1659: P_API_NAME => L_API_NAME
1660: ,P_PKG_NAME => G_PKG_NAME
1661: ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
1662: ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
1663: ,X_MSG_COUNT => X_MSG_COUNT
1664: ,X_MSG_DATA => X_MSG_DATA
1665: ,X_RETURN_STATUS => X_RETURN_STATUS);
1666: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1667: JTF_PLSQL_API.HANDLE_EXCEPTIONS(
1668: P_API_NAME => L_API_NAME
1669: ,P_PKG_NAME => G_PKG_NAME
1670: ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
1671: ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
1672: ,X_MSG_COUNT => X_MSG_COUNT
1673: ,X_MSG_DATA => X_MSG_DATA
1674: ,X_RETURN_STATUS => X_RETURN_STATUS);
1676: Rollback to Update_Misc_MMTT_PUB;
1677: fnd_message.set_name('CSP', 'CSP_UNEXPECTED_EXEC_ERRORS');
1678: fnd_message.set_token ('ROUTINE', l_api_name, TRUE);
1679: fnd_message.set_token ('SQLERRM', sqlerrm, TRUE);
1680: fnd_msg_pub.add;
1681: fnd_msg_pub.count_and_get
1682: ( p_count => x_msg_count
1683: , p_data => x_msg_data);
1684: x_return_status := fnd_api.g_ret_sts_error;
1677: fnd_message.set_name('CSP', 'CSP_UNEXPECTED_EXEC_ERRORS');
1678: fnd_message.set_token ('ROUTINE', l_api_name, TRUE);
1679: fnd_message.set_token ('SQLERRM', sqlerrm, TRUE);
1680: fnd_msg_pub.add;
1681: fnd_msg_pub.count_and_get
1682: ( p_count => x_msg_count
1683: , p_data => x_msg_data);
1684: x_return_status := fnd_api.g_ret_sts_error;
1685:
1738: x_return_status := fnd_api.g_ret_sts_success;
1739:
1740: -- initialize message list
1741: IF fnd_api.to_boolean(p_init_msg_list) THEN
1742: FND_MSG_PUB.initialize;
1743: END IF;
1744:
1745: -- Standard call to check for call compatibility.
1746: IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1753:
1754: IF p_organization_id IS NULL THEN
1755: fnd_message.set_name ('CSP', 'CSP_MISSING_PARAMETERS');
1756: fnd_message.set_token ('PARAMETER', 'p_organization_id', TRUE);
1757: fnd_msg_pub.add;
1758: RAISE EXCP_USER_DEFINED;
1759: ELSE
1760: -- check whether the organizaton exists.
1761: BEGIN
1764: WHERE organization_id = p_organization_id;
1765: EXCEPTION
1766: WHEN NO_DATA_FOUND THEN
1767: FND_MESSAGE.SET_NAME ('INV', 'INVALID ORGANIZATION');
1768: FND_MSG_PUB.ADD;
1769: RAISE EXCP_USER_DEFINED;
1770: WHEN OTHERS THEN
1771: fnd_message.set_name('CSP', 'CSP_UNEXPECTED_ERRORS');
1772: fnd_message.set_token('ERR_FIELD', 'p_organization_id', TRUE);
1771: fnd_message.set_name('CSP', 'CSP_UNEXPECTED_ERRORS');
1772: fnd_message.set_token('ERR_FIELD', 'p_organization_id', TRUE);
1773: fnd_message.set_token('ROUTINE', l_api_name, TRUE);
1774: fnd_message.set_token('TABLE', 'mtl_organizations', TRUE);
1775: FND_MSG_PUB.ADD;
1776: RAISE EXCP_USER_DEFINED;
1777: END;
1778: END IF;
1779:
1800:
1801: EXCEPTION
1802: WHEN EXCP_USER_DEFINED THEN
1803: Rollback to Save_Pick_PUB;
1804: fnd_msg_pub.count_and_get
1805: ( p_count => x_msg_count
1806: , p_data => x_msg_data);
1807: x_return_status := FND_API.G_RET_STS_ERROR;
1808:
1809: WHEN FND_API.G_EXC_ERROR THEN
1810: JTF_PLSQL_API.HANDLE_EXCEPTIONS(
1811: P_API_NAME => L_API_NAME
1812: ,P_PKG_NAME => G_PKG_NAME
1813: ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
1814: ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
1815: ,X_MSG_COUNT => X_MSG_COUNT
1816: ,X_MSG_DATA => X_MSG_DATA
1817: ,X_RETURN_STATUS => X_RETURN_STATUS);
1818: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1819: JTF_PLSQL_API.HANDLE_EXCEPTIONS(
1820: P_API_NAME => L_API_NAME
1821: ,P_PKG_NAME => G_PKG_NAME
1822: ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
1823: ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
1824: ,X_MSG_COUNT => X_MSG_COUNT
1825: ,X_MSG_DATA => X_MSG_DATA
1826: ,X_RETURN_STATUS => X_RETURN_STATUS);
1828: Rollback to Save_Pick_PUB;
1829: fnd_message.set_name('CSP', 'CSP_UNEXPECTED_EXEC_ERRORS');
1830: fnd_message.set_token ('ROUTINE', l_api_name, TRUE);
1831: fnd_message.set_token ('SQLERRM', sqlerrm, TRUE);
1832: fnd_msg_pub.add;
1833: fnd_msg_pub.count_and_get
1834: ( p_count => x_msg_count
1835: , p_data => x_msg_data);
1836: x_return_status := fnd_api.g_ret_sts_error;
1829: fnd_message.set_name('CSP', 'CSP_UNEXPECTED_EXEC_ERRORS');
1830: fnd_message.set_token ('ROUTINE', l_api_name, TRUE);
1831: fnd_message.set_token ('SQLERRM', sqlerrm, TRUE);
1832: fnd_msg_pub.add;
1833: fnd_msg_pub.count_and_get
1834: ( p_count => x_msg_count
1835: , p_data => x_msg_data);
1836: x_return_status := fnd_api.g_ret_sts_error;
1837: