CREATE OR REPLACE PACKAGE BODY spr_001a_smp_calculation AS /****************************************************************************** Function: smp_records_exist Purpose: Returns true if smp records exist for the seedlot number, false otherwise. ******************************************************************************/ FUNCTION smp_records_exist( p_seedlot_number IN VARCHAR2) RETURN BOOLEAN IS v_record_count NUMBER := 0; BEGIN SELECT COUNT(*) INTO v_record_count FROM SMP_MIX WHERE seedlot_number = p_seedlot_number; IF v_record_count > 0 THEN RETURN TRUE; ELSE RETURN FALSE; END IF; END; /****************************************************************************** Procedure: get_display_fields Purpose: Retrieve information such as client acronyms, addresses, names ******************************************************************************/ PROCEDURE get_display_fields ( p_seedlot_number IN VARCHAR2 ,p_seed_plan_unit_id IN VARCHAR2 ,p_spu_name IN OUT VARCHAR2 ,p_spu_species IN OUT VARCHAR2 ,p_spu_spz IN OUT VARCHAR2 ,p_spu_elev IN OUT VARCHAR2 ,p_orchard_id IN VARCHAR2 ,p_orchard_name IN OUT VARCHAR2 ,p_secondary_orchard_id IN VARCHAR2 ,p_secondary_orchard_name IN OUT VARCHAR2 ,p_error_message IN OUT VARCHAR2) IS CURSOR c_orchard (p_lookup_orchard_id IN VARCHAR2) IS SELECT o.orchard_name FROM orchard o , client_location c WHERE o.orchard_id = p_lookup_orchard_id AND c.client_number = o.client_number AND c.client_locn_code = o.client_locn_code; CURSOR c_spu IS SELECT spu_display , vegetation_code , seed_plan_zone_code , elev_display FROM v_spw_seed_plan_unit WHERE seed_plan_unit_id = to_number(p_seed_plan_unit_id); BEGIN --Orchard IF p_orchard_id IS NULL THEN p_orchard_name := NULL; ELSE OPEN c_orchard(p_orchard_id); FETCH c_orchard INTO p_orchard_name; CLOSE c_orchard; END IF; --Secondary Orchard IF p_secondary_orchard_id IS NULL THEN p_secondary_orchard_name := NULL; ELSE OPEN c_orchard(p_secondary_orchard_id); FETCH c_orchard INTO p_secondary_orchard_name; CLOSE c_orchard; END IF; --SPU IF p_seed_plan_unit_id IS NULL THEN p_spu_species := NULL; p_spu_spz := NULL; p_spu_elev := NULL; ELSE --Get SPU Display field OPEN c_spu; FETCH c_spu INTO p_spu_name,p_spu_species,p_spu_spz,p_spu_elev; CLOSE c_spu; END IF; END get_display_fields; /****************************************************************************** Procedure: populate_parent_tree Purpose: Populate parameters with data based on parent tree and seed plan unit id. ******************************************************************************/ PROCEDURE populate_parent_tree ( p_parent_tree_id IN VARCHAR2 ,p_seedlot_number IN VARCHAR2 ,p_seed_plan_unit_id IN VARCHAR2 ,p_get_latest_gq_ind IN VARCHAR2 ,p_bv_g IN OUT VARCHAR2 ,p_bv_d IN OUT VARCHAR2 ,p_bv_r IN OUT VARCHAR2 ,p_bv_m IN OUT VARCHAR2 ,p_cv_g IN OUT VARCHAR2 ,p_cv_d IN OUT VARCHAR2 ,p_cv_r IN OUT VARCHAR2 ,p_cv_m IN OUT VARCHAR2 ,p_g_estimated_ind IN OUT VARCHAR2 ,p_d_estimated_ind IN OUT VARCHAR2 ,p_r_estimated_ind IN OUT VARCHAR2 ,p_m_estimated_ind IN OUT VARCHAR2 ,p_elevation IN OUT VARCHAR2 ,p_latitude_degrees IN OUT VARCHAR2 ,p_latitude_minutes IN OUT VARCHAR2 ,p_latitude_seconds IN OUT VARCHAR2 ,p_longitude_degrees IN OUT VARCHAR2 ,p_longitude_minutes IN OUT VARCHAR2 ,p_longitude_seconds IN OUT VARCHAR2 ,p_geography_error_ind IN OUT VARCHAR2 ,p_revision_count IN OUT VARCHAR2 ,p_error_message IN OUT VARCHAR2) IS -- cursor to get the genetic quality values based on pt id and spu. CURSOR c_gq (p_pt_id IN NUMBER ,p_ssu_id IN NUMBER) IS SELECT pt.parent_tree_id , pt.parent_tree_number , ptgqbvg.genetic_quality_value bv_g , ptgqbvd.genetic_quality_value bv_d , ptgqbvr.genetic_quality_value bv_r , ptgqbvm.genetic_quality_value bv_m , ptgqcvg.genetic_quality_value cv_g , ptgqcvd.genetic_quality_value cv_d , ptgqcvr.genetic_quality_value cv_r , ptgqcvm.genetic_quality_value cv_m --If Parent Tree nor Female Parent have geog - set ind to 'Y' , CASE WHEN NVL(pt.elevation,0) > 0 OR NVL(fem_pt.elevation,0) > 0 THEN 'N' ELSE 'Y' END geography_error_ind FROM PARENT_TREE_GENETIC_QUALITY ptgqbvg , PARENT_TREE_GENETIC_QUALITY ptgqbvd , PARENT_TREE_GENETIC_QUALITY ptgqbvr , PARENT_TREE_GENETIC_QUALITY ptgqbvm , PARENT_TREE_GENETIC_QUALITY ptgqcvg , PARENT_TREE_GENETIC_QUALITY ptgqcvd , PARENT_TREE_GENETIC_QUALITY ptgqcvr , PARENT_TREE_GENETIC_QUALITY ptgqcvm , PARENT_TREE pt , PARENT_TREE fem_pt WHERE pt.parent_tree_reg_status_code = 'APP' AND fem_pt.parent_tree_id(+) = pt.female_parent_parent_tree_id AND ptgqbvg.parent_tree_id(+) = pt.parent_tree_id AND ptgqbvg.genetic_type_code(+) = 'BV' AND ptgqbvg.genetic_worth_code(+) = 'G' AND ptgqbvg.seed_plan_unit_id(+) = p_ssu_id AND ptgqbvg.genetic_worth_calc_ind(+) = 'Y' AND ptgqbvd.parent_tree_id(+) = pt.parent_tree_id AND ptgqbvd.genetic_type_code(+) = 'BV' AND ptgqbvd.genetic_worth_code(+) = 'D' AND ptgqbvd.seed_plan_unit_id(+) = p_ssu_id AND ptgqbvd.genetic_worth_calc_ind(+) = 'Y' AND ptgqbvr.parent_tree_id(+) = pt.parent_tree_id AND ptgqbvr.genetic_type_code(+) = 'BV' AND ptgqbvr.genetic_worth_code(+) = 'R' AND ptgqbvr.seed_plan_unit_id(+) = p_ssu_id AND ptgqbvr.genetic_worth_calc_ind(+) = 'Y' AND ptgqbvm.parent_tree_id(+) = pt.parent_tree_id AND ptgqbvm.genetic_type_code(+) = 'BV' AND ptgqbvm.genetic_worth_code(+) = 'M' AND ptgqbvm.seed_plan_unit_id(+) = p_ssu_id AND ptgqbvm.genetic_worth_calc_ind(+) = 'Y' AND ptgqcvg.parent_tree_id(+) = pt.parent_tree_id AND ptgqcvg.genetic_type_code(+) = 'CV' AND ptgqcvg.genetic_worth_code(+) = 'G' AND ptgqcvg.seed_plan_unit_id(+) = p_ssu_id AND ptgqcvg.genetic_worth_calc_ind(+) = 'Y' AND ptgqcvd.parent_tree_id(+) = pt.parent_tree_id AND ptgqcvd.genetic_type_code(+) = 'CV' AND ptgqcvd.genetic_worth_code(+) = 'D' AND ptgqcvd.seed_plan_unit_id(+) = p_ssu_id AND ptgqcvd.genetic_worth_calc_ind(+) = 'Y' AND ptgqcvr.parent_tree_id(+) = pt.parent_tree_id AND ptgqcvr.genetic_type_code(+) = 'CV' AND ptgqcvr.genetic_worth_code(+) = 'R' AND ptgqcvr.seed_plan_unit_id(+) = p_ssu_id AND ptgqcvr.genetic_worth_calc_ind(+) = 'Y' AND ptgqcvm.parent_tree_id(+) = pt.parent_tree_id AND ptgqcvm.genetic_type_code(+) = 'CV' AND ptgqcvm.genetic_worth_code(+) = 'M' AND ptgqcvm.seed_plan_unit_id(+) = p_ssu_id AND ptgqcvm.genetic_worth_calc_ind(+) = 'Y' AND pt.parent_tree_id = p_pt_id; r_gq c_gq%ROWTYPE; -- cursor to get the genetic quality values saved in smp mix CURSOR c_smgq (p_pt_id IN NUMBER ,p_sl_num IN VARCHAR2) IS SELECT pt.parent_tree_id , pt.parent_tree_number , smgqbvg.genetic_quality_value bv_g , smgqbvd.genetic_quality_value bv_d , smgqbvr.genetic_quality_value bv_r , smgqbvm.genetic_quality_value bv_m , smgqcvg.genetic_quality_value cv_g , smgqcvd.genetic_quality_value cv_d , smgqcvr.genetic_quality_value cv_r , smgqcvm.genetic_quality_value cv_m , NVL(smgqbvg.estimated_ind, smgqcvg.estimated_ind) g_estimated_ind , NVL(smgqbvd.estimated_ind, smgqcvd.estimated_ind) d_estimated_ind , NVL(smgqbvr.estimated_ind, smgqcvr.estimated_ind) r_estimated_ind , NVL(smgqbvm.estimated_ind, smgqcvm.estimated_ind) m_estimated_ind --If Parent Tree nor Female Parent have geog - set ind to 'Y' , CASE WHEN NVL(pt.elevation,0) > 0 OR NVL(fem_pt.elevation,0) > 0 THEN 'N' ELSE 'Y' END geography_error_ind FROM SMP_MIX_GEN_QLTY smgqbvg , SMP_MIX_GEN_QLTY smgqbvd , SMP_MIX_GEN_QLTY smgqbvr , SMP_MIX_GEN_QLTY smgqbvm , SMP_MIX_GEN_QLTY smgqcvg , SMP_MIX_GEN_QLTY smgqcvd , SMP_MIX_GEN_QLTY smgqcvr , SMP_MIX_GEN_QLTY smgqcvm , PARENT_TREE pt , PARENT_TREE fem_pt WHERE pt.parent_tree_reg_status_code = 'APP' AND fem_pt.parent_tree_id(+) = pt.female_parent_parent_tree_id AND smgqbvg.parent_tree_id(+) = pt.parent_tree_id AND smgqbvg.genetic_type_code(+) = 'BV' AND smgqbvg.genetic_worth_code(+) = 'G' AND smgqbvg.seedlot_number(+) = p_sl_num AND smgqbvd.parent_tree_id(+) = pt.parent_tree_id AND smgqbvd.genetic_type_code(+) = 'BV' AND smgqbvd.genetic_worth_code(+) = 'D' AND smgqbvd.seedlot_number(+) = p_sl_num AND smgqbvr.parent_tree_id(+) = pt.parent_tree_id AND smgqbvr.genetic_type_code(+) = 'BV' AND smgqbvr.genetic_worth_code(+) = 'R' AND smgqbvr.seedlot_number(+) = p_sl_num AND smgqbvm.parent_tree_id(+) = pt.parent_tree_id AND smgqbvm.genetic_type_code(+) = 'BV' AND smgqbvm.genetic_worth_code(+) = 'M' AND smgqbvm.seedlot_number(+) = p_sl_num AND smgqcvg.parent_tree_id(+) = pt.parent_tree_id AND smgqcvg.genetic_type_code(+) = 'CV' AND smgqcvg.genetic_worth_code(+) = 'G' AND smgqcvg.seedlot_number(+) = p_sl_num AND smgqcvd.parent_tree_id(+) = pt.parent_tree_id AND smgqcvd.genetic_type_code(+) = 'CV' AND smgqcvd.genetic_worth_code(+) = 'D' AND smgqcvd.seedlot_number(+) = p_sl_num AND smgqcvr.parent_tree_id(+) = pt.parent_tree_id AND smgqcvr.genetic_type_code(+) = 'CV' AND smgqcvr.genetic_worth_code(+) = 'R' AND smgqcvr.seedlot_number(+) = p_sl_num AND smgqcvm.parent_tree_id(+) = pt.parent_tree_id AND smgqcvm.genetic_type_code(+) = 'CV' AND smgqcvm.genetic_worth_code(+) = 'M' AND smgqcvm.seedlot_number(+) = p_sl_num AND pt.parent_tree_id = p_pt_id; r_smgq c_smgq%ROWTYPE; CONST_DEFAULT_UNTESTED_GW_G CONSTANT NUMBER(1) := 2; CONST_DEFAULT_UNTESTED_GW_D CONSTANT NUMBER(1) := 0; CONST_DEFAULT_UNTESTED_GW_R CONSTANT NUMBER(1) := 0; CONST_DEFAULT_UNTESTED_GW_M CONSTANT NUMBER(1) := 0; BEGIN IF p_get_latest_gq_ind = 'Y' OR p_revision_count IS NULL THEN OPEN c_gq(p_parent_tree_id ,p_seed_plan_unit_id); FETCH c_gq INTO r_gq; CLOSE c_gq; p_bv_g := r_gq.bv_g; p_bv_d := r_gq.bv_d; p_bv_r := r_gq.bv_r; p_bv_m := r_gq.bv_m; p_cv_g := r_gq.cv_g; p_cv_d := r_gq.cv_d; p_cv_r := r_gq.cv_r; p_cv_m := r_gq.cv_m; p_geography_error_ind := r_gq.geography_error_ind; ELSE OPEN c_smgq(p_parent_tree_id ,p_seedlot_number); FETCH c_smgq INTO r_smgq; CLOSE c_smgq; p_bv_g := r_smgq.bv_g; p_bv_d := r_smgq.bv_d; p_bv_r := r_smgq.bv_r; p_bv_m := r_smgq.bv_m; p_cv_g := r_smgq.cv_g; p_cv_d := r_smgq.cv_d; p_cv_r := r_smgq.cv_r; p_cv_m := r_smgq.cv_m; p_geography_error_ind := r_smgq.geography_error_ind; p_g_estimated_ind := r_smgq.g_estimated_ind; p_d_estimated_ind := r_smgq.d_estimated_ind; p_r_estimated_ind := r_smgq.r_estimated_ind; p_m_estimated_ind := r_smgq.m_estimated_ind; END IF; IF p_bv_g IS NULL THEN p_bv_g := CONST_DEFAULT_UNTESTED_GW_G; p_g_estimated_ind := 'Y'; ELSIF p_g_estimated_ind IS NULL THEN p_g_estimated_ind := 'N'; END IF; IF p_bv_d IS NULL THEN p_bv_d := CONST_DEFAULT_UNTESTED_GW_D; p_d_estimated_ind := 'Y'; ELSIF p_d_estimated_ind IS NULL THEN p_d_estimated_ind := 'N'; END IF; IF p_bv_r IS NULL THEN p_bv_r := CONST_DEFAULT_UNTESTED_GW_R; p_r_estimated_ind := 'Y'; ELSIF p_r_estimated_ind IS NULL THEN p_r_estimated_ind := 'N'; END IF; IF p_bv_m IS NULL THEN p_bv_m := CONST_DEFAULT_UNTESTED_GW_M; p_m_estimated_ind := 'Y'; ELSIF p_m_estimated_ind IS NULL THEN p_m_estimated_ind := 'N'; END IF; spr_get_pt_geog(p_parent_tree_id ,p_elevation ,p_latitude_degrees ,p_latitude_minutes ,p_latitude_seconds ,p_longitude_degrees ,p_longitude_minutes ,p_longitude_seconds); EXCEPTION WHEN OTHERS THEN P_ERROR_MESSAGE := P_ERROR_MESSAGE || 'spar.web.usr.database.unexpected:SPR_001A_SMP_CALCULATION,POPULATE_PARENT_TREE,'||SQLCODE||','||SQLERRM||';'; END populate_parent_tree; /****************************************************************************** Procedure: recalc Purpose: Recalculate derived Seedlot information based on Parent Trees and send back to interface but DO NOT perform save. ******************************************************************************/ PROCEDURE recalc ( --seedlot information p_seedlot_number IN VARCHAR2 ,p_seedlot_status_code IN VARCHAR2 ,p_vegetation_code IN VARCHAR2 --Orchard information ,p_orchard_id IN VARCHAR2 ,p_secondary_orchard_id IN VARCHAR2 ,p_get_latest_gq_ind IN VARCHAR2 --Area of use ,p_seed_plan_unit_id IN VARCHAR2 --summary info ,p_smp_parents_outside IN OUT VARCHAR2 ,p_smp_mix_bv_g IN OUT VARCHAR2 ,p_smp_mix_bv_d IN OUT VARCHAR2 ,p_smp_mix_bv_r IN OUT VARCHAR2 ,p_smp_mix_bv_m IN OUT VARCHAR2 ,p_smp_mix_elevation IN OUT VARCHAR2 ,p_smp_mix_latitude_degrees IN OUT VARCHAR2 ,p_smp_mix_latitude_minutes IN OUT VARCHAR2 ,p_smp_mix_longitude_degrees IN OUT VARCHAR2 ,p_smp_mix_longitude_minutes IN OUT VARCHAR2 ,p_error_message IN OUT VARCHAR2 ,p_pt IN OUT spr_001a_smp_object_varray) IS b_get_latest BOOLEAN; v_seedlot_number NUMBER; v_total_amount_of_material NUMBER := 0; v_num_parents_from_outside NUMBER := 0; v_coll_lat NUMBER := 0; v_coll_long NUMBER := 0; v_sum_wtd_bv_g seedlot_parent_tree.SMP_MIX_BV_GROWTH%TYPE := 0; v_sum_wtd_bv_d seedlot_parent_tree.SMP_MIX_BV_DENSITY%TYPE := 0; v_sum_wtd_bv_r seedlot_parent_tree.SMP_MIX_BV_RESISTANCE%TYPE := 0; v_sum_wtd_bv_m seedlot_parent_tree.SMP_MIX_BV_MAJOR_GENE%TYPE := 0; v_sum_wtd_elev seedlot_parent_tree.SMP_MIX_ELEVATION%TYPE := 0; v_sum_wtd_lat NUMBER := 0; v_sum_wtd_long NUMBER := 0; BEGIN -- This procedure assumes that the array already has all parent tree info populated in it. IF p_pt IS NOT NULL THEN -- First loop through list to get total amount of material and # of pt outside of seedlot. FOR i IN p_pt.FIRST..p_pt.LAST LOOP IF p_pt(i).amount_of_material IS NOT NULL THEN v_total_amount_of_material := v_total_amount_of_material + p_pt(i).amount_of_material; BEGIN SELECT SEEDLOT_NUMBER INTO v_seedlot_number FROM SEEDLOT_PARENT_TREE WHERE SEEDLOT_NUMBER = p_seedlot_number AND PARENT_TREE_ID = p_pt(i).parent_tree_id; EXCEPTION WHEN NO_DATA_FOUND THEN v_seedlot_number := NULL; END; IF v_seedlot_number IS NULL THEN v_num_parents_from_outside := v_num_parents_from_outside + 1; END IF; END IF; END LOOP; -- Second loop through list to calculate proportion and weight values. FOR i IN p_pt.FIRST..p_pt.LAST LOOP --rr_debugger('-----------------------'); --rr_debugger('pt num: '||p_pt(i).PARENT_TREE_NUMBER); IF p_pt(i).amount_of_material IS NOT NULL THEN p_pt(i).proportion := NVL(p_pt(i).amount_of_material,0) / NVL(v_total_amount_of_material,1); p_pt(i).weighted_bv_g := p_pt(i).proportion * NVL(p_pt(i).bv_g, p_pt(i).cv_g); p_pt(i).weighted_bv_d := p_pt(i).proportion * NVL(p_pt(i).bv_d, p_pt(i).cv_d); p_pt(i).weighted_bv_r := p_pt(i).proportion * NVL(p_pt(i).bv_r, p_pt(i).cv_r); p_pt(i).weighted_bv_m := p_pt(i).proportion * NVL(p_pt(i).bv_m, p_pt(i).cv_m); p_pt(i).weighted_elevation := p_pt(i).proportion * NVL(p_pt(i).elevation, 0); --rr_debugger('proportion: '||p_pt(i).proportion); --rr_debugger('wtd bv-g: '||p_pt(i).weighted_bv_g); --rr_debugger('wtd bv-d: '||p_pt(i).weighted_bv_d); --rr_debugger('wtd bv-r: '||p_pt(i).weighted_bv_r); --rr_debugger('wtd bv-m: '||p_pt(i).weighted_bv_m); --rr_debugger('wtd elev: '||p_pt(i).weighted_elevation); v_coll_lat := (p_pt(i).latitude_degrees*60) + p_pt(i).latitude_minutes + (p_pt(i).latitude_seconds/60); v_coll_long := (p_pt(i).longitude_degrees*60) + p_pt(i).longitude_minutes + (p_pt(i).longitude_seconds/60); --rr_debugger('coll lat: '||v_coll_lat); --rr_debugger('coll long: '||v_coll_long); p_pt(i).weighted_latitude := p_pt(i).proportion * NVL(v_coll_lat, 0); p_pt(i).weighted_longitude := p_pt(i).proportion * NVL(v_coll_long, 0); v_sum_wtd_bv_g := v_sum_wtd_bv_g + p_pt(i).weighted_bv_g; v_sum_wtd_bv_d := v_sum_wtd_bv_d + p_pt(i).weighted_bv_d; v_sum_wtd_bv_r := v_sum_wtd_bv_r + p_pt(i).weighted_bv_r; v_sum_wtd_bv_m := v_sum_wtd_bv_m + p_pt(i).weighted_bv_m; v_sum_wtd_elev := v_sum_wtd_elev + p_pt(i).weighted_elevation; v_sum_wtd_lat := v_sum_wtd_lat + p_pt(i).weighted_latitude; v_sum_wtd_long := v_sum_wtd_long + p_pt(i).weighted_longitude; v_coll_lat := 0; v_coll_long := 0; END IF; END LOOP; -- set smp mix calculated values. p_smp_mix_bv_g := v_sum_wtd_bv_g; p_smp_mix_bv_d := v_sum_wtd_bv_d; p_smp_mix_bv_r := v_sum_wtd_bv_r; p_smp_mix_bv_m := v_sum_wtd_bv_m; p_smp_mix_elevation := v_sum_wtd_elev; p_smp_mix_latitude_degrees := TRUNC(v_sum_wtd_lat/60); p_smp_mix_latitude_minutes := TRUNC(MOD(v_sum_wtd_lat,60)); p_smp_mix_longitude_degrees := TRUNC(v_sum_wtd_long/60); p_smp_mix_longitude_minutes := TRUNC(MOD(v_sum_wtd_long,60)); p_smp_parents_outside := v_num_parents_from_outside; --rr_debugger('smp bv-g: '||p_smp_mix_bv_g); --rr_debugger('smp bv-d: '||p_smp_mix_bv_d); --rr_debugger('smp bv-r: '||p_smp_mix_bv_r); --rr_debugger('smp bv-m: '||p_smp_mix_bv_m); --rr_debugger('smp elevation: '||p_smp_mix_elevation); --rr_debugger('smp lat deg: '||(v_sum_wtd_lat/60)); --rr_debugger('smp lat min: '||(MOD(v_sum_wtd_lat,60))); --rr_debugger('smp long deg: '||(v_sum_wtd_long/60)); --rr_debugger('smp long min: '||(MOD(v_sum_wtd_long,60))); --rr_debugger('smp pt outside: '||p_smp_parents_outside); END IF; EXCEPTION WHEN COLLECTION_IS_NULL THEN NULL; WHEN OTHERS THEN P_ERROR_MESSAGE := P_ERROR_MESSAGE || 'spar.web.usr.database.unexpected:SPR_001A_SMP_CALCULATION,RECALC,'||SQLCODE||','||SQLERRM||';'; END recalc; /****************************************************************************** Procedure: LOGICAL_DELETE Purpose: Given a seedlot_number and comma delimited list of Parent Tree Ids, deletes rows not found in the list. ******************************************************************************/ PROCEDURE logical_delete ( p_seedlot_number IN VARCHAR2 ,p_smp_comma_delim IN VARCHAR2) IS v_list VARCHAR2(1000); CURSOR cur_smp IS SELECT parent_tree_id FROM smp_mix WHERE seedlot_number = p_seedlot_number AND INSTR(v_list,','|| TO_CHAR(parent_tree_id) ||',') = 0; r_smp cur_smp%ROWTYPE; BEGIN --Turn "A,B,C" into ",A,B,C," v_list := ','||p_smp_comma_delim||','; OPEN cur_smp; FETCH cur_smp INTO r_smp; WHILE cur_smp%FOUND LOOP DELETE FROM smp_mix_gen_qlty WHERE seedlot_number = p_seedlot_number AND parent_tree_id = r_smp.parent_tree_id; DELETE FROM smp_mix WHERE seedlot_number = p_seedlot_number AND parent_tree_id = r_smp.parent_tree_id; FETCH cur_smp INTO r_smp; END LOOP; END logical_delete; /****************************************************************************** Procedure: get_smp_mix Purpose: Retrieve SMP_MIX and SMP_MIX_GEN_QLTY (cursor) info ******************************************************************************/ PROCEDURE get_smp_mix ( p_seedlot_number IN VARCHAR2 ,p_seed_plan_unit_id IN VARCHAR2 ,p_get_latest_gq_ind IN VARCHAR2 ,p_error_message IN OUT VARCHAR2 ,p_result IN OUT spr_001a_smp_object_varray) IS CURSOR c_pt IS SELECT pt.parent_tree_id , pt.parent_tree_number , sm.amount_of_material , sm.revision_count FROM SMP_MIX sm , PARENT_TREE pt WHERE pt.parent_tree_id = sm.parent_tree_id AND sm.seedlot_number = p_seedlot_number ORDER BY TO_NUMBER(parent_tree_number); r_pt c_pt%ROWTYPE; v_smp_pt_element spr_001a_smp_object; v_empty_result spr_001a_smp_object_varray; r_contrib c_pt%ROWTYPE; b_bv_selected BOOLEAN := FALSE; b_cv_selected BOOLEAN := FALSE; v_bv_g SMP_MIX_GEN_QLTY.GENETIC_QUALITY_VALUE%TYPE; v_bv_d SMP_MIX_GEN_QLTY.GENETIC_QUALITY_VALUE%TYPE; v_bv_r SMP_MIX_GEN_QLTY.GENETIC_QUALITY_VALUE%TYPE; v_bv_m SMP_MIX_GEN_QLTY.GENETIC_QUALITY_VALUE%TYPE; v_cv_g SMP_MIX_GEN_QLTY.GENETIC_QUALITY_VALUE%TYPE; v_cv_d SMP_MIX_GEN_QLTY.GENETIC_QUALITY_VALUE%TYPE; v_cv_r SMP_MIX_GEN_QLTY.GENETIC_QUALITY_VALUE%TYPE; v_cv_m SMP_MIX_GEN_QLTY.GENETIC_QUALITY_VALUE%TYPE; v_g_estimated_ind SMP_MIX_GEN_QLTY.ESTIMATED_IND%TYPE; v_d_estimated_ind SMP_MIX_GEN_QLTY.ESTIMATED_IND%TYPE; v_r_estimated_ind SMP_MIX_GEN_QLTY.ESTIMATED_IND%TYPE; v_m_estimated_ind SMP_MIX_GEN_QLTY.ESTIMATED_IND%TYPE; v_elevation PARENT_TREE.ELEVATION%TYPE; v_latitude_degrees PARENT_TREE.LATITUDE_DEGREES%TYPE; v_latitude_minutes PARENT_TREE.LATITUDE_MINUTES%TYPE; v_latitude_seconds PARENT_TREE.LATITUDE_SECONDS%TYPE; v_longitude_degrees PARENT_TREE.LONGITUDE_DEGREES%TYPE; v_longitude_minutes PARENT_TREE.LONGITUDE_MINUTES%TYPE; v_longitude_seconds PARENT_TREE.LONGITUDE_SECONDS%TYPE; v_geography_error_ind VARCHAR2(1); v_proportion NUMBER; v_weighted_bv_g NUMBER; v_weighted_bv_d NUMBER; v_weighted_bv_r NUMBER; v_weighted_bv_m NUMBER; v_weighted_elevation NUMBER; v_weighted_latitude NUMBER; v_weighted_longitude NUMBER; BEGIN --Init array by setting it to an empty array of same type p_result := v_empty_result; OPEN c_pt; FETCH c_pt INTO r_pt; WHILE c_pt%FOUND LOOP r_contrib.parent_tree_id := r_pt.parent_tree_id; r_contrib.parent_tree_number := r_pt.parent_tree_number; r_contrib.amount_of_material := r_pt.amount_of_material; r_contrib.revision_count := r_pt.revision_count; populate_parent_tree(r_contrib.parent_tree_id ,p_seedlot_number ,p_seed_plan_unit_id ,p_get_latest_gq_ind ,v_bv_g ,v_bv_d ,v_bv_r ,v_bv_m ,v_cv_g ,v_cv_d ,v_cv_r ,v_cv_m ,v_g_estimated_ind ,v_d_estimated_ind ,v_r_estimated_ind ,v_m_estimated_ind ,v_elevation ,v_latitude_degrees ,v_latitude_minutes ,v_latitude_seconds ,v_longitude_degrees ,v_longitude_minutes ,v_longitude_seconds ,v_geography_error_ind ,r_contrib.revision_count ,p_error_message); v_smp_pt_element := spr_001a_smp_object(r_contrib.parent_tree_id , r_contrib.parent_tree_number , v_g_estimated_ind , v_d_estimated_ind , v_r_estimated_ind , v_m_estimated_ind , 'N' , r_contrib.amount_of_material , v_proportion , v_bv_g, v_bv_d, v_bv_r, v_bv_m , v_cv_g, v_cv_d, v_cv_r, v_cv_m , v_elevation , v_latitude_degrees , v_latitude_minutes , v_latitude_seconds , v_longitude_degrees , v_longitude_minutes , v_longitude_seconds , v_geography_error_ind , v_weighted_bv_g , v_weighted_bv_d , v_weighted_bv_r , v_weighted_bv_m , v_weighted_elevation , v_weighted_latitude , v_weighted_longitude , r_contrib.revision_count); IF p_result IS NULL OR p_result.COUNT = 0 THEN p_result := spr_001a_smp_object_varray(v_smp_pt_element); ELSE p_result.EXTEND; p_result(p_result.COUNT) := v_smp_pt_element; END IF; FETCH c_pt INTO r_pt; v_smp_pt_element := NULL; r_contrib:= NULL; v_proportion := NULL; v_g_estimated_ind := NULL; v_d_estimated_ind := NULL; v_r_estimated_ind := NULL; v_m_estimated_ind := NULL; v_bv_g := NULL; v_bv_d := NULL; v_bv_r := NULL; v_bv_m := NULL; v_cv_g := NULL; v_cv_d := NULL; v_cv_r := NULL; v_cv_m := NULL; v_elevation := NULL; v_latitude_degrees := NULL; v_latitude_minutes := NULL; v_latitude_seconds := NULL; v_longitude_degrees := NULL; v_longitude_minutes := NULL; v_longitude_seconds := NULL; v_geography_error_ind := NULL; v_weighted_bv_g := NULL; v_weighted_bv_d := NULL; v_weighted_bv_r := NULL; v_weighted_bv_m := NULL; v_weighted_elevation := NULL; v_weighted_latitude := NULL; v_weighted_longitude := NULL; END LOOP; END get_smp_mix; /****************************************************************************** Procedure: get Purpose: Retrieve SEEDLOT, SMP_MIX and SMP_MIX_GEN_QLTY (cursor) info ******************************************************************************/ PROCEDURE get ( --seedlot information p_seedlot_number IN OUT VARCHAR2 ,p_approved_date IN OUT VARCHAR2 ,p_seedlot_status_code IN OUT VARCHAR2 ,p_vegetation_code IN OUT VARCHAR2 -- Orchard Information ,p_orchard_id IN OUT VARCHAR2 ,p_orchard_name IN OUT VARCHAR2 ,p_secondary_orchard_id IN OUT VARCHAR2 ,p_secondary_orchard_name IN OUT VARCHAR2 ,p_get_latest_gq_ind IN OUT VARCHAR2 --Area of use ,p_seed_plan_unit_id IN OUT NUMBER ,p_spu_name IN OUT VARCHAR2 ,p_spu_species IN OUT VARCHAR2 ,p_spu_spz IN OUT VARCHAR2 ,p_spu_elev IN OUT VARCHAR2 --summary info ,p_smp_parents_outside IN OUT VARCHAR2 ,p_smp_mix_bv_g IN OUT VARCHAR2 ,p_smp_mix_bv_d IN OUT VARCHAR2 ,p_smp_mix_bv_r IN OUT VARCHAR2 ,p_smp_mix_bv_m IN OUT VARCHAR2 ,p_smp_mix_elevation IN OUT VARCHAR2 ,p_smp_mix_latitude_degrees IN OUT VARCHAR2 ,p_smp_mix_latitude_minutes IN OUT VARCHAR2 ,p_smp_mix_longitude_degrees IN OUT VARCHAR2 ,p_smp_mix_longitude_minutes IN OUT VARCHAR2 --record info ,p_seedlot_revision_count IN OUT NUMBER --USER INFO ,p_user_type IN OUT VARCHAR2 ,p_update_userid IN OUT VARCHAR2 ,p_user_org_unit_no IN OUT NUMBER ,p_user_client_number IN OUT VARCHAR2 ,p_user_client_locn_code IN OUT VARCHAR2 ,p_error_message IN OUT VARCHAR2 ,p_result IN OUT spr_001a_smp_object_varray) IS BEGIN spr_seedlot.init(p_seedlot_number); spr_seedlot.get(p_user_client_number,p_user_org_unit_no); --Should never happen on this form IF spr_seedlot.get_revision_count IS NULL THEN RAISE NO_DATA_FOUND; END IF; p_seedlot_number := spr_seedlot.get_seedlot_number; p_approved_date := Sil_Date_Conversion.Convert_to_char(spr_seedlot.get_approved_timestamp); p_seedlot_status_code := spr_seedlot.get_seedlot_status_code; p_vegetation_code := spr_seedlot.get_vegetation_code; --Orchard Info p_orchard_id := spr_seedlot.get_orchard_id; p_secondary_orchard_id := spr_seedlot.get_secondary_orchard_id; --Area of use p_seed_plan_unit_id := spr_seedlot.get_seed_plan_unit_id; p_smp_parents_outside := spr_seedlot.get_smp_parents_outside; p_seedlot_revision_count := spr_seedlot.get_revision_count; --Get display items get_display_fields (p_seedlot_number ,p_seed_plan_unit_id ,p_spu_name ,p_spu_species ,p_spu_spz ,p_spu_elev ,p_orchard_id ,p_orchard_name ,p_secondary_orchard_id ,p_secondary_orchard_name ,p_error_message); -- populate p_result with smp_mix for seedlot get_smp_mix (p_seedlot_number ,spr_seedlot.get_seed_plan_unit_id ,p_get_latest_gq_ind ,p_error_message ,p_result); -- recalculates all derived values, and fills-out rest of smp parent tree info. recalc ( --seedlot information p_seedlot_number ,p_seedlot_status_code ,p_vegetation_code --Orchard Information ,p_orchard_id ,p_secondary_orchard_id ,p_get_latest_gq_ind --Area of use ,p_seed_plan_unit_id --summary info ,p_smp_parents_outside ,p_smp_mix_bv_g ,p_smp_mix_bv_d ,p_smp_mix_bv_r ,p_smp_mix_bv_m ,p_smp_mix_elevation ,p_smp_mix_latitude_degrees ,p_smp_mix_latitude_minutes ,p_smp_mix_longitude_degrees ,p_smp_mix_longitude_minutes ,p_error_message ,p_result); EXCEPTION WHEN NO_DATA_FOUND THEN p_error_message := p_error_message || 'spar.web.error.usr.seedlot_not_found;'; WHEN OTHERS THEN P_ERROR_MESSAGE := P_ERROR_MESSAGE || 'spar.web.usr.database.unexpected:SPR_001A_SMP_CALCULATION,GET,'||SQLCODE||','||SQLERRM||';'; END get; /****************************************************************************** Procedure: save_smp_gen_qlty Purpose: UPDATE or INSERT one SMP_MIX_GEN_QLTY row. ******************************************************************************/ PROCEDURE save_smp_gen_qlty ( p_seedlot_number IN VARCHAR2 , p_parent_tree_id IN NUMBER , p_genetic_type_code IN VARCHAR2 , p_genetic_worth_code IN VARCHAR2 , p_genetic_quality_value IN NUMBER , p_estimated_ind IN VARCHAR2) IS BEGIN UPDATE smp_mix_gen_qlty SET genetic_quality_value = p_genetic_quality_value , estimated_ind = p_estimated_ind , revision_count = revision_count + 1 WHERE seedlot_number = p_seedlot_number AND parent_tree_id = p_parent_tree_id AND genetic_type_code = p_genetic_type_code AND genetic_worth_code = p_genetic_worth_code; IF SQL%ROWCOUNT = 0 THEN INSERT INTO smp_mix_gen_qlty ( seedlot_number , parent_tree_id , genetic_type_code , genetic_worth_code , genetic_quality_value , estimated_ind , revision_count) VALUES ( p_seedlot_number , p_parent_tree_id , p_genetic_type_code , p_genetic_worth_code , p_genetic_quality_value , p_estimated_ind , 1); END IF; END save_smp_gen_qlty; /****************************************************************************** Procedure: save_smp_mix Purpose: UPDATE or INSERT one SMP_MIX row. ******************************************************************************/ PROCEDURE save_smp_mix ( p_seedlot_number IN VARCHAR2 , p_error_message IN OUT VARCHAR2 , p_result IN OUT spr_001a_smp_object_varray) IS BEGIN FOR i IN p_result.FIRST..p_result.LAST LOOP IF p_result(i).PARENT_TREE_ID IS NOT NULL THEN UPDATE smp_mix SET amount_of_material = p_result(i).amount_of_material , revision_count = revision_count + 1 WHERE seedlot_number = p_seedlot_number AND parent_tree_id = p_result(i).parent_tree_id; IF SQL%ROWCOUNT = 0 THEN INSERT INTO smp_mix ( seedlot_number , parent_tree_id , amount_of_material , revision_count) VALUES ( p_seedlot_number , p_result(i).parent_tree_id , p_result(i).amount_of_material , 1); END IF; IF p_result(i).bv_g IS NULL THEN DELETE FROM smp_mix_gen_qlty WHERE seedlot_number = p_seedlot_number AND parent_tree_id = p_result(i).parent_tree_id AND genetic_type_code = 'BV' AND genetic_worth_code = 'G'; ELSE save_smp_gen_qlty(p_seedlot_number ,p_result(i).parent_tree_id ,'BV' ,'G' ,p_result(i).bv_g ,p_result(i).g_estimated_ind); END IF; IF p_result(i).bv_d IS NULL THEN DELETE FROM smp_mix_gen_qlty WHERE seedlot_number = p_seedlot_number AND parent_tree_id = p_result(i).parent_tree_id AND genetic_type_code = 'BV' AND genetic_worth_code = 'D'; ELSE save_smp_gen_qlty(p_seedlot_number ,p_result(i).parent_tree_id ,'BV' ,'D' ,p_result(i).bv_d ,p_result(i).d_estimated_ind); END IF; IF p_result(i).bv_r IS NULL THEN DELETE FROM smp_mix_gen_qlty WHERE seedlot_number = p_seedlot_number AND parent_tree_id = p_result(i).parent_tree_id AND genetic_type_code = 'BV' AND genetic_worth_code = 'R'; ELSE save_smp_gen_qlty(p_seedlot_number ,p_result(i).parent_tree_id ,'BV' ,'R' ,p_result(i).bv_r ,p_result(i).r_estimated_ind); END IF; IF p_result(i).bv_m IS NULL THEN DELETE FROM smp_mix_gen_qlty WHERE seedlot_number = p_seedlot_number AND parent_tree_id = p_result(i).parent_tree_id AND genetic_type_code = 'BV' AND genetic_worth_code = 'M'; ELSE save_smp_gen_qlty(p_seedlot_number ,p_result(i).parent_tree_id ,'BV' ,'M' ,p_result(i).bv_m ,p_result(i).m_estimated_ind); END IF; IF p_result(i).cv_g IS NULL THEN DELETE FROM smp_mix_gen_qlty WHERE seedlot_number = p_seedlot_number AND parent_tree_id = p_result(i).parent_tree_id AND genetic_type_code = 'CV' AND genetic_worth_code = 'G'; ELSE save_smp_gen_qlty(p_seedlot_number ,p_result(i).parent_tree_id ,'CV' ,'G' ,p_result(i).cv_g ,p_result(i).g_estimated_ind); END IF; IF p_result(i).cv_d IS NULL THEN DELETE FROM smp_mix_gen_qlty WHERE seedlot_number = p_seedlot_number AND parent_tree_id = p_result(i).parent_tree_id AND genetic_type_code = 'CV' AND genetic_worth_code = 'D'; ELSE save_smp_gen_qlty(p_seedlot_number ,p_result(i).parent_tree_id ,'CV' ,'D' ,p_result(i).cv_d ,p_result(i).d_estimated_ind); END IF; IF p_result(i).cv_r IS NULL THEN DELETE FROM smp_mix_gen_qlty WHERE seedlot_number = p_seedlot_number AND parent_tree_id = p_result(i).parent_tree_id AND genetic_type_code = 'CV' AND genetic_worth_code = 'R'; ELSE save_smp_gen_qlty(p_seedlot_number ,p_result(i).parent_tree_id ,'CV' ,'R' ,p_result(i).cv_r ,p_result(i).r_estimated_ind); END IF; IF p_result(i).cv_m IS NULL THEN DELETE FROM smp_mix_gen_qlty WHERE seedlot_number = p_seedlot_number AND parent_tree_id = p_result(i).parent_tree_id AND genetic_type_code = 'CV' AND genetic_worth_code = 'M'; ELSE save_smp_gen_qlty(p_seedlot_number ,p_result(i).parent_tree_id ,'CV' ,'M' ,p_result(i).cv_m ,p_result(i).m_estimated_ind); END IF; END IF; END LOOP; END save_smp_mix; /****************************************************************************** Procedure: validate Purpose: Iterate through list of parent trees and validate that the parent trees are valid for SMP Calculation, and populate with values. ******************************************************************************/ PROCEDURE validate ( --seedlot information p_seedlot_number IN VARCHAR2 ,p_vegetation_code IN VARCHAR2 ,p_seed_plan_unit_id IN VARCHAR ,p_get_latest_gq_ind IN VARCHAR2 ,p_error_message IN OUT VARCHAR2 ,p_result IN OUT spr_001a_smp_object_varray) IS -- cursor to get the parent tree id based on pt number and species. CURSOR c_parent_tree (p_parent_tree_number IN VARCHAR2 , p_vegetation_code IN VARCHAR2) IS SELECT PARENT_TREE_ID ,DECODE(TESTED_IND, 'Y', 'N', 'Y') UNTESTED_IND ,ACTIVE_IND ,PARENT_TREE_REG_STATUS_CODE FROM PARENT_TREE WHERE PARENT_TREE_NUMBER = p_parent_tree_number AND VEGETATION_CODE = p_vegetation_code; r_parent_tree c_parent_tree%ROWTYPE; BEGIN IF p_result IS NOT NULL THEN FOR i IN p_result.FIRST..p_result.LAST LOOP IF p_result(i).PARENT_TREE_NUMBER IS NOT NULL THEN --Get the parent tree id based off of pt number and vegetation code OPEN c_parent_tree(p_result(i).parent_tree_number ,p_vegetation_code); FETCH c_parent_tree INTO r_parent_tree; CLOSE c_parent_tree; p_result(i).PARENT_TREE_ID := r_parent_tree.PARENT_TREE_ID; p_result(i).UNTESTED_IND := r_parent_tree.UNTESTED_IND; IF p_result(i).parent_tree_id IS NULL THEN p_error_message := p_error_message || 'spar.web.error.usr.field.code.invalid:Parent Tree '||p_result(i).PARENT_TREE_NUMBER||',species in SMP Mix;'; ELSIF r_parent_tree.UNTESTED_IND = 'Y' OR r_parent_tree.ACTIVE_IND = 'N' THEN p_error_message := p_error_message || 'spar.web.error.usr.spr001a.parentTreeInformationValidationWrapper: selected for SMP Mix is inactive or untested,'||p_result(i).PARENT_TREE_NUMBER||';'; ELSE populate_parent_tree(p_result(i).parent_tree_id ,p_seedlot_number ,p_seed_plan_unit_id ,p_get_latest_gq_ind ,p_result(i).bv_g ,p_result(i).bv_d ,p_result(i).bv_r ,p_result(i).bv_m ,p_result(i).cv_g ,p_result(i).cv_d ,p_result(i).cv_r ,p_result(i).cv_m ,p_result(i).g_estimated_ind ,p_result(i).d_estimated_ind ,p_result(i).r_estimated_ind ,p_result(i).m_estimated_ind ,p_result(i).elevation ,p_result(i).latitude_degrees ,p_result(i).latitude_minutes ,p_result(i).latitude_seconds ,p_result(i).longitude_degrees ,p_result(i).longitude_minutes ,p_result(i).longitude_seconds ,p_result(i).geography_error_ind ,p_result(i).revision_count ,p_error_message); END IF; END IF; END LOOP; END IF; EXCEPTION WHEN COLLECTION_IS_NULL THEN NULL; WHEN OTHERS THEN P_ERROR_MESSAGE := P_ERROR_MESSAGE || 'spar.web.usr.database.unexpected:SPR_001A_SMP_CALCULATION,VALIDATE,'||SQLCODE||','||SQLERRM||';'; END validate; /****************************************************************************** Procedure: save Purpose: Save SEEDLOT SMP MIX information ******************************************************************************/ PROCEDURE save ( --seedlot information p_seedlot_number IN VARCHAR2 ,p_seedlot_status_code IN VARCHAR2 ,p_vegetation_code IN VARCHAR2 --Orchard information ,p_orchard_id IN VARCHAR2 ,p_secondary_orchard_id IN VARCHAR2 ,p_get_latest_gq_ind IN VARCHAR2 --Area of use ,p_seed_plan_unit_id IN VARCHAR2 --summary info ,p_smp_parents_outside IN OUT VARCHAR2 ,p_smp_mix_bv_g IN OUT VARCHAR2 ,p_smp_mix_bv_d IN OUT VARCHAR2 ,p_smp_mix_bv_r IN OUT VARCHAR2 ,p_smp_mix_bv_m IN OUT VARCHAR2 ,p_smp_mix_elevation IN OUT VARCHAR2 ,p_smp_mix_latitude_degrees IN OUT VARCHAR2 ,p_smp_mix_latitude_minutes IN OUT VARCHAR2 ,p_smp_mix_longitude_degrees IN OUT VARCHAR2 ,p_smp_mix_longitude_minutes IN OUT VARCHAR2 --record info ,p_seedlot_revision_count IN OUT VARCHAR2 --USER INFO ,p_update_userid IN OUT VARCHAR2 ,p_user_org_unit_no IN OUT VARCHAR2 ,p_user_client_number IN OUT VARCHAR2 ,p_user_client_locn_code IN OUT VARCHAR2 ,p_error_message IN OUT VARCHAR2 ,p_result IN OUT spr_001a_smp_object_varray) IS e_update_failed EXCEPTION; v_result spr_001a_smp_object_varray; e_seedlot_error_raised EXCEPTION; v_parent_tree_list VARCHAR2(1000); BEGIN --Setup seedlot for save spr_seedlot.init(p_seedlot_number); spr_seedlot.get(p_user_client_number,p_user_org_unit_no); --Should never happen on this form IF spr_seedlot.get_revision_count IS NULL THEN RAISE NO_DATA_FOUND; END IF; IF p_result IS NOT NULL AND p_result.COUNT > 0 THEN validate ( --seedlot information p_seedlot_number ,spr_seedlot.get_vegetation_code ,spr_seedlot.get_seed_plan_unit_id ,p_get_latest_gq_ind ,p_error_message ,p_result); recalc ( --seedlot information p_seedlot_number ,p_seedlot_status_code ,p_vegetation_code --Orchard Information ,p_orchard_id ,p_secondary_orchard_id ,p_get_latest_gq_ind --Area of use ,p_seed_plan_unit_id --summary info ,p_smp_parents_outside ,p_smp_mix_bv_g ,p_smp_mix_bv_d ,p_smp_mix_bv_r ,p_smp_mix_bv_m ,p_smp_mix_elevation ,p_smp_mix_latitude_degrees ,p_smp_mix_latitude_minutes ,p_smp_mix_longitude_degrees ,p_smp_mix_longitude_minutes ,p_error_message ,p_result); spr_seedlot.set_smp_parents_outside(p_smp_parents_outside); --record info spr_seedlot.set_revision_count(p_seedlot_revision_count); spr_seedlot.set_update_userid(p_update_userid); --Perform update spr_seedlot.change; IF spr_seedlot.error_raised THEN RAISE e_seedlot_error_raised; END IF; p_seedlot_revision_count := spr_seedlot.get_revision_count; save_smp_mix(p_seedlot_number ,p_error_message ,p_result); FOR i IN p_result.FIRST..p_result.LAST LOOP IF p_result(i).PARENT_TREE_ID IS NOT NULL THEN v_parent_tree_list := v_parent_tree_list || p_result(i).PARENT_TREE_ID || ','; END IF; END LOOP; END IF; logical_delete(p_seedlot_number , v_parent_tree_list); EXCEPTION WHEN e_update_failed THEN p_error_message := p_error_message || 'spar.web.error.usr.database.record.modified:Seedlot Parent Tree;'; WHEN e_seedlot_error_raised THEN p_error_message := p_error_message || spr_seedlot.get_error_message; WHEN COLLECTION_IS_NULL THEN NULL; WHEN OTHERS THEN P_ERROR_MESSAGE := P_ERROR_MESSAGE || 'spar.web.usr.database.unexpected:SPR_001A_SMP_CALCULATION,SAVE,'||SQLCODE||','||SQLERRM||';'; END save; /****************************************************************************** Procedure: recalculate Purpose: Recalculate the SMP Mix used by packages to update and possibly save new calculations. ******************************************************************************/ PROCEDURE recalculate ( p_seedlot_number IN VARCHAR2 ,p_seedlot_status_code IN VARCHAR2 ,p_vegetation_code IN VARCHAR2 --Orchard information ,p_orchard_id IN VARCHAR2 ,p_secondary_orchard_id IN VARCHAR2 --Area of use ,p_seed_plan_unit_id IN VARCHAR2 ,p_get_latest_gq_ind IN VARCHAR2 ,p_save_recalculation_ind IN VARCHAR2 --summary info ,p_smp_parents_outside IN OUT VARCHAR2 ,p_smp_mix_bv_g IN OUT VARCHAR2 ,p_smp_mix_bv_d IN OUT VARCHAR2 ,p_smp_mix_bv_r IN OUT VARCHAR2 ,p_smp_mix_bv_m IN OUT VARCHAR2 ,p_smp_mix_elevation IN OUT VARCHAR2 ,p_smp_mix_latitude_degrees IN OUT VARCHAR2 ,p_smp_mix_latitude_minutes IN OUT VARCHAR2 ,p_smp_mix_longitude_degrees IN OUT VARCHAR2 ,p_smp_mix_longitude_minutes IN OUT VARCHAR2 ,p_error_message IN OUT VARCHAR2) IS v_smp_mix spr_001a_smp_object_varray; BEGIN get_smp_mix ( p_seedlot_number ,p_seed_plan_unit_id ,p_get_latest_gq_ind ,p_error_message ,v_smp_mix); IF v_smp_mix IS NOT NULL AND v_smp_mix.COUNT > 0 THEN recalc ( p_seedlot_number ,p_seedlot_status_code ,p_vegetation_code ,p_orchard_id ,p_secondary_orchard_id ,p_get_latest_gq_ind ,p_seed_plan_unit_id --summary info ,p_smp_parents_outside ,p_smp_mix_bv_g ,p_smp_mix_bv_d ,p_smp_mix_bv_r ,p_smp_mix_bv_m ,p_smp_mix_elevation ,p_smp_mix_latitude_degrees ,p_smp_mix_latitude_minutes ,p_smp_mix_longitude_degrees ,p_smp_mix_longitude_minutes ,p_error_message ,v_smp_mix); IF p_save_recalculation_ind = 'Y' THEN save_smp_mix ( p_seedlot_number ,p_error_message ,v_smp_mix); END IF; END IF; END recalculate; /****************************************************************************** Procedure: mainline Purpose: Handle Java/Struts action. ******************************************************************************/ PROCEDURE mainline ( p_action IN OUT VARCHAR2 --seedlot information ,p_seedlot_number IN OUT VARCHAR2 ,p_approved_date IN OUT VARCHAR2 ,p_seedlot_status_code IN OUT VARCHAR2 ,p_vegetation_code IN OUT VARCHAR2 --Orchard information ,p_orchard_id IN OUT VARCHAR2 ,p_orchard_name IN OUT VARCHAR2 ,p_secondary_orchard_id IN OUT VARCHAR2 ,p_secondary_orchard_name IN OUT VARCHAR2 ,p_get_latest_gq_ind IN OUT VARCHAR2 --Area of use ,p_seed_plan_unit_id IN OUT VARCHAR2 ,p_spu_name IN OUT VARCHAR2 ,p_spu_species IN OUT VARCHAR2 ,p_spu_spz IN OUT VARCHAR2 ,p_spu_elev IN OUT VARCHAR2 --summary info ,p_smp_parents_outside IN OUT VARCHAR2 ,p_smp_mix_bv_g IN OUT VARCHAR2 ,p_smp_mix_bv_d IN OUT VARCHAR2 ,p_smp_mix_bv_r IN OUT VARCHAR2 ,p_smp_mix_bv_m IN OUT VARCHAR2 ,p_smp_mix_elevation IN OUT VARCHAR2 ,p_smp_mix_latitude_degrees IN OUT VARCHAR2 ,p_smp_mix_latitude_minutes IN OUT VARCHAR2 ,p_smp_mix_longitude_degrees IN OUT VARCHAR2 ,p_smp_mix_longitude_minutes IN OUT VARCHAR2 --record info ,p_seedlot_revision_count IN OUT VARCHAR2 --USER INFO ,p_user_type IN OUT VARCHAR2 ,p_update_userid IN OUT VARCHAR2 ,p_user_org_unit_no IN OUT VARCHAR2 ,p_user_client_number IN OUT VARCHAR2 ,p_user_client_locn_code IN OUT VARCHAR2 ,p_error_message IN OUT VARCHAR2 ,p_result IN OUT spr_001a_smp_object_varray) IS e_invalid_function_request EXCEPTION; v_seed_plan_unit_id seedlot.seed_plan_unit_id%TYPE; v_seedlot_revision_count seedlot.revision_count%TYPE; v_user_org_unit_no seedlot.org_unit_no%TYPE; BEGIN --Cast numeric parms v_seed_plan_unit_id := TO_NUMBER(p_seed_plan_unit_id); v_seedlot_revision_count := TO_NUMBER(p_seedlot_revision_count); v_user_org_unit_no := TO_NUMBER(p_user_org_unit_no); IF p_action = 'GET' THEN get ( --seedlot information p_seedlot_number ,p_approved_date ,p_seedlot_status_code ,p_vegetation_code --Orchard Information ,p_orchard_id ,p_orchard_name ,p_secondary_orchard_id ,p_secondary_orchard_name ,p_get_latest_gq_ind --Area of use ,v_seed_plan_unit_id ,p_spu_name ,p_spu_species ,p_spu_spz ,p_spu_elev --summary info ,p_smp_parents_outside ,p_smp_mix_bv_g ,p_smp_mix_bv_d ,p_smp_mix_bv_r ,p_smp_mix_bv_m ,p_smp_mix_elevation ,p_smp_mix_latitude_degrees ,p_smp_mix_latitude_minutes ,p_smp_mix_longitude_degrees ,p_smp_mix_longitude_minutes --record info ,v_seedlot_revision_count --USER INFO ,p_user_type ,p_update_userid ,v_user_org_unit_no ,p_user_client_number ,p_user_client_locn_code ,p_error_message ,p_result); ELSIF p_action = 'SAVE' THEN -- Validate, recalculate values, then save records. save ( --seedlot information p_seedlot_number ,p_seedlot_status_code ,p_vegetation_code --Orchard Information ,p_orchard_id ,p_secondary_orchard_id ,p_get_latest_gq_ind --Area of use ,v_seed_plan_unit_id --summary info ,p_smp_parents_outside ,p_smp_mix_bv_g ,p_smp_mix_bv_d ,p_smp_mix_bv_r ,p_smp_mix_bv_m ,p_smp_mix_elevation ,p_smp_mix_latitude_degrees ,p_smp_mix_latitude_minutes ,p_smp_mix_longitude_degrees ,p_smp_mix_longitude_minutes --record info ,v_seedlot_revision_count --USER INFO ,p_update_userid ,v_user_org_unit_no ,p_user_client_number ,p_user_client_locn_code ,p_error_message ,p_result); ELSE RAISE e_invalid_function_request; END IF; --Recast numeric parms p_seed_plan_unit_id := TO_CHAR(v_seed_plan_unit_id); p_seedlot_revision_count := TO_CHAR(v_seedlot_revision_count); p_user_org_unit_no := TO_CHAR(v_user_org_unit_no); EXCEPTION WHEN e_invalid_function_request THEN p_error_message := p_error_message || 'spar.web.usr.database.invalid.function:SPR_001A_SMP_CALCULATION,MAINLINE,'||p_action||';'; END mainline; END spr_001a_smp_calculation;