pu_summary_purch

--Modified by Adrian, Jul 31, 2017, mengubah order pada summary_monthly_purchasing_temp menjadi ORDER BY C.ref_doc_date DESC, C.create_datetime DESC
--dan meng-comment kondisi AND A.item_amount > 0

CREATE OR REPLACE FUNCTION pu_summary_purch(bigint, character varying, bigint, character varying, character varying, bigint)
  RETURNS void AS
$BODY$
DECLARE
    pTenantId           ALIAS FOR $1;
    pSessionId          ALIAS FOR $2;
    pOuId               ALIAS FOR $3;
    pYearMonth          ALIAS FOR $4;
    pDatetime           ALIAS FOR $5;
    pUserId             ALIAS FOR $6;

    vReceiveGoodsDocTypeId  bigint;
    vPrevYearMonth      character varying(6);
    vDefaultCurrCode    character varying(10);
    vDefaultPrice       numeric;
    vDefaultGlCurrCode  character varying(100);
    vRoundingMode       character varying(10);
BEGIN

    vReceiveGoodsDocTypeId := 111;
    vDefaultCurrCode := '-99';
    vDefaultPrice := 0;

    vDefaultGlCurrCode := f_get_value_system_config_by_param_code(pTenantId,'ValutaBuku');
    vRoundingMode := f_get_value_system_config_by_param_code(pTenantId, 'rounding.mode.non.tax');

    SELECT TO_CHAR(TO_DATE(pYearMonth,'YYYYMM') - interval '1 Month','YYYYMM') INTO vPrevYearMonth;

    INSERT INTO pu_summary_monthly_latest_purchasing
        (tenant_id, ou_id, year_month_date,
         product_id, curr_code, nett_price, gross_price,
         gl_curr_code, gl_nett_price, gl_gross_price,
         ref_doc_type_id, ref_id, ref_doc_no, ref_doc_date,
         version, create_datetime, create_user_id, update_datetime, update_user_id)
    SELECT tenant_id, ou_id, pYearMonth,
           product_id, curr_code, nett_price, gross_price,
           gl_curr_code, gl_nett_price, gl_gross_price,
           ref_doc_type_id, ref_id, ref_doc_no, ref_doc_date,
           0, pDatetime, pUserId, pDatetime, pUserId
    FROM pu_summary_monthly_latest_purchasing
    WHERE ou_id = pOuId
    AND tenant_id = pTenantId
    AND year_month_date = vPrevYearMonth;

    INSERT INTO pu_summary_monthly_latest_purchasing
        (tenant_id, ou_id, year_month_date,
         product_id, curr_code, nett_price, gross_price,
         gl_curr_code, gl_nett_price, gl_gross_price,
         ref_doc_type_id, ref_id, ref_doc_no, ref_doc_date,
         version, create_datetime, create_user_id, update_datetime, update_user_id)
    SELECT A.tenant_id, pOuId, pYearMonth,
           A.product_id, vDefaultCurrCode, vDefaultPrice, vDefaultPrice,
           vDefaultGlCurrCode, vDefaultPrice, vDefaultPrice,
           -99, -99, ' ', ' ',
           0, pDatetime, pUserId, pDatetime, pUserId
    FROM m_product A
    WHERE A.tenant_id = pTenantId
    AND NOT EXISTS (
        SELECT 1 FROM pu_summary_monthly_latest_purchasing B
        WHERE B.ou_id = pOuId
        AND B.tenant_id = pTenantId
        AND B.year_month_date = pYearMonth
        AND B.product_id = A.product_id
    )
    AND NOT EXISTS (
        SELECT 1 FROM m_ext_product C
        WHERE C.product_id = A.product_id
    );

    WITH summary_monthly_purchasing_temp AS (
        SELECT A.tenant_id, A.ou_id, F.year_month_date,
           B.product_id, A.curr_code AS curr_code,
           CASE WHEN vRoundingMode = 'RD'
            THEN TRUNC(SUM(A.item_amount) / SUM(A.qty_rcv_po), f_get_digit_decimal_doc_curr(-99, A.curr_code))
            ELSE ROUND(SUM(A.item_amount) / SUM(A.qty_rcv_po), f_get_digit_decimal_doc_curr(-99, A.curr_code))
           END AS nett_price,
           CASE WHEN vRoundingMode = 'RD'
            THEN TRUNC(SUM(A.item_amount + COALESCE(G.tax_amount, 0)) / SUM(A.qty_rcv_po), f_get_digit_decimal_doc_curr(-99, A.curr_code))
            ELSE ROUND(SUM(A.item_amount + COALESCE(G.tax_amount, 0)) / SUM(A.qty_rcv_po), f_get_digit_decimal_doc_curr(-99, A.curr_code))
           END AS gross_price,
           CASE WHEN vRoundingMode = 'RD'
            THEN TRUNC(SUM(A.item_amount) / SUM(A.qty_rcv_po), f_get_digit_decimal_doc_curr(-99, vDefaultGlCurrCode)) * f_commercial_rate(pTenantId, A.ref_doc_date, A.curr_code, vDefaultGlCurrCode)
            ELSE ROUND(SUM(A.item_amount) / SUM(A.qty_rcv_po), f_get_digit_decimal_doc_curr(-99, vDefaultGlCurrCode)) * f_commercial_rate(pTenantId, A.ref_doc_date, A.curr_code, vDefaultGlCurrCode)
           END AS gl_nett_price,
           CASE WHEN vRoundingMode = 'RD'
            THEN TRUNC(SUM(A.item_amount + COALESCE(G.tax_amount, 0)) / SUM(A.qty_rcv_po), f_get_digit_decimal_doc_curr(-99, vDefaultGlCurrCode)) * f_commercial_rate(pTenantId, A.ref_doc_date, A.curr_code, vDefaultGlCurrCode)
            ELSE ROUND(SUM(A.item_amount + COALESCE(G.tax_amount, 0)) / SUM(A.qty_rcv_po), f_get_digit_decimal_doc_curr(-99, vDefaultGlCurrCode)) * f_commercial_rate(pTenantId, A.ref_doc_date, A.curr_code, vDefaultGlCurrCode)
            END AS gl_gross_price,
           A.ref_doc_type_id, A.ref_id, A.ref_doc_no, A.ref_doc_date
        FROM pu_po_balance_invoice A
        INNER JOIN pu_receive_goods_item B ON A.ref_item_id = B.receive_goods_item_id
        INNER JOIN dt_date F ON A.ref_doc_date = F.string_date
        LEFT OUTER JOIN pu_po_balance_invoice_tax G
            ON A.tenant_id = G.tenant_id
            AND A.ou_id = G.ou_id
            AND A.partner_id = G.partner_id
            AND A.ref_doc_type_id = G.ref_doc_type_id
            AND A.ref_id = G.ref_id
            AND A.ref_item_id = G.ref_item_id
        WHERE A.ref_doc_type_id = vReceiveGoodsDocTypeId
        --AND A.item_amount > 0
        AND F.year_month_date = pYearMonth
        AND A.ou_id = pOuId
        AND A.tenant_id = pTenantId
        AND A.po_balance_invoice_id = (
            SELECT C.po_balance_invoice_id
            FROM pu_po_balance_invoice C
            INNER JOIN pu_receive_goods_item D ON C.ref_item_id = D.receive_goods_item_id
            INNER JOIN dt_date E ON C.ref_doc_date = E.string_date
            WHERE D.product_id = B.product_id
            AND E.year_month_date = F.year_month_date
            AND C.ou_id = A.ou_id
            AND C.ref_doc_type_id = vReceiveGoodsDocTypeId
            AND C.tenant_id = pTenantId
            ORDER BY C.ref_doc_date DESC, C.create_datetime DESC
            LIMIT 1
        )
        GROUP BY A.tenant_id, A.ou_id, F.year_month_date, B.product_id, A.curr_code, A.ref_doc_type_id, A.ref_id, A.ref_doc_no, A.ref_doc_date
    )
    UPDATE pu_summary_monthly_latest_purchasing Z
    SET curr_code = Y.curr_code, nett_price = Y.nett_price, gross_price = Y.gross_price,
        gl_curr_code = vDefaultGlCurrCode, gl_nett_price = Y.gl_nett_price, gl_gross_price = Y.gl_gross_price,
        ref_doc_type_id = Y.ref_doc_type_id, ref_id = Y.ref_id, ref_doc_no = Y.ref_doc_no, ref_doc_date = Y.ref_doc_date,
        version = Z.version + 1, update_datetime = pDatetime, update_user_id = pUserId
    FROM summary_monthly_purchasing_temp Y
    WHERE Y.tenant_id = Z.tenant_id
    AND Y.year_month_date = Z.year_month_date
    AND Y.product_id = Z.product_id
    AND Y.ou_id = Z.ou_id;

    DELETE FROM pu_summary_monthly_latest_purchasing A
    WHERE A.tenant_id = pTenantId
    AND A.year_month_date = pYearMonth
    AND A.ou_id = pOuId
    AND A.curr_code = vDefaultCurrCode;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
  /