CREATE OR REPLACE PROCEDURE public."Prc_P_MaterialRequestAfterSave"(
    "p_Id" uuid
)
LANGUAGE plpgsql
AS $$
BEGIN
    IF "p_Id" IS NULL THEN RETURN; END IF;

    UPDATE "Prc_T_MaterialRequest" r SET
        "DeptApproverUserNames" = (
            SELECT string_agg(u."UserName", '|')
            FROM "Adm_T_Approver" a
            INNER JOIN "AbpUsers" u ON u."UserName" = a."ApprovalUserName" AND u."IsDeleted" = false AND u."IsActive" = true
                AND u."TenantId" IS NOT DISTINCT FROM a."TenantId"
            WHERE a."IsDeleted" = false AND a."IsActive" = true AND a."ApprovalArea" = 'Purchase'
              AND a."DepartmentId" = r."DepartmentId"
              AND a."TenantId" IS NOT DISTINCT FROM r."TenantId"),
        "PurchaseApproverUserNames" = (
            SELECT string_agg(u."UserName", '|')
            FROM "AbpUsers" u
            WHERE u."IsDeleted" = false AND u."IsActive" = true
              AND u."TenantId" IS NOT DISTINCT FROM r."TenantId"
              AND EXISTS (
                  SELECT 1
                  FROM "AbpPermissionGrants" g
                  WHERE g."Name" = 'Prc.MaterialRequestPurchaseApproval.Update'
                    AND g."TenantId" IS NOT DISTINCT FROM r."TenantId"
                    AND ((g."ProviderName" = 'U' AND g."ProviderKey" = u."Id"::text)
                      OR (g."ProviderName" = 'R' AND g."ProviderKey" IN (
                            SELECT ro."Name"
                            FROM "AbpUserRoles" ur
                            INNER JOIN "AbpRoles" ro ON ro."Id" = ur."RoleId"
                            WHERE ur."UserId" = u."Id")))))
    WHERE r."Id" = "p_Id";
END;
$$;