[Home] [Help]
PACKAGE BODY: APPS.PER_JOB_INFO_TYPES_PKG
Source
1 PACKAGE BODY PER_JOB_INFO_TYPES_PKG as
2 /* $Header: perjobit.pkb 120.1 2011/04/28 09:56:20 sidsaxen ship $ */
3
4 --------------------------------------------------------------------------------
5 g_dummy number(1); -- Dummy for cursor returns which are not needed
6 g_business_group_id number(15); -- For validating translation;
7 g_legislation_code varchar2(150); -- For validating translation;
8 --------------------------------------------------------------------------------
9 --
10 PROCEDURE UNIQUENESS_CHECK(P_INFORMATION_TYPE VARCHAR2,
11 P_ACTIVE_INACTIVE_FLAG VARCHAR2,
12 P_LEGISLATION_CODE VARCHAR2,
13 P_ROWID VARCHAR2,
14 P_DESCRIPTION VARCHAR2)
15 IS
16 L_DUMMY1 number;
17 CURSOR C1 IS
18 select 1
19 from PER_JOB_INFO_types t
20 where upper(t.description) = upper(P_DESCRIPTION)
21 and nvl(t.legislation_code, nvl(P_LEGISLATION_CODE, 'XXX') )
22 = nvl(P_LEGISLATION_CODE, 'XXX')
23 and (P_ROWID is null
24 or P_ROWID <> t.rowid);
25 BEGIN
26 OPEN C1;
27 FETCH C1 INTO L_DUMMY1;
28 IF C1%NOTFOUND THEN
29 CLOSE C1;
30 ELSE
31 CLOSE C1;
32 hr_utility.set_message('801','HR_7777_DEF_DESCR_EXISTS');
33 hr_utility.raise_error;
34 END IF;
35 end UNIQUENESS_CHECK;
36 --
37 procedure INSERT_ROW (
38 X_ROWID in out nocopy VARCHAR2,
39 X_INFORMATION_TYPE in VARCHAR2,
40 X_ACTIVE_INACTIVE_FLAG in VARCHAR2,
41 X_MULTIPLE_OCCURENCES_FLAG in VARCHAR2,
42 X_LEGISLATION_CODE in VARCHAR2,
43 X_REQUEST_ID in NUMBER,
44 X_OBJECT_VERSION_NUMBER in NUMBER,
45 X_DESCRIPTION in VARCHAR2,
46 X_CREATION_DATE in DATE,
47 X_CREATED_BY in NUMBER,
48 X_LAST_UPDATE_DATE in DATE,
49 X_LAST_UPDATED_BY in NUMBER,
50 X_LAST_UPDATE_LOGIN in NUMBER
51 ) is
52 cursor C is select ROWID from PER_JOB_INFO_TYPES
53 where INFORMATION_TYPE = X_INFORMATION_TYPE
54 ;
55 begin
56 insert into PER_JOB_INFO_TYPES (
57 INFORMATION_TYPE,
58 ACTIVE_INACTIVE_FLAG,
59 MULTIPLE_OCCURENCES_FLAG,
60 LEGISLATION_CODE,
61 REQUEST_ID,
62 OBJECT_VERSION_NUMBER,
63 DESCRIPTION,
64 CREATION_DATE,
65 CREATED_BY,
66 LAST_UPDATE_DATE,
67 LAST_UPDATED_BY,
68 LAST_UPDATE_LOGIN
69 ) values (
70 X_INFORMATION_TYPE,
71 X_ACTIVE_INACTIVE_FLAG,
72 X_MULTIPLE_OCCURENCES_FLAG,
73 X_LEGISLATION_CODE,
74 X_REQUEST_ID,
75 X_OBJECT_VERSION_NUMBER,
76 X_DESCRIPTION,
77 X_CREATION_DATE,
78 X_CREATED_BY,
79 X_LAST_UPDATE_DATE,
80 X_LAST_UPDATED_BY,
81 X_LAST_UPDATE_LOGIN
82 );
83
84 open c;
85 fetch c into X_ROWID;
86 if (c%notfound) then
87 close c;
88 raise no_data_found;
89 end if;
90 close c;
91
92 end INSERT_ROW;
93
94 procedure LOCK_ROW (
95 X_INFORMATION_TYPE in VARCHAR2,
96 X_ACTIVE_INACTIVE_FLAG in VARCHAR2,
97 X_MULTIPLE_OCCURENCES_FLAG in VARCHAR2,
98 X_LEGISLATION_CODE in VARCHAR2,
99 X_REQUEST_ID in NUMBER,
100 X_OBJECT_VERSION_NUMBER in NUMBER,
101 X_DESCRIPTION in VARCHAR2
102 ) is
103 cursor c is select
104 ACTIVE_INACTIVE_FLAG,
105 MULTIPLE_OCCURENCES_FLAG,
106 LEGISLATION_CODE,
107 REQUEST_ID,
108 OBJECT_VERSION_NUMBER
109 from PER_JOB_INFO_TYPES
110 where INFORMATION_TYPE = X_INFORMATION_TYPE
111 for update of INFORMATION_TYPE nowait;
112 recinfo c%rowtype;
113
114 begin
115 open c;
116 fetch c into recinfo;
117 if (c%notfound) then
118 close c;
119 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
120 app_exception.raise_exception;
121 end if;
122 close c;
123 if ( (recinfo.ACTIVE_INACTIVE_FLAG = X_ACTIVE_INACTIVE_FLAG)
124 AND (recinfo.MULTIPLE_OCCURENCES_FLAG = X_MULTIPLE_OCCURENCES_FLAG)
125 AND ((recinfo.LEGISLATION_CODE = X_LEGISLATION_CODE)
126 OR ((recinfo.LEGISLATION_CODE is null) AND (X_LEGISLATION_CODE is null)))
127 AND ((recinfo.REQUEST_ID = X_REQUEST_ID)
128 OR ((recinfo.REQUEST_ID is null) AND (X_REQUEST_ID is null)))
129 AND ((recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
130 OR ((recinfo.OBJECT_VERSION_NUMBER is null) AND (X_OBJECT_VERSION_NUMBER is null)))
131 ) then
132 null;
133 else
134 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
135 app_exception.raise_exception;
136 end if;
137
138 return;
139 end LOCK_ROW;
140
141 procedure UPDATE_ROW (
142 X_INFORMATION_TYPE in VARCHAR2,
143 X_ACTIVE_INACTIVE_FLAG in VARCHAR2,
144 X_MULTIPLE_OCCURENCES_FLAG in VARCHAR2,
145 X_LEGISLATION_CODE in VARCHAR2,
146 X_REQUEST_ID in NUMBER,
147 X_OBJECT_VERSION_NUMBER in NUMBER,
148 X_DESCRIPTION in VARCHAR2,
149 X_LAST_UPDATE_DATE in DATE,
150 X_LAST_UPDATED_BY in NUMBER,
151 X_LAST_UPDATE_LOGIN in NUMBER
152 ) is
153 begin
154 update PER_JOB_INFO_TYPES set
155 ACTIVE_INACTIVE_FLAG = X_ACTIVE_INACTIVE_FLAG,
156 MULTIPLE_OCCURENCES_FLAG = X_MULTIPLE_OCCURENCES_FLAG,
157 LEGISLATION_CODE = X_LEGISLATION_CODE,
158 REQUEST_ID = X_REQUEST_ID,
159 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
160 DESCRIPTION = X_DESCRIPTION,
161 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
162 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
163 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
164 where INFORMATION_TYPE = X_INFORMATION_TYPE;
165
166 if (sql%notfound) then
167 raise no_data_found;
168 end if;
169
170 end UPDATE_ROW;
171
172 procedure DELETE_ROW (
173 X_INFORMATION_TYPE in VARCHAR2
174 ) is
175 begin
176 --
177 -- Added the following code as a part of Zero Downtime Patching Project.
178 -- Code Starts Here.
179 --
180 per_ric_pkg.Chk_integrity(
181 p_entity_name=>'PER_JOB_INFO_TYPES',
182 p_ref_entity=>'PER_JOB_EXTRA_INFO',
183 p_ref_column_name=>'INFORMATION_TYPE',
184 p_ref_col_value_varchar=> X_INFORMATION_TYPE,
185 p_ref_col_value_number=> NULL,
186 p_ref_col_value_date=> NULL,
187 p_ref_type=>'DEL');
188 --
189 -- Code Ends Here
190 --
191 delete from PER_JOB_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_JOB_INFO_TYPES_PKG.LOAD_ROW';
210 l_rowid rowid;
211 l_request_id PER_JOB_INFO_types.request_id%TYPE;
212 l_progam_application_id PER_JOB_INFO_types.program_application_id%TYPE;
213 l_program_id PER_JOB_INFO_types.program_id%TYPE;
214 l_program_update_date PER_JOB_INFO_types.program_update_date%TYPE;
215 l_created_by PER_JOB_INFO_types.created_by%TYPE := 0;
216 l_creation_date PER_JOB_INFO_types.creation_date%TYPE := SYSDATE;
217 l_last_update_date PER_JOB_INFO_types.last_update_date%TYPE := SYSDATE;
218 l_last_updated_by PER_JOB_INFO_types.last_updated_by%TYPE := 0;
219 l_last_update_login PER_JOB_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_job_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_JOB_INFO_TYPES_PKG;