[Home] [Help]
PACKAGE BODY: APPS.MTL_RELATED_ITEMS_PKG
Source
1 PACKAGE BODY MTL_RELATED_ITEMS_PKG as
2 /* $Header: INVISDRB.pls 120.1 2006/02/23 22:24:18 swshukla noship $ */
3
4 PROCEDURE Insert_Row (X_Rowid IN OUT NOCOPY VARCHAR2,
5 X_Inventory_Item_Id NUMBER,
6 X_Organization_Id NUMBER,
7 X_Related_Item_Id NUMBER,
8 X_Relationship_Type_Id NUMBER,
9 X_Reciprocal_Flag VARCHAR2,
10 X_Planning_Enabled_Flag VARCHAR2,
11 X_Start_Date DATE,
12 X_End_Date DATE,
13 X_Attr_Context VARCHAR2,
14 X_Attr_Char1 VARCHAR2,
15 X_Attr_Char2 VARCHAR2,
16 X_Attr_Char3 VARCHAR2,
17 X_Attr_Char4 VARCHAR2,
18 X_Attr_Char5 VARCHAR2,
19 X_Attr_Char6 VARCHAR2,
20 X_Attr_Char7 VARCHAR2,
21 X_Attr_Char8 VARCHAR2,
22 X_Attr_Char9 VARCHAR2,
23 X_Attr_Char10 VARCHAR2,
24 X_Attr_Num1 NUMBER,
25 X_Attr_Num2 NUMBER,
26 X_Attr_Num3 NUMBER,
27 X_Attr_Num4 NUMBER,
28 X_Attr_Num5 NUMBER,
29 X_Attr_Num6 NUMBER,
30 X_Attr_Num7 NUMBER,
31 X_Attr_Num8 NUMBER,
32 X_Attr_Num9 NUMBER,
33 X_Attr_Num10 NUMBER,
34 X_Attr_Date1 DATE,
35 X_Attr_Date2 DATE,
36 X_Attr_Date3 DATE,
37 X_Attr_Date4 DATE,
38 X_Attr_Date5 DATE,
39 X_Attr_Date6 DATE,
40 X_Attr_Date7 DATE,
41 X_Attr_Date8 DATE,
42 X_Attr_Date9 DATE,
43 X_Attr_Date10 DATE,
44 X_Last_Update_Date DATE,
45 X_Last_Updated_By NUMBER,
46 X_Creation_Date DATE,
47 X_Created_By NUMBER,
48 X_Last_Update_Login NUMBER,
49 X_Object_Version_Number NUMBER
50 ) IS
51
52 CURSOR C IS SELECT rowid FROM MTL_RELATED_ITEMS
53 WHERE inventory_item_id = X_Inventory_Item_Id
54 AND organization_id = X_Organization_Id
55 AND related_item_id = X_Related_Item_Id
56 AND Relationship_Type_Id = X_Relationship_Type_Id
57 AND Reciprocal_Flag = X_Reciprocal_Flag ;
58
59 BEGIN
60
61 INSERT INTO MTL_RELATED_ITEMS(
62 inventory_item_id,
63 organization_id,
64 related_item_id,
65 relationship_type_id,
66 reciprocal_flag,
67 planning_enabled_flag,
68 Start_Date,
69 End_Date,
70 Attr_Context,
71 Attr_Char1,
72 Attr_Char2,
73 Attr_Char3,
74 Attr_Char4,
75 Attr_Char5,
76 Attr_Char6,
77 Attr_Char7,
78 Attr_Char8,
79 Attr_Char9,
80 Attr_Char10,
81 Attr_Num1,
82 Attr_Num2,
83 Attr_Num3,
84 Attr_Num4,
85 Attr_Num5,
86 Attr_Num6,
87 Attr_Num7,
88 Attr_Num8,
89 Attr_Num9,
90 Attr_Num10,
91 Attr_Date1,
92 Attr_Date2,
93 Attr_Date3,
94 Attr_Date4,
95 Attr_Date5,
96 Attr_Date6,
97 Attr_Date7,
98 Attr_Date8,
99 Attr_Date9,
100 Attr_Date10,
101 last_update_date,
102 last_updated_by,
103 creation_date,
104 created_by,
105 last_update_login,
106 object_version_number
107 ) VALUES (
108 X_Inventory_Item_Id,
109 X_Organization_Id,
110 X_Related_Item_Id,
111 X_Relationship_Type_Id,
112 X_Reciprocal_Flag,
113 X_Planning_Enabled_Flag,
114 X_Start_Date,
115 X_End_Date,
116 X_Attr_Context,
117 X_Attr_Char1,
118 X_Attr_Char2,
119 X_Attr_Char3,
120 X_Attr_Char4,
121 X_Attr_Char5,
122 X_Attr_Char6,
123 X_Attr_Char7,
124 X_Attr_Char8,
125 X_Attr_Char9,
126 X_Attr_Char10,
127 X_Attr_Num1,
128 X_Attr_Num2,
129 X_Attr_Num3,
130 X_Attr_Num4,
131 X_Attr_Num5,
132 X_Attr_Num6,
133 X_Attr_Num7,
134 X_Attr_Num8,
135 X_Attr_Num9,
136 X_Attr_Num10,
137 X_Attr_Date1,
138 X_Attr_Date2,
139 X_Attr_Date3,
140 X_Attr_Date4,
141 X_Attr_Date5,
142 X_Attr_Date6,
143 X_Attr_Date7,
144 X_Attr_Date8,
145 X_Attr_Date9,
146 X_Attr_Date10,
147 X_Last_Update_Date,
148 X_Last_Updated_By,
149 X_Creation_Date,
150 X_Created_By,
151 X_Last_Update_Login,
152 X_Object_Version_Number
153 );
154
155 OPEN C;
156 FETCH C INTO X_Rowid;
157 if (C%NOTFOUND) then
158 CLOSE C;
159 Raise NO_DATA_FOUND;
160 end if;
161 CLOSE C;
162
163 END Insert_Row;
164
165
166 PROCEDURE Lock_Row (X_Rowid VARCHAR2,
167 X_Inventory_Item_Id NUMBER,
168 X_Organization_Id NUMBER,
169 X_Related_Item_Id NUMBER,
170 X_Relationship_Type_Id NUMBER,
171 X_Reciprocal_Flag VARCHAR2,
172 X_Planning_Enabled_Flag VARCHAR2,
173 X_Start_Date DATE,
174 X_End_Date DATE,
175 X_Attr_Context VARCHAR2,
176 X_Attr_Char1 VARCHAR2,
177 X_Attr_Char2 VARCHAR2,
178 X_Attr_Char3 VARCHAR2,
179 X_Attr_Char4 VARCHAR2,
180 X_Attr_Char5 VARCHAR2,
181 X_Attr_Char6 VARCHAR2,
182 X_Attr_Char7 VARCHAR2,
183 X_Attr_Char8 VARCHAR2,
184 X_Attr_Char9 VARCHAR2,
185 X_Attr_Char10 VARCHAR2,
186 X_Attr_Num1 NUMBER,
187 X_Attr_Num2 NUMBER,
188 X_Attr_Num3 NUMBER,
189 X_Attr_Num4 NUMBER,
190 X_Attr_Num5 NUMBER,
191 X_Attr_Num6 NUMBER,
192 X_Attr_Num7 NUMBER,
193 X_Attr_Num8 NUMBER,
194 X_Attr_Num9 NUMBER,
195 X_Attr_Num10 NUMBER,
196 X_Attr_Date1 DATE,
197 X_Attr_Date2 DATE,
198 X_Attr_Date3 DATE,
199 X_Attr_Date4 DATE,
200 X_Attr_Date5 DATE,
201 X_Attr_Date6 DATE,
202 X_Attr_Date7 DATE,
203 X_Attr_Date8 DATE,
204 X_Attr_Date9 DATE,
205 X_Attr_Date10 DATE
206 ) IS
207
208 CURSOR C IS
209 SELECT *
210 FROM MTL_RELATED_ITEMS
211 WHERE rowid = X_Rowid
212 FOR UPDATE of Inventory_Item_Id NOWAIT;
213
214 Recinfo C%ROWTYPE;
215
216 BEGIN
217
218 OPEN C;
219 FETCH C INTO Recinfo;
220 if (C%NOTFOUND) then
221 CLOSE C;
222 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
223 APP_EXCEPTION.Raise_Exception;
224 end if;
225 CLOSE C;
226
227 if ( (Recinfo.inventory_item_id = X_Inventory_Item_Id)
228 AND (Recinfo.organization_id = X_Organization_Id)
229 AND (Recinfo.related_item_id = X_Related_Item_Id)
230 AND (Recinfo.relationship_type_id = X_Relationship_Type_Id)
231 AND (Recinfo.reciprocal_flag = X_Reciprocal_Flag)
232 AND (nvl(Recinfo.planning_enabled_flag,'N') = nvl(X_planning_enabled_Flag,'N'))
233 AND (Recinfo.Start_Date = X_Start_Date
234 OR ((Recinfo.Start_Date IS NULL)
235 AND (X_Start_Date IS NULL)))
236 AND (Recinfo.End_Date = X_End_Date
237 OR ((Recinfo.End_Date IS NULL)
238 AND (X_End_Date IS NULL)))
239 AND (Recinfo.Attr_Context = X_Attr_Context
240 OR ((Recinfo.Attr_Context IS NULL)
241 AND (X_Attr_Context IS NULL)))
242 AND (Recinfo.Attr_Char1 = X_Attr_Char1
243 OR ((Recinfo.Attr_Char1 IS NULL)
244 AND (X_Attr_Char1 IS NULL)))
245 AND (Recinfo.Attr_Char2 = X_Attr_Char2
246 OR ((Recinfo.Attr_Char2 IS NULL)
247 AND (X_Attr_Char2 IS NULL)))
248 AND (Recinfo.Attr_Char3 = X_Attr_Char3
249 OR ((Recinfo.Attr_Char3 IS NULL)
250 AND (X_Attr_Char3 IS NULL)))
251 AND (Recinfo.Attr_Char4 = X_Attr_Char4
252 OR ((Recinfo.Attr_Char4 IS NULL)
253 AND (X_Attr_Char4 IS NULL)))
254 AND (Recinfo.Attr_Char5 = X_Attr_Char5
255 OR ((Recinfo.Attr_Char5 IS NULL)
256 AND (X_Attr_Char5 IS NULL)))
257 AND (Recinfo.Attr_Char6 = X_Attr_Char6
258 OR ((Recinfo.Attr_Char6 IS NULL)
259 AND (X_Attr_Char6 IS NULL)))
260 AND (Recinfo.Attr_Char7 = X_Attr_Char7
261 OR ((Recinfo.Attr_Char7 IS NULL)
262 AND (X_Attr_Char7 IS NULL)))
263 AND (Recinfo.Attr_Char8 = X_Attr_Char8
264 OR ((Recinfo.Attr_Char8 IS NULL)
265 AND (X_Attr_Char8 IS NULL)))
266 AND (Recinfo.Attr_Char9 = X_Attr_Char9
267 OR ((Recinfo.Attr_Char9 IS NULL)
268 AND (X_Attr_Char9 IS NULL)))
269 AND (Recinfo.Attr_Char10 = X_Attr_Char10
270 OR ((Recinfo.Attr_Char10 IS NULL)
271 AND (X_Attr_Char10 IS NULL)))
272 AND (Recinfo.Attr_Num1 = X_Attr_Num1
273 OR ((Recinfo.Attr_Num1 IS NULL)
274 AND (X_Attr_Num1 IS NULL)))
275 AND (Recinfo.Attr_Num2 = X_Attr_Num2
276 OR ((Recinfo.Attr_Num2 IS NULL)
277 AND (X_Attr_Num2 IS NULL)))
278 AND (Recinfo.Attr_Num3 = X_Attr_Num3
279 OR ((Recinfo.Attr_Num3 IS NULL)
280 AND (X_Attr_Num3 IS NULL)))
281 AND (Recinfo.Attr_Num4 = X_Attr_Num4
282 OR ((Recinfo.Attr_Num4 IS NULL)
283 AND (X_Attr_Num4 IS NULL)))
284 AND (Recinfo.Attr_Num5 = X_Attr_Num5
285 OR ((Recinfo.Attr_Num5 IS NULL)
286 AND (X_Attr_Num5 IS NULL)))
287 AND (Recinfo.Attr_Num6 = X_Attr_Num6
288 OR ((Recinfo.Attr_Num6 IS NULL)
289 AND (X_Attr_Num6 IS NULL)))
290 AND (Recinfo.Attr_Num7 = X_Attr_Num7
291 OR ((Recinfo.Attr_Num7 IS NULL)
292 AND (X_Attr_Num7 IS NULL)))
293 AND (Recinfo.Attr_Num8 = X_Attr_Num8
294 OR ((Recinfo.Attr_Num8 IS NULL)
295 AND (X_Attr_Num8 IS NULL)))
296 AND (Recinfo.Attr_Num9 = X_Attr_Num9
297 OR ((Recinfo.Attr_Num9 IS NULL)
298 AND (X_Attr_Num9 IS NULL)))
299 AND (Recinfo.Attr_Num10 = X_Attr_Num10
300 OR ((Recinfo.Attr_Num10 IS NULL)
301 AND (X_Attr_Num10 IS NULL)))
302 AND (Recinfo.Attr_Date1 = X_Attr_Date1
303 OR ((Recinfo.Attr_Date1 IS NULL) AND (X_Attr_Date1 IS NULL)))
304 AND (Recinfo.Attr_Date2 = X_Attr_Date2
305 OR ((Recinfo.Attr_Date2 IS NULL) AND (X_Attr_Date2 IS NULL)))
306 AND (Recinfo.Attr_Date3 = X_Attr_Date3
307 OR ((Recinfo.Attr_Date3 IS NULL) AND (X_Attr_Date3 IS NULL)))
308 AND (Recinfo.Attr_Date4 = X_Attr_Date4
309 OR ((Recinfo.Attr_Date4 IS NULL) AND (X_Attr_Date4 IS NULL)))
310 AND (Recinfo.Attr_Date5 = X_Attr_Date5
311 OR ((Recinfo.Attr_Date5 IS NULL) AND (X_Attr_Date5 IS NULL)))
312 AND (Recinfo.Attr_Date6 = X_Attr_Date6
313 OR ((Recinfo.Attr_Date6 IS NULL) AND (X_Attr_Date6 IS NULL)))
314 AND (Recinfo.Attr_Date7 = X_Attr_Date7
315 OR ((Recinfo.Attr_Date7 IS NULL) AND (X_Attr_Date7 IS NULL)))
316 AND (Recinfo.Attr_Date8 = X_Attr_Date8
317 OR ((Recinfo.Attr_Date8 IS NULL) AND (X_Attr_Date8 IS NULL)))
318 AND (Recinfo.Attr_Date9 = X_Attr_Date9
319 OR ((Recinfo.Attr_Date9 IS NULL) AND (X_Attr_Date9 IS NULL)))
320 AND (Recinfo.Attr_Date10 = X_Attr_Date10
321 OR ((Recinfo.Attr_Date10 IS NULL) AND (X_Attr_Date10 IS NULL)))
322 ) then
323 return;
324 else
325 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
326 APP_EXCEPTION.Raise_Exception;
327 end if;
328
329 END Lock_Row;
330
331
332 PROCEDURE Update_Row (X_Rowid VARCHAR2,
333 X_Inventory_Item_Id NUMBER,
334 X_Organization_Id NUMBER,
335 X_Related_Item_Id NUMBER,
336 X_Relationship_Type_Id NUMBER,
337 X_Reciprocal_Flag VARCHAR2,
338 X_Planning_Enabled_Flag VARCHAR2,
339 X_Start_Date DATE,
340 X_End_Date DATE,
341 X_Attr_Context VARCHAR2,
342 X_Attr_Char1 VARCHAR2,
343 X_Attr_Char2 VARCHAR2,
344 X_Attr_Char3 VARCHAR2,
345 X_Attr_Char4 VARCHAR2,
346 X_Attr_Char5 VARCHAR2,
347 X_Attr_Char6 VARCHAR2,
351 X_Attr_Char10 VARCHAR2,
348 X_Attr_Char7 VARCHAR2,
349 X_Attr_Char8 VARCHAR2,
350 X_Attr_Char9 VARCHAR2,
352 X_Attr_Num1 NUMBER,
353 X_Attr_Num2 NUMBER,
354 X_Attr_Num3 NUMBER,
355 X_Attr_Num4 NUMBER,
356 X_Attr_Num5 NUMBER,
357 X_Attr_Num6 NUMBER,
358 X_Attr_Num7 NUMBER,
359 X_Attr_Num8 NUMBER,
360 X_Attr_Num9 NUMBER,
361 X_Attr_Num10 NUMBER,
362 X_Attr_Date1 DATE,
363 X_Attr_Date2 DATE,
364 X_Attr_Date3 DATE,
365 X_Attr_Date4 DATE,
366 X_Attr_Date5 DATE,
367 X_Attr_Date6 DATE,
368 X_Attr_Date7 DATE,
369 X_Attr_Date8 DATE,
370 X_Attr_Date9 DATE,
371 X_Attr_Date10 DATE,
372 X_Last_Update_Date DATE,
373 X_Last_Updated_By NUMBER,
374 X_Last_Update_Login NUMBER
375
376 ) IS
377 BEGIN
378
379 UPDATE MTL_RELATED_ITEMS
380 SET
381 inventory_item_id = X_Inventory_Item_Id,
382 organization_id = X_Organization_Id,
383 related_item_id = X_Related_Item_Id,
384 relationship_type_id = X_Relationship_Type_Id,
385 reciprocal_flag = X_Reciprocal_Flag,
386 planning_enabled_flag = X_planning_enabled_flag,
387 Start_Date = X_Start_Date,
388 End_Date = X_End_Date,
389 Attr_Context = X_Attr_Context,
390 Attr_Char1 = X_Attr_Char1,
391 Attr_Char2 = X_Attr_Char2,
392 Attr_Char3 = X_Attr_Char3,
393 Attr_Char4 = X_Attr_Char4,
394 Attr_Char5 = X_Attr_Char5,
395 Attr_Char6 = X_Attr_Char6,
396 Attr_Char7 = X_Attr_Char7,
397 Attr_Char8 = X_Attr_Char8,
398 Attr_Char9 = X_Attr_Char9,
399 Attr_Char10 = X_Attr_Char10,
400 Attr_Num1 = X_Attr_Num1,
401 Attr_Num2 = X_Attr_Num2,
402 Attr_Num3 = X_Attr_Num3,
403 Attr_Num4 = X_Attr_Num4,
404 Attr_Num5 = X_Attr_Num5,
405 Attr_Num6 = X_Attr_Num6,
406 Attr_Num7 = X_Attr_Num7,
407 Attr_Num8 = X_Attr_Num8,
408 Attr_Num9 = X_Attr_Num9,
409 Attr_Num10 = X_Attr_Num10,
410 Attr_Date1 = X_Attr_Date1,
411 Attr_Date2 = X_Attr_Date2,
412 Attr_Date3 = X_Attr_Date3,
413 Attr_Date4 = X_Attr_Date4,
414 Attr_Date5 = X_Attr_Date5,
415 Attr_Date6 = X_Attr_Date6,
416 Attr_Date7 = X_Attr_Date7,
417 Attr_Date8 = X_Attr_Date8,
418 Attr_Date9 = X_Attr_Date9,
419 Attr_Date10 = X_Attr_Date10,
420 last_update_date = X_Last_Update_Date,
421 last_update_login = X_Last_Update_Login,
422 last_updated_by = X_Last_Updated_By
423 WHERE rowid = X_Rowid;
424
425 if (SQL%NOTFOUND) then
426 Raise NO_DATA_FOUND;
427 end if;
428
429 END Update_Row;
430
431 PROCEDURE Delete_Row(X_Rowid VARCHAR2) IS
432 BEGIN
433 DELETE FROM MTL_RELATED_ITEMS
434 WHERE rowid = X_Rowid;
435
436 if (SQL%NOTFOUND) then
437 Raise NO_DATA_FOUND;
438 end if;
439
440 END Delete_Row;
441
442
443 END MTL_RELATED_ITEMS_PKG;