-- OBJECT TYPE:  PL/SQL
-- OBJECT NAME:  tzprnop.sql
-- AUTHOR:       David Buchannon
--
-- DESCRIPTION:  Creates glbextr entry for students that were billed on a particular 
--               date, that don't meet a set of possibe of exceptions, and had a amount due
--               greater than 0.00
--               1) student has authorized aid or memoed aid, auth and memoed
--               2) student is on a contract **
--               3) student is level exempted gr and pr **
--               4) student payment(net)/credited charges meet a percentage of the billed amount
--                        
--               ** See revision for 2008-03-24

-- DATE REVISED:  
--               Process will only look for 'F' type memos, as we only want to protect schedules 
--               and not prevent holds and late fees
-- 2008-03-24
--               Contract was removed as a protected type, Done before this date, making note of it.
--               Adding seperate parameters to control the protection of GR and PR students
--
-- 2008-10-24
--              Added an additional 'protected' group excluding students that have both the employee
--              dependent waiver and Alabama PACT for the term from the pop-sel output.
--              added a seperate list output for this group
---- 2010-09-08    M Chen 
--               Added 'T' in brmemo_srce_code check in  c_has_aid_memo cursor 

SET echo OFF;
SET verify OFF;
SET feedback OFF;
SET tab OFF;
SET serveroutput ON;


DECLARE

v_pact_code TBRACCD.TBRACCD_DETAIL_CODE%TYPE;
v_term_code varchar2(6);
v_run_mode varchar2(1):= 'A';
v_num_processed number(12):=0;
v_has_aid varchar2(1);
v_on_contract varchar2(1);
v_exempt_level_GR varchar2(1);
v_exempt_level_PR varchar2(1);
v_bill_run date;
v_special_payments tbraccd.tbraccd_amount%TYPE;
v_amount_due_threshold tbbstmt.tbbstmt_amount_due%TYPE;
v_payment_ratio number(12,10);
V_CNT NUMBER (12):=0;
v_found BOOLEAN :=FALSE;
r_pidm spriden.spriden_pidm%TYPE;
v_outline VARCHAR(2048);

--pop sel variables
v_psel_application CONSTANT glbextr.glbextr_application%TYPE := 'AR';
v_psel_selection CONSTANT glbextr.glbextr_selection%TYPE := 'SET_AR_HOLDS';
v_psel_creator_id CONSTANT glbextr.glbextr_creator_id%TYPE := 'MCCOYGC';
v_psel_user_id CONSTANT glbextr.glbextr_user_id%TYPE := 'MCCOYGC';



--select students with memo aid for the term
CURSOR c_has_aid_memo (v_pidm varchar2,v_term_code varchar2,v_expiration_date date) IS
SELECT DISTINCT tbrmemo_pidm AS r_pidm
FROM tbrmemo 
WHERE tbrmemo_pidm = v_pidm
AND tbrmemo_term_code = v_term_code
AND tbrmemo_srce_code IN  ('F', 'T')
AND trunc(tbrmemo_expiration_date) > trunc(v_expiration_date)
;

--select students with authorized aid for the term
CURSOR c_has_aid_auth (v_pidm varchar2,v_term_code varchar2) IS
SELECT DISTINCT rprauth_pidm AS r_pidm
FROM rprauth
WHERE 
rprauth_pidm = v_pidm
AND rprauth_term_code = v_term_code;

--select the students on contract for a term
--cursor is no longer called, left for reference
CURSOR c_on_contract (v_pidm varchar2,v_term_code varchar2) IS
SELECT DISTINCT tbbcstu_stu_pidm AS r_pidm
FROM tbbcstu 
WHERE tbbcstu_stu_pidm = v_pidm
AND tbbcstu_term_code = v_term_code;

