DBA Data[Home] [Help]

PACKAGE BODY: APPS.IEC_O_RELEASE_CTLS_PKG

Source


1 package body IEC_O_RELEASE_CTLS_PKG as
2 /* $Header: IECHRLCB.pls 115.11 2004/02/12 18:41:43 jcmoore ship $ */
3 procedure INSERT_ROW (
4   X_ROWID in out NOCOPY VARCHAR2,
5   X_RELEASE_CONTROL_ID in NUMBER,
6   X_SOURCE_TYPE_CODE in VARCHAR2,
7   X_OBJECT_VERSION_NUMBER in NUMBER,
8   X_RELEASE_CONTROL_NAME in VARCHAR2,
9   X_CREATION_DATE in DATE,
10   X_CREATED_BY in NUMBER,
11   X_LAST_UPDATE_DATE in DATE,
12   X_LAST_UPDATED_BY in NUMBER,
13   X_LAST_UPDATE_LOGIN in NUMBER,
14   X_PARENT_ID in NUMBER
15 ) is
16   cursor C is select ROWID from IEC_O_RELEASE_CTLS_B
17     where RELEASE_CONTROL_ID = X_RELEASE_CONTROL_ID
18     ;
19 begin
20   insert into IEC_O_RELEASE_CTLS_B (
21     RELEASE_CONTROL_ID,
22     SOURCE_TYPE_CODE,
23     OBJECT_VERSION_NUMBER,
24     CREATION_DATE,
25     CREATED_BY,
26     LAST_UPDATE_DATE,
27     LAST_UPDATED_BY,
28     LAST_UPDATE_LOGIN,
29     PARENT_ID
30   ) values (
31     X_RELEASE_CONTROL_ID,
32     X_SOURCE_TYPE_CODE,
33     X_OBJECT_VERSION_NUMBER,
34     X_CREATION_DATE,
35     X_CREATED_BY,
36     X_LAST_UPDATE_DATE,
37     X_LAST_UPDATED_BY,
38     X_LAST_UPDATE_LOGIN,
39     X_PARENT_ID
40   );
41 
42   insert into IEC_O_RELEASE_CTLS_TL (
43     RELEASE_CONTROL_ID,
44     RELEASE_CONTROL_NAME,
45     CREATED_BY,
46     CREATION_DATE,
47     LAST_UPDATED_BY,
48     LAST_UPDATE_DATE,
49     LAST_UPDATE_LOGIN,
50     OBJECT_VERSION_NUMBER,
51     LANGUAGE,
52     SOURCE_LANG
53   ) select
54     X_RELEASE_CONTROL_ID,
55     X_RELEASE_CONTROL_NAME,
56     X_CREATED_BY,
57     X_CREATION_DATE,
58     X_LAST_UPDATED_BY,
59     X_LAST_UPDATE_DATE,
60     X_LAST_UPDATE_LOGIN,
61     X_OBJECT_VERSION_NUMBER,
62     L.LANGUAGE_CODE,
63     userenv('LANG')
64   from FND_LANGUAGES L
65   where L.INSTALLED_FLAG in ('I', 'B')
66   and not exists
67     (select NULL
68     from IEC_O_RELEASE_CTLS_TL T
69     where T.RELEASE_CONTROL_ID = X_RELEASE_CONTROL_ID
70     and T.LANGUAGE = L.LANGUAGE_CODE);
71 
72   open c;
73   fetch c into X_ROWID;
74   if (c%notfound) then
75     close c;
76     raise no_data_found;
77   end if;
78   close c;
79 
80 end INSERT_ROW;
81 
82 procedure LOCK_ROW (
83   X_RELEASE_CONTROL_ID in NUMBER,
84   X_SOURCE_TYPE_CODE in VARCHAR2,
85   X_OBJECT_VERSION_NUMBER in NUMBER,
86   X_RELEASE_CONTROL_NAME in VARCHAR2
87 ) is
88   cursor c is select
89       SOURCE_TYPE_CODE,
90       OBJECT_VERSION_NUMBER
91     from IEC_O_RELEASE_CTLS_B
92     where RELEASE_CONTROL_ID = X_RELEASE_CONTROL_ID
93     for update of RELEASE_CONTROL_ID nowait;
94   recinfo c%rowtype;
95 
96   cursor c1 is select
97       RELEASE_CONTROL_NAME,
98       decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
99     from IEC_O_RELEASE_CTLS_TL
100     where RELEASE_CONTROL_ID = X_RELEASE_CONTROL_ID
101     and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
102     for update of RELEASE_CONTROL_ID nowait;
103 begin
104   open c;
105   fetch c into recinfo;
106   if (c%notfound) then
107     close c;
108     fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
109     app_exception.raise_exception;
110   end if;
111   close c;
112   if (    (recinfo.SOURCE_TYPE_CODE = X_SOURCE_TYPE_CODE)
113       AND (recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
114   ) then
115     null;
116   else
117     fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
118     app_exception.raise_exception;
119   end if;
120 
121   for tlinfo in c1 loop
122     if (tlinfo.BASELANG = 'Y') then
123       if (    (tlinfo.RELEASE_CONTROL_NAME = X_RELEASE_CONTROL_NAME)
124       ) then
125         null;
126       else
127         fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
128         app_exception.raise_exception;
129       end if;
130     end if;
131   end loop;
132   return;
133 end LOCK_ROW;
134 
135 procedure UPDATE_ROW (
136   X_RELEASE_CONTROL_ID in NUMBER,
137   X_SOURCE_TYPE_CODE in VARCHAR2,
138   X_OBJECT_VERSION_NUMBER in NUMBER,
139   X_RELEASE_CONTROL_NAME in VARCHAR2,
140   X_LAST_UPDATE_DATE in DATE,
141   X_LAST_UPDATED_BY in NUMBER,
142   X_LAST_UPDATE_LOGIN in NUMBER
143 ) is
144 begin
145   update IEC_O_RELEASE_CTLS_B set
146     SOURCE_TYPE_CODE = X_SOURCE_TYPE_CODE,
147     OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
148     LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
149     LAST_UPDATED_BY = X_LAST_UPDATED_BY,
150     LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
151   where RELEASE_CONTROL_ID = X_RELEASE_CONTROL_ID;
152 
153   if (sql%notfound) then
154     raise no_data_found;
155   end if;
156 
157   update IEC_O_RELEASE_CTLS_TL set
158     RELEASE_CONTROL_NAME = X_RELEASE_CONTROL_NAME,
159     LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
160     LAST_UPDATED_BY = X_LAST_UPDATED_BY,
161     LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
162     SOURCE_LANG = userenv('LANG')
163   where RELEASE_CONTROL_ID = X_RELEASE_CONTROL_ID
164   and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
165 
166   if (sql%notfound) then
167     raise no_data_found;
168   end if;
169 end UPDATE_ROW;
170 
171 procedure DELETE_ROW (
172   X_RELEASE_CONTROL_ID in NUMBER
173 ) is
174 begin
175   delete from IEC_O_RELEASE_CTLS_TL
176   where RELEASE_CONTROL_ID = X_RELEASE_CONTROL_ID;
177 
178   if (sql%notfound) then
179     raise no_data_found;
180   end if;
181 
182   delete from IEC_O_RELEASE_CTLS_B
183   where RELEASE_CONTROL_ID = X_RELEASE_CONTROL_ID;
184 
185   if (sql%notfound) then
186     raise no_data_found;
187   end if;
188 end DELETE_ROW;
189 
190 procedure ADD_LANGUAGE
191 is
192 begin
193   delete from IEC_O_RELEASE_CTLS_TL T
194   where not exists
195     (select NULL
196     from IEC_O_RELEASE_CTLS_B B
197     where B.RELEASE_CONTROL_ID = T.RELEASE_CONTROL_ID
198     );
199 
200   update IEC_O_RELEASE_CTLS_TL T set (
201       RELEASE_CONTROL_NAME
202     ) = (select
203       B.RELEASE_CONTROL_NAME
204     from IEC_O_RELEASE_CTLS_TL B
205     where B.RELEASE_CONTROL_ID = T.RELEASE_CONTROL_ID
206     and B.LANGUAGE = T.SOURCE_LANG)
207   where (
208       T.RELEASE_CONTROL_ID,
209       T.LANGUAGE
210   ) in (select
211       SUBT.RELEASE_CONTROL_ID,
212       SUBT.LANGUAGE
213     from IEC_O_RELEASE_CTLS_TL SUBB, IEC_O_RELEASE_CTLS_TL SUBT
214     where SUBB.RELEASE_CONTROL_ID = SUBT.RELEASE_CONTROL_ID
215     and SUBB.LANGUAGE = SUBT.SOURCE_LANG
216     and (SUBB.RELEASE_CONTROL_NAME <> SUBT.RELEASE_CONTROL_NAME
217   ));
218 
219   insert into IEC_O_RELEASE_CTLS_TL (
220     RELEASE_CONTROL_ID,
221     RELEASE_CONTROL_NAME,
222     CREATED_BY,
223     CREATION_DATE,
224     LAST_UPDATED_BY,
225     LAST_UPDATE_DATE,
226     LAST_UPDATE_LOGIN,
227     OBJECT_VERSION_NUMBER,
228     LANGUAGE,
229     SOURCE_LANG
230   ) select /*+ ORDERED */
231     B.RELEASE_CONTROL_ID,
232     B.RELEASE_CONTROL_NAME,
233     B.CREATED_BY,
234     B.CREATION_DATE,
235     B.LAST_UPDATED_BY,
236     B.LAST_UPDATE_DATE,
237     B.LAST_UPDATE_LOGIN,
238     B.OBJECT_VERSION_NUMBER,
239     L.LANGUAGE_CODE,
240     B.SOURCE_LANG
241   from IEC_O_RELEASE_CTLS_TL B, FND_LANGUAGES L
242   where L.INSTALLED_FLAG in ('I', 'B')
243   and B.LANGUAGE = userenv('LANG')
244   and not exists
245     (select NULL
246     from IEC_O_RELEASE_CTLS_TL T
247     where T.RELEASE_CONTROL_ID = B.RELEASE_CONTROL_ID
248     and T.LANGUAGE = L.LANGUAGE_CODE);
249 end ADD_LANGUAGE;
250 
251 end IEC_O_RELEASE_CTLS_PKG;