[Home] [Help]
PACKAGE BODY: APPS.CSM_LOBS_EVENT_PKG
Source
1 PACKAGE BODY CSM_LOBS_EVENT_PKG AS
2 /* $Header: csmelobb.pls 120.10 2008/02/07 07:07:17 anaraman ship $*/
3 --
4 -- To modify this template, edit file PKGBODY.TXT in TEMPLATE
5 -- directory of SQL Navigator
6 --
7 -- Purpose: Briefly explain the functionality of the package body
8 --
9 -- MODIFICATION HISTORY
10 -- Person Date Comments
11 -- --------- ------ ------------------------------------------
12 -- Enter procedure, function bodies as shown below
13
14 g_debug_level NUMBER; -- debug level
15 g_pk1_name CONSTANT VARCHAR2(30) := 'FILE_ID';
16 g_table_name CONSTANT VARCHAR2(30) := 'CSF_M_LOBS';
17
18 TYPE Number_TAB IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
19 TYPE Varchar2_TAB IS TABLE OF VARCHAR2(255) INDEX BY BINARY_INTEGER;
20
21 --Bug 4938130
22 PROCEDURE CONC_DOWNLOAD_ATTACHMENTS (p_status OUT NOCOPY VARCHAR2,
23 p_message OUT NOCOPY VARCHAR2)
24 IS
25 PRAGMA AUTONOMOUS_TRANSACTION;
26 l_pub_item VARCHAR2(30) := 'CSF_M_LOBS';
27 l_prog_update_date jtm_con_request_data.last_run_date%TYPE;
28 l_run_date date;
29 l_sqlerrno varchar2(20);
30 l_sqlerrmsg varchar2(2000);
31 l_tab_access_id ASG_DOWNLOAD.ACCESS_LIST;
32 l_tab_user_id ASG_DOWNLOAD.USER_LIST;
33 l_publicationitemname VARCHAR2(50):= 'CSF_M_LOBS';
34 l_tab_file_id Number_TAB;
35 l_tab_seq_num Number_TAB;
36 l_tab_name Varchar2_TAB;
37 l_tab_description Varchar2_TAB;
38 l_tab_language Varchar2_TAB;
39 l_tab_entity_name Varchar2_TAB;
40 l_tab_pk1_value Varchar2_TAB;
41 l_tab_pk2_value Varchar2_TAB;
42 l_tab_upd_user Varchar2_TAB;
43 l_tab_category Varchar2_TAB;
44 l_tab_usage_type Varchar2_TAB;
45 l_tab_data_type Varchar2_TAB;
46 l_dummy BOOLEAN;
47 l_tab_datatype_id Number_TAB;
48 l_tab_document_id Number_TAB;
49 l_tab_title Varchar2_TAB;
50 --NOTE
51 --DATATYPE ID 1 = SHORT TEXT
52 --DATATYPE ID 5 = WEB PAGE (URL)
53 --DATATYPE ID 6 = LOBS
54 --Cursor to get the SR attachemnts
55 CURSOR l_SRAtt_csr
56 IS
57 SELECT csm_fnd_lobs_acc_s.NEXTVAL, fnddoc.media_id, acc.user_id,
58 fnddoccat_tl.name, fnddoc_tl.description, fnddoc_tl.language,
59 fndattdoc.entity_name, fndattdoc.pk1_value, fndattdoc.pk2_value,
60 fndattdoc.seq_num,asg.user_name,fnddoccat_tl.user_name,
61 fnddoc.usage_type,doctype.user_name,fnddoc.datatype_id,
62 fnddoc.document_id,fnddoc_tl.title
63 FROM fnd_documents_tl fnddoc_tl, fnd_documents fnddoc,
64 fnd_document_categories_tl fnddoccat_tl,
65 fnd_attached_documents fndattdoc,
66 -- fnd_lobs lobs,
67 csm_incidents_all_acc acc, asg_user asg,
68 FND_DOCUMENT_DATATYPES doctype
69 WHERE fndattdoc.document_id = fnddoc_tl.document_id
70 AND fnddoc_tl.language = asg.language
71 AND asg.user_id=asg.owner_id
72 AND fnddoc_tl.document_id = fnddoc.document_id
73 --AND fnddoc.media_id = lobs.file_id(+)
74 AND fnddoccat_tl.category_id = fnddoc.category_id
75 AND fnddoccat_tl.language = asg.language
76 AND fndattdoc.pk1_value = to_char(acc.incident_id)
77 AND asg.user_id = acc.user_id
78 AND fnddoccat_tl.name = 'MISC'
79 AND fndattdoc.entity_name = 'CS_INCIDENTS'
80 AND fnddoc.datatype_id in(1,5,6)
81 AND doctype.datatype_id = fnddoc.datatype_id
82 AND doctype.language = asg.language
83 AND NOT EXISTS (SELECT 1 FROM CSM_FND_LOBS_ACC cflacc
84 WHERE cflacc.document_id = fnddoc.document_id
85 AND cflacc.user_id = acc.user_id);
86
87 --Cursor to get the task attachemnts
88 CURSOR l_TaskAtt_csr
89 IS
90 SELECT csm_fnd_lobs_acc_s.NEXTVAL, fnddoc.media_id, acc.user_id,
91 fnddoccat_tl.name, fnddoc_tl.description, fnddoc_tl.language,
92 fndattdoc.entity_name, fndattdoc.pk1_value, fndattdoc.pk2_value,
93 fndattdoc.seq_num,asg.user_name,fnddoccat_tl.user_name,
94 fnddoc.usage_type,doctype.user_name,fnddoc.datatype_id,
95 fnddoc.document_id,fnddoc_tl.title
96 FROM fnd_documents_tl fnddoc_tl, fnd_documents fnddoc,
97 fnd_document_categories_tl fnddoccat_tl,
98 fnd_attached_documents fndattdoc,
99 -- fnd_lobs lobs,
100 csm_tasks_acc acc, asg_user asg,
101 FND_DOCUMENT_DATATYPES doctype
102 WHERE fndattdoc.document_id = fnddoc_tl.document_id
103 AND fnddoc_tl.language = asg.language
104 AND fnddoc_tl.document_id = fnddoc.document_id
105 --AND fnddoc.media_id = lobs.file_id(+)
106 AND fnddoccat_tl.category_id = fnddoc.category_id
107 AND fnddoccat_tl.language = asg.language
108 AND fndattdoc.pk1_value = to_char(acc.task_id)
109 AND asg.user_id = acc.user_id
110 AND asg.user_id=asg.owner_id
111 AND fnddoccat_tl.name = 'MISC'
112 AND fndattdoc.entity_name = 'JTF_TASKS_B'
113 AND fnddoc.datatype_id in(1,5,6)
114 AND doctype.datatype_id = fnddoc.datatype_id
115 AND doctype.language = asg.language
116 AND NOT EXISTS (SELECT 1 FROM CSM_FND_LOBS_ACC cflacc
117 WHERE cflacc.document_id = fnddoc.document_id
118 AND cflacc.user_id = acc.user_id);
119
120 --Bug 5726888
121 --Cursor to get the Signature attachemnts
122 CURSOR c_Signature IS
123 SELECT csm_fnd_lobs_acc_s.NEXTVAL, fnddoc.media_id, acc.user_id,
124 fnddoccat_tl.name, fnddoc_tl.description, fnddoc_tl.language,
125 fndattdoc.entity_name, fndattdoc.pk1_value, fndattdoc.pk2_value,
126 fndattdoc.seq_num,asg.user_name,fnddoccat_tl.user_name,
127 fnddoc.usage_type,doctype.user_name,fnddoc.datatype_id,
128 fnddoc.document_id,fnddoc_tl.title
129 FROM fnd_documents_tl fnddoc_tl, fnd_documents fnddoc,
130 fnd_document_categories_tl fnddoccat_tl,
131 fnd_attached_documents fndattdoc,
132 -- fnd_lobs lobs,
133 csm_debrief_headers_acc acc, asg_user asg,
134 FND_DOCUMENT_DATATYPES doctype
135 WHERE fndattdoc.document_id = fnddoc_tl.document_id
136 AND fnddoc_tl.language = asg.language
137 AND fnddoc_tl.document_id = fnddoc.document_id
138 -- AND fnddoc.media_id = lobs.file_id
139 AND fnddoccat_tl.category_id = fnddoc.category_id
140 AND fnddoccat_tl.language = asg.language
141 AND fndattdoc.pk1_value = to_char(acc.debrief_header_id)
142 AND fnddoccat_tl.name = 'SIGNATURE'
143 AND fndattdoc.entity_name = 'CSF_DEBRIEF_HEADERS'
144 AND fnddoc.datatype_id =6
145 AND doctype.datatype_id = fnddoc.datatype_id
146 AND doctype.language = asg.language
147 AND acc.user_id = asg.user_id
148 AND asg.user_id=asg.owner_id
149 AND NOT EXISTS (SELECT 1 FROM CSM_FND_LOBS_ACC cflacc
150 WHERE cflacc.document_id = fnddoc.document_id
151 AND cflacc.user_id = acc.user_id);
152
153 BEGIN
154 -- data program is run
155 l_run_date := SYSDATE;
156 /*** get debug level ***/
157 g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
158 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
159 jtm_message_log_pkg.Log_Msg
160 ( 0
161 , g_table_name
162 , 'Entering CONC_DOWNLOAD_ATTACHMENTS'
163 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
164 );
165 END IF;
166
167 --Emptying all lists.
168 l_tab_access_id.delete;
169 l_tab_user_id.delete;
170 l_tab_file_id.delete;
171 l_tab_name.delete;
172 l_tab_description.delete;
173 l_tab_language.delete;
174 l_tab_entity_name.delete;
175 l_tab_pk1_value.delete;
176 l_tab_pk2_value.delete;
177 l_tab_seq_num.delete;
178 l_tab_upd_user.delete;
179 l_tab_category.delete;
180 l_tab_usage_type.delete;
181 l_tab_data_type.delete;
182 l_tab_datatype_id.delete;
183 l_tab_document_id.delete;
184 l_tab_title.delete;
185
186 --If the max size of attachment is less than 1, then exit.
187 IF (TO_NUMBER(FND_PROFILE.Value('CSM_MAX_ATTACHMENT_SIZE')) < 1) THEN
188 /*Update the last run date*/
189 UPDATE jtm_con_request_data SET last_run_date = l_run_date
190 WHERE package_name = 'CSM_LOBS_EVENT_PKG'
191 AND procedure_name = 'CONC_DOWNLOAD_ATTACHMENTS';
192
193 COMMIT;
194
195 p_status := 'FINE';
196 p_message := 'CSM_LOBS_EVENT_PKG.CONC_DOWNLOAD_ATTACHMENTS '
197 || ' Executed successfully';
198
199 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
200 jtm_message_log_pkg.Log_Msg
201 ( 0
202 , g_table_name
203 , 'Leaving CONC_DOWNLOAD_ATTACHMENTS - Max att download size is less than 1'
204 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
205 );
206 END IF;
207 RETURN;
208 END IF;
209
210
211 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
212 jtm_message_log_pkg.Log_Msg
213 ( 0
214 , g_table_name
215 , 'Downloading Attachments for SRs'
216 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
217 );
218 END IF;
219
220 --- DOWNLOAD SR ATTACHMENTS
221 OPEN l_SRAtt_csr;
222 FETCH l_SRAtt_csr BULK COLLECT INTO l_tab_access_id, l_tab_file_id, l_tab_user_id, l_tab_name,
223 l_tab_description, l_tab_language, l_tab_entity_name, l_tab_pk1_value,
224 l_tab_pk2_value, l_tab_seq_num,l_tab_upd_user,l_tab_category,l_tab_usage_type,l_tab_data_type,
225 l_tab_datatype_id,l_tab_document_id,l_tab_title;
226 CLOSE l_SRAtt_csr;
227
228 IF l_tab_access_id.COUNT > 0 THEN
229 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
230 jtm_message_log_pkg.Log_Msg
231 ( 0
232 , g_table_name
233 , 'Pushing ' || l_tab_access_id.COUNT || ' updated record(s)'
234 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
235 );
236 END IF;
237
238 FORALL i in l_tab_access_id.FIRST .. l_tab_access_id.LAST
239 INSERT INTO CSM_FND_LOBS_ACC
240 (access_id, file_id, user_id, name, description,
241 language, entity_name, pk1_value, pk2_value, seq_num,
242 update_user, category, usage_type, data_type, counter,
243 last_update_date, last_updated_by, creation_date,created_by, data_type_id,
244 document_id,title)
245 VALUES (l_tab_access_id(i), l_tab_file_id(i), l_tab_user_id(i), l_tab_name(i), l_tab_description(i),
246 l_tab_language(i), l_tab_entity_name(i), l_tab_pk1_value(i), l_tab_pk2_value(i), l_tab_seq_num(i),
247 l_tab_upd_user(i), l_tab_category(i), l_tab_usage_type(i),l_tab_data_type(i), 1,
248 sysdate, 1, sysdate, 1, l_tab_datatype_id(i),
249 l_tab_document_id(i),l_tab_title(i));
250
251 --do markdiry for all the selected records
252 l_dummy := asg_download.mark_dirty(
253 P_PUB_ITEM => l_publicationitemname
254 , P_ACCESSLIST => l_tab_access_id
255 , P_USERID_LIST => l_tab_user_id
256 , P_DML_TYPE => 'I'
257 , P_TIMESTAMP => SYSDATE);
258
259 COMMIT;
260
261 END IF;
262
263 --Emptying all lists.
264 l_tab_access_id.delete;
265 l_tab_user_id.delete;
266 l_tab_file_id.delete;
267 l_tab_name.delete;
268 l_tab_description.delete;
269 l_tab_language.delete;
270 l_tab_entity_name.delete;
271 l_tab_pk1_value.delete;
272 l_tab_pk2_value.delete;
273 l_tab_seq_num.delete;
274 l_tab_upd_user.delete;
275 l_tab_category.delete;
276 l_tab_usage_type.delete;
277 l_tab_data_type.delete;
278 l_tab_datatype_id.delete;
279 l_tab_document_id.delete;
280 l_tab_title.delete;
281
282 --- DOWNLOAD TASK ATTACHMENTS
283 OPEN l_TaskAtt_csr;
284 FETCH l_TaskAtt_csr BULK COLLECT INTO l_tab_access_id, l_tab_file_id, l_tab_user_id, l_tab_name,
285 l_tab_description, l_tab_language, l_tab_entity_name, l_tab_pk1_value,
286 l_tab_pk2_value, l_tab_seq_num,l_tab_upd_user,l_tab_category,l_tab_usage_type,l_tab_data_type,
287 l_tab_datatype_id,l_tab_document_id,l_tab_title;
288 CLOSE l_TaskAtt_csr;
289
290 IF l_tab_access_id.COUNT > 0 THEN
291 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
292 jtm_message_log_pkg.Log_Msg
293 ( 0
294 , g_table_name
295 , 'Pushing ' || l_tab_access_id.COUNT || ' updated record(s)'
296 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
297 );
298 END IF;
299 --insert the rows into access table
300 FORALL i in l_tab_access_id.FIRST .. l_tab_access_id.LAST
301 INSERT INTO CSM_FND_LOBS_ACC
302 (access_id, file_id, user_id, name, description,
303 language, entity_name, pk1_value, pk2_value, seq_num,
304 update_user, category, usage_type, data_type, counter,
305 last_update_date, last_updated_by, creation_date,created_by, data_type_id,
306 document_id,title)
307 VALUES (l_tab_access_id(i), l_tab_file_id(i), l_tab_user_id(i), l_tab_name(i), l_tab_description(i),
308 l_tab_language(i), l_tab_entity_name(i), l_tab_pk1_value(i), l_tab_pk2_value(i), l_tab_seq_num(i),
309 l_tab_upd_user(i), l_tab_category(i), l_tab_usage_type(i),l_tab_data_type(i), 1,
310 sysdate, 1, sysdate, 1, l_tab_datatype_id(i),
311 l_tab_document_id(i),l_tab_title(i));
312
313
314 l_dummy := asg_download.mark_dirty(
315 P_PUB_ITEM => l_publicationitemname
316 , P_ACCESSLIST => l_tab_access_id
317 , P_USERID_LIST => l_tab_user_id
318 , P_DML_TYPE => 'I'
319 , P_TIMESTAMP => SYSDATE);
320
321 COMMIT;
322
323 END IF;
324
325 --Bug 5726888
326 --Emptying all lists.
327 l_tab_access_id.delete;
328 l_tab_user_id.delete;
329 l_tab_file_id.delete;
330 l_tab_name.delete;
331 l_tab_description.delete;
332 l_tab_language.delete;
333 l_tab_entity_name.delete;
334 l_tab_pk1_value.delete;
335 l_tab_pk2_value.delete;
336 l_tab_seq_num.delete;
337 l_tab_upd_user.delete;
338 l_tab_category.delete;
339 l_tab_usage_type.delete;
340 l_tab_data_type.delete;
341 l_tab_datatype_id.delete;
342 l_tab_document_id.delete;
343 l_tab_title.delete;
344
345 --- DOWNLOAD SIGNATURE ATTACHMENTS
346 OPEN c_Signature;
347 FETCH c_Signature BULK COLLECT INTO l_tab_access_id, l_tab_file_id, l_tab_user_id, l_tab_name,
348 l_tab_description, l_tab_language, l_tab_entity_name, l_tab_pk1_value,
349 l_tab_pk2_value, l_tab_seq_num,l_tab_upd_user,l_tab_category,l_tab_usage_type,l_tab_data_type,
350 l_tab_datatype_id,l_tab_document_id,l_tab_title;
351 CLOSE c_Signature;
352
353 IF l_tab_access_id.COUNT > 0 THEN
354 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
355 jtm_message_log_pkg.Log_Msg
356 ( 0
357 , g_table_name
358 , 'Pushing ' || l_tab_access_id.COUNT || ' updated record(s)'
359 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
360 );
361 END IF;
362 --insert the rows into access table
363 FORALL i in l_tab_access_id.FIRST .. l_tab_access_id.LAST
364 INSERT INTO CSM_FND_LOBS_ACC
365 (access_id, file_id, user_id, name, description,
366 language, entity_name, pk1_value, pk2_value, seq_num,
367 update_user, category, usage_type, data_type, counter,
368 last_update_date, last_updated_by, creation_date,created_by, data_type_id,
369 document_id,title)
370 VALUES (l_tab_access_id(i), l_tab_file_id(i), l_tab_user_id(i), l_tab_name(i), l_tab_description(i),
371 l_tab_language(i), l_tab_entity_name(i), l_tab_pk1_value(i), l_tab_pk2_value(i), l_tab_seq_num(i),
372 l_tab_upd_user(i), l_tab_category(i), l_tab_usage_type(i),l_tab_data_type(i), 1,
373 sysdate, 1, sysdate, 1, l_tab_datatype_id(i),
374 l_tab_document_id(i),l_tab_title(i));
375
376
377 l_dummy := asg_download.mark_dirty(
378 P_PUB_ITEM => l_publicationitemname
382 , P_TIMESTAMP => SYSDATE);
379 , P_ACCESSLIST => l_tab_access_id
380 , P_USERID_LIST => l_tab_user_id
381 , P_DML_TYPE => 'I'
383
384 COMMIT;
385
386 END IF;
387
388
389 -- set the program update date in jtm_con_request_data to sysdate
390 UPDATE jtm_con_request_data
391 SET last_run_date = l_run_date
392 WHERE package_name = 'CSM_LOBS_EVENT_PKG'
393 AND procedure_name = 'CONC_DOWNLOAD_ATTACHMENTS';
394
395 COMMIT;
396
397 p_status := 'FINE';
398 p_message := 'CSM_LOBS_EVENT_PKG.CONC_DOWNLOAD_ATTACHMENTS '
399 || ' Executed successfully';
400
401 EXCEPTION
402 WHEN others THEN
403 l_sqlerrno := to_char(SQLCODE);
404 l_sqlerrmsg := substr(SQLERRM, 1,2000);
405 p_status := 'ERROR';
406 p_message := 'Error in '||
407 'CSM_LOBS_EVENT_PKG.CONC_DOWNLOAD_ATTACHMENTS: ' || l_sqlerrno || ':' || l_sqlerrmsg;
408 CSM_UTIL_PKG.LOG('CSM_CSI_ITEM_ATTR_EVENT_PKG ERROR : ' || l_sqlerrno || ':' || l_sqlerrmsg, 'CSM_CSI_ITEM_ATTR_EVENT_PKG.Refresh_acc',FND_LOG.LEVEL_EXCEPTION);
409 ROLLBACK;
410 END CONC_DOWNLOAD_ATTACHMENTS;
411
412 --Bug 4938130
413 PROCEDURE INSERT_ALL_ACC_RECORDS (p_user_id IN NUMBER )
414 IS
415 --Cursor to get the SR attachemnts
416 CURSOR c_SRAtt (b_user_id NUMBER) IS
417 SELECT csm_fnd_lobs_acc_s.NEXTVAL, fnddoc.media_id, acc.user_id,
418 fnddoccat_tl.name, fnddoc_tl.description, fnddoc_tl.language,
419 fndattdoc.entity_name, fndattdoc.pk1_value, fndattdoc.pk2_value,
420 fndattdoc.seq_num,asg.user_name,fnddoccat_tl.user_name,
421 fnddoc.usage_type,doctype.user_name,fnddoc.datatype_id,
422 fnddoc.document_id,fnddoc_tl.title
423 FROM fnd_documents_tl fnddoc_tl, fnd_documents fnddoc,
424 fnd_document_categories_tl fnddoccat_tl,
425 fnd_attached_documents fndattdoc,
426 -- fnd_lobs lobs,
427 csm_incidents_all_acc acc, asg_user asg,
428 FND_DOCUMENT_DATATYPES doctype
429 WHERE fndattdoc.document_id = fnddoc_tl.document_id
430 AND fnddoc_tl.language = asg.language
431 AND fnddoc_tl.document_id = fnddoc.document_id
432 -- AND fnddoc.media_id = lobs.file_id (+)
433 AND fnddoccat_tl.category_id = fnddoc.category_id
434 AND fnddoccat_tl.language = asg.language
435 AND fndattdoc.pk1_value = to_char(acc.incident_id)
436 AND asg.user_id = acc.user_id
437 AND fnddoccat_tl.name = 'MISC'
438 AND fndattdoc.entity_name = 'CS_INCIDENTS'
439 AND fnddoc.datatype_id in (1,5,6)
440 AND doctype.datatype_id = fnddoc.datatype_id
441 AND doctype.language = asg.language
442 AND acc.user_id = b_user_id;
443
444 --Cursor to get the task attachemnts
445 CURSOR c_TaskAtt (b_user_id NUMBER) IS
446 SELECT csm_fnd_lobs_acc_s.NEXTVAL, fnddoc.media_id, acc.user_id,
447 fnddoccat_tl.name, fnddoc_tl.description, fnddoc_tl.language,
448 fndattdoc.entity_name, fndattdoc.pk1_value, fndattdoc.pk2_value,
449 fndattdoc.seq_num,asg.user_name,fnddoccat_tl.user_name,
450 fnddoc.usage_type,doctype.user_name,fnddoc.datatype_id,
451 fnddoc.document_id,fnddoc_tl.title
452 FROM fnd_documents_tl fnddoc_tl, fnd_documents fnddoc,
453 fnd_document_categories_tl fnddoccat_tl,
454 fnd_attached_documents fndattdoc,
455 -- fnd_lobs lobs,
456 csm_tasks_acc acc, asg_user asg,
457 FND_DOCUMENT_DATATYPES doctype
458 WHERE fndattdoc.document_id = fnddoc_tl.document_id
459 AND fnddoc_tl.language = asg.language
460 AND fnddoc_tl.document_id = fnddoc.document_id
461 -- AND fnddoc.media_id = lobs.file_id (+)
462 AND fnddoccat_tl.category_id = fnddoc.category_id
463 AND fnddoccat_tl.language = asg.language
464 AND fndattdoc.pk1_value = to_char(acc.task_id)
465 AND asg.user_id = acc.user_id
466 AND fnddoccat_tl.name = 'MISC'
467 AND fndattdoc.entity_name = 'JTF_TASKS_B'
468 AND fnddoc.datatype_id in (1,5,6)
469 AND doctype.datatype_id = fnddoc.datatype_id
470 AND doctype.language = asg.language
471 AND acc.user_id = b_user_id;
472
473 --Cursor to get the Signature attachemnts
474 CURSOR c_Signature (b_user_id NUMBER) IS
475 SELECT csm_fnd_lobs_acc_s.NEXTVAL, fnddoc.media_id, acc.user_id,
476 fnddoccat_tl.name, fnddoc_tl.description, fnddoc_tl.language,
477 fndattdoc.entity_name, fndattdoc.pk1_value, fndattdoc.pk2_value,
478 fndattdoc.seq_num,asg.user_name,fnddoccat_tl.user_name,
479 fnddoc.usage_type,doctype.user_name,fnddoc.datatype_id,
480 fnddoc.document_id,fnddoc_tl.title
481 FROM fnd_documents_tl fnddoc_tl, fnd_documents fnddoc,
482 fnd_document_categories_tl fnddoccat_tl,
483 fnd_attached_documents fndattdoc,
484 -- fnd_lobs lobs,
485 csm_debrief_headers_acc acc, asg_user asg,
486 FND_DOCUMENT_DATATYPES doctype
487 WHERE fndattdoc.document_id = fnddoc_tl.document_id
488 AND fnddoc_tl.language = asg.language
489 AND fnddoc_tl.document_id = fnddoc.document_id
490 -- AND fnddoc.media_id = lobs.file_id
491 AND fnddoccat_tl.category_id = fnddoc.category_id
492 AND fnddoccat_tl.language = asg.language
496 AND fndattdoc.entity_name = 'CSF_DEBRIEF_HEADERS'
493 AND fndattdoc.pk1_value = to_char(acc.debrief_header_id)
494 AND asg.user_id = b_user_id
495 AND fnddoccat_tl.name = 'SIGNATURE'
497 AND fnddoc.datatype_id =6
498 AND doctype.datatype_id = fnddoc.datatype_id
499 AND doctype.language = asg.language
500 AND acc.user_id = asg.user_id;
501
502 l_dummy BOOLEAN;
503 l_publicationitemname VARCHAR2(50):= 'CSF_M_LOBS';
504 l_tab_access_id ASG_DOWNLOAD.ACCESS_LIST;
505 l_tab_user_id ASG_DOWNLOAD.USER_LIST;
506
507 l_tab_file_id Number_TAB;
508 l_tab_seq_num Number_TAB;
509 l_tab_name Varchar2_TAB;
510 l_tab_description Varchar2_TAB;
511 l_tab_language Varchar2_TAB;
512 l_tab_entity_name Varchar2_TAB;
513 l_tab_pk1_value Varchar2_TAB;
514 l_tab_pk2_value Varchar2_TAB;
515 l_tab_upd_user Varchar2_TAB;
516 l_tab_category Varchar2_TAB;
517 l_tab_usage_type Varchar2_TAB;
518 l_tab_data_type Varchar2_TAB;
519 l_tab_datatype_id Number_TAB;
520 l_tab_document_id Number_TAB;
521 l_tab_title Varchar2_TAB;
522
523 x_return_status varchar2(2000);
524 BEGIN
525
526 /*** get debug level ***/
527 g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
528 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
529 jtm_message_log_pkg.Log_Msg
530 ( 0
531 , g_table_name
532 , 'Entering INSERT_ALL_ACC_RECORDS'
533 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
534 );
535 END IF;
536 --Empty the access table for the user before insert it freshly
537 --bug 5213097
538 DELETE FROM CSM_FND_LOBS_ACC WHERE user_id = p_user_id;
539
540 --SIGNATURE
541 l_tab_access_id.delete;
542 l_tab_user_id.delete;
543 l_tab_file_id.delete;
544 l_tab_name.delete;
545 l_tab_description.delete;
546 l_tab_language.delete;
547 l_tab_entity_name.delete;
548 l_tab_pk1_value.delete;
549 l_tab_pk2_value.delete;
550 l_tab_seq_num.delete;
551 l_tab_upd_user.delete;
552 l_tab_category.delete;
553 l_tab_usage_type.delete;
554 l_tab_data_type.delete;
555 l_tab_datatype_id.delete;
556 l_tab_document_id.delete;
557 l_tab_title.delete;
558
559 OPEN c_Signature(p_user_id);
560 FETCH c_Signature BULK COLLECT INTO l_tab_access_id, l_tab_file_id, l_tab_user_id, l_tab_name,
561 l_tab_description, l_tab_language, l_tab_entity_name, l_tab_pk1_value,
562 l_tab_pk2_value, l_tab_seq_num,l_tab_upd_user,l_tab_category,l_tab_usage_type,l_tab_data_type,
563 l_tab_datatype_id,l_tab_document_id,l_tab_title;
564 CLOSE c_Signature;
565
566 IF l_tab_access_id.COUNT > 0 THEN
567 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
568 jtm_message_log_pkg.Log_Msg
569 ( 0
570 , g_table_name
571 , 'Pushing ' || l_tab_access_id.COUNT || ' updated record(s)'
572 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
573 );
574 END IF;
575
576 FORALL i in l_tab_access_id.FIRST .. l_tab_access_id.LAST
577 INSERT INTO CSM_FND_LOBS_ACC
578 (access_id, file_id, user_id, name, description,
579 language, entity_name, pk1_value, pk2_value, seq_num,
580 update_user, category, usage_type, data_type, counter,
581 last_update_date, last_updated_by, creation_date,created_by, data_type_id,
582 document_id,title)
583 VALUES (l_tab_access_id(i), l_tab_file_id(i), l_tab_user_id(i), l_tab_name(i), l_tab_description(i),
584 l_tab_language(i), l_tab_entity_name(i), l_tab_pk1_value(i), l_tab_pk2_value(i), l_tab_seq_num(i),
585 l_tab_upd_user(i), l_tab_category(i), l_tab_usage_type(i),l_tab_data_type(i), 1,
586 sysdate, 1, sysdate, 1, l_tab_datatype_id(i),
587 l_tab_document_id(i),l_tab_title(i));
588
589
590 l_dummy := asg_download.mark_dirty(
591 P_PUB_ITEM => l_publicationitemname
592 , P_ACCESSLIST => l_tab_access_id
593 , P_USERID_LIST => l_tab_user_id
594 , P_DML_TYPE => 'I'
595 , P_TIMESTAMP => SYSDATE);
596
597 END IF;
598
599 --SR
600 l_tab_access_id.delete;
601 l_tab_user_id.delete;
602 l_tab_file_id.delete;
603 l_tab_name.delete;
604 l_tab_description.delete;
605 l_tab_language.delete;
606 l_tab_entity_name.delete;
607 l_tab_pk1_value.delete;
608 l_tab_pk2_value.delete;
609 l_tab_seq_num.delete;
610 l_tab_upd_user.delete;
611 l_tab_category.delete;
612 l_tab_usage_type.delete;
613 l_tab_data_type.delete;
614 l_tab_datatype_id.delete;
615 l_tab_document_id.delete;
616 l_tab_title.delete;
617
618 --If the max size of attachment is less than 1, then exit.
619 IF (TO_NUMBER(FND_PROFILE.Value('CSM_MAX_ATTACHMENT_SIZE')) < 1) THEN
620 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
621 jtm_message_log_pkg.Log_Msg
622 ( 0
623 , g_table_name
624 , 'Leaving INSERT_ALL_ACC_RECORDS - Max att download size is less than 1'
625 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
629 x_return_status := FND_API.G_RET_STS_SUCCESS;
626 );
627 END IF;
628
630 RETURN;
631 END IF;
632
633 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
634 jtm_message_log_pkg.Log_Msg
635 ( 0
636 , g_table_name
637 , 'Downloading Attachments for SRs'
638 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
639 );
640 END IF;
641
642 OPEN c_SRAtt(p_user_id);
643 FETCH c_SRAtt BULK COLLECT INTO l_tab_access_id, l_tab_file_id, l_tab_user_id, l_tab_name,
644 l_tab_description, l_tab_language, l_tab_entity_name, l_tab_pk1_value,
645 l_tab_pk2_value, l_tab_seq_num,l_tab_upd_user,l_tab_category,l_tab_usage_type,l_tab_data_type,
646 l_tab_datatype_id,l_tab_document_id,l_tab_title;
647 CLOSE c_SRAtt;
648
649 IF l_tab_access_id.COUNT > 0 THEN
650 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
651 jtm_message_log_pkg.Log_Msg
652 ( 0
653 , g_table_name
654 , 'Pushing ' || l_tab_access_id.COUNT || ' updated record(s)'
655 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
656 );
657 END IF;
658
659 FORALL i in l_tab_access_id.FIRST .. l_tab_access_id.LAST
660 INSERT INTO CSM_FND_LOBS_ACC
661 (access_id, file_id, user_id, name, description,
662 language, entity_name, pk1_value, pk2_value, seq_num,
663 update_user, category, usage_type, data_type, counter,
664 last_update_date, last_updated_by, creation_date,created_by, data_type_id,
665 document_id,title)
666 VALUES (l_tab_access_id(i), l_tab_file_id(i), l_tab_user_id(i), l_tab_name(i), l_tab_description(i),
667 l_tab_language(i), l_tab_entity_name(i), l_tab_pk1_value(i), l_tab_pk2_value(i), l_tab_seq_num(i),
668 l_tab_upd_user(i), l_tab_category(i), l_tab_usage_type(i),l_tab_data_type(i), 1,
669 sysdate, 1, sysdate, 1, l_tab_datatype_id(i),
670 l_tab_document_id(i),l_tab_title(i));
671
672
673 l_dummy := asg_download.mark_dirty(
674 P_PUB_ITEM => l_publicationitemname
675 , P_ACCESSLIST => l_tab_access_id
676 , P_USERID_LIST => l_tab_user_id
677 , P_DML_TYPE => 'I'
678 , P_TIMESTAMP => SYSDATE);
679
680 END IF;
681
682 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
683 jtm_message_log_pkg.Log_Msg
684 ( 0
685 , g_table_name
686 , 'Downloading Attachments for Tasks'
687 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
688 );
689 END IF;
690
691 --TASK
692 l_tab_access_id.delete;
693 l_tab_user_id.delete;
694 l_tab_file_id.delete;
695 l_tab_name.delete;
696 l_tab_description.delete;
697 l_tab_language.delete;
698 l_tab_entity_name.delete;
699 l_tab_pk1_value.delete;
700 l_tab_pk2_value.delete;
701 l_tab_seq_num.delete;
702 l_tab_upd_user.delete;
703 l_tab_category.delete;
704 l_tab_usage_type.delete;
705 l_tab_data_type.delete;
706 l_tab_datatype_id.delete;
707 l_tab_document_id.delete;
708 l_tab_title.delete;
709
710 OPEN c_TaskAtt(p_user_id);
711 FETCH c_TaskAtt BULK COLLECT INTO l_tab_access_id, l_tab_file_id, l_tab_user_id, l_tab_name,
712 l_tab_description, l_tab_language, l_tab_entity_name, l_tab_pk1_value,
713 l_tab_pk2_value, l_tab_seq_num,l_tab_upd_user,l_tab_category,l_tab_usage_type,l_tab_data_type,
714 l_tab_datatype_id,l_tab_document_id,l_tab_title;
715 CLOSE c_TaskAtt;
716
717 IF l_tab_access_id.COUNT > 0 THEN
718 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
719 jtm_message_log_pkg.Log_Msg
720 ( 0
721 , g_table_name
722 , 'Pushing ' || l_tab_access_id.COUNT || ' updated record(s)'
723 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
724 );
725 END IF;
726
727 FORALL i in l_tab_access_id.FIRST .. l_tab_access_id.LAST
728 INSERT INTO CSM_FND_LOBS_ACC
729 (access_id, file_id, user_id, name, description,
730 language, entity_name, pk1_value, pk2_value, seq_num,
731 update_user, category, usage_type, data_type, counter,
732 last_update_date, last_updated_by, creation_date,created_by, data_type_id,
733 document_id,title)
734 VALUES (l_tab_access_id(i), l_tab_file_id(i), l_tab_user_id(i), l_tab_name(i), l_tab_description(i),
735 l_tab_language(i), l_tab_entity_name(i), l_tab_pk1_value(i), l_tab_pk2_value(i), l_tab_seq_num(i),
736 l_tab_upd_user(i), l_tab_category(i), l_tab_usage_type(i),l_tab_data_type(i), 1,
737 sysdate, 1, sysdate, 1, l_tab_datatype_id(i),
738 l_tab_document_id(i),l_tab_title(i));
739
740
741 l_dummy := asg_download.mark_dirty(
742 P_PUB_ITEM => l_publicationitemname
743 , P_ACCESSLIST => l_tab_access_id
744 , P_USERID_LIST => l_tab_user_id
745 , P_DML_TYPE => 'I'
746 , P_TIMESTAMP => SYSDATE);
747
748 END IF;
749
750
751 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
752 jtm_message_log_pkg.Log_Msg
756 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
753 ( 0
754 , g_table_name
755 , 'Downloading Signatures'
757 );
758 END IF;
759
760
761 x_return_status := FND_API.G_RET_STS_SUCCESS;
762
763 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
764 jtm_message_log_pkg.Log_Msg
765 ( 0
766 , g_table_name
767 , 'Leaving INSERT_ALL_ACC_RECORDS'
768 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
769 );
770 END IF;
771
772 EXCEPTION
773
774 WHEN OTHERS THEN
775 x_return_status := FND_API.G_RET_STS_ERROR;
776 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
777 jtm_message_log_pkg.Log_Msg
778 ( 0
779 , g_table_name
780 , 'INSERT_ALL_ACC_RECORDS'||fnd_global.local_chr(10)||
781 'Error: '||sqlerrm
782 , JTM_HOOK_UTIL_PKG.g_debug_level_error);
783 END IF;
784 RAISE;
785 END INSERT_ALL_ACC_RECORDS;
786
787 --Bug 4938130
788 PROCEDURE INSERT_ACC_RECORD(p_task_assignment_id IN NUMBER, p_user_id IN NUMBER)
789 IS
790
791 CURSOR l_signature_csr(b_task_assignment_id IN NUMBER, b_user_id IN NUMBER,
792 b_lang IN VARCHAR)
793 IS
794 SELECT csm_fnd_lobs_acc_s.NEXTVAL, fnddoc.media_id, acc.user_id,
795 fnddoccat_tl.name, fnddoc_tl.description, fnddoc_tl.language,
796 fndatt.entity_name, fndatt.pk1_value, fndatt.pk2_value,
797 fndatt.seq_num,asg.user_name,fnddoccat_tl.user_name,
798 fnddoc.usage_type,doctype.user_name,fnddoc.datatype_id,
799 fnddoc.document_id,fnddoc_tl.title
800 FROM csf_debrief_headers dbfhdr, fnd_attached_documents fndatt,
801 fnd_documents_tl fnddoc_tl, fnd_documents fnddoc,
802 -- fnd_lobs lobs,
803 fnd_document_categories_tl fnddoccat_tl,
804 csm_debrief_headers_acc acc,asg_user asg,
805 FND_DOCUMENT_DATATYPES doctype
806 WHERE dbfhdr.task_assignment_id = b_task_assignment_id
807 AND acc.debrief_header_id = dbfhdr.debrief_header_id
808 AND acc.user_id = b_user_id
809 AND asg.user_id = b_user_id
810 AND fndatt.entity_name = 'CSF_DEBRIEF_HEADERS'
811 AND fndatt.pk1_value = dbfhdr.debrief_header_id
812 AND fndatt.document_id = fnddoc_tl.document_id
813 AND fnddoc_tl.document_id = fnddoc.document_id
814 AND fnddoc_tl.language = b_lang
815 --AND fnddoc.media_id = lobs.file_id
816 AND fnddoccat_tl.category_id = fnddoc.category_id
817 AND fnddoccat_tl.language = b_lang
818 AND fnddoccat_tl.name = 'SIGNATURE'
819 AND fnddoc.datatype_id = 6
820 AND doctype.datatype_id = fnddoc.datatype_id
821 AND doctype.language = asg.language
822 AND NOT EXISTS (SELECT 1 FROM CSM_FND_LOBS_ACC cflacc
823 WHERE cflacc.document_id = fnddoc.document_id
824 AND cflacc.user_id = acc.user_id);
825
826 -- get SR attachments
827 CURSOR l_SRAtt_csr (b_task_assignment_id IN NUMBER, b_user_id IN NUMBER,
828 b_lang VARCHAR)
829 IS
830 SELECT csm_fnd_lobs_acc_s.NEXTVAL, fnddoc.media_id, acc.user_id,
831 fnddoccat_tl.name, fnddoc_tl.description, fnddoc_tl.language,
832 fndattdoc.entity_name, fndattdoc.pk1_value, fndattdoc.pk2_value,
833 fndattdoc.seq_num,asg.user_name,fnddoccat_tl.user_name,
834 fnddoc.usage_type,doctype.user_name,fnddoc.datatype_id,
835 fnddoc.document_id,fnddoc_tl.title
836 FROM jtf_task_assignments jta, jtf_tasks_b jt,
837 fnd_documents_tl fnddoc_tl, fnd_documents fnddoc,
838 fnd_document_categories_tl fnddoccat_tl,
839 fnd_attached_documents fndattdoc,
840 -- fnd_lobs lobs,
841 csm_incidents_all_acc acc,asg_user asg,
842 FND_DOCUMENT_DATATYPES doctype
843 WHERE jta.task_assignment_id = b_task_assignment_id
844 AND jta.task_id = jt.task_id
845 AND jt.source_object_id = acc.incident_id
846 AND jt.source_object_type_code = 'SR'
847 AND fndattdoc.document_id = fnddoc_tl.document_id
848 AND fnddoc_tl.language = b_lang
849 AND fnddoc_tl.document_id = fnddoc.document_id
850 --AND fnddoc.media_id = lobs.file_id (+)
851 AND fnddoccat_tl.category_id = fnddoc.category_id
852 AND fnddoccat_tl.language = b_lang
853 AND fndattdoc.pk1_value = to_char(acc.incident_id)
854 AND acc.user_id = b_user_id
855 AND asg.user_id = b_user_id
856 AND fnddoccat_tl.name = 'MISC'
857 AND fndattdoc.entity_name = 'CS_INCIDENTS'
858 AND fnddoc.datatype_id in (1,5,6)
859 AND doctype.datatype_id = fnddoc.datatype_id
860 AND doctype.language = asg.language
861 AND NOT EXISTS (SELECT 1 FROM CSM_FND_LOBS_ACC cflacc
862 WHERE cflacc.document_id = fnddoc.document_id
863 AND cflacc.user_id = acc.user_id);
864
865 --get the task attachemnts
866 CURSOR l_TaskAtt_csr (b_task_assignment_id IN NUMBER, b_user_id IN NUMBER,
867 b_lang IN VARCHAR)
868 IS
869 SELECT csm_fnd_lobs_acc_s.NEXTVAL, fnddoc.media_id, acc.user_id,
870 fnddoccat_tl.name, fnddoc_tl.description, fnddoc_tl.language,
871 fndattdoc.entity_name, fndattdoc.pk1_value, fndattdoc.pk2_value,
872 fndattdoc.seq_num,asg.user_name,fnddoccat_tl.user_name,
873 fnddoc.usage_type,doctype.user_name,fnddoc.datatype_id,
874 fnddoc.document_id,fnddoc_tl.title
875 FROM jtf_task_assignments jta,
879 -- fnd_lobs lobs,
876 fnd_documents_tl fnddoc_tl, fnd_documents fnddoc,
877 fnd_document_categories_tl fnddoccat_tl,
878 fnd_attached_documents fndattdoc,
880 csm_tasks_acc acc,asg_user asg,
881 FND_DOCUMENT_DATATYPES doctype
882 WHERE jta.task_assignment_id = b_task_assignment_id
883 AND acc.task_id = jta.task_id
884 AND fndattdoc.document_id = fnddoc_tl.document_id
885 AND fnddoc_tl.language = b_lang
886 AND fnddoc_tl.document_id = fnddoc.document_id
887 --AND fnddoc.media_id = lobs.file_id (+)
888 AND fnddoccat_tl.category_id = fnddoc.category_id
889 AND fnddoccat_tl.language = b_lang
890 AND fndattdoc.pk1_value = to_char(acc.task_id)
891 AND acc.user_id = b_user_id
892 AND asg.user_id = b_user_id
893 AND fnddoccat_tl.name = 'MISC'
894 AND fndattdoc.entity_name = 'JTF_TASKS_B'
895 AND fnddoc.datatype_id in (1,5,6)
896 AND doctype.datatype_id = fnddoc.datatype_id
897 AND doctype.language = asg.language
898 AND NOT EXISTS (SELECT 1 FROM CSM_FND_LOBS_ACC cflacc
899 WHERE cflacc.document_id = fnddoc.document_id
900 AND cflacc.user_id = acc.user_id);
901
902 CURSOR l_user_csr(b_user_id IN NUMBER)
903 IS
904 SELECT LANGUAGE
905 FROM asg_user
906 WHERE user_id=b_user_id;
907
908 l_sqlerrno VARCHAR2(20);
909 l_sqlerrmsg VARCHAR2(4000);
910 l_error_msg VARCHAR2(4000);
911 l_return_status VARCHAR2(2000);
912
913 l_tab_access_id ASG_DOWNLOAD.ACCESS_LIST;
914 l_tab_user_id ASG_DOWNLOAD.USER_LIST;
915 l_publicationitemname VARCHAR2(50):= 'CSF_M_LOBS';
916 l_tab_file_id Number_TAB;
917 l_tab_seq_num Number_TAB;
918 l_tab_name Varchar2_TAB;
919 l_tab_description Varchar2_TAB;
920 l_tab_language Varchar2_TAB;
921 l_tab_entity_name Varchar2_TAB;
922 l_tab_pk1_value Varchar2_TAB;
923 l_tab_pk2_value Varchar2_TAB;
924 l_tab_upd_user Varchar2_TAB;
925 l_tab_category Varchar2_TAB;
926 l_tab_usage_type Varchar2_TAB;
927 l_tab_data_type Varchar2_TAB;
928 l_tab_datatype_id Number_TAB;
929 l_tab_document_id Number_TAB;
930 l_tab_title Varchar2_TAB;
931
932 l_dummy BOOLEAN;
933 g_debug_level NUMBER;
934 l_excep_markdirty_failed EXCEPTION;
935 l_language_user VARCHAR(10);
936
937 BEGIN
938 CSM_UTIL_PKG.LOG('Entering INSERT_ACC_RECORD for task_assignment_id: ' || p_task_assignment_id,
939 'CSM_LOBS_EVENT_PKG.INSERT_ACC_RECORD',FND_LOG.LEVEL_PROCEDURE);
940
941 l_publicationitemname := 'CSF_M_LOBS';
942
943
944 OPEN l_user_csr(p_user_id);
945 FETCH l_user_csr into l_language_user;
946 CLOSE l_user_csr;
947
948
949 ---DOWNLOAD SR AND TASK Attachments
950 /*** get debug level ***/
951 g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
952 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
953 jtm_message_log_pkg.Log_Msg
954 ( 0
955 , g_table_name
956 , 'Entering CSM_LOBS_EVENT_PKG.INSERT_ACC_RECORD'
957 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
958 );
959 END IF;
960
961 --Emptying all lists.
962 l_tab_access_id.delete;
963 l_tab_user_id.delete;
964 l_tab_file_id.delete;
965 l_tab_name.delete;
966 l_tab_description.delete;
967 l_tab_language.delete;
968 l_tab_entity_name.delete;
969 l_tab_pk1_value.delete;
970 l_tab_pk2_value.delete;
971 l_tab_seq_num.delete;
972 l_tab_upd_user.delete;
973 l_tab_category.delete;
974 l_tab_usage_type.delete;
975 l_tab_data_type.delete;
976 l_tab_datatype_id.delete;
977 l_tab_document_id.delete;
978 l_tab_title.delete;
979
980 --- ATTACHMENT DOWNLOAD FOR SIGNATURE
981 OPEN l_signature_csr(p_task_assignment_id, p_user_id,l_language_user);
982 FETCH l_signature_csr BULK COLLECT INTO l_tab_access_id, l_tab_file_id, l_tab_user_id, l_tab_name,
983 l_tab_description, l_tab_language, l_tab_entity_name, l_tab_pk1_value,
984 l_tab_pk2_value, l_tab_seq_num,l_tab_upd_user,l_tab_category,l_tab_usage_type,l_tab_data_type,
985 l_tab_datatype_id,l_tab_document_id,l_tab_title;
986 CLOSE l_signature_csr;
987
988 IF l_tab_access_id.COUNT > 0 THEN
989 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
990 jtm_message_log_pkg.Log_Msg
991 ( 0
992 , g_table_name
993 , 'Pushing ' || l_tab_access_id.COUNT || ' updated record(s)'
994 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
995 );
996 END IF;
997
998 FORALL i in l_tab_access_id.FIRST .. l_tab_access_id.LAST
999 INSERT INTO CSM_FND_LOBS_ACC
1000 (access_id, file_id, user_id, name, description,
1001 language, entity_name, pk1_value, pk2_value, seq_num,
1002 update_user, category, usage_type, data_type, counter,
1003 last_update_date, last_updated_by, creation_date,created_by, data_type_id,
1004 document_id,title)
1005 VALUES (l_tab_access_id(i), l_tab_file_id(i), l_tab_user_id(i), l_tab_name(i), l_tab_description(i),
1006 l_tab_language(i), l_tab_entity_name(i), l_tab_pk1_value(i), l_tab_pk2_value(i), l_tab_seq_num(i),
1007 l_tab_upd_user(i), l_tab_category(i), l_tab_usage_type(i),l_tab_data_type(i), 1,
1008 sysdate, 1, sysdate, 1, l_tab_datatype_id(i),
1009 l_tab_document_id(i),l_tab_title(i));
1010
1011 l_dummy := asg_download.mark_dirty(
1012 P_PUB_ITEM => l_publicationitemname
1013 , P_ACCESSLIST => l_tab_access_id
1014 , P_USERID_LIST => l_tab_user_id
1015 , P_DML_TYPE => 'I'
1016 , P_TIMESTAMP => SYSDATE);
1017
1018 COMMIT;
1019
1020 END IF;
1021
1022 --If the max size of attachment is less than 1, then exit.(this should be done only
1023 --for SR and TASK attachments)
1024 IF (TO_NUMBER(FND_PROFILE.Value('CSM_MAX_ATTACHMENT_SIZE')) < 1) THEN
1025 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
1026 jtm_message_log_pkg.Log_Msg
1027 ( 0
1028 , g_table_name
1029 , 'Leaving CSM_LOBS_EVENT_PKG.INSERT_ACC_RECORD - Max att download size is less than 1'
1030 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
1031 );
1032 END IF;
1033 RETURN;
1034 END IF;
1035
1036
1037 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
1038 jtm_message_log_pkg.Log_Msg
1039 ( 0
1040 , g_table_name
1041 , 'Downloading Attachments for SRs'
1042 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
1043 );
1044 END IF;
1045
1046 --Emptying all lists.
1047 l_tab_access_id.delete;
1048 l_tab_user_id.delete;
1049 l_tab_file_id.delete;
1050 l_tab_name.delete;
1051 l_tab_description.delete;
1052 l_tab_language.delete;
1053 l_tab_entity_name.delete;
1054 l_tab_pk1_value.delete;
1055 l_tab_pk2_value.delete;
1056 l_tab_seq_num.delete;
1057 l_tab_upd_user.delete;
1058 l_tab_category.delete;
1059 l_tab_usage_type.delete;
1060 l_tab_data_type.delete;
1061 l_tab_datatype_id.delete;
1062 l_tab_document_id.delete;
1063 l_tab_title.delete;
1064
1065 --- ATTACHMENT DOWNLOAD FOR SR
1066 OPEN l_SRAtt_csr(p_task_assignment_id, p_user_id,l_language_user);
1067 FETCH l_SRAtt_csr BULK COLLECT INTO l_tab_access_id, l_tab_file_id, l_tab_user_id, l_tab_name,
1068 l_tab_description, l_tab_language, l_tab_entity_name, l_tab_pk1_value,
1069 l_tab_pk2_value, l_tab_seq_num,l_tab_upd_user,l_tab_category,l_tab_usage_type,l_tab_data_type,
1070 l_tab_datatype_id,l_tab_document_id,l_tab_title;
1071 CLOSE l_SRAtt_csr;
1072
1073 IF l_tab_access_id.COUNT > 0 THEN
1074 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
1075 jtm_message_log_pkg.Log_Msg
1076 ( 0
1077 , g_table_name
1078 , 'Pushing ' || l_tab_access_id.COUNT || ' updated record(s)'
1079 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
1080 );
1081 END IF;
1082
1083 FORALL i in l_tab_access_id.FIRST .. l_tab_access_id.LAST
1084 INSERT INTO CSM_FND_LOBS_ACC
1085 (access_id, file_id, user_id, name, description,
1086 language, entity_name, pk1_value, pk2_value, seq_num,
1087 update_user, category, usage_type, data_type, counter,
1088 last_update_date, last_updated_by, creation_date,created_by, data_type_id,
1089 document_id,title)
1090 VALUES (l_tab_access_id(i), l_tab_file_id(i), l_tab_user_id(i), l_tab_name(i), l_tab_description(i),
1091 l_tab_language(i), l_tab_entity_name(i), l_tab_pk1_value(i), l_tab_pk2_value(i), l_tab_seq_num(i),
1092 l_tab_upd_user(i), l_tab_category(i), l_tab_usage_type(i),l_tab_data_type(i), 1,
1093 sysdate, 1, sysdate, 1, l_tab_datatype_id(i),
1094 l_tab_document_id(i),l_tab_title(i));
1095
1096
1097 l_dummy := asg_download.mark_dirty(
1098 P_PUB_ITEM => l_publicationitemname
1099 , P_ACCESSLIST => l_tab_access_id
1100 , P_USERID_LIST => l_tab_user_id
1101 , P_DML_TYPE => 'I'
1102 , P_TIMESTAMP => SYSDATE);
1103
1104 COMMIT;
1105
1106 END IF;
1107
1108 --Emptying all lists.
1109 l_tab_access_id.delete;
1110 l_tab_user_id.delete;
1111 l_tab_file_id.delete;
1112 l_tab_name.delete;
1113 l_tab_description.delete;
1114 l_tab_language.delete;
1115 l_tab_entity_name.delete;
1116 l_tab_pk1_value.delete;
1117 l_tab_pk2_value.delete;
1118 l_tab_seq_num.delete;
1119 l_tab_upd_user.delete;
1120 l_tab_category.delete;
1121 l_tab_usage_type.delete;
1122 l_tab_data_type.delete;
1123 l_tab_datatype_id.delete;
1124 l_tab_document_id.delete;
1125 l_tab_title.delete;
1126
1127 --- ATTACHMENT DOWNLOAD FOR TASK
1128 OPEN l_TaskAtt_csr(p_task_assignment_id, p_user_id,l_language_user);
1129 FETCH l_TaskAtt_csr BULK COLLECT INTO l_tab_access_id, l_tab_file_id, l_tab_user_id, l_tab_name,
1130 l_tab_description, l_tab_language, l_tab_entity_name, l_tab_pk1_value,
1131 l_tab_pk2_value, l_tab_seq_num,l_tab_upd_user,l_tab_category,l_tab_usage_type,l_tab_data_type,
1132 l_tab_datatype_id,l_tab_document_id,l_tab_title;
1133 CLOSE l_TaskAtt_csr;
1134
1135 IF l_tab_access_id.COUNT > 0 THEN
1136 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
1137 jtm_message_log_pkg.Log_Msg
1138 ( 0
1139 , g_table_name
1140 , 'Pushing ' || l_tab_access_id.COUNT || ' updated record(s)'
1141 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
1142 );
1143 END IF;
1144
1145 FORALL i in l_tab_access_id.FIRST .. l_tab_access_id.LAST
1146 INSERT INTO CSM_FND_LOBS_ACC
1147 (access_id, file_id, user_id, name, description,
1148 language, entity_name, pk1_value, pk2_value, seq_num,
1149 update_user, category, usage_type, data_type, counter,
1150 last_update_date, last_updated_by, creation_date,created_by, data_type_id,
1151 document_id,title)
1152 VALUES (l_tab_access_id(i), l_tab_file_id(i), l_tab_user_id(i), l_tab_name(i), l_tab_description(i),
1153 l_tab_language(i), l_tab_entity_name(i), l_tab_pk1_value(i), l_tab_pk2_value(i), l_tab_seq_num(i),
1154 l_tab_upd_user(i), l_tab_category(i), l_tab_usage_type(i),l_tab_data_type(i), 1,
1155 sysdate, 1, sysdate, 1, l_tab_datatype_id(i),
1156 l_tab_document_id(i),l_tab_title(i));
1157
1158
1159 l_dummy := asg_download.mark_dirty(
1160 P_PUB_ITEM => l_publicationitemname
1161 , P_ACCESSLIST => l_tab_access_id
1162 , P_USERID_LIST => l_tab_user_id
1163 , P_DML_TYPE => 'I'
1164 , P_TIMESTAMP => SYSDATE);
1165
1166 COMMIT;
1167
1168 END IF;
1169
1170 CSM_UTIL_PKG.LOG('Leaving INSERT_ACC_RECORD for task_assignment_id: ' || p_task_assignment_id,
1171 'CSM_LOBS_EVENT_PKG.INSERT_ACC_RECORD',FND_LOG.LEVEL_PROCEDURE);
1172 EXCEPTION
1173 WHEN l_excep_markdirty_failed THEN
1174 l_error_msg := ' FAILED MarkDirty in INSERT_ACC_RECORD for task_assignment_id:' || to_char(p_task_assignment_id);
1175 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_LOBS_EVENT_PKG.INSERT_ACC_RECORD',FND_LOG.LEVEL_EXCEPTION);
1176 RAISE;
1177
1178 WHEN OTHERS THEN
1179 l_sqlerrno := to_char(SQLCODE);
1180 l_sqlerrmsg := substr(SQLERRM, 1,2000);
1181 l_error_msg := ' Exception in INSERT_ACC_RECORD for task_assignment_id:'
1182 || to_char(p_task_assignment_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
1183 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_LOBS_EVENT_PKG.INSERT_ACC_RECORD',FND_LOG.LEVEL_EXCEPTION);
1184 RAISE;
1185 END INSERT_ACC_RECORD;
1186
1187 --Bug 4938130
1188 PROCEDURE DELETE_ACC_RECORD(p_task_assignment_id IN NUMBER, p_resource_id IN NUMBER)
1189 IS
1190 --Cursor for signature
1191 CURSOR l_signature_csr(b_task_assignment_id IN number, b_user_id IN number)
1192 IS
1193 SELECT acc.access_id, acc.user_id
1194 FROM csm_fnd_lobs_acc acc,
1195 csf_debrief_headers dbh
1196 WHERE acc.user_id = b_user_id
1197 AND acc.entity_name = 'CSF_DEBRIEF_HEADERS'
1198 AND dbh.task_assignment_id = b_task_assignment_id
1199 AND acc.pk1_value = to_char(dbh.debrief_header_id);
1200
1201 --Cursor for SR attachments
1202 CURSOR c_SRAtt ( b_task_assignment_id IN NUMBER,
1203 b_user_id IN NUMBER) IS
1204 SELECT acc.access_id, acc.user_id
1205 FROM csm_fnd_lobs_acc acc,
1206 jtf_task_assignments jta,
1210 AND jt.task_id = jta.task_id
1207 jtf_tasks_b jt
1208 WHERE acc.user_id = b_user_id
1209 AND acc.entity_name = 'CS_INCIDENTS'
1211 AND jta.task_assignment_id = b_task_assignment_id
1212 AND acc.pk1_value = to_char(jt.source_object_id);
1213
1214 --Cursor for Task attachments
1215 CURSOR l_TaskAtt_csr ( b_task_assignment_id IN NUMBER,
1216 b_user_id IN NUMBER) IS
1217 SELECT acc.access_id, acc.user_id
1218 FROM csm_fnd_lobs_acc acc,
1219 jtf_task_assignments jta
1220 WHERE acc.user_id = b_user_id
1221 AND acc.entity_name = 'JTF_TASKS_B'
1222 AND jta.task_assignment_id = b_task_assignment_id
1223 AND acc.pk1_value = to_char(jta.task_id);
1224
1225 --CURSOR to get userid
1226 CURSOR l_userid_csr (b_resource_id IN NUMBER)
1227 IS
1228 SELECT user_id
1229 FROM asg_user
1230 WHERE resource_id=b_resource_id;
1231
1232 l_sqlerrno VARCHAR2(20);
1233 l_sqlerrmsg VARCHAR2(4000);
1234 l_error_msg VARCHAR2(4000);
1235 l_return_status VARCHAR2(2000);
1236
1237 l_resourcelist number;
1238 l_publicationitemname VARCHAR2(50) := 'CSF_M_LOBS';
1239 l_excep_markdirty_failed EXCEPTION;
1240 g_debug_level NUMBER;
1241 l_tab_access_id ASG_DOWNLOAD.ACCESS_LIST;
1242 l_tab_user_id ASG_DOWNLOAD.USER_LIST;
1243 l_dummy BOOLEAN;
1244 l_userid NUMBER;
1245
1246 BEGIN
1247
1248 g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
1249
1250 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
1251 jtm_message_log_pkg.Log_Msg
1252 ( p_task_assignment_id
1253 , g_table_name
1254 , 'Entering CSM_LOBS_EVENT_PKG.DELETE_ACC_RECORD'
1255 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
1256 END IF;
1257
1258 --cursor to get language
1259 OPEN l_userid_csr (p_resource_id);
1260 FETCH l_userid_csr INTO l_userid;
1261 CLOSE l_userid_csr;
1262
1263 --Deleting Signature attachments
1264
1265 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
1266 jtm_message_log_pkg.Log_Msg
1267 ( p_task_assignment_id
1268 , g_table_name
1269 , 'Deleting record for CS_INCIDENTS and resource_id: ' || p_resource_id
1270 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
1271 END IF;
1272
1273 OPEN l_signature_csr(p_task_assignment_id, l_userid);
1274 FETCH l_signature_csr BULK COLLECT INTO l_tab_access_id, l_tab_user_id;
1275 CLOSE l_signature_csr;
1276
1277 IF l_tab_access_id.COUNT > 0 THEN
1278 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
1279 jtm_message_log_pkg.Log_Msg
1280 ( 0
1281 , g_table_name
1282 , 'Pushing ' || l_tab_access_id.COUNT || ' updated record(s)'
1283 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
1284 );
1285 END IF;
1286
1287 FORALL i in l_tab_access_id.FIRST .. l_tab_access_id.LAST
1288 DELETE FROM CSM_FND_LOBS_ACC WHERE ACCESS_ID = l_tab_access_id(i);
1289
1290 l_dummy := asg_download.mark_dirty(
1291 P_PUB_ITEM => l_publicationitemname
1292 , P_ACCESSLIST => l_tab_access_id
1293 , P_USERID_LIST => l_tab_user_id
1294 , P_DML_TYPE => 'D'
1295 , P_TIMESTAMP => SYSDATE);
1296
1297 COMMIT;
1298
1299 END IF;
1300
1301 --SR attachment deletion
1302 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
1303 jtm_message_log_pkg.Log_Msg
1304 ( p_task_assignment_id
1305 , g_table_name
1306 , 'Deleting record for CS_INCIDENTS and resource_id: ' || p_resource_id
1307 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
1308 END IF;
1309
1310 OPEN c_SRAtt(p_task_assignment_id, l_userid);
1311 FETCH c_SRAtt BULK COLLECT INTO l_tab_access_id, l_tab_user_id;
1312 CLOSE c_SRAtt;
1313
1314 IF l_tab_access_id.COUNT > 0 THEN
1315 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
1316 jtm_message_log_pkg.Log_Msg
1317 ( 0
1318 , g_table_name
1319 , 'Pushing ' || l_tab_access_id.COUNT || ' updated record(s)'
1320 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
1321 );
1322 END IF;
1323
1324 FORALL i in l_tab_access_id.FIRST .. l_tab_access_id.LAST
1325 DELETE FROM CSM_FND_LOBS_ACC WHERE ACCESS_ID = l_tab_access_id(i);
1326
1327 l_dummy := asg_download.mark_dirty(
1328 P_PUB_ITEM => l_publicationitemname
1329 , P_ACCESSLIST => l_tab_access_id
1330 , P_USERID_LIST => l_tab_user_id
1331 , P_DML_TYPE => 'D'
1332 , P_TIMESTAMP => SYSDATE);
1333
1334 COMMIT;
1335
1336 END IF;
1337
1338 ----------------------------------
1339 --Deletion for Tasks
1340 -----------------------------------
1341 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
1342 jtm_message_log_pkg.Log_Msg
1343 ( p_task_assignment_id
1344 , g_table_name
1345 , 'Deleting record for JTF_TASKS_B and resource_id: ' || p_resource_id
1349 OPEN l_TaskAtt_csr(p_task_assignment_id, l_userid);
1346 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
1347 END IF;
1348
1350 FETCH l_TaskAtt_csr BULK COLLECT INTO l_tab_access_id, l_tab_user_id;
1351 CLOSE l_TaskAtt_csr;
1352
1353 IF l_tab_access_id.COUNT > 0 THEN
1354 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
1355 jtm_message_log_pkg.Log_Msg
1356 ( 0
1357 , g_table_name
1358 , 'Pushing ' || l_tab_access_id.COUNT || ' updated record(s)'
1359 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
1360 );
1361 END IF;
1362
1363 FORALL i in l_tab_access_id.FIRST .. l_tab_access_id.LAST
1364 DELETE FROM CSM_FND_LOBS_ACC WHERE ACCESS_ID = l_tab_access_id(i);
1365
1366 l_dummy := asg_download.mark_dirty(
1367 P_PUB_ITEM => l_publicationitemname
1368 , P_ACCESSLIST => l_tab_access_id
1369 , P_USERID_LIST => l_tab_user_id
1370 , P_DML_TYPE => 'D'
1371 , P_TIMESTAMP => SYSDATE);
1372
1373 COMMIT;
1374
1375 END IF;
1376
1377 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
1378 jtm_message_log_pkg.Log_Msg
1379 ( p_task_assignment_id
1380 , g_table_name
1381 , 'Exiting CSM_LOBS_EVENT_PKG.DELETE_ACC_RECORD'
1382 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
1383 END IF;
1384
1385 CSM_UTIL_PKG.LOG('Leaving DELETE_ACC_RECORD for task_assignment_id: ' || p_task_assignment_id,
1386 'CSM_LOBS_EVENT_PKG.DELETE_ACC_RECORD',FND_LOG.LEVEL_PROCEDURE);
1387 EXCEPTION
1388 WHEN l_excep_markdirty_failed THEN
1389 l_error_msg := ' FAILED MarkDirty in DELETE_ACC_RECORD for task_assignment_id:' || to_char(p_task_assignment_id);
1390 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_LOBS_EVENT_PKG.DELETE_ACC_RECORD',FND_LOG.LEVEL_EXCEPTION);
1391 RAISE;
1392
1393 WHEN OTHERS THEN
1394 l_sqlerrno := to_char(SQLCODE);
1395 l_sqlerrmsg := substr(SQLERRM, 1,2000);
1396 l_error_msg := ' Exception in DELETE_ACC_RECORD for task_assignment_id:'
1397 || to_char(p_task_assignment_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
1398 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_LOBS_EVENT_PKG.DELETE_ACC_RECORD',FND_LOG.LEVEL_EXCEPTION);
1399 RAISE;
1400 END DELETE_ACC_RECORD;
1401
1402
1403 PROCEDURE INSERT_ACC_ON_UPLOAD(p_PK1_value IN NUMBER, p_user_id IN NUMBER,
1404 p_entity_name IN VARCHAR, p_data_typeid IN NUMBER, p_dodirty BOOLEAN)
1405 IS
1406 -- get SR attachments
1407 --p_dodirty is used only for lobs attachments(6).if its sets to true then mark dirty has to be done.The reason
1408 --for this is the file id sent from client may not be proper and so we allow the server to generate the file id
1409 CURSOR l_SRAtt_csr (b_incident_id IN NUMBER, b_user_id IN NUMBER,
1410 b_data_typeid IN NUMBER)
1411 IS
1412 SELECT csm_fnd_lobs_acc_s.NEXTVAL, fnddoc.media_id, acc.user_id,
1413 fnddoccat_tl.name, fnddoc_tl.description, fnddoc_tl.language,
1414 fndattdoc.entity_name, fndattdoc.pk1_value, fndattdoc.pk2_value,
1415 fndattdoc.seq_num, asg.user_name, fnddoccat_tl.user_name,
1416 fnddoc.usage_type, doctype.user_name, fnddoc.datatype_id,
1417 fnddoc.document_id, fnddoc_tl.title
1418 FROM fnd_documents_tl fnddoc_tl,
1419 fnd_documents fnddoc,
1420 fnd_document_categories_tl fnddoccat_tl,
1421 fnd_attached_documents fndattdoc,
1422 csm_incidents_all_acc acc,
1423 asg_user asg,
1424 FND_DOCUMENT_DATATYPES doctype
1425 WHERE fndattdoc.document_id = fnddoc_tl.document_id
1426 AND fnddoc_tl.language = asg.language
1427 AND fnddoc_tl.document_id = fnddoc.document_id
1428 AND fnddoccat_tl.category_id = fnddoc.category_id
1429 AND fnddoccat_tl.language = asg.language
1430 AND fndattdoc.pk1_value = to_char(acc.incident_id)
1431 AND fndattdoc.pk1_value = to_char(b_incident_id)
1432 AND asg.user_id = acc.user_id
1433 AND fnddoccat_tl.name = 'MISC'
1434 AND fndattdoc.entity_name = 'CS_INCIDENTS'
1435 AND fnddoc.datatype_id = b_data_typeid
1436 AND doctype.datatype_id = fnddoc.datatype_id
1437 AND doctype.language = asg.language
1438 AND acc.user_id = b_user_id
1439 AND NOT EXISTS (SELECT 1
1440 FROM CSM_FND_LOBS_ACC cflacc
1441 WHERE cflacc.document_id = fnddoc.document_id
1442 AND cflacc.user_id = acc.user_id);
1443
1444 --Cursor to get the task attachemnts
1445 CURSOR l_TaskAtt_csr(b_task_id IN NUMBER, b_user_id IN NUMBER,
1446 b_data_typeid IN NUMBER)
1447 IS
1448 SELECT csm_fnd_lobs_acc_s.NEXTVAL, fnddoc.media_id, acc.user_id,
1449 fnddoccat_tl.name, fnddoc_tl.description, fnddoc_tl.language,
1450 fndattdoc.entity_name, fndattdoc.pk1_value, fndattdoc.pk2_value,
1451 fndattdoc.seq_num, asg.user_name, fnddoccat_tl.user_name,
1452 fnddoc.usage_type, doctype.user_name, fnddoc.datatype_id,
1453 fnddoc.document_id, fnddoc_tl.title
1454 FROM fnd_documents_tl fnddoc_tl,
1455 fnd_documents fnddoc,
1456 fnd_document_categories_tl fnddoccat_tl,
1457 fnd_attached_documents fndattdoc,
1458 csm_tasks_acc acc,
1459 asg_user asg,
1460 FND_DOCUMENT_DATATYPES doctype
1461 WHERE fndattdoc.document_id = fnddoc_tl.document_id
1462 AND fnddoc_tl.language = asg.language
1463 AND fnddoc_tl.document_id = fnddoc.document_id
1464 AND fnddoccat_tl.category_id = fnddoc.category_id
1465 AND fnddoccat_tl.language = asg.language
1466 AND fndattdoc.pk1_value = to_char(acc.task_id)
1467 AND fndattdoc.pk1_value = to_char(b_task_id)
1468 AND asg.user_id = acc.user_id
1469 AND fnddoccat_tl.name = 'MISC'
1470 AND fndattdoc.entity_name = 'JTF_TASKS_B'
1471 AND fnddoc.datatype_id = b_data_typeid
1472 AND doctype.datatype_id = fnddoc.datatype_id
1473 AND doctype.language = asg.language
1474 AND acc.user_id = b_user_id
1475 AND NOT EXISTS (SELECT 1
1476 FROM CSM_FND_LOBS_ACC cflacc
1477 WHERE cflacc.document_id = fnddoc.document_id
1478 AND cflacc.user_id = acc.user_id);
1479
1480 --Bug 5726888
1481 --Cursor to get the Signature attachemnts
1482 CURSOR c_Signature (b_debrief_id IN NUMBER, b_user_id IN NUMBER,
1483 b_data_typeid IN NUMBER) IS
1484 SELECT csm_fnd_lobs_acc_s.NEXTVAL, fnddoc.media_id, acc.user_id,
1485 fnddoccat_tl.name, fnddoc_tl.description, fnddoc_tl.language,
1486 fndattdoc.entity_name, fndattdoc.pk1_value, fndattdoc.pk2_value,
1487 fndattdoc.seq_num,asg.user_name,fnddoccat_tl.user_name,
1488 fnddoc.usage_type,doctype.user_name,fnddoc.datatype_id,
1492 fnd_attached_documents fndattdoc,
1489 fnddoc.document_id,fnddoc_tl.title
1490 FROM fnd_documents_tl fnddoc_tl, fnd_documents fnddoc,
1491 fnd_document_categories_tl fnddoccat_tl,
1493 csm_debrief_headers_acc acc, asg_user asg,
1494 FND_DOCUMENT_DATATYPES doctype
1495 WHERE fndattdoc.document_id = fnddoc_tl.document_id
1496 AND fnddoc_tl.language = asg.language
1497 AND fnddoc_tl.document_id = fnddoc.document_id
1498 AND fnddoccat_tl.category_id = fnddoc.category_id
1499 AND fnddoccat_tl.language = asg.language
1500 AND fndattdoc.pk1_value = to_char(acc.debrief_header_id)
1501 AND acc.debrief_header_id = b_debrief_id
1502 AND fnddoccat_tl.name = 'SIGNATURE'
1503 AND fndattdoc.entity_name = 'CSF_DEBRIEF_HEADERS'
1504 AND fnddoc.datatype_id =b_data_typeid
1505 AND doctype.datatype_id = fnddoc.datatype_id
1506 AND doctype.language = asg.language
1507 AND acc.user_id = asg.user_id
1508 AND acc.user_id=b_user_id
1509 AND NOT EXISTS (SELECT 1 FROM CSM_FND_LOBS_ACC cflacc
1510 WHERE cflacc.document_id = fnddoc.document_id
1511 AND cflacc.user_id = acc.user_id);
1512
1513 TYPE Number_TAB IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
1514 TYPE Varchar2_TAB IS TABLE OF VARCHAR2(255) INDEX BY BINARY_INTEGER;
1515 l_sqlerrno VARCHAR2(20);
1516 l_sqlerrmsg VARCHAR2(4000);
1517 l_error_msg VARCHAR2(4000);
1518 l_return_status VARCHAR2(2000);
1519
1520 l_tab_access_id ASG_DOWNLOAD.ACCESS_LIST;
1521 l_tab_user_id ASG_DOWNLOAD.USER_LIST;
1522
1523 l_publicationitemname VARCHAR2(50):= 'CSF_M_LOBS';
1524 l_tab_file_id Number_TAB;
1525 l_tab_seq_num Number_TAB;
1526 l_tab_name Varchar2_TAB;
1527 l_tab_description Varchar2_TAB;
1528 l_tab_language Varchar2_TAB;
1529 l_tab_entity_name Varchar2_TAB;
1530 l_tab_pk1_value Varchar2_TAB;
1531 l_tab_pk2_value Varchar2_TAB;
1532 l_tab_upd_user Varchar2_TAB;
1533 l_tab_category Varchar2_TAB;
1534 l_tab_usage_type Varchar2_TAB;
1535 l_tab_data_type Varchar2_TAB;
1536 l_tab_datatype_id Number_TAB;
1537 l_tab_document_id Number_TAB;
1538 l_tab_title Varchar2_TAB;
1539
1540 l_dummy BOOLEAN;
1541 g_debug_level NUMBER;
1542
1543 BEGIN
1544 CSM_UTIL_PKG.LOG('Entering INSERT_ACC_ON_UPLOAD for task_assignment_id: ' || p_PK1_value,
1545 'CSM_LOBS_EVENT_PKG.INSERT_ACC_ON_UPLOAD',FND_LOG.LEVEL_PROCEDURE);
1546
1547 l_publicationitemname := 'CSF_M_LOBS';
1548
1549 ---DOWNLOAD SR AND TASK Attachments
1550 /*** get debug level ***/
1551 g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
1552 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
1553 jtm_message_log_pkg.Log_Msg
1554 ( 0
1555 , g_table_name
1556 , 'Entering CSM_LOBS_EVENT_PKG.INSERT_ACC_ON_UPLOAD'
1557 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
1558 );
1559 END IF;
1560 --If the max size of attachment is less than 1, then exit.(this should be done only
1561 --for SR and TASK attachments)
1562 IF (TO_NUMBER(FND_PROFILE.Value('CSM_MAX_ATTACHMENT_SIZE')) < 1) THEN
1563 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
1564 jtm_message_log_pkg.Log_Msg
1565 ( 0
1566 , g_table_name
1567 , 'Leaving CSM_LOBS_EVENT_PKG.INSERT_ACC_ON_UPLOAD - Max att download size is less than 1'
1568 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
1569 );
1570 END IF;
1571 RETURN;
1572 END IF;
1573
1574 --Emptying all lists.
1575 l_tab_access_id.delete;
1576 l_tab_user_id.delete;
1577 l_tab_file_id.delete;
1578 l_tab_name.delete;
1579 l_tab_description.delete;
1580 l_tab_language.delete;
1581 l_tab_entity_name.delete;
1582 l_tab_pk1_value.delete;
1583 l_tab_pk2_value.delete;
1584 l_tab_seq_num.delete;
1585 l_tab_upd_user.delete;
1586 l_tab_category.delete;
1587 l_tab_usage_type.delete;
1588 l_tab_data_type.delete;
1589 l_tab_datatype_id.delete;
1590 l_tab_document_id.delete;
1591 l_tab_title.delete;
1592
1593 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
1594 jtm_message_log_pkg.Log_Msg
1595 ( 0
1596 , g_table_name
1597 , 'Downloading Attachments for SRs'
1598 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
1599 );
1600 END IF;
1601
1602 IF p_entity_name = 'CS_INCIDENTS' THEN
1603 --- ATTACHMENT DOWNLOAD FOR SR
1604 OPEN l_SRAtt_csr(p_PK1_value, p_user_id,p_data_typeid);
1605 FETCH l_SRAtt_csr BULK COLLECT INTO l_tab_access_id, l_tab_file_id, l_tab_user_id, l_tab_name,
1606 l_tab_description, l_tab_language, l_tab_entity_name, l_tab_pk1_value,
1607 l_tab_pk2_value, l_tab_seq_num,l_tab_upd_user,l_tab_category,l_tab_usage_type,l_tab_data_type,
1608 l_tab_datatype_id,l_tab_document_id,l_tab_title;
1609 CLOSE l_SRAtt_csr;
1610 END IF;
1611
1612 IF p_entity_name = 'JTF_TASKS_B' THEN
1613 --- ATTACHMENT DOWNLOAD FOR task
1614 OPEN l_TaskAtt_csr(p_PK1_value, p_user_id,p_data_typeid);
1615 FETCH l_TaskAtt_csr BULK COLLECT INTO l_tab_access_id, l_tab_file_id, l_tab_user_id, l_tab_name,
1616 l_tab_description, l_tab_language, l_tab_entity_name, l_tab_pk1_value,
1617 l_tab_pk2_value, l_tab_seq_num,l_tab_upd_user,l_tab_category,l_tab_usage_type,l_tab_data_type,
1618 l_tab_datatype_id,l_tab_document_id,l_tab_title;
1619 CLOSE l_TaskAtt_csr;
1620
1621 END IF;
1622
1623 --Bug 5726888
1624 IF p_entity_name = 'CSF_DEBRIEF_HEADERS' THEN
1625 --- SIGNATURE DOWNLOAD
1626 OPEN c_Signature(p_PK1_value, p_user_id,p_data_typeid);
1627 FETCH c_Signature BULK COLLECT INTO l_tab_access_id, l_tab_file_id, l_tab_user_id, l_tab_name,
1628 l_tab_description, l_tab_language, l_tab_entity_name, l_tab_pk1_value,
1629 l_tab_pk2_value, l_tab_seq_num,l_tab_upd_user,l_tab_category,l_tab_usage_type,l_tab_data_type,
1630 l_tab_datatype_id,l_tab_document_id,l_tab_title;
1631 CLOSE c_Signature;
1632
1633 END IF;
1634
1635 IF l_tab_access_id.COUNT > 0 THEN
1636
1637 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
1638 jtm_message_log_pkg.Log_Msg
1639 ( 0
1640 , g_table_name
1641 , 'Pushing ' || l_tab_access_id.COUNT || ' updated record(s)'
1642 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
1643 );
1644 END IF;
1645
1646 FORALL i in l_tab_access_id.FIRST .. l_tab_access_id.LAST
1647 INSERT INTO CSM_FND_LOBS_ACC
1648 (access_id, file_id, user_id, name, description,
1649 language, entity_name, pk1_value, pk2_value, seq_num,
1650 update_user, category, usage_type, data_type, counter,
1651 last_update_date, last_updated_by, creation_date,created_by, data_type_id,
1652 document_id,title)
1653 VALUES (l_tab_access_id(i), l_tab_file_id(i), l_tab_user_id(i), l_tab_name(i), l_tab_description(i),
1654 l_tab_language(i), l_tab_entity_name(i), l_tab_pk1_value(i), l_tab_pk2_value(i), l_tab_seq_num(i),
1655 l_tab_upd_user(i), l_tab_category(i), l_tab_usage_type(i),l_tab_data_type(i), 1,
1656 sysdate, 1, sysdate, 1, l_tab_datatype_id(i),
1657 l_tab_document_id(i),l_tab_title(i));
1658
1659
1660 IF p_data_typeid =5 OR p_data_typeid =1 OR (p_data_typeid=6 AND p_dodirty = TRUE) THEN
1661 --do mark dirly only for URL and Short Text
1662 l_dummy := asg_download.mark_dirty(
1663 P_PUB_ITEM => l_publicationitemname
1664 , P_ACCESSLIST => l_tab_access_id
1665 , P_USERID_LIST => l_tab_user_id
1666 , P_DML_TYPE => 'I'
1667 , P_TIMESTAMP => SYSDATE);
1668
1669 END IF;
1670
1671
1672 END IF;--Access id count if
1673
1674 EXCEPTION
1675 WHEN OTHERS THEN
1676 l_sqlerrno := to_char(SQLCODE);
1677 l_sqlerrmsg := substr(SQLERRM, 1,2000);
1678 l_error_msg := ' Exception in INSERT_ACC_ON_UPLOAD for PK1_value:'
1679 || to_char(p_PK1_value) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
1680 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_LOBS_EVENT_PKG.INSERT_ACC_ON_UPLOAD',FND_LOG.LEVEL_EXCEPTION);
1681 RAISE;
1682
1683 END INSERT_ACC_ON_UPLOAD;
1684
1685
1686 END CSM_LOBS_EVENT_PKG;