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