[Home] [Help]
PACKAGE BODY: APPS.CSL_LOBS_ACC_PKG
Source
1 PACKAGE BODY CSL_LOBS_ACC_PKG AS
2 /* $Header: csllbacb.pls 120.0 2005/05/30 07:41:21 appldev noship $ */
3
4 g_publication_item_name CONSTANT JTM_HOOK_UTIL_PKG.t_publication_item_list :=
5 JTM_HOOK_UTIL_PKG.t_publication_item_list('CSL_LOBS');
6
7 g_debug_level NUMBER; -- debug level
8 g_table_name CONSTANT VARCHAR2(30) := 'CSL_LOBS';
9 g_pk1_name CONSTANT VARCHAR2(30) := 'FILE_ID';
10
11
12 PROCEDURE INSERT_ACC_RECORD ( p_task_assignment_id IN NUMBER,
13 p_resource_id IN NUMBER)
14 IS
15 l_task_assignment_id jtf_task_assignments.task_assignment_id%type;
16 l_resource_id jtf_task_assignments.resource_id%type;
17 l_user_id fnd_user.user_id%TYPE;
18 l_item_key varchar2(50);
19 l_seq_val number(15);
20 l_pkvalueslist asg_download.pk_list;
21 l_null_pkvalueslist asg_download.pk_list;
22 l_accessid number;
23 l_markdirty_rc BOOLEAN;
24 l_dmllist varchar2(1);
25 l_dml varchar2(1);
26 l_timestamp DATE;
27 l_resourcelist number;
28 l_publicationitemname VARCHAR2(50);
29 l_excep_markdirty_failed EXCEPTION;
30 l_err_mesg VARCHAR2(1000);
31
32 CURSOR l_signature_csr(p_task_assignment_id IN number,
33 p_resource_id IN number) IS
34 SELECT distinct lob.file_id, jtrs.user_id
35 FROM jtf_task_assignments jtf, csf_debrief_headers dbfhdr,
36 jtf_rs_resource_extns jtrs, asg_user, fnd_attached_documents fndatt,
37 fnd_documents_tl fnddoc_tl, fnd_documents fnddoc, fnd_lobs lob,
38 fnd_document_categories_tl fnddoccat_tl
39 WHERE jtf.task_assignment_id = p_task_assignment_id
40 AND jtf.task_assignment_id = dbfhdr.task_assignment_id
41 AND jtf.resource_id = p_resource_id
42 AND jtrs.resource_id = jtf.resource_id
43 AND asg_user.resource_id = jtf.resource_id
44 AND fndatt.entity_name = 'CSF_DEBRIEF_HEADERS'
45 AND fndatt.pk1_value = dbfhdr.debrief_header_id
46 AND fndatt.document_id = fnddoc_tl.document_id
47 AND fnddoc_tl.document_id = fnddoc.document_id
48 AND fnddoc_tl.language = asg_user.language
49 AND fnddoc_tl.media_id = lob.file_id
50 AND fnddoccat_tl.category_id = fnddoc.category_id
51 AND fnddoccat_tl.language = asg_user.language
52 AND fnddoccat_tl.user_name = 'Signature';
53
54 CURSOR l_rs_resource_extns_csr (p_user_id fnd_user.user_id%type) IS
55 SELECT resource_id FROM jtf_rs_resource_extns WHERE user_id = p_user_id;
56
57 BEGIN
58
59 g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
60
61 l_task_assignment_id := p_task_assignment_id;
62 l_resource_id := p_resource_id;
63 l_publicationitemname := 'CSL_LOBS';
64 l_timestamp := SYSDATE;
65
66 l_dmllist := ASG_DOWNLOAD.INS;
67
68 FOR l_signature_rec IN l_signature_csr(l_task_assignment_id, l_resource_id)
69 LOOP
70 -- initialize l_pkvalueslist
71 l_pkvalueslist := l_null_pkvalueslist;
72
73 l_accessid := l_signature_rec.file_id;
74 l_pkvalueslist(1) := to_char(l_signature_rec.file_id);
75
76 open l_rs_resource_extns_csr(l_signature_rec.user_id);
77 fetch l_rs_resource_extns_csr into l_resourcelist;
78 close l_rs_resource_extns_csr;
79
80 -- make the markdirty call
81 l_markdirty_rc := asg_download.MarkDirty (
82 l_publicationitemname
83 , l_accessid
84 , l_resourcelist
85 , l_dmllist
86 , l_timestamp
87 , l_pkvalueslist);
88
89 IF NOT l_markdirty_rc THEN
90 RAISE l_excep_markdirty_failed;
91 END IF;
92 END LOOP;
93
94 EXCEPTION
95 WHEN l_excep_markdirty_failed THEN
96
97 l_err_mesg := 'EXCEPTION IN CSL_LOBS_PKG.INSERT_ACC_RECORD :'
98 || to_char(p_task_assignment_id);
99 jtm_message_log_pkg.Log_Msg (
100 p_task_assignment_id
101 , 'LOBS_ACC_PKG.INSERT_ACC'
102 , l_err_mesg
103 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
104
105 RAISE;
106
107 WHEN OTHERS THEN
108 l_err_mesg := 'EXCEPTION IN CSL_LOBS_PKG.INSERT_ACC_RECORD : '
109 || to_char(p_task_assignment_id);
110 jtm_message_log_pkg.Log_Msg (
111 p_task_assignment_id
112 , 'LOBS_ACC_PKG.INSERT_ACC'
113 , l_err_mesg
114 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
115
116 RAISE;
117
118 END INSERT_ACC_RECORD;
119
120
121
122 PROCEDURE DELETE_ACC_RECORD ( p_task_assignment_id IN NUMBER,
123 p_resource_id IN NUMBER)
124
125 IS
126 l_task_assignment_id jtf_task_assignments.task_assignment_id%type;
127 l_resource_id jtf_task_assignments.resource_id%type;
128 l_user_id fnd_user.user_id%TYPE;
129 l_item_key varchar2(50);
130 l_seq_val number(15);
131 l_pkvalueslist asg_download.pk_list;
132 l_null_pkvalueslist asg_download.pk_list;
133 l_accessid number;
134 l_markdirty_rc BOOLEAN;
135 l_dmllist varchar2(1);
136 l_dml varchar2(1);
137 l_timestamp DATE;
138 l_resourcelist number;
139 l_publicationitemname VARCHAR2(50);
140 l_excep_markdirty_failed EXCEPTION;
141 l_err_mesg VARCHAR2(1000);
142
143 CURSOR l_signature_csr (p_task_assignment_id IN number,
144 p_resource_id IN number) IS
145 SELECT distinct lob.file_id, acc.user_id
146 FROM csm_task_assignments_acc acc , csf_debrief_headers dbfhdr,
147 asg_user, fnd_attached_documents fndatt, fnd_documents_tl fnddoc_tl,
148 fnd_documents fnddoc, fnd_document_categories_tl fnddoccat_tl,
149 fnd_lobs lob
150 WHERE asg_user.resource_id = p_resource_id
151 AND acc.task_assignment_id = p_task_assignment_id
152 AND acc.task_assignment_id = dbfhdr.task_assignment_id
153 AND asg_user.user_id = acc.user_id
154 AND fndatt.entity_name = 'CSF_DEBRIEF_HEADERS'
155 AND fndatt.pk1_value = dbfhdr.debrief_header_id
156 AND fndatt.document_id = fnddoc_tl.document_id
157 AND fnddoc_tl.document_id = fnddoc.document_id
158 AND fnddoc_tl.language = asg_user.language
159 AND fnddoc_tl.media_id = lob.file_id
160 AND fnddoccat_tl.category_id = fnddoc.category_id
161 AND fnddoccat_tl.language = asg_user.language
162 AND fnddoccat_tl.user_name = 'Signature';
163
164 CURSOR l_rs_resource_extns_csr (p_user_id fnd_user.user_id%type) IS
165 SELECT resource_id FROM jtf_rs_resource_extns WHERE user_id = p_user_id;
166
167 BEGIN
168
169 g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
170
171 l_task_assignment_id := p_task_assignment_id;
172 l_resource_id := p_resource_id;
173 l_publicationitemname := 'CSL_LOBS';
174 l_timestamp := SYSDATE;
175 l_dmllist := ASG_DOWNLOAD.DEL;
176
177 FOR l_signature_rec IN l_signature_csr(l_task_assignment_id, l_resource_id)
178 LOOP
179 -- initialize l_pkvalueslist
180 l_pkvalueslist := l_null_pkvalueslist;
181 l_accessid := l_signature_rec.file_id;
182 l_pkvalueslist(1) := to_char(l_signature_rec.file_id);
183
184 open l_rs_resource_extns_csr(l_signature_rec.user_id);
185 fetch l_rs_resource_extns_csr into l_resourcelist;
186 close l_rs_resource_extns_csr;
187
188 -- make the markdirty call
189
190 l_markdirty_rc := asg_download.MarkDirty (
191 l_publicationitemname
192 , l_accessid
193 , l_resourcelist
194 , l_dmllist
195 , l_timestamp
196 , l_pkvalueslist);
197
198 IF not l_markdirty_rc THEN
199 RAISE l_excep_markdirty_failed;
200 END IF;
201 END LOOP;
202
203 EXCEPTION
204 WHEN l_excep_markdirty_failed then
205 l_err_mesg := 'EXCEPTION IN CSL_LOBS_PKG.DELETE_ACC_RECORD :'
206 || to_char(p_task_assignment_id);
207 jtm_message_log_pkg.Log_Msg (
208 p_task_assignment_id
209 , 'LOBS_ACC_PKG.INSERT_ACC'
210 , l_err_mesg
211 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
212 RAISE;
213
214 WHEN OTHERS THEN
215 l_err_mesg := 'EXCEPTION IN CSL_LOBS_PKG.DELETE_ACC_RECORD :'
216 || to_char(p_task_assignment_id);
217 jtm_message_log_pkg.Log_Msg (
218 p_task_assignment_id
219 , 'LOBS_ACC_PKG.INSERT_ACC'
220 , l_err_mesg
221 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
222 RAISE;
223
224 END DELETE_ACC_RECORD;
225
226
227 --Bug 3724142
228 PROCEDURE CONC_DOWNLOAD_ATTACHMENTS ( p_status OUT NOCOPY VARCHAR2,
229 p_message OUT NOCOPY VARCHAR2)
230 IS
231 PRAGMA AUTONOMOUS_TRANSACTION;
232
233 /*** get the last run date of the concurent program ***/
234 CURSOR c_LastRundate
235 IS
236 select LAST_RUN_DATE
237 from JTM_CON_REQUEST_DATA
238 where package_name = 'CSL_LOBS_ACC_PKG'
239 AND procedure_name = 'CONC_DOWNLOAD_ATTACHMENTS';
240
241 --Cursor to get the SR attachemnts
242 CURSOR c_SRAtt (b_lastRundate date) IS
243 SELECT lobs.file_id, acc.resource_id
244 FROM fnd_documents_tl fnddoc_tl, fnd_documents fnddoc,
245 fnd_document_categories_tl fnddoccat_tl,
246 fnd_attached_documents fndattdoc, fnd_lobs lobs,
247 csl_cs_incidents_all_acc acc, asg_user asg
248 WHERE fndattdoc.document_id = fnddoc_tl.document_id
249 AND fnddoc_tl.language = asg.language
250 AND fnddoc_tl.document_id = fnddoc.document_id
251 AND fnddoc_tl.media_id = lobs.file_id
252 AND fnddoccat_tl.category_id = fnddoc.category_id
253 AND fnddoccat_tl.language = asg.language
254 AND fndattdoc.pk1_value = to_char(acc.incident_id)
255 AND asg.resource_id = acc.resource_id
256 AND fnddoccat_tl.name = 'MISC'
257 AND fndattdoc.entity_name = 'CS_INCIDENTS'
258 AND fnddoc.datatype_id=6
259 AND fndattdoc.last_update_date >= b_lastRundate;
260
261 --Cursor to get the task attachemnts
262 CURSOR c_TaskAtt (b_lastRundate date) IS
263 SELECT lobs.file_id, acc.resource_id
264 FROM fnd_documents_tl fnddoc_tl, fnd_documents fnddoc,
265 fnd_document_categories_tl fnddoccat_tl,
266 fnd_attached_documents fndattdoc, fnd_lobs lobs,
267 csl_jtf_tasks_acc acc, asg_user asg
268 WHERE fndattdoc.document_id = fnddoc_tl.document_id
269 AND fnddoc_tl.language = asg.language
270 AND fnddoc_tl.document_id = fnddoc.document_id
271 AND fnddoc_tl.media_id = lobs.file_id
272 AND fnddoccat_tl.category_id = fnddoc.category_id
273 AND fnddoccat_tl.language = asg.language
274 AND fndattdoc.pk1_value = to_char(acc.task_id)
275 AND asg.resource_id = acc.resource_id
276 AND fnddoccat_tl.name = 'MISC'
277 AND fndattdoc.entity_name = 'JTF_TASKS_B'
278 AND fnddoc.datatype_id=6
279 AND fndattdoc.last_update_date >= b_lastRundate;
280
281 /* For future support
282 --Cursor to get the Customer attachemnts
283 CURSOR c_PartyAtt (b_lastRundate date) IS
284 SELECT lobs.file_id, acc.resource_id
285 FROM fnd_documents_tl fnddoc_tl, fnd_documents fnddoc,
286 fnd_document_categories_tl fnddoccat_tl,
287 fnd_attached_documents fndattdoc, fnd_lobs lobs,
288 csl_hz_parties_acc acc, asg_user asg
289 WHERE fndattdoc.document_id = fnddoc_tl.document_id
290 AND fnddoc_tl.language = asg.language
291 AND fnddoc_tl.document_id = fnddoc.document_id
292 AND fnddoc_tl.media_id = lobs.file_id
293 AND fnddoccat_tl.category_id = fnddoc.category_id
294 AND fnddoccat_tl.language = asg.language
295 AND fndattdoc.pk1_value = to_char(acc.party_id)
296 AND asg.resource_id = acc.resource_id
297 AND fnddoccat_tl.name = 'MISC'
298 AND fndattdoc.entity_name = 'HZ_PARTIES'
299 AND fnddoc.datatype_id=6
300 AND fndattdoc.last_update_date >= b_lastRundate;
301
302 --Cursor to get the IB Item attachemnts
303 CURSOR c_IBItemAtt (b_lastRundate date) IS
304 SELECT lobs.file_id, acc.resource_id
305 FROM fnd_documents_tl fnddoc_tl, fnd_documents fnddoc,
306 fnd_document_categories_tl fnddoccat_tl,
307 fnd_attached_documents fndattdoc, fnd_lobs lobs,
308 csl_csi_item_instances_acc acc, asg_user asg
309 WHERE fndattdoc.document_id = fnddoc_tl.document_id
310 AND fnddoc_tl.language = asg.language
311 AND fnddoc_tl.document_id = fnddoc.document_id
312 AND fnddoc_tl.media_id = lobs.file_id
313 AND fnddoccat_tl.category_id = fnddoc.category_id
314 AND fnddoccat_tl.language = asg.language
315 AND fndattdoc.pk1_value = to_char(acc.instance_id)
316 AND asg.resource_id = acc.resource_id
317 AND fnddoccat_tl.name = 'MISC'
318 AND fndattdoc.entity_name = 'CS_CUSTOMER_PRODUCTS_ALL'
319 AND fnddoc.datatype_id=6
320 AND fndattdoc.last_update_date >= b_lastRundate;
321
322 --Cursor to get the debrief attachemnts
323 CURSOR c_DebriefAtt (b_lastRundate date) IS
324 SELECT lobs.file_id, acc.resource_id
325 FROM fnd_documents_tl fnddoc_tl, fnd_documents fnddoc,
326 fnd_document_categories_tl fnddoccat_tl,
327 fnd_attached_documents fndattdoc, fnd_lobs lobs,
328 JTM_CSF_DEBRIEF_HEADERS_ACC acc, asg_user asg
329 WHERE fndattdoc.document_id = fnddoc_tl.document_id
330 AND fnddoc_tl.language = asg.language
331 AND fnddoc_tl.document_id = fnddoc.document_id
332 AND fnddoc_tl.media_id = lobs.file_id
333 AND fnddoccat_tl.category_id = fnddoc.category_id
334 AND fnddoccat_tl.language = asg.language
335 AND fndattdoc.pk1_value = to_char(acc.debrief_header_id)
336 AND asg.resource_id = acc.resource_id
337 AND fnddoccat_tl.name = 'MISC'
338 AND fndattdoc.entity_name = 'CSF_DEBRIEF_HEADERS'
339 AND fnddoc.datatype_id=6
340 AND fndattdoc.last_update_date >= b_lastRundate;
341 */
342
343 r_LastRundate c_LastRundate%ROWTYPE;
344 l_current_run_date DATE;
345 l_dummy BOOLEAN;
346
347 l_tab_access_id ASG_DOWNLOAD.ACCESS_LIST;
348
349 l_tab_resource_id ASG_DOWNLOAD.USER_LIST;
350
351 BEGIN
352
353 /*** get debug level ***/
354 g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
355 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
356 jtm_message_log_pkg.Log_Msg
357 ( 0
358 , g_table_name
359 , 'Entering CONC_DOWNLOAD_ATTACHMENTS'
360 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
361 );
362 END IF;
363
364 /*** First retrieve last run date of the conccurent program ***/
365 OPEN c_LastRundate;
366 FETCH c_LastRundate INTO r_LastRundate;
367 CLOSE c_LastRundate;
368
369 l_current_run_date := SYSDATE;
370
371 --SR
372 l_tab_access_id.delete;
373 l_tab_resource_id.delete;
374
375
376 --If the max size of attachment is less than 1, then exit.
377 IF (TO_NUMBER(FND_PROFILE.Value('CSM_MAX_ATTACHMENT_SIZE')) < 1) THEN
378 /*Update the last run date*/
379 UPDATE jtm_con_request_data SET last_run_date = l_current_run_date
380 WHERE package_name = 'CSL_LOBS_ACC_PKG'
381 AND procedure_name = 'CONC_DOWNLOAD_ATTACHMENTS';
382
383 COMMIT;
384
385 p_status := 'FINE';
386 p_message := 'CSL_LOBS_ACC_PKG.CONC_DOWNLOAD_ATTACHMENTS '
387 || ' Executed successfully';
388
389 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
390 jtm_message_log_pkg.Log_Msg
391 ( 0
392 , g_table_name
393 , 'Leaving CONC_DOWNLOAD_ATTACHMENTS - Max att download size is less than 1'
394 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
395 );
396 END IF;
397
398 RETURN;
399 END IF;
400
401 OPEN c_SRAtt(r_LastRundate.last_run_date);
402 FETCH c_SRAtt BULK COLLECT INTO l_tab_access_id, l_tab_resource_id;
403 CLOSE c_SRAtt;
404
405 IF l_tab_access_id.COUNT > 0 THEN
406 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
407 jtm_message_log_pkg.Log_Msg
408 ( 0
409 , g_table_name
410 , 'Pushing ' || l_tab_access_id.COUNT || ' updated record(s)'
411 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
412 );
413 END IF;
414
415 l_dummy := asg_download.markdirty(
416 P_PUB_ITEM => g_publication_item_name(1)
417 , P_ACCESSLIST => l_tab_access_id
418 , P_RESOURCELIST => l_tab_resource_id
419 , P_DML_TYPE => 'I'
420 , P_TIMESTAMP => SYSDATE);
421
422 END IF;
423
424
425 --TASK
426 l_tab_access_id.delete;
427 l_tab_resource_id.delete;
428
429 OPEN c_TaskAtt(r_LastRundate.last_run_date);
430 FETCH c_TaskAtt BULK COLLECT INTO l_tab_access_id, l_tab_resource_id;
431 CLOSE c_TaskAtt;
432
433 IF l_tab_access_id.COUNT > 0 THEN
434 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
435 jtm_message_log_pkg.Log_Msg
436 ( 0
437 , g_table_name
438 , 'Pushing ' || l_tab_access_id.COUNT || ' updated record(s)'
439 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
440 );
441 END IF;
442
443 l_dummy := asg_download.markdirty(
444 P_PUB_ITEM => g_publication_item_name(1)
445 , P_ACCESSLIST => l_tab_access_id
446 , P_RESOURCELIST => l_tab_resource_id
447 , P_DML_TYPE => 'I'
448 , P_TIMESTAMP => SYSDATE);
449
450 END IF;
451
452 /*For future support
453 --Customer
454 l_tab_access_id.delete;
455 l_tab_resource_id.delete;
456
457 OPEN c_PartyAtt(r_LastRundate.last_run_date);
458 FETCH c_PartyAtt BULK COLLECT INTO l_tab_access_id, l_tab_resource_id;
459 CLOSE c_PartyAtt;
460
461 IF l_tab_access_id.COUNT > 0 THEN
462 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
463 jtm_message_log_pkg.Log_Msg
464 ( 0
465 , g_table_name
466 , 'Pushing ' || l_tab_access_id.COUNT || ' updated record(s)'
467 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
468 );
469 END IF;
470
471 l_dummy := asg_download.markdirty(
472 P_PUB_ITEM => g_publication_item_name(1)
473 , P_ACCESSLIST => l_tab_access_id
474 , P_RESOURCELIST => l_tab_resource_id
475 , P_DML_TYPE => 'I'
476 , P_TIMESTAMP => SYSDATE);
477
478 END IF;
479
480 --IB Item
481 l_tab_access_id.delete;
482 l_tab_resource_id.delete;
483
484 OPEN c_IBItemAtt(r_LastRundate.last_run_date);
485 FETCH c_IBItemAtt BULK COLLECT INTO l_tab_access_id, l_tab_resource_id;
486 CLOSE c_IBItemAtt;
487
488 IF l_tab_access_id.COUNT > 0 THEN
489 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
490 jtm_message_log_pkg.Log_Msg
491 ( 0
492 , g_table_name
493 , 'Pushing ' || l_tab_access_id.COUNT || ' updated record(s)'
494 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
495 );
496 END IF;
497
498 l_dummy := asg_download.markdirty(
499 P_PUB_ITEM => g_publication_item_name(1)
500 , P_ACCESSLIST => l_tab_access_id
501 , P_RESOURCELIST => l_tab_resource_id
502 , P_DML_TYPE => 'I'
503 , P_TIMESTAMP => SYSDATE);
504
505 END IF;
506
507
508 --DEBRIEF
509 l_tab_access_id.delete;
510 l_tab_resource_id.delete;
511
512 OPEN c_DebriefAtt(r_LastRundate.last_run_date);
513 FETCH c_DebriefAtt BULK COLLECT INTO l_tab_access_id, l_tab_resource_id;
514 CLOSE c_DebriefAtt;
515
516 IF l_tab_access_id.COUNT > 0 THEN
517 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
518 jtm_message_log_pkg.Log_Msg
519 ( 0
520 , g_table_name
521 , 'Pushing ' || l_tab_access_id.COUNT || ' updated record(s)'
522 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
523 );
524 END IF;
525
526 l_dummy := asg_download.markdirty(
527 P_PUB_ITEM => g_publication_item_name(1)
528 , P_ACCESSLIST => l_tab_access_id
529 , P_RESOURCELIST => l_tab_resource_id
530 , P_DML_TYPE => 'I'
531 , P_TIMESTAMP => SYSDATE);
532
533 END IF;
534 */
535
536 /*Update the last run date*/
537 UPDATE jtm_con_request_data SET last_run_date = l_current_run_date
538 WHERE package_name = 'CSL_LOBS_ACC_PKG'
539 AND procedure_name = 'CONC_DOWNLOAD_ATTACHMENTS';
540
541 COMMIT;
542
543 p_status := 'FINE';
544 p_message := 'CSL_LOBS_ACC_PKG.CONC_DOWNLOAD_ATTACHMENTS '
545 || ' Executed successfully';
546
547 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
548 jtm_message_log_pkg.Log_Msg
549 ( 0
550 , g_table_name
551 , 'Leaving CONC_DOWNLOAD_ATTACHMENTS'
552 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
553 );
554 END IF;
555
556 EXCEPTION
557
558 WHEN OTHERS THEN
559 p_status := 'ERROR';
560 p_message := 'Error in '||
561 'CSL_LOBS_ACC_PKG.CONC_DOWNLOAD_ATTACHMENTS: '
562 || substr(SQLERRM, 1, 2000);
563
564
565 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
566 jtm_message_log_pkg.Log_Msg
567 ( 0
568 , g_table_name
569 , 'CONC_DOWNLOAD_ATTACHMENTS'||fnd_global.local_chr(10)||
570 'Error: '||sqlerrm
571 , JTM_HOOK_UTIL_PKG.g_debug_level_error);
572 END IF;
573 ROLLBACK;
574
575 END CONC_DOWNLOAD_ATTACHMENTS;
576
577 --Bug 3724142
578 --To download attachment linked to a particular SR. Called by the
579 --incidents API.
580 PROCEDURE DOWNLOAD_SR_ATTACHMENTS ( p_incident_id IN NUMBER)
581 IS
582
583 --Cursor to get the SR attachemnts
584 CURSOR c_SRAtt (b_incident_id number) IS
585 SELECT lobs.file_id, acc.resource_id
586 FROM fnd_documents_tl fnddoc_tl, fnd_documents fnddoc,
587 fnd_document_categories_tl fnddoccat_tl,
588 fnd_attached_documents fndattdoc, fnd_lobs lobs,
589 csl_cs_incidents_all_acc acc, asg_user asg
590 WHERE fndattdoc.document_id = fnddoc_tl.document_id
591 AND fnddoc_tl.language = asg.language
592 AND fnddoc_tl.document_id = fnddoc.document_id
593 AND fnddoc_tl.media_id = lobs.file_id
594 AND fnddoccat_tl.category_id = fnddoc.category_id
595 AND fnddoccat_tl.language = asg.language
596 AND fndattdoc.pk1_value = to_char(acc.incident_id)
597 AND asg.resource_id = acc.resource_id
598 AND fnddoccat_tl.name = 'MISC'
599 AND fndattdoc.entity_name = 'CS_INCIDENTS'
600 AND fnddoc.datatype_id=6
601 AND acc.incident_id = b_incident_id;
602
603 l_dummy BOOLEAN;
604 l_tab_access_id ASG_DOWNLOAD.ACCESS_LIST;
605 l_tab_resource_id ASG_DOWNLOAD.USER_LIST;
606
607 BEGIN
608
609 /*** get debug level ***/
610 g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
611 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
612 jtm_message_log_pkg.Log_Msg
613 ( 0
614 , g_table_name
615 , 'Entering DOWNLOAD_SR_ATTACHMENTS'
616 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
617 );
618 END IF;
619
620 --SR
621 l_tab_access_id.delete;
622 l_tab_resource_id.delete;
623
624
625 --If the max size of attachment is less than 1, then exit.
626 IF (TO_NUMBER(FND_PROFILE.Value('CSM_MAX_ATTACHMENT_SIZE')) < 1) THEN
627 /*Update the last run date*/
628
629 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
630 jtm_message_log_pkg.Log_Msg
631 ( 0
632 , g_table_name
633 , 'Leaving DOWNLOAD_SR_ATTACHMENTS - Max att download size is less than 1'
634 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
635 );
636 END IF;
637
638 RETURN;
639 END IF;
640
641 OPEN c_SRAtt(p_incident_id);
642 FETCH c_SRAtt BULK COLLECT INTO l_tab_access_id, l_tab_resource_id;
643 CLOSE c_SRAtt;
644
645 IF l_tab_access_id.COUNT > 0 THEN
646 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
647 jtm_message_log_pkg.Log_Msg
648 ( 0
649 , g_table_name
650 , 'Pushing ' || l_tab_access_id.COUNT || ' updated record(s)'
651 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
652 );
653 END IF;
654
655 l_dummy := asg_download.markdirty(
656 P_PUB_ITEM => g_publication_item_name(1)
657 , P_ACCESSLIST => l_tab_access_id
658 , P_RESOURCELIST => l_tab_resource_id
659 , P_DML_TYPE => 'I'
660 , P_TIMESTAMP => SYSDATE);
661
662 END IF;
663
664
665 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
666 jtm_message_log_pkg.Log_Msg
667 ( 0
668 , g_table_name
669 , 'Leaving DOWNLOAD_SR_ATTACHMENTS'
670 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
671 );
672 END IF;
673
674 EXCEPTION
675
676 WHEN OTHERS THEN
677 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
678 jtm_message_log_pkg.Log_Msg
679 ( 0
680 , g_table_name
681 , 'DOWNLOAD_SR_ATTACHMENTS'||fnd_global.local_chr(10)||
682 'Error: '||sqlerrm
683 , JTM_HOOK_UTIL_PKG.g_debug_level_error);
684 END IF;
685 ROLLBACK;
686
687 END DOWNLOAD_SR_ATTACHMENTS;
688
689
690 --Bug 3724142
691 --To download attachment linked to a particular task. Called by the
692 --Tasks API.
693 PROCEDURE DOWNLOAD_TASK_ATTACHMENTS ( p_task_id IN NUMBER)
694 IS
695
696 --Cursor to get the SR attachemnts
697 CURSOR c_TaskAtt (b_task_id number) IS
698 SELECT lobs.file_id, acc.resource_id
699 FROM fnd_documents_tl fnddoc_tl, fnd_documents fnddoc,
700 fnd_document_categories_tl fnddoccat_tl,
701 fnd_attached_documents fndattdoc, fnd_lobs lobs,
702 csl_jtf_tasks_acc acc, asg_user asg
703 WHERE fndattdoc.document_id = fnddoc_tl.document_id
704 AND fnddoc_tl.language = asg.language
705 AND fnddoc_tl.document_id = fnddoc.document_id
706 AND fnddoc_tl.media_id = lobs.file_id
707 AND fnddoccat_tl.category_id = fnddoc.category_id
708 AND fnddoccat_tl.language = asg.language
709 AND fndattdoc.pk1_value = to_char(acc.task_id)
710 AND asg.resource_id = acc.resource_id
711 AND fnddoccat_tl.name = 'MISC'
712 AND fndattdoc.entity_name = 'JTF_TASKS_B'
713 AND fnddoc.datatype_id=6
714 AND acc.task_id = b_task_id;
715
716 l_dummy BOOLEAN;
717 l_tab_access_id ASG_DOWNLOAD.ACCESS_LIST;
718 l_tab_resource_id ASG_DOWNLOAD.USER_LIST;
719
720 BEGIN
721
722 /*** get debug level ***/
723 g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
724 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
725 jtm_message_log_pkg.Log_Msg
726 ( 0
727 , g_table_name
728 , 'Entering DOWNLOAD_TASK_ATTACHMENTS'
729 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
730 );
731 END IF;
732
733 --If the max size of attachment is less than 1, then exit.
734 IF (TO_NUMBER(FND_PROFILE.Value('CSM_MAX_ATTACHMENT_SIZE')) < 1) THEN
735
736 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
737 jtm_message_log_pkg.Log_Msg
738 ( 0
739 , g_table_name
740 , 'Leaving DOWNLOAD_TASK_ATTACHMENTS - Max att download size is less than 1'
741 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
742 );
743 END IF;
744
745 RETURN;
746 END IF;
747
748 --TASK
749 l_tab_access_id.delete;
750 l_tab_resource_id.delete;
751
752 OPEN c_TaskAtt(p_task_id);
753 FETCH c_TaskAtt BULK COLLECT INTO l_tab_access_id, l_tab_resource_id;
754 CLOSE c_TaskAtt;
755
756 IF l_tab_access_id.COUNT > 0 THEN
757 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
758 jtm_message_log_pkg.Log_Msg
759 ( 0
760 , g_table_name
761 , 'Pushing ' || l_tab_access_id.COUNT || ' updated record(s)'
762 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
763 );
764 END IF;
765
766 l_dummy := asg_download.markdirty(
767 P_PUB_ITEM => g_publication_item_name(1)
768 , P_ACCESSLIST => l_tab_access_id
769 , P_RESOURCELIST => l_tab_resource_id
770 , P_DML_TYPE => 'I'
771 , P_TIMESTAMP => SYSDATE);
772
773 END IF;
774
775 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
776 jtm_message_log_pkg.Log_Msg
777 ( 0
778 , g_table_name
779 , 'Leaving DOWNLOAD_TASK_ATTACHMENTS'
780 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
781 );
782 END IF;
783
784 EXCEPTION
785
786 WHEN OTHERS THEN
787 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
788 jtm_message_log_pkg.Log_Msg
789 ( 0
790 , g_table_name
791 , 'DOWNLOAD_TASK_ATTACHMENTS'||fnd_global.local_chr(10)||
792 'Error: '||sqlerrm
793 , JTM_HOOK_UTIL_PKG.g_debug_level_error);
794 END IF;
795 ROLLBACK;
796
797 END DOWNLOAD_TASK_ATTACHMENTS;
798
799 --Bug 3724142
800 --To delete the attachment when SR/Task is deleted
801 PROCEDURE DELETE_ATTACHMENTS ( p_entity_name IN VARCHAR2,
802 p_primary_key IN NUMBER,
803 p_resource_id IN NUMBER)
804
805 IS
806
807 CURSOR c_SRAtt ( b_incident_id IN NUMBER,
808 b_resource_id IN NUMBER) IS
809 SELECT lobs.file_id, b_resource_id
810 FROM fnd_documents_tl fnddoc_tl, fnd_documents fnddoc,
811 fnd_document_categories_tl fnddoccat_tl,
812 fnd_attached_documents fndattdoc, fnd_lobs lobs,
813 asg_user asg
814 WHERE fndattdoc.document_id = fnddoc_tl.document_id
815 AND fnddoc_tl.language = asg.language
816 AND fnddoc_tl.document_id = fnddoc.document_id
817 AND fnddoc_tl.media_id = lobs.file_id
818 AND fnddoccat_tl.category_id = fnddoc.category_id
819 AND fnddoccat_tl.language = asg.language
820 AND fnddoccat_tl.name = 'MISC'
821 AND fndattdoc.entity_name = 'CS_INCIDENTS'
822 AND fnddoc.datatype_id=6
823 AND fndattdoc.pk1_value = to_char(b_incident_id)
824 AND asg.resource_id = b_resource_id;
825
826 CURSOR c_TaskAtt ( b_task_id IN NUMBER,
827 b_resource_id IN NUMBER) IS
828 SELECT lobs.file_id, b_resource_id
829 FROM fnd_documents_tl fnddoc_tl, fnd_documents fnddoc,
830 fnd_document_categories_tl fnddoccat_tl,
831 fnd_attached_documents fndattdoc, fnd_lobs lobs,
832 asg_user asg
833 WHERE fndattdoc.document_id = fnddoc_tl.document_id
834 AND fnddoc_tl.language = asg.language
835 AND fnddoc_tl.document_id = fnddoc.document_id
836 AND fnddoc_tl.media_id = lobs.file_id
837 AND fnddoccat_tl.category_id = fnddoc.category_id
838 AND fnddoccat_tl.language = asg.language
839 AND fnddoccat_tl.name = 'MISC'
840 AND fndattdoc.entity_name = 'JTF_TASKS_B'
841 AND fnddoc.datatype_id=6
842 AND fndattdoc.pk1_value = to_char(b_task_id)
843 AND asg.resource_id = b_resource_id;
844
845 r_SRAtt c_SRAtt%ROWTYPE;
846 r_TaskAtt c_TaskAtt%ROWTYPE;
847
848 l_dummy BOOLEAN;
849 l_tab_access_id ASG_DOWNLOAD.ACCESS_LIST;
850 l_tab_resource_id ASG_DOWNLOAD.USER_LIST;
851
852 l_err_mesg VARCHAR2(1000);
853 BEGIN
854
855 g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
856
857 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
858 jtm_message_log_pkg.Log_Msg
859 ( p_primary_key
860 , g_table_name
861 , 'Entering Delete_Attachments'
862 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
863 END IF;
864
865 IF (p_entity_name = 'CS_INCIDENTS') THEN
866
867 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
868 jtm_message_log_pkg.Log_Msg
869 ( p_primary_key
870 , g_table_name
871 , 'Deleting record for CS_INCIDENTS and resource_id: ' || p_resource_id
872 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
873 END IF;
874
875 OPEN c_SRAtt(p_primary_key, p_resource_id);
876 FETCH c_SRAtt BULK COLLECT INTO l_tab_access_id, l_tab_resource_id;
877 CLOSE c_SRAtt;
878
879 IF l_tab_access_id.COUNT > 0 THEN
880 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
881 jtm_message_log_pkg.Log_Msg
882 ( 0
883 , g_table_name
884 , 'Pushing ' || l_tab_access_id.COUNT || ' updated record(s)'
885 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
886 );
887 END IF;
888
889 l_dummy := asg_download.markdirty(
890 P_PUB_ITEM => g_publication_item_name(1)
891 , P_ACCESSLIST => l_tab_access_id
892 , P_RESOURCELIST => l_tab_resource_id
893 , P_DML_TYPE => 'D'
894 , P_TIMESTAMP => SYSDATE);
895
896 END IF;
897
898 END IF; --IF (p_entity_name = 'CS_INCIDENTS') THEN
899
900
901 IF (p_entity_name = 'JTF_TASKS_B') THEN
902
903 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
904 jtm_message_log_pkg.Log_Msg
905 ( p_primary_key
906 , g_table_name
907 , 'Deleting record for JTF_TASKS_B and resource_id: ' || p_resource_id
908 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
909 END IF;
910
911 OPEN c_TaskAtt(p_primary_key, p_resource_id);
912 FETCH c_TaskAtt BULK COLLECT INTO l_tab_access_id, l_tab_resource_id;
913 CLOSE c_TaskAtt;
914
915 IF l_tab_access_id.COUNT > 0 THEN
916 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
917 jtm_message_log_pkg.Log_Msg
918 ( 0
919 , g_table_name
920 , 'Pushing ' || l_tab_access_id.COUNT || ' updated record(s)'
921 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
922 );
923 END IF;
924
925 l_dummy := asg_download.markdirty(
926 P_PUB_ITEM => g_publication_item_name(1)
927 , P_ACCESSLIST => l_tab_access_id
928 , P_RESOURCELIST => l_tab_resource_id
929 , P_DML_TYPE => 'D'
930 , P_TIMESTAMP => SYSDATE);
931
932 END IF;
933 END IF; --IF (p_entity_name = 'JTF_TASKS_B') THEN
934
935 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
936 jtm_message_log_pkg.Log_Msg
937 ( p_primary_key
938 , g_table_name
939 , 'Exiting Delete_Attachments'
940 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
941 END IF;
942
943 EXCEPTION
944
945 WHEN OTHERS THEN
946 l_err_mesg := 'EXCEPTION IN ' || p_entity_name
947 || ' '|| substr(sqlerrm, 0 , 255);
948 jtm_message_log_pkg.Log_Msg (
949 p_primary_key
950 , 'LOBS_ACC_PKG.DELETE_ATTACHMENTS'
951 , l_err_mesg
952 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
953 RAISE;
954
955 END DELETE_ATTACHMENTS;
956
957 END CSL_LOBS_ACC_PKG;