[Home] [Help]
PACKAGE BODY: APPS.HR_DOCUMENT_TYPES_PKG
Source
1 package body HR_DOCUMENT_TYPES_PKG as
2 /* $Header: perditlct.pkb 120.0 2005/06/24 07:52:30 appldev noship $ */
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 insert into HR_DOCUMENT_TYPES_TL (
114 DOCUMENT_TYPE_ID,
115 DOCUMENT_TYPE,
116 DESCRIPTION,
117 CREATED_BY,
118 CREATION_DATE,
119 LAST_UPDATED_BY,
120 LAST_UPDATE_DATE,
121 LAST_UPDATE_LOGIN,
122 LANGUAGE,
123 SOURCE_LANGUAGE
124 ) select
125 X_DOCUMENT_TYPE_ID,
126 X_DOCUMENT_TYPE,
127 X_DESCRIPTION,
128 X_CREATED_BY,
129 X_CREATION_DATE,
130 X_LAST_UPDATED_BY,
131 X_LAST_UPDATE_DATE,
132 X_LAST_UPDATE_LOGIN,
133 L.LANGUAGE_CODE,
134 userenv('LANG')
135 from FND_LANGUAGES L
136 where L.INSTALLED_FLAG in ('I', 'B')
137 and not exists
138 (select NULL
139 from HR_DOCUMENT_TYPES_TL T
140 where T.DOCUMENT_TYPE_ID = X_DOCUMENT_TYPE_ID
141 and T.LANGUAGE = L.LANGUAGE_CODE);
142
143 end INSERT_ROW;
144 --
145 procedure UPDATE_ROW (
146 X_DOCUMENT_TYPE_ID in NUMBER,
147 X_SYSTEM_DOCUMENT_TYPE in VARCHAR2,
148 X_CATEGORY_CODE in VARCHAR2,
149 X_SUB_CATEGORY_CODE in VARCHAR2,
150 X_ACTIVE_INACTIVE_FLAG in VARCHAR2,
151 X_MULTIPLE_OCCURENCES_FLAG in VARCHAR2,
152 X_LEGISLATION_CODE in VARCHAR2,
153 X_AUTHORIZATION_REQUIRED in VARCHAR2,
154 X_WARNING_PERIOD in NUMBER,
155 X_OBJECT_VERSION_NUMBER in NUMBER,
156 X_DOCUMENT_TYPE in VARCHAR2,
157 X_DESCRIPTION in VARCHAR2,
158 X_LAST_UPDATE_DATE in DATE,
159 X_LAST_UPDATED_BY in NUMBER,
160 X_LAST_UPDATE_LOGIN in NUMBER
161 ) is
162 begin
163 update HR_DOCUMENT_TYPES set
164 CATEGORY_CODE = X_CATEGORY_CODE,
165 SUB_CATEGORY_CODE = X_SUB_CATEGORY_CODE,
166 ACTIVE_INACTIVE_FLAG = X_ACTIVE_INACTIVE_FLAG,
167 MULTIPLE_OCCURENCES_FLAG = X_MULTIPLE_OCCURENCES_FLAG,
168 LEGISLATION_CODE = X_LEGISLATION_CODE,
169 AUTHORIZATION_REQUIRED = X_AUTHORIZATION_REQUIRED,
170 WARNING_PERIOD = X_WARNING_PERIOD,
171 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
172 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
173 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
174 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
175 where DOCUMENT_TYPE_ID = X_DOCUMENT_TYPE_ID;
176
177 if (sql%notfound) then
178 raise no_data_found;
179 end if;
180
181 update HR_DOCUMENT_TYPES_TL set
182 DOCUMENT_TYPE = X_DOCUMENT_TYPE,
183 DESCRIPTION = X_DESCRIPTION,
184 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
185 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
186 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
187 SOURCE_LANGUAGE = userenv('LANG')
188 where DOCUMENT_TYPE_ID = X_DOCUMENT_TYPE_ID
189 and userenv('LANG') in (LANGUAGE, SOURCE_LANGUAGE);
190
191 if (sql%notfound) then
192 raise no_data_found;
193 end if;
194
195 end UPDATE_ROW;
196 --
197 procedure DELETE_ROW (
198 X_DOCUMENT_TYPE_ID in NUMBER
199 ) is
200 begin
201 delete from HR_DOCUMENT_TYPES_TL
202 where DOCUMENT_TYPE_ID = X_DOCUMENT_TYPE_ID;
203
204 if (sql%notfound) then
205 raise no_data_found;
206 end if;
207
208 delete from HR_DOCUMENT_TYPES
209 where DOCUMENT_TYPE_ID = X_DOCUMENT_TYPE_ID;
210 if (sql%notfound) then
211 raise no_data_found;
212 end if;
213 end DELETE_ROW;
214 --
215 procedure ADD_LANGUAGE
216 is
217 begin
218 delete from HR_DOCUMENT_TYPES_TL T
219 where not exists
220 (select NULL
221 from HR_DOCUMENT_TYPES B
222 where B.DOCUMENT_TYPE_ID = T.DOCUMENT_TYPE_ID
223 );
224
225 update HR_DOCUMENT_TYPES_TL T set (
226 DOCUMENT_TYPE
227 ) = (select
228 B.DOCUMENT_TYPE
229 from HR_DOCUMENT_TYPES_TL B
230 where B.DOCUMENT_TYPE_ID = T.DOCUMENT_TYPE_ID
231 and B.LANGUAGE = T.SOURCE_LANGUAGE)
232 where (
233 T.DOCUMENT_TYPE_ID,
234 T.LANGUAGE
235 ) in (select
236 SUBT.DOCUMENT_TYPE_ID,
237 SUBT.LANGUAGE
238 from HR_DOCUMENT_TYPES_TL SUBB, HR_DOCUMENT_TYPES_TL SUBT
239 where SUBB.DOCUMENT_TYPE_ID = SUBT.DOCUMENT_TYPE_ID
240 and SUBB.LANGUAGE = SUBT.SOURCE_LANGUAGE
241 and (SUBB.DOCUMENT_TYPE <> SUBT.DOCUMENT_TYPE
242 ));
243
244 insert into HR_DOCUMENT_TYPES_TL (
245 DOCUMENT_TYPE_ID,
246 DOCUMENT_TYPE,
247 DESCRIPTION,
248 CREATED_BY,
249 CREATION_DATE,
250 LAST_UPDATED_BY,
251 LAST_UPDATE_DATE,
252 LAST_UPDATE_LOGIN,
253 LANGUAGE,
254 SOURCE_LANGUAGE
255 ) select
256 B.DOCUMENT_TYPE_ID,
257 B.DOCUMENT_TYPE,
258 B.DESCRIPTION,
259 B.CREATED_BY,
260 B.CREATION_DATE,
261 B.LAST_UPDATED_BY,
262 B.LAST_UPDATE_DATE,
263 B.LAST_UPDATE_LOGIN,
264 L.LANGUAGE_CODE,
265 B.SOURCE_LANGUAGE
266 from HR_DOCUMENT_TYPES_TL B, FND_LANGUAGES L
267 where L.INSTALLED_FLAG in ('I', 'B')
268 and B.LANGUAGE = userenv('LANG')
269 and not exists
270 (select NULL
271 from HR_DOCUMENT_TYPES_TL T
272 where T.DOCUMENT_TYPE_ID = B.DOCUMENT_TYPE_ID
273 and T.LANGUAGE = L.LANGUAGE_CODE);
274 end ADD_LANGUAGE;
275
276 --
277 procedure LOAD_ROW (
278 X_SYSTEM_DOCUMENT_TYPE in VARCHAR2,
279 X_CATEGORY_CODE in VARCHAR2,
280 X_SUB_CATEGORY_CODE in VARCHAR2,
281 X_ACTIVE_INACTIVE_FLAG in VARCHAR2,
282 X_MULTIPLE_OCCURENCES_FLAG in VARCHAR2,
283 X_LEGISLATION_CODE in VARCHAR2,
284 X_AUTHORIZATION_REQUIRED in VARCHAR2,
285 X_WARNING_PERIOD in NUMBER,
286 X_OBJECT_VERSION_NUMBER in NUMBER,
287 X_OWNER in VARCHAR2,
288 X_DOCUMENT_TYPE in VARCHAR2,
289 X_DESCRIPTION in VARCHAR2
290 ) is
291 X_CREATION_DATE DATE;
292 X_CREATED_BY NUMBER;
293 X_LAST_UPDATE_DATE DATE;
294 X_LAST_UPDATED_BY NUMBER;
295 X_LAST_UPDATE_LOGIN NUMBER;
296 X_DOCUMENT_TYPE_ID NUMBER;
297 begin
298 --
299 KEY_TO_IDS (
300 X_SYSTEM_DOCUMENT_TYPE,
301 X_LEGISLATION_CODE,
302 X_DOCUMENT_TYPE_ID
303 );
304 --
305 OWNER_TO_WHO (
306 X_OWNER,
307 X_CREATION_DATE,
308 X_CREATED_BY,
309 X_LAST_UPDATE_DATE,
310 X_LAST_UPDATED_BY,
311 X_LAST_UPDATE_LOGIN
312 );
313 --
314 begin
315 UPDATE_ROW (
316 X_DOCUMENT_TYPE_ID,
317 X_SYSTEM_DOCUMENT_TYPE,
318 X_CATEGORY_CODE,
319 X_SUB_CATEGORY_CODE,
320 X_ACTIVE_INACTIVE_FLAG,
321 X_MULTIPLE_OCCURENCES_FLAG,
322 X_LEGISLATION_CODE,
323 X_AUTHORIZATION_REQUIRED,
324 X_WARNING_PERIOD,
325 X_OBJECT_VERSION_NUMBER,
326 X_DOCUMENT_TYPE,
327 X_DESCRIPTION,
328 X_LAST_UPDATE_DATE,
329 X_LAST_UPDATED_BY,
330 X_LAST_UPDATE_LOGIN
331 );
332 exception
333 when no_data_found then
334 INSERT_ROW (
335 X_DOCUMENT_TYPE_ID,
336 X_SYSTEM_DOCUMENT_TYPE,
337 X_CATEGORY_CODE,
338 X_SUB_CATEGORY_CODE,
339 X_ACTIVE_INACTIVE_FLAG,
340 X_MULTIPLE_OCCURENCES_FLAG,
341 X_LEGISLATION_CODE,
342 X_AUTHORIZATION_REQUIRED,
343 X_WARNING_PERIOD,
344 X_OBJECT_VERSION_NUMBER,
345 X_DOCUMENT_TYPE,
346 X_DESCRIPTION,
347 X_CREATION_DATE,
348 X_CREATED_BY,
349 X_LAST_UPDATE_DATE,
350 X_LAST_UPDATED_BY,
351 X_LAST_UPDATE_LOGIN
352 );
353 end;
354 end LOAD_ROW;
355 --
356 procedure TRANSLATE_ROW (
357 X_SYSTEM_DOCUMENT_TYPE in VARCHAR2,
358 X_LEGISLATION_CODE in VARCHAR2,
359 X_DOCUMENT_TYPE in VARCHAR2,
360 X_DESCRIPTION in VARCHAR2,
361 X_OWNER in VARCHAR2
362 ) is
363 X_CREATION_DATE DATE;
364 X_CREATED_BY NUMBER;
365 X_LAST_UPDATE_DATE DATE;
366 X_LAST_UPDATED_BY NUMBER;
367 X_LAST_UPDATE_LOGIN NUMBER;
368 X_DOCUMENT_TYPE_ID NUMBER;
369 begin
370 --
371 KEY_TO_IDS (
372 X_SYSTEM_DOCUMENT_TYPE,
373 X_LEGISLATION_CODE,
374 X_DOCUMENT_TYPE_ID
375 );
376 --
377 OWNER_TO_WHO (
378 X_OWNER,
379 X_CREATION_DATE,
380 X_CREATED_BY,
381 X_LAST_UPDATE_DATE,
382 X_LAST_UPDATED_BY,
383 X_LAST_UPDATE_LOGIN
384 );
385 --
386 update HR_DOCUMENT_TYPES_TL
387 set DOCUMENT_TYPE = X_DOCUMENT_TYPE,
388 DESCRIPTION = X_DESCRIPTION,
389 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
390 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
391 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
392 SOURCE_LANGUAGE = userenv('LANG')
393 where DOCUMENT_TYPE_ID = X_DOCUMENT_TYPE_ID
394 and userenv('LANG') in (LANGUAGE,SOURCE_LANGUAGE);
395
396 end TRANSLATE_ROW;
397 --
398 end HR_DOCUMENT_TYPES_PKG;