1 PACKAGE BODY mtl_og_util_pkg AS
2 /* $Header: INVOGUTB.pls 115.16 2004/08/13 02:01:16 ssia ship $ */
3 --
4 /*
5 * Get the Object_ID and Object_Type of an object given its object_number
6 * and Inventory_Item_Id
7 */
8 PROCEDURE get_objid(
9 p_object_number IN VARCHAR2
10 , p_inventory_item_id IN NUMBER
11 , p_organization_id IN NUMBER
12 , x_object_id OUT NOCOPY NUMBER
13 , x_object_type OUT NOCOPY NUMBER
14 , x_return_status OUT NOCOPY VARCHAR2
15 , x_msg_data OUT NOCOPY VARCHAR2
16 , x_msg_count OUT NOCOPY NUMBER
17 ) IS
18 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
19 BEGIN
20 x_return_status := FND_API.G_RET_STS_SUCCESS;
21 /* Bug 2456261: Added Object_Type = 2 in the Query */
22 SELECT object_id, object_type
23 INTO x_object_id, x_object_type
24 FROM mtl_object_numbers_v
25 WHERE object_number = p_object_number
26 AND inventory_item_id = p_inventory_item_id
27 AND object_type = 2
28 AND organization_id = NVL(p_organization_id, organization_id);
29
30 EXCEPTION
31 WHEN NO_DATA_FOUND THEN
32 x_return_status := FND_API.G_RET_STS_ERROR;
33 fnd_message.set_name('INV','INV_NO_OBJECT_ID');
34 fnd_message.set_token('OBJECT_NAME',p_object_number,FALSE);
35 fnd_msg_pub.ADD;
36 fnd_msg_pub.count_and_get(p_encoded => FND_API.G_FALSE, p_data => x_msg_data, p_count => x_msg_count);
37 WHEN OTHERS THEN
38 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
39 fnd_msg_pub.count_and_get(p_encoded => FND_API.G_FALSE, p_data => x_msg_data, p_count => x_msg_count);
40 END get_objid;
41
42 /*
43 * Insert a new Genealogy record connecting a child and it's parent
44 * Due to AK Navigator considerations, to show 'Child Genealogy', all objects
45 * should have a record connecting them with a parent. Similary, to show
46 * 'Parent-Genealogy' all objects should have a record connecting them
47 * to a child.
48 */
49 PROCEDURE gen_insert(
50 p_rowid IN OUT NOCOPY VARCHAR2
51 , p_item_id IN NUMBER
52 , p_object_num IN VARCHAR2
53 , p_parent_item_id IN NUMBER
54 , p_parent_object_num IN VARCHAR2
55 , p_origin_txn_id IN NUMBER
56 , p_org_id IN NUMBER := NULL
57 ) IS
58 l_trx_date DATE;
59 l_obj_id NUMBER;
60 l_obj_type NUMBER;
61 l_parent_obj_id NUMBER;
62 l_parent_obj_type NUMBER;
63 l_genealogy_origin NUMBER;
64 l_genealogy_type NUMBER;
65 l_user_id NUMBER := fnd_global.user_id;
66 l_return_status VARCHAR2(30);
67 l_msg_data VARCHAR2(200);
68 l_msg_count NUMBER;
69
70 CURSOR c IS
71 SELECT ROWID
72 FROM mtl_object_genealogy
73 WHERE object_id = l_obj_id
74 AND parent_object_id = l_parent_obj_id;
75 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
76 BEGIN
77 /*
78 * First get Obj. ID and Type of Object and its parent
79 */
80 get_objid(p_object_num, p_item_id, p_org_id, l_obj_id,l_obj_type,l_return_status,l_msg_data,l_msg_count);
81 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
82 RAISE NO_DATA_FOUND;
83 END IF;
84 get_objid(p_parent_object_num, p_parent_item_id, p_org_id, l_parent_obj_id,l_parent_obj_type,l_return_status,l_msg_data,l_msg_count);
85 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
86 RAISE NO_DATA_FOUND;
87 END IF;
88 l_genealogy_type := 1;
89 l_genealogy_origin := 1;
90 l_trx_date := SYSDATE;
91
92 INSERT INTO mtl_object_genealogy
93 (
94 object_id
95 , object_type
96 , parent_object_type
97 , parent_object_id
98 , last_update_date
99 , last_updated_by
100 , creation_date
101 , created_by
102 , start_date_active
103 , genealogy_origin
104 , origin_txn_id
105 , genealogy_type
106 )
107 VALUES (
108 l_obj_id
109 , l_obj_type
110 , l_parent_obj_type
111 , l_parent_obj_id
112 , SYSDATE
113 , l_user_id
114 , SYSDATE
115 , l_user_id
116 , l_trx_date
117 , l_genealogy_origin
118 , p_origin_txn_id
119 , l_genealogy_type
120 );
121
122 OPEN c;
123 FETCH c INTO p_rowid;
124
125 IF (c%NOTFOUND) THEN
126 CLOSE c;
127 RAISE NO_DATA_FOUND;
128 END IF;
129
130 CLOSE c;
131 END gen_insert;
132
133
134 PROCEDURE gen_insert(
135 x_return_status OUT NOCOPY VARCHAR2
136 , x_msg_data OUT NOCOPY VARCHAR2
137 , x_msg_count OUT NOCOPY NUMBER
138 , p_item_id IN NUMBER
139 , p_object_num IN VARCHAR2
140 , p_parent_item_id IN NUMBER
141 , p_parent_object_num IN VARCHAR2
142 , p_origin_txn_id IN NUMBER
143 , p_org_id IN NUMBER
144 ) IS
145 l_trx_date DATE;
146 l_obj_id NUMBER;
147 l_obj_type NUMBER;
148 l_parent_obj_id NUMBER;
149 l_parent_obj_type NUMBER;
150 l_genealogy_origin NUMBER;
151 l_genealogy_type NUMBER;
152 l_user_id NUMBER := fnd_global.user_id;
153 l_rowid VARCHAR2(30);
154 l_parent_ser_ctrl NUMBER;
155
156 CURSOR c IS
157 SELECT ROWID
158 FROM mtl_object_genealogy
159 WHERE object_id = l_obj_id
160 AND parent_object_id = l_parent_obj_id;
161 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
162 BEGIN
163 x_return_status := FND_API.G_RET_STS_SUCCESS;
164
165 /* Checking whether Parent Item is Serial Controlled */
166 BEGIN
167 SELECT serial_number_control_code INTO l_parent_ser_ctrl
168 FROM mtl_system_items
169 WHERE inventory_item_id = p_parent_item_id
170 AND organization_id = p_org_id;
171
172 IF l_parent_ser_ctrl IN (1,6) THEN
173 IF (l_debug = 1) THEN
174 inv_log_util.trace('Parent not Serial Controlled','INV_OG_UTIL_PKG.GET_INSERT',3);
175 END IF;
176 RETURN;
177 END IF;
178 EXCEPTION
179 WHEN NO_DATA_FOUND THEN
180 fnd_message.set_name(application=> 'INV',NAME => 'INV_INVALID_ITEM_ORG');
181 fnd_msg_pub.ADD;
182 RAISE FND_API.G_EXC_ERROR;
183 END;
184
185 /* Getting Object ID of the Child Object */
186 get_objid(p_object_num, p_item_id, p_org_id, l_obj_id, l_obj_type, x_return_status, x_msg_data, x_msg_count);
187 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
188 RAISE FND_API.G_EXC_ERROR;
189 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
190 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
191 END IF;
192
193 /* Getting Object ID of the Parent Object */
194 get_objid(p_parent_object_num, p_parent_item_id, p_org_id, l_parent_obj_id, l_parent_obj_type, x_return_status, x_msg_data, x_msg_count);
195 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
196 RAISE FND_API.G_EXC_ERROR;
197 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
198 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
199 END IF;
200
201 l_genealogy_type := 1;
202 l_genealogy_origin := 1;
203 l_trx_date := SYSDATE;
204
205 INSERT INTO mtl_object_genealogy
206 (
207 object_id
208 , object_type
209 , parent_object_type
210 , parent_object_id
211 , last_update_date
212 , last_updated_by
213 , creation_date
214 , created_by
215 , start_date_active
216 , genealogy_origin
217 , origin_txn_id
218 , genealogy_type
219 )
220 VALUES (
221 l_obj_id
222 , l_obj_type
223 , l_parent_obj_type
224 , l_parent_obj_id
225 , SYSDATE
226 , l_user_id
227 , SYSDATE
228 , l_user_id
229 , l_trx_date
230 , l_genealogy_origin
231 , p_origin_txn_id
232 , l_genealogy_type
233 );
234
235 OPEN c;
236 FETCH c INTO l_rowid;
237
238 IF (c%NOTFOUND) THEN
239 CLOSE c;
240 fnd_message.set_name(application=> 'INV',NAME => 'INV_INSERT_ERROR');
241 fnd_message.set_token(token => 'ENTITY1',VALUE => 'Genealogy',TRANSLATE=> FALSE);
242 fnd_msg_pub.ADD;
243 RAISE FND_API.G_EXC_ERROR;
244 END IF;
245
246 CLOSE c;
247 EXCEPTION
248 WHEN FND_API.G_EXC_ERROR THEN
249 x_return_status := FND_API.G_RET_STS_ERROR;
250 fnd_msg_pub.count_and_get(p_encoded => FND_API.G_FALSE, p_data => x_msg_data, p_count => x_msg_count);
251 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
252 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
253 fnd_msg_pub.count_and_get(p_encoded => FND_API.G_FALSE, p_data => x_msg_data, p_count => x_msg_count);
254 WHEN OTHERS THEN
255 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
256 fnd_msg_pub.count_and_get(p_encoded => FND_API.G_FALSE, p_data => x_msg_data, p_count => x_msg_count);
257 END gen_insert;
258
259 /** added the procedure gen_update for the 'Serial Tracking in WIP Project.
260 This updates the mtl_object_genealogy and mtl_serial_numbers tables
261 when a serialized component is returned to stores from a WIP job.
262 The genealogy between the parent and the child serials should be disabled
263 whenever a component return transaction is performed. */
264 PROCEDURE gen_update(
265 x_return_status OUT NOCOPY VARCHAR2
266 , x_msg_data OUT NOCOPY VARCHAR2
267 , x_msg_count OUT NOCOPY NUMBER
268 , p_item_id IN NUMBER
269 , p_sernum IN VARCHAR2
270 , p_parent_sernum IN VARCHAR2
271 , p_org_id IN NUMBER
272 ) IS
273 l_object_id NUMBER;
274 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
275 BEGIN
276 x_return_status := FND_API.G_RET_STS_SUCCESS;
277
278 BEGIN
279 if( p_parent_sernum is not null ) then
280 SELECT gen_object_id INTO l_object_id
281 FROM mtl_serial_numbers
282 WHERE serial_number = p_sernum
283 AND parent_serial_number = p_parent_sernum
284 AND current_organization_id = p_org_id
285 AND inventory_item_id = p_item_id; --Bug # 2682600
286 else
287 SELECT gen_object_id into l_object_id
288 FROM mtl_serial_numbers
289 WHERE serial_number = p_sernum
290 AND current_organization_id = p_org_id
291 AND inventory_item_id = p_item_id; --Bug # 2682600
292 end if;
293
294 EXCEPTION
295 WHEN NO_DATA_FOUND THEN
296 IF (l_debug = 1) THEN
297 inv_trx_util_pub.trace(' no data found in gen_update','MTL_OG_UTIL_PKG');
298 END IF;
299 x_return_status := FND_API.G_RET_STS_ERROR;
300 WHEN OTHERS THEN
301 IF (l_debug = 1) THEN
302 inv_trx_util_pub.trace(' exception in gen_update','MTL_OG_UTIL_PKG');
303 END IF;
304 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
305 END;
306 BEGIN
307 UPDATE mtl_object_genealogy
308 SET end_date_active = SYSDATE
309 ,LAST_UPDATE_DATE = SYSDATE
310 ,LAST_UPDATED_BY = -1
311 ,LAST_UPDATE_LOGIN = -1
312 WHERE object_id = l_object_id
313 AND END_date_active IS NULL
314 AND genealogy_type <> 5 ;
315
316 UPDATE mtl_serial_numbers
317 SET parent_serial_number = NULL
318 ,LAST_UPDATE_DATE = SYSDATE
319 ,LAST_UPDATED_BY = -1
320 ,LAST_UPDATE_LOGIN = -1
321 WHERE gen_object_id = l_object_id;
322 EXCEPTION
323 WHEN no_data_found THEN
324 IF (l_debug = 1) THEN
325 inv_trx_util_pub.trace(' no data found while trying to update in gen_update','MTL_OG_UTIL_PKG');
326 END IF;
327 x_return_status := FND_API.G_RET_STS_ERROR;
328 WHEN OTHERS THEN
329 IF (l_debug = 1) THEN
330 inv_trx_util_pub.trace(' exception in gen_update when trying to update','MTL_OG_UTIL_PKG');
331 END IF;
332 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
333 END;
334 END gen_update;
335
336 /** Insert a record corresponding to an event into the MTL_OBJECT_EVENTS table
340 , p_item_id IN NUMBER
337 */
338 PROCEDURE event_insert(
339 p_rowid IN OUT NOCOPY VARCHAR2
341 , p_object_number IN VARCHAR2
342 , p_trx_id IN NUMBER
343 , p_trx_date IN DATE
344 , p_trx_src_id IN NUMBER
345 , p_trx_actin_id IN NUMBER
346 , p_org_id IN NUMBER := NULL
347 ) IS
348 l_gen_event_type NUMBER;
349 l_object_type NUMBER;
350 l_object_id NUMBER := 0;
351 l_user_id NUMBER := fnd_global.user_id;
352 l_return_status VARCHAR2(30);
353 l_msg_data VARCHAR2(200);
354 l_msg_count NUMBER;
355 /* mrana : 10/02/01:
356 Removed the cursot C, to select rowid of the row just inserted into mtl_object_event
357 since it is un-necessary.
358 Also , now populating p_rowid (IN OUT parameter) with '0' to avoid any problems with NULL
359 calue in inltis.
360 p_rowid is not used anywhere */
361 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
362 BEGIN
363 p_rowid := '0';
364 /*
365 * Determine the type of transaction from the transaction source and action
366 */
367 get_objid(p_object_number, p_item_id, p_org_id, l_object_id, l_object_type, l_return_status, l_msg_data, l_msg_count);
368 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
369 RAISE NO_DATA_FOUND;
370 END IF;
371
372 /*
373 * Determine the Genealogy Event Type
374 * If src_id = 5 AND trx_actin_id = 1 this is a WIP issue. So event
375 * type is 'Built'. If src_id = 13 and trx_actin_id = 27, then this
376 * is a Misc. receipt transaction. So event is 'Issued'
377 */
378 IF (p_trx_src_id = 5)
379 AND (p_trx_actin_id = 1) THEN
380 l_gen_event_type := 1;
381 ELSIF (p_trx_src_id = 13)
382 AND (p_trx_actin_id = 27) THEN
383 l_gen_event_type := 4;
384 END IF;
385
386 INSERT INTO mtl_object_events
387 (
388 object_id
389 , genealogy_event_type
390 , genealogy_event_date
391 , transaction_id
392 , creation_date
393 , created_by
394 , last_update_date
395 , last_updated_by
396 )
397 VALUES (
398 l_object_id
399 , l_gen_event_type
400 , p_trx_date
401 , p_trx_id
402 , SYSDATE
403 , l_user_id
404 , SYSDATE
405 , l_user_id
406 );
407 END event_insert;
408 END;
[Home] [Help]