--select students that are level exempted
CURSOR c_exempt_level (v_pidm varchar2,v_term_code varchar2, v_levl_code varchar2) IS
SELECT DISTINCT A.sgbstdn_pidm AS r_pidm
FROM
sgbstdn A
WHERE A.sgbstdn_pidm = v_pidm
AND A.sgbstdn_levl_code = v_levl_code
AND A.sgbstdn_term_code_eff = 
(
	SELECT max(b.sgbstdn_term_code_eff)
	FROM sgbstdn b
	WHERE
	b.sgbstdn_pidm = A.sgbstdn_pidm
	AND b.sgbstdn_term_code_eff <= v_term_code
)
;

--*********************************************************************
--select students that have both AL PACT and a dependent waiver for a term 
CURSOR c_has_pact_depd_waiver (v_pidm varchar2, v_term_code varchar2, v_pact_code varchar2) IS
SELECT DISTINCT A.tbraccd_pidm AS r_pidm
FROM tbraccd A
WHERE A.TBRACCD_PIDM = v_pidm--'2005636'
AND A.TBRACCD_DETAIL_CODE = v_pact_code--'5091'
AND A.TBRACCD_TERM_CODE = v_term_code--'200910'
AND EXISTS
(
    SELECT 'x'
    FROM tbraccd b
    WHERE b.TBRACCD_PIDM = A.TBRACCD_PIDM
    AND b.TBRACCD_TERM_CODE = A.TBRACCD_TERM_CODE
    AND b.TBRACCD_DETAIL_CODE IN ('6413','8413')
);
--*********************************************************************

--installment plan queries
CURSOR c_tbbstmt  (v_bill_run date,v_amount_due_threshold number)IS 
SELECT DISTINCT A.*,spriden.*,
(
     SELECT count(*) 
     FROM tbbstmt b 
     WHERE b.tbbstmt_pidm = A.tbbstmt_pidm 
     AND trunc(b.tbbstmt_bill_date) = trunc(A.tbbstmt_bill_date)
)  
FROM tbbstmt A
INNER JOIN spriden
ON tbbstmt_pidm = spriden_pidm
WHERE 
trunc(A.tbbstmt_bill_date) = trunc(v_bill_run)
AND A.tbbstmt_amount_due >= v_amount_due_threshold
AND spriden_change_ind IS NULL
;

--local copy of  tb_receivable.f_sum_net_amount
--specialized aspects
--1, uses entry date, and only sums payments and negative charges, - neg sign
FUNCTION f_loc_sum_net_amount_special(
 p_pidm                      tbraccd.tbraccd_pidm%TYPE,
 p_term_code                 tbraccd.tbraccd_term_code%TYPE DEFAULT NULL,
 p_detail_code               tbraccd.tbraccd_detail_code%TYPE DEFAULT NULL,
 p_dcat_code                 tbbdetc.tbbdetc_dcat_code%TYPE DEFAULT NULL,
 p_bill_date                 tbraccd.tbraccd_bill_date%TYPE DEFAULT NULL,
 p_srce_code                 tbraccd.tbraccd_srce_code%TYPE DEFAULT NULL,
 p_invoice_number            tbraccd.tbraccd_invoice_number%TYPE DEFAULT NULL,
 p_inv_number_paid           tbraccd.tbraccd_inv_number_paid%TYPE DEFAULT NULL,
 p_effective_date            tbraccd.tbraccd_effective_date%TYPE DEFAULT NULL)

