DARTS : UM_FIN_AID_DTVW

SELECT A.EMPLID,
       A.DISBURSEMENT_ID,
       A.AID_YEAR,
       A.INSTITUTION,
       A.ITEM_TYPE,
       A.ACAD_CAREER,
       A.ACTION_DTTM,
       A.ACTION_DT,
       A.UM_DISB_DTTM,
       A.UM_ORIG_AMT_DTTM,
       A.ACAD_CAREER_DESCR,
       A.LAST_NAME,
       A.FIRST_NAME,
       A.MIDDLE_INITIAL,
       A.NAME_SUFFIX,
       A.UM_CALC_AID_AMT,
       A.AGGREGATE_AREA,
       A.AGI,
       A.AWARD_PERIOD,
       A.UM_AWARD_PER_DESCR,
       A.AWARD_DISB_ACTION,
       A.UM_CALC_PAR_TOTINC,
       A.CHARGE_PRIORITY,
       A.CHILDREN,
       A.CITIZENSHIP_STATUS,
       A.DEPENDENTS,
       A.DEPNDNCY_STAT,
       A.UM_DEPN_STAT_DESCR,
       A.DISBURSE_METHOD,
       A.UM_DISB_METH_DESCR,
       A.UM_ORIG_OFFER_AMT,
       A.OFFER_AMT,
       A.ACCEPT_AMOUNT,
       A.AUTHORIZED_AMOUNT,
       A.DISB_AMOUNT,
       A.DISB_TO_DATE,
       A.DISBURSEMENT_PLAN,
       A.FA_SOURCE,
       A.FA_SOURCE_DESCR,
       A.FED_EFC,
       A.FED_NEED,
       A.FED_NEED_BASE_AID,
       A.FED_OR_INST,
       A.FED_OVRAWD_AMT,
       A.FED_OVRAWD_COA,
       A.FED_PARENT_CONTRB,
       A.FED_SPECIAL_AID,
       A.FED_STDNT_CONTRB,
       A.FED_TOTAL_AID,
       A.FED_UNMET_COA,
       A.FED_UNMET_NEED,
       A.FED_YEAR_COA,
       A.FEDERAL_ID,
       A.FEDERAL_ID_DESCR,
       A.UM_EST_AID_FLG,
       A.FIN_AID_TYPE,
       A.UM_FINAID_TYP_DESC,
       A.FIRST_BACH_DEGREE,
       A.FISAP_TOT_INC,
       A.GRADUATE,
       A.GRADUATE_STUDENT,
       A.HOUSING_CODE_1,
       A.UM_HOUSE_CD_DESCR,
       A.HS_GRAD_DT,
       A.INST_DESCR,
       A.ISIR_CALC_EFC,
       A.ITEM_TYPE_DESCR,
       A.ITEM_TYPE_CD,
       A.UM_ITM_TYPCD_DESCR,
       A.KEYWORD1,
       A.KEYWORD2,
       A.KEYWORD3,
       A.LOAN_PROGRAM,
       A.UM_LOAN_PROG_DESCR,
       A.MARITAL_STATUS,
       A.MARRIED,
       A.NEED_BASED,
       A.ORPHAN,
       A.PRIMARY_EFC,
       A.PRORATED_EFC,
       A.REPORT_CODE,
       A.SCHOOL_CHOICE_1,
       A.SPLIT_CODE,
       A.STATE_RESIDENCE,
       A.STU_TOTAL_INC,
       A.TITLE_IV,
       A.TOTAL_INCOME,
       A.UM_SCRTY_1,
       A.UM_SCRTY_2,
       A.UM_SCRTY_3,
       A.UM_SCRTY_4,
       A.UM_SCRTY_5,
       A.UM_SCRTY_6,
       A.UM_SCRTY_7,
       A.UM_SCRTY_8,
       A.VET_ED_BENEFIT,
       A.VETERAN,
       A.BIRTHDATE,
       A.SEX
  FROM PS_UM_FIN_AIDDT A;

 !**********************************************************************
!  UMFAIDDT: Financial Aid DATE Table.                          *
!***********************************************************************
! P.ANDERSEN  						2/26/2014				  			             *
!***********************************************************************
! Mod #    Programmer        Date             Description              *
! ------   ----------     ----    -------------------------------------*
!
!***********************************************************************

