[Home] [Help]
PACKAGE BODY: APPS.GHR_CMP_DEL
Source
1 Package Body ghr_cmp_del as
2 /* $Header: ghcmprhi.pkb 120.0 2005/05/29 02:54:02 appldev noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ghr_cmp_del.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------------------------------< delete_dml >------------------------------|
12 -- ----------------------------------------------------------------------------
13 -- {Start Of Comments}
14 --
15 -- Description:
16 -- This procedure controls the actual dml delete logic. The functions of
17 -- this procedure are as follows:
18 -- 1) To set and unset the g_api_dml status as required (as we are about to
19 -- perform dml).
20 -- 2) To delete the specified row from the schema using the primary key in
21 -- the predicates.
22 -- 3) To trap any constraint violations that may have occurred.
23 -- 4) To raise any other errors.
24 --
25 -- Prerequisites:
26 -- This is an internal private procedure which must be called from the del
27 -- procedure.
28 --
29 -- In Parameters:
30 -- A Pl/Sql record structre.
31 --
32 -- Post Success:
33 -- The specified row will be delete from the schema.
34 --
35 -- Post Failure:
36 -- On the delete dml failure it is important to note that we always reset the
37 -- g_api_dml status to false.
38 -- If a child integrity constraint violation is raised the
39 -- constraint_error procedure will be called.
40 -- If any other error is reported, the error will be raised after the
41 -- g_api_dml status is reset.
42 --
43 -- Developer Implementation Notes:
44 -- None.
45 --
46 -- Access Status:
47 -- Internal Row Handler Use Only.
48 --
49 -- {End Of Comments}
50 -- ----------------------------------------------------------------------------
51 Procedure delete_dml
52 (p_rec in ghr_cmp_shd.g_rec_type
53 ) is
54 --
55 l_proc varchar2(72) := g_package||'delete_dml';
56 --
57 Begin
58 hr_utility.set_location('Entering:'||l_proc, 5);
59 --
60 --
61 --
62 -- Delete the ghr_complaints2 row.
63 --
64 delete from ghr_complaints2
65 where complaint_id = p_rec.complaint_id;
66 --
67 --
68 --
69 hr_utility.set_location(' Leaving:'||l_proc, 10);
70 --
71 Exception
72 When hr_api.child_integrity_violated then
73 -- Child integrity has been violated
74 --
75 ghr_cmp_shd.constraint_error
76 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
77 When Others Then
78 --
79 Raise;
80 End delete_dml;
81 --
82 -- ----------------------------------------------------------------------------
83 -- |------------------------------< pre_delete >------------------------------|
84 -- ----------------------------------------------------------------------------
85 -- {Start Of Comments}
86 --
87 -- Description:
88 -- This private procedure contains any processing which is required before
89 -- the delete dml.
90 --
91 -- Prerequisites:
92 -- This is an internal procedure which is called from the del procedure.
93 --
94 -- In Parameters:
95 -- A Pl/Sql record structre.
96 --
97 -- Post Success:
98 -- Processing continues.
99 --
100 -- Post Failure:
101 -- If an error has occurred, an error message and exception will be raised
102 -- but not handled.
103 --
104 -- Developer Implementation Notes:
105 -- Any pre-processing required before the delete dml is issued should be
106 -- coded within this procedure. It is important to note that any 3rd party
107 -- maintenance should be reviewed before placing in this procedure.
108 --
109 -- Access Status:
110 -- Internal Row Handler Use Only.
111 --
112 -- {End Of Comments}
113 -- ----------------------------------------------------------------------------
114 Procedure pre_delete(p_rec in ghr_cmp_shd.g_rec_type) is
115 --
116 l_proc varchar2(72) := g_package||'pre_delete';
117 --
118 Begin
119 hr_utility.set_location('Entering:'||l_proc, 5);
120 --
121 hr_utility.set_location(' Leaving:'||l_proc, 10);
122 End pre_delete;
123 --
124 -- ----------------------------------------------------------------------------
125 -- |-----------------------------< post_delete >------------------------------|
126 -- ----------------------------------------------------------------------------
127 -- {Start Of Comments}
128 --
129 -- Description:
130 -- This private procedure contains any processing which is required after
131 -- the delete dml.
132 --
133 -- Prerequistes:
134 -- This is an internal procedure which is called from the del procedure.
135 --
136 -- In Parameters:
137 -- A Pl/Sql record structure.
138 --
139 -- Post Success:
140 -- Processing continues.
141 --
142 -- Post Failure:
143 -- If an error has occurred, an error message and exception will be raised
144 -- but not handled.
145 --
146 -- Developer Implementation Notes:
147 -- Any post-processing required after the delete dml is issued should be
148 -- coded within this procedure. It is important to note that any 3rd party
149 -- maintenance should be reviewed before placing in this procedure.
150 --
151 -- Access Status:
152 -- Internal Row Handler Use Only.
153 --
154 -- {End Of Comments}
155 -- -----------------------------------------------------------------------------
156 Procedure post_delete(p_rec in ghr_cmp_shd.g_rec_type) is
157 --
158 l_proc varchar2(72) := g_package||'post_delete';
159 --
160 Begin
161 hr_utility.set_location('Entering:'||l_proc, 5);
162
163 begin
164 --
165 ghr_cmp_rkd.after_delete
166 (p_complaint_id => p_rec.complaint_id
167 ,p_complainant_person_id_o => ghr_cmp_shd.g_old_rec.complainant_person_id
168 ,p_business_group_id_o => ghr_cmp_shd.g_old_rec.business_group_id
169 ,p_docket_number_o => ghr_cmp_shd.g_old_rec.docket_number
170 ,p_stage_o => ghr_cmp_shd.g_old_rec.stage
171 ,p_class_flag_o => ghr_cmp_shd.g_old_rec.class_flag
172 ,p_mixed_flag_o => ghr_cmp_shd.g_old_rec.mixed_flag
173 ,p_consolidated_flag_o => ghr_cmp_shd.g_old_rec.consolidated_flag
174 ,p_remand_flag_o => ghr_cmp_shd.g_old_rec.remand_flag
175 ,p_active_flag_o => ghr_cmp_shd.g_old_rec.active_flag
176 ,p_information_inquiry_o => ghr_cmp_shd.g_old_rec.information_inquiry
177 ,p_pcom_init_o => ghr_cmp_shd.g_old_rec.pcom_init
178 ,p_alleg_incident_o => ghr_cmp_shd.g_old_rec.alleg_incident
179 ,p_alleg_discrim_org_id_o => ghr_cmp_shd.g_old_rec.alleg_discrim_org_id
180 ,p_rr_ltr_date_o => ghr_cmp_shd.g_old_rec.rr_ltr_date
181 ,p_rr_ltr_recvd_o => ghr_cmp_shd.g_old_rec.rr_ltr_recvd
182 ,p_pre_com_elec_o => ghr_cmp_shd.g_old_rec.pre_com_elec
183 --,p_adr_offered_o => ghr_cmp_shd.g_old_rec.adr_offered
184 ,p_class_agent_flag_o => ghr_cmp_shd.g_old_rec.class_agent_flag
185 ,p_pre_com_desc_o => ghr_cmp_shd.g_old_rec.pre_com_desc
186 ,p_counselor_asg_o => ghr_cmp_shd.g_old_rec.counselor_asg
187 ,p_init_counselor_interview_o => ghr_cmp_shd.g_old_rec.init_counselor_interview
188 ,p_anonymity_requested_o => ghr_cmp_shd.g_old_rec.anonymity_requested
189 ,p_counsel_ext_ltr_o => ghr_cmp_shd.g_old_rec.counsel_ext_ltr
190 ,p_traditional_counsel_outcom_o => ghr_cmp_shd.g_old_rec.traditional_counsel_outcome
191 ,p_final_interview_o => ghr_cmp_shd.g_old_rec.final_interview
192 ,p_notice_rtf_recvd_o => ghr_cmp_shd.g_old_rec.notice_rtf_recvd
193 ,p_precom_closed_o => ghr_cmp_shd.g_old_rec.precom_closed
194 ,p_precom_closure_nature_o => ghr_cmp_shd.g_old_rec.precom_closure_nature
195 ,p_counselor_rpt_sub_o => ghr_cmp_shd.g_old_rec.counselor_rpt_sub
196 ,p_hr_office_org_id_o => ghr_cmp_shd.g_old_rec.hr_office_org_id
197 ,p_eeo_office_org_id_o => ghr_cmp_shd.g_old_rec.eeo_office_org_id
198 ,p_serviced_org_id_o => ghr_cmp_shd.g_old_rec.serviced_org_id
199 ,p_formal_com_filed_o => ghr_cmp_shd.g_old_rec.formal_com_filed
200 ,p_ack_ltr_o => ghr_cmp_shd.g_old_rec.ack_ltr
201 ,p_clarification_ltr_date_o => ghr_cmp_shd.g_old_rec.clarification_ltr_date
202 ,p_clarification_response_rec_o => ghr_cmp_shd.g_old_rec.clarification_response_recvd
203 ,p_forwarded_legal_review_o => ghr_cmp_shd.g_old_rec.forwarded_legal_review
204 ,p_returned_from_legal_o => ghr_cmp_shd.g_old_rec.returned_from_legal
205 ,p_letter_type_o => ghr_cmp_shd.g_old_rec.letter_type
206 ,p_letter_date_o => ghr_cmp_shd.g_old_rec.letter_date
207 ,p_letter_recvd_o => ghr_cmp_shd.g_old_rec.letter_recvd
208 ,p_investigation_source_o => ghr_cmp_shd.g_old_rec.investigation_source
209 ,p_investigator_recvd_req_o => ghr_cmp_shd.g_old_rec.investigator_recvd_req
210 ,p_agency_investigator_req_o => ghr_cmp_shd.g_old_rec.agency_investigator_req
211 ,p_investigator_asg_o => ghr_cmp_shd.g_old_rec.investigator_asg
212 ,p_investigation_start_o => ghr_cmp_shd.g_old_rec.investigation_start
213 ,p_investigation_end_o => ghr_cmp_shd.g_old_rec.investigation_end
214 ,p_investigation_extended_o => ghr_cmp_shd.g_old_rec.investigation_extended
215 ,p_invest_extension_desc_o => ghr_cmp_shd.g_old_rec.invest_extension_desc
216 ,p_agency_recvd_roi_o => ghr_cmp_shd.g_old_rec.agency_recvd_roi
217 ,p_comrep_recvd_roi_o => ghr_cmp_shd.g_old_rec.comrep_recvd_roi
218 ,p_options_ltr_date_o => ghr_cmp_shd.g_old_rec.options_ltr_date
219 ,p_comrep_recvd_opt_ltr_o => ghr_cmp_shd.g_old_rec.comrep_recvd_opt_ltr
220 ,p_comrep_opt_ltr_response_o => ghr_cmp_shd.g_old_rec.comrep_opt_ltr_response
221 ,p_resolution_offer_o => ghr_cmp_shd.g_old_rec.resolution_offer
222 ,p_comrep_resol_offer_recvd_o => ghr_cmp_shd.g_old_rec.comrep_resol_offer_recvd
223 ,p_comrep_resol_offer_respons_o => ghr_cmp_shd.g_old_rec.comrep_resol_offer_response
224 ,p_comrep_resol_offer_desc_o => ghr_cmp_shd.g_old_rec.comrep_resol_offer_desc
225 ,p_resol_offer_signed_o => ghr_cmp_shd.g_old_rec.resol_offer_signed
226 ,p_resol_offer_desc_o => ghr_cmp_shd.g_old_rec.resol_offer_desc
227 ,p_hearing_source_o => ghr_cmp_shd.g_old_rec.hearing_source
228 ,p_agency_notified_hearing_o => ghr_cmp_shd.g_old_rec.agency_notified_hearing
229 ,p_eeoc_hearing_docket_num_o => ghr_cmp_shd.g_old_rec.eeoc_hearing_docket_num
230 ,p_hearing_complete_o => ghr_cmp_shd.g_old_rec.hearing_complete
231 ,p_aj_merit_decision_date_o => ghr_cmp_shd.g_old_rec.aj_merit_decision_date
232 ,p_agency_recvd_aj_merit_dec_o => ghr_cmp_shd.g_old_rec.agency_recvd_aj_merit_dec
233 ,p_aj_merit_decision_o => ghr_cmp_shd.g_old_rec.aj_merit_decision
234 ,p_aj_ca_decision_date_o => ghr_cmp_shd.g_old_rec.aj_ca_decision_date
235 ,p_agency_recvd_aj_ca_dec_o => ghr_cmp_shd.g_old_rec.agency_recvd_aj_ca_dec
236 ,p_aj_ca_decision_o => ghr_cmp_shd.g_old_rec.aj_ca_decision
237 ,p_fad_requested_o => ghr_cmp_shd.g_old_rec.fad_requested
238 ,p_merit_fad_o => ghr_cmp_shd.g_old_rec.merit_fad
239 ,p_attorney_fees_fad_o => ghr_cmp_shd.g_old_rec.attorney_fees_fad
240 ,p_comp_damages_fad_o => ghr_cmp_shd.g_old_rec.comp_damages_fad
241 ,p_non_compliance_fad_o => ghr_cmp_shd.g_old_rec.non_compliance_fad
242 ,p_fad_req_recvd_eeo_office_o => ghr_cmp_shd.g_old_rec.fad_req_recvd_eeo_office
243 ,p_fad_req_forwd_to_agency_o => ghr_cmp_shd.g_old_rec.fad_req_forwd_to_agency
244 ,p_agency_recvd_request_o => ghr_cmp_shd.g_old_rec.agency_recvd_request
245 ,p_fad_due_o => ghr_cmp_shd.g_old_rec.fad_due
246 ,p_fad_date_o => ghr_cmp_shd.g_old_rec.fad_date
247 ,p_fad_decision_o => ghr_cmp_shd.g_old_rec.fad_decision
248 --,p_fad_final_action_closure_o => ghr_cmp_shd.g_old_rec.fad_final_action_closure
249 ,p_fad_forwd_to_comrep_o => ghr_cmp_shd.g_old_rec.fad_forwd_to_comrep
250 ,p_fad_recvd_by_comrep_o => ghr_cmp_shd.g_old_rec.fad_recvd_by_comrep
251 ,p_fad_imp_ltr_forwd_to_org_o => ghr_cmp_shd.g_old_rec.fad_imp_ltr_forwd_to_org
252 ,p_fad_decision_forwd_legal_o => ghr_cmp_shd.g_old_rec.fad_decision_forwd_legal
253 ,p_fad_decision_recvd_legal_o => ghr_cmp_shd.g_old_rec.fad_decision_recvd_legal
254 ,p_fa_source_o => ghr_cmp_shd.g_old_rec.fa_source
255 ,p_final_action_due_o => ghr_cmp_shd.g_old_rec.final_action_due
256 --,p_final_action_nature_of_clo_o => ghr_cmp_shd.g_old_rec.final_action_nature_of_closure
257 ,p_final_act_forwd_comrep_o => ghr_cmp_shd.g_old_rec.final_act_forwd_comrep
258 ,p_final_act_recvd_comrep_o => ghr_cmp_shd.g_old_rec.final_act_recvd_comrep
259 ,p_final_action_decision_date_o => ghr_cmp_shd.g_old_rec.final_action_decision_date
260 ,p_final_action_decision_o => ghr_cmp_shd.g_old_rec.final_action_decision
261 ,p_fa_imp_ltr_forwd_to_org_o => ghr_cmp_shd.g_old_rec.fa_imp_ltr_forwd_to_org
262 ,p_fa_decision_forwd_legal_o => ghr_cmp_shd.g_old_rec.fa_decision_forwd_legal
263 ,p_fa_decision_recvd_legal_o => ghr_cmp_shd.g_old_rec.fa_decision_recvd_legal
264 ,p_civil_action_filed_o => ghr_cmp_shd.g_old_rec.civil_action_filed
265 ,p_agency_closure_confirmed_o => ghr_cmp_shd.g_old_rec.agency_closure_confirmed
266 ,p_consolidated_complaint_id_o => ghr_cmp_shd.g_old_rec.consolidated_complaint_id
267 ,p_consolidated_o => ghr_cmp_shd.g_old_rec.consolidated
268 ,p_stage_of_consolidation_o => ghr_cmp_shd.g_old_rec.stage_of_consolidation
269 ,p_comrep_notif_consolidation_o => ghr_cmp_shd.g_old_rec.comrep_notif_consolidation
270 ,p_consolidation_desc_o => ghr_cmp_shd.g_old_rec.consolidation_desc
271 ,p_complaint_closed_o => ghr_cmp_shd.g_old_rec.complaint_closed
272 ,p_nature_of_closure_o => ghr_cmp_shd.g_old_rec.nature_of_closure
273 ,p_complaint_closed_desc_o => ghr_cmp_shd.g_old_rec.complaint_closed_desc
274 ,p_filed_formal_class_o => ghr_cmp_shd.g_old_rec.filed_formal_class
275 ,p_forwd_eeoc_o => ghr_cmp_shd.g_old_rec.forwd_eeoc
276 ,p_aj_cert_decision_date_o => ghr_cmp_shd.g_old_rec.aj_cert_decision_date
277 ,p_aj_cert_decision_recvd_o => ghr_cmp_shd.g_old_rec.aj_cert_decision_recvd
278 ,p_aj_cert_decision_o => ghr_cmp_shd.g_old_rec.aj_cert_decision
279 ,p_class_members_notified_o => ghr_cmp_shd.g_old_rec.class_members_notified
280 ,p_number_of_complaintants_o => ghr_cmp_shd.g_old_rec.number_of_complaintants
281 ,p_class_hearing_o => ghr_cmp_shd.g_old_rec.class_hearing
282 ,p_aj_dec_o => ghr_cmp_shd.g_old_rec.aj_dec
283 ,p_agency_recvd_aj_dec_o => ghr_cmp_shd.g_old_rec.agency_recvd_aj_dec
284 ,p_aj_decision_o => ghr_cmp_shd.g_old_rec.aj_decision
285 ,p_object_version_number_o => ghr_cmp_shd.g_old_rec.object_version_number
286 ,p_agency_brief_eeoc_o => ghr_cmp_shd.g_old_rec.agency_brief_eeoc
287 ,p_agency_notif_of_civil_acti_o => ghr_cmp_shd.g_old_rec.agency_notif_of_civil_action
288 ,p_fad_source_o => ghr_cmp_shd.g_old_rec.fad_source
289 ,p_agency_files_forwarded_eeo_o => ghr_cmp_shd.g_old_rec.agency_files_forwarded_eeoc
290 ,p_hearing_req_o => ghr_cmp_shd.g_old_rec.hearing_req
291 ,p_agency_code_o => ghr_cmp_shd.g_old_rec.agency_code
292 ,p_audited_by_o => ghr_cmp_shd.g_old_rec.audited_by
293 ,p_record_received_o => ghr_cmp_shd.g_old_rec.record_received
294 ,p_attribute_category_o => ghr_cmp_shd.g_old_rec.attribute_category
295 ,p_attribute1_o => ghr_cmp_shd.g_old_rec.attribute1
296 ,p_attribute2_o => ghr_cmp_shd.g_old_rec.attribute2
297 ,p_attribute3_o => ghr_cmp_shd.g_old_rec.attribute3
298 ,p_attribute4_o => ghr_cmp_shd.g_old_rec.attribute4
299 ,p_attribute5_o => ghr_cmp_shd.g_old_rec.attribute5
300 ,p_attribute6_o => ghr_cmp_shd.g_old_rec.attribute6
301 ,p_attribute7_o => ghr_cmp_shd.g_old_rec.attribute7
302 ,p_attribute8_o => ghr_cmp_shd.g_old_rec.attribute8
303 ,p_attribute9_o => ghr_cmp_shd.g_old_rec.attribute9
304 ,p_attribute10_o => ghr_cmp_shd.g_old_rec.attribute10
305 ,p_attribute11_o => ghr_cmp_shd.g_old_rec.attribute11
306 ,p_attribute12_o => ghr_cmp_shd.g_old_rec.attribute12
307 ,p_attribute13_o => ghr_cmp_shd.g_old_rec.attribute13
308 ,p_attribute14_o => ghr_cmp_shd.g_old_rec.attribute14
309 ,p_attribute15_o => ghr_cmp_shd.g_old_rec.attribute15
310 ,p_attribute16_o => ghr_cmp_shd.g_old_rec.attribute16
311 ,p_attribute17_o => ghr_cmp_shd.g_old_rec.attribute17
312 ,p_attribute18_o => ghr_cmp_shd.g_old_rec.attribute18
313 ,p_attribute19_o => ghr_cmp_shd.g_old_rec.attribute19
314 ,p_attribute20_o => ghr_cmp_shd.g_old_rec.attribute20
315 ,p_attribute21_o => ghr_cmp_shd.g_old_rec.attribute21
316 ,p_attribute22_o => ghr_cmp_shd.g_old_rec.attribute22
317 ,p_attribute23_o => ghr_cmp_shd.g_old_rec.attribute23
318 ,p_attribute24_o => ghr_cmp_shd.g_old_rec.attribute24
319 ,p_attribute25_o => ghr_cmp_shd.g_old_rec.attribute25
320 ,p_attribute26_o => ghr_cmp_shd.g_old_rec.attribute26
321 ,p_attribute27_o => ghr_cmp_shd.g_old_rec.attribute27
322 ,p_attribute28_o => ghr_cmp_shd.g_old_rec.attribute28
323 ,p_attribute29_o => ghr_cmp_shd.g_old_rec.attribute29
324 ,p_attribute30_o => ghr_cmp_shd.g_old_rec.attribute30
325 );
326 --
327 exception
328 --
329 when hr_api.cannot_find_prog_unit then
330 --
331 hr_api.cannot_find_prog_unit_error
332 (p_module_name => 'GHR_COMPLAINTS2'
333 ,p_hook_type => 'AD');
334 --
335 end;
336 --
337 hr_utility.set_location(' Leaving:'||l_proc, 10);
338 End post_delete;
339 --
340 -- ----------------------------------------------------------------------------
341 -- |---------------------------------< del >----------------------------------|
342 -- ----------------------------------------------------------------------------
343 Procedure del
344 (p_rec in ghr_cmp_shd.g_rec_type
345 ) is
346 --
347 l_proc varchar2(72) := g_package||'del';
348 --
349 Begin
350 hr_utility.set_location('Entering:'||l_proc, 5);
351 --
352 -- We must lock the row which we need to delete.
353 --
354 ghr_cmp_shd.lck
355 (p_rec.complaint_id
356 ,p_rec.object_version_number
357 );
358 --
359 -- Call the supporting delete validate operation
360 --
361 ghr_cmp_bus.delete_validate(p_rec);
362 --
363 -- Call the supporting pre-delete operation
364 --
365 ghr_cmp_del.pre_delete(p_rec);
366 --
367 -- Delete the row.
368 --
369 ghr_cmp_del.delete_dml(p_rec);
370 --
371 -- Call the supporting post-delete operation
372 --
373 ghr_cmp_del.post_delete(p_rec);
374 --
375 End del;
376 --
377 -- ----------------------------------------------------------------------------
378 -- |---------------------------------< del >----------------------------------|
379 -- ----------------------------------------------------------------------------
380 Procedure del
381 (p_complaint_id in number
382 ,p_object_version_number in number
383 ) is
384 --
385 l_rec ghr_cmp_shd.g_rec_type;
386 l_proc varchar2(72) := g_package||'del';
387 --
388 Begin
389 hr_utility.set_location('Entering:'||l_proc, 5);
390 --
391 -- As the delete procedure accepts a plsql record structure we do need to
392 -- convert the arguments into the record structure.
393 -- We don't need to call the supplied conversion argument routine as we
394 -- only need a few attributes.
395 --
396 l_rec.complaint_id := p_complaint_id;
397 l_rec.object_version_number := p_object_version_number;
398 --
399 -- Having converted the arguments into the ghr_cmp_rec
400 -- plsql record structure we must call the corresponding entity
401 -- business process
402 --
403 ghr_cmp_del.del(l_rec);
404 --
405 hr_utility.set_location(' Leaving:'||l_proc, 10);
406 End del;
407 --
408 end ghr_cmp_del;