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