[Home] [Help]
PACKAGE BODY: APPS.GHR_CMP_INS
Source
1 Package Body ghr_cmp_ins 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_ins.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------------------------------< insert_dml >------------------------------|
12 -- ----------------------------------------------------------------------------
13 -- {Start Of Comments}
14 --
15 -- Description:
16 -- This procedure controls the actual dml insert logic. The processing of
17 -- this procedure are as follows:
18 -- 1) Initialise the object_version_number to 1 if the object_version_number
19 -- is defined as an attribute for this entity.
20 -- 2) To set and unset the g_api_dml status as required (as we are about to
21 -- perform dml).
22 -- 3) To insert the row into the schema.
23 -- 4) To trap any constraint violations that may have occurred.
24 -- 5) To raise any other errors.
25 --
26 -- Prerequisites:
27 -- This is an internal private procedure which must be called from the ins
28 -- procedure and must have all mandatory attributes set (except the
29 -- object_version_number which is initialised within this procedure).
30 --
31 -- In Parameters:
32 -- A Pl/Sql record structre.
33 --
34 -- Post Success:
35 -- The specified row will be inserted into the schema.
36 --
37 -- Post Failure:
38 -- On the insert dml failure it is important to note that we always reset the
39 -- g_api_dml status to false.
40 -- If a check, unique or parent integrity constraint violation is raised the
41 -- constraint_error procedure will be called.
42 -- If any other error is reported, the error will be raised after the
43 -- g_api_dml status is reset.
44 --
45 -- Developer Implementation Notes:
46 -- None.
47 --
48 -- Access Status:
49 -- Internal Row Handler Use Only.
50 --
51 -- {End Of Comments}
52 -- ----------------------------------------------------------------------------
53 Procedure insert_dml
54 (p_rec in out nocopy ghr_cmp_shd.g_rec_type
55 ) is
56 --
57 l_proc varchar2(72) := g_package||'insert_dml';
58 --
59 Begin
60 hr_utility.set_location('Entering:'||l_proc, 5);
61 p_rec.object_version_number := 1; -- Initialise the object version
62 --
63 --
64 --
65 -- Insert the row into: ghr_complaints2
66 --
67 insert into ghr_complaints2
68 (complaint_id
69 ,complainant_person_id
70 ,business_group_id
71 ,docket_number
72 ,stage
73 ,class_flag
74 ,mixed_flag
75 ,consolidated_flag
76 ,remand_flag
77 ,active_flag
78 ,information_inquiry
79 ,pcom_init
80 ,alleg_incident
81 ,alleg_discrim_org_id
82 ,rr_ltr_date
83 ,rr_ltr_recvd
84 ,pre_com_elec
85 --,adr_offered
86 ,class_agent_flag
87 ,pre_com_desc
88 ,counselor_asg
89 ,init_counselor_interview
90 ,anonymity_requested
91 ,counsel_ext_ltr
92 ,traditional_counsel_outcome
93 ,final_interview
94 ,notice_rtf_recvd
95 ,precom_closed
96 ,precom_closure_nature
97 ,counselor_rpt_sub
98 ,hr_office_org_id
99 ,eeo_office_org_id
100 ,serviced_org_id
101 ,formal_com_filed
102 ,ack_ltr
103 ,clarification_ltr_date
104 ,clarification_response_recvd
105 ,forwarded_legal_review
106 ,returned_from_legal
107 ,letter_type
108 ,letter_date
109 ,letter_recvd
110 ,investigation_source
111 ,investigator_recvd_req
112 ,agency_investigator_req
113 ,investigator_asg
114 ,investigation_start
115 ,investigation_end
116 ,investigation_extended
117 ,invest_extension_desc
118 ,agency_recvd_roi
119 ,comrep_recvd_roi
120 ,options_ltr_date
121 ,comrep_recvd_opt_ltr
122 ,comrep_opt_ltr_response
123 ,resolution_offer
124 ,comrep_resol_offer_recvd
125 ,comrep_resol_offer_response
126 ,comrep_resol_offer_desc
127 ,resol_offer_signed
128 ,resol_offer_desc
129 ,hearing_source
130 ,agency_notified_hearing
131 ,eeoc_hearing_docket_num
132 ,hearing_complete
133 ,aj_merit_decision_date
134 ,agency_recvd_aj_merit_dec
135 ,aj_merit_decision
136 ,aj_ca_decision_date
137 ,agency_recvd_aj_ca_dec
138 ,aj_ca_decision
139 ,fad_requested
140 ,merit_fad
141 ,attorney_fees_fad
142 ,comp_damages_fad
143 ,non_compliance_fad
144 ,fad_req_recvd_eeo_office
145 ,fad_req_forwd_to_agency
146 ,agency_recvd_request
147 ,fad_due
148 ,fad_date
149 ,fad_decision
150 -- ,fad_final_action_closure
151 ,fad_forwd_to_comrep
152 ,fad_recvd_by_comrep
153 ,fad_imp_ltr_forwd_to_org
154 ,fad_decision_forwd_legal
155 ,fad_decision_recvd_legal
156 ,fa_source
157 ,final_action_due
158 --,final_action_nature_of_closure
159 ,final_act_forwd_comrep
160 ,final_act_recvd_comrep
161 ,final_action_decision_date
162 ,final_action_decision
163 ,fa_imp_ltr_forwd_to_org
164 ,fa_decision_forwd_legal
165 ,fa_decision_recvd_legal
166 ,civil_action_filed
167 ,agency_closure_confirmed
168 ,consolidated_complaint_id
169 ,consolidated
170 ,stage_of_consolidation
171 ,comrep_notif_consolidation
172 ,consolidation_desc
173 ,complaint_closed
174 ,nature_of_closure
175 ,complaint_closed_desc
176 ,filed_formal_class
177 ,forwd_eeoc
178 ,aj_cert_decision_date
179 ,aj_cert_decision_recvd
180 ,aj_cert_decision
181 ,class_members_notified
182 ,number_of_complaintants
183 ,class_hearing
184 ,aj_dec
185 ,agency_recvd_aj_dec
186 ,aj_decision
187 ,object_version_number
188 ,agency_brief_eeoc
189 ,agency_notif_of_civil_action
190 ,fad_source
191 ,agency_files_forwarded_eeoc
192 ,hearing_req
193 ,agency_code
194 ,audited_by
195 ,record_received
196 ,attribute_category
197 ,attribute1
198 ,attribute2
199 ,attribute3
200 ,attribute4
201 ,attribute5
202 ,attribute6
203 ,attribute7
204 ,attribute8
205 ,attribute9
206 ,attribute10
207 ,attribute11
208 ,attribute12
209 ,attribute13
210 ,attribute14
211 ,attribute15
212 ,attribute16
213 ,attribute17
214 ,attribute18
215 ,attribute19
216 ,attribute20
217 ,attribute21
218 ,attribute22
219 ,attribute23
220 ,attribute24
221 ,attribute25
222 ,attribute26
223 ,attribute27
224 ,attribute28
225 ,attribute29
226 ,attribute30
227 )
228 Values
229 (p_rec.complaint_id
230 ,p_rec.complainant_person_id
231 ,p_rec.business_group_id
232 ,p_rec.docket_number
233 ,p_rec.stage
234 ,p_rec.class_flag
235 ,p_rec.mixed_flag
236 ,p_rec.consolidated_flag
237 ,p_rec.remand_flag
238 ,p_rec.active_flag
239 ,p_rec.information_inquiry
240 ,p_rec.pcom_init
241 ,p_rec.alleg_incident
242 ,p_rec.alleg_discrim_org_id
243 ,p_rec.rr_ltr_date
244 ,p_rec.rr_ltr_recvd
245 ,p_rec.pre_com_elec
246 --,p_rec.adr_offered
247 ,p_rec.class_agent_flag
248 ,p_rec.pre_com_desc
249 ,p_rec.counselor_asg
250 ,p_rec.init_counselor_interview
251 ,p_rec.anonymity_requested
252 ,p_rec.counsel_ext_ltr
253 ,p_rec.traditional_counsel_outcome
254 ,p_rec.final_interview
255 ,p_rec.notice_rtf_recvd
256 ,p_rec.precom_closed
257 ,p_rec.precom_closure_nature
258 ,p_rec.counselor_rpt_sub
259 ,p_rec.hr_office_org_id
260 ,p_rec.eeo_office_org_id
261 ,p_rec.serviced_org_id
262 ,p_rec.formal_com_filed
263 ,p_rec.ack_ltr
264 ,p_rec.clarification_ltr_date
265 ,p_rec.clarification_response_recvd
266 ,p_rec.forwarded_legal_review
267 ,p_rec.returned_from_legal
268 ,p_rec.letter_type
269 ,p_rec.letter_date
270 ,p_rec.letter_recvd
271 ,p_rec.investigation_source
272 ,p_rec.investigator_recvd_req
273 ,p_rec.agency_investigator_req
274 ,p_rec.investigator_asg
275 ,p_rec.investigation_start
276 ,p_rec.investigation_end
277 ,p_rec.investigation_extended
278 ,p_rec.invest_extension_desc
279 ,p_rec.agency_recvd_roi
280 ,p_rec.comrep_recvd_roi
281 ,p_rec.options_ltr_date
282 ,p_rec.comrep_recvd_opt_ltr
283 ,p_rec.comrep_opt_ltr_response
284 ,p_rec.resolution_offer
285 ,p_rec.comrep_resol_offer_recvd
286 ,p_rec.comrep_resol_offer_response
287 ,p_rec.comrep_resol_offer_desc
288 ,p_rec.resol_offer_signed
289 ,p_rec.resol_offer_desc
290 ,p_rec.hearing_source
291 ,p_rec.agency_notified_hearing
292 ,p_rec.eeoc_hearing_docket_num
293 ,p_rec.hearing_complete
294 ,p_rec.aj_merit_decision_date
295 ,p_rec.agency_recvd_aj_merit_dec
296 ,p_rec.aj_merit_decision
297 ,p_rec.aj_ca_decision_date
298 ,p_rec.agency_recvd_aj_ca_dec
299 ,p_rec.aj_ca_decision
300 ,p_rec.fad_requested
301 ,p_rec.merit_fad
302 ,p_rec.attorney_fees_fad
303 ,p_rec.comp_damages_fad
304 ,p_rec.non_compliance_fad
305 ,p_rec.fad_req_recvd_eeo_office
306 ,p_rec.fad_req_forwd_to_agency
307 ,p_rec.agency_recvd_request
308 ,p_rec.fad_due
309 ,p_rec.fad_date
310 ,p_rec.fad_decision
311 -- ,p_rec.fad_final_action_closure
312 ,p_rec.fad_forwd_to_comrep
313 ,p_rec.fad_recvd_by_comrep
314 ,p_rec.fad_imp_ltr_forwd_to_org
315 ,p_rec.fad_decision_forwd_legal
316 ,p_rec.fad_decision_recvd_legal
317 ,p_rec.fa_source
318 ,p_rec.final_action_due
319 -- ,p_rec.final_action_nature_of_closure
320 ,p_rec.final_act_forwd_comrep
321 ,p_rec.final_act_recvd_comrep
322 ,p_rec.final_action_decision_date
323 ,p_rec.final_action_decision
324 ,p_rec.fa_imp_ltr_forwd_to_org
325 ,p_rec.fa_decision_forwd_legal
326 ,p_rec.fa_decision_recvd_legal
327 ,p_rec.civil_action_filed
328 ,p_rec.agency_closure_confirmed
329 ,p_rec.consolidated_complaint_id
330 ,p_rec.consolidated
331 ,p_rec.stage_of_consolidation
332 ,p_rec.comrep_notif_consolidation
333 ,p_rec.consolidation_desc
334 ,p_rec.complaint_closed
335 ,p_rec.nature_of_closure
336 ,p_rec.complaint_closed_desc
337 ,p_rec.filed_formal_class
338 ,p_rec.forwd_eeoc
339 ,p_rec.aj_cert_decision_date
340 ,p_rec.aj_cert_decision_recvd
341 ,p_rec.aj_cert_decision
342 ,p_rec.class_members_notified
343 ,p_rec.number_of_complaintants
344 ,p_rec.class_hearing
345 ,p_rec.aj_dec
346 ,p_rec.agency_recvd_aj_dec
347 ,p_rec.aj_decision
348 ,p_rec.object_version_number
349 ,p_rec.agency_brief_eeoc
350 ,p_rec.agency_notif_of_civil_action
351 ,p_rec.fad_source
352 ,p_rec.agency_files_forwarded_eeoc
353 ,p_rec.hearing_req
354 ,p_rec.agency_code
355 ,p_rec.audited_by
356 ,p_rec.record_received
357 ,p_rec.attribute_category
358 ,p_rec.attribute1
359 ,p_rec.attribute2
360 ,p_rec.attribute3
361 ,p_rec.attribute4
362 ,p_rec.attribute5
363 ,p_rec.attribute6
364 ,p_rec.attribute7
365 ,p_rec.attribute8
366 ,p_rec.attribute9
367 ,p_rec.attribute10
368 ,p_rec.attribute11
369 ,p_rec.attribute12
370 ,p_rec.attribute13
371 ,p_rec.attribute14
372 ,p_rec.attribute15
373 ,p_rec.attribute16
374 ,p_rec.attribute17
375 ,p_rec.attribute18
376 ,p_rec.attribute19
377 ,p_rec.attribute20
378 ,p_rec.attribute21
379 ,p_rec.attribute22
380 ,p_rec.attribute23
381 ,p_rec.attribute24
382 ,p_rec.attribute25
383 ,p_rec.attribute26
384 ,p_rec.attribute27
385 ,p_rec.attribute28
386 ,p_rec.attribute29
387 ,p_rec.attribute30
388 );
389
390 --
391 --
392 --
393 hr_utility.set_location(' Leaving:'||l_proc, 10);
394 Exception
395 When hr_api.check_integrity_violated Then
396 -- A check constraint has been violated
397 --
398 ghr_cmp_shd.constraint_error
399 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
400 When hr_api.parent_integrity_violated Then
401 -- Parent integrity has been violated
402 --
403 ghr_cmp_shd.constraint_error
404 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
405 When hr_api.unique_integrity_violated Then
406 -- Unique integrity has been violated
407 --
408 ghr_cmp_shd.constraint_error
409 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
410 When Others Then
411 --
412 Raise;
413 End insert_dml;
414 --
415 -- ----------------------------------------------------------------------------
416 -- |------------------------------< pre_insert >------------------------------|
417 -- ----------------------------------------------------------------------------
418 -- {Start Of Comments}
419 --
420 -- Description:
421 -- This private procedure contains any processing which is required before
422 -- the insert dml. Presently, if the entity has a corresponding primary
423 -- key which is maintained by an associating sequence, the primary key for
424 -- the entity will be populated with the next sequence value in
425 -- preparation for the insert dml.
426 --
427 -- Prerequisites:
428 -- This is an internal procedure which is called from the ins procedure.
429 --
430 -- In Parameters:
431 -- A Pl/Sql record structre.
432 --
433 -- Post Success:
434 -- Processing continues.
435 --
436 -- Post Failure:
437 -- If an error has occurred, an error message and exception will be raised
438 -- but not handled.
439 --
440 -- Developer Implementation Notes:
441 -- Any pre-processing required before the insert dml is issued should be
442 -- coded within this procedure. As stated above, a good example is the
443 -- generation of a primary key number via a corresponding sequence.
444 -- It is important to note that any 3rd party maintenance should be reviewed
445 -- before placing in this procedure.
446 --
447 -- Access Status:
448 -- Internal Row Handler Use Only.
449 --
450 -- {End Of Comments}
451 -- ----------------------------------------------------------------------------
452 Procedure pre_insert
453 (p_rec in out nocopy ghr_cmp_shd.g_rec_type
454 ) is
455 --
456 l_proc varchar2(72) := g_package||'pre_insert';
457 --
458 Cursor C_Sel1 is select ghr_complaints2_s.nextval from sys.dual;
459 --
460 Begin
461 hr_utility.set_location('Entering:'||l_proc, 5);
462 --
463 --
464 -- Select the next sequence number
465 --
466 Open C_Sel1;
467 Fetch C_Sel1 Into p_rec.complaint_id;
468 Close C_Sel1;
469 --
470 hr_utility.set_location(' Leaving:'||l_proc, 10);
471 End pre_insert;
472 --
473 -- ----------------------------------------------------------------------------
474 -- |-----------------------------< post_insert >------------------------------|
475 -- ----------------------------------------------------------------------------
476 -- {Start Of Comments}
477 --
478 -- Description:
479 -- This private procedure contains any processing which is required after
480 -- the insert dml.
481 --
482 -- Prerequisites:
483 -- This is an internal procedure which is called from the ins procedure.
484 --
485 -- In Parameters:
486 -- A Pl/Sql record structre.
487 --
488 -- Post Success:
489 -- Processing continues.
490 --
491 -- Post Failure:
492 -- If an error has occurred, an error message and exception will be raised
493 -- but not handled.
494 --
495 -- Developer Implementation Notes:
496 -- Any post-processing required after the insert dml is issued should be
497 -- coded within this procedure. It is important to note that any 3rd party
498 -- maintenance should be reviewed before placing in this procedure.
499 --
500 -- Access Status:
501 -- Internal Row Handler Use Only.
502 --
503 -- {End Of Comments}
504 -- ----------------------------------------------------------------------------
505 Procedure post_insert
506 (p_effective_date in date
507 ,p_rec in ghr_cmp_shd.g_rec_type
508 ) is
509 --
510 l_proc varchar2(72) := g_package||'post_insert';
511 --
512 Begin
513 hr_utility.set_location('Entering:'||l_proc, 5);
514
515 begin
516 --
517 ghr_cmp_rki.after_insert
518 (p_effective_date => p_effective_date
519 ,p_complaint_id => p_rec.complaint_id
520 ,p_complainant_person_id => p_rec.complainant_person_id
521 ,p_business_group_id => p_rec.business_group_id
522 ,p_docket_number => p_rec.docket_number
523 ,p_stage => p_rec.stage
524 ,p_class_flag => p_rec.class_flag
525 ,p_mixed_flag => p_rec.mixed_flag
526 ,p_consolidated_flag => p_rec.consolidated_flag
527 ,p_remand_flag => p_rec.remand_flag
528 ,p_active_flag => p_rec.active_flag
529 ,p_information_inquiry => p_rec.information_inquiry
530 ,p_pcom_init => p_rec.pcom_init
531 ,p_alleg_incident => p_rec.alleg_incident
532 ,p_alleg_discrim_org_id => p_rec.alleg_discrim_org_id
533 ,p_rr_ltr_date => p_rec.rr_ltr_date
534 ,p_rr_ltr_recvd => p_rec.rr_ltr_recvd
535 ,p_pre_com_elec => p_rec.pre_com_elec
536 --,p_adr_offered => p_rec.adr_offered
537 ,p_class_agent_flag => p_rec.class_agent_flag
538 ,p_pre_com_desc => p_rec.pre_com_desc
539 ,p_counselor_asg => p_rec.counselor_asg
540 ,p_init_counselor_interview => p_rec.init_counselor_interview
541 ,p_anonymity_requested => p_rec.anonymity_requested
542 ,p_counsel_ext_ltr => p_rec.counsel_ext_ltr
543 ,p_traditional_counsel_outcome => p_rec.traditional_counsel_outcome
544 ,p_final_interview => p_rec.final_interview
545 ,p_notice_rtf_recvd => p_rec.notice_rtf_recvd
546 ,p_precom_closed => p_rec.precom_closed
547 ,p_precom_closure_nature => p_rec.precom_closure_nature
548 ,p_counselor_rpt_sub => p_rec.counselor_rpt_sub
549 ,p_hr_office_org_id => p_rec.hr_office_org_id
550 ,p_eeo_office_org_id => p_rec.eeo_office_org_id
551 ,p_serviced_org_id => p_rec.serviced_org_id
552 ,p_formal_com_filed => p_rec.formal_com_filed
553 ,p_ack_ltr => p_rec.ack_ltr
554 ,p_clarification_ltr_date => p_rec.clarification_ltr_date
555 ,p_clarification_response_recvd => p_rec.clarification_response_recvd
556 ,p_forwarded_legal_review => p_rec.forwarded_legal_review
557 ,p_returned_from_legal => p_rec.returned_from_legal
558 ,p_letter_type => p_rec.letter_type
559 ,p_letter_date => p_rec.letter_date
560 ,p_letter_recvd => p_rec.letter_recvd
561 ,p_investigation_source => p_rec.investigation_source
562 ,p_investigator_recvd_req => p_rec.investigator_recvd_req
563 ,p_agency_investigator_req => p_rec.agency_investigator_req
564 ,p_investigator_asg => p_rec.investigator_asg
565 ,p_investigation_start => p_rec.investigation_start
566 ,p_investigation_end => p_rec.investigation_end
567 ,p_investigation_extended => p_rec.investigation_extended
568 ,p_invest_extension_desc => p_rec.invest_extension_desc
569 ,p_agency_recvd_roi => p_rec.agency_recvd_roi
570 ,p_comrep_recvd_roi => p_rec.comrep_recvd_roi
571 ,p_options_ltr_date => p_rec.options_ltr_date
572 ,p_comrep_recvd_opt_ltr => p_rec.comrep_recvd_opt_ltr
573 ,p_comrep_opt_ltr_response => p_rec.comrep_opt_ltr_response
574 ,p_resolution_offer => p_rec.resolution_offer
575 ,p_comrep_resol_offer_recvd => p_rec.comrep_resol_offer_recvd
576 ,p_comrep_resol_offer_response => p_rec.comrep_resol_offer_response
577 ,p_comrep_resol_offer_desc => p_rec.comrep_resol_offer_desc
578 ,p_resol_offer_signed => p_rec.resol_offer_signed
579 ,p_resol_offer_desc => p_rec.resol_offer_desc
580 ,p_hearing_source => p_rec.hearing_source
581 ,p_agency_notified_hearing => p_rec.agency_notified_hearing
582 ,p_eeoc_hearing_docket_num => p_rec.eeoc_hearing_docket_num
583 ,p_hearing_complete => p_rec.hearing_complete
584 ,p_aj_merit_decision_date => p_rec.aj_merit_decision_date
585 ,p_agency_recvd_aj_merit_dec => p_rec.agency_recvd_aj_merit_dec
586 ,p_aj_merit_decision => p_rec.aj_merit_decision
587 ,p_aj_ca_decision_date => p_rec.aj_ca_decision_date
588 ,p_agency_recvd_aj_ca_dec => p_rec.agency_recvd_aj_ca_dec
589 ,p_aj_ca_decision => p_rec.aj_ca_decision
590 ,p_fad_requested => p_rec.fad_requested
591 ,p_merit_fad => p_rec.merit_fad
592 ,p_attorney_fees_fad => p_rec.attorney_fees_fad
593 ,p_comp_damages_fad => p_rec.comp_damages_fad
594 ,p_non_compliance_fad => p_rec.non_compliance_fad
595 ,p_fad_req_recvd_eeo_office => p_rec.fad_req_recvd_eeo_office
596 ,p_fad_req_forwd_to_agency => p_rec.fad_req_forwd_to_agency
597 ,p_agency_recvd_request => p_rec.agency_recvd_request
598 ,p_fad_due => p_rec.fad_due
599 ,p_fad_date => p_rec.fad_date
600 ,p_fad_decision => p_rec.fad_decision
601 -- ,p_fad_final_action_closure => p_rec.fad_final_action_closure
602 ,p_fad_forwd_to_comrep => p_rec.fad_forwd_to_comrep
603 ,p_fad_recvd_by_comrep => p_rec.fad_recvd_by_comrep
604 ,p_fad_imp_ltr_forwd_to_org => p_rec.fad_imp_ltr_forwd_to_org
605 ,p_fad_decision_forwd_legal => p_rec.fad_decision_forwd_legal
606 ,p_fad_decision_recvd_legal => p_rec.fad_decision_recvd_legal
607 ,p_fa_source => p_rec.fa_source
608 ,p_final_action_due => p_rec.final_action_due
609 -- ,p_final_action_nature_of_closu => p_rec.final_action_nature_of_closure
610 ,p_final_act_forwd_comrep => p_rec.final_act_forwd_comrep
611 ,p_final_act_recvd_comrep => p_rec.final_act_recvd_comrep
612 ,p_final_action_decision_date => p_rec.final_action_decision_date
613 ,p_final_action_decision => p_rec.final_action_decision
614 ,p_fa_imp_ltr_forwd_to_org => p_rec.fa_imp_ltr_forwd_to_org
615 ,p_fa_decision_forwd_legal => p_rec.fa_decision_forwd_legal
616 ,p_fa_decision_recvd_legal => p_rec.fa_decision_recvd_legal
617 ,p_civil_action_filed => p_rec.civil_action_filed
618 ,p_agency_closure_confirmed => p_rec.agency_closure_confirmed
619 ,p_consolidated_complaint_id => p_rec.consolidated_complaint_id
620 ,p_consolidated => p_rec.consolidated
621 ,p_stage_of_consolidation => p_rec.stage_of_consolidation
622 ,p_comrep_notif_consolidation => p_rec.comrep_notif_consolidation
623 ,p_consolidation_desc => p_rec.consolidation_desc
624 ,p_complaint_closed => p_rec.complaint_closed
625 ,p_nature_of_closure => p_rec.nature_of_closure
626 ,p_complaint_closed_desc => p_rec.complaint_closed_desc
627 ,p_filed_formal_class => p_rec.filed_formal_class
628 ,p_forwd_eeoc => p_rec.forwd_eeoc
629 ,p_aj_cert_decision_date => p_rec.aj_cert_decision_date
630 ,p_aj_cert_decision_recvd => p_rec.aj_cert_decision_recvd
631 ,p_aj_cert_decision => p_rec.aj_cert_decision
632 ,p_class_members_notified => p_rec.class_members_notified
633 ,p_number_of_complaintants => p_rec.number_of_complaintants
634 ,p_class_hearing => p_rec.class_hearing
635 ,p_aj_dec => p_rec.aj_dec
636 ,p_agency_recvd_aj_dec => p_rec.agency_recvd_aj_dec
637 ,p_aj_decision => p_rec.aj_decision
638 ,p_object_version_number => p_rec.object_version_number
639 ,p_agency_brief_eeoc => p_rec.agency_brief_eeoc
640 ,p_agency_notif_of_civil_action => p_rec.agency_notif_of_civil_action
641 ,p_fad_source => p_rec.fad_source
642 ,p_agency_files_forwarded_eeoc => p_rec.agency_files_forwarded_eeoc
643 ,p_hearing_req => p_rec.hearing_req
644 ,p_agency_code => p_rec.agency_code
645 ,p_audited_by => p_rec.audited_by
646 ,p_record_received => p_rec.record_received
647 ,p_attribute_category => p_rec.attribute_category
648 ,p_attribute1 => p_rec.attribute1
649 ,p_attribute2 => p_rec.attribute2
650 ,p_attribute3 => p_rec.attribute3
651 ,p_attribute4 => p_rec.attribute4
652 ,p_attribute5 => p_rec.attribute5
653 ,p_attribute6 => p_rec.attribute6
654 ,p_attribute7 => p_rec.attribute7
655 ,p_attribute8 => p_rec.attribute8
656 ,p_attribute9 => p_rec.attribute9
657 ,p_attribute10 => p_rec.attribute10
658 ,p_attribute11 => p_rec.attribute11
659 ,p_attribute12 => p_rec.attribute12
660 ,p_attribute13 => p_rec.attribute13
661 ,p_attribute14 => p_rec.attribute14
662 ,p_attribute15 => p_rec.attribute15
663 ,p_attribute16 => p_rec.attribute16
664 ,p_attribute17 => p_rec.attribute17
665 ,p_attribute18 => p_rec.attribute18
666 ,p_attribute19 => p_rec.attribute19
667 ,p_attribute20 => p_rec.attribute20
668 ,p_attribute21 => p_rec.attribute21
669 ,p_attribute22 => p_rec.attribute22
670 ,p_attribute23 => p_rec.attribute23
671 ,p_attribute24 => p_rec.attribute24
672 ,p_attribute25 => p_rec.attribute25
673 ,p_attribute26 => p_rec.attribute26
674 ,p_attribute27 => p_rec.attribute27
675 ,p_attribute28 => p_rec.attribute28
676 ,p_attribute29 => p_rec.attribute29
677 ,p_attribute30 => p_rec.attribute30
678 );
679 --
680 exception
681 --
682 when hr_api.cannot_find_prog_unit then
683 --
684 hr_api.cannot_find_prog_unit_error
685 (p_module_name => 'GHR_COMPLAINTS2'
686 ,p_hook_type => 'AI');
687 --
688 end;
689
690 --
691 hr_utility.set_location(' Leaving:'||l_proc, 10);
692 End post_insert;
693 --
694 -- ----------------------------------------------------------------------------
695 -- |---------------------------------< ins >----------------------------------|
696 -- ----------------------------------------------------------------------------
697 Procedure ins
698 (p_effective_date in date
699 ,p_rec in out nocopy ghr_cmp_shd.g_rec_type
700 ) is
701 --
702 l_proc varchar2(72) := g_package||'ins';
703 --
704 Begin
705 hr_utility.set_location('Entering:'||l_proc, 5);
706 --
707 -- Call the supporting insert validate operations
708 --
709 ghr_cmp_bus.insert_validate
710 (p_effective_date
711 ,p_rec
712 );
713 --
714 -- Call the supporting pre-insert operation
715 --
716 ghr_cmp_ins.pre_insert(p_rec);
717 --
718 -- Insert the row
719 --
720 ghr_cmp_ins.insert_dml(p_rec);
721 --
722 -- Call the supporting post-insert operation
723 --
724 ghr_cmp_ins.post_insert
725 (p_effective_date
726 ,p_rec
727 );
728 --
729 hr_utility.set_location('Leaving:'||l_proc, 20);
730 end ins;
731 --
732 -- ----------------------------------------------------------------------------
733 -- |---------------------------------< ins >----------------------------------|
734 -- ----------------------------------------------------------------------------
735 Procedure ins
736 (p_effective_date in date
737 ,p_complainant_person_id in number
738 ,p_business_group_id in number
739 ,p_docket_number in varchar2
740 ,p_stage in varchar2
741 ,p_class_flag in varchar2
742 ,p_mixed_flag in varchar2
743 ,p_consolidated_flag in varchar2
744 ,p_remand_flag in varchar2
745 ,p_active_flag in varchar2
746 ,p_information_inquiry in date
747 ,p_pcom_init in date
748 ,p_alleg_incident in date
749 ,p_alleg_discrim_org_id in number
750 ,p_rr_ltr_date in date
751 ,p_rr_ltr_recvd in date
752 ,p_pre_com_elec in varchar2
753 --,p_adr_offered in varchar2
754 ,p_class_agent_flag in varchar2
755 ,p_pre_com_desc in varchar2
756 ,p_counselor_asg in date
757 ,p_init_counselor_interview in date
758 ,p_anonymity_requested in varchar2
759 ,p_counsel_ext_ltr in date
760 ,p_traditional_counsel_outcome in varchar2
761 ,p_final_interview in date
762 ,p_notice_rtf_recvd in date
763 ,p_precom_closed in date
764 ,p_precom_closure_nature in varchar2
765 ,p_counselor_rpt_sub in date
766 ,p_hr_office_org_id in number
767 ,p_eeo_office_org_id in number
768 ,p_serviced_org_id in number
769 ,p_formal_com_filed in date
770 ,p_ack_ltr in date
771 ,p_clarification_ltr_date in date
772 ,p_clarification_response_recvd in date
773 ,p_forwarded_legal_review in date
774 ,p_returned_from_legal in date
775 ,p_letter_type in varchar2
776 ,p_letter_date in date
777 ,p_letter_recvd in date
778 ,p_investigation_source in varchar2
779 ,p_investigator_recvd_req in date
780 ,p_agency_investigator_req in date
781 ,p_investigator_asg in date
782 ,p_investigation_start in date
783 ,p_investigation_end in date
784 ,p_investigation_extended in date
785 ,p_invest_extension_desc in varchar2
786 ,p_agency_recvd_roi in date
787 ,p_comrep_recvd_roi in date
788 ,p_options_ltr_date in date
789 ,p_comrep_recvd_opt_ltr in date
790 ,p_comrep_opt_ltr_response in varchar2
791 ,p_resolution_offer in date
792 ,p_comrep_resol_offer_recvd in date
793 ,p_comrep_resol_offer_response in date
794 ,p_comrep_resol_offer_desc in varchar2
795 ,p_resol_offer_signed in date
796 ,p_resol_offer_desc in varchar2
797 ,p_hearing_source in varchar2
798 ,p_agency_notified_hearing in date
799 ,p_eeoc_hearing_docket_num in varchar2
800 ,p_hearing_complete in date
801 ,p_aj_merit_decision_date in date
802 ,p_agency_recvd_aj_merit_dec in date
803 ,p_aj_merit_decision in varchar2
804 ,p_aj_ca_decision_date in date
805 ,p_agency_recvd_aj_ca_dec in date
806 ,p_aj_ca_decision in varchar2
807 ,p_fad_requested in date
808 ,p_merit_fad in varchar2
809 ,p_attorney_fees_fad in varchar2
810 ,p_comp_damages_fad in varchar2
811 ,p_non_compliance_fad in varchar2
812 ,p_fad_req_recvd_eeo_office in date
813 ,p_fad_req_forwd_to_agency in date
814 ,p_agency_recvd_request in date
815 ,p_fad_due in date
816 ,p_fad_date in date
817 ,p_fad_decision in varchar2
818 --,p_fad_final_action_closure in varchar2
819 ,p_fad_forwd_to_comrep in date
820 ,p_fad_recvd_by_comrep in date
821 ,p_fad_imp_ltr_forwd_to_org in date
822 ,p_fad_decision_forwd_legal in date
823 ,p_fad_decision_recvd_legal in date
824 ,p_fa_source in varchar2
825 ,p_final_action_due in date
826 --,p_final_action_nature_of_closu in varchar2
827 ,p_final_act_forwd_comrep in date
828 ,p_final_act_recvd_comrep in date
829 ,p_final_action_decision_date in date
830 ,p_final_action_decision in varchar2
831 ,p_fa_imp_ltr_forwd_to_org in date
832 ,p_fa_decision_forwd_legal in date
833 ,p_fa_decision_recvd_legal in date
834 ,p_civil_action_filed in date
835 ,p_agency_closure_confirmed in date
836 ,p_consolidated_complaint_id in number
837 ,p_consolidated in date
838 ,p_stage_of_consolidation in varchar2
839 ,p_comrep_notif_consolidation in date
840 ,p_consolidation_desc in varchar2
841 ,p_complaint_closed in date
842 ,p_nature_of_closure in varchar2
843 ,p_complaint_closed_desc in varchar2
844 ,p_filed_formal_class in date
845 ,p_forwd_eeoc in date
846 ,p_aj_cert_decision_date in date
847 ,p_aj_cert_decision_recvd in date
848 ,p_aj_cert_decision in varchar2
849 ,p_class_members_notified in date
850 ,p_number_of_complaintants in number
851 ,p_class_hearing in date
852 ,p_aj_dec in date
853 ,p_agency_recvd_aj_dec in date
854 ,p_aj_decision in varchar2
855 ,p_agency_brief_eeoc in date
856 ,p_agency_notif_of_civil_action in date
857 ,p_fad_source in varchar2
858 ,p_agency_files_forwarded_eeoc in date
859 ,p_hearing_req in date
860 ,p_agency_code in varchar2
861 ,p_audited_by in varchar2
862 ,p_record_received in date
863 ,p_attribute_category in varchar2
864 ,p_attribute1 in varchar2
865 ,p_attribute2 in varchar2
866 ,p_attribute3 in varchar2
867 ,p_attribute4 in varchar2
868 ,p_attribute5 in varchar2
869 ,p_attribute6 in varchar2
870 ,p_attribute7 in varchar2
871 ,p_attribute8 in varchar2
872 ,p_attribute9 in varchar2
873 ,p_attribute10 in varchar2
874 ,p_attribute11 in varchar2
875 ,p_attribute12 in varchar2
876 ,p_attribute13 in varchar2
877 ,p_attribute14 in varchar2
878 ,p_attribute15 in varchar2
879 ,p_attribute16 in varchar2
880 ,p_attribute17 in varchar2
881 ,p_attribute18 in varchar2
882 ,p_attribute19 in varchar2
883 ,p_attribute20 in varchar2
884 ,p_attribute21 in varchar2
885 ,p_attribute22 in varchar2
886 ,p_attribute23 in varchar2
887 ,p_attribute24 in varchar2
888 ,p_attribute25 in varchar2
889 ,p_attribute26 in varchar2
890 ,p_attribute27 in varchar2
891 ,p_attribute28 in varchar2
892 ,p_attribute29 in varchar2
893 ,p_attribute30 in varchar2
894 ,p_complaint_id out nocopy number
895 ,p_object_version_number out nocopy number
896 ) is
897 --
898 l_rec ghr_cmp_shd.g_rec_type;
899 l_proc varchar2(72) := g_package||'ins';
900 --
901 Begin
902 hr_utility.set_location('Entering:'||l_proc, 5);
903 --
904 -- Call conversion function to turn arguments into the
905 -- p_rec structure.
906 --
907 l_rec :=
908 ghr_cmp_shd.convert_args
909 (null
910 ,p_complainant_person_id
911 ,p_business_group_id
912 ,p_docket_number
913 ,p_stage
914 ,p_class_flag
915 ,p_mixed_flag
916 ,p_consolidated_flag
917 ,p_remand_flag
918 ,p_active_flag
919 ,p_information_inquiry
920 ,p_pcom_init
921 ,p_alleg_incident
922 ,p_alleg_discrim_org_id
923 ,p_rr_ltr_date
924 ,p_rr_ltr_recvd
925 ,p_pre_com_elec
926 --,p_adr_offered
927 ,p_class_agent_flag
928 ,p_pre_com_desc
929 ,p_counselor_asg
930 ,p_init_counselor_interview
931 ,p_anonymity_requested
932 ,p_counsel_ext_ltr
933 ,p_traditional_counsel_outcome
934 ,p_final_interview
935 ,p_notice_rtf_recvd
936 ,p_precom_closed
937 ,p_precom_closure_nature
938 ,p_counselor_rpt_sub
939 ,p_hr_office_org_id
940 ,p_eeo_office_org_id
941 ,p_serviced_org_id
942 ,p_formal_com_filed
943 ,p_ack_ltr
944 ,p_clarification_ltr_date
945 ,p_clarification_response_recvd
946 ,p_forwarded_legal_review
947 ,p_returned_from_legal
948 ,p_letter_type
949 ,p_letter_date
950 ,p_letter_recvd
951 ,p_investigation_source
952 ,p_investigator_recvd_req
953 ,p_agency_investigator_req
954 ,p_investigator_asg
955 ,p_investigation_start
956 ,p_investigation_end
957 ,p_investigation_extended
958 ,p_invest_extension_desc
959 ,p_agency_recvd_roi
960 ,p_comrep_recvd_roi
961 ,p_options_ltr_date
962 ,p_comrep_recvd_opt_ltr
963 ,p_comrep_opt_ltr_response
964 ,p_resolution_offer
965 ,p_comrep_resol_offer_recvd
966 ,p_comrep_resol_offer_response
967 ,p_comrep_resol_offer_desc
968 ,p_resol_offer_signed
969 ,p_resol_offer_desc
970 ,p_hearing_source
971 ,p_agency_notified_hearing
972 ,p_eeoc_hearing_docket_num
973 ,p_hearing_complete
974 ,p_aj_merit_decision_date
975 ,p_agency_recvd_aj_merit_dec
976 ,p_aj_merit_decision
977 ,p_aj_ca_decision_date
978 ,p_agency_recvd_aj_ca_dec
979 ,p_aj_ca_decision
980 ,p_fad_requested
981 ,p_merit_fad
982 ,p_attorney_fees_fad
983 ,p_comp_damages_fad
984 ,p_non_compliance_fad
985 ,p_fad_req_recvd_eeo_office
986 ,p_fad_req_forwd_to_agency
987 ,p_agency_recvd_request
988 ,p_fad_due
989 ,p_fad_date
990 ,p_fad_decision
991 --,p_fad_final_action_closure
992 ,p_fad_forwd_to_comrep
993 ,p_fad_recvd_by_comrep
994 ,p_fad_imp_ltr_forwd_to_org
995 ,p_fad_decision_forwd_legal
996 ,p_fad_decision_recvd_legal
997 ,p_fa_source
998 ,p_final_action_due
999 --,p_final_action_nature_of_closu
1000 ,p_final_act_forwd_comrep
1001 ,p_final_act_recvd_comrep
1002 ,p_final_action_decision_date
1003 ,p_final_action_decision
1004 ,p_fa_imp_ltr_forwd_to_org
1005 ,p_fa_decision_forwd_legal
1006 ,p_fa_decision_recvd_legal
1007 ,p_civil_action_filed
1008 ,p_agency_closure_confirmed
1009 ,p_consolidated_complaint_id
1010 ,p_consolidated
1011 ,p_stage_of_consolidation
1012 ,p_comrep_notif_consolidation
1013 ,p_consolidation_desc
1014 ,p_complaint_closed
1015 ,p_nature_of_closure
1016 ,p_complaint_closed_desc
1017 ,p_filed_formal_class
1018 ,p_forwd_eeoc
1019 ,p_aj_cert_decision_date
1020 ,p_aj_cert_decision_recvd
1021 ,p_aj_cert_decision
1022 ,p_class_members_notified
1023 ,p_number_of_complaintants
1024 ,p_class_hearing
1025 ,p_aj_dec
1026 ,p_agency_recvd_aj_dec
1027 ,p_aj_decision
1028 ,null
1029 ,p_agency_brief_eeoc
1030 ,p_agency_notif_of_civil_action
1031 ,p_fad_source
1032 ,p_agency_files_forwarded_eeoc
1033 ,p_hearing_req
1034 ,p_agency_code
1035 ,p_audited_by
1036 ,p_record_received
1037 ,p_attribute_category
1038 ,p_attribute1
1039 ,p_attribute2
1040 ,p_attribute3
1041 ,p_attribute4
1042 ,p_attribute5
1043 ,p_attribute6
1044 ,p_attribute7
1045 ,p_attribute8
1046 ,p_attribute9
1047 ,p_attribute10
1048 ,p_attribute11
1049 ,p_attribute12
1050 ,p_attribute13
1051 ,p_attribute14
1052 ,p_attribute15
1053 ,p_attribute16
1054 ,p_attribute17
1055 ,p_attribute18
1056 ,p_attribute19
1057 ,p_attribute20
1058 ,p_attribute21
1059 ,p_attribute22
1060 ,p_attribute23
1061 ,p_attribute24
1062 ,p_attribute25
1063 ,p_attribute26
1064 ,p_attribute27
1065 ,p_attribute28
1066 ,p_attribute29
1067 ,p_attribute30
1068 );
1069 --
1070 -- Having converted the arguments into the ghr_cmp_rec
1071 -- plsql record structure we call the corresponding record business process.
1072 --
1073 ghr_cmp_ins.ins
1074 (p_effective_date
1075 ,l_rec
1076 );
1077 --
1078 -- As the primary key argument(s)
1079 -- are specified as an OUT's we must set these values.
1080 --
1081 p_complaint_id := l_rec.complaint_id;
1082 p_object_version_number := l_rec.object_version_number;
1083 --
1084 hr_utility.set_location(' Leaving:'||l_proc, 10);
1085 End ins;
1086 --
1087 end ghr_cmp_ins;