36 lines
1.6 KiB
PL/PgSQL
36 lines
1.6 KiB
PL/PgSQL
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;
|
|
$$;
|