SELECT spriden_id as ID, 
       spriden_first_name as F_Name, 
       spriden_last_name as L_Name,
       (SELECT MAX(goremal_email_address)
          FROM goremal
         WHERE goremal_pidm = tbraccd_pidm
           AND goremal_status_ind = 'A'
           AND goremal_preferred_ind = 'Y') Email,
       tbraccd_term_code as refund_term,
       NVL((SELECT MAX('Y')
          FROM tbraccd i
         WHERE i.tbraccd_pidm = otbr.tbraccd_pidm
           AND i.tbraccd_term_code = otbr.tbraccd_term_code
           AND i.tbraccd_detail_code IN
               ( SELECT tbbdetc_detail_code
                   FROM tbbdetc
                  WHERE tbbdetc_tiv_ind = 'Y') ), 'N') TIV_in_Refund_Term,
       tbraccd_detail_code as refund_code,
       tbraccd_tran_number as refund_tran,
       TBRACCD_AMOUNT as refund_amount,
 --      TBRACCD_EFFECTIVE_DATE as refund_inv_date,
       TBRACCD_DOCUMENT_NUMBER as refund_inv,
       fabinck_bank_code as Bank,
       fabinck_check_num as Check_Num,
       (SELECT SUM (fabinck_net_amt)
          FROM fabinck i
         WHERE i.fabinck_check_num = o.fabinck_check_num) Total_Check_Amt,
       fabinck_check_date as Check_Date,
       (SELECT sgbstdn_camp_code
          FROM sgbstdn o
         WHERE sgbstdn_pidm = tbraccd_pidm
           AND sgbstdn_term_code_eff = 
              (SELECT MAX(i.sgbstdn_term_code_eff)
                 FROM sgbstdn i
                WHERE i.sgbstdn_pidm = o.sgbstdn_pidm) ) CAMPUS
  FROM fabinck o, tbraccd otbr, spriden
WHERE  spriden_pidm = tbraccd_pidm
   AND TRUNC(fabinck_check_date) BETWEEN '&Start_Date' AND '&End_Date'
     --:user; /*Check Cut Month - First Day and Last Day*/
   AND spriden_change_ind IS NULL
   AND fabinck_invh_code = tbraccd_document_number
   AND tbraccd_detail_code IN 
       (SELECT tbbdetc_detail_code 
          FROM tbbdetc
         WHERE tbbdetc_dcat_code = 'RFD')
   AND fabinck_check_num NOT LIKE '!%'
   AND fabinck_check_num NOT LIKE 'Y%'
   AND fabinck_cancel_ind IS NULL
   AND EXISTS (SELECT 'X'
                 FROM tbraccd
                WHERE tbraccd_pidm = spriden_pidm
                  AND tbraccd_aidy_code IN ('&Aid_Year_1', '&Aid_Year_2', '&Aid_Year_3')
                  --:user;/*Enter from one-to-three aid years*/
                  AND tbraccd_detail_code IN
                      (SELECT tbbdetc_detail_code
                         FROM tbbdetc
                        WHERE tbbdetc_tiv_ind = 'Y'))
AND NVL(( SELECT sob2cmp_acad_inst
                FROM sob2cmp, sgbstdn
               WHERE sgbstdn_pidm = tbraccd_pidm
                 AND sob2cmp_camp_code = sgbstdn_camp_code
                 AND sgbstdn_term_code_eff = 
                    (SELECT MAX(sgbstdn_term_code_eff)
                       FROM sgbstdn
                      WHERE sgbstdn_pidm = tbraccd_pidm) ), 'NULL')
            LIKE UPPER(NVL('&mau_code', '%'))
            --:user;/*Choose MAU or Campus - not both */
   AND INSTRB(UPPER(NVL('&campus_list',
                        'ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890^')),
            --:user;/*Choose Campus or MAU not both*/
              NVL(( SELECT sgbstdn_camp_code
                      FROM sgbstdn
                     WHERE sgbstdn_pidm = tbraccd_pidm
                       AND sgbstdn_term_code_eff = 
                           (SELECT MAX(sgbstdn_term_code_eff)
                              FROM sgbstdn
                             WHERE sgbstdn_pidm = tbraccd_pidm) ), '^')
             ) <> 0
   AND NOT EXISTS (SELECT 'X' 
                 FROM fabbktp
                WHERE fabbktp_check_deposit_code = fabinck_check_num )
  Order by fabinck_bank_code, SPRIDEN.SPRIDEN_ID
/


