Laporan Rekap Stock

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

    pSessionId                  ALIAS FOR $1;
    pRecordOwnerId              ALIAS FOR $2;
    pProductId                  ALIAS FOR $3;
    pDate                       ALIAS FOR $4;

    vEmptyId                    bigint := -99;
    vReceiveGoodsDocTypeId      bigint := 111;
    vPosDocTypeId               bigint := 390;
    vAdjDocTypeId               bigint := 521;
    vReturnNoteDocTypeId        bigint := 502;

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

    vYearMonth                  character varying := '';
    vFilerProduct               character varying := '';

BEGIN

    DELETE FROM tt_rekap_saldo_qty WHERE session_id = pSessionId;

    SELECT substring(pDate, 1, 6) INTO vYearMonth;

    OPEN pRefHeader FOR
        SELECT f_get_record_owner_name(pRecordOwnerId) AS record_owner_name, CASE WHEN pProductId <> vEmptyId
            THEN concat(f_get_product_code(pProductId), ' - ', f_get_product_name(pProductId)) ELSE 'ALL' END AS product,
            pDate AS date;
    RETURN NEXT pRefHeader;

    IF pProductId <> vEmptyId THEN
        vFilerProduct := ' AND A.product_id =  '''|| pProductId ||'''';
    END IF;

    -- Init Saldo Awal Qty
    EXECUTE '
        INSERT INTO tt_rekap_saldo_qty (
            session_id, record_owner_id, doc_type_id, product_id,
            init_qty, buy_qty, sell_qty, adj_qty, return_qty
        )
        SELECT $1, A.record_owner_id, $2, A.product_id,
            A.qty, 0, 0, 0, 0
        FROM in_summary_monthly_qty A
        WHERE A.year_month <= $3
            ' || vFilerProduct ||'
            AND A.record_owner_id = $4'
    USING pSessionId, vEmptyId, vYearMonth, pRecordOwnerId;

    -- Init Qty Beli
    EXECUTE '
        INSERT INTO tt_rekap_saldo_qty (
            session_id, record_owner_id, doc_type_id, product_id,
            init_qty, buy_qty, sell_qty, adj_qty, return_qty
        )
        SELECT $1, A.record_owner_id, $5, A.product_id,
            0, COALESCE(SUM(qty), 0), 0, 0, 0
        FROM in_log_product_balance_stock A
        WHERE A.record_owner_id = $2
            AND A.ref_doc_date <= $3
            AND A.ref_doc_type_id = $4
            ' || vFilerProduct ||'
        GROUP BY A.record_owner_id, A.product_id'
    USING pSessionId, pRecordOwnerId, pDate, vReceiveGoodsDocTypeId, vEmptyId;

    -- Init Qty Penjualan
    EXECUTE '
        INSERT INTO tt_rekap_saldo_qty (
            session_id, record_owner_id, doc_type_id, product_id,
            init_qty, buy_qty, sell_qty, adj_qty, return_qty
        )
        SELECT $1, A.record_owner_id, $5, A.product_id,
            0, 0, COALESCE(SUM(qty), 0), 0, 0
        FROM in_log_product_balance_stock A
        WHERE A.record_owner_id = $2
            AND A.ref_doc_date <= $3
            AND A.ref_doc_type_id = $4
            ' || vFilerProduct ||'
        GROUP BY A.record_owner_id, A.product_id'
    USING pSessionId, pRecordOwnerId, pDate, vPosDocTypeId, vEmptyId;

    -- init Adj Stock
    EXECUTE '
        INSERT INTO tt_rekap_saldo_qty (
            session_id, record_owner_id, doc_type_id, product_id,
            init_qty, buy_qty, sell_qty, adj_qty, return_qty
        )
        SELECT $1, A.record_owner_id, $5, A.product_id,
            0, 0, 0, COALESCE(SUM(qty), 0), 0
        FROM in_log_product_balance_stock A
        WHERE A.record_owner_id = $2
            AND A.ref_doc_date <= $3
            AND A.ref_doc_type_id = $4
            ' || vFilerProduct ||'
        GROUP BY A.record_owner_id, A.product_id'
    USING pSessionId, pRecordOwnerId, pDate, vAdjDocTypeId, vEmptyId;

    -- Init retur Beli
    EXECUTE '
        INSERT INTO tt_rekap_saldo_qty (
            session_id, record_owner_id, doc_type_id, product_id,
            init_qty, buy_qty, sell_qty, adj_qty, return_qty
        )
        SELECT $1, A.record_owner_id, $5, A.product_id,
            0, 0, 0, 0, COALESCE(SUM(qty), 0)
        FROM in_log_product_balance_stock A
        WHERE A.record_owner_id = $2
            AND A.ref_doc_date <= $3
            AND A.ref_doc_type_id = $4
            ' || vFilerProduct ||'
        GROUP BY A.record_owner_id, A.product_id'
    USING pSessionId, pRecordOwnerId, pDate, vReturnNoteDocTypeId, vEmptyId;

    OPEN pRefDetail FOR
        SELECT Z.*,  init_qty+buy_qty+sell_qty+adj_qty+return_qty AS final_qty,
            0 AS avg_hpp
        FROM (
            SELECT f_get_product_code(A.product_id) AS product_code, f_get_product_name(A.product_id) AS product_name,
                COALESCE(SUM(init_qty), 0) AS init_qty, COALESCE(SUM(buy_qty), 0) AS buy_qty,
                COALESCE(SUM(sell_qty), 0) AS sell_qty, COALESCE(SUM(adj_qty), 0) AS adj_qty,
                COALESCE(SUM(return_qty), 0) AS return_qty
            FROM tt_rekap_saldo_qty A
            WHERE session_id = pSessionId
            GROUP BY A.product_id
        ) Z
        ORDER BY product_name ASC;
    RETURN NEXT pRefDetail;

    DELETE FROM tt_rekap_saldo_qty WHERE session_id = pSessionId;

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