gethistory.sas

Go to the documentation of this file.

/**
  @file
  @brief Returns the list of previously approved / rejected items.
  @details History is taken from MPE_SUBMIT (where status_cd ne 'SUBMITTED') and
  filtered according to the groups in MPE_SECURITY (unless the user is in the
  DC admin group).
 
  <h4> SAS Macros </h4>
  @li mpe_getvars.sas
  @li mpe_getgroups.sas
  @li mp_abort.sas
  @li mf_getuser.sas
 
  <h4> Service Inputs </h4>
  <h5> BROWSERPARAMS </h5>
  The following variables MAY be provided from frontend (HIST can also be set
  in MPE_CONFIG):
 
  @li HIST - number of records to return
  @li STARTROW - the starting row (default is 1)
 
  <h4> Service Outputs </h4>
 
  <h5> FROMSAS </h5>
  This table is returned, starting from &STARTROW for &HIST rows (ordered
  descending on SUBMITTED datetime)
  @li TABLE_ID
  @li BASE_TABLE
  @li SUBMITTED
  @li SUBMITTED_REASON_TXT
  @li SUBMITTER
  @li REVIEWED
  @li STATUS
  @li REVIEWED_ON_DTTM
  @li APPROVER
 
  <h5> HISTPARAMS </h5>
 
  @li HIST - rows returned
  @li STARTROW - starting row used
  @li NOBS - Number of observations (rows) available
 
  @version 9.3
  @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()
 
/* hard coded HIST value */
%let hist=40;
%let startrow=1;
 
/* load parameters from frontend (HIST and STARTROW) */
data _null_;
  set &DC_LIBREF..mpe_config(where=(
    var_scope="DC_REVIEW"
    and var_name='HISTORY_ROWS'
    and &dc_dttmtfmt. lt tx_to
    and var_active=1
  ));
  call symputx('hist',var_value,'G');
  putlog 'mpe_config: ' var_name "=" var_value;
run;
 
/* load parameters (override HIST again if provided) */
%mpe_getvars(BrowserParams, BrowserParams)
 
 
/* determine users group membership */
%mpe_getgroups(user=%mf_getuser(),outds=work.usergroups)
 
PROC FORMAT;
  picture yymmddhhmmss other='%0Y-%0m-%0d %0H:%0M:%0S' (datatype=datetime);
RUN;
 
/* check to see if the user is an admin, or has *ALL* access rights */
%let authcheck=0;
proc sql noprint;
create table work.authcheck
  as select *
  from usergroups
  where upcase(groupname)="%upcase(&mpeadmins)"
  or upcase(groupname) in (
    select upcase(sas_group) from &mpelib..mpe_security
      where libref='*ALL*' and &dc_dttmtfmt. lt tx_to
  );
select count(*) into: authcheck from &syslast;
 
%mp_abort(iftrue= (&syscc > 0)
  ,mac=&_program
  ,msg=%str(syscc=&syscc after auth check)
)
 
/* now get the previous &hist records from mpe_submit */
proc sql;
create view work.submits as
  select distinct a.TABLE_ID
    ,cats(a.base_lib,'.',a.base_ds) as base_table
    ,put(a.SUBMITTED_ON_DTTM,yymmddhhmmss.) as submitted
    ,a.submitted_reason_txt
    ,a.submitted_by_nm  as submitter
    ,put(a.REVIEWED_ON_DTTM,yymmddhhmmss.) as REVIEWED
    ,a.submit_status_cd as status
  from &mpelib..mpe_submit(where=(submit_status_cd ne 'SUBMITTED')) a
 
%macro gethistory();
%if &authcheck=0 %then %do;
  /* filter for allowed items */
  left join &mpelib..mpe_security(where=(&dc_dttmtfmt. lt tx_to)) b
    on a.base_lib=b.libref
      and (a.base_ds=b.dsn or b.dsn='*ALL*')
  where upcase(b.SAS_GROUP) in (select upcase(groupname) from work.usergroups)
    and b.access_level in ('VIEW','AUDIT','EDIT','APPROVE')
%end;
%mend gethistory;
%gethistory()
;
 
/* get latest reason text */
create table work.reviews as 
  select a.* 
    ,b.reviewed_on_dttm
    ,b.reviewed_by_nm as approver
    ,b.review_reason_txt 
  from work.submits a 
  left join &mpelib..mpe_review b 
  on a.table_id=b.table_id 
  order by a.table_id desc, b.reviewed_on_dttm desc;
 
%mp_abort(iftrue= (&syscc > 0)
  ,mac=&_program
  ,msg=%str(syscc=&syscc after fetching submits)
)
 
data work.fromsas;
  set work.reviews;
  by descending table_id descending reviewed_on_dttm;
  if first.table_id;
  if _n_ ge &startrow;
  n+1;
  if n>&hist then stop;
  drop n;
run;
 
proc sql noprint;
select count(*) into: nobs from work.submits;
data work.histparams;
  hist=&hist;
  startrow=&startrow;
  nobs=&nobs;
run;
 
%mp_abort(iftrue= (&syscc > 0)
  ,mac=&_program
  ,msg=%str(syscc=&syscc)
)
%webout(OPEN)
%webout(OBJ,fromSAS)
%webout(OBJ,histparams)
%webout(CLOSE)
 
%mpeterm()