with ou id

DROP VIEW IF EXISTS vw_outstanding_history_achievement_per_salesman;
CREATE OR REPLACE VIEW vw_outstanding_history_achievement_per_salesman AS
SELECT tenant_id, salesman_id, year_month,
    total_item_amount+total_warranty_item AS sales_amount,
    (total_item_amount+total_warranty_item+tax_item_amount+tax_warranty_item-total_purch_amount) AS gross_profit_amount
FROM ( SELECT COALESCE(SUM(f_get_amount_before_tax(B.qty_dlv_so * (D.gross_sell_price - D.discount_amount), D.flg_tax_amount, D.tax_percentage, f_get_digit_decimal_doc_curr(A.doc_type_id, D.curr_code), f_get_value_system_config_by_param_code(A.tenant_id, 'rounding.mode.non.tax'))), 0) AS total_item_amount,
        COALESCE(SUM(f_tax_rounding(A.tenant_id, f_get_amount_before_tax(B.qty_dlv_so * (D.gross_sell_price - D.discount_amount), D.flg_tax_amount, D.tax_percentage, f_get_digit_decimal_doc_curr(A.doc_type_id, C.curr_code), f_get_value_system_config_by_param_code(A.tenant_id, 'rounding.mode.non.tax')), D.tax_percentage)), 0) tax_item_amount,
        COALESCE(SUM(f_get_amount_before_tax(B.qty_dlv_so * H.warranty_sell_price, D.flg_tax_amount, D.tax_percentage, f_get_digit_decimal_doc_curr(A.doc_type_id, D.curr_code), f_get_value_system_config_by_param_code(A.tenant_id, 'rounding.mode.non.tax'))), 0) AS total_warranty_item,
        COALESCE(SUM(f_tax_rounding(A.tenant_id, f_get_amount_before_tax(B.qty_dlv_so * H.warranty_sell_price, D.flg_tax_amount, D.tax_percentage, f_get_digit_decimal_doc_curr(A.doc_type_id, C.curr_code), f_get_value_system_config_by_param_code(A.tenant_id, 'rounding.mode.non.tax')), D.tax_percentage)), 0) AS tax_warranty_item,
        COALESCE(SUM(F.price * B.qty_dlv_so), 0) AS total_purch_amount, C.salesman_id, SUBSTRING(A.doc_date, 0 , 7) AS year_month, A.tenant_id, A.ou_id
    FROM sl_do A
    INNER JOIN sl_do_item B ON A.do_id = B.do_id
    INNER JOIN sl_so C ON A.ref_id = C.so_id AND A.ref_doc_type_id = C.doc_type_id
    INNER JOIN sl_so_item D ON C.so_id = D.so_id AND B.ref_id = D.so_item_id
    LEFT OUTER JOIN sl_so_item_purchasing F ON F.so_item_id = D.so_item_id
    LEFT OUTER JOIN sl_so_warranty_item H ON H.so_item_id = D.so_item_id
    WHERE A.status_doc NOT IN ('R')
    AND NOT EXISTS (SELECT 1 FROM fi_po_internal_so_igs_tagging Z WHERE Z.so_id = C.so_id)
    GROUP BY C.salesman_id, A.doc_date, A.tenant_id, A.ou_id
) tt_in_do
UNION
SELECT tenant_id, salesman_id, year_month,
    total_item_amount+total_warranty_item AS sales_amount,
    (total_item_amount+total_warranty_item+tax_item_amount+tax_warranty_item-total_purch_amount) AS gross_profit_amount
