/* Formatted on 8/15/2018 8:56:52 AM (QP5 v5.265.14096.38000) */
SET DEFINE OFF;

CREATE OR REPLACE TRIGGER SATURN.TBRCBRQ_POST_INSERT_ROW
   AFTER INSERT
   ON "TAISMGR"."TBRCBRQ"
   FOR EACH ROW
BEGIN
   DECLARE
      w_one_up_no      NUMBER (9) := 0;
      w_gjbpdft_row    gjbpdft%ROWTYPE;
      student_id       VARCHAR2 (9);
      billing_rule     VARCHAR2 (30);
      billing_date     VARCHAR2 (30);
      job_printer      VARCHAR2 (30);
      gtvsdax_status   gtvsdax.gtvsdax_internal_code%TYPE;

      --cursor to grab the trigger status from gtvsdax
      CURSOR get_gtvsdax_status
      IS
         SELECT gtvsdax_external_code
           FROM gtvsdax
          WHERE     gtvsdax_internal_code = 'SCHEDINV'
                AND gtvsdax_internal_code_group = 'NOSLEEP';


      PROCEDURE load_gjbprun
      IS
         CURSOR one_up_c
         IS
            SELECT gjbpseq.NEXTVAL FROM DUAL;

         CURSOR gjbpdft_c
         IS
            SELECT *
              FROM gjbpdft
             WHERE     gjbpdft_job = 'TSRCBIL'
                   AND gjbpdft_jprm_code = 'TRIGGER'
                   AND gjbpdft_user_id = 'NOSLEEP';

         --cursor to grab the students ID number
         CURSOR spriden_c
         IS
            SELECT spriden_id
              FROM spriden
             WHERE     spriden_pidm = :NEW.tbrcbrq_pidm
                   AND spriden_change_ind IS NULL;


         --cursor to grab the billing rule from gtvsdax
         CURSOR gtvsdax_rule_c
         IS
            SELECT gtvsdax_external_code
              FROM gtvsdax
             WHERE     gtvsdax_internal_code = :NEW.tbrcbrq_term_code
                   AND gtvsdax_translation_code = 'RULE';

         --cursor to grab the billing date from gtvsdax
         CURSOR gtvsdax_date_c
         IS
            SELECT gtvsdax_external_code
              FROM gtvsdax
             WHERE     gtvsdax_internal_code = :NEW.tbrcbrq_term_code
                   AND gtvsdax_translation_code = 'DATE';
      BEGIN
         OPEN one_up_c;

         FETCH one_up_c INTO w_one_up_no;

         CLOSE one_up_c;

         BEGIN
            DELETE FROM gjbprun
                  WHERE     gjbprun_job = 'TSRCBIL'
                        AND gjbprun_one_up_no = w_one_up_no;
         END;


         --fetch the students ID
         OPEN spriden_c;

         FETCH spriden_c INTO student_id;

         CLOSE spriden_c;

         --fetch the rule
         OPEN gtvsdax_rule_c;

         FETCH gtvsdax_rule_c INTO billing_rule;

         CLOSE gtvsdax_rule_c;

         --fetch the billing date
         OPEN gtvsdax_date_c;

         FETCH gtvsdax_date_c INTO billing_date;

         CLOSE gtvsdax_date_c;

         OPEN gjbpdft_c;

         LOOP
            FETCH gjbpdft_c INTO w_gjbpdft_row;

            EXIT WHEN gjbpdft_c%NOTFOUND;

            --loop below takes the settings from the actual bill
            --request in TBRCBRQ and overrides NOSLEEP's default parameters
            --in gjbpdft so that the final parameter set placed in gjbprun
            --will launch a no-sleep instance of TSRCBIL that catches
            --the new bill request

            IF w_gjbpdft_row.gjbpdft_number = '01'
            THEN
               w_gjbpdft_row.gjbpdft_value := :NEW.tbrcbrq_term_code;
            END IF;

            IF w_gjbpdft_row.gjbpdft_number = '04'
            THEN
               w_gjbpdft_row.gjbpdft_value := student_id;
            END IF;

            IF w_gjbpdft_row.gjbpdft_number = '05'
            THEN
               w_gjbpdft_row.gjbpdft_value := :NEW.tbrcbrq_printer;
            END IF;

            IF w_gjbpdft_row.gjbpdft_number = '12'
            THEN
               w_gjbpdft_row.gjbpdft_value := billing_rule;
            END IF;

            --tsrcbil.pc generates an error when sysdate comes from the stored parm set
            --the same does not occur for parm 2 address sel date
            IF w_gjbpdft_row.gjbpdft_number = '17'
            THEN
               w_gjbpdft_row.gjbpdft_value :=
                  TO_CHAR (TO_DATE (SYSDATE, 'DD-MON-YYYY'));
            END IF;

            IF w_gjbpdft_row.gjbpdft_number = '18'
            THEN
               w_gjbpdft_row.gjbpdft_value := billing_date;
            END IF;

            --inserts the row value for this pass through the loop

            BEGIN
               INSERT INTO gjbprun (gjbprun_job,
                                    gjbprun_one_up_no,
                                    gjbprun_number,
                                    gjbprun_activity_date,
                                    gjbprun_value)
                    VALUES ('TSRCBIL',
                            w_one_up_no,
                            w_gjbpdft_row.gjbpdft_number,
                            SYSDATE,
                            w_gjbpdft_row.gjbpdft_value);
            END;
         END LOOP;

         CLOSE gjbpdft_c;
      END load_gjbprun;
   BEGIN
      --fetch the gtvsdax status
      OPEN get_gtvsdax_status;

      FETCH get_gtvsdax_status INTO gtvsdax_status;

      CLOSE get_gtvsdax_status;

      --   IF :new.tbrcbrq_printer = 'sc_burp09' or :new.tbrcbrq_printer = 'sc_onecard_cir210_1G' or :new.tbrcbrq_printer = 'sc_onecard_cir210_1' or :new.tbrcbrq_printer = 'sc_oitp10' or :new.tbrcbrq_printer = 'sc_bursar_hp4050_1' or :new.tbrcbrq_printer = 'sc_bursar_hp4050_2' or :new.tbrcbrq_printer = 'sc_bursar_hp4050_3' or :new.tbrcbrq_printer = 'sc_bursar_hp4050_4' or :new.tbrcbrq_printer = 'sc_bursar_hp4000_1' or :new.tbrcbrq_printer = 'sc_bursar_hp5_1' or :new.tbrcbrq_printer = 'sc_finaid_hp4000_1' or :new.tbrcbrq_printer = 'sc_onecard_hp2200_1' or :new.tbrcbrq_printer = 'sc_regist_hp4050_1' or :new.tbrcbrq_printer = 'sc_regist_hp4050_2' or :new.tbrcbrq_printer = 'sc_regp03' or :new.tbrcbrq_printer = 'sc_regp04' or :new.tbrcbrq_printer = 'sc_resp05' or :new.tbrcbrq_printer = 'sc_stdsupsr_hp2100_1' THEN

      --translate problem queues to correct printcap entry

      job_printer := :NEW.tbrcbrq_printer;

      IF job_printer = 'sc_onecard_cir210_1G'
      THEN
         job_printer := 'sc_onecard_cir210_1';
      END IF;

      IF gtvsdax_status = 'Y'
      THEN
         load_gjbprun;

         baninst1.gp_submit_gjajobs ('TSRCBIL',
                                     'C',
                                     '',
                                     '',
                                     w_one_up_no || '04',
                                     --         'DATABASE',
                                     --     :new.tbrcbrq_printer,
                                     job_printer,
                                     -- line below is for evisions form
                                     'SCHEDBILL',
                                     '',
                                     '',
                                     '',
                                     w_one_up_no);
      --   END IF;
      END IF;
   END;
--         BEGIN
--            delete
--              from tbrcbrq
--             where tbrcbrq_pidm       = :new.tbrcbrq_pidm
--               and tbrcbrq_printer    = :new.tbrcbrq_printer;
--         END;

END;
/

SHOW ERRORS;