SHARE
TWEET

perbaikan

a guest Jun 16th, 2019 52 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  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;
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