Report Rekap Stock

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

    pSessionId                  ALIAS FOR $1;
    pRecordOwnerId              ALIAS FOR $2;
    pBrand                      ALIAS FOR $3;
    pProduct                    ALIAS FOR $4;
    pCategory                   ALIAS FOR $5;
    pDateFrom                   ALIAS FOR $6;
    pDateTo                     ALIAS FOR $7;

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

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

    vYearMonth                  character varying := '';
    vFilerProduct               character varying := '';
    vFilerBrand                 character varying := '';
    vFilerCategory              character varying := '';
    vLastYearMonth              character varying := '';
    vLastDateFrom               character varying := '';
    vSaldoDateFrom              character varying := '';

BEGIN

    IF pProduct <> vEmpty THEN
        vFilerProduct := ' AND ( UPPER(B.product_code) LIKE UPPER('''|| pProduct ||''') OR UPPER(B.product_name) LIKE UPPER('''|| pProduct ||''') )';
    END IF;

    IF pBrand <> vEmpty THEN
        vFilerBrand := ' AND UPPER(B.brand_name) LIKE  UPPER('''|| pBrand ||''')';
    END IF;

    IF pCategory <> vEmpty THEN
        vFilerProduct := ' AND ( UPPER(f_get_product_ctgr_code_by_product_id(B.product_id)) LIKE UPPER('''|| pCategory ||''') OR UPPER(f_get_product_ctgr_name_by_product_id(B.product_id)) LIKE UPPER('''|| pCategory ||''') )';
    END IF;

    OPEN pRefHeader FOR
        SELECT f_get_record_owner_name(pRecordOwnerId) AS record_owner_name, pDateFrom AS date_from, pDateTo as date_to;
    RETURN NEXT pRefHeader;


    /**
     * cari terakhir bulan tahun terakhir tutup bulan sebelum atau sama dengan tanggal awal
     */
    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);

    /**
     * Init saldo awal berdasrkan tahun bulan yang didapat dari terakhir tutup bulan (vLastYearMonth)
     */
    EXECUTE '
        INSERT INTO tt_rekap_saldo_qty (
            session_id, record_owner_id, product_id,
            init_qty, buy_qty, sell_qty, adj_qty, return_qty, void_pos_qty
        )
        SELECT $1, A.record_owner_id, A.product_id,
            COALESCE(SUM(A.qty), 0), 0, 0, 0, 0, 0
        FROM in_summary_monthly_qty A
        WHERE A.year_month = $3
            AND A.record_owner_id = $4
        GROUP BY A.record_owner_id, A.product_id'
    USING pSessionId, vEmptyId, vLastYearMonth, pRecordOwnerId;

    vSaldoDateFrom := vLastYearMonth||'01';

    SELECT TO_CHAR(TO_DATE(pDateFrom,'YYYYMMDD') + interval '-1 Day','YYYYMMDD') INTO vLastDateFrom;

    /**
     * Init Saldo awal dari log product balance stock berdasrkan awal tahun bulan dari terakhir tutup bulan
     * sampai dengan tanggal awal -1 hari
     */

    EXECUTE '
        INSERT INTO tt_rekap_saldo_qty (
            session_id, record_owner_id, product_id,
            init_qty, buy_qty, sell_qty, adj_qty, return_qty, void_pos_qty
        )
        SELECT $1, record_owner_id, product_id,
            COALESCE(SUM(qty), 0), 0, 0, 0, 0, 0
        FROM in_log_product_balance_stock
        WHERE record_owner_id = $2
            AND ref_doc_date BETWEEN $3 AND $4
        GROUP BY record_owner_id, product_id'
    USING pSessionId, pRecordOwnerId, vSaldoDateFrom, vLastDateFrom;

    -- Qty Dokumen Terima Barang
    EXECUTE '
        INSERT INTO tt_rekap_saldo_qty (
            session_id, record_owner_id, product_id,
            init_qty, buy_qty, sell_qty, adj_qty, return_qty, void_pos_qty
        )
        SELECT $1, A.record_owner_id, A.product_id,
            0, COALESCE(SUM(qty), 0), 0, 0, 0, 0
        FROM in_log_product_balance_stock A
        WHERE A.record_owner_id = $2
            AND A.ref_doc_date BETWEEN $3 AND $4
            AND A.ref_doc_type_id = $5
        GROUP BY A.record_owner_id, A.product_id'
    USING pSessionId, pRecordOwnerId, pDateFrom, pDateTo, vReceiveGoodsDocTypeId, vEmptyId;

    -- Qty Dokumen Penjualan (POS)
    EXECUTE '
        INSERT INTO tt_rekap_saldo_qty (
            session_id, record_owner_id, product_id,
            init_qty, buy_qty, sell_qty, adj_qty, return_qty, void_pos_qty
        )
        SELECT $1, A.record_owner_id, A.product_id,
            0, 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 BETWEEN $3 AND $4
            AND A.ref_doc_type_id = $5
        GROUP BY A.record_owner_id, A.product_id'
    USING pSessionId, pRecordOwnerId, pDateFrom, pDateTo, vPosDocTypeId, vEmptyId;

    -- Qty Dokumen Void Penjualan (POS)
    EXECUTE '
        INSERT INTO tt_rekap_saldo_qty (
            session_id, record_owner_id, product_id,
            init_qty, buy_qty, sell_qty, adj_qty, return_qty, void_pos_qty
        )
        SELECT $1, A.record_owner_id, A.product_id,
            0, 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 BETWEEN $3 AND $4
            AND A.ref_doc_type_id = $5
        GROUP BY A.record_owner_id, A.product_id'
    USING pSessionId, pRecordOwnerId, pDateFrom, pDateTo, vVoidPosDocTypeId, vEmptyId;

    -- Qty Dokumen Adj Stock
    EXECUTE '
        INSERT INTO tt_rekap_saldo_qty (
            session_id, record_owner_id, product_id,
            init_qty, buy_qty, sell_qty, adj_qty, return_qty, void_pos_qty
        )
        SELECT $1, A.record_owner_id, A.product_id,
            0, 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 BETWEEN $3 AND $4
            AND A.ref_doc_type_id = $5
        GROUP BY A.record_owner_id, A.product_id'
    USING pSessionId, pRecordOwnerId, pDateFrom, pDateTo, vAdjDocTypeId, vEmptyId;

    -- Qty Dokumen Retur Barang
    EXECUTE '
        INSERT INTO tt_rekap_saldo_qty (
            session_id, record_owner_id, product_id,
            init_qty, buy_qty, sell_qty, adj_qty, return_qty, void_pos_qty
        )
        SELECT $1, A.record_owner_id, A.product_id,
            0, 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 BETWEEN $3 AND $4
            AND A.ref_doc_type_id = $5
        GROUP BY A.record_owner_id, A.product_id'
    USING pSessionId, pRecordOwnerId, pDateFrom, pDateTo, vReturnNoteDocTypeId, vEmptyId;

    OPEN pRefDetail FOR
        EXECUTE '
                SELECT *,  init_qty+buy_qty+sell_qty+adj_qty+return_qty+void_pos_qty AS saldo_akhir, 0 AS hpp_avg
                FROM (

                    SELECT f_get_product_code(B.product_id) AS product_code, f_get_product_name(B.product_id) AS product_name, B.product_id,
                        SUM(A.init_qty) AS init_qty, SUM(A.buy_qty) AS buy_qty, SUM(A.sell_qty) AS sell_qty, SUM(A.adj_qty) AS adj_qty,
                        SUM(A.return_qty) AS return_qty, SUM(A.void_pos_qty) AS void_pos_qty
                    FROM tt_rekap_saldo_qty A
                    INNER JOIN m_product B ON A.product_id = B.product_id
                    WHERE A.session_id = $1'
                        || vFilerProduct || vFilerBrand || vFilerProduct ||'
                    GROUP BY B.product_id
                ) Z'
        USING pSessionId;
    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;
  /