[Home] [Help]
PACKAGE BODY: APPS.INVICXRF
Source
1 PACKAGE BODY INVICXRF as
2 /* $Header: INVICXRB.pls 120.1 2005/06/30 06:43:51 appldev ship $ */
3
4 PROCEDURE Insert_Row (X_Rowid OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
5 X_Customer_Item_Id NUMBER,
6 X_Inventory_Item_Id NUMBER,
7 X_Master_Organization_Id NUMBER,
8 X_Rank NUMBER,
9 X_Inactive_Flag VARCHAR2,
10 X_Last_Update_Date DATE,
11 X_Last_Updated_By NUMBER,
12 X_Creation_Date DATE,
13 X_Created_By NUMBER,
14 X_Last_Update_Login NUMBER,
15 X_Attribute_Category VARCHAR2,
16 X_Attribute1 VARCHAR2,
17 X_Attribute2 VARCHAR2,
18 X_Attribute3 VARCHAR2,
19 X_Attribute4 VARCHAR2,
20 X_Attribute5 VARCHAR2,
21 X_Attribute6 VARCHAR2,
22 X_Attribute7 VARCHAR2,
23 X_Attribute8 VARCHAR2,
24 X_Attribute9 VARCHAR2,
25 X_Attribute10 VARCHAR2,
26 X_Attribute11 VARCHAR2,
27 X_Attribute12 VARCHAR2,
28 X_Attribute13 VARCHAR2,
29 X_Attribute14 VARCHAR2,
30 X_Attribute15 VARCHAR2
31 ) IS
32
33 CURSOR C IS SELECT rowid FROM mtl_customer_item_xrefs
34 WHERE customer_item_id = X_Customer_Item_Id
35 AND inventory_item_id = X_Inventory_Item_Id
36 AND master_organization_id = X_Master_Organization_Id;
37
38 BEGIN
39
40 INSERT INTO mtl_customer_item_xrefs(
41 Customer_Item_Id,
42 Inventory_Item_Id,
43 Master_Organization_Id,
44 Preference_Number,
45 Inactive_Flag,
46 Last_Update_Date,
47 Last_Updated_By,
48 Creation_Date,
49 Created_By,
50 Last_Update_Login,
51 Attribute_Category,
52 Attribute1,
53 Attribute2,
54 Attribute3,
55 Attribute4,
56 Attribute5,
57 Attribute6,
58 Attribute7,
59 Attribute8,
60 Attribute9,
61 Attribute10,
62 Attribute11,
63 Attribute12,
64 Attribute13,
65 Attribute14,
66 Attribute15
67 ) VALUES (
68 X_Customer_Item_Id,
69 X_Inventory_Item_Id,
70 X_Master_Organization_Id,
71 X_Rank,
72 X_Inactive_Flag,
73 X_Last_Update_Date,
74 X_Last_Updated_By,
75 X_Creation_Date,
76 X_Created_By,
77 X_Last_Update_Login,
78 X_Attribute_Category,
79 X_Attribute1,
80 X_Attribute2,
81 X_Attribute3,
82 X_Attribute4,
83 X_Attribute5,
84 X_Attribute6,
85 X_Attribute7,
86 X_Attribute8,
87 X_Attribute9,
88 X_Attribute10,
89 X_Attribute11,
90 X_Attribute12,
91 X_Attribute13,
92 X_Attribute14,
93 X_Attribute15
94 );
95
96 OPEN C;
97 FETCH C INTO X_Rowid;
98 IF (C%NOTFOUND) THEN
99 CLOSE C;
100 RAISE NO_DATA_FOUND;
101 END IF;
102 CLOSE C;
103 END Insert_Row;
104
105
106 PROCEDURE Lock_Row (X_Rowid VARCHAR2,
107 X_Customer_Item_Id NUMBER,
108 X_Inventory_Item_Id NUMBER,
109 X_Master_Organization_Id NUMBER,
110 X_Rank NUMBER,
111 X_Inactive_Flag VARCHAR2,
112 X_Last_Update_Date DATE,
113 X_Last_Updated_By NUMBER,
114 X_Creation_Date DATE,
115 X_Created_By NUMBER,
116 X_Last_Update_Login NUMBER,
117 X_Attribute_Category VARCHAR2,
118 X_Attribute1 VARCHAR2,
119 X_Attribute2 VARCHAR2,
120 X_Attribute3 VARCHAR2,
121 X_Attribute4 VARCHAR2,
122 X_Attribute5 VARCHAR2,
123 X_Attribute6 VARCHAR2,
124 X_Attribute7 VARCHAR2,
125 X_Attribute8 VARCHAR2,
126 X_Attribute9 VARCHAR2,
127 X_Attribute10 VARCHAR2,
128 X_Attribute11 VARCHAR2,
129 X_Attribute12 VARCHAR2,
130 X_Attribute13 VARCHAR2,
131 X_Attribute14 VARCHAR2,
132 X_Attribute15 VARCHAR2
133 ) IS
134
135 CURSOR C IS
136
137 SELECT *
138 FROM mtl_customer_item_xrefs
139 WHERE rowid = X_Rowid
140 FOR UPDATE of Customer_Item_Id, Inventory_Item_Id,
141 Master_Organization_Id NOWAIT;
142
143 Recinfo C%ROWTYPE;
144
145 BEGIN
146 OPEN C;
147 FETCH C INTO Recinfo;
148
149 IF (C%NOTFOUND) THEN
150
151 CLOSE C;
152 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
153 APP_EXCEPTION.Raise_Exception;
154
155 END IF;
156
157 CLOSE C;
158
159 IF (
160 (Recinfo.inventory_item_id = X_Inventory_Item_Id)
161 AND (Recinfo.customer_item_id = X_Customer_Item_Id )
162 AND (Recinfo.master_organization_id = X_Master_Organization_Id)
163 AND (Recinfo.preference_number = X_Rank)
164 AND (Recinfo.last_updated_by = X_Last_Updated_By)
165 AND (Recinfo.last_update_date = X_Last_Update_Date)
166 AND (Recinfo.creation_date = X_Creation_Date)
167 AND (Recinfo.created_by = X_Created_By)
168 AND (Recinfo.inactive_flag = X_Inactive_Flag)
169 AND ( (Recinfo.attribute1 = X_Attribute1)
170 OR ( (Recinfo.attribute1 IS NULL)
171 AND (X_Attribute1 IS NULL)))
172 AND ( (Recinfo.attribute2 = X_Attribute2)
173 OR ( (Recinfo.attribute2 IS NULL)
174 AND (X_Attribute2 IS NULL)))
175 AND ( (Recinfo.attribute3 = X_Attribute3)
176 OR ( (Recinfo.attribute3 IS NULL)
177 AND (X_Attribute3 IS NULL)))
178 AND ( (Recinfo.attribute4 = X_Attribute4)
179 OR ( (Recinfo.attribute4 IS NULL)
180 AND (X_Attribute4 IS NULL)))
181 AND ( (Recinfo.attribute5 = X_Attribute5)
182 OR ( (Recinfo.attribute5 IS NULL)
183 AND (X_Attribute5 IS NULL)))
184 AND ( (Recinfo.attribute6 = X_Attribute6)
185 OR ( (Recinfo.attribute6 IS NULL)
186 AND (X_Attribute6 IS NULL)))
187 AND ( (Recinfo.attribute7 = X_Attribute7)
188 OR ( (Recinfo.attribute7 IS NULL)
189 AND (X_Attribute7 IS NULL)))
190 AND ( (Recinfo.attribute8 = X_Attribute8)
191 OR ( (Recinfo.attribute8 IS NULL)
192 AND (X_Attribute8 IS NULL)))
193 AND ( (Recinfo.attribute9 = X_Attribute9)
194 OR ( (Recinfo.attribute9 IS NULL)
195 AND (X_Attribute9 IS NULL)))
196 AND ( (Recinfo.attribute10 = X_Attribute10)
197 OR ( (Recinfo.attribute10 IS NULL)
198 AND (X_Attribute10 IS NULL)))
199 AND ( (Recinfo.attribute11 = X_Attribute11)
200 OR ( (Recinfo.attribute11 IS NULL)
201 AND (X_Attribute11 IS NULL)))
202 AND ( (Recinfo.attribute12 = X_Attribute12)
203 OR ( (Recinfo.attribute12 IS NULL)
204 AND (X_Attribute12 IS NULL)))
205 AND ( (Recinfo.attribute13 = X_Attribute13)
206 OR ( (Recinfo.attribute13 IS NULL)
207 AND (X_Attribute13 IS NULL)))
208 AND ( (Recinfo.attribute14 = X_Attribute14)
209 OR ( (Recinfo.attribute14 IS NULL)
210 AND (X_Attribute14 IS NULL)))
211 AND ( (Recinfo.attribute15 = X_Attribute15)
212 OR ( (Recinfo.attribute15 IS NULL)
213 AND (X_Attribute15 IS NULL)))
214 AND ( (Recinfo.attribute_category = X_Attribute_Category)
215 OR ( (Recinfo.attribute_category IS NULL)
216 AND (X_Attribute_Category IS NULL)))
217
218 ) THEN
219
220 RETURN;
221
222 ELSE
223
224 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
225 APP_EXCEPTION.Raise_Exception;
226
227 END IF;
228
229 END Lock_Row;
230
231
232 PROCEDURE Update_Row (X_Rowid VARCHAR2,
233 X_Customer_Item_Id NUMBER,
234 X_Inventory_Item_Id NUMBER,
235 X_Master_Organization_Id NUMBER,
236 X_Rank NUMBER,
237 X_Inactive_Flag VARCHAR2,
238 X_Last_Update_Date DATE,
239 X_Last_Updated_By NUMBER,
240 X_Creation_Date DATE,
241 X_Created_By NUMBER,
242 X_Last_Update_Login NUMBER,
243 X_Attribute_Category VARCHAR2,
244 X_Attribute1 VARCHAR2,
245 X_Attribute2 VARCHAR2,
246 X_Attribute3 VARCHAR2,
247 X_Attribute4 VARCHAR2,
248 X_Attribute5 VARCHAR2,
249 X_Attribute6 VARCHAR2,
250 X_Attribute7 VARCHAR2,
251 X_Attribute8 VARCHAR2,
252 X_Attribute9 VARCHAR2,
253 X_Attribute10 VARCHAR2,
254 X_Attribute11 VARCHAR2,
255 X_Attribute12 VARCHAR2,
256 X_Attribute13 VARCHAR2,
257 X_Attribute14 VARCHAR2,
258 X_Attribute15 VARCHAR2
259 ) IS
260 BEGIN
261
262 UPDATE mtl_customer_item_xrefs
263 SET
264
265 Customer_Item_Id = X_Customer_Item_Id,
266 Inventory_Item_Id = X_Inventory_Item_Id,
267 Master_Organization_Id = X_Master_Organization_Id,
268 Preference_Number = X_Rank,
269 Inactive_Flag = X_Inactive_Flag,
270 Last_Update_Date = X_Last_Update_Date,
271 Last_Updated_By = X_Last_Updated_By,
272 Creation_Date = X_Creation_Date,
273 Created_By = X_Created_By,
274 Last_Update_Login = X_Last_Update_Login,
275 Attribute_Category = X_Attribute_Category,
276 Attribute1 = X_Attribute1,
277 Attribute2 = X_Attribute2,
278 Attribute3 = X_Attribute3,
279 Attribute4 = X_Attribute4,
280 Attribute5 = X_Attribute5,
281 Attribute6 = X_Attribute6,
282 Attribute7 = X_Attribute7,
283 Attribute8 = X_Attribute8,
284 Attribute9 = X_Attribute9,
285 Attribute10 = X_Attribute10,
286 Attribute11 = X_Attribute11,
287 Attribute12 = X_Attribute12,
288 Attribute13 = X_Attribute13,
289 Attribute14 = X_Attribute14,
290 Attribute15 = X_Attribute15
291
292 WHERE rowid = X_Rowid;
293
294 IF (SQL%NOTFOUND) THEN
295
296 RAISE NO_DATA_FOUND;
297
298 END IF;
299
300 END Update_Row;
301
302
303 PROCEDURE CHECK_UNIQUE (X_Rowid VARCHAR2,
304 X_Customer_Item_Id NUMBER,
305 X_Inventory_Item_Id NUMBER,
306 X_Master_Organization_Id NUMBER) IS
307
308 Dummy NUMBER;
309
310 BEGIN
311
312 SELECT COUNT(1)
313 INTO Dummy
314 FROM MTL_CUSTOMER_ITEM_XREFS
315 WHERE Customer_Item_Id = X_Customer_Item_Id
316 AND Inventory_Item_Id = X_Inventory_Item_Id
317 AND Master_Organization_Id = X_Master_Organization_Id
318 AND ((X_Rowid IS NULL) OR (ROWID <> X_Rowid));
319
320 IF (Dummy >= 1) THEN
321
322 FND_MESSAGE.SET_NAME('INV', 'INV_DUP_CUST_ITEM_XREF');
323 APP_EXCEPTION.RAISE_EXCEPTION;
324
325 END IF;
326
327 END CHECK_UNIQUE;
328
329
330 PROCEDURE CHECK_UNIQUE_RANK (X_Rowid VARCHAR2,
331 X_Customer_Item_Id NUMBER,
332 X_Master_Organization_Id NUMBER,
333 X_Rank NUMBER ) IS
334
335 Dummy NUMBER;
336
337 BEGIN
338
339 SELECT COUNT(1)
340 INTO Dummy
341 FROM MTL_CUSTOMER_ITEM_XREFS
342 WHERE Customer_Item_Id = X_Customer_Item_Id
343 AND Master_Organization_Id = X_Master_Organization_Id
344 AND Preference_Number = X_Rank
345 AND ((X_Rowid IS NULL) OR (ROWID <> X_Rowid));
346
347 IF (Dummy >= 1) THEN
348
349 FND_MESSAGE.SET_NAME('INV', 'INV_DUP_CUST_ITEM_XREF');
350 APP_EXCEPTION.RAISE_EXCEPTION;
351
352 END IF;
353
354 END CHECK_UNIQUE_RANK;
355
356
357 END INVICXRF;