SPROC 2

CREATE PROCEDURE [dbo].[batch_report_v2]
(
@SBN BIGINT,
@userlogin VARCHAR(128),
@Rev_Comment VARCHAR(255),
@Input_Report_Rev INT --If @Report_Rev = -1, create the report
                      --If @Report_Rev >= 0, just display the already created report
)
AS

DECLARE @Batch_Report_Ver VARCHAR(25)
SET @Batch_Report_Ver = (SELECT DB_NAME());

DECLARE @Report_Date DATETIME


--Clear out the tables for the new report data
DELETE FROM [dbo].[TempQR_Master]
DELETE FROM [dbo].[TempQR_Sub]
DELETE FROM [dbo].[TempArticle]
DELETE FROM [dbo].[TempDose_Family]
DELETE FROM [dbo].[TempEBeamSubsystem]
DELETE FROM [dbo].[TempRecipe]
DELETE FROM [dbo].[TempUPN]
DELETE FROM [dbo].[TempSBNInfo]
DELETE FROM [dbo].[TempQR_Master_Diverted]


------------------------------------------------------------------------
--Get all Quality Records associated with a particular SBN for later use
--by saving the queries to temp tables for easier retrieval, and for the
--SQL Reporting tool. It requires these tables to exist.
------------------------------------------------------------------------

DECLARE @TempQR_Master TABLE(
    CountID BIGINT,
    SiteID INT,
    TreatmentSystemID INT,
    Barcode VARCHAR(200),
    Label_SN VARCHAR(50),
    SBN INT,
    ProductCode VARCHAR(50),
    Lot VARCHAR(50),
    ProcessSpecID INT,
    PARAM INT,
    PARAM_REV INT,
    CONFIG_REV INT,
    SCALE_REV INT,
    SETUP_STAT BIT,
    VALIDATION_RUN BIT,
    RECEIVED_TIME DATETIME,
    OUTPUT_TIME DATETIME,
    REJECT_FORCED BIT,
    PROCESSING_STATUS TINYINT,
    OUTPUT_STATUS TINYINT,
    INTERRUPTIONS INT,
    db_rev VARCHAR(50),
    OutfeedBarcode VARCHAR(200),
    CreatedTime DATETIME,
    TrackClass VARCHAR(50),
    Op_uname VARCHAR(255),
    Op_rname VARCHAR(255),
    Rec_CRC INT
)

IF (@Input_Report_Rev = -1) --Create a new report
BEGIN
    INSERT INTO @TempQR_Master
    SELECT *
    FROM QR_Master
    WHERE QR_Master.SBN = @SBN
END
ELSE
BEGIN
    --Only get boxes that belong to this SBN in case the report was created in the middle of a batch
    --This is done by only selecting boxes that have records before this Rev of the report was created.
    SELECT @Report_Date = CreatedTime
    FROM Report_Summary_v2  --Version 2
    WHERE SBN = @SBN AND Report_Revision   = @Input_Report_Rev

    INSERT INTO @TempQR_Master
    SELECT *
    FROM QR_Master
    WHERE QR_Master.SBN = @SBN AND
          QR_Master.Output_Time < @Report_Date

END

--Now for Boxes which were Diverted from QR_Master
/*
This section uses the TempQR_Master table to get the Output_Status
code which is referenced against a list of Divert Reasons for each value > 0.
An Output_Status of 0 means it was treated OK.
This reason is then appended to the end of the TempQR_Master_Diverted table
after everything from the QR_Master table is copied
*/
DECLARE @Reject_Reason VARCHAR(128)
DECLARE @i CURSOR

DECLARE @Interruption_Reason VARCHAR(128)
DECLARE @j CURSOR

DECLARE @CountID BIGINT
DECLARE @SiteID INT
DECLARE @TreatmentSystemID INT
DECLARE @Barcode VARCHAR(200)
DECLARE @Label_SN VARCHAR(50)
DECLARE @SBN_2 INT
DECLARE @ProductCode VARCHAR(50)
DECLARE @Lot VARCHAR(50)
DECLARE @ProcessSpecID INT
DECLARE @Param INT
DECLARE @Param_Rev INT
DECLARE @Config_Rev INT
DECLARE @Scale_Rev INT
DECLARE @Setup_Stat BIT
DECLARE @VALIDATION_RUN BIT
DECLARE @Received_Time DATETIME
DECLARE @Output_Time DATETIME
DECLARE @Reject_Forced BIT
DECLARE @Processing_Status TINYINT
DECLARE @Output_Status TINYINT
DECLARE @Interruptions INT
DECLARE @db_rev VARCHAR(50)
DECLARE @OutfeedBarcode VARCHAR(200)
DECLARE @CreatedTime DATETIME
DECLARE @TrackClass VARCHAR(50)
DECLARE @Op_uname VARCHAR(255)
DECLARE @Op_rname VARCHAR(255)
DECLARE @Rec_CRC INT

DECLARE @TempQR_Master_Diverted TABLE(
    CountID BIGINT,
    SiteID INT,
    TreatmentSystemID INT,
    Barcode VARCHAR(200),
    Label_SN VARCHAR(50),
    SBN INT,
    ProductCode VARCHAR(50),
    Lot VARCHAR(50),
    ProcessSpecID INT,
    PARAM INT,
    PARAM_REV INT,
    CONFIG_REV INT,
    SCALE_REV INT,
    SETUP_STAT BIT,
    VALIDATION_RUN BIT,
    RECEIVED_TIME DATETIME,
    OUTPUT_TIME DATETIME,
    REJECT_FORCED BIT,
    PROCESSING_STATUS TINYINT,
    OUTPUT_STATUS TINYINT,
    INTERRUPTIONS INT,
    db_rev VARCHAR(50),
    OutfeedBarcode VARCHAR(200),
    CreatedTime DATETIME,
    TrackClass VARCHAR(50),
    Op_uname VARCHAR(255),
    Op_rname VARCHAR(255),
    Rec_CRC INT,
    Reject_Reason VARCHAR(128),
    Interruption_Reason VARCHAR(128)
)

-- Only add boxes which have a divert reason, and QA boxes which have a 'proper'
-- divert reason, not just the fact that it was a QA box.
SET @i = CURSOR FOR
SELECT *
FROM @TempQR_Master
WHERE Output_Status <> 0 AND Output_Status <> 35 AND Output_Status <> 36

