[Home] [Help]
PACKAGE BODY: APPS.CSI_MASS_EDIT_ENTRIES_B_PKG
Source
1 PACKAGE BODY CSI_MASS_EDIT_ENTRIES_B_PKG as
2 /* $Header: csitmedb.pls 120.2.12010000.2 2008/11/06 20:28:59 mashah ship $ */
3 -- Start of Comments
4 -- Package name : CSI_MASS_EDIT_ENTRIES_B_PKG
5 -- Purpose :
6 -- History :
7 -- NOTE :
8 -- End of Comments
9
10
11 G_PKG_NAME CONSTANT VARCHAR2(30):= 'CSI_MASS_EDIT_ENTRIES_B_PKG';
12 G_FILE_NAME CONSTANT VARCHAR2(12) := 'csitmedb.pls';
13
14 PROCEDURE Insert_Row(
15 px_ENTRY_ID IN OUT NOCOPY NUMBER,
16 px_TXN_LINE_ID IN OUT NOCOPY NUMBER,
17 px_TXN_LINE_DETAIL_ID IN OUT NOCOPY NUMBER,
18 p_STATUS_CODE VARCHAR2,
19 p_SCHEDULE_DATE DATE,
20 p_START_DATE DATE,
21 p_END_DATE DATE,
22 p_CREATED_BY NUMBER,
23 p_CREATION_DATE DATE,
24 p_LAST_UPDATED_BY NUMBER,
25 p_LAST_UPDATE_DATE DATE,
26 p_LAST_UPDATE_LOGIN NUMBER,
27 p_OBJECT_VERSION_NUMBER NUMBER,
28 p_NAME VARCHAR2,
29 p_BATCH_TYPE VARCHAR2,
30 p_DESCRIPTION VARCHAR2,
31 p_SYSTEM_CASCADE VARCHAR2
32 )
33
34 IS
35
36 L_Transaction_Id NUMBER;
37 L_Txn_Line_Detail_Id NUMBER;
38 L_sub_type_id NUMBER;
39
40 CURSOR C2 IS SELECT CSI_MASS_EDIT_ENTRIES_S.nextval FROM sys.dual;
44 FETCH C2 INTO px_ENTRY_ID;
41 BEGIN
42 If (px_ENTRY_ID IS NULL) OR (px_ENTRY_ID = FND_API.G_MISS_NUM) then
43 OPEN C2;
45 CLOSE C2;
46 End If;
47
48 SELECT CSI_T_TRANSACTION_LINES_S.nextval
49 INTO px_TXN_LINE_ID
50 FROM SYS.Dual;
51
52 INSERT INTO CSI_MASS_EDIT_ENTRIES_B(
53 ENTRY_ID,
54 TXN_LINE_ID,
55 STATUS_CODE,
56 BATCH_TYPE,
57 SCHEDULE_DATE,
58 START_DATE,
59 END_DATE,
60 CREATED_BY,
61 CREATION_DATE,
62 LAST_UPDATED_BY,
63 LAST_UPDATE_DATE,
64 LAST_UPDATE_LOGIN,
65 OBJECT_VERSION_NUMBER,
66 SYSTEM_CASCADE
67 ) VALUES (
68 px_ENTRY_ID,
69 decode( px_TXN_LINE_ID, FND_API.G_MISS_NUM, NULL, px_TXN_LINE_ID),
70 decode( p_STATUS_CODE, FND_API.G_MISS_CHAR, NULL, p_STATUS_CODE),
71 decode( p_BATCH_TYPE, FND_API.G_MISS_CHAR, NULL, p_BATCH_TYPE),
72 decode( p_SCHEDULE_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), p_SCHEDULE_DATE),
73 decode( p_START_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), p_START_DATE),
74 decode( p_END_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), p_END_DATE),
75 decode( p_CREATED_BY, FND_API.G_MISS_NUM, NULL, p_CREATED_BY),
76 decode( p_CREATION_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), p_CREATION_DATE),
77 decode( p_LAST_UPDATED_BY, FND_API.G_MISS_NUM, NULL, p_LAST_UPDATED_BY),
78 decode( p_LAST_UPDATE_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), p_LAST_UPDATE_DATE),
79 decode( p_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, NULL, p_LAST_UPDATE_LOGIN),
80 decode( p_OBJECT_VERSION_NUMBER, FND_API.G_MISS_NUM, NULL, p_OBJECT_VERSION_NUMBER),
81 decode( p_SYSTEM_CASCADE, FND_API.G_MISS_CHAR, NULL, p_SYSTEM_CASCADE));
82
83 INSERT INTO CSI_MASS_EDIT_ENTRIES_TL(
84 ENTRY_ID,
85 NAME,
86 DESCRIPTION,
87 LANGUAGE,
88 SOURCE_LANG,
89 CREATED_BY,
90 CREATION_DATE,
91 LAST_UPDATED_BY,
92 LAST_UPDATE_DATE,
93 LAST_UPDATE_LOGIN
94 ) SELECT
95 px_ENTRY_ID,
96 decode( p_NAME, FND_API.G_MISS_CHAR, NULL, p_NAME),
97 decode( p_DESCRIPTION, FND_API.G_MISS_CHAR, NULL, p_DESCRIPTION),
98 L.LANGUAGE_CODE, userenv('LANG'),
99 decode( p_CREATED_BY, FND_API.G_MISS_NUM, NULL, p_CREATED_BY),
100 decode( p_CREATION_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), p_CREATION_DATE),
101 decode( p_LAST_UPDATED_BY, FND_API.G_MISS_NUM, NULL, p_LAST_UPDATED_BY),
102 decode( p_LAST_UPDATE_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), p_LAST_UPDATE_DATE),
103 decode( p_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, NULL, p_LAST_UPDATE_LOGIN)
104 from FND_LANGUAGES L
105 where L.INSTALLED_FLAG in ('I', 'B')
106 and not exists
107 (select NULL
108 from CSI_MASS_EDIT_ENTRIES_TL T
109 where T.ENTRY_ID = px_ENTRY_ID
110 and T.LANGUAGE = L.LANGUAGE_CODE);
111
112
113 INSERT INTO CSI_T_TRANSACTION_LINES(
114 TRANSACTION_LINE_ID,
115 SOURCE_TRANSACTION_TABLE,
116 SOURCE_TRANSACTION_ID,
117 CREATED_BY,
118 CREATION_DATE,
119 LAST_UPDATED_BY,
120 LAST_UPDATE_DATE,
121 OBJECT_VERSION_NUMBER,
122 SOURCE_TRANSACTION_TYPE_ID,
123 PROCESSING_STATUS
124 ) VALUES (
125 decode( px_TXN_LINE_ID, FND_API.G_MISS_NUM, NULL, px_TXN_LINE_ID),
126 'CSI_MASS_EDIT_ENTRIES',
127 px_ENTRY_ID,
128 decode( p_CREATED_BY, FND_API.G_MISS_NUM, NULL, p_CREATED_BY),
129 decode( p_CREATION_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), p_CREATION_DATE),
130 decode( p_LAST_UPDATED_BY, FND_API.G_MISS_NUM, NULL, p_LAST_UPDATED_BY),
131 decode( p_LAST_UPDATE_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), p_LAST_UPDATE_DATE),
132 decode( p_OBJECT_VERSION_NUMBER, FND_API.G_MISS_NUM, NULL, p_OBJECT_VERSION_NUMBER),
133 3 ,
134 'SUBMIT');
135
136 -- Inserting Non - Source Record Into CSI_T_TXN_LINE_DETAILS Table.
137
138 SELECT CSI_T_TXN_LINE_DETAILS_S.nextval
139 INTO L_Txn_Line_Detail_Id
140 FROM SYS.Dual;
141
142 px_TXN_LINE_DETAIL_ID := l_txn_line_detail_id;
143
144 SELECT sub_type_id
145 INTO l_SUB_TYPE_ID
146 FROM CSI_TXN_SUB_TYPES
147 WHERE transaction_type_id = 3
148 AND IB_TXN_TYPE_CODE = p_BATCH_TYPE;
149
150 INSERT INTO CSI_T_TXN_LINE_DETAILS(
151 TXN_LINE_DETAIL_ID,
152 TRANSACTION_LINE_ID,
153 INSTANCE_EXISTS_FLAG,
154 SOURCE_TRANSACTION_FLAG,
155 CREATED_BY,
156 CREATION_DATE,
157 LAST_UPDATED_BY,
158 LAST_UPDATE_DATE,
159 OBJECT_VERSION_NUMBER,
160 SUB_TYPE_ID,
161 ASSC_TXN_LINE_DETAIL_ID,
162 PROCESSING_STATUS
163 ) VALUES (
164 L_Txn_Line_Detail_Id,
165 decode( px_TXN_LINE_ID, FND_API.G_MISS_NUM, NULL, px_TXN_LINE_ID),
166 'N',
167 'Y',
168 decode( p_CREATED_BY, FND_API.G_MISS_NUM, NULL, p_CREATED_BY),
172 decode( p_OBJECT_VERSION_NUMBER, FND_API.G_MISS_NUM, NULL, p_OBJECT_VERSION_NUMBER),
169 decode( p_CREATION_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), p_CREATION_DATE),
170 decode( p_LAST_UPDATED_BY, FND_API.G_MISS_NUM, NULL, p_LAST_UPDATED_BY),
171 decode( p_LAST_UPDATE_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), p_LAST_UPDATE_DATE),
173 l_sub_type_id,
174 l_txn_line_detail_id ,
175 'SUBMIT');
176 End Insert_Row;
177
178 PROCEDURE Update_Row(
179 p_ENTRY_ID NUMBER,
180 p_TXN_LINE_ID NUMBER,
181 p_STATUS_CODE VARCHAR2,
182 p_SCHEDULE_DATE DATE,
183 p_START_DATE DATE,
184 p_END_DATE DATE,
185 p_CREATED_BY NUMBER,
186 p_CREATION_DATE DATE,
187 p_LAST_UPDATED_BY NUMBER,
188 p_LAST_UPDATE_DATE DATE,
189 p_LAST_UPDATE_LOGIN NUMBER,
190 p_OBJECT_VERSION_NUMBER NUMBER,
191 p_NAME VARCHAR2,
192 p_BATCH_TYPE VARCHAR2,
193 p_DESCRIPTION VARCHAR2,
194 p_SYSTEM_CASCADE VARCHAR2
195 )
196
197 IS
198 BEGIN
199 Update CSI_MASS_EDIT_ENTRIES_B
200 SET
201 STATUS_CODE = decode( p_STATUS_CODE, FND_API.G_MISS_CHAR, STATUS_CODE, p_STATUS_CODE),
202 SCHEDULE_DATE = decode( p_SCHEDULE_DATE, FND_API.G_MISS_DATE, SCHEDULE_DATE, p_SCHEDULE_DATE),
203 START_DATE = decode( p_START_DATE, FND_API.G_MISS_DATE, START_DATE, p_START_DATE),
204 END_DATE = decode( p_END_DATE, FND_API.G_MISS_DATE, END_DATE, p_END_DATE),
205 CREATED_BY = decode( p_CREATED_BY, FND_API.G_MISS_NUM, CREATED_BY, p_CREATED_BY),
206 CREATION_DATE = decode( p_CREATION_DATE, FND_API.G_MISS_DATE, CREATION_DATE, p_CREATION_DATE),
207 LAST_UPDATED_BY = decode( p_LAST_UPDATED_BY, FND_API.G_MISS_NUM, LAST_UPDATED_BY, p_LAST_UPDATED_BY),
208 LAST_UPDATE_DATE = decode( p_LAST_UPDATE_DATE, FND_API.G_MISS_DATE, LAST_UPDATE_DATE, p_LAST_UPDATE_DATE),
209 LAST_UPDATE_LOGIN = decode( p_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, LAST_UPDATE_LOGIN, p_LAST_UPDATE_LOGIN),
210 OBJECT_VERSION_NUMBER = decode( p_OBJECT_VERSION_NUMBER, FND_API.G_MISS_NUM, OBJECT_VERSION_NUMBER, p_OBJECT_VERSION_NUMBER),
211 BATCH_TYPE = decode( p_BATCH_TYPE, FND_API.G_MISS_CHAR, BATCH_TYPE, p_BATCH_TYPE),
212 SYSTEM_CASCADE = decode( p_SYSTEM_CASCADE, FND_API.G_MISS_CHAR, SYSTEM_CASCADE, p_SYSTEM_CASCADE)
213 where ENTRY_ID = p_ENTRY_ID;
214 -- and txn_line_id = p_txn_line_id;
215
216 If (SQL%NOTFOUND) then
217 RAISE NO_DATA_FOUND;
218 End If;
219
220 update CSI_MASS_EDIT_ENTRIES_TL set
221 NAME = decode( p_NAME, FND_API.G_MISS_CHAR, NAME, p_NAME),
222 DESCRIPTION = decode( p_DESCRIPTION, FND_API.G_MISS_CHAR, DESCRIPTION, p_DESCRIPTION),
223 LAST_UPDATE_DATE = decode( p_LAST_UPDATE_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), p_LAST_UPDATE_DATE),
224 LAST_UPDATED_BY = decode( p_LAST_UPDATED_BY, FND_API.G_MISS_NUM, NULL, p_LAST_UPDATED_BY),
225 LAST_UPDATE_LOGIN = decode( p_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, NULL, p_LAST_UPDATE_LOGIN),
226 SOURCE_LANG = userenv('LANG')
227 where ENTRY_ID = p_ENTRY_ID
228 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
229
230 If (SQL%NOTFOUND) then
231 RAISE NO_DATA_FOUND;
232 End If;
233 END Update_Row;
234
235 PROCEDURE Delete_Row(
236 p_ENTRY_ID NUMBER)
237 IS
238 BEGIN
239
240 -- DELETE_ROW cannot be used to delete Item records.
241
242 --raise_application_error( -20000, 'Cannot delete Entry from CSI_MASS_EDIT_ENTRIES_B_PKG.DELETE_ROW' ); uncommented for mass update r12
243
244 DELETE FROM CSI_MASS_EDIT_ENTRIES_B
245 WHERE ENTRY_ID = p_ENTRY_ID;
246 If (SQL%NOTFOUND) then
247 RAISE NO_DATA_FOUND;
248 End If;
249
250 DELETE FROM CSI_MASS_EDIT_ENTRIES_TL
251 WHERE ENTRY_ID = p_ENTRY_ID;
252
253 If (SQL%NOTFOUND) then
254 RAISE NO_DATA_FOUND;
255 End If;
256
257 END Delete_Row;
258
259 PROCEDURE Lock_Row(
260 p_ENTRY_ID NUMBER,
261 p_TXN_LINE_ID NUMBER,
262 p_STATUS_CODE VARCHAR2,
263 p_SCHEDULE_DATE DATE,
264 p_START_DATE DATE,
265 p_END_DATE DATE,
266 p_CREATED_BY NUMBER,
267 p_CREATION_DATE DATE,
268 p_LAST_UPDATED_BY NUMBER,
269 p_LAST_UPDATE_DATE DATE,
270 p_LAST_UPDATE_LOGIN NUMBER,
271 p_OBJECT_VERSION_NUMBER NUMBER,
272 p_NAME VARCHAR2
273 )
274
275 IS
276 CURSOR C IS
277 SELECT *
278 FROM CSI_MASS_EDIT_ENTRIES_B
279 WHERE ENTRY_ID = p_ENTRY_ID
280 FOR UPDATE of ENTRY_ID NOWAIT;
281 Recinfo C%ROWTYPE;
282
283 CURSOR C1 IS
284 SELECT ENTRY_ID,
285 NAME,
286 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
287 FROM CSI_MASS_EDIT_ENTRIES_TL
288 WHERE ENTRY_ID = p_ENTRY_ID
289 AND userenv('LANG') in (LANGUAGE, SOURCE_LANG)
290 FOR UPDATE of ENTRY_ID NOWAIT;
291
292
293 BEGIN
294 OPEN C;
295 FETCH C INTO Recinfo;
296 If (C%NOTFOUND) then
297 CLOSE C;
298 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
299 APP_EXCEPTION.RAISE_EXCEPTION;
300 End If;
301 CLOSE C;
302 if (
303 ( Recinfo.ENTRY_ID = p_ENTRY_ID)
304 AND ( Recinfo.TXN_LINE_ID = p_TXN_LINE_ID)
305 AND ( ( Recinfo.STATUS_CODE = p_STATUS_CODE)
306 OR ( ( Recinfo.STATUS_CODE IS NULL )
307 AND ( p_STATUS_CODE IS NULL )))
308 AND ( ( Recinfo.SCHEDULE_DATE = p_SCHEDULE_DATE)
309 OR ( ( Recinfo.SCHEDULE_DATE IS NULL )
310 AND ( p_SCHEDULE_DATE IS NULL )))
311 AND ( ( Recinfo.START_DATE = p_START_DATE)
312 OR ( ( Recinfo.START_DATE IS NULL )
313 AND ( p_START_DATE IS NULL )))
314 AND ( ( Recinfo.END_DATE = p_END_DATE)
315 OR ( ( Recinfo.END_DATE IS NULL )
316 AND ( p_END_DATE IS NULL )))
317 AND ( ( Recinfo.CREATED_BY = p_CREATED_BY)
318 OR ( ( Recinfo.CREATED_BY IS NULL )
319 AND ( p_CREATED_BY IS NULL )))
320 AND ( ( Recinfo.CREATION_DATE = p_CREATION_DATE)
321 OR ( ( Recinfo.CREATION_DATE IS NULL )
322 AND ( p_CREATION_DATE IS NULL )))
323 AND ( ( Recinfo.LAST_UPDATED_BY = p_LAST_UPDATED_BY)
324 OR ( ( Recinfo.LAST_UPDATED_BY IS NULL )
325 AND ( p_LAST_UPDATED_BY IS NULL )))
326 AND ( ( Recinfo.LAST_UPDATE_DATE = p_LAST_UPDATE_DATE)
327 OR ( ( Recinfo.LAST_UPDATE_DATE IS NULL )
328 AND ( p_LAST_UPDATE_DATE IS NULL )))
329 AND ( ( Recinfo.LAST_UPDATE_LOGIN = p_LAST_UPDATE_LOGIN)
330 OR ( ( Recinfo.LAST_UPDATE_LOGIN IS NULL )
331 AND ( p_LAST_UPDATE_LOGIN IS NULL )))
332 AND ( ( Recinfo.OBJECT_VERSION_NUMBER = p_OBJECT_VERSION_NUMBER)
333 OR ( ( Recinfo.OBJECT_VERSION_NUMBER IS NULL )
334 AND ( p_OBJECT_VERSION_NUMBER IS NULL )))
335 ) then
336 return;
337 else
338 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED');
339 APP_EXCEPTION.RAISE_EXCEPTION;
340 End If;
341
342 FOR tlinfo in C1 LOOP
343 IF (tlinfo.BASELANG = 'Y') then
344 IF (
345 ( tlinfo.ENTRY_ID = p_ENTRY_ID)
346 AND ( ( tlinfo.NAME = p_NAME)
347 OR ( ( tlinfo.NAME IS NULL )
348 AND ( p_NAME IS NULL )
349 )
350 )
351 ) THEN
352 NULL;
353 ELSE
354 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
355 app_exception.raise_exception;
356 END IF;
357 END IF;
358 END LOOP;
359 RETURN;
360
361 END Lock_Row;
362
363 PROCEDURE ADD_LANGUAGE
364 IS
365 BEGIN
366 /********* COMMENTED FOR BUG 4238439 (Refer 3723612 for solution)
367 DELETE FROM CSI_MASS_EDIT_ENTRIES_TL T
368 WHERE NOT EXISTS
369 (SELECT NULL
370 FROM CSI_MASS_EDIT_ENTRIES_B B
371 WHERE B.ENTRY_ID = T.ENTRY_ID
372 );
373
374 UPDATE CSI_MASS_EDIT_ENTRIES_TL T SET (
375 NAME
376 ) = (SELECT
377 B.NAME
378 FROM CSI_MASS_EDIT_ENTRIES_TL B
379 WHERE B.ENTRY_ID = T.ENTRY_ID
380 AND B.LANGUAGE = T.SOURCE_LANG)
381 WHERE (
382 T.ENTRY_ID,
383 T.LANGUAGE
384 ) IN (SELECT
385 SUBT.ENTRY_ID,
386 SUBT.LANGUAGE
387 FROM CSI_MASS_EDIT_ENTRIES_TL SUBB, CSI_MASS_EDIT_ENTRIES_TL SUBT
388 WHERE SUBB.ENTRY_ID = SUBT.ENTRY_ID
389 AND SUBB.LANGUAGE = SUBT.SOURCE_LANG
390 AND (SUBB.NAME <> SUBT.NAME
391 OR (SUBB.NAME IS NULL AND SUBT.NAME IS NOT NULL)
392 OR (SUBB.NAME IS NOT NULL AND SUBT.NAME IS NULL)
393 ));
394 ********* END OF COMMENT **********/
395
396 INSERT /*+ append parallel(tt) */ INTO CSI_MASS_EDIT_ENTRIES_TL tt (
397 ENTRY_ID,
398 NAME,
399 CREATED_BY,
400 CREATION_DATE,
401 LAST_UPDATED_BY,
402 LAST_UPDATE_DATE,
403 LAST_UPDATE_LOGIN,
404 LANGUAGE,
405 SOURCE_LANG
406 )
407 select /*+ parallel(v) parallel(t) use_nl(t) */ v.* from
408 ( SELECT /*+ no_merge ordered parallel(b) */
409 B.ENTRY_ID,
410 B.NAME,
411 B.CREATED_BY,
412 B.CREATION_DATE,
413 B.LAST_UPDATED_BY,
414 B.LAST_UPDATE_DATE,
415 B.LAST_UPDATE_LOGIN,
416 L.LANGUAGE_CODE,
417 B.SOURCE_LANG
418 FROM CSI_MASS_EDIT_ENTRIES_TL B, FND_LANGUAGES L
419 WHERE L.INSTALLED_FLAG IN ('I', 'B')
420 AND B.LANGUAGE = userenv('LANG')
421 ) v, CSI_MASS_EDIT_ENTRIES_TL t
422 WHERE t.entry_id(+) = v.entry_id
423 AND t.language(+) = v.language_code
424 AND t.entry_id IS NULL;
425
426 /******* COMMENTED for Bug 4238439
427 AND NOT EXISTS
428 (SELECT NULL
429 FROM CSI_MASS_EDIT_ENTRIES_TL T
430 WHERE T.ENTRY_ID = B.ENTRY_ID
431 AND T.LANGUAGE = L.LANGUAGE_CODE); **********/
432 END ADD_LANGUAGE;
433
434
435 PROCEDURE TRANSLATE_ROW (
436 p_entry_id IN NUMBER,
437 p_name IN VARCHAR2,
438 p_owner IN VARCHAR2
439 ) IS
440 BEGIN
441 UPDATE csi_mass_edit_entries_tl
442 SET NAME = p_name,
443 LAST_UPDATE_DATE = SYSDATE,
444 LAST_UPDATED_BY = decode(p_owner, 'SEED', 1, 0),
445 LAST_UPDATE_LOGIN = 0,
446 SOURCE_LANG = userenv('LANG')
447 WHERE entry_id= p_entry_id
448 AND userenv('LANG') IN (language, source_lang);
449 END TRANSLATE_ROW;
450
451
452
453 End CSI_MASS_EDIT_ENTRIES_B_PKG;