/****************************************************************************** Procedure: calc_pt_contrib Purpose: Recalculate Area of Use, GW, Ne, Collection geography. -All calculations taken from the 2004 version of the Seedlot Certification Template (Excel). -Excel treats empty cells as 0 for calculations so NVL(__,0) is used liberally. -CURRENTLY ONLY CALCULATES GW-G. ******************************************************************************/ PROCEDURE calc_pt_contrib ( p_pt IN OUT spr_001a_object_varray , p_get_current_tests IN BOOLEAN) IS v_contaminant_pollen_bv seedlot.contaminant_pollen_bv%TYPE; v_smp_parents_outside seedlot.smp_parents_outside%TYPE; --Abbreviations used: -- f_ female m_ male p_ parent -- contrib_ contribution poll_ pollen prop_ proportion -- wtd_ weighted --col:xx = Seedlot Certification Template (xls) reference .... BEGIN --Get current GQ values if required and load array from saved data if not passed load_array(p_pt,p_get_current_tests); --Contam pollen bv used in calc - use set value if it has been set, otherwise --use the previous value...in either case, convert null to 0 as Excel would IF gb_contaminant_pollen_bv = 'Y' THEN v_contaminant_pollen_bv := NVL(g_contaminant_pollen_bv,0); ELSE v_contaminant_pollen_bv := NVL(r_previous.contaminant_pollen_bv,0); END IF; --SMP Parents Outside used in calc - use set value if it has been set, otherwise --use the previous value...in either case, convert null to 0 as Excel would IF gb_smp_parents_outside = 'Y' THEN v_smp_parents_outside := NVL(g_smp_parents_outside,0); ELSE v_smp_parents_outside := NVL(r_previous.smp_parents_outside,0); END IF; --First pass to calculate simple sums used in row-based calcs --and to check if all bv d/r/m are estimated. FOR i IN 1..p_pt.COUNT LOOP v_total_cone_count := v_total_cone_count + NVL(p_pt(i).cone_count,0); v_total_pollen_count := v_total_pollen_count + NVL(p_pt(i).pollen_count,0); IF p_pt(i).D_ESTIMATED_IND <> 'Y' AND (p_pt(i).CONE_COUNT IS NOT NULL OR p_pt(i).POLLEN_COUNT IS NOT NULL) THEN b_bv_d_not_estimated := TRUE; END IF; IF p_pt(i).R_ESTIMATED_IND <> 'Y' AND (p_pt(i).CONE_COUNT IS NOT NULL OR p_pt(i).POLLEN_COUNT IS NOT NULL) THEN b_bv_r_not_estimated := TRUE; END IF; IF p_pt(i).M_ESTIMATED_IND <> 'Y' AND (p_pt(i).CONE_COUNT IS NOT NULL OR p_pt(i).POLLEN_COUNT IS NOT NULL) THEN b_bv_m_not_estimated := TRUE; END IF; END LOOP; --Second pass to calculate total male gw contribution orchard pollen --(uses v_total_pollen_count from first pass) FOR i IN 1..p_pt.COUNT LOOP --col:W IF v_total_pollen_count = 0 THEN v_parent_prop_orch_poll := 0; ELSE v_parent_prop_orch_poll := NVL(p_pt(i).pollen_count,0) / v_total_pollen_count; END IF; --col:X v_m_gw_g_contrib_orch_poll := v_parent_prop_orch_poll * COALESCE(p_pt(i).bv_g,p_pt(i).cv_g,0); v_m_gw_d_contrib_orch_poll := v_parent_prop_orch_poll * COALESCE(p_pt(i).bv_d,p_pt(i).cv_d,0); v_m_gw_r_contrib_orch_poll := v_parent_prop_orch_poll * COALESCE(p_pt(i).bv_r,p_pt(i).cv_r,0); v_m_gw_m_contrib_orch_poll := v_parent_prop_orch_poll * COALESCE(p_pt(i).bv_m,p_pt(i).cv_m,0); --accumulate total SUM(x) v_sum_m_gw_g_contrib_orch_poll := v_sum_m_gw_g_contrib_orch_poll + v_m_gw_g_contrib_orch_poll; v_sum_m_gw_d_contrib_orch_poll := v_sum_m_gw_d_contrib_orch_poll + v_m_gw_d_contrib_orch_poll; v_sum_m_gw_r_contrib_orch_poll := v_sum_m_gw_r_contrib_orch_poll + v_m_gw_r_contrib_orch_poll; v_sum_m_gw_m_contrib_orch_poll := v_sum_m_gw_m_contrib_orch_poll + v_m_gw_m_contrib_orch_poll; END LOOP; -- recalculate the smp mix values (only if smp records exist for seedlot). v_smp_records_exist := spr_001a_smp_calculation.smp_records_exist(get_seedlot_number); IF v_smp_records_exist THEN spr_001a_smp_calculation.recalculate(get_seedlot_number ,get_seedlot_status_code ,get_vegetation_code ,get_orchard_id ,get_secondary_orchard_id ,get_seed_plan_unit_id ,'N' ,'N' ,v_smp_parents_outside ,v_old_smp_mix_bv_growth ,v_old_smp_mix_bv_density ,v_old_smp_mix_bv_resistance ,v_old_smp_mix_bv_major_gene ,v_old_smp_mix_elevation ,v_old_smp_mix_lat_deg ,v_old_smp_mix_lat_min ,v_old_smp_mix_long_deg ,v_old_smp_mix_long_min ,g_error_message); IF p_get_current_tests THEN spr_001a_smp_calculation.recalculate(get_seedlot_number ,get_seedlot_status_code ,get_vegetation_code ,get_orchard_id ,get_secondary_orchard_id ,get_seed_plan_unit_id ,'Y' ,'N' ,v_smp_parents_outside ,v_new_smp_mix_bv_growth ,v_new_smp_mix_bv_density ,v_new_smp_mix_bv_resistance ,v_new_smp_mix_bv_major_gene ,v_new_smp_mix_elevation ,v_new_smp_mix_lat_deg ,v_new_smp_mix_lat_min ,v_new_smp_mix_long_deg ,v_new_smp_mix_long_min ,g_error_message); END IF; g_smp_parents_outside := NVL(v_smp_parents_outside, 0); END IF; --Third pass to calc values that depend on totals derived above and the remainder FOR i IN 1..p_pt.COUNT LOOP --ignore rows without cone or pollen count IF p_pt(i).cone_count IS NOT NULL OR p_pt(i).pollen_count IS NOT NULL THEN v_total_parent_trees := v_total_parent_trees + 1; -- replace smp mix values, if changed. IF p_get_current_tests THEN IF NVL(p_pt(i).smp_mix_bv_growth,0) = v_old_smp_mix_bv_growth AND NVL(p_pt(i).smp_mix_bv_density,0) = v_old_smp_mix_bv_density AND NVL(p_pt(i).smp_mix_bv_resistance,0) = v_old_smp_mix_bv_resistance AND NVL(p_pt(i).smp_mix_bv_major_gene,0) = v_old_smp_mix_bv_major_gene AND NVL(p_pt(i).smp_mix_elevation,0) = v_old_smp_mix_elevation AND NVL(p_pt(i).smp_mix_latitude_degrees,0) = v_old_smp_mix_lat_deg AND NVL(p_pt(i).smp_mix_latitude_minutes,0) = v_old_smp_mix_lat_min AND NVL(p_pt(i).smp_mix_longitude_degrees,0) = v_old_smp_mix_long_deg AND NVL(p_pt(i).smp_mix_longitude_minutes,0) = v_old_smp_mix_long_min THEN p_pt(i).smp_mix_bv_growth := v_new_smp_mix_bv_growth; p_pt(i).smp_mix_bv_density := v_new_smp_mix_bv_density; p_pt(i).smp_mix_bv_resistance := v_new_smp_mix_bv_resistance; p_pt(i).smp_mix_bv_major_gene := v_new_smp_mix_bv_major_gene; p_pt(i).smp_mix_elevation := v_new_smp_mix_elevation; p_pt(i).smp_mix_latitude_degrees := v_new_smp_mix_lat_deg; p_pt(i).smp_mix_latitude_minutes := v_new_smp_mix_lat_min; p_pt(i).smp_mix_longitude_degrees := v_new_smp_mix_long_deg; p_pt(i).smp_mix_longitude_minutes := v_new_smp_mix_long_min; END IF; END IF; --Convert NULL array values to 0 as Excel does v_a_cone_count := NVL(p_pt(i).cone_count,0); v_a_pollen_count := NVL(p_pt(i).pollen_count,0); v_a_smp_success_pct := NVL(p_pt(i).smp_success_pct,0); v_a_smp_mix_bv_growth := NVL(p_pt(i).smp_mix_bv_growth,0); v_a_smp_mix_bv_density := NVL(p_pt(i).smp_mix_bv_density,0); v_a_smp_mix_bv_resistance := NVL(p_pt(i).smp_mix_bv_resistance,0); v_a_smp_mix_bv_major_gene := NVL(p_pt(i).smp_mix_bv_major_gene,0); v_a_smp_mix_latitude_degrees := NVL(p_pt(i).smp_mix_latitude_degrees,0); v_a_smp_mix_latitude_minutes := NVL(p_pt(i).smp_mix_latitude_minutes,0); v_a_smp_mix_longitude_degrees := NVL(p_pt(i).smp_mix_longitude_degrees,0); v_a_smp_mix_longitude_minutes := NVL(p_pt(i).smp_mix_longitude_minutes,0); v_a_smp_mix_elevation := NVL(p_pt(i).smp_mix_elevation,0); v_a_non_orchard_pollen_contam := NVL(p_pt(i).non_orchard_pollen_contam_pct,0); --values to calc avg smp mix bv g (only contribute to avg if specified) IF p_pt(i).smp_mix_bv_growth IS NOT NULL THEN v_total_smp_mix_bv_growth := v_total_smp_mix_bv_growth + v_a_smp_mix_bv_growth; v_num_smp_mix_bv_growth := v_num_smp_mix_bv_growth + 1; END IF; --values to calc avg smp mix bv d (only contribute to avg if specified) IF p_pt(i).smp_mix_bv_density IS NOT NULL THEN v_total_smp_mix_bv_density := v_total_smp_mix_bv_density + v_a_smp_mix_bv_density; v_num_smp_mix_bv_density := v_num_smp_mix_bv_density + 1; END IF; --values to calc avg smp mix bv r (only contribute to avg if specified) IF p_pt(i).smp_mix_bv_resistance IS NOT NULL THEN v_total_smp_mix_bv_resistance := v_total_smp_mix_bv_resistance + v_a_smp_mix_bv_resistance; v_num_smp_mix_bv_resistance := v_num_smp_mix_bv_resistance + 1; END IF; --values to calc avg smp mix bv m (only contribute to avg if specified) IF p_pt(i).smp_mix_bv_major_gene IS NOT NULL THEN v_total_smp_mix_bv_major_gene := v_total_smp_mix_bv_major_gene + v_a_smp_mix_bv_major_gene; v_num_smp_mix_bv_major_gene := v_num_smp_mix_bv_major_gene + 1; END IF; --values to calc avg non-orchard pollen contamination pct (only contribute to avg if specified) IF p_pt(i).non_orchard_pollen_contam_pct IS NOT NULL THEN v_total_non_orchard_pollen := v_total_non_orchard_pollen + v_a_non_orchard_pollen_contam; v_num_non_orchard_pollen := v_num_non_orchard_pollen + 1; END IF; --rr_debugger('-----------------------------------------------------'); --rr_debugger('parent tree id '||p_pt(i).parent_tree_id); --Get geography from parents if present spr_get_pt_geog(p_pt(i).parent_tree_id ,v_pt_elevation ,v_pt_latitude_degrees ,v_pt_latitude_minutes ,v_pt_latitude_seconds ,v_pt_longitude_degrees ,v_pt_longitude_minutes ,v_pt_longitude_seconds); -->Set to 0 if NULL as Excel would v_pt_elevation := NVL(v_pt_elevation,0); v_pt_latitude_degrees := NVL(v_pt_latitude_degrees,0); v_pt_latitude_minutes := NVL(v_pt_latitude_minutes,0); v_pt_latitude_seconds := NVL(v_pt_latitude_seconds,0); v_pt_longitude_degrees := NVL(v_pt_longitude_degrees,0); v_pt_longitude_minutes := NVL(v_pt_longitude_minutes,0); v_pt_longitude_seconds := NVL(v_pt_longitude_seconds,0); --NOTE for lat/long: converting all lat/longs to seconds instead of a decimal as -- spreadsheet did --rr_debugger('lat '||v_pt_latitude_degrees||'/'||v_pt_latitude_minutes); --col:E v_coll_lat := (v_pt_latitude_degrees*3600) + (v_pt_latitude_minutes*60) + v_pt_latitude_seconds; --rr_debugger('E: '||TO_CHAR(v_coll_lat)); --col:H v_coll_long := (v_pt_longitude_degrees*3600) + (v_pt_longitude_minutes*60) + v_pt_longitude_seconds; --rr_debugger('H: '||TO_CHAR(v_coll_long)); --col:T v_lat := (v_a_smp_mix_latitude_degrees*3600) + (v_a_smp_mix_latitude_minutes*60); --rr_debugger('T: '||TO_CHAR(v_lat)); --col:U v_long := (v_a_smp_mix_longitude_degrees*3600) + (v_a_smp_mix_longitude_minutes*60); --rr_debugger('U: '||TO_CHAR(v_long)); --col:V IF v_total_cone_count = 0 THEN v_female_crop_pop := 0; ELSE v_female_crop_pop := v_a_cone_count / v_total_cone_count; END IF; --rr_debugger('V: '||v_female_crop_pop); --col:W IF v_total_pollen_count = 0 THEN v_parent_prop_orch_poll := 0; ELSE v_parent_prop_orch_poll := v_a_pollen_count / v_total_pollen_count; END IF; --rr_debugger('W: '||v_parent_prop_orch_poll); --col:Y v_f_gw_g_contrib := v_female_crop_pop * NVL(p_pt(i).bv_g,p_pt(i).cv_g); v_f_gw_d_contrib := v_female_crop_pop * NVL(p_pt(i).bv_d,p_pt(i).cv_d); v_f_gw_r_contrib := v_female_crop_pop * NVL(p_pt(i).bv_r,p_pt(i).cv_r); v_f_gw_m_contrib := v_female_crop_pop * NVL(p_pt(i).bv_m,p_pt(i).cv_m); --rr_debugger('Y(g): '||v_f_gw_g_contrib); --rr_debugger('Y(d): '||v_f_gw_d_contrib); --rr_debugger('Y(r): '||v_f_gw_r_contrib); --rr_debugger('Y(m): '||v_f_gw_m_contrib); --col:Z v_m_smp_g_contrib := ( (v_a_smp_success_pct * v_a_smp_mix_bv_growth) / 100 ) * v_female_crop_pop; v_m_smp_d_contrib := ( (v_a_smp_success_pct * v_a_smp_mix_bv_density) / 100 ) * v_female_crop_pop; v_m_smp_r_contrib := ( (v_a_smp_success_pct * v_a_smp_mix_bv_resistance) / 100 ) * v_female_crop_pop; v_m_smp_m_contrib := ( (v_a_smp_success_pct * v_a_smp_mix_bv_major_gene) / 100 ) * v_female_crop_pop; --rr_debugger('Z(g): '||v_m_smp_g_contrib); --rr_debugger('Z(d): '||v_m_smp_d_contrib); --rr_debugger('Z(r): '||v_m_smp_r_contrib); --rr_debugger('Z(m): '||v_m_smp_m_contrib); --col:AA v_m_contam_contrib := ( (1 - (v_a_smp_success_pct/100)) * (v_a_non_orchard_pollen_contam/100) * v_contaminant_pollen_bv ) * v_female_crop_pop; --col:AB (depends on SUM(X)=v_sum_m_gw_contrib_orch_poll) v_m_orch_poll_contrib_g := ( ( 1 - (v_a_smp_success_pct/100) - (v_a_non_orchard_pollen_contam/100) ) * v_sum_m_gw_g_contrib_orch_poll ) * v_female_crop_pop; v_m_orch_poll_contrib_d := ( ( 1 - (v_a_smp_success_pct/100) - (v_a_non_orchard_pollen_contam/100) ) * v_sum_m_gw_d_contrib_orch_poll ) * v_female_crop_pop; v_m_orch_poll_contrib_r := ( ( 1 - (v_a_smp_success_pct/100) - (v_a_non_orchard_pollen_contam/100) ) * v_sum_m_gw_r_contrib_orch_poll ) * v_female_crop_pop; v_m_orch_poll_contrib_m := ( ( 1 - (v_a_smp_success_pct/100) - (v_a_non_orchard_pollen_contam/100) ) * v_sum_m_gw_m_contrib_orch_poll ) * v_female_crop_pop; --col:AC depends on prev value v_m_total_gw_g_contrib := v_m_smp_g_contrib + v_m_contam_contrib + v_m_orch_poll_contrib_g; v_m_total_gw_d_contrib := v_m_smp_d_contrib + v_m_contam_contrib + v_m_orch_poll_contrib_d; v_m_total_gw_r_contrib := v_m_smp_r_contrib + v_m_contam_contrib + v_m_orch_poll_contrib_r; v_m_total_gw_m_contrib := v_m_smp_m_contrib + v_m_contam_contrib + v_m_orch_poll_contrib_m; --rr_debugger('AC(g): '||TO_CHAR(v_m_total_gw_g_contrib)); --rr_debugger('AC(d): '||TO_CHAR(v_m_total_gw_d_contrib)); --rr_debugger('AC(r): '||TO_CHAR(v_m_total_gw_r_contrib)); --rr_debugger('AC(m): '||TO_CHAR(v_m_total_gw_m_contrib)); --col:AD IF v_total_pollen_count = 0 THEN v_p_total_gw_g_contrib := v_f_gw_g_contrib; v_p_total_gw_d_contrib := v_f_gw_d_contrib; v_p_total_gw_r_contrib := v_f_gw_r_contrib; v_p_total_gw_m_contrib := v_f_gw_m_contrib; ELSE v_p_total_gw_g_contrib := (v_f_gw_g_contrib + v_m_total_gw_g_contrib) / 2; v_p_total_gw_d_contrib := (v_f_gw_d_contrib + v_m_total_gw_d_contrib) / 2; v_p_total_gw_r_contrib := (v_f_gw_r_contrib + v_m_total_gw_r_contrib) / 2; v_p_total_gw_m_contrib := (v_f_gw_m_contrib + v_m_total_gw_m_contrib) / 2; END IF; --rr_debugger('AD(g): '||v_p_total_gw_g_contrib); --rr_debugger('AD(d): '||v_p_total_gw_d_contrib); --rr_debugger('AD(r): '||v_p_total_gw_r_contrib); --rr_debugger('AD(m): '||v_p_total_gw_m_contrib); --Set total gw contrib back into array so it can be displayed/saved p_pt(i).total_genetic_worth_contrib := v_p_total_gw_g_contrib; -- p_pt(i).total_genetic_worth_r_contrib := v_p_total_gw_r_contrib; -- maybe in future these calculations will be used. -- p_pt(i).total_genetic_worth_d_contrib := v_p_total_gw_d_contrib; --SUM(AD) v_sum_p_total_gw_g_contrib := v_sum_p_total_gw_g_contrib + v_p_total_gw_g_contrib; --rr_debugger('SUM(AD)(g): '||v_sum_p_total_gw_g_contrib); v_sum_p_total_gw_d_contrib := v_sum_p_total_gw_d_contrib + NVL(v_p_total_gw_d_contrib,0); --rr_debugger('SUM(AD)(d): '||v_sum_p_total_gw_d_contrib); v_sum_p_total_gw_r_contrib := v_sum_p_total_gw_r_contrib + NVL(v_p_total_gw_r_contrib,0); --rr_debugger('SUM(AD)(r): '||v_sum_p_total_gw_r_contrib); v_sum_p_total_gw_m_contrib := v_sum_p_total_gw_m_contrib + NVL(v_p_total_gw_m_contrib,0); --rr_debugger('SUM(AD)(m): '||v_sum_p_total_gw_m_contrib); --col:AE IF v_total_pollen_count = 0 THEN v_p_prop_contrib := v_female_crop_pop; ELSE v_p_prop_contrib := (v_female_crop_pop + v_parent_prop_orch_poll) / 2; END IF; v_sum_p_prop_contrib := v_sum_p_prop_contrib + v_p_prop_contrib; --rr_debugger('SUM(AE): '||v_sum_p_prop_contrib); --SUM(AE for Tested Parent Trees) IF p_pt(i).untested_ind != 'Y' AND (p_pt(i).g_estimated_ind != 'Y' OR p_pt(i).r_estimated_ind != 'Y' OR p_pt(i).d_estimated_ind != 'Y' OR p_pt(i).m_estimated_ind != 'Y')THEN v_sum_p_prop_contrib_tested := v_sum_p_prop_contrib_tested + v_p_prop_contrib; END IF; --rr_debugger('SUM(AE for Tested Parent Trees): '||v_sum_p_prop_contrib_tested); --col:AF v_p_contrib_elev_no_smp_poll := v_pt_elevation * (v_p_prop_contrib-((v_female_crop_pop*v_a_smp_success_pct)/200)); --rr_debugger('AF: '||TO_CHAR(v_p_contrib_elev_no_smp_poll)); --col:AG v_p_contrib_lat_no_smp_poll := v_coll_lat * (v_p_prop_contrib-((v_female_crop_pop*v_a_smp_success_pct)/200)); --rr_debugger('AG: '||TO_CHAR(v_p_contrib_lat_no_smp_poll)); --col:AH v_p_contrib_long_no_smp_poll := v_coll_long * (v_p_prop_contrib-((v_female_crop_pop*v_a_smp_success_pct)/200)); --rr_debugger('AH: '||TO_CHAR(v_p_contrib_long_no_smp_poll)); --col:AI v_smp_poll_wtd_contrib_elev := ((v_a_smp_mix_elevation * v_a_smp_success_pct)/200)*v_female_crop_pop; --rr_debugger('AI: '||TO_CHAR(v_smp_poll_wtd_contrib_elev)); --col:AJ v_smp_poll_wtd_contrib_lat := ((v_lat * v_a_smp_success_pct)/200)*v_female_crop_pop; --rr_debugger('AJ: '||TO_CHAR(v_smp_poll_wtd_contrib_lat)); --col:AK v_smp_poll_wtd_contrib_long := ((v_long * v_a_smp_success_pct)/200)*v_female_crop_pop; --rr_debugger('AK: '||TO_CHAR(v_smp_poll_wtd_contrib_long)); --col:AL v_wtd_elev_p_and_smp_poll := v_p_contrib_elev_no_smp_poll + v_smp_poll_wtd_contrib_elev; --rr_debugger('AL: '||TO_CHAR(v_wtd_elev_p_and_smp_poll)); v_sum_wtd_elev_p_and_smp_poll := v_sum_wtd_elev_p_and_smp_poll + v_wtd_elev_p_and_smp_poll; --rr_debugger('SUM AL: '||TO_CHAR(v_sum_wtd_elev_p_and_smp_poll)); --col:AM v_wtd_lat_p_and_smp_poll := v_p_contrib_lat_no_smp_poll + v_smp_poll_wtd_contrib_lat; --rr_debugger('AM: '||TO_CHAR(v_wtd_lat_p_and_smp_poll)); v_sum_wtd_lat_p_and_smp_poll := v_sum_wtd_lat_p_and_smp_poll + v_wtd_lat_p_and_smp_poll; --rr_debugger('SUM AM: '||TO_CHAR(v_sum_wtd_lat_p_and_smp_poll)); --col:AN v_wtd_long_p_and_smp_poll := v_p_contrib_long_no_smp_poll + v_smp_poll_wtd_contrib_long; --rr_debugger('AN: '||TO_CHAR(v_wtd_long_p_and_smp_poll)); v_sum_wtd_long_p_and_smp_poll := v_sum_wtd_long_p_and_smp_poll + v_wtd_long_p_and_smp_poll; --rr_debugger('SUM AN: '||TO_CHAR(v_sum_wtd_long_p_and_smp_poll)); --col:AO v_ne_no_smp_contrib := POWER(v_p_prop_contrib,2); v_sum_ne_no_smp_contrib := v_sum_ne_no_smp_contrib + v_ne_no_smp_contrib; --col:AP (xls did /100 so left in for comparison and * 100 at end to get smp success %) v_smp_success_wtd_by_f_p := (v_female_crop_pop * v_a_smp_success_pct) / 100; v_sum_smp_success_wtd_by_f_p := v_sum_smp_success_wtd_by_f_p + v_smp_success_wtd_by_f_p; --col:AQ v_orch_gamete_contr := POWER( ( v_female_crop_pop + (0.75*v_parent_prop_orch_poll) )/2,2); v_sum_orch_gamete_contr := v_sum_orch_gamete_contr + v_orch_gamete_contr; --rr_debugger('AQ: '||TO_CHAR(v_orch_gamete_contr)); ELSE --Set total gw contrib back into array so it can be displayed/saved p_pt(i).total_genetic_worth_contrib := 0; -- p_pt(i).total_genetic_worth_r_contrib := 0; -- p_pt(i).total_genetic_worth_d_contrib := 0; END IF; --cone_count or pollen_count not null END LOOP; --rr_debugger('SUM AQ: '||TO_CHAR(v_sum_orch_gamete_contr)); --calc avg smp mix bv g IF v_num_smp_mix_bv_growth > 0 THEN v_avg_smp_mix_bv_growth := v_total_smp_mix_bv_growth / v_num_smp_mix_bv_growth; END IF; --calc avg smp mix bv d IF v_num_smp_mix_bv_density > 0 THEN v_avg_smp_mix_bv_density := v_total_smp_mix_bv_density / v_num_smp_mix_bv_density; END IF; --calc avg smp mix bv r IF v_num_smp_mix_bv_resistance > 0 THEN v_avg_smp_mix_bv_resistance := v_total_smp_mix_bv_resistance / v_num_smp_mix_bv_resistance; END IF; --calc avg smp mix bv m IF v_num_smp_mix_bv_major_gene > 0 THEN v_avg_smp_mix_bv_major_gene := v_total_smp_mix_bv_major_gene / v_num_smp_mix_bv_major_gene; END IF; --calc avg non-orchard pollen contamination pct IF v_num_non_orchard_pollen > 0 THEN v_avg_non_orchard_pollen := v_total_non_orchard_pollen / v_num_non_orchard_pollen; END IF; --GW-G v_gw_g := ROUND(v_sum_p_total_gw_g_contrib); --rr_debugger('GW-G: '||v_gw_g); --GW-D v_gw_d := ROUND(v_sum_p_total_gw_d_contrib); --rr_debugger('GW-D: '||v_gw_d); --GW-R v_gw_r := ROUND(v_sum_p_total_gw_r_contrib); --rr_debugger('GW-R: '||v_gw_r); --GW-M v_gw_m := ROUND(v_sum_p_total_gw_m_contrib); --rr_debugger('GW-M: '||v_gw_m); IF g_coancestry IS NOT NULL THEN --Effective Population Size with Coancestry considered IF g_coancestry = 0 THEN v_effective_pop_size := 0; ELSE v_effective_pop_size := 0.5/g_coancestry; END IF; ELSIF v_smp_parents_outside > 0 THEN --Effective Population Size with SMP (for Growth) v_effective_pop_size := ROUND(1/ (v_sum_orch_gamete_contr + ( POWER(0.25/(2*v_smp_parents_outside),2) * v_smp_parents_outside ) ) ,1); ELSE --Effective Population Size IF v_sum_orch_gamete_contr = 0 THEN v_effective_pop_size := 0; ELSE v_effective_pop_size := ROUND(1/v_sum_ne_no_smp_contrib,1); END IF; END IF; IF v_total_parent_trees > 0 THEN v_lat_deg := TRUNC(v_sum_wtd_lat_p_and_smp_poll/3600); v_lat_min := TRUNC(MOD(v_sum_wtd_lat_p_and_smp_poll,3600)/60); v_lat_sec := TRUNC(MOD(v_sum_wtd_lat_p_and_smp_poll,60)); v_long_deg := TRUNC(v_sum_wtd_long_p_and_smp_poll/3600); v_long_min := TRUNC(MOD(v_sum_wtd_long_p_and_smp_poll,3600)/60); v_long_sec := TRUNC(MOD(v_sum_wtd_long_p_and_smp_poll,60)); v_elev := ROUND(v_sum_wtd_elev_p_and_smp_poll); v_smp_mean_bv_growth := ROUND(v_avg_smp_mix_bv_growth,1); v_smp_mean_bv_density := ROUND(v_avg_smp_mix_bv_density,1); v_smp_mean_bv_resistance := ROUND(v_avg_smp_mix_bv_resistance,1); v_smp_mean_bv_major_gene := ROUND(v_avg_smp_mix_bv_major_gene,1); v_smp_success_pct := ROUND(v_sum_smp_success_wtd_by_f_p * 100); IF v_total_non_orchard_pollen = 0 THEN v_orchard_contamination_pct := 0; ELSE v_orchard_contamination_pct := ROUND(v_avg_non_orchard_pollen); END IF; --No errors raised, set values in record r_pt_contrib.collection_elevation := v_elev; r_pt_contrib.collection_elevation_min := v_elev_min; r_pt_contrib.collection_elevation_max := v_elev_max; r_pt_contrib.collection_lat_deg := v_lat_deg; r_pt_contrib.collection_lat_min := v_lat_min; r_pt_contrib.collection_lat_sec := v_lat_sec; r_pt_contrib.collection_long_deg := v_long_deg; r_pt_contrib.collection_long_min := v_long_min; r_pt_contrib.collection_long_sec := v_long_sec; r_pt_contrib.smp_mean_bv_growth := v_smp_mean_bv_growth; r_pt_contrib.smp_mean_bv_density := v_smp_mean_bv_density; r_pt_contrib.smp_mean_bv_resistance := v_smp_mean_bv_resistance; r_pt_contrib.smp_mean_bv_major_gene := v_smp_mean_bv_major_gene; r_pt_contrib.smp_success_pct := v_smp_success_pct; r_pt_contrib.orchard_contamination_pct := v_orchard_contamination_pct; r_pt_contrib.effective_pop_size := v_effective_pop_size; r_pt_contrib.gw_g := v_gw_g; -- pt#33182 - if all values of bv-d/r/m are estimated, don't calculate gw. IF b_bv_d_not_estimated THEN r_pt_contrib.gw_d := v_gw_d; END IF; IF b_bv_r_not_estimated THEN r_pt_contrib.gw_r := v_gw_r; END IF; IF b_bv_m_not_estimated THEN r_pt_contrib.gw_m := v_gw_m; END IF; r_pt_contrib.total_parent_trees := v_total_parent_trees; r_pt_contrib.pct_tested_parent_trees := ROUND((v_sum_p_prop_contrib_tested / v_sum_p_prop_contrib) * 100); r_pt_contrib.pct_untested_parent_trees := 100 - r_pt_contrib.pct_tested_parent_trees; END IF; --v_total_parent_trees > 0 --rr_debugger('tested% '||TO_CHAR(r_pt_contrib.pct_tested_parent_trees)); --rr_debugger('#trees '||TO_CHAR(r_pt_contrib.total_parent_trees)); --rr_debugger('eff '||TO_CHAR(r_pt_contrib.effective_pop_size)); --rr_debugger('gwg '||TO_CHAR(r_pt_contrib.gw_g)); --rr_debugger('gwd '||TO_CHAR(r_pt_contrib.gw_d)); --rr_debugger('gwr '||TO_CHAR(r_pt_contrib.gw_r)); --rr_debugger('gwm '||TO_CHAR(r_pt_contrib.gw_m)); --rr_debugger('lat deg min sec '||TO_CHAR(r_pt_contrib.collection_lat_deg)||' '||TO_CHAR(r_pt_contrib.collection_lat_min)||' '||TO_CHAR(r_pt_contrib.collection_lat_sec)); --rr_debugger('long deg min sec '||TO_CHAR(r_pt_contrib.collection_long_deg)||' '||TO_CHAR(r_pt_contrib.collection_long_min)||' '||TO_CHAR(r_pt_contrib.collection_long_sec)); --rr_debugger('elev '||TO_CHAR(r_pt_contrib.collection_elevation)); --rr_debugger('SMP pollen mix mean BV growth '||TO_CHAR(r_pt_contrib.smp_mean_bv_growth)); --rr_debugger('SMP pollen mix mean BV density '||TO_CHAR(r_pt_contrib.smp_mean_bv_density)); --rr_debugger('SMP pollen mix mean BV resistance '||TO_CHAR(r_pt_contrib.smp_mean_bv_resistance)); --rr_debugger('SMP pollen mix mean BV major_gene '||TO_CHAR(r_pt_contrib.smp_mean_bv_major_gene)); --rr_debugger('SMP Success % '||TO_CHAR(r_pt_contrib.smp_success_pct)); --rr_debugger('Orchard contamination % '||TO_CHAR(v_orchard_contamination_pct)); END calc_pt_contrib;