[Home] [Help]
PACKAGE BODY: APPS.HR_DOCUMENT_TYPES_PKG
Source
1 package body HR_DOCUMENT_TYPES_PKG as
2 /* $Header: perditlct.pkb 120.1 2011/04/28 09:57:06 sidsaxen ship $ */
3 --
4 procedure KEY_TO_IDS (
5 X_SYSTEM_DOCUMENT_TYPE in VARCHAR2,
6 X_LEGISLATION_CODE in VARCHAR2,
7 X_DOCUMENT_TYPE_ID out nocopy NUMBER
8 ) is
9 --
10 cursor CSR_DOCUMENT_TYPE (
11 X_SYS_DOC_TYPE in VARCHAR2,
12 X_LEG_CODE in VARCHAR2
13 ) is
14 select HDT.DOCUMENT_TYPE_ID
15 from HR_DOCUMENT_TYPES HDT
16 where HDT.SYSTEM_DOCUMENT_TYPE = X_SYS_DOC_TYPE
17 and ((X_LEG_CODE is NULL and HDT.LEGISLATION_CODE is NULL)
18 or
19 (X_LEG_CODE = HDT.LEGISLATION_CODE));
20 --
21 cursor CSR_SEQUENCE is
22 select HR_DOCUMENT_TYPES_S.nextval
23 from dual;
24 --
25 begin
26 open CSR_DOCUMENT_TYPE (X_SYSTEM_DOCUMENT_TYPE,X_LEGISLATION_CODE);
27 fetch CSR_DOCUMENT_TYPE into X_DOCUMENT_TYPE_ID;
28 if (CSR_DOCUMENT_TYPE%notfound) then
29 open CSR_SEQUENCE;
30 fetch CSR_SEQUENCE into X_DOCUMENT_TYPE_ID;
31 close CSR_SEQUENCE;
32 end if;
33 close CSR_DOCUMENT_TYPE;
34 end KEY_TO_IDS;
35
36 --
37 procedure OWNER_TO_WHO (
38 X_OWNER in VARCHAR2,
39 X_CREATION_DATE out nocopy DATE,
40 X_CREATED_BY out nocopy NUMBER,
41 X_LAST_UPDATE_DATE out nocopy DATE,
42 X_LAST_UPDATED_BY out nocopy NUMBER,
43 X_LAST_UPDATE_LOGIN out nocopy NUMBER
44 ) is
45 begin
46 if X_OWNER = 'SEED' then
47 X_CREATED_BY := 1;
48 X_LAST_UPDATED_BY := 1;
49 else
50 X_CREATED_BY := 0;
51 X_LAST_UPDATED_BY := 0;
52 end if;
53 X_CREATION_DATE := sysdate;
54 X_LAST_UPDATE_DATE := sysdate;
55 X_LAST_UPDATE_LOGIN := 0;
56 end OWNER_TO_WHO;
57 --
58 procedure INSERT_ROW (
59 X_DOCUMENT_TYPE_ID in NUMBER,
60 X_SYSTEM_DOCUMENT_TYPE in VARCHAR2,
61 X_CATEGORY_CODE in VARCHAR2,
62 X_SUB_CATEGORY_CODE in VARCHAR2,
63 X_ACTIVE_INACTIVE_FLAG in VARCHAR2,
64 X_MULTIPLE_OCCURENCES_FLAG in VARCHAR2,
65 X_LEGISLATION_CODE in VARCHAR2,
66 X_AUTHORIZATION_REQUIRED in VARCHAR2,
67 X_WARNING_PERIOD in NUMBER,
68 X_OBJECT_VERSION_NUMBER in NUMBER,
69 X_DOCUMENT_TYPE in VARCHAR2,
70 X_DESCRIPTION in VARCHAR2,
71 X_CREATION_DATE in DATE,
72 X_CREATED_BY in NUMBER,
73 X_LAST_UPDATE_DATE in DATE,
74 X_LAST_UPDATED_BY in NUMBER,
75 X_LAST_UPDATE_LOGIN in NUMBER
76 ) is
77 begin
78 --
79 insert into HR_DOCUMENT_TYPES (
80 DOCUMENT_TYPE_ID,
81 SYSTEM_DOCUMENT_TYPE,
82 CATEGORY_CODE,
83 SUB_CATEGORY_CODE,
84 ACTIVE_INACTIVE_FLAG,
85 MULTIPLE_OCCURENCES_FLAG,
86 LEGISLATION_CODE,
87 AUTHORIZATION_REQUIRED,
88 WARNING_PERIOD,
89 OBJECT_VERSION_NUMBER,
90 CREATION_DATE,
91 CREATED_BY,
92 LAST_UPDATE_DATE,
93 LAST_UPDATED_BY,
94 LAST_UPDATE_LOGIN)
95 values (
96 X_DOCUMENT_TYPE_ID,
97 X_SYSTEM_DOCUMENT_TYPE,
98 X_CATEGORY_CODE,
99 X_SUB_CATEGORY_CODE,
100 X_ACTIVE_INACTIVE_FLAG,
101 X_MULTIPLE_OCCURENCES_FLAG,
102 X_LEGISLATION_CODE,
103 X_AUTHORIZATION_REQUIRED,
104 X_WARNING_PERIOD,
105 X_OBJECT_VERSION_NUMBER,
106 X_CREATION_DATE,
107 X_CREATED_BY,
108 X_LAST_UPDATE_DATE,
109 X_LAST_UPDATED_BY,
110 X_LAST_UPDATE_LOGIN
111 );
112 --
113
114 --
115 -- Added the following code as a part of Zero Downtime Patching Project.
116 -- Code Starts Here.
117 --
118 per_ric_pkg.Chk_integrity (
119 p_entity_name=> 'HR_DOCUMENT_TYPES_TL',
120 p_ref_entity=> 'HR_DOCUMENT_TYPES',
121 p_ref_column_name=>'DOCUMENT_TYPE_ID',
122 p_ref_col_value_number=>X_DOCUMENT_TYPE_ID,
123 p_ref_col_value_varchar=>NULL,
124 p_ref_col_value_date=>NULL,
125 p_ref_type=>'INS');
126 --
127 -- Code Ends Here
128 --
129
130 insert into HR_DOCUMENT_TYPES_TL (
131 DOCUMENT_TYPE_ID,
132 DOCUMENT_TYPE,
133 DESCRIPTION,
134 CREATED_BY,
135 CREATION_DATE,
136 LAST_UPDATED_BY,
137 LAST_UPDATE_DATE,
138 LAST_UPDATE_LOGIN,
139 LANGUAGE,
140 SOURCE_LANGUAGE
141 ) select
142 X_DOCUMENT_TYPE_ID,
143 X_DOCUMENT_TYPE,
144 X_DESCRIPTION,
145 X_CREATED_BY,
146 X_CREATION_DATE,
147 X_LAST_UPDATED_BY,
148 X_LAST_UPDATE_DATE,
149 X_LAST_UPDATE_LOGIN,
150 L.LANGUAGE_CODE,
151 userenv('LANG')
152 from FND_LANGUAGES L
153 where L.INSTALLED_FLAG in ('I', 'B')
154 and not exists
155 (select NULL
156 from HR_DOCUMENT_TYPES_TL T
157 where T.DOCUMENT_TYPE_ID = X_DOCUMENT_TYPE_ID
158 and T.LANGUAGE = L.LANGUAGE_CODE);
159
160 end INSERT_ROW;
161 --
162 procedure UPDATE_ROW (
163 X_DOCUMENT_TYPE_ID in NUMBER,
164 X_SYSTEM_DOCUMENT_TYPE in VARCHAR2,
165 X_CATEGORY_CODE in VARCHAR2,
166 X_SUB_CATEGORY_CODE in VARCHAR2,
167 X_ACTIVE_INACTIVE_FLAG in VARCHAR2,
168 X_MULTIPLE_OCCURENCES_FLAG in VARCHAR2,
169 X_LEGISLATION_CODE in VARCHAR2,
170 X_AUTHORIZATION_REQUIRED in VARCHAR2,
171 X_WARNING_PERIOD in NUMBER,
172 X_OBJECT_VERSION_NUMBER in NUMBER,
173 X_DOCUMENT_TYPE in VARCHAR2,
174 X_DESCRIPTION in VARCHAR2,
175 X_LAST_UPDATE_DATE in DATE,
176 X_LAST_UPDATED_BY in NUMBER,
177 X_LAST_UPDATE_LOGIN in NUMBER
178 ) is
179 begin
180 update HR_DOCUMENT_TYPES set
181 CATEGORY_CODE = X_CATEGORY_CODE,
182 SUB_CATEGORY_CODE = X_SUB_CATEGORY_CODE,
183 ACTIVE_INACTIVE_FLAG = X_ACTIVE_INACTIVE_FLAG,
184 MULTIPLE_OCCURENCES_FLAG = X_MULTIPLE_OCCURENCES_FLAG,
185 LEGISLATION_CODE = X_LEGISLATION_CODE,
186 AUTHORIZATION_REQUIRED = X_AUTHORIZATION_REQUIRED,
187 WARNING_PERIOD = X_WARNING_PERIOD,
188 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
189 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
190 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
191 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
192 where DOCUMENT_TYPE_ID = X_DOCUMENT_TYPE_ID;
193
194 if (sql%notfound) then
195 raise no_data_found;
196 end if;
197
198 update HR_DOCUMENT_TYPES_TL set
199 DOCUMENT_TYPE = X_DOCUMENT_TYPE,
200 DESCRIPTION = X_DESCRIPTION,
201 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
202 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
203 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
204 SOURCE_LANGUAGE = userenv('LANG')
205 where DOCUMENT_TYPE_ID = X_DOCUMENT_TYPE_ID
206 and userenv('LANG') in (LANGUAGE, SOURCE_LANGUAGE);
207
208 if (sql%notfound) then
209 raise no_data_found;
210 end if;
211
212 end UPDATE_ROW;
213 --
214 procedure DELETE_ROW (
215 X_DOCUMENT_TYPE_ID in NUMBER
216 ) is
217 begin
218 delete from HR_DOCUMENT_TYPES_TL
219 where DOCUMENT_TYPE_ID = X_DOCUMENT_TYPE_ID;
220
221 if (sql%notfound) then
222 raise no_data_found;
223 end if;
224
225 --
226 -- Added the following code as a part of Zero Downtime Patching Project.
227 -- Code Starts Here.
228 --
229 per_ric_pkg.Chk_integrity (
230 p_entity_name=> 'HR_DOCUMENT_TYPES',
231 p_ref_entity=> 'HR_DOCUMENT_TYPES_TL',
232 p_ref_column_name=>'DOCUMENT_TYPE_ID',
233 p_ref_col_value_number=>X_DOCUMENT_TYPE_ID,
234 p_ref_col_value_varchar=>NULL,
235 p_ref_col_value_date=>NULL,
236 p_ref_type=>'DEL');
237 --
238 -- Code Ends Here
239 --
240
241 delete from HR_DOCUMENT_TYPES
242 where DOCUMENT_TYPE_ID = X_DOCUMENT_TYPE_ID;
243 if (sql%notfound) then
244 raise no_data_found;
245 end if;
246 end DELETE_ROW;
247 --
248 procedure ADD_LANGUAGE
249 is
250 begin
251 delete from HR_DOCUMENT_TYPES_TL T
252 where not exists
253 (select NULL
254 from HR_DOCUMENT_TYPES B
255 where B.DOCUMENT_TYPE_ID = T.DOCUMENT_TYPE_ID
256 );
257
258 update HR_DOCUMENT_TYPES_TL T set (
259 DOCUMENT_TYPE
260 ) = (select
261 B.DOCUMENT_TYPE
262 from HR_DOCUMENT_TYPES_TL B
263 where B.DOCUMENT_TYPE_ID = T.DOCUMENT_TYPE_ID
264 and B.LANGUAGE = T.SOURCE_LANGUAGE)
265 where (
266 T.DOCUMENT_TYPE_ID,
267 T.LANGUAGE
268 ) in (select
269 SUBT.DOCUMENT_TYPE_ID,
270 SUBT.LANGUAGE
271 from HR_DOCUMENT_TYPES_TL SUBB, HR_DOCUMENT_TYPES_TL SUBT
272 where SUBB.DOCUMENT_TYPE_ID = SUBT.DOCUMENT_TYPE_ID
273 and SUBB.LANGUAGE = SUBT.SOURCE_LANGUAGE
274 and (SUBB.DOCUMENT_TYPE <> SUBT.DOCUMENT_TYPE
275 ));
276
277 insert into HR_DOCUMENT_TYPES_TL (
278 DOCUMENT_TYPE_ID,
279 DOCUMENT_TYPE,
280 DESCRIPTION,
281 CREATED_BY,
282 CREATION_DATE,
283 LAST_UPDATED_BY,
284 LAST_UPDATE_DATE,
285 LAST_UPDATE_LOGIN,
286 LANGUAGE,
287 SOURCE_LANGUAGE
288 ) select
289 B.DOCUMENT_TYPE_ID,
290 B.DOCUMENT_TYPE,
291 B.DESCRIPTION,
292 B.CREATED_BY,
293 B.CREATION_DATE,
294 B.LAST_UPDATED_BY,
295 B.LAST_UPDATE_DATE,
296 B.LAST_UPDATE_LOGIN,
297 L.LANGUAGE_CODE,
298 B.SOURCE_LANGUAGE
299 from HR_DOCUMENT_TYPES_TL B, FND_LANGUAGES L
300 where L.INSTALLED_FLAG in ('I', 'B')
301 and B.LANGUAGE = userenv('LANG')
302 and not exists
303 (select NULL
304 from HR_DOCUMENT_TYPES_TL T
305 where T.DOCUMENT_TYPE_ID = B.DOCUMENT_TYPE_ID
306 and T.LANGUAGE = L.LANGUAGE_CODE);
307 end ADD_LANGUAGE;
308
309 --
310 procedure LOAD_ROW (
311 X_SYSTEM_DOCUMENT_TYPE in VARCHAR2,
312 X_CATEGORY_CODE in VARCHAR2,
313 X_SUB_CATEGORY_CODE in VARCHAR2,
314 X_ACTIVE_INACTIVE_FLAG in VARCHAR2,
315 X_MULTIPLE_OCCURENCES_FLAG in VARCHAR2,
316 X_LEGISLATION_CODE in VARCHAR2,
317 X_AUTHORIZATION_REQUIRED in VARCHAR2,
318 X_WARNING_PERIOD in NUMBER,
319 X_OBJECT_VERSION_NUMBER in NUMBER,
320 X_OWNER in VARCHAR2,
321 X_DOCUMENT_TYPE in VARCHAR2,
322 X_DESCRIPTION in VARCHAR2
323 ) is
324 X_CREATION_DATE DATE;
325 X_CREATED_BY NUMBER;
326 X_LAST_UPDATE_DATE DATE;
327 X_LAST_UPDATED_BY NUMBER;
328 X_LAST_UPDATE_LOGIN NUMBER;
329 X_DOCUMENT_TYPE_ID NUMBER;
330 begin
331 --
332 KEY_TO_IDS (
333 X_SYSTEM_DOCUMENT_TYPE,
334 X_LEGISLATION_CODE,
335 X_DOCUMENT_TYPE_ID
336 );
337 --
338 OWNER_TO_WHO (
339 X_OWNER,
340 X_CREATION_DATE,
341 X_CREATED_BY,
342 X_LAST_UPDATE_DATE,
343 X_LAST_UPDATED_BY,
344 X_LAST_UPDATE_LOGIN
345 );
346 --
347 begin
348 UPDATE_ROW (
349 X_DOCUMENT_TYPE_ID,
350 X_SYSTEM_DOCUMENT_TYPE,
351 X_CATEGORY_CODE,
352 X_SUB_CATEGORY_CODE,
353 X_ACTIVE_INACTIVE_FLAG,
354 X_MULTIPLE_OCCURENCES_FLAG,
355 X_LEGISLATION_CODE,
356 X_AUTHORIZATION_REQUIRED,
357 X_WARNING_PERIOD,
358 X_OBJECT_VERSION_NUMBER,
359 X_DOCUMENT_TYPE,
360 X_DESCRIPTION,
361 X_LAST_UPDATE_DATE,
362 X_LAST_UPDATED_BY,
363 X_LAST_UPDATE_LOGIN
364 );
365 exception
366 when no_data_found then
367 INSERT_ROW (
368 X_DOCUMENT_TYPE_ID,
369 X_SYSTEM_DOCUMENT_TYPE,
370 X_CATEGORY_CODE,
371 X_SUB_CATEGORY_CODE,
372 X_ACTIVE_INACTIVE_FLAG,
373 X_MULTIPLE_OCCURENCES_FLAG,
374 X_LEGISLATION_CODE,
375 X_AUTHORIZATION_REQUIRED,
376 X_WARNING_PERIOD,
377 X_OBJECT_VERSION_NUMBER,
378 X_DOCUMENT_TYPE,
379 X_DESCRIPTION,
380 X_CREATION_DATE,
381 X_CREATED_BY,
382 X_LAST_UPDATE_DATE,
383 X_LAST_UPDATED_BY,
384 X_LAST_UPDATE_LOGIN
385 );
386 end;
387 end LOAD_ROW;
388 --
389 procedure TRANSLATE_ROW (
390 X_SYSTEM_DOCUMENT_TYPE in VARCHAR2,
391 X_LEGISLATION_CODE in VARCHAR2,
392 X_DOCUMENT_TYPE in VARCHAR2,
393 X_DESCRIPTION in VARCHAR2,
394 X_OWNER in VARCHAR2
395 ) is
396 X_CREATION_DATE DATE;
397 X_CREATED_BY NUMBER;
398 X_LAST_UPDATE_DATE DATE;
399 X_LAST_UPDATED_BY NUMBER;
400 X_LAST_UPDATE_LOGIN NUMBER;
401 X_DOCUMENT_TYPE_ID NUMBER;
402 begin
403 --
404 KEY_TO_IDS (
405 X_SYSTEM_DOCUMENT_TYPE,
406 X_LEGISLATION_CODE,
407 X_DOCUMENT_TYPE_ID
408 );
409 --
410 OWNER_TO_WHO (
411 X_OWNER,
412 X_CREATION_DATE,
413 X_CREATED_BY,
414 X_LAST_UPDATE_DATE,
415 X_LAST_UPDATED_BY,
416 X_LAST_UPDATE_LOGIN
417 );
418 --
422 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
419 update HR_DOCUMENT_TYPES_TL
420 set DOCUMENT_TYPE = X_DOCUMENT_TYPE,
421 DESCRIPTION = X_DESCRIPTION,
423 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
424 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
425 SOURCE_LANGUAGE = userenv('LANG')
426 where DOCUMENT_TYPE_ID = X_DOCUMENT_TYPE_ID
427 and userenv('LANG') in (LANGUAGE,SOURCE_LANGUAGE);
428
429 end TRANSLATE_ROW;
430 --
431 end HR_DOCUMENT_TYPES_PKG;