set termout off
set pagesize 0
set linesize 600
set newpage 0
set verify off
set echo off
set heading off
set feedback off
--==========================================================================
-- Sallie Mae E-Billing Extract
--==========================================================================
spool c:\spooledfiles\slma_extract.txt
SELECT  'HR'
        ||rpad('000257300',9)  
        ||rpad('000899984',9)  
        ||rpad('000257300',9) 
        ||rpad(to_char(sysdate,'YYYYMMDD'),8)
        ||rpad(to_char(sysdate,'hh24miss'),6)
        ||'T'
        ||'TB'
        ||rpad(' ',1)
        ||'IN'
        ||rpad(' ',28)   
        ||rpad('Dartmouth College',30)
        ||rpad('Ron Hiser',30)
        ||rpad(' ',462)
        ||rpad('*',1)
FROM DUAL;
SELECT  '01'
        ||rpad('E',2)
        ||rpad('Student Account Statement',40)
        ||rpad(a.ID,16)
        ||rpad(a.LAST_NAME,35)
        ||rpad(a.FIRST_NAME,25)
	||rpad(nvl(substr(a.middle_name,1,1),' '),1)
        ||rpad(nvl(a.fml_name,' '),60)
	||rpad(nvl(j.PERM_STREET1,' '),40)
	||rpad(nvl(j.PERM_STREET2,' '),40)
	||rpad(nvl(j.PERM_STREET3,' '),40)
        ||rpad(' ',40)
	||rpad(nvl(j.PERM_CITY,' '),25)
	||rpad(nvl(j.PERM_STATE,' '),2)
	||rpad(nvl(j.PERM_ZIP,' '),5)
        ||rpad(nvl(j.PERM_ZIP,' '),4)
	||case when j.PERM_NATION is null then rpad('USA',25) else rpad(' ',25) end
        ||case when j.PERM_NATION is not null then rpad(stvnatn_code,2)else rpad(' ',2) end
        ||rpad(' ',50)
        ||rpad(nvl(to_char(a.birth_date,'YYYYMMDD'),' '),8)
	||rpad(nvl(a.BLITZ_ADDRESS,' '),50)
	||rpad(nvl(a.CURRENT_TERM_LEVEL,' ')||nvl(a.class_year,' '),16)
        ||rpad(' ',71)
        ||rpad('*',1) 
        ||'02'
        ||rpad(to_char(AR_TRANSACTIONS_SLM.BILL_DATE,'YYYYMMDD'),15)
        ||lpad(to_char(GET_PREVIOUS_BALANCE(a.pidm) + nvl(c.CHARGES,'0')- nvl(d.PAYMENTS,'0') - 0 - nvl(e.AUTHAID,'0') - nvl(g.ANTICIPATEDAID,'0')),12,'0')
        ||rpad(to_char(AR_TRANSACTIONS_SLM.DUE_DATE,'YYYYMMDD'),15)
        ||lpad(nvl(to_char(to_number(c.CHARGES)),'0'),12,'0')
        ||lpad(nvl(to_char(to_number(d.payments)),'0'),12,'0')
        ||rpad(' ',12)
        ||rpad(' ',12)
        ||rpad(' ',12)
        ||lpad(nvl(to_char(to_number(GET_PREVIOUS_BALANCE(a.pidm))),'0'),12,'0')
        ||lpad(to_char(to_number(GET_PREVIOUS_BALANCE(a.pidm) + nvl(c.CHARGES,'0') - nvl(d.PAYMENTS,'0'))),12,'0')
        ||rpad(' ',12)
        ||rpad(' ',12)
        ||rpad(' ',12)
        ||rpad(' ',10)
        ||lpad(nvl(to_char(to_number(e.AUTHAID)),'0'),10,'0')
        ||lpad(nvl(to_char(to_number(g.ANTICIPATEDAID)),'0'),10,'0')
        ||rpad(' ',1)
        ||rpad(' ',10)
        ||rpad(' ',10)
        ||rpad(nvl(a.BLITZ_ADDRESS,' '),50)
        ||rpad(' ',12)
        ||rpad(' ',12)
        ||rpad(' ',12)
        ||rpad(' ',10)
        ||rpad(' ',10)
        ||rpad(' ',3)
        ||rpad(' ',12)
        ||rpad(' ',12)
        ||rpad(' ',3)
        ||rpad(' ',5)
        ||rpad(' ',12)
        ||rpad(' ',5)
        ||rpad(' ',2)
        ||rpad(' ',2)
        ||rpad(' ',1)
        ||rpad(' ',2)
        ||rpad(' ',219)
        ||rpad('*',1)
        ||'03'
        ||rpad(nvl(AR_TRANSACTIONS_SLM.TERM_CODE,' '),15)
        ||rpad(to_char(AR_TRANSACTIONS_SLM.EFF_DATE,'YYYYMMDD'),10)
        ||rpad(nvl(AR_TRANSACTIONS_SLM.TRANS_DESC,' '),35)
        ||lpad(nvl(to_char(to_number(AR_TRANSACTIONS_SLM.charges)),'0'),12,'0')
        ||lpad(nvl(to_char(to_number(AR_TRANSACTIONS_SLM.payments)),'0'),12,'0')
        ||rpad(' ',15)
        ||rpad(' ',2)
        ||lpad(' ',12,'0')
        ||lpad(' ',12,'0')
        ||rpad(' ',45)
        ||rpad(' ',2)
        ||lpad(' ',12,'0')
        ||lpad(' ',12,'0')
        ||lpad(' ',12,'0')
        ||lpad(' ',12,'0')
        ||lpad(' ',12,'0')
        ||lpad(' ',12,'0')
        ||lpad(' ',12,'0')
        ||rpad(' ',90)
        ||rpad(' ',251)
        ||rpad('*',1)
        ||'04'
        ||rpad(' ',90)
        ||rpad(' ',507)
        ||rpad('*',1)
        ||'05'
        ||rpad(' ',90)
        ||rpad(' ',2)
        ||rpad(' ',505)
        ||rpad('*',1)
