query sales

-- DO
WITH tt_sl_do_outstanding AS (
    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(344, D.curr_code), 'RD')), 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(311, 'IDR'), 'RD'), 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(344, D.curr_code), 'RD')), 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(342, C.curr_code), 'RD'), D.tax_percentage)), 0) AS tax_warranty_item
    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')
)
SELECT *
FROM tt_sl_do_outstanding;

--WITH tt_do_receipt AS (
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(344, D.curr_code), 'RD')), 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(344, D.curr_code), 'RD'), 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(344, D.curr_code), 'RD')), 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(2342, D.curr_code), 'RD'), D.tax_percentage)), 0) AS tax_warranty_item
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 E.salesman_id = 3556
  AND SUBSTRING(A.doc_date, 0 , 7) = '201503'
--)
--SELECT total_item_amount AS sales_amount, gross_profit_amount + tax_sales + tax_warranty AS gross_profit_amount
--FROM tt_do_receipt;

--RN
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(334, 'IDR'), 'RD'))) 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(900, D.curr_code), 'RD')), 0) AS tax_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(900, D.curr_code), 'RD'), D.tax_percentage)), 0) AS tax_warranty
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
WHERE A.doc_type_id = 502
--  AND A.status_doc NOT IN ('R');

--SUM(f_get_amount_before_tax_and_disc((A.qty_return * A.qty_so * A.price) / A.qty_int_so, (B.regular_disc_amount + B.promo_disc_amount) * (A.qty_return * A.qty_so) / (A.qty_int_so / B.qty_dlv_so), A.flg_tax_amount, A.tax_percentage, f_get_digit_decimal_doc_curr(vReturnNoteDocTypeId, A.curr_code), vRoundingModeNonTax)),

--B.qty_realization*D.qty_so/D.qty_int

-- SO APPROVED
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(344, B.curr_code), 'RD')), 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(332, B.curr_code), 'RD'), 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(344, B.curr_code), 'RD')), 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(332, B.curr_code), 'RD'), B.tax_percentage)), 0) AS tax_warranty_item
--SUM(C.qty_so*B.gross_sell_price)
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')
WHERE A.status_doc IN ('R')
    AND C.status_item NOT IN ('V', 'C', 'F')
--    AND A.salesman_id = 5673
    AND C.qty_so - C.qty_cancel + C.qty_add + C.qty_return - C.qty_dlv > 0;