[Home] [Help]
PACKAGE BODY: APPS.PA_PERIOD_MASKS_PKG
Source
1 PACKAGE BODY PA_PERIOD_MASKS_PKG as
2 --$Header: PAFPPMTB.pls 120.2 2005/06/07 02:08:31 appldev $
3 PROCEDURE INSERT_ROW(
4 X_ROWID IN OUT NOCOPY rowid,
5 X_PERIOD_MASK_ID IN pa_period_masks_b.period_mask_id%type,
6 X_EFFECTIVE_START_DATE IN pa_period_masks_b.effective_start_date%type,
7 X_EFFECTIVE_END_DATE IN pa_period_masks_b.effective_end_date%type,
8 X_TIME_PHASE_CODE IN pa_period_masks_b.time_phase_code%type,
9 X_CREATION_DATE IN pa_period_masks_b.creation_date%type,
10 X_CREATED_BY IN pa_period_masks_b.created_by%type,
11 X_LAST_UPDATE_LOGIN IN pa_period_masks_b.last_update_login%type,
12 X_LAST_UPDATED_BY IN pa_period_masks_b.last_updated_by%type,
13 X_LAST_UPDATE_DATE IN pa_period_masks_b.last_update_date%type,
14 X_RECORD_VERSION_NUMBER IN pa_period_masks_b.record_version_number%type,
15 X_PRE_DEFINED_FLAG IN pa_period_masks_b.pre_defined_flag%type,
16 X_NAME IN pa_period_masks_tl.name%type,
17 X_DESCRIPTION IN pa_period_masks_tl.description%type)
18 IS
19
20 l_period_mask_id pa_period_masks_b.period_mask_id%type;
21
22
23 CURSOR C IS SELECT ROWID FROM PA_PERIOD_MASKS_B
24 WHERE period_mask_id = l_period_mask_id;
25
26 BEGIN
27
28 SELECT NVL(x_period_mask_id,pa_period_masks_s.nextval)
29 INTO l_period_mask_id
30 FROM DUAL;
31
32 INSERT INTO PA_PERIOD_MASKS_B(
33 period_mask_id,
34 effective_start_date,
35 effective_end_date,
36 time_phase_code,
37 creation_date,
38 created_by,
39 last_update_login,
40 last_updated_by,
41 last_update_date,
42 record_version_number,
43 pre_defined_flag
44 ) VALUES (
45 l_period_mask_id,
46 X_EFFECTIVE_START_DATE,
47 X_EFFECTIVE_END_DATE,
48 X_TIME_PHASE_CODE,
49 X_CREATION_DATE,
50 X_CREATED_BY,
51 X_LAST_UPDATE_LOGIN,
52 X_LAST_UPDATED_BY,
53 X_LAST_UPDATE_DATE,
54 X_RECORD_VERSION_NUMBER,
55 X_PRE_DEFINED_FLAG
56 );
57
58 INSERT INTO PA_PERIOD_MASKS_TL(
59 LAST_UPDATE_LOGIN,
60 CREATION_DATE,
61 CREATED_BY,
62 LAST_UPDATE_DATE,
63 LAST_UPDATED_BY,
64 PERIOD_MASK_ID,
65 NAME,
66 DESCRIPTION,
67 LANGUAGE,
68 SOURCE_LANG
69 ) SELECT
70 X_LAST_UPDATE_LOGIN,
71 X_CREATION_DATE,
72 X_CREATED_BY,
73 X_LAST_UPDATE_DATE,
74 X_LAST_UPDATED_BY,
75 L_PERIOD_MASK_ID,
76 X_NAME,
77 X_DESCRIPTION,
78 L.LANGUAGE_CODE,
79 USERENV('LANG')
80 FROM FND_LANGUAGES L
81 WHERE L.INSTALLED_FLAG in ('I', 'B')
82 AND NOT EXISTS
83 (SELECT NULL
84 FROM PA_PERIOD_MASKS_TL ppmt
85 WHERE ppmt.period_mask_id = l_period_mask_id
86 AND ppmt.language = l.language_code);
87
88 OPEN c;
89 FETCH c INTO X_ROWID;
90 IF (c%NOTFOUND) THEN
91 CLOSE c;
92 RAISE NO_DATA_FOUND;
93 END IF;
94 CLOSE c;
95
96 END INSERT_ROW;
97
98 PROCEDURE LOCK_ROW(
99 X_PERIOD_MASK_ID IN pa_period_masks_b.period_mask_id%type
100 ) IS
101 CURSOR c IS SELECT
102 period_mask_id
103 FROM pa_period_masks_b
104 WHERE period_mask_id = x_period_mask_id
105 FOR UPDATE of period_mask_id NOWAIT;
106
107 recinfo c%ROWTYPE;
108
109 cursor c1 is SELECT
110 NAME,
111 DESCRIPTION,
112 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
113 FROM PA_period_masks_tl
114 WHERE period_mask_id = X_PERIOD_MASK_ID
115 AND USERENV('LANG') IN (LANGUAGE, SOURCE_LANG)
116 FOR UPDATE OF period_mask_id NOWAIT;
117
118 recinfo1 c1%ROWTYPE;
119
120 BEGIN
121
122
123 OPEN c;
124 FETCH c INTO recinfo;
125 IF (c%NOTFOUND) THEN
126 CLOSE c;
127 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
128 app_exception.raise_exception;
129 END IF;
130 CLOSE c;
131
132 return;
133 END LOCK_ROW;
134
135
136
137 PROCEDURE UPDATE_ROW(
138 X_PERIOD_MASK_ID IN pa_period_masks_b.period_mask_id%type,
139 X_EFFECTIVE_START_DATE IN pa_period_masks_b.effective_start_date%type,
140 X_EFFECTIVE_END_DATE IN pa_period_masks_b.effective_end_date%type,
141 X_TIME_PHASE_CODE IN pa_period_masks_b.time_phase_code%type,
142 X_CREATION_DATE IN pa_period_masks_b.creation_date%type,
143 X_CREATED_BY IN pa_period_masks_b.created_by%type,
144 X_LAST_UPDATE_LOGIN IN pa_period_masks_b.last_update_login%type,
145 X_LAST_UPDATED_BY IN pa_period_masks_b.last_updated_by%type,
146 X_LAST_UPDATE_DATE IN pa_period_masks_b.last_update_date%type,
147 X_RECORD_VERSION_NUMBER IN pa_period_masks_b.record_version_number%type,
148 X_PRE_DEFINED_FLAG IN pa_period_masks_b.pre_defined_flag%type,
149 X_NAME IN pa_period_masks_tl.name%type,
150 X_DESCRIPTION IN pa_period_masks_tl.description%type)
151 IS
152 BEGIN
153
154 UPDATE pa_period_masks_b
155 SET effective_start_date = X_EFFECTIVE_START_DATE,
156 effective_end_date = X_EFFECTIVE_END_DATE,
157 time_phase_code = X_TIME_PHASE_CODE,
158 creation_date = X_CREATION_DATE,
159 created_by = X_CREATED_BY,
160 last_update_login = X_LAST_UPDATE_LOGIN,
161 last_updated_by = X_LAST_UPDATED_BY,
162 last_update_date = X_LAST_UPDATE_DATE,
163 record_version_number = X_RECORD_VERSION_NUMBER,
164 pre_defined_flag = X_PRE_DEFINED_FLAG
165 where period_mask_id = X_PERIOD_MASK_ID;
166
167 IF (SQL%NOTFOUND) THEN
168 RAISE no_data_found;
169 END IF;
170
171 UPDATE PA_PERIOD_MASKS_TL
172 SET
173 NAME = X_NAME,
174 DESCRIPTION = X_DESCRIPTION,
175 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
176 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
177 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
178 SOURCE_LANG = USERENV('LANG')
179 WHERE period_mask_id = X_PERIOD_MASK_ID
180 AND USERENV('LANG') IN (LANGUAGE, SOURCE_LANG); /* 4397924: modified */
181
182 IF (SQL%NOTFOUND) THEN
183 RAISE no_data_found;
184 END IF;
185
186 END UPDATE_ROW;
187
188
189 PROCEDURE DELETE_ROW(
190 X_PERIOD_MASK_ID IN pa_period_masks_b.period_mask_id%type
191 ) IS
192 BEGIN
193
194 DELETE FROM PA_PERIOD_MASKS_TL
195 WHERE period_mask_id = X_PERIOD_MASK_ID;
196 /* 4397924: Commented the below AND condition as all the records
197 have to be deleted from the _TL table
198 AND USERENV('LANG') IN (SELECT DISTINCT source_lang
199 FROM pa_period_masks_tl); */
200
201 IF (SQL%NOTFOUND) THEN
202 RAISE no_data_found;
203 END IF;
204
205 DELETE FROM PA_PERIOD_MASKS_B
206 WHERE period_mask_id = X_PERIOD_MASK_ID;
207
208 IF (SQL%NOTFOUND) THEN
209 RAISE no_data_found;
210 END IF;
211
212 END DELETE_ROW;
213
214
215 PROCEDURE ADD_LANGUAGE IS
216
217 BEGIN
218
219 DELETE FROM pa_period_masks_tl t
220 WHERE NOT EXISTS
221 (SELECT null
222 FROM pa_period_masks_b b /* 4397924: Referring to the base table */
223 WHERE b.period_mask_id = t.period_mask_id);
224
225 UPDATE pa_period_masks_tl t
226 SET (name,description) =
227 (SELECT b.name,
228 b.description
229 FROM pa_period_masks_tl b
230 WHERE b.period_mask_id = t.period_mask_id
231 AND b.language = t.source_lang)
232 WHERE (t.period_mask_id,
233 t.language) IN (SELECT subt.period_mask_id,
234 subt.language
235 FROM pa_period_masks_tl subb,
236 pa_period_masks_tl subt
237 WHERE subb.period_mask_id = subt.period_mask_id
238 AND subb.language = subt.source_lang
239 AND (subb.name <> subt.name
240 OR subb.description <> subt.description
241 OR (subb.description IS NULL AND subt.description IS NOT NULL)
242 OR (subb.description IS NOT NULL AND subt.description IS NULL)));
243
244 INSERT INTO pa_period_masks_tl(period_mask_id, /* 4397924: added the NOT NULL column */
245 name,
246 description,
247 language,
248 source_lang,
249 last_update_date,
250 last_updated_by,
251 creation_date,
252 created_by,
253 last_update_login)
254 SELECT period_mask_id, /* 4397924: added the NOT NULL column */
255 name,
256 description,
257 l.language_code, /* 4397924: modified */
258 source_lang,
259 b.last_update_date,
260 b.last_updated_by,
261 b.creation_date,
262 b.created_by,
263 b.last_update_login
264 FROM pa_period_masks_tl b,
265 fnd_languages l
266 WHERE l.installed_flag in ('I','B')
267 AND b.language = userenv('LANG')
268 AND NOT EXISTS (SELECT null
269 FROM pa_period_masks_tl t
270 WHERE t.period_mask_id = b.period_mask_id
271 AND t.language = l.language_code);
272
273 END ADD_LANGUAGE;
274
275
276 PROCEDURE TRANSLATE_ROW(
277 X_PERIOD_MASK_ID IN pa_period_masks_b.period_mask_id%type,
278 X_OWNER IN VARCHAR2,
279 X_NAME IN pa_period_masks_tl.name%type,
280 X_DESCRIPTION IN pa_period_masks_tl.description%type
281 ) IS
282 BEGIN
283
284 UPDATE PA_PERIOD_MASKS_TL
285 SET
286 NAME = X_NAME,
287 DESCRIPTION = X_DESCRIPTION,
288 LAST_UPDATE_DATE = sysdate,
289 LAST_UPDATED_BY = decode(X_OWNER, 'SEED', 1, 0),
290 LAST_UPDATE_LOGIN = 0,
291 SOURCE_LANG = USERENV('LANG')
292 WHERE PERIOD_MASK_ID = X_PERIOD_MASK_ID
293 AND USERENV('LANG') IN (LANGUAGE, SOURCE_LANG);
294
295 IF (SQL%NOTFOUND) THEN
296 RAISE no_data_found;
297 END IF;
298
299 END TRANSLATE_ROW;
300
301 PROCEDURE LOAD_ROW(
302 X_PERIOD_MASK_ID IN pa_period_masks_b.period_mask_id%type,
303 X_EFFECTIVE_START_DATE IN pa_period_masks_b.effective_start_date%type,
304 X_EFFECTIVE_END_DATE IN pa_period_masks_b.effective_end_date%type,
305 X_TIME_PHASE_CODE IN pa_period_masks_b.time_phase_code%type,
306 X_CREATION_DATE IN pa_period_masks_b.creation_date%type,
307 X_CREATED_BY IN pa_period_masks_b.created_by%type,
308 X_LAST_UPDATE_LOGIN IN pa_period_masks_b.last_update_login%type,
309 X_LAST_UPDATED_BY IN pa_period_masks_b.last_updated_by%type,
310 X_LAST_UPDATE_DATE IN pa_period_masks_b.last_update_date%type,
311 X_RECORD_VERSION_NUMBER IN pa_period_masks_b.record_version_number%type,
312 X_PRE_DEFINED_FLAG IN pa_period_masks_b.pre_defined_flag%type,
313 X_NAME IN pa_period_masks_tl.name%type,
314 X_DESCRIPTION IN pa_period_masks_tl.description%type,
315 X_OWNER IN VARCHAR2 )
316 IS
317
318 user_id NUMBER;
319 X_ROWID ROWID;
320
321 BEGIN
322
323 IF (X_OWNER = 'SEED') THEN
324 user_id := 1;
325 ELSE
326 user_id :=0;
327 END IF;
328
329 PA_PERIOD_MASKS_PKG.UPDATE_ROW(
330 X_PERIOD_MASK_ID => X_PERIOD_MASK_ID ,
331 X_EFFECTIVE_START_DATE => X_EFFECTIVE_START_DATE,
332 X_EFFECTIVE_END_DATE => X_EFFECTIVE_END_DATE,
333 X_TIME_PHASE_CODE => X_TIME_PHASE_CODE,
334 X_CREATION_DATE => X_CREATION_DATE,
335 X_CREATED_BY => X_CREATED_BY,
336 X_LAST_UPDATE_LOGIN => 0, /* 4397924: modified */
337 X_LAST_UPDATED_BY => user_id, /* 4397924: modified */
338 X_LAST_UPDATE_DATE => sysdate, /* 4397924: modified */
339 X_RECORD_VERSION_NUMBER => X_RECORD_VERSION_NUMBER,
340 X_PRE_DEFINED_FLAG => X_PRE_DEFINED_FLAG,
341 X_NAME => X_NAME,
342 X_DESCRIPTION => X_DESCRIPTION );
343
344 EXCEPTION
345 WHEN no_data_found THEN
346 PA_PERIOD_MASKS_PKG.INSERT_ROW(
347 X_ROWID => X_ROWID ,
348 X_PERIOD_MASK_ID => X_PERIOD_MASK_ID,
349 X_EFFECTIVE_START_DATE => X_EFFECTIVE_START_DATE,
350 X_EFFECTIVE_END_DATE => X_EFFECTIVE_END_DATE,
351 X_TIME_PHASE_CODE => X_TIME_PHASE_CODE,
352 X_CREATION_DATE => sysdate, /* 4397924: modified */
353 X_CREATED_BY => user_id, /* 4397924: modified */
354 X_LAST_UPDATE_LOGIN => 0, /* 4397924: modified */
355 X_LAST_UPDATED_BY => user_id, /* 4397924: modified */
356 X_LAST_UPDATE_DATE => sysdate, /* 4397924: modified */
357 X_RECORD_VERSION_NUMBER => X_RECORD_VERSION_NUMBER,
358 X_PRE_DEFINED_FLAG => X_PRE_DEFINED_FLAG,
359 X_NAME => X_NAME,
360 X_DESCRIPTION => X_DESCRIPTION
361 );
362
363 END LOAD_ROW;
364
365 END PA_PERIOD_MASKS_PKG;