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