1 PACKAGE BODY AHL_MR_LOOP_CHAIN_RELNS_PVT AS
2 /* $Header: AHLVLCRB.pls 120.0.12020000.2 2012/12/10 13:43:11 shnatu noship $ */
3 G_PKG_NAME VARCHAR2(30) := 'AHL_MR_LOOP_CHAIN_RELNS_PVT';
4
5 /* local procedures declaration
6 */
7 PROCEDURE GET_MR_LOOP_CHAIN_DTLS
8 (
9 p_start_mr_reln_id IN NUMBER,
10 p_relationship IN VARCHAR2,
11 p_mr_header_id IN NUMBER,
12 x_cm_mr_chain_loop OUT NOCOPY MR_CHAIN_LOOP_TBL_TYPE
13 );
14 -- Declare local procedure GET_UCHEADER --
15 PROCEDURE get_ucHeader(
16 p_item_instance_id IN NUMBER,
17 x_ucHeaderID OUT NOCOPY NUMBER,
18 x_unitName OUT NOCOPY VARCHAR2);
19
20 /** Procedure to validate the list of MRs added in the screen.
21 **/
22 PROCEDURE VALIDATE_LOOP_CHAIN
23 (
24 p_api_version IN NUMBER,
25 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
26 p_commit IN VARCHAR2 := FND_API.G_FALSE,
27 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
28 p_page_mode IN VARCHAR2 := 'CREATE',
29 x_return_status OUT NOCOPY VARCHAR2,
30 x_msg_count OUT NOCOPY NUMBER,
31 x_msg_data OUT NOCOPY ERR_MSG_DATA,
32 x_mr_header_id OUT NOCOPY MR_HEADER_IDS_TBL_TYPE,
33 p_mr_relns IN MR_RELNS_TBL_TYPE )
34 IS
35 -- Cursor to fetch the MR details for a given MR header ID
36 CURSOR mr_dtls_csr(p_mr_header_id_csr NUMBER)
37 IS
38 SELECT MR.mr_header_id,
39 MR.title,
40 MR.revision,
41 MR.repetitive_flag,
42 MR.implement_status_code,
43 MR.effective_from,
44 MR.effective_to
45 FROM ahl_mr_headers_b MR
46 WHERE MR.mr_header_id = p_mr_header_id_csr
47 AND MR.repetitive_flag = 'Y'
48 AND TRUNC(sysdate) <= TRUNC(NVL(MR.effective_to,sysdate+1));
49 -- Cursor to fetch the relationship details
50 CURSOR mr_relns_csr(p_mr_header_id_csr NUMBER, p_effective_date DATE)
51 IS
52 SELECT rel.mr_header_id,
53 rel.related_mr_header_id,
54 relationship_code
55 FROM ahl_mr_relationships rel,
56 ahl_mr_headers_b MR
57 WHERE (rel.mr_header_id = MR.mr_header_id
58 OR rel.related_mr_header_id = MR.mr_header_id)
59 AND (rel.mr_header_id = p_mr_header_id_csr
60 OR rel.related_mr_header_id = p_mr_header_id_csr)
61 AND TRUNC(p_effective_date) <= TRUNC(NVL(MR.effective_to, p_effective_date + 1))
62 AND MR.MR_STATUS_CODE IN ('DRAFT','COMPLETE','APPROVAL_PENDING');
63 -- AND rel.relationship_code <> 'PARENT';
64
65 l_mr_relns_rec mr_relns_csr%ROWTYPE;
66 l_debug VARCHAR2(1) :=AHL_DEBUG_PUB.is_log_enabled;
67 l_api_name CONSTANT VARCHAR2(30) := 'VALIDATE_LOOP_CHAIN';
68 l_api_version CONSTANT NUMBER := 1.0;
69 l_msg_count NUMBER;
70 l_return_status VARCHAR2(1);
71 l_msg_data VARCHAR2(30);
72 l_max_effective_from DATE := NULL;
73 l_min_effective_to DATE := NULL;
74 l_start_mr_rel_id NUMBER;
75 l_other_valid_start_mr NUMBER;
76 l_start_mr_title AHL_MR_HEADERS_B.TITLE%TYPE;
77 l_sequence NUMBER := 1;
78 l_mr_loop_chain_rel_cd VARCHAR2(30);
79 l_start_mr_header_id number;
80 l_mr_loop_chain MR_CHAIN_LOOP_TBL_TYPE;
81 l_cm_instances MR_ITEM_INSTANCE_TBL_TYPE;
82 l_mr_rec_type mr_dtls_csr%ROWTYPE;
83 l_is_mr_valid VARCHAR2(1) := 'N';
84
85 BEGIN
86
87 IF l_debug = 'Y' THEN
88 AHL_DEBUG_PUB.enable_debug;
89 AHL_DEBUG_PUB.debug('Begin private API: AHL_MR_LOOP_CHAIN_RELNS_PVT.VALIDATE_LOOP_CHAIN');
90 END IF;
91
92 IF FND_API.to_boolean(p_init_msg_list) THEN
93 FND_MSG_PUB.initialize;
94 END IF;
95
96 FOR l_mr_index IN p_mr_relns.FIRST..p_mr_relns.LAST
97 loop
98
99 l_mr_loop_chain(l_mr_index).mr_relationship_id := p_mr_relns(l_mr_index).mr_relationship_id;
100 l_mr_loop_chain(l_mr_index).mr_header_id := p_mr_relns(l_mr_index).mr_header_id;
101 l_mr_loop_chain(l_mr_index).mr_sequence := p_mr_relns(l_mr_index).mr_sequence;
102 l_mr_loop_chain(l_mr_index).RELATIONSHIP := p_mr_relns(l_mr_index).RELATIONSHIP_CODE;
103
104 FOR l_mr_header_rec IN mr_dtls_csr(p_mr_relns(l_mr_index).mr_header_id)
105 LOOP
106
107 IF p_mr_relns(l_mr_index).MR_SEQUENCE = 1 THEN
108 l_start_mr_title := l_mr_header_rec.title;
109 l_start_mr_rel_id := p_mr_relns(l_mr_index).MR_RELATIONSHIP_ID;
110 l_start_mr_header_id := p_mr_relns(l_mr_index).MR_HEADER_ID;
111 END IF;
112
113 --sukhwsin::Complex Assembly Maintenance Changes - starts
114 --Raise error if MR is having implementation status as soft limit because soft limit MR cannot be a part of any relationship.
115 IF (l_mr_header_rec.implement_status_code = 'SOFT_LIMIT') THEN
116 x_return_status := FND_API.G_RET_STS_ERROR;
117 x_mr_header_id(l_mr_index) := p_mr_relns(l_mr_index).mr_header_id;
118 x_msg_data(l_mr_index) := 'AHL_CAM_MR_IMP_STS_ERR';
119 END IF;
120 --sukhwsin::Complex Assembly Maintenance Changes - ends
121
122 --Chain: Except the starting MR, other MRs should be planned.
123 IF l_mr_header_rec.implement_status_code = 'OPTIONAL_DO_NOT_IMPLEMENT' THEN
124 -- Unplanned MRs cannot be 1) part of a Loop, 2) Cannot be other than starting
125 -- MR in a chain
129 x_msg_data(l_mr_index) := 'AHL_FMP_CHAIN_UNPLANNED_MR';
126 IF p_mr_relns(l_mr_index).RELATIONSHIP_CODE = 'CHAIN' AND p_mr_relns(l_mr_index).MR_SEQUENCE <> 1 THEN
127 x_return_status := FND_API.G_RET_STS_ERROR;
128 x_mr_header_id(l_mr_index) := p_mr_relns(l_mr_index).mr_header_id;
130 -- Loop: all MRs should be planned.
131 ELSIF p_mr_relns(l_mr_index).RELATIONSHIP_CODE = 'LOOP' THEN
132 x_return_status := FND_API.G_RET_STS_ERROR;
133 x_mr_header_id(l_mr_index) := p_mr_relns(l_mr_index).mr_header_id;
134 x_msg_data(l_mr_index) := 'AHL_FMP_LOOP_UNPLANNED_MR';
135 END IF;
136
137 ELSE
138 /* * Validating whether the MR is part of any other relationship(i.e. Initiates/Terminates)
139 * However starting MR can be initiated by any MR. */
140 FOR l_mr_relns_rec IN mr_relns_csr(p_mr_relns(l_mr_index).mr_header_id, NVL(l_mr_header_rec.effective_to, SYSDATE))
141 LOOP
142
143 IF l_mr_relns_rec.relationship_code <> 'INITIATES' OR l_mr_relns_rec.mr_header_id = p_mr_relns(l_mr_index).mr_header_id OR p_mr_relns(l_mr_index).MR_SEQUENCE <> 1 THEN
144 x_return_status := FND_API.G_RET_STS_ERROR;
145 x_mr_header_id(l_mr_index) := p_mr_relns(l_mr_index).mr_header_id;
146 IF l_mr_relns_rec.relationship_code = 'INITIATES'
147 THEN
148 x_msg_data(l_mr_index) := 'AHL_FMP_MR_INIT_REL_EXIST';
149
150 ELSIF l_mr_relns_rec.relationship_code = 'TERMINATES'
151 THEN
152 x_msg_data(l_mr_index) := 'AHL_FMP_MR_TERM_REL_EXIST';
153
154 ELSIF l_mr_relns_rec.relationship_code = 'PARENT' AND p_mr_relns(l_mr_index).RELATIONSHIP_CODE = 'LOOP'
155 THEN
156 x_msg_data(l_mr_index) := 'AHL_FMP_GROUP_MR_LOOP';
157
158 ELSIF l_mr_relns_rec.relationship_code = 'PARENT' AND p_mr_relns(l_mr_index).RELATIONSHIP_CODE = 'CHAIN'
159 THEN
160 x_msg_data(l_mr_index) := 'AHL_FMP_GROUP_MR_CHAIN';
161
162 ELSE
163 x_msg_data(l_mr_index) := 'AHL_FMP_MR_REL_EXIST';
164 END IF;
165
166 END IF;
167
168 END LOOP;
169
170 --Identifying the Maximum effective from and Minimum effective To to validate the effective dates.
171 -- IF l_max_effective_from IS NULL THEN
172 -- l_max_effective_from := l_mr_header_rec.effective_from;
173 -- ELSIF (l_mr_header_rec.effective_from > l_max_effective_from AND l_sequence = p_mr_relns(l_mr_index).MR_SEQUENCE) OR (l_mr_header_rec.effective_from < l_max_effective_from) THEN
174 -- l_max_effective_from := l_mr_header_rec.effective_from;
175 -- END IF;
176 --
177 -- IF l_min_effective_to IS NULL THEN
178 -- l_min_effective_to := l_mr_header_rec.effective_to;
179 -- ELSIF (l_mr_header_rec.effective_to < l_min_effective_to AND l_sequence = p_mr_relns(l_mr_index).MR_SEQUENCE) OR (l_mr_header_rec.effective_to > l_min_effective_to) THEN
180 -- l_min_effective_to := l_mr_header_rec.effective_to;
181 -- END IF;,
182
183 END IF;
184
185 IF l_sequence = p_mr_relns(l_mr_index).MR_SEQUENCE THEN
186 l_sequence := l_sequence + 1;
187 END IF;
188
189 END LOOP;
190
191 -- IF l_min_effective_to IS NULL THEN
192 -- l_min_effective_to := SYSDATE;
193 -- END IF;
194
195 END LOOP;
199 IF l_debug = 'Y' THEN
196 FOR l_mr_index IN p_mr_relns.FIRST..p_mr_relns.LAST
197 LOOP
198 --Validating whether the MR is part of another loop/chain relationship.
200 AHL_DEBUG_PUB.enable_debug;
201 AHL_DEBUG_PUB.debug('p_page_mode::' || p_page_mode);
202 END IF;
203
204 OPEN mr_dtls_csr(p_mr_relns(l_mr_index).mr_header_id);
205 FETCH mr_dtls_csr INTO l_mr_rec_type;
206 IF mr_dtls_csr%FOUND THEN
207 l_is_mr_valid := 'Y';
208 END IF;
209 CLOSE mr_dtls_csr;
210
211 -- In case of EDIT mode, there will be a starting MR present already
212 -- and the validation should consider all other relationship except the current one.
213 IF(l_is_mr_valid = 'Y') THEN
214 if p_page_mode = 'EDIT' then
215 l_other_valid_start_mr := get_valid_start_mr_rel(p_mr_relns(l_mr_index).mr_header_id,
216 l_start_mr_header_id, l_start_mr_rel_id, l_start_mr_title,
217 NULL, NVL(l_mr_rec_type.effective_to, SYSDATE), NULL);
218 else
219 l_other_valid_start_mr := get_valid_start_mr_rel(p_mr_relns(l_mr_index).mr_header_id,
220 l_start_mr_header_id, NULL, l_start_mr_title, NULL,
221 NVL(l_mr_rec_type.effective_to, SYSDATE), NULL);
222 END IF;
223
224 -- If there exist another Valid relationship, fetch the relationship type and
225 -- display the error message
226 IF l_other_valid_start_mr IS NOT NULL THEN
227 x_return_status := FND_API.G_RET_STS_ERROR;
228 x_mr_header_id(l_mr_index) := p_mr_relns(l_mr_index).mr_header_id;
229
230 SELECT relationship_code INTO l_mr_loop_chain_rel_cd FROM ahl_mr_loop_chain_relns WHERE mr_relationship_id = l_other_valid_start_mr;
231
232 IF l_mr_loop_chain_rel_cd IS NOT NULL AND l_mr_loop_chain_rel_cd = 'LOOP'
233 THEN
234 x_msg_data(l_mr_index) := 'AHL_FMP_MR_LOOP_REL_EXIST';
235
236 ELSIF l_mr_loop_chain_rel_cd IS NOT NULL AND l_mr_loop_chain_rel_cd = 'CHAIN'
237 THEN
238 x_msg_data(l_mr_index) := 'AHL_FMP_MR_CHAIN_REL_EXIST';
239
240 ELSE
241 x_msg_data(l_mr_index) := 'AHL_FMP_MR_REL_EXIST';
242
243 END IF;
244 END IF;
245 END IF;
246
247 END LOOP;
248
249 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
250 RAISE FND_API.G_EXC_ERROR;
251
252 ELSE
253 -- Check if there exist a common item for all MRs in the relationship,
254 -- if not raise a warning
255 GET_CM_AFFECTED_ITEMS(1.0,FND_API.G_TRUE,FND_API.G_TRUE,NULL, l_return_status, l_msg_count, l_msg_data, l_cm_instances, l_mr_loop_chain);
256
257 IF l_cm_instances IS NULL OR l_cm_instances.count = 0 THEN
258 x_msg_data(0) := 'AHL_FMP_CM_AFF_ITEMS';
259 x_return_status := FND_API.G_RET_STS_ERROR;
260 RAISE FND_API.G_EXC_ERROR;
261 END IF;
262 END IF;
263
264 x_return_status := FND_API.G_RET_STS_SUCCESS;
265
266 IF mr_dtls_csr%ISOPEN THEN
267 CLOSE mr_dtls_csr;
268 END IF;
269
270 IF l_debug = 'Y' THEN
271 AHL_DEBUG_PUB.enable_debug;
272 AHL_DEBUG_PUB.debug('End private API: AHL_MR_LOOP_CHAIN_RELNS_PVT.VALIDATE_LOOP_CHAIN');
273 END IF;
274
275 EXCEPTION
276 WHEN FND_API.G_EXC_ERROR THEN
277 IF l_debug = 'Y' THEN
278 AHL_DEBUG_PUB.enable_debug;
279 AHL_DEBUG_PUB.debug('ERROR IN AHL_FMP_PVT.VALIDATE_LOOP_CHAIN');
280 END IF;
281
282 WHEN OTHERS THEN
283 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
284 p_data => l_msg_data,
285 p_encoded => fnd_api.g_false);
286 IF l_debug = 'Y' THEN
287 AHL_DEBUG_PUB.enable_debug;
288 AHL_DEBUG_PUB.debug('Unexpected ERROR IN AHL_FMP_PVT.VALIDATE_LOOP_CHAIN');
289 END IF;
290
291 END VALIDATE_LOOP_CHAIN;
292
293 /** Function to get a valid start MR Relation. Logic is to find the first active start MR relationship ID, for which the MRs part of the
294 relationship are also active, in sequence and with valid status.
295 **/
296 FUNCTION get_valid_start_mr_rel
297 (
298 p_mr_header_id IN NUMBER,
299 p_start_mr_header_id IN NUMBER,
300 p_start_mr_rel_id IN NUMBER,
301 p_start_mr_title VARCHAR2,
302 p_effective_from IN DATE,
303 p_effective_to IN DATE,
304 p_rel_code IN VARCHAR2
305 )
306 RETURN NUMBER
307 IS
308 -- Fetch a valid loop chain relationship considering the status and effective dates
309 CURSOR get_loop_chain_relns(c_mr_header_id NUMBER, c_start_mr_header_id NUMBER, c_start_mr_rel_id NUMBER, c_start_mr_title VARCHAR2, c_effective_from DATE, c_effective_to DATE, c_rel_code VARCHAR2)
310 IS
311 SELECT rel.mr_header_id,
312 rel.start_mr_relationship_id,
313 rel.sequence_number,
314 rel.relationship_code,
315 mr.mr_status_code,
316 mr.effective_from,
317 mr.effective_to
318 FROM ahl_mr_loop_chain_relns rel,
319 ahl_mr_headers_b mr
320 WHERE mr.mr_header_id = rel.mr_header_id
321 AND rel.start_mr_relationship_id IN
322 (SELECT start_mr_relationship_id
323 FROM ahl_mr_loop_chain_relns A
324 WHERE mr_header_id = c_mr_header_id
325 AND ((c_rel_code IS NULL)
326 OR (relationship_code = c_rel_code))
327 AND ( c_start_mr_rel_id IS NULL
328 OR (start_mr_relationship_id NOT IN
329 (SELECT b.start_mr_relationship_id
330 FROM ahl_mr_headers_b a,
331 ahl_mr_loop_chain_relns b
332 WHERE a.mr_header_id = b.mr_header_id
333 AND b.start_mr_relationship_id = c_start_mr_rel_id))
334 )
338 AND ((c_start_mr_header_id IS NULL)
335 )
336 AND ((c_effective_from IS NULL)
337 OR (TRUNC(c_effective_from) >= TRUNC(MR.effective_from)))
339 OR (rel.start_mr_relationship_id NOT IN
340 (SELECT start_mr_relationship_id FROM ahl_mr_loop_chain_relns
341 WHERE mr_header_id IN (SELECT mr_header_id FROM
342 ahl_mr_headers_b WHERE title = c_start_mr_title
343 AND mr_header_id <> c_start_mr_header_id)
344 )))
345 AND
346 (c_effective_to IS NULL
347 OR (TRUNC(c_effective_to) < TRUNC(NVL(MR.effective_to,sysdate+1))))
348 AND mr.mr_status_code IN ('DRAFT','COMPLETE','APPROVAL_PENDING')
349 AND mr.repetitive_flag = 'Y'
350 AND mr.application_usg_code = FND_PROFILE.VALUE('AHL_APPLN_USAGE')
351 ORDER BY rel.start_mr_relationship_id,
352 rel.sequence_number;
353
354 l_loop_chain_rec get_loop_chain_relns%ROWTYPE;
355 l_sequence NUMBER := 1;
356 l_valid NUMBER := 1;
357 l_loop_index NUMBER := 0;
358 l_start_mr_rel_id NUMBER;
359 l_skip_mr_rel_id NUMBER;
360 l_debug VARCHAR2(1) :=AHL_DEBUG_PUB.is_log_enabled;
361 l_mr_rel_count NUMBER := 0;
362 l_max_sequence NUMBER := 0;
363
364 BEGIN
365 IF l_debug = 'Y' THEN
366 AHL_DEBUG_PUB.enable_debug;
367 AHL_DEBUG_PUB.debug('Begin private API: AHL_MR_LOOP_CHAIN_RELNS_PVT.get_valid_start_mr_rel');
368 END IF;
369
370 FOR l_loop_chain_rec IN get_loop_chain_relns(p_mr_header_id, p_start_mr_header_id, p_start_mr_rel_id, p_start_mr_title, p_effective_from, p_effective_to, p_rel_code)
371 LOOP
372 -- As indicated before, for 'EDIT' mode, we should not consider the existing
373 -- relationship for validation, hence skip if the relationship ID is same as the current one
374 IF l_skip_mr_rel_id IS NULL OR (l_skip_mr_rel_id <> l_loop_chain_rec.start_mr_relationship_id) THEN
375
376 -- This check is to terminate further processing as a valid relationship is identified
377 -- 1. Check whether its the starting MR
378 -- 2. l_valid - indicates that there exist a valid relationship
379 -- 3. l_loop_index - Since l_valid will be initially 1, this variable is used to
380 -- identify if this is not the first iteration
381 -- 4. l_mr_rel_count - holds the maximum sequence number of a particular relationship
382 -- Hence checking l_mr_rel_count <= l_max_sequence means that the last MR of a
383 -- relationship is encountered
384
385 IF l_loop_chain_rec.sequence_number = 1 AND l_valid = 1 AND l_loop_index <> 0 AND l_mr_rel_count <= l_max_sequence THEN
386 EXIT;
387
388 ELSE
389 -- If this is the starting MR of a relationship, store the l_start_mr_rel_id and
390 -- identify the max sequence number of this relationship
391 IF l_loop_chain_rec.sequence_number = 1 THEN
392 l_sequence := 1;
393 l_start_mr_rel_id := l_loop_chain_rec.start_mr_relationship_id;
394 SELECT MAX(sequence_number) INTO l_mr_rel_count FROM ahl_mr_loop_chain_relns WHERE start_mr_relationship_id = l_start_mr_rel_id;
395 ELSE
396 -- If not starting MR and the sequence number is out of synch then set the l_valid
397 -- indicator to 0, which means that its not a valid relationship.(one or more MRs
398 -- not active)
399 IF l_loop_chain_rec.sequence_number > l_sequence THEN
400 l_sequence := 0;
401 l_valid := 0;
402 l_skip_mr_rel_id := l_loop_chain_rec.start_mr_relationship_id;
403 l_start_mr_rel_id := NULL;
404 ELSE
405 l_valid := 1;
406 l_max_sequence := l_loop_chain_rec.sequence_number;
407 END IF;
408
409 END IF;
410
411 END IF;
412 IF l_loop_chain_rec.sequence_number = l_sequence THEN
413 l_sequence := l_sequence + 1;
414 END IF;
415 END IF;
416 l_loop_index := l_loop_index + 1;
417 END LOOP;
418 -- Looping is completed, now check if the max sequence number of a relationship is
419 -- = to the current max sequence number identified thru looping,
420 -- if l_mr_rel_count > l_max_sequence, then it means that the last MR in the
421 -- relationship is invalid and hence the relationship itself is invalid
422 IF l_valid = 1 AND l_mr_rel_count > l_max_sequence THEN
423 l_start_mr_rel_id := NULL;
424 END IF;
425 IF l_debug = 'Y' THEN
426 AHL_DEBUG_PUB.enable_debug;
427 AHL_DEBUG_PUB.debug('End private API: AHL_MR_LOOP_CHAIN_RELNS_PVT.get_valid_start_mr_rel');
428 END IF;
429
430 RETURN l_start_mr_rel_id;
431
432 END get_valid_start_mr_rel;
433
434 /*
435 Procedure to get the details of the starting MRs in the relationship.
436 */
437
438 PROCEDURE get_start_mr_relns(p_mr_header_id IN NUMBER,
442 x_msg_count OUT NOCOPY NUMBER,
439 p_rel_code IN VARCHAR2,
440 x_start_mr_relns OUT NOCOPY MR_CHAIN_LOOP_TBL_TYPE,
441 x_return_status OUT NOCOPY VARCHAR2,
443 x_msg_data OUT NOCOPY VARCHAR2)
444 IS
445 CURSOR get_loop_chain_relns(c_mr_header_id NUMBER, c_rel_code VARCHAR2)
446 IS
447 SELECT rel.mr_header_id,
448 rel.start_mr_relationship_id,
449 rel.sequence_number,
450 rel.relationship_code,
451 mr.mr_status_code,
452 mr.effective_from,
453 mr.effective_to
454 FROM ahl_mr_loop_chain_relns rel,
455 ahl_mr_headers_b mr
456 WHERE mr.mr_header_id = rel.mr_header_id
457 and rel.start_mr_relationship_id in
458 (select reln.start_mr_relationship_id
459 from ahl_mr_loop_chain_relns reln
460 WHERE reln.mr_header_id = c_mr_header_id
461 and ((c_rel_code is null)
462 OR (reln.relationship_code = c_rel_code))
463 )
464 -- Modified for SBE Bug#12837789
465 AND TRUNC(SYSDATE) < TRUNC(NVL(MR.effective_to,SYSDATE+1))
466 AND mr.mr_status_code IN ('DRAFT','COMPLETE','APPROVAL_PENDING')
467 AND MR.repetitive_flag = 'Y'
468 ORDER BY rel.start_mr_relationship_id,
469 rel.sequence_number;
470
471 CURSOR get_mr_dtls(p_mr_header_id IN NUMBER)
472 IS
473 SELECT MR.title,
474 MR_TL.description,
475 STATUS.MEANING status,
476 MR.revision,
477 MR.version_number,
478 MR.effective_from,
479 MR.effective_to
480 FROM ahl_mr_headers_b MR,
481 ahl_mr_headers_tl MR_TL,
482 (SELECT LOOKUP_CODE,
483 MEANING
484 FROM FND_LOOKUP_VALUES
485 WHERE LOOKUP_TYPE = 'AHL_FMP_REVISION_STATUS'
486 AND LANGUAGE = USERENV('LANG')
487 ) STATUS
488 WHERE MR.mr_header_id = p_mr_header_id
489 AND MR_TL.LANGUAGE = USERENV('LANG')
490 AND STATUS.LOOKUP_CODE = MR.mr_status_code
491 AND MR.mr_header_id = MR_TL.mr_header_id
492 AND MR.repetitive_flag = 'Y'
493 -- Modified for SBE Bug#12837789
494 AND TRUNC(sysdate) < TRUNC(NVL(MR.effective_to,sysdate+1));
495
496 l_loop_chain_rec get_loop_chain_relns%ROWTYPE;
497 l_mr_dtls_rec get_mr_dtls%ROWTYPE;
498 l_sequence NUMBER := 1;
499 l_valid NUMBER := 1;
500 l_loop_index NUMBER := 0;
501 l_start_mr_index NUMBER := 0;
502 l_start_mr_header_id NUMBER;
503 l_skip_mr_rel_id NUMBER;
504 l_mr_rel_id NUMBER;
505 l_debug VARCHAR2(1) :=AHL_DEBUG_PUB.is_log_enabled;
506 l_max_rel_seq NUMBER; -- Holds the max seq number from the DB
507 l_current_max_seq NUMBER; -- Holds the current relation's max seq number
508
509 BEGIN
510 IF l_debug = 'Y' THEN
511 AHL_DEBUG_PUB.enable_debug;
512 AHL_DEBUG_PUB.debug('Begin private API: AHL_MR_LOOP_CHAIN_RELNS_PVT.get_start_mr_relns');
513 END IF;
514
515 FOR l_loop_chain_rec IN get_loop_chain_relns(p_mr_header_id, p_rel_code)
516 LOOP
517 /* Check whether the sequence is continous, if any discontinous sequence exist then the relationship is not valid.*/
518 IF l_skip_mr_rel_id IS NULL OR (l_skip_mr_rel_id <> l_loop_chain_rec.start_mr_relationship_id) THEN
519
520 IF l_loop_chain_rec.sequence_number = 1 AND l_valid = 1 AND l_loop_index <> 0
521 AND l_max_rel_seq = l_current_max_seq
522 THEN
523 x_start_mr_relns(l_start_mr_index).MR_HEADER_ID := l_start_mr_header_id ;
524 x_start_mr_relns(l_start_mr_index).mr_relationship_id := l_mr_rel_id;
525 l_start_mr_index := l_start_mr_index + 1;
526 END IF;
527
528 IF l_loop_chain_rec.sequence_number = 1 THEN
529 l_sequence := 1;
530 l_start_mr_header_id := l_loop_chain_rec.mr_header_id;
531 l_mr_rel_id := l_loop_chain_rec.start_mr_relationship_id;
532 SELECT MAX(sequence_number) INTO l_max_rel_seq FROM ahl_mr_loop_chain_relns WHERE
533 start_mr_relationship_id = l_mr_rel_id;
534 l_current_max_seq := 1;
535 ELSE
536 IF l_loop_chain_rec.sequence_number > l_sequence THEN
537 l_sequence := 0;
538 l_valid := 0;
539 l_skip_mr_rel_id := l_loop_chain_rec.start_mr_relationship_id;
540 l_start_mr_header_id := NULL;
541 l_mr_rel_id := NULL;
542 ELSE
543 l_valid := 1;
544 END IF;
545
546 END IF;
547 l_current_max_seq := l_loop_chain_rec.sequence_number;
548
549 IF l_loop_chain_rec.sequence_number = l_sequence THEN
550 l_sequence := l_sequence + 1;
551 END IF;
552 END IF;
553 l_loop_index := l_loop_index + 1;
554 END LOOP;
555 IF l_valid = 1 AND l_start_mr_header_id IS NOT NULL AND l_mr_rel_id IS NOT NULL
556 AND l_max_rel_seq = l_current_max_seq
557 THEN
558 x_start_mr_relns(l_start_mr_index).MR_HEADER_ID := l_start_mr_header_id ;
559 x_start_mr_relns(l_start_mr_index).mr_relationship_id := l_mr_rel_id;
560 END IF;
561
562 IF x_start_mr_relns IS NOT NULL AND x_start_mr_relns.COUNT >= 1 THEN
563 FOR l_mr_index IN x_start_mr_relns.FIRST..x_start_mr_relns.LAST
564 LOOP
565 OPEN get_mr_dtls(x_start_mr_relns(l_mr_index).MR_HEADER_ID);
566 FETCH get_mr_dtls INTO l_mr_dtls_rec;
567
568 IF get_mr_dtls%NOTFOUND
569 THEN
570 l_valid := 0;
571 ELSE
575 x_start_mr_relns(l_mr_index).description := l_mr_dtls_rec.description;
572 x_start_mr_relns(l_mr_index).title := l_mr_dtls_rec.title;
573 x_start_mr_relns(l_mr_index).revision := l_mr_dtls_rec.revision;
574 x_start_mr_relns(l_mr_index).version_number := l_mr_dtls_rec.version_number;
576 x_start_mr_relns(l_mr_index).status := l_mr_dtls_rec.status;
577 x_start_mr_relns(l_mr_index).effective_from := l_mr_dtls_rec.effective_from;
578 x_start_mr_relns(l_mr_index).effective_to := l_mr_dtls_rec.effective_to;
579 END IF;
580 CLOSE get_mr_dtls;
581 END LOOP;
582 END IF;
583 IF l_debug = 'Y' THEN
584 AHL_DEBUG_PUB.enable_debug;
585 AHL_DEBUG_PUB.debug('End private API: AHL_MR_LOOP_CHAIN_RELNS_PVT.get_start_mr_relns');
586 END IF;
587 END get_start_mr_relns;
588
589 /**
590 Procedure to get the common affected items for view Loop/Chain UI
591 */
592 PROCEDURE GET_CM_AFFECTED_ITEMS
593 (
594 p_api_version IN NUMBER,
595 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
596 p_commit IN VARCHAR2 := FND_API.G_FALSE,
597 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
598 x_return_status OUT NOCOPY VARCHAR2,
599 x_msg_count OUT NOCOPY NUMBER,
600 x_msg_data OUT NOCOPY VARCHAR2,
601 x_mr_item_instances OUT NOCOPY MR_ITEM_INSTANCE_TBL_TYPE,
602 p_cm_mr_chain_loop IN MR_CHAIN_LOOP_TBL_TYPE)
603 IS
604 itemInstanceIndex NUMBER := 1;
605 presentFlag CHAR(1) := 'N';
606 l_mr_item_inst_tbl AHL_FMP_PVT.MR_ITEM_INSTANCE_TBL_TYPE;
607 l_applicable_mr_tbl AHL_FMP_PVT.APPLICABLE_MR_TBL_TYPE;
608 l_debug VARCHAR2(1) := AHL_DEBUG_PUB.is_log_enabled;
609 l_api_name CONSTANT VARCHAR2(30) := 'GET_CM_AFFECTED_ITEMS';
610 l_api_version CONSTANT NUMBER := 1.0;
611 l_tempIndex NUMBER := 0;
612 l_return_status VARCHAR2(1);
613 l_msg_count NUMBER;
614 l_msg_data VARCHAR2(30);
615
616 BEGIN
617 SAVEPOINT GET_CM_AFFECTED_ITEMS_PVT;
618 IF l_debug = 'Y' THEN
619 AHL_DEBUG_PUB.enable_debug;
620 AHL_DEBUG_PUB.debug('Begin private API: AHL_MR_LOOP_CHAIN_RELNS_PVT.GET_CM_AFFECTED_ITEMS');
621 END IF;
622
623 -- Loop thru each MR in the chain/loop
624 FOR loopchainIndex IN p_cm_mr_chain_loop.FIRST..p_cm_mr_chain_loop.LAST
625 LOOP
626
627 -- Call procedure to get the affected items for a MR
628 -- JKJain, NR Analysis and Forecasting
629 AHL_FMP_PVT.GET_MR_AFFECTED_ITEMS( 1.0,NULL,NULL,NULL, l_return_Status, l_msg_count, l_msg_data, p_cm_mr_chain_loop(loopchainIndex).mr_header_id ,NULL,NULL,NULL, NULL,'N', SYSDATE, l_mr_item_inst_tbl);
630
631 IF l_tempIndex = 0 AND l_mr_item_inst_tbl.COUNT > 0 THEN
632 FOR itemIndex IN l_mr_item_inst_tbl.FIRST..l_mr_item_inst_tbl.LAST
633 LOOP
634 x_mr_item_instances(itemIndex).ITEM_NUMBER := l_mr_item_inst_tbl(itemIndex).ITEM_NUMBER;
635 x_mr_item_instances(itemIndex).SERIAL_NUMBER := l_mr_item_inst_tbl(itemIndex).SERIAL_NUMBER;
636 x_mr_item_instances(itemIndex).LOCATION := l_mr_item_inst_tbl(itemIndex).LOCATION;
637 x_mr_item_instances(itemIndex).STATUS := l_mr_item_inst_tbl(itemIndex).STATUS;
638 x_mr_item_instances(itemIndex).OWNER := l_mr_item_inst_tbl(itemIndex).OWNER;
639 x_mr_item_instances(itemIndex).CONDITION := l_mr_item_inst_tbl(itemIndex).CONDITION;
640 x_mr_item_instances(itemIndex).UNIT_NAME := l_mr_item_inst_tbl(itemIndex).UNIT_NAME;
641 x_mr_item_instances(itemIndex).ITEM_INSTANCE_ID := l_mr_item_inst_tbl(itemIndex).ITEM_INSTANCE_ID;
642 x_mr_item_instances(itemIndex).INVENTORY_ITEM_ID := l_mr_item_inst_tbl(itemIndex).INVENTORY_ITEM_ID;
643 x_mr_item_instances(itemIndex).MR_EFFECTIVITY_ID := l_mr_item_inst_tbl(itemIndex).MR_EFFECTIVITY_ID;
644 x_mr_item_instances(itemIndex).UC_HEADER_ID := l_mr_item_inst_tbl(itemIndex).UC_HEADER_ID;
645 END LOOP;
646 ELSE
647
648 IF x_mr_item_instances IS NULL OR x_mr_item_instances.COUNT = 0 THEN
649 EXIT;
650 END IF;
651
652 IF l_mr_item_inst_tbl IS NOT NULL AND l_mr_item_inst_tbl.COUNT > 0 THEN
653 -- For Each item in the common affected items list,
654 --check if the item exist in the affected item list of the current MR.
655 FOR i IN x_mr_item_instances.FIRST..x_mr_item_instances.LAST
656 LOOP
657 itemInstanceIndex := i;
658 FOR j IN l_mr_item_inst_tbl.FIRST..l_mr_item_inst_tbl.LAST
659 LOOP
660
661 IF l_mr_item_inst_tbl(j).item_instance_id = x_mr_item_instances(i).item_instance_id THEN
662 presentFlag := 'Y';
663 EXIT;
664 ELSE
665 presentFlag := 'N';
666 END IF;
667 END LOOP;
668
669 IF presentFlag = 'N' THEN
670 x_mr_item_instances.delete(itemInstanceIndex);
671 ELSE
672 presentFlag := 'N';
673 END IF;
674
675 END LOOP;
676 ELSE
677 x_mr_item_instances.DELETE;
678 EXIT;
679 END IF;
680 END IF;
681 l_tempIndex := l_tempIndex + 1;
682 END LOOP;
683 --If no common affected items exist, then delete the loop chain table
684 --IF x_mr_item_instances.COUNT = 0 THEN
685 -- x_cm_mr_chain_loop.DELETE;
686 --END IF;
687
688 IF l_debug = 'Y' THEN
689 AHL_DEBUG_PUB.enable_debug;
690 IF x_mr_item_instances.COUNT = 0 THEN
691 AHL_DEBUG_PUB.debug('AHL_MR_LOOP_CHAIN_RELNS_PVT.GET_CM_AFFECTED_ITEMS : No Common affected items');
692 END IF;
693 AHL_DEBUG_PUB.debug('END private API: AHL_MR_LOOP_CHAIN_RELNS_PVT.GET_CM_AFFECTED_ITEMS');
694 END IF;
695 EXCEPTION
696 WHEN OTHERS THEN
700 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'AHL_MR_LOOP_CHAIN_RELNS_PVT',
697 ROLLBACK TO GET_CM_AFFECTED_ITEMS_PVT;
698 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
699 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
701 p_procedure_name => 'GET_CM_AFFECTED_ITEMS',
702 p_error_text => SUBSTRB(SQLERRM,1,240));
703 END IF;
704 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
705 p_count => x_msg_count,
706 p_data => x_msg_data);
707 IF l_debug = 'Y' THEN
708 AHL_DEBUG_PUB.log_app_messages (x_msg_count, x_msg_data,
709 'OTHER ERROR IN PRIVATE:' );
710 AHL_DEBUG_PUB.debug('AHL_MR_LOOP_CHAIN_RELNS_PVT.GET_CM_AFFECTED_ITEMS', 'AHL_MR_LOOP_CHAIN_RELNS_PVT.GET_CM_AFFECTED_ITEMS');
711 AHL_DEBUG_PUB.disable_debug;
712 END IF;
713 END GET_CM_AFFECTED_ITEMS;
714
715 /**
716 Procedure to get the MRs and details in a LOOP/CHAIN relationship
717 **/
718
719 PROCEDURE GET_MR_LOOP_CHAIN_DTLS
720 (
721 p_start_mr_reln_id IN NUMBER,
722 p_relationship IN VARCHAR2,
723 p_mr_header_id IN NUMBER,
724 x_cm_mr_chain_loop OUT NOCOPY MR_CHAIN_LOOP_TBL_TYPE
725 )
726 IS
727 CURSOR mr_loop_chain_csr(c_start_mr_reln_id NUMBER) IS
728 SELECT *
729 FROM
730 (SELECT rel.sequence_number sequence_no ,
731 MR.mr_header_id,
732 MR.title,
733 MR_TL.description,
734 STATUS.MEANING status,
735 MR.revision,
736 MR.version_number,
737 MR.effective_from,
738 MR.effective_to,
739 rel.sequence_number,
740 rel.mr_relationship_id,
741 rel.relationship_code,
742 /* Modified for Bug# 12686413 - inactive MRs displayed in view loop/chain UI*/
743 (ROW_NUMBER() OVER( PARTITION BY rel.sequence_number ORDER BY rel.sequence_number, NVL(effective_to, SYSDATE) DESC, MR.mr_status_code)) orderSeq
744 FROM ahl_mr_headers_b MR,
745 ahl_mr_headers_tl MR_TL,
746 (SELECT LOOKUP_CODE,
747 MEANING
748 FROM FND_LOOKUP_VALUES
749 WHERE LOOKUP_TYPE = 'AHL_FMP_REVISION_STATUS'
750 AND LANGUAGE = USERENV('LANG')
751 ) STATUS,
752 AHL_MR_LOOP_CHAIN_RELNS rel
753 WHERE MR_TL.LANGUAGE = USERENV('LANG')
754 AND rel.start_mr_relationship_id = c_start_mr_reln_id
755 AND STATUS.LOOKUP_CODE = MR.mr_status_code
756 AND rel.mr_header_id = MR.mr_header_id
757 AND MR.mr_header_id = MR_TL.mr_header_id
758 AND TRUNC(MR.effective_from) <= SYSDATE
759 AND NVL(TRUNC(MR.effective_to), SYSDATE) >= SYSDATE
760 ORDER BY rel.sequence_number,
761 MR.effective_from
762 ) LoopChain
763 WHERE LoopChain.orderSeq = 1;
764
765
766 l_debug VARCHAR2(1) :=AHL_DEBUG_PUB.is_log_enabled;
767 l_api_name CONSTANT VARCHAR2(30) := 'GET_MR_LOOP_CHAIN_DTLS';
768 l_api_version CONSTANT NUMBER := 1.0;
769 l_chainindex NUMBER := 0;
770 l_debug_prefix VARCHAR2(50) := 'AHL_MR_LOOP_CHAIN_RELNS_PVT.GET_MR_LOOP_CHAIN_DTLS';
771 l_start_mr_reln_id NUMBER;
772 l_max_seq NUMBER;
773
774 BEGIN
775 IF l_debug = 'Y' THEN
776 AHL_DEBUG_PUB.enable_debug;
777 AHL_DEBUG_PUB.debug('Begin private API: AHL_MR_LOOP_CHAIN_RELNS_PVT.GET_MR_LOOP_CHAIN_DTLS');
778 END IF;
779
780 l_start_mr_reln_id := p_start_mr_reln_id;
781
782 IF l_start_mr_reln_id IS NULL THEN
783 l_start_mr_reln_id := get_valid_start_mr_rel(p_mr_header_id, NULL, NULL, NULL, NULL, SYSDATE, p_relationship);
784 END IF;
785
786 SELECT MAX(sequence_number) INTO l_max_seq FROM ahl_mr_loop_chain_relns WHERE start_mr_relationship_id = l_start_mr_reln_id;
787
788 FOR mr_loop_chain_rec IN mr_loop_chain_csr(l_start_mr_reln_id)
789 LOOP
790 IF((l_chainindex + 1) = mr_loop_chain_rec.sequence_no) THEN
791 x_cm_mr_chain_loop(l_chainindex).MR_HEADER_ID := mr_loop_chain_rec.mr_header_id;
792 x_cm_mr_chain_loop(l_chainindex).TITLE := mr_loop_chain_rec.title;
793 x_cm_mr_chain_loop(l_chainindex).DESCRIPTION := mr_loop_chain_rec.description;
794 x_cm_mr_chain_loop(l_chainindex).REVISION := mr_loop_chain_rec.revision;
795 x_cm_mr_chain_loop(l_chainindex).VERSION_NUMBER := mr_loop_chain_rec.version_number;
799 x_cm_mr_chain_loop(l_chainindex).MR_SEQUENCE := mr_loop_chain_rec.sequence_no;
796 x_cm_mr_chain_loop(l_chainindex).STATUS := mr_loop_chain_rec.status;
797 x_cm_mr_chain_loop(l_chainindex).EFFECTIVE_FROM := mr_loop_chain_rec.effective_from;
798 x_cm_mr_chain_loop(l_chainindex).EFFECTIVE_TO := mr_loop_chain_rec.effective_to;
800 x_cm_mr_chain_loop(l_chainindex).RELATIONSHIP := mr_loop_chain_rec.relationship_code;
801 ELSE
802 x_cm_mr_chain_loop.DELETE;
803 EXIT;
804 END IF;
805 l_chainindex := l_chainindex + 1;
806
807 END LOOP;
808
809 IF l_chainindex <> l_max_seq THEN
810 x_cm_mr_chain_loop.DELETE;
811 END IF;
812 IF l_debug = 'Y' THEN
813 AHL_DEBUG_PUB.enable_debug;
814 AHL_DEBUG_PUB.debug('GET_MR_LOOP_CHAIN_DTLS:: ' || x_cm_mr_chain_loop.COUNT);
815 END IF;
816 IF l_debug = 'Y' THEN
817 AHL_DEBUG_PUB.enable_debug;
818 AHL_DEBUG_PUB.debug('End private API: AHL_MR_LOOP_CHAIN_RELNS_PVT.GET_MR_LOOP_CHAIN_DTLS');
819 END IF;
820
821 END GET_MR_LOOP_CHAIN_DTLS;
822
823 -- Define procedure get_ucHeader, get the unit_config_header_id and unit name for
824 -- a given item_instance_id
825 PROCEDURE get_ucHeader (p_item_instance_id IN NUMBER,
826 x_ucHeaderID OUT NOCOPY NUMBER,
827 x_unitName OUT NOCOPY VARCHAR2)
828 IS
829 -- Get ucHeader for component
830 CURSOR get_unit_name_com (p_item_instance_id IN NUMBER) IS
831 SELECT unit_config_header_id, name
832 FROM ahl_unit_config_headers
833 WHERE csi_item_instance_id IN ( SELECT object_id
834 FROM csi_ii_relationships
835 START WITH subject_id = p_item_instance_id
836 AND relationship_type_code = 'COMPONENT-OF'
837 AND sysdate between trunc(nvl(active_start_date,sysdate))
838 AND trunc(nvl(active_end_date, SYSDATE+1))
839 CONNECT BY subject_id = PRIOR object_id
840 AND relationship_type_code = 'COMPONENT-OF'
841 AND sysdate between trunc(nvl(active_start_date,sysdate))
842 AND trunc(nvl(active_end_date, SYSDATE+1))
843 )
844 AND sysdate between trunc(nvl(active_start_date,sysdate))
845 AND trunc(nvl(active_end_date, SYSDATE+1));
846
847 -- Get ucHeader for top node
848 CURSOR get_unit_name_top (p_item_instance_id IN NUMBER) IS
849 SELECT unit_config_header_id, name
850 FROM ahl_unit_config_headers
851 WHERE csi_item_instance_id = p_item_instance_id
852 AND sysdate between trunc(nvl(active_start_date,sysdate))
853 AND trunc(nvl(active_end_date, SYSDATE+1));
854
855 l_get_unit_name_com get_unit_name_com%ROWTYPE;
856 l_get_unit_name_top get_unit_name_top%ROWTYPE;
857 l_debug VARCHAR2(1) :=AHL_DEBUG_PUB.is_log_enabled;
858 BEGIN
859 --Check for top node.
860 OPEN get_unit_name_top(p_item_instance_id);
861 FETCH get_unit_name_top INTO l_get_unit_name_top;
862 IF (get_unit_name_top%NOTFOUND) THEN
863 -- Check for component.
864 OPEN get_unit_name_com(p_item_instance_id);
865 FETCH get_unit_name_com INTO l_get_unit_name_com;
866 IF (get_unit_name_com%NOTFOUND) THEN
867 x_ucHeaderID := NULL;
868 x_unitName := NULL;
869 ELSE
870 x_ucHeaderID := l_get_unit_name_com.unit_config_header_id;
871 x_unitName := l_get_unit_name_com.name;
872 END IF;
873 CLOSE get_unit_name_com;
874 ELSE
875 x_ucHeaderID := l_get_unit_name_top.unit_config_header_id;
876 x_unitName := l_get_unit_name_top.name;
877 END IF;
878 CLOSE get_unit_name_top;
879
880 END get_ucHeader;
881
882 /**
883 Procedure to get the list of currently active Loop/Chain for View Loop/Chain Functionaliy
884 */
885 PROCEDURE VIEW_MR_LOOP_CHAIN
886 (
890 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
887 p_api_version IN NUMBER,
888 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
889 p_commit IN VARCHAR2 := FND_API.G_FALSE,
891 x_return_status OUT NOCOPY VARCHAR2,
892 x_msg_count OUT NOCOPY NUMBER,
893 x_msg_data OUT NOCOPY VARCHAR2,
894 p_start_mr_reln_id IN NUMBER,
895 p_relationship IN VARCHAR2,
896 p_mr_header_id IN NUMBER,
897 p_ue_id IN NUMBER,
898 p_calling_module IN VARCHAR2,
899 p_item_instance_id IN NUMBER,
900 x_mr_item_instances OUT NOCOPY MR_ITEM_INSTANCE_TBL_TYPE,
901 x_cm_mr_chain_loop OUT NOCOPY MR_CHAIN_LOOP_TBL_TYPE
902 )
903 IS
904
905 CURSOR ue_details_csr(c_ue_id NUMBER, c_start_ue_id NUMBER) IS
906 SELECT MR.mr_header_id,
907 MR.title,
908 MR_TL.description,
909 STATUS.MEANING status,
910 MR.revision,
911 MR.version_number,
912 MR.effective_from,
913 MR.effective_to,
914 eff.loop_chain_seq_num,
915 eff.accomplish_trigger_type relationship
916 FROM ahl_unit_effectivities_b eff,
917 (SELECT LOOKUP_CODE,
918 MEANING
919 FROM FND_LOOKUP_VALUES
920 WHERE LOOKUP_TYPE = 'AHL_FMP_REVISION_STATUS'
921 AND LANGUAGE = USERENV('LANG')
922 ) STATUS,
923 ahl_mr_headers_b MR,
924 ahl_mr_headers_tl MR_TL
925 WHERE eff.mr_header_id = MR.mr_header_id
926 AND MR_TL.LANGUAGE = USERENV('LANG')
927 AND status.lookup_code = MR.mr_status_code
928 AND MR.mr_header_id = MR_TL.mr_header_id
929 AND (eff.start_lc_ue_id = c_start_ue_id
930 /*Commented because, BUE populates start_lc_ue_id for the first sequence also
931 OR (eff.unit_effectivity_id = c_start_ue_id AND loop_chain_seq_num = 1)*/
932 )
933 AND nvl(effective_to, SYSDATE + 1) > SYSDATE
934 ORDER BY loop_chain_seq_num;
935
936 CURSOR get_inst_attributes_csr(c_item_instance_id NUMBER) IS
937
938 SELECT ii.serial_number,
939 (select kfv.concatenated_segments from mtl_system_items_kfv kfv
940 where kfv.inventory_item_id = ii.inventory_item_id
941 AND kfv.organization_id = ii.inv_master_organization_id) item_number,
942 ii.inventory_item_id,
943 ahl_util_uc_pkg.getcsi_locationDesc(ii.location_id, ii.location_type_code,
944 ii.inv_organization_id, ii.inv_subinventory_name,
945 ii.inv_locator_id, ii.wip_job_id) Location_description,
946 (select f.meaning from csi_lookups f where ii.instance_usage_code = f.lookup_code
947 AND f.lookup_type = 'CSI_INSTANCE_USAGE_CODE') Status,
948 (select p.party_name from csi_inst_party_details_v p
949 where p.instance_id = ii.instance_id and p.relationship_type_code = 'OWNER') owner_name,
950 (select mat.description from mtl_material_statuses mat where ii.INSTANCE_CONDITION_ID = mat.status_id) condition
951 FROM csi_item_instances ii
952 WHERE ii.instance_id = c_item_instance_id;
953
954 l_sequence NUMBER := 0;
955 l_debug VARCHAR2(1) :=AHL_DEBUG_PUB.is_log_enabled;
956 l_api_name CONSTANT VARCHAR2(30) := 'VALIDATE_LOOP_CHAIN';
957 l_api_version CONSTANT NUMBER := 1.0;
958 l_debug_prefix VARCHAR2(50) := 'AHL_MR_LOOP_CHAIN_RELNS_PVT.VIEW_MR_LOOP_CHAIN';
959 l_start_ue_id NUMBER;
960
961 BEGIN
962 IF l_debug = 'Y' THEN
963 AHL_DEBUG_PUB.enable_debug;
964 AHL_DEBUG_PUB.debug('Begin private API: AHL_MR_LOOP_CHAIN_RELNS_PVT.VIEW_MR_LOOP_CHAIN');
965 END IF;
966 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version, p_api_version,
967 l_api_name, G_PKG_NAME)
968 THEN
969 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
970 END IF;
971
972 IF FND_API.to_boolean(p_init_msg_list) THEN
973 FND_MSG_PUB.initialize;
974 END IF;
975
976 --Loop through ahl_mr_relationships to find the relationship
977 IF p_calling_module IS NOT NULL AND p_calling_module = 'UMP'
978 THEN
979 SELECT start_lc_ue_id INTO l_start_ue_id FROM ahl_unit_effectivities_b WHERE unit_effectivity_id = p_ue_id;
980
981 IF l_start_ue_id IS NULL THEN
982 l_start_ue_id := p_ue_id;
983 END IF;
984
985 FOR relationRec IN ue_details_csr(p_ue_id, l_start_ue_id)
986 LOOP
987 x_cm_mr_chain_loop(l_sequence).MR_SEQUENCE := relationRec.loop_chain_seq_num;
988 x_cm_mr_chain_loop(l_sequence).mr_header_id := relationRec.mr_header_id;
989 x_cm_mr_chain_loop(l_sequence).title := relationRec.title;
993 x_cm_mr_chain_loop(l_sequence).version_number:= relationRec.version_number;
990 x_cm_mr_chain_loop(l_sequence).description:= relationRec.description;
991 x_cm_mr_chain_loop(l_sequence).status:= relationRec.status;
992 x_cm_mr_chain_loop(l_sequence).revision:= relationRec.revision;
994 x_cm_mr_chain_loop(l_sequence).effective_from:= relationRec.effective_from;
995 x_cm_mr_chain_loop(l_sequence).effective_to:= relationRec.effective_to;
996 x_cm_mr_chain_loop(l_sequence).relationship := relationRec.relationship;
997 l_sequence := l_sequence + 1;
998 END LOOP;
999
1000 ELSE
1001
1002 GET_MR_LOOP_CHAIN_DTLS(p_start_mr_reln_id, p_relationship, p_mr_header_id, x_cm_mr_chain_loop);
1003
1004 END IF;
1005
1006 IF x_cm_mr_chain_loop IS NOT NULL AND x_cm_mr_chain_loop.COUNT > 0 AND p_calling_module IS NOT NULL
1007 THEN
1008
1009 IF p_calling_module = 'FMP'
1010 THEN
1011
1012 GET_CM_AFFECTED_ITEMS(1.0,FND_API.G_TRUE,FND_API.G_TRUE,NULL, x_return_status, x_msg_count, x_msg_data, x_mr_item_instances, x_cm_mr_chain_loop);
1013
1014 ELSIF p_calling_module = 'UMP'
1015 THEN
1016
1017 OPEN get_inst_attributes_csr(p_item_instance_id);
1018 FETCH get_inst_attributes_csr INTO
1019 x_mr_item_instances(0).SERIAL_NUMBER,
1020 x_mr_item_instances(0).ITEM_NUMBER,
1021 x_mr_item_instances(0).INVENTORY_ITEM_ID,
1022 x_mr_item_instances(0).LOCATION,
1023 x_mr_item_instances(0).STATUS,
1024 x_mr_item_instances(0).OWNER,
1025 x_mr_item_instances(0).CONDITION;
1026
1027 CLOSE get_inst_attributes_csr;
1028
1029 get_ucHeader(p_item_instance_id,
1030 x_ucHeaderID => x_mr_item_instances(0).UC_HEADER_ID,
1031 x_unitName => x_mr_item_instances(0).UNIT_NAME);
1032
1033 END IF;
1034 END IF;
1035 -- store the details to the temp tables.
1036 DELETE FROM ahl_mr_instances_temp;
1037 IF x_mr_item_instances IS NOT NULL AND x_mr_item_instances.COUNT > 0
1038 THEN
1039 FOR i IN x_mr_item_instances.FIRST..x_mr_item_instances.LAST
1040 LOOP
1041 IF(x_mr_item_instances.EXISTS(i)) THEN
1042 INSERT INTO ahl_mr_instances_temp
1043 (
1044 MR_INSTANCE_TEMP_ID,
1045 MR_EFFECTIVITY_ID,
1046 ITEM_INSTANCE_ID,
1047 SERIAL_NUMBER,
1048 ITEM_NUMBER,
1049 INVENTORY_ITEM_ID,
1050 LOCATION,
1051 STATUS,
1052 OWNER,
1053 CONDITION,
1054 UNIT_NAME,
1055 UC_HEADER_ID
1056 )
1057 VALUES
1058 (
1059 i,
1060 x_mr_item_instances(i).mr_effectivity_id,
1061 x_mr_item_instances(i).item_instance_id,
1062 x_mr_item_instances(i).serial_number,
1063 x_mr_item_instances(i).item_number,
1064 x_mr_item_instances(i).inventory_item_id,
1065 x_mr_item_instances(i).location,
1066 x_mr_item_instances(i).status,
1067 x_mr_item_instances(i).owner,
1068 x_mr_item_instances(i).condition,
1069 x_mr_item_instances(i).UNIT_NAME,
1070 x_mr_item_instances(i).UC_HEADER_ID
1071 );
1072 END IF;
1073 END LOOP;
1074 END IF;
1075 IF l_debug = 'Y' THEN
1076 AHL_DEBUG_PUB.enable_debug;
1077 AHL_DEBUG_PUB.debug('End private API: AHL_MR_LOOP_CHAIN_RELNS_PVT.VIEW_MR_LOOP_CHAIN');
1078 END IF;
1079
1080 EXCEPTION
1081 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1082 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1083 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1084 p_count => x_msg_count,
1085 p_data => x_msg_data);
1086 IF l_debug = 'Y' THEN
1087 AHL_DEBUG_PUB.log_app_messages(x_msg_count, x_msg_data,
1088 'UNEXPECTED ERROR IN PRIVATE:' );
1089 AHL_DEBUG_PUB.debug('AHL_MR_LOOP_CHAIN_RELNS_PVT.VIEW_MR_LOOP_CHAIN', l_debug_prefix);
1090 AHL_DEBUG_PUB.disable_debug;
1091 END IF;
1092
1093 WHEN FND_API.G_EXC_ERROR THEN
1094 x_return_status := FND_API.G_RET_STS_ERROR;
1095 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1096 p_count => x_msg_count,
1097 p_data => x_msg_data);
1098 IF l_debug = 'Y' THEN
1099 AHL_DEBUG_PUB.log_app_messages(x_msg_count, x_msg_data,
1100 'ERROR IN PRIVATE:' );
1101 AHL_DEBUG_PUB.debug('AHL_MR_LOOP_CHAIN_RELNS_PVT.VIEW_MR_LOOP_CHAIN', l_debug_prefix);
1102 AHL_DEBUG_PUB.disable_debug;
1103 END IF;
1104
1105 WHEN OTHERS THEN
1106 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1107 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1108 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'AHL_MR_LOOP_CHAIN_RELNS_PVT',
1109 p_procedure_name => 'VIEW_MR_LOOP_CHAIN',
1110 p_error_text => SUBSTRB(SQLERRM,1,240));
1111 END IF;
1112 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1113 p_count => x_msg_count,
1114 p_data => x_msg_data);
1115 IF l_debug = 'Y' THEN
1116 AHL_DEBUG_PUB.log_app_messages (x_msg_count, x_msg_data,
1120 END IF;
1117 'OTHER ERROR IN PRIVATE:' );
1118 AHL_DEBUG_PUB.debug('AHL_MR_LOOP_CHAIN_RELNS_PVT.VIEW_MR_LOOP_CHAIN', l_debug_prefix);
1119 AHL_DEBUG_PUB.disable_debug;
1121
1122
1123
1124 end view_mr_loop_chain;
1125
1126 PROCEDURE get_valid_loop_chain_relns(
1127 p_mr_header_id IN NUMBER,
1128 p_effective_from IN DATE,
1129 p_effective_to IN DATE,
1130 p_rel_code IN VARCHAR2,
1131 x_return_status OUT NOCOPY VARCHAR2,
1132 x_msg_count OUT NOCOPY NUMBER,
1133 x_msg_data OUT NOCOPY VARCHAR2,
1134 x_start_mr_relns OUT NOCOPY MR_CHAIN_LOOP_TBL_TYPE)
1135 IS
1136 CURSOR get_loop_chain_relns(c_mr_header_id NUMBER,c_effective_from DATE, c_effective_to DATE, c_rel_code VARCHAR2)
1137 IS
1138 SELECT rel.mr_header_id,
1139 rel.start_mr_relationship_id,
1140 rel.sequence_number,
1141 rel.relationship_code,
1142 mr.mr_status_code,
1143 mr.effective_from,
1144 mr.effective_to
1145 FROM ahl_mr_loop_chain_relns rel,
1146 ahl_mr_headers_b mr
1147 WHERE mr.mr_header_id = rel.mr_header_id
1148 AND rel.start_mr_relationship_id IN
1149 (SELECT reln.start_mr_relationship_id
1150 FROM ahl_mr_loop_chain_relns reln
1151 WHERE reln.mr_header_id = c_mr_header_id
1152 AND (c_rel_code IS NULL
1153 OR reln.relationship_code = c_rel_code)
1154 )
1155 AND TRUNC(mr.effective_from) <= TRUNC(c_effective_from)
1156 AND TRUNC(NVL(mr.effective_to,sysdate+1)) > TRUNC(c_effective_to)
1157 AND mr.mr_status_code = 'COMPLETE'
1158 AND mr.repetitive_flag = 'Y'
1159 ORDER BY rel.start_mr_relationship_id,
1160 rel.sequence_number;
1161
1162 l_sequence NUMBER := 1;
1163 l_valid NUMBER := 1;
1164 l_loop_index NUMBER := 0;
1165 l_mr_present CHAR(1) := 'N';
1166 l_start_mr_rel_id NUMBER;
1167 l_skip_mr_rel_id NUMBER;
1168 l_debug VARCHAR2(1) :=ahl_debug_pub.is_log_enabled;
1169 l_mr_rel_count NUMBER := 0;
1170 l_max_sequence NUMBER := 0;
1171 l_skip_sequence NUMBER := 0;
1172 l_debug_prefix VARCHAR2(70) := 'AHL_MR_LOOP_CHAIN_RELNS_PVT.get_valid_loop_chain_relns';
1173 BEGIN
1174
1175 IF l_debug = 'Y' THEN
1176 AHL_DEBUG_PUB.enable_debug;
1177 AHL_DEBUG_PUB.debug('Begin private API: AHL_MR_LOOP_CHAIN_RELNS_PVT.get_valid_loop_chain_relns');
1178 END IF;
1179
1180 FOR l_loop_chain_rec IN get_loop_chain_relns(p_mr_header_id, p_effective_from, p_effective_to, p_rel_code)
1181 LOOP
1182 IF l_skip_mr_rel_id IS NULL OR (l_skip_mr_rel_id <> l_loop_chain_rec.start_mr_relationship_id) THEN
1183 IF l_loop_chain_rec.sequence_number = 1 AND l_valid = 1 AND l_loop_index <> 0 AND l_mr_rel_count <= l_max_sequence AND l_mr_present = 'Y' THEN
1184 EXIT;
1185 ELSE
1186 IF l_loop_chain_rec.sequence_number = 1 THEN
1187 x_start_mr_relns.DELETE;
1188 l_sequence := 1;
1189 l_start_mr_rel_id := l_loop_chain_rec.start_mr_relationship_id;
1190 x_start_mr_relns(0).mr_header_id := l_loop_chain_rec.mr_header_id;
1191 x_start_mr_relns(0).mr_sequence := l_loop_chain_rec.sequence_number;
1192 l_mr_present := 'Y';
1193 l_skip_sequence := 0;
1194
1195 SELECT MAX(sequence_number)
1196 INTO l_mr_rel_count
1197 FROM ahl_mr_loop_chain_relns
1198 WHERE start_mr_relationship_id = l_start_mr_rel_id;
1199
1200 ELSE
1201 IF l_loop_chain_rec.sequence_number > l_sequence THEN
1202 l_sequence := 0;
1203 l_valid := 0;
1204 l_skip_mr_rel_id := l_loop_chain_rec.start_mr_relationship_id;
1205 l_start_mr_rel_id := NULL;
1206 x_start_mr_relns.DELETE;
1207 ELSE
1208 IF l_skip_sequence <> l_loop_chain_rec.sequence_number
1209 THEN
1210 l_valid := 1;
1211 l_max_sequence := l_loop_chain_rec.sequence_number;
1212 x_start_mr_relns(l_loop_chain_rec.sequence_number - 1).mr_header_id := l_loop_chain_rec.mr_header_id;
1213 x_start_mr_relns(l_loop_chain_rec.sequence_number - 1).mr_sequence := l_loop_chain_rec.sequence_number;
1214
1215 IF p_mr_header_id = l_loop_chain_rec.mr_header_id THEN
1216 l_mr_present := 'Y';
1217 l_skip_sequence := l_loop_chain_rec.sequence_number;
1218 END IF;
1219
1220 END IF;
1221 END IF;
1222
1223 END IF;
1224
1225 END IF;
1226
1227 IF l_loop_chain_rec.sequence_number = l_sequence THEN
1228 l_sequence := l_sequence + 1;
1229 END IF;
1230
1231 END IF;
1232 l_loop_index := l_loop_index + 1;
1233 END LOOP;
1234
1235 IF l_valid = 1 AND (l_mr_rel_count > l_max_sequence OR l_mr_present = 'N') THEN
1236 l_start_mr_rel_id := NULL;
1237 x_start_mr_relns.DELETE;
1238 END IF;
1239
1240 IF l_debug = 'Y' THEN
1241 ahl_debug_pub.enable_debug;
1242 ahl_debug_pub.debug('End private API: AHL_MR_LOOP_CHAIN_RELNS_PVT.get_valid_loop_chain_relns');
1243 END IF;
1244
1245 EXCEPTION
1246 WHEN OTHERS THEN
1247 x_return_status := FND_API.G_RET_STS_ERROR;
1248 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE, p_count => x_msg_count, p_data => x_msg_data);
1249
1250 IF l_debug = 'Y' THEN
1251 AHL_DEBUG_PUB.log_app_messages(x_msg_count, x_msg_data, 'ERROR IN PRIVATE:' );
1252 AHL_DEBUG_PUB.DEBUG('AHL_MR_LOOP_CHAIN_RELNS_PVT.VIEW_MR_LOOP_CHAIN', l_debug_prefix);
1253 AHL_DEBUG_PUB.disable_debug;
1254 END IF;
1255
1256 END get_valid_loop_chain_relns;
1257
1258 function IS_MR_IN_LOOP_OR_CHAIN (P_MR_HEADER_ID in number )
1259 RETURN VARCHAR2 IS
1260
1261 l_result VARCHAR2(5);
1262 l_count NUMBER;
1263 l_start_mr NUMBER;
1264
1265 --Changes for Bug#12837789 Begins
1266 l_valid_flag CHAR(1);
1267 CURSOR get_is_mr_valid(p_mr_header_id_csr NUMBER)
1268 IS
1269 SELECT 'X' FROM ahl_mr_headers_b WHERE mr_header_id = p_mr_header_id_csr AND
1270 NVL(effective_to, SYSDATE + 1) > SYSDATE;
1271
1272 --Changes for Bug#12837789 Ends
1273 begin
1274 l_result := 'NONE';
1275 --Changes for Bug#12837789 Begins
1276 OPEN get_is_mr_valid(P_MR_HEADER_ID);
1277 FETCH get_is_mr_valid INTO l_valid_flag;
1278
1279 IF get_is_mr_valid%NOTFOUND THEN
1280 RETURN l_result;
1281 END IF;
1282 CLOSE get_is_mr_valid;
1283 --Changes for Bug#12837789 Ends
1284 l_start_mr := get_valid_start_mr_rel(P_MR_HEADER_ID, NULL, NULL, NULL, NULL, SYSDATE, 'LOOP');
1285
1286 IF l_start_mr IS NOT NULL
1287 THEN
1288 l_result := 'LOOP';
1289 ELSE
1290 l_start_mr := get_valid_start_mr_rel(P_MR_HEADER_ID, NULL, NULL, NULL, NULL, SYSDATE, 'CHAIN');
1291
1292 IF l_start_mr IS NOT NULL
1293 THEN
1294 l_result := 'CHAIN';
1295 END IF;
1296
1297 END IF;
1298
1299 RETURN l_result ;
1300
1301 end IS_MR_IN_LOOP_OR_CHAIN;
1302
1303 FUNCTION IS_MR_IN_LOOP(p_mr_header_id IN NUMBER )
1304 RETURN VARCHAR2 IS
1305
1306 l_result VARCHAR2(10);
1307 l_start_mr NUMBER;
1308 --Changes for Bug#12837789 Begins
1309 l_valid_flag CHAR(1);
1310 CURSOR get_is_mr_valid(p_mr_header_id_csr NUMBER)
1311 IS
1312 SELECT 'X' FROM ahl_mr_headers_b WHERE mr_header_id = p_mr_header_id_csr AND
1313 NVL(effective_to, SYSDATE + 1) > SYSDATE;
1314
1315 --Changes for Bug#12837789 Ends
1316
1317 begin
1318 l_result := 'true';
1319
1320 --Changes for Bug#12837789 Begins
1321 OPEN get_is_mr_valid(P_MR_HEADER_ID);
1322 FETCH get_is_mr_valid INTO l_valid_flag;
1323
1324 IF get_is_mr_valid%NOTFOUND THEN
1325 RETURN 'false';
1326 END IF;
1327 CLOSE get_is_mr_valid;
1328 --Changes for Bug#12837789 Ends
1329
1330 l_start_mr := get_valid_start_mr_rel(p_mr_header_id, NULL, NULL, NULL, NULL, SYSDATE, 'LOOP');
1331
1332 IF l_start_mr IS NOT NULL
1333 THEN
1334 l_result := 'false';
1335 END IF;
1336
1337 RETURN l_result ;
1338
1339 END IS_MR_IN_LOOP;
1340
1341
1342 FUNCTION IS_MR_IN_CHAIN(p_mr_header_id IN NUMBER )
1343 RETURN VARCHAR2 IS
1344
1345 l_result VARCHAR2(10);
1346 l_start_mr NUMBER;
1347 --Changes for Bug#12837789 Begins
1348 l_valid_flag CHAR(1);
1349 CURSOR get_is_mr_valid(p_mr_header_id_csr NUMBER)
1350 IS
1351 SELECT 'X' FROM ahl_mr_headers_b WHERE mr_header_id = p_mr_header_id_csr AND
1352 NVL(effective_to, SYSDATE + 1) > SYSDATE;
1353
1354 --Changes for Bug#12837789 Ends
1355
1356 begin
1357 l_result := 'true';
1358
1359 --Changes for Bug#12837789 Begins
1360 OPEN get_is_mr_valid(P_MR_HEADER_ID);
1361 FETCH get_is_mr_valid INTO l_valid_flag;
1362
1363 IF get_is_mr_valid%NOTFOUND THEN
1364 RETURN 'false';
1365 END IF;
1366 CLOSE get_is_mr_valid;
1367 --Changes for Bug#12837789 Ends
1368
1369 l_start_mr := get_valid_start_mr_rel(p_mr_header_id, NULL, NULL, NULL, NULL, SYSDATE, 'CHAIN');
1370
1371 IF l_start_mr IS NOT NULL
1372 THEN
1373 l_result := 'false';
1374 END IF;
1375
1376 RETURN l_result ;
1377
1378 END IS_MR_IN_CHAIN;
1379
1380 END AHL_MR_LOOP_CHAIN_RELNS_PVT;
[Home] [Help]