CREATE OR REPLACE PROCEDURE Generate_XSection_Analysis( 
    pRunsetCode IN NUMBER ) 
IS 
    vAPlate        APLATE.APLATE_CODE%TYPE; 
    vSequence      INTEGER := 0; 
    vAvgActivity   NUMBER; 
    vMaxActivity   NUMBER; 
    vMinActivity   NUMBER; 
    vStdDev        NUMBER; 
    vBogusCount    NUMBER; 
    vScoreCount    INTEGER; 
    vScoreRate     NUMBER; 
    vEmptyWells    NUMBER; 
    vTotalPlates   INTEGER; 
    vScoreResponse CHAR(1); 
    vAssayCode     ASSAY.ASSAY_CODE%TYPE; 
    /* Parameterized cursor: pRow and pCol are local variables. */ 
    /* xsection_score_count filled earlier in the procedure. */ 
    CURSOR cResults (pRow IN CHAR, pCol IN CHAR) IS 
      SELECT * 
        FROM xsection_score_count 
        WHERE runset_code = pRunsetCode 
          AND row_letter = pRow 
          AND column_number = pCol; 
 /* xsection_score filled externally and prior to running xsection. */ 
    CURSOR cAssay  IS 
      SELECT * 
        FROM xsection_score 
        WHERE assay_code = vAssayCode 
        ORDER BY rank DESC; 
    CURSOR cAwell IS 
      SELECT * 
      FROM AWELL 
      WHERE APLATE_CODE = vAPlate 
      ORDER BY ROW_LETTER, COLUMN_NUMBER; 
    CURSOR cScore(vRow IN CHAR, vCol IN CHAR) IS 
      SELECT DISTINCT score 
        FROM AWELL 
        WHERE runset_code = pRunsetCode 
          AND row_letter = vRow 
          AND column_number = vCol; 

  BEGIN 
     Calculate_Runset(pRunsetCode);

    /* Remove prior analysis for this runset. */ 
    DELETE FROM xsection_score_count 
      WHERE RUNSET_CODE = pRunsetCode; 
    DELETE FROM xsection_analysis 
      WHERE RUNSET_CODE = pRUnsetCode; 
    /* Generate the scores; otherwise, this is useless. */ 
    score_runset(pRunsetCode); 
    SELECT COUNT(*) 
      INTO vTotalPlates 
      FROM aplate 
      WHERE runset_code = pRunsetCode; 
    SELECT aplate_code 
      INTO vAPlate 
      FROM aplate 
      WHERE runset_code = pRunsetCode 
        AND runset_order = 1; 
    /* Loop over all wells on a typical plate. */ 
    /* The first plate in the runset is used as a template */ 
    /* for the xsections to allow extending the */ 
    /* the technique to arbitrary-sized plates. */ 
    <<plate_loop>> 
    FOR vAwell in cAwell LOOP 
      /* Sum scores across xsection. */ 
      <<score_loop>> 
      FOR vScore IN cScore(vAwell.row_letter,vAwell.column_number) LOOP 
        SELECT COUNT(score) 
          INTO vScoreCount 
          FROM awell 
          WHERE runset_code = pRunsetCode 
            AND row_letter = vAwell.row_letter 
            AND column_number = vAwell.column_number 
            AND score = vScore.score 
            AND bogus_flag IS NULL          /* valid data only */ 
            AND sample_code IS NOT NULL;    /* filled wells only */ 
          INSERT INTO xsection_score_count 
            ( runset_code, 
              row_letter, 
              column_number, 
              score, 
              score_count, 
              score_rate ) 
            VALUES ( pRunsetCode, 
                     vAwell.row_letter, 
                     vAwell.column_number, 
                     vScore.score, 
                     vScoreCount, 
                     100.0*VscoreCount/vTotalPlates 
           ); 
        END LOOP score_loop; 
      SELECT assay_code 
        INTO vAssayCode 
        FROM runset 
        WHERE runset_code = pRunsetCode; 
      /* Nested loop does prioritized assignment of score. */ 
      <<results_loop>> 
 FOR vResults IN cResults(vAwell.row_letter,vAwell.column_number) LOOP 
        vScoreResponse := NULL; 
        <<assay_loop>> 
        FOR vAssay IN cAssay LOOP 
          IF vResults.score = vAssay.score 
              AND vResults.Score_Rate > vAssay.threshold THEN 
            vScoreResponse := vAssay.response; 
          END IF; 
        END LOOP assay_loop; 
      END LOOP results_loop; 
      SELECT AVG(calc_value), 
             MAX(calc_value), 
             MIN(calc_value), 
             STDDEV(calc_value), 
             COUNT(bogus_flag) 
        INTO vAvgActivity, 
             vMaxActivity, 
             vMinActivity, 
             vStdDev, 
             vBogusCount 
        FROM awell 
        WHERE runset_code = pRunsetCode 
          AND row_letter = vAwell.row_letter 
          AND column_number = vAwell.column_number; 
      SELECT COUNT(*) 
        INTO vEmptyWells 
        FROM awell 
       WHERE runset_code = pRunsetCode 
         AND row_letter = vAwell.row_letter 
         AND column_number = vAwell.column_number 
         AND sample_code IS NULL; 
      vSequence := vSequence + 1; 
      INSERT INTO XSECTION_ANALYSIS 
          ( RUNSET_CODE, 
            ROW_LETTER, 
            COLUMN_NUMBER, 
            RUNSET_ORDER, 
            WELL_TYPE, 
            avg_VALUE, 
            max_VALUE, 
            min_VALUE, 
            std_dev, 
            bogus_count, 
            pct_bogus, 
            empty_count, 
            pct_empty, 
            SCORE, 
            COLOR_ID, 
            ALERT ) 
          VALUES ( 
            pRunsetCode, 
            vAwell.ROW_LETTER, 
            vAwell.COLUMN_NUMBER, 
            vSequence, 
            vAwell.WELL_TYPE, 
            vAvgActivity, 
            vMaxActivity, 
            vMinActivity, 
            vStdDev, 
            vBogusCount,                    /* invalid wells */
            100.0*vBogusCount/vTotalPlates, 
            vEmptyWells,                    /* empty wells */
            100.0*vEmptyWells/vTotalPlates, 
            vScoreResponse, 
            NULL, 
            NULL 
          ); 
    END LOOP plate_loop; 
    /* Handles bogusing here: */
    /* Sets alert to true if the bogus_rate is too high. */ 
    UPDATE xsection_analysis 
      SET alert = 'T' 
      WHERE runset_code = pRunsetCode 
      AND pct_bogus > (SELECT bogus_threshold 
                          FROM assay 
                          WHERE assay_code = vAssayCode); 
    COMMIT; 
  /* Have an update here that sets the color. */ 
  END Generate_XSection_Analysis; 
/
SHOW ERRORS PROCEDURE Generate_Xsection_Analysis