CREATE OR REPLACE FUNCTION fs_migrate_product_margin_supplier_from_th(
    character varying,
    bigint)
  RETURNS void AS
$BODY$
DECLARE
	pDatetime    	 ALIAS FOR $1;
	pUserId	    	 ALIAS FOR $2;

	vYes 			 character varying(1);
	vEmptyChar 		 character varying(1);
	vTenantId		 bigint;
	vDbName			 character varying(50);
	vDbPort			 character varying(10);
	vDbHost			 character varying(50);
	vDbUser 		 character varying(50);
	vDbPassword		 character varying(50);

BEGIN

	vYes := 'Y';
	vEmptyChar := '';
	vTenantId := 11;

	--mendapatkan data server tujuan dblink
	SELECT f_get_value_system_config_by_param_code(vTenantId, 'PALOMATH.DB.NAME') INTO vDbName;
	SELECT f_get_value_system_config_by_param_code(vTenantId, 'PALOMATH.DB.PORT') INTO vDbPort;
	SELECT f_get_value_system_config_by_param_code(vTenantId, 'PALOMATH.DB.HOSTADDR') INTO vDbHost;
	SELECT f_get_value_system_config_by_param_code(vTenantId, 'PALOMATH.DB.USER') INTO vDbUser;
	SELECT f_get_value_system_config_by_param_code(vTenantId, 'PALOMATH.DB.PASSWORD') INTO vDbPassword;

	--dari TH ke table temp di WSC
	UPDATE temp_product_margin_supplier_th A
	SET
		disc_member_percentage = X.disc_member_percentage,
		disc_promo_percentage = X.disc_promo_percentage,
		margin_supplier_percentage = X.margin_supplier_percentage,
		margin_internal_percentage = X.margin_internal_percentage,
		update_datetime = pDatetime,
		update_user_id = pUserId,
		version = version + 1
	FROM dblink('dbname='||vDbName||' port='||vDbPort||' hostaddr='||vDbHost||' user='||vDbUser||' password='||vDbPassword||''::text,
	'SELECT B.partner_code, C.brand_code,
	A.disc_member_percentage, A.disc_promo_percentage, A.margin_supplier_percentage, A.margin_internal_percentage
	FROM m_product_margin_supplier A
	INNER JOIN m_partner B ON A.supplier_id = B.partner_id
	INNER JOIN m_brand C ON A.brand_id = C.brand_id'
	)
	AS X ( merchant_code text, brand_code text, disc_member_percentage numeric, disc_promo_percentage numeric,
	    margin_supplier_percentage numeric, margin_internal_percentage numeric)
	WHERE A.merchant_code = X.merchant_code
	AND A.brand_code = X.brand_code;

	INSERT INTO temp_product_margin_supplier_th(
            merchant_code, brand_code, disc_member_percentage,
            disc_promo_percentage, margin_supplier_percentage, margin_internal_percentage,
            create_datetime, create_user_id, update_datetime, update_user_id
            )
	SELECT merchant_code, brand_code, disc_member_percentage,
	disc_promo_percentage, margin_supplier_percentage, margin_internal_percentage,
	pDatetime, pUserId, pDatetime, pUserId
	FROM dblink('dbname='||vDbName||' port='||vDbPort||' hostaddr='||vDbHost||' user='||vDbUser||' password='||vDbPassword||''::text,
	'SELECT B.partner_code, C.brand_code,
	A.disc_member_percentage, A.disc_promo_percentage, A.margin_supplier_percentage, A.margin_internal_percentage
	FROM m_product_margin_supplier A
	INNER JOIN m_partner B ON A.supplier_id = B.partner_id
	INNER JOIN m_brand C ON A.brand_id = C.brand_id'
	)
	AS X ( merchant_code text, brand_code text, disc_member_percentage numeric, disc_promo_percentage numeric,
	    margin_supplier_percentage numeric, margin_internal_percentage numeric)
	WHERE NOT EXISTS(
	SELECT 1 FROM temp_product_margin_supplier_th Y
	WHERE Y.merchant_code = X.merchant_code
	AND Y.brand_code = X.brand_code);

	-- update m_product_margin_supplier
	UPDATE m_product_margin_supplier A
	SET
		disc_member_percentage = W.disc_member_percentage,
		disc_promo_percentage = W.disc_promo_percentage,
		margin_supplier_percentage = W.margin_supplier_percentage,
		margin_internal_percentage = W.margin_internal_percentage,
		update_datetime = pDatetime,
		update_user_id = pUserId,
		version = version + 1
	FROM (
		SELECT X.merchant_code, X.brand_code, X.disc_member_percentage, X.disc_promo_percentage,
		X.margin_supplier_percentage, X.margin_internal_percentage
		FROM temp_product_margin_supplier_th X
		INNER JOIN m_product_margin_supplier Y
		ON X.merchant_code = Y.supplier_code AND X.brand_code = Y.brand_code
	) W
	WHERE A.supplier_code = W.merchant_code
	AND A.brand_code = W.brand_code;

	--	dari table temp ke m_product_margin_supplier
	INSERT INTO m_product_margin_supplier(
            tenant_id, supplier_code, brand_code,
            disc_member_percentage, disc_promo_percentage, margin_supplier_percentage,
            margin_internal_percentage, create_datetime, create_user_id,
            update_datetime, update_user_id, active, active_datetime,
            non_active_datetime)
    SELECT vTenantId, merchant_code, brand_code,
    		disc_member_percentage, disc_promo_percentage, margin_supplier_percentage,
    		margin_internal_percentage, pDatetime, pUserId,
    		pDatetime, pUserId, vYes, pDatetime, vEmptyChar
    FROM temp_product_margin_supplier_th A
    WHERE NOT EXISTS (
    	SELECT 1 FROM m_product_margin_supplier X
    	WHERE X.supplier_code = A.merchant_code
    	AND X.brand_code = A.brand_code
    )
    GROUP BY merchant_code, brand_code,
    		disc_member_percentage, disc_promo_percentage, margin_supplier_percentage,
    		margin_internal_percentage;

    --	dari table temp ke m_merchant_brand
	INSERT INTO m_merchant_brand(
            merchant_code, brand_code, create_datetime,
            create_user_id, update_datetime, update_user_id, active,
            active_datetime, non_active_datetime)
    SELECT merchant_code, brand_code, pDatetime,
    		pUserId, pDatetime, pUserId, vYes,
    		pDatetime, vEmptyChar
    FROM temp_product_margin_supplier_th A
    WHERE NOT EXISTS (
    	SELECT 1 FROM m_merchant_brand X
    	WHERE X.merchant_code = A.merchant_code
    	AND X.brand_code = A.brand_code
    )
    GROUP BY merchant_code, brand_code;


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