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