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.CSD_BULLETINS_PKG

Source


1 package body CSD_BULLETINS_PKG as
2 /* $Header: csdtbulb.pls 120.0.12010000.1 2008/12/11 01:05:10 swai noship $ */
3 
4 procedure INSERT_ROW (
5   PX_ROWID             in out nocopy VARCHAR2,
6   PX_BULLETIN_ID          in out nocopy NUMBER,
7   P_OBJECT_VERSION_NUMBER        in NUMBER,
8   P_CREATION_DATE                in DATE,
9   P_CREATED_BY                   in NUMBER,
10   P_LAST_UPDATE_DATE             in DATE,
11   P_LAST_UPDATED_BY              in NUMBER,
12   P_LAST_UPDATE_LOGIN            in NUMBER,
13   P_NAME                         in VARCHAR2,
14   P_DESCRIPTION                  in VARCHAR2,
15   P_BULLETIN_TYPE_CODE           in VARCHAR2,
16   P_ACTIVE_FROM                  in DATE,
17   P_ACTIVE_TO                    in DATE,
18   P_PUBLISHED_FLAG               in VARCHAR2,
19   P_ESCALATION_CODE              in VARCHAR2,
20   P_MANDATORY_FLAG               in VARCHAR2,
21   P_FREQUENCY_CODE               in VARCHAR2,
22   P_WF_ITEM_TYPE                 in VARCHAR2,
23   P_WF_PROCESS_NAME              in VARCHAR2,
24   P_ATTRIBUTE_CATEGORY           in VARCHAR2,
25   P_ATTRIBUTE1                   in VARCHAR2,
26   P_ATTRIBUTE2                   in VARCHAR2,
27   P_ATTRIBUTE3                   in VARCHAR2,
28   P_ATTRIBUTE4                   in VARCHAR2,
29   P_ATTRIBUTE5                   in VARCHAR2,
30   P_ATTRIBUTE6                   in VARCHAR2,
31   P_ATTRIBUTE7                   in VARCHAR2,
32   P_ATTRIBUTE8                   in VARCHAR2,
33   P_ATTRIBUTE9                   in VARCHAR2,
34   P_ATTRIBUTE10                  in VARCHAR2,
35   P_ATTRIBUTE11                  in VARCHAR2,
36   P_ATTRIBUTE12                  in VARCHAR2,
37   P_ATTRIBUTE13                  in VARCHAR2,
38   P_ATTRIBUTE14                  in VARCHAR2,
39   P_ATTRIBUTE15                  in VARCHAR2
40 ) is
41 
42   cursor C is select ROWID from CSD_BULLETINS_B
43     where BULLETIN_ID = PX_BULLETIN_ID
44     ;
45 
46 begin
47 
48   select CSD_BULLETINS_S1.nextval
49   into PX_BULLETIN_ID
50   from dual;
51 
52   insert into CSD_BULLETINS_B (
53     BULLETIN_ID,
54     OBJECT_VERSION_NUMBER,
55     CREATION_DATE,
56     CREATED_BY,
57     LAST_UPDATE_DATE,
58     LAST_UPDATED_BY,
59     LAST_UPDATE_LOGIN,
60     BULLETIN_TYPE_CODE,
61     ACTIVE_FROM,
62     ACTIVE_TO,
63     PUBLISHED_FLAG,
64     ESCALATION_CODE,
65     MANDATORY_FLAG,
66     FREQUENCY_CODE,
67     WF_ITEM_TYPE,
68     WF_PROCESS_NAME,
69     ATTRIBUTE_CATEGORY,
70     ATTRIBUTE1,
71     ATTRIBUTE2,
72     ATTRIBUTE3,
73     ATTRIBUTE4,
74     ATTRIBUTE5,
75     ATTRIBUTE6,
76     ATTRIBUTE7,
77     ATTRIBUTE8,
78     ATTRIBUTE9,
79     ATTRIBUTE10,
80     ATTRIBUTE11,
81     ATTRIBUTE12,
82     ATTRIBUTE13,
83     ATTRIBUTE14,
84     ATTRIBUTE15
85   ) values (
86     PX_BULLETIN_ID,
87     P_OBJECT_VERSION_NUMBER,
88     P_CREATION_DATE,
89     P_CREATED_BY,
90     P_LAST_UPDATE_DATE,
91     P_LAST_UPDATED_BY,
92     P_LAST_UPDATE_LOGIN,
93     P_BULLETIN_TYPE_CODE,
94     P_ACTIVE_FROM,
95     P_ACTIVE_TO,
96     P_PUBLISHED_FLAG,
97     P_ESCALATION_CODE,
98     P_MANDATORY_FLAG,
99     P_FREQUENCY_CODE,
100     P_WF_ITEM_TYPE,
101     P_WF_PROCESS_NAME,
102     P_ATTRIBUTE_CATEGORY,
103     P_ATTRIBUTE1,
104     P_ATTRIBUTE2,
105     P_ATTRIBUTE3,
106     P_ATTRIBUTE4,
107     P_ATTRIBUTE5,
108     P_ATTRIBUTE6,
109     P_ATTRIBUTE7,
110     P_ATTRIBUTE8,
111     P_ATTRIBUTE9,
112     P_ATTRIBUTE10,
113     P_ATTRIBUTE11,
114     P_ATTRIBUTE12,
115     P_ATTRIBUTE13,
116     P_ATTRIBUTE14,
117     P_ATTRIBUTE15
118   );
119 
120   insert into CSD_BULLETINS_TL (
121     BULLETIN_ID,
122     NAME,
123     DESCRIPTION,
124     CREATED_BY,
125     CREATION_DATE,
126     LAST_UPDATED_BY,
127     LAST_UPDATE_DATE,
128     LAST_UPDATE_LOGIN,
129     LANGUAGE,
130     SOURCE_LANG
131   ) select
132         PX_BULLETIN_ID,
133         P_NAME,
134         P_DESCRIPTION,
135         P_CREATED_BY,
136         P_CREATION_DATE,
137         P_LAST_UPDATED_BY,
138         P_LAST_UPDATE_DATE,
139         P_LAST_UPDATE_LOGIN,
140         L.LANGUAGE_CODE,
141         userenv('LANG')
142     from FND_LANGUAGES L
143    where L.INSTALLED_FLAG in ('I', 'B')
144      and not exists
145     (select NULL
146        from CSD_BULLETINS_TL T
147       where T.BULLETIN_ID = PX_BULLETIN_ID
148         and T.LANGUAGE = L.LANGUAGE_CODE);
149 
150   open c;
151   fetch c into PX_ROWID;
152   if (c%notfound) then
153     close c;
154     raise no_data_found;
155   end if;
156   close c;
157 
158 end INSERT_ROW;
159 
160 procedure LOCK_ROW (
161   P_BULLETIN_ID in NUMBER,
162   P_OBJECT_VERSION_NUMBER in NUMBER
163 ) is
164   cursor c is select
165       OBJECT_VERSION_NUMBER
166     from CSD_BULLETINS_B
167     where BULLETIN_ID = P_BULLETIN_ID
168     for update of BULLETIN_ID nowait;
169   recinfo c%rowtype;
170 
171 begin
172 
173   open c;
174   fetch c into recinfo;
175   if (c%notfound) then
176     close c;
177     fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
178     app_exception.raise_exception;
179   end if;
180   close c;
181 
182   if (recinfo.OBJECT_VERSION_NUMBER = P_OBJECT_VERSION_NUMBER) then
183     null;
184   else
185     fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
186     app_exception.raise_exception;
187   end if;
188 
189 /*
190   for tlinfo in c1 loop
191     if (tlinfo.BASELANG = 'Y') then
192       if (    ((tlinfo.DESCRIPTION = P_DESCRIPTION)
193                OR ((tlinfo.DESCRIPTION is null) AND (P_DESCRIPTION is null)))
194       ) then
195         null;
196       else
197         fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
198         app_exception.raise_exception;
199       end if;
200     end if;
201   end loop;
202 */
203 
204   return;
205 end LOCK_ROW;
206 
207 procedure UPDATE_ROW (
208   P_BULLETIN_ID in NUMBER,
209   P_OBJECT_VERSION_NUMBER        in NUMBER,
210   P_CREATION_DATE                in DATE,
211   P_CREATED_BY                   in NUMBER,
212   P_LAST_UPDATE_DATE             in DATE,
213   P_LAST_UPDATED_BY              in NUMBER,
214   P_LAST_UPDATE_LOGIN            in NUMBER,
215   P_NAME                         in VARCHAR2,
216   P_DESCRIPTION                  in VARCHAR2,
217   P_BULLETIN_TYPE_CODE           in VARCHAR2,
218   P_ACTIVE_FROM                  in DATE,
219   P_ACTIVE_TO                    in DATE,
220   P_PUBLISHED_FLAG               in VARCHAR2,
221   P_ESCALATION_CODE              in VARCHAR2,
222   P_MANDATORY_FLAG               in VARCHAR2,
223   P_FREQUENCY_CODE               in VARCHAR2,
224   P_WF_ITEM_TYPE                 in VARCHAR2,
225   P_WF_PROCESS_NAME              in VARCHAR2,
226   P_ATTRIBUTE_CATEGORY           in VARCHAR2,
227   P_ATTRIBUTE1                   in VARCHAR2,
228   P_ATTRIBUTE2                   in VARCHAR2,
229   P_ATTRIBUTE3                   in VARCHAR2,
230   P_ATTRIBUTE4                   in VARCHAR2,
231   P_ATTRIBUTE5                   in VARCHAR2,
232   P_ATTRIBUTE6                   in VARCHAR2,
233   P_ATTRIBUTE7                   in VARCHAR2,
234   P_ATTRIBUTE8                   in VARCHAR2,
235   P_ATTRIBUTE9                   in VARCHAR2,
236   P_ATTRIBUTE10                  in VARCHAR2,
237   P_ATTRIBUTE11                  in VARCHAR2,
238   P_ATTRIBUTE12                  in VARCHAR2,
239   P_ATTRIBUTE13                  in VARCHAR2,
240   P_ATTRIBUTE14                  in VARCHAR2,
241   P_ATTRIBUTE15                  in VARCHAR2
242 ) is
243 begin
244   update CSD_BULLETINS_B set
245          OBJECT_VERSION_NUMBER = decode( P_OBJECT_VERSION_NUMBER, FND_API.G_MISS_NUM, NULL, NULL, OBJECT_VERSION_NUMBER, P_OBJECT_VERSION_NUMBER)
246          ,CREATED_BY = decode( P_CREATED_BY, FND_API.G_MISS_NUM, NULL, NULL, CREATED_BY, P_CREATED_BY)
247          ,CREATION_DATE = decode( P_CREATION_DATE, FND_API.G_MISS_DATE, NULL, NULL, CREATION_DATE, P_CREATION_DATE)
248          ,LAST_UPDATED_BY = decode( P_LAST_UPDATED_BY, FND_API.G_MISS_NUM, NULL, NULL, LAST_UPDATED_BY, P_LAST_UPDATED_BY)
249          ,LAST_UPDATE_DATE = decode( P_LAST_UPDATE_DATE, FND_API.G_MISS_DATE, NULL, NULL, LAST_UPDATE_DATE, P_LAST_UPDATE_DATE)
250          ,LAST_UPDATE_LOGIN = decode( P_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, NULL, NULL, LAST_UPDATE_LOGIN, P_LAST_UPDATE_LOGIN)
251 
252          ,BULLETIN_TYPE_CODE = decode( P_BULLETIN_TYPE_CODE, FND_API.G_MISS_CHAR, NULL, NULL, BULLETIN_TYPE_CODE, P_BULLETIN_TYPE_CODE)
253          ,ACTIVE_FROM = decode( P_ACTIVE_FROM, FND_API.G_MISS_DATE, NULL, NULL, ACTIVE_FROM, P_ACTIVE_FROM)
254          ,ACTIVE_TO = decode( P_ACTIVE_TO, FND_API.G_MISS_DATE, NULL, NULL, ACTIVE_TO, P_ACTIVE_TO)
255          ,PUBLISHED_FLAG = decode( P_PUBLISHED_FLAG, FND_API.G_MISS_CHAR, NULL, NULL, PUBLISHED_FLAG, P_PUBLISHED_FLAG)
256          ,ESCALATION_CODE = decode( P_ESCALATION_CODE, FND_API.G_MISS_CHAR, NULL, NULL, ESCALATION_CODE, P_ESCALATION_CODE)
257          ,MANDATORY_FLAG = decode( P_MANDATORY_FLAG, FND_API.G_MISS_CHAR, NULL, NULL, MANDATORY_FLAG, P_MANDATORY_FLAG)
258 
259          ,FREQUENCY_CODE = decode( P_FREQUENCY_CODE, FND_API.G_MISS_CHAR, NULL, NULL, FREQUENCY_CODE, P_FREQUENCY_CODE)
260          ,WF_ITEM_TYPE = decode( P_WF_ITEM_TYPE, FND_API.G_MISS_CHAR, NULL, NULL, WF_ITEM_TYPE, P_WF_ITEM_TYPE)
261          ,WF_PROCESS_NAME = decode( P_WF_PROCESS_NAME, FND_API.G_MISS_CHAR, NULL, NULL, WF_PROCESS_NAME, P_WF_PROCESS_NAME)
262 
263          ,ATTRIBUTE_CATEGORY = decode( P_ATTRIBUTE_CATEGORY, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE_CATEGORY, P_ATTRIBUTE_CATEGORY)
264          ,ATTRIBUTE1 = decode( P_ATTRIBUTE1, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE1, P_ATTRIBUTE1)
265          ,ATTRIBUTE2 = decode( P_ATTRIBUTE2, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE2, P_ATTRIBUTE2)
266          ,ATTRIBUTE3 = decode( P_ATTRIBUTE3, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE3, P_ATTRIBUTE3)
267          ,ATTRIBUTE4 = decode( P_ATTRIBUTE4, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE4, P_ATTRIBUTE4)
268          ,ATTRIBUTE5 = decode( P_ATTRIBUTE5, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE5, P_ATTRIBUTE5)
269          ,ATTRIBUTE6 = decode( P_ATTRIBUTE6, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE6, P_ATTRIBUTE6)
270          ,ATTRIBUTE7 = decode( P_ATTRIBUTE7, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE7, P_ATTRIBUTE7)
271          ,ATTRIBUTE8 = decode( P_ATTRIBUTE8, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE8, P_ATTRIBUTE8)
272          ,ATTRIBUTE9 = decode( P_ATTRIBUTE9, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE9, P_ATTRIBUTE9)
273          ,ATTRIBUTE10 = decode( P_ATTRIBUTE10, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE10, P_ATTRIBUTE10)
274          ,ATTRIBUTE11 = decode( P_ATTRIBUTE11, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE11, P_ATTRIBUTE11)
275          ,ATTRIBUTE12 = decode( P_ATTRIBUTE12, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE12, P_ATTRIBUTE12)
276          ,ATTRIBUTE13 = decode( P_ATTRIBUTE13, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE13, P_ATTRIBUTE13)
277          ,ATTRIBUTE14 = decode( P_ATTRIBUTE14, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE14, P_ATTRIBUTE14)
278          ,ATTRIBUTE15 = decode( P_ATTRIBUTE15, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE15, P_ATTRIBUTE15)
279   where BULLETIN_ID = P_BULLETIN_ID;
280 
281   if (sql%notfound) then
282     raise no_data_found;
283   end if;
284 
285   update CSD_BULLETINS_TL set
286           CREATED_BY = decode( P_CREATED_BY, FND_API.G_MISS_NUM, NULL, NULL, CREATED_BY, P_CREATED_BY)
287          ,CREATION_DATE = decode( P_CREATION_DATE, FND_API.G_MISS_DATE, NULL, NULL, CREATION_DATE, P_CREATION_DATE)
288          ,LAST_UPDATED_BY = decode( P_LAST_UPDATED_BY, FND_API.G_MISS_NUM, NULL, NULL, LAST_UPDATED_BY, P_LAST_UPDATED_BY)
289          ,LAST_UPDATE_DATE = decode( P_LAST_UPDATE_DATE, FND_API.G_MISS_DATE, NULL, NULL, LAST_UPDATE_DATE, P_LAST_UPDATE_DATE)
290          ,LAST_UPDATE_LOGIN = decode( P_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, NULL, NULL, LAST_UPDATE_LOGIN, P_LAST_UPDATE_LOGIN)
291          ,NAME = decode( P_NAME, FND_API.G_MISS_CHAR, NULL, NULL, NAME, P_NAME)
292          ,DESCRIPTION = decode( P_DESCRIPTION, FND_API.G_MISS_CHAR, NULL, NULL, DESCRIPTION, P_DESCRIPTION)
293          ,SOURCE_LANG = userenv('LANG')
294   where BULLETIN_ID = P_BULLETIN_ID
295   and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
296 
297   if (sql%notfound) then
298     raise no_data_found;
299   end if;
300 
301 end UPDATE_ROW;
302 
303 procedure DELETE_ROW (
304   P_BULLETIN_ID in NUMBER
305 ) is
306 begin
307   delete from CSD_BULLETINS_TL
308   where BULLETIN_ID = P_BULLETIN_ID;
309 
310   if (sql%notfound) then
311     raise no_data_found;
312   end if;
313 
314   delete from CSD_BULLETINS_B
315   where BULLETIN_ID = P_BULLETIN_ID;
316 
317   if (sql%notfound) then
318     raise no_data_found;
319   end if;
320 end DELETE_ROW;
321 
322 procedure ADD_LANGUAGE
323 is
324 begin
325   delete from CSD_BULLETINS_TL T
326   where not exists
327     (select NULL
328     from CSD_BULLETINS_B B
329     where B.BULLETIN_ID = T.BULLETIN_ID
330     );
331 
332   update CSD_BULLETINS_TL T set (
333       NAME,
334       DESCRIPTION
335     ) = (select
336       B.NAME,
337       B.DESCRIPTION
338     from CSD_BULLETINS_TL B
339     where B.BULLETIN_ID = T.BULLETIN_ID
340     and B.LANGUAGE = T.SOURCE_LANG)
341   where (
342       T.BULLETIN_ID,
343       T.LANGUAGE
344   ) in (select
345       SUBT.BULLETIN_ID,
346       SUBT.LANGUAGE
347     from CSD_BULLETINS_TL SUBB, CSD_BULLETINS_TL SUBT
348     where SUBB.BULLETIN_ID = SUBT.BULLETIN_ID
349     and SUBB.LANGUAGE = SUBT.SOURCE_LANG
350     and (SUBB.NAME <> SUBT.NAME
351       or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
352       or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
353       or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
354   ));
355 
356   insert into CSD_BULLETINS_TL (
357     BULLETIN_ID,
358     CREATED_BY,
359     CREATION_DATE,
360     LAST_UPDATED_BY,
361     LAST_UPDATE_DATE,
362     LAST_UPDATE_LOGIN,
363     NAME,
364     DESCRIPTION,
365     LANGUAGE,
366     SOURCE_LANG
367   ) select /*+ ORDERED */
368     B.BULLETIN_ID,
369     B.CREATED_BY,
370     B.CREATION_DATE,
371     B.LAST_UPDATED_BY,
372     B.LAST_UPDATE_DATE,
373     B.LAST_UPDATE_LOGIN,
374     B.NAME,
375     B.DESCRIPTION,
376     L.LANGUAGE_CODE,
377     B.SOURCE_LANG
378   from CSD_BULLETINS_TL B, FND_LANGUAGES L
379   where L.INSTALLED_FLAG in ('I', 'B')
380   and B.LANGUAGE = userenv('LANG')
381   and not exists
382     (select NULL
383     from CSD_BULLETINS_TL T
384     where T.BULLETIN_ID = B.BULLETIN_ID
385     and T.LANGUAGE = L.LANGUAGE_CODE);
386 end ADD_LANGUAGE;
387 
388 
389 end CSD_BULLETINS_PKG;