CREATE view usd_v_group_jobs
(group_name,  group_uuid,
    agent_name, agent_type, agent_uuid,
    productname, productversion, product_uuid,
    object_uuid, task, state,
    errorcause, errorparam,
    activationtime,
    creationtime, completiontime,
    userparams, packagetype,
    jobname, job_uuid,
    "procedure", procedureversion, procedure_uuid)

as
select GR.label, GR.group_uuid,
    CA.agent_name, CA.agent_type, CA.object_uuid,
    RSW.productname, RSW.productversion, RSW.objectid,
    APP.objectid, APP.task, APP.status,
    APP.errorcause, APP.errorparam,
    APP.activationtime,
    APP.creationtime, APP.completiontime,
    APP.userparams, RSW.packagetype,
    ACTIV.jobname, ACTIV.objectid,
    ACP.itemname, ACP.itemversion, ACP.objectid

from ca_group_def GR, ca_group_member GM,
    usd_v_agents CA, usd_target T,
    usd_applic APP, usd_actproc ACP, usd_rsw RSW, usd_activity ACTIV
where
    GR.group_uuid = GM.group_uuid         and
    GM.member_uuid =CA.object_uuid         and
    (CA.agent_type=1 or CA.agent_type=4)     and
        CA.object_uuid    =T.objectid         and
    T.objectid     = APP.target         and
    APP.actproc     = ACP.objectid         and
    ACP.rsw     = RSW.objectid         and
    APP.activity     = ACTIV.objectid
GO
GRANT SELECT ON  [dbo].[usd_v_group_jobs] TO [ams_group]
GRANT REFERENCES ON  [dbo].[usd_v_group_jobs] TO [ca_itrm_group]
GRANT SELECT ON  [dbo].[usd_v_group_jobs] TO [ca_itrm_group]
GRANT INSERT ON  [dbo].[usd_v_group_jobs] TO [ca_itrm_group]
GRANT DELETE ON  [dbo].[usd_v_group_jobs] TO [ca_itrm_group]
GRANT UPDATE ON  [dbo].[usd_v_group_jobs] TO [ca_itrm_group]
GRANT SELECT ON  [dbo].[usd_v_group_jobs] TO [ca_itrm_group_ams]
GRANT SELECT ON  [dbo].[usd_v_group_jobs] TO [upmuser_group]
GO