#include 'setenv.sqc'  		!Set environment

!***********************************************************************
!        Begin Setup                                                   *
! REPORT-309  P. Andersen                           Tune SQL           *                                
!***********************************************************************
Begin-Setup

begin-sql
alter session set current_schema = SYSADM
end-sql

Load-Lookup Name=Get_Xlat_Acad_career
     table='psxlatitem xlt'
     key = fieldvalue
     return_value = xlatlongname
     where='xlt.fieldname= ''ACAD_CAREER'' and xlt.effdt= ( select max(xlt_ed.effdt) from psxlatitem xlt_ed  where xlt_ed.fieldvalue = xlt.fieldvalue and xlt_ed.fieldname = xlt.fieldname and xlt.effdt <= SYSDATE)'

Load-Lookup Name=Get_Xlat_Award_period
     table='psxlatitem xlt'
     key = fieldvalue
     return_value = xlatlongname
     where='xlt.fieldname= ''AWARD_PERIOD'' and xlt.effdt= ( select max(xlt_ed.effdt) from psxlatitem xlt_ed  where xlt_ed.fieldvalue = xlt.fieldvalue and xlt_ed.fieldname = xlt.fieldname and xlt.effdt <= SYSDATE)'
    
Load-Lookup Name=Get_Xlat_Depndncy_stat
     table='psxlatitem xlt'
     key = fieldvalue
     return_value = xlatlongname
     where='xlt.fieldname= ''DEPNDNCY_STAT'' and xlt.effdt= ( select max(xlt_ed.effdt) from psxlatitem xlt_ed  where xlt_ed.fieldvalue = xlt.fieldvalue and xlt_ed.fieldname = xlt.fieldname and xlt.effdt <= SYSDATE)'
     
Load-Lookup Name=Get_Xlat_Disburse_method
     table='psxlatitem xlt'
     key = fieldvalue
     return_value = xlatlongname
     where='xlt.fieldname= ''DISBURSE_METHOD'' and xlt.effdt= ( select max(xlt_ed.effdt) from psxlatitem xlt_ed  where xlt_ed.fieldvalue = xlt.fieldvalue and xlt_ed.fieldname = xlt.fieldname and xlt.effdt <= SYSDATE)'

Load-Lookup Name=Get_Xlat_efc_status
     table='psxlatitem xlt'
     key = fieldvalue
     return_value = xlatlongname
     where='xlt.fieldname= ''EFC_STATUS'' and xlt.effdt= ( select max(xlt_ed.effdt) from psxlatitem xlt_ed  where xlt_ed.fieldvalue = xlt.fieldvalue and xlt_ed.fieldname = xlt.fieldname and xlt.effdt <= SYSDATE)'

Load-Lookup Name=Get_Xlat_fa_source
     table='psxlatitem xlt'
     key = fieldvalue
     return_value = xlatlongname
     where='xlt.fieldname= ''FA_SOURCE'' and xlt.effdt= ( select max(xlt_ed.effdt) from psxlatitem xlt_ed  where xlt_ed.fieldvalue = xlt.fieldvalue and xlt_ed.fieldname = xlt.fieldname and xlt.effdt <= SYSDATE)'

Load-Lookup Name=Get_Xlat_fin_aid_type
     table='psxlatitem xlt'
     key = fieldvalue
     return_value = xlatlongname
     where='xlt.fieldname= ''FIN_AID_TYPE'' and xlt.effdt= ( select max(xlt_ed.effdt) from psxlatitem xlt_ed  where xlt_ed.fieldvalue = xlt.fieldvalue and xlt_ed.fieldname = xlt.fieldname and xlt.effdt <= SYSDATE)'

Load-Lookup Name=Get_Xlat_federal_id
     table='psxlatitem xlt'
     key = fieldvalue
     return_value = xlatlongname
     where='xlt.fieldname= ''FEDERAL_ID'' and xlt.effdt= ( select max(xlt_ed.effdt) from psxlatitem xlt_ed  where xlt_ed.fieldvalue = xlt.fieldvalue and xlt_ed.fieldname = xlt.fieldname and xlt.effdt <= SYSDATE)'

