temp

WITH TEMP AS(
    SELECT A.doc_no, A.doc_date, B.product_code, A.gross_sell_price,
           A.nett_sell_price_system,  A.nett_sell_price, A.nett_sell_price_verified, A.qty, A.qty_verified, A.flg_unused,
           A.nett_sell_price_verified * A.qty_verified AS sub_total, C.flg_denda,
           A.outlet_id, A.promo_code_verified, A.status_item, B.product_id, A.trx_mobile_pos_item_balance_id
    FROM i_trx_mobile_pos_item_balance A
    INNER JOIN m_product B  ON A.product_id = B.product_id
    INNER JOIN i_verification_trx_mobile_pos_item C  ON A.trx_mobile_pos_item_id = C.ref_item_id
    UNION
    SELECT A.doc_no, A.doc_date, B.product_code, A.gross_sell_price,
        A.nett_sell_price_system, A.nett_sell_price, A.nett_sell_price_verified, A.qty, A.qty_verified, A.flg_unused,
        A.nett_sell_price_verified * A.qty_verified AS sub_total, C.flg_denda,
        A.outlet_id, A.promo_code_verified, A.status_item, B.product_id, A.trx_mobile_pos_item_balance_id
    FROM i_trx_mobile_pos_item_balance A
    INNER JOIN m_product B ON A.product_id = B.product_id
    INNER JOIN i_verification_trx_mobile_pos_item C ON A.trx_mobile_pos_item_id = C.verification_trx_item_id
        AND C.ref_item_id = -99
    UNION
    SELECT A.doc_no, A.doc_date, B.product_code, A.gross_sell_price,
        A.nett_sell_price_system, A.nett_sell_price, A.nett_sell_price_verified, A.qty, A.qty_verified, A.flg_unused,
        A.nett_sell_price_verified * A.qty_verified AS sub_total, A.flg_denda,
        A.outlet_id, A.promo_code_verified, A.status_item, B.product_id, A.trx_mobile_pos_item_balance_id
    FROM i_trx_mobile_pos_item_balance A
    INNER JOIN m_product B ON A.product_id = B.product_id
    WHERE NOT EXISTS(
        SELECT 1 FROM i_verification_trx_mobile_pos_item C
        INNER JOIN i_spg_fine D ON C.verification_trx_item_id = D.ref_item_id
        WHERE A.trx_mobile_pos_item_id = C.verification_trx_item_id)
    )
SELECT A.trx_mobile_pos_item_balance_id, C.group_outlet_name, D.outlet_name, A.doc_no, A.doc_date, A.promo_code_verified,
    A.status_item,A.flg_unused, A.product_code, A.gross_sell_price,
    A.nett_sell_price_system, A.nett_sell_price, A.nett_sell_price_verified, CAST(A.qty AS BIGINT) AS qty, CAST(A.qty_verified AS BIGINT) AS qty_verified,
    A.flg_denda AS flg_denda, f_get_fine_amount_spg(A.trx_mobile_pos_item_balance_id) AS denda, A.flg_unused, A.sub_total, COALESCE(E.purch_price, 0) AS hpp,
    SUM(A.qty_verified* COALESCE(E.purch_price, 0)) AS sub_total_hpp
FROM TEMP A
INNER JOIN m_outlet_group B ON A.outlet_id = B.outlet_id
INNER JOIN m_group_outlet C ON B.group_outlet_id = C.group_outlet_id
INNER JOIN i_outlet D ON A.outlet_id = D.outlet_id
LEFT JOIN m_purch_price_product E ON A.product_id = E.product_id
    AND E.ou_id = 10
    AND D.tenant_id = E.tenant_id
    AND A.doc_date BETWEEN E.date_from AND E.date_to
WHERE C.group_outlet_id = 11
    AND A.outlet_id = 173
    AND A.doc_date BETWEEN '20170901' AND '20170930'
    AND flg_unused = 'N' AND status_item = 'Y'
GROUP BY A.trx_mobile_pos_item_balance_id, C.group_outlet_name, D.outlet_name, A.doc_no, A.doc_date, A.promo_code_verified,
    A.status_item, A.flg_unused,A.product_code, A.gross_sell_price,
    A.nett_sell_price_system, A.nett_sell_price, A.nett_sell_price_verified, A.qty, A.qty_verified,
    A.flg_denda, A.flg_unused, A.sub_total, E.purch_price, A.trx_mobile_pos_item_balance_id