/* Formatted on 2007/11/12 16:50 (Formatter Plus v4.8.8) */
SELECT   '[' || a.c_acnt || '] ' || b.s_acnt_h_nm || '/'
         || b.s_acnt_s_nm acnt,
         '[' || a.c_trust_cc || '] ' || d.s_cc_name trustcc,
            '['
         || a.c_buditem
         || a.c_buddtl
         || '] '
         || s_iname
         || '/'
         || s_dname budcode,
           SUM (DECODE (SUBSTR (a.m_yymm, 5, 2), '77', a.n_conf_amt, 0))
         / 1000 conf,
           SUM (DECODE (SUBSTR (a.m_yymm, 5, 2),
                        '77', 0,
                        '13', 0,
                        a.n_conf_amt
                       )
               )
         / 1000 total
    FROM tb_fixexppl a,
         com.vw_xxx_acnt b,
         tb_budgcode c,
         (SELECT c_cc, s_cc_name
            FROM tb_costcntr
          UNION
          SELECT 'XXXXX' c_cc, 'ÀÚü°æºñ' s_cc_name
            FROM DUAL) d,
         tb_acnttrst t
   WHERE a.c_acnt = b.c_acnt
     AND a.c_acnt = c.c_acnt
     AND a.c_buditem = c.c_buditem
     AND a.c_buddtl = c.c_buddtl
     AND a.c_acnt <> '7100201'
     AND a.c_trust_cc = d.c_cc
     AND a.c_acnt = t.c_acnt
     AND a.c_trust_cc = t.c_trust_cc
     AND a.c_plan = 'BA'
     AND a.m_yymm BETWEEN '200801' AND '200877'
     AND a.c_cc LIKE '7%'
     AND a.f_trust = 'N'
     AND t.f_create <> '3'
GROUP BY '[' || a.c_acnt || '] ' || b.s_acnt_h_nm || '/' || b.s_acnt_s_nm,
         '[' || a.c_trust_cc || '] ' || d.s_cc_name,
         a.f_trust,
         '[' || a.c_buditem || a.c_buddtl || '] ' || s_iname || '/' || s_dname
UNION
SELECT   '[' || a.c_acnt || '] ' || b.s_acnt_h_nm || '/'
         || b.s_acnt_s_nm acnt,
         '[XXXXX] ÀÚü°æºñ' trustcc,
         '[' || a.c_bud || '] ' || s_iname || '/' || s_dname budcode,
         SUM (a.n_conf_amt) / 1000 conf, NVL (MAX (total) / 1000, 0) total
    FROM tb_btripexp a,
         com.vw_xxx_acnt b,
         tb_budgcode c,
         (SELECT   SUBSTR (m_yymm, 1, 4) y_year, c_plan c_plan, c_cc c_cc,
                   c_acnt c_acnt, c_buditem || c_buddtl c_bud,
                   SUM (n_conf_amt) total
              FROM tb_fixexppl
             WHERE c_acnt = '7100201'
               AND c_trust_cc = 'XXXXX'
               AND SUBSTR (m_yymm, 5, 2) BETWEEN '01' AND '12'
          GROUP BY SUBSTR (m_yymm, 1, 4),
                   c_plan,
                   c_cc,
                   c_acnt,
                   c_buditem || c_buddtl) e
   WHERE a.y_year = '2008'
     AND a.c_plan = 'BA'
     AND a.c_cc LIKE '7%'
     AND a.c_acnt = b.c_acnt
     AND a.c_acnt = c.c_acnt
     AND a.c_bud = c.c_buditem || c.c_buddtl
     AND a.y_year = e.y_year(+)
     AND a.c_plan = e.c_plan(+)
     AND a.c_acnt = e.c_acnt(+)
     AND a.c_cc = e.c_cc(+)
     AND a.c_bud = e.c_bud(+)
GROUP BY '[' || a.c_acnt || '] ' || b.s_acnt_h_nm || '/' || b.s_acnt_s_nm,
         '[' || a.c_bud || '] ' || s_iname || '/' || s_dname