Advertisement
Guest User

Untitled

a guest
Feb 14th, 2018
88
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 3.99 KB | None | 0 0
  1. CREATE OR REPLACE FUNCTION fs_migrate_product_margin_supplier_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_product_margin_supplier_th(
  34. merchant_code, brand_code, disc_member_percentage,
  35. disc_promo_percentage, margin_supplier_percentage, margin_internal_percentage,
  36. create_datetime, create_user_id, update_datetime, update_user_id
  37. )
  38. SELECT merchant_code, brand_code, disc_member_percentage,
  39. disc_promo_percentage, margin_supplier_percentage, margin_internal_percentage,
  40. pDatetime, pUserId, pDatetime, pUserId
  41. FROM dblink('dbname='||vDbName||' port='||vDbPort||' hostaddr='||vDbHost||' user='||vDbUser||' password='||vDbPassword||''::text,
  42. 'SELECT B.partner_code, C.brand_code,
  43. A.disc_member_percentage, A.disc_promo_percentage, A.margin_supplier_percentage, A.margin_internal_percentage
  44. FROM m_product_margin_supplier A
  45. INNER JOIN m_partner B ON A.supplier_id = B.partner_id
  46. INNER JOIN m_brand C ON A.brand_id = C.brand_id'
  47. )
  48. AS X ( merchant_code text, brand_code text, disc_member_percentage numeric, disc_promo_percentage numeric,
  49. margin_supplier_percentage numeric, margin_internal_percentage numeric)
  50. WHERE NOT EXISTS(
  51. SELECT 1 FROM temp_product_margin_supplier_th Y
  52. WHERE Y.merchant_code = X.merchant_code
  53. AND Y.brand_code = X.brand_code);
  54.  
  55. -- dari table temp ke m_product_margin_supplier
  56. INSERT INTO m_product_margin_supplier(
  57. tenant_id, supplier_code, brand_code,
  58. disc_member_percentage, disc_promo_percentage, margin_supplier_percentage,
  59. margin_internal_percentage, create_datetime, create_user_id,
  60. update_datetime, update_user_id, active, active_datetime,
  61. non_active_datetime)
  62. SELECT vTenantId, merchant_code, brand_code,
  63. disc_member_percentage, disc_promo_percentage, margin_supplier_percentage,
  64. margin_internal_percentage, pDatetime, pUserId,
  65. pDatetime, pUserId, vYes, pDatetime, vEmptyChar
  66. FROM temp_product_margin_supplier_th A
  67. WHERE NOT EXISTS (
  68. SELECT 1 FROM m_product_margin_supplier X
  69. WHERE X.supplier_code = A.merchant_code
  70. AND X.brand_code = A.brand_code
  71. )
  72. GROUP BY merchant_code, brand_code,
  73. disc_member_percentage, disc_promo_percentage, margin_supplier_percentage,
  74. margin_internal_percentage;
  75.  
  76. -- dari table temp ke m_merchant_brand
  77. INSERT INTO m_merchant_brand(
  78. merchant_code, brand_code, create_datetime,
  79. create_user_id, update_datetime, update_user_id, active,
  80. active_datetime, non_active_datetime)
  81. SELECT merchant_code, brand_code, pDatetime,
  82. pUserId, pDatetime, pUserId, vYes,
  83. pDatetime, vEmptyChar
  84. FROM temp_product_margin_supplier_th A
  85. WHERE NOT EXISTS (
  86. SELECT 1 FROM m_merchant_brand X
  87. WHERE X.merchant_code = A.merchant_code
  88. AND X.brand_code = A.brand_code
  89. )
  90. GROUP BY merchant_code, brand_code;
  91.  
  92.  
  93. END;
  94. $BODY$
  95. LANGUAGE plpgsql VOLATILE
  96. COST 100;
  97. /
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement