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