Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- CREATE OR REPLACE FUNCTION f_validate_mapping_user_ou (CHARACTER VARYING, BIGINT,BIGINT,CHARACTER VARYING)
- RETURNS void AS
- $BODY$
- DECLARE
- pSessionId ALIAS FOR $1;
- pTenantId ALIAS FOR $2;
- pUserId ALIAS FOR $3;
- pDatetime ALIAS FOR $4;
- vYes CHARACTER VARYING;
- vNo CHARACTER VARYING;
- vInProgress CHARACTER VARYING;
- vActive CHARACTER VARYING;
- BEGIN
- vYes := 'Y';
- vNo := 'N';
- vInProgress := 'I';
- vActive := 'Y';
- UPDATE tt_migrate_mapping_user_ou
- SET flg_validate = vInProgress, message = ''
- WHERE session_id = pSessionId;
- RAISE NOTICE 'function dimulai';
- RAISE NOTICE 'Session ID: %', pSessionId;
- RAISE NOTICE 'Tenant ID: %', pTenantId;
- /* 1.Validasi Username ,Kodeou, Active harus di isi
- * 2.Validasi username harus terdaftar di t_user
- * 3.Validasi ou_code harus terdaftar di t_ou
- * 4.Validasi tidak ada data duplikat saat migrasi
- * 5.Validasi data untuk memastikan data yang dimigrasi belum terdaftar
- * */
- /*1.Validasi Username ,Kode ou harus di isi*/
- /*UPDATE tt_migrate_mapping_user_ou A
- SET flg_validate = vNo, message = message || 'Username harus diisi, '
- WHERE session_id = pSessionId
- AND flg_validate IN ( vNo, vInProgress)
- AND A.username = '';*/
- UPDATE tt_migrate_mapping_user_ou A
- SET flg_validate = vNo, message = message || 'Kode OU harus diisi, '
- WHERE session_id = pSessionId
- AND flg_validate IN ( vNo, vInProgress)
- AND A.ou_code = '';
- /*2.Validasi username harus terdaftar di t_user*/
- UPDATE tt_migrate_mapping_user_ou A
- SET flg_validate = vNo, message = message || 'Username tidak terdaftar,'
- WHERE session_id = pSessionId
- AND flg_validate IN (vNo,vInProgress)
- AND NOT EXISTS (
- SELECT 1 FROM t_user B WHERE A.username = B.username
- );
- /*3.Validasi ou_code harus terdaftar di t_ou*/
- UPDATE tt_migrate_mapping_user_ou A
- SET flg_validate = vNo, message = message || 'Kode OU tidak terdaftar,'
- WHERE session_id = pSessionId
- AND flg_validate IN (vNo,vInProgress)
- AND NOT EXISTS (
- SELECT 1 FROM t_ou B WHERE A.ou_code = B.ou_code
- );
- /*4.Validasi tidak ada data duplikat saat migrasi
- WITH duplicateCode AS (
- SELECT username,ou_code
- FROM tt_migrate_mapping_user_ou
- WHERE session_id= pSessionId
- AND tenant_id =pTenantId
- GROUP BY ou_code,username
- HAVING COUNT(ou_code) > 1 AND COUNT(username)>1
- )
- UPDATE tt_migrate_mapping_user_ou Z
- SET flg_validate=vYes,message, 'Username dan kode OU Duplikat, ')
- FROM T_ou A, duplicateCode B
- WHERE Z.session_id = pSessionId
- AND Z.tenant_id = pTenantId
- AND Z.ou_code = B.ou_code
- AND A.username = B.username
- AND A.flg_validate = vNo;*/
- /* 5.Validasi data untuk memastikan data yang dimigrasi belum terdaftar*/
- UPDATE tt_migrate_mapping_user_ou Z
- SET flg_validate = vNo, message = message || 'Data telah terdaftar,'
- WHERE session_id = pSessionId
- AND flg_validate IN (vNo,vInProgress)
- AND EXISTS (
- SELECT 1 FROM t_policy_ou A
- INNER JOIN t_user B ON A.user_id = B.user_id
- INNER JOIN t_ou C ON A.ou_id = C.ou_id
- WHERE Z.username = B.username
- AND Z.ou_code = C.ou_code
- );
- END ;
- $BODY$
- LANGUAGE plpgsql VOLATILE
- COST 100;
- ALTER FUNCTION fs_validate_product(CHARACTER VARYING, BIGINT, BIGINT, CHARACTER VARYING)
- OWNER TO postgres;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement