glints.public

Parameters

Name	Type	Mode
		IN

Definition

DECLARE
      verification_method TEXT;
      company_identity_details JSONB;
      company_verification_details_value JSONB;
      transaction_id TEXT;
      employer_verification_type TEXT;
      employer_info JSONB;
    BEGIN
      SELECT "verificationMethod" INTO verification_method FROM "CompanyVerificationMethods" WHERE id = NEW."companyVerificationMethodId";

company_identity_details := jsonb_build_object('tier', verification_method);

IF verification_method = 'EMPLOYER_VERIFICATION' THEN
        SELECT value INTO company_verification_details_value
        FROM "CompanyVerificationRequestDetails"
        WHERE "companyVerificationRequestId" = NEW.id
          AND "documentType" = 'EMPLOYER_VERIFICATION'
        LIMIT 1;

IF company_verification_details_value IS NOT NULL THEN
          transaction_id := company_verification_details_value->'value'->>'transactionId';
          employer_verification_type := company_verification_details_value->'value'->>'type';

IF transaction_id IS NOT NULL AND employer_verification_type IS NOT NULL THEN
            SELECT "employerInfo" INTO employer_info
            FROM "EmployerVerifications"
            WHERE "transactionId" = transaction_id
              AND "type" = employer_verification_type::"enum_EmployerVerifications_type"
            LIMIT 1;

IF employer_info IS NOT NULL AND (employer_info->'data') ? 'userName' THEN
              company_identity_details := company_identity_details || jsonb_build_object('userName', employer_info->'data'->>'userName');
            END IF;
          END IF;
        END IF;
      END IF;

INSERT INTO "CompanyIdentities" ("companyId", "type", "status", "details", "createdAt", "updatedAt")
      VALUES (NEW."companyId", 'TIER_VERIFICATION', 'ACTIVE', company_identity_details, NOW(), NOW())
      ON CONFLICT ("companyId", "type") DO UPDATE
      SET details = EXCLUDED.details, "updatedAt" = NOW();
      RETURN NEW;
    END;

upsert_company_identities_tier_verification()

Parameters

Definition