1 PACKAGE BODY AHL_SB_RULES_PVT AS
2 /* $Header: AHLVSBRB.pls 120.3.12020000.2 2012/12/07 14:37:27 sareepar noship $ */
3
4 -- Package constants
5 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AHL_SB_RULES_PVT';
6 G_APP_NAME CONSTANT VARCHAR2(3) := 'AHL';
7
8 ------------------------------------------------------------------------------------
9 -- Common variables
10 ------------------------------------------------------------------------------------
11 l_log_current_level NUMBER := FND_LOG.g_current_runtime_level;
12 l_log_statement NUMBER := FND_LOG.level_statement;
13 l_log_procedure NUMBER := FND_LOG.level_procedure;
14 l_log_error NUMBER := FND_LOG.level_error;
15 l_log_exception NUMBER := FND_LOG.level_exception;
16 l_log_unexpected NUMBER := FND_LOG.level_unexpected;
17 ------------------------------------------------------------------------------------
18
19 ------------------------------------------------------------------------------------
20 -- Start of Comments
21 -- Procedure name : Check_Unit_Completeness
22 -- Type : Public
23 -- Function : If visit id is given:
24 -- - it's a call from workorder UIs
25 -- - along with the MRs accomplished on the unit, consider the ones attached to the visit too
26 -- Else:
27 -- - it's a call from UC Validate UI
28 -- - consider just the MRs accomplished on the unit
29 -- Gives the error messages pointing to the erring rules.
30 -- Pre-reqs :
31 -- Parameters :
32 --
33 -- Check_Unit_Completeness Parameters:
34 -- p_uc_header_id IN Given unit header id. Required
35 -- p_visit_id IN Given visit id. Required
36 --
37 -- End of Comments
38
39 PROCEDURE Check_Unit_Completeness (
40 p_api_version IN NUMBER := 1.0,
41 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
42 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
43 p_module_type IN VARCHAR2 := NULL,
44 p_uc_header_id IN NUMBER,
45 p_visit_id IN NUMBER,
46 p_x_erring_rules_tbl IN OUT NOCOPY AHL_UC_VALIDATION_PUB.Error_Tbl_Type,
47 x_return_status OUT NOCOPY VARCHAR2,
48 x_msg_count OUT NOCOPY NUMBER,
49 x_msg_data OUT NOCOPY VARCHAR2
50 ) IS
51
52 -- cursor to check the unit header id
53 CURSOR chk_unit_header_id_csr (c_uc_header_id NUMBER) IS
54 SELECT name
55 FROM AHL_UNIT_CONFIG_HEADERS
56 WHERE unit_config_header_id = c_uc_header_id
57 AND TRUNC(NVL(active_start_date, SYSDATE)) <= TRUNC(SYSDATE)
58 AND TRUNC(NVL(active_end_date, SYSDATE+1)) > TRUNC(SYSDATE);
59
60 -- cursor to check the visit id
61 CURSOR chk_visit_id_csr (c_visit_id NUMBER) IS
62 SELECT 'X'
63 FROM AHL_VISITS_B
64 WHERE visit_id = c_visit_id;
65
66 -- cursor to get applicable SB rules for the given unit
67 CURSOR get_appl_sb_rules_csr1 (c_uc_header_id NUMBER) IS
68 SELECT SB.rule_id,
69 SB.rule_name,
70 SB.relationship_id
71 FROM AHL_SB_POSITION_RULES SB,
72 AHL_UNIT_CONFIG_HEADERS UC
73 WHERE UC.unit_config_header_id = c_uc_header_id
74 AND SB.mc_header_id = UC.master_config_id
75 -- take only the rules which have MRs accomplished on the unit
76 AND EXISTS (SELECT 'X'
77 FROM AHL_UNIT_EFFECTIVITIES_B UE
78 WHERE UE.mr_header_id = SB.mr_header_id
79 AND UE.csi_item_instance_id = UC.csi_item_instance_id -- SB MRs are effective on root node
80 AND UE.accomplished_date IS NOT NULL)
81 -- take only the MAX sequence rules for a position, among the accomplished MRs
82 AND SB.rule_sequence = (SELECT MAX(SB2.rule_sequence)
83 FROM AHL_SB_POSITION_RULES SB2
84 WHERE SB2.relationship_id = SB.relationship_id
85 AND EXISTS (SELECT 'X'
86 FROM AHL_UNIT_EFFECTIVITIES_B UE
87 WHERE UE.mr_header_id = SB2.mr_header_id
88 AND UE.csi_item_instance_id = UC.csi_item_instance_id
89 AND UE.accomplished_date IS NOT NULL))
90 AND TRUNC(NVL(SB.active_start_date, SYSDATE)) <= TRUNC(SYSDATE)
91 AND TRUNC(NVL(SB.active_end_date, SYSDATE+1)) > TRUNC(SYSDATE);
92
93 -- cursor to get applicable SB rules for the given unit and visit
94 CURSOR get_appl_sb_rules_csr2 (c_uc_header_id NUMBER,
95 c_visit_id NUMBER) IS
96 SELECT SB.rule_id,
97 SB.rule_name,
98 SB.relationship_id
99 FROM AHL_SB_POSITION_RULES SB,
100 AHL_UNIT_CONFIG_HEADERS UC
101 WHERE UC.unit_config_header_id = c_uc_header_id
102 AND SB.mc_header_id = UC.master_config_id
103 -- take only the rules which have MRs accomplished on the unit
104 AND EXISTS (SELECT 'X'
105 FROM AHL_UNIT_EFFECTIVITIES_B UE
106 WHERE UE.mr_header_id = SB.mr_header_id
107 AND UE.csi_item_instance_id = UC.csi_item_instance_id -- SB MRs are effective on root node
108 AND UE.accomplished_date IS NOT NULL)
109 -- take only the MAX sequence rules for a position, among the accomplished MRs
110 AND SB.rule_sequence = (SELECT MAX(SB2.rule_sequence)
111 FROM AHL_SB_POSITION_RULES SB2
112 WHERE SB2.relationship_id = SB.relationship_id
113 AND EXISTS (SELECT 'X'
114 FROM AHL_UNIT_EFFECTIVITIES_B UE
115 WHERE UE.mr_header_id = SB2.mr_header_id
116 AND UE.csi_item_instance_id = UC.csi_item_instance_id
117 AND UE.accomplished_date IS NOT NULL))
118 AND TRUNC(NVL(SB.active_start_date, SYSDATE)) <= TRUNC(SYSDATE)
119 AND TRUNC(NVL(SB.active_end_date, SYSDATE+1)) > TRUNC(SYSDATE)
120 UNION
121 SELECT SB.rule_id,
122 SB.rule_name,
123 SB.relationship_id
124 FROM AHL_SB_POSITION_RULES SB,
125 AHL_VISIT_TASKS_B VT,
126 AHL_UNIT_CONFIG_HEADERS UC
127 WHERE UC.unit_config_header_id = c_uc_header_id
128 AND SB.mc_header_id = UC.master_config_id
129 -- take only the rules which have MRs attached to the visit
130 AND VT.visit_id = c_visit_id
131 AND VT.mr_id = SB.mr_header_id
132 AND VT.status_code NOT IN ('CANCELLED', 'DELETED')
133 -- take only the MAX sequence rules for a position
134 AND SB.rule_sequence = (SELECT MAX(SB2.rule_sequence)
135 FROM AHL_SB_POSITION_RULES SB2
136 WHERE SB2.relationship_id = SB.relationship_id
137 AND SB2.mr_header_id = SB.mr_header_id)
138 AND TRUNC(NVL(SB.active_start_date, SYSDATE)) <= TRUNC(SYSDATE)
139 AND TRUNC(NVL(SB.active_end_date, SYSDATE+1)) > TRUNC(SYSDATE);
140
141 -- cursor to validate the given SB rule against the given unit and relationship
142 CURSOR validate_sb_rule_csr (c_uc_header_id NUMBER,
143 c_rule_id NUMBER,
144 c_relationship_id NUMBER) IS
145 SELECT 'X'
146 FROM AHL_SB_RULE_ITEMS SBI,
147 AHL_ITEM_ASSOCIATIONS_B IAB,
148 CSI_ITEM_INSTANCES CSI,
149 CSI_II_RELATIONSHIPS CSR
150 WHERE SBI.rule_id = c_rule_id
151 AND IAB.item_association_id = SBI.item_association_id
152 AND CSI.inventory_item_id = IAB.inventory_item_id
153 AND CSI.inv_master_organization_id = IAB.inventory_org_id
154 AND CSR.subject_id = CSI.instance_id -- SB rules are created for non-root positions
155 AND CSR.position_reference = TO_CHAR(c_relationship_id)
156 AND CSR.relationship_type_code = 'COMPONENT-OF'
157 AND TRUNC(NVL(CSR.active_end_date, SYSDATE+1)) > TRUNC(SYSDATE)
158 AND AHL_UTIL_UC_PKG.get_sub_uc_header_id(CSR.object_id) = c_uc_header_id;
159
160 --
161 l_api_version CONSTANT NUMBER := 1.0;
162 l_api_name CONSTANT VARCHAR2(30) := 'Check_Unit_Completeness';
163 l_full_name CONSTANT VARCHAR2(100) := 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name;
164
165 TYPE t_id_tbl IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
166 TYPE t_rule_name_tbl IS TABLE OF VARCHAR2(80) INDEX BY BINARY_INTEGER;
167
168 l_rule_id_tbl t_id_tbl;
169 l_rule_name_tbl t_rule_name_tbl;
170 l_rel_id_tbl t_id_tbl;
171
172 l_unit_name VARCHAR2(80);
173 l_evaluation_status VARCHAR2(1);
174 l_dummy VARCHAR2(1);
175 --
176
177 BEGIN
178 IF (l_log_procedure >= l_log_current_level) THEN
179 FND_LOG.string(l_log_procedure, l_full_name || '.begin', 'At the start of the API');
180 END IF;
181
182 -- Standard start of API savepoint
183 SAVEPOINT Check_Unit_Completeness_Pvt;
184
185 -- Standard call to check for call compatibility
186 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version,
187 l_api_name, G_PKG_NAME) THEN
188 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
189 END IF;
190
191 -- Initialize message list if p_init_msg_list is set to TRUE
192 IF FND_API.To_Boolean( p_init_msg_list) THEN
193 FND_MSG_PUB.Initialize;
194 END IF;
195
196 -- Initialize Procedure return status to success
197 x_return_status := FND_API.G_RET_STS_SUCCESS;
198
199 IF (l_log_statement >= l_log_current_level) THEN
200 FND_LOG.string(l_log_statement, l_full_name,
201 ' p_uc_header_id = '||p_uc_header_id||
202 ', p_visit_id = '||p_visit_id||
203 ', p_x_erring_rules_tbl.COUNT = '||p_x_erring_rules_tbl.COUNT);
204 END IF;
205
206 -- Check for the unit header id
207 OPEN chk_unit_header_id_csr(p_uc_header_id);
208 FETCH chk_unit_header_id_csr INTO l_unit_name;
209 IF (chk_unit_header_id_csr%NOTFOUND) THEN
210 -- invalid unit header id
211 CLOSE chk_unit_header_id_csr;
212 IF (l_log_statement >= l_log_current_level) THEN
213 FND_LOG.string(l_log_statement, l_full_name, 'invalid unit header id');
214 END IF;
215 -- The Unit Configuration header identifier UC_HEADER_ID is invalid.
216 FND_MESSAGE.Set_Name(G_APP_NAME, 'AHL_UC_HEADER_ID_INVALID');
217 FND_MESSAGE.Set_Token('UC_HEADER_ID', p_uc_header_id);
218 FND_MSG_PUB.ADD;
219 RAISE FND_API.G_EXC_ERROR;
220 END IF;
221 CLOSE chk_unit_header_id_csr;
222
223 -- Check for the visit id
224 IF (p_visit_id IS NOT NULL) THEN
225 -- It's a call from workorder UIs
226 -- Validate the visit id
227 OPEN chk_visit_id_csr(p_visit_id);
228 FETCH chk_visit_id_csr INTO l_dummy;
229 IF (chk_visit_id_csr%NOTFOUND) THEN
230 -- invalid visit id
231 CLOSE chk_visit_id_csr;
232 IF (l_log_statement >= l_log_current_level) THEN
233 FND_LOG.string(l_log_statement, l_full_name, 'invalid visit id');
234 END IF;
235 -- Visit id VISIT_ID is invalid.
236 FND_MESSAGE.Set_Name(G_APP_NAME, 'AHL_VWP_INVALID_VST');
237 FND_MESSAGE.Set_Token('VISIT_ID', p_visit_id);
238 FND_MSG_PUB.ADD;
239 RAISE FND_API.G_EXC_ERROR;
240 END IF;
241 CLOSE chk_visit_id_csr;
242 END IF; -- visit id NULL check
243
244 -- Get the applicable SB rules
245 IF (p_visit_id IS NULL) THEN
246 -- It's a call from UC validate UI
247 -- consider just the accomplished MRs
248 OPEN get_appl_sb_rules_csr1(p_uc_header_id);
249 FETCH get_appl_sb_rules_csr1 BULK COLLECT INTO l_rule_id_tbl, l_rule_name_tbl, l_rel_id_tbl;
250 CLOSE get_appl_sb_rules_csr1;
251 ELSE
252 -- It's a call from workorder UIs
253 -- along with the accomplished MRs, consider the visit MRs too
254 OPEN get_appl_sb_rules_csr2(p_uc_header_id, p_visit_id);
255 FETCH get_appl_sb_rules_csr2 BULK COLLECT INTO l_rule_id_tbl, l_rule_name_tbl, l_rel_id_tbl;
256 CLOSE get_appl_sb_rules_csr2;
257 END IF;
258
259 IF (l_log_statement >= l_log_current_level) THEN
260 FND_LOG.string(l_log_statement, l_full_name,
261 ' l_rule_id_tbl.COUNT = '||l_rule_id_tbl.COUNT||
262 ', l_rule_name_tbl.COUNT = '||l_rule_name_tbl.COUNT||
263 ', l_rel_id_tbl.COUNT = '||l_rel_id_tbl.COUNT);
264 END IF;
265
266 -- Validate the rules thus fetched above
267 IF (l_rule_id_tbl.COUNT > 0) THEN
268 FOR i IN l_rule_id_tbl.FIRST..l_rule_id_tbl.LAST LOOP
269 OPEN validate_sb_rule_csr(p_uc_header_id, l_rule_id_tbl(i), l_rel_id_tbl(i));
270 FETCH validate_sb_rule_csr INTO l_dummy;
271 IF (validate_sb_rule_csr%NOTFOUND) THEN
272 -- Put this erring rule in the OUT table parameter
273 -- Rule RULE_NAME evaluation failed for the unit UNIT_NAME.
274 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_MC_RULE_FAILED');
275 FND_MESSAGE.Set_Token('RULE_NAME',l_rule_name_tbl(i));
276 FND_MESSAGE.Set_Token('UNIT_NAME',l_unit_name);
277 IF (p_x_erring_rules_tbl.COUNT > 0) THEN
278 p_x_erring_rules_tbl(p_x_erring_rules_tbl.LAST+1) := FND_MESSAGE.Get;
279 ELSE
280 p_x_erring_rules_tbl(0) := FND_MESSAGE.Get;
281 END IF;
282 IF (l_log_statement >= l_log_current_level) THEN
283 FND_LOG.string(l_log_statement, l_full_name, 'rule violated = '||l_rule_name_tbl(i));
284 END IF;
285 END IF;
286 CLOSE validate_sb_rule_csr;
287 END LOOP;
288 END IF;
289
290 IF (l_log_statement >= l_log_current_level) THEN
291 FND_LOG.string(l_log_statement, l_full_name,
292 ' p_x_erring_rules_tbl.COUNT = '||p_x_erring_rules_tbl.COUNT);
293 END IF;
294
295 IF (l_log_procedure >= l_log_current_level) THEN
296 FND_LOG.string(l_log_procedure, l_full_name || '.end', 'At the end of the API');
297 END IF;
298
299 EXCEPTION
300 WHEN FND_API.G_EXC_ERROR THEN
301 ROLLBACK TO Check_Unit_Completeness_Pvt;
302 x_return_status := FND_API.G_RET_STS_ERROR;
303 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
304 p_data => x_msg_data,
305 p_encoded => FND_API.G_FALSE);
306 IF (l_log_exception >= l_log_current_level) THEN
307 FND_LOG.string(l_log_exception, l_full_name, 'Execution Error: ' || x_msg_data);
308 END IF;
309
310 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
311 ROLLBACK TO Check_Unit_Completeness_Pvt;
312 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
313 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
314 p_data => x_msg_data,
315 p_encoded => FND_API.G_FALSE);
316 IF (l_log_unexpected >= l_log_current_level) THEN
317 FND_LOG.string(l_log_unexpected, l_full_name, 'Unexpected Exception: ' || x_msg_data);
318 END IF;
319
320 WHEN OTHERS THEN
321 ROLLBACK TO Check_Unit_Completeness_Pvt;
322 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
323 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
324 FND_MSG_PUB.add_exc_msg(p_pkg_name => G_PKG_NAME,
325 p_procedure_name => l_api_name,
326 p_error_text => SUBSTR(SQLERRM,1,500));
327 END IF;
328 FND_MSG_PUB.count_and_get(p_count => x_msg_count,
329 p_data => x_msg_data,
330 p_encoded => fnd_api.G_FALSE);
331 IF (l_log_unexpected >= l_log_current_level) THEN
332 FND_LOG.string(l_log_unexpected, l_full_name, 'Other Exception: ' || x_msg_data);
333 END IF;
334
335 END Check_Unit_Completeness;
336 ------------------------------------------------------------------------------------
337
338 ------------------------------------------------------------------------------------
339 -- Start of Comments
340 -- Procedure name : Check_Inst_Completeness
341 -- Type : Public
342 -- Function : Checks for the validity of applicable SB rules, if any, for a given instance.
343 -- Gives the error messages pointing to the erring rules.
344 -- Pre-reqs :
345 -- Parameters :
346 --
347 -- Check_Inst_Completeness Parameters:
348 -- p_csi_instance_id IN Given instance id. Required
349 --
350 -- End of Comments
351
352 PROCEDURE Check_Inst_Completeness (
353 p_api_version IN NUMBER := 1.0,
354 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
355 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
356 p_module_type IN VARCHAR2 := NULL,
357 p_csi_instance_id IN NUMBER,
358 p_x_erring_rules_tbl IN OUT NOCOPY AHL_UC_VALIDATION_PUB.Error_Tbl_Type,
359 x_return_status OUT NOCOPY VARCHAR2,
360 x_msg_count OUT NOCOPY NUMBER,
361 x_msg_data OUT NOCOPY VARCHAR2
362 ) IS
363
364 -- cursor to check the instance id
365 CURSOR chk_instance_id_csr (c_instance_id NUMBER) IS
366 SELECT 'X'
367 FROM CSI_ITEM_INSTANCES
368 WHERE instance_id = c_instance_id
369 AND TRUNC(NVL(active_start_date, SYSDATE)) <= TRUNC(SYSDATE)
370 AND TRUNC(NVL(active_end_date, SYSDATE+1)) > TRUNC(SYSDATE);
371
372 -- cursor to get the name of the given unit
373 CURSOR get_unit_name_csr (c_uc_header_id NUMBER) IS
374 SELECT name
375 FROM AHL_UNIT_CONFIG_HEADERS
376 WHERE unit_config_header_id = c_uc_header_id
377 AND TRUNC(NVL(active_start_date, SYSDATE)) <= TRUNC(SYSDATE)
378 AND TRUNC(NVL(active_end_date, SYSDATE+1)) > TRUNC(SYSDATE);
379
380 -- cursor to get applicable SB rules for the given unit and instance (position)
381 CURSOR get_appl_sb_rules_csr (c_uc_header_id NUMBER,
382 c_instance_id NUMBER) IS
383 SELECT SB.rule_id,
384 SB.rule_name,
385 SB.relationship_id
386 FROM AHL_SB_POSITION_RULES SB,
387 AHL_UNIT_CONFIG_HEADERS UC
388 WHERE UC.unit_config_header_id = c_uc_header_id
389 AND SB.mc_header_id = UC.master_config_id
390 -- take only the rules which have MRs accomplished on the unit
391 AND EXISTS (SELECT 'X'
392 FROM AHL_UNIT_EFFECTIVITIES_B UE
393 WHERE UE.mr_header_id = SB.mr_header_id
394 AND UE.csi_item_instance_id = UC.csi_item_instance_id -- SB MRs are effective on root node
395 AND UE.accomplished_date IS NOT NULL)
396 -- take only the MAX sequence rules for a position, among the accomplished MRs
397 AND SB.rule_sequence = (SELECT MAX(SB2.rule_sequence)
398 FROM AHL_SB_POSITION_RULES SB2
399 WHERE SB2.relationship_id = SB.relationship_id
400 AND EXISTS (SELECT 'X'
401 FROM AHL_UNIT_EFFECTIVITIES_B UE
402 WHERE UE.mr_header_id = SB2.mr_header_id
403 AND UE.csi_item_instance_id = UC.csi_item_instance_id
404 AND UE.accomplished_date IS NOT NULL))
405 AND EXISTS (SELECT 'X'
406 FROM CSI_II_RELATIONSHIPS
407 WHERE subject_id = c_instance_id -- SB rules are created for non-root positions
408 AND TO_NUMBER(position_reference) = SB.relationship_id
409 AND relationship_type_code = 'COMPONENT-OF'
410 AND TRUNC(NVL(active_end_date, SYSDATE+1)) > TRUNC(SYSDATE))
411 AND TRUNC(NVL(SB.active_start_date, SYSDATE)) <= TRUNC(SYSDATE)
412 AND TRUNC(NVL(SB.active_end_date, SYSDATE+1)) > TRUNC(SYSDATE);
413
414 -- cursor to validate the given SB rule against the given instance
415 CURSOR validate_sb_rule_csr (c_rule_id NUMBER,
416 c_instance_id NUMBER) IS
417 SELECT 'X'
418 FROM AHL_SB_RULE_ITEMS SBI,
419 AHL_ITEM_ASSOCIATIONS_B IAB,
420 CSI_ITEM_INSTANCES CSI
421 WHERE SBI.rule_id = c_rule_id
422 AND IAB.item_association_id = SBI.item_association_id
423 AND CSI.inventory_item_id = IAB.inventory_item_id
424 AND CSI.inv_master_organization_id = IAB.inventory_org_id
425 AND CSI.instance_id = c_instance_id;
426
427 --
428 l_api_version CONSTANT NUMBER := 1.0;
429 l_api_name CONSTANT VARCHAR2(30) := 'Check_Inst_Completeness';
430 l_full_name CONSTANT VARCHAR2(100) := 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name;
431
432 TYPE t_id_tbl IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
433 TYPE t_rule_name_tbl IS TABLE OF VARCHAR2(80) INDEX BY BINARY_INTEGER;
434
435 l_rule_id_tbl t_id_tbl;
436 l_rule_name_tbl t_rule_name_tbl;
437 l_rel_id_tbl t_id_tbl;
438
439 l_uc_header_id NUMBER;
440 l_unit_name VARCHAR2(80);
441 l_msg_count NUMBER;
442 l_dummy VARCHAR2(1);
443 --
444
445 BEGIN
446 IF (l_log_procedure >= l_log_current_level) THEN
447 FND_LOG.string(l_log_procedure, l_full_name || '.begin', 'At the start of the API');
448 END IF;
449
450 -- Standard start of API savepoint
451 SAVEPOINT Check_Inst_Completeness_Pvt;
452
453 -- Standard call to check for call compatibility
454 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version,
455 l_api_name, G_PKG_NAME) THEN
456 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
457 END IF;
458
459 -- Initialize message list if p_init_msg_list is set to TRUE
460 IF FND_API.To_Boolean( p_init_msg_list) THEN
461 FND_MSG_PUB.Initialize;
462 END IF;
463
464 -- Initialize Procedure return status to success
465 x_return_status := FND_API.G_RET_STS_SUCCESS;
466
467 IF (l_log_statement >= l_log_current_level) THEN
468 FND_LOG.string(l_log_statement, l_full_name,
469 ' p_csi_instance_id = '||p_csi_instance_id);
470 END IF;
471
472 -- Get the error message count till this point
473 l_msg_count := FND_MSG_PUB.count_msg;
474
475 -- Check for the instance id
476 OPEN chk_instance_id_csr(p_csi_instance_id);
477 FETCH chk_instance_id_csr INTO l_dummy;
478 IF (chk_instance_id_csr%NOTFOUND) THEN
479 -- invalid instance id
480 IF (l_log_statement >= l_log_current_level) THEN
481 FND_LOG.string(l_log_statement, l_full_name, 'invalid instance id');
482 END IF;
483 -- The item instance INSTANCE is invalid.
484 FND_MESSAGE.Set_Name(G_APP_NAME, 'AHL_FMP_INVALID_ITEM_INSTANCE');
485 FND_MESSAGE.Set_Token('INSTANCE', p_csi_instance_id);
486 FND_MSG_PUB.ADD;
487 END IF;
488 CLOSE chk_instance_id_csr;
489
490 -- Get the immediate unit for the instance
491 l_uc_header_id := AHL_UTIL_UC_PKG.get_sub_uc_header_id(p_csi_instance_id);
492 OPEN get_unit_name_csr(l_uc_header_id);
493 FETCH get_unit_name_csr INTO l_unit_name;
494 IF (get_unit_name_csr%NOTFOUND) THEN
495 -- invalid unit header id
496 IF (l_log_statement >= l_log_current_level) THEN
497 FND_LOG.string(l_log_statement, l_full_name, 'invalid unit header id l_uc_header_id = '||l_uc_header_id);
498 END IF;
499 -- The Unit Configuration header identifier UC_HEADER_ID is invalid.
500 FND_MESSAGE.Set_Name(G_APP_NAME, 'AHL_UC_HEADER_ID_INVALID');
501 FND_MESSAGE.Set_Token('UC_HEADER_ID', l_uc_header_id);
502 FND_MSG_PUB.ADD;
503 END IF;
504 CLOSE get_unit_name_csr;
505
506 -- If any errors occured, then raise them
507 IF (l_msg_count < FND_MSG_PUB.count_msg) THEN
508 IF (l_log_statement >= l_log_current_level) THEN
509 FND_LOG.string(l_log_statement, l_full_name, 'given instance and / or its unit are invalid');
510 END IF;
511 RAISE FND_API.G_EXC_ERROR;
512 END IF;
513
514 IF (l_log_statement >= l_log_current_level) THEN
515 FND_LOG.string(l_log_statement, l_full_name,
516 ' l_uc_header_id = '||l_uc_header_id||', l_unit_name = '||l_unit_name);
517 END IF;
518
519 -- Get the applicable SB rules for the unit and the given instance
520 -- NOTE: This should fetch just one rule. Keeping the BULK COLLECT logic from the
521 -- previous version to avoid a lot of code change.
522 OPEN get_appl_sb_rules_csr(l_uc_header_id, p_csi_instance_id);
523 FETCH get_appl_sb_rules_csr BULK COLLECT INTO l_rule_id_tbl, l_rule_name_tbl, l_rel_id_tbl;
524 CLOSE get_appl_sb_rules_csr;
525
526 IF (l_log_statement >= l_log_current_level) THEN
527 FND_LOG.string(l_log_statement, l_full_name,
528 ' l_rule_id_tbl.COUNT = '||l_rule_id_tbl.COUNT||
529 ', l_rule_name_tbl.COUNT = '||l_rule_name_tbl.COUNT||
530 ', l_rel_id_tbl.COUNT = '||l_rel_id_tbl.COUNT);
531 END IF;
532
533 -- Validate the rules thus fetched above
534 IF (l_rule_id_tbl.COUNT > 0) THEN
535 FOR i IN l_rule_id_tbl.FIRST..l_rule_id_tbl.LAST LOOP
536 OPEN validate_sb_rule_csr(l_rule_id_tbl(i), p_csi_instance_id);
537 FETCH validate_sb_rule_csr INTO l_dummy;
538 IF (validate_sb_rule_csr%NOTFOUND) THEN
539 -- Put this erring rule in the OUT table parameter
540 -- Rule RULE_NAME evaluation failed for the unit UNIT_NAME.
541 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_MC_RULE_FAILED');
542 FND_MESSAGE.Set_Token('RULE_NAME',l_rule_name_tbl(i));
543 FND_MESSAGE.Set_Token('UNIT_NAME',l_unit_name);
544 IF (p_x_erring_rules_tbl.COUNT > 0) THEN
545 p_x_erring_rules_tbl(p_x_erring_rules_tbl.LAST+1) := FND_MESSAGE.Get;
546 ELSE
547 p_x_erring_rules_tbl(0) := FND_MESSAGE.Get;
548 END IF;
549 IF (l_log_statement >= l_log_current_level) THEN
550 FND_LOG.string(l_log_statement, l_full_name, 'rule violated = '||l_rule_name_tbl(i));
551 END IF;
552 END IF;
553 CLOSE validate_sb_rule_csr;
554 END LOOP;
555 END IF;
556
557 IF (l_log_statement >= l_log_current_level) THEN
558 FND_LOG.string(l_log_statement, l_full_name,
559 ' p_x_erring_rules_tbl.COUNT = '||p_x_erring_rules_tbl.COUNT);
560 END IF;
561
562 IF (l_log_procedure >= l_log_current_level) THEN
563 FND_LOG.string(l_log_procedure, l_full_name || '.end', 'At the end of the API');
564 END IF;
565
566 EXCEPTION
567 WHEN FND_API.G_EXC_ERROR THEN
568 ROLLBACK TO Check_Inst_Completeness_Pvt;
569 x_return_status := FND_API.G_RET_STS_ERROR;
570 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
571 p_data => x_msg_data,
572 p_encoded => FND_API.G_FALSE);
573 IF (l_log_exception >= l_log_current_level) THEN
574 FND_LOG.string(l_log_exception, l_full_name, 'Execution Error: ' || x_msg_data);
575 END IF;
576
577 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
578 ROLLBACK TO Check_Inst_Completeness_Pvt;
579 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
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 IF (l_log_unexpected >= l_log_current_level) THEN
584 FND_LOG.string(l_log_unexpected, l_full_name, 'Unexpected Exception: ' || x_msg_data);
585 END IF;
586
587 WHEN OTHERS THEN
588 ROLLBACK TO Check_Inst_Completeness_Pvt;
589 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
590 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
591 FND_MSG_PUB.add_exc_msg(p_pkg_name => G_PKG_NAME,
592 p_procedure_name => l_api_name,
593 p_error_text => SUBSTR(SQLERRM,1,500));
594 END IF;
595 FND_MSG_PUB.count_and_get(p_count => x_msg_count,
596 p_data => x_msg_data,
597 p_encoded => fnd_api.G_FALSE);
598 IF (l_log_unexpected >= l_log_current_level) THEN
599 FND_LOG.string(l_log_unexpected, l_full_name, 'Other Exception: ' || x_msg_data);
600 END IF;
601
602 END Check_Inst_Completeness;
603 ------------------------------------------------------------------------------------
604
605 ------------------------------------------------------------------------------------
606 -- Start of Comments
607 -- Procedure name : Evaluate_Rules_For_Pos
608 -- Type : Public
609 -- Function : Evaluates SB rules for a given MC position and matching unit in the given unit.
610 -- Puts the erring rules in the message stack. Calling APIs need to check the message stack count.
611 -- Pre-reqs :
612 -- Parameters :
613 --
614 -- Evaluate_Rules_For_Pos Parameters:
615 -- p_mc_header_id IN Given MC header id. Required
616 -- p_mc_pos_key IN Given MC position key. Required
617 -- p_inv_item_id IN Given inventory item id. Required
618 -- p_inv_org_id IN Given inventory org id. Required
619 -- p_uc_header_id IN Given unit header id. Required
620 --
621 -- End of Comments
622
623 PROCEDURE Evaluate_Rules_For_Pos (
624 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
625 p_mc_header_id IN NUMBER,
626 p_mc_pos_key IN NUMBER,
627 p_inv_item_id IN NUMBER,
628 p_inv_org_id IN NUMBER,
629 p_uc_header_id IN NUMBER
630 ) IS
631
632 -- cursor to get relationship id for the given MC and position
633 CURSOR get_mc_rel_id_csr (c_mc_header_id NUMBER,
634 c_pos_key NUMBER) IS
635 SELECT relationship_id
636 FROM AHL_MC_RELATIONSHIPS
637 WHERE mc_header_id = c_mc_header_id
638 AND position_key = c_pos_key
639 AND TRUNC(NVL(active_start_date, SYSDATE)) <= TRUNC(SYSDATE)
640 AND TRUNC(NVL(active_end_date, SYSDATE+1)) > TRUNC(SYSDATE);
641
642 -- cursor to get the matching units' details for the given MC and unit
643 CURSOR get_unit_det_csr (c_uc_header_id NUMBER,
644 c_mc_header_id NUMBER) IS
645 SELECT unit_config_header_id,
646 name
647 FROM AHL_UNIT_CONFIG_HEADERS
648 WHERE master_config_id = c_mc_header_id
649 AND TRUNC(NVL(active_start_date, SYSDATE)) <= TRUNC(SYSDATE)
650 AND TRUNC(NVL(active_end_date, SYSDATE+1)) > TRUNC(SYSDATE)
651 AND unit_config_header_id IN (SELECT unit_config_header_id
652 FROM AHL_UNIT_CONFIG_HEADERS
653 START WITH unit_config_header_id = c_uc_header_id
654 CONNECT BY parent_uc_header_id = PRIOR unit_config_header_id);
655
656 -- cursor to get applicable SB rules for the given unit and position
657 CURSOR get_appl_sb_rules_csr (c_uc_header_id NUMBER,
658 c_relationship_id NUMBER) IS
659 SELECT SB.rule_id,
660 SB.rule_name
661 FROM AHL_SB_POSITION_RULES SB,
662 AHL_UNIT_CONFIG_HEADERS UC
663 WHERE UC.unit_config_header_id = c_uc_header_id
664 AND SB.mc_header_id = UC.master_config_id
665 -- take only the rules which have MRs accomplished on the unit
666 AND EXISTS (SELECT 'X'
667 FROM AHL_UNIT_EFFECTIVITIES_B UE
668 WHERE UE.mr_header_id = SB.mr_header_id
669 AND UE.csi_item_instance_id = UC.csi_item_instance_id -- SB MRs are effective on root node
670 AND UE.accomplished_date IS NOT NULL)
671 -- take only the MAX sequence rules for a position, among the accomplished MRs
672 AND SB.rule_sequence = (SELECT MAX(SB2.rule_sequence)
673 FROM AHL_SB_POSITION_RULES SB2
674 WHERE SB2.relationship_id = SB.relationship_id
675 AND EXISTS (SELECT 'X'
676 FROM AHL_UNIT_EFFECTIVITIES_B UE
677 WHERE UE.mr_header_id = SB2.mr_header_id
678 AND UE.csi_item_instance_id = UC.csi_item_instance_id
679 AND UE.accomplished_date IS NOT NULL))
680 AND SB.relationship_id = c_relationship_id
681 AND TRUNC(NVL(SB.active_start_date, SYSDATE)) <= TRUNC(SYSDATE)
682 AND TRUNC(NVL(SB.active_end_date, SYSDATE+1)) > TRUNC(SYSDATE);
683
684 -- cursor to validate the given SB rule against the given item
685 CURSOR validate_sb_rule_csr (c_rule_id NUMBER,
686 c_inv_item_id NUMBER,
687 c_inv_org_id NUMBER) IS
688 SELECT 'X'
689 FROM AHL_SB_RULE_ITEMS SBI,
690 AHL_ITEM_ASSOCIATIONS_B IAB,
691 MTL_SYSTEM_ITEMS_B MTL
692 WHERE SBI.rule_id = c_rule_id
693 AND IAB.item_association_id = SBI.item_association_id
694 AND MTL.inventory_item_id = IAB.inventory_item_id
695 AND IAB.inventory_item_id = c_inv_item_id
696 AND MTL.organization_id = c_inv_org_id;
697
698 --
699 l_api_name CONSTANT VARCHAR2(30) := 'Evaluate_Rules_For_Pos';
700 l_full_name CONSTANT VARCHAR2(100) := 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name;
701
702 TYPE t_rule_id_tbl IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
703 TYPE t_rule_name_tbl IS TABLE OF VARCHAR2(80) INDEX BY BINARY_INTEGER;
704
705 l_rule_id_tbl t_rule_id_tbl;
706 l_rule_name_tbl t_rule_name_tbl;
707
708 l_mc_rel_id NUMBER;
709 l_dummy VARCHAR2(1);
710 l_count NUMBER := 0;
711 --
712
713 BEGIN
714 IF (l_log_procedure >= l_log_current_level) THEN
715 FND_LOG.string(l_log_procedure, l_full_name || '.begin', 'At the start of the API');
716 END IF;
717
718 -- Initialize message list if p_init_msg_list is set to TRUE
719 IF FND_API.To_Boolean(p_init_msg_list) THEN
720 FND_MSG_PUB.Initialize;
721 END IF;
722
723 IF (l_log_statement >= l_log_current_level) THEN
724 FND_LOG.string(l_log_statement, l_full_name,
725 ' p_mc_header_id = '||p_mc_header_id||
726 ', p_mc_pos_key = '||p_mc_pos_key||
727 ', p_inv_item_id = '||p_inv_item_id||
728 ', p_inv_org_id = '||p_inv_org_id||
729 ', p_uc_header_id = '||p_uc_header_id);
730 END IF;
731
732 -- Get the relationship id for the given MC and position
733 OPEN get_mc_rel_id_csr(p_mc_header_id, p_mc_pos_key);
734 FETCH get_mc_rel_id_csr INTO l_mc_rel_id;
735 IF (get_mc_rel_id_csr%NOTFOUND) THEN
736 -- invalid MC and position
737 IF (l_log_statement >= l_log_current_level) THEN
738 FND_LOG.string(l_log_statement, l_full_name, 'invalid MC and position');
739 END IF;
740 FND_MESSAGE.Set_Name(G_APP_NAME, 'AHL_MC_NODE_NOT_FOUND'); -- Master Configuration node not found.
741 FND_MSG_PUB.ADD;
742 END IF;
743 CLOSE get_mc_rel_id_csr;
744
745 -- Get the matching units
746 FOR l_get_unit_det_csr_rec IN get_unit_det_csr(p_uc_header_id, p_mc_header_id) LOOP
747 -- Get the applicable SB rules for this unit and relationship
748 -- NOTE: This should fetch just one rule. Keeping the BULK COLLECT logic from the
749 -- previous version to avoid a lot of code change.
750 OPEN get_appl_sb_rules_csr(l_get_unit_det_csr_rec.unit_config_header_id, l_mc_rel_id);
751 FETCH get_appl_sb_rules_csr BULK COLLECT INTO l_rule_id_tbl, l_rule_name_tbl;
752 CLOSE get_appl_sb_rules_csr;
753
754 -- Evaluate the rules thus fetched, against the given item
755 IF (l_rule_id_tbl.COUNT > 0) THEN
756 FOR i IN l_rule_id_tbl.FIRST..l_rule_id_tbl.LAST LOOP
757 OPEN validate_sb_rule_csr(l_rule_id_tbl(i), p_inv_item_id, p_inv_org_id);
758 FETCH validate_sb_rule_csr INTO l_dummy;
759 IF (validate_sb_rule_csr%NOTFOUND) THEN
760 -- Put this erring rule in the message stack
761 -- Rule RULE_NAME evaluation failed for the unit UNIT_NAME.
762 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_MC_RULE_FAILED');
763 FND_MESSAGE.Set_Token('RULE_NAME',l_rule_name_tbl(i));
764 FND_MESSAGE.Set_Token('UNIT_NAME',l_get_unit_det_csr_rec.name);
765 FND_MSG_PUB.ADD;
766 IF (l_log_statement >= l_log_current_level) THEN
767 FND_LOG.string(l_log_statement, l_full_name, 'rule violated = '||l_rule_name_tbl(i));
768 END IF;
769 END IF;
770 CLOSE validate_sb_rule_csr;
771 END LOOP;
772 END IF;
773
774 l_count := l_count + 1;
775 END LOOP;
776 IF (l_count = 0) THEN
777 -- no matching unit found
778 IF (l_log_statement >= l_log_current_level) THEN
779 FND_LOG.string(l_log_statement, l_full_name, 'no matching unit found');
780 END IF;
781 FND_MESSAGE.Set_Name(G_APP_NAME, 'AHL_PC_UNIT_NOT_FOUND'); -- No such unit found in Unit Configuration.
782 FND_MSG_PUB.ADD;
783 END IF;
784
785 IF (l_log_procedure >= l_log_current_level) THEN
786 FND_LOG.string(l_log_procedure, l_full_name || '.end', 'At the end of the API');
787 END IF;
788
789 END Evaluate_Rules_For_Pos;
790 ------------------------------------------------------------------------------------
791
792 ------------------------------------------------------------------------------------
793 -- Start of Comments
794 -- Procedure name : Copy_Rules_For_MR
795 -- Type : Public
796 -- Function : Copies the SB rules from a source SB MR (original) to destination SB MR (revised).
797 -- NOTE: Translated string for 'Copy' will be prefixed to the rule name.
798 -- Pre-reqs :
799 -- Parameters :
800 --
801 -- Copy_Rules_For_MR Parameters:
802 -- p_src_mr_header_id IN Given source MR header id. Required
803 -- p_dst_mr_header_id IN Given destination MR header id. Required
804 --
805 -- End of Comments
806
807 PROCEDURE Copy_Rules_For_MR (
808 p_api_version IN NUMBER := 1.0,
809 p_commit IN VARCHAR2 := FND_API.G_FALSE,
810 p_src_mr_header_id IN NUMBER,
811 p_dst_mr_header_id IN NUMBER,
812 x_return_status OUT NOCOPY VARCHAR2,
813 x_msg_count OUT NOCOPY NUMBER,
814 x_msg_data OUT NOCOPY VARCHAR2
815 ) IS
816
817 -- cursor to check the MR header id
818 CURSOR chk_mr_header_id_csr (c_mr_header_id NUMBER) IS
819 SELECT 'X'
820 FROM AHL_MR_HEADERS_B
821 WHERE mr_header_id = c_mr_header_id
822 AND program_type_code = 'SERV_BLTN'
823 AND mr_status_code <> 'TERMINATED';
824
825 -- curosr to get the SB rules' details for a given MR header id
826 CURSOR get_rule_det_csr (c_mr_header_id NUMBER) IS
827 SELECT *
828 FROM AHL_SB_POSITION_RULES
829 WHERE mr_header_id = c_mr_header_id;
830
831 -- curosr to get the SB rule items' details for a given rule id
832 CURSOR get_rule_items_det_csr (c_rule_id NUMBER) IS
833 SELECT *
834 FROM AHL_SB_RULE_ITEMS
835 WHERE rule_id = c_rule_id;
836
837 --
838 l_api_version CONSTANT NUMBER := 1.0;
839 l_api_name CONSTANT VARCHAR2(30) := 'Copy_Rules_For_MR';
840 l_full_name CONSTANT VARCHAR2(100) := 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name;
841
842 l_msg_count NUMBER;
843 l_rule_id NUMBER;
844 l_rule_association_id NUMBER;
845 l_copy VARCHAR2(30);
846 l_dummy VARCHAR2(1);
847 --
848
849 BEGIN
850 IF (l_log_procedure >= l_log_current_level) THEN
851 FND_LOG.string(l_log_procedure, l_full_name || '.begin', 'At the start of the API');
852 END IF;
853
854 -- Standard start of API savepoint
855 SAVEPOINT Copy_Rules_For_MR_Pvt;
856
857 -- Standard call to check for call compatibility
858 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version,
859 l_api_name, G_PKG_NAME) THEN
860 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
861 END IF;
862
863 -- Initialize Procedure return status to success
864 x_return_status := FND_API.G_RET_STS_SUCCESS;
865
866 IF (l_log_statement >= l_log_current_level) THEN
867 FND_LOG.string(l_log_statement, l_full_name,
868 ' p_src_mr_header_id = '||p_src_mr_header_id||
869 ', p_dst_mr_header_id = '||p_dst_mr_header_id);
870 END IF;
871
872 -- Get the error message count till this point
873 l_msg_count := FND_MSG_PUB.count_msg;
874
875 -- Check for the given MR ids
876 OPEN chk_mr_header_id_csr(p_src_mr_header_id);
877 FETCH chk_mr_header_id_csr INTO l_dummy;
878 IF (chk_mr_header_id_csr%NOTFOUND) THEN
879 -- invalid source MR header id
880 IF (l_log_statement >= l_log_current_level) THEN
881 FND_LOG.string(l_log_statement, l_full_name, 'invalid source MR header id');
882 END IF;
883 FND_MESSAGE.Set_Name(G_APP_NAME, 'AHL_UMP_PUE_MR_NOTFOUND'); -- Maintenance requirement ID (MR_ID) not found.
884 FND_MESSAGE.Set_Token('MR_ID', p_src_mr_header_id);
885 FND_MSG_PUB.ADD;
886 END IF;
887 CLOSE chk_mr_header_id_csr;
888 OPEN chk_mr_header_id_csr(p_dst_mr_header_id);
889 FETCH chk_mr_header_id_csr INTO l_dummy;
890 IF (chk_mr_header_id_csr%NOTFOUND) THEN
891 -- invalid destination MR header id
892 IF (l_log_statement >= l_log_current_level) THEN
893 FND_LOG.string(l_log_statement, l_full_name, 'invalid destination MR header id');
894 END IF;
895 FND_MESSAGE.Set_Name(G_APP_NAME, 'AHL_UMP_PUE_MR_NOTFOUND'); -- Maintenance requirement ID (MR_ID) not found.
896 FND_MESSAGE.Set_Token('MR_ID', p_dst_mr_header_id);
897 FND_MSG_PUB.ADD;
898 END IF;
899 CLOSE chk_mr_header_id_csr;
900
901 -- If any errors occured, then raise them
902 IF (l_msg_count < FND_MSG_PUB.count_msg) THEN
903 RAISE FND_API.G_EXC_ERROR;
904 END IF;
905
906 -- Copy the SB rules from source MR to destination MR
907 -- 1) get the translated message AHL_COM_COPY
908 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_COM_COPY'); -- Copy
909 l_copy := FND_MESSAGE.Get;
910 IF (l_log_statement >= l_log_current_level) THEN
911 FND_LOG.string(l_log_statement, l_full_name, 'l_copy = '||l_copy);
912 END IF;
913
914 -- 2) copy the SB rules and their items
915 FOR l_get_rule_det_csr_rec IN get_rule_det_csr(p_src_mr_header_id) LOOP
916 -- get the next value from the sequence
917 l_rule_id := AHL_SB_POSITION_RULES_S.NEXTVAL;
918
919 -- insert the rule
920 INSERT INTO AHL_SB_POSITION_RULES(
921 rule_id, object_version_number,
922 last_update_date, last_updated_by, creation_date, created_by, last_update_login,
923 rule_name, mr_header_id,
924 mc_header_id, relationship_id,
925 rule_sequence, active_start_date, active_end_date)
926 VALUES(
927 l_rule_id, 1,
928 SYSDATE, FND_GLOBAL.User_Id, SYSDATE, FND_GLOBAL.User_Id, FND_GLOBAL.Login_Id,
929 SUBSTR(l_copy||'_'||l_get_rule_det_csr_rec.rule_name, 0, 80), p_dst_mr_header_id,
930 l_get_rule_det_csr_rec.mc_header_id, l_get_rule_det_csr_rec.relationship_id,
931 l_get_rule_det_csr_rec.rule_sequence, SYSDATE, NULL);
932
933 -- insert the rule's items
934 FOR l_get_rule_items_det_csr_rec IN get_rule_items_det_csr(l_get_rule_det_csr_rec.rule_id) LOOP
935 -- get the next value from the sequence
936 l_rule_association_id := AHL_SB_RULE_ITEMS_S.NEXTVAL;
937
938 -- insert the item
939 INSERT INTO AHL_SB_RULE_ITEMS(
940 rule_association_id, object_version_number,
941 last_update_date, last_updated_by, creation_date, created_by, last_update_login,
942 rule_id, item_group_id,
943 item_association_id, sub_config_item)
944 VALUES(
945 l_rule_association_id, 1,
946 SYSDATE, FND_GLOBAL.User_Id, SYSDATE, FND_GLOBAL.User_Id, FND_GLOBAL.Login_Id,
947 l_rule_id, l_get_rule_items_det_csr_rec.item_group_id,
948 l_get_rule_items_det_csr_rec.item_association_id, l_get_rule_items_det_csr_rec.sub_config_item);
949 END LOOP;
950
951 IF (l_log_statement >= l_log_current_level) THEN
952 FND_LOG.string(l_log_statement, l_full_name, 'copied rule = '||l_get_rule_det_csr_rec.rule_name);
953 END IF;
954 END LOOP;
955
956 -- Standard check of p_commit
957 IF FND_API.TO_BOOLEAN(p_commit) THEN
958 COMMIT WORK;
959 END IF;
960
961 IF (l_log_procedure >= l_log_current_level) THEN
962 FND_LOG.string(l_log_procedure, l_full_name || '.end', 'At the end of the API');
963 END IF;
964
965 EXCEPTION
966 WHEN FND_API.G_EXC_ERROR THEN
967 ROLLBACK TO Copy_Rules_For_MR_Pvt;
968 x_return_status := FND_API.G_RET_STS_ERROR;
969 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
970 p_data => x_msg_data,
971 p_encoded => FND_API.G_FALSE);
972 IF (l_log_exception >= l_log_current_level) THEN
973 FND_LOG.string(l_log_exception, l_full_name, 'Execution Error: ' || x_msg_data);
974 END IF;
975
976 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
977 ROLLBACK TO Copy_Rules_For_MR_Pvt;
978 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
979 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
980 p_data => x_msg_data,
981 p_encoded => FND_API.G_FALSE);
982 IF (l_log_unexpected >= l_log_current_level) THEN
983 FND_LOG.string(l_log_unexpected, l_full_name, 'Unexpected Exception: ' || x_msg_data);
984 END IF;
985
986 WHEN OTHERS THEN
987 ROLLBACK TO Copy_Rules_For_MR_Pvt;
988 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
989 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
990 FND_MSG_PUB.add_exc_msg(p_pkg_name => G_PKG_NAME,
991 p_procedure_name => l_api_name,
992 p_error_text => SUBSTR(SQLERRM,1,500));
993 END IF;
994 FND_MSG_PUB.count_and_get(p_count => x_msg_count,
995 p_data => x_msg_data,
996 p_encoded => fnd_api.G_FALSE);
997 IF (l_log_unexpected >= l_log_current_level) THEN
998 FND_LOG.string(l_log_unexpected, l_full_name, 'Other Exception: ' || x_msg_data);
999 END IF;
1000
1001 END Copy_Rules_For_MR;
1002 ------------------------------------------------------------------------------------
1003
1004 ------------------------------------------------------------------------------------
1005 -- Start of Comments
1006 -- Procedure name : Update_Rules_For_MR
1007 -- Type : Public
1008 -- Function : Updates the SB rules for a given MR. To be called during MR completion, to
1009 -- update any non-effective rules of the edited DRAFT version.
1010 -- Pre-reqs :
1011 -- Parameters :
1012 --
1013 -- Update_Rules_For_MR Parameters:
1014 -- p_mr_header_id IN Given MR header id. Required
1015 --
1016 -- End of Comments
1017
1018 PROCEDURE Update_Rules_For_MR (
1019 p_api_version IN NUMBER := 1.0,
1020 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1021 p_mr_header_id IN NUMBER,
1022 x_return_status OUT NOCOPY VARCHAR2,
1023 x_msg_count OUT NOCOPY NUMBER,
1024 x_msg_data OUT NOCOPY VARCHAR2
1025 ) IS
1026
1027 -- Modified by SURRKUMA :: 12896263 :: 16-FEB-2012
1028 -- cursor to check the given MR header id
1029 CURSOR chk_mr_header_id_csr (c_mr_header_id NUMBER) IS
1030 SELECT 'X'
1031 FROM AHL_MR_HEADERS_B
1032 WHERE mr_header_id = c_mr_header_id
1033 AND program_type_code = 'SERV_BLTN';
1034
1035 -- cursor to get the MC header id for a unit
1036 CURSOR get_mc_header_id_csr (c_uc_header_id NUMBER) IS
1037 SELECT master_config_id
1038 FROM AHL_UNIT_CONFIG_HEADERS
1039 WHERE unit_config_header_id = c_uc_header_id;
1040
1041 -- cursor to get the MC header ids for the SB rules defined for a given MR
1042 CURSOR get_sb_mc_header_id_csr (c_mr_header_id NUMBER) IS
1043 SELECT DISTINCT mc_header_id
1044 FROM AHL_SB_POSITION_RULES
1045 WHERE mr_header_id = c_mr_header_id;
1046
1047 -- cursor to get the SB rule ids for a given MC and MR
1048 CURSOR get_rule_id_csr (c_mc_header_id NUMBER,
1049 c_mr_header_id NUMBER) IS
1050 SELECT rule_id
1051 FROM AHL_SB_POSITION_RULES
1052 WHERE mc_header_id = c_mc_header_id
1053 AND mr_header_id = c_mr_header_id;
1054
1055 --
1056 l_api_version CONSTANT NUMBER := 1.0;
1057 l_api_name CONSTANT VARCHAR2(30) := 'Update_Rules_For_MR';
1058 l_full_name CONSTANT VARCHAR2(100) := 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name;
1059
1060 TYPE t_mc_hdr_id_tbl IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
1061
1062 l_eff_mc_hdr_id_tbl t_mc_hdr_id_tbl;
1063 l_sb_mc_hdr_id_tbl t_mc_hdr_id_tbl;
1064
1065 l_mr_item_inst_tbl AHL_FMP_PVT.MR_Item_Instance_Tbl_Type;
1066 l_mc_hdr_id NUMBER;
1067 l_dummy VARCHAR2(1);
1068 --
1069
1070 BEGIN
1071 IF (l_log_procedure >= l_log_current_level) THEN
1072 FND_LOG.string(l_log_procedure, l_full_name || '.begin', 'At the start of the API');
1073 END IF;
1074
1075 -- Standard start of API savepoint
1076 SAVEPOINT Update_Rules_For_MR_Pvt;
1077
1078 -- Standard call to check for call compatibility
1079 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version,
1080 l_api_name, G_PKG_NAME) THEN
1081 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1082 END IF;
1083
1084 -- Initialize Procedure return status to success
1085 x_return_status := FND_API.G_RET_STS_SUCCESS;
1086
1087 IF (l_log_statement >= l_log_current_level) THEN
1088 FND_LOG.string(l_log_statement, l_full_name,
1089 ' p_mr_header_id = '||p_mr_header_id);
1090 END IF;
1091
1092 -- Check for the given MR id
1093 OPEN chk_mr_header_id_csr(p_mr_header_id);
1094 FETCH chk_mr_header_id_csr INTO l_dummy;
1095 IF (chk_mr_header_id_csr%NOTFOUND) THEN
1096 -- invalid MR header id
1097 CLOSE chk_mr_header_id_csr;
1098 IF (l_log_statement >= l_log_current_level) THEN
1099 FND_LOG.string(l_log_statement, l_full_name, 'invalid MR header id');
1100 END IF;
1101 FND_MESSAGE.Set_Name(G_APP_NAME, 'AHL_UMP_PUE_MR_NOTFOUND'); -- Maintenance requirement ID (MR_ID) not found.
1102 FND_MESSAGE.Set_Token('MR_ID', p_mr_header_id);
1103 FND_MSG_PUB.ADD;
1104 RAISE FND_API.G_EXC_ERROR;
1105 END IF;
1106 CLOSE chk_mr_header_id_csr;
1107
1108 -- Get the MC header ids for the SB rules defined for the given MR
1109 OPEN get_sb_mc_header_id_csr(p_mr_header_id);
1110 FETCH get_sb_mc_header_id_csr BULK COLLECT INTO l_sb_mc_hdr_id_tbl;
1111 CLOSE get_sb_mc_header_id_csr;
1112 IF (l_log_statement >= l_log_current_level) THEN
1113 FND_LOG.string(l_log_statement, l_full_name, 'l_sb_mc_hdr_id_tbl.COUNT = '||l_sb_mc_hdr_id_tbl.COUNT);
1114 END IF;
1115
1116 IF (l_sb_mc_hdr_id_tbl.COUNT > 0) THEN
1117 -- 1) Get the MR affected items
1118 -- NOTE: leave the following parameters to their default values give below:
1119 -- p_mr_effectivity_id := NULL; need to look into all the effectivities of the MR
1120 -- p_top_node_flag := 'N'; no need to get the top instance
1121 -- p_unique_inst_flag := 'N'; no need to get DISTINCT instances
1122 -- p_sort_flag := 'N'; no need to get a sorted list of instances
1123 AHL_FMP_PVT.Get_MR_Affected_Items(
1124 p_api_version => 1.0,
1125 p_commit => p_commit,
1126 p_mr_header_id => p_mr_header_id,
1127 x_mr_item_inst_tbl => l_mr_item_inst_tbl,
1128 x_return_status => x_return_status,
1129 x_msg_count => x_msg_count,
1130 x_msg_data => x_msg_data
1131 );
1132
1133 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1134 IF (l_log_statement >= l_log_current_level) THEN
1135 FND_LOG.string(l_log_statement, l_full_name, 'AHL_FMP_PVT.Get_MR_Affected_Items call errored out');
1136 END IF;
1137 RAISE FND_API.G_EXC_ERROR;
1138 END IF;
1139
1140 IF (l_log_statement >= l_log_current_level) THEN
1141 FND_LOG.string(l_log_statement, l_full_name, 'l_mr_item_inst_tbl.COUNT = '||l_mr_item_inst_tbl.COUNT);
1142 END IF;
1143
1144 IF (l_mr_item_inst_tbl.COUNT > 0) THEN
1145 -- 2) Get all the effective MCs
1146 FOR i IN l_mr_item_inst_tbl.FIRST..l_mr_item_inst_tbl.LAST LOOP
1147 OPEN get_mc_header_id_csr(l_mr_item_inst_tbl(i).uc_header_id);
1148 FETCH get_mc_header_id_csr INTO l_mc_hdr_id;
1149 IF (get_mc_header_id_csr%FOUND) THEN
1150 -- put this MC header id in the associative array for effective MCs
1151 l_eff_mc_hdr_id_tbl(l_mc_hdr_id) := l_mc_hdr_id;
1152 IF (l_log_statement >= l_log_current_level) THEN
1153 FND_LOG.string(l_log_statement, l_full_name, 'effective MC header id put = '||l_mc_hdr_id);
1154 END IF;
1155 END IF;
1156 CLOSE get_mc_header_id_csr;
1157 END LOOP; -- LOOP for l_mr_item_inst_tbl
1158
1159 -- 3) For all the MCs present in l_sb_mc_hdr_id_tbl but not in l_eff_mc_hdr_id_tbl,
1160 -- delete the SB rules defined for the given MR
1161 FOR i IN l_sb_mc_hdr_id_tbl.FIRST..l_sb_mc_hdr_id_tbl.LAST LOOP
1162 IF (NOT l_eff_mc_hdr_id_tbl.EXISTS(l_sb_mc_hdr_id_tbl(i))) THEN
1163 -- get the SB rule ids defined for this MC and given MR
1164 IF (l_log_statement >= l_log_current_level) THEN
1165 FND_LOG.string(l_log_statement, l_full_name, 'erring SB MC found = '||l_sb_mc_hdr_id_tbl(i));
1166 END IF;
1167 FOR l_get_rule_id_csr_rec IN get_rule_id_csr(l_sb_mc_hdr_id_tbl(i), p_mr_header_id) LOOP
1168 -- delete the SB rule items
1169 DELETE FROM AHL_SB_RULE_ITEMS WHERE rule_id = l_get_rule_id_csr_rec.rule_id;
1170
1171 -- delete the SB rule itself
1172 DELETE FROM AHL_SB_POSITION_RULES WHERE rule_id = l_get_rule_id_csr_rec.rule_id;
1173
1174 IF (l_log_statement >= l_log_current_level) THEN
1175 FND_LOG.string(l_log_statement, l_full_name, 'rule deleted = '||l_get_rule_id_csr_rec.rule_id);
1176 END IF;
1177 END LOOP;
1178 END IF;
1179 END LOOP; -- LOOP for l_sb_mc_hdr_id_tbl
1180 END IF; -- l_mr_item_inst_tbl COUNT check
1181 END IF; -- l_sb_mc_hdr_id_tbl COUNT check
1182
1183 -- Standard check of p_commit
1184 IF FND_API.TO_BOOLEAN(p_commit) THEN
1185 COMMIT WORK;
1186 END IF;
1187
1188 IF (l_log_procedure >= l_log_current_level) THEN
1189 FND_LOG.string(l_log_procedure, l_full_name || '.end', 'At the end of the API');
1190 END IF;
1191
1192 EXCEPTION
1193 WHEN FND_API.G_EXC_ERROR THEN
1194 ROLLBACK TO Update_Rules_For_MR_Pvt;
1195 x_return_status := FND_API.G_RET_STS_ERROR;
1196 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1197 p_data => x_msg_data,
1198 p_encoded => FND_API.G_FALSE);
1199 IF (l_log_exception >= l_log_current_level) THEN
1200 FND_LOG.string(l_log_exception, l_full_name, 'Execution Error: ' || x_msg_data);
1201 END IF;
1202
1203 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1204 ROLLBACK TO Update_Rules_For_MR_Pvt;
1205 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1206 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1207 p_data => x_msg_data,
1208 p_encoded => FND_API.G_FALSE);
1209 IF (l_log_unexpected >= l_log_current_level) THEN
1210 FND_LOG.string(l_log_unexpected, l_full_name, 'Unexpected Exception: ' || x_msg_data);
1211 END IF;
1212
1213 WHEN OTHERS THEN
1214 ROLLBACK TO Update_Rules_For_MR_Pvt;
1215 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1216 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1217 FND_MSG_PUB.add_exc_msg(p_pkg_name => G_PKG_NAME,
1218 p_procedure_name => l_api_name,
1219 p_error_text => SUBSTR(SQLERRM,1,500));
1220 END IF;
1221 FND_MSG_PUB.count_and_get(p_count => x_msg_count,
1222 p_data => x_msg_data,
1223 p_encoded => fnd_api.G_FALSE);
1224 IF (l_log_unexpected >= l_log_current_level) THEN
1225 FND_LOG.string(l_log_unexpected, l_full_name, 'Other Exception: ' || x_msg_data);
1226 END IF;
1227
1228 END Update_Rules_For_MR;
1229 ------------------------------------------------------------------------------------
1230
1231 ------------------------------------------------------------------------------------
1232 -- Start of Comments
1233 -- Procedure name : Delete_Rules_For_MR
1234 -- Type : Public
1235 -- Function : Deletes the SB rules for a given MR. To be called during MR deletion, to
1236 -- delete all the rules for the version getting deleted.
1237 -- Pre-reqs :
1238 -- Parameters :
1239 --
1240 -- Update_Rules_For_MR Parameters:
1241 -- p_mr_header_id IN Given MR header id. Required
1242 --
1243 -- End of Comments
1244
1245 PROCEDURE Delete_Rules_For_MR (
1246 p_api_version IN NUMBER := 1.0,
1247 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1248 p_mr_header_id IN NUMBER,
1249 x_return_status OUT NOCOPY VARCHAR2,
1250 x_msg_count OUT NOCOPY NUMBER,
1251 x_msg_data OUT NOCOPY VARCHAR2
1252 ) IS
1253
1254 -- cursor to check the given MR header id
1255 CURSOR chk_mr_header_id_csr (c_mr_header_id NUMBER) IS
1256 SELECT 'X'
1257 FROM AHL_MR_HEADERS_B
1258 WHERE mr_header_id = c_mr_header_id
1259 AND program_type_code = 'SERV_BLTN'
1260 AND mr_status_code = 'DRAFT';
1261
1262 -- cursor to get the SB rule ids for a given MR
1263 CURSOR get_rule_id_csr (c_mr_header_id NUMBER) IS
1264 SELECT rule_id
1265 FROM AHL_SB_POSITION_RULES
1266 WHERE mr_header_id = c_mr_header_id;
1267
1268 --
1269 l_api_version CONSTANT NUMBER := 1.0;
1270 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Rules_For_MR';
1271 l_full_name CONSTANT VARCHAR2(100) := 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name;
1272
1273 TYPE t_rule_id_tbl IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
1274
1275 l_rule_id_tbl t_rule_id_tbl;
1276 l_dummy VARCHAR2(1);
1277 --
1278
1279 BEGIN
1280 IF (l_log_procedure >= l_log_current_level) THEN
1281 FND_LOG.string(l_log_procedure, l_full_name || '.begin', 'At the start of the API');
1282 END IF;
1283
1284 -- Standard start of API savepoint
1285 SAVEPOINT Delete_Rules_For_MR_Pvt;
1286
1287 -- Standard call to check for call compatibility
1288 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version,
1289 l_api_name, G_PKG_NAME) THEN
1290 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1291 END IF;
1292
1293 -- Initialize Procedure return status to success
1294 x_return_status := FND_API.G_RET_STS_SUCCESS;
1295
1296 IF (l_log_statement >= l_log_current_level) THEN
1297 FND_LOG.string(l_log_statement, l_full_name,
1298 ' p_mr_header_id = '||p_mr_header_id);
1299 END IF;
1300
1301 -- Check for the given MR id
1302 OPEN chk_mr_header_id_csr(p_mr_header_id);
1303 FETCH chk_mr_header_id_csr INTO l_dummy;
1304 IF (chk_mr_header_id_csr%NOTFOUND) THEN
1305 -- invalid MR header id
1306 CLOSE chk_mr_header_id_csr;
1307 IF (l_log_statement >= l_log_current_level) THEN
1308 FND_LOG.string(l_log_statement, l_full_name, 'invalid MR header id');
1309 END IF;
1310 FND_MESSAGE.Set_Name(G_APP_NAME, 'AHL_UMP_PUE_MR_NOTFOUND'); -- Maintenance requirement ID (MR_ID) not found.
1311 FND_MESSAGE.Set_Token('MR_ID', p_mr_header_id);
1312 FND_MSG_PUB.ADD;
1313 RAISE FND_API.G_EXC_ERROR;
1314 END IF;
1315 CLOSE chk_mr_header_id_csr;
1316
1317 -- Get the SB rules defined for the given MR
1318 OPEN get_rule_id_csr(p_mr_header_id);
1319 FETCH get_rule_id_csr BULK COLLECT INTO l_rule_id_tbl;
1320 CLOSE get_rule_id_csr;
1321 IF (l_log_statement >= l_log_current_level) THEN
1322 FND_LOG.string(l_log_statement, l_full_name, 'l_rule_id_tbl.COUNT = '||l_rule_id_tbl.COUNT);
1323 END IF;
1324
1325 IF (l_rule_id_tbl.COUNT > 0) THEN
1326 FOR i IN l_rule_id_tbl.FIRST..l_rule_id_tbl.LAST LOOP
1327 -- 1) Delete the SB rule items
1328 DELETE FROM AHL_SB_RULE_ITEMS WHERE rule_id = l_rule_id_tbl(i);
1329
1330 -- 2) Delete the SB rule itself
1331 DELETE FROM AHL_SB_POSITION_RULES WHERE rule_id = l_rule_id_tbl(i);
1332 END LOOP; -- LOOP for l_rule_id_tbl
1333 END IF; -- l_rule_id_tbl COUNT check
1334
1335 -- Standard check of p_commit
1336 IF FND_API.TO_BOOLEAN(p_commit) THEN
1337 COMMIT WORK;
1338 END IF;
1339
1340 IF (l_log_procedure >= l_log_current_level) THEN
1341 FND_LOG.string(l_log_procedure, l_full_name || '.end', 'At the end of the API');
1342 END IF;
1343
1344 EXCEPTION
1345 WHEN FND_API.G_EXC_ERROR THEN
1346 ROLLBACK TO Delete_Rules_For_MR_Pvt;
1347 x_return_status := FND_API.G_RET_STS_ERROR;
1348 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1349 p_data => x_msg_data,
1350 p_encoded => FND_API.G_FALSE);
1351 IF (l_log_exception >= l_log_current_level) THEN
1352 FND_LOG.string(l_log_exception, l_full_name, 'Execution Error: ' || x_msg_data);
1353 END IF;
1354
1355 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1356 ROLLBACK TO Delete_Rules_For_MR_Pvt;
1357 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1358 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1359 p_data => x_msg_data,
1360 p_encoded => FND_API.G_FALSE);
1361 IF (l_log_unexpected >= l_log_current_level) THEN
1362 FND_LOG.string(l_log_unexpected, l_full_name, 'Unexpected Exception: ' || x_msg_data);
1363 END IF;
1364
1365 WHEN OTHERS THEN
1366 ROLLBACK TO Delete_Rules_For_MR_Pvt;
1367 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1368 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1369 FND_MSG_PUB.add_exc_msg(p_pkg_name => G_PKG_NAME,
1370 p_procedure_name => l_api_name,
1371 p_error_text => SUBSTR(SQLERRM,1,500));
1372 END IF;
1373 FND_MSG_PUB.count_and_get(p_count => x_msg_count,
1374 p_data => x_msg_data,
1375 p_encoded => fnd_api.G_FALSE);
1376 IF (l_log_unexpected >= l_log_current_level) THEN
1377 FND_LOG.string(l_log_unexpected, l_full_name, 'Other Exception: ' || x_msg_data);
1378 END IF;
1379
1380 END Delete_Rules_For_MR;
1381 ------------------------------------------------------------------------------------
1382
1383 ------------------------------------------------------------------------------------
1384 -- Start of Comments
1385 -- Procedure name : Update_Rules_For_IG
1386 -- Type : Public
1387 -- Function : Updates the SB rules for a given Item Group. To be called during item group completion, to
1388 -- update SB rules like this:
1389 -- 1) Fork: Deletes the rule items for the old item group and the affected positions.
1390 -- 2) Merge: Deletes the rule items for the removed items in the item group.
1391 -- If there are no items in the SB rule after the above, then deletes the rule too.
1392 -- Pre-reqs :
1393 -- Parameters :
1394 --
1395 -- Update_Rules_For_IG Parameters:
1396 -- p_frk_or_mrg_flg IN Fork or merge indicator ('F' / 'M'). Required
1397 -- p_item_group_id IN Given item group id. Required
1398 -- p_src_item_group_id IN Source item group id for the given item group. Required
1399 --
1400 -- End of Comments
1401
1402 PROCEDURE Update_Rules_For_IG (
1403 p_frk_or_mrg_flg IN VARCHAR2,
1404 p_item_group_id IN NUMBER,
1405 p_src_item_group_id IN NUMBER
1406 ) IS
1407
1408 -- cursor to check whether SB rule update is required
1409 CURSOR chk_rule_upd_required (c_src_item_group_id NUMBER) IS
1410 SELECT 'X'
1411 FROM AHL_SB_RULE_ITEMS
1412 WHERE item_group_id = c_src_item_group_id;
1413
1414 -- cursor to check the given item group id
1415 CURSOR chk_item_group_id_csr (c_item_group_id NUMBER) IS
1416 SELECT 'X'
1417 FROM AHL_ITEM_GROUPS_B
1418 WHERE item_group_id = c_item_group_id;
1419
1420 -- curosr to get the SB rules defined for the positions affected by fork
1421 CURSOR get_rule_id_for_fork_csr (c_src_item_group_id NUMBER) IS
1422 SELECT DISTINCT SBR.rule_id
1423 FROM AHL_SB_POSITION_RULES SBR,
1424 AHL_SB_RULE_ITEMS SBI
1425 WHERE SBR.rule_id = SBI.rule_id
1426 AND SBI.item_group_id = c_src_item_group_id
1427 AND EXISTS (SELECT 'X'
1428 FROM AHL_MC_RELATIONSHIPS MCR
1429 WHERE MCR.relationship_id = SBR.relationship_id
1430 AND MCR.item_group_id = c_src_item_group_id
1431 AND MCR.temp_item_group_id IS NOT NULL);
1432
1433 -- curosr to get all the SB rules
1434 CURSOR get_rule_id_for_merge_csr IS
1435 SELECT rule_id
1436 FROM AHL_SB_POSITION_RULES
1437 WHERE TRUNC(NVL(active_start_date, SYSDATE)) <= TRUNC(SYSDATE)
1438 AND TRUNC(NVL(active_end_date, SYSDATE+1)) > TRUNC(SYSDATE);
1439
1440 -- cursor to get the item associations' details for the given item group
1441 CURSOR get_item_asso_det_csr (c_item_group_id NUMBER) IS
1442 SELECT item_association_id,
1443 TO_CHAR(inventory_item_id)||'-'||revision||'-'||TO_CHAR(inventory_org_id) item_pattern
1444 FROM AHL_ITEM_ASSOCIATIONS_B
1445 WHERE item_group_id = c_item_group_id;
1446
1447 -- cursor to check if the given SB rule is fit to delete, i.e. it has no items
1448 CURSOR chk_rule_del_OK_csr (c_rule_id NUMBER) IS
1449 SELECT 'X'
1450 FROM AHL_SB_RULE_ITEMS
1451 WHERE rule_id = c_rule_id;
1452
1453 -- cursor to check if SB rule item exists for a given item association
1454 CURSOR chk_rule_exists_csr (c_item_asso_id NUMBER) IS
1455 SELECT 'X'
1456 FROM AHL_SB_RULE_ITEMS
1457 WHERE item_association_id = c_item_asso_id;
1458
1459 --
1460 l_api_name CONSTANT VARCHAR2(30) := 'Update_Rules_For_IG';
1461 l_full_name CONSTANT VARCHAR2(100) := 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name;
1462
1463 TYPE t_id_tbl IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
1464 TYPE t_pattern_tbl IS TABLE OF VARCHAR2(200) INDEX BY BINARY_INTEGER;
1465 TYPE t_asso_id_tbl IS TABLE OF NUMBER INDEX BY VARCHAR2(200);
1466 TYPE t_asso_ptrn_tbl IS TABLE OF VARCHAR2(200) INDEX BY VARCHAR2(200);
1467
1468 l_rule_id_for_fork_tbl t_id_tbl;
1469 l_rule_id_for_merge_tbl t_id_tbl;
1470
1471 l_src_item_asso_id_tbl t_id_tbl;
1472 l_src_item_pattern_tbl t_pattern_tbl;
1473 l_item_asso_id_tbl t_asso_id_tbl;
1474 l_item_pattern_tbl t_asso_ptrn_tbl;
1475
1476 l_dummy VARCHAR2(1);
1477 l_action VARCHAR2(10);
1478 --
1479
1480 BEGIN
1481 IF (l_log_procedure >= l_log_current_level) THEN
1482 FND_LOG.string(l_log_procedure, l_full_name || '.begin', 'At the start of the API');
1483 END IF;
1484
1485 IF (l_log_statement >= l_log_current_level) THEN
1486 FND_LOG.string(l_log_statement, l_full_name,
1487 ' p_frk_or_mrg_flg = '||p_frk_or_mrg_flg||
1488 ', p_item_group_id = '||p_item_group_id||
1489 ', p_src_item_group_id = '||p_src_item_group_id);
1490 END IF;
1491
1492 -- Check whether SB rule update is required
1493 OPEN chk_rule_upd_required(p_src_item_group_id);
1494 FETCH chk_rule_upd_required INTO l_dummy;
1495 IF (chk_rule_upd_required%NOTFOUND) THEN
1496 -- no SB rules exist for the source item group - return
1497 CLOSE chk_rule_upd_required;
1498 IF (l_log_statement >= l_log_current_level) THEN
1499 FND_LOG.string(l_log_statement, l_full_name, 'no SB rules exist for the source item group - return');
1500 END IF;
1501 RETURN;
1502 END IF;
1503 CLOSE chk_rule_upd_required;
1504
1505 -- Check the given item group ids
1506 OPEN chk_item_group_id_csr(p_item_group_id);
1507 FETCH chk_item_group_id_csr INTO l_dummy;
1508 IF (chk_item_group_id_csr%NOTFOUND) THEN
1509 -- invalid item group id
1510 IF (l_log_statement >= l_log_current_level) THEN
1511 FND_LOG.string(l_log_statement, l_full_name, 'invalid item group id');
1512 END IF;
1513 FND_MESSAGE.Set_Name(G_APP_NAME, 'AHL_MC_ITEMGRP_INVALID'); -- Item Group ITEM_GRP is invalid.
1514 FND_MESSAGE.Set_Token('ITEM_GRP', p_item_group_id);
1515 FND_MSG_PUB.ADD;
1516 END IF;
1517 CLOSE chk_item_group_id_csr;
1518 OPEN chk_item_group_id_csr(p_src_item_group_id);
1519 FETCH chk_item_group_id_csr INTO l_dummy;
1520 IF (chk_item_group_id_csr%NOTFOUND) THEN
1521 -- invalid source item group id
1522 IF (l_log_statement >= l_log_current_level) THEN
1523 FND_LOG.string(l_log_statement, l_full_name, 'invalid source item group id');
1524 END IF;
1525 FND_MESSAGE.Set_Name(G_APP_NAME, 'AHL_MC_ITEMGRP_INVALID'); -- Item Group ITEM_GRP is invalid.
1526 FND_MESSAGE.Set_Token('ITEM_GRP', p_src_item_group_id);
1527 FND_MSG_PUB.ADD;
1528 END IF;
1529 CLOSE chk_item_group_id_csr;
1530
1531 -- Check for the fork or merge flag
1532 IF (NVL(p_frk_or_mrg_flg, '-') = 'F') THEN
1533 -- Fork handling
1534 -- 1) Get the SB rules defined for the positions affected by fork
1535 OPEN get_rule_id_for_fork_csr(p_src_item_group_id);
1536 FETCH get_rule_id_for_fork_csr BULK COLLECT INTO l_rule_id_for_fork_tbl;
1537 CLOSE get_rule_id_for_fork_csr;
1538 IF (l_log_statement >= l_log_current_level) THEN
1539 FND_LOG.string(l_log_statement, l_full_name, 'l_rule_id_for_fork_tbl.COUNT = '||l_rule_id_for_fork_tbl.COUNT);
1540 END IF;
1541
1542 IF (l_rule_id_for_fork_tbl.COUNT > 0) THEN
1543 FOR i IN l_rule_id_for_fork_tbl.FIRST..l_rule_id_for_fork_tbl.LAST LOOP
1544 -- 2) For these rules, delete the items coming from the source item group
1545 DELETE FROM AHL_SB_RULE_ITEMS
1546 WHERE rule_id = l_rule_id_for_fork_tbl(i)
1547 AND item_group_id = p_src_item_group_id;
1548
1549 IF (l_log_statement >= l_log_current_level) THEN
1550 FND_LOG.string(l_log_statement, l_full_name, 'applicable rule items deleted');
1551 END IF;
1552
1553 -- 3) If the rule has no items left, then delete it too
1554 l_dummy := NULL;
1555 OPEN chk_rule_del_OK_csr(l_rule_id_for_fork_tbl(i));
1556 FETCH chk_rule_del_OK_csr INTO l_dummy;
1557 CLOSE chk_rule_del_OK_csr;
1558 IF (l_dummy IS NULL) THEN
1559 -- delete the SB rule itself
1560 DELETE FROM AHL_SB_POSITION_RULES WHERE rule_id = l_rule_id_for_fork_tbl(i);
1561
1562 IF (l_log_statement >= l_log_current_level) THEN
1563 FND_LOG.string(l_log_statement, l_full_name, 'rule deleted = '||l_rule_id_for_fork_tbl(i));
1564 END IF;
1565 END IF;
1566 END LOOP;
1567 END IF;
1568 ELSIF (NVL(p_frk_or_mrg_flg, '-') = 'M') THEN
1569 -- Merge handling
1570 -- 1) Get the item associations' details for the given source item group
1571 OPEN get_item_asso_det_csr(p_src_item_group_id);
1572 FETCH get_item_asso_det_csr BULK COLLECT INTO l_src_item_asso_id_tbl, l_src_item_pattern_tbl;
1573 CLOSE get_item_asso_det_csr;
1574 IF (l_log_statement >= l_log_current_level) THEN
1575 FND_LOG.string(l_log_statement, l_full_name, 'l_src_item_asso_id_tbl.COUNT = '||l_src_item_asso_id_tbl.COUNT);
1576 END IF;
1577
1578 IF (l_src_item_asso_id_tbl.COUNT > 0) THEN
1579 -- 2) Get the item associations' details for the given item group, in associative arrays
1580 FOR l_csr_rec IN get_item_asso_det_csr(p_item_group_id) LOOP
1581 l_item_asso_id_tbl(l_csr_rec.item_pattern) := l_csr_rec.item_association_id;
1582 l_item_pattern_tbl(l_csr_rec.item_pattern) := l_csr_rec.item_pattern;
1583 END LOOP;
1584
1585 -- 3) For the item pattern present in the source but not in the given item group,
1586 -- i.e. removed and updated items, delete the corresponding item from the rule items
1587 -- For others, i.e. retained items, update the corresponding associations in rule items
1588 FOR i IN l_src_item_asso_id_tbl.FIRST..l_src_item_asso_id_tbl.LAST LOOP
1589 -- check if the rule item exists for the source association
1590 l_dummy := NULL;
1591 OPEN chk_rule_exists_csr(l_src_item_asso_id_tbl(i));
1592 FETCH chk_rule_exists_csr INTO l_dummy;
1593 CLOSE chk_rule_exists_csr;
1594 IF (l_dummy IS NOT NULL) THEN
1595 IF (NOT l_item_pattern_tbl.EXISTS(l_src_item_pattern_tbl(i))) THEN
1596 -- delete the rule item
1597 DELETE FROM AHL_SB_RULE_ITEMS WHERE item_association_id = l_src_item_asso_id_tbl(i);
1598 l_action := 'deleted';
1599 ELSE
1600 -- update the rule item
1601 UPDATE AHL_SB_RULE_ITEMS
1602 SET item_association_id = l_item_asso_id_tbl(l_src_item_pattern_tbl(i))
1603 WHERE item_association_id = l_src_item_asso_id_tbl(i);
1604 l_action := 'updated';
1605 END IF; -- item pattern check
1606
1607 IF (l_log_statement >= l_log_current_level) THEN
1608 FND_LOG.string(l_log_statement, l_full_name, 'rule item '||l_action||' = '||l_src_item_asso_id_tbl(i));
1609 END IF;
1610 END IF; -- l_dummy check
1611 END LOOP; -- LOOP for l_src_item_asso_id_tbl
1612 END IF; -- l_src_item_asso_id_tbl COUNT check
1613
1614 -- 4) Get all the active SB rules, and if any rule has no item left now, then delete it
1615 OPEN get_rule_id_for_merge_csr;
1616 FETCH get_rule_id_for_merge_csr BULK COLLECT INTO l_rule_id_for_merge_tbl;
1617 CLOSE get_rule_id_for_merge_csr;
1618 IF (l_log_statement >= l_log_current_level) THEN
1619 FND_LOG.string(l_log_statement, l_full_name, 'l_rule_id_for_merge_tbl.COUNT = '||l_rule_id_for_merge_tbl.COUNT);
1620 END IF;
1621 IF (l_rule_id_for_merge_tbl.COUNT > 0) THEN
1622 FOR i IN l_rule_id_for_merge_tbl.FIRST..l_rule_id_for_merge_tbl.LAST LOOP
1623 l_dummy := NULL;
1624 OPEN chk_rule_del_OK_csr(l_rule_id_for_merge_tbl(i));
1625 FETCH chk_rule_del_OK_csr INTO l_dummy;
1626 CLOSE chk_rule_del_OK_csr;
1627 IF (l_dummy IS NULL) THEN
1628 -- delete the SB rule itself
1629 DELETE FROM AHL_SB_POSITION_RULES WHERE rule_id = l_rule_id_for_merge_tbl(i);
1630
1631 IF (l_log_statement >= l_log_current_level) THEN
1632 FND_LOG.string(l_log_statement, l_full_name, 'rule deleted = '||l_rule_id_for_merge_tbl(i));
1633 END IF;
1634 END IF;
1635 END LOOP;
1636 END IF; -- l_rule_id_for_merge_tbl COUNT check
1637 END IF; -- fork or merge indicator check
1638
1639 IF (l_log_procedure >= l_log_current_level) THEN
1640 FND_LOG.string(l_log_procedure, l_full_name || '.end', 'At the end of the API');
1641 END IF;
1642
1643 END Update_Rules_For_IG;
1644 ------------------------------------------------------------------------------------
1645
1646 ------------------------------------------------------------------------------------
1647 -- Start of Comments
1648 -- Procedure name : Get_Violated_Instances
1649 -- Type : Public
1650 -- Function : Gets the SB rules' violated instance ids for a given unit, in an associative array.
1651 -- Pre-reqs :
1652 -- Parameters :
1653 --
1654 -- Get_Violated_Instances Parameters:
1655 -- p_uc_header_id IN Given unit header id. Required
1656 --
1657 -- End of Comments
1658
1659 PROCEDURE Get_Violated_Instances (
1660 p_api_version IN NUMBER := 1.0,
1661 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1662 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1663 p_module_type IN VARCHAR2 := NULL,
1664 p_uc_header_id IN NUMBER,
1665 x_violated_inst_tbl OUT NOCOPY Violated_Inst_Tbl_Type,
1666 x_return_status OUT NOCOPY VARCHAR2,
1667 x_msg_count OUT NOCOPY NUMBER,
1668 x_msg_data OUT NOCOPY VARCHAR2
1669 ) IS
1670
1671 -- cursor to check the unit header id
1672 CURSOR chk_unit_header_id_csr (c_uc_header_id NUMBER) IS
1673 SELECT 'X'
1674 FROM AHL_UNIT_CONFIG_HEADERS
1675 WHERE unit_config_header_id = c_uc_header_id
1676 AND TRUNC(NVL(active_start_date, SYSDATE)) <= TRUNC(SYSDATE)
1677 AND TRUNC(NVL(active_end_date, SYSDATE+1)) > TRUNC(SYSDATE);
1678
1679 -- cursor to get applicable SB rules for the given unit
1680 CURSOR get_appl_sb_rules_csr (c_uc_header_id NUMBER) IS
1681 SELECT SB.rule_id,
1682 SB.relationship_id
1683 FROM AHL_SB_POSITION_RULES SB,
1684 AHL_UNIT_CONFIG_HEADERS UC
1685 WHERE UC.unit_config_header_id = c_uc_header_id
1686 AND SB.mc_header_id = UC.master_config_id
1687 -- take only the rules which have MRs accomplished on the unit
1688 AND EXISTS (SELECT 'X'
1689 FROM AHL_UNIT_EFFECTIVITIES_B UE
1690 WHERE UE.mr_header_id = SB.mr_header_id
1691 AND UE.csi_item_instance_id = UC.csi_item_instance_id -- SB MRs are effective on root node
1692 AND UE.accomplished_date IS NOT NULL)
1693 -- take only the MAX sequence rules for a position, among the accomplished MRs
1694 AND SB.rule_sequence = (SELECT MAX(SB2.rule_sequence)
1695 FROM AHL_SB_POSITION_RULES SB2
1696 WHERE SB2.relationship_id = SB.relationship_id
1697 AND EXISTS (SELECT 'X'
1698 FROM AHL_UNIT_EFFECTIVITIES_B UE
1699 WHERE UE.mr_header_id = SB2.mr_header_id
1700 AND UE.csi_item_instance_id = UC.csi_item_instance_id
1701 AND UE.accomplished_date IS NOT NULL))
1702 AND TRUNC(NVL(SB.active_start_date, SYSDATE)) <= TRUNC(SYSDATE)
1703 AND TRUNC(NVL(SB.active_end_date, SYSDATE+1)) > TRUNC(SYSDATE);
1704
1705 -- cursor to validate the given SB rule against the given unit and relationship
1706 CURSOR validate_sb_rule_csr (c_uc_header_id NUMBER,
1707 c_rule_id NUMBER,
1708 c_relationship_id NUMBER) IS
1709 SELECT 'X'
1710 FROM AHL_SB_RULE_ITEMS SBI,
1711 AHL_ITEM_ASSOCIATIONS_B IAB,
1712 CSI_ITEM_INSTANCES CSI,
1713 CSI_II_RELATIONSHIPS CSR
1714 WHERE SBI.rule_id = c_rule_id
1715 AND IAB.item_association_id = SBI.item_association_id
1716 AND CSI.inventory_item_id = IAB.inventory_item_id
1717 AND CSI.inv_master_organization_id = IAB.inventory_org_id
1718 AND CSR.subject_id = CSI.instance_id -- SB rules are created for non-root positions
1719 AND CSR.position_reference = TO_CHAR(c_relationship_id)
1720 AND CSR.relationship_type_code = 'COMPONENT-OF'
1721 AND TRUNC(NVL(CSR.active_end_date, SYSDATE+1)) > TRUNC(SYSDATE)
1722 AND AHL_UTIL_UC_PKG.get_sub_uc_header_id(CSR.object_id) = c_uc_header_id;
1723
1724 -- cursor to get the instance id for a given unit and relationship
1725 CURSOR get_instance_id_csr (c_uc_header_id NUMBER,
1726 c_relationship_id NUMBER) IS
1727 SELECT subject_id
1728 FROM CSI_II_RELATIONSHIPS
1729 WHERE position_reference = TO_CHAR(c_relationship_id)
1730 AND relationship_type_code = 'COMPONENT-OF'
1731 AND TRUNC(NVL(active_end_date, SYSDATE+1)) > TRUNC(SYSDATE)
1732 AND AHL_UTIL_UC_PKG.get_sub_uc_header_id(object_id) = c_uc_header_id; -- SB rules are created for non-root positions
1733
1734 --
1735 l_api_version CONSTANT NUMBER := 1.0;
1736 l_api_name CONSTANT VARCHAR2(30) := 'Get_Violated_Instances';
1737 l_full_name CONSTANT VARCHAR2(100) := 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name;
1738
1739 TYPE t_id_tbl IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
1740
1741 l_rule_id_tbl t_id_tbl;
1742 l_rel_id_tbl t_id_tbl;
1743
1744 l_instance_id NUMBER;
1745 l_dummy VARCHAR2(1);
1746 --
1747
1748 BEGIN
1749 IF (l_log_procedure >= l_log_current_level) THEN
1750 FND_LOG.string(l_log_procedure, l_full_name || '.begin', 'At the start of the API');
1751 END IF;
1752
1753 -- Standard start of API savepoint
1754 SAVEPOINT Get_Violated_Instances_Pvt;
1755
1756 -- Standard call to check for call compatibility
1757 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version,
1758 l_api_name, G_PKG_NAME) THEN
1759 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1760 END IF;
1761
1762 -- Initialize message list if p_init_msg_list is set to TRUE
1763 IF FND_API.To_Boolean( p_init_msg_list) THEN
1764 FND_MSG_PUB.Initialize;
1765 END IF;
1766
1767 -- Initialize Procedure return status to success
1768 x_return_status := FND_API.G_RET_STS_SUCCESS;
1769
1770 IF (l_log_statement >= l_log_current_level) THEN
1771 FND_LOG.string(l_log_statement, l_full_name,
1772 ' p_uc_header_id = '||p_uc_header_id);
1773 END IF;
1774
1775 -- Check for the unit header id
1776 OPEN chk_unit_header_id_csr(p_uc_header_id);
1777 FETCH chk_unit_header_id_csr INTO l_dummy;
1778 IF (chk_unit_header_id_csr%NOTFOUND) THEN
1779 -- invalid unit header id
1780 CLOSE chk_unit_header_id_csr;
1781 IF (l_log_statement >= l_log_current_level) THEN
1782 FND_LOG.string(l_log_statement, l_full_name, 'invalid unit header id');
1783 END IF;
1784 -- The Unit Configuration header identifier UC_HEADER_ID is invalid.
1785 FND_MESSAGE.Set_Name(G_APP_NAME, 'AHL_UC_HEADER_ID_INVALID');
1786 FND_MESSAGE.Set_Token('UC_HEADER_ID', p_uc_header_id);
1787 FND_MSG_PUB.ADD;
1788 RAISE FND_API.G_EXC_ERROR;
1789 END IF;
1790 CLOSE chk_unit_header_id_csr;
1791
1792 -- Get the applicable SB rules
1793 OPEN get_appl_sb_rules_csr(p_uc_header_id);
1794 FETCH get_appl_sb_rules_csr BULK COLLECT INTO l_rule_id_tbl, l_rel_id_tbl;
1795 CLOSE get_appl_sb_rules_csr;
1796
1797 IF (l_log_statement >= l_log_current_level) THEN
1798 FND_LOG.string(l_log_statement, l_full_name,
1799 ' l_rule_id_tbl.COUNT = '||l_rule_id_tbl.COUNT||
1800 ' ,l_rel_id_tbl.COUNT = '||l_rel_id_tbl.COUNT);
1801 END IF;
1802
1803 -- Validate the rules thus fetched above
1804 IF (l_rule_id_tbl.COUNT > 0) THEN
1805 FOR i IN l_rule_id_tbl.FIRST..l_rule_id_tbl.LAST LOOP
1806 OPEN validate_sb_rule_csr(p_uc_header_id, l_rule_id_tbl(i), l_rel_id_tbl(i));
1807 FETCH validate_sb_rule_csr INTO l_dummy;
1808 IF (validate_sb_rule_csr%NOTFOUND) THEN
1809 -- Get this erring rule's instance id, and put it in the OUT table parameter
1810 l_instance_id := NULL;
1811 OPEN get_instance_id_csr(p_uc_header_id, l_rel_id_tbl(i));
1812 FETCH get_instance_id_csr INTO l_instance_id;
1813 CLOSE get_instance_id_csr;
1814 IF (l_instance_id IS NOT NULL) THEN
1815 x_violated_inst_tbl(l_instance_id) := l_instance_id;
1816 END IF;
1817 IF (l_log_statement >= l_log_current_level) THEN
1818 FND_LOG.string(l_log_statement, l_full_name,
1819 ' rule violated = '||l_rule_id_tbl(i)||
1820 ', l_instance_id = '||l_instance_id);
1821 END IF;
1822 END IF;
1823 CLOSE validate_sb_rule_csr;
1824 END LOOP;
1825 END IF;
1826
1827 IF (l_log_procedure >= l_log_current_level) THEN
1828 FND_LOG.string(l_log_procedure, l_full_name || '.end', 'At the end of the API');
1829 END IF;
1830
1831 EXCEPTION
1832 WHEN FND_API.G_EXC_ERROR THEN
1833 ROLLBACK TO Get_Violated_Instances_Pvt;
1834 x_return_status := FND_API.G_RET_STS_ERROR;
1835 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1836 p_data => x_msg_data,
1837 p_encoded => FND_API.G_FALSE);
1838 IF (l_log_exception >= l_log_current_level) THEN
1839 FND_LOG.string(l_log_exception, l_full_name, 'Execution Error: ' || x_msg_data);
1840 END IF;
1841
1842 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1843 ROLLBACK TO Get_Violated_Instances_Pvt;
1844 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1845 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1846 p_data => x_msg_data,
1847 p_encoded => FND_API.G_FALSE);
1848 IF (l_log_unexpected >= l_log_current_level) THEN
1849 FND_LOG.string(l_log_unexpected, l_full_name, 'Unexpected Exception: ' || x_msg_data);
1850 END IF;
1851
1852 WHEN OTHERS THEN
1853 ROLLBACK TO Get_Violated_Instances_Pvt;
1854 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1855 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1856 FND_MSG_PUB.add_exc_msg(p_pkg_name => G_PKG_NAME,
1857 p_procedure_name => l_api_name,
1858 p_error_text => SUBSTR(SQLERRM,1,500));
1859 END IF;
1860 FND_MSG_PUB.count_and_get(p_count => x_msg_count,
1861 p_data => x_msg_data,
1862 p_encoded => fnd_api.G_FALSE);
1863 IF (l_log_unexpected >= l_log_current_level) THEN
1864 FND_LOG.string(l_log_unexpected, l_full_name, 'Other Exception: ' || x_msg_data);
1865 END IF;
1866
1867 END Get_Violated_Instances;
1868 ------------------------------------------------------------------------------------
1869
1870 ------------------------------------------------------------------------------------
1871 -- Start of Comments
1872 -- Procedure name : Get_Affected_MC
1873 -- Type : Public
1874 -- Function : Gets the affected MC header ids for a given MR
1875 -- Pre-reqs :
1876 -- Parameters :
1877 --
1878 -- Get_Affected_MC Parameters:
1879 -- p_mr_header_id IN Given MR header id. Required
1880 --
1881 -- End of Comments
1882
1883 PROCEDURE Get_Affected_MC (
1884 p_api_version IN NUMBER := 1.0,
1885 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1886 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1887 p_module_type IN VARCHAR2 := NULL,
1888 p_mr_header_id IN NUMBER,
1889 x_affected_mc_tbl OUT NOCOPY Affected_MC_Tbl_Type,
1890 x_return_status OUT NOCOPY VARCHAR2,
1891 x_msg_count OUT NOCOPY NUMBER,
1892 x_msg_data OUT NOCOPY VARCHAR2
1893 ) IS
1894
1895 -- cursor to check the given MR header id
1896 CURSOR chk_mr_header_id_csr (c_mr_header_id NUMBER) IS
1897 SELECT 'X'
1898 FROM AHL_MR_HEADERS_B
1899 WHERE mr_header_id = c_mr_header_id
1900 AND program_type_code = 'SERV_BLTN';
1901
1902 -- cursor to get the MC header id for a unit
1903 CURSOR get_mc_header_id_csr (c_uc_header_id NUMBER) IS
1904 SELECT master_config_id
1905 FROM AHL_UNIT_CONFIG_HEADERS
1906 WHERE unit_config_header_id = c_uc_header_id;
1907
1908 --
1909 l_api_version CONSTANT NUMBER := 1.0;
1910 l_api_name CONSTANT VARCHAR2(30) := 'Get_Affected_MC';
1911 l_full_name CONSTANT VARCHAR2(100) := 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name;
1912
1913 l_mr_item_inst_tbl AHL_FMP_PVT.MR_Item_Instance_Tbl_Type;
1914 l_mc_hdr_id NUMBER;
1915 l_dummy VARCHAR2(1);
1916 --
1917
1918 BEGIN
1919 IF (l_log_procedure >= l_log_current_level) THEN
1920 FND_LOG.string(l_log_procedure, l_full_name || '.begin', 'At the start of the API');
1921 END IF;
1922
1923 -- Standard start of API savepoint
1924 SAVEPOINT Get_Affected_MC_Pvt;
1925
1926 -- Standard call to check for call compatibility
1927 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version,
1928 l_api_name, G_PKG_NAME) THEN
1929 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1930 END IF;
1931
1932 -- Initialize message list if p_init_msg_list is set to TRUE
1933 IF FND_API.To_Boolean( p_init_msg_list) THEN
1934 FND_MSG_PUB.Initialize;
1935 END IF;
1936
1937 -- Initialize Procedure return status to success
1938 x_return_status := FND_API.G_RET_STS_SUCCESS;
1939
1940 IF (l_log_statement >= l_log_current_level) THEN
1941 FND_LOG.string(l_log_statement, l_full_name,
1942 ' p_mr_header_id = '||p_mr_header_id);
1943 END IF;
1944
1945 -- Check for the given MR id
1946 OPEN chk_mr_header_id_csr(p_mr_header_id);
1947 FETCH chk_mr_header_id_csr INTO l_dummy;
1948 IF (chk_mr_header_id_csr%NOTFOUND) THEN
1949 -- invalid MR header id
1950 CLOSE chk_mr_header_id_csr;
1951 IF (l_log_statement >= l_log_current_level) THEN
1952 FND_LOG.string(l_log_statement, l_full_name, 'invalid MR header id');
1953 END IF;
1954 FND_MESSAGE.Set_Name(G_APP_NAME, 'AHL_UMP_PUE_MR_NOTFOUND'); -- Maintenance requirement ID (MR_ID) not found.
1955 FND_MESSAGE.Set_Token('MR_ID', p_mr_header_id);
1956 FND_MSG_PUB.ADD;
1957 RAISE FND_API.G_EXC_ERROR;
1958 END IF;
1959 CLOSE chk_mr_header_id_csr;
1960
1961 -- 1) Get the MR affected items
1962 -- NOTE: leave the following parameters to their default values give below:
1963 -- p_mr_effectivity_id := NULL; need to look into all the effectivities of the MR
1964 -- p_top_node_flag := 'N'; no need to get the top instance
1965 -- p_unique_inst_flag := 'N'; no need to get DISTINCT instances
1966 -- p_sort_flag := 'N'; no need to get a sorted list of instances
1967 AHL_FMP_PVT.Get_MR_Affected_Items(
1968 p_api_version => 1.0,
1969 p_init_msg_list => p_init_msg_list,
1970 p_validation_level => p_validation_level,
1971 p_mr_header_id => p_mr_header_id,
1972 x_mr_item_inst_tbl => l_mr_item_inst_tbl,
1973 x_return_status => x_return_status,
1974 x_msg_count => x_msg_count,
1975 x_msg_data => x_msg_data
1976 );
1977
1978 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1979 IF (l_log_statement >= l_log_current_level) THEN
1980 FND_LOG.string(l_log_statement, l_full_name, 'AHL_FMP_PVT.Get_MR_Affected_Items call errored out');
1981 END IF;
1982 RAISE FND_API.G_EXC_ERROR;
1983 END IF;
1984
1985 IF (l_log_statement >= l_log_current_level) THEN
1986 FND_LOG.string(l_log_statement, l_full_name, 'l_mr_item_inst_tbl.COUNT = '||l_mr_item_inst_tbl.COUNT);
1987 END IF;
1988
1989 IF (l_mr_item_inst_tbl.COUNT > 0) THEN
1990 -- 2) Get all the effective MCs
1991 FOR i IN l_mr_item_inst_tbl.FIRST..l_mr_item_inst_tbl.LAST LOOP
1992 OPEN get_mc_header_id_csr(l_mr_item_inst_tbl(i).uc_header_id);
1993 FETCH get_mc_header_id_csr INTO l_mc_hdr_id;
1994 IF (get_mc_header_id_csr%FOUND) THEN
1995 -- put this MC header id in the OUT table for effective MCs
1996 IF (x_affected_mc_tbl.COUNT > 0) THEN
1997 x_affected_mc_tbl(x_affected_mc_tbl.LAST+1) := l_mc_hdr_id;
1998 ELSE
1999 x_affected_mc_tbl(0) := l_mc_hdr_id;
2000 END IF;
2001 IF (l_log_statement >= l_log_current_level) THEN
2002 FND_LOG.string(l_log_statement, l_full_name, 'effective MC header id put = '||l_mc_hdr_id);
2003 END IF;
2004 END IF;
2005 CLOSE get_mc_header_id_csr;
2006 END LOOP; -- LOOP for l_mr_item_inst_tbl
2007 END IF; -- l_mr_item_inst_tbl COUNT check
2008
2009 IF (l_log_statement >= l_log_current_level) THEN
2010 FND_LOG.string(l_log_statement, l_full_name,
2011 ' x_affected_mc_tbl.COUNT = '||x_affected_mc_tbl.COUNT);
2012 END IF;
2013
2014 IF (l_log_procedure >= l_log_current_level) THEN
2015 FND_LOG.string(l_log_procedure, l_full_name || '.end', 'At the end of the API');
2016 END IF;
2017
2018 EXCEPTION
2019 WHEN FND_API.G_EXC_ERROR THEN
2020 ROLLBACK TO Get_Affected_MC_Pvt;
2021 x_return_status := FND_API.G_RET_STS_ERROR;
2022 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2023 p_data => x_msg_data,
2024 p_encoded => FND_API.G_FALSE);
2025 IF (l_log_exception >= l_log_current_level) THEN
2026 FND_LOG.string(l_log_exception, l_full_name, 'Execution Error: ' || x_msg_data);
2027 END IF;
2028
2029 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2030 ROLLBACK TO Get_Affected_MC_Pvt;
2031 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2032 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2033 p_data => x_msg_data,
2034 p_encoded => FND_API.G_FALSE);
2035 IF (l_log_unexpected >= l_log_current_level) THEN
2036 FND_LOG.string(l_log_unexpected, l_full_name, 'Unexpected Exception: ' || x_msg_data);
2037 END IF;
2038
2039 WHEN OTHERS THEN
2040 ROLLBACK TO Get_Affected_MC_Pvt;
2041 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2042 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2043 FND_MSG_PUB.add_exc_msg(p_pkg_name => G_PKG_NAME,
2044 p_procedure_name => l_api_name,
2045 p_error_text => SUBSTR(SQLERRM,1,500));
2046 END IF;
2047 FND_MSG_PUB.count_and_get(p_count => x_msg_count,
2048 p_data => x_msg_data,
2049 p_encoded => fnd_api.G_FALSE);
2050 IF (l_log_unexpected >= l_log_current_level) THEN
2051 FND_LOG.string(l_log_unexpected, l_full_name, 'Other Exception: ' || x_msg_data);
2052 END IF;
2053
2054 END Get_Affected_MC;
2055 ------------------------------------------------------------------------------------
2056
2057 END AHL_SB_RULES_PVT;
[Home] [Help]