OPEN @i
-- Get the first record
FETCH NEXT FROM @i INTO
@CountID,
@SiteID,
@TreatmentSystemID,
@Barcode,
@Label_SN,
@SBN_2,
@ProductCode,
@Lot,
@ProcessSpecID,
@Param,
@Param_Rev,
@Config_Rev,
@Scale_Rev,
@Setup_Stat,
@Validation_Run,
@Received_Time,
@Output_Time,
@Reject_Forced,
@Processing_Status,
@Output_Status,
@Interruptions,
@db_rev,
@OutfeedBarcode,
@CreatedTime,
@TrackClass,
@Op_uname,
@Op_rname,
@Rec_CRC

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @Reject_Reason =   -- This is the reason for a Diverted box that shows up in the Report
        CASE @Output_Status

            WHEN 0  THEN 'Accepted'
            WHEN 1  THEN 'NA fault'
            WHEN 2  THEN 'NA fault'
            WHEN 3  THEN 'NA fault'
            WHEN 4  THEN 'NA fault'
            WHEN 5  THEN 'NA fault'
            WHEN 6  THEN 'NA fault'
            WHEN 7  THEN 'NA fault'
            WHEN 8  THEN 'NA fault'
            WHEN 9  THEN 'NA fault'
            WHEN 10 THEN 'NA fault'
            WHEN 11 THEN 'NA fault'
            WHEN 12 THEN 'NA fault'
            WHEN 13 THEN 'NA fault'
            WHEN 14 THEN 'NA fault'
            WHEN 15 THEN 'NA fault'
            WHEN 16 THEN 'NA fault'
            WHEN 17 THEN 'NA fault'
            WHEN 18 THEN 'NA fault'
            WHEN 19 THEN 'NA fault'
            WHEN 20 THEN 'NA fault'
            WHEN 21 THEN 'NA fault'
            WHEN 22 THEN 'NA fault'
            WHEN 23 THEN 'NA fault'
            WHEN 24 THEN 'NA fault'
            WHEN 25 THEN 'NA fault'
            WHEN 26 THEN 'NA fault'
            WHEN 27 THEN 'NA fault'
            WHEN 28 THEN 'No Read at Outfeed'
            WHEN 29 THEN 'Out of Sequence at Outfeed'
            WHEN 30 THEN 'Barcode Mismatch at Outfeed'
            WHEN 31 THEN 'Wrong Orientation at Outfeed'
            WHEN 32 THEN 'NA fault'
            WHEN 33 THEN 'Processing out of limits after first pass'
            WHEN 34 THEN 'Processing out of limits after second pass'
            WHEN 35 THEN 'QA load after first pass'
            WHEN 36 THEN 'QA load after second pass'
            WHEN 37 THEN 'Process OK after first pass'
            WHEN 38 THEN 'Process OK after second pass'
            WHEN 39 THEN 'NA fault'
            WHEN 40 THEN 'NA fault'
            WHEN 41 THEN 'NA fault'
            WHEN 42 THEN 'NA fault'
            WHEN 43 THEN 'NA fault'
            WHEN 44 THEN 'NA fault'
            WHEN 45 THEN 'NA fault'
            WHEN 46 THEN 'NA fault'
            WHEN 47 THEN 'Reject on Min Dose'
            WHEN 48 THEN 'Reject on Max Dose'
            WHEN 49 THEN 'Reject on Min and Max Dose'
            WHEN 50 THEN 'Trans Coeff low limit A FP'
            WHEN 51 THEN 'Trans Coeff high limit A FP'
            WHEN 52 THEN 'Beam Current low limit A FP'
            WHEN 53 THEN 'Beam Current high limit A FP'
            WHEN 54 THEN 'Scan Left low limit A FP'
            WHEN 55 THEN 'Scan Left high limit A FP'
            WHEN 56 THEN 'Scan Right low limit A FP'
            WHEN 57 THEN 'Scan Right high limit A FP'
            WHEN 58 THEN 'UBC Speed low limit A FP'
            WHEN 59 THEN 'UBC Speed high limit A FP'
            WHEN 60 THEN 'Trans Coeff low limit B FP'
            WHEN 61 THEN 'Trans Coeff high limit B FP'
            WHEN 62 THEN 'Beam Current low limit B FP'
            WHEN 63 THEN 'Beam Current high limit B FP'
            WHEN 64 THEN 'Scan Left low limit B FP'
            WHEN 65 THEN 'Scan Left high limit B FP'
            WHEN 66 THEN 'Scan Right low limit B FP'
            WHEN 67 THEN 'Scan Right high limit B FP'
            WHEN 68 THEN 'UBC Speed low limit B FP'
            WHEN 69 THEN 'UBC Speed high limit B FP'
            WHEN 70 THEN 'Trans Coeff low limit A SP'
            WHEN 71 THEN 'Trans Coeff high limit A SP'
            WHEN 72 THEN 'Beam Current low limit A SP'
            WHEN 73 THEN 'Beam Current high limit A SP'
            WHEN 74 THEN 'Scan Left low limit A SP'
            WHEN 75 THEN 'Scan Left high limit A SP'
            WHEN 76 THEN 'Scan Right low limit A SP'
            WHEN 77 THEN 'Scan Right high limit A SP'
            WHEN 78 THEN 'UBC Speed low limit A SP'
            WHEN 79 THEN 'UBC Speed high limit A SP'
            WHEN 80 THEN 'Trans Coeff low limit B SP'
            WHEN 81 THEN 'Trans Coeff high limit B SP'
            WHEN 82 THEN 'Beam Current low limit B SP'
            WHEN 83 THEN 'Beam Current high limit B SP'
            WHEN 84 THEN 'Scan Left low limit B SP'
            WHEN 85 THEN 'Scan Left high limit B SP'
            WHEN 86 THEN 'Scan Right low limit B SP'
            WHEN 87 THEN 'Scan Right high limit B SP'
            WHEN 88 THEN 'UBC Speed low limit B SP'
            WHEN 89 THEN 'UBC Speed high limit B SP'
            WHEN 90 THEN 'Process Value Error'
            WHEN 91 THEN 'Dose Value Error'
            WHEN 92 THEN 'NA fault'
            WHEN 93 THEN 'NA fault'
            WHEN 94 THEN 'NA fault'
            WHEN 95 THEN 'NA fault'
            WHEN 96 THEN 'NA fault'
            WHEN 97 THEN 'NA fault'
            WHEN 98 THEN 'NA fault'
            WHEN 99 THEN 'NA fault'
            ELSE 'Reason Not Specified'
        END --for the CASE

    SET @Interruption_Reason =   -- This is the interruption reason that shows up in the Report
        CASE @Interruptions
            --These are Interruptions from the Process Controller
            WHEN 0 THEN 'NONE'
            WHEN 1 THEN 'OZONE_BLOWER_FLOW_SW_1'
            WHEN 2 THEN 'OZONE_BLOWER_FLOW_SW_2'
            WHEN 3 THEN 'OL_OZONE_BLOWER_1'
            WHEN 4 THEN 'OL_OZONE_BLOWER_2'
            WHEN 5 THEN 'UBC_A_INTLK'
            WHEN 6 THEN 'UBC_B_INTLK'
            WHEN 7 THEN 'PARAM_SET_NOT_LOADED'
            WHEN 8 THEN 'UBC_SPD_NOT_LOADED'
            WHEN 9 THEN 'UBC_SPEED_CHECK_INTLK'
            WHEN 10 THEN 'DEC_TABLE_CRC'
            WHEN 11 THEN 'SBN_TABLE_CRC'
            WHEN 12 THEN 'PR_TABLE_CRC'
            WHEN 13 THEN 'SCALE_LOAD_FLAG'
            WHEN 14 THEN 'CONFIG_LOAD_FLAG'
            WHEN 15 THEN 'PARAM_CRC'
            WHEN 16 THEN 'CONFIG_CRC'
            WHEN 17 THEN 'SCALE_CRC'
            WHEN 18 THEN 'SQL_INTLK'
            WHEN 19 THEN 'SQL_CONN_INTLK'
            WHEN 20 THEN 'PARAM_LOADED'
            WHEN 21 THEN 'CONFIG_LOADED'
            WHEN 22 THEN 'SCALE_LOADED'
            WHEN 23 THEN 'WATCH_DOG'
            WHEN 24 THEN 'ACC_A_READY'
            WHEN 25 THEN 'ACC_B_READY'
            WHEN 26 THEN 'BOX_BURN_INTLK'
            WHEN 27 THEN 'PROCESS_OFF'
            WHEN 28 THEN 'spare'
            WHEN 29 THEN 'spare'
            WHEN 30 THEN 'spare'
            WHEN 31 THEN 'spare'
            --These are Interruptions for EBeamSubsystem 1A
            WHEN 100 THEN '1A NONE'
            WHEN 101 THEN '1A BEAM_KEY_OFF'
            WHEN 102 THEN '1A F14_OFF'
            WHEN 103 THEN '1A GUN_PUMP_HV'
            WHEN 104 THEN '1A SCAN_PUMP_HV'
            WHEN 105 THEN '1A KLY_PUMP_HV'
            WHEN 106 THEN '1A SUMMARY_TRIP'
            WHEN 107 THEN '1A ACC_WINDOW_BLOWER'
            WHEN 108 THEN '1A SAFETY_SET'
            WHEN 109 THEN '1A CLIPPER_MON'
            WHEN 110 THEN '1A BIAS_V_MON'
            WHEN 111 THEN '1A PARAM_CRC'
            WHEN 112 THEN '1A CONFIG_CRC'
            WHEN 113 THEN '1A SCALE_CRC'
            WHEN 114 THEN '1A PARAM_LOADED'
            WHEN 115 THEN '1A CONFIG_LOADED'
            WHEN 116 THEN '1A SCALE_LOADED'
            WHEN 117 THEN '1A WATCH_DOG'
            WHEN 118 THEN '1A GUN_FIL_I'
            WHEN 119 THEN '1A GUN_FIL_V'
            WHEN 120 THEN '1A GUN_GRID_V'
            WHEN 121 THEN '1A PROCESS_OFF'
            WHEN 122 THEN '1A BIAS_V_MON'
            WHEN 123 THEN '1A GUN_GRID_V'
            WHEN 124 THEN '1A STC_IP_I'
            WHEN 125 THEN '1A STC_XP_I'
            WHEN 126 THEN '1A GUN_FOCUS_V'
            WHEN 127 THEN '1A GUN_FOCUS_I'
            WHEN 128 THEN '1A GUN_HV'
            WHEN 129 THEN '1A RF_DRIVER'
            WHEN 130 THEN '1A GUN_PUMP'
            WHEN 131 THEN '1A SCAN_PUMP'
            WHEN 132 THEN '1A KLY_PUMP'
            WHEN 133 THEN '1A K_COLL_FLOW'
            WHEN 134 THEN '1A IGBT_I'
            WHEN 135 THEN '1A PFN_V'
            WHEN 136 THEN '1A INV_DIODE'
            WHEN 137 THEN '1A K_COLL_POW'
            WHEN 138 THEN '1A K_PUL_I'
            WHEN 139 THEN '1A K_PUL_V'
            WHEN 140 THEN '1A RF_FWD_1'
            WHEN 141 THEN '1A RF_REF_1'
            WHEN 142 THEN '1A RF_SIN_1'
            WHEN 143 THEN '1A RF_COS_1'
            WHEN 144 THEN '1A K_COLL_IN'
            WHEN 145 THEN '1A K_COLL_OUT'
            WHEN 146 THEN '1A K_FIL_V'
            WHEN 147 THEN '1A K_FIL_I'
            WHEN 148 THEN '1A K_SOL_V'
            WHEN 149 THEN '1A K_SOL_I'
            WHEN 150 THEN '1A BUSBAR_V'
            WHEN 151 THEN '1A SCAN_I_L'
            WHEN 152 THEN '1A SCAN_I_R'
            WHEN 153 THEN '1A PRF'
            WHEN 154 THEN '1A ACC_TEMP'
            WHEN 155 THEN '1A FAC_TEMP'
            WHEN 156 THEN '1A THY_RES'
            WHEN 157 THEN '1A SF6_PRESS'
            WHEN 158 THEN '1A GUN_I_PEAK'
            WHEN 159 THEN '1A GUN_HVPS_I'
            WHEN 160 THEN '1A SCAN_SPEED'
            WHEN 161 THEN '1A VB0_1'
            WHEN 162 THEN '1A CHG_I_COM'
            WHEN 163 THEN '1A BEAM_CLAMP_1'
            WHEN 164 THEN '1A RF_FREQ'
            WHEN 165 THEN '1A RAMP_T_1'
            WHEN 166 THEN '1A TEMP_IGBT_A'
            WHEN 167 THEN '1A PFN_V_COM'
            WHEN 168 THEN '1A AN_IP_A'
            WHEN 169 THEN '1A AN_IP_B'
            WHEN 170 THEN '1A AN_IP_C'
            WHEN 171 THEN '1A SCAN_V'
            WHEN 172 THEN '1A BEAM_CLAMP_2'
            WHEN 173 THEN '1A ACC_FOC_I'
            WHEN 174 THEN '1A ACC_FOC_V'
            WHEN 175 THEN '1A STC_XP_V'
            WHEN 176 THEN '1A STC_IP_V'
            WHEN 177 THEN '1A PT_BIAS_V'
            WHEN 178 THEN '1A BEAM_I_AVE'
            WHEN 179 THEN '1A RF_FWD_2'
            WHEN 180 THEN '1A RF_REF_2'
            WHEN 181 THEN '1A RF_SIN_2'
            WHEN 182 THEN '1A RF_COS_2'
            WHEN 183 THEN '1A KLY_OK'
            WHEN 184 THEN '1A THY_OK'
            WHEN 185 THEN '1A POWER_KEY_ON'
            WHEN 186 THEN '1A W2_LEVEL_SW_OK'
            WHEN 187 THEN '1A SHL_TEMP_SHUTDOWN'
            WHEN 188 THEN '1A SCALE_LOAD_FLAG'
            WHEN 189 THEN '1A CONFIG_LOAD_FLAG'
            WHEN 190 THEN '1A K_COLL_IN'
            WHEN 191 THEN '1A K_COLL_OUT'
            WHEN 192 THEN '1A FAC_TEMP'
            WHEN 193 THEN '1A AN_IP_B'
            WHEN 194 THEN '1A SQL_OK'
            WHEN 195 THEN '1A WINDOW_PDF'
            WHEN 196 THEN '1A SOL_FLOW'
            WHEN 197 THEN '1A LOAD_FLOW'
            WHEN 198 THEN '1A CIRC_FLOW'
            WHEN 199 THEN '1A PT_FLOW'
            WHEN 200 THEN '1A IGBT_FLOW'
            WHEN 201 THEN '1A MAN_HIGH_PRESS'
            WHEN 202 THEN '1A MAN_LOW_PRESS'
            WHEN 203 THEN '1A KLY_BODY_FLOW'
            WHEN 204 THEN '1A MAN_RET_PRESS'
            WHEN 205 THEN '1A OIL_LEVEL_OK'
            WHEN 206 THEN '1A m2113.1'
            WHEN 207 THEN '1A m2113.2'
            WHEN 208 THEN '1A KLY_WIN_FLOW'
            WHEN 209 THEN '1A PT_TEMP'
            WHEN 210 THEN '1A PC_OZONE_BLOWER'
            WHEN 211 THEN '1A PC_UBC_SPEED'
            WHEN 212 THEN '1A m2113.7'
            WHEN 213 THEN '1A m2114.0'
            WHEN 214 THEN '1A m2114.1'
            WHEN 215 THEN '1A m2114.2'
            WHEN 216 THEN '1A m2114.3'
            WHEN 217 THEN '1A m2114.4'
            WHEN 218 THEN '1A m2114.5'
            WHEN 219 THEN '1A m2114.6'
            WHEN 220 THEN '1A m2114.7'
            WHEN 221 THEN '1A m2115.0'
            WHEN 222 THEN '1A m2115.1'
            WHEN 223 THEN '1A m2115.2'
            WHEN 224 THEN '1A m2115.3'
            WHEN 225 THEN '1A m2115.4'
            WHEN 226 THEN '1A m2115.5'
            WHEN 227 THEN '1A m2115.6'
            WHEN 228 THEN '1A m2115.7'
            WHEN 229 THEN '1A m2116.0'
            WHEN 230 THEN '1A m2116.1'
            WHEN 231 THEN '1A m2116.2'
            WHEN 232 THEN '1A m2116.3'
            WHEN 233 THEN '1A m2116.4'
            WHEN 234 THEN '1A m2116.5'
            WHEN 235 THEN '1A m2116.6'
            WHEN 236 THEN '1A m2116.7'
            WHEN 237 THEN '1A m2117.0'
            WHEN 238 THEN '1A m2117.1'
            WHEN 239 THEN '1A m2117.2'
            WHEN 240 THEN '1A m2117.3'
            WHEN 241 THEN '1A m2117.4'
            WHEN 242 THEN '1A m2117.5'
            WHEN 243 THEN '1A m2117.6'
            WHEN 244 THEN '1A m2117.7'
            WHEN 245 THEN '1A m2118.0'
            WHEN 246 THEN '1A m2118.1'
            WHEN 247 THEN '1A m2118.2'
            WHEN 248 THEN '1A m2118.3'
            WHEN 249 THEN '1A m2118.4'
            WHEN 250 THEN '1A m2118.5'
            WHEN 251 THEN '1A m2118.6'
            WHEN 252 THEN '1A m2118.7'
            WHEN 253 THEN '1A m2119.0'
            WHEN 254 THEN '1A m2119.1'
            WHEN 255 THEN '1A m2119.2'
            WHEN 256 THEN '1A m2119.3'
            WHEN 257 THEN '1A m2119.4'
            WHEN 258 THEN '1A m2119.5'
            WHEN 259 THEN '1A m2119.6'
            WHEN 260 THEN '1A m2119.7'
            WHEN 261 THEN '1A m2120.0'
            WHEN 262 THEN '1A m2120.1'
            WHEN 263 THEN '1A m2120.2'
            WHEN 264 THEN '1A m2120.3'
            WHEN 265 THEN '1A m2120.4'
            WHEN 266 THEN '1A m2120.5'
            WHEN 267 THEN '1A m2120.6'
            WHEN 268 THEN '1A m2120.7'
            WHEN 269 THEN '1A m2121.0'
            WHEN 270 THEN '1A m2121.1'
            WHEN 271 THEN '1A m2121.2'
            WHEN 272 THEN '1A m2121.3'
            WHEN 273 THEN '1A m2121.4'
            WHEN 274 THEN '1A m2121.5'
            WHEN 275 THEN '1A m2121.6'
            WHEN 276 THEN '1A m2121.7'
            WHEN 277 THEN '1A m2122.0'
            WHEN 278 THEN '1A m2122.1'
            WHEN 279 THEN '1A m2122.2'
            WHEN 280 THEN '1A m2122.3'
            WHEN 281 THEN '1A m2122.4'
            WHEN 282 THEN '1A m2122.5'
            WHEN 283 THEN '1A m2122.6'
            WHEN 284 THEN '1A m2122.7'
            WHEN 285 THEN '1A m2123.0'
            WHEN 286 THEN '1A m2123.1'
            WHEN 287 THEN '1A m2123.2'
            WHEN 288 THEN '1A m2123.3'
            WHEN 289 THEN '1A m2123.4'
            WHEN 290 THEN '1A m2123.5'
            WHEN 291 THEN '1A m2123.6'
            WHEN 292 THEN '1A m2123.7'
            WHEN 293 THEN '1A m2124.0'
            WHEN 294 THEN '1A m2124.1'
            WHEN 295 THEN '1A m2124.2'
            WHEN 296 THEN '1A m2124.3'
            WHEN 297 THEN '1A m2124.4'
            WHEN 298 THEN '1A m2124.5'
            WHEN 299 THEN '1A m2124.6'
            WHEN 300 THEN '1A m2124.7'
            WHEN 301 THEN '1A m2125.0'
            WHEN 302 THEN '1A m2125.1'
            WHEN 303 THEN '1A m2125.2'
            WHEN 304 THEN '1A m2125.3'
            WHEN 305 THEN '1A m2125.4'
            --These are Interruptions for EBeamSubsystem 2B
            WHEN 400 THEN '2B NONE'
            WHEN 401 THEN '2B BEAM_KEY_OFF'
            WHEN 402 THEN '2B F14_OFF'
            WHEN 403 THEN '2B GUN_PUMP_HV'
            WHEN 404 THEN '2B SCAN_PUMP_HV'
            WHEN 405 THEN '2B KLY_PUMP_HV'
            WHEN 406 THEN '2B SUMMARY_TRIP'
            WHEN 407 THEN '2B ACC_WINDOW_BLOWER'
            WHEN 408 THEN '2B SAFETY_SET'
            WHEN 409 THEN '2B CLIPPER_MON'
            WHEN 410 THEN '2B BIAS_V_MON'
            WHEN 411 THEN '2B PARAM_CRC'
            WHEN 412 THEN '2B CONFIG_CRC'
            WHEN 413 THEN '2B SCALE_CRC'
            WHEN 414 THEN '2B PARAM_LOADED'
            WHEN 415 THEN '2B CONFIG_LOADED'
            WHEN 416 THEN '2B SCALE_LOADED'
            WHEN 417 THEN '2B WATCH_DOG'
            WHEN 418 THEN '2B GUN_FIL_I'
            WHEN 419 THEN '2B GUN_FIL_V'
            WHEN 420 THEN '2B GUN_GRID_V'
            WHEN 421 THEN '2B PROCESS_OFF'
            WHEN 422 THEN '2B BIAS_V_MON'
            WHEN 423 THEN '2B GUN_GRID_V'
            WHEN 424 THEN '2B STC_IP_I'
            WHEN 425 THEN '2B STC_XP_I'
            WHEN 426 THEN '2B GUN_FOCUS_V'
            WHEN 427 THEN '2B GUN_FOCUS_I'
            WHEN 428 THEN '2B GUN_HV'
            WHEN 429 THEN '2B RF_DRIVER'
            WHEN 430 THEN '2B GUN_PUMP'
            WHEN 431 THEN '2B SCAN_PUMP'
            WHEN 432 THEN '2B KLY_PUMP'
            WHEN 433 THEN '2B K_COLL_FLOW'
            WHEN 434 THEN '2B IGBT_I'
            WHEN 435 THEN '2B PFN_V'
            WHEN 436 THEN '2B INV_DIODE'
            WHEN 437 THEN '2B K_COLL_POW'
            WHEN 438 THEN '2B K_PUL_I'
            WHEN 439 THEN '2B K_PUL_V'
            WHEN 440 THEN '2B RF_FWD_1'
            WHEN 441 THEN '2B RF_REF_1'
            WHEN 442 THEN '2B RF_SIN_1'
            WHEN 443 THEN '2B RF_COS_1'
            WHEN 444 THEN '2B K_COLL_IN'
            WHEN 445 THEN '2B K_COLL_OUT'
            WHEN 446 THEN '2B K_FIL_V'
            WHEN 447 THEN '2B K_FIL_I'
            WHEN 448 THEN '2B K_SOL_V'
            WHEN 449 THEN '2B K_SOL_I'
            WHEN 450 THEN '2B BUSBAR_V'
            WHEN 451 THEN '2B SCAN_I_L'
            WHEN 452 THEN '2B SCAN_I_R'
            WHEN 453 THEN '2B PRF'
            WHEN 454 THEN '2B ACC_TEMP'
            WHEN 455 THEN '2B FAC_TEMP'
            WHEN 456 THEN '2B THY_RES'
            WHEN 457 THEN '2B SF6_PRESS'
            WHEN 458 THEN '2B GUN_I_PEAK'
            WHEN 459 THEN '2B GUN_HVPS_I'
            WHEN 460 THEN '2B SCAN_SPEED'
            WHEN 461 THEN '2B VB0_1'
            WHEN 462 THEN '2B CHG_I_COM'
            WHEN 463 THEN '2B BEAM_CLAMP_1'
            WHEN 464 THEN '2B RF_FREQ'
            WHEN 465 THEN '2B RAMP_T_1'
            WHEN 466 THEN '2B TEMP_IGBT_A'
            WHEN 467 THEN '2B PFN_V_COM'
            WHEN 468 THEN '2B AN_IP_A'
            WHEN 469 THEN '2B AN_IP_B'
            WHEN 470 THEN '2B AN_IP_C'
            WHEN 471 THEN '2B SCAN_V'
            WHEN 472 THEN '2B BEAM_CLAMP_2'
            WHEN 473 THEN '2B ACC_FOC_I'
            WHEN 474 THEN '2B ACC_FOC_V'
            WHEN 475 THEN '2B STC_XP_V'
            WHEN 476 THEN '2B STC_IP_V'
            WHEN 477 THEN '2B PT_BIAS_V'
            WHEN 478 THEN '2B BEAM_I_AVE'
            WHEN 479 THEN '2B RF_FWD_2'
            WHEN 480 THEN '2B RF_REF_2'
            WHEN 481 THEN '2B RF_SIN_2'
            WHEN 482 THEN '2B RF_COS_2'
            WHEN 483 THEN '2B KLY_OK'
            WHEN 484 THEN '2B THY_OK'
            WHEN 485 THEN '2B POWER_KEY_ON'
            WHEN 486 THEN '2B W2_LEVEL_SW_OK'
            WHEN 487 THEN '2B SHL_TEMP_SHUTDOWN'
            WHEN 488 THEN '2B SCALE_LOAD_FLAG'
            WHEN 489 THEN '2B CONFIG_LOAD_FLAG'
            WHEN 490 THEN '2B K_COLL_IN'
            WHEN 491 THEN '2B K_COLL_OUT'
            WHEN 492 THEN '2B FAC_TEMP'
            WHEN 493 THEN '2B AN_IP_B'
            WHEN 494 THEN '2B SQL_OK'
            WHEN 495 THEN '2B WINDOW_PDF'
            WHEN 496 THEN '2B SOL_FLOW'
            WHEN 497 THEN '2B LOAD_FLOW'
            WHEN 498 THEN '2B CIRC_FLOW'
            WHEN 499 THEN '2B PT_FLOW'
            WHEN 500 THEN '2B IGBT_FLOW'
            WHEN 501 THEN '2B MAN_HIGH_PRESS'
            WHEN 502 THEN '2B MAN_LOW_PRESS'
            WHEN 503 THEN '2B KLY_BODY_FLOW'
            WHEN 504 THEN '2B MAN_RET_PRESS'
            WHEN 505 THEN '2B OIL_LEVEL_OK'
            WHEN 506 THEN '2B m2113.1'
            WHEN 507 THEN '2B m2113.2'
            WHEN 508 THEN '2B KLY_WIN_FLOW'
            WHEN 509 THEN '2B PT_TEMP'
            WHEN 510 THEN '2B PC_OZONE_BLOWER'
            WHEN 511 THEN '2B PC_UBC_SPEED'
            WHEN 512 THEN '2B m2113.7'
            WHEN 513 THEN '2B m2114.0'
            WHEN 514 THEN '2B m2114.1'
            WHEN 515 THEN '2B m2114.2'
            WHEN 516 THEN '2B m2114.3'
            WHEN 517 THEN '2B m2114.4'
            WHEN 518 THEN '2B m2114.5'
            WHEN 519 THEN '2B m2114.6'
            WHEN 520 THEN '2B m2114.7'
            WHEN 521 THEN '2B m2115.0'
            WHEN 522 THEN '2B m2115.1'
            WHEN 523 THEN '2B m2115.2'
            WHEN 524 THEN '2B m2115.3'
            WHEN 525 THEN '2B m2115.4'
            WHEN 526 THEN '2B m2115.5'
            WHEN 527 THEN '2B m2115.6'
            WHEN 528 THEN '2B m2115.7'
            WHEN 529 THEN '2B m2116.0'
            WHEN 530 THEN '2B m2116.1'
            WHEN 531 THEN '2B m2116.2'
            WHEN 532 THEN '2B m2116.3'
            WHEN 533 THEN '2B m2116.4'
            WHEN 534 THEN '2B m2116.5'
            WHEN 535 THEN '2B m2116.6'
            WHEN 536 THEN '2B m2116.7'
            WHEN 537 THEN '2B m2117.0'
            WHEN 538 THEN '2B m2117.1'
            WHEN 539 THEN '2B m2117.2'
            WHEN 540 THEN '2B m2117.3'
            WHEN 541 THEN '2B m2117.4'
            WHEN 542 THEN '2B m2117.5'
            WHEN 543 THEN '2B m2117.6'
            WHEN 544 THEN '2B m2117.7'
            WHEN 545 THEN '2B m2118.0'
            WHEN 546 THEN '2B m2118.1'
            WHEN 547 THEN '2B m2118.2'
            WHEN 548 THEN '2B m2118.3'
            WHEN 549 THEN '2B m2118.4'
            WHEN 550 THEN '2B m2118.5'
            WHEN 551 THEN '2B m2118.6'
            WHEN 552 THEN '2B m2118.7'
            WHEN 553 THEN '2B m2119.0'
            WHEN 554 THEN '2B m2119.1'
            WHEN 555 THEN '2B m2119.2'
            WHEN 556 THEN '2B m2119.3'
            WHEN 557 THEN '2B m2119.4'
            WHEN 558 THEN '2B m2119.5'
            WHEN 559 THEN '2B m2119.6'
            WHEN 560 THEN '2B m2119.7'
            WHEN 561 THEN '2B m2120.0'
            WHEN 562 THEN '2B m2120.1'
            WHEN 563 THEN '2B m2120.2'
            WHEN 564 THEN '2B m2120.3'
            WHEN 565 THEN '2B m2120.4'
            WHEN 566 THEN '2B m2120.5'
            WHEN 567 THEN '2B m2120.6'
            WHEN 568 THEN '2B m2120.7'
            WHEN 569 THEN '2B m2121.0'
            WHEN 570 THEN '2B m2121.1'
            WHEN 571 THEN '2B m2121.2'
            WHEN 572 THEN '2B m2121.3'
            WHEN 573 THEN '2B m2121.4'
            WHEN 574 THEN '2B m2121.5'
            WHEN 575 THEN '2B m2121.6'
            WHEN 576 THEN '2B m2121.7'
            WHEN 577 THEN '2B m2122.0'
            WHEN 578 THEN '2B m2122.1'
            WHEN 579 THEN '2B m2122.2'
            WHEN 580 THEN '2B m2122.3'
            WHEN 581 THEN '2B m2122.4'
            WHEN 582 THEN '2B m2122.5'
            WHEN 583 THEN '2B m2122.6'
            WHEN 584 THEN '2B m2122.7'
            WHEN 585 THEN '2B m2123.0'
            WHEN 586 THEN '2B m2123.1'
            WHEN 587 THEN '2B m2123.2'
            WHEN 588 THEN '2B m2123.3'
            WHEN 589 THEN '2B m2123.4'
            WHEN 590 THEN '2B m2123.5'
            WHEN 591 THEN '2B m2123.6'
            WHEN 592 THEN '2B m2123.7'
            WHEN 593 THEN '2B m2124.0'
            WHEN 594 THEN '2B m2124.1'
            WHEN 595 THEN '2B m2124.2'
            WHEN 596 THEN '2B m2124.3'
            WHEN 597 THEN '2B m2124.4'
            WHEN 598 THEN '2B m2124.5'
            WHEN 599 THEN '2B m2124.6'
            WHEN 600 THEN '2B m2124.7'
            WHEN 601 THEN '2B m2125.0'
            WHEN 602 THEN '2B m2125.1'
            WHEN 603 THEN '2B m2125.2'
            WHEN 604 THEN '2B m2125.3'
            WHEN 605 THEN '2B m2125.4'
            ELSE 'Interruption Not Specified'
        END --for the CASE


    INSERT INTO @TempQR_Master_Diverted
    SELECT
    @CountID,
    @SiteID,
    @TreatmentSystemID,
    @Barcode,
    @Label_SN,
    @SBN_2,
    @ProductCode,
    @Lot,
    @ProcessSpecID,
    @Param,
    @Param_Rev,
    @Config_Rev,
    @Scale_Rev,
    @Setup_Stat,
    @Validation_Run,
    @Received_Time,
    @Output_Time,
    @Reject_Forced,
    @Processing_Status,
    @Output_Status,
    @Interruptions,
    @db_rev,
    @OutfeedBarcode,
    @CreatedTime,
    @TrackClass,
    @Op_uname,
    @Op_rname,
    @Rec_CRC,
    @Reject_Reason,
    @Interruption_Reason

    -- Get the next record since we're not at the end
    FETCH NEXT FROM @i INTO
    @CountID,
    @SiteID,
    @TreatmentSystemID,
    @Barcode,
    @Label_SN,
    @SBN_2,
    @ProductCode,
    @Lot,
    @ProcessSpecID,
    @Param,
    @Param_Rev,
    @Config_Rev,
    @Scale_Rev,
    @Setup_Stat,
    @Validation_Run,
    @Received_Time,
    @Output_Time,
    @Reject_Forced,
    @Processing_Status,
    @Output_Status,
    @Interruptions,
    @db_rev,
    @OutfeedBarcode,
    @CreatedTime,
    @TrackClass,
    @Op_uname,
    @Op_rname,
    @Rec_CRC
