getapprovals.sas

Go to the documentation of this file.

/**
  @file getapprovals.sas
  @brief Returns a list of staged data items that need to be approved
  @details
 
  <h4> SAS Macros </h4>
  @li mpe_getgroups.sas
  @li mp_abort.sas
  @li mf_getuser.sas
 
  @version 9.2
  @author 4GL Apps Ltd
  @copyright 4GL Apps Ltd.  This code may only be used within Data Controller
    and may not be re-distributed or re-sold without the express permission of
    4GL Apps Ltd.
 
**/
 
%mpeinit()
 
/* determine users group membership */
%let user=%mf_getuser();
%mpe_getgroups(user=&user,outds=work.groups)
 
PROC FORMAT;
  picture yymmddhhmmss other='%0Y-%0m-%0d %0H:%0M:%0S' (datatype=datetime);
RUN;
 
proc sql noprint;
create table out1 (rename=(SUBMITTED_ON_DTTM1=SUBMITTED_ON_DTTM)) as
  select table_id
    ,submit_status_cd as REVIEW_STATUS_ID
    ,SUBMITTED_BY_NM
    ,cats(base_lib,'.',base_ds) as base_table
    ,put(submitted_on_dttm,yymmddhhmmss.) as SUBMITTED_ON_DTTM1
    ,submitted_on_dttm as SUBMITTED_ON_DTTM2
    ,submitted_reason_txt
    ,num_of_approvals_required
    ,num_of_approvals_remaining
    ,base_lib as libref
    ,base_ds as dsn
  from &mpelib..mpe_submit (where=(submit_status_cd='SUBMITTED'))
  /* filter out any submits for which approval is already made */
  where table_id not in (
    select table_id from &mpelib..mpe_review where submitted_by_nm="&user"
  );
 
%macro getapprovals();
  %local admin_check;
  select count(*) into: admin_check
    from groups
    where groupname="&mpeadmins"
    or groupname in (
      select sas_group from &mpelib..mpe_security
        where libref='*ALL*'
          and &dc_dttmtfmt. lt tx_to
          and access_level in ('APPROVE')
    );
  %if &admin_check >0 %then %do;
    create table fromSAS as
      select distinct * from out1
      order by SUBMITTED_ON_DTTM2 desc;
  %end;
  %else %do;
    create table fromSAS as
      select distinct a.*
      from out1 a
      inner join &mpelib..mpe_security b
      on a.libref=b.libref
        and (a.dsn=b.dsn or b.dsn='*ALL*')
        and &dc_dttmtfmt. lt b.tx_to
        and b.ACCESS_LEVEL ='APPROVE'
        and b.SAS_GROUP in (select groupname from work.groups)
      order by SUBMITTED_ON_DTTM2 desc;
  %end;
%mend getapprovals;
%getapprovals()
 
%mp_abort(iftrue= (&syscc ne 0)
  ,mac=&_program..sas
  ,msg=%str(syscc=&syscc)
)
 
%webout(OPEN)
%webout(OBJ,fromSAS)
%webout(CLOSE)
 
 
%mpeterm()