RETURN NUMBER
IS
lv_sum_net_amount                 NUMBER; -- not initialized, as intended to return null if no records match
CURSOR get_sum_net_amount_c IS
 SELECT SUM(DECODE(tbbdetc_type_ind,
            'C',tbraccd_amount,
                tbraccd_amount * -1))
   FROM tbbdetc,
        tbraccd
  WHERE tbbdetc_detail_code = tbraccd_detail_code
    AND tbraccd_pidm = p_pidm
    AND (p_term_code IS NULL
      OR tbraccd_term_code LIKE p_term_code)
    AND (p_detail_code IS NULL
      OR tbraccd_detail_code LIKE p_detail_code)
    AND (p_dcat_code IS NULL
      OR tbbdetc_dcat_code = p_dcat_code)
    AND (p_srce_code IS NULL
      OR tbraccd_srce_code = p_srce_code)
    AND (p_invoice_number IS NULL
      OR tbraccd_invoice_number = p_invoice_number)
    AND (p_inv_number_paid IS NULL
      OR tbraccd_inv_number_paid = p_inv_number_paid)
    AND (trunc(tbraccd_bill_date) > trunc(p_bill_date)
      OR tbraccd_bill_date IS NULL)
    AND tbraccd_effective_date <= p_effective_date
    AND (tbbdetc_type_ind = 'P' 
         OR 
        (tbbdetc_type_ind = 'C' AND tbraccd_amount < 0)
        );

BEGIN
 OPEN   get_sum_net_amount_c;
 FETCH  get_sum_net_amount_c INTO lv_sum_net_amount;
 CLOSE  get_sum_net_amount_c;
 RETURN NVL(lv_sum_net_amount,0);
END f_loc_sum_net_amount_special;

PROCEDURE p_delete_old_psel IS
--to delete previous system generated popsel contents
BEGIN
DELETE
FROM glbextr
WHERE
GLBEXTR_APPLICATION = v_psel_application
AND GLBEXTR_SELECTION = v_psel_selection
AND GLBEXTR_CREATOR_ID = v_psel_creator_id
AND GLBEXTR_USER_ID = v_psel_user_id
AND GLBEXTR_SYS_IND = 'S';
END p_delete_old_psel;

PROCEDURE p_insert_psel (v_rec_billed c_tbbstmt%ROWTYPE) IS
v_outline varchar2(2000);
BEGIN

     INSERT 
     INTO GLBEXTR 
     (
          GLBEXTR_APPLICATION,
          GLBEXTR_SELECTION, 
          GLBEXTR_CREATOR_ID, 
          GLBEXTR_USER_ID,
          GLBEXTR_KEY, 
          GLBEXTR_ACTIVITY_DATE, 
          GLBEXTR_SYS_IND, 
          GLBEXTR_SLCT_IND 
     )
     VALUES 
     (
          v_psel_application,
          v_psel_selection, 
          v_psel_creator_id, 
          v_psel_user_id, 
          v_rec_billed.spriden_pidm,  
          sysdate,
          'S', 
          NULL
     ); 
     v_outline := v_rec_billed.spriden_id || trim(v_rec_billed.spriden_last_name || ', '||
                  v_rec_billed.spriden_first_name || ' ' || v_rec_billed.spriden_mi);
     gzpadmn.p_spool_lis('&1', &2, NULL, NULL,v_outline,TRUE);
END p_insert_psel;


BEGIN

dbms_output.enable(2000000);
dbms_output.put_line('starting ...' || systimestamp);

--parms
v_term_code := gzpadmn.f_get_parm ('&1','&2','&4','01');
v_run_mode := gzpadmn.f_get_parm ('&1','&2','&4','02');
v_bill_run := gzpadmn.f_get_parm('&1','&2','&4','03');
v_amount_due_threshold := gzpadmn.f_get_parm('&1','&2','&4','04');
v_payment_ratio := gzpadmn.f_get_parm('&1','&2','&4','05');
v_exempt_level_GR := upper(gzpadmn.f_get_parm('&1','&2','&4','06'));
v_exempt_level_PR := upper(gzpadmn.f_get_parm('&1','&2','&4','07'));
v_pact_code := gzpadmn.f_get_parm('&1','&2','&4','08');

--PRINT THE PARMS 
dbms_output.put_line('Term Code:' || v_term_code);
dbms_output.put_line('Run Mode:' || v_run_mode);
dbms_output.put_line('Bill Date for Amount Due:' || v_bill_run);
dbms_output.put_line('Amount Due Threshold:' || v_amount_due_threshold);
dbms_output.put_line('Payment Percentage:' || v_payment_ratio);
dbms_output.put_line('Protect (exempt) GR Students:' || v_exempt_level_GR);
dbms_output.put_line('Protect (exempt) PR Students:' || v_exempt_level_PR);
dbms_output.put_line('PACT Detail Code:' || v_pact_code);


