[Home] [Help]
PACKAGE BODY: APPS.JTM_HOOK_UTIL_PKG
Source
1 PACKAGE BODY JTM_HOOK_UTIL_PKG AS
2 /* $Header: jtmhutlb.pls 120.3 2006/01/13 03:21:17 trajasek noship $ */
3
4 /*** Globals ***/
5 g_resource_id NUMBER; -- variable used for caching current resource_id
6 g_debug_level NUMBER; -- variable containing debug level
7
8 /*** For return of profile values ***/
9 G_SITE_LEVEL_ID CONSTANT NUMBER := 10001;
10 G_APPL_LEVEL_ID CONSTANT NUMBER := 10002;
11 G_RESP_LEVEL_ID CONSTANT NUMBER := 10003;
12 G_USER_LEVEL_ID CONSTANT NUMBER := 10004;
13
14 /***
15 Function that returns debug level.
16 0 = No debug
17 1 = Log errors
18 2 = Log errors and functional messages
19 3 = Log errors, functional messages and SQL statements
20 4 = Full Debug
21 ***/
22 FUNCTION Get_Debug_Level
23 RETURN NUMBER
24 IS
25 BEGIN
26 /*** has debug mode already been retrieved ***/
27 IF g_debug_level IS NULL THEN
28 /*** no -> get it from profile ***/
29 g_debug_level := FND_PROFILE.VALUE( 'JTM_DEBUG_LEVEL');
30 END IF;
31 RETURN g_debug_level;
32 END Get_Debug_Level;
33
34 /*** Function to check if this resource is a Mobile Field Service/Laptop user ***/
35 FUNCTION isMobileFSresource
36 ( p_resource_id in NUMBER
37 )
38 RETURN BOOLEAN
39 IS
40 /*** cursor to check if user is mobile user resource ***/
41 /* A user is a mobile FS user when:
42 - Exists in ASG_USER
43 - Has a responsibility that is mapped to the publication 'SERVICEL'
44 */
45 --Bug 4924543
46 CURSOR c_asg_user( b_resource_id NUMBER ) IS
47 SELECT null
48 FROM asg_user au
49 , asg_user_pub_resps aupr
50 WHERE au.user_name = aupr.user_name
51 AND aupr.pub_name = 'SERVICEL'
52 AND au.enabled = 'Y'
53 AND au.resource_id = b_resource_id;
54
55 r_asg_user c_asg_user%ROWTYPE;
56
57 BEGIN
58 OPEN c_asg_user( p_resource_id );
59 FETCH c_asg_user INTO r_asg_user;
60 IF c_asg_user%NOTFOUND THEN
61 /*** resource is not a mobile user -> exit ***/
62 CLOSE c_asg_user;
63 RETURN FALSE;
64 END IF;
65 CLOSE c_asg_user;
66 RETURN TRUE;
67 END isMobileFSresource;
68
69 /***
70 Procedure that returns resource_id for a given client_name.
71 ***/
72 FUNCTION Get_Resource_Id( p_client_name IN VARCHAR2
73 )
74 RETURN NUMBER IS
75 CURSOR c_resource ( b_client_name VARCHAR2) IS
76 SELECT resource_id
77 FROM jtf_rs_resource_extns rre
78 , fnd_user usr
79 WHERE usr.user_id = rre.user_id
80 AND usr.user_name = b_client_name;
81 r_resource c_resource%ROWTYPE;
82 BEGIN
83 OPEN c_resource( p_client_name );
84 FETCH c_resource INTO r_resource;
85 CLOSE c_resource;
86 RETURN r_resource.resource_id;
87 END Get_Resource_Id;
88
89 FUNCTION Get_User_Id( p_client_name IN VARCHAR2
90 )
91 RETURN NUMBER
92 IS
93 CURSOR c_user( b_client_name VARCHAR2 ) IS
94 SELECT user_id
95 FROM fnd_user
96 WHERE user_name = b_client_name;
97 r_user c_user%ROWTYPE;
98 BEGIN
99 OPEN c_user( p_client_name );
100 FETCH c_user INTO r_user;
101 CLOSE c_user;
102 RETURN r_user.user_id;
103 END Get_User_Id;
104
105 /***
106 Procedure that checks if an ACC record exists for a given resource_id.
107 If so, it returns the ACC record's access_id.
108 If not, it returns -1.
109 ***/
110 FUNCTION Get_Acc_Id
111 ( p_acc_table_name in VARCHAR2
112 , p_resource_id in NUMBER
113 , p_pk1_name in VARCHAR2
114 , p_pk1_num_value in NUMBER DEFAULT NULL
115 , p_pk1_char_value in VARCHAR2 DEFAULT NULL
116 , p_pk1_date_value in DATE DEFAULT NULL
117 , p_pk2_name in VARCHAR2 DEFAULT NULL
118 , p_pk2_num_value in NUMBER DEFAULT NULL
119 , p_pk2_char_value in VARCHAR2 DEFAULT NULL
120 , p_pk2_date_value in DATE DEFAULT NULL
121 , p_pk3_name in VARCHAR2 DEFAULT NULL
122 , p_pk3_num_value in NUMBER DEFAULT NULL
123 , p_pk3_char_value in VARCHAR2 DEFAULT NULL
124 , p_pk3_date_value in DATE DEFAULT NULL
125 )
126 RETURN NUMBER
127 IS
128 l_stmt VARCHAR2(2000);
129 l_access_id NUMBER;
130 l_error_msg VARCHAR2(4000);
131 l_merged_pk VARCHAR2(4000);
132 l_pk1_value VARCHAR2(4000);
133 l_pk2_value VARCHAR2(4000) := NULL;
134 l_pk3_value VARCHAR2(4000) := NULL;
135 l_pk1_string VARCHAR2(4000);
136 l_pk2_string VARCHAR2(4000);
137 l_pk3_string VARCHAR2(4000);
138 BEGIN
139
140 IF p_pk1_date_value IS null THEN
141 l_pk1_value := NVL( TO_CHAR(p_pk1_num_value ), p_pk1_char_value );
142 l_pk1_string:= ':2';
143 ELSE
144 l_pk1_value := to_char((p_pk1_date_value),'j');
145 l_pk1_string:= 'to_date(:2,''j'')';
146 END IF;
147 /* Create Execute statement and log strings */
148 l_stmt := 'SELECT ACCESS_ID FROM ' || p_acc_table_name ||
149 ' WHERE RESOURCE_ID = :1' ||
150 ' AND ' || p_pk1_name || ' = ' || l_pk1_string;
151 l_error_msg := ' :2 = ' || l_pk1_value;
152 l_merged_pk := l_pk1_value;
153 IF p_pk2_name IS NOT NULL THEN
154 IF p_pk2_date_VALUE IS null THEN
155 l_pk2_value := NVL( TO_CHAR(p_pk2_num_value ), p_pk2_char_value );
156 l_pk2_string:= ':4';
157 ELSE
158 l_pk2_value := to_char((p_pk2_date_value),'j');
159 l_pk2_string:= 'to_date(:4,''j'')';
160 END IF;
161 /* Create Execute statement and log strings */
162 l_stmt := l_stmt || ' AND ' || p_pk2_name || ' = ' || l_pk2_string;
163 if ( Length(l_error_msg || fnd_global.local_chr(10)
164 || ' :4 = ' || l_pk2_value) < 4000) then
165 l_error_msg := l_error_msg || fnd_global.local_chr(10)
166 || ' :4 = ' || l_pk2_value;
167 elsif (Length(l_error_msg || ' ...') < 4000) then
168 l_error_msg := l_error_msg || ' ...';
169 end if;
170
171 if ( Length(l_merged_pk || ' , ' || l_pk2_value) < 4000 ) then
172 l_merged_pk := l_merged_pk || ' , ' || l_pk2_value;
173 elsif ( Length(l_merged_pk || ' ...') < 4000 ) then
174 l_merged_pk := l_merged_pk || ' ...';
175 end if;
176 IF p_pk3_name IS NOT null THEN
177 /* There are three PK's */
178 IF p_pk3_date_value IS null THEN
179 l_pk3_value := NVL( TO_CHAR(p_pk3_num_value ), p_pk3_char_value );
180 l_pk3_string:= ':5';
181 ELSE
182 l_pk3_value := to_char((p_pk3_date_value),'j');
183 l_pk3_string:= 'to_date(:5,''j'')';
184 END IF;
185 /* Create Execute statement and log strings */
186 l_stmt := l_stmt || ' AND ' || p_pk3_name || ' = ' || l_pk3_string;
187 if ( Length(l_error_msg || fnd_global.local_chr(10)
188 || ' :5 = ' || l_pk3_value) < 4000) then
189 l_error_msg := l_error_msg || fnd_global.local_chr(10)
190 || ' :5 = ' || l_pk3_value;
191 elsif (Length(l_error_msg || ' ...') < 4000) then
192 l_error_msg := l_error_msg || ' ...';
193 end if;
194 if ( Length(l_merged_pk || ' , ' || l_pk3_value) < 4000 ) then
195 l_merged_pk := l_merged_pk || ' , ' || l_pk3_value;
196 elsif ( Length(l_merged_pk || ' ...') < 4000 ) then
197 l_merged_pk := l_merged_pk || ' ...';
198 end if;
199 END IF;
200 END IF;
201
202 IF Get_Debug_Level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_SQL THEN
203 jtm_message_log_pkg.Log_Msg
204 ( l_merged_pk
205 , p_acc_table_name
206 , 'JTM_HOOK_UTIL_PKG.Get_Acc_Id executing:' || fnd_global.local_chr(10) ||
207 l_stmt || fnd_global.local_chr(10) ||
208 ':1 = ' || p_resource_id || fnd_global.local_chr(10) || l_error_msg
209 , JTM_HOOK_UTIL_PKG.g_debug_level_full
210 , 'jtm_message_log_pkg');
211 END IF;
212
213 IF p_pk2_name IS NULL THEN
214 EXECUTE IMMEDIATE l_stmt INTO l_access_id USING p_resource_id, l_pk1_value;
215 ELSIF p_pk3_name IS NULL then
216 EXECUTE IMMEDIATE l_stmt INTO l_access_id USING p_resource_id, l_pk1_value, l_pk2_value;
217 ELSE
218 EXECUTE IMMEDIATE l_stmt INTO l_access_id USING p_resource_id, l_pk1_value, l_pk2_value, l_pk3_value;
219 END IF;
220 /*** record exists -> return access code ***/
221 RETURN l_access_id;
222
223 EXCEPTION
224 WHEN NO_DATA_FOUND THEN
225 /*** Record doesn't exist ***/
226 RETURN -1;
227 WHEN OTHERS THEN
228 /*** Raise any other error ***/
229 RAISE;
230 END Get_Acc_Id;
231
232 /***
233 Procedure that returns all RESOURCE_ID, ACCESS_ID combinations present in ACC for a given
234 table_name, primary key name and primary key value
235 ***/
236 PROCEDURE Get_Resource_Acc_List
237 ( p_acc_table_name in VARCHAR2
238 , p_pk1_name in VARCHAR2
239 , p_pk1_num_value in NUMBER DEFAULT NULL
240 , p_pk1_char_value in VARCHAR2 DEFAULT NULL
241 , p_pk1_date_value in DATE DEFAULT NULL
242 , p_pk2_name in VARCHAR2 DEFAULT NULL
243 , p_pk2_num_value in NUMBER DEFAULT NULL
244 , p_pk2_char_value in VARCHAR2 DEFAULT NULL
245 , p_pk2_date_value in DATE DEFAULT NULL
246 , p_pk3_name in VARCHAR2 DEFAULT NULL
247 , p_pk3_num_value in NUMBER DEFAULT NULL
248 , p_pk3_char_value in VARCHAR2 DEFAULT NULL
249 , p_pk3_date_value in DATE DEFAULT NULL
250 , l_tab_resource_id out NOCOPY dbms_sql.Number_Table
251 , l_tab_access_id out NOCOPY dbms_sql.Number_Table
252 )
253 IS
254 l_stmt VARCHAR2(2000);
255 l_cursor INTEGER;
256 l_count INTEGER;
257 l_index NUMBER;
258 l_error_msg VARCHAR2(4000);
259 l_merged_pk VARCHAR2(4000);
260 l_pk1_value VARCHAR2(4000);
261 l_pk2_value VARCHAR2(4000) := NULL;
262 l_pk3_value VARCHAR2(4000) := NULL;
263 l_pk1_string VARCHAR2(4000);
264 l_pk2_string VARCHAR2(4000);
265 l_pk3_string VARCHAR2(4000);
266 BEGIN
267 IF p_pk1_date_value IS null THEN
268 l_pk1_value := NVL( TO_CHAR(p_pk1_num_value ), p_pk1_char_value );
269 l_pk1_string:= ':P1';
270 ELSE
271 l_pk1_value := to_char((p_pk1_date_value),'j');
272 l_pk1_string:= 'to_date(:P1,''j'')';
273 END IF;
274 /* Create Execute statement and log strings */
275 l_stmt := 'SELECT RESOURCE_ID, ACCESS_ID FROM ' || p_acc_table_name ||
276 ' WHERE ' || p_pk1_name || ' = ' || l_pk1_string;
277 l_error_msg := ' :P1 = ' || l_pk1_value;
278 l_merged_pk := l_pk1_value;
279 IF p_pk2_name IS NOT NULL THEN
280 IF p_pk2_date_VALUE IS null THEN
281 l_pk2_value := NVL( TO_CHAR(p_pk2_num_value ), p_pk2_char_value );
282 l_pk2_string:= ':P2';
283 ELSE
284 l_pk2_value := to_char((p_pk2_date_value),'j');
285 l_pk2_string:= 'to_date(:P2,''j'')';
286 END IF;
287 /* Create Execute statement and log strings */
288 l_stmt := l_stmt || ' AND ' || p_pk2_name || ' = ' || l_pk2_string;
289 if ( Length(l_error_msg || fnd_global.local_chr(10)
290 || ' :P2 = ' || l_pk2_value) < 4000) then
291 l_error_msg := l_error_msg || fnd_global.local_chr(10)
292 || ' :P2 = ' || l_pk2_value;
293 elsif (Length(l_error_msg || ' ...') < 4000) then
294 l_error_msg := l_error_msg || ' ...';
295 end if;
296
297 if ( Length(l_merged_pk || ' , ' || l_pk2_value) < 4000 ) then
298 l_merged_pk := l_merged_pk || ' , ' || l_pk2_value;
299 elsif ( Length(l_merged_pk || ' ...') < 4000 ) then
300 l_merged_pk := l_merged_pk || ' ...';
301 end if;
302 IF p_pk3_name IS NOT null THEN
303 /* There are three PK's */
304 IF p_pk3_date_value IS null THEN
305 l_pk3_value := NVL( TO_CHAR(p_pk3_num_value ), p_pk3_char_value );
306 l_pk3_string:= ':P3';
307 ELSE
308 l_pk3_value := to_char((p_pk3_date_value),'j');
309 l_pk3_string:= 'to_date(:P3,''j'')';
310 END IF;
311 /* Create Execute statement and log strings */
312 l_stmt := l_stmt || ' AND ' || p_pk3_name || ' = ' || l_pk3_string;
313 if ( Length(l_error_msg || fnd_global.local_chr(10)
314 || ' :P3 = ' || l_pk3_value) < 4000) then
315 l_error_msg := l_error_msg || fnd_global.local_chr(10)
316 || ' :P3 = ' || l_pk3_value;
317 elsif (Length(l_error_msg || ' ...') < 4000) then
318 l_error_msg := l_error_msg || ' ...';
319 end if;
320 if ( Length(l_merged_pk || ' , ' || l_pk3_value) < 4000 ) then
321 l_merged_pk := l_merged_pk || ' , ' || l_pk3_value;
322 elsif ( Length(l_merged_pk || ' ...') < 4000 ) then
323 l_merged_pk := l_merged_pk || ' ...';
324 end if;
325 END IF;
326 END IF;
327
328 IF Get_Debug_Level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_SQL THEN
329 jtm_message_log_pkg.Log_Msg
330 ( l_merged_pk
331 , p_acc_table_name
332 , 'JTM_HOOK_UTIL_PKG.Get_Resource_Acc_List executing:' || fnd_global.local_chr(10) ||
333 l_stmt || fnd_global.local_chr(10) || l_error_msg
334 , JTM_HOOK_UTIL_PKG.g_debug_level_full
335 , 'jtm_message_log_pkg');
336 END IF;
337
338 l_cursor := dbms_sql.open_cursor;
339 dbms_sql.parse( l_cursor, l_stmt, dbms_sql.v7);
340 dbms_sql.bind_variable( l_cursor, 'P1', l_pk1_value);
341 IF p_pk2_name IS NOT NULL THEN
342 dbms_sql.bind_variable( l_cursor, 'P2', l_pk2_value );
343 IF p_pk3_name IS NOT NULL THEN
344 dbms_sql.bind_variable( l_cursor, 'P3', l_pk3_value );
345 END IF;
346 END IF;
347 l_index := 1;
348 dbms_sql.define_array( l_cursor, 1, l_tab_resource_id, 10, l_index);
349 dbms_sql.define_array( l_cursor, 2, l_tab_access_id, 10, l_index);
350 l_count := dbms_sql.execute( l_cursor );
351 LOOP
352 l_count := dbms_sql.fetch_rows(l_cursor);
353
354 dbms_sql.column_value( l_cursor, '1', l_tab_resource_id);
355 dbms_sql.column_value( l_cursor, '2', l_tab_access_id);
356
357 EXIT WHEN l_count <> 10;
358 END LOOP;
359 dbms_sql.close_cursor( l_cursor );
360 END Get_Resource_Acc_List;
361
362 /***
363 Procedure that inserts a record into any ACC table
364 ***/
365 PROCEDURE INSERT_ACC
366 ( p_publication_item_names in t_publication_item_list
367 , p_acc_table_name in VARCHAR2
368 , p_resource_id in NUMBER
369 , p_pk1_name in VARCHAR2
370 , p_pk1_num_value in NUMBER DEFAULT NULL
371 , p_pk1_char_value in VARCHAR2 DEFAULT NULL
372 , p_pk1_date_value in DATE DEFAULT NULL
373 , p_pk2_name in VARCHAR2 DEFAULT NULL
374 , p_pk2_num_value in NUMBER DEFAULT NULL
375 , p_pk2_char_value in VARCHAR2 DEFAULT NULL
376 , p_pk2_date_value in DATE DEFAULT NULL
377 , p_pk3_name in VARCHAR2 DEFAULT NULL
378 , p_pk3_num_value in NUMBER DEFAULT NULL
379 , p_pk3_char_value in VARCHAR2 DEFAULT NULL
380 , p_pk3_date_value in DATE DEFAULT NULL
381 )
382 IS
383 l_stmt VARCHAR2(2000);
384 l_access_id NUMBER;
385 l_error_msg VARCHAR2(4000);
386 l_merged_pk VARCHAR2(4000);
387 l_pk1_value VARCHAR2(4000);
388 l_pk2_value VARCHAR2(4000) := NULL;
389 l_pk3_value VARCHAR2(4000) := NULL;
390 l_pk1_string VARCHAR2(4000);
391 l_pk2_string VARCHAR2(4000);
392 l_pk3_string VARCHAR2(4000);
393 l_rc BOOLEAN;
394 BEGIN
395 /*** insert new ACC record for current resource ***/
396 l_access_id := Get_Acc_Id
400 , p_pk1_num_value => p_pk1_num_value
397 ( p_acc_table_name => p_acc_table_name
398 , p_resource_id => p_resource_id
399 , p_pk1_name => p_pk1_name
401 , p_pk1_char_value => p_pk1_char_value
402 , p_pk1_date_value => p_pk1_date_value
403 , p_pk2_name => p_pk2_name
404 , p_pk2_num_value => p_pk2_num_value
405 , p_pk2_char_value => p_pk2_char_value
406 , p_pk2_date_value => p_pk2_date_value
407 , p_pk3_name => p_pk2_name
408 , p_pk3_num_value => p_pk2_num_value
409 , p_pk3_char_value => p_pk2_char_value
410 , p_pk3_date_value => p_pk2_date_value);
411
412 IF l_access_id <> -1 THEN
413 /*Record already exists for this user, increasing the counter*/
414 l_stmt := 'UPDATE '||p_acc_table_name||
415 ' SET COUNTER = COUNTER + 1'||
416 ', LAST_UPDATE_DATE = SYSDATE '||
417 ', LAST_UPDATED_BY = 1 '||
418 ' WHERE ACCESS_ID = :1 ';
419
420 IF Get_Debug_Level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_SQL THEN
421 jtm_message_log_pkg.Log_Msg
422 ( l_access_id
423 , p_acc_table_name
424 , 'JTM_HOOK_UTIL_PKG.Insert_Acc executing:' || fnd_global.local_chr(10) || l_stmt
425 , JTM_HOOK_UTIL_PKG.g_debug_level_full
426 , 'jtm_message_log_pkg');
427 END IF;
428 EXECUTE IMMEDIATE l_stmt using l_access_id;
429
430 ELSE
431 /*Record does not exists so do the insert*/
432 /* Check how many PK there are and transfer values */
433 IF p_pk1_date_value IS null THEN
434 l_pk1_value := NVL( TO_CHAR(p_pk1_num_value ), p_pk1_char_value );
435 l_pk1_string:= ':2';
436 ELSE
437 l_pk1_value := to_char((p_pk1_date_value),'j');
438 l_pk1_string:= 'to_date(:2,''j'')';
439 END IF;
440 /* Create Execute statement and log strings */
441 l_stmt := 'INSERT INTO ' || p_acc_table_name || ' (ACCESS_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY,'||
442 ' CREATION_DATE, CREATED_BY, COUNTER, RESOURCE_ID, ' || p_pk1_name || ') ' ||
443 'VALUES (JTM_ACC_TABLE_S.NEXTVAL, SYSDATE, 1, SYSDATE, 1, 1, :1, ' || l_pk1_string ||
444 ') RETURNING ACCESS_ID INTO :3';
445 l_error_msg := ' :2 = ' || l_pk1_value;
446 l_merged_pk := l_pk1_value;
447 IF p_pk2_name IS NOT NULL THEN
448 IF p_pk2_date_VALUE IS null THEN
449 l_pk2_value := NVL( TO_CHAR(p_pk2_num_value ), p_pk2_char_value );
450 l_pk2_string:= ':4';
451 ELSE
452 l_pk2_value := to_char((p_pk2_date_value),'j');
453 l_pk2_string:= 'to_date(:4,''j'')';
454 END IF;
455 /* Create Execute statement and log strings */
456 l_stmt := 'INSERT INTO ' || p_acc_table_name || ' (ACCESS_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY,'||
457 ' CREATION_DATE, CREATED_BY, COUNTER, RESOURCE_ID, ' ||
458 p_pk1_name ||', '|| p_pk2_name || ') ' ||
459 'VALUES (JTM_ACC_TABLE_S.NEXTVAL, SYSDATE, 1, SYSDATE, 1, 1, :1, ' ||
460 l_pk1_string || ', ' || l_pk2_string || ' ) RETURNING '|| 'ACCESS_ID INTO :3';
461 if ( Length(l_error_msg || fnd_global.local_chr(10)
462 || ' :4 = ' || l_pk2_value) < 4000) then
463 l_error_msg := l_error_msg || fnd_global.local_chr(10)
464 || ' :4 = ' || l_pk2_value;
465 elsif (Length(l_error_msg || ' ...') < 4000) then
466 l_error_msg := l_error_msg || ' ...';
467 end if;
468 if ( Length(l_merged_pk || ' , ' || l_pk2_value) < 4000 ) then
469 l_merged_pk := l_merged_pk || ' , ' || l_pk2_value;
470 elsif ( Length(l_merged_pk || ' ...') < 4000 ) then
471 l_merged_pk := l_merged_pk || ' ...';
472 end if;
473 IF p_pk3_name IS NOT null THEN
474 /* There are three PK's */
475 IF p_pk3_date_value IS null THEN
476 l_pk3_value := NVL( TO_CHAR(p_pk3_num_value ), p_pk3_char_value );
477 l_pk3_string:= ':5';
478 ELSE
479 l_pk3_value := to_char((p_pk3_date_value),'j');
480 l_pk3_string:= 'to_date(:5,''j'')';
481 END IF;
482 /* Create Execute statement and log strings */
483 l_stmt := 'INSERT INTO ' || p_acc_table_name || ' (ACCESS_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY,'||
484 ' CREATION_DATE, CREATED_BY, COUNTER, RESOURCE_ID, ' ||
485 p_pk1_name ||', '|| p_pk2_name ||', '|| p_pk3_name || ') ' ||
486 'VALUES (JTM_ACC_TABLE_S.NEXTVAL, SYSDATE, 1, SYSDATE, 1, 1, :1, ' ||
487 l_pk1_string || ', ' || l_pk2_string || ', ' || l_pk3_string || ' ) RETURNING '||
488 'ACCESS_ID INTO :3';
489 if ( Length(l_error_msg || fnd_global.local_chr(10)
490 || ' :5 = ' || l_pk3_value) < 4000) then
491 l_error_msg := l_error_msg || fnd_global.local_chr(10)
492 || ' :5 = ' || l_pk3_value;
493 elsif (Length(l_error_msg || ' ...') < 4000) then
494 l_error_msg := l_error_msg || ' ...';
495 end if;
496
497 if ( Length(l_merged_pk || ' , ' || l_pk3_value) < 4000 ) then
498 l_merged_pk := l_merged_pk || ' , ' || l_pk3_value;
499 elsif ( Length(l_merged_pk || ' ...') < 4000 ) then
500 l_merged_pk := l_merged_pk || ' ...';
501 end if;
502 END IF;
503 END IF;
504
505 IF Get_Debug_Level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_SQL THEN
506 jtm_message_log_pkg.Log_Msg
507 ( l_merged_pk
508 , p_acc_table_name
509 , 'JTM_HOOK_UTIL_PKG.Insert_Acc executing:' || fnd_global.local_chr(10) ||
510 l_stmt || fnd_global.local_chr(10) || ':1 = ' || p_resource_id
511 || fnd_global.local_chr(10) || l_error_msg
515
512 , JTM_HOOK_UTIL_PKG.g_debug_level_full
513 , 'jtm_message_log_pkg');
514 END IF;
516 /*We have at least one PK so get the value*/
517 IF l_pk2_value IS NULL THEN
518 /* Only one PK available */
519 EXECUTE IMMEDIATE l_stmt USING p_resource_id, l_pk1_value RETURNING INTO l_access_id;
520 ELSIF l_pk3_value IS NULL THEN
521 /*Two PK's */
522 EXECUTE IMMEDIATE l_stmt USING p_resource_id, l_pk1_value, l_pk2_value RETURNING INTO l_access_id;
523 ELSE
524 /* Three PK's */
525 EXECUTE IMMEDIATE l_stmt USING p_resource_id, l_pk1_value, l_pk2_value, l_pk3_value
526 RETURNING INTO l_access_id;
527 END IF;
528 -- insert record in outqueue ASG call here *****************************************
529 FOR i IN 1 .. p_publication_item_names.LAST LOOP
530 l_rc := asg_download.markDirty(p_publication_item_names(i), l_access_id, p_resource_id, 'I', sysdate );
531 END LOOP;
532 END IF;
533 END Insert_Acc;
534
535 /*** Procedure that re-sends a record with given acc_id to the mobile ***/
536 PROCEDURE Update_Acc
537 ( p_publication_item_names in t_publication_item_list
538 ,p_acc_table_name in VARCHAR2
539 ,p_resource_id in NUMBER
540 ,p_access_id in NUMBER
541 )
542 IS
543 l_rc BOOLEAN;
544 BEGIN
545 --call update outqueue ASG call *****************************************
546 FOR i IN 1 .. p_publication_item_names.LAST LOOP
547 l_rc := asg_download.markDirty(p_publication_item_names(i), p_access_id, p_resource_id, 'U', sysdate );
548 END LOOP;
549 END Update_Acc;
550
551 /***
552 Procedure that deletes record(s) from any ACC table
553 If p_resource_id is NULL, all ACC records that match the PK values are deleted.
554 If p_resource_id is specified and p_operator='=' the ACC record is only deleted for that specific resource.
555 If p_resource_id is specified and p_operator='<>' all ACC records with resource_id<>p_resource_id are deleted
556 ***/
557 PROCEDURE Delete_Acc
558 ( p_publication_item_names in t_publication_item_list
559 ,p_acc_table_name in VARCHAR2
560 ,p_pk1_name in VARCHAR2
561 ,p_pk1_num_value in NUMBER DEFAULT NULL
562 ,p_pk1_char_value in VARCHAR2 DEFAULT NULL
563 , p_pk1_date_value in DATE DEFAULT NULL
564 , p_pk2_name in VARCHAR2 DEFAULT NULL
565 , p_pk2_num_value in NUMBER DEFAULT NULL
566 , p_pk2_char_value in VARCHAR2 DEFAULT NULL
567 , p_pk2_date_value in DATE DEFAULT NULL
568 , p_pk3_name in VARCHAR2 DEFAULT NULL
569 , p_pk3_num_value in NUMBER DEFAULT NULL
570 , p_pk3_char_value in VARCHAR2 DEFAULT NULL
571 , p_pk3_date_value in DATE DEFAULT NULL
572 ,p_resource_id in NUMBER DEFAULT NULL
573 ,p_operator in VARCHAR2 DEFAULT NULL
574 )
575 IS
576 l_stmt VARCHAR2(4000);
577 l_cursor INTEGER;
578 l_count INTEGER;
579 l_tab_mobile_user_id dbms_sql.Number_Table;
580 l_tab_access_id dbms_sql.Number_Table;
581 l_pk1_value VARCHAR2(4000);
582 l_pk2_value VARCHAR2(4000);
583 l_pk3_value VARCHAR2(4000);
584 l_pk1_string VARCHAR2(4000);
585 l_pk2_string VARCHAR2(4000);
586 l_pk3_string VARCHAR2(4000);
587 l_pk_err_msg_txt VARCHAR2(4000);
588 l_count_value NUMBER;
589 l_rc BOOLEAN;
590 l_index NUMBER;
591 l_operator VARCHAR2(20);
592 BEGIN
593 IF p_operator is null THEN
594 l_operator := '=';
595 ELSE
596 l_operator := p_operator;
597 END IF;
598
599 IF p_pk1_date_value IS null THEN
600 l_pk1_value := NVL( TO_CHAR(p_pk1_num_value ), p_pk1_char_value );
601 l_pk1_string:= ':P1';
602 ELSE
603 l_pk1_value := to_char((p_pk1_date_value),'j');
604 l_pk1_string:= 'to_date(:P1,''j'')';
605 END IF;
606 IF p_pk2_name IS NOT NULL THEN
607 IF p_pk2_date_value IS null THEN
608 l_pk2_value := NVL( TO_CHAR(p_pk2_num_value ), p_pk2_char_value );
609 l_pk2_string:= ':P2';
610 ELSE
611 l_pk2_value := to_char((p_pk2_date_value),'j');
612 l_pk2_string:= 'to_date(:P2,''j'')';
613 END IF;
614 IF p_pk3_name IS NOT NULL THEN
615 /* There are three PK's */
616 IF p_pk3_date_value IS null THEN
617 l_pk3_value := NVL( TO_CHAR(p_pk3_num_value ), p_pk3_char_value );
618 l_pk3_string:= ':P3';
619 ELSE
620 l_pk3_value := to_char((p_pk3_date_value),'j');
621 l_pk3_string:= 'to_date(:P3,''j'')';
622 END IF;
623 END IF;
624 END IF;
625
626 /*** At least 1 PK ***/
627 l_stmt := 'SELECT RESOURCE_ID, ACCESS_ID FROM ' || p_acc_table_name ||
628 ' WHERE COUNTER = 1 AND ' || p_pk1_name || ' = ' || l_pk1_string;
629 IF p_pk2_name IS NOT NULL THEN
630 /*** 2 PK's available ***/
631 l_stmt := l_stmt ||' AND ' || p_pk2_name || ' = ' || l_pk2_string;
632 IF p_pk3_name IS NOT NULL THEN
633 /*** 3 PK's available ***/
634 l_stmt := l_stmt ||' AND ' || p_pk3_name || ' = ' || l_pk3_string;
635 END IF;
636 END IF;
637
638 l_cursor := dbms_sql.open_cursor;
639
640 /*** was resource_id provided? ***/
641 IF p_resource_id IS NOT NULL THEN
642 /*** yes -> add p_operator filter on mobile_user_id to WHERE clause ***/
643 l_stmt := l_stmt || ' AND RESOURCE_ID ' || l_operator || ' :P4 ';
644 dbms_sql.parse( l_cursor, l_stmt, dbms_sql.v7);
645 dbms_sql.bind_variable( l_cursor, 'P4', p_resource_id );
646 ELSE
647 /*** no -> delete all ACC records ***/
651 dbms_sql.bind_variable( l_cursor, 'P1', l_pk1_value );
648 dbms_sql.parse( l_cursor, l_stmt, dbms_sql.v7);
649 END IF;
650
652 IF p_pk2_name IS NOT NULL THEN
653 dbms_sql.bind_variable( l_cursor, 'P2', l_pk2_value );
654 IF p_pk3_name IS NOT NULL THEN
655 dbms_sql.bind_variable( l_cursor, 'P3', l_pk3_value );
656 END IF;
657 END IF;
658
659 l_index := 1;
660 dbms_sql.define_array( l_cursor, 1, l_tab_mobile_user_id, 10, l_index);
661 dbms_sql.define_array( l_cursor, 2, l_tab_access_id, 10, l_index);
662
663
664 IF Get_Debug_Level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_SQL THEN
665 jtm_message_log_pkg.Log_Msg
666 ( l_pk1_value||','||l_pk2_value||','||l_pk3_value
667 , p_acc_table_name
668 , 'JTM_HOOK_UTIL_PKG.Delete_Acc executing:' || fnd_global.local_chr(10) ||
669 l_stmt || fnd_global.local_chr(10) ||
670 ':P1 = ' || l_pk1_value || fnd_global.local_chr(10) ||
671 ':P2 = ' || l_pk2_value || fnd_global.local_chr(10) ||
672 ':P3 = ' || l_pk3_value || fnd_global.local_chr(10) ||
673 ':P4 = ' || p_resource_id
674 , JTM_HOOK_UTIL_PKG.g_debug_level_full
675 , 'jtm_message_log_pkg');
676 END IF;
677
678 l_count := dbms_sql.execute( l_cursor );
679 LOOP
680 l_count := dbms_sql.fetch_rows(l_cursor);
681
682 dbms_sql.column_value( l_cursor, '1', l_tab_mobile_user_id);
683 dbms_sql.column_value( l_cursor, '2', l_tab_access_id);
684
685 EXIT WHEN l_count <> 10;
686 END LOOP;
687 dbms_sql.close_cursor( l_cursor );
688
689 /*** were any records deleted? ***/
690 IF l_tab_mobile_user_id.COUNT > 0 THEN
691 /*** yes -> loop over arrays containing mobile_user_id and access_id and notify oLite ***/
692 FOR i IN l_tab_mobile_user_id.FIRST .. l_tab_mobile_user_id.LAST LOOP
693 -- notify oLite of deletion ***
694 FOR j IN 1 .. p_publication_item_names.LAST LOOP
695 l_rc := asg_download.markDirty( p_publication_item_names(j), l_tab_access_id(i)
696 , l_tab_mobile_user_id(i), 'D', sysdate );
697 END LOOP;
698 END LOOP;
699 END IF;
700
701 /*Perform the actual delete*/
702 l_stmt := 'DELETE '||p_acc_table_name||
703 ' WHERE COUNTER = 1'||
704 ' AND '||p_pk1_name||' = ' || l_pk1_string;
705 IF p_pk2_name IS NOT NULL THEN
706 l_stmt := l_stmt ||' AND '||p_pk2_name|| ' = ' || l_pk2_string;
707 IF p_pk3_name IS NOT NULL THEN
708 l_stmt := l_stmt ||' AND '||p_pk3_name|| ' = ' || l_pk3_string;
709 END IF;
710 END IF;
711 IF p_resource_id IS NOT NULL THEN
712 l_stmt := l_stmt ||' AND RESOURCE_ID '||l_operator||' :P4';
713 END IF;
714
715 IF Get_Debug_Level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_SQL THEN
716 jtm_message_log_pkg.Log_Msg
717 ( l_pk1_value||','||l_pk2_value||','||l_pk3_value
718 , p_acc_table_name
719 , 'JTM_HOOK_UTIL_PKG.Delete_Acc executing :' || fnd_global.local_chr(10) ||
720 l_stmt || fnd_global.local_chr(10) ||
721 ':P1 = ' || l_pk1_value || fnd_global.local_chr(10) ||
722 ':P2 = ' || l_pk2_value || fnd_global.local_chr(10) ||
723 ':P3 = ' || l_pk3_value || fnd_global.local_chr(10) ||
724 ':P4 = ' || p_resource_id
725 , JTM_HOOK_UTIL_PKG.g_debug_level_full
726 , 'jtm_message_log_pkg');
727 END IF;
728
729 IF p_pk2_name IS NULL AND p_pk3_name IS NULL AND p_resource_id IS NULL THEN
730 EXECUTE IMMEDIATE l_stmt USING l_pk1_value;
731 ELSIF p_resource_id IS NULL AND p_pk3_name IS NULL AND p_pk2_name IS NOT NULL THEN
732 EXECUTE IMMEDIATE l_stmt USING l_pk1_value, l_pk2_value;
733 ELSIF p_resource_id IS NULL AND p_pk3_name IS NOT NULL AND p_pk2_name IS NOT NULL THEN
734 EXECUTE IMMEDIATE l_stmt USING l_pk1_value, l_pk2_value, l_pk3_value;
735 ELSIF p_resource_id IS NOT NULL AND p_pk2_name IS NULL AND p_pk3_name IS NULL THEN
736 EXECUTE IMMEDIATE l_stmt USING l_pk1_value, p_resource_id;
737 ELSIF p_resource_id IS NOT NULL AND p_pk2_name IS NOT NULL AND p_pk3_name IS NULL THEN
738 EXECUTE IMMEDIATE l_stmt USING l_pk1_value, l_pk2_value, p_resource_id;
739 ELSE
740 EXECUTE IMMEDIATE l_stmt USING l_pk1_value, l_pk2_value, l_pk3_value, p_resource_id;
741 END IF;
742
743 /* Now we have deleted all records we have to decrease the counter of the remaining records*/
744 l_stmt := 'UPDATE '||p_acc_table_name||
745 ' SET COUNTER = COUNTER - 1'||
746 ', LAST_UPDATE_DATE = SYSDATE'||
747 ', LAST_UPDATED_BY = 1'||
748 ' WHERE COUNTER >= 2 AND '||p_pk1_name||' = ' || l_pk1_string;
749 IF p_pk2_name IS NOT NULL THEN
750 l_stmt := l_stmt ||' AND '||p_pk2_name|| ' = ' || l_pk2_string;
751 IF p_pk3_name IS NOT NULL THEN
752 l_stmt := l_stmt ||' AND '||p_pk3_name|| ' = ' || l_pk3_string;
753 END IF;
754 END IF;
755 IF p_resource_id IS NOT NULL THEN
756 l_stmt := l_stmt ||' AND RESOURCE_ID '|| l_operator ||' :P4';
757 END IF;
758
759 IF Get_Debug_Level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_SQL THEN
760 jtm_message_log_pkg.Log_Msg
761 ( l_pk1_value
762 , p_acc_table_name
763 , 'JTM_HOOK_UTIL_PKG.Delete_Acc executing:' || fnd_global.local_chr(10) || l_stmt
764 , JTM_HOOK_UTIL_PKG.g_debug_level_full
765 , 'jtm_message_log_pkg');
766 END IF;
767
768 IF p_pk2_name IS NULL AND p_pk3_name IS NULL AND p_resource_id IS NULL THEN
769 EXECUTE IMMEDIATE l_stmt USING l_pk1_value;
770 ELSIF p_resource_id IS NULL AND p_pk3_name IS NULL AND p_pk2_name IS NOT NULL THEN
771 EXECUTE IMMEDIATE l_stmt USING l_pk1_value, l_pk2_value;
772 ELSIF p_resource_id IS NULL AND p_pk3_name IS NOT NULL AND p_pk2_name IS NOT NULL THEN
773 EXECUTE IMMEDIATE l_stmt USING l_pk1_value, l_pk2_value, l_pk3_value;
777 EXECUTE IMMEDIATE l_stmt USING l_pk1_value, l_pk2_value, p_resource_id;
774 ELSIF p_resource_id IS NOT NULL AND p_pk2_name IS NULL AND p_pk3_name IS NULL THEN
775 EXECUTE IMMEDIATE l_stmt USING l_pk1_value, p_resource_id;
776 ELSIF p_resource_id IS NOT NULL AND p_pk2_name IS NOT NULL AND p_pk3_name IS NULL THEN
778 ELSE
779 EXECUTE IMMEDIATE l_stmt USING l_pk1_value, l_pk2_value, l_pk3_value, p_resource_id;
780 END IF;
781
782 EXCEPTION WHEN OTHERS THEN
783 IF l_cursor <> 0 THEN
784 dbms_sql.close_cursor( l_cursor );
785 END IF;
786 RAISE;
787 END Delete_Acc;
788
789 /**/
790 PROCEDURE DELETE_ACC_FOR_RESOURCE
791 ( p_acc_table_name IN VARCHAR2
792 , p_resource_id IN NUMBER
793 ) IS
794 l_stmt VARCHAR2(1000);
795 BEGIN
796 l_stmt := 'DELETE ' || p_acc_table_name ||' WHERE RESOURCE_ID = :P1';
797
798 IF Get_Debug_Level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_SQL THEN
799 jtm_message_log_pkg.Log_Msg
800 ( p_resource_id
801 , p_acc_table_name
802 , 'JTM_HOOK_UTIL_PKG.Delete_Acc_4Res executing:' || fnd_global.local_chr(10) ||
803 l_stmt || fnd_global.local_chr(10)||'P1 = '||p_resource_id
804 , JTM_HOOK_UTIL_PKG.g_debug_level_full
805 , 'jtm_message_log_pkg');
806 END IF;
807
808 EXECUTE IMMEDIATE l_stmt USING p_resource_id;
809
810 END;
811
812 /*** Better it would be to use th Get specific funtion of fnd_profile ***/
813 FUNCTION Get_Profile_Value( p_name IN VARCHAR2
814 , p_site_id IN NUMBER DEFAULT NULL
815 , p_appl_id IN NUMBER DEFAULT NULL
816 , p_user_id IN NUMBER DEFAULT NULL
817 , p_resp_id IN NUMBER DEFAULT NULL
818 )
819 RETURN VARCHAR2
820 IS
821
822 CURSOR c_profile_option_value ( b_profile_option_name VARCHAR2,
823 b_site_level_value NUMBER,
824 b_appl_level_value NUMBER,
825 b_resp_level_value NUMBER,
826 b_user_level_value NUMBER
827 ) IS
828 SELECT val.profile_option_value
829 FROM fnd_profile_options opt,
830 fnd_profile_option_values val
831 WHERE NVL(opt.start_date_active, SYSDATE) <= SYSDATE
832 AND NVL(opt.end_date_active, SYSDATE) >= SYSDATE
833 AND opt.profile_option_name = b_profile_option_name
834 AND opt.application_id = val.application_id
835 AND opt.profile_option_id = val.profile_option_id
836 AND ( ( val.level_id = G_SITE_LEVEL_ID AND
837 val.level_value = b_site_level_value
838 ) OR
839 ( val.level_id = G_APPL_LEVEL_ID AND
840 val.level_value = b_appl_level_value
841 ) OR
842 ( val.level_id = G_RESP_LEVEL_ID AND
843 val.level_value = b_resp_level_value
844 ) OR
845 ( val.level_id = G_USER_LEVEL_ID AND
846 val.level_value = b_user_level_value
847 )
848 )
849 ORDER BY val.level_id DESC;
850
851
852 r_profile_option_value c_profile_option_value%ROWTYPE;
853
854 BEGIN
855
856 OPEN c_profile_option_value ( p_name,
857 p_site_id,
858 p_appl_id,
859 p_resp_id,
860 p_user_id
861 );
862 FETCH c_profile_option_value INTO r_profile_option_value;
863 CLOSE c_profile_option_value;
864
865 RETURN r_profile_option_value.profile_option_value;
866
867 END Get_Profile_Value;
868
869 END JTM_HOOK_UTIL_PKG;