SHARE
TWEET

perbaikan 2

a guest Jun 16th, 2019 60 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. CREATE OR REPLACE FUNCTION f_validate_mapping_user_ou (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.     UPDATE tt_migrate_mapping_user_ou
  23.     SET flg_validate = vInProgress, message = ''
  24.     WHERE session_id = pSessionId;
  25.    
  26.     RAISE NOTICE 'function dimulai';
  27.     RAISE NOTICE 'Session ID: %', pSessionId;
  28.     RAISE NOTICE 'Tenant ID: %', pTenantId;  
  29.    
  30.     /* 1.Validasi Username ,Kodeou, Active harus di isi
  31.      * 2.Validasi username harus terdaftar di t_user
  32.      * 3.Validasi ou_code harus terdaftar di t_ou
  33.      * 4.Validasi tidak ada data duplikat saat migrasi
  34.      * 5.Validasi data untuk memastikan data yang dimigrasi belum terdaftar
  35.      * */
  36.    
  37.     /*1.Validasi Username ,Kode ou harus di isi*/
  38.       /*UPDATE tt_migrate_mapping_user_ou A
  39.         SET flg_validate = vNo, message = message || 'Username harus diisi, '
  40.         WHERE session_id = pSessionId
  41.         AND flg_validate IN ( vNo, vInProgress)
  42.         AND A.username = '';*/
  43.        
  44.       UPDATE tt_migrate_mapping_user_ou A
  45.         SET flg_validate = vNo, message = message || 'Kode OU harus diisi, '
  46.         WHERE session_id = pSessionId
  47.         AND flg_validate IN ( vNo, vInProgress)
  48.         AND A.ou_code = '';
  49.                
  50.     /*2.Validasi username harus terdaftar di t_user*/
  51.       UPDATE tt_migrate_mapping_user_ou A
  52.        SET flg_validate = vNo, message = message || 'Username tidak terdaftar,'
  53.        WHERE session_id = pSessionId
  54.        AND flg_validate IN (vNo,vInProgress)
  55.              AND NOT EXISTS (
  56.             SELECT 1 FROM t_user B WHERE A.username = B.username
  57.        );
  58.        
  59.     /*3.Validasi ou_code harus terdaftar di t_ou*/
  60.       UPDATE tt_migrate_mapping_user_ou A
  61.        SET flg_validate = vNo, message = message || 'Kode OU tidak terdaftar,'
  62.        WHERE session_id = pSessionId
  63.        AND flg_validate IN (vNo,vInProgress)
  64.              AND NOT EXISTS (
  65.             SELECT 1 FROM t_ou B WHERE A.ou_code = B.ou_code
  66.        );
  67.        
  68.      /*4.Validasi tidak ada data duplikat saat migrasi
  69.        WITH duplicateCode AS (
  70.         SELECT username,ou_code
  71.         FROM tt_migrate_mapping_user_ou
  72.         WHERE session_id= pSessionId
  73.             AND tenant_id =pTenantId
  74.             GROUP BY ou_code,username
  75.             HAVING COUNT(ou_code) > 1 AND COUNT(username)>1
  76.        )
  77.        UPDATE tt_migrate_mapping_user_ou Z
  78.         SET flg_validate=vYes,message, 'Username dan kode OU Duplikat, ')
  79.         FROM T_ou A, duplicateCode B
  80.         WHERE Z.session_id = pSessionId
  81.         AND Z.tenant_id = pTenantId
  82.         AND Z.ou_code = B.ou_code
  83.         AND A.username = B.username
  84.         AND A.flg_validate = vNo;*/
  85.        
  86.   /* 5.Validasi data untuk memastikan data yang dimigrasi belum terdaftar*/  
  87.       UPDATE tt_migrate_mapping_user_ou Z
  88.        SET flg_validate = vNo, message = message || 'Data telah terdaftar,'
  89.        WHERE session_id = pSessionId
  90.        AND flg_validate IN (vNo,vInProgress)
  91.        AND EXISTS (
  92.       SELECT 1 FROM t_policy_ou A
  93.             INNER JOIN t_user B ON A.user_id = B.user_id
  94.             INNER JOIN t_ou C ON A.ou_id = C.ou_id
  95.             WHERE Z.username = B.username
  96.             AND Z.ou_code = C.ou_code
  97.        );
  98.   END ;
  99. $BODY$
  100.   LANGUAGE plpgsql VOLATILE
  101.   COST 100;
  102. ALTER FUNCTION fs_validate_product(CHARACTER VARYING, BIGINT, BIGINT, CHARACTER VARYING)
  103.   OWNER TO postgres;
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top