--delete the old popsel contents,except the user entered
p_delete_old_psel;


--main driver loop for all person's billed on target billed with amount due at
-- or over the parm percentage
FOR rec_billed IN c_tbbstmt(v_bill_run,v_amount_due_threshold) LOOP
     v_found:= FALSE;

     --1 test if student is on auth aid
     OPEN c_has_aid_auth(rec_billed.spriden_pidm,v_term_code);
     FETCH c_has_aid_auth INTO r_pidm;
     IF c_has_aid_auth%FOUND THEN
          v_found:= TRUE;
     END IF;
     CLOSE c_has_aid_auth;

     --2 test if student is on memo
     IF v_found = FALSE THEN
          OPEN c_has_aid_memo(rec_billed.spriden_pidm,v_term_code,sysdate);
          FETCH c_has_aid_memo INTO r_pidm;
          IF c_has_aid_memo%FOUND THEN
               v_found:= TRUE;
          END IF;
          CLOSE c_has_aid_memo;
     END IF;

     --3 test if student is on contract
--     if v_found = false then
--          open c_on_contract(rec_billed.spriden_pidm,v_term_code);
--          fetch c_on_contract into r_pidm;
--          if c_on_contract%found then
--               v_found:= true;
--          end if;
--          close c_on_contract;
--     end if;

     --4 test if student is level exempt GR
     IF v_found = FALSE AND v_exempt_level_GR = 'Y' THEN
          OPEN c_exempt_level(rec_billed.spriden_pidm,v_term_code,'GR');
          FETCH c_exempt_level INTO r_pidm;
          IF c_exempt_level%FOUND THEN
               v_found:= TRUE;
          END IF;
          CLOSE c_exempt_level;
     END IF;

     --5 test if student is level exempt PR
     IF v_found = FALSE AND v_exempt_level_PR = 'Y' THEN
          OPEN c_exempt_level(rec_billed.spriden_pidm,v_term_code,'PR');
          FETCH c_exempt_level INTO r_pidm;
          IF c_exempt_level%FOUND THEN
               v_found:= TRUE;
          END IF;
          CLOSE c_exempt_level;
     END IF;
     
     --********************************************************
     --6 test if student is employee dependent waiver and PACT
     IF v_found = FALSE THEN
          OPEN c_has_pact_depd_waiver(rec_billed.spriden_pidm, v_term_code, v_pact_code);
          FETCH c_has_pact_depd_waiver INTO r_pidm;
          IF c_has_pact_depd_waiver%FOUND THEN
               v_outline := rec_billed.spriden_id || trim(rec_billed.spriden_last_name || ', '||
               rec_billed.spriden_first_name || ' ' || rec_billed.spriden_mi);
               gzpadmn.p_spool_lis('&1','&2','PACT','I',v_outline,TRUE);
               v_found:= TRUE;
          END IF;
          CLOSE c_has_pact_depd_waiver;
     END IF;
     --********************************************************

     --7 test if student at 50%
     IF v_found = FALSE THEN
          --get the students netpayments and reversed charges since that bill
          v_special_payments := f_loc_sum_net_amount_special
                                (p_pidm => rec_billed.tbbstmt_pidm,
                                 p_effective_date => sysdate);
          IF ((v_special_payments *-1 +.01)/rec_billed.tbbstmt_amount_due) >= v_payment_ratio THEN
               v_found := TRUE;
          END IF;
     END IF;

	IF v_found = FALSE THEN -- no exception found then give hold
          p_insert_psel(rec_billed);
     END IF;
END LOOP;

IF upper(v_run_mode) = 'U' THEN
     COMMIT;
ELSE
     ROLLBACK;
END IF;
dbms_output.put_line('finished ...' || systimestamp);
END;
/
exit;