DARTS : UM_FIN_AID

!**********************************************************************
!  UMFINAID: Financial Aid At A Glance Table.                          *
!***********************************************************************
! Created By:	A. Lapierre			  											                 *
! Created on:	October 21, 2011 										  			             *
!***********************************************************************
! Mod #    Programmer        Date             Description              *
! ------   ----------     ----    -------------------------------------*
! 1        PSA            02/2014 Rewrite -mutlti aid year,faster,small*
!                                 no need for xtra sqr,temp_table with *
!                                 fast refresh,new fields - REPORT-148 *
!                                 changes too numerous to mark         *
! UMREPORT-218 PSA 5/15           Change value of ITM_TYPCD_DESCR      *
!***********************************************************************
#include 'setenv.sqc'  		!Set environment
!***********************************************************************
!        Begin Setup                                                   *
!***********************************************************************
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_Application_stat
     table='psxlatitem xlt'
     key = fieldvalue
     return_value = xlatlongname
     where='xlt.fieldname= ''APPLICATION_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_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                                ! UMREPORT-218 PSA 5/15  
     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)'
End-Setup
!**********************************************************************
!***********************************************************************
! Process-Main Procedure                                               *
!***********************************************************************
Begin-Program
   Let $Reporttitle = 'Financial Aid Table'
   let $Reportid = 'umfinaid'
   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_AID_BLD
END-SQL
COMMIT
SHOW 'Truncate PS_UM_FIN_AID_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_UMFAIDBF 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
a.emplid, 
a.aid_year,
a.strm,
a.disbursement_ID,
a.institution, 
a.item_type, 
a.acad_career, 
initcap(lower(nvl(d.iwd_std_last_name,' '))) &glast_name,  !
initcap(lower(nvl(d.iwd_std_first_nm02,' '))) &gfirst_name, 
nvl(iwd_stu_mi,' ')   &gmiddle_name, 
' ' &gname_suffix, 
a.accept_balance,
b.aggregate_area,
d.agi,
f.app_stat_dt, 
f.application_stat,
c.award_period, 
NVL((d.fisap_tot_inc - e.stu_total_inc ), 0) &um_calc_par_totinc, 
b.charge_priority, 
d.children,
d.citizenship_status, 
d.dependents, 
d.depndncy_stat, 
b.disburse_method,
a.disbursed_balance, 
f.dt_app_received, 
d.dt_app_completed, 
f.efc_status, 
b.fa_source, 
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, 
b.fin_aid_type, 
d.first_bach_degree, 
d.fisap_tot_inc, 
d.graduate, 
d.graduate_student, 
f.hold_ind, 
NVL(d.housing_code_1,'2') &d.housing_code_1 
d.hs_grad_dt, 
c.isir_calc_efc ,
b.item_type_cd, 
b.descr,                          ! um_itm_typcd_descr 
b.keyword1, 
b.keyword2, 
b.keyword3, 
b.loan_program, 
d.marital_stat, 
d.married, 
b.need_based, 
a.offer_balance, 
d.orphan, 
f.pell_eligibility, 
e.primary_efc, 
e.prorated_efc, 
b.report_code, 
d.school_choice_1, 
d.state_residence, 
e.stu_total_inc, 
b.title_iv, 
f.titleiv_elig, 
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, 
f.verf_selection_ind, 
f.verif_trk_flg, 
f.verifcation_type, 
c.vet_ed_benefit, 
d.veteran,
d.birthdate &gbirthdate,
decode(d.sfa_stdnt_gender,'2','F','1','M',' ') &gsex
  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_Application_stat &f.application_stat $application_stat_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_efc_status  &f.efc_status $efc_status_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  ! UMREPORT-218 PSA 5/15  
     
  
  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 $efc_status_descr < ' ' 
   Let $efc_status_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,&A.STRM,#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,5000) = 0 
    show 'Records processed:' #Fin_count
    commit
  end-if
  DO Insert_Fin_Aid_Tbl 
  
