r_outlet_kartu_stok

/*
 * rekap stok
 * filter mandatory : session_id, tenant_id, ou_id, date_from, date_to
 * filter optional : category product, sub category product, product
 * jika filter optional diisi -99 artinya data digunakan semua
 */

--Modified by Adrian, Sep 15, 2017, menambahkan GTI dan GTO selain Outlet POS

CREATE OR REPLACE FUNCTION r_outlet_kartu_stok(character varying, bigint, bigint, character varying, character varying, bigint, bigint, character varying, bigint)
RETURNS SETOF refcursor AS
$BODY$
DECLARE
    pRefHeader          REFCURSOR := 'refHeader';
    pRefDetail          REFCURSOR := 'refDetail';
    pSessionId              ALIAS FOR $1;
    pTenantId               ALIAS FOR $2;
    pOuId                   ALIAS FOR $3;
    pDateFrom               ALIAS FOR $4;
    pDateTo                 ALIAS FOR $5;
    pCtgrProductId          ALIAS FOR $6;
    pSubCtgrProductId       ALIAS FOR $7;
    pProductName            ALIAS FOR $8;
    pUserId                 ALIAS FOR $9;

    vEmptyValue             character varying(1);
    vEmptyId                bigint;
    vAllId                  bigint;
    vDocTypeAwal            bigint;
    vDocTypeTrfIn           bigint;
    vDocTypeTrfInReceipt    bigint;
    vDocTypeTrfOut          bigint;
    vDocTypeAdj             bigint;
    vDocTypePosShop         bigint;
    vDocTypeReturnPosShop   bigint;
    vDocTypePosShopInshop   bigint;
    vDocTypeReturnPosShopInShop bigint;
    vDocTypeVoidPosShop         bigint;
    vDocTypeVoidPosShopInShop   bigint;
    vLastYearMonth          character varying(6);
    vNextYearMonth          character varying(6);
    vFlagYes                character varying(1);
    vSaldoDateFrom          character varying(8);
    vWarehouseId            bigint;
    vStartTrxDate           character varying(8);

    vDocTypeTransferIn      bigint;
    vDocTypeTransferOut     bigint;