FROM sturecords.BASIC_STUDENT_INFO_DI a,sturecords.AL_BIO_ADDRESS_PERM_DI j,stvnatn,charges c,payments d,ar_authorized_aid e,AR_ANTICIPATED_AID g,AR_TRANSACTIONS_SLM
WHERE a.student_status = 'AS'
and a.pidm = j.pidm 
and j.PERM_NATION = stvnatn_nation(+)
and a.pidm = c.pidm(+)
and a.pidm = d.PIDM(+)
and c.bill_date = d.BILL_DATE
and a.pidm = e.PIDM(+)
and a.pidm = g.pidm(+)
and AR_TRANSACTIONS_SLM.BILL_DATE = '11-MAY-07'
and a.pidm = AR_TRANSACTIONS_SLM.pidm
and c.BILL_DATE = AR_TRANSACTIONS_SLM.BILL_DATE
and a.pidm = '1259878'
order by a.sort_name;
SELECT 'TR'
       ||lpad(COUNT(z.pidm),5,'0')
       ||lpad(COUNT(z.pidm),5,'0')
       ||rpad(to_char(sysdate,'YYYYMMDD'),8)
       ||rpad(to_char(sysdate,'hh24miss'),6)
       ||rpad(' ',573)
       ||rpad('*',1)
FROM sturecords.BASIC_STUDENT_INFO_DI z,sturecords.AL_BIO_ADDRESS_PERM_DI j,stvnatn,charges c,payments d,ar_authorized_aid e,AR_ANTICIPATED_AID g,AR_TRANSACTIONS_SLM
WHERE z.student_status = 'AS'
and z.pidm = j.pidm 
and j.PERM_NATION = stvnatn_nation(+)
and z.pidm = c.pidm(+)
and z.pidm = d.PIDM(+)
and c.bill_date = d.BILL_DATE
and z.pidm = e.PIDM(+)
and z.pidm = g.pidm(+)
and AR_TRANSACTIONS_SLM.BILL_DATE = '11-MAY-07'
and z.pidm = '1259878'
and z.pidm = AR_TRANSACTIONS_SLM.pidm
and c.BILL_DATE = AR_TRANSACTIONS_SLM.BILL_DATE;
spool off
set linesize 75
set pagesize 35
set feedback on
set headings on