CREATE OR REPLACE FUNCTION f_get_latest_catalog_code_by_product_id_for_sdp(bigint)
  RETURNS character varying AS
$BODY$
DECLARE
    pMasterId       alias for $1;

    vResult         character varying;

BEGIN

    vResult := ' ';

    return vResult;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
 /

 /**
 * Adrian, June 22, 2018
 * Ambil product desc dari m_product_catalog
 * Jika ada, ambil yang active = 'Y'
 * Jika tidak ada, ambil yang active = 'N'
 */

CREATE OR REPLACE FUNCTION f_get_product_description_for_sdp(bigint)
  RETURNS character varying AS
$BODY$
DECLARE
    pMasterId               alias for $1;

    vResult         character varying;

BEGIN

    vResult := ' ';

    return vResult;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
 /

/**
 * Adrian, June 25, 2018
 * View untuk product SDP
 */

CREATE OR REPLACE VIEW vw_product_for_sdp AS
    SELECT A.product_id, A.tenant_id, A.product_code, A.product_name,
        COALESCE(B.barcode, '') AS barcode, D.ctgr_product_code, E.sub_ctgr_product_code,
        COALESCE(C.weight, 0) AS weight, COALESCE(C.dimension_length, 0) AS dimension_length,
        COALESCE(C.dimension_width, 0) AS dimension_width, COALESCE(C.dimension_height, 0) AS dimension_height,
        f_get_available_stock_for_sdp(A.product_id) AS available_stock,
        F.style_product, f_get_size_name_by_code(F.size)::character varying(255) AS size, f_get_color_name_by_code(F.color)::character varying(255) AS color,
        A.class_product,
        COALESCE(H.product_ctgr_tree_name, ''::character varying(100)) AS category_name_lv0,
        COALESCE(I.product_ctgr_tree_name, ''::character varying(100)) AS category_name_lv1,
        COALESCE(J.product_ctgr_tree_name, ''::character varying(100)) AS category_name_lv2,
        COALESCE(K.product_ctgr_tree_name, ''::character varying(100)) AS category_name_lv3,
        f_get_brand_name(A.brand_id) AS brand_name,
        f_get_product_description_for_sdp(A.product_id) AS product_description,
        COALESCE(L.online_price, 0) AS online_price,
        COALESCE(L.online_price, 0) AS online_sale_prices,
        F.flg_manage_stock
    FROM m_product A
    INNER JOIN m_ctgr_product D ON A.ctgr_product_id = D.ctgr_product_id
    INNER JOIN m_sub_ctgr_product E ON A.sub_ctgr_product_id = E.sub_ctgr_product_id
    INNER JOIN m_product_custom F ON A.product_id = F.product_id
    INNER JOIN m_product_ctgr_tree G ON A.sub_ctgr_product_id = G.product_ctgr_tree_id
    LEFT JOIN m_product_online_price L ON A.tenant_id = L.tenant_id
        AND f_get_latest_catalog_code_by_product_id_for_sdp(A.product_id) = L.catalog_code
        AND F.style_product = L.style_product
    LEFT JOIN m_product_barcode B ON A.product_id = B.product_id
    LEFT JOIN m_product_weight_dimension C ON A.product_id = C.product_id
    LEFT JOIN m_product_ctgr_tree H ON RPAD(LEFT(G.product_ctgr_tree_code, 1), 1, '#') = H.product_ctgr_tree_code
    LEFT JOIN m_product_ctgr_tree I ON RPAD(LEFT(G.product_ctgr_tree_code, 3), 3, '#') = I.product_ctgr_tree_code
    LEFT JOIN m_product_ctgr_tree J ON RPAD(LEFT(G.product_ctgr_tree_code, 5), 5, '#') = J.product_ctgr_tree_code
    LEFT JOIN m_product_ctgr_tree K ON RPAD(LEFT(G.product_ctgr_tree_code, 7), 7, '#') = K.product_ctgr_tree_code;