[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.2 2006/06/27 10:41:15 bshukla noship $ */
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 insert into PER_ASS_STATUS_TYPE_AMENDS_TL (
76 ASS_STATUS_TYPE_AMEND_ID,
77 USER_STATUS,
78 LAST_UPDATE_DATE,
79 LAST_UPDATED_BY,
80 LAST_UPDATE_LOGIN,
81 CREATED_BY,
82 CREATION_DATE,
83 LANGUAGE,
84 SOURCE_LANG
85 ) select
86 X_ASS_STATUS_TYPE_AMEND_ID,
87 X_USER_STATUS,
88 X_LAST_UPDATE_DATE,
89 X_LAST_UPDATED_BY,
90 X_LAST_UPDATE_LOGIN,
91 X_CREATED_BY,
92 X_CREATION_DATE,
93 L.LANGUAGE_CODE,
94 USERENV('LANG')
95 from FND_LANGUAGES L
96 where L.INSTALLED_FLAG in ('I','B')
97 and not exists (
98 select null
99 from PER_ASS_STATUS_TYPE_AMENDS_TL
100 where ASS_STATUS_TYPE_AMEND_ID = X_ASS_STATUS_TYPE_AMEND_ID
101 and LANGUAGE = L.LANGUAGE_CODE );
102 end INSERT_ROW;
103
104 procedure UPDATE_ROW (
105 X_ASS_STATUS_TYPE_AMEND_ID in NUMBER,
106 X_USER_STATUS in VARCHAR2,
107 X_LAST_UPDATE_DATE in DATE,
108 X_LAST_UPDATED_BY in NUMBER,
109 X_LAST_UPDATE_LOGIN in NUMBER,
110 X_CREATION_DATE in DATE,
111 X_CREATED_BY in NUMBER
112 ) is
113 begin
114 update PER_ASS_STATUS_TYPE_AMENDS_TL
115 set USER_STATUS = X_USER_STATUS,
116 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
117 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
118 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
119 SOURCE_LANG = userenv('LANG')
120 where ASS_STATUS_TYPE_AMEND_ID = X_ASS_STATUS_TYPE_AMEND_ID
121 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
122
123 if (sql%notfound) then
124 raise no_data_found;
125 end if;
126 end UPDATE_ROW;
127
128 procedure DELETE_ROW (
129 X_ASS_STATUS_TYPE_AMEND_ID in NUMBER
130 ) is
131 begin
132 delete from PER_ASS_STATUS_TYPE_AMENDS_TL
133 where ASS_STATUS_TYPE_AMEND_ID = X_ASS_STATUS_TYPE_AMEND_ID;
134
135 if (sql%notfound) then
136 raise no_data_found;
137 end if;
138 end DELETE_ROW;
139
140 procedure ADD_LANGUAGE
141 is
142 begin
143 delete from PER_ASS_STATUS_TYPE_AMENDS_TL T
144 where not exists
145 (select NULL
146 from PER_ASS_STATUS_TYPE_AMENDS B
147 where B.ASS_STATUS_TYPE_AMEND_ID = T.ASS_STATUS_TYPE_AMEND_ID
148 );
149
150 update PER_ASS_STATUS_TYPE_AMENDS_TL T set (
151 USER_STATUS
152 ) = (select B.USER_STATUS
153 from PER_ASS_STATUS_TYPE_AMENDS_TL B
154 where B.ASS_STATUS_TYPE_AMEND_ID = T.ASS_STATUS_TYPE_AMEND_ID
155 and B.LANGUAGE = T.SOURCE_LANG)
156 where (
157 T.ASS_STATUS_TYPE_AMEND_ID,
158 T.LANGUAGE
159 ) in (select
160 SUBT.ASS_STATUS_TYPE_AMEND_ID,
161 SUBT.LANGUAGE
162 from PER_ASS_STATUS_TYPE_AMENDS_TL SUBB,
163 PER_ASS_STATUS_TYPE_AMENDS_TL SUBT
164 where SUBB.ASS_STATUS_TYPE_AMEND_ID =
165 SUBT.ASS_STATUS_TYPE_AMEND_ID
166 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
167 and (SUBB.USER_STATUS <> SUBT.USER_STATUS));
168
169 insert into PER_ASS_STATUS_TYPE_AMENDS_TL (
170 ASS_STATUS_TYPE_AMEND_ID,
171 USER_STATUS,
172 LAST_UPDATE_DATE,
173 LAST_UPDATED_BY,
174 LAST_UPDATE_LOGIN,
175 CREATED_BY,
176 CREATION_DATE,
177 LANGUAGE,
178 SOURCE_LANG
179 ) select
180 B.ASS_STATUS_TYPE_AMEND_ID,
181 B.USER_STATUS,
182 B.LAST_UPDATE_DATE,
183 B.LAST_UPDATED_BY,
184 B.LAST_UPDATE_LOGIN,
185 B.CREATED_BY,
186 B.CREATION_DATE,
187 L.LANGUAGE_CODE,
188 B.SOURCE_LANG
189 from PER_ASS_STATUS_TYPE_AMENDS_TL B,
190 FND_LANGUAGES L
191 where L.INSTALLED_FLAG in ('I', 'B')
192 and B.LANGUAGE = userenv('LANG')
193 and not exists
194 (select NULL
195 from PER_ASS_STATUS_TYPE_AMENDS_TL T
196 where T.ASS_STATUS_TYPE_AMEND_ID = B.ASS_STATUS_TYPE_AMEND_ID
197 and T.LANGUAGE = L.LANGUAGE_CODE);
198 end ADD_LANGUAGE;
199
200 procedure LOAD_ROW(
201 X_STATUS in VARCHAR2,
202 X_BUSINESS_GROUP_NAME in VARCHAR2,
203 X_OWNER in VARCHAR2,
204 X_USER_STATUS in VARCHAR2,
205 X_LAST_UPDATE_DATE IN VARCHAR2 default sysdate,
206 X_CUSTOM_MODE IN VARCHAR2 default null
207 ) IS
208 X_ASS_STATUS_TYPE_AMEND_ID NUMBER;
209 X_BUSINESS_GROUP_ID NUMBER;
210 X_CREATION_DATE DATE :=sysdate;
211 X_CREATED_BY NUMBER;
212 -- X_LAST_UPDATE_DATE DATE;
213 X_LAST_UPDATED_BY NUMBER;
214 X_LAST_UPDATE_LOGIN NUMBER;
215 f_luby number; -- entity owner in file
216 f_ludate date; -- entity update date in file
217 db_luby number; -- entity owner in db
218 db_ludate date; -- entity update date in db
219
220 begin
221 if X_OWNER = 'SEED' then
222 X_CREATED_BY := 1;
223 else
224 X_CREATED_BY := 0;
225 end if;
226
227 KEY_TO_IDS (
228 X_STATUS,
229 X_BUSINESS_GROUP_NAME,
230 X_ASS_STATUS_TYPE_AMEND_ID,
231 X_BUSINESS_GROUP_ID
232 );
233 -- Commenting this as X_LAST_UPDATE_DATE is now an Input parameter.
234 /* OWNER_TO_WHO (
235 X_OWNER,
236 X_CREATION_DATE,
237 X_CREATED_BY,
238 X_LAST_UPDATE_DATE,
239 X_LAST_UPDATED_BY,
240 X_LAST_UPDATE_LOGIN
241 ); */
242
243 begin
244 f_luby := fnd_load_util.owner_id(X_OWNER);
245 -- Translate char last_update_date to date
246 f_ludate := nvl(to_date(X_LAST_UPDATE_DATE, 'YYYY/MM/DD'), sysdate);
247 select LAST_UPDATED_BY, LAST_UPDATE_DATE
248 into db_luby, db_ludate
249 from PER_ASS_STATUS_TYPE_AMENDS
250 where ASS_STATUS_TYPE_AMEND_ID = TO_NUMBER(X_ASS_STATUS_TYPE_AMEND_ID);
251
252 -- Test for customization and version
253 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
254 db_ludate, X_CUSTOM_MODE)) then
255
256 UPDATE_ROW (
257 X_ASS_STATUS_TYPE_AMEND_ID,
258 X_USER_STATUS,
259 f_ludate,
260 f_luby,
261 0,
262 X_CREATION_DATE,
263 X_CREATED_BY
264 );
265 end if;
266 exception
267 when no_data_found then
268
269 INSERT_ROW (
270 X_ASS_STATUS_TYPE_AMEND_ID,
271 X_USER_STATUS,
272 f_ludate,
273 f_luby,
274 0,
275 X_CREATION_DATE,
276 X_CREATED_BY
277 );
278 end;
279 end LOAD_ROW;
280
281 procedure TRANSLATE_ROW(
282 X_STATUS in VARCHAR2,
283 X_BUSINESS_GROUP_NAME in VARCHAR2,
284 X_OWNER in VARCHAR2,
285 X_USER_STATUS in VARCHAR2,
286 X_LAST_UPDATE_DATE IN VARCHAR2 default sysdate,
287 X_CUSTOM_MODE IN VARCHAR2 default null
288 ) IS
289 X_ASS_STATUS_TYPE_AMEND_ID NUMBER;
290 X_BUSINESS_GROUP_ID NUMBER;
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 f_luby number; -- entity owner in file
297 f_ludate date; -- entity update date in file
298 db_luby number; -- entity owner in db
299 db_ludate date; -- entity update date in db
300
301 begin
302 KEY_TO_IDS (
303 X_STATUS,
304 X_BUSINESS_GROUP_NAME,
305 X_ASS_STATUS_TYPE_AMEND_ID,
306 X_BUSINESS_GROUP_ID
307 );
308 -- Commenting this as X_LAST_UPDATE_DATE is now an Input parameter.
309 /* OWNER_TO_WHO (
310 X_OWNER,
311 X_CREATION_DATE,
312 X_CREATED_BY,
313 X_LAST_UPDATE_DATE,
314 X_LAST_UPDATED_BY,
315 X_LAST_UPDATE_LOGIN
316 ); */
317 -- Translate owner to file_last_updated_by
318 f_luby := fnd_load_util.owner_id(x_owner);
319
320 -- Translate char last_update_date to date
321 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
322
323 select LAST_UPDATED_BY, LAST_UPDATE_DATE
324 into db_luby, db_ludate
325 from PER_ASS_STATUS_TYPE_AMENDS_TL
326 where ASS_STATUS_TYPE_AMEND_ID = TO_NUMBER(X_ASS_STATUS_TYPE_AMEND_ID)
327 and LANGUAGE=userenv('LANG');
328
329 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
330 db_ludate,X_CUSTOM_MODE)) then
331 update PER_ASS_STATUS_TYPE_AMENDS_TL set
332 USER_STATUS = X_USER_STATUS,
333 LAST_UPDATE_DATE = db_ludate,
334 LAST_UPDATED_BY = db_luby,
335 LAST_UPDATE_LOGIN = 0,
336 SOURCE_LANG = userenv('LANG')
337 where userenv('LANG') in (LANGUAGE,SOURCE_LANG)
338 and ASS_STATUS_TYPE_AMEND_ID = TO_NUMBER(x_ASS_STATUS_TYPE_AMEND_ID);
339
340 end if;
341 end TRANSLATE_ROW;
342
343 end PER_ASS_STATUS_TYPE_AMENDS_PKG;