eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.CSP_EXCESS_RULES_B_PKG

Source


1 PACKAGE BODY CSP_EXCESS_RULES_B_PKG as
2 /* $Header: csptexrb.pls 115.5 2002/11/26 07:26:56 hhaugeru noship $ */
3 -- Start of Comments
4 -- Package name     : CSP_EXCESS_RULES_B_PKG
5 -- Purpose          :
6 -- History          :
7 -- NOTE             :
8 -- End of Comments
9 
10 
11 
12 G_PKG_NAME CONSTANT VARCHAR2(30):= 'CSP_EXCESS_RULES_B_PKG';
13 G_FILE_NAME CONSTANT VARCHAR2(12) := 'csptexrb.pls';
14 
15 PROCEDURE Insert_Row(
16           px_EXCESS_RULE_ID   IN OUT NOCOPY NUMBER,
17           p_CREATED_BY    NUMBER,
18           p_CREATION_DATE    DATE,
19           p_LAST_UPDATED_BY    NUMBER,
20           p_LAST_UPDATE_DATE    DATE,
21           p_LAST_UPDATE_LOGIN    NUMBER,
22           p_EXCESS_RULE_NAME    VARCHAR2,
23           p_TOTAL_MAX_EXCESS    NUMBER,
24           p_LINE_MAX_EXCESS    NUMBER,
25           p_DAYS_SINCE_RECEIPT    NUMBER,
26           p_TOTAL_EXCESS_VALUE    NUMBER,
27           p_TOP_EXCESS_LINES    NUMBER,
28           p_CATEGORY_SET_ID    NUMBER,
29           p_CATEGORY_ID    NUMBER,
30           p_ATTRIBUTE_CATEGORY    VARCHAR2,
31           p_ATTRIBUTE1    VARCHAR2,
32           p_ATTRIBUTE2    VARCHAR2,
33           p_ATTRIBUTE3    VARCHAR2,
34           p_ATTRIBUTE4    VARCHAR2,
35           p_ATTRIBUTE5    VARCHAR2,
36           p_ATTRIBUTE6    VARCHAR2,
37           p_ATTRIBUTE7    VARCHAR2,
38           p_ATTRIBUTE8    VARCHAR2,
39           p_ATTRIBUTE9    VARCHAR2,
40           p_ATTRIBUTE10    VARCHAR2,
41           p_ATTRIBUTE11    VARCHAR2,
42           p_ATTRIBUTE12    VARCHAR2,
43           p_ATTRIBUTE13    VARCHAR2,
44           p_ATTRIBUTE14    VARCHAR2,
45           p_ATTRIBUTE15    VARCHAR2,
46           p_DESCRIPTION       VARCHAR2)
47  IS
48    CURSOR C2 IS SELECT CSP_EXCESS_RULES_B_S1.nextval FROM sys.dual;
49 BEGIN
50    If (px_EXCESS_RULE_ID IS NULL) OR (px_EXCESS_RULE_ID = FND_API.G_MISS_NUM) then
51        OPEN C2;
52        FETCH C2 INTO px_EXCESS_RULE_ID;
53        CLOSE C2;
54    End If;
55    INSERT INTO CSP_EXCESS_RULES_B(
56            EXCESS_RULE_ID,
57            CREATED_BY,
58            CREATION_DATE,
59            LAST_UPDATED_BY,
60            LAST_UPDATE_DATE,
61            LAST_UPDATE_LOGIN,
62            EXCESS_RULE_NAME,
63            TOTAL_MAX_EXCESS,
64            LINE_MAX_EXCESS,
65            DAYS_SINCE_RECEIPT,
66            TOTAL_EXCESS_VALUE,
67            TOP_EXCESS_LINES,
68            CATEGORY_SET_ID,
69            CATEGORY_ID,
70            ATTRIBUTE_CATEGORY,
71            ATTRIBUTE1,
72            ATTRIBUTE2,
73            ATTRIBUTE3,
74            ATTRIBUTE4,
75            ATTRIBUTE5,
76            ATTRIBUTE6,
77            ATTRIBUTE7,
78            ATTRIBUTE8,
79            ATTRIBUTE9,
80            ATTRIBUTE10,
81            ATTRIBUTE11,
82            ATTRIBUTE12,
83            ATTRIBUTE13,
84            ATTRIBUTE14,
85            ATTRIBUTE15
86           ) VALUES (
87            px_EXCESS_RULE_ID,
88            decode( p_CREATED_BY, FND_API.G_MISS_NUM, NULL, p_CREATED_BY),
89            decode( p_CREATION_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), p_CREATION_DATE),
90            decode( p_LAST_UPDATED_BY, FND_API.G_MISS_NUM, NULL, p_LAST_UPDATED_BY),
91            decode( p_LAST_UPDATE_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), p_LAST_UPDATE_DATE),
92            decode( p_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, NULL, p_LAST_UPDATE_LOGIN),
93            decode( p_EXCESS_RULE_NAME, FND_API.G_MISS_CHAR, NULL, p_EXCESS_RULE_NAME),
94            decode( p_TOTAL_MAX_EXCESS, FND_API.G_MISS_NUM, NULL, p_TOTAL_MAX_EXCESS),
95            decode( p_LINE_MAX_EXCESS, FND_API.G_MISS_NUM, NULL, p_LINE_MAX_EXCESS),
96            decode( p_DAYS_SINCE_RECEIPT, FND_API.G_MISS_NUM, NULL, p_DAYS_SINCE_RECEIPT),
97            decode( p_TOTAL_EXCESS_VALUE, FND_API.G_MISS_NUM, NULL, p_TOTAL_EXCESS_VALUE),
98            decode( p_TOP_EXCESS_LINES, FND_API.G_MISS_NUM, NULL, p_TOP_EXCESS_LINES),
99            decode( p_CATEGORY_SET_ID, FND_API.G_MISS_NUM, NULL, p_CATEGORY_SET_ID),
100            decode( p_CATEGORY_ID, FND_API.G_MISS_NUM, NULL, p_CATEGORY_ID),
101            decode( p_ATTRIBUTE_CATEGORY, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE_CATEGORY),
102            decode( p_ATTRIBUTE1, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE1),
103            decode( p_ATTRIBUTE2, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE2),
104            decode( p_ATTRIBUTE3, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE3),
105            decode( p_ATTRIBUTE4, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE4),
106            decode( p_ATTRIBUTE5, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE5),
107            decode( p_ATTRIBUTE6, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE6),
108            decode( p_ATTRIBUTE7, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE7),
109            decode( p_ATTRIBUTE8, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE8),
110            decode( p_ATTRIBUTE9, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE9),
111            decode( p_ATTRIBUTE10, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE10),
112            decode( p_ATTRIBUTE11, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE11),
113            decode( p_ATTRIBUTE12, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE12),
114            decode( p_ATTRIBUTE13, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE13),
115            decode( p_ATTRIBUTE14, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE14),
116            decode( p_ATTRIBUTE15, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE15));
117            insert into CSP_EXCESS_RULES_TL (
118                                               EXCESS_RULE_ID,
119                                               CREATED_BY,
120                                               CREATION_DATE,
121                                               LAST_UPDATED_BY,
122                                               LAST_UPDATE_DATE,
123                                               LAST_UPDATE_LOGIN,
124                                               DESCRIPTION,
125                                               LANGUAGE,
126                                               SOURCE_LANG
127                                               ) select
128                                                     pX_EXCESS_RULE_ID,
129                                                     p_CREATED_BY,
130                                                     p_CREATION_DATE,
131                                                     p_LAST_UPDATED_BY,
132                                                     p_last_update_DATE,
133                                                     p_LAST_UPDATE_LOGIN,
134                                                      p_DESCRIPTION,
135                                                     L.LANGUAGE_CODE,
136                                                     userenv('LANG')
137                                               from FND_LANGUAGES L
138                                               where L.INSTALLED_FLAG in ('I', 'B')
139                                               and not exists
140                                                 (select NULL
141                                                  from CSP_EXCESS_RULES_TL T
142                                                  where T.EXCESS_RULE_ID = pX_EXCESS_RULE_ID
143                                                 and T.LANGUAGE = L.LANGUAGE_CODE);
144 
145 End Insert_Row;
146 
147 PROCEDURE Update_Row(
148           p_EXCESS_RULE_ID    NUMBER,
149           p_CREATED_BY    NUMBER,
150           p_CREATION_DATE    DATE,
151           p_LAST_UPDATED_BY    NUMBER,
152           p_LAST_UPDATE_DATE    DATE,
153           p_LAST_UPDATE_LOGIN    NUMBER,
154           p_EXCESS_RULE_NAME    VARCHAR2,
155           p_TOTAL_MAX_EXCESS    NUMBER,
156           p_LINE_MAX_EXCESS    NUMBER,
157           p_DAYS_SINCE_RECEIPT    NUMBER,
158           p_TOTAL_EXCESS_VALUE    NUMBER,
159           p_TOP_EXCESS_LINES    NUMBER,
160           p_CATEGORY_SET_ID    NUMBER,
161           p_CATEGORY_ID    NUMBER,
162           p_ATTRIBUTE_CATEGORY    VARCHAR2,
163           p_ATTRIBUTE1    VARCHAR2,
164           p_ATTRIBUTE2    VARCHAR2,
165           p_ATTRIBUTE3    VARCHAR2,
166           p_ATTRIBUTE4    VARCHAR2,
167           p_ATTRIBUTE5    VARCHAR2,
168           p_ATTRIBUTE6    VARCHAR2,
169           p_ATTRIBUTE7    VARCHAR2,
170           p_ATTRIBUTE8    VARCHAR2,
171           p_ATTRIBUTE9    VARCHAR2,
172           p_ATTRIBUTE10    VARCHAR2,
173           p_ATTRIBUTE11    VARCHAR2,
174           p_ATTRIBUTE12    VARCHAR2,
175           p_ATTRIBUTE13    VARCHAR2,
176           p_ATTRIBUTE14    VARCHAR2,
177           p_ATTRIBUTE15    VARCHAR2,
178           p_DESCRIPTION       VARCHAR2)
179  IS
180  BEGIN
181     Update CSP_EXCESS_RULES_B
182     SET
183               CREATED_BY = decode( p_CREATED_BY, FND_API.G_MISS_NUM, CREATED_BY, p_CREATED_BY),
184               CREATION_DATE = decode( p_CREATION_DATE, FND_API.G_MISS_DATE, CREATION_DATE, p_CREATION_DATE),
185               LAST_UPDATED_BY = decode( p_LAST_UPDATED_BY, FND_API.G_MISS_NUM, LAST_UPDATED_BY, p_LAST_UPDATED_BY),
186               LAST_UPDATE_DATE = decode( p_LAST_UPDATE_DATE, FND_API.G_MISS_DATE, LAST_UPDATE_DATE, p_LAST_UPDATE_DATE),
187               LAST_UPDATE_LOGIN = decode( p_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, LAST_UPDATE_LOGIN, p_LAST_UPDATE_LOGIN),
188               EXCESS_RULE_NAME = decode( p_EXCESS_RULE_NAME, FND_API.G_MISS_CHAR, EXCESS_RULE_NAME, p_EXCESS_RULE_NAME),
189               TOTAL_MAX_EXCESS = decode( p_TOTAL_MAX_EXCESS, FND_API.G_MISS_NUM, TOTAL_MAX_EXCESS, p_TOTAL_MAX_EXCESS),
190               LINE_MAX_EXCESS = decode( p_LINE_MAX_EXCESS, FND_API.G_MISS_NUM, LINE_MAX_EXCESS, p_LINE_MAX_EXCESS),
191               DAYS_SINCE_RECEIPT = decode( p_DAYS_SINCE_RECEIPT, FND_API.G_MISS_NUM, DAYS_SINCE_RECEIPT, p_DAYS_SINCE_RECEIPT),
192               TOTAL_EXCESS_VALUE = decode( p_TOTAL_EXCESS_VALUE, FND_API.G_MISS_NUM, TOTAL_EXCESS_VALUE, p_TOTAL_EXCESS_VALUE),
193               TOP_EXCESS_LINES = decode( p_TOP_EXCESS_LINES, FND_API.G_MISS_NUM, TOP_EXCESS_LINES, p_TOP_EXCESS_LINES),
194               CATEGORY_SET_ID = decode( p_CATEGORY_SET_ID, FND_API.G_MISS_NUM, CATEGORY_SET_ID, p_CATEGORY_SET_ID),
195               CATEGORY_ID = decode( p_CATEGORY_ID, FND_API.G_MISS_NUM, CATEGORY_ID, p_CATEGORY_ID),
196               ATTRIBUTE_CATEGORY = decode( p_ATTRIBUTE_CATEGORY, FND_API.G_MISS_CHAR, ATTRIBUTE_CATEGORY, p_ATTRIBUTE_CATEGORY),
197               ATTRIBUTE1 = decode( p_ATTRIBUTE1, FND_API.G_MISS_CHAR, ATTRIBUTE1, p_ATTRIBUTE1),
198               ATTRIBUTE2 = decode( p_ATTRIBUTE2, FND_API.G_MISS_CHAR, ATTRIBUTE2, p_ATTRIBUTE2),
199               ATTRIBUTE3 = decode( p_ATTRIBUTE3, FND_API.G_MISS_CHAR, ATTRIBUTE3, p_ATTRIBUTE3),
200               ATTRIBUTE4 = decode( p_ATTRIBUTE4, FND_API.G_MISS_CHAR, ATTRIBUTE4, p_ATTRIBUTE4),
201               ATTRIBUTE5 = decode( p_ATTRIBUTE5, FND_API.G_MISS_CHAR, ATTRIBUTE5, p_ATTRIBUTE5),
202               ATTRIBUTE6 = decode( p_ATTRIBUTE6, FND_API.G_MISS_CHAR, ATTRIBUTE6, p_ATTRIBUTE6),
203               ATTRIBUTE7 = decode( p_ATTRIBUTE7, FND_API.G_MISS_CHAR, ATTRIBUTE7, p_ATTRIBUTE7),
204               ATTRIBUTE8 = decode( p_ATTRIBUTE8, FND_API.G_MISS_CHAR, ATTRIBUTE8, p_ATTRIBUTE8),
205               ATTRIBUTE9 = decode( p_ATTRIBUTE9, FND_API.G_MISS_CHAR, ATTRIBUTE9, p_ATTRIBUTE9),
206               ATTRIBUTE10 = decode( p_ATTRIBUTE10, FND_API.G_MISS_CHAR, ATTRIBUTE10, p_ATTRIBUTE10),
207               ATTRIBUTE11 = decode( p_ATTRIBUTE11, FND_API.G_MISS_CHAR, ATTRIBUTE11, p_ATTRIBUTE11),
208               ATTRIBUTE12 = decode( p_ATTRIBUTE12, FND_API.G_MISS_CHAR, ATTRIBUTE12, p_ATTRIBUTE12),
209               ATTRIBUTE13 = decode( p_ATTRIBUTE13, FND_API.G_MISS_CHAR, ATTRIBUTE13, p_ATTRIBUTE13),
210               ATTRIBUTE14 = decode( p_ATTRIBUTE14, FND_API.G_MISS_CHAR, ATTRIBUTE14, p_ATTRIBUTE14),
211               ATTRIBUTE15 = decode( p_ATTRIBUTE15, FND_API.G_MISS_CHAR, ATTRIBUTE15, p_ATTRIBUTE15)
212     where EXCESS_RULE_ID = p_EXCESS_RULE_ID;
213 
214     If (SQL%NOTFOUND) then
215         RAISE NO_DATA_FOUND;
216     End If;
217 
218     update CSP_EXCESS_RULES_TL set
219         DESCRIPTION = p_DESCRIPTION,
220         LAST_UPDATE_DATE = p_last_update_DATE,
221         LAST_UPDATED_BY = p_LAST_UPDATED_BY,
222         LAST_UPDATE_LOGIN = p_LAST_UPDATE_LOGIN,
223         SOURCE_LANG = userenv('LANG')
224    where EXCESS_RULE_ID = p_EXCESS_RULE_ID
225    and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
226 
227    if (sql%notfound) then
228      raise no_data_found;
229    end if;
230 END Update_Row;
231 
232 PROCEDURE Delete_Row(
233     p_EXCESS_RULE_ID  NUMBER)
234  IS
235  BEGIN
236    DELETE FROM CSP_EXCESS_RULES_B
237     WHERE EXCESS_RULE_ID = p_EXCESS_RULE_ID;
238    If (SQL%NOTFOUND) then
239        RAISE NO_DATA_FOUND;
240    End If;
241    DELETE FROM CSP_EXCESS_RULES_TL
242     WHERE EXCESS_RULE_ID = p_EXCESS_RULE_ID;
243    If (SQL%NOTFOUND) then
244        RAISE NO_DATA_FOUND;
245    End If;
246  END Delete_Row;
247 
248 PROCEDURE Lock_Row(
249           p_EXCESS_RULE_ID    NUMBER,
250           p_CREATED_BY    NUMBER,
251           p_CREATION_DATE    DATE,
252           p_LAST_UPDATED_BY    NUMBER,
253           p_LAST_UPDATE_DATE    DATE,
254           p_LAST_UPDATE_LOGIN    NUMBER,
255           p_EXCESS_RULE_NAME    VARCHAR2,
256           p_TOTAL_MAX_EXCESS    NUMBER,
257           p_LINE_MAX_EXCESS    NUMBER,
258           p_DAYS_SINCE_RECEIPT    NUMBER,
259           p_TOTAL_EXCESS_VALUE    NUMBER,
260           p_TOP_EXCESS_LINES    NUMBER,
261           p_CATEGORY_SET_ID    NUMBER,
262           p_CATEGORY_ID    NUMBER,
263           p_ATTRIBUTE_CATEGORY    VARCHAR2,
264           p_ATTRIBUTE1    VARCHAR2,
265           p_ATTRIBUTE2    VARCHAR2,
266           p_ATTRIBUTE3    VARCHAR2,
267           p_ATTRIBUTE4    VARCHAR2,
268           p_ATTRIBUTE5    VARCHAR2,
269           p_ATTRIBUTE6    VARCHAR2,
270           p_ATTRIBUTE7    VARCHAR2,
271           p_ATTRIBUTE8    VARCHAR2,
272           p_ATTRIBUTE9    VARCHAR2,
273           p_ATTRIBUTE10    VARCHAR2,
274           p_ATTRIBUTE11    VARCHAR2,
275           p_ATTRIBUTE12    VARCHAR2,
276           p_ATTRIBUTE13    VARCHAR2,
277           p_ATTRIBUTE14    VARCHAR2,
278           p_ATTRIBUTE15    VARCHAR2,
279           p_DESCRIPTION       VARCHAR2)
280  IS
281    CURSOR C IS
282         SELECT *
283          FROM CSP_EXCESS_RULES_B
284         WHERE EXCESS_RULE_ID =  p_EXCESS_RULE_ID
285         FOR UPDATE of EXCESS_RULE_ID NOWAIT;
286    CURSOR c1 is select
287       DESCRIPTION,
288       decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
289     from CSP_EXCESS_RULES_TL
290     where EXCESS_RULE_ID = p_EXCESS_RULE_ID
291     and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
292     for update of EXCESS_RULE_ID nowait;
293 
294    Recinfo C%ROWTYPE;
295  BEGIN
296     OPEN C;
297     FETCH C INTO Recinfo;
298     If (C%NOTFOUND) then
299         CLOSE C;
300         FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
301         APP_EXCEPTION.RAISE_EXCEPTION;
302     End If;
303     CLOSE C;
304     if (
308                 AND (  p_CREATED_BY IS NULL )))
305            (      Recinfo.EXCESS_RULE_ID = p_EXCESS_RULE_ID)
306        AND (    ( Recinfo.CREATED_BY = p_CREATED_BY)
307             OR (    ( Recinfo.CREATED_BY IS NULL )
309        AND (    ( Recinfo.CREATION_DATE = p_CREATION_DATE)
310             OR (    ( Recinfo.CREATION_DATE IS NULL )
311                 AND (  p_CREATION_DATE IS NULL )))
312        AND (    ( Recinfo.LAST_UPDATED_BY = p_LAST_UPDATED_BY)
313             OR (    ( Recinfo.LAST_UPDATED_BY IS NULL )
314                 AND (  p_LAST_UPDATED_BY IS NULL )))
315        AND (    ( Recinfo.LAST_UPDATE_DATE = p_LAST_UPDATE_DATE)
316             OR (    ( Recinfo.LAST_UPDATE_DATE IS NULL )
317                 AND (  p_LAST_UPDATE_DATE IS NULL )))
318        AND (    ( Recinfo.LAST_UPDATE_LOGIN = p_LAST_UPDATE_LOGIN)
319             OR (    ( Recinfo.LAST_UPDATE_LOGIN IS NULL )
320                 AND (  p_LAST_UPDATE_LOGIN IS NULL )))
321        AND (    ( Recinfo.EXCESS_RULE_NAME = p_EXCESS_RULE_NAME)
322             OR (    ( Recinfo.EXCESS_RULE_NAME IS NULL )
323                 AND (  p_EXCESS_RULE_NAME IS NULL )))
324        AND (    ( Recinfo.TOTAL_MAX_EXCESS = p_TOTAL_MAX_EXCESS)
325             OR (    ( Recinfo.TOTAL_MAX_EXCESS IS NULL )
326                 AND (  p_TOTAL_MAX_EXCESS IS NULL )))
327        AND (    ( Recinfo.LINE_MAX_EXCESS = p_LINE_MAX_EXCESS)
328             OR (    ( Recinfo.LINE_MAX_EXCESS IS NULL )
329                 AND (  p_LINE_MAX_EXCESS IS NULL )))
330        AND (    ( Recinfo.DAYS_SINCE_RECEIPT = p_DAYS_SINCE_RECEIPT)
331             OR (    ( Recinfo.DAYS_SINCE_RECEIPT IS NULL )
332                 AND (  p_DAYS_SINCE_RECEIPT IS NULL )))
333        AND (    ( Recinfo.TOTAL_EXCESS_VALUE = p_TOTAL_EXCESS_VALUE)
334             OR (    ( Recinfo.TOTAL_EXCESS_VALUE IS NULL )
335                 AND (  p_TOTAL_EXCESS_VALUE IS NULL )))
336        AND (    ( Recinfo.TOP_EXCESS_LINES = p_TOP_EXCESS_LINES)
337             OR (    ( Recinfo.TOP_EXCESS_LINES IS NULL )
338                 AND (  p_TOP_EXCESS_LINES IS NULL )))
339        AND (    ( Recinfo.CATEGORY_SET_ID = p_CATEGORY_SET_ID)
340             OR (    ( Recinfo.CATEGORY_SET_ID IS NULL )
341                 AND (  p_CATEGORY_SET_ID IS NULL )))
342        AND (    ( Recinfo.CATEGORY_ID = p_CATEGORY_ID)
343             OR (    ( Recinfo.CATEGORY_ID IS NULL )
344                 AND (  p_CATEGORY_ID IS NULL )))
345        AND (    ( Recinfo.ATTRIBUTE_CATEGORY = p_ATTRIBUTE_CATEGORY)
346             OR (    ( Recinfo.ATTRIBUTE_CATEGORY IS NULL )
347                 AND (  p_ATTRIBUTE_CATEGORY IS NULL )))
348        AND (    ( Recinfo.ATTRIBUTE1 = p_ATTRIBUTE1)
349             OR (    ( Recinfo.ATTRIBUTE1 IS NULL )
350                 AND (  p_ATTRIBUTE1 IS NULL )))
351        AND (    ( Recinfo.ATTRIBUTE2 = p_ATTRIBUTE2)
352             OR (    ( Recinfo.ATTRIBUTE2 IS NULL )
353                 AND (  p_ATTRIBUTE2 IS NULL )))
354        AND (    ( Recinfo.ATTRIBUTE3 = p_ATTRIBUTE3)
355             OR (    ( Recinfo.ATTRIBUTE3 IS NULL )
356                 AND (  p_ATTRIBUTE3 IS NULL )))
357        AND (    ( Recinfo.ATTRIBUTE4 = p_ATTRIBUTE4)
358             OR (    ( Recinfo.ATTRIBUTE4 IS NULL )
359                 AND (  p_ATTRIBUTE4 IS NULL )))
360        AND (    ( Recinfo.ATTRIBUTE5 = p_ATTRIBUTE5)
361             OR (    ( Recinfo.ATTRIBUTE5 IS NULL )
362                 AND (  p_ATTRIBUTE5 IS NULL )))
363        AND (    ( Recinfo.ATTRIBUTE6 = p_ATTRIBUTE6)
364             OR (    ( Recinfo.ATTRIBUTE6 IS NULL )
365                 AND (  p_ATTRIBUTE6 IS NULL )))
366        AND (    ( Recinfo.ATTRIBUTE7 = p_ATTRIBUTE7)
367             OR (    ( Recinfo.ATTRIBUTE7 IS NULL )
368                 AND (  p_ATTRIBUTE7 IS NULL )))
369        AND (    ( Recinfo.ATTRIBUTE8 = p_ATTRIBUTE8)
370             OR (    ( Recinfo.ATTRIBUTE8 IS NULL )
371                 AND (  p_ATTRIBUTE8 IS NULL )))
372        AND (    ( Recinfo.ATTRIBUTE9 = p_ATTRIBUTE9)
373             OR (    ( Recinfo.ATTRIBUTE9 IS NULL )
374                 AND (  p_ATTRIBUTE9 IS NULL )))
375        AND (    ( Recinfo.ATTRIBUTE10 = p_ATTRIBUTE10)
376             OR (    ( Recinfo.ATTRIBUTE10 IS NULL )
377                 AND (  p_ATTRIBUTE10 IS NULL )))
378        AND (    ( Recinfo.ATTRIBUTE11 = p_ATTRIBUTE11)
379             OR (    ( Recinfo.ATTRIBUTE11 IS NULL )
380                 AND (  p_ATTRIBUTE11 IS NULL )))
381        AND (    ( Recinfo.ATTRIBUTE12 = p_ATTRIBUTE12)
382             OR (    ( Recinfo.ATTRIBUTE12 IS NULL )
383                 AND (  p_ATTRIBUTE12 IS NULL )))
384        AND (    ( Recinfo.ATTRIBUTE13 = p_ATTRIBUTE13)
385             OR (    ( Recinfo.ATTRIBUTE13 IS NULL )
386                 AND (  p_ATTRIBUTE13 IS NULL )))
387        AND (    ( Recinfo.ATTRIBUTE14 = p_ATTRIBUTE14)
388             OR (    ( Recinfo.ATTRIBUTE14 IS NULL )
389                 AND (  p_ATTRIBUTE14 IS NULL )))
390        AND (    ( Recinfo.ATTRIBUTE15 = p_ATTRIBUTE15)
391             OR (    ( Recinfo.ATTRIBUTE15 IS NULL )
392                 AND (  p_ATTRIBUTE15 IS NULL )))
393        ) then
394        return;
395    else
396        FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED');
397        APP_EXCEPTION.RAISE_EXCEPTION;
398    End If;
399    for tlinfo in c1 loop
400     if (tlinfo.BASELANG = 'Y') then
401       if (    ((tlinfo.DESCRIPTION = p_DESCRIPTION)
402                OR ((tlinfo.DESCRIPTION is null) AND (p_DESCRIPTION is null)))
403       ) then
404         null;
405       else
406         fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
407         app_exception.raise_exception;
408       end if;
409     end if;
410   end loop;
411   return;
412 END Lock_Row;
413 procedure ADD_LANGUAGE
414 is
415 begin
416   delete from CSP_EXCESS_RULES_TL T
417   where not exists
418     (select NULL
419     from CSP_EXCESS_RULES_B B
420     where B.EXCESS_RULE_ID = T.EXCESS_RULE_ID
421     );
422 
426       B.DESCRIPTION
423   update CSP_EXCESS_RULES_TL T set (
424       DESCRIPTION
425     ) = (select
427     from CSP_EXCESS_RULES_TL B
428     where B.EXCESS_RULE_ID = T.EXCESS_RULE_ID
429     and B.LANGUAGE = T.SOURCE_LANG)
430   where (
431       T.EXCESS_RULE_ID,
432       T.LANGUAGE
433   ) in (select
434       SUBT.EXCESS_RULE_ID,
435       SUBT.LANGUAGE
436     from CSP_EXCESS_RULES_TL SUBB, CSP_EXCESS_RULES_TL SUBT
437     where SUBB.EXCESS_RULE_ID = SUBT.EXCESS_RULE_ID
438     and SUBB.LANGUAGE = SUBT.SOURCE_LANG
439     and (SUBB.DESCRIPTION <> SUBT.DESCRIPTION
440       or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
441       or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
442   ));
443 
444   insert into CSP_EXCESS_RULES_TL (
445     EXCESS_RULE_ID,
446     CREATED_BY,
447     CREATION_DATE,
448     LAST_UPDATED_BY,
449     LAST_UPDATE_DATE,
450     LAST_UPDATE_LOGIN,
451     DESCRIPTION,
452     LANGUAGE,
453     SOURCE_LANG
454   ) select
455     B.EXCESS_RULE_ID,
456     B.CREATED_BY,
457     B.CREATION_DATE,
458     B.LAST_UPDATED_BY,
459     B.LAST_UPDATE_DATE,
460     B.LAST_UPDATE_LOGIN,
461     B.DESCRIPTION,
462     L.LANGUAGE_CODE,
463     B.SOURCE_LANG
464   from CSP_EXCESS_RULES_TL B, FND_LANGUAGES L
465   where L.INSTALLED_FLAG in ('I', 'B')
466   and B.LANGUAGE = userenv('LANG')
467   and not exists
468     (select NULL
469     from CSP_EXCESS_RULES_TL T
470     where T.EXCESS_RULE_ID = B.EXCESS_RULE_ID
471     and T.LANGUAGE = L.LANGUAGE_CODE);
472 end ADD_LANGUAGE;
473 
474 PROCEDURE Translate_Row
475 ( p_excess_rule_id     IN  NUMBER
476 , p_description          IN  VARCHAR2
477 , p_owner                IN  VARCHAR2
478 )
479 IS
480 l_user_id    NUMBER := 0;
481 BEGIN
482 
483   if p_owner = 'SEED' then
484     l_user_id := 1;
485   end if;
486 
487   UPDATE csp_excess_rules_tl
488     SET description = p_description
489       , last_update_date  = SYSDATE
490       , last_updated_by   = l_user_id
491       , last_update_login = 0
492       , source_lang       = userenv('LANG')
493     WHERE excess_rule_id = p_excess_rule_id
494       AND userenv('LANG') IN (language, source_lang);
495 
496 EXCEPTION
497   WHEN OTHERS THEN
498     IF FND_MSG_PUB.Check_Msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
499     THEN
500       FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, 'Translate_Row');
501     END IF;
502     RAISE;
503 
504 END Translate_Row;
505 
506 PROCEDURE Load_Row
507 ( p_excess_rule_id    IN  NUMBER
508 , p_description         IN  VARCHAR2
509 , p_owner               IN VARCHAR2
510 )
511 IS
512 
513 l_excess_rule_id      NUMBER;
514 l_user_id               NUMBER := 0;
515 
516 BEGIN
517 
518   -- assign user ID
519   if p_owner = 'SEED' then
520     l_user_id := 1; --SEED
521   end if;
522 
523   BEGIN
524     -- update row if present
525     Update_Row(
526           p_EXCESS_RULE_ID => p_excess_rule_id,
527           p_CREATED_BY     => l_user_id,
528           p_CREATION_DATE  => sysdate ,
529           p_LAST_UPDATED_BY => l_user_id,
530           p_LAST_UPDATE_DATE => sysdate,
531           p_LAST_UPDATE_LOGIN => 0,
532           p_EXCESS_RULE_NAME => FND_API.G_MISS_CHAR,
533           p_TOTAL_MAX_EXCESS => FND_API.G_MISS_NUM,
534           p_LINE_MAX_EXCESS => FND_API.G_MISS_NUM,
535           p_DAYS_SINCE_RECEIPT => FND_API.G_MISS_NUM,
536           p_TOTAL_EXCESS_VALUE => FND_API.G_MISS_NUM,
537           p_TOP_EXCESS_LINES => FND_API.G_MISS_NUM,
538           p_CATEGORY_SET_ID => FND_API.G_MISS_NUM,
539           p_CATEGORY_ID => FND_API.G_MISS_NUM,
540           p_ATTRIBUTE_CATEGORY =>      FND_API.G_MISS_CHAR,
541           p_ATTRIBUTE1 =>      FND_API.G_MISS_CHAR,
542           p_ATTRIBUTE2 =>      FND_API.G_MISS_CHAR,
543           p_ATTRIBUTE3 =>      FND_API.G_MISS_CHAR,
544           p_ATTRIBUTE4 =>      FND_API.G_MISS_CHAR,
545           p_ATTRIBUTE5 =>      FND_API.G_MISS_CHAR,
546           p_ATTRIBUTE6 =>      FND_API.G_MISS_CHAR,
547           p_ATTRIBUTE7 =>      FND_API.G_MISS_CHAR,
548           p_ATTRIBUTE8 =>      FND_API.G_MISS_CHAR,
549           p_ATTRIBUTE9 =>      FND_API.G_MISS_CHAR,
550           p_ATTRIBUTE10 =>      FND_API.G_MISS_CHAR,
551           p_ATTRIBUTE11 =>      FND_API.G_MISS_CHAR,
552           p_ATTRIBUTE12 =>      FND_API.G_MISS_CHAR,
553           p_ATTRIBUTE13 =>      FND_API.G_MISS_CHAR,
554           p_ATTRIBUTE14 =>      FND_API.G_MISS_CHAR,
555           p_ATTRIBUTE15 =>      FND_API.G_MISS_CHAR,
556           p_DESCRIPTION => p_description);
557   EXCEPTION
558     WHEN NO_DATA_FOUND THEN
559       -- insert row
560       Insert_Row(
561           px_EXCESS_RULE_ID => l_excess_rule_id,
562           p_CREATED_BY     => l_user_id,
563           p_CREATION_DATE  => sysdate ,
564           p_LAST_UPDATED_BY => l_user_id,
565           p_LAST_UPDATE_DATE => sysdate,
566           p_LAST_UPDATE_LOGIN => 0,
567           p_EXCESS_RULE_NAME => FND_API.G_MISS_CHAR,
568           p_TOTAL_MAX_EXCESS => FND_API.G_MISS_NUM,
569           p_LINE_MAX_EXCESS => FND_API.G_MISS_NUM,
570           p_DAYS_SINCE_RECEIPT => FND_API.G_MISS_NUM,
571           p_TOTAL_EXCESS_VALUE => FND_API.G_MISS_NUM,
572           p_TOP_EXCESS_LINES => FND_API.G_MISS_NUM,
573           p_CATEGORY_SET_ID => FND_API.G_MISS_NUM,
574           p_CATEGORY_ID => FND_API.G_MISS_NUM,
575           p_ATTRIBUTE_CATEGORY =>      FND_API.G_MISS_CHAR,
576           p_ATTRIBUTE1 =>      FND_API.G_MISS_CHAR,
577           p_ATTRIBUTE2 =>      FND_API.G_MISS_CHAR,
581           p_ATTRIBUTE6 =>      FND_API.G_MISS_CHAR,
578           p_ATTRIBUTE3 =>      FND_API.G_MISS_CHAR,
579           p_ATTRIBUTE4 =>      FND_API.G_MISS_CHAR,
580           p_ATTRIBUTE5 =>      FND_API.G_MISS_CHAR,
582           p_ATTRIBUTE7 =>      FND_API.G_MISS_CHAR,
583           p_ATTRIBUTE8 =>      FND_API.G_MISS_CHAR,
584           p_ATTRIBUTE9 =>      FND_API.G_MISS_CHAR,
585           p_ATTRIBUTE10 =>      FND_API.G_MISS_CHAR,
586           p_ATTRIBUTE11 =>      FND_API.G_MISS_CHAR,
587           p_ATTRIBUTE12 =>      FND_API.G_MISS_CHAR,
588           p_ATTRIBUTE13 =>      FND_API.G_MISS_CHAR,
589           p_ATTRIBUTE14 =>      FND_API.G_MISS_CHAR,
590           p_ATTRIBUTE15 =>      FND_API.G_MISS_CHAR,
591           p_DESCRIPTION => p_description);
592   END;
593 
594 EXCEPTION
595   WHEN OTHERS THEN
596     IF FND_MSG_PUB.Check_Msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
597     THEN
598       FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, 'Load_Row');
599     END IF;
600     RAISE;
601 
602 END Load_Row;
603 End CSP_EXCESS_RULES_B_PKG;