BEGIN

    vEmptyValue := ' ';
    vEmptyId := -99;
    vAllId := -99;
    vDocTypeAwal := -99;
    vDocTypeTrfIn := 534;
    vDocTypeTrfInReceipt := 536;
    vDocTypeTrfOut := 532;
    vDocTypeAdj := 413;
    vDocTypePosShop := 401;
    vDocTypeReturnPosShop := 402;
    vDocTypePosShopInShop := 403;
    vDocTypeReturnPosShopInShop := 404;
    vDocTypeVoidPosShop := 405;
    vDocTypeVoidPosShopInShop := 406;
    vFlagYes := 'Y';
    vLastYearMonth := ' ';
    vNextYearMonth := ' ';

    vDocTypeTransferIn := 535;
    vDocTypeTransferOut := 533;

    DELETE FROM tr_kartu_stok_by_doc_type WHERE session_id = pSessionId;

    SELECT warehouse_id, start_date_trx INTO vWarehouseId, vStartTrxDate
    FROM i_outlet
    WHERE tenant_id = pTenantId AND ou_id = pOuId;

    SELECT COALESCE(MAX(date_year_month),' ') INTO vLastYearMonth
    FROM i_outlet_admin_monthly_process
    WHERE tenant_id = pTenantId AND
        ou_id = pOuId AND
        flg_process = vFlagYes AND
        date_year_month < substr(pDateFrom,1,6);

    IF vLastYearMonth = ' ' THEN

        /*
         * proses admin belum pernah dilakukan
         * jadi saldo awal stok diambil dari summary monthly qty yang year month = bulan dari start tgl trx
         */

        INSERT INTO tr_kartu_stok_by_doc_type
        (session_id, sort_no, tenant_id, ou_id, doc_type_id, doc_desc, ref_id,
        doc_no, doc_date, partner_id, product_id, warehouse_id,
        product_balance_id, product_status, base_uom_id, qty_in, qty_out,
        product_code, product_name, ctgr_product_id, sub_ctgr_product_id)
        SELECT pSessionId, 1, A.tenant_id, A.sub_ou_id, vDocTypeAwal, 'SALDO AWAL', vEmptyId,
            'SALDO_AWAL', vStartTrxDate, vEmptyId, A.product_id, A.warehouse_id,
            A.product_balance_id, A.product_status, A.base_uom_id, SUM(A.qty), 0,
            B.product_code, B.product_name, B.ctgr_product_id, B.sub_ctgr_product_id
        FROM in_summary_monthly_qty A, m_product B
        WHERE A.tenant_id = pTenantId AND
            A.sub_ou_id = pOuId AND
            A.date_year_month = substr(vStartTrxDate,1,6) AND
            A.warehouse_id = vWarehouseId AND
            A.product_id = B.product_id AND
            A.doc_type_id = vDocTypeAwal
        GROUP BY A.tenant_id, A.sub_ou_id, A.product_id, A.warehouse_id,
            A.product_balance_id, A.product_status, A.base_uom_id,
            B.product_code, B.product_name, B.ctgr_product_id, B.sub_ctgr_product_id;

        IF vStartTrxDate < pDateFrom THEN

            INSERT INTO tr_kartu_stok_by_doc_type
            (session_id, sort_no, tenant_id, ou_id, doc_type_id, doc_desc, ref_id,
            doc_no, doc_date, partner_id, product_id, warehouse_id,
            product_balance_id, product_status, base_uom_id, qty_in, qty_out,
            product_code, product_name, ctgr_product_id, sub_ctgr_product_id)
            SELECT pSessionId, 1, A.tenant_id, A.ou_id, vDocTypeAwal, 'SALDO AWAL', vEmptyId,
                'SALDO_AWAL', vStartTrxDate, vEmptyId, A.product_id, A.warehouse_id,
                A.product_balance_id, A.product_status, A.base_uom_id, SUM(A.qty), 0,
                B.product_code, B.product_name, B.ctgr_product_id, B.sub_ctgr_product_id
            FROM in_log_product_balance_stock A, m_product B
            WHERE A.tenant_id = pTenantId AND
                A.ou_id = pOuId AND
                A.doc_date < pDateFrom AND
                A.warehouse_id = vWarehouseId AND
                A.product_id = B.product_id
            GROUP BY A.tenant_id, A.ou_id, A.product_id, A.warehouse_id,
                A.product_balance_id, A.product_status, A.base_uom_id,
                B.product_code, B.product_name, B.ctgr_product_id, B.sub_ctgr_product_id;

        END IF;
    ELSE

        SELECT TO_CHAR(TO_DATE(vLastYearMonth,'YYYYMM') + interval '1 Month','YYYYMM') INTO vNextYearMonth;
        vSaldoDateFrom = vNextYearMonth||'01';
        /*
         * jika proses admin sudah pernah dilakukan
         * maka ambil data summary monthly qty dari last year month + 1 bulan
         */
        INSERT INTO tr_kartu_stok_by_doc_type
        (session_id, sort_no, tenant_id, ou_id, doc_type_id, doc_desc, ref_id,
        doc_no, doc_date, partner_id, product_id, warehouse_id,
        product_balance_id, product_status, base_uom_id, qty_in, qty_out,
        product_code, product_name, ctgr_product_id, sub_ctgr_product_id)
        SELECT pSessionId, 1, A.tenant_id, A.sub_ou_id, vDocTypeAwal, 'SALDO AWAL', vEmptyId,
            'SALDO_AWAL', pDateFrom, vEmptyId, A.product_id, A.warehouse_id,
            A.product_balance_id, A.product_status, A.base_uom_id, SUM(A.qty), 0,
            B.product_code, B.product_name, B.ctgr_product_id, B.sub_ctgr_product_id
        FROM in_summary_monthly_qty A, m_product B
        WHERE A.tenant_id = pTenantId AND
            A.sub_ou_id = pOuId AND
            A.date_year_month = vNextYearMonth AND
            A.warehouse_id = vWarehouseId AND
            A.product_id = B.product_id AND
            A.doc_type_id = vDocTypeAwal
        GROUP BY A.tenant_id, A.sub_ou_id, A.product_id, A.warehouse_id,
            A.product_balance_id, A.product_status, A.base_uom_id,
            B.product_code, B.product_name, B.ctgr_product_id, B.sub_ctgr_product_id;

        INSERT INTO tr_kartu_stok_by_doc_type
        (session_id, sort_no, tenant_id, ou_id, doc_type_id, doc_desc, ref_id,
        doc_no, doc_date, partner_id, product_id, warehouse_id,
        product_balance_id, product_status, base_uom_id, qty_in, qty_out,
        product_code, product_name, ctgr_product_id, sub_ctgr_product_id)
        SELECT pSessionId, 1, A.tenant_id, A.ou_id, vDocTypeAwal, 'SALDO AWAL', vEmptyId,
            'SALDO_AWAL', pDateFrom, vEmptyId, A.product_id, A.warehouse_id,
            A.product_balance_id, A.product_status, A.base_uom_id, SUM(A.qty), 0,
            B.product_code, B.product_name, B.ctgr_product_id, B.sub_ctgr_product_id
        FROM in_log_product_balance_stock A, m_product B
        WHERE A.tenant_id = pTenantId AND
            A.ou_id = pOuId AND
            A.doc_date >= vSaldoDateFrom AND
            A.doc_date < pDateFrom AND
            A.doc_type_id <> vDocTypeAwal AND
            A.warehouse_id = vWarehouseId AND
            A.product_id = B.product_id
        GROUP BY A.tenant_id, A.ou_id, A.product_id, A.warehouse_id,
            A.product_balance_id, A.product_status, A.base_uom_id,
            B.product_code, B.product_name, B.ctgr_product_id, B.sub_ctgr_product_id;

    END IF;

    INSERT INTO tr_kartu_stok_by_doc_type
    (session_id, sort_no, tenant_id, ou_id, doc_type_id, doc_desc, ref_id,
    doc_no, doc_date, partner_id, product_id, warehouse_id,
    product_balance_id, product_status, base_uom_id, qty_in, qty_out,
    product_code, product_name, ctgr_product_id, sub_ctgr_product_id)
    SELECT pSessionId, 2, A.tenant_id, A.ou_id, A.doc_type_id, C.doc_desc, B.inventory_id,
        A.doc_no, A.doc_date, A.partner_id, A.product_id, B.warehouse_from_id,
        A.product_balance_id, A.product_status, A.base_uom_id, SUM(A.qty), 0,
        D.product_code, D.product_name, D.ctgr_product_id, D.sub_ctgr_product_id
    FROM in_log_product_balance_stock A, in_inventory B, m_document C, m_product D
    WHERE A.tenant_id = pTenantId AND
        A.ou_id = pOuId AND
        A.doc_date BETWEEN pDateFrom AND pDateTo AND
        A.doc_type_id IN (vDocTypeTrfIn,vDocTypeTrfInReceipt,vDocTypeTransferIn) AND
        A.ref_id = B.inventory_id AND
        A.doc_type_id = B.doc_type_id AND
        A.doc_type_id = C.doc_type_id AND
        A.product_id = D.product_id
    GROUP BY A.tenant_id, A.ou_id, A.doc_type_id, B.inventory_id, C.doc_desc,
        A.doc_no, A.doc_date, A.partner_id, A.product_id, B.warehouse_from_id,
        A.product_balance_id, A.product_status, A.base_uom_id,
        D.product_code, D.product_name, D.ctgr_product_id, D.sub_ctgr_product_id;


    INSERT INTO tr_kartu_stok_by_doc_type
    (session_id, sort_no, tenant_id, ou_id, doc_type_id, doc_desc, ref_id,
    doc_no, doc_date, partner_id, product_id, warehouse_id,
    product_balance_id, product_status, base_uom_id, qty_in, qty_out,
    product_code, product_name, ctgr_product_id, sub_ctgr_product_id)
    SELECT pSessionId, 2, A.tenant_id, A.ou_id, A.doc_type_id, C.doc_desc, B.inventory_id,
        A.doc_no, A.doc_date, A.partner_id, A.product_id, B.warehouse_to_id,
        A.product_balance_id, A.product_status, A.base_uom_id, 0, SUM(A.qty),
        D.product_code, D.product_name, D.ctgr_product_id, D.sub_ctgr_product_id
    FROM in_log_product_balance_stock A, in_inventory B, m_document C, m_product D
    WHERE A.tenant_id = pTenantId AND
        A.ou_id = pOuId AND
        A.doc_date BETWEEN pDateFrom AND pDateTo AND
        A.doc_type_id IN (vDocTypeTrfOut, vDocTypeTransferOut) AND
        A.ref_id = B.inventory_id AND
        A.doc_type_id = B.doc_type_id AND
        A.doc_type_id = C.doc_type_id AND
        A.product_id = D.product_id
    GROUP BY A.tenant_id, A.ou_id, A.doc_type_id, B.inventory_id, C.doc_desc,
        A.doc_no, A.doc_date, A.partner_id, A.product_id, B.warehouse_to_id,
        A.product_balance_id, A.product_status, A.base_uom_id,
        D.product_code, D.product_name, D.ctgr_product_id, D.sub_ctgr_product_id;


    INSERT INTO tr_kartu_stok_by_doc_type
    (session_id, sort_no, tenant_id, ou_id, doc_type_id, doc_desc, ref_id,
    doc_no, doc_date, partner_id, product_id, warehouse_id,
    product_balance_id, product_status, base_uom_id, qty_in, qty_out,
    product_code, product_name, ctgr_product_id, sub_ctgr_product_id)
    SELECT pSessionId, 3, A.tenant_id, A.ou_id, A.doc_type_id, C.doc_desc, A.ref_id,
        A.doc_no, A.doc_date, A.partner_id, A.product_id, A.warehouse_id,
        A.product_balance_id, A.product_status, A.base_uom_id, SUM(A.qty), 0,
        D.product_code, D.product_name, D.ctgr_product_id, D.sub_ctgr_product_id
    FROM in_log_product_balance_stock A, m_document C, m_product D
    WHERE A.tenant_id = pTenantId AND
        A.ou_id = pOuId AND
        A.doc_date BETWEEN pDateFrom AND pDateTo AND
        A.doc_type_id = vDocTypeAdj AND
        A.doc_type_id = C.doc_type_id AND
        A.product_id = D.product_id
    GROUP BY A.tenant_id, A.ou_id, A.doc_type_id, A.ref_id, C.doc_desc,
        A.doc_no, A.doc_date, A.partner_id, A.product_id, A.warehouse_id,
        A.product_balance_id, A.product_status, A.base_uom_id,
        D.product_code, D.product_name, D.ctgr_product_id, D.sub_ctgr_product_id;


    INSERT INTO tr_kartu_stok_by_doc_type
    (session_id, sort_no, tenant_id, ou_id, doc_type_id, doc_desc, ref_id,
    doc_no, doc_date, partner_id, product_id, warehouse_id,
    product_balance_id, product_status, base_uom_id, qty_in, qty_out,
    product_code, product_name, ctgr_product_id, sub_ctgr_product_id)
    SELECT pSessionId, 2, A.tenant_id, A.ou_id, A.doc_type_id, B.doc_desc, A.ref_id,
        A.doc_no, A.doc_date, A.partner_id, A.product_id, vWarehouseId,
        A.product_balance_id, A.product_status, A.base_uom_id, 0, SUM(A.qty),
        C.product_code, C.product_name, C.ctgr_product_id, C.sub_ctgr_product_id
    FROM in_log_product_balance_stock A, m_document B, m_product C
    WHERE A.tenant_id = pTenantId AND
        A.ou_id = pOuId AND
        A.doc_date BETWEEN pDateFrom AND pDateTo AND
        A.doc_type_id IN (vDocTypePosShop, vDocTypePosShopInShop) AND
        A.doc_type_id = B.doc_type_id AND
        A.product_id = C.product_id
    GROUP BY A.tenant_id, A.ou_id, A.doc_type_id, A.ref_id, B.doc_desc,
        A.doc_no, A.doc_date, A.partner_id, A.product_id,
        A.product_balance_id, A.product_status, A.base_uom_id,
        C.product_code, C.product_name, C.ctgr_product_id, C.sub_ctgr_product_id;

    INSERT INTO tr_kartu_stok_by_doc_type
    (session_id, sort_no, tenant_id, ou_id, doc_type_id, doc_desc, ref_id,
    doc_no, doc_date, partner_id, product_id, warehouse_id,
    product_balance_id, product_status, base_uom_id, qty_in, qty_out,
    product_code, product_name, ctgr_product_id, sub_ctgr_product_id)
    SELECT pSessionId, 3, A.tenant_id, A.ou_id, A.doc_type_id, B.doc_desc, A.ref_id,
        A.doc_no, A.doc_date, A.partner_id, A.product_id, vWarehouseId,
        A.product_balance_id, A.product_status, A.base_uom_id, 0, SUM(A.qty),
        C.product_code, C.product_name, C.ctgr_product_id, C.sub_ctgr_product_id
    FROM in_log_product_balance_stock A, m_document B, m_product C
    WHERE A.tenant_id = pTenantId AND
        A.ou_id = pOuId AND
        A.doc_date BETWEEN pDateFrom AND pDateTo AND
        A.doc_type_id IN (vDocTypeReturnPosShop, vDocTypeReturnPosShopInShop) AND
        A.doc_type_id = B.doc_type_id AND
        A.product_id = C.product_id
    GROUP BY A.tenant_id, A.ou_id, A.doc_type_id, A.ref_id, B.doc_desc,
        A.doc_no, A.doc_date, A.partner_id, A.product_id,
        A.product_balance_id, A.product_status, A.base_uom_id,
        C.product_code, C.product_name, C.ctgr_product_id, C.sub_ctgr_product_id;

    INSERT INTO tr_kartu_stok_by_doc_type
    (session_id, sort_no, tenant_id, ou_id, doc_type_id, doc_desc, ref_id,
    doc_no, doc_date, partner_id, product_id, warehouse_id,
    product_balance_id, product_status, base_uom_id, qty_in, qty_out,
    product_code, product_name, ctgr_product_id, sub_ctgr_product_id)
    SELECT pSessionId, 4, A.tenant_id, A.ou_id, A.doc_type_id, B.doc_desc, A.ref_id,
        A.doc_no, A.doc_date, A.partner_id, A.product_id, vWarehouseId,
        A.product_balance_id, A.product_status, A.base_uom_id, SUM(A.qty), 0,
        C.product_code, C.product_name, C.ctgr_product_id, C.sub_ctgr_product_id
    FROM in_log_product_balance_stock A, m_document B, m_product C
    WHERE A.tenant_id = pTenantId AND
        A.ou_id = pOuId AND
        A.doc_date BETWEEN pDateFrom AND pDateTo AND
        A.doc_type_id IN (vDocTypeVoidPosShop, vDocTypeVoidPosShopInShop) AND
        A.doc_type_id = B.doc_type_id AND
        A.product_id = C.product_id
    GROUP BY A.tenant_id, A.ou_id, A.doc_type_id, A.ref_id, B.doc_desc,
        A.doc_no, A.doc_date, A.partner_id, A.product_id,
        A.product_balance_id, A.product_status, A.base_uom_id,
        C.product_code, C.product_name, C.ctgr_product_id, C.sub_ctgr_product_id;

    IF pCtgrProductId <> vAllId THEN

        DELETE FROM tr_kartu_stok_by_doc_type WHERE session_id = pSessionId AND ctgr_product_id <> pCtgrProductId;

    END IF;

    IF pSubCtgrProductId <> vAllId THEN

        DELETE FROM tr_kartu_stok_by_doc_type WHERE session_id = pSessionId AND sub_ctgr_product_id <> pSubCtgrProductId;

    END IF;

    Open pRefHeader FOR
    SELECT fullName AS full_name, pDateFrom AS date_from, pDateTo AS date_to
    FROM t_user
    WHERE user_id = pUserId;

    RETURN NEXT pRefHeader;

    IF pProductName <> vEmptyValue THEN

        Open pRefDetail FOR
        SELECT A.sort_no, A.product_code AS product_code, A.product_name AS product_name, D.uom_code AS uom_code,
            B.ctgr_product_name AS ctgr_product_name, C.sub_ctgr_product_name AS sub_ctgr_product_name,
            A.doc_desc AS doc_desc, A.doc_no AS doc_no, A.doc_date AS doc_date,
            G.warehouse_name AS warehouse_name, SUM(A.qty_in) AS qty_in, SUM(A.qty_out) AS qty_out
        FROM tr_kartu_stok_by_doc_type A,
            m_ctgr_product B, m_sub_ctgr_product C,
            m_uom D, t_ou F, m_warehouse G
        WHERE A.session_id = pSessionId AND
            A.ctgr_product_id = B.ctgr_product_id AND
            A.sub_ctgr_product_id = C.sub_ctgr_product_id AND
            A.base_uom_id = D.uom_id AND
            A.ou_id = F.ou_id AND
            A.warehouse_id = G.warehouse_id AND
            (A.product_name LIKE '%'||pProductName||'%' OR
            A.product_code LIKE '%'||pProductName||'%')
        GROUP BY B.ctgr_product_name, C.sub_ctgr_product_name, A.product_code, A.product_name, D.uom_code,
            A.doc_desc, A.doc_no, A.doc_date, F.ou_name, G.warehouse_name, A.sort_no
        ORDER BY B.ctgr_product_name, C.sub_ctgr_product_name, A.product_code, A.doc_date, A.sort_no, A.doc_no;

    ELSE

        Open pRefDetail FOR
        SELECT A.sort_no, A.product_code AS product_code, A.product_name AS product_name, D.uom_code AS uom_code,
            B.ctgr_product_name AS ctgr_product_name, C.sub_ctgr_product_name AS sub_ctgr_product_name,
            A.doc_desc AS doc_desc, A.doc_no AS doc_no, A.doc_date AS doc_date,
            G.warehouse_name AS warehouse_name, SUM(A.qty_in) AS qty_in, SUM(A.qty_out) AS qty_out
        FROM tr_kartu_stok_by_doc_type A,
            m_ctgr_product B, m_sub_ctgr_product C,
            m_uom D, t_ou F, m_warehouse G
        WHERE A.session_id = pSessionId AND
            A.ctgr_product_id = B.ctgr_product_id AND
            A.sub_ctgr_product_id = C.sub_ctgr_product_id AND
            A.base_uom_id = D.uom_id AND
            A.ou_id = F.ou_id AND
            A.warehouse_id = G.warehouse_id
        GROUP BY B.ctgr_product_name, C.sub_ctgr_product_name, A.product_code, A.product_name, D.uom_code,
            A.doc_desc, A.doc_no, A.doc_date, F.ou_name, G.warehouse_name, A.sort_no
        ORDER BY B.ctgr_product_name, C.sub_ctgr_product_name, A.product_code, A.doc_date, A.sort_no, A.doc_no;

    END IF;

    RETURN NEXT pRefDetail;

    DELETE FROM tr_kartu_stok_by_doc_type WHERE session_id = pSessionId;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;
  /