SPR001A - Calculation of SMP Mix

 Description:

This page is used to calculate SMP Mix for Seedlots from Orchard Parent Trees. The workflow for this page is as follows:

  1. User searches for and selects Parent Trees that will be used for SMP calculation. The parent trees may come from various orchards, but must be of the same species.
  2. Enter the volume for each parent tree
  3. User presses Save to save the entries.
  4. Based on the entered Parent Trees, the user is presented with a summary of the calculated SMP values.
  5. Pressing the Save SMP button calculates and saves SMP Summary values. Clicking Return to Parent Tree Contrib. returns user to the SPR001A Parent Tree Contribution page with the calculated SMP values populating the Copy SMP fields. This may also be done in one step by clicking Save and Return to Parent Tree Contrib.

 
 Screen Layout (Parent Tree Contribution):

 
 Business Rules:
  • This page is accessed via the Calculate SMP Mix button at the bottom of the SPR01A Parent Tree Contribution page.
  • This page can only be accessed, if an Orchard Id and Primary SPU has been selected and saved from the previous page.
  • Only users with SPR_TREE_SEED_CENTRE_ADMIN role can update Seedlots in Submitted(SUB), Approved(APP) or Complete(COM) status.
  • Seedlots in Expired(EXP) and Cancelled(CAN) Status cannot be updated by any authority group.
  • If the Primary Seed Plan Unit is changed or if an Orchard is removed or changed, for the seedlot, the SMP Parent Tree entries will not be affected, but to recalculation will be required by user on this page to get the new # of SMP Parents from Outside value. There will be a warning for user to recalculate displayed on the Parent Contribution screen whenever there is changed Parent Tree data.
  • Default list for entering parent trees is 10 rows, but more rows can be added by pressing the Add More Parents button

Field Descriptions:
Header:
 
Seedlot Number:  
 

Total Length:

 5
 

Data Type:

 Alpha Numeric
 

Data Source:

 SEEDLOT.SEEDLOT_NUMBER
     
Orchard Information:
 
Orchard ID:  
  Total Length: 3
 

Data Type:

 Alpha Numeric
 

Data Source:

 SEEDLOT.ORCHARD_ID

Optionality:

 Read Only
 
  Orchard Name:  
  Total Length: 30
 

Data Type:

 Alpha Numeric
 

Data Source:

ORCHARD.ORCHARD_NAME

 

Optionality:

 Read Only
     
Additional Orchard ID:  
  Total Length: 3
 

Data Type:

 Alpha Numeric
 

Data Source:

 SEEDLOT.SECONDARY_ORCHARD_ID
 

Optionality:

 Read Only
     
  Additional Orchard Name:  
  Total Length: 30
 

Data Type:

 Alpha Numeric
 

Data Source:

ORCHARD.ORCHARD_NAME

 

Optionality:

 Read Only
     
Area of Use:
 
  Primary SPU:  
 

Total Length:

 10
 

Data Type:

 Alpha Numeric
 

Data Source:

 SEEDLOT.SEED_PLAN_UNIT_ID
     
  Species, SPZ A, Elevation Range(m):  
 

Data Source:

 V_SPW_SEED_PLAN_UNIT.VEGETATION_CODE V_SPW_SEED_PLAN_UNIT.SEED_PLAN_ZONE_CODE V_SPW_SEED_PLAN_UNIT.ELEVATION_MIN V_SPW_SEED_PLAN_UNIT.ELEVATION_MAX (WHERE SEED_PLAN_UNIT_ID = <seed_plan_unit_id from Primary SPU>)
     
     
 Parent Tree Information:
 
Parent Tree Number:  
 

Total Length:

 5
 

Data Source:

 PARENT_TREE.PARENT_TREE_NUMBER
  Validations:
  1. Parent Tree must be in system.
  2. Must be of same species of seedlot
  3. Must be tested and active
  4. Must be unique
     
Volume (ml):  
  Total Length: 9,2
  Data Source: SMP_MIX.AMOUNT_OF_MATERIAL
     
Proportion:  
  Total Length: 5,4
  Data Source: Calculated by Volume/Total Volume
   
   
