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