[Home] [Help]
PACKAGE BODY: APPS.CAC_NOTE_PURGE_PVT
Source
1 PACKAGE BODY CAC_NOTE_PURGE_PVT AS
2 /* $Header: cacntpvb.pls 120.5 2006/01/02 21:16:34 mpadhiar noship $ */
3 /*=======================================================================+
4 | Copyright (c) 2002 Oracle Corporation Redwood Shores, California, USA|
5 | All rights reserved. |
6 +=======================================================================+
7 | FILENAME |
8 | cacntpvb.pls |
9 | |
10 | DESCRIPTION |
11 | - This package is implemented for Notes purge program that would be |
12 | called from TASK or SR. |
13 | This is private API and should not be called by other team. |
14 | It will be called by CAC_NOTE_PURGE_PUB.PURGE_NOTES program only. |
15 | |
16 | NOTES |
17 | |
18 | Date Developer Change |
19 | ---------- --------------- -----------------------------------|
20 | 08-08-2005 Abhinav Raina Created |
21 | 12-19-2005 Manas Padhiary Changed for Bug no 4612646 |
22 +======================================================================*/
23
24 Procedure purge_notes(
25 p_api_version IN NUMBER,
26 p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false,
27 p_commit IN VARCHAR2 DEFAULT fnd_api.g_false,
28 x_return_status OUT NOCOPY VARCHAR2,
29 x_msg_data OUT NOCOPY VARCHAR2,
30 x_msg_count OUT NOCOPY NUMBER,
31 p_processing_set_id IN NUMBER,
32 p_object_type IN VARCHAR2 )
33
34 IS
35
36 l_api_version CONSTANT NUMBER := 1.0;
37 l_api_name CONSTANT VARCHAR2(30) := 'PURGE_NOTES';
38
39 -- Modified by Manas For bug no 4612646 (for Implementing code Review )
40
41 cursor c_fetch_note_ids is
42 select jtf_note_id
43 from jtf_notes_b
44 where source_object_code = p_object_type
45 and source_object_id in ( select object_id
46 from jtf_object_purge_param_tmp
47 where processing_set_id = p_processing_set_id
48 and ( purge_status is null or purge_status <> 'E'));
49
50 cursor c_fetch_note_contx_ids is
51 select jtf_note_id
52 from jtf_note_contexts
53 where note_context_type = p_object_type
54 and note_context_type_id in ( select object_id
55 from jtf_object_purge_param_tmp
56 where processing_set_id = p_processing_set_id
57 and ( purge_status is null or purge_status <> 'E'));
58
59 TYPE t_tab_num Is Table Of NUMBER;
60 l_tab_note_id t_tab_num:=t_tab_num();
61 l_tab_note_contx_id t_tab_num:=t_tab_num();
62 l_entity_name VARCHAR2(30) := 'JTF_NOTES_B';
63
64 BEGIN
65
66 SAVEPOINT purge_notes;
67 x_return_status := fnd_api.g_ret_sts_success;
68
69 IF NOT fnd_api.compatible_api_call (
70 l_api_version,
71 p_api_version,
72 l_api_name,
73 g_pkg_name
74 )
75 THEN
76 RAISE fnd_api.g_exc_unexpected_error;
77 END IF;
78
79 IF fnd_api.to_boolean (p_init_msg_list)
80 THEN
81 fnd_msg_pub.initialize;
82 END IF;
83
84 Open c_fetch_note_ids;
85 Fetch c_fetch_note_ids Bulk Collect Into l_tab_note_id;
86 Close c_fetch_note_ids;
87
88 IF l_tab_note_id.COUNT > 0
89 THEN
90 --Delete data from JTF_NOTES_B table
91 Forall j In l_tab_note_id.FIRST.. l_tab_note_id.LAST
92 Delete JTF_NOTES_B
93 Where jtf_note_id = l_tab_note_id(j);
94
95 --Delete data from JTF_NOTES_TL table
96 Forall j In l_tab_note_id.FIRST.. l_tab_note_id.LAST
97 Delete JTF_NOTES_TL
98 Where jtf_note_id = l_tab_note_id(j);
99
100 --Delete data from JTF_NOTE_CONTEXTS table
101 Forall j In l_tab_note_id.FIRST.. l_tab_note_id.LAST
102 Delete JTF_NOTE_CONTEXTS
103 Where jtf_note_id = l_tab_note_id(j);
104
105 -- Calling delete attachment API
106
107 -- Modified by Manas For bug no 4612646
108 -- Instead of directly deleting data from tables, Calling the procedure
109 -- fnd_attached_documents2_pkg.delete_attachments
110
111
112 For j In 1.. l_tab_note_id.LAST loop
113 fnd_attached_documents2_pkg.delete_attachments(X_entity_name=>l_entity_name,
114 X_pk1_value =>to_char(l_tab_note_id(j)),
115 X_pk2_value => NULL,
116 X_pk3_value => NULL,
117 X_pk4_value => NULL,
118 X_pk5_value => NULL,
119 X_delete_document_flag =>'Y',
120 X_automatically_added_flag => NULL) ;
121 end loop;
122
123
124
125 END IF;
126
127 Open c_fetch_note_contx_ids;
128 Fetch c_fetch_note_contx_ids Bulk Collect Into l_tab_note_contx_id;
129 Close c_fetch_note_contx_ids;
130
131 IF l_tab_note_contx_id.COUNT > 0
132 THEN
133 --Delete data from JTF_NOTES_B table
134 Forall j In l_tab_note_contx_id.FIRST.. l_tab_note_contx_id.LAST
135 Delete JTF_NOTE_CONTEXTS
136 Where jtf_note_id = l_tab_note_contx_id(j);
137 END IF;
138
139 IF fnd_api.to_boolean(p_commit)
140 THEN
141 COMMIT WORK;
142 END IF;
143
144 EXCEPTION
145 WHEN fnd_api.g_exc_unexpected_error
146 THEN
147 ROLLBACK TO purge_notes;
148 x_return_status := fnd_api.g_ret_sts_unexp_error;
149 fnd_msg_pub.count_and_get (
150 p_count => x_msg_count,
151 p_data => x_msg_data
152 );
153
154 WHEN OTHERS
155 THEN
156 ROLLBACK TO purge_notes;
157 fnd_message.set_name ('JTF', 'CAC_NOTE_UNKNOWN_ERROR');
158 fnd_message.set_token ('P_TEXT', SQLCODE || SQLERRM);
159 fnd_msg_pub.add;
160
161 x_return_status := fnd_api.g_ret_sts_unexp_error;
162 fnd_msg_pub.count_and_get (
163 p_count => x_msg_count,
164 p_data => x_msg_data
165 );
166 END purge_notes ;
167
168 END;