[Home] [Help]
885: mc_name,
886: position_key,
887: position
888: --sukhwsin: changes for SB Effectivity ends
889: FROM AHL_RT_OPER_MATERIALS_V
890: WHERE rt_oper_material_id = c_rt_oper_material_id;
891: BEGIN
892: -- Get the old record from AHL_RT_OPER_MATERIALS.
893: OPEN get_old_rec( p_x_material_req_rec.rt_oper_material_id );
888: --sukhwsin: changes for SB Effectivity ends
889: FROM AHL_RT_OPER_MATERIALS_V
890: WHERE rt_oper_material_id = c_rt_oper_material_id;
891: BEGIN
892: -- Get the old record from AHL_RT_OPER_MATERIALS.
893: OPEN get_old_rec( p_x_material_req_rec.rt_oper_material_id );
894: FETCH get_old_rec INTO
895: l_old_material_req_rec.item_group_id,
896: l_old_material_req_rec.item_group_name,
1156: from ahl_route_effectivities_v
1157: WHERE ROUTE_EFFECTIVITY_ID = C_ROUTE_EFFECTIVITY_ID
1158: ;
1159: BEGIN
1160: -- Get the old record from AHL_RT_OPER_MATERIALS.
1161: OPEN get_old_rec( p_x_route_efct_rec.ROUTE_EFFECTIVITY_ID );
1162: FETCH get_old_rec INTO
1163: l_old_route_efct_rec.inventory_item_id
1164: ,l_old_route_efct_rec.inventory_master_org_id
1852: --sukhwsin: added for duplicate check for MC and Position
1853: ROMV.mc_id,
1854: ROMV.position_key
1855: FROM
1856: AHL_RT_OPER_MATERIALS ROMV
1857: WHERE
1858: ROMV.object_id = c_object_id AND
1859: ROMV.association_type_code = c_association_type AND
1860: NOT EXISTS
2339: IF ( p_x_material_req_tbl(i).dml_operation = 'C' ) THEN
2340: BEGIN
2341: -- Insert the record
2342: p_x_material_req_tbl(i).object_version_number := 1;
2343: INSERT INTO AHL_RT_OPER_MATERIALS
2344: (
2345: RT_OPER_MATERIAL_ID,
2346: OBJECT_VERSION_NUMBER,
2347: OBJECT_ID,
2383: POSITION_KEY
2384: --sukhwsin: Added columns for SB Effectivity ends
2385: ) VALUES
2386: (
2387: AHL_RT_OPER_MATERIALS_S.NEXTVAL,
2388: p_x_material_req_tbl(i).object_version_number,
2389: p_object_id,
2390: p_association_type,
2391: p_x_material_req_tbl(i).position_path_id ,
2458: fnd_log.string
2459: (
2460: fnd_log.level_unexpected,
2461: 'ahl.plsql.'||G_PKG_NAME||'.'||G_API_NAME,
2462: 'AHL_RT_OPER_MATERIALS insert error = ['||SQLERRM||']'
2463: );
2464: END IF;
2465: END IF;
2466: END;
2466: END;
2467: ELSIF ( p_x_material_req_tbl(i).dml_operation = 'U' ) THEN
2468: BEGIN
2469: -- Update the record
2470: UPDATE AHL_RT_OPER_MATERIALS SET
2471: object_version_number = object_version_number + 1,
2472: item_comp_detail_id = p_x_material_req_tbl(i).item_comp_detail_id ,
2473: position_path_id = p_x_material_req_tbl(i).position_path_id ,
2474: item_group_id = p_x_material_req_tbl(i).item_group_id,
2527: fnd_log.string
2528: (
2529: fnd_log.level_unexpected,
2530: 'ahl.plsql.'||G_PKG_NAME||'.'||G_API_NAME,
2531: 'AHL_RT_OPER_MATERIALS update error = ['||SQLERRM||']'
2532: );
2533: END IF;
2534: END IF;
2535: END;
2534: END IF;
2535: END;
2536: ELSIF ( p_x_material_req_tbl(i).dml_operation = 'D' ) THEN
2537: -- Delete the record
2538: DELETE AHL_RT_OPER_MATERIALS
2539: WHERE rt_oper_material_id = p_x_material_req_tbl(i).rt_oper_material_id
2540: AND object_version_number = p_x_material_req_tbl(i).object_version_number;
2541: -- If the record does not exist, then, abort API.
2542: IF ( SQL%ROWCOUNT = 0 ) THEN
2951: -- Set OUT values
2952: p_x_route_efct_tbl(i).object_version_number := p_x_route_efct_tbl(i).object_version_number + 1;
2953: ELSIF ( p_x_route_efct_tbl(i).dml_operation = 'D' ) THEN
2954: -- Delete the record
2955: DELETE FROM AHL_RT_OPER_MATERIALS
2956: WHERE OBJECT_ID = p_x_route_efct_tbl(i).route_effectivity_id
2957: AND ASSOCIATION_TYPE_CODE = 'DISPOSITION';
2958: -- If no records exist, then, Continue.
2959: IF ( SQL%ROWCOUNT = 0 ) THEN