1 package body WFA_HTML_JSP as
2 /* $Header: wfjspb.pls 120.6 2006/04/27 23:47:46 hgandiko noship $ */
3
4 /* get notification id given item type, item key, username.
5 ** Created for integration with SSP Orders to Approve
6 **/
7 function getSSPNid (
8 username IN VARCHAR2,
9 itemtype IN VARCHAR2,
10 itemkey IN VARCHAR2
11 )
12 return number
13 IS
14 nid number;
15 colon pls_integer; -- Magic orig_system decoder
16 uorig_system varchar2(8); -- User orig_system for indexes
17 uorig_system_id pls_integer; -- User orig_system_id for indexes
18 ctx varchar2(2000);
19 BEGIN
20
21 -- Fetch user orig_system_ids for indexes in main cursor
22 begin
23 -- cannot rewrite using wf_directory package because of pragma WNPS
24 colon := instr(username, ':');
25 if (colon = 0) then
26 select WR.ORIG_SYSTEM, WR.ORIG_SYSTEM_ID
27 into uorig_system, uorig_system_id
28 from WF_ROLES WR
29 where WR.NAME = username
30 and WR.ORIG_SYSTEM NOT IN ('HZ_PARTY','POS','ENG_LIST','AMV_CHN',
31 'HZ_GROUP','CUST_CONT');
32
33 else
34 select WR.ORIG_SYSTEM, WR.ORIG_SYSTEM_ID
35 into uorig_system, uorig_system_id
36 from WF_ROLES WR
37 where WR.ORIG_SYSTEM = substr(username, 1, colon-1)
38 and WR.ORIG_SYSTEM_ID = substr(username, colon+1)
39 and WR.NAME = username;
40 end if;
41 exception
42 when no_data_found then
43 -- wf_core.token('ROLE', username);
44 -- wf_core.raise('WFNTF_ROLE');
45 raise;
46 end;
47
48 ctx := itemtype||':'||itemkey||':'||'%';
49 select MAX(notification_id) into nid
50 from (
51 select notification_id, context
52 from WF_NOTIFICATIONS
53 where more_info_role = username
54 union all
55 select notification_id, context
56 from WF_NOTIFICATIONS
57 where more_info_role is null
58 and RECIPIENT_ROLE in
59 (select WUR.ROLE_NAME
60 from WF_USER_ROLES WUR
61 where WUR.USER_ORIG_SYSTEM = getSSPNid.uorig_system
62 and WUR.USER_ORIG_SYSTEM_ID = getSSPNid.uorig_system_id
63 and WUR.USER_NAME = username)
64 ) v
65 where v.context like ctx;
66 return nid;
67
68 exception
69 when others then
70 -- wf_core.context('Wfa_Html_Jsp', 'getSSPNid', username, itemtype, itemkey);
71 raise;
72 end getSSPNid;
73
74
75 /* get notification id given item type, item key, username.
76 ** Created for integration with SSP Orders to Approve
77 ** returns open notifications only
78 **/
79 function getSSPOpenNid (
80 username IN VARCHAR2,
81 itemtype IN VARCHAR2,
82 itemkey IN VARCHAR2
83 )
84 return number
85 IS
86 nid number;
87 colon pls_integer; -- Magic orig_system decoder
88 uorig_system varchar2(8); -- User orig_system for indexes
89 uorig_system_id pls_integer; -- User orig_system_id for indexes
90 ctx varchar2(2000);
91 BEGIN
92
93 -- Fetch user orig_system_ids for indexes in main cursor
94 begin
95 -- cannot rewrite using wf_directory package because of pragma WNPS
96 colon := instr(username, ':');
97 if (colon = 0) then
98 select WR.ORIG_SYSTEM, WR.ORIG_SYSTEM_ID
99 into uorig_system, uorig_system_id
100 from WF_ROLES WR
101 where WR.NAME = username
102 and WR.ORIG_SYSTEM NOT IN ('HZ_PARTY','POS','ENG_LIST','AMV_CHN',
103 'HZ_GROUP','CUST_CONT');
104
105 else
106 select WR.ORIG_SYSTEM, WR.ORIG_SYSTEM_ID
107 into uorig_system, uorig_system_id
108 from WF_ROLES WR
109 where WR.ORIG_SYSTEM = substr(username, 1, colon-1)
110 and WR.ORIG_SYSTEM_ID = substr(username, colon+1)
111 and WR.NAME = username;
112 end if;
113 exception
114 when no_data_found then
115 -- wf_core.token('ROLE', username);
116 -- wf_core.raise('WFNTF_ROLE');
117 raise;
118 end;
119
120 ctx := itemtype||':'||itemkey||':'||'%';
121 select MAX(notification_id) into nid
122 from (
123 select notification_id, context
124 from WF_NOTIFICATIONS
125 where more_info_role = username
126 and status = 'OPEN'
127 union all
128 select notification_id, context
129 from WF_NOTIFICATIONS
130 where more_info_role is null
131 and RECIPIENT_ROLE in
132 (select WUR.ROLE_NAME
133 from WF_USER_ROLES WUR
134 where WUR.USER_ORIG_SYSTEM = getSSPOpenNid.uorig_system
135 and WUR.USER_ORIG_SYSTEM_ID = getSSPOpenNid.uorig_system_id
136 and WUR.USER_NAME = username)
137 and status = 'OPEN'
138 ) v
139 where v.context like ctx;
140
141 return nid;
142
143 exception
144 when others then
145 -- wf_core.context('Wfa_Html_Jsp', 'getSSPOpenNid', username, itemtype, itemkey);
146 raise;
147 end getSSPOpenNid;
148
149 end WFA_HTML_JSP;
[Home] [Help]