END -- for the BEGIN associated with the WHILE @@FETCH_STATUS above

CLOSE @i
DEALLOCATE @i

DECLARE @TempQR_Sub TABLE(
    CountID BIGINT,
    SiteID INT,
    TreatmentSystemID INT,
    EBeamSubsystemID INT,
    Barcode VARCHAR(200),
    Pass INT,
    ProductSide INT,
    ProductOrientation INT,
    ENTER_UBC_TIME DATETIME,
    EXIT_UBC_TIME DATETIME,
    CONFIG_REV INT,
    SCALE_REV INT,
    ProcessSpecID INT,
    ProcessSpecRev INT,
    DOSE_FAMILY INT,
    DOSE_FAMILY_REV INT,
    RECIPE INT,
    RECIPE_REV INT,
    PARAM_SET INT,
    PARAM_SET_REV INT,
    PRESET_UBC_SPEED DECIMAL(7,3),
    ENERGY_MIN DECIMAL(5,3),
    ENERGY_AVG DECIMAL(5,3),
    ENERGY_MAX DECIMAL(5,3),
    AVE_BEAM_MIN DECIMAL(5,1),
    AVE_BEAM_AVG DECIMAL(5,1),
    AVE_BEAM_MAX DECIMAL(5,1),
    SCAN_L_MIN DECIMAL(7,3),
    SCAN_L_AVG DECIMAL(7,3),
    SCAN_L_MAX DECIMAL(7,3),
    SCAN_R_MIN DECIMAL(7,3),
    SCAN_R_AVG DECIMAL(7,3),
    SCAN_R_MAX DECIMAL(7,3),
    UBC_SPEED_MIN DECIMAL(7,3),
    UBC_SPEED_AVG DECIMAL(7,3),
    UBC_SPEED_MAX DECIMAL(7,3),
    DOSE_VARIATION_FACTOR_MIN DECIMAL(4,3),
    DOSE_VARIATION_FACTOR_AVG DECIMAL(4,3),
    DOSE_VARIATION_FACTOR_MAX DECIMAL(4,3),
    DOSE_MIN DECIMAL(7,3),
    DOSE_AVG DECIMAL(7,3),
    DOSE_MAX DECIMAL(7,3),
    CreatedTime DATETIME,
    TrackClass VARCHAR(50),
    TrackTime DATETIME,
    Op_uname VARCHAR(255),
    Op_rname VARCHAR(255),
    Rec_CRC INT
)

