CREATE OR REPLACE FUNCTION r_report_product_catalog_preparation(character varying, bigint)
  RETURNS SETOF refcursor AS
$BODY$
DECLARE
    pCatalogCode         ALIAS FOR $1;
    pTenantId            ALIAS FOR $2;

    vRefHeader              REFCURSOR := 'refHeader';
    vRefDetail              REFCURSOR := 'refDetail';
    vReportTitle            character varying(50) := 'PRODUCT CATALOG PREPARATION LIST';
    vGender                 character varying(255):='GENDER';
    vDisplay                character varying(255):='DISPLAY';
    vBulk                   character varying(255):='BULK';
    vReceive                character varying(255):='RECEIVE';

    vEmpty          character varying(1):='';


BEGIN
    OPEN vRefHeader FOR
        SELECT vReportTitle AS report_title,catalog_name,start_date,end_date
        FROM m_catalog
        WHERE catalog_code = pCatalogCode AND tenant_id = pTenantId;
    RETURN NEXT vRefHeader;

    OPEN vRefDetail FOR

    EXECUTE '
    SELECT A.product_code,B.product_name,A.supplier_code,
     C.partner_name,E.ctgr_product_code,E.ctgr_product_name,
     A.brand_code,F.brand_name,G.value,A.product_catalog_code,
     A.product_catalog_name,A.doc_no,A.doc_date,
     A.flg_lanjut_catalog,A.qty_commit,A.flg_consign,
     A.supplier_price,A.cogs,A.catalog_price,A.price_after_disc,A.price_after_disc2,
     A.flg_discontinue,A.halaman_katalok,
     A.pemakaian_halaman,H.disc_member_percentage,H.disc_promo_percentage,
     H.margin_supplier_percentage,H.margin_internal_percentage,SUM(I.qty)AS stock_qty,A.flg_launching,A.product_catalog_desc,
     A.catalog_price2,A.nett_price2,K.username,
     COALESCE(L.style_product, $6) AS style_product, N.product_value
     FROM m_product_catalog_preparation
     A LEFT JOIN m_product
     B ON A.product_code = B.product_code
     LEFT JOIN vw_supplier C ON A.supplier_code = C.partner_code
     LEFT JOIN m_ctgr_product
     E ON B.ctgr_product_id = E.ctgr_product_id
     LEFT JOIN m_brand
     F ON A.brand_code = F.brand_code
     LEFT JOIN m_product_custom_field
     G ON B.product_id = G.product_id AND product_attr_code = $2
     LEFT JOIN m_product_margin_supplier
     H ON F.brand_id = H.brand_id AND C.partner_id = H.supplier_id AND CAST(A.disc_member_percent AS numeric) = H.disc_member_percentage AND CAST(A.disc_promo_percent AS numeric) = H.disc_promo_percentage
     LEFT JOIN in_product_balance_stock
     I ON I.product_id =B.product_id
     LEFT JOIN m_warehouse
     J ON J.warehouse_id = I.warehouse_id AND J.warehouse_code in ($3,$4,$5)
     LEFT JOIN t_user K ON A.update_user_id = K.user_id
     LEFT JOIN m_product_custom L ON B.product_id = L.product_id
     LEFT JOIN m_catalog M ON A.catalog_code = M.catalog_code
     LEFT JOIN m_style_product N ON L.style_product = N.style_product AND M.catalog_id = N.catalog_id
     WHERE A.catalog_code = $1
     GROUP BY A.product_code,B.product_name,A.supplier_code,
         C.partner_name,E.ctgr_product_code,E.ctgr_product_name,
     A.brand_code,F.brand_name,G.value,A.product_catalog_code,
     A.product_catalog_name,A.doc_no,A.doc_date,
     A.flg_lanjut_catalog,A.qty_commit,A.flg_consign,
     A.supplier_price,A.cogs,A.catalog_price,A.price_after_disc,A.price_after_disc2,
     A.flg_discontinue,A.halaman_katalok,
     A.pemakaian_halaman,H.disc_member_percentage,H.disc_promo_percentage,
     H.margin_supplier_percentage,H.margin_internal_percentage,A.flg_launching,A.product_catalog_desc,
     A.catalog_price2,A.nett_price2,K.username,
     L.style_product,N.product_value'
    USING pCatalogCode,vGender,vBulk,vDisplay,vReceive,vEmpty;

    RETURN NEXT vRefDetail;

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