[Home] [Help]
157: e_error_fetch_item EXCEPTION;
158:
159: BEGIN
160: -- Initialize API return status to success
161: x_return_status := FND_API.G_RET_STS_SUCCESS;
162:
163: IF NOT (p_operation in ('INSERT', 'UPDATE', 'DELETE')) THEN
164: -- Invalid Operation
165: GMD_API_PUB.Log_Message('GMD_INVALID_OPERATION');
162:
163: IF NOT (p_operation in ('INSERT', 'UPDATE', 'DELETE')) THEN
164: -- Invalid Operation
165: GMD_API_PUB.Log_Message('GMD_INVALID_OPERATION');
166: RAISE FND_API.G_EXC_ERROR;
167: END IF;
168:
169: -- Verify that the specification exists.
170: l_spec.spec_id := p_mon_vr.spec_id;
209: );
210: -- No need if called from FORM since it is already
211: -- done in the form
212: -- All messages should be already raised
213: IF l_return_status = FND_API.G_RET_STS_ERROR THEN
214: -- Message is alrady logged by check_for_null procedure
215: RAISE FND_API.G_EXC_ERROR;
216: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
217: -- Message is alrady logged by check_for_null procedure
211: -- done in the form
212: -- All messages should be already raised
213: IF l_return_status = FND_API.G_RET_STS_ERROR THEN
214: -- Message is alrady logged by check_for_null procedure
215: RAISE FND_API.G_EXC_ERROR;
216: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
217: -- Message is alrady logged by check_for_null procedure
218: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
219: END IF;
212: -- All messages should be already raised
213: IF l_return_status = FND_API.G_RET_STS_ERROR THEN
214: -- Message is alrady logged by check_for_null procedure
215: RAISE FND_API.G_EXC_ERROR;
216: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
217: -- Message is alrady logged by check_for_null procedure
218: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
219: END IF;
220:
214: -- Message is alrady logged by check_for_null procedure
215: RAISE FND_API.G_EXC_ERROR;
216: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
217: -- Message is alrady logged by check_for_null procedure
218: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
219: END IF;
220:
221: l_mon_vr := l_mon_vr_tmp;
222: END IF;
228: THEN
229: -- Disaster, Trying to insert duplicate
230: -- Put the message in function mon_vr_exist.
231: -- GMD_API_PUB.Log_Message('GMD_MON_VR_EXIST');
232: RAISE FND_API.G_EXC_ERROR;
233: END IF;
234:
235: -- No need to check the return status because above procedure
236: -- logs appropriate message on the stack and raises an exception.
238: -- The Start Date must be less than the End Date
239: If ( l_mon_vr.end_date IS NOT NULL AND
240: l_mon_vr.start_date > l_mon_vr.end_date) THEN
241: GMD_API_PUB.Log_Message('GMD_SPEC_VR_EFF_DATE_ERROR');
242: RAISE FND_API.G_EXC_ERROR;
243: END IF;
244:
245: -- Spec VR Status Must be less than Spec Status upto Appoved Stages
246: IF (floor(l_spec.spec_status/100) <= 7 AND
246: IF (floor(l_spec.spec_status/100) <= 7 AND
247: floor(l_mon_vr.spec_vr_status/100) <= 7 AND
248: l_mon_vr.spec_vr_status > l_spec.spec_status) THEN
249: GMD_API_PUB.Log_Message('GMD_SPEC_VR_STATUS_HIGHER');
250: RAISE FND_API.G_EXC_ERROR;
251: END IF;
252:
253: -- All systems GO...
254: x_mon_vr := l_mon_vr;
253: -- All systems GO...
254: x_mon_vr := l_mon_vr;
255:
256: EXCEPTION
257: WHEN FND_API.G_EXC_ERROR OR
258: e_spec_fetch_error OR
259: e_smpl_plan_fetch_error OR
260: e_error_fetch_item
261: THEN
258: e_spec_fetch_error OR
259: e_smpl_plan_fetch_error OR
260: e_error_fetch_item
261: THEN
262: x_return_status := FND_API.G_RET_STS_ERROR ;
263: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
264: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
265: WHEN OTHERS THEN
266: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
259: e_smpl_plan_fetch_error OR
260: e_error_fetch_item
261: THEN
262: x_return_status := FND_API.G_RET_STS_ERROR ;
263: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
264: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
265: WHEN OTHERS THEN
266: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
267:
260: e_error_fetch_item
261: THEN
262: x_return_status := FND_API.G_RET_STS_ERROR ;
263: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
264: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
265: WHEN OTHERS THEN
266: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
267:
268:
262: x_return_status := FND_API.G_RET_STS_ERROR ;
263: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
264: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
265: WHEN OTHERS THEN
266: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
267:
268:
269: END validate_mon_vr;
270:
342: OR l_mon_vr.last_update_date IS NULL)
343: THEN
344: GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
345: 'WHAT', 'the dates must not be NULL');
346: RAISE FND_API.G_EXC_ERROR;
347: END IF;
348:
349:
350: -- Bug 3451798
362: -- Bug 3451839
363: -- Invalid Rule Type
364: GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
365: 'WHAT', 'The monitoring spec rule type');
366: RAISE FND_API.G_EXC_ERROR;
367: end if ;
368:
369:
370: -- Loct Organization is valid
375: THEN
376: CLOSE c_orgn;
377: GMD_API_PUB.Log_Message('GMD_ORGANIZATION_ID_NOT_FOUND',
378: 'ORGN_ID', l_mon_vr.locator_organization_id);
379: RAISE FND_API.G_EXC_ERROR;
380: END IF;
381: CLOSE c_orgn;
382: END IF;
383:
382: END IF;
383:
384: --srakrish BUG 5251172: Checking if the responsibility is available to the Locator organization.
385: IF NOT (gmd_api_grp.OrgnAccessible(p_mon_vr.locator_organization_id)) THEN
386: RAISE FND_API.G_EXC_ERROR;
387: END IF;
388:
389: -- Resource is valid (Bug 3451868)
390: IF (l_mon_vr.resources IS NOT NULL) THEN
395: THEN
396: CLOSE c_resources;
397: GMD_API_PUB.Log_Message('GMD_RESOURCE_NOT_FOUND',
398: 'RESOURCE', l_mon_vr.resources);
399: RAISE FND_API.G_EXC_ERROR;
400: END IF;
401: CLOSE c_resources;
402: END IF;
403:
410: THEN
411: CLOSE c_orgn;
412: GMD_API_PUB.Log_Message('GMD_ORGANIZATION_ID_NOT_FOUND',
413: 'ORGN_ID', l_mon_vr.resource_organization_id);
414: RAISE FND_API.G_EXC_ERROR;
415: END IF;
416: CLOSE c_orgn;
417: END IF;
418:
417: END IF;
418:
419: --srakrish BUG 5251172: Checking if the responsibility is available to the Resource organization.
420: IF NOT (gmd_api_grp.OrgnAccessible(p_mon_vr.resource_organization_id)) THEN
421: RAISE FND_API.G_EXC_ERROR;
422: END IF;
423:
424: -- Resource Instance is valid (Bug 3451868)
425: IF (l_mon_vr.resource_instance_id IS NOT NULL) THEN
430: THEN
431: CLOSE c_resource_instance;
432: GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
433: 'WHAT', 'The resource instance');
434: RAISE FND_API.G_EXC_ERROR;
435: END IF;
436: CLOSE c_resource_instance;
437: END IF;
438:
446: THEN
447: CLOSE c_subinventory;
448: GMD_API_PUB.Log_Message('GMD_SUBINVENTORY_NOT_FOUND',
449: 'SUBINVENTORY', l_mon_vr.subinventory);
450: RAISE FND_API.G_EXC_ERROR;
451: END IF;
452: CLOSE c_subinventory;
453:
454: END IF;
476: IF (c_locator%NOTFOUND)
477: THEN
478: CLOSE c_locator;
479: GMD_API_PUB.Log_Message('GMD_LOCT_NOT_FOUND');
480: RAISE FND_API.G_EXC_ERROR;
481: END IF;
482: CLOSE c_locator;
483: END IF; -- location IS NOT NULL
484: ELSE --l_locator_type NOT IN (2,3)
485: IF (l_mon_vr.locator_id IS NOT NULL)
486: THEN
487: GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
488: 'WHAT', 'locator should be NULL');
489: RAISE FND_API.G_EXC_ERROR;
490: END IF;
491: END IF; -- l_locator_type IN (2,3)
492:
493: --=========================================================================
501: THEN
502: CLOSE c_status;
503: GMD_API_PUB.Log_Message('GMD_SPEC_STATUS_NOT_FOUND',
504: 'STATUS', l_mon_vr.spec_vr_status);
505: RAISE FND_API.G_EXC_ERROR;
506: END IF;
507: CLOSE c_status;
508:
509: --=========================================================================
511: --=========================================================================
512: IF (l_mon_vr.start_date IS NULL)
513: THEN
514: GMD_API_PUB.Log_Message('GMD_SPEC_VR_START_DATE_REQD');
515: RAISE FND_API.G_EXC_ERROR;
516: END IF;
517:
518: x_mon_vr := l_mon_vr;
519:
518: x_mon_vr := l_mon_vr;
519:
520:
521: EXCEPTION
522: WHEN FND_API.G_EXC_ERROR THEN
523: x_return_status := FND_API.G_RET_STS_ERROR ;
524: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
525: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
526: WHEN OTHERS THEN
519:
520:
521: EXCEPTION
522: WHEN FND_API.G_EXC_ERROR THEN
523: x_return_status := FND_API.G_RET_STS_ERROR ;
524: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
525: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
526: WHEN OTHERS THEN
527: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
520:
521: EXCEPTION
522: WHEN FND_API.G_EXC_ERROR THEN
523: x_return_status := FND_API.G_RET_STS_ERROR ;
524: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
525: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
526: WHEN OTHERS THEN
527: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
528:
521: EXCEPTION
522: WHEN FND_API.G_EXC_ERROR THEN
523: x_return_status := FND_API.G_RET_STS_ERROR ;
524: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
525: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
526: WHEN OTHERS THEN
527: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
528:
529: END check_for_null_and_fks_in_mvr;
523: x_return_status := FND_API.G_RET_STS_ERROR ;
524: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
525: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
526: WHEN OTHERS THEN
527: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
528:
529: END check_for_null_and_fks_in_mvr;
530:
531:
696: e_error_fetch_item EXCEPTION;
697:
698: BEGIN
699: -- Initialize API return status to success
700: x_return_status := FND_API.G_RET_STS_SUCCESS;
701:
702: IF NOT (p_operation in ('INSERT', 'UPDATE', 'DELETE')) THEN
703: -- Invalid Operation
704: GMD_API_PUB.Log_Message('GMD_INVALID_OPERATION');
701:
702: IF NOT (p_operation in ('INSERT', 'UPDATE', 'DELETE')) THEN
703: -- Invalid Operation
704: GMD_API_PUB.Log_Message('GMD_INVALID_OPERATION');
705: RAISE FND_API.G_EXC_ERROR;
706: END IF;
707:
708: -- Verify that the specification exists.
709: l_spec.spec_id := p_inv_vr.spec_id;
742: --For mini pack L, bug 3439865
743: IF ( nvl(p_inv_vr.auto_sample_ind,'N') not in ('N','Y')) THEN
744: GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
745: 'WHAT', 'INVALID_AUTO_SAMPLE_IND');
746: RAISE FND_API.G_EXC_ERROR;
747: END IF;
748: -- end 3439865
749: -- Check for NULLs and Valid Foreign Keys in the input parameter
750: check_for_null_and_fks_in_ivr
756: );
757: -- No need if called from FORM since it is already
758: -- done in the form
759:
760: IF l_return_status = FND_API.G_RET_STS_ERROR THEN
761: -- Message is alrady logged by check_for_null procedure
762: RAISE FND_API.G_EXC_ERROR;
763: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
764: -- Message is alrady logged by check_for_null procedure
758: -- done in the form
759:
760: IF l_return_status = FND_API.G_RET_STS_ERROR THEN
761: -- Message is alrady logged by check_for_null procedure
762: RAISE FND_API.G_EXC_ERROR;
763: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
764: -- Message is alrady logged by check_for_null procedure
765: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
766: END IF;
759:
760: IF l_return_status = FND_API.G_RET_STS_ERROR THEN
761: -- Message is alrady logged by check_for_null procedure
762: RAISE FND_API.G_EXC_ERROR;
763: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
764: -- Message is alrady logged by check_for_null procedure
765: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
766: END IF;
767:
761: -- Message is alrady logged by check_for_null procedure
762: RAISE FND_API.G_EXC_ERROR;
763: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
764: -- Message is alrady logged by check_for_null procedure
765: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
766: END IF;
767:
768: l_inv_vr := l_inv_vr_tmp;
769: END IF;
776: THEN
777: -- Disaster, Trying to insert duplicate
778: -- bug 2630007, odaboval put the message in function inv_vr_exist.
779: -- GMD_API_PUB.Log_Message('GMD_INV_VR_EXIST');
780: RAISE FND_API.G_EXC_ERROR;
781: END IF;
782:
783:
784: -- Check to make sure that a samplingplan exists
825: uom_rate => l_uom_rate );
826:
827: IF l_uom_rate = -99999 THEN
828: GMD_API_PUB.Log_Message('GMD_UOM_CONVERSION_ERROR');
829: RAISE FND_API.G_EXC_ERROR;
830: END IF;
831: END IF;
832:
833: -- No need to check the return status because above procedure
837: -- bug 2691994 02-DEC-02, odaboval changed p_inv_vr by l_inv_vr
838: If ( l_inv_vr.end_date IS NOT NULL AND
839: l_inv_vr.start_date > l_inv_vr.end_date) THEN
840: GMD_API_PUB.Log_Message('GMD_SPEC_VR_EFF_DATE_ERROR');
841: RAISE FND_API.G_EXC_ERROR;
842: END IF;
843:
844: -- Spec VR Status Must be less than Spec Status upto Appoved Stages
845: IF (floor(l_spec.spec_status/100) <= 7 AND
845: IF (floor(l_spec.spec_status/100) <= 7 AND
846: floor(l_inv_vr.spec_vr_status/100) <= 7 AND
847: l_inv_vr.spec_vr_status > l_spec.spec_status) THEN
848: GMD_API_PUB.Log_Message('GMD_SPEC_VR_STATUS_HIGHER');
849: RAISE FND_API.G_EXC_ERROR;
850: END IF;
851:
852: -- srakrish Bug 5276602: Checking if the Lot optionalfeild is set to yes when lot or parent lot or entered.
853: -- This scenario exists when the api is called from the wrapper.
853: -- This scenario exists when the api is called from the wrapper.
854: IF l_inv_vr.lot_number IS NOT NULL or l_inv_vr.parent_lot_number IS NOT NULL then
855: IF l_inv_vr.lot_optional_on_sample = 'Y' THEN
856: GMD_API_PUB.Log_Message('GMD_SPEC_VR_LOT_CNTRL_INVALID');
857: RAISE FND_API.G_EXC_ERROR;
858: END IF;
859: END IF;
860:
861: -- All systems GO...
861: -- All systems GO...
862: x_inv_vr := l_inv_vr;
863:
864: EXCEPTION
865: WHEN FND_API.G_EXC_ERROR OR
866: e_spec_fetch_error OR
867: e_smpl_plan_fetch_error OR
868: e_error_fetch_item
869: THEN
866: e_spec_fetch_error OR
867: e_smpl_plan_fetch_error OR
868: e_error_fetch_item
869: THEN
870: x_return_status := FND_API.G_RET_STS_ERROR ;
871: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
872: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
873: WHEN OTHERS THEN
874: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
867: e_smpl_plan_fetch_error OR
868: e_error_fetch_item
869: THEN
870: x_return_status := FND_API.G_RET_STS_ERROR ;
871: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
872: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
873: WHEN OTHERS THEN
874: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
875:
868: e_error_fetch_item
869: THEN
870: x_return_status := FND_API.G_RET_STS_ERROR ;
871: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
872: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
873: WHEN OTHERS THEN
874: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
875:
876:
870: x_return_status := FND_API.G_RET_STS_ERROR ;
871: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
872: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
873: WHEN OTHERS THEN
874: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
875:
876:
877: END validate_inv_vr;
878:
972: OR l_inv_vr.last_update_date IS NULL)
973: THEN
974: GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
975: 'WHAT', 'the dates must not be NULL');
976: RAISE FND_API.G_EXC_ERROR;
977: END IF;
978:
979: -- Organization is valid
980: IF (l_inv_vr.organization_id IS NOT NULL) THEN
985: THEN
986: CLOSE c_orgn;
987: GMD_API_PUB.Log_Message('GMD_ORGANIZATION_ID_NOT_FOUND',
988: 'ORGN_ID', l_inv_vr.organization_id);
989: RAISE FND_API.G_EXC_ERROR;
990: END IF;
991: CLOSE c_orgn;
992: END IF;
993:
992: END IF;
993:
994: --srakrish BUG 5251172: Checking if the responsibility is available to the organization.
995: IF NOT (gmd_api_grp.OrgnAccessible(p_inv_vr.organization_id)) THEN
996: RAISE FND_API.G_EXC_ERROR;
997: END IF;
998:
999: --=========================================================================
1000: -- Get Item Controls
1023: THEN
1024: CLOSE c_item_lot_number;
1025: GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
1026: 'WHAT', 'lot_number');
1027: RAISE FND_API.G_EXC_ERROR;
1028: END IF;
1029: CLOSE c_item_lot_number;
1030: ELSE --Item is not lot controlled
1031: FND_MESSAGE.SET_NAME('GMD','GMD_ITEM_NOT_LOT_CONTROL');
1029: CLOSE c_item_lot_number;
1030: ELSE --Item is not lot controlled
1031: FND_MESSAGE.SET_NAME('GMD','GMD_ITEM_NOT_LOT_CONTROL');
1032: FND_MSG_PUB.ADD;
1033: RAISE FND_API.G_EXC_ERROR;
1034: END IF;
1035: END IF;
1036:
1037: IF (l_inv_vr.parent_lot_number IS NOT NULL)
1043: THEN
1044: CLOSE c_item_parent_lot;
1045: GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
1046: 'WHAT', 'parent_lot_number');
1047: RAISE FND_API.G_EXC_ERROR;
1048: END IF;
1049: CLOSE c_item_parent_lot;
1050: ELSE --Item is not child lot controlled
1051: FND_MESSAGE.SET_NAME('GMD','GMD_ITEM_NOT_CHILD_LOT_CONTROL');
1049: CLOSE c_item_parent_lot;
1050: ELSE --Item is not child lot controlled
1051: FND_MESSAGE.SET_NAME('GMD','GMD_ITEM_NOT_CHILD_LOT_CONTROL');
1052: FND_MSG_PUB.ADD;
1053: RAISE FND_API.G_EXC_ERROR;
1054: END IF;
1055: END IF;
1056:
1057: -- Subinventory is Valid
1062: THEN
1063: CLOSE c_subinventory;
1064: GMD_API_PUB.Log_Message('GMD_SUBINVENTORY_NOT_FOUND',
1065: 'SUBINVENTORY', l_inv_vr.subinventory);
1066: RAISE FND_API.G_EXC_ERROR;
1067: END IF;
1068: CLOSE c_subinventory;
1069: END IF;
1070:
1083: IF (c_locator%NOTFOUND)
1084: THEN
1085: CLOSE c_locator;
1086: GMD_API_PUB.Log_Message('GMD_LOCT_NOT_FOUND');
1087: RAISE FND_API.G_EXC_ERROR;
1088: END IF;
1089: CLOSE c_locator;
1090: END IF; -- location IS NOT NULL
1091: ELSE -- Here l_locator_type not IN (2,3)
1092: IF (l_inv_vr.locator_id IS NOT NULL)
1093: THEN
1094: GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
1095: 'WHAT', 'locator should be NULL');
1096: RAISE FND_API.G_EXC_ERROR;
1097: END IF;
1098: END IF; -- l_locator_type IN (2,3)
1099: --=========================================================================
1100: -- lot_optional_on_sample :
1124: THEN
1125: CLOSE c_status;
1126: GMD_API_PUB.Log_Message('GMD_SPEC_STATUS_NOT_FOUND',
1127: 'STATUS', l_inv_vr.spec_vr_status);
1128: RAISE FND_API.G_EXC_ERROR;
1129: END IF;
1130: CLOSE c_status;
1131:
1132: --=========================================================================
1134: --=========================================================================
1135: IF (l_inv_vr.start_date IS NULL)
1136: THEN
1137: GMD_API_PUB.Log_Message('GMD_SPEC_VR_START_DATE_REQD');
1138: RAISE FND_API.G_EXC_ERROR;
1139: END IF;
1140:
1141: --=========================================================================
1142: -- COA section :
1149: x_inv_vr := l_inv_vr;
1150:
1151:
1152: EXCEPTION
1153: WHEN FND_API.G_EXC_ERROR THEN
1154: x_return_status := FND_API.G_RET_STS_ERROR ;
1155: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1156: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1157: WHEN OTHERS THEN
1150:
1151:
1152: EXCEPTION
1153: WHEN FND_API.G_EXC_ERROR THEN
1154: x_return_status := FND_API.G_RET_STS_ERROR ;
1155: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1156: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1157: WHEN OTHERS THEN
1158: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1151:
1152: EXCEPTION
1153: WHEN FND_API.G_EXC_ERROR THEN
1154: x_return_status := FND_API.G_RET_STS_ERROR ;
1155: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1156: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1157: WHEN OTHERS THEN
1158: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1159:
1152: EXCEPTION
1153: WHEN FND_API.G_EXC_ERROR THEN
1154: x_return_status := FND_API.G_RET_STS_ERROR ;
1155: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1156: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1157: WHEN OTHERS THEN
1158: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1159:
1160: END check_for_null_and_fks_in_ivr;
1154: x_return_status := FND_API.G_RET_STS_ERROR ;
1155: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1156: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1157: WHEN OTHERS THEN
1158: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1159:
1160: END check_for_null_and_fks_in_ivr;
1161:
1162:
1325: e_error_fetch_item EXCEPTION;
1326:
1327: BEGIN
1328: -- Initialize API return status to success
1329: x_return_status := FND_API.G_RET_STS_SUCCESS;
1330:
1331: IF NOT (p_operation in ('INSERT', 'UPDATE', 'DELETE')) THEN
1332: -- Invalid Operation
1333: GMD_API_PUB.Log_Message('GMD_INVALID_OPERATION');
1330:
1331: IF NOT (p_operation in ('INSERT', 'UPDATE', 'DELETE')) THEN
1332: -- Invalid Operation
1333: GMD_API_PUB.Log_Message('GMD_INVALID_OPERATION');
1334: RAISE FND_API.G_EXC_ERROR;
1335: END IF;
1336:
1337: -- Verify that the specification exists.
1338: l_spec.spec_id := p_wip_vr.spec_id;
1369: IF ((p_wip_vr.sampling_plan_id IS NULL) and
1370: (p_wip_vr.auto_sample_ind = 'Y'))
1371: THEN
1372: GMD_API_PUB.Log_Message('GMD_NEED_SAMPLE_PLAN');
1373: RAISE FND_API.G_EXC_ERROR;
1374: END IF;
1375:
1376:
1377: -- bug 2691994 02-DEC-02:
1382: --For mini pack L, bug 3439865
1383: IF (nvl(p_wip_vr.auto_sample_ind,'N') not in ('N','Y')) THEN
1384: GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
1385: 'WHAT', 'INVALID_AUTO_SAMPLE_IND');
1386: RAISE FND_API.G_EXC_ERROR;
1387: END IF;
1388: -- end 3439865
1389: -- Check for NULLs and Valid Foreign Keys in the input parameter
1390: GMD_SPEC_VRS_GRP.check_for_null_and_fks_in_wvr
1396: );
1397: -- No need if called from FORM since it is already
1398: -- done in the form
1399:
1400: IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1401: -- Message is alrady logged by check_for_null procedure
1402: RAISE FND_API.G_EXC_ERROR;
1403: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1404: -- Message is alrady logged by check_for_null procedure
1398: -- done in the form
1399:
1400: IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1401: -- Message is alrady logged by check_for_null procedure
1402: RAISE FND_API.G_EXC_ERROR;
1403: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1404: -- Message is alrady logged by check_for_null procedure
1405: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1406: END IF;
1399:
1400: IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1401: -- Message is alrady logged by check_for_null procedure
1402: RAISE FND_API.G_EXC_ERROR;
1403: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1404: -- Message is alrady logged by check_for_null procedure
1405: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1406: END IF;
1407:
1401: -- Message is alrady logged by check_for_null procedure
1402: RAISE FND_API.G_EXC_ERROR;
1403: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1404: -- Message is alrady logged by check_for_null procedure
1405: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1406: END IF;
1407:
1408: l_wip_vr := l_wip_vr_tmp;
1409: END IF;
1415: THEN
1416: -- Disaster, Trying to insert duplicate
1417: -- bug 2630007, odaboval put the message in function wip_vr_exist.
1418: -- GMD_API_PUB.Log_Message('GMD_WIP_VR_EXIST');
1419: RAISE FND_API.G_EXC_ERROR;
1420: END IF;
1421:
1422: -- Sample Quantity UOM must be convertible to Item's UOM
1423: BEGIN
1450: uom_rate => l_uom_rate );
1451:
1452: IF l_uom_rate = -99999 THEN
1453: GMD_API_PUB.Log_Message('GMD_UOM_CONVERSION_ERROR');
1454: RAISE FND_API.G_EXC_ERROR;
1455: END IF;
1456: END IF;
1457:
1458: -- No need to check the return status because above procedure
1462: -- bug 2691994 02-DEC-02, odaboval changed p_wip_vr by l_wip_vr
1463: If ( l_wip_vr.end_date IS NOT NULL AND
1464: l_wip_vr.start_date > l_wip_vr.end_date) THEN
1465: GMD_API_PUB.Log_Message('GMD_SPEC_VR_EFF_DATE_ERROR');
1466: RAISE FND_API.G_EXC_ERROR;
1467: END IF;
1468:
1469: -- Spec VR Status Must be less than Spec Status upto Appoved Stages
1470: -- bug 2691994 02-DEC-02, odaboval changed p_wip_vr by l_wip_vr
1471: IF (floor(l_spec.spec_status/100) <= 7 AND
1472: floor(l_wip_vr.spec_vr_status/100) <= 7 AND
1473: l_wip_vr.spec_vr_status > l_spec.spec_status) THEN
1474: GMD_API_PUB.Log_Message('GMD_SPEC_VR_STATUS_HIGHER');
1475: RAISE FND_API.G_EXC_ERROR;
1476: END IF;
1477:
1478: -- All systems GO...
1479: x_wip_vr := l_wip_vr;
1478: -- All systems GO...
1479: x_wip_vr := l_wip_vr;
1480:
1481: EXCEPTION
1482: WHEN FND_API.G_EXC_ERROR OR
1483: e_spec_fetch_error OR
1484: e_smpl_plan_fetch_error OR
1485: e_error_fetch_item
1486:
1484: e_smpl_plan_fetch_error OR
1485: e_error_fetch_item
1486:
1487: THEN
1488: x_return_status := FND_API.G_RET_STS_ERROR ;
1489: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1490: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1491: WHEN OTHERS THEN
1492: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1485: e_error_fetch_item
1486:
1487: THEN
1488: x_return_status := FND_API.G_RET_STS_ERROR ;
1489: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1490: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1491: WHEN OTHERS THEN
1492: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1493:
1486:
1487: THEN
1488: x_return_status := FND_API.G_RET_STS_ERROR ;
1489: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1490: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1491: WHEN OTHERS THEN
1492: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1493:
1494:
1488: x_return_status := FND_API.G_RET_STS_ERROR ;
1489: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1490: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1491: WHEN OTHERS THEN
1492: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1493:
1494:
1495: END validate_wip_vr;
1496:
1803: l_wip_vr.routing_no IS NULL AND
1804: l_wip_vr.oprn_id IS NULL AND
1805: l_wip_vr.oprn_no IS NULL) THEN
1806: GMD_API_PUB.Log_Message('GMD_WIP_VR_ALL_NULL');
1807: RAISE FND_API.G_EXC_ERROR;
1808: END IF;
1809:
1810: --=========================================================================
1811: -- WHO section :
1816: OR l_wip_vr.last_update_date IS NULL)
1817: THEN
1818: GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
1819: 'WHAT', 'the dates must not be NULL');
1820: RAISE FND_API.G_EXC_ERROR;
1821: END IF;
1822:
1823: --=========================================================================
1824: -- Organization : must be a PLANT and belong to the USER
1832: THEN
1833: CLOSE c_orgn_plant;
1834: GMD_API_PUB.Log_Message('GMD_ORGANIZATION_ID_NOT_FOUND',
1835: 'ORGN_ID', l_wip_vr.organization_id);
1836: RAISE FND_API.G_EXC_ERROR;
1837: END IF;
1838: CLOSE c_orgn_plant;
1839: END IF;
1840:
1839: END IF;
1840:
1841: --srakrish BUG 5251172: Checking if the responsibility is available to the organization.
1842: IF NOT (gmd_api_grp.OrgnAccessible(p_wip_vr.organization_id)) THEN
1843: RAISE FND_API.G_EXC_ERROR;
1844: END IF;
1845:
1846: --=========================================================================
1847: -- Get Item Controls
1883: THEN
1884: CLOSE c_status;
1885: GMD_API_PUB.Log_Message('GMD_SPEC_STATUS_NOT_FOUND',
1886: 'STATUS', l_wip_vr.spec_vr_status);
1887: RAISE FND_API.G_EXC_ERROR;
1888: END IF;
1889: CLOSE c_status;
1890:
1891: --=========================================================================
1893: --=========================================================================
1894: IF (l_wip_vr.start_date IS NULL)
1895: THEN
1896: GMD_API_PUB.Log_Message('GMD_SPEC_VR_START_DATE_REQD');
1897: RAISE FND_API.G_EXC_ERROR;
1898: END IF;
1899:
1900: --=========================================================================
1901: -- COA section :
1914: IF (l_wip_vr.organization_id IS NULL)
1915: THEN
1916: GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
1917: 'WHAT', 'the organization id must not be NULL');
1918: RAISE FND_API.G_EXC_ERROR;
1919: END IF;
1920:
1921: OPEN c_batch;
1922: FETCH c_batch
1926: IF (c_batch%NOTFOUND)
1927: THEN
1928: CLOSE c_batch;
1929: GMD_API_PUB.Log_Message('GMD_BATCH_NOT_FOUND');
1930: RAISE FND_API.G_EXC_ERROR;
1931: END IF;
1932: CLOSE c_batch;
1933:
1934: --=========================================================================
1941: IF ( NVL(l_wip_vr.recipe_id, l_recipe_id) <> l_recipe_id)
1942: THEN
1943: GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
1944: 'WHAT', 'Passed recipe_id doesn''t match the batch''s recipe_id.');
1945: RAISE FND_API.G_EXC_ERROR;
1946: END IF;
1947: -- 2: recipe_no
1948: IF ( NVL(l_wip_vr.recipe_no, l_recipe_no) <> l_recipe_no)
1949: THEN
1948: IF ( NVL(l_wip_vr.recipe_no, l_recipe_no) <> l_recipe_no)
1949: THEN
1950: GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
1951: 'WHAT', 'Passed recipe_no doesn''t match the batch''s recipe_no.');
1952: RAISE FND_API.G_EXC_ERROR;
1953: END IF;
1954: -- 3: recipe_version
1955: IF ( NVL(l_wip_vr.recipe_version, l_recipe_version) <> l_recipe_version)
1956: THEN
1955: IF ( NVL(l_wip_vr.recipe_version, l_recipe_version) <> l_recipe_version)
1956: THEN
1957: GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
1958: 'WHAT', 'Passed recipe_version doesn''t match the batch''s recipe_version.');
1959: RAISE FND_API.G_EXC_ERROR;
1960: END IF;
1961:
1962: -- 4: formula_id
1963: IF ( NVL(l_wip_vr.formula_id, l_formula_id) <> l_formula_id)
1963: IF ( NVL(l_wip_vr.formula_id, l_formula_id) <> l_formula_id)
1964: THEN
1965: GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
1966: 'WHAT', 'Passed formula_id doesn''t match the batch''s formula_id.');
1967: RAISE FND_API.G_EXC_ERROR;
1968: END IF;
1969: -- 5: formula_no
1970: IF ( NVL(l_wip_vr.formula_no, l_formula_no) <> l_formula_no)
1971: THEN
1970: IF ( NVL(l_wip_vr.formula_no, l_formula_no) <> l_formula_no)
1971: THEN
1972: GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
1973: 'WHAT', 'Passed formula_no doesn''t match the batch''s formula_no.');
1974: RAISE FND_API.G_EXC_ERROR;
1975: END IF;
1976: -- 6: formula_vers
1977: IF ( NVL(l_wip_vr.formula_vers, l_formula_vers) <> l_formula_vers)
1978: THEN
1977: IF ( NVL(l_wip_vr.formula_vers, l_formula_vers) <> l_formula_vers)
1978: THEN
1979: GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
1980: 'WHAT', 'Passed formula_vers doesn''t match the batch''s formula_vers.');
1981: RAISE FND_API.G_EXC_ERROR;
1982: END IF;
1983:
1984: -- 7: routing_id
1985: IF ( NVL(l_wip_vr.routing_id, l_routing_id) <> l_routing_id)
1985: IF ( NVL(l_wip_vr.routing_id, l_routing_id) <> l_routing_id)
1986: THEN
1987: GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
1988: 'WHAT', 'Passed routing_id doesn''t match the batch''s routing_id.');
1989: RAISE FND_API.G_EXC_ERROR;
1990: END IF;
1991: -- 8: routing_no
1992: IF ( NVL(l_wip_vr.routing_no, l_routing_no) <> l_routing_no)
1993: THEN
1992: IF ( NVL(l_wip_vr.routing_no, l_routing_no) <> l_routing_no)
1993: THEN
1994: GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
1995: 'WHAT', 'Passed routing_no doesn''t match the batch''s routing_no.');
1996: RAISE FND_API.G_EXC_ERROR;
1997: END IF;
1998: -- 9: routing_vers
1999: IF ( NVL(l_wip_vr.routing_vers, l_routing_vers) <> l_routing_vers)
2000: THEN
1999: IF ( NVL(l_wip_vr.routing_vers, l_routing_vers) <> l_routing_vers)
2000: THEN
2001: GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
2002: 'WHAT', 'Passed routing_vers doesn''t match the batch''s routing_vers.');
2003: RAISE FND_API.G_EXC_ERROR;
2004: END IF;
2005:
2006: -- At this stage, either l_wip_vr.recipe.... are NULL
2007: -- or they are equal to the local variables. I re-populate the fields (when they are NULL)
2034: IF (c_recipe_id%NOTFOUND)
2035: THEN
2036: CLOSE c_recipe_id;
2037: GMD_API_PUB.Log_Message('GMD_RECIPE_NOT_FOUND');
2038: RAISE FND_API.G_EXC_ERROR;
2039: END IF;
2040: CLOSE c_recipe_id;
2041:
2042: -- 4: formula_id
2043: IF ( NVL(l_wip_vr.formula_id, l_formula_id) <> l_formula_id)
2044: THEN
2045: GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
2046: 'WHAT', 'Passed formula_id doesn''t match the batch''s formula_id.');
2047: RAISE FND_API.G_EXC_ERROR;
2048: END IF;
2049: -- 5: formula_no
2050: IF ( NVL(l_wip_vr.formula_no, l_formula_no) <> l_formula_no)
2051: THEN
2050: IF ( NVL(l_wip_vr.formula_no, l_formula_no) <> l_formula_no)
2051: THEN
2052: GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
2053: 'WHAT', 'Passed formula_no doesn''t match the batch''s formula_no.');
2054: RAISE FND_API.G_EXC_ERROR;
2055: END IF;
2056: -- 6: formula_vers
2057: IF ( NVL(l_wip_vr.formula_vers, l_formula_vers) <> l_formula_vers)
2058: THEN
2057: IF ( NVL(l_wip_vr.formula_vers, l_formula_vers) <> l_formula_vers)
2058: THEN
2059: GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
2060: 'WHAT', 'Passed formula_vers doesn''t match the batch''s formula_vers.');
2061: RAISE FND_API.G_EXC_ERROR;
2062: END IF;
2063:
2064: -- 7: routing_id
2065: IF ( NVL(l_wip_vr.routing_id, l_routing_id) <> l_routing_id)
2065: IF ( NVL(l_wip_vr.routing_id, l_routing_id) <> l_routing_id)
2066: THEN
2067: GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
2068: 'WHAT', 'Passed routing_id doesn''t match the batch''s routing_id.');
2069: RAISE FND_API.G_EXC_ERROR;
2070: END IF;
2071: -- 8: routing_no
2072: IF ( NVL(l_wip_vr.routing_no, l_routing_no) <> l_routing_no)
2073: THEN
2072: IF ( NVL(l_wip_vr.routing_no, l_routing_no) <> l_routing_no)
2073: THEN
2074: GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
2075: 'WHAT', 'Passed routing_no doesn''t match the batch''s routing_no.');
2076: RAISE FND_API.G_EXC_ERROR;
2077: END IF;
2078: -- 9: routing_vers
2079: IF ( NVL(l_wip_vr.routing_vers, l_routing_vers) <> l_routing_vers)
2080: THEN
2079: IF ( NVL(l_wip_vr.routing_vers, l_routing_vers) <> l_routing_vers)
2080: THEN
2081: GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
2082: 'WHAT', 'Passed routing_vers doesn''t match the batch''s routing_vers.');
2083: RAISE FND_API.G_EXC_ERROR;
2084: END IF;
2085:
2086: -- At this stage, either l_wip_vr.formula/routing.... are NULL
2087: -- or they are equal to the local variables.
2104: IF (c_recipe_no%NOTFOUND)
2105: THEN
2106: CLOSE c_recipe_no;
2107: GMD_API_PUB.Log_Message('GMD_RECIPE_NOT_FOUND');
2108: RAISE FND_API.G_EXC_ERROR;
2109: END IF;
2110: CLOSE c_recipe_no;
2111:
2112: -- Populated the defaults :
2133: IF (c_formula_id%NOTFOUND)
2134: THEN
2135: CLOSE c_formula_id;
2136: GMD_API_PUB.Log_Message('GMD_FORMULA_NOT_FOUND');
2137: RAISE FND_API.G_EXC_ERROR;
2138: END IF;
2139: CLOSE c_formula_id;
2140:
2141: -- Populated the defaults, ignoring the passed values :
2152: IF (c_formula_no%NOTFOUND)
2153: THEN
2154: CLOSE c_formula_no;
2155: GMD_API_PUB.Log_Message('GMD_FORMULA_NOT_FOUND');
2156: RAISE FND_API.G_EXC_ERROR;
2157: END IF;
2158: CLOSE c_formula_no;
2159:
2160: -- Populated the defaults :
2181: IF (c_routing_id%NOTFOUND)
2182: THEN
2183: CLOSE c_routing_id;
2184: GMD_API_PUB.Log_Message('GMD_ROUTING_NOT_FOUND');
2185: RAISE FND_API.G_EXC_ERROR;
2186: END IF;
2187: CLOSE c_routing_id;
2188:
2189: -- Populated the defaults, ignoring the passed values :
2199: IF (c_routing_no%NOTFOUND)
2200: THEN
2201: CLOSE c_routing_no;
2202: GMD_API_PUB.Log_Message('GMD_ROUTING_NOT_FOUND');
2203: RAISE FND_API.G_EXC_ERROR;
2204: END IF;
2205: CLOSE c_routing_no;
2206:
2207: -- Populated the defaults :
2224: IF (l_wip_vr.formula_id IS NULL)
2225: THEN
2226: GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
2227: 'WHAT', 'Formula id must be NOT NULL');
2228: RAISE FND_API.G_EXC_ERROR;
2229: END IF;
2230:
2231: OPEN c_formulaline_id;
2232: FETCH c_formulaline_id
2234: IF (c_formulaline_id%NOTFOUND)
2235: THEN
2236: CLOSE c_formulaline_id;
2237: GMD_API_PUB.Log_Message('GMD_FORMULA_LINE_NOT_FOUND');
2238: RAISE FND_API.G_EXC_ERROR;
2239: END IF;
2240: CLOSE c_formulaline_id;
2241: END IF;
2242:
2250: IF (l_wip_vr.batch_id IS NULL)
2251: THEN
2252: GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
2253: 'WHAT', 'Batch id must be NOT NULL');
2254: RAISE FND_API.G_EXC_ERROR;
2255: END IF;
2256:
2257: OPEN c_material_detail_id;
2258: FETCH c_material_detail_id
2260: IF (c_material_detail_id%NOTFOUND)
2261: THEN
2262: CLOSE c_material_detail_id;
2263: GMD_API_PUB.Log_Message('GMD_MATERIAL_DTL_NOT_FOUND');
2264: RAISE FND_API.G_EXC_ERROR;
2265: END IF;
2266: CLOSE c_material_detail_id;
2267: END IF;
2268:
2276: IF (l_wip_vr.step_id IS NULL AND l_wip_vr.step_no IS NOT NULL)
2277: THEN
2278: GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
2279: 'WHAT', 'Step id must be populated');
2280: RAISE FND_API.G_EXC_ERROR;
2281: END IF;
2282:
2283: IF (l_wip_vr.batch_id IS NOT NULL AND l_wip_vr.step_id IS NOT NULL)
2284: THEN
2289: IF (c_batchstep%NOTFOUND)
2290: THEN
2291: CLOSE c_batchstep;
2292: GMD_API_PUB.Log_Message('GMD_BATCH_STEP_NOT_FOUND');
2293: RAISE FND_API.G_EXC_ERROR;
2294: END IF;
2295: CLOSE c_batchstep;
2296:
2297: -- Populated the defaults, ignoring the passed values :
2306: IF (c_routingstep%NOTFOUND)
2307: THEN
2308: CLOSE c_routingstep;
2309: GMD_API_PUB.Log_Message('GMD_ROUTING_STEP_NOT_FOUND');
2310: RAISE FND_API.G_EXC_ERROR;
2311: END IF;
2312: CLOSE c_routingstep;
2313:
2314: -- Populated the defaults, ignoring the passed values :
2326: IF (c_oprn%NOTFOUND)
2327: THEN
2328: CLOSE c_oprn;
2329: GMD_API_PUB.Log_Message('GMD_BATCH_STEP_NOT_FOUND');
2330: RAISE FND_API.G_EXC_ERROR;
2331: END IF;
2332: CLOSE c_oprn;
2333:
2334: -- Populated the defaults, ignoring the passed values :
2341: -- All Systems Go...
2342: x_wip_vr := l_wip_vr;
2343:
2344: EXCEPTION
2345: WHEN FND_API.G_EXC_ERROR THEN
2346: x_return_status := FND_API.G_RET_STS_ERROR ;
2347: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2348: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2349: WHEN OTHERS THEN
2342: x_wip_vr := l_wip_vr;
2343:
2344: EXCEPTION
2345: WHEN FND_API.G_EXC_ERROR THEN
2346: x_return_status := FND_API.G_RET_STS_ERROR ;
2347: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2348: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2349: WHEN OTHERS THEN
2350: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2343:
2344: EXCEPTION
2345: WHEN FND_API.G_EXC_ERROR THEN
2346: x_return_status := FND_API.G_RET_STS_ERROR ;
2347: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2348: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2349: WHEN OTHERS THEN
2350: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2351:
2344: EXCEPTION
2345: WHEN FND_API.G_EXC_ERROR THEN
2346: x_return_status := FND_API.G_RET_STS_ERROR ;
2347: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2348: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2349: WHEN OTHERS THEN
2350: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2351:
2352: END check_for_null_and_fks_in_wvr;
2346: x_return_status := FND_API.G_RET_STS_ERROR ;
2347: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2348: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2349: WHEN OTHERS THEN
2350: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2351:
2352: END check_for_null_and_fks_in_wvr;
2353:
2354:
2544: e_error_fetch_item EXCEPTION;
2545:
2546: BEGIN
2547: -- Initialize API return status to success
2548: x_return_status := FND_API.G_RET_STS_SUCCESS;
2549:
2550: IF NOT (p_operation in ('INSERT', 'UPDATE', 'DELETE')) THEN
2551: -- Invalid Operation
2552: GMD_API_PUB.Log_Message('GMD_INVALID_OPERATION');
2549:
2550: IF NOT (p_operation in ('INSERT', 'UPDATE', 'DELETE')) THEN
2551: -- Invalid Operation
2552: GMD_API_PUB.Log_Message('GMD_INVALID_OPERATION');
2553: RAISE FND_API.G_EXC_ERROR;
2554: END IF;
2555:
2556: -- Verify that the specification exists.
2557: l_spec.spec_id := p_cust_vr.spec_id;
2592: );
2593: -- No need if called from FORM since it is already
2594: -- done in the form
2595:
2596: IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2597: -- Message is alrady logged by check_for_null procedure
2598: RAISE FND_API.G_EXC_ERROR;
2599: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2600: -- Message is alrady logged by check_for_null procedure
2594: -- done in the form
2595:
2596: IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2597: -- Message is alrady logged by check_for_null procedure
2598: RAISE FND_API.G_EXC_ERROR;
2599: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2600: -- Message is alrady logged by check_for_null procedure
2601: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2602: END IF;
2595:
2596: IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2597: -- Message is alrady logged by check_for_null procedure
2598: RAISE FND_API.G_EXC_ERROR;
2599: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2600: -- Message is alrady logged by check_for_null procedure
2601: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2602: END IF;
2603: END IF;
2597: -- Message is alrady logged by check_for_null procedure
2598: RAISE FND_API.G_EXC_ERROR;
2599: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2600: -- Message is alrady logged by check_for_null procedure
2601: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2602: END IF;
2603: END IF;
2604:
2605: -- First Verify that the SAME VR does not exists
2608: THEN
2609: -- Disaster, Trying to insert duplicate
2610: -- bug 2630007, odaboval put the message in function cust_vr_exist.
2611: -- GMD_API_PUB.Log_Message('GMD_CUST_VR_EXIST');
2612: RAISE FND_API.G_EXC_ERROR;
2613: END IF;
2614:
2615: -- Sample Quantity UOM must be convertible to Item's UOM
2616: BEGIN
2640: uom_rate => l_uom_rate );
2641:
2642: IF l_uom_rate = -99999 THEN
2643: GMD_API_PUB.Log_Message('GMD_UOM_CONVERSION_ERROR');
2644: RAISE FND_API.G_EXC_ERROR;
2645: END IF;
2646: END IF;
2647:
2648: -- No need to check the return status because above procedure
2651: -- The Start Date must be less than the End Date
2652: If ( p_cust_vr.end_date IS NOT NULL AND
2653: p_cust_vr.start_date > p_cust_vr.end_date) THEN
2654: GMD_API_PUB.Log_Message('GMD_SPEC_VR_EFF_DATE_ERROR');
2655: RAISE FND_API.G_EXC_ERROR;
2656: END IF;
2657:
2658: -- Spec VR Status Must be less than Spec Status upto Appoved Stages
2659: IF (floor(l_spec.spec_status/100) <= 7 AND
2659: IF (floor(l_spec.spec_status/100) <= 7 AND
2660: floor(p_cust_vr.spec_vr_status/100) <= 7 AND
2661: p_cust_vr.spec_vr_status > l_spec.spec_status) THEN
2662: GMD_API_PUB.Log_Message('GMD_SPEC_VR_STATUS_HIGHER');
2663: RAISE FND_API.G_EXC_ERROR;
2664: END IF;
2665:
2666: -- All systems GO...
2667:
2665:
2666: -- All systems GO...
2667:
2668: EXCEPTION
2669: WHEN FND_API.G_EXC_ERROR OR
2670: e_spec_fetch_error OR
2671: e_smpl_plan_fetch_error OR
2672: e_error_fetch_item
2673: THEN
2670: e_spec_fetch_error OR
2671: e_smpl_plan_fetch_error OR
2672: e_error_fetch_item
2673: THEN
2674: x_return_status := FND_API.G_RET_STS_ERROR ;
2675: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2676: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2677: WHEN OTHERS THEN
2678: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2671: e_smpl_plan_fetch_error OR
2672: e_error_fetch_item
2673: THEN
2674: x_return_status := FND_API.G_RET_STS_ERROR ;
2675: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2676: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2677: WHEN OTHERS THEN
2678: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2679:
2672: e_error_fetch_item
2673: THEN
2674: x_return_status := FND_API.G_RET_STS_ERROR ;
2675: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2676: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2677: WHEN OTHERS THEN
2678: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2679:
2680:
2674: x_return_status := FND_API.G_RET_STS_ERROR ;
2675: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2676: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2677: WHEN OTHERS THEN
2678: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2679:
2680:
2681: END validate_cust_vr;
2682:
2811: OR p_cust_vr.last_update_date IS NULL)
2812: THEN
2813: GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
2814: 'WHAT', 'the dates must not be NULL');
2815: RAISE FND_API.G_EXC_ERROR;
2816: END IF;
2817:
2818: --=========================================================================
2819: -- Organization : must belong to the USER
2830: THEN
2831: CLOSE c_orgn;
2832: GMD_API_PUB.Log_Message('GMD_ORGANIZATION_ID_NOT_FOUND',
2833: 'ORGN_ID', p_cust_vr.organization_id);
2834: RAISE FND_API.G_EXC_ERROR;
2835: END IF;
2836: CLOSE c_orgn;*/
2837:
2838: -- replace above with function as above check was not working as designed - bug 5223014 rework
2837:
2838: -- replace above with function as above check was not working as designed - bug 5223014 rework
2839:
2840: IF NOT (gmd_api_grp.OrgnAccessible(p_cust_vr.organization_id)) THEN
2841: RAISE FND_API.G_EXC_ERROR;
2842: END IF;
2843:
2844: END IF;
2845:
2879: THEN
2880: CLOSE c_status;
2881: GMD_API_PUB.Log_Message('GMD_SPEC_STATUS_NOT_FOUND',
2882: 'STATUS', p_cust_vr.spec_vr_status);
2883: RAISE FND_API.G_EXC_ERROR;
2884: END IF;
2885: CLOSE c_status;
2886:
2887:
2890: --=========================================================================
2891: IF (p_cust_vr.start_date IS NULL)
2892: THEN
2893: GMD_API_PUB.Log_Message('GMD_SPEC_VR_START_DATE_REQD');
2894: RAISE FND_API.G_EXC_ERROR;
2895: END IF;
2896:
2897: --=========================================================================
2898: -- COA section :
2907: --=========================================================================
2908: IF (p_cust_vr.cust_id IS NULL)
2909: THEN
2910: GMD_API_PUB.Log_Message('GMD_CUSTOMER_REQD');
2911: RAISE FND_API.G_EXC_ERROR;
2912: ELSE
2913: OPEN c_cust;
2914: FETCH c_cust
2915: INTO dummy;
2916: IF (c_cust%NOTFOUND)
2917: THEN
2918: CLOSE c_cust;
2919: GMD_API_PUB.Log_Message('GMD_CUSTOMER_NOT_FOUND');
2920: RAISE FND_API.G_EXC_ERROR;
2921: END IF;
2922: CLOSE c_cust;
2923: END IF;
2924:
2933: IF (c_org%NOTFOUND)
2934: THEN
2935: CLOSE c_org;
2936: GMD_API_PUB.Log_Message('GMD_ORG_NOT_FOUND');
2937: RAISE FND_API.G_EXC_ERROR;
2938: END IF;
2939: CLOSE c_org;
2940: END IF;
2941:
2947: IF (p_cust_vr.cust_id IS NULL)
2948: THEN
2949: GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
2950: 'WHAT', 'the customer number must not be NULL');
2951: RAISE FND_API.G_EXC_ERROR;
2952: END IF;
2953:
2954: OPEN c_ship_to;
2955: FETCH c_ship_to
2957: IF (c_ship_to%NOTFOUND)
2958: THEN
2959: CLOSE c_ship_to;
2960: GMD_API_PUB.Log_Message('GMD_SHIP_TO_NOT_FOUND');
2961: RAISE FND_API.G_EXC_ERROR;
2962: END IF;
2963: CLOSE c_ship_to;
2964: END IF;
2965:
2974: IF (c_order%NOTFOUND)
2975: THEN
2976: CLOSE c_order;
2977: GMD_API_PUB.Log_Message('GMD_ORDER_NOT_FOUND');
2978: RAISE FND_API.G_EXC_ERROR;
2979: END IF;
2980: CLOSE c_order;
2981: END IF;
2982:
2991: IF (p_cust_vr.order_id IS NULL)
2992: THEN
2993: GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
2994: 'WHAT', 'the order number must not be NULL');
2995: RAISE FND_API.G_EXC_ERROR;
2996: END IF;
2997: IF (p_cust_vr.order_line_id IS NULL
2998: OR p_cust_vr.order_line IS NULL)
2999: THEN
2998: OR p_cust_vr.order_line IS NULL)
2999: THEN
3000: GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
3001: 'WHAT', 'the order line AND id must not be NULL');
3002: RAISE FND_API.G_EXC_ERROR;
3003: END IF;
3004:
3005: OPEN c_order_line;
3006: FETCH c_order_line
3008: IF (c_order_line%NOTFOUND)
3009: THEN
3010: CLOSE c_order_line;
3011: GMD_API_PUB.Log_Message('GMD_ORDER_LINE_NOT_FOUND');
3012: RAISE FND_API.G_EXC_ERROR;
3013: END IF;
3014: CLOSE c_order_line;
3015: END IF;
3016:
3016:
3017: -- All Systems Go...
3018:
3019: EXCEPTION
3020: WHEN FND_API.G_EXC_ERROR THEN
3021: x_return_status := FND_API.G_RET_STS_ERROR ;
3022: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3023: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3024: WHEN OTHERS THEN
3017: -- All Systems Go...
3018:
3019: EXCEPTION
3020: WHEN FND_API.G_EXC_ERROR THEN
3021: x_return_status := FND_API.G_RET_STS_ERROR ;
3022: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3023: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3024: WHEN OTHERS THEN
3025: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3018:
3019: EXCEPTION
3020: WHEN FND_API.G_EXC_ERROR THEN
3021: x_return_status := FND_API.G_RET_STS_ERROR ;
3022: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3023: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3024: WHEN OTHERS THEN
3025: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3026:
3019: EXCEPTION
3020: WHEN FND_API.G_EXC_ERROR THEN
3021: x_return_status := FND_API.G_RET_STS_ERROR ;
3022: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3023: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3024: WHEN OTHERS THEN
3025: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3026:
3027: END check_for_null_and_fks_in_cvr;
3021: x_return_status := FND_API.G_RET_STS_ERROR ;
3022: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3023: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3024: WHEN OTHERS THEN
3025: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3026:
3027: END check_for_null_and_fks_in_cvr;
3028:
3029:
3194: e_error_fetch_item EXCEPTION;
3195:
3196: BEGIN
3197: -- Initialize API return status to success
3198: x_return_status := FND_API.G_RET_STS_SUCCESS;
3199:
3200: IF NOT (p_operation in ('INSERT', 'UPDATE', 'DELETE')) THEN
3201: -- Invalid Operation
3202: GMD_API_PUB.Log_Message('GMD_INVALID_OPERATION');
3199:
3200: IF NOT (p_operation in ('INSERT', 'UPDATE', 'DELETE')) THEN
3201: -- Invalid Operation
3202: GMD_API_PUB.Log_Message('GMD_INVALID_OPERATION');
3203: RAISE FND_API.G_EXC_ERROR;
3204: END IF;
3205:
3206: -- Verify that the specification exists.
3207: l_spec.spec_id := p_supp_vr.spec_id;
3236: --For mini pack L, bug 3439865
3237: IF (nvl(p_supp_vr.auto_sample_ind,'N') not in ('N','Y')) THEN
3238: GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
3239: 'WHAT', 'INVALID_AUTO_SAMPLE_IND');
3240: RAISE FND_API.G_EXC_ERROR;
3241: END IF;
3242: -- end 3439865
3243: -- Check for NULLs and Valid Foreign Keys in the input parameter
3244: GMD_SPEC_VRS_GRP.check_for_null_and_fks_in_svr
3249: );
3250: -- No need if called from FORM since it is already
3251: -- done in the form
3252:
3253: IF l_return_status = FND_API.G_RET_STS_ERROR THEN
3254: -- Message is alrady logged by check_for_null procedure
3255: RAISE FND_API.G_EXC_ERROR;
3256: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3257: -- Message is alrady logged by check_for_null procedure
3251: -- done in the form
3252:
3253: IF l_return_status = FND_API.G_RET_STS_ERROR THEN
3254: -- Message is alrady logged by check_for_null procedure
3255: RAISE FND_API.G_EXC_ERROR;
3256: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3257: -- Message is alrady logged by check_for_null procedure
3258: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3259: END IF;
3252:
3253: IF l_return_status = FND_API.G_RET_STS_ERROR THEN
3254: -- Message is alrady logged by check_for_null procedure
3255: RAISE FND_API.G_EXC_ERROR;
3256: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3257: -- Message is alrady logged by check_for_null procedure
3258: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3259: END IF;
3260: END IF;
3254: -- Message is alrady logged by check_for_null procedure
3255: RAISE FND_API.G_EXC_ERROR;
3256: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3257: -- Message is alrady logged by check_for_null procedure
3258: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3259: END IF;
3260: END IF;
3261:
3262: -- First Verify that the SAME VR does not exists
3265: THEN
3266: -- Disaster, Trying to insert duplicate
3267: -- bug 2630007, odaboval put the message in function supp_vr_exist.
3268: -- GMD_API_PUB.Log_Message('GMD_SUPP_VR_EXIST');
3269: RAISE FND_API.G_EXC_ERROR;
3270: END IF;
3271:
3272: -- Check to make sure that a samplingplan exists
3273: -- if auto sample flag on
3306: uom_rate => l_uom_rate );
3307:
3308: IF l_uom_rate = -99999 THEN
3309: GMD_API_PUB.Log_Message('GMD_UOM_CONVERSION_ERROR');
3310: RAISE FND_API.G_EXC_ERROR;
3311: END IF;
3312: END IF;
3313:
3314: -- No need to check the return status because above procedure
3317: -- The Start Date must be less than the End Date
3318: If ( p_supp_vr.end_date IS NOT NULL AND
3319: p_supp_vr.start_date > p_supp_vr.end_date) THEN
3320: GMD_API_PUB.Log_Message('GMD_SPEC_VR_EFF_DATE_ERROR');
3321: RAISE FND_API.G_EXC_ERROR;
3322: END IF;
3323:
3324: -- Spec VR Status Must be less than Spec Status upto Appoved Stages
3325: IF (floor(l_spec.spec_status/100) <= 7 AND
3325: IF (floor(l_spec.spec_status/100) <= 7 AND
3326: floor(p_supp_vr.spec_vr_status/100) <= 7 AND
3327: p_supp_vr.spec_vr_status > l_spec.spec_status) THEN
3328: GMD_API_PUB.Log_Message('GMD_SPEC_VR_STATUS_HIGHER');
3329: RAISE FND_API.G_EXC_ERROR;
3330: END IF;
3331:
3332: -- All systems GO...
3333:
3331:
3332: -- All systems GO...
3333:
3334: EXCEPTION
3335: WHEN FND_API.G_EXC_ERROR OR
3336: e_spec_fetch_error OR
3337: e_smpl_plan_fetch_error OR
3338: e_error_fetch_item
3339: THEN
3336: e_spec_fetch_error OR
3337: e_smpl_plan_fetch_error OR
3338: e_error_fetch_item
3339: THEN
3340: x_return_status := FND_API.G_RET_STS_ERROR ;
3341: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3342: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3343: WHEN OTHERS THEN
3344: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3337: e_smpl_plan_fetch_error OR
3338: e_error_fetch_item
3339: THEN
3340: x_return_status := FND_API.G_RET_STS_ERROR ;
3341: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3342: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3343: WHEN OTHERS THEN
3344: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3345:
3338: e_error_fetch_item
3339: THEN
3340: x_return_status := FND_API.G_RET_STS_ERROR ;
3341: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3342: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3343: WHEN OTHERS THEN
3344: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3345:
3346:
3340: x_return_status := FND_API.G_RET_STS_ERROR ;
3341: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3342: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3343: WHEN OTHERS THEN
3344: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3345:
3346:
3347: END validate_supp_vr;
3348:
3457: OR p_supp_vr.last_update_date IS NULL)
3458: THEN
3459: GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
3460: 'WHAT', 'the dates must not be NULL');
3461: RAISE FND_API.G_EXC_ERROR;
3462: END IF;
3463:
3464:
3465:
3471: IF c_orgn%NOTFOUND THEN
3472: CLOSE c_orgn;
3473: GMD_API_PUB.Log_Message('GMD_ORGANIZATION_ID_NOT_FOUND',
3474: 'ORGN_ID', p_supp_vr.organization_id);
3475: RAISE FND_API.G_EXC_ERROR;
3476: END IF;
3477: CLOSE c_orgn;
3478: END IF;
3479:
3478: END IF;
3479:
3480: --srakrish BUG 5251172: Checking if the responsibility is available to the organization.
3481: IF NOT (gmd_api_grp.OrgnAccessible(p_supp_vr.organization_id)) THEN
3482: RAISE FND_API.G_EXC_ERROR;
3483: END IF;
3484:
3485:
3486:
3498: FND_MESSAGE.SET_NAME('GMD','GMD_NOTFOUND');
3499: FND_MESSAGE.SET_TOKEN('WHAT', 'INVENTORY_ITEM_ID');
3500: FND_MESSAGE.SET_TOKEN('VALUE', p_spec.inventory_item_id);
3501: FND_MSG_PUB.ADD;
3502: RAISE FND_API.G_EXC_ERROR;
3503: END IF;
3504: CLOSE c_item_controls;
3505:
3506: --=========================================================================
3530: THEN
3531: CLOSE c_status;
3532: GMD_API_PUB.Log_Message('GMD_SPEC_STATUS_NOT_FOUND',
3533: 'STATUS', p_supp_vr.spec_vr_status);
3534: RAISE FND_API.G_EXC_ERROR;
3535: END IF;
3536: CLOSE c_status;
3537:
3538: --=========================================================================
3540: --=========================================================================
3541: IF (p_supp_vr.start_date IS NULL)
3542: THEN
3543: GMD_API_PUB.Log_Message('GMD_SPEC_VR_START_DATE_REQD');
3544: RAISE FND_API.G_EXC_ERROR;
3545: END IF;
3546:
3547: --=========================================================================
3548: -- COA section :
3557: --=========================================================================
3558: IF (p_supp_vr.supplier_id IS NULL)
3559: THEN
3560: GMD_API_PUB.Log_Message('GMD_SUPPLIER_REQD');
3561: RAISE FND_API.G_EXC_ERROR;
3562: ELSE
3563: OPEN c_supplier;
3564: FETCH c_supplier INTO dummy;
3565: IF (c_supplier%NOTFOUND)
3565: IF (c_supplier%NOTFOUND)
3566: THEN
3567: CLOSE c_supplier;
3568: GMD_API_PUB.Log_Message('GMD_SUPPLIER_NOT_FOUND');
3569: RAISE FND_API.G_EXC_ERROR;
3570: END IF;
3571: CLOSE c_supplier;
3572: END IF;
3573:
3585: FND_MESSAGE.SET_NAME('GMD','GMD_NOTFOUND');
3586: FND_MESSAGE.SET_TOKEN('WHAT', 'SUPPLIER_SITE_ID');
3587: FND_MESSAGE.SET_TOKEN('VALUE', p_supp_vr.supplier_site_id);
3588: FND_MSG_PUB.ADD;
3589: RAISE FND_API.G_EXC_ERROR;
3590: END IF;
3591: CLOSE c_supplier_site;
3592: END IF;
3593:
3601: IF (p_supp_vr.supplier_site_id IS NULL)
3602: THEN
3603: GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
3604: 'WHAT', 'supplier_site_id must not be NULL');
3605: RAISE FND_API.G_EXC_ERROR;
3606: END IF;
3607:
3608: OPEN c_po;
3609: FETCH c_po INTO dummy;
3610: IF (c_po%NOTFOUND)
3611: THEN
3612: CLOSE c_po;
3613: GMD_API_PUB.Log_Message('GMD_PO_NOT_FOUND');
3614: RAISE FND_API.G_EXC_ERROR;
3615: END IF;
3616: CLOSE c_po;
3617: END IF;
3618:
3626: IF (p_supp_vr.po_header_id IS NULL)
3627: THEN
3628: GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
3629: 'WHAT', 'po_header_id must not be NULL');
3630: RAISE FND_API.G_EXC_ERROR;
3631: END IF;
3632:
3633: IF (p_supp_vr.supplier_site_id IS NULL)
3634: THEN
3633: IF (p_supp_vr.supplier_site_id IS NULL)
3634: THEN
3635: GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
3636: 'WHAT', 'supplier site must not be NULL');
3637: RAISE FND_API.G_EXC_ERROR;
3638: END IF;
3639:
3640: OPEN c_po_line;
3641: FETCH c_po_line INTO dummy;
3642: IF (c_po_line%NOTFOUND)
3643: THEN
3644: CLOSE c_po_line;
3645: GMD_API_PUB.Log_Message('GMD_PO_LINE_NOT_FOUND');
3646: RAISE FND_API.G_EXC_ERROR;
3647: END IF;
3648: CLOSE c_po_line;
3649: END IF;
3650:
3650:
3651: -- All Systems Go...
3652:
3653: EXCEPTION
3654: WHEN FND_API.G_EXC_ERROR THEN
3655: x_return_status := FND_API.G_RET_STS_ERROR ;
3656: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3657: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3658: WHEN OTHERS THEN
3651: -- All Systems Go...
3652:
3653: EXCEPTION
3654: WHEN FND_API.G_EXC_ERROR THEN
3655: x_return_status := FND_API.G_RET_STS_ERROR ;
3656: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3657: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3658: WHEN OTHERS THEN
3659: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3652:
3653: EXCEPTION
3654: WHEN FND_API.G_EXC_ERROR THEN
3655: x_return_status := FND_API.G_RET_STS_ERROR ;
3656: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3657: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3658: WHEN OTHERS THEN
3659: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3660:
3653: EXCEPTION
3654: WHEN FND_API.G_EXC_ERROR THEN
3655: x_return_status := FND_API.G_RET_STS_ERROR ;
3656: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3657: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3658: WHEN OTHERS THEN
3659: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3660:
3661: END check_for_null_and_fks_in_svr;
3655: x_return_status := FND_API.G_RET_STS_ERROR ;
3656: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3657: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3658: WHEN OTHERS THEN
3659: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3660:
3661: END check_for_null_and_fks_in_svr;
3662:
3663:
3791: l_inventory_spec_vrs_out GMD_INVENTORY_SPEC_VRS%ROWTYPE;
3792:
3793: BEGIN
3794: l_progress := '010';
3795: x_return_status := FND_API.G_RET_STS_SUCCESS ;
3796:
3797: -- validate for primary key
3798: -- ========================
3799: IF p_spec_id IS NULL THEN
3797: -- validate for primary key
3798: -- ========================
3799: IF p_spec_id IS NULL THEN
3800: GMD_API_PUB.Log_Message('GMD_SPEC_ID_REQUIRED');
3801: RAISE FND_API.G_EXC_ERROR;
3802: ELSE
3803: l_inventory_spec_vrs.spec_id := p_spec_id;
3804: END IF;
3805:
3804: END IF;
3805:
3806: IF p_spec_vr_id IS NULL THEN
3807: GMD_API_PUB.Log_Message('GMD_SPEC_VR_ID_REQUIRED');
3808: RAISE FND_API.G_EXC_ERROR;
3809: ELSE
3810: l_inventory_spec_vrs.spec_vr_id := p_spec_vr_id;
3811: END IF;
3812:
3817: GMD_API_PUB.Log_Message('GMD_FAILED_TO_FETCH_ROW',
3818: 'l_table_name', 'GMD_INVENTORY_SPEC_VRS',
3819: 'l_column_name', 'SPEC_VR_ID',
3820: 'l_key_value', l_inventory_spec_vrs.spec_vr_id);
3821: RAISE FND_API.G_EXC_ERROR;
3822: END IF;
3823:
3824: l_inventory_spec_vrs := l_inventory_spec_vrs_out ;
3825:
3830: GMD_API_PUB.Log_Message('GMD_RECORD_DELETE_MARKED',
3831: 'l_table_name', 'GMD_INVENTORY_SPEC_VRS',
3832: 'l_column_name', 'SPEC_VR_ID',
3833: 'l_key_value', l_inventory_spec_vrs.spec_vr_id);
3834: RAISE FND_API.G_EXC_ERROR;
3835: END IF;
3836:
3837:
3838: EXCEPTION
3835: END IF;
3836:
3837:
3838: EXCEPTION
3839: WHEN FND_API.G_EXC_ERROR THEN
3840: x_return_status := FND_API.G_RET_STS_ERROR ;
3841: x_message_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
3842:
3843: WHEN OTHERS THEN
3836:
3837:
3838: EXCEPTION
3839: WHEN FND_API.G_EXC_ERROR THEN
3840: x_return_status := FND_API.G_RET_STS_ERROR ;
3841: x_message_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
3842:
3843: WHEN OTHERS THEN
3844: FND_MESSAGE.Set_Name('GMD','GMD_API_ERROR');
3837:
3838: EXCEPTION
3839: WHEN FND_API.G_EXC_ERROR THEN
3840: x_return_status := FND_API.G_RET_STS_ERROR ;
3841: x_message_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
3842:
3843: WHEN OTHERS THEN
3844: FND_MESSAGE.Set_Name('GMD','GMD_API_ERROR');
3845: FND_MESSAGE.Set_Token('PACKAGE','GMD_SPEC_GRP.VALIDATE_BEFORE_DELETE');
3845: FND_MESSAGE.Set_Token('PACKAGE','GMD_SPEC_GRP.VALIDATE_BEFORE_DELETE');
3846: FND_MESSAGE.Set_Token('ERROR', substr(sqlerrm,1,100));
3847: FND_MESSAGE.Set_Token('POSITION',l_progress );
3848: FND_MSG_PUB.ADD;
3849: x_message_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
3850: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3851:
3852: END VALIDATE_BEFORE_DELETE_INV_VRS ;
3853:
3846: FND_MESSAGE.Set_Token('ERROR', substr(sqlerrm,1,100));
3847: FND_MESSAGE.Set_Token('POSITION',l_progress );
3848: FND_MSG_PUB.ADD;
3849: x_message_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
3850: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3851:
3852: END VALIDATE_BEFORE_DELETE_INV_VRS ;
3853:
3854: /*===========================================================================
3875: l_wip_spec_vrs_out GMD_WIP_SPEC_VRS%ROWTYPE;
3876:
3877: BEGIN
3878: l_progress := '010';
3879: x_return_status := FND_API.G_RET_STS_SUCCESS ;
3880:
3881: -- validate for primary key
3882: -- ========================
3883: IF p_spec_id IS NULL THEN
3881: -- validate for primary key
3882: -- ========================
3883: IF p_spec_id IS NULL THEN
3884: GMD_API_PUB.Log_Message('GMD_SPEC_ID_REQUIRED');
3885: RAISE FND_API.G_EXC_ERROR;
3886: ELSE
3887: l_wip_spec_vrs.spec_id := p_spec_id;
3888: END IF;
3889:
3888: END IF;
3889:
3890: IF p_spec_vr_id IS NULL THEN
3891: GMD_API_PUB.Log_Message('GMD_SPEC_VR_ID_REQUIRED');
3892: RAISE FND_API.G_EXC_ERROR;
3893: ELSE
3894: l_wip_spec_vrs.spec_vr_id := p_spec_vr_id;
3895: END IF;
3896:
3901: GMD_API_PUB.Log_Message('GMD_FAILED_TO_FETCH_ROW',
3902: 'l_table_name', 'GMD_WIP_SPEC_VRS',
3903: 'l_column_name', 'SPEC_VR_ID',
3904: 'l_key_value', l_wip_spec_vrs.spec_vr_id);
3905: RAISE FND_API.G_EXC_ERROR;
3906: END IF;
3907:
3908: l_wip_spec_vrs := l_wip_spec_vrs_out ;
3909:
3914: GMD_API_PUB.Log_Message('GMD_RECORD_DELETE_MARKED',
3915: 'l_table_name', 'GMD_WIP_SPEC_VRS',
3916: 'l_column_name', 'SPEC_VR_ID',
3917: 'l_key_value', l_wip_spec_vrs.spec_vr_id);
3918: RAISE FND_API.G_EXC_ERROR;
3919: END IF;
3920:
3921:
3922: EXCEPTION
3919: END IF;
3920:
3921:
3922: EXCEPTION
3923: WHEN FND_API.G_EXC_ERROR THEN
3924: x_return_status := FND_API.G_RET_STS_ERROR ;
3925: x_message_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
3926:
3927: WHEN OTHERS THEN
3920:
3921:
3922: EXCEPTION
3923: WHEN FND_API.G_EXC_ERROR THEN
3924: x_return_status := FND_API.G_RET_STS_ERROR ;
3925: x_message_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
3926:
3927: WHEN OTHERS THEN
3928: FND_MESSAGE.Set_Name('GMD','GMD_API_ERROR');
3921:
3922: EXCEPTION
3923: WHEN FND_API.G_EXC_ERROR THEN
3924: x_return_status := FND_API.G_RET_STS_ERROR ;
3925: x_message_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
3926:
3927: WHEN OTHERS THEN
3928: FND_MESSAGE.Set_Name('GMD','GMD_API_ERROR');
3929: FND_MESSAGE.Set_Token('PACKAGE','GMD_SPEC_GRP.VALIDATE_BEFORE_DELETE');
3929: FND_MESSAGE.Set_Token('PACKAGE','GMD_SPEC_GRP.VALIDATE_BEFORE_DELETE');
3930: FND_MESSAGE.Set_Token('ERROR', substr(sqlerrm,1,100));
3931: FND_MESSAGE.Set_Token('POSITION',l_progress );
3932: FND_MSG_PUB.ADD;
3933: x_message_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
3934: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3935:
3936: END VALIDATE_BEFORE_DELETE_WIP_VRS ;
3937:
3930: FND_MESSAGE.Set_Token('ERROR', substr(sqlerrm,1,100));
3931: FND_MESSAGE.Set_Token('POSITION',l_progress );
3932: FND_MSG_PUB.ADD;
3933: x_message_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
3934: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3935:
3936: END VALIDATE_BEFORE_DELETE_WIP_VRS ;
3937:
3938: /*===========================================================================
3958: l_customer_spec_vrs_out GMD_CUSTOMER_SPEC_VRS%ROWTYPE;
3959:
3960: BEGIN
3961: l_progress := '010';
3962: x_return_status := FND_API.G_RET_STS_SUCCESS ;
3963:
3964: -- validate for primary key
3965: -- ========================
3966: IF p_spec_id IS NULL THEN
3964: -- validate for primary key
3965: -- ========================
3966: IF p_spec_id IS NULL THEN
3967: GMD_API_PUB.Log_Message('GMD_SPEC_ID_REQUIRED');
3968: RAISE FND_API.G_EXC_ERROR;
3969: ELSE
3970: l_customer_spec_vrs.spec_id := p_spec_id;
3971: END IF;
3972:
3971: END IF;
3972:
3973: IF p_spec_vr_id IS NULL THEN
3974: GMD_API_PUB.Log_Message('GMD_SPEC_VR_ID_REQUIRED');
3975: RAISE FND_API.G_EXC_ERROR;
3976: ELSE
3977: l_customer_spec_vrs.spec_vr_id := p_spec_vr_id;
3978: END IF;
3979:
3984: GMD_API_PUB.Log_Message('GMD_FAILED_TO_FETCH_ROW',
3985: 'l_table_name', 'GMD_CUSTOMER_SPEC_VRS',
3986: 'l_column_name', 'SPEC_VR_ID',
3987: 'l_key_value', l_customer_spec_vrs.spec_vr_id);
3988: RAISE FND_API.G_EXC_ERROR;
3989: END IF;
3990:
3991: l_customer_spec_vrs := l_customer_spec_vrs_out ;
3992:
3997: GMD_API_PUB.Log_Message('GMD_RECORD_DELETE_MARKED',
3998: 'l_table_name', 'GMD_CUSTOMER_SPEC_VRS',
3999: 'l_column_name', 'SPEC_VR_ID',
4000: 'l_key_value', l_customer_spec_vrs.spec_vr_id);
4001: RAISE FND_API.G_EXC_ERROR;
4002: END IF;
4003:
4004:
4005: EXCEPTION
4002: END IF;
4003:
4004:
4005: EXCEPTION
4006: WHEN FND_API.G_EXC_ERROR THEN
4007: x_return_status := FND_API.G_RET_STS_ERROR ;
4008: x_message_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
4009:
4010: WHEN OTHERS THEN
4003:
4004:
4005: EXCEPTION
4006: WHEN FND_API.G_EXC_ERROR THEN
4007: x_return_status := FND_API.G_RET_STS_ERROR ;
4008: x_message_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
4009:
4010: WHEN OTHERS THEN
4011: FND_MESSAGE.Set_Name('GMD','GMD_API_ERROR');
4004:
4005: EXCEPTION
4006: WHEN FND_API.G_EXC_ERROR THEN
4007: x_return_status := FND_API.G_RET_STS_ERROR ;
4008: x_message_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
4009:
4010: WHEN OTHERS THEN
4011: FND_MESSAGE.Set_Name('GMD','GMD_API_ERROR');
4012: FND_MESSAGE.Set_Token('PACKAGE','GMD_SPEC_GRP.VALIDATE_BEFORE_DELETE');
4012: FND_MESSAGE.Set_Token('PACKAGE','GMD_SPEC_GRP.VALIDATE_BEFORE_DELETE');
4013: FND_MESSAGE.Set_Token('ERROR', substr(sqlerrm,1,100));
4014: FND_MESSAGE.Set_Token('POSITION',l_progress );
4015: FND_MSG_PUB.ADD;
4016: x_message_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
4017: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4018:
4019: END VALIDATE_BEFORE_DELETE_CST_VRS ;
4020:
4013: FND_MESSAGE.Set_Token('ERROR', substr(sqlerrm,1,100));
4014: FND_MESSAGE.Set_Token('POSITION',l_progress );
4015: FND_MSG_PUB.ADD;
4016: x_message_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
4017: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4018:
4019: END VALIDATE_BEFORE_DELETE_CST_VRS ;
4020:
4021: /*===========================================================================
4041: l_supplier_spec_vrs_out GMD_SUPPLIER_SPEC_VRS%ROWTYPE;
4042:
4043: BEGIN
4044: l_progress := '010';
4045: x_return_status := FND_API.G_RET_STS_SUCCESS ;
4046:
4047: -- validate for primary key
4048: -- ========================
4049: IF p_spec_id IS NULL THEN
4047: -- validate for primary key
4048: -- ========================
4049: IF p_spec_id IS NULL THEN
4050: GMD_API_PUB.Log_Message('GMD_SPEC_ID_REQUIRED');
4051: RAISE FND_API.G_EXC_ERROR;
4052: ELSE
4053: l_supplier_spec_vrs.spec_id := p_spec_id;
4054: END IF;
4055:
4054: END IF;
4055:
4056: IF p_spec_vr_id IS NULL THEN
4057: GMD_API_PUB.Log_Message('GMD_SPEC_VR_ID_REQUIRED');
4058: RAISE FND_API.G_EXC_ERROR;
4059: ELSE
4060: l_supplier_spec_vrs.spec_vr_id := p_spec_vr_id;
4061: END IF;
4062:
4067: GMD_API_PUB.Log_Message('GMD_FAILED_TO_FETCH_ROW',
4068: 'l_table_name', 'GMD_SUPPLIER_SPEC_VRS',
4069: 'l_column_name', 'SPEC_VR_ID',
4070: 'l_key_value', l_supplier_spec_vrs.spec_vr_id);
4071: RAISE FND_API.G_EXC_ERROR;
4072: END IF;
4073:
4074: l_supplier_spec_vrs := l_supplier_spec_vrs_out ;
4075:
4080: GMD_API_PUB.Log_Message('GMD_RECORD_DELETE_MARKED',
4081: 'l_table_name', 'GMD_SUPPLIER_SPEC_VRS',
4082: 'l_column_name', 'SPEC_VR_ID',
4083: 'l_key_value', l_supplier_spec_vrs.spec_vr_id);
4084: RAISE FND_API.G_EXC_ERROR;
4085: END IF;
4086:
4087:
4088: EXCEPTION
4085: END IF;
4086:
4087:
4088: EXCEPTION
4089: WHEN FND_API.G_EXC_ERROR THEN
4090: x_return_status := FND_API.G_RET_STS_ERROR ;
4091: x_message_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
4092:
4093: WHEN OTHERS THEN
4086:
4087:
4088: EXCEPTION
4089: WHEN FND_API.G_EXC_ERROR THEN
4090: x_return_status := FND_API.G_RET_STS_ERROR ;
4091: x_message_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
4092:
4093: WHEN OTHERS THEN
4094: FND_MESSAGE.Set_Name('GMD','GMD_API_ERROR');
4087:
4088: EXCEPTION
4089: WHEN FND_API.G_EXC_ERROR THEN
4090: x_return_status := FND_API.G_RET_STS_ERROR ;
4091: x_message_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
4092:
4093: WHEN OTHERS THEN
4094: FND_MESSAGE.Set_Name('GMD','GMD_API_ERROR');
4095: FND_MESSAGE.Set_Token('PACKAGE','GMD_SPEC_GRP.VALIDATE_BEFORE_DELETE');
4095: FND_MESSAGE.Set_Token('PACKAGE','GMD_SPEC_GRP.VALIDATE_BEFORE_DELETE');
4096: FND_MESSAGE.Set_Token('ERROR', substr(sqlerrm,1,100));
4097: FND_MESSAGE.Set_Token('POSITION',l_progress );
4098: FND_MSG_PUB.ADD;
4099: x_message_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
4100: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4101:
4102: END VALIDATE_BEFORE_DELETE_SUP_VRS ;
4103:
4096: FND_MESSAGE.Set_Token('ERROR', substr(sqlerrm,1,100));
4097: FND_MESSAGE.Set_Token('POSITION',l_progress );
4098: FND_MSG_PUB.ADD;
4099: x_message_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
4100: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4101:
4102: END VALIDATE_BEFORE_DELETE_SUP_VRS ;
4103:
4104: /*===========================================================================
4126: THEN
4127: FND_MESSAGE.SET_NAME('GMD','GMD_WRONG_VALUE');
4128: FND_MESSAGE.SET_TOKEN('WHAT', 'USER_ID');
4129: FND_MSG_PUB.ADD;
4130: RAISE FND_API.G_EXC_ERROR;
4131: ELSE
4132: OPEN c_who( p_user_id);
4133: FETCH c_who
4134: INTO dummy;
4139: FND_MESSAGE.SET_NAME('GMD','GMD_NOTFOUND');
4140: FND_MESSAGE.SET_TOKEN('WHAT', 'USER_ID');
4141: FND_MESSAGE.SET_TOKEN('VALUE', p_user_id);
4142: FND_MSG_PUB.ADD;
4143: RAISE FND_API.G_EXC_ERROR;
4144: END IF;
4145: CLOSE c_who;
4146: END IF;
4147:
4179: AND (p_coa_at_ship_ind <> 'Y')
4180: THEN
4181: GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
4182: 'WHAT', 'coa_at_ship_ind value must be either NULL or Y');
4183: RAISE FND_API.G_EXC_ERROR;
4184: END IF;
4185: IF (p_coa_at_invoice_ind IS NOT NULL)
4186: AND (p_coa_at_invoice_ind <> 'Y')
4187: THEN
4186: AND (p_coa_at_invoice_ind <> 'Y')
4187: THEN
4188: GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
4189: 'WHAT', 'coa_at_invoice_ind value must be either NULL or Y');
4190: RAISE FND_API.G_EXC_ERROR;
4191: END IF;
4192: IF (p_coa_req_from_supl_ind IS NOT NULL)
4193: AND (p_coa_req_from_supl_ind <> 'Y')
4194: THEN
4193: AND (p_coa_req_from_supl_ind <> 'Y')
4194: THEN
4195: GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
4196: 'WHAT', 'coa_req_from_supl_ind value must be either NULL or Y');
4197: RAISE FND_API.G_EXC_ERROR;
4198: END IF;
4199: IF (p_coa_type IS NOT NULL)
4200: THEN
4201: OPEN c_coa_type;
4207: FND_MESSAGE.Set_Name('GMD','GMD_NOTFOUND');
4208: FND_MESSAGE.Set_Token('WHAT', 'COA_TYPE');
4209: FND_MESSAGE.Set_Token('VALUE', p_coa_type);
4210: FND_MSG_PUB.Add;
4211: RAISE FND_API.G_EXC_ERROR;
4212: END IF;
4213: CLOSE c_coa_type;
4214: END IF;
4215:
4225: IF (p_coa_at_ship_ind IS NOT NULL)
4226: THEN
4227: GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
4228: 'WHAT', 'coa_at_ship_ind must be NULL');
4229: RAISE FND_API.G_EXC_ERROR;
4230: END IF;
4231:
4232: IF (p_coa_at_invoice_ind IS NOT NULL)
4233: THEN
4232: IF (p_coa_at_invoice_ind IS NOT NULL)
4233: THEN
4234: GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
4235: 'WHAT', 'coa_at_invoice_ind must be NULL');
4236: RAISE FND_API.G_EXC_ERROR;
4237: END IF;
4238:
4239: IF (p_coa_req_from_supl_ind IS NOT NULL)
4240: THEN
4239: IF (p_coa_req_from_supl_ind IS NOT NULL)
4240: THEN
4241: GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
4242: 'WHAT', 'coa_req_from_supl_ind must be NULL');
4243: RAISE FND_API.G_EXC_ERROR;
4244: END IF;
4245: END IF; -- coa_type is NULL
4246:
4247: END check_COA;
4299: AND (p_lot_optional_on_sample <> 'Y')
4300: THEN
4301: GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
4302: 'WHAT', 'lot_optional_on_sample value must be either NULL or Y');
4303: RAISE FND_API.G_EXC_ERROR;
4304: END IF;
4305:
4306: --Enhancement# 3476560. Added validation for 'Delayed Lot Entry' that it should be 'Y' or Null.
4307: IF (p_delayed_lot_entry IS NOT NULL)
4308: AND(p_delayed_lot_entry<>'Y')
4309: THEN
4310: GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
4311: 'WHAT', 'delayed_lot_entry value must be either NULL or Y');
4312: RAISE FND_API.G_EXC_ERROR;
4313: END IF;
4314:
4315: --Bug# 3652938. Added validation for the invalid combination of 'Lot Optional on Sample' and 'Delayed Lot Entry'.
4316: IF (p_lot_optional_on_sample IS NULL)
4317: AND(p_delayed_lot_entry = 'Y')
4318: THEN
4319: GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
4320: 'WHAT', 'delayed_lot_entry value cannot be Y when lot_optional_on_sample is NULL');
4321: RAISE FND_API.G_EXC_ERROR;
4322: END IF;
4323:
4324: IF (p_VR_type IN ('INVENTORY', 'WIP','SUPPLIER'))
4325: THEN
4327: AND (p_control_lot_attrib_ind <> 'Y')
4328: THEN
4329: GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
4330: 'WHAT', 'control_lot_attrib_ind value must be either NULL or Y');
4331: RAISE FND_API.G_EXC_ERROR;
4332: END IF;
4333:
4334: IF (p_sample_inv_trans_ind IS NOT NULL)
4335: AND (p_sample_inv_trans_ind <> 'Y')
4335: AND (p_sample_inv_trans_ind <> 'Y')
4336: THEN
4337: GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
4338: 'WHAT', 'sample_inv_trans_ind value must be either NULL or Y');
4339: RAISE FND_API.G_EXC_ERROR;
4340: END IF;
4341:
4342: -- Extra field for WIP :
4343: IF (p_VR_type = 'WIP')
4346: AND (p_control_batch_step_ind <> 'Y')
4347: THEN
4348: GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
4349: 'WHAT', 'control_batch_step_ind value must be either NULL or Y');
4350: RAISE FND_API.G_EXC_ERROR;
4351: END IF;
4352:
4353: -- Bug# 5440347 start
4354: IF (p_auto_complete_batch_step IS NOT NULL)
4355: AND (p_auto_complete_batch_step <> 'Y')
4356: THEN
4357: GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
4358: 'WHAT', 'auto_complete_batch_step value must be either NULL or Y');
4359: RAISE FND_API.G_EXC_ERROR;
4360: END IF;
4361: -- Bug# 5440347 end
4362:
4363:
4365: --IF ( p_lot_optional_on_sample IS NOT NULL)
4366: --THEN
4367: -- GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
4368: -- 'WHAT', 'lot_optional_on_sample must be NULL');
4369: -- RAISE FND_API.G_EXC_ERROR;
4370: --END IF;
4371:
4372: END IF;
4373:
4378: IF (p_lot_ctl = 1) AND (p_lot_optional_on_sample IS NOT NULL)
4379: THEN
4380: GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
4381: 'WHAT', 'For a non controlled item, lot_optional_on_sample must be NULL');
4382: RAISE FND_API.G_EXC_ERROR;
4383: END IF;
4384:
4385: IF (p_VR_type IN ('INVENTORY', 'WIP','SUPPLIER'))
4386: THEN
4388: IF (p_sample_inv_trans_ind IS NOT NULL)
4389: THEN
4390: GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
4391: 'WHAT', 'sample_inv_trans_ind must be NULL');
4392: RAISE FND_API.G_EXC_ERROR;
4393: END IF;
4394:
4395: --RLNAGARA Bug # 4900420 Removed the validation code for control_lot_attributes
4396:
4402: IF (p_control_batch_step_ind IS NOT NULL)
4403: THEN
4404: GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
4405: 'WHAT', 'control_batch_step_ind must be NULL');
4406: RAISE FND_API.G_EXC_ERROR;
4407: END IF;
4408: END IF;*/
4409: ELSE --p_lot_optional_on_sample IS NULL
4410:
4422: IF (p_control_lot_attrib_ind IS NOT NULL)
4423: THEN
4424: GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
4425: 'WHAT', 'control_lot_attrib_ind must be NULL');
4426: RAISE FND_API.G_EXC_ERROR;
4427: END IF;
4428:
4429: IF (p_in_spec_lot_status_id IS NOT NULL)
4430: THEN
4429: IF (p_in_spec_lot_status_id IS NOT NULL)
4430: THEN
4431: GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
4432: 'WHAT', 'in_spec_lot_status_id must be NULL');
4433: RAISE FND_API.G_EXC_ERROR;
4434: END IF;
4435:
4436: IF (p_out_of_spec_lot_status_id IS NOT NULL)
4437: THEN
4436: IF (p_out_of_spec_lot_status_id IS NOT NULL)
4437: THEN
4438: GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
4439: 'WHAT', 'out_of_spec_lot_status_id must be NULL');
4440: RAISE FND_API.G_EXC_ERROR;
4441: END IF;
4442: ELSE --p_status_ctl <> 'N'
4443: IF (p_control_lot_attrib_ind IS NULL)
4444: THEN
4449: IF (p_in_spec_lot_status_id IS NOT NULL)
4450: THEN
4451: GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
4452: 'WHAT', 'in_spec_lot_status_id must be NULL');
4453: RAISE FND_API.G_EXC_ERROR;
4454: END IF;
4455:
4456: IF (p_out_of_spec_lot_status_id IS NOT NULL)
4457: THEN
4456: IF (p_out_of_spec_lot_status_id IS NOT NULL)
4457: THEN
4458: GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
4459: 'WHAT', 'out_of_spec_lot_status_id must be NULL');
4460: RAISE FND_API.G_EXC_ERROR;
4461: END IF;
4462: ELSE
4463: --=========================================================================
4464: -- In this case, control_lot_attrib_ind IS NOT NULL,
4469: IF (p_in_spec_lot_status_id IS NULL)
4470: THEN
4471: GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
4472: 'WHAT', 'in_spec_lot_status_id is MANDATORY');
4473: RAISE FND_API.G_EXC_ERROR;
4474: ELSE
4475: OPEN c_lot_status(p_in_spec_lot_status_id);
4476: FETCH c_lot_status INTO dummy;
4477: IF (c_lot_status%NOTFOUND)
4480: FND_MESSAGE.SET_NAME('GMD','GMD_NOTFOUND');
4481: FND_MESSAGE.SET_TOKEN('WHAT', 'IN_SPEC_LOT_STATUS_ID');
4482: FND_MESSAGE.SET_TOKEN('VALUE', p_in_spec_lot_status_id);
4483: FND_MSG_PUB.ADD;
4484: RAISE FND_API.G_EXC_ERROR;
4485: END IF;
4486: CLOSE c_lot_status;
4487: END IF; -- in_spec_lot_status IS NULL
4488:
4489: IF (p_out_of_spec_lot_status_id IS NULL)
4490: THEN
4491: GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
4492: 'WHAT', 'out_of_spec_lot_status_id is MANDATORY');
4493: RAISE FND_API.G_EXC_ERROR;
4494: ELSE
4495: OPEN c_lot_status(p_out_of_spec_lot_status_id);
4496: FETCH c_lot_status INTO dummy;
4497: IF (c_lot_status%NOTFOUND)
4500: FND_MESSAGE.SET_NAME('GMD','GMD_NOTFOUND');
4501: FND_MESSAGE.SET_TOKEN('WHAT', 'OUT_OF_SPEC_LOT_STATUS_ID');
4502: FND_MESSAGE.SET_TOKEN('VALUE', p_out_of_spec_lot_status_id);
4503: FND_MSG_PUB.ADD;
4504: RAISE FND_API.G_EXC_ERROR;
4505: END IF;
4506: CLOSE c_lot_status;
4507: END IF; -- out_of_spec_lot_status_id IS NULL
4508: END IF; -- control_lot_attrib_ind IS NOT NULL