1 PACKAGE BODY AHL_ENIGMA_IPC_PROCS_PVT AS
2 /* $Header: AHLVEIPB.pls 120.5.12020000.2 2012/12/14 08:46:41 shnatu ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AHL_ENIGMA_IPC_PROCS_PVT';
5
6 ------------------------------------------------------------------------------------
7 -- Common constants and variables
8 ------------------------------------------------------------------------------------
9 l_log_current_level NUMBER := FND_LOG.g_current_runtime_level;
10 l_log_statement NUMBER := FND_LOG.level_statement;
11 l_log_procedure NUMBER := FND_LOG.level_procedure;
12 l_log_error NUMBER := FND_LOG.level_error;
13 l_log_unexpected NUMBER := FND_LOG.level_unexpected;
14
15 l_ig_report_type VARCHAR2(30) := 'IPC_AIG';
16 l_mc_report_type VARCHAR2(30) := 'IPC_MC';
17 l_ic_report_type VARCHAR2(30) := 'IPC_CL';
18
19 l_user_id NUMBER := NULL;
20 l_resp_id NUMBER := NULL;
21 l_app_id NUMBER := 867;
22 ------------------------------------------------------------------------------------
23
24 ------------------------------------------------------------------------------------
25 -- private function to concatenate multiple error messages into one string
26 FUNCTION Get_Msg_Data(p_msg_count IN NUMBER) RETURN VARCHAR2 IS
27 --
28 l_msg_data VARCHAR2(4000);
29 l_temp_msg_data VARCHAR2(2000);
30 l_msg_index_out NUMBER;
31 l_msg_count NUMBER;
32 --
33
34 BEGIN
35 l_msg_count := p_msg_count;
36 IF (l_msg_count IS NULL)THEN
37 RETURN NULL;
38 END IF;
39
40 IF (l_msg_count = 1) then
41 FND_MSG_PUB.count_and_get(p_count => l_msg_count,
42 p_data => l_temp_msg_data,
43 p_encoded => FND_API.G_FALSE);
44 l_msg_data := l_temp_msg_data;
45 ELSE
46 IF (l_msg_count > 0) THEN
47 FOR i IN 1..l_msg_count LOOP
48 FND_MSG_PUB.get(
49 p_encoded => FND_API.G_FALSE,
50 p_data => l_temp_msg_data,
51 p_msg_index_out => l_msg_index_out);
52
53 IF (i = 1) THEN
54 l_msg_data := l_temp_msg_data;
55 ELSE
56 l_msg_data := l_msg_data || ' | ' || l_temp_msg_data;
57 END IF;
58 END LOOP;
59 END IF;
60 END IF;
61
62 RETURN l_msg_data;
63 END GET_MSG_DATA;
64
65 -- private procedure to log process details in the new table AHL_ENIGMA_PROCESS_LOG
66 PROCEDURE Enter_Process_Log (
67 p_report_type IN VARCHAR2,
68 p_report_file_name IN VARCHAR2,
69 p_object_name IN VARCHAR2,
70 p_operation_flag IN VARCHAR2,
71 p_process_status IN VARCHAR2,
72 p_msg_count IN NUMBER
73 ) IS
74 --
75 l_log_id NUMBER;
76 l_msg_data VARCHAR2(2000) := NULL;
77 --
78
79 BEGIN
80 -- get the next value of the sequence AHL_ENIGMA_PROCESS_LOG_S
81 SELECT AHL_ENIGMA_PROCESS_LOG_S.NEXTVAL INTO l_log_id FROM DUAL;
82
83 IF (p_process_status <> 'S') THEN
84 -- call the private function Get_Msg_Data
85 l_msg_data := SUBSTR(Get_Msg_Data(p_msg_count), 1, 2000);
86 END IF;
87
88 -- insert the log in the log table
89 INSERT INTO AHL_ENIGMA_PROCESS_LOG (
90 log_id,
91 last_update_date,
92 last_updated_by,
93 creation_date,
94 created_by,
95 last_update_login,
96 process_date_time,
97 report_type,
98 report_file_name,
99 object_name,
100 operation_flag,
101 process_status,
102 process_error
103 ) VALUES (
104 l_log_id,
105 SYSDATE,
106 TO_NUMBER(FND_GLOBAL.USER_ID),
107 SYSDATE,
108 TO_NUMBER(FND_GLOBAL.USER_ID),
109 TO_NUMBER(FND_GLOBAL.LOGIN_ID),
110 SYSDATE,
111 p_report_type,
112 p_report_file_name,
113 p_object_name,
114 p_operation_flag,
115 p_process_status,
116 l_msg_data
117 );
118 END Enter_Process_Log;
119 ------------------------------------------------------------------------------------
120
121 ------------------------------------------------------------------------------------
122 -- Start of Comments
123 -- Procedure name : Process_Item_Group
124 -- Type : Public
125 -- Function : Procedure to create, modify and delete item groups. Will be called by the item group web service.
126 -- Pre-reqs :
127 -- Parameters :
128 --
129 -- Process_Item_Group Parameters:
130 -- p_report_file_name IN VARCHAR2 Required
131 -- p_item_group_rec IN AHL_MC_ITEMGROUP_PVT.Item_Group_Rec_Type Required
132 -- p_items_tbl IN AHL_MC_ITEMGROUP_PVT.Item_Association_Tbl_Type Required
133 --
134 -- End of Comments
135
136 PROCEDURE Process_Item_Group (
137 p_api_version IN NUMBER,
138 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
139 p_commit IN VARCHAR2 := FND_API.G_FALSE,
140 x_return_status OUT NOCOPY VARCHAR2,
141 x_msg_count OUT NOCOPY NUMBER,
142 x_msg_data OUT NOCOPY VARCHAR2,
143 p_report_file_name IN VARCHAR2 := NULL,
144 p_item_group_rec IN AHL_MC_ITEMGROUP_PVT.Item_Group_Rec_Type,
145 p_items_tbl IN AHL_MC_ITEMGROUP_PVT.Item_Association_Tbl_Type
146 ) IS
147
148 CURSOR get_ig_id_csr (p_ig_name VARCHAR2) IS
149 SELECT item_group_id
150 FROM ahl_item_groups_b
151 WHERE name = NVL(p_ig_name, '-')
152 AND status_code <> 'REMOVED'
153 AND NVL(sourced_from_enigma_flag, 'N') = 'Y';
154
155 CURSOR chk_ig_type_code_csr (p_ig_type_code VARCHAR2) IS
156 SELECT 'X'
157 FROM fnd_lookups
158 WHERE lookup_code = NVL(p_ig_type_code, 'X')
159 AND lookup_type = 'AHL_ITEMGROUP_TYPE'
160 AND TRUNC(NVL(end_date_active, SYSDATE - 1)) < TRUNC(SYSDATE);
161
162 CURSOR get_item_id_csr (p_item_number VARCHAR2, p_org_id NUMBER) IS
163 SELECT inventory_item_id
164 FROM mtl_system_items_kfv
165 WHERE concatenated_segments = NVL(p_item_number, '-')
166 AND organization_id = p_org_id;
167
168 CURSOR chk_interchg_type_code_csr (p_interchg_type_code VARCHAR2) IS
169 SELECT 'X'
170 FROM fnd_lookups
171 WHERE lookup_code = NVL(p_interchg_type_code, 'X')
172 AND lookup_type = 'AHL_INTERCHANGE_ITEM_TYPE'
173 AND TRUNC(NVL(end_date_active, SYSDATE - 1)) < TRUNC(SYSDATE);
174
175 CURSOR get_ia_id_csr (p_ig_id NUMBER, p_item_id NUMBER, p_item_revision VARCHAR2, p_org_id NUMBER) IS
176 SELECT item_association_id
177 FROM ahl_item_associations_b
178 WHERE item_group_id = p_ig_id
179 AND inventory_item_id = p_item_id
180 AND NVL(revision, '-') = NVL(p_item_revision, '-')
181 AND inventory_org_id = p_org_id;
182
183 --
184 l_api_version CONSTANT NUMBER := 1.0;
185 l_api_name CONSTANT VARCHAR2(30) := 'Process_Item_Group';
186 l_full_name CONSTANT VARCHAR2(60) := 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name;
187
188 l_item_group_rec AHL_MC_ITEMGROUP_PVT.Item_Group_Rec_Type DEFAULT p_item_group_rec;
189 l_items_tbl AHL_MC_ITEMGROUP_PVT.Item_Association_Tbl_Type DEFAULT p_items_tbl;
190 l_org_id NUMBER := NULL;
191 l_dummy VARCHAR2(1);
192 l_msg_count NUMBER;
193 --
194
195 BEGIN
196 IF (l_log_procedure >= l_log_current_level) THEN
197 FND_LOG.string(l_log_procedure, l_full_name || '.begin', 'At the start of the API');
198 END IF;
199
200 -- Standard start of API savepoint
201 SAVEPOINT Process_Item_Group_Pub;
202
203 -- Initialize Procedure return status to success
204 x_return_status := FND_API.G_RET_STS_SUCCESS;
205
206 -- Standard call to check for call compatibility
207 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version,
208 l_api_name, G_PKG_NAME) THEN
209 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
210 END IF;
211
212 -- Initialize message list if p_init_msg_list is set to TRUE
213 IF FND_API.To_Boolean(p_init_msg_list) THEN
214 FND_MSG_PUB.Initialize;
215 END IF;
216
217 -- get the Enigma user and responsibility, and call apps_initialize
218 l_user_id := FND_PROFILE.VALUE('AHL_ENIGMA_USER');
219 l_resp_id := FND_PROFILE.VALUE('AHL_ENIGMA_RESPONSIBILITY');
220 IF (l_log_statement >= l_log_current_level) THEN
221 FND_LOG.string(l_log_statement, l_full_name, 'l_user_id = '||l_user_id||', l_resp_id = '||l_resp_id);
222 END IF;
223 BEGIN
224 FND_GLOBAL.APPS_INITIALIZE(l_user_id, l_resp_id, l_app_id);
225 EXCEPTION
226 WHEN OTHERS THEN
227 IF (l_log_statement >= l_log_current_level) THEN
228 FND_LOG.string(l_log_statement, l_full_name, 'FND_GLOBAL.APPS_INITIALIZE call failed - '||SQLERRM);
229 END IF;
230 END;
231
232 -- get the org id from the new profile
233 l_org_id := FND_PROFILE.VALUE('AHL_ENIGMA_ITEM_ORG');
234
235 IF (l_log_statement >= l_log_current_level) THEN
236 FND_LOG.string(l_log_statement, l_full_name, 'l_org_id = '||l_org_id);
237 END IF;
238
239 -- check for the profile value
240 IF (l_org_id IS NULL) THEN
241 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_ENIGMA_ORG_PRFL_NULL'); -- Profile 'AHL: Enigma Sourced Item Org' can't be NULL. (new message)
242 FND_MSG_PUB.ADD;
243 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
244 END IF;
245
246 IF (l_log_statement >= l_log_current_level) THEN
247 FND_LOG.string(l_log_statement, l_full_name,
248 'operation flag for the header = '||l_item_group_rec.operation_flag);
249 END IF;
250
251 -- convert the operation flag: 'N' to 'C'
252 IF (l_item_group_rec.operation_flag = 'N') THEN
253 l_item_group_rec.operation_flag := 'C';
254 ELSIF ((l_item_group_rec.operation_flag <> 'M' AND l_item_group_rec.operation_flag <> 'D') OR
255 l_item_group_rec.operation_flag IS NULL) THEN
256 -- invalid operation flag
257 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_ENIGMA_OPR_FLAG_INVALID'); -- Operation flag OPR_FLAG is invalid. (new message)
258 FND_MESSAGE.Set_Token('OPR_FLAG', l_item_group_rec.operation_flag);
259 FND_MSG_PUB.ADD;
260 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
261 END IF;
262
263 IF (l_log_statement >= l_log_current_level) THEN
264 FND_LOG.string(l_log_statement, l_full_name, 'l_items_tbl.COUNT = '|| l_items_tbl.COUNT);
265 END IF;
266
267 -- check for item associations table
268 IF (l_items_tbl.COUNT < 1) THEN
269 IF (l_log_statement >= l_log_current_level) THEN
270 FND_LOG.string(l_log_statement, l_full_name, 'no items for association');
271 END IF;
272 -- items table can't be empty for creation - it needs at least one item
273 IF (l_item_group_rec.operation_flag = 'C') THEN
274 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_ENIGMA_AIG_NO_ITEMS'); -- At least one association item is required to create the Item Group. (new message)
275 FND_MSG_PUB.ADD;
276 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
277 END IF;
278 END IF;
279
280 -- check for the header's operation flag
281 IF (l_item_group_rec.operation_flag = 'D') THEN
282 -------------------------- Remove Item Group --------------------------
283
284 -- convert the item group name to id
285 OPEN get_ig_id_csr (l_item_group_rec.name);
286 FETCH get_ig_id_csr INTO l_item_group_rec.item_group_id;
287 IF (get_ig_id_csr%NOTFOUND) THEN
288 CLOSE get_ig_id_csr;
289 -- item group name is invalid
290 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_ITEMGRP_INVALID'); -- Item Group ITEM_GRP is invalid.
291 FND_MESSAGE.Set_Token('ITEM_GRP', l_item_group_rec.name);
292 FND_MSG_PUB.ADD;
293 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
294 END IF;
295 CLOSE get_ig_id_csr;
296
297 IF (l_log_statement >= l_log_current_level) THEN
298 FND_LOG.string(l_log_statement, l_full_name,
299 'Calling private API AHL_MC_ITEMGROUP_PVT.Remove_Item_group.');
300 END IF;
301
302 -- call the private API
303 AHL_MC_ITEMGROUP_PVT.Remove_Item_group(
304 p_api_version => p_api_version,
305 p_init_msg_list => p_init_msg_list,
306 x_return_status => x_return_status,
307 x_msg_count => x_msg_count,
308 x_msg_data => x_msg_data,
309 p_item_group_rec => l_item_group_rec
310 );
311
312 IF (l_log_statement >= l_log_current_level) THEN
313 FND_LOG.string(l_log_statement, l_full_name,
314 'AHL_MC_ITEMGROUP_PVT.Remove_Item_group returned x_return_status as ' || x_return_status);
315 END IF;
316
317 -- check for the return status
318 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
319 IF (l_log_statement >= l_log_current_level) THEN
320 FND_LOG.string(l_log_statement, l_full_name,
321 'Raising exception with x_return_status = ' || x_return_status);
322 END IF;
323 RAISE FND_API.G_EXC_ERROR;
324 END IF;
325
326 ELSIF (l_item_group_rec.operation_flag = 'C') THEN
327 -------------------------- Create Item Group --------------------------
328
329 -- validate the header type code
330 OPEN chk_ig_type_code_csr (l_item_group_rec.type_code);
331 FETCH chk_ig_type_code_csr INTO l_dummy;
332 IF (chk_ig_type_code_csr%NOTFOUND) THEN
333 CLOSE chk_ig_type_code_csr;
334 -- item group type is invalid
335 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_IG_TYPE_INVALID'); -- Item Group Type is invalid.
336 FND_MSG_PUB.ADD;
337 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
338 END IF;
339 CLOSE chk_ig_type_code_csr;
340
341 IF (l_items_tbl.COUNT >= 1) THEN
342 l_msg_count := FND_MSG_PUB.count_msg;
343 -- perform validations and conversions for the item associations
344 FOR i IN l_items_tbl.FIRST..l_items_tbl.LAST LOOP
345 -- convert the operation flag: 'N' to 'C'
346 IF (l_items_tbl(i).operation_flag = 'N') THEN
347 l_items_tbl(i).operation_flag := 'C';
348 ELSIF ((l_items_tbl(i).operation_flag <> 'M' AND l_items_tbl(i).operation_flag <> 'D') OR
349 l_items_tbl(i).operation_flag IS NULL) THEN
350 -- invalid operation flag
351 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_ENIGMA_OPR_FLAG_INVALID'); -- Operation flag OPR_FLAG is invalid. (new message)
352 FND_MESSAGE.Set_Token('OPR_FLAG', l_items_tbl(i).operation_flag);
353 FND_MSG_PUB.ADD;
354 END IF;
355
356 -- set the inventory org id
357 l_items_tbl(i).inventory_org_id := l_org_id;
358
359 -- fetch the item id from the item number
360 OPEN get_item_id_csr (l_items_tbl(i).inventory_item_name, l_items_tbl(i).inventory_org_id);
361 FETCH get_item_id_csr INTO l_items_tbl(i).inventory_item_id;
362 IF (get_item_id_csr%NOTFOUND) THEN
363 -- item is invalid
364 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_INV_INVALID'); -- Inventory Item (INV_ITEM) is invalid.
365 FND_MESSAGE.Set_Token('INV_ITEM', l_items_tbl(i).inventory_item_name);
366 FND_MSG_PUB.ADD;
367 END IF;
368 CLOSE get_item_id_csr;
369
370 -- validate interchange type code
371 OPEN chk_interchg_type_code_csr (l_items_tbl(i).interchange_type_code);
372 FETCH chk_interchg_type_code_csr INTO l_dummy;
373 IF (chk_interchg_type_code_csr%NOTFOUND) THEN
374 -- item group type is invalid
375 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_INTER_INVALID'); -- Interchangeability Type (INTER_CODE) for Item (INV_ITEM) is invalid.
376 FND_MESSAGE.Set_Token('INTER_CODE', l_items_tbl(i).interchange_type_code);
377 FND_MESSAGE.Set_Token('INV_ITEM', l_items_tbl(i).inventory_item_name);
378 FND_MSG_PUB.ADD;
379 END IF;
380 CLOSE chk_interchg_type_code_csr;
381 END LOOP;
382 IF (l_msg_count < FND_MSG_PUB.count_msg) THEN
383 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
384 END IF;
385 END IF;
386
387 IF (l_log_statement >= l_log_current_level) THEN
388 FND_LOG.string(l_log_statement, l_full_name,
389 'Calling private API AHL_MC_ITEMGROUP_PVT.Create_Item_group.');
390 END IF;
391
392 -- call the private API
393 AHL_MC_ITEMGROUP_PVT.Create_Item_group(
394 p_api_version => p_api_version,
395 p_init_msg_list => p_init_msg_list,
396 x_return_status => x_return_status,
397 x_msg_count => x_msg_count,
398 x_msg_data => x_msg_data,
399 p_x_item_group_rec => l_item_group_rec,
400 p_x_items_tbl => l_items_tbl
401 );
402
403 IF (l_log_statement >= l_log_current_level) THEN
404 FND_LOG.string(l_log_statement, l_full_name,
405 'AHL_MC_ITEMGROUP_PVT.Create_Item_group returned x_return_status as ' || x_return_status);
406 END IF;
407
408 -- check for the return status
409 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
410 IF (l_log_statement >= l_log_current_level) THEN
411 FND_LOG.string(l_log_statement, l_full_name,
412 'Raising exception with x_return_status = ' || x_return_status);
413 END IF;
414 RAISE FND_API.G_EXC_ERROR;
415 END IF;
416
417 -- move the item group to Complete status
418 IF (l_log_statement >= l_log_current_level) THEN
419 FND_LOG.string(l_log_statement, l_full_name,
420 'Calling private API AHL_MC_ITEMGROUP_PVT.Approve_ItemGroups.');
421 END IF;
422
423 -- call the private API
424 AHL_MC_ITEMGROUP_PVT.Approve_ItemGroups(
425 p_api_version => p_api_version,
426 p_init_msg_list => p_init_msg_list,
427 p_module_type => NULL, -- not used in the API
428 x_return_status => x_return_status,
429 x_msg_count => x_msg_count,
430 x_msg_data => x_msg_data,
431 p_appr_status => 'APPROVED',
432 p_ItemGroups_id => l_item_group_rec.item_group_id,
433 p_object_version_number => 1
434 );
435
436 IF (l_log_statement >= l_log_current_level) THEN
437 FND_LOG.string(l_log_statement, l_full_name,
438 'AHL_MC_ITEMGROUP_PVT.Approve_ItemGroups returned x_return_status as ' || x_return_status);
439 END IF;
440
441 -- check for the return status
442 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
443 IF (l_log_statement >= l_log_current_level) THEN
444 FND_LOG.string(l_log_statement, l_full_name,
445 'Raising exception with x_return_status = ' || x_return_status);
446 END IF;
447 RAISE FND_API.G_EXC_ERROR;
448 END IF;
449
450 -- update the new field SOURCED_FROM_ENIGMA_FLAG in the table AHL_ITEM_GROUPS_B
451 UPDATE ahl_item_groups_b
452 SET sourced_from_enigma_flag = 'Y'
453 WHERE item_group_id = l_item_group_rec.item_group_id;
454
455 ELSIF (l_item_group_rec.operation_flag = 'M') THEN
456 -------------------------- Update Item Group --------------------------
457
458 -- convert the item group name to id
459 OPEN get_ig_id_csr (l_item_group_rec.name);
460 FETCH get_ig_id_csr INTO l_item_group_rec.item_group_id;
461 IF (get_ig_id_csr%NOTFOUND) THEN
462 CLOSE get_ig_id_csr;
463 -- item group name is invalid
464 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_ITEMGRP_INVALID'); -- Item Group ITEM_GRP is invalid.
465 FND_MESSAGE.Set_Token('ITEM_GRP', l_item_group_rec.name);
466 FND_MSG_PUB.ADD;
467 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
468 END IF;
469 CLOSE get_ig_id_csr;
470
471 -- validate the header type code
472 OPEN chk_ig_type_code_csr (l_item_group_rec.type_code);
473 FETCH chk_ig_type_code_csr INTO l_dummy;
474 IF (chk_ig_type_code_csr%NOTFOUND) THEN
475 CLOSE chk_ig_type_code_csr;
476 -- item group type is invalid
477 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_IG_TYPE_INVALID'); -- Item Group Type is invalid.
478 FND_MSG_PUB.ADD;
479 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
480 END IF;
481 CLOSE chk_ig_type_code_csr;
482
483 IF (l_items_tbl.COUNT >= 1) THEN
484 l_msg_count := FND_MSG_PUB.count_msg;
485 -- perform validations and conversions for the item associations
486 FOR i IN l_items_tbl.FIRST..l_items_tbl.LAST LOOP
487 -- convert the operation flag: 'N' to 'C'
488 IF (l_items_tbl(i).operation_flag = 'N') THEN
489 l_items_tbl(i).operation_flag := 'C';
490 ELSIF ((l_items_tbl(i).operation_flag <> 'M' AND l_items_tbl(i).operation_flag <> 'D') OR
491 l_items_tbl(i).operation_flag IS NULL) THEN
492 -- invalid operation flag
493 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_ENIGMA_OPR_FLAG_INVALID'); -- Operation flag OPR_FLAG is invalid. (new message)
494 FND_MESSAGE.Set_Token('OPR_FLAG', l_items_tbl(i).operation_flag);
495 FND_MSG_PUB.ADD;
496 END IF;
497
498 -- set the inventory org id
499 l_items_tbl(i).inventory_org_id := l_org_id;
500
501 -- fetch the item id from the item number
502 OPEN get_item_id_csr (l_items_tbl(i).inventory_item_name, l_items_tbl(i).inventory_org_id);
503 FETCH get_item_id_csr INTO l_items_tbl(i).inventory_item_id;
504 IF (get_item_id_csr%NOTFOUND) THEN
505 -- item is invalid
506 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_INV_INVALID'); -- Inventory Item (INV_ITEM) is invalid.
507 FND_MESSAGE.Set_Token('INV_ITEM', l_items_tbl(i).inventory_item_name);
508 FND_MSG_PUB.ADD;
509 END IF;
510 CLOSE get_item_id_csr;
511
512 -- for update and delete operations, fetch the association id
513 IF (l_items_tbl(i).operation_flag <> 'C') THEN
514 OPEN get_ia_id_csr (l_item_group_rec.item_group_id, l_items_tbl(i).inventory_item_id,
515 l_items_tbl(i).revision, l_items_tbl(i).inventory_org_id);
516 FETCH get_ia_id_csr INTO l_items_tbl(i).item_association_id;
517 IF (get_ia_id_csr%NOTFOUND) THEN
518 -- item association is invalid
519 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_IA_INVALID'); -- Invalid association of item ITEM. (new message)
520 FND_MESSAGE.Set_Token('ITEM', l_items_tbl(i).inventory_item_name);
521 FND_MSG_PUB.ADD;
522 END IF;
523 CLOSE get_ia_id_csr;
524 END IF;
525
526 -- set the item group id as well, for all the operations
527 l_items_tbl(i).item_group_id := l_item_group_rec.item_group_id;
528
529 -- validate interchange type code
530 OPEN chk_interchg_type_code_csr (l_items_tbl(i).interchange_type_code);
531 FETCH chk_interchg_type_code_csr INTO l_dummy;
532 IF (chk_interchg_type_code_csr%NOTFOUND) THEN
533 -- item group type is invalid
534 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_INTER_INVALID'); -- Interchangeability Type (INTER_CODE) for Item (INV_ITEM) is invalid.
535 FND_MESSAGE.Set_Token('INTER_CODE', l_items_tbl(i).interchange_type_code);
536 FND_MESSAGE.Set_Token('INV_ITEM', l_items_tbl(i).inventory_item_name);
537 FND_MSG_PUB.ADD;
538 END IF;
539 CLOSE chk_interchg_type_code_csr;
540 END LOOP;
541 IF (l_msg_count < FND_MSG_PUB.count_msg) THEN
542 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
543 END IF;
544 END IF;
545
546 IF (l_log_statement >= l_log_current_level) THEN
547 FND_LOG.string(l_log_statement, l_full_name,
548 'Calling private API AHL_MC_ITEMGROUP_PVT.Modify_Item_group.');
549 END IF;
550
551 -- call the private API
552 AHL_MC_ITEMGROUP_PVT.Modify_Item_group(
553 p_api_version => p_api_version,
554 p_init_msg_list => p_init_msg_list,
555 x_return_status => x_return_status,
556 x_msg_count => x_msg_count,
557 x_msg_data => x_msg_data,
558 p_item_group_rec => l_item_group_rec,
559 p_x_items_tbl => l_items_tbl
560 );
561
562 IF (l_log_statement >= l_log_current_level) THEN
563 FND_LOG.string(l_log_statement, l_full_name,
564 'AHL_MC_ITEMGROUP_PVT.Modify_Item_group returned x_return_status as ' || x_return_status);
565 END IF;
566
567 -- check for the return status
568 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
569 IF (l_log_statement >= l_log_current_level) THEN
570 FND_LOG.string(l_log_statement, l_full_name,
571 'Raising exception with x_return_status = ' || x_return_status);
572 END IF;
573 RAISE FND_API.G_EXC_ERROR;
574 END IF;
575
576 END IF; -- header's operation flag check
577
578 -- Standard check of p_commit
579 IF FND_API.TO_BOOLEAN(p_commit) THEN
580 COMMIT WORK;
581 END IF;
582
583 -- enter the success log
584 Enter_Process_Log (
585 p_report_type => l_ig_report_type,
586 p_report_file_name => p_report_file_name,
587 p_object_name => l_item_group_rec.name,
588 p_operation_flag => l_item_group_rec.operation_flag,
589 p_process_status => FND_API.G_RET_STS_SUCCESS,
590 p_msg_count => 0
591 );
592
593 IF (l_log_procedure >= l_log_current_level) THEN
594 FND_LOG.string(l_log_procedure, l_full_name || '.end', 'End of the API');
595 END IF;
596
597 EXCEPTION
598 WHEN FND_API.G_EXC_ERROR THEN
599 Rollback to Process_Item_Group_Pub;
600 x_return_status := FND_API.G_RET_STS_ERROR;
601 x_msg_count := FND_MSG_PUB.count_msg;
602 -- enter the error log
603 Enter_Process_Log (
604 p_report_type => l_ig_report_type,
605 p_report_file_name => p_report_file_name,
606 p_object_name => l_item_group_rec.name,
607 p_operation_flag => l_item_group_rec.operation_flag,
608 p_process_status => x_return_status,
609 p_msg_count => x_msg_count
610 );
611
612 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
613 Rollback to Process_Item_Group_Pub;
614 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
615 x_msg_count := FND_MSG_PUB.count_msg;
616 -- enter the error log
617 Enter_Process_Log (
618 p_report_type => l_ig_report_type,
619 p_report_file_name => p_report_file_name,
620 p_object_name => l_item_group_rec.name,
621 p_operation_flag => l_item_group_rec.operation_flag,
622 p_process_status => x_return_status,
623 p_msg_count => x_msg_count
624 );
625
626 WHEN OTHERS THEN
627 Rollback to Process_Item_Group_Pub;
628 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
629 FND_MSG_PUB.Add_Exc_Msg( p_pkg_name => G_PKG_NAME,
630 p_procedure_name => l_api_name,
631 p_error_text => SQLERRM);
632 x_msg_count := FND_MSG_PUB.count_msg;
633 -- enter the error log
634 Enter_Process_Log (
635 p_report_type => l_ig_report_type,
636 p_report_file_name => p_report_file_name,
637 p_object_name => l_item_group_rec.name,
638 p_operation_flag => l_item_group_rec.operation_flag,
639 p_process_status => x_return_status,
640 p_msg_count => x_msg_count
641 );
642
643 END Process_Item_Group;
644
645 ------------------------------------------------------------------------------------
646 -- Start of Comments
647 -- Procedure name : Process_Master_Config
648 -- Type : Public
649 -- Function : Procedure to create master configuration. Will be called by the MC web service.
650 -- Pre-reqs :
651 -- Parameters :
652 --
653 -- Process_Item_Group Parameters:
654 -- p_report_file_name IN VARCHAR2 Required
655 -- p_mc_header_rec IN AHL_MC_MasterConfig_PVT.Header_Rec_Type Required
656 -- p_nodes_tbl IN AHL_MC_Node_PVT.Node_Tbl_Type Required
657 --
658 -- End of Comments
659
660 PROCEDURE Process_Master_Config (
661 p_api_version IN NUMBER,
662 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
663 p_commit IN VARCHAR2 := FND_API.G_FALSE,
664 x_return_status OUT NOCOPY VARCHAR2,
665 x_msg_count OUT NOCOPY NUMBER,
666 x_msg_data OUT NOCOPY VARCHAR2,
667 p_report_file_name IN VARCHAR2 := NULL,
668 p_mc_header_rec IN AHL_MC_MasterConfig_PVT.Header_Rec_Type,
669 p_nodes_tbl IN AHL_MC_Node_PVT.Node_Tbl_Type
670 ) IS
671
672 CURSOR chk_model_code_csr (p_model_code VARCHAR2) IS
673 SELECT 'X'
674 FROM fnd_lookups
675 WHERE lookup_code = p_model_code
676 AND lookup_type = 'AHL_ENIGMA_MODEL_CODE'
677 AND TRUNC(NVL(end_date_active, SYSDATE - 1)) < TRUNC(SYSDATE);
678
679 CURSOR get_pos_ref_csr (p_pos_ref_code VARCHAR2) IS
680 SELECT meaning
681 FROM fnd_lookups
682 WHERE lookup_code = NVL(p_pos_ref_code, '-')
683 AND lookup_type = 'AHL_POSITION_REFERENCE'
684 AND TRUNC(NVL(end_date_active, SYSDATE - 1)) < TRUNC(SYSDATE);
685
686 CURSOR get_ig_id_csr (p_ig_name VARCHAR2) IS
687 SELECT item_group_id
688 FROM ahl_item_groups_b
689 WHERE name = NVL(p_ig_name, '-')
690 AND status_code <> 'REMOVED'
691 AND NVL(sourced_from_enigma_flag, 'N') = 'Y';
692
693 CURSOR chk_ata_code_csr (p_ata_code_code VARCHAR2) IS
694 SELECT 'X'
695 FROM fnd_lookups
696 WHERE lookup_code = p_ata_code_code
697 AND lookup_type = 'AHL_ATA_CODE'
698 AND TRUNC(NVL(end_date_active, SYSDATE - 1)) < TRUNC(SYSDATE);
699
700 --
701 l_api_version CONSTANT NUMBER := 1.0;
702 l_api_name CONSTANT VARCHAR2(30) := 'Process_Master_Config';
703 l_full_name CONSTANT VARCHAR2(60) := 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name;
704
705 -- Following three object types have been defined in the DB. For details, please refer their XDFs.
706 -- 1) AHL_MC_POS_REF
707 -- 2) AHL_MC_POS_DETAIL
708 -- 3) AHL_MC_POS_STACK
709
710 l_mc_header_rec AHL_MC_MasterConfig_PVT.Header_Rec_Type DEFAULT p_mc_header_rec;
711 l_nodes_tbl AHL_MC_Node_PVT.Node_Tbl_Type DEFAULT p_nodes_tbl;
712
713 l_mc_pos_stack AHL_MC_POS_STACK := AHL_MC_POS_STACK(NULL, NULL, NULL);
714 l_mc_pos_ref AHL_MC_POS_REF := AHL_MC_POS_REF(NULL, NULL);
715 l_stack_data AHL_MC_POS_REF := NULL;
716
717 l_dummy VARCHAR2(1);
718 l_msg_count NUMBER;
719
720 l_Counter_Rules_Tbl_Type AHL_MC_Node_PVT.Counter_Rules_Tbl_Type; -- used as dummy, for API call
721 l_SubConfig_Tbl_Type AHL_MC_Node_PVT.SubConfig_Tbl_Type; -- used as dummy, for API call
722 --
723
724 BEGIN
725 IF (l_log_procedure >= l_log_current_level) THEN
726 FND_LOG.string(l_log_procedure, l_full_name || '.begin', 'At the start of the API');
727 END IF;
728
729 -- Standard start of API savepoint
730 SAVEPOINT Process_Master_Config_Pub;
731
732 -- Initialize Procedure return status to success
733 x_return_status := FND_API.G_RET_STS_SUCCESS;
734
735 -- Standard call to check for call compatibility
736 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version,
737 l_api_name, G_PKG_NAME) THEN
738 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
739 END IF;
740
741 -- Initialize message list if p_init_msg_list is set to TRUE
742 IF FND_API.To_Boolean(p_init_msg_list) THEN
743 FND_MSG_PUB.Initialize;
744 END IF;
745
746 -- get the Enigma user and responsibility, and call apps_initialize
747 l_user_id := FND_PROFILE.VALUE('AHL_ENIGMA_USER');
748 l_resp_id := FND_PROFILE.VALUE('AHL_ENIGMA_RESPONSIBILITY');
749 IF (l_log_statement >= l_log_current_level) THEN
750 FND_LOG.string(l_log_statement, l_full_name, 'l_user_id = '||l_user_id||', l_resp_id = '||l_resp_id);
751 END IF;
752 BEGIN
753 FND_GLOBAL.APPS_INITIALIZE(l_user_id, l_resp_id, l_app_id);
754 EXCEPTION
755 WHEN OTHERS THEN
756 IF (l_log_statement >= l_log_current_level) THEN
757 FND_LOG.string(l_log_statement, l_full_name, 'FND_GLOBAL.APPS_INITIALIZE call failed - '||SQLERRM);
758 END IF;
759 END;
760
761 IF (l_log_statement >= l_log_current_level) THEN
762 FND_LOG.string(l_log_statement, l_full_name,
763 'l_nodes_tbl.COUNT = '|| l_nodes_tbl.COUNT);
764 END IF;
765
766 -- check for nodes table
767 IF (l_nodes_tbl.COUNT < 1) THEN
768 IF (l_log_statement >= l_log_current_level) THEN
769 FND_LOG.string(l_log_statement, l_full_name, 'no nodes for the MC');
770 END IF;
771 -- nodes table can't be empty - it needs at least the root node
772 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_ENIGMA_NO_NODES'); -- At least one position is required to create the Master Configuration. (new message)
773 FND_MSG_PUB.ADD;
774 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
775 END IF;
776
777 -- validate the header's model code
778 IF (l_mc_header_rec.model_code IS NOT NULL) THEN
779 OPEN chk_model_code_csr (l_mc_header_rec.model_code);
780 FETCH chk_model_code_csr INTO l_dummy;
781 IF (chk_model_code_csr%NOTFOUND) THEN
782 CLOSE chk_model_code_csr;
783 -- model code is invalid
784 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_MODEL_INVALID'); -- Model "MODEL_MEANING" is invalid.
785 FND_MESSAGE.Set_Token('MODEL_MEANING', l_mc_header_rec.model_code);
786 FND_MSG_PUB.ADD;
787 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
788 END IF;
789 CLOSE chk_model_code_csr;
790 END IF;
791
792 l_msg_count := FND_MSG_PUB.count_msg;
793 -- perform validations and conversions for the nodes
794 FOR i IN l_nodes_tbl.FIRST..l_nodes_tbl.LAST LOOP
795 -- validate position reference code
796 OPEN get_pos_ref_csr (l_nodes_tbl(i).position_ref_code);
797 FETCH get_pos_ref_csr INTO l_nodes_tbl(i).position_ref_meaning;
798 IF (get_pos_ref_csr%NOTFOUND) THEN
799 -- position reference code is invalid
800 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_POSREF_INVALID'); -- Position reference (POSREF) is invalid.
801 FND_MESSAGE.Set_Token('POSREF', l_nodes_tbl(i).position_ref_code);
802 FND_MSG_PUB.ADD;
803 END IF;
804 CLOSE get_pos_ref_csr;
805
806 -- convert the item group name to id
807 OPEN get_ig_id_csr (l_nodes_tbl(i).item_group_name);
808 FETCH get_ig_id_csr INTO l_nodes_tbl(i).item_group_id;
809 IF (get_ig_id_csr%NOTFOUND) THEN
810 -- item group name is invalid, so set it to NULL
811 l_nodes_tbl(i).item_group_id := NULL;
812 END IF;
813 CLOSE get_ig_id_csr;
814
815 -- validate ATA code
816 IF (l_nodes_tbl(i).ata_code IS NOT NULL) THEN
817 OPEN chk_ata_code_csr (l_nodes_tbl(i).ata_code);
818 FETCH chk_ata_code_csr INTO l_dummy;
819 IF (chk_ata_code_csr%NOTFOUND) THEN
820 -- model code is invalid
821 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_ATASEQ_INVALID'); -- ATA Code "ATAMEANING" is invalid
822 FND_MESSAGE.Set_Token('ATAMEANING', l_nodes_tbl(i).ata_code);
823 FND_MSG_PUB.ADD;
824 END IF;
825 CLOSE chk_ata_code_csr;
826 END IF;
827 END LOOP;
828 IF (l_msg_count < FND_MSG_PUB.count_msg) THEN
829 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
830 END IF;
831
832 -- create the MC header
833 l_mc_header_rec.operation_flag := 'C';
834 l_mc_header_rec.version_number := 1;
835 l_mc_header_rec.config_status_code := 'DRAFT';
836 l_nodes_tbl(1).operation_flag := 'C';
837 l_nodes_tbl(1).position_necessity_code := 'MANDATORY';
838 l_nodes_tbl(1).display_order := 1;
839
840 IF (l_log_statement >= l_log_current_level) THEN
841 FND_LOG.string(l_log_statement, l_full_name,
842 'Calling private API AHL_MC_MasterConfig_PVT.Create_Master_Config.');
843 END IF;
844
845 -- call the private API
846 AHL_MC_MasterConfig_PVT.Create_Master_Config(
847 p_api_version => p_api_version,
848 p_init_msg_list => p_init_msg_list,
849 x_return_status => x_return_status,
850 x_msg_count => x_msg_count,
851 x_msg_data => x_msg_data,
852 p_x_mc_header_rec => l_mc_header_rec,
853 p_x_node_rec => l_nodes_tbl(1)
854 );
855
856 IF (l_log_statement >= l_log_current_level) THEN
857 FND_LOG.string(l_log_statement, l_full_name,
858 'AHL_MC_MasterConfig_PVT.Create_Master_Config returned x_return_status as ' || x_return_status||
859 ', MC header id = '||l_mc_header_rec.mc_header_id||
860 ', root node relationship id = '||l_nodes_tbl(1).relationship_id);
861 END IF;
862
863 -- check for the return status
864 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
865 IF (l_log_statement >= l_log_current_level) THEN
866 FND_LOG.string(l_log_statement, l_full_name,
867 'Raising exception with x_return_status = ' || x_return_status);
868 END IF;
869 RAISE FND_API.G_EXC_ERROR;
870 END IF;
871
872 -- push the root node position data in the position stack
873 l_mc_pos_ref.put(l_nodes_tbl(1).position_ref_code, l_nodes_tbl(1).relationship_id);
874 l_mc_pos_stack.initialize(); -- initialize the stack
875 l_mc_pos_stack.push(l_mc_pos_ref);
876
877 -- create the rest of the tree, starting from the second node
878 IF (l_nodes_tbl.COUNT >= 2) THEN
879 FOR i IN l_nodes_tbl.FIRST+1..l_nodes_tbl.LAST LOOP
880 l_nodes_tbl(i).mc_header_id := l_mc_header_rec.mc_header_id;
881 l_nodes_tbl(i).operation_flag := 'C';
882 l_nodes_tbl(i).position_necessity_code := 'MANDATORY';
883 l_nodes_tbl(i).display_order := i;
884
885 -- get the parent relationship id from the stack
886 WHILE (NOT l_mc_pos_stack.under_flow) LOOP
887 -- check with the top of the stack position
888 l_mc_pos_stack.top_pos(l_stack_data);
889 IF (l_stack_data.pos_ref_code_matches(l_nodes_tbl(i).parent_position_ref_code)) THEN
890 -- this is the parent of the current node
891 l_stack_data.get_rel_id(l_nodes_tbl(i).parent_relationship_id);
892 EXIT;
893 ELSE
894 l_mc_pos_stack.pop(l_stack_data); -- pop from the stack
895 END IF;
896 END LOOP;
897
898 -- if no match found, raise exception
899 IF (l_mc_pos_stack.under_flow) THEN
900 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_ENIGMA_PARENT_INVALID'); -- Parent position PAR_POS_REF for the position POS_REF is invalid. (new message)
901 FND_MESSAGE.Set_Token('PAR_POS_REF', l_nodes_tbl(i).parent_position_ref_code);
902 FND_MESSAGE.Set_Token('POS_REF', l_nodes_tbl(i).position_ref_code);
903 FND_MSG_PUB.ADD;
904 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
905 END IF;
906
907 IF (l_log_statement >= l_log_current_level) THEN
908 FND_LOG.string(l_log_statement, l_full_name,
909 'Calling private API AHL_MC_Node_PVT.Create_Node.');
910 END IF;
911
912 -- call the private API
913 AHL_MC_Node_PVT.Create_Node(
914 p_api_version => p_api_version,
915 p_init_msg_list => p_init_msg_list,
916 x_return_status => x_return_status,
917 x_msg_count => x_msg_count,
918 x_msg_data => x_msg_data,
919 p_x_node_rec => l_nodes_tbl(i),
920 p_x_counter_rules_tbl => l_Counter_Rules_Tbl_Type,
921 p_x_subconfig_tbl => l_SubConfig_Tbl_Type
922 );
923
924 IF (l_log_statement >= l_log_current_level) THEN
925 FND_LOG.string(l_log_statement, l_full_name,
926 'AHL_MC_Node_PVT.Create_Node returned x_return_status as ' || x_return_status||
927 ', node relationship id = '||l_nodes_tbl(i).relationship_id);
928 END IF;
929
930 -- check for the return status
931 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
932 IF (l_log_statement >= l_log_current_level) THEN
933 FND_LOG.string(l_log_statement, l_full_name,
934 'Raising exception with x_return_status = ' || x_return_status);
935 END IF;
936 RAISE FND_API.G_EXC_ERROR;
937 END IF;
938
939 -- push the node position data in the position stack
940 l_mc_pos_ref.put(l_nodes_tbl(i).position_ref_code, l_nodes_tbl(i).relationship_id);
941 IF (NOT l_mc_pos_stack.over_flow) THEN
942 l_mc_pos_stack.push(l_mc_pos_ref);
943 ELSE
944 -- Position stack AHL_MC_POS_STACK object has exceeded its max_size. Please increase it to an appropriate value. (new message)
945 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_ENIGMA_STACK_OVRFLW');
946 FND_MSG_PUB.ADD;
947 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
948 END IF;
949 END LOOP;
950 END IF;
951
952 -- update the new field SOURCED_FROM_ENIGMA_FLAG in the table AHL_MC_HEADERS_B
953 UPDATE ahl_mc_headers_b
954 SET sourced_from_enigma_flag = 'Y'
955 WHERE mc_header_id = l_mc_header_rec.mc_header_id;
956
957 -- Standard check of p_commit
958 IF FND_API.TO_BOOLEAN(p_commit) THEN
959 COMMIT WORK;
960 END IF;
961
962 -- enter the success log
963 Enter_Process_Log (
964 p_report_type => l_mc_report_type,
965 p_report_file_name => p_report_file_name,
966 p_object_name => p_mc_header_rec.name,
967 p_operation_flag => 'C',
968 p_process_status => FND_API.G_RET_STS_SUCCESS,
969 p_msg_count => 0
970 );
971
972 IF (l_log_procedure >= l_log_current_level) THEN
973 FND_LOG.string(l_log_procedure, l_full_name || '.end', 'End of the API');
974 END IF;
975
976 EXCEPTION
977 WHEN FND_API.G_EXC_ERROR THEN
978 Rollback to Process_Master_Config_Pub;
979 x_return_status := FND_API.G_RET_STS_ERROR;
980 x_msg_count := FND_MSG_PUB.count_msg;
981 -- enter the error log
982 Enter_Process_Log (
983 p_report_type => l_mc_report_type,
984 p_report_file_name => p_report_file_name,
985 p_object_name => p_mc_header_rec.name,
986 p_operation_flag => 'C',
987 p_process_status => x_return_status,
988 p_msg_count => x_msg_count
989 );
990
991 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
992 Rollback to Process_Master_Config_Pub;
993 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
994 x_msg_count := FND_MSG_PUB.count_msg;
995 -- enter the error log
996 Enter_Process_Log (
997 p_report_type => l_mc_report_type,
998 p_report_file_name => p_report_file_name,
999 p_object_name => p_mc_header_rec.name,
1000 p_operation_flag => 'C',
1001 p_process_status => x_return_status,
1002 p_msg_count => x_msg_count
1003 );
1004
1005 WHEN OTHERS THEN
1006 Rollback to Process_Master_Config_Pub;
1007 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1008 FND_MSG_PUB.Add_Exc_Msg( p_pkg_name => G_PKG_NAME,
1009 p_procedure_name => l_api_name,
1010 p_error_text => SQLERRM);
1011 x_msg_count := FND_MSG_PUB.count_msg;
1012 -- enter the error log
1013 Enter_Process_Log (
1014 p_report_type => l_mc_report_type,
1015 p_report_file_name => p_report_file_name,
1016 p_object_name => p_mc_header_rec.name,
1017 p_operation_flag => 'C',
1018 p_process_status => x_return_status,
1019 p_msg_count => x_msg_count
1020 );
1021
1022 END Process_Master_Config;
1023
1024 ------------------------------------------------------------------------------------
1025 -- Start of Comments
1026 -- Procedure name : Process_Item_Composition
1027 -- Type : Public
1028 -- Function : Procedure to create and modify item compositions. Will be called by the item composition web service.
1029 -- Pre-reqs :
1030 -- Parameters :
1031 --
1032 -- Process_Item_Group Parameters:
1033 -- p_report_file_name IN VARCHAR2 Required
1034 -- p_ic_header_rec IN AHL_MC_ITEM_COMP_PVT.Header_Rec_Type Required
1035 -- p_det_tbl IN AHL_MC_ITEM_COMP_PVT.Header_Rec_Type Required
1036 --
1037 -- End of Comments
1038
1039 PROCEDURE Process_Item_Composition (
1040 p_api_version IN NUMBER,
1041 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1042 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1043 x_return_status OUT NOCOPY VARCHAR2,
1044 x_msg_count OUT NOCOPY NUMBER,
1045 x_msg_data OUT NOCOPY VARCHAR2,
1046 p_report_file_name IN VARCHAR2 := NULL,
1047 p_ic_header_rec IN AHL_MC_ITEM_COMP_PVT.Header_Rec_Type,
1048 p_det_tbl IN AHL_MC_ITEM_COMP_PVT.Det_Tbl_Type
1049 ) IS
1050
1051 CURSOR get_ic_id_csr (p_item_name VARCHAR2, p_org_id NUMBER) IS
1052 SELECT ahl.item_composition_id,
1053 ahl.object_version_number
1054 FROM ahl_item_compositions ahl, mtl_system_items_kfv mtl
1055 WHERE mtl.concatenated_segments = NVL(p_item_name, '-')
1056 AND mtl.organization_id = p_org_id
1057 AND mtl.inventory_item_id = ahl.inventory_item_id
1058 AND TRUNC(NVL(ahl.effective_end_date, SYSDATE + 1)) > TRUNC(SYSDATE)
1059 AND NVL(ahl.sourced_from_enigma_flag, 'N') = 'Y';
1060
1061 CURSOR get_item_id_csr (p_item_number VARCHAR2, p_org_id NUMBER) IS
1062 SELECT inventory_item_id
1063 FROM mtl_system_items_kfv
1064 WHERE concatenated_segments = NVL(p_item_number, '-')
1065 AND organization_id = p_org_id;
1066
1067 CURSOR get_ic_det_id_csr (p_ic_id NUMBER, p_item_id NUMBER, p_org_id NUMBER) IS
1068 SELECT item_comp_detail_id
1069 FROM ahl_item_comp_details
1070 WHERE item_composition_id = p_ic_id
1071 AND inventory_item_id = p_item_id
1072 AND inventory_master_org_id = p_org_id;
1073
1074 --
1075 l_api_version CONSTANT NUMBER := 1.0;
1076 l_api_name CONSTANT VARCHAR2(30) := 'Process_Item_Composition';
1077 l_full_name CONSTANT VARCHAR2(60) := 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name;
1078
1079 l_ic_header_rec AHL_MC_ITEM_COMP_PVT.Header_Rec_Type DEFAULT p_ic_header_rec;
1080 l_det_tbl AHL_MC_ITEM_COMP_PVT.Det_Tbl_Type DEFAULT p_det_tbl;
1081 l_org_id NUMBER := NULL;
1082 l_dummy VARCHAR2(1);
1083 l_msg_count NUMBER;
1084 --
1085
1086 BEGIN
1087 IF (l_log_procedure >= l_log_current_level) THEN
1088 FND_LOG.string(l_log_procedure, l_full_name || '.begin', 'At the start of the API');
1089 END IF;
1090
1091 -- Standard start of API savepoint
1092 SAVEPOINT Process_Item_Composition_Pub;
1093
1094 -- Initialize Procedure return status to success
1095 x_return_status := FND_API.G_RET_STS_SUCCESS;
1096
1097 -- Standard call to check for call compatibility
1098 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version,
1099 l_api_name, G_PKG_NAME) THEN
1100 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1101 END IF;
1102
1103 -- Initialize message list if p_init_msg_list is set to TRUE
1104 IF FND_API.To_Boolean(p_init_msg_list) THEN
1105 FND_MSG_PUB.Initialize;
1106 END IF;
1107
1108 -- get the Enigma user and responsibility, and call apps_initialize
1109 l_user_id := FND_PROFILE.VALUE('AHL_ENIGMA_USER');
1110 l_resp_id := FND_PROFILE.VALUE('AHL_ENIGMA_RESPONSIBILITY');
1111 IF (l_log_statement >= l_log_current_level) THEN
1112 FND_LOG.string(l_log_statement, l_full_name, 'l_user_id = '||l_user_id||', l_resp_id = '||l_resp_id);
1113 END IF;
1114 BEGIN
1115 FND_GLOBAL.APPS_INITIALIZE(l_user_id, l_resp_id, l_app_id);
1116 EXCEPTION
1117 WHEN OTHERS THEN
1118 IF (l_log_statement >= l_log_current_level) THEN
1119 FND_LOG.string(l_log_statement, l_full_name, 'FND_GLOBAL.APPS_INITIALIZE call failed - '||SQLERRM);
1120 END IF;
1121 END;
1122
1123 -- get the org id from the new profile
1124 l_org_id := FND_PROFILE.VALUE('AHL_ENIGMA_ITEM_ORG');
1125
1126 IF (l_log_statement >= l_log_current_level) THEN
1127 FND_LOG.string(l_log_statement, l_full_name, 'l_org_id = '||l_org_id);
1128 END IF;
1129
1130 -- check for the profile value
1131 IF (l_org_id IS NULL) THEN
1132 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_ENIGMA_ORG_PRFL_NULL'); -- Profile 'AHL: Enigma Sourced Item Org' can't be NULL. (new message)
1133 FND_MSG_PUB.ADD;
1134 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1135 END IF;
1136
1137 -- set the inventory org id and the master org id in the header record
1138 l_ic_header_rec.inventory_org_id := l_org_id;
1139 l_ic_header_rec.inventory_master_org_id := l_org_id;
1140
1141 IF (l_log_statement >= l_log_current_level) THEN
1142 FND_LOG.string(l_log_statement, l_full_name,
1143 'operation flag for the header = '||l_ic_header_rec.operation_flag);
1144 END IF;
1145
1146 -- convert the operation flag: 'N' to 'C'
1147 IF (l_ic_header_rec.operation_flag = 'N') THEN
1148 l_ic_header_rec.operation_flag := 'C';
1149 ELSIF ((l_ic_header_rec.operation_flag <> 'M' AND l_ic_header_rec.operation_flag <> 'D') OR
1150 l_ic_header_rec.operation_flag IS NULL) THEN
1151 -- invalid operation flag
1152 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_ENIGMA_OPR_FLAG_INVALID'); -- Operation flag OPR_FLAG is invalid. (new message)
1153 FND_MESSAGE.Set_Token('OPR_FLAG', l_ic_header_rec.operation_flag);
1154 FND_MSG_PUB.ADD;
1155 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1156 END IF;
1157
1158 IF (l_log_statement >= l_log_current_level) THEN
1159 FND_LOG.string(l_log_statement, l_full_name, 'l_det_tbl.COUNT = '|| l_det_tbl.COUNT);
1160 END IF;
1161
1162 -- check for item details table
1163 IF (l_det_tbl.COUNT < 1) THEN
1164 IF (l_log_statement >= l_log_current_level) THEN
1165 FND_LOG.string(l_log_statement, l_full_name, 'no items for the composition');
1166 END IF;
1167 -- items table can't be empty for creation and update - it needs at least one item
1168 IF (l_ic_header_rec.operation_flag <> 'D') THEN
1169 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_ENIGMA_CL_NO_ITEMS'); -- At least one composition item is required to create or update the Composition List. (new message)
1170 FND_MSG_PUB.ADD;
1171 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1172 END IF;
1173 END IF;
1174
1175 -- check for the header's operation flag
1176 IF (l_ic_header_rec.operation_flag = 'D') THEN
1177 -------------------------- Remove Item Composition --------------------------
1178
1179 -- get the composition item id from the header item name
1180 OPEN get_ic_id_csr (l_ic_header_rec.inventory_item_name, l_org_id);
1181 FETCH get_ic_id_csr INTO l_ic_header_rec.item_composition_id, l_ic_header_rec.object_version_number;
1182 IF (get_ic_id_csr%NOTFOUND) THEN
1183 CLOSE get_ic_id_csr;
1184 -- item composition header is invalid
1185 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_COMP_HEADER_MISMATCH'); -- Item Composition header is invalid.
1186 FND_MSG_PUB.ADD;
1187 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1188 END IF;
1189 CLOSE get_ic_id_csr;
1190
1191 IF (l_log_statement >= l_log_current_level) THEN
1192 FND_LOG.string(l_log_statement, l_full_name,
1193 'Calling private API AHL_MC_ITEM_COMP_PVT.Delete_Item_Composition.');
1194 END IF;
1195
1196 -- call the private API
1197 AHL_MC_ITEM_COMP_PVT.Delete_Item_Composition(
1198 p_api_version => p_api_version,
1199 p_init_msg_list => p_init_msg_list,
1200 x_return_status => x_return_status,
1201 x_msg_count => x_msg_count,
1202 x_msg_data => x_msg_data,
1203 p_item_composition_id => l_ic_header_rec.item_composition_id,
1204 p_object_version_number => l_ic_header_rec.object_version_number
1205 );
1206
1207 IF (l_log_statement >= l_log_current_level) THEN
1208 FND_LOG.string(l_log_statement, l_full_name,
1209 'AHL_MC_ITEM_COMP_PVT.Delete_Item_Composition returned x_return_status as ' || x_return_status);
1210 END IF;
1211
1212 -- check for the return status
1213 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1214 IF (l_log_statement >= l_log_current_level) THEN
1215 FND_LOG.string(l_log_statement, l_full_name,
1216 'Raising exception with x_return_status = ' || x_return_status);
1217 END IF;
1218 RAISE FND_API.G_EXC_ERROR;
1219 END IF;
1220
1221 ELSIF (l_ic_header_rec.operation_flag = 'C') THEN
1222 -------------------------- Create Item Composition --------------------------
1223
1224 -- get the item id for the header item number
1225 OPEN get_item_id_csr (l_ic_header_rec.inventory_item_name, l_org_id);
1226 FETCH get_item_id_csr INTO l_ic_header_rec.inventory_item_id;
1227 IF (get_item_id_csr%NOTFOUND) THEN
1228 CLOSE get_item_id_csr;
1229 -- header item is invalid
1230 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_INV_INVALID'); -- Inventory Item (INV_ITEM) is invalid.
1231 FND_MESSAGE.Set_Token('INV_ITEM', l_ic_header_rec.inventory_item_name);
1232 FND_MSG_PUB.ADD;
1233 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1234 END IF;
1235 CLOSE get_item_id_csr;
1236
1237 IF (l_det_tbl.COUNT >= 1) THEN
1238 l_msg_count := FND_MSG_PUB.count_msg;
1239 -- perform validations and conversions for the item details
1240 FOR i IN l_det_tbl.FIRST..l_det_tbl.LAST LOOP
1241 -- convert the operation flag: 'N' to 'C'
1242 IF (l_det_tbl(i).operation_flag = 'N') THEN
1243 l_det_tbl(i).operation_flag := 'C';
1244 ELSIF ((l_det_tbl(i).operation_flag <> 'M' AND l_det_tbl(i).operation_flag <> 'D') OR
1245 l_det_tbl(i).operation_flag IS NULL) THEN
1246 -- invalid operation flag
1247 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_ENIGMA_OPR_FLAG_INVALID'); -- Operation flag OPR_FLAG is invalid. (new message)
1248 FND_MESSAGE.Set_Token('OPR_FLAG', l_det_tbl(i).operation_flag);
1249 FND_MSG_PUB.ADD;
1250 END IF;
1251
1252 -- set the inventory org id, and the master org id
1253 l_det_tbl(i).inventory_org_id := l_org_id;
1254 l_det_tbl(i).inventory_master_org_id := l_org_id;
1255
1256 -- fetch the item id from the item number
1257 OPEN get_item_id_csr (l_det_tbl(i).inventory_item_name, l_org_id);
1258 FETCH get_item_id_csr INTO l_det_tbl(i).inventory_item_id;
1259 IF (get_item_id_csr%NOTFOUND) THEN
1260 -- item is invalid
1261 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_INV_INVALID'); -- Inventory Item (INV_ITEM) is invalid.
1262 FND_MESSAGE.Set_Token('INV_ITEM', l_det_tbl(i).inventory_item_name);
1263 FND_MSG_PUB.ADD;
1264 END IF;
1265 CLOSE get_item_id_csr;
1266 END LOOP;
1267 IF (l_msg_count < FND_MSG_PUB.count_msg) THEN
1268 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1269 END IF;
1270 END IF;
1271
1272 IF (l_log_statement >= l_log_current_level) THEN
1273 FND_LOG.string(l_log_statement, l_full_name,
1274 'Calling private API AHL_MC_ITEM_COMP_PVT.Create_Item_Composition.');
1275 END IF;
1276
1277 -- call the private API
1278 AHL_MC_ITEM_COMP_PVT.Create_Item_Composition(
1279 p_api_version => p_api_version,
1280 p_init_msg_list => p_init_msg_list,
1281 x_return_status => x_return_status,
1282 x_msg_count => x_msg_count,
1283 x_msg_data => x_msg_data,
1284 p_x_ic_header_rec => l_ic_header_rec,
1285 p_x_det_tbl => l_det_tbl
1286 );
1287
1288 IF (l_log_statement >= l_log_current_level) THEN
1289 FND_LOG.string(l_log_statement, l_full_name,
1290 'AHL_MC_ITEM_COMP_PVT.Create_Item_Composition returned x_return_status as ' || x_return_status);
1291 END IF;
1292
1293 -- check for the return status
1294 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1295 IF (l_log_statement >= l_log_current_level) THEN
1296 FND_LOG.string(l_log_statement, l_full_name,
1297 'Raising exception with x_return_status = ' || x_return_status);
1298 END IF;
1299 RAISE FND_API.G_EXC_ERROR;
1300 END IF;
1301
1302 -- move the item composition to Complete status
1303 IF (l_log_statement >= l_log_current_level) THEN
1304 FND_LOG.string(l_log_statement, l_full_name,
1305 'Calling private API AHL_MC_ITEM_COMP_PVT.Approve_Item_Composiiton.');
1306 END IF;
1307
1308 -- call the private API
1309 AHL_MC_ITEM_COMP_PVT.Approve_Item_Composiiton(
1310 p_api_version => p_api_version,
1311 p_init_msg_list => p_init_msg_list,
1312 p_module_type => NULL, -- not used in the API
1313 x_return_status => x_return_status,
1314 x_msg_count => x_msg_count,
1315 x_msg_data => x_msg_data,
1316 p_appr_status => 'APPROVED',
1317 p_Item_comp_id => l_ic_header_rec.item_composition_id,
1318 p_object_version_number => 1
1319 );
1320
1321 IF (l_log_statement >= l_log_current_level) THEN
1322 FND_LOG.string(l_log_statement, l_full_name,
1323 'AHL_MC_ITEM_COMP_PVT.Approve_Item_Composiiton returned x_return_status as ' || x_return_status);
1324 END IF;
1325
1326 -- check for the return status
1327 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1328 IF (l_log_statement >= l_log_current_level) THEN
1329 FND_LOG.string(l_log_statement, l_full_name,
1330 'Raising exception with x_return_status = ' || x_return_status);
1331 END IF;
1332 RAISE FND_API.G_EXC_ERROR;
1333 END IF;
1334
1335 -- update the new field SOURCED_FROM_ENIGMA_FLAG in the table AHL_ITEM_COMPOSITIONS
1336 UPDATE ahl_item_compositions
1337 SET sourced_from_enigma_flag = 'Y'
1338 WHERE item_composition_id = l_ic_header_rec.item_composition_id;
1339
1340 ELSIF (l_ic_header_rec.operation_flag = 'M') THEN
1341 -------------------------- Update Item Composition --------------------------
1342
1343 -- get the composition item id from the header item name
1344 OPEN get_ic_id_csr (l_ic_header_rec.inventory_item_name, l_org_id);
1345 FETCH get_ic_id_csr INTO l_ic_header_rec.item_composition_id, l_ic_header_rec.object_version_number;
1346 IF (get_ic_id_csr%NOTFOUND) THEN
1347 CLOSE get_ic_id_csr;
1348 -- item composition header is invalid
1349 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_COMP_HEADER_MISMATCH'); -- Item Composition header is invalid.
1350 FND_MSG_PUB.ADD;
1351 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1352 END IF;
1353 CLOSE get_ic_id_csr;
1354
1355 IF (l_det_tbl.COUNT >= 1) THEN
1356 l_msg_count := FND_MSG_PUB.count_msg;
1357 -- perform validations and conversions for the item associations
1358 FOR i IN l_det_tbl.FIRST..l_det_tbl.LAST LOOP
1359 -- convert the operation flag: 'N' to 'C'
1360 IF (l_det_tbl(i).operation_flag = 'N') THEN
1361 l_det_tbl(i).operation_flag := 'C';
1362 ELSIF ((l_det_tbl(i).operation_flag <> 'M' AND l_det_tbl(i).operation_flag <> 'D') OR
1363 l_det_tbl(i).operation_flag IS NULL) THEN
1364 -- invalid operation flag
1365 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_ENIGMA_OPR_FLAG_INVALID'); -- Operation flag OPR_FLAG is invalid. (new message)
1366 FND_MESSAGE.Set_Token('OPR_FLAG', l_det_tbl(i).operation_flag);
1367 FND_MSG_PUB.ADD;
1368 END IF;
1369
1370 -- set the inventory org id, and the master org id
1371 l_det_tbl(i).inventory_org_id := l_org_id;
1372 l_det_tbl(i).inventory_master_org_id := l_org_id;
1373
1374 -- fetch the item id from the item number
1375 OPEN get_item_id_csr (l_det_tbl(i).inventory_item_name, l_org_id);
1376 FETCH get_item_id_csr INTO l_det_tbl(i).inventory_item_id;
1377 IF (get_item_id_csr%NOTFOUND) THEN
1378 -- item is invalid
1379 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_INV_INVALID'); -- Inventory Item (INV_ITEM) is invalid.
1380 FND_MESSAGE.Set_Token('INV_ITEM', l_det_tbl(i).inventory_item_name);
1381 FND_MSG_PUB.ADD;
1382 END IF;
1383 CLOSE get_item_id_csr;
1384
1385 -- for update and delete operations, fetch the item detail id
1386 IF (l_det_tbl(i).operation_flag <> 'C') THEN
1387 OPEN get_ic_det_id_csr (l_ic_header_rec.item_composition_id, l_det_tbl(i).inventory_item_id, l_org_id);
1388 FETCH get_ic_det_id_csr INTO l_det_tbl(i).item_comp_detail_id;
1389 IF (get_ic_det_id_csr%NOTFOUND) THEN
1390 -- composition item is invalid
1391 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_IC_INVALID'); -- Invalid composition item ITEM. (new message)
1392 FND_MESSAGE.Set_Token('ITEM', l_det_tbl(i).inventory_item_name);
1393 FND_MSG_PUB.ADD;
1394 END IF;
1395 CLOSE get_ic_det_id_csr;
1396
1397 -- set the item composition id as well
1398 l_det_tbl(i).item_composition_id := l_ic_header_rec.item_composition_id;
1399 END IF;
1400 END LOOP;
1401 IF (l_msg_count < FND_MSG_PUB.count_msg) THEN
1402 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1403 END IF;
1404 END IF;
1405
1406 IF (l_log_statement >= l_log_current_level) THEN
1407 FND_LOG.string(l_log_statement, l_full_name,
1408 'Calling private API AHL_MC_ITEM_COMP_PVT.Modify_Item_Composition.');
1409 END IF;
1410
1411 -- call the private API
1412 AHL_MC_ITEM_COMP_PVT.Modify_Item_Composition(
1413 p_api_version => p_api_version,
1414 p_init_msg_list => p_init_msg_list,
1415 x_return_status => x_return_status,
1416 x_msg_count => x_msg_count,
1417 x_msg_data => x_msg_data,
1418 p_x_ic_header_rec => l_ic_header_rec,
1419 p_x_det_tbl => l_det_tbl
1420 );
1421
1422 IF (l_log_statement >= l_log_current_level) THEN
1423 FND_LOG.string(l_log_statement, l_full_name,
1424 'AHL_MC_ITEM_COMP_PVT.Modify_Item_Composition returned x_return_status as ' || x_return_status);
1425 END IF;
1426
1427 -- check for the return status
1428 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1429 IF (l_log_statement >= l_log_current_level) THEN
1430 FND_LOG.string(l_log_statement, l_full_name,
1431 'Raising exception with x_return_status = ' || x_return_status);
1432 END IF;
1433 RAISE FND_API.G_EXC_ERROR;
1434 END IF;
1435
1436 END IF; -- header's operation flag check
1437
1438 -- Standard check of p_commit
1439 IF FND_API.TO_BOOLEAN(p_commit) THEN
1440 COMMIT WORK;
1441 END IF;
1442
1443 -- enter the success log
1444 Enter_Process_Log (
1445 p_report_type => l_ic_report_type,
1446 p_report_file_name => p_report_file_name,
1447 p_object_name => l_ic_header_rec.inventory_item_name,
1448 p_operation_flag => l_ic_header_rec.operation_flag,
1449 p_process_status => FND_API.G_RET_STS_SUCCESS,
1450 p_msg_count => 0
1451 );
1452
1453 IF (l_log_procedure >= l_log_current_level) THEN
1454 FND_LOG.string(l_log_procedure, l_full_name || '.end', 'End of the API');
1455 END IF;
1456
1457 EXCEPTION
1458 WHEN FND_API.G_EXC_ERROR THEN
1459 Rollback to Process_Item_Composition_Pub;
1460 x_return_status := FND_API.G_RET_STS_ERROR;
1461 x_msg_count := FND_MSG_PUB.count_msg;
1462 -- enter the error log
1463 Enter_Process_Log (
1464 p_report_type => l_ic_report_type,
1465 p_report_file_name => p_report_file_name,
1466 p_object_name => l_ic_header_rec.inventory_item_name,
1467 p_operation_flag => l_ic_header_rec.operation_flag,
1468 p_process_status => x_return_status,
1469 p_msg_count => x_msg_count
1470 );
1471
1472 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1473 Rollback to Process_Item_Composition_Pub;
1474 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1475 x_msg_count := FND_MSG_PUB.count_msg;
1476 -- enter the error log
1477 Enter_Process_Log (
1478 p_report_type => l_ic_report_type,
1479 p_report_file_name => p_report_file_name,
1480 p_object_name => l_ic_header_rec.inventory_item_name,
1481 p_operation_flag => l_ic_header_rec.operation_flag,
1482 p_process_status => x_return_status,
1483 p_msg_count => x_msg_count
1484 );
1485
1486 WHEN OTHERS THEN
1487 Rollback to Process_Item_Composition_Pub;
1488 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1489 FND_MSG_PUB.Add_Exc_Msg( p_pkg_name => G_PKG_NAME,
1490 p_procedure_name => l_api_name,
1491 p_error_text => SQLERRM);
1492 x_msg_count := FND_MSG_PUB.count_msg;
1493 -- enter the error log
1494 Enter_Process_Log (
1495 p_report_type => l_ic_report_type,
1496 p_report_file_name => p_report_file_name,
1497 p_object_name => l_ic_header_rec.inventory_item_name,
1498 p_operation_flag => l_ic_header_rec.operation_flag,
1499 p_process_status => x_return_status,
1500 p_msg_count => x_msg_count
1501 );
1502
1503 END Process_Item_Composition;
1504
1505 ------------------------------------------------------------------------------------
1506 -- Start of Comments
1507 -- Procedure name : Invoke_IPC_Process
1508 -- Type : Public
1509 -- Function : Procedure to invoke BPEL IPC process AhlProcessIPCReports. Will be
1510 -- configured as a Concurrent Program.
1511 -- Pre-reqs :
1512 -- Parameters :
1513 --
1514 -- Process_Item_Group Parameters: None
1515 --
1516 -- End of Comments
1517
1518 PROCEDURE Invoke_IPC_Process (
1519 errbuf OUT NOCOPY VARCHAR2,
1520 retcode OUT NOCOPY NUMBER
1521 ) IS
1522
1523 --
1524 l_api_name CONSTANT VARCHAR2(30) := 'Invoke_IPC_Process';
1525 l_full_name CONSTANT VARCHAR2(60) := 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name;
1526
1527 l_xml_input VARCHAR2(1000);
1528 l_xml_output VARCHAR2(32000);
1529 l_temp_string VARCHAR2(32000);
1530 l_process_URL VARCHAR2(500) := NULL;
1531 l_start_index NUMBER;
1532 l_end_index NUMBER;
1533 l_length NUMBER;
1534 http_req UTL_HTTP.REQ;
1535 http_resp UTL_HTTP.RESP;
1536 --
1537
1538 BEGIN
1539 FND_FILE.PUT_LINE(FND_FILE.LOG, l_full_name||', start');
1540 FND_FILE.NEW_LINE(FND_FILE.LOG, 1);
1541 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Start time: '||TO_CHAR(SYSDATE, 'Month DD, YYYY HH24:MI:SS'));
1542 FND_FILE.NEW_LINE(FND_FILE.LOG, 1);
1543
1544 -- initialize return status to success
1545 retcode := 0;
1546
1547 -- fetch the BPEL IPC process AhlProcessIPCReports URL from the profile 'AHL: BPEL IPC Process (AhlProcessIPCReports) URL'
1548 l_process_URL := FND_PROFILE.VALUE('AHL_ENIGMA_BPEL_IPC_URL');
1549
1550 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Prcoess URL: '||l_process_URL);
1551 FND_FILE.NEW_LINE(FND_FILE.LOG, 1);
1552
1553 -- check for process URL
1554 IF (l_process_URL IS NULL) THEN
1555 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error occurred. Details: '||
1556 'profile AHL: BPEL IPC Process (AhlProcessIPCReports) URL is not set.');
1557 FND_FILE.NEW_LINE(FND_FILE.LOG, 1);
1558 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1559 END IF;
1560
1561 -- create the empty XML input for the process
1562 l_xml_input := ' <env:Envelope xmlns:env="http://schemas.xmlsoap.org/soap/envelope/" '||
1563 ' xmlns:ns1="http://xmlns.oracle.com/AhlProcessIPCReports"> '||
1564 ' <env:Header/> '||
1565 ' <env:Body> '||
1566 ' <ns1:AhlProcessIPCReportsProcessRequest> '||
1567 ' <ns1:input></ns1:input> '||
1568 ' </ns1:AhlProcessIPCReportsProcessRequest> '||
1569 ' </env:Body> '||
1570 ' </env:Envelope> ';
1571
1572 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Process input: '||l_xml_input);
1573 FND_FILE.NEW_LINE(FND_FILE.LOG, 1);
1574
1575 -- create the HTTP request
1576 http_req := UTL_HTTP.BEGIN_REQUEST
1577 ( l_process_URL,
1578 'POST',
1579 'HTTP/1.1'
1580 );
1581
1582 -- set the request header properties and write the XML input in it
1583 UTL_HTTP.SET_HEADER(http_req, 'Content-Type', 'text/xml');
1584 UTL_HTTP.SET_HEADER(http_req, 'Content-Length', LENGTH(l_xml_input));
1585 UTL_HTTP.SET_HEADER(http_req, 'SOAPAction', 'process');
1586 UTL_HTTP.WRITE_TEXT(http_req, l_xml_input);
1587
1588 -- get the response and write it in the XML output
1589 http_resp := UTL_HTTP.GET_RESPONSE(http_req);
1590 UTL_HTTP.READ_TEXT(http_resp, l_xml_output);
1591 UTL_HTTP.END_RESPONSE(http_resp);
1592
1593 -- fetch the process errors, if any, from the <faultstring xmlns=""> tag
1594 l_start_index := INSTR(l_xml_output, '<faultstring xmlns="">') + 22; -- 22 is to accommodate length of '<faultstring xmlns="">'
1595 IF (l_start_index <> 22) THEN
1596 l_end_index := INSTR(l_xml_output, '</faultstring>');
1597 l_length := l_end_index - l_start_index;
1598 l_temp_string := SUBSTR(l_xml_output, l_start_index, l_length);
1599
1600 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error occurred. Details:');
1601 FND_FILE.NEW_LINE(FND_FILE.LOG, 2);
1602 FND_FILE.PUT_LINE(FND_FILE.LOG, l_temp_string);
1603 FND_FILE.NEW_LINE(FND_FILE.LOG, 2);
1604
1605 l_start_index := INSTR(l_xml_output, '<summary>') + 9; -- 9 is to accommodate length of '<summary>'
1606 IF (l_start_index <> 9) THEN
1607 l_end_index := INSTR(l_xml_output, '</summary>');
1608 l_length := l_end_index - l_start_index;
1609 l_temp_string := SUBSTR(l_xml_output, l_start_index, l_length);
1610
1611 FND_FILE.PUT_LINE(FND_FILE.LOG, l_temp_string);
1612 FND_FILE.NEW_LINE(FND_FILE.LOG, 2);
1613 END IF;
1614
1615 l_start_index := INSTR(l_xml_output, '<detail>') + 8; -- 8 is to accommodate length of '<detail>'
1616 IF (l_start_index <> 8) THEN
1617 l_end_index := INSTR(l_xml_output, '</detail>');
1618 l_length := l_end_index - l_start_index;
1619 l_temp_string := SUBSTR(l_xml_output, l_start_index, l_length);
1620
1621 FND_FILE.PUT_LINE(FND_FILE.LOG, l_temp_string);
1622 FND_FILE.NEW_LINE(FND_FILE.LOG, 2);
1623 END IF;
1624
1625 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1626 END IF;
1627
1628 -- fetch the process output from the <result> tag
1629 l_start_index := INSTR(l_xml_output, '<result>') + 8; -- 8 is to accommodate length of '<result>'
1630 l_end_index := INSTR(l_xml_output, '</result>');
1631 l_length := l_end_index - l_start_index;
1632 l_xml_output := SUBSTR(l_xml_output, l_start_index, l_length);
1633
1634 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Process output: '||l_xml_output);
1635 FND_FILE.NEW_LINE(FND_FILE.LOG, 1);
1636
1637 -- put the process output in the Concurrent Program's output
1638 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, l_xml_output);
1639
1640 FND_FILE.PUT_LINE(FND_FILE.LOG, l_full_name||', end');
1641 FND_FILE.NEW_LINE(FND_FILE.LOG, 1);
1642 FND_FILE.PUT_LINE(FND_FILE.LOG, 'End time: '||TO_CHAR(SYSDATE, 'Month DD, YYYY HH24:MI:SS'));
1643
1644 EXCEPTION
1645 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1646 retcode := 2;
1647 errbuf := l_full_name||', error occurred.';
1648 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Process failed. View program logs for details.');
1649
1650 WHEN OTHERS THEN
1651 retcode := 2;
1652 errbuf := l_full_name||': '||SUBSTRB(SQLERRM, 1, 200);
1653 FND_FILE.PUT_LINE(FND_FILE.LOG, l_full_name||', exception occurred: '||SUBSTRB(SQLERRM, 1, 200));
1654 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Process failed. View program logs for details.');
1655
1656 END Invoke_IPC_Process;
1657
1658 END AHL_ENIGMA_IPC_PROCS_PVT;
[Home] [Help]