--Now for QR_Sub table
INSERT INTO @TempQR_Sub
SELECT
QR_Sub.CountID,
QR_Sub.SiteID,
QR_Sub.TreatmentSystemID,
QR_Sub.EBeamSubsystemID,
QR_Sub.Barcode,
QR_Sub.Pass,
QR_Sub.ProductSide,
QR_Sub.ProductOrientation,
QR_Sub.ENTER_UBC_TIME,
QR_Sub.EXIT_UBC_TIME,
QR_Sub.CONFIG_REV,
QR_Sub.SCALE_REV,
QR_Sub.ProcessSpecID,
QR_Sub.ProcessSpecRev,
QR_Sub.DOSE_FAMILY,
QR_Sub.DOSE_FAMILY_REV,
QR_Sub.RECIPE,
QR_Sub.RECIPE_REV,
QR_Sub.PARAM_SET,
QR_Sub.PARAM_SET_REV,
QR_Sub.PRESET_UBC_SPEED,
QR_Sub.ENERGY_MIN,
QR_Sub.ENERGY_AVG,
QR_Sub.ENERGY_MAX,
QR_Sub.AVE_BEAM_MIN,
QR_Sub.AVE_BEAM_AVG,
QR_Sub.AVE_BEAM_MAX,
QR_Sub.SCAN_L_MIN,
QR_Sub.SCAN_L_AVG,
QR_Sub.SCAN_L_MAX,
QR_Sub.SCAN_R_MIN,
QR_Sub.SCAN_R_AVG,
QR_Sub.SCAN_R_MAX,
QR_Sub.UBC_SPEED_MIN,
QR_Sub.UBC_SPEED_AVG,
QR_Sub.UBC_SPEED_MAX,
QR_Sub.DOSE_VARIATION_FACTOR_MIN,
QR_Sub.DOSE_VARIATION_FACTOR_AVG,
QR_Sub.DOSE_VARIATION_FACTOR_MAX,
QR_Sub.DOSE_MIN,
QR_Sub.DOSE_AVG,
QR_Sub.DOSE_MAX,
QR_Sub.CreatedTime,
QR_Sub.TrackClass,
QR_Sub.TrackTime,
QR_Sub.Op_uname,
QR_Sub.Op_rname,
QR_Sub.Rec_CRC