FROM ( SELECT  COALESCE(SUM(-1 * f_get_amount_before_tax(COALESCE(B.qty_return * D.qty_so / NULLIF(D.qty_int, 0), 0) * (D.gross_sell_price - D.discount_amount), D.flg_tax_amount, D.tax_percentage, f_get_digit_decimal_doc_curr(A.doc_type_id, D.curr_code), f_get_value_system_config_by_param_code(A.tenant_id, 'rounding.mode.non.tax'))), 0) AS total_item_amount,
        COALESCE(SUM(-1 * f_tax_rounding(A.tenant_id, f_get_amount_before_tax(COALESCE(B.qty_return * D.qty_so / NULLIF(D.qty_int, 0), 0) * (D.gross_sell_price - D.discount_amount), D.flg_tax_amount, D.tax_percentage, f_get_digit_decimal_doc_curr(A.doc_type_id, D.curr_code), f_get_value_system_config_by_param_code(A.tenant_id, 'rounding.mode.non.tax')), D.tax_percentage)), 0) AS tax_item_amount,
        COALESCE(SUM(-1 * f_get_amount_before_tax(COALESCE(B.qty_return * D.qty_so / NULLIF(D.qty_int, 0), 0) * H.warranty_sell_price, D.flg_tax_amount, D.tax_percentage, f_get_digit_decimal_doc_curr(A.doc_type_id, D.curr_code), f_get_value_system_config_by_param_code(A.tenant_id, 'rounding.mode.non.tax'))), 0) AS total_warranty_item,
        COALESCE(SUM(-1 * f_tax_rounding(E.tenant_id, f_get_amount_before_tax(COALESCE(B.qty_return * D.qty_so / NULLIF(D.qty_int, 0), 0) * H.warranty_sell_price, D.flg_tax_amount, D.tax_percentage, f_get_digit_decimal_doc_curr(A.doc_type_id, D.curr_code), f_get_value_system_config_by_param_code(A.tenant_id, 'rounding.mode.non.tax')), D.tax_percentage)), 0) AS tax_warranty_item,
        COALESCE(SUM(G.price * D.qty_int), 0) AS total_purch_amount, E.salesman_id, SUBSTRING(A.doc_date, 0 , 7) AS year_month, A.tenant_id, A.ou_id
    FROM in_do_receipt A
    INNER JOIN in_do_receipt_item B ON A.do_receipt_id = B.do_receipt_id
    INNER JOIN in_balance_do_item C ON B.ref_id = C.do_item_id
    INNER JOIN sl_so_item D ON C.so_item_id = D.so_item_id
    INNER JOIN sl_so E ON D.so_id = E.so_id
    INNER JOIN sl_do F ON F.do_id = A.ref_id AND A.ref_doc_type_id = F.doc_type_id
    LEFT OUTER JOIN sl_so_item_purchasing G ON G.so_item_id = D.so_item_id
    LEFT OUTER JOIN sl_so_warranty_item H ON H.so_item_id = D.so_item_id
    WHERE A.status_doc NOT IN ('R')
        AND NOT EXISTS (SELECT 1 FROM fi_po_internal_so_igs_tagging Z WHERE Z.so_id = E.so_id)
    GROUP BY E.salesman_id, A.doc_date, A.tenant_id, A.ou_id
) tt_temp_do_receipt
UNION
SELECT tenant_id, salesman_id, year_month,
    total_item_amount+total_warranty_item AS sales_amount,
    (total_item_amount+total_warranty_item+tax_item_amount+tax_warranty_item-total_purch_amount) AS gross_profit_amount
FROM ( SELECT COALESCE(SUM(f_get_amount_before_tax_and_disc((B.qty_realization * D.qty_so * D.gross_sell_price) / D.qty_int, (H.regular_disc_amount + H.promo_disc_amount) * (B.qty_realization * D.qty_so) / (D.qty_int / H.qty_dlv_so), D.flg_tax_amount,
                D.tax_percentage, f_get_digit_decimal_doc_curr(A.doc_type_id, D.curr_code), f_get_value_system_config_by_param_code(A.tenant_id, 'rounding.mode.non.tax'))), 0) AS total_item_amount,
        COALESCE(SUM(f_get_amount_before_tax_and_disc((B.qty_realization * D.qty_so * D.gross_sell_price) / D.qty_int, (B.qty_realization * D.qty_so * D.discount_amount) / D.qty_int, D.flg_tax_amount, D.tax_percentage, f_get_digit_decimal_doc_curr(A.doc_type_id, D.curr_code), f_get_value_system_config_by_param_code(A.tenant_id, 'rounding.mode.non.tax'))), 0) AS tax_item_amount,
        COALESCE(SUM(((B.qty_realization * D.qty_so) / D.qty_int) * I.warranty_sell_price) ,0) AS total_warranty_item,
        COALESCE(SUM(f_tax_rounding(A.tenant_id, f_get_amount_before_tax((B.qty_realization * D.qty_so * I.warranty_sell_price) / D.qty_int, D.flg_tax_amount, D.tax_percentage, f_get_digit_decimal_doc_curr(A.doc_type_id, D.curr_code), f_get_value_system_config_by_param_code(A.tenant_id, 'rounding.mode.non.tax')), D.tax_percentage)), 0) AS tax_warranty_item,
        COALESCE(SUM(J.price * D.qty_int), 0) AS total_purch_amount, E.salesman_id, SUBSTRING(A.doc_date, 0 , 7) AS year_month, A.tenant_id, A.ou_to_id AS ou_id
    FROM in_inventory A
    INNER JOIN in_inventory_item B ON A.inventory_id = B.inventory_id
    INNER JOIN in_balance_do_item C ON B.ref_item_id = C.do_item_id
    INNER JOIN sl_so_item D ON C.so_item_id = D.so_item_id
    INNER JOIN sl_so E ON D.so_id = E.so_id
    INNER JOIN sl_do F ON E.so_id = F.ref_id
        AND E.doc_type_id = F.ref_doc_type_id
    INNER JOIN sl_do_item G ON F.do_id = G.do_id
    INNER JOIN sl_so_balance_invoice H ON H.ref_id = F.do_id
        AND H.ref_item_id = G.do_item_id
        AND H.do_receipt_item_id = -99
    LEFT OUTER JOIN sl_so_warranty_item I ON G.ref_id = I.so_item_id
    LEFT OUTER JOIN sl_so_item_purchasing J ON J.so_item_id = D.so_item_id
    WHERE A.status_doc NOT IN ('R')
        AND A.doc_type_id IN (502)
        AND NOT EXISTS (SELECT 1 FROM fi_po_internal_so_igs_tagging Z WHERE Z.so_id = E.so_id)
    GROUP BY E.salesman_id, A.doc_date, A.tenant_id, A.ou_to_id
) tt_temp_rn
UNION
SELECT tenant_id, salesman_id, year_month,
    total_item_amount+total_warranty_item AS sales_amount,
    (total_item_amount+total_warranty_item+tax_item_amount+tax_warranty_item-total_purch_amount) AS gross_profit_amount