FROM ps_stdnt_awrd_disb a, 
ps_um_ityp_fa_vw b, 
ps_stdnt_awd_per c, 
ps_isir_control f,
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_balance <> 0  OR A.ACCEPT_balance <> 0 OR A.DISBURSED_balance <> 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 = f.emplid (+) 
   AND a.institution = f.institution (+) 
   AND a.aid_year = f.aid_year (+) 
   AND (f.effdt = ( 
 SELECT MAX(f_ed.effdt) 
  FROM ps_isir_control f_ed 
 WHERE f.emplid = f_ed.emplid 
   AND f.institution = f_ed.institution 
   AND f.aid_year = f_ed.aid_year 
   AND f_ed.effdt <= sysdate) 
    OR F.EFFDT IS NULL) 
   AND (f.effseq = ( 
 SELECT MAX(f_es.effseq) 
  FROM ps_isir_control f_es 
 WHERE f.emplid = f_es.emplid 
   AND f.institution = f_es.institution 
   AND f.aid_year = f_es.aid_year 
   AND f.effdt = f_es.effdt) 
    OR F.EFFSEQ IS NULL) 
   AND f.emplid = d.emplid (+) 
   AND f.institution = d.institution(+) 
   AND f.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.EFFSEQ IS NULL) 
   AND f.emplid = e.emplid (+) 
   AND f.institution = e.institution (+) 
   AND f.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,
SUM(A1.ITEM_AMT) &ITEM_AMOUNT
   Let #housing_amt = &ITEM_AMOUNT
FROM sysadm.PS_ITEM_SF A1, SYSADM.PS_UM_ITYP_FA_VW B1
WHERE  A1.ITEM_TYPE = B1.ITEM_TYPE
AND A1.BUSINESS_UNIT = B1.SETID
AND A1.AID_YEAR = B1.AID_YEAR
AND B1.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
END-SELECT
end-procedure
!**********************************************************************
! Insert_Fin_Aid_Tbl      [Generate PS_UM_FIN_AID_VW Table]       *  
!**********************************************************************
Begin-Procedure  Insert_Fin_Aid_Tbl 
let $Calling_Procedure = 'Insert_Fin_Aid_Tbl'
let $housing = 'Off-Campus'
  