FROM QR_Sub

JOIN QR_Master
ON QR_Master.Barcode = QR_Sub.Barcode AND
   QR_Master.RECEIVED_TIME < QR_Sub.ENTER_UBC_TIME AND
   QR_Master.OUTPUT_TIME > QR_Sub.EXIT_UBC_TIME
WHERE
QR_Master.SBN = @SBN

----------------------------------------------------------------------------------
-- Done Getting all Quality Records associated with a particular SBN for later use
----------------------------------------------------------------------------------


----------------------------------------------------------------------------------
--Get EBeamSubsystem data associated with a particular SBN
----------------------------------------------------------------------------------
DECLARE @UBC_A_Speed_Tol VARCHAR(20)
DECLARE @UBC_B_Speed_Tol VARCHAR(20)

DECLARE @TempEBeamSubsystem TABLE(
    TempEBeamSubsystemID INT,
    Sterilizer_Alias VARCHAR(50),
    Recipe INT,
    Recipe_Rev INT,
    Param_Set INT,
    Param_Set_Rev INT,
    Preset_UBC_Speed DECIMAL(7,3),
    Config_Rev INT,
    Scale_Rev INT,
    [Description] VARCHAR(255),
    Ave_Beam_I_Set VARCHAR(20),
    Scan_I_L_Set VARCHAR(20),
    Scan_I_R_Set VARCHAR(20),
    Ave_Beam_I_Tol VARCHAR(20),
    Scan_I_L_Tol VARCHAR(20),
    Scan_I_R_Tol VARCHAR(20),
    UBC_Speed_Tol VARCHAR(20)
)


