1 package body GMO_INSTR_SET_INSTANCE_PKG as
2 /* $Header: GMOINSIB.pls 120.0 2005/06/29 04:22 shthakke noship $ */
3
4 procedure INSERT_ROW (
5 X_ROWID in out nocopy VARCHAR2,
6 X_INSTRUCTION_SET_ID in NUMBER,
7 X_INSTR_SET_STATUS in VARCHAR2,
8 X_INSTRUCTION_TYPE in VARCHAR2,
9 X_INSTR_SET_NAME in VARCHAR2,
10 X_ENTITY_NAME in VARCHAR2,
11 X_ENTITY_KEY in VARCHAR2,
12 X_ACKN_STATUS in VARCHAR2,
13 X_ORIG_SOURCE in VARCHAR2,
14 X_ORIG_SOURCE_ID in NUMBER,
15 X_INSTR_SET_DESC 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_SET_INSTANCE_B
23 where INSTRUCTION_SET_ID = X_INSTRUCTION_SET_ID
24 ;
25 begin
26 insert into GMO_INSTR_SET_INSTANCE_B (
27 INSTR_SET_STATUS,
28 INSTRUCTION_SET_ID,
29 INSTRUCTION_TYPE,
30 INSTR_SET_NAME,
31 ENTITY_NAME,
32 ENTITY_KEY,
33 ACKN_STATUS,
34 ORIG_SOURCE,
35 ORIG_SOURCE_ID,
36 CREATION_DATE,
37 CREATED_BY,
38 LAST_UPDATE_DATE,
39 LAST_UPDATED_BY,
40 LAST_UPDATE_LOGIN
41 ) values (
42 X_INSTR_SET_STATUS,
43 X_INSTRUCTION_SET_ID,
44 X_INSTRUCTION_TYPE,
45 X_INSTR_SET_NAME,
46 X_ENTITY_NAME,
47 X_ENTITY_KEY,
48 X_ACKN_STATUS,
49 X_ORIG_SOURCE,
50 X_ORIG_SOURCE_ID,
51 X_CREATION_DATE,
52 X_CREATED_BY,
53 X_LAST_UPDATE_DATE,
54 X_LAST_UPDATED_BY,
55 X_LAST_UPDATE_LOGIN
56 );
57
58 insert into GMO_INSTR_SET_INSTANCE_TL (
59 INSTRUCTION_SET_ID,
60 INSTR_SET_DESC,
61 CREATION_DATE,
62 CREATED_BY,
63 LAST_UPDATE_DATE,
64 LAST_UPDATED_BY,
65 LAST_UPDATE_LOGIN,
66 LANGUAGE,
67 SOURCE_LANG
68 ) select
69 X_INSTRUCTION_SET_ID,
70 X_INSTR_SET_DESC,
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_SET_INSTANCE_TL T
83 where T.INSTRUCTION_SET_ID = X_INSTRUCTION_SET_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_SET_ID in NUMBER,
98 X_INSTR_SET_STATUS in VARCHAR2,
99 X_INSTRUCTION_TYPE in VARCHAR2,
100 X_INSTR_SET_NAME in VARCHAR2,
101 X_ENTITY_NAME in VARCHAR2,
102 X_ENTITY_KEY in VARCHAR2,
103 X_ACKN_STATUS in VARCHAR2,
104 X_ORIG_SOURCE in VARCHAR2,
105 X_ORIG_SOURCE_ID in NUMBER,
106 X_INSTR_SET_DESC in VARCHAR2
107 ) is
108 cursor c is select
109 INSTR_SET_STATUS,
110 INSTRUCTION_TYPE,
111 INSTR_SET_NAME,
112 ENTITY_NAME,
113 ENTITY_KEY,
114 ACKN_STATUS,
115 ORIG_SOURCE,
116 ORIG_SOURCE_ID
117 from GMO_INSTR_SET_INSTANCE_B
118 where INSTRUCTION_SET_ID = X_INSTRUCTION_SET_ID
119 for update of INSTRUCTION_SET_ID nowait;
120 recinfo c%rowtype;
121
122 cursor c1 is select
123 INSTR_SET_DESC,
124 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
125 from GMO_INSTR_SET_INSTANCE_TL
126 where INSTRUCTION_SET_ID = X_INSTRUCTION_SET_ID
127 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
128 for update of INSTRUCTION_SET_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.INSTR_SET_STATUS = X_INSTR_SET_STATUS)
139 OR ((recinfo.INSTR_SET_STATUS is null) AND (X_INSTR_SET_STATUS is null)))
140 AND ((recinfo.INSTRUCTION_TYPE = X_INSTRUCTION_TYPE)
141 OR ((recinfo.INSTRUCTION_TYPE is null) AND (X_INSTRUCTION_TYPE is null)))
142 AND ((recinfo.INSTR_SET_NAME = X_INSTR_SET_NAME)
143 OR ((recinfo.INSTR_SET_NAME is null) AND (X_INSTR_SET_NAME is null)))
144 AND ((recinfo.ENTITY_NAME = X_ENTITY_NAME)
145 OR ((recinfo.ENTITY_NAME is null) AND (X_ENTITY_NAME is null)))
146 AND ((recinfo.ENTITY_KEY = X_ENTITY_KEY)
147 OR ((recinfo.ENTITY_KEY is null) AND (X_ENTITY_KEY is null)))
148 AND ((recinfo.ACKN_STATUS = X_ACKN_STATUS)
149 OR ((recinfo.ACKN_STATUS is null) AND (X_ACKN_STATUS is null)))
150 AND ((recinfo.ORIG_SOURCE = X_ORIG_SOURCE)
151 OR ((recinfo.ORIG_SOURCE is null) AND (X_ORIG_SOURCE is null)))
152 AND ((recinfo.ORIG_SOURCE_ID = X_ORIG_SOURCE_ID)
153 OR ((recinfo.ORIG_SOURCE_ID is null) AND (X_ORIG_SOURCE_ID 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
161 for tlinfo in c1 loop
162 if (tlinfo.BASELANG = 'Y') then
163 if ( ((tlinfo.INSTR_SET_DESC = X_INSTR_SET_DESC)
164 OR ((tlinfo.INSTR_SET_DESC is null) AND (X_INSTR_SET_DESC is null)))
165 ) then
166 null;
167 else
168 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
169 app_exception.raise_exception;
170 end if;
171 end if;
172 end loop;
173 return;
174 end LOCK_ROW;
175
176 procedure UPDATE_ROW (
177 X_INSTRUCTION_SET_ID in NUMBER,
178 X_INSTR_SET_STATUS in VARCHAR2,
179 X_INSTRUCTION_TYPE in VARCHAR2,
180 X_INSTR_SET_NAME in VARCHAR2,
181 X_ENTITY_NAME in VARCHAR2,
182 X_ENTITY_KEY in VARCHAR2,
183 X_ACKN_STATUS in VARCHAR2,
184 X_ORIG_SOURCE in VARCHAR2,
185 X_ORIG_SOURCE_ID in NUMBER,
186 X_INSTR_SET_DESC in VARCHAR2,
187 X_LAST_UPDATE_DATE in DATE,
188 X_LAST_UPDATED_BY in NUMBER,
189 X_LAST_UPDATE_LOGIN in NUMBER
190 ) is
191 begin
192 update GMO_INSTR_SET_INSTANCE_B set
193 INSTR_SET_STATUS = X_INSTR_SET_STATUS,
194 INSTRUCTION_TYPE = X_INSTRUCTION_TYPE,
195 INSTR_SET_NAME = X_INSTR_SET_NAME,
196 ENTITY_NAME = X_ENTITY_NAME,
197 ENTITY_KEY = X_ENTITY_KEY,
198 ACKN_STATUS = X_ACKN_STATUS,
199 ORIG_SOURCE = X_ORIG_SOURCE,
200 ORIG_SOURCE_ID = X_ORIG_SOURCE_ID,
201 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
202 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
203 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
204 where INSTRUCTION_SET_ID = X_INSTRUCTION_SET_ID;
205
206 if (sql%notfound) then
207 raise no_data_found;
208 end if;
209
210 update GMO_INSTR_SET_INSTANCE_TL set
211 INSTR_SET_DESC = X_INSTR_SET_DESC,
212 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
213 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
214 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
215 SOURCE_LANG = userenv('LANG')
216 where INSTRUCTION_SET_ID = X_INSTRUCTION_SET_ID
217 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
218
219 if (sql%notfound) then
220 raise no_data_found;
221 end if;
222 end UPDATE_ROW;
223
224 procedure DELETE_ROW (
225 X_INSTRUCTION_SET_ID in NUMBER
226 ) is
227 begin
228 delete from GMO_INSTR_SET_INSTANCE_TL
229 where INSTRUCTION_SET_ID = X_INSTRUCTION_SET_ID;
230
231 if (sql%notfound) then
232 raise no_data_found;
233 end if;
234
235 delete from GMO_INSTR_SET_INSTANCE_B
236 where INSTRUCTION_SET_ID = X_INSTRUCTION_SET_ID;
237
238 if (sql%notfound) then
239 raise no_data_found;
240 end if;
241 end DELETE_ROW;
242
243 procedure ADD_LANGUAGE
244 is
245 begin
246 delete from GMO_INSTR_SET_INSTANCE_TL T
247 where not exists
248 (select NULL
249 from GMO_INSTR_SET_INSTANCE_B B
250 where B.INSTRUCTION_SET_ID = T.INSTRUCTION_SET_ID
251 );
252
253 update GMO_INSTR_SET_INSTANCE_TL T set (
254 INSTR_SET_DESC
255 ) = (select
256 B.INSTR_SET_DESC
257 from GMO_INSTR_SET_INSTANCE_TL B
258 where B.INSTRUCTION_SET_ID = T.INSTRUCTION_SET_ID
259 and B.LANGUAGE = T.SOURCE_LANG)
260 where (
261 T.INSTRUCTION_SET_ID,
262 T.LANGUAGE
263 ) in (select
264 SUBT.INSTRUCTION_SET_ID,
265 SUBT.LANGUAGE
266 from GMO_INSTR_SET_INSTANCE_TL SUBB, GMO_INSTR_SET_INSTANCE_TL SUBT
267 where SUBB.INSTRUCTION_SET_ID = SUBT.INSTRUCTION_SET_ID
268 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
269 and (SUBB.INSTR_SET_DESC <> SUBT.INSTR_SET_DESC
270 or (SUBB.INSTR_SET_DESC is null and SUBT.INSTR_SET_DESC is not null)
271 or (SUBB.INSTR_SET_DESC is not null and SUBT.INSTR_SET_DESC is null)
272 ));
273
274 insert into GMO_INSTR_SET_INSTANCE_TL (
275 INSTRUCTION_SET_ID,
276 INSTR_SET_DESC,
277 CREATION_DATE,
278 CREATED_BY,
279 LAST_UPDATE_DATE,
280 LAST_UPDATED_BY,
281 LAST_UPDATE_LOGIN,
282 LANGUAGE,
283 SOURCE_LANG
284 ) select /*+ ORDERED */
285 B.INSTRUCTION_SET_ID,
286 B.INSTR_SET_DESC,
287 B.CREATION_DATE,
288 B.CREATED_BY,
289 B.LAST_UPDATE_DATE,
290 B.LAST_UPDATED_BY,
291 B.LAST_UPDATE_LOGIN,
292 L.LANGUAGE_CODE,
293 B.SOURCE_LANG
294 from GMO_INSTR_SET_INSTANCE_TL B, FND_LANGUAGES L
295 where L.INSTALLED_FLAG in ('I', 'B')
296 and B.LANGUAGE = userenv('LANG')
297 and not exists
298 (select NULL
299 from GMO_INSTR_SET_INSTANCE_TL T
300 where T.INSTRUCTION_SET_ID = B.INSTRUCTION_SET_ID
301 and T.LANGUAGE = L.LANGUAGE_CODE);
302 end ADD_LANGUAGE;
303
304 end GMO_INSTR_SET_INSTANCE_PKG;
[Home] [Help]