[Home] [Help]
PACKAGE BODY: APPS.AHL_MC_ITEM_COMP_PUB
Source
1 PACKAGE BODY AHL_MC_ITEM_COMP_PUB AS
2 /* $Header: AHLPICXB.pls 115.2 2003/08/29 09:59:39 tamdas noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AHL_MC_ITEM_COMP_PUB';
5
6 PROCEDURE Convert_code_to_ID(p_x_item_comp_rec IN OUT NOCOPY ahl_mc_item_comp_pvt.Detail_Rec_Type);
7
8 PROCEDURE Process_Item_Composition(
9 p_api_version IN NUMBER,
10 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
11 p_commit IN VARCHAR2 := FND_API.G_FALSE,
12 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
13 p_module_type IN VARCHAR2 := NULL,
14 x_return_status OUT NOCOPY VARCHAR2,
15 x_msg_count OUT NOCOPY NUMBER,
16 x_msg_data OUT NOCOPY VARCHAR2,
17 p_x_ic_header_rec IN OUT NOCOPY AHL_MC_ITEM_COMP_PVT.Header_Rec_Type,
18 p_x_ic_det_tbl IN OUT NOCOPY AHL_MC_ITEM_COMP_PVT.Det_Tbl_Type
19 ) AS
20
21 l_init_msg_list VARCHAR2(10):=FND_API.G_TRUE;
22
23
24 CURSOR mtl_parameters_csr (p_org_code IN VARCHAR2) IS
25 SELECT organization_id
26 FROM mtl_parameters
27 WHERE organization_code = p_org_code;
28
29
30 CURSOR mtl_system_items_csr(p_inventory_item_name IN VARCHAR2,
31 p_inv_organization_id IN NUMBER) IS
32 SELECT inventory_item_id
33 FROM ahl_mtl_items_non_ou_v
34 WHERE concatenated_segments = p_inventory_item_name
35 AND inventory_org_id = p_inv_organization_id;
36
37 CURSOR mtl_segment_csr(p_inventory_item_id IN NUMBER,
38 p_inv_organization_id IN NUMBER) IS
39 SELECT concatenated_segments
40 FROM ahl_mtl_items_non_ou_v
41 WHERE inventory_item_id = p_inventory_item_id
42 AND inventory_org_id = p_inv_organization_id;
43
44 l_inv_org_id NUMBER;
45 l_inv_item_id NUMBER;
46 l_item_name VARCHAR2(2000);
47 l_msg_count NUMBER;
48
49 BEGIN
50
51 SAVEPOINT Process_Item_Composition;
52
53
54 -- Standard call to check for call compatibility.
55
56 IF FND_API.to_boolean(l_init_msg_list) THEN
57 FND_MSG_PUB.initialize;
58 END IF;
59
60 -- Initialize API return status to success
61
62 x_return_status :=FND_API.G_RET_STS_SUCCESS;
63
64 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
65 THEN
66 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
67 'ahl.plsql.'||G_PKG_NAME||'.Process_Item_Composition', 'Begin of Process_Item_Composition');
68 END IF;
69
70
71 IF p_x_ic_header_rec.operation_flag IN ('C','M') THEN
72
73 IF p_x_ic_header_rec.inventory_org_id IS NULL THEN
74
75 IF p_x_ic_header_rec.inventory_org_code IS NOT NULL THEN
76 OPEN mtl_parameters_csr (p_x_ic_header_rec.inventory_org_code);
77 FETCH mtl_parameters_csr INTO l_inv_org_id;
78 IF (mtl_parameters_csr%FOUND) THEN
79 p_x_ic_header_rec.inventory_org_id := l_inv_org_id;
80 ELSE
81 FND_MESSAGE.Set_Name('AHL','AHL_MC_ORG_INVALID');
82 FND_MESSAGE.Set_Token('ORG',p_x_ic_header_rec.inventory_org_code);
83 FND_MSG_PUB.ADD;
84 END IF;
85 ELSE
86 FND_MESSAGE.SET_NAME('AHL','AHL_MC_ORG_NULL');
87 FND_MSG_PUB.ADD;
88 END IF;
89 END IF; -- end of inventory_org_id condition.
90
91 IF p_x_ic_header_rec.inventory_item_id IS NULL THEN
92
93 IF p_x_ic_header_rec.inventory_item_name IS NOT NULL THEN
94 OPEN mtl_system_items_csr (p_x_ic_header_rec.inventory_item_name,
95 p_x_ic_header_rec.inventory_org_id);
96 FETCH mtl_system_items_csr INTO l_inv_item_id;
97 IF (mtl_system_items_csr%FOUND) THEN
98 p_x_ic_header_rec.inventory_item_id := l_inv_item_id;
99 ELSE
100 FND_MESSAGE.Set_Name('AHL','AHL_MC_INV_INVALID');
101 FND_MESSAGE.Set_Token('INV_ITEM',p_x_ic_header_rec.inventory_item_name);
102 FND_MSG_PUB.ADD;
103 END IF;
104 ELSE
105 FND_MESSAGE.SET_NAME('AHL','AHL_MC_INV_NULL');
106 FND_MSG_PUB.ADD;
107 END IF;
108 ELSIF p_x_ic_header_rec.inventory_item_id IS NOT NULL THEN
109 OPEN mtl_segment_csr(p_x_ic_header_rec.inventory_item_id,
110 p_x_ic_header_rec.inventory_org_id);
111 FETCH mtl_segment_csr INTO l_item_name;
112 IF (mtl_segment_csr%NOTFOUND) THEN
113 FND_MESSAGE.Set_Name('AHL','AHL_MC_INV_INVALID');
114 FND_MESSAGE.Set_Token('INV_ITEM',p_x_ic_header_rec.inventory_item_id);
115 FND_MSG_PUB.ADD;
116 ELSE
117 p_x_ic_header_rec.inventory_item_name := l_item_name;
118 END IF;
119 CLOSE mtl_segment_csr;
120 END IF; -- end of inventory_item_id check
121
122
123
124 END IF; --- end of flag condition
125
126 IF (p_x_ic_det_tbl.COUNT > 0) THEN
127
128 FOR I IN p_x_ic_det_tbl.first..p_x_ic_det_tbl.last
129 Loop
130
131 IF (p_module_type = 'JSP') THEN
132 p_x_ic_det_tbl(i).inventory_item_id := NULL;
133 p_x_ic_det_tbl(i).item_group_id := NULL;
134 END IF;
135 IF p_x_ic_det_tbl(i).OPERATION_FLAG <> 'D' THEN
136 Convert_code_to_ID(p_x_ic_det_tbl(i));
137
138 END IF;
139
140 End Loop;
141
142 END IF;
143
144 l_msg_count := FND_MSG_PUB.count_msg;
145
146 IF l_msg_count > 0
147 THEN
148 X_msg_count := l_msg_count;
149 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
150 RAISE FND_API.G_EXC_ERROR;
151 END IF;
152
153 IF p_x_ic_header_rec.operation_flag = 'C' THEN
154
155 ahl_mc_item_comp_pvt.Create_Item_Composition(
156 p_api_version => p_api_version,
157 --p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
158 --p_commit IN VARCHAR2 := FND_API.G_FALSE,
159 --p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
160 x_return_status => x_return_status,
161 x_msg_count => x_msg_count,
162 x_msg_data => x_msg_data,
163 p_x_ic_header_rec => p_x_ic_header_rec,
164 p_x_det_tbl => p_x_ic_det_tbl);
165
166 ELSIF p_x_ic_header_rec.operation_flag = 'M' THEN
167
168 ahl_mc_item_comp_pvt.Modify_Item_Composition(
169 p_api_version => p_api_version,
170 --p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
171 --p_commit IN VARCHAR2 := FND_API.G_FALSE,
172 --p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
173 x_return_status => x_return_status,
174 x_msg_count => x_msg_count,
175 x_msg_data => x_msg_data,
176 p_x_ic_header_rec => p_x_ic_header_rec,
177 p_x_det_tbl => p_x_ic_det_tbl);
178
179 ELSIF p_x_ic_header_rec.operation_flag = 'D' THEN
180
181 ahl_mc_item_comp_pvt.delete_item_composition(
182 p_api_version => p_api_version,
183 -- p_init_msg_list =>
184 -- p_commit =>
185 -- p_validation_level =>
186 x_return_status => x_return_status,
187 x_msg_count => x_msg_count,
188 x_msg_data => x_msg_data,
189 p_item_composition_id => p_x_ic_header_rec.item_composition_id,
190 p_object_version_number => p_x_ic_header_rec.object_version_number);
191
192 END IF;
193
194 IF FND_API.TO_BOOLEAN(p_commit) THEN
195 COMMIT WORK;
196 END IF;
197
198 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
199 THEN
200 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
201 'ahl.plsql.'||G_PKG_NAME||'.Process_Item_Composition', 'End of Process_Item_Composition');
202 END IF;
203
204
205 EXCEPTION
206 WHEN FND_API.G_EXC_ERROR THEN
207 x_return_status := FND_API.G_RET_STS_ERROR;
208 Rollback to Process_Item_Composition;
209 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
210 p_data => x_msg_data,
211 p_encoded => fnd_api.g_false);
212
213 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
214 THEN
215 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
216 'ahl.plsql.'||G_PKG_NAME||'.Process_Item_Composition', 'Error in Process_Item_Composition');
217 END IF;
218
219
220 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
221 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
222 Rollback to Process_Item_Composition;
223 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
224 p_data => x_msg_data,
225 p_encoded => fnd_api.g_false);
226
227 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
228 THEN
229 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
230 'ahl.plsql.'||G_PKG_NAME||'.Process_Item_Composition', 'Unexpected Error in Process_Item_Composition');
231 END IF;
232
233 WHEN OTHERS THEN
234 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
235 Rollback to Process_Item_Composition;
236 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
237 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
238 p_procedure_name => 'Process_Item_Composition',
239 p_error_text => SUBSTR(SQLERRM,1,240));
240 END IF;
241 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
242 p_data => x_msg_data,
243 p_encoded => fnd_api.g_false);
244
245 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
246 THEN
247 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
248 'ahl.plsql.'||G_PKG_NAME||'.Process_Item_Composition', 'Unknown Error in Process_Item_Composition');
249 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
250 'ahl.plsql.'||G_PKG_NAME||'.Process_Item_Composition', SQLERRM);
251 END IF;
252
253
254 END Process_Item_Composition;
255
256
257 PROCEDURE Convert_code_to_ID(p_x_item_comp_rec IN OUT NOCOPY ahl_mc_item_comp_pvt.Detail_Rec_Type) IS
258
259
260 -- For Item Group id.
261 CURSOR get_itemGroup_csr(p_item_group_name IN VARCHAR2) IS
262 SELECT item_group_id
263 FROM ahl_item_groups_vl
264 WHERE name = p_item_group_name
265 AND status_code = 'COMPLETE'
266 AND type_code = 'NON-TRACKED';
267
268 -- For organization id.
269 CURSOR mtl_parameters_csr (p_org_code IN VARCHAR2) IS
270 SELECT organization_id
271 FROM mtl_parameters
272 WHERE organization_code = p_org_code;
273
274 -- For inventory_item_id.
275 CURSOR mtl_system_items_csr(p_inventory_item_name IN VARCHAR2,
276 p_inv_organization_id IN NUMBER) IS
277 SELECT inventory_item_id
278 FROM ahl_mtl_items_non_ou_v
279 WHERE concatenated_segments = p_inventory_item_name
280 AND inventory_org_id = p_inv_organization_id;
281
282 -- For concatenated segments.
283 CURSOR mtl_segment_csr(p_inventory_item_id IN NUMBER,
284 p_inv_organization_id IN NUMBER) IS
285 SELECT concatenated_segments
286 FROM ahl_mtl_items_non_ou_v
287 WHERE inventory_item_id = p_inventory_item_id
288 AND inventory_org_id = p_inv_organization_id;
289
290
291
292 l_inventory_id NUMBER;
293 l_item_group_id NUMBER;
294 l_item_comp_rec ahl_mc_item_comp_pvt.Detail_Rec_Type DEFAULT p_x_item_comp_rec;
295 l_inventory_org_id NUMBER;
296 l_item_association_id NUMBER;
297 l_lookup_code fnd_lookups.lookup_code%TYPE;
298 l_return_val BOOLEAN;
299 l_concatenated_segments ahl_mtl_items_ou_v.concatenated_segments%TYPE;
300 l_dummy varchar2(200);
301
302 BEGIN
303
304 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
305 THEN
306 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
307 'ahl_mc_item_comp_pvt.Convert_InTo_ID', 'Begin of Convert_InTo_ID');
308 END IF;
309
310 IF (l_item_comp_rec.item_group_name IS NOT NULL) THEN
311 OPEN get_itemGroup_csr (l_item_comp_rec.item_group_name);
312 FETCH get_itemGroup_csr INTO l_item_group_id;
313 IF (get_itemGroup_csr%FOUND) THEN
314 l_item_comp_rec.item_group_id := l_item_group_id;
315 ELSE
316 FND_MESSAGE.Set_Name('AHL','AHL_MC_COMP_IG_INVALID');
317 FND_MESSAGE.Set_Token('ITEM_GRP',l_item_comp_rec.item_group_name);
318 FND_MSG_PUB.ADD;
319 END IF;
320 CLOSE get_itemGroup_csr;
321 END IF;
322
323 IF (l_item_comp_rec.inventory_item_id IS NOT NULL) OR
324 (l_item_comp_rec.inventory_item_name IS NOT NULL) THEN
325
326
327 -- For Inventory Organization Code.
328 IF (l_item_comp_rec.inventory_org_id IS NULL) OR
329 (l_item_comp_rec.inventory_org_id = FND_API.G_MISS_NUM)
330 THEN
331 -- if code is present.
332 IF (l_item_comp_rec.inventory_org_code IS NOT NULL) AND
333 (l_item_comp_rec.inventory_org_code <> FND_API.G_MISS_CHAR) THEN
334 OPEN mtl_parameters_csr (l_item_comp_rec.inventory_org_code);
335 FETCH mtl_parameters_csr INTO l_inventory_org_id;
336 IF (mtl_parameters_csr%FOUND) THEN
337 l_item_comp_rec.inventory_org_id := l_inventory_org_id;
338 ELSE
339 FND_MESSAGE.Set_Name('AHL','AHL_MC_ORG_INVALID');
340 FND_MESSAGE.Set_Token('ORG',l_item_comp_rec.inventory_org_code);
341 FND_MSG_PUB.ADD;
342 END IF;
343 CLOSE mtl_parameters_csr;
344 ELSIF (l_item_comp_rec.operation_flag = 'C') THEN
345 -- Both ID and code are missing.
346 FND_MESSAGE.Set_Name('AHL','AHL_MC_ORG_NULL');
347 FND_MSG_PUB.ADD;
348 END IF;
349
350 END IF;
351
352 -- For Inventory item.
353 IF (l_item_comp_rec.inventory_item_id IS NULL) OR
354 (l_item_comp_rec.inventory_item_id = FND_API.G_MISS_NUM)
355 THEN
356 -- check if name exists.
357 IF (l_item_comp_rec.inventory_item_name IS NOT NULL) AND
358 (l_item_comp_rec.inventory_item_name <> FND_API.G_MISS_CHAR) THEN
359
360 OPEN mtl_system_items_csr(l_item_comp_rec.inventory_item_name,
361 l_item_comp_rec.inventory_org_id);
362 FETCH mtl_system_items_csr INTO l_inventory_id;
363 IF (mtl_system_items_csr%FOUND) THEN
364 l_item_comp_rec.inventory_item_id := l_inventory_id;
365
366 ELSE
367 FND_MESSAGE.Set_Name('AHL','AHL_MC_INV_INVALID');
368 FND_MESSAGE.Set_Token('INV_ITEM',l_item_comp_rec.inventory_item_name);
369 FND_MSG_PUB.ADD;
370 END IF;
371 CLOSE mtl_system_items_csr;
372 ELSIF (l_item_comp_rec.operation_flag = 'C') THEN
373 -- Both ID and name missing.
374 FND_MESSAGE.Set_Name('AHL','AHL_MC_INV_NULL');
375 FND_MSG_PUB.ADD;
376 END IF;
377
378 ELSE
379 OPEN mtl_segment_csr(l_item_comp_rec.inventory_item_id,
380 l_item_comp_rec.inventory_org_id);
381 FETCH mtl_segment_csr INTO l_concatenated_segments;
382 IF (mtl_segment_csr%NOTFOUND) THEN
383 FND_MESSAGE.Set_Name('AHL','AHL_MC_INV_INVALID');
384 FND_MESSAGE.Set_Token('INV_ITEM',l_item_comp_rec.inventory_item_id);
385 FND_MSG_PUB.ADD;
386 ELSE
387 l_item_comp_rec.inventory_item_name := l_concatenated_segments;
388 END IF;
389 CLOSE mtl_segment_csr;
390
391 END IF;
392
393 END IF;
394
395
396 -- return changed record.
397 p_x_item_comp_rec := l_item_comp_rec;
398
399
400 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
401 THEN
402 l_dummy := 'Inventory p_x_item_comp_rec '||to_char(p_x_item_comp_rec.inventory_item_id);
403 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
404 'ahl_mc_item_comp_pvt.Convert_InTo_ID', l_dummy);
405 END IF;
406
407
408 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
409 THEN
410 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
411 'ahl_mc_item_comp_pvt.Convert_InTo_ID', 'End of Convert_InTo_ID');
412 END IF;
413
414 END Convert_code_to_ID;
415
416
417
418 End AHL_MC_ITEM_COMP_PUB;