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