[Home] [Help]
PACKAGE BODY: APPS.CSM_ACC_PKG
Source
1 PACKAGE BODY CSM_ACC_PKG AS
2 /* $Header: csmeaccb.pls 120.4 2008/04/22 11:45:05 trajasek ship $*/
3 --
4 -- To modify this template, edit file PKGBODY.TXT in TEMPLATE
5 -- directory of SQL Navigator
6 --
7 -- Purpose: Provides generic procedures to manipulate ACC tables, and
8 -- mark dirty records for users, in process
9 --
10 -- MODIFICATION HISTORY
11 -- Person Date Comments
12 -- Anurag 09/16/02 Created
13 -- --------- ------ ------------------------------------------
14 -- Enter procedure, function bodies as shown below
15 CURSOR c_asg_user (c_user_id NUMBER)
16 IS
17 SELECT resource_id
18 FROM ASG_USER
19 WHERE user_id = c_user_id
20 AND Enabled ='Y';
21
22
23 FUNCTION Check_for_owner(p_user_id NUMBER)
24 RETURN NUMBER;
25
26 /***
27 Procedure that checks if an ACC record exists for a given resource_id.
28 If so, it returns the ACC record's access_id.
29 If not, it returns -1.
30 ***/
31 FUNCTION Get_Acc_Id
32 ( p_acc_table_name in VARCHAR2
33 , p_user_id in NUMBER
34 , p_pk1_name in VARCHAR2
35 , p_pk1_num_value in NUMBER DEFAULT NULL
36 , p_pk1_char_value in VARCHAR2 DEFAULT NULL
37 , p_pk1_date_value in DATE DEFAULT NULL
38 , p_pk2_name in VARCHAR2 DEFAULT NULL
39 , p_pk2_num_value in NUMBER DEFAULT NULL
40 , p_pk2_char_value in VARCHAR2 DEFAULT NULL
41 , p_pk2_date_value in DATE DEFAULT NULL
42 , p_pk3_name in VARCHAR2 DEFAULT NULL
43 , p_pk3_num_value in NUMBER DEFAULT NULL
44 , p_pk3_char_value in VARCHAR2 DEFAULT NULL
45 , p_pk3_date_value in DATE DEFAULT NULL
46 )
47 RETURN NUMBER
48 IS
49 l_stmt VARCHAR2(2000);
50 l_access_id NUMBER;
51 l_error_msg VARCHAR2(4000);
52 l_merged_pk VARCHAR2(4000);
53 l_pk1_value VARCHAR2(4000);
54 l_pk2_value VARCHAR2(4000) := NULL;
55 l_pk3_value VARCHAR2(4000) := NULL;
56 l_pk1_string VARCHAR2(4000);
57 l_pk2_string VARCHAR2(4000);
58 l_pk3_string VARCHAR2(4000);
59 BEGIN
60
61 IF p_pk1_date_value IS null THEN
62 l_pk1_value := NVL( TO_CHAR(p_pk1_num_value ), p_pk1_char_value );
63 l_pk1_string:= ':2';
64 ELSE
65 l_pk1_value := to_char((p_pk1_date_value),'j');
66 l_pk1_string:= 'to_date(:2,''j'')';
67 END IF;
68 /* Create Execute statement and log strings */
69 l_stmt := 'SELECT ACCESS_ID FROM ' || p_acc_table_name ||
70 ' WHERE USER_ID = :1' ||
71 ' AND ' || p_pk1_name || ' = ' || l_pk1_string;
72 l_error_msg := ' :2 = ' || l_pk1_value;
73 l_merged_pk := l_pk1_value;
74 IF p_pk2_name IS NOT NULL THEN
75 IF p_pk2_date_VALUE IS null THEN
76 l_pk2_value := NVL( TO_CHAR(p_pk2_num_value ), p_pk2_char_value );
77 l_pk2_string:= ':4';
78 ELSE
79 l_pk2_value := to_char((p_pk2_date_value),'j');
80 l_pk2_string:= 'to_date(:4,''j'')';
81 END IF;
82 /* Create Execute statement and log strings */
83 l_stmt := l_stmt || ' AND ' || p_pk2_name || ' = ' || l_pk2_string;
84 if ( Length(l_error_msg || fnd_global.local_chr(10)
85 || ' :4 = ' || l_pk2_value) < 4000) then
86 l_error_msg := l_error_msg || fnd_global.local_chr(10)
87 || ' :4 = ' || l_pk2_value;
88 elsif (Length(l_error_msg || ' ...') < 4000) then
89 l_error_msg := l_error_msg || ' ...';
90 end if;
91
92 if ( Length(l_merged_pk || ' , ' || l_pk2_value) < 4000 ) then
93 l_merged_pk := l_merged_pk || ' , ' || l_pk2_value;
94 elsif ( Length(l_merged_pk || ' ...') < 4000 ) then
95 l_merged_pk := l_merged_pk || ' ...';
96 end if;
97 IF p_pk3_name IS NOT null THEN
98 /* There are three PK's */
99 IF p_pk3_date_value IS null THEN
100 l_pk3_value := NVL( TO_CHAR(p_pk3_num_value ), p_pk3_char_value );
101 l_pk3_string:= ':5';
102 ELSE
103 l_pk3_value := to_char((p_pk3_date_value),'j');
104 l_pk3_string:= 'to_date(:5,''j'')';
105 END IF;
106 /* Create Execute statement and log strings */
107 l_stmt := l_stmt || ' AND ' || p_pk3_name || ' = ' || l_pk3_string;
108 if ( Length(l_error_msg || fnd_global.local_chr(10)
109 || ' :5 = ' || l_pk3_value) < 4000) then
110 l_error_msg := l_error_msg || fnd_global.local_chr(10)
111 || ' :5 = ' || l_pk3_value;
112 elsif (Length(l_error_msg || ' ...') < 4000) then
113 l_error_msg := l_error_msg || ' ...';
114 end if;
115 if ( Length(l_merged_pk || ' , ' || l_pk3_value) < 4000 ) then
116 l_merged_pk := l_merged_pk || ' , ' || l_pk3_value;
117 elsif ( Length(l_merged_pk || ' ...') < 4000 ) then
118 l_merged_pk := l_merged_pk || ' ...';
119 end if;
120 END IF;
121 END IF;
122
123 CSM_UTIL_PKG.LOG( l_stmt || fnd_global.local_chr(10) ||
124 ':1 = ' || p_user_id || fnd_global.local_chr(10) || l_error_msg,
125 'CSM_ACC_PKG.GET_ACC_ID',FND_LOG.LEVEL_PROCEDURE);
126
127
128 IF p_pk2_name IS NULL THEN
129 EXECUTE IMMEDIATE l_stmt INTO l_access_id USING p_user_id, l_pk1_value;
130 ELSIF p_pk3_name IS NULL then
131 EXECUTE IMMEDIATE l_stmt INTO l_access_id USING p_user_id, l_pk1_value, l_pk2_value;
132 ELSE
133 EXECUTE IMMEDIATE l_stmt INTO l_access_id USING p_user_id, l_pk1_value, l_pk2_value, l_pk3_value;
134 END IF;
135 /*** record exists -> return access code ***/
136 RETURN l_access_id;
137
138 EXCEPTION
139 WHEN NO_DATA_FOUND THEN
140 CSM_UTIL_PKG.LOG( 'No Access_ID found for Statement:' || l_stmt || fnd_global.local_chr(10) ||
141 ':1 = ' || p_user_id, 'CSM_ACC_PKG.GET_ACC_ID',FND_LOG.LEVEL_EXCEPTION);
142 /*** Record doesn't exist ***/
143 RETURN -1;
144 WHEN OTHERS THEN
145 CSM_UTIL_PKG.LOG( 'Exception occurred in CSM_ACC_PKG.GET_ACC_ID' || sqlerrm, 'CSM_ACC_PKG.GET_ACC_ID',FND_LOG.LEVEL_EXCEPTION);
146 /*** Raise any other error ***/
147 RAISE;
148 END Get_Acc_Id;
149
150 /***
151 Procedure that inserts a record into any ACC table.
152 ***/
153 PROCEDURE INSERT_ACC
154 ( p_publication_item_names in t_publication_item_list
155 , p_acc_table_name in VARCHAR2
156 , p_seq_name in VARCHAR2
157 , p_user_id in NUMBER
158 , p_pk1_name in VARCHAR2
159 , p_pk1_num_value in NUMBER DEFAULT NULL
160 , p_pk1_char_value in VARCHAR2 DEFAULT NULL
161 , p_pk1_date_value in DATE DEFAULT NULL
162 , p_pk2_name in VARCHAR2 DEFAULT NULL
163 , p_pk2_num_value in NUMBER DEFAULT NULL
164 , p_pk2_char_value in VARCHAR2 DEFAULT NULL
165 , p_pk2_date_value in DATE DEFAULT NULL
166 , p_pk3_name in VARCHAR2 DEFAULT NULL
167 , p_pk3_num_value in NUMBER DEFAULT NULL
168 , p_pk3_char_value in VARCHAR2 DEFAULT NULL
169 , p_pk3_date_value in DATE DEFAULT NULL
170 )
171 IS
172 l_stmt VARCHAR2(2000);
173 l_access_id NUMBER;
174 l_error_msg VARCHAR2(4000);
175 l_merged_pk VARCHAR2(4000);
176 l_pk1_value VARCHAR2(4000);
177 l_pk2_value VARCHAR2(4000) := NULL;
178 l_pk3_value VARCHAR2(4000) := NULL;
179 l_pk1_string VARCHAR2(4000);
180 l_pk2_string VARCHAR2(4000);
181 l_pk3_string VARCHAR2(4000);
182 l_rc BOOLEAN;
183 l_resource_id jtf_rs_resource_extns.resource_id%TYPE;
184 l_owner_id NUMBER;
185 l_user_id NUMBER;
186 BEGIN
187
188 --Function replaces owner if data routed to owner is set
189 l_user_id := Check_for_owner(p_user_id);
190
191 /*** insert new ACC record for current resource ***/
192 l_access_id := Get_Acc_Id
193 ( p_acc_table_name => p_acc_table_name
194 , p_user_id => l_user_id
195 , p_pk1_name => p_pk1_name
196 , p_pk1_num_value => p_pk1_num_value
197 , p_pk1_char_value => p_pk1_char_value
198 , p_pk1_date_value => p_pk1_date_value
199 , p_pk2_name => p_pk2_name
200 , p_pk2_num_value => p_pk2_num_value
201 , p_pk2_char_value => p_pk2_char_value
202 , p_pk2_date_value => p_pk2_date_value
203 , p_pk3_name => p_pk3_name
204 , p_pk3_num_value => p_pk3_num_value
205 , p_pk3_char_value => p_pk3_char_value
206 , p_pk3_date_value => p_pk3_date_value);
207
208 IF l_access_id <> -1 THEN
209 /*Record already exists for this user, increasing the counter*/
210 l_stmt := 'UPDATE '||p_acc_table_name||
211 ' SET COUNTER = COUNTER + 1'||
212 ', LAST_UPDATE_DATE = SYSDATE '||
213 ', LAST_UPDATED_BY = 1 '||
214 ', LAST_UPDATE_LOGIN = 1' ||
215 ' WHERE ACCESS_ID = :1'; -- ||l_access_id;
216
217
218
219 CSM_UTIL_PKG.LOG( l_stmt, 'CSM_ACC_PKG.INSERT_ACC',FND_LOG.LEVEL_PROCEDURE);
220
221 EXECUTE IMMEDIATE l_stmt using l_access_id;
222
223 ELSE
224 /*Record does not exists so do the insert*/
225 /* Check how many PK there are and transfer values */
226 IF p_pk1_date_value IS null THEN
227 l_pk1_value := NVL( TO_CHAR(p_pk1_num_value ), p_pk1_char_value );
228 l_pk1_string:= ':2';
229 ELSE
230 l_pk1_value := to_char((p_pk1_date_value),'j');
231 l_pk1_string:= 'to_date(:2,''j'')';
232 END IF;
233 /* Create Execute statement and log strings */
234 l_stmt := 'INSERT INTO ' || p_acc_table_name || ' (ACCESS_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY,'||
235 ' CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, COUNTER, USER_ID, ' || p_pk1_name || ') ' ||
236 'VALUES ('
237 || p_seq_name || '.NEXTVAL, SYSDATE, 1, SYSDATE, 1, 1, 1, :1, ' || l_pk1_string ||
238 ') RETURNING ACCESS_ID INTO :3';
239 l_error_msg := ' :2 = ' || l_pk1_value;
240 l_merged_pk := l_pk1_value;
241 IF p_pk2_name IS NOT NULL THEN
242 IF p_pk2_date_VALUE IS null THEN
243 l_pk2_value := NVL( TO_CHAR(p_pk2_num_value ), p_pk2_char_value );
244 l_pk2_string:= ':4';
245 ELSE
246 l_pk2_value := to_char((p_pk2_date_value),'j');
247 l_pk2_string:= 'to_date(:4,''j'')';
248 END IF;
249 /* Create Execute statement and log strings */
250 l_stmt := 'INSERT INTO ' || p_acc_table_name || ' (ACCESS_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY,'||
251 ' CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, COUNTER, USER_ID, ' ||
252 p_pk1_name ||', '|| p_pk2_name || ') ' ||
253 'VALUES ('
254 || p_seq_name || '.NEXTVAL, SYSDATE, 1, SYSDATE, 1, 1, 1, :1, ' ||
255 l_pk1_string || ', ' || l_pk2_string || ' ) RETURNING '|| 'ACCESS_ID INTO :3';
256 if ( Length(l_error_msg || fnd_global.local_chr(10)
257 || ' :4 = ' || l_pk2_value) < 4000) then
258 l_error_msg := l_error_msg || fnd_global.local_chr(10)
259 || ' :4 = ' || l_pk2_value;
260 elsif (Length(l_error_msg || ' ...') < 4000) then
261 l_error_msg := l_error_msg || ' ...';
262 end if;
263 if ( Length(l_merged_pk || ' , ' || l_pk2_value) < 4000 ) then
264 l_merged_pk := l_merged_pk || ' , ' || l_pk2_value;
265 elsif ( Length(l_merged_pk || ' ...') < 4000 ) then
266 l_merged_pk := l_merged_pk || ' ...';
267 end if;
268 IF p_pk3_name IS NOT null THEN
269 /* There are three PK's */
270 IF p_pk3_date_value IS null THEN
271 l_pk3_value := NVL( TO_CHAR(p_pk3_num_value ), p_pk3_char_value );
272 l_pk3_string:= ':5';
273 ELSE
274 l_pk3_value := to_char((p_pk3_date_value),'j');
275 l_pk3_string:= 'to_date(:5,''j'')';
276 END IF;
277 /* Create Execute statement and log strings */
278 l_stmt := 'INSERT INTO ' || p_acc_table_name || ' (ACCESS_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY,'||
279 ' CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, COUNTER, USER_ID, ' ||
280 p_pk1_name ||', '|| p_pk2_name ||', '|| p_pk3_name || ') ' ||
281 'VALUES ('
282 || p_seq_name || '.NEXTVAL, SYSDATE, 1, SYSDATE, 1, 1, 1, :1, ' ||
283 l_pk1_string || ', ' || l_pk2_string || ', ' || l_pk3_string || ' ) RETURNING '||
284 'ACCESS_ID INTO :3';
285 if ( Length(l_error_msg || fnd_global.local_chr(10)
286 || ' :5 = ' || l_pk3_value) < 4000) then
287 l_error_msg := l_error_msg || fnd_global.local_chr(10)
288 || ' :5 = ' || l_pk3_value;
289 elsif (Length(l_error_msg || ' ...') < 4000) then
290 l_error_msg := l_error_msg || ' ...';
291 end if;
292
293 if ( Length(l_merged_pk || ' , ' || l_pk3_value) < 4000 ) then
294 l_merged_pk := l_merged_pk || ' , ' || l_pk3_value;
295 elsif ( Length(l_merged_pk || ' ...') < 4000 ) then
296 l_merged_pk := l_merged_pk || ' ...';
297 end if;
298 END IF;
299 END IF;
300
301 CSM_UTIL_PKG.LOG( 'executing:' || fnd_global.local_chr(10) ||
302 l_stmt || fnd_global.local_chr(10) || ':1 = ' || l_user_id
303 || fnd_global.local_chr(10) || l_error_msg, 'CSM_ACC_PKG.INSERT_ACC',FND_LOG.LEVEL_PROCEDURE);
304
305
306 /*We have at least one PK so get the value*/
307 IF l_pk2_value IS NULL THEN
308 /* Only one PK available */
309 EXECUTE IMMEDIATE l_stmt USING l_user_id, l_pk1_value RETURNING INTO l_access_id;
310 ELSIF l_pk3_value IS NULL THEN
314 /* Three PK's */
311 /*Two PK's */
312 EXECUTE IMMEDIATE l_stmt USING l_user_id, l_pk1_value, l_pk2_value RETURNING INTO l_access_id;
313 ELSE
315 EXECUTE IMMEDIATE l_stmt USING l_user_id, l_pk1_value, l_pk2_value, l_pk3_value
316 RETURNING INTO l_access_id;
317 END IF;
318 -- insert record in outqueue ASG call here *****************************************
319 --get the resource id
320 OPEN c_asg_user (l_user_id);
321 FETCH c_asg_user INTO l_resource_id;
322 CLOSE c_asg_user;
323
324 IF l_resource_id IS NOT NULL THEN --do mark diry only for valid MFS user
325 FOR i IN 1 .. p_publication_item_names.LAST LOOP
326 l_rc := asg_download.markDirty(p_publication_item_names(i), l_access_id, l_resource_id, 'I', sysdate );
327 END LOOP;
328 END IF;
329 END IF;
330
331 EXCEPTION
332 WHEN OTHERS THEN
333 CSM_UTIL_PKG.LOG( 'Exception occurred in CSM_ACC_PKG.INSERT_ACC' || sqlerrm, 'CSM_ACC_PKG.INSERT_ACC',FND_LOG.LEVEL_EXCEPTION);
334 /*** Raise any other error ***/
335 RAISE;
336
337 END Insert_Acc;
338
339
340 /***
341 Procedure that deletes record(s) from any ACC table
342 If p_resource_id is NULL, all ACC records that match the PK values are deleted.
343 If p_resource_id is specified and p_operator='=' the ACC record is only deleted for that specific resource.
344 If p_resource_id is specified and p_operator='<>' all ACC records with resource_id<>p_resource_id are deleted
345 ***/
346 PROCEDURE Delete_Acc
347 ( p_publication_item_names in t_publication_item_list
348 ,p_acc_table_name in VARCHAR2
349 ,p_pk1_name in VARCHAR2
350 ,p_pk1_num_value in NUMBER DEFAULT NULL
351 ,p_pk1_char_value in VARCHAR2 DEFAULT NULL
352 , p_pk1_date_value in DATE DEFAULT NULL
353 , p_pk2_name in VARCHAR2 DEFAULT NULL
354 , p_pk2_num_value in NUMBER DEFAULT NULL
355 , p_pk2_char_value in VARCHAR2 DEFAULT NULL
356 , p_pk2_date_value in DATE DEFAULT NULL
357 , p_pk3_name in VARCHAR2 DEFAULT NULL
358 , p_pk3_num_value in NUMBER DEFAULT NULL
359 , p_pk3_char_value in VARCHAR2 DEFAULT NULL
360 , p_pk3_date_value in DATE DEFAULT NULL
361 ,p_user_id in NUMBER DEFAULT NULL
362 ,p_operator in VARCHAR2 DEFAULT '='
363 )
364 IS
365 l_stmt VARCHAR2(4000);
366 l_cursor INTEGER;
367 l_count INTEGER;
368 l_tab_mobile_user_id dbms_sql.Number_Table;
369 l_tab_access_id dbms_sql.Number_Table;
370 l_pk1_value VARCHAR2(4000);
371 l_pk2_value VARCHAR2(4000);
372 l_pk3_value VARCHAR2(4000);
373 l_pk1_string VARCHAR2(4000);
374 l_pk2_string VARCHAR2(4000);
375 l_pk3_string VARCHAR2(4000);
376 l_pk_err_msg_txt VARCHAR2(4000);
377 l_count_value NUMBER;
378 l_rc BOOLEAN;
379 l_index NUMBER;
380 l_resource_id jtf_rs_resource_extns.resource_id%TYPE;
381 l_user_id NUMBER;
382 BEGIN
383 --Function replaces owner if data routed to owner is set
384 l_user_id := Check_for_owner(p_user_id);
385
386 IF p_pk1_date_value IS null THEN
387 l_pk1_value := NVL( TO_CHAR(p_pk1_num_value ), p_pk1_char_value );
388 l_pk1_string:= ':P1';
389 ELSE
390 l_pk1_value := to_char((p_pk1_date_value),'j');
391 l_pk1_string:= 'to_date(:P1,''j'')';
392 END IF;
393 IF p_pk2_name IS NOT NULL THEN
394 IF p_pk2_date_value IS null THEN
395 l_pk2_value := NVL( TO_CHAR(p_pk2_num_value ), p_pk2_char_value );
396 l_pk2_string:= ':P2';
397 ELSE
398 l_pk2_value := to_char((p_pk2_date_value),'j');
399 l_pk2_string:= 'to_date(:P2,''j'')';
400 END IF;
401 IF p_pk3_name IS NOT NULL THEN
402 /* There are three PK's */
403 IF p_pk3_date_value IS null THEN
404 l_pk3_value := NVL( TO_CHAR(p_pk3_num_value ), p_pk3_char_value );
405 l_pk3_string:= ':P3';
406 ELSE
407 l_pk3_value := to_char((p_pk3_date_value),'j');
408 l_pk3_string:= 'to_date(:P3,''j'')';
409 END IF;
410 END IF;
411 END IF;
412
413 /*** At least 1 PK ***/
414 l_stmt := 'SELECT USER_ID, ACCESS_ID FROM ' || p_acc_table_name ||
415 ' WHERE COUNTER = 1 AND ' || p_pk1_name || ' = ' || l_pk1_string;
416 IF p_pk2_name IS NOT NULL THEN
417 /*** 2 PK's available ***/
418 l_stmt := l_stmt ||' AND ' || p_pk2_name || ' = ' || l_pk2_string;
419 IF p_pk3_name IS NOT NULL THEN
420 /*** 3 PK's available ***/
421 l_stmt := l_stmt ||' AND ' || p_pk3_name || ' = ' || l_pk3_string;
422 END IF;
423 END IF;
424
425 l_cursor := dbms_sql.open_cursor;
426
427 /*** was user_id provided? ***/
428 IF l_user_id IS NOT NULL THEN
429 /*** yes -> add p_operator filter on mobile_user_id to WHERE clause ***/
430 l_stmt := l_stmt || ' AND USER_ID ' || p_operator || ' :P4 ';
431 dbms_sql.parse( l_cursor, l_stmt, dbms_sql.v7);
432 dbms_sql.bind_variable( l_cursor, 'P4', l_user_id );
433 ELSE
434 /*** no -> delete all ACC records ***/
435 dbms_sql.parse( l_cursor, l_stmt, dbms_sql.v7);
436 END IF;
437
441 IF p_pk3_name IS NOT NULL THEN
438 dbms_sql.bind_variable( l_cursor, 'P1', l_pk1_value );
439 IF p_pk2_name IS NOT NULL THEN
440 dbms_sql.bind_variable( l_cursor, 'P2', l_pk2_value );
442 dbms_sql.bind_variable( l_cursor, 'P3', l_pk3_value );
443 END IF;
444 END IF;
445
446 l_index := 1;
447 dbms_sql.define_array( l_cursor, 1, l_tab_mobile_user_id, 10, l_index);
448 dbms_sql.define_array( l_cursor, 2, l_tab_access_id, 10, l_index);
449
450
451 CSM_UTIL_PKG.LOG( 'executing:' || l_stmt || fnd_global.local_chr(10) ||
452 ':P1 = ' || l_pk1_value || fnd_global.local_chr(10) ||
453 ':P2 = ' || l_pk2_value || fnd_global.local_chr(10) ||
454 ':P3 = ' || l_pk3_value || fnd_global.local_chr(10) ||
455 ':P4 = ' || l_user_id, 'CSM_ACC_PKG.DELETE_ACC',FND_LOG.LEVEL_PROCEDURE);
456
457
458 l_count := dbms_sql.execute( l_cursor );
459 LOOP
460 l_count := dbms_sql.fetch_rows(l_cursor);
461
462 dbms_sql.column_value( l_cursor, '1', l_tab_mobile_user_id);
463 dbms_sql.column_value( l_cursor, '2', l_tab_access_id);
464
465 EXIT WHEN l_count <> 10;
466 END LOOP;
467 dbms_sql.close_cursor( l_cursor );
468
469 /*** were any records deleted? ***/
470 IF l_tab_mobile_user_id.COUNT > 0 THEN
471 /*** yes -> loop over arrays containing mobile_user_id and access_id and notify oLite ***/
472 FOR i IN l_tab_mobile_user_id.FIRST .. l_tab_mobile_user_id.LAST LOOP
473 -- notify oLite of deletion ***
474 --get the resource id
475 OPEN c_asg_user (l_tab_mobile_user_id(i));
476 FETCH c_asg_user INTO l_resource_id;
477 CLOSE c_asg_user;
478
479 IF l_resource_id IS NOT NULL THEN --do mark diry only for valid MFS user
480 FOR j IN 1 .. p_publication_item_names.LAST LOOP
481 l_rc := asg_download.markDirty( p_publication_item_names(j), l_tab_access_id(i)
482 , l_resource_id, 'D', sysdate );
483 END LOOP;
484 END IF;
485 END LOOP;
486 END IF;
487
488 /*Perform the actual delete*/
489 l_stmt := 'DELETE '||p_acc_table_name||
490 ' WHERE COUNTER = 1'||
491 ' AND '||p_pk1_name||' = ' || l_pk1_string;
492 IF p_pk2_name IS NOT NULL THEN
493 l_stmt := l_stmt ||' AND '||p_pk2_name|| ' = ' || l_pk2_string;
494 IF p_pk3_name IS NOT NULL THEN
495 l_stmt := l_stmt ||' AND '||p_pk3_name|| ' = ' || l_pk3_string;
496 END IF;
497 END IF;
498 IF l_user_id IS NOT NULL THEN
499 l_stmt := l_stmt ||' AND USER_ID '||p_operator||' :P4';
500 END IF;
501
502
503 IF p_pk2_name IS NULL AND p_pk3_name IS NULL AND l_user_id IS NULL THEN
504 EXECUTE IMMEDIATE l_stmt USING l_pk1_value;
505 ELSIF l_user_id IS NULL AND p_pk3_name IS NULL AND p_pk2_name IS NOT NULL THEN
506 EXECUTE IMMEDIATE l_stmt USING l_pk1_value, l_pk2_value;
507 ELSIF l_user_id IS NULL AND p_pk3_name IS NOT NULL AND p_pk2_name IS NOT NULL THEN
508 EXECUTE IMMEDIATE l_stmt USING l_pk1_value, l_pk2_value, l_pk3_value;
509 ELSIF l_user_id IS NOT NULL AND p_pk2_name IS NULL AND p_pk3_name IS NULL THEN
510 EXECUTE IMMEDIATE l_stmt USING l_pk1_value, l_user_id;
511 ELSIF l_user_id IS NOT NULL AND p_pk2_name IS NOT NULL AND p_pk3_name IS NULL THEN
512 EXECUTE IMMEDIATE l_stmt USING l_pk1_value, l_pk2_value, l_user_id;
513 ELSE
514 EXECUTE IMMEDIATE l_stmt USING l_pk1_value, l_pk2_value, l_pk3_value, l_user_id;
515 END IF;
516
517 /* Now we have deleted all records we have to decrease the counter of the remaining records*/
518 l_stmt := 'UPDATE '||p_acc_table_name||
519 ' SET COUNTER = COUNTER - 1'||
520 ', LAST_UPDATE_DATE = SYSDATE'||
521 ', LAST_UPDATED_BY = 1'||
522 ' WHERE COUNTER >= 2 AND '||p_pk1_name||' = ' || l_pk1_string;
523 IF p_pk2_name IS NOT NULL THEN
524 l_stmt := l_stmt ||' AND '||p_pk2_name|| ' = ' || l_pk2_string;
525 IF p_pk3_name IS NOT NULL THEN
526 l_stmt := l_stmt ||' AND '||p_pk3_name|| ' = ' || l_pk3_string;
527 END IF;
528 END IF;
529 IF l_user_id IS NOT NULL THEN
530 l_stmt := l_stmt ||' AND USER_ID '|| p_operator ||' :P4';
531 END IF;
532
533
534 IF p_pk2_name IS NULL AND p_pk3_name IS NULL AND l_user_id IS NULL THEN
535 EXECUTE IMMEDIATE l_stmt USING l_pk1_value;
536 ELSIF l_user_id IS NULL AND p_pk3_name IS NULL AND p_pk2_name IS NOT NULL THEN
537 EXECUTE IMMEDIATE l_stmt USING l_pk1_value, l_pk2_value;
538 ELSIF l_user_id IS NULL AND p_pk3_name IS NOT NULL AND p_pk2_name IS NOT NULL THEN
539 EXECUTE IMMEDIATE l_stmt USING l_pk1_value, l_pk2_value, l_pk3_value;
540 ELSIF l_user_id IS NOT NULL AND p_pk2_name IS NULL AND p_pk3_name IS NULL THEN
541 EXECUTE IMMEDIATE l_stmt USING l_pk1_value, l_user_id;
542 ELSIF l_user_id IS NOT NULL AND p_pk2_name IS NOT NULL AND p_pk3_name IS NULL THEN
543 EXECUTE IMMEDIATE l_stmt USING l_pk1_value, l_pk2_value, l_user_id;
544 ELSE
545 EXECUTE IMMEDIATE l_stmt USING l_pk1_value, l_pk2_value, l_pk3_value, l_user_id;
546 END IF;
547
548 EXCEPTION WHEN OTHERS THEN
549 IF l_cursor <> 0 THEN
550 dbms_sql.close_cursor( l_cursor );
551 END IF;
552 CSM_UTIL_PKG.LOG( 'Exception occurred in CSM_ACC_PKG.DELETE_ACC' || sqlerrm, 'CSM_ACC_PKG.DELETE_ACC',FND_LOG.LEVEL_EXCEPTION);
553 /*** Raise any other error ***/
554 RAISE;
555 END Delete_Acc;
556
557 /*** Procedure that re-sends a record with given acc_id to the mobile ***/
558 PROCEDURE Update_Acc
559 ( p_publication_item_names in t_publication_item_list
560 ,p_acc_table_name in VARCHAR2
561 ,p_user_id in NUMBER
562 ,p_access_id in NUMBER
563 )
564 IS
565 l_rc BOOLEAN;
566 l_resource_id jtf_rs_resource_extns.resource_id%TYPE;
567 l_user_id NUMBER;
568 BEGIN
569 --Function replaces owner if data routed to owner is set
570 l_user_id := Check_for_owner(p_user_id);
571
572 --get the resource id
573 OPEN c_asg_user (l_user_id);
574 FETCH c_asg_user INTO l_resource_id;
575 CLOSE c_asg_user;
576
577 IF l_resource_id IS NOT NULL THEN --do mark diry only for valid MFS user
578 --call update outqueue ASG call *****************************************
579 FOR i IN 1 .. p_publication_item_names.LAST LOOP
580 l_rc := asg_download.markDirty(p_publication_item_names(i), p_access_id, l_resource_id, 'U', sysdate );
581 END LOOP;
582 END IF;
583
584 EXCEPTION
585 WHEN OTHERS THEN
586 CSM_UTIL_PKG.LOG( 'Exception occurred in CSM_ACC_PKG.UPDATE_ACC' || sqlerrm, 'CSM_ACC_PKG.UPDATE_ACC',FND_LOG.LEVEL_EXCEPTION);
587 /*** Raise any other error ***/
588 RAISE;
589
590 END Update_Acc;
591
592 FUNCTION Check_for_owner(p_user_id NUMBER)
593 RETURN NUMBER
594 IS
595 l_user_id NUMBER;
596 l_owner_id NUMBER;
597
598 BEGIN
599
600 /*checking if data has to be processed for the owner*/
601 IF csm_profile_pkg.Get_Route_Data_To_Owner ='Y' THEN
602 --get owner
603 l_owner_id := csm_util_pkg.get_owner(p_user_id);
604 IF l_owner_id > -1 THEN --If the owner is valid
605 l_user_id := l_owner_id;
606 ELSE --if owner is invalid then use user_id
607 l_user_id := p_user_id;
608 END IF;
609 ELSE --if the profile is not set then process for the user
610 l_user_id := p_user_id;
611 END IF;
612 RETURN l_user_id;
613
614 END Check_for_owner;
615
616 END;