Report Kartu Stock

CREATE OR REPLACE FUNCTION rpt.r_report_kartu_stock(character varying, bigint, bigint, character varying, character varying)
  RETURNS SETOF refcursor AS
$BODY$
DECLARE
    pRefHeader                  REFCURSOR := 'refHeader';
    pRefDetail                  REFCURSOR := 'refDetail';
    pRefSummary                 REFCURSOR := 'refSummary';

    pSessionId                  ALIAS FOR $1;
    pRecordOwnerId              ALIAS FOR $2;
    pProductId                  ALIAS FOR $3;
    pDateFrom                   ALIAS FOR $4;
    pDateTo                     ALIAS FOR $5;

    vEmptyId                    bigint := -99;
    vBeginningQty               bigint := 0;
    vEndQty                     bigint := 0;
    vCalculateQty               bigint := 0;

    vEmpty                      character varying := '';
    vNo                         character varying := 'N';
    vYes                        character varying := 'Y';

    vYearMonth                  character varying := '';
    vFilerProduct               character varying := '';
    vLastYearMonth              character varying := '';
    vNextYearMonth              character varying := '';
    vSaldoDateFrom              character varying := '';

    vCursorRow                  tt_summary_trx_document%ROWTYPE;

BEGIN

    DELETE FROM tt_summary_qty WHERE session_id = pSessionId;
    DELETE FROM tt_summary_trx_document WHERE session_id = pSessionId;

    OPEN pRefHeader FOR
        SELECT f_get_record_owner_name(pRecordOwnerId) AS record_owner_name, pDateFrom AS date_from, pDateTo AS date_to,
                f_get_product_code(pProductId) AS product_code, f_get_product_name(pProductId) AS product_name;
    RETURN NEXT pRefHeader;

    /**
     * cari terakhir bulan tahun terakhir tutup bulan
     */
    SELECT COALESCE(MAX(year_month), ' ') INTO vLastYearMonth
    FROM m_admin_process_ledger
    WHERE record_owner_id = pRecordOwnerId AND
        flag_process = vYes AND
        ledger_code = 'INV' AND
        year_month < substring(pDateFrom,1,6);

    SELECT TO_CHAR(TO_DATE(vLastYearMonth,'YYYYMM') + interval '1 Month','YYYYMM') INTO vNextYearMonth;
    vSaldoDateFrom := vNextYearMonth||'01';

    /**
     * 1. Ambil qty dari in_summary_monthly_qty berdasrkan (vNextYearMonth)
     * 2. Init Saldo awal dari log product balance stock berdasrkan awal tahun bulan dari terakhir tutup bulan
     */
    EXECUTE '
        WITH TEMP AS (
            SELECT $1 AS session_id, A.record_owner_id, COALESCE(SUM(A.qty), 0) AS qty
            FROM in_summary_monthly_qty A
            WHERE A.year_month = $6
                AND A.record_owner_id = $2
                AND A.product_id = $5
            GROUP BY A.record_owner_id, A.product_id

            UNION

            SELECT $1 AS session_id, record_owner_id, COALESCE(SUM(qty), 0) AS qty
            FROM in_log_product_balance_stock
            WHERE record_owner_id = $2
                AND ref_doc_date >= $3
                AND ref_doc_date < $4
                AND product_id = $5
            GROUP BY record_owner_id, product_id
        )
        INSERT INTO tt_summary_qty(
            session_id, record_owner_id, beginning_qty, end_qty
        )
        SELECT session_id, record_owner_id, SUM(qty), SUM(qty)
        FROM TEMP
        WHERE session_id = $1
        GROUP BY session_id, record_owner_id'
    USING pSessionId, pRecordOwnerId, vSaldoDateFrom, pDateFrom, pProductId, vNextYearMonth;

    -- Ambil Semua dokumen transaksi yang ada dalam jangka periode
    EXECUTE '
        INSERT INTO tt_summary_trx_document (
            session_id, record_owner_id, doc_no, doc_date, doc_desc, qty
        )
        SELECT $4, A.record_owner_id, A.ref_doc_no, A.ref_doc_date, f_get_doc_desc(A.ref_doc_type_id), COALESCE(SUM(A.qty), 0)
        FROM in_log_product_balance_stock A
        WHERE A.record_owner_id = $1
            AND A.ref_doc_date BETWEEN $2 AND $3
            AND A.product_id = $5
        GROUP BY A.record_owner_id, A.ref_doc_no, A.ref_doc_date, A.ref_doc_type_id
        ORDER BY A.ref_doc_date ASC'
    USING pRecordOwnerId, pDateFrom, pDateTo, pSessionId, pProductId;

    FOR vCursorRow IN
        SELECT *
        FROM tt_summary_trx_document
        WHERE session_id = pSessionId
    LOOP

        /*
         * Melakukan pengecekan apakah ada saldo awal jika tidak ada lakukan insert saldo awal
         * dari hasil stock akhir
         */
        IF(SELECT 1 FROM tt_summary_qty WHERE session_id = pSessionId AND record_owner_id = pRecordOwnerId) THEN
                SELECT COALESCE(SUM(end_qty), 0) INTO vBeginningQty FROM tt_summary_qty WHERE session_id = pSessionId AND record_owner_id = pRecordOwnerId;
        ELSE
            vBeginningQty := 0;
            INSERT INTO tt_summary_qty(
                session_id, record_owner_id, beginning_qty, end_qty
            ) VALUES
            (pSessionId, pRecordOwnerId, 0, 0);

        END IF;

        vCalculateQty := vBeginningQty+vCursorRow.qty;

        INSERT INTO tt_sumary_trx_log_product_balance(
            session_id, doc_no, doc_date, doc_desc, beginning_qty,
            trx_qty, end_qty
        ) VALUES (pSessionId, vCursorRow.doc_no, vCursorRow.doc_date, vCursorRow.doc_desc, vBeginningQty,
            vCursorRow.qty, vCalculateQty);

        UPDATE tt_summary_qty SET end_qty = vCalculateQty WHERE session_id = pSessionId AND record_owner_id = pRecordOwnerId;
    END LOOP;

    OPEN pRefDetail FOR
        SELECT *
        FROM tt_sumary_trx_log_product_balance
        WHERE session_id = pSessionId;
    RETURN NEXT pRefDetail;

    OPEN pRefSummary FOR
        SELECT COALESCE(SUM(beginning_qty), 0) AS beginning_qty, COALESCE(SUM(end_qty), 0) AS end_qty
        FROM tt_summary_qty
        WHERE session_id = pSessionId;
    RETURN NEXT pRefSummary;

    DELETE FROM tt_summary_qty WHERE session_id = pSessionId;
    DELETE FROM tt_summary_trx_document WHERE session_id = pSessionId;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  SET search_path = public, mstr, ptg, htg, pb, pj, batch
  COST 100;
  /