[Home] [Help]
161: l_api_name CONSTANT VARCHAR2 (30) := 'CREATE_SAMPLES';
162: l_api_version CONSTANT NUMBER := 3.0;
163: l_msg_count NUMBER :=0;
164: l_msg_data VARCHAR2(2000);
165: l_return_status VARCHAR2(1):=FND_API.G_RET_STS_SUCCESS;
166: l_samples_val_rec GMD_SAMPLES%ROWTYPE;
167: l_qc_samples_rec GMD_SAMPLES%ROWTYPE;
168: l_qc_samples_out_rec GMD_SAMPLES%ROWTYPE;
169: l_sample_spec_disp GMD_SAMPLE_SPEC_DISP%ROWTYPE;
273: --dbms_output.put_line('Entered Procedure CREATE_SAMPLES');
274:
275: /* Standard call to check for call compatibility. */
276:
277: IF NOT FND_API.Compatible_API_CALL
278: (l_api_version , p_api_version , l_api_name , G_PKG_NAME)
279: THEN
280: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
281: END IF;
276:
277: IF NOT FND_API.Compatible_API_CALL
278: (l_api_version , p_api_version , l_api_name , G_PKG_NAME)
279: THEN
280: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
281: END IF;
282:
283: /* Initialize message list if p_int_msg_list is set TRUE. */
284: IF FND_API.to_boolean(p_init_msg_list)
280: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
281: END IF;
282:
283: /* Initialize message list if p_int_msg_list is set TRUE. */
284: IF FND_API.to_boolean(p_init_msg_list)
285: THEN
286: FND_MSG_PUB.Initialize;
287: END IF;
288:
287: END IF;
288:
289: -- Initialize API return Parameters
290:
291: x_return_status := FND_API.G_RET_STS_SUCCESS;
292: l_samples_val_rec := p_qc_samples_rec;
293:
294:
295: -- Validate User Name Parameter
299: IF NVL(l_user_id, -1) < 0
300: THEN
301: GMD_API_PUB.Log_Message('GMD_INVALID_USER_NAME',
302: 'L_USER_NAME', p_user_name);
303: RAISE FND_API.G_EXC_ERROR;
304: ELSE
305: -- Added below for BUG 2711643. Hverddin
306: GMD_API_PUB.SET_USER_CONTEXT(p_user_id => l_user_id,
307: x_return_status => l_return_status);
305: -- Added below for BUG 2711643. Hverddin
306: GMD_API_PUB.SET_USER_CONTEXT(p_user_id => l_user_id,
307: x_return_status => l_return_status);
308:
309: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
310: RAISE FND_API.G_EXC_ERROR;
311: END IF;
312:
313: l_samples_val_rec.created_by := l_user_id;
306: GMD_API_PUB.SET_USER_CONTEXT(p_user_id => l_user_id,
307: x_return_status => l_return_status);
308:
309: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
310: RAISE FND_API.G_EXC_ERROR;
311: END IF;
312:
313: l_samples_val_rec.created_by := l_user_id;
314: l_samples_val_rec.last_updated_by := l_user_id;
317:
318: -- Validate organization_id Passed.
319: IF (l_samples_val_rec.organization_id IS NULL) THEN
320: GMD_API_PUB.Log_Message('GMD_ORGN_CODE_REQD');
321: RAISE FND_API.G_EXC_ERROR;
322: END IF;
323:
324:
325: -- Validate RETAIN_AS Passed.
325: -- Validate RETAIN_AS Passed.
326: IF ((l_samples_val_rec.sample_disposition NOT IN ('0RT', '0PL'))
327: AND (l_samples_val_rec.retain_as IS NOT NULL)) THEN
328: GMD_API_PUB.Log_Message('GMD_PLANNED_RETAINED_SAMPLES');
329: RAISE FND_API.G_EXC_ERROR;
330: END IF;
331:
332: -- Determine Type of Doc Sequencing defined for sample no.
333: -- Bug 4165704: doc numbering now kept in quality parameters table
347: , x_orgn_found => found );
348:
349: IF (l_return_status <> 'S') THEN
350: GMD_API_PUB.Log_Message('GMD_QM_ORG_PARAMETER');
351: RAISE FND_API.G_EXC_ERROR;
352: END IF;
353:
354: l_assign_type := quality_config.sample_assignment_type;
355:
382: );
383:
384: --dbms_output.put_line('after validate, before return stat check');
385:
386: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
387: RAISE FND_API.G_EXC_ERROR;
388: END IF;
389:
390: --dbms_output.put_line('after validate, after return stat check');
383:
384: --dbms_output.put_line('after validate, before return stat check');
385:
386: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
387: RAISE FND_API.G_EXC_ERROR;
388: END IF;
389:
390: --dbms_output.put_line('after validate, after return stat check');
391:
413: x_return_status => l_return_status,
414: x_msg_data => l_msg_data
415: )THEN
416: GMD_API_PUB.Log_Message('GMD_SPEC_NOT_FOUND');
417: RAISE FND_API.G_EXC_ERROR;
418: END IF;
419:
420: IF l_qc_samples_rec.source IN ('I','C','W','S') AND p_qc_samples_rec.sample_inv_trans_ind IS NULL THEN
421:
443: IF ((l_qc_samples_rec.lot_number IS NULL)
444: AND ((l_qc_samples_rec.parent_lot_number IS NULL) AND (G_CHILD_LOT_FLAG = 'Y'))) THEN
445: GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
446: 'WHAT', 'lot_number');
447: RAISE FND_API.G_EXC_ERROR;
448:
449: END IF;
450:
451:
497: ELSIF (l_qc_samples_rec.sample_disposition = '1P') THEN
498: l_sample_active_cnt := 1;
499: ELSE
500: GMD_API_PUB.Log_Message('GMD_SAMPLE_DISPOSITION_INVALID');
501: RAISE FND_API.G_EXC_ERROR;
502: END IF;
503:
504: IF l_sampling_events.sampling_event_id is NULL THEN
505:
603:
604: IF NOT GMD_SAMPLING_EVENTS_PVT.insert_row (
605: p_sampling_events => l_sampling_events,
606: x_sampling_events => l_sampling_events_out) THEN
607: RAISE FND_API.G_EXC_ERROR;
608: END IF;
609:
610: --dbms_output.put_line('after insert se row ');
611:
628: GMD_API_PUB.Log_Message('GMD_LOCKING_FAILURE',
629: 'l_table_name', 'GMD_SAMPLING_EVENTS',
630: 'l_column_name','SAMPLING_EVENT_ID',
631: 'l_key_value', l_sampling_events.sampling_event_id);
632: RAISE FND_API.G_EXC_ERROR;
633: ELSE
634: -- Bug 3401377: added retain_as, archived_taken, reserved_taken to sampling
635: -- event table for MPL
636: IF ((l_qc_samples_rec.retain_as = ( 'A'))
659:
660: IF NOT GMD_SAMPLING_EVENTS_PVT.fetch_row(
661: p_sampling_events => l_sampling_events,
662: x_sampling_events => l_sampling_events_out) THEN
663: RAISE FND_API.G_EXC_ERROR;
664: END IF;
665:
666: -- Bug 2987571: make sure sample instance is saved to new sample
667: -- Select from the sampling events Table
701:
702: IF NOT GMD_SAMPLES_PVT.insert_row (
703: p_samples => l_qc_samples_rec,
704: x_samples => l_qc_samples_out_rec) THEN
705: RAISE FND_API.G_EXC_ERROR;
706:
707: END IF;
708:
709:
732: x_return_status => l_return_status);
733:
734: -- dbms_output.put_line('RES Return Status => ' || l_return_status);
735:
736: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
737: RAISE FND_API.G_EXC_ERROR;
738: END IF;
739:
740: --dbms_output.put_line('end RES Return Status => ' || l_return_status);
733:
734: -- dbms_output.put_line('RES Return Status => ' || l_return_status);
735:
736: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
737: RAISE FND_API.G_EXC_ERROR;
738: END IF;
739:
740: --dbms_output.put_line('end RES Return Status => ' || l_return_status);
741:
802: END IF; -- source = 'S'
803:
804: IF (l_qc_samples_out_rec.source_subinventory IS NULL ) THEN
805: GMD_API_PUB.Log_Message('GMD_QM_NO_INVENTORY_TRANS_API2');
806: RAISE FND_API.G_EXC_ERROR;
807: ELSIF (l_qc_samples_out_rec.receipt_line_id IS NULL ) THEN
808: GMD_API_PUB.Log_Message('GMD_QM_NO_INVENTORY_TRANS_API');
809: RAISE FND_API.G_EXC_ERROR;
810: END IF;
805: GMD_API_PUB.Log_Message('GMD_QM_NO_INVENTORY_TRANS_API2');
806: RAISE FND_API.G_EXC_ERROR;
807: ELSIF (l_qc_samples_out_rec.receipt_line_id IS NULL ) THEN
808: GMD_API_PUB.Log_Message('GMD_QM_NO_INVENTORY_TRANS_API');
809: RAISE FND_API.G_EXC_ERROR;
810: END IF;
811:
812: --END IF; -- end Bug 3516802
813:
813:
814: -- Bug 3491783: if whse is not specified, can not generate inv trans
815: ELSIF (l_qc_samples_out_rec.subinventory IS NULL) THEN
816: GMD_API_PUB.Log_Message('GMD_QM_WHSE_REQ_INV');
817: RAISE FND_API.G_EXC_ERROR;
818: END IF; -- end bug
819:
820:
821: GMD_SAMPLES_GRP.create_inv_txn
830:
831: -- dbms_output.put_line('end create inv txn Return Status => ' || l_return_status);
832:
833: IF (l_return_status <> 'S') THEN
834: RAISE FND_API.G_EXC_ERROR;
835: END IF;
836: -- Bug 3401377 : if planned sample, do not generate the inventory transaction (from MPL)
837: ELSIF l_qc_samples_out_rec.source = 'W' AND
838: l_qc_samples_out_rec.sample_disposition <> '0PL' AND
851: -- FETCH Cur_replenish_whse_plant INTO l_qc_samples_out_rec.source_whse ;
852: -- IF Cur_replenish_whse_plant%NOTFOUND THEN
853: -- CLOSE Cur_replenish_whse_plant;
854: -- GMD_API_PUB.Log_Message('GMD_REPLENISH_WHSE_NOT_FOUND');
855: -- RAISE FND_API.G_EXC_ERROR;
856: -- END IF;
857: -- CLOSE Cur_replenish_whse_plant;
858: -- ELSE
859: -- CLOSE Cur_replenish_whse;
881: x_message_data => l_msg_data
882: );
883:
884: IF (l_return_status <> 'S') THEN
885: RAISE FND_API.G_EXC_ERROR;
886: END IF;
887:
888:
889: GMD_SAMPLES_GRP.post_wip_txn
891: x_return_status => l_return_status
892: );
893:
894: IF (l_return_status <> 'S') THEN
895: RAISE FND_API.G_EXC_ERROR;
896: END IF;
897:
898: -- bug# 2995114
899: -- added create_inv_txn to decrease the inventory which was increased by create_wip_txn/post_wip_txn
909: );
910:
911:
912: IF (l_return_status <> 'S') THEN
913: RAISE FND_API.G_EXC_ERROR;
914: END IF;
915:
916: END IF; -- IF l_batch_status <> 4 then
917:
917:
918: END IF;
919: END IF;
920: -- Standard Check of p_commit.
921: IF FND_API.to_boolean(p_commit) THEN
922: COMMIT WORK;
923: END IF;
924:
925: -- Set return Parameters
935: --dbms_output.put_line('The end');
936:
937:
938: EXCEPTION
939: WHEN FND_API.G_EXC_ERROR THEN
940: ROLLBACK TO CREATE_SAMPLES;
941: x_return_status := FND_API.G_RET_STS_ERROR;
942: FND_MSG_PUB.Count_AND_GET ( p_encoded => FND_API.G_FALSE
943: , p_count => x_msg_count
937:
938: EXCEPTION
939: WHEN FND_API.G_EXC_ERROR THEN
940: ROLLBACK TO CREATE_SAMPLES;
941: x_return_status := FND_API.G_RET_STS_ERROR;
942: FND_MSG_PUB.Count_AND_GET ( p_encoded => FND_API.G_FALSE
943: , p_count => x_msg_count
944: , p_data => x_msg_data
945: );
938: EXCEPTION
939: WHEN FND_API.G_EXC_ERROR THEN
940: ROLLBACK TO CREATE_SAMPLES;
941: x_return_status := FND_API.G_RET_STS_ERROR;
942: FND_MSG_PUB.Count_AND_GET ( p_encoded => FND_API.G_FALSE
943: , p_count => x_msg_count
944: , p_data => x_msg_data
945: );
946:
943: , p_count => x_msg_count
944: , p_data => x_msg_data
945: );
946:
947: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
948: ROLLBACK TO CREATE_SAMPLES;
949: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
950: FND_MSG_PUB.Count_AND_GET ( p_encoded=> FND_API.G_FALSE
951: , p_count => x_msg_count
945: );
946:
947: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
948: ROLLBACK TO CREATE_SAMPLES;
949: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
950: FND_MSG_PUB.Count_AND_GET ( p_encoded=> FND_API.G_FALSE
951: , p_count => x_msg_count
952: , p_data => x_msg_data
953: );
946:
947: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
948: ROLLBACK TO CREATE_SAMPLES;
949: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
950: FND_MSG_PUB.Count_AND_GET ( p_encoded=> FND_API.G_FALSE
951: , p_count => x_msg_count
952: , p_data => x_msg_data
953: );
954:
954:
955:
956:
957: WHEN OTHERS THEN
958: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
959: ROLLBACK TO CREATE_SAMPLES;
960: FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME
961: , l_api_name
962: );
960: FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME
961: , l_api_name
962: );
963:
964: FND_MSG_PUB.Count_AND_GET ( p_encoded=> FND_API.G_FALSE
965: , p_count => x_msg_count
966: , p_data => x_msg_data
967: );
968:
1217: -- end Bug #3401377 : added get spec for location, resource and stability samples
1218: ELSE
1219: GMD_API_PUB.Log_Message('GMD_SAMPLE_SOURCE_INVALID',
1220: 'l_source', p_samples_rec.source);
1221: RAISE FND_API.G_EXC_ERROR;
1222: END IF;
1223:
1224: RETURN FALSE;
1225:
1224: RETURN FALSE;
1225:
1226: EXCEPTION
1227:
1228: WHEN FND_API.G_EXC_ERROR THEN
1229: RETURN FALSE;
1230: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1231: RETURN FALSE;
1232: WHEN OTHERS THEN
1226: EXCEPTION
1227:
1228: WHEN FND_API.G_EXC_ERROR THEN
1229: RETURN FALSE;
1230: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1231: RETURN FALSE;
1232: WHEN OTHERS THEN
1233: RETURN FALSE;
1234:
1250: l_api_name CONSTANT VARCHAR2 (30) := 'DELETE_SAMPLES';
1251: l_api_version CONSTANT NUMBER := 3.0;
1252: l_msg_count NUMBER :=0;
1253: l_msg_data VARCHAR2(2000);
1254: l_return_status VARCHAR2(1):=FND_API.G_RET_STS_SUCCESS;
1255: l_qc_samples_out_rec GMD_SAMPLES%ROWTYPE;
1256: l_qc_samples_rec GMD_SAMPLES%ROWTYPE;
1257: l_rowid VARCHAR2(10);
1258: l_test_type VARCHAR2(10);
1267: SAVEPOINT DELETE_SAMPLES;
1268:
1269: -- Standard call to check for call compatibility.
1270:
1271: IF NOT FND_API.Compatible_API_CALL
1272: (l_api_version , p_api_version , l_api_name , G_PKG_NAME)
1273: THEN
1274: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1275: END IF;
1270:
1271: IF NOT FND_API.Compatible_API_CALL
1272: (l_api_version , p_api_version , l_api_name , G_PKG_NAME)
1273: THEN
1274: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1275: END IF;
1276:
1277: -- Initialize message list if p_int_msg_list is set TRUE.
1278: IF FND_API.to_boolean(p_init_msg_list)
1274: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1275: END IF;
1276:
1277: -- Initialize message list if p_int_msg_list is set TRUE.
1278: IF FND_API.to_boolean(p_init_msg_list)
1279: THEN
1280: FND_MSG_PUB.Initialize;
1281: END IF;
1282:
1281: END IF;
1282:
1283: -- Initialize API return Parameters
1284:
1285: l_return_status := FND_API.G_RET_STS_SUCCESS;
1286:
1287: -- Validate User Name Parameter
1288:
1289: GMA_GLOBAL_GRP.Get_Who ( p_user_name => p_user_name
1292: IF NVL(l_user_id, -1) < 0
1293: THEN
1294: GMD_API_PUB.Log_Message('GMD_INVALID_USER_NAME',
1295: 'l_user_name', p_user_name);
1296: RAISE FND_API.G_EXC_ERROR;
1297: END IF;
1298:
1299: -- Check Required Fields Present
1300:
1305: -- GMD_API_PUB.Log_Message('GMD_SAMPLE_SOURCE_INVALID');
1306: /* GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
1307: 'SAMPLE NO', ' IS NULL');*/
1308: GMD_API_PUB.Log_Message('GMD_SAMPLE_NUM_NULL');
1309: RAISE FND_API.G_EXC_ERROR;
1310: END IF;
1311:
1312: IF ( p_qc_samples_rec.organization_id is NULL) THEN
1313: GMD_API_PUB.Log_Message('GMD_SAMPLE_ORGN_CODE_REQD');
1310: END IF;
1311:
1312: IF ( p_qc_samples_rec.organization_id is NULL) THEN
1313: GMD_API_PUB.Log_Message('GMD_SAMPLE_ORGN_CODE_REQD');
1314: RAISE FND_API.G_EXC_ERROR;
1315: END IF;
1316:
1317: END IF; -- Key Sample values Present
1318:
1322: IF NOT GMD_SAMPLES_PVT.fetch_row (
1323: p_samples => p_qc_samples_rec,
1324: x_samples => l_qc_samples_out_rec) THEN
1325: -- dbms_output.put_line('Sample Record Not Found');
1326: RAISE FND_API.G_EXC_ERROR;
1327: END IF;
1328:
1329: -- Validate that the Sample Header is Not Already Marked For Purge
1330:
1332: GMD_API_PUB.Log_Message('GMD_RECORD_DELETE_MARKED',
1333: 'l_table_name', 'GMD_SAMPLES',
1334: 'l_column_name', 'SAMPLE_ID',
1335: 'l_key_value', l_qc_samples_out_rec.sample_id);
1336: RAISE FND_API.G_EXC_ERROR;
1337: END IF;
1338:
1339:
1340: -- Mark this record for Purge, this routine will also lock the row.
1349: 'l_table_name', 'GMD_SAMPLES',
1350: 'l_column_name','SAMPLE_ID',
1351: 'l_key_value', l_qc_samples_out_rec.sample_id);
1352:
1353: RAISE FND_API.G_EXC_ERROR;
1354:
1355: END IF;
1356:
1357: -- If the sample dispostion is not Retain or Cancel
1366: GMD_API_PUB.Log_Message('GMD_LOCKING_FAILURE',
1367: 'l_table_name', 'GMD_SAMPLING_EVENTS',
1368: 'l_column_name','SAMPLING_EVENT_ID',
1369: 'l_key_value', l_qc_samples_out_rec.sampling_event_id);
1370: RAISE FND_API.G_EXC_ERROR;
1371: ELSE
1372:
1373: -- Update the sampling events Table
1374: UPDATE GMD_SAMPLING_EVENTS
1381:
1382: END IF; -- Sample Disposition Not Retain OR Cancel
1383:
1384: -- Standard Check of p_commit.
1385: IF FND_API.to_boolean(p_commit)
1386: THEN
1387: COMMIT WORK;
1388: END IF;
1389:
1390:
1391: x_return_status := l_return_status;
1392:
1393: EXCEPTION
1394: WHEN FND_API.G_EXC_ERROR THEN
1395: ROLLBACK TO DELETE_SAMPLES;
1396: x_return_status := FND_API.G_RET_STS_ERROR;
1397: FND_MSG_PUB.Count_AND_GET ( p_encoded => FND_API.G_FALSE
1398: , p_count => x_msg_count
1392:
1393: EXCEPTION
1394: WHEN FND_API.G_EXC_ERROR THEN
1395: ROLLBACK TO DELETE_SAMPLES;
1396: x_return_status := FND_API.G_RET_STS_ERROR;
1397: FND_MSG_PUB.Count_AND_GET ( p_encoded => FND_API.G_FALSE
1398: , p_count => x_msg_count
1399: , p_data => x_msg_data
1400: );
1393: EXCEPTION
1394: WHEN FND_API.G_EXC_ERROR THEN
1395: ROLLBACK TO DELETE_SAMPLES;
1396: x_return_status := FND_API.G_RET_STS_ERROR;
1397: FND_MSG_PUB.Count_AND_GET ( p_encoded => FND_API.G_FALSE
1398: , p_count => x_msg_count
1399: , p_data => x_msg_data
1400: );
1401:
1398: , p_count => x_msg_count
1399: , p_data => x_msg_data
1400: );
1401:
1402: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1403: ROLLBACK TO DELETE_SAMPLES;
1404: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1405: FND_MSG_PUB.Count_AND_GET ( p_encoded=> FND_API.G_FALSE
1406: , p_count => x_msg_count
1400: );
1401:
1402: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1403: ROLLBACK TO DELETE_SAMPLES;
1404: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1405: FND_MSG_PUB.Count_AND_GET ( p_encoded=> FND_API.G_FALSE
1406: , p_count => x_msg_count
1407: , p_data => x_msg_data
1408: );
1401:
1402: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1403: ROLLBACK TO DELETE_SAMPLES;
1404: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1405: FND_MSG_PUB.Count_AND_GET ( p_encoded=> FND_API.G_FALSE
1406: , p_count => x_msg_count
1407: , p_data => x_msg_data
1408: );
1409:
1409:
1410:
1411:
1412: WHEN OTHERS THEN
1413: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1414: ROLLBACK TO DELETE_SAMPLES;
1415: FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME
1416: , l_api_name
1417: );
1415: FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME
1416: , l_api_name
1417: );
1418:
1419: FND_MSG_PUB.Count_AND_GET ( p_encoded=> FND_API.G_FALSE
1420: , p_count => x_msg_count
1421: , p_data => x_msg_data
1422: );
1423:
1499: BEGIN
1500:
1501: -- Assign API local Variables;
1502: l_sample_rec := p_sample_rec;
1503: l_return_status := FND_API.G_RET_STS_SUCCESS;
1504:
1505: -- We should only be validating W,I,C,S source type which
1506: -- all reqire the ite to exist.
1507:
1506: -- all reqire the ite to exist.
1507:
1508: IF (l_sample_rec.inventory_item_id IS NULL) THEN
1509: GMD_API_PUB.Log_Message('GMD_SPEC_ITEM_REQD');
1510: RAISE FND_API.G_EXC_ERROR;
1511: ELSE
1512: -- Validate that the item is valid.
1513: -- Bug 4165704: validation is now done with call to get_item_values
1514: -- OPEN c_item_controls;
1515: -- FETCH c_item_controls INTO l_status_ctl, l_lot_ctl , l_sublot_ctl, l_item_loct_ctl;
1516: -- IF (c_item_controls%NOTFOUND) THEN
1517: -- CLOSE c_item_controls;
1518: -- GMD_API_PUB.Log_Message('GMD_SPEC_ITEM_NOT_FOUND');
1519: -- RAISE FND_API.G_EXC_ERROR;
1520: -- END IF;
1521: -- CLOSE c_item_controls;
1522:
1523: l_sample_display.organization_id := p_sample_rec.organization_id;
1527:
1528: -- test for whether an item was found
1529: IF l_sample_display.item_number IS NULL THEN
1530: GMD_API_PUB.Log_Message('GMD_SPEC_ITEM_NOT_FOUND');
1531: RAISE FND_API.G_EXC_ERROR;
1532:
1533: ELSE
1534: l_lot_ctl := l_sample_display.lot_control_code ;
1535: G_LOT_CTL := l_sample_display.lot_control_code ; -- 528854
1549: /* IF ((l_sample_rec.lot_number IS NULL)
1550: AND ((l_sample_rec.parent_lot_number IS NULL) AND (l_sample_display.child_lot_flag = 'Y'))) THEN
1551: GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
1552: 'WHAT', 'lot_number');
1553: RAISE FND_API.G_EXC_ERROR;
1554:
1555: END IF; */
1556:
1557: -- Bug 4165704: Logic changed for new parent lot/lot instead of lot/sublot
1560: OR (l_sample_rec.parent_lot_number IS NOT NULL)) THEN
1561:
1562: IF ( l_child_lot_flag = 'N') AND ( l_sample_rec.parent_lot_number IS NOT NULL) THEN
1563: GMD_API_PUB.Log_Message('GMD_QM_PARENT_LOT_NULL');
1564: RAISE FND_API.G_EXC_ERROR;
1565: END IF; -- If item is not child lot controlled parent lot shouldn't exist.
1566:
1567:
1568: OPEN c_item_lot(l_child_lot_flag); --srakrish bug 5687499: Passing the child lot flags to the cursor.
1570: IF (c_item_lot%NOTFOUND) THEN
1571: CLOSE c_item_lot;
1572: GMD_API_PUB.Log_Message('GMD_ITEM_LOT_NOT_FOUND',
1573: 'LOT_NUMBER', l_sample_rec.lot_number);
1574: RAISE FND_API.G_EXC_ERROR;
1575: END IF;
1576: CLOSE c_item_lot;
1577:
1578:
1584: --IF (c_item_sublot%NOTFOUND) THEN
1585: --CLOSE c_item_sublot;
1586: --GMD_API_PUB.Log_Message('GMD_ITEM_SUBLOT_NOT_FOUND',
1587: -- 'SUBLOT_NO', l_sample_rec.sublot_no);
1588: --RAISE FND_API.G_EXC_ERROR;
1589: --END IF;
1590: --CLOSE c_item_sublot;
1591: --l_sample_rec.lot_id := l_lot_id;
1592: --END IF; -- validating lot/sublot
1592: --END IF; -- validating lot/sublot
1593: --END IF; -- if item is lot/sublot controlled.
1594: ELSIF ( l_child_lot_flag = 'N') THEN --srakrish bug 5687499: Included the else part to check for the lot when the item is not child lot controlled. Lot is a required field.
1595: GMD_API_PUB.Log_Message('GMD_QM_LOT_REQUIRED');
1596: RAISE FND_API.G_EXC_ERROR;
1597: END IF; -- If item is lot controlled an lot_id specified.
1598: END IF; --lot control = 2
1599:
1600: -- Bug 4165704: added grade control check here
1599:
1600: -- Bug 4165704: added grade control check here
1601: IF (l_sample_display.grade_control_flag = 'N' AND p_grade is NOT NULL) THEN
1602: GMD_API_PUB.Log_Message('GMD_GRADE_MUST_NULL');
1603: RAISE FND_API.G_EXC_ERROR;
1604: END IF;
1605:
1606: -- Bug 5283854 : added revision control check here
1607: IF l_sample_display.Revision_qty_control_code = 2 then
1606: -- Bug 5283854 : added revision control check here
1607: IF l_sample_display.Revision_qty_control_code = 2 then
1608: IF (l_sample_rec.revision IS NULL) THEN
1609: GMD_API_PUB.Log_Message('GMD_API_REVISION_CTRL');
1610: RAISE FND_API.G_EXC_ERROR;
1611:
1612: END IF;
1613:
1614: END IF;
1619: x_return_status := l_return_status;
1620:
1621: EXCEPTION
1622:
1623: WHEN FND_API.G_EXC_ERROR THEN
1624: x_return_status := FND_API.G_RET_STS_ERROR ;
1625: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1626: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1627: WHEN OTHERS THEN
1620:
1621: EXCEPTION
1622:
1623: WHEN FND_API.G_EXC_ERROR THEN
1624: x_return_status := FND_API.G_RET_STS_ERROR ;
1625: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1626: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1627: WHEN OTHERS THEN
1628: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1621: EXCEPTION
1622:
1623: WHEN FND_API.G_EXC_ERROR THEN
1624: x_return_status := FND_API.G_RET_STS_ERROR ;
1625: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1626: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1627: WHEN OTHERS THEN
1628: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1629:
1622:
1623: WHEN FND_API.G_EXC_ERROR THEN
1624: x_return_status := FND_API.G_RET_STS_ERROR ;
1625: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1626: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1627: WHEN OTHERS THEN
1628: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1629:
1630: END VALIDATE_ITEM_CONTROLS;
1624: x_return_status := FND_API.G_RET_STS_ERROR ;
1625: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1626: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1627: WHEN OTHERS THEN
1628: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1629:
1630: END VALIDATE_ITEM_CONTROLS;
1631:
1632:
1659:
1660: BEGIN
1661:
1662: -- Assign API local Variables;
1663: l_return_status := FND_API.G_RET_STS_SUCCESS;
1664:
1665: -- This Procedure Assumes that the whse and item have been validated.
1666:
1667:
1670: -- Validate the location
1671: IF (l_locator_control = 1) THEN
1672: IF (p_sample_rec.locator_id IS NOT NULL) THEN
1673: GMD_API_PUB.Log_Message('GMD_LOCATION_MUST_NULL');
1674: RAISE FND_API.G_EXC_ERROR;
1675: END IF;
1676: ELSIF (l_locator_control > 1 ) THEN -- Item is location controlled.
1677: IF (p_sample_rec.locator_id IS NOT NULL) THEN
1678: -- Check that Location exist in MTL_ITEM_LOCATIONS
1681: IF (c_subinventory_loct%NOTFOUND) THEN
1682: -- CLOSE c_subinventory_loct;
1683: GMD_API_PUB.Log_Message('GMD_LOCT_NOT_FOUND',
1684: 'LOCATION', p_sample_rec.locator_id);
1685: -- RAISE FND_API.G_EXC_ERROR;
1686: END IF;
1687: CLOSE c_subinventory_loct;
1688: ELSE -- location CANNOT NULL
1689: GMD_API_PUB.Log_Message('GMD_LOCATION_MUST_NULL');
1686: END IF;
1687: CLOSE c_subinventory_loct;
1688: ELSE -- location CANNOT NULL
1689: GMD_API_PUB.Log_Message('GMD_LOCATION_MUST_NULL');
1690: RAISE FND_API.G_EXC_ERROR;
1691: END IF; -- location IS NOT NULL
1692: END IF; -- l_locator_control
1693:
1694:
1696: x_return_status := l_return_status;
1697:
1698: EXCEPTION
1699:
1700: WHEN FND_API.G_EXC_ERROR THEN
1701: x_return_status := FND_API.G_RET_STS_ERROR ;
1702: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1703: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1704: WHEN OTHERS THEN
1697:
1698: EXCEPTION
1699:
1700: WHEN FND_API.G_EXC_ERROR THEN
1701: x_return_status := FND_API.G_RET_STS_ERROR ;
1702: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1703: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1704: WHEN OTHERS THEN
1705: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1698: EXCEPTION
1699:
1700: WHEN FND_API.G_EXC_ERROR THEN
1701: x_return_status := FND_API.G_RET_STS_ERROR ;
1702: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1703: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1704: WHEN OTHERS THEN
1705: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1706:
1699:
1700: WHEN FND_API.G_EXC_ERROR THEN
1701: x_return_status := FND_API.G_RET_STS_ERROR ;
1702: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1703: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1704: WHEN OTHERS THEN
1705: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1706:
1707: END VALIDATE_INV_SAMPLE;
1701: x_return_status := FND_API.G_RET_STS_ERROR ;
1702: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1703: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1704: WHEN OTHERS THEN
1705: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1706:
1707: END VALIDATE_INV_SAMPLE;
1708:
1709:
1812: BEGIN
1813:
1814:
1815: -- Assign API local Variables;
1816: l_return_status := FND_API.G_RET_STS_SUCCESS;
1817: l_sample_rec := p_sample_rec;
1818:
1819:
1820: -- For WIP sample the batch_id must be specified.
1819:
1820: -- For WIP sample the batch_id must be specified.
1821: IF (p_sample_rec.batch_id IS NULL ) THEN
1822: GMD_API_PUB.Log_Message('GMD_NO_WIP_PARAM');
1823: RAISE FND_API.G_EXC_ERROR;
1824: END IF;
1825:
1826: ----------------------------------------------
1827: -- Derive Values Using Batch_id
1836: l_sample_rec.routing_id;
1837: IF (c_batch%NOTFOUND) THEN
1838: CLOSE c_batch;
1839: GMD_API_PUB.Log_Message('GMD_BATCH_NOT_FOUND');
1840: RAISE FND_API.G_EXC_ERROR;
1841: END IF;
1842: CLOSE c_batch;
1843:
1844: END IF;
1856: FETCH c_formulaline_id INTO l_dummy;
1857: IF (c_formulaline_id%NOTFOUND) THEN
1858: CLOSE c_formulaline_id;
1859: GMD_API_PUB.Log_Message('GMD_FORMULA_LINE_NOT_FOUND');
1860: RAISE FND_API.G_EXC_ERROR;
1861: END IF;
1862: CLOSE c_formulaline_id;
1863: END IF;
1864:
1868: FETCH c_material_detail_id INTO l_dummy;
1869: IF (c_material_detail_id%NOTFOUND) THEN
1870: CLOSE c_material_detail_id;
1871: GMD_API_PUB.Log_Message('GMD_MATERIAL_DTL_NOT_FOUND');
1872: RAISE FND_API.G_EXC_ERROR;
1873: END IF;
1874: CLOSE c_material_detail_id;
1875: END IF;
1876:
1886: INTO l_sample_rec.step_no,l_sample_rec.oprn_id;
1887: IF (c_batchstep%NOTFOUND) THEN
1888: CLOSE c_batchstep;
1889: GMD_API_PUB.Log_Message('GMD_BATCH_STEP_NOT_FOUND');
1890: RAISE FND_API.G_EXC_ERROR;
1891: END IF;
1892: CLOSE c_batchstep;
1893:
1894: END IF;
1901: INTO l_dummy;
1902: IF (c_oprn%NOTFOUND) THEN
1903: CLOSE c_oprn;
1904: GMD_API_PUB.Log_Message('GMD_BATCH_STEP_NOT_FOUND');
1905: RAISE FND_API.G_EXC_ERROR;
1906: END IF;
1907: CLOSE c_oprn;
1908: END IF;
1909:
1913:
1914:
1915: EXCEPTION
1916:
1917: WHEN FND_API.G_EXC_ERROR THEN
1918: x_return_status := FND_API.G_RET_STS_ERROR ;
1919: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1920: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1921: WHEN OTHERS THEN
1914:
1915: EXCEPTION
1916:
1917: WHEN FND_API.G_EXC_ERROR THEN
1918: x_return_status := FND_API.G_RET_STS_ERROR ;
1919: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1920: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1921: WHEN OTHERS THEN
1922: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1915: EXCEPTION
1916:
1917: WHEN FND_API.G_EXC_ERROR THEN
1918: x_return_status := FND_API.G_RET_STS_ERROR ;
1919: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1920: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1921: WHEN OTHERS THEN
1922: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1923:
1916:
1917: WHEN FND_API.G_EXC_ERROR THEN
1918: x_return_status := FND_API.G_RET_STS_ERROR ;
1919: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1920: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1921: WHEN OTHERS THEN
1922: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1923:
1924: END VALIDATE_WIP_SAMPLE;
1918: x_return_status := FND_API.G_RET_STS_ERROR ;
1919: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1920: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1921: WHEN OTHERS THEN
1922: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1923:
1924: END VALIDATE_WIP_SAMPLE;
1925:
1926: PROCEDURE VALIDATE_CUST_SAMPLE
2020: BEGIN
2021:
2022:
2023: -- Assign API local Variables;
2024: l_return_status := FND_API.G_RET_STS_SUCCESS;
2025:
2026: -- For A Customer Sample Source the Cust_id
2027: -- Must be Specified. This also validates the Org_id
2028:
2027: -- Must be Specified. This also validates the Org_id
2028:
2029: IF (p_sample_rec.cust_id IS NULL) THEN
2030: GMD_API_PUB.Log_Message('GMD_CUSTOMER_REQD');
2031: RAISE FND_API.G_EXC_ERROR;
2032: ELSE
2033: OPEN c_cust;
2034: FETCH c_cust
2035: INTO l_dummy;
2035: INTO l_dummy;
2036: IF (c_cust%NOTFOUND) THEN
2037: CLOSE c_cust;
2038: GMD_API_PUB.Log_Message('GMD_CUSTOMER_NOT_FOUND');
2039: RAISE FND_API.G_EXC_ERROR;
2040: END IF;
2041: CLOSE c_cust;
2042: END IF;
2043:
2049: INTO l_dummy;
2050: IF (c_ship_to%NOTFOUND) THEN
2051: CLOSE c_ship_to;
2052: GMD_API_PUB.Log_Message('GMD_SHIP_TO_NOT_FOUND');
2053: RAISE FND_API.G_EXC_ERROR;
2054: END IF;
2055: CLOSE c_ship_to;
2056: END IF;
2057:
2065: IF (c_order%NOTFOUND)
2066: THEN
2067: CLOSE c_order;
2068: GMD_API_PUB.Log_Message('GMD_ORDER_NOT_FOUND');
2069: RAISE FND_API.G_EXC_ERROR;
2070: END IF;
2071: CLOSE c_order;
2072: END IF;
2073:
2078: AND p_sample_rec.order_id IS NOT NULL) THEN
2079:
2080: GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
2081: 'WHAT', 'the order_id must not be NULL');
2082: RAISE FND_API.G_EXC_ERROR;
2083: ELSE
2084:
2085: OPEN c_order_line;
2086: FETCH c_order_line
2087: INTO l_dummy;
2088: IF (c_order_line%NOTFOUND) THEN
2089: CLOSE c_order_line;
2090: GMD_API_PUB.Log_Message('GMD_ORDER_LINE_NOT_FOUND');
2091: RAISE FND_API.G_EXC_ERROR;
2092: END IF;
2093: CLOSE c_order_line;
2094: END IF; */
2095:
2097: IF (p_sample_rec.order_id IS NULL) THEN
2098: /*GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
2099: 'WHAT', 'the order_id must not be NULL');*/
2100: GMD_API_PUB.Log_Message('GMD_ORDER_ID_MUST_NOT_NULL');
2101: RAISE FND_API.G_EXC_ERROR;
2102: END IF;
2103:
2104: OPEN c_order_line;
2105: FETCH c_order_line
2107:
2108: IF (c_order_line%NOTFOUND) THEN
2109: CLOSE c_order_line;
2110: GMD_API_PUB.Log_Message('GMD_ORDER_LINE_NOT_FOUND');
2111: RAISE FND_API.G_EXC_ERROR;
2112: END IF;
2113: CLOSE c_order_line;
2114:
2115: END IF;
2119: x_return_status := l_return_status;
2120:
2121: EXCEPTION
2122:
2123: WHEN FND_API.G_EXC_ERROR THEN
2124: x_return_status := FND_API.G_RET_STS_ERROR ;
2125: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2126: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2127: WHEN OTHERS THEN
2120:
2121: EXCEPTION
2122:
2123: WHEN FND_API.G_EXC_ERROR THEN
2124: x_return_status := FND_API.G_RET_STS_ERROR ;
2125: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2126: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2127: WHEN OTHERS THEN
2128: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2121: EXCEPTION
2122:
2123: WHEN FND_API.G_EXC_ERROR THEN
2124: x_return_status := FND_API.G_RET_STS_ERROR ;
2125: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2126: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2127: WHEN OTHERS THEN
2128: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2129: END VALIDATE_CUST_SAMPLE;
2122:
2123: WHEN FND_API.G_EXC_ERROR THEN
2124: x_return_status := FND_API.G_RET_STS_ERROR ;
2125: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2126: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2127: WHEN OTHERS THEN
2128: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2129: END VALIDATE_CUST_SAMPLE;
2130:
2124: x_return_status := FND_API.G_RET_STS_ERROR ;
2125: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2126: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2127: WHEN OTHERS THEN
2128: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2129: END VALIDATE_CUST_SAMPLE;
2130:
2131:
2132:
2229: BEGIN
2230: --dbms_output.put_line('b4 validate supplier sample ');
2231:
2232: -- Assign API local Variables;
2233: l_return_status := FND_API.G_RET_STS_SUCCESS;
2234:
2235: -- supplier_id : This field is mandatory
2236:
2237: IF (p_sample_rec.supplier_id IS NULL)
2236:
2237: IF (p_sample_rec.supplier_id IS NULL)
2238: THEN
2239: GMD_API_PUB.Log_Message('GMD_SUPPLIER_REQD');
2240: RAISE FND_API.G_EXC_ERROR;
2241: ELSE
2242: OPEN c_supplier;
2243: FETCH c_supplier INTO l_dummy;
2244: IF (c_supplier%NOTFOUND)
2244: IF (c_supplier%NOTFOUND)
2245: THEN
2246: CLOSE c_supplier;
2247: GMD_API_PUB.Log_Message('GMD_SUPPLIER_NOT_FOUND');
2248: RAISE FND_API.G_EXC_ERROR;
2249: END IF;
2250: CLOSE c_supplier;
2251: END IF;
2252:
2264: FND_MESSAGE.SET_NAME('GMD','GMD_NOTFOUND');
2265: FND_MESSAGE.SET_TOKEN('WHAT', 'SUPPLIER_SITE_ID');
2266: FND_MESSAGE.SET_TOKEN('VALUE', p_sample_rec.supplier_site_id);
2267: FND_MSG_PUB.ADD;
2268: RAISE FND_API.G_EXC_ERROR;
2269: END IF;
2270: CLOSE c_supplier_site;
2271: END IF;
2272:
2281: THEN
2282: /*GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
2283: 'WHAT', 'supplier_site_id must not be NULL');*/
2284: GMD_API_PUB.Log_Message('GMD_SUPP_SITE_MUST_NOT_NULL');
2285: RAISE FND_API.G_EXC_ERROR;
2286: END IF;
2287:
2288: OPEN c_po;
2289: FETCH c_po INTO l_dummy;
2290: IF (c_po%NOTFOUND)
2291: THEN
2292: CLOSE c_po;
2293: GMD_API_PUB.Log_Message('GMD_PO_NOT_FOUND');
2294: RAISE FND_API.G_EXC_ERROR;
2295: END IF;
2296: CLOSE c_po;
2297: END IF;
2298:
2308: THEN
2309: /*GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
2310: 'WHAT', 'po_header_id must not be NULL');*/
2311: GMD_API_PUB.Log_Message('GMD_PO_HEADER_MUST_NOT_NULL');
2312: RAISE FND_API.G_EXC_ERROR;
2313: END IF;
2314:
2315: IF (p_sample_rec.supplier_site_id IS NULL)
2316: THEN
2316: THEN
2317: /*GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
2318: 'WHAT', 'supplier site must not be NULL');*/
2319: GMD_API_PUB.Log_Message('GMD_SUPP_SITE_MUST_NOT_NULL');
2320: RAISE FND_API.G_EXC_ERROR;
2321: END IF;
2322:
2323: OPEN c_po_line;
2324: FETCH c_po_line INTO l_dummy;
2325: IF (c_po_line%NOTFOUND)
2326: THEN
2327: CLOSE c_po_line;
2328: GMD_API_PUB.Log_Message('GMD_PO_LINE_NOT_FOUND');
2329: RAISE FND_API.G_EXC_ERROR;
2330: END IF;
2331: CLOSE c_po_line;
2332: END IF;
2333:
2343: THEN
2344: /*GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
2345: 'WHAT', 'po_header_id must not be NULL');*/
2346: GMD_API_PUB.Log_Message('GMD_PO_HEADER_MUST_NOT_NULL');
2347: RAISE FND_API.G_EXC_ERROR;
2348: END IF;
2349: IF (p_sample_rec.po_line_id IS NULL)
2350: THEN
2351: /*GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
2350: THEN
2351: /*GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
2352: 'WHAT', 'po_line_id must not be NULL');*/
2353: GMD_API_PUB.Log_Message('GMD_PO_LINE_MUST_NOT_NULL');
2354: RAISE FND_API.G_EXC_ERROR;
2355: END IF;
2356: END IF;
2357:
2358: IF (p_sample_rec.receipt_id IS NOT NULL AND p_sample_rec.receipt_line_id IS NULL)
2359: THEN
2360: /*GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
2361: 'WHAT', 'receipt_line_id must not be NULL');*/
2362: GMD_API_PUB.Log_Message('GMD_RECEIPT_LINE_MUST_NOT_NULL');
2363: RAISE FND_API.G_EXC_ERROR;
2364: END IF;
2365:
2366: IF (p_sample_rec.receipt_line_id IS NOT NULL AND p_sample_rec.receipt_id IS NULL)
2367: THEN
2367: THEN
2368: /*GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
2369: 'WHAT', 'receipt_id must not be NULL');*/
2370: GMD_API_PUB.Log_Message('GMD_RECEIPT_MUST_NOT_NULL');
2371: RAISE FND_API.G_EXC_ERROR;
2372: END IF;
2373:
2374: IF(p_sample_rec.receipt_id IS NOT NULL AND p_sample_rec.receipt_line_id IS NOT NULL)
2375: THEN
2378: IF (c_receipt_info%NOTFOUND)
2379: THEN
2380: CLOSE c_receipt_info;
2381: GMD_API_PUB.Log_Message('GMD_RECEIPT_NOT_FOUND');
2382: RAISE FND_API.G_EXC_ERROR;
2383: END IF;
2384: CLOSE c_receipt_info;
2385: END IF;
2386:
2391:
2392:
2393: EXCEPTION
2394:
2395: WHEN FND_API.G_EXC_ERROR THEN
2396: x_return_status := FND_API.G_RET_STS_ERROR ;
2397: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2398: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2399: WHEN OTHERS THEN
2392:
2393: EXCEPTION
2394:
2395: WHEN FND_API.G_EXC_ERROR THEN
2396: x_return_status := FND_API.G_RET_STS_ERROR ;
2397: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2398: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2399: WHEN OTHERS THEN
2400: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2393: EXCEPTION
2394:
2395: WHEN FND_API.G_EXC_ERROR THEN
2396: x_return_status := FND_API.G_RET_STS_ERROR ;
2397: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2398: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2399: WHEN OTHERS THEN
2400: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2401: END VALIDATE_SUPP_SAMPLE;
2394:
2395: WHEN FND_API.G_EXC_ERROR THEN
2396: x_return_status := FND_API.G_RET_STS_ERROR ;
2397: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2398: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2399: WHEN OTHERS THEN
2400: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2401: END VALIDATE_SUPP_SAMPLE;
2402:
2396: x_return_status := FND_API.G_RET_STS_ERROR ;
2397: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2398: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2399: WHEN OTHERS THEN
2400: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2401: END VALIDATE_SUPP_SAMPLE;
2402:
2403:
2404: -- Added for MPK Bug
2410:
2411: BEGIN
2412:
2413: -- Stability Study samples cannot be loaded through Create Private API
2414: x_return_status := FND_API.G_RET_STS_ERROR;
2415:
2416: END VALIDATE_STABILITY_SAMPLE;
2417:
2418:
2435: l_return_status VARCHAR2(1);
2436:
2437: BEGIN
2438: -- Assign API local Variables;
2439: l_return_status := FND_API.G_RET_STS_SUCCESS;
2440:
2441: IF ((p_sample_rec.inventory_item_id IS NOT NULL)
2442: OR (p_sample_rec.lot_number IS NOT NULL)) THEN
2443: /*GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
2442: OR (p_sample_rec.lot_number IS NOT NULL)) THEN
2443: /*GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
2444: 'Item and Lot', ' should not be specified for Monitor sample');*/
2445: GMD_API_PUB.Log_Message('GMD_ITEM_LOT_MONITOR_SAMPLE');
2446: RAISE FND_API.G_EXC_ERROR;
2447: END IF; -- test item sources
2448:
2449: IF ((p_sample_rec.sample_qty IS NOT NULL)
2450: OR (p_sample_rec.sample_qty_uom IS NOT NULL)) THEN
2450: OR (p_sample_rec.sample_qty_uom IS NOT NULL)) THEN
2451: /*GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
2452: 'Quantity and UOM', ' should not be specified for Monitor sample');*/
2453: GMD_API_PUB.Log_Message('GMD_QTY_UOM_MONITOR_SAMPLE');
2454: RAISE FND_API.G_EXC_ERROR;
2455: END IF; -- test item sources
2456:
2457: IF p_sample_rec.resources IS NULL THEN
2458: GMD_API_PUB.Log_Message('GMD_QC_RESOURCE_REQD');
2455: END IF; -- test item sources
2456:
2457: IF p_sample_rec.resources IS NULL THEN
2458: GMD_API_PUB.Log_Message('GMD_QC_RESOURCE_REQD');
2459: RAISE FND_API.G_EXC_ERROR;
2460: END IF; -- test item sources
2461:
2462: -- validate resource
2463: -- start of bug# 3452384
2468: THEN
2469: CLOSE c_resource;
2470: GMD_API_PUB.Log_Message('GMD_RESOURCE_NOT_FOUND',
2471: 'RESOURCE', p_sample_rec.resources);
2472: RAISE FND_API.G_EXC_ERROR;
2473: END IF;
2474: CLOSE c_resource;
2475:
2476: -- end of bug 3452384
2479: x_return_status := l_return_status;
2480:
2481: EXCEPTION
2482:
2483: WHEN FND_API.G_EXC_ERROR THEN
2484: x_return_status := FND_API.G_RET_STS_ERROR ;
2485: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2486: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2487: WHEN OTHERS THEN
2480:
2481: EXCEPTION
2482:
2483: WHEN FND_API.G_EXC_ERROR THEN
2484: x_return_status := FND_API.G_RET_STS_ERROR ;
2485: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2486: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2487: WHEN OTHERS THEN
2488: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2481: EXCEPTION
2482:
2483: WHEN FND_API.G_EXC_ERROR THEN
2484: x_return_status := FND_API.G_RET_STS_ERROR ;
2485: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2486: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2487: WHEN OTHERS THEN
2488: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2489:
2482:
2483: WHEN FND_API.G_EXC_ERROR THEN
2484: x_return_status := FND_API.G_RET_STS_ERROR ;
2485: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2486: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2487: WHEN OTHERS THEN
2488: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2489:
2490: END VALIDATE_RESOURCE_SAMPLE;
2484: x_return_status := FND_API.G_RET_STS_ERROR ;
2485: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2486: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2487: WHEN OTHERS THEN
2488: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2489:
2490: END VALIDATE_RESOURCE_SAMPLE;
2491:
2492:
2515:
2516:
2517: BEGIN
2518: -- Assign API local Variables;
2519: l_return_status := FND_API.G_RET_STS_SUCCESS;
2520:
2521: IF ((p_sample_rec.inventory_item_id IS NOT NULL)
2522: OR (p_sample_rec.lot_number IS NOT NULL)) THEN
2523: GMD_API_PUB.Log_Message('GMD_ITEM_LOT_MONITOR_SAMPLE');
2520:
2521: IF ((p_sample_rec.inventory_item_id IS NOT NULL)
2522: OR (p_sample_rec.lot_number IS NOT NULL)) THEN
2523: GMD_API_PUB.Log_Message('GMD_ITEM_LOT_MONITOR_SAMPLE');
2524: RAISE FND_API.G_EXC_ERROR;
2525: END IF; -- test item sources
2526:
2527: IF ((p_sample_rec.sample_qty IS NOT NULL)
2528: OR (p_sample_rec.sample_qty_uom IS NOT NULL)) THEN
2526:
2527: IF ((p_sample_rec.sample_qty IS NOT NULL)
2528: OR (p_sample_rec.sample_qty_uom IS NOT NULL)) THEN
2529: GMD_API_PUB.Log_Message('GMD_QTY_UOM_MONITOR_SAMPLE');
2530: RAISE FND_API.G_EXC_ERROR;
2531: END IF; -- test item sources
2532:
2533: IF p_sample_rec.subinventory IS NULL THEN
2534: GMD_API_PUB.Log_Message('GMD_QC_WHSE_REQD');
2531: END IF; -- test item sources
2532:
2533: IF p_sample_rec.subinventory IS NULL THEN
2534: GMD_API_PUB.Log_Message('GMD_QC_WHSE_REQD');
2535: RAISE FND_API.G_EXC_ERROR;
2536: END IF; -- test item sources
2537:
2538: -- This Procedure Assumes that the whse and item have been validated.
2539:
2564:
2565: IF (p_locator_control = 1) THEN
2566: IF (p_sample_rec.locator_id IS NOT NULL) THEN
2567: GMD_API_PUB.Log_Message('GMD_LOCATION_MUST_NULL');
2568: RAISE FND_API.G_EXC_ERROR;
2569: END IF;
2570: ELSE
2571: -- Item is location controlled.
2572: IF (p_sample_rec.locator_id IS NULL) THEN
2571: -- Item is location controlled.
2572: IF (p_sample_rec.locator_id IS NULL) THEN
2573: -- Location cannot be NULL in this case.
2574: GMD_API_PUB.Log_Message('GMD_LOCATION_MUST_NULL');
2575: RAISE FND_API.G_EXC_ERROR;
2576: ELSE
2577: -- Check that Location exist in MTL_ITEM_LOCATIONS
2578: OPEN c_subinventory_loct;
2579: FETCH c_subinventory_loct INTO l_dummy;
2580: IF (c_subinventory_loct%NOTFOUND) THEN
2581: --CLOSE c_subinventory_loct;
2582: GMD_API_PUB.Log_Message('GMD_LOCT_NOT_FOUND',
2583: 'LOCATION', p_sample_rec.locator_id);
2584: --RAISE FND_API.G_EXC_ERROR;
2585: END IF;
2586: CLOSE c_subinventory_loct;
2587: END IF; -- location IS NOT NULL
2588: END IF; -- p_locator_control = 1
2592: x_return_status := l_return_status;
2593:
2594: EXCEPTION
2595:
2596: WHEN FND_API.G_EXC_ERROR THEN
2597: x_return_status := FND_API.G_RET_STS_ERROR ;
2598: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2599: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2600: WHEN OTHERS THEN
2593:
2594: EXCEPTION
2595:
2596: WHEN FND_API.G_EXC_ERROR THEN
2597: x_return_status := FND_API.G_RET_STS_ERROR ;
2598: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2599: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2600: WHEN OTHERS THEN
2601: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2594: EXCEPTION
2595:
2596: WHEN FND_API.G_EXC_ERROR THEN
2597: x_return_status := FND_API.G_RET_STS_ERROR ;
2598: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2599: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2600: WHEN OTHERS THEN
2601: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2602:
2595:
2596: WHEN FND_API.G_EXC_ERROR THEN
2597: x_return_status := FND_API.G_RET_STS_ERROR ;
2598: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2599: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2600: WHEN OTHERS THEN
2601: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2602:
2603: END VALIDATE_LOCATION_SAMPLE;
2597: x_return_status := FND_API.G_RET_STS_ERROR ;
2598: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2599: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2600: WHEN OTHERS THEN
2601: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2602:
2603: END VALIDATE_LOCATION_SAMPLE;
2604:
2605:
2725:
2726: BEGIN
2727:
2728: -- Initialize API return status to success
2729: l_return_status := FND_API.G_RET_STS_SUCCESS;
2730: l_sample_rec := p_sample_rec;
2731:
2732: ----------------------------------
2733: -- Validate Sample Required Fields
2735:
2736: -- Orgn Code
2737: IF (p_sample_rec.organization_id IS NULL) THEN
2738: GMD_API_PUB.Log_Message('GMD_ORGN_CODE_REQD');
2739: RAISE FND_API.G_EXC_ERROR;
2740: ELSE
2741: -- Check that orgn Code exist in SY_ORGN_MST
2742: OPEN c_orgn(p_sample_rec.organization_id);
2743: FETCH c_orgn INTO l_dummy;
2744: IF c_orgn%NOTFOUND THEN
2745: CLOSE c_orgn;
2746: GMD_API_PUB.Log_Message('GMD_ORGN_CODE_NOT_FOUND',
2747: 'ORGN', p_sample_rec.organization_id);
2748: RAISE FND_API.G_EXC_ERROR;
2749: END IF;
2750: CLOSE c_orgn;
2751: END IF;
2752:
2753: -- Sampler Id Validation
2754:
2755: IF (p_sample_rec.sampler_id IS NULL ) THEN
2756: GMD_API_PUB.Log_Message('GMD_SAMPLER_ID_REQD');
2757: RAISE FND_API.G_EXC_ERROR;
2758: ELSE
2759: -- Check that orgn Code exist in mtl_parameters
2760: OPEN c_sampler(p_sample_rec.organization_id, p_sample_rec.sampler_id);
2761: FETCH c_sampler INTO l_dummy;
2761: FETCH c_sampler INTO l_dummy;
2762: IF c_sampler%NOTFOUND THEN
2763: GMD_API_PUB.Log_Message('GMD_SAMPLER_ID_NOTFOUND',
2764: 'SAMPLER', p_sample_rec.sampler_id);
2765: RAISE FND_API.G_EXC_ERROR;
2766: CLOSE c_sampler;
2767: END IF;
2768: CLOSE c_sampler;
2769: END IF;
2770:
2771: -- Sample No
2772: IF (ltrim(rtrim(p_sample_rec.sample_no)) IS NULL) THEN
2773: GMD_API_PUB.Log_Message('GMD_SAMPLE_NUMBER_REQD');
2774: RAISE FND_API.G_EXC_ERROR;
2775: END IF;
2776:
2777:
2778: -- QC Lab Orgn Code
2777:
2778: -- QC Lab Orgn Code
2779: IF (p_sample_rec.lab_organization_id IS NULL) THEN
2780: GMD_API_PUB.Log_Message('GMD_QC_LAB_ORGN_CODE_REQD');
2781: RAISE FND_API.G_EXC_ERROR;
2782: ELSE
2783: -- Check that QC Lab Orgn Code exist in SY_ORGN_MST
2784: OPEN c_lab_orgn(p_sample_rec.lab_organization_id);
2785: FETCH c_lab_orgn INTO l_dummy;
2786: IF c_lab_orgn%NOTFOUND THEN
2787: CLOSE c_lab_orgn;
2788: GMD_API_PUB.Log_Message('GMD_QC_LAB_ORGN_CODE_NOT_FOUND',
2789: 'ORGN', p_sample_rec.lab_organization_id);
2790: RAISE FND_API.G_EXC_ERROR;
2791: END IF;
2792: CLOSE c_lab_orgn;
2793: END IF;
2794:
2797: IF ((p_sample_rec.sample_disposition IS NULL) OR
2798: (NOT (p_sample_rec.sample_disposition in ('0RT', '0PL', '1P')))) THEN
2799: -- Now, what is the disposition of this sample?
2800: GMD_API_PUB.Log_Message('GMD_SAMPLE_DISPOSITION_INVALID');
2801: RAISE FND_API.G_EXC_ERROR;
2802: END IF;
2803:
2804: -- Sample Source
2805: -- Bug 3401377: added Stability Study and Monitor samples to MPK
2807: (NOT (p_sample_rec.source in ('I', 'W', 'C', 'S', 'T','L','R')))
2808: ) THEN
2809: -- Now, what is the source of this sample? Where did it come from?
2810: GMD_API_PUB.Log_Message('GMD_SAMPLE_SOURCE_INVALID');
2811: RAISE FND_API.G_EXC_ERROR;
2812:
2813: ELSE -- VAlidate that sample type is Correct
2814:
2815: IF ( p_sample_rec.sample_type is NULL ) THEN
2815: IF ( p_sample_rec.sample_type is NULL ) THEN
2816: /*GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
2817: 'WHAT', 'SAMPLE TYPE must be Specified');*/
2818: GMD_API_PUB.Log_Message('GMD_SAMPLE_TYPE');
2819: RAISE FND_API.G_EXC_ERROR;
2820:
2821: --ELSIF ( p_sample_rec.sample_type <> 'I') THEN
2822: ELSIF ( p_sample_rec.sample_type NOT IN ('M' , 'I')) THEN
2823:
2823:
2824: /*GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
2825: 'WHAT', 'Only sample_type = M or I are Suppported');*/
2826: GMD_API_PUB.Log_Message('GMD_SAMPLE_TYPE_SUPPORTED');
2827: RAISE FND_API.G_EXC_ERROR;
2828:
2829: ELSIF ( p_sample_rec.sample_type = 'I')
2830: AND ( NOT (p_sample_rec.source in ('I', 'W', 'C', 'S', 'T'))) THEN
2831: /*GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
2830: AND ( NOT (p_sample_rec.source in ('I', 'W', 'C', 'S', 'T'))) THEN
2831: /*GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
2832: 'WHAT', 'SAMPLE TYPE Inventory does not match source');*/
2833: GMD_API_PUB.Log_Message('GMD_SAMPLE_TYPE_INVENTORY');
2834: RAISE FND_API.G_EXC_ERROR;
2835:
2836: ELSIF ( p_sample_rec.sample_type = 'M')
2837: AND ( NOT (p_sample_rec.source in ('R', 'L'))) THEN
2838: /*GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
2837: AND ( NOT (p_sample_rec.source in ('R', 'L'))) THEN
2838: /*GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
2839: 'WHAT', 'SAMPLE TYPE Monitor does not match source');*/
2840: GMD_API_PUB.Log_Message('GMD_SAMPLE_TYPE_MONITOR');
2841: RAISE FND_API.G_EXC_ERROR;
2842: END IF;
2843:
2844: END IF;
2845:
2849: OPEN uom;
2850: FETCH uom INTO l_uom;
2851: IF (uom%NOTFOUND) THEN
2852: GMD_API_PUB.Log_Message('GMD_SAMPLE_UOM_REQD');
2853: RAISE FND_API.G_EXC_ERROR;
2854: END IF;
2855: CLOSE uom;
2856:
2857: IF p_sample_rec.sample_qty_uom <> l_uom THEN
2867: from_name => NULL,
2868: to_name => NULL);
2869: IF (l_test_qty < 0 ) THEN
2870: GMD_API_PUB.Log_Message('FM_UOMMUSTCONVERT');
2871: RAISE FND_API.G_EXC_ERROR;
2872: END IF;
2873:
2874: END IF; -- IF p_sample_rec.sample_qty_uom <> l_uom THEN
2875:
2883: -- Bug 3401377: Monitor samples do not have a qty or uom
2884: IF ((p_sample_rec.sample_qty IS NULL)
2885: AND (p_sample_rec.sample_type = 'I' )) THEN
2886: GMD_API_PUB.Log_Message('GMD_SAMPLE_QTY_REQD');
2887: RAISE FND_API.G_EXC_ERROR;
2888: END IF;
2889:
2890: -- Sample UOM
2891: -- Bug 3401377: Monitor samples do not have a qty or uom
2891: -- Bug 3401377: Monitor samples do not have a qty or uom
2892: IF ( (p_sample_rec.sample_qty_uom IS NULL)
2893: AND (p_sample_rec.sample_type = 'I' )) THEN
2894: GMD_API_PUB.Log_Message('GMD_SAMPLE_UOM_REQD');
2895: RAISE FND_API.G_EXC_ERROR;
2896: END IF;
2897:
2898: -- Validate Whse_code if passed.
2899:
2906: IF (c_subinventory%NOTFOUND) THEN
2907: CLOSE c_subinventory;
2908: GMD_API_PUB.Log_Message('GMD_WHSE_NOT_FOUND',
2909: 'WHSE_CODE', p_sample_rec.subinventory);
2910: RAISE FND_API.G_EXC_ERROR;
2911: END IF;
2912: CLOSE c_subinventory;
2913:
2914: -- Bug 4165704: taken out because it was not needed
2917: --FETCH c_orgn_whse INTO l_dummy;
2918: --IF (c_orgn_whse%NOTFOUND) THEN
2919: -- CLOSE c_orgn_whse;
2920: -- GMD_API_PUB.Log_Message('GMD_WHSE_AND_ORGN_CODE');
2921: -- RAISE FND_API.G_EXC_ERROR;
2922: -- END IF;
2923: -- CLOSE c_orgn_whse;
2924: END IF;
2925:
2927:
2928: -- 5283854 if subinv is not specified and locator is then error out
2929: IF p_sample_rec.subinventory IS NULL and p_sample_rec.locator_id is NOT NULL THEN
2930: GMD_API_PUB.Log_Message('GMD_API_LOCATOR_NOT_REQD');
2931: RAISE FND_API.G_EXC_ERROR;
2932: END IF;
2933: -- end 5283854
2934:
2935: -- Bug 4165704: used common routine to determine location control
2947: IF ( p_sample_rec.locator_id is NOT NULL) THEN
2948: /*GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
2949: 'WHAT', 'Location should be NULL');*/
2950: GMD_API_PUB.Log_Message('GMD_LOCATION_MUST_NULL');
2951: RAISE FND_API.G_EXC_ERROR;
2952: END IF;
2953:
2954: ELSIF ( l_locator_control > 1) THEN -- Validate that location exists in whse
2955: -- Bug 4165704: updated for locator and subinventory
2956:
2957: -- 5283854 if subinv is specified and locator is NOT then error out
2958: IF p_sample_rec.subinventory IS NOT NULL and p_sample_rec.locator_id is NULL THEN
2959: GMD_API_PUB.Log_Message('GMD_API_SUBINV_REQD');
2960: RAISE FND_API.G_EXC_ERROR;
2961: END IF;
2962: -- end 5283854
2963:
2964: IF ( p_sample_rec.locator_id is NOT NULL) THEN -- location specified
2968: IF (c_subinventory_locator%NOTFOUND) THEN
2969: --CLOSE c_subinventory_locator;
2970: GMD_API_PUB.Log_Message('GMD_LOCT_NOT_FOUND',
2971: 'LOCATOR', p_sample_rec.locator_id);
2972: RAISE FND_API.G_EXC_ERROR; -- 5283854 rework - take out -- from this line
2973: END IF;
2974: CLOSE c_subinventory_locator;
2975: END IF;
2976:
2982:
2983: IF p_sample_rec.storage_organization_id is NULL and ( p_sample_rec.storage_subinventory IS NOT NULL or p_sample_rec.storage_locator_id is NOT NULL )
2984: then
2985: GMD_API_PUB.Log_Message('GMD_LOCATION_MUST_NULL');
2986: RAISE FND_API.G_EXC_ERROR;
2987: END IF;
2988:
2989:
2990: IF p_sample_rec.storage_organization_id IS NOT NULL
2995:
2996: -- if subinv is not specified and locator is then error out
2997: IF p_sample_rec.storage_subinventory IS NULL and p_sample_rec.storage_locator_id is NOT NULL THEN
2998: GMD_API_PUB.Log_Message('GMD_API_LOCATOR_NOT_REQD');
2999: RAISE FND_API.G_EXC_ERROR;
3000: END IF;
3001:
3002:
3003: GMD_COMMON_GRP.item_is_locator_controlled (
3012: IF ( p_sample_rec.storage_locator_id is NOT NULL) THEN
3013: /*GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
3014: 'WHAT', 'Location should be NULL');*/
3015: GMD_API_PUB.Log_Message('GMD_LOCATION_MUST_NULL');
3016: RAISE FND_API.G_EXC_ERROR;
3017: END IF;
3018:
3019: ELSIF ( l_locator_control > 1) THEN -- Validate that locater exists in subinv
3020:
3021:
3022: -- if subinv is specified and locator is NOT then error out
3023: IF p_sample_rec.storage_subinventory IS NOT NULL and p_sample_rec.storage_locator_id is NULL THEN
3024: GMD_API_PUB.Log_Message('GMD_API_SUBINV_REQD');
3025: RAISE FND_API.G_EXC_ERROR;
3026: END IF;
3027:
3028: IF ( p_sample_rec.storage_locator_id is NOT NULL) THEN -- location specified
3029:
3031: FETCH c_storage_subinventory_locator INTO l_dummy;
3032: IF (c_storage_subinventory_locator%NOTFOUND) THEN
3033: GMD_API_PUB.Log_Message('GMD_LOCT_NOT_FOUND',
3034: 'LOCATOR', p_sample_rec.storage_locator_id);
3035: RAISE FND_API.G_EXC_ERROR;
3036: END IF;
3037: CLOSE c_storage_subinventory_locator;
3038: END IF;
3039:
3056: p_grade => p_grade,
3057: x_sample_rec => l_sample_rec,
3058: x_return_status => l_return_status);
3059:
3060: IF l_return_status = FND_API.G_RET_STS_ERROR THEN
3061: -- Message is alrady logged by check_for_null procedure
3062: RAISE FND_API.G_EXC_ERROR;
3063: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3064: -- Message is alrady logged by check_for_null procedure
3058: x_return_status => l_return_status);
3059:
3060: IF l_return_status = FND_API.G_RET_STS_ERROR THEN
3061: -- Message is alrady logged by check_for_null procedure
3062: RAISE FND_API.G_EXC_ERROR;
3063: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3064: -- Message is alrady logged by check_for_null procedure
3065: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3066: END IF;
3059:
3060: IF l_return_status = FND_API.G_RET_STS_ERROR THEN
3061: -- Message is alrady logged by check_for_null procedure
3062: RAISE FND_API.G_EXC_ERROR;
3063: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3064: -- Message is alrady logged by check_for_null procedure
3065: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3066: END IF;
3067: END IF; -- bug 3401377
3061: -- Message is alrady logged by check_for_null procedure
3062: RAISE FND_API.G_EXC_ERROR;
3063: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3064: -- Message is alrady logged by check_for_null procedure
3065: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3066: END IF;
3067: END IF; -- bug 3401377
3068:
3069: --3431884
3083: p_sample_rec => l_sample_rec,
3084: p_locator_control => l_locator_control,
3085: x_return_status => l_return_status);
3086:
3087: IF l_return_status = FND_API.G_RET_STS_ERROR THEN
3088: -- Message is alrady logged by check_for_null procedure
3089: RAISE FND_API.G_EXC_ERROR;
3090: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3091: -- Message is alrady logged by check_for_null procedure
3085: x_return_status => l_return_status);
3086:
3087: IF l_return_status = FND_API.G_RET_STS_ERROR THEN
3088: -- Message is alrady logged by check_for_null procedure
3089: RAISE FND_API.G_EXC_ERROR;
3090: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3091: -- Message is alrady logged by check_for_null procedure
3092: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3093: END IF;
3086:
3087: IF l_return_status = FND_API.G_RET_STS_ERROR THEN
3088: -- Message is alrady logged by check_for_null procedure
3089: RAISE FND_API.G_EXC_ERROR;
3090: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3091: -- Message is alrady logged by check_for_null procedure
3092: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3093: END IF;
3094:
3088: -- Message is alrady logged by check_for_null procedure
3089: RAISE FND_API.G_EXC_ERROR;
3090: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3091: -- Message is alrady logged by check_for_null procedure
3092: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3093: END IF;
3094:
3095:
3096: ELSIF ( p_sample_rec.source = 'W') THEN
3099: p_sample_rec => l_sample_rec,
3100: x_sample_rec => l_sample_out_rec,
3101: x_return_status => l_return_status);
3102:
3103: IF l_return_status = FND_API.G_RET_STS_ERROR THEN
3104: -- Message is alrady logged by check_for_null procedure
3105: RAISE FND_API.G_EXC_ERROR;
3106: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3107: -- Message is alrady logged by check_for_null procedure
3101: x_return_status => l_return_status);
3102:
3103: IF l_return_status = FND_API.G_RET_STS_ERROR THEN
3104: -- Message is alrady logged by check_for_null procedure
3105: RAISE FND_API.G_EXC_ERROR;
3106: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3107: -- Message is alrady logged by check_for_null procedure
3108: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3109: END IF;
3102:
3103: IF l_return_status = FND_API.G_RET_STS_ERROR THEN
3104: -- Message is alrady logged by check_for_null procedure
3105: RAISE FND_API.G_EXC_ERROR;
3106: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3107: -- Message is alrady logged by check_for_null procedure
3108: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3109: END IF;
3110:
3104: -- Message is alrady logged by check_for_null procedure
3105: RAISE FND_API.G_EXC_ERROR;
3106: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3107: -- Message is alrady logged by check_for_null procedure
3108: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3109: END IF;
3110:
3111: l_sample_rec := l_sample_out_rec;
3112:
3115: Validate_cust_sample (
3116: p_sample_rec => l_sample_rec,
3117: x_return_status => l_return_status);
3118:
3119: IF l_return_status = FND_API.G_RET_STS_ERROR THEN
3120: -- Message is alrady logged by check_for_null procedure
3121: RAISE FND_API.G_EXC_ERROR;
3122: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3123: -- Message is alrady logged by check_for_null procedure
3117: x_return_status => l_return_status);
3118:
3119: IF l_return_status = FND_API.G_RET_STS_ERROR THEN
3120: -- Message is alrady logged by check_for_null procedure
3121: RAISE FND_API.G_EXC_ERROR;
3122: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3123: -- Message is alrady logged by check_for_null procedure
3124: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3125: END IF;
3118:
3119: IF l_return_status = FND_API.G_RET_STS_ERROR THEN
3120: -- Message is alrady logged by check_for_null procedure
3121: RAISE FND_API.G_EXC_ERROR;
3122: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3123: -- Message is alrady logged by check_for_null procedure
3124: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3125: END IF;
3126:
3120: -- Message is alrady logged by check_for_null procedure
3121: RAISE FND_API.G_EXC_ERROR;
3122: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3123: -- Message is alrady logged by check_for_null procedure
3124: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3125: END IF;
3126:
3127: ELSIF ( p_sample_rec.source = 'S') THEN
3128: -- validate_supp_sample;
3132: Validate_supp_sample (
3133: p_sample_rec => l_sample_rec,
3134: x_return_status => l_return_status);
3135:
3136: IF l_return_status = FND_API.G_RET_STS_ERROR THEN
3137: -- Message is alrady logged by check_for_null procedure
3138: RAISE FND_API.G_EXC_ERROR;
3139: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3140: -- Message is alrady logged by check_for_null procedure
3134: x_return_status => l_return_status);
3135:
3136: IF l_return_status = FND_API.G_RET_STS_ERROR THEN
3137: -- Message is alrady logged by check_for_null procedure
3138: RAISE FND_API.G_EXC_ERROR;
3139: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3140: -- Message is alrady logged by check_for_null procedure
3141: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3142: END IF;
3135:
3136: IF l_return_status = FND_API.G_RET_STS_ERROR THEN
3137: -- Message is alrady logged by check_for_null procedure
3138: RAISE FND_API.G_EXC_ERROR;
3139: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3140: -- Message is alrady logged by check_for_null procedure
3141: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3142: END IF;
3143:
3137: -- Message is alrady logged by check_for_null procedure
3138: RAISE FND_API.G_EXC_ERROR;
3139: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3140: -- Message is alrady logged by check_for_null procedure
3141: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3142: END IF;
3143:
3144: --dbms_output.put_line('after Call to validate supp samples');
3145:
3150: Validate_stability_sample (
3151: p_sample_rec => l_sample_rec,
3152: x_return_status => l_return_status);
3153:
3154: IF l_return_status = FND_API.G_RET_STS_ERROR THEN
3155: RAISE FND_API.G_EXC_ERROR;
3156: END IF;
3157:
3158: ELSIF ( p_sample_rec.source = 'R') THEN
3151: p_sample_rec => l_sample_rec,
3152: x_return_status => l_return_status);
3153:
3154: IF l_return_status = FND_API.G_RET_STS_ERROR THEN
3155: RAISE FND_API.G_EXC_ERROR;
3156: END IF;
3157:
3158: ELSIF ( p_sample_rec.source = 'R') THEN
3159: -- validate_resource_sample;
3161: Validate_resource_sample (
3162: p_sample_rec => l_sample_rec,
3163: x_return_status => l_return_status);
3164:
3165: IF l_return_status = FND_API.G_RET_STS_ERROR THEN
3166: -- Message is alrady logged by check_for_null procedure
3167: RAISE FND_API.G_EXC_ERROR;
3168: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3169: -- Message is alrady logged by check_for_null procedure
3163: x_return_status => l_return_status);
3164:
3165: IF l_return_status = FND_API.G_RET_STS_ERROR THEN
3166: -- Message is alrady logged by check_for_null procedure
3167: RAISE FND_API.G_EXC_ERROR;
3168: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3169: -- Message is alrady logged by check_for_null procedure
3170: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3171: END IF;
3164:
3165: IF l_return_status = FND_API.G_RET_STS_ERROR THEN
3166: -- Message is alrady logged by check_for_null procedure
3167: RAISE FND_API.G_EXC_ERROR;
3168: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3169: -- Message is alrady logged by check_for_null procedure
3170: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3171: END IF;
3172:
3166: -- Message is alrady logged by check_for_null procedure
3167: RAISE FND_API.G_EXC_ERROR;
3168: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3169: -- Message is alrady logged by check_for_null procedure
3170: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3171: END IF;
3172:
3173: ELSIF ( p_sample_rec.source = 'L') THEN
3174: -- validate_location_sample;
3177: p_sample_rec => l_sample_rec,
3178: p_locator_control => l_locator_control,
3179: x_return_status => l_return_status);
3180:
3181: IF l_return_status = FND_API.G_RET_STS_ERROR THEN
3182: -- Message is alrady logged by check_for_null procedure
3183: RAISE FND_API.G_EXC_ERROR;
3184: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3185: -- Message is alrady logged by check_for_null procedure
3179: x_return_status => l_return_status);
3180:
3181: IF l_return_status = FND_API.G_RET_STS_ERROR THEN
3182: -- Message is alrady logged by check_for_null procedure
3183: RAISE FND_API.G_EXC_ERROR;
3184: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3185: -- Message is alrady logged by check_for_null procedure
3186: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3187: END IF;
3180:
3181: IF l_return_status = FND_API.G_RET_STS_ERROR THEN
3182: -- Message is alrady logged by check_for_null procedure
3183: RAISE FND_API.G_EXC_ERROR;
3184: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3185: -- Message is alrady logged by check_for_null procedure
3186: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3187: END IF;
3188:
3182: -- Message is alrady logged by check_for_null procedure
3183: RAISE FND_API.G_EXC_ERROR;
3184: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3185: -- Message is alrady logged by check_for_null procedure
3186: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3187: END IF;
3188:
3189: END IF; -- Validation for Supplier Sample
3190:
3195: ELSIF (NOT GMD_QC_TESTS_GRP.validate_test_priority
3196: (p_test_priority => l_sample_rec.priority)
3197: ) THEN
3198: GMD_API_PUB.Log_Message('GMD_INVALID_TEST_PRIORITY');
3199: RAISE FND_API.G_EXC_ERROR;
3200: END IF;
3201:
3202: --dbms_output.put_line('after validate_test_priority');
3203:
3206: IF NVL(l_sample_rec.sample_inv_trans_ind ,'N') NOT IN ('N','Y') THEN
3207: GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
3208: 'WHAT', 'SAMPLE_INV_TRANS_IND');
3209:
3210: RAISE FND_API.G_EXC_ERROR;
3211: END IF;
3212:
3213:
3214:
3227:
3228: --dbms_output.put_line('after GMD_SAMPLES_GRP.validate_sample');
3229:
3230:
3231: IF l_return_status = FND_API.G_RET_STS_ERROR THEN
3232: -- Message is alrady logged by check_for_null procedure
3233: RAISE FND_API.G_EXC_ERROR;
3234: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3235: -- Message is alrady logged by check_for_null procedure
3229:
3230:
3231: IF l_return_status = FND_API.G_RET_STS_ERROR THEN
3232: -- Message is alrady logged by check_for_null procedure
3233: RAISE FND_API.G_EXC_ERROR;
3234: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3235: -- Message is alrady logged by check_for_null procedure
3236: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3237: END IF;
3230:
3231: IF l_return_status = FND_API.G_RET_STS_ERROR THEN
3232: -- Message is alrady logged by check_for_null procedure
3233: RAISE FND_API.G_EXC_ERROR;
3234: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3235: -- Message is alrady logged by check_for_null procedure
3236: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3237: END IF;
3238:
3232: -- Message is alrady logged by check_for_null procedure
3233: RAISE FND_API.G_EXC_ERROR;
3234: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3235: -- Message is alrady logged by check_for_null procedure
3236: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3237: END IF;
3238:
3239:
3240:
3244:
3245:
3246:
3247: EXCEPTION
3248: WHEN FND_API.G_EXC_ERROR THEN
3249: x_return_status := FND_API.G_RET_STS_ERROR ;
3250: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3251: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3252: WHEN OTHERS THEN
3245:
3246:
3247: EXCEPTION
3248: WHEN FND_API.G_EXC_ERROR THEN
3249: x_return_status := FND_API.G_RET_STS_ERROR ;
3250: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3251: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3252: WHEN OTHERS THEN
3253: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3246:
3247: EXCEPTION
3248: WHEN FND_API.G_EXC_ERROR THEN
3249: x_return_status := FND_API.G_RET_STS_ERROR ;
3250: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3251: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3252: WHEN OTHERS THEN
3253: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3254:
3247: EXCEPTION
3248: WHEN FND_API.G_EXC_ERROR THEN
3249: x_return_status := FND_API.G_RET_STS_ERROR ;
3250: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3251: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3252: WHEN OTHERS THEN
3253: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3254:
3255: END VALIDATE_SAMPLE;
3249: x_return_status := FND_API.G_RET_STS_ERROR ;
3250: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3251: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3252: WHEN OTHERS THEN
3253: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3254:
3255: END VALIDATE_SAMPLE;
3256:
3257: END GMD_SAMPLES_PUB;