FROM (SELECT COALESCE(SUM(f_get_amount_before_tax(C.qty_so * (B.gross_sell_price - B.discount_amount), B.flg_tax_amount, B.tax_percentage, f_get_digit_decimal_doc_curr(A.doc_type_id, B.curr_code), f_get_value_system_config_by_param_code(A.tenant_id, 'rounding.mode.non.tax'))), 0) AS total_item_amount,
        COALESCE(SUM(f_tax_rounding(A.tenant_id, f_get_amount_before_tax(C.qty_so * (B.gross_sell_price - B.discount_amount), B.flg_tax_amount, B.tax_percentage, f_get_digit_decimal_doc_curr(A.doc_type_id, B.curr_code), f_get_value_system_config_by_param_code(A.tenant_id, 'rounding.mode.non.tax')), B.tax_percentage)), 0) AS tax_item_amount,
        COALESCE(SUM(f_get_amount_before_tax(E.warranty_sell_qty * E.warranty_sell_price, B.flg_tax_amount, B.tax_percentage, f_get_digit_decimal_doc_curr(A.doc_type_id, B.curr_code), f_get_value_system_config_by_param_code(A.tenant_id, 'rounding.mode.non.tax'))), 0) AS total_warranty_item,
        COALESCE(SUM(f_tax_rounding(A.tenant_id, f_get_amount_before_tax(E.warranty_sell_qty * E.warranty_sell_price, B.flg_tax_amount, B.tax_percentage, f_get_digit_decimal_doc_curr(A.doc_type_id, B.curr_code), f_get_value_system_config_by_param_code(A.tenant_id, 'rounding.mode.non.tax')), B.tax_percentage)), 0) AS tax_warranty_item,
        COALESCE(SUM(F.price * C.qty_so), 0) AS total_purch_amount, A.salesman_id, SUBSTRING(A.doc_date, 0 , 7) AS year_month, A.tenant_id, A.ou_id
    FROM sl_so A
    INNER JOIN sl_so_item B ON A.so_id = B.so_id
    INNER JOIN sl_so_balance_item C ON B.so_item_id = C.so_item_id
    LEFT OUTER JOIN sl_so_warranty_item D ON D.so_item_id = C.so_item_id
    LEFT OUTER JOIN sl_so_balance_warranty_item E ON D.so_warranty_item_id = E.so_warranty_item_id
        AND D.so_item_id = E.so_item_id
        AND E.status_item IN ('R', 'I')
    LEFT OUTER JOIN sl_so_item_purchasing F ON F.so_item_id = D.so_item_id
    WHERE A.status_doc IN ('R')
        AND C.status_item NOT IN ('V', 'C', 'F')
        AND C.qty_so - C.qty_cancel + C.qty_add + C.qty_return - C.qty_dlv > 0
        AND NOT EXISTS (SELECT 1 FROM fi_po_internal_so_igs_tagging Z WHERE Z.so_id = A.so_id)
    GROUP BY A.salesman_id, A.doc_date, A.tenant_id, A.ou_id
) AS tt_temp_so;