[Home] [Help]
PACKAGE BODY: APPS.PER_ASS_STATUS_TYPE_AMENDS_PKG
Source
1 package body PER_ASS_STATUS_TYPE_AMENDS_PKG as
2 /* $Header: peastamd.pkb 120.3 2011/04/28 10:15:37 sidsaxen ship $ */
3 procedure KEY_TO_IDS (
4 X_USER_STATUS in VARCHAR2,
5 X_BUSINESS_GROUP_NAME in VARCHAR2,
6 X_ASSIGNMENT_STATUS_TYPE_ID out nocopy VARCHAR2,
7 X_BUSINESS_GROUP_ID out nocopy NUMBER
8 ) is
9 cursor CSR_BUSINESS_GROUP (
10 L_NAME in VARCHAR2
11 ) is
12 select BUSINESS_GROUP_ID
13 from PER_BUSINESS_GROUPS
14 WHERE NAME = L_NAME;
15
16 cursor CSR_ASS_STATUS_TYPE_AMEND (
17 L_STATUS in VARCHAR2,
18 L_BUSINESS_GROUP_ID in NUMBER
19 ) is
20
21 select ASS_STATUS_TYPE_AMEND_ID
22 from PER_ASS_STATUS_TYPE_AMENDS
23 where USER_STATUS = L_STATUS
24 and BUSINESS_GROUP_ID = L_BUSINESS_GROUP_ID;
25
26 L_BUSINESS_GROUP_ID NUMBER;
27 begin
28 open CSR_BUSINESS_GROUP (
29 X_BUSINESS_GROUP_NAME
30 );
31 fetch CSR_BUSINESS_GROUP into L_BUSINESS_GROUP_ID;
32 close CSR_BUSINESS_GROUP;
33
34 X_BUSINESS_GROUP_ID := L_BUSINESS_GROUP_ID;
35
36 open CSR_ASS_STATUS_TYPE_AMEND (
37 X_USER_STATUS,
38 L_BUSINESS_GROUP_ID
39 );
40 fetch CSR_ASS_STATUS_TYPE_AMEND into X_ASSIGNMENT_STATUS_TYPE_ID;
41 close CSR_ASS_STATUS_TYPE_AMEND;
42 end KEY_TO_IDS;
43
44 procedure OWNER_TO_WHO (
45 X_OWNER in VARCHAR2,
46 X_CREATION_DATE out nocopy DATE,
47 X_CREATED_BY out nocopy NUMBER,
48 X_LAST_UPDATE_DATE out nocopy DATE,
49 X_LAST_UPDATED_BY out nocopy NUMBER,
50 X_LAST_UPDATE_LOGIN out nocopy NUMBER
51 ) is
52 begin
53 if X_OWNER = 'SEED' then
54 X_CREATED_BY := 1;
55 X_LAST_UPDATED_BY := 1;
56 else
57 X_CREATED_BY := 0;
58 X_LAST_UPDATED_BY := 0;
59 end if;
60 X_CREATION_DATE := sysdate;
61 X_LAST_UPDATE_DATE := sysdate;
62 X_LAST_UPDATE_LOGIN := 0;
63 end OWNER_TO_WHO;
64
65 procedure INSERT_ROW (
66 X_ASS_STATUS_TYPE_AMEND_ID in NUMBER,
67 X_USER_STATUS in VARCHAR2,
68 X_LAST_UPDATE_DATE in DATE,
69 X_LAST_UPDATED_BY in NUMBER,
70 X_LAST_UPDATE_LOGIN in NUMBER,
71 X_CREATION_DATE in DATE,
72 X_CREATED_BY in NUMBER
73 ) is
74 begin
75
76 --
77 -- Added the following code as a part of Zero Downtime Patching Project.
78 -- Code Starts Here.
79 --
80
81 BEGIN
82 PER_RIC_PKG.chk_integrity (
83 p_entity_name => 'PER_ASS_STATUS_TYPE_AMENDS_TL',
84 p_ref_entity => 'PER_ASS_STATUS_TYPE_AMENDS',
85 p_ref_column_name => 'ASS_STATUS_TYPE_AMEND_ID',
86 p_ref_col_value_number => X_ASS_STATUS_TYPE_AMEND_ID,
87 p_ref_col_value_varchar => NULL,
88 p_ref_col_value_date => NULL,
89 p_ref_type => 'INS');
90
91 END;
92 --
93 -- Code Ends Here.
94 --
95
96 insert into PER_ASS_STATUS_TYPE_AMENDS_TL (
97 ASS_STATUS_TYPE_AMEND_ID,
98 USER_STATUS,
99 LAST_UPDATE_DATE,
100 LAST_UPDATED_BY,
101 LAST_UPDATE_LOGIN,
102 CREATED_BY,
103 CREATION_DATE,
104 LANGUAGE,
105 SOURCE_LANG
106 ) select
107 X_ASS_STATUS_TYPE_AMEND_ID,
108 X_USER_STATUS,
109 X_LAST_UPDATE_DATE,
110 X_LAST_UPDATED_BY,
111 X_LAST_UPDATE_LOGIN,
112 X_CREATED_BY,
113 X_CREATION_DATE,
114 L.LANGUAGE_CODE,
115 USERENV('LANG')
116 from FND_LANGUAGES L
117 where L.INSTALLED_FLAG in ('I','B')
118 and not exists (
119 select null
120 from PER_ASS_STATUS_TYPE_AMENDS_TL
121 where ASS_STATUS_TYPE_AMEND_ID = X_ASS_STATUS_TYPE_AMEND_ID
122 and LANGUAGE = L.LANGUAGE_CODE );
123 end INSERT_ROW;
124
125 procedure UPDATE_ROW (
126 X_ASS_STATUS_TYPE_AMEND_ID in NUMBER,
127 X_USER_STATUS in VARCHAR2,
128 X_LAST_UPDATE_DATE in DATE,
129 X_LAST_UPDATED_BY in NUMBER,
130 X_LAST_UPDATE_LOGIN in NUMBER,
131 X_CREATION_DATE in DATE,
132 X_CREATED_BY in NUMBER
133 ) is
134 begin
135 update PER_ASS_STATUS_TYPE_AMENDS_TL
136 set USER_STATUS = X_USER_STATUS,
137 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
138 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
139 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
140 SOURCE_LANG = userenv('LANG')
141 where ASS_STATUS_TYPE_AMEND_ID = X_ASS_STATUS_TYPE_AMEND_ID
142 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
143
144 if (sql%notfound) then
145 raise no_data_found;
146 end if;
147 end UPDATE_ROW;
148
149 procedure DELETE_ROW (
150 X_ASS_STATUS_TYPE_AMEND_ID in NUMBER
151 ) is
152 begin
153 delete from PER_ASS_STATUS_TYPE_AMENDS_TL
154 where ASS_STATUS_TYPE_AMEND_ID = X_ASS_STATUS_TYPE_AMEND_ID;
155
156 if (sql%notfound) then
157 raise no_data_found;
158 end if;
159 end DELETE_ROW;
160
161 procedure ADD_LANGUAGE
162 is
163 begin
164 delete from PER_ASS_STATUS_TYPE_AMENDS_TL T
165 where not exists
166 (select NULL
167 from PER_ASS_STATUS_TYPE_AMENDS B
168 where B.ASS_STATUS_TYPE_AMEND_ID = T.ASS_STATUS_TYPE_AMEND_ID
169 );
170
171 update PER_ASS_STATUS_TYPE_AMENDS_TL T set (
172 USER_STATUS
173 ) = (select B.USER_STATUS
174 from PER_ASS_STATUS_TYPE_AMENDS_TL B
175 where B.ASS_STATUS_TYPE_AMEND_ID = T.ASS_STATUS_TYPE_AMEND_ID
176 and B.LANGUAGE = T.SOURCE_LANG)
177 where (
178 T.ASS_STATUS_TYPE_AMEND_ID,
179 T.LANGUAGE
180 ) in (select
181 SUBT.ASS_STATUS_TYPE_AMEND_ID,
182 SUBT.LANGUAGE
183 from PER_ASS_STATUS_TYPE_AMENDS_TL SUBB,
184 PER_ASS_STATUS_TYPE_AMENDS_TL SUBT
185 where SUBB.ASS_STATUS_TYPE_AMEND_ID =
186 SUBT.ASS_STATUS_TYPE_AMEND_ID
187 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
188 and (SUBB.USER_STATUS <> SUBT.USER_STATUS));
189
190 insert into PER_ASS_STATUS_TYPE_AMENDS_TL (
191 ASS_STATUS_TYPE_AMEND_ID,
192 USER_STATUS,
193 LAST_UPDATE_DATE,
194 LAST_UPDATED_BY,
195 LAST_UPDATE_LOGIN,
196 CREATED_BY,
197 CREATION_DATE,
198 LANGUAGE,
199 SOURCE_LANG
200 ) select
201 B.ASS_STATUS_TYPE_AMEND_ID,
202 B.USER_STATUS,
203 B.LAST_UPDATE_DATE,
204 B.LAST_UPDATED_BY,
205 B.LAST_UPDATE_LOGIN,
206 B.CREATED_BY,
207 B.CREATION_DATE,
208 L.LANGUAGE_CODE,
209 B.SOURCE_LANG
210 from PER_ASS_STATUS_TYPE_AMENDS_TL B,
211 FND_LANGUAGES L
212 where L.INSTALLED_FLAG in ('I', 'B')
213 and B.LANGUAGE = userenv('LANG')
214 and not exists
215 (select NULL
216 from PER_ASS_STATUS_TYPE_AMENDS_TL T
217 where T.ASS_STATUS_TYPE_AMEND_ID = B.ASS_STATUS_TYPE_AMEND_ID
218 and T.LANGUAGE = L.LANGUAGE_CODE);
219 end ADD_LANGUAGE;
220
221 procedure LOAD_ROW(
222 X_STATUS in VARCHAR2,
223 X_BUSINESS_GROUP_NAME in VARCHAR2,
224 X_OWNER in VARCHAR2,
225 X_USER_STATUS in VARCHAR2,
226 X_LAST_UPDATE_DATE IN VARCHAR2 default sysdate,
227 X_CUSTOM_MODE IN VARCHAR2 default null
228 ) IS
229 X_ASS_STATUS_TYPE_AMEND_ID NUMBER;
230 X_BUSINESS_GROUP_ID NUMBER;
231 X_CREATION_DATE DATE :=sysdate;
232 X_CREATED_BY NUMBER;
233 -- X_LAST_UPDATE_DATE DATE;
234 X_LAST_UPDATED_BY NUMBER;
235 X_LAST_UPDATE_LOGIN NUMBER;
236 f_luby number; -- entity owner in file
237 f_ludate date; -- entity update date in file
238 db_luby number; -- entity owner in db
239 db_ludate date; -- entity update date in db
240
241 begin
242 if X_OWNER = 'SEED' then
243 X_CREATED_BY := 1;
244 else
245 X_CREATED_BY := 0;
246 end if;
247
248 KEY_TO_IDS (
249 X_STATUS,
250 X_BUSINESS_GROUP_NAME,
251 X_ASS_STATUS_TYPE_AMEND_ID,
252 X_BUSINESS_GROUP_ID
253 );
254 -- Commenting this as X_LAST_UPDATE_DATE is now an Input parameter.
255 /* OWNER_TO_WHO (
256 X_OWNER,
257 X_CREATION_DATE,
258 X_CREATED_BY,
259 X_LAST_UPDATE_DATE,
260 X_LAST_UPDATED_BY,
261 X_LAST_UPDATE_LOGIN
262 ); */
263
264 begin
265 f_luby := fnd_load_util.owner_id(X_OWNER);
266 -- Translate char last_update_date to date
267 f_ludate := nvl(to_date(X_LAST_UPDATE_DATE, 'YYYY/MM/DD'), sysdate);
268 select LAST_UPDATED_BY, LAST_UPDATE_DATE
269 into db_luby, db_ludate
270 from PER_ASS_STATUS_TYPE_AMENDS
271 where ASS_STATUS_TYPE_AMEND_ID = TO_NUMBER(X_ASS_STATUS_TYPE_AMEND_ID);
272
273 -- Test for customization and version
274 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
275 db_ludate, X_CUSTOM_MODE)) then
276
277 UPDATE_ROW (
278 X_ASS_STATUS_TYPE_AMEND_ID,
279 X_USER_STATUS,
280 f_ludate,
281 f_luby,
282 0,
283 X_CREATION_DATE,
284 X_CREATED_BY
285 );
286 end if;
287 exception
288 when no_data_found then
289
290 INSERT_ROW (
291 X_ASS_STATUS_TYPE_AMEND_ID,
292 X_USER_STATUS,
293 f_ludate,
294 f_luby,
295 0,
296 X_CREATION_DATE,
297 X_CREATED_BY
298 );
299 end;
300 end LOAD_ROW;
301
302 procedure TRANSLATE_ROW(
303 X_STATUS in VARCHAR2,
304 X_BUSINESS_GROUP_NAME in VARCHAR2,
305 X_OWNER in VARCHAR2,
306 X_USER_STATUS in VARCHAR2,
307 X_LAST_UPDATE_DATE IN VARCHAR2 default sysdate,
308 X_CUSTOM_MODE IN VARCHAR2 default null
309 ) IS
310 X_ASS_STATUS_TYPE_AMEND_ID NUMBER;
311 X_BUSINESS_GROUP_ID NUMBER;
312 X_CREATION_DATE DATE;
313 X_CREATED_BY NUMBER;
314 -- X_LAST_UPDATE_DATE DATE;
315 X_LAST_UPDATED_BY NUMBER;
316 X_LAST_UPDATE_LOGIN NUMBER;
317 f_luby number; -- entity owner in file
318 f_ludate date; -- entity update date in file
319 db_luby number; -- entity owner in db
320 db_ludate date; -- entity update date in db
321
322 begin
323 KEY_TO_IDS (
324 X_STATUS,
325 X_BUSINESS_GROUP_NAME,
326 X_ASS_STATUS_TYPE_AMEND_ID,
327 X_BUSINESS_GROUP_ID
328 );
329 -- Commenting this as X_LAST_UPDATE_DATE is now an Input parameter.
330 /* OWNER_TO_WHO (
331 X_OWNER,
332 X_CREATION_DATE,
333 X_CREATED_BY,
334 X_LAST_UPDATE_DATE,
335 X_LAST_UPDATED_BY,
336 X_LAST_UPDATE_LOGIN
337 ); */
338 -- Translate owner to file_last_updated_by
339 f_luby := fnd_load_util.owner_id(x_owner);
340
341 -- Translate char last_update_date to date
342 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
343
344 select LAST_UPDATED_BY, LAST_UPDATE_DATE
345 into db_luby, db_ludate
346 from PER_ASS_STATUS_TYPE_AMENDS_TL
347 where ASS_STATUS_TYPE_AMEND_ID = TO_NUMBER(X_ASS_STATUS_TYPE_AMEND_ID)
348 and LANGUAGE=userenv('LANG');
349
350 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
351 db_ludate,X_CUSTOM_MODE)) then
352 update PER_ASS_STATUS_TYPE_AMENDS_TL set
353 USER_STATUS = X_USER_STATUS,
354 LAST_UPDATE_DATE = db_ludate,
355 LAST_UPDATED_BY = db_luby,
356 LAST_UPDATE_LOGIN = 0,
357 SOURCE_LANG = userenv('LANG')
358 where userenv('LANG') in (LANGUAGE,SOURCE_LANG)
359 and ASS_STATUS_TYPE_AMEND_ID = TO_NUMBER(x_ASS_STATUS_TYPE_AMEND_ID);
360
361 end if;
362 end TRANSLATE_ROW;
363
364 end PER_ASS_STATUS_TYPE_AMENDS_PKG;