pu_get_latest_purchasing_price_by_date_20170809_3

--Modified by Adrian, Jul 31, 2017
--meng-comment kondisi AND A.item_amount > 0 pada tt_purch_by_period
--mengubah ambil harga beli dari PO terakhir
--po_balance_invoice_id diambil dari produk paling awal dalam PO terakhir per tanggal

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

    vEmptyString    character varying := '';
    vNo             character varying := 'N';
    vYes            character varying := 'Y';
    vZero           numeric := 0;
    vStartDate              character varying(8);
    vStartDateFromStartMonth character varying(8);
    vEndDate                character varying(8);
    vLastSummaryPeriod      character varying(8);
    vDefaultGlCurrCode      character varying(100);
    vRoundingMode           character varying(10);
    vOneMonthBeforeStartDate character varying(6);
    vOneMonthAfterLastSummary character varying(6);
    vReceiveGoodsDocTypeId  bigint := 111;
BEGIN

    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');

    DELETE FROM pu_temp_summary_monthly_latest_purchasing WHERE session_id = pSessionId;
    DELETE FROM tt_purch_by_period WHERE session_id = pSessionId;

    ANALYZE tt_in_latest_purchasing_price_by_date;

    -- 1. pindahkan semua yg di input ke output nya terlebih dahulu
    -- (contoh: doc date ada bulan Desember 2014 - Maret 2015)
    INSERT INTO tt_out_latest_purchasing_price_by_date (
        session_id, tenant_id, ou_bu_id, doc_date, product_id,
        purch_curr_code, purch_nett_price, purch_gross_price,
        gl_purch_curr_code, gl_purch_nett_price, gl_purch_gross_price,
        flg_cogs
    )
    SELECT pSessionId, tenant_id, ou_bu_id, doc_date, product_id,
           vEmptyString, vZero, vZero,
           vEmptyString, vZero, vZero,
           vNo
    FROM tt_in_latest_purchasing_price_by_date
    WHERE session_id = pSessionId
    AND tenant_id = pTenantId;

    ANALYZE tt_out_latest_purchasing_price_by_date;

    -- 2. cari periode penjualan yang harus dicari nilai pembeliannya
    SELECT MIN(doc_date), MAX(doc_date) INTO vStartDate, vEndDate
    FROM tt_out_latest_purchasing_price_by_date
    WHERE session_id = pSessionId;

    ANALYZE pu_summary_monthly_latest_purchasing;

    -- 3. cari rekap purchasing terakhir bulan apa (contoh: Januari 2015) sebelum start date
    SELECT MAX(year_month_date) INTO vLastSummaryPeriod
    FROM pu_summary_monthly_latest_purchasing
    WHERE ou_id = pOuId
    AND tenant_id = pTenantId
    AND year_month_date < vStartDate;

    -- 4. lakukan proses rekap temporary dari periode terakhir rekap + 1 bulan sampai periode penjualan awal - 1 bulan

    SELECT TO_CHAR(TO_DATE(vStartDate,'YYYYMMDD') - interval '1 Month','YYYYMM') INTO vOneMonthBeforeStartDate;
    SELECT TO_CHAR(TO_DATE(vLastSummaryPeriod,'YYYYMM') + interval '1 Month','YYYYMM') INTO vOneMonthAfterLastSummary;

    SELECT SUBSTRING(vStartDate, 1, 6) || '01' INTO vStartDateFromStartMonth;

    IF SUBSTRING(vStartDate, 1, 6) <= vLastSummaryPeriod THEN
        INSERT INTO pu_temp_summary_monthly_latest_purchasing
        (session_id, 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 pSessionId, 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,
               0, pDatetime, pUserId, pDatetime, pUserId
        FROM pu_summary_monthly_latest_purchasing
        WHERE ou_id = pOuId
        AND tenant_id = pTenantId
        AND year_month_date = vOneMonthBeforeStartDate;
    ELSE
        INSERT INTO pu_temp_summary_monthly_latest_purchasing
        (session_id, 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 pSessionId, 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,
               0, pDatetime, pUserId, pDatetime, pUserId
        FROM pu_summary_monthly_latest_purchasing
        WHERE ou_id = pOuId
        AND tenant_id = pTenantId
        AND year_month_date = vLastSummaryPeriod;

        WHILE vOneMonthAfterLastSummary <= vOneMonthBeforeStartDate LOOP
            PERFORM pu_temp_summary_purch(pTenantId,pSessionId,pOuId,vOneMonthAfterLastSummary,pDatetime,pUserId);
            SELECT TO_CHAR(TO_DATE(vOneMonthAfterLastSummary,'YYYYMM') + interval '1 Month','YYYYMM') INTO vOneMonthAfterLastSummary;
        END LOOP;
    END IF;

    ANALYZE tt_out_latest_purchasing_price_by_date;

    -- 5. ambil transaksi pembelian dari bulan transaksi yg di cari di nomor 1 (contoh: Desember 2014 - Maret 2015)
    INSERT INTO tt_purch_by_period (
        session_id, tenant_id, ou_id, doc_date, product_id, curr_code,
        nett_price, gross_price, gl_curr_code, gl_nett_price, gl_gross_price
    )
    SELECT pSessionId, A.tenant_id, A.ou_id, A.ref_doc_date,
       B.product_id, A.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,
       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,
       vDefaultGlCurrCode,
       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,
       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
    FROM pu_po_balance_invoice A
    INNER JOIN pu_receive_goods_item B ON A.ref_item_id = B.receive_goods_item_id
    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 A.ref_doc_date BETWEEN vStartDateFromStartMonth AND vEndDate
    AND A.ou_id = pOuId
    AND A.tenant_id = pTenantId
    AND EXISTS (
        SELECT 1
        FROM tt_out_latest_purchasing_price_by_date C
        WHERE C.session_id = pSessionId
        AND C.product_id = B.product_id
        AND A.ou_id = C.ou_bu_id
    )
    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
        INNER JOIN pu_po_item F ON D.ref_id = F.po_item_id
        WHERE D.product_id = B.product_id
        --AND E.year_month_date = SUBSTR(A.ref_doc_date, 1, 6)
        AND C.ref_doc_date <= A.ref_doc_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, F.line_no ASC
        LIMIT 1
    )
    GROUP BY A.tenant_id, A.ou_id, A.ref_doc_date, B.product_id, A.curr_code
    ORDER BY A.ref_doc_date DESC;

    ANALYZE tt_out_latest_purchasing_price_by_date;
    ANALYZE tt_purch_by_period;

    -- 6. update nilai pembelian terakhir dari step nomor 5
    WITH tt_purch_by_period_by_date AS (
        SELECT A.session_id, B.ou_id, A.product_id, A.doc_date, MAX(B.doc_date) AS max_doc_date
        FROM tt_purch_by_period B
        INNER JOIN tt_out_latest_purchasing_price_by_date A
        ON B.session_id = A.session_id
        AND A.product_id = B.product_id
        AND A.ou_bu_id = B.ou_id
        AND B.doc_date <= A.doc_date
        WHERE A.session_id = pSessionId
        GROUP BY A.session_id, B.ou_id, A.product_id, A.doc_date
    )
    UPDATE tt_out_latest_purchasing_price_by_date A
    SET purch_curr_code = B.curr_code,
        purch_nett_price = B.nett_price,
        purch_gross_price = B.gross_price,
        gl_purch_curr_code = B.gl_curr_code,
        gl_purch_nett_price = B.gl_nett_price,
        gl_purch_gross_price = B.gl_gross_price,
        flg_cogs = vYes
    FROM tt_purch_by_period B
    INNER JOIN tt_purch_by_period_by_date C
        ON B.session_id = C.session_id
        AND C.product_id = B.product_id
        AND C.ou_id = B.ou_id
        AND B.doc_date = C.max_doc_date
    WHERE B.session_id = A.session_id
    AND A.session_id = pSessionId
    AND A.product_id = B.product_id
    AND A.ou_bu_id = B.ou_id
    AND A.doc_date = C.doc_date
    AND A.flg_cogs = vNo;

    ANALYZE pu_temp_summary_monthly_latest_purchasing;

    -- 7. update nilai pembelian terakhir bagi yang belum dapat nilai di nomor 6 dengan nilai rekap terakhir dari rekap pembelian (temp)
    UPDATE tt_out_latest_purchasing_price_by_date A
    SET purch_curr_code = B.curr_code,
        purch_nett_price = B.nett_price,
        purch_gross_price = B.gross_price,
        gl_purch_curr_code = B.gl_curr_code,
        gl_purch_nett_price = B.gl_nett_price,
        gl_purch_gross_price = B.gl_gross_price,
        flg_cogs = vYes
    FROM pu_temp_summary_monthly_latest_purchasing B
    WHERE A.flg_cogs = vNo
    AND A.session_id = pSessionId
    AND A.product_id = B.product_id
    AND A.ou_bu_id = B.ou_id
    AND B.year_month_date = vOneMonthBeforeStartDate
    AND B.session_id = pSessionId;

    DELETE FROM pu_temp_summary_monthly_latest_purchasing WHERE session_id = pSessionId;
    DELETE FROM tt_purch_by_period WHERE session_id = pSessionId;

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