[Home] [Help]
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');
177: RAISE FND_API.G_EXC_ERROR;
178: END IF;
179:
180: -- Verify that the specification exists.
183: p_specifications => l_spec,
184: x_specifications => l_spec_out)
185: ) THEN
186: -- Fetch Error
187: GMD_API_PUB.Log_Message('GMD_SPEC_FETCH_ERROR');
188: RAISE e_spec_fetch_error;
189: END IF;
190:
191: l_spec := l_spec_out ;
199: p_sampling_plan => l_sampling_plan,
200: x_sampling_plan => l_sampling_plan_out)
201: ) THEN
202: -- Fetch Error
203: GMD_API_PUB.Log_Message('GMD_SAMPLING_PLAN_FETCH_ERROR');
204: RAISE e_smpl_plan_fetch_error;
205: END IF;
206: l_sampling_plan:= l_sampling_plan_out ;
207: END IF;
238: AND mon_vr_exist(l_mon_vr, l_spec))
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
248:
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
256: -- Spec VR Status Must be less than Spec Status upto Appoved Stages
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...
351: check_who( p_user_id => l_mon_vr.last_updated_by);
352: IF (l_mon_vr.creation_date IS NULL
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:
371: l_mon_vr.resource_instance_id := NULL;
372: else
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:
384: FETCH c_orgn INTO dummy;
385: IF (c_orgn%NOTFOUND)
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;
404: FETCH c_resources INTO dummy;
405: IF (c_resources%NOTFOUND)
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;
419: FETCH c_orgn INTO dummy;
420: IF (c_orgn%NOTFOUND)
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;
439: FETCH c_resource_instance INTO dummy;
440: IF (c_resource_instance%NOTFOUND)
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;
455: FETCH c_subinventory INTO dummy;
456: IF (c_subinventory%NOTFOUND)
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;
486: FETCH c_locator INTO dummy;
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
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)
510: INTO dummy;
511: IF (c_status%NOTFOUND)
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;
521: -- start_date : This field is mandatory
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;
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');
716: RAISE FND_API.G_EXC_ERROR;
717: END IF;
718:
719: -- Verify that the specification exists.
722: p_specifications => l_spec,
723: x_specifications => l_spec_out)
724: ) THEN
725: -- Fetch Error
726: GMD_API_PUB.Log_Message('GMD_SPEC_FETCH_ERROR');
727: RAISE e_spec_fetch_error;
728: END IF;
729:
730: l_spec := l_spec_out ;
738: p_sampling_plan => l_sampling_plan,
739: x_sampling_plan => l_sampling_plan_out)
740: ) THEN
741: -- Fetch Error
742: GMD_API_PUB.Log_Message('GMD_SAMPLING_PLAN_FETCH_ERROR');
743: RAISE e_smpl_plan_fetch_error;
744: END IF;
745: l_sampling_plan:= l_sampling_plan_out ;
746: END IF;
751: l_inv_vr := p_inv_vr;
752: IF (p_called_from = 'API') THEN
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
786: AND inv_vr_exist(l_inv_vr, l_spec))
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:
796: -- if auto sample flag on
797: IF ((p_inv_vr.sampling_plan_id IS NULL) and
798: (p_inv_vr.auto_sample_ind = 'Y'))
799: THEN
800: GMD_API_PUB.Log_Message('GMD_NEED_SAMPLE_PLAN');
801: RAISE e_smpl_plan_fetch_error;
802: END IF;
803:
804:
813: WHERE inventory_item_id = l_inventory_item_id
814: AND organization_id = l_organization_id;
815: EXCEPTION
816: WHEN OTHERS THEN
817: GMD_API_PUB.Log_Message('GMD_ITEM_FETCH_ERROR');
818: RAISE e_error_fetch_item;
819: END;
820:
821: IF (l_inv_vr.sampling_plan_id IS NOT NULL)
835: organization_id => l_organization_id ,
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:
847: -- The Start Date must be less than the End Date
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
855: -- Spec VR Status Must be less than Spec Status upto Appoved Stages
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.
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.
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:
981: check_who( p_user_id => l_inv_vr.last_updated_by);
982: IF (l_inv_vr.creation_date IS NULL
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:
994: FETCH c_orgn INTO dummy;
995: IF (c_orgn%NOTFOUND)
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;
1032: FETCH c_item_lot_number INTO dummy;
1033: IF (c_item_lot_number%NOTFOUND)
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;
1052: FETCH c_item_parent_lot INTO dummy;
1053: IF (c_item_parent_lot%NOTFOUND)
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;
1071: FETCH c_subinventory INTO dummy;
1072: IF (c_subinventory%NOTFOUND)
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;
1093: FETCH c_locator INTO dummy;
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
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)
1129: --RLNAGARA LPN ME 7027149 start Check for WMS enabled organization.
1130: IF (l_inv_vr.organization_id IS NOT NULL) THEN
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;
1147: INTO dummy;
1148: IF (c_status%NOTFOUND)
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;
1158: -- start_date : This field is mandatory
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: --=========================================================================
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');
1359: RAISE FND_API.G_EXC_ERROR;
1360: END IF;
1361:
1362: -- Verify that the specification exists.
1365: p_specifications => l_spec,
1366: x_specifications => l_spec_out)
1367: ) THEN
1368: -- Fetch Error
1369: GMD_API_PUB.Log_Message('GMD_SPEC_FETCH_ERROR');
1370: RAISE e_spec_fetch_error;
1371: END IF;
1372:
1373: l_spec := l_spec_out ;
1381: p_sampling_plan => l_sampling_plan,
1382: x_sampling_plan => l_sampling_plan_out)
1383: ) THEN
1384: -- Fetch Error
1385: GMD_API_PUB.Log_Message('GMD_SAMPLING_PLAN_FETCH_ERROR');
1386: RAISE e_smpl_plan_fetch_error;
1387: END IF;
1388: l_sampling_plan := l_sampling_plan_out ;
1389: END IF;
1393:
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:
1405: l_wip_vr := p_wip_vr;
1406: IF (p_called_from = 'API') THEN
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
1439: AND wip_vr_exist(l_wip_vr, l_spec))
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
1455: WHERE inventory_item_id = l_inventory_item_id
1456: AND organization_id = l_organization_id;
1457: EXCEPTION
1458: WHEN OTHERS THEN
1459: GMD_API_PUB.Log_Message('GMD_ITEM_FETCH_ERROR');
1460: RAISE e_error_fetch_item;
1461: END;
1462:
1463:
1474: organization_id => l_organization_id,
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:
1486: -- The Start Date must be less than the End Date
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...
1901: l_wip_vr.routing_id IS NULL AND
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: --=========================================================================
1913: check_who( p_user_id => l_wip_vr.last_updated_by);
1914: IF (l_wip_vr.creation_date IS NULL
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:
1929: FETCH c_orgn_plant INTO dummy;
1930: IF (c_orgn_plant%NOTFOUND)
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;
1975: --RLNAGARA LPN ME 7027149 start Check for WMS enabled organization.
1976: IF (l_wip_vr.organization_id IS NOT NULL) THEN
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;
1992: INTO dummy;
1993: IF (c_status%NOTFOUND)
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;
2003: -- start_date : This field is mandatory
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: --=========================================================================
2023: IF (l_wip_vr.batch_id IS NOT NULL)
2024: THEN
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:
2036: , l_routing_id, l_routing_no, l_routing_vers;
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:
2050: --=========================================================================
2051: -- 1: recipe_id
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
2057: END IF;
2058: -- 2: recipe_no
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
2064: END IF;
2065: -- 3: recipe_version
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:
2072:
2073: -- 4: 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
2079: END IF;
2080: -- 5: formula_no
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
2086: END IF;
2087: -- 6: formula_vers
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:
2094:
2095: -- 7: 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
2101: END IF;
2102: -- 8: routing_no
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
2108: END IF;
2109: -- 9: routing_vers
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:
2148: , l_routing_id, l_routing_no, l_routing_vers;
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
2161:
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
2173: END IF;
2174: -- 4: formula_id
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
2180: END IF;
2181: -- 5: formula_no
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
2187: END IF;
2188: -- 6: formula_vers
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:
2195:
2196: -- 7: 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
2202: END IF;
2203: -- 8: routing_no
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
2209: END IF;
2210: -- 9: routing_vers
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:
2237: INTO l_recipe_id, l_recipe_version;
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:
2250:
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:
2281:
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:
2300:
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:
2329:
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:
2347: INTO l_routing_id, l_routing_vers;
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:
2371: AND (l_wip_vr.material_detail_id IS NULL)
2372: THEN
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:
2382: INTO dummy;
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;
2397: IF (l_wip_vr.material_detail_id IS NOT NULL)
2398: THEN
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:
2408: INTO dummy;
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;
2423: -- If step_no NOT NULL and step_id NULL, then error.
2424: --=========================================================================
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:
2437: INTO l_step_no;
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:
2454: INTO l_step_no;
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:
2474: INTO l_oprn_no, l_oprn_vers;
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:
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');
2702: RAISE FND_API.G_EXC_ERROR;
2703: END IF;
2704:
2705: -- Verify that the specification exists.
2708: p_specifications => l_spec,
2709: x_specifications => l_spec_out)
2710: ) THEN
2711: -- Fetch Error
2712: GMD_API_PUB.Log_Message('GMD_SPEC_FETCH_ERROR');
2713: RAISE e_spec_fetch_error;
2714: END IF;
2715:
2716: l_spec := l_spec_out ;
2724: p_sampling_plan => l_sampling_plan,
2725: x_sampling_plan => l_sampling_plan_out)
2726: ) THEN
2727: -- Fetch Error
2728: GMD_API_PUB.Log_Message('GMD_SAMPLING_PLAN_FETCH_ERROR');
2729: RAISE e_smpl_plan_fetch_error;
2730: END IF;
2731: l_sampling_plan := l_sampling_plan_out;
2732: END IF;
2756: AND cust_vr_exist(p_cust_vr, l_spec))
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
2772: WHERE inventory_item_id = l_inventory_item_id
2773: AND organization_id = l_organization_id;
2774: EXCEPTION
2775: WHEN OTHERS THEN
2776: GMD_API_PUB.Log_Message('GMD_ITEM_FETCH_ERROR');
2777: RAISE e_error_fetch_item;
2778: END;
2779:
2780: --odab added this test.
2788: organization_id => l_organization_id ,
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:
2799:
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
2807: -- Spec VR Status Must be less than Spec Status upto Appoved Stages
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...
2958: check_who( p_user_id => p_cust_vr.last_updated_by);
2959: IF (p_cust_vr.creation_date IS NULL
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:
2977: FETCH c_orgn INTO dummy;
2978: IF (c_orgn%NOTFOUND)
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;*/
3027: INTO dummy;
3028: IF (c_status%NOTFOUND)
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;
3039: -- start_date : This field is mandatory
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: --=========================================================================
3056: -- cust_id : This field is mandatory
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;
3082: INTO dummy;
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;
3095: IF (p_cust_vr.ship_to_site_id IS NOT NULL)
3096: THEN
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:
3106: INTO dummy;
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;
3123: INTO dummy;
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;
3139: OR p_cust_vr.order_line IS NOT NULL)
3140: THEN
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
3146: END IF;
3147: IF (p_cust_vr.order_line_id IS NULL
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:
3157: INTO dummy;
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;
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');
3353: RAISE FND_API.G_EXC_ERROR;
3354: END IF;
3355:
3356: -- Verify that the specification exists.
3359: p_specifications => l_spec,
3360: x_specifications => l_spec_out)
3361: ) THEN
3362: -- Fetch Error
3363: GMD_API_PUB.Log_Message('GMD_SPEC_FETCH_ERROR');
3364: RAISE e_spec_fetch_error;
3365: END IF;
3366:
3367: l_spec := l_spec_out ;
3375: p_sampling_plan => l_sampling_plan,
3376: x_sampling_plan => l_sampling_plan_out)
3377: ) THEN
3378: -- Fetch Error
3379: GMD_API_PUB.Log_Message('GMD_SAMPLING_PLAN_FETCH_ERROR');
3380: RAISE e_smpl_plan_fetch_error;
3381: END IF;
3382: l_sampling_plan := l_sampling_plan_out ;
3383: END IF;
3384:
3385: IF (p_called_from = 'API') THEN
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
3414: AND supp_vr_exist(p_supp_vr, l_spec))
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
3424: IF ((p_supp_vr.sampling_plan_id IS NULL) and
3425: (p_supp_vr.auto_sample_ind = 'Y'))
3426: THEN
3427: GMD_API_PUB.Log_Message('GMD_NEED_SAMPLE_PLAN');
3428: RAISE e_smpl_plan_fetch_error;
3429: END IF;
3430:
3431: -- Sample Quantity UOM must be convertible to Item's UOM
3439: WHERE inventory_item_id = l_inventory_item_id
3440: AND organization_id = l_organization_id;
3441: EXCEPTION
3442: WHEN OTHERS THEN
3443: GMD_API_PUB.Log_Message('GMD_ITEM_FETCH_ERROR');
3444: RAISE e_error_fetch_item;
3445: END;
3446:
3447: --odab added this test.
3455: organization_id => l_organization_id ,
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:
3466:
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
3474: -- Spec VR Status Must be less than Spec Status upto Appoved Stages
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...
3605: check_who( p_user_id => p_supp_vr.last_updated_by);
3606: IF (p_supp_vr.creation_date IS NULL
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:
3619: OPEN c_orgn( p_supp_vr.organization_id);
3620: FETCH c_orgn INTO dummy;
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;
3674: --RLNAGARA LPN ME 7027149 start Check for WMS enabled organization.
3675: IF (p_supp_vr.organization_id IS NOT NULL) THEN
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;
3691: INTO dummy;
3692: IF (c_status%NOTFOUND)
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;
3702: -- start_date : This field is mandatory
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: --=========================================================================
3719: -- supplier_id : This field is mandatory
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;
3727: FETCH c_supplier INTO dummy;
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;
3762: IF (p_supp_vr.po_header_id IS NOT NULL)
3763: THEN
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:
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;
3787: IF (p_supp_vr.po_line_id IS NOT NULL)
3788: THEN
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:
3794: END IF;
3795:
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:
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;
3959:
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;
3966: l_inventory_spec_vrs.spec_id := p_spec_id;
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;
3976: -- Fetch the row
3977: -- =============
3978: IF NOT GMD_Inventory_Spec_VRS_PVT.Fetch_Row(l_inventory_spec_vrs,l_inventory_spec_vrs_out)
3979: THEN
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;
3989: -- Terminate if the row is already delete marked
3990: -- =============================================
3991: IF l_inventory_spec_vrs.delete_mark <> 0
3992: THEN
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;
4043:
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;
4050: l_wip_spec_vrs.spec_id := p_spec_id;
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;
4060: -- Fetch the row
4061: -- =============
4062: IF NOT GMD_WIP_Spec_VRS_PVT.Fetch_Row(l_wip_spec_vrs,l_wip_spec_vrs_out)
4063: THEN
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;
4073: -- Terminate if the row is already delete marked
4074: -- =============================================
4075: IF l_wip_spec_vrs.delete_mark <> 0
4076: THEN
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;
4126:
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;
4133: l_customer_spec_vrs.spec_id := p_spec_id;
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;
4143: -- Fetch the row
4144: -- =============
4145: IF NOT GMD_Customer_Spec_VRS_PVT.Fetch_Row(l_customer_spec_vrs,l_customer_spec_vrs_out)
4146: THEN
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;
4156: -- Terminate if the row is already delete marked
4157: -- =============================================
4158: IF l_customer_spec_vrs.delete_mark <> 0
4159: THEN
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;
4209:
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;
4216: l_supplier_spec_vrs.spec_id := p_spec_id;
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;
4226: -- Fetch the row
4227: -- =============
4228: IF NOT GMD_Supplier_Spec_VRS_PVT.Fetch_Row(l_supplier_spec_vrs,l_supplier_spec_vrs_out)
4229: THEN
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;
4239: -- Terminate if the row is already delete marked
4240: -- =============================================
4241: IF l_supplier_spec_vrs.delete_mark <> 0
4242: THEN
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;
4340: -- The only value for these controls are (NULL, 'Y')
4341: IF (p_coa_at_ship_ind IS NOT NULL)
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)
4347: END IF;
4348: IF (p_coa_at_invoice_ind IS NOT NULL)
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)
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
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)
4386: IF (p_coa_type IS NULL)
4387: THEN
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:
4393: END IF;
4394:
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:
4400: END IF;
4401:
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
4461: -- The only value for these controls are (NULL, 'Y')
4462: IF (p_lot_optional_on_sample IS NOT NULL)
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:
4478:
4479: --RLNAGARA LPN ME 7027149 start
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:
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',
4489: 'WHAT', 'delayed_lpn_entry value must be either NULL for CUSTOMER VRs');
4490: RAISE FND_API.G_EXC_ERROR;
4491: END IF;
4492:
4495: --Bug# 3652938. Added validation for the invalid combination of 'Lot Optional on Sample' and 'Delayed Lot Entry'.
4496: IF (p_lot_optional_on_sample IS NULL)
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:
4505: THEN
4506: IF (p_control_lot_attrib_ind IS NOT NULL)
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:
4513:
4514: IF (p_sample_inv_trans_ind IS NOT NULL)
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:
4524: THEN
4525: IF (p_control_batch_step_ind IS NOT NULL)
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
4543:
4544: --Enhancement# 3476560. Removed special validation for 'Lot Optional on sample'.
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:
4556: -- Functional Check :
4557: -- Bug 2698118 : When non-lot-controlled item then lot_optional_on_sample MUST be NULL
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:
4566: THEN
4567: IF (p_lot_optional_on_sample IS NOT NULL ) 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:
4580: /*IF (p_VR_type = 'WIP')
4581: THEN
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;*/
4600: -- control_lot_attrib_ind, in_spec_lot_status_id, out_of_spec_lot_status_id
4601: --=========================================================================
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:
4607: END IF;
4608:
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:
4614: END IF;
4615:
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'
4627: -- in_spec_lot_status_id, out_of_spec_lot_status_id
4628: --=========================================================================
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:
4634: END IF;
4635:
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
4647: --=========================================================================
4648: -- Check the values of in_spec_lot_status_id and out_of_spec_lot_status_id
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);
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);