[Home] [Help]
PACKAGE BODY: APPS.AHL_MC_ITEM_COMP_PVT
Source
1 PACKAGE BODY AHL_MC_ITEM_COMP_PVT AS
2 /* $Header: AHLVICXB.pls 120.1 2006/01/10 03:41:28 sagarwal noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'ahl_mc_item_comp_pvt';
5
6 PROCEDURE Create_Line_Item (p_item_composition_id IN NUMBER,
7 p_x_comp_det_rec IN OUT NOCOPY AHL_MC_ITEM_COMP_PVT.Detail_Rec_Type
8 );
9
10 PROCEDURE Update_Line_Item (p_item_composition_id IN NUMBER,
11 p_x_comp_det_rec IN OUT NOCOPY AHL_MC_ITEM_COMP_PVT.Detail_Rec_Type
12 );
13
14 PROCEDURE Delete_Line_Item (p_item_composition_id IN NUMBER,
15 p_x_comp_det_rec IN OUT NOCOPY AHL_MC_ITEM_COMP_PVT.Detail_Rec_Type
16 );
17
18 PROCEDURE Validate_InventoryID(p_inventory_item_id IN NUMBER,
19 p_inventory_org_id IN NUMBER,
20 p_record_type IN VARCHAR2,
21 p_master_org_id IN OUT NOCOPY NUMBER
22 ) IS
23
24
25 CURSOR mtl_system_items_non_ou_csr(p_inventory_item_id IN NUMBER,
26 p_inventory_org_id IN NUMBER) IS
27 SELECT NVL(comms_nl_trackable_flag,'N'), concatenated_segments,
28 SERIAL_NBR_CNTRL_CODE,revision_qty_cntrl_code
29 FROM ahl_mtl_items_non_ou_v
30 WHERE inventory_item_id = p_inventory_item_id
31 AND inventory_org_id = p_inventory_org_id;
32
33
34 CURSOR mtl_parameters_csr(p_inventory_org_id IN NUMBER) IS
35 SELECT organization_code,
36 master_organization_id
37 FROM mtl_parameters
38 WHERE organization_id = p_inventory_org_id;
39
40 l_instance_track VARCHAR2(1);
41 l_segment1 ahl_mtl_items_ou_v.concatenated_segments%TYPE;
42 l_serial_number_control NUMBER;
43 l_revision_qty_control_code NUMBER;
44 l_organization_code mtl_parameters.organization_code%TYPE;
45 l_master_org_id NUMBER;
46
47 BEGIN
48
49 -- For organization code
50 OPEN mtl_parameters_csr(p_inventory_org_id);
51 FETCH mtl_parameters_csr INTO l_organization_code,l_master_org_id;
52 IF (mtl_parameters_csr%NOTFOUND) THEN
53 FND_MESSAGE.Set_Name('AHL','AHL_MC_ORG_INVALID');
54 FND_MESSAGE.Set_Token('ORG',p_inventory_org_id);
55 FND_MSG_PUB.ADD;
56 ELSE
57 p_master_org_id := l_master_org_id;
58 /*IF l_master_org_id <> p_master_org_id THEN
59 FND_MESSAGE.Set_Name('AHL','AHL_MC_MASTER_ORG_INVALID');
60 FND_MESSAGE.Set_Token('ORG',p_master_org_id);
61 FND_MSG_PUB.ADD;
62 */
63 END IF;
64 CLOSE mtl_parameters_csr;
65
66
67 OPEN mtl_system_items_non_ou_csr(p_inventory_item_id,p_inventory_org_id);
68 FETCH mtl_system_items_non_ou_csr INTO l_instance_track, l_segment1, l_serial_number_control,
69 l_revision_qty_control_code;
70 l_segment1 := l_segment1 || ',' || l_organization_code;
71
72 IF (mtl_system_items_non_ou_csr%NOTFOUND) THEN
73 FND_MESSAGE.Set_Name('AHL','AHL_MC_INV_INVALID');
74 FND_MESSAGE.Set_Token('INV_ITEM',p_inventory_item_id);
75 FND_MSG_PUB.ADD;
76 --dbms_output.put_line('Inventory item does not exist');
77
78 l_segment1 := null;
79 l_revision_qty_control_code := null;
80 l_serial_number_control := null;
81
82 ELSE
83
84 IF ( UPPER(p_record_type) = 'HEADER' AND UPPER(l_instance_track) <> 'Y')
85 THEN
86 FND_MESSAGE.Set_Name('AHL','AHL_MC_INV_TRACK');
87 FND_MESSAGE.Set_Token('INV_ITEM',l_segment1);
88 FND_MSG_PUB.ADD;
89 --dbms_output.put_line('Rec Type '||p_record_type || ' and '||l_instance_track);
90 ELSIF ( UPPER(p_record_type) = 'DETAIL' AND UPPER(l_instance_track) = 'Y')
91 THEN
92 FND_MESSAGE.Set_Name('AHL','AHL_MC_INV_NON_TRACK');
93 FND_MESSAGE.Set_Token('INV_ITEM',l_segment1);
94 FND_MSG_PUB.ADD;
95 --dbms_output.put_line('Inventory item are trackable');
96 END IF;
97
98 END IF;
99
100 CLOSE mtl_system_items_non_ou_csr;
101
102 END Validate_InventoryID;
103
104 PROCEDURE Validate_Qty_UOM(p_uom_code IN VARCHAR2,
105 p_quantity IN NUMBER,
106 p_inventory_item_id IN NUMBER,
107 p_inventory_org_id IN NUMBER,
108 p_inv_segment IN VARCHAR2,
109 p_item_group_name IN VARCHAR2) IS
110
111 cursor validate_uom(p_uom_code in varchar2) is
112 select uom_code
113 from mtl_units_of_measure_vl
114 where uom_code = p_uom_code;
115
116 l_uom_code varchar2(3);
117 BEGIN
118
119
120 IF p_item_group_name IS NOT NULL AND
121 (p_uom_code IS NULL AND (p_quantity IS NULL OR p_quantity = 0)) THEN
122 RETURN;
123 END IF;
124
125
126
127 -- Check if UOM entered and valid.
128 IF (p_uom_code IS NULL OR p_uom_code = FND_API.G_MISS_CHAR) THEN
129 -- uom_code is null but quantity is not null.
130 IF (p_inv_segment IS NULL OR p_inv_segment = FND_API.G_MISS_CHAR) THEN
131 FND_MESSAGE.Set_Name('AHL','AHL_MC_IG_UOM_NULL');
132 FND_MESSAGE.Set_Token('IG',p_item_group_name);
133 FND_MSG_PUB.ADD;
134 ELSE
135 FND_MESSAGE.Set_Name('AHL','AHL_MC_INVUOM_NULL');
136 FND_MESSAGE.Set_Token('INV_ITEM',p_inv_segment);
137 FND_MSG_PUB.ADD;
138 END IF;
139 --dbms_output.put_line('Uom is null');
140 ELSIF p_inventory_item_id IS NOT NULL AND p_inventory_org_id IS NOT NULL THEN
141 IF NOT(inv_convert.Validate_Item_Uom(p_item_id => p_inventory_item_id,
142 p_organization_id => p_inventory_org_id,
143 p_uom_code => p_uom_code))
144 THEN
145 FND_MESSAGE.Set_Name('AHL','AHL_MC_INVUOM_INVALID');
146 FND_MESSAGE.Set_Token('UOM_CODE',p_uom_code);
147 FND_MESSAGE.Set_Token('INV_ITEM',p_inv_segment);
148 FND_MSG_PUB.ADD;
149 --dbms_output.put_line('Invalid UOM code for the item');
150 END IF;
151 ELSIF p_item_group_name IS NOT NULL THEN
152 OPEN validate_uom(p_uom_code);
153 FETCH validate_uom INTO l_uom_code;
154 IF validate_uom%NOTFOUND THEN
155 FND_MESSAGE.Set_Name('AHL','AHL_MC_IG_UOM_INVALID');
156 FND_MESSAGE.Set_Token('UOM_CODE',p_uom_code);
157 FND_MESSAGE.Set_Token('IG',p_item_group_name);
158 FND_MSG_PUB.ADD;
159 END IF;
160 END IF ;
161
162 -- Validate quantity.
163 IF (p_quantity IS NULL OR p_quantity = FND_API.G_MISS_NUM) THEN
164 IF (p_inv_segment IS NULL OR p_inv_segment = FND_API.G_MISS_CHAR) THEN
165 FND_MESSAGE.Set_Name('AHL','AHL_MC_IG_QTY_NULL');
166 FND_MESSAGE.Set_Token('IG',p_item_group_name);
167 FND_MSG_PUB.ADD;
168 ELSE
169 FND_MESSAGE.Set_Name('AHL','AHL_MC_QTY_NULL');
170 FND_MESSAGE.Set_Token('INV_ITEM',p_inv_segment);
171 FND_MSG_PUB.ADD;
172 END IF;
173 --dbms_output.put_line('Quantity is null');
174 ELSIF (p_quantity < 0) THEN
175 IF (p_inv_segment IS NULL OR p_inv_segment = FND_API.G_MISS_CHAR) THEN
176 FND_MESSAGE.Set_Name('AHL','AHL_MC_IG_QTY_INVALID');
177 FND_MESSAGE.Set_Token('IG',p_item_group_name);
178 FND_MESSAGE.Set_Token('QUANTITY',p_quantity);
179 FND_MSG_PUB.ADD;
180 ELSE
181 FND_MESSAGE.Set_Name('AHL','AHL_MC_INVQTY_INVALID');
182 FND_MESSAGE.Set_Token('INV_ITEM',p_inv_segment);
183 FND_MESSAGE.Set_Token('QUANTITY',p_quantity);
184 FND_MSG_PUB.ADD;
185 END IF;
186 --dbms_output.put_line('Invalid quantity');
187 END IF;
188
189 END Validate_Qty_UOM;
190
191
192
193 PROCEDURE Validate_Item_Comp_Det( p_item_composition_id IN NUMBER,
194 P_inv_item_name IN VARCHAR2,
195 p_x_Detail_Rec_Type IN OUT NOCOPY ahl_mc_item_comp_pvt.Detail_Rec_Type
196 )
197 AS
198
199
200 CURSOR validate_item_group(p_item_group_name IN VARCHAR2,
201 p_item_group_id IN NUMBER)
202 IS
203 SELECT 'x'
204 FROM ahl_item_groups_vl
205 WHERE item_group_id = p_item_group_id
206 AND name = p_item_group_name
207 AND type_code = 'NON-TRACKED'
208 AND status_code = 'COMPLETE';
209
210 CURSOR item_group_exists(p_item_group_id IN NUMBER,
211 p_item_composition_id IN NUMBER)
212 IS
213 SELECT 'x'
214 FROM AHL_ITEM_COMP_DETAILS
215 WHERE ITEM_GROUP_ID = p_item_group_id
216 AND ITEM_COMPOSITION_ID= p_item_composition_id
217 AND EFFECTIVE_END_DATE is null;
218
219 CURSOR inv_item_exists(p_inventory_item_id IN NUMBER,
220 p_inventory_master_org_id IN NUMBER,
221 p_item_composition_id IN NUMBER)
222 IS
223 SELECT 'x'
224 FROM AHL_ITEM_COMP_DETAILS
225 WHERE INVENTORY_ITEM_ID = p_inventory_item_id
226 AND INVENTORY_MASTER_ORG_ID = p_inventory_master_org_id
227 AND ITEM_COMPOSITION_ID= p_item_composition_id
228 AND EFFECTIVE_END_DATE is null;
229
230
231 CURSOR get_item_comp_det( p_item_composition_id IN NUMBER,
232 p_item_comp_detail_id IN NUMBER)
233 IS
234 SELECT 'x'
235 FROM AHL_ITEM_COMP_DETAILS
236 WHERE item_comp_detail_id = p_item_comp_detail_id
237 AND item_composition_id= p_item_composition_id;
238
239
240 l_dummy varchar2(1);
241
242 l_Detail_Rec_Type ahl_mc_item_comp_pvt.Detail_Rec_Type DEFAULT p_x_Detail_Rec_Type;
243
244 BEGIN
245
246
247 IF l_Detail_Rec_Type.operation_flag ='M' THEN
248
249 --dbms_output.put_line(l_Detail_Rec_Type.item_composition_id ||'--'|| p_item_composition_id);
250 IF NVL(l_Detail_Rec_Type.item_composition_id,0) <> NVL(p_item_composition_id,0) THEN
251 FND_MESSAGE.Set_Name('AHL','AHL_MC_COMP_INVALID_HEADER');
252 FND_MSG_PUB.ADD;
253 END IF;
254
255 OPEN get_item_comp_det(p_item_composition_id,l_Detail_Rec_Type.item_comp_detail_id);
256 FETCH get_item_comp_det INTO l_dummy;
257 IF get_item_comp_det%NOTFOUND THEN
258 FND_MESSAGE.Set_Name('AHL','AHL_MC_COMP_DETAIL_NO_EXIST');
259 FND_MSG_PUB.ADD;
260 END IF;
261 CLOSE get_item_comp_det;
262
263 END IF;
264
265
266
267 IF l_Detail_Rec_Type.item_group_name IS NOT NULL
268 AND l_Detail_Rec_Type.item_group_id IS NOT NULL THEN
269 OPEN validate_item_group(l_Detail_Rec_Type.item_group_name,
270 l_Detail_Rec_Type.item_group_id);
271 FETCH validate_item_group INTO l_dummy;
272 IF validate_item_group%NOTFOUND THEN
273 FND_MESSAGE.Set_Name('AHL','AHL_MC_COMP_IG_INVALID');
274 FND_MESSAGE.set_token('ITEM_GRP',l_Detail_Rec_Type.item_group_name);
275 FND_MSG_PUB.ADD;
276 END IF;
277 CLOSE validate_item_group;
278
279 IF l_Detail_Rec_Type.operation_flag ='C' THEN
280 OPEN item_group_exists(l_Detail_Rec_Type.item_group_id,
281 p_item_composition_id);
282 FETCH item_group_exists INTO l_dummy;
283 IF item_group_exists%FOUND THEN
284 FND_MESSAGE.Set_Name('AHL','AHL_MC_ITEM_COMP_IG_EXISTS');
285 FND_MESSAGE.set_token('ITEM_GRP',l_Detail_Rec_Type.item_group_name);
286 FND_MESSAGE.set_token('INV_ITEM',p_inv_item_name);
287
288 FND_MSG_PUB.ADD;
289 END IF;
290 CLOSE item_group_exists;
291 END IF;
292 END IF;
293
294
295 IF l_Detail_Rec_Type.INVENTORY_ITEM_ID IS NOT NULL THEN
296 Validate_InventoryID(l_Detail_Rec_Type.inventory_item_id ,
297 l_Detail_Rec_Type.inventory_org_id ,
298 'DETAIL',
299 l_Detail_Rec_Type.inventory_master_org_id
300 ) ;
301 IF p_item_composition_id IS NOT NULL THEN
302 OPEN inv_item_exists(l_Detail_Rec_Type.inventory_item_id,
303 l_Detail_Rec_Type.inventory_master_org_id ,
304 p_item_composition_id);
305 FETCH inv_item_exists INTO l_dummy;
306 IF inv_item_exists%FOUND THEN
307 IF l_Detail_Rec_Type.operation_flag ='C' THEN
308 FND_MESSAGE.Set_Name('AHL','AHL_MC_ITEM_COMP_INV_EXISTS');
309 FND_MESSAGE.set_token('INV_ITM',l_Detail_Rec_Type.inventory_item_name);
310 FND_MESSAGE.set_token('INV_ITEM',p_inv_item_name);
311 FND_MSG_PUB.ADD;
312 END IF;
313 ELSE
314 IF l_Detail_Rec_Type.operation_flag ='M' THEN
315 FND_MESSAGE.Set_Name('AHL','AHL_MC_ITEM_COMP_INV_NOEXISTS');
316 FND_MSG_PUB.ADD;
317 END IF;
318 END IF;
319 CLOSE inv_item_exists;
320 END IF;
321
322
323 END IF;
324 IF l_Detail_Rec_Type.operation_flag <>'D' THEN
325 Validate_Qty_UOM(p_uom_code => l_Detail_Rec_Type.uom_code,
326 p_quantity => l_Detail_Rec_Type.quantity,
327 p_inventory_item_id => l_Detail_Rec_Type.inventory_item_id,
328 p_inventory_org_id => l_Detail_Rec_Type.inventory_org_id,
329 p_inv_segment => l_Detail_Rec_Type.inventory_item_name,
330 p_item_group_name => l_Detail_Rec_Type.item_group_name );
331 END IF;
332
333 IF (l_Detail_Rec_Type.inventory_item_id IS NULL AND
334 l_Detail_Rec_Type.item_group_name IS NULL AND
335 l_Detail_Rec_Type.operation_flag <>'D') OR
336 (l_Detail_Rec_Type.inventory_item_id IS NOT NULL AND
337 l_Detail_Rec_Type.item_group_name IS NOT NULL )
338 THEN
339 FND_MESSAGE.Set_Name('AHL','AHL_MC_COMP_ASSOS_NULL');
340 FND_MSG_PUB.ADD;
341 END IF;
342
343 IF l_Detail_Rec_Type.item_composition_id <> p_item_composition_id THEN
344 FND_MESSAGE.Set_Name('AHL','AHL_MC_COMP_HEADER_MISMATCH');
345 FND_MSG_PUB.ADD;
346 END IF;
347
348 p_x_Detail_Rec_Type := l_Detail_Rec_Type;
349
350
351 END;
352
353 -- Start of Comments --
354 -- Procedure name : Create_Item_Composition
355 -- Type : Private
356 -- Function : Creates Item Composition for Trackable Items in ahl_item_compositions.
357 -- Also creates item-group and Non-Trackable Item association in ahl_comp_details table.
358 -- Pre-reqs :
359 -- Parameters :
360 -- Standard IN Parameters :
361 -- p_api_version IN NUMBER Required
362 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
363 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
364 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
365 --
366 -- Standard OUT Parameters :
367 -- x_return_status OUT VARCHAR2 Required
368 -- x_msg_count OUT NUMBER Required
369 -- x_msg_data OUT VARCHAR2 Required
370 -- Item Header Composition Record :
371 -- inventory_item_id required.
372 -- inventory_item_name required.
373 -- inventory_org_id required.
374 -- inventory_org_code required.
375 -- operation_flag required to be 'C'.(Create)
376 -- Item Associations Record :
377 -- item_group_id Required. ( If inventory_item_id Non Trackable Item is NUll)
378 -- item_group_name Required.
379 -- inventory_item_id Required. ( If item group is NUll) Item Should be non trackable.
380 -- inventory_item_name Required.
381 -- inventory_org_id Required.
382 -- inventory_org_code Required.
383 -- operation_flag Required to be 'C'.(Create)
384 -- End of Comments --
385
386 PROCEDURE Create_Item_Composition(
387 p_api_version IN NUMBER,
388 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
389 p_commit IN VARCHAR2 := FND_API.G_FALSE,
390 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
391 x_return_status OUT NOCOPY VARCHAR2,
392 x_msg_count OUT NOCOPY NUMBER,
393 x_msg_data OUT NOCOPY VARCHAR2,
394 p_x_ic_header_rec IN OUT NOCOPY AHL_MC_ITEM_COMP_PVT.Header_Rec_Type,
395 p_x_det_tbl IN OUT NOCOPY AHL_MC_ITEM_COMP_PVT.Det_Tbl_Type
396 )
397
398 AS
399
400 CURSOR item_composition_dup(p_inventory_item_id IN NUMBER ,
401 p_master_org_id IN NUMBER)
402 IS
403 SELECT 'x'
404 FROM AHL_ITEM_COMPOSITIONS
405 WHERE INVENTORY_ITEM_ID = p_inventory_item_id
406 AND INVENTORY_MASTER_ORG_ID = p_master_org_id;
407 --AND TRUNC(NVL(EFFECTIVE_END_DATE,sysdate)) >= TRUNC(sysdate);
408
409 l_dummy varchar2(1);
410 l_item_composition_id NUMBER;
411 l_user_id NUMBER;
412
413 BEGIN
414
415 SAVEPOINT Create_Item_Composition;
416
417
418 -- Standard call to check for call compatibility.
419
420 IF FND_API.to_boolean(p_init_msg_list) THEN
421 FND_MSG_PUB.initialize;
422 END IF;
423
424 -- Initialize API return status to success
425
426 x_return_status :=FND_API.G_RET_STS_SUCCESS;
427
428 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
429 THEN
430 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
431 'ahl.plsql.'||G_PKG_NAME||'.Create_Item_Composition', 'Begin of Create_Item_Composition');
432 END IF;
433
434
435
436 Validate_InventoryID(p_x_ic_header_rec.INVENTORY_ITEM_ID ,
437 p_x_ic_header_rec.INVENTORY_ORG_ID ,
438 'HEADER',
439 p_x_ic_header_rec.INVENTORY_MASTER_ORG_ID
440 ) ;
441
442 OPEN item_composition_dup(p_x_ic_header_rec.INVENTORY_ITEM_ID,
443 p_x_ic_header_rec.INVENTORY_MASTER_ORG_ID);
444 FETCH item_composition_dup INTO l_dummy;
445 IF item_composition_dup%FOUND THEN
446 FND_MESSAGE.Set_Name('AHL','AHL_MC_ITEM_COMP_EXISTS');
447 FND_MESSAGE.Set_Token('INV_ITEM',p_x_ic_header_rec.inventory_item_name);
448 FND_MSG_PUB.ADD;
449 RAISE FND_API.G_EXC_ERROR;
450 END IF;
451
452 CLOSE item_composition_dup;
453
454
455 Select AHL_ITEM_COMPOSITIONS_S.NEXTVAL
456 INTO l_item_composition_id
457 FROM DUAL;
458
459 l_user_id := to_number(fnd_global.USER_ID);
460
461 INSERT INTO AHL_ITEM_COMPOSITIONS(
462 ITEM_COMPOSITION_ID ,
463 INVENTORY_ITEM_ID ,
464 INVENTORY_MASTER_ORG_ID,
465 DRAFT_FLAG ,
466 APPROVAL_STATUS_CODE ,
467 EFFECTIVE_END_DATE ,
468 LINK_COMP_ID ,
469 LAST_UPDATE_DATE ,
470 LAST_UPDATED_BY ,
471 CREATION_DATE ,
472 CREATED_BY ,
473 LAST_UPDATE_LOGIN ,
474 OBJECT_VERSION_NUMBER ,
475 SECURITY_GROUP_ID ,
476 ATTRIBUTE_CATEGORY ,
477 ATTRIBUTE1 ,
478 ATTRIBUTE2 ,
479 ATTRIBUTE3 ,
480 ATTRIBUTE4 ,
481 ATTRIBUTE5 ,
482 ATTRIBUTE6 ,
483 ATTRIBUTE7 ,
484 ATTRIBUTE8 ,
485 ATTRIBUTE9 ,
486 ATTRIBUTE10 ,
487 ATTRIBUTE11 ,
488 ATTRIBUTE12 ,
489 ATTRIBUTE13 ,
490 ATTRIBUTE14 ,
491 ATTRIBUTE15 )
492
493 VALUES
494 (
495 l_item_composition_id,
496 p_x_ic_header_rec.INVENTORY_ITEM_ID ,
497 p_x_ic_header_rec.INVENTORY_MASTER_ORG_ID,
498 'N',
499 'DRAFT',
500 NULL,
501 NULL,
502 sysdate,
503 l_user_id,
504 sysdate,
505 l_user_id ,
506 to_number(fnd_global.LOGIN_ID) ,
507 1 ,
508 NULL,
509 p_x_ic_header_rec.ATTRIBUTE_CATEGORY,
510 p_x_ic_header_rec.ATTRIBUTE1,
511 p_x_ic_header_rec.ATTRIBUTE2,
512 p_x_ic_header_rec.ATTRIBUTE3,
513 p_x_ic_header_rec.ATTRIBUTE4,
514 p_x_ic_header_rec.ATTRIBUTE5,
515 p_x_ic_header_rec.ATTRIBUTE6,
516 p_x_ic_header_rec.ATTRIBUTE7,
517 p_x_ic_header_rec.ATTRIBUTE8,
518 p_x_ic_header_rec.ATTRIBUTE9,
519 p_x_ic_header_rec.ATTRIBUTE10,
520 p_x_ic_header_rec.ATTRIBUTE11,
521 p_x_ic_header_rec.ATTRIBUTE12,
522 p_x_ic_header_rec.ATTRIBUTE13,
523 p_x_ic_header_rec.ATTRIBUTE14,
524 p_x_ic_header_rec.ATTRIBUTE15);
525
526
527 -- Validate the Item Composition Details
528
529 FOR I IN 1..p_x_det_tbl.count
530 LOOP
531
532
533 Validate_Item_Comp_Det( p_item_composition_id => p_x_ic_header_rec.item_composition_id ,
534 p_inv_item_name => p_x_ic_header_rec.inventory_item_name,
535 p_x_Detail_Rec_Type => p_x_det_tbl(I)
536 );
537
538 x_msg_count := FND_MSG_PUB.count_msg;
539
540 IF x_msg_count = 0 THEN
541
542 Create_Line_Item (p_item_composition_id => l_item_composition_id,
543 p_x_comp_det_rec => p_x_det_tbl(I)
544 );
545
546 END IF;
547
548 END LOOP;
549
550
551 -- Check Error Message stack.
552 x_msg_count := FND_MSG_PUB.count_msg;
553 IF x_msg_count > 0 THEN
554 RAISE FND_API.G_EXC_ERROR;
555 END IF;
556
557
558 p_x_ic_header_rec.ITEM_COMPOSITION_ID := l_item_composition_id;
559
560 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
561 THEN
562 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
563 'ahl.plsql.'||G_PKG_NAME||'.Create_Item_Composition', 'End of Create_Item_Composition');
564 END IF;
565
566
567 EXCEPTION
568 WHEN FND_API.G_EXC_ERROR THEN
569 x_return_status := FND_API.G_RET_STS_ERROR;
570 Rollback to Create_Item_Composition;
571 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
572 p_data => x_msg_data,
573 p_encoded => fnd_api.g_false);
574
575 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
576 THEN
577 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
578 'ahl.plsql.'||G_PKG_NAME||'.Create_Item_Composition', 'Error in Create_Item_Composition');
579 END IF;
580
581
582 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
583 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
584 Rollback to Create_Item_Composition;
585 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
586 p_data => x_msg_data,
587 p_encoded => fnd_api.g_false);
588
589 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
590 THEN
591 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
592 'ahl.plsql.'||G_PKG_NAME||'.Create_Item_Composition', 'Unexpected Error in Create_Item_Composition');
593 END IF;
594
595 WHEN OTHERS THEN
596 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
597 Rollback to Create_Item_Composition;
598 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
599 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
600 p_procedure_name => 'Create_Item_Composition',
601 p_error_text => SUBSTR(SQLERRM,1,240));
602 END IF;
603 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
604 p_data => x_msg_data,
605 p_encoded => fnd_api.g_false);
606
607 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
608 THEN
609 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
610 'ahl.plsql.'||G_PKG_NAME||'.Create_Item_Composition', 'Unknown Error in Create_Item_Composition');
611 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
612 'ahl.plsql.'||G_PKG_NAME||'.Create_Item_Composition', SQLERRM);
613
614 END IF;
615
616
617 END Create_Item_Composition;
618
619 -- Start of Comments --
620 -- Procedure name : Modify_Item_Composition
621 -- Type : Private
622 -- Function : Modifies Item Composition for Trackable Items in ahl_item_compositions.
623 -- Also creates,modifies item-group and Non-Trackable Item association in ahl_comp_details table.
624 -- Pre-reqs :
625 -- Parameters :
626 -- Standard IN Parameters :
627 -- p_api_version IN NUMBER Required
628 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
629 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
630 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
631 --
632 -- Standard OUT Parameters :
633 -- x_return_status OUT VARCHAR2 Required
634 -- x_msg_count OUT NUMBER Required
635 -- x_msg_data OUT VARCHAR2 Required
636 -- Item Header Composition Record :
637 -- inventory_item_id required.
638 -- inventory_item_name required.
639 -- inventory_org_id required.
640 -- inventory_org_code required.
641 -- operation_flag required to be 'M'.(Create)
642 -- Item Associations Record :
643 -- item_group_id Required. ( If inventory_item_id Non Trackable Item is NUll)
644 -- item_group_name Required.
645 -- inventory_item_id Required. ( If item group is NUll) Item Should be non trackable.
646 -- inventory_item_name Required.
647 -- inventory_org_id Required.
648 -- inventory_org_code Required.
649 -- operation_flag Required to be 'C'.(Create)
650 -- End of Comments --
651
652 PROCEDURE Modify_Item_Composition(
653 p_api_version IN NUMBER,
654 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
655 p_commit IN VARCHAR2 := FND_API.G_FALSE,
656 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
657 x_return_status OUT NOCOPY VARCHAR2,
658 x_msg_count OUT NOCOPY NUMBER,
659 x_msg_data OUT NOCOPY VARCHAR2,
660 p_x_ic_header_rec IN OUT NOCOPY AHL_MC_ITEM_COMP_PVT.Header_Rec_Type,
661 p_x_det_tbl IN OUT NOCOPY AHL_MC_ITEM_COMP_PVT.Det_Tbl_Type
662 )
663
664 AS
665
666 CURSOR item_composition_det(p_item_composition_id IN NUMBER)
667 IS
668 SELECT
669 item_composition_id ,
670 inventory_item_id ,
671 inventory_master_org_id,
672 draft_flag ,
673 approval_status_code ,
674 effective_end_date ,
675 link_comp_id ,
676 last_update_date ,
677 last_updated_by ,
678 creation_date ,
679 created_by ,
680 last_update_login ,
681 object_version_number ,
682 security_group_id ,
683 attribute_category ,
684 attribute1 ,
685 attribute2 ,
686 attribute3 ,
687 attribute4 ,
688 attribute5 ,
689 attribute6 ,
690 attribute7 ,
691 attribute8 ,
692 attribute9 ,
693 attribute10 ,
694 attribute11 ,
695 attribute12 ,
696 attribute13 ,
697 attribute14 ,
698 attribute15
699 FROM AHL_ITEM_COMPOSITIONS
700 WHERE ITEM_COMPOSITION_ID = p_item_composition_id;
701
702 l_dummy varchar2(1);
703
704 l_item_composition_rec item_composition_det%ROWTYPE;
705
706 BEGIN
707
708 SAVEPOINT Modify_Item_Composition;
709
710
711 -- Standard call to check for call compatibility.
712
713 IF FND_API.to_boolean(p_init_msg_list) THEN
714 FND_MSG_PUB.initialize;
715 END IF;
716
717 -- Initialize API return status to success
718
719 x_return_status :=FND_API.G_RET_STS_SUCCESS;
720
721 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
722 THEN
723 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
724 'ahl.plsql.'||G_PKG_NAME||'.Modify_Item_Composition', 'Begin of Modify_Item_Composition');
725 END IF;
726
727
728 OPEN item_composition_det(p_x_ic_header_rec.item_composition_id
729 );
730 FETCH item_composition_det INTO l_item_composition_rec;
731 IF item_composition_det%NOTFOUND THEN
732 FND_MESSAGE.Set_Name('AHL','AHL_MC_ITEM_COMP_NOT_EXISTS');
733 FND_MSG_PUB.ADD;
734 RAISE FND_API.G_EXC_ERROR;
735 END IF;
736
737 CLOSE item_composition_det;
738
739
740 IF l_item_composition_rec.object_version_number <> p_x_ic_header_rec.object_version_number THEN
741 FND_MESSAGE.Set_Name('AHL','AHL_COM_RECORD_CHANGED');
742 FND_MSG_PUB.ADD;
743 RAISE FND_API.G_EXC_ERROR;
744 END IF;
745
746
747 IF l_item_composition_rec.approval_status_code NOT IN ('DRAFT','APPROVAL_REJECTED') THEN
748 FND_MESSAGE.Set_Name('AHL','AHL_MC_COMP_STATUS_NO_EDIT');
749 FND_MSG_PUB.ADD;
750 RAISE FND_API.G_EXC_ERROR;
751 END IF;
752
753
754 IF (l_item_composition_rec.approval_status_code = 'APPROVAL_REJECTED') THEN
755 l_item_composition_rec.approval_status_code := 'DRAFT';
756 END IF;
757
758
759
760 UPDATE AHL_ITEM_COMPOSITIONS
761 SET
762 APPROVAL_STATUS_CODE = l_item_composition_rec.approval_status_code ,
763 LAST_UPDATE_DATE = sysdate,
764 LAST_UPDATED_BY = to_number(fnd_global.USER_ID),
765 LAST_UPDATE_LOGIN =to_number(fnd_global.LOGIN_ID) ,
766 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER+1,
767 SECURITY_GROUP_ID =NULL,
768 ATTRIBUTE_CATEGORY = p_x_ic_header_rec.ATTRIBUTE_CATEGORY,
769 ATTRIBUTE1 = p_x_ic_header_rec.ATTRIBUTE1,
770 ATTRIBUTE2 = p_x_ic_header_rec.ATTRIBUTE2,
771 ATTRIBUTE3 = p_x_ic_header_rec.ATTRIBUTE3,
772 ATTRIBUTE4 = p_x_ic_header_rec.ATTRIBUTE4,
773 ATTRIBUTE5 = p_x_ic_header_rec.ATTRIBUTE5,
774 ATTRIBUTE6 = p_x_ic_header_rec.ATTRIBUTE6,
775 ATTRIBUTE7 = p_x_ic_header_rec.ATTRIBUTE7,
776 ATTRIBUTE8 = p_x_ic_header_rec.ATTRIBUTE8,
777 ATTRIBUTE9 = p_x_ic_header_rec.ATTRIBUTE9,
778 ATTRIBUTE10 = p_x_ic_header_rec.ATTRIBUTE10,
779 ATTRIBUTE11 = p_x_ic_header_rec.ATTRIBUTE11,
780 ATTRIBUTE12 = p_x_ic_header_rec.ATTRIBUTE12,
781 ATTRIBUTE13 = p_x_ic_header_rec.ATTRIBUTE13,
782 ATTRIBUTE14 = p_x_ic_header_rec.ATTRIBUTE14,
783 ATTRIBUTE15 = p_x_ic_header_rec.ATTRIBUTE15
784 WHERE ITEM_COMPOSITION_ID = p_x_ic_header_rec.ITEM_COMPOSITION_ID
785 AND OBJECT_VERSION_NUMBER = p_x_ic_header_rec.object_version_number;
786
787
788 -- Validate the Item Composition Details
789
790 FOR I IN p_x_det_tbl.FIRST..p_x_det_tbl.LAST
791 LOOP
792
793 Validate_Item_Comp_Det( p_item_composition_id => p_x_ic_header_rec.item_composition_id ,
794 P_inv_item_name => p_x_ic_header_rec.inventory_item_name,
795 p_x_Detail_Rec_Type => p_x_det_tbl(I)
796 );
797
798 x_msg_count := FND_MSG_PUB.count_msg;
799
800 IF x_msg_count = 0 THEN
801
802 IF p_x_det_tbl(I).operation_flag = 'C' THEN
803
804 Create_Line_Item (p_item_composition_id => p_x_ic_header_rec.ITEM_COMPOSITION_ID,
805 p_x_comp_det_rec => p_x_det_tbl(I)
806 );
807
808 ELSIF p_x_det_tbl(I).operation_flag = 'M' THEN
809
810 Update_Line_Item (p_item_composition_id => p_x_ic_header_rec.ITEM_COMPOSITION_ID,
811 p_x_comp_det_rec => p_x_det_tbl(I)
812 );
813
814
815 ELSIF p_x_det_tbl(I).operation_flag = 'D' THEN
816
817 Delete_Line_Item (p_item_composition_id => p_x_ic_header_rec.ITEM_COMPOSITION_ID,
818 p_x_comp_det_rec => p_x_det_tbl(I)
819 );
820
821 END IF;
822
823 END IF;
824
825 END LOOP;
826
827 -- Check Error Message stack.
828 x_msg_count := FND_MSG_PUB.count_msg;
829 IF x_msg_count > 0 THEN
830 RAISE FND_API.G_EXC_ERROR;
831 END IF;
832
833
834 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
835 THEN
836 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
837 'ahl.plsql.'||G_PKG_NAME||'.Modify_Item_Composition', 'End of Modify_Item_Composition');
838 END IF;
839
840
841
842 EXCEPTION
843 WHEN FND_API.G_EXC_ERROR THEN
844 x_return_status := FND_API.G_RET_STS_ERROR;
845 Rollback to Modify_Item_Composition;
846 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
847 p_data => x_msg_data,
848 p_encoded => fnd_api.g_false);
849
850 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
851 THEN
852 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
853 'ahl.plsql.'||G_PKG_NAME||'.Modify_Item_Composition', 'Error in Modify_Item_Composition');
854 END IF;
855
856
857 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
858 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
859 Rollback to Modify_Item_Composition;
860 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
861 p_data => x_msg_data,
862 p_encoded => fnd_api.g_false);
863
864 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
865 THEN
866 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
867 'ahl.plsql.'||G_PKG_NAME||'.Modify_Item_Composition', 'Unexpected Error in Modify_Item_Composition');
868 END IF;
869
870 WHEN OTHERS THEN
871 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
872 Rollback to Modify_Item_Composition;
873 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
874 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
875 p_procedure_name => 'Modify_Item_Composition',
876 p_error_text => SUBSTR(SQLERRM,1,240));
877 END IF;
878 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
879 p_data => x_msg_data,
880 p_encoded => fnd_api.g_false);
881
882 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
883 THEN
884 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
885 'ahl.plsql.'||G_PKG_NAME||'.Modify_Item_Composition', 'Unknown Error in Modify_Item_Composition');
886 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
887 'ahl.plsql.'||G_PKG_NAME||'.Modify_Item_Composition', SQLERRM);
888
889 END IF;
890
891 END Modify_Item_Composition;
892
893 -- Start of Comments --
894 -- Procedure name : Delete_Item_Composition
895 -- Type : Private
896 -- Function : Deletes Item Composition for Trackable Items in ahl_item_compositions.
897 -- Also deletes association in ahl_comp_details table.
898 -- Incase of Complete status Item Composition it Expires it.
899 -- Pre-reqs :
900 -- Parameters :
901 -- Standard IN Parameters :
902 -- p_api_version IN NUMBER Required
903 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
904 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
905 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
906 --
907 -- Standard OUT Parameters :
908 -- x_return_status OUT VARCHAR2 Required
909 -- x_msg_count OUT NUMBER Required
910 -- x_msg_data OUT VARCHAR2 Required
911 -- Item Header Composition Record :
912 -- p_item_composition_ID Required
913 -- p_object_version_number Required.
914 -- End of Comments --
915
916 PROCEDURE Delete_Item_Composition (
917 p_api_version IN NUMBER,
918 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
919 p_commit IN VARCHAR2 := FND_API.G_FALSE,
920 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
921 x_return_status OUT NOCOPY VARCHAR2,
922 x_msg_count OUT NOCOPY NUMBER,
923 x_msg_data OUT NOCOPY VARCHAR2,
924 p_item_composition_ID IN NUMBER ,
925 p_object_version_number IN NUMBER
926 )
927
928 AS
929
930 CURSOR item_composition_det(p_item_composition_id IN NUMBER)
931 IS
932 SELECT
933 item_composition_id ,
934 inventory_item_id ,
935 inventory_master_org_id,
936 draft_flag ,
937 approval_status_code ,
938 effective_end_date ,
939 link_comp_id ,
940 last_update_date ,
941 last_updated_by ,
942 creation_date ,
943 created_by ,
944 last_update_login ,
945 object_version_number ,
946 security_group_id ,
947 attribute_category ,
948 attribute1 ,
949 attribute2 ,
950 attribute3 ,
951 attribute4 ,
952 attribute5 ,
953 attribute6 ,
954 attribute7 ,
955 attribute8 ,
956 attribute9 ,
957 attribute10 ,
958 attribute11 ,
959 attribute12 ,
960 attribute13 ,
961 attribute14 ,
962 attribute15
963 FROM AHL_ITEM_COMPOSITIONS
964 WHERE ITEM_COMPOSITION_ID = p_item_composition_id;
965
966 CURSOR disposition_exist(p_item_composition_id IN NUMBER)
967 IS
968 -- Modified Cursor Below for Perf Fix - Bug 4913935
969 -- SELECT 'x'
970 -- FROM ahl_route_effectivities_v
971 -- WHERE ITEM_COMPOSITION_ID = p_item_composition_id;
972 SELECT 'x'
973 FROM ahl_route_effectivities re, AHL_ITEM_COMP_V icd
974 WHERE icd.ITEM_COMPOSITION_ID = p_item_composition_id
975 AND re.inventory_item_id = ICD.inventory_item_id
976 AND re.INVENTORY_MASTER_ORG_ID = ICD.INVENTORY_MASTER_ORG_ID
977 AND ICD.APPROVAL_STATUS_CODE = 'COMPLETE';
978
979 CURSOR item_rev_exists(p_item_composition_id IN NUMBER)
980 IS
981 SELECT item_composition_id
982 FROM AHL_ITEM_COMPOSITIONS
983 WHERE link_comp_id = p_item_composition_id;
984
985
986 l_dummy varchar2(1);
987 l_rev_item_composition_id NUMBER;
988
989 l_item_composition_rec item_composition_det%ROWTYPE;
990
991 BEGIN
992
993 SAVEPOINT Delete_Item_Composition;
994
995
996 -- Standard call to check for call compatibility.
997
998 IF FND_API.to_boolean(p_init_msg_list) THEN
999 FND_MSG_PUB.initialize;
1000 END IF;
1001
1002 -- Initialize API return status to success
1003
1004 x_return_status :=FND_API.G_RET_STS_SUCCESS;
1005
1006 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
1007 THEN
1008 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1009 'ahl.plsql.'||G_PKG_NAME||'.Delete_Item_Composition', 'Begin of Delete_Item_Composition');
1010 END IF;
1011
1012
1013 OPEN item_composition_det(p_item_composition_id
1014 );
1015 FETCH item_composition_det INTO l_item_composition_rec;
1016 IF item_composition_det%NOTFOUND THEN
1017 FND_MESSAGE.Set_Name('AHL','AHL_MC_ITEM_COMP_NOT_EXISTS');
1018 FND_MSG_PUB.ADD;
1019 RAISE FND_API.G_EXC_ERROR;
1020 END IF;
1021
1022 CLOSE item_composition_det;
1023
1024
1025 IF l_item_composition_rec.object_version_number <> p_object_version_number THEN
1026 FND_MESSAGE.Set_Name('AHL','AHL_COM_RECORD_CHANGED');
1027 FND_MSG_PUB.ADD;
1028 RAISE FND_API.G_EXC_ERROR;
1029 END IF;
1030
1031
1032 IF l_item_composition_rec.approval_status_code = 'APPROVAL_PENDING' THEN
1033 FND_MESSAGE.Set_Name('AHL','AHL_MC_COMP_STATUS_NO_DELETE');
1034 FND_MSG_PUB.ADD;
1035 RAISE FND_API.G_EXC_ERROR;
1036 END IF;
1037
1038
1039 -- Check Error Message stack.
1040 x_msg_count := FND_MSG_PUB.count_msg;
1041 IF x_msg_count > 0 THEN
1042 RAISE FND_API.G_EXC_ERROR;
1043 END IF;
1044
1045
1046 IF l_item_composition_rec.approval_status_code = 'COMPLETE' THEN
1047
1048 OPEN disposition_exist(p_item_composition_id
1049 );
1050 FETCH disposition_exist INTO l_dummy;
1051 IF disposition_exist%FOUND THEN
1052 FND_MESSAGE.Set_Name('AHL','AHL_MC_COMP_DISP_EXISTS');
1053 FND_MSG_PUB.ADD;
1054 RAISE FND_API.G_EXC_ERROR;
1055 END IF;
1056
1057 CLOSE disposition_exist;
1058
1059 OPEN item_rev_exists(p_item_composition_id);
1060 FETCH item_rev_exists INTO l_rev_item_composition_id;
1061 IF item_rev_exists%FOUND THEN
1062 FND_MESSAGE.Set_Name('AHL','AHL_MC_COMP_REV_EXISTS');
1063 FND_MSG_PUB.ADD;
1064 RAISE FND_API.G_EXC_ERROR;
1065 END IF;
1066 CLOSE item_rev_exists;
1067
1068
1069 IF l_item_composition_rec.effective_end_date IS NOT NULL AND
1070 TRUNC(l_item_composition_rec.effective_end_date) <= TRUNC(SYSDATE) THEN
1071 FND_MESSAGE.Set_Name('AHL','AHL_MC_COMP_STATUS_NO_DELETE');
1072 FND_MSG_PUB.ADD;
1073 RAISE FND_API.G_EXC_ERROR;
1074 END IF;
1075
1076
1077
1078 UPDATE AHL_ITEM_COMPOSITIONS
1079 SET
1080 LAST_UPDATE_DATE = sysdate,
1081 LAST_UPDATED_BY = to_number(fnd_global.USER_ID),
1082 LAST_UPDATE_LOGIN =to_number(fnd_global.LOGIN_ID) ,
1083 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER+1,
1084 SECURITY_GROUP_ID =NULL,
1085 EFFECTIVE_END_DATE = sysdate -1
1086 WHERE ITEM_COMPOSITION_ID = p_item_composition_ID
1087 AND OBJECT_VERSION_NUMBER = p_object_version_number;
1088
1089
1090 ELSIF l_item_composition_rec.approval_status_code IN ('DRAFT','APPROVAL_REJECTED') THEN
1091
1092
1093 DELETE FROM AHL_ITEM_COMP_DETAILS
1094 WHERE ITEM_COMPOSITION_ID = p_item_composition_id;
1095
1096 DELETE FROM AHL_ITEM_COMPOSITIONS
1097 WHERE ITEM_COMPOSITION_ID = p_item_composition_id;
1098
1099 END IF;
1100
1101
1102
1103 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
1104 THEN
1105 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1106 'ahl.plsql.'||G_PKG_NAME||'.Delete_Item_Composition', 'End of Delete_Item_Composition');
1107 END IF;
1108
1109 EXCEPTION
1110 WHEN FND_API.G_EXC_ERROR THEN
1111 x_return_status := FND_API.G_RET_STS_ERROR;
1112 Rollback to Delete_Item_Composition;
1113 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1114 p_data => x_msg_data,
1115 p_encoded => fnd_api.g_false);
1116
1117 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
1118 THEN
1119 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1120 'ahl.plsql.'||G_PKG_NAME||'.Delete_Item_Composition', 'Error in Delete_Item_Composition');
1121 END IF;
1122
1123
1124 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1125 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1126 Rollback to Delete_Item_Composition;
1127 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1128 p_data => x_msg_data,
1129 p_encoded => fnd_api.g_false);
1130
1131 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
1132 THEN
1133 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1134 'ahl.plsql.'||G_PKG_NAME||'.Delete_Item_Composition', 'Unexpected Error in Delete_Item_Composition');
1135 END IF;
1136
1137 WHEN OTHERS THEN
1138 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1139 Rollback to Delete_Item_Composition;
1140 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1141 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
1142 p_procedure_name => 'Delete_Item_Composition',
1143 p_error_text => SUBSTR(SQLERRM,1,240));
1144 END IF;
1145 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1146 p_data => x_msg_data,
1147 p_encoded => fnd_api.g_false);
1148
1149 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
1150 THEN
1151 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1152 'ahl.plsql.'||G_PKG_NAME||'.Delete_Item_Composition', 'Unknown Error in Delete_Item_Composition');
1153 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1154 'ahl.plsql.'||G_PKG_NAME||'.Delete_Item_Composition', SQLERRM);
1155
1156 END IF;
1157
1158
1159 END Delete_Item_Composition;
1160
1161
1162 -- Start of Comments --
1163 -- Procedure name : Reopen_Item_Composition
1164 -- Type : Private
1165 -- Function : Re-Open'ss Item Composition for Trackable Items in ahl_item_compositions.
1166 --
1167 -- Pre-reqs :
1168 -- Parameters :
1169 -- Standard IN Parameters :
1170 -- p_api_version IN NUMBER Required
1171 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
1172 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
1173 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
1174 --
1175 -- Standard OUT Parameters :
1176 -- x_return_status OUT VARCHAR2 Required
1177 -- x_msg_count OUT NUMBER Required
1178 -- x_msg_data OUT VARCHAR2 Required
1179 -- Item Header Composition Record :
1180 -- p_item_composition_ID Required
1181 -- p_object_version_number Required.
1182 -- End of Comments --
1183
1184 PROCEDURE Reopen_Item_Composition (
1185 p_api_version IN NUMBER,
1186 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1187 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1188 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1189 x_return_status OUT NOCOPY VARCHAR2,
1190 x_msg_count OUT NOCOPY NUMBER,
1191 x_msg_data OUT NOCOPY VARCHAR2,
1192 p_item_composition_ID IN NUMBER ,
1193 p_object_version_number IN NUMBER
1194
1195 )
1196
1197 AS
1198
1199 CURSOR item_composition_det(p_item_composition_id IN NUMBER)
1200 IS
1201 SELECT
1202 item_composition_id ,
1203 inventory_item_id ,
1204 inventory_master_org_id,
1205 draft_flag ,
1206 approval_status_code ,
1207 effective_end_date ,
1208 link_comp_id ,
1209 last_update_date ,
1210 last_updated_by ,
1211 creation_date ,
1212 created_by ,
1213 last_update_login ,
1214 object_version_number ,
1215 security_group_id ,
1216 attribute_category ,
1217 attribute1 ,
1218 attribute2 ,
1219 attribute3 ,
1220 attribute4 ,
1221 attribute5 ,
1222 attribute6 ,
1223 attribute7 ,
1224 attribute8 ,
1225 attribute9 ,
1226 attribute10 ,
1227 attribute11 ,
1228 attribute12 ,
1229 attribute13 ,
1230 attribute14 ,
1231 attribute15
1232 FROM AHL_ITEM_COMPOSITIONS
1233 WHERE ITEM_COMPOSITION_ID = p_item_composition_id;
1234
1235 /*
1236 CURSOR item_composition_exists(p_inv_item_id IN NUMBER,
1237 p_inv_master_org_id IN NUMBER)
1238 IS
1239 SELECT 'x'
1240 FROM AHL_ITEM_COMPOSITIONS
1241 WHERE inventory_item_id = p_inv_item_id
1242 AND inventory_master_org_id = p_inv_master_org_id
1243 AND TRUNC(NVL(EFFECTIVE_END_DATE,sysdate)) >= TRUNC(sysdate);
1244 */
1245
1246 l_dummy varchar2(1);
1247
1248 l_item_composition_rec item_composition_det%ROWTYPE;
1249
1250
1251 BEGIN
1252
1253 SAVEPOINT Reopen_Item_Composition;
1254
1255
1256 -- Standard call to check for call compatibility.
1257
1258 IF FND_API.to_boolean(p_init_msg_list) THEN
1259 FND_MSG_PUB.initialize;
1260 END IF;
1261
1262 -- Initialize API return status to success
1263
1264 x_return_status :=FND_API.G_RET_STS_SUCCESS;
1265
1266 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
1267 THEN
1268 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1269 'ahl.plsql.'||G_PKG_NAME||'.Reopen_Item_Composition', 'Begin of Reopen_Item_Composition');
1270 END IF;
1271
1272 OPEN item_composition_det(p_item_composition_ID);
1273 FETCH item_composition_det INTO l_item_composition_rec;
1274 IF item_composition_det%NOTFOUND THEN
1275 FND_MESSAGE.Set_Name('AHL','AHL_MC_ITEM_COMP_NOT_EXISTS');
1276 FND_MSG_PUB.ADD;
1277 RAISE FND_API.G_EXC_ERROR;
1278 END IF;
1279
1280 CLOSE item_composition_det;
1281
1282
1283 IF l_item_composition_rec.object_version_number <> p_object_version_number THEN
1284 FND_MESSAGE.Set_Name('AHL','AHL_COM_RECORD_CHANGED');
1285 FND_MSG_PUB.ADD;
1286 RAISE FND_API.G_EXC_ERROR;
1287 END IF;
1288
1289 /*
1290 OPEN item_composition_exists(l_item_composition_rec.inventory_item_id,
1291 l_item_composition_rec.inventory_master_org_id);
1292 FETCH item_composition_exists INTO l_dummy;
1293 IF item_composition_exists%FOUND THEN
1294 FND_MESSAGE.Set_Name('AHL','AHL_MC_ITEM_COMP_EXISTS');
1295 FND_MESSAGE.Set_Token('INV_ITEM',p_x_ic_header_rec.inventory_item_id);
1296 FND_MSG_PUB.ADD;
1297 RAISE FND_API.G_EXC_ERROR;
1298 END IF;
1299
1300 CLOSE item_composition_exists;
1301 */
1302
1303
1304 IF NVL(l_item_composition_rec.effective_end_date,TRUNC(SYSDATE)) >= TRUNC(SYSDATE) THEN
1305 FND_MESSAGE.Set_Name('AHL','AHL_MC_ITEM_COMP_OPEN');
1306 FND_MSG_PUB.ADD;
1307 RAISE FND_API.G_EXC_ERROR;
1308 END IF;
1309
1310
1311 -- Check Error Message stack.
1312 x_msg_count := FND_MSG_PUB.count_msg;
1313 IF x_msg_count > 0 THEN
1314 RAISE FND_API.G_EXC_ERROR;
1315 END IF;
1316
1317
1318
1319 UPDATE AHL_ITEM_COMPOSITIONS
1320 SET
1321 LAST_UPDATE_DATE = sysdate,
1322 LAST_UPDATED_BY = to_number(fnd_global.USER_ID),
1323 LAST_UPDATE_LOGIN =to_number(fnd_global.LOGIN_ID) ,
1324 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER+1,
1325 SECURITY_GROUP_ID =NULL,
1326 EFFECTIVE_END_DATE = null
1327 WHERE ITEM_COMPOSITION_ID = p_item_composition_ID
1328 AND OBJECT_VERSION_NUMBER = p_object_version_number;
1329
1330
1331 -- Standard check of p_commit
1332 IF FND_API.TO_BOOLEAN(p_commit) THEN
1333 COMMIT WORK;
1334 END IF;
1335
1336
1337 EXCEPTION
1338 WHEN FND_API.G_EXC_ERROR THEN
1339 x_return_status := FND_API.G_RET_STS_ERROR;
1340 Rollback to Reopen_Item_Composition;
1341 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1342 p_data => x_msg_data,
1343 p_encoded => fnd_api.g_false);
1344
1345 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
1346 THEN
1347 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1348 'ahl.plsql.'||G_PKG_NAME||'.Reopen_Item_Composition', 'Error in Reopen_Item_Composition');
1349 END IF;
1350
1351
1352 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1353 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1354 Rollback to Reopen_Item_Composition;
1355 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1356 p_data => x_msg_data,
1357 p_encoded => fnd_api.g_false);
1358
1359 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
1360 THEN
1361 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1362 'ahl.plsql.'||G_PKG_NAME||'.Reopen_Item_Composition', 'Unexpected Error in Reopen_Item_Composition');
1363 END IF;
1364
1365 WHEN OTHERS THEN
1366 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1367 Rollback to Reopen_Item_Composition;
1368 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1369 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
1370 p_procedure_name => 'Reopen_Item_Composition',
1371 p_error_text => SUBSTR(SQLERRM,1,240));
1372 END IF;
1373 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1374 p_data => x_msg_data,
1375 p_encoded => fnd_api.g_false);
1376
1377 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
1378 THEN
1379 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1380 'ahl.plsql.'||G_PKG_NAME||'.Reopen_Item_Composition', 'Unknown Error in Reopen_Item_Composition');
1381 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1382 'ahl.plsql.'||G_PKG_NAME||'.Reopen_Item_Composition', SQLERRM);
1383 END IF;
1384
1385
1386 END Reopen_Item_Composition;
1387
1388
1389
1390 PROCEDURE Create_Line_Item (p_item_composition_id IN NUMBER,
1391 p_x_comp_det_rec IN OUT NOCOPY AHL_MC_ITEM_COMP_PVT.Detail_Rec_Type
1392 )
1393
1394 AS
1395
1396 l_item_comp_detail_id NUMBER;
1397 l_user_id NUMBER;
1398
1399 BEGIN
1400
1401 SELECT AHL_ITEM_COMP_DETAILS_S.NEXTVAL
1402 INTO l_item_comp_detail_id
1403 FROM DUAL;
1404
1405 l_user_id := to_number(fnd_global.USER_ID);
1406
1407 IF p_item_composition_id IS NOT NULL THEN
1408
1409 INSERT INTO AHL_ITEM_COMP_DETAILS(
1410 ITEM_COMP_DETAIL_ID ,
1411 ITEM_COMPOSITION_ID ,
1412 ITEM_GROUP_ID ,
1413 INVENTORY_ITEM_ID ,
1414 INVENTORY_MASTER_ORG_ID,
1415 UOM_CODE ,
1416 QUANTITY ,
1417 EFFECTIVE_END_DATE ,
1418 LINK_COMP_DETL_ID ,
1419 LAST_UPDATE_DATE ,
1420 LAST_UPDATED_BY ,
1421 CREATION_DATE ,
1422 CREATED_BY ,
1423 LAST_UPDATE_LOGIN ,
1424 OBJECT_VERSION_NUMBER ,
1425 SECURITY_GROUP_ID ,
1426 ATTRIBUTE_CATEGORY ,
1427 ATTRIBUTE1 ,
1428 ATTRIBUTE2 ,
1429 ATTRIBUTE3 ,
1430 ATTRIBUTE4 ,
1431 ATTRIBUTE5 ,
1432 ATTRIBUTE6 ,
1433 ATTRIBUTE7 ,
1434 ATTRIBUTE8 ,
1435 ATTRIBUTE9 ,
1436 ATTRIBUTE10 ,
1437 ATTRIBUTE11 ,
1438 ATTRIBUTE12 ,
1439 ATTRIBUTE13 ,
1440 ATTRIBUTE14 ,
1441 ATTRIBUTE15 )
1442
1443 VALUES
1444 (
1445 l_item_comp_detail_id ,
1446 p_item_composition_id ,
1447 p_x_comp_det_rec.ITEM_GROUP_ID ,
1448 p_x_comp_det_rec.INVENTORY_ITEM_ID ,
1449 p_x_comp_det_rec.INVENTORY_MASTER_ORG_ID,
1450 p_x_comp_det_rec.UOM_CODE ,
1451 p_x_comp_det_rec.QUANTITY ,
1452 NULL ,
1453 NULL,
1454 sysdate,
1455 l_user_id ,
1456 sysdate,
1457 l_user_id,
1458 to_number(fnd_global.LOGIN_ID),
1459 1,
1460 NULL,
1461 p_x_comp_det_rec.ATTRIBUTE_CATEGORY ,
1462 p_x_comp_det_rec.ATTRIBUTE1 ,
1463 p_x_comp_det_rec.ATTRIBUTE2 ,
1464 p_x_comp_det_rec.ATTRIBUTE3 ,
1465 p_x_comp_det_rec.ATTRIBUTE4 ,
1466 p_x_comp_det_rec.ATTRIBUTE5 ,
1467 p_x_comp_det_rec.ATTRIBUTE6 ,
1468 p_x_comp_det_rec.ATTRIBUTE7 ,
1469 p_x_comp_det_rec.ATTRIBUTE8 ,
1470 p_x_comp_det_rec.ATTRIBUTE9 ,
1471 p_x_comp_det_rec.ATTRIBUTE10 ,
1472 p_x_comp_det_rec.ATTRIBUTE11 ,
1473 p_x_comp_det_rec.ATTRIBUTE12 ,
1474 p_x_comp_det_rec.ATTRIBUTE13 ,
1475 p_x_comp_det_rec.ATTRIBUTE14 ,
1476 p_x_comp_det_rec.ATTRIBUTE15
1477 ) RETURNING
1478 ITEM_COMP_DETAIL_ID ,
1479 ITEM_COMPOSITION_ID
1480 INTO
1481 p_x_comp_det_rec.ITEM_COMP_DETAIL_ID,
1482 p_x_comp_det_rec.ITEM_COMPOSITION_ID
1483 ;
1484
1485 END IF;
1486
1487
1488
1489 END;
1490
1491
1492 PROCEDURE Update_Line_Item (p_item_composition_id IN NUMBER,
1493 p_x_comp_det_rec IN OUT NOCOPY AHL_MC_ITEM_COMP_PVT.Detail_Rec_Type
1494 )
1495
1496 AS
1497
1498 BEGIN
1499
1500 UPDATE AHL_ITEM_COMP_DETAILS
1501 SET
1502 ITEM_GROUP_ID =p_x_comp_det_rec.ITEM_GROUP_ID,
1503 INVENTORY_ITEM_ID =p_x_comp_det_rec.INVENTORY_ITEM_ID,
1504 INVENTORY_MASTER_ORG_ID = p_x_comp_det_rec.INVENTORY_MASTER_ORG_ID,
1505 UOM_CODE =p_x_comp_det_rec.UOM_CODE ,
1506 QUANTITY =p_x_comp_det_rec.QUANTITY,
1507 LAST_UPDATE_DATE =sysdate,
1508 LAST_UPDATED_BY = to_number(fnd_global.USER_ID),
1509 LAST_UPDATE_LOGIN = to_number(fnd_global.LOGIN_ID),
1510 OBJECT_VERSION_NUMBER =object_version_number+1,
1511 SECURITY_GROUP_ID =NULL,
1512 ATTRIBUTE_CATEGORY =p_x_comp_det_rec.ATTRIBUTE_CATEGORY,
1513 ATTRIBUTE1 =p_x_comp_det_rec.ATTRIBUTE1,
1514 ATTRIBUTE2 =p_x_comp_det_rec.ATTRIBUTE2,
1515 ATTRIBUTE3 =p_x_comp_det_rec.ATTRIBUTE3,
1516 ATTRIBUTE4 =p_x_comp_det_rec.ATTRIBUTE4,
1517 ATTRIBUTE5 =p_x_comp_det_rec.ATTRIBUTE5,
1518 ATTRIBUTE6 =p_x_comp_det_rec.ATTRIBUTE6,
1519 ATTRIBUTE7 =p_x_comp_det_rec.ATTRIBUTE7,
1520 ATTRIBUTE8 =p_x_comp_det_rec.ATTRIBUTE8,
1521 ATTRIBUTE9 =p_x_comp_det_rec.ATTRIBUTE9,
1522 ATTRIBUTE10 =p_x_comp_det_rec.ATTRIBUTE10,
1523 ATTRIBUTE11 =p_x_comp_det_rec.ATTRIBUTE11,
1524 ATTRIBUTE12 =p_x_comp_det_rec.ATTRIBUTE12,
1525 ATTRIBUTE13 =p_x_comp_det_rec.ATTRIBUTE13,
1526 ATTRIBUTE14 =p_x_comp_det_rec.ATTRIBUTE14,
1527 ATTRIBUTE15 =p_x_comp_det_rec.ATTRIBUTE15
1528 WHERE
1529 ITEM_COMP_DETAIL_ID = p_x_comp_det_rec.item_comp_detail_id
1530 AND ITEM_COMPOSITION_ID = p_item_composition_id ;
1531
1532
1533 END Update_Line_Item;
1534
1535
1536
1537 PROCEDURE Delete_Line_Item (p_item_composition_id IN NUMBER,
1538 p_x_comp_det_rec IN OUT NOCOPY AHL_MC_ITEM_COMP_PVT.Detail_Rec_Type
1539 )
1540
1541 AS
1542
1543 BEGIN
1544
1545 DELETE FROM AHL_ITEM_COMP_DETAILS
1546 WHERE ITEM_COMP_DETAIL_ID = p_x_comp_det_rec.item_comp_detail_id
1547 AND ITEM_COMPOSITION_ID = p_item_composition_id;
1548
1549
1550 END Delete_Line_Item;
1551
1552
1553
1554 -- Start of Comments --
1555 -- Procedure name : Initiate_Item_Comp_Approval
1556 -- Type : Private
1557 -- Function : Intiates Approval Process for Item groups
1558 --
1559 -- Pre-reqs :
1560 -- Parameters :
1561 -- Standard IN Parameters :
1562 -- p_api_version IN NUMBER Required
1563 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
1564 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
1565 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
1566 --
1567 -- Standard OUT Parameters :
1568 -- x_return_status OUT VARCHAR2 Required
1569 -- x_msg_count OUT NUMBER Required
1570 -- x_msg_data OUT VARCHAR2 Required
1571 --
1572 -- Item_Composition_id Required.
1573 -- Object_version_number Required.
1574 -- Approval type Required.
1575 --
1576 -- Enhancement 115.10
1577 -- End of Comments --
1578 PROCEDURE Initiate_Item_Comp_Approval (
1579 p_api_version IN NUMBER,
1580 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1581 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1582 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1583 x_return_status OUT NOCOPY VARCHAR2,
1584 x_msg_count OUT NOCOPY NUMBER,
1585 x_msg_data OUT NOCOPY VARCHAR2,
1586 p_Item_Composition_id IN NUMBER,
1587 p_object_version_number IN NUMBER,
1588 p_approval_type IN VARCHAR2
1589 )
1590
1591
1592 IS
1593
1594 l_api_name CONSTANT VARCHAR2(30) := 'Initiate_Item_Comp_Approval';
1595 l_api_version CONSTANT NUMBER := 1.0;
1596
1597 l_counter NUMBER:=0;
1598 l_status VARCHAR2(30);
1599 l_object VARCHAR2(30):='ICWF';
1600 l_approval_type VARCHAR2(100):='CONCEPT';
1601 l_active VARCHAR2(50) := 'N';
1602 l_process_name VARCHAR2(50);
1603 l_item_type VARCHAR2(50);
1604 l_return_status VARCHAR2(50);
1605 l_msg_count NUMBER;
1606 l_msg_data VARCHAR2(2000);
1607 l_activity_id NUMBER:=p_Item_Composition_id;
1608 l_Status VARCHAR2(1);
1609 l_init_msg_list VARCHAR2(10):=FND_API.G_TRUE;
1610 l_object_Version_number NUMBER:=nvl(p_object_Version_number,0);
1611
1612 l_upd_status VARCHAR2(50);
1613 l_rev_status VARCHAR2(50);
1614 l_approval_status VARCHAR2(30) := 'APPROVED';
1615
1616
1617
1618 CURSOR get_Item_comp_Det(c_item_comp_id NUMBER)
1619 is
1620 Select item_composition_id,
1621 approval_status_code,
1622 object_version_number,
1623 concatenated_segments
1624 From ahl_item_comp_v
1625 Where item_composition_id = c_item_comp_id;
1626
1627
1628
1629 l_item_comp_rec get_Item_comp_Det%rowtype;
1630
1631
1632 l_msg VARCHAR2(30);
1633 l_dummy VARCHAR2(1);
1634 l_count NUMBER ;
1635
1636
1637 BEGIN
1638 SAVEPOINT Initiate_Item_Comp_Approval;
1639
1640 -- Standard call to check for call compatibility.
1641
1642 IF FND_API.to_boolean(l_init_msg_list) THEN
1643 FND_MSG_PUB.initialize;
1644 END IF;
1645
1646 -- Initialize API return status to success
1647
1648 x_return_status :=FND_API.G_RET_STS_SUCCESS;
1649
1650 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
1651 THEN
1652 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1653 'ahl.plsql.'||G_PKG_NAME||'.Initiate_Item_Comp_Approval', 'Begin Initiate_Item_Comp_Approval');
1654 END IF;
1655
1656
1657
1658
1659 -- Start work Flow Process
1660 ahl_utility_pvt.get_wf_process_name(
1661 p_object =>l_object,
1662 x_active =>l_active,
1663 x_process_name =>l_process_name ,
1664 x_item_type =>l_item_type,
1665 x_return_status=>l_return_status,
1666 x_msg_count =>l_msg_count,
1667 x_msg_data =>l_msg_data);
1668
1669 Select count(*)
1670 into l_count
1671 from ahl_item_comp_details
1672 where item_composition_id = p_Item_Composition_id;
1673
1674
1675 IF p_object_Version_number is null or p_object_Version_number=FND_API.G_MISS_NUM
1676 THEN
1677 FND_MESSAGE.SET_NAME('AHL','AHL_MC_OBJ_VERSION_NULL');
1678 FND_MSG_PUB.ADD;
1679 END IF;
1680
1681 IF p_Item_Composition_id is null or p_Item_Composition_id = FND_API.G_MISS_NUM
1682 THEN
1683 FND_MESSAGE.SET_NAME('AHL','AHL_MC_OBJECT_ID_NULL');
1684 FND_MSG_PUB.ADD;
1685 ELSE
1686
1687 OPEN get_Item_comp_Det(p_Item_Composition_id);
1688 FETCH get_Item_comp_Det INTO l_item_comp_rec;
1689 CLOSE get_Item_comp_Det;
1690
1691
1692 select count(*)
1693 into l_count
1694 from ahl_item_comp_details
1695 where item_composition_id = p_Item_Composition_id;
1696
1697 IF l_count < 1 THEN
1698 FND_MESSAGE.SET_NAME('AHL','AHL_MC_IC_EMPTY');
1699 FND_MESSAGE.Set_Token('ITEM_COMP',l_item_comp_rec.concatenated_segments);
1700 FND_MSG_PUB.ADD;
1701 RAISE FND_API.G_EXC_ERROR;
1702 END IF;
1703
1704
1705 IF p_approval_type = 'APPROVE'
1706 THEN
1707 IF l_item_comp_rec.approval_status_code='DRAFT' or
1708 l_item_comp_rec.approval_status_code='APPROVAL_REJECTED'
1709 THEN
1710 l_upd_status := 'APPROVAL_PENDING';
1711 ELSE
1712 FND_MESSAGE.SET_NAME('AHL','AHL_MC_IC_STAT_NOT_DRFT');
1713 FND_MSG_PUB.ADD;
1714 END IF;
1715 ELSE
1716 FND_MESSAGE.SET_NAME('AHL','AHL_APPR_TYPE_CODE_MISSING');
1717 FND_MSG_PUB.ADD;
1718 END IF;
1719
1720 END IF;
1721
1722 l_msg_count := FND_MSG_PUB.count_msg;
1723
1724 IF l_msg_count > 0
1725 THEN
1726 X_msg_count := l_msg_count;
1727 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1728 RAISE FND_API.G_EXC_ERROR;
1729 END IF;
1730
1731
1732 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
1733 THEN
1734 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1735 'ahl.plsql.'||G_PKG_NAME||'.Initiate_Item_Comp_Approval', 'Updating Item group');
1736 END IF;
1737
1738 Update ahl_item_compositions
1739 Set APPROVAL_STATUS_CODE=l_upd_status,
1740 OBJECT_VERSION_NUMBER=OBJECT_VERSION_NUMBER+1
1741 Where ITEM_COMPOSITION_ID = p_Item_Composition_id
1742 and OBJECT_VERSION_NUMBER=p_object_Version_number;
1743
1744 IF sql%rowcount=0
1745 THEN
1746 FND_MESSAGE.SET_NAME('AHL','AHL_COM_RECORD_CHANGED');
1747 FND_MSG_PUB.ADD;
1748 RAISE FND_API.G_EXC_ERROR;
1749 END IF;
1750
1751 IF l_ACTIVE='Y'
1752 THEN
1753
1754
1755 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
1756 THEN
1757 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1758 'ahl.plsql.'||G_PKG_NAME||'.Initiate_Item_Comp_Approval', 'Calling ahl_generic_aprv_pvt.start_wf_process');
1759 END IF;
1760
1761 AHL_GENERIC_APRV_PVT.START_WF_PROCESS(
1762 P_OBJECT =>l_object,
1763 P_ACTIVITY_ID =>l_activity_id,
1764 P_APPROVAL_TYPE =>'CONCEPT',
1765 P_OBJECT_VERSION_NUMBER =>l_object_version_number+1,
1766 P_ORIG_STATUS_CODE =>'ACTIVE',
1767 P_NEW_STATUS_CODE =>'APPROVED',
1768 P_REJECT_STATUS_CODE =>'REJECTED',
1769 P_REQUESTER_USERID =>fnd_global.user_id,
1770 P_NOTES_FROM_REQUESTER =>NULL,
1771 P_WORKFLOWPROCESS =>'AHL_GEN_APPROVAL',
1772 P_ITEM_TYPE =>'AHLGAPP');
1773 ELSE
1774 ahl_mc_item_comp_pvt.approve_item_composiiton
1775 (
1776 p_api_version =>l_api_version,
1777 -- p_init_msg_list =>l_init_msg_list,
1778 -- p_commit =>l_commit,
1779 -- p_validation_level =>NULL ,
1780 -- p_default =>NULL ,
1781 p_module_type =>NULL,
1782 x_return_status =>l_return_status,
1783 x_msg_count =>l_msg_count ,
1784 x_msg_data =>l_msg_data ,
1785 p_appr_status =>l_approval_status,
1786 P_ITEM_COMP_ID =>p_Item_Composition_id,
1787 p_object_version_number =>p_object_Version_number+1
1788 );
1789
1790 END IF ;
1791
1792 l_msg_count := FND_MSG_PUB.count_msg;
1793
1794 IF l_msg_count > 0
1795 THEN
1796 X_msg_count := l_msg_count;
1797 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1798 RAISE FND_API.G_EXC_ERROR;
1799 END IF;
1800
1801
1802 -- Standard check of p_commit
1803 IF FND_API.TO_BOOLEAN(p_commit) THEN
1804 COMMIT WORK;
1805 END IF;
1806
1807 -- Standard call to get message count and if count is 1, get message info
1808 FND_MSG_PUB.Count_And_Get
1809 ( p_count => x_msg_count,
1810 p_data => x_msg_data,
1811 p_encoded => fnd_api.g_false);
1812
1813 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
1814 THEN
1815 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1816 'ahl.plsql.'||G_PKG_NAME||'.Initiate_Item_Comp_Approval', 'End of Initiate_Item_Comp_Approval');
1817 END IF;
1818
1819
1820
1821 EXCEPTION
1822 WHEN FND_API.G_EXC_ERROR THEN
1823 x_return_status := FND_API.G_RET_STS_ERROR;
1824 Rollback to Initiate_Item_Comp_Approval;
1825 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1826 p_data => x_msg_data,
1827 p_encoded => fnd_api.g_false);
1828
1829 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
1830 THEN
1831 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1832 'ahl.plsql.'||G_PKG_NAME||'.Initiate_Item_Comp_Approval', 'Error in Initiate_Item_Comp_Approval');
1833 END IF;
1834
1835
1836 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1837 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1838 Rollback to Initiate_Item_Comp_Approval;
1839 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1840 p_data => x_msg_data,
1841 p_encoded => fnd_api.g_false);
1842
1843 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
1844 THEN
1845 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1846 'ahl.plsql.'||G_PKG_NAME||'.Initiate_Item_Comp_Approval', 'Unexpected Error in Initiate_Item_Comp_Approval');
1847 END IF;
1848
1849 WHEN OTHERS THEN
1850 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1851 Rollback to Initiate_Item_Comp_Approval;
1852 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1853 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
1854 p_procedure_name => 'Initiate_Item_Comp_Approval',
1855 p_error_text => SUBSTR(SQLERRM,1,240));
1856 END IF;
1857 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1858 p_data => x_msg_data,
1859 p_encoded => fnd_api.g_false);
1860
1861 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
1862 THEN
1863 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1864 'ahl.plsql.'||G_PKG_NAME||'.Initiate_Item_Comp_Approval', 'Unknown Error in Initiate_Item_Comp_Approval');
1865 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1866 'ahl.plsql.'||G_PKG_NAME||'.Initiate_Item_Comp_Approval', SQLERRM);
1867
1868 END IF;
1869
1870
1871 END Initiate_Item_Comp_Approval;
1872
1873
1874 -- Start of Comments --
1875 -- Procedure name : Create_Item_Comp_Revision
1876 -- Type : Private
1877 -- Function : To create a New Revision of Item group
1878 --
1879 -- Pre-reqs :
1880 -- Parameters :
1881 -- Standard IN Parameters :
1882 -- p_api_version IN NUMBER Required
1883 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
1884 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
1885 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
1886 --
1887 -- Standard OUT Parameters :
1888 -- x_return_status OUT VARCHAR2 Required
1889 -- x_msg_count OUT NUMBER Required
1890 -- x_msg_data OUT VARCHAR2 Required
1891
1892 -- Item_Comp_id Required.
1893 -- Object_version_number Required.
1894 -- Enhancement 115.10
1895 --
1896 -- End of Comments --
1897
1898 PROCEDURE Create_Item_Comp_Revision (
1899 p_api_version IN NUMBER,
1900 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1901 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1902 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1903 P_DEFAULT IN VARCHAR2 := FND_API.G_FALSE,
1904 P_MODULE_TYPE IN VARCHAR2,
1905 x_return_status OUT NOCOPY VARCHAR2,
1906 x_msg_count OUT NOCOPY NUMBER,
1907 x_msg_data OUT NOCOPY VARCHAR2,
1908 p_Item_comp_id IN NUMBER,
1909 p_object_version_number IN NUMBER,
1910 x_Item_comp_id OUT NOCOPY NUMBER
1911 ) AS
1912
1913 cursor get_item_comp_det(c_item_comp_id in Number)
1914 Is
1915 Select
1916 item_composition_id,
1917 approval_status_code,
1918 draft_flag,
1919 inventory_item_id,
1920 inventory_master_org_id,
1921 effective_end_date,
1922 object_version_number
1923 from ahl_item_compositions
1924 Where item_composition_id = c_item_comp_id;
1925
1926 l_item_comp_det get_item_comp_det%rowtype;
1927
1928
1929 cursor get_revision_info(c_item_comp_id in Number)
1930 is
1931 Select 'x'
1932 from ahl_item_compositions
1933 where link_comp_id = c_item_comp_id;
1934
1935 l_dummy VARCHAR2(1);
1936 l_msg_count Number;
1937 l_init_msg_list VARCHAR2(10):=FND_API.G_TRUE;
1938 l_item_comp_id Number;
1939 l_last_update_login NUMBER;
1940 l_last_updated_by NUMBER;
1941 l_rowid UROWID;
1942 l_item_composition_id NUMBER;
1943 l_created_by NUMBER;
1944
1945 BEGIN
1946
1947
1948 SAVEPOINT Create_Item_Comp_Revision;
1949
1950 -- Standard call to check for call compatibility.
1951
1952 IF FND_API.to_boolean(l_init_msg_list) THEN
1953 FND_MSG_PUB.initialize;
1954 END IF;
1955
1956 -- Initialize API return status to success
1957
1958 x_return_status :=FND_API.G_RET_STS_SUCCESS;
1959
1960 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
1961 THEN
1962 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1963 'ahl.plsql.'||G_PKG_NAME||'.Create_Item_Comp_Revision', 'Begin of Create_Item_Comp_Revision');
1964 END IF;
1965
1966
1967
1968 OPEN get_item_comp_det(p_Item_comp_id);
1969 Fetch get_item_comp_det into l_item_comp_det;
1970 IF get_item_comp_det%NOTFOUND THEN
1971 FND_MESSAGE.SET_NAME('AHL','AHL_MC_OBJECT_ID_NULL');
1972 FND_MSG_PUB.ADD;
1973 END IF;
1974 close get_item_comp_det;
1975
1976
1977 IF l_item_comp_det.approval_status_code <> 'COMPLETE'
1978 OR l_item_comp_det.effective_end_date IS NOT NULL
1979 THEN
1980 FND_MESSAGE.SET_NAME('AHL','AHL_MC_IC_STAT_NOT_COMP');
1981 FND_MSG_PUB.ADD;
1982 END IF;
1983
1984
1985 IF l_item_comp_det.object_version_number <> p_object_version_number
1986 THEN
1987 FND_MESSAGE.SET_NAME('AHL','AHL_COM_RECORD_CHANGED');
1988 FND_MSG_PUB.ADD;
1989 END IF;
1990
1991
1992 OPEN get_revision_info(p_Item_comp_id);
1993 FETCH get_revision_info INTO l_dummy;
1994 IF get_revision_info%FOUND THEN
1995 FND_MESSAGE.SET_NAME('AHL','AHL_MC_IC_REVISION_EXIST');
1996 FND_MSG_PUB.ADD;
1997 END IF;
1998 CLOSE get_revision_info;
1999
2000
2001
2002 l_msg_count := FND_MSG_PUB.count_msg;
2003
2004 IF l_msg_count > 0
2005 THEN
2006 X_msg_count := l_msg_count;
2007 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2008 RAISE FND_API.G_EXC_ERROR;
2009 END IF;
2010
2011 -- Sequence Number for the New Revision.
2012
2013
2014 l_last_updated_by := to_number(fnd_global.USER_ID);
2015 l_last_update_login := to_number(fnd_global.LOGIN_ID);
2016 l_created_by := to_number(fnd_global.user_id);
2017
2018
2019 Select AHL_ITEM_COMPOSITIONS_S.NEXTVAL
2020 INTO l_item_composition_id
2021 FROM DUAL;
2022
2023
2024 INSERT INTO AHL_ITEM_COMPOSITIONS(
2025 ITEM_COMPOSITION_ID ,
2026 INVENTORY_ITEM_ID ,
2027 INVENTORY_MASTER_ORG_ID,
2028 DRAFT_FLAG ,
2029 APPROVAL_STATUS_CODE ,
2030 EFFECTIVE_END_DATE ,
2031 LINK_COMP_ID ,
2032 LAST_UPDATE_DATE ,
2033 LAST_UPDATED_BY ,
2034 CREATION_DATE ,
2035 CREATED_BY ,
2036 LAST_UPDATE_LOGIN ,
2037 OBJECT_VERSION_NUMBER ,
2038 SECURITY_GROUP_ID ,
2039 ATTRIBUTE_CATEGORY ,
2040 ATTRIBUTE1 ,
2041 ATTRIBUTE2 ,
2042 ATTRIBUTE3 ,
2043 ATTRIBUTE4 ,
2044 ATTRIBUTE5 ,
2045 ATTRIBUTE6 ,
2046 ATTRIBUTE7 ,
2047 ATTRIBUTE8 ,
2048 ATTRIBUTE9 ,
2049 ATTRIBUTE10 ,
2050 ATTRIBUTE11 ,
2051 ATTRIBUTE12 ,
2052 ATTRIBUTE13 ,
2053 ATTRIBUTE14 ,
2054 ATTRIBUTE15 )
2055
2056 SELECT l_item_composition_id,
2057 inventory_item_id ,
2058 inventory_master_org_id,
2059 draft_flag ,
2060 'DRAFT' ,
2061 effective_end_date ,
2062 p_Item_comp_id ,
2063 sysdate ,
2064 l_last_updated_by ,
2065 sysdate ,
2066 l_created_by ,
2067 l_last_update_login ,
2068 1 ,
2069 security_group_id ,
2070 attribute_category ,
2071 attribute1 ,
2072 attribute2 ,
2073 attribute3 ,
2074 attribute4 ,
2075 attribute5 ,
2076 attribute6 ,
2077 attribute7 ,
2078 attribute8 ,
2079 attribute9 ,
2080 attribute10 ,
2081 attribute11 ,
2082 attribute12 ,
2083 attribute13 ,
2084 attribute14 ,
2085 attribute15
2086 FROM ahl_item_compositions
2087 WHERE item_composition_id = p_Item_comp_id
2088 AND object_version_number = p_object_version_number
2089 AND effective_end_date is null;
2090
2091
2092
2093 x_Item_comp_id := l_item_composition_id;
2094
2095
2096
2097
2098 IF p_Item_comp_id IS NOT NULL THEN
2099
2100 INSERT INTO AHL_ITEM_COMP_DETAILS(
2101 ITEM_COMP_DETAIL_ID ,
2102 ITEM_COMPOSITION_ID ,
2103 ITEM_GROUP_ID ,
2104 INVENTORY_ITEM_ID ,
2105 INVENTORY_MASTER_ORG_ID,
2106 UOM_CODE ,
2107 QUANTITY ,
2108 EFFECTIVE_END_DATE ,
2109 LINK_COMP_DETL_ID ,
2110 LAST_UPDATE_DATE ,
2111 LAST_UPDATED_BY ,
2112 CREATION_DATE ,
2113 CREATED_BY ,
2114 LAST_UPDATE_LOGIN ,
2115 OBJECT_VERSION_NUMBER ,
2116 SECURITY_GROUP_ID ,
2117 ATTRIBUTE_CATEGORY ,
2118 ATTRIBUTE1 ,
2119 ATTRIBUTE2 ,
2120 ATTRIBUTE3 ,
2121 ATTRIBUTE4 ,
2122 ATTRIBUTE5 ,
2123 ATTRIBUTE6 ,
2124 ATTRIBUTE7 ,
2125 ATTRIBUTE8 ,
2126 ATTRIBUTE9 ,
2127 ATTRIBUTE10 ,
2128 ATTRIBUTE11 ,
2129 ATTRIBUTE12 ,
2130 ATTRIBUTE13 ,
2131 ATTRIBUTE14 ,
2132 ATTRIBUTE15 )
2133
2134 SELECT AHL_ITEM_COMP_DETAILS_S.NEXTVAL ,
2135 l_item_composition_id ,
2136 item_group_id ,
2137 inventory_item_id ,
2138 inventory_master_org_id,
2139 uom_code ,
2140 quantity ,
2141 effective_end_date ,
2142 item_comp_detail_id ,
2143 sysdate ,
2144 l_last_updated_by ,
2145 sysdate ,
2146 l_created_by ,
2147 l_last_update_login ,
2148 1 ,
2149 security_group_id ,
2150 attribute_category ,
2151 attribute1 ,
2152 attribute2 ,
2153 attribute3 ,
2154 attribute4 ,
2155 attribute5 ,
2156 attribute6 ,
2157 attribute7 ,
2158 attribute8 ,
2159 attribute9 ,
2160 attribute10 ,
2161 attribute11 ,
2162 attribute12 ,
2163 attribute13 ,
2164 attribute14 ,
2165 attribute15
2166 FROM ahl_item_comp_details
2167 WHERE item_composition_id = p_Item_comp_id
2168 AND effective_end_date is null;
2169
2170 END IF;
2171
2172 UPDATE ahl_item_compositions
2173 SET DRAFT_FLAG = 'Y',
2174 LAST_UPDATE_DATE = sysdate,
2175 LAST_UPDATED_BY = to_number(fnd_global.USER_ID),
2176 LAST_UPDATE_LOGIN =to_number(fnd_global.LOGIN_ID),
2177 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER+1,
2178 SECURITY_GROUP_ID =NULL
2179 WHERE item_composition_id = p_Item_comp_id
2180 AND object_version_number = p_object_version_number;
2181
2182
2183 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
2184 THEN
2185 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
2186 'ahl.plsql.'||G_PKG_NAME||'.Create_Item_Comp_Revision', 'End of Loop');
2187 END IF;
2188
2189 -- Standard check of p_commit
2190 IF FND_API.TO_BOOLEAN(p_commit) THEN
2191 COMMIT WORK;
2192 END IF;
2193
2194 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
2195 THEN
2196 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
2197 'ahl.plsql.'||G_PKG_NAME||'.Create_Item_Comp_Revision', 'End of Create_Item_Comp_Revision');
2198 END IF;
2199
2200
2201
2202 EXCEPTION
2203 WHEN FND_API.G_EXC_ERROR THEN
2204 x_return_status := FND_API.G_RET_STS_ERROR;
2205 Rollback to Create_Item_Comp_Revision;
2206 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2207 p_data => x_msg_data,
2208 p_encoded => fnd_api.g_false);
2209 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
2210 THEN
2211 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
2212 'ahl.plsql.'||G_PKG_NAME||'.Create_Item_Comp_Revision', 'Error in Create_Item_Comp_Revision');
2213 END IF;
2214
2215
2216 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2217 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2218 Rollback to Create_Item_Comp_Revision;
2219 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2220 p_data => x_msg_data,
2221 p_encoded => fnd_api.g_false);
2222 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
2223 THEN
2224 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
2225 'ahl.plsql.'||G_PKG_NAME||'.Create_Item_Comp_Revision', 'Unecpected Error in Create_Item_Comp_Revision');
2226 END IF;
2227
2228
2229 WHEN OTHERS THEN
2230 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2231 Rollback to Create_Item_Comp_Revision;
2232 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2233 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
2234 p_procedure_name => 'Create_Item_Comp_Revision',
2235 p_error_text => SUBSTR(SQLERRM,1,240));
2236 END IF;
2237 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2238 p_data => x_msg_data,
2239 p_encoded => fnd_api.g_false);
2240 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
2241 THEN
2242 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
2243 'ahl.plsql.'||G_PKG_NAME||'.Create_Item_Comp_Revision', 'Unknown Error in Create_Item_Comp_Revision');
2244 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
2245 'ahl.plsql.'||G_PKG_NAME||'.Create_Item_Comp_Revision',SQLERRM );
2246
2247 END IF;
2248
2249
2250
2251 END Create_Item_Comp_Revision;
2252
2253
2254
2255 -- Start of Comments --
2256 -- Procedure name : Approve_Item_Composiiton
2257 -- Type : Private
2258 -- Function : To Approve Item group will be called by approval package
2259 -- Version : Added for 115.10
2260 --
2261 -- Pre-reqs :
2262 -- Parameters :
2263 -- Standard IN Parameters :
2264 -- p_api_version IN NUMBER Required
2265 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
2266 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
2267 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
2268 --
2269 -- Standard OUT Parameters :
2270 -- x_return_status OUT VARCHAR2 Required
2271 -- x_msg_count OUT NUMBER Required
2272 -- x_msg_data OUT VARCHAR2 Required
2273
2274 -- P_appr_status Required.
2275 -- Item_Group_id Required.
2276 -- Object_version_number Required.
2277 --
2278 --
2279 -- End of Comments --
2280
2281 PROCEDURE Approve_Item_Composiiton (
2282 p_api_version IN NUMBER,
2283 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2284 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2285 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2286 P_DEFAULT IN VARCHAR2 := FND_API.G_FALSE,
2287 P_MODULE_TYPE IN VARCHAR2,
2288 x_return_status OUT NOCOPY VARCHAR2,
2289 x_msg_count OUT NOCOPY NUMBER,
2290 x_msg_data OUT NOCOPY VARCHAR2,
2291 p_appr_status IN VARCHAR2,
2292 p_Item_comp_id IN NUMBER,
2293 p_object_version_number IN NUMBER)
2294
2295 AS
2296
2297 cursor get_item_comp_det(c_item_comp_id in Number)
2298 Is Select
2299 item_composition_id,
2300 approval_status_code,
2301 draft_flag,
2302 link_comp_id,
2303 inventory_item_id,
2304 inventory_master_org_id,
2305 effective_end_date,
2306 object_version_number
2307 from ahl_item_compositions
2308 Where item_composition_id = c_item_comp_id;
2309
2310 l_item_comp_det get_item_comp_det%rowType;
2311
2312 type t_id is table of number index by binary_integer;
2313 type t_uom_code is table of varchar2(3) index by binary_integer;
2314
2315 l_item_group_id t_id;
2316 l_inventory_item_id t_id;
2317 l_inventory_master_org_id t_id;
2318 l_uom_code t_uom_code;
2319 l_quantity t_id;
2320 l_link_comp_detl_id t_id;
2321 l_object_version_number t_id;
2322
2323 l_status VARCHAR2(30);
2324 l_msg_count Number;
2325 l_init_msg_list VARCHAR2(10):=FND_API.G_TRUE;
2326 l_rowid urowid;
2327 l_action varchar2(2);
2328
2329
2330 BEGIN
2331
2332 SAVEPOINT Approve_Item_Composiiton;
2333
2334
2335 -- Standard call to check for call compatibility.
2336
2337 IF FND_API.to_boolean(l_init_msg_list) THEN
2338 FND_MSG_PUB.initialize;
2339 END IF;
2340
2341 -- Initialize API return status to success
2342
2343 x_return_status :=FND_API.G_RET_STS_SUCCESS;
2344
2345 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
2346 THEN
2347 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
2348 'ahl.plsql.ahl_mc_itemgroup_pvt.Approve_Item_Composiiton', 'Begin of Approve_Item_Composiiton');
2349 END IF;
2350
2351
2352
2353 OPEN get_item_comp_det(p_Item_comp_id);
2354 FETCH get_item_comp_det INTO l_item_comp_det;
2355 IF get_item_comp_det%NOTFOUND
2356 THEN
2357 FND_MESSAGE.SET_NAME('AHL','AHL_MC_OBJECT_ID_NULL');
2358 FND_MSG_PUB.ADD;
2359 END IF;
2360 CLOSE get_item_comp_det;
2361
2362 IF l_item_comp_det.object_version_number <> p_object_version_number
2363 THEN
2364 FND_MESSAGE.SET_NAME('AHL','AHL_COM_RECORD_CHANGED');
2365 FND_MSG_PUB.ADD;
2366 END IF;
2367
2368
2369 l_msg_count := FND_MSG_PUB.count_msg;
2370
2371 IF l_msg_count > 0
2372 THEN
2373 X_msg_count := l_msg_count;
2374 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2375 RAISE FND_API.G_EXC_ERROR;
2376 END IF;
2377
2378
2379 IF p_appr_status='APPROVED'
2380 THEN
2381 l_status:='COMPLETE';
2382 ELSE
2383 l_status:='APPROVAL_REJECTED';
2384 END IF;
2385
2386
2387 IF l_status = 'COMPLETE'
2388 THEN
2389 -- Insert record into histroy table.
2390
2391
2392
2393 IF l_item_comp_det.link_comp_id IS NULL THEN
2394
2395 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
2396 THEN
2397 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
2398 'ahl.plsql.ahl_mc_itemgroup_pvt.Approve_Item_Composiiton', 'link_comp_id is null');
2399 END IF;
2400
2401
2402 update ahl_item_compositions
2403 set approval_status_code=l_status,
2404 object_version_number = object_version_number+1
2405 where item_composition_id=l_item_comp_det.item_composition_id
2406 and object_version_number = l_item_comp_det.object_version_number;
2407
2408 l_action :='C';
2409
2410
2411 ELSE
2412
2413 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
2414 THEN
2415 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
2416 'ahl.plsql.ahl_mc_itemgroup_pvt.Approve_Item_Composiiton', 'link_comp_id is not null');
2417 END IF;
2418
2419
2420 select
2421 item_group_id ,
2422 inventory_item_id ,
2423 inventory_master_org_id,
2424 uom_code ,
2425 quantity ,
2426 link_comp_detl_id ,
2427 object_version_number
2428 bulk collect
2429 into
2430 l_item_group_id ,
2431 l_inventory_item_id ,
2432 l_inventory_master_org_id,
2433 l_uom_code ,
2434 l_quantity ,
2435 l_link_comp_detl_id ,
2436 l_object_version_number
2437 from ahl_item_comp_details
2438 where item_composition_id = l_item_comp_det.item_composition_id
2439 and link_comp_detl_id is not null
2440 and effective_end_date is null;
2441
2442
2443 FORALL I IN 1..l_object_version_number.count
2444 update ahl_item_comp_details set
2445 uom_code =l_uom_code(I),
2446 quantity =l_quantity(I),
2447 last_update_date = sysdate,
2448 last_updated_by = to_number(fnd_global.user_id),
2449 last_update_login = to_number(fnd_global.login_id),
2450 object_version_number =l_object_version_number(I)+1
2451 where ITEM_COMP_DETAIL_ID = l_link_comp_detl_id(I)
2452 and item_composition_id = l_item_comp_det.LINK_COMP_ID
2453 and NVL(item_group_id,-3) = NVL(l_item_group_id(I),-3)
2454 and NVL(inventory_item_id,-3) = NVL(l_inventory_item_id(I),-3)
2455 and NVL(inventory_master_org_id,-3)= NVL(l_inventory_master_org_id(I),-3)
2456 and effective_end_date is null;
2457
2458
2459 Update ahl_item_comp_details
2460 Set effective_end_date = sysdate-1,
2461 last_update_date =sysdate,
2462 last_updated_by = to_number(fnd_global.user_id),
2463 last_update_login = to_number(fnd_global.login_id),
2464 object_version_number =object_version_number+1
2465 Where item_composition_id=l_item_comp_det.link_comp_id
2466 and effective_end_date is null
2467 and ITEM_COMP_DETAIL_ID not in (
2468 Select link_comp_detl_id
2469 from ahl_item_comp_details
2470 where item_composition_id = l_item_comp_det.item_composition_id
2471 and link_comp_detl_id is not null);
2472
2473
2474 Update ahl_item_comp_details
2475 Set item_composition_id = l_item_comp_det.link_comp_id,
2476 last_update_date =sysdate,
2477 last_updated_by = to_number(fnd_global.user_id),
2478 last_update_login = to_number(fnd_global.login_id),
2479 object_version_number =object_version_number+1
2480 Where item_composition_id=l_item_comp_det.item_composition_id
2481 and link_comp_detl_id is null;
2482
2483
2484 Update ahl_item_compositions
2485 Set last_update_date =sysdate,
2486 last_updated_by = to_number(fnd_global.user_id),
2487 last_update_login = to_number(fnd_global.login_id),
2488 object_version_number =object_version_number+1
2489 Where item_composition_id=l_item_comp_det.link_comp_id;
2490
2491 Delete from ahl_item_comp_details
2492 where item_composition_id = l_item_comp_det.item_composition_id;
2493
2494 Delete from ahl_item_compositions
2495 Where item_composition_id=l_item_comp_det.item_composition_id;
2496
2497
2498 End if;
2499
2500
2501
2502
2503
2504 ELSIF l_status = 'APPROVAL_REJECTED' THEN
2505
2506 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
2507 THEN
2508 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
2509 'ahl.plsql.ahl_mc_itemgroup_pvt.Approve_Item_Composiiton', 'Approval Rejected');
2510 END IF;
2511
2512 update ahl_item_compositions
2513 set approval_status_code=l_status,
2514 object_version_number = object_version_number+1
2515 where item_composition_id=l_item_comp_det.item_composition_id
2516 and object_version_number = l_item_comp_det.object_version_number;
2517
2518
2519
2520
2521 End if;
2522
2523 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
2524 THEN
2525 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
2526 'ahl.plsql.ahl_mc_itemgroup_pvt.Approve_Item_Composiiton', 'End of Approve_Item_Composiiton');
2527 END IF;
2528
2529
2530
2531 EXCEPTION
2532 WHEN FND_API.G_EXC_ERROR THEN
2533 x_return_status := FND_API.G_RET_STS_ERROR;
2534 Rollback to Approve_Item_Composiiton;
2535 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2536 p_data => x_msg_data,
2537 p_encoded => fnd_api.g_false);
2538
2539 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
2540 THEN
2541 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
2542 'ahl.plsql.ahl_mc_itemgroup_pvt.Approve_Item_Composiiton', 'Error in Approve_Item_Composiiton');
2543 END IF;
2544
2545
2546 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2547 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2548 Rollback to Approve_Item_Composiiton;
2549 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2550 p_data => x_msg_data,
2551 p_encoded => fnd_api.g_false);
2552
2553 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
2554 THEN
2555 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
2556 'ahl.plsql.ahl_mc_itemgroup_pvt.Approve_Item_Composiiton', 'Unexpected Error in Approve_Item_Composiiton');
2557 END IF;
2558
2559 WHEN OTHERS THEN
2560 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2561 Rollback to Approve_Item_Composiiton;
2562 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2563 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
2564 p_procedure_name => 'Approve_Item_Composiiton',
2565 p_error_text => SUBSTR(SQLERRM,1,240));
2566 END IF;
2567 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2568 p_data => x_msg_data,
2569 p_encoded => fnd_api.g_false);
2570
2571 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
2572 THEN
2573 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
2574 'ahl.plsql.ahl_mc_itemgroup_pvt.Approve_Item_Composiiton', 'Unknown Error in Approve_Item_Composiiton');
2575 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
2576 'ahl.plsql.ahl_mc_itemgroup_pvt.Approve_Item_Composiiton', SQLERRM);
2577
2578 END IF;
2579
2580
2581 END Approve_Item_Composiiton;
2582
2583
2584
2585 End AHL_MC_ITEM_COMP_PVT;