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