CREATE OR REPLACE FUNCTION f_get_inquiry_stock_qty_for_paloma(character varying, bigint, bigint, bigint, bigint, bigint, character varying, character varying, character varying, character varying, bigint, bigint)
  RETURNS SETOF refcursor AS
$BODY$
DECLARE
    pRefQtyDetail       REFCURSOR := 'refQtyDetail';
    pSessionId          ALIAS FOR $1;
    pTenantId           ALIAS FOR $2;
    pOuId               ALIAS FOR $3;
    pBrandId            ALIAS FOR $4;
    pCtgrProductId      ALIAS FOR $5;
    pSubCtgrProductId   ALIAS FOR $6;
    pProductCode            ALIAS FOR $7;
    pProductName            ALIAS FOR $8;
    pShowData           ALIAS FOR $9;
    pWarehouseName      ALIAS FOR $10;
    pLimit              ALIAS FOR $11;
    pOffset             ALIAS FOR $12;

    vEmptyId            bigint := -99;

    vEmptyIdString      character varying := '-99';
    vEmptyString        character varying := '';
    vFlagYes            character varying := 'Y';

    vFilterBrandId            text := '';
    vFilterCtgrProductId      text := '';
    vFilterSubCtgrProductId   text := '';
    vFilterProductCode        text := '';
    vFilterProductName        text := '';
    vFilterProductCode2       text := '';
    vFilterProductName2       text := '';
    vFilterWarehouseName      text := '';
    vFilterShowData           text := '';
    vFilterLimit              text := '';
    vFilterOffset             text := '';
    vUpperProductCode         text := UPPER(pProductCode);
    vUpperProductName         text := UPPER(pProductName);
    vUpperWarehouseName       text := UPPER(pWarehouseName);

BEGIN
    -- hapus tabel penampung data stok produk semua warehouse tanpa filter warehousename
    DELETE FROM tr_get_inquiry_stock_qty WHERE session_id = pSessionId;
    -- hapus tabel penampung data stok produk semua warehouse dengan filter warehousename
    DELETE FROM tr_get_inquiry_stock_all_qty WHERE session_id = pSessionId;
    -- hapus tabel penampung data warehouse
    DELETE FROM tr_warehouse_inquiry_stock WHERE session_id = pSessionId;
    -- hapus tabel penampung data product
    DELETE FROM tr_product_inquiry_stock WHERE session_id = pSessionId;

    /**
     * Ambil dari table saldo filter berdasarkan ,
     * Tenant, OU, Brand, kategory, subkategory, produk , dan qty
     */

    IF (pBrandId <> vEmptyId) THEN
        vFilterBrandId := ' AND E.brand_id = ' || pBrandId;
    END IF;

    IF (pCtgrProductId <> vEmptyId) THEN
        vFilterCtgrProductId := ' AND E.ctgr_product_id = ' || pCtgrProductId;
    END IF;

    IF (pSubCtgrProductId <> vEmptyId) THEN
        vFilterSubCtgrProductId := ' AND E.sub_ctgr_product_id = ' || pSubCtgrProductId;
    END IF;

    IF (pProductCode <> vEmptyString) THEN
        vFilterProductCode := ' AND UPPER(E.product_code) = ''' || vUpperProductCode || '''';
    END IF;

    IF (pProductName <> vEmptyString) THEN
        vFilterProductName := ' AND UPPER(E.product_name) like ''%' || vUpperProductName || '%''';
    END IF;

    IF (pWarehouseName <> vEmptyString) THEN
        vFilterWarehouseName  := ' AND UPPER(F.warehouse_name) like ''%' || vUpperWarehouseName || '%''';
    END IF;

    IF (pShowData <> vEmptyIdString) THEN
        vFilterShowData := ' HAVING SUM(A.qty) > 0 ';
    END IF;


    IF (pLimit <> vEmptyId) THEN
        vFilterLimit := ' LIMIT  ' || pLimit;
    END IF;

    IF (pOffset <> vEmptyId) THEN
        vFilterOffset := ' OFFSET  ' || pOffset;
    END IF;

    Open pRefQtyDetail FOR
      EXECUTE  ' SELECT A.product_id, f_get_product_code(A.product_id) AS product_code, f_get_product_name(A.product_id) AS product_name, '||
               '        A.warehouse_id, f_get_warehouse_code(A.warehouse_id) AS warehouse_code, f_get_warehouse_name(A.warehouse_id) AS warehouse_name, '||
               '        SUM(A.qty) AS qty, A.base_uom_id, f_get_uom_code(A.base_uom_id) AS base_uom_code, f_get_uom_name(A.base_uom_id) AS base_uom_name, D.flg_serial_number, '||
               '        A.product_status, A.whs_location_code '||
               ' FROM in_product_balance_stock A '||
               ' INNER JOIN m_product E ON A.product_id = E.product_id '||
               ' INNER JOIN m_warehouse_ou B ON A.warehouse_id = B.warehouse_id '||
               ' INNER JOIN m_warehouse F ON A.warehouse_id = F.warehouse_id '||
               ' INNER JOIN m_sub_ctgr_product D ON E.sub_ctgr_product_id = D.sub_ctgr_product_id '||
               ' WHERE A.tenant_id = '|| pTenantId ||
                vFilterWarehouseName ||
                vFilterProductCode ||
                vFilterProductName ||
                vFilterSubCtgrProductId ||
                vFilterCtgrProductId ||
                vFilterBrandId ||
                ' AND B.ou_id IN ( ' ||
                '     SELECT C.ou_id FROM m_ou_structure C WHERE C.ou_bu_id = '|| pOuId ||' OR C.ou_branch_id = '|| pOuId ||' OR C.ou_sub_bu_id = '|| pOuId ||
                ' ) ' ||
                ' GROUP BY A.product_id, A.warehouse_id, A.base_uom_id, D.flg_serial_number, A.product_status ,A.whs_location_code '||
                vFilterShowData ||
                ' ORDER BY product_code ASC, product_name ASC, warehouse_name ASC, qty DESC ' ||
                vFilterLimit ||
                vFilterOffset;

            RETURN NEXT pRefQtyDetail ;


    -- hapus tabel penampung data stok produk semua warehouse tanpa filter warehousename
    DELETE FROM tr_get_inquiry_stock_qty WHERE session_id = pSessionId;
    -- hapus tabel penampung data stok produk semua warehouse dengan filter warehousename
    DELETE FROM tr_get_inquiry_stock_all_qty WHERE session_id = pSessionId;
    -- hapus tabel penampung data warehouse
    DELETE FROM tr_warehouse_inquiry_stock WHERE session_id = pSessionId;
    -- hapus tabel penampung data product
    DELETE FROM tr_product_inquiry_stock WHERE session_id = pSessionId;

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