BV-G/CV-G:  
 

Total Length:

 4,1
Format: 999.0
 

Data Source:

 SMP_MIX_GEN_QLTY.GENETIC_QUALITY_VALUE (WHERE GENETIC_WORTH_CODE='G')
Notes: Display only
   
BV-D/CV-D:  
 

Total Length:

 4,1
Format: 999.0
 

Data Source:

 SMP_MIX_GEN_QLTY.GENETIC_QUALITY_VALUE (WHERE GENETIC_WORTH_CODE='D')
  Notes: Display only
     
BV-R/CV-R:  
 

Total Length:

 4,1
Format: 999.0
 

Data Source:

 SMP_MIX_GEN_QLTY.GENETIC_QUALITY_VALUE (WHERE GENETIC_WORTH_CODE='R')
  Notes: Display only
     
  Elevation:  
  Total Length: 7,2
  Data Source: SIL_GET_PT_GEOG()
  Notes: Display only
     
  Latitude:  
  Total Length: 2 / 2 / 2
  Format: Degrees° Minutes' Seconds"
  Data Source: SIL_GET_PT_GEOG()
  Notes: Display only
     
  Longitude:  
  Total Length: 3 / 2 / 2
  Format: Degrees° Minutes' Seconds"
  Data Source: SIL_GET_PT_GEOG()
  Notes: Display only
     
  Weighted BV-G:  
  Format: Numeric
  Data Source: Calculated by Proportion * BV-G
  Notes: Display only
     
     
  Weighted BV-R:  
  Format: Numeric
  Data Source: Calculated by Proportion * BV-R
  Notes: Display only
     
  Weighted Elev:  
  Format: Numeric
  Data Source: Calculated by Proportion * Elevation
  Notes: Display only
     
     
  Weighted Latitude:  
  Format: Numeric
  Data Source: Calculated by Proportion * Latitude
  Notes: Display only
     
     
  Weighted Longitude: Proportion * Longitude
  Format: Numeric
  Data Source: Calculated by Proportion * Longitude
  Notes: Display only
     
Summary:
     
  # of SMP Parents from Outside:  
 

Total Length:

 5
  Format: 99999
 

Data Type:

 Numeric
 

Data Source:

 SEEDLOT.SMP_PARENTS_OUTSIDE
 

Notes:

Display Only
The number of SMP Parent Trees entered for the Seedlot that are not included in the Parent Tree Contibution. (i.e. PARENT_TREE_ID exists in SMP_MIX for the SEEDLOT_NUMBER but does not exist in SEEDLOT_PARENT_TREE for the same SEEDLOT_NUMBER).
     
Total Volume:  
 

Total Length:

 12,2
 

Data Type:

 Numeric
 

Data Source:

 Calculated by adding all entered volumes
 

Notes:

 Display Only
     
Mean elev. of SMP mix used on parent:  
 

Total Length:

 7,2
 

Data Type:

 Numeric
 

Data Source:

 Calculated by sum of all Weighted Elevation values
 

Notes:

 Display Only
     
Mean (degrees/minutes) lat. of SMP mix used on parent:  
 

Total Length:

 2 / 2
 

Data Type:

 Numeric
 

Data Source:

 Calculated by sum of all Weighted Latitiude values
 

Notes:

Display Only
Any Seconds value of the Latitude will be truncated.
     
Mean (degrees/minutes) long. of SMP mix used on parent:  
 

Total Length:

 3 / 2
 

Data Type:

 Numeric
 

Data Source:

 Calculated by sum of all Weighted Longitude values
 

Notes:

 Display Only
Any Seconds value of the Longitude will be truncated.
     
Interim Calculations:
     
SMP weightedcontrib to BV-G:  
 

Data Source:

 Interim calculation - not stored
 

Derivation:

 Parent Tree BV-G * Proportion
     
SMP weighted contrib to BV-R:  
 

Data Source:

 Interim calculation - not stored
 

Derivation:

 Parent Tree BV-R * Proportion
     
SMP weightedcontrib to elev.:  
 

