[Home] [Help]
106: -- Check for duplicate Setup Data in AHL_RA_SETUPS
107: BEGIN
108: SELECT 'X'
109: INTO l_dummy
110: FROM DUAL
111: WHERE EXISTS (SELECT 1
112: FROM AHL_RA_SETUPS
113: WHERE SETUP_CODE = l_setup_data_rec.SETUP_CODE
114: AND nvl(STATUS_ID,'-1') = nvl(DECODE(l_setup_data_rec.SETUP_CODE,'ITEM_STATUS',l_setup_data_rec.STATUS_ID,STATUS_ID),'-1')
173: -- Validate l_setup_data_rec.STATUS_ID passed to be a valid value
174: BEGIN
175: SELECT 'X'
176: INTO l_dummy
177: FROM DUAL
178: WHERE EXISTS (SELECT 1
179: FROM MTL_MATERIAL_STATUSES_VL
180: WHERE STATUS_ID = l_setup_data_rec.STATUS_ID);
181:
202: -- Validate l_setup_data_rec.REMOVAL_CODE passed to be a valid value
203: BEGIN
204: SELECT 'X'
205: INTO l_dummy
206: FROM DUAL
207: WHERE EXISTS (SELECT 1
208: FROM FND_LOOKUPS
209: WHERE LOOKUP_TYPE = 'AHL_REMOVAL_CODE'
210: AND LOOKUP_CODE = l_setup_data_rec.REMOVAL_CODE);
226:
227: END IF;
228:
229: -- Initialize RA_SETUP_ID to sequence next val for insert
230: SELECT AHL_RA_SETUPS_S.NEXTVAL INTO l_setup_data_rec.RA_SETUP_ID FROM DUAL;
231:
232: -- Initialize object version number to 1
233: l_setup_data_rec.OBJECT_VERSION_NUMBER := 1;
234:
618: --Validate MC_HEADER_ID
619: BEGIN
620: SELECT 'Y'
621: INTO l_dummy
622: FROM dual
623: WHERE EXISTS( SELECT 'X'
624: FROM ahl_mc_headers_b
625: WHERE mc_header_id = l_ra_def_hdr_rec.MC_HEADER_ID);
626:
643: --Validate RELATIONSHIP_ID
644: BEGIN
645: SELECT 'Y'
646: INTO l_dummy
647: FROM dual
648: WHERE EXISTS(SELECT 'X'
649: FROM ahl_mc_relationships
650: WHERE mc_header_id = l_ra_def_hdr_rec.MC_HEADER_ID
651: AND relationship_id = l_ra_def_hdr_rec.RELATIONSHIP_ID);
669: --Validate INVENTORY_ITEM_ID, INVENTORY_ORG_ID, ITEM_REVISION from AHL_ITEM_ASSOCIATIONS_B
670: BEGIN
671: SELECT 'Y'
672: INTO l_dummy
673: FROM dual
674: WHERE EXISTS(SELECT 'X'
675: FROM ahl_mc_relationships mcr, ahl_item_associations_b ia
676: WHERE mcr.relationship_id = l_ra_def_hdr_rec.RELATIONSHIP_ID
677: AND mcr.item_group_id = ia.item_group_id
699: --Uniquness Check for MC_HEADER_ID, RELATIONSHIP_ID, INVENTORY_ITEM_ID, INVENTORY_ORG_ID, ITEM_REVISION
700: BEGIN
701: SELECT 'Y'
702: INTO l_dummy
703: FROM dual
704: WHERE EXISTS(SELECT 'X'
705: FROM ahl_ra_definition_hdr
706: WHERE mc_header_id = l_ra_def_hdr_rec.MC_HEADER_ID
707: AND relationship_id = l_ra_def_hdr_rec.RELATIONSHIP_ID
724: END IF;
725: END;
726:
727: -- Initialize RA_DEFINITION_HDR_ID to sequence next val for insert
728: SELECT AHL_RA_DEFINITION_HDR_S.NEXTVAL into l_ra_def_hdr_rec.RA_DEFINITION_HDR_ID from dual;
729:
730: -- Initialize object version number to 1
731: l_ra_def_hdr_rec.OBJECT_VERSION_NUMBER := 1;
732:
1235: --Uniqueness Check for the counterId
1236: BEGIN
1237: SELECT 'Y'
1238: INTO l_dummy
1239: FROM dual
1240: WHERE EXISTS(SELECT 'X'
1241: FROM AHL_RA_DEFINITION_DTLS
1242: WHERE COUNTER_ID = l_ra_def_dtl_rec.COUNTER_ID
1243: AND RA_DEFINITION_HDR_ID = l_ra_def_dtl_rec.RA_DEFINITION_HDR_ID);
1293: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1294: END IF;
1295:
1296: -- Initialize RA_DEFINITION_DTL_ID to sequence next val for insert
1297: SELECT AHL_RA_DEFINITION_DTLS_S.NEXTVAL into l_ra_def_dtl_rec.RA_DEFINITION_DTL_ID from dual;
1298:
1299: -- Initialize object version number to 1
1300: l_ra_def_dtl_rec.OBJECT_VERSION_NUMBER := 1;
1301:
2264: --A since New Counter should not have been previously declared as overhaul counter.
2265: BEGIN
2266: SELECT 'Y'
2267: INTO l_dummy
2268: FROM dual
2269: WHERE EXISTS(SELECT 'X'
2270: FROM AHL_RA_CTR_ASSOCIATIONS
2271: WHERE since_overhaul_counter_id = l_counter_assoc_rec.SINCE_NEW_COUNTER_ID);
2272:
2290: IF(l_counter_assoc_rec.SINCE_OVERHAUL_COUNTER_ID IS NOT NULL) THEN
2291: BEGIN
2292: SELECT 'Y'
2293: INTO l_dummy
2294: FROM dual
2295: WHERE EXISTS(SELECT 'X'
2296: FROM AHL_RA_CTR_ASSOCIATIONS
2297: WHERE since_new_counter_id = l_counter_assoc_rec.SINCE_OVERHAUL_COUNTER_ID);
2298:
2317: --Duplicate record validation
2318: BEGIN
2319: SELECT 'Y'
2320: INTO l_dummy
2321: FROM dual
2322: WHERE EXISTS(SELECT 'X'
2323: FROM AHL_RA_CTR_ASSOCIATIONS counters
2324: WHERE SINCE_NEW_COUNTER_ID = l_counter_assoc_rec.SINCE_NEW_COUNTER_ID
2325: AND nvl(SINCE_OVERHAUL_COUNTER_ID,-1) = nvl(l_counter_assoc_rec.SINCE_OVERHAUL_COUNTER_ID,-1));
2341: END IF;
2342: END;
2343:
2344: -- Initialize RA_COUNTER_ASSOCIATION_ID to sequence next val for insert
2345: SELECT AHL_RA_CTR_ASSOCIATIONS_S.NEXTVAL into l_counter_assoc_rec.RA_COUNTER_ASSOCIATION_ID from dual;
2346:
2347: -- Initialize object version number to 1
2348: l_counter_assoc_rec.OBJECT_VERSION_NUMBER := 1;
2349:
2768: -- See earlier Query in 120.11
2769: BEGIN
2770: SELECT 'Y'
2771: INTO l_dummy
2772: FROM DUAL
2773: WHERE EXISTS(SELECT 'X'
2774: FROM MTL_PARAMETERS MP
2775: WHERE MP.ORGANIZATION_ID = l_fct_assoc_rec.ORGANIZATION_ID
2776: AND MP.EAM_ENABLED_FLAG='Y');
2794: -- Validate value of FORECAST_DESIGNATOR passed
2795: BEGIN
2796: SELECT 'Y'
2797: INTO l_dummy
2798: FROM DUAL
2799: WHERE EXISTS(SELECT 'X'
2800: FROM mrp_forecast_designators_v MRP
2801: WHERE MRP.FORECAST_DESIGNATOR = l_fct_assoc_rec.FORECAST_DESIGNATOR
2802: AND MRP.ORGANIZATION_ID = l_fct_assoc_rec.ORGANIZATION_ID
2860: -- 3. records with Overlapping values of probablities should not exist for the Org Id in AHL_RA_FCT_ASSOCIATIONS
2861: BEGIN
2862: SELECT 'Y'
2863: INTO l_dummy
2864: FROM DUAL
2865: WHERE EXISTS (SELECT 'X'
2866: FROM AHL_RA_FCT_ASSOCIATIONS
2867: WHERE ORGANIZATION_ID = l_fct_assoc_rec.ORGANIZATION_ID
2868: -- Bug 4998568 :: Probability Values Should not overlap irrespective of the Fct Designator
2899: -- Record can be created for each Organization - irrespective of the Forecast Selected.
2900: BEGIN
2901: SELECT 'Y'
2902: INTO l_dummy
2903: FROM DUAL
2904: WHERE EXISTS (SELECT 'X'
2905: FROM AHL_RA_FCT_ASSOCIATIONS
2906: WHERE ORGANIZATION_ID = l_fct_assoc_rec.ORGANIZATION_ID
2907: AND ASSOCIATION_TYPE_CODE = l_fct_assoc_rec.ASSOCIATION_TYPE_CODE);
2929:
2930: END IF;
2931:
2932: -- Initialize RA_FCT_ASSOCIATION_ID to sequence next val for insert
2933: SELECT AHL_RA_FCT_ASSOCIATIONS_S.NEXTVAL INTO l_fct_assoc_rec.RA_FCT_ASSOCIATION_ID FROM DUAL;
2934:
2935: -- Initialize object version number to 1
2936: l_fct_assoc_rec.OBJECT_VERSION_NUMBER := 1;
2937:
3251: -- Removed non-required reference to ORG_ORGANIZATION_DEFINTIONS below
3252: -- See earlier Query in 120.11
3253: SELECT 'Y'
3254: INTO l_dummy
3255: FROM DUAL
3256: WHERE EXISTS(SELECT 'X'
3257: FROM MTL_PARAMETERS MP
3258: WHERE MP.ORGANIZATION_ID = l_fct_assoc_rec.ORGANIZATION_ID
3259: AND MP.EAM_ENABLED_FLAG='Y');
3277: -- Validate value of FORECAST_DESIGNATOR passed
3278: BEGIN
3279: SELECT 'Y'
3280: INTO l_dummy
3281: FROM DUAL
3282: WHERE EXISTS(SELECT 'X'
3283: FROM mrp_forecast_designators_v MRP
3284: WHERE MRP.FORECAST_DESIGNATOR = l_fct_assoc_rec.FORECAST_DESIGNATOR
3285: AND MRP.ORGANIZATION_ID = l_fct_assoc_rec.ORGANIZATION_ID
3343: -- 3. records with Overlapping values of probablities should not exist for the Org Id and forecast designator in AHL_RA_FCT_ASSOCIATIONS
3344: BEGIN
3345: SELECT 'Y'
3346: INTO l_dummy
3347: FROM DUAL
3348: WHERE EXISTS (SELECT 'X'
3349: FROM AHL_RA_FCT_ASSOCIATIONS
3350: WHERE ORGANIZATION_ID = l_fct_assoc_rec.ORGANIZATION_ID
3351: -- Bug 4998568 :: Probability Values Should not overlap irrespective of the Fct Designator
3383: -- Record can be created for each Organization - irrespective of the Forecast Selected.
3384: BEGIN
3385: SELECT 'Y'
3386: INTO l_dummy
3387: FROM DUAL
3388: WHERE EXISTS (SELECT 'X'
3389: FROM AHL_RA_FCT_ASSOCIATIONS
3390: WHERE ORGANIZATION_ID = l_fct_assoc_rec.ORGANIZATION_ID
3391: AND ASSOCIATION_TYPE_CODE = l_fct_assoc_rec.ASSOCIATION_TYPE_CODE