daily pastebin goal
65%
SHARE
TWEET

Untitled

a guest Feb 14th, 2018 71 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. CREATE OR REPLACE FUNCTION fs_migrate_merchant_from_th(
  2.     character varying,
  3.     bigint)
  4.   RETURNS void AS
  5. $BODY$
  6. DECLARE
  7.     pDatetime        ALIAS FOR $1;
  8.     pUserId          ALIAS FOR $2;
  9.    
  10.     vYes             character varying(1);
  11.     vEmptyChar       character varying(1);
  12.     vTenantId        bigint;
  13.     vDbName          character varying(50);
  14.     vDbPort          character varying(10);
  15.     vDbHost          character varying(50);
  16.     vDbUser          character varying(50);
  17.     vDbPassword      character varying(50);
  18.    
  19. BEGIN
  20.    
  21.     vYes := 'Y';
  22.     vEmptyChar := '';
  23.     vTenantId := 11;
  24.    
  25.     --mendapatkan data server tujuan dblink
  26.     SELECT f_get_value_system_config_by_param_code(vTenantId, 'PALOMATH.DB.NAME') INTO vDbName;
  27.     SELECT f_get_value_system_config_by_param_code(vTenantId, 'PALOMATH.DB.PORT') INTO vDbPort;
  28.     SELECT f_get_value_system_config_by_param_code(vTenantId, 'PALOMATH.DB.HOSTADDR') INTO vDbHost;
  29.     SELECT f_get_value_system_config_by_param_code(vTenantId, 'PALOMATH.DB.USER') INTO vDbUser;
  30.     SELECT f_get_value_system_config_by_param_code(vTenantId, 'PALOMATH.DB.PASSWORD') INTO vDbPassword;
  31.    
  32.     --dari TH ke table temp di WSC
  33.     INSERT INTO temp_merchant_th(
  34.             merchant_code, merchant_name, merchant_ctgr_code, cp_name, cp_job,
  35.             address1, address2, address3, city, zip_code, country,
  36.             phone1, phone2, fax1, fax2, email, department,
  37.             job_level, phone_ext1, phone_ext2, mobile_phone1, mobile_phone2,  
  38.             flg_responsibility, flg_email_notif, npwp_no, npwp_name,
  39.             npwp_date, flg_pkp, create_datetime, create_user_id, update_datetime, update_user_id)
  40.     SELECT partner_code, partner_name, 'GENERAL', cp_name, cp_job,
  41.         address1, address2, address3, city, zip_code, country,
  42.         phone1, phone2, fax1, fax2, email, department, job_level,
  43.         phone_ext1, phone_ext2, mobile_phone1, mobile_phone2,
  44.         flg_responsibility, flg_email_notif, npwp_no, npwp_name,
  45.         npwp_date, flg_pkp, '20180213172700', -99, '20180213172700', -99
  46.     FROM dblink('dbname='||vDbName||' port='||vDbPort||' hostaddr='||vDbHost||' user='||vDbUser||' password='||vDbPassword||''::text,
  47.     'SELECT A.partner_code, A.partner_name, B.cp_name, B.cp_job,
  48.         B.address1, B.address2, B.address3, B.city, B.zip_code, B.country,
  49.         B.phone1, B.phone2, B.fax1, B.fax2, B.email, B.department, B.job_level,
  50.         B.phone_ext1, B.phone_ext2, B.mobile_phone1, B.mobile_phone2,
  51.         B.flg_responsibility, B.flg_email_notif, COALESCE(C.npwp_no, '' ''), COALESCE(C.npwp_name, '' ''),
  52.         COALESCE(C.npwp_date, '' ''), COALESCE(C.flg_pkp, ''N'')
  53.     FROM m_partner A
  54.     INNER JOIN m_partner_cp B ON A.partner_id = B.partner_id
  55.     LEFT JOIN m_partner_npwp C ON A.partner_id = C.partner_id
  56.     WHERE EXISTS (
  57.     SELECT 1 FROM vw_supplier X
  58.     WHERE X.partner_id = A.partner_id
  59.     )'
  60.     )
  61.     AS X ( partner_code text, partner_name text, cp_name text, cp_job text,
  62.         address1 text, address2 text, address3 text, city text, zip_code text, country text,
  63.         phone1 text, phone2 text, fax1 text, fax2 text, email text, department text, job_level text,
  64.         phone_ext1 text, phone_ext2 text, mobile_phone1 text, mobile_phone2 text,
  65.         flg_responsibility text, flg_email_notif text, npwp_no text, npwp_name text,
  66.         npwp_date text, flg_pkp text)
  67.     WHERE NOT EXISTS(
  68.     SELECT 1 FROM temp_merchant_th Y
  69.     WHERE Y.merchant_code = X.partner_code
  70.     );
  71.        
  72.     --dari table temp ke m_merchant
  73.     INSERT INTO m_merchant(
  74.             tenant_id, merchant_code, merchant_name, merchant_ctgr_code,
  75.             flg_brand_category_mapping, product_introduction, create_datetime,
  76.             create_user_id, update_datetime, update_user_id, active,
  77.             active_datetime, non_active_datetime)
  78.     SELECT vTenantId, merchant_code, merchant_name, merchant_ctgr_code,
  79.             vYes, vEmptyChar, pDatetime,
  80.             pUserId, pDatetime, pUserId, vYes,
  81.             pDatetime, vEmptyChar
  82.     FROM temp_merchant_th A
  83.     WHERE NOT EXISTS (
  84.         SELECT 1 FROM m_merchant X WHERE X.merchant_code = A.merchant_code
  85.     )
  86.     GROUP BY merchant_code, merchant_name, merchant_ctgr_code;
  87.    
  88.     --dari table temp ke m_merchant_cp
  89.     INSERT INTO m_merchant_cp(
  90.             tenant_id, merchant_id, cp_name, cp_job, address1,
  91.             address2, address3, city, zip_code, country, phone1, phone2,
  92.             fax1, fax2, email, department, job_level, phone_ext1, phone_ext2,
  93.             mobile_phone1, mobile_phone2, flg_responsibility, flg_email_notif,
  94.             create_datetime, create_user_id, update_datetime, update_user_id,
  95.             active, active_datetime, non_active_datetime)
  96.     SELECT vTenantId, B.merchant_id, A.cp_name, A.cp_job, A.address1,
  97.             A.address2, A.address3, A.city, A.zip_code, A.country, A.phone1, A.phone2,
  98.             A.fax1, A.fax2, A.email, A.department, A.job_level, A.phone_ext1, A.phone_ext2,
  99.             A.mobile_phone1, A.mobile_phone2, A.flg_responsibility, A.flg_email_notif,
  100.             pDatetime, pUserId, pDatetime, pUserId,
  101.             vYes, pDatetime, vEmptyChar
  102.     FROM temp_merchant_th A
  103.     INNER JOIN m_merchant B ON A.merchant_code = B.merchant_code
  104.     WHERE NOT EXISTS (
  105.         SELECT 1 FROM m_merchant_cp X
  106.         WHERE X.merchant_id = B.merchant_id
  107.         AND X.cp_name = A.cp_name
  108.     )
  109.     GROUP BY B.merchant_id, A.cp_name, A.cp_job, A.address1,
  110.             A.address2, A.address3, A.city, A.zip_code, A.country, A.phone1, A.phone2,
  111.             A.fax1, A.fax2, A.email, A.department, A.job_level, A.phone_ext1, A.phone_ext2,
  112.             A.mobile_phone1, A.mobile_phone2, A.flg_responsibility, A.flg_email_notif;
  113.    
  114.     --dari table temp ke m_merchant_npwp
  115.     INSERT INTO m_merchant_npwp(
  116.             tenant_id, merchant_id, npwp_no, npwp_name,
  117.             npwp_date, flg_pkp, create_datetime, create_user_id, update_datetime,
  118.             update_user_id, active, active_datetime, non_active_datetime)
  119.     SELECT vTenantId, B.merchant_id, A.npwp_no, A.npwp_name,
  120.             A.npwp_date, A.flg_pkp, pDatetime, pUserId, pDatetime,  
  121.             pUserId, vYes, pDatetime, vEmptyChar
  122.     FROM temp_merchant_th A
  123.     INNER JOIN m_merchant B ON A.merchant_code = B.merchant_code
  124.     WHERE NOT EXISTS (
  125.         SELECT 1 FROM m_merchant_npwp X WHERE X.merchant_id = B.merchant_id
  126.     )
  127.     GROUP BY B.merchant_id, A.npwp_no, A.npwp_name,
  128.             A.npwp_date, A.flg_pkp;
  129.    
  130. END;
  131. $BODY$
  132.   LANGUAGE plpgsql VOLATILE
  133.   COST 100;
  134.   /
RAW Paste Data
Top