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