CREATE OR REPLACE FUNCTION fs_migrate_merchant_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
	INSERT INTO temp_merchant_th(
            merchant_code, merchant_name, merchant_ctgr_code, cp_name, cp_job,
            address1, address2, address3, city, zip_code, country,
            phone1, phone2, fax1, fax2, email, department,
            job_level, phone_ext1, phone_ext2, mobile_phone1, mobile_phone2,
            flg_responsibility, flg_email_notif, npwp_no, npwp_name,
            npwp_date, flg_pkp, create_datetime, create_user_id, update_datetime, update_user_id)
	SELECT partner_code, partner_name, 'GENERAL', cp_name, cp_job,
	    address1, address2, address3, city, zip_code, country,
	    phone1, phone2, fax1, fax2, email, department, job_level,
	    phone_ext1, phone_ext2, mobile_phone1, mobile_phone2,
	    flg_responsibility, flg_email_notif, npwp_no, npwp_name,
	    npwp_date, flg_pkp, pDatetime, pUserId, pDatetime, pUserId
	FROM dblink('dbname='||vDbName||' port='||vDbPort||' hostaddr='||vDbHost||' user='||vDbUser||' password='||vDbPassword||''::text,
	'SELECT A.partner_code, A.partner_name, B.cp_name, B.cp_job,
	    B.address1, B.address2, B.address3, B.city, B.zip_code, B.country,
	    B.phone1, B.phone2, B.fax1, B.fax2, B.email, B.department, B.job_level,
	    B.phone_ext1, B.phone_ext2, B.mobile_phone1, B.mobile_phone2,
	    B.flg_responsibility, B.flg_email_notif, COALESCE(C.npwp_no, '' ''), COALESCE(C.npwp_name, '' ''),
	    COALESCE(C.npwp_date, '' ''), COALESCE(C.flg_pkp, ''N'')
	FROM m_partner A
	INNER JOIN m_partner_cp B ON A.partner_id = B.partner_id
	LEFT JOIN m_partner_npwp C ON A.partner_id = C.partner_id
	WHERE EXISTS (
	SELECT 1 FROM vw_supplier X
	WHERE X.partner_id = A.partner_id
	)'
	)
	AS X ( partner_code text, partner_name text, cp_name text, cp_job text,
	    address1 text, address2 text, address3 text, city text, zip_code text, country text,
	    phone1 text, phone2 text, fax1 text, fax2 text, email text, department text, job_level text,
	    phone_ext1 text, phone_ext2 text, mobile_phone1 text, mobile_phone2 text,
	    flg_responsibility text, flg_email_notif text, npwp_no text, npwp_name text,
	    npwp_date text, flg_pkp text)
	WHERE NOT EXISTS(
	SELECT 1 FROM temp_merchant_th Y
	WHERE Y.merchant_code = X.partner_code
	);

	--dari table temp ke m_merchant
	INSERT INTO m_merchant(
            tenant_id, merchant_code, merchant_name, merchant_ctgr_code,
            flg_brand_category_mapping, product_introduction, create_datetime,
            create_user_id, update_datetime, update_user_id, active,
            active_datetime, non_active_datetime)
    SELECT vTenantId, merchant_code, merchant_name, merchant_ctgr_code,
    		vYes, vEmptyChar, pDatetime,
    		pUserId, pDatetime, pUserId, vYes,
    		pDatetime, vEmptyChar
    FROM temp_merchant_th A
    WHERE NOT EXISTS (
    	SELECT 1 FROM m_merchant X WHERE X.merchant_code = A.merchant_code
    )
    GROUP BY merchant_code, merchant_name, merchant_ctgr_code;

    --dari table temp ke m_merchant_cp
    INSERT INTO m_merchant_cp(
            tenant_id, merchant_id, cp_name, cp_job, address1,
            address2, address3, city, zip_code, country, phone1, phone2,
            fax1, fax2, email, department, job_level, phone_ext1, phone_ext2,
            mobile_phone1, mobile_phone2, flg_responsibility, flg_email_notif,
            create_datetime, create_user_id, update_datetime, update_user_id,
            active, active_datetime, non_active_datetime)
    SELECT vTenantId, B.merchant_id, A.cp_name, A.cp_job, A.address1,
            A.address2, A.address3, A.city, A.zip_code, A.country, A.phone1, A.phone2,
            A.fax1, A.fax2, A.email, A.department, A.job_level, A.phone_ext1, A.phone_ext2,
            A.mobile_phone1, A.mobile_phone2, A.flg_responsibility, A.flg_email_notif,
    		pDatetime, pUserId, pDatetime, pUserId,
    		vYes, pDatetime, vEmptyChar
    FROM temp_merchant_th A
    INNER JOIN m_merchant B ON A.merchant_code = B.merchant_code
    WHERE NOT EXISTS (
    	SELECT 1 FROM m_merchant_cp X
    	WHERE X.merchant_id = B.merchant_id
    	AND X.cp_name = A.cp_name
    )
    GROUP BY B.merchant_id, A.cp_name, A.cp_job, A.address1,
            A.address2, A.address3, A.city, A.zip_code, A.country, A.phone1, A.phone2,
            A.fax1, A.fax2, A.email, A.department, A.job_level, A.phone_ext1, A.phone_ext2,
            A.mobile_phone1, A.mobile_phone2, A.flg_responsibility, A.flg_email_notif;

    --dari table temp ke m_merchant_npwp
    INSERT INTO m_merchant_npwp(
            tenant_id, merchant_id, npwp_no, npwp_name,
            npwp_date, flg_pkp, create_datetime, create_user_id, update_datetime,
            update_user_id, active, active_datetime, non_active_datetime)
    SELECT vTenantId, B.merchant_id, A.npwp_no, A.npwp_name,
    		A.npwp_date, A.flg_pkp, pDatetime, pUserId, pDatetime,
    		pUserId, vYes, pDatetime, vEmptyChar
    FROM temp_merchant_th A
    INNER JOIN m_merchant B ON A.merchant_code = B.merchant_code
    WHERE NOT EXISTS (
    	SELECT 1 FROM m_merchant_npwp X WHERE X.merchant_id = B.merchant_id
    )
    GROUP BY B.merchant_id, A.npwp_no, A.npwp_name,
    		A.npwp_date, A.flg_pkp;

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