1 PACKAGE BODY hr_us_osha AS
2 /* $Header: peusosha.pkb 120.0 2005/05/31 22:42:20 appldev noship $ */
3 /*
4 ******************************************************************
5 * *
6 * Copyright (C) 1992 Oracle Corporation UK Ltd., *
7 * Chertsey, England. *
8 * *
9 * All rights reserved. *
10 * *
11 * This material has been provided pursuant to an agreement *
12 * containing restrictions on its use. The material is also *
13 * protected by copyright law. No part of this material may *
14 * be copied or distributed, transmitted or transcribed, in *
15 * any form or by any means, electronic, mechanical, magnetic, *
16 * manual, or otherwise, or disclosed to third parties without *
17 * the express written permission of Oracle Corporation UK Ltd, *
18 * Oracle Park, Bittams Lane, Guildford Road, Chertsey, Surrey, *
19 * England. *
20 * *
21 ****************************************************************** */
22 /*
23 Name : hr_us_osha (BODY)
24
25 Description : This package declares a function required to generate
26 OSHA-reportable incident case numbers.
27
28 Change List
29 -----------
30
31 Version Date Author ER/CR No. Description of Change
32 -------+---------+----------+---------+--------------------------
33 70.0 27-Apr-95 SSDESAI Created
34 70.2 10-JUL-97 MBOCUTT By using profile options the
35 110.1 date format is now in canonical format
36 so ammend the substr command on the
37 p_incident_year to reflect this.
38 115.1 13-AUG-01 GPERRY Changed routine so that it creates
39 unique case numbers and fills in missed
40 case numbers.
41 WWBUG 1714703.
42 ================================================================= */
43 --
44 --
45 -- Called as a default for the Case Number segment of the
46 -- OSHA-reportable Incident Flex Structure.
47 --
48 -- Function used to generate the case number.
49 --
50 function generate_case_number return varchar2 IS
51 --
52 v_case_number varchar2(150);
53 l_dummy varchar2(1);
54 --
55 cursor c1 is
56 select null
57 from sys.dual
58 where exists (select null
59 from per_analysis_criteria pac,
60 fnd_id_flex_structures fif
61 where pac.id_flex_num = fif.id_flex_num
62 and fif.id_flex_structure_code = 'OSHA-REPORTABLE_INCIDENT'
63 and pac.segment1 = v_case_number);
64 --
65 cursor c2 is
66 select max(pac.segment1)+v_case_number
67 from per_analysis_criteria pac,
68 fnd_id_flex_structures fif
69 where pac.id_flex_num = fif.id_flex_num
70 and fif.id_flex_structure_code = 'OSHA-REPORTABLE_INCIDENT';
71 --
72 begin
73 --
74 select per_osha_case_number_s.nextval
75 into v_case_number
76 from sys.dual;
77 --
78 -- If the value is taken then use the max value + sequence, that will be
79 -- unique, otherwise use the sequence number to fill in some of the gaps.
80 -- Gradually using this technique we would be able to fill in all the
81 -- gaps.
82 --
83 open c1;
84 --
85 fetch c1 into l_dummy;
86 --
87 if c1%found then
88 --
89 -- The number has been taken so use the max id + the sequence number
90 --
91 open c2;
92 --
93 fetch c2 into v_case_number;
94 --
95 close c2;
96 --
97 end if;
98 --
99 close c1;
100 --
101 return(v_case_number);
102 --
103 end generate_case_number;
104 --
105 END hr_us_osha;
[Home] [Help]