Load-Lookup Name=Get_Xlat_loan_program
     table='psxlatitem xlt'
     key = fieldvalue
     return_value = xlatlongname
     where='xlt.fieldname= ''LOAN_PROGRAM'' and xlt.effdt= ( select max(xlt_ed.effdt) from psxlatitem xlt_ed  where xlt_ed.fieldvalue = xlt.fieldvalue and xlt_ed.fieldname = xlt.fieldname and xlt.effdt <= SYSDATE)'

Load-Lookup Name=Get_Xlat_housing_code_1
     table='psxlatitem xlt'
     key = fieldvalue
     return_value = xlatlongname
     where='xlt.fieldname= ''HOUSING_CODE_1'' and xlt.effdt= ( select max(xlt_ed.effdt) from psxlatitem xlt_ed  where xlt_ed.fieldvalue = xlt.fieldvalue and xlt_ed.fieldname = xlt.fieldname and xlt.effdt <= SYSDATE)'

Load-Lookup Name=Get_Xlat_item_type_cd
     table='psxlatitem xlt'
     key = fieldvalue
     return_value = xlatlongname
     where='xlt.fieldname= ''ITEM_TYPE_CD'' and xlt.effdt= ( select max(xlt_ed.effdt) from psxlatitem xlt_ed  where xlt_ed.fieldvalue = xlt.fieldvalue and xlt_ed.fieldname = xlt.fieldname and xlt.effdt <= SYSDATE)'
     
Load-Lookup Name=Get_item_type_cd
     table='PS_ITEM_TYPE_TBL D1'
     key = ITEM_TYPE
     return_value = ITEM_TYPE_CD
     where='D1.EFFDT = (SELECT MAX(BEF.EFFDT) FROM PS_ITEM_TYPE_TBL BEF  WHERE D1.SETID = BEF.SETID  AND D1.ITEM_TYPE = BEF.ITEM_TYPE  AND BEF.EFFDT <= SYSDATE) AND D1.ITEM_TYPE_CD = ''C'' '

End-Setup

!**********************************************************************


!***********************************************************************
! Process-Main Procedure                                               *
!***********************************************************************
Begin-Program
   Let $Reporttitle = 'Financial Aid Date Table'
   let $Reportid = 'umfaiddt'
   do Stdapi-Init
   if $prcs_process_instance <= ''
   let $prcs_oprid = 'PANDERSEN'
    let $prcs_run_cntl_id = 'BATCH'
   end-if
   let $input_aid_yr = '2012'  
   do Select-Parameters   
   show $input_aid_yr  
   show $input_aid_yr_to
   do delete_rows          
   do List_Employees
   do Blasto_Data
   do Stdapi-Term
End-Program

!**********************************************************************
! Delete_Rows                                    *
!**********************************************************************
Begin-Procedure Delete_Rows
Let $Calling_Procedure = 'Delete_Rows'
BEGIN-SQL 
   TRUNCATE TABLE PS_UM_FIN_AIDT_BLD
END-SQL

COMMIT

SHOW 'Truncate PS_UM_FIN_AIDDT_BLD.'
End-Procedure Delete_Rows
!***********************************************************************
!  Select Parameters                                       
!***********************************************************************

Begin-Procedure Select-Parameters
begin-select
R.Aid_Year,
R.Aid_Year_to

  Let $input_aid_yr = &R.Aid_Year
  Let $input_aid_yr_to = &R.Aid_Year_To
  let $term1 = substr($input_aid_yr,3,2)||'20'
  let $term2 = substr($input_aid_yr,3,2)||'10'
  
FROM SYSADM.PS_UM_RUN_UMFAIDDT R
WHERE R.OPRID = $prcs_oprid
  AND R.RUN_CNTL_ID = $prcs_run_cntl_id

end-select
End-Procedure
!**********************************************************************
! Process_Employee-Data Procedure                                     *
!**********************************************************************
Begin-Procedure List_Employees
 Let $Calling_Procedure = 'List_Employees'
 Let #Fin_Count = 0
 show 'List Employees'