Data Source:

 Interim calculation - not stored
 

Derivation:

 Mean elev. of SMP mix used on parent * Proportion
     
SMP weighted contrib. to lat.:  
 

Data Source:

 Interim calculation - not stored
 

Derivation:

 Mean Latitude of SMP mix used on parent Converted to decimal * Proportion
     
SMP weightedcontrib. to long.:  
 

Data Source:

 Interim calculation - not stored
 

Derivation:

 Mean Longitude of SMP mix used on parent Converted to decimal * Proportion
     
# of SMP Parents from Outside:  
 

Data Source:

 Interim calculation - not stored
 

Derivation:

SELECT COUNT(sm.parent_tree_id)
  FROM SEEDLOT_PARENT_TREE spt
      ,SMP_MIX sm
 WHERE sm.seedlot_number = smp.seedlot_number
   AND smp.seedlot_number = p_seedlot_number
   AND sm.parent_tree_id <> smp.parent_tree_id;
     
Total Volume:  
 

Data Source:

 Interim calculation - not stored
 

Derivation:

SELECT SUM(sm.AMOUNT_OF MATERIAL)
  FROM SMP_MIX sm
 WHERE sm.seedlot_number = p_seedlot_number
     
BV-G SMP mix used on parent:  
 

Data Source:

 Interim calculation - not stored
  Derivation: SUM of weighted BV-G
     
BV-R SMP mix used on parent:  
 

Data Source:

 Interim calculation - not stored
  Derivation: SUM of weighted BV-R
     
Mean elev. of SMP mix used on parent:  
 

Data Source:

 Interim calculation - not stored
  Derivation: SUM of weighted Elevation
     
Mean Latitude of SMP mix used on parent:  
 

Data Source:

 Interim calculation - not stored
  Derivation:

SUM of weighted Latitude converted to degrees and minutes
     
Mean Longitude of SMP mix used on parent:  
 

Data Source:

 Interim calculation - not stored
  Derivation: SUM of weighted Latitude converted to degrees and minutes
     
Button:

 

Save and Return to Parent Tree Contrib.

  
  Action: Calculates and saves SMP entry values then returns to SPR01a Parent Contribution page with calculated SMP values populating the Copy SMP fields.
     
Save SMP
Action: Calculates and saves SMP entry values.
     
  Clear
  Action: Clears all page information and performs database GET processing to return previously saved information.
     
Print List
Action: Launches a new window with a printer friendly version of the current SMP Mix Calculation list.
     
  '...'
  Action: Performs subsearch of Parent Tree, bringing back selected data to this page.
     
Database Transactions:
Procedure: MAINLINE  
 

Parameters:
parameters for all page items plus:
p_action
p_result (smp mix array)

Call the procedure corresponding to p_action.  Valid actions are:
GET
RECALC
SAVE

     
Procedure: RECALC  
 

Parameters:
all MAINLINE parameters

  • Sets all of the fields for processing.
  • Calculates the Proportion, Weighted BV-G, BV-R, Elevation, Latitude, and Longitude for each entered Parent Tree for the mix
  • From the calculated weights, calculate the SMP values.
  • Calculate the number of parent trees that are not from the listed orchard.
     
Procedure: GET  
 

Parameters:
all MAINLINE parameters

  • Sets all of the fields for processing.
  • Calls the RECALC procedure to calculate all Weighted column values, and Summary information
  • Returns the list of Parent Trees in an array
     
Procedure: SAVE  
 

Parameters:

  • Sets all of the fields for processing.
  • Calls the RECALC procedure to calculate all Weighted column values, and Summary information
  • Calls the SEEDLOT_CHANGE processing to save the calculated # of SMP Parents from Outside to the SEEDLOT table.
  • Set parameters to the results of SEEDLOT CHANGE processing.
     
Change History:  
2006-07-20:  
Elaine Wong Initially Created
2006-07-31:  
Elaine Wong Updated with changes to query, and formatting.
[Return to main SPAR page]
Province of British Columbia, Ministry of Forests, Information Management Group
Send suggestions or comments to Web Administrator
December 21, 2005