Advertisement
Guest User

perbaikan

a guest
Jun 16th, 2019
100
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 3.64 KB | None | 0 0
  1. CREATE OR REPLACE FUNCTION fs_validate_product(CHARACTER VARYING, BIGINT,BIGINT,CHARACTER VARYING)
  2. RETURNS void AS
  3. $BODY$
  4. DECLARE
  5. pSessionId ALIAS FOR $1;
  6. pTenantId ALIAS FOR $2;
  7. pUserId ALIAS FOR $3;
  8. pDatetime ALIAS FOR $4;
  9.  
  10.  
  11. vYes CHARACTER VARYING;
  12. vNo CHARACTER VARYING;
  13. vInProgress CHARACTER VARYING;
  14. vActive CHARACTER VARYING;
  15.  
  16. BEGIN
  17. vYes := 'Y';
  18. vNo := 'N';
  19. vInProgress := 'I';
  20. vActive := 'Y';
  21.  
  22. RAISE NOTICE 'function dimulai';
  23. RAISE NOTICE 'Session ID: %', pSessionId;
  24. RAISE NOTICE 'Tenant ID: %', pTenantId;
  25.  
  26. /* 1.Validasi Username ,Kodeou, Active harus di isi
  27. * 2.Validasi username harus terdaftar di t_user
  28. * 3.Validasi ou_code harus terdaftar di t_ou
  29. * 4.Validasi tidak ada data duplikat saat migrasi
  30. * 5.Validasi data untuk memastikan data yang dimigrasi belum terdaftar
  31. * */
  32.  
  33. /*1.Validasi Username ,Kode ou harus di isi*/
  34. /*UPDATE tt_migrate_mapping_user_ou A
  35. SET flg_validate = vNo, message = message || 'Username harus diisi, '
  36. WHERE session_id = pSessionId
  37. AND flg_validate IN ( vNo, vInProgress)
  38. AND A.username = '';*/
  39.  
  40. UPDATE tt_migrate_mapping_user_ou A
  41. SET flg_validate = vNo, message = message || 'Kode OU harus diisi, '
  42. WHERE session_id = pSessionId
  43. AND flg_validate IN ( vNo, vInProgress)
  44. AND A.ou_code = '';
  45.  
  46. /*2.Validasi username harus terdaftar di t_user*/
  47. UPDATE tt_migrate_mapping_user_ou A
  48. SET flg_validate = vNo, message = message || 'Username tidak terdaftar,'
  49. WHERE session_id = pSessionId
  50. AND flg_validate IN (vNo,vInProgress)
  51. AND NOT EXISTS (
  52. SELECT 1 FROM t_user B WHERE A.username = B.username
  53. );
  54.  
  55. /*3.Validasi ou_code harus terdaftar di t_ou*/
  56. UPDATE tt_migrate_mapping_user_ou A
  57. SET flg_validate = vNo, message = message || 'Kode OU tidak terdaftar,'
  58. WHERE session_id = pSessionId
  59. AND flg_validate IN (vNo,vInProgress)
  60. AND NOT EXISTS (
  61. SELECT 1 FROM t_ou B WHERE A.ou_code = B.ou_code
  62. );
  63.  
  64. /*4.Validasi tidak ada data duplikat saat migrasi
  65. WITH duplicateCode AS (
  66. SELECT username,ou_code
  67. FROM tt_migrate_mapping_user_ou
  68. WHERE session_id= pSessionId
  69. AND tenant_id =pTenantId
  70. GROUP BY ou_code,username
  71. HAVING COUNT(ou_code) > 1 AND COUNT(username)>1
  72. )
  73. UPDATE tt_migrate_mapping_user_ou Z
  74. SET flg_validate=vYes,message, 'Username dan kode OU Duplikat, ')
  75. FROM T_ou A, duplicateCode B
  76. WHERE Z.session_id = pSessionId
  77. AND Z.tenant_id = pTenantId
  78. AND Z.ou_code = B.ou_code
  79. AND A.username = B.username
  80. AND A.flg_validate = vNo;*/
  81.  
  82. /* 5.Validasi data untuk memastikan data yang dimigrasi belum terdaftar*/
  83. UPDATE tt_migrate_mapping_user_ou Z
  84. SET flg_validate = vNo, message = message || 'Data telah terdaftar,'
  85. WHERE session_id = pSessionId
  86. AND flg_validate IN (vNo,vInProgress)
  87. AND EXISTS (
  88. SELECT 1 FROM t_policy_ou A
  89. INNER JOIN t_user B ON A.user_id = B.user_id
  90. INNER JOIN t_ou C ON A.ou_id = C.ou_id
  91. WHERE Z.username = B.username
  92. AND Z.ou_code = C.ou_code
  93. );
  94. END ;
  95. $BODY$
  96. LANGUAGE plpgsql VOLATILE
  97. COST 100;
  98. ALTER FUNCTION fs_validate_product(CHARACTER VARYING, BIGINT, BIGINT, CHARACTER VARYING)
  99. OWNER TO postgres;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement