[Home] [Help]
PACKAGE BODY: APPS.BIS_PMV_APPROVALS_PVT
Source
1 PACKAGE BODY BIS_PMV_APPROVALS_PVT as
2 /* $Header: BISAPPVB.pls 120.0.12000000.1 2007/01/19 17:54:36 appldev ship $ */
3 -- dbdrv: sql ~PROD ~PATH ~FILE none none none package &phase=plb \
4 -- dbdrv: checkfile(115.13=120.0):~PROD:~PATH:~FILE
5 ----------------------------------------------------------------------------
6 -- PACKAGE: BIS_PMV_APPROVALS_PVT
7 -- --
8 -- DESCRIPTION: Approvals APIs for PMV
9 -- --
10 -- MODIFICATIONS --
11 -- Date User Modification
12 -- XX-XXX-XX XXXXXXXX Modifications made, which procedures changed & --
13 -- list bug number, if fixing a bug. --
14 -- --
15 -- 12/02/05 nkishore Initial creation --
16 ----------------------------------------------------------------------------
17 PROCEDURE APPROVALS_SQL (p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL
18 ,x_custom_sql OUT NOCOPY VARCHAR2
19 ,x_custom_attr OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
20 l_sql varchar2(5000);
21 l_exp_url varchar2(1000);
22 l_req_url varchar2(1000);
23 l_custom_rec BIS_QUERY_ATTRIBUTES;
24 l_bind_ctr NUMBER;
25 l_code_table BISVIEWER.t_char;
26 l_meaning_table BISVIEWER.t_char;
27 l_type_where VARCHAR2(1000);
28 l_types VARCHAR(1000);
29 l_mean_types VARCHAR2(1000);
30 l_msg_types BISVIEWER.t_char;
31 l_notif_count BISVIEWER.t_num;
32 l_more_msg_types BISVIEWER.t_char;
33 l_more_notif_count BISVIEWER.t_num;
34 l_found boolean;
35 l_first boolean;
36 l_len NUMBER;
37
38 CURSOR getApprovalTypes is
39 SELECT lookup_code, meaning FROM fnd_lookup_values_vl WHERE lookup_type = 'BIS_PMV_APPROVAL_TYPES';
40
41 CURSOR get_Notifications IS
42 SELECT n.message_type, count(distinct n.notification_id)
43 FROM wf_notifications n, wf_notification_attributes a
44 where n.notification_id = a.notification_id
45 AND ( (n.more_info_role is null)
46 AND (n.recipient_role in (SELECT role_name from wf_user_roles where user_name=FND_GLOBAL.USER_NAME)))
47 and n.status='OPEN' and n.message_type in (SELECT lookup_code FROM fnd_lookup_values WHERE lookup_type = 'BIS_PMV_APPROVAL_TYPES')
48 group by n.message_type;
49
50 CURSOR get_more_notifications IS
51 SELECT n.message_type, count(distinct n.notification_id)
52 FROM wf_notifications n, wf_notification_attributes a
53 where n.notification_id = a.notification_id AND
54 (n.more_info_role in (SELECT role_name from wf_user_roles where user_name=FND_GLOBAL.USER_NAME))
55 and n.status='OPEN' and n.message_type in (SELECT lookup_code FROM fnd_lookup_values WHERE lookup_type = 'BIS_PMV_APPROVAL_TYPES')
56 group by n.message_type;
57
58 BEGIN
59 l_exp_url := '''pFunctionName=BIS_PMV_APPROVALS_DETAIL&pParamIds=Y&APPR_STATUS+APPR_STATUS=OPEN&DBI_OBJ_TYPE=BIS_NOTIFY_PROMPT''';
60 l_req_url := '''pFunctionName=BIS_PMV_APPROVALS_DETAIL&pParamIds=Y&APPR_STATUS+APPR_STATUS=OPEN&DBI_OBJ_TYPE=BIS_NOTIFY_PROMPT''';
61 --l_exp_url := '''pFunctionName=WF_SS_NOTIFICATIONS&pParamIds=Y'''; --put this if there is a change later for notifications
62
63 IF get_Notifications%ISOPEN THEN
64 CLOSE get_Notifications;
65 END IF;
66 OPEN get_Notifications;
67 FETCH get_Notifications BULK COLLECT INTO l_msg_types, l_notif_count;
68 CLOSE get_Notifications;
69
70 IF get_more_notifications%ISOPEN THEN
71 CLOSE get_more_notifications;
72 END IF;
73 OPEN get_more_notifications;
74 FETCH get_more_notifications BULK COLLECT INTO l_more_msg_types, l_more_notif_count;
75 CLOSE get_more_notifications;
76
77 IF l_more_msg_types IS NOT NULL AND l_more_msg_types.COUNT >0 THEN
78 FOR i IN l_more_msg_types.FIRST..l_more_msg_types.LAST LOOP
79 l_found := false;
80 IF ( (l_msg_types IS NOT NULL) AND (l_msg_types.COUNT >0) ) THEN
81 FOR j IN l_msg_types.FIRST..l_msg_types.LAST LOOP
82 IF(l_more_msg_types(i) = l_msg_types(j)) THEN
83 l_notif_count(j) := l_notif_count(j)+l_more_notif_count(i);
84 l_found := true;
85 END IF;
86 END LOOP;
87 END IF;
88 IF(NOT l_found) THEN
89 l_len := l_msg_types.COUNT;
90 l_msg_types(l_len+1) := l_more_msg_types(i);
91 l_notif_count(l_len+1) := l_more_notif_count(i);
92 END IF;
93 END LOOP;
94 END IF;
95
96 IF getApprovalTypes%ISOPEN THEN
97 CLOSE getApprovalTypes;
98 END IF;
99 OPEN getApprovalTypes;
100 FETCH getApprovalTypes BULK COLLECT INTO l_code_table, l_meaning_table;
101 CLOSE getApprovalTypes;
102
103 l_custom_rec := BIS_PMV_PARAMETERS_PUB.Initialize_Query_Type;
104 x_custom_attr := BIS_QUERY_ATTRIBUTES_TBL();
105 l_bind_ctr := 1;
106 l_first := true;
107
108 IF l_msg_types IS NOT NULL AND l_msg_types.COUNT >0 THEN
109 FOR i IN l_msg_types.FIRST..l_msg_types.LAST LOOP
110 IF(NOT l_first) THEN
111 l_sql := l_sql || ' UNION ';
112 END IF;
113
114 l_custom_rec.attribute_name := ':l_msgtype'||i;
115 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
116 l_custom_rec.attribute_value := l_msg_types(i);
117 l_custom_rec.attribute_data_type :=BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
118 x_custom_attr.Extend();
119 x_custom_attr(l_bind_ctr):=l_custom_rec;
120 l_bind_ctr:=l_bind_ctr+1;
121
122 l_mean_types := ' decode(:l_msgtype'||i;
123
124 IF l_code_table IS NOT NULL AND l_code_table.COUNT >0 THEN
125 FOR j IN l_code_table.FIRST..l_code_table.LAST LOOP
126 l_mean_types := l_mean_types||' ,'||':l_appcode'||j||', '||':l_app_meaning'||j;
127 END LOOP;
128 END IF;
129
130 l_mean_types := l_mean_types||' ,:l_msgtype'||i||') VIEWBY, ';
131
132 l_sql := l_sql || ' (SELECT '|| l_mean_types || ' :l_msgtype'||i||' BIS_NOTIFY_PROMPT, ' || l_notif_count(i) ||' BIS_OBJECT_ROW_COUNT, '||
133 l_exp_url ||' BISREPORTURL '||
134 ' FROM DUAL) ';
135 l_first := false;
136 END LOOP;
137 END IF;
138 IF( l_first) THEN
139 l_sql := ' SELECT n.message_type VIEWBY, n.message_type BIS_NOTIFY_PROMPT, 0 BIS_OBJECT_ROW_COUNT, '||'''url'''||' BISREPORTURL '||
140 ' FROM wf_notifications n WHERE 1=2 ';
141 END IF;
142 /*
143 l_sql := ' SELECT '||l_mean_types||
144 ' n.message_type BIS_NOTIFY_PROMPT, count(distinct n.notification_id) BIS_OBJECT_ROW_COUNT, '|| l_exp_url ||' BISREPORTURL '||
145 ' FROM wf_notifications n, wf_notification_attributes a
146 where n.notification_id = a.notification_id
147 and n.status ='||'''OPEN'''||l_type_where||
148 ' and n.recipient_role in (SELECT role_name from wf_user_roles where user_name=FND_GLOBAL.USER_NAME) '||
149 ' group by n.message_type ';*/
150
151 x_custom_sql := l_sql;
152
153
154 IF l_code_table IS NOT NULL AND l_code_table.COUNT >0 THEN
155 FOR i IN l_code_table.FIRST..l_code_table.LAST LOOP
156 l_custom_rec.attribute_name :=':l_appcode'||i;
157 l_custom_rec.attribute_value := l_code_table(i);
158 l_custom_rec.attribute_data_type :=BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
159 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
160 x_custom_attr.Extend();
161 x_custom_attr(l_bind_ctr):=l_custom_rec;
162 l_bind_ctr:=l_bind_ctr+1;
163
164 l_custom_rec.attribute_name :=':l_app_meaning'||i;
165 l_custom_rec.attribute_value := l_meaning_table(i);
166 l_custom_rec.attribute_data_type :=BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
167 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
168 x_custom_attr.Extend();
169 x_custom_attr(l_bind_ctr):=l_custom_rec;
170 l_bind_ctr:=l_bind_ctr+1;
171
172 END LOOP;
173 END IF;
174
175 END APPROVALS_SQL;
176
177 PROCEDURE APPROVALS_DETAIL_SQL (p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL
178 ,x_custom_sql OUT NOCOPY VARCHAR2
179 ,x_custom_attr OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
180 l_sql varchar2(5000);
181 l_drill_url varchar2(1000);
182 l_status varchar2(30);
183 l_msg_type varchar2(80);
184 l_status_where varchar2(250);
185 l_type_where varchar2(250);
186 l_orderby VARCHAR2(250);
187 l_custom_rec BIS_QUERY_ATTRIBUTES;
188 l_bind_ctr NUMBER;
189 l_code_table BISVIEWER.t_char;
190 l_types VARCHAR2(250);
191 l_select_clause VARCHAR2(2000);
192 l_from_clause VARCHAR2(200);
193 l_notif_where_clause VARCHAR2(200);
194 l_recipient_where_clause VARCHAR2(500);
195 l_more_where_clause VARCHAR2(500);
196 l_isOpen boolean;
197 --l_req_url varchar2(1000);
198 cursor getApprovalTypes is
199 SELECT lookup_code FROM fnd_lookup_values_vl WHERE lookup_type = 'BIS_PMV_APPROVAL_TYPES';
200 BEGIN
201 FOR i in 1..p_page_parameter_tbl.COUNT
202 LOOP
203 IF (p_page_parameter_tbl(i).parameter_name = 'APPR_STATUS+APPR_STATUS') THEN
204 l_status := p_page_parameter_tbl(i).parameter_id;
205 ELSIF (p_page_parameter_tbl(i).parameter_name = 'APPR_MSGTYPE+APPR_MSGTYPE') THEN
206 l_msg_type := p_page_parameter_tbl(i).parameter_id;
207 ELSIF (p_page_parameter_tbl(i).parameter_name = 'ORDERBY') THEN
208 l_orderby := ' ORDER BY '|| p_page_parameter_tbl(i).parameter_value;
209 END IF;
210 END LOOP;
211 l_isOpen := false;
212 IF ((l_msg_type is not null))THEN
213 l_msg_type := replace(l_msg_type, '''','');
214 END IF;
215 IF ((l_status is not null))THEN
216 l_status := replace(l_status, '''','');
217 END IF;
218
219 IF (l_msg_type ='APEXP') THEN
220 l_type_where := ' and n.message_type='||'''APEXP''';
221 ELSIF (l_msg_type = 'REQAPPRV') THEN
222 l_type_where := ' and n.message_type='||'''REQAPPRV''';
223 ELSIF (l_msg_type is not null) THEN
224 l_type_where := ' and n.message_type=:msg_type ';
225 ELSE
226 --Start of getting all approval types
227 IF getApprovalTypes%ISOPEN THEN
228 CLOSE getApprovalTypes;
229 END IF;
230 OPEN getApprovalTypes;
231 FETCH getApprovalTypes BULK COLLECT INTO l_code_table;
232 CLOSE getApprovalTypes;
233
234 IF l_code_table IS NOT NULL AND l_code_table.COUNT >0 THEN
235 FOR i IN l_code_table.FIRST..l_code_table.LAST LOOP
236 l_types := l_types||','||':l_appcode'||i;
237 END LOOP;
238 END IF;
239 l_type_where := ' AND n.message_type IN('||substr(l_types,2)||') ';
240 --End of getting all approval types
241 END IF;
242 IF (l_status ='OPEN') THEN
243 l_isOpen := true;
244 l_status_where := ' and n.status='||'''OPEN''';
245 ELSIF (l_status = 'CLOSED') THEN
246 l_status_where := ' and n.status='||'''CLOSED''';
247 ELSIF (l_status = 'CANCELED') THEN
248 l_status_where := ' and n.status='||'''CANCELED''';
249 ELSE
250 l_status_where := ' ';
251 END IF;
252
253 --l_drill_url := '''pFunctionName=FII_IEXPENSES_DRILL&dbiReportHeaderId=BIS_COLUMN_5&pParamIds=Y&APPR_MSGTYPE=BIS_STATUS''';
254 --l_req_url := '''pFunctionName=POA_DBI_REQ_DRILL&reqHeaderId=BIS_COLUMN_5''';
255
256 l_drill_url := '''pFunctionName=FND_WFNTF_DETAILS&NtfId=BIS_COLUMN_5&pParamIds=Y''';
257
258 l_select_clause := ' SELECT distinct FROM_USER BIS_COLUMN_1, n.subject BIS_COLUMN_2,'||
259 ' n.begin_date BIS_COLUMN_3, n.end_date BIS_COLUMN_4,'||
260 ' n.notification_id BIS_COLUMN_5 ,'||
261 l_drill_url ||' BIS_COLUMN_6 ';
262 l_from_clause := ' FROM wf_notifications n, wf_notification_attributes a ';
263 l_notif_where_clause := ' where n.notification_id = a.notification_id ';
264 IF (l_isOpen) THEN
265 l_recipient_where_clause := ' AND ( (n.more_info_role is null) AND (n.recipient_role in (SELECT role_name from wf_user_roles where user_name=FND_GLOBAL.USER_NAME))) ';
266 ELSE
267 l_recipient_where_clause := ' AND ( n.recipient_role in (SELECT role_name from wf_user_roles where user_name=FND_GLOBAL.USER_NAME)) ';
268 END IF;
269 l_more_where_clause := ' AND (n.more_info_role in (SELECT role_name from wf_user_roles where user_name=FND_GLOBAL.USER_NAME)) ';
270
271
272 l_sql := '('||l_select_clause || l_from_clause ||l_notif_where_clause||
273 l_recipient_where_clause ||
274 l_status_where || l_type_where ||')';
275 l_sql := l_sql||' UNION ('||l_select_clause || l_from_clause ||l_notif_where_clause||
276 l_more_where_clause ||
277 l_status_where || l_type_where ||')';
278 l_sql := l_sql || l_orderby;
279 x_custom_sql := l_sql;
280
281 l_custom_rec := BIS_PMV_PARAMETERS_PUB.Initialize_Query_Type;
282 x_custom_attr := BIS_QUERY_ATTRIBUTES_TBL();
283
284 l_bind_ctr := 1;
285 l_custom_rec.attribute_name := ':msg_type';
286 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
287 l_custom_rec.attribute_value := l_msg_type;
288 l_custom_rec.attribute_data_type :=BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
289 x_custom_attr.Extend();
290 x_custom_attr(l_bind_ctr):=l_custom_rec;
291 l_bind_ctr:=l_bind_ctr+1;
292
293 IF l_code_table IS NOT NULL AND l_code_table.COUNT >0 THEN
294 FOR i IN l_code_table.FIRST..l_code_table.LAST LOOP
295 l_custom_rec.attribute_name :=':l_appcode'||i;
296 l_custom_rec.attribute_value := l_code_table(i);
297 l_custom_rec.attribute_data_type :=BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
298 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
299 x_custom_attr.Extend();
300 x_custom_attr(l_bind_ctr):=l_custom_rec;
301 l_bind_ctr:=l_bind_ctr+1;
302 END LOOP;
303 END IF;
304
305 END APPROVALS_DETAIL_SQL;
306
307
308 END BIS_PMV_APPROVALS_PVT;