1 Package ghr_cmp_shd AUTHID CURRENT_USER as
2 /* $Header: ghcmprhi.pkh 120.0 2005/05/29 02:54:10 appldev noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Global Record Type Specification |
6 -- ----------------------------------------------------------------------------
7 --
8 Type g_rec_type Is Record
9 (complaint_id number(15)
10 ,complainant_person_id number(15)
11 ,business_group_id number(15)
12 ,docket_number varchar2(50)
13 ,stage varchar2(30) -- Increased length
14 ,class_flag varchar2(9) -- Increased length
15 ,mixed_flag varchar2(9) -- Increased length
16 ,consolidated_flag varchar2(9) -- Increased length
17 ,remand_flag varchar2(9) -- Increased length
18 ,active_flag varchar2(9) -- Increased length
19 ,information_inquiry date
20 ,pcom_init date
21 ,alleg_incident date
22 ,alleg_discrim_org_id number(15)
23 ,rr_ltr_date date
24 ,rr_ltr_recvd date
25 ,pre_com_elec varchar2(30) -- Increased length
26 --,adr_offered varchar2(9) -- Increased length
27 ,class_agent_flag varchar2(9) -- Increased length
28 ,pre_com_desc varchar2(500)
29 ,counselor_asg date
30 ,init_counselor_interview date
31 ,anonymity_requested varchar2(9) -- Increased length
32 ,counsel_ext_ltr date
33 ,traditional_counsel_outcome varchar2(30) -- Increased length
34 ,final_interview date
35 ,notice_rtf_recvd date
36 ,precom_closed date
37 ,precom_closure_nature varchar2(30) -- Increased length
38 ,counselor_rpt_sub date
39 ,hr_office_org_id number(15)
40 ,eeo_office_org_id number(15)
41 ,serviced_org_id number(15)
42 ,formal_com_filed date
43 ,ack_ltr date
44 ,clarification_ltr_date date
45 ,clarification_response_recvd date
46 ,forwarded_legal_review date
47 ,returned_from_legal date
48 ,letter_type varchar2(30)
49 ,letter_date date
50 ,letter_recvd date
51 ,investigation_source varchar2(30)
52 ,investigator_recvd_req date
53 ,agency_investigator_req date
54 ,investigator_asg date
55 ,investigation_start date
56 ,investigation_end date
57 ,investigation_extended date
58 ,invest_extension_desc varchar2(500)
59 ,agency_recvd_roi date
60 ,comrep_recvd_roi date
61 ,options_ltr_date date
62 ,comrep_recvd_opt_ltr date
63 ,comrep_opt_ltr_response varchar2(30) -- Increased length
64 ,resolution_offer date
65 ,comrep_resol_offer_recvd date
66 ,comrep_resol_offer_response date
67 ,comrep_resol_offer_desc varchar2(30)
68 ,resol_offer_signed date
69 ,resol_offer_desc varchar2(2000)
70 ,hearing_source varchar2(30) -- Increased length
71 ,agency_notified_hearing date
72 ,eeoc_hearing_docket_num varchar2(50)
73 ,hearing_complete date
74 ,aj_merit_decision_date date
75 ,agency_recvd_aj_merit_dec date
76 ,aj_merit_decision varchar2(30) -- Increased length
77 ,aj_ca_decision_date date
78 ,agency_recvd_aj_ca_dec date
79 ,aj_ca_decision varchar2(30) -- Increased length
80 ,fad_requested date
81 ,merit_fad varchar2(9) -- Increased length
82 ,attorney_fees_fad varchar2(9) -- Increased length
83 ,comp_damages_fad varchar2(9) -- Increased length
84 ,non_compliance_fad varchar2(9) -- Increased length
85 ,fad_req_recvd_eeo_office date
86 ,fad_req_forwd_to_agency date
87 ,agency_recvd_request date
88 ,fad_due date
89 ,fad_date date
90 ,fad_decision varchar2(30) -- Increased length
91 --,fad_final_action_closure varchar2(30) -- Increased length
92 ,fad_forwd_to_comrep date
93 ,fad_recvd_by_comrep date
94 ,fad_imp_ltr_forwd_to_org date
95 ,fad_decision_forwd_legal date
96 ,fad_decision_recvd_legal date
97 ,fa_source varchar2(30) -- Increased length
98 ,final_action_due date
99 --,final_action_nature_of_closure varchar2(30) -- Increased length
100 ,final_act_forwd_comrep date
101 ,final_act_recvd_comrep date
102 ,final_action_decision_date date
103 ,final_action_decision varchar2(30) -- Increased length
104 ,fa_imp_ltr_forwd_to_org date
105 ,fa_decision_forwd_legal date
106 ,fa_decision_recvd_legal date
107 ,civil_action_filed date
108 ,agency_closure_confirmed date
109 ,consolidated_complaint_id number(15)
110 ,consolidated date
111 ,stage_of_consolidation varchar2(30) -- Increased length
112 ,comrep_notif_consolidation date
113 ,consolidation_desc varchar2(500)
114 ,complaint_closed date
115 ,nature_of_closure varchar2(30)
116 ,complaint_closed_desc varchar2(2000)
117 ,filed_formal_class date
118 ,forwd_eeoc date
119 ,aj_cert_decision_date date
120 ,aj_cert_decision_recvd date
121 ,aj_cert_decision varchar2(30) -- Increased length
122 ,class_members_notified date
123 ,number_of_complaintants number(9)
124 ,class_hearing date
125 ,aj_dec date
126 ,agency_recvd_aj_dec date
127 ,aj_decision varchar2(30)
128 ,object_version_number number(15)
129 ,agency_brief_eeoc date
130 ,agency_notif_of_civil_action date
131 ,fad_source varchar2(30) -- Increased length
132 ,agency_files_forwarded_eeoc date
133 ,hearing_req date
134 ,agency_code varchar2(4)
135 ,audited_by varchar2(30)
136 ,record_received date
137 ,attribute_category varchar2(30)
138 ,attribute1 varchar2(150)
139 ,attribute2 varchar2(150)
140 ,attribute3 varchar2(150)
141 ,attribute4 varchar2(150)
142 ,attribute5 varchar2(150)
143 ,attribute6 varchar2(150)
144 ,attribute7 varchar2(150)
145 ,attribute8 varchar2(150)
146 ,attribute9 varchar2(150)
147 ,attribute10 varchar2(150)
148 ,attribute11 varchar2(150)
149 ,attribute12 varchar2(150)
150 ,attribute13 varchar2(150)
151 ,attribute14 varchar2(150)
152 ,attribute15 varchar2(150)
153 ,attribute16 varchar2(150)
154 ,attribute17 varchar2(150)
155 ,attribute18 varchar2(150)
156 ,attribute19 varchar2(150)
157 ,attribute20 varchar2(150)
158 ,attribute21 varchar2(150)
159 ,attribute22 varchar2(150)
160 ,attribute23 varchar2(150)
161 ,attribute24 varchar2(150)
162 ,attribute25 varchar2(150)
163 ,attribute26 varchar2(150)
164 ,attribute27 varchar2(150)
165 ,attribute28 varchar2(150)
166 ,attribute29 varchar2(150)
167 ,attribute30 varchar2(150)
168 );
169 --
170 -- ----------------------------------------------------------------------------
171 -- | Global Definitions - Internal Development Use Only |
172 -- ----------------------------------------------------------------------------
173 --
174 g_old_rec g_rec_type; -- Global record definition
175 --
176 -- ----------------------------------------------------------------------------
177 -- |---------------------------< constraint_error >---------------------------|
178 -- ----------------------------------------------------------------------------
179 -- {Start Of Comments}
180 --
181 -- Description:
182 -- This procedure is called when a constraint has been violated (i.e.
183 -- The exception hr_api.check_integrity_violated,
184 -- hr_api.parent_integrity_violated, hr_api.child_integrity_violated or
185 -- hr_api.unique_integrity_violated has been raised).
186 -- The exceptions can only be raised as follows:
187 -- 1) A check constraint can only be violated during an INSERT or UPDATE
188 -- dml operation.
189 -- 2) A parent integrity constraint can only be violated during an
190 -- INSERT or UPDATE dml operation.
191 -- 3) A child integrity constraint can only be violated during an
192 -- DELETE dml operation.
193 -- 4) A unique integrity constraint can only be violated during INSERT or
194 -- UPDATE dml operation.
195 --
196 -- Prerequisites:
197 -- 1) Either hr_api.check_integrity_violated,
198 -- hr_api.parent_integrity_violated, hr_api.child_integrity_violated or
199 -- hr_api.unique_integrity_violated has been raised with the subsequent
200 -- stripping of the constraint name from the generated error message
201 -- text.
202 -- 2) Standalone validation test which corresponds with a constraint error.
203 --
204 -- In Parameter:
205 -- p_constraint_name is in upper format and is just the constraint name
206 -- (e.g. not prefixed by brackets, schema owner etc).
207 --
208 -- Post Success:
209 -- Development dependant.
210 --
211 -- Post Failure:
212 -- Developement dependant.
213 --
214 -- Developer Implementation Notes:
215 -- For each constraint being checked the hr system package failure message
216 -- has been generated as a template only. These system error messages should
217 -- be modified as required (i.e. change the system failure message to a user
218 -- friendly defined error message).
219 --
220 -- Access Status:
221 -- Internal Development Use Only.
222 --
223 -- {End Of Comments}
224 -- ----------------------------------------------------------------------------
225 Procedure constraint_error
226 (p_constraint_name in all_constraints.constraint_name%TYPE);
227 --
228 -- ----------------------------------------------------------------------------
229 -- |-----------------------------< api_updating >-----------------------------|
230 -- ----------------------------------------------------------------------------
231 -- {Start Of Comments}
232 --
233 -- Description:
234 -- This function is used to populate the g_old_rec record with the
235 -- current row from the database for the specified primary key
236 -- provided that the primary key exists and is valid and does not
237 -- already match the current g_old_rec. The function will always return
238 -- a TRUE value if the g_old_rec is populated with the current row.
239 -- A FALSE value will be returned if all of the primary key arguments
240 -- are null.
241 --
242 -- Prerequisites:
243 -- None.
244 --
245 -- In Parameters:
246 --
247 -- Post Success:
248 -- A value of TRUE will be returned indiciating that the g_old_rec
249 -- is current.
250 -- A value of FALSE will be returned if all of the primary key arguments
251 -- have a null value (this indicates that the row has not be inserted into
252 -- the Schema), and therefore could never have a corresponding row.
253 --
254 -- Post Failure:
255 -- A failure can only occur under two circumstances:
256 -- 1) The primary key is invalid (i.e. a row does not exist for the
257 -- specified primary key values).
258 -- 2) If an object_version_number exists but is NOT the same as the current
259 -- g_old_rec value.
260 --
261 -- Developer Implementation Notes:
262 -- None.
263 --
264 -- Access Status:
265 -- Internal Development Use Only.
266 --
267 -- {End Of Comments}
268 -- ----------------------------------------------------------------------------
269 Function api_updating
270 (p_complaint_id in number
271 ,p_object_version_number in number
272 ) Return Boolean;
273 --
274 -- ----------------------------------------------------------------------------
275 -- |---------------------------------< lck >----------------------------------|
276 -- ----------------------------------------------------------------------------
277 -- {Start of comments}
278 --
279 -- Description:
280 -- The Lck process has two main functions to perform. Firstly, the row to be
281 -- updated or deleted must be locked. The locking of the row will only be
282 -- successful if the row is not currently locked by another user.
283 -- Secondly, during the locking of the row, the row is selected into
284 -- the g_old_rec data structure which enables the current row values from
285 -- the server to be available to the api.
286 --
287 -- Prerequisites:
288 -- When attempting to call the lock the object version number (if defined)
289 -- is mandatory.
290 --
291 -- In Parameters:
292 -- The arguments to the Lck process are the primary key(s) which uniquely
293 -- identify the row and the object version number of row.
294 --
295 -- Post Success:
296 -- On successful completion of the Lck process the row to be updated or
297 -- deleted will be locked and selected into the global data structure
298 -- g_old_rec.
299 --
300 -- Post Failure:
301 -- The Lck process can fail for three reasons:
302 -- 1) When attempting to lock the row the row could already be locked by
303 -- another user. This will raise the HR_Api.Object_Locked exception.
304 -- 2) The row which is required to be locked doesn't exist in the HR Schema.
305 -- This error is trapped and reported using the message name
306 -- 'HR_7220_INVALID_PRIMARY_KEY'.
307 -- 3) The row although existing in the HR Schema has a different object
308 -- version number than the object version number specified.
309 -- This error is trapped and reported using the message name
310 -- 'HR_7155_OBJECT_INVALID'.
311 --
312 -- Developer Implementation Notes:
313 -- For each primary key and the object version number arguments add a
314 -- call to hr_api.mandatory_arg_error procedure to ensure that these
315 -- argument values are not null.
316 --
317 -- Access Status:
318 -- Internal Development Use Only.
319 --
320 -- {End of comments}
321 -- ----------------------------------------------------------------------------
322 Procedure lck
323 (p_complaint_id in number
324 ,p_object_version_number in number
325 );
326 --
327 -- ----------------------------------------------------------------------------
328 -- |-----------------------------< convert_args >-----------------------------|
329 -- ----------------------------------------------------------------------------
330 -- {Start Of Comments}
331 --
332 -- Description:
333 -- This function is used to turn attribute parameters into the record
334 -- structure parameter g_rec_type.
335 --
336 -- Prerequisites:
337 -- This is a private function and can only be called from the ins or upd
338 -- attribute processes.
339 --
340 -- In Parameters:
341 --
342 -- Post Success:
343 -- A returning record structure will be returned.
344 --
345 -- Post Failure:
349 --
346 -- No direct error handling is required within this function. Any possible
347 -- errors within this function will be a PL/SQL value error due to
348 -- conversion of datatypes or data lengths.
350 -- Developer Implementation Notes:
351 -- None.
352 --
353 -- Access Status:
354 -- Internal Row Handler Use Only.
355 --
356 -- {End Of Comments}
357 -- ----------------------------------------------------------------------------
358 Function convert_args
359 (p_complaint_id in number
360 ,p_complainant_person_id in number
361 ,p_business_group_id in number
362 ,p_docket_number in varchar2
363 ,p_stage in varchar2
364 ,p_class_flag in varchar2
365 ,p_mixed_flag in varchar2
366 ,p_consolidated_flag in varchar2
367 ,p_remand_flag in varchar2
368 ,p_active_flag in varchar2
369 ,p_information_inquiry in date
370 ,p_pcom_init in date
371 ,p_alleg_incident in date
372 ,p_alleg_discrim_org_id in number
373 ,p_rr_ltr_date in date
374 ,p_rr_ltr_recvd in date
375 ,p_pre_com_elec in varchar2
376 --,p_adr_offered in varchar2
377 ,p_class_agent_flag in varchar2
378 ,p_pre_com_desc in varchar2
379 ,p_counselor_asg in date
380 ,p_init_counselor_interview in date
381 ,p_anonymity_requested in varchar2
382 ,p_counsel_ext_ltr in date
383 ,p_traditional_counsel_outcome in varchar2
384 ,p_final_interview in date
385 ,p_notice_rtf_recvd in date
386 ,p_precom_closed in date
387 ,p_precom_closure_nature in varchar2
388 ,p_counselor_rpt_sub in date
389 ,p_hr_office_org_id in number
390 ,p_eeo_office_org_id in number
391 ,p_serviced_org_id in number
392 ,p_formal_com_filed in date
393 ,p_ack_ltr in date
394 ,p_clarification_ltr_date in date
395 ,p_clarification_response_recvd in date
396 ,p_forwarded_legal_review in date
397 ,p_returned_from_legal in date
398 ,p_letter_type in varchar2
399 ,p_letter_date in date
400 ,p_letter_recvd in date
401 ,p_investigation_source in varchar2
402 ,p_investigator_recvd_req in date
403 ,p_agency_investigator_req in date
404 ,p_investigator_asg in date
405 ,p_investigation_start in date
406 ,p_investigation_end in date
407 ,p_investigation_extended in date
408 ,p_invest_extension_desc in varchar2
409 ,p_agency_recvd_roi in date
410 ,p_comrep_recvd_roi in date
411 ,p_options_ltr_date in date
412 ,p_comrep_recvd_opt_ltr in date
413 ,p_comrep_opt_ltr_response in varchar2
414 ,p_resolution_offer in date
415 ,p_comrep_resol_offer_recvd in date
416 ,p_comrep_resol_offer_response in date
417 ,p_comrep_resol_offer_desc in varchar2
418 ,p_resol_offer_signed in date
419 ,p_resol_offer_desc in varchar2
420 ,p_hearing_source in varchar2
421 ,p_agency_notified_hearing in date
422 ,p_eeoc_hearing_docket_num in varchar2
423 ,p_hearing_complete in date
424 ,p_aj_merit_decision_date in date
425 ,p_agency_recvd_aj_merit_dec in date
426 ,p_aj_merit_decision in varchar2
427 ,p_aj_ca_decision_date in date
428 ,p_agency_recvd_aj_ca_dec in date
429 ,p_aj_ca_decision in varchar2
430 ,p_fad_requested in date
431 ,p_merit_fad in varchar2
432 ,p_attorney_fees_fad in varchar2
433 ,p_comp_damages_fad in varchar2
434 ,p_non_compliance_fad in varchar2
435 ,p_fad_req_recvd_eeo_office in date
436 ,p_fad_req_forwd_to_agency in date
437 ,p_agency_recvd_request in date
438 ,p_fad_due in date
439 ,p_fad_date in date
440 ,p_fad_decision in varchar2
441 --,p_fad_final_action_closure in varchar2
442 ,p_fad_forwd_to_comrep in date
443 ,p_fad_recvd_by_comrep in date
444 ,p_fad_imp_ltr_forwd_to_org in date
445 ,p_fad_decision_forwd_legal in date
446 ,p_fad_decision_recvd_legal in date
447 ,p_fa_source in varchar2
448 ,p_final_action_due in date
449 --,p_final_action_nature_of_closu in varchar2
450 ,p_final_act_forwd_comrep in date
451 ,p_final_act_recvd_comrep in date
452 ,p_final_action_decision_date in date
453 ,p_final_action_decision in varchar2
454 ,p_fa_imp_ltr_forwd_to_org in date
455 ,p_fa_decision_forwd_legal in date
456 ,p_fa_decision_recvd_legal in date
457 ,p_civil_action_filed in date
458 ,p_agency_closure_confirmed in date
459 ,p_consolidated_complaint_id in number
460 ,p_consolidated in date
461 ,p_stage_of_consolidation in varchar2
465 ,p_nature_of_closure in varchar2
462 ,p_comrep_notif_consolidation in date
463 ,p_consolidation_desc in varchar2
464 ,p_complaint_closed in date
466 ,p_complaint_closed_desc in varchar2
467 ,p_filed_formal_class in date
468 ,p_forwd_eeoc in date
469 ,p_aj_cert_decision_date in date
470 ,p_aj_cert_decision_recvd in date
471 ,p_aj_cert_decision in varchar2
472 ,p_class_members_notified in date
473 ,p_number_of_complaintants in number
474 ,p_class_hearing in date
475 ,p_aj_dec in date
476 ,p_agency_recvd_aj_dec in date
477 ,p_aj_decision in varchar2
478 ,p_object_version_number in number
479 ,p_agency_brief_eeoc in date
480 ,p_agency_notif_of_civil_action in date
481 ,p_fad_source in varchar2
482 ,p_agency_files_forwarded_eeoc in date
483 ,p_hearing_req in date
484 ,p_agency_code in varchar2
485 ,p_audited_by in varchar2
486 ,p_record_received in date
487 ,p_attribute_category in varchar2
488 ,p_attribute1 in varchar2
489 ,p_attribute2 in varchar2
490 ,p_attribute3 in varchar2
491 ,p_attribute4 in varchar2
492 ,p_attribute5 in varchar2
493 ,p_attribute6 in varchar2
494 ,p_attribute7 in varchar2
495 ,p_attribute8 in varchar2
496 ,p_attribute9 in varchar2
497 ,p_attribute10 in varchar2
498 ,p_attribute11 in varchar2
499 ,p_attribute12 in varchar2
500 ,p_attribute13 in varchar2
501 ,p_attribute14 in varchar2
502 ,p_attribute15 in varchar2
503 ,p_attribute16 in varchar2
504 ,p_attribute17 in varchar2
505 ,p_attribute18 in varchar2
506 ,p_attribute19 in varchar2
507 ,p_attribute20 in varchar2
508 ,p_attribute21 in varchar2
509 ,p_attribute22 in varchar2
510 ,p_attribute23 in varchar2
511 ,p_attribute24 in varchar2
512 ,p_attribute25 in varchar2
513 ,p_attribute26 in varchar2
514 ,p_attribute27 in varchar2
515 ,p_attribute28 in varchar2
519 Return g_rec_type;
516 ,p_attribute29 in varchar2
517 ,p_attribute30 in varchar2
518 )
520 --
521 end ghr_cmp_shd;
[Home] [Help]