INSERT INTO @TempEBeamSubsystem
SELECT DISTINCT
tqrs.EBeamSubsystemID AS TempEBeamSubsystemID,
IniInformation.ini_value AS Sterilizer_Alias,
tqrs.Recipe,            --From @TempQR_Sub
tqrs.Recipe_Rev,        --From @TempQR_Sub
tqrs.Param_Set,         --From @TempQR_Sub
tqrs.Param_Set_Rev,     --From @TempQR_Sub
tqrs.Preset_UBC_Speed,  --From @TempQR_Sub
tqrs.Config_Rev,        --From @TempQR_Sub
tqrs.Scale_Rev,         --From @TempQR_Sub
Param_List.Description,

Param_Set.tag111 AS Ave_Beam_I_Set,
-- Scan_I_Left_Set
(CAST(Param_Set.tag33 AS DECIMAL(6,2)) + --tag33 is scan_i
                          CAST(Param_Set.tag34 AS DECIMAL(6,2))) AS Scan_I_L_Set,  --tag34 is offset
-- Scan_I_Right_Set
(-1*(CAST(Param_Set.tag33 AS DECIMAL(6,2))) + --tag33 is scan_i
                           CAST(Param_Set.tag34 AS DECIMAL(6,2))) AS Scan_I_R_Set, --tag34 is offset
Param_Tol.tag99 AS Ave_Beam_I_Tol,
Param_Tol.tag33 AS Scan_I_L_Tol,
Param_Tol.tag34 AS Scan_I_R_Tol,
Param_Tol.Tag3 AS UBC_Speed_Tol

FROM @TempQR_Sub tqrs

JOIN Param_List
ON tqrs.EBeamSubsystemID = Param_List.Sterilizer AND
   tqrs.Param_Set        = Param_List.Param AND
   tqrs.Param_Set_Rev    = Param_List.Revision

JOIN @TempQR_Master tqrm --Have to alias this table or the next line won't work
ON tqrm.Barcode = tqrs.Barcode AND
   tqrm.SBN     = @SBN

JOIN Param_Set
ON tqrs.Param_Set        = Param_Set.Param AND
   tqrs.Param_Set_Rev    = Param_Set.Revision AND
   tqrs.EBeamSubsystemID = Param_Set.Sterilizer

JOIN Param_Tol
ON tqrs.Param_Set        = Param_Tol.Param AND
   tqrs.Param_Set_Rev    = Param_Tol.Revision AND
   tqrs.EBeamSubsystemID = Param_Tol.Sterilizer

JOIN IniInformation
ON tqrs.EBeamSubsystemID        = IniInformation.sterilizer AND
   IniInformation.ini_parameter = 'sterilizer_alias'

WHERE tqrs.Param_Set          = tqrs.Param_Set AND
      tqrs.Param_Set_Rev      = tqrs.Param_Set_Rev AND
      tqrs.EBeamSubsystemID   = tqrs.EBeamSubsystemID
----------------------------------------------------------------------------------
--Done getting EBeamSubsystem data associated with an SBN
----------------------------------------------------------------------------------
SELECT * FROM @TempEBeamSubsystem

----------------------------------------------------------------------------------
--Get the Process Spec associated with a particular SBN
----------------------------------------------------------------------------------

DECLARE @TempArticle TABLE(
    Article INT,
    Revision INT,
    Issue INT,
    Sterilizer TINYINT,
    Customer INT,
    [Description] VARCHAR(255),
    Current_Rev BIT,
    Op_uname VARCHAR(255),
    Op_rname VARCHAR(255),
    CreatedTime DATETIME,
    TrackClass VARCHAR(50),
    TrackTime DATETIME,
    Rec_CRC INT,
    Instructions VARCHAR(255),
    Dose_Family INT,
    Recipe INT,
    [Enabled] INT,
    [LENGTH] DECIMAL(4,0),
    Width DECIMAL(7,3),
    Thickness DECIMAL(7,3),
    Mass DECIMAL(7,3),
    QA_Rec BIT,
    Load_to_DT BIT,
    WildCard BIT,
    Ref_Process_Dose DECIMAL(6,2),
    Ref_Min_Dose DECIMAL(6,2),
    Ref_Max_Dose DECIMAL(6,2),
    Max_Allowed_Dose DECIMAL(6,2),
    Ref_Nom_Dose_1 DECIMAL(6,2),
    Ref_Nom_Dose_2 DECIMAL(6,2),
    Timestamp_Window DECIMAL(7,3),
    Use_Timestamp BIT,
    Force_QA BIT,
    Rev_Comment VARCHAR(255),
    DF_Revs BIT,
    Number_of_Passes INT,
    BoxOrientation INT,
    MaxUBCSpeedScalling DECIMAL(5,3),
    AddDosesFromBothSides BIT
)

IF (SELECT COUNT(*) FROM @TempQR_Master) = 0
BEGIN
    --If @TempQR_Master is empty, no box has entered the system yet
    INSERT INTO @TempArticle
    SELECT DISTINCT Article.*
    FROM  SBN_UPN, UPN, Article
    WHERE SBN_UPN.SBN     = @SBN AND
          SBN_UPN.UPN     = UPN.UPN AND
          UPN.Current_Rev = 'true' AND
          UPN.Article     = Article.Article AND
          Article.Current_Rev = 'true'
END
ELSE
BEGIN
    --ELSE use the actual data found in the Quality Records
    INSERT INTO @TempArticle
    SELECT *
    FROM Article
    WHERE Article.Article    = (SELECT DISTINCT tqrm.ProcessSpecID
                                FROM @TempQR_Master tqrm
                                WHERE tqrm.SBN = @SBN) AND

          Article.Revision   = (SELECT DISTINCT tqrs.ProcessSpecRev
                                FROM @TempQR_Master tqrm, @TempQR_Sub tqrs
                                WHERE tqrm.Barcode = tqrs.Barcode AND
                                      tqrm.SBN = @SBN) AND

          Article.Sterilizer = (SELECT DISTINCT tqrm.TreatmentSystemID
                                FROM @TempQR_Master tqrm
                                WHERE tqrm.SBN = @SBN) AND

          Article.Issue      = 1