BEGIN-SQL 
INSERT INTO SYSADM.PS_UM_FIN_AID_BLD
VALUES
(
&a.emplid,
NVL(&a.aid_year,' '),
NVL(&a.strm,' '),
NVL(&a.disbursement_id,' '),
NVL(&a.institution,' '),
NVL(&a.item_type,' ') ,
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,
NVL(&a.acad_career,' '),
$acad_career_descr,                           ! acad_career_descr
NVL(&glast_name ,' '),
NVL(&gfirst_name,' '),
NVL(Substr(&gmiddle_name, 1 ,1),' '),        ! middle_initial
NVL(&gname_suffix,' '),
NVL(&a.accept_balance, 0),
CASE WHEN &b.fin_aid_type  <> 'W' AND &b.disburse_method = 'N' AND &a.disbursed_balance  = 0 THEN &a.accept_balance WHEN &b.fin_aid_type  <> 'W' AND &b.disburse_method = ' ' AND &a.disbursed_balance  = 0 THEN &a.accept_balance ELSE &a.disbursed_balance END,
NVL(&b.aggregate_area,' '),
NVL(&d.agi, 0),
NVL(&f.app_stat_dt ,'01-JAN-1901'),
NVL(&f.application_stat,' '),
$application_stat_descr ,                      ! application stat description
NVL(&c.award_period,' '),
$award_period_descr,                           ! award_period_descr,
&um_calc_par_totinc,
NVL(&b.charge_priority,' '),
NVL(&d.children,' '),
NVL(&d.citizenship_status,' '),
NVL(&d.dependents,' '),
NVL(&d.depndncy_stat,' '),
$depndncy_stat_descr,                          ! depndncy_stat_descr
NVL(&b.disburse_method,' '),
$disburse_method_descr ,                       ! disburse_method_descr 
NVL(&a.disbursed_balance, 0),
NVL(&f.dt_app_received,'01-JAN-1901'),
NVL(&d.dt_app_completed,'01-JAN-1901'),
NVL(&f.efc_status,' ') ,
$efc_status_descr,                             !  efc_status_descr 
NVL(&b.fa_source,' '),
$fa_source_descr,                              !  fa_SOURCE_DESCR
NVL(&c.fed_efc, 0),
NVL(&c.fed_need, 0),
NVL(&c.fed_need_base_aid, 0),
NVL(&b.fed_or_inst,' '),
NVL(&c.fed_ovrawd_amt, 0),
NVL(&c.fed_ovrawd_coa, 0),
NVL(&c.fed_parent_contrb, 0),
NVL(&c.fed_special_aid, 0),
NVL(&c.fed_stdnt_contrb, 0),
NVL(&c.fed_total_aid, 0),
NVL(&c.fed_unmet_coa, 0),
NVL(&c.fed_unmet_need, 0),
NVL(&c.fed_year_coa, 0),
NVL(&b.federal_id,' '),
$federal_id_descr,                            ! federal_id_descr
NVL(&b.fin_aid_type,' '),
$fin_aid_type_descr,                          ! fin_aid_type_descr
NVL(&d.first_bach_degree,' '),
NVL(&d.fisap_tot_inc, 0),
NVL(&d.graduate,' '),
NVL(&d.graduate_student,' '),
NVL(&f.hold_ind,' '),
$housing_code_descr_long,
NVL(&d.hs_grad_dt ,'01-JAN-1901'),
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,
NVL(&c.isir_calc_efc, 0),
NVL(&b.descr,' '),
NVL(&b.item_type_cd,' '),
$item_type_cd_descr  ,                          ! UMREPORT-218 PSA 5/15  
NVL(&b.keyword1,' '),
NVL(&b.keyword2,' '),
NVL(&b.keyword3,' '),
NVL(&b.loan_program,' '),
$loan_program_descr,                          ! loan_program_descr
NVL(&d.marital_stat,' '),
NVL(&d.married,' '),
NVL(&b.need_based,' '),
NVL(&a.offer_balance, 0),
NVL(&d.orphan,' '),
NVL(&f.pell_eligibility,' '),
NVL(&e.primary_efc, 0),
NVL(&e.prorated_efc, 0),
NVL(&b.report_code,' '),
NVL(&d.school_choice_1,' '),
NVL(&d.state_residence,' ') ,
NVL(&e.stu_total_inc, 0),
NVL(&b.title_iv,' '),
NVL(&f.titleiv_elig,' '),
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,' ') ,
NVL(&f.verf_selection_ind,' '),
NVL(&f.verif_trk_flg,' '),
NVL(&f.verifcation_type,' '),
NVL(&c.vet_ed_benefit, 0),
NVL(&d.veteran,' '),
NVL(&gsex,' '),
NVL(&gbirthdate,to_date('01-01-1901','MM-DD-YYYY')),
&d.housing_code_1
)
END-SQL
End-Procedure Insert_Fin_Aid_Tbl
begin-procedure Blasto_Data
Begin-select
count(*) &CTR_UM_BLD
FROM SYSADM.PS_UM_FIN_AID_BLD buildies
end-select
if &CTR_UM_BLD > 0	 
    begin-sql 
    DELETE  FROM ps_um_fin_aid 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_AID ( SELECT * FROM SYSADM.PS_UM_FIN_AID_BLD)
   END-SQL
   
   BEGIN-SQL
   COMMIT
   END-SQL
   show 'Rows inserted from UM_FIN_AID_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
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