Loading...
Searching...
No Matches
gethistory.sas
Go to the documentation of this file.
1/**
2 @file
3 @brief Returns the list of previously approved / rejected items.
4 @details History is taken from MPE_SUBMIT (where status_cd ne 'SUBMITTED') and
5 filtered according to the groups in MPE_SECURITY (unless the user is in the
6 DC admin group).
7
8 <h4> SAS Macros </h4>
9 @li mpe_getvars.sas
10 @li mpe_getgroups.sas
11 @li mp_abort.sas
12 @li mf_getuser.sas
13
14 <h4> Service Inputs </h4>
15 <h5> BROWSERPARAMS </h5>
16 The following variables MAY be provided from frontend (HIST can also be set
17 in MPE_CONFIG):
18
19 @li HIST - number of records to return
20 @li STARTROW - the starting row (default is 1)
21
22 <h4> Service Outputs </h4>
23
24 <h5> FROMSAS </h5>
25 This table is returned, starting from &STARTROW for &HIST rows (ordered
26 descending on SUBMITTED datetime)
27 @li TABLE_ID
28 @li BASE_TABLE
29 @li SUBMITTED
30 @li SUBMITTED_REASON_TXT
31 @li SUBMITTER
32 @li REVIEWED
33 @li STATUS
34 @li REVIEWED_ON_DTTM
35 @li APPROVER
36
37 <h5> HISTPARAMS </h5>
38
39 @li HIST - rows returned
40 @li STARTROW - starting row used
41 @li NOBS - Number of observations (rows) available
42
43 @version 9.3
44 @author 4GL Apps Ltd
45 @copyright 4GL Apps Ltd. This code may only be used within Data Controller
46 and may not be re-distributed or re-sold without the express permission of
47 4GL Apps Ltd.
48
49**/
50
51%mpeinit()
52
53/* hard coded HIST value */
54%let hist=40;
55%let startrow=1;
56
57/* load parameters from frontend (HIST and STARTROW) */
58data _null_;
59 set &DC_LIBREF..mpe_config(where=(
60 var_scope="DC_REVIEW"
61 and var_name='HISTORY_ROWS'
62 and &dc_dttmtfmt. lt tx_to
63 and var_active=1
64 ));
65 call symputx('hist',var_value,'G');
66 putlog 'mpe_config: ' var_name "=" var_value;
67run;
68
69/* load parameters (override HIST again if provided) */
70%mpe_getvars(BrowserParams, BrowserParams)
71
72
73/* determine users group membership */
74%mpe_getgroups(user=%mf_getuser(),outds=work.usergroups)
75
76PROC FORMAT;
77 picture yymmddhhmmss other='%0Y-%0m-%0d %0H:%0M:%0S' (datatype=datetime);
78RUN;
79
80/* check to see if the user is an admin, or has *ALL* access rights */
81%let authcheck=0;
82proc sql noprint;
83create table work.authcheck
84 as select *
85 from usergroups
86 where upcase(groupname)="%upcase(&mpeadmins)"
87 or upcase(groupname) in (
88 select upcase(sas_group) from &mpelib..mpe_security
89 where libref='*ALL*' and &dc_dttmtfmt. lt tx_to
90 );
91select count(*) into: authcheck from &syslast;
92
93%mp_abort(iftrue= (&syscc > 0)
94 ,mac=&_program
95 ,msg=%str(syscc=&syscc after auth check)
96)
97
98/* now get the previous &hist records from mpe_submit */
99proc sql;
100create view work.submits as
101 select distinct a.TABLE_ID
102 ,cats(a.base_lib,'.',a.base_ds) as base_table
103 ,put(a.SUBMITTED_ON_DTTM,yymmddhhmmss.) as submitted
104 ,a.submitted_reason_txt
105 ,a.submitted_by_nm as submitter
106 ,put(a.REVIEWED_ON_DTTM,yymmddhhmmss.) as REVIEWED
107 ,a.submit_status_cd as status
108 from &mpelib..mpe_submit(where=(submit_status_cd ne 'SUBMITTED')) a
109
110%macro gethistory();
111%if &authcheck=0 %then %do;
112 /* filter for allowed items */
113 left join &mpelib..mpe_security(where=(&dc_dttmtfmt. lt tx_to)) b
114 on a.base_lib=b.libref
115 and (a.base_ds=b.dsn or b.dsn='*ALL*')
116 where upcase(b.SAS_GROUP) in (select upcase(groupname) from work.usergroups)
117 and b.access_level in ('VIEW','AUDIT','EDIT','APPROVE')
118%end;
119%mend gethistory;
120%gethistory()
121;
122
123/* get latest reason text */
124create table work.reviews as
125 select a.*
126 ,b.reviewed_on_dttm
127 ,b.reviewed_by_nm as approver
128 ,b.review_reason_txt
129 from work.submits a
130 left join &mpelib..mpe_review b
131 on a.table_id=b.table_id
132 order by a.table_id desc, b.reviewed_on_dttm desc;
133
134%mp_abort(iftrue= (&syscc > 0)
135 ,mac=&_program
136 ,msg=%str(syscc=&syscc after fetching submits)
137)
138
139data work.fromsas;
140 set work.reviews;
141 by descending table_id descending reviewed_on_dttm;
142 if first.table_id;
143 if _n_ ge &startrow;
144 n+1;
145 if n>&hist then stop;
146 drop n;
147run;
148
149proc sql noprint;
150select count(*) into: nobs from work.submits;
151data work.histparams;
152 hist=&hist;
153 startrow=&startrow;
154 nobs=&nobs;
155run;
156
157%mp_abort(iftrue= (&syscc > 0)
158 ,mac=&_program
159 ,msg=%str(syscc=&syscc)
160)
161%webout(OPEN)
162%webout(OBJ,fromSAS,missing=STRING)
163%webout(OBJ,histparams)
164%webout(CLOSE)
165
166%mpeterm()