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