SELECT DISTINCT mother.shop_code, mother.shop_name, accredited, SUM(amount1),SUM(amount2)
FROM
(SELECT shop_code, shop_name, 
         DECODE(accredit_tag,'Y','ACCREDITED SHOP','NON-ACCREDITED SHOP') accredited
   FROM motorshop
WHERE NVL(accredit_tag,'N') = NVL(:accredit_tag,accredit_tag)
ORDER BY shop_code
) mother,
( SELECT DISTINCT a.shop_name, b.line_cd, b.subline_cd, b.iss_cd, b.clm_yy, b.clm_seq_no, b.shop_code,
           SUBSTR(b.line_cd||'-'||b.subline_cd||'-'||b.iss_cd||'-'||
                LTRIM(TO_CHAR(b.clm_yy,'09'))||'-'||LTRIM(TO_CHAR(b.clm_seq_no)),1,20) claimno,
--           decode(a.accredit_tag,'N','NON-ACCREDITED','ACCREDITED') accredit,
       DECODE (TO_CHAR(c.ref_date,'YY'),
	                    TO_CHAR(:period_from,'YY'), 
		                             SUM(NVL(e.parts,0)+NVL(e.scpe_wrk,0)+NVL(e.out_wrks,0)+NVL(e.vat,0)
									 +NVL(e.parts_supp,0)-(NVL(e.deductible,0)+NVL(e.depreciation,0)))
			 ,0 ) amount1,
       DECODE (TO_CHAR(c.ref_date,'YY'),
	                    TO_CHAR(ADD_MONTHS(:period_from,-12),'YY'), 
		                             SUM(NVL(e.parts,0)+NVL(e.scpe_wrk,0)+NVL(e.out_wrks,0)+NVL(e.vat,0)
									 +NVL(e.parts_supp,0)-(NVL(e.deductible,0)+NVL(e.depreciation,0)))
			 ,0 ) amount2		 
    FROM evaluate e, inspect_hist b, claim_loa c, motorshop a,
          issource d
 WHERE b.iss_cd = d.iss_cd
   AND e.line_cd = c.line_cd
   AND e.subline_cd = c.subline_cd
   AND e.iss_cd = c.iss_cd
   AND e.clm_yy = c.clm_yy
   AND e.clm_seq_no = c.clm_seq_no 
   AND e.eval_yy = c.eval_yy
   AND e.eval_seq_no = c.eval_seq_no
   AND NOT EXISTS (SELECT 'x' 
                     FROM eval_repl 
                    WHERE eval_yy_o = e.eval_yy
                      AND eval_seq_no_o = e.eval_seq_no)
   AND b.line_cd = c.line_cd
   AND b.subline_cd = c.subline_cd
   AND b.iss_cd = c.iss_cd
   AND b.clm_yy = c.clm_yy
   AND b.clm_seq_no = c.clm_seq_no
   AND b.pol_seq_no = c.pol_seq_no
   AND b.item_no = c.item_no
   AND b.shop_code = c.shop_code
   AND b.history_no = (SELECT MAX(history_no)
                          FROM inspect_hist
                         WHERE line_cd = b.line_cd
                           AND subline_cd = b.subline_cd
                           AND iss_cd = b.iss_cd
                           AND clm_yy = b.clm_yy
                           AND clm_seq_no = b.clm_seq_no)
   AND b.shop_code = a.shop_code
   AND b.line_cd = 21
   --&issource
--   and b.subline_cd = 1
--   and b.iss_cd = nvl(:issource, b.iss_cd)
--   and b.clm_yy = 0
--   and b.clm_seq_no = 100009
--     and nvl(active_tag,'N') = 'Y'
--   and a.accredit_tag = 'Y'
   AND 
   (
   c.ref_date BETWEEN :period_from AND :period_to
   OR
   c.ref_date BETWEEN ADD_MONTHS(:period_from,-12) AND ADD_MONTHS(:period_to,-12)
   )
 --order by shop_name, b.line_cd, b.subline_cd, b.iss_cd, b.clm_yy, b.clm_seq_no
GROUP BY a.shop_name, b.line_cd, b.subline_cd, b.iss_cd, b.clm_yy, b.clm_seq_no, b.shop_code,
              a.accredit_tag, c.ref_date
) m_child
WHERE mother.shop_code = m_child.shop_code
GROUP BY mother.shop_code, mother.shop_name, accredited--, sum(amount1),sum(amount2)
ORDER BY mother.shop_code