[Home] [Help]
PACKAGE BODY: APPS.JTF_BRMRULE_PVT
Source
1 PACKAGE BODY JTF_BRMRule_PVT AS
2 /* $Header: jtfvbrb.pls 120.2 2005/07/05 07:44:49 abraina ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'JTF_BRMRule_PVT';
5
6 --------------------------------------------------------------------------
7 -- Start of comments
8 -- API name : Create_BRMRule
9 -- Type : Private
10 -- Function : Create record in JTF_BRM_RULES_B and _TL tables.
11 -- Pre-reqs : None.
12 -- Parameters :
13 -- name direction type required?
14 -- ---- --------- ---- ---------
15 -- p_api_version IN NUMBER required
16 -- p_init_msg_list IN VARCHAR2 optional
17 -- p_commit IN VARCHAR2 optional
18 -- p_validation_level IN NUMBER optional
19 -- x_return_status OUT VARCHAR2 required
20 -- x_msg_count OUT NUMBER required
21 -- x_msg_data OUT VARCHAR2 required
22 -- p_br_rec IN BRM_Rule_rec_type required
23 -- x_record_id OUT NUMBER required
24 --
25 -- Version : Current version 1.1
26 -- Previous version 1.0
27 -- Initial version 1.1
28 --
29 -- End of comments
30 --------------------------------------------------------------------------
31
32 PROCEDURE Create_BRMRule
33 ( p_api_version IN NUMBER
34 , p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false
35 , p_commit IN VARCHAR2 DEFAULT fnd_api.g_false
36 , p_validation_level IN NUMBER DEFAULT fnd_api.g_valid_level_full
37 , x_return_status OUT NOCOPY VARCHAR2
38 , x_msg_count OUT NOCOPY NUMBER
39 , x_msg_data OUT NOCOPY VARCHAR2
40 , p_br_rec IN BRM_Rule_rec_type
41 , x_record_id OUT NOCOPY NUMBER
42 )
43 IS
44 l_api_name CONSTANT VARCHAR2(30) := 'Create_BRMRule';
45 l_api_version CONSTANT NUMBER := 1.1;
46 l_api_name_full CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_api_name;
47 l_object_version_number NUMBER := 1;
48 l_return_status VARCHAR2(1);
49 l_rowid ROWID;
50 l_rule_id NUMBER;
51
52 CURSOR c IS SELECT ROWID
53 FROM JTF_BRM_RULES_B
54 WHERE RULE_ID = l_rule_id;
55
56 BEGIN
57 --
58 -- Standard start of API savepoint
59 --
60 SAVEPOINT Create_BRMRule_PVT;
61
62 --
63 -- Standard call to check for call compatibility
64 --
65 IF NOT FND_API.Compatible_API_Call( l_api_version
66 , p_api_version
67 , l_api_name
68 , G_PKG_NAME)
69 THEN
70 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
71 END IF;
72
73 --
74 -- Initialize message list if p_init_msg_list is set to TRUE
75 --
76 IF FND_API.To_Boolean(p_init_msg_list)
77 THEN
78 FND_MSG_PUB.Initialize;
79 END IF;
80
81 --
82 -- Initialize API return status to success
83 --
84 x_return_status := FND_API.G_RET_STS_SUCCESS;
85
86 ------------------------------------------------------------------------
87 -- Perform the database operation. Generate the method ID from the
88 -- sequence, then insert the passed in attributes into the
89 -- JTF_BRM_Rule tables.
90 ----------------------------------------------------------------------
91 IF (p_br_rec.RULE_ID IS NULL)
92 THEN
93 SELECT JTF_BRM_RULES_S.NEXTVAL INTO l_rule_id
94 FROM DUAL;
95 ELSE
96 l_rule_id := p_br_rec.RULE_ID;
97 END IF;
98
99 --
100 -- Insert into _B table
101 --
102 INSERT INTO JTF_BRM_RULES_B
103 ( RULE_ID
104 , BRM_OBJECT_TYPE
105 , BRM_OBJECT_CODE
106 , SEEDED_FLAG
107 , VIEW_DEFINITION
108 , VIEW_NAME
109 , RULE_OWNER
110 , START_DATE_ACTIVE
111 , END_DATE_ACTIVE
112 , ATTRIBUTE1
113 , ATTRIBUTE2
114 , ATTRIBUTE3
115 , ATTRIBUTE4
116 , ATTRIBUTE5
117 , ATTRIBUTE6
118 , ATTRIBUTE7
119 , ATTRIBUTE8
120 , ATTRIBUTE9
121 , ATTRIBUTE10
122 , ATTRIBUTE11
123 , ATTRIBUTE12
124 , ATTRIBUTE13
125 , ATTRIBUTE14
126 , ATTRIBUTE15
127 , ATTRIBUTE_CATEGORY
128 , CREATION_DATE
129 , CREATED_BY
130 , LAST_UPDATE_DATE
131 , LAST_UPDATED_BY
132 , LAST_UPDATE_LOGIN
133 , OBJECT_VERSION_NUMBER
134 , APPLICATION_ID
135 ) VALUES
136 ( l_rule_id
137 , p_br_rec.BRM_OBJECT_TYPE
138 , p_br_rec.BRM_OBJECT_CODE
139 , p_br_rec.SEEDED_FLAG
140 , p_br_rec.VIEW_DEFINITION
141 , p_br_rec.VIEW_NAME
142 , p_br_rec.RULE_OWNER
143 , p_br_rec.START_DATE_ACTIVE
144 , p_br_rec.END_DATE_ACTIVE
145 , p_br_rec.ATTRIBUTE1
146 , p_br_rec.ATTRIBUTE2
147 , p_br_rec.ATTRIBUTE3
148 , p_br_rec.ATTRIBUTE4
149 , p_br_rec.ATTRIBUTE5
150 , p_br_rec.ATTRIBUTE6
151 , p_br_rec.ATTRIBUTE7
152 , p_br_rec.ATTRIBUTE8
153 , p_br_rec.ATTRIBUTE9
154 , p_br_rec.ATTRIBUTE10
155 , p_br_rec.ATTRIBUTE11
156 , p_br_rec.ATTRIBUTE12
157 , p_br_rec.ATTRIBUTE13
158 , p_br_rec.ATTRIBUTE14
159 , p_br_rec.ATTRIBUTE15
160 , p_br_rec.ATTRIBUTE_CATEGORY
161 , p_br_rec.CREATION_DATE
162 , p_br_rec.CREATED_BY
163 , p_br_rec.LAST_UPDATE_DATE
164 , p_br_rec.LAST_UPDATED_BY
165 , p_br_rec.LAST_UPDATE_LOGIN
166 , l_object_version_number
167 , p_br_rec.APPLICATION_ID
168 );
169
170 --
171 -- Insert into _TL table
172 --
173 INSERT INTO JTF_BRM_RULES_TL
174 ( RULE_ID
175 , RULE_NAME
176 , RULE_DESCRIPTION
177 , CREATED_BY
178 , CREATION_DATE
179 , LAST_UPDATED_BY
180 , LAST_UPDATE_DATE
181 , LAST_UPDATE_LOGIN
182 , LANGUAGE
183 , SOURCE_LANG
184 , APPLICATION_ID
185 ) SELECT l_rule_id
186 , p_br_rec.RULE_NAME
187 , p_br_rec.RULE_DESCRIPTION
188 , p_br_rec.CREATED_BY
189 , p_br_rec.CREATION_DATE
190 , p_br_rec.LAST_UPDATED_BY
191 , p_br_rec.LAST_UPDATE_DATE
192 , p_br_rec.LAST_UPDATE_LOGIN
193 , l.LANGUAGE_CODE
194 , userenv('LANG')
195 , p_br_rec.APPLICATION_ID
196 FROM FND_LANGUAGES l
197 WHERE l.INSTALLED_FLAG IN ('I','B')
198 AND NOT EXISTS ( SELECT NULL
199 FROM JTF_BRM_RULES_TL t
200 WHERE t.RULE_ID = l_rule_id
201 AND t.LANGUAGE = l.LANGUAGE_CODE);
202
203 --
204 -- Check whether the insert was succesfull
205 --
206 OPEN c;
207 FETCH c INTO l_rowid;
208 IF (c%NOTFOUND)
209 THEN
210 CLOSE c;
211 RAISE no_data_found;
212 END IF;
213 CLOSE c;
214
215 --
216 -- Return the key value to the caller
217 --
218 x_record_id := l_rule_id;
219
220 --
221 -- Standard check of p_commit
222 --
223 IF FND_API.To_Boolean(p_commit)
224 THEN
225 COMMIT WORK;
226 END IF;
227
228 --
229 -- Standard call to get message count and if count is 1, get message info
230 --
231 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
232 , p_data => x_msg_data
233 );
234
235 EXCEPTION
236 WHEN FND_API.G_EXC_ERROR
237 THEN
238 ROLLBACK TO Create_BRMRule_PVT;
239 x_return_status := FND_API.G_RET_STS_ERROR;
240 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
241 , p_data => x_msg_data
242 );
243 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
244 THEN
245 ROLLBACK TO Create_BRMRule_PVT;
246 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
247 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
248 , p_data => x_msg_data
249 );
250 WHEN OTHERS
251 THEN
252 ROLLBACK TO Create_BRMRule_PVT;
253 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
254 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
255 THEN
256 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME
257 , l_api_name
258 );
259 END IF;
260 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
261 , p_data => x_msg_data
262 );
263
264 END Create_BRMRule;
265
266 --------------------------------------------------------------------------
267 -- Start of comments
268 -- API name : Update_BRMRule
269 -- Type : Private
270 -- Function : Update record in JTF_BRM_RULES_B and _TL tables.
271 -- Pre-reqs : None.
272 -- Parameters :
273 -- name direction type required?
274 -- ---- --------- ---- ---------
275 -- p_api_version IN NUMBER required
276 -- p_init_msg_list IN VARCHAR2 optional
277 -- p_commit IN VARCHAR2 optional
278 -- p_validation_level IN NUMBER optional
279 -- x_return_status OUT VARCHAR2 required
280 -- x_msg_count OUT NUMBER required
281 -- x_msg_data OUT VARCHAR2 required
282 -- p_br_rec IN BRM_Rule_rec_type required
283 --
284 -- Version : Current version 1.1
285 -- Previous version 1.0
286 -- Initial version 1.0
287 --
288 -- Notes:
289 --
290 -- End of comments
291 --------------------------------------------------------------------------
292 PROCEDURE Update_BRMRule
293 ( p_api_version IN NUMBER
294 , p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false
295 , p_commit IN VARCHAR2 DEFAULT fnd_api.g_false
296 , p_validation_level IN NUMBER DEFAULT fnd_api.g_valid_level_full
297 , x_return_status OUT NOCOPY VARCHAR2
298 , x_msg_count OUT NOCOPY NUMBER
299 , x_msg_data OUT NOCOPY VARCHAR2
300 , p_br_rec IN BRM_Rule_rec_type
301 )
302 IS
303 l_api_name CONSTANT VARCHAR2(30) := 'Update_BRMRule';
304 l_api_version CONSTANT NUMBER := 1.1;
305 l_api_name_full CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_api_name;
306 l_object_version_number NUMBER;
307 l_return_status VARCHAR2(1);
308 l_msg_count NUMBER;
309 l_msg_data VARCHAR2(2000);
310
311 BEGIN
312 --
313 -- Standard start of API savepoint
314 --
315 SAVEPOINT Update_BRMRule_PVT;
316
317 --
318 -- Standard call to check for call compatibility
319 --
320 IF NOT FND_API.Compatible_API_Call( l_api_version
321 , p_api_version
322 , l_api_name
323 , G_PKG_NAME)
324 THEN
325 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
326 END IF;
327
328 --
329 -- Initialize message list if p_init_msg_list is set to TRUE
330 --
331 IF FND_API.To_Boolean(p_init_msg_list)
332 THEN
333 FND_MSG_PUB.Initialize;
334 END IF;
335
336 --
337 -- Initialize API return status to success
338 --
339 x_return_status := FND_API.G_RET_STS_SUCCESS;
340
341 --
342 -- Get new object versio number
343 --
344 SELECT JTF_BRM_OBJECT_VERSION_S.NEXTVAL
345 INTO l_object_version_number
346 FROM DUAL;
347
348 UPDATE JTF_BRM_RULES_B
349 SET BRM_OBJECT_TYPE = p_br_rec.BRM_OBJECT_TYPE
350 , BRM_OBJECT_CODE = p_br_rec.BRM_OBJECT_CODE
351 , SEEDED_FLAG = p_br_rec.SEEDED_FLAG
352 , VIEW_DEFINITION = p_br_rec.VIEW_DEFINITION
353 , VIEW_NAME = p_br_rec.VIEW_NAME
354 , RULE_OWNER = p_br_rec.RULE_OWNER
355 , START_DATE_ACTIVE = p_br_rec.START_DATE_ACTIVE
356 , END_DATE_ACTIVE = p_br_rec.END_DATE_ACTIVE
357 , ATTRIBUTE1 = p_br_rec.ATTRIBUTE1
358 , ATTRIBUTE2 = p_br_rec.ATTRIBUTE2
359 , ATTRIBUTE3 = p_br_rec.ATTRIBUTE3
360 , ATTRIBUTE4 = p_br_rec.ATTRIBUTE4
361 , ATTRIBUTE5 = p_br_rec.ATTRIBUTE5
362 , ATTRIBUTE6 = p_br_rec.ATTRIBUTE6
363 , ATTRIBUTE7 = p_br_rec.ATTRIBUTE7
364 , ATTRIBUTE8 = p_br_rec.ATTRIBUTE8
365 , ATTRIBUTE9 = p_br_rec.ATTRIBUTE9
366 , ATTRIBUTE10 = p_br_rec.ATTRIBUTE10
367 , ATTRIBUTE11 = p_br_rec.ATTRIBUTE11
368 , ATTRIBUTE12 = p_br_rec.ATTRIBUTE12
369 , ATTRIBUTE13 = p_br_rec.ATTRIBUTE13
370 , ATTRIBUTE14 = p_br_rec.ATTRIBUTE14
371 , ATTRIBUTE15 = p_br_rec.ATTRIBUTE15
372 , ATTRIBUTE_CATEGORY = p_br_rec.ATTRIBUTE_CATEGORY
373 , LAST_UPDATE_DATE = p_br_rec.LAST_UPDATE_DATE
374 , LAST_UPDATED_BY = p_br_rec.LAST_UPDATED_BY
375 , LAST_UPDATE_LOGIN = p_br_rec.LAST_UPDATE_LOGIN
376 , OBJECT_VERSION_NUMBER = l_object_version_number
377 , APPLICATION_ID = p_br_rec.APPLICATION_ID
378 WHERE RULE_ID = p_br_rec.RULE_ID;
379
380 --
381 -- Check whether update was succesful
382 --
383 IF (SQL%NOTFOUND)
384 THEN
385 RAISE NO_DATA_FOUND;
386 END IF;
387
388 UPDATE JTF_BRM_RULES_TL
389 SET RULE_NAME = p_br_rec.RULE_NAME
390 , RULE_DESCRIPTION = p_br_rec.RULE_DESCRIPTION
391 , LAST_UPDATE_DATE = p_br_rec.LAST_UPDATE_DATE
392 , LAST_UPDATED_BY = p_br_rec.LAST_UPDATED_BY
393 , LAST_UPDATE_LOGIN = p_br_rec.LAST_UPDATE_LOGIN
394 , SOURCE_LANG = userenv('LANG')
395 , APPLICATION_ID = p_br_rec.APPLICATION_ID
396 WHERE RULE_ID = p_br_rec.RULE_ID
397 AND userenv('LANG') IN (LANGUAGE, SOURCE_LANG);
398
399 --
400 -- Check whether update was succesful
401 --
402 IF (SQL%NOTFOUND)
403 THEN
404 RAISE NO_DATA_FOUND;
405 END IF;
406
407 --
408 -- Standard check of p_commit
409 --
410 IF FND_API.To_Boolean(p_commit)
411 THEN
412 COMMIT WORK;
413 END IF;
414
415 --
416 -- Standard call to get message count and if count is 1, get message info
417 --
418 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
419 , p_data => x_msg_data
420 );
421
422 EXCEPTION
423 WHEN FND_API.G_EXC_ERROR
424 THEN
425 ROLLBACK TO Update_BRMRule_PVT;
426 x_return_status := FND_API.G_RET_STS_ERROR;
427 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
428 , p_data => x_msg_data
429 );
430
431 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
432 THEN
433 ROLLBACK TO Update_BRMRule_PVT;
434 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
435 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
436 , p_data => x_msg_data
437 );
438
439 WHEN OTHERS
440 THEN
441 ROLLBACK TO Update_BRMRule_PVT;
442 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
443 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
444 THEN
445 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME
446 , l_api_name
447 );
448 END IF;
449 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
453 END Update_BRMRule;
450 , p_data => x_msg_data
451 );
452
454
455 --------------------------------------------------------------------------
456 -- Start of comments
457 -- API Name : Delete_BRMRule
458 -- Type : Private
459 -- Description : Delete record in JTF_BRM_RULES_B and _TL tables.
460 -- Pre-reqs : None
461 -- Parameters :
462 -- name direction type required?
463 -- ---- --------- ---- ---------
464 -- p_api_version IN NUMBER required
465 -- p_init_msg_list IN VARCHAR2 optional
466 -- p_commit IN VARCHAR2 optional
467 -- p_validation_level IN NUMBER optional
468 -- x_return_status OUT VARCHAR2 required
469 -- x_msg_count OUT NUMBER required
470 -- x_msg_data OUT VARCHAR2 required
471 -- p_rule_id IN NUMBER required
472 --
473 -- Version : Current version 1.1
474 -- Previous version 1.0
475 -- Initial version 1.0
476 --
477 -- Notes: :
478 --
479 -- End of comments
480 --------------------------------------------------------------------------
481 PROCEDURE Delete_BRMRule
482 ( p_api_version IN NUMBER
483 , p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false
484 , p_commit IN VARCHAR2 DEFAULT fnd_api.g_false
485 , p_validation_level IN NUMBER DEFAULT fnd_api.g_valid_level_full
486 , x_return_status OUT NOCOPY VARCHAR2
487 , x_msg_count OUT NOCOPY NUMBER
488 , x_msg_data OUT NOCOPY VARCHAR2
489 , p_rule_id IN NUMBER
490 )
491 IS
492 l_api_name CONSTANT VARCHAR2(30) := 'Delete_BRMRule';
493 l_api_version CONSTANT NUMBER := 1.1;
494 l_api_name_full CONSTANT VARCHAR2(62) := G_PKG_NAME||'.'||l_api_name;
495 l_return_status VARCHAR2(1);
496 l_msg_count NUMBER;
497 l_msg_data VARCHAR2(2000);
498 BEGIN
499 --
500 -- Establish save point
501 --
502 SAVEPOINT Delete_BRMRule_PVT;
503
504 --
505 -- Check version number
506 --
507 IF NOT FND_API.Compatible_API_Call( l_api_version
508 , p_api_version
509 , l_api_name
510 , G_PKG_NAME
511 )
512 THEN
513 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
514 END IF;
515
516 --
517 -- Initialize message list if requested
518 --
519 IF FND_API.to_Boolean( p_init_msg_list )
520 THEN
521 FND_MSG_PUB.initialize;
522 END IF;
523
524 --
525 -- Initialize return status to SUCCESS
526 --
527 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
528
529 DELETE FROM JTF_BRM_RULES_TL
530 WHERE RULE_ID = p_rule_id;
531
532 --
533 -- Check whether delete was succesful
534 --
535 IF (SQL%NOTFOUND)
536 THEN
537 RAISE no_data_found;
538 END IF;
539
540 DELETE FROM JTF_BRM_RULES_B
541 WHERE RULE_ID = p_rule_id;
542
543 --
544 -- Check whether delete was succesful
545 --
546 IF (SQL%NOTFOUND)
547 THEN
548 RAISE no_data_found;
549 END IF;
550
551 IF FND_API.To_Boolean( p_commit )
552 THEN
553 COMMIT WORK;
554 END IF;
555
556 FND_MSG_PUB.Count_And_Get( p_count => X_MSG_COUNT
557 , p_data => X_MSG_DATA
558 );
559
560 EXCEPTION
561 WHEN FND_API.G_EXC_ERROR
562 THEN
563 ROLLBACK TO Delete_BRMRule_PVT;
564 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
565 FND_MSG_PUB.Count_And_Get( p_count => X_MSG_COUNT
566 , p_data => X_MSG_DATA
567 );
568
569 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
570 THEN
571 ROLLBACK TO Delete_BRMRule_PVT;
572 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
573 FND_MSG_PUB.Count_And_Get( p_count => X_MSG_COUNT
574 , p_data => X_MSG_DATA
575 );
576
577 WHEN OTHERS
578 THEN
579 ROLLBACK TO Delete_BRMRule_PVT;
580 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
581 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
582 THEN
583 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME
584 , l_api_name
585 );
586 END IF;
587 FND_MSG_PUB.Count_And_Get( p_count => X_MSG_COUNT
588 , p_data => X_MSG_DATA
589 );
590
591 END Delete_BRMRule;
592
593 --------------------------------------------------------------------------
594 -- Start of comments
595 -- API Name : Add_Language
596 -- Type : Private
600 -- Parameters : None
597 -- Description : Additional Language processing for JTF_BRM_RULES_B and
598 -- _TL tables.
599 -- Pre-reqs : None
601 -- Version : Current version 1.0
602 -- Previous version none
603 -- Initial version 1.0
604 --
605 -- Notes: :
606 --
607 -- End of comments
608 --------------------------------------------------------------------------
609 PROCEDURE Add_Language
610 IS
611 BEGIN
612
613 --
614 -- Delete all records that don't have a base record
615 --
616 DELETE FROM JTF_BRM_RULES_TL t
617 WHERE NOT EXISTS (SELECT NULL
618 FROM JTF_BRM_RULES_B b
619 WHERE b.RULE_ID = t.RULE_ID
620 );
621
622 --
623 -- Translate the records that already exists
624 --
625 UPDATE JTF_BRM_RULES_TL T
626 SET ( RULE_NAME
627 , RULE_DESCRIPTION
628 ) = ( SELECT B.RULE_NAME
629 , B.RULE_DESCRIPTION
630 FROM JTF_BRM_RULES_TL B
631 WHERE B.RULE_ID = T.RULE_ID
632 AND B.LANGUAGE = T.SOURCE_LANG
633 )
634 WHERE ( T.RULE_ID
635 , T.LANGUAGE
636 ) IN ( SELECT SUBT.RULE_ID
637 , SUBT.LANGUAGE
638 FROM JTF_BRM_RULES_TL SUBB
639 , JTF_BRM_RULES_TL SUBT
640 WHERE SUBB.RULE_ID = SUBT.RULE_ID
641 AND SUBB.LANGUAGE = SUBT.SOURCE_LANG
642 AND ( SUBB.RULE_NAME <> SUBT.RULE_NAME
643 OR SUBB.RULE_DESCRIPTION <> SUBT.RULE_DESCRIPTION
644 OR ( SUBB.RULE_DESCRIPTION IS NULL
645 AND SUBT.RULE_DESCRIPTION IS NOT NULL
646 )
647 OR ( SUBB.RULE_DESCRIPTION IS NOT NULL
648 AND SUBT.RULE_DESCRIPTION IS NULL
649 )
650 )
651 );
652
653 --
654 -- add records for new languages
655 --
656 INSERT INTO JTF_BRM_RULES_TL
657 ( RULE_ID
658 , CREATED_BY
659 , CREATION_DATE
660 , LAST_UPDATED_BY
661 , LAST_UPDATE_DATE
662 , LAST_UPDATE_LOGIN
663 , RULE_NAME
664 , RULE_DESCRIPTION
665 , LANGUAGE
666 , SOURCE_LANG
667 , APPLICATION_ID
668 ) SELECT B.RULE_ID
669 , B.CREATED_BY
670 , B.CREATION_DATE
671 , B.LAST_UPDATED_BY
672 , B.LAST_UPDATE_DATE
673 , B.LAST_UPDATE_LOGIN
674 , B.RULE_NAME
675 , B.RULE_DESCRIPTION
676 , L.LANGUAGE_CODE
677 , B.SOURCE_LANG
678 , B.APPLICATION_ID
679 FROM JTF_BRM_RULES_TL B, FND_LANGUAGES L
680 WHERE L.INSTALLED_FLAG IN ('I', 'B')
681 AND B.LANGUAGE = USERENV('LANG')
682 AND NOT EXISTS (SELECT NULL
683 FROM JTF_BRM_RULES_TL T
684 WHERE T.RULE_ID = B.RULE_ID
685 AND T.LANGUAGE = L.LANGUAGE_CODE
686 );
687 END Add_Language;
688
689 --------------------------------------------------------------------------
690 -- Start of comments
691 -- API Name : Translate_Row
692 -- Type : Private
693 -- Description : Additional Language processing for JTF_BRM_RULES_B and
694 -- _TL tables. Used in the FNDLOAD definition file (.lct)
695 -- Pre-reqs : None
696 -- Parameters : None
697 -- Version : Current version 1.0
698 -- Previous version none
699 -- Initial version 1.0
700 --
701 -- Notes: :
702 --
703 -- End of comments
704 --------------------------------------------------------------------------
705 PROCEDURE Translate_Row
706 ( p_rule_id IN NUMBER
707 , p_rule_name IN VARCHAR2
708 , p_rule_description IN VARCHAR2
709 , p_owner IN VARCHAR2
710 )
711 IS
712 BEGIN
713 UPDATE JTF_BRM_RULES_TL
714 SET RULE_NAME = p_rule_name
715 , RULE_DESCRIPTION = p_rule_description
716 , LAST_UPDATE_DATE = SYSDATE
717 , LAST_UPDATED_BY = DECODE(p_owner, 'SEED',1,0)
718 , LAST_UPDATE_LOGIN = 0
719 , SOURCE_LANG = userenv('LANG')
720 WHERE userenv('LANG') IN (LANGUAGE, SOURCE_LANG)
721 AND RULE_ID = p_rule_id;
722 END Translate_Row;
723
724 --------------------------------------------------------------------------
725 -- Start of comments
726 -- API Name : Load_Row
727 -- Type : Private
728 -- Description : Additional Language processing for JTF_BRM_RULES_B and
729 -- _TL tables. Used in the FNDLOAD definition file (.lct)
730 -- Pre-reqs : None
731 -- Parameters : None
732 -- Version : Current version 1.0
733 -- Previous version none
734 -- Initial version 1.0
735 --
736 -- Notes: :
737 --
738 -- End of comments
739 --------------------------------------------------------------------------
740 PROCEDURE Load_Row
741 ( p_rule_id IN NUMBER
742 , p_br_rec IN BRM_Rule_rec_type
743 , p_owner IN VARCHAR2
744 )
745 IS
746 l_user_id NUMBER;
750 -- Determine the user_id from p_owner
747
748 BEGIN
749 ------------------------------------------------------------------------
751 ------------------------------------------------------------------------
752 IF (p_owner IS NOT NULL) AND (p_owner = 'SEED')
753 THEN
754 l_user_id := 1;
755 ELSE
756 l_user_id := 0;
757 END IF;
758
759 ------------------------------------------------------------------------
760 -- Try to update the record in the base table
761 ------------------------------------------------------------------------
762 UPDATE JTF_BRM_RULES_B
763 SET BRM_OBJECT_TYPE = p_br_rec.brm_object_type
764 , BRM_OBJECT_CODE = p_br_rec.brm_object_code
765 , SEEDED_FLAG = p_br_rec.seeded_flag
766 , VIEW_DEFINITION = P_br_rec.view_definition
767 , VIEW_NAME = p_br_rec.view_name
768 , RULE_OWNER = p_br_rec.rule_owner
769 , START_DATE_ACTIVE = p_br_rec.start_date_active
770 , END_DATE_ACTIVE = p_br_rec.end_date_active
771 , ATTRIBUTE1 = p_br_rec.attribute1
772 , ATTRIBUTE2 = p_br_rec.attribute2
773 , ATTRIBUTE3 = p_br_rec.attribute3
774 , ATTRIBUTE4 = p_br_rec.attribute4
775 , ATTRIBUTE5 = p_br_rec.attribute5
776 , ATTRIBUTE6 = p_br_rec.attribute6
777 , ATTRIBUTE7 = p_br_rec.attribute7
778 , ATTRIBUTE8 = p_br_rec.attribute8
779 , ATTRIBUTE9 = p_br_rec.attribute9
780 , ATTRIBUTE10 = p_br_rec.attribute10
781 , ATTRIBUTE11 = p_br_rec.attribute11
782 , ATTRIBUTE12 = p_br_rec.attribute12
783 , ATTRIBUTE13 = p_br_rec.attribute13
784 , ATTRIBUTE14 = p_br_rec.attribute14
785 , ATTRIBUTE15 = p_br_rec.attribute15
786 , ATTRIBUTE_CATEGORY = p_br_rec.attribute_category
787 , OBJECT_VERSION_NUMBER = p_br_rec.object_version_number
788 , APPLICATION_ID = p_br_rec.application_id
789 , LAST_UPDATE_DATE = SYSDATE
790 , LAST_UPDATED_BY = l_user_id
791 , LAST_UPDATE_LOGIN = 0
792 WHERE RULE_ID = p_rule_id;
793
794 ------------------------------------------------------------------------
795 -- Apparently the record doesn't exist so create it
796 ------------------------------------------------------------------------
797 IF (SQL%NOTFOUND)
798 THEN
799 INSERT INTO JTF_BRM_RULES_B
800 ( RULE_ID
801 , BRM_OBJECT_TYPE
802 , BRM_OBJECT_CODE
803 , SEEDED_FLAG
804 , VIEW_DEFINITION
805 , VIEW_NAME
806 , RULE_OWNER
807 , START_DATE_ACTIVE
808 , END_DATE_ACTIVE
809 , ATTRIBUTE1
810 , ATTRIBUTE2
811 , ATTRIBUTE3
812 , ATTRIBUTE4
813 , ATTRIBUTE5
814 , ATTRIBUTE6
815 , ATTRIBUTE7
816 , ATTRIBUTE8
817 , ATTRIBUTE9
818 , ATTRIBUTE10
819 , ATTRIBUTE11
820 , ATTRIBUTE12
821 , ATTRIBUTE13
822 , ATTRIBUTE14
823 , ATTRIBUTE15
824 , ATTRIBUTE_CATEGORY
825 , OBJECT_VERSION_NUMBER
826 , APPLICATION_ID
827 , CREATION_DATE
828 , CREATED_BY
829 , LAST_UPDATE_DATE
830 , LAST_UPDATED_BY
831 , LAST_UPDATE_LOGIN
832 ) VALUES
833 ( p_rule_id
834 , p_br_rec.BRM_OBJECT_TYPE
835 , p_br_rec.BRM_OBJECT_CODE
836 , p_br_rec.SEEDED_FLAG
837 , p_br_rec.VIEW_DEFINITION
838 , p_br_rec.VIEW_NAME
839 , p_br_rec.RULE_OWNER
840 , p_br_rec.START_DATE_ACTIVE
841 , p_br_rec.END_DATE_ACTIVE
842 , p_br_rec.ATTRIBUTE1
843 , p_br_rec.ATTRIBUTE2
844 , p_br_rec.ATTRIBUTE3
845 , p_br_rec.ATTRIBUTE4
846 , p_br_rec.ATTRIBUTE5
847 , p_br_rec.ATTRIBUTE6
848 , p_br_rec.ATTRIBUTE7
849 , p_br_rec.ATTRIBUTE8
850 , p_br_rec.ATTRIBUTE9
851 , p_br_rec.ATTRIBUTE10
852 , p_br_rec.ATTRIBUTE11
853 , p_br_rec.ATTRIBUTE12
854 , p_br_rec.ATTRIBUTE13
855 , p_br_rec.ATTRIBUTE14
856 , p_br_rec.ATTRIBUTE15
857 , p_br_rec.ATTRIBUTE_CATEGORY
858 , p_br_rec.object_version_number
859 , p_br_rec.application_id
860 , SYSDATE
861 , l_user_id
862 , SYSDATE
863 , l_user_id
864 , 0
865 );
866 END IF;
867
868 ------------------------------------------------------------------------
869 -- Try to update the record in the language table
870 ------------------------------------------------------------------------
871 UPDATE JTF_BRM_RULES_TL
872 SET RULE_NAME = p_br_rec.rule_name
873 , RULE_DESCRIPTION = p_br_rec.rule_description
874 , LAST_UPDATE_DATE = SYSDATE
875 , LAST_UPDATED_BY = l_user_id
876 , LAST_UPDATE_LOGIN = 0
877 , SOURCE_LANG = userenv('LANG')
878 WHERE RULE_ID = p_rule_id
879 AND userenv('LANG') IN (LANGUAGE, SOURCE_LANG);
880
881 ------------------------------------------------------------------------
882 -- Apparently the record doesn't exist so create it
883 ------------------------------------------------------------------------
887 ( RULE_ID
884 IF (SQL%NOTFOUND)
885 THEN
886 INSERT INTO JTF_BRM_RULES_TL
888 , CREATED_BY
889 , CREATION_DATE
890 , LAST_UPDATED_BY
891 , LAST_UPDATE_DATE
892 , LAST_UPDATE_LOGIN
893 , RULE_NAME
894 , RULE_DESCRIPTION
895 , LANGUAGE
896 , SOURCE_LANG
897 , APPLICATION_ID
898 ) SELECT p_rule_id
899 , l_user_id
900 , SYSDATE
901 , l_user_id
902 , SYSDATE
903 , 0
904 , p_br_rec.rule_name
905 , p_br_rec.rule_description
906 , l.LANGUAGE_CODE
907 , userenv('LANG')
908 , p_br_rec.application_id
909 FROM FND_LANGUAGES l
910 WHERE l.INSTALLED_FLAG IN ('I', 'B')
911 AND NOT EXISTS( SELECT NULL
912 FROM JTF_BRM_RULES_TL t
913 WHERE t.RULE_ID = p_rule_id
914 AND t.LANGUAGE = l.LANGUAGE_CODE
915 );
916 END IF;
917 END Load_Row;
918
919 END JTF_BRMRule_PVT;