[Home] [Help]
PACKAGE BODY: APPS.PA_AMOUNT_TYPES_PKG
Source
1 package body PA_AMOUNT_TYPES_PKG as
2 /* $Header: PARRATLB.pls 120.1.12020000.2 2012/07/19 09:58:29 admarath ship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
5 X_AMOUNT_TYPE_ID in NUMBER,
6 X_PLAN_ADJ_AMOUNT_FLAG in VARCHAR2,
7 X_PLAN_ADJUSTABLE_FLAG in VARCHAR2,
8 X_AMOUNT_TYPE_CODE in VARCHAR2,
9 X_AMOUNT_TYPE_CLASS in VARCHAR2,
10 X_AMOUNT_TYPE_NAME in VARCHAR2,
11 X_CREATION_DATE in DATE,
12 X_CREATED_BY in NUMBER,
13 X_LAST_UPDATE_DATE in DATE,
14 X_LAST_UPDATED_BY in NUMBER,
15 X_LAST_UPDATE_LOGIN in NUMBER
16 ) is
17 cursor C is select ROWID from PA_AMOUNT_TYPES_B
18 where AMOUNT_TYPE_ID = X_AMOUNT_TYPE_ID
19 ;
20
21 nc_ROWID VARCHAR2(30);
22
23 begin
24 nc_ROWID := X_ROWID;
25
26 insert into PA_AMOUNT_TYPES_B (
27 PLAN_ADJ_AMOUNT_FLAG,
28 PLAN_ADJUSTABLE_FLAG,
29 AMOUNT_TYPE_ID,
30 AMOUNT_TYPE_CODE,
31 AMOUNT_TYPE_CLASS,
32 CREATION_DATE,
33 CREATED_BY,
34 LAST_UPDATE_DATE,
35 LAST_UPDATED_BY,
36 LAST_UPDATE_LOGIN
37 ) values (
38 X_PLAN_ADJ_AMOUNT_FLAG,
39 X_PLAN_ADJUSTABLE_FLAG,
40 X_AMOUNT_TYPE_ID,
41 X_AMOUNT_TYPE_CODE,
42 X_AMOUNT_TYPE_CLASS,
43 X_CREATION_DATE,
44 X_CREATED_BY,
45 X_LAST_UPDATE_DATE,
46 X_LAST_UPDATED_BY,
47 X_LAST_UPDATE_LOGIN
48 );
49
50 insert into PA_AMOUNT_TYPES_TL (
51 AMOUNT_TYPE_ID,
52 AMOUNT_TYPE_NAME,
53 LAST_UPDATE_DATE,
54 LAST_UPDATED_BY,
55 CREATION_DATE,
56 CREATED_BY,
57 LAST_UPDATE_LOGIN,
58 LANGUAGE,
59 SOURCE_LANG
60 ) select
61 X_AMOUNT_TYPE_ID,
62 X_AMOUNT_TYPE_NAME,
63 X_LAST_UPDATE_DATE,
64 X_LAST_UPDATED_BY,
65 X_CREATION_DATE,
66 X_CREATED_BY,
67 X_LAST_UPDATE_LOGIN,
68 L.LANGUAGE_CODE,
69 userenv('LANG')
70 from FND_LANGUAGES L
71 where L.INSTALLED_FLAG in ('I', 'B')
72 and not exists
73 (select NULL
74 from PA_AMOUNT_TYPES_TL T
75 where T.AMOUNT_TYPE_ID = X_AMOUNT_TYPE_ID
76 and T.LANGUAGE = L.LANGUAGE_CODE);
77
78 open c;
79 fetch c into X_ROWID;
80 if (c%notfound) then
81 close c;
82 raise no_data_found;
83 end if;
84 close c;
85
86 EXCEPTION
87 WHEN OTHERS THEN
88 X_ROWID := nc_ROWID;
89 RAISE;
90
91 end INSERT_ROW;
92
93 procedure LOCK_ROW (
94 X_AMOUNT_TYPE_ID in NUMBER,
95 X_PLAN_ADJ_AMOUNT_FLAG in VARCHAR2,
96 X_PLAN_ADJUSTABLE_FLAG in VARCHAR2,
97 X_AMOUNT_TYPE_CODE in VARCHAR2,
98 X_AMOUNT_TYPE_CLASS in VARCHAR2,
99 X_AMOUNT_TYPE_NAME in VARCHAR2
100 ) is
101 cursor c is select
102 PLAN_ADJ_AMOUNT_FLAG,
103 PLAN_ADJUSTABLE_FLAG,
104 AMOUNT_TYPE_CODE,
105 AMOUNT_TYPE_CLASS
106 from PA_AMOUNT_TYPES_B
107 where AMOUNT_TYPE_ID = X_AMOUNT_TYPE_ID
108 for update of AMOUNT_TYPE_ID nowait;
109 recinfo c%rowtype;
110
111 cursor c1 is select
112 AMOUNT_TYPE_NAME,
113 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
114 from PA_AMOUNT_TYPES_TL
115 where AMOUNT_TYPE_ID = X_AMOUNT_TYPE_ID
116 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
117 for update of AMOUNT_TYPE_ID nowait;
118 begin
119 open c;
120 fetch c into recinfo;
121 if (c%notfound) then
122 close c;
123 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
124 app_exception.raise_exception;
125 end if;
126 close c;
127 if ( ((recinfo.PLAN_ADJ_AMOUNT_FLAG = X_PLAN_ADJ_AMOUNT_FLAG)
128 OR ((recinfo.PLAN_ADJ_AMOUNT_FLAG is null) AND (X_PLAN_ADJ_AMOUNT_FLAG is null)))
129 AND ((recinfo.PLAN_ADJUSTABLE_FLAG = X_PLAN_ADJUSTABLE_FLAG)
130 OR ((recinfo.PLAN_ADJUSTABLE_FLAG is null) AND (X_PLAN_ADJUSTABLE_FLAG is null)))
131 AND (recinfo.AMOUNT_TYPE_CODE = X_AMOUNT_TYPE_CODE)
132 AND (recinfo.AMOUNT_TYPE_CLASS = X_AMOUNT_TYPE_CLASS)
133 ) then
134 null;
135 else
136 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
137 app_exception.raise_exception;
138 end if;
139
140 for tlinfo in c1 loop
141 if (tlinfo.BASELANG = 'Y') then
142 if ( (tlinfo.AMOUNT_TYPE_NAME = X_AMOUNT_TYPE_NAME)
143 ) then
144 null;
145 else
146 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
147 app_exception.raise_exception;
148 end if;
149 end if;
150 end loop;
151 return;
152 end LOCK_ROW;
153
154 procedure UPDATE_ROW (
155 X_AMOUNT_TYPE_ID in NUMBER,
156 X_PLAN_ADJ_AMOUNT_FLAG in VARCHAR2,
157 X_PLAN_ADJUSTABLE_FLAG in VARCHAR2,
158 X_AMOUNT_TYPE_CODE in VARCHAR2,
159 X_AMOUNT_TYPE_CLASS in VARCHAR2,
160 X_AMOUNT_TYPE_NAME in VARCHAR2,
161 X_LAST_UPDATE_DATE in DATE,
162 X_LAST_UPDATED_BY in NUMBER,
163 X_LAST_UPDATE_LOGIN in NUMBER
164 ) is
165 begin
166 update PA_AMOUNT_TYPES_B set
167 PLAN_ADJ_AMOUNT_FLAG = X_PLAN_ADJ_AMOUNT_FLAG,
168 PLAN_ADJUSTABLE_FLAG = X_PLAN_ADJUSTABLE_FLAG,
169 AMOUNT_TYPE_CODE = X_AMOUNT_TYPE_CODE,
170 AMOUNT_TYPE_CLASS = X_AMOUNT_TYPE_CLASS,
171 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
172 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
173 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
174 where AMOUNT_TYPE_ID = X_AMOUNT_TYPE_ID;
175
176 if (sql%notfound) then
177 raise no_data_found;
178 end if;
179
180 update PA_AMOUNT_TYPES_TL set
181 AMOUNT_TYPE_NAME = X_AMOUNT_TYPE_NAME,
182 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
183 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
184 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
185 SOURCE_LANG = userenv('LANG')
186 where AMOUNT_TYPE_ID = X_AMOUNT_TYPE_ID
187 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
188
189 if (sql%notfound) then
190 raise no_data_found;
191 end if;
192 end UPDATE_ROW;
193
194 procedure DELETE_ROW (
195 X_AMOUNT_TYPE_ID in NUMBER
196 ) is
197 begin
198 delete from PA_AMOUNT_TYPES_TL
199 where AMOUNT_TYPE_ID = X_AMOUNT_TYPE_ID;
200
201 if (sql%notfound) then
202 raise no_data_found;
203 end if;
204
205 delete from PA_AMOUNT_TYPES_B
206 where AMOUNT_TYPE_ID = X_AMOUNT_TYPE_ID;
207
208 if (sql%notfound) then
209 raise no_data_found;
210 end if;
211 end DELETE_ROW;
212
213 procedure ADD_LANGUAGE
214 is
215 begin
216 delete from PA_AMOUNT_TYPES_TL T
217 where not exists
218 (select NULL
219 from PA_AMOUNT_TYPES_B B
220 where B.AMOUNT_TYPE_ID = T.AMOUNT_TYPE_ID
221 );
222
223 update PA_AMOUNT_TYPES_TL T set (
224 AMOUNT_TYPE_NAME
225 ) = (select
226 B.AMOUNT_TYPE_NAME
227 from PA_AMOUNT_TYPES_TL B
228 where B.AMOUNT_TYPE_ID = T.AMOUNT_TYPE_ID
229 and B.LANGUAGE = T.SOURCE_LANG)
230 where (
231 T.AMOUNT_TYPE_ID,
232 T.LANGUAGE
233 ) in (select
234 SUBT.AMOUNT_TYPE_ID,
235 SUBT.LANGUAGE
236 from PA_AMOUNT_TYPES_TL SUBB, PA_AMOUNT_TYPES_TL SUBT
237 where SUBB.AMOUNT_TYPE_ID = SUBT.AMOUNT_TYPE_ID
238 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
239 and (SUBB.AMOUNT_TYPE_NAME <> SUBT.AMOUNT_TYPE_NAME
240 ));
241
242 insert into PA_AMOUNT_TYPES_TL (
243 AMOUNT_TYPE_ID,
244 AMOUNT_TYPE_NAME,
245 LAST_UPDATE_DATE,
246 LAST_UPDATED_BY,
247 CREATION_DATE,
248 CREATED_BY,
249 LAST_UPDATE_LOGIN,
250 LANGUAGE,
251 SOURCE_LANG
252 ) select
253 B.AMOUNT_TYPE_ID,
254 B.AMOUNT_TYPE_NAME,
255 B.LAST_UPDATE_DATE,
256 B.LAST_UPDATED_BY,
257 B.CREATION_DATE,
258 B.CREATED_BY,
259 B.LAST_UPDATE_LOGIN,
260 L.LANGUAGE_CODE,
261 B.SOURCE_LANG
262 from PA_AMOUNT_TYPES_TL B, FND_LANGUAGES L
263 where L.INSTALLED_FLAG in ('I', 'B')
264 and B.LANGUAGE = userenv('LANG')
265 and not exists
266 (select NULL
267 from PA_AMOUNT_TYPES_TL T
268 where T.AMOUNT_TYPE_ID = B.AMOUNT_TYPE_ID
269 and T.LANGUAGE = L.LANGUAGE_CODE);
270 end ADD_LANGUAGE;
271
272 procedure TRANSLATE_ROW(
273 X_AMOUNT_TYPE_NAME in VARCHAR2,
274 X_AMOUNT_TYPE_ID in NUMBER,
275 X_OWNER in VARCHAR2
276 ) is
277 begin
278
279 update PA_AMOUNT_TYPES_TL set
280 AMOUNT_TYPE_NAME = X_AMOUNT_TYPE_NAME,
281 LAST_UPDATE_DATE = sysdate,
282 LAST_UPDATED_BY = decode(X_OWNER, 'SEED', 1, 0),
283 LAST_UPDATE_LOGIN = 0,
284 SOURCE_LANG = USERENV('LANG')
285 where AMOUNT_TYPE_ID = X_AMOUNT_TYPE_ID
286 and USERENV('LANG') IN ( LANGUAGE , SOURCE_LANG );
287
288 if (sql%notfound) then
289 raise no_data_found;
290 end if;
291 end TRANSLATE_ROW;
292
293 procedure LOAD_ROW(
294 X_AMOUNT_TYPE_ID in NUMBER,
295 X_PLAN_ADJ_AMOUNT_FLAG in VARCHAR2,
296 X_PLAN_ADJUSTABLE_FLAG in VARCHAR2,
297 X_AMOUNT_TYPE_CODE in VARCHAR2,
298 X_AMOUNT_TYPE_CLASS in VARCHAR2,
299 X_AMOUNT_TYPE_NAME in VARCHAR2,
300 X_OWNER in VARCHAR2
301 ) is
302
303 user_id NUMBER;
304 X_ROWID VARCHAR2(64);
305
306 begin
307
308 if (X_OWNER = 'SEED')then
309 user_id := 1;
310 else
311 user_id := 0;
312 end if;
313
314 pa_amount_types_pkg.UPDATE_ROW(
315 X_AMOUNT_TYPE_ID => X_AMOUNT_TYPE_ID,
316 X_PLAN_ADJ_AMOUNT_FLAG => X_PLAN_ADJ_AMOUNT_FLAG,
317 X_PLAN_ADJUSTABLE_FLAG => X_PLAN_ADJUSTABLE_FLAG,
318 X_AMOUNT_TYPE_CODE => X_AMOUNT_TYPE_CODE,
319 X_AMOUNT_TYPE_CLASS => X_AMOUNT_TYPE_CLASS,
320 X_AMOUNT_TYPE_NAME => X_AMOUNT_TYPE_NAME,
321 X_LAST_UPDATE_DATE => sysdate,
322 X_LAST_UPDATED_BY => user_id,
323 X_LAST_UPDATE_LOGIN => 0
324 );
325 exception
326 when NO_DATA_FOUND then
327 pa_amount_types_pkg.INSERT_ROW(
328 X_ROWID => X_ROWID,
329 X_AMOUNT_TYPE_ID => X_AMOUNT_TYPE_ID,
330 X_PLAN_ADJ_AMOUNT_FLAG => X_PLAN_ADJ_AMOUNT_FLAG,
331 X_PLAN_ADJUSTABLE_FLAG => X_PLAN_ADJUSTABLE_FLAG,
332 X_AMOUNT_TYPE_CODE => X_AMOUNT_TYPE_CODE,
333 X_AMOUNT_TYPE_CLASS => X_AMOUNT_TYPE_CLASS,
334 X_AMOUNT_TYPE_NAME => X_AMOUNT_TYPE_NAME,
335 X_CREATION_DATE => sysdate,
336 X_CREATED_BY => user_id,
337 X_LAST_UPDATE_DATE => sysdate,
338 X_LAST_UPDATED_BY => user_id,
339 X_LAST_UPDATE_LOGIN => 0
340 );
341 end LOAD_ROW;
342
343 end PA_AMOUNT_TYPES_PKG;