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