query

WITH TEMP AS(
    SELECT A.doc_no, A.doc_date, 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, COALESCE(D.fine_amount,0) AS denda,
        A.outlet_id, A.promo_code_verified, A.status_item, A.product_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
    LEFT JOIN i_spg_fine D  ON C.verification_trx_item_id = D.ref_item_id
    UNION
    SELECT A.doc_no, A.doc_date, 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, COALESCE(D.fine_amount,0) AS denda,
        A.outlet_id, A.promo_code_verified, A.status_item, A.product_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
    LEFT JOIN i_spg_fine D  ON C.verification_trx_item_id = D.ref_item_id
    UNION
    SELECT A.doc_no, A.doc_date, 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, 'N' AS flg_denda, 0 AS denda,
                  A.outlet_id, A.promo_code_verified, A.status_item, A.product_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
        LEFT 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
            OR A.trx_mobile_pos_item_id = C.ref_item_id )
)
SELECT *
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'