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