BEGIN-SELECT   on-error=sql_error
/*+ OPT_PARAM('_OPTIMIZER_COST_BASED_TRANSFORMATION', 'ON') */
a.action_dttm ,
trunc(a.action_dttm) &action_dt,
max(a.action_dttm) over (partition by  a.emplid, a.aid_year, a.institution, a.item_type, a.acad_career) &UM_DISB_DTTM,
min(a.action_dttm) over (partition by  a.emplid, a.aid_year, a.institution, a.item_type, a.acad_career) &UM_ORIG_AMT_DTTM,
a.disbursement_id, 
a.emplid, 
a.aid_year, 
a.institution, 
a.item_type, 
a.acad_career, 
!-- , o.xlatlongname um_acad_career_descr
initcap(lower(nvl(d.iwd_std_last_name,' '))) &g.last_name,  !
initcap(lower(nvl(d.iwd_std_first_nm02,' '))) &g.first_name, 
nvl(iwd_stu_mi,' ')   &middle_initial, 
' ' &g.name_suffix, 
CASE WHEN b.fin_aid_type  <> 'W' AND b.disburse_method = 'N' AND a.disb_amount  = 0 THEN a.accept_amount WHEN b.fin_aid_type  <> 'W' AND b.disburse_method = ' ' AND a.disb_amount  = 0 THEN a.accept_amount ELSE a.disb_amount END &um_calc_aid_amt,
b.aggregate_area, 
d.agi, 
c.award_period, 
!-- , q.xlatlongname UM_AWARD_PER_DESCR
a.award_disb_action, 
( d.fisap_tot_inc - e.stu_total_inc ) &um_calc_par_totinc 
b.charge_priority, 
d.children, 
d.citizenship_status, 
d.dependents, 
d.depndncy_stat, 
!-- , r.xlatlongname um_depn_stat_descr
b.disburse_method, 
!-- , n.xlatlongname um_disb_meth_descr
First_value(a.offer_amount) over (partition by  a.emplid, a.aid_year, a.institution, a.item_type, a.acad_career order by a.action_dttm) &Um_orig_offer_amt
a.offer_amount, 
a.accept_amount, 
a.authorized_amount, 
a.disb_amount, 
a.disb_to_date,
a.disbursement_plan, 
b.fa_source, 
!-- , i.xlatlongname UM_FA_SOURCE_DESCR
c.fed_efc, 
c.fed_need, 
c.fed_need_base_aid, 
b.fed_or_inst, 
c.fed_ovrawd_amt, 
c.fed_ovrawd_coa, 
c.fed_parent_contrb, 
c.fed_special_aid, 
c.fed_stdnt_contrb, 
c.fed_total_aid, 
c.fed_unmet_coa, 
c.fed_unmet_need, 
c.fed_year_coa, 
b.federal_id, 
!-- , j.xlatlongname um_federal_id_descr
CASE WHEN UPPER(b.descr) LIKE '%EST.%' THEN 'Y' WHEN UPPER(b.descr) LIKE '%ESTIMATED%' THEN 'Y' WHEN UPPER(b.descr) LIKE 'EST %' THEN 'Y' WHEN UPPER(b.descr) LIKE '%EST''D%' THEN 'Y' WHEN UPPER(b.descr) LIKE 'E-%' THEN 'Y' ELSE 'N' END  &um_est_aid_flag 
b.fin_aid_type, 
!-- , k.xlatlongname um_fin_aid_type_descr
d.first_bach_degree, 
d.fisap_tot_inc, 
d.graduate, 
d.graduate_student, 
d.housing_code_1, 
!-- , l.xlatlongname UM_HOUSING_CD_DESCR
d.hs_grad_dt, 
CASE WHEN a.institution = 'UMS01'THEN 'UMA' WHEN a.institution = 'UMS02'THEN 'UMF' WHEN a.institution = 'UMS03'THEN 'UMFK' WHEN a.institution = 'UMS04'THEN 'UMM' WHEN a.institution = 'UMS07'THEN 'UMPI' WHEN a.institution = 'UMS05'THEN 'UM' WHEN a.institution = 'UMS06'THEN 'USM' ELSE 'Other' END &um_inst_descr 
c.isir_calc_efc, 
b.descr  &item_type_descr, 
b.item_type_cd, 
!-- , s.xlatlongname as UM_ITM_TYPCD_DESCR
b.keyword1, 
b.keyword2, 
b.keyword3, 
b.loan_program, 
!-- , t.xlatlongname UM_LOAN_PROG_DESCR
d.marital_stat, 
d.married, 
b.need_based, 
d.orphan, 
e.primary_efc, 
e.prorated_efc, 
b.report_code, 
d.school_choice_1, 
a.split_code, 
d.state_residence, 
e.stu_total_inc, 
b.title_iv, 
e.total_income, 
b.um_scrty_1, 
b.um_scrty_2, 
b.um_scrty_3, 
b.um_scrty_4, 
b.um_scrty_5, 
b.um_scrty_6, 
b.um_scrty_7, 
b.um_scrty_8, 
c.vet_ed_benefit, 
d.veteran ,
d.birthdate &g.birthdate,
decode(d.sfa_stdnt_gender,'2','F','1','M',' ') &g.sex
  Add 1 to #Fin_count
  Let $housing_code_descr = 'Off-Campus'
  Lookup Get_Xlat_Acad_Career &a.acad_career $acad_career_descr                   
  Lookup Get_Xlat_Award_period &c.award_period $award_period_descr                
  Lookup Get_Xlat_Depndncy_stat  &d.depndncy_stat $depndncy_stat_descr            
  Lookup Get_Xlat_Disburse_method  &b.disburse_method $disburse_method_descr      
  Lookup Get_Xlat_fa_source  &b.fa_source $fa_source_descr                        
  Lookup Get_Xlat_fin_aid_type &b.fin_aid_type $fin_aid_type_descr                
  Lookup Get_Xlat_federal_id &b.federal_id $federal_id_descr                      
  Lookup Get_Xlat_loan_program &b.loan_program $loan_program_descr                
  Lookup Get_Xlat_housing_code_1 &d.housing_code_1 $housing_code_descr            
  Lookup Get_Xlat_item_type_cd &b.item_type_cd $item_type_cd_descr
  if $acad_career_descr < ' ' 
   Let $acad_career_descr = ' '
  end-if
  if $application_stat_descr < ' ' 
   Let $application_stat_descr = ' '
  end-if
  if $award_period_descr < ' ' 
   Let $award_period_descr = ' '
  end-if
  if $depndncy_stat_descr < ' '
   Let $depndncy_stat_descr = ' '
  end-if
  if $disburse_method_descr < ' '
   Let $disburse_method_descr = ' '
  end-if
  if $fa_source_descr < ' ' 
   Let $fa_source_descr = ' '
  end-if
  if $fin_aid_type_descr < ' '
   Let $fin_aid_type_descr = ' '
  end-if
  if $federal_id_descr < ' '
   Let $federal_id_descr = ' '
  end-if
  if $loan_program_descr < ' '
   Let $loan_program_descr = ' '
  end-if
  if $housing_code_descr < ' '
   Let $housing_code_descr = 'Off-Campus'
  end-if
  
  Do Get_Housing_amount(&A.EMPLID, &A.INSTITUTION,$term2,#housing_amt)
  
  Let $housing_code_descr_long = $housing_code_descr
  if ($housing_code_descr = 'On-Campus' or $housing_code_descr = 'Off-Campus')
      if #housing_amt > 0 
       Let $housing_code_descr_long ='On-Campus' 
      else
       Let $housing_code_descr_long  = 'Off-Campus'
      end-if
  end-if
  if $housing_code_descr = 'Parents'  
     if #housing_amt > 0 
      Let $housing_code_descr_long = 'On-Campus'
     else
      Let $housing_code_descr_long = 'Parents'
     end-if
   end-if
   
  
  
  
  if mod(#Fin_count,10000) = 0 
    show 'Records processed:' #Fin_count
    commit
  end-if

  DO Insert_Fin_Aiddt_Tbl 

  
  FROM ps_stdnt_awrd_actv a 
  , 
  ! first part of PS_UM_ITYP_FA_VW
  (
  SELECT a.setid 
 , a.item_type 
 , a.aid_year 
 , a.effdt 
 , a.eff_status 
 , a.descr 
 , a.descrshort 
 , a.fa_source 
 , a.federal_id 
 , a.loan_program 
 , a.title_iv 
 , a.fin_aid_type 
 , a.fed_or_inst 
 , a.need_based 
 , a.aggregate_area 
 , a.report_code 
 , a.disburse_method 
 , b.item_type_cd 
 , b.charge_priority 
 , b.keyword1 
 , b.keyword2 
 , b.keyword3 
 , c.um_scrty_1 
 , c.um_scrty_2 
 , c.um_scrty_3 
 , c.um_scrty_4 
 , c.um_scrty_5 
 , c.um_scrty_6 
 , c.um_scrty_7 
 , c.um_scrty_8 
 , c.um_gasb_1 
 , c.um_gasb_2 
 , c.um_gasb_3 
 , c.um_gasb_4 
 , c.um_gasb_5 
  FROM ps_item_type_fa a 
  , ps_item_type_tbl b 
  , ps_um_ityp_treends c 
 WHERE a.setid = b.setid 
   AND a.item_type = b.item_type 
   AND b.effdt = ( 
 SELECT MAX(bef.effdt) 
  FROM ps_item_type_tbl bef 
 WHERE b.setid = bef.setid 
   AND b.item_type = bef.item_type 
   AND bef.effdt <= sysdate) 
   AND b.eff_status = 'A' 
   AND a.setid = c.setid (+) 
   AND a.item_type = c.item_type(+) 
   AND a.effdt = ( 
 SELECT MAX(aef.effdt) 
  FROM ps_item_type_fa aef 
 WHERE a.setid = aef.setid 
   AND a.item_type = aef.item_type 
   AND a.aid_year = aef.aid_year 
   AND aef.effdt <= sysdate) 
   AND a.eff_status = 'A' ) b

  , ps_stdnt_awd_per c 
  , ps_isir_student d 
  , ps_isir_computed e 
 ! , ps_personal_data g 

     
WHERE A.AID_YEAR >= $input_aid_yr
AND A.AID_YEAR <= $input_aid_yr_to
AND (A.OFFER_amount <> 0  OR A.ACCEPT_amount <> 0 OR A.DISB_amount <> 0)
AND a.aid_year = b.aid_year 
AND a.item_type = b.item_type 
AND a.institution = b.setid 
AND a.emplid = c.emplid 
AND a.institution = c.institution 
AND a.aid_year = c.aid_year 
AND c.award_period = 'A' 
AND a.emplid = d.emplid(+)
   AND a.institution = d.institution(+)
   AND a.aid_year = d.aid_year(+)
   AND (d.effdt = (SELECT MAX(d_ed.effdt)
                     FROM ps_isir_student d_ed
                    WHERE d.emplid = d_ed.emplid
                      AND d.institution = d_ed.institution
                      AND d.aid_year = d_ed.aid_year
                      AND d_ed.effdt <= sysdate) OR d.effdt IS NULL)
   AND (d.effseq = (SELECT MAX(d_es.effseq)
                      FROM ps_isir_student d_es
                     WHERE d.emplid = d_es.emplid
                       AND d.institution = d_es.institution
                       AND d.aid_year = d_es.aid_year
                       AND d.effdt = d_es.effdt) OR d.effdt IS NULL)
   AND a.emplid = e.emplid(+)                                            
   AND a.institution = e.institution(+)
   AND a.aid_year = e.aid_year(+)
   AND (e.effdt = ( 
 SELECT  MAX(e_ed.effdt) 
  FROM ps_isir_computed e_ed 
 WHERE e.emplid = e_ed.emplid 
   AND e.institution = e_ed.institution 
   AND e.aid_year = e_ed.aid_year 
   AND e_ed.effdt <= sysdate) 
    OR e.effdt IS NULL) 
AND (e.effseq = ( 
 SELECT  MAX(e_es.effseq) 
  FROM ps_isir_computed e_es 
 WHERE e.emplid = e_es.emplid 
   AND e.institution = e_es.institution 
   AND e.aid_year = e_es.aid_year 
   AND e.effdt = e_es.effdt) 
    OR e.effseq IS NULL) 
!   AND a.emplid = g.emplid 

   
END-SELECT

show 'Done List Employees'

End-Procedure Process-Employee-Data
!**********************************************************************
! Get Housing Amount
!**********************************************************************
Begin-procedure Get_housing_amount(($in_emplid, $in_institution,$in_strm ,:#housing_amt)
Let #housing_amt = 0
BEGIN-SELECT 
A1.BUSINESS_UNIT,
A1.EMPLID,
a1.acad_year,
a1.item_term,
a1.item_type,
SUM(A1.ITEM_AMT) &ITEM_AMOUNT
  Let $item_type = ' '
  Lookup Get_ITEM_TYPE_CD &a1.item_type $item_type
  if $item_type = 'C'
   Let #housing_amt = #housing_amt + &ITEM_AMOUNT
  end-if
      ! REPORT-309 substitute PS_UM_ITYP_TREENDS and PS_ITEM_TYPE_TBL for PS_UM_ITYP_FA_VW
FROM sysadm.PS_ITEM_SF A1, SYSADM.PS_UM_ITYP_TREENDS B1
   !, SYSADM.PS_ITEM_TYPE_TBL D1               
WHERE  A1.ITEM_TYPE = B1.ITEM_TYPE
AND A1.BUSINESS_UNIT = B1.SETID
!AND B1.SETID = D1.SETID
!AND B1.ITEM_TYPE = D1.ITEM_TYPE
!     AND D1.EFFDT = (SELECT MAX(BEF.EFFDT) 
!  FROM PS_ITEM_TYPE_TBL BEF 
! WHERE D1.SETID = BEF.SETID 
!   AND D1.ITEM_TYPE = BEF.ITEM_TYPE 
 !  AND BEF.EFFDT <= SYSDATE) 
!AND D1.ITEM_TYPE_CD = 'C'
AND B1.UM_SCRTY_3 = 'RMB'
AND B1.UM_SCRTY_4 = 'RMB_HOUS'
AND SUBSTR(A1.ITEM_TERM,1,2)= SUBSTR(a1.acad_year,3,2)
AND A1.emplid = $in_emplid
AND A1.BUSINESS_UNIT = $in_institution
AND A1.item_term = $in_strm
group by A1.BUSINESS_UNIT, A1.EMPLID, a1.acad_year,a1.item_term, a1.item_type
END-SELECT
end-procedure

!**********************************************************************
! Insert_Fin_Aid_Tbl      [Generate PS_UM_FIN_AID_VW Table]       *  
!**********************************************************************
Begin-Procedure  Insert_Fin_Aiddt_Tbl 

let $Calling_Procedure = 'Insert_Fin_Aid_Tbl'
let $housing = 'Off-Campus'
BEGIN-SQL on-error=sql_error
INSERT INTO SYSADM.PS_UM_FIN_AIDT_BLD
VALUES(&a.emplid,
NVL(&a.disbursement_id,' '),
NVL(&a.aid_year,' '),
&a.institution, 
&a.item_type, 
&a.acad_career,
&A.ACTION_DTTM,
&ACTION_DT,
&UM_DISB_DTTM,
&UM_ORIG_AMT_DTTM, 
$acad_career_descr,
&g.last_name, 
&g.first_name, 
&middle_initial,  
NVL(&g.name_suffix,' '), 
&um_calc_aid_amt,
nvl(&b.aggregate_area,' '), 
nvl(&d.agi,0), 
&c.award_period, 
$AWARD_PERIOD_DESCR,
&a.award_disb_action,
nvl(&um_calc_par_totinc,0) ,
nvl(&b.charge_priority,' '),
nvl(&d.children,' '), 
nvl(&d.citizenship_status,' '), 
nvl(&d.dependents,' '), 
nvl(&d.depndncy_stat,' '), 
$depndncy_stat_descr,
nvl(&b.disburse_method,' '), 
$disburse_method_descr,
&Um_orig_offer_amt,
&a.offer_amount, 
&a.accept_amount, 
&a.authorized_amount, 
&a.disb_amount, 
&a.disb_to_date,
&a.disbursement_plan, 
nvl(&b.fa_source,' '), 
$fa_source_descr,
&c.fed_efc, 
&c.fed_need,
&c.fed_need_base_aid, 
nvl(&b.fed_or_inst,' '), 
&c.fed_ovrawd_amt, 
&c.fed_ovrawd_coa, 
&c.fed_parent_contrb, 
&c.fed_special_aid, 
&c.fed_stdnt_contrb, 
&c.fed_total_aid, 
&c.fed_unmet_coa, 
&c.fed_unmet_need, 
&c.fed_year_coa, 
nvl(&b.federal_id,' '), 
$federal_id_descr,
&um_est_aid_flag, 
nvl(&b.fin_aid_type,' '), 
$fin_aid_type_descr,
nvl(&d.first_bach_degree,' '), 
nvl(&d.fisap_tot_inc,0),
nvl(&d.graduate,' '), 
nvl(&d.graduate_student,' '), 
NVL(&d.housing_code_1,'2'), 
$HOUSING_CODE_DESCR_LONG,
nvl(&d.hs_grad_dt,'01-JAN-1901'), 
&um_inst_descr, 
&c.isir_calc_efc, 
&item_type_descr, 
&b.item_type_cd, 
$item_type_cd_descr,
nvl(&b.keyword1,' '),
nvl(&b.keyword2,' '), 
nvl(&b.keyword3,' '), 
nvl(&b.loan_program,' '), 
$loan_program_descr,
nvl(&d.marital_stat,' '), 
nvl(&d.married,' '), 
nvl(&b.need_based,' '), 
nvl(&d.orphan,' '), 
nvl(&e.primary_efc,0), 
nvl(&e.prorated_efc,0), 
&b.report_code, 
nvl(&d.school_choice_1,' '), 
&a.split_code, 
nvl(&d.state_residence,' '), 
nvl(&e.stu_total_inc,0), 
nvl(&b.title_iv,' '), 
nvl(&e.total_income,0), 
nvl(&b.um_scrty_1,' '), 
nvl(&b.um_scrty_2,' '), 
nvl(&b.um_scrty_3,' '), 
nvl(&b.um_scrty_4,' '), 
nvl(&b.um_scrty_5,' '), 
nvl(&b.um_scrty_6,' '), 
nvl(&b.um_scrty_7,' '), 
nvl(&b.um_scrty_8,' '), 
&c.vet_ed_benefit, 
nvl(&d.veteran,' '), 
&g.birthdate,
&g.sex
)

END-SQL

End-Procedure Insert_Fin_Aiddt_Tbl

begin-procedure Blasto_Data
Begin-select
count(*) &CTR_UM_BLD
FROM SYSADM.PS_UM_FIN_AIDT_BLD buildies
end-select
if &CTR_UM_BLD > 0	 
    begin-sql 
    DELETE  FROM ps_um_fin_aiddt dxx where AID_YEAR >= $input_aid_yr AND AID_YEAR <= $input_aid_yr_to
    end-sql
 

   BEGIN-SQL 
   COMMIT
   END-SQL
   show 'Extract trimmed.'
   
   BEGIN-SQL
   INSERT INTO PS_UM_FIN_AIDDT ( SELECT * FROM SYSADM.PS_UM_FIN_AIDT_BLD)
   END-SQL
   
   BEGIN-SQL
   COMMIT
   END-SQL
   show 'Rows inserted from UM_FIN_AIDDT_BLD.'
 else
   show 'Nothing in the Build Table.'
 end-if
end-procedure


!***********************************************************************
! SQL-Error Procedure
!***********************************************************************
Begin-Procedure SQL_Error

show '$Calling_Procedure => ' $Calling_Procedure
display $SQL-Error

show &A.ACTION_DTTM
show &a.disbursement_id
show &a.emplid
show &a.aid_year
show &a.institution 
show &a.item_type 

End-Procedure SQL_Error




!***********************************************************************
!SQC Include Files                                                     *
!***********************************************************************
#include 'stdapi.sqc'		!Update Process API
#include 'datetime.sqc'		!Date and Time Functions
#include 'datemath.sqc'		!Date Manipulation Routines
#include 'curdttim.sqc' 	!Get-Current-DateTime procedure
#include 'tranctrl.sqc'		!Transaction Control