[Home] [Help]
PACKAGE BODY: APPS.IRC_APL_TRACK_PURGE_PKG
Source
1 package body irc_apl_track_purge_pkg as
2 /* $Header: ircapltrackpurge.pkb 120.0.12000000.1 2007/03/26 13:02:33 vboggava noship $ */
3 -- Package variables
4 --
5 g_package varchar2(33) := 'irc_apl_track_purge_pkg.';
6 --
7 -- ----------------------------------------------------------------------------
8 -- |--------------------------< print_log_msg >-------------------------------|
9 -- ----------------------------------------------------------------------------
10 --
11 procedure print_log_msg
12 (
13 p_print_process in varchar2
14 ) is
15 l_header varchar2(500);
16 l_underline varchar2(500);
17 begin
18
19 if p_print_process = 'APL' then
20 begin
21 l_header := rpad('SNAPSHOT_ID',20)||' '||
22 rpad('PERSON_ID',10);
23 --
24 l_underline := rpad('-',20,'-')||' '||
25 rpad('-',10,'-');
26 --
27 fnd_file.put_line(fnd_file.log, 'call to delete Applicant Profile Snapshot');
28 fnd_file.put_line(fnd_file.log, 'List of Applicant Profile Snapshots deleted');
29 --
30 fnd_file.put_line(fnd_file.log,l_header);
31 fnd_file.put_line(fnd_file.log,l_underline);
32 --
33 end;
34 elsif p_print_process = 'DOC' then
35 begin
36 l_underline := rpad('-',75,'-');
37 fnd_file.put_line(fnd_file.log,l_underline);
38 --
39 l_header := rpad('DOCUMENT_ID',20)||' '||
40 rpad('PERSON_ID',10);
41 --
42 l_underline := rpad('-',20,'-')||' '||
43 rpad('-',10,'-');
44 --
45 fnd_file.put_line(fnd_file.log, 'call to delete Applicant Document Snapshot');
46 fnd_file.put_line(fnd_file.log, 'List of Applicant Document Snapshots deleted');
47 --
48 fnd_file.put_line(fnd_file.log,l_header);
49 fnd_file.put_line(fnd_file.log,l_underline);
50 --
51 end;
52 elsif p_print_process = 'SRC' then
53 begin
54 l_underline := rpad('-',75,'-');
55 fnd_file.put_line(fnd_file.log,l_underline);
56 --
57 l_header := rpad('SAVED_SEARCH_CRITERIA_ID',30)||' '||
58 rpad('VACANCY_ID',10);
59 --
60 l_underline := rpad('-',30,'-')||' '||
61 rpad('-',10,'-');
62 --
63 fnd_file.put_line(fnd_file.log, 'call to delete Saved Search Criteria');
64 fnd_file.put_line(fnd_file.log, 'List of Saved Search Criteria deleted');
65 --
66 fnd_file.put_line(fnd_file.log,l_header);
67 fnd_file.put_line(fnd_file.log,l_underline);
68 --
69 end;
70 elsif p_print_process = 'ACC' then
71 begin
72 l_underline := rpad('-',75,'-');
73 fnd_file.put_line(fnd_file.log,l_underline);
74 --
75 l_header := rpad('APL_PROFILE_ACCESS_ID',30)||' '||
76 rpad('PERSON_ID',10);
77 --
78 l_underline := rpad('-',30,'-')||' '||
79 rpad('-',10,'-');
80 --
81 fnd_file.put_line(fnd_file.log, 'call to delete Applicant Profile Access');
82 fnd_file.put_line(fnd_file.log, 'List of Applicant Profile Access deleted');
83 --
84 fnd_file.put_line(fnd_file.log,l_header);
85 fnd_file.put_line(fnd_file.log,l_underline);
86 --
87 end;
88 end if;
89
90 end print_log_msg;
91 --
92 -- ----------------------------------------------------------------------------
93 -- |--------------------------< purge_records >-------------------------------|
94 -- ----------------------------------------------------------------------------
95 --
96 procedure purge_records
97 (
98 p_months in Number
99 ) is
100 --
101 l_proc varchar2(72) := g_package ||'purge_records';
102 l_effective_date date := sysdate;
103 l_print_info varchar2(32000);
104 l_doc_purge varchar2(10) := 'Y';
105 --
106 cursor csr_apl_profile_snapshot is
107 select aps.profile_snapshot_id, aps.person_id, aps.object_version_number
108 from irc_apl_profile_snapshots aps
109 where months_between(sysdate,aps.creation_date) > p_months;
110 --
111 cursor csr_apl_doc_snapshot is
112 select doc.document_id, doc.type,
113 doc.person_id, doc.party_id, doc.end_date,
114 doc.object_version_number
115 from irc_documents doc
116 where doc.end_date is not null
117 and months_between(sysdate,doc.end_date) > p_months;
118 --
119 cursor csr_saved_search_criteria is
120 select iss.saved_search_criteria_id,
121 iss.vacancy_id, iss.object_version_number
122 from irc_saved_search_criteria iss
123 where months_between(sysdate,iss.creation_date) > p_months;
124 --
125 cursor csr_apl_profile_access is
126 select apa.apl_profile_access_id,
127 apa.person_id,
128 apa.object_version_number
129 from irc_apl_profile_access apa
130 where months_between(sysdate,apa.creation_date) > p_months;
131 --
132 --
133 begin
134 --
135 hr_utility.set_location('Entering Purge Records:'||l_proc, 10);
136 hr_utility.set_location('Delete Applicant Profile Snapshot:'||l_proc, 20);
137
138
139
140 --To Print the Log For the Applicant Profile Snapshot deletion
141 irc_apl_track_purge_pkg.print_log_msg(p_print_process => 'APL');
142 --
143
144 For rec_apl_profile_snapshot In csr_apl_profile_snapshot Loop
145 irc_apl_prfl_snapshots_api.delete_applicant_snapshot
146 ( p_effective_date => l_effective_date
147 ,p_person_id => rec_apl_profile_snapshot.person_id
148 ,p_profile_snapshot_id => rec_apl_profile_snapshot.profile_snapshot_id
149 ,p_object_version_number => rec_apl_profile_snapshot.object_version_number
150 );
151 --
152 hr_utility.set_location('Print Delete Snapshot:'||l_proc,25);
153 l_print_info := rpad(nvl(to_char(rec_apl_profile_snapshot.profile_snapshot_id),' '),20) ||
154 rpad(nvl(to_char(rec_apl_profile_snapshot.person_id),' '),10);
155 --
156 fnd_file.put_line(fnd_file.log,l_print_info);
157
158 End loop;
159 --
160
161 hr_utility.set_location('Delete Applicant Document Snapshot:'||l_proc, 30);
162
163 --To Print the Log For the Document Snapshot deletion
164 irc_apl_track_purge_pkg.print_log_msg(p_print_process => 'DOC');
165 --
166
167 For rec_apl_doc_snapshot In csr_apl_doc_snapshot Loop
168 irc_document_api.delete_document
169 ( p_effective_date => l_effective_date
170 ,p_document_id => rec_apl_doc_snapshot.document_id
171 ,p_object_version_number => rec_apl_doc_snapshot.object_version_number
172 ,p_person_id => rec_apl_doc_snapshot.person_id
173 ,p_party_id => rec_apl_doc_snapshot.party_id
174 ,p_end_date => rec_apl_doc_snapshot.end_date
175 ,p_type => rec_apl_doc_snapshot.type
176 ,p_purge => l_doc_purge
177 );
178 --
179 hr_utility.set_location('Print Delete Document:'||l_proc,35);
180 l_print_info := rpad(nvl(to_char(rec_apl_doc_snapshot.document_id),' '),20) ||
181 rpad(nvl(to_char(rec_apl_doc_snapshot.person_id),' '),10);
182 --
183 fnd_file.put_line(fnd_file.log,l_print_info);
184 End loop;
185 --
186
187 hr_utility.set_location('Delete Saved Search Criteria:'||l_proc, 40);
188
189 --To Print the Log For the Saved Search Criteria deletion
190 irc_apl_track_purge_pkg.print_log_msg(p_print_process => 'SRC');
191 --
192
193 For rec_saved_search_criteria In csr_saved_search_criteria Loop
194 irc_saved_search_criteria_api.delete_search_criteria
195 ( p_vacancy_id => rec_saved_search_criteria.vacancy_id
196 ,p_saved_search_criteria_id => rec_saved_search_criteria.saved_search_criteria_id
197 ,p_object_version_number => rec_saved_search_criteria.object_version_number
198 );
199 --
200 hr_utility.set_location('Print Delete Search Criteria:'||l_proc,45);
201 l_print_info := rpad(nvl(to_char(rec_saved_search_criteria.saved_search_criteria_id),' '),30) ||
202 rpad(nvl(to_char(rec_saved_search_criteria.vacancy_id),' '),10);
203 --
204 fnd_file.put_line(fnd_file.log,l_print_info);
205 End loop;
206 --
207
208 hr_utility.set_location('Delete Applicant Profile Access:'||l_proc, 50);
209
210 --To Print the Log For the Applicant Profile Access deletion
211 irc_apl_track_purge_pkg.print_log_msg(p_print_process => 'ACC');
212 --
213
214 For rec_apl_profile_access In csr_apl_profile_access Loop
215 irc_apl_profile_access_api.delete_apl_profile_access
216 ( p_person_id => rec_apl_profile_access.person_id
217 ,p_apl_profile_access_id => rec_apl_profile_access.apl_profile_access_id
218 ,p_object_version_number => rec_apl_profile_access.object_version_number
219 );
220 --
221 hr_utility.set_location('Print Delete PROFILE ACCESS:'||l_proc,55);
222 l_print_info := rpad(nvl(to_char(rec_apl_profile_access.apl_profile_access_id),' '),30) ||
223 rpad(nvl(to_char(rec_apl_profile_access.person_id),' '),10);
224 --
225 fnd_file.put_line(fnd_file.log,l_print_info);
226 End loop;
227 --
228
229 hr_utility.set_location('Leaving Purge Records:'||l_proc, 100);
230
231 end purge_records;
232 --
233 -- ----------------------------------------------------------------------------
234 -- |--------------------------< purge_record_process >------------------------|
235 -- ----------------------------------------------------------------------------
236 --
237 procedure purge_record_process (errbuf out nocopy varchar2
238 ,retcode out nocopy varchar2
239 ,p_months in number) is
240 --
241 l_proc varchar2(72) := g_package ||'purge_record_process';
242 --
243 begin
244 --
245 hr_utility.set_location('Entering Purge Record Process:'||l_proc, 10);
246 --
247 irc_apl_track_purge_pkg.purge_records
248 (p_months => p_months
249 );
250 --
251 retcode := 0;
252 --
253 hr_utility.set_location('Leaving Purge Record Process:'||l_proc, 70);
254
255 exception
256 when others then
257 --
258 hr_utility.set_location('Leaving Purge Record Process:'||l_proc, 80);
259 rollback;
260 --
261 -- Set the return parameters to indicate failure
262 --
263 errbuf := sqlerrm;
264 retcode := 2;
265 --
266 end purge_record_process;
267 --
268
269 end irc_apl_track_purge_pkg;