f_validate_mapping_user_salesman

    CREATE OR REPLACE FUNCTION f_validate_mapping_user_salesman(character varying,bigint, bigint,character varying)
        RETURNS void AS
    $BODY$
    DECLARE

        pSessionId      ALIAS FOR $1;
        pTenantId       ALIAS FOR $2;
        pUserId         ALIAS FOR $3;
        datetime        ALIAS FOR $4;

        vYes            CHARACTER VARYING;
        vNo             CHARACTER VARYING;
        vInProgress     CHARACTER VARYING;

    BEGIN
        vYes    :=  'Y';
        vNo :=  'N';
        vInProgress :=  'I';

        RAISE NOTICE 'function dimulai';
        RAISE NOTICE 'Session Id : %',pSessionId;
        RAISE NOTICE 'Tenant Id : %',pTenantId;

        UPDATE tt_migrate_mapping_user_salesman
        SET flg_validate = vInProgress, message = ''
        WHERE session_id = pSessionId;

        --Validasi Username, Kode Salesman, dan Active yang harus diisi
           UPDATE tt_migrate_mapping_user_salesman 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_salesman A
            SET flg_validate = vNo, message = message || 'Kode Salesman harus diisi, '
            WHERE session_id = pSessionId
            AND flg_validate IN ( vNo, vInProgress)
            AND A.partner_code = '';

           UPDATE tt_migrate_mapping_user_salesman A
            SET flg_validate = vNo, message = message || 'Active harus diisi, '
            WHERE session_id = pSessionId
            AND flg_validate IN ( vNo, vInProgress)
            AND A.active = '';

        -- Validasi username harus terdaftar di table t_user
            UPDATE tt_migrate_mapping_user_salesman 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
               );

        --Validasi kode salesman harus terdaftar di table m_partner
           UPDATE tt_migrate_mapping_user_salesman A
            SET flg_validate = vNo, message = message || 'Kode Salesman tidak terdaftar, '
            WHERE session_id = pSessionId
            AND flg_validate IN ( vNo, vInProgress)
            AND NOT EXISTS (
                SELECT 1 FROM m_partner B WHERE A.partner_code = B.partner_code
            );

        --Validasi Active berisi Y/N
           UPDATE tt_migrate_mapping_user_salesman A
            SET flg_validate = vNo, message = message || 'Active harus berisi Y atau N, '
            WHERE session_id = pSessionId
            AND flg_validate = vInProgress
            AND A.active NOT IN ( vYes, vNo);

        /*--Validasi data duplikat berdasarkan username dan kode salesman
            WITH duplicateCode AS (
                SELECT username, partner_code
                FROM tt_migrate_mapping_user_salesman
                WHERE session_id = pSessionId
                    AND tenant_id = pTenantId
                GROUP BY partner_code,username
                HAVING COUNT(partner_code) > 1 AND COUNT(username) > 1
            )
            UPDATE tt_migrate_mapping_user_salesman Z
                SET flg_validate=vYes, message = 'Username dan Kode Salesman Duplikat'
            FROM t_user A, duplicateCode B
            WHERE Z.session_id = pSessionId
                AND A.session_id = Z.session_id
                AND A.tenantt_id = Z.tenant_id
                AND Z.partner_code = B.partner_code
                AND A.username = B.username
                AND A.flg_validate = vInProgress;*/

          --Validasi data yang di migrasi belum terdaftar
           UPDATE tt_migrate_mapping_user_salesman 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 m_policy_salesman A
                INNER JOIN t_user B ON A.user_id = B.user_id
                INNER JOIN m_partner C ON A.partner_id = C.partner_id
                WHERE Z.username = B.username
                    AND Z.partner_code = C.partner_code
            );

        UPDATE tt_migrate_mapping_user_salesman
        SET flg_validate = vYes, flg_migrate = vInProgress
        WHERE session_id = pSessionId AND flg_validate=vInProgress;

    END ;
    $BODY$
      LANGUAGE plpgsql VOLATILE
      COST 100;
    ALTER FUNCTION f_validate_mapping_user_salesman(CHARACTER VARYING, bigint,bigint, CHARACTER VARYING)
      OWNER TO postgres;