[Home] [Help]
168: e_error_fetch_item EXCEPTION;
169:
170: BEGIN
171: -- Initialize API return status to success
172: x_return_status := FND_API.G_RET_STS_SUCCESS;
173:
174: IF NOT (p_operation in ('INSERT', 'UPDATE', 'DELETE')) THEN
175: -- Invalid Operation
176: GMD_API_PUB.Log_Message('GMD_INVALID_OPERATION');
173:
174: IF NOT (p_operation in ('INSERT', 'UPDATE', 'DELETE')) THEN
175: -- Invalid Operation
176: GMD_API_PUB.Log_Message('GMD_INVALID_OPERATION');
177: RAISE FND_API.G_EXC_ERROR;
178: END IF;
179:
180: -- Verify that the specification exists.
181: l_spec.spec_id := p_mon_vr.spec_id;
220: );
221: -- No need if called from FORM since it is already
222: -- done in the form
223: -- All messages should be already raised
224: IF l_return_status = FND_API.G_RET_STS_ERROR THEN
225: -- Message is alrady logged by check_for_null procedure
226: RAISE FND_API.G_EXC_ERROR;
227: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
228: -- Message is alrady logged by check_for_null procedure
222: -- done in the form
223: -- All messages should be already raised
224: IF l_return_status = FND_API.G_RET_STS_ERROR THEN
225: -- Message is alrady logged by check_for_null procedure
226: RAISE FND_API.G_EXC_ERROR;
227: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
228: -- Message is alrady logged by check_for_null procedure
229: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
230: END IF;
223: -- All messages should be already raised
224: IF l_return_status = FND_API.G_RET_STS_ERROR THEN
225: -- Message is alrady logged by check_for_null procedure
226: RAISE FND_API.G_EXC_ERROR;
227: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
228: -- Message is alrady logged by check_for_null procedure
229: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
230: END IF;
231:
225: -- Message is alrady logged by check_for_null procedure
226: RAISE FND_API.G_EXC_ERROR;
227: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
228: -- Message is alrady logged by check_for_null procedure
229: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
230: END IF;
231:
232: l_mon_vr := l_mon_vr_tmp;
233: END IF;
239: THEN
240: -- Disaster, Trying to insert duplicate
241: -- Put the message in function mon_vr_exist.
242: -- GMD_API_PUB.Log_Message('GMD_MON_VR_EXIST');
243: RAISE FND_API.G_EXC_ERROR;
244: END IF;
245:
246: -- No need to check the return status because above procedure
247: -- logs appropriate message on the stack and raises an exception.
249: -- The Start Date must be less than the End Date
250: If ( l_mon_vr.end_date IS NOT NULL AND
251: l_mon_vr.start_date > l_mon_vr.end_date) THEN
252: GMD_API_PUB.Log_Message('GMD_SPEC_VR_EFF_DATE_ERROR');
253: RAISE FND_API.G_EXC_ERROR;
254: END IF;
255:
256: -- Spec VR Status Must be less than Spec Status upto Appoved Stages
257: IF (floor(l_spec.spec_status/100) <= 7 AND
257: IF (floor(l_spec.spec_status/100) <= 7 AND
258: floor(l_mon_vr.spec_vr_status/100) <= 7 AND
259: l_mon_vr.spec_vr_status > l_spec.spec_status) THEN
260: GMD_API_PUB.Log_Message('GMD_SPEC_VR_STATUS_HIGHER');
261: RAISE FND_API.G_EXC_ERROR;
262: END IF;
263:
264: -- All systems GO...
265: x_mon_vr := l_mon_vr;
264: -- All systems GO...
265: x_mon_vr := l_mon_vr;
266:
267: EXCEPTION
268: WHEN FND_API.G_EXC_ERROR OR
269: e_spec_fetch_error OR
270: e_smpl_plan_fetch_error OR
271: e_error_fetch_item
272: THEN
269: e_spec_fetch_error OR
270: e_smpl_plan_fetch_error OR
271: e_error_fetch_item
272: THEN
273: x_return_status := FND_API.G_RET_STS_ERROR ;
274: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
275: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
276: WHEN OTHERS THEN
277: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
270: e_smpl_plan_fetch_error OR
271: e_error_fetch_item
272: THEN
273: x_return_status := FND_API.G_RET_STS_ERROR ;
274: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
275: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
276: WHEN OTHERS THEN
277: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
278:
271: e_error_fetch_item
272: THEN
273: x_return_status := FND_API.G_RET_STS_ERROR ;
274: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
275: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
276: WHEN OTHERS THEN
277: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
278:
279:
273: x_return_status := FND_API.G_RET_STS_ERROR ;
274: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
275: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
276: WHEN OTHERS THEN
277: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
278:
279:
280: END validate_mon_vr;
281:
353: OR l_mon_vr.last_update_date IS NULL)
354: THEN
355: GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
356: 'WHAT', 'the dates must not be NULL');
357: RAISE FND_API.G_EXC_ERROR;
358: END IF;
359:
360:
361: -- Bug 3451798
373: -- Bug 3451839
374: -- Invalid Rule Type
375: GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
376: 'WHAT', 'The monitoring spec rule type');
377: RAISE FND_API.G_EXC_ERROR;
378: end if ;
379:
380:
381: -- Loct Organization is valid
386: THEN
387: CLOSE c_orgn;
388: GMD_API_PUB.Log_Message('GMD_ORGANIZATION_ID_NOT_FOUND',
389: 'ORGN_ID', l_mon_vr.locator_organization_id);
390: RAISE FND_API.G_EXC_ERROR;
391: END IF;
392: CLOSE c_orgn;
393: END IF;
394:
393: END IF;
394:
395: --srakrish BUG 5251172: Checking if the responsibility is available to the Locator organization.
396: IF NOT (gmd_api_grp.OrgnAccessible(l_mon_vr.locator_organization_id)) THEN
397: RAISE FND_API.G_EXC_ERROR;
398: END IF;
399:
400: -- Resource is valid (Bug 3451868)
401: IF (l_mon_vr.resources IS NOT NULL) THEN
406: THEN
407: CLOSE c_resources;
408: GMD_API_PUB.Log_Message('GMD_RESOURCE_NOT_FOUND',
409: 'RESOURCE', l_mon_vr.resources);
410: RAISE FND_API.G_EXC_ERROR;
411: END IF;
412: CLOSE c_resources;
413: END IF;
414:
421: THEN
422: CLOSE c_orgn;
423: GMD_API_PUB.Log_Message('GMD_ORGANIZATION_ID_NOT_FOUND',
424: 'ORGN_ID', l_mon_vr.resource_organization_id);
425: RAISE FND_API.G_EXC_ERROR;
426: END IF;
427: CLOSE c_orgn;
428: END IF;
429:
428: END IF;
429:
430: --srakrish BUG 5251172: Checking if the responsibility is available to the Resource organization.
431: IF NOT (gmd_api_grp.OrgnAccessible(l_mon_vr.resource_organization_id)) THEN
432: RAISE FND_API.G_EXC_ERROR;
433: END IF;
434:
435: -- Resource Instance is valid (Bug 3451868)
436: IF (l_mon_vr.resource_instance_id IS NOT NULL) THEN
441: THEN
442: CLOSE c_resource_instance;
443: GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
444: 'WHAT', 'The resource instance');
445: RAISE FND_API.G_EXC_ERROR;
446: END IF;
447: CLOSE c_resource_instance;
448: END IF;
449:
457: THEN
458: CLOSE c_subinventory;
459: GMD_API_PUB.Log_Message('GMD_SUBINVENTORY_NOT_FOUND',
460: 'SUBINVENTORY', l_mon_vr.subinventory);
461: RAISE FND_API.G_EXC_ERROR;
462: END IF;
463: CLOSE c_subinventory;
464:
465: END IF;
487: IF (c_locator%NOTFOUND)
488: THEN
489: CLOSE c_locator;
490: GMD_API_PUB.Log_Message('GMD_LOCT_NOT_FOUND');
491: RAISE FND_API.G_EXC_ERROR;
492: END IF;
493: CLOSE c_locator;
494: END IF; -- location IS NOT NULL
495: ELSE --l_locator_type NOT IN (2,3)
496: IF (l_mon_vr.locator_id IS NOT NULL)
497: THEN
498: GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
499: 'WHAT', 'locator should be NULL');
500: RAISE FND_API.G_EXC_ERROR;
501: END IF;
502: END IF; -- l_locator_type IN (2,3)
503:
504: --=========================================================================
512: THEN
513: CLOSE c_status;
514: GMD_API_PUB.Log_Message('GMD_SPEC_STATUS_NOT_FOUND',
515: 'STATUS', l_mon_vr.spec_vr_status);
516: RAISE FND_API.G_EXC_ERROR;
517: END IF;
518: CLOSE c_status;
519:
520: --=========================================================================
522: --=========================================================================
523: IF (l_mon_vr.start_date IS NULL)
524: THEN
525: GMD_API_PUB.Log_Message('GMD_SPEC_VR_START_DATE_REQD');
526: RAISE FND_API.G_EXC_ERROR;
527: END IF;
528:
529: x_mon_vr := l_mon_vr;
530:
529: x_mon_vr := l_mon_vr;
530:
531:
532: EXCEPTION
533: WHEN FND_API.G_EXC_ERROR THEN
534: x_return_status := FND_API.G_RET_STS_ERROR ;
535: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
536: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
537: WHEN OTHERS THEN
530:
531:
532: EXCEPTION
533: WHEN FND_API.G_EXC_ERROR THEN
534: x_return_status := FND_API.G_RET_STS_ERROR ;
535: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
536: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
537: WHEN OTHERS THEN
538: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
531:
532: EXCEPTION
533: WHEN FND_API.G_EXC_ERROR THEN
534: x_return_status := FND_API.G_RET_STS_ERROR ;
535: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
536: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
537: WHEN OTHERS THEN
538: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
539:
532: EXCEPTION
533: WHEN FND_API.G_EXC_ERROR THEN
534: x_return_status := FND_API.G_RET_STS_ERROR ;
535: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
536: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
537: WHEN OTHERS THEN
538: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
539:
540: END check_for_null_and_fks_in_mvr;
534: x_return_status := FND_API.G_RET_STS_ERROR ;
535: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
536: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
537: WHEN OTHERS THEN
538: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
539:
540: END check_for_null_and_fks_in_mvr;
541:
542:
707: e_error_fetch_item EXCEPTION;
708:
709: BEGIN
710: -- Initialize API return status to success
711: x_return_status := FND_API.G_RET_STS_SUCCESS;
712:
713: IF NOT (p_operation in ('INSERT', 'UPDATE', 'DELETE')) THEN
714: -- Invalid Operation
715: GMD_API_PUB.Log_Message('GMD_INVALID_OPERATION');
712:
713: IF NOT (p_operation in ('INSERT', 'UPDATE', 'DELETE')) THEN
714: -- Invalid Operation
715: GMD_API_PUB.Log_Message('GMD_INVALID_OPERATION');
716: RAISE FND_API.G_EXC_ERROR;
717: END IF;
718:
719: -- Verify that the specification exists.
720: l_spec.spec_id := p_inv_vr.spec_id;
753: --For mini pack L, bug 3439865
754: IF ( nvl(p_inv_vr.auto_sample_ind,'N') not in ('N','Y')) THEN
755: GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
756: 'WHAT', 'INVALID_AUTO_SAMPLE_IND');
757: RAISE FND_API.G_EXC_ERROR;
758: END IF;
759: -- end 3439865
760: -- Check for NULLs and Valid Foreign Keys in the input parameter
761: check_for_null_and_fks_in_ivr
767: );
768: -- No need if called from FORM since it is already
769: -- done in the form
770:
771: IF l_return_status = FND_API.G_RET_STS_ERROR THEN
772: -- Message is alrady logged by check_for_null procedure
773: RAISE FND_API.G_EXC_ERROR;
774: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
775: -- Message is alrady logged by check_for_null procedure
769: -- done in the form
770:
771: IF l_return_status = FND_API.G_RET_STS_ERROR THEN
772: -- Message is alrady logged by check_for_null procedure
773: RAISE FND_API.G_EXC_ERROR;
774: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
775: -- Message is alrady logged by check_for_null procedure
776: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
777: END IF;
770:
771: IF l_return_status = FND_API.G_RET_STS_ERROR THEN
772: -- Message is alrady logged by check_for_null procedure
773: RAISE FND_API.G_EXC_ERROR;
774: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
775: -- Message is alrady logged by check_for_null procedure
776: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
777: END IF;
778:
772: -- Message is alrady logged by check_for_null procedure
773: RAISE FND_API.G_EXC_ERROR;
774: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
775: -- Message is alrady logged by check_for_null procedure
776: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
777: END IF;
778:
779: l_inv_vr := l_inv_vr_tmp;
780: END IF;
787: THEN
788: -- Disaster, Trying to insert duplicate
789: -- bug 2630007, odaboval put the message in function inv_vr_exist.
790: -- GMD_API_PUB.Log_Message('GMD_INV_VR_EXIST');
791: RAISE FND_API.G_EXC_ERROR;
792: END IF;
793:
794:
795: -- Check to make sure that a samplingplan exists
836: uom_rate => l_uom_rate );
837:
838: IF l_uom_rate = -99999 THEN
839: GMD_API_PUB.Log_Message('GMD_UOM_CONVERSION_ERROR');
840: RAISE FND_API.G_EXC_ERROR;
841: END IF;
842: END IF;
843:
844: -- No need to check the return status because above procedure
848: -- bug 2691994 02-DEC-02, odaboval changed p_inv_vr by l_inv_vr
849: If ( l_inv_vr.end_date IS NOT NULL AND
850: l_inv_vr.start_date > l_inv_vr.end_date) THEN
851: GMD_API_PUB.Log_Message('GMD_SPEC_VR_EFF_DATE_ERROR');
852: RAISE FND_API.G_EXC_ERROR;
853: END IF;
854:
855: -- Spec VR Status Must be less than Spec Status upto Appoved Stages
856: IF (floor(l_spec.spec_status/100) <= 7 AND
856: IF (floor(l_spec.spec_status/100) <= 7 AND
857: floor(l_inv_vr.spec_vr_status/100) <= 7 AND
858: l_inv_vr.spec_vr_status > l_spec.spec_status) THEN
859: GMD_API_PUB.Log_Message('GMD_SPEC_VR_STATUS_HIGHER');
860: RAISE FND_API.G_EXC_ERROR;
861: END IF;
862:
863: -- srakrish Bug 5276602: Checking if the Lot optionalfeild is set to yes when lot or parent lot or entered.
864: -- This scenario exists when the api is called from the wrapper.
864: -- This scenario exists when the api is called from the wrapper.
865: IF l_inv_vr.lot_number IS NOT NULL or l_inv_vr.parent_lot_number IS NOT NULL then
866: IF l_inv_vr.lot_optional_on_sample = 'Y' THEN
867: GMD_API_PUB.Log_Message('GMD_SPEC_VR_LOT_CNTRL_INVALID');
868: RAISE FND_API.G_EXC_ERROR;
869: END IF;
870: END IF;
871:
872: -- All systems GO...
872: -- All systems GO...
873: x_inv_vr := l_inv_vr;
874:
875: EXCEPTION
876: WHEN FND_API.G_EXC_ERROR OR
877: e_spec_fetch_error OR
878: e_smpl_plan_fetch_error OR
879: e_error_fetch_item
880: THEN
877: e_spec_fetch_error OR
878: e_smpl_plan_fetch_error OR
879: e_error_fetch_item
880: THEN
881: x_return_status := FND_API.G_RET_STS_ERROR ;
882: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
883: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
884: WHEN OTHERS THEN
885: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
878: e_smpl_plan_fetch_error OR
879: e_error_fetch_item
880: THEN
881: x_return_status := FND_API.G_RET_STS_ERROR ;
882: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
883: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
884: WHEN OTHERS THEN
885: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
886:
879: e_error_fetch_item
880: THEN
881: x_return_status := FND_API.G_RET_STS_ERROR ;
882: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
883: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
884: WHEN OTHERS THEN
885: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
886:
887:
881: x_return_status := FND_API.G_RET_STS_ERROR ;
882: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
883: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
884: WHEN OTHERS THEN
885: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
886:
887:
888: END validate_inv_vr;
889:
983: OR l_inv_vr.last_update_date IS NULL)
984: THEN
985: GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
986: 'WHAT', 'the dates must not be NULL');
987: RAISE FND_API.G_EXC_ERROR;
988: END IF;
989:
990: -- Organization is valid
991: IF (l_inv_vr.organization_id IS NOT NULL) THEN
996: THEN
997: CLOSE c_orgn;
998: GMD_API_PUB.Log_Message('GMD_ORGANIZATION_ID_NOT_FOUND',
999: 'ORGN_ID', l_inv_vr.organization_id);
1000: RAISE FND_API.G_EXC_ERROR;
1001: END IF;
1002: CLOSE c_orgn;
1003: END IF;
1004:
1003: END IF;
1004:
1005: --srakrish BUG 5251172: Checking if the responsibility is available to the organization.
1006: IF NOT (gmd_api_grp.OrgnAccessible(l_inv_vr.organization_id)) THEN
1007: RAISE FND_API.G_EXC_ERROR;
1008: END IF;
1009:
1010: --=========================================================================
1011: -- Get Item Controls
1034: THEN
1035: CLOSE c_item_lot_number;
1036: GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
1037: 'WHAT', 'lot_number');
1038: RAISE FND_API.G_EXC_ERROR;
1039: END IF;
1040: CLOSE c_item_lot_number;
1041: ELSE --Item is not lot controlled
1042: FND_MESSAGE.SET_NAME('GMD','GMD_ITEM_NOT_LOT_CONTROL');
1040: CLOSE c_item_lot_number;
1041: ELSE --Item is not lot controlled
1042: FND_MESSAGE.SET_NAME('GMD','GMD_ITEM_NOT_LOT_CONTROL');
1043: FND_MSG_PUB.ADD;
1044: RAISE FND_API.G_EXC_ERROR;
1045: END IF;
1046: END IF;
1047:
1048: IF (l_inv_vr.parent_lot_number IS NOT NULL)
1054: THEN
1055: CLOSE c_item_parent_lot;
1056: GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
1057: 'WHAT', 'parent_lot_number');
1058: RAISE FND_API.G_EXC_ERROR;
1059: END IF;
1060: CLOSE c_item_parent_lot;
1061: ELSE --Item is not child lot controlled
1062: FND_MESSAGE.SET_NAME('GMD','GMD_ITEM_NOT_CHILD_LOT_CONTROL');
1060: CLOSE c_item_parent_lot;
1061: ELSE --Item is not child lot controlled
1062: FND_MESSAGE.SET_NAME('GMD','GMD_ITEM_NOT_CHILD_LOT_CONTROL');
1063: FND_MSG_PUB.ADD;
1064: RAISE FND_API.G_EXC_ERROR;
1065: END IF;
1066: END IF;
1067:
1068: -- Subinventory is Valid
1073: THEN
1074: CLOSE c_subinventory;
1075: GMD_API_PUB.Log_Message('GMD_SUBINVENTORY_NOT_FOUND',
1076: 'SUBINVENTORY', l_inv_vr.subinventory);
1077: RAISE FND_API.G_EXC_ERROR;
1078: END IF;
1079: CLOSE c_subinventory;
1080: END IF;
1081:
1094: IF (c_locator%NOTFOUND)
1095: THEN
1096: CLOSE c_locator;
1097: GMD_API_PUB.Log_Message('GMD_LOCT_NOT_FOUND');
1098: RAISE FND_API.G_EXC_ERROR;
1099: END IF;
1100: CLOSE c_locator;
1101: END IF; -- location IS NOT NULL
1102: ELSE -- Here l_locator_type not IN (2,3)
1103: IF (l_inv_vr.locator_id IS NOT NULL)
1104: THEN
1105: GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
1106: 'WHAT', 'locator should be NULL');
1107: RAISE FND_API.G_EXC_ERROR;
1108: END IF;
1109: END IF; -- l_locator_type IN (2,3)
1110: --=========================================================================
1111: -- lot_optional_on_sample :
1131: IF NOT check_wms_enabled(l_inv_vr.organization_id) THEN -- If the Org is not a wms enabled then delayed_lpn_entry should be NULL
1132: IF l_inv_vr.delayed_lpn_entry IS NOT NULL THEN
1133: GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
1134: 'WHAT', 'Delayed_LPN_Entry should be NULL for Non-WMS Enabled Organization.');
1135: RAISE FND_API.G_EXC_ERROR;
1136: END IF;
1137: END IF;
1138: END IF;
1139: --RLNAGARA LPN ME 7027149 end
1149: THEN
1150: CLOSE c_status;
1151: GMD_API_PUB.Log_Message('GMD_SPEC_STATUS_NOT_FOUND',
1152: 'STATUS', l_inv_vr.spec_vr_status);
1153: RAISE FND_API.G_EXC_ERROR;
1154: END IF;
1155: CLOSE c_status;
1156:
1157: --=========================================================================
1159: --=========================================================================
1160: IF (l_inv_vr.start_date IS NULL)
1161: THEN
1162: GMD_API_PUB.Log_Message('GMD_SPEC_VR_START_DATE_REQD');
1163: RAISE FND_API.G_EXC_ERROR;
1164: END IF;
1165:
1166: --=========================================================================
1167: -- COA section :
1174: x_inv_vr := l_inv_vr;
1175:
1176:
1177: EXCEPTION
1178: WHEN FND_API.G_EXC_ERROR THEN
1179: x_return_status := FND_API.G_RET_STS_ERROR ;
1180: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1181: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1182: WHEN OTHERS THEN
1175:
1176:
1177: EXCEPTION
1178: WHEN FND_API.G_EXC_ERROR THEN
1179: x_return_status := FND_API.G_RET_STS_ERROR ;
1180: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1181: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1182: WHEN OTHERS THEN
1183: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1176:
1177: EXCEPTION
1178: WHEN FND_API.G_EXC_ERROR THEN
1179: x_return_status := FND_API.G_RET_STS_ERROR ;
1180: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1181: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1182: WHEN OTHERS THEN
1183: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1184:
1177: EXCEPTION
1178: WHEN FND_API.G_EXC_ERROR THEN
1179: x_return_status := FND_API.G_RET_STS_ERROR ;
1180: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1181: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1182: WHEN OTHERS THEN
1183: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1184:
1185: END check_for_null_and_fks_in_ivr;
1179: x_return_status := FND_API.G_RET_STS_ERROR ;
1180: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1181: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1182: WHEN OTHERS THEN
1183: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1184:
1185: END check_for_null_and_fks_in_ivr;
1186:
1187:
1350: e_error_fetch_item EXCEPTION;
1351:
1352: BEGIN
1353: -- Initialize API return status to success
1354: x_return_status := FND_API.G_RET_STS_SUCCESS;
1355:
1356: IF NOT (p_operation in ('INSERT', 'UPDATE', 'DELETE')) THEN
1357: -- Invalid Operation
1358: GMD_API_PUB.Log_Message('GMD_INVALID_OPERATION');
1355:
1356: IF NOT (p_operation in ('INSERT', 'UPDATE', 'DELETE')) THEN
1357: -- Invalid Operation
1358: GMD_API_PUB.Log_Message('GMD_INVALID_OPERATION');
1359: RAISE FND_API.G_EXC_ERROR;
1360: END IF;
1361:
1362: -- Verify that the specification exists.
1363: l_spec.spec_id := p_wip_vr.spec_id;
1394: IF ((p_wip_vr.sampling_plan_id IS NULL) and
1395: (p_wip_vr.auto_sample_ind = 'Y'))
1396: THEN
1397: GMD_API_PUB.Log_Message('GMD_NEED_SAMPLE_PLAN');
1398: RAISE FND_API.G_EXC_ERROR;
1399: END IF;
1400:
1401:
1402: -- bug 2691994 02-DEC-02:
1407: --For mini pack L, bug 3439865
1408: IF (nvl(p_wip_vr.auto_sample_ind,'N') not in ('N','Y')) THEN
1409: GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
1410: 'WHAT', 'INVALID_AUTO_SAMPLE_IND');
1411: RAISE FND_API.G_EXC_ERROR;
1412: END IF;
1413: -- end 3439865
1414: -- Check for NULLs and Valid Foreign Keys in the input parameter
1415: GMD_SPEC_VRS_GRP.check_for_null_and_fks_in_wvr
1421: );
1422: -- No need if called from FORM since it is already
1423: -- done in the form
1424:
1425: IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1426: -- Message is alrady logged by check_for_null procedure
1427: RAISE FND_API.G_EXC_ERROR;
1428: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1429: -- Message is alrady logged by check_for_null procedure
1423: -- done in the form
1424:
1425: IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1426: -- Message is alrady logged by check_for_null procedure
1427: RAISE FND_API.G_EXC_ERROR;
1428: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1429: -- Message is alrady logged by check_for_null procedure
1430: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1431: END IF;
1424:
1425: IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1426: -- Message is alrady logged by check_for_null procedure
1427: RAISE FND_API.G_EXC_ERROR;
1428: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1429: -- Message is alrady logged by check_for_null procedure
1430: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1431: END IF;
1432:
1426: -- Message is alrady logged by check_for_null procedure
1427: RAISE FND_API.G_EXC_ERROR;
1428: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1429: -- Message is alrady logged by check_for_null procedure
1430: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1431: END IF;
1432:
1433: l_wip_vr := l_wip_vr_tmp;
1434: END IF;
1440: THEN
1441: -- Disaster, Trying to insert duplicate
1442: -- bug 2630007, odaboval put the message in function wip_vr_exist.
1443: -- GMD_API_PUB.Log_Message('GMD_WIP_VR_EXIST');
1444: RAISE FND_API.G_EXC_ERROR;
1445: END IF;
1446:
1447: -- Sample Quantity UOM must be convertible to Item's UOM
1448: BEGIN
1475: uom_rate => l_uom_rate );
1476:
1477: IF l_uom_rate = -99999 THEN
1478: GMD_API_PUB.Log_Message('GMD_UOM_CONVERSION_ERROR');
1479: RAISE FND_API.G_EXC_ERROR;
1480: END IF;
1481: END IF;
1482:
1483: -- No need to check the return status because above procedure
1487: -- bug 2691994 02-DEC-02, odaboval changed p_wip_vr by l_wip_vr
1488: If ( l_wip_vr.end_date IS NOT NULL AND
1489: l_wip_vr.start_date > l_wip_vr.end_date) THEN
1490: GMD_API_PUB.Log_Message('GMD_SPEC_VR_EFF_DATE_ERROR');
1491: RAISE FND_API.G_EXC_ERROR;
1492: END IF;
1493:
1494: -- Spec VR Status Must be less than Spec Status upto Appoved Stages
1495: -- bug 2691994 02-DEC-02, odaboval changed p_wip_vr by l_wip_vr
1496: IF (floor(l_spec.spec_status/100) <= 7 AND
1497: floor(l_wip_vr.spec_vr_status/100) <= 7 AND
1498: l_wip_vr.spec_vr_status > l_spec.spec_status) THEN
1499: GMD_API_PUB.Log_Message('GMD_SPEC_VR_STATUS_HIGHER');
1500: RAISE FND_API.G_EXC_ERROR;
1501: END IF;
1502:
1503: -- All systems GO...
1504: x_wip_vr := l_wip_vr;
1503: -- All systems GO...
1504: x_wip_vr := l_wip_vr;
1505:
1506: EXCEPTION
1507: WHEN FND_API.G_EXC_ERROR OR
1508: e_spec_fetch_error OR
1509: e_smpl_plan_fetch_error OR
1510: e_error_fetch_item
1511:
1509: e_smpl_plan_fetch_error OR
1510: e_error_fetch_item
1511:
1512: THEN
1513: x_return_status := FND_API.G_RET_STS_ERROR ;
1514: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1515: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1516: WHEN OTHERS THEN
1517: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1510: e_error_fetch_item
1511:
1512: THEN
1513: x_return_status := FND_API.G_RET_STS_ERROR ;
1514: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1515: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1516: WHEN OTHERS THEN
1517: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1518:
1511:
1512: THEN
1513: x_return_status := FND_API.G_RET_STS_ERROR ;
1514: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1515: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1516: WHEN OTHERS THEN
1517: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1518:
1519:
1513: x_return_status := FND_API.G_RET_STS_ERROR ;
1514: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1515: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1516: WHEN OTHERS THEN
1517: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1518:
1519:
1520: END validate_wip_vr;
1521:
1902: l_wip_vr.routing_no IS NULL AND
1903: l_wip_vr.oprn_id IS NULL AND
1904: l_wip_vr.oprn_no IS NULL) THEN
1905: GMD_API_PUB.Log_Message('GMD_WIP_VR_ALL_NULL');
1906: RAISE FND_API.G_EXC_ERROR;
1907: END IF;
1908:
1909: --=========================================================================
1910: -- WHO section :
1915: OR l_wip_vr.last_update_date IS NULL)
1916: THEN
1917: GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
1918: 'WHAT', 'the dates must not be NULL');
1919: RAISE FND_API.G_EXC_ERROR;
1920: END IF;
1921:
1922: --=========================================================================
1923: -- Organization : must be a PLANT and belong to the USER
1931: THEN
1932: CLOSE c_orgn_plant;
1933: GMD_API_PUB.Log_Message('GMD_ORGANIZATION_ID_NOT_FOUND',
1934: 'ORGN_ID', l_wip_vr.organization_id);
1935: RAISE FND_API.G_EXC_ERROR;
1936: END IF;
1937: CLOSE c_orgn_plant;
1938: END IF;
1939:
1938: END IF;
1939:
1940: --srakrish BUG 5251172: Checking if the responsibility is available to the organization.
1941: IF NOT (gmd_api_grp.OrgnAccessible(l_wip_vr.organization_id)) THEN
1942: RAISE FND_API.G_EXC_ERROR;
1943: END IF;
1944:
1945: --=========================================================================
1946: -- Get Item Controls
1977: IF NOT check_wms_enabled(l_wip_vr.organization_id) THEN -- If the Org is not a wms enabled then delayed_lpn_entry should be NULL
1978: IF l_wip_vr.delayed_lpn_entry IS NOT NULL THEN
1979: GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
1980: 'WHAT', 'Delayed_LPN_Entry should be NULL for Non-WMS Enabled Organization.');
1981: RAISE FND_API.G_EXC_ERROR;
1982: END IF;
1983: END IF;
1984: END IF;
1985: --RLNAGARA LPN ME 7027149 end
1994: THEN
1995: CLOSE c_status;
1996: GMD_API_PUB.Log_Message('GMD_SPEC_STATUS_NOT_FOUND',
1997: 'STATUS', l_wip_vr.spec_vr_status);
1998: RAISE FND_API.G_EXC_ERROR;
1999: END IF;
2000: CLOSE c_status;
2001:
2002: --=========================================================================
2004: --=========================================================================
2005: IF (l_wip_vr.start_date IS NULL)
2006: THEN
2007: GMD_API_PUB.Log_Message('GMD_SPEC_VR_START_DATE_REQD');
2008: RAISE FND_API.G_EXC_ERROR;
2009: END IF;
2010:
2011: --=========================================================================
2012: -- COA section :
2025: IF (l_wip_vr.organization_id IS NULL)
2026: THEN
2027: GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
2028: 'WHAT', 'the organization id must not be NULL');
2029: RAISE FND_API.G_EXC_ERROR;
2030: END IF;
2031:
2032: OPEN c_batch;
2033: FETCH c_batch
2037: IF (c_batch%NOTFOUND)
2038: THEN
2039: CLOSE c_batch;
2040: GMD_API_PUB.Log_Message('GMD_BATCH_NOT_FOUND');
2041: RAISE FND_API.G_EXC_ERROR;
2042: END IF;
2043: CLOSE c_batch;
2044:
2045: --=========================================================================
2052: IF ( NVL(l_wip_vr.recipe_id, l_recipe_id) <> l_recipe_id)
2053: THEN
2054: GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
2055: 'WHAT', 'Passed recipe_id doesn''t match the batch''s recipe_id.');
2056: RAISE FND_API.G_EXC_ERROR;
2057: END IF;
2058: -- 2: recipe_no
2059: IF ( NVL(l_wip_vr.recipe_no, l_recipe_no) <> l_recipe_no)
2060: THEN
2059: IF ( NVL(l_wip_vr.recipe_no, l_recipe_no) <> l_recipe_no)
2060: THEN
2061: GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
2062: 'WHAT', 'Passed recipe_no doesn''t match the batch''s recipe_no.');
2063: RAISE FND_API.G_EXC_ERROR;
2064: END IF;
2065: -- 3: recipe_version
2066: IF ( NVL(l_wip_vr.recipe_version, l_recipe_version) <> l_recipe_version)
2067: THEN
2066: IF ( NVL(l_wip_vr.recipe_version, l_recipe_version) <> l_recipe_version)
2067: THEN
2068: GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
2069: 'WHAT', 'Passed recipe_version doesn''t match the batch''s recipe_version.');
2070: RAISE FND_API.G_EXC_ERROR;
2071: END IF;
2072:
2073: -- 4: formula_id
2074: IF ( NVL(l_wip_vr.formula_id, l_formula_id) <> l_formula_id)
2074: IF ( NVL(l_wip_vr.formula_id, l_formula_id) <> l_formula_id)
2075: THEN
2076: GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
2077: 'WHAT', 'Passed formula_id doesn''t match the batch''s formula_id.');
2078: RAISE FND_API.G_EXC_ERROR;
2079: END IF;
2080: -- 5: formula_no
2081: IF ( NVL(l_wip_vr.formula_no, l_formula_no) <> l_formula_no)
2082: THEN
2081: IF ( NVL(l_wip_vr.formula_no, l_formula_no) <> l_formula_no)
2082: THEN
2083: GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
2084: 'WHAT', 'Passed formula_no doesn''t match the batch''s formula_no.');
2085: RAISE FND_API.G_EXC_ERROR;
2086: END IF;
2087: -- 6: formula_vers
2088: IF ( NVL(l_wip_vr.formula_vers, l_formula_vers) <> l_formula_vers)
2089: THEN
2088: IF ( NVL(l_wip_vr.formula_vers, l_formula_vers) <> l_formula_vers)
2089: THEN
2090: GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
2091: 'WHAT', 'Passed formula_vers doesn''t match the batch''s formula_vers.');
2092: RAISE FND_API.G_EXC_ERROR;
2093: END IF;
2094:
2095: -- 7: routing_id
2096: IF ( NVL(l_wip_vr.routing_id, l_routing_id) <> l_routing_id)
2096: IF ( NVL(l_wip_vr.routing_id, l_routing_id) <> l_routing_id)
2097: THEN
2098: GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
2099: 'WHAT', 'Passed routing_id doesn''t match the batch''s routing_id.');
2100: RAISE FND_API.G_EXC_ERROR;
2101: END IF;
2102: -- 8: routing_no
2103: IF ( NVL(l_wip_vr.routing_no, l_routing_no) <> l_routing_no)
2104: THEN
2103: IF ( NVL(l_wip_vr.routing_no, l_routing_no) <> l_routing_no)
2104: THEN
2105: GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
2106: 'WHAT', 'Passed routing_no doesn''t match the batch''s routing_no.');
2107: RAISE FND_API.G_EXC_ERROR;
2108: END IF;
2109: -- 9: routing_vers
2110: IF ( NVL(l_wip_vr.routing_vers, l_routing_vers) <> l_routing_vers)
2111: THEN
2110: IF ( NVL(l_wip_vr.routing_vers, l_routing_vers) <> l_routing_vers)
2111: THEN
2112: GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
2113: 'WHAT', 'Passed routing_vers doesn''t match the batch''s routing_vers.');
2114: RAISE FND_API.G_EXC_ERROR;
2115: END IF;
2116:
2117: -- At this stage, either l_wip_vr.recipe.... are NULL
2118: -- or they are equal to the local variables. I re-populate the fields (when they are NULL)
2149: IF (c_recipe_id%NOTFOUND)
2150: THEN
2151: CLOSE c_recipe_id;
2152: GMD_API_PUB.Log_Message('GMD_RECIPE_NOT_FOUND');
2153: RAISE FND_API.G_EXC_ERROR;
2154: END IF;
2155: CLOSE c_recipe_id;
2156: ELSE
2157: OPEN c_recipe_id_no_route;
2162: IF (c_recipe_id_no_route%NOTFOUND)
2163: THEN
2164: CLOSE c_recipe_id_no_route;
2165: GMD_API_PUB.Log_Message('GMD_RECIPE_NOT_FOUND');
2166: RAISE FND_API.G_EXC_ERROR;
2167: END IF;
2168: CLOSE c_recipe_id_no_route;
2169: l_routing_id := NULL; -- 8901257
2170: l_routing_no := NULL; -- 8901257
2175: IF ( NVL(l_wip_vr.formula_id, l_formula_id) <> l_formula_id)
2176: THEN
2177: GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
2178: 'WHAT', 'Passed formula_id doesn''t match the batch''s formula_id.');
2179: RAISE FND_API.G_EXC_ERROR;
2180: END IF;
2181: -- 5: formula_no
2182: IF ( NVL(l_wip_vr.formula_no, l_formula_no) <> l_formula_no)
2183: THEN
2182: IF ( NVL(l_wip_vr.formula_no, l_formula_no) <> l_formula_no)
2183: THEN
2184: GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
2185: 'WHAT', 'Passed formula_no doesn''t match the batch''s formula_no.');
2186: RAISE FND_API.G_EXC_ERROR;
2187: END IF;
2188: -- 6: formula_vers
2189: IF ( NVL(l_wip_vr.formula_vers, l_formula_vers) <> l_formula_vers)
2190: THEN
2189: IF ( NVL(l_wip_vr.formula_vers, l_formula_vers) <> l_formula_vers)
2190: THEN
2191: GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
2192: 'WHAT', 'Passed formula_vers doesn''t match the batch''s formula_vers.');
2193: RAISE FND_API.G_EXC_ERROR;
2194: END IF;
2195:
2196: -- 7: routing_id
2197: IF ( NVL(l_wip_vr.routing_id, l_routing_id) <> l_routing_id)
2197: IF ( NVL(l_wip_vr.routing_id, l_routing_id) <> l_routing_id)
2198: THEN
2199: GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
2200: 'WHAT', 'Passed routing_id doesn''t match the batch''s routing_id.');
2201: RAISE FND_API.G_EXC_ERROR;
2202: END IF;
2203: -- 8: routing_no
2204: IF ( NVL(l_wip_vr.routing_no, l_routing_no) <> l_routing_no)
2205: THEN
2204: IF ( NVL(l_wip_vr.routing_no, l_routing_no) <> l_routing_no)
2205: THEN
2206: GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
2207: 'WHAT', 'Passed routing_no doesn''t match the batch''s routing_no.');
2208: RAISE FND_API.G_EXC_ERROR;
2209: END IF;
2210: -- 9: routing_vers
2211: IF ( NVL(l_wip_vr.routing_vers, l_routing_vers) <> l_routing_vers)
2212: THEN
2211: IF ( NVL(l_wip_vr.routing_vers, l_routing_vers) <> l_routing_vers)
2212: THEN
2213: GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
2214: 'WHAT', 'Passed routing_vers doesn''t match the batch''s routing_vers.');
2215: RAISE FND_API.G_EXC_ERROR;
2216: END IF;
2217:
2218: -- At this stage, either l_wip_vr.formula/routing.... are NULL
2219: -- or they are equal to the local variables.
2238: IF (c_recipe_no%NOTFOUND)
2239: THEN
2240: CLOSE c_recipe_no;
2241: GMD_API_PUB.Log_Message('GMD_RECIPE_NOT_FOUND');
2242: RAISE FND_API.G_EXC_ERROR;
2243: END IF;
2244: CLOSE c_recipe_no;
2245:
2246: ELSE
2251: IF (c_recipe_no_no_route%NOTFOUND)
2252: THEN
2253: CLOSE c_recipe_no_no_route;
2254: GMD_API_PUB.Log_Message('GMD_RECIPE_NOT_FOUND');
2255: RAISE FND_API.G_EXC_ERROR;
2256: END IF;
2257: CLOSE c_recipe_no_no_route;
2258:
2259: END IF; -- IF l_wip_vr.routing_id IS NOT NULL THEN
2282: IF (c_formula_id%NOTFOUND)
2283: THEN
2284: CLOSE c_formula_id;
2285: GMD_API_PUB.Log_Message('GMD_FORMULA_NOT_FOUND');
2286: RAISE FND_API.G_EXC_ERROR;
2287: END IF;
2288: CLOSE c_formula_id;
2289:
2290: -- Populated the defaults, ignoring the passed values :
2301: IF (c_formula_no%NOTFOUND)
2302: THEN
2303: CLOSE c_formula_no;
2304: GMD_API_PUB.Log_Message('GMD_FORMULA_NOT_FOUND');
2305: RAISE FND_API.G_EXC_ERROR;
2306: END IF;
2307: CLOSE c_formula_no;
2308:
2309: -- Populated the defaults :
2330: IF (c_routing_id%NOTFOUND)
2331: THEN
2332: CLOSE c_routing_id;
2333: GMD_API_PUB.Log_Message('GMD_ROUTING_NOT_FOUND');
2334: RAISE FND_API.G_EXC_ERROR;
2335: END IF;
2336: CLOSE c_routing_id;
2337:
2338: -- Populated the defaults, ignoring the passed values :
2348: IF (c_routing_no%NOTFOUND)
2349: THEN
2350: CLOSE c_routing_no;
2351: GMD_API_PUB.Log_Message('GMD_ROUTING_NOT_FOUND');
2352: RAISE FND_API.G_EXC_ERROR;
2353: END IF;
2354: CLOSE c_routing_no;
2355:
2356: -- Populated the defaults :
2373: IF (l_wip_vr.formula_id IS NULL)
2374: THEN
2375: GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
2376: 'WHAT', 'Formula id must be NOT NULL');
2377: RAISE FND_API.G_EXC_ERROR;
2378: END IF;
2379:
2380: OPEN c_formulaline_id;
2381: FETCH c_formulaline_id
2383: IF (c_formulaline_id%NOTFOUND)
2384: THEN
2385: CLOSE c_formulaline_id;
2386: GMD_API_PUB.Log_Message('GMD_FORMULA_LINE_NOT_FOUND');
2387: RAISE FND_API.G_EXC_ERROR;
2388: END IF;
2389: CLOSE c_formulaline_id;
2390: END IF;
2391:
2399: IF (l_wip_vr.batch_id IS NULL)
2400: THEN
2401: GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
2402: 'WHAT', 'Batch id must be NOT NULL');
2403: RAISE FND_API.G_EXC_ERROR;
2404: END IF;
2405:
2406: OPEN c_material_detail_id;
2407: FETCH c_material_detail_id
2409: IF (c_material_detail_id%NOTFOUND)
2410: THEN
2411: CLOSE c_material_detail_id;
2412: GMD_API_PUB.Log_Message('GMD_MATERIAL_DTL_NOT_FOUND');
2413: RAISE FND_API.G_EXC_ERROR;
2414: END IF;
2415: CLOSE c_material_detail_id;
2416: END IF;
2417:
2425: IF (l_wip_vr.step_id IS NULL AND l_wip_vr.step_no IS NOT NULL)
2426: THEN
2427: GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
2428: 'WHAT', 'Step id must be populated');
2429: RAISE FND_API.G_EXC_ERROR;
2430: END IF;
2431:
2432: IF (l_wip_vr.batch_id IS NOT NULL AND l_wip_vr.step_id IS NOT NULL)
2433: THEN
2438: IF (c_batchstep%NOTFOUND)
2439: THEN
2440: CLOSE c_batchstep;
2441: GMD_API_PUB.Log_Message('GMD_BATCH_STEP_NOT_FOUND');
2442: RAISE FND_API.G_EXC_ERROR;
2443: END IF;
2444: CLOSE c_batchstep;
2445:
2446: -- Populated the defaults, ignoring the passed values :
2455: IF (c_routingstep%NOTFOUND)
2456: THEN
2457: CLOSE c_routingstep;
2458: GMD_API_PUB.Log_Message('GMD_ROUTING_STEP_NOT_FOUND');
2459: RAISE FND_API.G_EXC_ERROR;
2460: END IF;
2461: CLOSE c_routingstep;
2462:
2463: -- Populated the defaults, ignoring the passed values :
2475: IF (c_oprn%NOTFOUND)
2476: THEN
2477: CLOSE c_oprn;
2478: GMD_API_PUB.Log_Message('GMD_BATCH_STEP_NOT_FOUND');
2479: RAISE FND_API.G_EXC_ERROR;
2480: END IF;
2481: CLOSE c_oprn;
2482:
2483: -- Populated the defaults, ignoring the passed values :
2490: -- All Systems Go...
2491: x_wip_vr := l_wip_vr;
2492:
2493: EXCEPTION
2494: WHEN FND_API.G_EXC_ERROR THEN
2495: x_return_status := FND_API.G_RET_STS_ERROR ;
2496: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2497: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2498: WHEN OTHERS THEN
2491: x_wip_vr := l_wip_vr;
2492:
2493: EXCEPTION
2494: WHEN FND_API.G_EXC_ERROR THEN
2495: x_return_status := FND_API.G_RET_STS_ERROR ;
2496: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2497: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2498: WHEN OTHERS THEN
2499: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2492:
2493: EXCEPTION
2494: WHEN FND_API.G_EXC_ERROR THEN
2495: x_return_status := FND_API.G_RET_STS_ERROR ;
2496: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2497: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2498: WHEN OTHERS THEN
2499: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2500:
2493: EXCEPTION
2494: WHEN FND_API.G_EXC_ERROR THEN
2495: x_return_status := FND_API.G_RET_STS_ERROR ;
2496: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2497: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2498: WHEN OTHERS THEN
2499: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2500:
2501: END check_for_null_and_fks_in_wvr;
2495: x_return_status := FND_API.G_RET_STS_ERROR ;
2496: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2497: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2498: WHEN OTHERS THEN
2499: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2500:
2501: END check_for_null_and_fks_in_wvr;
2502:
2503:
2693: e_error_fetch_item EXCEPTION;
2694:
2695: BEGIN
2696: -- Initialize API return status to success
2697: x_return_status := FND_API.G_RET_STS_SUCCESS;
2698:
2699: IF NOT (p_operation in ('INSERT', 'UPDATE', 'DELETE')) THEN
2700: -- Invalid Operation
2701: GMD_API_PUB.Log_Message('GMD_INVALID_OPERATION');
2698:
2699: IF NOT (p_operation in ('INSERT', 'UPDATE', 'DELETE')) THEN
2700: -- Invalid Operation
2701: GMD_API_PUB.Log_Message('GMD_INVALID_OPERATION');
2702: RAISE FND_API.G_EXC_ERROR;
2703: END IF;
2704:
2705: -- Verify that the specification exists.
2706: l_spec.spec_id := p_cust_vr.spec_id;
2741: );
2742: -- No need if called from FORM since it is already
2743: -- done in the form
2744:
2745: IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2746: -- Message is alrady logged by check_for_null procedure
2747: RAISE FND_API.G_EXC_ERROR;
2748: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2749: -- Message is alrady logged by check_for_null procedure
2743: -- done in the form
2744:
2745: IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2746: -- Message is alrady logged by check_for_null procedure
2747: RAISE FND_API.G_EXC_ERROR;
2748: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2749: -- Message is alrady logged by check_for_null procedure
2750: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2751: END IF;
2744:
2745: IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2746: -- Message is alrady logged by check_for_null procedure
2747: RAISE FND_API.G_EXC_ERROR;
2748: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2749: -- Message is alrady logged by check_for_null procedure
2750: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2751: END IF;
2752: END IF;
2746: -- Message is alrady logged by check_for_null procedure
2747: RAISE FND_API.G_EXC_ERROR;
2748: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2749: -- Message is alrady logged by check_for_null procedure
2750: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2751: END IF;
2752: END IF;
2753:
2754: -- First Verify that the SAME VR does not exists
2757: THEN
2758: -- Disaster, Trying to insert duplicate
2759: -- bug 2630007, odaboval put the message in function cust_vr_exist.
2760: -- GMD_API_PUB.Log_Message('GMD_CUST_VR_EXIST');
2761: RAISE FND_API.G_EXC_ERROR;
2762: END IF;
2763:
2764: -- Sample Quantity UOM must be convertible to Item's UOM
2765: BEGIN
2789: uom_rate => l_uom_rate );
2790:
2791: IF l_uom_rate = -99999 THEN
2792: GMD_API_PUB.Log_Message('GMD_UOM_CONVERSION_ERROR');
2793: RAISE FND_API.G_EXC_ERROR;
2794: END IF;
2795: END IF;
2796:
2797: -- No need to check the return status because above procedure
2800: -- The Start Date must be less than the End Date
2801: If ( p_cust_vr.end_date IS NOT NULL AND
2802: p_cust_vr.start_date > p_cust_vr.end_date) THEN
2803: GMD_API_PUB.Log_Message('GMD_SPEC_VR_EFF_DATE_ERROR');
2804: RAISE FND_API.G_EXC_ERROR;
2805: END IF;
2806:
2807: -- Spec VR Status Must be less than Spec Status upto Appoved Stages
2808: IF (floor(l_spec.spec_status/100) <= 7 AND
2808: IF (floor(l_spec.spec_status/100) <= 7 AND
2809: floor(p_cust_vr.spec_vr_status/100) <= 7 AND
2810: p_cust_vr.spec_vr_status > l_spec.spec_status) THEN
2811: GMD_API_PUB.Log_Message('GMD_SPEC_VR_STATUS_HIGHER');
2812: RAISE FND_API.G_EXC_ERROR;
2813: END IF;
2814:
2815: -- All systems GO...
2816:
2814:
2815: -- All systems GO...
2816:
2817: EXCEPTION
2818: WHEN FND_API.G_EXC_ERROR OR
2819: e_spec_fetch_error OR
2820: e_smpl_plan_fetch_error OR
2821: e_error_fetch_item
2822: THEN
2819: e_spec_fetch_error OR
2820: e_smpl_plan_fetch_error OR
2821: e_error_fetch_item
2822: THEN
2823: x_return_status := FND_API.G_RET_STS_ERROR ;
2824: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2825: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2826: WHEN OTHERS THEN
2827: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2820: e_smpl_plan_fetch_error OR
2821: e_error_fetch_item
2822: THEN
2823: x_return_status := FND_API.G_RET_STS_ERROR ;
2824: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2825: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2826: WHEN OTHERS THEN
2827: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2828:
2821: e_error_fetch_item
2822: THEN
2823: x_return_status := FND_API.G_RET_STS_ERROR ;
2824: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2825: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2826: WHEN OTHERS THEN
2827: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2828:
2829:
2823: x_return_status := FND_API.G_RET_STS_ERROR ;
2824: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2825: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2826: WHEN OTHERS THEN
2827: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2828:
2829:
2830: END validate_cust_vr;
2831:
2960: OR p_cust_vr.last_update_date IS NULL)
2961: THEN
2962: GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
2963: 'WHAT', 'the dates must not be NULL');
2964: RAISE FND_API.G_EXC_ERROR;
2965: END IF;
2966:
2967: --=========================================================================
2968: -- Organization : must belong to the USER
2979: THEN
2980: CLOSE c_orgn;
2981: GMD_API_PUB.Log_Message('GMD_ORGANIZATION_ID_NOT_FOUND',
2982: 'ORGN_ID', p_cust_vr.organization_id);
2983: RAISE FND_API.G_EXC_ERROR;
2984: END IF;
2985: CLOSE c_orgn;*/
2986:
2987: -- replace above with function as above check was not working as designed - bug 5223014 rework
2986:
2987: -- replace above with function as above check was not working as designed - bug 5223014 rework
2988:
2989: IF NOT (gmd_api_grp.OrgnAccessible(p_cust_vr.organization_id)) THEN
2990: RAISE FND_API.G_EXC_ERROR;
2991: END IF;
2992:
2993: END IF;
2994:
3029: THEN
3030: CLOSE c_status;
3031: GMD_API_PUB.Log_Message('GMD_SPEC_STATUS_NOT_FOUND',
3032: 'STATUS', p_cust_vr.spec_vr_status);
3033: RAISE FND_API.G_EXC_ERROR;
3034: END IF;
3035: CLOSE c_status;
3036:
3037:
3040: --=========================================================================
3041: IF (p_cust_vr.start_date IS NULL)
3042: THEN
3043: GMD_API_PUB.Log_Message('GMD_SPEC_VR_START_DATE_REQD');
3044: RAISE FND_API.G_EXC_ERROR;
3045: END IF;
3046:
3047: --=========================================================================
3048: -- COA section :
3057: --=========================================================================
3058: IF (p_cust_vr.cust_id IS NULL)
3059: THEN
3060: GMD_API_PUB.Log_Message('GMD_CUSTOMER_REQD');
3061: RAISE FND_API.G_EXC_ERROR;
3062: ELSE
3063: OPEN c_cust;
3064: FETCH c_cust
3065: INTO dummy;
3066: IF (c_cust%NOTFOUND)
3067: THEN
3068: CLOSE c_cust;
3069: GMD_API_PUB.Log_Message('GMD_CUSTOMER_NOT_FOUND');
3070: RAISE FND_API.G_EXC_ERROR;
3071: END IF;
3072: CLOSE c_cust;
3073: END IF;
3074:
3083: IF (c_org%NOTFOUND)
3084: THEN
3085: CLOSE c_org;
3086: GMD_API_PUB.Log_Message('GMD_ORG_NOT_FOUND');
3087: RAISE FND_API.G_EXC_ERROR;
3088: END IF;
3089: CLOSE c_org;
3090: END IF;
3091:
3097: IF (p_cust_vr.cust_id IS NULL)
3098: THEN
3099: GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
3100: 'WHAT', 'the customer number must not be NULL');
3101: RAISE FND_API.G_EXC_ERROR;
3102: END IF;
3103:
3104: OPEN c_ship_to;
3105: FETCH c_ship_to
3107: IF (c_ship_to%NOTFOUND)
3108: THEN
3109: CLOSE c_ship_to;
3110: GMD_API_PUB.Log_Message('GMD_SHIP_TO_NOT_FOUND');
3111: RAISE FND_API.G_EXC_ERROR;
3112: END IF;
3113: CLOSE c_ship_to;
3114: END IF;
3115:
3124: IF (c_order%NOTFOUND)
3125: THEN
3126: CLOSE c_order;
3127: GMD_API_PUB.Log_Message('GMD_ORDER_NOT_FOUND');
3128: RAISE FND_API.G_EXC_ERROR;
3129: END IF;
3130: CLOSE c_order;
3131: END IF;
3132:
3141: IF (p_cust_vr.order_id IS NULL)
3142: THEN
3143: GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
3144: 'WHAT', 'the order number must not be NULL');
3145: RAISE FND_API.G_EXC_ERROR;
3146: END IF;
3147: IF (p_cust_vr.order_line_id IS NULL
3148: OR p_cust_vr.order_line IS NULL)
3149: THEN
3148: OR p_cust_vr.order_line IS NULL)
3149: THEN
3150: GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
3151: 'WHAT', 'the order line AND id must not be NULL');
3152: RAISE FND_API.G_EXC_ERROR;
3153: END IF;
3154:
3155: OPEN c_order_line;
3156: FETCH c_order_line
3158: IF (c_order_line%NOTFOUND)
3159: THEN
3160: CLOSE c_order_line;
3161: GMD_API_PUB.Log_Message('GMD_ORDER_LINE_NOT_FOUND');
3162: RAISE FND_API.G_EXC_ERROR;
3163: END IF;
3164: CLOSE c_order_line;
3165: END IF;
3166:
3166:
3167: -- All Systems Go...
3168:
3169: EXCEPTION
3170: WHEN FND_API.G_EXC_ERROR THEN
3171: x_return_status := FND_API.G_RET_STS_ERROR ;
3172: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3173: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3174: WHEN OTHERS THEN
3167: -- All Systems Go...
3168:
3169: EXCEPTION
3170: WHEN FND_API.G_EXC_ERROR THEN
3171: x_return_status := FND_API.G_RET_STS_ERROR ;
3172: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3173: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3174: WHEN OTHERS THEN
3175: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3168:
3169: EXCEPTION
3170: WHEN FND_API.G_EXC_ERROR THEN
3171: x_return_status := FND_API.G_RET_STS_ERROR ;
3172: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3173: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3174: WHEN OTHERS THEN
3175: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3176:
3169: EXCEPTION
3170: WHEN FND_API.G_EXC_ERROR THEN
3171: x_return_status := FND_API.G_RET_STS_ERROR ;
3172: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3173: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3174: WHEN OTHERS THEN
3175: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3176:
3177: END check_for_null_and_fks_in_cvr;
3171: x_return_status := FND_API.G_RET_STS_ERROR ;
3172: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3173: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3174: WHEN OTHERS THEN
3175: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3176:
3177: END check_for_null_and_fks_in_cvr;
3178:
3179:
3344: e_error_fetch_item EXCEPTION;
3345:
3346: BEGIN
3347: -- Initialize API return status to success
3348: x_return_status := FND_API.G_RET_STS_SUCCESS;
3349:
3350: IF NOT (p_operation in ('INSERT', 'UPDATE', 'DELETE')) THEN
3351: -- Invalid Operation
3352: GMD_API_PUB.Log_Message('GMD_INVALID_OPERATION');
3349:
3350: IF NOT (p_operation in ('INSERT', 'UPDATE', 'DELETE')) THEN
3351: -- Invalid Operation
3352: GMD_API_PUB.Log_Message('GMD_INVALID_OPERATION');
3353: RAISE FND_API.G_EXC_ERROR;
3354: END IF;
3355:
3356: -- Verify that the specification exists.
3357: l_spec.spec_id := p_supp_vr.spec_id;
3386: --For mini pack L, bug 3439865
3387: IF (nvl(p_supp_vr.auto_sample_ind,'N') not in ('N','Y')) THEN
3388: GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
3389: 'WHAT', 'INVALID_AUTO_SAMPLE_IND');
3390: RAISE FND_API.G_EXC_ERROR;
3391: END IF;
3392: -- end 3439865
3393: -- Check for NULLs and Valid Foreign Keys in the input parameter
3394: GMD_SPEC_VRS_GRP.check_for_null_and_fks_in_svr
3399: );
3400: -- No need if called from FORM since it is already
3401: -- done in the form
3402:
3403: IF l_return_status = FND_API.G_RET_STS_ERROR THEN
3404: -- Message is alrady logged by check_for_null procedure
3405: RAISE FND_API.G_EXC_ERROR;
3406: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3407: -- Message is alrady logged by check_for_null procedure
3401: -- done in the form
3402:
3403: IF l_return_status = FND_API.G_RET_STS_ERROR THEN
3404: -- Message is alrady logged by check_for_null procedure
3405: RAISE FND_API.G_EXC_ERROR;
3406: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3407: -- Message is alrady logged by check_for_null procedure
3408: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3409: END IF;
3402:
3403: IF l_return_status = FND_API.G_RET_STS_ERROR THEN
3404: -- Message is alrady logged by check_for_null procedure
3405: RAISE FND_API.G_EXC_ERROR;
3406: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3407: -- Message is alrady logged by check_for_null procedure
3408: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3409: END IF;
3410: END IF;
3404: -- Message is alrady logged by check_for_null procedure
3405: RAISE FND_API.G_EXC_ERROR;
3406: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3407: -- Message is alrady logged by check_for_null procedure
3408: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3409: END IF;
3410: END IF;
3411:
3412: -- First Verify that the SAME VR does not exists
3415: THEN
3416: -- Disaster, Trying to insert duplicate
3417: -- bug 2630007, odaboval put the message in function supp_vr_exist.
3418: -- GMD_API_PUB.Log_Message('GMD_SUPP_VR_EXIST');
3419: RAISE FND_API.G_EXC_ERROR;
3420: END IF;
3421:
3422: -- Check to make sure that a samplingplan exists
3423: -- if auto sample flag on
3456: uom_rate => l_uom_rate );
3457:
3458: IF l_uom_rate = -99999 THEN
3459: GMD_API_PUB.Log_Message('GMD_UOM_CONVERSION_ERROR');
3460: RAISE FND_API.G_EXC_ERROR;
3461: END IF;
3462: END IF;
3463:
3464: -- No need to check the return status because above procedure
3467: -- The Start Date must be less than the End Date
3468: If ( p_supp_vr.end_date IS NOT NULL AND
3469: p_supp_vr.start_date > p_supp_vr.end_date) THEN
3470: GMD_API_PUB.Log_Message('GMD_SPEC_VR_EFF_DATE_ERROR');
3471: RAISE FND_API.G_EXC_ERROR;
3472: END IF;
3473:
3474: -- Spec VR Status Must be less than Spec Status upto Appoved Stages
3475: IF (floor(l_spec.spec_status/100) <= 7 AND
3475: IF (floor(l_spec.spec_status/100) <= 7 AND
3476: floor(p_supp_vr.spec_vr_status/100) <= 7 AND
3477: p_supp_vr.spec_vr_status > l_spec.spec_status) THEN
3478: GMD_API_PUB.Log_Message('GMD_SPEC_VR_STATUS_HIGHER');
3479: RAISE FND_API.G_EXC_ERROR;
3480: END IF;
3481:
3482: -- All systems GO...
3483:
3481:
3482: -- All systems GO...
3483:
3484: EXCEPTION
3485: WHEN FND_API.G_EXC_ERROR OR
3486: e_spec_fetch_error OR
3487: e_smpl_plan_fetch_error OR
3488: e_error_fetch_item
3489: THEN
3486: e_spec_fetch_error OR
3487: e_smpl_plan_fetch_error OR
3488: e_error_fetch_item
3489: THEN
3490: x_return_status := FND_API.G_RET_STS_ERROR ;
3491: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3492: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3493: WHEN OTHERS THEN
3494: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3487: e_smpl_plan_fetch_error OR
3488: e_error_fetch_item
3489: THEN
3490: x_return_status := FND_API.G_RET_STS_ERROR ;
3491: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3492: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3493: WHEN OTHERS THEN
3494: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3495:
3488: e_error_fetch_item
3489: THEN
3490: x_return_status := FND_API.G_RET_STS_ERROR ;
3491: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3492: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3493: WHEN OTHERS THEN
3494: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3495:
3496:
3490: x_return_status := FND_API.G_RET_STS_ERROR ;
3491: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3492: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3493: WHEN OTHERS THEN
3494: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3495:
3496:
3497: END validate_supp_vr;
3498:
3607: OR p_supp_vr.last_update_date IS NULL)
3608: THEN
3609: GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
3610: 'WHAT', 'the dates must not be NULL');
3611: RAISE FND_API.G_EXC_ERROR;
3612: END IF;
3613:
3614:
3615:
3621: IF c_orgn%NOTFOUND THEN
3622: CLOSE c_orgn;
3623: GMD_API_PUB.Log_Message('GMD_ORGANIZATION_ID_NOT_FOUND',
3624: 'ORGN_ID', p_supp_vr.organization_id);
3625: RAISE FND_API.G_EXC_ERROR;
3626: END IF;
3627: CLOSE c_orgn;
3628: END IF;
3629:
3628: END IF;
3629:
3630: --srakrish BUG 5251172: Checking if the responsibility is available to the organization.
3631: IF NOT (gmd_api_grp.OrgnAccessible(p_supp_vr.organization_id)) THEN
3632: RAISE FND_API.G_EXC_ERROR;
3633: END IF;
3634:
3635:
3636:
3648: FND_MESSAGE.SET_NAME('GMD','GMD_NOTFOUND');
3649: FND_MESSAGE.SET_TOKEN('WHAT', 'INVENTORY_ITEM_ID');
3650: FND_MESSAGE.SET_TOKEN('VALUE', p_spec.inventory_item_id);
3651: FND_MSG_PUB.ADD;
3652: RAISE FND_API.G_EXC_ERROR;
3653: END IF;
3654: CLOSE c_item_controls;
3655:
3656: --=========================================================================
3676: IF NOT check_wms_enabled(p_supp_vr.organization_id) THEN -- If the Org is not a wms enabled then delayed_lpn_entry should be NULL
3677: IF p_supp_vr.delayed_lpn_entry IS NOT NULL THEN
3678: GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
3679: 'WHAT', 'Delayed_LPN_Entry should be NULL for Non-WMS Enabled Organization.');
3680: RAISE FND_API.G_EXC_ERROR;
3681: END IF;
3682: END IF;
3683: END IF;
3684: --RLNAGARA LPN ME 7027149 end
3693: THEN
3694: CLOSE c_status;
3695: GMD_API_PUB.Log_Message('GMD_SPEC_STATUS_NOT_FOUND',
3696: 'STATUS', p_supp_vr.spec_vr_status);
3697: RAISE FND_API.G_EXC_ERROR;
3698: END IF;
3699: CLOSE c_status;
3700:
3701: --=========================================================================
3703: --=========================================================================
3704: IF (p_supp_vr.start_date IS NULL)
3705: THEN
3706: GMD_API_PUB.Log_Message('GMD_SPEC_VR_START_DATE_REQD');
3707: RAISE FND_API.G_EXC_ERROR;
3708: END IF;
3709:
3710: --=========================================================================
3711: -- COA section :
3720: --=========================================================================
3721: IF (p_supp_vr.supplier_id IS NULL)
3722: THEN
3723: GMD_API_PUB.Log_Message('GMD_SUPPLIER_REQD');
3724: RAISE FND_API.G_EXC_ERROR;
3725: ELSE
3726: OPEN c_supplier;
3727: FETCH c_supplier INTO dummy;
3728: IF (c_supplier%NOTFOUND)
3728: IF (c_supplier%NOTFOUND)
3729: THEN
3730: CLOSE c_supplier;
3731: GMD_API_PUB.Log_Message('GMD_SUPPLIER_NOT_FOUND');
3732: RAISE FND_API.G_EXC_ERROR;
3733: END IF;
3734: CLOSE c_supplier;
3735: END IF;
3736:
3748: FND_MESSAGE.SET_NAME('GMD','GMD_NOTFOUND');
3749: FND_MESSAGE.SET_TOKEN('WHAT', 'SUPPLIER_SITE_ID');
3750: FND_MESSAGE.SET_TOKEN('VALUE', p_supp_vr.supplier_site_id);
3751: FND_MSG_PUB.ADD;
3752: RAISE FND_API.G_EXC_ERROR;
3753: END IF;
3754: CLOSE c_supplier_site;
3755: END IF;
3756:
3764: IF (p_supp_vr.supplier_site_id IS NULL)
3765: THEN
3766: GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
3767: 'WHAT', 'supplier_site_id must not be NULL');
3768: RAISE FND_API.G_EXC_ERROR;
3769: END IF;
3770:
3771: OPEN c_po;
3772: FETCH c_po INTO dummy;
3773: IF (c_po%NOTFOUND)
3774: THEN
3775: CLOSE c_po;
3776: GMD_API_PUB.Log_Message('GMD_PO_NOT_FOUND');
3777: RAISE FND_API.G_EXC_ERROR;
3778: END IF;
3779: CLOSE c_po;
3780: END IF;
3781:
3789: IF (p_supp_vr.po_header_id IS NULL)
3790: THEN
3791: GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
3792: 'WHAT', 'po_header_id must not be NULL');
3793: RAISE FND_API.G_EXC_ERROR;
3794: END IF;
3795:
3796: IF (p_supp_vr.supplier_site_id IS NULL)
3797: THEN
3796: IF (p_supp_vr.supplier_site_id IS NULL)
3797: THEN
3798: GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
3799: 'WHAT', 'supplier site must not be NULL');
3800: RAISE FND_API.G_EXC_ERROR;
3801: END IF;
3802:
3803: OPEN c_po_line;
3804: FETCH c_po_line INTO dummy;
3805: IF (c_po_line%NOTFOUND)
3806: THEN
3807: CLOSE c_po_line;
3808: GMD_API_PUB.Log_Message('GMD_PO_LINE_NOT_FOUND');
3809: RAISE FND_API.G_EXC_ERROR;
3810: END IF;
3811: CLOSE c_po_line;
3812: END IF;
3813:
3813:
3814: -- All Systems Go...
3815:
3816: EXCEPTION
3817: WHEN FND_API.G_EXC_ERROR THEN
3818: x_return_status := FND_API.G_RET_STS_ERROR ;
3819: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3820: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3821: WHEN OTHERS THEN
3814: -- All Systems Go...
3815:
3816: EXCEPTION
3817: WHEN FND_API.G_EXC_ERROR THEN
3818: x_return_status := FND_API.G_RET_STS_ERROR ;
3819: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3820: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3821: WHEN OTHERS THEN
3822: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3815:
3816: EXCEPTION
3817: WHEN FND_API.G_EXC_ERROR THEN
3818: x_return_status := FND_API.G_RET_STS_ERROR ;
3819: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3820: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3821: WHEN OTHERS THEN
3822: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3823:
3816: EXCEPTION
3817: WHEN FND_API.G_EXC_ERROR THEN
3818: x_return_status := FND_API.G_RET_STS_ERROR ;
3819: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3820: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3821: WHEN OTHERS THEN
3822: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3823:
3824: END check_for_null_and_fks_in_svr;
3818: x_return_status := FND_API.G_RET_STS_ERROR ;
3819: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3820: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3821: WHEN OTHERS THEN
3822: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3823:
3824: END check_for_null_and_fks_in_svr;
3825:
3826:
3954: l_inventory_spec_vrs_out GMD_INVENTORY_SPEC_VRS%ROWTYPE;
3955:
3956: BEGIN
3957: l_progress := '010';
3958: x_return_status := FND_API.G_RET_STS_SUCCESS ;
3959:
3960: -- validate for primary key
3961: -- ========================
3962: IF p_spec_id IS NULL THEN
3960: -- validate for primary key
3961: -- ========================
3962: IF p_spec_id IS NULL THEN
3963: GMD_API_PUB.Log_Message('GMD_SPEC_ID_REQUIRED');
3964: RAISE FND_API.G_EXC_ERROR;
3965: ELSE
3966: l_inventory_spec_vrs.spec_id := p_spec_id;
3967: END IF;
3968:
3967: END IF;
3968:
3969: IF p_spec_vr_id IS NULL THEN
3970: GMD_API_PUB.Log_Message('GMD_SPEC_VR_ID_REQUIRED');
3971: RAISE FND_API.G_EXC_ERROR;
3972: ELSE
3973: l_inventory_spec_vrs.spec_vr_id := p_spec_vr_id;
3974: END IF;
3975:
3980: GMD_API_PUB.Log_Message('GMD_FAILED_TO_FETCH_ROW',
3981: 'l_table_name', 'GMD_INVENTORY_SPEC_VRS',
3982: 'l_column_name', 'SPEC_VR_ID',
3983: 'l_key_value', l_inventory_spec_vrs.spec_vr_id);
3984: RAISE FND_API.G_EXC_ERROR;
3985: END IF;
3986:
3987: l_inventory_spec_vrs := l_inventory_spec_vrs_out ;
3988:
3993: GMD_API_PUB.Log_Message('GMD_RECORD_DELETE_MARKED',
3994: 'l_table_name', 'GMD_INVENTORY_SPEC_VRS',
3995: 'l_column_name', 'SPEC_VR_ID',
3996: 'l_key_value', l_inventory_spec_vrs.spec_vr_id);
3997: RAISE FND_API.G_EXC_ERROR;
3998: END IF;
3999:
4000:
4001: EXCEPTION
3998: END IF;
3999:
4000:
4001: EXCEPTION
4002: WHEN FND_API.G_EXC_ERROR THEN
4003: x_return_status := FND_API.G_RET_STS_ERROR ;
4004: x_message_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
4005:
4006: WHEN OTHERS THEN
3999:
4000:
4001: EXCEPTION
4002: WHEN FND_API.G_EXC_ERROR THEN
4003: x_return_status := FND_API.G_RET_STS_ERROR ;
4004: x_message_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
4005:
4006: WHEN OTHERS THEN
4007: FND_MESSAGE.Set_Name('GMD','GMD_API_ERROR');
4000:
4001: EXCEPTION
4002: WHEN FND_API.G_EXC_ERROR THEN
4003: x_return_status := FND_API.G_RET_STS_ERROR ;
4004: x_message_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
4005:
4006: WHEN OTHERS THEN
4007: FND_MESSAGE.Set_Name('GMD','GMD_API_ERROR');
4008: FND_MESSAGE.Set_Token('PACKAGE','GMD_SPEC_GRP.VALIDATE_BEFORE_DELETE');
4008: FND_MESSAGE.Set_Token('PACKAGE','GMD_SPEC_GRP.VALIDATE_BEFORE_DELETE');
4009: FND_MESSAGE.Set_Token('ERROR', substr(sqlerrm,1,100));
4010: FND_MESSAGE.Set_Token('POSITION',l_progress );
4011: FND_MSG_PUB.ADD;
4012: x_message_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
4013: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4014:
4015: END VALIDATE_BEFORE_DELETE_INV_VRS ;
4016:
4009: FND_MESSAGE.Set_Token('ERROR', substr(sqlerrm,1,100));
4010: FND_MESSAGE.Set_Token('POSITION',l_progress );
4011: FND_MSG_PUB.ADD;
4012: x_message_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
4013: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4014:
4015: END VALIDATE_BEFORE_DELETE_INV_VRS ;
4016:
4017: /*===========================================================================
4038: l_wip_spec_vrs_out GMD_WIP_SPEC_VRS%ROWTYPE;
4039:
4040: BEGIN
4041: l_progress := '010';
4042: x_return_status := FND_API.G_RET_STS_SUCCESS ;
4043:
4044: -- validate for primary key
4045: -- ========================
4046: IF p_spec_id IS NULL THEN
4044: -- validate for primary key
4045: -- ========================
4046: IF p_spec_id IS NULL THEN
4047: GMD_API_PUB.Log_Message('GMD_SPEC_ID_REQUIRED');
4048: RAISE FND_API.G_EXC_ERROR;
4049: ELSE
4050: l_wip_spec_vrs.spec_id := p_spec_id;
4051: END IF;
4052:
4051: END IF;
4052:
4053: IF p_spec_vr_id IS NULL THEN
4054: GMD_API_PUB.Log_Message('GMD_SPEC_VR_ID_REQUIRED');
4055: RAISE FND_API.G_EXC_ERROR;
4056: ELSE
4057: l_wip_spec_vrs.spec_vr_id := p_spec_vr_id;
4058: END IF;
4059:
4064: GMD_API_PUB.Log_Message('GMD_FAILED_TO_FETCH_ROW',
4065: 'l_table_name', 'GMD_WIP_SPEC_VRS',
4066: 'l_column_name', 'SPEC_VR_ID',
4067: 'l_key_value', l_wip_spec_vrs.spec_vr_id);
4068: RAISE FND_API.G_EXC_ERROR;
4069: END IF;
4070:
4071: l_wip_spec_vrs := l_wip_spec_vrs_out ;
4072:
4077: GMD_API_PUB.Log_Message('GMD_RECORD_DELETE_MARKED',
4078: 'l_table_name', 'GMD_WIP_SPEC_VRS',
4079: 'l_column_name', 'SPEC_VR_ID',
4080: 'l_key_value', l_wip_spec_vrs.spec_vr_id);
4081: RAISE FND_API.G_EXC_ERROR;
4082: END IF;
4083:
4084:
4085: EXCEPTION
4082: END IF;
4083:
4084:
4085: EXCEPTION
4086: WHEN FND_API.G_EXC_ERROR THEN
4087: x_return_status := FND_API.G_RET_STS_ERROR ;
4088: x_message_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
4089:
4090: WHEN OTHERS THEN
4083:
4084:
4085: EXCEPTION
4086: WHEN FND_API.G_EXC_ERROR THEN
4087: x_return_status := FND_API.G_RET_STS_ERROR ;
4088: x_message_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
4089:
4090: WHEN OTHERS THEN
4091: FND_MESSAGE.Set_Name('GMD','GMD_API_ERROR');
4084:
4085: EXCEPTION
4086: WHEN FND_API.G_EXC_ERROR THEN
4087: x_return_status := FND_API.G_RET_STS_ERROR ;
4088: x_message_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
4089:
4090: WHEN OTHERS THEN
4091: FND_MESSAGE.Set_Name('GMD','GMD_API_ERROR');
4092: FND_MESSAGE.Set_Token('PACKAGE','GMD_SPEC_GRP.VALIDATE_BEFORE_DELETE');
4092: FND_MESSAGE.Set_Token('PACKAGE','GMD_SPEC_GRP.VALIDATE_BEFORE_DELETE');
4093: FND_MESSAGE.Set_Token('ERROR', substr(sqlerrm,1,100));
4094: FND_MESSAGE.Set_Token('POSITION',l_progress );
4095: FND_MSG_PUB.ADD;
4096: x_message_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
4097: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4098:
4099: END VALIDATE_BEFORE_DELETE_WIP_VRS ;
4100:
4093: FND_MESSAGE.Set_Token('ERROR', substr(sqlerrm,1,100));
4094: FND_MESSAGE.Set_Token('POSITION',l_progress );
4095: FND_MSG_PUB.ADD;
4096: x_message_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
4097: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4098:
4099: END VALIDATE_BEFORE_DELETE_WIP_VRS ;
4100:
4101: /*===========================================================================
4121: l_customer_spec_vrs_out GMD_CUSTOMER_SPEC_VRS%ROWTYPE;
4122:
4123: BEGIN
4124: l_progress := '010';
4125: x_return_status := FND_API.G_RET_STS_SUCCESS ;
4126:
4127: -- validate for primary key
4128: -- ========================
4129: IF p_spec_id IS NULL THEN
4127: -- validate for primary key
4128: -- ========================
4129: IF p_spec_id IS NULL THEN
4130: GMD_API_PUB.Log_Message('GMD_SPEC_ID_REQUIRED');
4131: RAISE FND_API.G_EXC_ERROR;
4132: ELSE
4133: l_customer_spec_vrs.spec_id := p_spec_id;
4134: END IF;
4135:
4134: END IF;
4135:
4136: IF p_spec_vr_id IS NULL THEN
4137: GMD_API_PUB.Log_Message('GMD_SPEC_VR_ID_REQUIRED');
4138: RAISE FND_API.G_EXC_ERROR;
4139: ELSE
4140: l_customer_spec_vrs.spec_vr_id := p_spec_vr_id;
4141: END IF;
4142:
4147: GMD_API_PUB.Log_Message('GMD_FAILED_TO_FETCH_ROW',
4148: 'l_table_name', 'GMD_CUSTOMER_SPEC_VRS',
4149: 'l_column_name', 'SPEC_VR_ID',
4150: 'l_key_value', l_customer_spec_vrs.spec_vr_id);
4151: RAISE FND_API.G_EXC_ERROR;
4152: END IF;
4153:
4154: l_customer_spec_vrs := l_customer_spec_vrs_out ;
4155:
4160: GMD_API_PUB.Log_Message('GMD_RECORD_DELETE_MARKED',
4161: 'l_table_name', 'GMD_CUSTOMER_SPEC_VRS',
4162: 'l_column_name', 'SPEC_VR_ID',
4163: 'l_key_value', l_customer_spec_vrs.spec_vr_id);
4164: RAISE FND_API.G_EXC_ERROR;
4165: END IF;
4166:
4167:
4168: EXCEPTION
4165: END IF;
4166:
4167:
4168: EXCEPTION
4169: WHEN FND_API.G_EXC_ERROR THEN
4170: x_return_status := FND_API.G_RET_STS_ERROR ;
4171: x_message_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
4172:
4173: WHEN OTHERS THEN
4166:
4167:
4168: EXCEPTION
4169: WHEN FND_API.G_EXC_ERROR THEN
4170: x_return_status := FND_API.G_RET_STS_ERROR ;
4171: x_message_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
4172:
4173: WHEN OTHERS THEN
4174: FND_MESSAGE.Set_Name('GMD','GMD_API_ERROR');
4167:
4168: EXCEPTION
4169: WHEN FND_API.G_EXC_ERROR THEN
4170: x_return_status := FND_API.G_RET_STS_ERROR ;
4171: x_message_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
4172:
4173: WHEN OTHERS THEN
4174: FND_MESSAGE.Set_Name('GMD','GMD_API_ERROR');
4175: FND_MESSAGE.Set_Token('PACKAGE','GMD_SPEC_GRP.VALIDATE_BEFORE_DELETE');
4175: FND_MESSAGE.Set_Token('PACKAGE','GMD_SPEC_GRP.VALIDATE_BEFORE_DELETE');
4176: FND_MESSAGE.Set_Token('ERROR', substr(sqlerrm,1,100));
4177: FND_MESSAGE.Set_Token('POSITION',l_progress );
4178: FND_MSG_PUB.ADD;
4179: x_message_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
4180: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4181:
4182: END VALIDATE_BEFORE_DELETE_CST_VRS ;
4183:
4176: FND_MESSAGE.Set_Token('ERROR', substr(sqlerrm,1,100));
4177: FND_MESSAGE.Set_Token('POSITION',l_progress );
4178: FND_MSG_PUB.ADD;
4179: x_message_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
4180: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4181:
4182: END VALIDATE_BEFORE_DELETE_CST_VRS ;
4183:
4184: /*===========================================================================
4204: l_supplier_spec_vrs_out GMD_SUPPLIER_SPEC_VRS%ROWTYPE;
4205:
4206: BEGIN
4207: l_progress := '010';
4208: x_return_status := FND_API.G_RET_STS_SUCCESS ;
4209:
4210: -- validate for primary key
4211: -- ========================
4212: IF p_spec_id IS NULL THEN
4210: -- validate for primary key
4211: -- ========================
4212: IF p_spec_id IS NULL THEN
4213: GMD_API_PUB.Log_Message('GMD_SPEC_ID_REQUIRED');
4214: RAISE FND_API.G_EXC_ERROR;
4215: ELSE
4216: l_supplier_spec_vrs.spec_id := p_spec_id;
4217: END IF;
4218:
4217: END IF;
4218:
4219: IF p_spec_vr_id IS NULL THEN
4220: GMD_API_PUB.Log_Message('GMD_SPEC_VR_ID_REQUIRED');
4221: RAISE FND_API.G_EXC_ERROR;
4222: ELSE
4223: l_supplier_spec_vrs.spec_vr_id := p_spec_vr_id;
4224: END IF;
4225:
4230: GMD_API_PUB.Log_Message('GMD_FAILED_TO_FETCH_ROW',
4231: 'l_table_name', 'GMD_SUPPLIER_SPEC_VRS',
4232: 'l_column_name', 'SPEC_VR_ID',
4233: 'l_key_value', l_supplier_spec_vrs.spec_vr_id);
4234: RAISE FND_API.G_EXC_ERROR;
4235: END IF;
4236:
4237: l_supplier_spec_vrs := l_supplier_spec_vrs_out ;
4238:
4243: GMD_API_PUB.Log_Message('GMD_RECORD_DELETE_MARKED',
4244: 'l_table_name', 'GMD_SUPPLIER_SPEC_VRS',
4245: 'l_column_name', 'SPEC_VR_ID',
4246: 'l_key_value', l_supplier_spec_vrs.spec_vr_id);
4247: RAISE FND_API.G_EXC_ERROR;
4248: END IF;
4249:
4250:
4251: EXCEPTION
4248: END IF;
4249:
4250:
4251: EXCEPTION
4252: WHEN FND_API.G_EXC_ERROR THEN
4253: x_return_status := FND_API.G_RET_STS_ERROR ;
4254: x_message_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
4255:
4256: WHEN OTHERS THEN
4249:
4250:
4251: EXCEPTION
4252: WHEN FND_API.G_EXC_ERROR THEN
4253: x_return_status := FND_API.G_RET_STS_ERROR ;
4254: x_message_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
4255:
4256: WHEN OTHERS THEN
4257: FND_MESSAGE.Set_Name('GMD','GMD_API_ERROR');
4250:
4251: EXCEPTION
4252: WHEN FND_API.G_EXC_ERROR THEN
4253: x_return_status := FND_API.G_RET_STS_ERROR ;
4254: x_message_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
4255:
4256: WHEN OTHERS THEN
4257: FND_MESSAGE.Set_Name('GMD','GMD_API_ERROR');
4258: FND_MESSAGE.Set_Token('PACKAGE','GMD_SPEC_GRP.VALIDATE_BEFORE_DELETE');
4258: FND_MESSAGE.Set_Token('PACKAGE','GMD_SPEC_GRP.VALIDATE_BEFORE_DELETE');
4259: FND_MESSAGE.Set_Token('ERROR', substr(sqlerrm,1,100));
4260: FND_MESSAGE.Set_Token('POSITION',l_progress );
4261: FND_MSG_PUB.ADD;
4262: x_message_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
4263: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4264:
4265: END VALIDATE_BEFORE_DELETE_SUP_VRS ;
4266:
4259: FND_MESSAGE.Set_Token('ERROR', substr(sqlerrm,1,100));
4260: FND_MESSAGE.Set_Token('POSITION',l_progress );
4261: FND_MSG_PUB.ADD;
4262: x_message_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
4263: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4264:
4265: END VALIDATE_BEFORE_DELETE_SUP_VRS ;
4266:
4267: /*===========================================================================
4289: THEN
4290: FND_MESSAGE.SET_NAME('GMD','GMD_WRONG_VALUE');
4291: FND_MESSAGE.SET_TOKEN('WHAT', 'USER_ID');
4292: FND_MSG_PUB.ADD;
4293: RAISE FND_API.G_EXC_ERROR;
4294: ELSE
4295: OPEN c_who( p_user_id);
4296: FETCH c_who
4297: INTO dummy;
4302: FND_MESSAGE.SET_NAME('GMD','GMD_NOTFOUND');
4303: FND_MESSAGE.SET_TOKEN('WHAT', 'USER_ID');
4304: FND_MESSAGE.SET_TOKEN('VALUE', p_user_id);
4305: FND_MSG_PUB.ADD;
4306: RAISE FND_API.G_EXC_ERROR;
4307: END IF;
4308: CLOSE c_who;
4309: END IF;
4310:
4342: AND (p_coa_at_ship_ind <> 'Y')
4343: THEN
4344: GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
4345: 'WHAT', 'coa_at_ship_ind value must be either NULL or Y');
4346: RAISE FND_API.G_EXC_ERROR;
4347: END IF;
4348: IF (p_coa_at_invoice_ind IS NOT NULL)
4349: AND (p_coa_at_invoice_ind <> 'Y')
4350: THEN
4349: AND (p_coa_at_invoice_ind <> 'Y')
4350: THEN
4351: GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
4352: 'WHAT', 'coa_at_invoice_ind value must be either NULL or Y');
4353: RAISE FND_API.G_EXC_ERROR;
4354: END IF;
4355: IF (p_coa_req_from_supl_ind IS NOT NULL)
4356: AND (p_coa_req_from_supl_ind <> 'Y')
4357: THEN
4356: AND (p_coa_req_from_supl_ind <> 'Y')
4357: THEN
4358: GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
4359: 'WHAT', 'coa_req_from_supl_ind value must be either NULL or Y');
4360: RAISE FND_API.G_EXC_ERROR;
4361: END IF;
4362: IF (p_coa_type IS NOT NULL)
4363: THEN
4364: OPEN c_coa_type;
4370: FND_MESSAGE.Set_Name('GMD','GMD_NOTFOUND');
4371: FND_MESSAGE.Set_Token('WHAT', 'COA_TYPE');
4372: FND_MESSAGE.Set_Token('VALUE', p_coa_type);
4373: FND_MSG_PUB.Add;
4374: RAISE FND_API.G_EXC_ERROR;
4375: END IF;
4376: CLOSE c_coa_type;
4377: END IF;
4378:
4388: IF (p_coa_at_ship_ind IS NOT NULL)
4389: THEN
4390: GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
4391: 'WHAT', 'coa_at_ship_ind must be NULL');
4392: RAISE FND_API.G_EXC_ERROR;
4393: END IF;
4394:
4395: IF (p_coa_at_invoice_ind IS NOT NULL)
4396: THEN
4395: IF (p_coa_at_invoice_ind IS NOT NULL)
4396: THEN
4397: GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
4398: 'WHAT', 'coa_at_invoice_ind must be NULL');
4399: RAISE FND_API.G_EXC_ERROR;
4400: END IF;
4401:
4402: IF (p_coa_req_from_supl_ind IS NOT NULL)
4403: THEN
4402: IF (p_coa_req_from_supl_ind IS NOT NULL)
4403: THEN
4404: GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
4405: 'WHAT', 'coa_req_from_supl_ind must be NULL');
4406: RAISE FND_API.G_EXC_ERROR;
4407: END IF;
4408: END IF; -- coa_type is NULL
4409:
4410: END check_COA;
4463: AND (p_lot_optional_on_sample <> 'Y')
4464: THEN
4465: GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
4466: 'WHAT', 'lot_optional_on_sample value must be either NULL or Y');
4467: RAISE FND_API.G_EXC_ERROR;
4468: END IF;
4469:
4470: --Enhancement# 3476560. Added validation for 'Delayed Lot Entry' that it should be 'Y' or Null.
4471: IF (p_delayed_lot_entry IS NOT NULL)
4472: AND(p_delayed_lot_entry<>'Y')
4473: THEN
4474: GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
4475: 'WHAT', 'delayed_lot_entry value must be either NULL or Y');
4476: RAISE FND_API.G_EXC_ERROR;
4477: END IF;
4478:
4479: --RLNAGARA LPN ME 7027149 start
4480:
4480:
4481: IF (p_delayed_lpn_entry IS NOT NULL) AND (p_delayed_lpn_entry <> 'Y') THEN
4482: GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
4483: 'WHAT', 'delayed_lpn_entry value must be either NULL or Y');
4484: RAISE FND_API.G_EXC_ERROR;
4485: END IF;
4486:
4487: IF (p_VR_type IN ('CUSTOMER') AND (p_delayed_lpn_entry IS NOT NULL)) THEN
4488: GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
4486:
4487: IF (p_VR_type IN ('CUSTOMER') AND (p_delayed_lpn_entry IS NOT NULL)) THEN
4488: GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
4489: 'WHAT', 'delayed_lpn_entry value must be either NULL for CUSTOMER VRs');
4490: RAISE FND_API.G_EXC_ERROR;
4491: END IF;
4492:
4493: --RLNAGARA LPN ME 7027149 end
4494:
4497: AND(p_delayed_lot_entry = 'Y')
4498: THEN
4499: GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
4500: 'WHAT', 'delayed_lot_entry value cannot be Y when lot_optional_on_sample is NULL');
4501: RAISE FND_API.G_EXC_ERROR;
4502: END IF;
4503:
4504: IF (p_VR_type IN ('INVENTORY', 'WIP','SUPPLIER'))
4505: THEN
4507: AND (p_control_lot_attrib_ind <> 'Y')
4508: THEN
4509: GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
4510: 'WHAT', 'control_lot_attrib_ind value must be either NULL or Y');
4511: RAISE FND_API.G_EXC_ERROR;
4512: END IF;
4513:
4514: IF (p_sample_inv_trans_ind IS NOT NULL)
4515: AND (p_sample_inv_trans_ind <> 'Y')
4515: AND (p_sample_inv_trans_ind <> 'Y')
4516: THEN
4517: GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
4518: 'WHAT', 'sample_inv_trans_ind value must be either NULL or Y');
4519: RAISE FND_API.G_EXC_ERROR;
4520: END IF;
4521:
4522: -- Extra field for WIP :
4523: IF (p_VR_type = 'WIP')
4526: AND (p_control_batch_step_ind <> 'Y')
4527: THEN
4528: GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
4529: 'WHAT', 'control_batch_step_ind value must be either NULL or Y');
4530: RAISE FND_API.G_EXC_ERROR;
4531: END IF;
4532:
4533: -- Bug# 5440347 start
4534: IF (p_auto_complete_batch_step IS NOT NULL)
4535: AND (p_auto_complete_batch_step <> 'Y')
4536: THEN
4537: GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
4538: 'WHAT', 'auto_complete_batch_step value must be either NULL or Y');
4539: RAISE FND_API.G_EXC_ERROR;
4540: END IF;
4541: -- Bug# 5440347 end
4542:
4543:
4545: --IF ( p_lot_optional_on_sample IS NOT NULL)
4546: --THEN
4547: -- GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
4548: -- 'WHAT', 'lot_optional_on_sample must be NULL');
4549: -- RAISE FND_API.G_EXC_ERROR;
4550: --END IF;
4551:
4552: END IF;
4553:
4558: IF (p_lot_ctl = 1) AND (p_lot_optional_on_sample IS NOT NULL)
4559: THEN
4560: GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
4561: 'WHAT', 'For a non controlled item, lot_optional_on_sample must be NULL');
4562: RAISE FND_API.G_EXC_ERROR;
4563: END IF;
4564:
4565: IF (p_VR_type IN ('INVENTORY', 'WIP','SUPPLIER'))
4566: THEN
4568: IF (p_sample_inv_trans_ind IS NOT NULL)
4569: THEN
4570: GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
4571: 'WHAT', 'sample_inv_trans_ind must be NULL');
4572: RAISE FND_API.G_EXC_ERROR;
4573: END IF;
4574:
4575: --RLNAGARA Bug # 4900420 Removed the validation code for control_lot_attributes
4576:
4582: IF (p_control_batch_step_ind IS NOT NULL)
4583: THEN
4584: GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
4585: 'WHAT', 'control_batch_step_ind must be NULL');
4586: RAISE FND_API.G_EXC_ERROR;
4587: END IF;
4588: END IF;*/
4589: ELSE --p_lot_optional_on_sample IS NULL
4590:
4602: IF (p_control_lot_attrib_ind IS NOT NULL)
4603: THEN
4604: GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
4605: 'WHAT', 'control_lot_attrib_ind must be NULL');
4606: RAISE FND_API.G_EXC_ERROR;
4607: END IF;
4608:
4609: IF (p_in_spec_lot_status_id IS NOT NULL)
4610: THEN
4609: IF (p_in_spec_lot_status_id IS NOT NULL)
4610: THEN
4611: GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
4612: 'WHAT', 'in_spec_lot_status_id must be NULL');
4613: RAISE FND_API.G_EXC_ERROR;
4614: END IF;
4615:
4616: IF (p_out_of_spec_lot_status_id IS NOT NULL)
4617: THEN
4616: IF (p_out_of_spec_lot_status_id IS NOT NULL)
4617: THEN
4618: GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
4619: 'WHAT', 'out_of_spec_lot_status_id must be NULL');
4620: RAISE FND_API.G_EXC_ERROR;
4621: END IF;
4622: ELSE --p_status_ctl <> 'N'
4623: IF (p_control_lot_attrib_ind IS NULL)
4624: THEN
4629: IF (p_in_spec_lot_status_id IS NOT NULL)
4630: THEN
4631: GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
4632: 'WHAT', 'in_spec_lot_status_id must be NULL');
4633: RAISE FND_API.G_EXC_ERROR;
4634: END IF;
4635:
4636: IF (p_out_of_spec_lot_status_id IS NOT NULL)
4637: THEN
4636: IF (p_out_of_spec_lot_status_id IS NOT NULL)
4637: THEN
4638: GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
4639: 'WHAT', 'out_of_spec_lot_status_id must be NULL');
4640: RAISE FND_API.G_EXC_ERROR;
4641: END IF;
4642: ELSE
4643: --=========================================================================
4644: -- In this case, control_lot_attrib_ind IS NOT NULL,
4649: IF (p_in_spec_lot_status_id IS NULL)
4650: THEN
4651: GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
4652: 'WHAT', 'in_spec_lot_status_id is MANDATORY');
4653: RAISE FND_API.G_EXC_ERROR;
4654: ELSE
4655: OPEN c_lot_status(p_in_spec_lot_status_id);
4656: FETCH c_lot_status INTO dummy;
4657: IF (c_lot_status%NOTFOUND)
4660: FND_MESSAGE.SET_NAME('GMD','GMD_NOTFOUND');
4661: FND_MESSAGE.SET_TOKEN('WHAT', 'IN_SPEC_LOT_STATUS_ID');
4662: FND_MESSAGE.SET_TOKEN('VALUE', p_in_spec_lot_status_id);
4663: FND_MSG_PUB.ADD;
4664: RAISE FND_API.G_EXC_ERROR;
4665: END IF;
4666: CLOSE c_lot_status;
4667: END IF; -- in_spec_lot_status IS NULL
4668:
4669: IF (p_out_of_spec_lot_status_id IS NULL)
4670: THEN
4671: GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
4672: 'WHAT', 'out_of_spec_lot_status_id is MANDATORY');
4673: RAISE FND_API.G_EXC_ERROR;
4674: ELSE
4675: OPEN c_lot_status(p_out_of_spec_lot_status_id);
4676: FETCH c_lot_status INTO dummy;
4677: IF (c_lot_status%NOTFOUND)
4680: FND_MESSAGE.SET_NAME('GMD','GMD_NOTFOUND');
4681: FND_MESSAGE.SET_TOKEN('WHAT', 'OUT_OF_SPEC_LOT_STATUS_ID');
4682: FND_MESSAGE.SET_TOKEN('VALUE', p_out_of_spec_lot_status_id);
4683: FND_MSG_PUB.ADD;
4684: RAISE FND_API.G_EXC_ERROR;
4685: END IF;
4686: CLOSE c_lot_status;
4687: END IF; -- out_of_spec_lot_status_id IS NULL
4688: END IF; -- control_lot_attrib_ind IS NOT NULL