1 Package Body pqh_documents_wrapper As
2 /* $Header: pqdocwrp.pkb 120.0 2005/05/29 01:50:31 appldev noship $ */
3 --
4 -- Package variables
5 --
6 g_package varchar2(33) := 'pqh_documents_wrapper.';
7 -- ----------------------------------------------------------------------------
8 -- |----------------------------< delete_document >---------------------------|
9 -- ----------------------------------------------------------------------------
10 PROCEDURE delete_document
11 (p_validate in number default hr_api.g_false_num
12 ,p_effective_date in date default trunc(sysdate)
13 ,p_datetrack_mode in varchar2
14 ,p_document_id in number
15 ,p_object_version_number in out NOCOPY number
16 ,p_effective_start_date out NOCOPY date
17 ,p_effective_end_date out NOCOPY date
18 ,p_return_status out NOCOPY varchar2
19 ) is
20 --
21 -- Define Cursor over Here
22 -- This cursor fetchs all records from child table irrespective of dates
23
24 Cursor CurRetrieveChildRecord is
25 Select document_attribute_id, object_version_number,effective_start_date
26 From pqh_document_attributes_f
27 Where document_id = p_document_id
28 AND p_effective_date between effective_start_date and effective_end_date;
29
30 Cursor csr_child_records_for_zap IS
31 Select document_attribute_id, object_version_number,effective_start_date
32 From pqh_document_attributes_f daf
33 Where daf.document_id = p_document_id
34 and rowid = (select min(rowid)
35 From pqh_document_attributes_f
36 Where document_id = p_document_id
37 and document_attribute_id = daf.document_attribute_id);
38
39 --
40 Cursor csr_child_attributes IS
41 Select document_attribute_id,effective_start_date
42 from pqh_document_attributes_f pdaf
43 where pdaf.document_id = p_document_id
44 and pdaf.effective_start_date >
45 (
46 select (effective_end_date) from pqh_documents_f pdf where p_effective_date between effective_start_date and effective_end_date
47 and pdf.document_id = pdaf.document_id
48 );
49
50 Cursor csr_future_versions IS
51 Select 'PRESENT' from pqh_documents_f
52 where document_id =p_document_id
53 and p_effective_date between effective_start_date and effective_end_date
54 and effective_end_date <> hr_general.end_of_time;
55 --
56 --
57 Cursor csr_future_eff_child_recs IS
58 Select max(effective_start_date) from
59 (
60 Select effective_start_date
61 from pqh_document_attributes_f
62 where document_id =p_document_id
63 and effective_start_date > p_effective_date
64 and document_attribute_id NOT IN (select document_attribute_id
65 from pqh_document_attributes_f
66 where document_id =p_document_id
67 and p_effective_date between effective_start_date and effective_end_date)
68 );
69 --
70 Cursor csr_future_end_dt_child_recs IS
71 Select max(effective_end_date)
72 from (
73 Select *
74 From pqh_document_attributes_f
75 Where document_id = p_document_id
76 And p_effective_date between effective_start_date and effective_end_date
77 AND p_effective_date < decode(effective_end_date , hr_general.end_of_time,p_effective_date-1,effective_end_date)
78 );
79 --
80 -- Variables for API Boolean parameters
81 l_validate boolean;
82 --
83 -- Variables for IN/OUT parameters
84 l_object_version_number number;
85 --
86 -- Other variables
87 l_proc varchar2(72) := g_package ||'delete_document';
88 l_ovn number;
89 l_effective_start_date date;
90 l_future_versions VARCHAR2(100);
91 l_eff_disp_date date:= null;
92 l_max_future_end_dt date := null;
93 Begin
94 hr_utility.set_location(' Entering:' || l_proc,10);
95 --
96 -- Issue a savepoint
97 --
98 savepoint delete_document_swi;
99 --
100 -- Initialise Multiple Message Detection
101 --
102 hr_multi_message.enable_message_list;
103 --
104 -- Remember IN OUT parameter IN values
105 --
106 l_object_version_number := p_object_version_number;
107 --
108 -- Convert constant values to their corresponding boolean value
109 --
110 l_validate :=
111 hr_api.constant_to_boolean
112 (p_constant_value => p_validate);
113 --
114 -- Register Surrogate ID or user key values
115
116 --
117 -- Check any future versions are present in the system for the selected document
118 -- as on that effective date or not.
119
120 IF ( p_datetrack_mode = 'DELETE' ) THEN
121 --
122 OPEN csr_future_versions;
123 Fetch csr_future_versions into l_future_versions;
124 CLOSE csr_future_versions;
125
126 IF (l_future_versions = 'PRESENT') THEN
127 --
128 --
129 fnd_message.set_name('PQH','PQH_SS_DELETE_MODE_INVALID');
130 fnd_message.raise_error;
131
132 --
133 ELSE -- No Futuer versions of Record is present .
134 -- Then check for child records which has Future Effective Start Date
135 OPEN csr_future_eff_child_recs;
136 FETCH csr_future_eff_child_recs into l_eff_disp_date;
137 CLOSE csr_future_eff_child_recs;
138
139 if (l_eff_disp_date is not null) then
140 fnd_message.set_name('PQH','PQH_SS_FTR_CHLD_RECS_PRSNT');
141 fnd_message.set_token('DATE_VALUE',l_eff_disp_date);
142 fnd_message.raise_error;
143 end if;
144
145 END IF;
146
147 OPEN csr_future_end_dt_child_recs;
148 FETCH csr_future_end_dt_child_recs into l_max_future_end_dt;
149 CLOSE csr_future_end_dt_child_recs;
150
151 l_max_future_end_dt :=l_max_future_end_dt+1;
152
153 IF (l_max_future_end_dt is not null) THEN
154 fnd_message.set_name('PQH','PQH_SS_FTR_ENDTD_CHLD_RECS');
155 fnd_message.set_token('DATE_VALUE',l_max_future_end_dt);
156 fnd_message.raise_error;
157 END IF;
158 --
159 --
160 For docAttributeCursorRow in CurRetrieveChildRecord
161 loop
162 l_ovn := docAttributeCursorRow.object_version_number;
163 l_effective_start_date :=docAttributeCursorRow.effective_start_date;
164
165 pqh_document_attributes_api.delete_document_attribute
166 (p_validate => l_validate
167 ,p_effective_date => p_effective_date
168 ,p_datetrack_mode => p_datetrack_mode
169 ,p_document_attribute_id => docAttributeCursorRow.document_attribute_id
170 ,p_object_version_number => l_ovn
171 ,p_effective_start_date => p_effective_start_date
172 ,p_effective_end_date => p_effective_end_date
173 );
174 hr_utility.set_location(' p_effective_start_date' ||p_effective_start_date,21);
175 hr_utility.set_location(' p_effective_end_date' ||p_effective_end_date,21);
176 end loop;
177 --
178 --
179 ELSIF ( p_datetrack_mode = 'ZAP' ) THEN
180 --
181 For docAttributeCursorRow in csr_child_records_for_zap
182 loop
183 l_ovn := docAttributeCursorRow.object_version_number;
184 l_effective_start_date :=docAttributeCursorRow.effective_start_date;
185
186 pqh_document_attributes_api.delete_document_attribute
187 (p_validate => l_validate
188 ,p_effective_date => l_effective_start_date
189 ,p_datetrack_mode => p_datetrack_mode
190 ,p_document_attribute_id => docAttributeCursorRow.document_attribute_id
191 ,p_object_version_number => l_ovn
192 ,p_effective_start_date => p_effective_start_date
193 ,p_effective_end_date => p_effective_end_date
194 );
195 hr_utility.set_location(' p_effective_start_date' ||p_effective_start_date,21);
196 hr_utility.set_location(' p_effective_end_date' ||p_effective_end_date,21);
197 end loop;
198
199 ELSIF ( p_datetrack_mode = 'FUTURE_CHANGE' ) THEN
200 For docAttributeCsr in csr_child_attributes
201 loop
202 delete from pqh_document_attributes_f where
203 document_attribute_id = docAttributeCsr.document_attribute_id
204 and effective_start_date >= docAttributeCsr.effective_start_date;
205 end loop;
206
207 --
208 END IF;
209
210
211 --
212 -- Call API for dependent Child Records
213
214 --
215 -- Call API
216 --
217 pqh_documents_api.delete_document
218 (p_validate => l_validate
219 ,p_effective_date => p_effective_date
220 ,p_datetrack_mode => p_datetrack_mode
221 ,p_document_id => p_document_id
222 ,p_object_version_number => p_object_version_number
223 ,p_effective_start_date => p_effective_start_date
224 ,p_effective_end_date => p_effective_end_date
225 );
226 --
227 -- If p_datetrack_mode is delete then we update the document attributes to be
228 -- valid till end of time.
229 --
230 IF ( p_datetrack_mode = 'DELETE' ) THEN
231 For docAttributeCursor in CurRetrieveChildRecord
232 loop
233
234 update pqh_document_attributes_f t
235 set t.effective_end_date = hr_general.end_of_time
236 where t.document_attribute_id = docAttributeCursor.document_attribute_id
237 and effective_end_date = p_effective_date
238 and effective_start_date = docAttributeCursor.effective_start_date;
239
240 end loop;
241 END IF;
242 --
243 -- Convert API warning boolean parameter values to specific
244 -- messages and add them to Multiple Message List
245 --
246 --
247 -- Convert API non-warning boolean parameter values
248 --
249 --
250 -- Derive the API return status value based on whether
251 -- messages of any type exist in the Multiple Message List.
252 -- Also disable Multiple Message Detection.
253 --
254 p_return_status := hr_multi_message.get_return_status_disable;
255 hr_utility.set_location(' Leaving:' || l_proc,20);
256 --
257 exception
258 when hr_multi_message.error_message_exist then
259 --
260 -- Catch the Multiple Message List exception which
261 -- indicates API processing has been aborted because
262 -- at least one message exists in the list.
263 --
264 rollback to delete_document_swi;
265 --
266 -- Reset IN OUT parameters and set OUT parameters
267 --
268 p_object_version_number := l_object_version_number;
269 p_effective_start_date := null;
270 p_effective_end_date := null;
271 p_return_status := hr_multi_message.get_return_status_disable;
272 hr_utility.set_location(' Leaving:' || l_proc, 30);
273 when others then
274 --
275 -- When Multiple Message Detection is enabled catch
276 -- any Application specific or other unexpected
277 -- exceptions. Adding appropriate details to the
278 -- Multiple Message List. Otherwise re-raise the
279 -- error.
280 --
281 rollback to delete_document_swi;
282 if hr_multi_message.unexpected_error_add(l_proc) then
283 hr_utility.set_location(' Leaving:' || l_proc,40);
284 raise;
285 end if;
286 --
287 -- Reset IN OUT and set OUT parameters
288 --
289 p_object_version_number := l_object_version_number;
290 p_effective_start_date := null;
291 p_effective_end_date := null;
292 p_return_status := hr_multi_message.get_return_status_disable;
293 hr_utility.set_location(' Leaving:' || l_proc,50);
294 end delete_document;
295 --
296 end pqh_documents_wrapper;
[Home] [Help]