CREATE TABLE stock_valuation AS (

SELECT m.group_code::text                                                       AS GROUP_CODE,
       m.sku::text                                                              AS SKU,
       m.brn_code::text                                                         AS BRN_CODE,
       m.stktype_code::text                                                     AS STKTYPE_CODE,
       ss.supplier_price_curr_cost::numeric(16,3)                               AS SUPPLIER_CURR_COST,
       sk.sku_price_curr_cost::numeric(16,3)                                    AS SKU_CURR_COST,
       SUM(m.qty_on_hand_01)::int4                                              AS QTY_ON_HAND,
       SUM(ss.supplier_price_curr_cost * m. qty_on_hand_01)::numeric(16,3)      AS QTY_ON_HAND_COST,
       SUM(sk.sku_price_curr_cost * m.qty_on_hand_01 * f.rate)::numeric(16,3)   AS QTY_ON_HAND_RSP,
       (SUM(ss.supplier_price_curr_cost * m.qty_on_hand_01) -
        SUM(sk.sku_price_curr_cost * m.qty_on_hand_01 * f.rate))::numeric(16,3) AS QTY_ON_HAND_MARKUP
FROM master_fpp_values m,
     master_sku_descr s,
     master_branch_descr b,
     supplier_price ss,
     price_tmpl_det p,
     sku_price sk,
     forex f
WHERE m.fpp_code = '200307'
AND   m.brn_code = b.brn_code
AND   m.sku = s.sku
AND   m.group_code = s.group_code
AND   s.control_code = '0'
AND   m.brn_code = p.branch_cde
AND   b.brn_code = p.branch_cde
AND   ss.group_cde = p.group_cde
AND   ss.price_tmpl_hdr_cde = p.price_tmpl_hdr_cde
AND   ss.price_tmpl_hdr_reg = p.price_tmpl_hdr_reg
AND   ss.group_cde = m.group_code
AND   ss.sku_mst_cde = m.sku
AND   ss.supplier_cde = m.supplier_code
AND   sk.price_tmpl_hdr_cde = p.price_tmpl_hdr_cde
AND   sk.price_tmpl_hdr_reg = p.price_tmpl_hdr_reg
AND   sk.group_cde = m.group_code
AND   sk.sku_mst_cde = m.sku
AND   b.country_code = f.from_ctry
AND   f.to_ctry = 'ZAF'
AND   f.fpp_code = '200307'
GROUP BY m.group_code,
         m.sku,
         m.brn_code,
         m.stktype_code,
         ss.supplier_price_curr_cost,
         sk.sku_price_curr_cost
);
