user vs ou

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_role B ON A.policy_id = B.policy_id
            INNER JOIN t_user X on B.user_id = X.user_id
            INNER JOIN t_ou C ON A.ou_id = C.ou_id
            WHERE Z.username = X.username
            AND Z.ou_code = C.ou_code
       );
    UPDATE tt_migrate_mapping_user_ou
    SET flg_validate = vYes,flg_migrate = vInProgress
    WHERE session_id = pSessionId AND flg_migrate = vInProgress;
  END ;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION  f_validate_mapping_user_ou (CHARACTER VARYING, BIGINT, BIGINT, CHARACTER VARYING)
  OWNER TO postgres;