[Home] [Help]
PACKAGE BODY: APPS.MTL_MFG_PART_NUMBERS_PKG
Source
1 PACKAGE BODY MTL_MFG_PART_NUMBERS_PKG as
2 /* $Header: INVIDMPB.pls 120.2.12010000.2 2008/07/29 12:52:47 ptkumar ship $ */
3
4 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
5
6 X_Manufacturer_Id NUMBER,
7 X_Mfg_Part_Num VARCHAR2,
8 X_Inventory_Item_Id NUMBER,
9 X_Last_Update_Date DATE,
10 X_Last_Updated_By NUMBER,
11 X_Creation_Date DATE,
12 X_Created_By NUMBER,
13 X_Last_Update_Login NUMBER,
14 X_Organization_Id NUMBER,
15 X_Description VARCHAR2,
16 X_Attribute_Category VARCHAR2,
17 X_Attribute1 VARCHAR2,
18 X_Attribute2 VARCHAR2,
19 X_Attribute3 VARCHAR2,
20 X_Attribute4 VARCHAR2,
21 X_Attribute5 VARCHAR2,
22 X_Attribute6 VARCHAR2,
23 X_Attribute7 VARCHAR2,
24 X_Attribute8 VARCHAR2,
25 X_Attribute9 VARCHAR2,
26 X_Attribute10 VARCHAR2,
27 X_Attribute11 VARCHAR2,
28 X_Attribute12 VARCHAR2,
29 X_Attribute13 VARCHAR2,
30 X_Attribute14 VARCHAR2,
31 X_Attribute15 VARCHAR2
32 ) IS
33 CURSOR C IS SELECT rowid FROM mtl_mfg_part_numbers
34 WHERE manufacturer_id = X_Manufacturer_Id
35 AND mfg_part_num = X_Mfg_Part_Num
36 AND inventory_item_id = X_Inventory_Item_Id;
37
38 BEGIN
39
40
41 INSERT INTO mtl_mfg_part_numbers(
42
43 manufacturer_id,
44 mfg_part_num,
45 inventory_item_id,
46 last_update_date,
47 last_updated_by,
48 creation_date,
49 created_by,
50 last_update_login,
51 organization_id,
52 description,
53 attribute_category,
54 attribute1,
55 attribute2,
56 attribute3,
57 attribute4,
58 attribute5,
59 attribute6,
60 attribute7,
61 attribute8,
62 attribute9,
63 attribute10,
64 attribute11,
65 attribute12,
66 attribute13,
67 attribute14,
68 attribute15
69 ) VALUES (
70
71 X_Manufacturer_Id,
72 X_Mfg_Part_Num,
73 X_Inventory_Item_Id,
74 X_Last_Update_Date,
75 X_Last_Updated_By,
76 X_Creation_Date,
77 X_Created_By,
78 X_Last_Update_Login,
79 X_Organization_Id,
80 X_Description,
81 X_Attribute_Category,
82 X_Attribute1,
83 X_Attribute2,
84 X_Attribute3,
85 X_Attribute4,
86 X_Attribute5,
87 X_Attribute6,
88 X_Attribute7,
89 X_Attribute8,
90 X_Attribute9,
91 X_Attribute10,
92 X_Attribute11,
93 X_Attribute12,
94 X_Attribute13,
95 X_Attribute14,
96 X_Attribute15
97
98 );
99
100 OPEN C;
101 FETCH C INTO X_Rowid;
102 if (C%NOTFOUND) then
103 CLOSE C;
104 Raise NO_DATA_FOUND;
105 end if;
106 CLOSE C;
107
108 /* R12: Business Event Enhancement:
109 Raise Event if AML got Created successfully */
110 BEGIN
111 INV_ITEM_EVENTS_PVT.Raise_Events(
112 p_event_name => 'EGO_WF_WRAPPER_PVT.G_AML_CHANGE_EVENT'
113 ,p_dml_type => 'CREATE'
114 ,p_inventory_item_id => X_Inventory_Item_Id
115 ,p_organization_id => X_Organization_Id
116 ,p_mfg_part_num => X_Mfg_Part_Num
117 ,p_manufacturer_id => X_Manufacturer_ID);
118 EXCEPTION
119 WHEN OTHERS THEN
120 NULL;
121 END;
122 /* Code Added for bug-6525662 starts here */
123 BEGIN
124 INV_ITEM_EVENTS_PVT.Invoke_ICX_APIs(
125 p_entity_type => 'ITEM'
126 ,p_dml_type => 'UPDATE'
127 ,p_inventory_item_id => X_Inventory_Item_Id
128 ,p_item_description => NULL
129 ,p_organization_id => X_Organization_Id
130 ,p_master_org_flag => NULL );
131
132 EXCEPTION
133 WHEN OTHERS THEN
134 NULL;
135 END;
136
137 /* Code Added for bug-6525662 Ends here */
138
139 END Insert_Row;
140
141
142 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
143
144 X_Manufacturer_Id NUMBER,
145 X_Mfg_Part_Num VARCHAR2,
146 X_Inventory_Item_Id NUMBER,
147 X_Organization_Id NUMBER,
148 X_Description VARCHAR2,
149 X_Attribute_Category VARCHAR2,
150 X_Attribute1 VARCHAR2,
151 X_Attribute2 VARCHAR2,
152 X_Attribute3 VARCHAR2,
153 X_Attribute4 VARCHAR2,
154 X_Attribute5 VARCHAR2,
155 X_Attribute6 VARCHAR2,
156 X_Attribute7 VARCHAR2,
157 X_Attribute8 VARCHAR2,
158 X_Attribute9 VARCHAR2,
159 X_Attribute10 VARCHAR2,
160 X_Attribute11 VARCHAR2,
161 X_Attribute12 VARCHAR2,
162 X_Attribute13 VARCHAR2,
163 X_Attribute14 VARCHAR2,
164 X_Attribute15 VARCHAR2
165 ) IS
166 CURSOR C IS
167 SELECT *
168 FROM mtl_mfg_part_numbers
169 WHERE rowid = X_Rowid
170 FOR UPDATE of Manufacturer_Id NOWAIT;
171 Recinfo C%ROWTYPE;
172
173
174 BEGIN
175 OPEN C;
176 FETCH C INTO Recinfo;
177 if (C%NOTFOUND) then
178 CLOSE C;
179 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
180 APP_EXCEPTION.Raise_Exception;
181 end if;
182 CLOSE C;
183 if ( (Recinfo.manufacturer_id = X_Manufacturer_Id)
184 AND (Recinfo.mfg_part_num = X_Mfg_Part_Num)
185 AND (Recinfo.inventory_item_id = X_Inventory_Item_Id)
186 AND ( (Recinfo.organization_id = X_Organization_Id)
187 OR ( (Recinfo.organization_id IS NULL)
188 AND (X_Organization_Id IS NULL)))
189 -- AND ( (Recinfo.mrp_planning_code = X_Mrp_Planning_Code)
190 -- OR ( (Recinfo.mrp_planning_code IS NULL)
191 -- AND (X_Mrp_Planning_Code IS NULL)))
192 AND ( (Recinfo.description = X_Description)
193 OR ( (Recinfo.description IS NULL)
194 AND (X_Description IS NULL)))
195 AND ( (Recinfo.attribute_category = X_Attribute_Category)
196 OR ( (Recinfo.attribute_category IS NULL)
197 AND (X_Attribute_Category IS NULL)))
198 AND ( (Recinfo.attribute1 = X_Attribute1)
199 OR ( (Recinfo.attribute1 IS NULL)
200 AND (X_Attribute1 IS NULL)))
201 AND ( (Recinfo.attribute2 = X_Attribute2)
202 OR ( (Recinfo.attribute2 IS NULL)
203 AND (X_Attribute2 IS NULL)))
204 AND ( (Recinfo.attribute3 = X_Attribute3)
205 OR ( (Recinfo.attribute3 IS NULL)
206 AND (X_Attribute3 IS NULL)))
207 AND ( (Recinfo.attribute4 = X_Attribute4)
208 OR ( (Recinfo.attribute4 IS NULL)
209 AND (X_Attribute4 IS NULL)))
210 AND ( (Recinfo.attribute5 = X_Attribute5)
211 OR ( (Recinfo.attribute5 IS NULL)
212 AND (X_Attribute5 IS NULL)))
213 AND ( (Recinfo.attribute6 = X_Attribute6)
214 OR ( (Recinfo.attribute6 IS NULL)
215 AND (X_Attribute6 IS NULL)))
216 AND ( (Recinfo.attribute7 = X_Attribute7)
217 OR ( (Recinfo.attribute7 IS NULL)
218 AND (X_Attribute7 IS NULL)))
219 AND ( (Recinfo.attribute8 = X_Attribute8)
220 OR ( (Recinfo.attribute8 IS NULL)
221 AND (X_Attribute8 IS NULL)))
222 AND ( (Recinfo.attribute9 = X_Attribute9)
223 OR ( (Recinfo.attribute9 IS NULL)
224 AND (X_Attribute9 IS NULL)))
225 AND ( (Recinfo.attribute10 = X_Attribute10)
226 OR ( (Recinfo.attribute10 IS NULL)
227 AND (X_Attribute10 IS NULL)))
228 AND ( (Recinfo.attribute11 = X_Attribute11)
229 OR ( (Recinfo.attribute11 IS NULL)
230 AND (X_Attribute11 IS NULL)))
231 AND ( (Recinfo.attribute12 = X_Attribute12)
232 OR ( (Recinfo.attribute12 IS NULL)
233 AND (X_Attribute12 IS NULL)))
234 AND ( (Recinfo.attribute13 = X_Attribute13)
235 OR ( (Recinfo.attribute13 IS NULL)
236 AND (X_Attribute13 IS NULL)))
237 AND ( (Recinfo.attribute14 = X_Attribute14)
238 OR ( (Recinfo.attribute14 IS NULL)
239 AND (X_Attribute14 IS NULL)))
240 AND ( (Recinfo.attribute15 = X_Attribute15)
241 OR ( (Recinfo.attribute15 IS NULL)
242 AND (X_Attribute15 IS NULL)))
243 ) then
244 return;
245 else
246 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
247 APP_EXCEPTION.Raise_Exception;
248 end if;
249 END Lock_Row;
250
251
252
253 PROCEDURE Update_Row(X_Rowid VARCHAR2,
254
255 X_Manufacturer_Id NUMBER,
256 X_Mfg_Part_Num VARCHAR2,
257 X_Inventory_Item_Id NUMBER,
258 X_Last_Update_Date DATE,
259 X_Last_Updated_By NUMBER,
260 X_Last_Update_Login NUMBER,
261 X_Organization_Id NUMBER,
262 X_Description VARCHAR2,
263 X_Attribute_Category VARCHAR2,
264 X_Attribute1 VARCHAR2,
265 X_Attribute2 VARCHAR2,
266 X_Attribute3 VARCHAR2,
267 X_Attribute4 VARCHAR2,
268 X_Attribute5 VARCHAR2,
269 X_Attribute6 VARCHAR2,
270 X_Attribute7 VARCHAR2,
271 X_Attribute8 VARCHAR2,
272 X_Attribute9 VARCHAR2,
273 X_Attribute10 VARCHAR2,
274 X_Attribute11 VARCHAR2,
275 X_Attribute12 VARCHAR2,
276 X_Attribute13 VARCHAR2,
277 X_Attribute14 VARCHAR2,
278 X_Attribute15 VARCHAR2
279
280 ) IS
281 BEGIN
282 UPDATE mtl_mfg_part_numbers
283 SET
284 manufacturer_id = X_Manufacturer_Id,
285 mfg_part_num = X_Mfg_Part_Num,
286 inventory_item_id = X_Inventory_Item_Id,
287 last_update_date = X_Last_Update_Date,
288 last_updated_by = X_Last_Updated_By,
289 last_update_login = X_Last_Update_Login,
290 organization_id = X_Organization_Id,
291 description = X_Description,
292 attribute_category = X_Attribute_Category,
293 attribute1 = X_Attribute1,
294 attribute2 = X_Attribute2,
295 attribute3 = X_Attribute3,
296 attribute4 = X_Attribute4,
297 attribute5 = X_Attribute5,
298 attribute6 = X_Attribute6,
299 attribute7 = X_Attribute7,
300 attribute8 = X_Attribute8,
301 attribute9 = X_Attribute9,
302 attribute10 = X_Attribute10,
303 attribute11 = X_Attribute11,
304 attribute12 = X_Attribute12,
305 attribute13 = X_Attribute13,
306 attribute14 = X_Attribute14,
307 attribute15 = X_Attribute15
308 WHERE rowid = X_Rowid;
309
310 if (SQL%NOTFOUND) then
311 Raise NO_DATA_FOUND;
312 end if;
313 /* R12: Business Event Enhancement:
314 Raise Event if AML got Updated successfully */
315 BEGIN
316 INV_ITEM_EVENTS_PVT.Raise_Events(
317 p_event_name => 'EGO_WF_WRAPPER_PVT.G_AML_CHANGE_EVENT'
318 ,p_dml_type => 'UPDATE'
319 ,p_inventory_item_id => X_Inventory_Item_Id
320 ,p_organization_id => X_Organization_Id
321 ,p_mfg_part_num => X_Mfg_Part_Num
322 ,p_manufacturer_id => X_Manufacturer_ID);
323 EXCEPTION
324 WHEN OTHERS THEN
325 NULL;
326 END;
327
328 /* Code Added for bug-6525662 starts here */
329 BEGIN
330 INV_ITEM_EVENTS_PVT.Invoke_ICX_APIs(
331 p_entity_type => 'ITEM'
332 ,p_dml_type => 'UPDATE'
333 ,p_inventory_item_id => X_Inventory_Item_Id
334 ,p_item_description => NULL
335 ,p_organization_id => X_Organization_Id
336 ,p_master_org_flag => NULL );
337
338 EXCEPTION
339 WHEN OTHERS THEN
340 NULL;
341 END;
342
343 /* Code Added for bug-6525662 Ends here */
344
345
346 END Update_Row;
347
348
349 PROCEDURE Delete_Row(X_Rowid VARCHAR2) IS
350 --R12 Business Events
351 l_Inventory_Item_Id mtl_mfg_part_numbers.INVENTORY_ITEM_ID%TYPE;
352 l_Organization_Id mtl_mfg_part_numbers.ORGANIZATION_ID%TYPE;
353 l_Mfg_Part_Num mtl_mfg_part_numbers.MFG_PART_NUM%TYPE;
354 l_Manufacturer_ID mtl_mfg_part_numbers.MANUFACTURER_ID%TYPE;
355
356 BEGIN
357 --R12 Fetch the parameter values to be passed to the event
358 SELECT INVENTORY_ITEM_ID,
359 ORGANIZATION_ID,
360 MFG_PART_NUM,
361 MANUFACTURER_ID
362 INTO
363 l_Inventory_Item_Id,
364 l_Organization_Id,
365 l_Mfg_Part_Num,
366 l_Manufacturer_ID
367 FROM mtl_mfg_part_numbers
368 WHERE rowid = X_Rowid;
369
370
371 DELETE FROM mtl_mfg_part_numbers
372 WHERE rowid = X_Rowid;
373
374 if (SQL%NOTFOUND) then
375 Raise NO_DATA_FOUND;
376 end if;
377
378 /* R12: Business Event Enhancement:
379 Raise Event if AML got deleted successfully */
380 BEGIN
381 INV_ITEM_EVENTS_PVT.Raise_Events(
382 p_event_name => 'EGO_WF_WRAPPER_PVT.G_AML_CHANGE_EVENT'
383 ,p_dml_type => 'DELETE'
384 ,p_inventory_item_id => l_Inventory_Item_Id
385 ,p_organization_id => l_Organization_Id
386 ,p_mfg_part_num => l_Mfg_Part_Num
387 ,p_manufacturer_id => l_Manufacturer_ID);
388 EXCEPTION
389 WHEN OTHERS THEN
390 NULL;
391 END;
392
393 /* Code Added for bug-6525662 starts here */
394 BEGIN
395
396 INV_ITEM_EVENTS_PVT.Invoke_ICX_APIs(
397 p_entity_type => 'ITEM'
398 ,p_dml_type => 'UPDATE'
399 ,p_inventory_item_id => l_Inventory_Item_Id
400 ,p_item_description => NULL
401 ,p_organization_id => l_Organization_Id
402 ,p_master_org_flag => NULL );
403
404 EXCEPTION
405 WHEN OTHERS THEN
406
407 NULL;
408
409 END;
410
411 /* Code Added for bug-6525662 Ends here */
412
413
414 END Delete_Row;
415
416 PROCEDURE Call_Sync_Index IS
417 l_dynamic_sql VARCHAR2(200);
418
419 BEGIN
420 l_dynamic_sql :=
421 ' BEGIN '||
422 ' EGO_ITEM_TEXT_UTIL.Sync_Index(); '||
423 ' END;';
424 EXECUTE IMMEDIATE l_dynamic_sql;
425 EXCEPTION
426 WHEN OTHERS THEN
427 NULL;
428 END Call_Sync_Index;
429
430 END MTL_MFG_PART_NUMBERS_PKG;