[Home] [Help]
PACKAGE BODY: APPS.PER_PEOPLE_INFO_TYPES_PKG
Source
1 PACKAGE BODY PER_PEOPLE_INFO_TYPES_PKG as
2 /* $Header: perperit.pkb 120.1 2011/04/28 09:54:09 sidsaxen 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_PEOPLE_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_OCCURENCES_FLAG in VARCHAR2,
41 X_LEGISLATION_CODE in VARCHAR2,
42 X_REQUEST_ID in NUMBER,
43 X_OBJECT_VERSION_NUMBER in NUMBER,
44 X_DESCRIPTION in VARCHAR2,
45 X_CREATION_DATE in DATE,
46 X_CREATED_BY in NUMBER,
47 X_LAST_UPDATE_DATE in DATE,
48 X_LAST_UPDATED_BY in NUMBER,
49 X_LAST_UPDATE_LOGIN in NUMBER
50 ) is
51 cursor C is select ROWID from PER_PEOPLE_INFO_TYPES
52 where INFORMATION_TYPE = X_INFORMATION_TYPE
53 ;
54 begin
55 insert into PER_PEOPLE_INFO_TYPES (
56 INFORMATION_TYPE,
57 ACTIVE_INACTIVE_FLAG,
58 MULTIPLE_OCCURENCES_FLAG,
59 LEGISLATION_CODE,
60 REQUEST_ID,
61 OBJECT_VERSION_NUMBER,
62 DESCRIPTION,
63 CREATION_DATE,
64 CREATED_BY,
65 LAST_UPDATE_DATE,
66 LAST_UPDATED_BY,
67 LAST_UPDATE_LOGIN
68 ) values (
69 X_INFORMATION_TYPE,
70 X_ACTIVE_INACTIVE_FLAG,
71 X_MULTIPLE_OCCURENCES_FLAG,
72 X_LEGISLATION_CODE,
73 X_REQUEST_ID,
74 X_OBJECT_VERSION_NUMBER,
75 X_DESCRIPTION,
76 X_CREATION_DATE,
77 X_CREATED_BY,
78 X_LAST_UPDATE_DATE,
79 X_LAST_UPDATED_BY,
80 X_LAST_UPDATE_LOGIN
81 );
82
83 open c;
84 fetch c into X_ROWID;
85 if (c%notfound) then
86 close c;
87 raise no_data_found;
88 end if;
89 close c;
90
91 end INSERT_ROW;
92
93 procedure LOCK_ROW (
94 X_INFORMATION_TYPE in VARCHAR2,
95 X_ACTIVE_INACTIVE_FLAG in VARCHAR2,
96 X_MULTIPLE_OCCURENCES_FLAG in VARCHAR2,
97 X_LEGISLATION_CODE in VARCHAR2,
98 X_REQUEST_ID in NUMBER,
99 X_OBJECT_VERSION_NUMBER in NUMBER,
100 X_DESCRIPTION in VARCHAR2
101 ) is
102 cursor c is select
103 ACTIVE_INACTIVE_FLAG,
104 MULTIPLE_OCCURENCES_FLAG,
105 LEGISLATION_CODE,
106 REQUEST_ID,
107 OBJECT_VERSION_NUMBER
108 from PER_PEOPLE_INFO_TYPES
109 where INFORMATION_TYPE = X_INFORMATION_TYPE
110 for update of INFORMATION_TYPE nowait;
111 recinfo c%rowtype;
112
113 begin
114 open c;
115 fetch c into recinfo;
116 if (c%notfound) then
117 close c;
118 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
119 app_exception.raise_exception;
120 end if;
121 close c;
122 if ( (recinfo.ACTIVE_INACTIVE_FLAG = X_ACTIVE_INACTIVE_FLAG)
123 AND (recinfo.MULTIPLE_OCCURENCES_FLAG = X_MULTIPLE_OCCURENCES_FLAG)
124 AND ((recinfo.LEGISLATION_CODE = X_LEGISLATION_CODE)
125 OR ((recinfo.LEGISLATION_CODE is null) AND (X_LEGISLATION_CODE is null)))
126 AND ((recinfo.REQUEST_ID = X_REQUEST_ID)
127 OR ((recinfo.REQUEST_ID is null) AND (X_REQUEST_ID is null)))
128 AND ((recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
129 OR ((recinfo.OBJECT_VERSION_NUMBER is null) AND (X_OBJECT_VERSION_NUMBER is null)))
130 ) then
131 null;
132 else
133 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
134 app_exception.raise_exception;
135 end if;
136
137 return;
138 end LOCK_ROW;
139
140 procedure UPDATE_ROW (
141 X_INFORMATION_TYPE in VARCHAR2,
142 X_ACTIVE_INACTIVE_FLAG in VARCHAR2,
143 X_MULTIPLE_OCCURENCES_FLAG in VARCHAR2,
144 X_LEGISLATION_CODE in VARCHAR2,
145 X_REQUEST_ID in NUMBER,
146 X_OBJECT_VERSION_NUMBER in NUMBER,
147 X_DESCRIPTION in VARCHAR2,
148 X_LAST_UPDATE_DATE in DATE,
149 X_LAST_UPDATED_BY in NUMBER,
150 X_LAST_UPDATE_LOGIN in NUMBER
151 ) is
152 begin
153 update PER_PEOPLE_INFO_TYPES set
154 ACTIVE_INACTIVE_FLAG = X_ACTIVE_INACTIVE_FLAG,
155 MULTIPLE_OCCURENCES_FLAG = X_MULTIPLE_OCCURENCES_FLAG,
156 LEGISLATION_CODE = X_LEGISLATION_CODE,
157 REQUEST_ID = X_REQUEST_ID,
158 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
159 DESCRIPTION = X_DESCRIPTION,
160 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
161 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
162 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
163 where INFORMATION_TYPE = X_INFORMATION_TYPE;
164
165 if (sql%notfound) then
166 raise no_data_found;
167 end if;
168
169 end UPDATE_ROW;
170
171 procedure DELETE_ROW (
172 X_INFORMATION_TYPE in VARCHAR2
173 ) is
174 begin
175 --
176 -- Added the following code as a part of Zero Downtime Patching Project.
177 -- Code Starts Here.
178 --
179 per_ric_pkg.Chk_integrity(
180 p_entity_name=>'PER_PEOPLE_INFO_TYPES',
181 p_ref_entity=>'PER_PEOPLE_EXTRA_INFO',
182 p_ref_column_name=>'INFORMATION_TYPE',
183 p_ref_col_value_varchar=> X_INFORMATION_TYPE,
184 p_ref_col_value_number=> NULL,
185 p_ref_col_value_date=> NULL,
186 p_ref_type=>'DEL');
187 --
188 -- Code Ends Here
189 --
190
191 delete from PER_PEOPLE_INFO_TYPES
192 where INFORMATION_TYPE = X_INFORMATION_TYPE;
193
194 if (sql%notfound) then
195 raise no_data_found;
196 end if;
197 end DELETE_ROW;
198
199 procedure LOAD_ROW
200 (X_INFORMATION_TYPE in varchar2
201 ,X_ACTIVE_INACTIVE_FLAG in varchar2
202 ,X_MULTIPLE_OCCURENCES_FLAG in varchar2
203 ,X_DESCRIPTION in varchar2
204 ,X_LEGISLATION_CODE in varchar2
205 ,X_OBJECT_VERSION_NUMBER in number
206 ,X_OWNER in varchar2
207 )
208 is
209 l_proc VARCHAR2(61) := 'PER_PEOPLE_INFO_TYPES_PKG.LOAD_ROW';
210 l_rowid rowid;
211 l_request_id PER_PEOPLE_INFO_types.request_id%TYPE;
212 l_progam_application_id PER_PEOPLE_INFO_types.program_application_id%TYPE;
213 l_program_id PER_PEOPLE_INFO_types.program_id%TYPE;
214 l_program_update_date PER_PEOPLE_INFO_types.program_update_date%TYPE;
215 l_created_by PER_PEOPLE_INFO_types.created_by%TYPE := 0;
216 l_creation_date PER_PEOPLE_INFO_types.creation_date%TYPE := SYSDATE;
217 l_last_update_date PER_PEOPLE_INFO_types.last_update_date%TYPE := SYSDATE;
218 l_last_updated_by PER_PEOPLE_INFO_types.last_updated_by%TYPE := 0;
219 l_last_update_login PER_PEOPLE_INFO_types.last_update_login%TYPE := 0;
220 begin
221 -- Translate developer keys to internal parameters
222 if X_OWNER = 'SEED' then
223 l_created_by := 1;
224 l_last_updated_by := 1;
225 end if;
226 -- Update or insert row as appropriate
227 begin
228 UPDATE_ROW
229 (X_INFORMATION_TYPE => X_INFORMATION_TYPE
230 ,X_ACTIVE_INACTIVE_FLAG => X_ACTIVE_INACTIVE_FLAG
231 ,X_MULTIPLE_OCCURENCES_FLAG => X_MULTIPLE_OCCURENCES_FLAG
232 ,X_DESCRIPTION => X_DESCRIPTION
233 ,X_LEGISLATION_CODE => X_LEGISLATION_CODE
234 ,X_OBJECT_VERSION_NUMBER => X_OBJECT_VERSION_NUMBER
235 ,X_REQUEST_ID => l_request_id
236 ,X_LAST_UPDATE_DATE => l_last_update_date
237 ,X_LAST_UPDATED_BY => l_last_updated_by
238 ,X_LAST_UPDATE_LOGIN => l_last_update_login
239 );
240 exception
241 when no_data_found then
242 INSERT_ROW
243 (X_ROWID => l_rowid
244 ,X_INFORMATION_TYPE => X_INFORMATION_TYPE
245 ,X_ACTIVE_INACTIVE_FLAG => X_ACTIVE_INACTIVE_FLAG
246 ,X_MULTIPLE_OCCURENCES_FLAG => X_MULTIPLE_OCCURENCES_FLAG
247 ,X_DESCRIPTION => X_DESCRIPTION
248 ,X_LEGISLATION_CODE => X_LEGISLATION_CODE
249 ,X_OBJECT_VERSION_NUMBER => X_OBJECT_VERSION_NUMBER
250 ,X_REQUEST_ID => l_request_id
251 ,X_CREATED_BY => l_created_by
252 ,X_CREATION_DATE => l_creation_date
253 ,X_LAST_UPDATE_DATE => l_last_update_date
254 ,X_LAST_UPDATED_BY => l_last_updated_by
255 ,X_LAST_UPDATE_LOGIN => l_last_update_login
256 );
257 end;
258 --
259 end LOAD_ROW;
260
261 procedure TRANSLATE_ROW
262 (X_INFORMATION_TYPE in varchar2
263 ,X_DESCRIPTION in varchar2
264 ,X_OWNER in varchar2
265 )
266 is
267 begin
268 UPDATE per_people_info_types
269 SET description = X_DESCRIPTION
270 ,last_update_date = SYSDATE
271 ,last_updated_by = DECODE(X_OWNER,'SEED',1,0)
272 ,last_update_login = 0
273 WHERE USERENV('LANG') = (select language_code from fnd_languages
274 where installed_flag = 'B')
275 AND information_type = X_INFORMATION_TYPE;
276 end TRANSLATE_ROW;
277
278
279 END PER_PEOPLE_INFO_TYPES_PKG;