END

----------------------------------------------------------------------------------
--Done getting the Process Spec associated with a particular SBN
----------------------------------------------------------------------------------


----------------------------------------------------------------------------------
--Get the Dose Family associated with a particular SBN
----------------------------------------------------------------------------------

DECLARE @TempDose_Family TABLE(
    Dose_Family INT,
    Revision INT,
    Issue INT,
    Sterilizer TINYINT,
    Customer INT,
    [Description] VARCHAR(255),
    Current_Rev BIT,
    Op_uname VARCHAR(255),
    Op_rname VARCHAR(255),
    CreatedTime DATETIME,
    TrackClass VARCHAR(50),
    TrackTime DATETIME,
    Rec_CRC INT,
    Min_Req_Dose DECIMAL(6,2),
    Process_Dose DECIMAL(6,2),
    [Enabled] INT,
    Force_QA BIT,
    Load_to_DT BIT,
    [Expiry_Date] DATETIME,
    Rev_Comment VARCHAR(255)
)

IF (SELECT COUNT(*) FROM @TempQR_Master) = 0
BEGIN
    --If @TempQR_Master is empty, no box has entered the system yet
    INSERT INTO @TempDose_Family
    SELECT DISTINCT Dose_Family.*
    FROM  @TempArticle ta, Dose_Family
    WHERE ta.Dose_Family          = Dose_Family.Dose_Family AND
          Dose_Family.Current_Rev = 'true'
END
ELSE
BEGIN

    INSERT INTO @TempDose_Family
    SELECT *
    FROM DOSE_FAMILY
    WHERE DOSE_FAMILY.Dose_Family = (SELECT DISTINCT tqrs.DOSE_FAMILY
                                     FROM @TempQR_Sub tqrs) AND

          DOSE_FAMILY.Revision    = (SELECT TOP 1 tqrs.DOSE_FAMILY_REV
                                     FROM @TempQR_Sub tqrs) AND

          DOSE_FAMILY.Sterilizer  = (SELECT DISTINCT tqrm.TreatmentSystemID
                                     FROM @TempQR_Master tqrm
                                     WHERE tqrm.SBN = @SBN) AND

          DOSE_FAMILY.Issue       = 1
END

----------------------------------------------------------------------------------
--Done getting the Dose Family associated with a particular SBN
----------------------------------------------------------------------------------


----------------------------------------------------------------------------------
--Get Recipes for EBeamSubsystems associated with a particular SBN
----------------------------------------------------------------------------------

DECLARE @TempRecipe TABLE(
    Recipe INT,
    Revision INT,
    Issue INT,
    SiteID INT,
    EBeamSubsystemID TINYINT,
    [Description] VARCHAR(255),
    Current_Rev BIT,
    Op_uname VARCHAR(255),
    Op_rname VARCHAR(255),
    CreatedTime DATETIME,
    TrackClass VARCHAR(50),
    TrackTime DATETIME,
    Rec_CRC INT,
    Instructions VARCHAR(255),
    Ref_Dose_1 DECIMAL(6,2),
    Ref_Dose_2 DECIMAL(6,2),
    Double_Sided BIT,
    Ref_Conv_Speed DECIMAL(6,2),
    Ref_Dose_Ratio DECIMAL(6,3),
    Param_Set INT,
    [Enabled] INT,
    Ref_Energy_1 DECIMAL(4,2),
    Ref_Energy_2 DECIMAL(4,2),
    Ref_Power_1 DECIMAL(6,3),
    Ref_Power_2 DECIMAL(6,3),
    Ref_Width_1 DECIMAL(4,0),
    Ref_Width_2 DECIMAL(4,0),
    Ref_Current_1 DECIMAL(5,1),
    Ref_Current_2 DECIMAL(5,1),
    Nominal_Dose_1 DECIMAL(6,2),
    Nominal_Dose_2 DECIMAL(6,2),
    Add_ND1_ND2 BIT,
    Scaling_Dose DECIMAL(6,2),
    Max_Diff DECIMAL(5,2),
    Use_ND1 BIT,
    Use_ND2 BIT,
    Force_QA BIT,
    Load_to_DT BIT,
    Rev_Comment VARCHAR(255)
)

INSERT INTO @TempRecipe
SELECT DISTINCT
Recipe.Recipe,
Recipe.Revision,
Recipe.Issue,
Recipe.SiteID,
Recipe.EBeamSubsystemID,
Recipe.Description,
Recipe.Current_Rev,
Recipe.Op_uname,
Recipe.Op_rname,
Recipe.CreatedTime,
Recipe.TrackClass,
Recipe.TrackTime,
Recipe.Rec_CRC,
Recipe.Instructions,
Recipe.Ref_Dose_1,
Recipe.Ref_Dose_2,
Recipe.Double_Sided,
Recipe.Ref_Conv_Speed,
Recipe.Ref_Dose_Ratio,
Recipe.Param_Set,
Recipe.Enabled,
Recipe.Ref_Energy_1,
Recipe.Ref_Energy_2,
Recipe.Ref_Power_1,
Recipe.Ref_Power_2,
Recipe.Ref_Width_1,
Recipe.Ref_Width_2,
Recipe.Ref_Current_1,
Recipe.Ref_Current_2,
Recipe.Nominal_Dose_1,
Recipe.Nominal_Dose_2,
Recipe.Add_ND1_ND2,
Recipe.Scaling_Dose,
Recipe.Max_Diff,
Recipe.Use_ND1,
Recipe.Use_ND2,
Recipe.Force_QA,
Recipe.Load_to_DT,
Recipe.Rev_Comment

FROM Recipe, @TempEBeamSubsystem tebs
WHERE Recipe.EBeamSubsystemID = tebs.TempEBeamSubsystemID AND
      Recipe.Recipe           = tebs.Recipe AND
      Recipe.Revision         = tebs.Recipe_Rev AND
      Recipe.Issue            = 1

----------------------------------------------------------------------------------
--Done getting Recipes for EBeamSubsystems associated with a particular SBN
----------------------------------------------------------------------------------


----------------------------------------------------------------------------------
--Get Product (UPN) Data associated with a particular SBN
----------------------------------------------------------------------------------

DECLARE @TempUPN TABLE(
    [Description] VARCHAR(255),
    QTY INT,
    Lot VARCHAR(100),
    UPN INT,
    Instructions VARCHAR(255),
    UPN_Entry VARCHAR(50)
)


INSERT INTO @TempUPN
SELECT
SBN_UPN.Description,
SBN_UPN.QTY,
SBN_UPN.Lot,
SBN_UPN.UPN,
UPN.Instructions,
UPN.UPN_Entry

FROM SBN_UPN, UPN, @TempArticle ta
WHERE SBN_UPN.SBN         = @SBN AND
      SBN_UPN.UPN         = UPN.UPN AND
      UPN.Sterilizer      = ta.Sterilizer AND
      UPN.Current_rev     = 1 AND
      SBN_UPN.Current_rev = 1
----------------------------------------------------------------------------------
--Done Getting Product (UPN) Data associated with a particular SBN
----------------------------------------------------------------------------------


----------------------------------------------------------------------------------
--Get various quality record values associated with a particular SBN
----------------------------------------------------------------------------------
DECLARE @Company_Name VARCHAR(255)
DECLARE @Location VARCHAR(255)
DECLARE @PC_Param INT
DECLARE @PC_ParamRev INT
DECLARE @PC_Config INT
DECLARE @PC_Scale INT

--Company Name
SET @Company_Name =
(SELECT Company.Description
FROM Company, Customer, @TempArticle ta
WHERE Company.Company      = Customer.Company AND
      Company.Sterilizer   = ta.Sterilizer AND
      Company.Current_Rev  = 1 AND
      Customer.Customer    = ta.Customer AND
      Customer.Sterilizer  = ta.Sterilizer AND
      Customer.Current_Rev = 1)

--Location (Customer Name)
SET @Location =
(SELECT Customer.Description
FROM Customer, @TempArticle ta
WHERE Customer.Customer    = ta.Customer AND
      Customer.Sterilizer  = ta.Sterilizer AND
      Customer.Current_Rev = 1)

--Process Controller Param Set and Revision, Config, and Scale
SET @PC_Param    = (SELECT DISTINCT PARAM FROM @TempQR_Master)
SET @PC_ParamRev = (SELECT DISTINCT PARAM_REV FROM @TempQR_Master)
SET @PC_Config   = (SELECT DISTINCT CONFIG_REV FROM @TempQR_Master)
SET @PC_Scale    = (SELECT DISTINCT SCALE_REV FROM @TempQR_Master)

----------------------------------------------------------------------------------
--Done getting various quality record values associated with a particular SBN
----------------------------------------------------------------------------------


