Query

SELECT Name, Code, Term, LPAD(CAST(AMOUNT * 100 AS VARCHAR2(7)), 7, '0') as Amount
FROM( 
SELECT Name, Code, Term, sum(amount) as amount
(
SELECT t1.Name, 
CASE WHEN t2.Code = '200701' then 'SU' when t2.Code = '200702' then 'AU' when t2.Code = '200703' then 'WI' 
when t2.Code = '200704' then 'SP' end as Code,
CASE WHEN t2.Term IN ('TU01', 'TU03, 'TU04') then 'TU' WHEN t2.Term = 'REGF' then 'OF' end as Term,
t2.Amount
FROM User t1, Charges t2
WHERE t1.Id = T2.Id ) as temp
GROUP BY Name, Code, Term) AS sub

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:

select sfrstcr_pidm,
decode(substr(sfrstcr_term_code,5,2),'01','SM','02','AU','03','WI','04','SP','AU'),
decode(TBRACCD_DETAIL_CODE,'TU01','TU','TU02','TU','TU03','TU','TU04','TU','REGF','OF','TU0S','OS','ATI','CF','CLIN','CF','DEF','CF','DHF','CF','EMST','CF','HSI','CF','LABC','CF','LABF','CF','NKIT','CF','NSGF','CF','RESX','CF','RKIT','CF','MEDS','CF','CF'),
LPAD(CAST(sum(tbraccd_amount)*100 as varchar(7)),7,'0')
    from   tbraccd,sfrstcr, 
           (select ssbsect_term_code trm,
                   ssbsect_crn crn,
                   f_hei_census(ssbsect_ptrm_start_date,
                                ssbsect_ptrm_end_date) census,
                   to_char(f_hei_census(ssbsect_ptrm_start_date,
                                ssbsect_ptrm_end_date),'DD-MON-YYYY') cnsdte
            from   ssbsect
            where  trunc(ssbsect_ptrm_end_date) between
                      to_date('18-JUN-2007','DD-MON-YYYY') and
                      to_date('15-JUN-2008','DD-MON-YYYY')
           ) crse
    where  (
            (sfrstcr_rsts_code in ('RE','RW','AU')
             and
             trunc(sfrstcr_add_date) <= crse.census
            )
            or
            (sfrstcr_rsts_code not in ('RE','RW','AU')
             and
             trunc(sfrstcr_rsts_date) > crse.census
            )
           )
and (TBRACCD_DETAIL_CODE = 'TU01' or TBRACCD_DETAIL_CODE = 'TU02' or TBRACCD_DETAIL_CODE = 'TU03' or TBRACCD_DETAIL_CODE = 'TU04' or TBRACCD_DETAIL_CODE = 'REGF' or TBRACCD_DETAIL_CODE = 'TU0S' or TBRACCD_DETAIL_CODE = 'ATI' or TBRACCD_DETAIL_CODE = 'CLIN' or TBRACCD_DETAIL_CODE = 'DEF' or TBRACCD_DETAIL_CODE = 'DHF' or TBRACCD_DETAIL_CODE = 'EMST' or TBRACCD_DETAIL_CODE = 'HSI' or TBRACCD_DETAIL_CODE = 'LABC' or TBRACCD_DETAIL_CODE = 'LABF' or TBRACCD_DETAIL_CODE = 'NKIT' or TBRACCD_DETAIL_CODE = 'NSGF' or TBRACCD_DETAIL_CODE = 'RESX' or TBRACCD_DETAIL_CODE = 'RKIT' or TBRACCD_DETAIL_CODE = 'MEDS')
and tbraccd_pidm = sfrstcr_pidm
and TBRACCD_TERM_CODE = crse.trm
     and sfrstcr_crn = crse.crn
     and sfrstcr_term_code = crse.trm
and sfrstcr_pidm=120000;

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33: