1 PACKAGE BODY AHL_FMP_PVT AS
2 /* $Header: AHLVFMPB.pls 120.9.12010000.2 2008/12/27 01:04:51 sracha ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):= 'AHL_FMP_PVT';
5 G_APPLN_USAGE VARCHAR2(30):=FND_PROFILE.VALUE('AHL_APPLN_USAGE');
6 --Define Global Cursor: get item instance attributes from ahl_unit_installed_details_v.
7 --The last two attributes: manufacturer_id and counctry_code are not in the view definition.
8 CURSOR get_inst_attri(c_item_instance_id NUMBER) IS
9 SELECT serial_number, item_number, inventory_item_id,
10 location_description, status, owner_name, condition,
11 mfg_date, 'm' manufacturer_id, 'c' country_code
12 FROM ahl_unit_installed_details_v
13 WHERE csi_item_instance_id = c_item_instance_id;
14
15
16 --Check whether the given item instance exists
17 CURSOR check_instance_exists(c_item_instance_id NUMBER) IS
18 SELECT instance_id
19 FROM csi_item_instances
20 WHERE instance_id = c_item_instance_id
21 AND SYSDATE between nvl(active_start_date,sysdate) and NVL(active_end_date,sysdate+1);
22
23
24 --Get Inventory Item ID for a given item instance
25 CURSOR get_inventory_item(c_item_instance_id NUMBER) IS
26 SELECT inventory_item_id
27 FROM csi_item_instances
28 WHERE instance_id = c_item_instance_id
29 AND SYSDATE between nvl(active_start_date,sysdate) and NVL(active_end_date,sysdate+1);
30
31 -- Declare Local Function CHECK_SN_INSIDE --
32
33 FUNCTION CHECK_SN_INSIDE(
34 p_sn IN VARCHAR2,
35 p_sn1 IN VARCHAR2,
36 p_sn2 IN VARCHAR2
37 ) RETURN BOOLEAN;
38
39 -- Declare Local Function CHECK_SN_OUTSIDE --
40 FUNCTION CHECK_SN_OUTSIDE(
41 p_sn IN VARCHAR2,
42 p_sn1 IN VARCHAR2,
43 p_sn2 IN VARCHAR2
44 ) RETURN BOOLEAN;
45
46 -- Declare Local Function CHECK_EFFECTIVITY_DETAILS --
47 FUNCTION CHECK_EFFECTIVITY_DETAILS(
48 p_item_instance_id IN NUMBER,
49 p_mr_effectivity_id IN NUMBER
50 ) RETURN BOOLEAN;
51
52 -- Declare local procedure GET_UCHEADER --
53 PROCEDURE get_ucHeader(
54 p_item_instance_id IN NUMBER,
55 x_ucHeaderID OUT NOCOPY NUMBER,
56 x_unitName OUT NOCOPY VARCHAR2);
57
58 FUNCTION get_topInstanceID(p_item_instance_id IN NUMBER) RETURN NUMBER;
59
60 -- Define Procedure GET_MR_AFFECTED_ITEMS --
61 PROCEDURE GET_MR_AFFECTED_ITEMS(
62 p_api_version IN NUMBER,
63 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
64 p_commit IN VARCHAR2 := FND_API.G_FALSE,
65 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
66 x_return_status OUT NOCOPY VARCHAR2,
67 x_msg_count OUT NOCOPY NUMBER,
68 x_msg_data OUT NOCOPY VARCHAR2,
69 p_mr_header_id IN NUMBER,
70 p_mr_effectivity_id IN NUMBER := NULL,
71 p_top_node_flag IN VARCHAR2 := 'N',
72 p_unique_inst_flag IN VARCHAR2 := 'N',
73 p_sort_flag IN VARCHAR2 := 'N',
74 x_mr_item_inst_tbl OUT NOCOPY MR_ITEM_INSTANCE_TBL_TYPE
75 ) IS
76 l_api_name CONSTANT VARCHAR2(30) := 'GET_MR_AFFECTED_ITEMS';
77 l_api_version CONSTANT NUMBER := 1.0;
78 l_msg_count NUMBER;
79 l_return_status VARCHAR2(1);
80 l_msg_data VARCHAR2(30);
81 l_mr_header_id NUMBER;
82 l_index NUMBER;
83 i NUMBER;
84 l_inst_attri get_inst_attri%ROWTYPE;
85 l_debug VARCHAR2(1) :=AHL_DEBUG_PUB.is_log_enabled;
86 l_error_flag VARCHAR2(1) :='N';
87 --check whether the given mr exists
88 CURSOR check_mr_exists(c_mr_header_id number)
89 IS
90 SELECT mr_header_id
91 FROM ahl_mr_headers_app_v
92 WHERE mr_header_id = c_mr_header_id;
93 --check whether the given mr_effecitivity_id exists
94 CURSOR check_mr_effect(c_mr_effectivity_id number ,c_mr_header_id number)
95 IS
96 SELECT mr_header_id, mr_effectivity_id, inventory_item_id,relationship_id, pc_node_id
97 FROM ahl_mr_effectivities_app_v
98 WHERE mr_effectivity_id = NVL(c_mr_effectivity_id,mr_effectivity_id)
99 AND mr_header_id = c_mr_header_id;
100 l_mr_effect check_mr_effect%ROWTYPE;
101
102 --get all the MR effectivity definitions for a given MR(Actually this cursor returns
103 --the only specified mr_effecitivity_id if it is not null, otherwise it returns
104 --all the mr_effectivity_id's for the given MR).
105 CURSOR get_mr_effect(c_mr_header_id NUMBER, c_mr_effectivity_id NUMBER)
106 IS
107 SELECT mr_header_id, mr_effectivity_id, inventory_item_id,relationship_id, pc_node_id
108 FROM ahl_mr_effectivities_app_v
109 WHERE mr_header_id = c_mr_header_id
110 AND mr_effectivity_id = NVL(c_mr_effectivity_id, mr_effectivity_id);
111 --get distinct item instances from the global temporary table
112 CURSOR get_dist_inst
113 IS
114 SELECT DISTINCT item_instance_id, serial_number, item_number,
115 inventory_item_id, location, status, owner, condition
116 FROM ahl_mr_instances_temp;
117 --get all item instances from the global temporary table
118 CURSOR get_all_inst
119 IS
120 SELECT mr_effectivity_id, item_instance_id, serial_number, item_number,
121 inventory_item_id, location, status, owner, condition
122 FROM ahl_mr_instances_temp;
123 --get distinct item instances from the global temporary table and sort them.
124 CURSOR get_dist_sort_inst
125 IS
126 SELECT DISTINCT item_instance_id, serial_number, item_number,
127 inventory_item_id, location, status, owner, condition
128 FROM ahl_mr_instances_temp
129 ORDER BY item_number, serial_number;
130 --get unit_name and uc_header_id for an item instance
131 /*
132 CURSOR get_uc_header(c_instance_id NUMBER) IS
133 SELECT unit_config_header_id, name
134 FROM ahl_unit_config_headers A
135 WHERE csi_item_instance_id = c_instance_id
136 OR EXISTS (SELECT 'X'
137 FROM csi_ii_relationships B
138 WHERE B.object_id = A.csi_item_instance_id
139 START WITH subject_id = c_instance_id
140 CONNECT BY subject_id = PRIOR object_id);
141 l_get_uc_header get_uc_header%ROWTYPE;
142 */
143 --only inventory item defined in MR effectivity definition, just top node
144 /*CURSOR get_top_inst1(c_inventory_item_id NUMBER)
145 IS
146 SELECT instance_id
147 FROM csi_item_instances A
148 WHERE inventory_item_id = c_inventory_item_id
149 AND SYSDATE between trunc(nvl(A.active_start_date,sysdate)) and trunc(nvl(a.active_end_date,sysdate+1))
150 AND NOT EXISTS (SELECT 'X'
151 FROM csi_ii_relationships B
152 WHERE B.subject_id = A.instance_id
153 AND relationship_type_code = 'COMPONENT-OF'
154 AND SYSDATE between trunc(nvl(B.active_start_date,sysdate)) and trunc(NVL(b.active_end_date,sysdate+1))
155 );*/
156 --same as before, but include all nodes, not only top node
157
158 CURSOR get_inst1(c_inventory_item_id NUMBER)
159 IS
160 SELECT instance_id
161 FROM csi_item_instances
162 WHERE inventory_item_id = c_inventory_item_id
163 AND SYSDATE between trunc(nvl(active_start_date,sysdate)) and trunc(nvl(active_end_date,sysdate+1));
164
165 --only position in MC defined in MR effectivity definition, just UC top node
166
167 /*CURSOR get_top_inst2(c_relationship_id NUMBER)
168 IS
169 SELECT a.csi_item_instance_id instance_id
170 FROM ahl_unit_config_headers a,ahl_applicable_instances b
171 WHERE a.csi_item_instance_id=b.csi_item_instance_id
172 and a.master_config_id=b.position_id
173 AND b.position_id=c_relationship_id
174 and SYSDATE between trunc(nvl(A.active_start_date,sysdate)) and trunc(nvl(a.active_end_date,sysdate+1))
175 and not exists (SELECT 'X'
176 FROM csi_ii_relationships
177 WHERE subject_id=b.csi_item_instance_id
178 AND relationship_type_code='COMPONENT_OF');*/
179
180
181 --same as before, but include all nodes, not only top node
182 CURSOR get_inst2(c_relationship_id NUMBER)
183 IS
184 SELECT a.csi_item_instance_id instance_id
185 FROM ahl_unit_config_headers a,ahl_applicable_instances b
186 WHERE a.csi_item_instance_id=b.csi_item_instance_id
187 and b.position_id= c_relationship_id
188 AND SYSDATE between trunc(nvl(A.active_start_date,sysdate)) and trunc(nvl(a.active_end_date,sysdate+1))
189 UNION
190 SELECT a.subject_id instance_id
191 FROM csi_ii_relationships a,ahl_applicable_instances b
192 WHERE a.subject_id=b.csi_item_instance_id
193 and b.position_id=c_relationship_id
194 AND SYSDATE between trunc(nvl(a.active_start_date,sysdate)) and trunc(nvl(a.active_end_date,sysdate+1))
195 AND a.relationship_type_code = 'COMPONENT-OF';
196
197
198 --position in MC and inventory item defined in MR effectivity definition, just
199 --top node
200 /*CURSOR get_top_inst3(c_relationship_id NUMBER, c_inventory_item_id NUMBER)
201 IS
202 SELECT a.csi_item_instance_id instance_id
203 FROM ahl_unit_config_headers A,ahl_applicable_instances api
204 --WHERE A.master_config_id=api.position_id
205 --AND A.csi_item_instance_id=api.csi_item_instance_id
206 WHERE A.csi_item_instance_id=api.csi_item_instance_id
207 AND api.position_id= C_RELATIONSHIP_ID
208 AND SYSDATE between trunc(nvl(a.active_start_date,sysdate))
209 AND trunc(nvl(a.active_end_date,sysdate+1))
210 AND EXISTS (SELECT 'X'
211 FROM csi_item_instances B
212 WHERE B.instance_id = api.csi_item_instance_id
213 AND SYSDATE between trunc(nvl(b.active_start_date,sysdate)) and trunc(nvl(b.active_end_date,sysdate+1))
214 AND inventory_item_id = c_inventory_item_id);*/
215
216 --same as before, but include all nodes, not only top node
217 CURSOR get_inst3(c_relationship_id NUMBER, c_inventory_item_id NUMBER)
218 IS
219 SELECT a.csi_item_instance_id instance_id
220 FROM ahl_unit_config_headers A,ahl_applicable_instances api
221 WHERE A.csi_item_instance_id=api.csi_item_instance_id
222 AND api.position_id=c_relationship_id
223 AND SYSDATE between trunc(nvl(a.active_start_date,sysdate))
224 and trunc(nvl(a.active_end_date,sysdate+1))
225 AND EXISTS (SELECT 'X'
226 FROM csi_item_instances B
227 WHERE B.instance_id = api.csi_item_instance_id
228 AND SYSDATE between trunc(nvl(b.active_start_date,sysdate))
229 and trunc(nvl(b.active_end_date,sysdate+1))
230 AND inventory_item_id = c_inventory_item_id)
231 UNION
232 SELECT subject_id instance_id
233 FROM csi_ii_relationships A,ahl_applicable_instances api
234 WHERE api.position_id=c_relationship_id
235 and api.csi_item_instance_id=a.subject_id
236 AND relationship_type_code = 'COMPONENT-OF'
237 AND SYSDATE between trunc(nvl(a.active_start_date,sysdate))
238 and trunc(nvl(a.active_end_date,sysdate+1))
239 AND EXISTS (SELECT 'X'
240 FROM csi_item_instances B
241 WHERE B.instance_id = api.csi_item_instance_id
242 AND sysdate between trunc(nvl(b.active_start_date,sysdate))
243 and trunc(nvl(b.active_end_date,sysdate+1))
244 AND inventory_item_id = c_inventory_item_id);
245 --inventory item and PC node defined in MR effectivity definition, just top node
246 /*CURSOR get_top_inst4(c_inventory_item_id NUMBER, c_pc_node_id NUMBER)
247 IS
248 SELECT a.csi_item_instance_id instance_id
249 FROM ahl_unit_config_headers A
250 WHERE EXISTS (SELECT 'X'
251 FROM csi_item_instances B
252 WHERE B.instance_id = A.csi_item_instance_id
253 AND sysdate between trunc(nvl(b.active_start_date,sysdate))
254 and trunc(nvl(b.active_end_date,sysdate+1))
255 AND B.inventory_item_id = c_inventory_item_id)
256 AND EXISTS (SELECT 'X'
257 FROM ahl_pc_associations C
258 WHERE C.unit_item_id = A.unit_config_header_id
259 AND C.association_type_flag = 'U'
260 AND EXISTS (SELECT 'X'
261 FROM ahl_pc_nodes_b D
262 WHERE D.pc_node_id = C.pc_node_id
263 START WITH D.pc_node_id = c_pc_node_id
264 CONNECT BY D.parent_node_id = PRIOR D.pc_node_id))
265 UNION
266 SELECT instance_id
267 FROM csi_item_instances A
268 WHERE A.inventory_item_id = c_inventory_item_id
269 AND sysdate between trunc(nvl(a.active_start_date,sysdate))
270 and trunc(nvl(a.active_end_date,sysdate+1))
271 AND NOT EXISTS (SELECT 'X'
272 FROM csi_ii_relationships B
273 WHERE B.subject_id = A.instance_id
274 AND sysdate between trunc(nvl(b.active_start_date,sysdate)) and trunc(nvl(b.active_end_date,sysdate+1))
275 AND B.relationship_type_code = 'COMPONENT-OF')
276 AND EXISTS (SELECT 'X'
277 FROM ahl_pc_associations C
278 WHERE C.unit_item_id = A.inventory_item_id
279 AND C.association_type_flag = 'I'
280 AND EXISTS (SELECT 'X'
281 FROM ahl_pc_nodes_b D
282 WHERE D.pc_node_id = C.pc_node_id
283 START WITH D.pc_node_id = c_pc_node_id
284 CONNECT BY D.parent_node_id = PRIOR D.pc_node_id));*/
285 -- same as before, but include all nodes, not only top node
286
287 CURSOR get_inst4(c_inventory_item_id NUMBER, c_pc_node_id NUMBER) IS
288 SELECT a.csi_item_instance_id instance_id
289 FROM ahl_unit_config_headers A
290 WHERE EXISTS (SELECT 'X'
291 FROM csi_item_instances B
292 WHERE B.instance_id = A.csi_item_instance_id
293 AND sysdate between trunc(nvl(b.active_start_date,sysdate))
294 and trunc(nvl(b.active_end_date,sysdate+1))
295 AND B.inventory_item_id = c_inventory_item_id)
296 AND EXISTS (SELECT 'X'
297 FROM ahl_pc_associations C
298 WHERE C.unit_item_id = A.unit_config_header_id
299 AND C.association_type_flag = 'U'
300 AND EXISTS (SELECT 'X'
301 FROM ahl_pc_nodes_b D
302 WHERE D.pc_node_id = C.pc_node_id
303 START WITH D.pc_node_id = c_pc_node_id
304 CONNECT BY D.parent_node_id = PRIOR D.pc_node_id))
305 UNION
306 SELECT instance_id
307 FROM csi_item_instances A
308 WHERE A.inventory_item_id = c_inventory_item_id
309 AND sysdate between trunc(nvl(a.active_start_date,sysdate))
310 and trunc(nvl(a.active_end_date,sysdate+1))
311 AND NOT EXISTS (SELECT 'X'
312 FROM csi_ii_relationships B
313 WHERE B.subject_id = A.instance_id
314 AND sysdate between trunc(nvl(b.active_start_date,sysdate)) and trunc(nvl(b.active_end_date,sysdate+1))
315 AND B.relationship_type_code = 'COMPONENT-OF')
316 AND EXISTS (SELECT 'X'
317 FROM ahl_pc_associations C
318 WHERE C.unit_item_id = A.inventory_item_id
319 AND C.association_type_flag = 'I'
320 AND EXISTS (SELECT 'X'
321 FROM ahl_pc_nodes_b D
322 WHERE D.pc_node_id = C.pc_node_id
323 START WITH D.pc_node_id = c_pc_node_id
324 CONNECT BY D.parent_node_id = PRIOR D.pc_node_id))
325 UNION -- aobe query added to fix bug number 5448015
326 SELECT a.csi_item_instance_id instance_id
327 FROM ahl_unit_config_headers A,ahl_applicable_instances api
328 WHERE SYSDATE between trunc(nvl(a.active_start_date,sysdate)) and trunc(nvl(a.active_end_date,sysdate+1))
329 AND a.csi_item_instance_id=api.csi_item_instance_id
330 AND EXISTS (SELECT 'X'
331 FROM csi_item_instances B
332 WHERE B.instance_id = Api.csi_item_instance_id
333 AND sysdate between trunc(nvl(b.active_start_date,sysdate)) and trunc(nvl(b.active_end_date,sysdate+1))
334 AND B.inventory_item_id = c_inventory_item_id)
335 AND EXISTS (SELECT 'X'
336 FROM ahl_pc_associations C
337 WHERE C.unit_item_id = A.unit_config_header_id
338 AND C.association_type_flag = 'U'
339 AND EXISTS (SELECT 'X'
340 FROM ahl_pc_nodes_b D
341 WHERE D.pc_node_id = C.pc_node_id
342 START WITH D.pc_node_id = c_pc_node_id
343 CONNECT BY D.parent_node_id = PRIOR D.pc_node_id))
344 UNION
345 SELECT a.subject_id instance_id
346 FROM csi_ii_relationships A
347 WHERE relationship_type_code = 'COMPONENT-OF'
348 AND sysdate between trunc(nvl(a.active_start_date,sysdate)) and trunc(nvl(a.active_end_date,sysdate+1))
349 AND A.SUBJECT_id IN (SELECT csi_item_instance_id from ahl_applicable_instances
350 where csi_item_instance_id=a.subject_id )
351 AND EXISTS (SELECT 'X'
352 FROM csi_item_instances B
353 WHERE B.instance_id = A.subject_id
354 AND SYSDATE between trunc(nvl(b.active_start_date,sysdate)) and trunc(nvl(b.active_end_date,sysdate+1))
355 AND B.inventory_item_id = c_inventory_item_id)
356 START WITH object_id IN (SELECT csi_item_instance_id
357 FROM ahl_unit_config_headers C
358 WHERE EXISTS (SELECT 'X'
359 FROM ahl_pc_associations D
360 WHERE D.unit_item_id = C.unit_config_header_id
361 AND D.association_type_flag = 'U'
362 AND EXISTS (SELECT 'X'
363 FROM ahl_pc_nodes_b E
364 WHERE E.pc_node_id = D.pc_node_id
365 START WITH E.pc_node_id = c_pc_node_id
366 CONNECT BY E.parent_node_id= PRIOR E.pc_node_id)))
367 CONNECT BY object_id = PRIOR subject_id
368 -- sunil- fix for bug7411016
369 AND SYSDATE between trunc(nvl(a.active_start_date,sysdate)) and trunc(nvl(a.active_end_date,sysdate+1))
370 AND a.relationship_type_code = 'COMPONENT-OF'
371 UNION
372 SELECT a.instance_id
373 FROM csi_item_instances A
374 WHERE A.inventory_item_id = c_inventory_item_id
375 AND SYSDATE between trunc(nvl(a.active_start_date,sysdate)) and trunc(nvl(a.active_end_date,sysdate+1))
376 AND EXISTS (SELECT 'X'
377 FROM ahl_pc_associations B
378 WHERE B.unit_item_id = A.inventory_item_id
379 AND B.association_type_flag = 'I'
380 AND EXISTS (SELECT 'X'
381 FROM ahl_pc_nodes_b C
382 WHERE C.pc_node_id = B.pc_node_id
383 START WITH C.pc_node_id = c_pc_node_id
384 CONNECT BY C.parent_node_id= PRIOR C.pc_node_id))
385 UNION
386 SELECT A.subject_id instance_id
387 FROM csi_ii_relationships A
388 WHERE relationship_type_code = 'COMPONENT-OF'
389 AND SYSDATE between trunc(nvl(a.active_start_date,sysdate)) and trunc(nvl(a.active_end_date,sysdate+1))
390 AND EXISTS (SELECT 'X'
391 FROM csi_item_instances B
392 WHERE B.instance_id = A.subject_id
393 AND SYSDATE between trunc(nvl(b.active_start_date,sysdate)) and trunc(nvl(b.active_end_date,sysdate+1))
394 AND B.inventory_item_id = c_inventory_item_id
395 )
396 START WITH object_id IN (SELECT C.instance_id
397 FROM csi_item_instances C
398 WHERE EXISTS (SELECT 'X'
399 FROM ahl_pc_associations D
400 WHERE D.unit_item_id = C.inventory_item_id
401 AND D.association_type_flag = 'I'
402 AND EXISTS (SELECT 'X'
403 FROM ahl_pc_nodes_b E
404 WHERE E.pc_node_id = D.pc_node_id
405 START WITH E.pc_node_id = c_pc_node_id
406 CONNECT BY E.parent_node_id= PRIOR E.pc_node_id)))
407 CONNECT BY object_id = PRIOR subject_id
408 -- sunil- fix for bug7411016
409 AND SYSDATE between trunc(nvl(a.active_start_date,sysdate)) and trunc(nvl(a.active_end_date,sysdate+1))
410 AND a.relationship_type_code = 'COMPONENT-OF';
411 --position in MC and PC node defined in MR effectivity definition, just top node
412 /*CURSOR get_top_inst5(c_relationship_id NUMBER, c_pc_node_id NUMBER) IS
413 SELECT a.csi_item_instance_id instance_id
414 FROM ahl_unit_config_headers A,ahl_applicable_instances api
415 WHERE api.position_id = c_relationship_id
416 and a.csi_item_instance_id=api.csi_item_instance_id
417 --and A.master_config_id=api.position_id
418 AND SYSDATE between trunc(nvl(a.active_start_date,sysdate)) and trunc(nvl(a.active_end_date,sysdate+1))
419 AND EXISTS (SELECT 'X'
420 FROM ahl_pc_associations B
421 WHERE B.unit_item_id = A.unit_config_header_id
422 AND B.association_type_flag = 'U'
423 AND EXISTS (SELECT 'X'
424 FROM ahl_pc_nodes_b C
425 WHERE C.pc_node_id = B.pc_node_id
426 START WITH C.pc_node_id = c_pc_node_id
427 CONNECT BY C.parent_node_id = PRIOR C.pc_node_id))
428 UNION
429 SELECT a.csi_item_instance_id instance_id
430 FROM ahl_unit_config_headers A,ahl_applicable_instances api
431 WHERE api.position_id = c_relationship_id
432 --and A.master_config_id=api.position_id
433 and api.csi_item_instance_id=a.csi_item_instance_id
434 AND SYSDATE between trunc(nvl(a.active_start_date,sysdate)) and trunc(nvl(a.active_end_date,sysdate+1))
435 AND EXISTS (SELECT 'X'
436 FROM csi_item_instances B
437 WHERE B.instance_id = A.csi_item_instance_id
438 AND EXISTS (SELECT 'X'
439 FROM ahl_pc_associations C
440 WHERE C.unit_item_id = B.inventory_item_id
441
442 AND C.association_type_flag = 'I'
443 AND EXISTS (SELECT 'X'
444 FROM ahl_pc_nodes_b D
445 WHERE D.pc_node_id = C.pc_node_id
446 START WITH D.pc_node_id = c_pc_node_id
447 CONNECT BY D.parent_node_id= PRIOR D.pc_node_id)
448 )
449 );*/
450 --same as before, but include all node, not only top node
451 CURSOR get_inst5(c_relationship_id NUMBER, c_pc_node_id NUMBER) IS
452 SELECT a.csi_item_instance_id instance_id
453 FROM ahl_unit_config_headers A,ahl_applicable_instances api
454 WHERE api.csi_item_instance_id=a.csi_item_instance_id
455 and api.position_id=c_relationship_id
456 AND SYSDATE between trunc(nvl(a.active_start_date,sysdate)) and trunc(nvl(a.active_end_date,sysdate+1))
457 AND EXISTS (SELECT 'X'
458 FROM ahl_pc_associations B
459 WHERE B.unit_item_id = A.unit_config_header_id
460 AND B.association_type_flag = 'U'
461 AND EXISTS (SELECT 'X'
462 FROM ahl_pc_nodes_b C
463 WHERE C.pc_node_id = B.pc_node_id
464 START WITH C.pc_node_id = c_pc_node_id
465 CONNECT BY C.parent_node_id= PRIOR C.pc_node_id))
466 UNION
467 SELECT a.subject_id instance_id
468 FROM csi_ii_relationships a
469 WHERE relationship_type_code = 'COMPONENT-OF'
470 AND subject_id in (Select csi_item_instance_id from ahl_applicable_instances)
471 AND sysdate between trunc(nvl(a.active_start_date,sysdate)) and trunc(nvl(a.active_end_date,sysdate+1))
472 START WITH object_id IN (SELECT csi_item_instance_id
473 FROM ahl_unit_config_headers Ax
474 WHERE ax.csi_item_instance_id=a.subject_id AND
475 EXISTS (SELECT 'X'
476 FROM ahl_pc_associations B
477 WHERE B.unit_item_id = Ax.unit_config_header_id
478 AND B.association_type_flag = 'U'
479 AND EXISTS (SELECT 'X'
480 FROM ahl_pc_nodes_b C
481 WHERE C.pc_node_id = B.pc_node_id
482 START WITH C.pc_node_id = c_pc_node_id
483 CONNECT BY C.parent_node_id= PRIOR C.pc_node_id)
484 )
485 )
486 CONNECT BY object_id = PRIOR subject_id
487 -- sunil- fix for bug7411016
488 AND SYSDATE between trunc(nvl(a.active_start_date,sysdate)) and trunc(nvl(a.active_end_date,sysdate+1))
489 AND a.relationship_type_code = 'COMPONENT-OF'
490 UNION
491 SELECT a.csi_item_instance_id instance_id
492 FROM ahl_unit_config_headers A,ahl_applicable_instances api
493 WHERE api.position_id = c_relationship_id
494 and api.csi_item_instance_id= a.csi_item_instance_id
495 AND SYSDATE between trunc(nvl(a.active_start_date,sysdate)) and trunc(nvl(a.active_end_date,sysdate+1))
496 AND EXISTS (SELECT 'X'
497 FROM csi_item_instances B
498 WHERE B.instance_id = api.csi_item_instance_id
499 AND SYSDATE between trunc(nvl(b.active_start_date,sysdate)) and trunc(nvl(b.active_end_date,sysdate+1))
500 AND EXISTS (SELECT 'X'
501 FROM ahl_pc_associations C
502 WHERE C.unit_item_id = B.inventory_item_id
503 AND C.association_type_flag = 'I'
504 AND EXISTS (SELECT 'X'
505 FROM ahl_pc_nodes_b D
506 WHERE D.pc_node_id = C.pc_node_id
507 START WITH D.pc_node_id = c_pc_node_id
508 CONNECT BY D.parent_node_id= PRIOR D.pc_node_id)))
509 UNION
510 SELECT a.subject_id instance_id
511 FROM csi_ii_relationships a
512 WHERE a.relationship_type_code = 'COMPONENT-OF'
513 AND subject_id in (Select csi_item_instance_id from ahl_applicable_instances)
514 AND SYSDATE between trunc(nvl(a.active_start_date,sysdate)) and trunc(nvl(a.active_end_date,sysdate+1))
515 AND subject_id in (select csi_item_instance_id from ahl_applicable_instances)
516 START WITH object_id IN (SELECT ax.instance_id
517 FROM csi_item_instances Ax
518 WHERE SYSDATE between trunc(nvl(ax.active_start_date,sysdate)) and trunc(nvl(ax.active_end_date,sysdate+1))
519 AND instance_id=a.subject_id
520 AND EXISTS (SELECT 'X'
521 FROM ahl_pc_associations B
522 WHERE B.unit_item_id = Ax.inventory_item_id
523 AND B.association_type_flag = 'I'
524 AND EXISTS (SELECT 'X'
525 FROM ahl_pc_nodes_b C
526 WHERE C.pc_node_id = B.pc_node_id
527 START WITH C.pc_node_id = c_pc_node_id
528 CONNECT BY C.parent_node_id= PRIOR C.pc_node_id)
529 )
530 )
531 CONNECT BY object_id = PRIOR subject_id
532 -- sunil- fix for bug7411016
533 AND SYSDATE between trunc(nvl(a.active_start_date,sysdate)) and trunc(nvl(a.active_end_date,sysdate+1))
534 AND a.relationship_type_code = 'COMPONENT-OF';
535 --all inventory item, position in MC and PC node defined in MR effectivity
536 --definition, just top node
537 /*CURSOR get_top_inst6(c_inventory_item_id NUMBER, c_relationship_id NUMBER, c_pc_node_id NUMBER) IS
538 SELECT a.csi_item_instance_id instance_id
539 FROM ahl_unit_config_headers A,ahl_applicable_instances api
540 WHERE api.position_id = c_relationship_id
541 AND api.csi_item_instance_id=A.csi_item_instance_id
542 --and A.master_config_id=api.position_id
543 AND SYSDATE between trunc(nvl(a.active_start_date,sysdate)) and trunc(nvl(a.active_end_date,sysdate+1))
544 AND EXISTS (SELECT 'X'
545 FROM csi_item_instances B
546 WHERE B.instance_id = Api.csi_item_instance_id
547 AND B.inventory_item_id = c_inventory_item_id)
548 AND EXISTS (SELECT 'X'
549 FROM ahl_pc_associations C
550 WHERE C.unit_item_id = A.unit_config_header_id
551 AND C.association_type_flag = 'U'
552 AND EXISTS (SELECT 'X'
553 FROM ahl_pc_nodes_b D
554 WHERE D.pc_node_id = C.pc_node_id
555 START WITH D.pc_node_id = c_pc_node_id
556 CONNECT BY D.parent_node_id = PRIOR D.pc_node_id))
557 UNION
558 SELECT a.csi_item_instance_id instance_id
559 FROM ahl_unit_config_headers A,ahl_applicable_instances api
560 WHERE Api.position_id = c_relationship_id
561 AND A.csi_item_instance_id=API.csi_item_instance_id
562 --and A.master_config_id=api.position_id
563 AND SYSDATE between trunc(nvl(a.active_start_date,sysdate)) and trunc(nvl(a.active_end_date,sysdate+1))
564 AND EXISTS (SELECT 'X'
565 FROM csi_item_instances B
566 WHERE B.instance_id = A.csi_item_instance_id
567 AND SYSDATE between trunc(nvl(b.active_start_date,sysdate)) and trunc(nvl(b.active_end_date,sysdate+1))
568 AND B.inventory_item_id = c_inventory_item_id
569 AND EXISTS (SELECT 'X'
570 FROM ahl_pc_associations C
571 WHERE C.unit_item_id = B.inventory_item_id
572 AND C.association_type_flag = 'I'
573 AND EXISTS (SELECT 'X'
574 FROM ahl_pc_nodes_b D
575 WHERE D.pc_node_id = C.pc_node_id
576 START WITH D.pc_node_id = c_pc_node_id
577 CONNECT BY D.parent_node_id= PRIOR D.pc_node_id)));*/
578 --same as before, but include all nodes, not only top node
579 CURSOR get_inst6(c_inventory_item_id NUMBER, c_relationship_id NUMBER, c_pc_node_id NUMBER) IS
580 SELECT a.csi_item_instance_id instance_id
581 FROM ahl_unit_config_headers A,ahl_applicable_instances api
582 WHERE api.position_id = c_relationship_id
583 AND api.csi_item_instance_id=A.csi_item_instance_id
584 -- AND subject_id in (Select csi_item_instance_id from ahl_applicable_instances)
585 AND SYSDATE between trunc(nvl(a.active_start_date,sysdate)) and trunc(nvl(a.active_end_date,sysdate+1))
586 AND EXISTS (SELECT 'X'
587 FROM csi_item_instances B
588 WHERE B.instance_id = api.csi_item_instance_id
589 AND SYSDATE between trunc(nvl(b.active_start_date,sysdate)) and trunc(nvl(b.active_end_date,sysdate+1))
590 AND B.inventory_item_id = c_inventory_item_id)
591 AND EXISTS (SELECT 'X'
592 FROM ahl_pc_associations C
593 WHERE C.unit_item_id = A.unit_config_header_id
594 AND C.association_type_flag = 'U'
595 AND EXISTS (SELECT 'X'
596 FROM ahl_pc_nodes_b D
597 WHERE D.pc_node_id = C.pc_node_id
598 START WITH D.pc_node_id = c_pc_node_id
599 CONNECT BY D.parent_node_id= PRIOR D.pc_node_id))
600 UNION
601 SELECT a.subject_id instance_id
602 FROM csi_ii_relationships A
603 WHERE relationship_type_code = 'COMPONENT-OF'
604 AND a.relationship_id in (Select position_id from AHL_APPLICABLE_INSTANCES)
605 AND SYSDATE between trunc(nvl(a.active_start_date,sysdate)) and trunc(nvl(a.active_end_date,sysdate+1))
606 AND EXISTS (SELECT 'X'
607 FROM csi_item_instances B
608 WHERE B.instance_id = A.subject_id
609 AND SYSDATE between trunc(nvl(b.active_start_date,sysdate)) and trunc(nvl(b.active_end_date,sysdate+1))
610 AND B.inventory_item_id = c_inventory_item_id)
611 START WITH object_id IN (SELECT c.csi_item_instance_id
612 FROM ahl_unit_config_headers C,ahl_applicable_instances api
613 WHERE c.csi_item_instance_id=api.csi_item_instance_id
614 and api.position_id=c_relationship_id and
615 EXISTS (SELECT 'X'
616 FROM ahl_pc_associations D
617 WHERE D.unit_item_id = C.unit_config_header_id
618 AND D.association_type_flag = 'U'
619 AND EXISTS (SELECT 'X'
620 FROM ahl_pc_nodes_b E
621 WHERE E.pc_node_id = D.pc_node_id
622 START WITH E.pc_node_id = c_pc_node_id
623 CONNECT BY E.parent_node_id= PRIOR E.pc_node_id)))
624 CONNECT BY object_id = PRIOR subject_id
625 -- sunil- fix for bug7411016
626 AND SYSDATE between trunc(nvl(a.active_start_date,sysdate)) and trunc(nvl(a.active_end_date,sysdate+1))
627 AND a.relationship_type_code = 'COMPONENT-OF'
628 UNION
629 SELECT a.csi_item_instance_id instance_id
630 FROM ahl_unit_config_headers A,ahl_applicable_instances api
631 WHERE api.position_id = c_relationship_id
632 AND A.csi_item_instance_id=API.csi_item_instance_id
633 AND SYSDATE between trunc(nvl(a.active_start_date,sysdate)) and trunc(nvl(a.active_end_date,sysdate+1))
634 AND EXISTS (SELECT 'X'
635 FROM csi_item_instances B
636 WHERE B.instance_id = api.csi_item_instance_id
637 AND SYSDATE between trunc(nvl(b.active_start_date,sysdate)) and trunc(nvl(b.active_end_date,sysdate+1))
638 AND B.inventory_item_id = c_inventory_item_id
639 AND EXISTS (SELECT 'X'
640 FROM ahl_pc_associations C
641 WHERE C.unit_item_id = B.inventory_item_id
642 AND C.association_type_flag = 'I'
643 AND EXISTS (SELECT 'X'
644 FROM ahl_pc_nodes_b D
645 WHERE D.pc_node_id = C.pc_node_id
646 START WITH D.pc_node_id = c_pc_node_id
647 CONNECT BY D.parent_node_id= PRIOR D.pc_node_id)))
648 UNION
649 SELECT subject_id instance_id
650 FROM csi_ii_relationships A
651 WHERE relationship_type_code = 'COMPONENT-OF'
652 AND a.subject_id in (Select csi_item_instance_id from AHL_APPLICABLE_INSTANCES )
653 AND SYSDATE between trunc(nvl(a.active_start_date,sysdate)) and trunc(nvl(a.active_end_date,sysdate+1))
654 AND EXISTS (SELECT 'X'
655 FROM csi_item_instances B
656 WHERE B.instance_id = A.subject_id
657 AND SYSDATE between trunc(nvl(b.active_start_date,sysdate)) and trunc(nvl(b.active_end_date,sysdate+1))
658 AND B.inventory_item_id = c_inventory_item_id)
659 START WITH object_id IN (SELECT instance_id
660 FROM csi_item_instances C
661 WHERE SYSDATE between trunc(nvl(c.active_start_date,sysdate)) and trunc(nvl(C.active_end_date,sysdate+1))
662 AND EXISTS (SELECT 'X'
663 FROM ahl_pc_associations D
664 WHERE D.unit_item_id = C.inventory_item_id
665 AND D.association_type_flag = 'I'
666 AND EXISTS (SELECT 'X'
667 FROM ahl_pc_nodes_b E
668 WHERE E.pc_node_id = D.pc_node_id
669 START WITH E.pc_node_id = c_pc_node_id
670 CONNECT BY E.parent_node_id= PRIOR E.pc_node_id)
671 )
672 )
673 CONNECT BY object_id = PRIOR subject_id
674 -- sunil- fix for bug7411016
675 AND SYSDATE between trunc(nvl(a.active_start_date,sysdate)) and trunc(nvl(a.active_end_date,sysdate+1))
676 AND a.relationship_type_code = 'COMPONENT-OF';
677 l_counter number;
678 BEGIN
679 SAVEPOINT GET_MR_AFFECTED_ITEMS_PVT;
680 IF l_debug = 'Y' THEN
681 AHL_DEBUG_PUB.enable_debug;
682 AHL_DEBUG_PUB.debug('Begin private API: AHL_FMP_PVT.GET_MR_AFFECTED_ITEMS');
683 END IF;
684 IF FND_API.to_boolean(p_init_msg_list) THEN
685 FND_MSG_PUB.initialize;
686 END IF;
687 x_return_status := 'S';
688 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
689 p_api_version,
690 l_api_name,
691 G_PKG_NAME)
692 THEN
693 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
694 END IF;
695 -- Check whether the mr_header_id exists --
696 AHL_DEBUG_PUB.debug(' Phase 1');
697
698 DELETE FROM ahl_mr_instances_temp;
699 DELETE FROM ahl_applicable_instances;
700
701 OPEN check_mr_exists(p_mr_header_id);
702 FETCH check_mr_exists INTO l_mr_header_id;
703 IF check_mr_exists%NOTFOUND THEN
704 CLOSE check_mr_exists;
705 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_INVALID_MR');
706 FND_MSG_PUB.ADD;
707 RAISE FND_API.G_EXC_ERROR;
708 END IF;
709 CLOSE check_mr_exists;
710 AHL_DEBUG_PUB.debug(' Phase 2');
711
712 -- Check whether the mr_effectivity_id exists and if it does, check whether it
713 -- belongs to the mr_header_id
714 OPEN check_mr_effect(p_mr_effectivity_id,p_mr_header_id);
715 LOOP
716 FETCH check_mr_effect INTO l_mr_effect;
717 EXIT WHEN check_mr_effect%NOTFOUND ;
718
719 IF l_mr_effect.relationship_id IS NOT NULL
720 THEN
721 AHL_MC_PATH_POSITION_PVT.map_position_to_instances
722 (
723 p_api_version =>p_api_version,
724 p_init_msg_list =>FND_API.G_FALSE,
725 p_commit =>FND_API.G_FALSE,
726 p_validation_level=>p_validation_level,
727 p_position_id =>l_mr_effect.relationship_id,
728 x_return_status =>l_return_Status,
729 x_msg_count =>l_msg_count,
730 x_msg_data =>l_msg_data
731 );
732
733 IF l_debug = 'Y' THEN
734 AHL_DEBUG_PUB.debug(' After Call to MC path positions');
735 Select count(*) into l_counter
736 from ahl_applicable_instances;
737 AHL_DEBUG_PUB.debug('Number of Recs found in ahl_applicable_instances are ...'||l_counter);
738 END IF;
739 END IF;
740 l_mr_header_id := l_mr_effect.mr_header_id;
741
742 IF (l_mr_header_id <> p_mr_header_id) THEN
743 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_INVALID_MR_EFFECTIVITY');
744 FND_MSG_PUB.ADD;
745 l_error_flag:='Y';
746 END IF;
747 --END IF;
748 END LOOP;
749 CLOSE check_mr_effect;
750 IF L_ERROR_FLAG='Y'
751 THEN
752 RAISE FND_API.G_EXC_ERROR;
753 END IF;
754 AHL_DEBUG_PUB.debug(' Phase 3 relation...' ||l_mr_effect.relationship_id);
755
756 -- To avoid being in the same database session when refreshing or table navigating
757 -- the same page due to using the global temporary table.
758 AHL_DEBUG_PUB.debug(' Phase 4');
759 l_index :=1;
760
761 FOR l_mr_effect IN get_mr_effect(p_mr_header_id, p_mr_effectivity_id)
762 LOOP
763 IF (l_mr_effect.inventory_item_id IS NOT NULL AND l_mr_effect.relationship_id IS NULL
764 AND l_mr_effect.pc_node_id IS NULL) THEN
765 AHL_DEBUG_PUB.debug(' Phase 8');
766 --DBMS_OUTPUT.put_line('API1: Come here in case 1B and l_index is: '||l_index);
767 FOR l_get_inst1 IN get_inst1(l_mr_effect.inventory_item_id)
768 LOOP
769 AHL_DEBUG_PUB.debug(' Phase 9');
770 IF (check_effectivity_details(l_get_inst1.instance_id, l_mr_effect.mr_effectivity_id )) THEN
771 x_mr_item_inst_tbl(l_index).item_instance_id := l_get_inst1.instance_id;
772 x_mr_item_inst_tbl(l_index).mr_effectivity_id := l_mr_effect.mr_effectivity_id;
773 l_index := l_index+1;
774 END IF;
775 AHL_DEBUG_PUB.debug(' Phase 10');
776 END LOOP;
777 ELSIF (l_mr_effect.relationship_id IS NOT NULL
778 AND l_mr_effect.inventory_item_id IS NULL
779 AND l_mr_effect.pc_node_id IS NULL)
780 THEN
781 --DBMS_OUTPUT.put_line('API1: Come here in case 2A and l_index is: '||l_index);
782 --DBMS_OUTPUT.put_line('API1: Come here in case 2B and l_index is: '||l_index);
783 FOR l_get_inst2 IN get_inst2(l_mr_effect.relationship_id) LOOP
784 IF (check_effectivity_details(l_get_inst2.instance_id, l_mr_effect.mr_effectivity_id )) THEN
785 x_mr_item_inst_tbl(l_index).item_instance_id := l_get_inst2.instance_id;
786 x_mr_item_inst_tbl(l_index).mr_effectivity_id := l_mr_effect.mr_effectivity_id;
787 l_index := l_index+1;
788 END IF;
789 END LOOP;
790 ELSIF (l_mr_effect.relationship_id IS NOT NULL AND l_mr_effect.inventory_item_id IS NOT NULL
791 AND l_mr_effect.pc_node_id IS NULL) THEN
792 --DBMS_OUTPUT.put_line('API1: Come here in case 3B and l_index is: '||l_index);
793 FOR l_get_inst3 IN get_inst3(l_mr_effect.relationship_id, l_mr_effect.inventory_item_id) LOOP
794 IF (check_effectivity_details(l_get_inst3.instance_id, l_mr_effect.mr_effectivity_id )) THEN
795 x_mr_item_inst_tbl(l_index).item_instance_id := l_get_inst3.instance_id;
796 x_mr_item_inst_tbl(l_index).mr_effectivity_id := l_mr_effect.mr_effectivity_id;
797 l_index := l_index+1;
798 END IF;
799 END LOOP;
800 ELSIF (l_mr_effect.inventory_item_id IS NOT NULL AND l_mr_effect.pc_node_id IS NOT NULL
801 AND l_mr_effect.relationship_id IS NULL) THEN
802 --DBMS_OUTPUT.put_line('API1: Come here in case 4B and l_index is: '||l_index);
803 FOR l_get_inst4 IN get_inst4(l_mr_effect.inventory_item_id, l_mr_effect.pc_node_id) LOOP
804 --DBMS_OUTPUT.put_line('API1: Come here in case 4B after open cursor and l_index is: '||l_index);
805 IF (check_effectivity_details(l_get_inst4.instance_id, l_mr_effect.mr_effectivity_id )) THEN
806 x_mr_item_inst_tbl(l_index).item_instance_id := l_get_inst4.instance_id;
807 x_mr_item_inst_tbl(l_index).mr_effectivity_id := l_mr_effect.mr_effectivity_id;
808 l_index := l_index+1;
809 END IF;
810 END LOOP;
811 ELSIF (l_mr_effect.relationship_id IS NOT NULL AND l_mr_effect.pc_node_id IS NOT NULL
812 AND l_mr_effect.inventory_item_id IS NULL) THEN
813 --DBMS_OUTPUT.put_line('API1: Come here in case 5B and l_index is: '||l_index);
814 FOR l_get_inst5 IN get_inst5(l_mr_effect.relationship_id, l_mr_effect.pc_node_id) LOOP
815 IF (check_effectivity_details(l_get_inst5.instance_id, l_mr_effect.mr_effectivity_id )) THEN
816 x_mr_item_inst_tbl(l_index).item_instance_id := l_get_inst5.instance_id;
817 x_mr_item_inst_tbl(l_index).mr_effectivity_id := l_mr_effect.mr_effectivity_id;
818 l_index := l_index+1;
819 END IF;
820 END LOOP;
821
822 ELSIF (l_mr_effect.inventory_item_id IS NOT NULL AND l_mr_effect.relationship_id IS NOT NULL
823 AND l_mr_effect.pc_node_id IS NOT NULL) THEN
824
825 --DBMS_OUTPUT.put_line('API1: Come here in case 6B and l_index is: '||l_index);
826 FOR l_get_inst6 IN get_inst6(l_mr_effect.inventory_item_id, l_mr_effect.relationship_id, l_mr_effect.pc_node_id) LOOP
827 IF (check_effectivity_details(l_get_inst6.instance_id, l_mr_effect.mr_effectivity_id )) THEN
828 x_mr_item_inst_tbl(l_index).item_instance_id := l_get_inst6.instance_id;
829 x_mr_item_inst_tbl(l_index).mr_effectivity_id := l_mr_effect.mr_effectivity_id;
830 l_index := l_index+1;
831 END IF;
832 END LOOP;
833
834 END IF;
835 END LOOP;
836 --DBMS_OUTPUT.put_line('API1: Come here after six cases and l_index is: '||l_index);
837 IF x_mr_item_inst_tbl.COUNT > 0 THEN
838 FOR i IN x_mr_item_inst_tbl.FIRST..x_mr_item_inst_tbl.LAST LOOP
839 --DBMS_OUTPUT.put_line('API1: Before checking details in for loop: i= '||i);
840 --Also filter OUT all of the items which are not job items
841 IF(p_top_node_flag = 'Y') THEN
842 x_mr_item_inst_tbl(i).item_instance_id := get_topInstanceID(x_mr_item_inst_tbl(i).item_instance_id);
843 END IF;
844 OPEN get_inst_attri(x_mr_item_inst_tbl(i).item_instance_id);
845 FETCH get_inst_attri INTO l_inst_attri;
846 x_mr_item_inst_tbl(i).serial_number := l_inst_attri.serial_number;
847 x_mr_item_inst_tbl(i).item_number := l_inst_attri.item_number;
848 x_mr_item_inst_tbl(i).inventory_item_id := l_inst_attri.inventory_item_id;
849 x_mr_item_inst_tbl(i).location := l_inst_attri.location_description;
850 x_mr_item_inst_tbl(i).status := l_inst_attri.status;
851 x_mr_item_inst_tbl(i).owner := l_inst_attri.owner_name;
852 x_mr_item_inst_tbl(i).condition := l_inst_attri.condition;
853 CLOSE get_inst_attri;
854
855 AHL_FMP_COMMON_PVT.validate_item(x_return_status => l_return_status,
856 x_msg_data => l_msg_data,
857 p_item_number => NULL,
858 p_x_inventory_item_id => x_mr_item_inst_tbl(i).inventory_item_id);
859 --DBMS_OUTPUT.put_line('PL/SQL table count= '||x_mr_item_inst_tbl.COUNT||' and last= '||x_mr_item_inst_tbl.LAST);
860 IF (l_return_status <> 'S')
861 THEN
862 --DBMS_OUTPUT.put_line('Deleting recored i= '||i);
863 x_mr_item_inst_tbl.DELETE(i);
864 END IF;
865 END LOOP;
866
867 --DBMS_OUTPUT.put_line('PL/SQL table count= '||x_mr_item_inst_tbl.COUNT||' and last= '||x_mr_item_inst_tbl.LAST);
868 IF x_mr_item_inst_tbl.COUNT > 0 THEN
869 FOR i IN x_mr_item_inst_tbl.FIRST..x_mr_item_inst_tbl.LAST LOOP
870 IF x_mr_item_inst_tbl.EXISTS(i) THEN
871 INSERT INTO ahl_mr_instances_temp
872 (
873 MR_INSTANCE_TEMP_ID,
874 MR_EFFECTIVITY_ID,
875 ITEM_INSTANCE_ID,
876 SERIAL_NUMBER,
877 ITEM_NUMBER,
878 INVENTORY_ITEM_ID,
879 LOCATION,
880 STATUS,
881 OWNER,
882 CONDITION,
883 UNIT_NAME,
884 UC_HEADER_ID
885 )
886 VALUES
887 (
888 i,
889 x_mr_item_inst_tbl(i).mr_effectivity_id,
890 x_mr_item_inst_tbl(i).item_instance_id,
891 x_mr_item_inst_tbl(i).serial_number,
892 x_mr_item_inst_tbl(i).item_number,
893 x_mr_item_inst_tbl(i).inventory_item_id,
894 x_mr_item_inst_tbl(i).location,
895 x_mr_item_inst_tbl(i).status,
896 x_mr_item_inst_tbl(i).owner,
897 x_mr_item_inst_tbl(i).condition,
898 NULL,
899 NULL
900 );
901 END IF;
902 END LOOP;
903 i := 1;
904 IF p_unique_inst_flag = 'Y' THEN
905 IF p_sort_flag = 'Y' THEN
906 FOR l_get_dist_inst IN get_dist_sort_inst LOOP
907 x_mr_item_inst_tbl(i).mr_effectivity_id := NULL;
908 x_mr_item_inst_tbl(i).item_instance_id := l_get_dist_inst.item_instance_id;
909 x_mr_item_inst_tbl(i).serial_number := l_get_dist_inst.serial_number;
910 x_mr_item_inst_tbl(i).item_number := l_get_dist_inst.item_number;
911 x_mr_item_inst_tbl(i).inventory_item_id := l_get_dist_inst.inventory_item_id;
912 x_mr_item_inst_tbl(i).location := l_get_dist_inst.location;
913 x_mr_item_inst_tbl(i).status := l_get_dist_inst.status;
914 x_mr_item_inst_tbl(i).owner := l_get_dist_inst.owner;
915 x_mr_item_inst_tbl(i).condition := l_get_dist_inst.condition;
916
917 get_ucHeader(p_item_instance_id => x_mr_item_inst_tbl(i).item_instance_id,
918 x_ucHeaderID => x_mr_item_inst_tbl(i).uc_header_id,
919 x_unitName => x_mr_item_inst_tbl(i).unit_name);
920 i := i+1;
921
922 END LOOP;
923 ELSE
924
925 FOR l_get_dist_inst IN get_dist_inst LOOP
926 x_mr_item_inst_tbl(i).mr_effectivity_id := NULL;
927 x_mr_item_inst_tbl(i).item_instance_id := l_get_dist_inst.item_instance_id;
928 x_mr_item_inst_tbl(i).serial_number := l_get_dist_inst.serial_number;
929 x_mr_item_inst_tbl(i).item_number := l_get_dist_inst.item_number;
930 x_mr_item_inst_tbl(i).inventory_item_id := l_get_dist_inst.inventory_item_id;
931 x_mr_item_inst_tbl(i).location := l_get_dist_inst.location;
932 x_mr_item_inst_tbl(i).status := l_get_dist_inst.status;
933 x_mr_item_inst_tbl(i).owner := l_get_dist_inst.owner;
934 x_mr_item_inst_tbl(i).condition := l_get_dist_inst.condition;
935 /*
936 OPEN get_uc_header(x_mr_item_inst_tbl(i).item_instance_id);
937 FETCH get_uc_header INTO l_get_uc_header;
938 IF get_uc_header%NOTFOUND THEN
939 x_mr_item_inst_tbl(i).unit_name := NULL;
940 x_mr_item_inst_tbl(i).uc_header_id := NULL;
941 CLOSE get_uc_header;
942 ELSE
943 x_mr_item_inst_tbl(i).unit_name := l_get_uc_header.name;
944 x_mr_item_inst_tbl(i).uc_header_id := l_get_uc_header.unit_config_header_id;
945 CLOSE get_uc_header;
946 END IF;
947 */
948 get_ucHeader(p_item_instance_id => x_mr_item_inst_tbl(i).item_instance_id,
949 x_ucHeaderID => x_mr_item_inst_tbl(i).uc_header_id,
950 x_unitName => x_mr_item_inst_tbl(i).unit_name);
951 i := i+1;
952 END LOOP;
953 END IF; -- p_sort_flag
954 ELSE
955 FOR l_get_all_inst IN get_all_inst LOOP
956 x_mr_item_inst_tbl(i).mr_effectivity_id := l_get_all_inst.mr_effectivity_id;
957 x_mr_item_inst_tbl(i).item_instance_id := l_get_all_inst.item_instance_id;
958 x_mr_item_inst_tbl(i).serial_number := l_get_all_inst.serial_number;
959 x_mr_item_inst_tbl(i).item_number := l_get_all_inst.item_number;
960 x_mr_item_inst_tbl(i).inventory_item_id := l_get_all_inst.inventory_item_id;
961 x_mr_item_inst_tbl(i).location := l_get_all_inst.location;
962 x_mr_item_inst_tbl(i).status := l_get_all_inst.status;
963 x_mr_item_inst_tbl(i).owner := l_get_all_inst.owner;
964 x_mr_item_inst_tbl(i).condition := l_get_all_inst.condition;
965 /*
966 OPEN get_uc_header(x_mr_item_inst_tbl(i).item_instance_id);
967 FETCH get_uc_header INTO l_get_uc_header;
968 IF get_uc_header%NOTFOUND THEN
969 x_mr_item_inst_tbl(i).unit_name := NULL;
970 x_mr_item_inst_tbl(i).uc_header_id := NULL;
971 CLOSE get_uc_header;
972 ELSE
973 x_mr_item_inst_tbl(i).unit_name := l_get_uc_header.name;
974 x_mr_item_inst_tbl(i).uc_header_id := l_get_uc_header.unit_config_header_id;
975 CLOSE get_uc_header;
976 END IF;
977 */
978 get_ucHeader(p_item_instance_id => x_mr_item_inst_tbl(i).item_instance_id,
979 x_ucHeaderID => x_mr_item_inst_tbl(i).uc_header_id,
980 x_unitName => x_mr_item_inst_tbl(i).unit_name);
981 i := i+1;
982 END LOOP;
983 END IF;
984 IF x_mr_item_inst_tbl.COUNT > i-1 THEN
985 FOR j IN i..x_mr_item_inst_tbl.LAST LOOP
986 IF x_mr_item_inst_tbl.EXISTS(j) THEN
987 x_mr_item_inst_tbl.DELETE(j);
988 END IF;
989 END LOOP;
990 END IF;
991 END IF;
992 END IF;
993
994 --DBMS_OUTPUT.put_line('API1: Just before commit and l_index= '||l_index);
995 --IF FND_API.TO_BOOLEAN(p_commit) THEN
996 -- COMMIT;
997 --END IF;
998 IF l_debug = 'Y' THEN
999 AHL_DEBUG_PUB.debug('End private API: AHL_FMP_PVT.GET_MR_AFFECTED_ITEMS');
1000 AHL_DEBUG_PUB.disable_debug;
1001 END IF;
1002 EXCEPTION WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1003 ROLLBACK TO GET_MR_AFFECTED_ITEMS_PVT;
1004 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1005 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1006 p_count => x_msg_count,
1007 p_data => x_msg_data);
1008 IF l_debug = 'Y' THEN
1009 AHL_DEBUG_PUB.log_app_messages(x_msg_count, x_msg_data,
1010 'UNEXPECTED ERROR IN PRIVATE:' );
1011 AHL_DEBUG_PUB.debug('AHL_FMP_PVT.GET_MR_AFFECTED_ITEMS');
1012 AHL_DEBUG_PUB.disable_debug;
1013 END IF;
1014 WHEN FND_API.G_EXC_ERROR THEN
1015 ROLLBACK TO GET_MR_AFFECTED_ITEMS_PVT;
1016 x_return_status := FND_API.G_RET_STS_ERROR;
1017 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1018 p_count => x_msg_count,
1019 p_data => x_msg_data);
1020 IF l_debug = 'Y' THEN
1021 AHL_DEBUG_PUB.log_app_messages(x_msg_count, x_msg_data,
1022 'ERROR IN PRIVATE:' );
1023 AHL_DEBUG_PUB.debug('AHL_FMP_PVT.GET_MR_AFFECTED_ITEMS');
1024 AHL_DEBUG_PUB.disable_debug;
1025 END IF;
1026 WHEN OTHERS THEN
1027 ROLLBACK TO GET_MR_AFFECTED_ITEMS_PVT;
1028 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1029 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1030 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'AHL_FMP_PVT',
1031 p_procedure_name => 'GET_MR_AFFECTED_ITEMS',
1032 p_error_text => SUBSTR(SQLERRM,1,240));
1033 END IF;
1034 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1035 p_count => x_msg_count,
1036 p_data => x_msg_data);
1037 IF l_debug = 'Y' THEN
1038 AHL_DEBUG_PUB.log_app_messages (x_msg_count, x_msg_data,
1039 'OTHER ERROR IN PRIVATE:' );
1040 AHL_DEBUG_PUB.debug('AHL_FMP_PVT.GET_MR_AFFECTED_ITEMS');
1041 AHL_DEBUG_PUB.disable_debug;
1042 END IF;
1043 END GET_MR_AFFECTED_ITEMS;
1044
1045 -- This API is revamped to fix Bug 6266738
1046 -- Define procedure GET_APPLICABLE_MRS --
1047 PROCEDURE GET_APPLICABLE_MRS(
1048 p_api_version IN NUMBER,
1049 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1050 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1051 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1052 x_return_status OUT NOCOPY VARCHAR2,
1053 x_msg_count OUT NOCOPY NUMBER,
1054 x_msg_data OUT NOCOPY VARCHAR2,
1055 p_item_instance_id IN NUMBER,
1056 p_mr_header_id IN NUMBER := NULL,
1057 p_components_flag IN VARCHAR2 := 'Y',
1058 p_include_doNotImplmt IN VARCHAR2 := 'Y',
1059 p_visit_type_code IN VARCHAR2 :=NULL,
1060 x_applicable_mr_tbl OUT NOCOPY APPLICABLE_MR_TBL_TYPE
1061 ) IS
1062
1063
1064 l_api_name CONSTANT VARCHAR2(30) := 'GET_APPLICABLE_MRS';
1065 l_api_version CONSTANT NUMBER := 1.0;
1066 l_msg_count NUMBER;
1067 l_return_status VARCHAR2(1);
1068 l_item_instance_id NUMBER;
1069 l_debug VARCHAR2(1) :=AHL_DEBUG_PUB.is_log_enabled;
1070
1071
1072 --Get inst_relation_rec for top node of UC
1073 CURSOR uc_top_inst(c_item_instance_id NUMBER) IS
1074 SELECT --NULL parent_item_instance_id,
1075 --a.csi_item_instance_id,
1076 --a.master_config_id,
1077 a.unit_config_header_id
1078 FROM ahl_unit_config_headers a
1079 WHERE a.csi_item_instance_id = c_item_instance_id
1080 /* Fix for bug#4052646
1081 * AND a.parent_uc_header_id is not null -- Line commented
1082 */
1083 AND a.parent_uc_header_id is null
1084 AND SYSDATE between trunc(nvl(a.active_start_date,sysdate)) and trunc(NVL(a.active_end_date,sysdate+1))
1085 ;
1086 l_uc_top_inst uc_top_inst%ROWTYPE;
1087
1088 /* rewrote query for performance
1089 CURSOR validate_pc_node_csr (c_instance_id NUMBER) --amsriniv
1090 IS
1091 SELECT pc_node_id --amsriniv
1092 FROM ahl_pc_nodes_b B
1093 --WHERE B.pc_node_id = c_pc_node_id
1094 START WITH B.pc_node_id IN (select pc_node_id
1095 from ahl_pc_associations itm, csi_item_instances csi,
1096 (SELECT object_id
1097 FROM csi_ii_relationships E
1098 START WITH E.subject_id = c_instance_id
1099 AND E.relationship_type_code = 'COMPONENT-OF'
1100 CONNECT BY E.subject_id = PRIOR E.object_id
1101 AND E.relationship_type_code = 'COMPONENT-OF'
1102 union all
1103 select c_instance_id
1104 from dual) ii
1105 where itm.association_type_flag = 'I'
1106 and itm.unit_item_id = csi.inventory_item_id
1107 and csi.instance_id = ii.object_id
1108 UNION ALL
1109 select pc_node_id
1110 from ahl_pc_associations unit, ahl_unit_config_headers uc,
1111 (SELECT object_id
1112 FROM csi_ii_relationships E
1113 START WITH E.subject_id = c_instance_id
1114 AND E.relationship_type_code = 'COMPONENT-OF'
1115 CONNECT BY E.subject_id = PRIOR E.object_id
1116 AND E.relationship_type_code = 'COMPONENT-OF'
1117 union
1118 select c_instance_id
1119 from dual) ii
1120 where unit.association_type_flag = 'U'
1121 and unit.unit_item_id = uc.unit_config_header_id
1122 and uc.csi_item_instance_id = ii.object_id)
1123 CONNECT BY B.pc_node_id = PRIOR B.parent_node_id;
1124 */
1125
1126 -- Get valid pc nodes for an instance
1127 CURSOR validate_pc_node_csr (c_instance_id NUMBER,
1128 c_pc_node_id NUMBER)
1129 IS
1130 WITH ii AS (SELECT object_id
1131 FROM csi_ii_relationships E
1132 START WITH E.subject_id = c_instance_id
1133 -- sunil- fix for bug7411016
1134 AND SYSDATE between trunc(nvl(E.active_start_date,sysdate)) and trunc(nvl(E.active_end_date,sysdate+1))
1135 AND E.relationship_type_code = 'COMPONENT-OF'
1136
1137
1138 CONNECT BY E.subject_id = PRIOR E.object_id
1139 -- sunil- fix for bug7411016
1140 AND SYSDATE between trunc(nvl(E.active_start_date,sysdate)) and trunc(nvl(E.active_end_date,sysdate+1))
1141 AND E.relationship_type_code = 'COMPONENT-OF'
1142 UNION ALL
1143 SELECT c_instance_id
1144 FROM DUAL)
1145 SELECT 'x' --pc_node_id --amsriniv
1146 FROM ahl_pc_nodes_b B
1147 WHERE B.pc_node_id = c_pc_node_id
1148 START WITH B.pc_node_id IN (select pc_node_id
1149 from ahl_pc_associations itm, csi_item_instances csi,ii
1150 where itm.association_type_flag = 'I'
1151 and itm.unit_item_id = csi.inventory_item_id
1152 and csi.instance_id = ii.object_id
1153 UNION ALL
1154 select pc_node_id
1155 from ahl_pc_associations unit, ahl_unit_config_headers uc, ii
1156 where unit.association_type_flag = 'U'
1157 and unit.unit_item_id = uc.unit_config_header_id
1158 and uc.csi_item_instance_id = ii.object_id)
1159 CONNECT BY B.pc_node_id = PRIOR B.parent_node_id;
1160
1161 --Get attributes of a given MR
1162 CURSOR get_mr_attri(c_mr_header_id NUMBER) IS
1163 SELECT repetitive_flag,
1164 show_repetitive_code,
1165 preceding_mr_header_id,
1166 copy_accomplishment_flag,
1167 implement_status_code,
1168 count_mr_descendents(c_mr_header_id) descendent_count
1169 FROM ahl_mr_headers_b --perf bug 6266738. using base tables.
1170 WHERE mr_header_id = c_mr_header_id;
1171
1172 l_get_mr_attri get_mr_attri%ROWTYPE;
1173
1174 /* not used
1175 -- Added for performance bug - 6138653
1176 CURSOR csi_root_instance_csr (p_instance_id IN NUMBER) IS
1177 SELECT root.object_id
1178 FROM csi_ii_relationships root
1179 WHERE NOT EXISTS (SELECT 'x'
1180 FROM csi_ii_relationships
1181 WHERE subject_id = root.object_id
1182 AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1))
1183 )
1184 START WITH root.subject_id = p_instance_id
1185 AND root.relationship_type_code = 'COMPONENT-OF'
1186 AND trunc(nvl(root.active_start_date,sysdate)) <= trunc(sysdate)
1187 AND trunc(sysdate) < trunc(nvl(root.active_end_date, sysdate+1))
1188 CONNECT BY PRIOR root.object_id = root.subject_id
1189 AND root.relationship_type_code = 'COMPONENT-OF'
1190 AND trunc(nvl(root.active_start_date,sysdate)) <= trunc(sysdate)
1191 AND trunc(sysdate) < trunc(nvl(root.active_end_date, sysdate+1));
1192 */
1193
1194 /* 12 Jul 08: rewrote for performance
1195 --- Performance Changes bug - 6138653
1196 CURSOR get_mr_details_csr(c_instance_id NUMBER, c_mr_header_id NUMBER, c_components_flag VARCHAR2) IS
1197 SELECT A.mr_header_id, A.mr_effectivity_id, A.relationship_id, A.pc_node_id, A.inventory_item_id,
1198 cir.object_id, cir.subject_id, cir.position_reference
1199 FROM ahl_mr_effectivities A,
1200 ahl_mr_headers_app_v MR,
1201 (select cir2.object_id,
1202 cii2.instance_id subject_id,
1203 nvl(uc.master_config_id, cir2.position_reference) position_reference,
1204 0 depth
1205 from csi_item_instances cii2, csi_ii_relationships cir2, ahl_unit_config_headers uc
1206 where cii2.instance_id = c_instance_id
1207 and cii2.instance_id = cir2.subject_id(+)
1208 and cii2.instance_id = uc.csi_item_instance_id(+)
1209 and uc.parent_uc_header_id(+) is null
1210 and SYSDATE between trunc(nvl(uc.active_start_date,sysdate)) and trunc(NVL(uc.active_end_date,sysdate+1))
1211 UNION ALL
1212 SELECT a.object_id,
1213 a.subject_id,
1214 to_number(a.position_reference), level depth
1215 FROM csi_ii_relationships a
1216 WHERE c_components_flag = 'Y'
1217 START WITH object_id = c_instance_id
1218 AND relationship_type_code = 'COMPONENT-OF'
1219 AND SYSDATE between trunc(nvl(active_start_date,sysdate)) and trunc(NVL(active_end_date,sysdate+1))
1220 CONNECT BY object_id = PRIOR subject_id
1221 AND relationship_type_code = 'COMPONENT-OF'
1222 AND SYSDATE between trunc(nvl(active_start_date,sysdate)) and trunc(NVL(active_end_date,sysdate+1))) cir,
1223 csi_item_instances cii
1224 WHERE A.mr_header_id = NVL(c_mr_header_id, A.mr_header_id)
1225 AND MR.mr_header_id = A.mr_header_id
1226 AND cir.subject_id = cii.instance_id
1227 AND MR.mr_status_code = 'COMPLETE'
1228 AND MR.program_type_code NOT IN ('MO_PROC') -- added in R12
1229 AND SYSDATE between trunc(MR.effective_from) and trunc(nvl(MR.effective_to,SYSDATE+1))
1230 AND MR.version_number in (SELECT max(MRM.version_number) from ahl_mr_headers_app_v MRM where SYSDATE between trunc(MR.effective_from) and trunc(nvl(MR.effective_to,SYSDATE+1)) and title=mr.title and mr_status_code='COMPLETE' group by MRM.title)
1231 AND (A.inventory_item_id = cii.inventory_item_id OR
1232 (A.inventory_item_id IS NULL AND A.relationship_id IS NOT NULL AND
1233 AHL_FMP_PVT.Instance_Matches_Path_Pos(cii.instance_id,A.relationship_id) = 'T'))
1234 ORDER BY cir.depth, cir.subject_id; -- depth, subject_id
1235 */
1236
1237 /* 15-Sept 08: Modified logic to seperate processing based Inventory Items, MC positions
1238 CURSOR get_mr_details_csr(c_instance_id NUMBER, c_mr_header_id NUMBER, c_components_flag VARCHAR2) IS
1239 WITH cir AS (select --cir2.object_id,
1240 cii2.instance_id subject_id,
1241 --nvl(uc.master_config_id, cir2.position_reference) position_reference,
1242 0 depth,
1243 cii2.inventory_item_id
1244 from csi_item_instances cii2 --, csi_ii_relationships cir2, ahl_unit_config_headers uc
1245 where cii2.instance_id = c_instance_id
1246 --and cii2.instance_id = cir2.subject_id(+)
1247 --and cii2.instance_id = uc.csi_item_instance_id(+)
1248 --and uc.parent_uc_header_id(+) is null
1249 --and SYSDATE between trunc(nvl(uc.active_start_date,sysdate))
1250 --and trunc(NVL(uc.active_end_date,sysdate+1))
1251 UNION ALL
1252 SELECT --a.object_id,
1253 a.subject_id,
1254 --to_number(a.position_reference),
1255 level depth,
1256 (select inventory_item_id
1257 from csi_item_instances
1258 where instance_id = a.subject_id) inventory_item_id
1259 FROM csi_ii_relationships a
1260 WHERE c_components_flag = 'Y'
1261 START WITH object_id = c_instance_id
1262 AND relationship_type_code = 'COMPONENT-OF'
1263 AND SYSDATE between trunc(nvl(active_start_date,sysdate))
1264 AND trunc(NVL(active_end_date,sysdate+1))
1265 CONNECT BY object_id = PRIOR subject_id
1266 AND relationship_type_code = 'COMPONENT-OF'
1267 AND SYSDATE between trunc(nvl(active_start_date,sysdate))
1268 AND trunc(NVL(active_end_date,sysdate+1))
1269 )
1270 SELECT * FROM (-- first query will match based on inventory items
1271 SELECT A.mr_header_id, A.mr_effectivity_id,
1272 -- A.relationship_id,
1273 A.pc_node_id,
1274 -- A.inventory_item_id,
1275 -- cir.object_id,
1276 cir.subject_id,
1277 --cir.position_reference ,
1278 cir.depth
1279 FROM ahl_mr_headers_app_v MR, cir,
1280 ahl_mr_effectivities A
1281 WHERE A.mr_header_id = NVL(c_mr_header_id, A.mr_header_id)
1282 AND MR.mr_header_id = A.mr_header_id
1283 AND MR.mr_status_code = 'COMPLETE'
1284 AND MR.program_type_code NOT IN ('MO_PROC') -- added in R12
1285 AND SYSDATE between trunc(MR.effective_from) and trunc(nvl(MR.effective_to,SYSDATE+1))
1286 AND MR.version_number in (SELECT max(MRM.version_number)
1287 from ahl_mr_headers_app_v MRM
1288 where SYSDATE between trunc(MR.effective_from)
1289 and trunc(nvl(MR.effective_to,SYSDATE+1))
1290 and title=mr.title and mr_status_code='COMPLETE'
1291 group by MRM.title)
1292 AND A.inventory_item_id = cir.inventory_item_id
1293
1294 UNION ALL
1295
1296 -- query will match based on path position
1297 SELECT A.mr_header_id, A.mr_effectivity_id, --A.relationship_id,
1298 A.pc_node_id, --A.inventory_item_id,
1299 --cir.object_id,
1300 cir.subject_id,
1301 --cir.position_reference,
1302 cir.depth
1303 FROM ahl_mr_headers_app_v MR, ahl_mc_path_positions mcp,
1304 --ahl_mc_headers_b hdr, ahl_mc_relationships rel,
1305 cir, ahl_mr_effectivities A
1306 WHERE A.mr_header_id = NVL(c_mr_header_id, A.mr_header_id)
1307 AND MR.mr_header_id = A.mr_header_id
1308 AND MR.mr_status_code = 'COMPLETE'
1309 AND MR.program_type_code NOT IN ('MO_PROC') -- added in R12
1310 AND SYSDATE between trunc(MR.effective_from)
1311 AND trunc(nvl(MR.effective_to,SYSDATE+1))
1312 AND MR.version_number in (SELECT max(MRM.version_number)
1313 from ahl_mr_headers_app_v MRM
1314 where SYSDATE between trunc(MR.effective_from)
1315 and trunc(nvl(MR.effective_to,SYSDATE+1))
1316 and title=mr.title and mr_status_code='COMPLETE'
1317 group by MRM.title)
1318 AND a.relationship_id = mcp.path_position_id
1319 AND (a.inventory_item_id IS NULL OR a.inventory_item_id = cir.inventory_item_id)
1320 -- AND TO_NUMBER(cir.POSITION_REFERENCE) = rel.RELATIONSHIP_ID
1321 -- AND REL.mc_header_id = HDR.mc_header_id
1322 AND AHL_FMP_PVT.Instance_Matches_Path_Pos(cir.subject_id,A.relationship_id) = 'T'
1323 -- AND AHL_FMP_PVT.Instance_Matches_Path_Pos(cir.subject_id,A.relationship_id,
1324 -- mcp.ENCODED_PATH_POSITION, hdr.mc_id,
1325 -- hdr.version_number, rel.position_key) = 'T'
1326
1327 ) appl_mr
1328 ORDER BY appl_mr.depth, appl_mr.subject_id; -- depth, subject_id
1329 */
1330
1331 -- for the configuration components, get valid mr effectivities based on inventory items.
1332 CURSOR get_comp_mr_inv_csr(c_instance_id NUMBER, c_mr_header_id NUMBER) IS
1333 SELECT A.mr_header_id, A.mr_effectivity_id,
1334 A.pc_node_id,
1335 ii.instance_id subject_id,
1336 (SELECT to_date(ciea1.attribute_value, 'DD/MM/YYYY') from csi_inst_extend_attrib_v ciea1
1337 WHERE ciea1.instance_id = ii.instance_id AND ciea1.attribute_code = 'AHL_MFG_DATE'
1338 AND ciea1.attribute_level = 'GLOBAL') mfg_date,
1339 ii.serial_number --,
1340 --(SELECT 'Y' from ahl_mr_effectivity_dtls where mr_effectivity_id = a.mr_effectivity_id and ROWNUM < 2) eff_dtls_flag
1341 FROM ( SELECT a.subject_id
1342 FROM csi_ii_relationships a
1343 START WITH object_id = c_instance_id
1344 AND relationship_type_code = 'COMPONENT-OF'
1345 AND SYSDATE between trunc(nvl(active_start_date,sysdate))
1346 AND trunc(NVL(active_end_date,sysdate+1))
1347 CONNECT BY object_id = PRIOR subject_id
1348 AND relationship_type_code = 'COMPONENT-OF'
1349 AND SYSDATE between trunc(nvl(active_start_date,sysdate))
1350 AND trunc(NVL(active_end_date,sysdate+1))
1351 ) cir, csi_item_instances ii,
1352 ahl_mr_effectivities A
1353 WHERE A.mr_header_id = NVL(c_mr_header_id, A.mr_header_id)
1354 AND ii.instance_id = cir.subject_id
1355 AND A.inventory_item_id = ii.inventory_item_id
1356 AND A.relationship_id is null
1357 AND exists (SELECT 'x' from ahl_mr_headers_app_v MR
1358 WHERE MR.mr_header_id = A.mr_header_id
1359 AND MR.program_type_code NOT IN ('MO_PROC') -- added in R12
1360 AND MR.version_number in (SELECT max(MRM.version_number)
1361 FROM ahl_mr_headers_app_v MRM
1362 WHERE mrm.title = mr.title
1363 AND SYSDATE between trunc(MR.effective_from)
1364 AND trunc(nvl(MR.effective_to,SYSDATE+1))
1365 AND mr_status_code='COMPLETE'
1366 )
1367 )
1368
1369 -- ORDER BY ii.instance_id;
1370 ORDER BY A.mr_effectivity_id, A.mr_header_id;
1371
1372 -- for input instance, get valid mr effectivities based on inventory items.
1373 CURSOR get_inst_mr_inv_csr(c_instance_id NUMBER, c_mr_header_id NUMBER) IS
1374 SELECT A.mr_header_id, A.mr_effectivity_id,
1375 A.pc_node_id,
1376 ii.instance_id subject_id,
1377 (SELECT to_date(ciea1.attribute_value, 'DD/MM/YYYY') from csi_inst_extend_attrib_v ciea1
1378 WHERE ciea1.instance_id = ii.instance_id AND ciea1.attribute_code = 'AHL_MFG_DATE'
1379 AND ciea1.attribute_level = 'GLOBAL') mfg_date,
1380 ii.serial_number --,
1381 --(SELECT 'Y' from ahl_mr_effectivity_dtls where mr_effectivity_id = a.mr_effectivity_id and ROWNUM < 2) eff_dtls_flag
1382 FROM csi_item_instances ii,
1383 ahl_mr_effectivities A
1384 WHERE ii.instance_id = c_instance_id
1385 AND A.mr_header_id = NVL(c_mr_header_id, A.mr_header_id)
1386 AND A.inventory_item_id = ii.inventory_item_id
1387 AND A.relationship_id is null
1388 AND exists (SELECT 'x' from ahl_mr_headers_app_v MR
1389 WHERE MR.mr_header_id = A.mr_header_id
1390 AND MR.program_type_code NOT IN ('MO_PROC') -- added in R12
1391 AND MR.version_number in (SELECT max(MRM.version_number)
1392 FROM ahl_mr_headers_app_v MRM
1393 WHERE mrm.title = mr.title
1394 AND SYSDATE between trunc(MR.effective_from)
1395 AND trunc(nvl(MR.effective_to,SYSDATE+1))
1396 AND mr_status_code='COMPLETE'
1397 )
1398 )
1399 --ORDER BY ii.instance_id;
1400 ORDER BY A.mr_effectivity_id, A.mr_header_id;
1401
1402
1403 -- get valid mr effectivities based on path position ID.
1404 CURSOR get_posn_mr_csr(c_mr_header_id NUMBER) IS
1405 SELECT A.mr_header_id, A.mr_effectivity_id,
1406 A.pc_node_id,
1407 cii.instance_id subject_id,
1408 (SELECT to_date(ciea1.attribute_value, 'DD/MM/YYYY') from csi_inst_extend_attrib_v ciea1
1409 WHERE ciea1.instance_id = cii.instance_id AND ciea1.attribute_code = 'AHL_MFG_DATE'
1410 AND ciea1.attribute_level = 'GLOBAL') mfg_date,
1411 cii.serial_number --,
1412 --(SELECT 'Y' from ahl_mr_effectivity_dtls where mr_effectivity_id = a.mr_effectivity_id and ROWNUM < 2) eff_dtls_flag
1413 FROM ahl_applicable_instances aai,csi_item_instances cii,
1414 ahl_mr_effectivities A
1415 WHERE A.mr_header_id = NVL(c_mr_header_id, A.mr_header_id)
1416 AND A.relationship_id IS NOT NULL
1417 AND aai.position_id = A.relationship_id
1418 AND aai.csi_item_instance_id = cii.instance_id
1419 AND nvl(A.inventory_item_id,cii.inventory_item_id) = cii.inventory_item_id
1420 AND exists (SELECT 'x' from ahl_mr_headers_app_v MR
1421 WHERE MR.mr_header_id = A.mr_header_id
1422 AND MR.program_type_code NOT IN ('MO_PROC') -- added in R12
1423 AND MR.version_number in (SELECT max(MRM.version_number)
1424 FROM ahl_mr_headers_app_v MRM
1425 WHERE mrm.title = mr.title
1426 AND SYSDATE between trunc(MR.effective_from)
1427 AND trunc(nvl(MR.effective_to,SYSDATE+1))
1428 AND mr_status_code='COMPLETE'
1429 )
1430 )
1431 --ORDER BY cii.instance_id;
1432 ORDER BY A.mr_effectivity_id, A.mr_header_id;
1433
1434
1435 /* 12 Jul 08: Modified for performance.
1436 CURSOR get_visit_mr_details_csr(c_instance_id NUMBER, c_visit_type_code VARCHAR2) IS
1437 SELECT A.mr_header_id, A.mr_effectivity_id, A.relationship_id, A.pc_node_id, A.inventory_item_id,
1438 cir.object_id, cir.subject_id, cir.position_reference
1439 FROM ahl_mr_effectivities A, ahl_mr_headers_app_v MR, ahl_mr_visit_types vis,
1440 (select cir2.object_id,
1441 cii2.instance_id subject_id,
1442 nvl(uc.master_config_id, cir2.position_reference) position_reference,
1443 0 depth
1444 from csi_item_instances cii2, csi_ii_relationships cir2, ahl_unit_config_headers uc
1445 where cii2.instance_id = c_instance_id
1446 and cii2.instance_id = cir2.subject_id(+)
1447 and cii2.instance_id = uc.csi_item_instance_id(+)
1448 and uc.parent_uc_header_id(+) is null
1449 and SYSDATE between trunc(nvl(uc.active_start_date,sysdate)) and trunc(NVL(uc.active_end_date,sysdate+1))
1450 ) cir,
1451 csi_item_instances cii
1452 WHERE MR.mr_header_id = A.mr_header_id
1453 AND A.mr_header_id = vis.mr_header_id
1454 AND vis.mr_visit_type_code = c_visit_type_code
1455 AND cir.subject_id = cii.instance_id
1456 AND MR.mr_status_code = 'COMPLETE'
1457 AND MR.program_type_code NOT IN ('MO_PROC') -- added in R12
1458 AND SYSDATE between trunc(MR.effective_from) and trunc(nvl(MR.effective_to,SYSDATE+1))
1459 AND MR.version_number in (SELECT max(MRM.version_number) from ahl_mr_headers_app_v MRM where SYSDATE between trunc(MR.effective_from) and trunc(nvl(MR.effective_to,SYSDATE+1)) and title=mr.title and mr_status_code='COMPLETE' group by MRM.title)
1460 AND (A.inventory_item_id = cii.inventory_item_id OR
1461 (A.inventory_item_id IS NULL AND A.relationship_id IS NOT NULL AND
1462 AHL_FMP_PVT.Instance_Matches_Path_Pos(cii.instance_id,A.relationship_id) = 'T'))
1463 ORDER BY cir.depth, cir.subject_id; -- depth, subject_id
1464 */
1465
1466 /* 15-Sept 08: Modified logic to seperate processing based Inventory Items, MC positions
1467 CURSOR get_visit_mr_details_csr(c_instance_id NUMBER, c_visit_type_code VARCHAR2) IS
1468 WITH cir AS (select --cir2.object_id,
1469 cii2.instance_id subject_id,
1470 --nvl(uc.master_config_id, cir2.position_reference) position_reference,
1471 0 depth,
1472 cii2.inventory_item_id
1473 from csi_item_instances cii2 --, csi_ii_relationships cir2, ahl_unit_config_headers uc
1474 where cii2.instance_id = c_instance_id
1475 --and cii2.instance_id = cir2.subject_id(+)
1476 --and cii2.instance_id = uc.csi_item_instance_id(+)
1477 --and uc.parent_uc_header_id(+) is null
1478 --and SYSDATE between trunc(nvl(uc.active_start_date,sysdate))
1479 --and trunc(NVL(uc.active_end_date,sysdate+1))
1480 )
1481 SELECT * FROM (-- first query will match based on inventory items
1482 SELECT A.mr_header_id, A.mr_effectivity_id,
1483 -- A.relationship_id,
1484 A.pc_node_id,
1485 -- A.inventory_item_id,
1486 --cir.object_id,
1487 cir.subject_id,
1488 --cir.position_reference ,
1489 cir.depth
1490 FROM ahl_mr_headers_app_v MR, ahl_mr_visit_types vis, cir,
1491 ahl_mr_effectivities A
1492 WHERE MR.mr_header_id = A.mr_header_id
1493 AND A.mr_header_id = vis.mr_header_id
1494 AND vis.mr_visit_type_code = c_visit_type_code
1495 AND MR.mr_status_code = 'COMPLETE'
1496 AND MR.program_type_code NOT IN ('MO_PROC') -- added in R12
1497 AND SYSDATE between trunc(MR.effective_from) and trunc(nvl(MR.effective_to,SYSDATE+1))
1498 AND MR.version_number in (SELECT max(MRM.version_number)
1499 from ahl_mr_headers_app_v MRM
1500 where SYSDATE between trunc(MR.effective_from)
1501 and trunc(nvl(MR.effective_to,SYSDATE+1))
1502 and title=mr.title and mr_status_code='COMPLETE'
1503 group by MRM.title)
1504 AND A.inventory_item_id = cir.inventory_item_id
1505
1506 UNION ALL
1507
1508 -- query will match based on path position
1509 SELECT A.mr_header_id, A.mr_effectivity_id, --A.relationship_id,
1510 A.pc_node_id, --A.inventory_item_id,
1511 --cir.object_id,
1512 cir.subject_id,
1513 --cir.position_reference,
1514 cir.depth
1515 FROM ahl_mr_headers_app_v MR, ahl_mr_visit_types vis, ahl_mc_path_positions mcp,
1516 --ahl_mc_headers_b hdr, ahl_mc_relationships rel,
1517 cir, ahl_mr_effectivities A
1518 WHERE MR.mr_header_id = A.mr_header_id
1519 AND A.mr_header_id = vis.mr_header_id
1520 AND vis.mr_visit_type_code = c_visit_type_code
1521 AND MR.mr_status_code = 'COMPLETE'
1522 AND MR.program_type_code NOT IN ('MO_PROC') -- added in R12
1523 AND SYSDATE between trunc(MR.effective_from)
1524 AND trunc(nvl(MR.effective_to,SYSDATE+1))
1525 AND MR.version_number in (SELECT max(MRM.version_number)
1526 from ahl_mr_headers_app_v MRM
1527 where SYSDATE between trunc(MR.effective_from)
1528 and trunc(nvl(MR.effective_to,SYSDATE+1))
1529 and title=mr.title and mr_status_code='COMPLETE'
1530 group by MRM.title)
1531 AND a.relationship_id = mcp.path_position_id
1532 AND (a.inventory_item_id IS NULL OR a.inventory_item_id = cir.inventory_item_id)
1533 -- AND TO_NUMBER(cir.POSITION_REFERENCE) = rel.RELATIONSHIP_ID
1534 -- AND REL.mc_header_id = HDR.mc_header_id
1535 AND AHL_FMP_PVT.Instance_Matches_Path_Pos(cir.subject_id,A.relationship_id) = 'T'
1536 -- AND AHL_FMP_PVT.Instance_Matches_Path_Pos(cir.subject_id,A.relationship_id,
1537 -- mcp.ENCODED_PATH_POSITION, hdr.mc_id,
1538 -- hdr.version_number, rel.position_key) = 'T'
1539
1540 ) appl_mr
1541 ORDER BY appl_mr.depth, appl_mr.subject_id; -- depth, subject_id
1542 */
1543
1544 -- for the configuration components, get valid mr effectivities based on inventory items.
1545 CURSOR get_comp_vst_inv_csr(c_instance_id NUMBER, c_visit_type_code VARCHAR2) IS
1546 SELECT A.mr_header_id, A.mr_effectivity_id,
1547 A.pc_node_id,
1548 cii.instance_id subject_id,
1549 (SELECT to_date(ciea1.attribute_value, 'DD/MM/YYYY') from csi_inst_extend_attrib_v ciea1
1550 WHERE ciea1.instance_id = cii.instance_id AND ciea1.attribute_code = 'AHL_MFG_DATE'
1551 AND ciea1.attribute_level = 'GLOBAL') mfg_date,
1552 cii.serial_number --,
1553 --(SELECT 'Y' from ahl_mr_effectivity_dtls where mr_effectivity_id = a.mr_effectivity_id and ROWNUM < 2) eff_dtls_flag
1554 FROM ( SELECT a.subject_id
1555 FROM csi_ii_relationships a
1556 START WITH object_id = c_instance_id
1557 AND relationship_type_code = 'COMPONENT-OF'
1558 AND SYSDATE between trunc(nvl(active_start_date,sysdate))
1559 AND trunc(NVL(active_end_date,sysdate+1))
1560 CONNECT BY object_id = PRIOR subject_id
1561 AND relationship_type_code = 'COMPONENT-OF'
1562 AND SYSDATE between trunc(nvl(active_start_date,sysdate))
1563 AND trunc(NVL(active_end_date,sysdate+1))
1564 ) cir, csi_item_instances cii, ahl_mr_visit_types vis,
1565 ahl_mr_effectivities A
1566 WHERE A.mr_header_id = vis.mr_header_id
1567 AND vis.mr_visit_type_code = c_visit_type_code
1568 AND cir.subject_id = cii.instance_id
1569 AND A.inventory_item_id = cii.inventory_item_id
1570 AND A.relationship_id is null
1571 AND exists (SELECT 'x' from ahl_mr_headers_app_v MR
1572 WHERE MR.mr_header_id = A.mr_header_id
1573 AND MR.program_type_code NOT IN ('MO_PROC') -- added in R12
1574 AND MR.version_number in (SELECT max(MRM.version_number)
1575 FROM ahl_mr_headers_app_v MRM
1576 WHERE mrm.title = mr.title
1577 AND SYSDATE between trunc(MR.effective_from)
1578 AND trunc(nvl(MR.effective_to,SYSDATE+1))
1579 AND mr_status_code='COMPLETE'
1580 )
1581 )
1582 -- ORDER BY cii.instance_id;
1583 ORDER BY A.mr_effectivity_id,A.mr_header_id;
1584
1585
1586 -- for input instance, get valid mr effectivities based on inventory items.
1587 CURSOR get_inst_vst_inv_csr(c_instance_id NUMBER, c_visit_type_code VARCHAR2) IS
1588 SELECT A.mr_header_id, A.mr_effectivity_id,
1589 A.pc_node_id,
1590 cii.instance_id subject_id,
1591 (SELECT to_date(ciea1.attribute_value, 'DD/MM/YYYY') from csi_inst_extend_attrib_v ciea1
1592 WHERE cii.instance_id = ciea1.instance_id(+) AND ciea1.attribute_code(+) = 'AHL_MFG_DATE'
1593 AND ciea1.attribute_level(+) = 'GLOBAL') mfg_date,
1594 cii.serial_number --,
1595 --(SELECT 'Y' from ahl_mr_effectivity_dtls where mr_effectivity_id = a.mr_effectivity_id and ROWNUM < 2) eff_dtls_flag
1596 FROM csi_item_instances cii,ahl_mr_visit_types vis,
1597 ahl_mr_effectivities A
1598 WHERE A.mr_header_id = vis.mr_header_id
1599 AND vis.mr_visit_type_code = c_visit_type_code
1600 AND cii.instance_id = c_instance_id
1601 AND A.inventory_item_id = cii.inventory_item_id
1602 AND A.relationship_id is null
1603 AND exists (SELECT 'x' from ahl_mr_headers_app_v MR
1604 WHERE MR.mr_header_id = A.mr_header_id
1605 AND MR.program_type_code NOT IN ('MO_PROC') -- added in R12
1606 AND MR.version_number in (SELECT max(MRM.version_number)
1607 FROM ahl_mr_headers_app_v MRM
1608 WHERE mrm.title = mr.title
1609 AND SYSDATE between trunc(MR.effective_from)
1610 AND trunc(nvl(MR.effective_to,SYSDATE+1))
1611 AND mr_status_code='COMPLETE'
1612 )
1613 )
1614 --ORDER BY cii.instance_id;
1615 ORDER BY A.mr_effectivity_id,A.mr_header_id;
1616
1617 -- get valid mr effectivities based on path position ID.
1618 CURSOR get_posn_vst_csr(c_visit_type_code VARCHAR2) IS
1619 SELECT A.mr_header_id, A.mr_effectivity_id,
1620 A.pc_node_id,
1621 cii.instance_id subject_id,
1622 (SELECT to_date(ciea1.attribute_value, 'DD/MM/YYYY') from csi_inst_extend_attrib_v ciea1
1623 WHERE ciea1.instance_id = cii.instance_id AND ciea1.attribute_code = 'AHL_MFG_DATE'
1624 AND ciea1.attribute_level = 'GLOBAL') mfg_date,
1625 cii.serial_number --,
1626 --(SELECT 'Y' from ahl_mr_effectivity_dtls where mr_effectivity_id = a.mr_effectivity_id and ROWNUM < 2) eff_dtls_flag
1627 FROM ahl_applicable_instances aai,csi_item_instances cii,ahl_mr_visit_types vis,
1628 ahl_mr_effectivities A
1629 WHERE A.mr_header_id = vis.mr_header_id
1630 AND vis.mr_visit_type_code = c_visit_type_code
1631 AND A.relationship_id IS NOT NULL
1632 AND aai.position_id = A.relationship_id
1633 AND nvl(A.inventory_item_id, cii.inventory_item_id) = cii.inventory_item_id
1634 AND exists (SELECT 'x' from ahl_mr_headers_app_v MR
1635 WHERE MR.mr_header_id = A.mr_header_id
1636 AND MR.program_type_code NOT IN ('MO_PROC') -- added in R12
1637 AND MR.version_number in (SELECT max(MRM.version_number)
1638 FROM ahl_mr_headers_app_v MRM
1639 WHERE mrm.title = mr.title
1640 AND SYSDATE between trunc(MR.effective_from)
1641 AND trunc(nvl(MR.effective_to,SYSDATE+1))
1642 AND mr_status_code='COMPLETE'
1643 )
1644 )
1645 --ORDER BY cii.instance_id;
1646 ORDER BY A.mr_effectivity_id, A.mr_header_id;
1647
1648
1649 -- check for path position based effectivities.
1650 CURSOR relationship_csr IS
1651 SELECT 'x' from dual
1652 WHERE exists (select 'x'
1653 from ahl_mr_effectivities mre
1654 where mre.relationship_id is not null
1655 and exists (SELECT 'x' from ahl_mr_headers_app_v MR
1656 WHERE MR.mr_header_id = mre.mr_header_id
1657 AND MR.program_type_code NOT IN ('MO_PROC') -- added in R12
1658 AND MR.version_number in (SELECT max(MRM.version_number)
1659 FROM ahl_mr_headers_app_v MRM
1660 WHERE mrm.title = mr.title
1661 AND SYSDATE between trunc(MR.effective_from)
1662 AND trunc(nvl(MR.effective_to,SYSDATE+1))
1663 AND mr_status_code='COMPLETE'
1664 )
1665 )
1666 );
1667
1668
1669 -- check for path position based effectivities for visit type
1670 CURSOR relationship_vtype_csr(p_visit_type_code IN VARCHAR2) IS
1671 SELECT 'x' from dual
1672 WHERE exists (select 'x'
1673 from ahl_mr_effectivities mre, ahl_mr_visit_types vis
1674 where vis.mr_visit_type_code = p_visit_type_code
1675 and mre.mr_header_id = vis.mr_header_id
1676 and mre.relationship_id is not null
1677 and exists (SELECT 'x' from ahl_mr_headers_app_v MR
1678 WHERE MR.mr_header_id = vis.mr_header_id
1679 AND MR.program_type_code NOT IN ('MO_PROC') -- added in R12
1680 AND MR.version_number in (SELECT max(MRM.version_number)
1681 FROM ahl_mr_headers_app_v MRM
1682 WHERE mrm.title = mr.title
1683 AND SYSDATE between trunc(MR.effective_from)
1684 AND trunc(nvl(MR.effective_to,SYSDATE+1))
1685 AND mr_status_code='COMPLETE'
1686 )
1687 )
1688 );
1689
1690 -- check for path position based effectivities for MR
1691 CURSOR relationship_mr_csr(p_mr_header_id IN NUMBER) IS
1692 SELECT 'x' from dual
1693 WHERE exists (select 'x'
1694 from ahl_mr_effectivities
1695 where mr_header_id = p_mr_header_id
1696 and relationship_id is not null);
1697
1698
1699 /* Not used
1700 amsriniv Bug 6971165 : To improve performance, instead of calling the cursor for every combination of
1701 c_instance_id and c_position_id, we call the below cursor only when instance changes. Then, to validate
1702 position_id, we iterate through the output of the cursor for the corresponding instance.
1703
1704 -- validate patch position Id.
1705 CURSOR relationship_csr(c_instance_id IN NUMBER) IS
1706 select position_id
1707 from ahl_applicable_instances
1708 where csi_item_instance_id = c_instance_id;
1709 */
1710
1711 /*
1712 amsriniv Bug 6971165 : To improve performance, replicating the logic of CHECK_EFFECTIVITY_DETAILS in this
1713 procedure so that the below two cursors are invoked only when required. Previously, the function
1714 CHECK_EFFECTIVITY_DETAILS was being called for every combination of mr_effectivity and instance_id. Now, the
1715 get_inst_att cursor is called once for an instance and get_effect_details is called only if the MR validity still
1716 holds.
1717 */
1718 CURSOR get_effect_details(c_mr_effectivity_id NUMBER) IS
1719 SELECT exclude_flag, serial_number_from, serial_number_to, manufacturer_id,
1720 manufacture_date_from, manufacture_date_to, country_code
1721 FROM ahl_mr_effectivity_dtls
1722 WHERE mr_effectivity_id = c_mr_effectivity_id
1723 ORDER BY exclude_flag ASC;
1724
1725 /* not used
1726 -- get instance details.
1727 CURSOR get_inst_att(c_item_instance_id NUMBER) IS
1728 SELECT csi.serial_number serial_number ,
1729 to_date(ciea1.attribute_value, 'DD/MM/YYYY') mfg_date ,
1730 'm' manufacturer_id ,
1731 'c' country_code
1732 FROM csi_item_instances csi,
1733 csi_inst_extend_attrib_v ciea1
1734 WHERE csi.instance_id = ciea1.instance_id(+)
1735 AND ciea1.attribute_code(+) = 'AHL_MFG_DATE'
1736 AND ciea1.attribute_level(+) = 'GLOBAL'
1737 AND csi.instance_id = c_item_instance_id;
1738 */
1739
1740 /* not used
1741 CURSOR is_position_check_req(c_mr_effectivity_id NUMBER) IS
1742 SELECT 'X'
1743 FROM ahl_mr_effectivities
1744 WHERE mr_effectivity_id = c_mr_effectivity_id
1745 AND inventory_item_id IS NOT NULL;
1746 */
1747
1748
1749 -- to get configuration nodes.
1750 CURSOR get_config_tree_csr ( p_csi_instance_id IN NUMBER) IS
1751 SELECT subject_id
1752 FROM csi_ii_relationships
1753 START WITH object_id = p_csi_instance_id
1754 AND relationship_type_code = 'COMPONENT-OF'
1755 AND trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
1756 AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1))
1757 CONNECT BY PRIOR subject_id = object_id
1758 AND relationship_type_code = 'COMPONENT-OF'
1759 AND trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
1760 AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1))
1761 ORDER BY level;
1762
1763
1764 TYPE nbr_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
1765 TYPE vchar_tbl_type IS TABLE OF VARCHAR2(600) INDEX BY BINARY_INTEGER;
1766 TYPE date_tbl_type IS TABLE OF DATE INDEX BY BINARY_INTEGER;
1767
1768 l_mr_header_id_tbl nbr_tbl_type;
1769 l_mr_effectivity_id_tbl nbr_tbl_type;
1770 l_mr_pc_node_id_tbl nbr_tbl_type;
1771 l_instance_id_tbl nbr_tbl_type;
1772 l_position_id_tbl nbr_tbl_type;
1773 l_pc_node_id_tbl nbr_tbl_type; --amsriniv
1774
1775 l_mfg_date_tbl date_tbl_type;
1776 l_serial_num_tbl vchar_tbl_type;
1777 --l_eff_exists_tbl vchar_tbl_type;
1778 l_subj_id_tbl nbr_tbl_type;
1779
1780 l_index number;
1781 --l_pc_node_inst_id number; --amsriniv
1782 l_valid_mr_flag varchar2(1);
1783 l_junk varchar2(1);
1784 l_rows_count number := 0; --amsriniv Bug 6971165
1785
1786 l_buffer_limit number := 1000;
1787
1788 -- dummy values as manufacturer and country code are not supported by
1789 -- the application.
1790 l_inst_manufacturer_id VARCHAR2(1) := 'm';
1791 l_inst_country_code VARCHAR2(1) := 'c';
1792
1793 l_path_posn_flag BOOLEAN;
1794
1795 l_process_loop NUMBER := 0;
1796 -- l_process_loop indicates the processing stage
1797 -- = 1: processing effectivities based on inventory items for input instance
1798 -- = 2: processing effectivities based on inventory items for config components.
1799 -- = 3: processing effectivities based on MC positions for input instance and
1800 -- components. Note component level details are populated in temp table
1801 -- only if the p_components_flag = 'Y'
1802
1803 l_prev_effectivity_id NUMBER;
1804 l_prev_mr_header_id NUMBER;
1805
1806
1807 --define record type to hold effectivity details.
1808 TYPE eff_dtl_rectype IS RECORD (
1809 eflag_tbl vchar_tbl_type,
1810 srl_from_tbl vchar_tbl_type,
1811 srl_to_tbl vchar_tbl_type,
1812 mID_tbl nbr_tbl_type,
1813 mdate_from_tbl date_tbl_type,
1814 mdate_to_tbl date_tbl_type,
1815 c_code_tbl vchar_tbl_type
1816 );
1817
1818 eff_dtl_rec eff_dtl_rectype;
1819
1820 BEGIN
1821 SAVEPOINT GET_APPLICABLE_MRS_PVT;
1822
1823 IF l_debug = 'Y' THEN
1824 AHL_DEBUG_PUB.enable_debug;
1825 AHL_DEBUG_PUB.debug('Begin private API: AHL_FMP_PVT.GET_APPLICABLE_MRS');
1826 AHL_DEBUG_PUB.debug('Input p_item_instance_id:' || p_item_instance_id);
1827 AHL_DEBUG_PUB.debug('Input p_mr_header_id:' || p_mr_header_id);
1828 AHL_DEBUG_PUB.debug('Input p_components_flag:' || p_components_flag);
1829 AHL_DEBUG_PUB.debug('Input p_include_doNotImplmt:' || p_include_doNotImplmt);
1830 AHL_DEBUG_PUB.debug('Input p_visit_type_code:' || p_visit_type_code);
1831 END IF;
1832
1833 IF FND_API.to_boolean(p_init_msg_list) THEN
1834 FND_MSG_PUB.initialize;
1835 END IF;
1836
1837 x_return_status := 'S';
1838
1839 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version, p_api_version,
1840 l_api_name, G_PKG_NAME)
1841 THEN
1842 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1843 END IF;
1844
1845 IF (p_validation_level = FND_API.G_VALID_LEVEL_FULL)
1846 THEN
1847 -- validate input instance.
1848 OPEN check_instance_exists(p_item_instance_id);
1849 FETCH check_instance_exists INTO l_item_instance_id;
1850 IF check_instance_exists%NOTFOUND THEN
1851 CLOSE check_instance_exists;
1852 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_INVALID_ITEM_INSTANCE');
1853 FND_MESSAGE.SET_TOKEN('INSTANCE',p_item_instance_id);
1854 FND_MSG_PUB.ADD;
1855 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1856 END IF;
1857 CLOSE check_instance_exists;
1858 END IF;
1859
1860 IF l_debug = 'Y' THEN
1861 AHL_DEBUG_PUB.debug('After instance validation:' || p_item_instance_id);
1862 END IF;
1863
1864 /* -- commenting as this is not required.
1865 -- get root instance.
1866 OPEN csi_root_instance_csr(p_item_instance_id);
1867 FETCH csi_root_instance_csr INTO l_root_instance_id;
1868 IF (csi_root_instance_csr%NOTFOUND) THEN
1869 l_root_instance_id := p_item_instance_id;
1870 END IF;
1871 CLOSE csi_root_instance_csr;
1872
1873 -- get uc header ID.
1874 OPEN uc_top_inst(l_root_instance_id);
1875 FETCH uc_top_inst INTO l_uc_header_id;
1876 CLOSE uc_top_inst;
1877 */
1878
1879
1880 -- start processing. First populate temp table ahl_applicable_instances by calling
1881 -- MC api to map instance (and components) to path positions.
1882
1883 IF l_debug = 'Y' THEN
1884 AHL_DEBUG_PUB.debug('Start Processing..');
1885 END IF;
1886
1887 l_path_posn_flag := FALSE;
1888
1889 IF (p_visit_type_code IS NOT NULL) THEN
1890 -- check if effectivities exist with path positions for visit type.
1891 OPEN relationship_vtype_csr(p_visit_type_code);
1892 FETCH relationship_vtype_csr INTO l_junk;
1893 IF (relationship_vtype_csr%FOUND) THEN
1894 l_path_posn_flag := TRUE;
1895 END IF;
1896 CLOSE relationship_vtype_csr;
1897
1898 ELSIF (p_mr_header_id IS NOT NULL) THEN
1899 -- check if effectivities exist with path positions for mr_header_id.
1900 OPEN relationship_mr_csr(p_mr_header_id);
1901 FETCH relationship_mr_csr INTO l_junk;
1902 IF (relationship_mr_csr%FOUND) THEN
1903 l_path_posn_flag := TRUE;
1904 END IF;
1905 CLOSE relationship_mr_csr;
1906
1907 ELSE
1908 -- check if any effectivites exist with path positions.
1909 OPEN relationship_csr;
1910 FETCH relationship_csr INTO l_junk;
1911 IF (relationship_csr%FOUND) THEN
1912 l_path_posn_flag := TRUE;
1913 END IF;
1914 CLOSE relationship_csr;
1915 END IF;
1916
1917
1918 IF (l_path_posn_flag) THEN
1919
1920 IF l_debug = 'Y' THEN
1921 AHL_DEBUG_PUB.debug('Processing MC Relationships..');
1922 END IF;
1923
1924 DELETE FROM ahl_applicable_instances;
1925 -- for input instance.
1926 AHL_MC_PATH_POSITION_PVT.map_instance_to_positions
1927 (
1928 p_api_version => 1.0,
1929 p_init_msg_list => fnd_api.g_false,
1930 p_commit => fnd_api.g_false,
1931 p_validation_level => p_validation_level,
1932 p_csi_item_instance_id => p_item_instance_id,
1933 x_return_status => l_return_status,
1934 x_msg_count => l_msg_count,
1935 x_msg_data => x_msg_data
1936 );
1937
1938 -- Raise errors if exceptions occur
1939 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
1940 RAISE FND_API.G_EXC_ERROR;
1941 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1942 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1943 END IF;
1944
1945 IF (p_components_flag = 'Y') THEN
1946 IF l_debug = 'Y' THEN
1947 AHL_DEBUG_PUB.debug('Processing Component MC Relationships..');
1948 END IF;
1949
1950 OPEN get_config_tree_csr(p_item_instance_id);
1951 LOOP
1952 FETCH get_config_tree_csr BULK COLLECT INTO l_subj_id_tbl LIMIT l_buffer_limit;
1953 EXIT WHEN l_subj_id_tbl.COUNT = 0;
1954
1955 FOR j IN l_subj_id_tbl.FIRST..l_subj_id_tbl.LAST LOOP
1956 AHL_MC_PATH_POSITION_PVT.map_instance_to_positions
1957 (
1958 p_api_version => 1.0,
1959 p_init_msg_list => fnd_api.g_false,
1960 p_commit => fnd_api.g_false,
1961 p_validation_level => p_validation_level,
1962 p_csi_item_instance_id => l_subj_id_tbl(j),
1963 x_return_status => l_return_status,
1964 x_msg_count => l_msg_count,
1965 x_msg_data => x_msg_data
1966 );
1967 -- Raise errors if exceptions occur
1968 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
1969 RAISE FND_API.G_EXC_ERROR;
1970 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1971 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1972 END IF;
1973
1974 END LOOP;
1975 l_subj_id_tbl.DELETE;
1976 END LOOP;
1977 CLOSE get_config_tree_csr;
1978
1979 END IF; -- p_components_flag
1980
1981 END IF; -- l_path_posn_flag
1982
1983 IF l_debug = 'Y' THEN
1984 AHL_DEBUG_PUB.debug('Processing Inventory Items..');
1985 END IF;
1986
1987 l_index := 1;
1988
1989 -- initialize. Tracks if MC api to get path position details has been called or not.
1990 -- l_pc_node_inst_id := NULL; --amsriniv
1991
1992 -- indicates processing stage.
1993 l_process_loop := 1; -- process inv items for input instance.
1994
1995 -- Loop based on processing stage
1996 LOOP
1997 -- Get effectivities for the mr and instance.
1998 IF (l_process_loop = 1) THEN
1999 -- get eff for top node
2000 IF (p_visit_type_code IS NULL) THEN
2001 OPEN get_inst_mr_inv_csr(p_item_instance_id,p_mr_header_id);
2002 ELSE
2003 OPEN get_inst_vst_inv_csr(p_item_instance_id, p_visit_type_code);
2004 END IF;
2005 ELSIF (l_process_loop = 2) THEN
2006 -- get eff for components.
2007 IF (p_visit_type_code IS NULL) THEN
2008 OPEN get_comp_mr_inv_csr(p_item_instance_id,p_mr_header_id);
2009 ELSE
2010 OPEN get_comp_vst_inv_csr(p_item_instance_id, p_visit_type_code);
2011 END IF;
2012 IF l_debug = 'Y' THEN
2013 AHL_DEBUG_PUB.debug('Processing Component Effectivities based on Inventory Items..');
2014 END IF;
2015
2016 ELSIF (l_process_loop = 3) THEN
2017 -- get eff based on positions
2018 IF (p_visit_type_code IS NULL) THEN
2019 OPEN get_posn_mr_csr(p_mr_header_id);
2020 ELSE
2021 OPEN get_posn_vst_csr(p_visit_type_code);
2022 END IF;
2023
2024 IF l_debug = 'Y' THEN
2025 AHL_DEBUG_PUB.debug('Processing Effectivities based on MC Positions..');
2026 END IF;
2027 END IF;
2028
2029 LOOP
2030 -- fetch effectivity data and process.
2031 IF (l_process_loop = 1) THEN
2032 IF (p_visit_type_code IS NULL) THEN
2033 FETCH get_inst_mr_inv_csr BULK COLLECT INTO l_mr_header_id_tbl, l_mr_effectivity_id_tbl, l_mr_pc_node_id_tbl,
2034 l_instance_id_tbl, l_mfg_date_tbl, l_serial_num_tbl --,l_eff_exists_tbl
2035 LIMIT l_buffer_limit;
2036
2037 ELSE
2038
2039 FETCH get_inst_vst_inv_csr BULK COLLECT INTO l_mr_header_id_tbl, l_mr_effectivity_id_tbl, l_mr_pc_node_id_tbl,
2040 l_instance_id_tbl, l_mfg_date_tbl, l_serial_num_tbl --,l_eff_exists_tbl
2041 LIMIT l_buffer_limit;
2042 END IF;
2043 ELSIF (l_process_loop = 2) THEN
2044 IF (p_visit_type_code IS NULL) THEN
2045 FETCH get_comp_mr_inv_csr BULK COLLECT INTO l_mr_header_id_tbl, l_mr_effectivity_id_tbl, l_mr_pc_node_id_tbl,
2046 l_instance_id_tbl, l_mfg_date_tbl, l_serial_num_tbl --, l_eff_exists_tbl
2047 LIMIT l_buffer_limit;
2048
2049 ELSE
2050
2051 FETCH get_comp_vst_inv_csr BULK COLLECT INTO l_mr_header_id_tbl, l_mr_effectivity_id_tbl, l_mr_pc_node_id_tbl,
2052 l_instance_id_tbl, l_mfg_date_tbl, l_serial_num_tbl --, l_eff_exists_tbl
2053 LIMIT l_buffer_limit;
2054 END IF;
2055 ELSIF (l_process_loop = 3) THEN
2056 IF (p_visit_type_code IS NULL) THEN
2057 FETCH get_posn_mr_csr BULK COLLECT INTO l_mr_header_id_tbl, l_mr_effectivity_id_tbl, l_mr_pc_node_id_tbl,
2058 l_instance_id_tbl, l_mfg_date_tbl, l_serial_num_tbl --, l_eff_exists_tbl
2059 LIMIT l_buffer_limit;
2060
2061 ELSE
2062
2063 FETCH get_posn_vst_csr BULK COLLECT INTO l_mr_header_id_tbl, l_mr_effectivity_id_tbl, l_mr_pc_node_id_tbl,
2064 l_instance_id_tbl, l_mfg_date_tbl, l_serial_num_tbl --, l_eff_exists_tbl
2065 LIMIT l_buffer_limit;
2066 END IF;
2067
2068 END IF; -- l_process_loop
2069
2070 EXIT WHEN (l_mr_header_id_tbl.count = 0);
2071
2072 IF l_debug = 'Y' THEN
2073 AHL_DEBUG_PUB.debug('Count of l_mr_header_id_tbl:' || l_mr_header_id_tbl.count);
2074 END IF;
2075
2076 -- process retrieved effectivity IDs.
2077 FOR i IN l_mr_effectivity_id_tbl.FIRST..l_mr_effectivity_id_tbl.LAST LOOP
2078
2079 /*
2080 IF l_debug = 'Y' THEN
2081 AHL_DEBUG_PUB.debug('Now processing MR ID:EFF ID:INST ID:' || l_mr_header_id_tbl(i) || ':' || l_mr_effectivity_id_tbl(i) || ':' || l_instance_id_tbl(i));
2082 END IF;
2083 */
2084
2085 -- to begin with, set effectivity as valid.
2086 l_valid_mr_flag := 'Y';
2087
2088 --DBMS_OUTPUT.PUT_LINE('API2: The number of MR before checking effectivity details is: '||l_appli_mr_tbl.COUNT||' After loop '||i);
2089
2090 -- 24 Oct 08: performance changes to reduce executions on effect details query.
2091 --IF (l_eff_exists_tbl(i) = 'Y') THEN
2092 IF(l_prev_effectivity_id IS NULL OR l_prev_effectivity_id <> l_mr_effectivity_id_tbl(i)) THEN
2093 -- read effectivity details
2094 OPEN get_effect_details(l_mr_effectivity_id_tbl(i));
2095 FETCH get_effect_details BULK COLLECT INTO eff_dtl_rec.eflag_tbl, eff_dtl_rec.srl_from_tbl, eff_dtl_rec.srl_to_tbl,
2096 eff_dtl_rec.mID_tbl, eff_dtl_rec.mdate_from_tbl, eff_dtl_rec.mdate_to_tbl, eff_dtl_rec.c_code_tbl;
2097 CLOSE get_effect_details;
2098 l_prev_effectivity_id := l_mr_effectivity_id_tbl(i);
2099 END IF;
2100 IF (eff_dtl_rec.eflag_tbl.count > 0) THEN
2101
2102 --FOR l_effect_dtl IN get_effect_details(l_mr_effectivity_id_tbl(i)) LOOP
2103 FOR j IN eff_dtl_rec.eflag_tbl.FIRST..eff_dtl_rec.eflag_tbl.LAST LOOP
2104 --l_rows_count := get_effect_details%ROWCOUNT;
2105 IF eff_dtl_rec.eflag_tbl(j) = 'N' THEN
2106 IF (check_sn_inside(l_serial_num_tbl(i), eff_dtl_rec.srl_from_tbl(j), eff_dtl_rec.srl_to_tbl(j)) AND
2107 (eff_dtl_rec.mID_tbl(j) IS NULL OR eff_dtl_rec.mID_tbl(j) = l_inst_manufacturer_id) AND
2108 (eff_dtl_rec.mdate_from_tbl(j) IS NULL OR eff_dtl_rec.mdate_from_tbl(j) <= l_mfg_date_tbl(i)) AND
2109 (eff_dtl_rec.mdate_to_tbl(j) IS NULL OR eff_dtl_rec.mdate_to_tbl(j) >= l_mfg_date_tbl(i)) AND
2110 (eff_dtl_rec.c_code_tbl(j) IS NULL OR eff_dtl_rec.c_code_tbl(j) = l_inst_country_code)) THEN
2111 l_valid_mr_flag := 'Y';
2112 EXIT;
2113 ELSE
2114 l_valid_mr_flag := 'N';
2115 END IF;
2116 ELSE
2117 IF (check_sn_outside(l_serial_num_tbl(i), eff_dtl_rec.srl_from_tbl(j), eff_dtl_rec.srl_to_tbl(j)) AND
2118 (eff_dtl_rec.mID_tbl(j) IS NULL OR eff_dtl_rec.mID_tbl(j) = l_inst_manufacturer_id) AND
2119 (eff_dtl_rec.mdate_from_tbl(j) IS NULL OR eff_dtl_rec.mdate_from_tbl(j) <= l_mfg_date_tbl(i)) AND
2120 (eff_dtl_rec.mdate_to_tbl(j) IS NULL OR eff_dtl_rec.mdate_to_tbl(j) >= l_mfg_date_tbl(i)) AND
2121 (eff_dtl_rec.c_code_tbl(j) IS NULL OR eff_dtl_rec.c_code_tbl(j) = l_inst_country_code)) THEN
2122 l_valid_mr_flag := 'Y';
2123 ELSE
2124 l_valid_mr_flag := 'N';
2125 EXIT;
2126 END IF;
2127 END IF;
2128 END LOOP;
2129
2130 END IF; -- eff_dtl_rec.eflag_tbl.count > 0
2131 --END IF; -- l_eff_exists_tbl(i)
2132
2133 -- 24 Oct 08: reverted code changes to chk for all pairs of c_instance_id and c_pc_node_id due to sorting change.
2134 -- Eff rows are now processed by effectivity ID and mr header ID.
2135 /*amsriniv. Bug 6767803. Rather than executing the below query for all pairs of c_instance_id and c_pc_node_id, to
2136 save on performance, we execute validate_pc_node_csr only when instance changes. We BULK COLLECT the PC_NODE_IDs
2137 into a table and iterate throught it whehn instance is unchanged.
2138 */
2139 --amsriniv. Begin
2140 IF (l_valid_mr_flag = 'Y' AND l_mr_pc_node_id_tbl(i) IS NOT NULL) THEN
2141 --l_valid_mr_flag := 'N';
2142 /*
2143 IF (l_pc_node_inst_id IS NULL OR (l_pc_node_inst_id IS NOT NULL AND l_pc_node_inst_id <> l_instance_id_tbl(i))) THEN
2144 l_pc_node_id_tbl.delete;
2145 */
2146 OPEN validate_pc_node_csr(l_instance_id_tbl(i), l_mr_pc_node_id_tbl(i));
2147 FETCH validate_pc_node_csr INTO l_junk;
2148 IF (validate_pc_node_csr%NOTFOUND) THEN
2149 l_valid_mr_flag := 'N';
2150 END IF;
2151 CLOSE validate_pc_node_csr;
2152 --l_pc_node_inst_id := l_instance_id_tbl(i);
2153 /*
2154 END IF;
2155 IF (l_pc_node_id_tbl.COUNT > 0) THEN
2156 FOR j IN l_pc_node_id_tbl.FIRST..l_pc_node_id_tbl.LAST LOOP
2157 IF (l_pc_node_id_tbl(j) = l_mr_pc_node_id_tbl(i)) THEN
2158 l_valid_mr_flag := 'Y';
2159 EXIT;
2160 END IF;
2161 END LOOP;
2162 END IF;
2163 */
2164 END IF;
2165 --amsriniv End
2166
2167 IF l_debug = 'Y' THEN
2168 AHL_DEBUG_PUB.debug('MR ID:EFF ID:INST ID:Valid Flag:' || l_mr_header_id_tbl(i) || ':' || l_mr_effectivity_id_tbl(i) || ':' || l_instance_id_tbl(i) || ':' || l_valid_mr_flag);
2169 END IF;
2170
2171 -- add row to x_applicable_mr_tbl
2172 IF (l_valid_mr_flag = 'Y') THEN
2173 --DBMS_OUTPUT.put_line('API2: The number of MR in for loop and before openning cursor is: '||l_appli_mr_tbl.COUNT||' and mr_header_id= '||l_appli_mr_tbl(j).mr_header_id||';');
2174 -- read MR details only once.
2175 IF (l_prev_mr_header_id IS NULL OR l_prev_mr_header_id <> l_mr_header_id_tbl(i)) THEN
2176 OPEN get_mr_attri(l_mr_header_id_tbl(i));
2177 FETCH get_mr_attri INTO l_get_mr_attri;
2178 IF get_mr_attri%NOTFOUND THEN
2179 CLOSE get_mr_attri;
2180 FND_MESSAGE.set_name('AHL','AHL_FMP_INVALID_MR');
2181 FND_MSG_PUB.add;
2182 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2183 ELSE
2184 l_prev_mr_header_id := l_mr_header_id_tbl(i);
2185 CLOSE get_mr_attri;
2186 END IF; -- get_mr_attri%NOTFOUND
2187 END IF; -- l_prev_mr_header_id
2188
2189 IF ((p_include_doNotImplmt <> 'N') OR
2190 (l_get_mr_attri.implement_status_code <> 'OPTIONAL_DO_NOT_IMPLEMENT')) THEN
2191 x_applicable_mr_tbl(l_index).mr_header_id := l_mr_header_id_tbl(i);
2192 x_applicable_mr_tbl(l_index).mr_effectivity_id := l_mr_effectivity_id_tbl(i);
2193 x_applicable_mr_tbl(l_index).item_instance_id := l_instance_id_tbl(i);
2194 x_applicable_mr_tbl(l_index).repetitive_flag := l_get_mr_attri.repetitive_flag;
2195 x_applicable_mr_tbl(l_index).show_repetitive_code := l_get_mr_attri.show_repetitive_code;
2196 x_applicable_mr_tbl(l_index).preceding_mr_header_id := l_get_mr_attri.preceding_mr_header_id;
2197 x_applicable_mr_tbl(l_index).copy_accomplishment_flag := l_get_mr_attri.copy_accomplishment_flag;
2198 x_applicable_mr_tbl(l_index).implement_status_code := l_get_mr_attri.implement_status_code;
2199 x_applicable_mr_tbl(l_index).descendent_count := l_get_mr_attri.descendent_count;
2200 IF l_debug = 'Y' THEN
2201 AHL_DEBUG_PUB.debug('AHL_APPLICABLE_MRS Attributes : mr_header_id ' ||
2202 x_applicable_mr_tbl(l_index).mr_header_id || ' mr_effectivity_id ' ||
2203 x_applicable_mr_tbl(l_index).mr_effectivity_id || ' item_instance_id ' ||
2204 x_applicable_mr_tbl(l_index).item_instance_id || ' repetitive_flag ' ||
2205 x_applicable_mr_tbl(l_index).repetitive_flag || ' show_repetitive_code ' ||
2206 x_applicable_mr_tbl(l_index).show_repetitive_code || ' preceding_mr_header_id ' ||
2207 x_applicable_mr_tbl(l_index).preceding_mr_header_id || ' copy_accomplishment_flag ' ||
2208 x_applicable_mr_tbl(l_index).copy_accomplishment_flag || ' implement_status_code ' ||
2209 x_applicable_mr_tbl(l_index).implement_status_code || ' descendent_count ' ||
2210 x_applicable_mr_tbl(l_index).descendent_count);
2211 END IF;
2212 l_index := l_index+1;
2213 END IF;
2214
2215 END IF; -- l_valid_mr_flag
2216
2217 END LOOP; -- l_mr_effectivity_id_tbl
2218
2219 -- reset tables and get the next batch of mr effectivities.
2220 l_mr_header_id_tbl.delete;
2221 l_mr_effectivity_id_tbl.delete;
2222 l_mr_pc_node_id_tbl.delete;
2223 l_instance_id_tbl.delete;
2224 l_mfg_date_tbl.delete;
2225 l_serial_num_tbl.delete;
2226 --l_eff_exists_tbl.delete;
2227
2228 END LOOP;
2229 -- set l_process_loop value to process next set of rows.
2230 IF (l_process_loop = 1) THEN
2231 IF (get_inst_mr_inv_csr%ISOPEN) THEN
2232 CLOSE get_inst_mr_inv_csr;
2233 ELSIF (get_inst_vst_inv_csr%ISOPEN) THEN
2234 CLOSE get_inst_vst_inv_csr;
2235 END IF;
2236
2237 IF (p_components_flag = 'Y') THEN
2238 l_process_loop := 2;
2239 ELSE
2240 l_process_loop := 3;
2241 END IF;
2242
2243 ELSIF (l_process_loop = 2) THEN
2244 IF (get_comp_mr_inv_csr%ISOPEN) THEN
2245 CLOSE get_comp_mr_inv_csr;
2246 ELSIF (get_comp_vst_inv_csr%ISOPEN) THEN
2247 CLOSE get_comp_vst_inv_csr;
2248 END IF;
2249
2250 l_process_loop := 3;
2251 ELSIF (l_process_loop = 3) THEN
2252 IF (get_posn_mr_csr%ISOPEN) THEN
2253 CLOSE get_posn_mr_csr;
2254 ELSIF (get_posn_mr_csr%ISOPEN) THEN
2255 CLOSE get_posn_vst_csr;
2256 END IF;
2257
2258 EXIT;
2259
2260 END IF;
2261
2262 END LOOP;
2263
2264 DELETE FROM ahl_applicable_instances;
2265
2266 --DBMS_OUTPUT.PUT_LINE('API2: Successfully executed API2!');
2267 IF l_debug = 'Y' THEN
2268 AHL_DEBUG_PUB.debug('End private API: AHL_FMP_PVT.GET_APPLICABLE_MRS');
2269 AHL_DEBUG_PUB.disable_debug;
2270 END IF;
2271
2272
2273 EXCEPTION
2274
2275 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2276 ROLLBACK TO GET_APPLICABLE_MRS_PVT;
2277 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2278 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
2279 p_count => x_msg_count,
2280 p_data => x_msg_data);
2281 IF l_debug = 'Y' THEN
2282 AHL_DEBUG_PUB.log_app_messages(x_msg_count, x_msg_data,
2283 'UNEXPECTED ERROR IN PRIVATE:' );
2284 AHL_DEBUG_PUB.debug('AHL_FMP_PVT.GET_APPLICABLE_MRS');
2285 AHL_DEBUG_PUB.disable_debug;
2286 END IF;
2287
2288 WHEN FND_API.G_EXC_ERROR THEN
2289 ROLLBACK TO GET_APPLICABLE_MRS_PVT;
2290 x_return_status := FND_API.G_RET_STS_ERROR;
2291 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
2292 p_count => x_msg_count,
2293 p_data => x_msg_data);
2294 IF l_debug = 'Y' THEN
2295 AHL_DEBUG_PUB.log_app_messages(x_msg_count, x_msg_data,
2296 'ERROR IN PRIVATE:' );
2297 AHL_DEBUG_PUB.debug('AHL_FMP_PVT.GET_APPLICABLE_MRS');
2298 AHL_DEBUG_PUB.disable_debug;
2299 END IF;
2300
2301 WHEN OTHERS THEN
2302 ROLLBACK TO GET_APPLICABLE_MRS_PVT;
2303 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2304 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2305 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'AHL_FMP_PVT',
2306 p_procedure_name => 'GET_APPLICABLE_MRS',
2307 p_error_text => SUBSTRB(SQLERRM,1,240));
2308 END IF;
2309 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
2310 p_count => x_msg_count,
2311 p_data => x_msg_data);
2312 IF l_debug = 'Y' THEN
2313 AHL_DEBUG_PUB.log_app_messages (x_msg_count, x_msg_data,
2314 'OTHER ERROR IN PRIVATE:' );
2315 AHL_DEBUG_PUB.debug('AHL_FMP_PVT.GET_APPLICABLE_MRS');
2316 AHL_DEBUG_PUB.disable_debug;
2317 END IF;
2318 END GET_APPLICABLE_MRS;
2319
2320 -- Define procedure get_ucHeader, get the unit_config_header_id and unit name for
2321 -- a given item_instance_id
2322 PROCEDURE get_ucHeader (p_item_instance_id IN NUMBER,
2323 x_ucHeaderID OUT NOCOPY NUMBER,
2324 x_unitName OUT NOCOPY VARCHAR2)
2325 IS
2326 -- Get ucHeader for component
2327 CURSOR get_unit_name_com (p_item_instance_id IN NUMBER) IS
2328 SELECT unit_config_header_id, name
2329 FROM ahl_unit_config_headers
2330 WHERE csi_item_instance_id IN ( SELECT object_id
2331 FROM csi_ii_relationships
2332 START WITH subject_id = p_item_instance_id
2333 AND relationship_type_code = 'COMPONENT-OF'
2334 AND sysdate between trunc(nvl(active_start_date,sysdate))
2335 AND trunc(nvl(active_end_date, SYSDATE+1))
2336 CONNECT BY subject_id = PRIOR object_id
2337 AND relationship_type_code = 'COMPONENT-OF'
2338 AND sysdate between trunc(nvl(active_start_date,sysdate))
2339 AND trunc(nvl(active_end_date, SYSDATE+1))
2340 )
2341 AND sysdate between trunc(nvl(active_start_date,sysdate))
2342 AND trunc(nvl(active_end_date, SYSDATE+1));
2343
2344 -- Get ucHeader for top node
2345 CURSOR get_unit_name_top (p_item_instance_id IN NUMBER) IS
2346 SELECT unit_config_header_id, name
2347 FROM ahl_unit_config_headers
2348 WHERE csi_item_instance_id = p_item_instance_id
2349 AND sysdate between trunc(nvl(active_start_date,sysdate))
2350 AND trunc(nvl(active_end_date, SYSDATE+1));
2351
2352 l_get_unit_name_com get_unit_name_com%ROWTYPE;
2353 l_get_unit_name_top get_unit_name_top%ROWTYPE;
2354 l_debug VARCHAR2(1) :=AHL_DEBUG_PUB.is_log_enabled;
2355 BEGIN
2356 --Check for top node.
2357 OPEN get_unit_name_top(p_item_instance_id);
2358 FETCH get_unit_name_top INTO l_get_unit_name_top;
2359 IF (get_unit_name_top%NOTFOUND) THEN
2360 -- Check for component.
2361 OPEN get_unit_name_com(p_item_instance_id);
2362 FETCH get_unit_name_com INTO l_get_unit_name_com;
2363 IF (get_unit_name_com%NOTFOUND) THEN
2364 x_ucHeaderID := NULL;
2365 x_unitName := NULL;
2366 ELSE
2367 x_ucHeaderID := l_get_unit_name_com.unit_config_header_id;
2368 x_unitName := l_get_unit_name_com.name;
2369 END IF;
2370 CLOSE get_unit_name_com;
2371 ELSE
2372 x_ucHeaderID := l_get_unit_name_top.unit_config_header_id;
2373 x_unitName := l_get_unit_name_top.name;
2374 END IF;
2375 CLOSE get_unit_name_top;
2376
2377 END get_ucHeader;
2378
2379 -- to get the top instance for a given item_instance_id
2380 FUNCTION get_topInstanceID(p_item_instance_id IN NUMBER) RETURN NUMBER
2381 IS
2382 -- Get top instance for top component
2383 CURSOR get_instance_top (p_item_instance_id IN NUMBER) IS
2384 SELECT A.instance_id
2385 FROM csi_item_instances A
2386 WHERE A.instance_id IN ( SELECT object_id
2387 FROM csi_ii_relationships
2388 START WITH subject_id = p_item_instance_id
2389 AND relationship_type_code = 'COMPONENT-OF'
2390 AND sysdate between trunc(nvl(active_start_date,sysdate))
2391 AND trunc(nvl(active_end_date, SYSDATE+1))
2392 CONNECT BY subject_id = PRIOR object_id
2393 AND relationship_type_code = 'COMPONENT-OF'
2394 AND sysdate between trunc(nvl(active_start_date,sysdate))
2395 AND trunc(nvl(active_end_date, SYSDATE+1))
2396 )
2397 AND sysdate between trunc(nvl(active_start_date,sysdate))
2398 AND trunc(nvl(active_end_date, SYSDATE+1))
2399 AND NOT EXISTS (SELECT 'X'
2400 FROM csi_ii_relationships B
2401 WHERE B.subject_id = A.instance_id
2402 AND relationship_type_code = 'COMPONENT-OF'
2403 AND SYSDATE between trunc(nvl(B.active_start_date,sysdate)) and trunc(NVL(b.active_end_date,sysdate+1))
2404 );
2405
2406 -- Get instance if it is a top component
2407 CURSOR get_instance_com (p_item_instance_id IN NUMBER) IS
2408 SELECT instance_id
2409 FROM csi_item_instances
2410 WHERE instance_id = p_item_instance_id
2411 AND sysdate between trunc(nvl(active_start_date,sysdate))
2412 AND trunc(nvl(active_end_date, SYSDATE+1));
2413
2414 l_debug VARCHAR2(1) :=AHL_DEBUG_PUB.is_log_enabled;
2415 l_top_instance_id NUMBER;
2416 BEGIN
2417 --Check for top node.
2418 OPEN get_instance_top(p_item_instance_id);
2419 FETCH get_instance_top INTO l_top_instance_id;
2420 IF (get_instance_top%NOTFOUND) THEN
2421 -- Check for component.
2422 OPEN get_instance_com(p_item_instance_id);
2423 FETCH get_instance_com INTO l_top_instance_id;
2424 IF (get_instance_com%NOTFOUND) THEN
2425 l_top_instance_id := NULL;
2426 END IF;
2427 CLOSE get_instance_com;
2428 END IF;
2429 CLOSE get_instance_top;
2430
2431 RETURN l_top_instance_id;
2432
2433 END get_topInstanceID;
2434
2435 -- Define function COUNT_MR_DESCENDENTS --
2436 FUNCTION COUNT_MR_DESCENDENTS(p_mr_header_id IN NUMBER)
2437 RETURN NUMBER IS
2438 CURSOR get_mr_descendents(c_mr_header_id NUMBER) IS
2439 -- when two group MRs having common MRs are added into another group,
2440 -- common MRs are counted only once. This may cause the larger group
2441 -- MR to be processed later
2442 /*
2443 SELECT --count(distinct related_mr_header_id)
2444 count(related_mr_header_id)
2445 FROM ahl_mr_relationships
2446 WHERE EXISTS (SELECT mr_header_id
2447 FROM ahl_mr_headers_b M -- perf bug 6266738
2448 WHERE mr_header_id = related_mr_header_id
2449 AND mr_status_code = 'COMPLETE'
2450 AND SYSDATE between trunc(effective_from) and trunc(nvl(effective_to,SYSDATE+1))
2451 AND (version_number) in (SELECT max(M1.version_number)
2452 from ahl_mr_headers_b M1
2453 where M1.title = m.title -- perf bug 6266738
2454 AND mr_status_code = 'COMPLETE'
2455 AND SYSDATE between trunc(effective_from) and trunc(nvl(effective_to,SYSDATE+1))
2456 )
2457 )
2458 START WITH mr_header_id = c_mr_header_id
2459 AND relationship_code = 'PARENT'
2460 CONNECT BY mr_header_id = PRIOR related_mr_header_id
2461 AND relationship_code = 'PARENT';
2462 */
2463
2464 SELECT count(amr.related_mr_header_id)
2465 FROM ahl_mr_relationships amr
2466 START WITH amr.mr_header_id = c_mr_header_id
2467 AND amr.relationship_code = 'PARENT'
2468 AND exists (select 'x' from ahl_mr_headers_b mr1
2469 where mr1.mr_header_id = amr.related_mr_header_id
2470 and mr1.version_number = (select max(mr2.version_number)
2471 from ahl_mr_headers_b mr2
2472 where mr2.title = mr1.title
2473 and mr2.mr_status_code = 'COMPLETE'
2474 and SYSDATE between trunc(mr2.effective_from)
2475 and trunc(nvl(mr2.effective_to,SYSDATE+1))
2476 )
2477 )
2478 CONNECT BY amr.mr_header_id = PRIOR amr.related_mr_header_id
2479 AND amr.relationship_code = 'PARENT'
2480 AND exists (select 'x' from ahl_mr_headers_b mr1
2481 where mr1.mr_header_id = amr.related_mr_header_id
2482 and mr1.version_number = (select max(mr2.version_number)
2483 from ahl_mr_headers_b mr2
2484 where mr2.title = mr1.title
2485 and mr2.mr_status_code = 'COMPLETE'
2486 and SYSDATE between trunc(mr2.effective_from)
2487 and trunc(nvl(mr2.effective_to,SYSDATE+1))
2488 )
2489 );
2490
2491 l_count NUMBER;
2492 BEGIN
2493 OPEN get_mr_descendents(p_mr_header_id);
2494 FETCH get_mr_descendents INTO l_count;
2495 CLOSE get_mr_descendents;
2496 RETURN l_count;
2497 END COUNT_MR_DESCENDENTS;
2498
2499 -- 10/27/08: This function is no longer used.
2500 -- This function accepts an instance id and a path position id
2501 -- and checks if the instance matches the path position.
2502 -- The path position id may correspond to a version specific path or
2503 -- a version neutral (various degrees) path. It may represent the entire
2504 -- path or only some of the lower most levels of the path.
2505 -- This returns 'T' if the instance matches the path position and 'F' if it does not.
2506 FUNCTION Instance_Matches_Path_Pos(p_instance_id IN NUMBER,
2507 p_path_position_id IN NUMBER) RETURN VARCHAR2 IS
2508
2509 l_api_name CONSTANT VARCHAR2(30) := 'Instance_Matches_Path_Pos';
2510 l_full_name CONSTANT VARCHAR2(80) := 'ahl.plsql.' || g_pkg_name || '.' || l_api_name;
2511 l_return_value VARCHAR2(1) := 'F';
2512 l_return_status VARCHAR2(1);
2513 l_msg_count NUMBER;
2514 l_msg_data VARCHAR2(2000);
2515 l_inst_path_pos_id NUMBER;
2516 l_inst_encoded_path AHL_MC_PATH_POSITIONS.ENCODED_PATH_POSITION%TYPE;
2517 l_input_encoded_path AHL_MC_PATH_POSITIONS.ENCODED_PATH_POSITION%TYPE;
2518 l_path_tbl AHL_MC_PATH_POSITION_PVT.Path_Position_Tbl_Type;
2519 l_path_rec AHL_MC_PATH_POSITION_PVT.Path_Position_Rec_Type;
2520 l_unit_csi_id NUMBER;
2521 l_index NUMBER;
2522
2523 --Fetches lowest level info
2524 CURSOR get_last_uc_rec_csr(c_csi_instance_id IN NUMBER) IS
2525 SELECT hdr.mc_id, hdr.version_number, rel.position_key
2526 FROM ahl_mc_headers_b hdr, ahl_mc_relationships rel, csi_ii_relationships csi_ii
2527 WHERE csi_ii.subject_id = c_csi_instance_id
2528 AND CSI_II.RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
2529 AND TRUNC(nvl(CSI_II.ACTIVE_START_DATE, sysdate)) <= TRUNC(sysdate)
2530 AND TRUNC(nvl(CSI_II.ACTIVE_END_DATE, sysdate+1)) > TRUNC(sysdate)
2531 AND TO_NUMBER(CSI_II.POSITION_REFERENCE) = REL.RELATIONSHIP_ID
2532 AND REL.mc_header_id = HDR.mc_header_id;
2533
2534 --Traverse up and fetch all unit instance ids
2535 CURSOR get_unit_instance_csr(c_csi_instance_id IN NUMBER) IS
2536 SELECT csi.object_id
2537 FROM csi_ii_relationships csi
2538 WHERE csi.object_id IN
2539 (SELECT csi_item_instance_id
2540 FROM ahl_unit_config_headers
2541 WHERE trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
2542 AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1))
2543 )
2544 START WITH csi.subject_id = c_csi_instance_id
2545 AND CSI.RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
2546 AND TRUNC(nvl(CSI.ACTIVE_START_DATE, sysdate)) <= TRUNC(sysdate)
2547 AND TRUNC(nvl(CSI.ACTIVE_END_DATE, sysdate+1)) > TRUNC(sysdate)
2548 CONNECT BY csi.subject_id = PRIOR csi.object_id
2549 AND CSI.RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
2550 AND TRUNC(nvl(CSI.ACTIVE_START_DATE, sysdate)) <= TRUNC(sysdate)
2551 AND TRUNC(nvl(CSI.ACTIVE_END_DATE, sysdate+1)) > TRUNC(sysdate)
2552 AND CSI.POSITION_REFERENCE IS NOT NULL;
2553
2554 --Fetch the unit and unit header info for each instance
2555 CURSOR get_uc_headers_csr(c_csi_instance_id IN NUMBER) IS
2556 SELECT up.parent_mc_id, up.parent_version_number, up.parent_position_key
2557 FROM ahl_uc_header_paths_v up
2558 WHERE up.csi_instance_id = c_csi_instance_id;
2559
2560 CURSOR get_top_unit_inst_csr (c_csi_instance_id IN NUMBER) IS
2561 SELECT hdr.mc_id, hdr.version_number, rel.position_key
2562 FROM ahl_mc_headers_b hdr, ahl_mc_relationships rel, ahl_unit_config_headers uch
2563 WHERE uch.csi_item_instance_id = c_csi_instance_id
2564 AND TRUNC(nvl(uch.ACTIVE_START_DATE, sysdate)) <= TRUNC(sysdate)
2565 AND TRUNC(nvl(uch.ACTIVE_END_DATE, sysdate+1)) > TRUNC(sysdate)
2566 AND hdr.mc_header_id = uch.master_config_id
2567 AND rel.mc_header_id = hdr.mc_header_id
2568 AND rel.parent_relationship_id IS NULL
2569 AND NOT EXISTS (SELECT 'X' FROM CSI_II_RELATIONSHIPS CIR
2570 WHERE CIR.SUBJECT_ID = uch.csi_item_instance_id
2571 AND CIR.RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
2572 AND TRUNC(nvl(CIR.ACTIVE_START_DATE, sysdate)) <= TRUNC(sysdate)
2573 AND TRUNC(nvl(CIR.ACTIVE_END_DATE, sysdate+1)) > TRUNC(sysdate));
2574
2575 CURSOR get_encoded_path_csr(c_path_position_id IN NUMBER) IS
2576 SELECT ENCODED_PATH_POSITION
2577 FROM AHL_MC_PATH_POSITIONS
2578 WHERE PATH_POSITION_ID = c_path_position_id;
2579
2580 BEGIN
2581 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
2582 fnd_log.string(fnd_log.level_procedure, l_full_name||'.begin',
2583 'At the start of PLSQL function. p_instance_id = ' || p_instance_id ||
2584 ', p_path_position_id = ' || p_path_position_id);
2585 END IF;
2586
2587 IF (p_path_position_id IS NULL) THEN
2588 l_return_value := 'T';
2589 RETURN l_return_value;
2590 END IF;
2591
2592 -- Get the version specific encoded path position for the instance
2593 -- NOTE: The following lines of code are reproduced from AHL_MC_PATH_POSITION_PVT.Map_Instance_To_Pos_ID
2594 -- However AHL_MC_PATH_POSITION_PVT.Map_Instance_To_Pos_ID itself cannot be used directly
2595 -- since it also updates the db by creating a path position if it does not exist and this is not
2596 -- acceptable if this function is to be called from a Select statement.
2597 --Fetch the position informations for the instance
2598 OPEN get_last_uc_rec_csr(p_instance_id);
2599 FETCH get_last_uc_rec_csr INTO l_path_rec.mc_id, l_path_rec.version_number, l_path_rec.position_key;
2600 IF (get_last_uc_rec_csr%FOUND) THEN
2601 l_path_tbl(1) := l_path_rec;
2602 --Now fetch the position paths which match at higher levels.
2603 l_index := 0;
2604 --Fetch the header rec info for the instance
2605 OPEN get_unit_instance_csr(p_instance_id);
2606 LOOP
2607 FETCH get_unit_instance_csr INTO l_unit_csi_id;
2608 EXIT WHEN get_unit_instance_csr%NOTFOUND;
2609
2610 OPEN get_uc_headers_csr(l_unit_csi_id);
2611 FETCH get_uc_headers_csr INTO l_path_rec.mc_id, l_path_rec.version_number, l_path_rec.position_key;
2612 CLOSE get_uc_headers_csr;
2613
2614 --Add the path up the tree, decrementing index for each node.
2615 IF (l_path_rec.mc_id is not null AND l_path_rec.position_key is not null) THEN
2616 l_path_tbl(l_index) := l_path_rec;
2617 l_index := l_index - 1;
2618 END IF;
2619 END LOOP;
2620 CLOSE get_unit_instance_csr;
2621 ELSE --if not position node then check if instance is the top unit node
2622 --Fetch the position informations for the unit instance
2623 OPEN get_top_unit_inst_csr(p_instance_id);
2624 FETCH get_top_unit_inst_csr INTO l_path_rec.mc_id, l_path_rec.version_number, l_path_rec.position_key;
2625 IF (get_top_unit_inst_csr%FOUND) THEN
2626 l_path_tbl(1) := l_path_rec;
2627 END IF;
2628 CLOSE get_top_unit_inst_csr;
2629 END IF;
2630 CLOSE get_last_uc_rec_csr;
2631
2632 -- End reproduction from AHL_MC_PATH_POSITION_PVT.Map_Instance_To_Pos_ID
2633
2634 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2635 fnd_log.string(fnd_log.level_statement, l_full_name,
2636 'l_path_tbl.COUNT = ' || l_path_tbl.COUNT);
2637 END IF;
2638 -- Now use the contents of l_path_tbl to create the encoded path
2639 IF (l_path_tbl.COUNT > 0) THEN
2640 l_inst_encoded_path := '';
2641 FOR i IN l_path_tbl.FIRST..l_path_tbl.LAST LOOP
2642 l_inst_encoded_path := l_inst_encoded_path || l_path_tbl(i).mc_id || ':' || l_path_tbl(i).version_number || ':' || l_path_tbl(i).position_key;
2643 IF (i < l_path_tbl.LAST) THEN
2644 l_inst_encoded_path := l_inst_encoded_path || '/';
2645 END IF;
2646 END LOOP;
2647 END IF;
2648
2649 -- Get the encoded path for the input path position
2650 OPEN get_encoded_path_csr(c_path_position_id => p_path_position_id);
2651 FETCH get_encoded_path_csr INTO l_input_encoded_path;
2652 CLOSE get_encoded_path_csr;
2653
2654 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
2655 fnd_log.string(fnd_log.level_statement, l_full_name,
2656 'l_inst_encoded_path = ' || l_inst_encoded_path ||
2657 ', l_input_encoded_path = ' || l_input_encoded_path);
2658 END IF;
2659
2660 -- See if the path positions match
2661 IF (l_inst_encoded_path LIKE '%' || l_input_encoded_path) THEN
2662 l_return_value := 'T';
2663 END IF;
2664
2665 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
2666 fnd_log.string(fnd_log.level_procedure, l_full_name||'.end',
2667 'At the end of PLSQL function. About to return ' || l_return_value);
2668 END IF;
2669 RETURN l_return_value;
2670 END;
2671
2672
2673 -- Define Local Function CHECK_EFFECTIVITY_DETAILS --
2674 FUNCTION CHECK_EFFECTIVITY_DETAILS(
2675 p_item_instance_id IN NUMBER,
2676 p_mr_effectivity_id IN NUMBER
2677 ) RETURN BOOLEAN IS
2678 CURSOR get_effect_detail(c_mr_effectivity_id NUMBER) IS
2679 SELECT exclude_flag, serial_number_from, serial_number_to, manufacturer_id,
2680 manufacture_date_from, manufacture_date_to, country_code
2681 FROM ahl_mr_effectivity_dtls -- perf bug 6266738
2682 WHERE mr_effectivity_id = c_mr_effectivity_id
2683 ORDER BY exclude_flag ASC;
2684
2685 --amsriniv
2686 CURSOR get_inst_attributes(c_item_instance_id NUMBER) IS
2687 SELECT csi.serial_number serial_number ,
2688 to_date(ciea1.attribute_value, 'DD/MM/YYYY') mfg_date ,
2689 'm' manufacturer_id ,
2690 'c' country_code
2691 FROM csi_item_instances csi,
2692 csi_inst_extend_attrib_v ciea1
2693 WHERE csi.instance_id = ciea1.instance_id(+)
2694 AND ciea1.attribute_code(+) = 'AHL_MFG_DATE'
2695 AND ciea1.attribute_level(+) = 'GLOBAL'
2696 AND csi.instance_id = c_item_instance_id;
2697
2698 l_inst_dtl get_inst_attributes%ROWTYPE;
2699 match_dtl BOOLEAN := FALSE;
2700 l_rows_count NUMBER := 0;
2701 BEGIN
2702 OPEN get_inst_attributes(p_item_instance_id);
2703 FETCH get_inst_attributes INTO l_inst_dtl;
2704 IF get_inst_attributes%NOTFOUND THEN
2705 CLOSE get_inst_attributes;
2706 FND_MESSAGE.set_name('AHL','AHL_FMP_INVALID_ITEM_INSTANCE');
2707 FND_MESSAGE.set_token('INSTANCE',p_item_instance_id);
2708 FND_MSG_PUB.add;
2709 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2710 RETURN match_dtl;
2711 END IF;
2712 CLOSE get_inst_attributes;
2713 --DBMS_OUTPUT.PUT_LINE('In check effectivity detail function and before the loop');
2714 FOR l_effect_dtl IN get_effect_detail(p_mr_effectivity_id) LOOP
2715 l_rows_count := get_effect_detail%ROWCOUNT;
2716 IF l_effect_dtl.exclude_flag = 'N' THEN
2717 --DBMS_OUTPUT.PUT_LINE('In check effectivity detail function, serial numbers are sn: '||l_inst_dtl.serial_number||' sn1: '||l_effect_dtl.serial_number_from||' sn2: '||
2718 --l_effect_dtl.serial_number_to);
2719 IF (check_sn_inside(l_inst_dtl.serial_number, l_effect_dtl.serial_number_from, l_effect_dtl.serial_number_to) AND
2720 -- l_inst_dtl.serial_number >= NVL(l_effect_dtl.serial_number_from, l_inst_dtl.serial_number) AND
2721 -- l_inst_dtl.serial_number <= NVL(l_effect_dtl.serial_number_to, l_inst_dtl.serial_number) AND
2722 (l_effect_dtl.manufacturer_id IS NULL OR l_effect_dtl.manufacturer_id = l_inst_dtl.manufacturer_id) AND
2723 (l_effect_dtl.manufacture_date_from IS NULL OR l_effect_dtl.manufacture_date_from <= l_inst_dtl.mfg_date) AND
2724 (l_effect_dtl.manufacture_date_to IS NULL OR l_effect_dtl.manufacture_date_to >= l_inst_dtl.mfg_date) AND
2725 (l_effect_dtl.country_code IS NULL OR l_effect_dtl.country_code = l_inst_dtl.country_code)) THEN
2726 match_dtl := TRUE;
2727 EXIT;
2728 END IF;
2729 ELSE
2730 --DBMS_OUTPUT.PUT_LINE('In check effectivity detail function, serial numbers are sn: '||l_inst_dtl.serial_number||' sn1: '||l_effect_dtl.serial_number_from||' sn2: '||
2731 --l_effect_dtl.serial_number_to);
2732 IF (check_sn_outside(l_inst_dtl.serial_number, l_effect_dtl.serial_number_from, l_effect_dtl.serial_number_to) AND
2733 -- l_inst_dtl.serial_number < NVL(l_effect_dtl.serial_number_from, l_inst_dtl.serial_number||'A') AND
2734 -- l_inst_dtl.serial_number > NVL(l_effect_dtl.serial_number_to, SUBSTR(l_inst_dtl.serial_number,1,LENGTH(l_inst_dtl.serial_number)-1)) AND
2735 (l_effect_dtl.manufacturer_id IS NULL OR l_effect_dtl.manufacturer_id <> l_inst_dtl.manufacturer_id) AND
2736 (l_effect_dtl.manufacture_date_from IS NULL OR l_effect_dtl.manufacture_date_from > l_inst_dtl.mfg_date) AND
2737 (l_effect_dtl.manufacture_date_to IS NULL OR l_effect_dtl.manufacture_date_to < l_inst_dtl.mfg_date) AND
2738 (l_effect_dtl.country_code IS NULL OR l_effect_dtl.country_code <> l_inst_dtl.country_code)) THEN
2739 match_dtl := TRUE;
2740 ELSE
2741 match_dtl := FALSE;
2742 EXIT;
2743 END IF;
2744 END IF;
2745
2746 END LOOP;
2747 IF l_rows_count = 0 THEN
2748 match_dtl := TRUE;
2749 END IF;
2750 RETURN match_dtl;
2751 END CHECK_EFFECTIVITY_DETAILS;
2752
2753 -- Define Local Procedure strip_serial_number_prefix --
2754 PROCEDURE strip_serial_number_prefix(
2755 p_serial_number IN VARCHAR2,
2756 x_serial_prefix OUT NOCOPY VARCHAR2,
2757 x_serial_suffix OUT NOCOPY NUMBER
2758 ) IS
2759 i NUMBER := 0;
2760 l_debug VARCHAR2(1) :=AHL_DEBUG_PUB.is_log_enabled;
2761 BEGIN
2762 LOOP
2763 i := i+1;
2764 EXIT WHEN SUBSTR(p_serial_number,-i,1) NOT IN ('0','1','2','3','4','5','6','7','8','9')
2765 OR i > LENGTH(p_serial_number);
2766 END LOOP;
2767 x_serial_prefix := SUBSTR(p_serial_number,1,LENGTH(p_serial_number)-i+1);
2768 x_serial_suffix := TO_NUMBER(NVL(SUBSTR(p_serial_number,-i+1,i-1),0));
2769 END strip_serial_number_prefix;
2770
2771 -- Check to see whether a VARCHAR2 contains only numeric values
2772 FUNCTION sn_num(
2773 p_serial_number IN VARCHAR2
2774 ) RETURN BOOLEAN IS
2775 i NUMBER := 0;
2776
2777 BEGIN
2778 LOOP
2779 i := i+1;
2780 EXIT WHEN SUBSTR(p_serial_number,-i,1) NOT IN ('0','1','2','3','4','5','6','7','8','9')
2781 OR i > LENGTH(p_serial_number);
2782 END LOOP;
2783 IF i = LENGTH(p_serial_number) + 1 THEN
2784 RETURN TRUE;
2785 ELSE
2786 RETURN FALSE;
2787 END IF;
2788 END sn_num;
2789
2790 -- Check to see whether a given serial number is within the serial number range defined.
2791 FUNCTION check_sn_inside(
2792 p_sn IN VARCHAR2,
2793 p_sn1 IN VARCHAR2,
2794 p_sn2 IN VARCHAR2
2795 ) RETURN BOOLEAN IS
2796 BEGIN
2797 IF (p_sn) IS NULL THEN
2798 RETURN FALSE;
2799 END IF;
2800
2801 IF (p_sn1 IS NOT NULL AND sn_num(p_sn1) AND p_sn2 IS NOT NULL AND sn_num(p_sn2)) THEN
2802 IF (sn_num(p_sn)) THEN
2803 IF (TO_NUMBER(p_sn1)<=TO_NUMBER(p_sn) AND TO_NUMBER(p_sn)<=TO_NUMBER(p_sn2)) THEN
2804 RETURN TRUE;
2805 ELSE
2806 RETURN FALSE;
2807 END IF;
2808 -- nonnumeric case.
2809 ELSE
2810 IF (p_sn1 <= p_sn AND p_sn<= p_sn2) THEN
2811 RETURN TRUE;
2812 ELSE
2813 RETURN FALSE;
2814 END IF;
2815 END IF;
2816 ELSIF (p_sn1 IS NULL AND p_sn2 IS NOT NULL AND sn_num(p_sn2)) THEN
2817 IF (sn_num(p_sn)) THEN
2818 IF (TO_NUMBER(p_sn)<=TO_NUMBER(p_sn2)) THEN
2819 RETURN TRUE;
2820 ELSE
2821 RETURN FALSE;
2822 END IF;
2823 -- nonnumeric case.
2824 ELSE
2825 IF (p_sn <= p_sn2) THEN
2826 RETURN TRUE;
2827 ELSE
2828 RETURN FALSE;
2829 END IF;
2830 END IF;
2831 ELSIF (p_sn2 IS NULL AND p_sn1 IS NOT NULL AND sn_num(p_sn1)) THEN
2832 IF (sn_num(p_sn)) THEN
2833 IF (TO_NUMBER(p_sn1)<=TO_NUMBER(p_sn)) THEN
2834 RETURN TRUE;
2835 ELSE
2836 RETURN FALSE;
2837 END IF;
2838 -- nonnumeric case.
2839 ELSE
2840 IF (p_sn1<p_sn) THEN
2841 RETURN TRUE;
2842 ELSE
2843 RETURN FALSE;
2844 END IF;
2845 END IF;
2846 ELSIF (p_sn1 IS NOT NULL AND p_sn2 IS NOT NULL AND (NOT sn_num(p_sn1) OR NOT sn_num(p_sn2))) THEN
2847 IF (p_sn IS NOT NULL AND p_sn <= P_sn2 AND p_sn >= p_sn1) THEN
2848 RETURN TRUE;
2849 ELSE
2850 RETURN FALSE;
2851 END IF;
2852 ELSIF (p_sn1 IS NOT NULL AND NOT sn_num(p_sn1) AND p_sn2 IS NULL) THEN
2853 IF (p_sn IS NOT NULL AND p_sn >= p_sn1) THEN
2854 RETURN TRUE;
2855 ELSE
2856 RETURN FALSE;
2857 END IF;
2858 ELSIF (p_sn2 IS NOT NULL AND NOT sn_num(p_sn2) AND p_sn1 IS NULL) THEN
2859 IF (p_sn IS NOT NULL AND p_sn <= p_sn2) THEN
2860 RETURN TRUE;
2861 ELSE
2862 RETURN FALSE;
2863 END IF;
2864 ELSE
2865 RETURN TRUE;
2866 END IF;
2867 END check_sn_inside;
2868
2869
2870 -- Check to see whether a given serial number is outside of the serial number range defined.
2871 FUNCTION check_sn_outside(
2872 p_sn IN VARCHAR2,
2873 p_sn1 IN VARCHAR2,
2874 p_sn2 IN VARCHAR2
2875 ) RETURN BOOLEAN IS
2876 BEGIN
2877
2878 -- serial number is null.
2879 IF (p_sn IS NULL) THEN
2880 RETURN FALSE;
2881 END IF;
2882
2883 IF (p_sn1 IS NOT NULL AND sn_num(p_sn1) AND p_sn2 IS NOT NULL AND sn_num(p_sn2)) THEN
2884 IF (sn_num(p_sn)) THEN
2885 IF (TO_NUMBER(p_sn1)>TO_NUMBER(p_sn) OR TO_NUMBER(p_sn)>TO_NUMBER(p_sn2)) THEN
2886 RETURN TRUE;
2887 ELSE
2888 RETURN FALSE;
2889 END IF;
2890 -- fix for bug# 6449096 - non-numeric serials.
2891 ELSIF (p_sn > P_sn2 OR p_sn < p_sn1) THEN
2892 RETURN TRUE;
2893 ELSE
2894 RETURN FALSE;
2895 END IF;
2896 ELSIF (p_sn1 IS NULL AND p_sn2 IS NOT NULL AND sn_num(p_sn2)) THEN
2897 IF (sn_num(p_sn)) THEN
2898 IF (TO_NUMBER(p_sn)>TO_NUMBER(p_sn2)) THEN
2899 RETURN TRUE;
2900 ELSE
2901 RETURN FALSE;
2902 END IF;
2903 -- fix for bug# 6449096 - non-numeric serials.
2904 ELSIF (p_sn > P_sn2) THEN
2905 RETURN TRUE;
2906 ELSE
2907 RETURN FALSE;
2908 END IF;
2909 ELSIF (p_sn2 IS NULL AND p_sn1 IS NOT NULL AND sn_num(p_sn1)) THEN
2910 IF (sn_num(p_sn)) THEN
2911 IF (TO_NUMBER(p_sn1)>TO_NUMBER(p_sn)) THEN
2912 RETURN TRUE;
2913 ELSE
2914 RETURN FALSE;
2915 END IF;
2916 -- fix for bug# 6449096 - non-numeric serials.
2917 ELSIF (p_sn1 > p_sn) THEN
2918 RETURN TRUE;
2919 ELSE
2920 RETURN FALSE;
2921 END IF;
2922 ELSIF (p_sn1 IS NOT NULL AND p_sn2 IS NOT NULL AND (NOT sn_num(p_sn1) OR NOT sn_num(p_sn2))) THEN
2923 IF (p_sn > P_sn2 OR p_sn < p_sn1) THEN
2924 RETURN TRUE;
2925 ELSE
2926 RETURN FALSE;
2927 END IF;
2928 ELSIF (p_sn1 IS NOT NULL AND NOT sn_num(p_sn1) AND p_sn2 IS NULL) THEN
2929 IF (p_sn < p_sn1) THEN
2930 RETURN TRUE;
2931 ELSE
2932 RETURN FALSE;
2933 END IF;
2934 ELSIF (p_sn2 IS NOT NULL AND NOT sn_num(p_sn2) AND p_sn1 IS NULL) THEN
2935 IF (p_sn > p_sn2) THEN
2936 RETURN TRUE;
2937 ELSE
2938 RETURN FALSE;
2939 END IF;
2940 ELSE
2941 RETURN TRUE;
2942 END IF;
2943
2944 -- if control reached here, then return status FALSE.
2945 RETURN FALSE;
2946 END check_sn_outside;
2947
2948 PROCEDURE GET_PM_APPLICABLE_MRS (
2949 p_api_version IN NUMBER,
2950 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2951 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2952 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2953 x_return_status OUT NOCOPY VARCHAR2,
2954 x_msg_count OUT NOCOPY NUMBER,
2955 x_msg_data OUT NOCOPY VARCHAR2,
2956 p_item_instance_id IN NUMBER,
2957 x_applicable_activities_tbl OUT NOCOPY applicable_activities_tbl_type,
2958 x_applicable_programs_tbl OUT NOCOPY applicable_programs_tbl_type
2959 ) IS
2960 iap NUMBER;
2961 iap_u NUMBER;
2962 iaa NUMBER;
2963 l_api_name CONSTANT VARCHAR2(30) := 'GET_PM_APPLICABLE_MRS';
2964 l_api_version CONSTANT NUMBER := 1.0;
2965 l_msg_count NUMBER;
2966 l_msg_data VARCHAR2(2000);
2967 l_return_status VARCHAR2(1);
2968 l_item_instance_id NUMBER;
2969 l_inventory_item_id NUMBER;
2970 l_pm_install VARCHAR2(1);
2971 l_appln_code VARCHAR2(30):=FND_PROFILE.VALUE('AHL_APPLN_USAGE');
2972 l_inp_rec OKS_PM_ENTITLEMENTS_PUB.get_pmcontin_rec;
2973 l_ent_contracts OKS_ENTITLEMENTS_PUB.get_contop_tbl;
2974 l_pm_activities OKS_PM_ENTITLEMENTS_PUB.get_activityop_tbl;
2975 l_prior_mr_header_id NUMBER;
2976 /*
2977 TYPE unique_mr_headers_rec_type IS RECORD
2978 (
2979 mr_header_id NUMBER,
2980 service_line_id NUMBER
2981 );
2982 TYPE unique_mr_headers_tbl_type IS TABLE OF unique_mr_headers_rec_type
2983 INDEX BY BINARY_INTEGER;
2984 l_unique_mr_headers_tbl unique_mr_headers_tbl_type;
2985 */
2986 CURSOR get_applicable_mrs(c_inventory_item_id NUMBER) IS
2987 SELECT mr_header_id, mr_effectivity_id
2988 FROM ahl_mr_effectivities_app_v
2989 WHERE inventory_item_id = c_inventory_item_id
2990 ORDER BY mr_header_id, mr_effectivity_id;
2991
2992 /*
2993 CURSOR get_activities(c_mr_header_id NUMBER) IS
2994 SELECT related_mr_header_id
2995 FROM ahl_mr_relationships_app_v
2996 WHERE mr_header_id = c_mr_header_id
2997 AND relationship_code = 'PARENT';
2998 */
2999 --Get attributes of a given MR
3000 CURSOR get_mr_attri(c_mr_header_id NUMBER) IS
3001 SELECT repetitive_flag,
3002 show_repetitive_code,
3003 whichever_first_code,
3004 implement_status_code
3005 FROM ahl_mr_headers_app_v
3006 WHERE mr_header_id = c_mr_header_id;
3007 l_get_mr_attri get_mr_attri%ROWTYPE;
3008 l_debug VARCHAR2(1) :=AHL_DEBUG_PUB.is_log_enabled;
3009 BEGIN
3010 SAVEPOINT GET_PM_APPLICABLE_MRS_PVT;
3011 IF l_debug = 'Y' THEN
3012 AHL_DEBUG_PUB.enable_debug;
3013 AHL_DEBUG_PUB.debug('Begin private API: AHL_FMP_PVT.GET_PM_APPLICABLE_MRS');
3014 AHL_DEBUG_PUB.debug('');
3015 END IF;
3016 IF FND_API.to_boolean(p_init_msg_list) THEN
3017 FND_MSG_PUB.initialize;
3018 END IF;
3019 x_return_status := 'S';
3020 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version, p_api_version,
3021 l_api_name, G_PKG_NAME)
3022 THEN
3023 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3024 END IF;
3025
3026 l_appln_code:=nvl(FND_PROFILE.VALUE('AHL_APPLN_USAGE'),'x');
3027 IF l_appln_code <> 'PM' THEN
3028 FND_MESSAGE.set_name('AHL', 'AHL_FMP_PM_NOT_INSTALLED');
3029 FND_MSG_PUB.add;
3030 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3031 END IF;
3032
3033 OPEN check_instance_exists(p_item_instance_id);
3034 FETCH check_instance_exists INTO l_item_instance_id;
3035 IF check_instance_exists%NOTFOUND THEN
3036 CLOSE check_instance_exists;
3037 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_INVALID_ITEM_INSTANCE');
3038 FND_MESSAGE.SET_TOKEN('INSTANCE',p_item_instance_id);
3039 FND_MSG_PUB.ADD;
3040 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3041 END IF;
3042 CLOSE check_instance_exists;
3043
3044 OPEN get_inventory_item(p_item_instance_id);
3045 FETCH get_inventory_item INTO l_inventory_item_id;
3046 IF get_inventory_item%NOTFOUND THEN
3047 CLOSE get_inventory_item;
3048 FND_MESSAGE.set_name('AHL', 'AHL_FMP_ITEM_NOT_EXISTS');
3049 FND_MESSAGE.set_token('INSTANCE',p_item_instance_id);
3050 FND_MSG_PUB.add;
3051 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3052 END IF;
3053 CLOSE get_inventory_item;
3054
3055 IF l_debug = 'Y' THEN
3056 AHL_DEBUG_PUB.debug('Item instance id:' || l_item_instance_id);
3057 END IF;
3058
3059 -- Call OKS API to get program_ids
3060 l_inp_rec.contract_number := NULL;
3061 l_inp_rec.contract_number_modifier := NULL;
3062 l_inp_rec.service_line_id := NULL;
3063 l_inp_rec.party_id := NULL;
3064 l_inp_rec.item_id := NULL;
3065 l_inp_rec.product_id := l_item_instance_id;
3066 l_inp_rec.request_date := NULL;
3067
3068 -- sracha[FP Bug #5509763] -- Changes begin
3069 -- l_inp_rec.request_date_start := NULL;
3070 -- l_inp_rec.request_date_end := NULL;
3071 l_inp_rec.request_date_start := sysdate;
3072 l_inp_rec.request_date_end := to_date('31-12-4712','DD-MM-YYYY');
3073 -- sracha [FP Bug #5509763] -- Changes end
3074
3075 l_inp_rec.sort_key := NULL;
3076
3077 OKS_PM_ENTITLEMENTS_PUB.get_pm_contracts
3078 ( p_api_version => 1.0,
3079 p_init_msg_list => FND_API.G_TRUE,
3080 p_inp_rec => l_inp_rec,
3081 x_return_status => l_return_status,
3082 x_msg_count => l_msg_count,
3083 x_msg_data => l_msg_data,
3084 x_ent_contracts => l_ent_contracts,
3085 x_pm_activities => l_pm_activities );
3086 IF l_msg_count > 0 THEN
3087 FND_MESSAGE.set_name('AHL', 'AHL_FMP_CALLING_OKS_API_ERROR');
3088 FND_MSG_PUB.add;
3089 RAISE FND_API.G_EXC_ERROR;
3090 END IF;
3091
3092 IF l_debug = 'Y' THEN
3093 AHL_DEBUG_PUB.debug('Count on ent_contracts:' || l_ent_contracts.count);
3094 AHL_DEBUG_PUB.debug('Count on pm_activities:' || l_pm_activities.count );
3095 END IF;
3096
3097 iap := 1;
3098 iaa := 1;
3099 -- iap_u := 1;
3100 -- l_prior_mr_header_id := -1;
3101 FOR l_get_applicable_mrs IN get_applicable_mrs(l_inventory_item_id) LOOP
3102 IF l_debug = 'Y' THEN
3103 AHL_DEBUG_PUB.debug('Appl MR found:' || l_get_applicable_mrs.mr_header_id);
3104 END IF;
3105
3106 IF l_ent_contracts.COUNT > 0 THEN
3107 FOR i IN l_ent_contracts.FIRST..l_ent_contracts.LAST LOOP
3108 IF l_ent_contracts(i).pm_program_id = l_get_applicable_mrs.mr_header_id THEN
3109
3110 IF l_debug = 'Y' THEN
3111 AHL_DEBUG_PUB.debug('Contract Number found:' || l_ent_contracts(i).contract_number);
3112 AHL_DEBUG_PUB.debug('Service Line ID:' || l_ent_contracts(i).service_line_id);
3113 AHL_DEBUG_PUB.debug('---------------------------');
3114 END IF;
3115
3116 x_applicable_programs_tbl(iap).contract_id := l_ent_contracts(i).contract_id;
3117 x_applicable_programs_tbl(iap).contract_number := l_ent_contracts(i).contract_number;
3118 x_applicable_programs_tbl(iap).contract_number_modifier := l_ent_contracts(i).contract_number_modifier;
3119 x_applicable_programs_tbl(iap).sts_code := l_ent_contracts(i).sts_code;
3120 x_applicable_programs_tbl(iap).service_line_id := l_ent_contracts(i).service_line_id;
3121 x_applicable_programs_tbl(iap).service_name := l_ent_contracts(i).service_name;
3122 x_applicable_programs_tbl(iap).service_description := l_ent_contracts(i).service_description;
3123 x_applicable_programs_tbl(iap).coverage_term_line_id := l_ent_contracts(i).coverage_term_line_id;
3124 x_applicable_programs_tbl(iap).coverage_term_name := l_ent_contracts(i).coverage_term_name;
3125 x_applicable_programs_tbl(iap).coverage_term_description := l_ent_contracts(i).coverage_term_description;
3126 x_applicable_programs_tbl(iap).coverage_type_code := l_ent_contracts(i).coverage_type_code;
3127 x_applicable_programs_tbl(iap).coverage_type_meaning := l_ent_contracts(i).coverage_type_meaning;
3128 x_applicable_programs_tbl(iap).coverage_type_imp_level := l_ent_contracts(i).coverage_type_imp_level;
3129 x_applicable_programs_tbl(iap).service_start_date := l_ent_contracts(i).service_start_date;
3130 x_applicable_programs_tbl(iap).service_end_date := l_ent_contracts(i).service_end_date;
3131 x_applicable_programs_tbl(iap).warranty_flag := l_ent_contracts(i).warranty_flag;
3132 x_applicable_programs_tbl(iap).eligible_for_entitlement := l_ent_contracts(i).eligible_for_entitlement;
3133 x_applicable_programs_tbl(iap).exp_reaction_time := l_ent_contracts(i).exp_reaction_time;
3134 x_applicable_programs_tbl(iap).exp_resolution_time := l_ent_contracts(i).exp_resolution_time;
3135 x_applicable_programs_tbl(iap).status_code := l_ent_contracts(i).status_code;
3136 x_applicable_programs_tbl(iap).status_text := l_ent_contracts(i).status_text;
3137 x_applicable_programs_tbl(iap).date_terminated := l_ent_contracts(i).date_terminated;
3138 x_applicable_programs_tbl(iap).pm_schedule_exists := l_ent_contracts(i).pm_schedule_exists;
3139 x_applicable_programs_tbl(iap).pm_program_id := l_ent_contracts(i).pm_program_id;
3140 x_applicable_programs_tbl(iap).mr_effectivity_id := l_get_applicable_mrs.mr_effectivity_id;
3141 iap := iap + 1;
3142 END IF;
3143 END LOOP;
3144 END IF;
3145 /* IF l_ent_contracts(i).pm_program_id <> l_prior_mr_header_id THEN
3146 l_unique_mr_headers_tbl(iap_u).mr_header_id := l_ent_contracts(i).pm_program_id;
3147 l_unique_mr_headers_tbl(iap_u).service_line_id := l_ent_contracts(i).service_line_id;
3148 iap_u := iap_u + 1;
3149 END IF;
3150 l_prior_mr_header_id := l_ent_contracts(i).pm_program_id;
3151 */
3152 IF l_pm_activities.COUNT > 0 THEN
3153 FOR i IN l_pm_activities.FIRST..l_pm_activities.LAST LOOP
3154 IF (l_pm_activities(i).activity_id = l_get_applicable_mrs.mr_header_id ) THEN
3155 x_applicable_activities_tbl(iaa).mr_header_id := l_pm_activities(i).activity_id;
3156 x_applicable_activities_tbl(iaa).item_instance_id := l_item_instance_id;
3157 x_applicable_activities_tbl(iaa).program_mr_header_id := l_pm_activities(i).pm_program_id;
3158 x_applicable_activities_tbl(iaa).service_line_id := l_pm_activities(i).service_line_id;
3159 x_applicable_activities_tbl(iaa).act_schedule_exists := l_pm_activities(i).act_schedule_exists;
3160 x_applicable_activities_tbl(iaa).mr_effectivity_id := l_get_applicable_mrs.mr_effectivity_id;
3161 iaa := iaa + 1;
3162 END IF;
3163 END LOOP;
3164 END IF;
3165 END LOOP;
3166 /*
3167 iaa := 1;
3168 IF x_applicable_programs_tbl.COUNT >= 1 THEN
3169 FOR i IN x_applicable_programs_tbl.FIRST..x_applicable_programs_tbl.LAST LOOP
3170 FOR l_get_activities IN get_activities(x_applicable_programs_tbl(i).pm_program_id) LOOP
3171 FOR l_get_applicable_mrs IN get_applicable_mrs(l_inventory_item_id) LOOP
3172 IF l_get_activities.related_mr_header_id = l_get_applicable_mrs.mr_header_id THEN
3173 x_applicable_activities_tbl(iaa).mr_header_id := l_get_applicable_mrs.mr_header_id;
3174 x_applicable_activities_tbl(iaa).mr_effectivity_id := l_get_applicable_mrs.mr_effectivity_id;
3175 x_applicable_activities_tbl(iaa).program_mr_header_id := x_applicable_programs_tbl(i).pm_program_id;
3176 x_applicable_activities_tbl(iaa).service_line_id := x_applicable_programs_tbl(i).service_line_id;
3177 iaa := iaa + 1;
3178 END IF;
3179 END LOOP;
3180 END LOOP;
3181 END LOOP;
3182 END IF;
3183 */
3184 IF x_applicable_activities_tbl.COUNT >= 1 THEN
3185 FOR i IN x_applicable_activities_tbl.FIRST..x_applicable_activities_tbl.LAST LOOP
3186 OPEN get_mr_attri(x_applicable_activities_tbl(i).mr_header_id);
3187 FETCH get_mr_attri INTO l_get_mr_attri;
3188 IF get_mr_attri%NOTFOUND THEN
3189 CLOSE get_mr_attri;
3190 FND_MESSAGE.set_name('AHL','AHL_FMP_INVALID_MR');
3191 FND_MSG_PUB.add;
3192 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3193 ELSE
3194 x_applicable_activities_tbl(i).repetitive_flag := l_get_mr_attri.repetitive_flag;
3195 x_applicable_activities_tbl(i).show_repetitive_code := l_get_mr_attri.show_repetitive_code;
3196 x_applicable_activities_tbl(i).whichever_first_code := l_get_mr_attri.whichever_first_code;
3197 x_applicable_activities_tbl(i).implement_status_code := l_get_mr_attri.implement_status_code;
3198 CLOSE get_mr_attri;
3199 END IF;
3200 END LOOP;
3201 END IF;
3202
3203 IF l_debug = 'Y' THEN
3204 AHL_DEBUG_PUB.debug('End private API: AHL_FMP_PVT.GET_PM_APPLICABLE_MRS');
3205 AHL_DEBUG_PUB.disable_debug;
3206 END IF;
3207
3208 EXCEPTION WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3209 ROLLBACK TO GET_PM_APPLICABLE_MRS_PVT;
3210 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3211 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
3212 p_count => x_msg_count,
3213 p_data => x_msg_data);
3214 IF l_debug = 'Y' THEN
3215 AHL_DEBUG_PUB.log_app_messages(x_msg_count, x_msg_data,
3216 'UNEXPECTED ERROR IN PRIVATE:' );
3217 AHL_DEBUG_PUB.debug('AHL_FMP_PVT.GET_PM_APPLICABLE_MRS');
3218 AHL_DEBUG_PUB.disable_debug;
3219 END IF;
3220 WHEN FND_API.G_EXC_ERROR THEN
3221 ROLLBACK TO GET_PM_APPLICABLE_MRS_PVT;
3222 x_return_status := FND_API.G_RET_STS_ERROR;
3223 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
3224 p_count => x_msg_count,
3225 p_data => x_msg_data);
3226 IF l_debug = 'Y' THEN
3227 AHL_DEBUG_PUB.log_app_messages(x_msg_count, x_msg_data,
3228 'ERROR IN PRIVATE:' );
3229 AHL_DEBUG_PUB.debug('AHL_FMP_PVT.GET_PM_APPLICABLE_MRS');
3230 AHL_DEBUG_PUB.disable_debug;
3231 END IF;
3232 WHEN OTHERS THEN
3233 ROLLBACK TO GET_PM_APPLICABLE_MRS_PVT;
3234 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3235 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3236 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'AHL_FMP_PVT',
3237 p_procedure_name => 'GET_PM_APPLICABLE_MRS',
3238 p_error_text => SUBSTRB(SQLERRM,1,240));
3239 END IF;
3240 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
3241 p_count => x_msg_count,
3242 p_data => x_msg_data);
3243 IF l_debug = 'Y' THEN
3244
3245 AHL_DEBUG_PUB.log_app_messages (x_msg_count, x_msg_data,
3246 'OTHER ERROR IN PRIVATE:' );
3247 AHL_DEBUG_PUB.debug('AHL_FMP_PVT.GET_PM_APPLICABLE_MRS');
3248 AHL_DEBUG_PUB.disable_debug;
3249 END IF;
3250
3251 END GET_PM_APPLICABLE_MRS;
3252
3253
3254 PROCEDURE get_visit_applicable_mrs (
3255 p_api_version IN NUMBER,
3256 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3257 p_commit IN VARCHAR2 := FND_API.G_FALSE,
3258 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
3259 x_return_status OUT NOCOPY VARCHAR2,
3260 x_msg_count OUT NOCOPY NUMBER,
3261 x_msg_data OUT NOCOPY VARCHAR2,
3262 p_item_instance_id IN NUMBER,
3263 p_visit_type_code IN VARCHAR2
3264 )
3265 AS
3266 l_api_version CONSTANT NUMBER := 1.0;
3267 l_appl_mrs_tbl AHL_FMP_PVT.applicable_mr_tbl_type;
3268
3269 BEGIN
3270 -- Initialize temporary table.
3271 SAVEPOINT GET_VISIT_APPLICABLE_MRS_PVT;
3272 DELETE FROM AHL_APPLICABLE_MRS;
3273
3274 -- call api to fetch all applicable mrs for ASO installation.
3275 AHL_FMP_PVT.get_applicable_mrs(
3276 p_api_version => l_api_version,
3277 p_init_msg_list => FND_API.G_FALSE,
3278 p_commit => FND_API.G_FALSE,
3279 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
3280 x_return_status => x_return_status,
3281 x_msg_count => x_msg_count,
3282 x_msg_data => x_msg_data,
3283 p_item_instance_id => p_item_instance_id,
3284 p_components_flag => 'N',
3285 p_include_doNotImplmt => 'Y',
3286 p_visit_type_code => p_visit_type_code,
3287 x_applicable_mr_tbl => l_appl_mrs_tbl);
3288
3289 -- Raise errors if exceptions occur
3290 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
3291 RAISE FND_API.G_EXC_ERROR;
3292 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
3293 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3294 END IF;
3295
3296
3297
3298 -- Populate temporary table ahl_applicable_mrs.
3299 IF (l_appl_mrs_tbl.COUNT > 0) THEN
3300 FOR i IN l_appl_mrs_tbl.FIRST..l_appl_mrs_tbl.LAST LOOP
3301 -- dbms_output.put_line( l_appl_mrs_tbl(i).item_instance_id||' '||
3302 -- l_appl_mrs_tbl(i).mr_header_id);
3303 INSERT INTO AHL_APPLICABLE_MRS
3304 (
3305 CSI_ITEM_INSTANCE_ID,
3306 MR_HEADER_ID,
3307 MR_EFFECTIVITY_ID,
3308 REPETITIVE_FLAG ,
3309 SHOW_REPETITIVE_CODE,
3310 COPY_ACCOMPLISHMENT_CODE,
3311 PRECEDING_MR_HEADER_ID,
3312 IMPLEMENT_STATUS_CODE,
3313 DESCENDENT_COUNT
3314 )
3315 Values
3316 (
3317 l_appl_mrs_tbl(i).item_instance_id,
3318 l_appl_mrs_tbl(i).mr_header_id,
3319 l_appl_mrs_tbl(i).mr_effectivity_id,
3320 l_appl_mrs_tbl(i).repetitive_flag,
3321 l_appl_mrs_tbl(i).show_repetitive_code,
3322 l_appl_mrs_tbl(i).copy_accomplishment_flag,
3323 l_appl_mrs_tbl(i).preceding_mr_header_id,
3324 l_appl_mrs_tbl(i).implement_status_code,
3325 l_appl_mrs_tbl(i).descendent_count
3326 );
3327 END LOOP;
3328 END IF;
3329
3330 AHL_UMP_UTIL_PKG.process_group_mrs;
3331
3332 -- Delete visit types that do not match..
3333 DELETE AHL_APPLICABLE_MRS A
3334 WHERE NOT EXISTS (SELECT 'x' FROM AHL_MR_VISIT_TYPES
3335 WHERE MR_HEADER_ID=A.MR_HEADER_ID
3336 AND MR_VISIT_TYPE_CODE = P_VISIT_TYPE_CODE
3337 );
3338
3339 -- delete MRs from relationships table to remove duplicates.
3340 DELETE AHL_APPLICABLE_MRS A
3341 WHERE EXISTS ( select 'x' FROM
3342 AHL_APPLICABLE_MR_RELNS REL
3343 WHERE REL.related_mr_header_id = A.mr_header_id
3344 AND REL.RELATED_CSI_ITEM_INSTANCE_ID = A.CSI_ITEM_INSTANCE_ID);
3345
3346 -- Delete corressponding records for the above deletes from relationships table.
3347 DELETE AHL_APPLICABLE_MR_RELNS A
3348 WHERE NOT EXISTS (SELECT 'x' FROM AHL_APPLICABLE_MRS B
3349 WHERE B.MR_HEADER_ID = A.ORIG_MR_HEADER_ID
3350 AND B.CSI_ITEM_INSTANCE_ID = A.ORIG_CSI_ITEM_INSTANCE_ID);
3351
3352
3353
3354 EXCEPTION WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3355 ROLLBACK TO GET_VISIT_APPLICABLE_MRS_PVT;
3356 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3357 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
3358 p_count => x_msg_count,
3359 p_data => x_msg_data);
3360 WHEN FND_API.G_EXC_ERROR THEN
3361 ROLLBACK TO GET_VISIT_APPLICABLE_MRS_PVT;
3362 x_return_status := FND_API.G_RET_STS_ERROR;
3363 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
3364 p_count => x_msg_count,
3365 p_data => x_msg_data);
3366 WHEN OTHERS THEN
3367 ROLLBACK TO GET_VISIT_APPLICABLE_MRS_PVT;
3368 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3369 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3370 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'AHL_FMP_PVT',
3371 p_procedure_name => 'GET_PM_APPLICABLE_MRS',
3372 p_error_text => SUBSTRB(SQLERRM,1,240));
3373 END IF;
3374 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
3375 p_count => x_msg_count,
3376 p_data => x_msg_data);
3377 END GET_VISIT_APPLICABLE_MRS;
3378
3379 END AHL_FMP_PVT; -- Package Body
3380
[Home] [Help]