----------------------------------------------------------------------------------
--Get various box quantities/values associated with a particular SBN
----------------------------------------------------------------------------------
DECLARE @SBN_Description VARCHAR(255)
DECLARE @Start_SBN_Time DATETIME
DECLARE @End_SBN_Time DATETIME
DECLARE @Dose_Min DECIMAL(7,3)
DECLARE @Dose_Max DECIMAL(7,3)
DECLARE @Total_Boxes INT
DECLARE @Boxes_Not_Diverted INT
DECLARE @Boxes_Processed INT
DECLARE @Boxes_Not_Processed INT
DECLARE @Non_RML_Boxes INT
DECLARE @RML_Boxes INT
DECLARE @Report_Rev INT
DECLARE @Report_Uname VARCHAR(255)
DECLARE @Report_Rname VARCHAR(255)

--SBN Description
SET @SBN_Description = (SELECT SBN.Description
                        FROM SBN
                        WHERE SBN.SBN         = @SBN AND
                              SBN.Current_Rev = 'true')

--Start Process Time
SET @Start_SBN_Time = (SELECT MIN(RECEIVED_TIME) FROM @TempQR_Master)

--End Process Time
SET @End_SBN_Time = (SELECT MAX(OUTPUT_TIME) FROM @TempQR_Master)

--Min (of Min) Dose for Accepted Boxes (Does this include RML (Dosimetry) Boxes?)
SET @Dose_Min = (
SELECT MIN(tqrs.DOSE_MIN) FROM @TempQR_Sub tqrs, @TempQR_Master tqrm
WHERE tqrm.Output_Status = 0 AND
      tqrm.Barcode = tqrs.Barcode)

--Max (of Max) Dose for Accepted Boxes (Does this include RML (Dosimetry) Boxes?)
SET @Dose_Max =
(SELECT MAX(tqrs.DOSE_MAX) FROM @TempQR_Sub tqrs, @TempQR_Master tqrm
WHERE tqrm.Output_Status = 0 AND
      tqrm.Barcode = tqrs.Barcode)

--Total Number of Boxes in SBN
--SUM the Qty for each Product (UPN) in the SBN
SET @Total_Boxes = (SELECT SUM(QTY) FROM SBN_UPN WHERE SBN=@SBN AND Current_Rev = 1)

--Number of Boxes Processed
SET @Boxes_Processed =
(SELECT COUNT(*) FROM @TempQR_Master tqrm)

--Number of Boxes Not Diverted (Includes RML's as long as they were treated OK)
SET @Boxes_Not_Diverted =
(SELECT COUNT(*) FROM @TempQR_Master tqrm
WHERE tqrm.Output_Status = 0)

--Number of Boxes Not Processed (Includes RML's)
SET @Boxes_Not_Processed = (@Total_Boxes) - (@Boxes_Processed)

--Number of Non-RML Boxes
SET @Non_RML_Boxes =
(SELECT COUNT(*) FROM @TempQR_Master tqrm
WHERE tqrm.Validation_Run = 0)

--Number of RML Boxes
SET @RML_Boxes =
(SELECT COUNT(*) FROM @TempQR_Master tqrm
WHERE tqrm.Validation_Run <> 0)


IF (@Input_Report_Rev = -1) --Create a new report, don't display an existing one
BEGIN
    --Check to see if any revisions of this report exist and increment for this report

    SELECT
    @Report_Rev = MAX(Report_Revision)
    FROM
    Report_Summary_v2  --Version 2
    WHERE
    SBN         = @SBN

    IF (@Report_Rev IS NULL)
    BEGIN
        SET @Report_Rev = 1
    END
    ELSE
    BEGIN
        SET @Report_Rev = @Report_Rev + 1
    END

    SET @Report_Uname = @userlogin

    SELECT @Report_Rname = fullname
    FROM Users
    WHERE login = @userlogin

    SET @Report_Date = GETDATE()

END
ELSE
BEGIN
    --Get details of existing report from Report_Summary_v2 table

    SET @Report_Rev = @Input_Report_Rev

    SELECT @Rev_Comment = Rev_Comment
    FROM Report_Summary_v2  --Version 2
    WHERE SBN = @SBN AND Report_Revision = @Input_Report_Rev

    SELECT @Batch_Report_Ver = Batch_Report_Version
    FROM Report_Summary_v2  --Version 2
    WHERE SBN = @SBN AND Report_Revision   = @Input_Report_Rev

    SELECT @Report_Uname = user_login
    FROM Report_Summary_v2  --Version 2
    WHERE SBN = @SBN AND Report_Revision   = @Input_Report_Rev

    SELECT @Report_Rname = user_fullname
    FROM Report_Summary_v2  --Version 2
    WHERE SBN = @SBN AND Report_Revision   = @Input_Report_Rev

    SELECT @Report_Date = CreatedTime
    FROM Report_Summary_v2  --Version 2
    WHERE SBN = @SBN AND Report_Revision   = @Input_Report_Rev

END


DECLARE @TempSBNInfo TABLE(
SBN BIGINT,
SBN_Description VARCHAR(255),
Company_Name VARCHAR(255),
Location VARCHAR(255),
PC_Param INT,
PC_ParamRev INT,
PC_Config INT,
PC_Scale INT,
Start_SBN_Time DATETIME,
End_SBN_Time DATETIME,
Dose_Min DECIMAL(7,3),
Dose_Max DECIMAL(7,3),
Total_Boxes INT,
Boxes_Not_Diverted INT,
Boxes_Processed INT,
Boxes_Not_Processed INT,
Non_RML_Boxes INT,
RML_Boxes INT,
--@Non_RML_Boxes_Diverted
Rev_Comment VARCHAR(255),
Report_Rev INT,
Batch_Report_Ver VARCHAR(25),
Report_Uname VARCHAR(255),
Report_Rname VARCHAR(255),
Report_Date DATETIME,
Detailed_Report BIT,
From_To_Report BIT
)

INSERT INTO @TempSBNInfo
SELECT
@SBN AS SBN,
@SBN_Description AS SBN_Description,
@Company_Name AS Company_Name,
@Location AS Location,
@PC_Param AS PC_Param,
@PC_ParamRev AS PC_ParamRev,
@PC_Config AS PC_Config,
@PC_Scale AS PC_Scale,
@Start_SBN_Time AS Start_SBN_Time,
@End_SBN_Time AS End_SBN_Time,
@Dose_Min AS Dose_Min,
@Dose_Max AS Dose_Max,
@Total_Boxes AS Total_Boxes,
@Boxes_Not_Diverted AS Boxes_Not_Diverted,
@Boxes_Processed AS Boxes_Processed,
@Boxes_Not_Processed AS Boxes_Not_Processed,
@Non_RML_Boxes AS Non_RML_Boxes,
@RML_Boxes AS RML_Boxes,
--@Non_RML_Boxes_Diverted
@Rev_Comment AS Rev_Comment,
@Report_Rev AS Report_Rev,
@Batch_Report_Ver AS Batch_Report_Ver,
@Report_Uname AS Report_Uname,
@Report_Rname AS Report_Rname,
@Report_Date AS Report_Date,
0 AS Detailed_Report,
0 AS From_To_Report

----------------------------------------------------------------------------------
--Done getting various box quantities/values associated with a particular SBN
----------------------------------------------------------------------------------


----------------------------------------------------------------------------------
--Add report info to Report_Summary_v2 table if generating a new report
----------------------------------------------------------------------------------

IF (@Input_Report_Rev = -1) --Create a new report, don't display an existing one
BEGIN

    DECLARE @fullname VARCHAR(128)
    DECLARE @CURRENT_DATE DATETIME

    SET @CURRENT_DATE = GETDATE()
    SET @fullname = (SELECT fullname
                     FROM users
                     WHERE [login] = @userlogin)

    INSERT INTO Report_Summary_v2
    SELECT
    @SBN,
    @Report_Rev,
    @Rev_Comment,
    @Batch_Report_Ver,
    @userlogin,
    @fullname,
    'Created',
    @CURRENT_DATE,
    @CURRENT_DATE,
    40

END


INSERT INTO TempQR_Master SELECT * FROM @TempQR_Master
INSERT INTO TempQR_Sub SELECT * FROM @TempQR_Sub
INSERT INTO TempArticle SELECT * FROM @TempArticle
INSERT INTO TempDose_Family SELECT * FROM @TempDose_Family
INSERT INTO TempEBeamSubsystem SELECT * FROM @TempEBeamSubsystem
INSERT INTO TempRecipe SELECT * FROM @TempRecipe
INSERT INTO TempUPN SELECT * FROM @TempUPN
INSERT INTO TempSBNInfo SELECT * FROM @TempSBNInfo
INSERT INTO TempQR_Master_Diverted SELECT * FROM @TempQR_Master_Diverted