[Home] [Help]
PACKAGE BODY: APPS.PER_PEOPLE_INFO_TYPES_PKG
Source
1 PACKAGE BODY PER_PEOPLE_INFO_TYPES_PKG as
2 /* $Header: perperit.pkb 115.3 2002/12/06 15:20:29 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_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 delete from PER_PEOPLE_INFO_TYPES
177 where INFORMATION_TYPE = X_INFORMATION_TYPE;
178
179 if (sql%notfound) then
180 raise no_data_found;
181 end if;
182 end DELETE_ROW;
183
184 procedure LOAD_ROW
185 (X_INFORMATION_TYPE in varchar2
186 ,X_ACTIVE_INACTIVE_FLAG in varchar2
187 ,X_MULTIPLE_OCCURENCES_FLAG in varchar2
188 ,X_DESCRIPTION in varchar2
189 ,X_LEGISLATION_CODE in varchar2
190 ,X_OBJECT_VERSION_NUMBER in number
191 ,X_OWNER in varchar2
192 )
193 is
194 l_proc VARCHAR2(61) := 'PER_PEOPLE_INFO_TYPES_PKG.LOAD_ROW';
195 l_rowid rowid;
196 l_request_id PER_PEOPLE_INFO_types.request_id%TYPE;
197 l_progam_application_id PER_PEOPLE_INFO_types.program_application_id%TYPE;
198 l_program_id PER_PEOPLE_INFO_types.program_id%TYPE;
199 l_program_update_date PER_PEOPLE_INFO_types.program_update_date%TYPE;
200 l_created_by PER_PEOPLE_INFO_types.created_by%TYPE := 0;
201 l_creation_date PER_PEOPLE_INFO_types.creation_date%TYPE := SYSDATE;
202 l_last_update_date PER_PEOPLE_INFO_types.last_update_date%TYPE := SYSDATE;
203 l_last_updated_by PER_PEOPLE_INFO_types.last_updated_by%TYPE := 0;
204 l_last_update_login PER_PEOPLE_INFO_types.last_update_login%TYPE := 0;
205 begin
206 -- Translate developer keys to internal parameters
207 if X_OWNER = 'SEED' then
208 l_created_by := 1;
209 l_last_updated_by := 1;
210 end if;
211 -- Update or insert row as appropriate
212 begin
213 UPDATE_ROW
214 (X_INFORMATION_TYPE => X_INFORMATION_TYPE
215 ,X_ACTIVE_INACTIVE_FLAG => X_ACTIVE_INACTIVE_FLAG
216 ,X_MULTIPLE_OCCURENCES_FLAG => X_MULTIPLE_OCCURENCES_FLAG
217 ,X_DESCRIPTION => X_DESCRIPTION
218 ,X_LEGISLATION_CODE => X_LEGISLATION_CODE
219 ,X_OBJECT_VERSION_NUMBER => X_OBJECT_VERSION_NUMBER
220 ,X_REQUEST_ID => l_request_id
221 ,X_LAST_UPDATE_DATE => l_last_update_date
222 ,X_LAST_UPDATED_BY => l_last_updated_by
223 ,X_LAST_UPDATE_LOGIN => l_last_update_login
224 );
225 exception
226 when no_data_found then
227 INSERT_ROW
228 (X_ROWID => l_rowid
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_CREATED_BY => l_created_by
237 ,X_CREATION_DATE => l_creation_date
238 ,X_LAST_UPDATE_DATE => l_last_update_date
239 ,X_LAST_UPDATED_BY => l_last_updated_by
240 ,X_LAST_UPDATE_LOGIN => l_last_update_login
241 );
242 end;
243 --
244 end LOAD_ROW;
245
246 procedure TRANSLATE_ROW
247 (X_INFORMATION_TYPE in varchar2
248 ,X_DESCRIPTION in varchar2
249 ,X_OWNER in varchar2
250 )
251 is
252 begin
253 UPDATE per_people_info_types
254 SET description = X_DESCRIPTION
255 ,last_update_date = SYSDATE
256 ,last_updated_by = DECODE(X_OWNER,'SEED',1,0)
257 ,last_update_login = 0
258 WHERE USERENV('LANG') = (select language_code from fnd_languages
259 where installed_flag = 'B')
260 AND information_type = X_INFORMATION_TYPE;
261 end TRANSLATE_ROW;
262
263
264 END PER_PEOPLE_INFO_TYPES_PKG;