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_FLOW_STATUSES_PKG

Source


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