CREATE OR REPLACE PACKAGE BODY spr_seedlot AS --member vars g_error_message VARCHAR2(4000); --member vars g_seed_plan_zone_code seedlot.seed_plan_zone_code%TYPE; gb_seed_plan_zone_code VARCHAR2(1); g_applicant_client_locn seedlot.applicant_client_locn%TYPE; gb_applicant_client_locn VARCHAR2(1); g_applicant_client_number seedlot.applicant_client_number%TYPE; gb_applicant_client_number VARCHAR2(1); g_applicant_email_address seedlot.applicant_email_address%TYPE; gb_applicant_email_address VARCHAR2(1); g_bc_source_ind seedlot.bc_source_ind%TYPE; gb_bc_source_ind VARCHAR2(1); g_biotech_processes_ind seedlot.biotech_processes_ind%TYPE; gb_biotech_processes_ind VARCHAR2(1); g_collection_area_radius seedlot.collection_area_radius%TYPE; gb_collection_area_radius VARCHAR2(1); g_collection_bgc_ind seedlot.collection_bgc_ind%TYPE; gb_collection_bgc_ind VARCHAR2(1); g_collection_spz_ind seedlot.collection_seed_plan_zone_ind%TYPE; gb_collection_spz_ind VARCHAR2(1); g_coll_standard_met_ind seedlot.collection_standard_met_ind%TYPE; gb_coll_standard_met_ind VARCHAR2(1); g_cone_collection_method2_cd seedlot.cone_collection_method2_code%TYPE; gb_cone_collection_method2_cd VARCHAR2(1); g_contaminant_pollen_bv seedlot.contaminant_pollen_bv%TYPE; gb_contaminant_pollen_bv VARCHAR2(1); g_controlled_cross_ind seedlot.controlled_cross_ind%TYPE; gb_controlled_cross_ind VARCHAR2(1); g_declared_userid seedlot.declared_userid%TYPE; gb_declared_userid VARCHAR2(1); g_declared_timestamp seedlot.declared_timestamp%TYPE; gb_declared_timestamp VARCHAR2(1); g_female_gametic_mthd_code seedlot.female_gametic_mthd_code%TYPE; gb_female_gametic_mthd_code VARCHAR2(1); g_latitude_sec_max seedlot.latitude_sec_max%TYPE; gb_latitude_sec_max VARCHAR2(1); g_latitude_sec_min seedlot.latitude_sec_min%TYPE; gb_latitude_sec_min VARCHAR2(1); g_longitude_sec_max seedlot.longitude_sec_max%TYPE; gb_longitude_sec_max VARCHAR2(1); g_longitude_sec_min seedlot.longitude_sec_min%TYPE; gb_longitude_sec_min VARCHAR2(1); g_male_gametic_mthd_code seedlot.male_gametic_mthd_code%TYPE; gb_male_gametic_mthd_code VARCHAR2(1); g_orchard_comment seedlot.orchard_comment%TYPE; gb_orchard_comment VARCHAR2(1); g_orchard_contamination_pct seedlot.orchard_contamination_pct%TYPE; gb_orchard_contamination_pct VARCHAR2(1); g_pollen_contamination_ind seedlot.pollen_contamination_ind%TYPE; gb_pollen_contamination_ind VARCHAR2(1); g_pollen_contam_mthd_code seedlot.pollen_contamination_mthd_code%TYPE; gb_pollen_contam_mthd_code VARCHAR2(1); g_pollen_contamination_pct seedlot.pollen_contamination_pct%TYPE; gb_pollen_contamination_pct VARCHAR2(1); g_provenance_id seedlot.provenance_id%TYPE; gb_provenance_id VARCHAR2(1); g_secondary_orchard_id seedlot.secondary_orchard_id%TYPE; gb_secondary_orchard_id VARCHAR2(1); g_seed_plan_unit_id seedlot.seed_plan_unit_id%TYPE; gb_seed_plan_unit_id VARCHAR2(1); g_seed_store_client_locn seedlot.seed_store_client_locn%TYPE; gb_seed_store_client_locn VARCHAR2(1); g_seed_store_client_number seedlot.seed_store_client_number%TYPE; gb_seed_store_client_number VARCHAR2(1); g_seedlot_source_code seedlot.seedlot_source_code%TYPE; gb_seedlot_source_code VARCHAR2(1); g_smp_mean_bv_growth seedlot_parent_tree.smp_mix_bv_growth%TYPE; gb_smp_mean_bv_growth VARCHAR2(1); g_smp_mean_bv_density seedlot_parent_tree.smp_mix_bv_density%TYPE; gb_smp_mean_bv_density VARCHAR2(1); g_smp_mean_bv_resistance seedlot_parent_tree.smp_mix_bv_resistance%TYPE; gb_smp_mean_bv_resistance VARCHAR2(1); g_smp_mean_bv_major_gene seedlot_parent_tree.smp_mix_bv_major_gene%TYPE; gb_smp_mean_bv_major_gene VARCHAR2(1); g_smp_parents_outside seedlot.smp_parents_outside%TYPE; gb_smp_parents_outside VARCHAR2(1); g_smp_success_pct seedlot.smp_success_pct%TYPE; gb_smp_success_pct VARCHAR2(1); g_temporary_storage_end_date seedlot.temporary_storage_end_date%TYPE; gb_temporary_storage_end_date VARCHAR2(1); g_temporary_storage_start_dt seedlot.temporary_storage_start_date%TYPE; gb_temporary_storage_start_dt VARCHAR2(1); g_total_parent_trees seedlot.total_parent_trees%TYPE; gb_total_parent_trees VARCHAR2(1); g_latitude_seconds seedlot.latitude_seconds%TYPE; gb_latitude_seconds VARCHAR2(1); g_longitude_seconds seedlot.longitude_seconds%TYPE; gb_longitude_seconds VARCHAR2(1); g_collection_lat_sec seedlot.collection_lat_sec%TYPE; gb_collection_lat_sec VARCHAR2(1); g_collection_long_sec seedlot.collection_long_sec%TYPE; gb_collection_long_sec VARCHAR2(1); g_seedlot_number seedlot.seedlot_number%TYPE; gb_seedlot_number VARCHAR2(1); g_seedlot_status_code seedlot.seedlot_status_code%TYPE; gb_seedlot_status_code VARCHAR2(1); g_vegetation_code seedlot.vegetation_code%TYPE; gb_vegetation_code VARCHAR2(1); g_genetic_class_code seedlot.genetic_class_code%TYPE; gb_genetic_class_code VARCHAR2(1); g_collection_source_code seedlot.collection_source_code%TYPE; gb_collection_source_code VARCHAR2(1); g_superior_prvnc_ind seedlot.superior_prvnc_ind%TYPE; gb_superior_prvnc_ind VARCHAR2(1); g_org_unit_no seedlot.org_unit_no%TYPE; gb_org_unit_no VARCHAR2(1); g_registered_seed_ind seedlot.registered_seed_ind%TYPE; gb_registered_seed_ind VARCHAR2(1); g_to_be_registrd_ind seedlot.to_be_registrd_ind%TYPE; gb_to_be_registrd_ind VARCHAR2(1); g_registered_date seedlot.registered_date%TYPE; gb_registered_date VARCHAR2(1); g_fs721a_signed_ind seedlot.fs721a_signed_ind%TYPE; gb_fs721a_signed_ind VARCHAR2(1); g_nad_datum_code seedlot.nad_datum_code%TYPE; gb_nad_datum_code VARCHAR2(1); g_utm_zone seedlot.utm_zone%TYPE; gb_utm_zone VARCHAR2(1); g_utm_easting seedlot.utm_easting%TYPE; gb_utm_easting VARCHAR2(1); g_utm_northing seedlot.utm_northing%TYPE; gb_utm_northing VARCHAR2(1); g_longitude_degrees seedlot.longitude_degrees%TYPE; gb_longitude_degrees VARCHAR2(1); g_longitude_minutes seedlot.longitude_minutes%TYPE; gb_longitude_minutes VARCHAR2(1); g_longitude_deg_min seedlot.longitude_deg_min%TYPE; gb_longitude_deg_min VARCHAR2(1); g_longitude_min_min seedlot.longitude_min_min%TYPE; gb_longitude_min_min VARCHAR2(1); g_longitude_deg_max seedlot.longitude_deg_max%TYPE; gb_longitude_deg_max VARCHAR2(1); g_longitude_min_max seedlot.longitude_min_max%TYPE; gb_longitude_min_max VARCHAR2(1); g_latitude_degrees seedlot.latitude_degrees%TYPE; gb_latitude_degrees VARCHAR2(1); g_latitude_minutes seedlot.latitude_minutes%TYPE; gb_latitude_minutes VARCHAR2(1); g_latitude_deg_min seedlot.latitude_deg_min%TYPE; gb_latitude_deg_min VARCHAR2(1); g_latitude_min_min seedlot.latitude_min_min%TYPE; gb_latitude_min_min VARCHAR2(1); g_latitude_deg_max seedlot.latitude_deg_max%TYPE; gb_latitude_deg_max VARCHAR2(1); g_latitude_min_max seedlot.latitude_min_max%TYPE; gb_latitude_min_max VARCHAR2(1); g_seed_coast_area_code seedlot.seed_coast_area_code%TYPE; gb_seed_coast_area_code VARCHAR2(1); g_elevation seedlot.elevation%TYPE; gb_elevation VARCHAR2(1); g_elevation_min seedlot.elevation_min%TYPE; gb_elevation_min VARCHAR2(1); g_elevation_max seedlot.elevation_max%TYPE; gb_elevation_max VARCHAR2(1); g_orchard_id seedlot.orchard_id%TYPE; gb_orchard_id VARCHAR2(1); g_collection_locn_desc seedlot.collection_locn_desc%TYPE; gb_collection_locn_desc VARCHAR2(1); g_collection_cli_number seedlot.collection_cli_number%TYPE; gb_collection_cli_number VARCHAR2(1); g_collection_cli_locn_cd seedlot.collection_cli_locn_cd%TYPE; gb_collection_cli_locn_cd VARCHAR2(1); g_collection_start_date seedlot.collection_start_date%TYPE; gb_collection_start_date VARCHAR2(1); g_collection_end_date seedlot.collection_end_date%TYPE; gb_collection_end_date VARCHAR2(1); g_cone_collection_method_cd seedlot.cone_collection_method_code%TYPE; gb_cone_collection_method_cd VARCHAR2(1); g_no_of_containers seedlot.no_of_containers%TYPE; gb_no_of_containers VARCHAR2(1); g_clctn_volume seedlot.clctn_volume%TYPE; gb_clctn_volume VARCHAR2(1); g_vol_per_container seedlot.vol_per_container%TYPE; gb_vol_per_container VARCHAR2(1); g_nmbr_trees_from_code seedlot.nmbr_trees_from_code%TYPE; gb_nmbr_trees_from_code VARCHAR2(1); g_coancestry seedlot.coancestry%TYPE; gb_coancestry VARCHAR2(1); g_effective_pop_size seedlot.effective_pop_size%TYPE; gb_effective_pop_size VARCHAR2(1); g_original_seed_qty seedlot.original_seed_qty%TYPE; gb_original_seed_qty VARCHAR2(1); g_interm_strg_client_number seedlot.interm_strg_client_number%TYPE; gb_interm_strg_client_number VARCHAR2(1); g_interm_strg_client_locn seedlot.interm_strg_client_locn%TYPE; gb_interm_strg_client_locn VARCHAR2(1); g_interm_strg_st_date seedlot.interm_strg_st_date%TYPE; gb_interm_strg_st_date VARCHAR2(1); g_interm_strg_end_date seedlot.interm_strg_end_date%TYPE; gb_interm_strg_end_date VARCHAR2(1); g_interm_facility_code seedlot.interm_facility_code%TYPE; gb_interm_facility_code VARCHAR2(1); g_extraction_st_date seedlot.extraction_st_date%TYPE; gb_extraction_st_date VARCHAR2(1); g_extraction_end_date seedlot.extraction_end_date%TYPE; gb_extraction_end_date VARCHAR2(1); g_extraction_volume seedlot.extraction_volume%TYPE; gb_extraction_volume VARCHAR2(1); g_extrct_cli_number seedlot.extrct_cli_number%TYPE; gb_extrct_cli_number VARCHAR2(1); g_extrct_cli_locn_cd seedlot.extrct_cli_locn_cd%TYPE; gb_extrct_cli_locn_cd VARCHAR2(1); g_stored_cli_number seedlot.stored_cli_number%TYPE; gb_stored_cli_number VARCHAR2(1); g_stored_cli_locn_cd seedlot.stored_cli_locn_cd%TYPE; gb_stored_cli_locn_cd VARCHAR2(1); g_lngterm_strg_st_date seedlot.lngterm_strg_st_date%TYPE; gb_lngterm_strg_st_date VARCHAR2(1); g_historical_tsr_date seedlot.historical_tsr_date%TYPE; gb_historical_tsr_date VARCHAR2(1); g_collection_lat_deg seedlot.collection_lat_deg%TYPE; gb_collection_lat_deg VARCHAR2(1); g_collection_lat_min seedlot.collection_lat_min%TYPE; gb_collection_lat_min VARCHAR2(1); g_collection_latitude_code seedlot.collection_latitude_code%TYPE; gb_collection_latitude_code VARCHAR2(1); g_collection_long_deg seedlot.collection_long_deg%TYPE; gb_collection_long_deg VARCHAR2(1); g_collection_long_min seedlot.collection_long_min%TYPE; gb_collection_long_min VARCHAR2(1); g_collection_longitude_code seedlot.collection_longitude_code%TYPE; gb_collection_longitude_code VARCHAR2(1); g_collection_elevation seedlot.collection_elevation%TYPE; gb_collection_elevation VARCHAR2(1); g_collection_elevation_min seedlot.collection_elevation_min%TYPE; gb_collection_elevation_min VARCHAR2(1); g_collection_elevation_max seedlot.collection_elevation_max%TYPE; gb_collection_elevation_max VARCHAR2(1); g_entry_timestamp seedlot.entry_timestamp%TYPE; gb_entry_timestamp VARCHAR2(1); g_entry_userid seedlot.entry_userid%TYPE; gb_entry_userid VARCHAR2(1); g_update_timestamp seedlot.update_timestamp%TYPE; gb_update_timestamp VARCHAR2(1); g_update_userid seedlot.update_userid%TYPE; gb_update_userid VARCHAR2(1); g_approved_timestamp seedlot.approved_timestamp%TYPE; gb_approved_timestamp VARCHAR2(1); g_approved_userid seedlot.approved_userid%TYPE; gb_approved_userid VARCHAR2(1); g_revision_count seedlot.revision_count%TYPE; gb_revision_count VARCHAR2(1); g_interm_strg_locn seedlot.interm_strg_locn%TYPE; gb_interm_strg_locn VARCHAR2(1); g_interm_strg_cmt seedlot.interm_strg_cmt%TYPE; gb_interm_strg_cmt VARCHAR2(1); g_ownership_comment seedlot.ownership_comment%TYPE; gb_ownership_comment VARCHAR2(1); g_cone_seed_desc seedlot.cone_seed_desc%TYPE; gb_cone_seed_desc VARCHAR2(1); g_extraction_comment seedlot.extraction_comment%TYPE; gb_extraction_comment VARCHAR2(1); g_seedlot_comment seedlot.seedlot_comment%TYPE; gb_seedlot_comment VARCHAR2(1); g_bgc_zone_code seedlot.bgc_zone_code%TYPE; gb_bgc_zone_code VARCHAR2(1); g_bgc_subzone_code seedlot.bgc_subzone_code%TYPE; gb_bgc_subzone_code VARCHAR2(1); g_variant seedlot.variant%TYPE; gb_variant VARCHAR2(1); g_bec_version_id seedlot.bec_version_id%TYPE; gb_bec_version_id VARCHAR2(1); --Related information g_gw_g seedlot_genetic_worth.genetic_worth_rtng%TYPE; g_gw_d seedlot_genetic_worth.genetic_worth_rtng%TYPE; g_gw_r seedlot_genetic_worth.genetic_worth_rtng%TYPE; g_gw_m seedlot_genetic_worth.genetic_worth_rtng%TYPE; g_spz_list VARCHAR2(100); g_tested_parent_trees_pct NUMBER(3); g_untested_parent_trees_pct NUMBER(3); g_is_lot_split BOOLEAN; -- Record to hold previous values r_previous seedlot%ROWTYPE; -- Record to hold parent tree contribution calculations TYPE t_pt_calc IS RECORD ( collection_elevation seedlot.collection_elevation%TYPE , collection_elevation_min seedlot.collection_elevation_min%TYPE , collection_elevation_max seedlot.collection_elevation_max%TYPE , collection_lat_deg seedlot.collection_lat_deg%TYPE , collection_lat_min seedlot.collection_lat_min%TYPE , collection_lat_sec seedlot.collection_lat_sec%TYPE , collection_long_deg seedlot.collection_long_deg%TYPE , collection_long_min seedlot.collection_long_min%TYPE , collection_long_sec seedlot.collection_long_sec%TYPE , smp_mean_bv_growth seedlot_parent_tree.smp_mix_bv_growth%TYPE , smp_mean_bv_density seedlot_parent_tree.smp_mix_bv_density%TYPE , smp_mean_bv_resistance seedlot_parent_tree.smp_mix_bv_resistance%TYPE , smp_mean_bv_major_gene seedlot_parent_tree.smp_mix_bv_major_gene%TYPE , smp_success_pct seedlot.smp_success_pct%TYPE , orchard_contamination_pct seedlot.orchard_contamination_pct%TYPE , effective_pop_size seedlot.effective_pop_size%TYPE , gw_g seedlot_genetic_worth.genetic_worth_rtng%TYPE , gw_d seedlot_genetic_worth.genetic_worth_rtng%TYPE , gw_r seedlot_genetic_worth.genetic_worth_rtng%TYPE , gw_m seedlot_genetic_worth.genetic_worth_rtng%TYPE , total_parent_trees seedlot.total_parent_trees%TYPE , pct_tested_parent_trees NUMBER(3) , pct_untested_parent_trees NUMBER(3)); r_pt_contrib t_pt_calc; CONST_CLASS_B_LOTNUM_MIN CONSTANT VARCHAR2(5) := '53000'; CONST_CLASS_B_LOTNUM_MAX CONSTANT VARCHAR2(5) := '59999'; CONST_CLASS_A_LOTNUM_MIN CONSTANT VARCHAR2(5) := '63000'; CONST_CLASS_A_LOTNUM_MAX CONSTANT VARCHAR2(5) := '69999'; CONST_CLASS_B_COPY_MIN CONSTANT VARCHAR2(5) := '52000'; CONST_CLASS_B_COPY_MAX CONSTANT VARCHAR2(5) := '52999'; CONST_CLASS_A_COPY_MIN CONSTANT VARCHAR2(5) := '62000'; CONST_CLASS_A_COPY_MAX CONSTANT VARCHAR2(5) := '62999'; /****************************************************************************** Procedure: get_previous_seedlot_values Purpose: Set the previous seedlot values used for validations. Pass p_force=TRUE to force get of latest values when they have already been retrieved. ******************************************************************************/ PROCEDURE get_previous_seedlot_values (p_force IN BOOLEAN DEFAULT FALSE) IS CURSOR c_previous IS SELECT * FROM seedlot WHERE seedlot_number = g_seedlot_number; BEGIN --If record is empty or caller specified FORCE option IF r_previous.seedlot_number IS NULL OR p_force THEN OPEN c_previous; FETCH c_previous INTO r_previous; CLOSE c_previous; --Don't populate old values into vars if seedlot was not found IF g_seedlot_number = r_previous.seedlot_number THEN --Populate vars with previous values (don't get revision_count) g_seed_plan_zone_code := r_previous.seed_plan_zone_code; g_applicant_client_locn := r_previous.applicant_client_locn; g_applicant_client_number := r_previous.applicant_client_number; g_applicant_email_address := r_previous.applicant_email_address; g_bc_source_ind := r_previous.bc_source_ind; g_biotech_processes_ind := r_previous.biotech_processes_ind; g_collection_area_radius := r_previous.collection_area_radius; g_collection_bgc_ind := r_previous.collection_bgc_ind; g_collection_spz_ind := r_previous.collection_seed_plan_zone_ind; g_coll_standard_met_ind := r_previous.collection_standard_met_ind; g_cone_collection_method2_cd := r_previous.cone_collection_method2_code; g_contaminant_pollen_bv := r_previous.contaminant_pollen_bv; g_controlled_cross_ind := r_previous.controlled_cross_ind; g_declared_userid := r_previous.declared_userid; g_declared_timestamp := r_previous.declared_timestamp; g_female_gametic_mthd_code := r_previous.female_gametic_mthd_code; g_latitude_sec_max := r_previous.latitude_sec_max; g_latitude_sec_min := r_previous.latitude_sec_min; g_longitude_sec_max := r_previous.longitude_sec_max; g_longitude_sec_min := r_previous.longitude_sec_min; g_male_gametic_mthd_code := r_previous.male_gametic_mthd_code; g_orchard_comment := r_previous.orchard_comment; g_orchard_contamination_pct := r_previous.orchard_contamination_pct; g_pollen_contamination_ind := r_previous.pollen_contamination_ind; g_pollen_contam_mthd_code := r_previous.pollen_contamination_mthd_code; g_pollen_contamination_pct := r_previous.pollen_contamination_pct; g_provenance_id := r_previous.provenance_id; g_secondary_orchard_id := r_previous.secondary_orchard_id; g_seed_plan_unit_id := r_previous.seed_plan_unit_id; g_seed_store_client_locn := r_previous.seed_store_client_locn; g_seed_store_client_number := r_previous.seed_store_client_number; g_seedlot_source_code := r_previous.seedlot_source_code; g_smp_mean_bv_growth := r_previous.smp_mean_bv_growth; g_smp_parents_outside := r_previous.smp_parents_outside; g_smp_success_pct := r_previous.smp_success_pct; g_temporary_storage_end_date := r_previous.temporary_storage_end_date; g_temporary_storage_start_dt := r_previous.temporary_storage_start_date; g_total_parent_trees := r_previous.total_parent_trees; g_latitude_seconds := r_previous.latitude_seconds; g_longitude_seconds := r_previous.longitude_seconds; g_collection_lat_sec := r_previous.collection_lat_sec; g_collection_long_sec := r_previous.collection_long_sec; g_seedlot_status_code := r_previous.seedlot_status_code; g_vegetation_code := r_previous.vegetation_code; g_genetic_class_code := r_previous.genetic_class_code; g_collection_source_code := r_previous.collection_source_code; g_superior_prvnc_ind := r_previous.superior_prvnc_ind; g_org_unit_no := r_previous.org_unit_no; g_registered_seed_ind := r_previous.registered_seed_ind; g_to_be_registrd_ind := r_previous.to_be_registrd_ind; g_registered_date := r_previous.registered_date; g_fs721a_signed_ind := r_previous.fs721a_signed_ind; g_nad_datum_code := r_previous.nad_datum_code; g_utm_zone := r_previous.utm_zone; g_utm_easting := r_previous.utm_easting; g_utm_northing := r_previous.utm_northing; g_longitude_degrees := r_previous.longitude_degrees; g_longitude_minutes := r_previous.longitude_minutes; g_longitude_deg_min := r_previous.longitude_deg_min; g_longitude_min_min := r_previous.longitude_min_min; g_longitude_deg_max := r_previous.longitude_deg_max; g_longitude_min_max := r_previous.longitude_min_max; g_latitude_degrees := r_previous.latitude_degrees; g_latitude_minutes := r_previous.latitude_minutes; g_latitude_deg_min := r_previous.latitude_deg_min; g_latitude_min_min := r_previous.latitude_min_min; g_latitude_deg_max := r_previous.latitude_deg_max; g_latitude_min_max := r_previous.latitude_min_max; g_seed_coast_area_code := r_previous.seed_coast_area_code; g_elevation := r_previous.elevation; g_elevation_min := r_previous.elevation_min; g_elevation_max := r_previous.elevation_max; g_orchard_id := r_previous.orchard_id; g_collection_locn_desc := r_previous.collection_locn_desc; g_collection_cli_number := r_previous.collection_cli_number; g_collection_cli_locn_cd := r_previous.collection_cli_locn_cd; g_collection_start_date := r_previous.collection_start_date; g_collection_end_date := r_previous.collection_end_date; g_cone_collection_method_cd := r_previous.cone_collection_method_code; g_no_of_containers := r_previous.no_of_containers; g_clctn_volume := r_previous.clctn_volume; g_vol_per_container := r_previous.vol_per_container; g_nmbr_trees_from_code := r_previous.nmbr_trees_from_code; g_coancestry := r_previous.coancestry; g_effective_pop_size := r_previous.effective_pop_size; g_original_seed_qty := r_previous.original_seed_qty; g_interm_strg_client_number := r_previous.interm_strg_client_number; g_interm_strg_client_locn := r_previous.interm_strg_client_locn; g_interm_strg_st_date := r_previous.interm_strg_st_date; g_interm_strg_end_date := r_previous.interm_strg_end_date; g_interm_facility_code := r_previous.interm_facility_code; g_extraction_st_date := r_previous.extraction_st_date; g_extraction_end_date := r_previous.extraction_end_date; g_extraction_volume := r_previous.extraction_volume; g_extrct_cli_number := r_previous.extrct_cli_number; g_extrct_cli_locn_cd := r_previous.extrct_cli_locn_cd; g_stored_cli_number := r_previous.stored_cli_number; g_stored_cli_locn_cd := r_previous.stored_cli_locn_cd; g_lngterm_strg_st_date := r_previous.lngterm_strg_st_date; g_historical_tsr_date := r_previous.historical_tsr_date; g_collection_lat_deg := r_previous.collection_lat_deg; g_collection_lat_min := r_previous.collection_lat_min; g_collection_latitude_code := r_previous.collection_latitude_code; g_collection_long_deg := r_previous.collection_long_deg; g_collection_long_min := r_previous.collection_long_min; g_collection_longitude_code := r_previous.collection_longitude_code; g_collection_elevation := r_previous.collection_elevation; g_collection_elevation_min := r_previous.collection_elevation_min; g_collection_elevation_max := r_previous.collection_elevation_max; g_entry_timestamp := r_previous.entry_timestamp; g_entry_userid := r_previous.entry_userid; g_update_timestamp := r_previous.update_timestamp; g_update_userid := r_previous.update_userid; g_approved_timestamp := r_previous.approved_timestamp; g_approved_userid := r_previous.approved_userid; g_interm_strg_locn := r_previous.interm_strg_locn; g_interm_strg_cmt := r_previous.interm_strg_cmt; g_ownership_comment := r_previous.ownership_comment; g_cone_seed_desc := r_previous.cone_seed_desc; g_extraction_comment := r_previous.extraction_comment; g_seedlot_comment := r_previous.seedlot_comment; g_bgc_zone_code := r_previous.bgc_zone_code; g_bgc_subzone_code := r_previous.bgc_subzone_code; g_variant := r_previous.variant; g_bec_version_id := r_previous.bec_version_id; --get spz's g_spz_list := get_seedlot_spz_list(g_seedlot_number); END IF; -- if g_seedlot_number = previous seedlot_number END IF; END get_previous_seedlot_values; /****************************************************************************** Procedure: init Purpose: Initialize member variables ******************************************************************************/ PROCEDURE init(p_seedlot_number IN VARCHAR2 DEFAULT NULL) IS BEGIN g_error_message := NULL; g_seed_plan_zone_code := NULL; gb_seed_plan_zone_code := 'N'; g_applicant_client_locn := NULL; gb_applicant_client_locn := 'N'; g_applicant_client_number := NULL; gb_applicant_client_number := 'N'; g_applicant_email_address := NULL; gb_applicant_email_address := 'N'; g_bc_source_ind := NULL; gb_bc_source_ind := 'N'; g_biotech_processes_ind := NULL; gb_biotech_processes_ind := 'N'; g_collection_area_radius := NULL; gb_collection_area_radius := 'N'; g_collection_bgc_ind := NULL; gb_collection_bgc_ind := 'N'; g_collection_spz_ind := NULL; gb_collection_spz_ind := 'N'; g_coll_standard_met_ind := NULL; gb_coll_standard_met_ind := 'N'; g_cone_collection_method2_cd := NULL; gb_cone_collection_method2_cd := 'N'; g_contaminant_pollen_bv := NULL; gb_contaminant_pollen_bv := 'N'; g_controlled_cross_ind := NULL; gb_controlled_cross_ind := 'N'; g_declared_userid := NULL; gb_declared_userid := 'N'; g_declared_timestamp := NULL; gb_declared_timestamp := 'N'; g_female_gametic_mthd_code := NULL; gb_female_gametic_mthd_code := 'N'; g_latitude_sec_max := NULL; gb_latitude_sec_max := 'N'; g_latitude_sec_min := NULL; gb_latitude_sec_min := 'N'; g_longitude_sec_max := NULL; gb_longitude_sec_max := 'N'; g_longitude_sec_min := NULL; gb_longitude_sec_min := 'N'; g_male_gametic_mthd_code := NULL; gb_male_gametic_mthd_code := 'N'; g_orchard_comment := NULL; gb_orchard_comment := 'N'; g_orchard_contamination_pct := NULL; gb_orchard_contamination_pct := 'N'; g_pollen_contamination_ind := NULL; gb_pollen_contamination_ind := 'N'; g_pollen_contam_mthd_code := NULL; gb_pollen_contam_mthd_code := 'N'; g_pollen_contamination_pct := NULL; gb_pollen_contamination_pct := 'N'; g_provenance_id := NULL; gb_provenance_id := 'N'; g_secondary_orchard_id := NULL; gb_secondary_orchard_id := 'N'; g_seed_plan_unit_id := NULL; gb_seed_plan_unit_id := 'N'; g_seed_store_client_locn := NULL; gb_seed_store_client_locn := 'N'; g_seed_store_client_number := NULL; gb_seed_store_client_number := 'N'; g_seedlot_source_code := NULL; gb_seedlot_source_code := 'N'; g_smp_mean_bv_growth := NULL; gb_smp_mean_bv_growth := 'N'; g_smp_mean_bv_density := NULL; gb_smp_mean_bv_density := 'N'; g_smp_mean_bv_resistance := NULL; gb_smp_mean_bv_resistance := 'N'; g_smp_mean_bv_major_gene := NULL; gb_smp_mean_bv_major_gene := 'N'; g_smp_parents_outside := NULL; gb_smp_parents_outside := 'N'; g_smp_success_pct := NULL; gb_smp_success_pct := 'N'; g_temporary_storage_end_date := NULL; gb_temporary_storage_end_date := 'N'; g_temporary_storage_start_dt := NULL; gb_temporary_storage_start_dt := 'N'; g_total_parent_trees := NULL; gb_total_parent_trees := 'N'; g_latitude_seconds := NULL; gb_latitude_seconds := 'N'; g_longitude_seconds := NULL; gb_longitude_seconds := 'N'; g_collection_lat_sec := NULL; gb_collection_lat_sec := 'N'; g_collection_long_sec := NULL; gb_collection_long_sec := 'N'; g_seedlot_number := NULL; gb_seedlot_number := 'N'; g_seedlot_status_code := NULL; gb_seedlot_status_code := 'N'; g_vegetation_code := NULL; gb_vegetation_code := 'N'; g_genetic_class_code := NULL; gb_genetic_class_code := 'N'; g_collection_source_code := NULL; gb_collection_source_code := 'N'; g_superior_prvnc_ind := NULL; gb_superior_prvnc_ind := 'N'; g_org_unit_no := NULL; gb_org_unit_no := 'N'; g_registered_seed_ind := NULL; gb_registered_seed_ind := 'N'; g_to_be_registrd_ind := NULL; gb_to_be_registrd_ind := 'N'; g_registered_date := NULL; gb_registered_date := 'N'; g_fs721a_signed_ind := NULL; gb_fs721a_signed_ind := 'N'; g_nad_datum_code := NULL; gb_nad_datum_code := 'N'; g_utm_zone := NULL; gb_utm_zone := 'N'; g_utm_easting := NULL; gb_utm_easting := 'N'; g_utm_northing := NULL; gb_utm_northing := 'N'; g_longitude_degrees := NULL; gb_longitude_degrees := 'N'; g_longitude_minutes := NULL; gb_longitude_minutes := 'N'; g_longitude_deg_min := NULL; gb_longitude_deg_min := 'N'; g_longitude_min_min := NULL; gb_longitude_min_min := 'N'; g_longitude_deg_max := NULL; gb_longitude_deg_max := 'N'; g_longitude_min_max := NULL; gb_longitude_min_max := 'N'; g_latitude_degrees := NULL; gb_latitude_degrees := 'N'; g_latitude_minutes := NULL; gb_latitude_minutes := 'N'; g_latitude_deg_min := NULL; gb_latitude_deg_min := 'N'; g_latitude_min_min := NULL; gb_latitude_min_min := 'N'; g_latitude_deg_max := NULL; gb_latitude_deg_max := 'N'; g_latitude_min_max := NULL; gb_latitude_min_max := 'N'; g_seed_coast_area_code := NULL; gb_seed_coast_area_code := 'N'; g_elevation := NULL; gb_elevation := 'N'; g_elevation_min := NULL; gb_elevation_min := 'N'; g_elevation_max := NULL; gb_elevation_max := 'N'; g_orchard_id := NULL; gb_orchard_id := 'N'; g_collection_locn_desc := NULL; gb_collection_locn_desc := 'N'; g_collection_cli_number := NULL; gb_collection_cli_number := 'N'; g_collection_cli_locn_cd := NULL; gb_collection_cli_locn_cd := 'N'; g_collection_start_date := NULL; gb_collection_start_date := 'N'; g_collection_end_date := NULL; gb_collection_end_date := 'N'; g_cone_collection_method_cd := NULL; gb_cone_collection_method_cd := 'N'; g_no_of_containers := NULL; gb_no_of_containers := 'N'; g_clctn_volume := NULL; gb_clctn_volume := 'N'; g_vol_per_container := NULL; gb_vol_per_container := 'N'; g_nmbr_trees_from_code := NULL; gb_nmbr_trees_from_code := 'N'; g_coancestry := NULL; gb_coancestry := 'N'; g_effective_pop_size := NULL; gb_effective_pop_size := 'N'; g_original_seed_qty := NULL; gb_original_seed_qty := 'N'; g_interm_strg_client_number := NULL; gb_interm_strg_client_number := 'N'; g_interm_strg_client_locn := NULL; gb_interm_strg_client_locn := 'N'; g_interm_strg_st_date := NULL; gb_interm_strg_st_date := 'N'; g_interm_strg_end_date := NULL; gb_interm_strg_end_date := 'N'; g_interm_facility_code := NULL; gb_interm_facility_code := 'N'; g_extraction_st_date := NULL; gb_extraction_st_date := 'N'; g_extraction_end_date := NULL; gb_extraction_end_date := 'N'; g_extraction_volume := NULL; gb_extraction_volume := 'N'; g_extrct_cli_number := NULL; gb_extrct_cli_number := 'N'; g_extrct_cli_locn_cd := NULL; gb_extrct_cli_locn_cd := 'N'; g_stored_cli_number := NULL; gb_stored_cli_number := 'N'; g_stored_cli_locn_cd := NULL; gb_stored_cli_locn_cd := 'N'; g_lngterm_strg_st_date := NULL; gb_lngterm_strg_st_date := 'N'; g_historical_tsr_date := NULL; gb_historical_tsr_date := 'N'; g_collection_lat_deg := NULL; gb_collection_lat_deg := 'N'; g_collection_lat_min := NULL; gb_collection_lat_min := 'N'; g_collection_latitude_code := NULL; gb_collection_latitude_code := 'N'; g_collection_long_deg := NULL; gb_collection_long_deg := 'N'; g_collection_long_min := NULL; gb_collection_long_min := 'N'; g_collection_longitude_code := NULL; gb_collection_longitude_code := 'N'; g_collection_elevation := NULL; gb_collection_elevation := 'N'; g_collection_elevation_min := NULL; gb_collection_elevation_min := 'N'; g_collection_elevation_max := NULL; gb_collection_elevation_max := 'N'; g_entry_timestamp := NULL; gb_entry_timestamp := 'N'; g_entry_userid := NULL; gb_entry_userid := 'N'; g_update_timestamp := NULL; gb_update_timestamp := 'N'; g_update_userid := NULL; gb_update_userid := 'N'; g_approved_timestamp := NULL; gb_approved_timestamp := 'N'; g_approved_userid := NULL; gb_approved_userid := 'N'; g_revision_count := NULL; gb_revision_count := 'N'; g_interm_strg_locn := NULL; gb_interm_strg_locn := 'N'; g_interm_strg_cmt := NULL; gb_interm_strg_cmt := 'N'; g_ownership_comment := NULL; gb_ownership_comment := 'N'; g_cone_seed_desc := NULL; gb_cone_seed_desc := 'N'; g_extraction_comment := NULL; gb_extraction_comment := 'N'; g_seedlot_comment := NULL; gb_seedlot_comment := 'N'; g_bgc_zone_code := NULL; gb_bgc_zone_code := 'N'; g_bgc_subzone_code := NULL; gb_bgc_subzone_code := 'N'; g_variant := NULL; gb_variant := 'N'; g_bec_version_id := NULL; gb_bec_version_id := 'N'; --Related information g_gw_g := NULL; g_gw_d := NULL; g_gw_r := NULL; g_gw_m := NULL; g_spz_list := NULL; g_tested_parent_trees_pct := NULL; g_untested_parent_trees_pct := NULL; g_is_lot_split := NULL; --Record types r_previous := NULL; r_pt_contrib := NULL; IF p_seedlot_number IS NOT NULL THEN g_seedlot_number := p_seedlot_number; get_previous_seedlot_values(TRUE); END IF; END init; --***START GETTERS --error raised? FUNCTION error_raised RETURN BOOLEAN IS BEGIN RETURN (g_error_message IS NOT NULL); END error_raised; --get error message FUNCTION get_error_message RETURN VARCHAR2 IS BEGIN RETURN g_error_message; END get_error_message; --get seed_plan_zone_code FUNCTION get_seed_plan_zone_code RETURN VARCHAR2 IS BEGIN RETURN g_seed_plan_zone_code; END get_seed_plan_zone_code; --get applicant_client_locn FUNCTION get_applicant_client_locn RETURN VARCHAR2 IS BEGIN RETURN g_applicant_client_locn; END get_applicant_client_locn; --get applicant_client_number FUNCTION get_applicant_client_number RETURN VARCHAR2 IS BEGIN RETURN g_applicant_client_number; END get_applicant_client_number; --get applicant_email_address FUNCTION get_applicant_email_address RETURN VARCHAR2 IS BEGIN RETURN g_applicant_email_address; END get_applicant_email_address; --get bc_source_ind FUNCTION get_bc_source_ind RETURN VARCHAR2 IS BEGIN RETURN g_bc_source_ind; END get_bc_source_ind; --get biotech_processes_ind FUNCTION get_biotech_processes_ind RETURN VARCHAR2 IS BEGIN RETURN g_biotech_processes_ind; END get_biotech_processes_ind; --get collection_area_radius FUNCTION get_collection_area_radius RETURN NUMBER IS BEGIN RETURN g_collection_area_radius; END get_collection_area_radius; --get collection_bgc_ind FUNCTION get_collection_bgc_ind RETURN VARCHAR2 IS BEGIN RETURN g_collection_bgc_ind; END get_collection_bgc_ind; --get collection_seed_plan_zone_ind FUNCTION get_collection_spz_ind RETURN VARCHAR2 IS BEGIN RETURN g_collection_spz_ind; END get_collection_spz_ind; --get collection_standard_met_ind FUNCTION get_coll_standard_met_ind RETURN VARCHAR2 IS BEGIN RETURN g_coll_standard_met_ind; END get_coll_standard_met_ind; --get cone_collection_method2_code FUNCTION get_cone_collection_method2_cd RETURN VARCHAR2 IS BEGIN RETURN g_cone_collection_method2_cd; END get_cone_collection_method2_cd; --get contaminant_pollen_bv FUNCTION get_contaminant_pollen_bv RETURN NUMBER IS BEGIN RETURN g_contaminant_pollen_bv; END get_contaminant_pollen_bv; --get controlled_cross_ind FUNCTION get_controlled_cross_ind RETURN VARCHAR2 IS BEGIN RETURN g_controlled_cross_ind; END get_controlled_cross_ind; --get declared_userid FUNCTION get_declared_userid RETURN VARCHAR2 IS BEGIN RETURN g_declared_userid; END get_declared_userid; --get declared_timestamp FUNCTION get_declared_timestamp RETURN DATE IS BEGIN RETURN g_declared_timestamp; END get_declared_timestamp; --get female_gametic_mthd_code FUNCTION get_female_gametic_mthd_code RETURN VARCHAR2 IS BEGIN RETURN g_female_gametic_mthd_code; END get_female_gametic_mthd_code; --get latitude_sec_max FUNCTION get_latitude_sec_max RETURN NUMBER IS BEGIN RETURN g_latitude_sec_max; END get_latitude_sec_max; --get latitude_sec_min FUNCTION get_latitude_sec_min RETURN NUMBER IS BEGIN RETURN g_latitude_sec_min; END get_latitude_sec_min; --get longitude_sec_max FUNCTION get_longitude_sec_max RETURN NUMBER IS BEGIN RETURN g_longitude_sec_max; END get_longitude_sec_max; --get longitude_sec_min FUNCTION get_longitude_sec_min RETURN NUMBER IS BEGIN RETURN g_longitude_sec_min; END get_longitude_sec_min; --get male_gametic_mthd_code FUNCTION get_male_gametic_mthd_code RETURN VARCHAR2 IS BEGIN RETURN g_male_gametic_mthd_code; END get_male_gametic_mthd_code; --get orchard_comment FUNCTION get_orchard_comment RETURN VARCHAR2 IS BEGIN RETURN g_orchard_comment; END get_orchard_comment; --get orchard_contamination_pct FUNCTION get_orchard_contamination_pct RETURN NUMBER IS BEGIN RETURN g_orchard_contamination_pct; END get_orchard_contamination_pct; --get pollen_contamination_ind FUNCTION get_pollen_contamination_ind RETURN VARCHAR2 IS BEGIN RETURN g_pollen_contamination_ind; END get_pollen_contamination_ind; --get pollen_contamination_mthd_code FUNCTION get_pollen_contam_mthd_code RETURN VARCHAR2 IS BEGIN RETURN g_pollen_contam_mthd_code; END get_pollen_contam_mthd_code; --get pollen_contamination_pct FUNCTION get_pollen_contamination_pct RETURN NUMBER IS BEGIN RETURN g_pollen_contamination_pct; END get_pollen_contamination_pct; --get provenance_id FUNCTION get_provenance_id RETURN NUMBER IS BEGIN RETURN g_provenance_id; END get_provenance_id; --get secondary_orchard_id FUNCTION get_secondary_orchard_id RETURN VARCHAR2 IS BEGIN RETURN g_secondary_orchard_id; END get_secondary_orchard_id; --get seed_plan_unit_id FUNCTION get_seed_plan_unit_id RETURN NUMBER IS BEGIN RETURN g_seed_plan_unit_id; END get_seed_plan_unit_id; --get seed_store_client_locn FUNCTION get_seed_store_client_locn RETURN VARCHAR2 IS BEGIN RETURN g_seed_store_client_locn; END get_seed_store_client_locn; --get seed_store_client_number FUNCTION get_seed_store_client_number RETURN VARCHAR2 IS BEGIN RETURN g_seed_store_client_number; END get_seed_store_client_number; --get seedlot_source_code FUNCTION get_seedlot_source_code RETURN VARCHAR2 IS BEGIN RETURN g_seedlot_source_code; END get_seedlot_source_code; --get smp_mean_bv_growth FUNCTION get_smp_mean_bv_growth RETURN VARCHAR2 IS BEGIN RETURN g_smp_mean_bv_growth; END get_smp_mean_bv_growth; --get smp_mean_bv_density FUNCTION get_smp_mean_bv_density RETURN VARCHAR2 IS BEGIN RETURN g_smp_mean_bv_density; END get_smp_mean_bv_density; --get smp_mean_bv_resistance FUNCTION get_smp_mean_bv_resistance RETURN VARCHAR2 IS BEGIN RETURN g_smp_mean_bv_resistance; END get_smp_mean_bv_resistance; --get smp_mean_bv_major_gene FUNCTION get_smp_mean_bv_major_gene RETURN VARCHAR2 IS BEGIN RETURN g_smp_mean_bv_major_gene; END get_smp_mean_bv_major_gene; --get smp_parents_outside FUNCTION get_smp_parents_outside RETURN NUMBER IS BEGIN RETURN g_smp_parents_outside; END get_smp_parents_outside; --get smp_success_pct FUNCTION get_smp_success_pct RETURN NUMBER IS BEGIN RETURN g_smp_success_pct; END get_smp_success_pct; --get temporary_storage_end_date FUNCTION get_temporary_storage_end_date RETURN DATE IS BEGIN RETURN g_temporary_storage_end_date; END get_temporary_storage_end_date; --get temporary_storage_start_date FUNCTION get_temporary_storage_start_dt RETURN DATE IS BEGIN RETURN g_temporary_storage_start_dt; END get_temporary_storage_start_dt; --get total_parent_trees FUNCTION get_total_parent_trees RETURN NUMBER IS BEGIN RETURN g_total_parent_trees; END get_total_parent_trees; --get latitude_seconds FUNCTION get_latitude_seconds RETURN NUMBER IS BEGIN RETURN g_latitude_seconds; END get_latitude_seconds; --get longitude_seconds FUNCTION get_longitude_seconds RETURN NUMBER IS BEGIN RETURN g_longitude_seconds; END get_longitude_seconds; --get collection_lat_sec FUNCTION get_collection_lat_sec RETURN NUMBER IS BEGIN RETURN g_collection_lat_sec; END get_collection_lat_sec; --get collection_long_sec FUNCTION get_collection_long_sec RETURN NUMBER IS BEGIN RETURN g_collection_long_sec; END get_collection_long_sec; --get seedlot_number FUNCTION get_seedlot_number RETURN VARCHAR2 IS BEGIN RETURN g_seedlot_number; END get_seedlot_number; --get seedlot_status_code FUNCTION get_seedlot_status_code RETURN VARCHAR2 IS BEGIN RETURN g_seedlot_status_code; END get_seedlot_status_code; --get vegetation_code FUNCTION get_vegetation_code RETURN VARCHAR2 IS BEGIN RETURN g_vegetation_code; END get_vegetation_code; --get genetic_class_code FUNCTION get_genetic_class_code RETURN VARCHAR2 IS BEGIN RETURN g_genetic_class_code; END get_genetic_class_code; --get collection_source_code FUNCTION get_collection_source_code RETURN VARCHAR2 IS BEGIN RETURN g_collection_source_code; END get_collection_source_code; --get superior_prvnc_ind FUNCTION get_superior_prvnc_ind RETURN VARCHAR2 IS BEGIN RETURN g_superior_prvnc_ind; END get_superior_prvnc_ind; --get org_unit_no FUNCTION get_org_unit_no RETURN NUMBER IS BEGIN RETURN g_org_unit_no; END get_org_unit_no; --get registered_seed_ind FUNCTION get_registered_seed_ind RETURN VARCHAR2 IS BEGIN RETURN g_registered_seed_ind; END get_registered_seed_ind; --get to_be_registrd_ind FUNCTION get_to_be_registrd_ind RETURN VARCHAR2 IS BEGIN RETURN g_to_be_registrd_ind; END get_to_be_registrd_ind; --get registered_date FUNCTION get_registered_date RETURN DATE IS BEGIN RETURN g_registered_date; END get_registered_date; --get fs721a_signed_ind FUNCTION get_fs721a_signed_ind RETURN VARCHAR2 IS BEGIN RETURN g_fs721a_signed_ind; END get_fs721a_signed_ind; --get nad_datum_code FUNCTION get_nad_datum_code RETURN VARCHAR2 IS BEGIN RETURN g_nad_datum_code; END get_nad_datum_code; --get utm_zone FUNCTION get_utm_zone RETURN NUMBER IS BEGIN RETURN g_utm_zone; END get_utm_zone; --get utm_easting FUNCTION get_utm_easting RETURN NUMBER IS BEGIN RETURN g_utm_easting; END get_utm_easting; --get utm_northing FUNCTION get_utm_northing RETURN NUMBER IS BEGIN RETURN g_utm_northing; END get_utm_northing; --get longitude_degrees FUNCTION get_longitude_degrees RETURN NUMBER IS BEGIN RETURN g_longitude_degrees; END get_longitude_degrees; --get longitude_minutes FUNCTION get_longitude_minutes RETURN NUMBER IS BEGIN RETURN g_longitude_minutes; END get_longitude_minutes; --get longitude_deg_min FUNCTION get_longitude_deg_min RETURN NUMBER IS BEGIN RETURN g_longitude_deg_min; END get_longitude_deg_min; --get longitude_min_min FUNCTION get_longitude_min_min RETURN NUMBER IS BEGIN RETURN g_longitude_min_min; END get_longitude_min_min; --get longitude_deg_max FUNCTION get_longitude_deg_max RETURN NUMBER IS BEGIN RETURN g_longitude_deg_max; END get_longitude_deg_max; --get longitude_min_max FUNCTION get_longitude_min_max RETURN NUMBER IS BEGIN RETURN g_longitude_min_max; END get_longitude_min_max; --get latitude_degrees FUNCTION get_latitude_degrees RETURN NUMBER IS BEGIN RETURN g_latitude_degrees; END get_latitude_degrees; --get latitude_minutes FUNCTION get_latitude_minutes RETURN NUMBER IS BEGIN RETURN g_latitude_minutes; END get_latitude_minutes; --get latitude_deg_min FUNCTION get_latitude_deg_min RETURN NUMBER IS BEGIN RETURN g_latitude_deg_min; END get_latitude_deg_min; --get latitude_min_min FUNCTION get_latitude_min_min RETURN NUMBER IS BEGIN RETURN g_latitude_min_min; END get_latitude_min_min; --get latitude_deg_max FUNCTION get_latitude_deg_max RETURN NUMBER IS BEGIN RETURN g_latitude_deg_max; END get_latitude_deg_max; --get latitude_min_max FUNCTION get_latitude_min_max RETURN NUMBER IS BEGIN RETURN g_latitude_min_max; END get_latitude_min_max; --get seed_coast_area_code FUNCTION get_seed_coast_area_code RETURN VARCHAR2 IS BEGIN RETURN g_seed_coast_area_code; END get_seed_coast_area_code; --get elevation FUNCTION get_elevation RETURN NUMBER IS BEGIN RETURN g_elevation; END get_elevation; --get elevation_min FUNCTION get_elevation_min RETURN NUMBER IS BEGIN RETURN g_elevation_min; END get_elevation_min; --get elevation_max FUNCTION get_elevation_max RETURN NUMBER IS BEGIN RETURN g_elevation_max; END get_elevation_max; --get orchard_id FUNCTION get_orchard_id RETURN VARCHAR2 IS BEGIN RETURN g_orchard_id; END get_orchard_id; --get collection_locn_desc FUNCTION get_collection_locn_desc RETURN VARCHAR2 IS BEGIN RETURN g_collection_locn_desc; END get_collection_locn_desc; --get collection_cli_number FUNCTION get_collection_cli_number RETURN VARCHAR2 IS BEGIN RETURN g_collection_cli_number; END get_collection_cli_number; --get collection_cli_locn_cd FUNCTION get_collection_cli_locn_cd RETURN VARCHAR2 IS BEGIN RETURN g_collection_cli_locn_cd; END get_collection_cli_locn_cd; --get collection_start_date FUNCTION get_collection_start_date RETURN DATE IS BEGIN RETURN g_collection_start_date; END get_collection_start_date; --get collection_end_date FUNCTION get_collection_end_date RETURN DATE IS BEGIN RETURN g_collection_end_date; END get_collection_end_date; --get cone_collection_method_code FUNCTION get_cone_collection_method_cd RETURN VARCHAR2 IS BEGIN RETURN g_cone_collection_method_cd; END get_cone_collection_method_cd; --get no_of_containers FUNCTION get_no_of_containers RETURN NUMBER IS BEGIN RETURN g_no_of_containers; END get_no_of_containers; --get clctn_volume FUNCTION get_clctn_volume RETURN NUMBER IS BEGIN RETURN g_clctn_volume; END get_clctn_volume; --get vol_per_container FUNCTION get_vol_per_container RETURN NUMBER IS BEGIN RETURN g_vol_per_container; END get_vol_per_container; --get nmbr_trees_from_code FUNCTION get_nmbr_trees_from_code RETURN VARCHAR2 IS BEGIN RETURN g_nmbr_trees_from_code; END get_nmbr_trees_from_code; --get coancestry FUNCTION get_coancestry RETURN NUMBER IS BEGIN RETURN g_coancestry; END get_coancestry; --get effective_pop_size FUNCTION get_effective_pop_size RETURN NUMBER IS BEGIN RETURN g_effective_pop_size; END get_effective_pop_size; --get original_seed_qty FUNCTION get_original_seed_qty RETURN NUMBER IS BEGIN RETURN g_original_seed_qty; END get_original_seed_qty; --get interm_strg_client_number FUNCTION get_interm_strg_client_number RETURN VARCHAR2 IS BEGIN RETURN g_interm_strg_client_number; END get_interm_strg_client_number; --get interm_strg_client_locn FUNCTION get_interm_strg_client_locn RETURN VARCHAR2 IS BEGIN RETURN g_interm_strg_client_locn; END get_interm_strg_client_locn; --get interm_strg_st_date FUNCTION get_interm_strg_st_date RETURN DATE IS BEGIN RETURN g_interm_strg_st_date; END get_interm_strg_st_date; --get interm_strg_end_date FUNCTION get_interm_strg_end_date RETURN DATE IS BEGIN RETURN g_interm_strg_end_date; END get_interm_strg_end_date; --get interm_facility_code FUNCTION get_interm_facility_code RETURN VARCHAR2 IS BEGIN RETURN g_interm_facility_code; END get_interm_facility_code; --get extraction_st_date FUNCTION get_extraction_st_date RETURN DATE IS BEGIN RETURN g_extraction_st_date; END get_extraction_st_date; --get extraction_end_date FUNCTION get_extraction_end_date RETURN DATE IS BEGIN RETURN g_extraction_end_date; END get_extraction_end_date; --get extraction_volume FUNCTION get_extraction_volume RETURN NUMBER IS BEGIN RETURN g_extraction_volume; END get_extraction_volume; --get extrct_cli_number FUNCTION get_extrct_cli_number RETURN VARCHAR2 IS BEGIN RETURN g_extrct_cli_number; END get_extrct_cli_number; --get extrct_cli_locn_cd FUNCTION get_extrct_cli_locn_cd RETURN VARCHAR2 IS BEGIN RETURN g_extrct_cli_locn_cd; END get_extrct_cli_locn_cd; --get stored_cli_number FUNCTION get_stored_cli_number RETURN VARCHAR2 IS BEGIN RETURN g_stored_cli_number; END get_stored_cli_number; --get stored_cli_locn_cd FUNCTION get_stored_cli_locn_cd RETURN VARCHAR2 IS BEGIN RETURN g_stored_cli_locn_cd; END get_stored_cli_locn_cd; --get lngterm_strg_st_date FUNCTION get_lngterm_strg_st_date RETURN DATE IS BEGIN RETURN g_lngterm_strg_st_date; END get_lngterm_strg_st_date; --get historical_tsr_date FUNCTION get_historical_tsr_date RETURN DATE IS BEGIN RETURN g_historical_tsr_date; END get_historical_tsr_date; --get collection_lat_deg FUNCTION get_collection_lat_deg RETURN NUMBER IS BEGIN RETURN g_collection_lat_deg; END get_collection_lat_deg; --get collection_lat_min FUNCTION get_collection_lat_min RETURN NUMBER IS BEGIN RETURN g_collection_lat_min; END get_collection_lat_min; --get collection_latitude_code FUNCTION get_collection_latitude_code RETURN VARCHAR2 IS BEGIN RETURN g_collection_latitude_code; END get_collection_latitude_code; --get collection_long_deg FUNCTION get_collection_long_deg RETURN NUMBER IS BEGIN RETURN g_collection_long_deg; END get_collection_long_deg; --get collection_long_min FUNCTION get_collection_long_min RETURN NUMBER IS BEGIN RETURN g_collection_long_min; END get_collection_long_min; --get collection_longitude_code FUNCTION get_collection_longitude_code RETURN VARCHAR2 IS BEGIN RETURN g_collection_longitude_code; END get_collection_longitude_code; --get collection_elevation FUNCTION get_collection_elevation RETURN NUMBER IS BEGIN RETURN g_collection_elevation; END get_collection_elevation; --get collection_elevation_min FUNCTION get_collection_elevation_min RETURN NUMBER IS BEGIN RETURN g_collection_elevation_min; END get_collection_elevation_min; --get collection_elevation_max FUNCTION get_collection_elevation_max RETURN NUMBER IS BEGIN RETURN g_collection_elevation_max; END get_collection_elevation_max; --get entry_timestamp FUNCTION get_entry_timestamp RETURN DATE IS BEGIN RETURN g_entry_timestamp; END get_entry_timestamp; --get entry_userid FUNCTION get_entry_userid RETURN VARCHAR2 IS BEGIN RETURN g_entry_userid; END get_entry_userid; --get update_timestamp FUNCTION get_update_timestamp RETURN DATE IS BEGIN RETURN g_update_timestamp; END get_update_timestamp; --get update_userid FUNCTION get_update_userid RETURN VARCHAR2 IS BEGIN RETURN g_update_userid; END get_update_userid; --get approved_timestamp FUNCTION get_approved_timestamp RETURN DATE IS BEGIN RETURN g_approved_timestamp; END get_approved_timestamp; --get approved_userid FUNCTION get_approved_userid RETURN VARCHAR2 IS BEGIN RETURN g_approved_userid; END get_approved_userid; --get revision_count FUNCTION get_revision_count RETURN NUMBER IS BEGIN RETURN g_revision_count; END get_revision_count; --get interm_strg_locn FUNCTION get_interm_strg_locn RETURN VARCHAR2 IS BEGIN RETURN g_interm_strg_locn; END get_interm_strg_locn; --get interm_strg_cmt FUNCTION get_interm_strg_cmt RETURN VARCHAR2 IS BEGIN RETURN g_interm_strg_cmt; END get_interm_strg_cmt; --get ownership_comment FUNCTION get_ownership_comment RETURN VARCHAR2 IS BEGIN RETURN g_ownership_comment; END get_ownership_comment; --get cone_seed_desc FUNCTION get_cone_seed_desc RETURN VARCHAR2 IS BEGIN RETURN g_cone_seed_desc; END get_cone_seed_desc; --get extraction_comment FUNCTION get_extraction_comment RETURN VARCHAR2 IS BEGIN RETURN g_extraction_comment; END get_extraction_comment; --get seedlot_comment FUNCTION get_seedlot_comment RETURN VARCHAR2 IS BEGIN RETURN g_seedlot_comment; END get_seedlot_comment; --get bgc_zone_code FUNCTION get_bgc_zone_code RETURN VARCHAR2 IS BEGIN RETURN g_bgc_zone_code; END get_bgc_zone_code; --get bgc_subzone_code FUNCTION get_bgc_subzone_code RETURN VARCHAR2 IS BEGIN RETURN g_bgc_subzone_code; END get_bgc_subzone_code; --get variant FUNCTION get_variant RETURN VARCHAR2 IS BEGIN RETURN g_variant; END get_variant; --get bec_version_id FUNCTION get_bec_version_id RETURN VARCHAR2 IS BEGIN RETURN g_bec_version_id; END get_bec_version_id; /*************************************************************** * The following functions should be removed, when the override * functionality is moved back into this package. ***************************************************************/ FUNCTION get_prev_bgc_zone_code RETURN VARCHAR2 IS BEGIN RETURN r_previous.bgc_zone_code; END get_prev_bgc_zone_code; FUNCTION get_prev_bgc_subzone_code RETURN VARCHAR2 IS BEGIN RETURN r_previous.bgc_subzone_code; END get_prev_bgc_subzone_code; FUNCTION get_prev_variant RETURN VARCHAR2 IS BEGIN RETURN r_previous.variant; END get_prev_variant; FUNCTION get_prev_collection_lat_deg RETURN NUMBER IS BEGIN RETURN r_previous.collection_lat_deg; END get_prev_collection_lat_deg; FUNCTION get_prev_collection_lat_min RETURN NUMBER IS BEGIN RETURN r_previous.collection_lat_min; END get_prev_collection_lat_min; FUNCTION get_prev_collection_lat_sec RETURN VARCHAR2 IS BEGIN RETURN r_previous.collection_lat_sec; END get_prev_collection_lat_sec; FUNCTION get_prev_collection_long_deg RETURN NUMBER IS BEGIN RETURN r_previous.collection_long_deg; END get_prev_collection_long_deg; FUNCTION get_prev_collection_long_min RETURN NUMBER IS BEGIN RETURN r_previous.collection_long_min; END get_prev_collection_long_min; FUNCTION get_prev_collection_long_sec RETURN VARCHAR2 IS BEGIN RETURN r_previous.collection_long_sec; END get_prev_collection_long_sec; /*************************************************************** * END ***************************************************************/ --RELATED INFORMATION -->get genetic worth GROWTH FUNCTION get_gw_g RETURN NUMBER IS BEGIN RETURN g_gw_g; END get_gw_g; -->get genetic worth DENSITY FUNCTION get_gw_d RETURN NUMBER IS BEGIN RETURN g_gw_d; END get_gw_d; -->get genetic worth RESISTANCE FUNCTION get_gw_r RETURN NUMBER IS BEGIN RETURN g_gw_r; END get_gw_r; -->get genetic worth Major Gene RESISTANCE FUNCTION get_gw_m RETURN NUMBER IS BEGIN RETURN g_gw_m; END get_gw_m; -->get list of Area of Use SPZ's FUNCTION get_spz_list RETURN VARCHAR2 IS BEGIN RETURN g_spz_list; END get_spz_list; -->get percentage of tested parent trees contributing to lot FUNCTION get_tested_parent_trees_pct RETURN NUMBER IS BEGIN RETURN g_tested_parent_trees_pct; END get_tested_parent_trees_pct; -->get percentage of untested parent trees contributing to lot FUNCTION get_untested_parent_trees_pct RETURN NUMBER IS BEGIN RETURN g_untested_parent_trees_pct; END get_untested_parent_trees_pct; -->get percentage of untested parent trees contributing to lot FUNCTION get_is_lot_split RETURN BOOLEAN IS BEGIN RETURN g_is_lot_split; END get_is_lot_split; -- Get the current bgc for the seedlot. FUNCTION get_new_bgc(p_seedlot_number IN SEEDLOT.SEEDLOT_NUMBER%TYPE) RETURN VARCHAR2 IS v_planting_site_point MDSYS.SDO_GEOMETRY; v_bgc_zone_code seedlot.bgc_zone_code%TYPE; v_bgc_subzone_code seedlot.bgc_subzone_code%TYPE; v_variant seedlot.variant%TYPE; v_bgc_string VARCHAR(500); BEGIN -- We are in trouble if there's no Geometry entry for the seedlot... -- retrieve and set the seedlot geometry capture method. spr_seedlot_geometry.init(p_seedlot_number); spr_seedlot_geometry.set_seedlot_number(p_seedlot_number); spr_seedlot_geometry.get(); v_planting_site_point := spr_seedlot_geometry.get_geometry; IF v_planting_site_point IS NOT NULL THEN spr_spatial_utils.get_bec(v_planting_site_point , v_bgc_zone_code , v_bgc_subzone_code , v_variant); v_bgc_string := v_bgc_zone_code || ' ' || v_bgc_subzone_code || ' ' || v_variant; END IF; RETURN v_bgc_string; END get_new_bgc; -- Get the current spz for the seedlot. FUNCTION get_new_spz(p_seedlot_number IN SEEDLOT.SEEDLOT_NUMBER%TYPE) RETURN VARCHAR2 IS v_planting_site_point MDSYS.SDO_GEOMETRY; v_vegetation_code seed_plan_zone.vegetation_code%TYPE; v_spzb seed_plan_zone.seed_plan_zone_code%TYPE; BEGIN -- We are in trouble if there's no Geometry entry for the seedlot... -- retrieve and set the seedlot geometry capture method. spr_seedlot_geometry.init(p_seedlot_number); spr_seedlot_geometry.set_seedlot_number(p_seedlot_number); spr_seedlot_geometry.get(); v_planting_site_point := spr_seedlot_geometry.get_geometry; -- Get the vegetation code from the seedlot. SELECT vegetation_code INTO v_vegetation_code FROM seedlot WHERE seedlot_number = p_seedlot_number; IF v_planting_site_point IS NOT NULL THEN v_spzb := spr_spatial_utils.get_spzb(v_planting_site_point , v_vegetation_code); END IF; RETURN v_spzb; END get_new_spz; -->Collection lat empty? FUNCTION is_collection_lat_empty RETURN BOOLEAN IS BEGIN RETURN g_collection_lat_deg IS NULL AND g_collection_lat_min IS NULL AND g_collection_lat_sec IS NULL; END is_collection_lat_empty; -->Collection long empty? FUNCTION is_collection_long_empty RETURN BOOLEAN IS BEGIN RETURN g_collection_long_deg IS NULL AND g_collection_long_min IS NULL AND g_collection_long_sec IS NULL; END is_collection_long_empty; -->Area of use can is replaced for these statuses even if it is specified FUNCTION is_area_of_use_status RETURN BOOLEAN IS BEGIN RETURN ((g_seedlot_status_code IN ('INC','PND')) OR (r_previous.seedlot_status_code IN ('INC','PND'))); END is_area_of_use_status; -->Area of use replaceable FUNCTION replace_area_of_use (p_check_value IN VARCHAR2) RETURN BOOLEAN IS BEGIN RETURN (is_area_of_use_status OR p_check_value IS NULL); END replace_area_of_use; -->Area of use min/max elevation empty? FUNCTION is_area_of_use_elev_empty RETURN BOOLEAN IS BEGIN RETURN g_elevation_min IS NULL AND g_elevation_max IS NULL; END is_area_of_use_elev_empty; -->Area of use min lat empty? FUNCTION is_area_of_use_lat_min_empty RETURN BOOLEAN IS BEGIN RETURN g_latitude_deg_min IS NULL AND g_latitude_min_min IS NULL AND g_latitude_sec_min IS NULL; END is_area_of_use_lat_min_empty; -->Area of use min/max lat empty? FUNCTION is_area_of_use_lat_max_empty RETURN BOOLEAN IS BEGIN RETURN g_latitude_deg_max IS NULL AND g_latitude_min_max IS NULL AND g_latitude_sec_max IS NULL; END is_area_of_use_lat_max_empty; -->Area of use min/max lat empty? FUNCTION is_area_of_use_lat_empty RETURN BOOLEAN IS BEGIN RETURN is_area_of_use_lat_min_empty AND is_area_of_use_lat_max_empty; END is_area_of_use_lat_empty; -->Area of use min/max long empty? FUNCTION is_area_of_use_long_min_empty RETURN BOOLEAN IS BEGIN RETURN g_longitude_deg_min IS NULL AND g_longitude_min_min IS NULL AND g_longitude_sec_min IS NULL; END is_area_of_use_long_min_empty; -->Area of use min/max long empty? FUNCTION is_area_of_use_long_max_empty RETURN BOOLEAN IS BEGIN RETURN g_longitude_deg_max IS NULL AND g_longitude_min_max IS NULL AND g_longitude_sec_max IS NULL; END is_area_of_use_long_max_empty; -->Area of use min/max long empty? FUNCTION is_area_of_use_long_empty RETURN BOOLEAN IS BEGIN RETURN is_area_of_use_long_min_empty AND is_area_of_use_long_max_empty; END is_area_of_use_long_empty; -->Lots approved on or after 2005-04-01 must adhere to Chief Forester's Standards FUNCTION is_lot_under_CFS RETURN BOOLEAN IS BEGIN RETURN g_approved_timestamp IS NULL OR g_approved_timestamp >= TO_DATE('2005-04-01','YYYY-MM-DD'); END is_lot_under_CFS; --***END GETTERS --***START SETTERS --set seed_plan_zone_code PROCEDURE set_seed_plan_zone_code(p_value IN VARCHAR2) IS BEGIN g_seed_plan_zone_code := p_value; gb_seed_plan_zone_code := 'Y'; END set_seed_plan_zone_code; --set applicant_client_locn PROCEDURE set_applicant_client_locn(p_value IN VARCHAR2) IS BEGIN g_applicant_client_locn := p_value; gb_applicant_client_locn := 'Y'; END set_applicant_client_locn; --set applicant_client_number PROCEDURE set_applicant_client_number(p_value IN VARCHAR2) IS BEGIN g_applicant_client_number := p_value; gb_applicant_client_number := 'Y'; END set_applicant_client_number; --set applicant_email_address PROCEDURE set_applicant_email_address(p_value IN VARCHAR2) IS BEGIN g_applicant_email_address := p_value; gb_applicant_email_address := 'Y'; END set_applicant_email_address; --set bc_source_ind PROCEDURE set_bc_source_ind(p_value IN VARCHAR2) IS BEGIN g_bc_source_ind := p_value; gb_bc_source_ind := 'Y'; END set_bc_source_ind; --set biotech_processes_ind PROCEDURE set_biotech_processes_ind(p_value IN VARCHAR2) IS BEGIN g_biotech_processes_ind := p_value; gb_biotech_processes_ind := 'Y'; END set_biotech_processes_ind; --set collection_area_radius PROCEDURE set_collection_area_radius(p_value IN NUMBER) IS BEGIN g_collection_area_radius := p_value; gb_collection_area_radius := 'Y'; END set_collection_area_radius; --set collection_bgc_ind PROCEDURE set_collection_bgc_ind(p_value IN VARCHAR2) IS BEGIN g_collection_bgc_ind := p_value; gb_collection_bgc_ind := 'Y'; END set_collection_bgc_ind; --set collection_seed_plan_zone_ind PROCEDURE set_collection_spz_ind(p_value IN VARCHAR2) IS BEGIN g_collection_spz_ind := p_value; gb_collection_spz_ind := 'Y'; END set_collection_spz_ind; --set collection_standard_met_ind PROCEDURE set_coll_standard_met_ind(p_value IN VARCHAR2) IS BEGIN g_coll_standard_met_ind := p_value; gb_coll_standard_met_ind := 'Y'; END set_coll_standard_met_ind; --set cone_collection_method2_code PROCEDURE set_cone_collection_method2_cd(p_value IN VARCHAR2) IS BEGIN g_cone_collection_method2_cd := p_value; gb_cone_collection_method2_cd := 'Y'; END set_cone_collection_method2_cd; --set contaminant_pollen_bv PROCEDURE set_contaminant_pollen_bv(p_value IN NUMBER) IS BEGIN g_contaminant_pollen_bv := p_value; gb_contaminant_pollen_bv := 'Y'; END set_contaminant_pollen_bv; --set controlled_cross_ind PROCEDURE set_controlled_cross_ind(p_value IN VARCHAR2) IS BEGIN g_controlled_cross_ind := p_value; gb_controlled_cross_ind := 'Y'; END set_controlled_cross_ind; --set declared_userid PROCEDURE set_declared_userid(p_value IN VARCHAR2) IS BEGIN g_declared_userid := p_value; gb_declared_userid := 'Y'; END set_declared_userid; --set declared_timestamp PROCEDURE set_declared_timestamp(p_value IN DATE) IS BEGIN g_declared_timestamp := p_value; gb_declared_timestamp := 'Y'; END set_declared_timestamp; --set female_gametic_mthd_code PROCEDURE set_female_gametic_mthd_code(p_value IN VARCHAR2) IS BEGIN g_female_gametic_mthd_code := p_value; gb_female_gametic_mthd_code := 'Y'; END set_female_gametic_mthd_code; --set latitude_sec_max PROCEDURE set_latitude_sec_max(p_value IN NUMBER) IS BEGIN g_latitude_sec_max := p_value; gb_latitude_sec_max := 'Y'; END set_latitude_sec_max; --set latitude_sec_min PROCEDURE set_latitude_sec_min(p_value IN NUMBER) IS BEGIN g_latitude_sec_min := p_value; gb_latitude_sec_min := 'Y'; END set_latitude_sec_min; --set longitude_sec_max PROCEDURE set_longitude_sec_max(p_value IN NUMBER) IS BEGIN g_longitude_sec_max := p_value; gb_longitude_sec_max := 'Y'; END set_longitude_sec_max; --set longitude_sec_min PROCEDURE set_longitude_sec_min(p_value IN NUMBER) IS BEGIN g_longitude_sec_min := p_value; gb_longitude_sec_min := 'Y'; END set_longitude_sec_min; --set male_gametic_mthd_code PROCEDURE set_male_gametic_mthd_code(p_value IN VARCHAR2) IS BEGIN g_male_gametic_mthd_code := p_value; gb_male_gametic_mthd_code := 'Y'; END set_male_gametic_mthd_code; --set orchard_comment PROCEDURE set_orchard_comment(p_value IN VARCHAR2) IS BEGIN g_orchard_comment := p_value; gb_orchard_comment := 'Y'; END set_orchard_comment; --set orchard_contamination_pct PROCEDURE set_orchard_contamination_pct(p_value IN NUMBER) IS BEGIN g_orchard_contamination_pct := p_value; gb_orchard_contamination_pct := 'Y'; END set_orchard_contamination_pct; --set pollen_contamination_ind PROCEDURE set_pollen_contamination_ind(p_value IN VARCHAR2) IS BEGIN g_pollen_contamination_ind := p_value; gb_pollen_contamination_ind := 'Y'; END set_pollen_contamination_ind; --set pollen_contamination_mthd_code PROCEDURE set_pollen_contam_mthd_code(p_value IN VARCHAR2) IS BEGIN g_pollen_contam_mthd_code := p_value; gb_pollen_contam_mthd_code := 'Y'; END set_pollen_contam_mthd_code; --set pollen_contamination_pct PROCEDURE set_pollen_contamination_pct(p_value IN NUMBER) IS BEGIN g_pollen_contamination_pct := p_value; gb_pollen_contamination_pct := 'Y'; END set_pollen_contamination_pct; --set provenance_id PROCEDURE set_provenance_id(p_value IN NUMBER) IS BEGIN g_provenance_id := p_value; gb_provenance_id := 'Y'; END set_provenance_id; --set secondary_orchard_id PROCEDURE set_secondary_orchard_id(p_value IN VARCHAR2) IS BEGIN g_secondary_orchard_id := p_value; gb_secondary_orchard_id := 'Y'; END set_secondary_orchard_id; --set seed_plan_unit_id PROCEDURE set_seed_plan_unit_id(p_value IN NUMBER) IS BEGIN g_seed_plan_unit_id := p_value; gb_seed_plan_unit_id := 'Y'; END set_seed_plan_unit_id; --set seed_store_client_locn PROCEDURE set_seed_store_client_locn(p_value IN VARCHAR2) IS BEGIN g_seed_store_client_locn := p_value; gb_seed_store_client_locn := 'Y'; END set_seed_store_client_locn; --set seed_store_client_number PROCEDURE set_seed_store_client_number(p_value IN VARCHAR2) IS BEGIN g_seed_store_client_number := p_value; gb_seed_store_client_number := 'Y'; END set_seed_store_client_number; --set seedlot_source_code PROCEDURE set_seedlot_source_code(p_value IN VARCHAR2) IS BEGIN g_seedlot_source_code := p_value; gb_seedlot_source_code := 'Y'; END set_seedlot_source_code; --set smp_mean_bv_growth PROCEDURE set_smp_mean_bv_growth(p_value IN VARCHAR2) IS BEGIN g_smp_mean_bv_growth := p_value; gb_smp_mean_bv_growth := 'Y'; END set_smp_mean_bv_growth; --set smp_mean_bv_density PROCEDURE set_smp_mean_bv_density(p_value IN VARCHAR2) IS BEGIN g_smp_mean_bv_density := p_value; gb_smp_mean_bv_density := 'Y'; END set_smp_mean_bv_density; --set smp_mean_bv_resistance PROCEDURE set_smp_mean_bv_resistance(p_value IN VARCHAR2) IS BEGIN g_smp_mean_bv_resistance := p_value; gb_smp_mean_bv_resistance := 'Y'; END set_smp_mean_bv_resistance; --set smp_mean_bv_major_gene PROCEDURE set_smp_mean_bv_major_gene(p_value IN VARCHAR2) IS BEGIN g_smp_mean_bv_major_gene := p_value; gb_smp_mean_bv_major_gene := 'Y'; END set_smp_mean_bv_major_gene; --set smp_parents_outside PROCEDURE set_smp_parents_outside(p_value IN NUMBER) IS BEGIN g_smp_parents_outside := p_value; gb_smp_parents_outside := 'Y'; END set_smp_parents_outside; --set smp_success_pct PROCEDURE set_smp_success_pct(p_value IN NUMBER) IS BEGIN g_smp_success_pct := p_value; gb_smp_success_pct := 'Y'; END set_smp_success_pct; --set temporary_storage_end_date PROCEDURE set_temporary_storage_end_date(p_value IN DATE) IS BEGIN g_temporary_storage_end_date := p_value; gb_temporary_storage_end_date := 'Y'; END set_temporary_storage_end_date; --set temporary_storage_start_date PROCEDURE set_temporary_storage_start_dt(p_value IN DATE) IS BEGIN g_temporary_storage_start_dt := p_value; gb_temporary_storage_start_dt := 'Y'; END set_temporary_storage_start_dt; --set total_parent_trees PROCEDURE set_total_parent_trees(p_value IN NUMBER) IS BEGIN g_total_parent_trees := p_value; gb_total_parent_trees := 'Y'; END set_total_parent_trees; --set latitude_seconds PROCEDURE set_latitude_seconds(p_value IN NUMBER) IS BEGIN g_latitude_seconds := p_value; gb_latitude_seconds := 'Y'; END set_latitude_seconds; --set longitude_seconds PROCEDURE set_longitude_seconds(p_value IN NUMBER) IS BEGIN g_longitude_seconds := p_value; gb_longitude_seconds := 'Y'; END set_longitude_seconds; --set collection_lat_sec PROCEDURE set_collection_lat_sec(p_value IN NUMBER) IS BEGIN g_collection_lat_sec := p_value; gb_collection_lat_sec := 'Y'; END set_collection_lat_sec; --set collection_long_sec PROCEDURE set_collection_long_sec(p_value IN NUMBER) IS BEGIN g_collection_long_sec := p_value; gb_collection_long_sec := 'Y'; END set_collection_long_sec; --set seedlot_number PROCEDURE set_seedlot_number(p_value IN VARCHAR2) IS BEGIN g_seedlot_number := p_value; gb_seedlot_number := 'Y'; END set_seedlot_number; --set seedlot_status_code PROCEDURE set_seedlot_status_code(p_value IN VARCHAR2) IS BEGIN g_seedlot_status_code := p_value; gb_seedlot_status_code := 'Y'; END set_seedlot_status_code; --set vegetation_code PROCEDURE set_vegetation_code(p_value IN VARCHAR2) IS BEGIN g_vegetation_code := p_value; gb_vegetation_code := 'Y'; END set_vegetation_code; --set genetic_class_code PROCEDURE set_genetic_class_code(p_value IN VARCHAR2) IS BEGIN g_genetic_class_code := p_value; gb_genetic_class_code := 'Y'; END set_genetic_class_code; --set collection_source_code PROCEDURE set_collection_source_code(p_value IN VARCHAR2) IS BEGIN g_collection_source_code := p_value; gb_collection_source_code := 'Y'; END set_collection_source_code; --set superior_prvnc_ind PROCEDURE set_superior_prvnc_ind(p_value IN VARCHAR2) IS BEGIN g_superior_prvnc_ind := p_value; gb_superior_prvnc_ind := 'Y'; END set_superior_prvnc_ind; --set org_unit_no PROCEDURE set_org_unit_no(p_value IN NUMBER) IS BEGIN g_org_unit_no := p_value; gb_org_unit_no := 'Y'; END set_org_unit_no; --set registered_seed_ind PROCEDURE set_registered_seed_ind(p_value IN VARCHAR2) IS BEGIN g_registered_seed_ind := p_value; gb_registered_seed_ind := 'Y'; END set_registered_seed_ind; --set to_be_registrd_ind PROCEDURE set_to_be_registrd_ind(p_value IN VARCHAR2) IS BEGIN g_to_be_registrd_ind := p_value; gb_to_be_registrd_ind := 'Y'; END set_to_be_registrd_ind; --set registered_date PROCEDURE set_registered_date(p_value IN DATE) IS BEGIN g_registered_date := p_value; gb_registered_date := 'Y'; END set_registered_date; --set fs721a_signed_ind PROCEDURE set_fs721a_signed_ind(p_value IN VARCHAR2) IS BEGIN g_fs721a_signed_ind := p_value; gb_fs721a_signed_ind := 'Y'; END set_fs721a_signed_ind; --set nad_datum_code PROCEDURE set_nad_datum_code(p_value IN VARCHAR2) IS BEGIN g_nad_datum_code := p_value; gb_nad_datum_code := 'Y'; END set_nad_datum_code; --set utm_zone PROCEDURE set_utm_zone(p_value IN NUMBER) IS BEGIN g_utm_zone := p_value; gb_utm_zone := 'Y'; END set_utm_zone; --set utm_easting PROCEDURE set_utm_easting(p_value IN NUMBER) IS BEGIN g_utm_easting := p_value; gb_utm_easting := 'Y'; END set_utm_easting; --set utm_northing PROCEDURE set_utm_northing(p_value IN NUMBER) IS BEGIN g_utm_northing := p_value; gb_utm_northing := 'Y'; END set_utm_northing; --set longitude_degrees PROCEDURE set_longitude_degrees(p_value IN NUMBER) IS BEGIN g_longitude_degrees := p_value; gb_longitude_degrees := 'Y'; END set_longitude_degrees; --set longitude_minutes PROCEDURE set_longitude_minutes(p_value IN NUMBER) IS BEGIN g_longitude_minutes := p_value; gb_longitude_minutes := 'Y'; END set_longitude_minutes; --set longitude_deg_min PROCEDURE set_longitude_deg_min(p_value IN NUMBER) IS BEGIN g_longitude_deg_min := p_value; gb_longitude_deg_min := 'Y'; END set_longitude_deg_min; --set longitude_min_min PROCEDURE set_longitude_min_min(p_value IN NUMBER) IS BEGIN g_longitude_min_min := p_value; gb_longitude_min_min := 'Y'; END set_longitude_min_min; --set longitude_deg_max PROCEDURE set_longitude_deg_max(p_value IN NUMBER) IS BEGIN g_longitude_deg_max := p_value; gb_longitude_deg_max := 'Y'; END set_longitude_deg_max; --set longitude_min_max PROCEDURE set_longitude_min_max(p_value IN NUMBER) IS BEGIN g_longitude_min_max := p_value; gb_longitude_min_max := 'Y'; END set_longitude_min_max; --set latitude_degrees PROCEDURE set_latitude_degrees(p_value IN NUMBER) IS BEGIN g_latitude_degrees := p_value; gb_latitude_degrees := 'Y'; END set_latitude_degrees; --set latitude_minutes PROCEDURE set_latitude_minutes(p_value IN NUMBER) IS BEGIN g_latitude_minutes := p_value; gb_latitude_minutes := 'Y'; END set_latitude_minutes; --set latitude_deg_min PROCEDURE set_latitude_deg_min(p_value IN NUMBER) IS BEGIN g_latitude_deg_min := p_value; gb_latitude_deg_min := 'Y'; END set_latitude_deg_min; --set latitude_min_min PROCEDURE set_latitude_min_min(p_value IN NUMBER) IS BEGIN g_latitude_min_min := p_value; gb_latitude_min_min := 'Y'; END set_latitude_min_min; --set latitude_deg_max PROCEDURE set_latitude_deg_max(p_value IN NUMBER) IS BEGIN g_latitude_deg_max := p_value; gb_latitude_deg_max := 'Y'; END set_latitude_deg_max; --set latitude_min_max PROCEDURE set_latitude_min_max(p_value IN NUMBER) IS BEGIN g_latitude_min_max := p_value; gb_latitude_min_max := 'Y'; END set_latitude_min_max; --set seed_coast_area_code PROCEDURE set_seed_coast_area_code(p_value IN VARCHAR2) IS BEGIN g_seed_coast_area_code := p_value; gb_seed_coast_area_code := 'Y'; END set_seed_coast_area_code; --set elevation PROCEDURE set_elevation(p_value IN NUMBER) IS BEGIN g_elevation := p_value; gb_elevation := 'Y'; END set_elevation; --set elevation_min PROCEDURE set_elevation_min(p_value IN NUMBER) IS BEGIN g_elevation_min := p_value; gb_elevation_min := 'Y'; END set_elevation_min; --set elevation_max PROCEDURE set_elevation_max(p_value IN NUMBER) IS BEGIN g_elevation_max := p_value; gb_elevation_max := 'Y'; END set_elevation_max; --set orchard_id PROCEDURE set_orchard_id(p_value IN VARCHAR2) IS BEGIN g_orchard_id := p_value; gb_orchard_id := 'Y'; END set_orchard_id; --set collection_locn_desc PROCEDURE set_collection_locn_desc(p_value IN VARCHAR2) IS BEGIN g_collection_locn_desc := p_value; gb_collection_locn_desc := 'Y'; END set_collection_locn_desc; --set collection_cli_number PROCEDURE set_collection_cli_number(p_value IN VARCHAR2) IS BEGIN g_collection_cli_number := p_value; gb_collection_cli_number := 'Y'; END set_collection_cli_number; --set collection_cli_locn_cd PROCEDURE set_collection_cli_locn_cd(p_value IN VARCHAR2) IS BEGIN g_collection_cli_locn_cd := p_value; gb_collection_cli_locn_cd := 'Y'; END set_collection_cli_locn_cd; --set collection_start_date PROCEDURE set_collection_start_date(p_value IN DATE) IS BEGIN g_collection_start_date := p_value; gb_collection_start_date := 'Y'; END set_collection_start_date; --set collection_end_date PROCEDURE set_collection_end_date(p_value IN DATE) IS BEGIN g_collection_end_date := p_value; gb_collection_end_date := 'Y'; END set_collection_end_date; --set cone_collection_method_code PROCEDURE set_cone_collection_method_cd(p_value IN VARCHAR2) IS BEGIN g_cone_collection_method_cd := p_value; gb_cone_collection_method_cd := 'Y'; END set_cone_collection_method_cd; --set no_of_containers PROCEDURE set_no_of_containers(p_value IN NUMBER) IS BEGIN g_no_of_containers := p_value; gb_no_of_containers := 'Y'; END set_no_of_containers; --set clctn_volume PROCEDURE set_clctn_volume(p_value IN NUMBER) IS BEGIN g_clctn_volume := p_value; gb_clctn_volume := 'Y'; END set_clctn_volume; --set vol_per_container PROCEDURE set_vol_per_container(p_value IN NUMBER) IS BEGIN g_vol_per_container := p_value; gb_vol_per_container := 'Y'; END set_vol_per_container; --set nmbr_trees_from_code PROCEDURE set_nmbr_trees_from_code(p_value IN VARCHAR2) IS BEGIN g_nmbr_trees_from_code := p_value; gb_nmbr_trees_from_code := 'Y'; END set_nmbr_trees_from_code; --set coancestry PROCEDURE set_coancestry(p_value IN NUMBER) IS BEGIN g_coancestry := p_value; gb_coancestry := 'Y'; END set_coancestry; --set effective_pop_size PROCEDURE set_effective_pop_size(p_value IN NUMBER) IS BEGIN g_effective_pop_size := p_value; gb_effective_pop_size := 'Y'; END set_effective_pop_size; --set original_seed_qty PROCEDURE set_original_seed_qty(p_value IN NUMBER) IS BEGIN g_original_seed_qty := p_value; gb_original_seed_qty := 'Y'; END set_original_seed_qty; --set interm_strg_client_number PROCEDURE set_interm_strg_client_number(p_value IN VARCHAR2) IS BEGIN g_interm_strg_client_number := p_value; gb_interm_strg_client_number := 'Y'; END set_interm_strg_client_number; --set interm_strg_client_locn PROCEDURE set_interm_strg_client_locn(p_value IN VARCHAR2) IS BEGIN g_interm_strg_client_locn := p_value; gb_interm_strg_client_locn := 'Y'; END set_interm_strg_client_locn; --set interm_strg_st_date PROCEDURE set_interm_strg_st_date(p_value IN DATE) IS BEGIN g_interm_strg_st_date := p_value; gb_interm_strg_st_date := 'Y'; END set_interm_strg_st_date; --set interm_strg_end_date PROCEDURE set_interm_strg_end_date(p_value IN DATE) IS BEGIN g_interm_strg_end_date := p_value; gb_interm_strg_end_date := 'Y'; END set_interm_strg_end_date; --set interm_facility_code PROCEDURE set_interm_facility_code(p_value IN VARCHAR2) IS BEGIN g_interm_facility_code := p_value; gb_interm_facility_code := 'Y'; END set_interm_facility_code; --set extraction_st_date PROCEDURE set_extraction_st_date(p_value IN DATE) IS BEGIN g_extraction_st_date := p_value; gb_extraction_st_date := 'Y'; END set_extraction_st_date; --set extraction_end_date PROCEDURE set_extraction_end_date(p_value IN DATE) IS BEGIN g_extraction_end_date := p_value; gb_extraction_end_date := 'Y'; END set_extraction_end_date; --set extraction_volume PROCEDURE set_extraction_volume(p_value IN NUMBER) IS BEGIN g_extraction_volume := p_value; gb_extraction_volume := 'Y'; END set_extraction_volume; --set extrct_cli_number PROCEDURE set_extrct_cli_number(p_value IN VARCHAR2) IS BEGIN g_extrct_cli_number := p_value; gb_extrct_cli_number := 'Y'; END set_extrct_cli_number; --set extrct_cli_locn_cd PROCEDURE set_extrct_cli_locn_cd(p_value IN VARCHAR2) IS BEGIN g_extrct_cli_locn_cd := p_value; gb_extrct_cli_locn_cd := 'Y'; END set_extrct_cli_locn_cd; --set stored_cli_number PROCEDURE set_stored_cli_number(p_value IN VARCHAR2) IS BEGIN g_stored_cli_number := p_value; gb_stored_cli_number := 'Y'; END set_stored_cli_number; --set stored_cli_locn_cd PROCEDURE set_stored_cli_locn_cd(p_value IN VARCHAR2) IS BEGIN g_stored_cli_locn_cd := p_value; gb_stored_cli_locn_cd := 'Y'; END set_stored_cli_locn_cd; --set lngterm_strg_st_date PROCEDURE set_lngterm_strg_st_date(p_value IN DATE) IS BEGIN g_lngterm_strg_st_date := p_value; gb_lngterm_strg_st_date := 'Y'; END set_lngterm_strg_st_date; --set historical_tsr_date PROCEDURE set_historical_tsr_date(p_value IN DATE) IS BEGIN g_historical_tsr_date := p_value; gb_historical_tsr_date := 'Y'; END set_historical_tsr_date; --set collection_lat_deg PROCEDURE set_collection_lat_deg(p_value IN NUMBER) IS BEGIN g_collection_lat_deg := p_value; gb_collection_lat_deg := 'Y'; END set_collection_lat_deg; --set collection_lat_min PROCEDURE set_collection_lat_min(p_value IN NUMBER) IS BEGIN g_collection_lat_min := p_value; gb_collection_lat_min := 'Y'; END set_collection_lat_min; --set collection_latitude_code PROCEDURE set_collection_latitude_code(p_value IN VARCHAR2) IS BEGIN g_collection_latitude_code := p_value; gb_collection_latitude_code := 'Y'; END set_collection_latitude_code; --set collection_long_deg PROCEDURE set_collection_long_deg(p_value IN NUMBER) IS BEGIN g_collection_long_deg := p_value; gb_collection_long_deg := 'Y'; END set_collection_long_deg; --set collection_long_min PROCEDURE set_collection_long_min(p_value IN NUMBER) IS BEGIN g_collection_long_min := p_value; gb_collection_long_min := 'Y'; END set_collection_long_min; --set collection_longitude_code PROCEDURE set_collection_longitude_code(p_value IN VARCHAR2) IS BEGIN g_collection_longitude_code := p_value; gb_collection_longitude_code := 'Y'; END set_collection_longitude_code; --set collection_elevation PROCEDURE set_collection_elevation(p_value IN NUMBER) IS BEGIN g_collection_elevation := p_value; gb_collection_elevation := 'Y'; END set_collection_elevation; --set collection_elevation_min PROCEDURE set_collection_elevation_min(p_value IN NUMBER) IS BEGIN g_collection_elevation_min := p_value; gb_collection_elevation_min := 'Y'; END set_collection_elevation_min; --set collection_elevation_max PROCEDURE set_collection_elevation_max(p_value IN NUMBER) IS BEGIN g_collection_elevation_max := p_value; gb_collection_elevation_max := 'Y'; END set_collection_elevation_max; --set entry_timestamp PROCEDURE set_entry_timestamp(p_value IN DATE) IS BEGIN g_entry_timestamp := p_value; gb_entry_timestamp := 'Y'; END set_entry_timestamp; --set entry_userid PROCEDURE set_entry_userid(p_value IN VARCHAR2) IS BEGIN g_entry_userid := p_value; gb_entry_userid := 'Y'; END set_entry_userid; --set update_timestamp PROCEDURE set_update_timestamp(p_value IN DATE) IS BEGIN g_update_timestamp := p_value; gb_update_timestamp := 'Y'; END set_update_timestamp; --set update_userid PROCEDURE set_update_userid(p_value IN VARCHAR2) IS BEGIN g_update_userid := p_value; gb_update_userid := 'Y'; END set_update_userid; --set approved_timestamp PROCEDURE set_approved_timestamp(p_value IN DATE) IS BEGIN g_approved_timestamp := p_value; gb_approved_timestamp := 'Y'; END set_approved_timestamp; --set approved_userid PROCEDURE set_approved_userid(p_value IN VARCHAR2) IS BEGIN g_approved_userid := p_value; gb_approved_userid := 'Y'; END set_approved_userid; --set revision_count PROCEDURE set_revision_count(p_value IN NUMBER) IS BEGIN g_revision_count := p_value; gb_revision_count := 'Y'; END set_revision_count; --set interm_strg_locn PROCEDURE set_interm_strg_locn(p_value IN VARCHAR2) IS BEGIN g_interm_strg_locn := p_value; gb_interm_strg_locn := 'Y'; END set_interm_strg_locn; --set interm_strg_cmt PROCEDURE set_interm_strg_cmt(p_value IN VARCHAR2) IS BEGIN g_interm_strg_cmt := p_value; gb_interm_strg_cmt := 'Y'; END set_interm_strg_cmt; --set ownership_comment PROCEDURE set_ownership_comment(p_value IN VARCHAR2) IS BEGIN g_ownership_comment := p_value; gb_ownership_comment := 'Y'; END set_ownership_comment; --set cone_seed_desc PROCEDURE set_cone_seed_desc(p_value IN VARCHAR2) IS BEGIN g_cone_seed_desc := p_value; gb_cone_seed_desc := 'Y'; END set_cone_seed_desc; --set extraction_comment PROCEDURE set_extraction_comment(p_value IN VARCHAR2) IS BEGIN g_extraction_comment := p_value; gb_extraction_comment := 'Y'; END set_extraction_comment; --set seedlot_comment PROCEDURE set_seedlot_comment(p_value IN VARCHAR2) IS BEGIN g_seedlot_comment := p_value; gb_seedlot_comment := 'Y'; END set_seedlot_comment; --set bgc_zone_code PROCEDURE set_bgc_zone_code(p_value IN VARCHAR2) IS BEGIN g_bgc_zone_code := p_value; gb_bgc_zone_code := 'Y'; END set_bgc_zone_code; --set bgc_subzone_code PROCEDURE set_bgc_subzone_code(p_value IN VARCHAR2) IS BEGIN g_bgc_subzone_code := p_value; gb_bgc_subzone_code := 'Y'; END set_bgc_subzone_code; --set variant PROCEDURE set_variant(p_value IN VARCHAR2) IS BEGIN g_variant := p_value; gb_variant := 'Y'; END set_variant; --set bec_version_id PROCEDURE set_bec_version_id(p_value IN VARCHAR2) IS BEGIN g_bec_version_id := p_value; gb_bec_version_id := 'Y'; END set_bec_version_id; --RELATED INFO -->set genetic worth GROWTH PROCEDURE set_gw_g(p_value IN NUMBER) IS BEGIN g_gw_g := p_value; END set_gw_g; -->set genetic worth DENSITY PROCEDURE set_gw_d(p_value IN NUMBER) IS BEGIN g_gw_d := p_value; END set_gw_d; -->set genetic worth RESISTANCE PROCEDURE set_gw_r(p_value IN NUMBER) IS BEGIN g_gw_r := p_value; END set_gw_r; -->set genetic worth Major Gene RESISTANCE PROCEDURE set_gw_m(p_value IN NUMBER) IS BEGIN g_gw_m := p_value; END set_gw_m; -->set list of Area of Use SPZ's PROCEDURE set_spz_list(p_value IN VARCHAR2) IS BEGIN g_spz_list := p_value; END set_spz_list; --***END SETTERS /****************************************************************************** Procedure: apply_superior_prov_limits Purpose: Apply superior provenance limits ******************************************************************************/ PROCEDURE apply_superior_prov_limits IS b_first_row_processed BOOLEAN; --Limits and SPZ's for B Class sup provenance CURSOR c_sup IS SELECT sp.genetic_worth_code , sp.genetic_worth_rating , sp.limit_up_elevation , sp.limit_down_elevation , sp.limit_elevation_min , sp.limit_elevation_max , sp.limit_latitude_min_degrees , sp.limit_latitude_min_minutes , sp.limit_latitude_max_degrees , sp.limit_latitude_max_minutes , spz.seed_plan_zone_code FROM superior_provenance sp , superior_provenance_plan_zone spz WHERE sp.provenance_id = g_provenance_id AND sp.vegetation_code = g_vegetation_code AND spz.provenance_id = sp.provenance_id ORDER BY spz.seed_plan_zone_code; v_spz_list g_spz_list%TYPE; BEGIN b_first_row_processed := FALSE; --Derive Superior Provenance limits FOR r_sup IN c_sup LOOP --Area of Use SPZ's v_spz_list := v_spz_list || r_sup.seed_plan_zone_code || ','; IF NOT b_first_row_processed THEN --Set Genetic Worth IF r_sup.genetic_worth_code = 'G' AND replace_area_of_use(TO_CHAR(g_gw_g)) THEN set_gw_g(r_sup.genetic_worth_rating); ELSIF r_sup.genetic_worth_code = 'D' AND replace_area_of_use(TO_CHAR(g_gw_d)) THEN set_gw_d(r_sup.genetic_worth_rating); ELSIF r_sup.genetic_worth_code = 'R' AND replace_area_of_use(TO_CHAR(g_gw_r)) THEN set_gw_r(r_sup.genetic_worth_rating); ELSIF r_sup.genetic_worth_code = 'M' AND replace_area_of_use(TO_CHAR(g_gw_m)) THEN set_gw_m(r_sup.genetic_worth_rating); END IF; IF is_area_of_use_status OR g_elevation_min IS NULL THEN --Set Area of Use Min Elevation -->limits can be up/down or a min/max IF r_sup.limit_down_elevation IS NOT NULL THEN --Calculate min and max based on Collection Mean Elev set_elevation_min(g_collection_elevation - r_sup.limit_down_elevation); set_elevation_max(g_collection_elevation + r_sup.limit_up_elevation); ELSE --Take Min and Max Elevation directly from table set_elevation_min (r_sup.limit_elevation_min); END IF; END IF; IF is_area_of_use_status OR g_elevation_max IS NULL THEN --Set Area of Use Min/Max Elevation -->limits can be up/down or a min/max IF r_sup.limit_up_elevation IS NOT NULL THEN --Calculate min and max based on Collection Mean Elev set_elevation_max(g_collection_elevation + r_sup.limit_up_elevation); ELSE --Take Min and Max Elevation directly from table set_elevation_max(r_sup.limit_elevation_max); END IF; END IF; IF is_area_of_use_status OR is_area_of_use_lat_min_empty THEN --Set Area of Use Min/Max Lat IF r_sup.limit_latitude_min_degrees IS NOT NULL THEN set_latitude_deg_min (r_sup.limit_latitude_min_degrees); set_latitude_min_min(r_sup.limit_latitude_min_minutes); set_latitude_sec_min(0); ELSE --Set to mean collection lat set_latitude_deg_min(g_collection_lat_deg); set_latitude_min_min(g_collection_lat_min); set_latitude_sec_min(g_collection_lat_sec); END IF; END IF; IF is_area_of_use_status OR is_area_of_use_lat_max_empty THEN IF r_sup.limit_latitude_min_degrees IS NOT NULL THEN set_latitude_deg_max(r_sup.limit_latitude_max_degrees); set_latitude_min_max(r_sup.limit_latitude_max_minutes); set_latitude_sec_max(0); ELSE --Set to mean collection lat set_latitude_deg_max(g_collection_lat_deg); set_latitude_min_max(g_collection_lat_min); set_latitude_sec_max(g_collection_lat_sec); END IF; END IF; --Currently no longitude limits for superior provenance lots --(defined by SPZ's) so set to collection mean IF is_area_of_use_status OR is_area_of_use_long_min_empty THEN set_longitude_deg_min(g_collection_long_deg); set_longitude_min_min(g_collection_long_min); set_longitude_sec_min(g_collection_long_sec); END IF; IF is_area_of_use_status OR is_area_of_use_long_max_empty THEN set_longitude_deg_max(g_collection_long_deg); set_longitude_min_max(g_collection_long_min); set_longitude_sec_max(g_collection_long_sec); END IF; b_first_row_processed := TRUE; END IF; END LOOP; IF replace_area_of_use(g_spz_list) THEN set_spz_list(RTRIM(v_spz_list,',')); END IF; END apply_superior_prov_limits; /****************************************************************************** Procedure: apply_no_superior_prov_limits Purpose: Apply NO superior provenance limits for Class B lots from within British Columbia (BC Source=Y) ******************************************************************************/ PROCEDURE apply_no_superior_prov_limits IS e_no_limits_found EXCEPTION; --Limits for B Class non-superior provenance CURSOR c_limit IS SELECT transfer_limit_skey , site_min_latdeg , site_max_latdeg , limit_north_latdeg , limit_north_latmnt , limit_south_latdeg , limit_south_latmnt , limit_east_longdeg , limit_east_longmnt , limit_west_longdeg , limit_west_longmnt , limit_up_elevatn , limit_down_elevatn FROM transfer_limit WHERE ( vegetation_code = g_vegetation_code OR vegetation_code IS NULL) AND genetic_class_code = g_genetic_class_code AND superior_prvnc_ind = NVL(g_superior_prvnc_ind,'N') AND coast_interior_code = spr_get_spz_type(g_seed_plan_zone_code) AND ( seed_plan_zone_code = g_seed_plan_zone_code OR seed_plan_zone_code IS NULL) AND g_collection_lat_deg BETWEEN site_min_latdeg AND site_max_latdeg ORDER BY vegetation_code, seed_plan_zone_code; r_limit c_limit%ROWTYPE; BEGIN --B Class with No Superior Provenance has no Genetic Worth g_gw_g := NULL; g_gw_d := NULL; g_gw_r := NULL; g_gw_m := NULL; -- not sure. --Area of Use SPZ = Collection SPZ IF replace_area_of_use(g_spz_list) THEN set_spz_list(g_seed_plan_zone_code); END IF; --Derive Non Superior Provenance limits OPEN c_limit; FETCH c_limit INTO r_limit; CLOSE c_limit; IF r_limit.transfer_limit_skey IS NULL THEN RAISE e_no_limits_found; END IF; --Set Area of Use Min/Max Elevation IF is_area_of_use_status OR g_elevation_min IS NULL THEN set_elevation_min(g_collection_elevation - NVL(r_limit.limit_down_elevatn,0)); END IF; -->Ensure elevation is at least 1m set_elevation_min(GREATEST(g_elevation_min,1)); IF is_area_of_use_status OR g_elevation_max IS NULL THEN set_elevation_max(g_collection_elevation + NVL(r_limit.limit_up_elevatn,0)); END IF; --Set Area of Use Min Lat IF is_area_of_use_status OR is_area_of_use_lat_min_empty THEN -->Min set_latitude_deg_min(NVL(g_collection_lat_deg,0) - NVL(r_limit.limit_south_latdeg,0)); set_latitude_min_min(g_collection_lat_min - NVL(r_limit.limit_south_latmnt,0)); set_latitude_sec_min(g_collection_lat_sec); -->Adjust for provincial boundary at 48 00 00 IF g_latitude_min_min < 0 THEN set_latitude_deg_min(NVL(g_latitude_deg_min,0) - 1); set_latitude_min_min(g_latitude_min_min + 60); END IF; IF g_latitude_deg_min < 48 THEN set_latitude_deg_min(48); set_latitude_min_min(0); set_latitude_sec_min(0); END IF; END IF; --Set Area of Use Max Lat IF is_area_of_use_status OR is_area_of_use_lat_max_empty THEN -->Max set_latitude_deg_max(g_collection_lat_deg + NVL(r_limit.limit_north_latdeg,0)); set_latitude_min_max(g_collection_lat_min + NVL(r_limit.limit_north_latmnt,0)); set_latitude_sec_max(g_collection_lat_sec); -->Adjust for provincial boundary at 60 00 00 IF g_latitude_min_max > 60 THEN set_latitude_deg_max(NVL(g_latitude_deg_max,0) + 1); set_latitude_min_max(NVL(g_latitude_min_max,0) - 60); END IF; IF g_latitude_deg_max > 60 THEN set_latitude_deg_max(60); set_latitude_min_max(0); set_latitude_sec_max(0); END IF; END IF; --Set Area of Use Min Long IF is_area_of_use_status OR is_area_of_use_long_min_empty THEN -->Min set_longitude_deg_min(g_collection_long_deg - NVL(r_limit.limit_east_longdeg,0)); set_longitude_min_min(g_collection_long_min - NVL(r_limit.limit_east_longmnt,0)); set_longitude_sec_min(g_collection_long_sec); -->Adjust for provincial boundary at 114 00 00 IF g_longitude_min_min < 0 THEN set_longitude_deg_min(NVL(g_longitude_deg_min,0) - 1); set_longitude_min_min(NVL(g_longitude_min_min,0) + 60); END IF; IF g_longitude_deg_min < 114 THEN set_longitude_deg_min(114); set_longitude_min_min(0); set_longitude_sec_min(0); END IF; END IF; --Set Area of Use Min Long IF is_area_of_use_status OR is_area_of_use_long_max_empty THEN -->Max set_longitude_deg_max(g_collection_long_deg + NVL(r_limit.limit_west_longdeg,0)); set_longitude_min_max(g_collection_long_min + NVL(r_limit.limit_west_longmnt,0)); set_longitude_sec_max(g_collection_long_sec); -->Adjust for provincial boundary at 140 00 00 IF g_longitude_min_max > 60 THEN set_longitude_deg_max(NVL(g_longitude_deg_max,0) + 1); set_longitude_min_max(NVL(g_longitude_min_max,0) - 60); END IF; IF g_longitude_deg_max > 140 THEN set_longitude_deg_max(140); set_longitude_min_max(0); set_longitude_sec_max(0); END IF; END IF; EXCEPTION WHEN e_no_limits_found THEN NULL; END apply_no_superior_prov_limits; /****************************************************************************** Function: get_untested_common_spz Purpose: Get the SPZB that all Untested Parent Trees have in common. If >1 SPZB found then returns NULL ******************************************************************************/ FUNCTION get_untested_common_spz RETURN VARCHAR2 IS v_spz parent_tree.seed_plan_zone_code%TYPE; BEGIN BEGIN SELECT DISTINCT pt.seed_plan_zone_code INTO v_spz FROM parent_tree pt , seedlot_parent_tree_gen_qlty sptgq , seedlot_parent_tree spt --select from temp table for recalc WHERE spt.seedlot_number = g_seedlot_number AND spt.seedlot_number = sptgq.seedlot_number AND spt.parent_tree_id = sptgq.parent_tree_id AND pt.parent_tree_id = spt.parent_tree_id AND sptgq.untested_ind = 'Y'; EXCEPTION WHEN TOO_MANY_ROWS OR NO_DATA_FOUND THEN NULL; END; RETURN (v_spz); END get_untested_common_spz; /****************************************************************************** Procedure: get_a_class_area_of_use_spz Purpose: Get primary and non-primary SPZ's for A class lot. ******************************************************************************/ PROCEDURE get_a_class_area_of_use_spz IS CURSOR c_tested IS SELECT spz.seed_plan_zone_code , spz.primary_ind FROM tested_pt_area_of_use_spz spz , tested_pt_area_of_use aou WHERE aou.seed_plan_unit_id = g_seed_plan_unit_id AND spz.tested_pt_area_of_use_id = aou.tested_pt_area_of_use_id ORDER BY 2 DESC; BEGIN --Always reset for INC,PND IF replace_area_of_use(g_spz_list) THEN --Tested Parent Trees IF g_seedlot_source_code IN ('TPT','CUS') AND g_seed_plan_unit_id IS NOT NULL THEN --Get primary/secondary SPZ's (primary sorted first) set_spz_list(NULL); FOR r_tested IN c_tested LOOP set_spz_list(g_spz_list || r_tested.seed_plan_zone_code || ','); END LOOP; --Untested Parent Trees ELSIF g_seedlot_source_code = 'UPT' THEN --Primary SPZ is the SPZ that all PT's share set_spz_list(get_untested_common_spz); END IF; --Remove trailing comma set_spz_list(RTRIM(g_spz_list,',')); END IF; END get_a_class_area_of_use_spz; /****************************************************************************** Procedure: get_tested_area_of_use_geog Purpose: Get Area of Use Geography for A Class lots from Tested Parent Trees ******************************************************************************/ PROCEDURE get_tested_area_of_use_geog IS --First row contains all necessary information CURSOR c_tested IS SELECT MIN(spu.elevation_min) elevation_min , MAX(spu.elevation_max) elevation_max , MIN(spu.latitude_degrees_min +(spu.latitude_minutes_min / 60)) latitude_min , MAX(spu.latitude_degrees_max +(spu.latitude_minutes_max / 60)) latitude_max FROM tested_pt_area_of_use primary_spu , tested_pt_area_of_use_spu included_spus , seed_plan_unit spu WHERE primary_spu.seed_plan_unit_id = g_seed_plan_unit_id AND included_spus.tested_pt_area_of_use_id = primary_spu.tested_pt_area_of_use_id AND included_spus.seed_plan_unit_id = spu.seed_plan_unit_id; r_tested c_tested%ROWTYPE; BEGIN IF g_seed_plan_unit_id IS NOT NULL THEN OPEN c_tested; FETCH c_tested INTO r_tested; CLOSE c_tested; IF replace_area_of_use(TO_CHAR(g_elevation_min)) THEN set_elevation_min(r_tested.elevation_min); END IF; IF replace_area_of_use(TO_CHAR(g_elevation_max)) THEN set_elevation_max(r_tested.elevation_max); END IF; IF is_area_of_use_status OR is_area_of_use_lat_min_empty THEN IF r_tested.latitude_min IS NULL THEN --No lat limits - set to mean of parent trees set_latitude_deg_min(g_collection_lat_deg); set_latitude_min_min(g_collection_lat_min); set_latitude_sec_min(g_collection_lat_sec); ELSE set_latitude_deg_min(TRUNC(r_tested.latitude_min)); set_latitude_min_min((r_tested.latitude_min - TRUNC(r_tested.latitude_min)) * 60); set_latitude_sec_min(0); END IF; END IF; IF is_area_of_use_status OR is_area_of_use_lat_max_empty THEN IF r_tested.latitude_max IS NULL THEN --No lat limits - set to mean of parent trees set_latitude_deg_max(g_collection_lat_deg); set_latitude_min_max(g_collection_lat_min); set_latitude_sec_max(g_collection_lat_sec); ELSE set_latitude_deg_max(TRUNC(r_tested.latitude_max)); set_latitude_min_max((r_tested.latitude_max - TRUNC(r_tested.latitude_max)) * 60); set_latitude_sec_max(0); END IF; END IF; --Currently no longitude limits for A class lots --(defined by SPZ's) so set to collection mean IF is_area_of_use_status OR is_area_of_use_long_min_empty THEN set_longitude_deg_min(g_collection_long_deg); set_longitude_min_min(g_collection_long_min); set_longitude_sec_min(g_collection_long_sec); END IF; IF is_area_of_use_status OR is_area_of_use_long_max_empty THEN set_longitude_deg_max(g_collection_long_deg); set_longitude_min_max(g_collection_long_min); set_longitude_sec_max(g_collection_long_sec); END IF; END IF; --g_seed_plan_unit_id IS NOT NULL END get_tested_area_of_use_geog; /****************************************************************************** Procedure: get_untested_area_of_use_geog Purpose: Get Area of Use Geography for A Class lots from Untested Parent Trees or Custom Lots ******************************************************************************/ PROCEDURE get_untested_area_of_use_geog IS e_no_limits_found EXCEPTION; v_spz parent_tree.seed_plan_zone_code%TYPE; CURSOR c_limit IS SELECT transfer_limit_skey , site_min_latdeg , site_max_latdeg , limit_north_latdeg , limit_north_latmnt , limit_south_latdeg , limit_south_latmnt , limit_east_longdeg , limit_east_longmnt , limit_west_longdeg , limit_west_longmnt , limit_up_elevatn , limit_down_elevatn FROM transfer_limit WHERE ( vegetation_code = g_vegetation_code OR vegetation_code IS NULL) AND genetic_class_code = g_genetic_class_code AND coast_interior_code = spr_get_spz_type(v_spz) AND ( seed_plan_zone_code = v_spz OR seed_plan_zone_code IS NULL) AND g_collection_lat_deg BETWEEN site_min_latdeg AND site_max_latdeg ORDER BY vegetation_code, seed_plan_zone_code; r_limit c_limit%ROWTYPE; BEGIN --Limits are retrieved based on SPZB common to untested parent trees v_spz := get_untested_common_spz; --Derive Non Superior Provenance limits OPEN c_limit; FETCH c_limit INTO r_limit; CLOSE c_limit; IF r_limit.transfer_limit_skey IS NULL THEN RAISE e_no_limits_found; END IF; --Set Area of Use Min/Max Elevation IF replace_area_of_use(TO_CHAR(g_elevation_min)) THEN set_elevation_min(g_collection_elevation - NVL(r_limit.limit_down_elevatn,0)); END IF; IF replace_area_of_use(TO_CHAR(g_elevation_max)) THEN set_elevation_max(g_collection_elevation + NVL(r_limit.limit_up_elevatn,0)); END IF; -->Ensure elevation is at least 1m set_elevation_min(GREATEST(g_elevation_min,1)); --Set Area of Use Min/Max Lat -->Min IF is_area_of_use_lat_min_empty OR is_area_of_use_status THEN set_latitude_deg_min(NVL(g_collection_lat_deg,0) - NVL(r_limit.limit_south_latdeg,0)); set_latitude_min_min(g_collection_lat_min - NVL(r_limit.limit_south_latmnt,0)); set_latitude_sec_min(g_collection_lat_sec); -->Adjust for provincial boundary at 48 00 00 IF g_latitude_min_min < 0 THEN set_latitude_deg_min(NVL(g_latitude_deg_min,0) - 1); set_latitude_min_min(g_latitude_min_min + 60); END IF; IF g_latitude_deg_min < 48 THEN set_latitude_deg_min(48); set_latitude_min_min(0); set_latitude_sec_min(0); END IF; END IF; -->Max IF is_area_of_use_lat_max_empty OR is_area_of_use_status THEN set_latitude_deg_max(g_collection_lat_deg + NVL(r_limit.limit_north_latdeg,0)); set_latitude_min_max(g_collection_lat_min + NVL(r_limit.limit_north_latmnt,0)); set_latitude_sec_max(g_collection_lat_sec); -->Adjust for provincial boundary at 60 00 00 IF g_latitude_min_max > 60 THEN set_latitude_deg_max(NVL(g_latitude_deg_max,0) + 1); set_latitude_min_max(NVL(g_latitude_min_max,0) - 60); END IF; IF g_latitude_deg_max > 60 THEN set_latitude_deg_max(60); set_latitude_min_max(0); set_latitude_sec_max(0); END IF; END IF; --Set Area of Use Min/Max Long -->Min IF is_area_of_use_long_min_empty OR is_area_of_use_status THEN set_longitude_deg_min(g_collection_long_deg - NVL(r_limit.limit_east_longdeg,0)); set_longitude_min_min(g_collection_long_min - NVL(r_limit.limit_east_longmnt,0)); set_longitude_sec_min(g_collection_long_sec); -->Adjust for provincial boundary at 114 00 00 IF g_longitude_min_min < 0 THEN set_longitude_deg_min(NVL(g_longitude_deg_min,0) - 1); set_longitude_min_min(NVL(g_longitude_min_min,0) + 60); END IF; IF g_longitude_deg_min < 114 THEN set_longitude_deg_min(114); set_longitude_min_min(0); set_longitude_sec_min(0); END IF; END IF; -->Max IF is_area_of_use_long_max_empty OR is_area_of_use_status THEN set_longitude_deg_max(g_collection_long_deg + NVL(r_limit.limit_west_longdeg,0)); set_longitude_min_max(g_collection_long_min + NVL(r_limit.limit_west_longmnt,0)); set_longitude_sec_max(g_collection_long_sec); -->Adjust for provincial boundary at 140 00 00 IF g_longitude_min_max > 60 THEN set_longitude_deg_max(NVL(g_longitude_deg_max,0) + 1); set_longitude_min_max(NVL(g_longitude_min_max,0) - 60); END IF; IF g_longitude_deg_max > 140 THEN set_longitude_deg_max(140); set_longitude_min_max(0); set_longitude_sec_max(0); END IF; END IF; EXCEPTION WHEN e_no_limits_found THEN NULL; END get_untested_area_of_use_geog; /****************************************************************************** Procedure: get_a_class_area_of_use_geog Purpose: Get Area of Use Geography for A Class lots ******************************************************************************/ PROCEDURE get_a_class_area_of_use_geog IS BEGIN --Tested Parent Trees IF g_seedlot_source_code = 'TPT' THEN get_tested_area_of_use_geog; --Untested Parent Trees and Custom Lots ELSIF g_seedlot_source_code IN ('UPT','CUS') THEN get_untested_area_of_use_geog; END IF; END get_a_class_area_of_use_geog; /****************************************************************************** Procedure: get_area_of_use Purpose: Derive area of use for A and B class lots. For certain statuses, defined by is_area_of_use_status(), Area of Use is always recalculated and replaced. For other statuses, each Area of Use item is only recalculated if it has been blanked-out (i.e. IS NULL) by the user. ******************************************************************************/ PROCEDURE get_area_of_use IS BEGIN --Blank area of use information for status where it will be replaced IF is_area_of_use_status THEN set_spz_list(NULL); set_elevation_min(NULL); set_elevation_max(NULL); set_latitude_deg_min(NULL); set_latitude_min_min(NULL); set_latitude_sec_min(NULL); set_latitude_deg_max(NULL); set_latitude_min_max(NULL); set_latitude_sec_max(NULL); set_longitude_deg_min(NULL); set_longitude_min_min(NULL); set_longitude_sec_min(NULL); set_longitude_deg_max(NULL); set_longitude_min_max(NULL); set_longitude_sec_max(NULL); END IF; --B Class limits IF g_genetic_class_code = 'B' THEN IF g_bc_source_ind = 'Y' THEN IF g_superior_prvnc_ind = 'Y' THEN apply_superior_prov_limits; ELSIF g_superior_prvnc_ind = 'N' THEN apply_no_superior_prov_limits; END IF; -- ELSE --BC Source not entered - cannot derive limits -- NULL; -- ELSIF g_bc_source_ind = 'N' THEN --Tree Seed Centre will set Area of Use so do not derive limits -- NULL; END IF; --A Class limits ELSE get_a_class_area_of_use_geog; get_a_class_area_of_use_spz; END IF; END get_area_of_use; /****************************************************************************** Procedure: provenance_is_valid_for_spp Purpose: Return TRUE if Provenance is valid for species, otherwise return FALSE. ******************************************************************************/ FUNCTION provenance_is_valid_for_spp RETURN BOOLEAN IS v_count NUMBER(10); BEGIN SELECT COUNT(1) INTO v_count FROM superior_provenance WHERE provenance_id = g_provenance_id AND vegetation_code = g_vegetation_code; RETURN (v_count > 0); END provenance_is_valid_for_spp; /****************************************************************************** Procedure: spu_is_valid_for_species Purpose: Return TRUE if Seed Plan Unit is valid for species, otherwise return FALSE. ******************************************************************************/ FUNCTION spu_is_valid_for_species RETURN BOOLEAN IS v_count NUMBER(10); BEGIN SELECT COUNT(1) INTO v_count FROM seed_plan_unit spu , seed_plan_zone spz WHERE spu.seed_plan_unit_id = g_seed_plan_unit_id AND spz.seed_plan_zone_id = spu.seed_plan_zone_id AND spz.vegetation_code = g_vegetation_code; RETURN (v_count > 0); END spu_is_valid_for_species; /****************************************************************************** Procedure: orchard_is_valid_for_species Purpose: Return TRUE if Orchard passed-in is valid for species, otherwise return FALSE. ******************************************************************************/ FUNCTION orchard_is_valid_for_species ( p_orchard_id IN VARCHAR2 ) RETURN BOOLEAN IS v_count NUMBER(10); BEGIN SELECT COUNT(1) INTO v_count FROM orchard WHERE orchard_id = p_orchard_id AND vegetation_code = g_vegetation_code; RETURN (v_count > 0); END orchard_is_valid_for_species; /****************************************************************************** Procedure: set_mean_area_of_use_geography Purpose: Copy Mean Area of Use geography to Mean Collection geography ******************************************************************************/ PROCEDURE set_mean_area_of_use_geography IS BEGIN --IMPORTANT: Mean Area of Use geography must be set as Transfer Guideline -- routines may use Mean Area of Use geography to derive -- Transfer Limits. -->Mean Elevation IF g_elevation_min = g_elevation_max THEN --->mean matches min and max set_elevation(g_elevation_max); ELSE --->default to collection elevation set_elevation(g_collection_elevation); END IF; -->Mean Latitude IF g_latitude_deg_min = g_latitude_deg_max AND g_latitude_min_min = g_latitude_min_max AND g_latitude_sec_min = g_latitude_sec_max THEN --->mean matches min and max set_latitude_degrees(g_latitude_deg_max); set_latitude_minutes(g_latitude_min_max); set_latitude_seconds(g_latitude_sec_max); ELSE --->default to mean collection lat set_latitude_degrees(g_collection_lat_deg); set_latitude_minutes(g_collection_lat_min); set_latitude_seconds(g_collection_lat_sec); END IF; -->Mean Longitude IF g_longitude_deg_min = g_longitude_deg_max AND g_longitude_min_min = g_longitude_min_max AND g_longitude_sec_min = g_longitude_sec_max THEN --->mean matches min and max set_longitude_degrees(g_longitude_deg_max); set_longitude_minutes(g_longitude_min_max); set_longitude_seconds(g_longitude_sec_max); ELSE --->default to mean collection long set_longitude_degrees(g_collection_long_deg); set_longitude_minutes(g_collection_long_min); set_longitude_seconds(g_collection_long_sec); END IF; END set_mean_area_of_use_geography; /****************************************************************************** Procedure: load_array Purpose: Populate array from saved data if array is not present. If p_get_current_tests is True: - Populate the BV/CV-G value in the array (whether passed-in or just generated as described above) with gq value flagged for use in calc . ******************************************************************************/ PROCEDURE load_array (p_pt_array IN OUT spr_001a_object_varray ,p_get_current_tests IN BOOLEAN) IS v_current_test_ind VARCHAR2(1); v_genetic_type_code parent_tree_genetic_quality.genetic_type_code%TYPE; CURSOR c_gq (p_parent_tree_id IN NUMBER , p_seed_plan_unit_id IN NUMBER , p_genetic_type_code IN VARCHAR2 , p_genetic_worth_code IN VARCHAR2) IS SELECT ptgq.genetic_quality_value FROM parent_tree_genetic_quality ptgq , parent_tree pt WHERE pt.parent_tree_id = p_parent_tree_id AND pt.parent_tree_reg_status_code = 'APP' AND pt.active_ind = 'Y' AND ptgq.parent_tree_id = pt.parent_tree_id AND ptgq.seed_plan_unit_id = p_seed_plan_unit_id AND ptgq.genetic_type_code = p_genetic_type_code AND ptgq.genetic_worth_code = p_genetic_worth_code AND ptgq.genetic_worth_calc_ind = 'Y'; r_gq c_gq%ROWTYPE; CURSOR c_refresh_list IS SELECT gq.parent_tree_id , pt.parent_tree_number , --G Estimated ind CASE WHEN gq.genetic_worth_code='G' AND pt.parent_tree_reg_status_code = 'APP' AND pt.active_ind = 'Y' AND v_current_test_ind = 'Y' AND ptgq.genetic_quality_value IS NOT NULL THEN 'N' WHEN gq.genetic_worth_code='G' THEN gq.estimated_ind ELSE NULL END g_estimated_ind , --D Estimated ind CASE WHEN gq.genetic_worth_code='D' AND pt.parent_tree_reg_status_code = 'APP' AND pt.active_ind = 'Y' AND v_current_test_ind = 'Y' AND ptgq.genetic_quality_value IS NOT NULL THEN 'N' WHEN gq.genetic_worth_code='D' THEN gq.estimated_ind ELSE NULL END d_estimated_ind , --R Estimated ind CASE WHEN gq.genetic_worth_code='R' AND pt.parent_tree_reg_status_code = 'APP' AND pt.active_ind = 'Y' AND v_current_test_ind = 'Y' AND ptgq.genetic_quality_value IS NOT NULL THEN 'N' WHEN gq.genetic_worth_code='R' THEN gq.estimated_ind ELSE NULL END r_estimated_ind , --M Estimated ind CASE WHEN gq.genetic_worth_code='M' AND pt.parent_tree_reg_status_code = 'APP' AND pt.active_ind = 'Y' AND v_current_test_ind = 'Y' AND ptgq.genetic_quality_value IS NOT NULL THEN 'N' WHEN gq.genetic_worth_code='M' THEN gq.estimated_ind ELSE NULL END m_estimated_ind , --Untested ind CASE WHEN (gq.genetic_worth_code='G' OR gq.genetic_worth_code='R' OR gq.genetic_worth_code='D' OR gq.genetic_worth_code='M') AND pt.parent_tree_reg_status_code = 'APP' AND pt.active_ind = 'Y' AND v_current_test_ind = 'Y' AND ptgq.genetic_quality_value IS NOT NULL THEN 'N' WHEN gq.genetic_worth_code='G' OR gq.genetic_worth_code='R' OR gq.genetic_worth_code='D' OR gq.genetic_worth_code='M' THEN gq.untested_ind ELSE NULL END untested_ind --BV's , CASE WHEN gq.genetic_type_code='BV' AND gq.genetic_worth_code='G' AND pt.parent_tree_reg_status_code = 'APP' AND pt.active_ind = 'Y' AND v_current_test_ind = 'Y' AND ptgq.genetic_quality_value IS NOT NULL THEN ptgq.genetic_quality_value WHEN gq.genetic_type_code='BV' AND gq.genetic_worth_code='G' THEN gq.genetic_quality_value ELSE NULL END bv_g , CASE WHEN gq.genetic_type_code='BV' AND gq.genetic_worth_code='D' AND pt.parent_tree_reg_status_code = 'APP' AND pt.active_ind = 'Y' AND v_current_test_ind = 'Y' AND ptgq.genetic_quality_value IS NOT NULL THEN ptgq.genetic_quality_value WHEN gq.genetic_type_code='BV' AND gq.genetic_worth_code='D' THEN gq.genetic_quality_value ELSE NULL END bv_d , CASE WHEN gq.genetic_type_code='BV' AND gq.genetic_worth_code='R' AND pt.parent_tree_reg_status_code = 'APP' AND pt.active_ind = 'Y' AND v_current_test_ind = 'Y' AND ptgq.genetic_quality_value IS NOT NULL THEN ptgq.genetic_quality_value WHEN gq.genetic_type_code='BV' AND gq.genetic_worth_code='R' THEN gq.genetic_quality_value ELSE NULL END bv_r , CASE WHEN gq.genetic_type_code='BV' AND gq.genetic_worth_code='M' AND pt.parent_tree_reg_status_code = 'APP' AND pt.active_ind = 'Y' AND v_current_test_ind = 'Y' AND ptgq.genetic_quality_value IS NOT NULL THEN ptgq.genetic_quality_value WHEN gq.genetic_type_code='BV' AND gq.genetic_worth_code='M' THEN gq.genetic_quality_value ELSE NULL END bv_m --CV's , CASE WHEN gq.genetic_type_code='CV' AND gq.genetic_worth_code='G' AND pt.parent_tree_reg_status_code = 'APP' AND pt.active_ind = 'Y' AND v_current_test_ind = 'Y' AND ptgq.genetic_quality_value IS NOT NULL THEN ptgq.genetic_quality_value WHEN gq.genetic_type_code='CV' AND gq.genetic_worth_code='G' THEN gq.genetic_quality_value ELSE NULL END cv_g , CASE WHEN gq.genetic_type_code='CV' AND gq.genetic_worth_code='D' AND pt.parent_tree_reg_status_code = 'APP' AND pt.active_ind = 'Y' AND v_current_test_ind = 'Y' AND ptgq.genetic_quality_value IS NOT NULL THEN ptgq.genetic_quality_value WHEN gq.genetic_type_code='CV' AND gq.genetic_worth_code='D' THEN gq.genetic_quality_value ELSE NULL END cv_d , CASE WHEN gq.genetic_type_code='CV' AND gq.genetic_worth_code='R' AND pt.parent_tree_reg_status_code = 'APP' AND pt.active_ind = 'Y' AND v_current_test_ind = 'Y' AND ptgq.genetic_quality_value IS NOT NULL THEN ptgq.genetic_quality_value WHEN gq.genetic_type_code='CV' AND gq.genetic_worth_code='R' THEN gq.genetic_quality_value ELSE NULL END cv_r , CASE WHEN gq.genetic_type_code='CV' AND gq.genetic_worth_code='M' AND pt.parent_tree_reg_status_code = 'APP' AND pt.active_ind = 'Y' AND v_current_test_ind = 'Y' AND ptgq.genetic_quality_value IS NOT NULL THEN ptgq.genetic_quality_value WHEN gq.genetic_type_code='CV' AND gq.genetic_worth_code='M' THEN gq.genetic_quality_value ELSE NULL END cv_m , spt.cone_count , spt.pollen_count , spt.smp_success_pct , spt.smp_mix_latitude_degrees , spt.smp_mix_latitude_minutes , spt.smp_mix_longitude_degrees , spt.smp_mix_longitude_minutes , spt.smp_mix_elevation , spt.smp_mix_bv_growth , spt.smp_mix_bv_density , spt.smp_mix_bv_resistance , spt.smp_mix_bv_major_gene , spt.non_orchard_pollen_contam_pct , spt.total_genetic_worth_contrib , spt.revision_count FROM parent_tree pt , parent_tree fem_pt , seedlot_parent_tree_gen_qlty gq , seedlot_parent_tree spt , parent_tree_genetic_quality ptgq WHERE spt.seedlot_number = g_seedlot_number AND gq.parent_tree_id = spt.parent_tree_id AND gq.seedlot_number = spt.seedlot_number AND (gq.genetic_worth_code = 'G' OR gq.genetic_worth_code = 'D' OR gq.genetic_worth_code = 'R' OR gq.genetic_worth_code = 'M') --outer join in case parent tree deleted AND pt.parent_tree_id(+) = spt.parent_tree_id --get female parent tree for geography error ind AND fem_pt.parent_tree_id(+) = pt.parent_tree_id --Get current test value AND ptgq.parent_tree_id(+) = gq.parent_tree_id AND ptgq.seed_plan_unit_id(+) = gq.seed_plan_unit_id AND ptgq.genetic_type_code(+) = gq.genetic_type_code AND ptgq.genetic_worth_code(+) = gq.genetic_worth_code AND ptgq.genetic_worth_calc_ind(+) = 'Y' ORDER BY 1; r_refresh_list c_refresh_list%ROWTYPE; r_contrib spar_parent_tree_contrib_temp%ROWTYPE; v_prev_parent_tree_id spar_parent_tree_contrib_temp.parent_tree_id%TYPE; BEGIN IF p_pt_array IS NULL THEN --Set indicator to be used in query v_current_test_ind := 'N'; IF p_get_current_tests THEN v_current_test_ind := 'Y'; END IF; --Empty out global temp table DELETE FROM spar_parent_tree_contrib_temp; OPEN c_refresh_list; FETCH c_refresh_list INTO r_refresh_list; WHILE c_refresh_list%FOUND LOOP r_contrib.parent_tree_id := r_refresh_list.parent_tree_id; r_contrib.parent_tree_number := r_refresh_list.parent_tree_number; --BV's IF r_refresh_list.bv_g IS NOT NULL THEN r_contrib.bv_g := r_refresh_list.bv_g; END IF; --CV's IF r_refresh_list.cv_g IS NOT NULL THEN r_contrib.cv_g := r_refresh_list.cv_g; END IF; --BV's IF r_refresh_list.bv_d IS NOT NULL THEN r_contrib.bv_d := r_refresh_list.bv_d; END IF; --CV's IF r_refresh_list.cv_d IS NOT NULL THEN r_contrib.cv_d := r_refresh_list.cv_d; END IF; --BV's IF r_refresh_list.bv_r IS NOT NULL THEN r_contrib.bv_r := r_refresh_list.bv_r; END IF; --CV's IF r_refresh_list.cv_r IS NOT NULL THEN r_contrib.cv_r := r_refresh_list.cv_r; END IF; --BV's IF r_refresh_list.bv_m IS NOT NULL THEN r_contrib.bv_m := r_refresh_list.bv_m; END IF; --CV's IF r_refresh_list.cv_r IS NOT NULL THEN r_contrib.cv_m := r_refresh_list.cv_m; END IF; --Estimated ind - applies to Growth IF r_refresh_list.g_estimated_ind IS NOT NULL THEN r_contrib.g_estimated_ind := r_refresh_list.g_estimated_ind; END IF; --D Estimated ind - applies to Density IF r_refresh_list.d_estimated_ind IS NOT NULL THEN r_contrib.d_estimated_ind := r_refresh_list.d_estimated_ind; END IF; --R Estimated ind - applies to Resistance IF r_refresh_list.r_estimated_ind IS NOT NULL THEN r_contrib.r_estimated_ind := r_refresh_list.r_estimated_ind; END IF; --M Estimated ind - applies to Major Gene Resistance IF r_refresh_list.m_estimated_ind IS NOT NULL THEN r_contrib.m_estimated_ind := r_refresh_list.m_estimated_ind; END IF; IF r_refresh_list.untested_ind IS NOT NULL THEN r_contrib.untested_ind := r_refresh_list.untested_ind; END IF; --Saved values r_contrib.revision_count := NVL(r_contrib.revision_count,r_refresh_list.revision_count); r_contrib.cone_count := NVL(r_contrib.cone_count,r_refresh_list.cone_count); r_contrib.pollen_count := NVL(r_contrib.pollen_count,r_refresh_list.pollen_count); r_contrib.smp_success_pct := NVL(r_contrib.smp_success_pct,r_refresh_list.smp_success_pct); r_contrib.smp_mix_bv_growth := NVL(r_contrib.smp_mix_bv_growth,r_refresh_list.smp_mix_bv_growth); r_contrib.smp_mix_bv_density := NVL(r_contrib.smp_mix_bv_density,r_refresh_list.smp_mix_bv_density); r_contrib.smp_mix_bv_resistance := NVL(r_contrib.smp_mix_bv_resistance,r_refresh_list.smp_mix_bv_resistance); r_contrib.smp_mix_bv_major_gene := NVL(r_contrib.smp_mix_bv_major_gene,r_refresh_list.smp_mix_bv_major_gene); r_contrib.smp_mix_latitude_degrees := NVL(r_contrib.smp_mix_latitude_degrees,r_refresh_list.smp_mix_latitude_degrees); r_contrib.smp_mix_latitude_minutes := NVL(r_contrib.smp_mix_latitude_minutes,r_refresh_list.smp_mix_latitude_minutes); r_contrib.smp_mix_longitude_degrees := NVL(r_contrib.smp_mix_longitude_degrees,r_refresh_list.smp_mix_longitude_degrees); r_contrib.smp_mix_longitude_minutes := NVL(r_contrib.smp_mix_longitude_minutes,r_refresh_list.smp_mix_longitude_minutes); r_contrib.smp_mix_elevation := NVL(r_contrib.smp_mix_elevation,r_refresh_list.smp_mix_elevation); r_contrib.non_orchard_pollen_contam_pct := NVL(r_contrib.non_orchard_pollen_contam_pct,r_refresh_list.non_orchard_pollen_contam_pct); -- r_contrib.smp_mix_bv_density := NVL(r_contrib.smp_mix_bv_density,r_refresh_list.smp_mix_bv_density); -- r_contrib.smp_mix_bv_density := NVL(r_contrib.smp_mix_bv_density,r_refresh_list.smp_mix_bv_density); -- r_contrib.smp_mix_bv_density := NVL(r_contrib.smp_mix_bv_density,r_refresh_list.smp_mix_bv_density); --Save previous parent tree and get next row v_prev_parent_tree_id := r_refresh_list.parent_tree_id; FETCH c_refresh_list INTO r_refresh_list; --If Parent Tree changed or last row IF v_prev_parent_tree_id != r_refresh_list.parent_tree_id OR c_refresh_list%NOTFOUND THEN --Insert the row INSERT INTO spar_parent_tree_contrib_temp ( parent_tree_id , parent_tree_number , g_estimated_ind , d_estimated_ind , r_estimated_ind , m_estimated_ind , untested_ind , bv_g, bv_d, bv_r, bv_m , cv_g, cv_d, cv_r, cv_m , cone_count , pollen_count , smp_success_pct , smp_mix_bv_growth , smp_mix_bv_density , smp_mix_bv_resistance , smp_mix_bv_major_gene , smp_mix_latitude_degrees , smp_mix_latitude_minutes , smp_mix_longitude_degrees , smp_mix_longitude_minutes , smp_mix_elevation , non_orchard_pollen_contam_pct , total_genetic_worth_contrib , revision_count) VALUES ( r_contrib.parent_tree_id , r_contrib.parent_tree_number , r_contrib.g_estimated_ind , r_contrib.d_estimated_ind , r_contrib.r_estimated_ind , r_contrib.m_estimated_ind , r_contrib.untested_ind , r_contrib.bv_g, r_contrib.bv_d, r_contrib.bv_r, r_contrib.bv_m , r_contrib.cv_g, r_contrib.cv_d, r_contrib.cv_r, r_contrib.cv_m , r_contrib.cone_count , r_contrib.pollen_count , r_contrib.smp_success_pct , r_contrib.smp_mix_bv_growth , r_contrib.smp_mix_bv_density , r_contrib.smp_mix_bv_resistance , r_contrib.smp_mix_bv_major_gene , r_contrib.smp_mix_latitude_degrees , r_contrib.smp_mix_latitude_minutes , r_contrib.smp_mix_longitude_degrees , r_contrib.smp_mix_longitude_minutes , r_contrib.smp_mix_elevation , r_contrib.non_orchard_pollen_contam_pct , r_contrib.total_genetic_worth_contrib , r_contrib.revision_count); r_contrib:= NULL; END IF; END LOOP; SELECT spr_001a_object( parent_tree_id , parent_tree_number , g_estimated_ind , d_estimated_ind , r_estimated_ind , m_estimated_ind , untested_ind , bv_g, bv_d, bv_r, bv_m , cv_g, cv_d, cv_r, cv_m , cone_count , pollen_count , smp_success_pct , smp_mix_latitude_degrees , smp_mix_latitude_minutes , smp_mix_longitude_degrees , smp_mix_longitude_minutes , smp_mix_elevation , smp_mix_bv_growth , smp_mix_bv_density , smp_mix_bv_resistance , smp_mix_bv_major_gene , non_orchard_pollen_contam_pct , total_genetic_worth_contrib , longitude_degrees , longitude_minutes , longitude_seconds , latitude_degrees , latitude_minutes , latitude_seconds , elevation , geography_error_ind , revision_count) BULK COLLECT INTO p_pt_array FROM spar_parent_tree_contrib_temp ORDER BY DECODE(cone_count||pollen_count,NULL,2,1),TO_NUMBER(parent_tree_number); ELSE IF p_get_current_tests AND p_pt_array.COUNT > 0 THEN --Loop through array setting latest gq values FOR i IN p_pt_array.FIRST..p_pt_array.LAST LOOP --Assume only saved rows need to get latest gq value --(others should already have latest) IF p_pt_array(i).revision_count IS NOT NULL THEN IF p_pt_array(i).bv_g IS NOT NULL THEN v_genetic_type_code := 'BV'; ELSE v_genetic_type_code := 'CV'; END IF; r_gq := NULL; --Get current Growth test flagged for use in GW calc OPEN c_gq(p_pt_array(i).parent_tree_id , g_seed_plan_unit_id , v_genetic_type_code , 'G'); FETCH c_gq INTO r_gq; CLOSE c_gq; -->if no result found, leave existing result IF r_gq.genetic_quality_value IS NOT NULL THEN IF p_pt_array(i).bv_g IS NOT NULL THEN p_pt_array(i).bv_g := r_gq.genetic_quality_value; p_pt_array(i).untested_ind := 'N'; p_pt_array(i).g_estimated_ind := 'N'; ELSE p_pt_array(i).cv_g := r_gq.genetic_quality_value; p_pt_array(i).untested_ind := 'N'; p_pt_array(i).g_estimated_ind := 'N'; END IF; END IF; r_gq := NULL; --Get current Density test flagged for use in GW calc OPEN c_gq(p_pt_array(i).parent_tree_id , g_seed_plan_unit_id , v_genetic_type_code , 'D'); FETCH c_gq INTO r_gq; CLOSE c_gq; IF r_gq.genetic_quality_value IS NOT NULL THEN IF p_pt_array(i).bv_d IS NOT NULL THEN p_pt_array(i).bv_d := r_gq.genetic_quality_value; p_pt_array(i).untested_ind := 'N'; p_pt_array(i).d_estimated_ind := 'N'; ELSE p_pt_array(i).cv_d := r_gq.genetic_quality_value; p_pt_array(i).untested_ind := 'N'; p_pt_array(i).d_estimated_ind := 'N'; END IF; END IF; r_gq := NULL; --Get current Resistance test flagged for use in GW calc OPEN c_gq(p_pt_array(i).parent_tree_id , g_seed_plan_unit_id , v_genetic_type_code , 'R'); FETCH c_gq INTO r_gq; CLOSE c_gq; IF r_gq.genetic_quality_value IS NOT NULL THEN IF p_pt_array(i).bv_r IS NOT NULL THEN p_pt_array(i).bv_r := r_gq.genetic_quality_value; p_pt_array(i).untested_ind := 'N'; p_pt_array(i).r_estimated_ind := 'N'; ELSE p_pt_array(i).cv_r := r_gq.genetic_quality_value; p_pt_array(i).untested_ind := 'N'; p_pt_array(i).r_estimated_ind := 'N'; END IF; END IF; r_gq := NULL; --Get current Major Gene Resistance test flagged for use in GW calc OPEN c_gq(p_pt_array(i).parent_tree_id , g_seed_plan_unit_id , v_genetic_type_code , 'M'); FETCH c_gq INTO r_gq; CLOSE c_gq; IF r_gq.genetic_quality_value IS NOT NULL THEN IF p_pt_array(i).bv_m IS NOT NULL THEN p_pt_array(i).bv_m := r_gq.genetic_quality_value; p_pt_array(i).untested_ind := 'N'; p_pt_array(i).m_estimated_ind := 'N'; ELSE p_pt_array(i).cv_m := r_gq.genetic_quality_value; p_pt_array(i).untested_ind := 'N'; p_pt_array(i).m_estimated_ind := 'N'; END IF; END IF; END IF; --revision_count is not null END LOOP; END IF; END IF; -- Get the geographic information for each of the objects in the array IF (p_pt_array.COUNT > 0) THEN FOR i IN p_pt_array.FIRST..p_pt_array.LAST LOOP spr_get_pt_geog( p_pt_array(i).parent_tree_id , p_pt_array(i).elevation , p_pt_array(i).latitude_degrees , p_pt_array(i).latitude_minutes , p_pt_array(i).latitude_seconds , p_pt_array(i).longitude_degrees , p_pt_array(i).longitude_minutes , p_pt_array(i).longitude_seconds); END LOOP; END IF; EXCEPTION WHEN COLLECTION_IS_NULL THEN NULL; END load_array; /****************************************************************************** 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 v_coll_lat NUMBER;--col:E v_coll_long NUMBER;--col:H v_lat NUMBER;--col:T v_long NUMBER;--col:U v_female_crop_pop NUMBER;--col:V v_parent_prop_orch_poll NUMBER;--col:W v_m_gw_g_contrib_orch_poll NUMBER;--col:X v_sum_m_gw_g_contrib_orch_poll NUMBER := 0;--SUM(X) v_m_gw_d_contrib_orch_poll NUMBER;--col:X v_sum_m_gw_d_contrib_orch_poll NUMBER := 0;--SUM(X) v_m_gw_r_contrib_orch_poll NUMBER;--col:X v_sum_m_gw_r_contrib_orch_poll NUMBER := 0;--SUM(X) v_m_gw_m_contrib_orch_poll NUMBER;--col:X v_sum_m_gw_m_contrib_orch_poll NUMBER := 0;--SUM(X) v_f_gw_g_contrib NUMBER;--col:Y v_m_smp_g_contrib NUMBER;--col:Z v_f_gw_d_contrib NUMBER;--col:Y v_m_smp_d_contrib NUMBER;--col:Z v_f_gw_r_contrib NUMBER;--col:Y v_m_smp_r_contrib NUMBER;--col:Z v_f_gw_m_contrib NUMBER;--col:Y v_m_smp_m_contrib NUMBER;--col:Z v_m_contam_contrib NUMBER;--col:AA v_m_orch_poll_contrib_g NUMBER;--col:AB(dependent on SUM(X) v_m_orch_poll_contrib_d NUMBER;--col:AB(dependent on SUM(X) v_m_orch_poll_contrib_r NUMBER;--col:AB(dependent on SUM(X) v_m_orch_poll_contrib_m NUMBER;--col:AB(dependent on SUM(X) v_m_total_gw_g_contrib NUMBER;--col:AC v_m_total_gw_d_contrib NUMBER;--col:AC v_m_total_gw_r_contrib NUMBER;--col:AC v_m_total_gw_m_contrib NUMBER;--col:AC v_p_total_gw_g_contrib NUMBER;--col:AD v_sum_p_total_gw_g_contrib NUMBER := 0;--SUM(AD) v_p_total_gw_d_contrib NUMBER;--col:AD v_sum_p_total_gw_d_contrib NUMBER := 0;--SUM(AD) v_p_total_gw_r_contrib NUMBER;--col:AD v_sum_p_total_gw_r_contrib NUMBER := 0;--SUM(AD) v_p_total_gw_m_contrib NUMBER;--col:AD v_sum_p_total_gw_m_contrib NUMBER := 0;--SUM(AD) v_p_prop_contrib NUMBER;--col:AE v_sum_p_prop_contrib NUMBER := 0;--SUM(AE) v_sum_p_prop_contrib_tested NUMBER := 0;--SUM(AE for Tested Parent Trees) v_p_contrib_elev_no_smp_poll NUMBER;--col:AF v_p_contrib_lat_no_smp_poll NUMBER;--col:AG v_p_contrib_long_no_smp_poll NUMBER;--col:AH v_smp_poll_wtd_contrib_elev NUMBER;--col:AI v_smp_poll_wtd_contrib_lat NUMBER;--col:AJ v_smp_poll_wtd_contrib_long NUMBER;--col:AK v_wtd_elev_p_and_smp_poll NUMBER;--col:AL v_sum_wtd_elev_p_and_smp_poll NUMBER := 0;--SUM(AL) v_wtd_lat_p_and_smp_poll NUMBER;--col:AM v_sum_wtd_lat_p_and_smp_poll NUMBER := 0;--SUM(am) v_wtd_long_p_and_smp_poll NUMBER;--col:AN v_sum_wtd_long_p_and_smp_poll NUMBER := 0;--SUM(AN) v_ne_no_smp_contrib NUMBER;--col:AO v_sum_ne_no_smp_contrib NUMBER := 0;--SUM(AO) v_smp_success_wtd_by_f_p NUMBER;--col:AP v_sum_smp_success_wtd_by_f_p NUMBER := 0;--SUM(AP) v_orch_gamete_contr NUMBER;--col:AQ v_sum_orch_gamete_contr NUMBER := 0;--SUM(AQ) v_total_parent_trees NUMBER(5) := 0; v_gw_g NUMBER; v_gw_d NUMBER; v_gw_r NUMBER; v_gw_m NUMBER; v_effective_pop_size NUMBER; v_lat_deg seedlot.collection_lat_deg%TYPE; v_lat_min seedlot.collection_lat_min%TYPE; v_lat_sec seedlot.collection_lat_sec%TYPE; v_long_deg seedlot.collection_long_deg%TYPE; v_long_min seedlot.collection_long_min%TYPE; v_long_sec seedlot.collection_long_sec%TYPE; v_elev seedlot.collection_elevation%TYPE; v_elev_min seedlot.collection_elevation%TYPE; v_elev_max seedlot.collection_elevation%TYPE; v_smp_mean_bv_growth seedlot_parent_tree.smp_mix_bv_growth%TYPE; v_smp_mean_bv_density seedlot_parent_tree.smp_mix_bv_density%TYPE; v_smp_mean_bv_resistance seedlot_parent_tree.smp_mix_bv_resistance%TYPE; v_smp_mean_bv_major_gene seedlot_parent_tree.smp_mix_bv_major_gene%TYPE; v_smp_success_pct seedlot.smp_success_pct%TYPE; v_orchard_contamination_pct seedlot.orchard_contamination_pct%TYPE; v_pt_elevation seedlot.collection_elevation%TYPE; v_pt_latitude_degrees seedlot.collection_lat_deg%TYPE; v_pt_latitude_minutes seedlot.collection_lat_min%TYPE; v_pt_latitude_seconds seedlot.collection_lat_sec%TYPE; v_pt_longitude_degrees seedlot.collection_long_deg%TYPE; v_pt_longitude_minutes seedlot.collection_long_min%TYPE; v_pt_longitude_seconds seedlot.collection_long_sec%TYPE; v_total_cone_count NUMBER := 0; v_total_pollen_count NUMBER := 0; --Values used in calculating averages v_total_smp_mix_bv_growth NUMBER := 0; v_num_smp_mix_bv_growth NUMBER(5) := 0; v_avg_smp_mix_bv_growth NUMBER; v_total_smp_mix_bv_density NUMBER := 0; v_num_smp_mix_bv_density NUMBER(5) := 0; v_avg_smp_mix_bv_density NUMBER; v_total_smp_mix_bv_resistance NUMBER := 0; v_num_smp_mix_bv_resistance NUMBER(5) := 0; v_avg_smp_mix_bv_resistance NUMBER; v_total_smp_mix_bv_major_gene NUMBER := 0; v_num_smp_mix_bv_major_gene NUMBER(5) := 0; v_avg_smp_mix_bv_major_gene NUMBER; v_total_non_orchard_pollen NUMBER := 0; v_num_non_orchard_pollen NUMBER(5) := 0; v_avg_non_orchard_pollen NUMBER; --Values pulled from the array v_a_cone_count NUMBER; v_a_pollen_count NUMBER; v_a_smp_success_pct NUMBER; v_a_smp_mix_bv_growth NUMBER; v_a_smp_mix_bv_density NUMBER; v_a_smp_mix_bv_resistance NUMBER; v_a_smp_mix_bv_major_gene NUMBER; v_a_smp_mix_latitude_degrees NUMBER; v_a_smp_mix_latitude_minutes NUMBER; v_a_smp_mix_longitude_degrees NUMBER; v_a_smp_mix_longitude_minutes NUMBER; v_a_smp_mix_elevation NUMBER; v_a_non_orchard_pollen_contam NUMBER; -- replace smp mix if latest bv calculated. v_smp_records_exist BOOLEAN; v_old_smp_mix_bv_growth NUMBER; v_old_smp_mix_bv_density NUMBER; v_old_smp_mix_bv_resistance NUMBER; v_old_smp_mix_bv_major_gene NUMBER; v_old_smp_mix_lat_deg NUMBER; v_old_smp_mix_lat_min NUMBER; v_old_smp_mix_long_deg NUMBER; v_old_smp_mix_long_min NUMBER; v_old_smp_mix_elevation NUMBER; v_new_smp_mix_bv_growth NUMBER; v_new_smp_mix_bv_density NUMBER; v_new_smp_mix_bv_resistance NUMBER; v_new_smp_mix_bv_major_gene NUMBER; v_new_smp_mix_lat_deg NUMBER; v_new_smp_mix_lat_min NUMBER; v_new_smp_mix_long_deg NUMBER; v_new_smp_mix_long_min NUMBER; v_new_smp_mix_elevation NUMBER; b_bv_d_not_estimated boolean := FALSE; b_bv_r_not_estimated boolean := FALSE; b_bv_m_not_estimated boolean := FALSE; 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; /****************************************************************************** Procedure: set_pt_contrib Purpose: Set Mean Collection Geography, Genetic Worth (GW), Effective Population Size (Ne) and other values calculated based on Parent Tree Contribution. p_replace_values = TRUE forces replacing of calculated values (otherwise they are only replaced based on status or the user blanking-out the current value) ******************************************************************************/ PROCEDURE set_pt_contrib ( p_replace_values IN BOOLEAN ) IS CONST_DEFAULT_UNTESTED_GW_G CONSTANT NUMBER(1) := SPR_CONSTANTS.CONST_DEFAULT_GEN_QLTY_G; CONST_DEFAULT_CUSTOM_GW_G CONSTANT NUMBER(1) := SPR_CONSTANTS.CONST_DEFAULT_GEN_QLTY_G; CONST_DEFAULT_UNTESTED_GW_D CONSTANT NUMBER(1) := SPR_CONSTANTS.CONST_DEFAULT_GEN_QLTY_D; CONST_DEFAULT_CUSTOM_GW_D CONSTANT NUMBER(1) := SPR_CONSTANTS.CONST_DEFAULT_GEN_QLTY_D; CONST_DEFAULT_UNTESTED_GW_R CONSTANT NUMBER(1) := SPR_CONSTANTS.CONST_DEFAULT_GEN_QLTY_R; CONST_DEFAULT_CUSTOM_GW_R CONSTANT NUMBER(1) := SPR_CONSTANTS.CONST_DEFAULT_GEN_QLTY_R; CONST_DEFAULT_UNTESTED_GW_M CONSTANT NUMBER(1) := SPR_CONSTANTS.CONST_DEFAULT_GEN_QLTY_R; CONST_DEFAULT_CUSTOM_GW_M CONSTANT NUMBER(1) := SPR_CONSTANTS.CONST_DEFAULT_GEN_QLTY_R; b_all_gw_blank boolean := FALSE; BEGIN --Collection Elev (do mean/min/max as a unit since only mean is --on the page and can be blanked for replacing) IF replace_area_of_use(TO_CHAR(g_collection_elevation)) OR p_replace_values THEN set_collection_elevation(r_pt_contrib.collection_elevation); set_collection_elevation_min(r_pt_contrib.collection_elevation_min); set_collection_elevation_max(r_pt_contrib.collection_elevation_max); END IF; --Collection Latitude IF is_area_of_use_status OR is_collection_lat_empty OR p_replace_values THEN set_collection_lat_deg(r_pt_contrib.collection_lat_deg); set_collection_lat_min(r_pt_contrib.collection_lat_min); set_collection_lat_sec(r_pt_contrib.collection_lat_sec); END IF; --Collection Longitude IF is_area_of_use_status OR is_collection_long_empty OR p_replace_values THEN set_collection_long_deg(r_pt_contrib.collection_long_deg); set_collection_long_min(r_pt_contrib.collection_long_min); set_collection_long_sec(r_pt_contrib.collection_long_sec); END IF; --SMP Mean BV-G IF replace_area_of_use(TO_CHAR(g_smp_mean_bv_growth)) OR p_replace_values THEN set_smp_mean_bv_growth(r_pt_contrib.smp_mean_bv_growth); END IF; --SMP Mean BV-D IF replace_area_of_use(TO_CHAR(g_smp_mean_bv_density)) OR p_replace_values THEN set_smp_mean_bv_density(r_pt_contrib.smp_mean_bv_density); END IF; --SMP Mean BV-R IF replace_area_of_use(TO_CHAR(g_smp_mean_bv_resistance)) OR p_replace_values THEN set_smp_mean_bv_resistance(r_pt_contrib.smp_mean_bv_resistance); END IF; --SMP Mean BV-M IF replace_area_of_use(TO_CHAR(g_smp_mean_bv_major_gene)) OR p_replace_values THEN set_smp_mean_bv_major_gene(r_pt_contrib.smp_mean_bv_major_gene); END IF; --SMP Success IF replace_area_of_use(TO_CHAR(g_smp_success_pct)) OR p_replace_values THEN set_smp_success_pct(r_pt_contrib.smp_success_pct); END IF; --Orchard Contam IF replace_area_of_use(TO_CHAR(g_orchard_contamination_pct)) OR p_replace_values THEN set_orchard_contamination_pct(r_pt_contrib.orchard_contamination_pct); END IF; --Effective Population Size IF replace_area_of_use(TO_CHAR(g_effective_pop_size)) OR p_replace_values THEN set_effective_pop_size(r_pt_contrib.effective_pop_size); END IF; --Total Parent Trees and ratio untested and tested set_total_parent_trees(r_pt_contrib.total_parent_trees); g_tested_parent_trees_pct := r_pt_contrib.pct_tested_parent_trees; g_untested_parent_trees_pct := r_pt_contrib.pct_untested_parent_trees; -- If all the gw values have been blanked-out, reset to the calculated gw values. IF g_gw_g IS NULL AND g_gw_d IS NULL AND g_gw_r IS NULL AND g_gw_m IS NULL THEN b_all_gw_blank := TRUE; END IF; --Genetic Worth - Growth -->NOTE: Interim rule (see pt#28587) - if GW-G has been blanked and another GW value exists do not replace GW-G IF is_area_of_use_status OR p_replace_values OR b_all_gw_blank THEN set_gw_g(NULL); IF g_seedlot_source_code = 'UPT' THEN IF r_pt_contrib.total_parent_trees > 0 THEN --Untested lots get default GW-G value set_gw_g(CONST_DEFAULT_UNTESTED_GW_G); END IF; ELSIF g_seedlot_source_code = 'CUS' THEN IF r_pt_contrib.total_parent_trees > 0 THEN --Custom lots get default GW-G value set_gw_g(CONST_DEFAULT_CUSTOM_GW_G); END IF; ELSE set_gw_g(r_pt_contrib.gw_g); END IF; END IF; --Genetic Worth - Density IF is_area_of_use_status OR p_replace_values OR b_all_gw_blank THEN set_gw_d(NULL); IF g_seedlot_source_code = 'UPT' AND r_pt_contrib.gw_d IS NOT NULL THEN IF r_pt_contrib.total_parent_trees > 0 THEN --Untested lots get default GW-D value set_gw_d(CONST_DEFAULT_UNTESTED_GW_D); END IF; ELSIF g_seedlot_source_code = 'CUS' AND r_pt_contrib.gw_d IS NOT NULL THEN IF r_pt_contrib.total_parent_trees > 0 THEN --Custom lots get default GW-D value set_gw_d(CONST_DEFAULT_CUSTOM_GW_D); END IF; ELSE set_gw_d(r_pt_contrib.gw_d); END IF; END IF; --Genetic Worth - Resistance IF is_area_of_use_status OR p_replace_values OR b_all_gw_blank THEN set_gw_r(NULL); IF g_seedlot_source_code = 'UPT' AND r_pt_contrib.gw_r IS NOT NULL THEN IF r_pt_contrib.total_parent_trees > 0 THEN --Untested lots get default GW-R value set_gw_r(CONST_DEFAULT_UNTESTED_GW_R); END IF; ELSIF g_seedlot_source_code = 'CUS' AND r_pt_contrib.gw_r IS NOT NULL THEN IF r_pt_contrib.total_parent_trees > 0 THEN --Custom lots get default GW-R value set_gw_r(CONST_DEFAULT_CUSTOM_GW_R); END IF; ELSE set_gw_r(r_pt_contrib.gw_r); END IF; END IF; --Genetic Worth - Major Gene Resistance IF is_area_of_use_status OR p_replace_values OR b_all_gw_blank THEN set_gw_m(NULL); IF g_seedlot_source_code = 'UPT' AND r_pt_contrib.gw_m IS NOT NULL THEN IF r_pt_contrib.total_parent_trees > 0 THEN --Untested lots get default GW-M value set_gw_m(CONST_DEFAULT_UNTESTED_GW_M); END IF; ELSIF g_seedlot_source_code = 'CUS' AND r_pt_contrib.gw_m IS NOT NULL THEN IF r_pt_contrib.total_parent_trees > 0 THEN --Custom lots get default GW-M value set_gw_m(CONST_DEFAULT_CUSTOM_GW_M); END IF; ELSE set_gw_m(r_pt_contrib.gw_m); END IF; END IF; END set_pt_contrib; /****************************************************************************** Procedure: recalc_all Purpose: Determines context (A or B class, BC Source, etc) and recalculate all defaults and derives Area of Use information in context. ******************************************************************************/ PROCEDURE recalc_all ( p_pt_array IN OUT spr_001a_object_varray , p_get_current_tests IN BOOLEAN --ignored if is_area_of_use_status() , p_replace_values IN BOOLEAN , p_role_list IN VARCHAR2) IS BEGIN get_previous_seedlot_values; IF g_genetic_class_code = 'B' THEN --Reset 'A' only values set_orchard_id(NULL); set_effective_pop_size(NULL); set_seed_plan_unit_id(NULL); set_seedlot_source_code(NULL); set_biotech_processes_ind(NULL); set_contaminant_pollen_bv(NULL); set_controlled_cross_ind(NULL); set_female_gametic_mthd_code(NULL); set_male_gametic_mthd_code(NULL); set_orchard_comment(NULL); set_orchard_contamination_pct(NULL); set_pollen_contamination_ind(NULL); set_pollen_contam_mthd_code(NULL); set_pollen_contamination_pct(NULL); set_secondary_orchard_id(NULL); set_smp_mean_bv_growth(NULL); set_smp_mean_bv_density(NULL); set_smp_mean_bv_resistance(NULL); set_smp_mean_bv_major_gene(NULL); set_smp_parents_outside(NULL); set_smp_success_pct(NULL); set_total_parent_trees(NULL); set_collection_source_code(NULL); set_fs721a_signed_ind(NULL); ELSE --Reset 'B' only values set_superior_prvnc_ind(NULL); set_provenance_id(NULL); set_collection_locn_desc(NULL); set_coll_standard_met_ind(NULL); set_nmbr_trees_from_code(NULL); set_collection_bgc_ind(NULL); set_collection_spz_ind(NULL); set_seed_plan_zone_code(NULL); set_seed_coast_area_code(NULL); set_collection_area_radius(NULL); set_bgc_zone_code(NULL); set_bgc_subzone_code(NULL); set_variant(NULL); set_bec_version_id(NULL); END IF; --BC Source IF g_bc_source_ind = 'Y' THEN --Default latitude code IF g_collection_lat_deg IS NOT NULL OR g_collection_lat_min IS NOT NULL OR g_collection_lat_sec IS NOT NULL THEN set_collection_latitude_code('N'); END IF; --Default longitude code IF g_collection_long_deg IS NOT NULL OR g_collection_long_min IS NOT NULL OR g_collection_long_sec IS NOT NULL THEN set_collection_longitude_code('W'); END IF; --Non-BC Source ELSIF g_bc_source_ind = 'N' THEN IF g_genetic_class_code = 'A' THEN set_superior_prvnc_ind(NULL); ELSIF g_genetic_class_code = 'B' THEN set_superior_prvnc_ind('N'); END IF; set_org_unit_no(NULL); set_provenance_id(NULL); set_bgc_zone_code(NULL); set_bgc_subzone_code(NULL); set_variant(NULL); set_bec_version_id(NULL); set_collection_bgc_ind(NULL); set_seed_plan_zone_code(NULL); set_collection_spz_ind(NULL); set_seed_coast_area_code(NULL); set_coll_standard_met_ind(NULL); END IF; IF g_superior_prvnc_ind = 'Y' THEN set_collection_locn_desc(NULL); ELSIF g_superior_prvnc_ind = 'N' THEN set_provenance_id(NULL); set_coll_standard_met_ind(NULL); --No GW for Non-Sup Prov B Lots set_gw_g(NULL); set_gw_d(NULL); set_gw_r(NULL); set_gw_m(NULL); END IF; --Status IF r_previous.seedlot_status_code = 'SUB' AND g_seedlot_status_code IN ('PND','INC') THEN --java should not allow SUB->INC but just in case set_declared_userid(NULL); set_declared_timestamp(NULL); END IF; --Species IF g_vegetation_code IS NULL THEN set_provenance_id(NULL); set_orchard_id(NULL); set_secondary_orchard_id(NULL); set_seed_plan_unit_id(NULL); --If species changed ELSIF g_vegetation_code != NVL(r_previous.vegetation_code,'~') THEN --Check species-specific information -->provenanance IF g_provenance_id IS NOT NULL AND (NOT provenance_is_valid_for_spp) THEN set_provenance_id(NULL); END IF; -->orchard IF g_orchard_id IS NOT NULL AND (NOT orchard_is_valid_for_species(g_orchard_id)) THEN set_orchard_id(NULL); END IF; -->secondary orchard IF g_secondary_orchard_id IS NOT NULL AND (NOT orchard_is_valid_for_species(g_secondary_orchard_id)) THEN set_secondary_orchard_id(NULL); END IF; -->spu IF g_seed_plan_unit_id IS NULL AND (NOT spu_is_valid_for_species) THEN set_seed_plan_unit_id(NULL); END IF; END IF; --Derive Parent Tree Contribution information --> GW, Effective Population Size, Collection Geography, etc. IF g_genetic_class_code = 'A' AND r_previous.seedlot_status_code != 'INC' THEN --populates r_pt_contrib calc_pt_contrib(p_pt_array,p_get_current_tests); --set pt contribution fields set_pt_contrib(p_replace_values); END IF; --Apply transfer limits to get Area of Use --> may use Collection Geography derived from pt contrib get_area_of_use; --Set Mean Area of Use geography to Mean Collection geography. set_mean_area_of_use_geography; END recalc_all; FUNCTION valid_lot_number_range ( p_seedlot_number IN VARCHAR2) RETURN BOOLEAN IS b_lot_number_valid BOOLEAN := TRUE; BEGIN --Check ranges (for pre-numbered FS721 and FS721A forms already issued) IF g_genetic_class_code = 'A' THEN IF TO_NUMBER(g_seedlot_number) NOT BETWEEN TO_NUMBER(CONST_CLASS_B_LOTNUM_MAX)+1 AND TO_NUMBER(CONST_CLASS_A_LOTNUM_MIN)-1 THEN g_error_message := g_error_message || 'spar.web.error.usr.new_lot_number:' || g_genetic_class_code ||',' || TO_CHAR(TO_NUMBER(CONST_CLASS_B_LOTNUM_MAX)+1)||',' || TO_CHAR(TO_NUMBER(CONST_CLASS_A_LOTNUM_MIN)-1)||';'; b_lot_number_valid := FALSE; END IF; ELSIF g_genetic_class_code = 'B' THEN IF TO_NUMBER(g_seedlot_number) >= TO_NUMBER(CONST_CLASS_B_LOTNUM_MIN) THEN g_error_message := g_error_message || 'spar.web.error.usr.new_lot_number:' || g_genetic_class_code ||',00001,' || TO_CHAR(TO_NUMBER(CONST_CLASS_B_LOTNUM_MIN)-1)||';'; b_lot_number_valid := FALSE; END IF; END IF; RETURN b_lot_number_valid; END valid_lot_number_range; /****************************************************************************** Procedure: validate_lot_number Purpose: If lot number specified on add, ensure it is not a dup and that it is in acceptable range for entry ******************************************************************************/ PROCEDURE validate_lot_number IS v_temp_num NUMBER(1); b_lot_number_valid BOOLEAN := TRUE; BEGIN --If adding and lot number specified IF g_revision_count IS NULL AND g_seedlot_number IS NOT NULL THEN b_lot_number_valid := valid_lot_number_range(g_seedlot_number); IF b_lot_number_valid THEN --Check for dups SELECT 1 INTO v_temp_num FROM seedlot WHERE seedlot_number = g_seedlot_number; g_error_message := g_error_message || 'ca.bc.gov.mof.sil.webade.db.error.usr.field.alreadyExists:Seedlot;'; END IF; END IF; EXCEPTION WHEN NO_DATA_FOUND THEN NULL; END validate_lot_number; /****************************************************************************** Procedure: validate_seedlot_cancel Purpose: Check whether it is OK to cancel seedlot ******************************************************************************/ PROCEDURE validate_seedlot_cancel (p_role_list IN OUT VARCHAR2, p_action_name IN OUT VARCHAR2) IS temp_code SEEDLOT.SEEDLOT_NUMBER%TYPE; status_code SEEDLOT.SEEDLOT_STATUS_CODE%TYPE; cancel_authority VARCHAR2(1); txn_count NUMBER(5); rqst_count NUMBER(5); BEGIN -- set authority/user flag cancel_authority := Spr_Check_Field_Authority(p_role_list,'CANCEL', p_action_name); -- don't bother WITH updates IF lot NOT FOUND SELECT SEEDLOT_NUMBER, SEEDLOT_STATUS_CODE INTO temp_code, status_code FROM SEEDLOT WHERE SEEDLOT_NUMBER = g_seedlot_number AND REVISION_COUNT = g_revision_count; -- check if user has authority to cancel IF g_seedlot_status_code = 'CAN' THEN IF cancel_authority = 'N' THEN g_error_message := g_error_message || 'spar.web.error.usr.seedlotstatus.noauthoritytocancel;'; ELSE -- Make sure there are no transactions or requests against the lot SELECT COUNT(*) INTO txn_count FROM SEEDLOT_TRANSACTION WHERE SEEDLOT_NUMBER = g_seedlot_number; SELECT COUNT(*) INTO rqst_count FROM REQUEST_SEEDLOT WHERE SEEDLOT_NUMBER = g_seedlot_number; IF txn_count + rqst_count > 0 THEN g_error_message := g_error_message || 'spar.web.error.usr.seedlotcancel.records_exist;'; END IF; END IF; END IF; END validate_seedlot_cancel; /****************************************************************************** Procedure: validate_seedlot_cancel (OVERLOADED) Purpose: Check whether it is OK to cancel seedlot - NEW RULES. Assumes interface has validated user's auth to CANcel. ******************************************************************************/ PROCEDURE validate_seedlot_cancel IS v_txn_count NUMBER(10); v_rqst_count NUMBER(10); BEGIN IF g_seedlot_status_code = 'CAN' AND r_previous.seedlot_status_code != 'CAN' THEN -- Make sure there are no transactions against the lot SELECT COUNT(1) INTO v_txn_count FROM seedlot_transaction WHERE seedlot_number = g_seedlot_number; -- Make sure there are no requests against the lot SELECT COUNT(1) INTO v_rqst_count FROM request_seedlot WHERE seedlot_number = g_seedlot_number; IF v_txn_count + v_rqst_count > 0 THEN g_error_message := g_error_message || 'spar.web.error.usr.seedlotcancel.records_exist;'; END IF; END IF; END validate_seedlot_cancel; /****************************************************************************** Procedure: validate_genetic_worth_change Purpose: Check whether it is OK to change genetic worth ******************************************************************************/ PROCEDURE validate_genetic_worth_change (p_role_list IN OUT VARCHAR2, p_action_name IN OUT VARCHAR2) IS temp_code SEEDLOT.SEEDLOT_NUMBER%TYPE; status_code SEEDLOT.SEEDLOT_STATUS_CODE%TYPE; prev_genetic_class_code SEEDLOT.GENETIC_CLASS_CODE%TYPE; gen_class_authority VARCHAR2(1); BEGIN -- set authority/user flag gen_class_authority := Spr_Check_Field_Authority(p_role_list,'CHANGE GENETIC CLASS', P_action_name); IF g_revision_count IS NOT NULL THEN -- don't bother WITH updates IF lot NOT FOUND SELECT SEEDLOT_NUMBER, SEEDLOT_STATUS_CODE, GENETIC_CLASS_CODE INTO TEMP_CODE, STATUS_CODE, PREV_GENETIC_CLASS_CODE FROM SEEDLOT WHERE SEEDLOT_NUMBER = g_seedlot_number AND REVISION_COUNT = g_revision_count; -- check if genetic class has changed IF prev_genetic_class_code IS NOT NULL AND g_genetic_class_code <> prev_genetic_class_code AND gen_class_authority = 'N' THEN g_error_message := g_error_message || 'spar.web.error.usr.class.insufficientauthority;'; END IF; END IF; END validate_genetic_worth_change; /****************************************************************************** Procedure: validate_genetic_class Purpose: Genetic Class and Collection Source cannot be null ******************************************************************************/ PROCEDURE validate_genetic_class IS BEGIN IF g_genetic_class_code IS NULL OR g_collection_source_code IS NULL THEN g_error_message := g_error_message || 'spar.web.error.usr.required.field:Genetic Class;'; END IF; END validate_genetic_class; /****************************************************************************** Procedure: validate_org Purpose: Validate org unit for Ministry user ******************************************************************************/ PROCEDURE validate_org (p_user_org_unit_no IN NUMBER) IS BEGIN IF g_org_unit_no IS NOT NULL AND p_user_org_unit_no IS NOT NULL THEN IF sil_check_user_org_authority(p_user_org_unit_no,g_org_unit_no) = 'N' THEN g_error_message := g_error_message || 'spar.web.error.usr.org_unit_code.you_must_belong_to_or_have_authority;'; END IF; END IF; END validate_org; /****************************************************************************** Procedure: validate_orchard_id Purpose: Ensure Orchard Id is not null in certain conditions ******************************************************************************/ PROCEDURE validate_orchard_id IS BEGIN IF g_orchard_id IS NULL AND SUBSTR(g_collection_source_code,1,1) = 'A' AND r_previous.genetic_class_code = 'A' AND r_previous.orchard_id IS NOT NULL THEN g_error_message := g_error_message || 'spar.web.error.usr.mandatory:Orchard ID, Genetic Class, A;'; END IF; END validate_orchard_id; /****************************************************************************** Procedure: validate_collection_locn Purpose: Ensure Collection Location is not null in certain conditions ******************************************************************************/ PROCEDURE validate_collection_locn IS BEGIN IF g_collection_locn_desc IS NULL AND SUBSTR(g_collection_source_code,1,1) = 'B' AND r_previous.genetic_class_code = 'B' AND r_previous.collection_locn_desc IS NOT NULL THEN g_error_message := g_error_message || 'spar.web.error.usr.mandatory:Collection LOCATION, Genetic Class, B;'; END IF; END validate_collection_locn; /****************************************************************************** Procedure: validate_coast_geo Purpose: Ensure Coast geographic location code is entered if previously entered ******************************************************************************/ PROCEDURE validate_coast_geo (p_spz1 IN OUT VARCHAR2) IS BEGIN IF g_seed_coast_area_code IS NULL AND r_previous.seed_coast_area_code IS NOT NULL AND r_previous.genetic_class_code = 'B' AND g_genetic_class_code = 'B' AND p_spz1 = 'M' AND r_previous.seed_plan_zone_code IS NOT NULL THEN g_error_message := g_error_message || 'spar.web.error.usr.spz_coastal_geo_area_mandatory;'; END IF; END validate_coast_geo; /****************************************************************************** Procedure: validate_mean_elevation Purpose: Ensure mean elevation is entered ******************************************************************************/ PROCEDURE validate_mean_elevation IS BEGIN IF NVL(g_elevation,0) = 0 AND r_previous.elevation > 0 THEN g_error_message := g_error_message || 'spar.web.error.usr.required.field:Mean Elevation;'; END IF; END validate_mean_elevation; /****************************************************************************** Procedure: validate_mean_latitude Purpose: Ensure mean latitude is entered ******************************************************************************/ PROCEDURE validate_mean_latitude IS BEGIN IF NVL(g_latitude_degrees,0) = 0 AND r_previous.latitude_degrees > 0 THEN g_error_message := g_error_message || 'spar.web.error.usr.required.field:Mean Latitude;'; END IF; END validate_mean_latitude; /****************************************************************************** Procedure: validate_min_latitude Purpose: Ensure min latitude is entered ******************************************************************************/ PROCEDURE validate_min_latitude IS BEGIN IF NVL(g_latitude_deg_min,0) = 0 AND r_previous.latitude_deg_min > 0 THEN g_error_message := g_error_message || 'spar.web.error.usr.required.field:Min Latitude;'; END IF; END validate_min_latitude; /****************************************************************************** Procedure: validate_max_latitude Purpose: Ensure max latitude is entered ******************************************************************************/ PROCEDURE validate_max_latitude IS BEGIN IF NVL(g_latitude_deg_max,0) = 0 AND r_previous.latitude_deg_max > 0 THEN g_error_message := g_error_message || 'spar.web.error.usr.required.field:Max Latitude;'; END IF; END validate_max_latitude; /****************************************************************************** Procedure: validate_mean_longitude Purpose: Ensure mean longitude is entered ******************************************************************************/ PROCEDURE validate_mean_longitude IS BEGIN IF NVL(g_longitude_degrees,0) = 0 AND r_previous.longitude_degrees > 0 THEN g_error_message := g_error_message || 'spar.web.error.usr.required.field:Mean Longitude;'; END IF; END validate_mean_longitude; /****************************************************************************** Procedure: validate_min_longitude Purpose: Ensure min longitude is entered ******************************************************************************/ PROCEDURE validate_min_longitude IS BEGIN IF NVL(g_longitude_deg_min,0) = 0 AND r_previous.longitude_deg_min > 0 THEN g_error_message := g_error_message || 'spar.web.error.usr.required.field:Min Longitude;'; END IF; END validate_min_longitude; /****************************************************************************** Procedure: validate_max_longitude Purpose: Ensure max longitude is entered ******************************************************************************/ PROCEDURE validate_max_longitude IS BEGIN IF NVL(g_longitude_deg_max,0) = 0 AND r_previous.longitude_deg_max > 0 THEN g_error_message := g_error_message || 'spar.web.error.usr.required.field:Max Longitude;'; END IF; END validate_max_longitude; /****************************************************************************** Procedure: validate_bgc_zone Purpose: Ensure bgc zone is entered ******************************************************************************/ PROCEDURE validate_bgc_zone IS BEGIN IF g_bgc_zone_code IS NULL AND r_previous.bgc_zone_code IS NOT NULL AND r_previous.genetic_class_code = 'B' AND g_genetic_class_code = 'B' THEN g_error_message := g_error_message || 'spar.web.error.usr.mandatory:BGC Zone, Genetic Class, B;'; END IF; END validate_bgc_zone; /****************************************************************************** Procedure: validate_collection_start Purpose: Ensure collection start date is entered ******************************************************************************/ PROCEDURE validate_collection_start IS BEGIN IF g_collection_start_date IS NULL AND r_previous.collection_start_date IS NOT NULL THEN g_error_message := g_error_message || 'spar.web.error.usr.required.field:Collection START DATE;'; END IF; END validate_collection_start; /****************************************************************************** Procedure: validate_collection_end Purpose: Ensure collection end date is entered ******************************************************************************/ PROCEDURE validate_collection_end IS BEGIN IF g_collection_end_date IS NULL AND r_previous.collection_end_date IS NOT NULL THEN g_error_message := g_error_message || 'spar.web.error.usr.required.field:Collection END DATE;'; END IF; END validate_collection_end; /****************************************************************************** Procedure: validate_vol_per_container Purpose: Ensure volume per container is entered ******************************************************************************/ PROCEDURE validate_vol_per_container IS BEGIN IF NVL(g_vol_per_container,0) = 0 AND NVL(r_previous.vol_per_container,0) <> 0 THEN g_error_message := g_error_message || 'spar.web.error.usr.required.field:Volume per Container;'; END IF; END validate_vol_per_container; /****************************************************************************** Procedure: validate_nmbr_trees Purpose: Ensure number of trees collected is entered ******************************************************************************/ PROCEDURE validate_nmbr_trees IS BEGIN IF g_nmbr_trees_from_code IS NULL AND r_previous.nmbr_trees_from_code IS NOT NULL AND r_previous.genetic_class_code = 'B' AND g_genetic_class_code = 'B' THEN g_error_message := g_error_message || 'spar.web.error.usr.mandatory:NUMBER OF Trees Collected FROM, Genetic Class, B;'; END IF; END validate_nmbr_trees; /****************************************************************************** Procedure: validate_cone_collection Purpose: Ensure cone collection method is entered ******************************************************************************/ PROCEDURE validate_cone_collection IS BEGIN IF g_cone_collection_method_cd IS NULL AND r_previous.cone_collection_method_code IS NOT NULL THEN g_error_message := g_error_message || 'spar.web.error.usr.required.field:Cone Collection Method;'; END IF; END validate_cone_collection; /****************************************************************************** Procedure: validate_vegetation_code Purpose: Ensure vegetation code is not blank ******************************************************************************/ PROCEDURE validate_vegetation_code IS BEGIN IF g_vegetation_code IS NULL THEN g_error_message := g_ERROR_MESSAGE || 'spar.web.error.usr.required.field:Species;'; END IF; END validate_vegetation_code; /****************************************************************************** Procedure: validate_coast_geo_mandatory Purpose: Ensure Coast geographic location code is not blank ******************************************************************************/ PROCEDURE validate_coast_geo_mandatory (p_spz1 IN OUT VARCHAR2) IS BEGIN IF g_seedlot_status_code <> 'COM' AND g_seed_coast_area_code IS NULL AND g_genetic_class_code = 'B' AND p_spz1 = 'M' THEN g_error_message := g_error_message || 'spar.web.error.usr.spz_coastal_geo_area_mandatory;'; END IF; END validate_coast_geo_mandatory; /****************************************************************************** Procedure: validate_superior_provenance Purpose: Ensure species and provenance agree ******************************************************************************/ PROCEDURE validate_superior_provenance IS CURSOR c_prov IS SELECT vegetation_code FROM superior_provenance WHERE vegetation_code = g_vegetation_code; r_prov c_prov%ROWTYPE; BEGIN --Can species have Superior Provenance? IF g_superior_prvnc_ind = 'Y' AND g_vegetation_code IS NOT NULL THEN OPEN c_prov; FETCH c_prov INTO r_prov; CLOSE c_prov; IF r_prov.vegetation_code IS NULL THEN g_error_message := g_error_message || 'spar.web.error.usr.no.superior.prov;'; END IF; END IF; --Is Collection Provenance valid for Species? IF g_provenance_id IS NOT NULL AND g_vegetation_code IS NOT NULL AND NOT provenance_is_valid_for_spp THEN IF r_prov.vegetation_code != g_vegetation_code THEN g_error_message := g_error_message || 'spar.web.error.usr.lot_spp:Collection Provenance selected;'; END IF; END IF; END validate_superior_provenance; /****************************************************************************** Procedure: validate_b_sup_prov_geog Purpose: Validate collection geography based on superior provenance. Currently only ELEVATION is validated. We may be able to validate 8km radius rule spatially in future. ******************************************************************************/ PROCEDURE validate_b_sup_prov_geog IS CURSOR c_prov IS SELECT provenance_id , collection_elevation_min , collection_elevation_max FROM superior_provenance WHERE provenance_id = g_provenance_id AND vegetation_code = g_vegetation_code; r_prov c_prov%ROWTYPE; CURSOR c_prov_spz IS SELECT seed_plan_zone_code FROM superior_provenance_plan_zone WHERE provenance_id = r_prov.provenance_id AND seed_plan_zone_code = g_seed_plan_zone_code; r_prov_spz c_prov_spz%ROWTYPE; BEGIN IF g_provenance_id IS NOT NULL AND g_vegetation_code IS NOT NULL THEN OPEN c_prov; FETCH c_prov INTO r_prov; CLOSE c_prov; IF r_prov.provenance_id IS NOT NULL THEN --Collection Elevation IF g_collection_elevation NOT BETWEEN NVL(r_prov.collection_elevation_min,-999999) AND NVL(r_prov.collection_elevation_max,999999) THEN g_error_message := g_error_message || 'spar.web.error.usr.sup_prov_geog:Collection Elevation Mean,' || TO_CHAR(r_prov.collection_elevation_min)||'-' || TO_CHAR(r_prov.collection_elevation_max)||'m;'; END IF; IF g_collection_elevation_min NOT BETWEEN NVL(r_prov.collection_elevation_min,-999999) AND NVL(r_prov.collection_elevation_max,999999) THEN g_error_message := g_error_message || 'spar.web.error.usr.sup_prov_geog:Collection Elevation Min,' || TO_CHAR(r_prov.collection_elevation_min)||'-' || TO_CHAR(r_prov.collection_elevation_max)||'m;'; END IF; IF g_collection_elevation_max NOT BETWEEN NVL(r_prov.collection_elevation_min,-999999) AND NVL(r_prov.collection_elevation_max,999999) THEN g_error_message := g_error_message || 'spar.web.error.usr.sup_prov_geog:Collection Elevation Max,' || TO_CHAR(r_prov.collection_elevation_min)||'-' || TO_CHAR(r_prov.collection_elevation_max)||'m;'; END IF; IF g_seed_plan_zone_code IS NOT NULL THEN --Collection SPZ OPEN c_prov_spz; FETCH c_prov_spz INTO r_prov_spz; CLOSE c_prov_spz; IF r_prov_spz.seed_plan_zone_code IS NULL THEN g_error_message := g_error_message || 'spar.web.error.usr.lot_coll_spz;'; END IF; END IF; END IF; END IF; END validate_b_sup_prov_geog; /****************************************************************************** Procedure: validate_orchard_for_spp Purpose: Ensure species and orchard agree ******************************************************************************/ PROCEDURE validate_orchard_for_spp ( p_orchard_id IN VARCHAR2 , p_msg IN VARCHAR2) IS CURSOR c_orchard IS SELECT vegetation_code FROM orchard WHERE orchard_id = p_orchard_id; r_orchard c_orchard%ROWTYPE; BEGIN IF p_orchard_id IS NOT NULL THEN OPEN c_orchard; FETCH c_orchard INTO r_orchard; CLOSE c_orchard; IF r_orchard.vegetation_code != g_vegetation_code THEN g_error_message := g_error_message || 'spar.web.error.usr.lot_spp:'||p_msg||';'; END IF; END IF; END validate_orchard_for_spp; /****************************************************************************** Procedure: validate_collection_spz_for_spp Purpose: Ensure species and spz agree ******************************************************************************/ PROCEDURE validate_spz_spp ( p_spz IN VARCHAR2 , p_msg IN VARCHAR2) IS CURSOR c_spz IS SELECT seed_plan_zone_id FROM seed_plan_zone WHERE seed_plan_zone_code = p_spz AND (vegetation_code = g_vegetation_code OR vegetation_code IS NULL) AND genetic_class_code = g_genetic_class_code; r_spz c_spz%ROWTYPE; BEGIN IF p_spz IS NOT NULL AND g_vegetation_code IS NOT NULL AND g_genetic_class_code IS NOT NULL THEN OPEN c_spz; FETCH c_spz INTO r_spz; CLOSE c_spz; IF r_spz.seed_plan_zone_id IS NULL THEN g_error_message := g_error_message || 'spar.web.error.usr.spz.invalid.combo.parm:'||p_msg||','||p_spz||';'; END IF; END IF; END validate_spz_spp; /****************************************************************************** Procedure: validate_mandatory_APP Purpose: Performs mandatory validation for status of APP and above ******************************************************************************/ PROCEDURE validate_mandatory_APP ( p_value IN VARCHAR2 , p_msg IN VARCHAR2) IS BEGIN IF g_seedlot_status_code NOT IN ('INC','PND','CAN','SUB') AND p_value IS NULL THEN g_error_message := g_error_message || 'spar.web.error.usr.required.field:'||p_msg||';'; END IF; END validate_mandatory_APP; /****************************************************************************** Procedure: validate_tested_untested_ratio Purpose: Validates the ratio of tested to untested parent trees ******************************************************************************/ PROCEDURE validate_tested_untested_ratio IS CONST_TESTED_PT_RATIO CONSTANT NUMBER(2) := 70; CONST_UNTESTED_PT_RATIO CONSTANT NUMBER(2) := 70; CONST_CUSTOM_TESTED_PT_RATIO CONSTANT NUMBER(2) := 70; BEGIN IF g_genetic_class_code = 'A' AND g_seedlot_status_code IN ('SUB','APP','COM','EXP') THEN --Lots from Tested Parent Trees must be comprised of 70% tested pt's IF is_lot_under_CFS AND g_seedlot_source_code = 'TPT' AND r_pt_contrib.total_parent_trees > 0 AND r_pt_contrib.pct_tested_parent_trees < CONST_TESTED_PT_RATIO THEN g_error_message := g_error_message || 'spar.web.error.usr.a_class_ratio:Tested,at least ' ||TO_CHAR(CONST_TESTED_PT_RATIO)||'%,Tested,' ||TO_CHAR(r_pt_contrib.pct_tested_parent_trees)||';'; --Custom lots must be comprised of at least 70% tested pt's ELSIF is_lot_under_CFS AND g_seedlot_source_code = 'CUS' AND r_pt_contrib.total_parent_trees > 0 AND r_pt_contrib.pct_tested_parent_trees < CONST_CUSTOM_TESTED_PT_RATIO THEN g_error_message := g_error_message || 'spar.web.error.usr.a_class_ratio:Custom,at least ' ||TO_CHAR(CONST_CUSTOM_TESTED_PT_RATIO)||'%,Tested,' ||TO_CHAR(r_pt_contrib.pct_tested_parent_trees)||';'; --Untested Parent Trees must be comprised of <70% tested pt's ELSIF is_lot_under_CFS AND g_seedlot_source_code = 'UPT' AND r_pt_contrib.total_parent_trees > 0 AND r_pt_contrib.pct_tested_parent_trees >= CONST_UNTESTED_PT_RATIO THEN g_error_message := g_error_message || 'spar.web.error.usr.a_class_ratio:Untested,less than ' ||TO_CHAR(CONST_UNTESTED_PT_RATIO)||'%,Tested,' ||TO_CHAR(r_pt_contrib.pct_tested_parent_trees)||';'; END IF; --Lots under Chief Forester's Standards must have Parent Trees /* edit is being relaxed during transition to new page to allow TSC to get lots approved without parent trees IF is_lot_under_CFS AND r_pt_contrib.total_parent_trees = 0 THEN g_error_message := g_error_message || 'spar.web.error.usr.no_trees;'; END IF; */ END IF; END validate_tested_untested_ratio; /****************************************************************************** Procedure: validate_seedlot_source_code Purpose: Ensures that TPT cannot be selected if the associated seedlot_plan_unit is not primary. ******************************************************************************/ PROCEDURE validate_seedlot_source_code IS v_primary_spu_selected_ind CHAR(1); BEGIN IF (g_seedlot_source_code = 'TPT') THEN -- A primary spu must be selected in order to select tested parent trees. SELECT DECODE(COUNT('x'), 0, 'Y', 'N') INTO v_primary_spu_selected_ind FROM seed_plan_unit spu WHERE spu.seed_plan_unit_id = g_seed_plan_unit_id AND spu.primary_ind = 'N'; IF (v_primary_spu_selected_ind = 'N') THEN g_error_message := g_error_message || 'spar.web.error.usr.database.primarySpuSelected;'; END IF; END IF; END validate_seedlot_source_code; /****************************************************************************** Procedure: validate_collection_elevation_min_max Purpose: Ensure the range between collection elevation min/max is within the CFS specified range for B class lots ******************************************************************************/ PROCEDURE validate_colln_elev_min_max IS e_no_limits_found EXCEPTION; v_min_max_range NUMBER; --Limits for collection elevation min/max CURSOR c_limit IS SELECT transfer_limit_skey , max_collection_elevation_range FROM transfer_limit tl WHERE ( vegetation_code = g_vegetation_code OR vegetation_code IS NULL) AND genetic_class_code = g_genetic_class_code AND superior_prvnc_ind = NVL(g_superior_prvnc_ind,'N') AND coast_interior_code = spr_get_spz_type(g_seed_plan_zone_code) AND ( seed_plan_zone_code = g_seed_plan_zone_code OR seed_plan_zone_code IS NULL) AND g_collection_lat_deg BETWEEN site_min_latdeg AND site_max_latdeg ORDER BY vegetation_code NULLS LAST, seed_plan_zone_code NULLS LAST; r_limit c_limit%ROWTYPE; BEGIN IF g_genetic_class_code = 'B' AND g_collection_elevation_min IS NOT NULL AND g_collection_elevation_max IS NOT NULL THEN --Derive collection elevation range limits (sorted so first record is all we need look at) OPEN c_limit; FETCH c_limit INTO r_limit; CLOSE c_limit; IF r_limit.transfer_limit_skey IS NULL THEN RAISE e_no_limits_found; END IF; IF r_limit.max_collection_elevation_range IS NOT NULL THEN v_min_max_range := g_collection_elevation_max - g_collection_elevation_min; IF v_min_max_range > r_limit.max_collection_elevation_range THEN g_error_message := g_error_message || 'spar.web.error.usr.coll_elev_range:'||r_limit.max_collection_elevation_range||';'; END IF; END IF; END IF; EXCEPTION WHEN e_no_limits_found THEN NULL; END validate_colln_elev_min_max; /****************************************************************************** Procedure: validate (New Spr01 Registration validations) Purpose: Assumes mandatory validations are done in interface except for Collection Geography and Area of Use information. Performs cross-validations interface is not capable of. ******************************************************************************/ PROCEDURE validate (p_user_org_unit_no IN NUMBER) IS v_spz_list g_spz_list%TYPE; v_pos NUMBER(3); v_spz g_seed_plan_zone_code%TYPE; v_spz_count NUMBER; BEGIN get_previous_seedlot_values; IF g_seedlot_status_code = 'CAN' AND r_previous.seedlot_status_code != 'CAN' THEN validate_seedlot_cancel; ELSE validate_lot_number; validate_org(p_user_org_unit_no); --Collection geography information must be filled if from BC or B lot --(B- lot requires mean Area of Use geog (which may be copied from collection geog) -- for transfer guidelines) IF g_bc_source_ind = 'Y' OR g_genetic_class_code = 'B' THEN validate_mandatory_APP(TO_CHAR(g_collection_lat_deg),'Collection Mean Latitude'); validate_mandatory_APP(TO_CHAR(g_collection_long_deg),'Collection Mean Longitude'); validate_mandatory_APP(TO_CHAR(g_collection_elevation),'Collection Elevation Mean'); END IF; IF g_genetic_class_code = 'B' THEN validate_mandatory_APP(TO_CHAR(g_collection_elevation_min),'Collection Elevation Min'); validate_mandatory_APP(TO_CHAR(g_collection_elevation_max),'Collection Elevation Max'); --validate maximum range between min and max elev for B class validate_colln_elev_min_max; ELSIF g_genetic_Class_code = 'A' THEN validate_seedlot_source_code; END IF; --Collections Provenance (Species dependent edit) validate_superior_provenance; --Collection Geography edits based on Superior Provenance validate_b_sup_prov_geog; --Orchard (Species dependent edits) validate_orchard_for_spp(g_orchard_id,'Orchard'); --Secondary Orchard (Species dependent edits) validate_orchard_for_spp(g_secondary_orchard_id,'Secondary Orchard'); --Collection SPZ (Species dependent edits) validate_spz_spp(g_seed_plan_zone_code,'Collection SPZ'); --Area of Use information must be filled --validate_mandatory_APP(TO_CHAR(g_latitude_degrees),'Area of Use Mean Latitude'); validate_mandatory_APP(TO_CHAR(g_latitude_deg_min),'Area of Use Min Latitude'); validate_mandatory_APP(TO_CHAR(g_latitude_deg_max),'Area of Use Max Latitude'); --validate_mandatory_APP(TO_CHAR(g_longitude_degrees),'Area of Use Mean Longitude'); validate_mandatory_APP(TO_CHAR(g_longitude_deg_min),'Area of Use Min Longitude'); validate_mandatory_APP(TO_CHAR(g_longitude_deg_max),'Area of Use Max Longitude'); --validate_mandatory_APP(TO_CHAR(g_elevation),'Area of Use Elevation Mean'); validate_mandatory_APP(TO_CHAR(g_elevation_min),'Area of Use Elevation Min'); validate_mandatory_APP(TO_CHAR(g_elevation_max),'Area of Use Elevation Max'); validate_mandatory_APP(g_spz_list,'Area of Use SPZ'); --Area of Use SPZ's (Species dependent edit) v_spz_list := g_spz_list; WHILE TRIM(v_spz_list) IS NOT NULL LOOP v_pos := INSTR(v_spz_list,','); IF v_pos > 0 THEN v_spz := SUBSTR(v_spz_list,1,v_pos-1); v_spz_list := SUBSTR(v_spz_list,v_pos+1); ELSE v_spz := RTRIM(v_spz_list,','); v_spz_list := NULL; END IF; validate_spz_spp(v_spz,'Area of Use SPZ'); v_spz_count := v_spz_count+1; END LOOP; -- If superior provenance is N, only one SPZ is allowed IF g_superior_prvnc_ind = 'N' AND v_spz_count > 1 THEN g_error_message := g_error_message || 'spar.web.error.usr.database.provenanceNo_SPZ_Limit;'; END IF; validate_tested_untested_ratio; END IF; END validate; /****************************************************************************** Procedure: validate (OVERLOADED) Purpose: These are the validations from the original SPR01 Seedlot Maintenance page. ******************************************************************************/ PROCEDURE validate (p_spz1 IN OUT VARCHAR2) IS BEGIN get_previous_seedlot_values; IF g_seedlot_status_code = 'COM' THEN validate_genetic_class; validate_orchard_id; validate_collection_locn; validate_coast_geo(p_spz1); validate_mean_elevation; validate_mean_latitude; validate_min_latitude; validate_max_latitude; validate_mean_longitude; validate_min_longitude; validate_max_longitude; validate_bgc_zone; validate_collection_start; validate_collection_end; validate_vol_per_container; validate_nmbr_trees; validate_cone_collection; END IF; END validate; /****************************************************************************** Procedure: get_seedlot_spz_list Purpose: Returns a comma delimited list of Area of Use Seed Planning Zones for a Seedlot. Written as a function so that it may be used outside of this package. ******************************************************************************/ FUNCTION get_seedlot_spz_list (p_seedlot_number IN VARCHAR2) RETURN VARCHAR2 IS CURSOR c_spz IS SELECT seed_plan_zone_code FROM seedlot_plan_zone WHERE seedlot_number = p_seedlot_number ORDER BY DECODE(primary_ind,'Y',1,2) --sort primary_ind to top , seed_plan_zone_code; v_spz_list VARCHAR2(100); BEGIN --Get comma delim list of SPZ's FOR r_spz IN c_spz LOOP v_spz_list := v_spz_list || r_spz.seed_plan_zone_code || ','; END LOOP; v_spz_list := RTRIM(v_spz_list,','); RETURN v_spz_list; END get_seedlot_spz_list; /****************************************************************************** Procedure: is_lot_split Purpose: Returns TRUE of the lot has been split, otherwise returns FALSE. Currently, the only was to tell if a lot has been split is if the lot has an ASP request and has heritage. ******************************************************************************/ FUNCTION is_lot_split (p_seedlot_number IN VARCHAR2) RETURN BOOLEAN IS CURSOR c_asp IS SELECT COUNT(1) FROM request_seedlot rs , spar_request sr WHERE rs.seedlot_number = p_seedlot_number AND sr.request_skey = rs.request_skey AND sr.request_type_code = 'ASP'; CURSOR c_heritage IS SELECT COUNT(1) FROM seedlot_heritage WHERE parent_seedlot_no = p_seedlot_number OR child_seedlot_no = p_seedlot_number; b_is_lot_split BOOLEAN; v_count NUMBER(10); BEGIN b_is_lot_split := FALSE; --ASP request exists OPEN c_asp; FETCH c_asp INTO v_count; CLOSE c_asp; IF v_count > 0 THEN OPEN c_heritage; FETCH c_heritage INTO v_count; CLOSE c_heritage; b_is_lot_split := (v_count > 0); END IF; RETURN b_is_lot_split; END is_lot_split; /****************************************************************************** Procedure: get Purpose: Retrieve ONE Seedlot row and associated Genetic Worth and Seed Plan Zone information. Security: HQ users can access all; otherwise: If user is a client or BCTS, access is allowed by applicant_client_number = user client number If user is an org, access is allowed by applicant_client_number = org unit resolving to a client number ******************************************************************************/ PROCEDURE get ( p_user_client_number IN VARCHAR2 , p_user_org_unit_no IN NUMBER ) IS v_user_client_number g_applicant_client_number%TYPE; v_tested NUMBER(5); v_total NUMBER(5); BEGIN --If user is not a client, resolve org to its client number v_user_client_number := NVL(p_user_client_number,sil_get_org_cli_number(p_user_org_unit_no)); SELECT DISTINCT s.seedlot_number ,s.to_be_registrd_ind ,s.registered_seed_ind ,s.seedlot_status_code ,s.vegetation_code ,s.genetic_class_code ,s.superior_prvnc_ind ,s.seedlot_source_code ,s.bc_source_ind ,s.applicant_client_number ,s.applicant_client_locn ,s.applicant_email_address --collection information ,s.org_unit_no ,s.collection_locn_desc ,s.provenance_id ,s.collection_standard_met_ind ,gwg.genetic_worth_rtng gw_g ,gwd.genetic_worth_rtng gw_d ,gwr.genetic_worth_rtng gw_r ,gwm.genetic_worth_rtng gw_m ,s.collection_elevation ,s.collection_elevation_min ,s.collection_elevation_max ,s.collection_area_radius ,s.collection_lat_deg ,s.collection_lat_min ,s.collection_lat_sec ,s.collection_latitude_code ,s.collection_long_deg ,s.collection_long_min ,s.collection_long_sec ,s.collection_longitude_code ,s.seed_plan_zone_code ,s.collection_seed_plan_zone_ind collection_spz_ind ,s.seed_coast_area_code ,s.bgc_zone_code ,s.bgc_subzone_code ,s.variant ,s.bec_version_id ,s.collection_bgc_ind ,s.collection_start_date ,s.collection_end_date ,s.cone_collection_method_code ,s.cone_collection_method2_code ,s.nmbr_trees_from_code ,s.no_of_containers ,s.vol_per_container ,s.clctn_volume ,s.seedlot_comment ,s.collection_cli_number ,s.collection_cli_locn_cd --collection - CLASS A ,s.orchard_id ,s.coancestry ,s.effective_pop_size ,s.seed_plan_unit_id --Area of use ,s.elevation_min ,s.elevation_max ,s.latitude_deg_min ,s.latitude_min_min ,s.latitude_sec_min ,s.latitude_deg_max ,s.latitude_min_max ,s.latitude_sec_max ,s.longitude_deg_min ,s.longitude_min_min ,s.longitude_sec_min ,s.longitude_deg_max ,s.longitude_min_max ,s.longitude_sec_max --interim cone storage ,s.interm_strg_client_number ,s.interm_strg_client_locn ,s.interm_strg_st_date ,s.interm_strg_end_date ,s.interm_facility_code ,s.interm_strg_locn --extraction ,s.extrct_cli_number ,s.extrct_cli_locn_cd ,s.extraction_st_date ,s.extraction_end_date ,s.original_seed_qty --temporary seed storage ,s.temporary_storage_end_date ,s.temporary_storage_start_date ,s.seed_store_client_number ,s.seed_store_client_locn --parent tree contribution ,s.secondary_orchard_id ,s.orchard_comment ,s.smp_mean_bv_growth ,s.smp_success_pct ,s.female_gametic_mthd_code ,s.male_gametic_mthd_code ,s.pollen_contamination_ind ,s.pollen_contamination_pct ,s.controlled_cross_ind ,s.biotech_processes_ind ,s.contaminant_pollen_bv ,s.pollen_contamination_mthd_code ,s.orchard_contamination_pct ,s.total_parent_trees ,s.smp_parents_outside --declaration ,s.declared_userid ,s.declared_timestamp ,s.update_userid ,s.update_timestamp ,s.entry_userid ,s.entry_timestamp ,s.approved_userid ,s.approved_timestamp --record info ,s.revision_count INTO g_seedlot_number ,g_to_be_registrd_ind ,g_registered_seed_ind ,g_seedlot_status_code ,g_vegetation_code ,g_genetic_class_code ,g_superior_prvnc_ind ,g_seedlot_source_code ,g_bc_source_ind ,g_applicant_client_number ,g_applicant_client_locn ,g_applicant_email_address --collection information ,g_org_unit_no ,g_collection_locn_desc ,g_provenance_id ,g_coll_standard_met_ind ,g_gw_g ,g_gw_d ,g_gw_r ,g_gw_m ,g_collection_elevation ,g_collection_elevation_min ,g_collection_elevation_max ,g_collection_area_radius ,g_collection_lat_deg ,g_collection_lat_min ,g_collection_lat_sec ,g_collection_latitude_code ,g_collection_long_deg ,g_collection_long_min ,g_collection_long_sec ,g_collection_longitude_code ,g_seed_plan_zone_code ,g_collection_spz_ind ,g_seed_coast_area_code ,g_bgc_zone_code ,g_bgc_subzone_code ,g_variant ,g_bec_version_id ,g_collection_bgc_ind ,g_collection_start_date ,g_collection_end_date ,g_cone_collection_method_cd ,g_cone_collection_method2_cd ,g_nmbr_trees_from_code ,g_no_of_containers ,g_vol_per_container ,g_clctn_volume ,g_seedlot_comment ,g_collection_cli_number ,g_collection_cli_locn_cd --collection - CLASS A ,g_orchard_id ,g_coancestry ,g_effective_pop_size ,g_seed_plan_unit_id --Area of use ,g_elevation_min ,g_elevation_max ,g_latitude_deg_min ,g_latitude_min_min ,g_latitude_sec_min ,g_latitude_deg_max ,g_latitude_min_max ,g_latitude_sec_max ,g_longitude_deg_min ,g_longitude_min_min ,g_longitude_sec_min ,g_longitude_deg_max ,g_longitude_min_max ,g_longitude_sec_max --interim cone storage ,g_interm_strg_client_number ,g_interm_strg_client_locn ,g_interm_strg_st_date ,g_interm_strg_end_date ,g_interm_facility_code ,g_interm_strg_locn --extraction ,g_extrct_cli_number ,g_extrct_cli_locn_cd ,g_extraction_st_date ,g_extraction_end_date ,g_original_seed_qty --temporary seed storage ,g_temporary_storage_end_date ,g_temporary_storage_start_dt ,g_seed_store_client_number ,g_seed_store_client_locn --parent tree contribution ,g_secondary_orchard_id ,g_orchard_comment ,g_smp_mean_bv_growth ,g_smp_success_pct ,g_female_gametic_mthd_code ,g_male_gametic_mthd_code ,g_pollen_contamination_ind ,g_pollen_contamination_pct ,g_controlled_cross_ind ,g_biotech_processes_ind ,g_contaminant_pollen_bv ,g_pollen_contam_mthd_code ,g_orchard_contamination_pct ,g_total_parent_trees ,g_smp_parents_outside --declaration ,g_declared_userid ,g_declared_timestamp ,g_update_userid ,g_update_timestamp ,g_entry_userid ,g_entry_timestamp ,g_approved_userid ,g_approved_timestamp --record info ,g_revision_count FROM seedlot s , seedlot_genetic_worth gwg , seedlot_genetic_worth gwd , seedlot_genetic_worth gwr , seedlot_genetic_worth gwm WHERE s.seedlot_number = g_seedlot_number AND (s.applicant_client_number = v_user_client_number OR s.applicant_client_number IN (select client_number from org_client_xref where client_number = v_user_client_number or org_unit_no = p_user_org_unit_no) OR s.seedlot_number IN (select seedlot_number from seedlot_owner_quantity where seedlot_number = g_seedlot_number and client_number = v_user_client_number) OR sil_get_org_level(p_user_org_unit_no) = 'H') AND gwg.seedlot_number(+) = s.seedlot_number AND gwg.genetic_worth_code(+) = 'G' AND gwd.seedlot_number(+) = s.seedlot_number AND gwd.genetic_worth_code(+) = 'D' AND gwr.seedlot_number(+) = s.seedlot_number AND gwr.genetic_worth_code(+) = 'R' AND gwm.seedlot_number(+) = s.seedlot_number AND gwm.genetic_worth_code(+) = 'M'; set_spz_list(get_seedlot_spz_list(g_seedlot_number)); g_is_lot_split := is_lot_split(g_seedlot_number); EXCEPTION WHEN NO_DATA_FOUND THEN NULL; END get; /****************************************************************************** Procedure: set_status_defaults Purpose: Set defaults that depend on Status ******************************************************************************/ PROCEDURE set_status_defaults IS BEGIN --Did we already get previous data? IF g_revision_count IS NOT NULL AND r_previous.seedlot_number IS NULL THEN get_previous_seedlot_values; END IF; --If approving, set approved userid/timestamp IF g_seedlot_status_code = 'APP' AND NVL(r_previous.seedlot_status_code,'~') != 'APP' AND r_previous.approved_userid IS NULL THEN set_approved_userid(g_update_userid); set_approved_timestamp(SYSDATE); --In case skipped SUB status, set declaration data to approver IF g_update_userid IS NULL THEN set_declared_userid(g_update_userid); set_declared_timestamp(SYSDATE); END IF; END IF; --If submitting, set declared userid/timestamp IF g_seedlot_status_code = 'SUB' AND NVL(r_previous.seedlot_status_code,'~') != 'SUB' AND r_previous.declared_userid IS NULL THEN set_declared_userid(g_update_userid); set_declared_timestamp(SYSDATE); END IF; END set_status_defaults; /****************************************************************************** Procedure: add Purpose: INSERT one row into SEEDLOT ******************************************************************************/ PROCEDURE add IS v_seedlot_number NUMBER(6); e_error_generating_lot_number EXCEPTION; BEGIN --Generate new number if none provided IF g_seedlot_number IS NULL THEN IF g_genetic_class_code = 'B' THEN SELECT MAX(TO_NUMBER(seedlot_number)) + 1 INTO v_seedlot_number FROM seedlot WHERE seedlot_number BETWEEN CONST_CLASS_B_LOTNUM_MIN AND TO_CHAR(TO_NUMBER(CONST_CLASS_B_LOTNUM_MAX)-1 ); IF v_seedlot_number >= CONST_CLASS_B_LOTNUM_MAX THEN RAISE e_error_generating_lot_number; ELSIF v_seedlot_number IS NULL THEN g_seedlot_number := TO_CHAR(TO_NUMBER(CONST_CLASS_B_LOTNUM_MIN) + 1); ELSE g_seedlot_number := TO_CHAR(v_seedlot_number); END IF; ELSIF g_genetic_class_code = 'A' THEN SELECT MAX(TO_NUMBER(seedlot_number)) + 1 INTO v_seedlot_number FROM seedlot WHERE seedlot_number BETWEEN CONST_CLASS_A_LOTNUM_MIN AND TO_CHAR(TO_NUMBER(CONST_CLASS_A_LOTNUM_MAX)-1 ); IF v_seedlot_number >= CONST_CLASS_A_LOTNUM_MAX THEN RAISE e_error_generating_lot_number; ELSIF v_seedlot_number IS NULL THEN g_seedlot_number := TO_CHAR(TO_NUMBER(CONST_CLASS_A_LOTNUM_MIN) + 1); ELSE g_seedlot_number := TO_CHAR(v_seedlot_number); END IF; END IF; END IF; --Defaults for a new seedlot, taken from old SPR01 package set_stored_cli_number('00012797'); set_stored_cli_locn_cd('00'); set_original_seed_qty(0); set_extraction_volume(NULL); set_utm_easting(0); set_utm_northing(0); set_utm_zone(0); set_registered_seed_ind('N'); --Set Mean Area of Use geography to Mean Collection geography. --Should already have been done in recalc() but IMPORTANT, thus insurance. set_mean_area_of_use_geography; --Set items that depend on status set_status_defaults; INSERT INTO seedlot ( seedlot_number ,seedlot_status_code ,vegetation_code ,genetic_class_code ,collection_source_code ,superior_prvnc_ind ,org_unit_no ,registered_seed_ind ,to_be_registrd_ind ,registered_date ,fs721a_signed_ind ,nad_datum_code ,utm_zone ,utm_easting ,utm_northing ,longitude_degrees ,longitude_minutes ,longitude_deg_min ,longitude_min_min ,longitude_deg_max ,longitude_min_max ,latitude_degrees ,latitude_minutes ,latitude_deg_min ,latitude_min_min ,latitude_deg_max ,latitude_min_max ,seed_coast_area_code ,elevation ,elevation_min ,elevation_max ,orchard_id ,collection_locn_desc ,collection_cli_number ,collection_cli_locn_cd ,collection_start_date ,collection_end_date ,cone_collection_method_code ,no_of_containers ,clctn_volume ,vol_per_container ,nmbr_trees_from_code ,coancestry ,effective_pop_size ,original_seed_qty ,interm_strg_st_date ,interm_strg_end_date ,interm_facility_code ,extraction_st_date ,extraction_end_date ,extraction_volume ,extrct_cli_number ,extrct_cli_locn_cd ,stored_cli_number ,stored_cli_locn_cd ,lngterm_strg_st_date ,historical_tsr_date ,collection_lat_deg ,collection_lat_min ,collection_latitude_code ,collection_long_deg ,collection_long_min ,collection_longitude_code ,collection_elevation ,collection_elevation_min ,collection_elevation_max ,entry_timestamp ,entry_userid ,update_timestamp ,update_userid ,approved_timestamp ,approved_userid ,revision_count ,interm_strg_locn ,interm_strg_cmt ,ownership_comment ,cone_seed_desc ,extraction_comment ,seedlot_comment ,bgc_zone_code ,bgc_subzone_code ,variant ,bec_version_id ,collection_lat_sec ,collection_long_sec ,latitude_seconds ,longitude_seconds ,seed_plan_zone_code ,applicant_client_locn ,applicant_client_number ,applicant_email_address ,bc_source_ind ,biotech_processes_ind ,collection_area_radius ,collection_bgc_ind ,collection_seed_plan_zone_ind ,collection_standard_met_ind ,cone_collection_method2_code ,contaminant_pollen_bv ,controlled_cross_ind ,declared_userid ,declared_timestamp ,female_gametic_mthd_code ,latitude_sec_max ,latitude_sec_min ,longitude_sec_max ,longitude_sec_min ,male_gametic_mthd_code ,orchard_comment ,orchard_contamination_pct ,pollen_contamination_ind ,pollen_contamination_mthd_code ,pollen_contamination_pct ,provenance_id ,secondary_orchard_id ,seed_plan_unit_id ,seed_store_client_locn ,seed_store_client_number ,seedlot_source_code ,smp_mean_bv_growth ,smp_parents_outside ,smp_success_pct ,temporary_storage_end_date ,temporary_storage_start_date ,total_parent_trees ,interm_strg_client_number ,interm_strg_client_locn) VALUES ( g_seedlot_number ,g_seedlot_status_code ,g_vegetation_code ,g_genetic_class_code ,g_collection_source_code ,g_superior_prvnc_ind ,g_org_unit_no ,g_registered_seed_ind ,g_to_be_registrd_ind ,g_registered_date ,g_fs721a_signed_ind ,g_nad_datum_code ,g_utm_zone ,g_utm_easting ,g_utm_northing ,g_longitude_degrees ,g_longitude_minutes ,g_longitude_deg_min ,g_longitude_min_min ,g_longitude_deg_max ,g_longitude_min_max ,g_latitude_degrees ,g_latitude_minutes ,g_latitude_deg_min ,g_latitude_min_min ,g_latitude_deg_max ,g_latitude_min_max ,g_seed_coast_area_code ,g_elevation ,g_elevation_min ,g_elevation_max ,g_orchard_id ,g_collection_locn_desc ,g_collection_cli_number ,g_collection_cli_locn_cd ,g_collection_start_date ,g_collection_end_date ,g_cone_collection_method_cd ,g_no_of_containers ,g_clctn_volume ,g_vol_per_container ,g_nmbr_trees_from_code ,g_coancestry ,g_effective_pop_size ,g_original_seed_qty ,g_interm_strg_st_date ,g_interm_strg_end_date ,g_interm_facility_code ,g_extraction_st_date ,g_extraction_end_date ,g_extraction_volume ,g_extrct_cli_number ,g_extrct_cli_locn_cd ,g_stored_cli_number ,g_stored_cli_locn_cd ,g_lngterm_strg_st_date ,g_historical_tsr_date ,g_collection_lat_deg ,g_collection_lat_min ,g_collection_latitude_code ,g_collection_long_deg ,g_collection_long_min ,g_collection_longitude_code ,g_collection_elevation ,g_collection_elevation_min ,g_collection_elevation_max ,g_entry_timestamp ,g_entry_userid ,g_update_timestamp ,g_update_userid ,g_approved_timestamp ,g_approved_userid ,g_revision_count ,g_interm_strg_locn ,g_interm_strg_cmt ,g_ownership_comment ,g_cone_seed_desc ,g_extraction_comment ,g_seedlot_comment ,g_bgc_zone_code ,g_bgc_subzone_code ,g_variant ,g_bec_version_id ,g_collection_lat_sec ,g_collection_long_sec ,g_latitude_seconds ,g_longitude_seconds ,g_seed_plan_zone_code ,g_applicant_client_locn ,g_applicant_client_number ,g_applicant_email_address ,g_bc_source_ind ,g_biotech_processes_ind ,g_collection_area_radius ,g_collection_bgc_ind ,g_collection_spz_ind ,g_coll_standard_met_ind ,g_cone_collection_method2_cd ,g_contaminant_pollen_bv ,g_controlled_cross_ind ,g_declared_userid ,g_declared_timestamp ,g_female_gametic_mthd_code ,g_latitude_sec_max ,g_latitude_sec_min ,g_longitude_sec_max ,g_longitude_sec_min ,g_male_gametic_mthd_code ,g_orchard_comment ,g_orchard_contamination_pct ,g_pollen_contamination_ind ,g_pollen_contam_mthd_code ,g_pollen_contamination_pct ,g_provenance_id ,g_secondary_orchard_id ,g_seed_plan_unit_id ,g_seed_store_client_locn ,g_seed_store_client_number ,g_seedlot_source_code ,g_smp_mean_bv_growth ,g_smp_parents_outside ,g_smp_success_pct ,g_temporary_storage_end_date ,g_temporary_storage_start_dt ,g_total_parent_trees ,g_interm_strg_client_number ,g_interm_strg_client_locn); EXCEPTION WHEN e_error_generating_lot_number THEN g_error_message := g_error_message || 'spar.web.error.usr.gen_lot_number;'; END add; /****************************************************************************** Procedure: change Purpose: UPDATE one SEEDLOT row ******************************************************************************/ PROCEDURE change IS BEGIN IF g_seedlot_status_code != 'CAN' THEN --Set Mean Area of Use geography to Mean Collection geography. --Should already have been done in recalc() but IMPORTANT, thus insurance. set_mean_area_of_use_geography; --Set items that depend on status set_status_defaults; END IF; UPDATE seedlot SET seedlot_status_code = DECODE(gb_seedlot_status_code,'Y',g_seedlot_status_code,seedlot_status_code) ,vegetation_code = DECODE(gb_vegetation_code,'Y',g_vegetation_code,vegetation_code) ,genetic_class_code = DECODE(gb_genetic_class_code,'Y',g_genetic_class_code,genetic_class_code) ,collection_source_code = DECODE(gb_collection_source_code,'Y',g_collection_source_code,collection_source_code) ,superior_prvnc_ind = DECODE(gb_superior_prvnc_ind,'Y',g_superior_prvnc_ind,superior_prvnc_ind) ,org_unit_no = DECODE(gb_org_unit_no,'Y',g_org_unit_no,org_unit_no) ,registered_seed_ind = DECODE(gb_registered_seed_ind,'Y',g_registered_seed_ind,registered_seed_ind) ,to_be_registrd_ind = DECODE(gb_to_be_registrd_ind,'Y',g_to_be_registrd_ind,to_be_registrd_ind) ,registered_date = DECODE(gb_registered_date,'Y',g_registered_date,registered_date) ,fs721a_signed_ind = DECODE(gb_fs721a_signed_ind,'Y',g_fs721a_signed_ind,fs721a_signed_ind) ,nad_datum_code = DECODE(gb_nad_datum_code,'Y',g_nad_datum_code,nad_datum_code) ,utm_zone = DECODE(gb_utm_zone,'Y',g_utm_zone,utm_zone) ,utm_easting = DECODE(gb_utm_easting,'Y',g_utm_easting,utm_easting) ,utm_northing = DECODE(gb_utm_northing,'Y',g_utm_northing,utm_northing) ,longitude_degrees = DECODE(gb_longitude_degrees,'Y',g_longitude_degrees,longitude_degrees) ,longitude_minutes = DECODE(gb_longitude_minutes,'Y',g_longitude_minutes,longitude_minutes) ,longitude_deg_min = DECODE(gb_longitude_deg_min,'Y',g_longitude_deg_min,longitude_deg_min) ,longitude_min_min = DECODE(gb_longitude_min_min,'Y',g_longitude_min_min,longitude_min_min) ,longitude_deg_max = DECODE(gb_longitude_deg_max,'Y',g_longitude_deg_max,longitude_deg_max) ,longitude_min_max = DECODE(gb_longitude_min_max,'Y',g_longitude_min_max,longitude_min_max) ,latitude_degrees = DECODE(gb_latitude_degrees,'Y',g_latitude_degrees,latitude_degrees) ,latitude_minutes = DECODE(gb_latitude_minutes,'Y',g_latitude_minutes,latitude_minutes) ,latitude_deg_min = DECODE(gb_latitude_deg_min,'Y',g_latitude_deg_min,latitude_deg_min) ,latitude_min_min = DECODE(gb_latitude_min_min,'Y',g_latitude_min_min,latitude_min_min) ,latitude_deg_max = DECODE(gb_latitude_deg_max,'Y',g_latitude_deg_max,latitude_deg_max) ,latitude_min_max = DECODE(gb_latitude_min_max,'Y',g_latitude_min_max,latitude_min_max) ,seed_coast_area_code = DECODE(gb_seed_coast_area_code,'Y',g_seed_coast_area_code,seed_coast_area_code) ,elevation = DECODE(gb_elevation,'Y',g_elevation,elevation) ,elevation_min = DECODE(gb_elevation_min,'Y',g_elevation_min,elevation_min) ,elevation_max = DECODE(gb_elevation_max,'Y',g_elevation_max,elevation_max) ,orchard_id = DECODE(gb_orchard_id,'Y',g_orchard_id,orchard_id) ,collection_locn_desc = DECODE(gb_collection_locn_desc,'Y',g_collection_locn_desc,collection_locn_desc) ,collection_cli_number = DECODE(gb_collection_cli_number,'Y',g_collection_cli_number,collection_cli_number) ,collection_cli_locn_cd = DECODE(gb_collection_cli_locn_cd,'Y',g_collection_cli_locn_cd,collection_cli_locn_cd) ,collection_start_date = DECODE(gb_collection_start_date,'Y',g_collection_start_date,collection_start_date) ,collection_end_date = DECODE(gb_collection_end_date,'Y',g_collection_end_date,collection_end_date) ,cone_collection_method_code = DECODE(gb_cone_collection_method_cd,'Y',g_cone_collection_method_cd,cone_collection_method_code) ,no_of_containers = DECODE(gb_no_of_containers,'Y',g_no_of_containers,no_of_containers) ,clctn_volume = DECODE(gb_clctn_volume,'Y',g_clctn_volume,clctn_volume) ,vol_per_container = DECODE(gb_vol_per_container,'Y',g_vol_per_container,vol_per_container) ,nmbr_trees_from_code = DECODE(gb_nmbr_trees_from_code,'Y',g_nmbr_trees_from_code,nmbr_trees_from_code) ,coancestry = DECODE(gb_coancestry,'Y',g_coancestry,coancestry) ,effective_pop_size = DECODE(gb_effective_pop_size,'Y',g_effective_pop_size,effective_pop_size) ,original_seed_qty = DECODE(gb_original_seed_qty,'Y',g_original_seed_qty,original_seed_qty) ,interm_strg_client_number = DECODE(gb_interm_strg_client_number,'Y',g_interm_strg_client_number,interm_strg_client_number) ,interm_strg_client_locn = DECODE(gb_interm_strg_client_locn,'Y',g_interm_strg_client_locn,interm_strg_client_locn) ,interm_strg_st_date = DECODE(gb_interm_strg_st_date,'Y',g_interm_strg_st_date,interm_strg_st_date) ,interm_strg_end_date = DECODE(gb_interm_strg_end_date,'Y',g_interm_strg_end_date,interm_strg_end_date) ,interm_facility_code = DECODE(gb_interm_facility_code,'Y',g_interm_facility_code,interm_facility_code) ,extraction_st_date = DECODE(gb_extraction_st_date,'Y',g_extraction_st_date,extraction_st_date) ,extraction_end_date = DECODE(gb_extraction_end_date,'Y',g_extraction_end_date,extraction_end_date) ,extraction_volume = DECODE(gb_extraction_volume,'Y',g_extraction_volume,extraction_volume) ,extrct_cli_number = DECODE(gb_extrct_cli_number,'Y',g_extrct_cli_number,extrct_cli_number) ,extrct_cli_locn_cd = DECODE(gb_extrct_cli_locn_cd,'Y',g_extrct_cli_locn_cd,extrct_cli_locn_cd) ,stored_cli_number = DECODE(gb_stored_cli_number,'Y',g_stored_cli_number,stored_cli_number) ,stored_cli_locn_cd = DECODE(gb_stored_cli_locn_cd,'Y',g_stored_cli_locn_cd,stored_cli_locn_cd) ,lngterm_strg_st_date = DECODE(gb_lngterm_strg_st_date,'Y',g_lngterm_strg_st_date,lngterm_strg_st_date) ,historical_tsr_date = DECODE(gb_historical_tsr_date,'Y',g_historical_tsr_date,historical_tsr_date) ,collection_lat_deg = DECODE(gb_collection_lat_deg,'Y',g_collection_lat_deg,collection_lat_deg) ,collection_lat_min = DECODE(gb_collection_lat_min,'Y',g_collection_lat_min,collection_lat_min) ,collection_latitude_code = DECODE(gb_collection_latitude_code,'Y',g_collection_latitude_code,collection_latitude_code) ,collection_long_deg = DECODE(gb_collection_long_deg,'Y',g_collection_long_deg,collection_long_deg) ,collection_long_min = DECODE(gb_collection_long_min,'Y',g_collection_long_min,collection_long_min) ,collection_longitude_code = DECODE(gb_collection_longitude_code,'Y',g_collection_longitude_code,collection_longitude_code) ,collection_elevation = DECODE(gb_collection_elevation,'Y',g_collection_elevation,collection_elevation) ,collection_elevation_min = DECODE(gb_collection_elevation_min,'Y',g_collection_elevation_min,collection_elevation_min) ,collection_elevation_max = DECODE(gb_collection_elevation_max,'Y',g_collection_elevation_max,collection_elevation_max) ,approved_timestamp = DECODE(gb_approved_timestamp,'Y',g_approved_timestamp,approved_timestamp) ,approved_userid = DECODE(gb_approved_userid,'Y',g_approved_userid,approved_userid) ,interm_strg_locn = DECODE(gb_interm_strg_locn,'Y',g_interm_strg_locn,interm_strg_locn) ,interm_strg_cmt = DECODE(gb_interm_strg_cmt,'Y',g_interm_strg_cmt,interm_strg_cmt) ,ownership_comment = DECODE(gb_ownership_comment,'Y',g_ownership_comment,ownership_comment) ,cone_seed_desc = DECODE(gb_cone_seed_desc,'Y',g_cone_seed_desc,cone_seed_desc) ,extraction_comment = DECODE(gb_extraction_comment,'Y',g_extraction_comment,extraction_comment) ,seedlot_comment = DECODE(gb_seedlot_comment,'Y',g_seedlot_comment,seedlot_comment) ,bgc_zone_code = DECODE(gb_bgc_zone_code,'Y',g_bgc_zone_code,bgc_zone_code) ,bgc_subzone_code = DECODE(gb_bgc_subzone_code,'Y',g_bgc_subzone_code,bgc_subzone_code) ,variant = DECODE(gb_variant,'Y',g_variant,variant) ,bec_version_id = DECODE(gb_bec_version_id,'Y',g_bec_version_id,bec_version_id) ,collection_lat_sec = DECODE(gb_collection_lat_sec,'Y',g_collection_lat_sec,collection_lat_sec) ,collection_long_sec = DECODE(gb_collection_long_sec,'Y',g_collection_long_sec,collection_long_sec) ,latitude_seconds = DECODE(gb_latitude_seconds,'Y',g_latitude_seconds,latitude_seconds) ,longitude_seconds = DECODE(gb_longitude_seconds,'Y',g_longitude_seconds,longitude_seconds) ,seed_plan_zone_code = DECODE(gb_seed_plan_zone_code,'Y',g_seed_plan_zone_code,seed_plan_zone_code) ,applicant_client_locn = DECODE(gb_applicant_client_locn,'Y',g_applicant_client_locn,applicant_client_locn) ,applicant_client_number = DECODE(gb_applicant_client_number,'Y',g_applicant_client_number,applicant_client_number) ,applicant_email_address = DECODE(gb_applicant_email_address,'Y',g_applicant_email_address,applicant_email_address) ,bc_source_ind = DECODE(gb_bc_source_ind,'Y',g_bc_source_ind,bc_source_ind) ,biotech_processes_ind = DECODE(gb_biotech_processes_ind,'Y',g_biotech_processes_ind,biotech_processes_ind) ,collection_area_radius = DECODE(gb_collection_area_radius,'Y',g_collection_area_radius,collection_area_radius) ,collection_bgc_ind = DECODE(gb_collection_bgc_ind,'Y',g_collection_bgc_ind,collection_bgc_ind) ,collection_seed_plan_zone_ind = DECODE(gb_collection_spz_ind,'Y',g_collection_spz_ind,collection_seed_plan_zone_ind) ,collection_standard_met_ind = DECODE(gb_coll_standard_met_ind,'Y',g_coll_standard_met_ind,collection_standard_met_ind) ,cone_collection_method2_code = DECODE(gb_cone_collection_method2_cd,'Y',g_cone_collection_method2_cd,cone_collection_method2_code) ,contaminant_pollen_bv = DECODE(gb_contaminant_pollen_bv,'Y',g_contaminant_pollen_bv,contaminant_pollen_bv) ,controlled_cross_ind = DECODE(gb_controlled_cross_ind,'Y',g_controlled_cross_ind,controlled_cross_ind) ,declared_userid = DECODE(gb_declared_userid,'Y',g_declared_userid,declared_userid) ,declared_timestamp = DECODE(gb_declared_timestamp,'Y',g_declared_timestamp,declared_timestamp) ,female_gametic_mthd_code = DECODE(gb_female_gametic_mthd_code,'Y',g_female_gametic_mthd_code,female_gametic_mthd_code) ,latitude_sec_max = DECODE(gb_latitude_sec_max,'Y',g_latitude_sec_max,latitude_sec_max) ,latitude_sec_min = DECODE(gb_latitude_sec_min,'Y',g_latitude_sec_min,latitude_sec_min) ,longitude_sec_max = DECODE(gb_longitude_sec_max,'Y',g_longitude_sec_max,longitude_sec_max) ,longitude_sec_min = DECODE(gb_longitude_sec_min,'Y',g_longitude_sec_min,longitude_sec_min) ,male_gametic_mthd_code = DECODE(gb_male_gametic_mthd_code,'Y',g_male_gametic_mthd_code,male_gametic_mthd_code) ,orchard_comment = DECODE(gb_orchard_comment,'Y',g_orchard_comment,orchard_comment) ,orchard_contamination_pct = DECODE(gb_orchard_contamination_pct,'Y',g_orchard_contamination_pct,orchard_contamination_pct) ,pollen_contamination_ind = DECODE(gb_pollen_contamination_ind,'Y',g_pollen_contamination_ind,pollen_contamination_ind) ,pollen_contamination_mthd_code = DECODE(gb_pollen_contam_mthd_code,'Y',g_pollen_contam_mthd_code,pollen_contamination_mthd_code) ,pollen_contamination_pct = DECODE(gb_pollen_contamination_pct,'Y',g_pollen_contamination_pct,pollen_contamination_pct) ,provenance_id = DECODE(gb_provenance_id,'Y',g_provenance_id,provenance_id) ,secondary_orchard_id = DECODE(gb_secondary_orchard_id,'Y',g_secondary_orchard_id,secondary_orchard_id) ,seed_plan_unit_id = DECODE(gb_seed_plan_unit_id,'Y',g_seed_plan_unit_id,seed_plan_unit_id) ,seed_store_client_locn = DECODE(gb_seed_store_client_locn,'Y',g_seed_store_client_locn,seed_store_client_locn) ,seed_store_client_number = DECODE(gb_seed_store_client_number,'Y',g_seed_store_client_number,seed_store_client_number) ,seedlot_source_code = DECODE(gb_seedlot_source_code,'Y',g_seedlot_source_code,seedlot_source_code) ,smp_mean_bv_growth = DECODE(gb_smp_mean_bv_growth,'Y',g_smp_mean_bv_growth,smp_mean_bv_growth) ,smp_parents_outside = DECODE(gb_smp_parents_outside,'Y',g_smp_parents_outside,smp_parents_outside) ,smp_success_pct = DECODE(gb_smp_success_pct,'Y',g_smp_success_pct,smp_success_pct) ,temporary_storage_end_date = DECODE(gb_temporary_storage_end_date,'Y',g_temporary_storage_end_date,temporary_storage_end_date) ,temporary_storage_start_date = DECODE(gb_temporary_storage_start_dt,'Y',g_temporary_storage_start_dt,temporary_storage_start_date) ,total_parent_trees = DECODE(gb_total_parent_trees,'Y',g_total_parent_trees,total_parent_trees) ,update_userid = g_update_userid ,update_timestamp = SYSDATE ,revision_count = revision_count + 1 WHERE seedlot_number = g_seedlot_number AND revision_count = g_revision_count RETURNING revision_count, update_timestamp INTO g_revision_count, g_update_timestamp; IF SQL%ROWCOUNT != 1 THEN g_error_message := g_error_message || 'spar.web.error.usr.database.record.modified:Seedlot;'; END IF; END change; /****************************************************************************** Procedure: remove Purpose: DELETE one row from SEEDLOT ******************************************************************************/ PROCEDURE remove IS BEGIN DELETE FROM seedlot WHERE seedlot_number = g_seedlot_number AND revision_count = g_revision_count; END remove; /****************************************************************************** Procedure: validate_new_copy_lot_number Purpose: Validate the entered seedlot number to be used for the copied seedlot, to make sure no errors. ******************************************************************************/ PROCEDURE validate_new_copy_lot_number ( p_new_copy_seedlot_number IN VARCHAR2) IS v_count NUMBER := 0; b_valid BOOLEAN := TRUE; BEGIN -- Check if the lot number already exists and not INC status. SELECT COUNT('x') INTO v_count FROM seedlot WHERE seedlot_number = p_new_copy_seedlot_number AND seedlot_status_code <> 'INC' OR p_new_copy_seedlot_number = g_seedlot_number; IF v_count > 0 THEN g_error_message := g_error_message || 'spar.web.error.spr01.usr.seedlot.copy.overwrite:' || 'INC;'; END IF; -- Make sure entered lot number is a valid one. --Check ranges (for pre-numbered FS721 and FS721A forms already issued) IF g_genetic_class_code = 'A' THEN IF TO_NUMBER(p_new_copy_seedlot_number) NOT BETWEEN TO_NUMBER(CONST_CLASS_B_LOTNUM_MAX)+1 AND TO_NUMBER(CONST_CLASS_A_LOTNUM_MAX) THEN g_error_message := g_error_message || 'spar.web.error.usr.new_lot_number:' || g_genetic_class_code ||',' || TO_CHAR(TO_NUMBER(CONST_CLASS_B_LOTNUM_MAX)+1)||',' || TO_CHAR(TO_NUMBER(CONST_CLASS_A_LOTNUM_MAX))||';'; END IF; ELSIF g_genetic_class_code = 'B' THEN IF TO_NUMBER(p_new_copy_seedlot_number) > TO_NUMBER(CONST_CLASS_B_LOTNUM_MAX) THEN g_error_message := g_error_message || 'spar.web.error.usr.new_lot_number:' || g_genetic_class_code ||',00001,' || TO_CHAR(TO_NUMBER(CONST_CLASS_B_LOTNUM_MAX))||';'; END IF; END IF; /* PT#43998-remove limit of lot needing to be in 52xxx and 62xxx ranges. IF g_genetic_class_code = 'A' THEN -- A lots must be in 62xxx range. IF p_new_copy_seedlot_number NOT BETWEEN CONST_CLASS_A_COPY_MIN AND CONST_CLASS_A_COPY_MAX THEN g_error_message := g_error_message || 'spar.web.error.usr.new_lot_number:' || g_genetic_class_code ||',' || CONST_CLASS_A_COPY_MIN ||',' || CONST_CLASS_A_COPY_MAX ||';'; END IF; ELSIF g_genetic_class_code = 'B' THEN -- B lots must be in52xxx range. IF p_new_copy_seedlot_number NOT BETWEEN CONST_CLASS_B_COPY_MIN AND CONST_CLASS_B_COPY_MAX THEN g_error_message := g_error_message || 'spar.web.error.usr.new_lot_number:' || g_genetic_class_code ||',' || CONST_CLASS_B_COPY_MIN ||',' || CONST_CLASS_B_COPY_MAX ||';'; END IF; END IF; */ END validate_new_copy_lot_number; /****************************************************************************** Procedure: copy_seedlot Purpose: DELETE one row from SEEDLOT ******************************************************************************/ PROCEDURE copy_seedlot ( p_new_copy_seedlot_number IN VARCHAR2 ,p_userid IN VARCHAR2) IS v_seedlot_number NUMBER; v_new_seedlot_number seedlot.seedlot_number%TYPE; v_seedlot_comment seedlot.seedlot_comment%TYPE; v_count NUMBER; e_error_generating_lot_number EXCEPTION; BEGIN IF p_new_copy_seedlot_number IS NULL THEN --Generate new number for copied seedlot. 52xxx for B lots, 62xxx for A lots. IF g_genetic_class_code = 'B' THEN SELECT MAX(TO_NUMBER(seedlot_number)) + 1 INTO v_seedlot_number FROM seedlot WHERE seedlot_number BETWEEN CONST_CLASS_B_COPY_MIN AND CONST_CLASS_B_COPY_MAX; IF v_seedlot_number IS NULL OR v_seedlot_number >= CONST_CLASS_B_COPY_MAX THEN RAISE e_error_generating_lot_number; END IF; ELSIF g_genetic_class_code = 'A' THEN SELECT MAX(TO_NUMBER(seedlot_number)) + 1 INTO v_seedlot_number FROM seedlot WHERE seedlot_number BETWEEN CONST_CLASS_A_COPY_MIN AND CONST_CLASS_A_COPY_MAX; IF v_seedlot_number IS NULL OR v_seedlot_number >= CONST_CLASS_A_COPY_MAX THEN RAISE e_error_generating_lot_number; END IF; END IF; v_new_seedlot_number := v_seedlot_number; ELSE validate_new_copy_lot_number(p_new_copy_seedlot_number); v_new_seedlot_number := p_new_copy_seedlot_number; END IF; IF g_error_message IS NULL THEN SELECT 'COPIED FROM LOT ' || g_seedlot_number || '. ' || SUBSTR(seedlot_comment, 1, 1950) INTO v_seedlot_comment FROM seedlot WHERE seedlot_number = g_seedlot_number; -- if lot already exists, delete its child records, and overwrite seedlot record. SELECT COUNT('x') INTO v_count FROM seedlot WHERE seedlot_number = v_new_seedlot_number; IF v_count >= 1 THEN DELETE FROM smp_mix_gen_qlty WHERE seedlot_number = v_new_seedlot_number; DELETE FROM smp_mix WHERE seedlot_number = v_new_seedlot_number; DELETE FROM seedlot_characteristic WHERE seedlot_number = v_new_seedlot_number; DELETE FROM seedlot_genetic_worth WHERE seedlot_number = v_new_seedlot_number; DELETE FROM seedlot_geometry WHERE seedlot_number = v_new_seedlot_number; DELETE FROM seedlot_collection_geometry WHERE seedlot_number = v_new_seedlot_number; DELETE FROM seedlot_heritage WHERE parent_seedlot_no = v_new_seedlot_number; DELETE FROM seedlot_heritage WHERE child_seedlot_no = v_new_seedlot_number; DELETE FROM seedlot_override WHERE seedlot_number = v_new_seedlot_number; DELETE FROM seedlot_owner_quantity WHERE seedlot_number = v_new_seedlot_number; DELETE FROM seedlot_physical_balance WHERE seedlot_number = v_new_seedlot_number; DELETE FROM seedlot_plan_zone WHERE seedlot_number = v_new_seedlot_number; -- PT#43998 - Retain the seedlot test data. --DELETE FROM seedlot_test -- WHERE seedlot_number = v_new_seedlot_number; DELETE FROM seedlot_parent_tree_gen_qlty WHERE seedlot_number = v_new_seedlot_number; DELETE FROM seedlot_parent_tree WHERE seedlot_number = v_new_seedlot_number; DELETE FROM seedlot_transaction WHERE seedlot_number = v_new_seedlot_number; UPDATE SEEDLOT SET seedlot_status_code = 'INC' , vegetation_code = g_vegetation_code , genetic_class_code = g_genetic_class_code , collection_source_code = g_collection_source_code , superior_prvnc_ind = g_superior_prvnc_ind , org_unit_no = g_org_unit_no , registered_seed_ind = 'N' -- registered_seed_ind , to_be_registrd_ind = g_to_be_registrd_ind , registered_date = NULL -- registered_date , fs721a_signed_ind = g_fs721a_signed_ind , bc_source_ind = g_bc_source_ind , nad_datum_code = g_nad_datum_code , utm_zone = g_utm_zone , utm_easting = g_utm_easting , utm_northing = g_utm_northing , longitude_degrees = g_longitude_degrees , longitude_minutes = g_longitude_minutes , longitude_seconds = g_longitude_seconds , longitude_deg_min = g_longitude_deg_min , longitude_min_min = g_longitude_min_min , longitude_sec_min = g_longitude_sec_min , longitude_deg_max = g_longitude_deg_max , longitude_min_max = g_longitude_min_max , longitude_sec_max = g_longitude_sec_max , latitude_degrees = g_latitude_degrees , latitude_minutes = g_latitude_minutes , latitude_seconds = g_latitude_seconds , latitude_deg_min = g_latitude_deg_min , latitude_min_min = g_latitude_min_min , latitude_sec_min = g_latitude_sec_min , latitude_deg_max = g_latitude_deg_max , latitude_min_max = g_latitude_min_max , latitude_sec_max = g_latitude_sec_max , seed_coast_area_code = g_seed_coast_area_code , elevation = g_elevation , elevation_min = g_elevation_min , elevation_max = g_elevation_max , seed_plan_unit_id = g_seed_plan_unit_id , orchard_id = g_orchard_id , secondary_orchard_id = g_secondary_orchard_id , collection_locn_desc = g_collection_locn_desc , collection_cli_number = g_collection_cli_number , collection_cli_locn_cd = g_collection_cli_locn_cd , collection_start_date = g_collection_start_date , collection_end_date = g_collection_end_date , cone_collection_method_code = g_cone_collection_method_cd , cone_collection_method2_code = g_cone_collection_method2_cd , collection_lat_deg = g_collection_lat_deg , collection_lat_min = g_collection_lat_min , collection_lat_sec = g_collection_lat_sec , collection_latitude_code = g_collection_latitude_code , collection_long_deg = g_collection_long_deg , collection_long_min = g_collection_long_min , collection_long_sec = g_collection_long_sec , collection_longitude_code = g_collection_longitude_code , collection_elevation = g_collection_elevation , collection_elevation_min = g_collection_elevation_min , collection_elevation_max = g_collection_elevation_max , collection_area_radius = g_collection_area_radius , collection_seed_plan_zone_ind = g_collection_spz_ind , collection_bgc_ind = g_collection_bgc_ind , no_of_containers = 1 -- no_of_containers , clctn_volume = 0.01 -- clctn_volume , vol_per_container = 0.01-- vol_per_container , nmbr_trees_from_code = g_nmbr_trees_from_code , effective_pop_size = g_effective_pop_size , original_seed_qty = 0 -- original_seed_qty , interm_strg_client_number = g_interm_strg_client_number , interm_strg_client_locn = g_interm_strg_client_locn , interm_strg_st_date = g_interm_strg_st_date , interm_strg_end_date = g_interm_strg_end_date , interm_facility_code = g_interm_facility_code , interm_strg_locn = g_interm_strg_locn , interm_strg_cmt = g_interm_strg_cmt , extraction_st_date = g_extraction_st_date , extraction_end_date = g_extraction_end_date , extraction_volume = 0 -- extraction_volume , extrct_cli_number = g_extrct_cli_number , extrct_cli_locn_cd = g_extrct_cli_locn_cd , extraction_comment = g_extraction_comment , stored_cli_number = g_stored_cli_number , stored_cli_locn_cd = g_stored_cli_locn_cd , lngterm_strg_st_date = g_lngterm_strg_st_date , historical_tsr_date = g_historical_tsr_date , ownership_comment = g_ownership_comment , cone_seed_desc = g_cone_seed_desc , seedlot_comment = v_seedlot_comment , temporary_storage_start_date = g_temporary_storage_start_dt , temporary_storage_end_date = g_temporary_storage_end_date , collection_standard_met_ind = g_coll_standard_met_ind , applicant_email_address = g_applicant_email_address , biotech_processes_ind = g_biotech_processes_ind , pollen_contamination_ind = g_pollen_contamination_ind , pollen_contamination_pct = g_pollen_contamination_pct , controlled_cross_ind = g_controlled_cross_ind , orchard_comment = g_orchard_comment , total_parent_trees = g_total_parent_trees , smp_parents_outside = g_smp_parents_outside , smp_mean_bv_growth = g_smp_mean_bv_growth , smp_success_pct = g_smp_success_pct , contaminant_pollen_bv = g_contaminant_pollen_bv , orchard_contamination_pct = g_orchard_contamination_pct , coancestry = g_coancestry , provenance_id = g_provenance_id , seed_plan_zone_code = g_seed_plan_zone_code , pollen_contamination_mthd_code = g_pollen_contam_mthd_code , applicant_client_number = g_applicant_client_number , applicant_client_locn = g_applicant_client_locn , seed_store_client_number = g_seed_store_client_number , seed_store_client_locn = g_seed_store_client_locn , seedlot_source_code = g_seedlot_source_code , female_gametic_mthd_code = g_female_gametic_mthd_code , male_gametic_mthd_code = g_male_gametic_mthd_code , bgc_zone_code = g_bgc_zone_code , bgc_subzone_code = g_bgc_subzone_code , variant = g_variant , bec_version_id = g_bec_version_id , entry_userid = p_userid -- entry_userid , entry_timestamp = SYSDATE -- entry_timestamp , update_userid = p_userid -- update_userid , update_timestamp = SYSDATE -- update_timestamp , approved_userid = 'COPIED_LOT' -- approved_userid , approved_timestamp = g_approved_timestamp -- retain the approved information, for older seedlots. , declared_userid = NULL -- declared_userid , declared_timestamp = NULL -- declared_timestamp , revision_count = 1 -- revision_count WHERE seedlot_number = v_new_seedlot_number; ELSE -- Copy the SEEDLOT record, with status of INC and userid/timestamp to the -- current user's and current date. INSERT INTO seedlot (seedlot_number , seedlot_status_code , vegetation_code , genetic_class_code , collection_source_code , superior_prvnc_ind , org_unit_no , registered_seed_ind , to_be_registrd_ind , registered_date , fs721a_signed_ind , bc_source_ind , nad_datum_code , utm_zone , utm_easting , utm_northing , longitude_degrees , longitude_minutes , longitude_seconds , longitude_deg_min , longitude_min_min , longitude_sec_min , longitude_deg_max , longitude_min_max , longitude_sec_max , latitude_degrees , latitude_minutes , latitude_seconds , latitude_deg_min , latitude_min_min , latitude_sec_min , latitude_deg_max , latitude_min_max , latitude_sec_max , seed_coast_area_code , elevation , elevation_min , elevation_max , seed_plan_unit_id , orchard_id , secondary_orchard_id , collection_locn_desc , collection_cli_number , collection_cli_locn_cd , collection_start_date , collection_end_date , cone_collection_method_code , cone_collection_method2_code , collection_lat_deg , collection_lat_min , collection_lat_sec , collection_latitude_code , collection_long_deg , collection_long_min , collection_long_sec , collection_longitude_code , collection_elevation , collection_elevation_min , collection_elevation_max , collection_area_radius , collection_seed_plan_zone_ind , collection_bgc_ind , no_of_containers , clctn_volume , vol_per_container , nmbr_trees_from_code , effective_pop_size , original_seed_qty , interm_strg_client_number , interm_strg_client_locn , interm_strg_st_date , interm_strg_end_date , interm_facility_code , interm_strg_locn , interm_strg_cmt , extraction_st_date , extraction_end_date , extraction_volume , extrct_cli_number , extrct_cli_locn_cd , extraction_comment , stored_cli_number , stored_cli_locn_cd , lngterm_strg_st_date , historical_tsr_date , ownership_comment , cone_seed_desc , seedlot_comment , temporary_storage_start_date , temporary_storage_end_date , collection_standard_met_ind , applicant_email_address , biotech_processes_ind , pollen_contamination_ind , pollen_contamination_pct , controlled_cross_ind , orchard_comment , total_parent_trees , smp_parents_outside , smp_mean_bv_growth , smp_success_pct , contaminant_pollen_bv , orchard_contamination_pct , coancestry , provenance_id , seed_plan_zone_code , pollen_contamination_mthd_code , applicant_client_number , applicant_client_locn , seed_store_client_number , seed_store_client_locn , seedlot_source_code , female_gametic_mthd_code , male_gametic_mthd_code , bgc_zone_code , bgc_subzone_code , variant , bec_version_id , entry_userid , entry_timestamp , update_userid , update_timestamp , approved_userid , approved_timestamp , declared_userid , declared_timestamp , revision_count) SELECT v_new_seedlot_number , 'INC' , vegetation_code , genetic_class_code , collection_source_code , superior_prvnc_ind , org_unit_no , 'N' -- registered_seed_ind , to_be_registrd_ind , NULL -- registered_date , fs721a_signed_ind , bc_source_ind , nad_datum_code , utm_zone , utm_easting , utm_northing , longitude_degrees , longitude_minutes , longitude_seconds , longitude_deg_min , longitude_min_min , longitude_sec_min , longitude_deg_max , longitude_min_max , longitude_sec_max , latitude_degrees , latitude_minutes , latitude_seconds , latitude_deg_min , latitude_min_min , latitude_sec_min , latitude_deg_max , latitude_min_max , latitude_sec_max , seed_coast_area_code , elevation , elevation_min , elevation_max , seed_plan_unit_id , orchard_id , secondary_orchard_id , collection_locn_desc , collection_cli_number , collection_cli_locn_cd , collection_start_date , collection_end_date , cone_collection_method_code , cone_collection_method2_code , collection_lat_deg , collection_lat_min , collection_lat_sec , collection_latitude_code , collection_long_deg , collection_long_min , collection_long_sec , collection_longitude_code , collection_elevation , collection_elevation_min , collection_elevation_max , collection_area_radius , collection_seed_plan_zone_ind , collection_bgc_ind , 1 -- no_of_containers , 0.01 -- clctn_volume , 0.01-- vol_per_container , nmbr_trees_from_code , effective_pop_size , 0 -- original_seed_qty , interm_strg_client_number , interm_strg_client_locn , interm_strg_st_date , interm_strg_end_date , interm_facility_code , interm_strg_locn , interm_strg_cmt , extraction_st_date , extraction_end_date , 0 -- extraction_volume , extrct_cli_number , extrct_cli_locn_cd , extraction_comment , stored_cli_number , stored_cli_locn_cd , lngterm_strg_st_date , historical_tsr_date , ownership_comment , cone_seed_desc , v_seedlot_comment , temporary_storage_start_date , temporary_storage_end_date , collection_standard_met_ind , applicant_email_address , biotech_processes_ind , pollen_contamination_ind , pollen_contamination_pct , controlled_cross_ind , orchard_comment , total_parent_trees , smp_parents_outside , smp_mean_bv_growth , smp_success_pct , contaminant_pollen_bv , orchard_contamination_pct , coancestry , provenance_id , seed_plan_zone_code , pollen_contamination_mthd_code , applicant_client_number , applicant_client_locn , seed_store_client_number , seed_store_client_locn , seedlot_source_code , female_gametic_mthd_code , male_gametic_mthd_code , bgc_zone_code , bgc_subzone_code , variant , bec_version_id , p_userid -- entry_userid , SYSDATE -- entry_timestamp , p_userid -- update_userid , SYSDATE -- update_timestamp , 'COPIED_LOT' -- approved_userid , approved_timestamp -- retain the approved information, for older seedlots. , NULL -- declared_userid , NULL -- declared_timestamp , 1 -- revision_count FROM seedlot WHERE seedlot_number = g_seedlot_number; END IF; -- Copy the SEEDLOT_GENETIC_WORTH data to the new seedlot. spr_seedlot_genetic_worth.copy_genetic_worth(g_seedlot_number , v_new_seedlot_number , p_userid); -- Copy the SEEDLOT_PLAN_ZONE data to the new seedlot. INSERT INTO seedlot_plan_zone (seedlot_number ,seed_plan_zone_code ,revision_count ,entry_userid ,entry_timestamp ,primary_ind) SELECT v_new_seedlot_number , seed_plan_zone_code , 1 , p_userid , SYSDATE , primary_ind FROM seedlot_plan_zone WHERE seedlot_number = g_seedlot_number; -- Copy the SEEDLOT_PARENT_TREE data to the new seedlot, with a new revision count. INSERT INTO seedlot_parent_tree (seedlot_number , parent_tree_id , cone_count , pollen_count , smp_success_pct , smp_mix_bv_growth , smp_mix_bv_resistance , smp_mix_bv_density , smp_mix_bv_major_gene , smp_mix_latitude_degrees , smp_mix_latitude_minutes , smp_mix_longitude_degrees , smp_mix_longitude_minutes , smp_mix_elevation , non_orchard_pollen_contam_pct , total_genetic_worth_contrib , revision_count) SELECT v_new_seedlot_number , parent_tree_id , cone_count , pollen_count , smp_success_pct , smp_mix_bv_growth , smp_mix_bv_resistance , smp_mix_bv_density , smp_mix_bv_major_gene , smp_mix_latitude_degrees , smp_mix_latitude_minutes , smp_mix_longitude_degrees , smp_mix_longitude_minutes , smp_mix_elevation , non_orchard_pollen_contam_pct , total_genetic_worth_contrib , 1 FROM seedlot_parent_tree WHERE seedlot_number = g_seedlot_number; -- Copy the SEEDLOT_PARENT_TREE_GEN_QLTY data to the new seedlot. INSERT INTO seedlot_parent_tree_gen_qlty (seedlot_number , parent_tree_id , genetic_type_code , genetic_worth_code , genetic_quality_value , revision_count , seed_plan_unit_id , estimated_ind , untested_ind) SELECT v_new_seedlot_number , parent_tree_id , genetic_type_code , genetic_worth_code , genetic_quality_value , 1 , seed_plan_unit_id , estimated_ind , untested_ind FROM seedlot_parent_tree_gen_qlty WHERE seedlot_number = g_seedlot_number; -- Copy the SMP_MIX data to the new seedlot. INSERT INTO smp_mix (seedlot_number , parent_tree_id , amount_of_material , revision_count) SELECT v_new_seedlot_number , parent_tree_id , amount_of_material , 1 FROM smp_mix WHERE seedlot_number = g_seedlot_number; -- Copy the SMP_MIX_GEN_QLTY data to the new seedlot. INSERT INTO smp_mix_gen_qlty (seedlot_number , parent_tree_id , genetic_type_code , genetic_worth_code , genetic_quality_value , estimated_ind , revision_count) SELECT v_new_seedlot_number , parent_tree_id , genetic_type_code , genetic_worth_code , genetic_quality_value , estimated_ind , 1 FROM smp_mix_gen_qlty WHERE seedlot_number = g_seedlot_number; g_seedlot_number := v_new_seedlot_number; END IF; END copy_seedlot; /****************************************************************************** Procedure: get_rank_a_germ_test_type Purpose: Static method to get Current Rank A Germ Test Type ******************************************************************************/ FUNCTION get_rank_a_germ_test_type ( p_seedlot_number IN VARCHAR2) RETURN VARCHAR2 IS CURSOR c_test IS SELECT seedlot_test_code FROM seedlot_test WHERE seedlot_number = p_seedlot_number AND current_test_ind = 'Y' AND preferred_prep_rnk = 'A'; r_test c_test%ROWTYPE; BEGIN OPEN c_test; FETCH c_test INTO r_test; CLOSE c_test; RETURN r_test.seedlot_test_code; END get_rank_a_germ_test_type; /****************************************************************************** Procedure: get_original_germ_pct Purpose: Static method to get Original Rank A Germ Test Type ******************************************************************************/ FUNCTION get_original_germ_pct ( p_seedlot_number IN VARCHAR2) RETURN NUMBER IS CURSOR c_test IS SELECT germination_pct FROM seedlot_test WHERE seedlot_number = p_seedlot_number AND preferred_prep_rnk = 'A' ORDER BY test_date ; r_test c_test%ROWTYPE; BEGIN OPEN c_test; FETCH c_test INTO r_test; CLOSE c_test; RETURN r_test.germination_pct; END get_original_germ_pct; /****************************************************************************** Procedure: get_original_tpg Purpose: Static method to get original trees per gram. Code based on CONSEP function cns_fn_get_orig_tpg ******************************************************************************/ FUNCTION get_original_tpg( p_seedlot_number IN VARCHAR2) RETURN NUMBER IS v_trees_per_gram NUMBER(11, 4); CURSOR c_lot IS SELECT s.vegetation_code , s.genetic_class_code FROM seedlot s WHERE s.seedlot_number = p_seedlot_number; r_lot c_lot%ROWTYPE; --First row returned is original germ CURSOR c_germ IS SELECT s.germination_pct FROM seedlot_test s WHERE s.seedlot_number = p_seedlot_number AND s.preferred_prep_rnk = 'A' ORDER BY s.test_date; r_germ c_germ%ROWTYPE; --First row returned is original spg CURSOR c_spg IS SELECT s.seeds_per_gram FROM seedlot_test s WHERE s.seedlot_number = p_seedlot_number AND s.seedlot_test_code = 'SPG' ORDER BY s.test_date; r_spg c_spg%ROWTYPE; CURSOR c_cpb IS SELECT cavities_per_block FROM cavities_block cb WHERE cb.seedling_container_code = '415B'; r_cpb c_cpb%ROWTYPE; CURSOR c_sow_rule( p_germ_pct IN NUMBER , p_genetic_class_code IN VARCHAR2 , p_vegetation_code IN VARCHAR2) IS SELECT seeds_per_cavity , swng_crctn_factor FROM sowing_rule_factor sr WHERE sr.genetic_class_code = p_genetic_class_code AND ( sr.vegetation_code = p_vegetation_code OR sr.vegetation_code IS NULL) AND sr.min_grmntn_pct <= p_germ_pct AND sr.max_grmntn_pct >= p_germ_pct ORDER BY sr.vegetation_code NULLS LAST; r_sow_rule c_sow_rule%ROWTYPE; CURSOR c_spb( p_seeds_cavity IN NUMBER) IS SELECT seeds_per_block FROM seeds_block sb WHERE sb.seedling_container_code = '415B' AND sb.seeds_per_cavity = p_seeds_cavity; r_spb c_spb%ROWTYPE; BEGIN OPEN c_lot; FETCH c_lot INTO r_lot; CLOSE c_lot; OPEN c_germ; FETCH c_germ INTO r_germ; CLOSE c_germ; OPEN c_spg; FETCH c_spg INTO r_spg; CLOSE c_spg; IF r_spg.seeds_per_gram IS NULL THEN --unexpected v_trees_per_gram := 0; ELSE OPEN c_cpb; FETCH c_cpb INTO r_cpb; CLOSE c_cpb; IF r_germ.germination_pct IS NULL THEN --unexpected v_trees_per_gram := 0; ELSE OPEN c_sow_rule(r_germ.germination_pct, r_lot.genetic_class_code, r_lot.vegetation_code); FETCH c_sow_rule INTO r_sow_rule; CLOSE c_sow_rule; OPEN c_spb (r_sow_rule.seeds_per_cavity); FETCH c_spb INTO r_spb; CLOSE c_spb; IF (r_spb.seeds_per_block = 0) OR (r_sow_rule.swng_crctn_factor = 0) THEN --unexpected v_trees_per_gram := 0; ELSE v_trees_per_gram := (r_spg.seeds_per_gram / r_spb.seeds_per_block) * (r_cpb.cavities_per_block / r_sow_rule.swng_crctn_factor); END IF; END IF; END IF; RETURN(v_trees_per_gram); END get_original_tpg; /****************************************************************************** Procedure: get_original_potential_trees Purpose: Static method to get original potential trees ******************************************************************************/ FUNCTION get_original_potential_trees( p_seedlot_number IN VARCHAR2) RETURN NUMBER /* NOTE: Value returned is NOT in thousands. */ IS v_potential_trees NUMBER(11, 1); CURSOR c_lot IS SELECT s.original_seed_qty FROM seedlot s WHERE s.seedlot_number = p_seedlot_number; r_lot c_lot%ROWTYPE; BEGIN OPEN c_lot; FETCH c_lot INTO r_lot; CLOSE c_lot; v_potential_trees := r_lot.original_seed_qty * get_original_tpg(p_seedlot_number); RETURN(v_potential_trees); END get_original_potential_trees; /****************************************************************************** Procedure: is_seedlot_b_plus Purpose: Returns TRUE if the seedlot is B+ (i.e. B class with superior provenance) ******************************************************************************/ FUNCTION is_seedlot_b_plus (p_seedlot_number IN VARCHAR2) RETURN BOOLEAN IS CURSOR c_lot IS SELECT COUNT(1) FROM seedlot WHERE seedlot_number = p_seedlot_number AND genetic_class_code = 'B' AND superior_prvnc_ind = 'Y'; v_count NUMBER(10); BEGIN --B+ row exists? OPEN c_lot; FETCH c_lot INTO v_count; CLOSE c_lot; RETURN (v_count > 0); END is_seedlot_b_plus; /****************************************************************************** Procedure: is_seedlot_b_not_supprov Purpose: Returns TRUE if the seedlot is B and is not of superior provenance. (i.e. B class with superior provenance of N) ******************************************************************************/ FUNCTION is_seedlot_b_not_supprov (p_seedlot_number IN VARCHAR2) RETURN BOOLEAN IS CURSOR c_lot IS SELECT COUNT(1) FROM seedlot WHERE seedlot_number = p_seedlot_number AND genetic_class_code = 'B' AND superior_prvnc_ind = 'N'; v_count NUMBER(10); BEGIN --B row exists? OPEN c_lot; FETCH c_lot INTO v_count; CLOSE c_lot; RETURN (v_count > 0); END is_seedlot_b_not_supprov; /****************************************************************************** Procedure: get_gw Purpose: Static: returns Genetic Worth value identified by the id and code passed in. ******************************************************************************/ FUNCTION get_gw (p_seedlot_number IN VARCHAR2 , p_genetic_worth_code IN VARCHAR2) RETURN NUMBER IS CURSOR c_lot IS SELECT genetic_worth_rtng FROM seedlot_genetic_worth WHERE seedlot_number = p_seedlot_number AND genetic_worth_code = p_genetic_worth_code; v_gw seedlot_genetic_worth.genetic_worth_rtng%TYPE; BEGIN OPEN c_lot; FETCH c_lot INTO v_gw; CLOSE c_lot; RETURN (v_gw); END get_gw; END spr_seedlot;