[Home] [Help]
PACKAGE BODY: APPS.PER_PREV_JOB_INFO_TYPES_PKG
Source
1 PACKAGE BODY PER_PREV_JOB_INFO_TYPES_PKG as
2 /* $Header: perpjxit.pkb 115.1 2002/12/06 15:20:42 eumenyio ship $ */
3 --------------------------------------------------------------------------------
4 g_dummy number(1); -- Dummy for cursor returns which are not needed
5 g_business_group_id number(15); -- For validating translation;
6 g_legislation_code varchar2(150); -- For validating translation;
7 --------------------------------------------------------------------------------
8 --
9 PROCEDURE UNIQUENESS_CHECK(P_INFORMATION_TYPE VARCHAR2,
10 P_ACTIVE_INACTIVE_FLAG VARCHAR2,
11 P_LEGISLATION_CODE VARCHAR2,
12 P_ROWID VARCHAR2,
13 P_DESCRIPTION VARCHAR2)
14 IS
15 L_DUMMY1 number;
16 CURSOR C1 IS
17 select 1
18 from PER_PREV_JOB_INFO_TYPES t
19 where upper(t.description) = upper(P_DESCRIPTION)
20 and nvl(t.legislation_code, nvl(P_LEGISLATION_CODE, 'XXX') )
21 = nvl(P_LEGISLATION_CODE, 'XXX')
22 and (P_ROWID is null
23 or P_ROWID <> t.rowid);
24 BEGIN
25 OPEN C1;
26 FETCH C1 INTO L_DUMMY1;
27 IF C1%NOTFOUND THEN
28 CLOSE C1;
29 ELSE
30 CLOSE C1;
31 hr_utility.set_message('801','HR_7777_DEF_DESCR_EXISTS');
32 hr_utility.raise_error;
33 END IF;
34 end UNIQUENESS_CHECK;
35 --
36 procedure INSERT_ROW (
37 X_ROWID in out nocopy VARCHAR2,
38 X_INFORMATION_TYPE in VARCHAR2,
39 X_ACTIVE_INACTIVE_FLAG in VARCHAR2,
40 X_MULTIPLE_OCCURANCES_FLAG in VARCHAR2,
41 X_LEGISLATION_CODE in VARCHAR2,
42 X_OBJECT_VERSION_NUMBER in NUMBER,
43 X_DESCRIPTION in VARCHAR2,
44 X_CREATION_DATE in DATE,
45 X_CREATED_BY in NUMBER,
46 X_LAST_UPDATE_DATE in DATE,
47 X_LAST_UPDATED_BY in NUMBER,
48 X_LAST_UPDATE_LOGIN in NUMBER
49 ) is
50 cursor C is select ROWID from PER_PREV_JOB_INFO_TYPES
51 where INFORMATION_TYPE = X_INFORMATION_TYPE
52 ;
53 begin
54 insert into PER_PREV_JOB_INFO_TYPES (
55 INFORMATION_TYPE,
56 ACTIVE_INACTIVE_FLAG,
57 MULTIPLE_OCCURANCES_FLAG,
58 LEGISLATION_CODE,
59 OBJECT_VERSION_NUMBER,
60 DESCRIPTION,
61 CREATION_DATE,
62 CREATED_BY,
63 LAST_UPDATE_DATE,
64 LAST_UPDATED_BY,
65 LAST_UPDATE_LOGIN
66 ) values (
67 X_INFORMATION_TYPE,
68 X_ACTIVE_INACTIVE_FLAG,
69 X_MULTIPLE_OCCURANCES_FLAG,
70 X_LEGISLATION_CODE,
71 X_OBJECT_VERSION_NUMBER,
72 X_DESCRIPTION,
73 X_CREATION_DATE,
74 X_CREATED_BY,
75 X_LAST_UPDATE_DATE,
76 X_LAST_UPDATED_BY,
77 X_LAST_UPDATE_LOGIN
78 );
79
80 open c;
81 fetch c into X_ROWID;
82 if (c%notfound) then
83 close c;
84 raise no_data_found;
85 end if;
86 close c;
87
88 end INSERT_ROW;
89
90 procedure LOCK_ROW (
91 X_INFORMATION_TYPE in VARCHAR2,
92 X_ACTIVE_INACTIVE_FLAG in VARCHAR2,
93 X_MULTIPLE_OCCURANCES_FLAG in VARCHAR2,
94 X_LEGISLATION_CODE in VARCHAR2,
95 X_OBJECT_VERSION_NUMBER in NUMBER,
96 X_DESCRIPTION in VARCHAR2
97 ) is
98 cursor c is select
99 ACTIVE_INACTIVE_FLAG,
100 MULTIPLE_OCCURANCES_FLAG,
101 LEGISLATION_CODE,
102 OBJECT_VERSION_NUMBER
103 from PER_PREV_JOB_INFO_TYPES
104 where INFORMATION_TYPE = X_INFORMATION_TYPE
105 for update of INFORMATION_TYPE nowait;
106 recinfo c%rowtype;
107
108 begin
109 open c;
110 fetch c into recinfo;
111 if (c%notfound) then
112 close c;
113 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
114 app_exception.raise_exception;
115 end if;
116 close c;
117 if ( (recinfo.ACTIVE_INACTIVE_FLAG = X_ACTIVE_INACTIVE_FLAG)
118 AND (recinfo.MULTIPLE_OCCURANCES_FLAG = X_MULTIPLE_OCCURANCES_FLAG)
119 AND ((recinfo.LEGISLATION_CODE = X_LEGISLATION_CODE)
120 OR ((recinfo.LEGISLATION_CODE is null) AND (X_LEGISLATION_CODE is null)))
121 AND ((recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
122 OR ((recinfo.OBJECT_VERSION_NUMBER is null) AND (X_OBJECT_VERSION_NUMBER is null)))
123 ) then
124 null;
125 else
126 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
127 app_exception.raise_exception;
128 end if;
129
130 return;
131 end LOCK_ROW;
132
133 procedure UPDATE_ROW (
134 X_INFORMATION_TYPE in VARCHAR2,
135 X_ACTIVE_INACTIVE_FLAG in VARCHAR2,
136 X_MULTIPLE_OCCURANCES_FLAG in VARCHAR2,
137 X_LEGISLATION_CODE in VARCHAR2,
138 X_OBJECT_VERSION_NUMBER in NUMBER,
139 X_DESCRIPTION in VARCHAR2,
140 X_LAST_UPDATE_DATE in DATE,
141 X_LAST_UPDATED_BY in NUMBER,
142 X_LAST_UPDATE_LOGIN in NUMBER
143 ) is
144 begin
145 update PER_PREV_JOB_INFO_TYPES set
146 ACTIVE_INACTIVE_FLAG = X_ACTIVE_INACTIVE_FLAG,
147 MULTIPLE_OCCURANCES_FLAG = X_MULTIPLE_OCCURANCES_FLAG,
148 LEGISLATION_CODE = X_LEGISLATION_CODE,
149 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
150 DESCRIPTION = X_DESCRIPTION,
151 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
152 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
153 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
154 where INFORMATION_TYPE = X_INFORMATION_TYPE;
155
156 if (sql%notfound) then
157 raise no_data_found;
158 end if;
159
160 end UPDATE_ROW;
161
162 procedure DELETE_ROW (
163 X_INFORMATION_TYPE in VARCHAR2
164 ) is
165 begin
166
167 delete from PER_PREV_JOB_INFO_TYPES
168 where INFORMATION_TYPE = X_INFORMATION_TYPE;
169
170 if (sql%notfound) then
171 raise no_data_found;
172 end if;
173 end DELETE_ROW;
174
175 procedure LOAD_ROW
176 (X_INFORMATION_TYPE in varchar2
177 ,X_ACTIVE_INACTIVE_FLAG in varchar2
178 ,X_MULTIPLE_OCCURANCES_FLAG in varchar2
179 ,X_DESCRIPTION in varchar2
180 ,X_LEGISLATION_CODE in varchar2
181 ,X_OBJECT_VERSION_NUMBER in number
182 ,X_OWNER in varchar2
183 )
184 is
185 l_proc VARCHAR2(61) := 'PER_PREV_JOB_INFO_TYPES_PKG.LOAD_ROW';
186 l_rowid rowid;
187 l_progam_application_id PER_PEOPLE_INFO_types.program_application_id%TYPE;
188 l_program_id PER_PEOPLE_INFO_types.program_id%TYPE;
189 l_program_update_date PER_PEOPLE_INFO_types.program_update_date%TYPE;
190 l_created_by PER_PEOPLE_INFO_types.created_by%TYPE := 0;
191 l_creation_date PER_PEOPLE_INFO_types.creation_date%TYPE := SYSDATE;
192 l_last_update_date PER_PEOPLE_INFO_types.last_update_date%TYPE := SYSDATE;
193 l_last_updated_by PER_PEOPLE_INFO_types.last_updated_by%TYPE := 0;
194 l_last_update_login PER_PEOPLE_INFO_types.last_update_login%TYPE := 0;
195 begin
196 -- Translate developer keys to internal parameters
197 if X_OWNER = 'SEED' then
198 l_created_by := 1;
199 l_last_updated_by := 1;
200 end if;
201 -- Update or insert row as appropriate
202 begin
203 UPDATE_ROW
204 (X_INFORMATION_TYPE => X_INFORMATION_TYPE
205 ,X_ACTIVE_INACTIVE_FLAG => X_ACTIVE_INACTIVE_FLAG
206 ,X_MULTIPLE_OCCURANCES_FLAG => X_MULTIPLE_OCCURANCES_FLAG
207 ,X_DESCRIPTION => X_DESCRIPTION
208 ,X_LEGISLATION_CODE => X_LEGISLATION_CODE
209 ,X_OBJECT_VERSION_NUMBER => X_OBJECT_VERSION_NUMBER
210 ,X_LAST_UPDATE_DATE => l_last_update_date
211 ,X_LAST_UPDATED_BY => l_last_updated_by
212 ,X_LAST_UPDATE_LOGIN => l_last_update_login
213 );
214 exception
215 when no_data_found then
216 INSERT_ROW
217 (X_ROWID => l_rowid
218 ,X_INFORMATION_TYPE => X_INFORMATION_TYPE
219 ,X_ACTIVE_INACTIVE_FLAG => X_ACTIVE_INACTIVE_FLAG
220 ,X_MULTIPLE_OCCURANCES_FLAG => X_MULTIPLE_OCCURANCES_FLAG
221 ,X_DESCRIPTION => X_DESCRIPTION
222 ,X_LEGISLATION_CODE => X_LEGISLATION_CODE
223 ,X_OBJECT_VERSION_NUMBER => X_OBJECT_VERSION_NUMBER
224 ,X_CREATED_BY => l_created_by
225 ,X_CREATION_DATE => l_creation_date
226 ,X_LAST_UPDATE_DATE => l_last_update_date
227 ,X_LAST_UPDATED_BY => l_last_updated_by
228 ,X_LAST_UPDATE_LOGIN => l_last_update_login
229 );
230 end;
231 --
232 end LOAD_ROW;
233
234 procedure TRANSLATE_ROW
235 (X_INFORMATION_TYPE in varchar2
236 ,X_DESCRIPTION in varchar2
237 ,X_OWNER in varchar2
238 )
239 is
240 begin
241 UPDATE per_people_info_types
242 SET description = X_DESCRIPTION
243 ,last_update_date = SYSDATE
244 ,last_updated_by = DECODE(X_OWNER,'SEED',1,0)
245 ,last_update_login = 0
246 WHERE USERENV('LANG') = (select language_code from fnd_languages
247 where installed_flag = 'B')
248 AND information_type = X_INFORMATION_TYPE;
249 end TRANSLATE_ROW;
250
251
252 END PER_PREV_JOB_INFO_TYPES_PKG;